Notes

SELECT
A.PRD_DSCP PRD_DSCP,
NVL(A.ATTEMPTED,0) ATTEMPTED ,
NVL(A.NUM_PTP,0) NUM_PTP,
nvl(A.NUM_PK,0) NUM_PK,
Case when NUM_PTP <> 0 then nvl(NUM_PK,0)/ NUM_PTP*100 else 0 end PERC_PK,
nvl(A.NUM_BP,0) NUM_BP,
CASE WHEN NUM_PTP <> 0 THEN nvl(NUM_BP,0)/NUM_PTP*100 else 0 end PERC_BP,
NVL(A.NUM_PTP_CM,0) NUM_PTP_CM,
CASE WHEN NUM_PTP <> 0 THEN NVL(NUM_PTP_CM,0)/NUM_PTP*100 else 0 end PERC_PTP_CM,
sysdate-9 DT_RPT,
A.PRD_CD PRD_CD
from
(SELECT
STG.PRD_DSCP,
STG.PRD_CD,
sum(case when note = 1 and trunc(STG.DT_CREATE) = trunc(sysdate-9) then note else null end) ATTEMPTED,
COUNT (case when (note = 1 and CD='PTP' and trunc(STG.DT_CREATE) = trunc(sysdate-9)) THEN note END ) AS NUM_PTP,
count (case when (note = 1 and CD='PTP' AND PTPS_CD ='PK' and trunc(dt_react)=trunc(sysdate-9)) then note end) AS NUM_PK,
count (case when (note = 1 and CD='PTP' AND PTPS_CD ='BP' and trunc(dt_react)=trunc(sysdate-9)) then note end) AS NUM_BP,
COUNT ( CASE WHEN (note = 1 and trunc(STG.DT_CREATE) = trunc(sysdate-9) and (EXTRACT(year FROM trunc(DT_END))*100 + EXTRACT(month FROM trunc(DT_END))) > (EXTRACT(year FROM sysdate-9)*100 + EXTRACT(month FROM sysdate-9)) AND trunc(DT_END)>= trunc(sysdate-9) AND CD='PTP')
THEN note END ) AS NUM_PTP_CM

FROM ( SELECT
NVL(TYPS.DSCP,'Credit Card') as PRD_DSCP,
NVL(TYPS.CD,'4') AS PRD_CD,
PTPS.CD as PTPS_CD,
row_number ( ) over (partition by ACCT.ACCT_NO,trunc(ARA.DT_CREATE ) order by ARA.DT_CREATE desc) as NOTE,
TMRT.CD AS CD,
NVL(PTP.DT_UPDATE,PTP.DT_CREATE) as dt_react,
PTP.DT_END AS DT_END,
ARA.DT_CREATE AS DT_CREATE,
sysdate-9 DT_RPT
FROM EBDCRS.T_ASSIGNMENTS ASGNMS
INNER JOIN EBDCRS.T_MCASES M ON M.ID = ASGNMS.MCASE_ID
INNER JOIN EBDCRS.T_CASES TC ON M.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_ACT_REACT ARA ON ARA.TCASE_ID = TC.ID
LEFT JOIN EBDCRS.T_PTP_DETAILS PTP ON PTP.ACT_REACT_ID = ARA.ID and PTP.SYS_APF_CD=ARA.SYS_APF_CD
INNER JOIN EBDCRS.T_HT_ACCTS ACCT ON TC.ACCT_NO = ACCT.ACCT_NO AND TC.SYS_APF_CD = ACCT.SYS_APF_CD and ACCT.CPF_FLAG='Y'
LEFT JOIN EBDCRS.T_MT_SEC_TYPS TYPS ON TYPS.CD=ACCT.SEC_DETAIL
LEFT JOIN EBDCRS.T_MT_PTP_STATUS PTPS ON PTP.MT_PTP_STS_ID = PTPS.ID AND PTP.SYS_APF_CD = PTPS.SYS_APF_CD
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_ACTS ACT On ARA.MT_ACT_ID = ACT.ID and ARA.SYS_APF_CD = ACT.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 TMRT ON REACT.MT_REACT_TYP_ID = TMRT.ID AND REACT.SYS_APF_CD = TMRT.SYS_APF_CD
where trunc(ARA.DT_CREATE) = trunc(sysdate-9)

union all

SELECT
NVL(TYPS.DSCP,'Credit Card') as PRD_DSCP,
NVL(TYPS.CD,'4') AS PRD_CD,
PTPS.CD as PTPS_CD,
row_number ( ) over (partition by ACCT.ACCT_NO,trunc(ARA.DT_CREATE ) order by ARA.DT_CREATE desc) as NOTE,
TMRT.CD AS CD,
NVL(PTP.DT_UPDATE,PTP.DT_CREATE) as dt_react,
PTP.DT_END AS DT_END,
ARA.DT_CREATE AS DT_CREATE,
sysdate-9 DT_RPT
FROM EBDCRS.T_ASSIGNMENTS ASGNMS
INNER JOIN EBDCRS.T_MCASES M ON M.ID = ASGNMS.MCASE_ID
INNER JOIN EBDCRS.T_CASES TC ON M.ID = TC.MCASE_ID
INNER JOIN EBDCRS.T_ACT_REACT ARA ON ARA.TCASE_ID = TC.ID
LEFT JOIN EBDCRS.T_PTP_DETAILS PTP ON PTP.ACT_REACT_ID = ARA.ID and PTP.SYS_APF_CD=ARA.SYS_APF_CD
INNER JOIN EBDCRS.T_HT_ACCTS ACCT ON TC.ACCT_NO = ACCT.ACCT_NO AND TC.SYS_APF_CD = ACCT.SYS_APF_CD and ACCT.CPF_FLAG='Y'
LEFT JOIN EBDCRS.T_MT_SEC_TYPS TYPS ON TYPS.CD=ACCT.SEC_DETAIL
LEFT JOIN EBDCRS.T_MT_PTP_STATUS PTPS ON PTP.MT_PTP_STS_ID = PTPS.ID AND PTP.SYS_APF_CD = PTPS.SYS_APF_CD
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_ACTS ACT On ARA.MT_ACT_ID = ACT.ID and ARA.SYS_APF_CD = ACT.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 TMRT ON REACT.MT_REACT_TYP_ID = TMRT.ID AND REACT.SYS_APF_CD = TMRT.SYS_APF_CD
WHERE trunc(NVL(PTP.DT_UPDATE,PTP.DT_CREATE))=trunc(sysdate-9)
)STG

GROUP BY STG.PRD_DSCP,STG.PRD_CD
) A ;