Information Systems:AR Detail Report

A special type of A/R report was created (upon shareholder request) to provide a list of A/R transactions broken down to the item level. The resulting SQL was converted to an Infonet utility called IN2_ARCUST.PGM. The query logic behind this report attempts to simulate the customized business logic that runs when the system produces an invoice, because this data is not logged to the database (in a usable manner) after it is generated and output to a printable document (the actual invoice). As the report is doing recalculations to work around data that doesn't physically (physidigitally?) exist, this page was written to point out some of the caveats and flaws about it.

Files

These are the files implicated in the query in order to be able to produce the desired results:

• Invoice lines (for items) from SROISDPL.
• Surcharge invoice lines from SROISDSU.
• Admin fee invoice lines from SROISA.
• The tax amount and discounts for invoice lines are logged to UZIVLIL1. This is a custom table that presumably was too close in proximity to base functionality that it was put in the ASW library (UP1480BFVA) instead of UWDASWPRDD.
• AR transaction lines are stored in SRODTA.

Caveats

Trying to recreate data and simulate application logic using query logic will be prone to error. These are some of the potential issues regarding this report.

• Invoices and credit notes are kept in the same file, both with positive amounts. Another field - invoice type - must therefore be used to determine whether the amount should be reported as positive (sales invoice) or negative (credit note).
• Discounts are applied to the invoice total, but the invoice total cannot be obtained by adding the amounts as they exist in the invoice table, because the program rounds them first when producing an invoice. Each invoice line is first rounded to 2 decimal places, then summed. The discount is then applied to this amount. These rounded amounts are not logged. The report calculates other values based on the net amounts that are stored to 4 decimal places, hence there will ultimately be discrepancies.
• The discount amount per invoice line is also not logged. Instead the total discount amount (for the invoice) is written to each invoice line in UZIVLIL1. Therefore, the discount amount per line/item must be recalculated by the query, since it is not logged anywhere.
• The previous point is further complicated by surcharge and admin fees, some of which are discounted but at the header level instead of the item (see next point).
• Discounts are calculated during invoice generation according to the terms code. However, terms codes exist at the invoice header level and at the item level (item level terms code overrides invoice header level if specified). In fact, every discount applied is an override (item overrides header). The terms code, terms code source (item or header), but inconsistently. For example, an item that derives its discount from the order header will log the terms code source correctly ('ORD'), but the actual terms code incorrectly.
• Item descriptions for some of the various misc. charges (surcharges, admin fees), are output to the document during invoice generation, but are not logged. Some line items on an invoice also don't have item numbers.
• AR will contain a record per invoice (INV, CRN etc.) with the total amount, but the report already displays the invoice lines and amounts from the invoice file, so these must be excluded. Same goes for discount entries (CAD), which are also summarized in AR.

Should I continue? There are many more points, but the point is, the query logic is fairly complex to work around the fact that some of the data required for the report is not logged (or logged in a usable manner) and therefore must be recalculated.

Notes

• Despite the complex query logic and known issues, the report produced is actually fairly accurate. The only known anomaly is the rounding issue, because that cannot possibly be compensated for by query logic.
• This report is being sent to Central Drugs for all stores (Feb. 2019) for input into their accounting software. While better off as a reference tool, the amount of data entry saved makes it arguably valuable enough such that the minor discrepancies that are inevitable with such a design are deemed tolerable by the store(s).