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
 

3 Foundation Data Maintenance

Foundation Data is basic information that is required for RMS to function properly. Most foundation data is managed through the RMS user interface or integrations (often RIB) from external systems. However, there are some batch processes that relate to Foundation Data. This chapter describes the batch processes that are used to maintain general foundation data.

Programs in this chapter can be divided into five basic categories:


Note:

For more information on Foundation Data, see the Item Maintenance chapter.

Batch Design Summary

The following batch designs are included in this functional area:

  • admin_api_purge.ksh (Purge Manage Admin records)

  • batch_compeffupd.ksh (Update ELC Components)

  • batch_expprofupd.ksh (Apply Pending Rate Changes to Expense Profiles)

  • batch_depchrgupd.ksh (Apply Pending to Up-Charge Cost Component Changes to Departments)

  • batch_itmcostcompupd.ksh (Apply Pending Item Cost Component Updates)

  • batch_alloctsfupd.ksh (Update Allocation and Transfer Based on Changes to Up-Charges)

  • batch_ordcostcompupd.ksh (Apply Pending Cost Component and ELC Changes to Purchase Orders)

  • elcexcprg.pc (Purge Aged Cost Component Exceptions)

  • dfrtbld.pc (Build Diff Ratios Based on Sales History)

  • lclrbld.pc (Rebuild Dynamic Location Lists)

  • batch_rfmvcurrconv.ksh (Refresh Currency Conversion Materialized View)

  • refmvlocprimadd.ksh (Refresh Address Materialized View)

  • cremhierdly.pc (Process Pending Merchandise Hierarchy Changes from External Systems)

  • reclsdly.pc (Reclassify Items in Merchandise Hierarchy)

  • supmth.pc (Rollup of Supplier Data)

  • schedprg.pc (Purge Aged Store Ship Schedule)

  • prchstprg.pc (Purge Aged Price History Data)

  • tcktdnld (Download of Data to be Printed on Tickets)

  • refmvl10entity (Refresh MV MV_L10N_ENTITY)

admin_api_purge (Purge Manage Admin records)

Module Name admin_api_purge.ksh
Description Purge Manage Admin records
Functional Area Administration
Module Type Admin
Module Technology ksh
Catalog ID
Runtime Parameters Database connection

Design Overview

This script purges data from tables used for uploading Foundation Data from spreadsheets based on the retention days specified in the system parameter- PROC_DATA_RETENTION_DAYS for both RMS and ReSA and will help in keeping the size of these tables controlled.

Scheduling Constraints

Table 3-1 Scheduling Constraints

Schedule Information Description

Processing Cycle

Ad Hoc

Frequency

As Needed

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

N/A

Key Tables Affected

Table 3-2 Key Tables Affected

Table Select Insert Update Delete

S9T_ERRORS

No

No

No

Yes

S9T_FOLDER

No

No

No

Yes

SVC_ADMIN_UPLD_ER

No

No

No

Yes

SVC_PROCESS_TRACKER

No

No

No

Yes


I/O Specification

N/A

batch_compeffupd (Update ELC Components)

Module Name batch_compeffupd.ksh
Description Apply Pending Cost Component, Up-charge and ELC Changes
Functional Area Foundation Data
Module Type Business Processing
Module Technology KSH
Catalog ID RMS185
Runtime Parameters NA

Design Overview

In RMS, users are allowed to make rate changes to cost components, up-charges and expense profiles and assign future effective dates to the changes. Additionally, when these future rate changes are specified, users can choose to cascade these changes to lower levels. The options for how the updates can be cascaded are described in the table below:

Table 3-3 Options for Cascading Updates

Updated Entity Cascade Options

Expense Profiles (Country, Supplier, or Partner)

Order, Item

Cost Component (Expense)

Country, Supplier, Partner, Item, Order

Cost Component (Assessment)

Item, Order

Cost Component (Up-charge)

Department, Item, Transfer/Allocation

Department Level Up-Charges

Item, Transfer/Allocation


This batch process is used to process updates to cost components of all types at the expense component level, updates to department level up-charges, and updates to expense profiles at the supplier, country, or partner level. The cascading to other levels is handled in the dependent processes which are run after this process:

  • Allocation and Transfer Up-charge Update (batch_alloctsfupd)

  • Expense Profile Update (batch_expprofupd)

  • Item Cost Component Update (batch_itmcostcompupd)

  • Purchase Order Cost Component Update (batch_ordcostcompupd)

  • Department Up-charge (batch_depchrgupd)

Scheduling Constraints

Table 3-4 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

Must be run before the following scripts:

  • batch_alloctsfupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

  • batch_depchrgupd.ksh

Pre-Processing

NA

Post-Processing

  • batch_alloctsfupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

  • batch_depchrgupd.ksh

Threading Scheme

NA


Restart/Recovery

NA

Key Tables Affected

Table 3-5 Key Tables Affected

Table Select Insert Update Delete

COST_COMP_UPD_STG

Yes

No

No

No

DEPT_CHRG_DETAIL

Yes

No

Yes

No

EXP_PROF_DETAIL

Yes

No

Yes

No

ELC_COMP

Yes

No

Yes

No


Design Assumptions

NA

batch_expprofupd (Apply Pending Rate Changes to Expense Profiles)

Module Name batch_expprofupd.ksh
Description Apply Pending Rate Changes to Expense Profiles
Functional Area Foundation Data
Module Type Business Processing
Module Technology ksh
Catalog ID RMS188
Runtime Parameters NA

Design Overview

In RMS, users are allowed to make rate changes to expense type cost components and assign future effective dates to the changes. Additionally, when these future rate changes are specified, users can choose to cascade these changes to lower levels. For expense type cost components, this includes the ability to cascade the changes to country, supplier, and partner expense profiles. This script will process the updates to country, supplier, and partner expense profiles once the rate changes reach their effective date.

Scheduling Constraints

Table 3-6 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

The following scripts can be executed in parallel:

  • batch_alloctsfupd.ksh

  • batch_depchrgupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

The pre-post job batch_costcompupd post should be run after all 5 complete

Pre-Processing

batch_compeffupd.ksh

Post-Processing

batch_costcompupd post (see note above)

Threading Scheme

NA


Restart/Recovery

NA

Key Tables Affected

Table 3-7 Key Tables Affected

Table Select Insert Update Delete

COST_COMP_UPD_GL_TEMP

Yes

Yes

