Pricing Batch Processes

2 Pricing Batch Processes

This chapter discusses batch processing within Oracle Retail Pricing.

Note:

Details around incoming/outgoing file storage are available in the "Merchandising File Transfer Service" section of the Merchandising Operations Guide - Vol 2.

Table 2-1 Functional Descriptions and Dependencies

Batch processes	Details
BDI Clearance Publishing	This batch process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to stage clearance data. The batch job BDI_PRICING_CLR_TX _JOB stages clearance data.
BDI Price Change Publishing	This batch process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to stage clearance data. The batch job BDI_PRICING_PC_TX _JOB stages price change data.
Clearance Cache Batch	This batch process stages pre-built JSON Messages to be used by the item/price Rest Service for clearance data.
ClearanceInductionBatch	This batch program allows the user to upload clearance events in bulk.
ClearancePriceChangePublishBatch	This batch process formats and stages output of clearance price change price events to be published via a flat file format.
ClearanceRDEDataBatch	This batch populates clearance data to be replicated to RDS for RDE to use.
futureRetailPurgeBatch	This timed multi-threaded batch deletes records from the future retail that are past the retention period of the associated price events.
FutureRetailRollUpBatch.sh	This batch attempts to roll up timelines at a lower level by comparing lower level timelines to higher levels and removing any lower level timelines that match higher level timelines exactly.
itemReclassBatch	When items are moved from one department/class/subclass to another in the merchandising system, this batch process accordingly sets the correct department/class/subclass for these items in the Future Retail table as well as updating the department as necessary in the RPM_ITEM_LOC table.
NewItemLocationBatch	This batch ranges item locations by putting them into the future retail table. Item and locations are fed to this program through the RPM_ITEM_LOC_WS table, which is populated by a Merchandising process.
NightlyBatchCleanup	This batch performs "clean up" logic against Pricing database objects.
Price Change Cache Batch	This batch process stages pre-built JSON Messages to be used by the item/price Rest Service for price change data.
PriceChangeInductionBatch	This batch program allows the user to upload regular price changes in bulk.
PriceEventExecutionBatch	This batch process performs the necessary work to start (regular price change, clearance price change, promotions) and end (price clearances, promotions) pricing events.
priceEventExecutionForChunkCCEmergencyEvents	This batch process performs the work to start and end emergency pricing events which were processed in chunk conflict checking.
ProcessPendingChunkBatch	The batch looks for any events from the chunk conflict checking process that need additional processing due to errors, and reprocesses them using regular conflict checking.
Promotion Cache Batch	This batch process stages pre-built JSON Messages to be used by the item/promo Rest Service for promotion data.
PromotionInductionBatch	This batch program allows the user to upload promotions in bulk.
Promotion Publishing	This batch process utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to target applications. The batch job BDI_PRICING_PROMO_OFFER_TX_CYCLE_JOB stages promotion data.
PurgeBatch	This purge batch calls most of the purge logic into one purge process.
PurgeGttCaptureBatch	This batch process deletes records from gtt data capture tables.
PurgeItemLocPayloadUnusedCCBatch	This batch specifically cleans up data related to staged payloads, conflict data for non-existent events and deleted items/item-locations.
RegularPriceChangePublishBatch	This batch process formats and stages output of regular price change price events to be published through a flat file format.

BDI Clearance Publishing (BDI_PRICING_CLR_TX _JOB)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to target applications. The batch job BDI_PRICING_CLR_TX _JOB is defined in the Merchandising JOS batch job admin to stage clearance data.

The program can be scheduled ad hoc, recurring, and nightly depending on the needs of each retailer. Each run of the program will include changes for approved clearances since the previous run.

Scheduling Constraints

Table 2-2 BDI_PRICING_CLR_TX_JOB Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring, Nightly
Scheduling Considerations	not applicable
Pre-Processing	not applicable
Post-Processing	not applicable
Threading Scheme	not applicable

Restart/Recovery

N/A

Key Tables Affected

Table 2-3 Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_CLR_BDI_HELPER_WS	Yes	No	No	No
RPM_CLEARANCE	Yes	No	No	No
RPM_PRICE_EVENT_PAYLOAD	Yes	No	No	No

Design Assumptions

N/A

Output

BDI extractor jobs call respective BDI functions to extract data from Pricing tables to BDI outbound staging table CLEARANCE_OUT.

Table 2-4 BDI Outbound Staging Table CLEARANCE_OUT

Name	Null	Type	Description
BDI_SEQ_ID	No	NUMBER	BDI Internal Column
BDI_APP_NAME	No	VARCHAR2(50)	BDI Internal Column
BDI_DATASET_TYPE	Yes	VARCHAR2(20)	BDI Internal Column
BDI_DATASET_ACTION	Yes	VARCHAR2(20)	BDI Internal Column
REC_ID	No	NUMBER(10,0)	The ID of the record
RECORD_TYPE	No	VARCHAR2(50)	The record type. Valid values (Create/Update/Delete)
CLEARANCE_ID	No	NUMBER(15,0)	The clearance ID
ITEM	Yes	VARCHAR2(25)	The item ID
LOCATION	Yes	NUMBER(10,0)	The location ID
LOCATION_TYPE	Yes	VARCHAR2(30)	The location Type. Valid values (S ‘Store’ or W ‘Warehouse).
EFFECTIVE_DATE	Yes	TIMESTAMP(2)	Effective date of the clearance
RETAIL	Yes	NUMBER(20,4)	The clearance retail for the item location
UOM	Yes	VARCHAR2(25)	The retail unit of measure
CURRENCY	Yes	VARCHAR2(25)	The currency for the location
RESET_INDICATOR	No	NUMBER(1,0)	Indicates if the clearance event is a reset. Valid values: 0- The record is not a reset; 1 – The record is a reset.

BDI Price Change Publishing (BDI_PRICING_PC_TX_CYCLE_JOB)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to target applications. The batch job BDI_PRICING_PC_TX _JOB is defined in the Merchandising JOS batch job admin to stage clearance data.

The program can be scheduled ad hoc, recurring, and nightly depending on the needs of each retailer. Each run of the program will include changes for approved price changes since the previous run.

Scheduling Constraints

Table 2-5 BDI_PRICING_PC_TX_CYCLE_JOB Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring, Nightly
Scheduling Considerations	not applicable
Pre-Processing	not applicable
Post-Processing	not applicable
Threading Scheme	not applicable

Restart/Recovery

N/A

Key Tables Affected

Table 2-6 Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_PC_BDI_HELPER_WS	Yes	No	No	No
RPM_PRICE_EVENT_PAYLOAD	Yes	No	No	No
RPM_PRICE_CHANGE	Yes	No	No	No

Design Assumptions

N/A

Output

BDI extractor jobs call respective BDI functions to extract data from Pricing tables to BDI outbound staging table PRICE_CHANGE_OUT.

Table 2-7 BDI Outbound Staging Table PRICE_CHANGE_OUT

Name	Null	Type	Description
BDI_SEQ_ID	No	NUMBER	BDI Internal Column
BDI_APP_NAME	No	VARCHAR2(50)	BDI Internal Column
BDI_DATASET_TYPE	Yes	VARCHAR2(20)	BDI Internal Column
BDI_DATASET_ACTION	Yes	VARCHAR2(20)	BDI Internal Column
REC_ID	No	NUMBER(10,0)	The ID of the record
RECORD_TYPE	No	VARCHAR2(50)	The record type. Valid values (Create/Update/Delete)
PRICE_CHANGE_ID	No	NUMBER(15,0)	The price change ID
ITEM	Yes	VARCHAR2(25)	The item ID
LOCATION	Yes	NUMBER(10,0)	The location ID
LOCATION_TYPE	Yes	VARCHAR2(30)	The location Type. Valid values (S ‘Store’ or W ‘Warehouse)
EFFECTIVE_DATE	Yes	TIMESTAMP(2)	Effective date of the price change
RETAIL	Yes	NUMBER(20,4)	The new regular retail for the item location
UOM	Yes	VARCHAR2(25)	The retail unit of measure
CURRENCY	Yes	VARCHAR2(25)	The currency for the location
RETAIL_CHANGE_IND	No	NUMBER(6,0)	Indicates whether the retail changed with this price change

Clearance Cache Batch

Table 2-8 ClearanceCacheBatch Details

Module Name	ClearanceCacheBatch.sh
Description	Build/Update JSON Cache data for Clearances
Functional Area	Clearance
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	ClearanceCacheBatch.sh

Design Overview

The ClearanceCacheBatch pre-builds JSON messages that are used by downstream systems via the item/price Rest Endpoint for Clearance events.

The process will look for data on RPM_ICL_PRICE_EVENT_PAYLOAD and RPM_PRICE_EVENT_PAYLOAD to determine what message structures to build and what flags to set to provide accurate action values when processing a service request.

Scheduling Constraints

Table 2-9 ClearanceCacheBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	The process is threaded based on clearance ids from RPM_CLEARANCE_PAYLOAD.

