Information Systems:ASW Warehouse Balances - Discrepancies
Overview
Four files are involved -
SROSRO - Warehouse balances - totals by warehouse / item SROITR - Inventory transactions - detail of everything that has updated SROSRO WHOLOP - Location occupancy (location balance) - totals by warehouse / item / location WHOLTR - Location transactions - detail of everything that has updated WHOLOP
We have two inventory systems running. One very basic that just keeps track of purchase orders received (items coming from or going to suppliers), sales orders invoiced (items going to or coming from stores), adjustments, and transfers between warehouses. These are files that begin with SR for 'stock room'. The other is more complex, and keeps track of the above, plus every movement of an item within the warehouse (files that begin with WH for 'warehouse').
The system is validated every night by summarizing WHOLOP by warehouse and item then comparing the results to SROSRO. See Location Balance Integrity Report for details.
It is also important that the summaries of the transaction files agree with the balance files. Some displays and reports use these, especially some for Health Canada - so they had better be accurate. Every Sunday queries are run that check this. See the SUMCOMPx sections in this document.
If any corrections are made to either of the balance files, also correct the transactions!
Location Balance Integrity Report
Every night, the totals of the warehouse/item file (SROSRO) are compared to the totals in the warehouse/item/location file (WHOLOP).
To check this, look in the end of day reports (wrksplf eodjob). Page down until you get to the held reports (Sts = HLD). Look for file WMR251P. Two of these are produced every night; one for MAI and one for RET. If the number of pages is 1, there are no discrepancies, and you can delete the file.
Display Spooled File
File . . . . . : WMR251P Page/Line 1/6
Control . . . . . B Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Selection
Differences only.... Y
Warehouse........... RET
Item................ all
Remarks............. SHEILAV
*VA/480B* L O C A T I O N B A L A N C E I N T E G R I T Y 6/05/14 8:46:17 WMR251P
Page 2
- - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Warehouse RET Returns Warehouse
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ASW-DIS BATCH ASW-WHM
Item / Description On hand On hand Difference On hand Difference Stock category
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
00665083 PMS-FLUOXETINE 10MG CAPSULES
Warehouse....... 41.28 ----------- ----------- 47.28 6.00 On Hand
02094803 ALTACE HCT 5/25MG TABLETS
Warehouse....... 4.17 ----------- ----------- 3.17 1.00- On Hand
*** End of list ***
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
Reasons for Differences
If there is a difference, it must be corrected. The problems are mostly in RET, with three usual causes.
A - Cancelling a return sales order (which removes the quantity from the location it was added to) after the product has been moved to a different location. The original location will go negative; which will cause it to be removed from WHOLOP (Location occupancy).
B - Cancelling the session (X’ing out) while creating a credit note from a hot line incident. This will update SROSRO (Warehouse balances) but not WHOLOP(Location occupancy), and leave a temporary order in SROITR.
C - Sometimes temporary orders are left in SROCSH (Copy sales order / order header) & SROCSP (Copy sales order/item lines). (Hmm. I think this may be related to the previous point.) When that temporary number is used again, the old items are included with the new order when WHOLOP(Location occupancy) and WHOLTR (Location transactions) are updated (I think only these two files are affected).
A - Cancelling a return sales order after the product has been moved to a different location
This will remove the quantity from the location it was originally added to, which will make it go negative. During the overnight processing, location records with zero or negative onhands are deleted from WHOLOP (Location occupancy).
To see this, go into location transaction inquiry. Key in the warehouse and item from the report, press enter, then press page back to get the most recent transactions.
*VA/480B* Location transaction enquiry 15:48:47 WMR16001
-------------------------------------------------------------------------------
Date Whs Zn Location Item Trans. Qty.
_ 03/25/14 RET 88 MAI-RETN 00665083 PMS-FLUOXETINE 10MG CAPSUL 39.00
_ 03/25/14 RET 88 MAI-RETN 00665083 PMS-FLUOXETINE 10MG CAPSUL 39.00-
_ 03/25/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 39.00
_ 04/09/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 1.00
_ 05/05/14 RET 88 MAI-RSTK 00665083 PMS-FLUOXETINE 10MG CAPSUL 2.00
_ 05/07/14 RET 88 MAI-RSTK 00665083 PMS-FLUOXETINE 10MG CAPSUL 2.00-
_ 05/07/14 RET 99 BRYANZ 00665083 PMS-FLUOXETINE 10MG CAPSUL 2.00
_ 05/07/14 RET 99 BRYANZ 00665083 PMS-FLUOXETINE 10MG CAPSUL 2.00-
_ 05/15/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 0.25
_ 05/28/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 1.00
_ 06/02/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 0.18
_ 06/03/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 4.00
_ 06/05/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 1.00
_ 06/05/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 1.00
_ 06/06/14 RET RE A417 00665083 PMS-FLUOXETINE 10MG CAPSUL 40.00-
-------------------------------------------------------------------------------
Date Whs Zn Location Item
______ RET __ ________ 00665083
F1=Shift info
B - Cancelling the session (X’ing out) while creating a credit note from a hot line incident
This will update SROSRO (Warehouse balances) but not WHOLOP(Location occupancy), and leave a temporary order in SROITR (Inventory transactions).
Use Information Systems:DFU on file SRBITR (Inventory transactions) in library UP1480BFVA to find any temporary orders are causing the problem. Key SOC for ‘Inventory event code’ and 9000000 for ‘Ord no’, then press enter and page down. If the record shown is inventory code SOC, has an order number greater than 9000000, is dated yesterday or before, and is for an item on the Location Balance Integrity report, It has to be corrected.
On another session, start Information Systems:DFU for file SRBSRO (Warehouse balances) in library UP1480BFVA. Key in the item number and warehouse from file SRBITR (Inventory transactions) and press enter. Press enter to see more data fields. Look at 'Qty del not inv', which is 'Quantity delivered not invoiced'. In this case it is negative, so it actually means 'received not credited'.
Note that all the quantity fields have three decimal positions, but that DFU does not show the decimal point. The '1000' shown is actually '1.000'.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : SRO File . . . . : SRBSRO
Qty del not inv: 1000-
Tr qty del not inv: ________________
Qty on supply orders: ________________
Min qty: ________________
Disp qty: ________________
Iss YTD: 2
Iss LY: 9
Qty iss YTD: 2000-
Qty iss LY: 9490-
Date: ________
Rec YTD: _____
Rec LY: _____
Qty rec YTD: ________________
Qty rec LY: ________________
Rec dte: ________
Ret YTD: _____
Ret LY: 5
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
The quantity on hand (shown on the previous screen) must be reduced by the quantity on SRBITR, and the quantity delivered not invoice must be increased. Note that the sum of these two fields added together must remain the same.
Now you can go back to SRBITR (Inventory transactions) and press F23 twice to delete the record. Now all four warehouse files are in balance for this item. (SROITR – Inventory transactions, SROSRO – Warehouse balance, WHOLTR – Location transactions, and WHOLOP – Location occupancy.)
Continue this process for all records in SRBITR (Inventory transactions) with inventory code SOC, order number greater than 9000000, and dated yesterday or before. As you correct items, mark them on the Location Balance Integrity report so you will if there are still items out of balance after you correct everything on the incomplete temporary orders.
SUMCOMP1 - Compare Warehouse Transactions to Location Transactions
These should be the same. One exception is shipments that have been received but not closed. This means items are in the transactions and balances for the locations, but not for the warehouse.
These quantities will be shown in Held Location Balances.
The only items to be concerned about are the once that aren't on both these reports.
To find the problem, start QUERY on two different sessions; one on file SROITR for the warehouse / item, and the other on file WHOLTR for the warehouse / zone / location / item. Total both by transaction quantity. Keep changing the date range being included in each query until you find the difference.
SUMCOMP2 - Compare Location Transactions to Location Balances
The first thing is to figure out which file is correct - transactions (WHOLTR) or balance (WHOLOP). The way to do that is to go to the location and count the number of items there and compare with the current location balance.
SUMCOMP3 - Held Location Balances
This is a list of item / location balances that are held; meaning that although the items have been received, and maybe even moved to the picking location, they are not available to sell until the shipment has been closed (which is when they add to the warehouse item balance file).