PSC_BNP_PAYMENT_HIST_HDR
This table contains historical payment header data for all payment methods
Details
-
Schema: FUSION
-
Object owner: PSC_CC
-
Object type: TABLE
-
Tablespace: TRANSACTION_TABLES
Primary Key
| Name | Columns |
|---|---|
|
PSC_BNP_PAYMENT_HIST_HDR_PK |
PAYMENT_HISTORY_KEY |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments | Status |
|---|---|---|---|---|---|---|
| PAYMENT_REFERENCE_ID | VARCHAR2 | 50 | Yes | Reference value created by the Payment Engine and sent as a pass thru data to the Payment Processor, Trust Account and Cash Drawer. | Active | |
| PAYMENT_HEADER_KEY | NUMBER | 18 | Indicates the primary key for a payment received in a third party cashiering system. | |||
| PAYMENT_HEADER_ID | VARCHAR2 | 50 | Identifies the alphanumeric identifier to the third party payment received by a cashiering solution. | |||
| EXT_PAYMENT_REFERENCE | VARCHAR2 | 50 | Identifies the payment reference provided by third party cashiering solution. | |||
| THIRD_PARTY_PAY_FLAG | VARCHAR2 | 1 | Yes | Identifies payments done in a third party cashiering system. | ||
| PAYMENT_DATE | DATE | Yes | Date in which the payment transaction occurred. | Active | ||
| PAYMENT_METHOD_CODE | VARCHAR2 | 30 | Yes | Method of Payment used in this payment transaction. The Payment methods Cash, Personal Check, Cashier???s Check, Email Order are collectively referred to as Cash Drawer. The Cash Drawer Payment Methods List is stored in the Cash Drawer History table. | Active | |
| PAYMENT_CURRENCY | VARCHAR2 | 3 | Yes | Currency Code used for Payment transaction. | Active | |
| PAYMENT_AMOUNT | NUMBER | Yes | Total Payment Amount authorized and settled by Payment Gateway and verified by the Trust Account System. | Active | ||
| AMOUNT_DUE | NUMBER | Yes | Total payment amount due that is sum total of amount on payment history lines. | |||
| PAYMENT_ADAPTER_ID | VARCHAR2 | 100 | The Adapter ID for the Payment Method. This value is expected only from Credit Card, Electronic Check, PayPal and Trust Account Payment. | Active | ||
| PAYMENT_TRANSACTION_ID | VARCHAR2 | 50 | Yes | This is a Transaction ID received from the Payment Gateway or from the Trust Account System. | Active | |
| CREDIT_CARD_TYPE | VARCHAR2 | 4 | The type of Credit Card used, for instance, Mastercard, Visa, etc. | Active | ||
| CREDIT_CARD_DIGITS | NUMBER | 4 | Last four digits of the credit card number used for payment. | Active | ||
| TRUST_ACCOUNT_NUMBER | VARCHAR2 | 30 | Attribute identifying the Logical Trust Account Number. | Active | ||
| SERVICE_CHARGE_AMOUNT | NUMBER | Convenience fee assessed by the Department for using a specific Payment Method. | Active | |||
| SERVICE_CHARGE_CURRENCY | VARCHAR2 | 3 | Currency used for the Service Charge. Typically this is the same currency used as Payment Currency. | Active | ||
| AUTHORIZATION_STATUS | VARCHAR2 | 4 | Yes | This Status value is returned by Payment Gateway or Trust Account System. | Active | |
| AUTHORIZATION_CODE | VARCHAR2 | 10 | Authorization code value returned by Payment Gateway or Trust Account System. | Active | ||
| AUTHORIZATION_DTTM | TIMESTAMP | Yes | Authorization date returned by the Payment Gateway, by the Trust Account System or a timestamp given by the Agency Staff for Cash Drawer and manual confirmation of Trust Account Funds. | Active | ||
| ECHECK_AGREE_DTTM | TIMESTAMP | Date and time of User???s consent and agreement on the Electronic Check terms and conditions. This is required only for Payment Method Electronic Check. | Active | |||
| PAYMENT_TRACKING_DATA1 | VARCHAR2 | 50 | Attribute identifying the custom tracking data. It is passed at the checkout from the calling application, after it is included in the Payment Request to the Payment Gateway and finally the Gateway sends it back as part of the response. | Active | ||
| PAYMENT_TRACKING_DATA2 | VARCHAR2 | 50 | Attribute identifying the custom tracking data. It is passed at the checkout from the calling application, after it is included in the Payment Request to the Payment Gateway and finally the Gateway sends it back as part of the response. | Active | ||
| RESPONSE_MESSAGE1 | VARCHAR2 | 100 | First part of the response message returned by the Payment Gateway or a free form message entered by Agency staff for Cash Drawer or Trust Account. | |||
| RESPONSE_MESSAGE2 | VARCHAR2 | 100 | Second part of the response message returned by the Payment Gateway or a free form message entered by Agency staff for Cash Drawer or Trust Account. | |||
| RESPONSE_MESSAGE3 | VARCHAR2 | 100 | Third part of the response message returned by the Payment Gateway or a free form message entered by Agency staff for Cash Drawer or Trust Account. | |||
| PAYER_FIRST_NAME | VARCHAR2 | 150 | First name of the payer of this payment transaction. | Active | ||
| PAYER_MIDDLE_NAME | VARCHAR2 | 60 | Middle name of the payer of this payment transaction. | Active | ||
| PAYER_LAST_NAME | VARCHAR2 | 150 | Last name of the payer of this payment transaction. | Active | ||
| PAYER_NAME_SUFFIX | VARCHAR2 | 30 | Suffix for the name of the payer of this payment transaction. | Active | ||
| PAYER_COUNTRY_CODE2 | VARCHAR2 | 2 | Two character Country Code that complies with ISO standard. | Active | ||
| PAYER_COUNTRY_CODE3 | VARCHAR2 | 3 | Three character Country Code that complies with ISO standard. | Active | ||
| PAYER_ADDRESS1 | VARCHAR2 | 240 | First line of the address of the Payer of this payment transaction. | Active | ||
| PAYER_ADDRESS2 | VARCHAR2 | 240 | Second line of the address of the Payer of this payment transaction. | Active | ||
| PAYER_ADDRESS3 | VARCHAR2 | 240 | Third line of the address of the Payer of this payment transaction. | Active | ||
| PAYER_ADDRESS4 | VARCHAR2 | 240 | Fourth line of the address of the Payer of this payment transaction. | Active | ||
| PAYER_CITY | VARCHAR2 | 60 | City of the Payer address of the Payer of this payment transaction. | Active | ||
| PAYER_POSTAL_CODE | VARCHAR2 | 60 | Postal Code of the Payer address of the Payer of this payment transaction. | Active | ||
| PAYER_POSTAL_PLUS4 | VARCHAR2 | 10 | Postal Plus of the Payer address of the Payer of this payment transaction. | Active | ||
| PAYER_STATE | VARCHAR2 | 60 | State of the Payer address of the Payer of this payment transaction. | Active | ||
| PAYER_PROVINCE | VARCHAR2 | 60 | Province of the Payer address of the Payer of this payment transaction. | Active | ||
| PAYER_COUNTY | VARCHAR2 | 60 | County of the Payer address of the Payer of this payment transaction. | Active | ||
| PAYER_EMAIL | VARCHAR2 | 320 | Email of the Payer of this payment transaction. | Active | ||
| PAYER_PHONE | VARCHAR2 | 60 | Phone of the Payer of this payment transaction. | Active | ||
| AGENCY_ID | NUMBER | 8 | Yes | Attribute identifying the agency associated with the row. | ||
| PARTY_ID | NUMBER | 18 | A unique identifier for the Customer associated to this payment. | |||
| PROFILE_ID | NUMBER | 18 | A unique identifier for the Profile. | |||
| BUSINESS_ENTITY_ID | VARCHAR2 | 50 | Business entity identifier for business license payment. | |||
| BUSINESS_ENTITY_PARTY_ID | NUMBER | 18 | Party id of the business entity associated with this transaction. | |||
| BUSINESS_ENTITY_NAME | VARCHAR2 | 100 | Name of the business entity associated with this transaction. | |||
| BUSINESS_ENTITY_DBA_NAME | VARCHAR2 | 100 | Doing business as name of the business entity associated with this transaction. | |||
| BUSINESS_PROFILE_ID | NUMBER | 18 | Profile id of the business entity associated with this transaction. | |||
| BUSINESS_LOCATION_ID | VARCHAR2 | 50 | Location id of the business entity associated with this transaction. | |||
| BUSINESS_LOCATION_PARTY_ID | NUMBER | 18 | Location party id of the business entity associated with this transaction. | |||
| PAYER_NAME | VARCHAR2 | 100 | The name of the person that paid for this transaction. | |||
| PAYER_ADDRESS | VARCHAR2 | 240 | The address of the person that paid for this transaction. | |||
| MIGRATED_DATA_FLAG | VARCHAR2 | 3 | Yes | Attribute identifying data migrated from legacy system | ||
| FAR_RECEIPT_STATUS | VARCHAR2 | 30 | Column contains status that drives the creation of receipts in oracle receivables. | |||
| ACCOUNTING_DATE | DATE | Accounting date for the payment transaction | ||||
| EVENT_ID | NUMBER | 18 | Event Id for the payment transaction | |||
| ACCTG_EVENT_STATUS | VARCHAR2 | 30 | Yes | Accounting event status for the payment | ||
| ACCOUNT_TYPE | VARCHAR2 | 30 | Account Type associated with the payment | |||
| ACCOUNT_PURPOSE | VARCHAR2 | 30 | Account Purpose associated with the payment | |||
| PAYMENT_HISTORY_KEY | NUMBER | 18 | Yes | A Unique identifier for the transaction. It uses auto-sequencing. | Active | |
| 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. | Active | |
| REVERSAL_FLAG | VARCHAR2 | 1 | Yes | Reversal flag to indicate payment reversal. | ||
| PAYMENT_STATUS | VARCHAR2 | 30 | Yes | Indicates payment reversal status. | ||
| REVERSAL_REASON_CD | VARCHAR2 | 30 | Indicates reversal reason code. | |||
| OA_APPLIED_FLAG | VARCHAR2 | 1 | Yes | Payment on account credit is applied | ||
| OA_AVAILABLE_AMOUNT | NUMBER | Amount available to apply payment on account credit | ||||
| OA_APPLIED_AMOUNT | NUMBER | Amount of credit applied to the payment | ||||
| REVERSAL_TRAN_TYPE | VARCHAR2 | 30 | Yes | Indicates type of reversal transaction | ||
| UNAPPLIED_FLAG | VARCHAR2 | 1 | Yes | Identifies unapplied payment record | ||
| CHECKOUT_ID | NUMBER | 18 | Checkout Identifier for the credit applied | |||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | Active | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | Active | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | Active | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | Active | ||
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | Active | ||
| RECONCILE_TYPE | VARCHAR2 | 30 | Payment reconcile type. | |||
| RECONCILE_DATETIME | TIMESTAMP | Reconcile date time. | ||||
| ACTIVITY_TYPE | VARCHAR2 | 30 | Identifies the type of activity for which payment is recorded. | |||
| PUBLISHED_STATUS | VARCHAR2 | 30 | Yes | Indicates the publish status of the transaction. | ||
| RECON_HISTORY_KEY | NUMBER | 18 | Unique identifier for each bank reconciliation transaction record. | |||
| GTWY_PAYMENT_DATETIME | TIMESTAMP | Indicates payment date provided by payment gateway. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| psc_bnp_payment_hist_lin | psc_bnp_payment_hist_hdr | PAYMENT_HISTORY_KEY |
Indexes
| Index | Uniqueness | Tablespace | Columns | Status |
|---|---|---|---|---|
| PSC_BNP_PAYMENT_HIST_HDR_N1 | Non Unique | Default | PAYMENT_REFERENCE_ID | |
| PSC_BNP_PAYMENT_HIST_HDR_PK | Unique | Default | PAYMENT_HISTORY_KEY | Active |