Calculate ledger balance by dimension set in X++ in AX2012/Dynamics 365 FO

There are a variety of ways users can view balances in the general ledger. Some of the most common options are:

1. Trial balance

2. Financial reports

3. Voucher transactions

4. Ledger reports

The same can be achieved by some jobs like this to calculate ledger balance by dimension set which is derived from the class DimensionserviceProvider.

static void CalculateBalanceByFocusSet(Args _args)
{
    List                                    dimensionValuesList;
    DimensionSetContract                    dimensionSet = new DimensionSetContract();
    DimensionAttributeValueContract         dimensionAttributeValueContract;
    DimensionAttributeValueContract         dimensionAttributeValueContract2;
    DimensionAttributeValueContract         dimensionAttributeValueContract3;
    RecId                                   ledgerDimensionId;
    RecId                                   ledgerId;
    DimensionAttributeValueCombination      dimAttrValueCombo;
    LedgerBalanceDimAttrValueComboAmounts   ledgerBalance;
    DimensionServiceProvider                serviceProvider = new DimensionServiceProvider();
    DimensionSetBalanceContract             result = new DimensionSetBalanceContract();
    DimensionSetCombinationContract         dimensionSetCombinationContract;
    
    dimensionValuesList = new List(Types::Class); 
        
    dimensionAttributeValueContract = new DimensionAttributeValueContract();
    dimensionAttributeValueContract.parmName("Mainaccount");
    dimensionAttributeValueContract.parmValue("111");
    /*
    dimensionAttributeValueContract2 = new DimensionAttributeValueContract();
    dimensionAttributeValueContract2.parmName("CostCenter");
    dimensionAttributeValueContract2.parmValue("1111");
    
    dimensionAttributeValueContract3 = new DimensionAttributeValueContract();
    dimensionAttributeValueContract3.parmName("Activity");
    dimensionAttributeValueContract3.parmValue("1111");
    */
    dimensionValuesList.addEnd(dimensionAttributeValueContract);
    //dimensionValuesList.addEnd(dimensionAttributeValueContract2);
    //dimensionValuesList.addEnd(dimensionAttributeValueContract3);
    
    
    dimensionSetCombinationContract = new DimensionSetCombinationContract();
    dimensionSetCombinationContract.parmValues(dimensionValuesList);
    dimensionSetCombinationContract.parmDimensionSetName("MA"); // Based on the input dimensions we need to set the name, ex : MA+CC+AC
    
    dimensionSet.parmAccountingDateFrom(1\5\2019);
    dimensionSet.parmAccountingDateTo(30\4\2020);
    dimensionSet.parmCombination(dimensionSetCombinationContract);
    
    // Validate DimensionSetContract
    DimensionServiceProvider::validateDimensionSet(dimensionSet);

    // Setup LedgerBalanceDimAttrValueComboAmounts
    ledgerBalance = LedgerBalanceDimAttrValueComboAmounts::construct();
    ledgerBalance.parmAccountingDateRange(dimensionSet.parmAccountingDateFrom(), dimensionSet.parmAccountingDateTo());
    ledgerBalance.parmIncludeOpeningPeriod(true);//_dimensionSet.parmIncludeOpeningFiscalPeriod());
    ledgerBalance.parmIncludeRegularPeriod(true);///_dimensionSet.parmIncludeOperatingFiscalPeriod());

    if (dimensionSet.parmIsSystemGeneratedUltimo())
    {
        ledgerBalance.parmIncludeClosingPeriodBySystem(dimensionSet.parmIsSystemGeneratedUltimo());
    }
    else
    {
        ledgerBalance.parmIncludeClosingPeriod(dimensionSet.parmIncludeClosingFiscalPeriod());
    }

    ledgerBalance.parmLedgerRecId(Ledger::current());

    ledgerDimensionId = serviceProvider.buildDimStorageForDimSetCombo(dimensionSet.parmCombination());
    Debug::assert(ledgerDimensionId);

    dimAttrValueCombo = DimensionAttributeValueCombination::find(ledgerDimensionId);

    ledgerBalance.calculateBalance(dimAttrValueCombo);

    info(strFmt("%1", ledgerBalance.getAccountingCurrencyBalance()));

}