No

Yes

COST_COMP_UPD_STG

Yes

No

No

No

EXP_PROF_HEAD

Yes

No

No

No

EXP_PROF_DETAIL

Yes

No

Yes

No

COST_COMP_EXC_LOG

No

Yes

No

No


Design Assumptions

NA

batch_depchrgupd (Apply Pending Up-Charge Cost Component Changes to Departments)

Module Name batch_depchrgupd.ksh
Description Apply Pending Up-Charge Cost Component Changes to Departments
Functional Area Foundation Data
Module Type Business Processing
Module Technology ksh
Catalog ID RMS186
Runtime Parameters NA

Design Overview

In RMS, users are allowed to make rate changes to up-charges and assign future effective dates for the updates. Additionally, when these future rate changes are specified, users can choose to cascade these changes to lower levels. For up-charges, this includes the ability to cascade the changes made at the cost component level (for up-charge components) to department level up-charges. This script will process the updates to department level up-charges once the rate changes reach their effective date.

Scheduling Constraints

Table 3-8 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

The following scripts can be executed in parallel:

  • batch_alloctsfupd.ksh

  • batch_depchrgupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

The pre-post job batch_costcompupd post should be run after all 5 complete

Pre-Processing

batch_compeffupd.ksh

Post-Processing

batch_costcompupd post (see note above)

Threading Scheme

NA


Restart/Recovery

NA

Key Tables Affected

Table 3-9 Key Tables Affected

Table Select Insert Update Delete

COST_COMP_UPD_GL_TEMP

Yes

Yes

No

Yes

COST_COMP_UPD_STG

Yes

No

No

No

DEPT_CHRG_DETAIL

Yes

No

Yes

No

COST_COMP_EXC_LOG

No

Yes

No

No


Design Assumptions

NA

batch_itmcostcompupd (Apply Pending Item Cost Component Updates)

Module Name batch_itmcostcompupd.ksh
Description Apply Pending Item Cost Component Updates
Functional Area Foundation Data
Module Type Business Processing
Module Technology ksh
Catalog ID RMS189
Runtime Parameters NA

Design Overview

In RMS, users are allowed to make rate changes to cost components, up-charges and expense profiles and assign future effective dates to the changes. Additionally, when these future rate changes are specified, users can choose to cascade these changes to lower levels. For items, changes can be cascaded down from each of the different types:

  • Expense Profiles (country, supplier, or partner)

  • Cost Components (expense, assessment, or up-charge)

  • Department-level Up-charges

This script will process the updates for items for each of these types of rate updates once the rate changes reach their effective date.

Scheduling Constraints

Table 3-10 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

The following scripts can be executed in parallel:

  • batch_alloctsfupd.ksh

  • batch_depchrgupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

The pre-post job batch_costcompupd post should be run after all 5 complete

Pre-Processing

batch_compeffupd.ksh

Post-Processing

batch_costcompupd post (see note above)

Threading Scheme

Threaded by from_loc for item up-charges, by supplier for item expenses. It is not threaded for item assessments.


Restart/Recovery

NA

Key Tables Affected

Table 3-11 Key Tables Affected

Table Select Insert Update Delete

COST_COMP_UPD_GL_TEMP

Yes

Yes

No

Yes

COST_COMP_UPD_STG

Yes

No

No

No

ITEM_EXP_HEAD

Yes

No

No

No

ITEM_EXP_DETAIL

Yes

No

Yes

No

EXP_PROF_HEAD

Yes

No

No

No

COST_COMP_EXC_LOG

No

Yes

No

No

ITEM_HTS_ASSESS

Yes

No

Yes

No

ITEM_CHRG_DETAIL

Yes

No

Yes

No


Design Assumptions

NA

batch_alloctsfupd (Update Allocation and Transfer Based on Changes to Up-Charges)

Module Name batch_alloctsfupd.ksh
Description Update Allocation and Transfer Based on Changes to Up-Charges
Functional Area Foundation Data
Module Type Business Processing
Module Technology ksh
Catalog ID RMS184
Runtime Parameters NA

Design Overview

In RMS, users are allowed to make rate changes to up-charge cost components and department level up-charges and assign future effective dates to the changes. One of the things that can be designated when these future rate changes are specified is whether this update should also impact any open transfers or allocations with items in the department. If they have been flagged to update open transfers and allocations, then this script will process the updates once they reach their effective date.

Scheduling Constraints

Table 3-12 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

The following scripts can be executed in parallel:

  • batch_alloctsfupd.ksh

  • batch_depchrgupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

The pre-post job batch_costcompupd post should be run after all 5 complete

Pre-Processing

batch_compeffupd.ksh

Post-Processing

batch_costcompupd post (see note above)

Threading Scheme

Threaded by alloc_no and tsf_no.


Restart/Recovery

NA

Key Tables Affected

Table 3-13 Key Tables Affected

Table Select Insert Update Delete

COST_COMP_UPD_GL_TEMP

Yes

Yes

No

Yes

COST_COMP_UPD_STG

Yes

No

No

No

ALLOC_CHRG

Yes

No

Yes

No

ALLOC_HEADER

Yes

No

No

No

ITEM_MASTER

Yes

No

No

No

SHIPMENT

No

No

No

Yes

SHIPSKU

No

No

No

Yes

TSFDETAIL_CHRG

Yes

No

Yes

No

TSFHEAD

Yes

No

No

No

COST_COMP_EXC_LOG

No

Yes

No

No


Design Assumptions

NA

batch_ordcostcompupd (Apply Pending Cost Component and ELC Changes to Purchase Orders)

Module Name batch_ordcostcompupd.ksh
Description Apply Pending Cost Component and ELC Changes to Purchase Orders
Functional Area Foundation Data
Module Type Business Processing
Module Technology ksh
Catalog ID RMS190
Runtime Parameters NA

Design Overview

In RMS, users are allowed to make rate changes to cost components and expense profiles and assign future effective dates for the updates. Additionally, when these future rate changes are specified, users can choose to cascade these changes to lower levels. For orders, changes can be cascaded down from each of the different types:

  • Expense Profiles (country, supplier, or partner)

  • Cost Components (expense or assessment)

This script will process the updates for open orders for each of these types of rate updates once the rate changes reach their effective date.

Scheduling Constraints

Table 3-14 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

The following scripts can be executed in parallel:

  • batch_alloctsfupd.ksh

  • batch_depchrgupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

