4 Conflict Checking

Conflict checking is made up of rules that determine whether a price event can be approved or unapproved.

The rules are broken up into these categories.

• Merge Validator Conflict Checking rules

  These rules are "pre-merge," meaning that the rules are run before the effect of the new price event is added to the RPM_FUTURE_RETAIL table.

• Post-merge Conflict Checking rules

  These rules are "post-merge," meaning that the effect of the new price event has been added to the RPM_FUTURE_RETAIL table prior to running through the rules. If any of these rules is violated by the price event, all effects of the price event are removed from the RPM_FUTURE_RETAIL table.

• Conflict checking rules controlled by system options

  These rules that can be turned on or off by disabling or enabling system options in RPM.

This chapter also explains how to add user-defined conflict checking rules. An overview of Bulk Conflict Checking and Chunk Conflict Checking (and their impact on performance) is also provided.

Note: RPM users can view the conflict status of price changes, clearances, and promotions (simple and threshold) through the user interface. Price events in conflict status also may be refreshed.

Merge Validator Conflict Checking Rules

Merge Validator is the first step in conflict checking rule validation. The price events (regular price changes, clearances, or promotions) that are proposed by the user are rejected before they populate the future timeline. This conflict checking is required, and the user cannot choose whether to run these checks.

1. Single UOM for all item/locations in a fixed-price price event

   This rule ensures that, for a fixed-price price event, only one UOM exists across all item/locations in the price event if a user does not specify a UOM to use. If the system is unable to find a single, unique UOM to use, a conflict is generated and the user must enter a value before attempting to approve the price event again. If a single, unique UOM is found, the price event is updated to reflect that value.

   
   

   Note: The UOM conflict will only come when price changes are created at zone level.

   
   

2. Duplicate price change

   This rule ensures that the new event does not cause multiple price changes for a given date at any point in time on an item/location timeline. If the date of the price change is equal to the VDATE, it is allowed. This allows multiple emergency price changes for the current day.

3. Duplicate clearance price change

   This rule ensures that the new clearance does not cause multiple clearances to take effect on a given date at any point on an item/location timeline. If the date of the clearance is equal to the VDATE, it is allowed. This allows multiple emergency clearances for the current day.

4. Multiple clearance events

   Reset dates for clearances must be in the past and in an executed state before another clearance event can populate the timeline.

   This rule ensures that only one markdown series can exist at any point for an item/location on the future timeline.

5. Multiple promotions for a customer segment

   This rule ensures that only one promotion exists at any point for an item/location/customer type timeline.

6. Approving a promotion exclusion to an already active promotion

   This rule ensures that a user is not able to approve a promotion for an item/location when an active promotion level exclusion exists for that item/location. A promotion level exclusion is a promotion detail that has a change type of exclude and the detail is not a child of another detail. This type of exclusion differs from a detail level exclusion in that a promotion level exclusion prevents item-locs from participating in any other details under the same promotion header where as a detail level exclusion prevents item-locs from participating in the parent detail only

Post-Merge Conflict Checking Rules (rpm_conflict_query_control Table)

This is the final series in the conflict check process. After the effect of the price event has been added to the RPM_FUTURE_RETAIL table, the validation is done by processing the following rules to ensure that the price event is valid.

The RPM_CONFLICT_QUERY_CONTROL table provides the ability to add some configuration your conflict checking. The configuration capabilities are as follows:

• The user can add custom conflict checking rules.

• The user can override the promotion constraint conflict checking rule. The rule that can be turned off is RPM_CC_PROMO_CONSTRAINT.VALIDATE.

  
  

  Note: It is possible for the user to override any of the remaining conflict checking rules for performance reasons, but this is not supported in base RPM. Turning off any of these rules could cause corrupt date in the RPM_FUTURE_RETAIL table.

  
  

• RPM_CC_NEG_RETAIL.VALIDATE

  Future retail cannot be negative. The retail cannot become negative as a result of adding or deleting a price change. Conflict checking is done for any row that is modified on or added to RPM_FUTURE_RETAIL or RPM_CUST_SEGMENT_PROMO_FR.

• RPM_CC_CLEAR_LT_REG.VALIDATE

  Clearance retail must be less than or equal to the regular retail (item/location). The first markdown can be equal to the regular retail, but any additional markdowns must reduce retail.

