NetPrice of an item using X++ Ax2012 R2 and R3.

Below is a test job to find the Net-Price of an item, considering different Trade Agreement settings.

static void NetAmountwithdiscounts(Args _args)
{

PriceDisc_Price             priceDisc_Price    = PriceDisc_Price::construct();
PriceDisc_LineDisc          priceDisc_LineDisc = PriceDisc_LineDisc::construct();
InventDim                   inventDim;
AmountCur                   LineAmount;

priceDisc_Price.parmModuleType( ModuleInventPurchSales::Sales);
priceDisc_Price.parmItemId(“iphone7”);
priceDisc_Price.parmInventDim(InventDim);
priceDisc_Price.parmUnitID(“ea”);
priceDisc_Price.parmPriceDate(systemDateGet());
priceDisc_Price.parmQty(1);
priceDisc_Price.parmAccountNum(“”);
priceDisc_Price.parmCurrencyCode(“USD”);
priceDisc_Price.parmPriceGroupId(“”);
print priceDisc_Price.price();
print priceDisc_Price.priceUnit();
pause;

priceDisc_LineDisc.parmModuleType( ModuleInventPurchSales::Sales);
priceDisc_LineDisc.parmItemId(“iphone7”);
priceDisc_LineDisc.parmInventDim(inventDim);
priceDisc_LineDisc.parmUnitID(“ea”);
priceDisc_LineDisc.parmDiscDate(systemDateGet());

priceDisc_LineDisc.parmQty(1);
priceDisc_LineDisc.parmAccountNum(“”);
priceDisc_LineDisc.parmCurrencyCode(“USD”);
priceDisc_LineDisc.parmAccountLineDiscCode();
priceDisc_LineDisc.parmItemLineDiscCode(InventTableModule::find(“iphone7”, ModuleInventPurchSales::Sales).LineDisc);
//  priceDisc_LineDisc.parmSalesPurchLine(_salesPurchLine);
priceDisc_LineDisc.findLineDiscAgreement();
print priceDisc_LineDisc.discAmount();   //linedisc
print priceDisc_LineDisc.discPct(); //LinePercent
pause;

LineAmount = PriceDisc::price2Amount(priceDisc_Price.price(),
priceDisc_Price.priceUnit(), //1
PriceDisc::discAmount(SalesParameters::find().Disc, priceDisc_LineDisc.discAmount(), 0),//should call this,
1,                   // deliver now
1,
0,
PriceDisc::discPercent(SalesParameters::find().Disc, priceDisc_LineDisc.discPct(), 0),
“USD”,
0,
true);

print LineAmount;
pause;

}

Advertisements

Leave a comment

Filed under Uncategorized

DateTimeUtil values in Queries in X++ ax2012

Hi

While working on a requirement with my friend we encountered an error , while trying to pass an extended query syntax.

Error is as stated below :

“Query extended range failure: Right parenthesis expected near pos XX.”

After some investigation I realized that , extended query syntax should have a proper brackets around it. MS support blog help me to understand the ways.

Lets look at the example below,

Requirement : To filter “sales orders” based on date1(UtcDateTime type) if no records found then filter the records on field date2(UtcDateTime type).  “Date-range” to filter values in field “date1/date2”  is provided by the user.

step 1: Create Table as shown below

Screen1 Screen2

step 2: Add some demo records

Screen3

step 3: Write a Job to fetch data as per the requirement mentioned above.

Screen 4-1

Step 5 : run the job

Screen5

Now you may wonder , why you get error “Query extended range failure: Right parenthesis expected near pos 59” ??  if you watch the  marked block carefully, you see that i have not added “brackets” around each “conditional statement” , however , i have combined two conditional statements using the && operator and have put brackets around it. Well, this will work in normal IF statement but not in the  “extended query” statement.

So below  statement is not the one supported by xpp compiler:

rangeStr = strfmt(‘((%1 >= %3 &&  %1 <= %4) || (%2 >= %3 && %2 <= %4))’,
fieldStr(A_TrackTable, TrackPeriod1),
fieldStr(A_TrackTable, TrackPeriod2),
DateTimeUtil::toStr(dte1),
DateTimeUtil::toStr(dte2));

Also, if you note query does not give desired results, query fails to filter.

step 6: Make modifications to extended query string and run the job again.

Now I add brackets around each conditional statement in extended Query , as shown below