The pre-post job batch_costcompupd post should be run after all 5 complete

Pre-Processing

batch_compeffupd.ksh

prepost batch_ordcostcompupd pre

Post-Processing

prepost batch_ordcostcompupd post

batch_costcompupd post (see note above)

Threading Scheme

Threaded by order number (order_no)


Restart/Recovery

NA

Key Tables Affected

Table 3-15 Key Tables Affected

Table Select Insert Update Delete

SYSTEM_OPTIONS

Yes

No

No

No

COST_COMP_UPD_GL_TEMP

Yes

Yes

No

Yes

COST_COMP_UPD_STG

Yes

No

No

No

ORDSKU_HTS

Yes

No

No

No

ORDSKU_HTS_ASSESS

Yes

No

No

No

CVB_DETAIL

Yes

No

No

No

CE_ORD_ITEM

Yes

No

No

No

CE_HEAD

Yes

No

No

No

ORDHEAD

Yes

No

No

No

ORDLOC

Yes

No

No

No

ORDSKU

Yes

No

No

No

ORDLOC_EXP

Yes

No

Yes

No

SHIPMENT

Yes

No

No

No

SHIPSKU

Yes

No

No

No

EXP_PROF_HEAD

Yes

No

No

No

COST_ZONE_GROUP_LOC

Yes

No

No

No

CE_CHARGES

No

No

No

Yes

COST_COMP_EXC_LOG

No

Yes

No

No


Design Assumptions

NA

elcexcprg (Purge Aged Cost Component Exceptions)

Module Name ELCEXCPRG.PC
Description Purge Aged Cost Component Exceptions
Functional Area Costing
Module Type Admin
Module Technology ProC
Catalog ID RM S222
Runtime Parameters NA

Design Overview

In RMS, users are allowed to make rate changes to cost components, up-charges and expense profiles with future effective dates. Additionally, when these future rate changes are specified, users can choose to cascade these changes to lower levels. The options for how the updates can be cascaded are described in the table below:

Table 3-16 ELCEXCPRG.PC - Cascade Options

Updated Entity Cascade Options

Expense Profiles (Country, Supplier, or Partner)

Order, Item

Cost Component (Expense)

Country, Supplier, Partner, Item, Order

Cost Component (Assessment)

Item, Order

Cost Component (Up-charge)

Department, Item, Transfer/Allocation

Department Level Up-Charges

Item, Transfer/Allocation


When the processes that apply these changes run, they may raise exceptions if the rate for an entity has been overwritten prior to the application of the future rate change. If so, then exceptions are written to the COST_COMP_EXC_LOG table. This program purges the records from this table based on a number of retention months that is passed as a runtime parameter.

Scheduling Constraints

Table 3-17 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

The following scripts can be executed in parallel:

  • batch_alloctsfupd.ksh

  • batch_depchrgupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

  • Prepost batch_costcompupd post

Pre-Processing

Prepost batch_costcompupd post

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

NA

Key Tables Affected

Table 3-18 Key Tables Affected

Table Select Insert Update Delete

COST_COMP_EXC_LOG

No

No

No

Yes


Design Assumptions

NA

dfrtbld (Build Diff Ratios Based on Sales History)

Module Name dfrtbld.pc
Description Build Diff Ratios Based on Sales History
Functional Area Foundation Data
Module Type Business Processing
Module Technology ProC
Catalog ID RM S214
Runtime Parameters NA

Design Overview

Diff ratios are used by RMS as a way to assign a ratio to a group of diffs or diff combinations based on sales history. The parameters for how these are created are setup online in RMS and include specifying a subclass and one or more diff groups for a particular date range. Users also specify how often the ratios should be refreshed and what types of sales should be considered, regular, promotional and/or clearance.

For ratios that are due to be rebuilt, this batch program uses this information and summarizes the total sales for items with the subclass and diff groups selected. It then calculates a percent to each diff combination/store. Diff ratios are used for PO distribution within RMS.

Scheduling Constraints

Table 3-19 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

This program will likely be run after sales information is uploaded into Oracle Retail.

Pre-Processing

uploadsales_all.ksh

Post-Processing

The SQL*Loader control file dfrtbld.ctl to load the data from ouput file.

Threading Scheme

Threaded by department


Restart/Recovery

This program is for multithreading and restart/recovery.

Key Tables Affected

Table 3-20 Key Tables Affected

Table Select Insert Update Delete

PERIOD

Yes

No

No

No

DIFF_RATIO_HEAD

Yes

No

Yes

No

DIFF_RATIO_DETAIL

No

No

No

Yes

DIFF_GROUP_DETAIL

Yes

No

No

No

V_RESTART_DEPT

Yes

No

No

No

ITEM_MASTER

Yes

No

No

No

ITEM_LOC_HIST

Yes

No

No

No


I/O Specification

This batch will create a comma delimited output data file for sql loader to upload data to table DIFF_RATIO_DETAIL. The control script for the sql loader is dfrtbld.ctl.

Input File Layout

Table 3-21 dfrtbld.pc - Input File Layout

Field Name Field Type Default Value Description

Diff_ratio_id

NA

NA

NA

Seq_no

NA

NA

NA

store

NA

NA

NA

Diff_1

NA

NA

NA

Diff_2

NA

NA

NA

Diff_3

NA

NA

NA

qty

NA

NA

NA

pct

NA

NA

NA


Design Assumptions

NA

lclrbld (Rebuild Dynamic Location Lists)

Module Name lclrbld.pc
Description Rebuild Dynamic Location Lists
Functional Area Foundation Data
Module Type Business Processing
Module Technology ProC
Catalog ID RMS255
Runtime Parameters NA

Design Overview

This program is used to rebuild dynamic location lists based on the criteria defined when the location list was created. Once run, the location list will be updated to include only the locations that currently meet the defined criteria for the list, including adding any new locations. Any locations which no longer fit the criteria will be removed.

Scheduling Constraints

Table 3-22 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

NA

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

Threaded by location list


Restart/Recovery

Table-based restart/recovery is used by the batch program.

Key Tables Affected

Table 3-23 Key Tables Affected

Table Select Insert Update Delete

LOC_LIST_HEAD

Yes

No

Yes

No

LOC_LIST_DETAIL

Yes

Yes

No

Yes


Design Assumptions

NA

batch_rfmvcurrconv (Refresh Currency Conversion Materialized View)

