Notes

Select USR.NM as COLL_NM,
COLL_ID,
NO_CUST_ASGN,
AMT_CUST_ASGN,
NO_ATTEMP,
NO_CUST_WORK,
NO_OF_CALL,
NO_OF_VISIT,
NO_OF_OTHER,
NO_OF_CONTACT,
Round((Case when (nvl(NO_OF_CALL,0)+nvl(NO_OF_VISIT,0)+nvl(NO_OF_OTHER,0))<>0 then nvl(NO_OF_CALL,0)/(nvl(NO_OF_CALL,0)+nvl(NO_OF_VISIT,0)+nvl(NO_OF_OTHER,0)) Else 0 End),2) as CONTACT_RATIO,
NO_PTP,
AMT_PTP,
NO_PTP_PK,
AMT_PTP_PK,
NO_PTP_BP,
AMT_PTP_BP,
Round((Case when nvl(NO_PTP,0)<>0 then nvl(NO_PTP_PK,0)/nvl(NO_PTP,0)*100 Else 0 End),2) PC_PTP_RATIO,
Round((Case when nvl(NO_PTP,0)<>0 then nvl(NO_PTP_BP,0)/nvl(NO_PTP,0)*100 Else 0 End),2) PC_BP_RATIO,
AMT_TOT_PYMNT_COLL,
BR_NM,
TO_NM,
TO_DATE(DT_RPT,'DD-MM-YYYY') as DT_RPT,
BR_CD,
BR_ID,
TO_CD
From (
Select COLL_ID,
Sum(NO_CUST_ASGN) as NO_CUST_ASGN,
Sum(AMT_CUST_ASGN) as AMT_CUST_ASGN,
Sum(NO_ATTEMP) as NO_ATTEMP,
Sum(NO_CUST_WORK) as NO_CUST_WORK,
Sum(NO_OF_CALL) as NO_OF_CALL,
Sum(NO_OF_VISIT) as NO_OF_VISIT,
Sum(NO_OF_OTHER) as NO_OF_OTHER,
Sum(NO_OF_CONTACT) as NO_OF_CONTACT,
Sum(NO_PTP) as NO_PTP,
Sum(AMT_PTP) as AMT_PTP,
Sum(NO_PTP_PK) as NO_PTP_PK,
Sum(AMT_PTP_PK) as AMT_PTP_PK,
Sum(NO_PTP_BP) as NO_PTP_BP,
Sum(AMT_PTP_BP) as AMT_PTP_BP,
Sum(AMT_TOT_PYMNT_COLL) as AMT_TOT_PYMNT_COLL,
BR_NM,
TO_NM,
DT_RPT,
BR_CD,
BR_ID,
TO_CD
From (
--TBL
Select a1.COLL_ID,
Count(distinct a1.MCASE_ID) as NO_CUST_ASGN,
Sum(nvl(TC1.AMT_CUST_ASGN,0)) as AMT_CUST_ASGN,
Sum(nvl(ARA.NO_ATTEMP,0)) as NO_ATTEMP,
Sum((Case when ARA.NO_ATTEMP <> 0 then 1 Else 0 End)) as NO_CUST_WORK,
Sum((Case when ARA.NO_OF_CALL <> 0 then 1 Else 0 End)) as NO_OF_CALL,
Sum((Case when ARA.NO_OF_VISIT <> 0 then 1 Else 0 End)) as NO_OF_VISIT,
--Sum(Case when (SMS1=1 and EMAIL1=1) then 1 Else ARA.NO_OF_OTHER End) as NO_OF_OTHER,
Sum(nvl(ARA.NO_OF_OTHER,0)) as NO_OF_OTHER,
Sum(nvl(ARA.NO_OF_CONTACT,0)) as NO_OF_CONTACT,
--Case when (Sum(ARA.NO_OF_CALL)+Sum(ARA.NO_OF_VISIT)+Sum(ARA.NO_OF_OTHER_12))<>0 then nvl(Sum(ARA.NO_OF_CONTACT),0)/(Sum(ARA.NO_OF_CALL)+Sum(ARA.NO_OF_VISIT)+Sum(ARA.NO_OF_OTHER_12)) Else 0 End as CONTACT_RATIO,
Sum(nvl(ARA1.NO_PTP,0)) as NO_PTP,
Sum(nvl(ARA1.AMT_PTP,0)) as AMT_PTP,
0 NO_PTP_PK,
0 AMT_PTP_PK,
0 NO_PTP_BP,
0 AMT_PTP_BP,
--Case when Sum(ARA.NO_PTP)<>0 then nvl(Sum(ARA.NO_PTP_PK),0)/Sum(ARA.NO_PTP)*100 Else 0 End PC_PTP_RATIO,
--Case when Sum(ARA.NO_PTP)<>0 then nvl(Sum(ARA.NO_PTP_BP),0)/Sum(ARA.NO_PTP)*100 Else 0 End PC_BP_RATIO,
0 AMT_TOT_PYMNT_COLL,
BR.DSCP as BR_NM,
TRO.DSCP as TO_NM,
TRO.CD as TO_CD,
TO_CHAR(a1.DT_RPT,'DD-MM-YYYY') as DT_RPT,
BR.CD as BR_CD,
BR.ID as BR_ID
From
(Select MCASE_ID, CURR_USER_ID as COLL_ID , WORST_AGING_CASE_ID, SYS_APF_CD, DT_ASSIGNED as DT_RPT
From EBDCRS.T_ASSIGNMENTS
Where TO_CHAR(DT_ASSIGNED,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
and SYS_APF_CD = 'SYSVN'
and CURR_USER_ID is not null
--and CURR_USER_ID = 'corpcol3'
) a1
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1' --'29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and a1.MCASE_ID = TC.MCASE_ID
--and a1.WORST_AGING_CASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Left join
(Select MCASE_ID, TC.ID, Sum(nvl(ACCT.AMT_DUE_CURR,0)) as AMT_CUST_ASGN
From EBDCRS.T_CASES TC
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Where TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
Group by MCASE_ID, TC.ID
)TC1
On a1.MCASE_ID = TC1.MCASE_ID
and TC.ID = TC1.ID
Left 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?
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) BR
On BR.ID = ACCT.MT_BR_ID
Left 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' -- Ph?GD
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) TRO
On TRO.ID = ACCT.MT_TO_ID
Left join
(Select ARA.*, ARA1.TCASE_ID
From (
Select ARA1.MCASE_ID, ARA1.CREATE_BY, ARA1.MT_BR_ID, ARA1.MT_TO_ID,
Count(Case when (ACTYP_CD is not null and REACTYP_CD is not null) then 1 Else 0 End) as NO_ATTEMP,
Max(Case when (ACTYP_CD = 'CALL' and REACTYP_CD = 'PR') then 1 Else 0 End) as NO_OF_CALL,
Max(Case when ACTYP_CD = 'FIELD VISIT' then 1 Else 0 End) as NO_OF_VISIT,
Max(Case when ACTYP_CD not in ('CALL','FIELD VISIT') then 1 Else 0 End) as NO_OF_OTHER,
Sum(Case when (ACT_CD is not null and REACT_CD is not null) then 1 Else 0 End) as NO_OF_CONTACT
From (
Select distinct ARA.MCASE_ID, ARA.CREATE_BY, ACCT.MT_BR_ID, ACCT.MT_TO_ID,
ACT.CD as ACT_CD, ACTYP.CD as ACTYP_CD, REACT.CD as REACT_CD, REACTYP.CD as REACTYP_CD
From EBDCRS.T_ACT_REACT ARA
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACTS ACT
On ARA.MT_ACT_ID = ACT.ID
and ARA.SYS_APF_CD = ACT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACT_TYPS ACTYP
On ACT.MT_ACT_TYP_ID = ACTYP.ID
and ACT.SYS_APF_CD = ACTYP.SYS_APF_CD
Inner join EBDCRS.T_MT_REACTS REACT
On ARA.MT_REACT_ID = REACT.ID
and ARA.SYS_APF_CD = REACT.SYS_APF_CD
Inner join EBDCRS.T_MT_REACT_TYPS REACTYP
On REACT.MT_REACT_TYP_ID = REACTYP.ID
and REACT.SYS_APF_CD = REACTYP.SYS_APF_CD
Where TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
--and ARA.MCASE_ID = '90AD76BCA62E5F90E053BF1B1FAC31AC'
and ARA.SYS_APF_CD = 'SYSVN'
)ARA1
Group by ARA1.MCASE_ID, ARA1.CREATE_BY, ARA1.MT_BR_ID, ARA1.MT_TO_ID
)ARA
Inner join
(Select ARA.MCASE_ID, ARA.CREATE_BY, Max(ARA.TCASE_ID) as TCASE_ID
From EBDCRS.T_ACT_REACT ARA
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Where TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
Group by ARA.MCASE_ID, ARA.CREATE_BY
)ARA1
On ARA.MCASE_ID = ARA1.MCASE_ID
and ARA.CREATE_BY = ARA1.CREATE_BY
) ARA
On a1.MCASE_ID = ARA.MCASE_ID
and TC.ID = ARA.TCASE_ID
and a1.COLL_ID = ARA.CREATE_BY
Left join
(Select ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY,
Max(Case when REACTYP.CD = 'PTP' then 1 Else 0 End) as NO_PTP,
Sum(Case when REACTYP.CD = 'PTP' and PTPD.VERSION = 0 then PTPD.PTP_AMOUNT Else 0 End) as AMT_PTP
From EBDCRS.T_ACT_REACT ARA
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACTS ACT
On ARA.MT_ACT_ID = ACT.ID
and ARA.SYS_APF_CD = ACT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACT_TYPS ACTYP
On ACT.MT_ACT_TYP_ID = ACTYP.ID
and ACT.SYS_APF_CD = ACTYP.SYS_APF_CD
Inner join EBDCRS.T_MT_REACTS REACT
On ARA.MT_REACT_ID = REACT.ID
and ARA.SYS_APF_CD = REACT.SYS_APF_CD
Inner join EBDCRS.T_MT_REACT_TYPS REACTYP
On REACT.MT_REACT_TYP_ID = REACTYP.ID
and REACT.SYS_APF_CD = REACTYP.SYS_APF_CD
Left join EBDCRS.T_PTP_DETAILS PTPD
On ARA.ID = PTPD.ACT_REACT_ID
and ARA.SYS_APF_CD = PTPD.SYS_APF_CD
Left join EBDCRS.T_MT_PTP_STATUS PTPS
On PTPD.MT_PTP_STS_ID = PTPS.ID
Where TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
and REACTYP.CD = 'PTP'
--and ARA.MCASE_ID = '90AD76BCA62E5F90E053BF1B1FAC31AC'
and ARA.SYS_APF_CD = 'SYSVN'
Group by ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY
) ARA1
On a1.MCASE_ID = ARA1.MCASE_ID
and TC.ID = ARA1.TCASE_ID
and a1.COLL_ID = ARA1.CREATE_BY
Group by a1.COLL_ID,TO_CHAR(a1.DT_RPT,'DD-MM-YYYY'),
BR.ID, BR.CD, BR.DSCP, TRO.CD, TRO.DSCP

Union ---payment
Select ARA.CREATE_BY as COLL_ID, --TC.ACCT_NO,
0 as NO_CUST_ASGN,
0 as AMT_CUST_ASGN,
0 NO_ATTEMP,
0 NO_CUST_WORK,
0 NO_OF_CALL,
0 NO_OF_VISIT,
0 NO_OF_OTHER,
0 NO_OF_CONTACT,
0 as NO_PTP,
0 as AMT_PTP,
0 as NO_PTP_PK,
0 as AMT_PTP_PK,
0 as NO_PTP_BP,
0 as AMT_PTP_BP,
Sum(nvl(PAYMENT.PYMT_AMT,0)) as AMT_TOT_PYMNT_COLL,
BR.DSCP as BR_NM,
TRO.DSCP as TO_NM,
TRO.CD as TO_CD,
TO_CHAR(SYSDATE-4,'DD-MM-YYYY') as DT_RPT,
BR.CD as BR_CD,
BR.ID as BR_ID
from
(Select HT_ACCT_NO,MT_HOST_ID, Sum(nvl(PYMT_AMT,0)) as PYMT_AMT
From EBDCRS.T_HT_ACCT_REPYMTS REP
Where TO_CHAR(DT_REPYMT,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
Group by HT_ACCT_NO,MT_HOST_ID) PAYMENT
inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
AND ACCT.ACCT_NO = PAYMENT.HT_ACCT_NO
and ACCT.MT_HOST_ID = PAYMENT.MT_HOST_ID
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
AND TC.ACCT_NO=ACCT.ACCT_NO
Inner join EBDCRS.T_ACT_REACT ARA
ON ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
INNER JOIN EBDCRS.T_PTP_DETAILS PTPD ON PTPD.ACT_REACT_ID = ARA.ID
Left 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?
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) BR
On BR.ID = ACCT.MT_BR_ID
Left 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' -- Ph?GD
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) TRO
On TRO.ID = ACCT.MT_TO_ID
GROUP BY ARA.CREATE_BY,TO_CHAR(SYSDATE-4,'DD-MM-YYYY'),
BR.ID, BR.CD, BR.DSCP, TRO.CD, TRO.DSCP


