LOY_TIER
This table has the Loyalty Tier information.
Details
-
Schema: FUSION
-
Object owner: LOY
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
LOY_TIER_PK |
LOY_TIER_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| LOY_TIER_ID | NUMBER | 18 | Yes | Who Column: Primary Key. | |
| CARD_DESC_TEXT | VARCHAR2 | 250 | Card Description. | ||
| CARD_TYPE_CODE | VARCHAR2 | 30 | Membership Card Type. Can be Regular, Silver, Gold etc. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| DEM_TIER_ID | NUMBER | 18 | DEM_TIER_ID | ||
| DESC_TEXT | VARCHAR2 | 250 | Descriptive Text. | ||
| DWNGRD_APPRV_FLAG | VARCHAR2 | 1 | Yes | Downgrade approval required flag used in batch processing. | |
| EXPIRE_POINTS_FLAG | VARCHAR2 | 1 | Yes | Flag if points do not expire while a member belongs to this tier. | |
| EXP_SNAPTO_CODE | VARCHAR2 | 30 | Indicates the day to which the expiration date should snap to e.g. End of Month, 1st day of quarter etc.. | ||
| EXTENSION_DAYS_NUM | NUMBER | The point expiration extension in days defined on tier level. It can be 30,60, 365 etc. e.g. Your Mileage Plus miles don't expire provided you earn or redeem any amount of miles at least once every 365 days. | |||
| GRACE_PERIOD_LEN | NUMBER | Grace period length. | |||
| GRACE_PERIOD_UOM | VARCHAR2 | 30 | GRACE_PERIOD_UOM | ||
| LOUNGE_CODE | VARCHAR2 | 30 | Lounge type, can be silver, gold etc. | ||
| LOUNGE_DESC_TEXT | VARCHAR2 | 250 | Lounge Description. | ||
| LWST_DEM_TIER_ID | NUMBER | 18 | Lowest Demotion Tier Id | ||
| NAME | VARCHAR2 | 30 | Yes | Name. | |
| NON_QUAL_EXP_LEN | NUMBER | NON_QUAL_EXP_LEN | |||
| TIER_CLASS_ID | NUMBER | 18 | Yes | Tier Class Identification. | |
| NON_QUAL_EXP_UOM | VARCHAR2 | 30 | NON_QUAL_EXP_UOM | ||
| ONLY_ACTIVE_FLAG | VARCHAR2 | 1 | Yes | When this flag is checked a statement will only be created and sent if there was activity during the statement period. | |
| POINT_TYPE_A_REQ | NUMBER | The number of points required to reach this tier from the next lowest tier. This value does not have any effect on how the rules are actually calculated. | |||
| POINT_TYPE_B_REQ | NUMBER | The number of points required to reach this tier from the next lowest tier. This value does not have any effect on how the rules are actually calculated. | |||
| POINT_TYPE_C_REQ | NUMBER | The number of points required to reach this tier from the next lowest tier. This value does not have any effect on how the rules are actually calculated. | |||
| POINT_TYPE_D_REQ | NUMBER | The number of points required to reach this tier from the next lowest tier. This value does not have any effect on how the rules are actually calculated. | |||
| QUAL_EXP_LEN | NUMBER | QUAL_EXP_LEN | |||
| QUAL_EXP_UOM | VARCHAR2 | 30 | QUAL_EXP_UOM | ||
| QUAL_PERIOD_LEN | NUMBER | QUAL_PERIOD_LEN | |||
| QUAL_PERIOD_UOM | VARCHAR2 | 30 | QUAL_PERIOD_UOM | ||
| REQUAL_PERIOD_LEN | NUMBER | Length of requalifying period. | |||
| REQUAL_PERIOD_UOM | VARCHAR2 | 30 | Requalifying period unit of measure. | ||
| SEQ_NUM | NUMBER | Yes | Sequence number to track tier order. | ||
| STMT_FREQ_CODE | VARCHAR2 | 30 | The frequency of when a statement is send. e.g. Quarterly, Annually, etc) | ||
| SHOW_FLAG | VARCHAR2 | 1 | Yes | Flag indicating whether to show the tier record to member. | |
| 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. | |
| QUAL_POINTS | NUMBER | 22 | Qualifying points of Tier |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| LOY_TIER | loy_tier | LWST_DEM_TIER_ID |
| LOY_TIER | loy_tier | DEM_TIER_ID |
| LOY_TIER | loy_tier_cls | TIER_CLASS_ID |
| loy_tier | loy_tier | LWST_DEM_TIER_ID |
| loy_tier | loy_tier | DEM_TIER_ID |
| loy_member | loy_tier | PR_DOM_TIER_ID |
| loy_actcrtr | loy_tier | TIER_ID |
| loy_mem_status | loy_tier | TIER_ID |
| loy_promo | loy_tier | TIER_ID |
| loy_card | loy_tier | TIER_ID |
| loy_prog_neg_point | loy_tier | TIER_ID |
| loy_mem_tier | loy_tier | FUTR_TIER_ID |
| loy_mem_tier | loy_tier | LWST_DEM_TIER_ID |
| loy_mem_tier | loy_tier | TIER_ID |
| loy_mem_tier | loy_tier | FROM_TIER_ID |
| loy_promo_tr | loy_tier | TIER_ID |
| loy_tier_cls | loy_tier | PR_TIER_ID |
| loy_mem_blkad | loy_tier | NEW_TIER_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| LOY_TIER_F1 | Non Unique | Default | LWST_DEM_TIER_ID |
| LOY_TIER_F2 | Non Unique | Default | DEM_TIER_ID |
| LOY_TIER_U1 | Unique | FUSION_TS_TX_DATA | LOY_TIER_ID |
| LOY_TIER_U2 | Unique | FUSION_TS_TX_DATA | TIER_CLASS_ID, NAME, CONFLICT_ID |