• RPM_CC_CLEAR_MKDN.VALIDATE

  The clearance markdown must be less than the previous markdown. The new event cannot cause a clearance retail to increase from markdown to markdown at any point on an item/location timeline. The first markdown can make no change, but each additional markdown must be lower than the previous markdown.

• RPM_CC_CLEARUOM_SELLUOM.VALIDATE

  The clearance fixed price change unit of measure (UOM) must be the same as the regular price UOM. For example, if the regular price is $2 each, a conflict occurs if you try to run a fixed price clearance for $20 per dozen.

• RPM_CC_FIXED_CLR_PROM_OVER.VALIDATE

  The fixed price clearance cannot overlap with a fixed price promotion if the promotion is defined as ”apply to clearance.”

  This rule ensures that the new event does not cause a fixed amount clearance to overlap with a promotion that has an Apply To code of Clearance Only or Regular and Clearance at any point on an item/location timeline.

• RPM_CC_PROM_LT_CLEAR_REG.VALIDATE

  The new selling retail cannot be lower than the promotional retail. �

  This rule ensures that the new event does not cause the selling or clearance retail to be less than the promotional retail at any point on the item/loc timeline. This rule applies only if the clearance overlap indicator is Y.

• RPM_CC_AMOUNT_OFF_UOM.VALIDATE

  Amount-off price events cannot change the unit of measure.

  This rule ensures that the new price change does not cause a fixed amount change value UOM to differ from the retail UOM at any point on the item/location timeline. For example, it would stop the scenario if the selling retail is $8 each and the price change is $2 off per ounce. This conflict check applies to price changes, clearances, and promotions.

• RPM_CC_MULTI_UNIT_UOM.VALIDATE

  Multi-unit retail cannot be less than the selling retail.

  This rule ensures that the new event does not cause the multi-unit retail to be less than the selling retail, or the selling retail to be more than purchase of two of the multi-units at any point on the item/location timeline. For example, if the single unit retail is $1 each and the multi-unit retail is $1.50 for two, the check ensures that the multi-unit retail is greater than the selling unit (yes), and the multi-unit retail is less than the multi-unit quantity times the selling retail: $1.50 is less than $1.00 times 2 (yes).

• RPM_CC_PC_PROM_OV.VALIDATE

  A regular price change cannot occur during a promotion.

  New price changes cannot overlap with a promotion at any point on the item/location timeline if the price change overlap indicator is N.

  
  

  Note: This rule also can be configured by changing the system option.

  
  

• RPM_CC_CL_PROM_OV.VALIDATE

  Clearances and promotions cannot overlap.

  Clearance price changes and promotional price changes cannot run concurrently unless the clearance overlap indicator is Y.

  
  

  Note: This rule also can be configured by changing the system option.

  
  

• RPM_CC_PROM_COMP_CNT.VALIDATE

  An item/location can exist on more than one promotion at a time, but it is limited by a set of system option.

  If Multiple Promotions Ind = N, the new promotion detail must be the only one that impacts the items/locations on the promotion detail during the timeframe of the promotion detail.

  If Multiple Promotions Ind = Y, the maximum number of promotions that an item/location can exist on must be less than or equal to the system option value (MAX_OVRLP_PROM_COMP_DETAIL). This count applies only to non-customer segment promotions

  
  

  Note: This rule also can be configured by changing the system option.

  
  

• RPM_CC_PROMO_CONSTRAINT.VALIDATE

  Validate new item/location price events against Promotion Constraint.

  This is the only rule in the table that can be overridden (turned off). If the rule action is set to N, all promotion constraints that are set up in RPM are ignored.

• RPM_CC_PROMO_FIXED_PRICE.VALIDATE

  One fixed-price promotion maximum (valid for Simple promotions).

  Only one fixed-price simple promotion can affect an item/location combination on the timeline. This conflict check verifies that only one fixed-price promotion exists on the same effective day per item/location.

  
  

  Note: Only one fixed price simple promotion allowed for an item location at one time will no longer be validated if the system option for overlapping simple promotions is set to non-compounding best deal on the conflict check rule for fixed price.

  
  

Rules Controlled by System Options

The following rules can be turned on or off by changing system options settings:

