Wednesday, 3 February 2016

Posted by at No comments:
Select Statements


                            CrossCompany
static void CON_SelectStatement(Args _args)
{
    CustTable      custTable;
    container      companies = ['USMF','IBM'];
    while select crossCompany:companies * from custTable
    {
        info(custTable.AccountNum);
       
    }
   
}
                                                firstFast 
static void CON_SelectfirstFast(Args _args)
{
    CustTable       custTable;
   
    while select firstFast  custTable
    info(custTable.AccountNum);
}
                                                firstOnly
static void CON_SelectfirstOnly(Args _args)
{
    CustTable       custTable;
   
    while select firstOnly custTable
    {
        info(strFmt("%1--%2",custTable.AccountNum,custTable.dataAreaId));
    }
}
                                                forUpdate
static void CON_SelectforUpdate(Args _args)
{
   Con_Calc     con_Calc;
   
    ttsBegin;
    while select forUpdate con_Calc where con_Calc.Name=="Satya"
    {
        con_Calc.Name="Satya CH";
        con_Calc.update();
        info(con_Calc.Name);
    }
    ttsCommit;
   
}
static void CON_Selectwhere(Args _args)
{
    CustTable       custTable;
   while select * from custTable where custTable.CustGroup=="10"
    {
        info(strFmt("%1--%2",custTable.CustGroup,custTable.AccountNum));
    }
}
                                                Groupby
static void CON_SelectGroupby(Args _args)
{
    CustTable       custTable;
    CustTrans       custTrans;
   
    while select minof(AmountMST) from custTrans group by AccountNum
    {
    info(strFmt("%1--%2",custTrans.AccountNum,custTrans.AmountMST));
    }
}
                                                count
static void CON_Selectcount(Args _args)
{
    CustTable       custTable;
    CustTrans       custTrans;
    int             a=0;
    while select count(RecId) from custTable
    {
       a = custTable.RecId;
    }
    info(strFmt("%1",a));
}
                                change Company
static void CON_SelectchangeCompany(Args _args)
{
    CustTable       custTable;
    info(custTable.dataAreaId);
    changeCompany('IBM')
    {
        custTable = null;
        while select custTable
        {
            info(strFmt('%1',custTable.dataAreaId));
        }
    }
}
                                Relational Operator
static void CON_Select(Args _args)
{
   CustTable       custTable;
   CustTrans       custTrans;
    int             a=0;
  while select custTable where custTable.AccountNum=="DE_005" && custTable.AccountNum=="DE_006"
    {
        info(strFmt("%1",custTable.AccountNum));
    }
}
                               
static void CON_Select(Args _args)
{
   CustTable       custTable;
   CustTrans       custTrans;
    int             a=0;
  while select custTable where custTable.AccountNum =="DE_005" || custTable.CustGroup =="10"
    {
        info(strFmt("%1--%2",custTable.AccountNum,custTable.CustGroup));
    }
}
                                              Joins
static void CON_SelectJoin(Args _args)
{
   CON_Car      car;
   CON_RentCar  rent;
    // Inner Join
  /*  while select car join rent where car.CarID == rent.CarID
    {
        info(strFmt("%1--%2", car.CarID,rent.CarID));
    }
   */
   //Outer Join
  /*  while select car outer join rent where car.CarID == rent.CarID
    {
        info(strFmt("%1--%2", car.CarID,rent.CarID));
    }
  */
  //Exists
   /* while select car exists join rent where car.CarID == rent.CarID
    {
        info(strFmt("%1--%2", car.CarID,rent.CarID));
    }
   */
  
   //Not Exists
    while select car notExists join rent where car.CarID == rent.CarID
    {
        info(strFmt("%1--%2", car.CarID,rent.CarID));
    }
 
}
Posted by at No comments:

Dynamic Queries

 

