Notes
Notes - notes.io |
TO_CHAR(a1.DT_RPT,'DD-MM-YYYY') as DT_RPT,
Count(a1.MCASE_ID) as NO_ACCT_ASSIGNED,
Sum(nvl(NO_SUCC_CONTACT,0)) as NO_SUCC_CONTACT,
Sum(nvl(NO_PTP,0)) as NO_PTP,
Sum(nvl(NO_PTP_PK,0)) as NO_PTP_PK,
Sum(nvl(NO_PTP_BP,0)) as NO_PTP_BP,
Sum(nvl(PYMT_AMT,0)) as AMT_PTP_COLLECT
From
(Select MCASE_ID, CURR_USER_ID, WORST_AGING_CASE_ID, DT_ASSIGNED as DT_RPT
From EBDCRS.T_ASSIGNMENTS ASGNMS
Where TO_CHAR(ASGNMS.DT_ASSIGNED,'DD-MM-YYYY') = TO_CHAR(SYSDATE-1,'DD-MM-YYYY')
and SYS_APF_CD = 'SYSVN'
and CURR_USER_ID is not null
--and CURR_USER_ID = 'CC.Ngoc01'
)a1
Inner join
(Select TC.ID, TC.MCASE_ID, ACCT.ACCT_NO, ACCT.MT_HOST_ID
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
--and ACCT.MT_PRODUCT_ID = 'BEDD21BB44AC7C12E0401FAC0E5C3C88' -- 'BEDD21BB44AC7C12E0401FAC0E5C3C88' --> Nh m CC
Where TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A2'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
) a2
On a1.MCASE_ID = a2.MCASE_ID
Left join
(Select ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY,
Sum(Case when (ACTYP.CD = 'CALL' and REACTYP.CD IN ('PTP','PR','SC')) then 1 Else 0 End) as NO_SUCC_CONTACT,
Sum(Case when REACTYP.CD = 'PTP' and PTPD.VERSION = 0 then 1 Else 0 End) as NO_PTP
From EBDCRS.T_ACT_REACT ARA
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-1,'DD-MM-YYYY')
--and ARA.MCASE_ID in ('90AD76BC2C125F90E053BF1B1FAC31AC','90AD76BC498B5F90E053BF1B1FAC31AC','90AD76BCF2065F90E053BF1B1FAC31AC')
--and ARA.TCASE_ID in ('90AD76BBF2925F90E053BF1B1FAC31AC','90AD76BBF1EB5F90E053BF1B1FAC31AC','90AD76BB8AD95F90E053BF1B1FAC31AC')
and ARA.SYS_APF_CD = 'SYSVN'
Group by ARA.MCASE_ID,ARA.TCASE_ID, ARA.CREATE_BY
) ARA
On a1.MCASE_ID = ARA.MCASE_ID
and a2.ID = ARA.TCASE_ID --Q1
and a1.CURR_USER_ID = ARA.CREATE_BY
Left join
(Select ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY,
Sum(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 = 'BP' and PTPD.VERSION = 0 then 1 Else 0 End) as NO_PTP_BP
From EBDCRS.T_ACT_REACT ARA
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(PTPD.DT_UPDATE,'DD-MM-YYYY') = TO_CHAR(SYSDATE-1,'DD-MM-YYYY')
and REACTYP.CD = 'PTP'
--and ((TRUNC(SYSDATE-1,'MM')+8 <= nvl(ARA.DT_UPDATE,ARA.DT_CREATE)
--and nvl(ARA.DT_UPDATE,ARA.DT_CREATE)<= SYSDATE-1) or TO_CHAR(TRUNC(SYSDATE-1,'MM')+8,'DD-MM-YYYY')= nvl(TO_CHAR(ARA.DT_UPDATE,'DD-MM-YYYY'),TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY')) or nvl(TO_CHAR(ARA.DT_UPDATE,'DD-MM-YYYY'),TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY'))= TO_CHAR(SYSDATE-1,'DD-MM-YYYY'))
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 a2.ID = ARA1.TCASE_ID --Q1
and a1.CURR_USER_ID = ARA1.CREATE_BY
Left join
(Select PTP.CREATE_BY, PTP.MCASE_ID, PTP.TCASE_ID, Sum(nvl(PYMT_AMT,0)) as PYMT_AMT
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
---and ACCT.MT_PRODUCT_ID = 'BEDD21BB44AC7C12E0401FAC0E5C3C88' -- 'BEDD21BB44AC7C12E0401FAC0E5C3C88' --> Nh m CC
and TC.MT_DEPT_ID = '29983461AC1E51164FD9BDD1C85777A2'
Inner join
(Select ARA.MCASE_ID, ARA.TCASE_ID, ARA.CREATE_BY,
PTPD.DT_START as PTP_START, PTPD.DT_END as PTP_DATE
From EBDCRS.T_ACT_REACT ARA
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 -- Q3
Left join EBDCRS.T_MT_PTP_STATUS PTPS
On PTPD.MT_PTP_STS_ID = PTPS.ID
Where REACTYP.CD = 'PTP' and PTPD.VERSION = 0
and ARA.SYS_APF_CD = 'SYSVN'
--and ((TRUNC(SYSDATE-1,'MM')+8 <= nvl(ARA.DT_UPDATE,ARA.DT_CREATE)
--and nvl(ARA.DT_UPDATE,ARA.DT_CREATE)<= SYSDATE-1) or TO_CHAR(TRUNC(SYSDATE-1,'MM')+8,'DD-MM-YYYY')= nvl(TO_CHAR(ARA.DT_UPDATE,'DD-MM-YYYY'),TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY')) or nvl(TO_CHAR(ARA.DT_UPDATE,'DD-MM-YYYY'),TO_CHAR(ARA.DT_CREATE,'DD-MM-YYYY'))= TO_CHAR(SYSDATE-1,'DD-MM-YYYY'))
--and (ARA.MCASE_ID = '909C863D03265F86E053BF1B1FACD72F' and ARA.TCASE_ID = '909C863B99665F86E053BF1B1FACD72F'
--or ARA.MCASE_ID = '909C863D2CF15F86E053BF1B1FACD72F' and ARA.TCASE_ID = '909C863BA0E75F86E053BF1B1FACD72F'
--or ARA.MCASE_ID = '909C863D3FD15F86E053BF1B1FACD72F' and ARA.TCASE_ID = '909C863BB4065F86E053BF1B1FACD72F')
--Order by ARA.MCASE_ID
) PTP
On TC.MCASE_ID = PTP.MCASE_ID
and TC.ID = PTP.TCASE_ID
Inner join EBDCRS.T_HT_ACCT_REPYMTS REP
On ACCT.ACCT_NO = REP.HT_ACCT_NO
and ACCT.MT_HOST_ID = REP.MT_HOST_ID
and (TO_CHAR(DT_REPYMT,'DD-MM-YYYY') = TO_CHAR(PTP.PTP_START,'DD-MM-YYYY') or TO_CHAR(DT_REPYMT,'DD-MM-YYYY') = TO_CHAR(PTP.PTP_DATE,'DD-MM-YYYY') or
DT_REPYMT between PTP.PTP_START and PTP.PTP_DATE)
Where TC.CPF_FLAG = 'Y'
and TC.SYS_APF_CD = 'SYSVN'
and TC.IS_CURED = 'N'
and TC.IS_OUT_OF_BOUND = 'N'
and TC.IS_OUT_OF_GROUP = 'N'
and TO_CHAR(REP.DT_REPYMT,'DD-MM-YYYY') = TO_CHAR(SYSDATE-1,'DD-MM-YYYY')
Group by PTP.CREATE_BY, PTP.MCASE_ID, PTP.TCASE_ID
) PAY
On a1.MCASE_ID = PAY.MCASE_ID
and a2.ID = PAY.TCASE_ID
and a1.CURR_USER_ID = PAY.CREATE_BY
Group by a1.CURR_USER_ID,TO_CHAR(a1.DT_RPT,'DD-MM-YYYY')
|
|
Notes is a web-based application for online taking notes. You can take your notes and share with others people. If you like taking long notes, notes.io is designed for you. To date, over 8,000,000,000+ notes created and continuing...
With notes.io;
- * You can take a note from anywhere and any device with internet connection.
- * You can share the notes in social platforms (YouTube, Facebook, Twitter, instagram etc.).
- * You can quickly share your contents without website, blog and e-mail.
- * You don't need to create any Account to share a note. As you wish you can use quick, easy and best shortened notes with sms, websites, e-mail, or messaging services (WhatsApp, iMessage, Telegram, Signal).
- * Notes.io has fabulous infrastructure design for a short link and allows you to share the note as an easy and understandable link.
Fast: Notes.io is built for speed and performance. You can take a notes quickly and browse your archive.
Easy: Notes.io doesn’t require installation. Just write and share note!
Short: Notes.io’s url just 8 character. You’ll get shorten link of your note when you want to share. (Ex: notes.io/q )
Free: Notes.io works for 14 years and has been free since the day it was started.
You immediately create your first note and start sharing with the ones you wish. If you want to contact us, you can use the following communication channels;
Email: [email protected]
Twitter: http://twitter.com/notesio
Instagram: http://instagram.com/notes.io
Facebook: http://facebook.com/notesio
Regards;
Notes.io Team