• An item/location can exist on more than one promotion at a given time

  If the indicator is set to Yes (checked), an item can have its retail price affected by more than one promotional discount at a single time in a given location. If the indicator is set to No (unchecked), only one promotional discount can exist at the same time for a given item/location.

• A regular price chance cannot occur during a promotion

  If Price change/Promotion overlap Ind = N, a price change cannot overlap with a promotion at any point on the item/loc timeline. If Price change/Promotion overlap Ind = Y, then a price change can be approved during a promotion and a promotion can be created that overlaps a price change.

• Clearances and promotions cannot overlap

  If Clearance/Promotion overlap Ind = N, a clearance cannot overlap with a promotion at any point on the item/loc timeline. If Clearance/Promotion overlap Ind = Y, a clearance can be approved during a promotion and a promotion can be created that overlaps a clearance.

• System Generated Exclusions Tolerance %

  This system option is used to determine the number of transaction items that will be automatically excluded during the price event approval process. This feature is available for price changes, clearance, and simple or threshold promotions that are created at a merchandise hierarchy level above transaction level. The percent value designated determines the number of items on a price event that can encounter conflicts and continue with the approval of the price event. If the number of items with conflicts is exceeds the percent value based on the total item in the price event, the price event will be set to worksheet status and conflicts will be flagged.

• System Option: Apply Promotion Change Type 1st

  Indicates the type of promotion change to apply first. This option is used only after fixed price and non-secondary promotions are processed. Ranking is applied to the promotion header before promotion components are considered. Promotions are applied to an item/location based on ranking order. These rules apply to all promotions, including those created using the InjectorPriceEventBatch program.

  
  

  Note: For information, see the subsection called Details under "InjectorPriceEventBatch Batch Design" in Chapter 7, "Java Batch Processes".

  
  
  
  

  Note: This system option will only be used when selling retail calculations are compounded. It will not be used when the system option simple promotion overlap rule is set to non-compounding best deal.

  
  

Ranking order is as follows:

1. Fixed price promotions.

2. Non secondary (as opposed to secondary flagged promotions).

3. System option: promotion apply order (amount off or percent off).

4. Secondary flagged promotions (promotion header check box).

Example:

Promotion 1 has one component. P1C1 has one detail that is Amount Off.

Promotion 2 has two components. P2C1 has one detail that is Percent Off, and P2C2 has one detail that is Fixed Price.

Both promotions are non secondary, and the Apply Promotion Change Type 1st system option is set to Amount Off.

For this example, the order of application is as follows:

1. Promotion 2, Component 2 (P2C2)

2. Promotion 2, Component 1 (P2C1)

3. Promotion 1, Component 1 (P1C1)

Because Promotion 2 includes a Fixed Price component, it is ranked higher than Promotion 1. All components under Promotion 2 are applied first, followed by the component under Promotion 1.

When applying Promotion 2, P2C2 (the Fixed Price component) is applied before P2C1. After all components under Promotion 2 are applied, the component under Promotion 1 is applied.

• System Option: Simple Promotion Overlap Rule.

  This field determines handling of simple promotion overlapping another simple promotion based on the following option detail. The field will be disabled after initial setting.

  • Compounding (default value)

    The selling price is calculated by compounding each (two or more) simple promotions for the overlapping period.

  • Non-compounding Best Deal

    The selling price is calculated for each (two or more) promotion separately for the overlapping period.

Adding User-Defined Conflict Checking Rules

The RPM_CONFLICT_QUERY_CONTROL table allows the addition of user-defined rules. When a row is added to the RPM_CONFLICT_QUERY_CONTROL table and a conflict function is implemented that fits the expected prototype, the rule is executed during all conflict check runs.

The conflict checking functions are executed after the new price event has been added to the RPM_FUTURE_RETAIL table so that the effect of the change can be seen by the rule checking function.

The following is an example of how to add a rule. Rule definition: No item should sell for less than $5.

