Creating Payment Journal in AR using X++

class CICledgerJournalDetails
{
    LedgerJournalTransTaxExtensionIN    ledgerJournalTransTaxExtensionIN;
    LedgerJournalTable                  ledgerJournalTable;
    LedgerJournalTrans                  ledgerJournalTrans;
    SalesParameters                     salesParameters;
    ledgerParameters                    ledgerParameters;
    LedgerJournalName                   ledgerJournalName;
    NumberSeq                           numberSeq;
    NumberSequenceTable                 numSeqTable,numberSequenceTable;
    container                           ledgerDimension,offsetDimension;
    RecId                               offsetledgerrecid;
    DimensionDisplayValue               dimensionDisplayValue;
    CICLedgerJournalTableExt            ledgerJournalTableExt;
    CICLedgerJournalTransExt            ledgerJournalTransExt;
    int64                               ledgerDim, OffsetledgerDim,DefDimension,LedgerOffsetAccountValue;
    Name                                LedgerAccountValue,LedgerOffsetAcc;
    RefRecId                            refRecId;
    CICCreditCommittal                  creditCommittalDetails;
    CICGBPDC                            gBPDCDetails;
    CICPettyCashRequisition             cicPettycshReq;
    LedgerJournalACType                 LedgerJournalACTypepetty;
    CICPettyCashReplenishment           pettyCashRep;
    CICPettyCashClaim                   pettyCashCla;
    CICPettyCashAutoGLLog               postingLogTable;
    LedgerJournalCheckPost              ledgerJournalValidate, ledgerJournalPost;
    int                                 counter;
    NumberSeq                           numberSeqDetails;
    purchParameters                     purchParameters;
    CICInternalRequisitionTable         internalRequisitionLocal;
    CustPaymModeTable                   custPaymModeTable;
    CompanyInfo                         companyInfo;
    CustParameters                      custParameters;
    #define.checkInvoiceAmount("Please check the invoice amount.")
}

---------------------------------------------------------------------------------------------------------------------

