Fraska Portal

Exploring the WebSphere Commerce world

WebSphere Commerce, Playing around with the coupon promotion data model

Posted by on in Administration
  • Font size: Larger Smaller
  • Hits: 6585
  • 19 Comments
  • Subscribe to this entry
  • Print

Let' s analyze a specific requirement: create a coupon promotion and assign it without using CMC and web/dialog activity but just querying the DB

This requirement could sounds a bit odd alone but if you think it in terms of promotions integration taking the data from an external 3rd party system it can start to make a bit of sense. 

The CMC authoring promotion tables and the Promotion XML

To accomplish our task, first of all, we need to understand exactly the tables involved during the creation of a coupon promotion. Starting from Info Center documentation we can find the following diagram reviews the main promotions data and their interactions within the promotion use:

b2ap3_thumbnail_promotions_tables.png

 

Basically, there are two main parts: the management center tables and the runtime tables.

The authoring promotions tables (PX_PROMOAUTH, PX_DESCRIPTION, ...) is a set of tables contains data useful to handle the created promotions in the management center console; in this way the business users can view, edit and delete the promotions as they prefer in a simple and fast way with the Promotion Tool.

However, the runtime engine of the store, uses just the promotion XML to evaluate if a promotion is applicable or not for an order. As you can already imagine, the promotion XML- contained in the field PX_PROMOTION.XMLPARAM- is the XML description of the promotion created in Management Center. So, the same information is stored in the MCM promotions tables and in PX_PROMOTION like an XML document. 

An example of coupon promotion

We can create an example of coupon promotion- using Management Center:

b2ap3_thumbnail_Promo_coupon_1.png

This coupon promotion will give 10$ discount. 

Once we use the MCM Promotion Tool and create this same coupon promotion, we can see the following tables updated:

  • Authoring Promotion tables
    • PX_ELEMENT
    • PX_ELEMENTNVP
    • CALCODE
    • CLCDPROMO
    • PX_PROMOAUTH
  • Promotion XML
    • PX_PROMOTION

It's interesting, for example, see the way MCM stores the AmountOff in the table PX_ELEMENTNVP

PX_ELEMENTNVP_ID PX_ELEMENT_ID NAME VALUE OPTCOUNTER
904 602 'AmountOff' '10' 0

Also the PX_PROMOTION is populated. In particular the field PX_PROMOTION.XMLPARAM contains the Promotion XML. Below the Promotion XML generated by the system:

