Notes

SELECT
POOL.CD AS POOL_CD,
POOL.DSCP AS POOL_DSCP,
A.CIF_NO AS CIF_NO,
(SELECT SUM(ACCT.PDD)
FROM EBDCRS.T_HT_ACCTS ACCT
WHERE ACCT.ID = A.ID
) AS DPD,
DG.CD AS DG,
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
INNER JOIN EBDCRS.T_CL_PLAN_ACTV_ROUTING_HIST H
ON H.CL_PLAN_ACTV_ROUTING_ID = R.ID
WHERE R.MCASE_ID = ASGM.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
WHERE R.MCASE_ID = ASGM.MCASE_ID
AND R.APPROVAL_STS = 'P'
AND R.DT_SUBMITTED BETWEEN TRUNC(SYSDATE, 'MM')
AND 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,
C17.CURRENT_DG,
C17.PREVIOUS_DG,
ASGM.MCASE_ID,
(SELECT ACCT.ACCT_NO
FROM EBDCRS.T_HT_ACCTS ACCT
INNER JOIN EBDCRS.T_CASES
ON T_CASES.ACCT_NO = ACCT.ACCT_NO
INNER JOIN EBDCRS.T_ASSIGNMENTS ASSIGN
ON T_CASES.ID = ASSIGN.WORST_AGING_CASE_ID
WHERE ACCT.CIF_NO = A.CIF_NO
AND T_CASES.MCASE_ID = ASGM.MCASE_ID
) AS ACCT_NO,
DEPT.CD AS DEPT
FROM EBDCRS.T_ASSIGNMENTS ASGM
INNER JOIN EBDCRS.T_MCASES MC
ON MC.ID = ASGM.MCASE_ID
INNER JOIN EBDCRS.T_CASES TC
ON TC.ID = ASGM.WORST_AGING_CASE_ID
AND TC.IS_OUT_OF_BOUND = 'N'
AND TC.IS_OUT_OF_GROUP = 'N'
AND TC.IS_CURED = 'N'
INNER JOIN EBDCRS.T_MT_POOLS POOL
ON ASGM.CURR_MT_POOL_ID = POOL.ID
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.SYS_APF_CD = TC.CIF_SYS_APF_CD
AND CIF.MT_HOST_ID = TC.CIF_MT_HOST_ID
AND CIF.CIF_NO = TC.CIF_NO
INNER JOIN EBDCRS.T_MT_DEPTS DEPT
ON TC.MT_DEPT_ID = DEPT.ID
INNER JOIN EBDCRS.T_MT_DEBT_GRPS DG
ON A.MT_DEBT_GRP_ID = DG.ID
INNER JOIN
(SELECT
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,
T_CASES.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.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 ACCT.CPF_FLAG = 'Y'
INNER JOIN
(SELECT
BR.ID,
BR.CD,
BR.DSCP
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
INNER JOIN
(SELECT
BR.ID,
BR.CD,
BR.DSCP
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' -- PḤNG GD
AND BR.IS_DEL = 'N'
AND BR.IS_DEACTIVATE = 'N'
) TRO
ON TRO.ID = ACCT.MT_TO_ID
GROUP BY ACCT.CIF_NO, TRO.ID, TRO.CD, TRO.DSCP, T_CASES.MCASE_ID, BR.ID, BR.CD, BR.DSCP
) OS
ON OS.CIF_NO = CIF.CIF_NO
AND OS.MCASE_ID = TC.MCASE_ID
LEFT 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 = '2'
AND A.SYS_APF_CD = 'SYSVN'
AND DEPT.CD = 'CORP'