Foundation Data Maintenance

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

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
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, you 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, you 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.

Restart/Recovery

N/A

Design Assumptions

N/A

Apply Pending Item Cost Component Updates (batch_itmcostcompupd)

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
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, you 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, you 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.

Restart/Recovery

N/A

Design Assumptions

N/A

Apply Pending Rate Changes to Expense Profiles (batch_expprofupd)

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
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, you 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, you 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.

Restart/Recovery

N/A

Design Assumptions

N/A

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

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
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, you 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, you 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.

Restart/Recovery

N/A

Design Assumptions

N/A

Build Diff Ratios Based on Sales History (dfrtbld)

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
Wrapper Script	rmswrap_multi_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

Diff ratios are used by Merchandising 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 Merchandising 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 Merchandising.

Restart/Recovery

This program is for multithreading and restart/recovery.

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.

Output File Layout

Table 3-1 dfrtbld.pc - Input File Layout

Field Name	Field Type	Default Value	Description
Diff_ratio_id	N/A	N/A	N/A
Seq_no	N/A	N/A	N/A
store	N/A	N/A	N/A
Diff_1	N/A	N/A	N/A
Diff_2	N/A	N/A	N/A
Diff_3	N/A	N/A	N/A
qty	N/A	N/A	N/A
pct	N/A	N/A	N/A

Design Assumptions

N/A

Like Store Batch Processing (likestorebatch)

Module Name	likestorebatch.ksh
Description	Like Store Batch Processing
Functional Area	Foundation
Module Type	Business Processing
Module Technology	Ksh
Catalog ID	N/A
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Previously, the like store functionality was also processed within the store add asynchronous process. However, this posed an issue when the like store process abnormally ends, which will hold up the store add process. There was also a performance consideration with the like store process, as it was possible that a single like store can have millions of items, which will take a long time to process, thus preventing the store add asynchronous process to process new records. The like store process has been decoupled from the store add program and now runs as a separate hourly batch job, removing the dependency between both processes.

The like store batch program picks up all rows from the store add staging table wherein the process status is set to 02STOREADD_POST and the like store column is populated. It will then gather all items associated to the like store and explode this to the like store staging table and process all the inserted records by chunk. Chunking is based on the system parameter maximum 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 into the temporary table for store add, 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 like store staging table. Once all rows are processed, the process status column is updated for the specific store, depending on whether there are records remaining in the like store staging table 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.

Restart/Recovery

In case of failure, the like store batch will not pick up any new entries from the store add table until the issue has been rectified. Errors are determined by looking up like store staging, if there are any rows left from the previous run. Successfully processed records are deleted from the staging table.

Design Assumptions

N/A

Process Pending Merchandise Hierarchy Changes from External Systems (cremhierdly)

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	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program reads merchandise hierarchy records from the pending merchandise hierarchy table whose effective date is tomorrow or earlier. The pending merchandise hierarchy 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 Merchandising based on the action and hierarchy types. The inserted/updated records are deleted from the pending merchandise hierarchy table after they have been successfully processed.

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

Restart/Recovery

This program is setup for multithreading and restart/recovery.

Design Assumptions

N/A

Purge Aged Cost Component Exceptions (elc_except_purge_job)

Module Name	rtvprg.pc
Description	Purge Aged Returns to Vendors
Functional Area	Transfers, Allocations and RTVs
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS320
Runtime Parameters	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

In RMFCS, you 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, you can choose to cascade these changes to lower levels. The options for how the updates can be cascaded are described in the table below:

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 component exceptions log table.

Thread assignment program will filter eligible records from cost component exceptions log table based on its purge criteria from defined number of retention months (default to 6 months). These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic will process all records from the staging table. Using bulk processing, this program will delete the records from cost component exceptions log table. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 3-2 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_ELC_EXC_PURGE_STG	Yes	Yes	No	Yes
COST_COMP_EXC_LOG	No	No	No	Yes

Design Assumptions

N/A

Purge Aged Cost Component Exceptions (elcexcprg)

Module Name	ELCEXCPRG.PC
Description	Purge Aged Cost Component Exceptions
Functional Area	Costing
Module Type	Admin
Module Technology	ProC
Catalog ID	RM S222
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, you 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, you 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 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.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Aged Price History Data (prchstprg)

Module Name	prchstprg.pc
Description	Purge Aged Price History Data
Functional Area	Foundation Data
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS298
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program deletes price history records, which are older than the price history retention days system parameter.

