Item Maintenance

4 Item Maintenance

This chapter contains information about the batch processes that relate to item maintenance.

Program Summary

Table 4-1 Item Maintenance - Program Summary

Program	Description
gtsbuilder	Global Tax Solution Builder
item_loc_purge_job	Daily Purge of Item-Location Data
itemupchrgcleanup	Item Up-Charge Cleanup
itm_indctn_purge.ksh	Purge Item induction staging tables
sitmain.pc	Scheduled Item Maintenance
vatdlxpl.pc	Mass VAT Updates for Items/Locations

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

Global Tax Solution Builder (gtsbuilder)

Module Name	gtsbuilder.ksh
Description	GTS Builder Processing
Functional Area	Foundation
Module Type	Business Processing
Module Technology	Ksh
Catalog ID	TBD
Runtime Parameters	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program is used to process new, modified or expired rules of the global tax solution. The criteria to find these rules are based on the rule status and the field END_DATE.

Any rule with status UPDATED or APPROVED will be considered to be processed. Rules in status ACTIVE but with the field END_DATE filled with a date older than the tax builder execution date will also be picked for processing.

The processing logic in each of the above scenarios will be:

Processing rules in status UPDATED: rules can be updated exclusively in the field END_DATE. In this case the date informed in the rule will be updated in GTS_MERCHT_TAX and GTS_ITEM_TAX tables. Once these updates are done, the status of the rule will be changed back to ACTIVE.
Processing rules in status APPROVED: rules in this status are basically new rules included in the system. The GTS builder process will also identify rules with conditions at parent level regions or higher level of merchandise hierarchy (dept or class) and will store new records into GTS_MERCH_TAX and GTS_ITEM_TAX table for the lowest level of region (child regions) and merchandise hierarchy (subclass). Once these updates are done, the status of the rule will be changed to ACTIVE.
Processing rules in status ACTIVE: the process will look for the field END_DATE in active rules. If this field is filled with a date older than the execution date, the process will simply change the rule status to CLOSED. No updates will be performed at merchandise level tax tables nor item level tax tables.

Restart/Recovery

In case of failure, the GTS builder batch will continue picking new or updated rules to process. During activation if any rule fails an error will be stored and the rule will have it status modified to "worksheet" for further review.

Design Assumptions

N/A

Item Up-Charge Cleanup (itemupchrgcleanup)

Module Name	cleanupitemupchrg
Description	Item Up-Charge Cleanup
Functional Area	Foundation
Module Type	Admin (Ad Hoc)
Module Technology	PLSQL
Catalog ID
Wrapper Script	rmswrap_plsql.ksh

Schedule

Oracle Retail Merchandising Batch Schedule (Ad Hoc)

The cleanup can be run as needed. Multiple executions will be required depending on the volume of eligible items and the configured processing limits.

cleanupitemupchrg

Design Overview

The purpose of this standalone ad-hoc batch program is to remove Item Charges that exactly match Department Charges to reflect exception-based item level up charges. Running this batch will delete unnecessary records from ITEM_CHRG_HEAD and ITEM_CHRG_DETAIL and update ITEM_MASTER.TSF_ALLOC_UP_CHRG_SOURCE_TYPE to D for those records, helping improve performance on processes referencing these tables.

Given the significant volume of the ITEM_CHRG_* tables, the batch is designed to run in multi-threaded mode with a control on the number of records that can be processed in the single run and the batch execution time. These parameters can be configured via Background Process Configuration Induction template under the Batch Name - ITEM_UPCHARGE_CLEANUP_ADHOC_JOB.

Configuration	Description
NUM_DATA_TO_PROCESS	Sets the candidate item limit per configured thread when preparing new work. The total candidate limit is this value multiplied by the number of threads. Default is 10000
NUM_THREADS	Sets the number of parallel workers. Increasing this value may improve throughput but also increases database resource usage. Items may not be distributed equally between workers. Default is 6
LIMIT_BATCH_TIME	Sets the processing time allowance for each worker in seconds. A positive value enables the limit; zero or a negative value disables it. Default is -1

For example, 1,000 records and four threads allow up to 4,000 candidate items to be prepared. A time limit of 300 gives each worker approximately five minutes to process its assigned items after threading phase has completed.

