Stock Count

Program Summary

The following batch designs are included in this functional area:

See Merchandising Operations Guide Volume 2 for details on the following stock count integration programs:

Conversion of Warehouse Stock Count Results File to Merchandising Integration Contract (lifstkup.pc)
Upload Stock Count Results from Stores/Warehouses (stockcountupload.ksh)

Calculate Actual Current Shrinkage and Budgeted Shrink to Apply to Stock Ledger (stkdly)

Module Name	stkdly.pc
Description	Calculate Actual Current Shrinkage and Budgeted Shrink to Apply to Stock Ledger
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS359
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The Stock Count Shrinkage Update batch calculates the 'value' variances for Unit & Value stock counts. The main functions are to calculate actual shrinkage amount that is used to correct the book stock value on the stock ledger and to calculate a budgeted shrinkage rate that will be applicable until the next count. Additionally, future transaction data snapshots are aggregated and stored into a table which will be used for shrinkage calculations in month end stock ledger batch process. The month end stock ledger batch process then uses these values when calculating ending inventory for the month.

Restart/Recovery

This batch program is multithreaded using the restart department view. The logical unit of work for this program is department/class/location.

Design Assumptions

N/A

Create Stock Count Requests Based on Schedules (stake_sched_explode_job)

Module Name	stake_sched_explode_job
Description	Create Stock Count Requests Based on Schedules
Functional Area	Stock Counts
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from stake count schedule and store or location list tables based on its review criteria from system parameter settings. The Stake Count Review Days parameter will determine and evaluate scheduled counts that are planned for x days from the current day. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will create stock count requests based in the pre-defined schedules for a location. For Unit counts, the item list specified is exploded out to the transaction-level and written to the count/item/location table. For Unit & Value counts, the transaction-level items contained in the specified department/class/subclass will be written to the count/item/location and count/product/location tables. If the schedule was created using a location list, then this process also explodes that down to the store or virtual warehouse level. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 16-1 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_STKSCHED_EXPLODE_STG	Yes	Yes	No	Yes
STAKE_SCHEDULE	Yes	No	Yes	No
V_RESTART_STORE_WH	Yes	No	No	No
PERIOD	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
STAKE_HEAD	No	Yes	No	No
STAKE_LOCATION	No	Yes	No	No
STAKE_PRODUCT	No	Yes	No	No
STAKE_PROD_LOC	No	Yes	No	No
STAKE_SKU_LOC	Yes	Yes	No	No
ITEM_MASTER	Yes	No	No	No
DEPS	Yes	No	No	No
SUBCLASS	Yes	No	No	No
PACKITEM	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
SKULIST_DETAIL	Yes	No	No	No
LOC_LIST_DETAIL	Yes	No	No	No
LOCATION_CLOSED	Yes	No	No	No
COMPANY_CLOSED	Yes	No	No	No
INV_TRACK_UNIT_OPTIONS	Yes	No	No	No

Design Assumption

N/A

Create Stock Count Requests Based on Schedules (stkschedxpld)

Module Name	stkschedxpld.pc
Description	Create Stock Count Requests Based on Schedules
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ProC
Catalog ID	N/A
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch process is used to create stock count requests based on pre-defined schedules for a location. It evaluates all scheduled counts, that are planned for x number of days from the current day. The number of days prior to the planned count date by which the count requests are created is determined by the system parameter Stock Count Review Days.

For Unit counts, the item list specified is exploded out to the transaction-level and written to the count/item/location table. For Unit & Value counts, the transaction-level items contained in the specified department/class/subclass will be written to the count/item/location table and count/product/location tables. If the schedule was created using a location list, then this process also explodes that down to the store or virtual warehouse level.

Restart/Recovery

The logical unit of work for this module is schedule, location. The changes will be posted when the maximum commit counter value is reached.

Design Assumption

N/A

Explode Stock Count Requests to Item Level (stkxpld)

Module Name	stkxpld.pc
Description	Explode Stock Count Requests to Item Level
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS364
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The Stock Count Explode batch is a nightly batch is used to explode stock count requests created at the department, class or subclass level to the item level. This process must run before the stock count snapshot is taken and is run for counts x days prior to the count based on the system parameter setting, Stock Count Lockout Days.

The batch process picks up product groups (departments, classes or subclasses) from the count/product table and inserts records into the count/item/location table and the count/product/location table (for Unit & Value counts) for all items in the product group that exist for the locations on the count. Only approved inventoried items are added to stock counts.

