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_CUST_CONT_BALANCES_UV

Source

View Text - Preformatted


SELECT HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, HD.ID CONSOLIDATED_INVOICE_ID, HD.CURRENCY_CODE, HD.DATE_CONSOLIDATED INVOICE_DATE, LN.SEQUENCE_NUMBER LINE_NUMBER , cUst_trx.trx_Number AR_Invoice_Number, cUst_trx.Customer_trx_Id ar_Invoice_Id, cUst_trx_Lines.CUSTOMER_TRX_LINE_ID Invoice_Line_Id, cUst_trx.Org_Id Org_Id, cUst.Party_Name Customer_Name, cust.party_number CUSTOMER_NUMBER, acct.cUst_Account_Id Customer_acct_Id, acct.Account_Number Customer_ACCOUNT_Number, cUst_trx.Bill_To_Site_Use_Id Bill_To_Site_Use_Id, st.khr_id, CN.Contract_Number Contract_Number, CN.START_DATE CONTRACT_START_DATE, cUst_trx.InterFace_header_Attribute7 Asset_Number, cUst_trx.InterFace_header_Attribute9 stream_type, st.sty_id, st.kle_id, st.sel_id, pay_sch.amount_due_original amount_due_original, pay_sch.amount_due_remaining amount_due_remaining, pay_sch.due_date, pay_sch.trx_date trx_date, pay_sch.status status FROM ra_customer_trx_all cust_trx, ra_customer_trx_lines_all cust_trx_lines, hz_cust_accounts acct, hz_parties cust, ar_payment_schedules_all pay_sch, okl_cnsld_ar_strms_b st, okl_cnsld_ar_lines_b ln, okl_cnsld_ar_hdrs_all_b hd, okc_k_headers_all_b cn WHERE cust_trx.bill_to_customer_id = acct.cust_account_id AND acct.party_id = cust.party_id AND cust_trx.customer_trx_id = pay_sch.customer_trx_id AND cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id AND cust_trx_lines.line_type = 'LINE' AND cn.id = st.khr_id AND pay_sch.class = 'INV' AND st.receivables_invoice_id = cust_trx.customer_trx_id AND ln.id = st.lln_id AND hd.id = ln.cnr_id AND cust_trx_lines.interface_line_attribute1 IS NOT NULL UNION ALL SELECT NULL consolidated_invoice_number, NULL consolidated_invoice_id, cust_trx.invoice_currency_code currency_code, cust_trx.trx_date invoice_date, cust_trx_lines.line_number line_number, cust_trx.trx_number ar_invoice_number, cust_trx.customer_trx_id ar_invoice_id, cust_trx_lines.customer_trx_line_id invoice_line_id, cust_trx.org_id org_id, cust.party_name customer_name, cust.party_number customer_number, acct.cust_account_id customer_acct_id, acct.account_number customer_account_number, cust_trx.bill_to_site_use_id bill_to_site_use_id, st.khr_id, cn.contract_number contract_number, cn.start_date start_date, cust_trx_lines.interface_line_attribute7 asset_number, cust_trx_lines.interface_line_attribute9 stream_type, st.sty_id sty_id, st.kle_id, st.sel_id, (nvl(cUst_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 = cUst_trx_Lines.Customer_trx_Line_Id and aa.line_type ='TAX'))Amount_Due_Original , (nvl(cUst_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 = cUst_trx_Lines.Customer_trx_Line_Id and aa.line_type ='TAX')) Amount_Due_remaining, ar.due_date invoice_due_date, ar.trx_date trx_date, ar.status status FROM ra_customer_trx_all cust_trx, ra_customer_trx_lines_all cust_trx_lines, hz_cust_accounts acct, hz_parties cust, ar_payment_schedules_all ar, okl_txd_ar_ln_dtls_b st, okc_k_headers_all_b cn WHERE cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id AND cust_trx.bill_to_customer_id = acct.cust_account_id AND acct.party_id = cust.party_id AND cust_trx_lines.line_type = 'LINE' AND cust_trx_lines.interface_line_attribute1 IS NULL AND cust_trx_lines.customer_trx_id = ar.customer_trx_id AND cust_trx.customer_trx_id = ar.customer_trx_id AND st.id = cust_trx_lines.interface_line_attribute14 AND st.khr_id = cn.id AND ar.CLASS = 'INV' AND cust_trx_lines.interface_line_context IN ( 'OKL_CONTRACTS','OKL_MANUAL')

View Text - HTML Formatted

SELECT HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, HD.ID CONSOLIDATED_INVOICE_ID
, HD.CURRENCY_CODE
, HD.DATE_CONSOLIDATED INVOICE_DATE
, LN.SEQUENCE_NUMBER LINE_NUMBER
, CUST_TRX.TRX_NUMBER AR_INVOICE_NUMBER
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, CUST_TRX.ORG_ID ORG_ID
, CUST.PARTY_NAME CUSTOMER_NAME
, CUST.PARTY_NUMBER CUSTOMER_NUMBER
, ACCT.CUST_ACCOUNT_ID CUSTOMER_ACCT_ID
, ACCT.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, ST.KHR_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, CN.START_DATE CONTRACT_START_DATE
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE7 ASSET_NUMBER
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE9 STREAM_TYPE
, ST.STY_ID
, ST.KLE_ID
, ST.SEL_ID
, PAY_SCH.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PAY_SCH.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PAY_SCH.DUE_DATE
, PAY_SCH.TRX_DATE TRX_DATE
, PAY_SCH.STATUS STATUS
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_ALL_B HD
, OKC_K_HEADERS_ALL_B CN
WHERE CUST_TRX.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CN.ID = ST.KHR_ID
AND PAY_SCH.CLASS = 'INV'
AND ST.RECEIVABLES_INVOICE_ID = CUST_TRX.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NOT NULL UNION ALL SELECT NULL CONSOLIDATED_INVOICE_NUMBER
, NULL CONSOLIDATED_INVOICE_ID
, CUST_TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX_LINES.LINE_NUMBER LINE_NUMBER
, CUST_TRX.TRX_NUMBER AR_INVOICE_NUMBER
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, CUST_TRX.ORG_ID ORG_ID
, CUST.PARTY_NAME CUSTOMER_NAME
, CUST.PARTY_NUMBER CUSTOMER_NUMBER
, ACCT.CUST_ACCOUNT_ID CUSTOMER_ACCT_ID
, ACCT.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, ST.KHR_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, CN.START_DATE START_DATE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE7 ASSET_NUMBER
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE9 STREAM_TYPE
, ST.STY_ID STY_ID
, ST.KLE_ID
, ST.SEL_ID
, (NVL(CUST_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 = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND AA.LINE_TYPE ='TAX'))AMOUNT_DUE_ORIGINAL
, (NVL(CUST_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 = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND AA.LINE_TYPE ='TAX')) AMOUNT_DUE_REMAINING
, AR.DUE_DATE INVOICE_DUE_DATE
, AR.TRX_DATE TRX_DATE
, AR.STATUS STATUS
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, AR_PAYMENT_SCHEDULES_ALL AR
, OKL_TXD_AR_LN_DTLS_B ST
, OKC_K_HEADERS_ALL_B CN
WHERE CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND CUST_TRX.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.CUSTOMER_TRX_ID = AR.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = AR.CUSTOMER_TRX_ID
AND ST.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND ST.KHR_ID = CN.ID
AND AR.CLASS = 'INV'
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')