Fetch the customer details using X++ in AX 2012

By -
Hi Technical,

Today, in this post we are going to share a X++ code to fetch the customer details as customer number, customer name, primary address, country, sales channel, sales rep., region, territories, agent, sales manager in AX 2012.

When you run this X++ code in AX 2012, an excel will generate with all the data.




Output:


To fetch this data create an AX job in AOT and copy paste the below code.

// Project Name: PKA_Adhoc_CustomerDetaills
// Created By: Chirag Gupta
// Created Date: 29-Jan-2020
// Summary: Fetch the customer details as customer number, customer name, primary address,
// country, sales channel, sales rep., region, territories, agent, sales manager.
static void PKA_GetCustomerData(Args _args)
{
    CustTable                                   custTable;
    smmBusRelChainGroup                         smmBusRelChainGroup;
    smmBusRelSegmentGroup                       smmBusRelSegmentGroup;
    smmBusRelSubSegmentGroup                    smmBusRelSubSegmentGroup;
    GOD_AgentMaster_RSA                         agentMaster_RSA;
    Name                                        salesRep, salesManager;
    LogisticsAddressCountryRegionTranslation    countryRegionTranslation;
    UserInfo                                    userInfo;
    LogisticsAddressCountryRegion               countryRegion;
    LogisticsPostalAddress                      postalAddress;
    SysExcelApplication                         xlsApplication;
    SysExcelWorkBooks                           xlsWorkBookCollection;
    SysExcelWorkBook                            xlsWorkBook;
    SysExcelWorkSheets                          xlsWorkSheetCollection;
    SysExcelWorkSheet                           xlsWorkSheet;
    SysExcelRange                               xlsRange;
    int                                         row;

    xlsApplication           = SysExcelApplication::construct();
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
    row                      = 1;

    xlsWorkSheet.cells().item(row, 1).value("Customer Number");
    xlsWorkSheet.cells().item(row, 2).value("Customer Name");
    xlsWorkSheet.cells().item(row, 3).value("Primary Address");
    xlsWorkSheet.cells().item(row, 4).value("Country");
    xlsWorkSheet.cells().item(row, 5).value("Sales Channel");
    xlsWorkSheet.cells().item(row, 6).value("Sales Rep.");
    xlsWorkSheet.cells().item(row, 7).value("Region");
    xlsWorkSheet.cells().item(row, 8).value("Territories");
    xlsWorkSheet.cells().item(row, 9).value("Agent");
    xlsWorkSheet.cells().item(row, 10).value("Sales Manager");

    while select firstOnly100 custTable where custTable.AccountNum == 'C04015'
    {
        row++;
        smmBusRelChainGroup.Description     = smmBusRelChainGroup::find(custTable.CompanyChainId).Description;
        smmBusRelSegmentGroup.Description   = smmBusRelSegmentGroup::find(custTable.SegmentId).Description;
        agentMaster_RSA.Description         = GOD_AgentMaster_RSA::find(custTable.GOD_Agent).Description;
        salesRep                            = HcmWorker::worker2Name(custTable.MainContactWorker);
        salesManager                        = HcmWorker::worker2Name(custTable.PKA_MainContactWorker);
        postalAddress                       = custTable.postalAddress();
        countryRegion                       = LogisticsAddressCountryRegion::find(postalAddress.CountryRegionId);

        select firstonly Language from userInfo where userInfo.Id == curUserId()
            join countryRegionTranslation
                where countryRegionTranslation.CountryRegionId == countryRegion.CountryRegionId &&
                        countryRegionTranslation.LanguageId    == userInfo.Language;

        select firstOnly smmBusRelSubSegmentGroup
            where smmBusRelSubSegmentGroup.SegmentId == custTable.SegmentId
            && smmBusRelSubSegmentGroup.SubsegmentId == custTable.SubsegmentId;

        smmBusRelSubSegmentGroup.SubSegmentDescription = smmBusRelSubSegmentGroup.SubSegmentDescription;

        //info(strFmt("%1 - %2 - %3 - %4 - %5 - %6 - %7 - %8 - %9 - %10",
                    //custTable.AccountNum,
                    //custTable.name(),
                    //postalAddress.Address,
                    //countryRegionTranslation.ShortName,
                    //smmBusRelChainGroup.Description,
                    //salesRep,
                    //smmBusRelSegmentGroup.Description,
                    //smmBusRelSubSegmentGroup.SubSegmentDescription,
                    //agentMaster_RSA.Description,
                    //salesManager
                    //)
            //);

        xlsWorkSheet.cells().item(row, 1).value(custTable.AccountNum);
        xlsWorkSheet.cells().item(row, 2).value(custTable.name());
        xlsWorkSheet.cells().item(row, 3).value(postalAddress.Address);
        xlsWorkSheet.cells().item(row, 4).value(countryRegionTranslation.ShortName);
        xlsWorkSheet.cells().item(row, 5).value(smmBusRelChainGroup.Description);
        xlsWorkSheet.cells().item(row, 6).value(salesRep);
        xlsWorkSheet.cells().item(row, 7).value(smmBusRelSegmentGroup.Description);
        xlsWorkSheet.cells().item(row, 8).value(smmBusRelSubSegmentGroup.SubSegmentDescription);
        xlsWorkSheet.cells().item(row, 9).value(agentMaster_RSA.Description);
        xlsWorkSheet.cells().item(row, 10).value(salesManager);

        info(strFmt("%1 record(s) generated in excel file.", row - 1));
    }
    xlsApplication.visible(true);
}