rangeStr = strFmt(‘((%1 >= %3) && (%1 <= %4)) || ((%2 >= %3) && (%2 <= %4))’,
fieldStr(A_TrackTable, TrackPeriod1),
fieldStr(A_TrackTable, TrackPeriod2),
DateTimeUtil::toStr(dte1),
DateTimeUtil::toStr(dte2)
);

Step 7: Run the job again.

You will get desired results:

Screen6

verify with Table

Screen7

Query filtered records , that are between “6/22/2015 12:00:00 am  and 6/28/2015 03:31:11 pm” in  fields TrackPeriod1 or TrackPeriod2.

Note that : SQL Queries generated in step 5 and step 7 are almost identical except the brackets around conditional statements.

Also note that, when you try to convert “DateTime” field to string , it is recommended use “DateTimeUtil::toStr”. The function   “datetime2str” does not help, in extended query statement.

Hope it helps.

Regards

Pradeep

LinkedIn

Leave a comment

Filed under Uncategorized

Product Category Hierarchy(Product Categories Tree lookup) in SSRS report dialog. Ax2012

Hi all

Today we shall see, how to add product-categories as a Tree Look up , in a SSRS report dialog.

Below i shall summarize the steps and they are self explanatory.

For this we take example of already existing report.

Step 1: Navigate to Procurement & sourcing -> Reports -> Categories -> Commodity codes by category . This already has a lookup for category. We shall add one field and call it Retail category.

Dialog 1

Step 2: Find contract and UI builder classes related to “Commodity codes by category” report.

  1.  Contract class : ProcCategoryCommodityCodesContract
  2.  UIBuilder class : ProcCategoryComodityCodesUIBuilder

Step 3: Modify Contract class : ProcCategoryCommodityCodesContract

a. Classdeclaration

ClassDecl1

b. add a new parm method

ParmEcoResCategory

Step 4: Modify UI builder class : ProcCategoryComodityCodesUIBuilder

a. Classdeclaration

UIBuilderClassDecl

b. build

Build

c. Add new lookup method lookupProdCategoryHierarchy

lookupMethod

d. modify postRun method

postRun

Step 5: Compile both contract and UI builder class and generate a incremental CIL.

Step 6: Navigate to Procurement & sourcing -> Reports -> Categories -> Commodity codes by category

finalResult

Thats it for today.

Leave a comment

Filed under Uncategorized

AX 2012 : Ax build.exe throws error unhandle exception …. at wmainCRTStartup()

one possible solution  to fix the error ““Unhandled Exception: System.Runtime.InteropService.SEHException: External Component has thrown an exception. at wmainCRTStartup()”

Follow below steps :

1. Refreshed WCF configuration of using AX2012 Client configuration utility.
2. Run Command prompt as “Administrator”, change directory to path where axbuild utility is located i.e. “c:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin”

3. Execute command “axbuild.exe xppcompileall /aos=01 /altbin=”C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin” /workers=4″

Leave a comment

Filed under Uncategorized

Getting Ledger transactions in Ax 2012

Reference : https://community.dynamics.com/ax/f/33/t/154294.aspx

In Ax 2009, it was pretty simple as you had to just loop through LedgerTrans table, Ax 2012 it has changed a bit.

Lets take an example and see how we can achieve this. Say you want to display following information in a report (for example purpose we will use infolog, I will cover reports in other blog entries).

Main account number – Main account name, Transaction date, voucher number, amount in base currency, amount in transaction currency and currency code.

To fetch transactions, now there are two tables that we need to use:

GeneralJournalEntry, GeneralJournalAccountEntry (There are more tables with GeneralJournalPrefix, I have not found there use yet but will update this post once I find more use of them).

static void findLedgerTransactions(Args _args)

