LOY_TXN_STG
Loyalty Transaction Staging table
Details
-
Schema: FUSION
-
Object owner: LOY
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
LOY_TXN_STG_PK |
LOY_TXN_STG_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| LOY_TXN_STG_ID | NUMBER | 18 | Yes | Who column | |
| 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). | |
| ACCR_RULE_ID | NUMBER | 18 | Accrual Rule applied to this transaction | ||
| ACTIVITY_DAY | NUMBER | Day derived from TXN_DT | |||
| ACTIVITY_HOUR | NUMBER | Hour dervied from TXN_DT | |||
| ADJ_LIST_PRI | NUMBER | ADJ_LIST_PRI | |||
| AMT_VAL | NUMBER | Amount for the transaction | |||
| APPROVE_DT | TIMESTAMP | approve date. | |||
| APPROVE_ID | NUMBER | 18 | Approve Id. | ||
| BID_FLAG | VARCHAR2 | 1 | Yes | Bid Flag | |
| BOOKING_DT | TIMESTAMP | Booking Date | |||
| BOOK_CLASS_CODE | VARCHAR2 | 30 | Book Class. | ||
| BULK_REF_ID | NUMBER | 18 | Reference Member Admin Bulk Actions - History Table | ||
| BU_ID | NUMBER | 18 | Yes | BU_ID | |
| CANCELLED_TXN_ID | NUMBER | 18 | Cancelled transaction Code. | ||
| CANC_REASON_CODE | VARCHAR2 | 30 | Cancellation Reason code | ||
| CHILD_TXN_ID | NUMBER | 18 | Child Transaction Id | ||
| COMMENTS | VARCHAR2 | 250 | COMMENTS | ||
| CORP_REF_NUM | VARCHAR2 | 15 | Corporate Reference Number for Joint Accruals | ||
| CURRENCY_CODE | VARCHAR2 | 15 | Currency Code for the loyalty transaction. | ||
| CURCY_EXCH_DT | TIMESTAMP | Currency Exchange date. | |||
| DEST_AIRPORT_CODE | VARCHAR2 | 30 | destination Airport Code. | ||
| DEST_ZONE_CODE | VARCHAR2 | 30 | Destination Zone | ||
| DOC_NUM | VARCHAR2 | 30 | document number. | ||
| END_DT | TIMESTAMP | End date. | |||
| EXPIRE_IN_DAYS | NUMBER | Expiry in date. | |||
| EXTERNAL_FLAG | VARCHAR2 | 1 | Yes | external Flag. | |
| EXT_RES_NUM | VARCHAR2 | 250 | External Residence Number. | ||
| FARE_BASIS | VARCHAR2 | 30 | Fare Basis | ||
| FARE_BASIS_CODE | VARCHAR2 | 30 | Fare Basis | ||
| FARE_TYPE_CODE | VARCHAR2 | 30 | Fare type. | ||
| FLT_NUM | VARCHAR2 | 30 | FLT_NUM | ||
| FLT_TYPE_CODE | VARCHAR2 | 30 | FLT_TYPE_CODE | ||
| GDS_REC_LOCATOR | VARCHAR2 | 30 | GDS_REC_LOCATOR | ||
| INTEGRATION_ID | VARCHAR2 | 30 | Identifier used for integration with external systems | ||
| INTERNAL_DEST | VARCHAR2 | 15 | Internal Destination. | ||
| INTERNAL_FLIGHT_DT | TIMESTAMP | Internal Flight date. | |||
| INTERNAL_ORIGIN | VARCHAR2 | 15 | Internal Origin | ||
| ITEM_NUM | VARCHAR2 | 30 | item Number. | ||
| LOAN_BALANCE | NUMBER | LOAN BALANCE | |||
| LOAN_REPAYMENT_DT | TIMESTAMP | Loan repayment date. | |||
| LOCATION_CODE | VARCHAR2 | 30 | Location code | ||
| LOC_ID | NUMBER | 18 | Location Id. | ||
| MEMBER_ID | NUMBER | 18 | Member id. | ||
| MKTG_FLT_NUM | VARCHAR2 | 30 | Marketing flight number | ||
| NUM_POINTS | NUMBER | Number points. | |||
| OPER_FLT_NUM | VARCHAR2 | 30 | Operating flight number | ||
| OPTY_COST | NUMBER | OPTY_COST | |||
| ORDER_NUM | VARCHAR2 | 30 | Order Number sent by POS system. Will be unique. Cannot use Orig Order Id column in this table as this has foreign key to order table. | ||
| ORDER_QTY | NUMBER | Sales Order Line Quantity | |||
| ORIG_ACRL_REF_ID | NUMBER | 18 | Original Accrual reference from the external system - used in case of accrual dispute for linking the original accrual | ||
| ORIG_AIRPORT_CODE | VARCHAR2 | 30 | origin Airport | ||
| ORIG_AMOUNT | NUMBER | Original Amount | |||
| ORIG_ORDER_ID | NUMBER | 18 | Original Order Id that lead to the creation of these transactions | ||
| ORIG_ORD_ITM_ID | NUMBER | 18 | Original Order Line Item for which this transaction was created | ||
| ORIG_POINTS | NUMBER | Original points | |||
| ORIG_ZONE_CODE | VARCHAR2 | 30 | Origin Zone. | ||
| OVR_DUP_CHECK_FLAG | VARCHAR2 | 1 | Yes | Indicates if duplicate check has been overridden | |
| OVR_PRI_FLAG | VARCHAR2 | 1 | Yes | Indicates if price has been overridden | |
| OWNER_ID | NUMBER | 18 | Owner Id. | ||
| PACKAGE_CODE | VARCHAR2 | 30 | Package Code in case the product is a Part of a Packaged Product. | ||
| PARTITION_NAME | VARCHAR2 | 30 | Partition Name. | ||
| PARTNER_ID | NUMBER | 18 | Partition Id. | ||
| PAR_TXN_ID | NUMBER | 18 | PAR_TXN_ID | ||
| PERIOD_DUR_CODE | VARCHAR2 | 30 | For storing the Period duration of the Lounge during the lounge Purchase member service | ||
| PERIOD_TYPE_CODE | VARCHAR2 | 30 | For storing the Period type (MONTHS / DAYS) of the Lounge during the lounge Purchase member service | ||
| PERSON_ID | NUMBER | 18 | Person Id. | ||
| PNR_NAME | VARCHAR2 | 50 | PNR_NAME | ||
| POINT_TYPE_ID | NUMBER | 18 | Point type id. | ||
| POST_DT | TIMESTAMP | post date. | |||
| PROCESSING_COMMENT | VARCHAR2 | 1000 | PROCESSING COMMENT | ||
| PROCESSING_LOG | VARCHAR2 | 1000 | PROCESSING LOG | ||
| PROCESS_DT | TIMESTAMP | Process date. | |||
| PROD_ID | NUMBER | 18 | PROD_ID | ||
| PROG_ID | NUMBER | 18 | Program Id. | ||
| PTNR_PROC_DT | TIMESTAMP | PTNR_PROC_DT | |||
| PTNR_STATUS_CODE | VARCHAR2 | 30 | PTNR_STATUS_CODE | ||
| PTNR_USER_ID | NUMBER | 18 | PTNR_USER_ID | ||
| PYMT_TYPE_CODE | VARCHAR2 | 30 | Payment Type Code for Redemption Payment type code | ||
| QUAL_FLAG | VARCHAR2 | 1 | Yes | QUAL_FLAG | |
| RDM_ITM_ID | NUMBER | 18 | Redemption Request Number | ||
| RDM_QTY | NUMBER | Redemption Quantity | |||
| RDM_QTY_UOM_CODE | VARCHAR2 | 30 | Unit Of Measure of the redemption quantity | ||
| REASON_CODE | VARCHAR2 | 30 | Reason Code for Partner Transactions | ||
| REFUND_BASIS_CODE | VARCHAR2 | 30 | Refund Basis e.g., Prorata, Percentage, No refund etc. | ||
| REFUND_PCT | NUMBER | Refund Percentage | |||
| RFND_OVRD_CMNTS | VARCHAR2 | 250 | Refund Override comments Used in case of Membership Cancellation | ||
| RFND_OVRD_RSN_CODE | VARCHAR2 | 30 | Refund Override Reason | ||
| SOURCE_CODE | VARCHAR2 | 30 | This column will signify the source. Valid values would be Order Management, Retail POS | ||
| START_DT | TIMESTAMP | Start date. | |||
| STATUS_CODE | VARCHAR2 | 30 | Yes | Status. | |
| SUBMIT_DT | TIMESTAMP | Submit date. | |||
| SUBMIT_TO_ID | NUMBER | 18 | Submit to id. | ||
| SUB_STATUS_CODE | VARCHAR2 | 30 | SUB_STATUS_CODE | ||
| SUB_TYPE_CODE | VARCHAR2 | 30 | Yes | SUB_TYPE_CODE | |
| TAX_AMT | NUMBER | Tax amount assocaited with the Service Transaction | |||
| TICKET_NUM | VARCHAR2 | 30 | Ticket Number. | ||
| TOTAL_LINES | NUMBER | Total Lines in Sales Order | |||
| TO_MEM_NUM | VARCHAR2 | 30 | Recipient Membership number Used in case of Member Points Transfer | ||
| TRANSFER_MEM_ID | NUMBER | 18 | The member whom the points are transferred to or from. | ||
| TRANSFER_TXN_ID | NUMBER | 18 | Transfer Transaction Id | ||
| TRVL_DISTANCE | NUMBER | Travel Distance - Used in case of Air Product | |||
| TRV_CLASS_CODE | VARCHAR2 | 30 | TRV_CLASS_CODE | ||
| TXN_CHANNEL_CODE | VARCHAR2 | 30 | Transaction channel Code. | ||
| TXN_DT | TIMESTAMP | Transaction Code. | |||
| TXN_NUM | VARCHAR2 | 30 | Yes | Transaction Code. | |
| TXN_SOURCE_CODE | VARCHAR2 | 30 | Air, Hotel, Car Rental, Others | ||
| TYPE_CODE | VARCHAR2 | 30 | Yes | Type Code. | |
| UNACC_MINOR_FLAG | VARCHAR2 | 1 | Yes | UNACC_MINOR_FLAG | |
| VAL_STATUS_CODE | VARCHAR2 | 30 | Identifier which stores the status of the validation performed on the record | ||
| VOUCHER_ID | NUMBER | 18 | Voucher Id. | ||
| VOUCHER_QTY | NUMBER | Voucher Quantity. | |||
| VOUCHER_TYPE_ID | NUMBER | 18 | Voucher type Id. | ||
| 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. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| LOY_TXN_STG | loy_acrl_rule | ACCR_RULE_ID |
| LOY_TXN_STG | loy_member | TRANSFER_MEM_ID |
| LOY_TXN_STG | loy_txn_stg | TRANSFER_TXN_ID |
| LOY_TXN_STG | loy_mem_blkad | BULK_REF_ID |
| LOY_TXN_STG | loy_txn_stg | CANCELLED_TXN_ID |
| LOY_TXN_STG | loy_txn_stg | CHILD_TXN_ID |
| LOY_TXN_STG | loy_member | MEMBER_ID |
| LOY_TXN_STG | loy_txn_stg | ORIG_ACRL_REF_ID |
| LOY_TXN_STG | loy_txn_stg | PAR_TXN_ID |
| LOY_TXN_STG | loy_attrdefn_b | POINT_TYPE_ID |
| LOY_TXN_STG | loy_program | PROG_ID |
| loy_txn_stg | loy_txn_stg | TRANSFER_TXN_ID |
| loy_txn_stg | loy_txn_stg | CANCELLED_TXN_ID |
| loy_txn_stg | loy_txn_stg | CHILD_TXN_ID |
| loy_txn_stg | loy_txn_stg | ORIG_ACRL_REF_ID |
| loy_txn_stg | loy_txn_stg | PAR_TXN_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| LOY_TXN_STG_FK1 | Non Unique | FUSION_TS_TX_DATA | ACCR_RULE_ID |
| LOY_TXN_STG_FK10 | Non Unique | FUSION_TS_TX_DATA | TRANSFER_MEM_ID |
| LOY_TXN_STG_FK11 | Non Unique | FUSION_TS_TX_DATA | TRANSFER_TXN_ID |
| LOY_TXN_STG_FK12 | Non Unique | FUSION_TS_TX_DATA | VOUCHER_ID |
| LOY_TXN_STG_FK2 | Non Unique | FUSION_TS_TX_DATA | BULK_REF_ID |
| LOY_TXN_STG_FK3 | Non Unique | FUSION_TS_TX_DATA | CANCELLED_TXN_ID |
| LOY_TXN_STG_FK4 | Non Unique | FUSION_TS_TX_DATA | CHILD_TXN_ID |
| LOY_TXN_STG_FK5 | Non Unique | FUSION_TS_TX_DATA | MEMBER_ID |
| LOY_TXN_STG_FK6 | Non Unique | FUSION_TS_TX_DATA | ORIG_ACRL_REF_ID |
| LOY_TXN_STG_FK7 | Non Unique | FUSION_TS_TX_DATA | PAR_TXN_ID |
| LOY_TXN_STG_FK8 | Non Unique | FUSION_TS_TX_DATA | POINT_TYPE_ID |
| LOY_TXN_STG_FK9 | Non Unique | FUSION_TS_TX_DATA | PROG_ID |
| LOY_TXN_STG_U1 | Unique | FUSION_TS_TX_DATA | LOY_TXN_STG_ID |