Notes

SELECT
POOL.CD AS POOL_CD,
POOL.DSCP AS POOL_DSCP,
CUS.CIF_NO AS CIF_NO,
sum(ACCT.PDD) AS DPD,
-- ACCT.FINAL_DEBT_GRP AS DEBT_GRP,
dg.cd AS DEBT_GRP,
CUS.NM_LINE_1 AS CIF_NM,
sum(TC.outstd_amt) AS AMT_OS_BAL,
-- sum(APPROVE.NO_APPROVE) AS NO_APPROVE_SUBMIT_CASES,
-- sum(PENDING.NO_PENDING) AS NO_PENDING_SUBMIT_CASES,
(SELECT COUNT(1)
FROM
ebdcrs.t_cl_plan_actv_routing r
INNER JOIN EBDCRS.t_ht_cifs c on c.id = r.ht_cif_id
INNER JOIN ebdcrs.t_cl_plan_actv_routing_hist h
ON h.cl_plan_actv_routing_id = r.id
WHERE r.mcase_id = ASGNMS.mcase_id AND h.approval_sts = 'A'
AND h.dt_approve BETWEEN trunc (sysdate, 'mm') AND SYSDATE-1
) AS NO_APPROVE_SUBMIT_CASES,
(SELECT COUNT(1)
FROM
ebdcrs.t_cl_plan_actv_routing r
INNER JOIN EBDCRS.t_ht_cifs c on c.id = r.ht_cif_id
INNER JOIN ebdcrs.t_cl_plan_actv_routing_hist h
ON h.cl_plan_actv_routing_id = r.id
WHERE r.mcase_id = ASGNMS.mcase_id AND h.approval_sts = 'P'
AND r.dt_submitted BETWEEN trunc (sysdate, 'mm') AND SYSDATE-1
) AS NO_PENDING_SUBMIT_CASES,
BR.DSCP AS BR_NM,
TRO.DSCP AS TO_NM,
USR.NM AS RM_NM,
SYSDATE-1 AS DT_RPT,
NULL AS BATCH_ID,
BR.ID AS BR_ID,
BR.CD AS BR_CD
FROM EBDCRS.T_ASSIGNMENTS ASGNMS
INNER JOIN EBDCRS.T_MCASES M_CASE ON M_CASE.ID = ASGNMS.MCASE_ID and M_CASE.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
INNER JOIN EBDCRS.T_CASES TC ON TC.MCASE_ID = ASGNMS.MCASE_ID
and TC.SYS_APF_CD = ASGNMS.SYS_APF_CD
and TC.IS_OUT_OF_BOUND = 'N'
AND TC.IS_OUT_OF_GROUP = 'N'
AND TC.IS_CURED = 'N'
INNER JOIN EBDCRS.T_HT_CIFS CUS ON CUS.SYS_APF_CD = TC.CIF_SYS_APF_CD AND CUS.MT_HOST_ID = TC.CIF_MT_HOST_ID AND CUS.CIF_NO = TC.CIF_NO
INNER JOIN EBDCRS.T_MT_POOLS POOL ON ASGNMS.CURR_MT_POOL_ID = POOL.ID
INNER JOIN EBDCRS.T_HT_ACCTS ACCT ON ACCT.ACCT_NO = TC.ACCT_NO AND ACCT.SYS_APF_CD = TC.SYS_APF_CD and ACCT.CPF_FLAG='Y'
inner join (Select BR.CD, BR.DSCP, BR.ID,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
and BR.SYS_APF_CD='SYSVN'
and LVL.CD = '20'
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N') BR ON BR.ID = ACCT.MT_BR_ID and BR.SYS_APF_CD = ACCT.SYS_APF_CD
inner join (Select BR.CD, BR.DSCP, BR.ID,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
and BR.SYS_APF_CD='SYSVN'
and LVL.CD = '10'
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N') TRO on TRO.ID = ACCT.MT_TO_ID and TRO.SYS_APF_CD = ACCT.SYS_APF_CD
LEFT JOIN EBDSMS.TBL_SEC_USER USR ON USR.ID = ASGNMS.CURR_USER_ID

INNER JOIN ebdcrs.t_mt_debt_grps dg ON acct.mt_debt_grp_id = dg.id
WHERE
TO_CHAR(ASGNMS.DT_ASSIGNED,'DD-MM-YYYY') = TO_CHAR(SYSDATE-1,'DD-MM-YYYY')AND (ACCT.PDD <=9 and ACCT.PDD >0) or ACCT.FINAL_DEBT_GRP = 2
GROUP BY POOL.CD,POOL.DSCP ,CUS.CIF_NO ,dg.cd ,CUS.NM_LINE_1,BR.DSCP ,TRO.DSCP ,USR.NM ,TO_CHAR(ASGNMS.DT_ASSIGNED,'DD-MM-YYYY'),BR.ID ,BR.CD,ASGNMS.mcase_id;
===============
SELECT
POOL.CD AS POOL_CD,
POOL.DSCP AS POOL_DSCP,
CUS.CIF_NO AS CIF_NO,
sum(ACCT.PDD) AS DPD,
-- ACCT.FINAL_DEBT_GRP AS DEBT_GRP,
dg.cd AS DEBT_GRP,
CUS.NM_LINE_1 AS CIF_NM,
sum(TC.outstd_amt) AS AMT_OS_BAL,
-- sum(APPROVE.NO_APPROVE) AS NO_APPROVE_SUBMIT_CASES,
-- sum(PENDING.NO_PENDING) AS NO_PENDING_SUBMIT_CASES,
(SELECT COUNT(1)
FROM
ebdcrs.t_cl_plan_actv_routing r
INNER JOIN EBDCRS.t_ht_cifs c on c.id = r.ht_cif_id
INNER JOIN ebdcrs.t_cl_plan_actv_routing_hist h
ON h.cl_plan_actv_routing_id = r.id
WHERE r.mcase_id = ASGNMS.mcase_id AND h.approval_sts = 'A'
AND h.dt_approve BETWEEN trunc (sysdate, 'mm') AND SYSDATE-1
) AS NO_APPROVE_SUBMIT_CASES,
(SELECT COUNT(1)
FROM
ebdcrs.t_cl_plan_actv_routing r
INNER JOIN EBDCRS.t_ht_cifs c on c.id = r.ht_cif_id
INNER JOIN ebdcrs.t_cl_plan_actv_routing_hist h
ON h.cl_plan_actv_routing_id = r.id
WHERE r.mcase_id = ASGNMS.mcase_id AND h.approval_sts = 'P'
AND r.dt_submitted BETWEEN trunc (sysdate, 'mm') AND SYSDATE-1
) AS NO_PENDING_SUBMIT_CASES,
BR.DSCP AS BR_NM,
TRO.DSCP AS TO_NM,
USR.NM AS RM_NM,
SYSDATE-1 AS DT_RPT,
NULL AS BATCH_ID,
BR.ID AS BR_ID,
BR.CD AS BR_CD
FROM EBDCRS.T_ASSIGNMENTS ASGNMS
INNER JOIN EBDCRS.T_MCASES M_CASE ON M_CASE.ID = ASGNMS.MCASE_ID and M_CASE.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
INNER JOIN EBDCRS.T_CASES TC ON TC.MCASE_ID = ASGNMS.MCASE_ID
and TC.SYS_APF_CD = ASGNMS.SYS_APF_CD
and TC.IS_OUT_OF_BOUND = 'N'
AND TC.IS_OUT_OF_GROUP = 'N'
AND ASGNMS.IS_CURED = 'N'
INNER JOIN EBDCRS.T_HT_CIFS CUS ON CUS.SYS_APF_CD = TC.CIF_SYS_APF_CD AND CUS.MT_HOST_ID = TC.CIF_MT_HOST_ID AND CUS.CIF_NO = TC.CIF_NO
INNER JOIN EBDCRS.T_MT_POOLS POOL ON ASGNMS.CURR_MT_POOL_ID = POOL.ID
INNER JOIN EBDCRS.T_HT_ACCTS ACCT ON ACCT.ACCT_NO = TC.ACCT_NO AND ACCT.SYS_APF_CD = TC.SYS_APF_CD and ACCT.CPF_FLAG='Y'
inner join (Select BR.CD, BR.DSCP, BR.ID,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
and BR.SYS_APF_CD='SYSVN'
and LVL.CD = '20'
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N') BR ON BR.ID = ACCT.MT_BR_ID and BR.SYS_APF_CD = ACCT.SYS_APF_CD
inner join (Select BR.CD, BR.DSCP, BR.ID,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
and BR.SYS_APF_CD='SYSVN'
and LVL.CD = '10'
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N') TRO on TRO.ID = ACCT.MT_TO_ID and TRO.SYS_APF_CD = ACCT.SYS_APF_CD
LEFT JOIN EBDSMS.TBL_SEC_USER USR ON USR.ID = ASGNMS.CURR_USER_ID

INNER JOIN ebdcrs.t_mt_debt_grps dg ON acct.mt_debt_grp_id = dg.id
WHERE
TO_CHAR(ASGNMS.DT_ASSIGNED,'DD-MM-YYYY') = TO_CHAR(SYSDATE-1,'DD-MM-YYYY')AND (ACCT.PDD <=9 and ACCT.PDD >0) or ACCT.FINAL_DEBT_GRP = 2
GROUP BY POOL.CD,POOL.DSCP ,CUS.CIF_NO ,dg.cd ,CUS.NM_LINE_1,BR.DSCP ,TRO.DSCP ,USR.NM ,TO_CHAR(ASGNMS.DT_ASSIGNED,'DD-MM-YYYY'),BR.ID ,BR.CD,ASGNMS.mcase_id;