IBY_TRXN_SUMMARIES_ALL
IBY_TRXN_SUMMARIES_ALL stores information about each payment request. This table is used for both online and offline payment transactions. The information contained in this table is generic in nature to cover the various payment instruments and the operations on them. The specific details are stored in other detail tables such as IBY_TRXN_CORE, IBY_TRXN_EXTENDED, and IBY_TRXN_FI, based on the type of operation and the payment instrument used for payment.
Details
-
Schema: FUSION
-
Object owner: IBY
-
Object type: TABLE
-
Tablespace: APPS_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
IBY_TRXN_SUMMARIES_ALL_PK |
TRXNMID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| TRXNMID | NUMBER | 18 | Yes | System-generated Identifier | |
| INITIATOR_EXTENSION_ID | NUMBER | 18 | Initiating transaction extension identifier. | ||
| TRANSACTIONID | NUMBER | 18 | Yes | Identifier generated for each payment request, or orapmtreq, and passed back to electronic commerce application | |
| TANGIBLEID | VARCHAR2 | 80 | Yes | Bill or order Identifier | |
| PAYEEID | VARCHAR2 | 80 | Yes | Identifier of the payee passed by electronic commerce application | |
| BEPID | NUMBER | 18 | Payment system Identifier | ||
| MPAYEEID | NUMBER | 18 | Yes | Payee Identifier | |
| ECAPPID | NUMBER | 18 | Yes | Electronic commerce application Identifier | |
| ORG_ID | NUMBER | 18 | Indicates the identifier of the business unit associated to the row. | ||
| PAYMENTMETHODNAME | VARCHAR2 | 80 | Payment method name | ||
| MTANGIBLEID | NUMBER | 18 | Yes | Master identifier that points to IBY_TANGIBLE table for additional information about orders | |
| PAYEEINSTRID | NUMBER | 18 | Instrument Identifier for the payee | ||
| PAYERID | VARCHAR2 | 80 | Payer Identifier | ||
| PAYERINSTRID | NUMBER | 18 | Instrument Identifier for the payer | ||
| DETAILLOOKUP | VARCHAR2 | 30 | Lookup column for CORE, EXTENDED, or FI transactions | ||
| AMOUNT | NUMBER | Transaction amount | |||
| INSTRNUMBER | VARCHAR2 | 60 | Instrument number can be credit card number for 11i in case of unregistered instruments. | ||
| CURRENCYNAMECODE | VARCHAR2 | 15 | Currency code | ||
| STATUS | NUMBER | 18 | Yes | Status of the transaction from lookup type IBY_TRANSACTION_STATUS | |
| UPDATEDATE | DATE | Date that scheduled payment is updated | |||
| TRXNTYPEID | NUMBER | 18 | From lookup type IBY_TRXNTYPES | ||
| ERRORLOCATION | NUMBER | Identifies the location of error | |||
| BEPCODE | VARCHAR2 | 40 | Error code from payment system | ||
| BEPMESSAGE | VARCHAR2 | 255 | Error message from payment system | ||
| BATCHID | VARCHAR2 | 80 | Batch Identifier provided by electronic commerce application | ||
| SETTLEDATE | DATE | Scheduled date for the bill or order used for offline payments only | |||
| MBATCHID | NUMBER | 18 | Batch Identifier | ||
| REQDATE | DATE | Date when the batch operation request is received | |||
| REQTYPE | VARCHAR2 | 20 | Type of request such as closebatch, purgebatch, or openbatch | ||
| REQSEQ | NUMBER | 4 | Request sequence | ||
| DESTURL | VARCHAR2 | 1024 | Payment system URL constructed during schedule of payments | ||
| NLSLANG | VARCHAR2 | 80 | NLS language | ||
| NEEDSUPDT | VARCHAR2 | 3 | Y or N flag indicating that the row has changed status | ||
| OVERALL_SCORE | NUMBER | Overall risk score | |||
| 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. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| INSTRTYPE | VARCHAR2 | 30 | Instrument type | ||
| SECURITY_GROUP_ID | NUMBER | Used in hosted environments | |||
| SALES_REP_PARTY_ID | NUMBER | 18 | Party Identifier for the sales representative who initiated the transaction. Foreign key to HZ_PARTIES | ||
| BEPKEY | VARCHAR2 | 80 | Merchant account Identifier used with this transaction | ||
| CUST_ACCOUNT_ID | NUMBER | 18 | Customer account for which the payment transaction is being processed. Foreign key to HZ_CUST_ACCOUNTS | ||
| INSTRSUBTYPE | VARCHAR2 | 40 | Holds the instrument subtype for the payment transaction | ||
| ECBATCHID | VARCHAR2 | 80 | Used for AR bills receivable integration. RA_BATCHES_ALL.BATCH_ID | ||
| SUB_KEY_ID | NUMBER | Oracle internal use only, reserved for future use | |||
| TRXNREF | VARCHAR2 | 240 | Optional, secondary transaction Identifier to be passed by source application. Useful in distinguishing transactions when the tangible or order ID must be shared | ||
| PROCESS_PROFILE_CODE | VARCHAR2 | 30 | Funds capture process profile | ||
| ORG_TYPE | VARCHAR2 | 30 | Organization type from lookup type IBY_ORGANIZATION_TYPES | ||
| PAYMENT_CHANNEL_CODE | VARCHAR2 | 30 | Payment method | ||
| PROC_REFERENCE_CODE | VARCHAR2 | 240 | Payment processor reference code | ||
| PROC_REFERENCE_AMOUNT | NUMBER | Payment processor reference amount | |||
| INSTRNUM_HASH | VARCHAR2 | 60 | Hash value of the transaction payment instrument number | ||
| INSTRNUM_LENGTH | NUMBER | 3 | Length of the transaction payment instrument | ||
| INSTRNUM_SEC_SEGMENT_ID | NUMBER | 18 | Identifier of the security segment holding the encrypted payment instrument number | ||
| CC_ISSUER_RANGE_ID | NUMBER | 18 | Identifier of the credit card number range the payment instrument number belongs to | ||
| SETTLEMENT_CUSTOMER_REFERENCE | VARCHAR2 | 255 | Settlement customer reference | ||
| ACCT_SITE_USE_ID | NUMBER | 18 | Account site use identifier | ||
| ACCT_SITE_ID | NUMBER | 18 | Identifier of the account site for a funds capture transaction | ||
| FIRST_TRXN_FLAG | VARCHAR2 | 1 | Y or N flag | ||
| LEGAL_ENTITY_ID | NUMBER | 18 | Legal entity identifier | ||
| BILL_TO_ADDRESS_ID | NUMBER | 18 | Identifier of the bill-to address | ||
| FACTORED_FLAG | VARCHAR2 | 1 | Y or N flag that indicates if this payment comes from a third party on behalf of the ultimate payer | ||
| BR_MATURITY_DATE | DATE | Bills receivable maturity date | |||
| SETTLEMENT_DUE_DATE | DATE | Settlement due date | |||
| CALL_APP_SERVICE_REQ_CODE | VARCHAR2 | 255 | External application request set identifier | ||
| BANK_CHARGE_BEARER_CODE | VARCHAR2 | 30 | Bank charge bearer | ||
| DIRDEB_INSTRUCTION_CODE | VARCHAR2 | 30 | Direct debit instruction code | ||
| DEBIT_ADVICE_DELIVERY_METHOD | VARCHAR2 | 30 | Default delivery method set on the payer level for debit notification | ||
| DEBIT_ADVICE_EMAIL | VARCHAR2 | 255 | Debit notification email address | ||
| DEBIT_ADVICE_FAX | VARCHAR2 | 100 | Debit notification fax number | ||
| PAYER_PARTY_ID | NUMBER | 18 | Payer TCA party identifier | ||
| DEBIT_AUTH_FLAG | VARCHAR2 | 1 | Debit authorization flag | ||
| DEBIT_AUTH_METHOD | VARCHAR2 | 30 | Debit authorization method | ||
| DEBIT_AUTH_REFERENCE | VARCHAR2 | 100 | Debit authorization reference | ||
| PAYER_INSTR_ASSIGNMENT_ID | NUMBER | 18 | Instrument assignment identifier for the payer instrument | ||
| PAYER_NOTIFICATION_REQUIRED | VARCHAR2 | 1 | Y or N flag indicating whether payer notification is created for this transaction if it is a settlement | ||
| PAYER_NOTIFICATION_CREATED | VARCHAR2 | 1 | Y or N flag indicating whether payer notification is created for this transaction if it is a settlement | ||
| AR_RECEIPT_METHOD_ID | NUMBER | 18 | Oracle Receivables receipt method | ||
| BR_DRAWEE_ISSUED_FLAG | VARCHAR2 | 1 | Yes or No flag that indicates if the the drawee issues the bill receivable | ||
| BR_SIGNED_FLAG | VARCHAR2 | 1 | Yes or No flag that indicates if the drawee needs to accept the bill receivable | ||
| SALT_VERSION | NUMBER | 2 | Salt version | ||
| DEBIT_AUTHORIZATION_ID | NUMBER | 18 | Debit authorization identifier | ||
| PAYMENT_TYPE_CODE | VARCHAR2 | 30 | Payment type code | ||
| LOGICAL_GROUP_REFERENCE | VARCHAR2 | 30 | Logical group reference | ||
| LOCALINSTR | VARCHAR2 | 35 | Direct debit local instrument. | ||
| SERVICE_LEVEL | VARCHAR2 | 35 | Direct debit service level. | ||
| PURPOSE_CODE | VARCHAR2 | 30 | Payment purpose codes as defined in the ISO20022 external code list. | ||
| SEQ_TYPE | VARCHAR2 | 30 | Payment sequence type. | ||
| ENTERPRISE_ID | NUMBER | 18 | Enterprise Id to support multi-tenancy | ||
| REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| STRUCTURED_PAYMENT_REFERENCE | VARCHAR2 | 256 | An industry standard reference on receivables transactions composed of letters, check digits, and transaction information that is used to identify the transaction, transaction lines, and its payments to the customer and bank. | ||
| EXTERNAL_SETTLEMENT_FLAG | VARCHAR2 | 1 | Indicates whether or not payment is externally settled. Possible values are Y or N. | ||
| TRXN_REQUEST_FORMAT_CODE | VARCHAR2 | 30 | Message format for creating outbound authorization, capture, or refund request. | ||
| TRXN_RESPONSE_FORMAT_CODE | VARCHAR2 | 30 | Message format for reading and processing inbound response received from payment gateway for authorization, capture, or refund request. | ||
| TRXN_REQUEST_ERROR_MESSAGE | VARCHAR2 | 360 | Error raised while initiating outbound request for authorization, capture, or refund request. | ||
| TRXN_RESPONSE_ERROR_MESSAGE | VARCHAR2 | 360 | Error raised while reading and processing inbound response received from payment gateway for authorization, capture, or refund request. | ||
| QUERY_REQUEST_FORMAT_CODE | VARCHAR2 | 30 | Message format for creating outbound request to query the payment gateway. | ||
| QUERY_RESPONSE_FORMAT_CODE | VARCHAR2 | 30 | Message format for reading and processing inbound response received from payment gateway for transaction query. | ||
| QUERY_REQUEST_ERROR_MESSAGE | VARCHAR2 | 360 | Error raised while initiating outbound request to query the payment gateway. | ||
| QUERY_RESPONSE_ERROR_MESSAGE | VARCHAR2 | 360 | Error raised while reading and processing inbound response received from payment gateway for payment query. | ||
| OAUTH_PERFORMED_FLAG | VARCHAR2 | 1 | Indicates whether the transaction supports Oauth. | ||
| INBOUND_FILE_ID | NUMBER | 18 | The identifier of the inbound acknowledgment file. | ||
| ACKNOWLEDGMENT_DATE | DATE | The date when the acknowledgment is processed. | |||
| BEP_STATUS | VARCHAR2 | 30 | Status of the transaction returned by the bank or payment system. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| IBY_TRXN_SUMMARIES_ALL | iby_payee | MPAYEEID |
| IBY_TRXN_SUMMARIES_ALL | iby_cc_issuer_ranges | CC_ISSUER_RANGE_ID |
| IBY_TRXN_SUMMARIES_ALL | iby_security_segments | INSTRNUM_SEC_SEGMENT_ID |
| IBY_TRXN_SUMMARIES_ALL | iby_pmt_instr_uses_all | PAYER_INSTR_ASSIGNMENT_ID |
| IBY_TRXN_SUMMARIES_ALL | ar_receipt_methods | AR_RECEIPT_METHOD_ID |
| IBY_TRXN_SUMMARIES_ALL | iby_batches_all | MBATCHID |
| IBY_TRXN_SUMMARIES_ALL | iby_bepinfo | BEPID |
| IBY_TRXN_SUMMARIES_ALL | iby_tangible | MTANGIBLEID |
| IBY_TRXN_SUMMARIES_ALL | hz_cust_accounts | CUST_ACCOUNT_ID |
| IBY_TRXN_SUMMARIES_ALL | hz_parties | SALES_REP_PARTY_ID |
| iby_trxn_core | iby_trxn_summaries_all | TRXNMID |
| iby_trxn_extensibility | iby_trxn_summaries_all | TRXNMID |
Indexes
| Index | Uniqueness | Tablespace | Columns | Status |
|---|---|---|---|---|
| IBY_TRXN_SUMMARIES_ALL_N10 | Non Unique | Default | CUST_ACCOUNT_ID | |
| IBY_TRXN_SUMMARIES_ALL_N11 | Non Unique | Default | REQDATE | |
| IBY_TRXN_SUMMARIES_ALL_N12 | Non Unique | Default | TRXNREF | Obsolete |
| IBY_TRXN_SUMMARIES_ALL_N13 | Non Unique | Default | INSTRNUM_HASH | Obsolete |
| IBY_TRXN_SUMMARIES_ALL_N14 | Non Unique | Default | MBATCHID | |
| IBY_TRXN_SUMMARIES_ALL_N15 | Non Unique | Default | TRXNTYPEID | |
| IBY_TRXN_SUMMARIES_ALL_N16 | Non Unique | Default | PAYERID, MPAYEEID | |
| IBY_TRXN_SUMMARIES_ALL_N17 | Non Unique | Default | DEBIT_AUTHORIZATION_ID, TRXNTYPEID, STATUS | |
| IBY_TRXN_SUMMARIES_ALL_N7 | Non Unique | Default | INSTRNUMBER | Obsolete |
| IBY_TRXN_SUMMARIES_ALL_N8 | Non Unique | Default | PAYERINSTRID | |
| IBY_TRXN_SUMMARIES_ALL_N9 | Non Unique | Default | BATCHID | |
| IBY_TRXN_SUMM_MTANGIBLEID_N5 | Non Unique | Default | MTANGIBLEID | |
| IBY_TRXN_SUMM_NEEDSUPD_N4 | Non Unique | Default | NEEDSUPDT | Obsolete |
| IBY_TRXN_SUMM_PAYEEID_N6 | Non Unique | Default | PAYEEID | |
| IBY_TRXN_SUMM_STATUS_N3 | Non Unique | Default | STATUS | |
| IBY_TRXN_SUMM_TANGIBLEID_N2 | Non Unique | Default | TANGIBLEID | |
| IBY_TRXN_SUMM_TRANS_ID_N1 | Non Unique | Default | TRANSACTIONID | |
| IBY_TRXN_SUMM_TRXNMID_U1 | Unique | Default | TRXNMID |