To Select All Customers from Custtable
static void CON_DynamicQuery(Args _args)
{
    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbd;
    CustTable               custTable;
   
    q   = new Query();
    qbd = q.addDataSource(tableNum(custTable));
    qbd.addSortField(fieldNum(CustTable,AccountNum),SortOrder::Ascending);
    qr = new QueryRun(q);
    while(qr.next())
    {
      custTable = qr.get(tableNum(custTable));
      info(strFmt("%1",custTable.AccountNum));
       
    }
   
}
   To Select Customers Between Range
static void CON_DynamicQuery(Args _args)
{
    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    CustTable               custTable;
   
    q       = new Query();
    qbds    = q.addDataSource(tableNum(custTable));
    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);  // order by
    qbr     = qbds.addRange(fieldNum(custTable,AccountNum)); // For all customers
    qbr.value(queryRange('US-001','US-009')); // for particular range
  
    qr = new QueryRun(q);
    while(qr.next())
    {
        custTable = qr.get(tableNum(custTable));
        info(custTable.AccountNum);
       
    }
}
 
Where Condition

static void Job34(Args _args)
{
    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    CustTable               custTable;
   
    q   = new Query();
    qbds = q.addDataSource(tableNum(custTable));
    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);
    qbds.addSortField(fieldNum(custTable,CustGroup),SortOrder::Descending);
    qbr = qbds.addRange(fieldnum(custTable,CustGroup));
    qbr.value(queryRange('10','30'));
   
    qr  = new QueryRun(q);
    while(qr.next())
    {
        custTable   = qr.get(tableNum(custTable));
        info(strFmt("%1--%2",custTable.CustGroup,custTable.AccountNum));
    }
}
  CrossCompany
 
static void CON_DynamicStatement(Args _args)
{
    CustTable       custTable;
   
    Query                   query;          
    QueryBuildDataSource    qbds;
    QueryRun                qr;
   
    query   = new Query();
    query.allowCrossCompany(true);
   // query.addCompanyRange('USMF');
   // query.addCompanyRange('IBM');
    qbds  = query.addDataSource(tableNum(custTable));
    qr    = new QueryRun(query);
    while(qr.next())
    {
      
            custTable = qr.get(tableNum(custTable));
            info(strFmt('%1-%2',custTable.AccountNum,custTable.dataAreaId));
    
    }
   
}
 
Aggregate Function -- min,max,sum,count,computedcolumn,database
 
static void CON_Dynamic(Args _args)
{
    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    VendTable               vendTable;
    CustTable               custTable;
    CustTrans               custTrans;
   
    q = new Query();
    qbds = q.addDataSource(tableNum(custTrans));
    qbds.addSelectionField(fieldNum(custTrans,AmountMST),Selectionfield::Min);
    qbds.addSelectionField(fieldNum(custTrans,RecId),SelectionField::Count);
   
    qr      = new QueryRun(q);
    while(qr.next())
    {
        custTrans = qr.get(tableNum(custTrans));
        info(strFmt("%1",custTrans.AmountMST));
    }
   
}
                              
  First only,first fast
static void CON_Dynamic(Args _args)
{
    int                     i;
    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    VendTable               vendTable;
    CustTable               custTable;
    CustTrans               custTrans;
   
    q   = new Query();
    qbds= q.addDataSource(tableNum(custTable));
    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);
    qbds.firstOnly(true);  //firstonly --firstfast
    qr = new QueryRun(q);
  
    while(qr.next())
    {
        custTable = qr.get(tableNum(custTable));
        info(strFmt("%1",custTable.AccountNum));
    }
}
 
    forUPdate
 
static void CON_DynamicforUpdate(Args _args)
{
    int                     i;
    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    VendTable               vendTable;
    CustTable               custTable;
    CustTrans               custTrans;
    Con_Calc                obj;
    q   = new Query();
    qbds= q.addDataSource(tableNum(Con_Calc));
    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);
    //qbds.firstFast(true);
    qbds.addSortField(fieldNum(Con_Calc,Name));
    qr = new QueryRun(q);
    while(qr.next())
    {
        obj = qr.get(tableNum(Con_Calc));
        obj.selectForUpdate(true);
        ttsBegin;
        if(obj.Name =="Siva")
        {
            obj.Name="Sivakumar";
            obj.update();
        }
        ttsCommit;
        info(strFmt("%1",obj.Name));
    }
}
   GroupBy
 
