LOY_PROMO
this is the Loyalty Promotion table
Details
-
Schema: FUSION
-
Object owner: LOY
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
LOY_PROMO_PK |
LOY_PROMO_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| LOY_PROMO_ID | NUMBER | 18 | Yes | Who column. | |
| ENROLL_ATTR_NAME | VARCHAR2 | 30 | It is promotion enrollment attribute name. A free text attribute to capture the name of enrollment attribute provided by the user. e.g. Incentive choice, Enrollment promotion incentive choice or so. The only thing is that the name will revolve around incentive choice and it will give flexibility to the user to provide the customer preferred attribute name without touching program. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| CONFLICT_ID | NUMBER | 18 | Yes | Disconnected Mobile: this value is used to guarantee the uniqueness of the row when duplicates are created in different databases (i.e. mobile databases or the server). | |
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| ACTIVE_FLAG | VARCHAR2 | 1 | Yes | Whether the Promotion is active or not. | |
| ADMIN_FLAG | VARCHAR2 | 1 | Yes | Flag indicating whether the promotion is an admin promotion or not. | |
| ALWAYS_APPLY_FLAG | VARCHAR2 | 1 | Yes | Indicates if the points from this Promotion should aways be given to the member irrespective of the Apply Code on the Program. | |
| APPLY_TO_TIERS_FLAG | VARCHAR2 | 1 | Yes | Indicates whether this Promotion is applicable to some limited number of tier. If it is applicable to any specific one or more tier (not all tiers) then this flag will be true (Y) else it will be false (N) by default. In case when the Promotion is applicable to all tiers, this falg will be defaulted to false (N). Once this flag is true (Y) that means that many Promotion and Tier mapping need to be created into S_LOY_PROMO_TR intersection table. There is no entry if all the possible tiers are applicable to a promotion. | |
| AVL_TO_PROCFLW_FLAG | VARCHAR2 | 1 | Yes | Flag indicating whether the promotion can be used in Process Flow (Flowcharts) or not. | |
| BASE_PROMO_FLAG | VARCHAR2 | 1 | Yes | Indicates whether promotion is a Base Promotion or not | |
| BU_ID | NUMBER | 18 | Yes | The primary organization this promotion belongs. | |
| DESC_TEXT | VARCHAR2 | 250 | Descriptive text about the Promotion. | ||
| EFF_VCHR_PRD_TP_CODE | VARCHAR2 | 30 | Effective voucher period type such as year, month etc. | ||
| EFF_VCHR_PRD_VAL | VARCHAR2 | 15 | Effective Voucher Period value | ||
| END_DT | TIMESTAMP | This Promotion applies to transactions before this end date only. | |||
| EXPORT_FLAG | VARCHAR2 | 1 | Yes | indicates whether this promotion needs to be exported or not | |
| FINAL_EXEC_DT | TIMESTAMP | Time at which to run the Qualifying Rule if any. | |||
| LOY_PROG_ID | NUMBER | 18 | Yes | Program to which this Promotion belongs. | |
| MKTG_OFFR_ID | NUMBER | 18 | Marketing Offer | ||
| NAME | VARCHAR2 | 50 | Yes | Name of the Promotion. | |
| NUM_TIMES | NUMBER | Number of times a member can qualify for a promotion. 0 means infinite times. | |||
| OPTIN_END_DT | TIMESTAMP | Date until which members can opt-in into the Promotion. | |||
| OPTIN_START_DT | TIMESTAMP | Date after which members can opt-in into the Promotion. | |||
| OPT_IN_FLAG | VARCHAR2 | 1 | Yes | indicates if members should have opted in for this Promotion. If selected, then only transactions for Members who are added to the S_LOY_PROMO_MEM intersection table will be considered for this Promotion. | |
| ORDER_FLAG | VARCHAR2 | 1 | Yes | Flag at the Promotion Attribute level to indicate if the Attribute is being grouped by the Order Number- Retail Requirement. | |
| PARTNER_ID | NUMBER | 18 | Partner with whom this Promotion is being run. When a transaction qualifies for this Promotion, points will be deducted from this Partners Point Block. | ||
| PKG_ACRL_FLAG | VARCHAR2 | 1 | Yes | indicates if the promotion is defined for package accrual | |
| POINT_LIMIT_CODE | VARCHAR2 | 30 | Point limit code. | ||
| PROD_INCLUSION_CODE | VARCHAR2 | 30 | indicates which Products this Promotion applies to. Possible values: All Products/Include Products/Exclude Products. Include Products/Exclude Products means that the Products selected in the S_LOY_PROMO_PROD intersection table should all be either Excluded or Included. | ||
| PROMO_NUM | VARCHAR2 | 30 | Yes | Public unique identifier for promotion. | |
| PR_PROMO_LOGO_ID | NUMBER | 18 | Promotion primary logo id | ||
| PTNR_PROC_DT | TIMESTAMP | Date-Time Partner reviewed Promotion and approved/rejected | |||
| PTNR_RVW_STAT_CODE | VARCHAR2 | 30 | Partner review status of the promotion | ||
| PTNR_USER_ID | NUMBER | 18 | Login of the partner that approved/rejected the promotion | ||
| RULE_MODEL_CODE | VARCHAR2 | 30 | Identifies whether the promotion would have hierarchical rules or standard rules | ||
| SM_DESC_TEXT | VARCHAR2 | 250 | Promotion Description that will be published on the social media. | ||
| SRC_ID | NUMBER | 18 | Foreign key to Campaign to tie Loyalty with Siebel Marketing | ||
| SRM_REQ_ID | NUMBER | 18 | Foreign Key to the Repeating Server Request if Time is specified. | ||
| START_DT | TIMESTAMP | This Promotion applies to transactions after this start date only. | |||
| SUB_TYPE_CODE | VARCHAR2 | 30 | Sub Type of the Promotion | ||
| TIER_ID | NUMBER | 18 | Tier for which this Promotion is being run. Required in the case of Tier Promotions and optional in the case of Transaction Promotions. | ||
| TYPE_CODE | VARCHAR2 | 30 | Tier Promotion/Transaction Promotion | ||
| MEM_INCLUSION_ID | VARCHAR2 | 30 | MEM_INCLUSION_ID | ||
| TIER_INCLUSION_ID | VARCHAR2 | 32 | TIER_INCLUSION_ID |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| LOY_PROMO | loy_program | LOY_PROG_ID |
| LOY_PROMO | loy_promo_att | PR_PROMO_LOGO_ID |
| LOY_PROMO | loy_tier | TIER_ID |
| loy_prog_point_block | loy_promo | PROMO_ID |
| loy_attr_itm | loy_promo | PROMO_ID |
| loy_acrl_itm | loy_promo | PROMO_ID |
| loy_aggr_point | loy_promo | PROMO_ID |
| loy_attrdefn_b | loy_promo | PROMOTION_ID |
| loy_incentive_choice | loy_promo | PROMO_ID |
| loy_promo_bu | loy_promo | PROMO_ID |
| loy_promo_x | loy_promo | PAR_ROW_ID |
| loy_rdm_itm | loy_promo | PROMO_ID |
| loy_mem_vchr | loy_promo | PROMO_ID |
| loy_promo_rl | loy_promo | PROMO_ID |
| loy_mem_tier | loy_promo | APPLIED_PROMO_ID |
| loy_txn | loy_promo | ENROL_PROMO_ID |
| loy_promo_tr | loy_promo | PROMOTION_ID |
| loy_promo_atr | loy_promo | LOY_PROMO_ID |
| loy_promo_att | loy_promo | PAR_ROW_ID |
| loy_bucket | loy_promo | PROMOTION_ID |
| loy_promomem | loy_promo | PROMO_ID |
| loy_mem_blkad | loy_promo | ENROL_PROMO_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| LOY_PROMO_FK1 | Non Unique | FUSION_TS_TX_DATA | LOY_PROG_ID |
| LOY_PROMO_FK2 | Non Unique | FUSION_TS_TX_DATA | PR_PROMO_LOGO_ID |
| LOY_PROMO_FK3 | Non Unique | FUSION_TS_TX_DATA | TIER_ID |
| LOY_PROMO_N1 | Non Unique | Default | LAST_UPDATE_DATE |
| LOY_PROMO_U1 | Unique | FUSION_TS_TX_DATA | LOY_PROMO_ID |
| LOY_PROMO_U2 | Unique | Default | NAME, LOY_PROG_ID, CONFLICT_ID |
| LOY_PROMO_U3 | Unique | Default | PROMO_NUM |