Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
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.
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)
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 |
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.
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 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.
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 |
Integration Type | Download from RMS |
File Name | Determined by runtime parameter |
Integration Contract | IntCon000013 |
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 |
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 |
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.
Integration Type | Upload to RMS |
File Name | Determined by runtime parameter |
Integration Contract | IntCon000049 |
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) |
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 |
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.
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 |
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.
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 |
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 |
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
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 |
Module Name | invaprg.pc |
Description | Purge Aged Inventory Adjustments |
Functional Area | Inventory |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS251 |
Runtime Parameters | NA |
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.
Module Name | customer_order_purge.ksh |
Description | Purge Aged Customer Orders |
Functional Area | Purchase Orders |
Module Type | Admin |
Module Technology | ksh |
Catalog ID | RMS205 |
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.