For transformable items, both the non-inventoried sellable items and inventoried orderable items that are contained in a product group will also be added to the count. For deposit items, only the content, crate and packs can be counted.

Restart/Recovery

This batch program is multithreaded using the restart all locations view. The logical unit of work for this program is a cycle count/location.

Design Assumption

N/A

Process Stock Count Results (stockcountprocess.ksh)

Module Name	stockcountprocess.ksh
Description	Process Stock Count Results
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ksh
Integration Catalog ID	RMS366
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The Stock Count Process batch processes actual count data from the selected store or physical warehouse to count/item/location table from the data staged by STOCKCOUNTUPLOAD.KSH. For a physical warehouse, this process also calls the Merchandising distribution library to apportion quantities to the virtual warehouses in Merchandising.

Restart/Recovery

The logical unit of work for stockcountprocess.ksh is a set of a single or multiple valid items at a given location. This set is defined as a chunk. Based on the example above, if for some reason, chunk 2 raised an error, INPUT FILE 6, 7, and 8 wouldn't be processed by this program. Other chunks, if there are no errors, would be processed. User has to correct the transaction details and upload the input file again that includes the affected CHUNKS for reprocessing.

Design Assumption

N/A

Purge Aged Stock Count (stkprg)

Module Name	stkprg.pc
Description	Purge Stock Count
Functional Area	Stock Counts
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS360
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

Purge Stock Counts is a data cleanup process to remove old counts from Merchandising. This batch process deletes records from the stock count tables with a stock take date earlier than the last end of month start date or those that have been otherwise flagged for delete. This process deletes records from stock count header and all corresponding child tables.

Restart/Recovery

This program is multi-threaded based on location and the logic of restart and recovery is based on cycle count and location. The deletion of stock count header and stock count product tables is performed in prepost as a post action.

This is done because stkprg is multi-threaded and each thread may have only deleted part of cycle count detail records; hence the records from stock count head and stock count product can only be deleted in the post program when all the details have been deleted.

Design Assumption

N/A

Purge Aged Stock Count (stock_count_purge_job)

Module Name	stock_count_purge_job
Description	Purge Stock Count
Functional Area	Stock Counts
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two-step processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from stock count header table based on its purge criteria from system variable settings. The Last End-of-Month Start Month parameter will determine records with earlier stock take date or those that have been flagged for deletion. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from stock count related tables. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 16-2 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_VARIABLES	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_STOCK_COUNT_PURGE_STG	Yes	Yes	No	Yes
STAKE_LOCATION	Yes	No	No	Yes
STAKE_QTY	No	No	No	Yes
STAKE_CONT	No	No	No	Yes
STAKE_SKU_LOC	No	No	No	Yes
STAKE_PROD_LOC	No	No	No	Yes
STAKE_PRODUCT	No	No	No	Yes
STAKE_HEAD	Yes	No	No	Yes

Design Assumption

N/A

Stock Count Snapshot Update (stkupd)

Module Name	stkupd.pc
Description	Stock Count Snapshot Update
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ProC
Integration Catalog ID	RMS362
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The Stock Count Snapshot Update is a nightly batch program used to take a ‘snapshot' of inventory, cost and retail values prior to the count commencing. This will be used to calculate the book value of the count. The stock count snapshot includes stock on hand, in-transit-qty, cost (either WAC or standard cost, based on system settings) and retail for each item-location record. The snapshot is taken on the day that the count is scheduled. Additionally, transaction data snapshots of future-dated transactions are captured and stored in a table that will be used by Stock Count Shrinkage Update batch.

Restart/Recovery

This program is multithread using the restart all locations view. The logical unit of work is an item/location.

Design Assumption

N/A

Update Stock On Hand Based on Stock Count Results (stkvar)

Module Name	stkvar.pc
Description	Update Stock On Hand Based on Stock Count Results
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ProC
Integration Catalog ID	RMS363
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The Stock Count Stock on Hand Updates batch process updates stock on hand based on the unit count results. For Unit counts, it also writes transaction data records for any variances to transaction code 22. For Unit & Value counts, it also computes the total cost and total retail value of the count and updates the count/product/location table with this information. The post processing for this batch inserts dept/class/subclass/location records into the week, month and half data tables in cases wherein they don't exist.

Restart/Recovery

The logical unit of work for this program is item, location type and location. This program is multithread using the restart stock count view. After the maximum commit counter number of rows is processed, intermittent commits are done to the database and the item/location information is written to restart tables for restart/recovery.

Design Assumption

N/A