Sql And X++ Queries

Exists Join Sql Query :

Select CONTAINERID,* From WHSCONTAINERLINE
    Where Exists
     (Select LOADLINE,INVENTTRANSID,LOADID From WHSLOADLINE
          Where WHSLOADLINE.INVENTTRANSID ='10904263'
                  And WHSLOADLINE.RECID = WHSCONTAINERLINE.LOADLINE)
while select containerLine
    where containerLine.ContainerId == _workTable.ContainerId
        exists join loadLine
            where loadLine.InventTransId    == _workInventTrans.InventTransIdParent
                       && loadLine.RecId            == containerLine.LoadLine

Exists Operator

Exists is a logical SQL operator that helps to check the sub-query result, either True or False. It is used to check either a row is returned through this sub-query or not? If one or more rows are returned, then this operator returns True otherwise False when no rows are returned. To check how to use Exists operator in SQL, Click here.

Syntax: Select column_name(s) From table_name Where Exists (Select column_name From table_name Where condition);

Example: Let we have two tables for which we will use Exists operator:

Product Table

Product ID	Product Name	Unit	Price	Supplier ID
1	Chang	24-12 oz bottles	$10	2
2	Chais	48-6 oz jars	$22	1
3	Aniseed Syrup	12-550 ml	$18	14
4	Exotic Liquid	36 boxes	$19	3
5	Gumbo Box	10 boxes	$21.5	15

Supplier Table:

Supplier ID	Supplier Name	City	Postal Code
1	Tokyo Traders	London	100
2	Kelly’s Homestead	Tokyo	48104
3	Cajun Delight	New Orleans	70117
4	Exotic Liquid	Arbor	EC1 4SD

Query:

Select Sr_Name From Supplier Where Exists (Select Pr_Name From Products Where Supplier_ID = Supplier.Supplier_ID And Price < 20);

Here, the SQL statement result is True, and it returns a list of products whose price is less than 20.

========================================================================

Inner Join Sql Query :

select containerLine.*
  from WHSCONTAINERLINE containerLine
 inner join WHSWORKTABLE workTable on workTable.CONTAINERID = containerLine.CONTAINERID
    inner join WHSLOADLINE whsloadline on whsloadline.RECID = containerLine.LOADLINE
inner join WHSWORKINVENTTRANS trans on trans.INVENTTRANSIDPARENT = whsloadline.INVENTTRANSID and trans.WORKID = workTable.WORKID
    where  containerLine.CONTAINERID = '0000156938'
   ======================================================================



print CompanyInfo::current();
=============================================================================================================================
Get days in month ex - jan ( 1- 31 will display )
static void dayOfMthExample(Args _arg)
{
   date d = today();
   date    endDate,startDate;
   int i;
   int start,end;
   int yr;
   ;
   //i = dayOfMth(d);
   yr  =   year(d);
   endDate = endmth(d);
   startDate   =   mkdate(1,mthofyr(d),yr);
   //i = dayofmth(endDate);

   start   =   dayofmth(startDate);
   end     =   dayofmth(endDate);

   //info(strfmt("Today's day of the month is %1" , int2Str(i)));
   //info(strfmt("%1 - %2",start,end));
   for(i=start; i <= end; i++)
   {
       info(strfmt("%1",i));
   }

}
 =======================================================================
 Will Display User – its roles In specific company
static void userRoleTesting(Args _args)
{
   UserInfo t2;
    SecurityRole t3;
    SecurityUserRole t1;
  // while select * from t1 join t2 where t1.user  == t2.id join t3 where  t3.RecId == t1.SecurityRole && t1.USER == curUserId()
    while select User, SecurityRole from t1
        join company from t2 where t2.id == t1.User
        join Name from t3 where t1.SecurityRole == t3.RecId &&
           t1.User == curUserId() &&
           t2.company == curext()
    {
        info(strFmt("%1,%2,%3",t1.User,t1.SecurityRole, t3.Name));
    }
}
====================================================================
How to Get string values separated by commas
 CustInvoiceJour custInvoiceJour,custinvoicejourloc;
    container con;
    int i;
    str multipleRecords;
    multipleRecords = args.parm();
    info(strFmt("%1",multipleRecords));
    con = str2con(multipleRecords);
  
    for(i=1;i<=conLen(con);i++)
    {
        custInvoiceJour.RecId = conpeek(con,i);
         info(strFmt("%1",custInvoiceJour.RecId));
        select custinvoicejourloc where custinvoicejourloc.RecId == custInvoiceJour.RecId;
         info(strFmt("%1",custinvoicejourloc.SalesId));
    }