public RefRecId GBPDCCreateJournal(CICGBPDC gBPDC)
{
    ttsBegin;
    if (counter == 0 )
    {
        select firstOnly JournalName from SalesParameters;
        ledgerJournalTable.JournalName = SalesParameters.JournalName;
        ledgerJournalTable.initFromLedgerJournalName(ledgerJournalTable.JournalName);
        ledgerJournalTable.insert();

        select firstOnly LedgerJournalTable, RecId from ledgerJournalTableExt
            where ledgerJournalTableExt.LedgerJournalTable == ledgerJournalTable.RecId;
        if(!ledgerJournalTableExt.RecId)
        {
            ledgerJournalTableExt.LedgerJournalTable = ledgerJournalTable.RecId;
            ledgerJournalTableExt.insert();
            refRecId = ledgerJournalTableExt.recid;
        }
    }
    counter++;
    select firstonly NumberSequenceTable, JournalName from ledgerJournalName
        where ledgerJournalName.JournalName == ledgerJournalTable.JournalName
    join NumberSequence from numberSequenceTable
        where numberSequenceTable.RecId == ledgerJournalName.NumberSequenceTable;

    numberSeq = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence);
    ledgerJournalTrans.JournalNum               = ledgerJournalTable.JournalNum;
    ledgerJournalTrans.voucher                  = numberseq.voucher();
    ledgerJournalTrans.initValue();
    LedgerJournaltrans.initFromLedgerJournalTable_LT(ledgerJournalTable);
    DefDimension = this.DefaultDimension(gBPDC.Divisions,gBPDC.ProfitCenter,"");
    ledgerJournalTrans.Txt                      = gBPDC.Text;
    ledgerJournalTrans.TransDate                = gBPDC.CommittalDate;
    LedgerJournalTrans.MarkedInvoice            = gBPDC.Invoice;
    ledgerJournalTrans.AmountCurCredit          = gBPDC.Amount;
    ledgerJournalTrans.CurrencyCode             = CompanyInfo::standardCurrency();
    ledgerJournalTrans.Company                  = curext();
    LedgerJournalTrans.AcknowledgementDate      = systemDateGet();
    ledgerJournalTrans.PaymMode                 = gBPDC.PaymMode;
    ledgerJournalTrans.PaymReference            = gBPDC.PaymReference;
    LedgerJournalTrans.CICDrawerName            = gBPDC.DrawerName;
    LedgerJournalTrans.CICDrawersBank           = gBPDC.DrawersBank;
    ledgerJournalTrans.CICCommissionPayoff      = gBPDC.CommissionPayoff;
    ledgerJournalTrans.Approved                 = NoYes::Yes;
    ledgerJournalTrans.Approver                 = HcmWorker::userId2Worker(curUserId());
    ledgerJournalTrans.Due                      = ledgerJournalTrans.TransDate;
    changecompany(ledgerJournalTrans.Company)
    {
        select firstOnly PostingProfile from custParameters
            index Key
            where custParameters.Key == 0;
        ledgerJournalTrans.PostingProfile           = custParameters.PostingProfile;//CustParameters::findByCompany(ledgerJournalTrans.Company).PostingProfile;
    }
    ledgerJournalTrans.AccountType              = LedgerJournalACType::Cust;
    LedgerAccountValue                          = gBPDC.Customer;
    ledgerJournalTrans.TransactionType          = LedgerTransType::Payment;
    if(LedgerAccountValue)
    {
        if(ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)
        {
            ledgerDim                           = DimensionStorage::getDynamicAccount(LedgerAccountValue, ledgerJournalTrans.AccountType);
            LedgerJournalTrans.LedgerDimension  = DimensionAttributeValueCombination::find(ledgerDim).Recid;
            LedgerJournalTrans.DefaultDimension = DefDimension;
        }
    }

    select firstonly OffsetAccountType, JournalName from ledgerJournalName
        where ledgerJournalName.JournalName == ledgerJournalTable.JournalName;

    ledgerJournalTrans.OffsetAccountType = ledgerJournalName.OffsetAccountType;



    select firstonly PaymMode, InterCompanyLedgerDimension from custPaymModeTable
        where custPaymModeTable.PaymMode == gBPDC.PaymMode;

    LedgerOffsetAccountValue                    = custPaymModeTable.InterCompanyLedgerDimension;
    LedgerJournalTrans.OffsetDefaultDimension   = DefDimension;

    if(LedgerOffsetAccountValue)
    {
        if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
        {
            OffsetledgerDim                          = DimensionDefaultingService::serviceCreateLedgerDimension(LedgerOffsetAccountValue, DefDimension);
            LedgerJournalTrans.OffsetLedgerDimension = OffsetledgerDim;
        }
        else
        {
            LedgerJournalTrans.OffsetLedgerDimension = LedgerOffsetAccountValue;
        }
    }

    ledgerJournalTrans.LineNum  = LedgerJournalTrans::lastLineNum(ledgerJournalTrans.JournalNum) + 1;
    ledgerJournalTrans.ExchRate = Currency::exchRate(ledgerJournalTrans.CurrencyCode, ledgerJournalTrans.TransDate);
    ledgerJournalTrans.insert();

    ledgerJournalTransTaxExtensionIN.LedgerJournalTrans = LedgerJournalTrans.RecId;
    ledgerJournalTransTaxExtensionIN.TaxInformation_IN  = TaxInformation_IN::findDefaultbyLocation(CompanyInfo::findByCompany_IN().PrimaryAddressLocation).RecId;
    ledgerJournalTransTaxExtensionIN.insert();

    select firstOnly ledgerJournalTrans, GBPDCId, AccountTypeId, RecId from ledgerJournalTransExt
        where ledgerJournalTransExt.ledgerJournalTrans == ledgerJournalTrans.RecId;
    if(!ledgerJournalTransExt.RecId)
    {
        ledgerJournalTransExt.GBPDCId            = gBPDC.GBPDCId;
        ledgerJournalTransExt.AccountTypeId      = gBPDC.AccountTypeId;
        ledgerJournalTransExt.ledgerJournalTrans = ledgerJournalTrans.RecId;
        ledgerJournalTransExt.insert();
    }
    select firstOnly forupdate recid, JournalNo from gBPDCDetails
        where gBPDCDetails.recid == gBPDC.RecId;
    if (gBPDCDetails.RecId)
    {
        gBPDCDetails.JournalNo = ledgerJournalTable.JournalNum;
        gBPDCDetails.update();
    }
    this.GBPDCcustomerSettlement(gBPDC);
    ttsCommit;

    return refRecId;
}

------------------------------------------------------------------------------------------------------------------------