Restart/Recovery

N/A

Key Tables Affected

Table 2-10 ClearanceCacheBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_CLR_JSON_THREAD_WS	Yes	Yes	No	Yes
RPM_ICL_PRICE_EVENT_PAYLOAD	Yes	No	No	Yes
RPM_PRICE_EVENT_PAYLOAD	Yes	No	Yes	No
RPM_CLEARANCE_PAYLOAD	Yes	No	No	No
RPM_CLR_JSON_CACHE	Yes	Yes	Yes	No

Design Assumptions

N/A

ClearanceInductionBatch (Clearance Induction Batch)

Table 2-11 ClearanceInductionBatch Details

Module Name	ClearanceInductionBatch.sh
Description	Clearance bulk upload process
Functional Area	Clearance
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	ClearanceInductionBatch.sh <incoming-dir-path> <Template_Key> [filter_Str ] The first argument is the path where the induction input files are placed. The second argument is the name of the standard clearance template key. This is a mandatory argument. The third argument is optional and, when provided, accepts the following values: XML - indicates that the batch has to look for xml files in the given incoming-dir-path and process them. ZIP - This is the default value when nothing is provided. The process will look for zip files containing xml files and process them sequentially.

Note:

File naming standards

XML file:

The file should have a prefix of CLIND. Ex: CLIND_ABC-10.10.18.xml

The file should contain the data in the format suggested by standard clearance upload template.

ZIP file:

The file should have a prefix of CLIND. Ex: CLIND_ABC.ZIP

The xml files with in the zip file should also have the prefix of CLIND.

Design Overview

The clearance induction batch process perform the necessary work to upload clearances in bulk. For the bulk upload, clearance data will be present in XML format with the data formatted in the standard clearance upload template. This batch accepts the clearance data present in XML format and also as zip files of xml files formatted in the standard template.

Scheduling Constraints

Table 2-12 ClearanceInductionBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 2-13 ClearanceInductionBatch Key Tables Affected

Table	Select	Insert	Update	Delete
S9T_TEMPLATE	Yes	No	No	No
SVC_PROCESS_TRACKER	Yes	Yes	Yes	No
S9T_ERRORS	Yes	Yes	No	No
RPM_CORESVC_CLEARANCE_ERR	Yes	Yes	No	No
RPM_SVC_CLEARANCE	Yes	Yes	Yes	No
RPM_CLEARANCE	Yes	Yes	Yes	Yes
RPM_CLEARANCE_GROUP	Yes	Yes	Yes	Yes

Design Assumptions

N/A

ClearancePriceChangePublishBatch (Clearance Price Change Publish Batch)

Table 2-14 ClearancePriceChangePublishBatch Details

Module Name	ClearancePriceChangePublishBatch.sh
Description	Clearance events are exported
Functional Area	Clearance
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	ClearancePriceChangePublishBatch.sh <outgoing-dir-path>

Design Overview

The ClearancePriceChangePublishBatch program formats and stages output of clearance price change price events.

The corresponding clearancePriceChangePublishExport shell script produces a pipe ("|") delimited flat-file export based on the output of the ClearancePriceChangePublishBatch.

The batch looks for price events in the RPM_PRICE_EVENT_PAYLOAD table with a MSG_FAMILY of 'ClrPrcChg' and distributes those events to multiple threads based on the settings in the RPM_BATCH_CONTROL table. Each thread reads in its set of clearance price change events from tables RPM_PRICE_EVENT_PAYLOAD and RPM_CLEARANCE_PAYLOAD and generates output in RPM_PRICE_PUBLISH_DATA. After the flat file is generated (see the following format), the associated records in the payload tables are deleted.

Then the flat-files per location based on the data from payload table that need to be published/processed will be created and zipped and copied to the outgoing-directory-path provided as a batch parameter.

Scheduling Constraints

Table 2-15 ClearancePriceChangePublishBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	The ClearancePriceChangePublishBatch program is threaded, using RPM_BATCH_CONTROL. The LUW is a single clearance price change event.

Restart/Recovery

N/A

Key Tables Affected

Table 2-16 ClearancePriceChangePublishBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_PRICE_EVENT_PAYLOAD	Yes	No	No	No
RPM_CLEARANCE_PAYLOAD	Yes	No	No	No

Output File

FHEAD - REQUIRED: File identification, one line per file.

FDETL - OPTIONAL: Price Change Event (Create or Modify).

FDELE - OPTIONAL: Price Change Event (Delete).

FTAIL - REQUIRED: End of file marker, one line per file.

Note:

File naming standards

The naming convention for the flat file will be (CLRPC_<timestamp>_<location>_<loc_type>.dat), where <timestamp> is the current system time stamp, <location> is the location ID and <loc_type> is the type of the location where 'S' is for Store and 'W' is for Warehouse. The zip file naming convention will be (CLRPC_<timestamp>.zip).

Output File Layout

Table 2-17 Output File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	Record Descriptor	Char(5)	FHEAD	File head marker
	Line ID	Number(10)	1	Unique line identification
	File Type	Char(5)	CLRPC	Clearance Price Changes
	Export timestamp	Timestamp		System clock timestamp (YYYYMMDDHHMISS)
	Location	Number(10)		Location identifier
	Location Type	Char(1)		S = Store, W = Warehouse
FDETL	Record Descriptor	Char(5)	FDETL	File Detail Marker (1 per clearance create or modify)
	Line ID	Number(10)		Unique line identification
	Event Type	Char(3)		CRE = Create, MOD = Modify
	Id	Number(15)		Clearance identifier
	Item	Char(25)		Item identifier
	Effective Date	Date		Clearance Effective Date (YYYYMMDDHH24MISS)
	Selling Retail	Number(20,4)		Selling retail with price change applied
	Selling Retail UOM	Char(4)		Selling retail unit of measure
	Selling Retail Currency	Char(3)		Selling retail currency
	Reset Clearance Id	Number(15)		Clearance reset identification
FDELE	Record Descriptor	Char(5)	FDELE	File Detail Delete Marker (1 per clearance delete)
	Line ID	Number(10)		Unique line identification
	Id	Number(15)		Clearance identifier
	Item	Char(25)		Item identifier
FTAIL	Record Descriptor	Char(5)	FTAIL	File tail marker
	Line ID	Number(10)		Unique line identification
	Number of lines	Number(10)		Number of lines in file not counting FHEAD and FTAIL

Design Assumptions

N/A

ClearanceRDEDataBatch

Table 2-18 ClearanceRDEDataBatch Details

Module Name	ClearanceRdeDataBatch.sh
Description	Populates clearance data to be replicate to RDS for RDE to use.
Functional Area	Clearances
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	ClearanceRdeDataBatch.sh

Design Overview

This batch process will use staged clearance payload data and create/update data that is replicated to RDS for use by RDE.

Scheduling Constraints

Table 2-19 ClearanceRDEDataBatch Scheduling Constraints

Schedule Information	Description
Frequency	Hourly Ad hoc
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 2-20 ClearanceRDEDataBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_CLEARANCE_PAYLOAD	Yes	No	No	No

Design Assumptions

N/A

FutureRetailPurgeBatch

Table 2-21 FutureRetailPurgeBatch Details

Module Name	FutureRetailPurgeBatch.sh
Description	Purges future retail data that is past the retention period.
Functional Area	Future Retail
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	futureRetailPurgeBatch.sh

Design Overview

This batch is a timed multi-threaded process that purges future retail data that are past the retention periods of their corresponding price events.

Scheduling Constraints

Table 2-22 FutureRetailPurgeBatch Scheduling Constraints

Schedule Information	Description
Frequency	Nightly
Scheduling Considerations	This process must be executed during the batch window. As it runs, other processes must not access the future retail tables.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	The batch uses bookmark logic to process merchandise hierarchies in a round robin fashion and running for a specific timeframe depending on the value of BATCH_TIME_LIMIT_HOURS in RPM_BATCH_CONTROL.

Restart/Recovery

Restart/Recovery is inherent in the design of this program, as records are deleted after processing they would not be picked up when the program is run again.

Key Tables Affected

Table 2-23 FutureRetailPurgeBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_FUTURE_RETAIL	Yes	No	No	Yes

Design Assumptions

N/A

FutureRetailRollUpBatch (Future Retail Roll Up Batch)

Table 2-24 FutureRetailRollUpBatch Details

Module Name	FutureRetailRollUpBatch.sh
Description	Attempts to roll up timelines on future retail if lower level timelines match higher levels.
Functional Area	Future Retail
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	futureRetailRollUpBatch.sh

Design Overview

This batch attempts to roll up lower level timelines to existing higher level timelines (for example, from Item/Location to Parent/Location) by comparing two related timelines and removing the lower level timelines if the two match exactly for all records.

Scheduling Constraints

Table 2-25 FutureRetailRollUpBatch Scheduling Constraints