static void CON_DynamicGroupBy(Args _args)
{
    Query           q;
    QueryRun        qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    CustTable               custTable;
    CustTrans               custTrans;
   
    q = new Query();
    qbds = q.addDataSource(tableNum(custTrans));
    //qbds.addSortField(fieldNum(custTable,AccountNum));
    qbds.addSelectionField(fieldNum(custTrans, AmountMST),SelectionField::Max);         //Aggregate
    qbds.addGroupByField(fieldNum(custTrans,AccountNum),OrderMode::GroupBy);     //group by
      
    qr = new QueryRun(q);
  
    while(qr.next())
    {
        custTrans = qr.get(tableNum(custTrans));
        info(strFmt("%1--%2",custTrans.AccountNum,custTrans.AmountMST));
    }
   
}
 
     Joins
 
static void CON_DynamicJoin(Args _args)
{
    Query                   q;
    QueryRun                qr;
    QueryBuildDataSource    qbdsCustTable,qbdsCustTrans;
    QueryBuildRange         qbr;
    CustTable               custTable;
    CustTrans               custTrans;
    CON_Car                 car;
    CON_RentCar             rent;
    q   = new Query();
    qbdsCustTable = q.addDataSource(tableNum(CON_Car));                     //Data Source 1
    //qbdsCustTable.addSortField(fieldNum(CON_Car,CarId),SortOrder::Descending);
    qbdsCustTrans = qbdsCustTable.addDataSource(tableNum(CON_RentCar));    //Data Source 2
    qbdsCustTrans.relations(false);
    qbdsCustTrans.joinMode(JoinMode::InnerJoin);                          //Join Type
    qbdsCustTrans.addLink(fieldNum(CON_Car,CarId),fieldNum(CON_RentCar,CarId));
   
    qr = new QueryRun(q); 
    while(qr.next())
    {
      
        car = qr.get(tableNum(CON_Car));
        rent = qr.get(tableNum(CON_RentCar));
        info(strFmt("%1--%2",car.CarId,rent.CarId));
    }
}
 Full Text Index
 
static void CON_FullTextIndex(Args _args)
{
    Query           q;
    QueryRun        qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    Con_Calc                obj;
    q       = new Query();
    qbds    = q.addDataSource(tableNum(Con_Calc));
    qbr     = qbds.addRange(fieldNum(Con_Calc,Name));
    qbr.rangeType(QueryRangeType::FullText);
    qbr.value("Raj kumar");
    qr      = new QueryRun(q);
  
    while(qr.next())
    {
        obj = qr.get(tableNum(Con_Calc));
        info(obj.Name);
    }
}
 
  Multiple Tables Join
 
static void CON_DyamicQUeries(Args _args)
{
    Query                   query = new Query();
    QueryBuildDataSource    salesTableDS;
    QueryBuildDataSource    salesLineDS;
    QueryBuildDataSource    custTableDS;
    QueryRun                qr;
    CustTable               custTable;
    SalesTable              salesTable;
    SalesLine               salesLine;
  
    salesTableDS    = query.addDataSource(tableNum(SalesTable));
   
    salesLineDS     = salesTableDS.addDataSource(tableNum(SalesLine));
    salesLineDS.relations(true);
    //salesLineDS.fetchMode(QueryFetchMode::One2One);
    salesLineDS.joinMode(JoinMode::InnerJoin);
    salesLineDS.addLink(fieldNum(SalesTable, SalesId), fieldNum(SalesLine, SalesId));
    custTableDS     = salesTableDS.addDataSource(tableNum(CustTable));   
    custTableDS.relations(false);   
    custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));
    //custTableDS.fetchMode(QueryFetchMode::One2One);
    custTableDS.joinMode(JoinMode::InnerJoin);
    //info(salesTableDS.toString());.
    qr = new QueryRun(query);
    while(qr.next())
    {
        salesTable= qr.get(tableNum(salesTable));
        salesLine = qr.get(tableNum(salesLine));
        CustTable = qr.get(tableNum(custTable));
   
        info(strFmt("%1---%2---%3",salesTable.SalesId,salesLine.SalesId,custTable.AccountNum));
    }

}