{

MainAccount                         mainAccount; //Holds the main accounts

GeneralJournalEntry                 generalJournalEntry; //Used to hold Ledger transactions

GeneralJournalAccountEntry          generalJournalAccountEntry; //Used to hold Ledger transactions

SubledgerJournalEntry               subLedgerJournalEntry; //Used to hold sub Ledger transactions (Like sales/purch invoice etc.)

SubledgerJournalAccountEntry        subLedgerJournalAccountEntry;  //Used to hold sub Ledger transactions (Like sales/purch invoice etc.)

DimensionAttributeValueCombination  dimAttrValueComb; //Used to store the combination of main accounts and dimensions

while select AccountingCurrencyAmount, TransactionCurrencyAmount,  TransactionCurrencyCode

from generalJournalAccountEntry

join dimAttrValueComb

where dimAttrValueComb.RecId == generalJournalAccountEntry.LedgerDimension

join AccountingDate, JournalNumber from generalJournalEntry

where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

&& generalJournalEntry.AccountingDate == 017\2010

&& generalJournalEntry.PostingLayer == OperationsTax::Current

&& generalJournalEntry.Ledger == Ledger::current()

join MainAccountId, Name from mainAccount

where mainAccount.RecId == dimAttrValueComb.MainAccount

&& mainAccount.MainAccountId == ‘130100’

join subLedgerJournalAccountEntry

where subLedgerJournalAccountEntry.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId

&& subLedgerJournalAccountEntry.LedgerDimension == generalJournalAccountEntry.LedgerDimension

join Voucher from subLedgerJournalEntry

where subLedgerJournalAccountEntry.SubledgerJournalEntry == subLedgerJournalEntry.RecId

//&& subLedgerJournalEntry.Ledger == Ledger::current()

{

info(strFmt(“%1-%2, %3, %4, %5, %6, %7, %8”, mainAccount.MainAccountId, mainAccount.Name,

generalJournalEntry.AccountingDate, subLedgerJournalEntry.Voucher,

generalJournalAccountEntry.TransactionCurrencyCode, generalJournalAccountEntry.TransactionCurrencyAmount,

generalJournalAccountEntry.AccountingCurrencyAmount,

generalJournalEntry.JournalNumber));

}

}

Leave a comment

Filed under Uncategorized

date 2 sql string (date)

str date2SqlStr(date _date)

{
str sqlDate;

if (_date == dateNull())

{

sqlDate = ’01\1\\1901′;

}

else

{

if(_date == maxdate())

{

sqlDate = ’31\\12\\2150′;

}

else

{

sqlDate = date2str(_date, 123, 2, 3, 2, 3, 4);

sqlDate = strReplace(sqlDate, ‘-‘, ‘\\’);

}

}

return sqlDate;

}

1 Comment

Filed under ax2012, date in a query, date to SQL date

Reserve and Remove reservation for an Item , X++ , Ax2012

Reserve:

static void A_reserveItem(Args _args)
{
InventTrans             inventTrans;
InventTransOrigin       inventTransOrigin;
InventMovement          inventMovement;
InventUpd_Reservation   inventUpd_Reservation ;

// Reserve an item
select inventTrans
where  inventTrans.ItemId                      == “0900270G31115”
&&  inventTrans.StatusReceipt               == StatusReceipt::None
&&  inventTrans.StatusIssue                 == StatusIssue::OnOrder
exists join inventTransOrigin
where   inventTransOrigin.RecId            == inventTrans.InventTransOrigin;

if(inventTrans.RecId)

{
Inventmovement = inventTrans.inventmovement(true);
inventUpd_Reservation = InventUpd_Reservation::newInventDim(inventmovement,inventTrans.inventDim(),inventTrans.Qty, false);
inventUpd_Reservation.updatenow();
}

info(‘done’);
}

Remove reservation:

static void A_RemoveReserveItem(Args _args)
{
InventTrans             inventTrans;
InventTransOrigin       inventTransOrigin;
InventMovement          inventMovement;
InventUpd_Reservation   inventUpd_Reservation ;

// Remove reservations and markings on a reserved salesorder
while select inventTrans
where  inventTrans.ItemId                      == “0900270G31115”
&&  inventTrans.StatusReceipt               == StatusReceipt::None
&& (inventTrans.StatusIssue                 == StatusIssue::ReservPhysical
||  inventTrans.StatusIssue                 == StatusIssue::ReservOrdered)
exists join inventTransOrigin
where   inventTransOrigin.RecId            == inventTrans.InventTransOrigin
{

if (inventTrans.MarkingRefInventTransOrigin)
{
InventTransOrigin::deleteMarking(inventTrans.MarkingRefInventTransOrigin, inventTrans.InventTransOrigin, -inventTrans.Qty, true);
InventTransOrigin::deleteMarking(inventTrans.InventTransOrigin, inventTrans.MarkingRefInventTransOrigin, inventTrans.Qty, true);
}

if (inventTrans.StatusIssue == StatusIssue::ReservPhysical || inventTrans.StatusIssue == StatusIssue::ReservOrdered)
{
Inventmovement = inventTrans.inventmovement(true);
inventUpd_Reservation = InventUpd_Reservation::newInventDim(inventmovement,inventTrans.inventDim(), -1 * inventTrans.Qty, false);
inventUpd_Reservation.updatenow();
}
}
info(‘Done’);
}

Leave a comment

Filed under Uncategorized