This program ensures that the most recent price history record for the item/location/transaction type combination is preserved and deletes all aged records.

Restart/Recovery

This program will use the commit_max_ctr on the restart_control table to periodically commit SQL delete operations. Restart/Recovery is achieved by processing records that have not been deleted. The restart bookmark table stores the current partition position as the bookmark string to restart a thread.

However, in cases where the price history table is very large, a particularly large rollback segment may be specified to reduce the risk of exceeding rollback segment space. This will depend on the size of normal rollback segments and the size of the price history table.

Performance Considerations

The commit max counter field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The recommended commit counter setting is 10000 records (subject to change based on experimentation). In case the price history table is very large then the number of partitions on the table may be increased and then after the number of threads for this program should be increased.

Design Assumptions

N/A

Purge Aged Price History Data (price_hist_purge_job)

Module Name	price_hist_purge_job
Description	Purge Aged Price History Data
Functional Area	Foundation Data
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from price history tables based on its purge criteria from system parameter settings which is Price History Retention Days. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the old/aged records from price history table and keep only the most recent records for the item/location/transaction type combinations. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

The decision to insert or not to insert the records into the history tables is based on the Archive Indicator and Archive Job Indicator from the Background Process Configuration table.

If both the Archive Indicator and Archive Job Indicator values are Y, then the data from the base tables are inserted into the history tables.
If both indicators are set to ‘N’, then the records are deleted from the base tables without inserting into the history tables.

Note:

For more information on how to configure this process for archiving, see the Merchandising Implementation Guide section entitled "Background Process Configuration".

Restart/Recovery

N/A

Key Tables Affected

Table 3-4 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_PRICE_HIST_PURGE_STG	Yes	Yes	No	Yes
PRICE_HIST	No	No	No	Yes
DBA_TAB_PARTITIONS	Yes	No	No	No
PRICE_HIST_PRG_HIST	No	Yes	No	No

Purge Aged Store Ship Schedule (activity_sched_purge_job)

Module Name	activity_sched_purge_job
Description	Purge Aged Store Ship Schedule
Functional Area	Foundation Data
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID
Runtime Parameters	Database connection
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps of processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from location closed, company closed exceptions and company closed tables based on its purge criteria from system parameter settings. The Location Closed History Months parameter will determine how long a location and/or company with close date should remain on the associated tables. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

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

Restart/Recovery

N/A

Key Tables Affected

Table 3-5 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_SCHED_PURGE_STG	Yes	Yes	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

N/A

Purge Aged Store Ship Schedule (schedprg)

Module Name	schedprg.pc
Description	Purge Aged Store Ship Schedule
Functional Area	Foundation Data
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS356
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

This program will use the commit max counter on the restart control table to periodically commit delete operations. Periodic commits are performed to ensure that rollback segments are not exceeded in case of considerable volume.

Design Assumptions

N/A

Purge Inactive Currency Rates (currency_rates_purge_job)

Module Name	currency_rates_purge_job
Description	Purge inactive currency rates
Functional Area	Foundation
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible currency rates records based on its purge criteria from system parameter settings. The currency rates purge months parameter in system options will hold the number of months after which an inactive exchange rate can be purged from the system. The inactive currency rates which are earlier than system options purge months are captured for deletion. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will purge the records from currency rates table. It will free up and clean the staging table afterwards.

There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 3-6 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_CURRENCY_RATES_PURGE_STG	Yes	Yes	No	Yes
CURRENCY_RATES	Yes	No	No	Yes

Design Assumptions

N/A

Purge Manage Admin Records (admin_api_purge)

Module Name	admin_api_purge.ksh
Description	Purge Manage Admin records
Functional Area	Administration
Module Type	Admin
Module Technology	ksh
Catalog ID
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 Merchandising and Sales Audit and will help in keeping the size of these tables controlled.

Restart/Recovery

N/A

I/O Specification

N/A

Rebuild Dynamic Item Lists (itmlrbld)

Module Name	itmlrbld.pc
Description	Rebuild Dynamic Item Lists
Functional Area	Foundation Data
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS255
Runtime Parameters	rmswrap_multi.ksh

Design Overview

