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
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)));
}
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));
}
}
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));
}
}
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));
}
}
