[Home] [Help]
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);
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);
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
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;
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);
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 );
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--
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;
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
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);
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--
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;
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;