Notes
Notes - notes.io |
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 -1 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-4) )
union (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 -1 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(NVL(PTP.DT_UPDATE,PTP.DT_CREATE))=trunc(sysdate-4))
union ( 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 -1 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-4) )
|
|
Notes.io is a web-based application for 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 12 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
