VIEW: APPS.IEX_AVAIL_OKL_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, LINE.SEQUENCE_NUMBER, LINE.ID, SM.NAME, ps.CUSTOMER_TRX_ID, PS.TRX_NUMBER, ps.payment_schedule_id, SUM(PS.AMOUNT_DUE_ORIGINAL), SUM(PS.AMOUNT_APPLIED), SUM(PS.AMOUNT_DUE_REMAINING), PS.TRX_DATE, ico.cas_id, del.delinquency_id FROM AR_PAYMENT_SCHEDULES PS, OKL_CNSLD_AR_HDRS_B HDR, OKL_CNSLD_AR_LINES_B LINE, OKL_CNSLD_AR_STRMS_B STREAM, okc_k_headers_b OKC, iex_delinquencies del, iex_case_objects ico, OKL_STRM_TYPE_v SM WHERE PS.CLASS IN ('INV' ,'CM','DB') AND ps.amount_due_remaining > 0 and ps.customer_trx_id=del.transaction_id and STREAM.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID AND HDR.ID = LINE.CNR_ID AND STREAM.LLN_ID = LINE.ID AND SM.ID = STREAM.STY_ID AND sm.name <>'CURE' and stream.khr_id =okc.id and okc.id =ico.object_id 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, LINE.SEQUENCE_NUMBER, LINE.ID, SM.NAME, ps.CUSTOMER_TRX_ID, PS.TRX_NUMBER, ps.payment_schedule_id, PS.TRX_DATE, ico.cas_id, del.delinquency_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
, LINE.SEQUENCE_NUMBER
, LINE.ID
, SM.NAME
, PS.CUSTOMER_TRX_ID
, PS.TRX_NUMBER
, PS.PAYMENT_SCHEDULE_ID
, SUM(PS.AMOUNT_DUE_ORIGINAL)
, SUM(PS.AMOUNT_APPLIED)
, SUM(PS.AMOUNT_DUE_REMAINING)
, PS.TRX_DATE
, ICO.CAS_ID
, DEL.DELINQUENCY_ID
FROM AR_PAYMENT_SCHEDULES PS
, OKL_CNSLD_AR_HDRS_B HDR
, OKL_CNSLD_AR_LINES_B LINE
, OKL_CNSLD_AR_STRMS_B STREAM
, OKC_K_HEADERS_B OKC
, IEX_DELINQUENCIES DEL
, IEX_CASE_OBJECTS ICO
, OKL_STRM_TYPE_V SM
WHERE PS.CLASS IN ('INV'
, 'CM'
, 'DB')
AND PS.AMOUNT_DUE_REMAINING > 0
AND PS.CUSTOMER_TRX_ID=DEL.TRANSACTION_ID
AND STREAM.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
AND HDR.ID = LINE.CNR_ID
AND STREAM.LLN_ID = LINE.ID
AND SM.ID = STREAM.STY_ID
AND SM.NAME <>'CURE'
AND STREAM.KHR_ID =OKC.ID
AND OKC.ID =ICO.OBJECT_ID
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
, LINE.SEQUENCE_NUMBER
, LINE.ID
, SM.NAME
, PS.CUSTOMER_TRX_ID
, PS.TRX_NUMBER
, PS.PAYMENT_SCHEDULE_ID
, PS.TRX_DATE
, ICO.CAS_ID
, DEL.DELINQUENCY_ID