Get Calculate Retail Sales Price in ax2012

 

static void demo_CRT_GetSalesPrice(Args _args)

{

    //To test this code, use the AX 2012 R3 CU8, and use the USRT company

    Microsoft.Dynamics.Commerce.Runtime.Data.IPricingDataManagerV2                pricingManager;

    Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.DiscountParameters priceParameters;

    Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine                       crtSalesLine;

    Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction                crtSalesTransaction;

    System.Object                                                                 roundingRule, currencyConverter;

    CLRObject                                                                     clrSalesLines, enumeratorSalesLine;

    CLRObject                                                                     clrObjectSalesLines;

    System.DateTimeOffset                                                         activeDateTimeOffset;

    System.String                                                                 lineIdString;

    System.String                                                                 customerPriceGroup = "";

    Price                                                                         price;

    Qty                                                                           qty;

    InventDim                                                                     inventdim;

 

    RetailStoreTable    storeTable = RetailStoreTable::find("HOUSTON");   //Lets use houston as an example here

    RetailChannelRecId  channelId = storeTable.RecId;                     //This is the recid for a retail channel. A retail store is an extension of a retail channel.

    CurrencyCode        currencyCode = storeTable.Currency;

    appl.setDefaultCompany(storeTable.company(), false);                  //Setting default company to houstons company

 

    //Creating a unique line ID

    lineIdString = System.Guid::NewGuid().toString();

 

    InventDim.InventLocationId = storeTable.inventLocation;

    InventDim.InventSiteId     = InventLocation::find(storeTable.inventLocation).InventSiteId;

    InventDim                  = InventDim::findOrCreate(InventDim);

 

    //Pricing manager : Encapsulates data access to pricing information.

    pricingManager          = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.AxPricingDataManager::BuildDataManager(channelId);

 

    //Price parameters : types of AX discounts (aka trade agreement discounts, aka not Retail Periodic Discounts) are currently activated and should be allowed on the transaction

    priceParameters         = Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.DiscountParameters::CreateAndInitialize(pricingManager);

    roundingRule            = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.ServiceHelpers::GetRoundingMethod(currencyCode);

    currencyConverter       = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.ServiceHelpers::GetCurrencyConverter(currencyCode);

    activeDateTimeOffset    = RetailPricingEngineHelper::getSessionDateTimeInChannelTimeZone(channelId);

 

    //Next we create a crtSalesLine, that represents a channel agnostic sales line.

    crtSalesLine = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine();

 

    crtSalesLine.set_ItemId("0001");        //Lets use product 0001 as our example

    crtSalesLine.set_InventoryDimensionId(InventDim.inventDimId);

    crtSalesLine.set_Quantity(10);          //Lets use quantity 10 as our example

    crtSalesLine.set_LineId(lineIdString);  //Let give this line a uniqe ID

 

    crtSalesLine.set_UnitOfMeasureSymbol("ea"); //Let use 'ea' as the unit

 

    //We then create a sales transaction, and add the salesline to this.

    crtSalesTransaction = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction();

    clrObjectSalesLines = crtSalesTransaction.get_SalesLines();

    clrObjectSalesLines.Add(crtSalesLine);                      //This is where the we add the salesLine to the sales transaction

 

    //Lets calculate the prices

    Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculatePricesForTransaction(

        crtSalesTransaction,

        pricingManager,

        roundingRule,

        currencyConverter,

        customerPriceGroup,

        currencyCode,

        false,

        activeDateTimeOffset);

 

    //Lets calculate the discounts

    Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculateDiscountsForLines(

        pricingManager,

        crtSalesTransaction,

        roundingRule,

        currencyConverter,

        currencyCode,

        '',

        '',

        '',

        true,

        Microsoft.Dynamics.Commerce.Runtime.DataModel.DiscountCalculationMode::CalculateAll,

        activeDateTimeOffset);

 

    //And, let's show what the CRT can give us.

    qty   = crtSalesLine.get_Quantity();

    Price = crtSalesLine.get_Price();

    info(strFmt("Normal sales price is %1 when buying %2", price, qty ));

 

    Price = crtSalesLine.get_DiscountAmount();

    info(strFmt("Discount amount pr unit is %1, when buying %2", price/qty, qty ));

 

    Price = crtSalesLine.get_NetAmountPerUnit();

    info(strFmt("Net amount pr unit is %1, when buying %2", price, qty ));

 

    Price = crtSalesLine.get_TotalAmount();

 

    info(strFmt("Total amount is %1 when buying %2", price, qty ));

}







Reference :- Thanks Mr. Kurt Hatlevik for sharing knowledge

https://kurthatlevik.com/2015/05/21/dax2012-r3-playing-with-retail-crt/




