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)
-
Apply Expense Profile Updates to Items (batch_expprof_itmcostupd)
-
-
Rebuilds of detail information for lists/groups
-
Application of pending changes
-
Rollup of detailed information
-
Foundation Data Purges
As an alternative to running some of the above processes in the batch cycle, a background process can be used. These include:
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 |
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. |
Add Missing Profile |
Only where NO item expenses exist, add expense profile routes and expenses. |
Add Missing Shipping Routes |
|
Merge Shipping Routes / Replace Expenses |
|
Merge Shipping Routes / Add Missing 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.
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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, 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.
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 |
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.
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 |
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".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 |
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.
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 |
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 |
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.
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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 |
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 |
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)
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 |
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.
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-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 |
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.
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 |
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.
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 |
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, 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)