AX2012 Export Ledger Balances to CSV Build Reference field lookup in Batch Dialog

The following sample code is used to build the Reference control in the BatchDialog.
for This Example I created the Query with the DataSource as  MainAccount and using this Query run for this dialog.
This File used to Export the export ledger balances to Excel.
Test considerations:

Export should be tried with date interval covering multiple months.

Test should included account and all dimensions used in account structure and advanced account structure

Balances should be validated -Verify that balances are express in company currency.


Class declaration

class UploadLedgerBalances extends RunBaseBatch
{
    QueryRun                    queryRun;
    DialogField                 dialogFromDate;
    DialogField                 dialogToDate;
    DialogField                 dialogFilename;
    DialogField                 dialogAccountStructureHierarchy;
    // Dialog
    DialogRunbase               dialog;
    DimensionHierarchyId        accountStructureHierarchy;
    #File
    TransDate                   fromDate,toDate;
    Filename                    filename;
    CommaTextIo         commaTextIo;
    FileIOPermission    permission;
    container  dimensionHeader,dimensionValues;

    #DEFINE.CurrentVersion(1)
     #LOCALMACRO.CurrentList
       fromDate,
       toDate,
       filename,
    accountStructureHierarchy
    #ENDMACRO
}


public void new()
{
    super();
    queryRun = new QueryRun(queryStr(OpexUpload));
}



public QueryRun queryRun()
{
    return queryRun;
}



public boolean showQueryValues()
{
    return true;
}


Override the Dialog method

public Object dialog()
{
    dialog = super();
    dialogFromDate  = dialog.addFieldValue(extendedTypeStr(TransDate), fromDate,  "@SYS24050");
    dialogToDate = dialog.addFieldValue(extendedTypeStr(TransDate), toDate,  "@SYS14656");
    dialogFilename = dialog.addFieldValue(extendedTypeStr(FilenameSave), filename, "@SYS16423");


    dialogAccountStructureHierarchy = dialog.addFieldValue(extendedTypeStr(DimensionHierarchyId),this.mainHierarchy() , "Account Structure");
    dialogAccountStructureHierarchy.registerOverrideMethod(methodstr(FormReferenceControl, lookupReference), methodstr(OpexUpload, lookupAccountReference), this);
    dialogAccountStructureHierarchy.registerOverrideMethod(methodstr(FormReferenceControl, resolveReference), methodstr(OpexUpload, resolveAccountReference), this);

    dialog.allowUpdateOnSelectCtrl(true);
    this.dialogSelectCtrl();
    return dialog;
}


public boolean getFromDialog()
{
    fromDate                          = dialogFromDate.value();
    toDate                            = dialogToDate.value();
    filename                          = dialogFilename.value();
    accountStructureHierarchy         = dialogAccountStructureHierarchy.value();
    return super();
}



public Common lookupAccountReference(FormReferenceControl _formReferenceControl)
{
    return DimensionHierarchy::LookupReference(_formReferenceControl);
}


public Common resolveAccountReference(FormReferenceControl _formReferenceControl)
{
    return DimensionHierarchy::ResolveReference(_formReferenceControl);
}




private DimensionHierarchyId mainHierarchy(DimensionHierarchyId _mainHierarchy =  accountStructureHierarchy)
{
    accountStructureHierarchy = _mainHierarchy;
    return  accountStructureHierarchy;
}

// DimensionHierarchy Table I create two methods as following which i used in the dialog class methods
public static Common LookupReference(FormReferenceControl _formReferenceControl)

{
    DimensionHierarchy      selectedRecord,
                            commonHierarchy;
    Query                   lookupQuery;
    QueryBuildDataSource    qbds;
    SysReferenceTableLookup sysTableLookup = SysReferenceTableLookup::newParameters(tableNum(DimensionHierarchy),
                                                                                    _formReferenceControl, true);

    // Do not call super as we're providing our own custom lookup logic.
    // selectedRecord = super(_formReferenceControl);

    // Display the Title and Department fields in the lookup form.
    sysTableLookup.addLookupfield(fieldNum(DimensionHierarchy, Name));
  //  sysTableLookup.addLookupfield(fieldNum(DimensionHierarchy, Description));

    // Create a custom Query that filters and displays just the person titles
    lookupQuery = new Query();
    qbds = lookupQuery.addDataSource(tableNum(DimensionHierarchy));
 //   qbds.addRange(fieldNum(DimensionHierarchy, StructureType)).value(queryValue(DimensionHierarchyType::Focus));
    qbds.addRange(fieldNum(DimensionHierarchy, DeletedVersion)).value(queryValue(0));
    qbds.addRange(fieldNum(DimensionHierarchy, IsSystemGenerated)).value(queryValue(NoYes::No));
    qbds.addRange(fieldNum(DimensionHierarchy, IsDraft)).value(queryValue(NoYes::No));
     // If a common focus is specify, filter to only show values that overlap

        // Add a range on value "0" to ensure that at least one range exists. Without
        // this dummy range at the beginning, if there are no hierarchies that apply
        // then no ranges will be added and the user would see all values by default.
        qbds.addRange(fieldNum(DimensionHierarchy, RecId)).value(queryValue(0));

        while select RecId from commonHierarchy where
            commonHierarchy.IsSystemGenerated == NoYes::No &&
            commonHierarchy.IsDraft           == NoYes::No &&
            commonHierarchy.DeletedVersion == 0
        {
           qbds.addRange(fieldNum(DimensionHierarchy, RecId)).value(queryValue(commonHierarchy.RecId));

        }
    sysTableLookup.parmQuery(lookupQuery);

    selectedRecord = sysTableLookup.performFormLookup();
    return selectedRecord;
}

// This method is used to resolve the reference Id based on the selected Account structure.

public static Common ResolveReference(FormReferenceControl _formReferenceControl)
{
    DimensionHierarchy  resolvedRecord;
    Name                hierarchyName;

    // Do not call super as we're providing our own disambiguation logic.
    // resolvedRecord = super();

    hierarchyName = _formReferenceControl.filterValue(AbsoluteFieldBinding::construct(fieldStr(DimensionHierarchy, Name), tableStr(DimensionHierarchy))).value();

    // Only search hierarchies
    select firstonly resolvedRecord where
        resolvedRecord.Name == hierarchyName &&
        resolvedRecord.IsSystemGenerated == NoYes::No &&
        resolvedRecord.IsDraft           == NoYes::No &&
        resolvedRecord.DeletedVersion == 0;

    return resolvedRecord;
}


/// <summary>
///    Contains the code that does the actual job of the class.
/// </summary>
public void run()
{
    MainAccount mainAccount;
    RefRecId ledgerDimensionRecid;
    DimensionAttributeValueCombination dimCombination;
    try
    {
        dimensionHeader=conNull();
        if (! this.validate())
        {
            return;
        }

        permission = new FileIOPermission(fileName,#io_append);
        permission.assert();
        commaTextIo = new CommaTextIo(fileName,#io_append);
        this.writeCommaIOHeader();
        while (queryRun.next())
        {
            mainAccount = queryRun.get(tableNum(MainAccount));
//If you want to get the Hierarchy based on the Main Account you  can uncomment the below line and used this recid in the while select.
//I am using the input from the user that we build on dialog.below bolded value is the selected from dialog.

          //  dimHierarchyRecid=DimensionHierarchy::getAccountStructure(mainAccount.RecId);
            while select  recid from dimCombination where  dimCombination.MainAccount==mainAccount.RecId
               && dimCombination.AccountStructure==accountStructureHierarchy
               && dimCombination.AccountStructure !=0
            {
                ledgerDimensionRecid=dimCombination.RecId;
                //gets the DimensionValues
                dimensionValues=conNull();
                this.getDimensionValues(ledgerDimensionRecid);

                this.getTransAmount(ledgerDimensionRecid,mainAccount.MainAccountId);
            }
        }
        CodeAccessPermission::revertAssert();
    }
    catch (Exception::Deadlock)
    {
        queryRun.reset();
        retry;
    }
}


// This method is used to get the active dimensions for the current entity -->
void getActiveFinancialDimensions()
{
    DimensionAttributeSetItem   dimAttrSetItem;
    DimensionAttribute          dimAttr;
    DimensionEnumeration        dimensionSetId;

    DimensionHierarchyLevel     dimHierarchyLevel;
    recId                       chartOfAccountsId;
    LedgerChartOfAccountsStructure  ledgerChartOfAccountsStructure;
    ;
    //Get the record Id (dimension set id) for current ledger to find active dimensions
    dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
    chartOfAccountsId = Ledger::find(Ledger::current()).ChartOfAccounts;

    //Find all the active dimensions for current ledger except main account and display them
    while select * from dimAttr
             where dimAttr.Type != DimensionAttributeType::MainAccount
        join RecId from dimAttrSetItem
            where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
                dimAttrSetItem.DimensionAttributeSet == dimensionSetId
        join RecId from dimHierarchyLevel
            order by level
            where dimHierarchyLevel.DimensionAttribute == dimAttr.RecId
         exists join ledgerChartOfAccountsStructure
            where ledgerChartOfAccountsStructure.DimensionHierarchy == dimHierarchyLevel.DimensionHierarchy &&
                  ledgerChartOfAccountsStructure.ChartOfAccounts == chartOfAccountsId
    {
        dimensionHeader +=dimAttr.Name;
    }
}

///  This method is used to get the dimension values for that ledger dimension-->

private void getDimensionValues(RefRecId _dimCombRecid)
{

    DimensionAttributeValueCombination  dimAttrValueComb;
    DimensionStorage        dimensionStorage;
    DimensionStorageSegment segment;
    int                     segmentCount, segmentIndex;
    int                     hierarchyCount, hierarchyIndex;

    SysDim                  segmentValue;
    ;

    dimAttrValueComb = DimensionAttributeValueCombination::find(_dimCombRecid);
    dimensionStorage = DimensionStorage::findById(dimAttrValueComb.RecId);
    if (dimensionStorage == null)
    {
        throw error("@SYS83964");
    }

    hierarchyCount = dimensionStorage.hierarchyCount();
    for(hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)
    {
        segmentCount = dimensionStorage.segmentCountForHierarchy(hierarchyIndex);
        //Loop through segments and display required values
        for (segmentIndex = 2; segmentIndex <= segmentCount; segmentIndex++)
        {
                segment = dimensionStorage.getSegmentForHierarchy(hierarchyIndex, segmentIndex);
                //Get segment value (id of the dimension)
                segmentValue        = segment.parmDisplayValue();
                dimensionValues += segmentValue;
        }
    }
}


///  This method is used to sum of transaction amount for that fiscal period-->

private void getTransAmount(RefRecId  _dimensionRecid,MainAccountNum _mainAccount)
{
    GeneralJournalEntry  generalJournalEntry;
    GeneralJournalAccountEntry generalJournalAccountEntry;
    LedgerDimensionAccount  ledgerDimensionAccount;
    FiscalCalendarPeriod fiscalCalendarPeriod;
    int numofPeriods;
    TransDate fiscalNextPeriodDate;
    AmountCur   VoucherTransAmount;
    Boolean  voucherExits;
    ;
    ledgerDimensionAccount=_dimensionRecid;
    // gets the number of periods between start date and Todate
    numofPeriods=FiscalCalendars::numberOfPeriodsBetweenDates(CompanyInfo::fiscalCalendarRecId(),fromDate,toDate);
    fiscalNextPeriodDate=fromDate;

    while(numofPeriods>0)
    {
fiscalCalendarPeriod=FiscalCalendars::findPeriodByPeriodCodeDate(CompanyInfo::fiscalCalendarRecId(),fiscalNextPeriodDate);
        VoucherTransAmount=0;
        voucherExits=false;
        while select LedgerDimension,TransactionCurrencyAmount,TransactionCurrencyCode,AccountingCurrencyAmount from generalJournalAccountEntry
                where generalJournalAccountEntry.LedgerDimension==ledgerDimensionAccount
        join JournalNumber from generalJournalEntry
            where generalJournalEntry.RecId==generalJournalAccountEntry.GeneralJournalEntry
            && generalJournalEntry.FiscalCalendarPeriod==fiscalCalendarPeriod.RecId
        {
            // write the lines for the Ledger account  for that fiscal period
            if(generalJournalAccountEntry.TransactionCurrencyCode == CompanyInfo::standardCurrency())
            {
                VoucherTransAmount +=generalJournalAccountEntry.TransactionCurrencyAmount;
            }
            else
            {
                VoucherTransAmount += generalJournalAccountEntry.AccountingCurrencyAmount;
            }
            voucherExits=true;
        }
        if(voucherExits)
        {
            this.WriteCommaLines(fiscalCalendarPeriod.EndDate,_mainAccount,VoucherTransAmount);
        }
        fiscalNextPeriodDate=FiscalCalendars::findNextPeriod(CompanyInfo::fiscalCalendarRecId(),fiscalNextPeriodDate);
        numofPeriods=FiscalCalendars::numberOfPeriodsBetweenDates(CompanyInfo::fiscalCalendarRecId(),fiscalNextPeriodDate,toDate);
    }
}


The OutPut of File will look like as below

"Date","Account","Department","Organization","Customer","Item","Project","SAPSUBLedgeraccount","Amount"
"2012/03/31","100020","410","RSDK_OTO","X00179","1-02-51400","00179","105",7.44
"2012/02/29","100030","410","RSDK_OTO","X00179","","","",-22
"2012/03/31","100030","410","RSOTO_DBIZ","X00208","1-02-53200","0100","115",-7.44



When you run this class you can see the Dialog as follows