Export User's Security Roles, Duties and Privileges in ax 2012

 static void AD_ExportUsersecurities_User(Args _args)

{

    SysExcelApplication         xlsApplication;

    SysExcelWorkBooks           xlsWorkBookCollection;

    SysExcelWorkBook            xlsWorkBook;

    SysExcelWorkSheets          xlsWorkSheetCollection;

    SysExcelWorkSheet           xlsWorkSheet;

    SysExcelRange               xlsRange;

    CustTable                   custTable;

    int                         row = 1;

    str                         fileName;

    SecurityTask                securityTask, securityTaskDuty, securityTaskPriv;

    SecuritySubTask             securitySubTask;

    SecurityRoleTaskGrant       securityRoleTaskGrant;

    SecurityRole                securityRole, securityRoleDuty,securityRolePriv, securityRoleM;

    SecurityTaskEntryPoint      SecurityTaskEntryPoint;

    SecurableObject             SecurableObject;

    SecurityUserRole            securityUserRole;

    UserInfo                    userInfo;

    SecurityTaskEntryPoint  taskEntryPoint;

    SecurityRole            role;

    SecurityRoleTaskGrant   taskGrant;

    SecuritySubTask         subTask;

    SecurityTask            privilege;

    str privName;

    str dutyName;

    str entrName;

    str accessLevel;

    boolean i = true,j = true;

    ;

    fileName = "UserName.CSV";    //Rename file

    xlsApplication           = SysExcelApplication::construct();

    xlsWorkBookCollection    = xlsApplication.workbooks();

    xlsWorkBook              = xlsWorkBookCollection.add();

    xlsWorkSheetCollection   = xlsWorkBook.worksheets();

    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

    xlsWorkSheet.cells().item(row,1).value("User Id");

    xlsWorkSheet.cells().item(row,2).value("User Role");

    xlsWorkSheet.cells().item(row,3).value("Duty");

    xlsWorkSheet.cells().item(row,4).value("Privilege AOT");

    xlsWorkSheet.cells().item(row,5).value("Privilege Description");

    row++;

    while select RecId,Name, AotName from securityRole

        join User,SecurityRole from securityUserRole

        where securityUserRole.User == "axadminr"     //Enter user information here

        && securityUserRole.SecurityRole == securityRole.RecId

    {

        while select SecurityRole,SecurityTask  from securityRoleTaskGrant

            where securityRoleTaskGrant.SecurityRole == securityRole.RecId

        {

            select * from securityTask

                where securityTask.RecId == securityRoleTaskGrant.SecurityTask

                && securityTask.Type != SecurityTaskType::ProcessCycle;

            {

                if(securityTask.Type == SecurityTaskType::Duty)

                {

                    while select * from subTask //where subTask.SecuritySubTask == SecurityTaskEntryPoint.SecurityTask

                        where subTask.SecurityTask == securityRoleTaskGrant.SecurityTask

                    {

                        select RecId, AotName, Name, Type from privilege

                            where privilege.RecId == securityRoleTaskGrant.SecurityTask

                            && SecurityTaskType::Duty == privilege.Type;

                                dutyName = privilege.AotName;

                        select RecId, AotName, Name, Type from privilege

                            where privilege.RecId == subTask.SecuritySubTask

                            && SecurityTaskType::Privilege == privilege.Type;

                            privName = privilege.AotName;

                        {

                            //Information to Excel

                            xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id

                            xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name

                            xlsWorkSheet.cells().item(row,3).value(dutyName); //DutyName

                            xlsWorkSheet.cells().item(row,4).value(privName);  //Privilege AOT Name

                            xlsWorkSheet.cells().item(row,5).value(securityTask.Name);  //Privilege Description

                            row++;

                        }

                    }

                }

                else             //Role if contain Privilegs directly

                {

                        xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id

                        xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name

                        xlsWorkSheet.cells().item(row,3).value(""); //DutyName

                        xlsWorkSheet.cells().item(row,4).value(securityTask.AotName);  //Privilege AOT Name

                        xlsWorkSheet.cells().item(row,5).value(securityTask.Name);  //Privilege Description

                }

            }

        }

    }

    if(WinApi::fileExists(fileName))

        WinApi::deleteFile(fileName);

    xlsWorkbook.saveAs(fileName);

    xlsApplication.visible(true);

    info(strFmt("File has been exported"));

}

Application Object Server (AOS) Services WSDL URI accessible

Performing a service status check for prerequisite 'Application Object Server (AOS) Services WSDL URI accessible'. - Check failed.

When I click on the "Error"link I get the message:

"Resolution: Attempt to access the WSDL port manually by entering the URI http://<SERVER>:/DynamicsAx/Services/MetadataService. ..."

It seems that the port (8101) is missing in the URI the prerequisite checker is trying to access.

If I enter the URI http://<SERVER>:8101/DynamicsAx/Services/MetadataService in the browser it works without any problem.

Solution:-

The rootcause of this issue is, you must have installed .NET Business Connector to resolve some 32/64 bit InstallPath issue.

