[Home] [Help]
138: AND au.user_name IN (FROM_ROLE,RECIPIENT_ROLE)
139: AND wfn.STATUS='OPEN'
140: AND wfn.MESSAGE_TYPE='CSM_MSGS'
141: AND (nvl(wfn.begin_date, sysdate) between
142: (sysdate - csm_profile_pkg.get_task_history_days(b_user_id))and sysdate)
143: AND NOT EXISTS(SELECT 1
144: FROM CSM_NOTIFICATIONS_ACC ACC
145: WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
146: AND ACC.USER_ID = AU.USER_ID);
156: AND RECIPIENT_ROLE LIKE 'JRES_GRP:%'
157: AND au.user_id =CSM_UTIL_PKG.get_group_owner(substr(WFN.RECIPIENT_ROLE,instr(WFN.RECIPIENT_ROLE,':')+1))
158: AND au.enabled='Y'
159: AND (nvl(wfn.begin_date, sysdate) between
160: (sysdate - csm_profile_pkg.get_task_history_days(b_user_id))and sysdate)
161: AND NOT EXISTS(SELECT 1
162: FROM CSM_NOTIFICATIONS_ACC ACC
163: WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
164: AND ACC.USER_ID = AU.USER_ID);
204: AND wfn.MESSAGE_TYPE='CSM_MSGS'
205: AND au.user_name IN (WFN.FROM_ROLE,WFN.RECIPIENT_ROLE)
206: AND au.enabled='Y'
207: AND (nvl(wfn.begin_date, sysdate) between
208: (sysdate - csm_profile_pkg.get_task_history_days(au.user_id))and sysdate)
209: AND NOT EXISTS(SELECT 1
210: FROM CSM_NOTIFICATIONS_ACC ACC
211: WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
212: AND ACC.USER_ID = AU.USER_ID);
222: AND RECIPIENT_ROLE LIKE 'JRES_GRP:%'
223: AND au.user_id =CSM_UTIL_PKG.get_group_owner(substr(WFN.RECIPIENT_ROLE,instr(WFN.RECIPIENT_ROLE,':')+1))
224: AND au.enabled='Y'
225: AND (nvl(wfn.begin_date, sysdate) between
226: (sysdate - csm_profile_pkg.get_task_history_days(au.user_id))and sysdate)
227: AND NOT EXISTS(SELECT 1
228: FROM CSM_NOTIFICATIONS_ACC ACC
229: WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
230: AND ACC.USER_ID = AU.USER_ID);
397: (wfn.RECIPIENT_ROLE NOT LIKE 'JRES_GRP:%')
398: OR
399: (wfn.status='CLOSED') --skip OPEN grp messages, let user delete/close it
400: )
401: AND (wfn.status='CLOSED' OR NVL(wfn.begin_date, SYSDATE) < (SYSDATE - csm_profile_pkg.get_task_history_days(acc.user_id))))
402: LOOP
403:
404: IF rec.status= 'OPEN' THEN
405: begin