12 Inventory

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

Program Summary

The following batch designs are included in this chapter:

Adjust Inventory for Wastage Items (wasteadj)

Module Name

wasteadj.pc

Description

Adjust Inventory for Wastage Items

Functional Area

Inventory

Module Type

Business Processing

Module Technology

ProC

Catalog ID

RMS388

Wrapper Script

rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

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.

Design Assumptions

N/A

Conversion for Inventory Buckets (conv_ils_inventory_buckets)

Module Name

conv_ils_inventory_buckets.ksh

Description

Conversion for Inventory Buckets

Functional Area

Inventory Tracking

Module Type

Admin (Ad Hoc)

Module Technology

KSH

Catalog ID

Wrapper Script

rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule (Ad Hoc)

This ad hoc conversion job is to be executed by Merchandising Foundation Cloud Service (MFCS) customers undergoing a phased go-live.

Design Overview

This standalone ad hoc batch program initializes the following inventory buckets in ITEM_LOC_SOH:

  • PO_ON_ORDER

  • PO_IN_TRANSIT

  • PACK_COMP_PO_ON_ORDER

  • PACK_COMP_PO_IN_TRANSIT

  • XDOCK_ALLOC_OUT

  • XDOCK_ALLOC_IN

  • PACK_COMP_XDOCK_ALLOC_OUT

  • PACK_COMP_XDOCK_ALLOC_IN

The batch first checks whether the BG_ASYNC_REFRESH_ILS_ON_ORDER_IN_TRANSIT background job is configured to run. If it is configured to run and the staging table REFRESH_ITEM_LOC_SOH contains records, the batch treats the background job as running and fails with an error to avoid contention. If the staging table is empty, the batch automatically turns off the background job for this task and proceeds with conversion.

The job obtains a lock on the conversion process to prevent concurrent conversion processes. The lock is released at the end of the process. The staging table is populated with all item-location records from ITEM_LOC_SOH. If a location is specified, only items for that location are loaded. The staging table drives the conversion process.

For each staged item-location record, the batch calculates and updates the inventory buckets in ITEM_LOC_SOH. Non-inventoried items are excluded. Calculations use existing purchase orders, purchase order shipments, and PO-sourced cross-dock allocations. Records are deleted from the staging table as they are processed.

The batch is threaded by item-location and uses the threading configuration in RMS_PLSQL_BATCH_CONFIG. The default settings are:

Setting Default Value

Program Name

CONV_ILS_INVENTORY_BUCKETS

Max Chunk Size

10000

Max Concurrent Threads

12

Retry Lock Attempts

6

Retry Wait Time

1

Exception

A background job performs the same function as this conversion batch. The background job is enabled by default and is intended for use during patching. If the conversion batch runs while the background job is actively running, the batch returns an error as described in Design Overview.

You can allow the background job to finish the conversion, or stop it and use the conversion batch instead. To switch to the conversion batch:

  1. Run the following SQL statements in the RMS01 schema: 

    update merch_batch_params
   set param_value = 'N',
       last_upd_id = user,
       last_upd_datetime = sysdate
 where param_key = 'BACKGROUND_RUN_FLAG'
   and batch_name = 'BG_ASYNC_REFRESH_ILS_ON_ORDER_IN_TRANSIT';

delete from merch_batch_bookmark
 where product = 'RMS'
   and batch_name = 'POPULATE_REFRESH_ITEM_LOC_SOH'
   and thread_val = 1;

update merch_post_patch_task
   set status = 'COMPLETED',
       last_update_id = user,
       last_update_datetime = sysdate
 where task_name = 'POPULATE_REFRESH_ITEM_LOC_SOH';

truncate table REFRESH_ITEM_LOC_SOH;

    Note:

    A locking error can occur when truncating the table if any background job threads are still running. Wait for those threads to finish, then truncate the table.

  2. Run the conversion batch.

Restart/Recovery

The process deletes records from REFRESH_ITEM_LOC_SOH as each item-location is processed. If the batch completes successfully but records remain in the staging table, those records were locked during processing and have not been converted. Rerun the job with a NULL location parameter to process the remaining records. Use the same approach if the batch fails and records remain in the staging table.

Failure While Loading the Staging Table

If the batch fails while loading the staging table, rerun it with a NULL location parameter. To rerun the batch for a specific location, first delete the entry for batch_name POPULATE_REFRESH_ITEM_LOC_SOH from MERCH_BATCH_BOOKMARK, then rerun the batch for that location.

To determine whether loading into the staging table failed, check for a record in MERCH_BATCH_BOOKMARK where batch_name is POPULATE_REFRESH_ITEM_LOC_SOH.

Failure While Processing a Specific Location

If a batch run for location X fails while processing staged records:

  • Rerunning for location X processes the remaining staged records for that location.

  • Rerunning for a different location Y processes only location Y. Existing staged records for location X are retained.

  • Rerunning with a NULL location parameter processes all locations, including location X.

Failure While Processing All Locations

If a batch run with a NULL location parameter fails while processing staged records:

  • Rerunning for a specific location processes only that location. Other locations remain in the staging table.

  • Rerunning with a NULL location parameter reprocesses all locations.

