Information Systems:Balance Unmatched PO's - Revision history

Norwinu at 17:17, 15 July 2016

2016-07-15T17:17:04Z

New page

The first (and largest) query month end report (file name QPQUPRFIL) gives the detail of unmatched receiving (remaining amounts on file SROGRT). The totals of the order types should match the G/L balances. You should look up the G/L balances as soon as possible, as the accounting department can back date entries to the previous period.

'''Order Type amount G/L balance difference'''
MO manual order 1,140.00 210050 0.00 1,140.00
RT return to vendor 170.00 210300 170.00-
CS car stock 0.00 210500 0.00
BE price variance-charge to H/O 0.00
PV price variance-charge to rep 0.00
QV quantity variance 3,616.79-
total 3,616.79- 210623 2,521.14 1,095.65-
PE EDI purchase order 693,407.41
PO regular purchase order 1,005,743.82
RO rush order 0.00
XF reference purchase order 8,949.45
total 1,708,100.68 220000 1,708,100.91- 0.23-
RE receiving error .00 220050 .00

==Month End==

A part of the month end process is to reconcile G/L transactions to the uninvoiced receipts. (See program BAL001CL.) Every G/L transaction is scrutinized, and the PO number(s) it refers to, and was matched with, is(are) found. All PO transactions that net out to zero, or that balance to uninvoiced receipts, are removed. The remaining transactions are shown in 'Out of Balance Unmatched POs' in InfoNet (Under uniVIEW / Finance).

==In InfoNet==

[[File:UnmatPO-3.jpg]]

This shows

- Total G/L transactions from file SROLOGGL that could not be net to zero, or balanced

- Total uninvoice receipts from file SROGRT that could not be balanced

- Total unposted batches from file SROTRAA that could not be balanced

- Total unposted journals from file SROOVF ([[Journal in Error|journal in error]]) that could not be balanced

'Balanced' means that the total of the G/L transaction in SROLOGGL, SROTRAA, and SROOVF for matched PO's either net to zero, or agree with the remaining amounts for SROGRT.

Some difference here are okay. The [[Generated Journal Entries|journal entries]] for manual orders (MO) are set up to both debit and credit 210050 when they are received, and again when they are invoiced. Therefore the G/L balance will always be zero; no matter what is in uninvoiced receipts (SROGRT).

[[File:UnmatPO-1.jpg]]

Click on 'Summary to Excel' to see the total by purchase order numbers.

==Export to Excel==

[[File:UnmatPO-2.jpg]]

Line 5 shows that PO 8195055 has been received, but not invoiced. The other lines show rounding errors. As these net out to zero, you don't really have to do anything with them - but they will stay on this inquiry for two fiscal years. They can be removed by doing an adjustment, with the PO number in the text field. Next month the PO will net out to zero, so will be ignored.

==Technical==

As part of the month end, '''BAL001CL''' is run, which extracts all G/L entries for accounts -

210050 (AP - clearing (manual orders))

210300 (AP returns debit adj (2052))

210500 (AP car stock (2055))

210625 (AP costing debit note clearing)

220000 (AP PO matching clearing)

220050 (AP Non-PO matching clearing)

This includes all records; right from conversion - which as of November 2015 was three and a half million.

For some transactions, the document number is the PO number; but not all - so the program '''BAL001R''' tries to figure it out.

- The text on some generated transactions is vendor number, seven spaces, then the PO number.

- Some transactions have text that is the vendor invoice number then vendor name. The invoice number can be used to get the PO number from the A/P transaction files.

- If none of the above, the program searches the text for a seven digit number starting with '8'.

Hint - this process will work much more efficiently if finance ensures that every transaction entered conforms to one of these.

If all the transactions for a PO for the same G/L number add up to zero, '''BAL002R''' will delete them.

'''BAL005R''' will delete offsetting amounts for the same G/L number and PO - but only if there are not uneven multiples. For example, if there is a single 5.00, and a single -5.00 they will be deleted. If there are three 5.00 and three -5.00 they will be deleted. If there is one 5.00 and two -5.00 they will not be deleted.

