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:

• Updates to Cost Components that must be applied other foundation data and transactions

  • batch_compeffupd.ksh

  • batch_alloctsfupd.ksh

  • batch_depchrgupd.ksh

  • batch_expprofupd.ksh

  • batch_itmcostcompupd.ksh

  • batch_ordcostcompupd.ksh

  • elcexcprg.ksh

• Rebuilds of detail information for lists/groups

  • dfrtbld.pc

  • lclrbld.pc

  • batch_rfmvcurrconv.ksh

  • refmvlocprimadd.ksh

• Application of pending changes

  • cremhierdly.pc

  • reclsdly.pc

• Rollup of detailed information

  • supmth.pc

• Foundation Data Purges

  • admin_api_purge.ksh

  • schedprg.pc

  • prchstprg.pc

Note: For more information on Foundation Data, see the Introduction 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'.