To get Customers total amount Transaction using X++ Code

static void CustOpenTrans(Args _args)
{
     CustTrans custtrans;
    CustTable custtbl;
    while select custtbl
    {
    select sum(AmountMST) from custtrans where custtrans.AccountNum == custtbl.AccountNum;
        info(strFmt("Customer - %1  Amount - %2",custtbl.AccountNum,custtrans.AmountMST));
    } }

How to Get Workflow Instance Number Based on Journal Number in Ax 2009

static void JournalNumBase_InstanceNum(Args _args)
{
    ledgerjournaltable          ledgerjournaltable;
    workflowtrackingtable       workflowtrackingtable;
    workflowtrackingstatustable workflowtrackingstatustable;
    Recid           recid;
    //CorrelationId   CorrelationId;
    guid            CorrelationId;

    select  Recid from ledgerjournaltable where ledgerjournaltable.JournalNum=="JN-000247" ;
    recid = ledgerjournaltable.RecId;

    select  workflowtrackingtable where workflowtrackingtable.ContextRecId == recid;
    CorrelationId = workflowtrackingtable.CorrelationId;

    select  workflowtrackingstatustable where workflowtrackingstatustable.CorrelationId ==CorrelationId;

    info(strfmt("Instance id %1 ",workflowtrackingstatustable.InstanceNumber));


}

List of items that have been sold on a sales order id Using Code in Ax 2012

class SalesListItems
{
    QueryRun queryRun;
}

Methods
static void main(Args _args)
{
    SalesListItems salesListItems = new SalesListItems();
    salesListItems.run();

}

private void run()
{
    this.buildQuery();
    if (queryRun.prompt())
    this.listItems();
}

private void listItems()
{
    SalesLine salesLine;
    while (queryRun.next())
    {
        salesLine = queryRun.get(tableNum(SalesLine));
        info(strFmt("%1 %2",salesLine.ItemId,salesLine.itemName()));
    }
}

private void buildQuery()
{
    Query query;
    QueryBuildDataSource dataSource;
    query = new query();

    dataSource = query.addDataSource(tableNum(SalesLine));
    query.addQueryFilter(dataSource, fieldStr(SalesLine,SalesId));
    queryRun = new QueryRun(query);
}

X++ Code for Sales order Invoiced which are Created through Sales Quotation Confirmed

static void Raj_SQTConfirmSTInvoiced(Args _args)
{
    SalesQuotationTable sqt;
    SalesQuotationLine  sql;
    SalesTable          st;
    ;
   while select QuotationId,salesidref,quotationstatus from sqt  join sql
        where sqt.QuotationId == sql.QuotationId
         && sqt.QuotationStatus == SalesQuotationStatus::Confirmed
         join st where sqt.SalesIdRef == st.SalesId
          && st.SalesStatus == SalesStatus::Invoiced
    {
        info(strFmt("%1 is quotationid confirmed,%2 is salesid invoiced",sqt.QuotationId, st.SalesId));
    }
}

Dynamic Query with Prompt in Ax 2012

static void Job23(Args _args)
{
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildRange queryBuildRange;
    QueryRun queryRun;
    Query    query;
    Qty total;
    InventTrans inventTrans;
    inventDim inventDim;
    query = new Query();
   
    queryBuildDataSource = query.addDataSource(tableNum(InventTrans));
    queryBuildDataSource.addSelectionField(fieldNum(InventTrans,Qty),SelectionField::Sum);
    queryBuildRange      = queryBuildDataSource.addRange(fieldNum(InventTrans,ItemId));
    queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(InventDim));
    queryBuildDataSource.addGroupByField(fieldNum(InventDim,InventBatchId));
    queryBuildDataSource.relations(true);
    queryRun = new QueryRun(query);
   
    if (queryRun.prompt())
    {
        while (queryRun.next())
        {
            inventTrans = queryRun.get(tableNum(InventTrans));
            inventDim   = queryRun.get(tableNum(inventDim));
            info(strFmt("Batch %1, qty %2",inventDim.inventBatchId, inventTrans.qty));
        }
    }
}

How to Calculate total sales order or sales quotation amount / discounts / tax etc., through code in Ax 2012