This also applies when the staging table load has completed, indicated by the absence of an entry in MERCH_BATCH_BOOKMARK for batch name POPULATE_REFRESH_ITEM_LOC_SOH.

I/O Specification

The job accepts an optional location parameter. If specified, ITEM_LOC_SOH is converted only for that location. Otherwise, all locations are converted.

Key Tables Affected

Table Select Insert Update Delete

REFRESH_ITEM_LOC_SOH

Yes

Yes

No

Yes

ITEM_LOC_SOH

Yes

No

Yes

No

MERCH_BATCH_PARAMS

Yes

No

Yes

No

MERCH_BATCH_BOOKMARK

Yes

Yes

No

Yes

MERCH_POST_PATCH_TASK

Yes

No

Yes

No

Design Assumptions

This batch is patterned after the processes executed by the POPULATE_REFRESH_ITEM_LOC_SOH and BG_ASYNC_REFRESH_ILS_ON_ORDER_IN_TRANSIT background tasks and uses the same staging table.

When the background job is configured to run, the conversion batch turns it off automatically only if REFRESH_ITEM_LOC_SOH is empty. If the staging table contains records, the conversion batch fails to avoid contention. For the steps to stop the background job and switch to the conversion batch, see Exception.

First Load of Inventory to Ledger (item_loc_soh_ledger_firstload)

Module Name

item_loc_soh_ledger_firstload.ksh

Description

First Load of Inventory to Ledger

Functional Area

Inventory Tracking

Module Type

Business Processing

Module Technology

Ksh

Catalog ID

RMS303

Wrapper Script

rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This job creates the first line of records in the ITEM_LOC_SOH_LEDGER table when the Inventory Layer Ind is Y and the Initialisation Status is I - In Progress in the SYSTEM_OPTIONS and INVENTORY_LAYER_TRACKING tables, respectively. This script will fetch the inventory of the item/location from the ITEM_LOC_SOH table that has stock on hand greater than 0.

Once the first load is inserted into the ledger table, the script updates the initialisation status to C - Completed in the INVENTORY_LAYER_TRACKING table. This job is a one-time execution and part of a nightly process.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Aged Customer Orders (customer_order_purge.ksh)

Module Name

customer_order_purge.ksh

Description

Purge Aged Customer Orders

Functional Area

Purchase Orders

Module Type

Admin

Module Technology

ksh

Catalog ID

RMS205

Wrapper Script

rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This module will serve as the wrapper for the package function which will purge the store fulfillment customer order records from the customer order tables based on the history months system parameter. This will also purge the obsolete records having the status 'X' where the customer order could not be created.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Aged Customer Orders (customer_orders_purge_job)

Module Name

customer_orders_purge_job

Description

Download Sales and Stock On Hand From RMS to Suppliers

Functional Area

Inventory

Module Type

Integration

Module Technology

Catalog ID

RMS47

Wrapper Script

b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of one step processing only. It will retain the business logic processing from original KSH script algorithm.

The Business logic program will remove all store fulfillment customer order records from customer order and customer order detail tables based on the purge criteria from the system parameter setting, customer order history months. This will also purge the obsolete records having status 'X' where the customer order could not be created.

Restart/Recovery

N/A

Key Tables Affected

Table 12-1 Key Tables Affected

Table Select Insert Update Delete

ORDCUST

Yes

No

No

Yes

ORDCUST_DETAIL

Yes

No

No

Yes

ORDCUST_CUSTOMER_DETAIL

Yes

No

No

No

PURGE_CONFIG_OPTIONS

Yes

No

No

No

PERIOD

Yes

No

No

No

Security Considerations

N/A

Purge Aged Inventory Adjustments (inv_adj_purge_job)

Module Name

inv_adj_purge_job

Description

Purge Aged Inventory Adjustments

Functional Area

Inventory

Module Type

Admin - Ad hoc

Module Technology

Background Processing

Catalog ID

N/A

Wrapper Script

b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job will purge aged inventory adjustment and aged item transformation records. This job is composed of two-step processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from inventory adjustment table and item transformation table based on its purge criteria from system parameter settings. The Inventory Adjustment Months parameter will determine records to be kept before they are purged if adjustment date has elapsed from this pre-determined number of months. The Inventory Adjustment Months parameter will also determine the item transformation records to be kept before they are purged if the transformation date has elapsed this pre- determined number of months.

These records are chunked, and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging tables. Using bulk processing, this program will first delete the records from inventory adjustment table followed by the item transformation tables. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 12-2 Key Tables Affected

Table Select Insert Update Delete

PURGE_CONFIG_OPTIONS

Yes

No

No

No

PERIOD

Yes

No

No

No

RMS_BATCH_STATUS

Yes

No

No

No

B8D_PROCESS_CONFIG

Yes

No

No

No

JOB_AUDIT_LOGS

No

Yes

No

No

B8D_INV_ADJ_PURGE_STG

Yes

Yes

No

Yes

INV_ADJ_PRG_HIST

No

Yes

No

No

INV_ADJ

No

No

No

Yes

B8D_ITEM_TRANSFORMATION_PURGE_STG