We hope this solution helped you to fetch customer details using X++ in AX 2012. Please comment your valuable feedback for this article...

 Author:
Chirag Gupta
Microsoft Dynamics D365/AX 2012
Senior Technical Consultant at IBM Bangalore

Date:
3-Apr-2020

Happy Learning !!
Location: Bengaluru, Karnataka, India

Comments

Post a Comment

Popular posts from this blog

[Solved] : Error: String or binary data would be truncated. Cannot execute a data definition language command on Vendor (VendLedgerReconciliationTmp)

By -
Image
Error : Cannot execute a data definition language command on Vendor (VendLedgerReconciliationTmp). Date: . The SQL database has issued an error. SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]String or binary data would be truncated. SQL statement: INSERT INTO tempdb."DBO".t10799_95B4A012AC0F4D298A6CA7C554A0C4B6 (ACCOUNTINGDATE,VOUCHER,VENDAMOUNTMST,POSTINGPROFILE,ARRIVAL,APPROVED,VENDORACCOUNTID,VENDGROUP,VENDORNAME,RECVERSION,PARTITION) SELECT T1.TRANSDATE,T1.VOUCHER,T1.AMOUNTMST,T1.POSTINGPROFILE,T1.ARRIVAL,T1.APPROVED,T2.ACCOUNTNUM,T2.VENDGROUP,T3.NAME,1,5637144576 FROM VENDTRANS T1 CROSS JOIN VENDTABLE T2 CROSS JOIN DIRPARTYTABLE T3 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'rsa')) AND ((((((T1.TRANSDATE>={ts '2019-07-22 00:00:00.000'}) AND (T1.TRANSDATE<={ts '2019-07-22 00:00:00.000'})) AND (T1.VOUCHER>='')) AND (T1.VOUCHER<=N'ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°ï½°')) AND (T1.POSTINGPROFIL...
Read more

AX 2012 - Command prompt compilation / AxBuild.exe for Parallel Compile on AOS of X++ to P-code / Parallel Compilation

By -
Image
Hello AX Technical, In this article, we will talk about parallel compilation with benefit of performance gain. Here, we need to understand that when we do parallel compilation it will take less time than compiling on the client. How to do parallel compilation: 1) Open cmd/command prompt and run as administrator. 2) Copy and paste, cd C:\Program Files\Microsoft Dynamics AX\60\Server\AX2012R2_MPG_LIVE\bin and press enter. Note: cd* = Change drive for the directory where AX server is installed. AX2012R2_MPG_LIVE is my AX server installed directory name, you can choose yours. 3) Now, copy and paste this, axbuild.exe xppcompileall /s=01 /altbin="C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin" Note: Here, s=01 means server instance one, you can have multiple instance for your AX server. You can check this in Microsoft Dynamics AX 2012 Server Configuration tool, it looks like below: 4) Press enter when you want to do compilation. Note : Suppose, y...
Read more

AX 2012 - How to send SSRS report in email as an attachment using X++

By -
Hi Technical, Today, in this post we will share, how you can send SSRS report in email as an attachment using X++. Please find the below code... // Created by: Chirag Gupta // Created date: 17-Sep-19 // Summary: Send SSRS Report in excel format as attachment in email. public void EmailSSRSReport(Args _args) {     SrsReportRunController controller = new SrsReportRunController();     SRSPrintDestinationSettings printSettings;     SrsReportEMailDataContract  emailContract;     // set report name     controller.parmReportName(ssrsReportStr(RMPMBPRReport, Report));     // create email contract     emailContract = new SrsReportEMailDataContract();     // fill in the email contract details     emailContract.parmAttachmentFileFormat(SRSReportFileFormat::Excel);     emailContract.parmSubject("RMPM BPR Report " + strUpr(curext()));     emailContract.parmBody(...
Read more

AX 2012 - How to stop AOS when services got stuck at stopping status

By -
Image
Hello Technical, Today, in this post we are going to share a very important solution when AOS services get stuck in stopping status. You might have seen sometimes when we restarted the AOS services and it get stuck in stopping phase and we need to do server restart but this solution tells you not to stop AOS when services got stuck. Let's talk about the solution... 1. Check AOS services status, if services are in stopping state then please do it like step 2. 2. Open Even Viewer and expand Windows Logs. 3. Click on Application and select Filter Current Log. 4. Filter Event Id by '149' in Filter Current Log form. 5. Select the latest information by Date and Time of event id. 6. Find the PID of AOS which you want to stop. 7. Open Task Manager. Note : *If the “PID” column is not shown, click on View > Select columns > Check the PID > Click “OK” 8.  Select the Ax32Serv.exe which has the same PID number as you found in Step #6. Your process ...
Read more

