Information Systems:ASW Warehouse Balances - Discrepancies
Warehouse Balance Discrepancies
Four files are involved -
SROSRO - Warehouse balances SROITR - Inventory transactions WHOLOP - Location occupancy WHOLTR - Location transactions
We have basically two inventory systems running. One very basic that just keeps track of purchase orders received, sales orders invoiced, and adjustments (files that begin with SR for 'stock room). The other is more more complex, and keeps track of every movement of an item within the warehouse (files that begin with WH for 'warehouse').
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 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 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.