1. Add a row to the rpm_conflict_query_control:

   insert into RPM_CONFLICT_QUERY_CONTROL (
   CONFLICT_QUERY_CONTROL_ID,
           CONFLICT_QUERY_FUNCTION_NAME,
           CONFLICT_QUERY_DESC,
           ACTIVE,
           OVERRIDE_ALLOWED,
           EXECUTION_ORDER,
           BASE_GENERATED,
           BASE_REQUIRED,
           LOCK_VERSION)
   select RPM_CONFLICT_QUERY_CONTROL_SEQ.nextval,
          'CUSTOM_RULE.BELOW_FIVE_CHECK',
          '$5 check',
          'Y',
          'Y',
          20,
          'N',
          'N',
          null
   from dual;
          
   

2. Implement CUSTOM_RULE.BELOW_FIVE_CHECK to fit the expected prototype. The function should take one input parameter of type VARCHAR2. The function should have one output parameter that is a CONFLICT_CHECK_ERROR_TBL to hold any error or conflict information. The function should return 0 for failure and 1 for success.

   CREATE OR REPLACE PACKAGE BODY CUSTOM_RULE AS
   -------------------------------------------------------
   FUNCTION BELOW_FIVE_CHECK(IO_error_table    IN OUT CONFLICT_CHECK_ERROR_TBL)
                             I_price_event_type IN     VARCHAR2)
   RETURN NUMBER IS
   
      L_program      VARCHAR2(61)  := 'CUSTOM_RULE.VALIDATE'
   
      L_error_rec         CONFLICT_CHECK_ERROR_REC := NULL;
      L_error_tbl         CONFLICT_CHECK_ERROR_TBL := CONFLICT_CHECK_ERROR_TBL();
      cursor C_CHECK is
         select price_event_id
                future_retail_id
                NULL cs_ promo_fr_id 
           from rpm_future_retail_gtt gtt
          where gtt.price_event_id NOT IN (select ccet.price_event_id
                                             from table(cast(L_error_tbl as conflict_check_error_tbl)) ccet)
            and (selling_retail       < 5
              or clear_retail         < 5
              or simple_promo_retail  < 5)
    union all
         select price_event_id,
                NULL future_retail_id,
                cust_segment_promo_id cs_promo_fr_id
           from rpm_cust_segment_promo_fr_gtt gtt
          where gtt.price_event_id NOT IN (select ccet.price_event_id
                                             from table(cast(L_error_tbl as conflict_check_error_tbl)) ccet)
            and promo_retail < 5;
   BEGIN
      if IO_error_table is NOT NULL and
         IO_error_table.count > 0 then
         L_error_tbl := IO_error_table;
      else
         L_error_rec := CONFLICT_CHECK_ERROR_REC(-99999, NULL, NULL, NULL);
         L_error_tbl := CONFLICT_CHECK_ERROR_TBL(L_error_rec);
      end if;
      for rec IN c_check loop
         L_error_rec := CONFLICT_CHECK_ERROR_REC(rec.price_event_id,
                                   rec.future_retail_id,
                               RPM_CONFLICT_LIBRARY.CONFLICT_ERROR,
                                   'below_five_check_string',
                                   rec.cs_promo_fr_id);
         if IO_error_table is NULL then
            IO_error_table := CONFLICT_CHECK_ERROR_TBL(L_error_rec);
         else
            IO_error_table.EXTEND;
            IO_error_table(IO_error_table.COUNT) := L_error_rec;
         end if;
      end loop;
      return 1;
   EXCEPTION
      when OTHERS then
         L_error_rec := CONFLICT_CHECK_ERROR_REC(NULL,
                                                 NULL,
                               RPM_CONSTANTS.PLSQL_ERROR,
                               SQL_LIB.CREATE_MSG('PACKAGE_ERROR',
                                                 SQLERRM,
                                        L_program,
                                        TO_CHAR(SQLCODE)));
         IO_error_table := CONFLICT_CHECK_ERROR_TBL(L_error_rec);
         return 0;
   END BELOW_FIVE_CHECK;
   --------------------------------------------------------
   END;
   /
   

3. Define the error string below_five_check_string for the new rule in the Java property file (messages.properties).

RPM_FUTURE_RETAIL

RPM provides retailers a forward looking view of all active and pending approved pricing events affecting an item at a given location. To do this, RPM uses a set of three Future Retail tables. Although each of these tables serves a different purpose, all of them work together, as shown in the following:

