VIEW: APPS.IEX_AVAIL_CONTRACTS_V

Source

View Text - Preformatted

SELECT okc.contract_number, okc.id, okc.currency_code, 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_case_objects ico ,iex_delinquencies del ,okl_strm_type_v strm ,okl_cnsld_ar_strms_b stream WHERE del.transaction_id =ps.customer_trx_id and PS.CLASS IN ('INV' ,'CM','DB') AND ps.amount_due_remaining > 0 and stream.receivables_invoice_id=ps.customer_trx_id and stream.khr_id=okc.id and okc.id =ico.object_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 ,okc.currency_code ,ico.cas_id

View Text - HTML Formatted

SELECT OKC.CONTRACT_NUMBER
, OKC.ID
, OKC.CURRENCY_CODE
, 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_CASE_OBJECTS ICO
, IEX_DELINQUENCIES DEL
, OKL_STRM_TYPE_V STRM
, OKL_CNSLD_AR_STRMS_B STREAM
WHERE DEL.TRANSACTION_ID =PS.CUSTOMER_TRX_ID
AND PS.CLASS IN ('INV'
, 'CM'
, 'DB')
AND PS.AMOUNT_DUE_REMAINING > 0
AND STREAM.RECEIVABLES_INVOICE_ID=PS.CUSTOMER_TRX_ID
AND STREAM.KHR_ID=OKC.ID
AND OKC.ID =ICO.OBJECT_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
, OKC.CURRENCY_CODE
, ICO.CAS_ID