This blog promotes knowledge sharing through experience and collaboration. For more product information, visit our WebSphere Commerce CSE page. For easier navigation, utilize the Categories to find posts that match your interest.
Troubleshooting Promotion: Analyzing Problem Using Database
In this post, we will look at the promotion requirement and expected behavior with respect to the shopping cart content by simply reviewing the database data. In near future, I'm planing to have another blog on using promotion trace to understand how promotion is getting evaluated and discounted.
For this post, I have configured OOB "Buy catalog entry X, get additional X at a percentage off" promotion. If I buy two LUM Compact Size Table Lamp I'm expecting the second one to be half price (i.e. due to 50% of second time promotion), but while testing I got 50% for both.
Clearly I'm not getting the expected results as per the Knowledge Center document on this promotion.
Promotion requirement and expected behavior:
When understanding requirements, I would ask below questions as it helps us to validated the expected behavior versus the actual out of box behavior.
To understand the promotion behavior you will have to understand the runtime promotion XML stored in PX_PROMOTION.XMLPARAM field. When evaluating promotion, OOB logic uses this XML to evaluate the promotion against the shopping cart. Example: runtime promotion XML provides detailed information for all possible nodes within XML, but in here we will review XML with respect to this promotion.
Executing below query to retrieve runtime promotion XML of the problematic promotion and format it.
SELECT XMLPARAM FROM px_promotion WHERE name = <promotion name> AND status = 1
Let's take a closer look at the XMLPARAM:
In some cases it is easier to look at database to determine the issue, specifically for production.
Verify promotion discount using database:
You can use following SELECT calcode_id FROM ordadjust WHERE orders_id =<orders_id> query to retrieve data from ORDADJUST, and CALCODE_ID from the result can be used to identify the promotion.
You can use following SELECT * FROM ordiadjust WHERE orderitems_id IN (SELECT orderitems_id FROM orderitems WHERE orders_id = <orders_id>) query to retrieve ORDIADJUST records to understand item level discount distribution.
The <State></State> section in the DETAIL data, indicates whether a promotion is QUALIFIED, APPLIED, ELIMINATED, or EXPIRED for the specified order. You can execute following SELECT * FROM px_promoarg WHERE orders_id = <orders_id> query to retrieve the promotion argument details:
Note: PX_PROMOARG data will only be available if you are persist it. For more details regarding this behavior, please refer to Configuring PromotionArgument database records to reduce locking conflicts document.
Based on the database findings, we can see that both ORDADJUST and PX_PROMOARG indicating that wrong promotion is applied i.e. '10%off-Lighting-10000102 ' is applied instead of '50%off-Lamp_BOGO-10000103 ', and if we deactivate that promotion then our promotion works fine. But, Why do we have to deactivate it? Looking at the Combining promotions, we can see that only one promotion with highest priority can apply with 'Exclusive within the same group' policy. In this case our '50%off-Lamp_BOGO-10000103 ' was priority 100 and '10%off-Lighting-10000102 ' was priority 400. At this point, consulting with business team and correcting the priority or the policy should resolve the issue.