Introduction:
This query is about to get the advance collections related columns for the customers.
SELECT HCA.CUST_ACCOUNT_ID,
IST.STATUS_CODE STRATEGY_STATUS,
ISTT.STRATEGY_NAME,
(SELECT ISWI.STATUS_CODE FROM IEX_STRATEGY_WORK_ITEMS ISWI
WHERE ISWI.STATUS_CODE IN(‘OPEN’,’PRE-WAIT’)
AND ISWI.STRATEGY_ID=IST.STRATEGY_ID
AND ISWI.STRATEGY_TEMP_ID=ISTT.STRATEGY_TEMP_ID)WORK_ITEM_STATUS,
(SELECT ISTWIT.NAME FROM IEX_STRATEGY_WORK_ITEMS ISWI,IEX_STRY_TEMP_WORK_ITEMS_TL ISTWIT
WHERE ISWI.WORK_ITEM_TEMPLATE_ID=ISTWIT.WORK_ITEM_TEMP_ID
AND ISWI.STATUS_CODE IN(‘OPEN’,’PRE-WAIT’)
AND ISWI.STRATEGY_ID=IST.STRATEGY_ID
AND ISWI.STRATEGY_TEMP_ID=ISTT.STRATEGY_TEMP_ID
AND ISTWIT.LANGUAGE=’US’)WORK_ITEM_NAME,
(SELECT ISWI.EXECUTE_START FROM IEX_STRATEGY_WORK_ITEMS ISWI
WHERE ISWI.STATUS_CODE IN(‘OPEN’,’PRE-WAIT’)
AND ISWI.STRATEGY_ID=IST.STRATEGY_ID
AND ISWI.STRATEGY_TEMP_ID=ISTT.STRATEGY_TEMP_ID)EXECUTE_START,
(SELECT ISWI.EXECUTE_END FROM IEX_STRATEGY_WORK_ITEMS ISWI
WHERE ISWI.STATUS_CODE IN(‘OPEN’,’PRE-WAIT’)
AND ISWI.STRATEGY_ID=IST.STRATEGY_ID
AND ISWI.STRATEGY_TEMP_ID=ISTT.STRATEGY_TEMP_ID)EXECUTE_END,
CASE WHEN IPD.BROKEN_ON_DATE>sysdate-30 and IPD.BROKEN_ON_DATE<sysdate+30 THEN IPD.STATUS ELSE NULL END PROMISE_STATUS,
CASE WHEN IPD.BROKEN_ON_DATE>sysdate-30 and IPD.BROKEN_ON_DATE<sysdate+30 THEN IPD.STATE ELSE NULL END PROMISE_STATE,
CASE WHEN IPD.BROKEN_ON_DATE>sysdate-30 and IPD.BROKEN_ON_DATE<sysdate+30 THEN IPD.BROKEN_ON_DATE ELSE NULL END BROKEN_ON_DATE
FROM HZ_CUST_ACCOUNTS_ALL HCA
,IEX_STRATEGIES IST
,IEX_STRATEGY_TEMPLATES_TL ISTT
,IEX_PROMISE_DETAILS IPD
,iron.iron_sgc_dialer_cust_child_tbl ISDCC
WHERE HCA.CUST_ACCOUNT_ID=IST.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID=IPD.CUST_ACCOUNT_ID(+)
AND ISDCC.RAC_CUSTOMER_NUMBER=HCA.ACCOUNT_NUMBER
AND IST.ORG_ID=IPD.ORG_ID(+)
AND HCA.ORG_ID=IPD.ORG_ID(+)
AND HCA.PARTY_ID=IST.PARTY_ID
AND IST.ORG_ID=p_org_id
AND IST.STATUS_CODE IN(‘OPEN’,’ONHOLD’)
AND IST.STRATEGY_TEMPLATE_ID=ISTT.STRATEGY_TEMP_ID
AND ISTT.LANGUAGE =’US’;
Summary:
This query is described about the advance collections related columns for the customers.
Know more about post.
Doyen.ebiz@gmail.com
