Query in AX 2012

Runtime query in AX 2012


// Code using X++ to build the query
Query                   query;
QueryRun                queryRun;
QueryBuildDataSource    qbds;
SalesTable              salesTable;
;

query    = new Query();
// Add a datasource to the query
qbds     = query.addDataSource(tableNum(SalesTable));
// Add a range to the newly added datasource.
qbds.addRange(fieldNum(SalesTable,SalesId)).value("00403_1036..00412_1036");
 
queryRun = new QueryRun(query);
 
while(queryRun.next())
{
   salesTable  =   queryRun.get(tableNum(SalesTable));
   info(SalesTable.SalesId + ", " + SalesTable.CustAccount);
}


// Code using a query string
static void Job14(Args _args)
{
    Query                           query;
    QueryRun                        queryRun;
    QueryBuildDataSource            qbds;
    QueryBuildRange                 qbr;   
    EcoResDistinctProductVariant    ecoResDistinctProductVariant; 
 
    query = new query(queryStr(EcoResProductVariantsPerCompany));
    queryRun = new QueryRun(query);
 
    while (queryRun.next())
    {
        ecoResDistinctProductVariant = queryRun.get(tableNum(ecoResDistinctProductVariant));
     
        info (strFmt("%1 - %2", EcoResDistinctProductVariant.SearchName, EcoResDistinctProductVariant.ProductMaster));
    } 
}


http://kashperuk.blogspot.com/2011/10/tutorial-ax2012-new-way-of-accessing.html

Calculate purchase/sales confirmation GST tax at line level (IGST/CGST/SGST) in AX 2012 R3

Hi guys,
It's very tricky to calculate GST percent at line level on purchase/sales confirmation because there is no direct table to fetch the GST percentage and value.

Here I am going to show you how we can calculate GST % at the line level.

In Data provider class creates a new method and this method calculate GST % and value

//#method1
private void getMeasures(ITaxDocument taxDocumentObject,RefRecId _PurchLinerecId)
{
    ITaxDocumentLineEnumerator          lineEnumerator;
    ITaxDocumentLine                    lineObject;
    ITaxableDocumentLine                taxableDocumentLine;
    ITaxDocumentComponentLineEnumerator componentLineEnumerator;
    ITaxDocumentComponentLine           componentLineObject;
    ITaxDocumentMeasureEnumerator       measureEnumerator;
    ITaxDocumentMeasure                 measureObject;

    LedgerVoucher                   ledgerVoucher;
    ITaxDocumentLine                taxDocumentHeaderLineObject;
    PurchLine_IN                    PurchLine_IN1;

    #TaxEngineModelLineMeasures


    if (taxDocumentObject)
    {
        lineEnumerator = taxDocumentObject.lines();
        while (lineEnumerator.moveNext())
        {
            lineObject = lineEnumerator.current();
            if (taxDocumentHeaderLineObject == null && lineObject.metaData().isHeaderLine())
            {
                taxDocumentHeaderLineObject = lineObject;
            }
            taxableDocumentLine = TaxableDocumentLineObject::construct(lineObject.sourceTableId(), lineObject.sourceRecId());
            componentLineEnumerator= lineObject.componentLines();

            if(lineObject.originSourceTableId() == 340 && lineObject.originSourceRecId() == _PurchLinerecId)
            {
                    //info(strFmt("%1-%2",lineObject.originSourceRecId(),lineObject.originSourceTableId()));
                    totval=0;
                    while (componentLineEnumerator.moveNext())
                    {
                        componentLineObject = componentLineEnumerator.current();
                        measureEnumerator = componentLineObject.measures();
                        while (measureEnumerator.moveNext())
                        {
                            measureObject = measureEnumerator.current();
                            if(measureObject.metaData().name() == strFmt("Rate") && componentLineObject.metaData().taxType() == "GST"
                              &&
                              (componentLineObject.metaData().taxComponent() == "CGST" ||
                              componentLineObject.metaData().taxComponent() == "IGST" ||
                              componentLineObject.metaData().taxComponent() == "SGST")
                              )
                            {
                                totval += measureObject.value().value();
                                //info(strFmt("%1--%2--%3",componentLineObject.metaData().taxComponent(),measureObject.metaData().name(),measureObject.value().value()));

                            }
                        }
                       // getTotal = totval*100;
                       // info(strFmt("%1",getTotal));
                    }
                    getTotal = totval*100;
                    //info(strFmt("%1",getTotal));
            }
        }
    }
}
___________________________

