Daily Purge of Item-Location Data (item_loc_purge_job)

Module Name	item_loc_purge_job
Description	Daily Purge of Item-Location Data
Functional Area	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 users 'delete' an item-location 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.

Thread assignment program (ITEM_LOC_PURGE_THREAD) will filter eligible records from daily purge (DAILY_PURGE) table wherein all entities ready for purging are exclusively related to Item-Location (ITEM_LOC table) records. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table B8D_ITEM_LOC_PURGE_STG.

The Business logic program (ITEM_LOC_PURGE) will process all records from the staging table. Using bulk processing, this program will delete item-location data from item-location related and associated tables. Complex referential integrity relationships determine whether data can actually be deleted from the database. 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 item-location data 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 4-2

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_ITEM_LOC_PURGE_STG	Yes	No	No	No
DAILY_PURGE	Yes	No	No	Yes
DAILY_PURGE_ERROR_LOG	Yes	Yes	No	Yes
REPL_RESULTS	Yes	No	No	Yes
BUYER_WKSHT_MANUAL	Yes	No	No	Yes
SUB_ITEMS_DETAIL	Yes	No	No	Yes
SUB_ITEMS_HEAD	Yes	No	No	Yes
REPL_ATTR_UPDATE_EXCLUDE	Yes	No	No	Yes
MASTER_REPL_ATTR	Yes	No	No	Yes
REPL_DAY	Yes	No	No	Yes
REPL_ITEM_LOC	Yes	No	No	Yes
REPL_ITEM_LOC_UPDATES	Yes	Yes	No	Yes
REPL_ITEM_LOC_SUPP_DIST	Yes	No	No	Yes
COST_SUSP_SUP_DETAIL_LOC	Yes	No	No	Yes
FUTURE_COST	Yes	No	No	Yes
ITEM_LOC_MFQUEUE	Yes	No	No	Yes
ITEM_LOC	Yes	No	No	Yes
ITEM_LOC_SOH	Yes	No	No	Yes
ITEM_LOC_TRAITS	Yes	No	No	Yes
ITEM_LOC_CFA_EXT	Yes	No	No	Yes
ITEM_SUPP_COUNTRY_BRACKET_COST	Yes	No	No	Yes
ITEM_SUPP_COUNTRY_LOC_CFA_EXT	Yes	No	No	Yes
ITEM_SUPP_COUNTRY_LOC	Yes	No	No	Yes

I/O Specification

N/A