18 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 rough categories, processes that prepare future stock counts and processes that process results for today's stock counts. The programs stkschedxpld.pc and stkxpld.pc prepare future stock counts. All other programs process results from today's stock counts.

For more information about Stock Counts, including the interaction of UI and batch processes and data flow, see the Oracle Retail Merchandising Functional Library (Doc ID: 1585843.1).

Note: he White Papers in this library are intended only for reference and educational purposes and may not reflect the latest version of Oracle Retail software.

Batch Design Summary

The following batch designs are included in this functional area:

• stkschedxpld.pc (Create Stock Count Requests Based on Schedules)

• stkxpld.pc (Explode Stock Count Requests to Item Level)

• lifstkup.pc (Conversion of RWMS Stock Count Results File to RMS Integration Contract)

• stockcountupload.ksh (Upload Stock Count Results from Stores/Warehouses)

• stockcountprocess.ksh (Process Stock Count Results)

• stkupd.pc (Stock Count Snapshot Update)

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

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

• stkprg.pc (Purge Aged Stock Count)

lifstkup (Conversion of RWMS Stock Count Results File to RMS Integration Contract)

Module Name	lifstkup.pc
Description	Conversion of RWMS Stock Count Results File to RMS Integration Contract
Functional Area	Stock Counts
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS150

Design Overview

The Stock Upload Conversion batch is used when RWMS sends count information to RMS. This batch converts the inventory balance upload file into the format supported by the Stock Count Upload process.

Scheduling Constraints

Table 18-1 Scheduling Constraints

Schedule Information	Description
Scheduling Considerations	This program should run before stockcountupload.ksh and after the warehouse management's inv_bal_upload.sh program.
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA - file based processing

Restart/Recovery

Oracle Retail standard file-based restart/recovery is used. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The recommended commit counter setting is 1000 records (subject to change based on implementation).

Key Tables Affected

Table 18-2 Key Tables Affected

Table	Select	Insert	Update	Delete
WH	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
STAKE_HEAD	Yes	No	No	No
STAKE_LOCATION	Yes	No	No	No

Input/Output Specification

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000172 (input from RWMS) IntCon000102 (output for RMS stockcountupload)

Input File Layout

Table 18-3 Input File Layout

Field Name	Field Type	Description
DC_DEST_ID	11 – Number (10) + 1 for trailing space	Unique identifier for the warehouse
TRANSACTION_DATE	15 – Date (14) + 1 for trailing space	Date on which the transaction occurred
ITEM_ID	26 - Varchar2 (25) + 1 for trailing space	Uniquely identifies the item on the count
AVAILABLE_QTY	15 – Number (12) + 1 for leading sign and + 1 for decimal and + 1 for trailing space	Units available for distribution
DISTRIBUTED_QTY	14 – Number (12) + 1 for decimal and + 1 for trailing space	Units distributed include: Units distributed but not yet picked, units picked but not yet manifested, units manifested but not yet shipped
RECEIVED_QTY	15 - Number (12) + 1 for leading sign and + 1 for decimal and + 1 for trailing space	Units received but not put away
TOTAL_QTY	14 – Number (12,4) + 1 for decimal and + 1 for trailing space	Sum of all units that physically exist: container status of: I, D, M, R, T, X
AVAILABLE_WEIGHT	15 – Number (12,4) + 1 for leading sign + 1 for decimal + 1 for trailing space	Weight available for distribution of catch weight items
RECEIVED_WEIGHT	14 – Number (12,4) + 1 for decimal + 1 for trailing space	Weight received but not put away for catch weight items
DISTRIBUTED_WEIGHT	14 – Number (12,4) + 1 for decimal + 1 for trailing space	Weight distributed includes: weight distributed but not yet picked, weight picked but not yet manifested, weight manifested but not yet shipped (value only catch weight items)
TOTAL_WEIGHT	13 – Number (12,4) + 1 for decimal	Sum of all weight that physically exist: container status of: I, D, M, R, T, X. For catch weight items

Output File Layout

