Multibuy Customer Segment Promotions Fails with ORA-30926 On RPM_ROLL_FORWARD_SQL.RPM_RF_RANK_PROMOS (Doc ID 2969848.1)

Last updated on AUGUST 24, 2023

Applies to:

Symptoms

When users create the Multibuy Customer Segment Promotions with the same items in the Buylist and Reward List, the Conflict Checking process aborts with an ORA-30926 error if RPM_CUST_SEGMENT_PROMO_FR already has records for these item-location combinations.

What is Working
-----------------------
Such an error does not occur for Non-customer or Simple Customer Segment Promotions.

Error
-----------------------

RPM Logs:
"PACKAGE_ERROR@1ORA-30926: unable to get a stable set of rows in the source tables@2RPM_ROLL_FORWARD_SQL.RPM_RF_RANK_PROMOS"

RPM UI:

"An unexpected error occurred during conflict checking. An administrator needs to be contacted with the associated price event id to correct the issue."

Steps to Reproduce
-----------------------
1. Execute RPM Diagnostic Scripts and RPM_DATA_VALIDATION_SQL functions to validate that there are no duplicates in RPM_FUTURE_RETAIL, RPM_PROMO_ITEM_LOC, RPM_CUST_SEGMENT_PROMO_FR tables and no records in RPM_MISSING_TIMELINE table.
2. Find the item/location combination that does not have records in RPM_CUST_SEGMENT_PROMO_FR yet.
3. Create the Multibuy Customer Segment Promotion in RPM for VDATE+1 with the same item in Buylist, and Reward List for the location checked in step 2.
4. Approve the promotion detail and validate that it is approved correctly.
5. Try to change the status of the Promotion from Approved to Worksheet.
6. Validate that the status was not changed due to the error.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.