Table Name	Description
RPM_FUTURE_RETAIL	This table serves as the main/”parent”.
RPM_PROMO_ITEM_LOC_EXPL	This table is an exploded representation of all active/approved promotions and serves as detail/”child” to RPM_FUTURE_RETAIL. This table does not necessarily hold data at transaction item and location level - timelines on this table will match those on RPM_FUTURE_RETAIL.
RPM_CUST_SEGMENT_PROMO_FR	This table holds promotional retails specific to customer segments and serves as detail/”child.”

The tables above are used and updated during the conflict checking process. See the Oracle Retail Price Management Data Model for the detail table definition of these tables.

Bulk Conflict Checking

This section describes:

• Bulk conflict checking and its impact on performance

• Functional areas affected by bulk conflict checking

• Batch design updates, including any additional parameters, for batch programs that have changed

Overview of Bulk Conflict Checking and Its Impact on Performance

RPM utilizes bulk processing for conflict checking of price events whenever possible.

To illustrate how the conflict checking engine processes a collection of price events, refer to the following example.

Assume that one user sent this collection of price changes to RPM to be approved.

Price Changes	Effective Date	Item/ Location	Status
PC 1	9/7/07	Item 1 / Zone 1	WORKSHEET
PC 2	9/28/07	Item 1 / Location 1	WORKSHEET
PC 3	9/15/07	Item 2 / Zone 2	WORKSHEET
PC 4	9/30/07	Item 3 / Zone 1	WORKSHEET

In the table above, Zone 1 contains the following locations:

Location 1

Location 2

Location 3

Location 4

Zone 2 contains the following locations:

Location 5

Location 6

Location 7

Location 8

Location 9

The first process is to separate these price changes into several collections (referred to as sequence) based on the item/location combination so that there are no item/locations overlapping in the sequence. In the example above, the PC1 and PC2 are overlapping. This collection of price changes is then divided into two sequences.

Sequence	Price Change	Effective Date	Item/ Location
1	PC 1 PC 3 PC 4	9/7/07 9/15/07 9/30/07	Item 1 / Zone 1 Item 2 / Zone 2 Item 3 / Zone 1
2	PC 2	9/28/07	Item 1 / Location 1

The above sequences are then processed one at a time, to ensure that there is no database locking issue. Note that for the overlapping price changes (PC1 and PC2), the decision of which price changes should go to the first or second sequence is based on the effective date of the price changes.

To further make this conflict checking process faster, the sequences are divided into several threads. These threads within the sequence are processed simultaneously. To decide the number of threads within the sequence, there is a row in the RPM_BATCH_CONTROL table with PROGRAM_NAME column equal to com.retek.rpm.app.bulkcc.service.BulkConflictCheckAppService.

The value of the THREAD_LUW_COUNT column decides the maximum number of item/location in the thread. This number can be adjusted (based on the number of processors, size of the processors, and size of the data sets being processed, among other things) to optimize this conflict checking process in their environment. The default value is 10,000.

In the example of the price changes above, if the THREAD_LUW_COUNT is set to 10 then the sequences are divided into threads similar to the following table.

Processed First: Sequence One

Sequence	Thread	Price Change	Item/ Location
1	1	PC 1	Item 1 / Location 1 Item 1 / Location 2 Item 1 / Location 3 Item 1 / Location 4
1	1	PC 3	Item 2 / Location 5 Item 2 / Location 6 Item 2 / Location 7 Item 2 / Location 8 Item 2 / Location 9
1	2	PC 4	Item 3 / Location 1 Item 3 / Location 2 Item 3 / Location 3 Item 3 / Location 4

Process Second: Sequence Two

Sequence	Thread	Price Change	Item/ Location
2	1	PC 2	Item 1 / Location 1

Even though the Item3/Location 1 of PC 4 can fit into Thread 1 of sequence 1, there is an additional rule that prevents that. This rule is: Item/locations of one price change cannot be processed across multiple threads.

Chunk Conflict Checking

RPM also can utilize chunk processing for conflict checking of price events whenever possible. This can be best illustrated by considering a promotion set up for a department 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 promotion as suggested above. By chunking those 50,000 item/locations into smaller groupings, multiple threads can be utilized to execute the conflict checking process.

To determine if a price even should be processed through chunking, there is a row on the RPM_BATCH_CONTROL table with PROGRAM_NAME column equal to com.retek.rpm.app.bulkcc.service.BulkConflictCheckAppService.