==========================================================================================================

 
1. How  to use Print  Record statements :

while  select sum(AmountMST), custTable.AccountNum,custTable.Currency,custTable.CustGroup from custtrans join custtable  group by custTable.AccountNum  where custTrans.AccountNum  == custTable.AccountNum
{
       info(strFmt("%1,%2", custTrans.AmountMST, custTrans.AmountMST));

           // projTable = queryRun.get(tableNum(ProjTable));info(strFmt("%1, %2, %3",projTable.ProjId,projTable.Name,projTable.Type));

}

2.  How  to do print  b/w 2 tables :

CustTable custtable;
    CustTrans custtrans;
    CustBalTmp custBalTmp;
    while select AccountNum  from custtable {
      while select sum(AmountMST) from custTrans where custTrans.AccountNum  == custTable.AccountNum

            custBalTmp.AmountMST = custTrans.AmountMST;
            custBalTmp.AccountNum = custTable.AccountNum;
            custBalTmp.Currency = custTable.Currency;
            custBalTmp.CustGroup = custTable.CustGroup;
             custBalTmp.insert();

    }

3.  Use  Group by in Select statement :

while select sum(InvoiceAmountMST) from custinvoicejour  group by custInvoiceJour.OrderAccount where custInvoiceJour.invoicedate == today()

    {

        info(strFmt("%1,%2",custInvoiceJour.OrderAccount, custInvoiceJour.InvoiceAmountMST));

    }

Customer 1101 :  10.00
Customer 1102:  58.00
Customer 1104 :  20.00

while select * from custinvoicejour where custInvoiceJour.invoicedate == today()

    {

        info(strFmt("%1,%2",custInvoiceJour.SalesId, custInvoiceJour.InvoiceAmount));

    }

SO : 101271   Invoice Amount : 10.00
SO : 101273   Invoice Amount : 3.00
SO : 101274   Invoice Amount : 55.00
SO : 101276   Invoice Amount : 20.00

4. How  to call DP class from job :
static void CallDpClass(Args _args)
{MachineBreakDownSlipTmp ACWATransferhistoryTmp;
MachineBreakDownSlip_queryDP dataprovider = new MachineBreakDownSlip_queryDP();
dataProvider.processReport();
NSGFinalSettlementForToledoTmp = dataProvider.getMachineBreakDownSlipTmp();
pause ;} 

static void CopyOfMano(Args _args)
{
    ProdRouteTrans  prodRouteTrans;
    ACWAPIPETotalItemProdPerMonthTmp  acwaPIPETotalItemProdPerMonthTmp;
    SandTestDP   dataprovider = new SandTestDP();
    ACWAPIPETotalItemProdPerMonthContract    contract    = new ACWAPIPETotalItemProdPerMonthContract();
    ;
    contract.parmFromDate(str2Date("07/05/2006",213));
    contract.parmToDate(str2Date("07/05/2006",213));
    contract.parmProdUnitId("2");
    contract.parmResource("301");
    dataProvider.parmDataContract(contract);
    dataProvider.processReport();
    acwaPIPETotalItemProdPerMonthTmp = dataProvider.getACWAPIPETotalItemProdPerMonthTmp();
}
 
5. Add  some days  to date  :
static void AddingMonthsToDOJ(Args _args){   int anInteger;   date aDate;   ;   aDate = 1\1\1998;   anInteger = 90;   aDate = aDate + anInteger;   info(strFmt("PPEndDate = %1",aDate));
}
o/p - PPEndDate = 4/1/1998

6. DateStartMth(today());  -To find  Date start of the month. 

