14 Inventory

Most inventory process is in RMS are performed via the UI and near real time RIB integrations. However, some inventory related batch processes exist to manage inventory data.

Batch Design Summary

The following batch designs are included in this chapter:

• edidlprd.pc (Download Sales and Stock On Hand From RMS to Suppliers)

• ordinvupld.pc (Upload and Process Inventory Reservations from ReSA)

• wasteadj.pc (Adjust Inventory for Wastage Items)

• refeodinventory.ksh (Refresh End of Day Inventory Snapshot)

• invaprg.pc (Purge Aged Inventory Adjustments)

• customer_order_purge.ksh (Purge Aged Customer Orders)

edidlprd (Download Sales and Stock On Hand From RMS to Suppliers)

Module Name	edidlprd.pc
Description	Download Sales and Stock On Hand From RMS to Suppliers
Functional Area	Inventory
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS47
Runtime Parameters	NA

Design Overview

This program is used to transmit item level sales and stock on hand information to vendors. The report is a summary that will be sent to specified suppliers via EDI giving sales details, as well as current stock on hand and in transit for all locations for each of the items supplied by that supplier. Only those suppliers which have an EDI sales reporting frequency of either daily or weekly will have files generated by this program. The system parameter EDI Daily Report Lag is used for suppliers receiving daily updates to determine the day lag for sales data sent, to account for late posting sales.

Scheduling Constraints

Table 14-1 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	refeodinventory.ksh must run successfully prior to execution to ensure that ITEM_LOC_SOH_EOD is up-to-date
Pre-Processing	refeodinventory.ksh, prepost pre
Post-Processing	prepost post
Threading Scheme	Multi-threaded by supplier through the locking of EDI_SUPS_TEMP table for each supplier fetched

Restart/Recovery

Restart/recovery in this program is achieved through utilizing the global temporary table EDI_SUPS_TEMP. Once a supplier is processed, it is deleted from the EDI_SUPS_TEMP table to prevent the same supplier from being processed again during recovery.

Key Tables Affected

Table 14-2 Key Tables Affected

Table	Select	Insert	Update	Delete
SUPS	Yes	No	No	No
EDI_SUPS_TEMP	Yes	No	No	Yes
EDI_DAILY_SALES	Yes	Yes	Yes	No
PERIOD	Yes	No	No	No
COMPHEAD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
STORE	Yes	No	No	No
WH	Yes	No	No	No
ITEM_SUPP_COUNTRY	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_SUPPLIER	Yes	No	No	No
ITEM_LOC_HIST	Yes	No	No	No
ITEM_LOC_SOH_EOD	Yes	No	No	No
ITEM_SUPP_COUNTRY_LOC	Yes	No	No	No

I/O Specification

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000013

Output File Layout

Table 14-3 edidlprd.pc - Output File

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	File record descriptor	Char(5)	FHEAD	Describes record type
	Line number	Number(10)	0000000001	Sequential file line number
	File source	Char(5)	DLPRD	File Type
	File create date	Char(8)	NA	Date that the file was created in YYYYMMDD format
THEAD	File record descriptor	Char(5)	THEAD	Identifies record type
	Line number	Number(10)	NA	Sequential file line number
	Transaction number	Number(10)	NA	Sequential transaction number
	Report date	Char(8)	NA	For weekly reporting, this will contain the current date. For daily reporting, it will be the date represented by the sales, current date – lag days. Both will be in the YYYYMMDD format
	Supplier	Number(10)	NA	RMS Supplier Number
TITM	File record descriptor	Char(5)	TITM	Identifies file record type
	Line number	Number(10)	NA	Sequential file line number
	Transaction number	Number(10)	NA	Sequential transaction number
	Item	Char(25)	NA	Transaction level item to which with the data is related
	Item_Num_Type	Char(6)	NA	Contains the item number type for the item on ITEM_MASTER
	Ref_Item	Char(25)	NA	Contains the primary reference item for the item in the file, if defined
	Ref_Item_Num_Type	Char(6)	NA	Contains the item number type for the reference item from ITEM_MASTER
	Vendor catalog number	Char(30)	NA	Contains the VPN (Vendor Product Number), if defined for the item/ supplier
	Item description	Char(250)	NA	Contains the transaction level item description from ITEM_MASTER