The process retains pending work for subsequent executions and checks charge eligibility again during worker selection. Items locked by another activity are skipped, and a warning containing the item number is recorded in the job audit logs.

The thread assignment process first identifies the records eligible for processing and stages them in a staging table. Once staging is complete, DBMS_SCHEDULER jobs are used to process the staged records in parallel.

This job needs to run multiple times till all the cleanup has completed. Below message in the Job logs indicates that all eligible records have been reviewed and cleanup is completed- ‘Item Upcharge Cleanup has completed. There are no more pending eligible records left for processing.’

Restart/Recovery

The process saves each successfully completed item. If an execution fails or reaches its time allowance, previously completed cleanup is retained in B8D_ITEM_UPCHARGE_CLEANUP_STG table.

Subsequent executions reuse pending work from B8D_ITEM_UPCHARGE_CLEANUP_STG table or refresh the candidate list when required. Skipped items will be reconsidered during the new run.

Successful completion of an execution does not necessarily mean that all eligible items have been processed. Review the job audit logs for processing status, time-limit messages, and locked items that were skipped.

I/O Specification

No additional parameters are required to be sent for batch processing.

The process reads item and department charge information, removes redundant item charges, and updates the item’s up-charge source. Processing status and skipped-item information are recorded in the job audit logs.

Key Tables Affected

Table	Select	Insert	Update	Delete
B8D_PROCESS_CONFIG	Yes	No	No	No
ITEM_CHRG_HEAD	Yes	No	No	Yes
ITEM_CHRG_DETAIL	Yes	No	No	Yes
ITEM_MASTER	Yes	No	Yes	No
B8D_ITEM_UPCHARGE_CLEANUP_STG	Yes	Yes	No	Yes

Design Assumptions

Only items whose charges fully match their department’s charges are eligible for cleanup. A partial match is insufficient.

Configuration settings should remain consistent while pending work is being reused, particularly the number of threads.

Customers should select processing volumes, thread counts, and time allowances appropriate to the available database capacity and operational window.

Mass VAT Updates for Items/Locations (vatdlxpl)

Module Name	vatdlxpl.pc
Description	Mass VAT Updates for Items/Locations
Functional Area	Item Maintenance
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS384
Runtime Parameters	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program updates VAT information for each item associated with a given VAT region and VAT code.

Restart/Recovery

This batch program performs commits to the database for every commit max number of rows.

Design Assumptions

N/A

Purge Item Induction Staging Tables (itm_indctn_purge.ksh)

Module Name	itm_indctn_purge.ksh
Description	Purge item induction staging tables
Functional Area	Foundation-Items
Module Type	Admin
Module Technology	Shell Script
Catalog ID	RMS498
Runtime Parameters	N/A

Design Overview

The purpose of this module is to remove old item records from the staging tables. Records that are candidates for deletion are:

Processes that have successfully been processed or processed with warnings that have been uploaded to Merchandising or downloaded to S9T
Processes that have status = 'PE', processed with errors and have no linked data
Processes in error status where all other related records containing the process ID have been processed successfully
Processes that have errors and are past the data retention days (system_options.proc_data_retention_days)
All item records within a process where all related records for the item in the other staging tables are successfully uploaded to Merchandising. The process tracker record for that process should not be deleted if there are other item records that are not uploaded to Merchandising.

Scheduling Constraints

Table 4-3 Scheduling Constraints

Schedule Information	Description
Processing Cycle	Ad Hoc
Frequency	Daily
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

Restart ability is implied, because the records that are selected from the cursor are deleted before the commit.

Scheduled Item Maintenance (sitmain)

Module Name	sitmainsqls.pls/sitmainsqlb.pls
Description	Scheduled Item Maintenance
Functional Area	Item Maintenance
Module Type	Business Processing
Module Technology	PLSQL
Catalog ID	RMS357
Wrapper Script	rmswrap_plsql.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

Scheduled item maintenance is a method of performing mass changes on item/location information. Scheduled item maintenance uses item and location lists to make the process of changing lots of information very easy for end users.This program explodes the intersection of these items and location lists to make the scheduled changes at the specific item/location level.

Restart/Recovery

This program has inherent restart ability because records are deleted from the scheduled item detail table as they are processed. The logical unit of work is an item/location combination.

Design Assumptions

N/A