Table 18-4 Output File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	file type record descriptor	Char (5)	FHEAD	Describes the file line type
	file line identifier	Number (10)	0000000001	ID of current line being processed
	file type	Char (4)	’STKU'	Identifies the file type
	stocktake_date	Date (14)	NA	The date on which the count occurred, formatted as YYYYMMDDHH24MISS
	file create date	Date (14)	NA	Date on which the file was created, formatted as YYYYMMDDHH24MISS
	cycle count	Number (8)	NA	stake_head.cycle_count
	Location type	Char (1)	’W'	Will always be ’W', as this process is only executed for warehouse locations
	location	Number(10)	NA	Indicates the number of the physical warehouse where the count occurred
FDETL	file type record descriptor	Char(5)	FDETL	Identifies the file line type
	file line identifier	Number(10)	NA	ID of current line being processed, internally incremented
	Item type	Char(3)	’ITM'	Indicates the type of item that was counted. This will always be ’ITM', indicating a transaction level item
	item value	Char(25)	NA	The ID of the item that was counted
	inventory quantity	Number(12)	NA	The total quantity or weight of product counted; includes four implied decimal places
	location description	Char(150)	NA	Used by RMS to determine the location where the item was counted. This program will always leave as NULL
FTAIL	file type record descriptor	Char(5)	FTAIL	Identifies the file line type
	file line identifier	Number(10)	NA	ID of current line being processed, internally incremenated
	file record count	Number(10)	NA	Indicates the number of detail records

Design Assumptions

NA

stockcountupload.ksh (Upload Stock Count Results from Stores/Warehouses)

Module Name	stockcountupload.ksh
Description	Upload Stock Count Results from Stores/Warehouses
Functional Area	Stock Count
Module Type	Integration
Module Technology	ksh
Catalog ID	RMS153
Runtime Parameters	NA

Design Overview

The purpose of this module is to upload the contents of the stock count file, which contains the results of a count that occurred in a store or warehouse, to staging tables for further processing.

Scheduling Constraints

Table 18-5 Scheduling Constraints

Schedule Information	Description
Frequency	Run after liftstkup.pc
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA

Key Tables Affected

Table 18-6 Key Tables Affected

Table	Select	Insert	Update	Delete
SVC_STKUPLD_FHEAD	Yes	Yes	Yes	Yes
SVC_STKUPLD_FDETL	Yes	Yes	Yes	Yes
SVC_STKUPLD_STATUS	Yes	Yes	Yes	Yes

Input/Output Specification

Integration Type	Upload in RMS
File Name	Determined by runtime parameter
Integratin Contract	IntCon000102

Input File Layout

Table 18-7 Input File Layout

Record Name	Field Name	Field Type	Default Value	Description
File Header	File head descriptor	Char(5)	FHEAD	Describes file line type
	File line identifier	Number(10)	0000000001	ID of current line being processed
	File Type	Char(4)	STKU	Identifies the file type
	File create date	Char(14)	NA	Indicates the date the file was created in YYYYMMDDHH24MISS format
	Stock take date	Char(14)	NA	Date on which stock count will take place in YYYYMMDDHHMISS format
	Cycle count	Number (8)	NA	Unique number to identify the stock count
	Location Type	Char(1)	NA	Indicates the type of location where the count occurred. Valid values are ’S','W','E'.
	Location	Number(10)	NA	The location where the stock count occurred
Transaction Record	File record descriptor	Char(5)	FDETL	Describes file line type
	Line Number	Number(10)	NA	Sequential file line number
	Item type	Char(3)	NA	Indicates the type of item counted – either transaction level (ITM) or reference item (REF)
	Item value	Char(25)	NA	Unique identifier for item that was counted
	Inventory quantity	Number(12)	NA	Total quantity counted for the item at the location formatted with 4 implied decimal places
	Location description	Char(150)	NA	Description of inventory location (such as,. sales floor, backroom)
FTAIL	File record descriptor	Char(5)	FTAIL	Marks end of file
	File line identifier	Number(10)	NA	ID of current line being processed, internally incremented
	File record count	Number(10)	NA	Number of detail records

Design Assumptions

This program uses grep to search log files for errors. The GREP function should point to the /usr/xpg4/bin/ directory instead of /usr/bin directory to utilize the ”-E” option. Otherwise, it will fail with an ”illegal option” error message.

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

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
Runtime Parameters	NA

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. The month end stock ledger batch process (saldly) then uses these values when calculating ending inventory for the month.

