VIEW: APPS.OKL_RCPT_ARINV_BALANCES_UV

Source

View Text - Preformatted

SELECT cUst_trx.trx_Number Invoice_Number, cUst_trx.Customer_trx_Id ar_Invoice_Id, cUst_trx_Lines.CUSTOMER_TRX_LINE_ID Invoice_Line_Id, cUst_trx.trx_Date Invoice_Date, cUst_trx.Invoice_Currency_Code Invoice_Currency_Code, 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, cUst_trx_Lines.Line_Number Line_IdentIfier, 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 cUst_trx_Lines.InterFace_Line_Context IN ( 'OKL_CONTRACTS','OKL_MANUAL') AND ar.class = 'INV'

View Text - HTML Formatted

SELECT CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, 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
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, 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 CUST_TRX_LINES.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')
AND AR.CLASS = 'INV'