Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
This chapter contains information about a number of batch processes perform administrative processes in RMS. These processes range from incrementing the ’current business date for transactions' (known in RMS as vdate) to purging unused data and auditing database transactions.
Table 2-1 Program Summary
Program | Description |
---|---|
async_job_status_retry_cleanup.ksh |
Purge Asynchronous Job Tables |
pre/post |
Pre/Post Helper Processes for Batch Programs |
dlyprg.pc |
Daily Purge of Foundation Data |
taxevntprg.pc |
Tax Event Purge |
dtesys.pc |
Increment Virtual Business Date |
trunctbl |
Truncate Table Script |
rms_oi_purge.ksh |
Purge Dashboard Working Tables |
raf_notification_purge.ksh |
Purge RAF Notifications |
batch_archive_purge_hist.ksh |
Archive and Truncate Purge History Tables |
admin_api_purge.ksh |
Purge Manage Admin Records |
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 |
Runtime Parameters | NA |
This is a batch job that will clean up the RMS 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 Adhoc and will accept a parameter of # days of information that will be deleted.
Module Name | Prepost.pc |
Description | Pre/Post Helper Processes for Batch Programs |
Functional Areas | Administration |
Module Type | Business Processing |
Module Technology | ProC |
Catalog ID | Individual Pre/Post Jobs have Catalog IDs |
Runtime Parameters | NA |
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.
Pre/Post contains the following helper functions, which are should be individually scheduled with the related main programs.
Table 2-4 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 |
NA |
NA |
RMS419 |
prepost btchcycl |
NA |
No related main process. Is used to enable DB policies that might have been disabled in order to run batch . |
RMS421 |
prepost poscdnld post |
NA |
poscdnld |
RMS423 |
prepost htsupld pre |
NA |
htsupld |
RMS424 |
prepost onordext pre |
NA |
onordext |
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 |
NA |
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 |
NA |
vendinvc |
RMS451 |
prepost vendinvf pre |
NA |
vendinvf |
RMS452 |
prepost vendinvc post |
NA |
vendinvc |
RMS453 |
prepost vendinvf post |
NA |
vendinvf |
RMS454 |
prepost docclose pre |
RMS219 |
docclose |
RMS455 |
prepost stkprg post |
RMS360 |
stkprg |
RMS456 |
prepost wfordupld pre |
RMS392 |
wfordupld |
RMS457 |
prepost wfretupld pre |
NA |
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 |
NA |
Sets the batch running ind to ’Y' to limit front end use of the system. |
RMS470 |
prepost end_batch post |
NA |
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 schdule. |
RMS488 |
prepost btchcycl post |
NA |
This job reenables all policies in the RMS owning schema. |
RMS489 |
prepost dealfct post |
RMS209 |
dealfct |
Table 2-6 Key Tables Affected
Table | Select | Insert | Update | Index | Delete | Turn cate |
Trigger | Refresh |
---|---|---|---|---|---|---|---|---|
ALL_CONSTRAINTS |
Y |
N |
N |
N |
N |
N |
N |
N |
ALL_IND_PARTITIONS |
Y |
N |
N |
N |
N |
N |
N |
N |
ALL_POLICIES |
Y |
N |
N |
N |
N |
N |
N |
N |
ALLOC_DETAIL |
Y |
N |
N |
N |
N |
N |
Y |
N |
ALLOC_HEADER |
Y |
N |
N |
N |
N |
N |
Y |
N |
CLASS |
Y |
N |
N |
N |
N |
N |
N |
N |
CLASS_SALES_FORECAST |
N |
N |
N |
Y |
N |
Y |
N |
N |
CLASS_SALES_HIST |
N |
N |
N |
N |
Y |
N |
N |
N |
CLASS_SALES_HIST_MTH |
Y |
N |
N |
N |
Y |
N |
N |
N |
COST_COMP_UPD_STG |
N |
N |
N |
N |
Y |
N |
N |
N |
COST_SUSP_SUP_HEAD |
N |
N |
Y |
N |
N |
N |
N |
N |
CUSTOMER_SEGMENT_POS_STG |
N |
N |
N |
N |
N |
Y |
N |
N |
DAILY_DATA |
Y |
N |
N |
N |
N |
N |
N |
N |
DAILY_DATA_BACKPOST |
N |
N |
N |
N |
N |
Y |
N |
N |
DAILY_DATA_TEMP |
Y |
N |
N |
N |
N |
Y |
N |
N |
DBA_INDEXES |
Y |
N |
N |
N |
N |
N |
N |
N |
DEALFCT_TEMP |
N |
Y |
N |
N |
N |
Y |
N |
N |
DEAL_ACTUALS_FORECAST |
Y |
N |
N |
N |
N |
N |
N |
N |
DEAL_ACTUALS_ITEM_LOC |
Y |
Y |
N |
N |
N |
N |
N |
N |
DEAL_BB_NO_REBATE_TEMP |
N |
Y |
N |
N |
N |
Y |
N |
N |
DEAL_BB_REBATE_PO_TEMP |
N |
Y |
N |
N |
N |
Y |
N |
N |
DEAL_BB_RECEIPT_SALES_TEMP |
Y |
N |
N |
N |
N |
Y |
N |
N |
DEAL_HEAD |
Y |
N |
Y |
N |
N |
N |
N |
N |
DEAL_DETAIL |
Y |
N |
N |
N |
N |
N |
N |
N |
DEAL_PERF_TRAN_DATA |
Y |
N |
N |
N |
N |
N |
N |
N |
DEAL_ITEM_LOC_EXPLODE |
Y |
N |
N |
N |
N |
N |
N |
N |
DEAL_TRAN_DATA_TEMP |
N |
Y |
N |
N |
N |
Y |
N |
N |
DEAL_ITEMLOC_ITEM |
N |
N |
Y |
N |
N |
N |
N |
N |
DEAL_ITEMLOC_PARENT_DIFF |
N |
N |
Y |
N |
N |
N |
N |
N |
DEAL_ITEMLOC_DCS |
N |
N |
Y |
N |
N |
N |
N |
N |
DEAL_ITEMLOC_DIV_GRP |
N |
N |
Y |
N |
N |
N |
N |
N |
DEPS |
Y |
N |
N |
N |
N |
N |
N |
N |
DEPT_SALES_FORECAST |
N |
N |
N |
Y |
N |
Y |
N |
N |
DEPT_SALES_HIST |
N |
N |
N |
N |
Y |
N |
N |
N |
DEPT_SALES_HIST_MTH |
Y |
N |
N |
N |
Y |
N |
N |
N |
DOC_CLOSE_QUEUE |
N |
Y |
N |
N |
Y |
N |
N |
N |
DOC_CLOSE_QUEUE |
N |
Y |
N |
N |
Y |
N |
N |
N |
DOC_CLOSE_QUEUE_TEMP |
Y |
Y |
N |
N |
N |
N |
N |
N |
DOC_PURGE_QUEUE |
N |
Y |
N |
N |
N |
Y |
N |
N |
DOMAIN_CLASS |
N |
N |
Y |
N |
N |
N |
N |
N |
DOMAIN_DEPT |
N |
N |
Y |
N |
N |
N |
N |
N |
DOMAIN_SUBCLASS |
N |
N |
Y |
N |
N |
N |
N |
N |
EDI_DAILY_SALES |
N |
N |
N |
N |
Y |
N |
N |
N |
EDI_ORD_TEMP |
N |
N |
N |
Y |
N |
Y |
N |
N |
EDI_SUPS_TEMP |
N |
Y |
N |
N |
N |
N |
N |
N |
FIXED_DEAL |
Y |
N |
Y |
N |
N |
N |
N |
N |
FIXED_DEAL_DATES |
N |
N |
Y |
N |
N |
N |
N |
N |
FORECAST_REBUILD |
N |
N |
N |
Y |
N |
Y |
N |
N |
GROUPS |
Y |
N |
N |
N |
N |
N |
N |
N |
HIST_REBUILD_MASK |
Y |
N |
N |
Y |
N |
Y |
N |
N |
IB_RESULTS |
N |
N |
Y |
N |
N |
N |
N |
N |
INVC_DETAIL |
N |
N |
Y |
N |
N |
N |
N |
N |
INVEC_DETAIL_TEMP |
Y |
N |
N |
N |
N |
Y |
N |
N |
INVC_DETAIL_TEMP2 |
N |
N |
N |
N |
N |
Y |
N |
N |
INVC_HEAD |
N |
N |
Y |
N |
N |
N |
N |
N |
INVC_HEAD_TEMP |
Y |
N |
N |
N |
N |
Y |
N |
N |
ITEM_FORECAST |
N |
N |
N |
Y |
N |
N |
N |
N |
ITEM_LOC |
Y |
N |
N |
N |
N |
N |
N |
N |
ITEM_MASTER |
Y |
N |
N |
N |
N |
N |
N |
N |
MC_REJECTIONS |
N |
N |
N |
Y |
N |
Y |
N |
N |
MOD_ORDER_ITEM_HTS |
N |
N |
N |
N |
Y |
N |
N |
N |
MV_RESTART_STORE_WH |
N |
N |
N |
N |
N |
N |
N |
Y |
MV_LOC_PRIM_ADDR |
N |
N |
N |
N |
N |
N |
N |
Y |
MV_L10N_ENTITY |
N |
N |
N |
N |
N |
N |
N |
Y |
ON_ORDER_TEMP |
N |
N |
N |
Y |
N |
Y |
N |
N |
ORD_MISSED |
N |
N |
N |
Y |
N |
Y |
N |
N |
ORD_TEMP |
N |
N |
N |
Y |
N |
Y |
N |
N |
ORDHEAD |
Y |
N |
N |
N |
N |
N |
N |
N |
ORDLOC |
Y |
N |
N |
N |
N |
N |
N |
N |
ORDSKU |
Y |
N |
N |
N |
N |
N |
N |
N |
OTB |
N |
Y |
Y |
N |
N |
N |
N |
N |
OTB_CASCADE_STG |
Y |
N |
N |
N |
N |
Y |
N |
N |
PERIOD |
Y |
N |
N |
N |
N |
N |
N |
N |
POS_COUPON_HEAD |
N |
N |
Y |
N |
N |
N |
N |
N |
POS_MERCH_CRITERIA |
N |
N |
Y |
N |
N |
N |
N |
N |
POS_PROD_REST_HEAD |
N |
N |
Y |
N |
N |
N |
N |
N |
POS_STORE |
N |
N |
Y |
N |
N |
N |
N |
N |
POS_TENDER_TYPE_HEAD |
N |
N |
Y |
N |
N |
N |
N |
N |
RECLASS_ITEM |
Y |
N |
N |
N |
N |
N |
N |
N |
RECLASS_ITEM_TEMP |
N |
N |
N |
N |
N |
Y |
N |
N |
REPL_ATTR_UPDATE_EXCLUDE |
Y |
Y |
N |
N |
Y |
N |
N |
N |
REPL_ATTR_UPDATE_HEAD |
Y |
Y |
N |
N |
Y |
N |
N |
N |
REPL_ATTR_UPDATE_ITEM |
Y |
Y |
Y |
N |
Y |
N |
N |
N |
REPL_ATTR_UPDATE_LOC |
Y |
Y |
N |
N |
Y |
N |
N |
N |
REPL_ITEM_LOC |
Y |
N |
N |
N |
N |
N |
N |
N |
REPL_ITEM_LOC_UPDATES |
N |
N |
N |
Y |
N |
N |
N |
N |
RESTART_CONTROL |
Y |
N |
N |
N |
N |
N |
N |
N |
RESTART_PROGRAM_HISTORY |
N |
Y |
N |
N |
N |
N |
N |
N |
RMS_BATCH_STATUS |
N |
N |
Y |
N |
N |
N |
N |
N |
RMS_SIZE_PROFILE |
N |
N |
N |
N |
N |
Y |
N |
N |
RPL_ALLOC_IN_TMP |
N |
Y |
N |
N |
N |
Y |
N |
N |
RPL_DISTRO_TMP |
N |
Y |
N |
Y |
N |
Y |
N |
N |
RPL_NET_INVENTORY_TMP |
N |
N |
N |
N |
N |
Y |
N |
N |
RTV_HEAD |
Y |
N |
N |
N |
N |
N |
N |
N |
SALWEEK_C_DAILY |
N |
Y |
N |
N |
N |
Y |
N |
N |
SALWEEK_C_WEEK |
Y |
Y |
N |
N |
N |
Y |
N |
N |
SALWEEK_RESTART_DEPT |
Y |
Y |
Y |
N |
N |
Y |
N |
N |
SHIPMENT |
N |
N |
N |
N |
Y |
N |
N |
N |
SHIPMENT_PUB_INFO |
N |
N |
N |
N |
Y |
N |
N |
N |
SHIPMENT_PURGE_TEMP |
Y |
N |
N |
N |
N |
Y |
N |
N |
STAGE_COMPLEX_DEAL_DETAIL |
N |
N |
N |
N |
N |
Y |
N |
N |
STAGE_COMPLEX_DEAL_HEAD |
N |
N |
N |
N |
N |
Y |
N |
N |
STAGE_FIXED_DEAL_DETAIL |
N |
N |
N |
N |
N |
Y |
N |
N |
STAGE_FIXED_DEAL_HEAD |
N |
N |
N |
N |
N |
Y |
N |
N |
STAKE_HEAD |
Y |
N |
N |
N |
N |
N |
N |
N |
STAKE_PROD_LOC |
Y |
N |
N |
N |
N |
N |
N |
N |
STAKE_PRODUCT |
N |
N |
N |
N |
Y |
N |
N |
N |
STAKE_SKU_LOC |
Y |
N |
N |
N |
N |
N |
N |
N |
STORE |
Y |
N |
N |
N |
N |
N |
N |
N |
SUBCLASS_SALES_FORECAST |
N |
N |
N |
Y |
N |
N |
N |
N |
SUBCLASS_SALES_HIST |
N |
N |
N |
N |
Y |
N |
N |
N |
SUBCLASS_SALES_HIST_MTH |
Y |
N |
N |
N |
Y |
N |
N |
N |
SUPS |
Y |
N |
N |
N |
N |
N |
N |
N |
SUP_DATA |
N |
N |
N |
N |
Y |
N |
N |
N |
SUPS_MIN_FAIL |
N |
N |
N |
Y |
N |
Y |
N |
N |
SVC_WF_ORD_DETAIL |
N |
N |
N |
N |
N |
Y |
N |
N |
SVC_WF_ORD_HEAD |
N |
N |
N |
N |
N |
Y |
N |
N |
SVC_WF_RET_DETAIL |
N |
N |
N |
N |
N |
Y |
N |
N |
SVC_WF_RET_HEAD |
N |
N |
N |
N |
N |
Y |
N |
N |
SVC_WF_RET_TAIL |
N |
N |
N |
N |
N |
Y |
N |
N |
SYSTEM_OPTIONS |
Y |
N |
N |
N |
N |
N |
N |
N |
SYSTEM_VARIABLES |
Y |
N |
Y |
N |
N |
N |
N |
N |
TEMP_TRAN_DATA |
Y |
N |
N |
Y |
N |
Y |
N |
N |
TEMP_TRAN_DATA_SUM |
N |
Y |
N |
Y |
N |
Y |
N |
N |
TIF_EXPLODE |
N |
N |
N |
Y |
N |
Y |
N |
N |
TRAN_DATA |
N |
Y |
N |
N |
N |
N |
N |
N |
TSFHEAD |
Y |
N |
Y |
N |
Y |
N |
N |
N |
TSFHEAD_CFA_EXT |
N |
N |
N |
N |
Y |
N |
N |
N |
VAT_CODE_RATES |
Y |
N |
N |
N |
N |
N |
N |
N |
VAT_ITEM |
Y |
N |
N |
N |
N |
N |
N |
N |
VENDINVC_TEMP |
N |
Y |
N |
N |
N |
Y |
N |
N |
WEEK_DATA |
Y |
N |
N |
N |
N |
N |
N |
N |
WH |
Y |
N |
N |
N |
N |
N |
N |
N |
ALLOC_PURGE_QUEUE |
N |
Y |
N |
N |
N |
N |
N |
N |
COUNTRY_ATTRIB |
Y |
N |
N |
N |
N |
N |
N |
N |
Module Name | dlyprg.pc |
Description | Daily Purge of Foundation Data |
Functional Areas | Administration |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS218 |
Runtime Parameters | NA |
The purpose of this program is to delete specific Foundation Data entities from RMS.
When users ’delete' a record in the RMS 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 can not 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 the DAILY_PURGE_ERROR_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.
Table 2-7 Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Daily |
Scheduling Considerations |
This program runs early in the batch schedule to ensure that deleted entities are not included in any subsequent processing. |
Pre-Processing |
N/ |
Post-Processing |
prepost dlyprg post |
Threading Scheme |
N/A |
This program has inherent restart ability. Records that have been successfully purged are deleted from the DAILY_PURGE table. This ensures that if the program is restarted, it does not attempt to delete records that have been previously processed.
Table 2-8 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
DAILY_PURGE |
Yes |
No |
No |
Yes |
DAILY_PURGE_ERROR_LOG |
Yes |
No |
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 |
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 |
REPL_RESULTS |
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 |
SUB_ITEMS_DETAIL |
Yes |
No |
No |
Yes |
SUB_ITEMS_HEAD |
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_EXCLUDE |
Yes |
No |
No |
Yes |
REPL_ATTR_UPDATE_LOC |
Yes |
No |
No |
Yes |
REPL_ATTR_UPDATE_HEAD |
Yes |
No |
No |
Yes |
MASTER_REPL_ATTR |
Yes |
No |
No |
Yes |
REPL_ATTR_UPDATE_ITEM |
Yes |
No |
No |
Yes |
REPL_DAY |
Yes |
No |
No |
Yes |
REPL_ITEM_LOC |
Yes |
No |
No |
Yes |
REPL_ITEM_LOC_UPDATES |
Yes |
Yes |
No |
No |
COST_SUSP_SUP_DETAIL_LOC |
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 |
FUTURE_COST |
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_LOC |
Yes |
No |
No |
Yes |
ITEM_LOC_SOH |
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 |
ITEM_LOC_TRAITS |
Yes |
No |
No |
Yes |
PACKITEM_BREAKOUT |
Yes |
No |
No |
Yes |
PACKITEM |
Yes |
No |
No |
Yes |
ITEM_SUPP_COUNTRY_BRACKET_COST |
Yes |
No |
No |
Yes |
ITEM_SUP_COUNTRY_LOC |
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 |
Module Name | Taxevntprg |
Description | Tax Event Purge |
Functional Area | Purchase Order |
Module Type | Admin |
Module Technology | PROC |
Catalog ID | RMS373 |
This batch purges the tax events from TAX_CALC_EVENT table. The records to be purged are based on its last_update_datetime along with tax_event_result.
Module Name | dtesys.pc |
Description | Increment Virtual Business Date |
Functional Area | Administration |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS220 |
Runtime Parameters | NA |
This batch program updates the PERIOD table for various dates required in RMS such as vdate, end-of-month and end-of-week dates.
Vdate (short for virtual business date) is used by RMS to maintain a consistent ’virtual' business date (without regard for actual date changes at midnight or different dates in different timezone) for accounting purposes. Note that vdate is used to determine the business date for the financial impact of transactions. Sysdate from the database is used to capture audit time and date stamps on 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).
Module Name | trunctbl.ksh |
Description | Truncate Table Script |
Functional Area | Foundation |
Module Type | Admin |
Module Technology | KSH |
Catalog ID | RMS475 |
Runtime Parameters | NA |
This program performs truncate operation on an RMS 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.
Currently, the following action and tables are processed by the batch. For the runtime parameters, refer to the Merchandising Batch Schedule.
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) |
This batch program calls OI_UTILITY.PURGE_RMS_OI_TABLES to truncate the data in the RMS 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.
Table 2-16 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 |
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 |
Runtime Parameters | $UP (database connect string) |
This batch program calls RAF_NOTIFICATION_TASK_PKG.DEL_NOTIF_PAST_RETENTION to delete notifications that are generated by RMS and RESA 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.
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 | NA |
Runtime Parameters | Database connection,
Archive Frequency in Days (Optional Input Parameter, Default value 180 Days) |
The purpose of this program is to archive and truncate purge history tables regularly in RMS.
When users 'delete' a record in the RMS 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 RMS transaction tables. Before deleting data from RMS transaction 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 Data Pump export utility (expdp) and move the dump file to SFTP 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 RMS archive date, after the successful archiving and truncation of purge history tables.
Table 2-19 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
0 |
Frequency |
Daily |
Scheduling Considerations |
This program should be run after all the purge processes execution in the batch cycle to ensure that all deleted transaction entities are included in the archival and truncation process. |
Pre-Processing |
NA |
Post-Processing |
NA |
Threading Scheme |
NA |
Table 2-20 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
RMS_ARCHIVE_DATES |
Yes |
Yes |
Yes |
No |
ALLOC_CHRG_PRG_HIST |
No |
No |
No |
Yes |
ALLOC_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
ALLOC_HEADER_PRG_HIST |
No |
No |
No |
Yes |
BUYER_WKSHT_MANUAL_PRG_HIST |
No |
No |
No |
Yes |
CONTRACT_COST_HIST_PRG_HIST |
No |
No |
No |
Yes |
CONTRACT_COST_PRG_HIST |
No |
No |
No |
Yes |
CONTRACT_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
CONTRACT_HEADER_PRG_HIST |
No |
No |
No |
Yes |
COST_SUSP_SUP_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
COST_SUSP_SUP_DTL_LOC_PRG_HIST |
No |
No |
No |
Yes |
COST_SUSP_SUP_HEAD_PRG_HIST |
No |
No |
No |
Yes |
CSTSUSP_SUP_HD_CFAEXT_PRG_HIST |
No |
No |
No |
Yes |
DAILY_DATA_PRG_HIST |
No |
No |
No |
Yes |
DEAL_ACTUALS_FORECAST_PRG_HIST |
No |
No |
No |
Yes |
DEAL_ACTUALS_ITEM_LOC_PRG_HIST |
No |
No |
No |
Yes |
DEAL_COMP_PROM_PRG_HIST |
No |
No |
No |
Yes |
DEAL_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
DEAL_HEAD_CFA_EXT_PRG_HIST |
No |
No |
No |
Yes |
DEAL_HEAD_PRG_HIST |
No |
No |
No |
Yes |
DEAL_ITEMLOC_DCS_PRG_HIST |
No |
No |
No |
Yes |
DEAL_ITEMLOC_DIV_GRP_PRG_HIST |
No |
No |
No |
Yes |
DEAL_ITEMLOC_ITEM_PRG_HIST |
No |
No |
No |
Yes |
DEAL_ITMLOC_PRENT_DIF_PRG_HIST |
No |
No |
No |
Yes |
DEAL_PROM_PRG_HIST |
No |
No |
No |
Yes |
DEAL_THRESHOLD_PRG_HIST |
No |
No |
No |
Yes |
DEAL_THRESHOLD_REV_PRG_HIST |
No |
No |
No |
Yes |
FIXED_DEAL_DATES_PRG_HIST |
No |
No |
No |
Yes |
FIXED_DEAL_MERCH_LOC_PRG_HIST |
No |
No |
No |
Yes |
FIXED_DEAL_MERCH_PRG_HIST |
No |
No |
No |
Yes |
FIXED_DEAL_PRG_HIST |
No |
No |
No |
Yes |
HALF_DATA_PRG_HIST |
No |
No |
No |
Yes |
IB_RESULTS_PRG_HIST |
No |
No |
No |
Yes |
INV_ADJ_PRG_HIST |
No |
No |
No |
Yes |
MONTH_DATA_PRG_HIST |
No |
No |
No |
Yes |
MRT_ITEM_LOC_PRG_HIST |
No |
No |
No |
Yes |
MRT_ITEM_PRG_HIST |
No |
No |
No |
Yes |
MRT_L10N_EXT_PRG_HIST |
No |
No |
No |
Yes |
MRT_PRG_HIST |
No |
No |
No |
Yes |
ORDCUST_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
ORDCUST_L10N_EXT_PRG_HIST |
No |
No |
No |
Yes |
ORDCUST_PRG_HIST |
No |
No |
No |
Yes |
ORDHEAD_CFA_EXT_PRG_HIST |
No |
No |
No |
Yes |
ORDHEAD_DISCOUNT_PRG_HIST |
No |
No |
No |
Yes |
ORDHEAD_L10N_EXT_PRG_HIST |
No |
No |
No |
Yes |
ORDHEAD_PRG_HIST |
No |
No |
No |
Yes |
ORDHEAD_REV_PRG_HIST |
No |
No |
No |
Yes |
ORDLOC_CFA_EXT_PRG_HIST |
No |
No |
No |
Yes |
ORDLOC_DISCOUNT_PRG_HIST |
No |
No |
No |
Yes |
ORDLOC_EXP_PRG_HIST |
No |
No |
No |
Yes |
ORDLOC_PRG_HIST |
No |
No |
No |
Yes |
ORDLOC_REV_PRG_HIST |
No |
No |
No |
Yes |
ORDSKU_CFA_EXT_PRG_HIST |
No |
No |
No |
Yes |
ORDSKU_HTS_ASSESS_PRG_HIST |
No |
No |
No |
Yes |
ORDSKU_HTS_PRG_HIST |
No |
No |
No |
Yes |
ORDSKU_PRG_HIST |
No |
No |
No |
Yes |
ORDSKU_REV_PRG_HIST |
No |
No |
No |
Yes |
ORD_TAX_BREAKUP_PRG_HIST |
No |
No |
No |
Yes |
POP_TERMS_DEF_PRG_HIST |
No |
No |
No |
Yes |
POP_TERMS_FULFILLMENT_PRG_HIST |
No |
No |
No |
Yes |
REPL_RESULTS_PRG_HIST |
No |
No |
No |
Yes |
RTV_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
RTV_HEAD_CFA_EXT_PRG_HIST |
No |
No |
No |
Yes |
RTV_HEAD_PRG_HIST |
No |
No |
No |
Yes |
SHIPMENT_PRG_HIST |
No |
No |
No |
Yes |
SHIPSKU_LOC_PRG_HIST |
No |
No |
No |
Yes |
SHIPSKU_PRG_HIST |
No |
No |
No |
Yes |
STAKE_HEAD_PRG_HIST |
No |
No |
No |
Yes |
STAKE_LOCATION_PRG_HIST |
No |
No |
No |
Yes |
STAKE_PRODUCT_PRG_HIST |
No |
No |
No |
Yes |
STAKE_PROD_LOC_PRG_HIST |
No |
No |
No |
Yes |
STAKE_QTY_PRG_HIST |
No |
No |
No |
Yes |
STAKE_SKU_LOC_PRG_HIST |
No |
No |
No |
Yes |
STORE_ORDERS_PRG_HIST |
No |
No |
No |
Yes |
TSFDETAIL_CHRG_PRG_HIST |
No |
No |
No |
Yes |
TSFDETAIL_PRG_HIST |
No |
No |
No |
Yes |
TSFHEAD_CFA_EXT_PRG_HIST |
No |
No |
No |
Yes |
TSFHEAD_L10N_EXT_PRG_HIST |
No |
No |
No |
Yes |
TSFHEAD_PRG_HIST |
No |
No |
No |
Yes |
TSF_ITEM_COST_PRG_HIST |
No |
No |
No |
Yes |
TSF_ITEM_WO_COST_PRG_HIST |
No |
No |
No |
Yes |
TSF_PACKING_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
TSF_PACKING_PRG_HIST |
No |
No |
No |
Yes |
TSF_WO_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
TSF_WO_HEAD_PRG_HIST |
No |
No |
No |
Yes |
TSF_XFORM_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
TSF_XFORM_PRG_HIST |
No |
No |
No |
Yes |
WEEK_DATA_PRG_HIST |
No |
No |
No |
Yes |
WF_BILLING_RETURNS_PRG_HIST |
No |
No |
No |
Yes |
WF_BILLING_SALES_PRG_HIST |
No |
No |
No |
Yes |
WF_ORDER_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
WF_ORDER_EXP_PRG_HIST |
No |
No |
No |
Yes |
WF_ORDER_HEAD_PRG_HIST |
No |
No |
No |
Yes |
WF_RETURN_DETAIL_PRG_HIST |
No |
No |
No |
Yes |
WF_RETURN_HEAD_PRG_HIST |
No |
No |
No |
Yes |
Module Name | admin_api_purge.ksh |
Description | Purge Manage Admin records |
Functional Area | Administration |
Module Type | Admin |
Module Technology | ksh |
Catalog ID | NA |
Runtime Parameters | Database connection |
This script purges data from tables used by the Manage Admin based on the retention days specified in the system parameter- PROC_DATA_RETENTION_DAYS. This batch cleans up the records loaded through Manage Admin for both RMS and ReSA and will help in keeping the size of these tables controlled