//#method2
Create another method in DP class with recid as a parameter and we will call earlier created getMeasures method in this class

private void getMeasuresforPurchLine(RefRecId _PurchLinerecId)
{
    ClassName                   bundlerClassName;
    TaxableDocumentDescriptor   bundler;
    ClassId                     bundlerId;
    ITaxableDocument            taxableDocumentObject;
    ITaxableDocumentLine        taxableDocumentLineObject;
    Common                      transactionTable =  PurchTable::find(PurchLine::findRecId(_PurchLinerecId).PurchId) ;  //SalesTable::find('F898-000626');
    SalesPurchJournalLine_IN    transactionLineTable;
    TaxValue                    gstTaxValue;
    ITaxDocument                taxDocumentObject;
    ITaxableDocument            taxableDocumentObjectLoc;
    TaxableDocumentDescriptor   descriptor;
    TaxDocumentProxy            taxDocProxy;
    ITaxDocumentLineEnumerator  taxDocLineEnumerator;
    ITaxDocumentLine            taxDocLine;
    SalesLineAmount             salesLineAmount,salesLineBaseAmt;
    ITaxDocumentComponentLineEnumerator cLineEnum;
    ITaxDocumentComponentLine           cLine;
    //str                         taxComponent;

    bundlerClassName = 'TaxableDocDescriptorPurchaseOrder';
    bundlerId = className2Id(bundlerClassName);

    if (SysDictTable::isTableMapped(tableNum(SalesPurchJournalLine_IN), transactionTable.TableId)
        && transactionTable.TableId != tableNum(LedgerJournalTrans))
    {
        transactionLineTable = transactionTable;
        transactionTable = transactionLineTable.SalesPurchJournalLine_IN::salesPurchJournalTable();
    }

    if (bundlerId != 0)
    {
        bundler = TaxableDocumentDescriptor::getTaxDocumentdescriptorServer(bundlerId, transactionTable);

        if (!bundler.skipTaxDocument())
        {
            taxableDocumentObject = TaxableDocumentObject::constructServer(bundler);
            taxDocumentObject = TaxBusinessService::recalculateTax(taxableDocumentObject, false);
        }

        if (taxDocumentObject)
        {
            // calling the above method
            this.getMeasures(taxDocumentObject,_PurchLinerecId);
        }
    }
}
____________________________

//#method3
Here we will create a new method getGSTpercent and call getMeasuresforPurchLine to get the punchline records.

private void getGSTPercent(PurchLineAllVersions     _purchLineAllVersions)
{
    PurchTable          PurchTableLoc;
    PurchLine           PurchLineLoc;
    VendPurchOrderJour  vendPurchOrderJourLoc;
    PurchLineAllVersions    purchLineAllVersions;

    select vendPurchOrderJour
        where vendPurchOrderJour.PurchId == purchid;

        select PurchLine
            where PurchLine.InventTransId == _purchLineAllVersions.InventTransId;

        this.getMeasuresforPurchLine(purchline.RecId);
}

With this above logic, we can calculate GST percent and value at line level.


Happy DAxing:-)

Get financial dimension by Recid.

Get financial dimension by Recid.

public static container getFinancialDimensionsByRecId(RecId     _defaultDimension)
{
    DimensionAttributeValueSetStorage    dimStorage;
    Counter                              i;
    container                            ctr;
    ;

    dimStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);

    for (i=1 ; i<= dimStorage.elements() ; i++)
    {
        ctr   += [DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name, dimStorage.getDisplayValueByIndex(i)];
    }

    return ctr;
}