Notes

SELECT DISTINCT
POOL.CD AS POOL_CD,
POOL.DSCP AS POOL_DSCP,
A.CIF_NO,
(SELECT SUM(ACCT.PDD)
FROM EBDCRS.T_HT_ACCTS ACCT
WHERE ACCT.ID = A.ID
) AS DPD,
DEPT.CD AS DEPT_GRP,
CIF.NM_LINE_1 AS CIF_NM,
OS.OS_BALANCE AS AMT_OS_BAL,
(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
AND R.SYS_APF_CD = C.SYS_APF_CD
INNER JOIN EBDCRS.T_CL_PLAN_ACTV_ROUTING_HIST H
ON H.CL_PLAN_ACTV_ROUTING_ID = R.ID
AND R.SYS_APF_CD = H.SYS_APF_CD
WHERE R.MCASE_ID = ASGM.MCASE_ID
AND H.APPROVAL_STS = 'A'
AND TRUNC(H.DT_APPROVE) >= TRUNC(SYSDATE, 'MM')
AND TRUNC(H.DT_APPROVE) <= TRUNC(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
AND R.SYS_APF_CD = C.SYS_APF_CD
WHERE R.MCASE_ID = ASGM.MCASE_ID
AND R.APPROVAL_STS = 'P'
AND TRUNC(R.DT_SUBMITTED) >= TRUNC(SYSDATE, 'MM')
AND TRUNC(R.DT_SUBMITTED) <= TRUNC(SYSDATE - 1)
) AS NO_PENDING_APPROVE,
OS.BR_NM AS BR_NM,
OS.TO_NM AS TO_NM,
(SELECT ACCT.RM_CD
FROM EBDCRS.T_HT_ACCTS ACCT
WHERE ACCT.ID = A.ID
) AS RM_NM,
SYSDATE - 1 AS DT_RPT,
OS.BR_ID AS BR_ID,
OS.BR_CD AS BR_CD,
DG.CD AS NEW_DG,
C17.PREVIOUS_DG AS PREVIOUS_DG,
C17.DEBT_GRP AS OLD_DG,
ASGM.MCASE_ID,
A.ACCT_NO
FROM EBDCRS.T_ASSIGNMENTS ASGM
INNER JOIN EBDCRS.T_MCASES MC
ON MC.ID = ASGM.MCASE_ID
AND MC.SYS_APF_CD = ASGM.SYS_APF_CD
INNER JOIN EBDCRS.T_CASES TC
ON TC.ID = ASGM.WORST_AGING_CASE_ID
AND TC.SYS_APF_CD = ASGM.SYS_APF_CD
INNER JOIN EBDCRS.T_MT_POOLS POOL
ON ASGM.CURR_MT_POOL_ID = POOL.ID
AND ASGM.SYS_APF_CD = POOL.SYS_APF_CD
INNER JOIN EBDCRS.T_HT_ACCTS A
ON TC.ACCT_NO = A.ACCT_NO
AND A.SYS_APF_CD = TC.SYS_APF_CD
AND A.CPF_FLAG = 'Y'
INNER JOIN EBDCRS.T_HT_CIFS CIF
ON CIF.MT_HOST_ID = TC.CIF_MT_HOST_ID
AND CIF.SYS_APF_CD = TC.SYS_APF_CD
AND CIF.CIF_NO = TC.CIF_NO
INNER JOIN EBDCRS.T_MT_DEPTS DEPT
ON TC.MT_DEPT_ID = DEPT.ID
AND DEPT.SYS_APF_CD = TC.SYS_APF_CD
INNER JOIN EBDCRS.T_MT_DEBT_GRPS DG
ON A.MT_DEBT_GRP_ID = DG.ID
AND DG.SYS_APF_CD = A.SYS_APF_CD
INNER JOIN
(SELECT DISTINCT
ACCT.CIF_NO,
TRO.ID AS TO_ID,
TRO.CD AS TO_CD,
TRO.DSCP AS TO_NM,
BR.ID AS BR_ID,
BR.CD AS BR_CD,
BR.DSCP AS BR_NM,
ASG.MCASE_ID,
SUM(ACCT.OUTSTD_AMT) AS OS_BALANCE
FROM EBDCRS.T_ASSIGNMENTS ASG
INNER JOIN EBDCRS.T_CASES T_CASES
ON T_CASES.MCASE_ID = ASG.MCASE_ID
AND T_CASES.SYS_APF_CD = ASG.SYS_APF_CD
--AND T_CASES.IS_OUT_OF_BOUND = 'N'
AND T_CASES.IS_OUT_OF_GROUP = 'N'
AND T_CASES.IS_CURED = 'N'
INNER JOIN EBDCRS.T_HT_ACCTS ACCT
ON T_CASES.ACCT_NO = ACCT.ACCT_NO
AND T_CASES.SYS_APF_CD = ACCT.SYS_APF_CD
AND ACCT.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 = ACCT.MT_BR_ID
AND BR.SYS_APF_CD = ACCT.SYS_APF_CD
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 = '10' -- PH?NG GD
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
GROUP BY ACCT.CIF_NO, TRO.ID, TRO.CD, TRO.DSCP, ASG.MCASE_ID, BR.ID, BR.CD, BR.DSCP
) OS
ON OS.CIF_NO = CIF.CIF_NO
AND OS.MCASE_ID = MC.ID
INNER JOIN EBREPORT.T_RPT_C17VN_DAILY_STG C17
ON C17.CIF_NO = A.CIF_NO
AND C17.ACCT_NO = A.ACCT_NO
WHERE DG.CD IN ('3','4','5')
AND A.SYS_APF_CD = SYS_APF_CD_IN
AND DEPT.CD = 'CORP'
--AND TO_CHAR(ASGM.DT_ASSIGNED,'DD-MM-YYYY') = TO_CHAR(SYSDATE - 1,'DD-MM-YYYY')
AND EXISTS (SELECT 1
FROM EBREPORT.T_RPT_C17VN_DAILY_STG S
WHERE S.CIF_NO = A.CIF_NO
AND S.ACCT_NO = A.ACCT_NO
AND S.DEBT_GRP <> DG.CD
AND (S.DEBT_GRP = '2'
OR S.PREVIOUS_DG = '2')
);