2 Administration Batch
This chapter contains information about a number of batch processes perform administrative processes in Merchandising. These processes range from incrementing the current business date for transactions (known in Merchandising as vdate) to purging unused data.
Program Summary
The batch programs covered in this section include the following:
-
Archive and Truncate Purge History Tables (batch_archive_purge_hist.ksh)
-
Load Spreadsheet Templates (ld_iindfiles.ksh and loadods.ksh)
-
Merch API Cache Refresh Wrapper Script (merchapirefreshwrap)
-
Merch API Data Rebuild Request Wrapper Script (merchapidatarebuildrequest)
-
Purge Asynchronous Job Tables (async_job_status_retry_cleanup.ksh)
-
Subscription Metrics Update (subscription_metrics_update_job)
Additionally, this chapter contains details on some background processes that can be run as an alternative to jobs that run during the nightly batch cycle:
Archive and Truncate Purge History Tables (batch_archive_purge_hist.ksh)
Module Name |
batch_archive_purge_hist.ksh |
Description |
Archive and Truncate Purge History Tables |
Functional Area |
Administration |
Module Type |
Admin |
Module Technology |
ksh |
Catalog ID |
RMS477 |
Wrapper Script |
N/A |
Design Overview
The purpose of this program is to archive and truncate purge history tables regularly in Merchandising.
When you 'delete' a record in the Merchandising user interface, information is generally not immediately deleted at the database level; instead, data is marked as being in deleted status and also inserted into the DAILY_PURGE table. Next the purge processes will delete the data from Merchandising transaction tables. Before deleting data from these tables, transaction data will be archived by inserting into purge history tables by the transaction purge processes.
The batch_archive_purge_hist.ksh will export the purge history table data as a dump file using Oracle Rest Data Services (ORDS) data pump export service and move the dump file to Object Storage site for customer pick up. And after successful export of the transaction data, purge history tables are truncated.
This script has the below functions:
-
check_archive_dates - checks for the archive last run date. Based on the last archive date and the archive frequency input parameter, decides whether to archive and truncate the purge history tables or not. This ensures that even though this batch job is scheduled to run daily, the actual archiving and purging of the purge history tables will only occur every X number of days based on the input parameter.
-
truncate_prg_hist_tables - Truncates purge history tables after successful export of the transaction data.
-
update_rms_archive_date - update the Merchandising archive date, after the successful archiving and truncation of purge history tables.
Daily Purge of Foundation Data (daily_purge_job)
Module Name |
daily_purge_job |
Description |
Daily Purge of Foundation Data |
Functional Areas |
Administration |
Module Type |
Admin - Adhoc |
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.
When you 'delete' a record in the RMFCSS user interface, information is generally not immediately deleted at the database level; instead, data is marked as being in deleted status and also inserted into the daily purge table.
A thread assignment program will filter eligible records from daily purge table wherein all entities ready for purging aside/except from Item-Location records. 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 specific Foundation Data entities from the respective RMFCS tables. Complex referential integrity relationships determine whether data can actually be deleted from the database (for example, a store cannot be deleted if any transactions related to the store are still on current transaction tables). This program checks these complex rules. If the deletion request passes the rules, this job will continues to delete the data. If it is not able to delete the data, it writes a record to the daily purge error log table for further investigation. This program will continue to attempt to delete marked data until all references have been purged from the system and the deletion of the foundation data entity finally succeeds. 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 2-1 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_DAILY_PURGE_STG |
Yes |
No |
No |
No |
DAILY_PURGE |
Yes |
No |
No |
Yes |
DAILY_PURGE_ERROR_LOG |
Yes |
Yes |
No |
Yes |
LOC_LIST_DETAIL |
No |
No |
No |
Yes |
MONTH_DATA_BUDGET |
Yes |
No |
No |
Yes |
HALF_DATA_BUDGET |
Yes |
No |
No |
Yes |
VAT_DEPS |
Yes |
No |
No |
Yes |
SKULIST_CRITERIA |
Yes |
No |
No |
Yes |
DOMAIN_DEPT |
Yes |
No |
No |
Yes |
FORECAST_REBUILD |
Yes |
No |
No |
Yes |
SUP_DATA |
Yes |
No |
No |
Yes |
DEPT_SALES_HIST |
Yes |
No |
No |
Yes |
DEPT_SALES_FORECAST |
Yes |
No |
No |
Yes |
DEAL_ITEMLOC |
Yes |
No |
No |
Yes |
DEPS |
Yes |
No |
No |
Yes |
STOCK_LEDGER_INSERTS |
Yes |
No |
No |
Yes |
STAKE_SCHEDULE |
Yes |
No |
No |
Yes |
DEPT_CHRG_DETAIL |
Yes |
No |
No |
Yes |
WH_DEPT |
Yes |
No |
No |
Yes |
DEPT_CHRG_HEAD |
Yes |
No |
No |
Yes |
SUP_BRACKET_COST |
Yes |
No |
No |
Yes |
SUP_REPL_DAY |
Yes |
No |
No |
Yes |
SUP_INV_MGMT |
Yes |
No |
No |
Yes |
FILTER_GROUP_MERCH |
Yes |
No |
No |
Yes |
IB_RESULTS |
Yes |
No |
No |
Yes |
WEEK_DATA |
Yes |
No |
No |
Yes |
DAILY_DATA |
Yes |
No |
No |
Yes |
MONTH_DATA |
Yes |
No |
No |
Yes |
TRAN_DATA_HISTORY |
Yes |
No |
No |
Yes |
HALF_DATA |
Yes |
No |
No |
Yes |
PARTNER |
Yes |
No |
No |
Yes |
SHIPMENT |
Yes |
No |
No |
Yes |
COST_ZONE_GROUP_LOC |
Yes |
No |
No |
Yes |
COST_ZONE |
Yes |
No |
No |
Yes |
COST_ZONE_GROUP |
Yes |
No |
No |
Yes |
UDA_ITEM_DEFAULTS |
Yes |
No |
No |
Yes |
DOMAIN_CLASS |
Yes |
No |
No |
Yes |
CLASS_SALES_HIST |
Yes |
No |
No |
Yes |
CLASS_SALES_FORECAST |
Yes |
No |
No |
Yes |
CLASS |
Yes |
No |
No |
Yes |
DOMAIN_SUBCLASS |
Yes |
No |
No |
Yes |
OTB |
Yes |
No |
No |
Yes |
DIFF_RATIO_DETAIL |
Yes |
No |
No |
Yes |
DIFF_RATIO_HEAD |
Yes |
No |
No |
Yes |
SUBCLASS_SALES_HIST |
Yes |
No |
No |
Yes |
SUBCLASS_SALES_FORECAST |
Yes |
No |
No |
Yes |
SUBCLASS |
Yes |
No |
No |
Yes |
MERCH_HIER_DEFAULT |
Yes |
No |
No |
Yes |
WH |
Yes |
No |
No |
Yes |
WH_ADD |
Yes |
No |
No |
Yes |
STORE_SHIP_DATE |
Yes |
No |
No |
Yes |
LOC_TRAITS_MATRIX |
Yes |
No |
No |
Yes |
COST_ZONE_GROUP_LOC |
Yes |
No |
No |
Yes |
ITEM_EXP_DETAIL |
Yes |
No |
No |
Yes |
ITEM_EXP_HEAD |
Yes |
No |
No |
Yes |
EXP_PROF_DETAIL |
Yes |
No |
No |
Yes |
EXP_PROF_HEAD |
Yes |
No |
No |
Yes |
STORE_GRADE_STORE |
Yes |
No |
No |
Yes |
DAILY_SALES_DISCOUNT |
Yes |
No |
No |
Yes |
LOAD_ERR |
Yes |
No |
No |
Yes |
STORE |
Yes |
No |
No |
Yes |
EDI_SALES_DAILY |
Yes |
No |
No |
Yes |
COMP_STORE_LINK |
Yes |
No |
No |
Yes |
SEC_GROUP_LOC_MATRIX |
Yes |
No |
No |
Yes |
LOC_CLSF_HEAD |
Yes |
No |
No |
Yes |
LOC_CLSF_DETAIL |
Yes |
No |
No |
Yes |
SOURCE_DLVRY_SCHED |
Yes |
No |
No |
Yes |
SOURCE_DLVRY_SCHED_DAYS |
Yes |
No |
No |
Yes |
SOURCE_DLVRY_SCHED_EXC |
Yes |
No |
No |
Yes |
COMPANY_CLOSED_EXCEP |
Yes |
No |
No |
Yes |
LOCATION_CLOSED |
Yes |
No |
No |
Yes |
POS_STORE |
Yes |
No |
No |
Yes |
STORE_HIERARCHY |
Yes |
No |
No |
Yes |
ADDR |
Yes |
No |
No |
Yes |
TIF_EXPLODE |
Yes |
No |
No |
Yes |
WALK_THROUGH_STORE |
Yes |
No |
No |
Yes |
SKULIST_DETAIL |
Yes |
No |
No |
Yes |
INV_STATUS_QTY |
Yes |
No |
No |
Yes |
REPL_ATTR_UPDATE_LOC |
Yes |
No |
No |
Yes |
REPL_ATTR_UPDATE_HEAD |
Yes |
No |
No |
Yes |
REPL_ATTR_UPDATE_ITEM |
Yes |
No |
No |
Yes |
COST_SUSP_SUP_DETAIL |
Yes |
No |
No |
Yes |
ITEM_HTS_ASSESS |
Yes |
No |
No |
Yes |
ITEM_HTS |
Yes |
No |
No |
Yes |
REQ_DOC |
Yes |
No |
No |
Yes |
ITEM_IMPORT_ATTR |
Yes |
No |
No |
Yes |
TIMELINE |
Yes |
No |
No |
Yes |
COND_TARIFF_TREATMENT |
Yes |
No |
No |
Yes |
ITEM_IMAGE |
Yes |
No |
No |
Yes |
ITEM_SUPP_UOM |
Yes |
No |
No |
Yes |
DEAL_SKU_TEMP |
Yes |
No |
No |
Yes |
DEAL_DETAIL |
Yes |
No |
No |
Yes |
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
Yes |
ITEM_SUPP_COUNTRY_DIM |
Yes |
No |
No |
Yes |
RECLASS_ITEM |
Yes |
No |
No |
Yes |
SUP_AVAIL |
Yes |
No |
No |
Yes |
ITEM_SUPPLIER |
Yes |
No |
No |
Yes |
ITEM_MASTER |
Yes |
No |
No |
Yes |
PACK_TMPL_DETAIL |
Yes |
No |
No |
Yes |
SUPS_PACK_TMPL_DESC |
Yes |
No |
No |
Yes |
PACK_TMPL_HEAD |
Yes |
No |
No |
Yes |
UDA_ITEM_LOV |
Yes |
No |
No |
Yes |
UDA_ITEM_DATE |
Yes |
No |
No |
Yes |
UDA_ITEM_FF |
Yes |
No |
No |
Yes |
ITEM_SEASONS |
Yes |
No |
No |
Yes |
ITEM_TICKET |
Yes |
No |
No |
Yes |
COMP_SHOP_LIST |
Yes |
No |
Yes |
Yes |
TICKET_REQUEST |
Yes |
No |
No |
Yes |
PRICE_HIST |
Yes |
Yes |
No |
Yes |
PACKITEM_BREAKOUT |
Yes |
No |
No |
Yes |
PACKITEM |
Yes |
No |
No |
Yes |
POS_MERCH_CRITERIA |
Yes |
No |
No |
Yes |
ITEM_CHRG_HEAD |
Yes |
No |
No |
Yes |
ITEM_CHRG_DETAIL |
Yes |
No |
No |
Yes |
RECLASS_COST_CHG_QUEUE |
Yes |
No |
No |
Yes |
ITEM_PUB_INFO |
Yes |
No |
No |
Yes |
ITEM_MFQUEUE |
Yes |
No |
No |
Yes |
ITEM_XFORM_HEAD |
Yes |
No |
No |
Yes |
ITEM_XFORM_DETAIL |
Yes |
No |
No |
Yes |
DEAL_ITEM_LOC_EXPLODE |
Yes |
No |
No |
Yes |
ITEM_APPROVAL_ERROR |
Yes |
No |
No |
Yes |
Daily Purge of Foundation Data (dlyprg)
Module Name |
dlyprg.pc |
Description |
Daily Purge of Foundation Data |
Functional Areas |
Administration |
Module Type |
Admin |
Module Technology |
ProC |
Catalog ID |
RMS218 |
Wrapper Script |
rmswrap.ksh |
Design Overview
The purpose of this program is to delete specific Foundation Data entities from Merchandising.
When users ‘delete' a record in the Merchandising user interface, information is generally not immediately deleted at the database level; instead, data is marked as being in deleted status and also inserted into the DAILY_PURGE table.
Complex referential integrity relationships determine whether data can actually be deleted from the database (for example, a store cannot be deleted if any transactions related to the store are still on current transaction tables). Dlyprg.pc checks these complex rules. If the deletion request passes the rules, dlyprg.pc deletes the data. If dlyprg.pc is not able to delete the data, it writes a record to a log table for further investigation. Dlyprg will continue to attempt to delete marked data until all references have been purged from the system and the deletion of the foundation data entity finally succeeds.
Increment Virtual Business Date (dtesys)
Module Name |
dtesys.pc |
Description |
Increment Virtual Business Date |
Functional Area |
Administration |
Module Type |
Admin |
Module Technology |
ProC |
Catalog ID |
RMS220 |
Wrapper Script |
rmswrap.ksh |
Design Overview
This batch program updates the PERIOD table for various dates required in Merchandising such as vdate, end-of-month and end-of-week dates.
Vdate (short for virtual business date) is used by Merchandising to maintain a consistent ‘virtual' business date (without regard for actual date changes at midnight or different dates in different timezone) for accounting purposes. Sysdate from the database is used to capture audit time and date stamps on transactions.
Note:
Vdate is used to determine the business date for the financial impact of transactions.
Generally, dtesys is run without additional input parameters and increments the data by one day. However, if a specific date is passed into the program as a parameter, the system date will be updated to that date.
Special processing also occurs:
-
Weekly
When vdate = next_eow_date_unit, the program increments the last_eow_date_unit and next_eow_date_unit columns on system_variables. The last_eow_date_unit is updated to the current next_eow_date_unit and the next_eow_date_unit is updated to the next end-of-week date (calculated).
-
Monthly
When vdate = next_eom_date_unit, the program updates the last_eom_date_unit and next_eom_date_unit columns on system_variables. The last_eom_date_unit is updated to the current next_eom_date_unit and the next_eom_date_unit is updated to the next end-of-month date (calculated).
Load Spreadsheet Templates (ld_iindfiles.ksh and loadods.ksh)
Module Name |
ld_iindfiles.ksh, loadods.ksh |
Description |
Load Spreadsheet Templates |
Functional Area |
Item Maintenance |
Module Type |
Install |
Module Technology |
ksh |
Catalog ID |
RMS199 |
Wrapper Script |
N/A |
Design Overview
These scripts are used to load data from template files to the Merchandising template tables as part of installation. They load templates used by induction processes for Merchandising and Pricing, as well as templates used for spreadsheet load of foundation data. They may also be run ad hoc in situations where the base files need to be reset.
Note:
There is no wrapper script for these programs. They are invoked directly by the installer.
Merch API Cache Refresh Wrapper Script (merchapirefreshwrap)
Module Name |
merchapirefreshwrap.ksh |
Description |
Wrapper shell script to process all the refresh requests present in MERCHAPI_ASYNC_REQUEST table. |
Functional Area |
Foundation and Inventory Tracking |
Module Type |
Business Processing |
Module Technology |
Ksh |
Catalog ID |
TBD |
Runtime Parameters |
N/A |
Design Overview
This wrapper batch program is used to process all the requests present in the MERCHAPI_ASYNC_REQUEST table. This table captures requests at an API level for the following scenarios:
-
API Enablement - When the API is enabled through the UI when an entry is made into the MERCHAPI_ASYNC_REQUEST table. This wrapper will do the initial load of the respective merchapi cache table and set the enable flag in the merchapi_config table.
-
API Disablement - When an enabled API is disabled through the UI, an entry is made into the MERCHAPI_ASYNC_REQUEST table. This wrapper will clear the respective merchapi cache table and disable the API in merchapi_config table.
-
Data Refresh – The data refresh request can be submitted using
merchapidatarebuildrequest.ksh
, which makes an entry in the MERCHAPI_ASYNC_REQUEST table. Depending on the type of data refresh (truncate/load or rebuild) the merchapi cache table is rebuilt.
Each API has a separate cache table and respective PLSQL package for API enable, disable and rebuild, which is maintained in the MERCH_BATCH_PARAM table. This wrapper script scans through the MERCAPI_ASYNC_REQUEST table, picks the request ID, completes the processing, and continues with the subsequent request. Duplicate or Invalid requests are ignored during processing. If the API is multi-thread enabled in the merch_batch_param table, this wrapper will spawn multiple threads to process the data.
Pre/Post Helper Processes for Batch Programs (prepost)
Module Name |
prepost.pc |
Description |
Pre/Post Helper Processes for Batch Programs |
Functional Area |
Administration |
Module Type |
Business Processing |
Module Technology |
ProC |
Catalog ID |
N/A Individual pre/post jobs have Catalog IDs |
Wrapper Script |
rmswrap.ksh |
Design Overview
The pre/post module facilitates multi-threading by allowing general system administration functions (such as table deletions or mass updates) to be completed after all threads of a particular program have been processed.
This program will take three parameters: username/password to log on to Oracle, a program before or after which this script must run and an indicator telling whether the script is a pre or post function. It will act as a shell script for running all pre-program and post-program updates and purges (the logic was removed from the programs themselves to enable multi-threading and restart/recovery).
Pre/Post contains the following helper functions, which are should be individually scheduled with the related main programs.
Table 2-2 Pre/Post Helper Functions
Catalog ID | Prepost Job | Related Main Program Catalog ID | Related Main Program |
---|---|---|---|
RMS400 |
prepost rpl pre |
RMS315 |
rplext |
RMS401 |
prepost salweek post |
RMS346 |
salweek |
RMS402 |
prepost salmth post |
RMS343 |
salmth |
RMS403 |
prepost rplapprv pre |
RMS300 |
rplapprv |
RMS404 |
prepost rplatupd pre |
RMS313 |
rplatupd |
RMS405 |
prepost rplatupd post |
RMS313 |
rplatupd |
RMS406 |
prepost rilmaint pre |
RMS311 |
rilmaint |
RMS407 |
prepost rilmaint post |
RMS311 |
rilmaint |
RMS408 |
prepost supmth post |
RMS369 |
supmth |
RMS409 |
prepost sccext post |
RMS355 |
sccext |
RMS410 |
prepost hstbld pre |
RMS239 |
hstbld |
RMS411 |
prepost hstbld post |
RMS239 |
hstbld |
RMS413 |
prepost edidlprd post |
RMS47 |
edidlprd |
RMS414 |
prepost edidlprd pre |
RMS47 |
edidlprd |
RMS417 |
prepost cntrordb post |
RMS232 |
cntrordb |
RMS418 |
prepost fsadnld post |
N/A |
N/A |
RMS419 |
prepost btchcycl |
N/A |
No related main process. Is used to enable DB policies that might have been disabled in order to run batch. |
RMS421 |
prepost poscdnld post |
N/A |
poscdnld |
RMS423 |
prepost htsupld pre |
N/A |
htsupld |
RMS425 |
prepost reclsdly pre |
RMS302 |
reclsdly |
RMS426 |
prepost reclsdly post |
RMS302 |
reclsdly |
RMS427 |
prepost ibcalc pre |
RMS249 |
ibcalc |
RMS428 |
prepost fcstprg pre |
RMS227 |
fcstprg |
RMS429 |
prepost fcstprg post |
RMS249 |
fcstprg |
RMS430 |
prepost reqext pre |
RMS310 |
reqext |
RMS431 |
prepost reqext post |
RMS310 |
reqext |
RMS432 |
prepost stkupd pre |
N/A |
Stkupd |
RMS433 |
prepost replroq pre |
RMS308 |
Replroq |
RMS434 |
prepost rplext post |
RMS315 |
Rplext |
RMS438 |
prepost saleoh pre |
RMS337 |
Saleoh |
RMS440 |
prepost salweek pre |
RMS346 |
salweek |
RMS441 |
prepost dealinc pre |
RMS211 |
Dealinc |
RMS442 |
prepost dealday pre |
RMS208 |
dealday |
RMS443 |
prepost dealday post |
RMS208 |
dealday |
RMS444 |
prepost dealact_nor pre |
RMS206 |
Dealact |
RMS445 |
prepost dealact_po pre |
RMS206 |
Dealact |
RMS446 |
prepost dealact_sales pre |
RMS206 |
Dealact |
RMS447 |
prepost dealfct pre |
RMS209 |
Dealfct |
RMS448 |
prepost dealcls post |
RMS209 |
Dealcls |
RMS449 |
prepost hstbldmth post |
RMS241 |
hstbldmth |
RMS450 |
prepost vendinvc pre |
N/A |
vendinvc |
RMS451 |
prepost vendinvf pre |
N/A |
vendinvf |
RMS452 |
prepost vendinvc post |
N/A |
vendinvc |
RMS453 |
prepost vendinvf post |
N/A |
vendinvf |
RMS454 |
prepost docclose pre |
RMS219 |
docclose |
RMS455 |
prepost stkprg post |
RMS360 |
stkprg |
RMS456 |
prepost wfordupld pre |
RMS392 |
wfordupld |
RMS457 |
prepost wfretupld pre |
N/A |
wfretupld |
RMS458 |
prepost replsizeprofile pre |
RMS309 |
replsizeprofile |
RMS459 |
prepost supsplit pre |
RMS370 |
supsplit |
RMS461 |
prepost batch_ordcostcompupd pre |
RMS190 |
batch_ordcostcompupd |
RMS462 |
prepost batch_ordcostcompupd post |
RMS190 |
batch_ordcostcompupd |
RMS463 |
prepost batch_costcompupd post |
RMS190 |
batch_ordcostcompupd |
RMS465 |
prepost dlyprg post |
RMS218 |
dlyprg |
RMS466 |
prepost tsfprg pre |
RMS380 |
tsfprg |
RMS467 |
prepost tsfprg post |
RMS380 |
tsfprg |
RMS468 |
prepost fcexec pre |
RMS223 |
fcexec |
RMS469 |
prepost start_batch pre |
N/A |
Sets the batch running ind to ‘Y' to limit front end use of the system. |
RMS470 |
prepost end_batch post |
N/A |
Sets the batch running ind to ‘N' to reenable all front end use of the system. This should be the last job in the batch cycle. |
RMS488 |
prepost btchcycl post |
N/A |
This job reenables all policies in the Merchandising owning schema. |
RMS489 |
prepost dealfct post |
RMS209 |
Dealfct |
prepost sitmain pre |
RMS357 |
sitmain |
|
prepost sitmain post |
RMS357 |
Sitmain |
|
prepost ediupack post |
ediupack |
Purge Aged Competitive Pricing Data (cmpprg.pc)
Module Name |
cmpprg.pc |
Description |
Purge Aged Competitive Pricing Data |
Functional Area |
Competitive Pricing |
Module Type |
Admin |
Module Technology |
ProC |
Catalog ID |
RMS198 |
Wrapper Script |
rmswrap.ksh |
Design Overview
This program deletes from the competitive price history table and the competitive shopping list table based purge criteria based on system parameter settings. The Competitive Pricing Months parameter will determine how many months competitive price history should be maintained before deletion. The Competitive Pricing List Days parameter will determine how long a requested shopping list should remain on the shopping list table if it is not complete by the requested shop date.
Purge Aged Competitive Pricing Data (comp_pricing_purge_job)
Module Name |
comp_pricing_purge_job |
Description |
Purge Aged Competitive Pricing Data |
Functional Area |
Competitive Pricing |
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 competitive price history and competitive shipping list tables based on its purge criteria from system parameter settings. The Competitive Pricing List Days parameter will determine how long a requested shopping list should remain on the shopping list table if it is not complete by the requested shop date. 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 competitive price history and competitive shipping list 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 2-3 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PERIOD |
Yes |
No |
No |
No |
PURGE_CONFIG_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_COMP_PRICING_PURGE_STG |
Yes |
Yes |
No |
Yes |
COMP_PRICE_HIST |
Yes |
No |
No |
Yes |
COMP_SHOP_LIST |
Yes |
No |
No |
Yes |
Purge and Archive Old Files in Batch Server (archivelogs)
Module Name |
archivelogs.ksh |
Description |
Purge and Archive Aged files in RMS Batch server |
Functional Area |
Administration |
Module Type |
Admin – Ad hoc |
Module Technology |
ksh |
Catalog ID |
|
Wrapper Script |
rmswrap_shell.ksh |
Design Overview
This ad hoc job archives 7-day-old files from the following folders:
-
${RETAIL_HOME}/data/processed,
-
${RETAIL_HOME}/log,
-
${RETAIL_HOME}/log/sqlloader
-
${RETAIL_HOME}/error
This job also purges 189-day-old files from the following folders:
-
${RETAIL_HOME}/data/processed/archive
-
${RETAIL_HOME}/log/archive
-
${RETAIL_HOME}/log/sqlloader/archive
-
${RETAIL_HOME}/error/archive
-
${RETAIL_HOME}/data/archive
Purge Asynchronous Job Tables (async_job_status_retry_cleanup.ksh)
Module Name |
async_job_status_retry_cleanup.ksh |
Description |
Purge Asynchronous Job Tables |
Functional Area |
Administration |
Module Type |
Admin |
Module Technology |
ksh |
Catalog ID |
RMS180 |
Wrapper Script |
rmswrap_shell.ksh |
Design Overview
This batch job cleans up the Merchandising asynchronous jobs tables. The asynchronous job management tables (RMS_ASYNC_STATUS and RMS_ASYNC_RETRY) track each asynchronous call that is made. These tables are used to see error information and help with retrying failed calls.
This program will be run ad hoc and will accept a parameter of # days of information that will be deleted.
Purge Dashboard Working Tables (rms_oi_purge.ksh)
Module Name |
rms_oi_purge.ksh |
Description |
Purge data from the dashboard working tables |
Functional Area |
Operational Insight Dashboard Reports |
Module Type |
Admin |
Module Technology |
Ksh |
Catalog ID |
RMS490 |
Runtime Parameters |
$UP (database connect string) |
Design Overview
This batch program calls OI_UTILITY.PURGE_RMS_OI_TABLES
to truncate the data in the Merchandising Operational
Insight Dashboard staging tables. During normal operation, the staged data for the session are deleted when a user closes
the report window. This program provides a way to clean up and control the size of the staging tables if data failed to be
deleted due to abnormal termination of the session.
Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
When no user is on-line using the OI dashboard reports. |
Pre-Processing |
N/A |
Post-Processing |
N/A |
Threading Scheme |
N/A |
Key Tables Affected
Table 2-5 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
RMS_OI_BUYER_EARLY_LATE_SHIP |
No |
No |
No |
Yes |
RMS_OI_BUYER_ORDERS_TO_APPROVE |
No |
No |
No |
Yes |
RMS_OI_INV_ANA_OPEN_ORDER |
No |
No |
No |
Yes |
RMS_OI_INV_ANA_VARIANCE |
No |
No |
No |
Yes |
RMS_OI_INV_CTL_NEG_INV |
No |
No |
No |
Yes |
RMS_OI_INV_ORD_ERRORS |
No |
No |
No |
Yes |
RMS_OI_INV_ORD_ITEM_ERRORS |
No |
No |
No |
Yes |
RMS_OI_MISSING_STOCK_COUNT |
No |
No |
No |
Yes |
RMS_OI_OVERDUE_SHIP_ALLOC |
No |
No |
No |
Yes |
RMS_OI_OVERDUE_SHIP_TSF |
No |
No |
No |
Yes |
RMS_OI_OVERDUE_SHIP_RTV |
No |
No |
No |
Yes |
RMS_OI_STK_ORD_PEND_CLOSE |
No |
No |
No |
Yes |
RMS_OI_STOCK_COUNT_VARIANCE |
No |
No |
No |
Yes |
RMS_OI_TSF_PEND_APPROVE |
No |
No |
No |
Yes |
RMS_OI_UNEXPECTED_INV |
No |
No |
No |
Yes |
RMS_OI_DATA_STWRD_INCOMP_ITEMS |
No |
No |
No |
Yes |
Purge Export Data (data_export_purge_job)
Module Name |
data_export_purge_job |
Description |
Purging of all the extracted records (week old) for Xstore. |
Functional Area |
Foundation1 |
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 one step processing only. It will retain the business logic processing from original KSH script algorithm.
The Business logic program will removed all old/aged records from the following staging tables related to data exported information which are considered week old regardless if data is extracted or not.
Key Tables Affected
Table 2-6 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
MERCHHIER_EXPORT_STG |
No |
No |
No |
Yes |
ORGHIER_EXPORT_STG |
No |
No |
No |
Yes |
STORE_EXPORT_STG |
No |
No |
No |
Yes |
DIFFS_EXPORT_STG |
No |
No |
No |
Yes |
DIFFGRP_EXPORT_STG |
No |
No |
No |
Yes |
ITEM_EXPORT_STG |
No |
No |
No |
Yes |
VAT_EXPORT_STG |
No |
No |
No |
Yes |
RELITEM_EXPORT_STG |
No |
No |
No |
Yes |
DATA_EXPORT_HIST |
No |
No |
No |
Yes |
Purge Export Data (export_stg_purge.ksh)
Module Name |
export_stg_purge.ksh |
Description |
Purging of all the extracted records (week old) for Xstore. |
Functional Area |
Foundation |
Module Type |
Integration |
Module Technology |
Ksh |
Catalog ID |
RMS265 |
Wrapper Script |
rmswrap_shell.ksh |
Design Overview
This batch job will be used to remove records that are a week old from the following staging tables.
-
Merchandise Hierarchy Export Staging
-
Organizational Hierarchy Export Staging
-
Store Export Staging
-
Differentiator Export Staging
-
Differentiator Group Export Staging
-
Item Export Staging
-
VAT Export Staging
-
Related Item Export Staging
-
Data Export History
Batch will purge all the records (Week old records) from its respective staging table whether data get extracted or not.
Purge Forecast Data (fcstprg)
Module Name |
fcstprg.pc |
Description |
Purge Forecast Data |
Functional Area |
Interface - Planning |
Module Type |
Admin |
Module Technology |
ProC |
Catalog ID |
RMS227 |
Wrapper Script |
rmswrap.ksh |
Purge Forecast Data (forecast_data_purge_job)
Module Name |
forecast_data_purge_job |
Description |
Purge Forecast Data |
Functional Area |
Interface - Planning |
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 RMS forecast information tables based on passed Domain ID. By default, all domains are captured and considered for purging criteria. 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 their respective RMFCS forecast information tables. Data deletion is performed by partition truncation, table truncation or deletion by domain. The method of deletion is dependent on whether or not the table is partitioned. This program serves to delete data by domains so that they can re-loaded with new forecast information from a forecasting system such as Demand Forecasting.. 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 | Select | Insert | Update | Delete |
---|---|---|---|---|
RMS_BATCH_STATUS |
Yes |
No |
No |
No |
B8D_PROCESS_CONFIG |
Yes |
No |
No |
No |
JOB_AUDIT_LOGS |
Yes |
No |
No |
No |
B8D_FORECAST_DATA_PURGE_STG |
Yes |
Yes |
No |
Yes |
ITEM_FORECAST |
No |
No |
No |
Yes |
DEPT_SALES_FORECAST |
No |
No |
No |
Yes |
CLASS_SALES_FORECAST |
No |
No |
No |
Yes |
SUBCLASS_SALES_FORECAST |
No |
No |
No |
Yes |
Purge Job Auditing Logs (job_audit_logs_purge_job)
Module Name |
job_audit_logs_purge_job |
Description |
Purge Old Job Auditing Logs |
Functional Area |
Administration |
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 one step processing only. This new program/job will use the newly created support program maintain for purging records where affected table is partitioned.
The Business logic program will invoke a call to a new program specific for handling historical logging table that is considered a partitioned table. A package function is called passing the target table name and will execute the proper deletion/purging of records from target table by exercising table partitioning handling such as Dropping Interval Partition (same as truncate or delete from table). There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop job with a flip of this indicator.
The purge program considered the system parameter setting, Job Logging History Months (job_log_hist_months) to determine those records that are older than a predetermined number of months.
Purge Manage Admin Records (admin_api_purge.ksh)
Module Name |
admin_api_purge.ksh |
Description |
Purge Manage Admin records |
Functional Area |
Administration |
Module Type |
Admin |
Module Technology |
ksh |
Catalog ID |
N/A |
Wrapper Script |
rmswrap_shell.ksh |
Purge Notifications (raf_notification_purge.ksh)
Module Name |
raf_notification_purge.ksh |
Description |
Purge notifications from the Retail Application Framework table |
Functional Area |
Notifications |
Module Type |
Admin |
Module Technology |
Ksh |
Catalog ID |
RMS80 |
Wrapper Script |
rmswrap_shell.ksh |
Design Overview
This batch program calls RAF_NOTIFICATION_TASK_PKG.DEL_NOTIF_PAST_RETENTION to delete notifications that are generated by Merchandising and Sales Audit and have passed the preconfigured number of retention days. This program provides a way to clean up and control the size of the RAF notification tables.
Refresh Materialized Views (refreshmview.ksh)
Module Name |
refreshmview.ksh |
Description |
Refreshes dept_sales_forecast, class_sales_forecast, subclass_sales_forecast, dept_sales_hist, class_sales_hist subclass_sales_hist, mv_subclass_loc_hist and mv_restart_stock_count materialized views |
Functional Area |
Financials |
Module Type |
Ad hoc |
Module Technology |
ksh |
Catalog ID |
N/A |
Wrapper Script |
rmswrap_shell.ksh |
Design Overview
This is a batch job that will refresh the specified materialized view. The materialized views that are refreshed are dept_sales_forecast
, class_sales_forecast
, subclass_sales_forecast
, dept_sales_hist
, class_sales_hist
, subclass_sales_hist
, mv_subclass_loc_hist
and mv_restart_stock_count
.
This program can be run ad hoc and will accept the materialized view name as the parameter. Nested refresh of the materialized
view can be controlled using the optional parameter (Valid values: Y
- True and N
- False).
By default, the refresh is nested.
Retail Business Metrics Calculation (rbm_metrics_calc_job)
Module Name |
rbm_metrics_calc_job |
Description |
Retail Business Metrics Calculation job |
Functional Area |
Retail Business Metrics |
Module Type |
Admin – Ad hoc |
Module Technology |
Background Processing |
Catalog ID |
N/A |
Wrapper Script |
b8dwrap.ksh |
Design Overview
This job is a background job that computes the business metric for all metrics marked as enabled (active_ind =
‘Y’
) in the rbm_master
table. The purpose of each metric is detailed in the metric_desc
and comments columns in rbm_master
table.
RBM_ID | METRIC_NAME | METRIC_DESC | COMMENTS |
---|---|---|---|
100 |
active_item_locs |
Count of transaction-level item locations where the status is active, and the item status is approved |
Count of item location records where the item/location status is |
101 |
gross_active_item_locs |
Count of all item locations where the status is active |
Count of item location
records where the item/location status is |
110 |
sales_audit_trans |
Count of sales audit transaction header records |
Count of sales audit transaction header records, across all stores. |
111 |
sales_audit_tran_line_items |
Count of sales audit transaction item records |
Count of sales audit transaction item records, across all stores. |
120 |
po_receipts |
Count of the purchase orders |
Count of the shipments by receipt date where the order number is not null, across all locations. |
121 |
po_lines_recieved |
Count of purchase order lines received |
Count of transaction data records, for
transaction code |
130 |
stock_order_receipts |
Count of stock order (transfer or allocation) receipts |
Count of the shipments by receipt date where the BOL number is not null, across all locations. |
131 |
stock_order_lines_recieved |
Count of stock order lines received (transfer or allocation) |
Count
of shipment transaction data records for transaction code |
140 |
cost_changes |
Total number of cost changes executed |
Sum by effective date the cost change header
records with a status of Extracted ( |
141 |
cost_change_details |
Total number of cost changes executed at detail level |
Sum by effective date
the cost change detail records with a status of Extracted ( |
142 |
cost_change_details_loc |
Total number of cost changes executed at detail location level |
Sum by
effective date the cost change detail location records with a status of Extracted ( |
500 |
price_changes |
Total number of price changes that have been executed |
Sum of count of the price changes
by effective date where the status is Executed ( |
510 |
Clearances |
Total number of clearance events (both markdowns and resets) executed |
Sum of count of
the Executed events (clearances) by effective date where the status is Executed ( |
600 |
Allocations |
Total number of allocations sent to Merchandising for execution |
Sum of count of allocations
( |
601 |
allocation_line_items |
Total number of allocation lines sent to Merchandising for execution |
Sum of count
of allocation details ( |
700 |
Invoices |
Total number of merchandise invoices posted to AP |
Sum of count of documents of type Merchandise
Invoice ( |
701 |
invoices_line_items |
Total number of invoice lines posted to AP |
Sum of count of detail lines for merchandise
invoices in |
710 |
other_documents |
Total number of documents posted to AP, excluding merchandise invoices |
Sum of count
of documents that are not type Merchandise Invoice ( |
The data will be computed at the frequency indicated by calc_freq
in the rbm_master
table
wherein D
stands for daily, W
stands for weekly, and M
stands for monthly.
The weekly metrics are calculated on the day indicated in the calc_weekday
column. The monthly metrics are
calculated on the day of the month indicated by the calc_day
column.
The aggregate_method
column indicates whether the calculated metric will be averaged or summed across
the data points collected. Do not change the aggregate_method
because data collected up until the date of
change will have a different meaning after the change. rbm_id
is a pre-populated ID with dependencies in
the rbm_values
table and therefore should not be changed.
The metrics can be enabled or disabled using the Retail Business Metric Data Induction Template.
The computed data is stored in the rbm_values
table. This data can then be used for reporting. For example,
the aggregated active item locations data is consumed by Subscription Metrics Update (subscription_metrics_update_job
), which in turn writes subscription metrics to the platform table to be used in reports in Retail Home.
Retain Item Forecast History (rms_oi_forecast_history.ksh)
Module Name |
rms_oi_forecast_history.ksh |
Description |
Retain 4 weeks of Item Forecast History |
Functional Area |
Item Forecast, Inventory Analyst Report |
Module Type |
Admin |
Module Technology |
Ksh |
Catalog ID |
RMS491 |
Wrapper Script |
rmswrap_shell.ksh |
Design Overview
This batch program preserves 4 weeks of weekly forecasted sales data from ITEM_FORECAST
in the ITEM_FORECAST_HISTORY
table before ITEM_FORECAST
is truncated and refreshed by the load_item_forecast.ksh
weekly batch program. The data in ITEM_FORECAST_HISTORY
is used to support the Inventory Variance to Forecast
report in the Inventory Analyst dashboard. If the system is not configured to use this report (for example, rms_oi_system_options.ia_variance_to_forecast_ind
is N
), then running this batch job will NOT copy any data to ITEM_FORECAST_HISTORY
.
To support potentially large volume of data on ITEM_FORECAST
and ITEM_FORECAST_HISTORY
, ITEM_FORECAST_HISTORY
is interval partitioned by EOW_DATE
with a partition interval of
7 days and an interval high value of EOW_DATE
+1. EOW_DATE
must be a valid EOW_DATE
based on calendar type – (4
) 454 or (C
) Standard Calendar.
Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Weekly |
Scheduling Considerations |
Before |
Pre-Processing |
N/A |
Post-Processing |
N/A |
Threading Scheme |
N/A |
Subscription Metrics Update (subscription_metrics_update_job)
Module Name |
subscription_metrics_update_job |
Description |
Subscription Metrics Update |
Functional Area |
Subscription Metrics |
Module Type |
Admin – Ad hoc |
Module Technology |
Background Processing |
Catalog ID |
N/A |
Wrapper Script |
b8dwrap.ksh |
Design Overview
This job is a background job that executes after rbm_metrics_calc_job
. First, the rbm_metric_calc_job
aggregates the number of active item locations for the current month. After that, subscription_metrics_update_job
reads the aggregated value, converts to Metrics UOM, and writes to the Platform tables. Retail Home displays the dashboard
graph using the values populated in the Platform tables.
Tax Event Purge (tax_event_purge_job)
Module Name |
tax_event_purge_job |
Description |
Tax Event Purge |
Functional Area |
Purchase Order |
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 tax calculated event table based on its purge criteria (retention number of days) with default value of 90 days and its tax event result defined as "C"ompleted Successfully. 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 tax calculated event 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.
Tax Event Purge (taxevntprg)
Module Name |
Taxevntprg |
Description |
Tax Event Purge |
Functional Area |
Purchase Order |
Module Type |
Admin |
Module Technology |
PROC |
Catalog ID |
RMS373 |
Wrapper Script |
N/A |
Truncate Table Script (trunctbl.ksh)
Module Name |
trunctbl.ksh |
Description |
Truncate Table Script |
Functional Area |
Foundation |
Module Type |
Admin |
Module Technology |
KSH |
Catalog ID |
RMS475 |
Wrapper Script |
rmswrap_shell.ksh |
Design Overview
This program performs truncate operations on a Merchandising table or a specific partition. It accepts an input table name and an optional partition name. If no partition name is passed, then the truncate is applied on the entire table.
This program must be run as either the Merchandising schema owner, or be run by a user that has been granted the following system privileges:
-
drop any table
-
alter any table
Currently, the following action and tables are processed by the batch. For the runtime parameters, refer to the Oracle Retail Merchandising Batch Schedule.
Table 2-9 Actions and Tables Processed by Batch
Table | Partition |
---|---|
NIL_INPUT_WORKING |
N/A |