VIEW: APPS.OKL_BPD_LEASING_PAYMENT_TRX_V

Source

View Text - Preformatted

SELECT PS.ROWID ROW_ID , NVL(iltt.name,decode(LN.LINE_TYPE,'CHARGE',NULL,LN.LINE_TYPE) ) line_name, HD.IXX_ID CUSTOMER_ID , SUBSTRB (PY.PARTY_NAME, 1, 50) CUSTOMER_NAME , CA.ACCOUNT_NUMBER CUSTOMER_NUMBER , HD.CURRENCY_CODE , HD.ID CONSOLIDATED_INVOICE_ID , HD.DATE_CONSOLIDATED INVOICE_DATE, CN.START_DATE START_DATE, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER , LN.SEQUENCE_NUMBER CONSOLIDATED_LINE_NUMBER , ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID , PS.TRX_NUMBER RECEIVABLES_INVOICE_NUMBER , ST.KHR_ID CONTRACT_ID , CN.CONTRACT_NUMBER CONTRACT_NUMBER , ST.KLE_ID CONTRACT_LINE_ID , ST.ID STREAM_ID , SM.ID STREAM_TYPE_ID , SMT.NAME STREAM_NAME , SM.STREAM_TYPE_PURPOSE STREAM_TYPE_PURPOSE, PS.PAYMENT_SCHEDULE_ID , PS.TRX_NUMBER TRX_NUMBER , PS.TRX_DATE TRX_DATE , PS.DUE_DATE DUE_DATE , PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL , PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING , ST.TAX_AMOUNT TAX_AMOUNT , ST.LATE_CHARGE_ASS_YN , PS.CLASS CLASS , PS.STATUS STATUS , PS.CREATED_BY CREATED_BY , PS.CREATION_DATE CREATION_DATE , PS.LAST_UPDATED_BY LAST_UPDATED_BY , PS.LAST_UPDATE_DATE LAST_UPDATE_DATE , PS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, ST.SEL_ID SEL_ID, HD.LEGAL_ENTITY_ID LEGAL_ENTITY_ID FROM OKL_STRM_TYPE_B SM,OKL_STRM_TYPE_TL SMT,OKL_INVC_LINE_TYPES_B ILT,OKL_INVC_LINE_TYPES_TL ILTT,AR_PAYMENT_SCHEDULES_ALL PS,OKL_CNSLD_AR_STRMS_B ST,OKL_CNSLD_AR_LINES_B LN,OKL_CNSLD_AR_HDRS_B HD,HZ_CUST_ACCOUNTS CA,HZ_PARTIES PY,OKC_K_HEADERS_ALL_B CN WHERE PS.CLASS IN ('INV', 'CM') AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID AND LN.ID = ST.LLN_ID AND HD.ID = LN.CNR_ID AND CA.CUST_ACCOUNT_ID = HD.IXX_ID AND PY.PARTY_ID = CA.PARTY_ID AND SM.ID = ST.STY_ID AND CN.ID = ST.KHR_ID AND ILT.ID (+) = LN.ILT_ID AND SM.ID = SMT.ID AND SMT.LANGUAGE = USERENV('LANG') AND ILT.ID = ILTT.ID AND ILTT.LANGUAGE = USERENV('LANG')

View Text - HTML Formatted

SELECT PS.ROWID ROW_ID
, NVL(ILTT.NAME
, DECODE(LN.LINE_TYPE
, 'CHARGE'
, NULL
, LN.LINE_TYPE) ) LINE_NAME
, HD.IXX_ID CUSTOMER_ID
, SUBSTRB (PY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HD.CURRENCY_CODE
, HD.ID CONSOLIDATED_INVOICE_ID
, HD.DATE_CONSOLIDATED INVOICE_DATE
, CN.START_DATE START_DATE
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, LN.SEQUENCE_NUMBER CONSOLIDATED_LINE_NUMBER
, ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, PS.TRX_NUMBER RECEIVABLES_INVOICE_NUMBER
, ST.KHR_ID CONTRACT_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, ST.KLE_ID CONTRACT_LINE_ID
, ST.ID STREAM_ID
, SM.ID STREAM_TYPE_ID
, SMT.NAME STREAM_NAME
, SM.STREAM_TYPE_PURPOSE STREAM_TYPE_PURPOSE
, PS.PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER TRX_NUMBER
, PS.TRX_DATE TRX_DATE
, PS.DUE_DATE DUE_DATE
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, ST.TAX_AMOUNT TAX_AMOUNT
, ST.LATE_CHARGE_ASS_YN
, PS.CLASS CLASS
, PS.STATUS STATUS
, PS.CREATED_BY CREATED_BY
, PS.CREATION_DATE CREATION_DATE
, PS.LAST_UPDATED_BY LAST_UPDATED_BY
, PS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, ST.SEL_ID SEL_ID
, HD.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
FROM OKL_STRM_TYPE_B SM
, OKL_STRM_TYPE_TL SMT
, OKL_INVC_LINE_TYPES_B ILT
, OKL_INVC_LINE_TYPES_TL ILTT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CN
WHERE PS.CLASS IN ('INV'
, 'CM')
AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND SM.ID = ST.STY_ID
AND CN.ID = ST.KHR_ID
AND ILT.ID (+) = LN.ILT_ID
AND SM.ID = SMT.ID
AND SMT.LANGUAGE = USERENV('LANG')
AND ILT.ID = ILTT.ID
AND ILTT.LANGUAGE = USERENV('LANG')