Entity Definition

Logical Name : Reward
Physical Name : TR_RWD

A Reward is an identified, named benefit provided by a retailer or 3rd party to customers in exchange for customer actions (like purchasing products, visiting web sites or stores, referring other customers, renting items, etc.) . ARTS uses a generic term, Reward, so it can represent a variety of retail benefits including promotional discounts, deals, loyalty program incentives, rebate programs, etc. using a consistent set of entitiy types.

Data Definition

Attribute Description Physical Name Domain Data Type Foreign Key Table
RewardID (PK) A token identifier for a Reward instance. ID_RWD Identity integer
EffectiveDateTime The date and time a Reward is active and may be earned and redeemed by customers. DT_EF_DT_TM EffectiveDateTime datetime
ExpirationDateTime The date and time a Reward is no longer active and may NOT be earned and redeemed by customers. DT_EP_DT_TM ExpirationDateTime datetime
RewardDescription Short description of the reward description. DE_RWD_DSCR DescriptionShort varchar(255)
TransactionExclusivityFlag A boolean indicator that if set to TRUE means that this transaction-level reward may be applied one time only item within the scope of a retail transaction. A transaction-level reward is indicated by assigning a value of 'TRANSACTION' to the RewardAttributionLevel attribute. Example: Buy $500 or more in a transaction and earn a bonus of 100 CUSTOMER MEMBER POINTS to your reward balance. Limit 1. FL_TRN_EXCLSVTY Flag integer
RewardAttributionLevelCode A code that assigns this reward's earning and/or redemption VALUE to: LINE_ITEM - The reward value is applied to a single line item or line items that satisfy eligibility requirements and derivation rule thresholds and limits. TRANSACTION - The reward value is applied to a whole transaction, usually the net sale subtotal (which will include previously applied LINE_ITEM price reductions. ACCOUNT - The reward value is applied to an account (applies to deferrable rewards only). This level code attribute is different from the RewardDerivation.RewardDerivationLevelCode The Reward.RewardAttributionLevelCode specifies the OUTPUT level that a reward value will be posted to. The RewardDerivationLevelCode specifies the level whose attributes are used as INPUT into calculating a reward value. CD_RWD_ATTRBTN_LV Code varchar(20)
RewardDispositionCode A code that determines when a reward is to be redeemed. There are two permitted values: INSTANT - reward is redeemed within the scope of the transaction it is earned in DEFERRED_ACCRUED - reward is redeemed at a later time in a subsequent transaction. ACCRUED_TO_INSTANT - accrued rewards are converted from reward currency to money and linked to a RetailPriceModifier and/or PriceModificationLineItem which reflect consuming the reward. Instant rewards apply to coupons, POS markdowns,etc. that create RetailPriceModifiers (line item discounts) and/or PriceModificationLineItem (transaction level discounts). CD_RWD_DPSN Code varchar(20)
RewardLimitAmount The maximum monetary value that is allowed for the Reward This serves as a stop-loss point for retailer discounts tied to a reward in a transaction. Complex reward eligibility rules and derivation rules may result in excessive discounts and this value sets a not to exceed ceiling on the discount amount. MO_RWD_LM_AMT Money decimal(16,5)
RewardIntraTransactionAppliedLimitCount A limit on the number of times a reward may be used within the scope of a transaction. Like RewardLimitAmount, this is a stop-loss rule. For example: Buy 2 SKU 2323 get a third for free - limit 2 REWARDS per transaction. Limits the reward to a total of 6 units of SKU 2323 at 33% discount. Buying a 7th unit or more reverts back to the regular per unit retail price. QY_RWD_INTRA_TRN_APPLD_LM QuantityInteger integer
RewardTransactionLevelActionCode Determines how a Reward is applied to a transaction, a transaction line item or a customer account. This code is related to the RewardDispositionCode. VALID VALUES: NEW_PRICE - base retail price is replaced by the RewardAmount value PCT_OFF - base retail price is reduced by the RewardAmount value which is a PERCENT AMT_OFF - base retail price is reduced by the RewardAmount which is a flat monetary value. EARN_REWARD_CURRENCY - Points, miles,or another retailer designated reward currency units are added to a customer account (note only applies if the customer has an account). REDEEM_REWARD_CURRENCY - Transforms the earned reward currency for the current transaction and, at the customers option, previously accrued reward currency balance into a discount that can be taken at a transaction or line item level CD_RWD_TRN_LV_ACTN Code varchar(20)
TimeGroupID (FK) A unique identifier for a set of consecutive hours on a set of days of the week. e.g. 10am - 2pm Mon, Wed and Fri. ID_GP_TM Identity integer TimeGroup(CO_GP_TM)
RewardShortName A short business name used in reporting to identify this Reward. NM_RWD_SHRT Name varchar(40)
RewardPriorityTieBreaker Value used to break a tie between two equally applicable rewards. The higher value in this attribute for two competing rewards marks reward that will be given. AI_RWD_TI_BRKR LineNumber smallint
SubtotalMinimumAmount The minimum value a transaction subtotal (i.e. a line item group or transaction-level total of line items) must have in order to have a reward applied. Example: Buy $150 or more worth of merchandise in a transaction get a 10% discount. The $150 is the SubtotalMininumAmount. If the customer buys $149.99 worth of merchandise NO DISCOUNT reward is earned. MO_SUBTOT_MNM_AMT Money decimal(16,5)
SubtotalMaximumAmount The maximum subtotal value (for line item groups or transaction totals) that once exceeded ends the reward application. Example Buy $100 to $500 merchandise and receive a 5% discount. The maximum value on which a discount may be given is $500. If a customer purchses $525 worth of merchandise, the discount is capped at $25 (5% of $500). The discount does NOT apply to any amount over the $500. MO_SUBTOT_MXM_AMT Money decimal(16,5)
TransactionLevelRewardAmount The monetary value or percent used in combination with the action taken on a transaction-level subtotal. MO_TRN_LV_RWD_AMT Money decimal(16,5)
RewardTaxTreatmentCode Indicates whether the Reward is taken PRE_TAX or POST_TAX. Pretax means the reward is subtracted from the tax basis of the purchase before calculating tax. Post tax means the reward is calculated after tax is calculated. That means the tax basis is the pre-reward value of the reward items. CD_RWD_TX_TRTMNT Code varchar(20)
PromotionalOfferID (FK) The unique identifier for a promotion. ID_PRM_OFR Identity integer PromotionalOffer(CO_PRM)
PromotionalInitiativeID (FK) A token ID for a retailer's PromotionInitiative instance. ID_PRML_INITV Identity integer PromotionalOffer(CO_PRM)
BuyOneGetOneTriggerFlag Boolean indicator that if TRUE means the reward applies if the total amount of trigger items in the subtotal (line item group or transaction total level) exceed the N value in a buy N get M reward. Enables a BOGO reward pattern at the transaction or line item group level. FL_BOGO_TRGR Flag integer
ExcludePriceOverrideItemFlag Boolean indicator that when TRUE means this reward will exclude any item with a PRICE OVERRIDE. FL_EXCLD_PRC_OVRRD_ITM Flag integer
ExcludeDiscountItemsFlag A boolean indicator that if set to TRUE means that this reward will exclude items that are already discounted. Prevents taking discounts on discounts in a reward. FL_EXCLD_DSC_ITM Flag integer
RewardGroupID (FK) Reward group token ID ID_RWD_GP Identity integer RewardGroup(CO_RWD_GP)


Parent Entity Verb Phrase Child Entity
RewardGroup classifies Reward
PromotionalOffer is implemented as Reward
TimeGroup defines weekly schedule for Reward
Reward RewardMaintenance
Reward is executed based on RewardEligibilityRule
Reward derived by RewardDerivationRule
Reward is applied through PriceModificationLineItem
Reward is presented to customer as RetailTransactionDocument
Reward is instantiated as CustomerReward
Reward identifies reward basis of CustomerRewardLineItem

Logical Views containing Reward

Logical View
Logical 01400 - Item Rewards Derivation - Transaction Level
Logical 04100 - Financial Ledger - Retail Transaction View
Logical 06500 - Time Groups View