Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

3 Foundation Data Maintenance

Foundation Data is basic information that is required for Merchandising to function properly. Most foundation data is managed through the Merchandising user interface or integrations 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 to other foundation data and transactions
- Apply Pending Cost Component and ELC Changes to Purchase Orders (batch_ordcostcompupd)
- Apply Pending Item Cost Component Updates (batch_itmcostcompupd)
- – Apply Pending Rate Changes to Expense Profiles (batch_expprofupd)
- – - Apply Pending Up Charge Cost Component Changes to Departments (batch_depchrgupd)
- – - Update Allocation and Transfer Based on Changes to Up Charges (batch_alloctsfupd)
- – Update ELC Components (batch_compeffupd)
- – Apply Expense Profile Updates to Items (batch_expprof_itmcostupd)
Rebuilds of detail information for lists/groups
- Build Diff Ratios Based on Sales History (dfrtbld)
- Rebuild Dynamic Item Lists (itmlrbld)
- Rebuild Dynamic Location Lists (lclrbld)
- Refresh Address Materialized View (refmvlocprimaddr)
- Refresh Currency Conversion Materialized View (batch_rfmvcurrconv)
- – Refresh Localization Materialized View (refmvl10entity)
Application of pending changes
- Like Store Batch Processing (likestorebatch)
- Process Pending Merchandise Hierarchy Changes from External Systems (cremhierdly)
- Reclassify Items in Merchandise Hierarchy (reclsdly)
Rollup of detailed information
- Rollup of Supplier Data (supmth)
Foundation Data Purges
- Purge Aged Cost Component Exceptions (elcexcprg)
- Purge Aged Price History Data (prchstprg)
- Purge Aged Store Ship Schedule (schedprg)
Purge Manage Admin Records (admin_api_purge)

As an alternative to running some of the above processes in the batch cycle, a background process can be used. These include:

Purge Aged Cost Component Exceptions (elc_except_purge_job)
Purge Aged Price History Data (price_hist_purge_job)
Purge Aged Store Ship Schedule (activity_sched_purge_job)
Purge Inactive Currency Rates (currency_rates_purge_job)
Rebuild Dynamic Location Lists (loc_list_rebuild_job)
Store Add Asynchronous Process (CORESVC_STORE_ADD_SQL. ADD_STORE)
Store Add Asynchronous Process (straddbatch.ksh)

Note

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

Apply Expense Profile Updates to Items (batch_expprof_itmcostupd)

Module Name batch_expprof_itmcostupd.ksh Description Apply Expense Profile Updates to Items Functional Area Foundation Data Module Type Business Processing Module Technology ksh Catalog ID Wrapper Script rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

In Merchandising, you are allowed to make rate changes to expense profiles at Country, Supplier, and Partner levels and push these updates to all items related to the profile definition.

This script will process the item expense updates based on the profile push method selected by the user.

Table 3-1 Expense Profile Push Methods

Method Action Replace Item Profile Delete all item shipping routes and expenses then add expense profile routes and expenses.

Table 3-1 (Cont.) Expense Profile Push Methods

Method	Action
Add Missing Profle	Only where NO item expenses exist, add expense profle routes and expenses.
Add Missing Shipping Routes	• Preserve existing shipping routes and expenses as is. • Add missing shipping routes and associated expenses.
Merge Shipping Routes / Replace Expenses	• Retain existing item expense routes which are not on the expense profle as is • For routes on the expense profle:
	– If shipping route exists on item expense table, delete associated expenses, EXCEPT expenses defned as an Always Default expense) that aren’t on the profle’s. route, and add all expenses on profle shipping route that weren’t retained. – If shipping route does not exist on item expense table, add missing route and associated expenses.
Merge Shipping Routes / Add Missing Expenses	• Retain existing item expense routes which are not on the expense profle as is. • For routes on the expense profle. • If shipping route exists on item expense table, delete associated expenses, EXCEPT expenses defned as an Always Default expense) that aren’t on the profle’s route and add all expenses on profle shipping route that weren’t retained. • If shipping route does not exist on item expense table, add missing route and associated expenses.

The cost component updates are going to be effective during the nightly batch run.

Restart/Recovery

The logical unit of work for this batch is the expense profile header values ( EXP_PROF_HEAD ).

Restart is based on the staging table TMP_EXP_PROFILE_ITEMS .

It will process records with PROCESS_STATUS =’N’ .

In case of abort, records that were already processed will have a value of P – processed or E – Error, and processing will start with remaining records with N status.

If no new records exist, the staging table will be rebuilt.

Key Tables Affected

Table	Select	Insert	Update	Delete
EXP_PROF_HEAD	Yes	No	Yes	No

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

Table	Select	Insert	Update	Delete
EXP_PROF_DETAIL	Yes	No	No	No
ITEM_EXP_HEAD	Yes	Yes	Yes	Yes
ITEM_EXP_DETAIL	Yes	Yes	Yes	Yes
ITEM_HTS_ASSESS	Yes	No	Yes	Yes

