PSC_BNP_PAYMENT_LN_ACCTG

Details

Schema: FUSION
Object owner: PSC_CC
Object type: VIEW

Columns

Name
AGENCY_ID PAYMENT_HISTORY_KEY ITEM_REC_KEY PAYMENT_REFERENCE_ID LINE_AMOUNT CURRENCY_CODE TRANSACTION_DATE PAYMENT_METHOD_CODE REVERSAL_REASON_CD RECORD_ID FEE_ITEM_ID CLASSIFICATION RECORD_TYPE_ID TRANSACTION_TYPE EVENT_ID ACCOUNTING_DATE ACCTG_EVENT_STATUS HAS_CASH_DRAWER TRUST_ACCOUNT_NUMBER ACCOUNT_TYPE ACCOUNT_PURPOSE OLD_PAYMENT_REFERENCE_ID ITEM_NAME RULE_CLASSIFICATION

Name

AGENCY_ID

PAYMENT_HISTORY_KEY

ITEM_REC_KEY

PAYMENT_REFERENCE_ID

LINE_AMOUNT

CURRENCY_CODE

TRANSACTION_DATE

PAYMENT_METHOD_CODE

REVERSAL_REASON_CD

RECORD_ID

FEE_ITEM_ID

CLASSIFICATION

RECORD_TYPE_ID

TRANSACTION_TYPE

EVENT_ID

ACCOUNTING_DATE

ACCTG_EVENT_STATUS

HAS_CASH_DRAWER

TRUST_ACCOUNT_NUMBER

ACCOUNT_TYPE

ACCOUNT_PURPOSE

OLD_PAYMENT_REFERENCE_ID

ITEM_NAME

RULE_CLASSIFICATION

Query

SQL_Statement
SELECT LIN.AGENCY_ID, LIN.PAYMENT_HISTORY_KEY, LIN.ITEM_REC_KEY, LIN.PAYMENT_REFERENCE_ID, LIN.ITEM_UNIT_PRICE LINE_AMOUNT, LIN.ITEM_CURRENCY CURRENCY_CODE, HDR.PAYMENT_DATE TRANSACTION_DATE, HDR.PAYMENT_METHOD_CODE, RVS.REVERSAL_REASON_CD, LIN.RECORD_ID, LIN.FEE_ITEM_ID, CONCAT('ORA_', LIN.CLASSIFICATION) CLASSIFICATION, LIN.RECORD_TYPE_ID, CASE WHEN LIN.CLASSIFICATION = 'CE' THEN CE.ISSUE_SUBTYPE ELSE REC.RECORD_TYPE END TRANSACTION_TYPE, LIN.EVENT_ID, LIN.ACCOUNTING_DATE, LIN.ACCTG_EVENT_STATUS, CASE WHEN HDR.PAYMENT_METHOD_CODE IN ('CASH', 'PRCK', 'CSCK') THEN 'Y' ELSE 'N' END HAS_CASH_DRAWER, HDR.TRUST_ACCOUNT_NUMBER, HDR.ACCOUNT_TYPE, HDR.ACCOUNT_PURPOSE, RVS.PAYMENT_REFERENCE_ID OLD_PAYMENT_REFERENCE_ID, LIN.ITEM_NAME, CASE WHEN HDR.PAYMENT_METHOD_CODE <> 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NULL AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') AND RVS.REVERSAL_TRAN_TYPE IS NULL THEN 'ORA_FEE_REVENUE' WHEN HDR.PAYMENT_METHOD_CODE <> 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('BNP') AND RVS.REVERSAL_TRAN_TYPE IS NULL THEN 'ORA_TRUST_DEPOSIT' WHEN HDR.PAYMENT_METHOD_CODE <> 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('BNP') AND RVS.REVERSAL_TRAN_TYPE = 'ORA_DEPOSIT' THEN 'ORA_TRUST_DEPOSIT' WHEN HDR.PAYMENT_METHOD_CODE = 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') AND RVS.REVERSAL_TRAN_TYPE IS NULL THEN 'ORA_TRUST_PAYMENT' WHEN HDR.PAYMENT_METHOD_CODE = 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') AND RVS.REVERSAL_TRAN_TYPE = 'ORA_FEE' THEN 'ORA_TRUST_PAYMENT' END RULE_CLASSIFICATION 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_BNP_PAYMENT_REVERSAL RVS ON RVS.PAYMENT_REFERENCE_ID = HDR.PAYMENT_TRANSACTION_ID AND RVS.PAYMENT_REVERSAL_KEY = LIN.ITEM_REFERENCE 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'

SQL_Statement

SELECT

LIN.AGENCY_ID, LIN.PAYMENT_HISTORY_KEY, LIN.ITEM_REC_KEY, LIN.PAYMENT_REFERENCE_ID, LIN.ITEM_UNIT_PRICE LINE_AMOUNT, LIN.ITEM_CURRENCY CURRENCY_CODE,

HDR.PAYMENT_DATE TRANSACTION_DATE, HDR.PAYMENT_METHOD_CODE, RVS.REVERSAL_REASON_CD,

LIN.RECORD_ID, LIN.FEE_ITEM_ID, CONCAT('ORA_', LIN.CLASSIFICATION) CLASSIFICATION, LIN.RECORD_TYPE_ID,

CASE WHEN LIN.CLASSIFICATION = 'CE' THEN CE.ISSUE_SUBTYPE ELSE REC.RECORD_TYPE END TRANSACTION_TYPE,

LIN.EVENT_ID, LIN.ACCOUNTING_DATE, LIN.ACCTG_EVENT_STATUS,

CASE WHEN HDR.PAYMENT_METHOD_CODE IN ('CASH', 'PRCK', 'CSCK') THEN 'Y' ELSE 'N' END HAS_CASH_DRAWER,

HDR.TRUST_ACCOUNT_NUMBER, HDR.ACCOUNT_TYPE, HDR.ACCOUNT_PURPOSE,

RVS.PAYMENT_REFERENCE_ID OLD_PAYMENT_REFERENCE_ID, LIN.ITEM_NAME,

CASE

WHEN HDR.PAYMENT_METHOD_CODE <> 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NULL AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') AND RVS.REVERSAL_TRAN_TYPE IS NULL THEN 'ORA_FEE_REVENUE'

WHEN HDR.PAYMENT_METHOD_CODE <> 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('BNP') AND RVS.REVERSAL_TRAN_TYPE IS NULL THEN 'ORA_TRUST_DEPOSIT'

WHEN HDR.PAYMENT_METHOD_CODE <> 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('BNP') AND RVS.REVERSAL_TRAN_TYPE = 'ORA_DEPOSIT' THEN 'ORA_TRUST_DEPOSIT'

WHEN HDR.PAYMENT_METHOD_CODE = 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') AND RVS.REVERSAL_TRAN_TYPE IS NULL THEN 'ORA_TRUST_PAYMENT'

WHEN HDR.PAYMENT_METHOD_CODE = 'TRSA' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') AND RVS.REVERSAL_TRAN_TYPE = 'ORA_FEE' THEN 'ORA_TRUST_PAYMENT'

END RULE_CLASSIFICATION

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_BNP_PAYMENT_REVERSAL RVS ON RVS.PAYMENT_REFERENCE_ID = HDR.PAYMENT_TRANSACTION_ID AND RVS.PAYMENT_REVERSAL_KEY = LIN.ITEM_REFERENCE

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'