public void GBPDCCustomerSettlement(CICGBPDC _gBPDC)
{
    custvendopentransmanager            manager;
    CustTransOpen                       CustTransOpen;
    CustTrans                           CustTransExt;
    ExchangeRateHelper                  exchangeRateHelper;
    AmountCur                           totalSettlement;

    select firstOnly AccountNum, RefRecId, AmountCur from CustTransopen
        where CustTransOpen.AccountNum  == _gBPDC.Customer
    join Invoice, RecId, AccountNum from CustTransExt
        where CustTransExt.Invoice      == _gBPDC.Invoice
        && CustTransExt.RecId           == CustTransopen.RefRecId
        && CustTransExt.AccountNum      == CustTransOpen.AccountNum;

    if(CustTransopen && CustTransOpen.AmountCur >= _gBPDC.Amount)
    {
        manager = custvendopentransmanager::construct(ledgerJournalTrans);
        manager.updateTransMarked(CustTransOpen,true);

        select firstonly RecId from companyInfo
            where companyInfo.DataArea == ledgerJournalTrans.Company;
        exchangeRateHelper  =  ExchangeRateHelper::newCurrency(Ledger::primaryLedger(companyInfo.RecId),ledgerJournalTrans.CurrencyCode);

        totalSettlement    = SpecTransManager::getTotalSettleAmountForSpecReference(
                                ledgerJournalTrans.Company,
                                ledgerJournalTrans.TableId,
                                ledgerJournalTrans.RecId,
                                ledgerJournalTrans.CurrencyCode,
                                ledgerJournalTrans.Company,
                                ledgerJournalTrans.TransDate,
                                exchangeRateHelper.prepareExchangeRateForStorage(ledgerJournalTrans.crossrate()));

        //To update in ledgerJournal trans
        ttsBegin;
        ledgerJournalTrans.selectForUpdate(true);
        ledgerJournalTrans.AmountCurCredit   = _gBPDC.Amount <= abs(totalSettlement) ? _gBPDC.Amount : 0;
        ledgerJournalTrans.SettleVoucher = SettlementType::SelectedTransact;
        ledgerJournalTrans.MarkedInvoice = _gBPDC.Invoice;
        ledgerJournalTrans.doupdate();
        ttsCommit;
    }
    else
    {
        checkFailed(#checkInvoiceAmount);
    }
}

Dynamic Query in X++

// The first step is to define the query variables that we will be using. This is building the framework.
    Query                                q;
    QueryRun                         qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange             qbr;
    PurchTable                        purchTable;

// Instantiates or creates the query framework.
    q       = new Query();

// Links the table to the data source definition.
    qbds    = q.addDataSource(tableNum(PurchTable));

//Set the Range and Range value
    qbr     = qbds.addRange(fieldNum(PurchTable, PurchId));
    qbr.value('GB-PO-000040');

//Instantiate the query
    qr      = new QueryRun(q);
 
//Query will display a query form for the user. The user can then add their own number sequences or sorting preferences. This is ideal to create much more dynamic queries for form information or reporting.

    if(qr.prompt())
    {

//The while loops over the Table in search of the information needed.
        while(qr.next())
        {
            purchTable = qr.get(tableNum(PurchTable));
            info(strfmt("%1 - %2",PurchTable.PurchId,PurchTable.PurchName));
        }
    }

SysQuery functions

Instead of direct assignment of the values to the query build range value, we can use SysQuery class methods to avoid the errors of datatype conversion. So here in this post I gave some useful methods.

QueryBuildRange qbr;

---To set the value we usually follow as below mentioned code

qbr.value(“Our Value”);

qbr.value(queryValue(“Our Value”));

--We can set the value using SysQuery methods like mentioned below

qbr.value(SysQuery::value(“Our Value”));

qbr.value(SysQuery::valueLike(“Our Value”));

qbr.value(SysQuery::valueNot(“Our Value”));

---For logical NOT

qbr.value(SysQuery::valueNot(“Our Value”));

---To retrive all records

in usual way we can give like empty (” “) but using sysquery class

qbr.value(SysQuery::valueUnlimited());

---To retrive Null records

qbr.value(SysQuery::valueEmptyString());

---To retrive Not Null records

qbr.value(SysQuery::valueNotEmptyString());

---To give range

usually we add qbr.value(fromDate,toDate)  but using sysQuery

qbr.value(SysQuery::range(fromDate,toDate);

The method will add the dots (‘..’)  like this

---To find total number of records available in the resulting query

info(strFmt(“%1”,SysQuery::countTotal(queryRun)));

---To find total number of Datasource available in the resulting query

info(strFmt(“%1”,SysQuery::countLoops(queryRun)));

--- For Datasource and Range creation

SysQuery::findOrCreateRange(parameter);

SysQuery::findOrCreateDataSource(parameter);

SysQuery::findOrCreateDataSourceByName(parameter);

How to select Financial Dimensions using the Default dimension value in X++?

dimStorage                  = DimensionAttributeValueSetStorage::find(custtrans.defaultdimension);

        for(i=1 ; i<= dimStorage.elements() ; i++)
        {
            if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == #Product)
            {
                ProductUnit = dimStorage.getDisplayValueByIndex(i);

                select Value,Description from dimensionFinancialTag where dimensionFinancialTag.Value == ProductUnit;
                Product = dimensionFinancialTag.Description;
            }
            if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == #ProfitCenter)
            {
                ProfitcenterUnit = dimStorage.getDisplayValueByIndex(i);

                select Value,Description from dimensionFinancialTag where dimensionFinancialTag.Value == ProfitcenterUnit;
                branchname = dimensionFinancialTag.Description;
            }
        }

How to select MainAccount using LedgerDimension in X++

With the help of getMainAccountFromLedgerDimension Method in DimensionAttributeValueCombination Table we can fetch the main account.


DimensionAttributeValueCombination::getMainAccountFromLedgerDimension(generalJournalAccountEntry.LedgerDimension);

Understanding Replacement key, Alternate key,Surrogate key,

WHAT IS A SURROGATE KEY?
In AX 2012, a surrogate key is the auto-generated primary key for a table in the database. The surrogate key is linked to the already existing RecId field within any table. This means that the surrogate key is a unique 64-bit integer value that is mandatory for the table and cannot be changed or have duplicates. The fact that it is a 64-bit integer (int64) value means table operations normally perform faster than other types of field such as string fields. This is the main strength of surrogate keys.

WHAT IS A REPLACEMENTKEY INDEX?
While a surrogate key is great for lookup and database performance, it is not useful for the end user because it gives no indication of the table’s purpose, or what related tables it is linked to. For this reason AX 2012 has added the ‘Replacement Key’ index property for tables. The replacement key index is a dropdown of alternate keys that have been specified for the table. There can be any number of alternate keys for a table but only a single replacement key. More than one field can be specified under a replacement key, and it is these fields that will be displayed to the end user on a form instead of the surrogate key field.

A replacement key is an alternate key that the system can display on forms instead of a meaningless numeric primary key value. Each table can have a maximum of one replacement key.


The replacement key is chosen by setting the ReplacementKey property on the table. The drop-down list offers every alternate key as an available value.


Steps while creating a replacement key 

1.Create a index with with properties AllowDuplicates to No and AlternateKey to Yes

2.Assign Primary index as surrogate key and Recplacement key as Index which we created.

3.Create a Primary key based foreign key relation on the Child Table.

4. A primary key based reference will be created on the Table and it reflects a RecId of the primary table.

5.Add a reference group column on the Form and assign the reference field to it.

6.It reflects the Field or shows the drop down with the fields present in the Index which is assigned a replacement key on the primary table. 

WHAT IS AN ALTERNATE KEY?
A table can have any number of alternate keys. An alternate key may be a natural key or a single field primary key used in foreign or primary key relations with other tables. In either case, to set one, the user must create a new index and then set AllowDuplicates to “No” and AlternateKey to “Yes”. If AllowDuplicates is not set to “No” then AlternateKey should be greyed out and uneditable

Difference between Research and Execute Query

Research
Calling research() will rerun the existing form query against the database, therefore updating the list with new/removed records as well as updating all existing rows. This will honor any existing filters and sorting on the form, that were set by the user.
Research(true)

The research method starting with AX 2009 accepts an optional boolean argument _retainPosition. If you call research(true), the cursor position in the grid will be preserved after the data has been refreshed. This is an extremely useful addition, which solves most of the problems with cursor positioning (findRecord method is the alternative, but this method is very slow).

ExecuteQuery
Calling executeQuery() will also rerun the query and update/add/delete the rows in the grid. The difference in behavior from research is described below.

ExecuteQuery should be used if you have modified the query in your code and need to refresh the form to display the data based on the updated query.

formDataSource.queryRun().query() vs formDataSource.query()

An important thing to mention here is that the form has 2 instances of the query object -

• one is the original datasource query (stored in formDataSource.query()), 
• and the other is the currently used query with any user filters applied (stored in formDataSource.queryRun().query()). 

When the research method is called, a new instance of the queryRun is created, using the formDataSource.queryRun().query() as the basis. Therefore, if the user has set up some filters on the displayed data, those will be preserved.

This is useful, for example, when multiple users work with a certain form, each user has his own filters set up for displaying only relevant data, and rows get inserted into the underlying table externally (for example, through AIF).

Calling executeQuery, on the other hand, will use the original query as the basis, therefore removing any user filters.

Source: http://kashperuk.blogspot.com/2010/03/tutorial-reread-refresh-research.html