VIEW: APPS.IEX_OWNERSHIPS_V

Source

View Text - Preformatted

SELECT p.party_name party_name, h.account_number account_number, l.location location, a.resource_id resource_id, p.party_id party_id, h.cust_account_id cust_account_id, l.site_use_id customer_site_use_id, 0 work_item_count, COUNT(*) promise_count, SUM(a.amount_due_remaining) amount_due_remaining FROM iex_promise_details a, hz_cust_accounts h, hz_parties p, iex_delinquencies_all d, hz_cust_site_uses_all l WHERE (UPPER(a.status) = 'BROKEN' OR UPPER(a.status) = 'COLLECTABLE') AND h.cust_account_id = a.cust_account_id AND h.party_id = p.party_id AND a.delinquency_id = d.delinquency_id(+) AND d.customer_site_use_id = l.site_use_id(+) GROUP BY p.party_name, h.account_number, l.location, a.resource_id, p.party_id, h.cust_account_id, l.site_use_id UNION ALL SELECT p.party_name party_name, h.account_number account_number, l.location location, a.resource_id resource_id, p.party_id party_id, h.cust_account_id cust_account_id, l.site_use_id customer_site_use_id, COUNT(*) work_item_count, 0 promise_count, 0 amount_due_remaining FROM iex_strategy_work_items a, iex_strategies s, hz_cust_accounts h, hz_parties p, hz_cust_site_uses_all l WHERE a.status_code = 'OPEN' AND s.strategy_id = a.strategy_id AND TRUNC(a.schedule_start) = TRUNC(a.schedule_start) AND s.party_id = p.party_id(+) AND s.cust_account_id = h.cust_account_id(+) AND l.site_use_id(+) = s.customer_site_use_id GROUP BY p.party_name, h.account_number, l.location, a.resource_id, p.party_id, h.cust_account_id, l.site_use_id

View Text - HTML Formatted

SELECT P.PARTY_NAME PARTY_NAME
, H.ACCOUNT_NUMBER ACCOUNT_NUMBER
, L.LOCATION LOCATION
, A.RESOURCE_ID RESOURCE_ID
, P.PARTY_ID PARTY_ID
, H.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, L.SITE_USE_ID CUSTOMER_SITE_USE_ID
, 0 WORK_ITEM_COUNT
, COUNT(*) PROMISE_COUNT
, SUM(A.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
FROM IEX_PROMISE_DETAILS A
, HZ_CUST_ACCOUNTS H
, HZ_PARTIES P
, IEX_DELINQUENCIES_ALL D
, HZ_CUST_SITE_USES_ALL L
WHERE (UPPER(A.STATUS) = 'BROKEN' OR UPPER(A.STATUS) = 'COLLECTABLE')
AND H.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND H.PARTY_ID = P.PARTY_ID
AND A.DELINQUENCY_ID = D.DELINQUENCY_ID(+)
AND D.CUSTOMER_SITE_USE_ID = L.SITE_USE_ID(+) GROUP BY P.PARTY_NAME
, H.ACCOUNT_NUMBER
, L.LOCATION
, A.RESOURCE_ID
, P.PARTY_ID
, H.CUST_ACCOUNT_ID
, L.SITE_USE_ID UNION ALL SELECT P.PARTY_NAME PARTY_NAME
, H.ACCOUNT_NUMBER ACCOUNT_NUMBER
, L.LOCATION LOCATION
, A.RESOURCE_ID RESOURCE_ID
, P.PARTY_ID PARTY_ID
, H.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, L.SITE_USE_ID CUSTOMER_SITE_USE_ID
, COUNT(*) WORK_ITEM_COUNT
, 0 PROMISE_COUNT
, 0 AMOUNT_DUE_REMAINING
FROM IEX_STRATEGY_WORK_ITEMS A
, IEX_STRATEGIES S
, HZ_CUST_ACCOUNTS H
, HZ_PARTIES P
, HZ_CUST_SITE_USES_ALL L
WHERE A.STATUS_CODE = 'OPEN'
AND S.STRATEGY_ID = A.STRATEGY_ID
AND TRUNC(A.SCHEDULE_START) = TRUNC(A.SCHEDULE_START)
AND S.PARTY_ID = P.PARTY_ID(+)
AND S.CUST_ACCOUNT_ID = H.CUST_ACCOUNT_ID(+)
AND L.SITE_USE_ID(+) = S.CUSTOMER_SITE_USE_ID GROUP BY P.PARTY_NAME
, H.ACCOUNT_NUMBER
, L.LOCATION
, A.RESOURCE_ID
, P.PARTY_ID
, H.CUST_ACCOUNT_ID
, L.SITE_USE_ID