Go to primary content
Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs
16.0.024
E89599-02
  Go To Table Of Contents
Contents

Previous
Previous
 
Next
Next
 

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