Fetch Data from direct SQL Queries in ax 2012 d365fo

 Fetch Data from direct SQL Queries

        Connection  connection;

        Statement   statement;

        str         sql;

        ResultSet   resultSet;

        SqlStatementExecutePermission   perm;

        //sql =  "SELECT COUNT(*) as [NumOfCombinations] FROM " + "( " + "select MyName, MyValue " + "from tab1 " + strFmt("where DATAAREAID = '%1' ",curext()) + "group by MyName, MyValue" + ") a";

        sql =  strFmt("select * from taxtrans where TRANSDATE between CONVERT(date, '%1') and CONVERT(date, '%2')", datetime2Str(DateTimeUtil::newDateTime(fromDateValue,0)), datetime2Str(DateTimeUtil::newDateTime(toDateValue,0)));

        connection = new connection();

        perm = new SqlStatementExecutePermission(sql);

        perm.assert();

        statement = connection.createStatement();

        resultSet = statement.executeQuery(sql);

        while (resultSet.next())

Record Insert List in D365 FO in AX 2012

Record Insert List to insert data in one SQL Call.

Helps in improve performance

RecordInsertList tmpValueReporting = new RecordInsertList(tableNum(VendProvisionalBalanceTmpValue), false, false, false, false, true, vendProvisionalBalanceTmpValue);

while select crosscompany AccountNum, ADLegalEntity, MainAccountId
from vendProvisionalBalanceTmpProcessing
group by AccountNum, MainAccountId, ADLegalEntity
join PostingProfile, IsSummaryAccount, sum(AmountAccounting), sum(AmountReporting), ADLegalEntity
from vendProvisionalBalanceTransTmp
group by AccountNum, PostingProfile, IsSummaryAccount, ADLegalEntity
where vendProvisionalBalanceTransTmp.AccountNum == vendProvisionalBalanceTmpProcessing.AccountNum
//&& vendProvisionalBalanceTransTmp.ADLegalEntity == vendProvisionalBalanceTmpProcessing.ADLegalEntity
&& vendProvisionalBalanceTransTmp.PostingProfile == vendProvisionalBalanceTmpProcessing.PostingProfile
&& vendProvisionalBalanceTransTmp.IsSummaryAccount == vendProvisionalBalanceTmpProcessing.IsSummaryAccount
&& vendProvisionalBalanceTransTmp.ADLegalEntity == vendProvisionalBalanceTmpProcessing.ADLegalEntity
&& vendProvisionalBalanceTransTmp.TransDate < _fromDate
{
vendProvisionalBalanceTmpValue.AccountNum = vendProvisionalBalanceTmpProcessing.AccountNum;
vendProvisionalBalanceTmpValue.ADLegalEntity = vendProvisionalBalanceTmpProcessing.ADLegalEntity;
vendProvisionalBalanceTmpValue.MainAccountId = vendProvisionalBalanceTmpProcessing.MainAccountId;
vendProvisionalBalanceTmpValue.PostingProfile = vendProvisionalBalanceTransTmp.PostingProfile;
vendProvisionalBalanceTmpValue.IsSummaryAccount = vendProvisionalBalanceTransTmp.IsSummaryAccount;
vendProvisionalBalanceTmpValue.Amount = vendProvisionalBalanceTransTmp.AmountAccounting;
vendProvisionalBalanceTmpValue.ReportingCurrencyAmount = vendProvisionalBalanceTransTmp.AmountReporting;
//vendProvisionalBalanceTmpValue.insert();
tmpValueReporting.add(vendProvisionalBalanceTmpValue);
}
tmpValueReporting.insertDatabase();

 

SSRS Report Deployment failed - The number of defined parameter is not equal to the number of cell definitions in the parameter panel. in d365fo

 

Report deployment parameter Error

Error: - The number of defined parameter is not equal to the number of cell definitions in the parameter panel.

solution - check the old/ extra parameter in parameter node

delete ReportParameterLayout tag of extra parameter from XML File to remove parameter issue

AX2012 - How to fix ID Conflict issue

 We can Get ID Conflict issue

while importing Model Store or XPO etc.

I was able to fix somehow by using below link: -

https://github.com/DAXRunBase/AX-2012-R3/tree/master/Fix%20Table%20and%20Field%20ID

http://dev.goshoom.net/2011/11/id-change/

Workflow approver with submitted date and approved date in d365fo

 