Design Assumptions

N/A

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

Module Name ordcostcompupdsqls.pls/ordcostcompupdsqlb.pls Description Apply Pending Cost Component and ELC Changes to Purchase Orders Functional Area Foundation Data Module Type Business Processing Module Technology PLSQL Catalog ID RMS190 Wrapper Script rmswrap_plsql.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 upcharges 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-2 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

New stores are staged on the store add table, and those that are ready to be processed will have a process status of ‘O2STOREADD_POST’. For each new store to be processed, this batch program will attempt to copy the desired information for each item associated with the like store. If information for all items is successfully processed for a given store, the store is removed from the store add table. If one or more items were not processed successfully, the process status of the store is set to ‘05LIKESTORE_FAIL’ to indicate the like store process failed. and the unprocessed items are retained. Users can reprocess these stores through the Asynchronous Job Log workflow.

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

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-3 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-4 ELCEXCPRG.PC - Cascade Options

Updated Entity Cascade Options Expense Profiles (Country, Order, Item Supplier, or Partner)

Table 3-4 (Cont.) ELCEXCPRG.PC - Cascade Options

Updated Entity	Cascade Options
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.

1. 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.

2. 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-5 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-6 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-7 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_PUR GE_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 parameterPROC_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 lclrbldsqlb.pls/lclrbldsqls.pls Description Rebuild Dynamic Location Lists Functional Area Foundation Data Module Type Business Processing Module Technology PLSQL

Catalog ID RMS255 Wrapper Script rmswrap_plsql.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-8 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 Description

batch_rfmvcurrconv.ksh Refresh Currency Conversion Materialized View

Functional Area Foundation Data Module Type Admin Module Technology ksh Catalog ID RMS193 Wrapper Script s 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-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

Table 3-9 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
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_DAY S	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 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 sourcedelivery-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-10 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

Table 3-10 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
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_DAY S	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

Queue Creation

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.

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 storeadd 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 Likestore 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 UpCharges (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-11 Options for Cascading Updates

Updated Entity Cascade Options Expense Profiles (Country, Order, Item Supplier, or Partner)

Table 3-11 (Cont.) Options for Cascading Updates

Updated Entity	Cascade Options
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

In this guide

2 Administration Batch — Batch Operations Guide · shares COMPANY_CLOSED_EXCEP, COST_ZONE, COST_ZONE_GROUP_LOC, DIFF_RATIO_DETAIL
G Appendix: Tables List — Merchandising Cloud Services Data Conversion Implementation Guide · shares COUNTRY_ATTRIB, CURRENCY_RATES, DIFF_RATIO_DETAIL, ITEM_HTS_ASSESS
3 Custom Validation Rules — Merchandising Cloud Services Customization and Extension Guide · shares ITEM_EXP_DETAIL, ITEM_EXP_HEAD, ITEM_HTS_ASSESS, RPM_ZONE
16 Stock Count — Batch Operations Guide · shares COMPANY_CLOSED, LOCATION_CLOSED, LOC_LIST_DETAIL
6 Scheduled Integration — Inbound and Outbound Integration Guide · shares COMPANY_CLOSED, LOCATION_CLOSED, LOC_LIST_HEAD, MV_CURRENCY_CONVERSION_RATES

Oracle Retail Knowledge Base

Explorer

3 Foundation Data Maintenance

Note

Apply Expense Profile Updates to Items (batch_expprof_itmcostupd)

Schedule

Design Overview

Restart/Recovery

Key Tables Affected

Design Assumptions

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Apply Pending Item Cost Component Updates (batch_itmcostcompupd)

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Build Diff Ratios Based on Sales History (dfrtbld)

Schedule

Design Overview

Restart/Recovery

I/O Specification

Output File Layout

Design Assumptions

Like Store Batch Processing (likestorebatch)

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Purge Aged Cost Component Exceptions (elc_except_purge_job)

Schedule

Design Overview

Restart/Recovery

Key Tables Affected

Design Assumptions

Purge Aged Cost Component Exceptions (elcexcprg)

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Purge Aged Price History Data (prchstprg)

Schedule

Design Overview

Restart/Recovery

Performance Considerations

Design Assumptions

Purge Aged Price History Data (price_hist_purge_job)

Schedule

Design Overview

Note

Restart/Recovery

Key Tables Affected

Purge Aged Store Ship Schedule (activity_sched_purge_job)

Schedule

Design Overview

Restart/Recovery

Key Tables Affected

Design Assumptions

Purge Aged Store Ship Schedule (schedprg)

Schedule

Design Overview

Restart/Recovery

Design Assumptions

Purge Inactive Currency Rates (currency_rates_purge_job)

Design Overview

Restart/Recovery