Get Discounts for a given Promotion code

ravikanth | April 9, 2009

I want to show the discount applicable for a given promotion coupon code and I feel this is one of the genuine requirement. I tried to play with the API to make this work. If we check back the associations between objects, the coupon code is stored under promotion, the promotions are associated to discounts and discounts are associated to campaign.

Campaign
|
Discount
|
Promotion Code
|
Coupon code

If you check data management object structure, Promotion code and campaigns are easily created and these are associated to a discount.

image

To check which promotion code is assigned to a discount, I think it would be simple. First – get all list of discounts and check for desired promotion code in each discount.

The real trick comes when we want to show the discounts based on the coupon code. I didn’t tried using API but I feel to write a simple script instead of iterating hundreds of discount options. Here is the script that gives the discount name associated for a particular coupon code.

 

Select u_campitem_name
from (
SELECT a.[i_pcdef_id], b.[u_campitem_name]
  FROM [mktg_order_discount] a , [mktg_Campaign_item] b 
WHERE a.i_campitem_id = b.i_campitem_id
and b.[b_campitem_active] = 1
) discounts,

(Select p.[i_pcdef_id], d.[u_pc_code]
from
[mktg_promocode_defn] p , [mktg_promocode] d 
Where p.[i_pcdef_id] = d.[i_pcdef_id]
)
promo
where discounts.[i_pcdef_id] = promo.[i_pcdef_id]
and promo.[u_pc_code] = ‘ABCD1234′

Note: replace “ABCD1234” with your coupon code.

Categories
Commerce Server 2009, commerce server 2007
Comments rss
Comments rss
Trackback
Trackback

« »

Leave a Reply