Get average unit cost of an item using x++ in D365 FO

Use this below code to get the average unit cost of an item using x++ by passing the relevant inventory dimensions and item

public class InventSumQuery
{    
    static void main(Args _args)
    {
        Qty                     totalQty;
        Amount                  cost;
        Query                   query;
        QueryRun                queryRun;
        QueryBuildDataSource    qbdsInventDim, qbdsInventSum;
        InventSum               inventSum;
   
        query = new query();

        qbdsInventSum = query.addDataSource(tableNum(InventSum)); 
        qbdsInventDim = qbdsInventSum.addDataSource(tableNum(InventDim));
        qbdsInventDim.relations(true);
        qbdsInventDim.joinMode(JoinMode::InnerJoin);
        qbdsInventDim.addRange(fieldNum(InventDim, InventSiteId)).value("");
        qbdsInventDim.addRange(fieldNum(InventDim, InventLocationId)).value("");
        qbdsInventDim.addRange(fieldNum(InventDim, wmsLocationId)).value("");
        qbdsInventSum.addGroupByField(fieldNum(InventSum, ItemId));

        qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedQty), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, Deducted), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, Received), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedValue), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, PhysicalValue), SelectionField::Sum);
    
        qbdsInventSum.addRange(fieldNum(InventSum, ItemId)).value("");

        queryrun = new QueryRun(query);

        while (queryRun.next())
        {
            queryRun.changed(tableNum(InventSum));
            {
                inventSum = queryRun.get(tableNum(InventSum));
                totalQty = inventSum.PostedQty - abs(inventSum.Deducted) + inventSum.Received;
                cost = abs(inventSum.PostedValue + inventSum.PhysicalValue) / totalQty;
                info(strFmt("Average unit cost - %1", abs(cost)));
            }

        }

    }
}

Import to target failed due to an update conflict as more than one process is trying to update the same record at the same time. Please try again in D365 FO

You may face this error when importing data through data entities and this mostly because of the index of the staging table.

The data in your file doesn't match with the index which causes violation and let system through this error.

Even this could happen when the index is not properly synchronized in the table that is used in the data entity. If so, try recreating the index.

Create delivery schedule for purchase line via x++ in ax 2012

I was supposed to create a delivery schedule for purchase lines via code and I created this job from the form, how the delivery schedule lines created in the backend.

The most important thing to handle is, the line number for the delivery lines. The logic needs to be created if you want to use custom line number else you can stick to the standard line numbering.

static void CreateDLVSchedule(Args _args)
{    
    List                                newScheduleLines = new List(Types::Container);
    PurchLine               		purchLine, purchLineNew;
    PurchDeliverySchedule   		purchDeliverySchedule;
    
    PurchTableForm_DeliverySchedule 	purchTableForm_DeliverySchedule;
    
    select purchLine
        where purchLine.PurchId == "PurchId"
        && purchLine.LineNumber == 10;
    
    purchTableForm_DeliverySchedule = PurchTableForm::construct(PurchTableFormId::DeliverySchedule, purchLine);    
    
    purchLineNew.setTmp();
    
    purchLineNew.data(purchLine.data());    
    
    purchTableForm_DeliverySchedule.purchLine_Init(purchLineNew);
    purchLineNew.LineNumber = purchLine.LineNumber + 1; // Add your logic to spcify line number
    purchTableForm_DeliverySchedule.purchLine_CreatePreSuper(purchLineNew);    
    newScheduleLines.addEnd([purchLineNew]);

    purchTableForm_DeliverySchedule.parmScheduleLines(newScheduleLines);
    purchTableForm_DeliverySchedule.parmMarkupConversionMode(DlvScheduleMarkupConversionMode::Copy);
    purchTableForm_DeliverySchedule.updateSchedule();
    
    info("Done");

}