Notes

SELECT DISTINCT
USR.USR_ID AS OFFICE_ID,
USR.USR_NM AS OFFICE_NAME,
SUM(NVL(TA1.NUM_OF_ASSIGNED,0)) AS NUM_OF_ASSIGNED,
SUM(NVL(TA2.NUM_OF_PAY_OFF, 0)) AS NUM_OF_PAY_OFF,
SUM(NVL(TA3.ASSIGNED_TO_OTHER_OFFICER, 0)) AS ASSIGNED_TO_OTHER_OFFICER,
SUM(NVL(TA4.NUM_OUT_OF_BOUND, 0)) AS NUM_OUT_OF_BOUND,
SUM(NVL(TA5.NORMAL, 0)) AS NORMAL,
SUM(NVL(TA5.OS_PRINCIPLE_AMOUNT, 0)) AS OS_PRINCIPLE_AMOUNT,
SYSDATE - 1 AS DT_RPT,
'BATCH_ID' AS BATCH_ID,
USR.BR_CD AS BR_ID
FROM
(SELECT DISTINCT
SU.ID AS USR_ID,
SU.NM AS USR_NM,
NVL(BR.CD,TR_OFFICE.CD) AS BR_CD,
MD.CD AS DEPT_CD
FROM EBDCRS.T_MT_MODS MODS
INNER JOIN EBDCRS.T_DEPT_MOD_CFGS DMC
ON DMC.MT_MOD_ID = MODS.ID
INNER JOIN EBDCRS.T_MT_DEPTS MD
ON MD.ID = DMC.MT_DEPT_ID
INNER JOIN EBDCRS.T_MT_TEAMS MT
ON MT.MT_DEPT_ID = DMC.MT_DEPT_ID
AND MT.MT_MOD_ID = DMC.MT_MOD_ID
AND MT.SYS_APF_CD = 'SYSVN'
INNER JOIN EBDCRS.T_TEAM_MEMBER_MEMBERTYP_RELS MMMR
ON MMMR.MT_TEAM_ID = MT.ID
INNER JOIN EBDCRS.T_MEMBERTYP_RELS MEMBER_RELS
ON MEMBER_RELS.ID = MMMR.MEMBERTYP_REL_ID
INNER JOIN EBDCRS.T_MT_MEMBER_TYPS MEMBER_TYPE
ON MEMBER_TYPE.ID = MEMBER_RELS.MT_MEMBER_TYP_ID
INNER JOIN EBDSMS.TBL_SEC_USER SU
ON SU.ID = MMMR.SEC_USER_ID
INNER JOIN EBDCRS.T_MT_BRANCHES TR_OFFICE on TR_OFFICE.CD = SU.MT_BR_CD
LEFT JOIN EBDCRS.T_MT_BRANCHES BR on BR.ID = TR_OFFICE.BRANCH_ID
WHERE MEMBER_TYPE.CD IN ('RO', 'BRRO')
AND MODS.CD = 'RC'





) USR
LEFT JOIN
(SELECT DISTINCT
WH.RECIPIENT_ID AS RECIPIENT_ID,
COUNT(DISTINCT WH.MCASE_ID) AS NUM_OF_ASSIGNED
FROM EBDCRS.T_WORKFLOW_HISTS WH
INNER JOIN EBDCRS.T_CASES TC
ON WH.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_MT_ROUTING_TYPS MRT
ON MRT.ID = WH.MT_ROUTING_TYP_ID
WHERE MRT.CD IN ('CASE_REASSIGNMENT','ROUTING')
GROUP BY WH.RECIPIENT_ID) TA1
ON USR.USR_ID = TA1.RECIPIENT_ID
LEFT JOIN
(SELECT DISTINCT
TA.CURR_USER_ID AS CURR_USER_ID,
COUNT(DISTINCT TA.MCASE_ID) AS NUM_OF_PAY_OFF
FROM EBDCRS.T_ASSIGNMENTS TA
INNER JOIN EBDCRS.T_WORKFLOW_HISTS WH
ON TA.MCASE_ID = WH.MCASE_ID
INNER JOIN EBDCRS.T_MT_ROUTING_TYPS MRT
ON MRT.ID = WH.MT_ROUTING_TYP_ID
AND MRT.CD IN ('CASE_REASSIGNMENT','ROUTING')
INNER JOIN EBDCRS.T_CASES TC
ON TA.MCASE_ID = TC.MCASE_ID
INNER JOIN EBDCRS.T_MCASES MCASE
ON MCASE.ID = TC.MCASE_ID
WHERE NOT EXISTS (SELECT 1
FROM EBDCRS.T_CASES TC3
WHERE TC3.MCASE_ID = MCASE.ID
AND TC3.IS_CURED = 'N')
GROUP BY TA.CURR_USER_ID) TA2
ON USR.USR_ID = TA2.CURR_USER_ID
LEFT JOIN
(SELECT
WH.RECIPIENT_ID AS RECIPIENT_ID,
COUNT(DISTINCT WH.MCASE_ID) AS ASSIGNED_TO_OTHER_OFFICER
FROM EBDCRS.T_ASSIGNMENTS TA
INNER JOIN EBDCRS.T_WORKFLOW_HISTS WH
ON TA.MCASE_ID = WH.MCASE_ID
INNER JOIN EBDCRS.T_MT_ROUTING_TYPS MRT
ON MRT.ID = WH.MT_ROUTING_TYP_ID
AND MRT.CD IN ('CASE_REASSIGNMENT','ROUTING')
WHERE MRT.CD IN ('CASE_REASSIGNMENT','ROUTING')
AND TA.CURR_USER_ID <> WH.RECIPIENT_ID
GROUP BY WH.RECIPIENT_ID) TA3
ON USR.USR_ID = TA3.RECIPIENT_ID
LEFT JOIN
(SELECT DISTINCT
TA.CURR_USER_ID AS CURR_USER_ID,
COUNT(DISTINCT WH.MCASE_ID) AS NUM_OUT_OF_BOUND
FROM EBDCRS.T_WORKFLOW_HISTS WH
INNER JOIN EBDCRS.T_MT_ROUTING_TYPS MRT
ON MRT.ID = WH.MT_ROUTING_TYP_ID
AND MRT.CD IN ('CASE_REASSIGNMENT','ROUTING')
INNER JOIN EBDCRS.T_ASSIGNMENTS TA
ON WH.MCASE_ID = TA.MCASE_ID
INNER JOIN EBDCRS.T_CASES TC
ON TA.MCASE_ID = TC.MCASE_ID
INNER JOIN EBDCRS.T_MCASES MCASE
ON MCASE.ID = TC.MCASE_ID
WHERE NOT EXISTS (SELECT 1
FROM EBDCRS.T_CASES TC4
WHERE TC4.MCASE_ID = MCASE.ID
AND TC4.IS_OUT_OF_BOUND = 'N')
GROUP BY TA.CURR_USER_ID) TA4
ON USR.USR_ID = TA4.CURR_USER_ID
LEFT JOIN
(SELECT
PRINCIPALAMT.CURR_USER_ID,
COUNT(DISTINCT PRINCIPALAMT.MCASE_ID) AS NORMAL,
SUM(PRINCIPALAMT.PRINCIPALAMT) AS OS_PRINCIPLE_AMOUNT
FROM
(SELECT
VWTOTAL.CURR_USER_ID,
VWTOTAL.MCASE_ID,
SUM (DECODE (VWTOTAL.CD, 'TW', VWTOTAL.AMT_OS_PRINCIPAL, VWTOTAL.DUE_PRINCIPAL_AMT))
OVER (PARTITION BY VWTOTAL.MCASE_ID) PRINCIPALAMT --TOTAL PRINCIPALAMT
FROM
(SELECT
TA.MCASE_ID,
MH.CD,
A.AMT_OS_PRINCIPAL,
A.DUE_PRINCIPAL_AMT,
TA.CURR_USER_ID
FROM EBDCRS.T_ASSIGNMENTS TA
INNER JOIN EBDCRS.T_MCASES TM
ON TM.ID = TA.MCASE_ID
INNER JOIN EBDCRS.T_CASES TC
ON TC.MCASE_ID = TM.ID
AND TA.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_MT_HOSTS MH
ON MH.ID = TC.ACCT_MT_HOST_ID
INNER JOIN EBDCRS.T_HT_ACCTS A
ON A.ACCT_NO = TC.ACCT_NO
AND A.CPF_FLAG = 'Y'
WHERE TC.MT_MOD_ID =
(SELECT ID
FROM EBDCRS.T_MT_MODS
WHERE CD = 'RC'
AND IS_DEL = 'N')
AND TC.IS_CURED = 'N'
AND TC.IS_OUT_OF_BOUND = 'N'
AND TC.IS_OUT_OF_GROUP = 'N'
AND TA.DT_WORKED_ON IS NULL
AND ((EXISTS
(SELECT 1
FROM EBDCRS.T_SKIP_ASGN_CASES SAC
INNER JOIN EBDCRS.T_MT_SKIP_ASGN_RSN SAR
ON SAR.ID = SAC.MT_SKIP_ASGN_RSN_ID
AND SAR.SYS_APF_CD = SAC.SYS_APF_CD
WHERE SAR.CD IN ('CASE_REASSIGNMENT', 'ROUTING')
AND SAC.MCASE_ID = TA.MCASE_ID)
AND TM.IS_SKIP_TRACE <> 'Y'
AND NVL (TM.IS_ST_ASSIGNED, 'N') <> 'Y')
OR TA.DT_ROUTE IS NOT NULL)) VWTOTAL) PRINCIPALAMT
GROUP BY PRINCIPALAMT.CURR_USER_ID) TA5
ON USR.USR_ID = TA5.CURR_USER_ID
WHERE USR.DEPT_CD = 'RECOVERY'
GROUP BY USR.USR_ID, USR.USR_NM, USR.BR_CD, SYSDATE - 1, 'BATCH_ID'
ORDER BY USR.USR_ID;