RPM_FUTURE_RETAIL (RFR) is one of the most critical tables for the Retail Price Management (RPM) pricing and conflict checking processes. When the data integrity in this table is not correct, it may cause price events and batches to fail, and price events to not get processed. The root cause for data integrity problems cannot be directly determined since most of the issues are related to Global Temp Tables (GTT) used during processing, which will eventually get their input from the RFR table.
Unforeseen issues such as missing or incorrect timelines started to occur in RPM versions 13.2.4 and 13.1.5 where storing timelines at higher levels was implemented. The code fixes being made to address reported issues will correct the code for future timeline creation, but the existing data has to be manually corrected via scripts.
The functional areas for which issues have been identified so far are:
- NewItemLocBatch.sh (NIL) logic when generating higher level timelines and intersection
- Conflict checking (CC) logic when creating new timelines as a result of new price events and/or intersection
Please note there may still be other bugs which create incorrect timelines or miss creating timelines altogether.
When it comes to investigating data integrity in RFR, there are 4 things to consider:
- Missing Timelines
- Duplicate Records
- Null Values in the CURR_HIER_LEVEL column
- Null Values in the MAX_HIER_LEVEL column
Below are some common error messages indicating there is an issue with data integrity in the RFR table along with a potential solution:
- Injector batch fails with '@0PACKAGE_ERROR@1ORA-32638: Non unique addressing in MODEL dimensions@2RPM_ROLL_FORWARD_SQL.RPM_RF_MULTI_UNIT_RETAIL' Error
- Use the ORA-32638 Data Fix Scripts attachment found in Doc ID 2023096.1
- Price changes pass conflict checking but immediately revert back to Worksheet status. The following error is seen in the RPM Log - '@0PACKAGE_ERROR@1ORA-30926: unable to get a stable set of rows in the source tables@2RPM_ROLL_FORWARD_SQL.EXECUTE'
- Read Doc ID 2081937.1 - Price Changes Pass Conflict Checking Successfully, But Go Back to Worksheet Status Immediately
- Regular price change approval through UI/Injector fails with '@0PACKAGE_ERROR@1ORA-00001: unique constraint (RMS13.PK_RPM_PC_TICKET_REQUEST) violated@2RPM_CC_PUBLISH.STAGE_PC_MESSAGES' Error
- Read Doc ID 2029307.1 - RPM_FUTURE_RETAIL Integrity Check - Diagnostic Script For Oracle Retail Price Management (RPM)
- RPM Location Move Schedule Batch fails with 'Unable to get a stable set of rows in the source tables@2RPM_ROLL_FORWARD_SQL.EXECUTE' Error
- The following code can be used to identify problematic duplicate records:
-- Find Duplicate RPM_FUTURE_RETAIL records
select dept, item, diff_id, location, zone_node_type, cur_hier_level, action_date, count(1)
group by dept, item, diff_id, location, zone_node_type, cur_hier_level, action_date
having count(1) > 1;
-- Find Duplicate RPM_PROMO_ITEM_LOC_EXPL records
select dept, item, diff_id, location, zone_node_type, cur_hier_level, promo_dtl_id, count(1)
group by dept, item, diff_id, location, zone_node_type, cur_hier_level, promo_dtl_id
having count(1) > 1;
In order to be proactive and try and avoid this issue occurring, it is suggested to run the RFR Integrity Check script periodically as part of a scheduled maintenance process.
- Doc ID 2029307.1 - RPM_FUTURE_RETAIL Integrity Check - Diagnostic Script For Oracle Retail Price Management (RPM)
If the application creates such timelines even after fixing the records, additional troubleshooting tools are available. Specifically, there are several unique indexes and/or debug codes that can be deployed on a test environment that will monitor and find the code which causes the issues. For assistance with these additional tools, please log a Service Request.