• The value of the THREAD_LUW_COUNT column of a row on the RPM_BATCH_CONTROL table with PROGRAM_NAME column equal to com.retek.rpm.app.bulkcc.service.ChunkConflictCheckAppService.

• The value of the CONFLICT_CHECK_CHUNK_FACTOR column of a single row in the RPM_SYSTEM_OPTIONS table. This column need to be updated by a DBA from the back-end as there is no UI screen available to update this column.

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 NVL(CONFLICT_CHECK_CHUNK_FACTOR, 2.5).

RPM 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 com.retek.rpm.app.bulkcc.service.ChunkConflictCheckAppService as the chunk size. If this is NULL, the chunk size will be 50,000.

Skipping Conflict Checking during Submit

Normally during the submit process, RPM performs a conflict checking process to validate whether any of the item/location combinations affected by the price event has any conflict. If there is any conflict, the price event stays in Worksheet status and the conflict will be displayed to the user. There is an option to skip the conflict checking process during the submit process, if it is not needed by the business process in place. This can be done by checking the Do not run Conflict Check for Submit system option check box. If this is done, RPM will not perform the conflict checking process (and will not show conflicts if there are any) when users perform a price event submit action. The price event status is switched to Submit immediately.

Skipping Conflict Checking during Complex Promotion Approval

Users can skip conflict checking process during complex promotion approval (and un-approval). When users approve (or un-approve) a complex promotion, the system does not perform conflict validation, but it still inserts into the Future Retail GTT table and performs the explosion and rolls forward logic. It populates the Payload tables so that RPM can communicate this complex promotion to other systems (for example, through RIB or ORPOS extract).

If this is required, and it meets their business requirements, users can do this by checking the Do not run Conflict Check for Complex Promotion Approval system option check box. If this system option is checked, RPM does not perform the conflict checking process (and will not show conflicts if there are any) when users approve (or un-approve) a Complex promotion.

This option potentially improves the approval of very large Complex promotions (such as Dept/Zone Level) dramatically. But retailers must consider this very carefully, because the following functionality is lost:

• Overlapping promotions: Item/Location in the Complex promotion is not considered when validating the overlapping promotions.

• Price Change Promotion Overlap/Clearance Promotion Overlap: Item/Location in the Complex promotion is not considered when validating these types of overlapping.

• The Complex promotion information is not available in the Future Retail tables.

• Deal cannot be attached to Complex promotions.

Although users can change this system option from one state to another in the System Options Edit screen at any time, flipping the system option (while having active or pending Complex promotions in the system) could produce unpredictable results, such as the following scenarios:

• The Do not run Conflict Check for Complex Promotion Approval system option is checked. The user approves a Complex promotion, unchecks the system option, and un-approves the Complex promotion (or cancels/changes the end date after the promotion becomes active). RPM attempts to execute the conflict checking process but cannot find the related records in the Future Retail tables. The Complex promotion might fail un-approval.

• The Do not run Conflict Check for Complex Promotion Approval system option is unchecked. The user approves a Complex promotion. RPM executes the conflict checking process and updates the Future Retail tables. The user checks the system option and un-approves the Complex promotion (or cancels/changes the end date after the promotion becomes active). RPM un-approves (or cancels/changes the end date of) the promotion without executing conflict checking process. Related records in Future Retail tables are not updated.

• The Do not run Conflict Check for Multi-Buy Promotion Approval system option is checked. The user approves a Complex promotion, unchecks the system option, and approves another Complex promotion that is overlapping with the first promotion. During the overlapping constraint validation, RPM will not see the first promotion.

Conflict Checking Refresh

Within the RPM user interface, price events in conflict checking status can be refreshed. On the detail dialogs for price changes, clearances, and promotions (simple and threshold), a button is enabled when price events are in conflict checking status. When this button is clicked, a separate pop-up window opens to list all price events in conflict checking status. From this window, the user can click Refresh to make a call to the database. If the status of the price event has changed, the change is reflected in this window.

From this window, the user can click Cancel (to not update the price event dialog) or Exit (to refresh the price event screen with the updated status for price events).

For information, see the Oracle Retail Price Management User Guide.