Schedule Information	Description
Frequency	Nightly
Scheduling Considerations	This process must be executed during the batch window. As it runs, other processes must not access the future retail tables.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	This batch is threaded by item.

Restart/Recovery

The batch uses bookmark logic to process merchandise hierarchies in a round robin fashion and running for a specific timeframe depending on the value of BATCH_TIME_LIMIT_HOURS in RPM_ BATCH_CONTROL.

Key Tables Affected

Table 2-26 FutureRetailRollUpBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_FUTURE_RETAIL	Yes	No	Yes	No

Design Assumptions

N/A

ItemReclassBatch (Item Reclass Batch)

Table 2-27 ItemReclassBatch Details

Module Name	ItemReclassBatch.sh
Description	Updates Pricing tables when a merchandise hierarchy change is made in RMS.
Functional Area	Future Retail
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	ItemReclassBatch.sh

Design Overview

When items are moved from one department/class/subclass to another in the merchandising system, this batch process accordingly sets the correct department/class/subclass for these items in the Future Retail table and the Pricing Item/Location table if the item has move departments.

This batch also removes the zones of the old primary zone group from zone future retail and item zone price tables, if the item’s old department had a different primary zone group than the new department.

Scheduling Constraints

Table 2-28 ItemReclassBatch Scheduling Constraints

Schedule Information	Description
Frequency	Nightly
Scheduling Considerations	Must be run during the batch window.
Pre-Processing	The RPM_ITEM_MODIFICATION table has been populated by the Merchandising reclassification batch process.
Post-Processing	N/A
Threading Scheme	Batch is multi-threaded based on old department and item_parent. Thread LUW is defined in rpm_batch_control. STYLE and all its SKU’s should be in same thread. One thread can have multiple STYLEs. One old department can be spread across multiple threads if it has more than LUW records. One thread should have only one old department.

Restart/Recovery

N/A

Key Tables Affected

Table 2-29 ItemReclassBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_FUTURE_RETAIL	Yes	No	Yes	No
RPM_ITEM_MODIFICATION	Yes	No	No	No
RPM_ITEM_MODIFICATION_THREAD	Yes	Yes	Yes	Yes
RPM_ZONE_FUTURE_RETAIL	Yes	No	No	Yes
RPM_ITEM_ZONE_PRICE	Yes	No	No	Yes

Design Assumptions

N/A

NewItemLocationBatch (New Item Location Batch)

Table 2-30 NewItemLocationBatch Details

Module Name	NewItemLocationBatch.sh
Description	Updates Pricing tables for new item/locations in RMS
Functional Area	Future Retail
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	NewItemLocationBatch.sh [N / {E <error commit count>} / {R [<process ID>]}] Where The 'status' argument (N/E/R) is optional and directs the application as to what "status" to process. If it's not specified, the batch will default it to 'N'ew mode. The last argument can be optional or required depending upon the status argument as describe in the section below: Valid values for the status argument are: 'N'ew: This will process records with status of N (New) from the staging table. When the batch is run in this mode, the last argument is not needed. 'E'rror: This will process records with status of E (Error) from the staging table. When the batch is run in this mode, the batch can have the error commit count argument as an optional argument. Error commit count is optional and is used only when the status argument is 'E'. If not specified, the batch will use the logical unit of work for processing 'R'estart: When the batch is run in this mode, then the process_id argument is required. This mode will only restart the rolling up functionality that is part of location move. It will call the RPM_NEW_ITEM_LOC_SQL.ROLLUP_NIL_DATA for the threads that are not in completed status in RPM_NIL_ROLLUP_THREAD. A required valid process ID parameter will also need to be passed in as well to indicate what process ID the batch should restart.

Design Overview

The NewItemLocationBatch program ranges item locations by putting them into the future retail table. Item locations are fed to this program via the RPM_ITEM_LOC_WS table, which is populated by an RMS process.

Scheduling Constraints

Table 2-31 NewItemLocationBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc
Scheduling Considerations	Must not have more than one instance running at a time.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	The NewItemLocationBatch is a multi-step and multi-threaded batch, meaning each of the two steps (inheritance process and rollup process) has its own independent threading. The first part, which is the insert to future retail table and inheritance process, is threaded by related item-locations where "related" means transaction items under a single parent items and locations within a zone that is part of a primary zone group. If there are price events, then it chooses a path based on batch control settings similar to the ones for a price event approval from UI, and it chooses to go to chunking or bulking based on setting and the volume of data.

Restart/Recovery

Processing Stage Rows in Error Status

This program is set up to re-process (re-attempt) rows that end up in error status. In the event that an error occurs during the processing of new status rows, the program should update the status on the stage table with E along with an error message. Once the error has been fixed, you can re-run this program with status E in an attempt to get the item/loc into Pricing.

Key Tables Affected

Table 2-32 NewItemLocationBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_FUTURE_RETAIL	Yes	No	Yes	No
RPM_ITEM_LOC	Yes	No	No	No
RPM_STAGE_ITEM_LOC	Yes	No	Yes	No
RPM_STAGE_ITEM_LOC_CLEAN	Yes	No	No	No
RPM_NIL_ROLLUP_THREAD	Yes	No	Yes	No
RPM_NIL_BULKCCPE_PROCESS_ID	Yes	No	No	No

Design Assumptions

N/A

NightlyBatchCleanup (Nightly Cleanup Batch)

Table 2-33 NightlyBatchCleanup Details

Module Name	NightlyBatchCleanup.sh
Description	Nightly clean up on pricing tables
Functional Area	All
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	NightlyBatchCleanupBatch.sh PRE/POST

Design Overview

The nightlyBatchCleanup batch program performs "clean up" logic against certain database structures.

Scheduling Constraints

Table 2-34 NightlyBatchCleanup Scheduling Constraints

Schedule Information	Description
Frequency	Nightly batch cycle
Scheduling Considerations	This batch should be run before the nightly batch window in "pre" mode and after the nightly batch window in "post" mode.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 2-35 NightlyBatchCleanup Key Tables Affected

Table	Select	Insert	Update	Delete
S9T_TEMPLATE	Yes	No	No	No
SVC_PROCESS_TRACKER	Yes	No	No	No
S9T_ERRORS	Yes	Yes	Yes	No
RPM_CORESVC_PRICE_CHANGE_ERR	Yes	No	No	No
RPM_SVC_PRICE_CHANGE	Yes	No	Yes	No
RPM_PRICE_CHANGE	Yes	No	No	No
RPM_PRICE_CHANGE_GROUP	No	Yes	No	No

Design Assumptions

N/A

Price Change Cache Batch

Table 2-36 PriceChangeCacheBatch Details

Module Name	PriceChangeCacheBatch.sh
Description	Build/Update JSON Cache data for Regular Price Changes
Functional Area	Regular Price Changes
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	PriceChangeCacheBatch.sh

Design Overview

The PriceChangeCacheBatch pre-builds JSON messages for that are used by downstream systems via the item/price Rest Endpoint for Price Change events.

Scheduling Constraints

Table 2-37 PriceChangeCacheBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	The process is threaded based on price change ids from RPM_PRICE_CHG_PAYLOAD.

Restart/Recovery

N/A

Key Tables Affected

Table 2-38 PriceChangeCacheBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_PC_JSON_THREAD_WS	Yes	Yes	No	Yes
RPM_ICL_PRICE_EVENT_PAYLOAD	Yes	No	No	Yes
RPM_PRICE_EVENT_PAYLOAD	Yes	No	Yes	No
RPM_PRICE_CHG _PAYLOAD	Yes	No	No	No
RPM_PC_JSON_CACHE	Yes	Yes	Yes	No

Design Assumptions

N/A

PriceChangeInductionBatch (Price Change Induction Batch)

Table 2-39 PriceChangeInductionBatch Details

Module Name	PriceChangeInductionBatch.sh
Description	Price Change bulk upload process
Functional Area	Price Change
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	PriceChangeInductionBatch .sh <incoming-dir-path> <Template_Key> [filter_Str ] The first argument is the path where the induction input files are placed. The second argument is the name of the standard price change template key. This is a mandatory argument. The third argument is an optional and when provided accepts the following values: XML - indicates that the batch has to look for xml files in the given incoming-dir-path and process them. ZIP - This is the default value when nothing is provided. The process will look for zip files containing xml files and process them sequentially.

Note:

File naming standards

XML file:

The file should have a prefix of PCIND. Ex: PCIND_ABC-10.10.18.xml

The file should contain the data in the format suggested by standard price change upload template.

ZIP file:

The file should have a prefix of PCIND. Ex: PCIND_ABC.ZIP

The xml files with in the zip file should also have the prefix of PCIND

Design Overview

PriceChangeInductionBatch uploads regular price changes in bulk. For the bulk upload, price change data will be present in XML format with the data formatted in the standard price change upload template. This batch accepts the price change data present in XML format and also as zip files of xml files formatted in the standard template.

Scheduling Constraints

Table 2-40 PriceChangeInductionBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 2-41 PriceChangeInductionBatch Key Tables Affected

