2 Administration Batch

Mirror of Oracle documentation

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

2 Administration Batch

This chapter contains information about batch processes that perform administrative operations in Merchandising. These processes range from incrementing the current business date for transactions (known in Merchandising as vdate ) to purging unused data and auditing database transactions.

It also includes background processes that can be run as alternatives to selected nightly batch jobs.

Program Summary

The batch programs covered in this section include the following:

• Archive and Truncate Purge History Tables (batch_archive_purge_hist_adhoc_job)

• Automated Data Export and Publish Utility (mfcs_data_export_publish_adhoc_job)

• Daily Purge of Foundation Data (daily_purge_job)

• Daily Purge of Foundation Data (dlyprg)

• Increment Virtual Business Date (dtesys)

• Load Spreadsheet Templates (ld_iindfiles.ksh and loadods.ksh)

• Merch API Cache Refresh Wrapper Script (merchapirefreshwrap)

• Merch API Data Rebuild Request Wrapper Script (merchapidatarebuildrequest)

• Merch API Delta Processing Wrapper Script (merchapiwrap)

• Pre/Post Helper Processes for Batch Programs (prepost)

• Purge Accounts Payable Publish Data (fif_ap_publish_purge_job)

• Purge Aged Competitive Pricing Data (cmpprg.pc)

• Purge Aged Competitive Pricing Data (comp_pricing_purge_job)

• Purge and Archive Old Files in Batch Server (archivelogs)

• Purge Asynchronous Job Tables (async_job_status_retry_cleanup.ksh)

• Purge Dashboard Working Tables (rms_oi_purge.ksh)

• Purge Export Data (data_export_purge_job)

• Purge Export Data (export_stg_purge.ksh)

• Purge Inactive Merch API Cache Data (merchapi_cache_purge_job)

• Purge Exported Sales and Return Transactions (saexpdw_export_purge_job)

• Purge Job Auditing Logs (job_audit_logs_purge_job)

• Purge Manage Admin Records (admin_api_purge.ksh)

• Purge Notifications (raf_notification_purge.ksh)

• Purge Obsoleted Tax Rules Records (gts_purge_job)

• Purge Published Financial Data (fif_gl_publish_purge_job)

• Purge SIOCS-RMS History Tables (sim_rms_history_purge_job)

• Purge SIOCS-RMS Inbound and Staging Tables (sim_rms_purge_job)

• Purge Staged Financial General Ledger Data (stg_fif_gl_purge_job)

• Refresh Materialized Views (refreshmview.ksh)

• Retail Business Metrics Calculation (rbm_metrics_calc_job)

• Retain Item Forecast History (rms_oi_forecast_history.ksh)

• Subscription Metrics Update (subscription_metrics_update_job)

• Tax Event Purge (tax_event_purge_job)

• Tax Event Purge (taxevntprg)

• Truncate Table Script (trunctbl.ksh)

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:

• Daily Purge of Foundation Data (daily_purge_job)

• Purge Aged Competitive Pricing Data (comp_pricing_purge_job)

• Purge Export Data (data_export_purge_job)

• Purge Job Auditing Logs (job_audit_logs_purge_job)

• Tax Event Purge (tax_event_purge_job)

Archive and Truncate Purge History Tables (batch_archive_purge_hist_adhoc_job)

Module Name batch_archive_purge_hist_adhoc_job Description Archive and Truncate Purge History Tables Functional Area Administration Module Type Admin Module Technology ksh Catalog ID RMS477 Wrapper Script batch_archive_purge_hist.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this standalone batch program is to maintain a configurable length of history in the purge history tables. Historical data older than this retention period are exported and deleted from the purge history tables.

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 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 date of insert is stored in the HIST_WRITTEN_DATE column. This date is used by this batch to determine whether the row should be archived and purged.

Historical retention of a purge history table depends on whether it contains rolled-up data or not. This classification is defined in the ARCHIVE_PURGE_CONTROL table. Note that the purge history tables are partitioned by month. As such, the retention criterion is at a month-level of granularity. The retention setting per type of data contained is defined in two columns in the PURGE_CONFIG_OPTIONS table. Maximum retention for both types is 18 months.

• PURGE_HIST_ROLLUP_RETENTION_MONTHS - This field contains the number of months retained in the purge history tables and is specific to tables containing rolled-up data (for example, WEEK_DATA_PRG_HIST , MONTH_DATA_PRG_HIST ).

• PURGE_HIST_RETENTION_MONTHS - This field contains the number of months retained in the purge history tables for non-rolled-up data.

Data that falls beyond the retention history (that is, older) is exported into dump files using the DBMS_DATAPUMP package. One dump file is exported per table. The filename format for the dump files is: EXPDAT_<table name>_YYMMDDHH24MISS.dmp . The dumps are then moved to Object Storage for the customer to retrieve using the DBMS_CLOUD package. The same dumps files are copied to the archive folder in persistent volume.