Error: There was no endpoint listening at net.tcp://your_server_name:8201/Dynamics AX/Services…

By -
Image
Hi Readers, Today, in this post we will share a very important solution for the below error. You may get this error at the time of report deployment in ax 2012 or may be when you are running a SSRS report. Error:  There was no endpoint listening at net.tcp:/your_server_name:8201/Dynamics AX/Services… "A call to the Microsoft Dynamics AX SRSFrameworkService service failed. There was no endpoint listening at net.tcp://############/DynamicsAx/Services/BIServices that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details." Solution: Method 1: 1) Go to System administration > Setup > Services & Application Integration Framework > Inbound ports. 2) Select BIServices port, activate same.  Note:  When you activate the same, you might get some more errors, please see the screen shot below: 3) Go to AOT >> Service Groups >...
Read more

AX 2012 - How to make a form field mandatory

By -
Image
Hello Readers, Some days ago, we had come across a requirement that we need to make a form field mandatory. We are sharing this article to help you to understand how you can achieve the same requirement. Requirement : Make 'document date' mandatory for Invoice Journal Lines form. Solution : 1. Go to Accounts payable/Area page/Journals/Invoices/Invoice Journal, Invoice journal form will open like below... 2. Click on lines in invoice journal form, journal voucher form will open. 3. Right click on document date field and click on personalize. You can see AOT form name to be edit. Click on edit button like below... 4. Expand the designs node of this form and make the document date mandatory after selecting mandatory = yes in the form properties. 5. Now, form document date field has been mandatory, if you try to save the form data without document date it will through an error like "Field 'Document date' must be filled in." ...
Read more

Fetch vendor address using X++ in AX 2012

By -
Image
Hi Technical, Today, in this post we are going to share a piece of code by which you can fetch vendor address using X++ in AX 2012. Create a AOT job and copy paste the below code to export vendor address in excel. // Project Name: PKA_WO67963_FetchCustomerVendorAddress // Created by: Chirag Gupta (ibm.chirag) // Created date: 18-Dec-19 // Summary: Fetch Vendor ID, Name or description, Address, Purpose, Primary static void PKA_VendorAddress(Args _args) {     SysExcelApplication         xlsApplication;     SysExcelWorkBooks           xlsWorkBookCollection;     SysExcelWorkBook            xlsWorkBook;     SysExcelWorkSheets          xlsWorkSheetCollection;     SysExcelWorkSheet           xlsWorkSheet;     SysExcelRange              ...
Read more

[Error] - Error in unit of amount in currency KES. Must be rounded to 0.01 | The unit of 37.563.5343100000000000 KES being posted to account 32710001-- is outside the current penny rounding threshold | Posting has been canceled

By -
Image
Hi Readers, Today, in this article we will share a very known issue of AX, most of the people are still struggling to find out the solution for the below error. We got this error when we were posting Ledger Journal using X++. One of the developers find out the solution for the below error so we are posting the solution here. Error : Error in unit of amount in currency KES. Must be rounded to 0.01. The unit of 37.563.5343100000000000 KES being posted to account 32710001-- is outside the current penny rounding threshold. Posting has been canceled. Solution : 1. Check in your code, how you are inserting Amount for Credit and Debit for Ledger Journal posting. 2. Make Amount field value till two decimals use roundDownDec method. 3. Please see the below code, we have written a AX Job to illustrate. // Code written by Chirag Gupta server static void roundDownDecExample(Args _args) {     real r = 37563.5343100000000000;     real a;   ...
Read more

AX 2012 - How to print current date and time on SSRS Report

By -
Hi Folks, Today, we are here with a new post for printing current date and time on SSRS Report, so you can use below format to print Date, Time and DateTime on your report. To print date: Format(Now, "dd/MM/yyyy") To print time: Format(Now, "hh:mm tt") To print datetime: =Format(Now, "dd/MM/yyyy") & vbCrLf & Format(Now, "hh:mm tt") Author: Chirag Gupta Microsoft Dynamics 365 AX Technical Consultant at IBM Bangalore Date: 3-Sep-2019 Happy Learning !!
Read more

For more information about this error navigate to the report server on the local server machine, or enable remote errors in AX 2012

By -
Image
Hi AX Technical, Today, in this post we will share a very helpful trick to fight with below kind of error. Error : For more information about this error navigate to the report server on the local server machine, or enable remote errors. Here, we see there is no such description of the error. So first we need to enable remote errors in AX 2012. Solution : Please follow the steps below. Method 1: Open SQL Server Management Studio (SSMS) and create a query. Copy, paste the below query to check and update the value. -- SQL Query written by Chirag Gupta -- To Check the value USE ReportServer select * from ConfigurationInfo where name = 'EnableRemoteErrors' -- To Update the value if the value is false USE ReportServer GO UPDATE ConfigurationInfo SET Value = 'True' WHERE Name = 'EnableRemoteErrors' Method 2: Follow below procedure to enable remote error in SSMS for SSRS 1) Open SQL Server Management Studio and log in with ...
Read more