16 Stock Count

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

16

Stock Count

A stock count is a comparison of an inventory snapshot at a point in time to an actual inventory count received from a location. Stock count batch processes can be divided into two categories: processes that prepare future stock counts and processes that process results. The programs stkschedxpld and stkxpld prepare future stock counts. All other programs are involved in processing results.

For more information about Stock Counts, including the interaction of UI and batch processes and data flow see the Stock Count Overview in Merchandising Documentation Library (Doc ID: 1585843.1).

Program Summary

The following batch designs are included in this functional area:

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

• Create Stock Count Requests Based on Schedules (stake_sched_explode_job) - background job

• Create Stock Count Requests Based on Schedules (stkschedxpld)

• Explode Stock Count Requests to Item Level (stkxpld)

• Process Stock Count Results (stockcountprocess.ksh)

• Purge Aged Stock Count (stkprg)

• Purge Aged Stock Count (stock_count_purge_job) - background job

• Stock Count Snapshot Update (stkupd)

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

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_S TG	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

Table 16-1 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
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

---

In this guide

• Guide: Batch Operations Guide
• Previous: 15 Sales History
• Next: 17 Stock Ledger

Related chapters

• 3 Foundation Data Maintenance — Batch Operations Guide · shares COMPANY_CLOSED, LOCATION_CLOSED, LOC_LIST_DETAIL
• 2 Administration Batch — Batch Operations Guide · shares LOCATION_CLOSED, LOC_LIST_DETAIL, SKULIST_DETAIL, STAKE_SCHEDULE
• F Appendix: Publication Tables and Triggers — Merchandising Cloud Services Data Conversion Implementation Guide · shares CODE_DETAIL, COMPANY_CLOSED, LOCATION_CLOSED