and The Default Client configuration in this server points to blank WSDL port.

To fix this issue:

1.Open Client configuration.

2.Create a new Configuration for AX and BusinessConnector. Ensure the WSDL port is set to the correct value [Default : 8101].

3.Perform Refresh Configuration to generate WCF Settings.

4.Exit the setup and run it again

How to send email using email template by Outlook in AX 2012

static void AD_SendEmailOutlook(Args _args)
{
    SysEmailId           emailId;
    DialogField          dlgEmailId;
    CustTable            custTable;
    LanguageId           languageId = 'EN-IN';
    #define.CurrentVersion(1)
    #localmacro.CurrentList
        emailId
    #endmacro
    SendEmail           sendEmail;
    SysInetMail         mail;
    str subject()
    {
        return SysEmailMessageTable::find(emailId, languageId).Subject;
    }
    str processMappings(str _message)
    {
        Map mappings;
        ;
        mappings = new Map(Types::String, Types::String);
        mappings.insert('CustomerName', "Anu");
        mappings.insert('CustomerStreet', "Testingg");
        return SysEmailMessage::stringExpand(_message, mappings);
    }
    str message()
    {
        COM                  document;
        COM                  body;
        str                  ret;
        SysEmailMessageTable message;
        #help
        ;
        message = SysEmailMessageTable::find(emailId, languageId);
        ret = processMappings(message.Mail);
        //ret = WebLet::weblets2Html4Help(ret, '');
        document = new COM(#HTMLDocumentClassName);
        SysEmailTable::insertHTML2Document(document, ret);
        body = document.body();
        if (!body)
        {
            return '';
        }
        return body.outerText();
    }

    ;
    //sendEmail = new SendEmail();
    emailId = "Test";
    mail = new SysInetMail();
    mail.parmForceSendDialog(true);
    mail.sendMail("support@****.com",subject(), message(),false);
    info("sent");
}

Send Email through code using System.Net.Mail in ax 2012

public void sendEmailNew()
{
    str                                   sender    = 'it@****.com';  // mail id of the sender
  martina.merola@obagservice.com';  //mulitple recipients can be specified
    str                                   recipient = '***@****.com; ****@****.com; ****@****.com';
    str                                   cc          = '****@****.com; ****@****.com';  // mulitple cc id's can be specified
    str                                   subject   = '**** Sales Data';
    str                                   body      = 'Hello **** Team,\n\r Kindly find the attached file as **** Sales Data with this mail. \n\r NOTE: - This is a system generated email. \n\r Regards, \n\r **** IT Team';
    str                                   fileName; // = @'C:\email attachment\****SalesData 20200211.csv';  //Location of the attachment file

    List                                                       toList;
    List                                                       ccList;
    ListEnumerator                                     enumList;
    Set                                                        permissionSet;
    System.Exception                                  exception;

    str                                                          mailServer;
    int                                                          mailServerPort;
    System.Net.Mail.SmtpClient                  mailClient;
    System.Net.Mail.MailMessage               mailMessage;
    System.Net.Mail.MailAddress                mailFrom;
    System.Net.Mail.MailAddress                mailTo;
    System.Net.Mail.MailAddressCollection mailToCollection;
    System.Net.Mail.MailAddressCollection mailCCCollection;
    System.Net.Mail.AttachmentCollection   mailAttachementCollection;
    System.Net.Mail.Attachment                  mailAttachment;
    ;

    fileName    = @'C:\email attachment\****SalesData '+date2str(fromDate,321,
                                                    DateDay::Digits2,
                                                    DateSeparator::None,
                                                    DateMonth::Digits2,
                                                    DateSeparator::None,
                                                    DateYear::Digits4)
                                                    //+"to"+date2str(toDate,321,
                                                    //DateDay::Digits2,
                                                    //DateSeparator::None,
                                                    //DateMonth::Digits2,
                                                    //DateSeparator::None,
                                                    //DateYear::Digits4)
                                                    +'.csv';
 
    try
    {
        toList = strSplit(recipient, ';');
        ccList = strSplit(cc, ';');
   
        permissionSet = new Set(Types::Class);
        permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
        permissionSet.add(new FileIOPermission(filename, 'test1'));
        CodeAccessPermission::assertMultiple(permissionSet);

        mailServer         = SysEmaiLParameters::find(false).SMTPRelayServerName;
        mailServerPort  = SysEmaiLParameters::find(false).SMTPPortNumber;
        mailClient          = new System.Net.Mail.SmtpClient(mailServer, mailServerPort);

        enumList = toList.getEnumerator();
        enumList.moveNext();
   
        mailFrom      = new System.Net.Mail.MailAddress(sender);
        mailTo          = new System.Net.Mail.MailAddress(strLTrim(strRTrim(enumList.current())));
        mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);
   
        mailToCollection = mailMessage.get_To();
        while (enumList.moveNext())
        {
            mailToCollection.Add(strLTrim(strRTrim(enumList.current())));
        }
   
        enumList                 = ccList.getEnumerator();
        mailCCCollection    = mailMessage.get_CC();
        while (enumList.moveNext())
        {
            mailCCCollection.Add(strLTrim(strRTrim(enumList.current())));
        }
   
        mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
        mailMessage.set_Subject(subject);
        mailMessage.set_Body(body);

        mailAttachementCollection   = mailMessage.get_Attachments();
        mailAttachment              = new System.Net.Mail.Attachment(fileName);
        mailAttachementCollection.Add(mailAttachment);

        mailClient.Send(mailMessage);
        mailMessage.Dispose();

        CodeAccessPermission::revertAssert();

        info("Email sent successfully");
    }
    catch (Exception::CLRError)
    {
        exception = ClrInterop::getLastException();
        while (exception)
        {
            info(exception.get_Message());
            exception = exception.get_InnerException();
        }
        CodeAccessPermission::revertAssert();
    }
}

