[Home] [Help]
PACKAGE BODY: APPS.IEB_SERVICEPLAN_PVT
Source
1 PACKAGE BODY IEB_ServicePlan_PVT AS
2 /* $Header: IEBSVPB.pls 115.10 2004/02/13 21:51:58 gpagadal noship $ */
3
4 -- ===============================================================
5 -- Start of Comments
6 -- Package name
7 -- IEB_ServicePlan_PVT
8 -- Purpose
9 -- To provide easy to use apis for Blending admin.
10 -- History
11 -- 02-July-2003 gpagadal Created.
12 -- NOTE
13 --
14 -- End of Comments
15 -- ===============================================================
16 PROCEDURE Create_ServicePlan( x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_plan_id OUT NOCOPY NUMBER,
20 p_name IN VARCHAR2,
21 p_desc IN VARCHAR2,
22 p_direction IN VARCHAR2,
23 p_media_type_id IN NUMBER
24 )as
25 l_language VARCHAR2(4);
26
27 l_source_lang VARCHAR2(4);
28
29 l_return_status VARCHAR2(4);
30
31 l_msg_count NUMBER(2);
32
33 l_svc_plan_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
34 l_temp_str varchar2(80);
35
36 BEGIN
37
38
39 fnd_msg_pub.delete_msg();
40 x_return_status := fnd_api.g_ret_sts_success;
41 FND_MSG_PUB.initialize;
42 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
43 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
44 x_msg_data := '';
45 x_msg_count := 0;
46 l_temp_str := null;
47
48
49 select IEB_SVC_PLAN_S1.nextval into l_svc_plan_id from dual;
50
51 EXECUTE immediate 'INSERT INTO IEB_SERVICE_PLANS '||
52 '( SVCPLN_ID, ' ||
53 ' CREATED_BY, ' ||
54 ' CREATION_DATE, ' ||
55 ' LAST_UPDATED_BY,' ||
56 ' LAST_UPDATE_DATE, ' ||
57 ' LAST_UPDATE_LOGIN, ' ||
58 ' SERVICE_PLAN_NAME, ' ||
59 ' DIRECTION, ' ||
60 ' TREATMENT, ' ||
61 ' DESCRIPTION, ' ||
62 ' MEDIA_TYPE_ID, ' ||
63 ' OBJECT_VERSION_NUMBER,' ||
64 ' SECURITY_GROUP_ID ' ||
65 ' )VALUES ' ||
66 '(:1 ,' ||
67 ' :2, '||
68 ' :3, '||
69 ' :4, '||
70 ' :5, '||
71 ' :6, '||
72 ' :7, '||
73 ' :8, '||
74 ' :9, '||
75 ' :10, '||
76 ' :11, '||
77 ' :12, '||
78 ' :13 '||
79 ') '
80 USING l_svc_plan_id,
81 FND_GLOBAL.USER_ID,
82 SYSDATE,
83 FND_GLOBAL.USER_ID,
84 SYSDATE,
85 FND_GLOBAL.LOGIN_ID,
86 LTRIM(RTRIM(p_name)),
87 p_direction,
88 l_temp_str,
89 LTRIM(RTRIM(p_desc)),
90 p_media_type_id,
91 0,
92 0;
93
94
95
96 insert into IEB_SERVICE_PLANS_TL (
97 CREATION_DATE,
98 LAST_UPDATED_BY,
99 LAST_UPDATE_DATE,
100 CREATED_BY,
101 SERVICE_PLAN_ID,
102 OBJECT_VERSION_NUMBER,
103 DESCRIPTION,
104 LAST_UPDATE_LOGIN,
105 PLAN_NAME,
106 LANGUAGE,
107 SOURCE_LANG
108 ) select
109 SYSDATE,
110 FND_GLOBAL.USER_ID,
111 SYSDATE,
112 FND_GLOBAL.USER_ID,
113 l_svc_plan_id,
114 0,
115 LTRIM(RTRIM(p_desc)),
116 FND_GLOBAL.LOGIN_ID,
117 LTRIM(RTRIM(p_name)),
118 L.LANGUAGE_CODE,
119 userenv('LANG')
120 FROM FND_LANGUAGES L
121 where L.INSTALLED_FLAG in ('I', 'B')
122 and not exists
123 (select NULL
124 from IEB_SERVICE_PLANS_TL T
125 where T.SERVICE_PLAN_ID = l_svc_plan_id
126 and T.LANGUAGE = L.LANGUAGE_CODE);
127
128
129
130
131 p_plan_id := l_svc_plan_id;
132 COMMIT;
133
134 EXCEPTION
135 WHEN fnd_api.g_exc_error THEN
136 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
137 ROLLBACK;
138 x_return_status := fnd_api.g_ret_sts_error;
139
140 WHEN fnd_api.g_exc_unexpected_error THEN
141 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
142 ROLLBACK;
143 x_return_status := fnd_api.g_ret_sts_unexp_error;
144
145 WHEN OTHERS THEN
146 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
147
148 ROLLBACK;
149 x_return_status := fnd_api.g_ret_sts_unexp_error;
150
151 END Create_ServicePlan;
152 --===================================================================
153 -- NAME
154 -- Create_IOCoverages
155 --
156 -- PURPOSE
157 -- Private api to create coverages.
158 --
159 -- NOTES
160 -- 1. Work blending Admin will use this procedure to create coverages
161 --
162 --
163 -- HISTORY
164 -- 30-July-2003 GPAGADAL Created
165
166 --===================================================================
167
168 PROCEDURE Create_IOCoverages ( x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_count OUT NOCOPY NUMBER,
170 x_msg_data OUT NOCOPY VARCHAR2,
171 rec_obj IN SYSTEM.IEB_SERVICE_COVERAGES_OBJ
172 ) as
173
174
175 l_language VARCHAR2(4);
176
177 l_source_lang VARCHAR2(4);
178
179 l_return_status VARCHAR2(4);
180
181 l_msg_count NUMBER(2);
182
183 l_svc_plan_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
184
185 l_isvccov_id IEB_INB_SVC_COVERAGES.ISVCCOV_ID%type;
186
187 l_osvccov_id IEB_OUTB_SVC_COVERAGES.OSVCCOV_ID%type;
188
189 l_begin_time IEB_OUTB_SVC_COVERAGES.BEGIN_TIME_HHMM%type;
190
191 l_end_time IEB_OUTB_SVC_COVERAGES.END_TIME_HHMM%type;
192
193 l_minagent IEB_SERVICE_LEVELS_B.MIN_AGENTS%type;
194
195 l_quota IEB_SERVICE_LEVELS_B.HOURLY_QUOTA%type;
196
197 l_level_id IEB_SERVICE_LEVELS_B.SERVICE_LEVEL_ID%type;
198
199 l_max_wait_time IEB_SERVICE_LEVELS_B.MAX_WAIT_TIME%type;
200
201 l_percentage IEB_SERVICE_LEVELS_B.GOAL_PERCENT%type;
202
203 l_time_threshold IEB_SERVICE_LEVELS_B.GOAL_TIME%type;
204
205 l_reroute_time IEB_SERVICE_LEVELS_B.REROUTE_TIME%type;
206
207 l_reroute_war_time IEB_SERVICE_LEVELS_B.REROUTE_WARNING_TIME%type;
208 l_temp_str varchar2(80);
209
210
211 BEGIN
212
213
214 fnd_msg_pub.delete_msg();
215 x_return_status := fnd_api.g_ret_sts_success;
216 FND_MSG_PUB.initialize;
217 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
218 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
219 x_msg_data := '';
220 x_msg_count := 0;
221 l_temp_str := null;
222
223 if ( rec_obj.direction = 'O') then
224
225 select b.HOURLY_QUOTA, b.MIN_AGENTS
226 into l_quota, l_minagent from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
227 where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
228 and tl.LANGUAGE = l_language
229 and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;
230
231 select IEB_SVC_COV_S2.nextval into l_osvccov_id from dual;
232
233 if l_quota IS NULL then
234 l_quota :=0;
235 end if;
236 if l_minagent IS NULL then
237 l_minagent := 0;
238 end if;
239
240 l_quota := ((rec_obj.end_time - rec_obj.start_time)/100)*l_quota;
241
242 EXECUTE immediate 'INSERT into IEB_OUTB_SVC_COVERAGES '||
243 ' ( OSVCCOV_ID, '||
244 ' CREATED_BY, '||
245 ' CREATION_DATE, '||
246 ' LAST_UPDATED_BY, '||
247 ' LAST_UPDATE_DATE, '||
248 ' LAST_UPDATE_LOGIN,'||
249 ' SCHEDULE_TYPE, '||
250 ' REGULAR_SCHD_DAY, '||
251 ' SPEC_SCHD_DATE, '||
252 ' BEGIN_TIME_HHMM, '||
253 ' END_TIME_HHMM, '||
254 ' MIN_AGENT, '||
255 ' QUOTA, '||
256 ' SVCPLN_SVCPLN_ID,'||
257 ' OBJECT_VERSION_NUMBER, '||
258 ' SECURITY_GROUP_ID, '||
259 ' SERVICE_LEVEL_ID'||
260 ' ) values ( '||
261 ' :1, '||
262 ' :2, '||
263 ' :3, '||
264 ' :4, '||
265 ' :5, '||
266 ' :6, '||
267 ' :7, '||
268 ' :8, '||
269 ' :9, '||
270 ' :10, '||
271 ' :11, '||
272 ' :12, '||
273 ' :13, '||
274 ' :14, '||
275 ' :15, '||
276 ' :16, '||
277 ' :17 '||
278 ' ) '
279 USING l_osvccov_id,
280 FND_GLOBAL.USER_ID,
281 SYSDATE,
282 FND_GLOBAL.USER_ID,
283 SYSDATE,
284 FND_GLOBAL.LOGIN_ID,
285 rec_obj.schedule_type,
286 rec_obj.regular_schd_day,
287 rec_obj.spec_schd_date,
288 rec_obj.start_time,
289 rec_obj.end_time,
290 l_minagent,
291 l_quota,
292 rec_obj.splan_id,
293 0,
294 0,
295 rec_obj.slevel_id;
296
297 elsif (rec_obj.direction = 'I') then
298
299 select b.MIN_AGENTS,b.GOAL_PERCENT, b.GOAL_TIME,
300 b.MAX_WAIT_TIME,b.REROUTE_TIME, b.REROUTE_WARNING_TIME
301 into l_minagent, l_percentage,
302 l_time_threshold, l_max_wait_time, l_reroute_time,
303 l_reroute_war_time
304 from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
305 where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
306 and tl.LANGUAGE = l_language
307 and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;
308
309
310 select IEB_SVC_COV_S1.nextval into l_isvccov_id from dual;
311
312 if l_minagent IS NULL then
313 l_minagent := 0;
314 end if;
315 if l_percentage IS NULL then
316 l_percentage :=0;
317 end if;
318 if l_time_threshold IS NULL then
319 l_time_threshold := 0;
320 end if;
321
322 if l_max_wait_time IS NULL then
323 l_max_wait_time := 0;
324 end if;
325 if l_reroute_time IS NULL then
326 l_reroute_time :=1;
327 end if;
328 if l_reroute_war_time IS NULL then
329 l_reroute_war_time := 1;
330 end if;
331
332
333 EXECUTE immediate ' INSERT INTO IEB_INB_SVC_COVERAGES '||
334 ' ( ISVCCOV_ID, '||
335 ' CREATED_BY, '||
336 ' CREATION_DATE, '||
337 ' LAST_UPDATED_BY, '||
338 ' LAST_UPDATE_DATE, '||
339 ' LAST_UPDATE_LOGIN, '||
340 ' SCHEDULE_TYPE, '||
341 ' REGULAR_SCHD_DAY, '||
342 ' SPEC_SCHD_DATE, '||
343 ' BEGIN_TIME_HHMM, '||
344 ' END_TIME_HHMM, '||
345 ' MIN_AGENT, '||
346 ' PERCENTAGE, '||
347 ' TIME_THRESHOLD, '||
348 ' MAX_WAIT_TIME, '||
349 ' REROUTE_TIME, '||
350 ' REROUTE_WARNING_TIME, '||
351 ' SVCPLN_SVCPLN_ID, '||
352 ' OBJECT_VERSION_NUMBER, '||
353 ' SECURITY_GROUP_ID, '||
354 ' SERVICE_LEVEL_ID '||
355 ' ) values ( '||
356 ' :1, '||
357 ' :2, '||
358 ' :3, '||
359 ' :4, '||
360 ' :5, '||
361 ' :6, '||
362 ' :7, '||
363 ' :8, '||
364 ' :9, '||
365 ' :10, '||
366 ' :11, '||
367 ' :12, '||
368 ' :13, '||
369 ' :14, '||
370 ' :15, '||
371 ' :16, '||
372 ' :17, '||
373 ' :18, '||
374 ' :19, '||
375 ' :20, '||
376 ' :21 '||
377 ' ) '
378 USING l_isvccov_id,
379 FND_GLOBAL.USER_ID,
380 SYSDATE,
381 FND_GLOBAL.USER_ID,
382 SYSDATE,
383 FND_GLOBAL.LOGIN_ID,
384 rec_obj.schedule_type,
385 rec_obj.regular_schd_day,
386 rec_obj.spec_schd_date,
387 rec_obj.start_time,
388 rec_obj.end_time,
389 l_minagent,
390 l_percentage,
391 l_time_threshold,
392 l_max_wait_time,
393 l_reroute_time,
394 l_reroute_war_time,
395 rec_obj.splan_id,
396 0,
397 0,
398 rec_obj.slevel_id;
399 end if;
400
401 COMMIT;
402
403 EXCEPTION
404 WHEN fnd_api.g_exc_error THEN
405 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
406 ROLLBACK;
407 x_return_status := fnd_api.g_ret_sts_error;
408
409 WHEN fnd_api.g_exc_unexpected_error THEN
410 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
411 ROLLBACK;
412 x_return_status := fnd_api.g_ret_sts_unexp_error;
413
414 WHEN OTHERS THEN
415 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
416
417 ROLLBACK;
418 x_return_status := fnd_api.g_ret_sts_unexp_error;
419
420 END Create_IOCoverages;
421 --===================================================================
422 -- NAME
423 -- Update_IOCoverages
424 --
425 -- PURPOSE
426 -- Private api to create regional plan.
427 --
428 -- NOTES
429 -- 1. Work blending Admin will use this procedure to create regional plan
430 --
431 --
432 -- HISTORY
433 -- 30-July-2003 GPAGADAL Created
434
435 --===================================================================
436
437 PROCEDURE Update_IOCoverages ( x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 rec_obj IN SYSTEM.IEB_SERVICE_COVERAGES_OBJ
441 )as
442
443
444 l_language VARCHAR2(4);
445
446 l_source_lang VARCHAR2(4);
447
448 l_return_status VARCHAR2(4);
449
450 l_msg_count NUMBER(2);
451
452 l_svc_plan_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
453
454 l_isvccov_id IEB_INB_SVC_COVERAGES.ISVCCOV_ID%type;
455
456 l_osvccov_id IEB_OUTB_SVC_COVERAGES.OSVCCOV_ID%type;
457
458 l_begin_time IEB_OUTB_SVC_COVERAGES.BEGIN_TIME_HHMM%type;
459
460 l_end_time IEB_OUTB_SVC_COVERAGES.END_TIME_HHMM%type;
461
462 l_minagent IEB_SERVICE_LEVELS_B.MIN_AGENTS%type;
463
464 l_quota IEB_SERVICE_LEVELS_B.HOURLY_QUOTA%type;
465
466 l_level_id IEB_SERVICE_LEVELS_B.SERVICE_LEVEL_ID%type;
467
468 l_max_wait_time IEB_SERVICE_LEVELS_B.MAX_WAIT_TIME%type;
469
470 l_percentage IEB_SERVICE_LEVELS_B.GOAL_PERCENT%type;
471
472 l_time_threshold IEB_SERVICE_LEVELS_B.GOAL_TIME%type;
473
474 l_reroute_time IEB_SERVICE_LEVELS_B.REROUTE_TIME%type;
475
476 l_reroute_war_time IEB_SERVICE_LEVELS_B.REROUTE_WARNING_TIME%type;
477 l_temp_str varchar2(80);
478
479
480 BEGIN
481
482
483 fnd_msg_pub.delete_msg();
484 x_return_status := fnd_api.g_ret_sts_success;
485 FND_MSG_PUB.initialize;
486 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
487 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
488 x_msg_data := '';
489 x_msg_count := 0;
490 l_temp_str := null;
491 if ( rec_obj.direction = 'O') then
492
493 select b.HOURLY_QUOTA, b.MIN_AGENTS
494 into l_quota, l_minagent from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
495 where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
496 and tl.LANGUAGE = l_language
497 and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;
498
499 l_quota := ((rec_obj.end_time - rec_obj.start_time)/100)*l_quota;
500
501
502 if(rec_obj.schedule_type = 'R') then
503
504 update IEB_OUTB_SVC_COVERAGES
505 set BEGIN_TIME_HHMM = rec_obj.start_time,
506 END_TIME_HHMM = rec_obj.end_time,
507 MIN_AGENT = l_minagent,
508 QUOTA = l_quota,
509 SERVICE_LEVEL_ID = rec_obj.slevel_id,
510 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
511 LAST_UPDATE_DATE = SYSDATE,
512 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
513 where SVCPLN_SVCPLN_ID = rec_obj.splan_id
514 and SCHEDULE_TYPE = rec_obj.schedule_type
515 and REGULAR_SCHD_DAY = rec_obj.regular_schd_day;
516
517 elsif(rec_obj.schedule_type ='S') then
518
519 update IEB_OUTB_SVC_COVERAGES
520 set BEGIN_TIME_HHMM = rec_obj.start_time,
521 END_TIME_HHMM = rec_obj.end_time,
522 MIN_AGENT = l_minagent,
523 QUOTA = l_quota,
524 SERVICE_LEVEL_ID = rec_obj.slevel_id,
525 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
526 LAST_UPDATE_DATE = SYSDATE,
527 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
528 where SVCPLN_SVCPLN_ID = rec_obj.splan_id
529 and SCHEDULE_TYPE = rec_obj.schedule_type
530 and SPEC_SCHD_DATE = rec_obj.spec_schd_date;
531 end if;
532
533 elsif (rec_obj.direction = 'I') then
534
535 select b.MIN_AGENTS,b.GOAL_PERCENT, b.GOAL_TIME,
536 b.MAX_WAIT_TIME,b.REROUTE_TIME, b.REROUTE_WARNING_TIME
537 into l_minagent, l_percentage,
538 l_time_threshold, l_max_wait_time, l_reroute_time,
539 l_reroute_war_time
540 from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
541 where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
542 and tl.LANGUAGE = l_language
543 and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;
544
545 if( l_reroute_time = null) then
546 l_reroute_time :=1;
547 end if;
548 if( l_reroute_war_time = null) then
549 l_reroute_war_time := 1;
550 end if;
551
552 if(rec_obj.schedule_type = 'R') then
553
554 update IEB_INB_SVC_COVERAGES
555 set BEGIN_TIME_HHMM = rec_obj.start_time,
556 END_TIME_HHMM = rec_obj.end_time,
557 MIN_AGENT = l_minagent,
558 PERCENTAGE = l_percentage,
559 TIME_THRESHOLD = l_time_threshold,
560 MAX_WAIT_TIME = l_max_wait_time,
561 REROUTE_TIME = l_reroute_time,
562 REROUTE_WARNING_TIME = l_reroute_war_time,
563 SERVICE_LEVEL_ID = rec_obj.slevel_id,
564 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
565 LAST_UPDATE_DATE = SYSDATE,
566 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
567 where SVCPLN_SVCPLN_ID = rec_obj.splan_id
568 and SCHEDULE_TYPE = rec_obj.schedule_type
569 and REGULAR_SCHD_DAY = rec_obj.regular_schd_day;
570
571 elsif(rec_obj.schedule_type ='S') then
572
573 update IEB_INB_SVC_COVERAGES
574 set BEGIN_TIME_HHMM = rec_obj.start_time,
575 END_TIME_HHMM = rec_obj.end_time,
576 MIN_AGENT = l_minagent,
577 PERCENTAGE = l_percentage,
578 TIME_THRESHOLD = l_time_threshold,
579 MAX_WAIT_TIME = l_max_wait_time,
580 REROUTE_TIME = l_reroute_time,
581 REROUTE_WARNING_TIME = l_reroute_war_time,
582 SERVICE_LEVEL_ID = rec_obj.slevel_id,
583 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
584 LAST_UPDATE_DATE = SYSDATE,
585 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
586 where SVCPLN_SVCPLN_ID = rec_obj.splan_id
587 and SCHEDULE_TYPE = rec_obj.schedule_type
588 and SPEC_SCHD_DATE = rec_obj.spec_schd_date;
589 end if;
590 end if;
591
592 COMMIT;
593
594 EXCEPTION
595 WHEN fnd_api.g_exc_error THEN
596 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
597 ROLLBACK;
598 x_return_status := fnd_api.g_ret_sts_error;
599
600 WHEN fnd_api.g_exc_unexpected_error THEN
601 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
602 ROLLBACK;
603 x_return_status := fnd_api.g_ret_sts_unexp_error;
604
605 WHEN OTHERS THEN
606 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
607
608 ROLLBACK;
609 x_return_status := fnd_api.g_ret_sts_unexp_error;
610
611 END Update_IOCoverages;
612 --===================================================================
613 -- NAME
614 -- Create_Classification
615 --
616 -- PURPOSE
617 -- Private api to create classification.
618 --
619 -- NOTES
620 -- 1. Work blending Admin will use this procedure to create classification
621 --
622 --
623 -- HISTORY
624 -- 31-July-2003 GPAGADAL Created
625
626 --===================================================================
627
628
629
630 PROCEDURE Create_Classification( x_return_status OUT NOCOPY VARCHAR2,
631 x_msg_count OUT NOCOPY NUMBER,
632 x_msg_data OUT NOCOPY VARCHAR2,
633 p_svc_cat_id in NUMBER,
634 p_classfn_name in VARCHAR2
635 )as
636 l_language VARCHAR2(4);
637
638 l_source_lang VARCHAR2(4);
639
640 l_return_status VARCHAR2(4);
641
642 l_msg_count NUMBER(2);
643
644 l_wbscrule_id IEB_WB_SVC_CAT_RULES.WBSCRULE_ID%type;
645
646 BEGIN
647
648
649 fnd_msg_pub.delete_msg();
650 x_return_status := fnd_api.g_ret_sts_success;
651 FND_MSG_PUB.initialize;
652 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
653 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
654 x_msg_data := '';
655 x_msg_count := 0;
656
657 select IEB_SVC_CAT_RULES_S1.nextval into l_wbscrule_id from dual;
658
659 EXECUTE immediate 'INSERT into IEB_WB_SVC_CAT_RULES '||
660 '(WBSCRULE_ID, '||
661 ' RULE_TYPE, '||
662 ' CREATED_BY, '||
663 ' CREATION_DATE, '||
664 ' LAST_UPDATED_BY, '||
665 ' LAST_UPDATE_DATE, '||
666 ' LAST_UPDATE_LOGIN, '||
667 ' CLASSIFICATION, '||
668 ' SKILL_INCLUDED_Y_N, '||
669 ' DESCRIPTION, '||
670 ' WBSC_WBSC_ID, '||
671 ' OBJECT_VERSION_NUMBER, '||
672 ' SECURITY_GROUP_ID '||
673 ' ) VALUES '||
674 ' ( :1,' ||
675 ' :2, '||
676 ' :3, '||
677 ' :4, '||
678 ' :5, '||
679 ' :6, '||
680 ' :7, '||
681 ' :8, '||
682 ' :9, '||
683 ' :10, '||
684 ' :11, '||
685 ' :12, '||
686 ' :13 '||
687 ' ) '
688 USING l_wbscrule_id,
689 'C',
690 FND_GLOBAL.USER_ID,
691 SYSDATE,
692 FND_GLOBAL.USER_ID,
693 SYSDATE,
694 FND_GLOBAL.LOGIN_ID,
695 p_classfn_name,
696 'N',
697 p_classfn_name,
698 p_svc_cat_id,
699 0,
700 0;
701
702 COMMIT;
703
704 EXCEPTION
705 WHEN fnd_api.g_exc_error THEN
706 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
707 ROLLBACK;
708 x_return_status := fnd_api.g_ret_sts_error;
709
710 WHEN fnd_api.g_exc_unexpected_error THEN
711 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
712 ROLLBACK;
713 x_return_status := fnd_api.g_ret_sts_unexp_error;
714
715 WHEN OTHERS THEN
716 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
717
718 ROLLBACK;
719 x_return_status := fnd_api.g_ret_sts_unexp_error;
720
721
722 END Create_Classification;
723
724 --===================================================================
725 -- NAME
726 -- Create_CampCategory
727 --
728 -- PURPOSE
729 -- Private api to create category
730 --
731 -- NOTES
732 -- 1. Work blending Admin will use this procedure to create category
733 --
734 -- HISTORY
735 -- 1-August-2003 GPAGADAL Created
736
737 --===================================================================
738
739
740 PROCEDURE Create_CampCategory( x_return_status OUT NOCOPY VARCHAR2,
741 x_msg_count OUT NOCOPY NUMBER,
742 x_msg_data OUT NOCOPY VARCHAR2,
743 p_svc_plan_id in NUMBER,
744 p_name in VARCHAR2,
745 p_media_type_id in NUMBER
746 )as
747 l_language VARCHAR2(4);
748
749 l_source_lang VARCHAR2(4);
750
751 l_return_status VARCHAR2(4);
752
753 l_msg_count NUMBER(2);
754
755 l_wbsc_id IEB_WB_SVC_CATS.WBSC_ID%type;
756
757 l_media_type IEB_SVC_CAT_TEMPS_B.media_type%type;
758
759 l_parent_id IEB_WB_SVC_CATS.parent_id%type;
760
761 l_cpn_svr_name IEB_WB_SVC_CATS.campaign_server_name%type;
762
763 l_svr_cat_name IEB_WB_SVC_CATS.service_category_name%type;
764
765 l_time_stamp NUMBER(15);
766
767 --create cursor to get the all servers
768 cursor c_cur is
769 select s.WBSVR_ID, s.WB_SERVER_NAME, s.IEO_SERVER_ID from IEB_WB_SERVERS s;
770
771 BEGIN
772
773
774 fnd_msg_pub.delete_msg();
775 x_return_status := fnd_api.g_ret_sts_success;
776 FND_MSG_PUB.initialize;
777 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
778 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
779 x_msg_data := '';
780 x_msg_count := 0;
781
782 select media_type into l_media_type
783 from IEB_SVC_CAT_TEMPS_B where media_type_id = p_media_type_id;
784
785 for c_rec in c_cur LOOP
786
787 select parent_id into l_parent_id
788 from IEB_WB_SVC_CATS where media_type_id= p_media_type_id
789 and wbsvr_wbsvr_id= c_rec.WBSVR_ID
790 and default_flag= 'Y';
791
792 SELECT DBMS_UTILITY.GET_TIME INTO l_time_stamp FROM dual;
793
794 l_svr_cat_name := p_svc_plan_id||p_name||to_char(l_time_stamp);
795
796
797 select IEB_SVC_CATS_S1.nextval into l_wbsc_id from dual;
798
799 EXECUTE immediate 'INSERT into IEB_WB_SVC_CATS '||
800 ' (WBSC_ID, '||
801 ' CREATED_BY, '||
802 ' CREATION_DATE, '||
803 ' LAST_UPDATED_BY, '||
804 ' LAST_UPDATE_DATE, '||
805 ' LAST_UPDATE_LOGIN, '||
806 ' SERVICE_CATEGORY_NAME, '||
807 ' CAMPAIGN_SERVER_NAME, '||
808 ' ACTIVE_Y_N, '||
809 ' MEDIA_TYPE, '||
810 ' PRIORITY, '||
811 ' DEPTH, '||
812 ' WBSVR_WBSVR_ID, '||
813 ' PARENT_ID, '||
814 ' SVCPLN_SVCPLN_ID, '||
815 ' OBJECT_VERSION_NUMBER, '||
816 ' SECURITY_GROUP_ID, '||
817 ' MEDIA_TYPE_ID'||
818 ' ) values ('||
819 ' :1, '||
820 ' :2, '||
821 ' :3, '||
822 ' :4, '||
823 ' :5, '||
824 ' :6, '||
825 ' :7, '||
826 ' :8, '||
827 ' :9, '||
828 ' :10, '||
829 ' :11, '||
830 ' :12, '||
831 ' :13, '||
832 ' :14, '||
833 ' :15, '||
834 ' :16, '||
835 ' :17, '||
836 ' :18 '||
837 ' ) '
838 USING l_wbsc_id,
839 FND_GLOBAL.USER_ID,
840 SYSDATE,
841 FND_GLOBAL.USER_ID,
842 SYSDATE,
843 FND_GLOBAL.LOGIN_ID,
844 l_svr_cat_name,
845 p_name,
846 'Y',
847 l_media_type,
848 0,
849 3,
850 c_rec.WBSVR_ID,
851 l_parent_id,
852 p_svc_plan_id,
853 0,
854 0,
855 p_media_type_id;
856
857
858 end loop;
859
860
861
862 COMMIT;
863
864 EXCEPTION
865 WHEN fnd_api.g_exc_error THEN
866 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
867 ROLLBACK;
868 x_return_status := fnd_api.g_ret_sts_error;
869
870 WHEN fnd_api.g_exc_unexpected_error THEN
871 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
872 ROLLBACK;
873 x_return_status := fnd_api.g_ret_sts_unexp_error;
874
875 WHEN OTHERS THEN
876 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
877
878 ROLLBACK;
879 x_return_status := fnd_api.g_ret_sts_unexp_error;
880
881
882 END Create_CampCategory;
883
884 --===================================================================
885 -- NAME
886 -- Create_ClassfnCategory
887 --
888 -- PURPOSE
889 -- Private api to create category
890 --
891 -- NOTES
892 -- 1. Work blending Admin will use this procedure to create category
893 --
894 -- HISTORY
895 -- 5-August-2003 GPAGADAL Created
896
897 --===================================================================
898
899
900 PROCEDURE Create_ClassfnCategory( x_return_status OUT NOCOPY VARCHAR2,
901 x_msg_count OUT NOCOPY NUMBER,
902 x_msg_data OUT NOCOPY VARCHAR2,
903 p_cat_id OUT NOCOPY NUMBER,
904 p_svc_plan_id in NUMBER,
905 p_name in VARCHAR2,
906 p_media_type_id in NUMBER,
907 p_server_id IN NUMBER
908 )as
909 l_language VARCHAR2(4);
910
911 l_source_lang VARCHAR2(4);
912
913 l_return_status VARCHAR2(4);
914
915 l_msg_count NUMBER(2);
916
917 l_wbsc_id IEB_WB_SVC_CATS.WBSC_ID%type;
918
919 l_media_type IEB_SVC_CAT_TEMPS_B.media_type%type;
920
921 l_parent_id IEB_WB_SVC_CATS.parent_id%type;
922
923 l_cpn_svr_name IEB_WB_SVC_CATS.campaign_server_name%type;
924
925 l_svr_cat_name IEB_WB_SVC_CATS.service_category_name%type;
926
927 l_time_stamp NUMBER(15);
928
929 BEGIN
930
931
932 fnd_msg_pub.delete_msg();
933 x_return_status := fnd_api.g_ret_sts_success;
934 FND_MSG_PUB.initialize;
935 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
936 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
937 x_msg_data := '';
938 x_msg_count := 0;
939
940
941 select parent_id into l_parent_id
942 from IEB_WB_SVC_CATS where media_type_id= p_media_type_id
943 and wbsvr_wbsvr_id= p_server_id
944 and default_flag= 'Y';
945
946 select media_type into l_media_type
947 from IEB_SVC_CAT_TEMPS_B where media_type_id = p_media_type_id;
948
949
950 SELECT DBMS_UTILITY.GET_TIME INTO l_time_stamp FROM dual;
951
952 l_svr_cat_name := p_svc_plan_id||p_name||to_char(l_time_stamp);
953
954 select IEB_SVC_CATS_S1.nextval into l_wbsc_id from dual;
955
956 EXECUTE immediate 'INSERT into IEB_WB_SVC_CATS '||
957 ' (WBSC_ID, '||
958 ' CREATED_BY, '||
959 ' CREATION_DATE, '||
960 ' LAST_UPDATED_BY, '||
961 ' LAST_UPDATE_DATE, '||
962 ' LAST_UPDATE_LOGIN, '||
963 ' SERVICE_CATEGORY_NAME, '||
964 ' ACTIVE_Y_N, '||
965 ' MEDIA_TYPE, '||
966 ' PRIORITY, '||
967 ' DEPTH, '||
968 ' WBSVR_WBSVR_ID, '||
969 ' PARENT_ID, '||
970 ' SVCPLN_SVCPLN_ID, '||
971 ' OBJECT_VERSION_NUMBER, '||
972 ' SECURITY_GROUP_ID, '||
973 ' MEDIA_TYPE_ID'||
974 ' ) values ('||
975 ' :1, '||
976 ' :2, '||
977 ' :3, '||
978 ' :4, '||
979 ' :5, '||
980 ' :6, '||
981 ' :7, '||
982 ' :8, '||
983 ' :9, '||
984 ' :10, '||
985 ' :11, '||
986 ' :12, '||
987 ' :13, '||
988 ' :14, '||
989 ' :15, '||
990 ' :16, '||
991 ' :17 '||
992 ' ) '
993 USING l_wbsc_id,
994 FND_GLOBAL.USER_ID,
995 SYSDATE,
996 FND_GLOBAL.USER_ID,
997 SYSDATE,
998 FND_GLOBAL.LOGIN_ID,
999 l_svr_cat_name,
1000 'Y',
1001 l_media_type,
1002 0,
1003 3,
1004 p_server_id,
1005 l_parent_id,
1006 p_svc_plan_id,
1007 0,
1008 0,
1009 p_media_type_id;
1010
1011
1012 p_cat_id := l_wbsc_id;
1013
1014
1015 COMMIT;
1016
1017 EXCEPTION
1018 WHEN fnd_api.g_exc_error THEN
1019 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
1020 ROLLBACK;
1021 x_return_status := fnd_api.g_ret_sts_error;
1022
1023 WHEN fnd_api.g_exc_unexpected_error THEN
1024 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1025 ROLLBACK;
1026 x_return_status := fnd_api.g_ret_sts_unexp_error;
1027
1028 WHEN OTHERS THEN
1029 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
1030
1031 ROLLBACK;
1032 x_return_status := fnd_api.g_ret_sts_unexp_error;
1033
1034 END Create_ClassfnCategory;
1035
1036 --===================================================================
1037 -- NAME
1038 -- Create_RegionalPlan
1039 --
1040 -- PURPOSE
1041 -- Private api to create regional plan.
1042 --
1043 -- NOTES
1044 -- 1. Work blending Admin will use this procedure to create regional plan
1045 --
1046 --
1047 -- HISTORY
1048 -- 24-July-2003 GPAGADAL Created
1049
1050 --===================================================================
1051
1052 PROCEDURE Create_RegionalPlan( x_return_status OUT NOCOPY VARCHAR2,
1053 x_msg_count OUT NOCOPY NUMBER,
1054 x_msg_data OUT NOCOPY VARCHAR2,
1055 p_plan_id OUT NOCOPY NUMBER,
1056 p_base_plan_id in NUMBER,
1057 p_name IN VARCHAR2,
1058 p_desc IN VARCHAR2,
1059 p_direction IN VARCHAR2,
1060 p_media_type_id IN NUMBER
1061 )as
1062
1063
1064 l_language VARCHAR2(4);
1065
1066 l_source_lang VARCHAR2(4);
1067
1068 l_return_status VARCHAR2(4);
1069
1070 l_msg_count NUMBER(2);
1071
1072 l_svc_plan_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
1073
1074 BEGIN
1075
1076
1077 fnd_msg_pub.delete_msg();
1078 x_return_status := fnd_api.g_ret_sts_success;
1079 FND_MSG_PUB.initialize;
1080 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1081 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1082 x_msg_data := '';
1083 x_msg_count := 0;
1084
1085
1086 IEB_ServicePlan_PVT.Create_ServicePlan(x_return_status,
1087 x_msg_count,
1088 x_msg_data,
1089 l_svc_plan_id,
1090 p_name,
1091 p_desc,
1092 p_direction,
1093 p_media_type_id);
1094
1095
1096 EXECUTE immediate 'INSERT into IEB_REGIONAL_PLANS '||
1097 '(SERVICE_PLAN_ID, '||
1098 ' CREATED_BY, '||
1099 ' CREATION_DATE, '||
1100 ' LAST_UPDATED_BY, '||
1101 ' LAST_UPDATE_DATE, '||
1102 ' LAST_UPDATE_LOGIN, '||
1103 ' BASE_PLAN_ID, '||
1104 ' OBJECT_VERSION_NUMBER, '||
1105 ' SECURITY_GROUP_ID '||
1106 ' ) VALUES '||
1107 ' (:1, '||
1108 ' :2, '||
1109 ' :3, '||
1110 ' :4, '||
1111 ' :5, '||
1112 ' :6, '||
1113 ' :7, '||
1114 ' :8, '||
1115 ' :9'||
1116 ') '
1117 USING l_svc_plan_id,
1118 FND_GLOBAL.USER_ID,
1119 SYSDATE,
1120 FND_GLOBAL.USER_ID,
1121 SYSDATE,
1122 FND_GLOBAL.LOGIN_ID,
1123 p_base_plan_id,
1124 0,
1125 0;
1126
1127 p_plan_id := l_svc_plan_id;
1128
1129 COMMIT;
1130
1131 EXCEPTION
1132 WHEN fnd_api.g_exc_error THEN
1133 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
1134 ROLLBACK;
1135 x_return_status := fnd_api.g_ret_sts_error;
1136
1137 WHEN fnd_api.g_exc_unexpected_error THEN
1138 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1139 ROLLBACK;
1140 x_return_status := fnd_api.g_ret_sts_unexp_error;
1141
1142 WHEN OTHERS THEN
1143 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
1144
1145 ROLLBACK;
1146 x_return_status := fnd_api.g_ret_sts_unexp_error;
1147
1148
1149 END Create_RegionalPlan;
1150
1151 --===================================================================
1152 -- NAME
1153 -- Create_GroupPlanMap
1154 --
1155 -- PURPOSE
1156 -- Private api to create group map.
1157 --
1158 -- NOTES
1159 -- 1. Work blending Admin will use this procedure to create group map
1160 --
1161 --
1162 -- HISTORY
1163 -- 24-July-2003 GPAGADAL Created
1164
1165 --===================================================================
1166 PROCEDURE Create_GroupPlanMap( x_return_status OUT NOCOPY VARCHAR2,
1167 x_msg_count OUT NOCOPY NUMBER,
1168 x_msg_data OUT NOCOPY VARCHAR2,
1169 p_plan_id in NUMBER,
1170 p_server_group_id IN NUMBER
1171 )as
1172
1173
1174 l_language VARCHAR2(4);
1175
1176 l_source_lang VARCHAR2(4);
1177
1178 l_return_status VARCHAR2(4);
1179
1180 l_msg_count NUMBER(2);
1181
1182 l_svc_plan_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
1183
1184 l_map_id IEB_GROUP_PLAN_MAPS.MAP_ID%type;
1185
1186 BEGIN
1187
1188
1189 fnd_msg_pub.delete_msg();
1190 x_return_status := fnd_api.g_ret_sts_success;
1191 FND_MSG_PUB.initialize;
1192 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1193 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1194 x_msg_data := '';
1195 x_msg_count := 0;
1196
1197
1198 select IEB_GROUP_PLAN_MAPS_S1.nextval into l_map_id from dual;
1199 EXECUTE immediate 'INSERT INTO IEB_GROUP_PLAN_MAPS '||
1200 '(MAP_ID, '||
1201 ' SERVICE_PLAN_ID, '||
1202 ' CREATED_BY, '||
1203 ' CREATION_DATE, '||
1204 ' LAST_UPDATED_BY, '||
1205 ' LAST_UPDATE_DATE, '||
1206 ' LAST_UPDATE_LOGIN, '||
1207 ' SERVER_GROUP_ID, '||
1208 ' OBJECT_VERSION_NUMBER, '||
1209 ' SECURITY_GROUP_ID '||
1210 ' ) VALUES '||
1211 ' ( :1, '||
1212 ' :2, '||
1213 ' :3, '||
1214 ' :4, '||
1215 ' :5, '||
1216 ' :6, '||
1217 ' :7, '||
1218 ' :8, '||
1219 ' :9, '||
1220 ' :10 '||
1221 ') '
1222 USING l_map_id,
1223 p_plan_id,
1224 FND_GLOBAL.USER_ID,
1225 SYSDATE,
1226 FND_GLOBAL.USER_ID,
1227 SYSDATE,
1228 FND_GLOBAL.LOGIN_ID,
1229 p_server_group_id,
1230 0,
1231 0 ;
1232
1233
1234
1235
1236 COMMIT;
1237
1238 EXCEPTION
1239 WHEN fnd_api.g_exc_error THEN
1240 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
1241 ROLLBACK;
1242 x_return_status := fnd_api.g_ret_sts_error;
1243
1244 WHEN fnd_api.g_exc_unexpected_error THEN
1245 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1246 ROLLBACK;
1247 x_return_status := fnd_api.g_ret_sts_unexp_error;
1248
1249 WHEN OTHERS THEN
1250 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
1251
1252 ROLLBACK;
1253 x_return_status := fnd_api.g_ret_sts_unexp_error;
1254
1255 END Create_GroupPlanMap;
1256
1257 --===================================================================
1258 -- NAME
1259 -- Update_Category
1260 --
1261 -- PURPOSE
1262 -- Private api to update category.
1263 --
1264 -- NOTES
1265 -- 1. Work blending Admin will use this procedure to update category
1266 --
1267 --
1268 -- HISTORY
1269 -- 3-Sep-2003 GPAGADAL Created
1270
1271 --===================================================================
1272
1273
1274 PROCEDURE Update_Category ( x_return_status OUT NOCOPY VARCHAR2,
1275 x_msg_count OUT NOCOPY NUMBER,
1276 x_msg_data OUT NOCOPY VARCHAR2,
1277 p_base_plan_id in NUMBER,
1278 p_media_type_id in NUMBER,
1279 p_reg_plan_id in NUMBER
1280 )
1281 as
1282 l_language VARCHAR2(4);
1283
1284 l_source_lang VARCHAR2(4);
1285
1286 l_return_status VARCHAR2(4);
1287
1288 l_msg_count NUMBER(2);
1289
1290
1291 BEGIN
1292
1293
1294 fnd_msg_pub.delete_msg();
1295 x_return_status := fnd_api.g_ret_sts_success;
1296 FND_MSG_PUB.initialize;
1297 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1298 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1299 x_msg_data := '';
1300
1301
1302 update IEB_WB_SVC_CATS set
1303 svcpln_svcpln_id = p_reg_plan_id,
1304 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1305 LAST_UPDATE_DATE = SYSDATE,
1306 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1307 where MEDIA_TYPE_ID = p_media_type_id
1308 and svcpln_svcpln_id = p_base_plan_id;
1309
1310
1311 COMMIT;
1312
1313 EXCEPTION
1314 WHEN fnd_api.g_exc_error THEN
1315 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
1316 ROLLBACK;
1317 x_return_status := fnd_api.g_ret_sts_error;
1318
1319 WHEN fnd_api.g_exc_unexpected_error THEN
1320 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1321 ROLLBACK;
1322 x_return_status := fnd_api.g_ret_sts_unexp_error;
1323
1324 WHEN OTHERS THEN
1325 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
1326
1327 ROLLBACK;
1328 x_return_status := fnd_api.g_ret_sts_unexp_error;
1329
1330 END Update_Category;
1331
1332
1333
1334 --===================================================================
1335 -- NAME
1336 -- Create_ServiceLevel
1337 --
1338 -- PURPOSE
1339 -- Private api to create service level.
1340 --
1341 -- NOTES
1342 -- 1. Work blending Admin will use this procedure to create service level
1343 --
1344 --
1345 -- HISTORY
1346 -- 11-July-2003 GPAGADAL Created
1347
1348 --===================================================================
1349 PROCEDURE Create_ServiceLevel ( x_return_status OUT NOCOPY VARCHAR2,
1350 x_msg_count OUT NOCOPY NUMBER,
1351 x_msg_data OUT NOCOPY VARCHAR2,
1352 rec_obj IN SYSTEM.IEB_SERVICE_LEVELS_OBJ
1353 )as
1354 l_language VARCHAR2(4);
1355
1356 l_source_lang VARCHAR2(4);
1357
1358 l_return_status VARCHAR2(4);
1359
1360 l_msg_count NUMBER(2);
1361
1362 l_service_lvl_id IEB_SERVICE_LEVELS_B.SERVICE_LEVEL_ID%type;
1363 l_temp_str varchar2(80);
1364
1365 BEGIN
1366
1367
1368 fnd_msg_pub.delete_msg();
1369 x_return_status := fnd_api.g_ret_sts_success;
1370 FND_MSG_PUB.initialize;
1371 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1372 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1373 x_msg_data := '';
1374 x_msg_count := 0;
1375 l_temp_str := null;
1376
1377
1378 select IEB_SVC_LEVEL_S1.nextval into l_service_lvl_id from dual;
1379
1380 EXECUTE immediate 'insert into IEB_SERVICE_LEVELS_B '||
1381 '(SERVICE_LEVEL_ID, '||
1382 ' CREATED_BY, '||
1383 ' CREATION_DATE, '||
1384 ' LAST_UPDATED_BY, '||
1385 ' LAST_UPDATE_DATE, '||
1386 ' LAST_UPDATE_LOGIN, '||
1387 ' DIRECTION, '||
1388 ' MANDATORY_FLAG, '||
1389 ' HOURLY_QUOTA, '||
1390 ' MIN_AGENTS, '||
1391 ' GOAL_PERCENT, '||
1392 ' GOAL_TIME, '||
1393 ' MAX_WAIT_TIME, '||
1394 ' REROUTE_TIME, '||
1395 ' REROUTE_WARNING_TIME, '||
1396 ' OBJECT_VERSION_NUMBER, '||
1397 ' SECURITY_GROUP_ID '||
1398 ' ) values '||
1399 ' (:1, '||
1400 ' :2, '||
1401 ' :3, '||
1402 ' :4, '||
1403 ' :5, '||
1404 ' :6, '||
1405 ' :7, '||
1406 ' :8, '||
1407 ' :9, '||
1408 ' :10, '||
1409 ' :11, '||
1410 ' :12, '||
1411 ' :13, '||
1412 ' :14, '||
1413 ' :15, '||
1414 ' :16, '||
1415 ' :17 '||
1416 ') '
1417 USING l_service_lvl_id,
1418 FND_GLOBAL.USER_ID,
1419 SYSDATE,
1420 FND_GLOBAL.USER_ID,
1421 SYSDATE,
1422 FND_GLOBAL.LOGIN_ID,
1423 rec_obj.direction,
1424 l_temp_str,
1425 rec_obj.hourly_quota,
1426 rec_obj.min_agents,
1427 rec_obj.goal_percent,
1428 rec_obj.goal_time,
1429 rec_obj.max_wait_time,
1430 rec_obj.reroute_time,
1431 rec_obj.reroute_warning_time,
1432 0,
1433 0;
1434
1435
1436
1437
1438
1439 insert into IEB_SERVICE_LEVELS_TL (
1440 SERVICE_LEVEL_ID,
1441 CREATED_BY,
1442 CREATION_DATE,
1443 LAST_UPDATED_BY,
1444 LAST_UPDATE_DATE,
1445 LAST_UPDATE_LOGIN,
1446 LEVEL_NAME,
1447 DESCRIPTION,
1448 OBJECT_VERSION_NUMBER,
1449 SECURITY_GROUP_ID,
1450 LANGUAGE,
1451 SOURCE_LANG
1452 ) select
1453 l_service_lvl_id,
1454 FND_GLOBAL.USER_ID,
1455 SYSDATE,
1456 FND_GLOBAL.USER_ID,
1457 SYSDATE,
1458 FND_GLOBAL.LOGIN_ID,
1459 LTRIM(RTRIM(rec_obj.level_name)),
1460 LTRIM(RTRIM(rec_obj.level_description)),
1461 0,
1462 0,
1463 L.LANGUAGE_CODE,
1464 userenv('LANG')
1465 from FND_LANGUAGES L
1466 where L.INSTALLED_FLAG in ('I', 'B')
1467 and not exists
1468 (select NULL
1469 from IEB_SERVICE_LEVELS_TL T
1470 where T.SERVICE_LEVEL_ID = l_service_lvl_id
1471 and T.LANGUAGE = L.LANGUAGE_CODE);
1472
1473
1474 COMMIT;
1475
1476 EXCEPTION
1477 WHEN fnd_api.g_exc_error THEN
1478 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
1479 ROLLBACK;
1480 x_return_status := fnd_api.g_ret_sts_error;
1481
1482 WHEN fnd_api.g_exc_unexpected_error THEN
1483 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1484 ROLLBACK;
1485 x_return_status := fnd_api.g_ret_sts_unexp_error;
1486
1487 WHEN OTHERS THEN
1488 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
1489
1490 ROLLBACK;
1491 x_return_status := fnd_api.g_ret_sts_unexp_error;
1492
1493 END Create_ServiceLevel;
1494
1495
1496 --===================================================================
1497 -- NAME
1498 -- Update_ServiceLevel
1499 --
1500 -- PURPOSE
1501 -- Private api to update service level.
1502 --
1503 -- NOTES
1504 -- 1. Work blending Admin will use this procedure to update service level
1505 --
1506 --
1507 -- HISTORY
1508 -- 11-July-2003 GPAGADAL Created
1509
1510 --===================================================================
1511 PROCEDURE Update_ServiceLevel ( x_return_status OUT NOCOPY VARCHAR2,
1512 x_msg_count OUT NOCOPY NUMBER,
1513 x_msg_data OUT NOCOPY VARCHAR2,
1514 rec_obj IN SYSTEM.IEB_SERVICE_LEVELS_OBJ
1515 )
1516
1517 as
1518 l_language VARCHAR2(4);
1519
1520 l_source_lang VARCHAR2(4);
1521
1522 l_return_status VARCHAR2(4);
1523
1524 l_msg_count NUMBER(2);
1525
1526 l_service_lvl_id IEB_SERVICE_LEVELS_B.SERVICE_LEVEL_ID%type;
1527
1528 BEGIN
1529
1530
1531 fnd_msg_pub.delete_msg();
1532 x_return_status := fnd_api.g_ret_sts_success;
1533 FND_MSG_PUB.initialize;
1534 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1535 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1536 x_msg_data := '';
1537
1538 if (rec_obj.direction = 'I') then
1539 -- dbms_output.put_line('inbound');
1540 update IEB_SERVICE_LEVELS_B set
1541 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1542 LAST_UPDATE_DATE = SYSDATE,
1543 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1544 GOAL_PERCENT = rec_obj.goal_percent,
1545 GOAL_TIME = rec_obj.goal_time,
1546 MAX_WAIT_TIME = rec_obj.max_wait_time,
1547 MIN_AGENTS= rec_obj.min_agents
1548 where
1549 SERVICE_LEVEL_ID = rec_obj.service_level_id;
1550
1551 elsif (rec_obj.direction = 'O') then
1552 -- dbms_output.put_line('outbound');
1553 update IEB_SERVICE_LEVELS_B set
1554 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1555 LAST_UPDATE_DATE = SYSDATE,
1556 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1557 HOURLY_QUOTA = rec_obj.hourly_quota,
1558 MIN_AGENTS= rec_obj.min_agents
1559 where
1560 SERVICE_LEVEL_ID = rec_obj.service_level_id;
1561
1562
1563
1564 end if;
1565
1566 COMMIT;
1567
1568 EXCEPTION
1569 WHEN fnd_api.g_exc_error THEN
1570 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
1571 ROLLBACK;
1572 x_return_status := fnd_api.g_ret_sts_error;
1573
1574 WHEN fnd_api.g_exc_unexpected_error THEN
1575 -- DBMS_OUTPUT.PUT_LINE('unexpected Error : '||sqlerrm);
1576 ROLLBACK;
1577 x_return_status := fnd_api.g_ret_sts_unexp_error;
1578
1579 WHEN OTHERS THEN
1580 -- DBMS_OUTPUT.PUT_LINE('other Error : '||sqlerrm);
1581
1582 ROLLBACK;
1583 x_return_status := fnd_api.g_ret_sts_unexp_error;
1584
1585 END Update_ServiceLevel;
1586
1587
1588
1589
1590 --===================================================================
1591 -- NAME
1592 -- Delete_Service_Level
1593 --
1594 -- PURPOSE
1595 -- Private api to delete service level
1596 --
1597 -- NOTES
1598 -- 1. Work blending Admin will use this procedure to delete service level
1599 --
1600 --
1601 -- HISTORY
1602 -- 09-July-2003 GPAGADAL Created
1603 --===================================================================
1604
1605
1606 PROCEDURE Delete_Service_Level( x_return_status OUT NOCOPY VARCHAR2,
1607 x_msg_count OUT NOCOPY NUMBER,
1608 x_msg_data OUT NOCOPY VARCHAR2,
1609 x_service_level_id IN NUMBER
1610 )
1611 is
1612
1613 l_language VARCHAR2(4);
1614
1615 l_service_level_id IEB_SERVICE_LEVELS_B.SERVICE_LEVEL_ID%type;
1616
1617 BEGIN
1618
1619 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1620 x_return_status := fnd_api.g_ret_sts_success;
1621 x_msg_count := 0;
1622 x_msg_data := '';
1623
1624
1625 EXECUTE immediate
1626 'delete from IEB_SERVICE_LEVELS_TL '||
1627 ' where SERVICE_LEVEL_ID = :1 and language= :2'
1628 USING x_service_level_id, l_language;
1629
1630 if (sql%notfound) then
1631 null;
1632 end if;
1633
1634 EXECUTE immediate
1635 ' delete from IEB_SERVICE_LEVELS_B '||
1636 ' where SERVICE_LEVEL_ID = :1'
1637 USING x_service_level_id;
1638
1639 if (sql%notfound) then
1640 null;
1641 end if;
1642
1643 COMMIT;
1644 EXCEPTION
1645 WHEN others THEN
1646 -- dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1647 ROLLBACK;
1648 x_return_status := fnd_api.g_ret_sts_unexp_error;
1649
1650
1651
1652
1653 END Delete_Service_Level;
1654
1655
1656 --===================================================================
1657 -- NAME
1658 -- Delete_Service_Plan
1659 --
1660 -- PURPOSE
1661 -- Private api to delete service plan
1662 --
1663 -- NOTES
1664 -- 1. Work blending Admin will use this procedure to delete service plan
1665 --
1666 --
1667 -- HISTORY
1668 -- 24-July-2003 GPAGADAL Created
1669 --===================================================================
1670
1671 PROCEDURE Delete_Service_Plan ( x_return_status OUT NOCOPY VARCHAR2,
1672 x_msg_count OUT NOCOPY NUMBER,
1673 x_msg_data OUT NOCOPY VARCHAR2,
1674 x_service_plan_id IN NUMBER
1675 )
1676 as
1677
1678
1679 l_service_plan_b_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
1680
1681 l_service_plan_tl_id IEB_SERVICE_PLANS_TL.SERVICE_PLAN_ID%type;
1682 l_language VARCHAR2(4);
1683
1684
1685 BEGIN
1686
1687 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1688 x_return_status := fnd_api.g_ret_sts_success;
1689 x_msg_count := 0;
1690 x_msg_data := '';
1691
1692 EXECUTE immediate
1693 ' delete from IEB_SERVICE_PLANS where SVCPLN_ID = :1 or SVCPLN_ID in ( select SERVICE_PLAN_ID from ieb_regional_plans where base_plan_id = :2)'
1694 USING x_service_plan_id, x_service_plan_id;
1695 if (sql%notfound) then
1696 null;
1697 end if;
1698
1699 EXECUTE immediate
1700 ' delete from IEB_SERVICE_PLANS_TL where SERVICE_PLAN_ID = :1 or SERVICE_PLAN_ID in ( select SERVICE_PLAN_ID from ieb_regional_plans where base_plan_id =:2)'
1701 USING x_service_plan_id, x_service_plan_id;
1702
1703 if (sql%notfound) then
1704 null;
1705 end if;
1706
1707
1708
1709 EXECUTE immediate
1710 ' delete from ieb_group_plan_maps where service_plan_id = :1 or service_plan_id in (select SERVICE_PLAN_ID from ieb_regional_plans where base_plan_id =:2)'
1711 USING x_service_plan_id, x_service_plan_id ;
1712
1713 if (sql%notfound) then
1714 null;
1715 end if;
1716
1717
1718 EXECUTE immediate
1719 ' delete from IEB_INB_SVC_COVERAGES where SVCPLN_SVCPLN_ID = :1 or SVCPLN_SVCPLN_ID in (select SERVICE_PLAN_ID from ieb_regional_plans where base_plan_id =:2)'
1720 USING x_service_plan_id, x_service_plan_id;
1721
1722 if (sql%notfound) then
1723 null;
1724 end if;
1725
1726 EXECUTE immediate
1727 ' delete from IEB_OUTB_SVC_COVERAGES where SVCPLN_SVCPLN_ID = :1 or SVCPLN_SVCPLN_ID in (select SERVICE_PLAN_ID from ieb_regional_plans where base_plan_id =:2)'
1728 USING x_service_plan_id, x_service_plan_id;
1729
1730 if (sql%notfound) then
1731 null;
1732 end if;
1733
1734 EXECUTE immediate
1735 ' delete from ieb_regional_plans where base_plan_id = :1'
1736 USING x_service_plan_id;
1737
1738 if (sql%notfound) then
1739 null;
1740 end if;
1741
1742
1743
1744 COMMIT;
1745 EXCEPTION
1746 WHEN others THEN
1747 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1748 ROLLBACK;
1749 x_return_status := fnd_api.g_ret_sts_unexp_error;
1750
1751
1752 END Delete_Service_Plan;
1753
1754 --===================================================================
1755 -- NAME
1756 -- Delete_Classification
1757 --
1758 -- PURPOSE
1759 -- Private api to delete classifications
1760 --
1761 -- NOTES
1762 -- 1. Work blending Admin will use this procedure to delete classifications
1763 --
1764 --
1765 -- HISTORY
1766 -- 1-August-2003 GPAGADAL Created
1767 --===================================================================
1768
1769 PROCEDURE Delete_Classification( x_return_status OUT NOCOPY VARCHAR2,
1770 x_msg_count OUT NOCOPY NUMBER,
1771 x_msg_data OUT NOCOPY VARCHAR2,
1772 p_svc_plan_id in NUMBER,
1773 p_media_type_id in NUMBER
1774 )
1775 as
1776
1777
1778 l_service_plan_b_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
1779
1780 l_service_plan_tl_id IEB_SERVICE_PLANS_TL.SERVICE_PLAN_ID%type;
1781 l_language VARCHAR2(4);
1782
1783
1784 BEGIN
1785
1786 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1787 x_return_status := fnd_api.g_ret_sts_success;
1788 x_msg_count := 0;
1789 x_msg_data := '';
1790
1791 EXECUTE immediate
1792 ' delete from IEB_WB_SVC_CAT_RULES '||
1793 ' where WBSC_WBSC_ID in (select distinct c.WBSC_ID from '||
1794 ' IEB_WB_SVC_CATS c,IEB_WB_SVC_CAT_RULES r , IEB_REGIONAL_PLANS p '||
1795 ' where c.WBSC_ID = r.WBSC_WBSC_ID '||
1796 ' and (c.SVCPLN_SVCPLN_ID = :1 or p.BASE_PLAN_ID = :2) '||
1797 ' and c.MEDIA_TYPE_ID = :3 '||
1798 ' and p.SERVICE_PLAN_ID (+)= c.SVCPLN_SVCPLN_ID ) '
1799 USING p_svc_plan_id,p_svc_plan_id, p_media_type_id;
1800
1801 if (sql%notfound) then
1802 null;
1803 end if;
1804
1805 EXECUTE immediate
1806 ' delete from IEB_WB_SVC_CATS s '||
1807 ' where s.WBSC_ID in ( select c.WBSC_ID from '||
1808 ' IEB_WB_SVC_CATS c, IEB_REGIONAL_PLANS p '||
1809 ' where c.MEDIA_TYPE_ID = :1'||
1810 ' and (c.svcpln_svcpln_id = :2 or p.BASE_PLAN_ID = :3) '||
1811 ' and p.SERVICE_PLAN_ID (+)= c.SVCPLN_SVCPLN_ID ) '
1812 USING p_media_type_id, p_svc_plan_id, p_svc_plan_id;
1813
1814 if (sql%notfound) then
1815 null;
1816 end if;
1817
1818 COMMIT;
1819 EXCEPTION
1820 WHEN others THEN
1821 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1822 ROLLBACK;
1823 x_return_status := fnd_api.g_ret_sts_unexp_error;
1824
1825
1826 END Delete_Classification;
1827
1828 --===================================================================
1829 -- NAME
1830 -- Delete_Category
1831 --
1832 -- PURPOSE
1833 -- Private api to delete service categories
1834 --
1835 -- NOTES
1836 -- 1. Work blending Admin will use this procedure to delete service categories
1837 --
1838 --
1839 -- HISTORY
1840 -- 1-August-2003 GPAGADAL Created
1841 --===================================================================
1842
1843 PROCEDURE Delete_Category( x_return_status OUT NOCOPY VARCHAR2,
1844 x_msg_count OUT NOCOPY NUMBER,
1845 x_msg_data OUT NOCOPY VARCHAR2,
1846 p_svc_plan_id in NUMBER,
1847 p_media_type_id in NUMBER
1848 ) as
1849
1850
1851 l_service_plan_b_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
1852
1853 l_service_plan_tl_id IEB_SERVICE_PLANS_TL.SERVICE_PLAN_ID%type;
1854 l_language VARCHAR2(4);
1855
1856
1857 BEGIN
1858
1859 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1860 x_return_status := fnd_api.g_ret_sts_success;
1861 x_msg_count := 0;
1862 x_msg_data := '';
1863
1864 EXECUTE immediate
1865 ' delete from IEB_WB_SVC_CATS s '||
1866 ' where s.WBSC_ID in ( select c.WBSC_ID from '||
1867 ' IEB_WB_SVC_CATS c, IEB_REGIONAL_PLANS p '||
1868 ' where c.MEDIA_TYPE_ID = :1'||
1869 ' and (c.svcpln_svcpln_id = :2 or p.BASE_PLAN_ID = :3) '||
1870 ' and p.SERVICE_PLAN_ID (+)= c.SVCPLN_SVCPLN_ID ) '
1871 USING p_media_type_id, p_svc_plan_id, p_svc_plan_id;
1872
1873 if (sql%notfound) then
1874 null;
1875 end if;
1876
1877
1878 COMMIT;
1879 EXCEPTION
1880 WHEN others THEN
1881 -- dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1882 ROLLBACK;
1883 x_return_status := fnd_api.g_ret_sts_unexp_error;
1884
1885
1886 END Delete_Category;
1887 --===================================================================
1888 -- NAME
1889 -- Delete_IOCoverages
1890 --
1891 -- PURPOSE
1892 -- Private api to delete service plan coverages
1893 --
1894 -- NOTES
1895 -- 1. Work blending Admin will use this procedure to delete service plan coverages
1896 --
1897 --
1898 -- HISTORY
1899 -- 4-August-2003 GPAGADAL Created
1900 --===================================================================
1901
1902 PROCEDURE Delete_IOCoverages ( x_return_status OUT NOCOPY VARCHAR2,
1903 x_msg_count OUT NOCOPY NUMBER,
1904 x_msg_data OUT NOCOPY VARCHAR2,
1905 p_direction IN VARCHAR2,
1906 p_plan_id IN VARCHAR2
1907 )as
1908
1909
1910 l_service_plan_b_id IEB_SERVICE_PLANS.SVCPLN_ID%type;
1911
1912 l_service_plan_tl_id IEB_SERVICE_PLANS_TL.SERVICE_PLAN_ID%type;
1913 l_language VARCHAR2(4);
1914
1915
1916 BEGIN
1917
1918 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1919 x_return_status := fnd_api.g_ret_sts_success;
1920 x_msg_count := 0;
1921 x_msg_data := '';
1922
1923 if( p_direction = 'I') then
1924
1925 EXECUTE immediate
1926 ' delete from IEB_INB_SVC_COVERAGES where svcpln_svcpln_id =:1 '
1927 USING p_plan_id;
1928
1929 if (sql%notfound) then
1930 null;
1931 end if;
1932 elsif (p_direction = 'O') then
1933
1934 EXECUTE immediate
1935 ' delete from IEB_OUTB_SVC_COVERAGES where svcpln_svcpln_id =:1 '
1936 USING p_plan_id;
1937
1938 if (sql%notfound) then
1939 null;
1940 end if;
1941 end if;
1942
1943 COMMIT;
1944 EXCEPTION
1945 WHEN others THEN
1946 -- dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
1947 ROLLBACK;
1948 x_return_status := fnd_api.g_ret_sts_unexp_error;
1949
1950
1951 END Delete_IOCoverages;
1952
1953
1954 PROCEDURE Delete_Regional_Plan ( x_return_status OUT NOCOPY VARCHAR2,
1955 x_msg_count OUT NOCOPY NUMBER,
1956 x_msg_data OUT NOCOPY VARCHAR2,
1957 p_base_plan_id IN NUMBER,
1958 p_reg_plan_id IN NUMBER )as
1959
1960
1961 l_media_type_id IEB_SERVICE_PLANS.MEDIA_TYPE_ID%type;
1962
1963 l_service_plan_tl_id IEB_SERVICE_PLANS_TL.SERVICE_PLAN_ID%type;
1964 l_language VARCHAR2(4);
1965
1966 l_source_lang VARCHAR2(4);
1967
1968
1969 BEGIN
1970
1971 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1972 x_return_status := fnd_api.g_ret_sts_success;
1973 x_msg_count := 0;
1974 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1975 x_msg_data := '';
1976
1977 select MEDIA_TYPE_ID into l_media_type_id from IEB_SERVICE_PLANS where SVCPLN_ID = p_base_plan_id;
1978
1979 update IEB_WB_SVC_CATS set
1980 svcpln_svcpln_id = p_base_plan_id,
1981 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1982 LAST_UPDATE_DATE = SYSDATE,
1983 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1984 where MEDIA_TYPE_ID = l_media_type_id
1985 and svcpln_svcpln_id = p_reg_plan_id;
1986
1987
1988
1989 EXECUTE immediate
1990 ' delete from ieb_regional_plans where base_plan_id = :1 and SERVICE_PLAN_ID = :2'
1991 USING p_base_plan_id, p_reg_plan_id ;
1992
1993 if (sql%notfound) then
1994 null;
1995 end if;
1996
1997 EXECUTE immediate
1998 ' delete from ieb_group_plan_maps where service_plan_id = :1 '
1999 USING p_reg_plan_id;
2000
2001 if (sql%notfound) then
2002 null;
2003 end if;
2004
2005 EXECUTE immediate
2006 ' delete from IEB_INB_SVC_COVERAGES where SVCPLN_SVCPLN_ID = :1 '
2007 USING p_reg_plan_id;
2008
2009 if (sql%notfound) then
2010 null;
2011 end if;
2012
2013 EXECUTE immediate
2014 ' delete from IEB_OUTB_SVC_COVERAGES where SVCPLN_SVCPLN_ID = :1'
2015 USING p_reg_plan_id;
2016
2017 if (sql%notfound) then
2018 null;
2019 end if;
2020 EXECUTE immediate
2021 ' delete from IEB_SERVICE_PLANS where SVCPLN_ID = :1'
2022 USING p_reg_plan_id;
2023
2024 if (sql%notfound) then
2025 null;
2026 end if;
2027
2028 EXECUTE immediate
2029 ' delete from IEB_SERVICE_PLANS_TL where SERVICE_PLAN_ID = :1'
2030 USING p_reg_plan_id;
2031
2032 if (sql%notfound) then
2033 null;
2034 end if;
2035
2036
2037 COMMIT;
2038 EXCEPTION
2039 WHEN others THEN
2040 -- dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
2041 ROLLBACK;
2042 x_return_status := fnd_api.g_ret_sts_unexp_error;
2043
2044
2045 END Delete_Regional_Plan;
2046
2047
2048 PROCEDURE Delete_Regional_PlanMaps ( x_return_status OUT NOCOPY VARCHAR2,
2049 x_msg_count OUT NOCOPY NUMBER,
2050 x_msg_data OUT NOCOPY VARCHAR2,
2051 p_reg_plan_id IN NUMBER ,
2052 p_base_plan_id IN NUMBER,
2053 p_media_type_id IN NUMBER)as
2054
2055 l_language VARCHAR2(4);
2056
2057 l_source_lang VARCHAR2(4);
2058
2059
2060 BEGIN
2061
2062 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2063 x_return_status := fnd_api.g_ret_sts_success;
2064 x_msg_count := 0;
2065 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
2066 x_msg_data := '';
2067
2068
2069 EXECUTE immediate
2070 ' delete from ieb_group_plan_maps where service_plan_id = :1 '
2071 USING p_reg_plan_id;
2072
2073 if (sql%notfound) then
2074 null;
2075 end if;
2076
2077
2078 update IEB_WB_SVC_CATS set
2079 svcpln_svcpln_id = p_base_plan_id,
2080 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2081 LAST_UPDATE_DATE = SYSDATE,
2082 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
2083 where MEDIA_TYPE_ID = p_media_type_id
2084 and svcpln_svcpln_id = p_reg_plan_id;
2085
2086
2087
2088 COMMIT;
2089 EXCEPTION
2090 WHEN others THEN
2091 -- dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
2092 ROLLBACK;
2093 x_return_status := fnd_api.g_ret_sts_unexp_error;
2094
2095
2096 END Delete_Regional_PlanMaps;
2097
2098
2099 PROCEDURE Delete_SpecDateCoverages ( x_return_status OUT NOCOPY VARCHAR2,
2100 x_msg_count OUT NOCOPY NUMBER,
2101 x_msg_data OUT NOCOPY VARCHAR2,
2102 p_direction IN VARCHAR2,
2103 p_plan_id IN VARCHAR2,
2104 p_spec_date IN VARCHAR2
2105 )as
2106
2107
2108 l_media_type_id IEB_SERVICE_PLANS.MEDIA_TYPE_ID%type;
2109
2110 l_service_plan_tl_id IEB_SERVICE_PLANS_TL.SERVICE_PLAN_ID%type;
2111 l_language VARCHAR2(4);
2112
2113 l_source_lang VARCHAR2(4);
2114
2115
2116 BEGIN
2117
2118 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2119 x_return_status := fnd_api.g_ret_sts_success;
2120 x_msg_count := 0;
2121 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
2122 x_msg_data := '';
2123
2124
2125
2126
2127 if( p_direction = 'I') then
2128
2129 EXECUTE immediate
2130 ' delete from IEB_INB_SVC_COVERAGES where svcpln_svcpln_id =:1 and SPEC_SCHD_DATE =:2 and SCHEDULE_TYPE = :3'
2131 USING p_plan_id, p_spec_date, 'S';
2132
2133 if (sql%notfound) then
2134 null;
2135 end if;
2136 elsif (p_direction = 'O') then
2137
2138 EXECUTE immediate
2139 ' delete from IEB_OUTB_SVC_COVERAGES where svcpln_svcpln_id =:1 and SPEC_SCHD_DATE=:2 and SCHEDULE_TYPE = :3'
2140 USING p_plan_id, p_spec_date, 'S';
2141
2142 if (sql%notfound) then
2143 null;
2144 end if;
2145 end if;
2146
2147 COMMIT;
2148 EXCEPTION
2149 WHEN others THEN
2150 -- dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
2151 ROLLBACK;
2152 x_return_status := fnd_api.g_ret_sts_unexp_error;
2153
2154
2155
2156 END Delete_SpecDateCoverages;
2157
2158
2159 END IEB_ServicePlan_PVT;