Information Systems:Unmatched Purchase Orders Not in Balance - Revision history

Norwinu at 23:04, 3 October 2016

2016-10-03T23:04:56Z

New page

Unmatched purchase orders are in SROGRT, with a remaining amount. As part of month end, a query report is generated of this file, with sub totals by purchase order type. These sub totals can be compared to the corresponding G/L accounts to make sure they are in balance.

G/L account Purchase order type2
210050 MO Manual Order
210300 RT Return to Vendor
210500 CS Car Stock
210625 BE Price Variance – charge to H/O
210625 PV Price Variance – charge to Rep
210625 QV Quantity Variance
210675 PC Prairie Supply Co-op Credit (no longer used)
210675 PS Prairie Supply Co-op (no longer used)
220000 PE EDI Purchase Order
220000 PO Regular Purchase Order
220000 RO Rush Order
220000 XF Reference Purchase Order
220050 RE Receiving Error

If they are not in balance, the problem could be a timing issue. Receiving or invoicing can be done after the period end – which is after the report has been printed.

(Note that these examples are for regular PO types, that post to 220000.)

To check for this, run a query on file SROLOGGL, looking for any transactions for the previous period (or earlier), that were entered after the unmatched PO query report was run. Note that two checks have to be done. One for the day following, after 6:29AM – which is after the end of day processing has completed; and the other for any time after.

LGDIM1 EQ '220000'
AND LGYYPP LE 201312
AND LGCDAT GT 20130201
OR LGDIM1 EQ '220000'
AND LGYYPP LE 201312
AND LGCDAT EQ 20130201
AND LGCRTI GT 062900

This example looks for any transactions for the period just ended, that were entered after the report was printed.

Total LGSAMO, and compare to the difference.

Also look the other way; transactions done before the period end, but posted to a later period.

If it does not match, more digging has to be done. Run these queries (from library XX2480BP) to build a file of all purchase orders for which the total posted to the G/L does not agree with the remaining amount on SROGRT.

SROGRT is a balance file; not a transaction file, so you must restore it from the period end tape.

UNMATB02 – Extract purchase order transactions by document number.

Change this to include G/L transaction up the closing period.

UNMATB03 – Extract purchase order transactions by the PO number in the text

Change this to include G/L transaction up the closing period. The PO in the text can be different than the transaction number, if PO’s are matched against each other.

UNMATB04 – Summarized extracted G/L transactions by PO and G/L number

UNMATB05 – Build work file to use in join

UNMATB06A to UNMATM06F – Build separate file for each G/L number

UNMATB07 – Merge all these separate file together into one file, with separate columns for each G/L number

UNMATB08 – Summarized received and remaining amounts on SROGRT (goods receiving) by PO

Change this to read the version of SROGRT you have restored.

UNMATB09 – Build a version of the PO header file with a character PO number field

This is needed because that is what the PO number extracted from the G/L text field is.

UNMATB10 – Consolidate all files

Run RPG program XX2480BP/UNMATPO to add to this file, any PO numbers that have been matched together.

Download SHEILA2/ POCONS2 to Excel. The total difference between the G/L (SROLOGGL) and the unmatched PO (SROGRT) should match the amount you are looking for.

* taking into account that you will have to manually remove transactions done after the unmatched PO report was generated.

Note – Since we went live, there have been about 1304 unmatched PO’s.

182 Received before go live; invoiced after

1068 Debit Notes that no longer go into 220000. They are out
of balance by the fictitious item used to charge tax.

495 RT
436 QV
105 PV
32 BE

1073 records for a total of -30324.33

22 Adjustments

Which leaves only about 32 PO’s that didn’t balance. Five times the receiving transactions went into the G/L twice. Six times invoicing did both a debit and a credit to 220000. Seven times there was a fictitious item on the PO (some should have been type MO). For the rest, the invoice amount didn’t agree with the amount received. The last time that happened was November 2006, so perhaps processes have been changed?

Once there is a known point at which this is in balance, you only have to investigate things after that date. ** When you have the unmatched purchase orders in balance, update the last in balance date, so that you will have fewer PO’s to manually balance next year. **

In balance at 2013Feb01.

Balanced at 2013Dec31, but corrections for system outage on Dec 16 were put into 201412, as 201411 was closed. See unmatched PO 201411.xlsx for details

[[Category: Finance Problems (I.T.)]]

Sheilav: Created page with "Unmatched purchase orders are in SROGRT, with a remaining amount. As part of month end, a query report is generated of this file, with sub totals by purchase order type. The..."

2015-10-19T17:22:43Z

Created page with "Unmatched purchase orders are in SROGRT, with a remaining amount. As part of month end, a query report is generated of this file, with sub totals by purchase order type. The..."

New page