1 PACKAGE BODY MSD_COMMON_UTILITIES_LB AS
2 /* $Header: msdculbb.pls 120.0 2005/05/25 19:56:47 appldev noship $ */
3
4
5 /* Public Functions */
6 /* This function returns the level_pk for a level value*/
7
8 function get_level_pk( p_level_id IN NUMBER, p_sr_level_pk IN VARCHAR2 ) return number Is
9
10 CURSOR c_level_pk
11 IS
12 select level_pk
13 from
14 msd_level_values_lb
15 where
16 level_id = p_level_id
17 and sr_level_pk = p_sr_level_pk ;
18
19 x_level_pk number ;
20
21 Begin
22
23 OPEN c_level_pk ;
24 FETCH c_level_pk INTO x_level_pk;
25 CLOSE c_level_pk ;
26
27
28 if x_level_pk is null then
29 select msd_level_values_s.nextval into x_level_pk
30 from sys.dual ;
31 end if ;
32
33 return x_level_pk;
34
35 exception
36 when others then
37 fnd_file.put_line(fnd_file.log,SQLERRM);
38 return null ;
39
40
41 end get_level_pk ;
42
43 /* This function returns the demad_plan_id for a given supply plan*/
44
45 FUNCTION get_demand_plan_id( p_plan_id IN NUMBER) return NUMBER
46 IS
47 CURSOR c_demand_plan_id
48 is
49 select demand_plan_id
50 from
51 msd_demand_plans
52 where
53 liab_plan_id = p_plan_id ;
54
55 x_demand_plan_id NUMBER ;
56
57 Begin
58 OPEN c_demand_plan_id ;
59 FETCH c_demand_plan_id INTO x_demand_plan_id;
60 CLOSE c_demand_plan_id ;
61
62 return x_demand_plan_id ;
63 END get_demand_plan_id ;
64
65
66 /* This function returns the supply_plan_id for a given demand plan id */
67
68 FUNCTION get_supply_plan_id( p_demand_plan_id IN NUMBER) return NUMBER
69 IS
70 CURSOR c_supply_plan_id
71 is
72 select liab_plan_id
73 from
74 msd_demand_plans
75 where
76 demand_plan_id = p_demand_plan_id ;
77
78 x_supply_plan_id NUMBER ;
79
80 Begin
81 OPEN c_supply_plan_id ;
82 FETCH c_supply_plan_id INTO x_supply_plan_id;
83 CLOSE c_supply_plan_id ;
84
85 return x_supply_plan_id ;
86 END get_supply_plan_id ;
87
88
89
90
91 /* This function returns the item category name for the given item */
92
93
94
95
96
97 FUNCTION get_item_cat_name( p_inventory_item_id IN NUMBER, p_category_set_id IN NUMBER ) RETURN VARCHAR2
98 IS
99
100 CURSOR c_category_name
101 IS
102 select mic.category_name
103 from msc_item_categories mic ,
104 MSC_TRADING_PARTNERS mtp
105 where
106 mtp.partner_type = 3
107 and mtp.sr_tp_id = mtp.master_organization
108 and mtp.sr_tp_id = mic.organization_id
109 and mic.inventory_item_id = p_inventory_item_id
110 and mic.category_set_id = p_category_set_id
111 and mic.sr_instance_id = mtp.sr_instance_id
112 order by mic.category_name ;
113
114 x_category_name VARCHAR2(100) ;
115
116 BEGIN
117 OPEN c_category_name ;
118 FETCH c_category_name INTO x_category_name ;
119 CLOSE c_category_name ;
120
121 IF x_category_name is null then return MSD_SR_UTIL.get_null_desc ;
122 end if ;
123
124 return x_category_name ;
125
126 END get_item_cat_name ;
127
128
129
130 /* This function returns the item category description for the given item */
131
132
133 FUNCTION get_item_cat_desc( p_inventory_item_id IN NUMBER, p_category_set_id IN NUMBER ) RETURN VARCHAR2
134 IS
135
136 CURSOR c_category_desc
137 IS
138 select mic.description
139 from msc_item_categories mic ,
140 MSC_TRADING_PARTNERS mtp
141 where
142 mtp.partner_type = 3
143 and mtp.sr_tp_id = mtp.master_organization
144 and mtp.sr_tp_id = mic.organization_id
145 and mic.inventory_item_id = p_inventory_item_id
146 and mic.category_set_id = p_category_set_id
147 and mic.sr_instance_id = mtp.sr_instance_id
148 order by mic.category_name ;
149
150 x_category_desc VARCHAR2(100) ;
151
152 BEGIN
153 OPEN c_category_desc ;
154 FETCH c_category_desc INTO x_category_desc ;
155 CLOSE c_category_desc ;
156
157 IF x_category_desc is null then return MSD_SR_UTIL.get_null_desc ;
158 end if ;
159
160 return x_category_desc ;
161
162 END get_item_cat_desc ;
163
164 /* This function returns the supply_plan_start_date */
165
166 FUNCTION get_supply_plan_start_date( p_plan_id IN NUMBER) return DATE
167 IS
168 CURSOR c_plan_start_date
169 is
170 select start_date
171 from msc_plans
172 where
173 plan_id = p_plan_id ;
174
175 x_plan_start_date DATE ;
176
177 Begin
178 OPEN c_plan_start_date ;
179 FETCH c_plan_start_date INTO x_plan_start_date;
180 CLOSE c_plan_start_date ;
181
182 IF x_plan_start_date IS NULL AND p_plan_id = -1 THEN return SYSDATE ;
183 END IF ;
184 return x_plan_start_date ;
185 END get_supply_plan_start_date ;
186
187
188
189 /* This function returns the supply_plan_end_date */
190
191 FUNCTION get_supply_plan_end_date( p_plan_id IN NUMBER) return DATE
192 IS
193 CURSOR c_plan_end_date
194 is
195 select cutoff_date
196 from msc_plans
197 where
198 plan_id = p_plan_id ;
199
200 x_plan_end_date DATE ;
201
202 Begin
203 OPEN c_plan_end_date ;
204 FETCH c_plan_end_date INTO x_plan_end_date;
205 CLOSE c_plan_end_date ;
206
207 return x_plan_end_date ;
208 END get_supply_plan_end_date ;
209
210 /* This function returns the supply_plan name */
211
212 FUNCTION get_supply_plan_name( p_plan_id IN NUMBER) return VARCHAR2
213 IS
214 CURSOR c_plan_name
215 is
216 select compile_designator
217 from msc_plans
218 where
219 plan_id = p_plan_id ;
220
221 x_plan_name VARCHAR2( 100) ;
222
223 Begin
224 OPEN c_plan_name ;
225 FETCH c_plan_name INTO x_plan_name;
226 CLOSE c_plan_name ;
227
228
229
230 IF p_plan_id = -1 THEN RETURN FND_MESSAGE.get_string('MSC', 'MSC_COLLAB_LIAB') ;
231 /* This needs to be made translatable */
232 END IF ;
233 return x_plan_name;
234 END get_supply_plan_name ;
235
236 /* This function returns end date for CP Plan */
237
238 FUNCTION get_cp_end_date return DATE
239 IS
240 CURSOR c_input_params IS
241 SELECT
242 distinct
243 mdpar.parameter_type ,
244 mcd.planning_server_view_name ,
245 mcd.description
246 FROM msd_dp_parameters mdpar ,
247 msd_cs_definitions mcd,
248 msd_demand_plans mdp,
249 msd_cs_defn_dim_dtls mcddd
250 where mdpar.demand_plan_id =mdp.demand_plan_id
251 and mdp.demand_plan_name = 'LIABILTY_PLAN'
252 and mdp.template_flag = 'Y'
253 and mdpar.parameter_type =mcd.name
254 and mcd.cs_definition_id = mcddd.cs_definition_id
255 and mcddd.dimension_code = 'TIM'
256 and nvl( mcd.planning_server_view_name, 'NA') <> 'NA'
257 and nvl(mcd.liability_user_flag , 'N') <> 'Y' ;
258
259
260
261
262 TYPE c_stream_typ IS REF CURSOR;
263 c_stream c_stream_typ; -- declare cursor variabl
264
265 x_max_date DATE ;
266 x_cp_liab_end_date DATE ;
267 v_sql_stmt VARCHAR2(200) ;
268 x_collab_liab VARCHAR2(200) ;
269
270
271 BEGIN
272 select sysdate into x_cp_liab_end_date from dual ;
273
274 for x_input_param_rec in c_input_params
275
276 loop
277
278 x_collab_liab := FND_MESSAGE.get_string('MSC', 'MSC_COLLAB_LIAB') ;
279
280 v_sql_stmt := 'select max(end_date) from '|| x_input_param_rec.planning_server_view_name|| ' where cs_name = '||''''||x_collab_liab||'''' ;
281
282 fnd_file.put_line(fnd_file.log , v_sql_stmt );
283
284 OPEN c_stream FOR v_sql_stmt;
285 FETCH c_stream INTO x_max_date;
286 CLOSE c_stream;
287
288
289 IF x_max_date > x_cp_liab_end_date THEN x_cp_liab_end_date := x_max_date ;
290
291 END IF ;
292
293
294 end loop ;
295
296 RETURN x_cp_liab_end_date ;
297
298 END get_cp_end_date ;
299
300 /* Returns the plan owning org */
301
302 FUNCTION get_plan_owning_org( p_plan_id IN NUMBER) return NUMBER
303 IS
304 CURSOR c_plan_owning_org
305 is
306 select md.organization_id
307 from
308 msc_designators md,
309 msc_plans mp
310 where
311 md.designator = mp.compile_designator
312 and mp.plan_id = p_plan_id ;
313
314 x_organization_id NUMBER ;
315 Begin
316 IF p_plan_id = -1 THEN RETURN -1 ;
317 END IF ;
318 OPEN c_plan_owning_org ;
319 FETCH c_plan_owning_org INTO x_organization_id;
320 CLOSE c_plan_owning_org ;
321
322 return x_organization_id ;
323
324
325
326
327 END get_plan_owning_org ;
328
329 /* Returns the plan owing instance of the supply plan */
330
331 FUNCTION get_plan_owning_instance( p_plan_id IN NUMBER) return NUMBER
332 IS
333 CURSOR c_plan_owning_instance
334 is
335 select md.sr_instance_id
336 from
337 msc_designators md ,
338 msc_plans mp
339 where
340 md.designator = mp.compile_designator
341 and mp.plan_id = p_plan_id ;
342
343 x_sr_instance_id NUMBER ;
344 Begin
345 IF p_plan_id = -1 THEN RETURN -1 ;
346 END IF ;
347 OPEN c_plan_owning_instance ;
348 FETCH c_plan_owning_instance INTO x_sr_instance_id ;
349 CLOSE c_plan_owning_instance ;
350
351 return x_sr_instance_id ;
352
353
354
355 END get_plan_owning_instance ;
356
357
358 /* This api is called by DPE to update the details of the previous liability */
359
360 procedure liability_post_process( p_demand_plan_id IN NUMBER ,
361 p_scenario_name IN VARCHAR2 ,
362 p_senario_rev_num IN NUMBER)
363
364
365 IS
366 CURSOR c_demand_plan
367 IS
368 select
369 mdp.demand_plan_id ,
370 mdp.plan_start_date ,
371 mds.scenario_id
372 from
373 msd_demand_plans mdp ,
374 msd_dp_scenarios mds
375 where mdp.demand_plan_id = p_demand_plan_id
376 and mdp.demand_plan_id = mds.demand_plan_id
377 and mds.SCENARIO_DESIGNATOR = 'TOTAL_LIABILITY'
378 and mdp.plan_type = 'LIABILITY';
379
380
381 BEGIN
382
383 for x_demand_plan_rec in c_demand_plan
384 LOOP
385
386 /*
387 UPDATE msd_demand_plans SET liability_revision_num = p_senario_rev_num ,
388 prev_liab_pub_plan_start_date = x_demand_plan_rec.plan_start_date
389 WHERE demand_plan_id = p_demand_plan_id ;
390 */
391
392 UPDATE MSD_DP_SCENARIO_REVISIONS SET plan_start_date = x_demand_plan_rec.plan_start_date
393 WHERE demand_plan_id = p_demand_plan_id and revision = p_senario_rev_num
394 and scenario_id = x_demand_plan_rec.scenario_id ;
395 /*
396 fnd_file.put_line(fnd_file.log , 'LIABILITY POST PROCESS' );
397 fnd_file.put_line(fnd_file.log , p_scenario_name );
398 fnd_file.put_line(fnd_file.log , p_senario_rev_num );
399 */
400 END LOOP ;
401
402 --RETURN 1 ;
403
404 commit ;
405
406
407 END ;
408
409
410 /* Updates supply plan dates in msd demand Plans */
411
412 FUNCTION liability_plan_update( p_demand_plan_id IN NUMBER )
413 RETURN NUMBER
414 IS
415 CURSOR c_demand_plan
416 IS
417 select
418 mds.scenario_id
419 from
420 msd_demand_plans mdp ,
421 msd_dp_scenarios mds
422 where mdp.demand_plan_id = p_demand_plan_id
423 and mdp.demand_plan_id = mds.demand_plan_id
424 and mds.SCENARIO_DESIGNATOR = 'TOTAL_LIABILITY' /* This will be translatable */
425 and mdp.plan_type = 'LIABILITY';
426
427
428
429 CURSOR c_liability_rev_num( p_scenario_id IN NUMBER)
430 is
431 select
432 max(revision),
433 plan_start_date
434 from
435 MSD_DP_SCENARIO_REVISIONS
436 where
437 demand_plan_id = p_demand_plan_id
438 and scenario_id = p_scenario_id
439 group by
440 plan_start_date
441 ;
442
443 x_plan_start_date DATE ;
444 x_rev_num NUMBER ;
445 x_scenario_id NUMBER ;
446
447
448
449 BEGIN
450
451
452 OPEN c_demand_plan ;
453 FETCH c_demand_plan INTO x_scenario_id ;
454 CLOSE c_demand_plan ;
455
456 OPEN c_liability_rev_num( x_scenario_id) ;
457 FETCH c_liability_rev_num into x_rev_num , x_plan_start_date ;
458 CLOSE c_liability_rev_num ;
459
460 UPDATE msd_demand_plans SET previous_plan_start_date = x_plan_start_date , LIABILITY_REVISION_NUM = x_rev_num
461 WHERE demand_plan_id = p_demand_plan_id ;
462
463
464
465
466 /*
467
468 for x_demand_plan_rec in c_demand_plan
469 LOOP
470
471 x_plan_start_date := get_supply_plan_start_date( x_demand_plan_rec.LIAB_PLAN_ID ) ;
472 IF x_plan_start_date > x_demand_plan_rec.plan_start_date THEN
473 UPDATE msd_demand_plans SET previous_plan_start_date = x_demand_plan_rec.prev_liab_pub_plan_start_date
474 WHERE demand_plan_id = p_demand_plan_id ;
475 END IF ;
476
477 IF x_plan_start_date = x_demand_plan_rec.prev_liab_pub_plan_start_date THEN
478
479 OPEN c_liability_rev_num( x_demand_plan_rec.previous_plan_start_date ,x_demand_plan_rec.scenario_id) ;
480 FETCH c_liability_rev_num into x_rev_num ;
481 CLOSE c_liability_rev_num ;
482
483
484 UPDATE msd_demand_plans SET prev_liab_pub_plan_start_date = x_demand_plan_rec.previous_plan_start_date
485 WHERE demand_plan_id = p_demand_plan_id ;
486
487 END IF ;
488
489
490 END LOOP ;
491 */
492
493 RETURN 1 ;
494
495 END liability_plan_update ;
496
497 /* Return default mfg CAL */
498
499 FUNCTION get_default_mfg_cal ( p_org_id IN NUMBER , p_instance_id IN NUMBER) RETURN VARCHAR2
500
501 IS
502 CURSOR c_calendar
503 IS
504 select calendar_code from
505 msc_trading_partners
506 where partner_type = 3
507 and sr_instance_id = p_instance_id
508 and sr_tp_id = p_org_id ;
509
510 x_cal_code VARCHAR2(200) ;
511
512
513 BEGIN
514
515 OPEN c_calendar ;
516 FETCH c_calendar into x_cal_code ;
517 CLOSE c_calendar ;
518
519 return x_cal_code ;
520
521 END ;
522
523
524 FUNCTION get_default_uom RETURN VARCHAR2
525
526 IS
527 CURSOR c_uom
528 IS
529 select
530 uom_code
531 from msc_uom_conversions
532 where upper(uom_code) = 'EA' and rownum = 1 ;
533
534 x_uom VARCHAR2(200) ;
535
536
537 BEGIN
538
539 x_uom := FND_PROFILE.Value('MSC_LIABILITY_BASE_UOM') ;
540
541 IF x_uom is NULL THEN
542 OPEN c_uom;
543 FETCH c_uom into x_uom ;
544 CLOSE c_uom ;
545 END IF ;
546
547 return x_uom ;
548
549 END ;
550
551
552 /*
553
554 This function returns the URL to launch the specific liability plan for certain user responsibility.
555
556 There are two arguments:
557
558 p_plan_id: ASCP Plan ID or -1 for CP Plan
559
560 p_function_id MSD_DP_ADMIN_SSA, MSD_DP_MGR_SSA or MSD_DP_PLANNER_SSA for different responsibilties
561
562 */
563
564
565
566 function get_liability_url(p_plan_id IN NUMBER ,
567
568 p_function_id IN VARCHAR2)
569
570 RETURN VARCHAR2
571
572
573
574 IS
575
576
577
578 CURSOR c_plan_url (p_demand_plan_id IN NUMBER)
579
580 IS
581
582 select
583
584 fnd_profile.value('APPS_SERVLET_AGENT') ||
585
586 '/oowa/aw92/'||
587
588 'dbapps.xwdevkit/xwd_init?apps.' ||
589
590 nvl(fnd_profile.value('MSD_CODE_AW'),'ODPCODE') ||
591
592 '/dp.init.shell?' ||
593
594 '/IDF=' || p_function_id ||
595
596 '/PLAN_TYPE=LIABILITY' ||
597
598 '/ID=' || p_demand_plan_id ||
599
600 '/SHR=MSD' || p_demand_plan_id ||
601
602 '/UID=' || fnd_global.user_id ||
603
604 '/RID=' || fnd_global.resp_id ||
605
606 '/RAID=' || fnd_global.resp_appl_id
607
608 from dual ;
609
610
611
612 x_demand_plan_id NUMBER ;
613
614 /* This varchar2 was changed from 200 to 400 to take care of CP issue */
615 x_plan_url VARCHAR2(400) ;
616
617
618
619
620 BEGIN
621
622
623
624 x_demand_plan_id := msd_common_utilities_lb.get_demand_plan_id(p_plan_id) ;
625
626
627
628 OPEN c_plan_url (x_demand_plan_id) ;
629
630 FETCH c_plan_url INTO x_plan_url ;
631
632 CLOSE c_plan_url ;
633
634
635
636 RETURN x_plan_url ;
637
638
639
640 END get_liability_url ;
641
642 /* This function populates MSC_ASL_AUTH_DETAILS with start date and end date */
643
644 procedure liability_preprocessor(p_plan_id IN NUMBER )
645
646 IS
647
648 CURSOR c_sup_item_org is
649 select
650 SUPPLIER_ID,
651 SUPPLIER_SITE_ID ,
652 ORGANIZATION_ID ,
653 SR_INSTANCE_ID,
654 INVENTORY_ITEM_ID,
655 AUTHORIZATION_CODE,
656 cutoff_days,
657 INCLUDE_LIABILITY_AGREEMENT,
658 ASL_LIABILITY_AGREEMENT_BASIS
659 from
660 msc_asl_auth_details
661 where
662 plan_id = -1
663 /* and INCLUDE_LIABILITY_AGREEMENT = 1 This filter will remove any disabled agreement */
664 order
665 by
666 SR_INSTANCE_ID,
667 SUPPLIER_ID,
668 SUPPLIER_SITE_ID,
669 ORGANIZATION_ID,
670 INVENTORY_ITEM_ID,
671 TRANSACTION_ID ;
672
673 x_start_days NUMBER ;
674 x_end_days NUMBER ;
675 x_prv_end_days NUMBER ;
676 x_supplier_id NUMBER ;
677 x_organization_id NUMBER ;
678 x_inventory_item_id NUMBER ;
679 x_sr_instance_id NUMBER ;
680 x_prv_supplier_id NUMBER ;
681 x_prv_organization_id NUMBER ;
682 x_prv_inventory_item_id NUMBER ;
683 x_prv_sr_instance_id NUMBER ;
684
685 BEGIN
686
687 x_prv_end_days := 0 ;
688 x_end_days := 0 ;
689
690 UPDATE msc_item_suppliers
691 set INCLUDE_LIABILITY_AGREEMENT = NULL ,
692 ASL_LIABILITY_AGREEMENT_BASIS =NULL
693 where
694 plan_id = p_plan_id ;
695 --and plan_id <> -1 ;
696
697 commit ;
698
699
700 FOR x_sup_item_org in c_sup_item_org
701
702 LOOP
703
704 IF (nvl(x_prv_supplier_id, x_sup_item_org.supplier_id ) <> x_sup_item_org.supplier_id) or
705 ( nvl( x_prv_organization_id , x_organization_id ) <> x_sup_item_org.organization_id ) or
706 (nvl(x_prv_sr_instance_id ,x_sr_instance_id) <> x_sup_item_org.sr_instance_id )or
707 ( nvl( x_prv_inventory_item_id , x_inventory_item_id ) <> x_sup_item_org.inventory_item_id)
708
709 THEN
710
711 x_prv_end_days := 0 ;
712 x_end_days := 0 ;
713 end if ;
714
715
716 UPDATE msc_asl_auth_details
717 set start_days = x_end_days ,
718 end_days = x_end_days + cutoff_days
719 where
720 PLAN_ID = -1 and
721 SUPPLIER_ID = x_sup_item_org.SUPPLIER_ID and
722 SUPPLIER_SITE_ID = x_sup_item_org.SUPPLIER_SITE_ID and
723 ORGANIZATION_ID = x_sup_item_org.ORGANIZATION_ID and
724 SR_INSTANCE_ID = x_sup_item_org.SR_INSTANCE_ID and
725 INVENTORY_ITEM_ID = x_sup_item_org.INVENTORY_ITEM_ID and
726 AUTHORIZATION_CODE = x_sup_item_org.AUTHORIZATION_CODE ;
727
728 x_end_days := x_sup_item_org.cutoff_days + x_prv_end_days ;
729
730 x_prv_supplier_id := x_sup_item_org.supplier_id ;
731 x_prv_organization_id := x_sup_item_org. organization_id ;
732 x_prv_sr_instance_id := x_sup_item_org.sr_instance_id ;
733 x_prv_inventory_item_id := x_sup_item_org.inventory_item_id ;
734 x_prv_end_days := x_end_days ;
735
736
737
738
739 UPDATE msc_item_suppliers
740 set INCLUDE_LIABILITY_AGREEMENT = x_sup_item_org. INCLUDE_LIABILITY_AGREEMENT ,
741 ASL_LIABILITY_AGREEMENT_BASIS = x_sup_item_org.ASL_LIABILITY_AGREEMENT_BASIS
742 where
743 SUPPLIER_ID = x_sup_item_org.SUPPLIER_ID and
744 SUPPLIER_SITE_ID = x_sup_item_org.SUPPLIER_SITE_ID and
745 ORGANIZATION_ID = x_sup_item_org.ORGANIZATION_ID and
746 SR_INSTANCE_ID = x_sup_item_org.SR_INSTANCE_ID and
747 INVENTORY_ITEM_ID = x_sup_item_org.INVENTORY_ITEM_ID and
748 plan_id = p_plan_id ;
749 --and plan_id <> -1 ;
750
751 commit ;
752
753 END LOOP ;
754
755 commit ;
756
757 END liability_preprocessor ;
758
759 function get_base_uom(p_item_id in number, p_dp_plan_id in number) return varchar2 IS
760
761 x_base_uom varchar2(30);
762 cursor c1 is
763 select msi2.uom_code
764 from msc_system_items msi,
765 msc_system_items msi2,
766 msc_trading_partners mtp
767 where msi2.organization_id = mtp.master_organization
768 and msi.organization_id = mtp.sr_tp_id
769 and msi.sr_instance_id = mtp.sr_instance_id
770 and msi.inventory_item_id = msi2.inventory_item_id
771 and msi2.plan_id = -1
772 and msi.inventory_item_id = p_item_id
773 and msi.plan_id = p_dp_plan_id
774 order by msi.sr_instance_id, msi.organization_id desc;
775
776
777
778 BEGIN
779
780
781 open c1;
782 fetch c1 into x_base_uom;
783 close c1;
784
785 return x_base_uom;
786
787
788
789 EXCEPTION when others then return NULL;
790
791
792
793 END get_base_uom;
794
795 /* This function is used by ASCP UI to decide whether a liability plan exists for an ASCP Plan*/
796 /* This function return 1 if the plan exists else return 2 */
797 function liability_plan_exists(p_plan_id IN NUMBER ) return boolean IS
798 x_status NUMBER ;
799 cursor c_status
800 is
801 select 1 from msd_demand_plans
802 where liab_plan_id = p_plan_id
803 and nvl( DP_BUILD_ERROR_FLAG, 'NO') = 'NO'
804 and plan_type = 'LIABILITY' ;
805 BEGIN
806 open c_status ;
807 fetch c_status into x_status ;
808 close c_status ;
809 if x_status = 1 then return TRUE ;
810 end if ;
811 return FALSE ;
812 EXCEPTION when others then return NULL;
813 END liability_plan_exists ;
814
815
816
817
818
819
820 END MSD_COMMON_UTILITIES_LB ;
[Home] [Help]