WCS Promotions Query

-- IBM documentation on WCS Promotions data model
-- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmpromo.htm 

-- Total Order level promotion by order id
SELECT amount as "total order level promotion" FROM ORDADJUST a, ORDERS b
where a.orders_id = b.orders_id
and orders_id ='<ORDER_ID>';

-- Get cart item, description and associated promotions at order item level
SELECT d.code as "Promotion Name" , c.amount as "Order item promotion Amount",  
(SELECT partnum from orderitems where orderitems_id in (c.orderitems_id)) as "Part Number",
(select name from catentdesc where catentry_id in (SELECT catentry_id from orderitems where orderitems_id in (c.orderitems_id)) and language_id='-1') as "Item description",
(SELECT catentry_id from orderitems where orderitems_id in (c.orderitems_id)) as "Catentry ID"
FROM ORDADJUST a, ORDERS b, ordiadjust c, calcode d
where a.orders_id = b.orders_id
and a.ordadjust_id = c.ordadjust_id
and a.calcode_id = d.calcode_id
-- Uncomment to get list of Discounts
-- and d.calusage_id = '-1'
-- Uncomment to get list of shipping promotion
-- and d.calusage_id = '-2'
and b.orders_id in ('<ORDER_ID>');

-- Promotion Code
SELECT *
 FROM PX_PROMOTION 
 INNER JOIN PX_CDPROMO ON PX_PROMOTION.PX_PROMOTION_ID=PX_CDPROMO.PX_PROMOTION_ID
 LEFT JOIN PX_CDPOOL ON PX_CDPOOL.PX_CDPOOL_ID=PX_CDPROMO.PX_CDPOOL_ID
WHERE
  Upper(PX_CDPOOL.CODE) IN ('<PROMO_CODE>')
  AND px_promotion.storeent_id= '<STORE_ID>';