Notes

select
b.RM_CD COLLECTOR_ID,--lay ten RM
a1.CIF_NO HT_CIF_NO,
a1.NM_LINE_1 HT_NM_LN_1,
nvl(a1.AMT_DUE_CURR,0) HT_ACCT_BILL_AMT,
a1.HT_ACCT_DT_PAST_DUE,
a1.COLLECTOR_NM,
a1.LAST_REACT_CD ,
a1.ACT_REACT_REMARKS ,
max(a2.LATEST_DT_END) KEEP (DENSE_RANK LAST ORDER BY a2.LATEST_DT_END desc) LATEST_DT_END,
SUM(nvl(a2.PTP_AMOUNT,0)) NUM_PTP_AMT,
a1.MT_BR_NM,
a1.MT_BR_CD,
a1.user_id RM_CD,
SYSDATE-1,
a1.CD
from

( select distinct
CUS.CIF_NO,CUS.NM_LINE_1 ,BR.CD MT_BR_CD,
sum(distinct ACCT.AMT_DUE_CURR) AMT_DUE_CURR,
max(ACCT.DT_PAST_DUE) KEEP (DENSE_RANK LAST ORDER BY ACCT.DT_PAST_DUE desc) HT_ACCT_DT_PAST_DUE,--ngay qu? h?n g?n nh?
MAX(REACT.DSCP) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) LAST_REACT_CD ,---l?y react g?n nh?t
MAX(REACT.CD) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) CD ,
MAX(ARA.REMARKS) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE) ACT_REACT_REMARKS ,
MAX(Sec.id) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) user_id,
MAX(SEC.NM) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) COLLECTOR_NM,
BR.DSCP MT_BR_NM
from
EBDCRS.T_ASSIGNMENTS SGM
INNER JOIN EBDCRS.T_MCASES M_CASE ON M_CASE.ID = SGM.MCASE_ID
INNER JOIN EBDCRS.T_CASES TC ON SGM.MCASE_ID = TC.MCASE_ID AND TC.is_cured = 'N' and tc.is_out_of_bound ='N' and tc.is_out_of_group = 'N'
inner join EBDCRS.T_HT_ACCTS ACCT on ACCT.ACCT_NO = TC.ACCT_NO and ACCT.SYS_APF_CD = 'SYSVN' and ACCT.CPF_FLAG='Y'
inner join EBDCRS.T_HT_CIFS CUS on CUS.CIF_NO = ACCT.CIF_NO and CUS.SYS_APF_CD = ACCT.SYS_APF_CD
INNER JOIN EBDCRS.T_ACT_REACT ARA ON ARA.TCASE_ID = TC.ID
inner join EBDCRS.t_mt_depts depts on TC.MT_DEPT_ID = depts.ID
inner join EBDCRS.T_MT_TEAMS TEAM on TEAM.MT_DEPT_ID = depts.ID and TEAM.CD like 'CC_%'
INNER JOIN ebdcrs.T_TEAM_MEMBER_MEMBERTYP_RELS RELS on TEAM.ID = RELS.MT_TEAM_ID and ARA.create_by= RELS.SEC_USER_ID
INNER JOIN EBDCRS.T_MT_REACTS REACT ON REACT.ID = ARA.MT_REACT_ID and ARA.SYS_APF_CD = REACT.SYS_APF_CD
inner join EBDSMS.TBL_SEC_USER SEC on ARA.CREATE_BY = SEC.ID
inner join EBDCRS.T_MT_BRANCHES BR on BR.ID = ACCT.MT_BR_ID and BR.SYS_APF_CD = ACCT.SYS_APF_CD
where trunc(ARA.DT_CREATE) = trunc(SYSDATE-3) and REACT.CD in ('WN','UC','PTP','NPTP','RTP','CP','MC','AD')
group by CUS.CIF_NO,CUS.NM_LINE_1 ,BR.CD,BR.DSCP) a1

