[Home] [Help]
PACKAGE BODY: APPS.IBE_WF_NOTIF_MSG_MAPS_PVT
Source
1 package body IBE_WF_NOTIF_MSG_MAPS_PVT as
2 /* $Header: IBEVWNMB.pls 120.2 2005/06/15 03:46:56 appldev $ */
3
4 --g_debug boolean := TRUE;
5 g_debug boolean := FALSE;
6 procedure debug(p_msg VARCHAR2) IS
7 l_debug VARCHAR2(1);
8
9 BEGIN
10 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
11
12 if( g_debug = TRUE ) then
13 --dbms_output.put_line(p_msg);
14 IF (l_debug = 'Y') THEN
15 IBE_UTIL.debug(p_msg);
16 END IF;
17 end if;
18 end;
19 procedure INSERT_ROW (
20 X_ROWID in out NOCOPY VARCHAR2,
21 P_NOTIF_MSG_MAP_ID in NUMBER,
22 P_NOTIF_SETUP_ID in NUMBER,
23 P_NOTIFICATION_NAME in VARCHAR2,
24 P_USER_TYPE in VARCHAR2,
25 P_ENABLED_FLAG in VARCHAR2,
26 P_DEFAULT_MSG_MAP_FLAG in VARCHAR2,
27 P_OBJECT_VERSION_NUMBER in NUMBER,
28 P_ALL_ORG_FLAG in VARCHAR2,
29 P_ALL_MSITE_FLAG in VARCHAR2,
30 P_ALL_USER_TYPE_FLAG in VARCHAR2,
31 P_MSITE_ID in NUMBER,
32 P_ORG_ID IN NUMBER,
33 P_MESSAGE_NAME in VARCHAR2,
34 P_CREATION_DATE in DATE,
35 P_CREATED_BY in NUMBER,
36 P_LAST_UPDATE_DATE in DATE,
37 P_LAST_UPDATED_BY in NUMBER,
38 P_LAST_UPDATE_LOGIN in NUMBER
39 ) is
40 cursor C is select ROWID from IBE_WF_NOTIF_MSG_MAPS
41 where NOTIF_MSG_MAP_ID = P_NOTIF_MSG_MAP_ID
42 and NOTIF_SETUP_ID = P_NOTIF_SETUP_ID
43 ;
44 begin
45 insert into IBE_WF_NOTIF_MSG_MAPS (
46 NOTIFICATION_NAME,
47 USER_TYPE,
48 MESSAGE_NAME,
49 ENABLED_FLAG,
50 DEFAULT_MSG_MAP_FLAG,
51 NOTIF_MSG_MAP_ID,
52 OBJECT_VERSION_NUMBER,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 NOTIF_SETUP_ID,
56 LAST_UPDATED_BY,
57 ALL_ORG_FLAG,
58 ALL_MSITE_FLAG,
59 ALL_USER_TYPE_FLAG,
60 MSITE_ID,
61 ORG_ID,
62 CREATION_DATE,
63 CREATED_BY
64 ) select
65 P_NOTIFICATION_NAME,
66 P_USER_TYPE,
67 P_MESSAGE_NAME,
68 P_ENABLED_FLAG,
69 P_DEFAULT_MSG_MAP_FLAG,
70 P_NOTIF_MSG_MAP_ID,
71 P_OBJECT_VERSION_NUMBER,
72 P_LAST_UPDATE_DATE,
73 P_LAST_UPDATE_LOGIN,
74 P_NOTIF_SETUP_ID,
75 P_LAST_UPDATED_BY,
76 P_ALL_ORG_FLAG,
77 P_ALL_MSITE_FLAG,
78 P_ALL_USER_TYPE_FLAG,
79 P_MSITE_ID,
80 P_ORG_ID,
81 P_CREATION_DATE,
82 P_CREATED_BY
83 from DUAL
84 where not exists
85 (select NULL
86 from IBE_WF_NOTIF_MSG_MAPS T
87 where T.NOTIF_MSG_MAP_ID = P_NOTIF_MSG_MAP_ID
88 and T.NOTIF_SETUP_ID = P_NOTIF_SETUP_ID);
89
90 open c;
91 fetch c into x_ROWID;
92 if (c%notfound) then
93 close c;
94 raise no_data_found;
95 end if;
96 close c;
97
98 end INSERT_ROW;
99
100 procedure LOCK_ROW (
101 P_NOTIF_MSG_MAP_ID in NUMBER,
102 P_NOTIF_SETUP_ID in NUMBER,
103 P_NOTIFICATION_NAME in VARCHAR2,
104 P_USER_TYPE in VARCHAR2,
105 P_ENABLED_FLAG in VARCHAR2,
106 P_DEFAULT_MSG_MAP_FLAG in VARCHAR2,
107 P_OBJECT_VERSION_NUMBER in NUMBER,
108 P_ALL_ORG_FLAG in VARCHAR2,
109 P_ALL_MSITE_FLAG in VARCHAR2,
110 P_ALL_USER_TYPE_FLAG in VARCHAR2,
111 P_MSITE_ID in NUMBER,
112 P_ORG_ID IN NUMBER,
113 P_MESSAGE_NAME in VARCHAR2
114 ) is
115 cursor c1 is select
116 NOTIFICATION_NAME,
117 USER_TYPE,
118 ENABLED_FLAG,
119 DEFAULT_MSG_MAP_FLAG,
120 OBJECT_VERSION_NUMBER,
121 ALL_ORG_FLAG,
122 ALL_MSITE_FLAG,
123 ALL_USER_TYPE_FLAG,
124 MSITE_ID,
125 ORG_ID,
126 MESSAGE_NAME
127 from IBE_WF_NOTIF_MSG_MAPS
128 where NOTIF_MSG_MAP_ID = P_NOTIF_MSG_MAP_ID
129 and NOTIF_SETUP_ID = P_NOTIF_SETUP_ID
130 for update of NOTIF_MSG_MAP_ID nowait;
131 begin
132 for tlinfo in c1 loop
133 if ( ((tlinfo.MESSAGE_NAME = P_MESSAGE_NAME)
134 OR ((tlinfo.MESSAGE_NAME is null) AND (P_MESSAGE_NAME is null)))
135 AND (tlinfo.NOTIFICATION_NAME = P_NOTIFICATION_NAME)
136 AND ((tlinfo.USER_TYPE = P_USER_TYPE)
137 OR ((tlinfo.USER_TYPE is null) AND (P_USER_TYPE is null)))
138 AND ((tlinfo.ENABLED_FLAG = P_ENABLED_FLAG)
139 OR ((tlinfo.ENABLED_FLAG is null) AND (P_ENABLED_FLAG is null)))
140 AND ((tlinfo.DEFAULT_MSG_MAP_FLAG = P_DEFAULT_MSG_MAP_FLAG)
141 OR ((tlinfo.DEFAULT_MSG_MAP_FLAG is null) AND (P_DEFAULT_MSG_MAP_FLAG is null)))
142 AND (tlinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
143 AND ((tlinfo.ALL_ORG_FLAG = P_ALL_ORG_FLAG)
144 OR ((tlinfo.ALL_ORG_FLAG is null) AND (P_ALL_ORG_FLAG is null)))
145 AND ((tlinfo.ALL_MSITE_FLAG = P_ALL_MSITE_FLAG)
146 OR ((tlinfo.ALL_MSITE_FLAG is null) AND (P_ALL_MSITE_FLAG is null)))
147 AND ((tlinfo.ALL_USER_TYPE_FLAG = P_ALL_USER_TYPE_FLAG)
148 OR ((tlinfo.ALL_USER_TYPE_FLAG is null) AND (P_ALL_USER_TYPE_FLAG is null)))
149 AND ((tlinfo.MSITE_ID = P_MSITE_ID)
150 OR ((tlinfo.MSITE_ID is null) AND (P_MSITE_ID is null)))
151 ) then
152 null;
153 else
154 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
155 app_exception.raise_exception;
156 end if;
157 end loop;
158 return;
159 end LOCK_ROW;
160
161 procedure UPDATE_ROW (
162 P_NOTIF_MSG_MAP_ID in NUMBER,
163 P_NOTIF_SETUP_ID in NUMBER,
164 P_NOTIFICATION_NAME in VARCHAR2,
165 P_USER_TYPE in VARCHAR2,
166 P_ENABLED_FLAG in VARCHAR2,
167 P_DEFAULT_MSG_MAP_FLAG in VARCHAR2,
168 P_OBJECT_VERSION_NUMBER in NUMBER,
169 P_ALL_ORG_FLAG in VARCHAR2,
170 P_ALL_MSITE_FLAG in VARCHAR2,
171 P_ALL_USER_TYPE_FLAG in VARCHAR2,
172 P_MSITE_ID in NUMBER,
173 P_ORG_ID in NUMBER,
174 P_MESSAGE_NAME in VARCHAR2,
175 P_LAST_UPDATE_DATE in DATE,
176 P_LAST_UPDATED_BY in NUMBER,
177 P_LAST_UPDATE_LOGIN in NUMBER
178 ) is
179 begin
180 debug('update_row 1');
181 debug('update_row 2 ' || p_notification_name);
182 debug('update_row 3 ' || p_notif_setup_id);
183 debug('update_row 4 ' || p_notif_msg_map_id);
184 update IBE_WF_NOTIF_MSG_MAPS set
185 NOTIF_SETUP_ID = P_NOTIF_SETUP_ID,
186 NOTIFICATION_NAME = P_NOTIFICATION_NAME,
187 USER_TYPE = P_USER_TYPE,
188 ENABLED_FLAG = P_ENABLED_FLAG,
189 DEFAULT_MSG_MAP_FLAG = P_DEFAULT_MSG_MAP_FLAG,
190 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
191 ALL_ORG_FLAG = P_ALL_ORG_FLAG,
192 ALL_MSITE_FLAG = P_ALL_MSITE_FLAG,
193 ALL_USER_TYPE_FLAG = P_ALL_USER_TYPE_FLAG,
194 MSITE_ID = P_MSITE_ID,
195 MESSAGE_NAME = P_MESSAGE_NAME,
196 ORG_ID = P_ORG_ID,
197 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
198 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
200 where NOTIF_MSG_MAP_ID = P_NOTIF_MSG_MAP_ID;
201 --bug 2212390 and NOTIF_SETUP_ID = P_NOTIF_SETUP_ID;
202 --And last_updated_by = p_last_updated_by;
203
204 debug('update_row 5 ' || p_notif_msg_map_id);
205 if (sql%notfound) then
206 debug('update_row 6 ' || p_notif_msg_map_id);
207 raise no_data_found;
208 end if;
209 debug('update_row 7 ' || p_notif_msg_map_id);
210 /*EXCEPTION
211 when NO_DATA_FOUND THEN
212 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_MAP_NOT_FOUND');
213 FND_MESSAGE.SET_TOKEN('ID', p_notif_msg_map_id);
214 FND_MSG_PUB.ADD;*/
215 /* added by abhandar 07/21/03*/
216 EXCEPTION
217 WHEN DUP_VAL_ON_INDEX THEN
218 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_MAP_DUPINDEX');
219 FND_MESSAGE.SET_TOKEN('ID', p_notif_msg_map_id);
220 FND_MSG_PUB.ADD;
221
222 end UPDATE_ROW;
223
224 procedure DELETE_ROW (
225 P_NOTIF_MSG_MAP_ID in NUMBER,
226 P_NOTIF_SETUP_ID in NUMBER
227 ) is
228 begin
229 delete from IBE_WF_NOTIF_MSG_MAPS
230 where NOTIF_MSG_MAP_ID = P_NOTIF_MSG_MAP_ID
231 and NOTIF_SETUP_ID = P_NOTIF_SETUP_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236 EXCEPTION
237 when NO_DATA_FOUND THEN
238 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_MAP_NOT_FOUND');
239 FND_MESSAGE.SET_TOKEN('ID', p_notif_msg_map_id);
240 FND_MSG_PUB.ADD;
241 end DELETE_ROW;
242
243 procedure LOAD_SEED_ROW (
244 P_NOTIF_MSG_MAP_ID in NUMBER,
245 P_NOTIF_SETUP_ID in NUMBER,
246 P_OWNER IN VARCHAR2,
247 P_NOTIFICATION_NAME in VARCHAR2,
248 P_USER_TYPE in VARCHAR2,
249 P_ENABLED_FLAG in VARCHAR2,
250 P_DEFAULT_MSG_MAP_FLAG in VARCHAR2,
251 P_OBJECT_VERSION_NUMBER in NUMBER,
252 P_ALL_ORG_FLAG in VARCHAR2,
253 P_ALL_MSITE_FLAG in VARCHAR2,
254 P_ALL_USER_TYPE_FLAG in VARCHAR2,
255 P_MSITE_ID in NUMBER,
256 P_ORG_ID IN NUMBER,
257 P_MESSAGE_NAME in VARCHAR2,
258 P_LAST_UPDATE_DATE in VARCHAR2,
259 P_CUSTOM_MODE in VARCHAR2,
260 P_UPLOAD_MODE in VARCHAR2
261 )
262
263 IS
264
265 BEGIN --{
266
267 if (P_UPLOAD_MODE = 'NLS')
268 then --{
269 null;
270 else
271 ibe_wf_notif_msg_maps_pvt.load_row(
272 p_notif_msg_map_id => P_NOTIF_MSG_MAP_ID,
273 p_notif_setup_id => P_NOTIF_SETUP_ID,
274 p_owner => P_OWNER,
275 p_notification_name => P_NOTIFICATION_NAME,
276 p_user_type => P_USER_TYPE,
277 p_enabled_flag => P_ENABLED_FLAG,
278 p_default_msg_map_flag => P_DEFAULT_MSG_MAP_FLAG,
279 p_object_version_number => P_OBJECT_VERSION_NUMBER,
280 p_all_org_flag => P_ALL_ORG_FLAG,
281 p_all_msite_flag => P_ALL_MSITE_FLAG,
282 p_all_user_type_flag => P_ALL_USER_TYPE_FLAG,
283 p_msite_id => P_MSITE_ID,
284 p_org_id => P_ORG_ID,
285 p_message_name => P_MESSAGE_NAME,
286 p_last_update_date => P_LAST_UPDATE_DATE,
287 p_custom_mode => P_CUSTOM_MODE
288 );
289 end if; --}
290
291 END LOAD_SEED_ROW; --}
292
293
294 procedure LOAD_ROW (
295 P_NOTIF_MSG_MAP_ID in NUMBER,
296 P_NOTIF_SETUP_ID in NUMBER,
297 P_OWNER IN VARCHAR2,
298 P_NOTIFICATION_NAME in VARCHAR2,
299 P_USER_TYPE in VARCHAR2,
300 P_ENABLED_FLAG in VARCHAR2,
301 P_DEFAULT_MSG_MAP_FLAG in VARCHAR2,
302 P_OBJECT_VERSION_NUMBER in NUMBER,
303 P_ALL_ORG_FLAG in VARCHAR2,
304 P_ALL_MSITE_FLAG in VARCHAR2,
305 P_ALL_USER_TYPE_FLAG in VARCHAR2,
306 P_MSITE_ID in NUMBER,
307 P_ORG_ID IN NUMBER,
308 P_MESSAGE_NAME in VARCHAR2,
309 P_LAST_UPDATE_DATE in VARCHAR2,
310 P_CUSTOM_MODE in VARCHAR2
311 ) IS
312 user_id NUMBER;
313 l_row_id VARCHAR2(64);
314 l_message_name VARCHAR2(30);
315 l_enabled_flag VARCHAR2(1);
316 l_last_updated_by NUMBER;
317 l_last_update_date DATE;
318 f_last_updated_by NUMBER;
319 f_last_update_date DATE;
320
321 BEGIN
322 if( p_owner = 'SEED' ) then
323 user_id := 1;
324 else
325 user_id := 0;
326 end if;
327
328 f_last_updated_by := fnd_load_util.owner_id(P_OWNER);
329 f_last_update_date := nvl(to_date(P_LAST_UPDATE_DATE,'YYYY/MM/DD'),sysdate);
330
331 BEGIN
332 select last_updated_by, last_update_date, message_name, enabled_flag
333 into l_last_updated_by, l_last_update_date,l_message_name, l_enabled_flag
334 from ibe_wf_notif_msg_maps
335 where notif_msg_map_id = p_notif_msg_map_id;
336 EXCEPTION
337 when no_data_found THEN
338 raise no_data_found;
342 then --{
339 END;
340
341 if (fnd_load_util.upload_test(f_last_updated_by,f_last_update_date,l_last_updated_by,l_last_update_date,P_CUSTOM_MODE))
343 if( l_last_updated_by = 1 ) then --{
344 update_row(
345 P_NOTIF_MSG_MAP_ID => p_notif_msg_map_id,
346 P_NOTIF_SETUP_ID => p_notif_setup_id,
347 P_NOTIFICATION_NAME => p_notification_name,
348 P_USER_TYPE => p_user_type,
349 P_ENABLED_FLAG => p_enabled_flag,
350 P_DEFAULT_MSG_MAP_FLAG => p_default_msg_map_flag,
351 P_OBJECT_VERSION_NUMBER => p_object_version_number,
352 P_ALL_ORG_FLAG => p_all_org_flag,
353 P_ALL_MSITE_FLAG => p_all_msite_flag,
354 P_ALL_USER_TYPE_FLAG => p_all_user_type_flag,
355 P_MSITE_ID => p_msite_id,
356 P_ORG_ID => p_org_id,
357 P_MESSAGE_NAME => p_message_name,
358 P_LAST_UPDATE_DATE => f_last_update_date,
359 P_LAST_UPDATED_BY => f_last_updated_by,
360 P_LAST_UPDATE_LOGIN => f_last_updated_by
361 );
362 else
363 update_row(
364 P_NOTIF_MSG_MAP_ID => p_notif_msg_map_id,
365 P_NOTIF_SETUP_ID => p_notif_setup_id,
366 P_NOTIFICATION_NAME => p_notification_name,
367 P_USER_TYPE => p_user_type,
368 P_ENABLED_FLAG => l_enabled_flag,
369 P_DEFAULT_MSG_MAP_FLAG => p_default_msg_map_flag,
370 P_OBJECT_VERSION_NUMBER => p_object_version_number,
371 P_ALL_ORG_FLAG => p_all_org_flag,
372 P_ALL_MSITE_FLAG => p_all_msite_flag,
373 P_ALL_USER_TYPE_FLAG => p_all_user_type_flag,
374 P_MSITE_ID => p_msite_id,
375 P_ORG_ID => p_org_id,
376 P_MESSAGE_NAME => l_message_name,
377 P_LAST_UPDATE_DATE => f_last_update_date,
378 P_LAST_UPDATED_BY => f_last_updated_by,
379 P_LAST_UPDATE_LOGIN => f_last_updated_by
380 );
381 end if; --}
382 end if; --}
383 EXCEPTION
384 WHEN NO_DATA_FOUND THEN
385 insert_row(
386 X_ROWID => l_row_id,
387 P_NOTIF_MSG_MAP_ID => p_notif_msg_map_id,
388 P_NOTIF_SETUP_ID => p_notif_setup_id,
389 P_NOTIFICATION_NAME => p_notification_name,
390 P_USER_TYPE => p_user_type,
391 P_ENABLED_FLAG => p_enabled_flag,
392 P_DEFAULT_MSG_MAP_FLAG => p_default_msg_map_flag,
393 P_OBJECT_VERSION_NUMBER => p_object_version_number,
394 P_ALL_ORG_FLAG => p_all_org_flag,
395 P_ALL_MSITE_FLAG => p_all_msite_flag,
396 P_ALL_USER_TYPE_FLAG => p_all_user_type_flag,
397 P_MSITE_ID => p_msite_id,
398 P_ORG_ID => p_org_id,
399 P_MESSAGE_NAME => p_message_name,
400 P_CREATION_DATE => f_last_update_date,
401 P_CREATED_BY => f_last_updated_by,
402 P_LAST_UPDATE_DATE => f_last_update_date,
403 P_LAST_UPDATED_BY => f_last_updated_by,
404 P_LAST_UPDATE_LOGIN => f_last_updated_by);
405 END LOAD_ROW;
406
407 function check_msg_map_exists(
408 p_notif_msg_map_id IN NUMBER,
409 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM) return BOOLEAN
410 IS
411 l_exists VARCHAR2(1) := '0';
412 BEGIN
413 if( p_object_version_number = FND_API.G_MISS_NUM OR p_object_version_number IS NULL ) then
414 select '1'
415 into l_exists
416 from dual
417 where exists (
418 select notif_msg_map_id, object_version_number
419 From ibe_wf_notif_msg_maps
420 where notif_msg_map_id = p_notif_msg_map_id
421 );
422 if( l_exists <> '1' ) then
423 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_MAP_NOT_FOUND');
424 FND_MESSAGE.SET_TOKEN('ID', p_notif_msg_map_id);
425 FND_MSG_PUB.ADD;
426 return FALSE;
427 else
428 return TRUE;
429 end if;
430 else
431 select '1'
432 into l_exists
433 from dual
434 where exists (
435 select notif_msg_map_id, object_version_number
436 From ibe_wf_notif_msg_maps
437 where notif_msg_map_id = p_notif_msg_map_id
438 and object_version_number = p_object_version_number
439 );
440 if( l_exists <> '1' ) then
441 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSGMAP_VER_NOT_MATCH');
442 FND_MESSAGE.SET_TOKEN('ID', p_notif_msg_map_id);
443 FND_MSG_PUB.ADD;
444 return FALSE;
445 else
446 return TRUE;
447 end if;
448 end if;
449 EXCEPTION
450 when NO_DATA_FOUND THEN
451 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_MAP_NOT_FOUND');
452 FND_MESSAGE.SET_TOKEN('ID', p_notif_msg_map_id);
453 FND_MSG_PUB.ADD;
454 return FALSE;
455 END;
456
457 FUNCTION check_msg_map_duplicate(
458 p_action IN VARCHAR2,
459 p_msg_map_id IN NUMBER,
460 p_notification_name IN VARCHAR2,
461 p_message_name IN VARCHAR2,
462 p_msite_id IN NUMBER,
463 p_org_id IN NUMBER,
464 p_user_type IN VARCHAR2,
465 p_all_org_flag IN VARCHAR2,
466 p_all_msite_flag IN VARCHAR2,
467 p_all_user_type_flag IN VARCHAR2) RETURN BOOLEAN
468 IS
469 l_exists VARCHAR2(1) := '0';
470 l_notif_msg_map_id NUMBER;
471 BEGIN
472 debug('inside check_msg_map_duplicate 1');
473 debug('p_notification_name is ' || p_notification_name);
474 debug('p_mesage_name is ' || p_message_name);
475 debug('p_msite_id is' || p_msite_id);
476 debug('p_org_id is ' || p_org_id);
477 debug('p_user_Type is ' || p_user_type);
478 debug('p_all_org_flag is ' || p_all_org_flag);
479 debug('p_all_msite_flag is ' || p_all_msite_flag);
483 into l_notif_msg_map_id
480 debug('p_all_user_type_flag is ' || p_all_user_type_flag);
481
482 select min(notif_msg_map_id)
484 from ibe_wf_notif_msg_maps
485 where notification_name = p_notification_name
486 and nvl(msite_id, -99999) = nvl(p_msite_id, -99999)
487 and nvl(org_id, -99999) = nvl(p_org_id, -99999)
488 and nvl(user_type, '@#$%') = nvl(p_user_type, '@#$%')
489 and nvl(all_org_flag, '@#$%') = nvl(p_all_org_flag, '@#$%')
490 and nvl(all_msite_flag, '@#$%') = nvl(p_all_msite_flag, '@#$%')
491 and nvl(all_user_type_flag, '@#$%') = nvl(p_all_user_type_flag, '@#$%');
492 --and message_name = p_message_name);
493
494 if( p_action = 'INSERT' ) Then
495 if( l_notif_msg_map_id is not null ) then
496 debug('return false');
497 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_DUP_NOTIF_MSG_MAPS');
498 FND_MESSAGE.SET_TOKEN('NAME', p_message_name);
499 FND_MSG_PUB.ADD;
500 return FALSE;
501 else
502 return TRUE;
503 end if;
504 elsif( p_action='UPDATE') then
505 if( l_notif_msg_map_id is not null AND l_notif_msg_map_id <> p_msg_map_id ) then
506 debug('return false');
507 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_DUP_NOTIF_MSG_MAPS');
508 FND_MESSAGE.SET_TOKEN('NAME', p_message_name);
509 FND_MSG_PUB.ADD;
510 return FALSE;
511 else
512 debug('return true');
513 return TRUE;
514 end if;
515 end if;
516 EXCEPTION
517 when NO_DATA_FOUND THEN
518 debug('return true');
519 return TRUE;
520 END;
521
522 procedure save_wf_notif_msg_maps(
523 p_api_version IN NUMBER,
524 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
525 p_commit IN VARCHAR2 := FND_API.G_FALSE,
526 x_return_status OUT NOCOPY VARCHAR2,
527 x_msg_count OUT NOCOPY NUMBER,
528 x_msg_data OUT NOCOPY VARCHAR2,
529 P_NOTIF_MSG_MAP_ID in NUMBER,
530 P_NOTIF_SETUP_ID in NUMBER,
531 P_NOTIFICATION_NAME in VARCHAR2,
532 P_USER_TYPE in VARCHAR2,
533 P_ENABLED_FLAG in VARCHAR2,
534 P_DEFAULT_MSG_MAP_FLAG in VARCHAR2,
535 P_ALL_ORG_FLAG in VARCHAR2,
536 P_ALL_MSITE_FLAG in VARCHAR2,
537 P_ALL_USER_TYPE_FLAG in VARCHAR2,
538 P_MSITE_ID in NUMBER,
539 P_ORG_ID in NUMBER,
540 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
541 P_MESSAGE_NAME in VARCHAR2)
542 IS
543 l_api_name VARCHAR2(30) := 'SAVE_WF_NOTIF_MSG_MAPS';
544 l_full_name VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
545 l_object_version_number NUMBER;
546 l_operation_type VARCHAR2(10) := 'INSERT';
547 l_rowid ROWID;
548 l_exists VARCHAR2(1);
549 l_notif_msg_map_id NUMBER;
550 l_notif_setup_id NUMBER;
551 BEGIN
552 --null;
553 debug('save_notif_msg_maps 1');
554 savepoint save_wf_notif_msg_maps;
555
556 if NOT FND_API.Compatible_Api_Call(g_api_version, p_api_version, l_api_name, g_pkg_name) THEN
557 raise FND_API.G_EXC_UNEXPECTED_ERROR;
558 end if;
559
560 if FND_API.To_Boolean(p_init_msg_list) then
561 FND_MSG_PUB.initialize;
562 end if;
563
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565
566
567 if( p_notif_msg_map_id IS NOT NULL AND p_notif_setup_id IS NOT NULL) THEN
568 debug('save_notif_msg_maps 2');
569 if( check_msg_map_exists(p_notif_msg_map_id, p_object_version_number) = TRUE ) THEN
570 l_operation_type := 'UPDATE';
571 if( p_object_version_number = FND_API.G_MISS_NUM ) then
572 select object_version_number
573 into l_object_version_number
574 from ibe_wf_notif_msg_maps
575 where notif_msg_map_id = p_notif_msg_map_id
576 and notif_setup_id = p_notif_setup_id;
577 else
578 l_object_version_number := p_object_version_number;
579 end if;
580 l_object_version_number := l_object_version_number + 1;
581 else
582 debug('save_notif_msg_maps 3');
583 raise FND_API.G_EXC_ERROR;
584 end if;
585 if( check_msg_map_duplicate(
586 l_operation_type, p_notif_msg_map_id, p_notification_name, p_message_name, p_msite_id, p_org_id, p_user_type,
587 p_all_org_flag, p_all_msite_flag, p_all_user_type_flag) <> TRUE ) then
588 debug('save_notif_msg_maps 5');
589 raise FND_API.G_EXC_ERROR;
590 end if;
591 end if;
592
593 if( l_operation_type = 'INSERT') THEN
594 debug('save_notif_msg_maps 4');
595 if( p_notification_name is not null and p_message_name is not null ) then
596 if( check_msg_map_duplicate(
597 l_operation_type, p_notif_msg_map_id, p_notification_name, p_message_name, p_msite_id, p_org_id, p_user_type,
598 p_all_org_flag, p_all_msite_flag, p_all_user_type_flag) <> TRUE ) then
599 debug('save_notif_msg_maps 5');
600 raise FND_API.G_EXC_ERROR;
601 end if;
602 else
603 debug('save_notif_msg_maps 6');
604 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_NAME_REQ');
605 FND_MSG_PUB.ADD;
606 raise FND_API.G_EXC_ERROR;
607 end if;
608 end if;
609
610 if( l_operation_type = 'INSERT' ) then
611 debug('save_notif_msg_maps 7');
612 select ibe_wf_notif_msg_maps_s1.nextval
613 into l_notif_msg_map_id
614 from dual;
615
616 debug('save_notif_msg_maps 8');
617 select notif_setup_id
618 into l_notif_setup_id
622 debug('save_notif_msg_maps 9');
619 from ibe_wf_notif_setup
620 where notification_name = p_notification_name;
621
623 insert_row(
624 X_ROWID => l_rowid,
625 P_NOTIF_MSG_MAP_ID => l_notif_msg_map_id,
626 P_NOTIF_SETUP_ID => l_notif_setup_id,
627 P_NOTIFICATION_NAME => p_notification_name,
628 P_USER_TYPE => p_user_type,
629 P_ENABLED_FLAG => p_enabled_flag,
630 P_DEFAULT_MSG_MAP_FLAG => p_default_msg_map_flag,
631 P_OBJECT_VERSION_NUMBER => 1.0,
632 P_ALL_ORG_FLAG => p_all_org_flag,
633 P_ALL_MSITE_FLAG => p_all_msite_flag,
634 P_ALL_USER_TYPE_FLAG => p_all_user_type_flag,
635 P_MSITE_ID => p_msite_id,
636 P_ORG_ID => p_org_id,
637 P_MESSAGE_NAME => p_message_name,
638 P_CREATION_DATE => sysdate,
639 P_CREATED_BY => FND_GLOBAL.user_id,
640 P_LAST_UPDATE_DATE => sysdate,
641 P_LAST_UPDATED_BY => FND_GLOBAL.user_id,
642 P_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id
643 );
644 debug('save_notif_msg_maps 10');
645 else
646 debug('save_notif_msg_maps 11');
647 l_notif_msg_map_id := p_notif_msg_map_id;
648 l_notif_setup_id := p_notif_setup_id;
649 begin
650 update_row(
651 P_NOTIF_MSG_MAP_ID => l_notif_msg_map_id,
652 P_NOTIF_SETUP_ID => l_notif_setup_id,
653 P_NOTIFICATION_NAME => p_notification_name,
654 P_USER_TYPE => p_user_type,
655 P_ENABLED_FLAG => p_enabled_flag,
656 P_DEFAULT_MSG_MAP_FLAG => p_default_msg_map_flag,
657 P_OBJECT_VERSION_NUMBER => l_object_version_number,
658 P_ALL_ORG_FLAG => p_all_org_flag,
659 P_ALL_MSITE_FLAG => p_all_msite_flag,
660 P_ALL_USER_TYPE_FLAG => p_all_user_type_flag,
661 P_MSITE_ID => p_msite_id,
662 P_ORG_ID => p_ORG_id,
663 P_MESSAGE_NAME => p_message_name,
664 P_LAST_UPDATE_DATE => sysdate,
665 P_LAST_UPDATED_BY => FND_GLOBAL.user_id,
666 P_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id
667 );
668 Exception
669 when no_data_found then
670 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_MAP_NOT_FOUND');
671 FND_MESSAGE.SET_TOKEN('ID', p_notif_msg_map_id);
672 FND_MSG_PUB.ADD;
673 end;
674 debug('save_notif_msg_maps 12');
675 end if;
676
677 if( FND_API.to_Boolean(p_commit)) then
678 debug('save_notif_msg_maps 13');
679 commit;
680 end if;
681
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
684 if( x_msg_count > 1 ) then
685 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
686 end if;
687 debug('save_notif_msg_maps 14');
688 EXCEPTION
689 when FND_API.G_EXC_ERROR THEN
690 rollback to save_wf_notif_msg_maps;
691 x_return_status := FND_API.G_RET_STS_ERROR;
692 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
693 if( x_msg_count > 1 ) then
694 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
695 end if;
696
697 when FND_API.G_EXC_UNEXPECTED_ERROR THEN
698 rollback to save_wf_notif_msg_maps;
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
701 if( x_msg_count > 1) then
702 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
703 end if;
704
705 when OTHERS then
706 rollback to save_wf_notif_msg_maps;
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708 if FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
709 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, l_api_name);
710 end if;
711 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
712 if( x_msg_count > 1 ) then
713 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
714 end if;
715 END save_wf_notif_msg_maps;
716
717 procedure delete_wf_notif_msg_maps(
718 p_api_version IN NUMBER,
719 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
720 p_commit IN VARCHAR2 := FND_API.G_FALSE,
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_msg_count OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2,
724 P_NOTIFICATION_NAME in VARCHAR2,
725 p_notif_msg_map_id IN NUMBER,
726 P_MESSAGE_NAME in VARCHAR2
727 ) IS
728 l_notif_msg_map_id NUMBER;
729 l_api_name CONSTANT VARCHAR2(30) := 'delete_wf_notif_msg_maps';
730 l_notification_name VARCHAR2(30);
731 l_notif_setup_id NUMBER;
732 cursor wf_notif_setup(p_notification_name IN VARCHAR2) IS
733 select notif_setup_id
734 from ibe_wf_notif_setup
735 where notification_name = p_notification_name;
736 BEGIN
737 --null;
738 savepoint delete_wf_notif_msg_maps;
739
740 IF NOT FND_API.compatible_api_call(g_api_version, p_api_version, l_api_name, g_pkg_name ) THEN
741 RAISE FND_API.g_exc_unexpected_error;
742 END IF;
743
744 IF FND_API.to_boolean(p_init_msg_list) THEN
745 FND_MSG_PUB.initialize;
746 END IF;
747
748 x_return_status := FND_API.G_RET_STS_SUCCESS;
749
750 if( p_notification_name IS NOT NULL AND p_message_name IS NOT NULL ) THEN
751 open wf_notif_setup(p_notification_name);
752 LOOP
753 fetch wf_notif_setup into l_notif_setup_id;
754 exit when wf_notif_setup%NOTFOUND;
755 end loop;
759 else
756 close wf_notif_setup;
757 if( check_msg_map_exists(p_notif_msg_map_id) = TRUE ) THEN
758 delete_row(p_notif_setup_id => l_notif_setup_id, p_notif_msg_map_id => p_notif_msg_map_id);
760 raise no_data_found;
761 end if;
762 else
763 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_MSG_NAME_REQ');
764 FND_MSG_PUB.ADD;
765 raise FND_API.G_EXC_ERROR;
766 end if;
767 EXCEPTION
768 when NO_DATA_FOUND THEN
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_NAME_NOT_FOUND');
771 FND_MESSAGE.SET_TOKEN('NAME', p_notification_name);
772 --FND_MSG_PUB.ADD;
773 --FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
774 x_msg_data := FND_MESSAGE.GET;
775 x_msg_count := 1;
776 when FND_API.G_EXC_ERROR THEN
777 rollback to save_wf_notif_msg_maps;
778 x_return_status := FND_API.G_RET_STS_ERROR;
779 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
780 if( x_msg_count > 1 ) then
781 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
782 end if;
783
784 when FND_API.G_EXC_UNEXPECTED_ERROR THEN
785 rollback to save_wf_notif_msg_maps;
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
788 if( x_msg_count > 1 ) then
789 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
790 end if;
791
792 when OTHERS then
793 rollback to save_wf_notif_msg_maps;
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 if FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
796 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, l_api_name);
797 end if;
798 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
799 if( x_msg_count > 1 ) then
800 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
801 end if;
802 ENd delete_wf_notif_msg_maps;
803
804 end IBE_WF_NOTIF_MSG_MAPS_PVT;