[Home] [Help]
PACKAGE BODY: APPS.CSM_QUERY_EVENT_PKG
Source
1 PACKAGE BODY CSM_QUERY_EVENT_PKG AS
2 /* $Header: csmeqryb.pls 120.7 2009/10/22 03:56:59 trajasek noship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13 g_pub_item_qry VARCHAR2(50) := 'CSM_QUERY';
14 g_pub_item_qvar VARCHAR2(50) := 'CSM_QUERY_VARIABLES';
15 g_pub_item_qval VARCHAR2(50) := 'CSM_QUERY_VARIABLE_VALUES';
16 g_pub_item_qins VARCHAR2(50) := 'CSM_QUERY_INSTANCES';
17 g_pub_item_qres VARCHAR2(50) := 'CSM_QUERY_RESULTS';
18
19 PROCEDURE REFRESH_ACC(p_status OUT NOCOPY VARCHAR2,
20 p_message OUT NOCOPY VARCHAR2)
21 IS
22 PRAGMA AUTONOMOUS_TRANSACTION;
23 TYPE QUERY_LIST IS TABLE OF CSM_QUERY_B.QUERY_ID%TYPE INDEX BY BINARY_INTEGER;
24 TYPE VARIABLE_LIST IS TABLE OF CSM_QUERY_VARIABLES_B.VARIABLE_ID%TYPE INDEX BY BINARY_INTEGER;
25
26 l_run_date DATE;
27 l_sqlerrno VARCHAR2(20);
28 l_sqlerrmsg VARCHAR2(2000);
29 l_mark_dirty boolean;
30 g_pub_item VARCHAR2(30) := 'CSM_QUERY';
31 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
32 l_access_list asg_download.access_list;
33 l_user_list asg_download.user_list;
34 l_query_id_list QUERY_LIST;
35 l_variable_id_list VARIABLE_LIST;
36
37 -- Cursor Declaration
38 CURSOR c_query_ins
39 IS
40 SELECT CSM_QUERY_ACC_S.NEXTVAL,
41 au.USER_ID,
42 b.QUERY_ID
43 FROM CSM_QUERY_B b,
44 ASG_USER au
45 WHERE ( (b.LEVEL_ID = 10003 AND b.LEVEL_VALUE = au.responsibility_id)--Support for Responsiblity
46 OR (b.LEVEL_ID = 10004 AND b.LEVEL_VALUE = au.USER_ID)
47 OR (b.LEVEL_ID = 10001 AND b.LEVEL_VALUE =0) )
48 AND au.USER_ID = au.OWNER_ID
49 AND au.ENABLED= 'Y'
50 AND NVL(b.DELETE_FLAG,'N') = 'N'
51 AND NOT EXISTS
52 (
53 SELECT 1
54 FROM CSM_QUERY_ACC acc
55 WHERE acc.QUERY_ID = b.QUERY_ID
56 AND acc.USER_ID = au.USER_ID
57 );
58
59 CURSOR c_query_var_ins
60 IS
61 SELECT CSM_QUERY_VARIABLES_ACC_S.NEXTVAL,
62 qacc.USER_ID,
63 b.QUERY_ID,
64 b.VARIABLE_ID
65 FROM CSM_QUERY_VARIABLES_B b,
66 CSM_QUERY_ACC qacc
67 WHERE qacc.QUERY_ID = b.QUERY_ID
68 AND NOT EXISTS
69 (
70 SELECT 1
71 FROM CSM_QUERY_VARIABLES_ACC vacc
72 WHERE vacc.QUERY_ID = qacc.QUERY_ID
73 AND vacc.USER_ID = qacc.USER_ID
74 AND b.VARIABLE_ID = vacc.VARIABLE_ID
75 );
76
77
78 --Update Cursor
79 CURSOR c_query_upd(p_lastrundate IN date)
80 IS
81 SELECT acc.ACCESS_ID,
82 acc.USER_ID
83 FROM CSM_QUERY_ACC acc,
84 CSM_QUERY_B b
85 WHERE acc.QUERY_ID = b.QUERY_ID
86 AND b.LAST_UPDATE_DATE >= p_lastrundate;
87
88 CURSOR c_query_var_upd(p_lastrundate IN date)
89 IS
90 SELECT acc.ACCESS_ID,
91 acc.USER_ID
92 FROM CSM_QUERY_VARIABLES_ACC acc,
93 CSM_QUERY_VARIABLES_B b
94 WHERE acc.QUERY_ID = b.QUERY_ID
95 AND acc.VARIABLE_ID = b.VARIABLE_ID
96 AND b.LAST_UPDATE_DATE >= p_lastrundate;
97
98
99 --Delete Cursors
100 CURSOR c_query_del
101 IS
102 SELECT acc.ACCESS_ID,
103 acc.USER_ID
104 FROM CSM_QUERY_ACC acc
105 WHERE EXISTS
106 (SELECT 1
107 FROM CSM_QUERY_B b
108 WHERE b.QUERY_ID = acc.QUERY_ID
109 AND NVL(b.DELETE_FLAG,'N') ='Y'
110 );
111 --Delete Query Variables from the ACC
112 CURSOR c_query_variables_del
113 IS
114 SELECT acc.ACCESS_ID,
115 acc.USER_ID
116 FROM CSM_QUERY_VARIABLES_ACC acc,
117 CSM_QUERY_B b
118 WHERE b.QUERY_ID = acc.QUERY_ID
119 AND NVL(b.DELETE_FLAG,'N') ='Y';
120
121 --Delete Query Instances from the ACC
122 CURSOR c_query_instances_del
123 IS
124 SELECT acc.ACCESS_ID,
125 acc.USER_ID
126 FROM CSM_QUERY_INSTANCES_ACC acc,
127 CSM_QUERY_B b
128 WHERE b.QUERY_ID = acc.QUERY_ID
129 AND ( NVL(b.DELETE_FLAG,'N') ='Y'
130 OR ( UPPER(b.RETENTION_POLICY) = 'AUTOMATIC'
131 AND acc.LAST_UPDATE_DATE < (SYSDATE-nvl(b.RETENTION_DAYS,1000))
132 )) ;
133
134 --Delete Query Variable Values from the ACC
135 CURSOR c_query_variables_val_del
136 IS
137 SELECT acc.ACCESS_ID,
138 acc.USER_ID
139 FROM CSM_QUERY_VARIABLE_VALUES_ACC acc
140 WHERE NOT EXISTS
141 (SELECT 1 FROM CSM_QUERY_INSTANCES_ACC iacc
142 WHERE iacc.USER_ID = acc.USER_ID
143 AND iacc.INSTANCE_ID = acc.INSTANCE_ID
144 AND iacc.QUERY_ID = acc.QUERY_ID);
145
146 --Delete Query Results from the ACC
147 CURSOR c_query_results_del
148 IS
149 SELECT acc.ACCESS_ID,
150 acc.USER_ID
151 FROM CSM_QUERY_RESULTS_ACC acc
152 WHERE NOT EXISTS
153 (SELECT 1 FROM CSM_QUERY_INSTANCES_ACC iacc
154 WHERE iacc.USER_ID = acc.USER_ID
155 AND iacc.INSTANCE_ID = acc.INSTANCE_ID
156 AND iacc.QUERY_ID = acc.QUERY_ID);
157
158 CURSOR l_last_run_date_csr
159 IS
160 SELECT nvl(last_run_date, (sysdate - 365*50))
161 FROM jtm_con_request_data
162 WHERE package_name = 'CSM_QUERY_EVENT_PKG'
163 AND procedure_name = 'REFRESH_ACC';
164
165 BEGIN
166
167 CSM_UTIL_PKG.LOG('Entering CSM_QUERY_EVENT_PKG.REFRESH_ACC ',
168 'CSM_QUERY_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_PROCEDURE);
169
170 -- data program is run
171 l_run_date := SYSDATE;
172
173 -- get last conc program update date
174 OPEN l_last_run_date_csr;
175 FETCH l_last_run_date_csr INTO l_prog_update_date;
176 CLOSE l_last_run_date_csr;
177
178 CSM_UTIL_PKG.LOG('Entering deletes ', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_STATEMENT);
179
180 -- process all DELETES
181 -------------------------------------------------------------------------------
182
183 -- process Query Deletion from Acc
184 OPEN c_query_del;
185 LOOP
186 IF l_access_list.count >0 THEN
187 l_access_list.delete;
188 END IF;
189 IF l_user_list.count >0 THEN
190 l_user_list.delete;
191 END IF;
192
193 FETCH c_query_del BULK COLLECT INTO l_access_list,l_user_list LIMIT 1000;
194 EXIT WHEN l_access_list.count = 0;
195
196 FOR i IN 1..l_access_list.count LOOP
197 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qry,
198 p_accessid => l_access_list(i),
199 p_userid => l_user_list(i),
200 p_dml => asg_download.del,
201 p_timestamp => l_run_date);
202
203 END LOOP;
204 FORALL i IN 1..l_access_list.count
205 DELETE FROM csm_query_acc WHERE access_id = l_access_list(i);
206
207 COMMIT;
208
209 END LOOP;
210 CLOSE c_query_del;
211 CSM_UTIL_PKG.LOG('Completed Query Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
212
213 -- process Query Variables Deletion from ACC table
214 OPEN c_query_variables_del;
215 LOOP
216 IF l_access_list.count >0 THEN
217 l_access_list.delete;
218 END IF;
219 IF l_user_list.count >0 THEN
220 l_user_list.delete;
221 END IF;
222
223 FETCH c_query_variables_del BULK COLLECT INTO l_access_list,l_user_list LIMIT 1000;
224 EXIT WHEN l_access_list.count = 0;
225
226 FOR i IN 1..l_access_list.count LOOP
227 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qvar,
228 p_accessid => l_access_list(i),
229 p_userid => l_user_list(i),
230 p_dml => asg_download.del,
231 p_timestamp => l_run_date);
232
233 END LOOP;
234 FORALL i IN 1..l_access_list.count
235 DELETE FROM csm_query_variables_acc WHERE access_id = l_access_list(i);
236
237 COMMIT;
238
239 END LOOP;
240 CLOSE c_query_variables_del;
241 CSM_UTIL_PKG.LOG('Completed Query Variables Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
242
243 -- process Query Instances Deletion from ACC table
244 OPEN c_query_instances_del;
245 LOOP
246 IF l_access_list.count >0 THEN
247 l_access_list.delete;
248 END IF;
249 IF l_user_list.count >0 THEN
250 l_user_list.delete;
251 END IF;
252
253 FETCH c_query_instances_del BULK COLLECT INTO l_access_list,l_user_list LIMIT 1000;
254 EXIT WHEN l_access_list.count = 0;
255
256 FOR i IN 1..l_access_list.count LOOP
257 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qins,
258 p_accessid => l_access_list(i),
259 p_userid => l_user_list(i),
260 p_dml => asg_download.del,
261 p_timestamp => l_run_date);
262
263 END LOOP;
264 FORALL i IN 1..l_access_list.count
265 DELETE FROM CSM_QUERY_INSTANCES_ACC WHERE access_id = l_access_list(i);
266
267 COMMIT;
268
269 END LOOP;
270 CLOSE c_query_instances_del;
271 CSM_UTIL_PKG.LOG('Completed Query Instances Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
272
273 -- process Query Variables values Deletion from ACC table
274 OPEN c_query_variables_val_del;
275 LOOP
276 IF l_access_list.count >0 THEN
277 l_access_list.delete;
278 END IF;
279 IF l_user_list.count >0 THEN
280 l_user_list.delete;
281 END IF;
282
283 FETCH c_query_variables_val_del BULK COLLECT INTO l_access_list,l_user_list LIMIT 1000;
284 EXIT WHEN l_access_list.count = 0;
285
286 FOR i IN 1..l_access_list.count LOOP
287 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qval,
288 p_accessid => l_access_list(i),
289 p_userid => l_user_list(i),
290 p_dml => asg_download.del,
291 p_timestamp => l_run_date);
292
293 END LOOP;
294 FORALL i IN 1..l_access_list.count
295 DELETE FROM CSM_QUERY_VARIABLE_VALUES_ACC WHERE access_id = l_access_list(i);
296
297 COMMIT;
298
299 END LOOP;
300 CLOSE c_query_variables_val_del;
301 CSM_UTIL_PKG.LOG('Completed Query Variable Values Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
302
303 -- process Query Results Deletion from ACC table
304 OPEN c_query_results_del;
305 LOOP
306 IF l_access_list.count >0 THEN
307 l_access_list.delete;
308 END IF;
309 IF l_user_list.count >0 THEN
310 l_user_list.delete;
311 END IF;
312
313 FETCH c_query_results_del BULK COLLECT INTO l_access_list,l_user_list LIMIT 1000;
314 EXIT WHEN l_access_list.count = 0;
315
316 FOR i IN 1..l_access_list.count LOOP
317 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qres,
318 p_accessid => l_access_list(i),
319 p_userid => l_user_list(i),
320 p_dml => asg_download.del,
321 p_timestamp => l_run_date);
322
323 END LOOP;
324 FORALL i IN 1..l_access_list.count
325 DELETE FROM CSM_QUERY_RESULTS_ACC WHERE access_id = l_access_list(i);
326
327 COMMIT;
328
329 END LOOP;
330 CLOSE c_query_results_del;
331 CSM_UTIL_PKG.LOG('Completed Query Results Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
332
333 CSM_UTIL_PKG.LOG('Leaving deletes and entering updates', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
334 -- process all updates
335 -------------------------------------------------------------------------------
336
337 --PRocess Query updates
338 OPEN c_query_upd(l_prog_update_date);
339 LOOP
340 IF l_access_list.count >0 THEN
341 l_access_list.delete;
342 END IF;
343 IF l_user_list.count >0 THEN
344 l_user_list.delete;
345 END IF;
346
347 FETCH c_query_upd BULK COLLECT INTO l_access_list,l_user_list LIMIT 1000;
348 EXIT WHEN l_access_list.count = 0;
349
350 FOR i IN 1..l_access_list.count LOOP
351 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qry,
352 p_accessid => l_access_list(i),
353 p_userid => l_user_list(i),
354 p_dml => asg_download.upd,
355 p_timestamp => l_run_date);
356
357 END LOOP;
358 COMMIT;
359
360 END LOOP;
361 CLOSE c_query_upd;
362 CSM_UTIL_PKG.LOG('Completed Query Updates', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
363
364 --PRocess Query updates
365 OPEN c_query_var_upd(l_prog_update_date);
366 LOOP
367 IF l_access_list.count >0 THEN
368 l_access_list.delete;
369 END IF;
370 IF l_user_list.count >0 THEN
371 l_user_list.delete;
372 END IF;
373
374 FETCH c_query_var_upd BULK COLLECT INTO l_access_list,l_user_list LIMIT 1000;
375 EXIT WHEN l_access_list.count = 0;
376
377 FOR i IN 1..l_access_list.count LOOP
378 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qvar,
379 p_accessid => l_access_list(i),
380 p_userid => l_user_list(i),
381 p_dml => asg_download.upd,
382 p_timestamp => l_run_date);
383
384 END LOOP;
385 COMMIT;
386
387 END LOOP;
388 CLOSE c_query_var_upd;
389 CSM_UTIL_PKG.LOG('Completed Query Variable Updates', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
390 CSM_UTIL_PKG.LOG('Leaving updates and entering inserts', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
391 --process all inserts
392 -------------------------------------------------------------------------------
393 --Process query inserts
394 OPEN c_query_ins;
395 LOOP
396 IF l_access_list.count >0 THEN
397 l_access_list.delete;
398 END IF;
399 IF l_user_list.count >0 THEN
400 l_user_list.delete;
401 END IF;
402 IF l_query_id_list.count >0 THEN
403 l_query_id_list.delete;
404 END IF;
405 FETCH c_query_ins BULK COLLECT INTO l_access_list,l_user_list,l_query_id_list LIMIT 1000;
406 EXIT WHEN l_access_list.count = 0;
407
408 FOR i IN 1..l_access_list.count LOOP
409 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qry,
410 p_accessid => l_access_list(i),
411 p_userid => l_user_list(i),
412 p_dml => asg_download.ins,
413 p_timestamp => l_run_date);
414
415 END LOOP;
416 FORALL i IN 1..l_access_list.count
417 INSERT INTO CSM_QUERY_ACC
418 (ACCESS_ID,
419 USER_ID,
420 QUERY_ID,
421 CREATED_BY, CREATION_DATE,
422 LAST_UPDATED_BY,
423 LAST_UPDATE_DATE,
424 LAST_UPDATE_LOGIN)
425 VALUES
426 (l_access_list(i),
427 l_user_list(i),
428 l_query_id_list(i),
429 fnd_global.user_id,
430 l_run_date,
431 fnd_global.user_id,
432 l_run_date,
433 fnd_global.login_id);
434
435 COMMIT;
436
437 END LOOP;
438 CLOSE c_query_ins;
439 CSM_UTIL_PKG.LOG('Completed Query Inserts', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
440 --Process query variable inserts
441 OPEN c_query_var_ins;
442 LOOP
443 IF l_access_list.count >0 THEN
444 l_access_list.delete;
445 END IF;
446 IF l_user_list.count >0 THEN
447 l_user_list.delete;
448 END IF;
449 IF l_query_id_list.count >0 THEN
450 l_query_id_list.delete;
451 END IF;
452 IF l_variable_id_list.count >0 THEN
453 l_variable_id_list.delete;
454 END IF;
455
456
457 FETCH c_query_var_ins BULK COLLECT INTO l_access_list,l_user_list,l_query_id_list,l_variable_id_list LIMIT 1000;
458 EXIT WHEN l_access_list.count = 0;
459
460 FOR i IN 1..l_access_list.count LOOP
461 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qvar,
462 p_accessid => l_access_list(i),
463 p_userid => l_user_list(i),
464 p_dml => asg_download.ins,
465 p_timestamp => l_run_date);
466
467 END LOOP;
468 FORALL i IN 1..l_access_list.count
469 INSERT INTO CSM_QUERY_VARIABLES_ACC
470 (ACCESS_ID,
471 USER_ID,
472 QUERY_ID,
473 VARIABLE_ID,
474 CREATED_BY,
475 CREATION_DATE,
476 LAST_UPDATED_BY,
477 LAST_UPDATE_DATE,
478 LAST_UPDATE_LOGIN,
479 GEN_PK)
480 VALUES
481 (l_access_list(i),
482 l_user_list(i),
483 l_query_id_list(i),
484 l_variable_id_list(i),
485 fnd_global.user_id,
486 l_run_date,
487 fnd_global.user_id,
488 l_run_date,
489 fnd_global.login_id,
490 l_access_list(i));
491
492 COMMIT;
493
494 END LOOP;
495 CLOSE c_query_var_ins;
496 CSM_UTIL_PKG.LOG('Completed Query Variable Inserts', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
497
498 --Delete Saved Queries that are deleted as part of Parent Query
499 DELETE FROM CSM_QUERY_B
500 WHERE LEVEL_ID =10004
501 AND SAVED_QUERY ='Y'
502 AND DELETE_FLAG ='Y';
503
504 -- update last_run_date
505 UPDATE jtm_con_request_data
506 SET last_run_date = l_run_date
507 WHERE package_name = 'CSM_QUERY_EVENT_PKG'
508 AND procedure_name = 'REFRESH_ACC';
509
510 COMMIT;
511
512 p_status := 'FINE';
513 p_message := 'CSM_QUERY_EVENT_PKG.REFRESH_ACC Executed successfully';
514
515 CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_EVENT_PKG.REFRESH_ACC ',
516 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
517
518 EXCEPTION
519 WHEN others THEN
520 l_sqlerrno := to_char(SQLCODE);
521 l_sqlerrmsg := substr(SQLERRM, 1,2000);
522 p_status := 'ERROR';
523 p_message := 'Error in CSM_QUERY_EVENT_PKG.REFRESH_ACC :' || l_sqlerrno || ':' || l_sqlerrmsg;
524 ROLLBACK;
525 CSM_UTIL_PKG.LOG('Exception in CSM_QUERY_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
526 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
527 END REFRESH_ACC;
528
529 PROCEDURE REFRESH_USER(p_user_id NUMBER)
530 IS
531 TYPE QUERY_LIST IS TABLE OF CSM_QUERY_B.QUERY_ID%TYPE INDEX BY BINARY_INTEGER;
532 TYPE VARIABLE_LIST IS TABLE OF CSM_QUERY_VARIABLES_B.VARIABLE_ID%TYPE INDEX BY BINARY_INTEGER;
533 l_sqlerrno VARCHAR2(20);
534 l_sqlerrmsg VARCHAR2(2000);
535 l_mark_dirty boolean;
536 l_access_list asg_download.access_list;
537 l_user_list asg_download.user_list;
538 l_query_id_list QUERY_LIST;
539 l_variable_id_list VARIABLE_LIST;
540
541 -- Cursor Declaration
542 CURSOR c_query_ins (c_user_id NUMBER)
543 IS
544 SELECT CSM_QUERY_ACC_S.NEXTVAL,
545 au.USER_ID,
546 b.QUERY_ID
550 OR (b.LEVEL_ID = 10004 AND b.LEVEL_VALUE = au.USER_ID)--Saved Query
547 FROM CSM_QUERY_B b,
548 ASG_USER au
549 WHERE ( (b.LEVEL_ID = 10003 AND b.LEVEL_VALUE = au.responsibility_id)--Support for Responsiblity
551 OR (b.LEVEL_ID = 10001 AND b.LEVEL_VALUE =0) )--Site level
552 AND au.USER_ID = c_user_id
553 AND au.USER_ID = au.OWNER_ID
554 AND NVL(b.DELETE_FLAG,'N') = 'N'
555 AND NOT EXISTS
556 (
557 SELECT 1
558 FROM CSM_QUERY_ACC acc
559 WHERE acc.QUERY_ID = b.QUERY_ID
560 AND acc.USER_ID = au.user_ID
561 );
562
563 CURSOR c_query_var_ins(c_user_id NUMBER)
564 IS
565 SELECT CSM_QUERY_VARIABLES_ACC_S.NEXTVAL,
566 qacc.USER_ID,
567 b.QUERY_ID,
568 b.VARIABLE_ID
569 FROM CSM_QUERY_VARIABLES_B b,
570 CSM_QUERY_ACC qacc
571 WHERE qacc.QUERY_ID = b.QUERY_ID
572 AND qacc.USER_ID = c_user_id
573 AND NOT EXISTS
574 (
575 SELECT 1
576 FROM CSM_QUERY_VARIABLES_ACC vacc
577 WHERE vacc.QUERY_ID = qacc.QUERY_ID
578 AND vacc.USER_ID = qacc.USER_ID
579 AND b.VARIABLE_ID = vacc.VARIABLE_ID
580 );
581 BEGIN
582
583 CSM_UTIL_PKG.LOG('Entering CSM_QUERY_EVENT_PKG.REFRESH_ACC For User id :'|| p_user_id,
584 'CSM_QUERY_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_PROCEDURE);
585
586 OPEN c_query_ins(p_user_id);
587 LOOP
588 IF l_access_list.count >0 THEN
589 l_access_list.delete;
590 END IF;
591 IF l_user_list.count >0 THEN
592 l_user_list.delete;
593 END IF;
594 IF l_query_id_list.count >0 THEN
595 l_query_id_list.delete;
596 END IF;
597
598 FETCH c_query_ins BULK COLLECT INTO l_access_list,l_user_list,l_query_id_list LIMIT 100;
599 EXIT WHEN l_access_list.count = 0;
600
601 FORALL i IN 1..l_access_list.count
602 INSERT INTO CSM_QUERY_ACC
603 (ACCESS_ID,
604 USER_ID,
605 QUERY_ID,
606 CREATED_BY, CREATION_DATE,
607 LAST_UPDATED_BY,
608 LAST_UPDATE_DATE,
609 LAST_UPDATE_LOGIN)
610 VALUES
611 (l_access_list(i),
612 l_user_list(i),
613 l_query_id_list(i),
614 fnd_global.user_id,
615 sysdate,
616 fnd_global.user_id,
617 sysdate,
618 fnd_global.login_id);
619
620 FOR i IN 1..l_access_list.count LOOP
621 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qry,
622 p_accessid => l_access_list(i),
623 p_userid => l_user_list(i),
624 p_dml => asg_download.ins,
625 p_timestamp => sysdate);
626
627 END LOOP;
628 END LOOP;
629 CLOSE c_query_ins;
630 CSM_UTIL_PKG.LOG('Completed Query Inserts for User id :'|| p_user_id, 'CSM_QUERY_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
631
632
633 --Process query variable inserts
634 OPEN c_query_var_ins(p_user_id);
635 LOOP
636 IF l_access_list.count >0 THEN
637 l_access_list.delete;
638 END IF;
639 IF l_user_list.count >0 THEN
640 l_user_list.delete;
641 END IF;
642 IF l_query_id_list.count >0 THEN
643 l_query_id_list.delete;
644 END IF;
645 IF l_variable_id_list.count >0 THEN
646 l_variable_id_list.delete;
647 END IF;
648
649
650 FETCH c_query_var_ins BULK COLLECT INTO l_access_list,l_user_list,l_query_id_list,l_variable_id_list LIMIT 100;
651 EXIT WHEN l_access_list.count = 0;
652
653 FORALL i IN 1..l_access_list.count
654 INSERT INTO CSM_QUERY_VARIABLES_ACC
655 (ACCESS_ID,
656 USER_ID,
657 QUERY_ID,
658 VARIABLE_ID,
659 CREATED_BY,
660 CREATION_DATE,
661 LAST_UPDATED_BY,
662 LAST_UPDATE_DATE,
663 LAST_UPDATE_LOGIN,
664 GEN_PK)
665 VALUES
666 (l_access_list(i),
667 l_user_list(i),
668 l_query_id_list(i),
669 l_variable_id_list(i),
670 fnd_global.user_id,
671 sysdate,
672 fnd_global.user_id,
673 sysdate,
674 fnd_global.login_id,
675 l_access_list(i));
676
677 FOR i IN 1..l_access_list.count LOOP
678 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => g_pub_item_qvar,
679 p_accessid => l_access_list(i),
680 p_userid => l_user_list(i),
681 p_dml => asg_download.ins,
682 p_timestamp => sysdate);
683
684 END LOOP;
685
686 END LOOP;
687 CLOSE c_query_var_ins;
688 CSM_UTIL_PKG.LOG('Completed Query Variable Inserts for User id: '|| p_user_id, 'CSM_QUERY_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
689
690 EXCEPTION
691 WHEN others THEN
692 l_sqlerrno := to_char(SQLCODE);
693 l_sqlerrmsg := substr(SQLERRM, 1,2000);
694 RAISE;
695 CSM_UTIL_PKG.LOG('Exception in CSM_QUERY_EVENT_PKG.REFRESH_USER: ' || l_sqlerrno || ':' || l_sqlerrmsg,
696 'CSM_QUERY_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_EXCEPTION);
697 END REFRESH_USER;
698
699 END CSM_QUERY_EVENT_PKG;