VIEW: APPS.IEX_PROFILE_CASE_DEL_V

Source

View Text - Preformatted

SELECT party_cust_id, cust_account_id, delinquency_id, case_id, case_number, due_Date, amount_remaining, currency amount_remaining_cc, amount_overdue, currency amount_overdue_cc, account, currency, customer_site_use_id, party_site_number, location, address FROM (SELECT id.party_cust_id party_cust_id, id.cust_account_id cust_account_id, id.delinquency_id delinquency_id, id.case_id case_id, icv.case_number case_number, (select min(aps.due_date) from okl_cnsld_ar_strms_b ocas, ar_payment_schedules_all aps, iex_case_objects ico WHERE ocas.khr_id = ico.object_id AND ocas.receivables_invoice_id = aps.customer_trx_id AND aps.class = 'INV' AND aps.due_date < sysdate AND NVL(aps.amount_due_remaining, 0) > 0 and ico.object_code = 'CONTRACTS' and ico.cas_id = id.case_id) due_Date, IEX_CASE_INFO_PUB.get_total_rcvble_for_case_fn(id.case_id) amount_remaining, iex_case_info_pub.get_amount_overdue(id.case_id) amount_overdue, (select icd.column_value from iex_case_definitions icd where icd.cas_id = id.case_id and column_name = 'CUSTOMER_ACCOUNT') Account, (select icd.column_value from iex_case_definitions icd where icd.cas_id = id.case_id and column_name = 'CURRENCY_CODE') Currency, id.customer_site_use_id customer_site_use_id, icsuv.party_site_number party_site_number, icsuv.location location, icsuv.address address from iex_delinquencies id, iex_cust_site_uses_v icsuv, iex_cases_v icv WHERE icsuv.customer_site_use_id = id.customer_site_use_id and icv.cas_id = id.case_id and id.status <> 'CURRENT')

View Text - HTML Formatted

SELECT PARTY_CUST_ID
, CUST_ACCOUNT_ID
, DELINQUENCY_ID
, CASE_ID
, CASE_NUMBER
, DUE_DATE
, AMOUNT_REMAINING
, CURRENCY AMOUNT_REMAINING_CC
, AMOUNT_OVERDUE
, CURRENCY AMOUNT_OVERDUE_CC
, ACCOUNT
, CURRENCY
, CUSTOMER_SITE_USE_ID
, PARTY_SITE_NUMBER
, LOCATION
, ADDRESS
FROM (SELECT ID.PARTY_CUST_ID PARTY_CUST_ID
, ID.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ID.DELINQUENCY_ID DELINQUENCY_ID
, ID.CASE_ID CASE_ID
, ICV.CASE_NUMBER CASE_NUMBER
, (SELECT MIN(APS.DUE_DATE)
FROM OKL_CNSLD_AR_STRMS_B OCAS
, AR_PAYMENT_SCHEDULES_ALL APS
, IEX_CASE_OBJECTS ICO
WHERE OCAS.KHR_ID = ICO.OBJECT_ID
AND OCAS.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND APS.CLASS = 'INV'
AND APS.DUE_DATE < SYSDATE
AND NVL(APS.AMOUNT_DUE_REMAINING
, 0) > 0
AND ICO.OBJECT_CODE = 'CONTRACTS'
AND ICO.CAS_ID = ID.CASE_ID) DUE_DATE
, IEX_CASE_INFO_PUB.GET_TOTAL_RCVBLE_FOR_CASE_FN(ID.CASE_ID) AMOUNT_REMAINING
, IEX_CASE_INFO_PUB.GET_AMOUNT_OVERDUE(ID.CASE_ID) AMOUNT_OVERDUE
, (SELECT ICD.COLUMN_VALUE
FROM IEX_CASE_DEFINITIONS ICD
WHERE ICD.CAS_ID = ID.CASE_ID
AND COLUMN_NAME = 'CUSTOMER_ACCOUNT') ACCOUNT
, (SELECT ICD.COLUMN_VALUE
FROM IEX_CASE_DEFINITIONS ICD
WHERE ICD.CAS_ID = ID.CASE_ID
AND COLUMN_NAME = 'CURRENCY_CODE') CURRENCY
, ID.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, ICSUV.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, ICSUV.LOCATION LOCATION
, ICSUV.ADDRESS ADDRESS
FROM IEX_DELINQUENCIES ID
, IEX_CUST_SITE_USES_V ICSUV
, IEX_CASES_V ICV
WHERE ICSUV.CUSTOMER_SITE_USE_ID = ID.CUSTOMER_SITE_USE_ID
AND ICV.CAS_ID = ID.CASE_ID
AND ID.STATUS <> 'CURRENT')