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.