Table	Select	Insert	Update	Delete
S9T_TEMPLATE	Yes	No	No	No
SVC_PROCESS_TRACKER	Yes	Yes	Yes	No
S9T_ERRORS	Yes	Yes	No	No
RPM_CORESVC_PRICE_CHANGE_ERR	Yes	No	No	No
RPM_SVC_PRICE_CHANGE	Yes	No	Yes	No
RPM_PRICE_CHANGE	Yes	No	No	No
RPM_PRICE_CHANGE_GROUP	No	Yes	No	No

Design Assumptions

N/A

PriceEventExecutionBatch (Price Event Execution Batch)

Table 2-42 PriceEventExecutionBatch Details

Module Name	PriceEventExecution.sh
Description	Starts events that need to be executed on a given date.
Functional Area	Price Change
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	PriceEventExecutionBatch.sh [restartInd Y\|N] Where the last argument of the PriceEventExecutionBatch indicates if the run should start over (use a value of N) or pick up where the previous run left off (use a value of Y).

Design Overview

The price event execution batch process performs the necessary work to start (regular price change and clearance price change) and end (reset) clearance pricing events.

The batch programs process regular price change and clearance price change events that are scheduled for the run date. Restartability features allow events missed in past runs of the batch to be picked up in later runs. When posting information in the ITEM_LOC and PRICE_HIST table, the batch process respects the active dates of their associated price events.

Clearances

Clearance markdowns that are scheduled to take place are executed. These include all clearances whose effective dates are <= VDATE+1.
Clearances that are scheduled to be completed (reset) are completed.

Regular price changes

Regular price changes that are scheduled to take place are executed. These include all price changes whose effective dates are <= VDATE+1.

Scheduling Constraints

Table 2-43 PriceEventExecutionBatch Scheduling Constraints

Schedule Information	Description
Frequency	Nightly
Scheduling Considerations	Salstage (Merchandising) should run before Price Event Execution.Price Event Execution should run before the Storadd (Merchandising) batch.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

The program is restartable and will pick up any events remaining to be processed in a given run.

Key Tables Affected

Table 2-44 PriceEventExecutionBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_PRICE_CHANGE	Yes	No	Yes	No
RPM_CLEARANCE	Yes	No	Yes	No
ITEM_LOC	Yes	No	Yes	No
PRICE_HIST	Yes	Yes	No	No
TRAN_DATA	Yes	Yes	No	No

Design Assumptions

N/A

PriceEventExecutionForChunkCCEmergencyEvents Batch

The priceEventExecutionForChunkCCEmergencyEvents process performs the necessary work to start and end Emergency Price Events that are processed through chunk conflict checking. For more details on chunk conflict checking, see the ProcessPendingChunkBatch batch design.

Usage

The following command runs the priceEventExecutionForChunkCCEmergencyEvents job:

priceEventExecutionForChunkCCEmergencyEvents.sh

Detail

The priceEventExecutionForChunkCCEmergencyEvents batch program process Emergency regular price changes and clearance price changes that are scheduled to run on vdate or less than vdate. All processes for the emergency batch are similar to the priceEventExecutionBatch in the above that it is also update Merchandising tables in one batch. It is calling the same function with different parameter so that the system knows to process an Emergency Price Events or Non-Emergency Price Events.

Primary Tables Involved

RPM_CHUNK_CC_STAGE_PEE
RPM_PRICE_CHANGE
RPM_CLEARANCE

Threading

The threading for priceEventExecutionForChunkCCEmergencyEvents batch is similar to the priceEventExecutionBatch. It utilizes concurrent processing and is threaded by a number of item-locations affected by the pricing events to be executed. For more detail, please see the PriceEventExecutionBatch process in the Threading section.

ProcessPendingChunkBatch Batch

Pricing can utilize chunk processing for conflict checking of price events, to optimize performance for large events.

To determine if a price event should be processed through chunking, there is a row on the RPM_BATCH_CONTROL table with PROGRAM_NAME column equal to "Bulk Conflict Check App Service".

The value of the THREAD_LUW_COUNT column of a row on the RPM_BATCH_CONTROL table with PROGRAM_NAME column equal to "Bulk Conflict Check App Service".

The conflict checking process utilizes chunking if the number of item/locations for a price event is greater than or equal to the THREAD_LUW_COUNT times a factor of 2.5.

Pricing then uses the value of the THREAD_LUW_COUNT column of a row on the RPM_BATCH_CONTROL table with PROGRAM_NAME column equal to "Bulk Conflict Check App Service" as the chunk size. If this is NULL, the chunk size will be 10,000.

Note:

This can be best illustrated by considering a price change set up for a style with 10,000 items and a zone with five locations.

The above mentioned bulk conflict checking process would be able to have only one thread that could process all 50,000 item/locations involved with one price event as suggested above. By chunking those 50,000 item/locations into smaller groupings, multiple threads can be utilized to execute the conflict checking process.

The ProcessPendingChunkBatch program attempts to reprocess push-back logic for threads that encountered errors in chunk conflict checking.

Usage

The following command runs the ProcessPendingChunkBatch job:

ProcessPendingChunkBatch.sh

Details

The batch looks for any push-back threads that completed with error and any price events that encountered errors in the post-push-back logic and attempts to reprocess them using the same logic that is used during the regular conflict checking processing.

Assumptions and Scheduling Notes

The ProcessPendingChunkBatch process can be run ad-hoc - the event of a price event moving to a pending status triggering the need to run this batch. Prior to running this batch, a DBA needs to verify what issues were encountered that caused a price event to be moved to a pending status (issues like unable to extend table space). These issues need to be rectified prior to running this batch. If no action is taken to resolve these issues, the batch will likely encounter the same issues and the price events will be left in a pending status.

Primary Tables Involved

RPM_BULK_CC_PE_CHUNK
RPM_BULK_CC_PE_THREAD
RPM_FUTURE_RETAIL_WS
RPM_FUTURE_RETAIL

Threading

The ProcessPendingChunkBatch program is threaded in that it will reuse the same threading logic used by the conflict checking engine when attempting to reprocess push-back threads.

Promotion Cache Batch

Table 2-45 PromotionCacheBatch Details

Module Name	PromotionCacheBatch.sh
Description	Build/Update JSON Cache data for Promotions
Functional Area	Promotions
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	PromotionCacheBatch.sh

Design Overview

The PromotionCacheBatch pre-builds JSON messages for that are used by downstream systems via the item/promo Rest Endpoint for Promotion events.

Scheduling Constraints

Table 2-46 PromotionCacheBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	The process is threaded based on offer ids from RPM_PROMO_OFFER_PAYLOAD.

Key Tables Affected

Table 2-47 PromotionCacheBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_PROMO_JSON_THREAD_WS	Yes	Yes	No	Yes
RPM_ICL_PRICE_EVENT_PAYLOAD	Yes	No	No	Yes
RPM_PRICE_EVENT_PAYLOAD	Yes	No	Yes	No
RPM_PROMO_OFFER _PAYLOAD	Yes	No	No	No
RPM_PROMO_JSON_CACHE	Yes	Yes	Yes	No

Design Assumptions

N/A

PromotionInductionBatch (Promotion Induction Batch)

Table 2-48 PromotionInductionBatch Details

Module Name	PromotionInductionBatch.sh
Description	Promotion bulk upload process
Functional Area	Promotion
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	PromotionInductionBatch.sh <incoming-dir-path> <Template_Key> [filter_Str] The first argument is the path where the induction input file(s) are located. It can be a file or a directory. The second argument is the name of a promotion template key. This is a mandatory argument. The third argument is an optional and when provided accepts the following values: XML - indicates that the batch has to look for xml files in the given incoming-directory-path and process them. ZIP - This is the default value when nothing is provided. The process will look for zip files containing xml files and process them.

Note:

File naming standards

XML file:

The files should have a prefix of PMIND. Files without this prefix are ignored. Ex: PMIND_ABC-10.10.18.xml

The file should contain the data in the format suggested by the given promotion upload template key argument.

ZIP file:

The file should have a prefix of PMIND. Files without this prefix are ignored. Ex: PMIND_ABC.zip

The xml files with in the zip file should also have the prefix of PMIND otherwise they will be ignored.

Design Overview

The promotion induction batch process perform the necessary work to upload promotions in bulk. For the bulk upload, promotion data will be present in XML format with the data formatted in a promotion upload template matching the given template key argument. This batch accepts the promotion data present in XML format and also as zip files of xml files.

Scheduling Constraints

Table 2-49 PromotionInductionBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Key Tables Affected

Table 2-50 PromotionInductionBatch Key Tables Affected