6. calculate  age from DOB:

static void CalculateAgeFromDOB(Args _args)
{
    HcmWorker hcmWorker;
    int fromYear,toYear;
    ;
    hcmWorker = HcmWorker::find(5637144617);
    fromYear = year(hcmWorker.BirthDate);
    toYear   = year(systemDateGet());
    info(strFmt("Age = %1",toYear-fromYear));
}

7. Run  report through job :
static void RunSSRSReport(Args _args)
{
    SrsReportRun srsReportRun;
    SelectedEmployeesClass      selEmpsToSent = new SelectedEmployeesClass();
    ;
    srsReportRun = new SrsReportRun("Payslip.PrecisionDesign1");
    srsReportRun.init();
    srsReportRun.reportCaption("Payslip.PrecisionDesign1");
    srsReportRun.reportParameter('Month').value(2);
    srsReportRun.reportParameter('Year').value(2012);
    srsReportRun.showDialog(false);
    // Print to a file named ReportExample in HTML/PDF format.
    srsReportRun.printDestinationSettings().printMediumType(SRSPrintMediumType::File);
    srsReportRun.printDestinationSettings().fileFormat(SRSReportFileFormat::PDF);
    srsReportRun.printDestinationSettings().overwriteFile(true);
    srsReportRun.printDestinationSettings().fileName(@"C:\InventTruckTransactionReport.pdf");
    if( srsReportRun )
    {
        srsReportRun.executeReport();
    }
}
Run  Menu item of Report :
static void Job17(Args _args)
{
    MenuFunction CustRptMI;
    Args ArgsLoc;
    ;
    CustRptMI = new MenuFunction(menuItemOutputStr(Payslip),MenuItemType::Output);
    ArgsLoc = new Args();
    ArgsLoc.parm("Month=2");
    ArgsLoc.parm("Year=2012");
    ArgsLoc.pack();
    CustRptMI.run(ArgsLoc);
    CustRptMI.wait();
}

8 To create Sales order through code :
static void createSaleorder(Args _args)
{
SalesTable salesTable;
SalesLine salesLine;
NumberSeq NumberSeq;
CustAccount CustAccount = '3012';
ItemId itemId = '1151';
SalesFormLetter salesFormLetter;
SalesFormLetter_Invoice invoice;
;
// Order header (salesTable)
// New order number from number range produce
NumberSeq = NumberSeq::newGetNum(SalesParameters::numRefSalesId() , true);
salesTable.SalesId = NumberSeq.num();
// Initialize the order header
salesTable.initValue();
salesTable.CustAccount = CustAccount;
// Initialization of the supplier-specific ordering data
salesTable.initFromCustTable();
// Create order header
salesTable.insert();
// Order position (PurchLine)
salesLine.clear();
// Assign order number and item number
salesLine.SalesId = salesTable.SalesId ;
salesLine.ItemId = itemId;
salesLine.createLine(NoYes::Yes, // Validate
NoYes::Yes, // initFromSalesTable
NoYes::Yes, // initFromInventTable
NoYes::Yes, // calcInventQty
NoYes::Yes, // searchMarkup
NoYes::Yes); // searchPrice
// Create a new object of the SalesFormLetter_Invoice by using the construct-method in SalesFormLetter
invoice = SalesFormLetter::construct(DocumentStatus::Invoice);
// Post the invoice
invoice.update(salesTable, SystemDateGet(), SalesUpdate::All, AccountOrder::None, false,true);
}

9. To Find  Date DIFF in hours :
static void DateDiffInHours(Args _args)
{
    Activation DateTime ActivationDateTime;
    ACWAPIPEServiceRequestTable ACWAPIPEServiceRequestTableFrom,ACWAPIPEServiceRequestTableTo;
    int HourVal,TimeVal;
    str HourwithTime;
    int64 sdjfsh;
    select ACWAPIPEServiceRequestTableFrom where ACWAPIPEServiceRequestTableFrom.RecId == 5637144835;
    select ACWAPIPEServiceRequestTableTo where ACWAPIPEServiceRequestTableTo.RecId == 5637146080;
    //sdjfsh = DateTimeUtil::getDifference(ACWAPIPEServiceRequestTableTo.createdDateTime,ACWAPIPEServiceRequestTableFrom.createdDateTime)/60;
    sdjfsh = DateTimeUtil::getDifference(ACWAPIPEServiceRequestTableTo.createdDateTime,ACWAPIPEServiceRequestTableFrom.createdDateTime);
    info(strFmt("%1",sdjfsh));
    HourVal = int642int(sdjfsh/60);
    TimeVal = sdjfsh - (HourVal*60);
    HourwithTime = int2str(HourVal) + ":" + int2str(TimeVal);
    info(strFmt("%1",HourwithTime));
  //  info(strFmt("%1",DateTimeUtil::anyToDateTime(ACWAPIPEServiceRequestTableTo.createdDateTime-ACWAPIPEServiceRequestTableFrom.createdDateTime)));
 }
Find  Difference of Dates :
static void Difference of dates(Args _args)
{
    NSGEosTransactions     NSGEosTransactions;
    str date1,date2;
    date todate,fromdate;
     int date3;
    str date4;
    select NSGEosTransactions;
    date1 =date2str(NSGEosTransactions.EmplStartDate(),213,2,4,2,4,4);
    fromdate = str2date(date1,213);
    date2 = date2str(NSGEosTransactions.TransDate,213,2,4,2,4,4);
    todate   = str2date(date2,213);
    date3 =  todate - fromdate;
    print date3;
    info(strFmt(date3));
    pause;   
}
Diff of dates :
static void Diff of dates (Args _args)
{
;
print str2Date("25/12/2012",123) - str2Date("22/11/12",123) + 1;
pause;
}
Add hours to time :
static void addhoursToTime(Args _args)
{
utcDateTime t, t1;
t = DateTimeUtil::utcNow();
t1 = DateTimeutil::addMonths(t,6);
print(t1);
pause;
}
10  Delete file :
static void deleteFile(Args _args)
{
    str fileName;
    ;
    fileName = "c:\\TEMP\\000123_2_2012.pdf";
    WINAPI::deleteFile(fileName);
}

 11. To get Employee TurnOver :
static void EmplTurnover(Args _args)
{
    NSGEmployeesTurnover    NSGEmployeesTurnover;
    int                     month;
    RecId                   joinedthismonth, leftthismonth;
    StartDate               monthStart;
    EndDate                 monthEnd;
    HcmWorker               emplTable;
    NSGEoSTransactions      eosTrans;
    EmplContract            activeContract;
    NSGEmployeesTurnOver    turnoverEmployees, turnoverEmployeesDel;
    SystemSetup             parameters = SystemSetup::find();
    NSGEmployeesTurnoverTmp      employeesTurnoverTmp;
    ;
    NSGEmployeesTurnover::PopulateData();
    //Added by britto
    for(month = 1; month <= 12; month++)
    {
        //Getting the month start and end...
        monthStart      = mkdate(1, month, parameters.CurrentYear);
        monthEnd        = endMth(monthStart);
        //Getting the employees joined in that month based on joining date field...
        select count(RecId) from emplTable
            where emplTable.JoiningDate >= monthStart
                && emplTable.JoiningDate <= monthEnd;
        joinedthismonth = emplTable.RecId;
        //Getting the terminated employees within this month...
        select count(RecId) from eosTrans
            where eosTrans.TransDate >= monthStart
                && eosTrans.TransDate <= monthEnd
                && eosTrans.EosTransType != NSGEoSTransType::Clearance;
        leftthismonth = eosTrans.RecId;
        //Inserting the data...
        employeesTurnoverTmp.MonthsOfYear = month;
        employeesTurnoverTmp.JoinedThisMonth = int642int(joinedthismonth);
        employeesTurnoverTmp.LeftThisMonth = int642int(leftthismonth);
        employeesTurnoverTmp.insert();
    }
}

