4 Item Maintenance

This chapter contains information about the batch processes that related to item maintenance. These processes include general item integration and processes to make mass changes to low level item information.

Program Summary

Table 4-1 Item Maintenance - Program Summary

Program	Description
sitmain.pc	Scheduled Item Maintenance
vatdlxpl.pc	Mass VAT Updates for Items/Locations
iindbatch.ksh	Upload item induction data through batch
itm_indctn_purge.ksh	Purge Item induction staging tables
Pricingeventprocess.ksh	Processing and application of Price events when RPM is not used.

sitmain (Scheduled Item Maintenance)

Module Name	sitmain.pc
Description	Scheduled Item Maintenance
Functional Area	Item Maintenance
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS357
Runtime Parameters	NA

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 item and location lists to make the scheduled changes at the specific item/location level.

Scheduling Constraints

Table 4-2 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This module should run after LCLRBLD.PC.
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

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

Key Tables Affected

Table 4-3 Key Tables Affected

Table	Select	Insert	Update	Delete
SIT_EXPLODE	Yes	No	Yes	No
SIT_DETAIL	Yes	No	No	Yes
ITEM_LOC	Yes	Yes	Yes	No
MC_REJECTIONS	Yes	No	Yes	No
ITEM_MASTER	Yes	No	No	No
PRICE_HIST	No	Yes	No	No
ITEM_LOC_SOH	No	Yes	No	No

vatdlxpl (Mass VAT Updates for Items/Locations)

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	NA

Design Overview

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

Scheduling Constraints

Table 4-4 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Run as needed.
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

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

Key Tables Affected

Table 4-5 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
VAT_CODE_RATES	Yes	No	No	No
VAT_ITEM	Yes	Yes	Yes	No
ITEM_LOC	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
STORE	Yes	No	No	No
CLASS	Yes	No	No	No

iindbatch.ksh (Upload Item Data)

Module Name	iindbatch.ksh
Description	Upload Item Data
Functional Area	Item Maintenance
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS474
Runtime Parameters	Database connection, Input File Name, Template Name, Destination (Optional Input Parameter)

Design Overview

This batch program is used to Bulk upload xml file data from template files to S9T_FOLDER table (into content_xml column). This batch will be responsible for validating the input parameters, below are the list of validations.

• The Input file should exist.

• The Input file's extension must be ”.xml”.

• The template_name should be valid.

• Destination (Optional Parameter) should be STG or RMS. If destination is not passed then default it to STG.

Scheduling Constraints

Table 4-6 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Key Tables Affected

Table 4-7 Key Tables Affected

Table	Select	Insert	Update	Delete
S9T_FOLDER	No	Yes	No	No
S9T_TEMPLATE	Yes	No	No	No
SVC_PROCESS_TRACKER	No	Yes	No	No
RMS_ASYNC_STATUS	No	Yes	No	No
RMS_ASYNC_RETRY	No	Yes	No	No

itm_indctn_purge.ksh (Purge Item Induction Staging Tables)

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	NA

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 RMS 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 RMS. The process tracker record for that process should not be deleted if there are other item records that are not uploaded to RMS.

Scheduling Constraints

Table 4-8 Scheduling Constraints

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

Restart/Recovery

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

Key Tables Affected

Table 4-9

