LOY_PROGRAM_INT
This Interface table holds Loyalty Program related info for Bulk Import purpose. Mapped to LOY_PRG_FRML, LOY_PROGRAM, LOY_PTSUBTYPE, LOY_RDM_PROD and LOY_PROD_PT.
Details
-
Schema: FUSION
-
Object owner: LOY
-
Object type: TABLE
-
Tablespace: INTERFACE
Primary Key
| Name | Columns |
|---|---|
|
LOY_PROGRAM_INT_PK |
BATCH_ID, INTERFACE_ROW_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| LOY_PROGRAM_INT_ID | NUMBER | 18 | Unique identifier of the Loyalty Program line in the interface table. System generated. | ||
| 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. | ||
| 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 | ||
| LOY_RDM_PROD_ID | NUMBER | 18 | Base Primary Key for ODI mapping purpose. | ||
| LOY_PTSUBTYPE_ID | NUMBER | 18 | Base Primary Key for ODI mapping purpose. | ||
| LOY_PRG_FRML_ID | NUMBER | 18 | Primary Key of Program Forumulae added for ODI Mapping purpose. | ||
| BATCH_ID | NUMBER | 18 | Yes | Unique Identifier for a Batch | |
| IMPORT_STATUS_CODE | VARCHAR2 | 30 | Status for the data in the interface table | ||
| INTERFACE_ROW_ID | NUMBER | 18 | Yes | Unique identifier of the interface record. Internal use only. | |
| 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. | ||
| INTERNAL_PROGRAM_OVN | NUMBER | 9 | Object Version Number copied from corresponding Loyalty Program 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. | ||
| 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. | |
| LOY_PROGRAM_ID | NUMBER | 18 | Reference Column to the LOY_PROGRAM (Primary Key to LOY_PROGRAM table) | ||
| PROGRAM_NAME | VARCHAR2 | 50 | Name of the Program | ||
| BU_ID | NUMBER | 18 | Organization running the program. | ||
| ACTIVE_FLAG | VARCHAR2 | 1 | Indicates if Program is Active. If not, then any transactions for this Program will not be processed and will have error. | ||
| PARTNER_FLAG | VARCHAR2 | 1 | To differentiate Internal Programs from Partner Programs | ||
| END_DT | TIMESTAMP | Program ends at this date | |||
| START_DT | TIMESTAMP | Program starts from this date | |||
| APPLY_CODE | VARCHAR2 | 30 | LOV indicates how to give points when multiple Promotions apply to a transaction. Possible values would be: Apply All Promos/Maximize by Point Type/Maximize by Promotion | ||
| HOST_ORG_ID | NUMBER | 18 | Hosting organization. Could be divisions, company, contractors, partners, etc. | ||
| MEMBER_COUNT | NUMBER | 22 | Number of Members | ||
| MEMBER_SHARED | NUMBER | 22 | Shared Number of Members | ||
| DESC_TEXT | VARCHAR2 | 250 | Description of the Program for informational purpose. | ||
| POINT_EXP_LEVEL_CODE | VARCHAR2 | 30 | Stores the level of point expiry. | ||
| PUR_STRT_MTH_CODE | VARCHAR2 | 30 | Purchase Start Month | ||
| PUR_STRT_DAY_CODE | VARCHAR2 | 30 | Purchase Start Day | ||
| TXFR_STRT_MTH_CODE | VARCHAR2 | 30 | Transfer Start Month | ||
| TXFR_STRT_DAY_CODE | VARCHAR2 | 30 | Transfer Start Day | ||
| PNT_TO_PAY_FLAG | VARCHAR2 | 1 | Whether points can be used to purchase | ||
| REVRECG_PERIOD_DUR | NUMBER | 22 | Revenue Recognition Period | ||
| BAT_RDM_PERD_TP_CODE | VARCHAR2 | 30 | Period Type as Months or Years to be given . This would be from LOV | ||
| BAT_RDM_PERD_DURTN | NUMBER | 22 | Batch Redemption Period duration. For Example, If period type is Years, Duration is 1, then batch redemption would be done once in a year. | ||
| THRSHLD_POINT_VAL | NUMBER | 22 | Minimum Points to be attained to qualify for Batch Redemption | ||
| POINT_TYPE_ID | NUMBER | 18 | Point Type to be used in batch redemption | ||
| REVRECG_PRD_TYP_CODE | VARCHAR2 | 30 | Revenue Recognition Period Type | ||
| DELETED_FLAG | VARCHAR2 | 4 | Indicates if record is soft deleted (Y or N). | ||
| ENROL_BONUS_FLAG | VARCHAR2 | 1 | Flag to indicate if an Enrolment Bonus Transaction is to be created for a new Member at the time of enrollment. | ||
| REFER_BONUS_FLAG | VARCHAR2 | 1 | Flag to indicate if a Referral Bonus Transaction is to be created for an existing referred Member. | ||
| PRIMARY_POINT_TP_ID | NUMBER | 18 | Indicates the Primary Point Type in the program. | ||
| LEGAL_ENTITY | VARCHAR2 | 30 | LEGAL_ENTITY | ||
| PRG_FRML_DESC_TEXT | VARCHAR2 | 1999 | Description | ||
| FRML_EXPR | CLOB | Loyalty Engine Parsable Formula Expression | |||
| FRML_TEXT | CLOB | User Friendly Formula Text | |||
| FRML_NAME | VARCHAR2 | 50 | Formula Name | ||
| PROGRAM_ID | NUMBER | 18 | Program Id | ||
| VALID_FLAG | VARCHAR2 | 1 | Indicates if formula is Valid | ||
| DEFAULT_FLAG | VARCHAR2 | 1 | Default Flag | ||
| LOY_PTSUBTYPE_DESC_TEXT | VARCHAR2 | 100 | Description | ||
| NAME | VARCHAR2 | 50 | Name | ||
| PROD_ID | NUMBER | 18 | Reference to product | ||
| PARTNR_ID | NUMBER | 18 | Reference to Partner | ||
| PROG_POINT_TYPE_ID | NUMBER | 18 | Reference to Point Types of program | ||
| PROD_REDEMPTION_POINT | NUMBER | 9 | Point to be deducted on redeption for a product from a partner under certain program. | ||
| PROD_REDEMP_START_DT | DATE | Effective Start date of the product Available for Redemption. | |||
| PROD_REDEMP_END_DT | DATE | Effective End date of the product Available for Redemption. | |||
| OFFER_TYPE_CODE | VARCHAR2 | 30 | Offer Type associated with product i.e., Accrual or Redemption | ||
| INV_ORG_ID | NUMBER | 18 | Inventory Organization Id. Required for identifying the product name. Inv_Item_Id and Inv_org_id is a composite key of EGP_SYSTEM_ITEMS_VL. |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| LOY_PROGRAM_INT_U1 | Unique | Default | BATCH_ID, INTERFACE_ROW_ID |