12.  Get  Hierarchy Structure :
static void getDimensionCombinationValues(Args _args)
{
    // DimensionAttributeValueCombination stores the combinations of dimension values
    // Any tables that uses dimension  combinations for main account and dimensions
    // Has a reference to this table’s recid
    DimensionAttributeValueCombination  dimAttrValueComb;
    //GeneralJournalAccountEntry is one such tables that refrences DimensionAttributeValueCombination
    GeneralJournalAccountEntry          gjAccEntry;
    // Class Dimension storage is used to store and manipulate the values of combination
    DimensionStorage        dimensionStorage;
    // Class DimensionStorageSegment will get specfic segments based on hierarchies
    DimensionStorageSegment segment;
    int                     segmentCount, segmentIndex;
    int                     hierarchyCount, hierarchyIndex;
    str                     segmentName, segmentDescription;
    SysDim                  segmentValue;
    ;
    //Get one record for demo purpose
    gjAccEntry = GeneralJournalAccountEntry::find(5637760032); //5637765403);
    setPrefix("Dimension values fetching");
    //Fetch the Value combination record
    dimAttrValueComb = DimensionAttributeValueCombination::find(gjAccEntry.LedgerDimension);
    setPrefix("Breakup for " + dimAttrValueComb.DisplayValue);
    // Get dimension storage
    dimensionStorage = DimensionStorage::findById(gjAccEntry.LedgerDimension);
    if (dimensionStorage == null)
    {
        throw error("@SYS83964");
    }
    // Get hierarchy count
    hierarchyCount = dimensionStorage.hierarchyCount();
    //Loop through hierarchies to get individual segments
    for(hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)
    {
        setPrefix(strFmt("Hierarchy: %1", DimensionHierarchy::find(dimensionStorage.getHierarchyId(hierarchyIndex)).Name));
        //Get segment count for hierarchy
        segmentCount = dimensionStorage.segmentCountForHierarchy(hierarchyIndex);
        //Loop through segments and display required values
        for (segmentIndex = 1; segmentIndex <= segmentCount; segmentIndex++)
        {
            // Get segment
            segment = dimensionStorage.getSegmentForHierarchy(hierarchyIndex, segmentIndex);
            // Get the segment information
            if (segment.parmDimensionAttributeValueId() != 0)
            {
                // Get segment name
                segmentName = DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId()).DimensionAttribute).Name;
                //Get segment value (id of the dimension)
                segmentValue        = segment.parmDisplayValue();
                //Get segment value name (Description for dimension)
                segmentDescription  = segment.getName();
                info(strFmt("%1: %2, %3", segmentName, segmentValue, segmentDescription));
            }
        }
    }
    /* http://ramdynamicsax.wordpress.com/2012/03/29/creation-and-posting-of-purchase-order-in-ms-dynamics-ax-2012/ */
}
13 Print  Dialog :

boolean myDialog(str FromChequeNum="1000", str NumOfCheque="300")
{
    Dialog dialog = new Dialog("@SYS23133");
    DialogField dialogAccountId = dialog.addField(
        extendedTypeStr(BankAccount));
    DialogField dialogFromChequeNum = dialog.addField(
        extendedTypeStr(BankChequeStartNum),
        "@SYS4083");
    DialogField dialogNumOfCheque = dialog.addField(
        extendedTypeStr(BankChequeQty),
        "@SYS14578");
    ;
    dialogAccountId.Value("456");
    dialogAccountId.Active(false);
    dialogFromChequeNum.Value(FromChequeNum);
    dialogNumOfCheque.Value(NumOfCheque);
    if (dialog.run())
    {
        FromChequeNum = dialogFromChequeNum.Value();
        NumOfCheque = dialogNumOfCheque.Value();
        return true;
    }
    return false;
}

 Join With  Where