Table	Select	Insert	Update	Delete
PROC_DATA_RETENTION_DAYS	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
SVC_PROCESS_TRACKER	Yes	No	No	Yes
SVC_PROCESS_ITEMS	No	No	No	Yes
SVC_ITEM_COST_DETAIL	No	No	No	Yes
SVC_ITEM_COST_HEAD	No	No	No	Yes
SVC_ITEM_COUNTRY	No	No	No	Yes
SVC_ITEM_COUNTRY_L10N_EXT	No	No	No	Yes
SVC_ITEM_MASTER	No	No	No	Yes
SVC_ITEM_MASTER_TL	No	No	No	Yes
SVC_ITEM_MASTER_CFA_EXT	No	No	No	Yes
SVC_ITEM_SUPPLIER	No	No	No	Yes
SVC_ITEM_SUPPLIER_TL	No	No	No	Yes
SVC_ITEM_SUPPLIER_CFA_EXT	No	No	No	Yes
SVC_ITEM_SUPP_COUNTRY	No	No	No	Yes
SVC_ITEM_SUPP_COUNTRY_CFA_EXT	No	No	No	Yes
SVC_ITEM_SUPP_COUNTRY_DIM	No	No	No	Yes
SVC_ITEM_SUPP_MANU_COUNTRY	No	No	No	Yes
SVC_ITEM_SUPP_UOM	No	No	No	Yes
SVC_ITEM_XFORM_DETAIL	No	No	No	Yes
SVC_ITEM_XFORM_HEAD	No	No	No	Yes
SVC_ITEM_XFORM_HEAD_TL	No	No	No	Yes
SVC_PACKITEM	No	No	No	Yes
SVC_RPM_ITEM_ZONE_PRICE	No	No	No	Yes
SVC_XITEM_RIZP_LOCS	No	No	No	Yes
SVC_XITEM_RIZP	No	No	No	Yes
SVC_ITEM_SEASONS	No	No	No	Yes
SVC_UDA_ITEM_DATE	No	No	No	Yes
SVC_UDA_ITEM_FF	No	No	No	Yes
SVC_UDA_ITEM_LOV	No	No	No	Yes
SVC_VAT_ITEM	No	No	No	Yes
SVC_ITEM_IMAGE	No	No	No	Yes
SVC_ITEM_IMAGE_TL	No	No	No	Yes
SVC_ITEM_HTS	No	No	No	Yes
SVC_ITEM_HTS_ASSESS	No	No	No	Yes
SVC_COST_SUSP_SUP_HEAD	No	No	No	Yes
SVC_COST_SUSP_SUP_DETAIL_LOC	No	No	No	Yes
SVC_COST_SUSP_SUP_DETAIL	No	No	No	Yes
SVC_CFA_EXT	No	No	No	Yes
CORESVC_ITEM_ERR	No	No	No	Yes
S9T_ERRORS	No	No	No	Yes
SVC_PROCESS_CHUNKS	No	No	No	Yes
S9T_FOLDER	No	No	No	Yes

Pricingeventprocess.ksh (Main Processing of Executing the Price Events)

Module Name	pricingeventprocess.ksh
Description	Main Processing of executing the staged pricing events
Functional Area	Price change
Module Type	Business Processing
Module Technology	ksh
Catalog ID	RMS494
Runtime Parameters	NA

Design Overview

This batch will be used when RPM is not used for Pricing. The purpose of the PRICINGEVENTPROCESS.KSH module is to process price events from the staged data which is populated by the Price Event RIB API. The staged pricing events for the next vdate is exploded based on the hierarchy level and is loaded into a temporary table. The price events are grouped into threads and chunks based on item and locations. The data is processed by thread for each chunk. The following common functions are performed on each price event record read from the stating table:

• Explode data at item/location level

• Group the data into threads and chunks based on item/location

• Validate price event

• Call CORESVC_XPRICE_SQL.PROCESS_DETAILS to execute the price events

Scheduling Constraints

Table 4-10 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	The number of threads running in parallel is based on value in the column RMS_PLSQL_BATCH_CONFIG.MAX_CONCURRENT_THREADS with the program name ”CORESVC_XPRICE_SQL”. Threading is based on chunks. Each chunk should have a defined size. This is defined in RMS_PLSQL_BATCH_CONFIG.MAX_CHUNK_SIZE. Chunks could be made up of a single or multiple THEAD/Items. Because multithreading logic based on chunks is applied, it is possible that a record is locked by another thread. Without a mechanism to perform waiting/retrying, record locking errors can occur more frequently. Note: The table RMS_PLSQL_BATCH_CONFIG, RETRY_LOCK_ATTEMPTS contains the number of times the thread attempts to acquire the lock for a table, and RETRY_WAIT_TIME is the number of seconds the thread waits before it retries.

Table 4-11 Example - Max Concurrent Threads and Chunk Size

MAX_CONCURRENT_THREADS	MAX_CHUNK_SIZE
4	3