Module Name batch_rfmvcurrconv.ksh
Description Refresh Currency Conversion Materialized View
Functional Area Foundation Data
Module Type Admin
Module Technology ksh
Catalog ID RMS193
Runtime Parameters NA

Design Overview

This script refreshes the materialized view MV_CURRENCY_CONVERSION_RATES.

Scheduling Constraints

Table 3-24 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

It must be scheduled after receiving currency rates from external systems

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

NA

Key Tables Affected

Table 3-25 Key Tables Affected

Table Select Insert Update Delete

MV_CURRENCY_CONVERSION_RATES

Yes

Yes

Yes

Yes

CURRENCY_RATES

Yes

No

No

No

EURO_EXCHANGE_RATE

Yes

No

No

No


Design Assumptions

NA

refmvlocprimaddr (Refresh Address Materialized View)

Module Name refmvlocprimaddr.pc
Description Refresh Address Materialized View
Functional Area Foundation Data
Module Type Admin
Module Technology ProC
Catalog ID RMS305
Runtime Parameters NA

Design Overview

This batch program refreshes the materialized view MV_LOC_PRIM_ADDR based on the ADDR and WH tables. The view will contain primary address information for all locations, including company stores, customer stores, physical and virtual warehouses and external finishers.

Scheduling Constraints

Table 3-26 Scheduling Constraints

Schedule Information Description

Frequency

As needed

Scheduling Considerations

NA

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

NA

Key Tables Affected

Table 3-27 Key Tables Affected

Table Select Insert Update Delete

ADDR

Yes

No

No

No

WH

Yes

No

No

No


Design Assumptions

NA

cremhierdly (Process Pending Merchandise Hierarchy Changes from External Systems)

Module Name cremhierdly.pc
Description Process Pending Merchandise Hierarchy Changes from External Systems
Functional Area Foundation Data
Module Type Business Processing
Module Technology ProC
Catalog ID RMS204
Runtime Parameters NA

Design Overview

This batch program reads merchandise hierarchy records from the PEND_MERCH_HIER table whose effective date is tomorrow or earlier. The PEND_MERCH_HIER table is populated by the Merchandise Hierarchy Reclass Subscription API. Each record is then used to either insert or update existing merchandise hierarchy data in RMS based on the action and hierarchy types. The inserted/updated records are deleted from the PEND_MERCH_HIER table after they have been successfully processed.

This program is only required if updates to the merchandise hierarchy in RMS are being managed outside the application.

Scheduling Constraints

Table 3-28 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

Must run prior to reclsdly.pc

Pre-Processing

NA

Post-Processing

reclsdly.pc

Threading Scheme

NA


Restart/Recovery

This program is setup for multithreading and restart/recovery.

Key Tables Affected

Table 3-29 Key Tables Affected

Table Select Insert Update Delete

PERIOD

Yes

No

No

No

PEND_MERCH_HIER

Yes

No

No

Yes

PEND_MERCH_HIER_TL

No

No

No

Yes

DIVISION

No

Yes

Yes

No

GROUPS

No

Yes

Yes

No

DEPS

No

Yes

Yes

No

CLASS

No

Yes

Yes

No

SUBCLASS

No

Yes

Yes

No


Design Assumptions

NA

