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
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:
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;
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:
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:
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
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
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
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
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: -------------------------------------------------------------------------------
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
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
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
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;
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
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
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:
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:
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
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);
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;
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)
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
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
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
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);
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);
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;
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;
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;