Information Systems:Unmatched Purchase Orders Not in Balance

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