[Home] [Help]
PACKAGE BODY: APPS.IBE_WF_NOTIF_SETUP_PVT
Source
1 package body IBE_WF_NOTIF_SETUP_PVT as
2 /* $Header: IBEVWFNB.pls 120.2 2005/07/25 12:35:02 appldev ship $ */
3
4
5 g_debug boolean := TRUE;
6
7 procedure debug(p_msg VARCHAR2)
8 IS
9 BEGIN
10 if( g_debug = TRUE ) then
11 -- dbms_output.put_line(p_msg);
12 null;
13 end if;
14 end;
15
16 procedure INSERT_ROW (
17
18 X_ROWID in out NOCOPY VARCHAR2,
19 P_NOTIF_SETUP_ID in NUMBER,
20 P_ORG_ID_FLAG in VARCHAR2,
21 P_MSITE_ID_FLAG in VARCHAR2,
22 P_USER_TYPE_FLAG in VARCHAR2,
23 P_ENABLED_FLAG in VARCHAR2,
24 P_DEFAULT_MESSAGE_NAME in VARCHAR2,
25 P_UPDATE_ENABLED_FLAG in VARCHAR2,
26 P_OBJECT_VERSION_NUMBER in NUMBER,
27 P_NOTIFICATION_NAME in VARCHAR2,
28 p_customized_flag IN VARCHAR2,
29 P_CREATION_DATE in DATE,
30 P_CREATED_BY in NUMBER,
31 P_LAST_UPDATE_DATE in DATE,
32 P_LAST_UPDATED_BY in NUMBER,
33 P_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is
36 select ROWID
37 from IBE_WF_NOTIF_SETUP
38 where NOTIF_SETUP_ID = p_NOTIF_SETUP_ID;
39 begin
40 insert into IBE_WF_NOTIF_SETUP (
41 NOTIF_SETUP_ID,
42 NOTIFICATION_NAME,
43 ORG_ID_FLAG,
44 MSITE_ID_FLAG,
45 USER_TYPE_FLAG,
46 ENABLED_FLAG,
47 DEFAULT_MESSAGE_NAME,
48 UPDATE_ENABLED_FLAG,
49 CUSTOMIZED_FLAG,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 OBJECT_VERSION_NUMBER
56 ) select
57 P_NOTIF_SETUP_ID,
58 P_NOTIFICATION_NAME,
59 P_ORG_ID_FLAG,
60 P_MSITE_ID_FLAG,
61 P_USER_TYPE_FLAG,
62 P_ENABLED_FLAG,
63 P_DEFAULT_MESSAGE_NAME,
64 P_UPDATE_ENABLED_FLAG,
65 P_CUSTOMIZED_FLAG,
66 P_CREATED_BY,
67 P_CREATION_DATE,
68 P_LAST_UPDATED_BY,
69 P_LAST_UPDATE_DATE,
70 P_LAST_UPDATE_LOGIN,
71 P_OBJECT_VERSION_NUMBER
72 FROM DUAL
73 WHERE not exists
74 (select NULL
75 from IBE_WF_NOTIF_SETUP T
76 where T.NOTIF_SETUP_ID = P_NOTIF_SETUP_ID);
77
78 open c;
79 fetch c into X_ROWID;
80 if (c%notfound) then
81 close c;
82 raise no_data_found;
83 end if;
84 close c;
85 end INSERT_ROW;
86
87 procedure LOCK_ROW (
88 P_NOTIF_SETUP_ID in NUMBER,
89 P_ORG_ID_FLAG in VARCHAR2,
90 P_MSITE_ID_FLAG in VARCHAR2,
91 P_USER_TYPE_FLAG in VARCHAR2,
92 P_ENABLED_FLAG in VARCHAR2,
93 P_DEFAULT_MESSAGE_NAME in VARCHAR2,
94 P_UPDATE_ENABLED_FLAG in VARCHAR2,
95 P_OBJECT_VERSION_NUMBER in NUMBER,
96 P_NOTIFICATION_NAME in VARCHAR2,
97 P_CUSTOMIZED_FLAG IN VARCHAR2
98 ) is
99 cursor c1 is select
100 ORG_ID_FLAG,
101 MSITE_ID_FLAG,
102 USER_TYPE_FLAG,
103 ENABLED_FLAG,
104 DEFAULT_MESSAGE_NAME,
105 UPDATE_ENABLED_FLAG,
106 OBJECT_VERSION_NUMBER,
107 NOTIFICATION_NAME,
108 CUSTOMIZED_FLAG
109 from IBE_WF_NOTIF_SETUP
110 where NOTIF_SETUP_ID = p_NOTIF_SETUP_ID
111 for update of NOTIF_SETUP_ID nowait;
112 begin
113 for tlinfo in c1 loop
114 if ( (tlinfo.NOTIFICATION_NAME = P_NOTIFICATION_NAME)
115 AND (tlinfo.ORG_ID_FLAG = P_ORG_ID_FLAG)
116 AND (tlinfo.MSITE_ID_FLAG = P_MSITE_ID_FLAG)
117 AND (tlinfo.USER_TYPE_FLAG = P_USER_TYPE_FLAG)
118 AND (tlinfo.ENABLED_FLAG = P_ENABLED_FLAG)
119 AND (tlinfo.DEFAULT_MESSAGE_NAME = P_DEFAULT_MESSAGE_NAME)
120 AND (tlinfo.CUSTOMIZED_FLAG = P_CUSTOMIZED_FLAG)
121 AND (tlinfo.UPDATE_ENABLED_FLAG = P_UPDATE_ENABLED_FLAG)
122 AND ((tlinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
123 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (P_OBJECT_VERSION_NUMBER is null)))
124 ) then
125 null;
126 else
127 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128 app_exception.raise_exception;
129 end if;
130 end loop;
131 return;
132 end LOCK_ROW;
133
134 procedure UPDATE_ROW (
135 P_NOTIF_SETUP_ID in NUMBER,
136 P_ORG_ID_FLAG in VARCHAR2,
137 P_MSITE_ID_FLAG in VARCHAR2,
138 P_USER_TYPE_FLAG in VARCHAR2,
139 P_ENABLED_FLAG in VARCHAR2,
140 P_DEFAULT_MESSAGE_NAME in VARCHAR2,
141 P_UPDATE_ENABLED_FLAG in VARCHAR2,
142 P_OBJECT_VERSION_NUMBER in NUMBER,
143 P_NOTIFICATION_NAME in VARCHAR2,
144 P_CUSTOMIZED_FLAG IN VARCHAR2,
145 P_LAST_UPDATE_DATE in DATE,
146 P_LAST_UPDATED_BY in NUMBER,
147 P_LAST_UPDATE_LOGIN in NUMBER
148 ) is
149 begin
150 --debug('update_row 1');
151 --debug('p_notif_setup_id is ' || p_notif_setup_id);
152 --debug('p_notification_name is ' || p_notification_name);
153 update IBE_WF_NOTIF_SETUP set
154 ORG_ID_FLAG = P_ORG_ID_FLAG,
155 MSITE_ID_FLAG = P_MSITE_ID_FLAG,
156 USER_TYPE_FLAG = p_USER_TYPE_FLAG,
157 ENABLED_FLAG = P_ENABLED_FLAG,
158 DEFAULT_MESSAGE_NAME = P_DEFAULT_MESSAGE_NAME,
159 UPDATE_ENABLED_FLAG = P_UPDATE_ENABLED_FLAG,
160 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
161 NOTIFICATION_NAME = P_NOTIFICATION_NAME,
162 CUSTOMIZED_FLAG = P_CUSTOMIZED_FLAG,
163 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
166 where NOTIF_SETUP_ID = P_NOTIF_SETUP_ID;
167
168 --debug('update_row 2');
169 if (sql%notfound) then
170 --debug('update_row 3');
171 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_NAME_NOT_FOUND');
172 FND_MESSAGE.SET_NAME('NAME', p_notification_name);
173 FND_MSG_PUB.ADD;
174 raise no_data_found;
175 end if;
176 --debug('update_row 4');
177 end UPDATE_ROW;
178
179 procedure DELETE_ROW (
180 P_NOTIF_SETUP_ID in NUMBER
181 ) is
182 begin
183 delete from IBE_WF_NOTIF_SETUP
184 where NOTIF_SETUP_ID = p_NOTIF_SETUP_ID;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 EXCEPTION
190 when NO_DATA_FOUND then
191 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_NOT_FOUND');
192 FND_MESSAGE.SET_TOKEN('ID', p_notif_setup_id);
193 FND_MSG_PUB.ADD;
194 end DELETE_ROW;
195
196 procedure LOAD_ROW(
197 P_NOTIF_SETUP_ID in NUMBER,
198 P_OWNER IN VARCHAR2,
199 P_ORG_ID_FLAG in VARCHAR2,
200 P_MSITE_ID_FLAG in VARCHAR2,
201 P_USER_TYPE_FLAG in VARCHAR2,
202 P_ENABLED_FLAG in VARCHAR2,
203 P_DEFAULT_MESSAGE_NAME in VARCHAR2,
204 P_UPDATE_ENABLED_FLAG in VARCHAR2,
205 P_OBJECT_VERSION_NUMBER in NUMBER,
206 p_customized_flag IN VARCHAR2,
207 P_NOTIFICATION_NAME in VARCHAR2,
208 P_LAST_UPDATE_DATE in varchar2,
209 P_CUSTOM_MODE in Varchar2) IS
210
211 l_row_id VARCHAR2(64);
212 l_enabled_flag VARCHAR2(1);
213
214 f_luby number; -- entity owner in file
215 f_ludate date; -- entity update date in file
216 db_luby number; -- entity owner in db
217 db_ludate date; -- entity update date in db
218
219 BEGIN
220
221 -- Translate owner to file_last_updated_by
222 f_luby := fnd_load_util.owner_id(P_OWNER);
223 -- Translate char last_update_date to date
224 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
225
226 select LAST_UPDATED_BY, LAST_UPDATE_DATE
227 into db_luby, db_ludate
228 from IBE_WF_NOTIF_SETUP
229 where notif_setup_id = P_NOTIF_SETUP_ID;
230
231 --Invoke standard merge comparison routine UPLOAD_TEST to determine whether to upload or not
232 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, P_CUSTOM_MODE)) then
233 --dbms_output.put_line('l_merge_data is true');
234 if( p_customized_flag = 'Y' ) then
235 BEGIN
236 select enabled_flag
237 into l_enabled_flag
238 from ibe_wf_notif_setup
239 where notif_setup_id = p_notif_setup_id;
240 EXCEPTION
241 when no_data_found then
242 raise no_data_found;
243 END;
244 update_row(
245 p_notif_setup_id => p_notif_Setup_id,
246 p_org_id_flag => p_org_id_flag,
247 p_msite_id_flag => p_msite_id_flag,
248 p_user_type_flag => p_user_type_flag,
249 p_enabled_flag => l_enabled_flag,
250 p_default_message_name => p_default_message_name,
251 p_update_enabled_flag => p_update_enabled_flag,
252 p_object_version_number => p_object_version_number,
253 p_notification_name => p_notification_name,
254 p_customized_flag => p_customized_flag,
255 p_last_update_date => f_ludate, --sysdate,
256 p_last_updated_by => f_luby,--user_id,
257 p_last_update_login => f_luby);--user_id);
258 else
259 update_row(
260 p_notif_setup_id => p_notif_Setup_id,
261 p_org_id_flag => p_org_id_flag,
262 p_msite_id_flag => p_msite_id_flag,
263 p_user_type_flag => p_user_type_flag,
264 p_enabled_flag => p_enabled_flag,
265 p_default_message_name => p_default_message_name,
266 p_update_enabled_flag => p_update_enabled_flag,
267 p_object_version_number => p_object_version_number,
268 p_notification_name => p_notification_name,
269 p_customized_flag => p_customized_flag,
270 p_last_update_date => f_ludate, --sysdate,
271 p_last_updated_by => f_luby,--user_id,
272 p_last_update_login => f_luby);--user_id);
273 end if;
274 --dbms_output.put_line('after update');
275 END IF;
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 insert_row(
279 X_ROWID => l_row_id,
280 P_NOTIF_SETUP_ID => p_notif_Setup_id,
281 P_ORG_ID_FLAG => p_org_id_flag,
282 P_MSITE_ID_FLAG => p_msite_id_flag,
283 P_USER_TYPE_FLAG => p_user_type_flag,
284 P_ENABLED_FLAG => p_enabled_flag,
285 P_DEFAULT_MESSAGE_NAME => p_default_message_name,
286 P_UPDATE_ENABLED_FLAG => p_update_enabled_flag,
287 P_OBJECT_VERSION_NUMBER => p_object_version_number,
288 P_NOTIFICATION_NAME => p_notification_name,
289 p_customized_flag => p_customized_flag,
290 P_CREATION_DATE => f_ludate, --sysdate,
291 P_CREATED_BY => f_luby,--user_id,
292 P_LAST_UPDATE_DATE => f_ludate, --sysdate,
293 P_LAST_UPDATED_BY => f_luby,--user_id,
294 P_LAST_UPDATE_LOGIN => f_luby);--user_id);
295
296 END LOAD_ROW;
297
298 function Check_Notif_Duplicate(
299 p_notification_name IN VARCHAR2)
300 RETURN BOOLEAN IS
301 l_exists VARCHAR2(1) := '0';
302 l_return_status VARCHAR2(1);
303 BEGIN
304 --debug('in Validate_Name ' || p_notification_name);
305 select '1'
306 into l_exists
307 from dual
308 where exists(
309 select distinct notification_name
310 from ibe_wf_notif_setup
311 where notification_name = p_notification_name);
312
313 if( l_exists = '1' ) then
314 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_DUP_NOTIF_SETUP');
315 FND_MESSAGE.SET_TOKEN('NAME', p_notification_name);
316 FND_MSG_PUB.ADD;
317 return FALSE;
318 else
319 return TRUE;
320 end if;
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323 --dbms_output.put_line('return true');
324 return TRUE;
325 END;
326
327 FUNCTION Check_Notif_Exists(
328 p_notif_setup_id IN NUMBER,
329 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM) RETURN BOOLEAN
330 IS
331 l_exists VARCHAR2(1) := '0';
332 BEGIN
333 if( p_object_version_number = FND_API.G_MISS_NUM OR p_object_version_number is NULL ) then
334 select '1'
335 into l_exists
336 From Dual
337 where exists(
338 select notif_setup_id, object_version_number, notification_name
339 from ibe_wf_notif_setup
340 where notif_setup_id = p_notif_setup_id);
341
342 if( l_exists = '1' ) then
343 return TRUE;
344 else
345 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_NOT_FOUND');
346 FND_MESSAGE.SET_TOKEN('ID', to_char(p_notif_setup_id));
347 FND_MSG_PUB.ADD;
348 return false;
349 END if;
350 else
351 select '1'
352 into l_exists
353 From Dual
354 where exists(
355 select notif_setup_id, object_version_number, notification_name
356 from ibe_wf_notif_setup
357 where notif_setup_id = p_notif_setup_id
358 And object_version_number = p_object_version_number);
359
360 if( l_exists = '1' ) then
361 return TRUE;
362 else
363 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_VER_NOT_MATCH');
364 FND_MESSAGE.SET_TOKEN('ID', to_char(p_notif_setup_id));
365 FND_MSG_PUB.ADD;
366 return false;
367 END if;
368 end if;
369
370 EXCEPTION
371 when NO_DATA_FOUND then
372 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_NOT_FOUND');
373 FND_MESSAGE.SET_TOKEN('ID', p_notif_setup_id);
374 FND_MSG_PUB.ADD;
375 return FALSE;
376 END;
377
378
379 procedure Save_WF_NOTIF_Setup(
380 p_api_version IN NUMBER,
381 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
382 p_commit IN VARCHAR2 := FND_API.G_FALSE,
383 x_return_status OUT NOCOPY VARCHAR2,
384 x_msg_count OUT NOCOPY NUMBER,
385 x_msg_data OUT NOCOPY VARCHAR2,
386 P_NOTIF_SETUP_ID in NUMBER,
387 P_ORG_ID_FLAG in VARCHAR2,
388 P_MSITE_ID_FLAG in VARCHAR2,
389 P_USER_TYPE_FLAG in VARCHAR2,
390 P_ENABLED_FLAG in VARCHAR2,
391 P_DEFAULT_MESSAGE_NAME in VARCHAR2,
392 P_UPDATE_ENABLED_FLAG in VARCHAR2,
393 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
394 P_NOTIFICATION_NAME in VARCHAR2,
395 p_customized_flag IN VARCHAR2
396 ) IS
397 l_api_name CONSTANT VARCHAR2(30) := 'save_wf_notif_setup';
398 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
399 l_operation_type VARCHAR2(10) := 'INSERT';
400 l_return_status VARCHAR2(1);
401 l_msg_data VARCHAR2(2000);
402 l_msg_count NUMBER;
403 l_notif_setup_id NUMBER;
404 l_notification_name VARCHAR2(30);
405 l_exists VARCHAr2(1);
406 l_object_version_number NUMBER;
407 cursor wf_setup_seq IS
408 select ibe_wf_notif_setup_s1.nextval
409 From dual;
410 l_rowid ROWID;
411 BEGIN
412 --debug('Save_wf_notif_setup 1');
413 savepoint save_wf_notif_setup;
414
415 if NOT FND_API.Compatible_Api_Call(g_api_version, p_api_version, l_api_name, g_pkg_name) THEN
416 --debug('Save_wf_notif_setup 2');
417 raise FND_API.G_EXC_UNEXPECTED_ERROR;
418 end if;
419
420 if FND_API.To_Boolean(p_init_msg_list) then
421 --debug('Save_wf_notif_setup 3');
422 FND_MSG_PUB.initialize;
423 end if;
424
425 --debug('Save_wf_notif_setup 4');
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427
428 if p_notif_setup_id IS NOT NULL THEN
429 --debug('Save_wf_notif_setup 5');
430 if( check_notif_exists(p_notif_setup_id, p_object_version_number) = TRUE ) then
431 l_operation_type :='UPDATE';
432 if( p_object_version_number = FND_API.G_MISS_NUM ) then
433 select object_version_number
434 into l_object_version_number
435 from ibe_wf_notif_setup
436 where notif_setup_id = p_notif_setup_id;
437 else
438 l_object_version_number := p_object_version_number;
439 end if;
440 l_object_version_number := l_object_version_number +1;
441 else
442 raise FND_API.G_EXC_ERROR;
443 end if;
444 end if;
445
446 if( p_notification_name is not null AND l_operation_type = 'INSERT') then
447 --debug('Save_wf_notif_setup 6');
448 if( check_notif_duplicate(p_notification_name) <> TRUE ) then
449 --debug('save_wf_notif_setup 7');
450 raise FND_API.G_EXC_ERROR;
451 end if;
452 --debug('save_wf_notif_setup 7a');
453
454 elsif( p_notification_name is null AND l_operation_type = 'INSERT') then
455
456 --debug('save_wf_notif_setup 9');
457 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_NOTIF_NAME');
458 FND_MESSAGE.SET_TOKEN('NAME', p_notification_name);
459 FND_MSG_PUB.ADD;
460 raise FND_API.G_EXC_ERROR;
461 end if;
462
463 --debug('save_wf_notif_setup 10');
464 -- now validate if all parameters that are required is not null
465
466 if( p_org_id_flag is null ) then
467 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_FIELD');
468 FND_MESSAGE.SET_TOKEN('NAME', 'ORG_ID_FLAG');
469 FND_MSG_PUB.ADD;
470 raise FND_API.G_EXC_ERROR;
471 elsif( p_msite_id_flag is null ) THEN
472 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_FIELD');
473 FND_MESSAGE.SET_TOKEN('NAME', 'MSITE_ID_FLAG');
474 FND_MSG_PUB.ADD;
475 raise FND_API.G_EXC_ERROR;
476 elsif( p_user_type_flag is null ) THEN
477 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_FIELD');
478 FND_MESSAGE.SET_TOKEN('NAME', 'USER_TYPE_FLAG');
479 FND_MSG_PUB.ADD;
480 raise FND_API.G_EXC_ERROR;
481 elsif( p_enabled_flag is null ) THEN
482 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_FIELD');
483 FND_MESSAGE.SET_TOKEN('NAME', 'ENABLED_FLAG');
484 FND_MSG_PUB.ADD;
485 raise FND_API.G_EXC_ERROR;
486 elsif( p_default_MESSAGE_NAME is null ) THEN
487 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_FIELD');
488 FND_MESSAGE.SET_TOKEN('NAME', 'DEFAULT_MESSAGE_NAME');
489 FND_MSG_PUB.ADD;
490 raise FND_API.G_EXC_ERROR;
491 end if;
492
493 if( l_operation_type = 'INSERT' ) THEN
494 --debug('save_wf_notif_setup 11');
495 if( p_customized_flag = 'Y' ) then
496 open wf_setup_seq;
497 fetch wf_setup_seq into l_notif_setup_id;
498 close wf_setup_seq;
499 --debug('save_wf_notif_setup 12');
500 elsif( p_customized_flag = 'N' ) then
501 BEGIN
502 select nvl(max(notif_setup_id) , 0)
503 into l_notif_setup_id
504 from ibe_wf_notif_setup;
505 EXCEPTION
506 when no_data_found THEN
507 l_notif_setup_id := 0;
508 END;
509 l_notif_setup_id := l_notif_Setup_id + 1;
510
511 end if;
512
513 insert_row(
514 X_ROWID => l_rowid,
515 P_NOTIF_SETUP_ID => l_notif_setup_id,
516 P_ORG_ID_FLAG => p_org_id_flag,
517 P_MSITE_ID_FLAG => p_msite_id_flag,
518 P_USER_TYPE_FLAG => p_user_type_flag,
519 P_ENABLED_FLAG => p_enabled_flag,
520 P_DEFAULT_MESSAGE_NAME => p_default_MESSAGE_NAME,
521 P_UPDATE_ENABLED_FLAG => p_UPDATE_ENABLED_FLAG,
522 P_OBJECT_VERSION_NUMBER => 1,
523 P_NOTIFICATION_NAME => p_notification_name,
524 p_customized_flag => p_customized_flag,
525 P_CREATION_DATE => sysdate,
526 P_CREATED_BY => FND_GLOBAL.user_id,
527 P_LAST_UPDATE_DATE => sysdate,
528 P_LAST_UPDATED_BY => FND_GLOBAL.user_id,
529 P_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id
530 );
531 --debug('save_wf_notif_setup 13');
532 elsif( l_operation_type = 'UPDATE') then
533 --debug('save_wf_notif_setup 14');
534 l_notif_setup_id := p_notif_setup_id;
535 update_row(
536 P_NOTIF_SETUP_ID => l_notif_setup_id,
537 P_ORG_ID_FLAG => p_org_id_flag,
538 P_MSITE_ID_FLAG => p_msite_id_flag,
539 P_USER_TYPE_FLAG => p_user_type_flag,
540 P_ENABLED_FLAG => p_enabled_flag,
541 P_DEFAULT_MESSAGE_NAME => p_default_MESSAGE_NAME,
542 P_UPDATE_ENABLED_FLAG => p_UPDATE_ENABLED_FLAG,
543 P_OBJECT_VERSION_NUMBER => l_object_version_number,
544 P_NOTIFICATION_NAME => p_notification_name,
545 p_customized_flag => p_customized_flag,
546 P_LAST_UPDATE_DATE => sysdate,
547 P_LAST_UPDATED_BY => FND_GLOBAL.user_id,
548 P_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id
549 );
550 --debug('save_wf_notif_setup 15');
551 end if;
552
553 if FND_API.to_Boolean(p_commit) THEN
554 --debug('save_wf_notif_setup 16');
555 commit;
556 end if;
557
558 --debug('save_wf_notif_setup 17');
559 x_return_status := FND_API.G_RET_STS_SUCCESS;
560
561 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
562 if( x_msg_count > 1 ) then
563 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
564 end if;
565 --debug('save_wf_notif_setup 18');
566 EXCEPTION
567 when FND_API.G_EXC_ERROR THEN
568 rollback to save_wf_notif_setup;
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
571 if( x_msg_count > 1 ) then
572 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
573 end if;
574 when FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 rollback to save_wf_notif_setup;
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
577 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
578 if( x_msg_count > 1 ) then
579 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
580 end if;
581
582 when OTHERS then
583 rollback to save_wf_notif_setup;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 if FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
586 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, l_api_name);
587 end if;
588 FND_MSG_PUB.COUNT_AND_GET(p_encoded => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
589 if( x_msg_count > 1 ) then
590 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
591 end if;
592 END save_wf_notif_setup;
593
594 PROCEDURE delete_wf_notif_setup
595 (
596 p_api_version IN NUMBER,
597 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
598 p_commit IN VARCHAR2 := FND_API.G_FALSE,
599 x_return_status OUT NOCOPY VARCHAR2,
600 x_msg_count OUT NOCOPY NUMBER,
601 x_msg_data OUT NOCOPY VARCHAR2,
602 p_notification_name IN VARCHAR2
603 ) IS
604 cursor wf_notif_setup(p_notification_name in VARCHAR2) is
605 select notif_setup_id
606 From IBE_WF_NOTIF_SETUP
607 where notification_name = p_notification_name;
608
609 cursor wf_notif_msg_maps(p_notif_setup_id IN NUMBER) IS
610 select notif_msg_map_id, notification_name, message_name
611 From ibe_wf_notif_msg_maps
612 where notif_setup_id = p_notif_setup_id;
613 --And default_msg_map_flag <> 'Y';
614
615 l_notif_Setup_id NUMBER;
616 l_api_name CONSTANT VARCHAR2(30) := 'delete_wf_notif_setup';
617 l_notification_name VARCHAR2(30);
618 l_message_name VARCHAR2(30);
619 l_notif_msg_map_id NUMBER;
620 l_return_status VARCHAR2(1);
621 BEGIN
622 -- null;
623 savepoint delete_wf_notif_setup;
624
625 IF NOT FND_API.compatible_api_call(g_api_version, p_api_version, l_api_name, g_pkg_name ) THEN
626 RAISE FND_API.g_exc_unexpected_error;
627 END IF;
628
629 IF FND_API.to_boolean(p_init_msg_list) THEN
630 FND_MSG_PUB.initialize;
631 END IF;
632
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 if( p_notification_name IS NOT NULL ) THEN
636 OPEN wf_notif_setup(p_notification_name);
637 FETCH WF_NOTIF_SETUP into l_notif_setup_id;
638 CLOSE WF_NOTIF_SETUP;
639
640 open wf_notif_msg_maps(l_notif_setup_id);
641 LOOP
642 fetch wf_notif_msg_maps into l_notif_msg_map_id, l_notification_name, l_message_name;
643 exit when wf_Notif_Msg_Maps%NOTFOUND;
644
645 IBE_WF_NOTIF_MSG_MAPS_PVT.Delete_Wf_Notif_Msg_MAPS(
646 p_api_version => 1.0,
647 p_init_msg_list => FND_API.G_FALSE,
648 p_commit => FND_API.G_FALSE,
649 x_return_status => l_return_status,
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data,
652 p_notification_name => p_notification_name,
653 p_notif_msg_map_id => l_notif_msg_map_id,
654 p_message_name => l_message_name);
655
656 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
657 raise FND_API.G_EXC_ERROR;
658 end if;
659 END LOOP;
660 close wf_notif_msg_maps;
661 Delete_Row(p_notif_setup_id => l_notif_setup_id);
662 else
663 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_NOTIF_NAME');
664 FND_MESSAGE.SET_TOKEN('NAME', 'Notification_Name');
665 FND_MSG_PUB.ADD;
666 raise FND_API.G_EXC_ERROR;
667 end if;
668 EXCEPTION
669 WHEN FND_API.g_exc_error THEN
670 ROLLBACK TO delete_wf_notif_setup;
671 x_return_status := FND_API.g_ret_sts_error;
672 FND_MSG_PUB.COUNT_AND_GET( p_encoded => FND_API.g_false, p_count => x_msg_count, p_data => x_msg_data );
673 if( x_msg_count > 1 ) then
674 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
675 end if;
676
677 WHEN FND_API.g_exc_unexpected_error THEN
678 ROLLBACK TO delete_wf_notif_setup;
679 x_return_status := FND_API.g_ret_sts_unexp_error ;
680 FND_MSG_PUB.COUNT_AND_GET( p_encoded => FND_API.g_false, p_count => x_msg_count, p_data => x_msg_data );
681 if( x_msg_count > 1 ) then
682 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
683 end if;
684 WHEN OTHERS THEN
685 ROLLBACK TO delete_wf_notif_setup;
686 x_return_status := FND_API.g_ret_sts_unexp_error ;
687 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
688 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, l_api_name);
689 END IF;
690 FND_MSG_PUB.COUNT_AND_GET( p_encoded => FND_API.g_false, p_count => x_msg_count, p_data => x_msg_data );
691 if( x_msg_count > 1 ) then
692 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
693 end if;
694 END Delete_wf_notif_setup;
695
696 procedure Update_Wf_notif_setup(
697 x_return_status OUT NOCOPY JTF_VARCHAR2_TABLE_100,
698 x_msg_count OUT NOCOPY NUMBER,
699 x_msg_data OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
700 p_notif_name_tbl IN JTF_VARCHAR2_TABLE_100,
701 p_enabled_flag_tbl IN JTF_VARCHAR2_TABLE_100)
702 IS
703 l_notif_name_tbl JTF_VARCHAR2_TABLE_100;
704 l_msite_flag VARCHAR2(1);
705 l_org_flag VARCHAR2(1);
706 l_user_type_flag VARCHAR2(1);
707 l_default_message_name VARCHAR2(30);
708 l_update_enabled_flag VARCHAR2(1);
709 l_customized_flag VARCHAR2(1);
710 l_notif_Setup_id NUMBER;
711 l_return_status VARCHAR2(1);
712 l_msg_data VARCHAR2(2000);
713 l_msg_count NUMBER;
714 l_object_version_number NUMBER;
715 BEGIN
716 --null;
717 x_return_status := JTF_VARCHAR2_TABLE_100();
718 x_msg_data := JTF_VARCHAR2_TABLE_2000();
719 if( p_notif_name_tbl IS NULL ) THEN
720 x_return_status.extend(1);
721 x_return_status(1) := FND_API.G_RET_STS_ERROR;
722 x_msg_data.extend(1);
723 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_NOTIF_NAME');
724 FND_MESSAGE.SET_TOKEN('NAME', 'Notification_Name');
725 x_msg_data(1) := FND_MESSAGE.GET;
726 x_msg_count := 1;
727 elsif( p_notif_name_tbl.COUNT <> p_enabled_flag_tbl.COUNT ) THEN
728 x_return_status.extend(1);
729 x_return_status(1) := FND_API.G_RET_STS_ERROR;
730 x_msg_data.extend(1);
731 x_msg_data(1) := FND_MESSAGE.GET_STRING('IBE', 'IBE_WF_NOTIF_UPDATE_ERROR');
732 x_msg_count := 1;
733 else
734 update ibe_wf_notif_setup
735 set enabled_flag = 'Y'
736 where enabled_flag <> 'Y';
737
738 x_return_status.extend(p_notif_name_tbl.COUNT);
739 x_msg_data.extend(p_notif_name_tbl.COUNT);
740 --debug('number of notif is ' || p_notif_name_tbl.COUNT);
741 for i in 1..p_notif_name_tbl.COUNT LOOP
742 --debug('p_notif_name ' || i || ' is ' || p_notif_name_tbl(i));
743 x_return_status(i) := FND_API.G_RET_STS_SUCCESS;
744 if( p_notif_name_tbl(i) IS NOT NULL ) THEN
745 BEGIN
746 select msite_id_flag, org_id_flag, user_type_flag, default_message_name, update_enabled_flag,
747 customized_flag, notif_setup_id, object_version_number
748 into l_msite_flag, l_org_flag, l_user_type_flag, l_default_message_name, l_update_enabled_flag,
749 l_customized_Flag, l_notif_setup_id, l_object_version_number
750 from ibe_Wf_notif_setup
751 where notification_name = p_notif_name_tbl(i);
752 EXCEPTION
753 when no_data_found THEN
754 x_return_status(i) := FND_API.G_RET_STS_ERROR;
755 x_msg_count := x_msg_count + 1;
756 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_NAME_NOT_FOUND');
757 FND_MESSAGE.SET_TOKEN('NAME', p_notif_name_tbl(i));
758 x_msg_data(i) := FND_MESSAGE.GET;
759 end;
760 --debug('l_notif_setup_id is ' || l_notif_setup_id);
761 --debug('l_object_version_number is ' || l_object_version_number);
762 if( x_return_status(i) = FND_API.G_RET_STS_SUCCESS ) then
763 IBE_WF_NOTIF_SETUP_PVT.save_wf_notif_setup(
764 p_api_version => 1.0,
765 p_init_msg_list => FND_API.G_TRUE,
766 p_commit => FND_API.G_TRUE,
767 x_return_Status => l_return_status,
768 x_msg_count => l_msg_count,
769 x_msg_data => l_msg_data,
770 p_notif_setup_id => l_notif_setup_id,
771 p_org_id_flag => l_org_flag,
772 p_msite_id_flag => l_msite_flag,
773 p_user_type_flag => l_user_type_flag,
774 p_enabled_flag => p_enabled_flag_tbl(i),
775 p_default_message_name => l_default_message_name,
776 p_update_enabled_flag => l_update_enabled_flag,
777 p_object_version_number => l_object_version_number,
778 p_notification_name => p_notif_name_tbl(i),
779 p_customized_flag => l_customized_flag);
780 x_return_status(i) := l_return_status;
781 x_msg_data(i) := l_msg_data;
782 x_msg_count := x_msg_count + l_msg_count;
783 end if;
784 else
785 x_return_status(i) := FND_API.G_RET_STS_ERROR;
786 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_REQUIRED_NOTIF_NAME');
787 FND_MESSAGE.SET_TOKEN('NAME', 'Notification_Name');
788 x_msg_data(i) := FND_MESSAGE.GET;
789 x_msg_count := x_msg_count + 1;
790 END IF;
791 END LOOP;
792 end if;
793 end update_wf_notif_setup;
794
795 FUNCTION Check_Notif_Enabled(p_notification_name VARCHAR2) RETURN VARCHAR2 IS
796 l_enabled_flag VARCHAR2(1);
797 BEGIN
798 select enabled_flag
799 into l_enabled_flag
800 from ibe_wf_notif_setup
801 where notification_name = p_notification_name;
802 return l_enabled_flag;
803 EXCEPTION
804 when NO_DATA_FOUND Then
805 return 'N';
806 END;
807 Procedure LOAD_SEED_ROW(
808 P_NOTIF_SETUP_ID in NUMBER,
809 P_OWNER IN VARCHAR2,
810 P_ORG_ID_FLAG in VARCHAR2,
811 P_MSITE_ID_FLAG in VARCHAR2,
812 P_USER_TYPE_FLAG in VARCHAR2,
813 P_ENABLED_FLAG in VARCHAR2,
814 P_DEFAULT_MESSAGE_NAME in VARCHAR2,
815 P_UPDATE_ENABLED_FLAG in VARCHAR2,
816 P_OBJECT_VERSION_NUMBER in NUMBER,
817 p_customized_flag IN VARCHAR2,
818 P_NOTIFICATION_NAME in VARCHAR2,
819 P_LAST_UPDATE_DATE in VARCHAR2,
820 P_CUSTOM_MODE in VARCHAR2,
821 P_UPLOAD_MODE in VARCHAR2)
822 is
823
824 Begin
825 if ( p_upload_mode = 'NLS') then
826 null;
827 else
828 IBE_WF_NOTIF_SETUP_PVT.LOAD_ROW(
829 P_NOTIF_SETUP_ID,
830 P_OWNER,
831 P_ORG_ID_FLAG,
832 P_MSITE_ID_FLAG,
833 P_USER_TYPE_FLAG,
834 P_ENABLED_FLAG,
835 P_DEFAULT_MESSAGE_NAME,
836 P_UPDATE_ENABLED_FLAG,
837 P_OBJECT_VERSION_NUMBER,
838 P_CUSTOMIZED_FLAG,
839 P_NOTIFICATION_NAME,
840 P_LAST_UPDATE_DATE,
841 P_CUSTOM_MODE);
842 end if;
843 end LOAD_SEED_ROW;
844
845 end IBE_WF_NOTIF_SETUP_PVT;