public void init()
{
AFZ_PurchaseRequisitionSummaryReportTableTmp prTmp;
PurchReqTable purchReqTable;
PurchReqLine purchReqLine;
WorkflowTrackingStatusTable workflowTrackingStatus, workflowTrackingStatusTable, workflowTrackingStatusTmp;

WorkflowTrackingTable workflowTrackingTable, workflowTrackingTableTmp;
WorkflowTrackingCommentTable workflowTrackingCommentTable;
UserInfo userInfo;
RecId _recId, workflowElementTableRecId;
Name _name, actionTakenByName;
HcmWorkerRecId workerRecId;
HcmWorker hcmWorker;
int i = 1;

super();

delete_from prTmp;

while select purchReqTable
{
prTmp.PurchReqId = purchReqTable.PurchReqId;
prTmp.PurchReqName = purchReqTable.PurchReqName;
prTmp.PRPreparerName = purchReqTable.originatorName();
prTmp.RequisitionStatus = purchReqTable.RequisitionStatus;
prTmp.PRPreparedDate = purchReqTable.CreatedDateTime;
prTmp.SubmittedDateTime = purchReqTable.SubmittedDateTime;
prTmp.AFZPRBudgetId = purchReqTable.AFZPRBudgetId;
prTmp.AFZProjId = purchReqTable.AFZProjId;
prTmp.AFZPRCategoryId = purchReqTable.AFZPRCategoryId;

PurchReqBusJustification PurchReqBusJustification;
select * from PurchReqBusJustification where PurchReqBusJustification.RefRecId == purchReqTable.RecId
&& PurchReqBusJustification.RefTableId == purchReqTable.TableId;

prTmp.Details = PurchReqBusJustification.BusinessJustification;

//prTmp.BusinessJustification = purchReqTable.BusinessJustification;
prTmp.AFZPRNotes = purchReqTable.AFZPRNotes;
prTmp.AFZPurchPoolId = purchReqTable.AFZPurchPoolId;

prTmp.POApproverLevel1 = '';
prTmp.POApproverReceiveDateL1 = utcDateTimeNull();
prTmp.POApproverApproveDateL1 = utcDateTimeNull();

prTmp.POApproverLevel2 = '';
prTmp.POApproverReceiveDateL2 = utcDateTimeNull();
prTmp.POApproverApproveDateL2 = utcDateTimeNull();

prTmp.POApproverLevel3 = '';
prTmp.POApproverReceiveDateL3 = utcDateTimeNull();
prTmp.POApproverApproveDateL3 = utcDateTimeNull();

prTmp.POApproverLevel4 = '';
prTmp.POApproverReceiveDateL4 = utcDateTimeNull();
prTmp.POApproverApproveDateL4 = utcDateTimeNull();

prTmp.POApproverLevel5 = '';
prTmp.POApproverReceiveDateL5 = utcDateTimeNull();
prTmp.POApproverApproveDateL5 = utcDateTimeNull();

prTmp.POApproverLevel6 = '';
prTmp.POApproverReceiveDateL6 = utcDateTimeNull();
prTmp.POApproverApproveDateL6 = utcDateTimeNull();

_recId = purchReqTable.RecId;

while select workflowTrackingStatus order by CreatedDateTime asc
where workflowTrackingStatus.ContextTableId == tableNum(PurchReqTable)
&& workflowTrackingStatus.ContextRecId == _recId
&& workflowTrackingStatus.TrackingStatus == WorkflowTrackingStatus::Completed
join workflowTrackingTable order by CreatedDateTime asc
where workflowTrackingTable.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowtrackingtable.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval
join userInfo
where userInfo.id == workflowTrackingTable.User
{

select workflowTrackingStatusTmp order by RecId desc
where workflowTrackingStatusTmp.ContextTableId == tableNum(PurchReqTable)
&& workflowTrackingStatusTmp.ContextRecId == _recId
&& workflowTrackingStatusTmp.TrackingStatus == WorkflowTrackingStatus::Completed;

if(workflowTrackingStatusTmp.RecId == workflowTrackingStatus.RecId)
{

prTmp.FinalApprovedDate = workflowTrackingTable.CreatedDateTime;

if(prTmp.POApproverLevel1 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel1 = userInfo.name;
else
prTmp.POApproverLevel1 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId,CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL1 = workflowTrackingTableTmp.CreatedDateTime;

prTmp.POApproverApproveDateL1 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel2 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel2 = userInfo.name;
else
prTmp.POApproverLevel2 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL2 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL2 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel3 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel3 = userInfo.name;
else
prTmp.POApproverLevel3 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL3 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL3 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel4 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel4 = userInfo.name;
else
prTmp.POApproverLevel4 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL4 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL4 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel5 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel5 = userInfo.name;
else
prTmp.POApproverLevel5 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL5 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL5 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel6 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel6 = userInfo.name;
else
prTmp.POApproverLevel6 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;


prTmp.POApproverReceiveDateL6 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL6 = workflowTrackingTable.CreatedDateTime;
}
}

}
prTmp.insert();
}

}

}