Posted by at No comments:
Functions in Ax 2012
static void CON_functions(Args _args)
{
    int             i;
    real            r;
    str             s;
    container       con;
    date            d;
   // AifDocumentCreateAttribute
    //i = 1<<4;
    //i = 1>>4;
    //i = (100>101)?1:5;
    //i = abs(10*20.69544);    abs        Retrieves the absolute value of a real number.
    // i = acos(180);          acos       Retrieves the arc cosine of a real number.
    // i = 42373;
    //mydate = any2date(i);                                     any2date
    //s = 1;
    //st = any2enum(s);                                         any2enum
   // x = any2int(s);                                           any2int
   // x = any2int64(s);                                         any2int64
   // x = any2real(s);                                          any2real
   // x = any2str(s);                                           any2str
   // i = asin(1.2);                                            asin
   // i = atan(1/0);                                            atan
   // x = attributeStr(AifDocumentCreateAttribute);             attributeStr
   // beep();                                                   beep
   // x = char2num("RaJendra",1);                               char2num
   // x = classIdGet(obj);                                      classIdGet
   // x = classStr(Global);                                     classStr
                            //Container
    //con = ["Rajendra","chilukuri","410",9440078438];
    //info(strfmt("%1",conPeek(con,5)));                        conPeek
    //con = conPoke(con,2,"Chilukuri's");
    //info(strfmt("%1",conPeek(con,2)));                        conPoke
    //s = conLen(con);
    //info(strfmt("%1",s));                                     conLen
    //con = conIns(con,5,"Hello How are You ??????");
    //info(strFmt("%1",conPeek(con,5)));                        conIns
    //info(strFmt("%1",conFind(con,"410")));                    conFind
    //con = conNull();
    //info(strFmt("%1",conPeek(con,1)));                        conNull
    //con = conDel(con,1,1);
    //info(strFmt("%1" ,conPeek(con,1)));                      // conDel
    //i = corrflagset(0.369,2);
    //i = cos(900);             cos         Retrieves the cosine of a real number.
    //i = cosh(0);              cosh        Argument values outside of the range –250 to 250 result in the following run-time error: "Argument for trigonometric function out of range."
    //i = cTerm(10,100,50);     cTerm       Calculates the number of periods required for the current investment value to yield a target value.
    //x = curext();             curext      Retrieves the extension that is used for the current company.
    //x = curUserId();          curUserId   Retrieves the nonnumeric ID that represents the current user.
    //x = datasetStr(CustomerList);  datasetStr           Retrieves the name of a dataset as a string.
    //d = today();
   // s = date2num(d);          date2num    Converts a date to an integer that corresponds to the number of days since 1 January, 1900.
   // date2Str                  Converts the specified date to a string.
    //utcDateTime         utc2 = 1959-06-17T15:44:33;
    //x = datetime2str(utc2);
    //formStr                   Retrieves the name of a form.
    // i = frac(1.3658);        Retrieves the decimal part of a real number.
    //info(strFmt("%1",funcName())); //Retrieves a string that contains the current function context.  o/p con_functions
    //setPrefix("Hai");
    //setPrefix("Hello");           Retrieves the current execution prefix after successive calls to the setPrefix function.
    //setPrefix("Namaskaram");
    //setPrefix("Whole Ax Team");
    //info(getPrefix());
    //date refDate = str2Date("4/9/2007", 213);
    //date inputDate = str2Date("10/5/2007", 213);
    //int numberOfIntervals;
    //;
    //numberOfIntervals = intvMax(inputDate, refDate, intvScale::YearMonth);
    //info(strFmt("%1",numberOfIntervals));
    //i = logn(100);            Retrieves the natural logarithm of the specified real number.
    //s = match("<abc","abcdef");       Searches for a string or expression within another string.
    //s = min(12,15,19);
    //d = maxDate();            Retrieves the maximum value allowed for a variable of type date.
    //i   = maxInt();
    //x = methodStr(Calculator,exponent);   Validates that the specified method exists in the specified class; if not, a compiler error occurs.
    //d = mkDate(11,1,2016);      Creates a date based on three integers, which indicate the day, month, and year, respectively.
    //str x1 =mthName(1);           Retrieves the name of the specified month
    //s = mthOfYr(today());
    //d = prevyr(today());
    //x = num2char(67);             Converts an integer to the corresponding ASCII character.
    //d = num2date(364);             // Retrieves the date that corresponds to the specified number of days after 01\01\1900.
    //s = power(5,2);
    //i = round(15,4.00);
    //info(strFmt("%1",sessionId()));
    //int seconds = 10;
    //int i1;
    //i = sleep(60);
    //d = str2Date("31/02/2010",213);
    //s = strAlpha("?a*bc123.");        Copies only the alphanumeric characters from a string.
    //i = strCmp("Raj","Raj");
    //strColSeq                         Converts all uppercase characters to lowercase characters and converts all characters with accents to the corresponding unaccented lowercase characters.
    //info(strFmt("%1", strDel("abcdef",2,3)));  Creates a copy of a string with the specified substring removed.
    //info(strFmt("%1",strKeep("Hellohainamastehi","hai"))); returns str2 values based on str1
    //info(strFmt("%1",strRep("Rajendra ",3)));   Repeats a string of characters.
    //info(strFmt("%1",timeNow()));             The number of seconds that have elapsed since midnight.
    //r = trunc(3.12);
    //print strFmt("r = %1",  r);               Truncates a real number by removing any decimal places.
    //pause;
    //info(strFmt("%1",year(today())));         Retrieves the year from a date value.
    info(strFmt("%1",yearDiff(today(),today()-7)));
}

