View: OKL_RCPT_INVOICE_LINES_UV

Product:	OKL - Lease and Finance Management
Description:	This view is used to fetch the open invoice lines for a contract or investor agreement or termination quote or non OKL invoices.
Implementation/DBA Data:	APPS.OKL_RCPT_INVOICE_LINES_UV

View Text

SELECT RA_CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, RA_CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, RA_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, RA_TRX_LINES.LINE_NUMBER INVOICE_LINE_NUMBER
, RA_CUST_TRX.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE INVOICE_DUE_DATE
, PAY_SCH.TERMS_SEQUENCE_NUMBER
, RA_CUST_TRX.ORG_ID
, RA_CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'LINE' LINE_TYPE
, RA_CUST_TRX.BILL_TO_CUSTOMER_ID
, (NVL(RA_TRX_LINES.EXTENDED_AMOUNT
, 0) + (SELECT NVL(SUM(AA.EXTENDED_AMOUNT)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID)) AMOUNT_DUE_ORIGINAL
, (NVL(RA_TRX_LINES.AMOUNT_DUE_REMAINING
, 0) + (SELECT NVL(SUM(AA.AMOUNT_DUE_REMAINING)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID)) AMOUNT_DUE_REMAINING
, NVL(RA_TRX_LINES.AMOUNT_DUE_REMAINING
, 0) LINE_BALANCE
, (SELECT NVL(SUM(AMOUNT_DUE_REMAINING)
, 0) LINE_TAX_AMOUNT
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND LINE_TYPE ='TAX') TAX_BALANCE
, ST.KHR_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, ST.KLE_ID
, RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE7 ASSET_NUMBER
, ST.STY_ID STY_ID
, RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE9 STREAM_TYPE
, QTE.ID TERM_QUOTE_ID
, QTE.QUOTE_NUMBER TERM_QUOTE_NUMBER
, NULL INVESTOR_AGREEMENT_NUMBER
, NULL INVESTOR_AGREEMENT_ID
FROM AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL RA_CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL RA_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B ST
, OKC_K_HEADERS_ALL_B CN
, OKL_TRX_QUOTES_ALL_B QTE
, OKL_TRX_AR_INVOICES_B TRX_AR
, OKL_TXL_AR_INV_LNS_B TXL_AR
WHERE RA_CUST_TRX.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RA_TRX_LINES.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND RA_CUST_TRX.INTERFACE_HEADER_CONTEXT IN ('OKL_CONTRACTS'
, 'OKL_MANUAL')
AND RA_TRX_LINES.INTERFACE_LINE_CONTEXT IN ('OKL_CONTRACTS'
, 'OKL_MANUAL')
AND PAY_SCH.STATUS = 'OP'
AND PAY_SCH.CLASS = 'INV'
AND RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND RA_TRX_LINES.LINE_TYPE = 'LINE'
AND ST.ID = RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND ST.KHR_ID = CN.ID
AND ST.TIL_ID_DETAILS = TXL_AR.ID
AND TXL_AR.TAI_ID = TRX_AR.ID
AND TRX_AR.QTE_ID = QTE.ID (+) UNION ALL SELECT RA_CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, RA_CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, RA_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, RA_TRX_LINES.LINE_NUMBER INVOICE_LINE_NUMBER
, RA_CUST_TRX.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE INVOICE_DUE_DATE
, PAY_SCH.TERMS_SEQUENCE_NUMBER
, RA_CUST_TRX.ORG_ID
, RA_CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'LINE' LINE_TYPE
, RA_CUST_TRX.BILL_TO_CUSTOMER_ID
, (NVL(RA_TRX_LINES.EXTENDED_AMOUNT
, 0) + (SELECT NVL(SUM(AA.EXTENDED_AMOUNT)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID)) AMOUNT_DUE_ORIGINAL
, (NVL(RA_TRX_LINES.AMOUNT_DUE_REMAINING
, 0) + (SELECT NVL(SUM(AA.AMOUNT_DUE_REMAINING)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID)) AMOUNT_DUE_REMAINING
, NVL(RA_TRX_LINES.AMOUNT_DUE_REMAINING
, 0) LINE_BALANCE
, (SELECT NVL(SUM(AMOUNT_DUE_REMAINING)
, 0) LINE_TAX_AMOUNT
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND LINE_TYPE = 'TAX') TAX_BALANCE
, NULL KHR_ID
, NULL CONTRACT_NUMBER
, NULL KLE_ID
, NULL ASSET_NUMBER
, ST.STY_ID STY_ID
, STRM.NAME STREAM_TYPE
, NULL TERM_QUOTE_ID
, NULL TERM_QUOTE_NUMBER
, RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 INVESTOR_AGREEMENT_NUMBER
, (SELECT ID
FROM OKC_K_HEADERS_ALL_B
WHERE CONTRACT_NUMBER = RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1) INVESTOR_AGREEMENT_ID
FROM AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL RA_CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL RA_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B ST
, OKL_STRM_TYPE_TL STRM
WHERE RA_CUST_TRX.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RA_TRX_LINES.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND RA_CUST_TRX.INTERFACE_HEADER_CONTEXT IN ('OKL_INVESTOR')
AND RA_TRX_LINES.INTERFACE_LINE_CONTEXT IN ('OKL_INVESTOR')
AND PAY_SCH.STATUS = 'OP'
AND PAY_SCH.CLASS = 'INV'
AND RA_TRX_LINES.LINE_TYPE = 'LINE'
AND ST.ID = RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND ST.STY_ID = STRM.ID
AND STRM.LANGUAGE = USERENV('LANG') UNION ALL SELECT HD.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, HD.ID CONSOLIDATED_INVOICE_ID
, RA_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, LN.SEQUENCE_NUMBER INVOICE_LINE_NUMBER
, HD.DATE_CONSOLIDATED INVOICE_DATE
, PAY_SCH.DUE_DATE INVOICE_DUE_DATE
, PAY_SCH.TERMS_SEQUENCE_NUMBER
, RA_CUST_TRX.ORG_ID
, RA_CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'LINE' LINE_TYPE
, RA_CUST_TRX.BILL_TO_CUSTOMER_ID
, PAY_SCH.AMOUNT_DUE_ORIGINAL
, PAY_SCH.AMOUNT_DUE_REMAINING
, PAY_SCH.AMOUNT_LINE_ITEMS_REMAINING LINE_BALANCE
, PAY_SCH.TAX_REMAINING TAX_BALANCE
, ST.KHR_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, ST.KLE_ID
, RA_CUST_TRX.INTERFACE_HEADER_ATTRIBUTE7 ASSET_NUMBER
, ST.STY_ID STY_ID
, RA_CUST_TRX.INTERFACE_HEADER_ATTRIBUTE9 STREAM_TYPE
, QTE.ID TERM_QUOTE_ID
, QTE.QUOTE_NUMBER TERM_QUOTE_NUMBER
, NULL INVESTOR_AGREEMENT_NUMBER
, NULL INVESTOR_AGREEMENT_ID
FROM OKL_CNSLD_AR_HDRS_ALL_B HD
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_STRMS_B ST
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL RA_CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL RA_TRX_LINES
, OKC_K_HEADERS_ALL_B CN
, OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TRX_QUOTES_ALL_B QTE
, OKL_TRX_AR_INVOICES_B TRX_AR
, OKL_TXL_AR_INV_LNS_B TXL_AR
WHERE RA_CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RA_TRX_LINES.LINE_TYPE = 'LINE'
AND CN.ID = ST.KHR_ID
AND PAY_SCH.CLASS = 'INV'
AND PAY_SCH.STATUS = 'OP'
AND ST.RECEIVABLES_INVOICE_ID = RA_CUST_TRX.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NOT NULL
AND PAY_SCH.AMOUNT_DUE_REMAINING > 0 /*-- FOLLOWING 3 OUTER JOINS SHOULD BE REMOVED AFTERT UPGRDE OF INTERFACE_LINE_ATTRIBUTE14 IS DONE FOR PRE R12 INVOICES (+) */
AND TLD.ID (+) = RA_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND TLD.TIL_ID_DETAILS = TXL_AR.ID (+)
AND TXL_AR.TAI_ID = TRX_AR.ID (+) /*-- ABOVE 3 OUTER JOINS SHOULD BE REMOVED AFTERT UPGRDE OF INTERFACE_LINE_ATTRIBUTE14 IS DONE FOR PRE R12 INVOICES (-)*/
AND TRX_AR.QTE_ID = QTE.ID (+) UNION ALL SELECT PAY_SCH.TRX_NUMBER INVOICE_NUMBER
, RA_CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, RA_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, RA_TRX_LINES.LINE_NUMBER INVOICE_LINE_NUMBER
, RA_CUST_TRX.TRX_DATE INVOICE_DATE
, PAY_SCH.DUE_DATE INVOICE_DUE_DATE
, PAY_SCH.TERMS_SEQUENCE_NUMBER
, RA_CUST_TRX.ORG_ID
, RA_CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'LINE' LINE_TYPE
, RA_CUST_TRX.BILL_TO_CUSTOMER_ID
, (NVL(RA_TRX_LINES.EXTENDED_AMOUNT
, 0) + (SELECT NVL(SUM(AA.EXTENDED_AMOUNT)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID)) AMOUNT_DUE_ORIGINAL
, (NVL(RA_TRX_LINES.AMOUNT_DUE_REMAINING
, 0) + (SELECT NVL(SUM(AA.AMOUNT_DUE_REMAINING)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL AA
WHERE AA.LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID)) AMOUNT_DUE_REMAINING
, NVL(RA_TRX_LINES.AMOUNT_DUE_REMAINING
, 0) LINE_BALANCE
, (SELECT NVL(SUM(AMOUNT_DUE_REMAINING)
, 0) LINE_TAX_AMOUNT
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE LINK_TO_CUST_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND LINE_TYPE = 'TAX') TAX_BALANCE
, NULL KHR_ID
, NULL CONTRACT_NUMBER
, NULL KLE_ID
, NULL ASSET_NUMBER
, NULL STY_ID
, NULL STREAM_TYPE
, NULL TERM_QUOTE_ID
, NULL TERM_QUOTE_NUMBER
, NULL INVESTOR_AGREEMENT_NUMBER
, NULL INVESTOR_AGREEMENT_ID
FROM AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL RA_CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL RA_TRX_LINES
WHERE RA_CUST_TRX.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RA_TRX_LINES.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND PAY_SCH.STATUS = 'OP'
AND PAY_SCH.CLASS = 'INV'
AND RA_TRX_LINES.LINE_TYPE = 'LINE'
AND RA_TRX_LINES.AMOUNT_DUE_REMAINING IS NOT NULL
AND (SELECT COUNT(1)
FROM AR_PAYMENT_SCHEDULES_ALL AA
WHERE AA.CUSTOMER_TRX_ID = RA_CUST_TRX.CUSTOMER_TRX_ID) = 1
AND NVL(RA_CUST_TRX.INTERFACE_HEADER_CONTEXT
, 'XX') NOT IN ('OKL_CONTRACTS'
, 'OKL_INVESTOR'
, 'OKL_MANUAL')
AND NVL(RA_TRX_LINES.INTERFACE_LINE_CONTEXT
, 'XX') NOT IN ('OKL_CONTRACTS'
, 'OKL_INVESTOR'
, 'OKL_MANUAL')

Columns