| Oracle® Retail Price Management Operations Guide Release 13.2.9 E71051-01 |
|
 Previous |
 Next |
Conflict checking is made up of 18 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 these 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�
There are three 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) also is 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 is the first step in conflict checking. 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. �
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 the zone level. |
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.�
Duplicate clearance price change�
This rule ensures that the new clearance does not cause multiple clearances for 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.�
Multiple clearance events�
Reset dates for clearances must be in the past 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. Only clearance resets that have a date greater or equal to the VDATE are considered.�
Multiple promotions for a customer segment
This rule ensures that only one promotion exists at any point for an item/location/customer type timeline.
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.
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 11 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 (simple or complex). Simple or complex refers to the simple_promo_retail and complex_promo_retail of rpm_future_retail table. �
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 changes 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 options.
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.�
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.�
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.
System Option: Hierarchy, Item List/price Event List % of Item Conflicts Allowed.�
This new system option is used to determine the number of transaction item exclusions that will be automatically created during the price event approval. this feature is available for price changes, clearance, and simple or threshold promotions that are created at a higher than transaction level. The percent value designated determines the number of items on a price event that can fail conflict check and continue with approval of the price event. If the number of conflicts found are greater than the percent value, 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�." |
Ranking order is as follows.
Fixed price promotions.
Non secondary (as opposed to secondary flagged promotions).
System option: promotion apply order (amount off or percent off).
Secondary flagged promotions (promotion header check box).
Example:
Promotion 1 has one component. P1C1 is Amount Off.
Promotion 2 has two components. P2C1 is Percent Off, and P2C2 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:
Promotion 2, Component 2 (P2C2)
Promotion 2, Component 1 (P2C1)
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.
|
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. |
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.
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;�
Implement CUSTOM_RULE.BELOW_FIVE_CHECK to fit the expected prototype. The function should take one input parameter of type VARCHAR2. The function should return a CONFLICT_CHECK_ERROR_TBL as an output parameter 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�
or complex_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_CONFLICT_LIBRARY.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;�
/�
Define the error string below_five_check_string for the new rule in the Java property file (messages.properties). �
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 below:
| 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 |
|
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.
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�
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, please refer to the example below.�
Assume that one user sent this collection of price changes to RPM to be approved.
| Price Change | 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:
���Zone 2 contains the following locations:�
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 change.�
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. The clients can adjust this number (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 that is set right now 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 table below.�
| 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 |
| 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.�
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.
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. Users have the option to skip the conflict checking process during the submit process, if they do not need that for their business process. They can do this by checking the "Do not run Conflict Check for Submit" system option check box. If they do this, RPM does not perform the conflict checking process (and will not show conflicts if there are any) when the user performs a price event submit action. The price event status is switched to "Submit" immediately.
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.
Within the RPM user interface, price events in confilct 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 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.