Union --TBL2
Select ARA.CREATE_BY as COLL_ID, --TC.ACCT_NO,
0 as NO_CUST_ASGN,
0 as AMT_CUST_ASGN,
Sum(nvl(ARA.NO_ATTEMP,0)) as NO_ATTEMP,
Sum((Case when ARA.NO_ATTEMP <> 0 then 1 Else 0 End)) as NO_CUST_WORK,
Sum((Case when ARA.NO_OF_CALL <> 0 then 1 Else 0 End)) as NO_OF_CALL,
Sum((Case when ARA.NO_OF_VISIT <> 0 then 1 Else 0 End)) as NO_OF_VISIT,
Sum(nvl(ARA.NO_OF_OTHER,0)) as NO_OF_OTHER,
Sum(nvl(ARA.NO_OF_CONTACT,0)) as NO_OF_CONTACT,
0 as NO_PTP,
0 as AMT_PTP,
0 as NO_PTP_PK,
0 as AMT_PTP_PK,
0 as NO_PTP_BP,
0 as AMT_PTP_BP,
0 as AMT_TOT_PYMNT_COLL,
BR.DSCP as BR_NM,
TRO.DSCP as TO_NM,
TRO.CD as TO_CD,
TO_CHAR(a1.DT_RPT,'DD-MM-YYYY') as DT_RPT,
BR.CD as BR_CD,
BR.ID as BR_ID
From
(Select MCASE_ID, CURR_USER_ID as COLL_ID , WORST_AGING_CASE_ID, SYS_APF_CD, DT_ASSIGNED as DT_RPT
From EBDCRS.T_ASSIGNMENTS
Where TO_CHAR(DT_ASSIGNED,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
and SYS_APF_CD = 'SYSVN'
and CURR_USER_ID is not null
--and CURR_USER_ID = 'corpcol3'
) a1
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and a1.MCASE_ID = TC.MCASE_ID
--and a1.WORST_AGING_CASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Left 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?
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) BR
On BR.ID = ACCT.MT_BR_ID
Left 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' -- Ph?GD
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) TRO
On TRO.ID = ACCT.MT_TO_ID
Inner join
(Select ARA.*, ARA1.TCASE_ID
From (
Select ARA1.MCASE_ID, ARA1.CREATE_BY, ARA1.MT_BR_ID, ARA1.MT_TO_ID,
Count(Case when (ACTYP_CD is not null and REACTYP_CD is not null) then 1 Else 0 End) as NO_ATTEMP,
Max(Case when (ACTYP_CD = 'CALL' and REACTYP_CD = 'PR') then 1 Else 0 End) as NO_OF_CALL,
Max(Case when ACTYP_CD = 'FIELD VISIT' then 1 Else 0 End) as NO_OF_VISIT,
Max(Case when ACTYP_CD not in ('CALL','FIELD VISIT') then 1 Else 0 End) as NO_OF_OTHER,
Sum(Case when (ACT_CD is not null and REACT_CD is not null) then 1 Else 0 End) as NO_OF_CONTACT
From (
Select distinct ARA.MCASE_ID, ARA.CREATE_BY, ACCT.MT_BR_ID, ACCT.MT_TO_ID,
ACT.CD as ACT_CD, ACTYP.CD as ACTYP_CD, REACT.CD as REACT_CD, REACTYP.CD as REACTYP_CD
From EBDCRS.T_ACT_REACT ARA
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACTS ACT
On ARA.MT_ACT_ID = ACT.ID
and ARA.SYS_APF_CD = ACT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACT_TYPS ACTYP
On ACT.MT_ACT_TYP_ID = ACTYP.ID
and ACT.SYS_APF_CD = ACTYP.SYS_APF_CD
Inner join EBDCRS.T_MT_REACTS REACT
On ARA.MT_REACT_ID = REACT.ID
and ARA.SYS_APF_CD = REACT.SYS_APF_CD
Inner join EBDCRS.T_MT_REACT_TYPS REACTYP
On REACT.MT_REACT_TYP_ID = REACTYP.ID
and REACT.SYS_APF_CD = REACTYP.SYS_APF_CD
Where TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
--and ARA.MCASE_ID = '90AD76BCA62E5F90E053BF1B1FAC31AC'
and ARA.SYS_APF_CD = 'SYSVN'
)ARA1
Group by ARA1.MCASE_ID, ARA1.CREATE_BY, ARA1.MT_BR_ID, ARA1.MT_TO_ID
)ARA
Inner join
(Select ARA.MCASE_ID, ARA.CREATE_BY, Max(ARA.TCASE_ID) as TCASE_ID
From EBDCRS.T_ACT_REACT ARA
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Where TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
Group by ARA.MCASE_ID, ARA.CREATE_BY
)ARA1
On ARA.MCASE_ID = ARA1.MCASE_ID
and ARA.CREATE_BY = ARA1.CREATE_BY
) ARA
On a1.MCASE_ID = ARA.MCASE_ID
and TC.ID = ARA.TCASE_ID
and a1.COLL_ID <> ARA.CREATE_BY
Group by ARA.CREATE_BY,TO_CHAR(a1.DT_RPT,'DD-MM-YYYY'),
BR.ID, BR.CD, BR.DSCP, TRO.CD, TRO.DSCP
Union --TBL3
Select ARA1.CREATE_BY as COLL_ID, --TC.ACCT_NO,
0 as NO_CUST_ASGN,
0 as AMT_CUST_ASGN,
0 as NO_ATTEMP,
0 as NO_CUST_WORK,
0 as NO_OF_CALL,
0 as NO_OF_VISIT,
0 as NO_OF_OTHER,
0 as NO_OF_CONTACT,
Sum(nvl(ARA1.NO_PTP,0)) as NO_PTP,
Sum(nvl(ARA1.AMT_PTP,0)) as AMT_PTP,
0 as NO_PTP_PK,
0 as AMT_PTP_PK,
0 as NO_PTP_BP,
0 as AMT_PTP_BP,
0 as AMT_TOT_PYMNT_COLL,
BR.DSCP as BR_NM,
TRO.DSCP as TO_NM,
TRO.CD as TO_CD,
TO_CHAR(a1.DT_RPT,'DD-MM-YYYY') as DT_RPT,
BR.CD as BR_CD,
BR.ID as BR_ID
From
(Select MCASE_ID, CURR_USER_ID as COLL_ID , WORST_AGING_CASE_ID, SYS_APF_CD, DT_ASSIGNED as DT_RPT
From EBDCRS.T_ASSIGNMENTS
Where TO_CHAR(DT_ASSIGNED,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
and SYS_APF_CD = 'SYSVN'
and CURR_USER_ID is not null
--and CURR_USER_ID = 'corpcol3'
) a1
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and a1.MCASE_ID = TC.MCASE_ID
--and a1.WORST_AGING_CASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Left 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?
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) BR
On BR.ID = ACCT.MT_BR_ID
Left 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' -- Ph?GD
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) TRO
On TRO.ID = ACCT.MT_TO_ID
Inner join
(Select ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY,
Max(Case when REACTYP.CD = 'PTP' then 1 Else 0 End) as NO_PTP,
Sum(Case when REACTYP.CD = 'PTP' and PTPD.VERSION = 0 then PTPD.PTP_AMOUNT Else 0 End) as AMT_PTP
From EBDCRS.T_ACT_REACT ARA
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACTS ACT
On ARA.MT_ACT_ID = ACT.ID
and ARA.SYS_APF_CD = ACT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACT_TYPS ACTYP
On ACT.MT_ACT_TYP_ID = ACTYP.ID
and ACT.SYS_APF_CD = ACTYP.SYS_APF_CD
Inner join EBDCRS.T_MT_REACTS REACT
On ARA.MT_REACT_ID = REACT.ID
and ARA.SYS_APF_CD = REACT.SYS_APF_CD
Inner join EBDCRS.T_MT_REACT_TYPS REACTYP
On REACT.MT_REACT_TYP_ID = REACTYP.ID
and REACT.SYS_APF_CD = REACTYP.SYS_APF_CD
Left join EBDCRS.T_PTP_DETAILS PTPD
On ARA.ID = PTPD.ACT_REACT_ID
and ARA.SYS_APF_CD = PTPD.SYS_APF_CD
Left join EBDCRS.T_MT_PTP_STATUS PTPS
On PTPD.MT_PTP_STS_ID = PTPS.ID
Where TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
and REACTYP.CD = 'PTP'
--and ARA.MCASE_ID = '90AD76BCA62E5F90E053BF1B1FAC31AC'
and ARA.SYS_APF_CD = 'SYSVN'
Group by ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY
) ARA1
On a1.MCASE_ID = ARA1.MCASE_ID
and TC.ID = ARA1.TCASE_ID
and a1.COLL_ID <> ARA1.CREATE_BY
Group by ARA1.CREATE_BY,TO_CHAR(a1.DT_RPT,'DD-MM-YYYY'),
BR.ID, BR.CD, BR.DSCP, TRO.CD, TRO.DSCP
Union --TBL4
Select ARA2.CREATE_BY as COLL_ID, --TC.ACCT_NO,
0 as NO_CUST_ASGN,
0 as AMT_CUST_ASGN,
0 as NO_ATTEMP,
0 as NO_CUST_WORK,
0 as NO_OF_CALL,
0 as NO_OF_VISIT,
0 as NO_OF_OTHER,
0 as NO_OF_CONTACT,
0 as NO_PTP,
0 as AMT_PTP,
Sum(nvl(ARA2.NO_PTP_PK,0)) as NO_PTP_PK,
Sum(nvl(ARA2.AMT_PTP_PK,0)) as AMT_PTP_PK,
Sum(nvl(ARA2.NO_PTP_BP,0)) as NO_PTP_BP,
Sum(nvl(ARA2.AMT_PTP_BP,0)) as AMT_PTP_BP,
0 as AMT_TOT_PYMNT_COLL,
ARA2.BR_NM,
ARA2.TO_NM,
ARA2.TO_CD,
TO_CHAR(SYSDATE-4,'DD-MM-YYYY') as DT_RPT,
ARA2.BR_CD,
ARA2.BR_ID
From