TQUTY	File record descriptor	Char(5)	TQUTY	Identifies record type
	Line number	Number(10)	NA	Sequential file line number
	Transaction number	Number(10)	NA	Sequential transaction number
	Quantity descriptor	Char(15)	NA	Indicates what the quantity represents, either ’On-hand' (stock), 'Sold'(sales), or 'In transit'
	Location type	Char(2)	NA	Indicates the type of location represented in the file: ’ST' for store or ’WH' warehouse
	Location	Number(10)	NA	Contains the store or warehouse number for which the information applies
	Unit cost	Number(20)	NA	Contains the current unit cost for the item/location with 4 implied decimal places. This value will be in the supplier's currency
	Quantity	Number(12)	NA	Indicates the quantity of the item sold, on hand or in transit to the location; the quantity is represented with 4 implied decimal places
TTAIL	File record descriptor	Char(5)	TTAIL	Identifies record type
	Line number	Number(10)	NA	Sequential file line number
	Transaction lines	Number(6)	NA	Number of lines for this transaction
FTAIL	File record descriptor	Char(5)	TTAIL	Identifies record type
	Line number	Number(10)	NA	Total number of lines in file
	Number of transaction lines	Number(10)	NA	Number of transaction lines in file

Design Assumptions

A data translator will be used to convert the flat file produced by RMS to the required EDI data format.

Only data for items where the supplier is indicated as the primary supplier/origin country for the item will be included in the report.

ordinvupld (Upload and Process Inventory Reservations from ReSA)

Module Name	ordinvupld.pc
Description	Upload and Process Inventory Reservations from ReSA
Functional Area	RMS
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS113
Runtime Parameters	NA

Scheduling Constraints

Table 14-4 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Run after saordinvexp.pc
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Multithreading based on the thread number padded with input file name

Restart/Recovery

The logical unit of work for ordinvupld.pc is a valid item status transaction at a given store/location. The logical unit of work is defined as a group of these transaction records. The Oracle Retail standard file-based restart/recovery logic is used. Records are committed to the database when the maximum commit counter is reached.

Key Tables Affected

Table 14-5 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_LOC_SOH	No	No	Yes	No
TRAN_DATA	No	Yes	No	No
ITEM_STATUS_QTY	Yes	Yes	Yes	No
ITEM_MASTER	Yes	No	No	No
STORE	Yes	No	No	No

I/O Specification

Integration Type	Upload to RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000049

Input File Layout

Table 14-6 ordinvupld.pc - Input File

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	Record descriptor	Char(5)	FHEAD	Identifies the file record type
	File Line Id	Char(10)	0000000001	Sequential file line number
	File type definition	Char(4)	ORIN	Identifies the file type
	File Create Date	Char(14)	NA	File Create Date in YYYYMMDDHHMMSS format
	Location	Number(10)	NA	Store location number
THEAD	Record descriptor	Char(5)	THEAD	Identifies the file record type
	File Line Id	Char(10)	NA	Sequential file line number
	Transaction Date & Time	Char(14)	Transaction Date	Date and time of the order processed
	Transaction Type	Char(6)	’SALE'	Transaction type code specifies whether the transaction is sale or Return
TDETL	Record descriptor	Char(5)	TDETL	Identifies the file record type
	File Line Id	Char(10)	NA	Sequential file line number
	Item Type	Char(3)	REF or	Can be REF or ITM
	Item	Char(25)	ITM	Id number of the ITM or REF
	Item Status	Char(6)	LIN - Layaway Initiate LCA - Layaway Cancel LCO - Layaway Complete PVLCO - Post void of Layaway complete ORI - Pickup/delivery Initiate ORC - Pickup/delivery Cancel ORD - Pickup/delivery Complete PVORD - Post void of Pick-up/delivery complete	Type of transaction
	Dept	Number(4)	NA	Department of item sold or returned
	Class	Number(4)	NA	Class of item sold or returned.
	Sub class	Number(4)	NA	Subclass of item sold or returned
	Pack Ind	Char(1)	NA	Pack indicator of item sold or returned
	Quantity Sign	Chanr(1)	'P' or 'N'	Sign of the quantity.
	Quantity	Number(12)	NA	Quantity * 10000 (4 implied decimal places), number of units for the given order (item) status
	Selling UOM	Char(4)	NA	UOM at which this item was sold
	Catchweight Ind	Char(1)	NA	Indicates if the item is a catchweight item. Valid values are Y or NULL
	Customer Order number	Char(48)	NA	Customer Order number
TTAIL	File Type Record Descriptor	Char(5)	TTAIL	Identifies file record type
	File Line Identifier	Number(10)	Specified by ReSA	ID of current line being processed by input file.
	Transaction count	Number(6)	Specified by ReSA	Number of TDETL records in this transaction set
