Late Posted Shipment Receipts on a Closed Count Make Perpetual and Financial Inventory Go out of Sync (Doc ID 2116514.1)

Last updated on MARCH 21, 2016

Applies to:

Oracle Retail Merchandising System - Version 14.0.2 and later
Information in this document applies to any platform.

Symptoms

Actual Behavior
---------------

Whenever a Late Polled Distribution Center (DC)-to-Store allocation Shipment Receipt is posted in RMS on a closed "unit and amount" count, the perpetual and financial inventory go out of sync between beginning of period and end of period.

Expected Behavior
-----------------------

Ideally the net increment in inventory value in ITEM_LOC_SOH (AV_COST * (STOCK_ON_HAND + IN_TRANSIT_QTY)) should match with the net increment in transactional cost.

Steps to Reproduce
-----------------------

1. Select 4 SKUs SKU1, SKU2, SKU3 and SKU4 at a store in such a way that their Weighted Average Cost (WAC) at store is different from the WAC at DC.
1a. Also, let their Stock on Hand (SOH) at store be 100 each. Their in_transit at store be ZERO each.
1b. Let vdate be D.
1c. Let tsf_force_close_ind be set as ‘RL’ on SYSTEM_OPTIONS table.
1d. Let RMS system be running on Weighted Average Cost, that is, std_av_ind = ‘A’ on SYSTEM_OPTIONS table.

2. Capture the Beginning Of Period (BOP) Perpetual Inventory (PI), for the SKUs at the store, as following
BOP PI1 for SKU1 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH
BOP PI2 for SKU2 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH
BOP PI3 for SKU3 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH
BOP PI4 for SKU4 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH

3. Prepare allocations for these 4 SKUs from DC to Store with 50 units as allocated Quantity (Qty) for all four items

4. Schedule a Unit and Amount stock count for the store with stock count date as vdate+1 that is D+1

5. Explode the count by running STKXPLD.PC (Stock Count Explode) batch.

6. Run DTESYS.PC (Increment Set System Date) batch to reflect new vdate = D+1

7. Take a snapshot of the count by running STKUPD.PC (Stock Count Snapshot) batch.

8. Prepare a physical count file with following Physical Count Qty (PCQ)
SKU1 with PCQ 150 units
SKU2 with PCQ 100 units
SKU3 with PCQ 125 units
SKU4 with PCQ 175 units

9. Upload the count file by running STOCKCOUNTUPLOAD.KSH (Upload Stock Count Results from Stores/Warehouses) and STOCKCOUNTPROCESS.KSH (Process Stock Count Results) batches.

10. Accept the stock count from RMS application. Accept both the units and the cost.

11. Run stock count adjustment job STKVAR.PC (Stock Count on Hand Updates). This will generate tran_code 41

12. Run stock count shrinkage job STKDLY.PC (Stock Count Shrinkage Update).

13. Run DTESYS.PC batch to reflect new vdate = D+2.

14. Publish allocation shipments in RMS for each of the four SKUs with shipped Qty as 50 units and with tran_date as D that is, tran_date of shipment records should be less than the stock count scheduled date
S1 -> SKU1 with 50 units
S2 -> SKU2 with 50 units
S3 -> SKU3 with 50 units
S4 -> SKU4 with 50 units

15. Publish Receipts (R) in RMS for each of the four shipments with tran_Date as D that is tran_date of receipt records should be less than the stock count scheduled date. The received Qty should be as following

R1 -> for Shipment S1, SKU1 with receipt Qty as 50 units. This reflects full receipt.
R2 -> for Shipment S2, SKU2 with receipt Qty as 0 unit. This reflects zero receipt.
R3 -> for Shipment S3, SKU3 with receipt Qty as 25 units. This reflects under receipt.
R4 -> for Shipment S4, SKU4 with receipt Qty as 75 units. This reflects over receit.

16. Capture the End Of Period (EOP) Perpetual Inventory (PI), for the SKUs at the store, as following
EOP PI1 for SKU1 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH
EOP PI2 for SKU2 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH
EOP PI3 for SKU3 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH
EOP PI4 for SKU4 = WAC * (STOCK_ON_HAND + IN_TRANSIT_QTY) from ITEM_LOC_SOH

17. Capture the TOTAL_COST of all the stock ledger impacting transaction from TRAN_DATA for each SKU and calculate the Net Transactional Cost (NTC) for each SKU as following

NTC1 for SKU1 = TC30 + TC 41 + TC 22 - TC 70 total_cost
NTC2 for SKU1 = TC30 + TC 41 + TC 22 - TC 70 total_cost
NTC3 for SKU1 = TC30 + TC 41 + TC 22 - TC 70 total_cost
NTC4 for SKU1 = TC30 + TC 41 + TC 22 - TC 70 total_cost

Note that if any other transaction, which impacts RMS stock ledger, is observed to be posted then consider it according to its role in stock ledger

18. Calculate the Net Perpetual Inventory (NPI) for each SKU as following
NPI1 = EOP PI1 - BOP PI1
NPI2 = EOP PI2 - BOP PI2
NPI3 = EOP PI3 - BOP PI3
NPI4 = EOP PI4 - BOP PI4

19. Compare the NPI and NTC for each SKU.
NPI1 = NTC 1
NPI2 = NTC 2
NPI3 = NTC 3
NPI4 = NTC 4

20. The NPI and NTC should match otherwise RMS inventory value is incorrect

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms