[Home] [Help]
32: CNMA.USER_ID
33: FROM CSM_NEW_MESSAGES_ACC CNMA
34: WHERE CNMA.LEVEL_ID=10003
35: AND NOT EXISTS (SELECT 1
36: FROM CSM_NEW_MESSAGES_PERZ CNMP,ASG_USER AU
37: WHERE CNMP.MESSAGE_ID = CNMA.MESSAGE_ID
38: AND CNMP.LANGUAGE=CNMA.LANGUAGE
39: AND CNMP.LEVEL_ID=10003
40: AND CNMA.USER_ID=AU.USER_ID
58: CNMA.USER_ID
59: FROM CSM_NEW_MESSAGES_ACC CNMA
60: WHERE CNMA.LEVEL_ID=0
61: AND EXISTS (SELECT 1
62: FROM CSM_NEW_MESSAGES_PERZ CNMP,ASG_USER AU
63: WHERE CNMP.MESSAGE_ID = CNMA.MESSAGE_ID
64: AND CNMA.USER_ID = AU.USER_ID
65: AND (
66: (CNMP.LEVEL_ID=10001
82: WHERE CNMA.LEVEL_ID = 10001
83: AND CNMA.LEVEL_VALUE = 0
84: AND EXISTS (SELECT 1
85: FROM CSM_NEW_MESSAGES_PERZ CNMP,
86: ASG_USER AU
87: WHERE CNMP.MESSAGE_ID=CNMA.MESSAGE_ID
88: AND CNMP.LANGUAGE = CNMA.LANGUAGE
89: AND CNMA.USER_ID = AU.USER_ID
90: AND (
100: FROM CSM_NEW_MESSAGES_ACC CNMA
101: WHERE CNMA.LEVEL_ID = 10003
102: AND EXISTS (SELECT 1
103: FROM CSM_NEW_MESSAGES_PERZ CNMP,
104: ASG_USER AU
105: WHERE CNMP.MESSAGE_ID=CNMA.MESSAGE_ID
106: AND CNMP.LANGUAGE = CNMA.LANGUAGE
107: AND CNMA.USER_ID = AU.USER_ID
108: AND CNMA.LEVEL_VALUE = AU.RESPONSIBILITY_ID
130:
131: TYPE con_rec_type IS RECORD
132: (
133: MESSAGE_ID CSM_NEW_MESSAGES.MESSAGE_ID%TYPE,
134: USER_ID ASG_USER.USER_ID%TYPE
135: );
136:
137: TYPE l_tab_type IS TABLE OF con_rec_type
138: INDEX BY BINARY_INTEGER;
244: WHERE CNMT.MESSAGE_ID = CNMA.MESSAGE_ID
245: AND CNMT.LANGUAGE = CNMA.LANGUAGE
246: AND CNMT.LAST_UPDATE_DATE>b_lastrundate
247: AND CNMA.LEVEL_ID=0 --PREVIOUSLY AND NOW ALSO NOT PERSONALIZED
248: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP,ASG_USER AU
249: WHERE CNMA.MESSAGE_ID=CNMP.MESSAGE_ID
250: AND AU.USER_ID=AU.OWNER_ID
251: AND ( CNMP.LEVEL_ID=10001
252: OR ---IF SITE-LEVEL PERZ IS THERE THEN DON'T UPDATE ANY REC WITH TL-TABLE
271: AU.USER_ID,
272: 1 ACCESS_ID
273: FROM CSM_NEW_MESSAGES CNM,
274: CSM_NEW_MESSAGES_PERZ CNMP,
275: ASG_USER AU
276: WHERE CNM.MESSAGE_ID=CNMP.MESSAGE_ID
277: AND CNMP.LANGUAGE =AU.LANGUAGE
278: AND AU.USER_ID=AU.OWNER_ID
279: AND ( (CNMP.LEVEL_VALUE = AU.USER_ID
313: AU.USER_ID,
314: 1 ACCESS_ID
315: FROM CSM_NEW_MESSAGES CNM,
316: CSM_NEW_MESSAGES_TL CNMT,
317: ASG_USER AU
318: WHERE CNM.MESSAGE_ID = CNMT.MESSAGE_ID
319: AND AU.USER_ID=AU.OWNER_ID
320: AND CNMT.LANGUAGE = AU.LANGUAGE
321: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP
344: LEVEL_VALUE CSM_NEW_MESSAGES_PERZ.LEVEL_VALUE%TYPE,
345: LANGUAGE CSM_NEW_MESSAGES_TL.LANGUAGE%TYPE,
346: MESSAGE_TEXT CSM_NEW_MESSAGES_TL.MESSAGE_TEXT%TYPE,
347: DESCRIPTION CSM_NEW_MESSAGES_TL.DESCRIPTION%TYPE,
348: USER_ID ASG_USER.USER_ID%TYPE,
349: ACCESS_ID CSM_NEW_MESSAGES_ACC.ACCESS_ID%TYPE
350: );
351:
352: TYPE l_tab_type IS TABLE OF con_rec_type
519: AU.USER_ID,
520: 1 ACCESS_ID
521: FROM CSM_NEW_MESSAGES CNM,
522: CSM_NEW_MESSAGES_PERZ CNMP,
523: ASG_USER AU
524: WHERE AU.USER_ID= b_user_id
525: AND AU.USER_ID=AU.OWNER_ID
526: AND CNM.MESSAGE_ID=CNMP.MESSAGE_ID
527: AND CNMP.LANGUAGE =AU.LANGUAGE
558: AU.USER_ID,
559: 1 ACCESS_ID
560: FROM CSM_NEW_MESSAGES CNM,
561: CSM_NEW_MESSAGES_TL CNMT,
562: ASG_USER AU
563: WHERE AU.USER_ID= b_user_id
564: AND AU.USER_ID=AU.OWNER_ID
565: AND CNM.MESSAGE_ID = CNMT.MESSAGE_ID
566: AND CNMT.LANGUAGE = AU.LANGUAGE
586: LEVEL_VALUE CSM_NEW_MESSAGES_PERZ.LEVEL_VALUE%TYPE,
587: LANGUAGE CSM_NEW_MESSAGES_TL.LANGUAGE%TYPE,
588: MESSAGE_TEXT CSM_NEW_MESSAGES_TL.MESSAGE_TEXT%TYPE,
589: DESCRIPTION CSM_NEW_MESSAGES_TL.DESCRIPTION%TYPE,
590: USER_ID ASG_USER.USER_ID%TYPE,
591: ACCESS_ID CSM_NEW_MESSAGES_ACC.ACCESS_ID%TYPE
592: );
593:
594: TYPE l_tab_type IS TABLE OF con_rec_type