left JOIN
(select distinct MAX(PTPD.DT_END) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) LATEST_DT_END,--- l?y ng?y g?n nh?t
MAX(REACT.DSCP) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) LAST_REACT_CD,
MAX(PTPD.PTP_AMOUNT) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) PTP_AMOUNT,
MAX(SEC.NM) KEEP (DENSE_RANK LAST ORDER BY ARA.DT_CREATE ) COLLECTOR_NM,
CUS.CIF_NO, BR.CD MT_BR_C,ACCT.ACCT_NO---t?ng s? ti?n
from EBDCRS.T_ASSIGNMENTS SGM
INNER JOIN EBDCRS.T_CASES TC ON SGM.MCASE_ID = TC.MCASE_ID AND TC.is_cured = 'N' and tc.is_out_of_bound ='N' and tc.is_out_of_group = 'N'
inner join EBDCRS.T_HT_ACCTS ACCT on ACCT.ACCT_NO = TC.ACCT_NO and ACCT.SYS_APF_CD = 'SYSVN' and ACCT.CPF_FLAG='Y'
inner join EBDCRS.T_HT_CIFS CUS on CUS.CIF_NO = ACCT.CIF_NO and CUS.SYS_APF_CD = ACCT.SYS_APF_CD
INNER JOIN EBDCRS.T_ACT_REACT ARA ON ARA.TCASE_ID = TC.ID
inner join EBDCRS.t_mt_depts depts on TC.MT_DEPT_ID = depts.ID
inner join EBDCRS.T_MT_TEAMS TEAM on TEAM.MT_DEPT_ID = depts.ID and TEAM.CD like 'CC_%'
INNER JOIN ebdcrs.T_TEAM_MEMBER_MEMBERTYP_RELS RELS on TEAM.ID = RELS.MT_TEAM_ID and ARA.create_by= RELS.SEC_USER_ID
inner join EBDCRS.T_MT_REACTS REACT ON REACT.ID = ARA.MT_REACT_ID and ARA.SYS_APF_CD = REACT.SYS_APF_CD
left JOIN EBDCRS.T_PTP_DETAILS PTPD ON PTPD.ACT_REACT_ID = ARA.ID
inner join EBDSMS.TBL_SEC_USER SEC on ARA.CREATE_BY = SEC.ID
inner join EBDCRS.T_MT_BRANCHES BR on BR.ID = ACCT.MT_BR_ID and BR.SYS_APF_CD = ACCT.SYS_APF_CD
where trunc(ARA.DT_CREATE) = trunc(SYSDATE-3)
group by CUS.CIF_NO,SEC.NM,BR.CD,ACCT.ACCT_NO) a2 on a1.CIF_NO=a2.CIF_NO and a1.LAST_REACT_CD=a2.LAST_REACT_CD
and a1.COLLECTOR_NM=a2.COLLECTOR_NM
inner join ( SELECT distinct A.RM_CD, A.CIF_NO FROM
EBDCRS.T_HT_ACCTS A
INNER JOIN EBDCRS.T_CASES
ON T_CASES.ACCT_NO = A.ACCT_NO
INNER JOIN EBDCRS.T_ASSIGNMENTS ass ON T_CASES.ID = ass.WORST_AGING_CASE_ID
AND T_CASES.IS_OUT_OF_BOUND = 'N'
AND T_CASES.IS_OUT_OF_GROUP = 'N'
AND T_CASES.IS_CURED = 'N'
AND A.CPF_FLAG = 'Y'
and T_CASES.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A2'
)b on b.CIF_NO= a1.CIF_NO
GROUP BY
b.RM_CD , a1.CIF_NO , a1.NM_LINE_1 , nvl(a1.AMT_DUE_CURR,0), a1.HT_ACCT_DT_PAST_DUE, a1.COLLECTOR_NM, a1.LAST_REACT_CD,a1.ACT_REACT_REMARKS ,a1.MT_BR_NM, a1.MT_BR_CD,a1.user_id ,SYSDATE-1,a1.CD;