PSC_BNP_FEE_PAY_LINE_VL

Details

• Schema: FUSION

• Object owner: PSC_CC

• Object type: VIEW

Columns

Name

LINE_PAYMENT_HISTORY_KEY LINE_PAYMENT_TRANSACTION_ID LINE_PAYMENT_REFERENCE_ID LINE_AGENCY_ID LINE_PAYMENT_DATE LINE_TYPE LINE_CLASSIFICATION LINE_AMOUNT LINE_CURRENCY ORIG_PAYMENT_HISTORY_KEY PAYL_ITEM_NAME PAYL_PERMIT_NUMBER PAYL_RECORD_TYPE_ID PAYL_ISSUE_SUBTYPE_ID PAYL_RECORD_TYPE_CODE PAYL_FEE_ITEM_ID PAYL_FEE_SCHEDULE_ID PAYL_DEPARTMENT_ID LINE_PAYMENT_METHOD_CODE LINE_REVERSAL_FLAG LINE_REVERSAL_REASON_CD LINE_SOURCE_REVERSAL_KEY CSHDRW_RECEIPT_LINE_NUMBER CSHDRW_LINE_NUMBER CSHDRW_RECEIPT_STATUS CSHDRW_DRAWER_ID LINE_DESCRIPTION ONACCT_REFUND_METHOD_CODE ONACCT_HISTORY_KEY ONACCT_REFERENCE_ID ONACCT_SOURCE ONACCT_REQUIRE_ACCOUNTING ONACCT_STATUS LINE_OA_APPLIED_FLAG LINE_OA_APPLIED_AMOUNT LINE_REVERSAL_TRAN_TYPE LINE_UNAPPLIED_FLAG LINE_NUMBER EVENT_ID LEDGER_ID

Query

SQL_Statement