Scheduling Constraints

Table 18-8 Scheduling Constraints

Schedule Information	Description
Frequency	Run before salweek.pc and salmth.pc
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	Threaded by department

Restart/Recovery

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

Key Tables Affected

Table 18-9 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
STAKE_PROD_LOC	Yes	No	Yes	No
STAKE_HEAD	Yes	No	No	No
DEPS	Yes	No	No	No
HALF_DATA_BUDGET	Yes	No	No	No
DAILY_DATA	Yes	No	No	No
WEEK_DATA	No	No	Yes	No
MONTH_DATA	Yes	No	Yes	No
HALF_DATA	No	No	Yes	No
DAILY_DATA_TEMP	No	Yes	No	No

Design Assumptions

NA

stkprg (Purge Aged Stock Count)

Module Name	stkprg.pc
Description	Purge Stock Count
Functional Area	Stock Counts
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS360
Runtime Parameters	NA

Design Overview

Purge Stock Counts is a data cleanup process to remove old counts from RMS. This batch process deletes records from the stock count tables with a stock take date earlier than the last EOM start date (SYSTEM_VARIABLES.LAST_EOM_START_MONTH) or those that have been otherwise flagged for delete. This process deletes records from STAKE_HEAD and all corresponding child tables, including STAKE_SKU_LOC and STAKE_PROD_LOC.

Scheduling Constraints

Table 18-10 Scheduling Constraints

Schedule Information	Description
Frequency	NA
Scheduling Considerations	NA
Pre-Processing	prepost stkptg post
Post-Processing	Threaded by location

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 STAKE_HEAD and STAKE_PRODUCT 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 STAKE_HEAD and STAKE_PRODUCT can only be deleted in the post program when all the details have been deleted.

Key Tables Affected

Table 18-11 Key Tables Affected

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

NA

stkschedxpld (Create Stock Count Requests Based on Schedules)

Module Name	stkschedxpld.pc
Description	Create Stock Count Requests Based on Schedules
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ProC
Integration Catalog ID	NA
Runtime Parameters	NA

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 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 (STAKE_REVIEW_DAYS).

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

Scheduling Constraints

Table 18-12 Scheduling Constraints

Schedule Information	Description
Scheduling Considerations	Run before stkxpld.pc
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Multi-threaded by location (store and warehouse)

Restart/Recovery

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

Key Tables Affected

Table 18-13 Key Tables Affected

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

NA

stkupd (Stock Count Snapshot Update)

Module Name	stkupd.pc
Description	Stock Count Snapshot Update
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ProC
Integration Catalog ID	RMS362
Runtime Parameters	NA

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.

Scheduling Constraints

Table 18-14 Scheduling Constraints

Schedule Information	Description
Scheduling Considerations	stkxpld should run prior to this program
Pre-Processing	prepost stkupd pre
Post-Processing	NA
Threading Scheme	Threaded by location

Restart/Recovery

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

Key Tables Affected

Table 18-15 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
STAKE_SKU_LOC	Yes	No	Yes	No
STAKE_HEAD	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No

Design Assumption

NA

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

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
Runtime Parameters	NA

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 TRAN_DATA records for any variances to tran code 22. For Unit & Value counts, it also computes the total cost and total retail value of the count and updates STAKE_PROD_LOC with this information.

Scheduling Constraints

Table 18-16 Scheduling Constraints

Schedule Information	Description
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Threaded by location

Restart/Recovery

The logical unit of work for this program is item, loc_type and location. This program is multithread using the v_restart_all_locations view. After the commit_max_ctr 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.

Key Tables Affected

