PSC_BNP_FEE_REVENUE_V

Details

• Schema: FUSION

• Object owner: PSC_CC

• Object type: VIEW

Columns

Name
TRANSACTION_ID LINE_KEY AGENCY_ID TRANSACTION_TYPE FEE_ITEM_ID TRANSACTION_DATE TRANSACTION_AMOUNT CURRENCY_CODE PAYMENT_METHOD_CODE ISSUE_SUBTYPE_ID ISSUE_SUBTYPE PAYMENT_REFERENCE_ID EVENT_ID CLASSIFICATION FROM_CASH_DRAWER TRUST_ACCOUNT_NUMBER RECORD_ID ACCOUNTING_DATE ACCTG_EVENT_STATUS

Query

SQL_Statement

SELECT LIN.PAYMENT_HISTORY_KEY TRANSACTION_ID, LIN.ITEM_REC_KEY LINE_KEY, LIN.AGENCY_ID, REC.RECORD_TYPE TRANSACTION_TYPE, LIN.FEE_ITEM_ID, HDR.PAYMENT_DATE TRANSACTION_DATE, LIN.ITEM_UNIT_PRICE TRANSACTION_AMOUNT, LIN.ITEM_CURRENCY CURRENCY_CODE, HDR.PAYMENT_METHOD_CODE, CE.ISSUE_SUBTYPE_ID, CE.ISSUE_SUBTYPE, LIN.PAYMENT_REFERENCE_ID, LIN.EVENT_ID, LIN.CLASSIFICATION, CASE WHEN HDR.PAYMENT_METHOD_CODE IN ('CASH', 'PRCK', 'CSCK') THEN 'Y' ELSE 'N' END FROM_CASH_DRAWER, HDR.TRUST_ACCOUNT_NUMBER, LIN.RECORD_ID, LIN.ACCOUNTING_DATE, LIN.ACCTG_EVENT_STATUS FROM PSC_BNP_PAYMENT_HIST_LIN LIN INNER JOIN PSC_BNP_PAYMENT_HIST_HDR HDR ON LIN.PAYMENT_HISTORY_KEY = HDR.PAYMENT_HISTORY_KEY LEFT OUTER JOIN PSC_LNP_RECORD_TYPE_VL REC ON LIN.RECORD_TYPE_ID = REC.RECORD_TYPE_ID AND LIN.CLASSIFICATION = REC.CLASSIFICATION LEFT OUTER JOIN PSC_CE_ISSUE_SUBTYPE_VL CE ON LIN.RECORD_TYPE_ID = CE.ISSUE_SUBTYPE_ID AND LIN.CLASSIFICATION = 'CE' WHERE LIN.ACCTG_EVENT_STATUS IN ('ORA_NONE', 'ORA_ERROR') AND ((HDR.PAYMENT_METHOD_CODE <> 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NULL) OR HDR.PAYMENT_METHOD_CODE IN ('TRSA'))