LOY_MEM_TIER_INT
Interface table to import Member Tier Data.
Details
-
Schema: FUSION
-
Object owner: LOY
-
Object type: TABLE
-
Tablespace: INTERFACE
Primary Key
| Name | Columns |
|---|---|
|
LOY_MEM_TIER_INT_PK |
BATCH_ID, INTERFACE_ROW_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| BATCH_ID | NUMBER | 18 | Yes | Unique Identifier for a Batch | |
| INTERFACE_ROW_ID | NUMBER | 18 | Yes | Unique identifier of the interface record. Internal use only. | |
| IMPORT_STATUS_CODE | VARCHAR2 | 30 | Status for the data in the interface table | ||
| ACTION_CODE | VARCHAR2 | 30 | Indicates delete action entered by user | ||
| INTERNAL_ACTION_CODE | VARCHAR2 | 30 | This column is created for internal use and will be populated and used by bulk import logic. | ||
| FILE_RECORD_NUM | NUMBER | 18 | Who column: The column is used to store the record number indicator in the csv file. Required by File Import Tool. | ||
| INTERNAL_MEMBER_OVN | NUMBER | 9 | Object Version Number copied from corresponding Member record. This is used for UPDATE cases to see if record is updated by another user during processing of bulk import and throw error appropriately. | ||
| FILE_NAME_SYSTASK_ID | NUMBER | 18 | Who column: The column is used to store SYSTASK_ID for the import activity execution. Required by File Import Tool | ||
| REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Who column: indicates the user who created the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | 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 | Who column: indicates the date and time of the creation of the row. | |||
| LAST_UPDATE_DATE | TIMESTAMP | Who column: indicates the date and time of the last update of the row. | |||
| 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. | |
| MEM_TIER_ID | NUMBER | 18 | Base Primary Key for bulk import purpose only. | ||
| ACTIVE_FLAG | VARCHAR2 | 1 | Active Flag that indicates whether this member tier record is current active tier of the member. A member must have one and only one active tier. | ||
| APPLIED_PROMO_ID | NUMBER | 18 | Tier Promotion which got applied for the tier change | ||
| APPROVED_DT | DATE | Approved date | |||
| APPR_STAT_CODE | VARCHAR2 | 30 | Approved Status Code | ||
| BULK_REF_ID | NUMBER | 18 | Stores the Membership Admin Bulk Action Reference | ||
| COMMENTS | VARCHAR2 | 250 | Tier change reason | ||
| END_DT | TIMESTAMP | End date. | |||
| FUTR_TIER_ID | NUMBER | 18 | Future tier that member can move to | ||
| FUTR_TIER_NAME | VARCHAR2 | 30 | Alternate Key to FUTR_TIER_ID. Tier Class and Program Name will be same. | ||
| FUTR_TIER_STAMP_DT | DATE | Date when the member was assessed for future tier | |||
| LWST_DEM_TIER_ID | NUMBER | 18 | Lowest Demotion Tier Id | ||
| LWST_DEM_TIER_NAME | VARCHAR2 | 30 | Alternate Key to resolve LWST_DEM_TIER_ID. | ||
| MEM_TIER_NUM | VARCHAR2 | 30 | Member Tier Number | ||
| NEXT_ASSESS_DT | DATE | Next tier assessment date | |||
| ORIG_END_DT | TIMESTAMP | Original tier end date, similar to END_DT. This will contain the end date before it is reset during deactivation. Will be needed if the original period needs to be reconstructed during retro processing | |||
| OVERRIDE_DT | DATE | Date to process member tier record on. This overrides the system date if present. Can also be used for manual overrides of what day to process on | |||
| POINT_TYPE_A_VAL | NUMBER | Point type A value | |||
| POINT_TYPE_B_VAL | NUMBER | Point type B value | |||
| POINT_TYPE_C_VAL | NUMBER | Point type C value | |||
| POINT_TYPE_D_VAL | NUMBER | Point type D value | |||
| POINT_TYPE_E_VAL | NUMBER | Point Type E value | |||
| POINT_TYPE_F_VAL | NUMBER | Point Type F value | |||
| POINT_TYPE_G_VAL | NUMBER | Point Type G value | |||
| POINT_TYPE_H_VAL | NUMBER | Point Type H value | |||
| PROCESSED_DT | DATE | Processed date. | |||
| PROCESSING_COMMENT | VARCHAR2 | 1000 | Processing Comment | ||
| PROCESSING_LOG | VARCHAR2 | 1000 | Log output of the tier change | ||
| PROCESSING_STAT_CODE | VARCHAR2 | 30 | Processing Status. | ||
| QUAL_START_DATE | TIMESTAMP | Qualification period start date | |||
| QUAL_END_DATE | TIMESTAMP | Qualification period end date | |||
| QUAL_FLAG | VARCHAR2 | 1 | Determines if the Member is in Requalifying mode for the current tier or Qualifying mode for the next tier. If Y, then Qualifying. If N, then Requalifying | ||
| SEQ_NUM | NUMBER | 9 | Sequence Number. | ||
| START_DT | TIMESTAMP | Start Date. | |||
| SUBMIT_TO_ID | NUMBER | 18 | Submit to Id. | ||
| TIER_CLASS_ID | NUMBER | 18 | Tier Class Id. | ||
| TIER_CLS_NAME | VARCHAR2 | 30 | Alternate key TIER_CLS_NAME. The combination of TIER_CLS_NAME and PROGRAM_NAME identifies the Tier Class | ||
| TIER_ID | NUMBER | 18 | Tier Id. | ||
| TIER_NAME | VARCHAR2 | 30 | Alternate key of TIER_ID. The combination of PROGRAM_NAME, TIER_CLS_NAME and TIER_NAME identifies the tier | ||
| TXN_ID | NUMBER | 18 | Transaction that caused this member tier record to be created (ie the retro transaction that caused this retro member tier record). Used to find processing date information | ||
| TXN_NUM | VARCHAR2 | 30 | Alternate key of transaction id | ||
| ACTION_NOTE | VARCHAR2 | 1000 | Action note | ||
| FROM_TIER_ID | NUMBER | 18 | Tier Id before tier movement | ||
| FROM_TIER_NAME | VARCHAR2 | 30 | Tier name before tier movement, business key of FROM_TIER_ID | ||
| FROM_TIER_START_DATE | DATE | Start date of the From Tier truncated to the day | |||
| TIER_CHANGE_TYPE_CODE | VARCHAR2 | 30 | Tier change type code. It is captured only for OTBI reporting purpose. | ||
| MEMBER_ID | NUMBER | 18 | Reference column to LOY_MEMBER (Primary Key to LOY_MEMBER table) | ||
| MEM_NUM | VARCHAR2 | 30 | Member Number | ||
| PROGRAM_NAME | VARCHAR2 | 50 | Program Name refers to Program Interface table as an alternate key. | ||
| PROMO_NAME | VARCHAR2 | 50 | Alternate key to resolve APPLIED_PROMO_ID. | ||
| BLKAD_SEQ_NUM | NUMBER | SEQ_NUM : Part of user Key . It is a sequence number within a program. Alternate key to resolve BULK_REF_ID. |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| LOY_MEM_TIER_INT_N1 | Non Unique | Default | BATCH_ID, MEMBER_ID, MEM_TIER_NUM |
| LOY_MEM_TIER_INT_U1 | Unique | Default | BATCH_ID, INTERFACE_ROW_ID |