Table	Select	Insert	Update	Delete
S9T_TEMPLATE	Yes	No	No	No
SVC_PROCESS_TRACKER	Yes	Yes	Yes	No
S9T_ERRORS	Yes	Yes	No	No
RPM_CORESVC_PROMO_ERR	Yes	Yes	No	No
RPM_SVC_PROMO	Yes	Yes	Yes	No
RPM_SVC_PROMO_OFFER	Yes	Yes	Yes	No
RPM_SVC_PROMO_OFFER_COND	Yes	Yes	Yes	No
RPM_SVC_PROMO_OFFER_COND_MERCH	Yes	Yes	Yes	No
RPM_SVC_PROMO_OFFER_RWD	Yes	Yes	Yes	No
RPM_SVC_PROMO_OFFER_RWD_MERCH	Yes	Yes	Yes	No
RPM_SVC_PROMO_OFFER_ZONE_LOC	Yes	Yes	Yes	No
RPM_PROMO	Yes	Yes	Yes	Yes
RPM_PROMO_OFFER	Yes	Yes	Yes	Yes
RPM_PROMO_OFFER_COND	Yes	Yes	Yes	Yes
RPM_PROMO_OFFER_COND_MERCH	Yes	Yes	Yes	Yes
RPM_PROMO_OFFER_REWARD	Yes	Yes	Yes	Yes
RPM_PROMO_OFFER_REWARD_MERCH	Yes	Yes	Yes	Yes
RPM_PROMO_OFFER_ZONE_LOC	Yes	Yes	Yes	Yes

Promotion Publishing (BDI_PRICING_PROMO_OFFER_TX_CYCLE_JOB)

This program utilizes BDI (Bulk Data Integration) to facilitate the bulk data movement to target applications. The batch job BDI_PRICING_PROMO_OFFER_TX_CYCLE_JOB is defined in the Merchandising JOS batch job admin to stage promotions data.

The program can be scheduled ad hoc, recurring, and nightly depending on the needs of each retailer. Each run of the program will include changes for approved price changes since the previous run.

Scheduling Constraints

Table 2-51 BDI_PRICING_CLR_TX_JOB Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring, Nightly
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Promotions Integration

The Pricing Cloud Service will communicate promotional offers at a rule criteria level instead of the item/location level, where applicable. Offers in an approved or active state and candidates for integration when edited.

The table below contains examples of message types that are created when certain activities are triggered by the user:

Table 2-52 Promotions Integration Examples

Message Type	Activities in Promotion UI
OfferCreate	This message type is generated when the user changes the state of an Offer to Approved state.
OfferDelete	This message type is generated when the following action happen: The user changes the state of an Offer to Delete state The user deletes an Offer
OfferHeaderUpdate	This message type is generated when the user updates the Promotion Offer Header information that is already in Approved state, for example, changing the start date, end date, comments, or coupon code of the promotion.
OfferCondUpdate	This message type is generated when the user updates the Condition information of an Offer, for example, changing the spending type or the spending value.
OfferRwdUpdate	This message type is generated when the user updates the Reward information of an Offer, for example, the reward value (change_type, change_amount, change_percent, etc.) of a promotion.
OfferRwdMerchAdd	This message type is generated when the user adds merchandise hierarchy to a promotion Reward list.
OfferCondMerchAdd	This message type is generated when the user adds merchandise hierarchy to a promotion Condition list.
OfferCondMerchDel	This message type is generated when the user deletes merchandise hierarchy from Promotion Condition list.
OfferRwdMerchDel	This message type is generated when the user deletes merchandise hierarchy from Promotion Reward list.
OfferLocAdd	This message type is generated when the user adds a location to a promotion.
OfferLocDel	This message type is generated when the user deletes a location to a promotion.
OfferCancel	This message type is generated when the user cancel a Promotion Offer.
OfferCondMerchCancel	This message type is generated when the user cancel a merchandise hierarchy from the Condition List of Promotion Offer.
OfferRwdMerchCancel	This message type is generated when the user cancel a merchandise hierarchy from the Reward List of Promotion Offer.
OfferLocCancel	This message type is generated when the user cancel a location of Promotion Offer.

Payload Tables

The table below lists Payload tables that are used to hold staged data for BDI population:

Table 2-53 Payload Tables that Hold Staged Data for BDI population

Table	Description
RPM_PRICE_EVENT_PAYLOAD	Message header level data - shared with PC and CLR Payload data. For promotion offers, the RIB_TYPE field will hold the same values as what is staged in the RPM_PROMO_OFFER_PUB_WS.PUB_TYPE_CODE table.
RPM_PROMO_OFFER_PUB_WS.PUB_TYPE_CODE	This table is populated for all promotion offer messages.
RPM_PROMO_OFFER_PAYLOAD	Offer header level data. This table is populated for all promotion offer messages.
RPM_PROMO_OFR_CND_MRCH_PAYLOAD	This table holds the payload information of the merchandise nodes associated with a promotion offer condition. This table is only populated when the condition for an approved offer has new merchandise data added to it or deleted from it.
RPM_PROMO_OFR_RWD_MRCH_PAYLOAD	This table holds the payload information of the merchandise nodes associated with a promotion offer reward. This table is only populated when the reward for an approved offer has new merchandise data added to it or deleted from it.
RPM_PROMO_OFFER_LOC_PAYLOAD	This table holds the payload information of the location nodes associated with a promotion offer. Only store data will be on this table - any zones associated with an offer will be exploded out to store level. This table is only populated when an approved offer has new zone/loc data added to it or deleted from it.
RPM_PROM_OFR_CNCL_ITEM_PAYLOAD	This table holds the payload information for items cancelled from active promotion offers. This table is only populated for active offers when merchandise is cancelled from a reward or condition.
RPM_PROM_OFR_CNCL_LOC_PAYLOAD	This table holds the payload information for locations cancelled from active promotion offers.
RPM_PROMO_OFFER_CANCEL_PAYLOAD	This table holds the payload information for when active promotion offers are cancelled as a whole.

Payload Population Logic

In many situations, the payload population logic will only populate a small piece of data about an offer (the action along with the promotion and offer ids). In these situations, the BDI extraction logic will use the promotion offer operational tables as the main source of data to stage in the BDI tables rather than the payload tables. In all scenarios, the RPM_PRICE_EVENT_PAYLOAD and RPM_PROMO_OFFER_PAYLOAD tables will be utilized.

BDI data population retrieves the data from both the Payload tables and also Promotion Operational tables depending on the message type.

In order to populate the data into BDI tables more efficiently, a workspace tables called RPM_PROMO_BDI_HELPER_WS is used. This workspace table will be inserted with the data from the two main payload tables that drives the whole processes, rpm_price_event_payload and rpm_promo_offer_payload tables.

Once the workspace table is populated, the system will perform some cleanup activities so that if the same promotions are being updated more than once, it will only write one Update message with the latest information into BDI tables. If the Promotion is being created and then Deleted, it will not get written into BDI tables at all. In order to avoid multiple update messages under the same promotion as mentioned previously, the system will retrieve the data from the Promotion Operational tables itself in order to get the latest data set of a promotion. The only time that payload tables records any activities is when a Promotion is deleted from the system, any merchandise hierarchy is deleted from a Promotion Condition or Reward, location is deleted from promotion and Canceling Promotion. Once the cleanup processed is done, the data will be inserted into BDI tables.

The table below maps which message structures use which payload or operational tables to build the BDI data:

Table 2-54 Message Structures to Build the BDI data

Message Type	Source Tables	Target Tables
OfferCreate	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER, RPM_PROMO_OFFER_COND, RPM_PROMO_OFFER_COND_MERCH, RPM_PROMO_OFFER_REWARD, RPM_PROMO_OFFER_REWARD_MERCH, RPM_PROMO_OFFER_ZONE_LOC	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFFER_OUT, PROMO_OFFER_COND_OUT, PROM_OFR_CND_MRCH_OUT, PROMO_OFR_REWARD_OUT, PROM_OFR_RWD_MRCH_OUT, PROMO_OFFER_LOC_OUT
OfferDelete	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFFER_OUT
OfferUpdate	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFFER_OUT
OfferCondUpdate	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER_COND	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFFER_COND_OUT
OfferCondMerchAdd	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER_COND_MERCH	PRC_PAYLD_MSG_HDR_OUT, PROM_OFR_CND_MRCH_OUT
OfferRwdUpdate	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER_REWARD	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFR_REWARD_OUT
OfferRwdMerchAdd	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER_REWARD_MERCH	PRC_PAYLD_MSG_HDR_OUT, PROM_OFR_RWD_MRCH_OUT
OfferCondMerchDel	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFR_CND_MRCH_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROM_OFR_CND_MRCH_OUT
OfferRwdMerchDel	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFR_CND_MRCH_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROM_OFR_RWD_MRCH_OUT
OfferLocAdd	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER_LOC_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFFER_LOC_OUT
OfferLocDel	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER_LOC_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFFER_LOC_OUT
OfferCancel	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROMO_OFFER_CANCEL_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROMO_OFR_CANCEL_OUT
OfferCondMerchCancel	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROM_OFR_CNCL_ITEM_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROM_OFR_CNCL_ITM_OUT
OfferRwdMerchCancel	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROM_OFR_CNCL_ITEM_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROM_OFR_CNCL_ITM_OUT
OfferLocCancel	RPM_PRICE_EVENT_PAYLOAD, RPM_PROMO_OFFER_PAYLOAD, RPM_PROM_OFR_CNCL_LOC_PAYLOAD	PRC_PAYLD_MSG_HDR_OUT, PROM_OFR_CNCL_LOC_OUT