static void JOINWhere(Args _args)
{
    EmpPayRoll EmpPayRoll;
    HcmWorker HcmWorker;
    while SELECT * FROM EmpPayRoll
    WHERE ((EmpPayRoll.EmplId == 'DNG9001'))
    && ((EmpPayRoll.Month == 8)) && ((EmpPayRoll.Year == 2012))
    JOIN * FROM HcmWorker
    where EmpPayRoll.EmplId == HcmWorker.PersonnelNumber && ((HcmWorker.NSGSponsership == 0))
    print EmpPayRoll.EmplId;
    pause;
}
15 . Send  Mail :
static void SendMail(Args _args)
{
    SysMailer pccSysMailer;
    Args argsRep = new Args();
    Args argsInv= new Args();
    Reportrun reportInv;
    ReportRun reportRep;
    // Set permissionSet,permissionSet1;
    InteropPermission PCCpermInteropPermission;
    str                                     receiverMailAddress;
    str                                     mailBody;
    str                                     mailSubject;
    str                                     CcMailAddress;
    str  FromMailAdd;
    UserInfo userinfo;
    str     smtpServer;
    int     SMTPPort;
    SysEmailParameters parameters = SysEmailParameters::find();
    ;
    select userinfo where userinfo.id == curUserId();
    FromMailAdd = sysUserInfo::find(curUserId()).Email;     //FromMailAdd = "manokaran@dynamicnetsoft.com";
    receiverMailAddress = "britto@dynamicnetsoft.com";
    PCCpermInteropPermission = new InteropPermission(InteropKind::ComInterop);
    PCCpermInteropPermission.assert();
    pccSysMailer = new Sysmailer();
    smtpServer  = parameters.SMTPRelayServerName;
    SMTPPort    = parameters.SMTPPortNumber;
    //pccSysMailer.SMTPRelayServer('10.12.2.12',25,'service.mfp@saudipcc.com','',false);
    pccSysMailer.SMTPRelayServer(smtpServer, SMTPPort,"britto@dynamicnetsoft.com",'',false);
    pccSysMailer.fromAddress("britto@dynamicnetsoft.com", "Petro Chevron Company");
    pccSysMailer.tos().appendAddress(receiverMailAddress);
    pccSysMailer.attachments().add("C:\\TEMP\\032012.txt");
    //pccSysMailer.ccs().appendAddress("ansar@dynamicnetsoft.com");
    mailBody            = "Dear Customer," +
    "\n\n Please  find attached Purchase Order Document for Document Number Purchased on "
    + "\n\n\n" +
    "Regards, \n" + userinfo.name + "\n\n\n" + "NOTE: This is electronically generated mail from Microsoft Dynamics AX. Please do not reply to this mail. ";
    pccSysMailer.subject(strfmt("Purchase Order Document"));
    pccSysMailer.htmlBody(mailBody);
    pccSysMailer.sendMail();
    info("Email has been sent to the appropriate vendor.");
}
 16

=========================================================================
static void Job36(Args _args)
{
    Timezone userTimeZone = DateTimeUtil::getUserPreferredTimeZone();
    HcmEmploymentValidFrom HcmEmploymentValidFrom;
    HcmEmploymentValidFrom = DateTimeUtil::applyTimeZoneOffset(HcmDateTimeUtil::startOfCurrentDay(), userTimeZone);
    info(strFmt("%1",DateTimeUtil::date(HcmEmploymentValidFrom)));
}

=========================================================================
Use Join and Group by :
static void Job2(Args _args)
{
    CustTable custTable;
    CustTrans custTrans;
    custgroup custGrouploc;
   
    while select * from custGrouploc
    {
       info(strFmt("CustGroup : %1",custGrouploc.CustGroup));
          
  while select AccountNum,sum(AmountMST) from custTrans join custTable group by custTrans.AccountNum
      where custTrans.AccountNum == custTable.AccountNum && custTable.CustGroup == custGrouploc.CustGroup
    {
        info(strFmt("AccountNum : %1, Amount : %2",custTrans.AccountNum , custTrans.AmountMST));
    }
    
    }
}
==============================================================
static void Job5(Args _args)
{
  BiddingTable biddingTable;
    BidTenderDocumentTable  bidTenderDocumentTable;
    BidTenderRiskFactorsTable bidTenderRiskFactorsTable;
   BiddingTenderEvaluationTable   biddingTenderEvaluationTable;  
    
while select * from biddingTable join biddingTenderEvaluationTable
where biddingTable.BidId == biddingTenderEvaluationTable.BidId
   /*&& biddingTenderEvaluationTable.OpportunityId == biddingTable.OpportunityId*/
join  bidTenderRiskFactorsTable where bidTenderRiskFactorsTable.BidId == BiddingTable.BidId
join bidTenderDocumentTable where/* bidTenderDocumentTable.OpportunityId == biddingTable.OpportunityId && */
        bidTenderDocumentTable.BidId == biddingTable.BidId && biddingTable.BidId == "JEDSFC-SAIT-000011R"
    {
    info(strFmt("%1", BiddingTable.BidId));
    }
}

