PSC_BNP_REFUND_LN_ACCTG

Details

Schema: FUSION
Object owner: PSC_CC
Object type: VIEW

Columns

Name
AGENCY_ID REFUND_HISTORY_KEY REFUND_RECORD_KEY REFUND_REFERENCE_ID LINE_AMOUNT CURRENCY_CODE TRANSACTION_DATE REFUND_METHOD_CODE REFUND_REASON_CODE RECORD_ID FEE_ITEM_ID CLASSIFICATION RECORD_TYPE_ID TRANSACTION_TYPE EVENT_ID ACCOUNTING_DATE ACCTG_EVENT_STATUS TRUST_ACCOUNT_NUMBER ACCOUNT_TYPE ACCOUNT_PURPOSE RULE_CLASSIFICATION

Name

AGENCY_ID

REFUND_HISTORY_KEY

REFUND_RECORD_KEY

REFUND_REFERENCE_ID

LINE_AMOUNT

CURRENCY_CODE

TRANSACTION_DATE

REFUND_METHOD_CODE

REFUND_REASON_CODE

RECORD_ID

FEE_ITEM_ID

CLASSIFICATION

RECORD_TYPE_ID

TRANSACTION_TYPE

EVENT_ID

ACCOUNTING_DATE

ACCTG_EVENT_STATUS

TRUST_ACCOUNT_NUMBER

ACCOUNT_TYPE

ACCOUNT_PURPOSE

RULE_CLASSIFICATION

Query

SQL_Statement
SELECT LIN.AGENCY_ID, LIN.REFUND_HISTORY_KEY, LIN.REFUND_RECORD_KEY, HDR.REFUND_REFERENCE_ID, LIN.ITEM_REFUND_AMOUNT LINE_AMOUNT, LIN.ITEM_REFUND_CURRENCY CURRENCY_CODE, TRUNC(HDR.REFUND_DTTM) TRANSACTION_DATE, HDR.REFUND_METHOD_CODE, HDR.REFUND_REASON_CODE, LIN.RECORD_ID, LIN.ITEM_DESCRIPTION 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, HDR.TRUST_ACCOUNT_NUMBER, HDR.ACCOUNT_TYPE, HDR.ACCOUNT_PURPOSE, CASE WHEN HDR.REFUND_METHOD_CODE = 'TRSA' THEN 'ORA_TRUST_PAYMENT_CHARGEBACK' WHEN HDR.REFUND_METHOD_CODE <> 'TRSA' AND LIN.CLASSIFICATION = 'BNP' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL THEN 'ORA_TRUST_DEPOSIT_REFUND' WHEN HDR.REFUND_METHOD_CODE <> 'TRSA' AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') THEN 'ORA_FEE_REFUND' END RULE_CLASSIFICATION FROM PSC_BNP_REFUND_HIST_LIN LIN INNER JOIN PSC_BNP_REFUND_HIST_HDR HDR ON LIN.REFUND_HISTORY_KEY = HDR.REFUND_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 HDR.REFUND_STATUS = 'ORA_RFND' AND ((LIN.CLASSIFICATION IN ('BNP') AND TRUST_ACCOUNT_NUMBER IS NOT NULL) OR LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE'))

SQL_Statement

SELECT

LIN.AGENCY_ID, LIN.REFUND_HISTORY_KEY, LIN.REFUND_RECORD_KEY, HDR.REFUND_REFERENCE_ID, LIN.ITEM_REFUND_AMOUNT LINE_AMOUNT, LIN.ITEM_REFUND_CURRENCY CURRENCY_CODE,

TRUNC(HDR.REFUND_DTTM) TRANSACTION_DATE, HDR.REFUND_METHOD_CODE, HDR.REFUND_REASON_CODE,

LIN.RECORD_ID, LIN.ITEM_DESCRIPTION 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,

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

CASE

WHEN HDR.REFUND_METHOD_CODE = 'TRSA' THEN 'ORA_TRUST_PAYMENT_CHARGEBACK'

WHEN HDR.REFUND_METHOD_CODE <> 'TRSA' AND LIN.CLASSIFICATION = 'BNP' AND HDR.TRUST_ACCOUNT_NUMBER IS NOT NULL THEN 'ORA_TRUST_DEPOSIT_REFUND'

WHEN HDR.REFUND_METHOD_CODE <> 'TRSA' AND LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE') THEN 'ORA_FEE_REFUND'

END RULE_CLASSIFICATION

FROM PSC_BNP_REFUND_HIST_LIN LIN

INNER JOIN PSC_BNP_REFUND_HIST_HDR HDR ON LIN.REFUND_HISTORY_KEY = HDR.REFUND_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 HDR.REFUND_STATUS = 'ORA_RFND'

AND ((LIN.CLASSIFICATION IN ('BNP') AND TRUST_ACCOUNT_NUMBER IS NOT NULL) OR LIN.CLASSIFICATION IN ('PMT', 'PZ', 'LIC', 'CE'))