There is a delay before the batch deletes the data that was exported. This delay in days is defined by PURGE_HIST_DELAY_DAYS in the PURGE_CONFIG_OPTIONS table. The maximum delay is 7 days. Setting this as 0 will purge a partition after it is exported during the same run (that is, no delay). Because the purge history tables are partitioned by month deletion is accomplished by dropping a partition.

The ARCHIVE_PURGE_TRACKER table tracks the progress of the batch against the table partitions that have been identified for archiving and purging. The successfully completion of each step is identified by the value in the STATUS column.

• Identify table partitions for archiving and purging. STATUS = 'N' (new).

• Export table partitions to dump files. STATUS = 'E' (exported).

• Move dump files into object storage “outgoing” prefix. STATUS = 'O' (outgoing).

• Copy dump files to archive folder. STATUS = 'A' (archived).

• Drop the table partitions. STATUS = 'P' (purged).

Restart/Recovery

This batch can be restarted after an abort if the cause of the failure is expected not to recur. It will restart the sequence of operations from the beginning. Processing for a particular table partition existing on the table will restart from the last successful step that was completed. For example, a partition in E (exported) status when the batch failed will proceed to the process that moves the exported dump files to object storage ( STATUS = 'O' ) on restart.

I/O Specifications

N/A

Design Assumptions

N/A

Automated Data Export and Publish Utility (mfcs_data_export_publish_adhoc_job)

Module Name mfcs_data_export_publish_adhoc_job Description Automated Data Export and Publish Utility Functional Area Administration Module Type Admin Module Technology ksh Catalog ID Wrapper Script batch_data_export_publish.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This standalone, ad hoc batch program extracts MFCS table data and publishes the resulting files to OCI Object Storage under the outgoing prefix. It is intended primarily for termination-ofservice data exports and should not be used as the primary backup solution. The batch program uses Oracle Data Pump to generate a dump file set. It extracts all tables from the MFCS RMS01 schema, excluding global temporary tables.

The dump file set is split across multiple files using the following naming convention:

EXPDAT_${exportSchemaName}_${exportFileTime}_DATA_EXPORT_PUBLISH%U.dmp

In this format, %U is an incrementing file sequence number starting at 01 and exportFileTime is the timestamp generated at runtime in the format YYMMDDHHMMSS. For example, 260622143508 represents June 22, 2026, at 14:35:08.

The Oracle Data Pump log file is also uploaded to OCI Object Storage. It uses the same base filename format as the dump files, with a .log extension.

Restart/Recovery

This program does not contain restart/recovery logic.

I/O Specifications

Dump file set:

EXPDAT_${exportSchemaName}_${exportFileTime}_DATA_EXPORT_PUBLISH%U.dmp

Oracle Data Pump log:

EXPDAT_${exportSchemaName}_${exportFileTime}_DATA_EXPORT_PUBLISH.log

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

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.

Restart Recovery

N/A

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

Table 2-1 (Cont.) Key Tables Affected

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

Table 2-1 (Cont.) Key Tables Affected

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

Table 2-1 (Cont.) Key Tables Affected

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

I/O Specification

N/A

Daily Purge of Foundation Data (dlyprg)

Module Name b8ddlyprgb.pls/b8ddlyprgs.pls b8dilprgb.pls/b8dilprgs.pls Description Daily Purge of Foundation Data Functional Areas Administration Module Type Admin Module Technology PL/SQL Catalog ID RMS218 Wrapper Script rmswrap_plsql.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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). The program checks these complex rules. If the deletion request passes the rules, dlyprg.pc deletes the data. If the program is not able to delete the data, it writes a record to a log table for further investigation. The 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.

Restart Recovery

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.

I/O Specification

N/A

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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-ofweek 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 endof-month date (calculated).

Restart/Recovery

N/A

I/O Specification

N/A

Design Assumptions

N/A

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

Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

Merch API Data Rebuild Request Wrapper Script (merchapidatarebuildrequest)

Module Name	merchapidatarebuildrequest.ksh
Description	Wrapper shell script to submit data refresh requests.
Functional Area	Foundation and Inventory tracking.
Module Type	Business Processing
Module Technology	Ksh
Catalog ID	TBD
Runtime Parameters	N/A

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This wrapper batch program is used to make an entry in the MERCHAPI_ASYNC_REQUEST table for data refresh requests. There are two data refresh types: one is TRUNCATE_AND_LOAD and the other one is REBUILD.

• TRUNCATE_AND_LOAD will be used for severe data corruption.

• REBUILD is used to build the JSON message for all the records.

The LAST_UPDATE_DATETIME update is dependent on the value of REFRESH_UPDATE_TIMESTAMP_IND. The silent update (REBUILD with no timestamp update) is applicable when a new field is added or removing the deprecated fields and don’t want to force-publish these changes. REBUILD with timestamp update is used in case of data discrepancies within Merchandising and Cache table or Merchandising and consuming system.

Restart/Recovery

N/A

Design Assumptions

N/A

Merch API Delta Processing Wrapper Script (merchapiwrap)

Module Name merchapiwrap.ksh Description Wrapper shell script to pre-process the Delta records for Merch integration API publishing. Functional Area Foundation and Inventory Tracking. Module Type Business Processing Module Technology Ksh Catalog ID TBD Runtime Parameters N/A

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This wrapper batch program is used to populate/merge the delta changes that were made to the base Merchandising tables into respective MERCHAPI cache tables based on the ICL entries. Each API has a separate cache table and respective API package to refresh the table. Based on the Job name, it retrieves all the Job parameters from the MERCH_BATCH_PARAM table and calls the Delta PLSQL dynamically to process all the changes that were logged in the respective ICL table. All the ICL records which are in “N” status and consumer configured for that particular API are picked up for processing. First, it updates all the “N” records to “I” (InProgress) and deletes all the “I” records once these are processed successfully.

Restart/Recovery

N/A

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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

Table 2-2 (Cont.) Pre/Post Helper Functions

Catalog ID	Prepost Job	Related Main Program Catalog ID	Related Main Program
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
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

Table 2-2 (Cont.) Pre/Post Helper Functions

Catalog ID	Prepost Job	Related Main Program Catalog ID	Related Main Program
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
	prepost dealact_reverse pre

Restart/Recovery

N/A

Purge Accounts Payable Publish Data (fif_ap_publish_purge_job)

Module Name	ff_ap_publish_purge_job
Description	Purge Accounts Payable Publish Data
Functional Area	General Ledger
Module Type	Admin – Ad hoc

Module Technology	Background Processing
Catalog ID
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of one step processing only.

The Business logic program will delete records from FIF_AP_HEAD_PUBLISH and FIF_AP_DETAIL_PUBLISH tables (week partitioned) where extract date is past the GL_PUBLISH_PURGE_DAYS plus an additional 7 days. It will then invoke the function PARTITION_SQL.PURGE_INTERVAL_PARTITION for each target table FIF_AP_HEAD_PUBLISH and FIF_AP_DETAIL_PUBLISH along with the retention days. This function executes 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). It will also archive the old records to the FIF_AP_HEAD_PUBLISH_PRG_HIST and FIF_AP_DETAIL_PUBLISH_PRG_HIST tables.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
FIF_AP_HEAD_PUBLISH	No	No	No	Yes
FIF_AP_DETAIL_PUBLISH	No	No	No	Yes
FIF_AP_HEAD_PUBLISH_PRG_HIST	No	Yes	No	No
FIF_AP_DETAIL_PUBLISH_PRG_HIST	No	Yes	No	No
PURGE_CONFIG_OPTIONS	Yes	No	No	No
ALL_TAB_PARTITIONS	Yes	No	No	No

I/O Specification

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Thread assignment program will filter eligible records from 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.

Restart/Recovery

N/A

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

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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

Restart/Recovery

N/A

Key Tables Affected

N/A

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Key Tables Affected

Table 2-4 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_ASYNC_STATUS	No	No	No	Yes
RMS_ASYNC_RETRY	No	No	No	Yes

Input/Out Specification

N/A

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

Restart/Recovery

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

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

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

Integration Contract

N/A

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Inactive Merch API Cache Data (merchapi_cache_purge_job)

Module Name merchapi_cache_purge_job Description Purge inactive Merch API Cache Data Functional Area Foundation and Inventory Tracking Module Type Admin – Ad hoc Module Technology Background Processing Catalog ID Wrapper Script b8dwrap.ksh

Design Overview

This background job purges the inactive records (that have passed retention days based on system option merchapi_cache_purge_days ) from all the Merchandising integration cache tables.

Restart/Recovery

N/A

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
MERCH_BATCH_PARAMS	Yes	No	No	No
MERCHAPI_DIFF_GROUP	Yes	No	No	Yes
MERCHAPI_DIFF_IDS	Yes	No	No	Yes
MERCHAPI_EXT_ALLOC	Yes	No	No	Yes
MERCHAPI_EXT_DIFF_GROUP	Yes	No	No	Yes
MERCHAPI_EXT_DIFF_IDS	Yes	No	No	Yes
MERCHAPI_EXT_DIFF_TYPE	Yes	No	No	Yes
MERCHAPI_EXT_ITEM	Yes	No	No	Yes
MERCHAPI_EXT_ITEM_VAT	Yes	No	No	Yes
MERCHAPI_EXT_MERCHHIER	Yes	No	No	Yes
MERCHAPI_EXT_ORDER	Yes	No	No	Yes
MERCHAPI_EXT_ORGHIER	Yes	No	No	Yes
MERCHAPI_EXT_PARTNER	Yes	No	No	Yes
MERCHAPI_EXT_RCV_UNIT_ADJ	Yes	No	No	Yes

Table	Select	Insert	Update	Delete
MERCHAPI_EXT_STORE	Yes	No	No	Yes
MERCHAPI_EXT_SUPPLIER	Yes	No	No	Yes
MERCHAPI_EXT_TRANSFER	Yes	No	No	Yes
MERCHAPI_EXT_UDA	Yes	No	No	Yes
MERCHAPI_EXT_WH	Yes	No	No	Yes
MERCHAPI_EXT_WOIN	Yes	No	No	Yes
MERCHAPI_EXT_WOOUT	Yes	No	No	Yes
MERCHAPI_FUTURE_AVAIL_CO_INV	Yes	No	No	Yes
MERCHAPI_ITEM	Yes	No	No	Yes
MERCHAPI_ITEM_IMAGE	Yes	No	No	Yes
MERCHAPI_ITEM_LOC	Yes	No	No	Yes
MERCHAPI_ITEM_LOC_SOH	Yes	No	No	Yes
MERCHAPI_MERCH_HIER	Yes	No	No	Yes
MERCHAPI_ORG_HIER	Yes	No	No	Yes
MERCHAPI_RELATED_ITEM	Yes	No	No	Yes
MERCHAPI_RTV	Yes	No	No	Yes
MERCHAPI_VAT	Yes	No	No	Yes

Design Assumptions

N/A

Purge Exported Sales and Return Transactions (saexpdw_export_purge_job)

Module Name saexpdw_export_purge_job Description Purge the exported sales and return transactions from SA_EXPDW_RDW_* tables Functional Area Financial data Module Type Admin – Ad hoc Module Technology Background Processing Catalog ID Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job purges the exported sales and return transactions from SA_EXPDW_RDW* tables. Records older than SYSDATE minus expdw_export_purge_days from SA_SYSTEM_OPTIONS

are purged. This job will first archive the data to SA_EXPDW_RDW*_PRG_HIST tables before purging.

Business logic is based on partition drop process and archival is based on the export_datetime column of SA_EXPDW_RDW* tables.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
SA_SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
SA_EXPDW_RDWT_HEAD	Yes	No	No	Yes
SA_EXPDW_RDWT_DETAIL	Yes	No	No	Yes
SA_EXPDW_RDWF_DETAIL	Yes	No	No	Yes
SA_EXPDW_RDWS_DETAIL	Yes	No	No	Yes
SA_EXPDW_RDWC_DETAIL	Yes	No	No	Yes
SA_EXPDW_RDWT_HEAD_PRG_HIST	No	Yes	No	No
SA_EXPDW_RDWT_DETAIL_PRG_HIST	No	Yes	No	No
SA_EXPDW_RDWF_DETAIL_PRG_HIST	No	Yes	No	No
SA_EXPDW_RDWS_DETAIL_PRG_HIST	No	Yes	No	No
SA_EXPDW_RDWC_DETAIL_PRG_HIST	No	Yes	No	No

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Key Tables Affected

Table 2-7 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
ALL_TAB_PARTITIONS	Yes	No	No	No
ALL_PART_TABLES	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	Yes

Design Assumptions

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This script purges data from tables used for uploading Foundation Data from spreadsheets based on the retention days specified in the system parameterPROC_DATA_RETENTION_DAYS for both Merchandising and Sales Audit and will help in keeping the size of these tables controlled.

Restart/Recovery

N/A

I/O Specifications

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Obsoleted Tax Rules Records (gts_purge_job)

Module Name gts_purge_job

Description Purge obsoleted tax rules records Functional Area Foundation Module Type Admin – Ad hoc Module Technology Background Processing Catalog ID Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job finds all records on GTS interface tables that have passed retention days based on Merchandising system option gts_purge_days and purges the records.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
GTS_ITEM_TAX	No	No	No	Yes
GTS_MERCH_TAX	No	No	No	Yes
GTS_RULES_CFA	No	No	No	Yes
GTS_RULES_CONDITIONS	No	No	No	Yes
GTS_RULES_ERROR	No	No	No	Yes
GTS_RULES_CONFIG_TL	No	No	No	Yes
GTS_RULES_CONFIG	No	No	No	Yes
GTS_BUILDER_STG	No	No	No	Yes
GTS_BUILDER_LOG	No	No	No	Yes
B8D_GTS_PURGE_STG	Yes	No	No	Yes
B8D_PROCESS_CONFIG	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No

Purge Published Financial Data (fif_gl_publish_purge_job)

Module Name	ff_gl_publish_purge_job
Description	Purge Published Financial Data
Functional Area	General Ledger
Module Type	Admin – Ad hoc

Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of one-step processing only.

The Business logic program deletes records from the FIF_GL_PUBLISH table (week partitioned) where the extract date is past the GL_PUBLISH_PURGE_DAYS plus additional 7 days. It then invokes the function PARTITION_SQL.PURGE_INTERVAL_PARTITION , passing the target table name FIF_GL_PUBLISH and the retention days. This function executes the proper deletion/ purging of records from the target table by exercising table partitioning handling, such as Dropping Interval Partition (same as truncate or delete from table). It also archives the old records to the FIF_GL_PUBLISH_PRG_HIST table.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
FIF_GL_PUBLISH	No	No	No	Yes
FIF_GL_PUBLISH_PRG_HIST	No	Yes	No	No
PURGE_CONFIG_OPTIONS	Yes	No	No	No
ALL_TAB_PARTITIONS	Yes	No	No	No

I/O Specification

N/A

Purge SIOCS-RMS History Tables (sim_rms_history_purge_job)

Module Name Description Functional Area Module Type Module Technology

sim_rms_history_purge_job Purge SIOCS-RMS history tables SIOCS-RMS Integration Admin – Ad hoc Background Processing

Catalog ID Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The Business logic program will invoke a call to a new program specific for handling SIOCSRMS historical tables, which are considered partitioned tables.

PARTITION_SQL.PURGE_INTERVAL_PARTITION is called by passing each target table names prefixed with SIM_RMS . This called program will execute the proper deletion/purging of records from target tables by exercising table partitioning handling such as Dropping Interval Partition (same as truncate or delete from table). Records older than SYSDATE minus inbound_data_history_months from SYSTEM_OPTIONS are purged.

Restart/Recovery

N/A

Locking Strategy

N/A

Security Considerations

N/A

Performance Considerations

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
ALL_PART_TABLES	Yes	No	No	No
ALL_TAB_PARTITIONS	Yes	No	No	No
SIM_RMS_ASNOUT_ITEM_HIST	No	No	No	Yes
SIM_RMS_ASNOUT_CARTON_HIST	No	No	No	Yes
SIM_RMS_ASNOUT_DISTRO_HIST	No	No	No	Yes
SIM_RMS_ASNOUT_HIST	No	No	No	Yes

Table	Select	Insert	Update	Delete
SIM_RMS_DSD_RECEIPT_DETAIL_HIST	No	No	No	Yes
SIM_RMS_DSD_RECEIPT_HIST	No	No	No	Yes
SIM_RMS_INV_ADJ_HIST	No	No	No	Yes
SIM_RMS_INVENTORY_REQUEST_DETAIL_HIST	No	No	No	Yes
SIM_RMS_INVENTORY_REQUEST_HIST	No	No	No	Yes
SIM_RMS_PO_RECEIPT_DETAIL_HIST	No	No	No	Yes
SIM_RMS_PO_RECEIPT_HIST	No	No	No	Yes
SIM_RMS_PO_RECEIPT_OVERAGE_HIST	No	No	No	Yes
SIM_RMS_RTV_CFA_HIST	No	No	No	Yes
SIM_RMS_RTV_DETAIL_HIST	No	No	No	Yes
SIM_RMS_RTV_HIST	No	No	No	Yes
SIM_RMS_STOCK_COUNT_RESULT_DETAIL_HIST	No	No	No	Yes
SIM_RMS_STOCK_COUNT_RESULT_HIST	No	No	No	Yes
SIM_RMS_STOCK_COUNT_SCHEDULE_PROD_HIST	No	No	No	Yes
SIM_RMS_STOCK_COUNT_SCHEDULE_STORE_HIS T	No	No	No	Yes
SIM_RMS_STOCK_COUNT_SCHEDULE_HIST	No	No	No	Yes
SIM_RMS_STOCK_ORDER_STATUS_DETAIL_HIST	No	No	No	Yes
SIM_RMS_STOCK_ORDER_STATUS_HIST	No	No	No	Yes
SIM_RMS_STOCK_ORDER_RECEIPT_DETAIL_HIST	No	No	No	Yes
SIM_RMS_STOCK_ORDER_RECEIPT_HIST	No	No	No	Yes
SIM_RMS_FDG_DTL_HIST	No	No	No	Yes
SIM_RMS_FDG_ETT_HIST	No	No	No	Yes
SIM_RMS_FDG_EXT_HIST	No	No	No	Yes
SIM_RMS_FDG_HDR_HIST	No	No	No	Yes
SIM_RMS_FDG_REF_HIST	No	No	No	Yes
SIM_RMS_FDG_TEXT_HIST	No	No	No	Yes
SIM_RMS_STORE_ORDER_REVIEW_HIST	No	No	No	Yes
SIM_RMS_STORE_ORDER_REVIEW_ITEM_HIST	No	No	No	Yes

I/O Specification

N/A

Purge SIOCS-RMS Inbound and Staging Tables (sim_rms_purge_job)

Module Name Description Functional Area

sim_rms_purge_job Purge SIOCS-RMS inbound and staging tables SIOCS-RMS Integration

Module Type Admin – Ad hoc Module Technology Background Processing Catalog ID Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The Business logic program invokes a call to a new program specific for handling SIOCS-RMS inbound tables as well as the corresponding staging tables used: SVC_ADMIN_UPLD_ER and SVC_PROCESS_TRACKER . It will delete all records that are older than sysdate minus the inbound_data_unprocessed_error_days value from SYSTEM_OPTIONS . The batch will not execute when the batch running indicator is on.

Restart/Recovery

N/A

Locking Strategy

Locked tables are skipped, but the process will continue to delete records from the remaining tables. Locked tables will be purged in the next run.

Security Considerations

N/A

Performance Considerations

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
SVC_ADMIN_UPLD_ER	No	No	No	Yes
SVC_PROCESS_TRACKER	No	No	No	Yes
SIM_RMS_INPUT_JSON	No	No	No	Yes
SIM_RMS_ASNOUT	No	No	No	Yes
SIM_RMS_DSD_RECEIPT	No	No	No	Yes
SVC_INV_ADJ	No	No	No	Yes

Table	Select	Insert	Update	Delete
SIM_RMS_INVENTORY_REQUEST	No	No	No	Yes
SIM_RMS_PO_RECEIPT	No	No	No	Yes
SIM_RMS_RTV	No	No	No	Yes
SIM_RMS_STOCK_COUNT_RESULT	No	No	No	Yes
SIM_RMS_STOCK_COUNT_SCHEDULE	No	No	No	Yes
SIM_RMS_STOCK_ORDER_STATUS	No	No	No	Yes
SIM_RMS_STOCK_ORDER_RECEIPT	No	No	No	Yes
SIM_RMS_STORE_ORDER_REVIEW	No	No	No	Yes

I/O Specification

N/A

Purge Staged Financial General Ledger Data (stg_fif_gl_purge_job)

Module Name stg_fif_gl_purge_job Description Purge Staged Financial General Ledger Data Functional Area General Ledger Module Type Admin – Ad hoc Module Technology Background Processing Catalog ID Wrapper Script b8dwrap.ksh

Scheduling

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of one-step processing only.

The Business logic program invokes a call to a new program specifically for handling historical tables that are considered partitioned tables. PARTITION_SQL.PURGE_INTERVAL_PARTITION is called passing the target table name STG_FIF_GL_DATA and retention days of 14. This function will execute the proper deletion/purging of records from the target table by exercising table partitioning handling such as Dropping Interval Partition (same as truncate or delete from table).

Restart/Recovery

N/A

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
STG_FIF_GL_DATA	No	No	No	Yes
SYSTEM_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
ALL_TAB_PARTITIONS	Yes	No	No	No
ALL_PART_TABLES	Yes	No	No	No

I/O Specification

N/A

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.

Schedule

Oracle Retail Merchandising Batch Schedule

Restart/Recovery

N/A

I/O Specification

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

This job (and its successor job subscription_metrics_update_job ) is also executed from the START_RIB_ADAPTOR_NON_INV_JOB job at the end of the MFCS nightly process.

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 isA(active) and the item status isA(approved). Only transaction-level items should be considered. Three active franchise location ranging records are considered equal to one retailer-owned active ranging record when calculating active item- location metrics.
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 isA(active) should be considered.

RBM_ID	METRIC_NAME	METRIC_DESC	COMMENTS
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 20, by receipt date, where the adjustment code is null, across all locations.
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 code44by day across all locations.
140	cost_changes	Total number of cost changes executed	Sum by effective date the cost change header records with a status of Extracted (E) .
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 (E) .
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 (E) .
143	late_posted_transactions	The rate of late posted transactions calculated as the count of Tran Data records where tran date < post date )/ (count of tran data records).	Calculate the rate of the late posted transaction from tran_data_history.
144	wf_gross_margin	Gross margin for B2B sales, calculated as (franchise sales retail - franchise sales cost) / franchise sales retail.	Calculate the gross margin of wholesale franchsie.me
145	gross_margin	Gross margin for non-B2B sales, calculated as gross margin amount / net sales retail.	Calculate the gross margin of wholesale franchise.

Retail Business Metrics Calculation (rbm_metrics_calc_job)

RBM_ID	METRIC_NAME	METRIC_DESC	COMMENTS
146	total_sales_revenue	Total sales revenue using the value of net sales at retail per period.	Calculate the total sales revenue value per period.
147	wf_order_fll_rate	B2B order fll rate calculated as the count of B2B orders without cancelled quantity / total number of B2B orders.	Calculate the percentage of fulfl franchise orders without cancelled quantity.
148	inv_turnover_rate	Inventory turn rate, calculated as net sales at cost / average inventory, with average inventory calculated as (opening stock + closing stock)/2.	Cost of Goods Sold / Average Inventory.
149	po_fll_rate	PO fll rate, calculated as the count of orders without cancelled quantity / total number of orders in a period.	Calculate the percentage of fulfl orders without cancelled quantity.
150	sell_through_rate	Sell through rate, calculated as total units sold / total units received on POs in a period.	Calculate the percentage rate of the selling goods.
151	shrink_rate	Shrink rate, calculated as the value of shrink / average inventory value, with average inventory calculated as (opening stock + closing stock) / 2.	Calculate the percentage rate of shrinkage.
152	stock_availability_rate	Stock Availability Rate is calculated as the count of expected item/locations / count of in stock item/ locations. Expected items are item/ locations in Active or Discontinued status. In stock item/item locations are item/ locations in Active or Discontinued status that have stock on hand.	Calculate stock availability rate