static void Raj_TotalSalesCalculate(Args _args)
{
    SalesTotals   salesTotals;

    SalesTable   salesTable;
    container   displayFields;
    str    totalTax, amountWithoutTax, amountInclTax,exchrate;

    salesTable = salesTable::find('001271');
    salesTotals  =  SalesTotals::construct(salesTable, salesUpdate::All);
    salesTotals.calc();
    displayFields =  salesTotals.displayFieldsCurrency(salesTotals.currencyCode());

    exchrate         = conPeek(displayFields,TradeTotals::posExchRate());
    amountWithoutTax  = conpeek(displayFields, TradeTotals::posBalance());
    amountInclTax   = conpeek(displayFields, TradeTotals::posTotalAmount());
    totalTax    = conpeek(displayFields,TradeTotals::posTaxTotal());
 
    info(strFmt("%1--%2--%3",amountWithoutTax,amountInclTax,totalTax));

}

Withholding tax for purchase order

display real withholdTax()
{
    PurchTable      purchTable;
    PurchTotals     _purchTotals;
   
   
    purchTable  = PurchTable::findRecId(callingTable.RecId);
    _purchTotals = PurchTotals::newPurchTable(purchTable);
    _purchTotals.calc();
    return  _purchTotals.taxWithhold_IN().taxAmountCurTotal();
 

}

ODBC Connection using Inbound and outbound in Ax 2012

First we have to Establish ODBC Connection

• Open Search > Administrative Tools in Ur PC
• Select ODBC 64 or ODBC 32
• A Prompt Will Appear as

• Click on Add

• Create New Data Base 
• Check Newly Created  Data Base are added to ODBC or not

                              Inbound
static void ODBCCONNECTION_Inbound(Args _args)
{
    LoginProperty                       loginproperty;
    OdbcConnection                      odbcconnection;
    Statement                           statement;
    ResultSet                           resultset;
    str                                 sql,criteria;
    SqlStatementExecutePermission       perm;
    Custtable                           custtable;
    ODBCTABLE                           odbctable;

    ;
    loginproperty = new loginproperty();
    loginproperty.setDSN("ODBCconnection");
    loginproperty.setDatabase("DataBaseName");

    odbcconnection = new odbcconnection(loginproperty);

    if(odbcconnection)
    {
        sql = "select * from [DataBaseName].[dbo].[ODBCCUSTTABLE]";// where custgroup<=20";
        perm= new SqlStatementExecutePermission(sql);
        perm.assert();

        statement = odbcconnection.createStatement();
        resultset = statement.executeQuery(sql);

        while(resultset.next())
        {
            odbctable.AccountNum= resultset.getstring(1);
            odbctable.CustGroup= resultset.getString(2);
            odbctable.Amount   = resultset.getstring(3);
            odbctable.insert();
           
        }
        resultset.close();
        statement.close();

       info("Data Imported");
    }
    else
    {
        error("Failed to logon to the Database through ODBC");
    }
}

                                                 Outbound
static void ODBCCONNECTION_Outbound(Args _args)
{

    LoginProperty                       loginproperty;
    OdbcConnection                      odbcconnection;
    Statement                           statement;
    ResultSet                           resultset;
    Notes                               sql,values;
    SqlStatementExecutePermission       perm;
    ODBCTABLE                           odbctable;
    ;

 
    //Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setServer("ServerName");
    loginProperty.setDatabase("DataBaseName");

     try
     {
            odbcConnection = new OdbcConnection(loginProperty);
            if(odbcConnection)
            {
                       select ODBCTABLE;
                  sql = "USE ODBCConnection";
                  sql = "INSERT INTO ODBCCUSTTABLE";
                  sql +="\n"+"  (AccountNum,CustGroup,Amount)";
                 // sql +="\nVALUES("+odbctable.accountNum+','+odbctable.CustGroup+','+odbctable.Amount+");";
                //sql+= "select (AccountNum,CustGroup,Amount) from odbctable")";
                sql += "\n"+"select AccountNum,CustGroup,Amount from ODBCTABLE;" ;
               
                    perm = new SqlStatementExecutePermission(sql);
                   perm.assert();
                    statement = odbcConnection.createStatement();
                    statement.executeUpdate(sql);
                    statement.close();
                    //update_recordset odbctable setting
                    //CustGroup = enum2str(NoYes::No)
                    //where odbctable.CustGroup ==enum2str(NoYes::Yes);
                    info("RecordS Inserted successfully.");
             }

     }
     Catch(Exception::Error)
      {
         ttsabort;
         error("Error Occured while Payment Schedule Processing");
      }

}