eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKL_RCPT_INVOICE_LINES_UV

Source

View Text - Preformatted


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')

View Text - HTML Formatted

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')