Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

Accrual amount in Accrued purchases report when discount is applied

This is about the following report – Procurement and Sourcing / Reports / Status / Accrued purchases.

In AX2012 (companing with AX2009) we’ve made a performance improvement of the report by changing it’s datasource. But if you use discounts the report might show you a wrong value. Below you will find a way to resolve it.

Scenario. In case you apply a discount to a purchase order and then post a packing slip, the amount of the packign slip’s Purchase, accrual is the amount before discount (for example, 1000), but the report will show you the amounts after discount (for example, 900). So there’s a discrepancy between the report and the real posting.

Below is an illustration of the code that will resolve the issue, but the report may work a bit slower. The new code lines are marked in yellow.

\Classes\VendAccruedPurchasesDP_NA\processReport
/// <summary>
/// Processes the SQL Server Reporting Services report business logic.
/// </summary>
/// <remarks>
/// This method provides the ability to write the report business logic. This method will be called by
/// SQL Server Reporting Services (SSRS) at run time. The method should compute data and populate the
/// data tables that will be returned to SSRS.
/// </remarks>
[SysEntryPointAttribute(false)]
public void
processReport()
{
    VendInvoiceTrans        vendInvoiceTrans, vendInvoiceTransNotExists;
    VendTable vendTable;
    InventTransOrigin      inventTransOrigin;
    InventTrans            inventTrans;
VendInvoicePackingSlipQuantityMatch     qtyMatched,     qtyMatchedNotExists;
VendAccruedPurchasesPartialInvoicedQty  partialInvoiced, partialInvoicedBeforeCutOff;
    this.getParametersFromContract();
    this.processReportQuery();

     if (physicalOnly)
    {
        this.buildPhysicalOnlyVendAccruedPurchases(true);
        this.buildPhysicalOnlyVendAccruedPurchases(false);
    }
    else
    {
        this.buildVendAccruedPurchases();
    }

    // Now that all rows exist for the report, do set-based
    // updates to fill in the remaining columns
     // Remove the product receipt records that don’t have invoices and are after the cut-off date
    delete_from vendAccruedPurchasesTmp_NA
        where vendAccruedPurchasesTmp_NA.DatePhysical > cutOffDate
        notexists join qtyMatched where
            qtyMatched.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine;
     // Remove the records that have product receipts and invoices that are after the cut-off date and don’t have
    // invoices that are prior to the cut-off date
    delete_from vendAccruedPurchasesTmp_NA
        where vendAccruedPurchasesTmp_NA.DatePhysical > cutOffDate
        exists join qtyMatched where
            qtyMatched.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
        join vendInvoiceTrans where
            vendInvoiceTrans.SourceDocumentLine == qtyMatched.InvoiceSourceDocumentLIne
            && vendInvoiceTrans.InvoiceDate > cutOffDate
        notexists join qtyMatchedNotExists where
            qtyMatchedNotExists.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
        join SourceDocumentLine, InvoiceDate from vendInvoiceTransNotExists where
            vendInvoiceTransNotExists.SourceDocumentLine == qtyMatchedNotExists.InvoiceSourceDocumentLIne
            && vendInvoiceTransNotExists.InvoiceDate <= cutOffDate;
     // Summation of partial invoiced quantity cannot be done in the
    // original insert_recordset as it would change the cardinality of
    // the rows in the report. Similarly, it can’t be done in a simple
    // update_recordset because update_recordset does not support summation..
    // Instead, do an insert_recordset with a sum into a staging table,
    // and then update out of that staging table.
     // At this point partialInvoiced may still contain quantities for the fully invoiced records.
    // We will delete them from vendAccruedPurchasesTmp_NA in the next statement.
     insert_recordset partialInvoiced (PackingSlipSourceDocumentLine, PartiallyInvoicedQuantity)
        select PackingSlipSourceDocumentLine from vendAccruedPurchasesTmp_NA
            group by vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
            where vendAccruedPurchasesTmp_NA.DatePhysical <= cutOffDate
                    join sum(Quantity) from qtyMatched where
                        qtyMatched.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
                    exists join vendInvoiceTrans where
                        vendInvoiceTrans.SourceDocumentLine == qtyMatched.InvoiceSourceDocumentLIne
                        && vendInvoiceTrans.InvoiceDate <= cutOffDate;
     // Remove those records that have already been fully invoiced (Qty = InvoicedQty)
    delete_from vendAccruedPurchasesTmp_NA
        exists join partialInvoiced where
            partialInvoiced.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
            && vendAccruedPurchasesTmp_NA.Qty == partialInvoiced.PartiallyInvoicedQuantity;
     // Grab and sum up records that are invoiced prior to the cut-off date, but were received after the cut-off date
    insert_recordset partialInvoicedBeforeCutOff (PackingSlipSourceDocumentLine, PartiallyInvoicedQuantity)
        select PackingSlipSourceDocumentLine from vendAccruedPurchasesTmp_NA
            group by vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
            where vendAccruedPurchasesTmp_NA.DatePhysical > cutOffDate
                    join sum(Quantity) from qtyMatched where
                        qtyMatched.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
                    exists join vendInvoiceTrans where
                        vendInvoiceTrans.SourceDocumentLine == qtyMatched.InvoiceSourceDocumentLIne
                        && vendInvoiceTrans.InvoiceDate <= cutOffDate;

    // Date the invoice was posted
    update_recordset vendAccruedPurchasesTmp_NA setting
        DateFinancial    = vendInvoiceTrans.InvoiceDate,
        CostAmountPosted = (vendInvoiceTrans.LineAmountMST / vendInvoiceTrans.Qty) * vendAccruedPurchasesTmp_NA.Qty
        join qtyMatched
            where qtyMatched.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
        join maxOf(InvoiceDate), LineAmountMST, Qty from vendInvoiceTrans
            where vendInvoiceTrans.SourceDocumentLine == qtyMatched.InvoiceSourceDocumentLine;
     // Vendor name
    update_recordSet vendAccruedPurchasesTmp_NA setting
        VendName = dirPartyTable.Name
        join dirPartyTable
        exists join vendTable where
            vendTable.AccountNum == vendAccruedPurchasesTmp_NA.InvoiceAccount &&
            vendTable.Party == dirPartyTable.RecId;
     // Cost amount posted (has to be calculated *after* Qty is calculated for partial invoicing)
    update_recordSet vendAccruedPurchasesTmp_NA setting
        CostAmountPhysical = (vendAccruedPurchasesTmp_NA.ValueMST / vendAccruedPurchasesTmp_NA.ReceivedQuantity) * (vendAccruedPurchasesTmp_NA.ReceivedQuantity – partialInvoiced.PartiallyInvoicedQuantity),
        CostAmountPosted = 0,
        qty              = (vendAccruedPurchasesTmp_NA.ReceivedQuantity – partialInvoiced.PartiallyInvoicedQuantity),
        Voucher          =
        where vendAccruedPurchasesTmp_NA.ReceivedQuantity != 0
        join partialInvoiced where
            partialInvoiced.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine;

    // Any packing slip that is prior to the cut-off date and has invoices that are only after the cut-off date should be updated.
    // If there are invoices prior the cut-off date, then this update does not apply.
    update_recordSet vendAccruedPurchasesTmp_NA setting
        CostAmountPosted = 0
        where vendAccruedPurchasesTmp_NA.ReceivedQuantity != 0
        join qtyMatched where
            qtyMatched.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine
        join vendInvoiceTrans where
            vendInvoiceTrans.SourceDocumentLine == qtyMatched.InvoiceSourceDocumentLIne
            && vendInvoiceTrans.InvoiceDate > cutOffDate
        notExists join partialInvoiced where
            partialInvoiced.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine;
     // Any packing slip that is prior to the cut-off date and has invoices that are before the cut-off date should be updated.
    update_recordSet vendAccruedPurchasesTmp_NA setting
        DateFinancial    = dateNull()
        where vendAccruedPurchasesTmp_NA.ReceivedQuantity != 0
        join partialInvoiced where
            partialInvoiced.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine;
     // Update Costs and quantities for packing slips that are after the cut-off, but invoices that are before the cut-off
    update_recordSet vendAccruedPurchasesTmp_NA setting
        CostAmountPhysical = (vendAccruedPurchasesTmp_NA.ValueMST / vendAccruedPurchasesTmp_NA.ReceivedQuantity) * partialInvoicedBeforeCutOff.PartiallyInvoicedQuantity,
        CostAmountPosted = (vendAccruedPurchasesTmp_NA.ValueMST / vendAccruedPurchasesTmp_NA.ReceivedQuantity) * partialInvoicedBeforeCutOff.PartiallyInvoicedQuantity,
        qty              = partialInvoicedBeforeCutOff.PartiallyInvoicedQuantity
        where vendAccruedPurchasesTmp_NA.ReceivedQuantity != 0
        join partialInvoicedBeforeCutOff where
            partialInvoicedBeforeCutOff.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp_NA.PackingSlipSourceDocumentLine;

    // Accrual (has to be calculated *after* CostAmountPosted is populated)
    update_recordSet vendAccruedPurchasesTmp_NA setting
        Accrual = vendAccruedPurchasesTmp_NA.CostAmountPhysical – vendAccruedPurchasesTmp_NA.CostAmountPosted
        where vendAccruedPurchasesTmp_NA.DatePhysical <= cutOffDate;

    update_recordSet vendAccruedPurchasesTmp_NA setting
        Accrual = 0 – vendAccruedPurchasesTmp_NA.CostAmountPosted
        where vendAccruedPurchasesTmp_NA.DatePhysical > cutOffDate;
    ttsbegin;
    while select forUpdate vendAccruedPurchasesTmp_NAZ
    {
        select RecId from inventTransOrigin
            where inventTransOrigin.InventTransId == vendAccruedPurchasesTmp_NA.InventTransID
        join inventTrans
            where inventTrans.InventTransOrigin   == inventTransOrigin.RecId
            &&    inventTrans.VoucherPhysical     == vendAccruedPurchasesTmp_NA.VoucherPhysical;
          if (inventTrans.DatePhysical && inventTrans.DateFinancial)
        {
            if (inventTrans.DatePhysical < inventTrans.DateFinancial)
            {
                accrual = inventTrans.costAmountPhysExclStdAdjustment();
            }
            else
            {

if (inventTrans.DatePhysical > inventTrans.DateFinancial)
                {
                    accrual = -inventTrans.costAmountPhysExclStdAdjustment();
                }
                else
                {
                    accrual = -inventTrans.CostAmountPosted;
                }
            }
        }
        else
        {
            if (inventTrans.DatePhysical)
            {
                accrual =  inventTrans.costAmountPhysExclStdAdjustment();
            }
            if (inventTrans.DateFinancial)
            {
                accrual =  -inventTrans.CostAmountPosted;
            }
        }
        vendAccruedPurchasesTmp_NA.Accrual = accrual;
        vendAccruedPurchasesTmp_NA.update();
    }
    ttsCommit;
}

Disclaimer. The described code changes are for your information only. Microsoft only supports installation of the packaged hotfix, and we do not recommend that you attempt to implement similar changes manually.