eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSM_NEW_MESSAGES_EVENT_PKG dependencies on CSM_NEW_MESSAGES_ACC

Line 4: g_new_msg_acc_tab_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_ACC';

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';

Line 21: FROM CSM_NEW_MESSAGES_ACC CNMA

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

Line 33: FROM CSM_NEW_MESSAGES_ACC CNMA

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

Line 46: FROM CSM_NEW_MESSAGES_ACC CNMA

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

Line 59: FROM CSM_NEW_MESSAGES_ACC CNMA

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

Line 81: FROM CSM_NEW_MESSAGES_ACC CNMA

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,

Line 100: FROM CSM_NEW_MESSAGES_ACC CNMA

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

Line 115: SELECT /+index (cnma csm_new_messages_acc_u2) /

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

Line 118: FROM CSM_NEW_MESSAGES_ACC CNMA

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:

Line 125: SELECT /+index (cnma csm_new_messages_acc_u2) /

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;

Line 127: FROM CSM_NEW_MESSAGES_ACC CNMA

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

Line 164: DELETE FROM CSM_NEW_MESSAGES_ACC WHERE ACCESS_ID=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:

Line 187: DELETE FROM CSM_NEW_MESSAGES_ACC WHERE ACCESS_ID=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);

Line 226: FROM CSM_NEW_MESSAGES_ACC CNMA,

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

Line 242: FROM CSM_NEW_MESSAGES_ACC CNMA,

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

Line 302: FROM CSM_NEW_MESSAGES_ACC ACC

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

Line 332: FROM CSM_NEW_MESSAGES_ACC ACC

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

Line 337: SELECT CSM_NEW_MESSAGES_ACC_S.NEXTVAL

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: (

Line 349: ACCESS_ID CSM_NEW_MESSAGES_ACC.ACCESS_ID%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;

Line 409: UPDATE CSM_NEW_MESSAGES_ACC

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,

Line 440: INSERT INTO CSM_NEW_MESSAGES_ACC

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,

Line 579: SELECT CSM_NEW_MESSAGES_ACC_S.NEXTVAL

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: (

Line 591: ACCESS_ID CSM_NEW_MESSAGES_ACC.ACCESS_ID%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;

Line 606: DELETE FROM CSM_NEW_MESSAGES_ACC WHERE USER_ID=p_user_id;

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;

Line 622: INSERT INTO CSM_NEW_MESSAGES_ACC

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,