BDI Tables

The message structure in the BDI tables will that even though there is a hierarchical relationship between tables, each table will not reference an ID for the entity above it. Rather, each full message will be identified by a unique ID that is stored on all tables - the PAYLOAD_ID column on each BDI table. Thus, for an OfferCreate message, each of the seven BDI tables populated will have data with the same PAYLOAD_ID value and the consuming system will need to understand the structure of the tables in relation to each other for that specific message type.

The table below lists the each BDI table and its parent table from a high level perspective:

Table 2-55 BDI Tables

BDI Table	Parent Table
PRC_PAYLD_MSG_HDR_OUT	none
PROMO_OFFER_OUT	PRC_PAYLD_MSG_HDR_OUT
PROMO_OFFER_COND_OUT	PROMO_OFFER_OUT
PROMO_OFR_REWARD_OUT	PROMO_OFFER_OUT
PROM_OFR_CND_MRCH_OUT	PROMO_OFFER_COND_OUT
PROM_OFR_RWD_MRCH_OUT	PROMO_OFR_REWARD_OUT
PROMO_OFFER_LOC_OUT	PROMO_OFFER_OUT
PROMO_OFR_CANCEL_OUT	PRC_PAYLD_MSG_HDR_OUT
PROM_OFR_CNCL_ITM_OUT	PRC_PAYLD_MSG_HDR_OUT
PROM_OFR_CNCL_LOC_OUT	PRC_PAYLD_MSG_HDR_OUT

Key Tables Affected

Table 2-56 BDI Key Tables Affected

Table	Select	Insert	Update	Delete
PRC_PAYLD_MSG_HDR_OUT	Yes	No	No	No
PROMO_OFFER_OUT	Yes	Yes	No	No
PROMO_OFFER_COND_OUT	Yes	No	No	No
PROMO_OFR_REWARD_OUT	Yes	No	No	No
PRC_PAYLD_MSG_HDR_OUT	Yes	No	No	No
PROMO_OFFER_COND_OUT	No	Yes	No	No
PROMO_OFR_REWARD_OUT	No	Yes	No	No
PROM_OFR_CND_MRCH_OUT	No	Yes	No	No
PROM_OFR_RWD_MRCH_OUT	No	Yes	No	No
PROMO_OFFER_LOC_OUT	No	Yes	No	No
PROMO_OFR_CANCEL_OUT	No	Yes	No	No
PROM_OFR_CNCL_ITM_OUT	No	Yes	No	No
PROM_OFR_CNCL_LOC_OUT	No	Yes	No	No

Output

BDI extractor jobs call respective BDI functions to extract data from Pricing tables to BDI outbound staging tables which mirror the structure of the Pricing promotions tables.

PRC_PAYLD_MSG_HDR_OUT

Table 2-57 PRC_PAYLD_MSG_HDR_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PRC_PAYLD_MSG_HDR_ID	NUMBER(10)	No	The unique payload ID for the message.
MESSAGE_TYPE	VARCHAR2(50)	No	The type of message associated to the payload_id.

PROMO_OFFER_OUT

Table 2-58 PROMO_OFFER_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROMO_OFFER_ID	NUMBER(10)	No	The payload ID of the promotion offer.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
OFFER_DESC	VARCHAR2(1000)	Yes	Offer description.
OFFER_CUST_DESC	VARCHAR2(1000)	Yes	The customer description of the offer.
LEVEL_CODE	NUMBER(2)	Yes	The level of the offer. Valid values are: 0 - Item, 1 - Transaction.
TYPE_CODE	NUMBER(2)	Yes	The type of the offer. Valid values are: 0 Item Simple, 1 Transaction Simple, 2 - Transaction Buy Get
TEMPLATE_ID	NUMBER(2)	Yes	The template of the offer. Valid values are: 0 - Get Discount, 1 - Buy X Get Discount, 2 - Spend X Get Discount, 4 - Get Y For Discount
START_DATE	TIMESTAMP(6)	Yes	The start date and time of the offer.
END_DATE	TIMESTAMP(6)	Yes	The end date and time of the offer.
COMMENTS	VARCHAR2(4000)	Yes	The comments for the offer.
COUPON_CODE	VARCHAR2(160)	Yes	The coupon code for the offer.
COUPON_CODE_REQ_IND	NUMBER(1)	Yes	Indicates if the coupon code is required.
DISTRIB_RULE_CODE	VARCHAR2(6)	Yes	The distribution rule code. Possible values are under CODE_TYPE 'OFDR'.
EXCLUSIVE_DISCOUNT_IND	NUMBER(1)	Yes	Indicates whether or not this offer is exclusive of other discounts.
GENERATED_BY	VARCHAR2(1)	Yes	The creator of the promotion. Possible values are under CODE_TYPE ‘PRCT’, S - Supplier and R - Retailer.
PRIMARY_SUPPLIER	NUMBER(10)	Yes	The primary supplier for the item/locations.
PURCHASE_TYPE	NUMBER(1)	Yes	The purchase type. Possible values are under CODE_TYPE ‘PRTP’, 0 - Owned, 1 - Consignment and 2 - Concession.

PROMO_OFFER_COND_OUT

Table 2-59 PROMO_OFFER_COND_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROMO_OFFER_COND_ID	NUMBER(10)	No	The payload ID of the condition of a promotion offer.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
COND_ID	NUMBER(15)	No	Condition ID.
BUY_SPEND_TYPE	NUMBER(1)	No	The buy spend type of the condition. Valid values are: 0 - Quantity, 1 - Amount
BUY_SPEND_VALUE	NUMBER(20, 4)	No	The buy spend value of the condition.
BUY_UOM	VARCHAR2(4)	Yes	The buy UOM of the condition.

PROMO_OFR_REWARD_OUT

Table 2-60 PROMO_OFR_REWARD_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROMO_OFR_REWARD_ID	NUMBER(10)	No	The payload ID of the reward of a promotion offer.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
REWARD_ID	NUMBER(15)	No	Reward ID.
CHANGE_TYPE	NUMBER(1)	No	Type of change for the reward. Valid values: change by amount (1), change by percent (0), fixed price (2)
CHANGE_AMOUNT	NUMBER(20,4)	Yes	The change by amount or fixed price amount.
CHANGE_PERCENT	NUMBER(7,4)	Yes	Percentage value when change type is change by percent.
QTY_TO_DISC	NUMBER(7,4)	Yes	The quantity to discount.
QTY_TO_DISC_UOM	VARCHAR2(4)	Yes	UOM of the discount quantity.
APPLY_TO_IND	NUMBER(1)	No	The apply to indicator of the reward. Valid values: Regular only - 0; Clearance only - 1; Regular and Clearance - 2

PROM_OFR_CND_MRCH_OUT

Table 2-61 PROM_OFR_CND_MRCH_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROM_OFR_CND_MRCH_ID	NUMBER(10)	No	The payload ID of the condition of a promotion offer.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
COND_ID	NUMBER(15)	No	Condition ID.
MERCH_LVL	NUMBER(2)	No	The merchandise level. Valid values are: 1 - Department; 2 - Class; 3 - Subclass; 4 - Parent Item; 5 - Parent/Diff Item; 6 - Transaction Item; 8 - All Departments; 11 - Supplier and/or Brand. Note - Supplier and/or Brand data can be included for Department, Class, or Subclass levels.
DEPT	NUMBER(4)	Yes	Department ID.
CLASS	NUMBER(4)	Yes	Class ID.
CLASS_ID	NUMBER(10)	Yes	The unique class ID value.
SUBCLASS	NUMBER(4)	Yes	Subclass ID.
SUBCLASS_ID	NUMBER(10)	Yes	The unique subclass ID value.
ITEM	VARCHAR2(25)	Yes	Item.
DIFF_ID	VARCHAR2(10)	Yes	Differentiator ID.
SUPPLIER_SITE	NUMBER(10)	Yes	Supplier Site ID
BRAND_NAME	VARCHAR2(30)	Yes	Brand Name
EXCLUDE_IND	NUMBER(1)	No	The exclude indicator.

PROM_OFR_RWD_MRCH_OUT