reclsdly (Reclassify Items in Merchandise Hierarchy

Module Name Reclsdly.pc
Description Reclassify Items in Merchandise Hierarchy
Functional Area Foundation
Module Type Business Processing
Module Technology ProC
Catalog ID RMS302
Runtime Parameters NA

Design Overview

This batch program is used to reclassify items from one department/class/subclass combination to another. Reclassification events that are due to go into effect the next day are processed by this batch process. Before the reclassification is executed, validation is performed to make sure that there are no issues which would prevent the reclassification from moving forward. If not, then the updates are made to update the item's merchandise hierarchy, as well as other related updates, such as moving the value of the inventory in the stock ledger and notifying the Pricing service of the update. Any issues that prevent the item from being reclassified raise a non-fatal error in the program and write the error to the MC_REJECTIONS table.

Scheduling Constraints

Table 3-30 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

Should run after cremhierdly

Pre-Processing

Prepost pre reclsdly

Post-Processing

Prepost reclsdly.post

Threading Scheme

Threaded by reclass_no


Restart/Recovery

This program is setup for multithreading and restart/recovery.

Key Tables Affected

Table 3-31 Key Tables Affected

Table Select Insert Update Delete

RECLASS_ITEM

Yes

No

No

Yes

RECLASS_HEAD

Yes

No

No

Yes

RECLASS_HEAD_TL

No

No

No

Yes

ITEM_MASTER

Yes

No

Yes

No

DEPS

Yes

No

No

No

GROUPS

Yes

No

No

No

PACKITEM

Yes

No

No

No

DEAL_ITEM_LOC_EXPLODE

Yes

No

No

Yes

DEAL_ITEMLOC

Yes

No

No

No

DEAL_HEAD

Yes

No

No

No

ORDHEAD

Yes

No

Yes

No

ORDSKU

Yes

No

No

No

DEAL_CALC_QUEUE

Yes

Yes

No

No

HIST_REBUILD_MASK

No

Yes

No

No

RECLASS_ERROR_LOG

No

Yes

Yes

Yes

STAKE_SKU_LOC

Yes

Yes

Yes

Yes

ITEM_LOC_SOH

Yes

No

Yes

No

REPL_ITEM_LOC_UPDATES

No

Yes

No

No

TRAN_DATA

No

Yes

No

No

SKULIST_DEPT

Yes

Yes

No

No

MC_REJECTIONS

No

Yes

No

No

RPM_ITEM_MODIFICATION

No

Yes

Yes

No


Design Assumptions

NA

supmth (Rollup of Supplier Data)

Module Name supmth.pc
Description Rollup of Supplier Data
Functional Area Inventory
Module Type Business Processing
Module Technology ProC
Catalog ID RMS369
Runtime Parameters NA

Design Overview

The primary function of supmth.pc is to convert daily transaction data to monthly data. After all data is converted, the daily information is deleted to reset the system for the next period by the batch module prepost and its supmth_post function.

The supmth.pc batch accumulates SUP_DATA amounts by department/supplier/transaction type and creates or updates one SUP_MONTH row for each department/supplier combination. Based on the transaction type on SUP_DATA, the following transactions are written to SUP_MONTH:

  • type 1 – purchases at cost (written for consignment sales and orders received at POS or online)

  • type 2 – purchases at retail (written for consignment sales and orders received at POS or online)

  • type 3 – claims at cost (written for claim dollars refunded on RTV orders)

  • type 10 – markdowns at retail (net amount based on markdowns, markups, markdown cancellations and markup cancellations)

  • type 20 – order cancellation costs (written for all supplier order cancellations)

  • type 30 – sales at retail (written for consignment stock sales)

  • type 40 – quantity failed (written for QC shipments with failed quantities)

  • type 70 – markdowns at cost (net amount based on supplier cost markdowns)

Scheduling Constraints

Table 3-32 Scheduling Constraints

Schedule Information Description

Frequency

Monthly

Scheduling Considerations

NA

Pre-Processing

NA

Post-Processing

Prepost supmth post

Threading Scheme

Threaded by department


Restart/Recovery

The logical unit of work is dept, supplier.

Key Tables Affected

Table 3-33 Key Tables Affected

Table Select Insert Update Delete

SUP_DATA

Yes

No

No

No

SUP_MONTH

No

Yes

No

No

SYSTEM_VARIABLES

Yes

No

No

No


Design Assumptions

NA

schedprg (Purge Aged Store Ship Schedule)

Module Name schedprg.pc
Description Purge Aged Store Ship Schedule
Functional Area Foundation Data
Module Type Admin
Module Technology ProC
Catalog ID RMS356
Runtime Parameters NA

Design Overview

This program will purge all old records related to store ship dates and location and company closed dates and exceptions. Old records are determined by the Ship Schedule History months and Location Closed History months system parameters.

Scheduling Constraints

Table 3-34 Scheduling Constraints

Schedule Information Description

Frequency

Monthly

Scheduling Considerations

NA

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

This program will periodically commit delete operations. Periodic commits are performed to ensure that rollback segments are not exceeded in case of considerable volume.

Key Tables Affected

Table 3-35 Key Tables Affected

Table Select Insert Update Delete

SYSTEM_OPTIONS

Yes

No

No

No

STORE_SHIP_DATE

No

No

No

Yes

COMPANY_CLOSED_EXCEP

No

No

No

Yes

COMPANY_CLOSED

No

No

No

Yes

COMPANY_CLOSED_TL

No

No

No

Yes

LOCATION_CLOSED

No

No

No

Yes

LOCATION_CLOSED_TL

No

No

No

Yes


Design Assumptions

NA

prchstprg(Purge Aged Price History Data)

Module Name prchstprg.pc
Description Purge Aged Price History Data
Functional Area Foundation Data
Module Type Admin
Module Technology ProC
Catalog ID RMS298
Runtime Parameters NA

Design Overview

The PRCHSTPRG program deletes PRICE_HIST records, which are older than a number of retention days specified SYSTEM_OPTIONS price_hist_retention_days.

This program ensures the most recent PRICE_HIST record for the item/location/tran type combination is preserved and deletes all aged records.

Scheduling Constraints

Table 3-36 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

Multi threaded. Threaded by table partition


Restart/Recovery

This program will periodically commit delete operations. Restart/Recovery is achieved by processing records that have not been deleted.

Key Tables Affected

Table 3-37 Key Tables Affected

Table Select Insert Update Delete

PERIOD

Yes

No

No

No

SYSTEM_OPTIONS

Yes

No

No

No

PRICE_HIST

No

No

No

Yes

DBA_TAB_PARTITIONS

Yes

No

No

No


tcktdnld (Download of Data to be Printed on Tickets)

Module Name tcktdnld.pc
Description Download of Data to be Printed on Tickets
Functional Area Foundation Data
Module Type Integration
Module Technology PROC
Catalog ID RMS59
Runtime Parameters NA

Design Overview

This program creates an output file containing the information to be printed on a ticket or label for a particular item/location. This program is driven by the requests for tickets generated from RMS and RPM. The details of what should be printed on each ticket are defined in RMS on the TICKET_TYPE_DETAIL table.

Scheduling Constraints

Table 3-38 Scheduling Constraints

Schedule Information Description

Frequency

Daily

Scheduling Considerations

NA

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

NA

Key Tables Affected

Table 3-39 Key Tables Affected

Table Select Insert Update Delete

TICKET_REQUEST

Yes

No

No

Yes

STORE

Yes

No

No

No

TICKET_TYPE_HEAD

Yes

No

No

No

ITEM_MASTER

Yes

No

No

No

TICKET_TYPE_DETAIL

Yes

No

No

No

UDA_VALUES

Yes

No

No

No

UDA_VALUES_TL

Yes

No

No

No

UDA_ITEM_LOV

Yes

No

No

No

UDA

Yes

No

No

No

UDA_TL

Yes

No

No

No

UDA_ITEM_FF

Yes

No

No

No

UDA_ITEM_FF_TL

Yes

No

No

No

UDA_ITEM_DATE

Yes

No

No

No

ITEM_TICKET

Yes

No

No

No

ITEM_LOC_SOH

Yes

No

No

No

ITEM_LOC

Yes

No

No

No

ITEM_SUPPLIER

Yes

No

No

No

ITEM_SUPP_COUNTRY_DIM

Yes

No

No

No

ITEM_SUPP_COUNTRY

Yes

No

No

No

DEPS

Yes

No

No

No

DEPS_TL

Yes

No

No

No

CLASS

Yes

No

No

No

CLASS_TL

Yes

No

No

No

SUBCLASS

Yes

No

No

No

SUBCLASS_TL

Yes

No

No

No

ORDHEAD

Yes

No

No

No

ORDSKU

Yes

No

No

No

WH

Yes

No

No

No

VAT_ITEM

Yes

No

No

No

RPM_PC_TICKET_REQUEST

Yes

No

No

Yes

GTAX_ITEM_ROLLUP

Yes

No

No

No


Integration Contract

Integration Type Download from RMS
File Name Determined by runtime parameters
Integration Contract IntCon000107

Output File Layout

Table 3-40 tcktdnld.pc - Output File Layout

Record Name Field Name Field Type Default Value Description

FHEAD

File Type Record Descriptor

Char(5)

FHEAD

Identifies file record type

File Line Sequence

Number(10)

NA

Line number of the current file

File Type Definition

Char(4)

TCKT

Identifies file as ’Print Ticket Requests'

File Create Date

Char(14)

NA

The date on which the file was created in ’YYYMMDDHHMISS' format

THEAD

File Type Record Descriptor

Char(5)

THEAD

Identifies file record type

File Line Sequence

Number(10)

NA

Line number of the current file

ITEM

Char(25)

NA

ID number of the transaction level item for which the ticket applies.

Ticket Type

Char(4)

NA

ID which indicates the ticket type to be printed

Location Type

Char(1)

NA

Identifies the type of location for which tickets will be printed. Valid values are store (S) and warehouse (W).

Location

Char(10)

NA

The ID of the store or warehouse for which tickets will be printed

Quantity

Number(12,4)

NA

The quantity of tickets to be printed; which includes 4 implied decimal places

TCOMP

File Type Record Descriptor

Char(5)

TCOMP

Identifies file record type

File Line Sequence

Number(10)

NA

Line number of the current file

ITEM

Char(25)

NA

ID number of the item which is only populated if the item in THEAD is a pack item

Quantity

Number(12,4)

NA

Quantity of the component item as a part of the pack; includes 4 implied decimal places

TDETL

File Type Record Descriptor

Char(5)

TDETL

Identifies file record type

File Line Sequence

Number(10)

NA

Line number of the current file

Detail Sequence Number

Number(10)

NA

Sequential number assigned to the detail records

Ticket Item

Char(4)

NA

ID indicating the detail to be printed on the ticket. If the attribute is a UDA, then this will contain the ID of the UDA. Otherwise, it is the code associated with the attribute in RMS (such as, CLSS = class)

Attribute Description

Char(120)

NA

Description of the attribute – either the UDA description or the RMS description for the attribute

Value

Char(250)

NA

Detail to be printed on the ticket (for example:. Item number, Department Number, Item description)

Supplement

Char(120)

NA

Supplemental description to the Value (for example: Department Name)

TTAIL

File Type Record Descriptor

Char(5)

TTAIL

Identifies file record type

File Line Sequence

Number(10)

NA

Line number of the current file

Transaction Detail Line Count

Number(6)

sum of detail lines

Sum of the detail lines within a transaction

FTAIL

File Type Record Descriptor

Char(5)

FTAIL

Identifies file record type

File Line Sequence

Number(10)

NA

Line number of the current file


Design Assumptions

NA

refmvl10entity (Refresh MV MV_L10N_ENTITY)

Module Name REFMVL10ENTITY.PC
Description Refresh Materialized view MV_L10N_ENTITY
Functional Area Administration
Module Type Admin
Module Technology ProC
Catalog ID RMS304
Runtime Parameters NA

Design Overview

This program refreshes the materialized view MV_L10N_ENTITY that is based on ADDR, OUTLOC, COMPHEAD, COUNTRY_ATTRIB table.

Scheduling Constraints

Table 3-41 Scheduling Constraints

Schedule Information Description

Scheduling Considerations

NA

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

This batch program uses table-based restart/recovery.

Locking Strategy

NA

Security Considerations

NA

Performance Considerations

NA

Key Tables Affected

Table 3-42 Key Tables Affected

Table Select Insert Update Delete

ADDR

Yes

No

No

No

OUTLOC

Yes

No

No

No

COMPHEAD

Yes

No

No

No

COUNTRY_ATTRIB

Yes

No

No

No


likestorebatch (Like Store Batch Processing)

Module Name likestorebatch.ksh
Description Like Store Batch Processing
Functional Area Foundation
Module Type Business Processing
Module Technology Ksh
Catalog ID N/A
Runtime Parameters $UP {Connect String}

Design Overview

This batch program is used to process stores from the STORE_ADD table with like stores to copy attributes and items from an existing store to a new store.

The likestore batch program picks up all rows from the STORE_ADD table wherein the PROCESS_STATUS is set to 02STOREADD_POST and the LIKESTORE column is populated.

It will then gather all items associated to the likestore and explode this to the SVC_LIKE_STORE_STAGING table and process all the inserted records by chunk. Chunking is based on the RMS_PL_SQL_BATCH_CONFIG.MAX_CHUNK_SIZE, and it should be noted that there is no sorting or grouping done when chunking the rows.

For each chunk, records are inserted on the temporary table SVC_LIKE_STORE_GTT, which will serve as the driving table for the like store process of each thread.

For each successfully processed chunk, it will delete all the matching rows from the SVC_LIKE_STORE_STAGING. Once all rows are processed, the STORE_ADD.PROCESS_STATUS is updated for the specific store, depending on whether there are records remaining in the SVC_LIKE_STORE_STAGING for that store. If there are no more entries for a store, then the store will be deleted from the STORE_ADD table. If there are entries remaining, then the status will be updated to 05LIKESTORE_FAIL.

Scheduling Constraints

Table 3-43 Scheduling Constraints

Schedule Information Description

Frequency

Hourly

Scheduling Considerations

NA

Pre-Processing

NA

Post-Processing

NA

Threading Scheme

NA


Restart/Recovery

In case of failure, the likestore batch will not pick up any new entries from the STORE_ADD table until the issue has been rectified. Successfully processed records are deleted from the SVC_LIKE_STORE_STAGING.

Key Tables Affected

Table 3-44 Key Tables Affected

Table Select Insert Update Delete

STORE_ADD

Yes

No

Yes

Yes

ITEM_EXP_HEAD

No

Yes

No

No

ITEM_EXP_DETAIL

No

Yes

No

No

ITEM_LOC

No

Yes

No

No

ITEM_LOC_SOH

No

Yes

No

No

PRICE_HIST

No

Yes

No

No

ITEM_SUPP_COUNTRY_LOC

No

Yes

No

No

REPL_ITEM_LOC

No

Yes

No

No

REPL_DAY

No

Yes

No

No

REPL_ITEM_LOC_UPDATES

No

Yes

No

No

SVC_LIKE_STORE_STAGING

Yes

Yes

No

Yes

SVC_LIKE_STORE_GTT

Yes

Yes

No

Yes


Design Assumptions

NA

straddbatch.ksh (Store Add Asynchronous Process)

Module Name straddbatch.ksh
Description Store Add Asynchronous Process
Functional Area Foundation Data
Module Type Admin
Module Technology .ksh
Catalog ID RMS496
Runtime Parameters NA

Business Overview

This asynchronous process creates new stores in RMS, along with all their associated records when a new store is initiated online in RMS or via the Store Subscription API.

Key Tables Affected

Table 3-45 Key Tables Affected

Table Select Insert Update Delete

STORE _ADD

Yes

No

No

Yes

STORE

Yes

Yes

No

No

STOCK_LEDGER_INSERTS

No

Yes

No

No

RPM_ZONE

No

Yes

No

No

RPM_ZONE_LOCATION

No

Yes

No

No

RMS_ASYNC_STATUS

Yes

Yes

Yes

No

RMS_ASYNC_RETRY

Yes

Yes

Yes

No

RMS_ASYNC_JON

Yes

No

No

No

LOC_TRAITS_MATRIX

No

Yes

No

No

COST_ZONE

No

Yes

No

No

COST_ZONE_GROUP_LOC

No

Yes

No

No

STORE_HIERARCHY

No

Yes

No

No

WF_COST_RELATIONSHIP

No

Yes

No

No

SOURCE_DLVRY_SCHED

No

Yes

No

No

SOURCE_DLVRY_SCHED_EXC

No

Yes

No

No

SOURCE_DLVRY_SCHED_DAYS

No

Yes

No

No

COMPANY_CLOSED_EXCEP

No

Yes

No

No

LOCATION_CLOSED

No

Yes

No

No

POS_STORE

No

Yes

No

No

PERIOD

Yes

No

No

No

SYSTEM_OPTIONS

Yes

No

No

No

STORE_ADD_L10N_EXT

Yes

Yes

No

Yes

STORE_ADD_CFA_EXT

Yes

Yes

No

Yes


Design Assumptions

The materialized views MV_LOC_SOB, MV_L10N_ENTITY and MV_LOC_PRIM_ADDR will be refreshed after the store has been added. It is assumed that the materialized view will still be available to other processes during the refresh.

Queue Creation

The function RMS_ASYNC_QUEUE_SQL.CREATE_QUEUE_SUBSCRIBER is called to drop and recreate the queue table if one already exists. This function is called with the JOB_TYPE as STORE_ADD (for example, the constant ASYNC_JOB_STORE_ADD) to create a queue for store processing.

Design Overview - Process Steps

This section describes the key design aspect of the store add process.

The overall process consists of 3 steps as outlined below.

  1. New (status-code: 00NEW). This is the status when store is just created.

  2. Store-Add (status-code: 01STOREADD)

  3. Store-Add-Post (status-code: 02STOREADD_POST)

The status-code of the current completed step of the process is updated in store_add.process_status column.

If STORE_ADD.LIKESTORE column is not null for the store, the status will remain in 02STOREADD_POST and the record will be picked up by the likestorebatch.ksh which runs as an hourly job. If not, then the STORE entry will be removed from the STORE_ADD table.

Running entire store-add as batch in case of AQ issues

In case of Oracle AQ issues if store-add step is not running in async mode then entire store-add proess can also be run in batch using below command

storeaddbatch.ksh $UP

This is provided only as a workaround in case of AQ issues. The recommended method is to let store-add step be processed in Async through AQ as it is designed.

Building Schedule Dependencies between Async process and other batches

Customers may need to build scheduling dependencies between async processes and other batch programs. For example, making pos-extract batches dependent upon completion of Like-store step of the store-add process. To do that, create a job in scheduler using following command and make required batches dependent upon this job.

straddasyncwait.ksh $UP "03LIKESTORE"

Similarly, if batch program needs to be made dependent upon other steps, schedule jobs by passing desired status.

Monitoring Progress of Store-Add Processes

The current completed step of the store-add process is updated in store_add.process_status column. In case of a Like-Store step (which is a separate batch program) the status of a store will remain in 02STOREADD_POST, until it is processed by the likestore batch program, which will in turn change the status to 03LIKETORE.

Once the process is completed, the store will be subsequently removed from the STORE_ADD table. If not, then the status will be changed to '05LIKESTORE_FAIL'.

CORESVC_STORE_ADD_SQL. ADD_STORE (Store Add Asynchronous Process)

Module Name CORESVC_STORE_ADD_SQL. ADD_STORE
Description Asynchronous Process
Functional Area Foundation Data
Module Type Admin
Module Technology PL SQL
Catalog ID RMS496
Runtime Parameters NA

Business Overview

This asynchronous process creates new stores in RMS, along with all their associated records when a new store is initiated online in RMS or via the Store Subscription API. Previously, the likestore functionality is also processed within the store add asynchronous process, but this has now been decoupled from the store add program and now runs as a separate hourly batch job, removing the dependency between both processes.

Key Tables Affected

Table 3-46 Key Tables Affected

Table Select Insert Update Delete

STORE _ADD

Yes

No

No

Yes

STORE

Yes

Yes

No

No

STOCK_LEDGER_INSERTS

No

Yes

No

No

RPM_ZONE

No

Yes

No

No

RPM_ZONE_LOCATION

No

Yes

No

No

RMS_ASYNC_STATUS

Yes

Yes

Yes

No

RMS_ASYNC_RETRY

Yes

Yes

Yes

No

RMS_ASYNC_JON

Yes

No

No

No

LOC_TRAITS_MATRIX

No

Yes

No

No

COST_ZONE

No

Yes

No

No

COST_ZONE_GROUP_LOC

No

Yes

No

No

STORE_HIERARCHY

No

Yes

No

No

WF_COST_RELATIONSHIP

No

Yes

No

No

SOURCE_DLVRY_SCHED

No

Yes

No

No

SOURCE_DLVRY_SCHED_EXC

No

Yes

No

No

SOURCE_DLVRY_SCHED_DAYS

No

Yes

No

No

COMPANY_CLOSED_EXCEP

No

Yes

No

No

LOCATION_CLOSED

No

Yes

No

No

POS_STORE

No

Yes

No

No

PERIOD

Yes

No

No

No

SYSTEM_OPTIONS

Yes

No

No

No

STORE_ADD_L10N_EXT

Yes

Yes

No

Yes

STORE_ADD_CFA_EXT

Yes

Yes

No

Yes


Design Assumptions

The materialized views MV_LOC_SOB, MV_L10N_ENTITY and MV_LOC_PRIM_ADDR will be refreshed after the store has been added. It is assumed that the materialized view will still be available to other processes during the refresh.

Queue Creation

The function RMS_ASYNC_QUEUE_SQL.CREATE_QUEUE_SUBSCRIBER is called to drop and recreate the queue table if one already exists. This function is called with the JOB_TYPE as STORE_ADD (for example, the constant ASYNC_JOB_STORE_ADD) to create a queue for store processing.

Design Overview - Process Steps

This section describes the key design aspect of the store add process.

The overall process consists of 3 steps as outlined below.

  1. New (status-code: 00NEW). This is the status when store is just created.

  2. Store-Add (status-code: 01STOREADD)

  3. Store-Add-Post (status-code: 02STOREADD_POST)

The status-code of the current completed step of the process is updated in store_add.process_status column.

If STORE_ADD.LIKESTORE column is not null for the store, the status will remain in 02STOREADD_POST and the record will be picked up by the likestorebatch.ksh which runs as an hourly job. If not, then the STORE entry will be removed from the STORE_ADD table.

Package Impact

Package name: coresvc_store_add_sql

Spec file name: coresvc_store_adds.pls

File name: coresvc_store_adds/b.pls

Function Level Description - ADD_STORE

Function: ADD_STORE
           (O_error_message          IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
            I_rms_async_id           IN      RMS_ASYNC_STATUS.RMS_ASYNC_ID%TYPE)

This function contains the core logic for adding a new store to RMS. The process of adding a store to RMS starts with store.fmb form. When a user creates a new store by using the form, an entry is made in the STORE_ADD table. Also entries are made into RMS_ASYNC_STATUS with the status as new and RMS_ASYNC_RETRY tables with a new RMS_ASYNC_ID. The RMS_ASYNC_ID is placed in the queue for processing. The de-queue process picks the RMS_ASYNC_ID generated and based on the JOB_TYPE (STORE_ADD) calls the CORESVC_STORE_ADD_SQL.ADD_STORE for further processing.

This function:

  • Calls PM_NOTIFY_API_SQL.NEW_LOCATION to create pricing records to update the RPM tables.

  • Calls the functions L10N_FLEX_ATTRIB_SQL.ADD_STORE_ATTRIB and CFA_SQL.ADD_STORE_ATTRIB.

  • Makes entries into cost-zone tables.

  • If like-store is mentioned and delivery schedule needs to be copied then copy source-delivery-schedule information. Hence entries are made into SOURCE_DLVRY_SCHED, SCHED_EXC and SCHED_DAYS tables.

  • If like-store is mentioned and locations close information needs to be copied then make entries into COMPANY_CLOSED_EXCEP and LOCATION_CLOSED tables based on like store.

  • Calls the function STKLEDGR_SQL.STOCK_LEDGER_INSERT to make entry into STOCK_LEDGER_INSERTS table.

  • Copies WF_COST_RELATIONSHIP and DEAL_PASSTHRU data for the specified costing location.

  • If like-store is mentioned then call the local function LIKE_STORE.

  • The MV_LOC_SOB, MV_L10N_ENTITY and MV_LOC_PRIM_ADDR materialized views are refreshed as well.

  • After completion of the process, it deletes the records from STORE_ADD_L10N_EXT, STORE_ADD_CFA_EXT and STORE_ADD tables.

On successful creation of the store the user is prompted with a message saying the RMS_ASYNC_ID is processed successfully. In case there is a failure during the store creation the user will also be notified. The user has to use the Asynchronous Job log form to view and reprocess the failed store. On clicking on reprocess in the Asynchronous Job log form an entry is made into the RMS_ASYNC_RETRY table. The RMS_ASYNC_ID is again placed in the queue for processing.

Spec file name: rmsasyncprocs/b.pls

Function Level Description - ENQUEUE_STORE_ADD

Function: ENQUEUE_STORE_ADD
           (O_error_message          IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
            I_rms_async_id           IN      RMS_ASYNC_STATUS.RMS_ASYNC_ID%TYPE)

This function adds the RMS_ASYNC_ID associated with the JOB_TYPE STORE_ADD created from the store form to the asynchronous queue. It also makes entries into the RMS_ASYNC_STATUS and RMS_ASYNC_RETRY table to track the status of the asynchronous job.

Function Level Description - ENQUEUE_STORE_ADD_RETRY

Function: ENQUEUE_STORE_ADD_RETRY
           (O_error_message          IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
            I_rms_async_id           IN      RMS_ASYNC_STATUS.RMS_ASYNC_ID%TYPE)

This function puts the RMS_ASYNC_ID associated with a STORE_ADD event to the asynchronous queue again for re-processing. It is invoked through the asynchronous job log form.

Function Level Description - NOTIFY_STORE_ADD

Procedure: NOTIFY_STORE_ADD(context     raw,
                            reginfo     sys.aq$_reg_info,
                            descr       sys.aq$_descriptor,
                            payload     raw,
                            payloadl    number)

This procedure is called during the de-queue process. This procedure calls the function CORESVC_STORE_ADD_SQL.ADD_STORE for store creation. Once the store creation is completed successfully it calls the function RMS_ASYNC_PROCESS_SQL.WRITE_SUCCESS to update the status of the RMS_ASYNC_ID as success. During a failure in store creation it calls the function RMS_ASYNC_PROCESS_SQL.WRITE_ERROR to update the status as error and also to update the error message. The user is notified of the success/failure of the store creation process.

Operations and Monitoring

This section describes the details required for running and monitoring this process.

Running entire Store-Add as Batch in Case of AQ Issues

In case of Oracle AQ issues if a store-add step is not running in async mode then the entire store-add process can also be run in batch using below command.

storeaddbatch.ksh $UP

This is provided only as a workaround in case of AQ issues. The recommended method is to let the store-add step be processed in Async through AQ as it is designed.

Building Schedule Dependencies between Async Process and other Batches

Customers may need to build scheduling dependencies between async processes and other batch programs. For example, making pos-extract batches dependent upon completion of a Like-store step of the store-add process. To do that, create a job in the scheduler by using the following command and make the required batches dependent upon this job.

straddasyncwait.ksh $UP "03LIKESTORE"

Similarly, if the batch program needs to be made dependent upon other steps, schedule jobs by passing desired status.

Monitoring Progress of Store-Add Processes

The current completed step of the store-add process is updated in the store_add.process_status column. In case of a Like-Store step (which is a separate batch program), the status of a store will remain in 02STOREADD_POST, until it is processed by the likestore batch program, which will in turn change the status to 03LIKETORE.

Once the process is completed, the store will be subsequently removed from the STORE_ADD table. If not, then the status will be changed to '05LIKESTORE_FAIL'.