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

Line 1: PACKAGE BODY CSM_NEW_MESSAGES_EVENT_PKG AS

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

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 5: g_new_msg_tl_tab_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_TL';

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:

Line 6: g_new_msg_perz_tab_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_PERZ';

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

Line 7: g_new_msg_table_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES';

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

Line 8: g_new_msg_pubi_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES';

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:

Line 10: g_new_msg_pkg_name CONSTANT VARCHAR2(30) := 'CSM_NEW_MESSAGES_EVENT_PKG';

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)

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 25: FROM CSM_NEW_MESSAGES_PERZ CNMP

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

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 36: FROM CSM_NEW_MESSAGES_PERZ CNMP,ASG_USER AU

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

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 50: FROM CSM_NEW_MESSAGES_PERZ CNMP

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)

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 62: FROM CSM_NEW_MESSAGES_PERZ CNMP,ASG_USER AU

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

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 85: FROM CSM_NEW_MESSAGES_PERZ CNMP,

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

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 103: FROM CSM_NEW_MESSAGES_PERZ CNMP,

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

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 120: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES CNM

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

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 133: MESSAGE_ID CSM_NEW_MESSAGES.MESSAGE_ID%TYPE,

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

Line 154: 'CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE',FND_LOG.LEVEL_PROCEDURE);

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

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 177: 'CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE',FND_LOG.LEVEL_PROCEDURE);

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

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 191: 'CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE',FND_LOG.LEVEL_PROCEDURE);

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

Line 193: p_message :='DELETION in CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE successful';

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

Line 200: p_message := 'Error in CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE: ' || l_sqlerrno || ':' || l_sqlerrmsg;

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:

Line 202: csm_util_pkg.log('CSM_NEW_MESSAGES_EVENT_PKG.HANDLE_DELETE ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);

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

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 227: CSM_NEW_MESSAGES_PERZ CNMP

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

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 243: CSM_NEW_MESSAGES_TL CNMT

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

Line 248: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP,ASG_USER AU

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

Line 273: FROM CSM_NEW_MESSAGES CNM,

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

Line 274: CSM_NEW_MESSAGES_PERZ CNMP,

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

Line 284: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1

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

Line 289: 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)

Line 294: 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)

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 315: FROM CSM_NEW_MESSAGES CNM,

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

Line 316: CSM_NEW_MESSAGES_TL CNMT,

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

Line 321: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP

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

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 342: MESSAGE_ID CSM_NEW_MESSAGES.MESSAGE_ID%TYPE,

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,

Line 343: LEVEL_ID CSM_NEW_MESSAGES_PERZ.LEVEL_ID%TYPE,

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,

Line 344: LEVEL_VALUE CSM_NEW_MESSAGES_PERZ.LEVEL_VALUE%TYPE,

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,

Line 345: LANGUAGE CSM_NEW_MESSAGES_TL.LANGUAGE%TYPE,

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

Line 346: MESSAGE_TEXT CSM_NEW_MESSAGES_TL.MESSAGE_TEXT%TYPE,

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

Line 347: DESCRIPTION CSM_NEW_MESSAGES_TL.DESCRIPTION%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:

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 366: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

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;

Line 373: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

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

Line 385: FROM CSM_NEW_MESSAGES_PERZ;

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;

Line 389: FROM CSM_NEW_MESSAGES_TL;

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

Line 403: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

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

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 422: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

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

Line 432: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

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;

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 480: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

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

Line 490: p_message := 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC executed successfully';

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:

Line 493: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

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

Line 500: p_message := p_message||': Error in CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;

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;

Line 502: csm_util_pkg.log('CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);

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

Line 521: FROM CSM_NEW_MESSAGES CNM,

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

Line 522: CSM_NEW_MESSAGES_PERZ CNMP,

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

Line 533: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP1

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

Line 538: 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)

Line 543: 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)

Line 560: FROM CSM_NEW_MESSAGES CNM,

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

Line 561: CSM_NEW_MESSAGES_TL CNMT,

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

Line 567: AND NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES_PERZ CNMP

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

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 584: MESSAGE_ID CSM_NEW_MESSAGES.MESSAGE_ID%TYPE,

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,

Line 585: LEVEL_ID CSM_NEW_MESSAGES_PERZ.LEVEL_ID%TYPE,

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,

Line 586: LEVEL_VALUE CSM_NEW_MESSAGES_PERZ.LEVEL_VALUE%TYPE,

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,

Line 587: LANGUAGE CSM_NEW_MESSAGES_TL.LANGUAGE%TYPE,

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

Line 588: MESSAGE_TEXT CSM_NEW_MESSAGES_TL.MESSAGE_TEXT%TYPE,

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

Line 589: DESCRIPTION CSM_NEW_MESSAGES_TL.DESCRIPTION%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:

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 604: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);

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

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 614: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);

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;

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,

Line 660: 'CSM_NEW_MESSAGES_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);

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;

Line 664: END CSM_NEW_MESSAGES_EVENT_PKG;

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;