RBM_ID	METRIC_NAME	METRIC_DESC	COMMENTS
153	stock_out_rate	Stock Out Rate is calculated as the inverse of the Stock Availability Rate (1- (count of expected item/locations / count of in stock item/ locations). Expected items are item/ locations in Active or Discontinued status. In stock item/item locations are item/ locations in Active or Discontinued status that have stock on hand.	Calculate stock out rate
154	issues_resolution_rate	Issue resolution rate, calculated as the number of audit issues resolved / total Issues identifed in a period.	Calculate the Sales Audit Issue Resolution Rate.
155	reconciliation_timeliness	The average time required to close store days in Sales Audit, calculated as the average of store close date - business date for a period.	Calculate the Sales Audit Reconciliation Timeliness.
156	unresolved_discrepancy_aging	Unresolved discrepancy aging, calculated as the average number of days outstanding for unresolved sales audit discrepancies.	Calculate the Sales Audit Issue Resolution Rate.
157	coverage_rate	Coverage rate is used to measure the percentage of stores being audited, calculated as count of audited store days / store days expected.	Calculate the Sales Audit Coverage Rate.
158	exception_rate	Exception rate calculated as the count of errors found / total transactions audited	Calculate the Sales Audit Exception Rate.

RBM_ID	METRIC_NAME	METRIC_DESC	COMMENTS
159	correction_rate	Percent of transactions requiring manual correction after initial sale, calculated as count of transactions with errors/total transactions for a period.	Calculate the Sales Audit Correction Rate.
160	over_short_variance_rate	The rate of over/ short variances in a period, calculated as count of discrepancies for the Over/Short Store total / all Over/Short Store totals.	Calculate the Sales Audit Store Over/Short Variance Rate.
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 (5).
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 (5).
511	pricing_accuracy	Pricing accuracy, calculated as the sum of sales units sold ”off retail” (prom type = 1006) / sum of sales units sold for a period.	Sum of sales units sold “off retail” (prom type = 1006) / sum of sales units sold for a period.
512	gross_margin_roi	Gross margin return on investment calculated as Gross Margin / ((Opening Stock Retail + Closing Stock Retail)/2).	Gross Margin / Average Inventory Cost.
513	markdown_rate	Markdown Rate calculcated as (Promotional Markdowns + Clearance Markdown) / Net Sales.	(Promotional Sales + Clearance Sales) / Net Sales.
514	promotional_effectiveness	Promotional Effectiveness calculcated as Promotional Sales / Regular Sales.	Promotional Sales / Regular Sales.

RBM_ID	METRIC_NAME	METRIC_DESC	COMMENTS
515	clearance_markdown_rate	Clearance markdown rate calculated as Clearance Markdowns / Net Sales.	clearance markdown rate.
600	Allocations	Total number of allocations sent to Merchandising for execution	Sum of count of allocations (ALC_ALLOC) inApprovedorPO Createdstatus by last update date.
601	allocation_line_items	Total number of allocation lines sent to Merchandising for execution	Sum of count of allocation details (ALC_ITEM_LOC) for allocations inApprovedorPO Createdstatus by last update date.
700	Invoices	Total number of merchandise invoices posted to AP	Sum of count of documents of type Merchandise Invoice (MRCHI) with a status of Posted (POSTED) by posted date.
701	invoices_line_items	Total number of invoice lines posted to AP	Sum of count of detail lines for merchandise invoices in Postedstatus by posted date.
710	other_documents	Total number of documents posted to AP, excluding merchandise invoices	Sum of count of documents that are not type Merchandise Invoice (MRCHI) with a status of Posted (POSTED) by posted date.
711	invoice_auto_match_rate	The rate at which merchandise invoices are auto matched. Calculated as the number of invoices in matched or posted status with a match type of Auto (A) or External (E) / Total invoices in matched or posted status.	Calculate the rate of ensure invoice processing accuracy.
712	invoice_discrepancy_rate	Invoice discrepancy rate calculated as the total value of discrepancies / total invoice value for merchandise invoices in matched or posted status for the period.	Calculate the rate of invoice discrepancy.
713	manually_matched_invoices	Count of merchandise invoices manually matched in posted or matched status.	Count of merchandise invoices manually matched in posted or matched status.

RBM_ID	METRIC_NAME	METRIC_DESC	COMMENTS
714	avg_invoice_process_time	Invoice processing time calculated as the average number of days between posted date and document date for merchandise invoices in posted status.	Average number of days between posted date and document date for merchandise invoices.
715	invoice_creation_automation	Invoice creation automation calculated as the number of invoices received via EDI / total vendor invoices.	Number of invoices received via EDI / Total vendor invoices.
716	invoices_written_off	Count of merchandise invoices calculated as the sum the value of variance within tolerance + invoices with a resolution action of DWO for merchandise invoices in posted or matched status.	Sum the value of variance within tolerance + invoices with a resolution action of DWO for merchandise invoices in posted or matched status.
717	charge_backs_debit_memo	Chargeback rate calculated as the count of debit memos of type Resolution / count of total merchandise invoices.	Count of debit memos of type Resolution / Count of total merchandise invoices.
718	charge_backs_credit_note	Chargeback rate calculated as the count of credit note requests of type Resolution / count of total merchandise invoices.	Count of credit note requests of type Resolution / Count of total merchandise invoices.
719	charge_backs_credit_memos	Chargeback rate calculated as the count of credit memos of type Resolution / count of total merchandise invoices.	Charge backs (credit memos).

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.

Additional enabled metrics cover late-posted transactions, B2B and non-B2B gross margin, sales revenue, B2B and purchase-order fill rate, inventory turn, sell-through, shrink, stock availability and stock-out rate, Sales Audit issue resolution, reconciliation timeliness, discrepancy aging, coverage, exception, correction, and over/short variance rates. Additional pricing metrics cover pricing accuracy, gross-margin return on investment, markdown, promotional effectiveness, and clearance markdown rates. Invoice metrics cover auto-match and discrepancy rates, manually matched invoices, processing time, invoice-creation automation, invoices written off, and debit memo, credit note, and credit memo chargebacks.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
RBM_MASTER	Yes	No	No	No
RBM_VALUES	Yes	Yes	Yes	No

Design Assumptions

N/A

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	Beforeload_item_forecast.kshweekly runs that truncates the data inITEM_FORECASTtable.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_FORECAST	Yes	No	No	No
ITEM_FORECAST_HIST	No	Yes	No	Yes

Design Assumptions

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

Schedule

Oracle Retail Merchandising Batch Schedule

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.

This job (and its predecessor job rbm_metric_calc_job ) is also executed from the START_RIB_ADAPTOR_NON_INV_JOB job at the end of the Merchandising nightly process.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
RAF_SUBS_METRIC_USAGE	Yes	Yes	No	No
RAF_SUBS_METRIC_QTY	Yes	Yes	Yes	No

Design Assumptions

N/A

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

Scheduling

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Thread assignment program will filter eligible records from 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.

Restart/Recovery

N/A

Key Tables Affected

Table 2-8 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_TAX_EVENT_PURGE_STG	Yes	Yes	No	Yes
TAX_CALC_EVENT	No	No	No	Yes
PERIOD	Yes	No	No	No

Input/Output Specification

N/A

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

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch purges the tax events from the tax calculation event table. The records to be purged are based on its last update timestamp along with the tax event result.

Restart/Recovery

N/A

Design Assumptions

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

Schedule

Oracle Retail Merchandising Batch Schedule

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

Restart/Recovery

N/A

Design Assumptions

N/A

---

In this guide

• Guide: Batch Operations Guide
• Previous: 19 Sales Audit
• Next: 3 Foundation Data Maintenance

Related chapters

• 5 ReSTful Web Services — Inbound and Outbound Integration Guide · shares COST_SUSP_SUP_DETAIL, COST_ZONE_GROUP_LOC, DAILY_PURGE, DEAL_DETAIL
• 3 Foundation Data Maintenance — Batch Operations Guide · shares COMPANY_CLOSED_EXCEP, COST_ZONE, COST_ZONE_GROUP_LOC, DIFF_RATIO_DETAIL
• 15 Sales History — Batch Operations Guide · shares ALL_PART_TABLES, ALL_TAB_PARTITIONS, CLASS_SALES_HIST, DAILY_SALES_DISCOUNT
• G Appendix: Tables List — Merchandising Cloud Services Data Conversion Implementation Guide · shares COND_TARIFF_TREATMENT, COST_SUSP_SUP_DETAIL, DIFF_RATIO_DETAIL, DIFF_RATIO_HEAD
• 6 Scheduled Integration — Inbound and Outbound Integration Guide · shares DAILY_DATA, DAILY_SALES_DISCOUNT, EOW_DATE, FIF_AP_DETAIL_PUBLISH
• 3 Custom Validation Rules — Merchandising Cloud Services Customization and Extension Guide · shares ITEM_CHRG_DETAIL, ITEM_CHRG_HEAD, ITEM_EXP_DETAIL, ITEM_EXP_HEAD