Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
Transfers, Allocations and Return to Vendor (RTV) transactions move inventory among locations. The majority of processing associated with these transactions occurs through the user interface and near real time RIB integration with Oracle Retail Store Inventory Management (SIM) and Oracle Retail Warehouse Management System (RWMS). However, RMS does use a variety of batch programs to maintain the data related to these transactions.
The following batch designs are included in this chapter:
docclose.pc - Close Transactions with no Expected Appointments, Shipments or Receipts
dummyctn.pc - Reconcile Received Dummy Carton IDs with Expected Cartons
tamperctn.pc - Detail Receive Damaged or Tampered with Cartons
distropcpub.pc - Stage Regular Price Changes on Open Allocations/Transfers so Publishing Sends New Retail to Subscribing Applications
mrt.pc - Create Transfers for Mass Return Transfer
mrtrtv.pc - Create Return To Vendor for Mass Return Transfer
mrtupd.pc - Close Mass Return Transfers
mrtprg.pc - Purge Aged Mass Return Transfers and RTVs
rtvprg.pc - Purge Aged Returns to Vendors
tsfclose.pc - Close Overdue Transfers
tsfprg.pc - Purge Aged Transfers
allocbt.ksh - Create Book Transfers for Allocations Between Warehouses in the Same Physical Warehouse.
Module Name | docclose.pc |
Description | Close Transactions with no Expected Appointments, Shipments or Receipts |
Functional Area | Transfers, Allocation, and RTVs |
Module Type | Business Processing |
Module Technology | ProC |
Catalog ID | RMS219 |
Runtime Parameters | NA |
This program will be used to attempt to close POs, transfers, and allocations that do not have any outstanding appointments, shipments or receipts expected. Receipts without appointments are recorded on the DOC_CLOSE_QUEUE table. Allocations souced from an inbound receipt of another document (such as, POs, Transfers, Allocations, ASNs and BOL) can only be closed if the sourcing document is closed. This batch program will retrieve unique documents from the table and use existing functions to attempt closure for each.
The logical unit of work is a unique doc and doc_type combination. The program is restartable on the doc number
Module Name | dummyctn.pc |
Description | Reconcile Received Dummy Carton IDs with Expected Cartons |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Business Processing |
Module Technology | ProC |
Catalog ID | RMS233 |
Runtime Parameters | NA |
When stock orders are received, if a carton number or barcode cannot be read due to damage to the box or other factors, a dummy ID is assigned to it and it is detail received at the store or warehouse. The dummy ID and the details of the carton received are then written to a staging table during the receiving process. This batch process scans stock orders to find transfers or allocations that contain cartons that were not received to see if any shipments contain un-received cartons that match the dummy carton receipt (both item and quantity). If a match is found, then the dummy carton is received against the matching carton. If a match is not found, an error is written to an error file and the record remains on the staging table.
This program deletes from the DUMMY_CARTON_STAGE table. The program will restart by processing the records that remain on the DUMMY_CARTON_STAGE table.
Table 15-4 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SHIPSKU_TEMP |
Yes |
Yes |
No |
Yes |
SHIPMENT |
Yes |
No |
Yes |
No |
SHIPSKU |
Yes |
No |
Yes |
No |
PACKITEM |
Yes |
No |
No |
No |
DUMMY_CARTON_STAGE |
Yes |
Yes |
Yes |
Yes |
TSFHEAD |
Yes |
No |
No |
No |
ALLOC_HEADER |
Yes |
No |
No |
No |
IF_ERRORS |
No |
Yes |
No |
No |
ALLOC_DETAIL |
No |
No |
Yes |
No |
SHIPITEM_INV_FLOW |
Yes |
No |
Yes |
No |
APPT_DETAIL |
No |
No |
Yes |
No |
DOC_CLOSE_QUEUE |
No |
Yes |
No |
No |
TRAN_DATA |
No |
Yes |
No |
No |
ITEM_LOC_SOH |
No |
Yes |
Yes |
No |
EDI_DAILY_SALES |
No |
No |
Yes |
No |
STAKE_SKU_LOC |
No |
Yes |
Yes |
No |
STAKE_PROD_LOC |
No |
No |
Yes |
No |
MRT_ITEM_LOC |
No |
No |
Yes |
No |
TSFDETAIL |
No |
Yes |
Yes |
No |
NWP |
No |
Yes |
Yes |
No |
INV_ADJ |
No |
Yes |
No |
No |
TSFDETAIL_CHRG |
No |
Yes |
No |
No |
ITEM_LOC |
No |
Yes |
No |
No |
PRICE_HIST |
No |
Yes |
No |
No |
ITEM_SUPP_COUNTRY_LOC |
No |
Yes |
Yes |
No |
ITEM_SUPP_COUNTRY_BRACKET_COST |
No |
Yes |
Yes |
No |
INV_STATUS_QTY |
No |
Yes |
Yes |
Yes |
Module Name | tamperctn.pc |
Description | Detail Receive Damaged or Tampered with Cartons |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Business Processing |
Module Technology | ProC |
Catalog ID | RMS371 |
Runtime Parameters | NA |
This program looks for items that were intended to be received as a pack and attempts to match based on component quantity. It reads records from the staging table for the carton ID for pack items not received and attempts to match on the components of the pack and quantity. If a match is found, then the dummy carton is received against the matching carton. If a match is not found, an error is written to an error file and the record remains on the staging table.
This program is only run if the Receive Pack Component (STORE_PACK_COMP_RCV) system parameter is ’Y'.
Module Name | distropcpub.pc |
Description | Stage Regular Price Changes on Open Allocations/Transfers so Publishing Sends New Retail to Subscribing Applications |
Functional Area | Transfers, Allocations, and RTV |
Module Type | Integration |
Module Technology | ProC |
Catalog ID | RMS216 |
Runtime Parameters | NA |
This program will look for any regular price change (tran type 4 or 11 from PRICE_HIST) that is due to go into effect tomorrow. Then, for any open allocations or transfers where the ’to' location and items that have price changes going into effect, it places a record on the allocation or transfer publishing queue tables, such that they can be picked up by the RIB and sent to the subscribing systems.
The logical unit of work is store. The driving cursor retrieves all item/locations that have price changes in effect from the next day. It also gets all of the component items of the non-sellable packs that have price changes.
Table 15-8 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PERIOD |
Yes |
No |
No |
No |
PRICE_HIST |
Yes |
No |
No |
No |
V_RESTART_STORE |
Yes |
No |
No |
No |
V_PACKSKU_QTY |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
ALLOC_HEADER |
Yes |
No |
No |
No |
ALLOC_DETAIL |
Yes |
No |
No |
No |
TSFHEAD |
Yes |
No |
No |
No |
TSDETAIL |
Yes |
No |
No |
No |
ORDHEAD_REV |
Yes |
No |
No |
No |
ORDHEAD |
Yes |
No |
No |
No |
ALLOC_MFQUEUE |
No |
Yes |
No |
No |
TSF_MFQUEUE |
No |
Yes |
No |
No |
Integration Type | Download from RMS |
File Name | NA |
Integration Contract | IntCon000196
ALLOC_MFQUEUE table |
Module Name | mrt.pc |
Description | Create Transfers for Mass Return Transfer |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Business Processing |
Module Technology | ProC |
Catalog ID | RMS273 |
Runtime Parameters | NA |
This batch program creates individual transfers for each ’from' location on an approved Mass Return Transfer. Transfers will be created in approved status, however for MRTs with a Quantity Type of ’Manual', meaning the MRT was created for a specific quantity rather than ’All Inventory', if the SOH at the sending location is lower than the requested quantity the status will be created in Input status. In addition, if the Transfer Not After Date specified on the MRT is earlier than or equal to the current date, the status of the associated transfers will also be set to Input.
Table 15-9 Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Daily |
Scheduling Considerations |
This batch should be scheduled to run before mrtupd.pc and mrtrtv.pc, and before any other transfer-related batches |
Pre-Processing |
NA |
Post-Processing |
mrtrtv |
Threading Scheme |
Threaded by warehouse |
The logical unit of work is a from/to location combination. This may represent a transfer of multiple items from a location (store or warehouse) to a warehouse, depending on how the MRT was created. Restart/recovery is based on from/to location as well. The batch program uses the v_restart_all_locations view to thread processing by warehouse (to location).
Table 15-10 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
MRT |
Yes |
No |
Yes |
No |
MRT_ITEM |
Yes |
No |
No |
No |
MRT_ITEM_LOC |
Yes |
No |
Yes |
No |
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
ITEM_LOC |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
ITEM_LOC_SOH |
Yes |
No |
Yes |
No |
TSFDETAIL |
Yes |
Yes |
Yes |
No |
TSFHEAD |
No |
Yes |
Yes |
No |
TRAN_DATA |
No |
Yes |
No |
No |
INV_STATUS_QTY |
Yes |
Yes |
Yes |
Yes |
PERIOD |
Yes |
No |
No |
No |
Module Name | mrtrtv.pc |
Description | Create Return To Vendor for Mass Return Transfer |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Business Processing |
Module Technology | ProC |
Catalog ID | RMS275 |
Runtime Parameters | NA |
This batch program creates RTVs for approved mass return transfers that require an RTV to be created automatically and have an RTV create date earlier than or equal to the current date. RTVs are created in either Input or Approved status, depending on how the MRT was created. The program will then set the status of all processed MRTs to ’R' in the MRT table, which indicates that the RTVs have been created.
The logical unit of work for this program is set at the warehouse level. Threading is done by store using the v_restart_all_locations view.
Module Name | mrtupd.pc |
Description | Close Mass Return Transfers |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS276 |
Runtime Parameters | NA |
This program updates the status of MRTs and their associated transfers to closed status, for MRTs or transfers associated with an MRT that remain open after the transfer and/or RTV not after dates have passed. MRTs that have transfers in progress (shipped but not received) will not be closed by this program.
The logical unit of work for this program is warehouse. This program is multi-threaded using the v_restart_all_locations view.
Module Name | mrtprg.pc |
Description | Purge Aged Mass Return Transfers and RTVs |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS274 |
Runtime Parameters | NA |
The purpose of this module is to purge mass return transfer (MRT) records, and their associated transfers and RTVs. Only MRTs with a status of closed in which all transfers associated with the MRT are also closed and where the time elapsed between the current date and the close date is at least equal to the system parameter value for MRT Transfer Retention days.
The logical unit of work for this batch program is a warehouse location. The program is multithreaded using v_restart_all_locations view.
Table 15-16 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PERIOD |
Yes |
No |
No |
No |
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
TSFHEAD |
Yes |
No |
No |
Yes |
TSFDETAIL |
No |
No |
No |
Yes |
SHIPMENT |
No |
No |
No |
Yes |
SHIPSKU |
Yes |
No |
No |
Yes |
SHIPITEM_INV_FLOW |
No |
No |
No |
Yes |
CARTON |
No |
No |
No |
Yes |
APPT_HEAD |
Yes |
No |
No |
Yes |
APPT_DETAIL |
Yes |
No |
No |
Yes |
DOC_CLOSE_QUEUE |
No |
No |
No |
Yes |
INVC_HEAD |
Yes |
No |
No |
Yes |
INVC_DETAIL |
Yes |
No |
No |
Yes |
MRT |
Yes |
No |
No |
Yes |
MRT_ITEM |
Yes |
No |
No |
Yes |
MRT_ITEM_LOC |
Yes |
No |
No |
Yes |
RTV_HEAD |
Yes |
No |
No |
Yes |
RTV_DETAIL |
No |
No |
No |
Yes |
TSFDETAIL_CHRG |
No |
No |
No |
Yes |
Module Name | rtvprg.pc |
Description | Purge Aged Returns to Vendors |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS320 |
Runtime Parameters | NA |
This batch program purges outdated RTV transactions from RMS. RTVs are considered outdated if they number of months between their completion date and the current date exceeds the system parameter RTV Order History Months and where all debit memos associated with the RTV have been posted.
Table 15-18 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
RTV_HEAD |
No |
No |
No |
Yes |
RTV_DETAIL |
No |
No |
No |
Yes |
INVC_HEAD |
Yes |
No |
No |
Yes |
INVC_DETAIL |
No |
No |
No |
Yes |
INVC_NON_MERCH |
Yes |
No |
No |
Yes |
INVC_MERCH_VAT |
Yes |
No |
No |
Yes |
INVC_DETAIL_VAT |
Yes |
No |
No |
Yes |
INVC_MATCH_QUEUE |
Yes |
No |
No |
Yes |
INVC_DISCOUNT |
Yes |
No |
No |
Yes |
INVC_TOLERANCE |
Yes |
No |
No |
Yes |
ORDLOC_INVC_COST |
Yes |
No |
Yes |
No |
INVC_MATCH_WKSHT |
Yes |
No |
No |
Yes |
INVC_XREF |
Yes |
No |
No |
Yes |
RTVITEM_INV_FLOW |
No |
No |
No |
Yes |
RTV_HEAD_CFA_EXT |
No |
No |
No |
Yes |
Module Name | tsfclose.pc |
Description | Close Overdue Transfers |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS379 |
Runtime Parameters | NA |
This batch program processes unshipped and partially shipped transfers that are considered ’overdue', based on system parameter settings. If this functionality is enabled (by setting the system parameter TSF_CLOSE_OVERDUE = ’Y'), then this program will evaluate transfers to determine if they are overdue. The way that a transfer is considered overdue depends on the source and destination locations. There are separate system parameters for each of store to store, store to warehouse, warehouse to store, and warehouse to warehouse types of transfers.
For unshipped transfers, the transfer status is updated to delete and transfer reserved and expected inventory is backed out on ITEM_LOC_SOH for the sending and receiving locations respectively. For transfers that are shipped but not fully received, an entry is made into doc_close_queue table. These transfers are picked up by docclose batch and closed after reconciliation.
The logical unit of work for this module is defined as a unique tsf_no. The v_restart_transfer view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the commit_max_ctr is reached.
Module Name | tsfprg.pc |
Description | Purge Aged Transfers |
Functional Area | Transfers, Allocations and RTVs |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS380 |
Runtime Parameters | NA |
This module purges closed or deleted transfers and their associated records after a set number of days, based on the Transfer History Months system parameter.
This batch program is multithreaded using the v_restart_transfer view. The logical unit of work is a transfer number. This batch program commits to the database for every commit_max_ctr number of transfers processed.
Table 15-22 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PERIOD |
Yes |
No |
No |
No |
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
TSFHEAD |
Yes |
No |
No |
Yes |
TSFDETAIL |
No |
No |
No |
Yes |
ALLOC_HEADER |
Yes |
No |
No |
Yes |
ALLOC_DETAIL |
No |
No |
No |
Yes |
ALLOC_CHRG |
Yes |
No |
No |
Yes |
ALLOC_PURGE_QUEUE |
Yes |
No |
No |
No |
DOC_PURGE_QUEUE |
Yes |
No |
No |
No |
SHIPSKU |
Yes |
No |
No |
Yes |
CARTON |
No |
No |
No |
Yes |
Module Name | allocbt.ksh |
Description | Create Book Transfers for Allocations Between Warehouses in the Same Physical Warehouse |
Functional Area | Inventory Movement |
Module Type | Business Processing |
Module Technology | ksh |
Catalog ID | RMS175 |
Runtime Parameters | NA |
In RMS, when an allocation is received that involves a movement of stock between two warehouses, it should be determined if the source and any of the destination warehouses belong to the same physical warehouse. If so, that portion of the allocation should be treated as a book transfer and not sent down to RWMS for processing. This batch job identifies such allocations and creates book transfers once the allocation source is received and/or the release date for the allocation is reached.
Allocations can be sourced either from a warehouse's available inventory or from an inbound receipt. These allocations are integrated into RMS's ALLOC_HEADER and ALLOC_DETAIL tables and can be identified as the following:
Warehouse Sourced Allocations:
Alloc_header.order_no is NULL and alloc_header.doc is NULL.
Purchase Ordered Sourced Allocations (Cross Doc POs):
Alloc_header.order_no holds the PO number and alloc_header.doc_type = ’PO'.
Linked shipments are identified through shipment.order_no = alloc_header.order_no.
Transfer Sourced Allocations:
Alloc_header.order_no holds the transfer number and alloc_header.doc_type = ’TSF'.
Linked shipments are identified through shipsku.distro_no = alloc_header.order_no.
Alloc_header.doc holds the allocation number and alloc_header.doc_type = ’ALLOC'.
Linked shipments are identified through shipsku.distro_no = alloc_header.doc.
ASN Sourced Allocations:
Alloc_header.doc holds the asn number and alloc_header.doc_type = ’ASN'.
Linked shipments are identified through shipment.asn = alloc_header.doc.
BOL Sourced Allocations:
Alloc_header.doc holds the bol_no and alloc_header.doc_type = ’BOL'.
Linked shipments are identified through shipment.bol_no = alloc_header.doc.
This batch job supports all above allocation scenarios and calls the core package function ALLOC_BOOK_TSF_SQL to create book transfers.