select * from GENERALJOURNALACCOUNTENTRY join GENERALJOURNALENTRY on

GENERALJOURNALACCOUNTENTRY.GENERALJOURNALENTRY= GENERALJOURNALENTRY.RECID

join FISCALCALENDARPERIOD on

GENERALJOURNALENTRY.FISCALCALENDARPERIOD = FISCALCALENDARPERIOD.RECID

join  DIMENSIONATTRIBUTEVALUECOMBINATION on

GENERALJOURNALACCOUNTENTRY.LEDGERDIMENSION = DIMENSIONATTRIBUTEVALUECOMBINATION.RECID

join MAINACCOUNT on DIMENSIONATTRIBUTEVALUECOMBINATION.MAINACCOUNT = MAINACCOUNT.RECID

join  GENERALJOURNALACCOUNTENTRYDIMENSION on

GENERALJOURNALACCOUNTENTRY.RECID = GENERALJOURNALACCOUNTENTRYDIMENSION.GENERALJOURNALACCOUNTENTRY

and FISCALCALENDARPERIOD.TYPE = '1' or FISCALCALENDARPERIOD.TYPE = '0'

where GENERALJOURNALACCOUNTENTRY.POSTINGTYPE != 19 and GENERALJOURNALENTRY.LEDGER = '5637146334'


======================================================================
Job To find Mandatory Fields in a table

static void mandatoryFieldsOfATable(Args _args)

{

SysDictTable sysDictTable;

SysDictField sysDictField;

TableId tableId;

Counter counter;

;

sysDictTable = new SysDictTable(tablenum(CustTable));

for(counter = 1;counter <= sysDictTable.fieldCnt(); counter++)

{

sysDictField = new sysDictField(sysDictTable.id(), sysDictTable.fieldCnt2Id(counter));

if(sysDictField.mandatory())

info(sysDictField.name());

}

}

   ======================================================================

1. How  to write update for the record :  

ttsBegin;

select  forUpdate RecId from HcmEmploymentLeave  where HcmEmploymentLeave.PersonnelNumber  == HcmEmploymentLeaveLinesTable.PersonnelNumber
                                                      && HcmEmploymentLeave.AbsenceID        == HcmEmploymentLeaveLinesTable.AbsenceID;
HcmEmploymentLeave.NoOfDays                     = HcmEmploymentLeave.NoOfDays - totalnoofdays;

HcmEmploymentLeave.update();

ttsCommit;
======================================================================
Create Dynamic Query : ( joining two tables
static void CustTableSales1(Args _args)
{
    Query       query;
    QueryRun    queryrun;
    QueryBuildDataSource    qbds1;
    QueryBuildDataSource    qbds2;
    QueryBuildRange         qbr1;
    QueryBuildRange         qbr2;
    CustTable               custTable;
    ;
    query   = new query();
    qbds1   =   query.addDataSource(tablenum(CustTable));
    qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
    qbr1    = qbds1.addRange(fieldnum(custTable,custGroup));
    qbr1.value(queryvalue('10'));
    qbr2    =  qbds1.addRange(fieldnum(custTable,Blocked));
    qbr2.value(queryvalue(CustVendorBlocked::No));
    qbds2   = qbds1.addDataSource(tablenum(SalesTable));
    qbds2.relations(false);
    qbds2.joinMode(joinmode::ExistsJoin);
    qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
    queryrun    = new queryrun(query);
    while(queryrun.next())
    {
    custTable   = queryrun.get(tablenum(custTable));
    info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name));
    }
}