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 |
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 |
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 |
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 |
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. |
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 ]
|
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 |
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 |
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. |
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 |
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 |
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.
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.
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.
|
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 |
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 |
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 |
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.
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-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. |
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 ]
|
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 |
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 |
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.
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.
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.
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.
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.
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-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. |
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]
|
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 |
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:
|
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
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 |
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 |
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 |
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 |
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 |
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 |
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. |
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 |