Table 18-17 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
ITEM_XFORM_HEAD	Yes	No	No	No
ITEM_XFORM_DETAIL	Yes	No	No	No
STAKE_SKU_LOC	Yes	No	Yes	No
STAKE_CONT	Yes	No	No	Yes
STAKE_HEAD	Yes	No	No	No
STAKE_CONT_TEMP	Yes	Yes	No	Yes
STAKE_PROD_LOC	Yes	No	Yes	No
WH	Yes	No	No	No
CLASS	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	Yes	No
ITEM_SUPP_COUNTRY	Yes	No	No	No
EDI_DAILY_SALES	No	No	Yes	No
TRAN_DATA	No	Yes	No	No
NWP	No	Yes	Yes	No
NWP_FREEZE_DATE	Yes	No	No	No
STAKE_QTY	Yes	No	No	No
STAKE_LOCATION	Yes	No	No	No
STAKE_PRODUCT	Yes	No	No	No
STORE	Yes	No	No	No
VAT_ITEM	Yes	No	No	No

Design Assumption

NA

stkxpld (Explode Stock Count Requests to Item Level)

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
Runtime Parameters	NA

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 (STAKE_LOCKOUT_DAYS).

The batch process picks up product groups (departments, classes or subclasses) from STAKE_PRODUCT and inserts records into STAKE_SKU_LOC and STAKE_PROD_LOC (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.

Scheduling Constraints

Table 18-18 Scheduling Constraints

Schedule Information	Description
Scheduling Considerations	This batch should run prior to prepost stkupd pre
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Threaded by location

Restart/Recovery

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

Key Tables Affected

Table 18-19 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
STAKE_LOCATION	Yes	No	No	No
STAKE_HEAD	Yes	No	No	No
STAKE_SKU_LOC	Yes	Yes	No	No
STAKE_PROD_LOC	Yes	Yes	No	No
STAKE_PRODUCT	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
ITEM_XFORM_HEAD	Yes	No	No	No
ITEM_XFORM_DETAIL	Yes	No	No	No
SUBCLASS	Yes	No	No	No

Design Assumption

NA

stockcountprocess.ksh (Process Stock Count Results)

Module Name	stockcountprocess.ksh
Description	Process Stock Count Results
Functional Area	Stock Counts
Module Type	Business Processing
Module Technology	ksh
Integration Catalog ID	RMS366
Runtime Parameters	NA

Design Overview

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

Scheduling Constraints

Table 18-20 Scheduling Constraints

Schedule Information	Description
Scheduling Considerations	Run after stockcountupload.ksh
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	The number of threads running in parallel is based on value in the column RMS_PLSQL_BATCH_CONFIG.MAX_CONCURRENT_THREADS with the program name ”CORESVC_SALES_UPLOAD_SQL”. Threading is based on chunks. Each chunk would have a defined size. This is defined in RMS_PLSQL_BATCH_CONFIG.MAX_CHUNK_SIZE. Chunks could be made up of a single or multiple THEAD/Items. Because multithreading logic based on chunks is applied, it is possible that a record is locked by another thread. Without a mechanism to perform waiting/retrying, record locking errors would happen more frequently In the table RMS_PLSQL_BATCH_CONFIG, RETRY_LOCK_ATTEMPTS contains the number of times the thread will try to acquire the lock for a table and RETRY_WAIT_TIME is the number of seconds the thread will wait before it retries

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.

Key Tables Affected

Table 18-21 Key Tables Affected

Table	Select	Insert	Update	Delete
STK_FILE_STG	Yes	Yes	No	No
STAKE_SKU_LOC	Yes	Yes	Yes	No
STK_SSL_TEMP	Yes	Yes	No	No
STAKE_QTY	Yes	Yes	Yes	Yes
WH	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
STK_SSL_TEMP	Yes	Yes	No	No
STK_XFORM_TEMP	Yes	Yes	No	No
STAKE_PROD_LOC	Yes	No	No	No
STAKE_PRODUCT	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
STAKE_PROD_LOC	Yes	No	No	No
ITEM_XFORM_DETAIL	Yes	No	No	No
ITEM_XFORM_HEAD	Yes	No	No	No
STK_XFORM_ORD_TEMP	Yes	Yes	No	No
STAKE_LOCATION	Yes	Yes	No	No
PARTNER	Yes	No	No	No
STAKE_HEAD	Yes	No	No	No
STK_DUP_SQT_TEMP	Yes	Yes	No	No
WORK_STKUPLD_STAKE_QTY_GTT	Yes	Yes	Yes	Yes
WORK_STKUPLD_ITEM_LOC_GTT	Yes	Yes	Yes	Yes

Design Assumption

NA