Table 2-62 PROM_OFR_RWD_MRCH_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	No	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	No	bdi internal column
PROM_OFR_RWD_MRCH_ID	NUMBER(10)	No	The payload ID of the merchandise node associated with the promotion offer reward.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
REWARD_ID	NUMBER(15)	No	Reward ID.
OFFER_REWARD_MERCH_ID	NUMBER(15)	No	The unique id for the offer reward merch record.
MERCH_LVL	NUMBER(2)	No	The merchandise level. Valid values are: 1 - Department; 2 - Class; 3 - Subclass; 4 - Parent Item; 5 - Parent/Diff Item; 6 - Transaction Item; 8 - All Departments; 11 - Supplier and/or Brand. Note - Supplier and/or Brand data can be included for Department, Class, or Subclass levels.
DEPT	NUMBER(4)	Yes	Department ID.
CLASS	NUMBER(4)	Yes	Class ID.
CLASS_ID	NUMBER(10)	Yes	The unique class ID value.
SUBCLASS	NUMBER(4)	Yes	Subclass ID.
SUBCLASS_ID	NUMBER(10)	Yes	The unique subclass ID value.
ITEM	VARCHAR2(25)	Yes	Item.
DIFF_ID	VARCHAR2(10)	Yes	Differentiator ID.
SUPPLIER_SITE	NUMBER(10)	Yes	Supplier Site ID
BRAND_NAME	VARCHAR2(30)	Yes	Brand Name
EXCLUDE_IND	NUMBER(1)	No	The exclude indicator.

PROMO_OFFER_LOC_OUT

Table 2-63 PROMO_OFFER_LOC_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROMO_OFFER_LOC_ID	NUMBER(10)	No	The payload ID of the location node associated with the promotion offer.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
LOCATION	NUMBER(10)	Yes	Location for the offer.
EXCLUDE_IND	NUMBER(1)	No	The exclude indicator.

PROMO_OFR_CANCEL_OUT

Table 2-64 PROMO_OFR_CANCEL_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROMO_OFR_CANCEL_ID	NUMBER(10)	No	The payload ID of the offer cancellation.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
CANCEL_DATETIME	TIMESTAMP(6)	No	The date and time that the offer cancellation takes effect.

PROM_OFR_CNCL_ITM_OUT

Table 2-65 PROM_OFR_CNCL_ITM_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROM_OFR_CNCL_ITM_ID	NUMBER(10)	No	The payload ID of the item cancellation from the offer.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
REWARD_COND_IND	VARCHAR2(1)	No	Indicates if the merch is cancelled from a condition or a reward. Valid values: C - Condition; R - Reward
COND_ID	NUMBER(15)	Yes	Condition ID.
REWARD_ID	NUMBER(15)	Yes	Reward ID.
MERCH_LVL	NUMBER(2)	No	The merchandise level. Valid values are: 1 - Department; 2 - Class; 3 - Subclass; 4 - Parent Item; 5 - Parent/Diff Item; 6 - Transaction Item; 8 - All Departments; 11 - Supplier and/or Brand. Note - Supplier and/or Brand data can be included for Department, Class, or Subclass levels.
DEPT	NUMBER(4)	Yes	Department ID.
CLASS	NUMBER(4)	Yes	Class ID.
CLASS_ID	NUMBER(10)	Yes	The unique class ID value.
SUBCLASS	NUMBER(4)	Yes	Subclass ID.
SUBCLASS_ID	NUMBER(10)	Yes	The unique subclass ID value.
ITEM	VARCHAR2(25)	Yes	Item.
DIFF_ID	VARCHAR2(10)	Yes	Differentiator ID.
CANCEL_DATETIME	TIMESTAMP(6)	No	The date and time that the offer cancellation takes effect.
SUPPLIER_SITE	NUMBER(10)	Yes	Supplier Site ID
BRAND_NAME	VARCHAR2(30)	Yes	Brand Name

PROM_OFR_CNCL_LOC_OUT

Table 2-66 PROM_OFR_CNCL_LOC_OUT

COLUMN	TYPE	NULLABLE	COMMENT
BDI_SEQ_ID	NUMBER	No	bdi internal column
BDI_APP_NAME	VARCHAR2(50)	No	bdi internal column
BDI_DATASET_TYPE	VARCHAR2(20)	Yes	bdi internal column
BDI_DATASET_ACTION	VARCHAR2(20)	Yes	bdi internal column
PROM_OFR_CNCL_LOC_ID	NUMBER(10)	No	The payload ID of the location cancellation from the offer.
PAYLOAD_ID	NUMBER(10)	No	The message payload ID.
PROMO_ID	NUMBER(10)	No	The promo ID.
OFFER_ID	NUMBER(10)	No	The offer ID.
LOCATION	NUMBER(10)	Yes	Location cancelled from the offer.
CANCEL_DATETIME	TIMESTAMP(6)	No	The date and time that the offer cancellation takes effect.

Purge Batch (PurgeBatch)

Here are the steps in the purge process:

Delete price changes which are in worksheet, rejected, or submitted status with an effective date beyond the reject hold days.
Remove price changes with effective dates beyond the price change history months retention setting. This is completed by dropping partitions which meet the date criteria.
Delete clearances which are in worksheet, rejected, or submitted status with an effective date beyond the reject hold days.
Remove clearances with effective dates beyond the clearance history months retention setting. This is completed by dropping partitions which meet the date criteria.
Delete all but the most recent zone future retail entry with an effective date before vdate.
Delete price change induction data for successful upload processes and those with an action date beyond the process retention days.
Delete clearance induction data for successful upload processes and those with an action date beyond the process retention days.
Truncate all the Bulk CC processing tables.
Truncate all UI, item creation, and item/location ranging workspace tables.

System Options

System options used for purge configuration:

RPM_PURGE_CONFIG_OPTIONS.PRICE_EVENTS_REJECT_HOLD_DAYS
RPM_PURGE_CONFIG_OPTIONS.PRICE_CHANGE_HIST_MONTHS
RPM_PURGE_CONFIG_OPTIONS.CLEARANCE_HIST_MONTHS
SYSTEM_OPTIONS.PROC_DATA_RETENTION_DAYS

Usage

PurgeBatch.sh

Scheduling Constraints

Table 2-67 PurgeBatch Scheduling Constraints

Schedule Information	Description
Frequency	Nightly
Scheduling Considerations	not applicable
Pre-Processing	not applicable
Post-Processing	not applicable
Threading Scheme	not applicable

Restart/Recovery

N/A

Key Tables Affected

Table 2-68 PurgeBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_ZONE_FUTURE_RETAIL	Yes	No	No	Yes
RPM_ZONE_LOCATION	Yes	No	No	Yes
RPM_ITEM_ZONE_PRICE	Yes	No	No	Yes
RPM_PRICE_CHANGE	Yes	No	No	Yes
RPM_CLEARANCE	Yes	No	No	Yes
RPM_PRICE_CHANGE	Yes	No	No	Yes
RPM_PRICE_CHANGE_GROUP	Yes	No	No	Yes
RPM_CLEARANCE	Yes	No	No	Yes
RPM_CLEARANCE_GROUP	Yes	No	No	Yes
RPM_ZONE_FUTURE_RETAIL	Yes	No	No	Yes
SVC_PROCESS_TRACKER	Yes	No	No	Yes
S9T_FOLDER	Yes	No	No	Yes
S9T_ERRORS	Yes	No	No	Yes
RPM_SVC_PRICE_CHANGE	Yes	No	No	Yes
RPM_CORESVC_PRICE_CHANGE_ERR	Yes	No	No	Yes
SVC_PROCESS_TRACKER	Yes	No	No	Yes
S9T_FOLDER	Yes	No	No	Yes
S9T_ERRORS	Yes	No	No	Yes
RPM_SVC_CLEARANCE	Yes	No	No	Yes
RPM_CORESVC_CLEARANCE_ERR	Yes	No	No	Yes
RPM_BULK_CC_PE_ITEM	Yes	No	No	Yes
RPM_BULK_CC_PE_LOCATION	Yes	No	No	Yes
RPM_BULK_CC_PE_CHUNK	Yes	No	No	Yes
RPM_BULK_CC_PE_THREAD	Yes	No	No	Yes
RPM_BULK_CC_PE_SEQUENCE	Yes	No	No	Yes
RPM_BULK_CC_PE	Yes	No	No	Yes
RPM_PE_CC_LOCK	Yes	No	No	Yes
RPM_CONFLICT_CHECK_RESULT	Yes	No	No	Yes
RPM_PC_MAINT_LOC_WS	Yes	No	No	Yes
RPM_PC_MAINT_ITEM_WS	Yes	No	No	Yes
RPM_PC_MAINT_WS	Yes	No	No	Yes
RPM_PC_GROUP_SEARCH_WS	Yes	No	No	Yes
RPM_CLR_MAINT_LOC_WS	Yes	No	No	Yes
RPM_CLR_MAINT_ITEM_WS	Yes	No	No	Yes
RPM_CLR_MAINT_WS	Yes	No	No	Yes
RPM_CLR_GROUP_SEARCH_WS	Yes	No	No	Yes
RPM_OFFER_ZONE_LOC_WS	Yes	No	No	Yes
RPM_OFFER_CON_RWD_MERCH_WS	Yes	No	No	Yes
RPM_OFFER_REWARD_WS	Yes	No	No	Yes
RPM_OFFER_COND_WS	Yes	No	No	Yes
RPM_OFFER_WS	Yes	No	No	Yes
RPM_PROMO_WS	Yes	No	No	Yes
RPM_PROMO_OFFER_SEARCH_WS	Yes	No	No	Yes
RPM_PROMO_CANCEL_MERCH_WS	Yes	No	No	Yes
RPM_PROMO_CANCEL_ZONE_NODE_WS	Yes	No	No	Yes
RPM_PROMO_OFFER_PUB_WS	Yes	No	No	Yes
RPM_PE_CREATE_ITEM_WS	Yes	No	No	Yes
RPM_PE_CREATE_LOC_WS	Yes	No	No	Yes
RPM_PE_CREATE_WS	Yes	No	No	Yes
RPM_PE_CREATE_SUMMARY_WS	Yes	No	No	Yes
RPM_OI_PC_PEND_APPRV_EOW	Yes	No	No	Yes
RPM_OI_PC_PEND_APPRV_DAY	Yes	No	No	Yes
RPM_OI_CLR_PEND_APPRV_EOW	Yes	No	No	Yes
RPM_OI_CLR_PEND_APPRV_DAY	Yes	No	No	Yes
RPM_OI_UPCOMING_OFFER_WS	Yes	No	No	Yes
RPM_ROWID_TEMP	Yes	No	No	Yes
RPM_STAGE_ITEM_LOC_RETAIL_TEMP	Yes	No	No	Yes