Posted by at No comments:
Tables & Classes involved in Purchase  Order

PurchTableType and PurchTableLine classes will get called while creating the PurchaseOrders.
PurchFormLetter_PurchOrder
PurchFormLetter_ApproveJournal
PurchFormLetter_Invoice
PurchFormLetter_PackingSlip
PurchFormLetter_ReceiptsList
classes will be used to post the PurchaseOrder at various document status (packing, invoice etc).
Tables:
PurchTable contains all purchase order headers regardless whether they have been posted or not.
PurchParmTable and PurchParmLine contains detailed information regarding posting Purchase headers and Lines.
VendPackingSlipJour and VendPackingSlipTrans tables contains posted packingslip headers and lines.
VendInvoiceJour and VendInvoiceTrans tables contains all invoiced purchase order headers and Lines.
VendReceiptsJour and VendReceiptsTrans tables contains posted receipt header and lines.
VendPurchOrderJour and VendPurchOrderTrans tables contains Purchase requisition headers and lines.


Posted by at No comments:
Tables & Classes involved in Sales Order


SalesTableType and SaleslineType classes will get called while creating the SalesOrders.
SalesFormLetter_Confirm
SalesFormLetter_Invoice
SalesFormLetter_PackingSlip
SalesFormLetter_PickingLlst
classes will be used to post the sales order at various document status (packing, invoice etc).
Tables:
SalesTable contains all SalesOrder headers regardless whether they have been posted or not.
The SalesParmTable and SalesParmLine contains detailed information regarding posting sales headers and Lines.
CustConfirmJour and CustConfirmTrans tables contains all Sales Confirmation headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.
CustPackingSlipJour and CustPackingSlipTrans tables contains all sales PackingSlip headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.
CustInvoiceJour and CustInvoiceTrans tables contains all sales all Sales Invoice headers and Lines posted in Dynamic Ax originating from Sales Orders and Lines.
Posted by at No comments:
Subscribe to: Posts (Atom)