In this run, threads are allocated based on the location. If there are 32 locations and the max thread is 4, then each thread contains 8 locations. In the example, there are 4 locations, so each location is allocated with different threads.

Thread 1	Chunk 1	loc 1	Item 1
Thread 1	Chunk 1	loc 1	Item 2
Thread 1	Chunk 1	loc 1	Item 3
Thread 2	Chunk 2	loc 2	Item 2
Thread 2	Chunk 2	loc 2	Item 3
Thread 2	Chunk 2	loc 2	Item 5
Thread 3	Chunk 3	loc 3	Item 6
Thread 3	Chunk 3	loc 3	Item 7
Thread 3	Chunk 3	loc 3	Item 8
Thread 4	Chunk 4	loc 4	Item 4
Thread 4	Chunk 4	loc 4	Item 2
Thread 4	Chunk 4	loc 4	Item 1

Restart/Recovery

The logical unit of work for this batch is a chunk. In the case of a failure of any record, the record is marked as Failed and processing continues on to process next records. In the case of a restart, all the failed records are updated with status, because ’N', chunk_id is reassigned based on the values in RMS_PLSQL_BATCH_CONFIG table and reprocessed.

Locking Strategy

Since the price event processes are run multiple times, a locking mechanism is put in place to allow online transactions and the pricingeventprocess.ksh module to run at the same time. The following tables would be locked for update:

• ITEM_MASTER

• ITEM_LOC

• REPL_ITEM_LOC

• SUP_DATA

Because multithreading logic based on chunks is applied, it is possible that a record is locked by another thread. Without a mechanism to perform waiting/retrying, record locking errors occur more frequently.

In the table RMS_PLSQL_BATCH_CONFIG, RETRY_LOCK_ATTEMPTS is the number of times the thread attempts to acquire the lock for a table. RETRY_WAIT_TIME is the number of seconds the thread waits before it retries. Once the number of retries is equal to the limit defined, the whole chunk is not processed and marked as failed.

Key Tables Affected

Table 4-12 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_LOC	Yes	No	Yes	No
ITEM_LOC_SOH	Yes	No	No	No
STORE	Yes	No	No	No
WH	Yes	No	Yes	No
ITEM_MASTER	Yes	No	No	No
DIFF_GROUP_HEAD	Yes	No	No	No
DIFF_GROUP_DETAIL	Yes	No	No	No
CHAIN	Yes	No	No	No
AREA	Yes	No	No	No
REGION	Yes	No	No	No
DISTRICT	Yes	No	No	No
CURRENCIES	Yes	No	No	No
STORE_HIERARCHY	Yes	No	No	No
ITEM_SUPP_COUNTRY_LOC	Yes	No	No	No
ITEM_SUPP_COUNTRY	Yes	No	No	No
PRICE_HIST	Yes	Yes	No	No
EMER_PRICE_HIST	No	Yes	No	No
SUP_DATA	No	Yes	No	No
TRAN_DATA	No	Yes	No	No
REPL_ITEM_LOC	Yes	No	Yes	No
SVC_PRICING_EVENT_HEAD	Yes	Yes	Yes	No
SVC_PRICING_EVENT_LOCS	Yes	Yes	No	No
SVC_PRICING_EVENT_TEMP	Yes	Yes	Yes	No

Integration Contract

Integration Type	Upload to RMS
File Name	NA
Integration Contract	NA

Design Assumptions

• Required fields are shown in the RIB documentation.

• Data being subscribed is assumed to be correct in terms of pricing information.

• Validations similar to that of conflict checking in RPM are not in scope.

• Complex Promotions are not supported.

Financial Transaction

pricingeventprocess.ksh writes transaction records to the TRAN_DATA table. For the full list of transaction codes, see the chapter addressing general ledger batch in this volume of the RMS Operations Guide, for the column TRAN_CODE.

pricingeventprocess.ksh writes the following:

Table 4-13 Transaction Codes

Transaction Code	Description
11	Markup (retail only)
12	Markup cancel (retail only)
13	Permanent Markdown (retail only)
14	Markdown cancel (retail only)
15	Promotional Markdown (retail only), including ’in-store' markdown
16	Clearance Markdown