Clear multiple open statements using X++ in ax 2012

static void ClearStatement(Args _args)
{
    RetailStatementTable    statementTable;

    while select forUpdate statementTable
        order by calculatedDate desc
            where (statementTable.storeId == '092')
                //&& statementTable.statementId > '091-000311'//mkDate(20,12,2018)
    {
        statementTable.unmarkTransactions();
        statementTable.unmarkPosBatches();

        ttsBegin;
        statementTable.recalculate = NoYes::No;
        statementTable.calculatedDate = dateNull();
        statementTable.CalculatedLines = 0;
        statementTable.calculatedTime = 0;
        //statementTable.stmtCalcInfoLog = "";
        //statementTable.stmtPostInfoLog = "";

        statementTable.doUpdate();
        ttsCommit;
    }
    info("Done");
}

How to Unregister or deregister all items in purchase order using X++ (Via Code) in ax 2012

Hi Guys,

I have registered PO lines using Item arrival journal.

Need to unregister more that 500 lines. below are lines of code to unregister item: -

static void testUnregistration(Args _args)

{

    PurchTable purchTable;

    InventTrans inventTrans;

    InventTransOrigin inventTransOrigin;

    PurchLine   purchLine;

    InventDim inventDim;

    TmpInventTransWMS tmpInventTransWMS;

    InventTransWMS_register inventTransWMS_register = inventTransWMS_register::newStandard(tmpInventTransWMS);

    while select purchLine where purchLine.PurchId == this.PurchId

    {

        select inventTransOrigin

        where inventTransOrigin.ReferenceId == purchLine.PurchId

        && inventTransOrigin.ItemId == purchLine.ItemId;

        while select inventTrans

        where inventTrans.InventTransOrigin == inventTransOrigin.RecId

        {

            select inventDim where inventDim.inventDimId == inventTrans.inventDimId;

            if (inventTrans.StatusReceipt == StatusReceipt::Registered)

            {

                inventTransWMS_register.createFromInventTrans(inventTrans, inventDim);

                inventTransWMS_register.updateInvent(purchLine);

            }

        }

    }

}

POWER BI - Changing datasource from Import to Direct Query

Hi all

I recently wanted to change the datasource from Import to Live Query using the "Power BI service" and had a good lookaround without turning up much other than "you can not"...

However I managed to get it going, effectively maintaining my designs/visuals and changing datasource so that I can get away with publishing one report whenever my data changes.

Prerequisites:

* TARGET:  A .pbix file with designs/visuals using import, no measures.

* SOURCE: A clean .pbix file using Live Query to a report/dataset published to app.powerbi.com (no designs/visuals)

   -This .pbix file must have the same table as the TARGET file (ie: using the same db)

Steps:

1. Rename .pbix files to .zip

2. Do this [edited steps]:

        => DELETE "Datamodel" from TARGET

        => COPY "Datamashup" from SOURCE to TARGET, overwrite.

        [ =>Not necessary to copy "Connections" based on latest test, but it does not seem to hurt]

        (I used Total Commander for this as it was simplest and it supports archive files)

3. Rename TARGET file back to .pbix

4. Open TARGET file in Power BI

5. The design/visuals and the Old table structure should be visible under "Fields"

6. A yellow warning reading "There are pending changes in your queries that haven't been applied  [Apply changes]"

    may or may not be displayed

7. Click [Apply changes] if it appears

8. All designs/visuals should get an (X) and the message "Something's wrong..."

9. Now go to "Get Data" and select your datasource (i wanted to use data from Power BI services, and a previously published

    reports dataset. Select your datasource and Load it.

10. The designs/visuals should get the proper data filled inn (connection in model checks out) assuming that the previously

      published report/dataset holds the same table (same name etc). If Not all visuals are correct, the proper measures may not be present in the datasource, re-create them there and open the TARGET pbix again to check.

11. PROSPER!!

If you in your TARGET file which is relying on datasource import has created measures and such, these should probably be re-created in the published report/dataset prior to following the steps above (untested).

Note: If you are unable to open the .pbix file in PowerBI after renaming it back to .zip, there might be an issue with your zip program. For my part it failed when using windows inbuildt functionality but worked when using Total Commander to copy files between the .zip files...

Hope this is of value and help.