select CASE when L.REVERSAL_FLAG='Y' then B.PAYMENT_HISTORY_KEY ELSE A.PAYMENT_HISTORY_KEY END LINE_PAYMENT_HISTORY_KEY ,A.PAYMENT_TRANSACTION_ID LINE_PAYMENT_TRANSACTION_ID ,A.PAYMENT_REFERENCE_ID LINE_PAYMENT_REFERENCE_ID ,A.AGENCY_ID LINE_AGENCY_ID ,A.PAYMENT_DATE LINE_PAYMENT_DATE ,'PAYLIN' LINE_TYPE ,CONCAT('ORA_', L.CLASSIFICATION) LINE_CLASSIFICATION ,CASE when L.REVERSAL_FLAG = 'Y' then RV.BALANCE_AMOUNT ELSE L.ITEM_UNIT_PRICE END LINE_AMOUNT ,L.ITEM_CURRENCY LINE_CURRENCY ,B.PAYMENT_HISTORY_KEY ORIG_PAYMENT_HISTORY_KEY ,L.ITEM_NAME PAYL_ITEM_NAME ,L.RECORD_ID PAYL_PERMIT_NUMBER ,L.RECORD_TYPE_ID PAYL_RECORD_TYPE_ID ,L.RECORD_TYPE_ID PAYL_ISSUE_SUBTYPE_ID ,RT.RECORD_TYPE PAYL_RECORD_TYPE_CODE ,L.FEE_ITEM_ID PAYL_FEE_ITEM_ID ,FR.FEE_SCHEDULE_ID PAYL_FEE_SCHEDULE_ID ,L.DEPARTMENT_ID PAYL_DEPARTMENT_ID ,NULL LINE_PAYMENT_METHOD_CODE ,CASE when (L.REVERSAL_FLAG = 'Y' and RV.FULL_REVERSAL_FLAG='Y') then 'Y' when (L.REVERSAL_FLAG = 'Y' and RV.FULL_REVERSAL_FLAG='N') then 'B' else 'N' END LINE_REVERSAL_FLAG ,RV.REVERSAL_REASON_CD LINE_REVERSAL_REASON_CD ,RV.PAYMENT_REVERSAL_KEY LINE_SOURCE_REVERSAL_KEY ,NULL CSHDRW_RECEIPT_LINE_NUMBER ,NULL CSHDRW_LINE_NUMBER ,NULL CSHDRW_RECEIPT_STATUS ,NULL CSHDRW_DRAWER_ID ,L.ITEM_DESCRIPTION LINE_DESCRIPTION ,NULL ONACCT_REFUND_METHOD_CODE ,NULL ONACCT_HISTORY_KEY ,NULL ONACCT_REFERENCE_ID ,NULL ONACCT_SOURCE ,NULL ONACCT_REQUIRE_ACCOUNTING ,NULL ONACCT_STATUS ,NULL LINE_OA_APPLIED_FLAG ,NULL LINE_OA_APPLIED_AMOUNT ,L.REVERSAL_TRAN_TYPE LINE_REVERSAL_TRAN_TYPE ,L.UNAPPLIED_FLAG LINE_UNAPPLIED_FLAG ,CASE when L.REVERSAL_FLAG='Y' then 50000 + L.ITEM_REC_KEY ELSE L.ITEM_REC_KEY END LINE_NUMBER ,A.EVENT_ID EVENT_ID ,C.LEDGER_ID LEDGER_ID from PSC_BNP_PAYMENT_HIST_LIN L inner join PSC_BNP_PAYMENT_HIST_HDR A ON L.PAYMENT_HISTORY_KEY = A.PAYMENT_HISTORY_KEY inner join psc_com_agency_b C ON C.AGENCY_ID = A.AGENCY_ID left outer join PSC_LNP_FEE_RECORD FR on L.FEE_REFERENCE_PUID = FR.FEE_RECORD_PUID left outer join PSC_LNP_RECORD R on L.RECORD_ID = R.LNP_RECORD_ID and R.ATTRIBUTE_CATEGORY = L.RECORD_TYPE_ID left outer join PSC_LNP_RECORD_TYPE_TL RT on R.ATTRIBUTE_CATEGORY = RT.RECORD_TYPE_ID and RT.LANGUAGE = USERENV('LANG') left outer join PSC_BNP_PAYMENT_REVERSAL RV ON RV.PAYMENT_REFERENCE_ID = A.PAYMENT_TRANSACTION_ID and RV.PAYMENT_REVERSAL_KEY = L.ITEM_REFERENCE left outer join psc_bnp_payment_hist_hdr B on B.PAYMENT_REFERENCE_ID = A.PAYMENT_TRANSACTION_ID UNION select CASE when B.REVERSAL_FLAG='Y' then OH.PAYMENT_HISTORY_KEY ELSE A.PAYMENT_HISTORY_KEY END LINE_PAYMENT_HISTORY_KEY ,A.PAYMENT_TRANSACTION_ID LINE_PAYMENT_TRANSACTION_ID ,A.PAYMENT_REFERENCE_ID LINE_PAYMENT_REFERENCE_ID ,A.AGENCY_ID LINE_AGENCY_ID ,A.PAYMENT_DATE LINE_PAYMENT_DATE ,'CSHDRW' LINE_TYPE ,'CDR' LINE_CLASSIFICATION ,B.LINE_AMOUNT LINE_AMOUNT ,B.CURRENCY_CODE LINE_CURRENCY ,OH.PAYMENT_HISTORY_KEY ORIG_PAYMENT_HISTORY_KEY ,CONCAT('CSHDRW_LN_', TO_CHAR(B.RECEIPT_LINE_NUMBER)) PAYL_ITEM_NAME ,NULL PAYL_PERMIT_NUMBER ,NULL PAYL_RECORD_TYPE_ID ,NULL PAYL_ISSUE_SUBTYPE_ID ,NULL PAYL_RECORD_TYPE_CODE ,NULL PAYL_FEE_ITEM_ID ,NULL PAYL_FEE_SCHEDULE_ID ,NULL PAYL_DEPARTMENT_ID ,B.PAYMENT_METHOD_CODE LINE_PAYMENT_METHOD_CODE ,B.REVERSAL_FLAG LINE_REVERSAL_FLAG ,B.REVERSAL_REASON_CD LINE_REVERSAL_REASON_CD ,CAST (NULL AS NUMBER(18)) LINE_SOURCE_REVERSAL_KEY ,B.RECEIPT_NUMBER CSHDRW_RECEIPT_LINE_NUMBER ,B.RECEIPT_LINE_NUMBER CSHDRW_LINE_NUMBER ,B.RECEIPT_STATUS CSHDRW_RECEIPT_STATUS ,B.DRAWER_ID CSHDRW_DRAWER_ID ,B.DESCRIPTION LINE_DESCRIPTION ,NULL ONACCT_REFUND_METHOD_CODE ,NULL ONACCT_HISTORY_KEY ,NULL ONACCT_REFERENCE_ID ,NULL ONACCT_SOURCE ,NULL ONACCT_REQUIRE_ACCOUNTING ,NULL ONACCT_STATUS ,B.OA_APPLIED_FLAG LINE_OA_APPLIED_FLAG ,B.OA_APPLIED_AMOUNT LINE_OA_APPLIED_AMOUNT ,B.REVERSAL_TRAN_TYPE LINE_REVERSAL_TRAN_TYPE ,B.UNAPPLIED_FLAG LINE_UNAPPLIED_FLAG ,CASE when B.REVERSAL_FLAG='Y' then B.RECEIPT_LINE_NUMBER + 150000 ELSE B.RECEIPT_LINE_NUMBER + 100000 END LINE_NUMBER ,A.EVENT_ID EVENT_ID ,C.LEDGER_ID LEDGER_ID FROM psc_bnp_csh_drw_pay_hist B inner join PSC_BNP_PAYMENT_HIST_HDR A ON B.PAYMENT_REFERENCE_ID = A.PAYMENT_REFERENCE_ID inner join psc_com_agency_b C ON C.AGENCY_ID = A.AGENCY_ID left outer join psc_bnp_payment_hist_hdr OH on OH.PAYMENT_REFERENCE_ID = A.PAYMENT_TRANSACTION_ID UNION select CASE when B.REVERSAL_FLAG='Y' then OH.PAYMENT_HISTORY_KEY ELSE A.PAYMENT_HISTORY_KEY END LINE_PAYMENT_HISTORY_KEY ,A.PAYMENT_TRANSACTION_ID LINE_PAYMENT_TRANSACTION_ID ,A.PAYMENT_REFERENCE_ID LINE_PAYMENT_REFERENCE_ID ,A.AGENCY_ID LINE_AGENCY_ID ,A.PAYMENT_DATE LINE_PAYMENT_DATE ,'ONACCT' LINE_TYPE ,'OVP' LINE_CLASSIFICATION ,B.ONACCOUNT_CREDIT LINE_AMOUNT ,B.ONACCOUNT_CURRENCY LINE_CURRENCY ,OH.PAYMENT_HISTORY_KEY ORIG_PAYMENT_HISTORY_KEY ,CONCAT('ONACCT_LN_', TO_CHAR(B.AGENCY_ID)) PAYL_ITEM_NAME ,NULL PAYL_PERMIT_NUMBER ,NULL PAYL_RECORD_TYPE_ID ,NULL PAYL_ISSUE_SUBTYPE_ID ,NULL PAYL_RECORD_TYPE_CODE ,NULL PAYL_FEE_ITEM_ID ,NULL PAYL_FEE_SCHEDULE_ID ,NULL PAYL_DEPARTMENT_ID ,B.PAYMENT_METHOD_CODE LINE_PAYMENT_METHOD_CODE ,B.REVERSAL_FLAG LINE_REVERSAL_FLAG ,CAST (NULL AS VARCHAR2(30)) LINE_REVERSAL_REASON_CD ,CAST (NULL AS NUMBER(18)) LINE_SOURCE_REVERSAL_KEY ,NULL CSHDRW_RECEIPT_LINE_NUMBER ,NULL CSHDRW_LINE_NUMBER ,NULL CSHDRW_RECEIPT_STATUS ,NULL CSHDRW_DRAWER_ID ,NULL LINE_DESCRIPTION ,B.REFUND_METHOD_CODE ONACCT_REFUND_METHOD_CODE ,B.ONACCOUNT_HISTORY_KEY ONACCT_HISTORY_KEY ,B.ONACCOUNT_REFERENCE_ID ONACCT_REFERENCE_ID ,B.ONACCOUNT_SOURCE ONACCT_SOURCE ,B.REQUIRE_ACCOUNTING ONACCT_REQUIRE_ACCOUNTING ,B.ONACCOUNT_STATUS ONACCT_STATUS ,B.OA_APPLIED_FLAG LINE_OA_APPLIED_FLAG ,NULL LINE_OA_APPLIED_AMOUNT ,B.REVERSAL_TRAN_TYPE LINE_REVERSAL_TRAN_TYPE ,B.UNAPPLIED_FLAG LINE_UNAPPLIED_FLAG ,CASE when B.REVERSAL_FLAG='Y' then ROW_NUMBER() OVER (PARTITION BY B.PAYMENT_REFERENCE_ID ORDER BY B.ONACCOUNT_HISTORY_KEY) + 250000 ELSE ROW_NUMBER() OVER (PARTITION BY B.PAYMENT_REFERENCE_ID ORDER BY B.ONACCOUNT_HISTORY_KEY) + 200000 END LINE_NUMBER ,A.EVENT_ID EVENT_ID ,C.LEDGER_ID LEDGER_ID FROM psc_bnp_payment_onaccount B inner join psc_bnp_payment_hist_hdr A ON B.PAYMENT_REFERENCE_ID = A.PAYMENT_REFERENCE_ID inner join psc_com_agency_b C ON C.AGENCY_ID = A.AGENCY_ID left outer join psc_bnp_payment_hist_hdr OH on OH.PAYMENT_REFERENCE_ID = A.PAYMENT_TRANSACTION_ID