FTAIL	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Identifier	Number(10)	Specified by external system	ID of current line being processed by input file
	File Record Counter	Number(10)	NA	Number of records/transactions processed in current file (only records between FHEAD & FTAIL)

Design Assumptions

NA

wasteadj (Adjust Inventory for Wastage Items)

Module Name	wasteadj.pc
Description	Adjust Inventory for Wastage Items
Functional Area	Inventory
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS388
Runtime Parameters	NA

Design Overview

This program reduces inventory of spoilage type wastage items to account for natural wastage that occurs over the shelf life of the product. This program affects only items with spoilage type wastage identified on ITEM_MASTER with a waste_type of ’SP' (spoilage). Sales type wastage is accounted for at the time of sale.

This program should be scheduled to run prior to the stock count and stock ledger batch to ensure that the stock adjustment taken during the current day is credited to the appropriate day.

Scheduling Constraints

Table 14-7 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program should be scheduled to run prior to the stock count and stock ledger batch to ensure that the stock adjustment taken during the current day is credited to the appropriate day
Pre-Processing	NA
Post-Processing	refeodinventory.ksh
Threading Scheme	Threaded by store

Restart/Recovery

The logical unit of work is an item/location. This batch program commits when the number of records processed has reached commit_max_ctr. If the program aborts, it restarts from the last successfully processed item /location.

Key Tables Affected

Table 14-8 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	Yes	No
CLASS	Yes	No	No	No
INV_ADJ_REASON	Yes	No	No	No
INV_ADJ	No	Yes	No	No
TRAN_DATA	No	Yes	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
STORE	Yes	No	No	No
WH	Yes	No	No	No
PARTNER	Yes	No	No	No
VAT_ITEM	Yes	No	No	No

Design Assumptions

NA

refeodinventory (Refresh End of Day Inventory Snapshot)

Module Name	refeodinventory.ksh
Description	Refresh End of Day Inventory Snapshot
Functional Area	Inventory Tracking
Module Type	Business Processing
Module Technology	Ksh
Catalog ID	RMS303
Runtime Parameters	NA

Design Overview

This script refreshes the ITEM_LOC_SOH_EOD. This end of day snapshot is used for assorted history build programs. The script does the following:

• Truncates the ITEM_LOC_SOH_EOD table

• Inserts all records from ITEM_LOC_SOH into ITEM_LOC_SOH_EOD

Scheduling Constraints

Table 14-9 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Must run prior to any batch programs that use data from ITEM_LOC_SOH_EOD to ensure that the table is up-to-date
Pre-Processing	wasteadj.pc
Post-Processing	Prepost edidlprd pre
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 14-10 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_LOC_SOH	Yes	No	No	No
ITEM_LOC_SOH_EOD	No	Yes	No	Yes
SYSTEM_OPTIONS	Yes	No	No	No

Design Assumptions

All of the daily updates pertaining to stock on hand have been performed during prior batches.

invaprg (Purge Aged Inventory Adjustments)

Module Name	invaprg.pc
Description	Purge Aged Inventory Adjustments
Functional Area	Inventory
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS251
Runtime Parameters	NA

Design Overview

This batch program all inventory adjustment records whose adjustment date has elapsed a pre-determined number of months. The number of months that inventory adjustment records are kept before they are purged by this batch is defined by the system parameter Inventory Adjustment Months.

Scheduling Constraints

Table 14-11 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

Na

Key Tables Affected

Table 14-12 Key Tables Affected

Table	Select	Insert	Update	Delete
PURGE_CONFIG_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
INV_ADJ	No	No	No	Yes

Design Assumptions

NA

customer_order_purge.ksh (Purge Aged Customer Orders)

Module Name	customer_order_purge.ksh
Description	Purge Aged Customer Orders
Functional Area	Purchase Orders
Module Type	Admin
Module Technology	ksh
Catalog ID	RMS205

Design Overview

This module purges the store fulfillment customer order records from ORDCUST and ORDCUST_DETAIL tables based on the CUST_ORDER_HISTORY_MONTHS from PURGE_CONFIG_OPTIONS table. This will also purge the obsolete records having status ’X' where the customer order could not be created.

Scheduling Constraints

Table 14-13 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	Run after tsfprg.pc and ordprg.pc
Pre-Processing	tsfprg.pc, ordprg.pc
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

Na

Key Tables Affected

Table 14-14 Key Tables Affected

Table	Select	Insert	Update	Delete
ORDCUST	Yes	No	No	Yes
ORDCUST_DETAIL	Yes	No	No	Yes
PURGE_CONFIG_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No

Security Considerations

NA