Notes

SELECT DISTINCT TA.MCASE_ID,TC.CIF_no,
MH.CD,
A.AMT_OS_PRINCIPAL,
A.DUE_PRINCIPAL_AMT,
TA.CURR_USER_ID,BR.CD BR_CD
FROM EBDCRS.T_ASSIGNMENTS TA
INNER JOIN EBDCRS.T_WORKFLOW_HISTS WH
ON TA.MCASE_ID = WH.MCASE_ID
INNER JOIN EBDCRS.T_MT_ROUTING_TYPS MRT
ON MRT.ID = WH.MT_ROUTING_TYP_ID
AND MRT.CD IN ('CASE_REASSIGNMENT','ROUTING')
INNER JOIN EBDCRS.T_MCASES MC
ON MC.ID = TA.MCASE_ID
INNER JOIN EBDCRS.T_MT_DEPTS DEPT
ON MC.MT_DEPT_ID = DEPT.ID
INNER JOIN EBDCRS.T_CASES TC
ON TC.MCASE_ID = MC.ID
AND TA.MCASE_ID = TC.MCASE_ID
AND TC.IS_OUT_OF_BOUND = 'N'
inner join
(select TCa.CIF_NO,
taa.MCASE_ID from ebdcrs.t_assignments taa
INNER JOIN ebdcrs.t_cases tca ON taa.mcase_id = tca.mcase_id and tca.id = taa.worst_aging_case_id AND TCa.is_cured = 'N' and tca.is_out_of_bound ='N' and tca.is_out_of_group = 'N' and TCa.CPF_FLAG = 'Y'
) CIFNO on CIFNO.MCASE_ID=TC.MCASE_ID and CIFNO.CIF_NO = TC.CIF_NO
INNER JOIN EBDCRS.T_MT_HOSTS MH
ON MH.ID = TC.ACCT_MT_HOST_ID
INNER JOIN EBDCRS.T_HT_ACCTS A
ON A.ACCT_NO = TC.ACCT_NO
AND A.CPF_FLAG = 'Y'
INNER JOIN
(SELECT
BR.ID,BR.CD,BR.DSCP,
BR.SYS_APF_CD
FROM EBDCRS.T_MT_BRANCHES BR
INNER JOIN EBDCRS.T_MT_BR_HRCHY_LVLS LVL
ON LVL.ID = BR.MT_BR_HRCHY_LVL_ID
WHERE LVL.CD = '20' – CHI NH?NH
AND BR.IS_DEL = 'N'
AND BR.IS_DEACTIVATE = 'N'
) BR
ON BR.ID = A.MT_BR_ID
WHERE DEPT.CD = 'RECOVERY'
AND TC.MT_MOD_ID = (SELECT ID
FROM EBDCRS.T_MT_MODS
WHERE CD = 'RC'
AND IS_DEL = 'N')
AND (
(EXISTS
(SELECT 1
FROM EBDCRS.T_SKIP_ASGN_CASES SAC
INNER JOIN EBDCRS.T_MT_SKIP_ASGN_RSN SAR
ON SAR.ID = SAC.MT_SKIP_ASGN_RSN_ID
AND SAR.SYS_APF_CD = SAC.SYS_APF_CD
WHERE SAR.CD IN ('CASE_REASSIGNMENT', 'ROUTING')
AND SAC.MCASE_ID = TA.MCASE_ID)
AND MC.IS_SKIP_TRACE <> 'Y'
AND NVL (MC.IS_ST_ASSIGNED, 'N') <> 'Y')
OR TA.DT_ROUTE IS NOT NULL)