Notes

select distinct
BR.ID BR_ID,
BR.CD BR_CD,
BR.DSCP BR_NAME,
CIF.CIF_NO CIF_NO,
CIF.NM_LINE_1 CIF_NAME,
POOL.DSCP as POOL_NAME,
( select TO_NUMBER(ACTV1.F01_VAL) from EBDCRS.T_LGL_ACTVS ACTV1
INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.ID = ACTV1.MT_ACTV_ID and MA1.CD IN ('KHTH10_020','KHTH20_020')
where ACTV1.LGL_ID = LGL.ID ) as PLANNED_COLLECTED_AMOUNT,

(select TO_NUMBER(ACTV1.F02_VAL) from EBDCRS.T_LGL_ACTVS ACTV1
INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.ID = ACTV1.MT_ACTV_ID and MA1.CD IN ('KHTH10_020','KHTH20_020')
where ACTV1.LGL_ID = LGL.ID) as ACTUAL_COLLECTED_AMOUNT,

(select TO_DATE(ACTV1.F01_VAL,'DD/MM/YYYY') from EBDCRS.T_LGL_ACTVS ACTV1
INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.ID = ACTV1.MT_ACTV_ID and MA1.CD IN ('KHTH10_040','KHTH20_040')
where ACTV1.LGL_ID = LGL.ID ) as PLANNED_COLLECTED_DATE,

(select TO_DATE(ACTV1.F02_VAL,'DD/MM/YYYY') from EBDCRS.T_LGL_ACTVS ACTV1
INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.ID = ACTV1.MT_ACTV_ID and MA1.CD IN ('KHTH10_040','KHTH20_040')
where ACTV1.LGL_ID = LGL.ID ) as ACTUAL_COLLECTED_DATE,

(select (ACTV1.F01_VAL) from EBDCRS.T_LGL_ACTVS ACTV1
INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.ID = ACTV1.MT_ACTV_ID and MA1.CD IN ('KHTH10_030','KHTH20_030')
where ACTV1.LGL_ID = LGL.ID ) as COLLECTED_SOURCE,

(select TO_DATE(ACTV1.F01_VAL,'DD/MM/YYYY') from EBDCRS.T_LGL_ACTVS ACTV1
INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.ID = ACTV1.MT_ACTV_ID and MA1.CD IN ('KHTH10_010','KHTH20_010')
where ACTV1.LGL_ID = LGL.ID ) as PLAN_DT,

( SELECT Listagg( ACTV1.REMARKS, ',') WITHIN GROUP (ORDER BY ACTV1.REMARKS ) AS REMARK
FROM EBDCRS.T_LGL_ACTVS ACTV1
INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.ID = ACTV1.MT_ACTV_ID
where ACTV1.LGL_ID = LGL.ID ) AS REMARK,

LGL.DT_CREATE AS DT_CREATE,

STGS.CD as MT_STG_CD,
STGS.ID MT_STG_ID,
SYSDATE-1 DT_RPT,
' ' AS BATCH_ID
from
EBDCRS. T_ASSIGNMENTS TA
INNER JOIN EBDCRS.T_MCASES MCASE ON MCASE.ID = TA.MCASE_ID
INNER JOIN EBDCRS.T_CASES TC ON TC.MCASE_ID = 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_MT_DEPTS DEPTS ON DEPTS.ID= TC.MT_DEPT_ID AND DEPTS.CD = 'RECOVERY'
INNER JOIN EBDCRS.T_HT_CIFS CIF ON CIF.CIF_NO = TC.CIF_NO
INNER JOIN EBDCRS.T_MT_POOLS POOL ON POOL.ID = TA.CURR_MT_POOL_ID
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 = TC.MT_CENTRE_ID
INNER JOIN EBDCRS.T_LGLS LGL ON LGL.MCASE_ID = MCASE.ID --and lgl.dt_create = (select max(LGL1.DT_CREATE) from EBDCRS.T_LGLS LGL1 where lgl1.MCASE_ID = lgl.MCASE_ID )
INNER JOIN EBDCRS.T_MT_STGS STGS ON STGS.ID = LGL.MT_STG_ID AND STGS.CD IN ('KHTH10','KHTH20')
INNER JOIN ebdcrs.T_LGL_ACTVS ACTV ON ACTV.LGL_ID = LGL.ID
INNER JOIN EBDCRS.T_MT_ACTVS MA ON MA.ID = ACTV.MT_ACTV_ID AND MA.CD IN ('KHTH10_020','KHTH10_040','KHTH10_030','KHTH10_010','KHTH20_020','KHTH20_040','KHTH20_030','KHTH10_020')
INNER JOIN EBDCRS.T_STG_PLAN_ACTV_ROUTING TSPAR ON TSPAR.LGL_ID = LGL.ID AND TSPAR.APPROVAL_STS = 'A'
WHERE trunc(TSPAR.dt_approve) = trunc(sysdate-1) ;