(Select ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY,
BR.DSCP as BR_NM,
TRO.DSCP as TO_NM,
TRO.CD as TO_CD,
BR.CD as BR_CD,
BR.ID as BR_ID,
Max(Case when REACTYP.CD = 'PTP' and PTPS.CD = 'PK' and PTPD.VERSION = 0 then 1 Else 0 End) as NO_PTP_PK,
Sum(Case when REACTYP.CD = 'PTP' and PTPS.CD = 'PK' and PTPD.VERSION = 0 then PTPD.PTP_AMOUNT Else 0 End) as AMT_PTP_PK,
Max(Case when REACTYP.CD = 'PTP' and PTPS.CD = 'BP' and PTPD.VERSION = 0 then 1 Else 0 End) as NO_PTP_BP,
Sum(Case when REACTYP.CD = 'PTP' and PTPS.CD = 'BP' and PTPD.VERSION = 0 then PTPD.PTP_AMOUNT Else 0 End) as AMT_PTP_BP
From EBDCRS.T_ACT_REACT ARA
Inner join EBDCRS.T_CASES TC
On TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
--and TC.IS_ATTEMPTED = 'Y'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A1'
-- and TC.IS_CURED = 'N'
-- and TC.IS_OUT_OF_BOUND = 'N'
-- and TC.IS_OUT_OF_GROUP = 'N'
and ARA.MCASE_ID = TC.MCASE_ID
and ARA.TCASE_ID = TC.ID
Inner join EBDCRS.T_HT_ACCTS ACCT
On ACCT.CPF_FLAG = 'Y'
and ACCT.SYS_APF_CD = 'SYSVN'
and TC.ACCT_NO = ACCT.ACCT_NO
and TC.ACCT_MT_HOST_ID = ACCT.MT_HOST_ID
and TC.ACCT_SYS_APF_CD = ACCT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACTS ACT
On ARA.MT_ACT_ID = ACT.ID
and ARA.SYS_APF_CD = ACT.SYS_APF_CD
Inner join EBDCRS.T_MT_ACT_TYPS ACTYP
On ACT.MT_ACT_TYP_ID = ACTYP.ID
and ACT.SYS_APF_CD = ACTYP.SYS_APF_CD
Inner join EBDCRS.T_MT_REACTS REACT
On ARA.MT_REACT_ID = REACT.ID
and ARA.SYS_APF_CD = REACT.SYS_APF_CD
Inner join EBDCRS.T_MT_REACT_TYPS REACTYP
On REACT.MT_REACT_TYP_ID = REACTYP.ID
and REACT.SYS_APF_CD = REACTYP.SYS_APF_CD
Left join EBDCRS.T_PTP_DETAILS PTPD
On ARA.ID = PTPD.ACT_REACT_ID
and ARA.SYS_APF_CD = PTPD.SYS_APF_CD
Left join EBDCRS.T_MT_PTP_STATUS PTPS
On PTPD.MT_PTP_STS_ID = PTPS.ID
Left 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?
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) BR
On BR.ID = ACCT.MT_BR_ID
Left 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' -- Ph?GD
and BR.IS_DEL = 'N'
and BR.IS_DEACTIVATE = 'N'
and BR.SYS_APF_CD = 'SYSVN'
) TRO
On TRO.ID = ACCT.MT_TO_ID
Where ---TO_CHAR(PTPD.DT_UPDATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-4,'DD-MM-YYYY')
trunc(DECODE( PTPS.CD,'PTP',PTPD.dt_create,nvl(PTPD.dt_update,PTPD.dt_create)))=trunc(SYSDATE-4)
and REACTYP.CD = 'PTP'
--and ARA.MCASE_ID = '90AD76BCA62E5F90E053BF1B1FAC31AC'
and ARA.SYS_APF_CD = 'SYSVN'
Group by ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY,BR.DSCP,TRO.DSCP, TRO.CD , BR.CD,BR.ID
) ARA2

Group by ARA2.CREATE_BY,TO_CHAR(SYSDATE-4,'DD-MM-YYYY'),
ARA2.BR_NM, ARA2.TO_NM,ARA2.TO_CD, ARA2.BR_CD,ARA2.BR_ID
) TBL5
Group by COLL_ID,BR_NM,TO_NM,DT_RPT,BR_CD,BR_ID,TO_CD
) TBL6
Inner join EBDSMS.TBL_SEC_USER USR
On TBL6.COLL_ID = USR.ID;