<?xml version="1.0" encoding="UTF-8"?>
<Promotion impl="com.ibm.commerce.marketing.promotion.DefaultPromotion">
   <PromotionKey>
      <PromotionName>10$ off-10000351</PromotionName>
      <StoreKey>
         <DN>ou=aurora,o=seller organization,o=root organization</DN>
         <Identifier>Aurora</Identifier>
      </StoreKey>
      <Version>1</Version>
      <Revision>0</Revision>
   </PromotionKey>
   <PromotionGroupKey>
      <GroupName>OrderLevelPromotion</GroupName>
      <StoreKey>
         <DN>ou=aurora,o=seller organization,o=root organization</DN>
         <Identifier>Aurora</Identifier>
      </StoreKey>
   </PromotionGroupKey>
   <TypedNLDescription impl="com.ibm.commerce.marketing.promotion.TypedNLDescription">
      <DefaultLocale>en_US</DefaultLocale>
      <Description locale="en_US" type="admin">10$ off</Description>
   </TypedNLDescription>
   <Priority>0</Priority>
   <Exclusive>0</Exclusive>
   <ExemptPolicyList />
   <ExplicitlyAppliedPolicyList />
   <Status>1</Status>
   <LastUpdate>11-03-2014 11:53:37</LastUpdate>
   <LastUpdateBy>
      <CustomerKey>
         <LogonId>wcsadmin</LogonId>
      </CustomerKey>
   </LastUpdateBy>
   <PerOrderLimit>2</PerOrderLimit>
   <PerShopperLimit>-1</PerShopperLimit>
   <ApplicationLimit>-1</ApplicationLimit>
   <TargetSales>0.00000</TargetSales>
   <CorrespondingRBDTypeName>OrderLevelValueDiscount</CorrespondingRBDTypeName>
   <Schedule impl="com.ibm.commerce.marketing.promotion.schedule.PromotionSchedule">
      <DateRange impl="com.ibm.commerce.marketing.promotion.schedule.DateRangeSchedule">
         <Start inclusive="true">11-03-2014 06:00:00</Start>
         <End inclusive="true">31-12-9999 23:59:59</End>
      </DateRange>
      <TimeWithinADay impl="com.ibm.commerce.marketing.promotion.schedule.TimeRangeWithinADaySchedule">
         <Start inclusive="true">00:00:00</Start>
         <End inclusive="true">23:59:59</End>
      </TimeWithinADay>
      <Week impl="com.ibm.commerce.marketing.promotion.schedule.WeekDaySchedule">
         <WeekDay>SUNDAY</WeekDay>
         <WeekDay>MONDAY</WeekDay>
         <WeekDay>TUESDAY</WeekDay>
         <WeekDay>WEDNESDAY</WeekDay>
         <WeekDay>THURSDAY</WeekDay>
         <WeekDay>FRIDAY</WeekDay>
         <WeekDay>SATURDAY</WeekDay>
      </Week>
   </Schedule>
   <PromotionType>1</PromotionType>
   <CouponAttribute impl="com.ibm.commerce.marketing.promotion.DefaultCouponAttribute">
      <EffectiveDays>0</EffectiveDays>
      <ExpirationDays>30</ExpirationDays>
      <AllowTransfer>false</AllowTransfer>
   </CouponAttribute>
   <PromotionCodeRequired>false</PromotionCodeRequired>
   <SkipTargetingConditionOnProperPromotionCodeEntered>false</SkipTargetingConditionOnProperPromotionCodeEntered>
   <CheckTargetingConditionAtRuntime>true</CheckTargetingConditionAtRuntime>
   <PromotionCodeCondition impl="com.ibm.commerce.marketing.promotion.condition.PromotionCodeCondition" />
   <Targeting impl="com.ibm.commerce.marketing.promotion.condition.TargetingCondition" />
   <CustomConditions />
   <PurchaseCondition impl="com.ibm.commerce.marketing.promotion.condition.PurchaseCondition">
      <Pattern impl="com.ibm.commerce.marketing.promotion.condition.Pattern">
         <UniqueConstraints>false</UniqueConstraints>
         <Constraint impl="com.ibm.commerce.marketing.promotion.condition.Constraint">
            <WeightedRange impl="com.ibm.commerce.marketing.promotion.condition.WeightedRange">
               <LowerBound>1</LowerBound>
               <UpperBound>-1</UpperBound>
               <Weight>1</Weight>
            </WeightedRange>
            <FilterChain impl="com.ibm.commerce.marketing.promotion.condition.FilterChain">
               <Filter impl="com.ibm.commerce.marketing.promotion.condition.DummyFilter" />
            </FilterChain>
         </Constraint>
      </Pattern>
      <Distribution impl="com.ibm.commerce.marketing.promotion.reward.Distribution">
         <Type>Volume</Type>
         <Base>Cost</Base>
         <Currency>USD</Currency>
         <Range impl="com.ibm.commerce.marketing.promotion.reward.DistributionRange">
            <UpperBound>-1</UpperBound>
            <LowerBound>1</LowerBound>
            <UpperBoundIncluded>false</UpperBoundIncluded>
            <LowerBoundIncluded>true</LowerBoundIncluded>
            <RewardChoice>
               <Reward impl="com.ibm.commerce.marketing.promotion.reward.DefaultReward">
                  <AdjustmentFunction impl="com.ibm.commerce.marketing.promotion.reward.AdjustmentFunction">
                     <FilterChain impl="com.ibm.commerce.marketing.promotion.condition.FilterChain">
                        <Filter impl="com.ibm.commerce.marketing.promotion.condition.DummyFilter" />
                     </FilterChain>
                     <Adjustment impl="com.ibm.commerce.marketing.promotion.reward.FixedAmountOffAdjustment">
                        <AmountOff>10</AmountOff>
                        <Currency>USD</Currency>
                        <AdjustmentType>AllAffectedItems</AdjustmentType>
                     </Adjustment>
                  </AdjustmentFunction>
                  <RewardPolicy>ALL</RewardPolicy>
               </Reward>
            </RewardChoice>
         </Range>
         <PatternFilter impl="com.ibm.commerce.marketing.promotion.condition.DummyPatternFilter" />
      </Distribution>
   </PurchaseCondition>
