View: OKL_BPD_AR_INV_LN_DTLS_V

Product:	OKL - Lease and Finance Management
Description:	View for Search invoice Page Details.
Implementation/DBA Data:	APPS.OKL_BPD_AR_INV_LN_DTLS_V

View Text

SELECT CN.CONTRACT_NUMBER CONTRACT_NUMBER
, ST.KLE_ID CONTRACT_LINE_ID
, LN.SEQUENCE_NUMBER CONSOLIDATED_LINE_NUMBER
, ILT.NAME LINE_NAME
, SUM(PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL
, SUM(PS.AMOUNT_DUE_ORIGINAL) - SUM(PS.AMOUNT_DUE_REMAINING) AMOUNT_PAID
, SUM(PS.AMOUNT_DUE_REMAINING) BALANCE
, ( SELECT NVL(SUM(APP.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL SCH
WHERE APP.STATUS = 'APP'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = SCH.PAYMENT_SCHEDULE_ID
AND SCH.CLASS = 'INV'
AND SCH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND APP.APPLICATION_TYPE = 'CASH' ) AMOUNT_APPLIED
, ( SELECT NVL(SUM(APP.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL SCH
WHERE APP.STATUS = 'APP'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = SCH.PAYMENT_SCHEDULE_ID
AND SCH.CLASS = 'INV'
AND SCH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND APP.APPLICATION_TYPE = 'CM' ) AMOUNT_CREDITED
, SUM(ST.TAX_AMOUNT) TAX_AMOUNT
, PS.TRX_NUMBER RECEIVABLES_INVOICE_NUMBER
, STB.NAME STREAM_TYPE
, LKUP.MEANING STREAM_PURPOSE_MEANING
, HD.CURRENCY_CODE CURRENCY_CODE
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, RACTRL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, CA.CUST_ACCOUNT_ID IXX_ID
, NULL TXN_LINE_TYPE
, RACTRL.INTERFACE_LINE_ATTRIBUTE14 TLD_ID
, PS.ORG_ID ORG_ID
FROM OKL_INVC_LINE_TYPES_V ILT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, OKC_K_HEADERS_B CN
, HZ_CUST_ACCOUNTS CA
, OKL_STRM_TYPE_V STB
, FND_LOOKUPS LKUP
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
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 CN.ID (+) = ST.KHR_ID
AND LN.ID = ST.LLN_ID
AND ILT.ID (+) = LN.ILT_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PS.CUSTOMER_ID = CA.CUST_ACCOUNT_ID
AND ST.STY_ID=STB.ID
AND LKUP.LOOKUP_CODE = STB.STREAM_TYPE_PURPOSE
AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND ST.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE' GROUP BY HD.CONSOLIDATED_INVOICE_NUMBER
, CN.CONTRACT_NUMBER
, ST.KLE_ID
, LN.SEQUENCE_NUMBER
, ILT.NAME
, PS.TRX_NUMBER
, STB.NAME
, LKUP.MEANING
, HD.CURRENCY_CODE
, PS.CUSTOMER_TRX_ID
, RACTRL.CUSTOMER_TRX_LINE_ID
, CA.CUST_ACCOUNT_ID
, RACTRL.INTERFACE_LINE_ATTRIBUTE14
, PS.ORG_ID UNION ALL SELECT ARL.CONTRACT_NUMBER CONTRACT_NUMBER
, ARL.CONTRACT_LINE_ID CONTRACT_LINE_ID
, ARL.LINE_NUMBER CONSOLIDATED_LINE_NUMBER
, ARL.LINE_TYPE LINE_NAME
, ARL.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, (ARL.AMOUNT_DUE_ORIGINAL - ARL.AMOUNT_DUE_REMAINING) AMOUNT_PAID
, /* ARL.AMOUNT_DUE_REMAINING BALANCE
, */ OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING( ARL.RECEIVABLES_INVOICE_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID) BALANCE
, /*( SELECT NVL(SUM(APP.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL SCH
WHERE APP.STATUS = 'APP'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = SCH.PAYMENT_SCHEDULE_ID
AND SCH.CLASS = 'INV'
AND SCH.CUSTOMER_TRX_ID = ARL.RECEIVABLES_INVOICE_ID
AND APP.APPLICATION_TYPE = 'CASH'
AND APP.APPLIED_CUSTOMER_TRX_ID = ARL.RECEIVABLES_INVOICE_ID
AND APP.APPLIED_CUSTOMER_TRX_LINE_ID = ARL.RECEIVABLES_INVOICE_LINE_ID ) AMOUNT_APPLIED
, */ OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED ( ARL.RECEIVABLES_INVOICE_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED ( ARL.RECEIVABLES_INVOICE_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID) AMOUNT_CREDITED
, ARL.TAX_AMOUNT TAX_AMOUNT
, ARL.RECEIVABLES_INVOICE_NUMBER RECEIVABLES_INVOICE_NUMBER
, STB.NAME STREAM_TYPE
, LKUP.MEANING STREAM_PURPOSE_MEANING
, ARL.CURRENCY_CODE CURRENCY_CODE
, ARL.RECEIVABLES_INVOICE_ID CUSTOMER_TRX_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID CUSTOMER_TRX_LINE_ID
, NULL CONSOLIDATED_INVOICE_NUMBER
, ARL.IXX_ID IXX_ID
, ARL.TXN_LINE_TYPE TXN_LINE_TYPE
, TO_CHAR(ARL.TXD_ID) TLD_ID
, ARL.ORG_ID ORG_ID
FROM OKL_BPD_AR_INV_LINES_V ARL
, HZ_CUST_ACCOUNTS CA
, OKL_STRM_TYPE_V STB
, FND_LOOKUPS LKUP
WHERE ARL.CLASS IN ('INV'
, 'CM')
AND CA.CUST_ACCOUNT_ID = ARL.IXX_ID
AND ARL.STREAM_TYPE_ID=STB.ID
AND LKUP.LOOKUP_CODE = STB.STREAM_TYPE_PURPOSE
AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND ARL.TXN_LINE_TYPE = 'LINE'

Columns