If all transactions for PO's that were matched together add up to zero, they are deleted in '''BAL008R'''.

Transactions for PO's that agree with uninvoiced receipts are deleted in '''BAL009R'''.

PO's that were in process as of conversion will never balance; therefore '''BAL010R''' will delete them from the transactions.

When an adjustment is made to balance the account, it can not be applied against each PO or vendor that is out of balance, so they will all still be in the transaction file. If, after two fiscal years, the account balances to zero, all transactions will be deleted. '''BAL017R''' is run for each G/L account number. It will -

- Delete all transactions for period 201412 and earlier, as we know the account is in balance.

- If all transactions two fiscal years old or older add up to zero, delete them. If they don't add up to zero, work backwards to find a period end at which they did, then delete them. This shouldn't be a problem, as the account should be balanced at every year end.

===Reasons for Being Out of Balance===

Manual Orders are configured to debit and credit account 210050 both when they are received, and when they are invoiced. This means that the total of the transactions, posted and unposted, should always add up to zero. Between receiving time and invoicing time, there will be a value in 'received not invoiced'; but that is not a problem.

Account 210625 can be out of balance if finance matches a debit note with a PO before the adjustment PO has been closed.

Sheilav at 21:18, 30 November 2015

2015-11-30T21:18:57Z

Sheilav at 21:17, 30 November 2015

2015-11-30T21:17:14Z

Sheilav at 21:04, 30 November 2015

2015-11-30T21:04:16Z

Sheilav at 21:00, 30 November 2015

2015-11-30T21:00:56Z

Sheilav at 20:30, 30 November 2015

2015-11-30T20:30:06Z

Sheilav at 20:23, 30 November 2015

2015-11-30T20:23:29Z

Sheilav at 20:08, 30 November 2015

2015-11-30T20:08:38Z

Sheilav at 19:38, 30 November 2015

2015-11-30T19:38:27Z

Sheilav at 18:28, 30 November 2015

2015-11-30T18:28:00Z

@@ Line 16: / Line 16: @@
  RE receiving error                      .00        220050           .00
-A part of the month end process is to reconcile G/L transactions to the uninvoiced receipts.  (See program BAL001CL.)  Every G/L transaction is scrutinized, and the PO number(s) it refers to, and was matched with, is(are) found. All PO transactions that net out to zero, or that balance to uninvoiced receipts, are removed.  The remaining transactions are shown in 'Out of Balance Unmatched POs' in InfoNet.
+A part of the month end process is to reconcile G/L transactions to the uninvoiced receipts.  (See program BAL001CL.)  Every G/L transaction is scrutinized, and the PO number(s) it refers to, and was matched with, is(are) found. All PO transactions that net out to zero, or that balance to uninvoiced receipts, are removed.  The remaining transactions are shown in 'Out of Balance Unmatched POs' in InfoNet (Under uniVIEW / Finance).
 [[File:unmatPO-3.jpg]]

@@ Line 26: / Line 26: @@
 [[File:unmatPO-2.jpg]]
-Line 5 shows that PO 8195055 has been received, but not invoiced.  The other lines show rounding errors.  As these net out to zero, you don't really have to do anything with them - but they will stay on this inquiry for two fiscal years.
+Line 5 shows that PO 8195055 has been received, but not invoiced.  The other lines show rounding errors.  As these net out to zero, you don't really have to do anything with them - but they will stay on this inquiry for two fiscal years.  They can be removed by doing an adjustment, with the PO number in the text field.  Next month the PO will net out to zero, so will be ignored.
 Use [[QUERY|Query]] on file SROTRAC (Entry pgm work file, G/L transactions) to look for transactions that have updated SROGRT (Goods reception transactions) but not the G/L file.

@@ Line 53: / Line 53: @@
 (AP - clearing (manual orders))
 (AP returns debit adj (2052))
 (AP car stock (2055))
 (AP costing debit note clearing)
 (AP PO matching clearing)
 (AP Non-PO matching clearing)

← Older revision		Revision as of 21:17, 30 November 2015
Line 49:		Line 49:

	==Technical==		==Technical==
		+
		+	As part of the month end, '''BAL001CL''' is run, which extracts all G/L entries for accounts -
		+
		+	210050 (AP - clearing (manual orders))
		+
		+	210300 (AP returns debit adj (2052))
		+
		+	210500 (AP car stock (2055))
		+
		+	210625 (AP costing debit note clearing)
		+
		+	220000 (AP PO matching clearing)
		+
		+	220050 (AP Non-PO matching clearing)
		+
		+	This includes all records; right from conversion - which as of November 2015 was three and a half million.
		+
		+	For some transactions, the document number is the PO number; but not all - so the program '''BAL001R''' tries to figure it out.
		+
		+	- The text on some generated transactions is vendor number, seven spaces, then the PO number.
		+
		+	- Some transactions have text that is the vendor invoice number then vendor name. The invoice number can be used to get the PO number from the A/P transaction files.
		+
		+	- If none of the above, the program searches the text for a seven digit number starting with '8'.
		+
		+	Hint - this process will work much more efficiently if finance ensures that every transaction entered conforms to one of these.
		+
		+	If all the transactions for a PO for the same G/L number add up to zero, '''BAL002R''' will delete them.
		+
		+	'''BAL005R''' will delete offsetting amounts for the same G/L number and PO - but only if there are not uneven multiples. For example, if there is a single 5.00, and a single -5.00 they will be deleted. If there are three 5.00 and three -5.00 they will be deleted. If there is one 5.00 and two -5.00 they will not be deleted.
		+
		+	If all transactions for PO's that were matched together add up to zero, they are deleted in '''BAL008R'''.
		+
		+	Transactions for PO's that agree with uninvoiced receipts are deleted in '''BAL009R'''.
		+
		+	PO's that were in process as of conversion will never balance; therefore '''BAL010R''' will delete them from the transactions.
		+
		+	When an adjustment is made to balance the account, it can not be applied against each PO or vendor that is out of balance, so they will all still be in the transaction file. If, after two fiscal years, the account balances to zero, all transactions will be deleted. '''BAL017R''' is run for each G/L account number. It will -
		+
		+	- Delete all transactions for period 201412 and earlier, as we know the account is in balance.
		+
		+	- If all transactions two fiscal years old or older add up to zero, delete them. If they don't add up to zero, work backwards to find a period end at which they did, then delete them. This shouldn't be a problem, as the account should be balanced at every year end.
		+
		+	===Reasons for Being Out of Balance===
		+
		+	Manual Orders are configured to debit and credit account 210050 both when they are received, and when they are invoiced. This means that the total of the transactions, posted and unposted, should always add up to zero. Between receiving time and invoicing time, there will be a value in 'received not invoiced'; but that is not a problem.
		+
		+	Account 210625 can be out of balance if finance matches a debit note with a PO before the adjustment PO has been closed.

@@ Line 16: / Line 16: @@
  RE receiving error                      .00        220050           .00
-A part of the month end process is to reconcile G/L transactions to the uninvoiced receipts.  (See program BAL001CL.)  Every G/L transaction is scrutinized, and the PO number it refers to, and was matched with, is found. All PO transactions that net out to zero, or that balance to uninvoiced receipts, are removed.  The remaining transactions are shown in 'Out of Balance Unmatched POs' in InfoNet.
+A part of the month end process is to reconcile G/L transactions to the uninvoiced receipts.  (See program BAL001CL.)  Every G/L transaction is scrutinized, and the PO number(s) it refers to, and was matched with, is(are) found. All PO transactions that net out to zero, or that balance to uninvoiced receipts, are removed.  The remaining transactions are shown in 'Out of Balance Unmatched POs' in InfoNet.
 [[File:unmatPO-3.jpg]]
 This shows
 - Total G/L transactions from file SROLOGGL that could not be net to zero, or balanced
 - Total uninvoice receipts from file SROGRT that could not be balanced
 - Total unposted batches from file SROTRAA that could not be balanced
 [[File:unmatPO-1.jpg]]
@@ Line 38: / Line 41: @@
 Line 5 shows that PO 8195055 has been received, but not invoiced.  The other lines show rounding errors.  As these net out to zero, you don't really have to do anything with them - but they will stay on this inquiry for two fiscal years.  They can be removed by doing an adjustment, with the PO number in the text field.  Next month the PO will net out to zero, so will be ignored.

@@ Line 15: / Line 15: @@
                     total       1,708,100.68        220000  1,708,100.91-         0.23-
  RE receiving error                      .00        220050           .00
 Some difference here are okay.  The [[Generated Journal Entries|journal entries]] for manual orders (MO) are set up to both debit and credit 210050 when they are received, and again when they are invoiced.  Therefore the G/L balance will always be zero; no matter what is in uninvoiced receipts (SROGRT).
 [[File:unmatPO-1.jpg]]

@@ Line 1: / Line 1: @@
 The first (and largest) query month end report (file name QPQUPRFIL) gives the detail of unmatched receiving (remaining amounts on file SROGRT).  The totals of the order types should match the G/L balances.  You should look up the G/L balances as soon as possible, as the accounting department can back date entries to the previous period.
- Order Type                           amount        G/L          balance    difference
+ '''Order Type                           amount        G/L          balance    difference'''
  MO manual order                    1,140.00        210050          0.00      1,140.00
  RT return to vendor                  170.00        210300        170.00-
@@ Line 16: / Line 16: @@
  RE receiving error                      .00        220050           .00
-Some difference here are okay.  The [[Generated Journal Entries|journal entries]] for manual orders (MO) are set up to both debit and credit 210050 when they are received, and again when they are invoiced.  Therefore the G/L balance will always be zero.
+Some difference here are okay.  The [[Generated Journal Entries|journal entries]] for manual orders (MO) are set up to both debit and credit 210050 when they are received, and again when they are invoiced.  Therefore the G/L balance will always be zero; no matter what is in uninvoiced receipts (SROGRT).
 Use [[QUERY|Query]] on file SROTRAC (Entry pgm work file, G/L transactions) to look for transactions that have updated SROGRT (Goods reception transactions) but not the G/L file.

@@ Line 1: / Line 1: @@
 The first (and largest) query month end report (file name QPQUPRFIL) gives the detail of unmatched receiving (remaining amounts on file SROGRT).  The totals of the order types should match the G/L balances.  You should look up the G/L balances as soon as possible, as the accounting department can back date entries to the previous period.
- '''Order Type'''                           '''amount'''        '''G/L'''          '''balance'''    '''difference'''
+ Order Type                           amount        G/L          balance    difference
- MO manual order                        0.00        210050          0.00
+ MO manual order                    1,140.00        210050          0.00      1,140.00
- RT return to vendor              135,776.75-       210300    135,776.75
+ RT return to vendor                  170.00        210300        170.00-
  CS car stock                           0.00        210500          0.00
  BE price variance-charge to H/O        0.00
  PV price variance-charge to rep        0.00
- QV quantity variance               2,728.86-
+ QV quantity variance               3,616.79-
-                    total           2,728.86-       210623      3,755.45      1,026.59
+                    total           3,616.79-       210623      2,521.14      1,095.65-
- PE EDI purchase order            790,769.36
+ PE EDI purchase order            693,407.41
- PO regular purchase order        350,847.95
+ PO regular purchase order      1,005,743.82
  RO rush order                          0.00
- XF reference purchase order        3,770.63
+ XF reference purchase order        8,949.45
-                    total       1,145,387,94        220000  1,145,388.17-         0.23-
+                    total       1,708,100.68        220000  1,708,100.91-         0.23-
- RE receiving error                   764.44-       220050        764.44
+ RE receiving error                      .00        220050           .00
 Some difference here are okay.  The [[Generated Journal Entries|journal entries]] for manual orders (MO) are set up to both debit and credit 210050 when they are received, and again when they are invoiced.  Therefore the G/L balance will always be zero.