PSC_BNP_FEE_PAYMENT_HDR_VL

Details

• Schema: FUSION

• Object owner: PSC_CC

• Object type: VIEW

Columns

Name

PAYH_PAYMENT_HISTORY_KEY PAYH_AGENCY_ID PAYH_PAYMENT_REFERENCE_ID PAYH_PAYMENT_DATE PAYH_PAYMENT_AMOUNT PAYH_PAYMENT_CURRENCY CUR_CONVERSION_DATE CUR_CONVERSION_RATE CUR_CONVERSION_TYPE PAYH_PAYMENT_TRANSACTION_ID PAYH_PAYMENT_METHOD_CODE PAYH_PAYER_NAME PAYH_PAYER_ADDRESS PAYH_TRUST_ACCOUNT_NUMBER PAYH_ACCOUNT_TYPE PAYH_ACCOUNT_PURPOSE PAYH_PAYMENT_STATUS PAYH_REVERSAL_FLAG PAYH_REVERSAL_REASON_CD PAYH_OA_APPLIED_FLAG PAYH_OA_APPLIED_AMOUNT PAYH_REVERSAL_TRAN_TYPE PAYH_UNAPPLIED_FLAG EVENT_ID LEDGER_ID

Query

SQL_Statement

select CASE when A.REVERSAL_FLAG='Y' then B.PAYMENT_HISTORY_KEY ELSE A.PAYMENT_HISTORY_KEY END PAYH_PAYMENT_HISTORY_KEY ,A.AGENCY_ID PAYH_AGENCY_ID ,A.PAYMENT_REFERENCE_ID PAYH_PAYMENT_REFERENCE_ID ,to_date(to_char(from_tz(A.CREATION_DATE,'UTC') at time zone C.TZNAME, 'YYYY-MM-DD'),'YYYY-MM-DD') PAYH_PAYMENT_DATE ,A.PAYMENT_AMOUNT PAYH_PAYMENT_AMOUNT ,A.PAYMENT_CURRENCY PAYH_PAYMENT_CURRENCY ,CAST (NULL AS DATE) CUR_CONVERSION_DATE ,CAST (NULL AS NUMBER) CUR_CONVERSION_RATE ,CAST (NULL AS VARCHAR2(30)) CUR_CONVERSION_TYPE ,A.PAYMENT_TRANSACTION_ID PAYH_PAYMENT_TRANSACTION_ID ,A.PAYMENT_METHOD_CODE PAYH_PAYMENT_METHOD_CODE ,A.PAYER_NAME PAYH_PAYER_NAME ,A.PAYER_ADDRESS PAYH_PAYER_ADDRESS ,A.TRUST_ACCOUNT_NUMBER PAYH_TRUST_ACCOUNT_NUMBER ,A.ACCOUNT_TYPE PAYH_ACCOUNT_TYPE ,A.ACCOUNT_PURPOSE PAYH_ACCOUNT_PURPOSE ,A.PAYMENT_STATUS PAYH_PAYMENT_STATUS ,A.REVERSAL_FLAG PAYH_REVERSAL_FLAG ,A.REVERSAL_REASON_CD PAYH_REVERSAL_REASON_CD ,A.OA_APPLIED_FLAG PAYH_OA_APPLIED_FLAG ,A.OA_APPLIED_AMOUNT PAYH_OA_APPLIED_AMOUNT ,A.REVERSAL_TRAN_TYPE PAYH_REVERSAL_TRAN_TYPE ,A.UNAPPLIED_FLAG PAYH_UNAPPLIED_FLAG ,A.EVENT_ID EVENT_ID ,C.LEDGER_ID LEDGER_ID from psc_bnp_payment_hist_hdr A inner join psc_com_agency_b C ON C.AGENCY_ID = A.AGENCY_ID left outer join psc_bnp_payment_hist_hdr B on B.PAYMENT_REFERENCE_ID = A.PAYMENT_TRANSACTION_ID