</Promotion>

The coupon promotion is fully described by this XML; the store, at runtime, will just evaluate the logic reported above (the Authoring Promotion tables won't be considered).

 

Wallet and coupon assignment

Until now we have just discussed about the creation of the coupon promotion. We still need to see how the coupons are assigned to the customers. It's pretty simple:

  • each customer can have a wallet;
  • each possible item of the wallet can be associated with a specific coupon;
  • the coupon can be associated with a coupon promotion (for example, than one we have just created).

In terms of tables, we have:

  • WALLET;
  • WALLETITEM;
  • PX_COUPON.

The OOB way to assign coupons (populating these tables) is through the use of a Web/Dialog Activity. For example, we can create a Dialog Activity assigns a coupon- related to the promotion created previously "Coupon T1 - 10$ off-10004051"- to the new registered customers.  

b2ap3_thumbnail_Promotion_coupon_6.png 

This dialog activity will assign the coupon promotion "10$ off" to the new registered customers.

Activating and running this dialog activity, once we register a new customer, he will get in his personal wallet the coupon promotion:

b2ap3_thumbnail_Promo_coupon_4.png 

 

The tables mentioned before will be populated with the following values:

 

PX_COUPON

PX_COUPON_ID USERS_ID STOREENT_ID VERSION EFFECTIVE EXPIRE STATUS ORDERS_ID GUESTID NAME CODE

100000000001 11002 11351 1 2014-03-11 12:30:07.061 2014-04-10 13:30:07.061 0 NULL NULL '10$ off-10000351' NULL

 

WALLET

WALLET_ID STOREENT_ID MEMBER_ID NAME 

10001 11351 11002 '' 

 

WALLETITEM

WALLETITEM_ID WALLET_ID NAME TYPE UNREAD DELETABLE MOVABLE RENAMABLE CREATIONDATE CONTENT

10001 10001 'Coupon 100000000001' -2 1 1 0 0 2014-03-11 12:30:11.905 '100000000001' 

 

It's interesting the way WALLETITEM and PX_COUPON are bounded with a relationship not based on a classic key-reference but just using the "Content" field value which can contain the Id of the Coupon promotion (Px_Coupon) we need to reference. Same thing happens between PX_COUPON and PX_PROMOTION; the field NAME references a promotion using its name.

 

Create a coupon promotion, querying the DB

Once we understood how the coupon promotion and wallet data are stored in the data base we can go on and create new promotion just querying the DB with the following statements, for example if we need to create same promotion as before but with 15$ OFF (instead of 10$) and assign it to the same customer:

  1. Create the Promotion XML, populating PX_PROMOTION: Create_CouponPromotion_PromotionXML_15$off_Manually_DBQueries.txt;
  2. Populate CMC Promotion Tables: Create_CouponPromotion_CMCTables_15$off_Manually_DBQueries.txt;
  3. Assign the coupon to a customer: Assign_CouponPromotion_10$off_Manually_DBQueries.txt.

The personal wallet of the lucky chosen customer will now have another great coupon:

b2ap3_thumbnail_Promo_coupon_5.png

 

 


Environment:

  • WebSphere Commerce v 7.0.0.6 FEP5
  • WebSphere Application Server 7.0.0.27
  • DB2 9.7
Rate this blog entry:
2

Comments

Leave your comment

Guest
Guest Friday, 17 August 2018

Most Popular Post

WebSphere Commerce, the SOLR extension index
Administration
Rate this blog entry:
4
WebSphere Commerce, Data Load and SOLR Delta Index
Data Load
Rate this blog entry:
0
WebSphere Commerce, FEP 7, SOLR index pre-process error
Administration
Rate this blog entry:
0

Latest Blogs

WebSphere Commerce, CommandLevelAuthorizationCache
Cache
Rate this blog entry:
0
WebSphere Commerce v8, toolkit exception, ClassNotFound db2
Administration
Rate this blog entry:
1
WebSphere Commerce, ATP migration
Store
Rate this blog entry:
0
WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, Performance analysis of few European stores
Performance
Rate this blog entry:
0