VIEW: APPS.IEX_AVAIL_CONS_INVOICES_V

Source

View Text - Preformatted

SELECT okc.contract_number, okc.id, HDR.ID, HDR.CONSOLIDATED_INVOICE_NUMBER, HDR.DUE_DATE, HDR.CURRENCY_CODE, HDR.TRX_STATUS_CODE, HDR.AMOUNT, SUM(PS.AMOUNT_DUE_ORIGINAL), SUM(PS.AMOUNT_APPLIED), SUM(PS.AMOUNT_DUE_REMAINING), ico.cas_id from ar_payment_schedules ps, okc_k_headers_b OKC, iex_delinquencies del, iex_case_objects ico ,OKL_CNSLD_AR_HDRS_B HDR ,OKL_CNSLD_AR_LINES_B LINE ,OKL_CNSLD_AR_STRMS_B STREAM ,okl_strm_type_v strm WHERE PS.CLASS IN ('INV' ,'CM','DB') AND ps.amount_due_remaining > 0 and okc.id =ico.object_id and del.transaction_id =ps.customer_trx_id and STREAM.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID and stream.khr_id =okc.id AND HDR.ID = LINE.CNR_ID AND STREAM.LLN_ID = LINE.ID and strm.id=stream.sty_id and strm.name <>'CURE' and not exists (select 1 from iex_writeoff_objects iwo1 where iwo1.TRANSACTION_ID =ps.PAYMENT_SCHEDULE_ID and iwo1.writeoff_status <>'R' ) GROUP BY okc.contract_number, okc.id, HDR.ID, HDR.CONSOLIDATED_INVOICE_NUMBER, HDR.DUE_DATE, HDR.CURRENCY_CODE, HDR.TRX_STATUS_CODE, HDR.AMOUNT, ico.cas_id

View Text - HTML Formatted

SELECT OKC.CONTRACT_NUMBER
, OKC.ID
, HDR.ID
, HDR.CONSOLIDATED_INVOICE_NUMBER
, HDR.DUE_DATE
, HDR.CURRENCY_CODE
, HDR.TRX_STATUS_CODE
, HDR.AMOUNT
, SUM(PS.AMOUNT_DUE_ORIGINAL)
, SUM(PS.AMOUNT_APPLIED)
, SUM(PS.AMOUNT_DUE_REMAINING)
, ICO.CAS_ID
FROM AR_PAYMENT_SCHEDULES PS
, OKC_K_HEADERS_B OKC
, IEX_DELINQUENCIES DEL
, IEX_CASE_OBJECTS ICO
, OKL_CNSLD_AR_HDRS_B HDR
, OKL_CNSLD_AR_LINES_B LINE
, OKL_CNSLD_AR_STRMS_B STREAM
, OKL_STRM_TYPE_V STRM
WHERE PS.CLASS IN ('INV'
, 'CM'
, 'DB')
AND PS.AMOUNT_DUE_REMAINING > 0
AND OKC.ID =ICO.OBJECT_ID
AND DEL.TRANSACTION_ID =PS.CUSTOMER_TRX_ID
AND STREAM.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
AND STREAM.KHR_ID =OKC.ID
AND HDR.ID = LINE.CNR_ID
AND STREAM.LLN_ID = LINE.ID
AND STRM.ID=STREAM.STY_ID
AND STRM.NAME <>'CURE'
AND NOT EXISTS (SELECT 1
FROM IEX_WRITEOFF_OBJECTS IWO1
WHERE IWO1.TRANSACTION_ID =PS.PAYMENT_SCHEDULE_ID
AND IWO1.WRITEOFF_STATUS <>'R' ) GROUP BY OKC.CONTRACT_NUMBER
, OKC.ID
, HDR.ID
, HDR.CONSOLIDATED_INVOICE_NUMBER
, HDR.DUE_DATE
, HDR.CURRENCY_CODE
, HDR.TRX_STATUS_CODE
, HDR.AMOUNT
, ICO.CAS_ID