[Home] [Help]
PACKAGE BODY: APPS.CSM_NEW_MESSAGES_EVENT_PKG
Source
1 PACKAGE BODY CSM_NEW_MESSAGES_EVENT_PKG AS
2 /* $Header: csmenmgb.pls 120.13.12010000.2 2008/10/22 12:43:56 trajasek ship $ */
3 /*** Globals ***/
4 g_new_msg_acc_tab_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_ACC';
5 g_new_msg_tl_tab_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_TL';
6 g_new_msg_perz_tab_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_PERZ';
7 g_new_msg_table_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES';
8 g_new_msg_pubi_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES';
9
10 g_new_msg_pkg_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_EVENT_PKG';
11 g_new_msg_api_name CONSTANT VARCHAR2(30) := 'REFRESH_ACC';
12
13 --Bug 5409433
14 PROCEDURE HANDLE_DELETE(p_status OUT NOCOPY VARCHAR2 ,p_message OUT NOCOPY VARCHAR2)
15 IS
16
17 CURSOR c_delete IS
18 --EARLIER PERZed ,NOW NO_PERZ : part 1 of 3
19 SELECT CNMA.MESSAGE_ID,
20 CNMA.USER_ID
21 FROM CSM_NEW_MESSAGES_ACC CNMA
22 WHERE CNMA.LEVEL_ID=10001
23 AND CNMA.LEVEL_VALUE=0
24 AND NOT EXISTS (SELECT 1
25 FROM CSM_NEW_MESSAGES_PERZ CNMP
26 WHERE CNMP.MESSAGE_ID = CNMA.MESSAGE_ID
27 AND CNMP.LANGUAGE=CNMA.LANGUAGE
28 AND CNMP.LEVEL_ID=10001 )
29 UNION ALL
30 --EARLIER PERZed ,NOW NO_PERZ : part 2 of 3
31 SELECT CNMA.MESSAGE_ID,
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
41 AND AU.RESPONSIBILITY_ID=CNMP.LEVEL_VALUE)
42 UNION ALL
43 --EARLIER PERZed ,NOW NO_PERZ : part 3 of 3
44 SELECT CNMA.MESSAGE_ID,
45 CNMA.USER_ID
46 FROM CSM_NEW_MESSAGES_ACC CNMA
47 WHERE CNMA.LEVEL_ID=10004
48 AND
49 NOT EXISTS (SELECT 1
50 FROM CSM_NEW_MESSAGES_PERZ CNMP
51 WHERE CNMP.MESSAGE_ID = CNMA.MESSAGE_ID
52 AND CNMP.LANGUAGE=CNMA.LANGUAGE
53 AND CNMP.LEVEL_ID=10004
54 AND CNMA.USER_ID=CNMP.LEVEL_VALUE)
55 UNION ALL
56 --EARLIER NO_PERZ,NOW PERZed
57 SELECT CNMA.MESSAGE_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
67 AND CNMP.LEVEL_VALUE=0)
68 OR
69 (CNMP.LEVEL_ID=10003
70 AND CNMP.LEVEL_VALUE=AU.RESPONSIBILITY_ID
71 /*AND CNMA.USER_ID=AU.USER_ID*/)
72 OR
73 (CNMP.LEVEL_ID=10004
74 /*AND CNMP.LEVEL_VALUE=CNMA.USER_ID*/
75 AND CNMP.LEVEL_VALUE=AU.USER_ID))
76 )
77 UNION ALL
78 --EARLIER PERZed ,BUT Now Inserted FINER LEVEL_ID : part 1 of 2
79 SELECT CNMA.MESSAGE_ID,
80 CNMA.USER_ID
81 FROM CSM_NEW_MESSAGES_ACC CNMA
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 (
91 (CNMP.LEVEL_ID=10003
92 AND CNMP.LEVEL_VALUE = AU.RESPONSIBILITY_ID) --Site to Resp
93 OR (CNMP.LEVEL_ID=10004
94 AND CNMP.LEVEL_VALUE = AU.USER_ID) -- Site to User
95 ))
96 UNION ALL
97 --EARLIER PERZed ,BUT Now Inserted FINER LEVEL_ID : part2 of 2
98 SELECT CNMA.MESSAGE_ID,
99 CNMA.USER_ID
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
109 AND CNMP.LEVEL_ID=10004
110 AND CNMP.LEVEL_VALUE = AU.USER_ID) ; --Resp to User
111
112
113 CURSOR c_delete_fragment IS
114 --EARLIER PERZed/NO_PERZed BUT NOW REMOVED FROM BASE_TABLE
115 SELECT /*+index (cnma csm_new_messages_acc_u2) */
116 CNMA.MESSAGE_ID,
117 CNMA.USER_ID
118 FROM CSM_NEW_MESSAGES_ACC CNMA
119 WHERE CNMA.message_id is not null
120 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES CNM
121 WHERE CNM.MESSAGE_ID=CNMA.MESSAGE_ID) ;
122
123
124 CURSOR c_get_access(b_msg_id NUMBER, b_user_id NUMBER) IS
125 SELECT /*+index (cnma csm_new_messages_acc_u2) */
126 CNMA.ACCESS_ID
127 FROM CSM_NEW_MESSAGES_ACC CNMA
128 WHERE CNMA.MESSAGE_ID = b_msg_id
129 AND CNMA.USER_ID = b_user_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;
139
140 l_tab l_tab_type;
141 l_access_id NUMBER;
142 l_dummy BOOLEAN;
143
144 l_sqlerrno VARCHAR2(20);
145 l_sqlerrmsg VARCHAR2(4000);
146 BEGIN
147
148 OPEN c_delete;
149 FETCH c_delete BULK COLLECT INTO l_tab;
150 CLOSE c_delete;
151
152
153 CSM_UTIL_PKG.LOG('Entering DELETE to remove ' || l_tab.count||' records',
154 'CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE',FND_LOG.LEVEL_PROCEDURE);
155
156 FOR I IN 1..l_tab.COUNT
157 LOOP
158 OPEN c_get_access(l_tab(I).MESSAGE_ID,l_tab(I).USER_ID);
159 FETCH c_get_access INTO l_access_id;
160 CLOSE c_get_access;
161
162 l_dummy := asg_download.mark_dirty(g_new_msg_pubi_name,l_ACCESS_ID , l_tab(I).USER_ID, 'D', sysdate );
163
164 DELETE FROM CSM_NEW_MESSAGES_ACC WHERE ACCESS_ID=l_ACCESS_ID;
165 END LOOP;
166
167 COMMIT;
168
169 l_tab.DELETE;
170
171 OPEN c_delete_fragment;
172 FETCH c_delete_fragment BULK COLLECT INTO l_tab;
173 CLOSE c_delete_fragment;
174
175
176 CSM_UTIL_PKG.LOG('Entering DELETE FRAGMENT to remove ' || l_tab.count||' records',
177 'CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE',FND_LOG.LEVEL_PROCEDURE);
178
179 FOR I IN 1..l_tab.COUNT
180 LOOP
181 OPEN c_get_access(l_tab(I).MESSAGE_ID,l_tab(I).USER_ID);
182 FETCH c_get_access INTO l_access_id;
183 CLOSE c_get_access;
184
185 l_dummy := asg_download.mark_dirty(g_new_msg_pubi_name,l_ACCESS_ID , l_tab(I).USER_ID, 'D', sysdate );
186
187 DELETE FROM CSM_NEW_MESSAGES_ACC WHERE ACCESS_ID=l_ACCESS_ID;
188 END LOOP;
189
190 CSM_UTIL_PKG.LOG('DELETION successful',
191 'CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE',FND_LOG.LEVEL_PROCEDURE);
192 p_status :='SUCCESS';
193 p_message :='DELETION in CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE successful';
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 l_sqlerrno := TO_CHAR(SQLCODE);
198 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
199 p_status := 'ERROR';
200 p_message := 'Error in CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE: ' || l_sqlerrno || ':' || l_sqlerrmsg;
201 RAISE;
202 csm_util_pkg.log('CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
203 END HANDLE_DELETE;
204
205 PROCEDURE REFRESH_ACC(p_status OUT NOCOPY VARCHAR2,
206 p_message OUT NOCOPY VARCHAR2) IS
207
208 PRAGMA AUTONOMOUS_TRANSACTION;
209 /*** get the last run date of the concurent program ***/
210 CURSOR c_LastRundate IS
211 SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
212 FROM JTM_CON_REQUEST_DATA
213 WHERE package_name = g_new_msg_pkg_name
214 AND procedure_name = g_new_msg_api_name;
215
216 --UPDATE--
217 CURSOR c_update(b_lastrundate DATE) IS
218 SELECT CNMA.MESSAGE_ID,
219 CNMA.LEVEL_ID,
220 CNMA.LEVEL_VALUE,
221 CNMA.LANGUAGE,
222 CNMP.MESSAGE_TEXT,
223 CNMP.DESCRIPTION,
224 CNMA.USER_ID,
225 CNMA.ACCESS_ID
226 FROM CSM_NEW_MESSAGES_ACC CNMA,
227 CSM_NEW_MESSAGES_PERZ CNMP
228 WHERE CNMA.MESSAGE_ID=CNMP.MESSAGE_ID
229 AND (CNMP.LEVEL_ID = CNMA.LEVEL_ID
230 AND CNMP.LEVEL_VALUE= CNMA.LEVEL_VALUE
231 AND CNMP.LANGUAGE = CNMA.LANGUAGE
232 AND CNMP.LAST_UPDATE_DATE>b_lastrundate )
233 UNION ALL
234 SELECT CNMA.MESSAGE_ID,
235 CNMA.LEVEL_ID,
236 CNMA.LEVEL_VALUE,
237 CNMA.LANGUAGE,
238 CNMT.MESSAGE_TEXT,
239 CNMT.DESCRIPTION,
240 CNMA.USER_ID,
241 CNMA.ACCESS_ID
242 FROM CSM_NEW_MESSAGES_ACC CNMA,
243 CSM_NEW_MESSAGES_TL CNMT
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
253 (CNMP.LEVEL_ID=10003
254 AND CNMA.USER_ID=AU.USER_ID
255 AND AU.RESPONSIBILITY_ID=CNMP.LEVEL_VALUE)
256 OR ---IF RESP-LEVEL PERZ IS THERE THEN DON'T UPDATE THAT USER RECS WITH TL-TABLE
257 (CNMP.LEVEL_ID=10004
258 AND CNMA.USER_ID=CNMP.LEVEL_VALUE) )) ;
259
260
261
262 --INSERT--
263 CURSOR c_insert IS
264 --PERZ EXISTS
265 SELECT CNM.MESSAGE_ID,
266 CNMP.LEVEL_ID,
267 CNMP.LEVEL_VALUE,
268 CNMP.LANGUAGE,
269 CNMP.MESSAGE_TEXT,
270 CNMP.DESCRIPTION,
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
280 AND CNMP.LEVEL_ID = 10004)
281 OR
282 (CNMP.LEVEL_VALUE = AU.RESPONSIBILITY_ID
283 AND CNMP.LEVEL_ID = 10003
284 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1
285 WHERE AU.USER_ID = CNMP1.LEVEL_VALUE AND CNMP1.LEVEL_ID = 10004))
286 OR
287 (CNMP.LEVEL_VALUE=0
288 AND CNMP.LEVEL_ID = 10001
289 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1
290 WHERE CNMP.MESSAGE_ID=CNMP1.MESSAGE_ID
291 AND CNMP.LANGUAGE=CNMP1.LANGUAGE
292 AND AU.RESPONSIBILITY_ID = CNMP1.LEVEL_VALUE
293 AND CNMP1.LEVEL_ID = 10003)
294 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1
295 WHERE CNMP.MESSAGE_ID=CNMP1.MESSAGE_ID
296 AND CNMP.LANGUAGE=CNMP1.LANGUAGE
297 AND AU.USER_ID = CNMP1.LEVEL_VALUE
298 AND CNMP1.LEVEL_ID = 10004)
299 )
300 )
301 AND NOT EXISTS (SELECT 1
302 FROM CSM_NEW_MESSAGES_ACC ACC
303 WHERE ACC.MESSAGE_ID = CNMP.MESSAGE_ID
304 AND ACC.USER_ID = AU.USER_ID)
305 UNION ALL
306 --PERZ DOESN'T EXIST
307 SELECT CNM.MESSAGE_ID,
308 0 LEVEL_ID,
309 0 LEVEL_VALUE,
310 CNMT.LANGUAGE,
311 CNMT.MESSAGE_TEXT,
312 CNMT.DESCRIPTION,
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
322 WHERE CNMP.MESSAGE_ID=CNM.MESSAGE_ID
323 AND(
324 CNMP.LEVEL_ID=10001
325 OR
326 (CNMP.LEVEL_ID=10003
327 AND CNMP.LEVEL_VALUE=AU.RESPONSIBILITY_ID)
328 OR
329 (CNMP.LEVEL_ID=10004
330 AND CNMP.LEVEL_VALUE=AU.USER_ID)))
331 AND NOT EXISTS (SELECT 1
332 FROM CSM_NEW_MESSAGES_ACC ACC
333 WHERE ACC.MESSAGE_ID = CNMT.MESSAGE_ID
334 AND ACC.USER_ID = AU.USER_ID);
335
336 CURSOR C_GET_ACCESS_ID IS
337 SELECT CSM_NEW_MESSAGES_ACC_S.NEXTVAL
338 FROM DUAL;
339
340 TYPE con_rec_type IS RECORD
341 (
342 MESSAGE_ID CSM_NEW_MESSAGES.MESSAGE_ID%TYPE,
343 LEVEL_ID CSM_NEW_MESSAGES_PERZ.LEVEL_ID%TYPE,
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
353 INDEX BY BINARY_INTEGER;
354
355 l_tab l_tab_type;
356 l_lastrundate c_LastRundate%ROWTYPE;
357 l_sqlerrno VARCHAR2(20);
358 l_sqlerrmsg VARCHAR2(4000);
359 l_dummy BOOLEAN;
360 l_access_id NUMBER;
361 l_max_update_date DATE;
362 l_checkupdates VARCHAR2(1) := 'N';
363 BEGIN
364
365 CSM_UTIL_PKG.LOG('Entering REFRESH_ACC: ',
366 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
367
368 OPEN c_lastrundate;
369 FETCH c_lastrundate INTO l_lastrundate;
370 CLOSE c_lastrundate;
371
372 CSM_UTIL_PKG.LOG('Got LASTRUNDATE ',
373 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
374
375 --Bug 5409433
376 --DELETE--
377 HANDLE_DELETE(p_status,p_message);
378 COMMIT;
379
380 --update--
381 -- Don't bother to run the update cursor unless the max(last_update_date) is > last_run_date
382 -- Will reduce buffer gets. See bug 5184173
383 -- An index will be created on last_update_date column to avoid FTS.
384 SELECT NVL(MAX(last_update_date), to_date('1', 'J')) INTO l_max_update_date
385 FROM CSM_NEW_MESSAGES_PERZ;
386 -- Find the next max_last_update_date only if the above query shows a lower last_update_date
387 IF(l_max_update_date <= l_lastrundate.last_run_date) THEN
388 SELECT NVL(MAX(last_update_date), to_date('1', 'J')) INTO l_max_update_date
389 FROM CSM_NEW_MESSAGES_TL;
390 IF(l_max_update_date > l_lastrundate.last_run_date) THEN
391 l_checkupdates := 'Y';
392 END IF;
393 ELSE
394 l_checkupdates := 'Y';
395 END IF;
396
397 IF(l_checkupdates = 'Y') THEN
398 OPEN c_update(l_lastrundate.LAST_RUN_DATE);
399 FETCH c_update BULK COLLECT INTO l_tab;
400 CLOSE c_update;
401
402 CSM_UTIL_PKG.LOG('Entering UPDATE to update ' || l_tab.count||' records',
403 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
404
405 FOR I IN 1..l_tab.COUNT LOOP
406 l_dummy := asg_download.mark_dirty(g_new_msg_pubi_name,l_tab(I).ACCESS_ID ,
407 l_tab(I).USER_ID, 'U', sysdate );
408
409 UPDATE CSM_NEW_MESSAGES_ACC
410 SET
411 MESSAGE_TEXT=L_TAB(I).MESSAGE_TEXT,
412 DESCRIPTION=L_TAB(I).DESCRIPTION,
413 LAST_UPDATE_DATE=SYSDATE,
414 LAST_UPDATED_BY=1,
415 LAST_UPDATE_LOGIN=1
416 WHERE ACCESS_ID=l_tab(I).ACCESS_ID;
417 END LOOP;
418
419 COMMIT;
420
421 CSM_UTIL_PKG.LOG('UPDATE Successful ',
422 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
423 l_tab.DELETE;
424 END IF;
425
426 --insert--
427 OPEN c_insert;
428 FETCH c_insert BULK COLLECT INTO l_tab;
429 CLOSE c_insert;
430
431 CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records',
432 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
433
434 FOR I IN 1..l_tab.COUNT
435 LOOP
436 OPEN C_GET_ACCESS_ID;
437 FETCH C_GET_ACCESS_ID INTO l_access_id;
438 CLOSE C_GET_ACCESS_ID;
439
440 INSERT INTO CSM_NEW_MESSAGES_ACC
441 ( ACCESS_ID,
442 MESSAGE_ID,
443 LEVEL_ID,
444 LEVEL_VALUE,
445 LANGUAGE,
446 USER_ID,
447 MESSAGE_TEXT,
448 DESCRIPTION,
449 COUNTER,
450 CREATED_BY,
451 CREATION_DATE,
452 LAST_UPDATED_BY,
453 LAST_UPDATE_DATE,
454 LAST_UPDATE_LOGIN
455 )
456 VALUES
457 ( l_access_id,
458 l_tab(I).MESSAGE_ID,
459 l_tab(I).LEVEL_ID,
460 l_tab(I).LEVEL_VALUE,
461 l_tab(I).LANGUAGE,
462 l_tab(I).USER_ID,
463 l_tab(I).MESSAGE_TEXT,
464 l_tab(I).DESCRIPTION,
465 1,
466 1,
467 SYSDATE,
468 1,
469 SYSDATE,
470 1
471 );
472
473 l_dummy := asg_download.mark_dirty(g_new_msg_pubi_name,l_access_id , l_tab(I).USER_ID, 'I', sysdate );
474
475 END LOOP;
476
477 COMMIT;
478
479 CSM_UTIL_PKG.LOG('INSERTION Successful ',
480 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
481
482 UPDATE JTM_CON_REQUEST_DATA
483 SET LAST_RUN_DATE = SYSDATE
484 WHERE package_name = g_new_msg_pkg_name
485 AND procedure_name = g_new_msg_api_name;
486
487 COMMIT;
488
489 p_status := 'FINE';
490 p_message := 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC executed successfully';
491
492 CSM_UTIL_PKG.LOG('Leaving REFRESH_ACC: ',
493 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 l_sqlerrno := TO_CHAR(SQLCODE);
498 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
499 p_status := 'ERROR';
500 p_message := p_message||': Error in CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
501 ROLLBACK;
502 csm_util_pkg.log('CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
503
504 END REFRESH_ACC;
505
506 --Bug 7239431
507 PROCEDURE REFRESH_USER(p_user_id NUMBER)
508 IS
509
510 --INSERT--
511 CURSOR c_insert(b_user_id NUMBER) IS
512 --PERZ EXISTS
513 SELECT CNM.MESSAGE_ID,
514 CNMP.LEVEL_ID,
515 CNMP.LEVEL_VALUE,
516 CNMP.LANGUAGE,
517 CNMP.MESSAGE_TEXT,
518 CNMP.DESCRIPTION,
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
528 AND ( (CNMP.LEVEL_VALUE = AU.USER_ID
529 AND CNMP.LEVEL_ID = 10004)
530 OR
531 (CNMP.LEVEL_VALUE = AU.RESPONSIBILITY_ID
532 AND CNMP.LEVEL_ID = 10003
533 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1
534 WHERE AU.USER_ID = CNMP1.LEVEL_VALUE AND CNMP1.LEVEL_ID = 10004))
535 OR
536 (CNMP.LEVEL_VALUE=0
537 AND CNMP.LEVEL_ID = 10001
538 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1
539 WHERE CNMP.MESSAGE_ID=CNMP1.MESSAGE_ID
540 AND CNMP.LANGUAGE=CNMP1.LANGUAGE
541 AND AU.RESPONSIBILITY_ID = CNMP1.LEVEL_VALUE
542 AND CNMP1.LEVEL_ID = 10003)
543 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1
544 WHERE CNMP.MESSAGE_ID=CNMP1.MESSAGE_ID
545 AND CNMP.LANGUAGE=CNMP1.LANGUAGE
546 AND AU.USER_ID = CNMP1.LEVEL_VALUE
547 AND CNMP1.LEVEL_ID = 10004)
548 )
549 )
550 UNION ALL
551 --PERZ DOESN'T EXIST
552 SELECT CNM.MESSAGE_ID,
553 0 LEVEL_ID,
554 0 LEVEL_VALUE,
555 CNMT.LANGUAGE,
556 CNMT.MESSAGE_TEXT,
557 CNMT.DESCRIPTION,
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
567 AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP
568 WHERE CNMP.MESSAGE_ID=CNM.MESSAGE_ID
569 AND(
570 CNMP.LEVEL_ID=10001
571 OR
572 (CNMP.LEVEL_ID=10003
573 AND CNMP.LEVEL_VALUE=AU.RESPONSIBILITY_ID)
574 OR
575 (CNMP.LEVEL_ID=10004
576 AND CNMP.LEVEL_VALUE=AU.USER_ID)));
577
578 CURSOR C_GET_ACCESS_ID IS
579 SELECT CSM_NEW_MESSAGES_ACC_S.NEXTVAL
580 FROM DUAL;
581
582 TYPE con_rec_type IS RECORD
583 (
584 MESSAGE_ID CSM_NEW_MESSAGES.MESSAGE_ID%TYPE,
585 LEVEL_ID CSM_NEW_MESSAGES_PERZ.LEVEL_ID%TYPE,
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
595 INDEX BY BINARY_INTEGER;
596
597 l_tab l_tab_type;
598 l_dummy BOOLEAN;
599 l_access_id NUMBER;
600
601 BEGIN
602
603 CSM_UTIL_PKG.LOG('Entering api REFRESH_USER with user id- '||p_user_id,
604 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
605
606 DELETE FROM CSM_NEW_MESSAGES_ACC WHERE USER_ID=p_user_id;
607
608 --insert--
609 OPEN c_insert(p_user_id);
610 FETCH c_insert BULK COLLECT INTO l_tab;
611 CLOSE c_insert;
612
613 CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records for user',
614 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
615
616 FOR I IN 1..l_tab.COUNT
617 LOOP
618 OPEN C_GET_ACCESS_ID;
619 FETCH C_GET_ACCESS_ID INTO l_access_id;
620 CLOSE C_GET_ACCESS_ID;
621
622 INSERT INTO CSM_NEW_MESSAGES_ACC
623 ( ACCESS_ID,
624 MESSAGE_ID,
625 LEVEL_ID,
626 LEVEL_VALUE,
627 LANGUAGE,
628 USER_ID,
629 MESSAGE_TEXT,
630 DESCRIPTION,
631 COUNTER,
632 CREATED_BY,
633 CREATION_DATE,
634 LAST_UPDATED_BY,
635 LAST_UPDATE_DATE,
636 LAST_UPDATE_LOGIN
637 )
638 VALUES
639 ( l_access_id,
640 l_tab(I).MESSAGE_ID,
641 l_tab(I).LEVEL_ID,
642 l_tab(I).LEVEL_VALUE,
643 l_tab(I).LANGUAGE,
644 l_tab(I).USER_ID,
645 l_tab(I).MESSAGE_TEXT,
646 l_tab(I).DESCRIPTION,
647 1,
648 1,
649 SYSDATE,
650 1,
651 SYSDATE,
652 1
653 );
654
655 l_dummy := asg_download.mark_dirty(g_new_msg_pubi_name,l_access_id , l_tab(I).USER_ID, 'I', sysdate );
656
657 END LOOP;
658
659 CSM_UTIL_PKG.LOG('INSERTION Successful ',
660 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
661
662 END REFRESH_USER;
663
664 END CSM_NEW_MESSAGES_EVENT_PKG;