View: OKL_CS_BPD_INV_DTL_V

Product:	OKL - Lease and Finance Management
Description:	New CS Billing base view
Implementation/DBA Data:	APPS.OKL_CS_BPD_INV_DTL_V

View Text

SELECT CNR.ID INVOICE_ID
, KHR.ID CHR_ID
, LSM.ID LSM_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, CNR.CURRENCY_CODE INVOICE_CURRENCY_CODE
, CNR.DUE_DATE DUE_DATE
, CNR.AMOUNT INVOICE_AMOUNT
, LSM.RECEIVABLES_INVOICE_ID AR_INVOICE_ID
, CNR.DATE_CONSOLIDATED INVOICE_DATE
, CNR.IBT_ID BILL_TO_SITE_USE_ID
, CNR.IXX_ID CUSTOMER_ACCT_ID
, CNR.ORG_ID ORG_ID
, 'OKL-CONS-INV' INVOICE_TYPE
, KHR.CONTRACT_NUMBER CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, LSM.STY_ID STY_ID
, LSM.KLE_ID KLE_ID
, LSM.AMOUNT LINE_AMOUNT
, LSM.TAX_AMOUNT TAX_AMOUNT
, APS.TAX_REMAINING TAX_REMAINING
, TO_NUMBER(APS.TRX_NUMBER) LINE_IDENTIFIER
, NVL(LSM.AMOUNT + LSM.TAX_AMOUNT
, 0) AMOUNT_ORIGINAL
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_APPLIED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_CREDITED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_REMAINING(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_REMAINING
FROM OKC_K_HEADERS_B KHR
, OKL_CNSLD_AR_HDRS_B CNR
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_STRMS_B LSM
, AR_PAYMENT_SCHEDULES_ALL APS
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
WHERE CNR.ID = LLN.CNR_ID
AND LLN.ID = LSM.LLN_ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.KHR_ID = KHR.ID
AND LSM.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE' UNION ALL SELECT CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, CHR.ID CHR_ID
, TLD.ID LSM_ID
, CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PAY_SCH.DUE_DATE DUE_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL INVOICE_AMOUNT
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CUST_TRX.BILL_TO_CUSTOMER_ID CUSTOMER_ACCT_ID
, CUST_TRX.ORG_ID ORG_ID
, 'AR-INVOICE' INVOICE_TYPE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, TLD.STY_ID STY_ID
, TLD.KLE_ID KLE_ID
, EXTENDED_AMOUNT LINE_AMOUNT
, (SELECT SUM(NVL(EXTENDED_AMOUNT
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_AMOUNT
, (SELECT SUM(NVL(AMOUNT_DUE_REMAINING
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_REMAINING
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ORIG(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ORIGINAL
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_REMAINING
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B TLD
, OKC_K_HEADERS_B CHR
WHERE CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND TLD.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CHR.CONTRACT_NUMBER = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')
AND CHR.SCS_CODE ='LEASE'

Columns