This chapter discusses Java-based batch processing within Oracle Retail Pricing.
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. |
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. |
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. |
futureRetailPurgeBatch |
This timed multi-threaded batch deletes records from future retail tables 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. |
NewItemLocationBatch |
This batch ranges item locations by putting them into the future retail table and Pricing item/location. Item and locations are fed to this program via the RPM_ITEM_LOC_WS table, which is populated by a Merchandising process. |
NightlyBatchCleanup |
This batch performs "clean up" logic against Pricing database objects. |
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 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. |
PromotionInductionBatch |
This batch program allows the user to upload promotions in bulk. |
PurgeBatch |
This generic purge batch calls most of the purge batches into one purge process. |
PurgeGttCaptureBatch |
This batch process deletes records from gtt data capture tables. |
RegularPriceChangePublishBatch |
This batch process formats and stages output of regular price change price events. |
RefreshPosDataBatch |
The RefreshPosDataBatch program deletes the contents of the payload tables. |
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.
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. |
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.
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 |
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.
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.
Table 2-9 has some examples of message types that are created when certain activities are being triggered by user:
Table 2-9 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. |
Table 2-10 lists Payload tables are used to hold staged data for BDI population:
Table 2-10 Payload Tables that Hold Staged Data for BDI population
Table | Description |
---|---|
RPM_PRICE_EVENT_PAYLOAD |
o 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. |
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.
Table 2-11 maps what message structures use which payload or operational tables to build the BDI data:.
Table 2-11 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 |
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.
Table 2-12 lists the BDI tables and its parent table from a high level perspective
Table 2-12 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 |
Table 2-13 BDI Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PRC_PAYLD_MSG_HDR_OUT |
Yes |
No |
No |
No |
PROMO_OFFER_OUT |
Yes |
No |
No |
No |
PROMO_OFFER_OUT |
Yes |
No |
No |
No |
PROMO_OFFER_COND_OUT |
Yes |
No |
No |
No |
PROMO_OFR_REWARD_OUT |
Yes |
No |
No |
No |
PROMO_OFFER_OUT |
Yes |
No |
No |
No |
PRC_PAYLD_MSG_HDR_OUT |
Yes |
No |
No |
No |
PROMO_OFFER_OUT |
No |
Yes |
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 |
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.
Table 2-14 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. |
Table 2-15 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. |
Table 2-16 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) |
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. |
Table 2-17 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 |
Table 2-18 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 |
DEPT |
NUMBER(4) |
Yes |
Department ID. |
CLASS |
NUMBER(4) |
Yes |
Class ID. |
SUBCLASS |
NUMBER(4) |
Yes |
Subclass ID. |
ITEM |
VARCHAR2(25) |
Yes |
Item. |
DIFF_ID |
VARCHAR2(10) |
Yes |
Differentiator ID. |
EXCLUDE_IND |
NUMBER(1) |
No |
The exclude indicator. |
Table 2-19 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. |
Table 2-20 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. |
Table 2-21 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 |
The date and time that the offer cancellation takes effect. |
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 |
DEPT |
NUMBER(4) |
Yes |
Department ID. |
CLASS |
NUMBER(4) |
Yes |
Class ID. |
SUBCLASS |
NUMBER(4) |
Yes |
Subclass ID. |
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. |
Table 2-22 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. |
Table 2-23 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 <user alias> <incoming-dir-path> <Template_Key> [filter_Str ]
|
Note: File naming standardsXML 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. |
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.
Table 2-25 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 |
Table 2-26 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 <user_alias> <outgoing-dir-path> |
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 RIB_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 successfully generated by the Export script (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-dir-path provided as a batch parameter.
Table 2-27 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. |
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 standardsThe 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). |
Table 2-29 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 |
Table 2-30 FutureRetailPurgeBatch Details
Module Name |
FutureRetailPurgeBatch.sh |
Description |
Purges future retail data that are past the retention period. |
Functional Area |
Future Retail |
Module Type |
Business Processing |
Module Technology |
Java |
Catalog ID |
|
Runtime Parameters |
futureRetailPurgeBatch.sh <user alias> |
This batch is a timed multi-threaded process that purges future retail data that are past the retention periods of their corresponding price events.
Table 2-31 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 is inherent in the design of this program, as records are deleted after processing they would not be picked up if the program is run again.
Table 2-33 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 <user alias> |
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.
Table 2-34 FutureRetailRollUpBatch Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Ad hoc |
Scheduling Considerations |
This process must be executed during the batch window. As it runs, other processes must not access the future retail tables. This batch can be run ad-hoc. |
Pre-Processing |
N/A |
Post-Processing |
N/A |
Threading Scheme |
This batch is threaded by item. |
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.
Table 2-36 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 <user alias> |
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.
Table 2-37 ItemReclassBatch Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Ad hoc |
Scheduling Considerations |
Must be run during the batch window. |
Pre-Processing |
The RPM_ITEM_MODIFICATION table has been populated by the RMS reclassification batch process. |
Post-Processing |
N/A |
Threading Scheme |
N/A |
Table 2-39 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 <user alias> [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. |
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.
Table 2-40 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 and item loc tables 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. |
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.
Processing Stage Rows in Restart Status
When running in Restart mode, the batch will attempt to re-process the future retail roll up functionality and to clean up item location staging tables. It will delete the records that were completely processed from the staging tables.
This mode has to be executed when there are threads/process ID that have errors or did not complete the roll-up process and clean-up of staging tables. This should be part of the business process. For example, clients can do this ad-hoc when no one is using the application. They also have to establish how they are going to retrieve process ID and threads that need reprocessing. If there won't be an established process for running NIL Batch in restart mode, the NIL thread status and staging tables data will increase and won't be cleaned up.
Table 2-42 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 <user_alias> PRE/POST |
The nightlyBatchCleanup batch program performs "clean up" logic against certain database structures.
Table 2-43 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 |
Table 2-44 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 |
Table 2-45 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 <user alias> <incoming-dir-path> <Template_Key> [filter_Str ]
|
Note: File naming standardsXML 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 |
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.
Table 2-47 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 |
Table 2-48 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 <user_alias> [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). |
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.
Table 2-49 PriceEventExecutionBatch Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Ad hoc, Recurring |
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 |
The program is restartable and will pick up any events remaining to be processed in a given run.
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.
The following command runs the priceEventExecutionForChunkCCEmergencyEvents job:
priceEventExecutionForChunkCCEmergencyEvents.sh userAlias
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.
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.
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.
Example: 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.
The following command runs the ProcessPendingChunkBatch job:
ProcessPendingChunkBatch.sh userAlias
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.
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.
Table 2-51 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 <user alias> <incoming-dir-path> <Template_Key> [filter_Str]
|
Note: File naming standardsXML 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. |
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.
Table 2-53 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 |
Here are the steps in the purge process:
Delete items and item/locations which were already deleted in the merchandising system from the pricing system.
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.
Delete published payload data from price changes, clearances, and promotions that is older than 7 days.
Truncate all the Bulk CC processing tables.
Delete conflict check error results for price changes and clearances which no longer exist.
Truncate all UI, item creation, and item/location ranging workspace tables.
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
PurgeBatch.sh <user_alias>PurgeBatch.sh <user_alias> <export_purge ALL/BDI/FLAT_FILE>
Where ALL is used if the customer is using both BDI integration and flat file integration, BDI for BDI only, or FLAT_FILE for flat file only.
Table 2-55 PurgeGTTCaptureBatch Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
RPM_ZONE_FUTURE_RETAIL |
Yes |
No |
No |
Yes |
RPM_ITEM_LOC |
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_STAGE_DELETED_ITEM_MASTER |
Yes |
No |
No |
Yes |
RPM_STAGE_DELETED_ITEM_LOC |
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_PROMO_OFFER_CANCEL_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PROM_OFR_CNCL_LOC_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PROM_OFR_CNCL_ITEM_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PROMO_OFFER_LOC_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PROMO_OFR_RWD_MRCH_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PROMO_OFR_CND_MRCH_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PROMO_OFFER_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PRICE_CHG_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_CLEARANCE_PAYLOAD |
Yes |
No |
No |
Yes |
RPM_PRICE_EVENT_PAYLOAD |
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_CON_CHECK_ERR |
Yes |
No |
No |
Yes |
RPM_CON_CHECK_ERR_DETAIL |
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 |
Table 2-56 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 <user_alias> |
Table 2-59 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 <user_alias> <outgoing-dir-path> |
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.
Table 2-60 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. |
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 standardsThe 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). |
Table 2-62 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 |