Yes

Yes

No

Yes

ITEM_TRANSFORMATION_WO_PRG_HIST

No

Yes

No

No

ITEM_TRANSFORMATION_WO

Yes

No

No

Yes

ITEM_TRANSFORMATION_DETAIL_PRG_HIST

No

Yes

No

No

ITEM_TRANSFORMATION_DETAIL

Yes

No

No

Yes

ITEM_TRANSFORMATION_HEAD_PRG_HIST

No

Yes

No

No

ITEM_TRANSFORMATION_HEAD

Yes

No

No

Yes

Design Assumptions

N/A

Purge Aged Inventory Adjustments (invaprg)

Module Name

invaprg.pc

Description

Purge Aged Inventory Adjustments

Functional Area

Inventory

Module Type

Admin

Module Technology

ProC

Catalog ID

RMS251

Wrapper Script

rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Restart/Recovery

N/A

Design Assumptions

N/A

Purged the Inventory Layer from Ledger Table (purge_item_loc_soh_ledger)

Module Name

purge_item_loc_soh_ledger.ksh

Description

Purged the Inventory Layer from Ledger table

Functional Area

Inventory Tracking

Module Type

Business Processing

Module Technology

Ksh

Catalog ID

RMS303

Wrapper Script

rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This job purged the data from the ITEM_LOC_SOH_LEDGER table based on the number of retention days (that is, INVENTORY_LAYER_HIST_DAYS) mentioned in SYSTEM_OPTIONS. Records that have 0 stocks and a last update date past the number of retention days are being purged. This job is a part of the nightly process.

Restart/Recovery

N/A

Design Assumptions

N/A

Refresh End of Day Inventory Snapshot (refeodinventory)

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

Wrapper Script

rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This script refreshes the item/location stock on hand end of day snapshot. This end of day snapshot is used for assorted history build programs. The script does the following:

  • Truncates the item/location stock on hand end of day snapshot table.

  • Inserts into the item/location stock on hand end of day snapshot table all records from the item/location stock on hand table reflecting the current business date transactions. The current business date transactions are based on the FLASHBACK_SCN captured in the FLASHBACK_SNAPSHOT_INFO table for the process_name SCCEXT_SALSTAGE_REFEODINVENTORY.

Restart/Recovery

N/A

Design Assumptions

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

  • The executing schema has DROP ANY TABLE privileges. This is needed to perform the truncate on the item/location end of day snapshot table.

  • The item/location end of day snapshot table is owned by the schema name specified in the TABLE_OWNER column of the SYSTEM_OPTIONS view.

Upload Stock Count Results from sim_rms_* input Table to svc_stkupld_* Staging Tables (simrmsstockcountupload)

Module Name

simrmsstockcountupload.ksh

Description

Upload stock count results into staging tables.

Functional Area

Stock Counts

Module Type

Business Processing

Module Technology

ksh

Catalog ID

Wrapper Script

rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This shell script uploads stock counts from the SIM inbound tables into the SVC_STKUPLD_* tables for further processing by the stockcountprocess batch. These tables serve as the interface by which the Merchandising core stock upload process updates the system with the actual inventory counts from the input tables.

The program retrieves records from the SIM_RMS_* input tables that are in N (new) status and processes each record in parallel depending on the number of threads specified in the rms_plsql_batch_config table. Data validation will be performed before loading into the SVC_STKUPLD_FHEAD, SVC_STKUPLD_FDETL, and SVC_STKUPLD_FTAIL tables. After a successful load, data is deleted from the input tables. This is scheduled to run in the nightly batch after stockcountupld and before stockcountprocess.

Design Assumptions

N/A

Wrapper for SIM_RMS_* APIs (batch_sim_rms_import.ksh)

Module Name

batch_sim_rms_import.ksh

Description

This is a wrapper script that calls the individual SIOCS to Merchandising APIs.

Functional Area

Cross Pillar

Module Type

Business Processing

Module Technology

ksh

Catalog ID

Runtime Parameters

N/A

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

A set of import tables in Merchandising, prefixed with SIM_RMS_ are exposed to SIOCS. These tables are populated with data from SIOCS, similar to how SIOCS populates staging tables to publish messages to RICS. This wrapper is used to process records from the SIOCS input tables into Merchandising. Below are the individual importer APIs that this wrapper supports:

  • SIM_RMS_IMPORT_ASNOUT

  • SIM_RMS_IMPORT_DSD_RECEIPT

  • SIM_RMS_IMPORT_INVADJ

  • SIM_RMS_IMPORT_INVENTORY_REQUEST

  • SIM_RMS_IMPORT_PO_RECEIPT

  • SIM_RMS_IMPORT_RTV

  • SIM_RMS_IMPORT_STOCK_ORDER_RECEIPT

  • SIM_RMS_IMPORT_STOCK_ORDER_STATUS

  • SIM_RMS_IMPORT_STOCK_COUNT_SCHEDULE

  • SIM_RMS_IMPORT_FDG

  • SIM_RMS_STORE_ORDER_REVIEW

The above APIs transform the data to call the same business logic used by RICS.

Restart/Recovery

N/A

Design Assumptions

N/A