Promotion Related DB queries.

--This table contains all the Promotion that is created and it can be identified by the name Under the column "NAME"
select * from PX_PROMOTION where PX_Promotion_id=10000301;

-- This table contains all the promotions that are created and the promotion name is present under "CODE" column
select * from calcode;

-- This table contains all the promotion codes available for the particular store.
select * from px_cdpool;

-- This table contains all the promotion code information for relationships with a promotion.
select * from PX_CDPROMO;

-- This table contains all the promotion code usage information.
select * from PX_CDUSAGE;

-- This table contains all of the coupons issued to individual customers.
select * from PX_COUPON;

-- This table contains promotion description information.
select * from PX_DESCRIPTION ;

-- This table Contains the promotion group information (i.e. under the grpname we can classify the type of promotion it belongs to).
select * from PX_GROUP;

-- This table contains promotion policies (based on the storeent_id we can classify the promotions and also we can get the status of the promotion).
select * from PX_POLICY;

-- This table contains details about how promotions are applied to an order.
select * from PX_PROMOARG;

-- This table contains statistics about promotion usage.
select * from PX_USAGE;

Promotion Data Model and its Life-Cycle

    Promotion Data Model

Promotions

Promotions provide you the ability to offer customers incentives to purchase. WebSphere Commerce supports numerous types of promotions. For example, price promotions that include offering simple discounts; merchandise specials such as gifts with purchase and buy-one-get-one. Additional service promotions are available that include offering reduced shipping costs.

Business users create and manage promotions with the Promotions tool in the Management Center.

Promotion lifecycle

A business user creates a promotion in the inactive state. From that point forward, the promotion can be activated, modified, and deleted.

The following diagram depicts the life-cycle of a promotion.

• When a promotion is first created, it is inactive.
• When an inactive promotion is activated, a new version of that promotion is created and marked active. The original promotion is then marked as obsolete.
• An active promotion can be withdrawn, marking it as inactive.
• Promotions are static. When changes are made to a promotion, a new revision is made. The new revision inherits the state of the promotion before the change. The original promotion is then marked as obsolete.
• Promotions in either active or inactive state can be modified or deleted. The changes take effect immediately after they are made.