PurgeGTTCaptureBatch (Purge GTT Capture Batch)

Table 2-69 PurgeGTTCaptureBatch Details

Module Name	PurgeGttCaptureBatch.sh
Description	Truncates data from the GTT capture related tables.
Functional Area	Various
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	PurgeGttCaptureBatch.sh

Design Overview

This batch truncates data from the GTT capture related tables.

Scheduling Constraints

Table 2-70 PurgeGTTCaptureBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc
Scheduling Considerations	Should be run during batch window.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 2-71 PurgeGTTCaptureBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_RFR_GTT_DATA_CAPTURE	Yes	No	No	No
RPM_RPILE_GTT_DATA_CAPTURE	Yes	No	No	No
RPM_CSPFR_GTT_DATA_CAPTURE	Yes	Yes	Yes	No
RPM_CLR_GTT_DATA_CAPTURE	Yes	No	No	No
RPM_FRILE_GTT_DATA_CAPTURE	Yes	No	Yes	No

Design Assumptions

N/A

PurgeItemLocPayloadUnusedCCBatch

Table 2-72 PurgeItemLocPayloadUnusedCCBatch Details

Module Name	PurgeItemLocPayloadUnusedCCBatch.sh
Description	Deletes data from the system for items/item-locs deleted from RMS; deletes payload data as necessary; deletes conflict data for events no longer in the system.
Functional Area	Various
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	PurgeItemLocPayloadUnusedCCBatch.sh

Design Overview

This batch cleans up data from the system for items/item-locs deleted from RMS; deletes payload data as necessary; deletes conflict data for events no longer in the system.

Scheduling Constraints

Table 2-73 PurgeItemLocPayloadUnusedCCBatch Scheduling Constraints

Schedule Information	Description
Frequency	Nightly
Scheduling Considerations	Should be run during batch window.
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

Key Tables Affected

Table 2-74 PurgeItemLocPayloadUnusedCCBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_PRICE_CHANGE	No	No	No	Yes
RPM_CLEARANCE	No	No	No	Yes
RPM_PROMO_OFFER_COND_MERCH	No	No	No	Yes
RPM_PROMO_OFFER_REWARD_MERCH	No	No	No	Yes
RPM_ITEM_LOC	No	No	No	Yes
RPM_FUTURE_RETAIL	No	No	No	Yes
RPM_PRICE_EVENT_PAYLOAD	No	No	No	Yes
RPM_PRICE_CHG_PAYLOAD	No	No	No	Yes
RPM_CLEARANCE_PAYLOAD	No	No	No	Yes
RPM_CON_CHECK_ERR	No	No	No	Yes
RPM_CON_CHECK_ERR_DETAIL	No	No	No	Yes

Design Assumptions

N/A

RegularPriceChangePublishBatch (Regular Price Change Publish Batch)

Table 2-75 RegularPriceChangePublishBatch Details

Module Name	RegularPriceChangePublishBatch.sh
Description	Price Change events are exported for integration to other systems.
Functional Area	Price Changes
Module Type	Business Processing
Module Technology	Java
Catalog ID
Runtime Parameters	RegularPriceChangePublishBatch.sh <outgoing-dir-path>

Design Overview

The RegularPriceChangePublishBatch program formats and stages output of regular price change price events.

The corresponding regularPriceChangePublishExport shell script produces a pipe ("|") delimited flat-file export based on the output of the RegularPriceChangePublishBatch.

The batch looks for price events in the RPM_PRICE_EVENT_PAYLOAD table with a RIB_FAMILY of "REGPRCCHG" and distributes those events to multiple threads based on the settings in the RPM_BATCH_CONTROL table. Each thread reads in its set of regular price change events from tables RPM_PRICE_EVENT_PAYLOAD and RPM_PRICE_CHG_PAYLOAD and generates output in RPM_PRICE_PUBLISH_ DATA.

A flat-file per location based on the data from payload table that need to be published/processed will be created. The naming convention for the flat file will be (REGPC_<timestamp> _<location>_<loc_type>.dat), where <timestamp> is the current system time stamp, <location> is the location ID and <loc_type> is the type of the location where 'S' is for Store and 'W' is for Warehouse.

Scheduling Constraints

Table 2-76 RegularPriceChangePublishBatch Scheduling Constraints

Schedule Information	Description
Frequency	Ad hoc, Recurring
Scheduling Considerations	N/A
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	The RegularPriceChangePublishBatch program is threaded, using RPM_BATCH_CONTROL. The LUW is a single price change event.

Restart/Recovery

N/A

Key Tables Affected

Table 2-77 RegularPriceChangePublishBatch Key Tables Affected

Table	Select	Insert	Update	Delete
RPM_PRICE_EVENT_PAYLOAD	Yes	No	No	No
RPM_PRICE_CHG_PAYLOAD	Yes	No	No	No

Output Files

FHEAD (required): File identification, one line per file.

FDETL (optional): Price Change Event (Create or Modify).

FDELE (optional): Price Change Event (Delete).

FTAIL (required): End of file marker, one line per file.

Note:

File naming standards

The naming convention for the flat file will be (REGPC_<timestamp>_<location>_<loc_type>.dat), where <timestamp> is the current system time stamp, <location> is the location ID and <loc_type> is the type of the location where 'S' is for Store and 'W' is for Warehouse. The zip file naming convetion will be (REGPC_<timestamp>.zip).

Output File Layout

Table 2-78 Output File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	Record Descriptor	Char(5)	FHEAD	File head marker
	Line ID	Number(10)	1	Unique line identifier
	File Type	Char(5)	REGPC	Regular Price Changes
	Export timestamp	Timestamp		System clock timestamp (YYYYMMDDHHMISS)
	Location	Number(10)		Location identifier
	Location Type	Char(1)		S = Store, W = Warehouse
FDETL	Record Descriptor	Char(5)	FDETL	File Detail Marker (1 per price change create or modify)
	Line ID	Number(10)		Unique line identifier
	Event Type	Char(3)		CRE = Create, MOD = Modify
	Id	Number(15)		Price Change identifier
	Item	Char(25)		Item identifier
	Effective Date	Date		Effective Date of price change (YYYYMMDDHH24MISS)
	Selling Unit Change Ind	Number(1)		Did selling unit retail change with this price event (0 = no change, 1 = changed)
	Selling Retail	Number(20,4)		Selling retail with price change applied
	Selling Retail UOM	Char(4)		Selling retail unit of measure
	Selling Retail Currency	Char(3)		Selling retail currency
	Multi-Unit Change Ind	Number(1)		Did multi-unit retail change with this price event (0 = no change, 1 = changed)
	Multi-Units	Number(12,4)		Number of multi-units
	Multi-Unit Retail	Number(20,4)		Multi-Unit Retails
	Multi-Unit UOM	Char(4)		Multi-Unit Retail Unit Of Measure
	Multi-Unit Currency	Char(3)		Multi-Unit Retail Currency
FDELE	Record Descriptor	Char(5)	FDELE	File Detail Delete Marker (1 per price change delete)
	Line ID	Number(10)		Unique line identifier
	Id	Number(15)		Price Change identifier
	Item	Char(25)		Item identifier
FTAIL	Record Descriptor	Char(5)	FTAIL	File tail marker
	Line ID	Number(10)		Unique line identifier
	Number of lines	Number(10)		Number of lines in file not counting FHEAD and FTAIL

Design Assumptions

N/A