This program is used to rebuild dynamic item lists based on the criteria defined when the item list was created. Once run, the item list will be updated to include only items that currently meet the defined criteria for the item list. All item's which no longer fit the criteria will be removed. Any addition or deletion of items as part of item list would reflect in scheduled Item Maintenance if corresponding item list is used.

Schedule

Oracle Retail Merchandising Batch Schedule

Restart/Recovery

The logical unit of work for this program is item list (skulist). The v_restart_item_list view is used for threading. Table-based restart/recovery is used by the batch program.

Design Assumptions

N/A

Rebuild Dynamic Location Lists (lclrbld)

Module Name	lclrbld.pc
Description	Rebuild Dynamic Location Lists
Functional Area	Foundation Data
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS255
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

The logical unit of work for this program is a location list. The restart location list view is used for threading. Table-based restart/recovery is used by the batch program.

Design Assumptions

N/A

Rebuild Dynamic Location Lists (loc_list_rebuild_job)

Module Name	loc_list_rebuild_job
Description	Rebuild Dynamic Location Lists
Functional Area	Foundation Data
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from location list header table which are based on the criteria defined when it was created. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will rebuild the location lists. 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. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 3-7 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_LOC_LIST_REBUILD_STG	Yes	Yes	No	Yes
LOC_LIST_HEAD	Yes	No	Yes	No
LOC_LIST_DETAIL	Yes	Yes	No	Yes

Design Assumptions

N/A

Reclassify Items in Merchandise Hierarchy (reclsdly)

Module Name	reclsdly.pc
Description	Reclassify Items in Merchandise Hierarchy
Functional Area	Foundation
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS302
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 mass change rejections table.

Restart/Recovery

The logical unit of work is the combination of the reclass number and item. Restart ability is also based on reclass number and item.

Design Assumptions

N/A

Refresh Address Materialized View (refmvlocprimaddr)

Module Name	refmvlocprimaddr.pc
Description	Refresh Address Materialized View
Functional Area	Foundation Data
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS305
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Restart/Recovery

N/A

Design Assumptions

N/A

Refresh Currency Conversion Materialized View (batch_rfmvcurrconv)

Module Name	batch_rfmvcurrconv.ksh
Description	Refresh Currency Conversion Materialized View
Functional Area	Foundation Data
Module Type	Admin
Module Technology	ksh
Catalog ID	RMS193
Wrapper Scripts	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This script refreshes the materialized view MV_CURRENCY_CONVERSION_RATES.

Restart/Recovery

N/A

Design Assumptions

N/A

Refresh Localization Materialized View (refmvl10entity)

Module Name	REFMVL10ENTITY.PC
Description	Refresh Materialized view MV_L10N_ENTITY
Functional Area	Administration
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS304
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Restart/Recovery

This batch program uses table-based restart/recovery.

Locking Strategy

N/A

Security Considerations

N/A

Performance Considerations

N/A

I/O Specification

N/A

Rollup of Supplier Data (supmth)

Module Name	supmth.pc
Description	Rollup of Supplier Data
Functional Area	Inventory
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS369
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The primary function of this batch 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. This is done by the batch's post processing function in prepost.

This module accumulates supplier data amounts by department/supplier/transaction type and creates or updates one supplier month row for each department/supplier combination. Based on the transaction type on supplier data, the following transactions are written to supplier 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)

Restart/Recovery

The logical unit of work is dept, supplier.

Design Assumptions

N/A

Store Add Asynchronous Process (CORESVC_STORE_ADD_SQL. ADD_STORE)

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	N/A

Business Overview

This asynchronous process creates new stores in Merchandising, along with all their associated records when a new store is initiated online in Merchandising 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-8 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.

New (status-code: 00NEW). This is the status when store is just created.
Store-Add (status-code: 01STOREADD)
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 Merchandising. The process of adding a store to Merchandising 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 Pricing 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 you are prompted with a message saying the RMS_ASYNC_ID is processed successfully. In case there is a failure during the store creation you will also be notified. You have 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. You are 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'.

Store Add Asynchronous Process (straddbatch.ksh)

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

Business Overview

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

Key Tables Affected

Table 3-9 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.

New (status-code: 00NEW). This is the status when store is just created.
Store-Add (status-code: 01STOREADD)
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'.

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

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
Wrapper Script	wmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, you 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.

Restart/Recovery

N/A

Design Assumptions

N/A

Update ELC Components (batch_compeffupd)

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
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, 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-10 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)

Restart/Recovery

N/A

Design Assumptions

N/A