[Home] [Help]
PACKAGE BODY: APPS.MSD_APPLY_TEMPLATE_DEMAND_PLAN
Source
1 PACKAGE BODY MSD_APPLY_TEMPLATE_DEMAND_PLAN AS
2 /* $Header: msdatdpb.pls 120.24 2006/05/29 12:21:07 brampall noship $ */
3
4 /* Private Package Variables */
5
6
7
8
9
10 /* Private Procedures */
11
12
13
14 g_sno number := 0;
15
16 g_call boolean := FALSE;
17
18
19
20 Procedure common_post_copy_process(p_new_dp_id in number);
21
22
23
24 Procedure sop_post_copy_process(p_new_dp_id in number);
25
26
27
28 Procedure liab_post_copy_process(p_new_dp_id in number);
29
30
31
32 Procedure common_all_post_process(p_new_dp_id in number);
33
34
35
36 Procedure Update_Formula_Names(p_new_dp_id in number);
37
38
39
40 Procedure Replace_formula_tokens(p_new_dp_id in number);
41
42
43
44 Procedure Parse_Dimension_Select_List(p_new_dp_id in number);
45
46
47
48 Procedure Replace_dimension_tokens(p_new_dp_id in number);
49
50
51
52 Procedure update_ascp_related_data(p_new_dp_id in number);
53
54
55
56 Procedure Validate_formula_parameters(p_new_dp_id in number);
57
58
59
60 Procedure validate_formulas(p_new_dp_id in number);
61
62
63
64 Procedure validate_doc_dim_selections(p_new_dp_id in number);
65
66
67
68 Procedure validate_doc_dimensions(p_new_dp_id in number);
69
70
71
72 Procedure validate_documents(p_new_dp_id in number);
73
74
75
76 Procedure refresh_document_dimensions(p_demand_plan_id in number);
77
78
79
80 Procedure refresh_formulas(p_demand_plan_id in number);
81
82
83
84 Procedure eol_post_copy_process(p_new_dp_id in number);
85
86 Procedure add_ascp_scenario_for_eol(p_new_dp_id in number,p_supply_plan_id in number, p_supply_plan_name in varchar2);
87
88 Procedure update_parameter_dates(p_demand_plan_id number);
89
90 Function get_dimension_script( p_demand_plan_id varchar2,p_dimension_code varchar2,p_dimension_script varchar2) return varchar2;
91
92
93
94 Function get_dimension_code( p_demand_plan_id varchar2,p_dimension_code varchar2) return varchar2;
95
96
97
98 Function get_level_id (p_demand_plan_id number,p_level_id number) return number;
99
100
101
102 Function get_hierarchy_id (p_demand_plan_id varchar2,p_hierarchy_id varchar2) return number;
103
104
105
106 /* Public Procedures and Function */
107
108
109
110 /*******************************************************
111
112 This Function creates the plan using template.
113
114 Parameter p_shared_db_location should be 'MSD'||p_new_dp_id.
115
116 Called from MSDDPLNS.fmb and msd_apply_template_demand_plan.create_plan_using_template.
117
118 Calls msd_copy_demand_plan.copy_demand_plan.
119
120 Returns 0 if successful and 1 if not.
121
122 ********************************************************/
123
124
125
126 function apply_template(
127
128 p_new_dp_id in out nocopy number,
129
130 p_target_demand_plan_name in VARCHAR2,
131
132 p_target_demand_plan_descr in VARCHAR2,
133
134 p_shared_db_location in VARCHAR2,
135
136 p_source_dp_id in NUMBER,
137
138 p_organization_id in number,
139
140 p_instance_id in number,
141
142 p_errcode in out nocopy varchar2
143
144 ) return NUMBER IS
145
146
147
148 x_ret_val number;
149
150
151
152 BEGIN
153
154
155
156 -- Copy Plan from Template
157
158 x_ret_val := msd_copy_demand_plan.copy_demand_plan(
159
160 p_new_dp_id,
161
162 p_target_demand_plan_name,
163
164 p_target_demand_plan_descr,
165
166 'MSD'|| p_new_dp_id,
167
168 p_source_dp_id,
169
170 p_organization_id,
171
172 p_instance_id,
173
174 p_errcode);
175
176
177
178
179
180 if x_ret_val = 0 then
181
182
183
184 -- Processing common to all plan types
185
186 common_post_copy_process(p_new_dp_id);
187
188
189
190 -- Processing for SOP Plans
191
192 sop_post_copy_process(p_new_dp_id);
193
194
195
196 -- Processing for LIABILITY Plans
197
198 liab_post_copy_process(p_new_dp_id);
199
200
201
202 -- Processing for EOL Plans
203
204 eol_post_copy_process(p_new_dp_id);
205
206
207
208 -- Process again for all plan types
209
210 common_all_post_process(p_new_dp_id);
211
212
213
214 -- Compile The seeded docs and formulas
215
216 create_seeded_definitions(p_new_dp_id,p_errcode);
217
218
219
220 -- commit the changes
221
222 commit;
223
224 return 0;
225
226 end if;
227
228
229
230 return 1;
231
232
233
234 EXCEPTION
235
236
237
238
239
240 WHEN OTHERS THEN
241
242 p_new_dp_id := null ;
243
244 p_errcode := substr(SQLERRM,1,150);
245
246 return 1;
247
248
249
250
251
252 END apply_template;
253
254
255
256 /*******************************************************
257
258 This Function is a wrapper over apply_template function.
259
260 Parameter p_shared_db_location should be 'MSD'||p_new_dp_id.
261
262 Called from Launch Liability Program.
263
264 Calls MSD_APPLY_TEMPLATE_DEMAND_PLAN.apply_template.
265
266 Returns TRUE if successful and FALSE if not.
267
268 ********************************************************/
269
270
271
272 function create_plan_using_template(
273
274 p_new_dp_id in out nocopy number,
275
276 p_target_demand_plan_name in VARCHAR2,
277
278 p_target_demand_plan_descr in VARCHAR2,
279
280 p_plan_type in VARCHAR2,
281
282 p_plan_start_date in date,
283
284 p_plan_end_date in date,
285
286 p_supply_plan_id in number,
287
288 p_supply_plan_name in VARCHAR2,
289
290 p_organization_id in number,
291
292 p_instance_id in number,
293
294 p_errcode in out nocopy varchar2
295
296 ) return boolean is
297
298
299
300 cursor c1 is
301
302 select demand_plan_id
303
304 from msd_demand_plans
305
306 where plan_type = p_plan_type
307
308 and template_flag = 'Y'
309
310 and default_template = 'Y';
311
312
313
314 x_ret_val number;
315
316 l_template_id number;
317
318
319
320 BEGIN
321
322
323
324
325
326 open c1;
327
328 fetch c1 into l_template_id;
329
330 close c1;
331
332
333
334 -- Create Plan from Template
335
336 x_ret_val := MSD_APPLY_TEMPLATE_DEMAND_PLAN.apply_template(
337
338 p_new_dp_id,
339
340 p_target_demand_plan_name,
341
342 p_target_demand_plan_descr,
343
344 'MSD' || p_new_dp_id,
345
346 l_template_id,
347
348 p_organization_id,
349
350 p_instance_id,
351
352 p_errcode);
353
354
355
356
357
358 if x_ret_val = 0 then
359
360
361
362
363
364 -- Update the Liability Specific Columns
365
366 update msd_demand_plans
367
368 set plan_start_date = p_plan_start_date,
369
370 plan_end_date = p_plan_end_date,
371
372 liab_plan_id = p_supply_plan_id,
373
374 liab_plan_name = p_supply_plan_name
375
376 where demand_plan_id = p_new_dp_id;
377
378
379
380 -- Update Dates of the input parameters
381
382 -- set the start date and end date for input parameters having time data
383
384 update msd_dp_parameters
385
386 set start_date = p_plan_start_date,
387
388 end_date = p_plan_end_date
389
390 where demand_plan_id = p_new_dp_id
391
392 and forecast_date_used is not null
393
394 and deleteable_flag = 'N';
395
396
397
398
399
400 -- set the parameter name to supply plan name for input parameters
401
402 update msd_dp_parameters
403
404 set parameter_name = p_supply_plan_name
405
406 where demand_plan_id = p_new_dp_id;
407
408
409
410 -- set the supply plan name to supply plan name for doc dim selection
411
412 update msd_dp_doc_dim_selections
413
414 set supply_plan_name = p_supply_plan_name
415
416 where demand_plan_id = p_new_dp_id
417
418 and selection_type = 'I';
419
420
421
422 -- set the supply plan name to supply plan name for formula parameters
423
424 update msd_dp_formula_parameters
425
426 set supply_plan_name = p_supply_plan_name
427
428 where demand_plan_id = p_new_dp_id
429
430 and parameter_type = 'I';
431
432
433
434 set_prd_lvl_for_liab_reports(p_new_dp_id,p_errcode);
435
436
437
438 -- Compile the definitions for seeded documents and formulas
439
440 create_seeded_definitions(p_new_dp_id,p_errcode);
441
442
443
444 -- replace formula name with ID
445
446 update msd_dp_formulas mdf
447
448 set upload_formula_id = (select formula_id from msd_dp_formulas mdf1
449
450 where mdf1.demand_plan_id = p_new_dp_id
451
452 and mdf1.formula_name = mdf.upload_formula_id)
453
454 where demand_plan_id = p_new_dp_id
455
456 and upload_formula_id is not null;
457
458
459
460 commit;
461
462
463
464 return true;
465
466
467
468 else
469
470
471
472 return false;
473
474
475
476 end if;
477
478
479
480 EXCEPTION
481
482
483
484
485
486 WHEN OTHERS THEN
487
488 p_new_dp_id := null ;
489
490 p_errcode := substr(SQLERRM,1,150);
491
492 return false;
493
494
495
496
497
498 END create_plan_using_template;
499
500
501
502 /*******************************************************
503
504 This Procedure compiles the definitions for seeded documents and formulas.
505
506 Called from msd_apply_template_demand_plan.create_plan_using_template and plan build pre processor.
507
508 Calls refresh_document_dimensions, refresh_formulas, Parse_Dimension_Select_List,
509
510 Replace_formula_tokens, Replace_dimension_tokens, Validate_formula_parameters,
511
512 validate_formulas, validate_doc_dim_selections, validate_doc_dimensions
513
514 and validate_documents
515
516 ********************************************************/
517
518
519
520 Procedure create_seeded_definitions(p_demand_plan_id in number,
521
522 p_errcode in out nocopy varchar2
523
524 )
525
526
527
528 is
529
530 cursor get_template_id is
531 select template_id
532 from msd_demand_plans
533 where demand_plan_id=p_demand_plan_id;
534
535 l_template_id varchar2(100);
536
537 Begin
538
539 open get_template_id;
540 fetch get_template_id into l_template_id;
541 close get_template_id;
542
543 if l_template_id is null then
544 return;
545 end if;
546
547 -- refresh documents dimensions data from the template
548
549 refresh_document_dimensions(p_demand_plan_id);
550
551
552
553 -- refresh formula data from template
554
555 refresh_formulas(p_demand_plan_id);
556
557
558
559 -- parse the selection script for dimensions with selection type as list
560
561 -- Parse_Dimension_Select_List(p_demand_plan_id); commented out as only one set of ascp specific measures needs to be added to a doc
562
563
564
565 -- replace tokens in formulas with values
566
567 Replace_formula_tokens(p_demand_plan_id);
568
569
570
571 -- replace tokens in dimensions with values
572
573 Replace_dimension_tokens(p_demand_plan_id);
574
575
576
577 -- validate formula parameters
578
579 Validate_formula_parameters(p_demand_plan_id);
580
581
582
583 -- validate formulas
584
585 validate_formulas(p_demand_plan_id);
586
587
588
589 -- validate dimension selections
590
591 validate_doc_dim_selections(p_demand_plan_id);
592
593
594
595 -- validate dimensions for seeded docs
596
597 validate_doc_dimensions(p_demand_plan_id);
598
599
600
601 -- validate seeded documents
602
603 validate_documents(p_demand_plan_id);
604
605
606
607 EXCEPTION
608
609
610
611
612
613 WHEN OTHERS THEN
614
615 p_errcode := substr(SQLERRM,1,150);
616
617
618
619 End create_seeded_definitions;
620
621
622
623 /*******************************************************
624
625 This Procedure refreshes the document dimension definitions for the plan from the template.
626
627 This is required to get the original seeded data and then create the definitions again from the plan.
628
629 ********************************************************/
630
631
632
633 Procedure refresh_document_dimensions(p_demand_plan_id in number)
634
635
636
637 is
638
639
640
641 cursor c1 is
642
643 select template_id
644
645 from msd_demand_plans
646
647 where demand_plan_id = p_demand_plan_id;
648
649
650
651 cursor c2 is
652
653 select document_name,document_id
654
655 from msd_dp_seeded_documents
656
657 where demand_plan_id = p_demand_plan_id;
658
659
660
664
661 cursor c3(p_template_id in number, p_document_name in varchar2) is
662
663 select document_id
665 from msd_dp_seeded_documents
666
667 where demand_plan_id = p_template_id
668
669 and document_name = p_document_name;
670
671
672
673 cursor c4(p_document_id in number) is
674
675 select dimension_code
676
677 from msd_dp_seeded_doc_dimensions
678
679 where demand_plan_id = p_demand_plan_id
680
681 and document_id = p_document_id;
682
683
684
685 cursor c5(p_template_id in number, p_document_id in number, p_dimension_code in varchar2) is
686
687 select selection_script
688
689 from msd_dp_seeded_doc_dimensions
690
691 where demand_plan_id = p_template_id
692
693 and document_id = p_document_id
694
695 and dimension_code = p_dimension_code;
696
697
698
699 l_document_id number;
700
701 l_selection_script varchar2(4000);
702
703 l_template_id number;
704
705 cursor watefall_document_id is
706 select document_id
707 from msd_dp_seeded_documents
708 where demand_plan_id=p_demand_plan_id
709 and document_name = 'MSD_SD_EOL_LWF';
710
711 waterfall_doc_id number;
712
713 Begin
714
715
716 waterfall_doc_id := -1;
717
718 open watefall_document_id;
719 fetch watefall_document_id into waterfall_doc_id;
720 close watefall_document_id;
721
722
723 open c1;
724
725 fetch c1 into l_template_id;
726
727 close c1;
728
729
730
731 -- for each seeded document in the plan
732
733 for c2_cur in c2 loop
734
735
736
737 -- get the corresponding documents ID from template
738
739 open c3(l_template_id,c2_cur.document_name);
740
741 fetch c3 into l_document_id;
742
743 close c3;
744
745
746
747 -- for each dimension code in seeded document for the plan
748
749 for c4_cur in c4(c2_cur.document_id) loop
750
751
752
753 -- get the selection script for the corresponding dimension in template
754
755 open c5(l_template_id, l_document_id, c4_cur.dimension_code);
756
757 fetch c5 into l_selection_script;
758
759 close c5;
760
761
762
763 -- update the selection script for the dimension in the plan with the selection script for the corresponding dimension in template
764
765 if c2_cur.document_id=waterfall_doc_id and c4_cur.dimension_code = 'MEAS' then
766 null;
767 else
768
769 update msd_dp_seeded_doc_dimensions
770 set selection_script = l_selection_script
771 where demand_plan_id = p_demand_plan_id
772 and document_id = c2_cur.document_id
773 and dimension_code = c4_cur.dimension_code;
774 end if;
775
776
777 end loop;
778
779
780
781 end loop;
782
783
784
785 End refresh_document_dimensions;
786
787
788
789 /*******************************************************
790
791 This Procedure refreshes the formula definitions for the plan from the template.
792
793 This is required to get the original seeded data and then create the definitions again from the plan.
794
795 ********************************************************/
796
797
798
799 Procedure refresh_formulas(p_demand_plan_id in number)
800
801
802
803 is
804
805
806
807 cursor c1 is
808
809 select template_id
810
811 from msd_demand_plans
812
813 where demand_plan_id = p_demand_plan_id;
814
815
816
817 cursor c2 is
818
819 select formula_name,formula_id
820
821 from msd_dp_formulas
822
823 where demand_plan_id = p_demand_plan_id;
824
825
826
827 cursor c3(p_template_id in number, p_formula_name in varchar2) is
828
829 select equation, custom_field1, custom_field2, isby, numerator, denominator
830
831 from msd_dp_formulas
832
833 where demand_plan_id = p_template_id
834
835 and formula_name = p_formula_name;
836
837
838
839 l_equation varchar2(4000);
840
841 l_custom_field1 varchar2(4000);
842
843 l_custom_field2 varchar2(4000);
844
845 l_isby varchar2(4000);
846
847 l_numerator varchar2(4000);
848
849 l_denominator varchar2(4000);
850
851 l_template_id number;
852
853 Begin
854
855
856
857 open c1;
858
859 fetch c1 into l_template_id;
860
861 close c1;
862
863
864
865 -- for each formula in the plan
866
867 for c2_cur in c2 loop
868
869
870
871 -- get the equation, custom_field1, isby, numerator and denominator for the corresponding formula in template
872
873 open c3(l_template_id, c2_cur.formula_name);
874
875 fetch c3 into l_equation,l_custom_field1,l_custom_field2,l_isby,l_numerator,l_denominator;
876
877 close c3;
878
879
880
881 -- update equation, custom_field1, isby, numerator and denominator for the formula in plan with the corresponding formula in template
882
883 update msd_dp_formulas
884
885 set equation = l_equation,
886
887 custom_field1 = l_custom_field1,
888
889 custom_field2 = l_custom_field2,
893 numerator = l_numerator,
890
891 isby = l_isby,
892
894
895 denominator = l_denominator
896
897 where demand_plan_id = p_demand_plan_id
898
899 and formula_name = c2_cur.formula_name;
900
901
902
903 end loop;
904
905
906
907 End refresh_formulas;
908
909
910
911 /*******************************************************
912
913 This Procedure does the processing common to all plan types after the plan is copied from the template.
914
915 Called by apply_template.
916
917 ********************************************************/
918
919
920
921 Procedure common_post_copy_process(p_new_dp_id in number)
922
923 is
924
925
926
927 cursor c1 is
928
929 select scenario_id, scenario_name, description
930
931 from msd_dp_scenarios_cs_v
932
933 where demand_plan_id = p_new_dp_id;
934
935
936
937 BEGIN
938
939
940
941 -- Update the template_flag and default_template columns as copy plan sets these to 'Y'
942
943 update msd_demand_plans
944
945 set template_flag = 'N',
946
947 default_template = 'N'
948
949 where demand_plan_id = p_new_dp_id;
950
951
952
953 -- Update the Name and Description of Scenarios as these are seeded as messages
954
955 for c1_cur in c1 loop
956
957
958
959 fnd_message.set_name('MSD',c1_cur.scenario_name);
960
961
962
963 update msd_dp_scenarios
964
965 set scenario_name = fnd_message.get
966
967 where demand_plan_id = p_new_dp_id
968
969 and scenario_id = c1_cur.scenario_id;
970
971
972
973 fnd_message.set_name('MSD',c1_cur.description);
974
975
976
977 update msd_dp_scenarios
978
979 set description = fnd_message.get
980
981 where demand_plan_id = p_new_dp_id
982
983 and scenario_id = c1_cur.scenario_id;
984
985
986
987 fnd_message.set_name('MSD',c1_cur.description);
988
989
990
991 update msd_dp_scenarios_tl
992
993 set description = fnd_message.get
994
995 where demand_plan_id = p_new_dp_id
996
997 and scenario_id = c1_cur.scenario_id;
998
999
1000
1001 end loop;
1002
1003
1004
1005 END common_post_copy_process;
1006
1007
1008 /*******************************************************
1009
1010 This Procedure does the processing specific to plan type 'EOL' and CALULATED type of parameters after the plan is copied from the template and common processing.
1011
1012 Called by eol_post_copy_process.
1013
1014 ********************************************************/
1015
1016 procedure replace_parameter_tokens(p_demand_plan_id number)
1017 as
1018
1019 cursor c1 is
1020 select parameter_id
1021 from msd_dp_parameters
1022 where demand_plan_id=p_demand_plan_id
1023 and stream_type='CALCULATED';
1024
1025 cursor c2(p_parameter_id number) is
1026 select parameter_sequence,parameter_type,parameter_component,parameter_value,supply_plan_name
1027 from msd_dp_formula_parameters
1028 where demand_plan_id=p_demand_plan_id
1029 and formula_id=p_parameter_id
1030 order by parameter_sequence;
1031
1032 cursor c3(p_demand_plan_id number) is
1033 select parameter_type,parameter_id
1034 from msd_dp_parameters
1035 where demand_plan_id=p_demand_plan_id;
1036
1037 cursor c4(p_demand_plan_id number) is
1038 select parameter_id
1039 from msd_dp_parameters
1040 where demand_plan_id=p_demand_plan_id
1041 and post_calculation is not null;
1042
1043 l_parameter_value varchar2(4000);
1044 begin
1045
1046 for c1_rec in c1
1047 loop
1048 for c2_rec in c2(c1_rec.parameter_id)
1049 loop
1050
1051 if c2_rec.parameter_type = 'I' then
1052 l_parameter_value := 'V.'||c2_rec.parameter_component||get_parameter_id(p_demand_plan_id,c2_rec.parameter_value, c2_rec.supply_plan_name, c2_rec.parameter_component);
1053 else
1054 l_parameter_value := c2_rec.parameter_value;
1055 end if;
1056
1057 update msd_dp_parameters
1058 set equation = replace(equation,'%'||c2_rec.parameter_sequence||'%', l_parameter_value)
1059 where demand_plan_id = p_demand_plan_id
1060 and parameter_id = c1_rec.parameter_id;
1061
1062 end loop;
1063 end loop;
1064
1065 for c3_rec in c3(p_demand_plan_id)
1066 loop
1067 for c4_rec in c4(p_demand_plan_id)
1068 loop
1069 update msd_dp_parameters
1070 set post_calculation=replace(post_calculation,c3_rec.parameter_type,c3_rec.parameter_id)
1071 where demand_plan_id=p_demand_plan_id
1072 and parameter_id=c4_rec.parameter_id;
1073 end loop;
1074 end loop;
1075
1076 exception
1077 when others then
1078 null;
1079
1080 end replace_parameter_tokens;
1081
1082
1083 /*******************************************************
1084
1085 This Procedure does the processing specific to plan type 'EOL' after the plan is copied from the template and common processing.
1086
1087 Called by apply_template.
1088
1089 ********************************************************/
1090
1091
1092
1093 Procedure update_parameter_dates(p_demand_plan_id number)
1094 is
1095
1096 cursor c1 is
1097 select plan_type
1098 from msd_demand_plans
1099 where demand_plan_id=p_demand_plan_id;
1103 begin
1100
1101 p_plan_type varchar2(10);
1102
1104 open c1;
1105 fetch c1 into p_plan_type;
1106 close c1;
1107
1108 if p_plan_type = 'EOL' then
1109
1110 update msd_dp_parameters
1111 set start_date=msd_common_utilities.get_bucket_start_date(sysdate,1,6,'GREGORIAN'),
1112 end_date=msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN')
1113 where parameter_type in ('MSD_ON_HAND')
1114 and demand_plan_id=p_demand_plan_id;
1115
1116 update msd_dp_parameters
1117 set start_date=msd_common_utilities.get_bucket_start_date(sysdate,1,6,'GREGORIAN'),
1118 end_date=msd_common_utilities.get_bucket_end_date(sysdate,24,6,'GREGORIAN')
1119 where parameter_type in ('MSD_GROSS_REQ_EXCESS_HORIZON','MSD_INTRANSIT','MSD_ONORDER',
1120 'MSD_GROSS_REQ_OBS_DATE','MSD_TOTAL_SUPPLY','MSD_ORDER_FORECAST',
1121 'MSD_FORECAST_BASIS_LIAB','MSD_AUTHORIZATION','MSD_SUPPLY_COMMIT',
1122 'MSD_SHORTAGE','MSD_FORECAST_LIABILITY','MSD_ESTIMATED_FORECAST_LIAB',
1123 'MSD_SIM_END_ITEM_DEMAND','MSD_ESTIMATED_GROSS_REQ')
1124 and demand_plan_id=p_demand_plan_id;
1125
1126
1127 end if;
1128
1129 end update_parameter_dates;
1130
1131 /*******************************************************
1132
1133 This Procedure does the processing specific to plan type 'EOL' after the plan is copied from the template and common processing.
1134
1135 Called by apply_template.
1136
1137 ********************************************************/
1138
1139
1140
1141 Procedure eol_post_copy_process(p_new_dp_id in number)
1142
1143 is
1144
1145
1146
1147 cursor c1 is
1148
1149 select plan_type
1150
1151 from msd_demand_plans
1152
1153 where demand_plan_id = p_new_dp_id;
1154
1155
1156
1157 l_plan_type varchar2(80);
1158
1159
1160
1161
1162
1163 BEGIN
1164
1165
1166
1167 -- get the plan type
1168
1169 open c1;
1170
1171 fetch c1 into l_plan_type;
1172
1173 close c1;
1174
1175
1176
1177 if l_plan_type = 'EOL' then
1178
1179 -- insert ASCP specific data
1180 update_ascp_related_data(p_new_dp_id);
1181
1182 -- Replace associate parameetr types with id's
1183 replace_associate_parameters(p_new_dp_id);
1184
1185 -- Replace calculated parameetr equation tokens
1186 replace_parameter_tokens(p_new_dp_id);
1187
1188 -- Update the start and end dates for the parameters
1189 update_parameter_dates(p_new_dp_id);
1190 /* Bug 5169157 */
1191 /*update msd_dp_parameters set allo_agg_basis_stream_id = (select parameter_id
1192 from msd_dp_parameters
1193 where demand_plan_id=p_new_dp_id
1194 and parameter_type='MSD_FORECAST_BASIS_LIAB')
1195 where demand_plan_id=p_new_dp_id
1196 and parameter_type='MSD_SIM_END_ITEM_DEMAND'; */
1197
1198 fnd_message.set_name('MSD','MSD_SIM_DEMAND_DEP_DEM_DESC');
1199 update msd_dp_parameters
1200 set dependent_demand_desc = fnd_message.get
1201 where demand_plan_id=p_new_dp_id
1202 and parameter_type='MSD_SIM_END_ITEM_DEMAND';
1203
1204 end if;
1205
1206
1207
1208 END eol_post_copy_process;
1209
1210 /*******************************************************
1211
1212 This Procedure does the processing specific to plan type 'EOL' after the plan is copied from the template and common processing.
1213
1214 ********************************************************/
1215
1216 Procedure replace_associate_parameters(p_new_dp_id in number)
1217 is
1218 cursor c1(p_parameter_type varchar2) is
1219 select parameter_id
1220 from msd_dp_parameters
1221 where demand_plan_id=p_new_dp_id
1222 and parameter_type=p_parameter_type;
1223
1224 cursor c2 is
1225 select scenario_id
1226 from msd_dp_scenarios
1227 where demand_plan_id=p_new_dp_id;
1228
1229 cursor c3(p_scenario_id number) is
1230 select associate_parameter
1231 from msd_dp_scenarios
1232 where demand_plan_id=p_new_dp_id
1233 and scenario_id=p_scenario_id;
1234
1235
1236 p_associate_parameter varchar2(200);
1237 p_parameter_id number;
1238
1239 sql_stmt varchar2(1000);
1240
1241 begin
1242
1243 for c2_rec in c2
1244 loop
1245
1246 open c3(c2_rec.scenario_id);
1247 fetch c3 into p_associate_parameter;
1248 close c3;
1249
1250 open c1(p_associate_parameter);
1251 fetch c1 into p_parameter_id;
1252 close c1;
1253
1254 update msd_dp_scenarios
1255 set associate_parameter=p_parameter_id
1256 where demand_plan_id=p_new_dp_id
1257 and scenario_id=c2_rec.scenario_id;
1258
1259
1260 end loop;
1261
1262
1263
1264 END replace_associate_parameters;
1265
1266 /*******************************************************
1267
1268 This Procedure does the processing specific to plan type 'SOP' after the plan is copied from the template and common processing.
1269
1270 Called by apply_template.
1271
1272 ********************************************************/
1273
1274
1275
1276 Procedure sop_post_copy_process(p_new_dp_id in number)
1277
1278 is
1279
1280
1281
1282 cursor c1 is
1283
1284 select plan_type
1285
1286 from msd_demand_plans
1287
1288 where demand_plan_id = p_new_dp_id;
1289
1290
1291
1292 l_plan_type varchar2(80);
1293
1294
1295
1296
1300
1297
1298 BEGIN
1299
1301
1302 -- get the plan type
1303
1304 open c1;
1305
1306 fetch c1 into l_plan_type;
1307
1308 close c1;
1309
1310
1311
1312 if l_plan_type = 'SOP' then
1313
1314
1315
1316
1317
1318 -- set the start and end dates of input parameters to month start date of 18 months backwards and current month end date respectively
1319
1320 update msd_dp_parameters
1321
1322 set start_date = msd_common_utilities.get_bucket_start_date(sysdate,-18,6,'GREGORIAN'),
1323
1324 end_date = msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN')
1325
1326 where demand_plan_id = p_new_dp_id
1327
1328 and (supply_plan_flag <> 'Y'
1329
1330 or supply_plan_flag is null);
1331
1332
1333
1334 -- set the history start and end dates of scenarios to month start date of 18 months backwards and current month end date respectively
1335
1336 -- set the horizon start and end dates of scenarios to month start date of next month and end date of 19 months forwards respectively
1337
1338 update msd_dp_scenarios
1339
1340 set history_start_date = msd_common_utilities.get_bucket_start_date(sysdate,-18,6,'GREGORIAN'),
1341
1342 history_end_date = msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN'),
1343
1344 horizon_start_date = msd_common_utilities.get_bucket_start_date(sysdate,2,6,'GREGORIAN'),
1345
1346 horizon_end_date = msd_common_utilities.get_bucket_end_date(sysdate,19,6,'GREGORIAN')
1347
1348 where demand_plan_id = p_new_dp_id;
1349
1350
1351
1352 -- insert ASCP specific data
1353
1354 update_ascp_related_data(p_new_dp_id);
1355
1356
1357
1358 end if;
1359
1360
1361
1362 END sop_post_copy_process;
1363
1364
1365
1366 /*******************************************************
1367
1368 This Procedure does the processing specific to plan type 'LIABILITY' after the plan is copied from the template and common processing.
1369
1370 Called by apply_template.
1371
1372 ********************************************************/
1373
1374
1375
1376 Procedure liab_post_copy_process(p_new_dp_id in number)
1377
1378 is
1379
1380
1381
1382 cursor c1 is
1383
1384 select liab_plan_name, plan_type, organization_id, sr_instance_id
1385
1386 from msd_demand_plans
1387
1388 where demand_plan_id = p_new_dp_id;
1389
1390
1391
1392
1393
1394 l_supply_plan_name varchar2(240);
1395
1396 l_plan_type varchar2(80);
1397
1398 l_org_id number;
1399
1400 l_instance_id number;
1401
1402
1403
1404
1405
1406 BEGIN
1407
1408
1409
1410 open c1;
1411
1412 fetch c1 into l_supply_plan_name, l_plan_type, l_org_id, l_instance_id;
1413
1414 close c1;
1415
1416
1417
1418 if l_plan_type = 'LIABILITY' then
1419
1420
1421
1422
1423
1424 -- insert default manufacturing calendar for PDS base liability plans
1425
1426 if l_org_id <> -1 then
1427
1428
1429
1430 insert into msd_dp_calendars
1431
1432 (
1433
1434 DEMAND_PLAN_ID
1435
1436 ,CALENDAR_TYPE
1437
1438 ,CALENDAR_CODE
1439
1440 ,CREATION_DATE
1441
1442 ,CREATED_BY
1443
1444 ,LAST_UPDATE_DATE
1445
1446 ,LAST_UPDATED_BY
1447
1448 ,LAST_UPDATE_LOGIN
1449
1450 ,REQUEST_ID
1451
1452 ,PROGRAM_APPLICATION_ID
1453
1454 ,PROGRAM_ID
1455
1456 ,PROGRAM_UPDATE_DATE
1457
1458 ,DELETEABLE_FLAG
1459
1460 ,ENABLE_NONSEED_FLAG
1461
1462 )
1463
1464 values
1465
1466 (
1467
1468 p_new_dp_id
1469
1470 ,2
1471
1472 ,MSD_COMMON_UTILITIES_LB.get_default_mfg_cal ( l_org_id,l_instance_id)
1473
1474 ,SYSDATE
1475
1476 ,fnd_global.user_id
1477
1478 ,SYSDATE
1479
1480 ,fnd_global.user_id
1481
1482 ,fnd_global.login_id
1483
1484 ,NULL
1485
1486 ,NULL
1487
1488 ,NULL
1489
1490 ,SYSDATE
1491
1492 ,null
1493
1494 ,'Y'
1495
1496 );
1497
1498
1499
1500 -- set min time level for manufacturing calendar to Manufacturing Week
1501
1502 update msd_demand_plans
1503
1504 set m_min_tim_lvl_id = 1
1505
1506 where demand_plan_id = p_new_dp_id;
1507
1508
1509
1510 end if;
1511
1512
1513
1514 end if;
1515
1516 END liab_post_copy_process;
1517
1518
1519
1520 /*******************************************************
1521
1522 This Procedure does the processing common to all plan types after the plan is copied from the template and plan type specific processing.
1523
1524 Called by apply_template.
1525
1526 Calls Update_Formula_Names.
1527
1528 ********************************************************/
1529
1530
1531
1532 Procedure common_all_post_process(p_new_dp_id in number)
1533
1534 is
1535
1536
1537
1538 cursor c2 is
1539
1540 select parameter_id, price_list_name
1541
1542 from msd_dp_parameters
1543
1544 where demand_plan_id = p_new_dp_id
1545
1546 and price_list_name is not null;
1547
1551
1548
1549
1550 cursor c3 is
1552 select scenario_id, price_list_name
1553
1554 from msd_dp_scenarios
1555
1556 where demand_plan_id = p_new_dp_id
1557
1558 and price_list_name is not null;
1559
1560
1561
1562 cursor c4 is
1563
1564 select dp_price_list_id, price_list_name
1565
1566 from msd_dp_price_lists
1567
1568 where demand_plan_id = p_new_dp_id;
1569
1570
1571
1572 BEGIN
1573
1574
1575
1576 -- for all input parameters with price list name specified
1577
1578 for c2_cur in c2 loop
1579
1580
1581
1582 -- replace message with text
1583
1584 update msd_dp_parameters
1585
1586 set price_list_name = fnd_message.get_string('MSD',c2_cur.price_list_name)
1587
1588 where demand_plan_id = p_new_dp_id
1589
1590 and parameter_id = c2_cur.parameter_id;
1591
1592
1593
1594 end loop;
1595
1596
1597
1598 -- for all scenarios with price list name specified
1599
1600 for c3_cur in c3 loop
1601
1602
1603
1604 -- replace message with text
1605
1606 update msd_dp_scenarios
1607
1608 set price_list_name = fnd_message.get_string('MSD',c3_cur.price_list_name)
1609
1610 where demand_plan_id = p_new_dp_id
1611
1612 and scenario_id = c3_cur.scenario_id;
1613
1614
1615
1616 end loop;
1617
1618
1619
1620 -- for all price list name specified
1621
1622 for c4_cur in c4 loop
1623
1624
1625
1626 -- replace message with text
1627
1628 update msd_dp_price_lists
1629
1630 set price_list_name = fnd_message.get_string('MSD',c4_cur.price_list_name)
1631
1632 where demand_plan_id = p_new_dp_id
1633
1634 and dp_price_list_id = c4_cur.dp_price_list_id;
1635
1636
1637
1638 end loop;
1639
1640
1641
1642 if not g_call then
1643
1644 -- Relace messages with text
1645
1646 Update_Formula_Names(p_new_dp_id);
1647
1648 end if;
1649
1650
1651
1652 END common_all_post_process;
1653
1654
1655
1656 /*******************************************************
1657
1658 This Procedure replaces the descriptions of Formula Names seeded as messsages with message text.
1659
1660 Called by common_all_post_process.
1661
1662 ********************************************************/
1663
1664
1665
1666 Procedure Update_Formula_Names(p_new_dp_id in number)
1667
1668 is
1669
1670
1671
1672 cursor c1 is
1673
1674 select formula_name, formula_desc, formula_id
1675
1676 from msd_dp_formulas
1677
1678 where demand_plan_id = p_new_dp_id
1679
1680 order by creation_sequence;
1681
1682
1683
1684 cursor c2 is
1685
1686 select document_id, description
1687
1688 from msd_dp_seeded_documents
1689
1690 where demand_plan_id = p_new_dp_id;
1691
1692
1693
1694 BEGIN
1695
1696
1697
1698 -- for each formula for the given plan
1699
1700 for c1_cur in c1 loop
1701
1702
1703
1704 --fnd_message.set_name('MSD',c1_cur.formula_desc);
1705
1706
1707
1708 -- update the description seeded as message with message text
1709
1710 update msd_dp_formulas
1711
1712 --set formula_desc = fnd_message.get
1713
1714 set formula_desc = fnd_message.get_string('MSD',c1_cur.formula_desc)
1715
1716 where formula_id = c1_cur.formula_id
1717
1718 and demand_plan_id = p_new_dp_id;
1719
1720
1721
1722 end loop;
1723
1724
1725
1726 for c2_cur in c2 loop
1727
1728
1729
1730 --fnd_message.set_name('MSD',c2_cur.description);
1731
1732
1733
1734 -- update the description seeded as message with message text
1735
1736 update msd_dp_seeded_documents
1737
1738 --set description = fnd_message.get
1739
1740 set description = fnd_message.get_string('MSD',c2_cur.description)
1741
1742 where document_id = c2_cur.document_id
1743
1744 and demand_plan_id = p_new_dp_id;
1745
1746
1747
1748 end loop;
1749
1750
1751
1752 g_call := TRUE;
1753
1754
1755
1756 END Update_Formula_Names;
1757
1758
1759
1760 /*******************************************************
1761
1762 This Procedure replaces the tokens in formula fields like equation, custom_field1 etc with values stored in formula parameters.
1763
1764 Called by create_seeded_definitions.
1765
1766 ********************************************************/
1767
1768
1769
1770 Procedure Replace_formula_tokens(p_new_dp_id in number)
1771
1772 is
1773
1774
1775
1776 cursor c1 is
1777
1778 select formula_id
1779
1780 from msd_dp_formulas
1781
1782 where demand_plan_id = p_new_dp_id
1783
1784 order by creation_sequence;
1785
1786
1787
1788 cursor c2(p_formula_id in number) is
1789
1790 select where_used, parameter_sequence, parameter_type, parameter_component, parameter_value, supply_plan_name
1791
1792 from msd_dp_formula_parameters
1793
1794 where demand_plan_id = p_new_dp_id
1795
1796 and formula_id = p_formula_id
1797
1801
1798 and enabled_flag = 'Y'
1799
1800 order by parameter_sequence;
1802
1803
1804 l_parameter_value varchar2(4000);
1805
1806
1807
1808 BEGIN
1809
1810
1811
1812
1813
1814 -- for each formula for the given plan
1815
1816 for c1_cur in c1 loop
1817
1818
1819
1820 -- for each generic parameter of the formula
1821
1822 for c2_cur in c2(c1_cur.formula_id) loop
1823
1824
1825
1826 -- prefix V.Q. to the parameter value if type is input parameter to the parameter ID
1827
1828 if c2_cur.parameter_type = 'I' then
1829
1830 l_parameter_value := 'V.'||c2_cur.parameter_component||get_parameter_id(p_new_dp_id,c2_cur.parameter_value, c2_cur.supply_plan_name, c2_cur.parameter_component);
1831
1832 -- prefix SYSF to the parameter value if type is formula to the formula ID
1833
1834 elsif c2_cur.parameter_type = 'F' then
1835
1836 l_parameter_value := 'SYSF'||get_formula_id(p_new_dp_id,c2_cur.parameter_value, c2_cur.supply_plan_name);
1837
1838 else
1839
1840 l_parameter_value := c2_cur.parameter_value;
1841
1842 end if;
1843
1844
1845
1846 -- update the names with IDS
1847
1848 update msd_dp_formulas
1849
1850 set custom_field1 = replace(custom_field1,'%'||c2_cur.parameter_sequence||'%', l_parameter_value),
1851
1852 custom_field2 = replace(custom_field2,'%'||c2_cur.parameter_sequence||'%', l_parameter_value),
1853
1854 equation = replace(equation,'%'||c2_cur.parameter_sequence||'%', l_parameter_value)
1855
1856 where demand_plan_id = p_new_dp_id
1857
1858 and formula_id = c1_cur.formula_id;
1859
1860
1861
1862 end loop;
1863
1864
1865
1866
1867
1868 end loop;
1869
1870
1871
1872
1873
1874 END Replace_formula_tokens;
1875
1876
1877
1878
1879
1880 /*******************************************************
1881
1882 This Procedure parses the select list for dimensions with selection type as List. e.g. measures .
1883
1884 The list contains values seperated by '\n'
1885
1886 Called by create_seeded_definitions
1887
1888 NO MORE USED
1889
1890 ********************************************************/
1891
1892
1893
1894 Procedure Parse_Dimension_Select_List(p_new_dp_id in number)
1895
1896 is
1897
1898
1899
1900 cursor c1 is
1901
1902 select document_id, dimension_code, selection_sequence
1903
1904 from msd_dp_doc_dim_selections
1905
1906 where demand_plan_id = p_new_dp_id
1907
1908 and (dimension_code, document_id) in
1909
1910 (select dimension_code, document_id from msd_dp_seeded_doc_dimensions
1911
1912 where demand_plan_id = p_new_dp_id
1913
1914 and selection_type = 'L')
1915
1916 order by document_id,dimension_code,selection_sequence;
1917
1918
1919
1920
1921
1922 BEGIN
1923
1924
1925
1926
1927
1928
1929
1930 update msd_dp_seeded_doc_dimensions
1931
1932 set selection_script = ''
1933
1934 where demand_plan_id = p_new_dp_id
1935
1936 and selection_type = 'L';
1937
1938
1939
1940 for c1_cur in c1 loop
1941
1942
1943
1944 update msd_dp_seeded_doc_dimensions
1945
1946 set selection_script = selection_script||'%'||c1_cur.selection_sequence||'%\n'
1947
1948 where demand_plan_id = p_new_dp_id
1949
1950 and document_id = c1_cur.document_id
1951
1952 and dimension_code = c1_cur.dimension_code
1953
1954 and selection_type = 'L';
1955
1956
1957
1958 end loop;
1959
1960
1961
1962 exception
1963
1964 when others then
1965
1966 null;
1967
1968
1969
1970 END Parse_Dimension_Select_List;
1971
1972
1973
1974 /*******************************************************
1975
1976 This Procedure replaces the tokens in selection script with values of document selections.
1977
1978 Called by create_seeded_definitions.
1979
1980 ********************************************************/
1981
1982
1983
1984 Procedure Replace_dimension_tokens(p_new_dp_id in number)
1985
1986 is
1987
1988
1989
1990 cursor c1 is
1991
1992 select document_id, dimension_code
1993
1994 from msd_dp_seeded_doc_dimensions
1995
1996 where demand_plan_id = p_new_dp_id
1997
1998 order by sequence_number;
1999
2000
2001
2002 cursor c2(p_document_id in number,p_dimension_code in varchar2) is
2003
2004 select selection_sequence, selection_type, selection_component, selection_value, supply_plan_name
2005
2006 from msd_dp_doc_dim_selections
2007
2008 where demand_plan_id = p_new_dp_id
2009
2010 and document_id = p_document_id
2011
2012 and dimension_code = p_dimension_code
2013
2014 and enabled_flag = 'Y'
2015
2016 order by selection_sequence;
2017
2018
2019
2020 l_selection_value varchar2(4000);
2021
2022
2023
2024 l_parameter_id number;
2025
2026 l_formula_id number;
2027
2028 l_hierarchy_id number;
2029
2030 l_level_id number;
2031
2032 l_dimension_code varchar2(4);
2033
2034 l_dimension_script varchar2(100);
2035
2036
2037
2041
2038
2039
2040 BEGIN
2042
2043
2044
2045
2046 for c1_cur in c1 loop
2047
2048
2049
2050 for c2_cur in c2(c1_cur.document_id, c1_cur.dimension_code) loop
2051
2052 if c2_cur.selection_type = 'I' then
2053
2054 l_parameter_id := get_parameter_id(p_new_dp_id,c2_cur.selection_value, c2_cur.supply_plan_name, c2_cur.selection_component);
2055
2056 if l_parameter_id is not null then
2057
2058 l_selection_value := 'V.'||c2_cur.selection_component||l_parameter_id;
2059
2060 else
2061
2062 l_selection_value := null;
2063
2064 end if;
2065
2066 elsif c2_cur.selection_type = 'F' then
2067
2068 l_formula_id := get_formula_id(p_new_dp_id,c2_cur.selection_value, c2_cur.supply_plan_name);
2069
2070 if l_formula_id is not null then
2071
2072 l_selection_value := 'SYSF'||l_formula_id;
2073
2074 else
2075
2076 l_selection_value := null;
2077
2078 end if;
2079
2080 elsif c2_cur.selection_type = 'H' then
2081
2082 l_hierarchy_id := get_hierarchy_id(p_new_dp_id,c2_cur.selection_value);
2083
2084 if l_hierarchy_id is not null then
2085
2086 l_selection_value := 'H'||c2_cur.selection_value;
2087
2088 else
2089
2090 l_selection_value := null;
2091
2092 end if;
2093
2094 elsif c2_cur.selection_type = 'L' then
2095
2096 l_level_id := get_level_id(p_new_dp_id,c2_cur.selection_value);
2097
2098 if l_level_id is not null then
2099
2100 l_selection_value := 'L'||c2_cur.selection_value;
2101
2102 else
2103
2104 l_selection_value := null;
2105
2106 end if;
2107
2108 elsif c2_cur.selection_type = 'D' then
2109
2110 l_dimension_code := get_dimension_code(p_new_dp_id,c2_cur.selection_value);
2111
2112 l_selection_value := l_dimension_code;
2113
2114 elsif c2_cur.selection_type = 'DS' then
2115
2116 l_dimension_script := get_dimension_script(p_new_dp_id,c2_cur.selection_component, c2_cur.selection_value);
2117
2118 l_selection_value := l_dimension_script;
2119
2120 else
2121
2122 l_selection_value := c2_cur.selection_value;
2123
2124 end if;
2125
2126
2127
2128 update msd_dp_seeded_doc_dimensions
2129
2130 set selection_script = replace(selection_script,'%'||c2_cur.selection_sequence||'%', l_selection_value)
2131
2132 where demand_plan_id = p_new_dp_id
2133
2134 and document_id = c1_cur.document_id
2135
2136 and dimension_code = c1_cur.dimension_code;
2137
2138
2139
2140 end loop;
2141
2142 /*update msd_dp_seeded_doc_dimensions
2143 set enabled_flag = decode(nvl(selection_script,'NOT_POSS'), 'NOT_POSS', 'N', enabled_flag)
2144 where document_id = c1_cur.document_id
2145 and dimension_code = c1_cur.dimension_code
2146 and demand_plan_id=p_new_dp_id
2147 and dimension_code = 'MEAS';*/
2148
2149
2150 end loop;
2151
2152
2153
2154 END Replace_dimension_tokens;
2155
2156
2157
2158 /*******************************************************
2159
2160 This Procedure adds the ASCP specific scenario, input parameters, formulas and adds more measures to seeded docs.
2161
2162 Called by sop_post_copy_process, eol_post_copy_process.
2163
2164 Calls add_ascp_scenario, add_ascp_input_parameter, add_ascp_formula and add_ascp_measure.
2165
2166 ********************************************************/
2167
2168
2169
2170 Procedure update_ascp_related_data(p_new_dp_id in number)
2171
2172 is
2173
2174
2175
2176 cursor c1 is
2177
2178 select demand_plan_name
2179
2180 from msd_demand_plans
2181
2182 where demand_plan_id = p_new_dp_id;
2183
2184
2185
2186 cursor c2(p_demand_plan_name in varchar2) is
2187
2188 select distinct supply_plan_id, supply_plan_name -- Bug 4729854
2189
2190 from msd_dp_supply_plans
2191
2192 where demand_plan_name = p_demand_plan_name;
2193
2194
2195
2196 cursor c3(p_demand_plan_name in varchar2) is
2197
2198 select count(*)
2199
2200 from msd_dp_supply_plans
2201
2202 where demand_plan_name = p_demand_plan_name;
2203
2204
2205
2206 l_demand_plan_name varchar2(200);
2207
2208 l_count number;
2209
2210
2211 cursor get_plan_type is
2212 select plan_type
2213 from msd_demand_plans
2214 where demand_plan_id=p_new_dp_id;
2215
2216 p_plan_type varchar2(10);
2217
2218 l_liab_plan_id number;
2219
2220 BEGIN
2221
2222
2223
2224 l_count := 0;
2225
2226 -- get the plan type for the plan
2227
2228 open get_plan_type;
2229 fetch get_plan_type into p_plan_type;
2230 close get_plan_type;
2231
2232 -- get the name of the plan for the given ID
2233
2234 open c1;
2235
2236 fetch c1 into l_demand_plan_name;
2237
2238 close c1;
2239
2240 if p_plan_type='EOL' then
2241 select max(supply_plan_id) into l_liab_plan_id from msd_dp_supply_plans where demand_plan_name = l_demand_plan_name;
2242 update msd_demand_plans set liab_plan_id=l_liab_plan_id where demand_plan_id=p_new_dp_id;
2243 end if;
2244
2245 -- for each suuply plan selected in Template window
2246
2247 for c2_cur in c2(l_demand_plan_name) loop
2248
2249
2250
2251 if p_plan_type='EOL' then
2255 end if;
2252 add_ascp_scenario_for_eol(p_new_dp_id, c2_cur.supply_plan_id, c2_cur.supply_plan_name);
2253 else
2254 add_ascp_scenario(p_new_dp_id, c2_cur.supply_plan_id, c2_cur.supply_plan_name); -- Bug 4729854
2256
2257
2258 add_ascp_input_parameter(p_new_dp_id, c2_cur.supply_plan_id, c2_cur.supply_plan_name); -- Bug 4729854
2259
2260
2261
2262 add_ascp_formula(p_new_dp_id, c2_cur.supply_plan_id, c2_cur.supply_plan_name); -- Bug 4729854
2263
2264
2265
2266 add_ascp_measure(p_new_dp_id, c2_cur.supply_plan_id, c2_cur.supply_plan_name); -- Bug 4729854
2267
2268
2269
2270 end loop;
2271
2272
2273
2274 open c3(l_demand_plan_name);
2275
2276 fetch c3 into l_count;
2277
2278 close c3;
2279
2280
2281
2282 if l_count = 0 then
2283
2284
2285
2286 add_ascp_scenario(p_new_dp_id, null,null);
2287
2288
2289
2290 end if;
2291
2292
2293
2294 -- delete data as not required after this
2295
2296 delete from msd_dp_supply_plans
2297
2298 where demand_plan_name = l_demand_plan_name;
2299
2300
2301
2302 EXCEPTION
2303
2304
2305
2306 WHEN OTHERS THEN
2307
2308 null;
2309
2310
2311
2312 END update_ascp_related_data;
2313
2314
2315
2316 /*******************************************************
2317
2318 This Procedure checks if any of mandatory parameters for the formulas is disabled and validates the formulas.
2319
2320 Called by create_seeded_definitions.
2321
2322 For Future Use
2323
2324 ********************************************************/
2325
2326
2327
2328 Procedure Validate_formula_parameters(p_new_dp_id in number)
2329
2330 is
2331
2332
2333
2334 BEGIN
2335
2336 null;
2337
2338 END Validate_formula_parameters;
2339
2340
2341
2342
2343
2344 Procedure validate_formulas(p_new_dp_id in number)
2345
2346 is
2347
2348
2349
2350 cursor c1 is
2351
2352 select distinct formula_id
2353
2354 from msd_dp_formula_parameters
2355
2356 where demand_plan_id = p_new_dp_id
2357
2358 and mandatory_flag = 'Y'
2359
2360 and enabled_flag = 'N';
2361
2362
2363
2364 BEGIN
2365
2366
2367
2368 -- Set all formulas to valid first
2369
2370 update msd_dp_formulas
2371
2372 set valid_flag = 'Y'
2373
2374 where demand_plan_id = p_new_dp_id;
2375
2376
2377
2378 -- invalidate the formula if any of the mandatory parameter is disabled
2379
2380 for c1_cur in c1 loop
2381
2382 update msd_dp_formulas
2383
2384 set valid_flag = 'N'
2385
2386 where demand_plan_id = p_new_dp_id
2387
2388 and formula_id = c1_cur.formula_id;
2389
2390 end loop;
2391
2392
2393
2394 END validate_formulas;
2395
2396
2397
2398 Procedure validate_doc_dim_selections(p_new_dp_id in number)
2399
2400 is
2401
2402
2403
2404 cursor c1 is
2405
2406 select plan_type
2407
2408 from msd_demand_plans
2409
2410 where demand_plan_id = p_new_dp_id;
2411
2412
2413
2414 cursor c2 is
2415
2416 select distinct selection_value, dimension_code
2417
2418 from msd_dp_doc_dim_selections
2419
2420 where demand_plan_id = p_new_dp_id
2421
2422 and selection_type = 'L';
2423
2424
2425
2426 cursor c3 is
2427
2428 select distinct selection_value, dimension_code
2429
2430 from msd_dp_doc_dim_selections
2431
2432 where demand_plan_id = p_new_dp_id
2433
2434 and selection_type = 'H';
2435
2436
2437
2438 cursor c4(p_plan_type in varchar2, p_dimension_code in varchar2) is
2439
2440 select level_id
2441
2442 from msd_dp_scenario_output_levels
2443
2444 where demand_plan_id = p_new_dp_id
2445
2446 and level_id in
2447
2448 (select level_id from msd_levels
2449
2450 where dimension_code = p_dimension_code
2451
2452 and nvl(plan_type,'DP') = decode(p_plan_type,null,'DP','SOP','DP','EOL','DP',p_plan_type))
2453
2454 and rownum < 2;
2455
2456
2457
2458 cursor c5(p_plan_type in varchar2, p_dimension_code in varchar2) is
2459
2460 select hierarchy_id
2461
2462 from msd_dp_hierarchies
2463
2464 where demand_plan_id = p_new_dp_id
2465
2466 and hierarchy_id in
2467
2468 (select hierarchy_id from msd_hierarchies
2469
2470 where dimension_code = p_dimension_code
2471
2472 and nvl(plan_type,'DP') = decode(p_plan_type,null,'DP','SOP','DP','EOL','DP',p_plan_type))
2473
2474 and rownum < 2;
2475
2476
2477
2478 l_count number;
2479
2480 l_level_id number;
2481
2482 l_plan_type varchar2(240);
2483
2484 l_hierarchy_id number;
2485
2486
2487
2488 BEGIN
2489
2490
2491
2492 -- get the plan type
2493
2494 open c1;
2495
2496 fetch c1 into l_plan_type;
2497
2498 close c1;
2499
2500
2501
2502 -- For all levels in seeded docs
2503
2504 for c2_cur in c2 loop
2505
2506
2507
2511
2508 -- check if the seeded level exists or has been removed
2509
2510 select count(*) into l_count
2512 from msd_levels
2513
2514 where level_id = c2_cur.selection_value
2515
2516 and nvl(plan_type,'DP') = decode(l_plan_type,null,'DP','SOP','DP','EOL','DP',l_plan_type);
2517
2518
2519
2520 -- if removed then
2521
2522 if l_count = 0 then
2523
2524
2525
2526 -- get any other level in plan that exists
2527
2528 open c4(l_plan_type, c2_cur.dimension_code);
2529
2530
2531
2532 fetch c4 into l_level_id;
2533
2534
2535
2536 -- if no such level then
2537
2538 if c4%notfound then
2539
2540
2541
2542 -- disable the selection
2543
2544 update msd_dp_doc_dim_selections
2545
2546 set enabled_flag = 'N'
2547
2548 where demand_plan_id = p_new_dp_id
2549
2550 and selection_type = 'L'
2551
2552 and selection_value = c2_cur.selection_value
2553
2554 and dimension_code = c2_cur.dimension_code;
2555
2556
2557
2558 else
2559
2560
2561
2562 -- change the level
2563
2564 update msd_dp_doc_dim_selections
2565
2566 set selection_value = l_level_id
2567
2568 where demand_plan_id = p_new_dp_id
2569
2570 and selection_type = 'L'
2571
2572 and selection_value = c2_cur.selection_value
2573
2574 and dimension_code = c2_cur.dimension_code;
2575
2576
2577
2578 end if;
2579
2580
2581
2582 close c4;
2583
2584
2585
2586 end if;
2587
2588
2589
2590 end loop;
2591
2592
2593
2594 -- For all hierarchies in seeded docs
2595
2596 for c3_cur in c3 loop
2597
2598
2599
2600 -- check if the seeded hierarchy exists or has been removed
2601
2602 select count(*) into l_count
2603
2604 from msd_hierarchies
2605
2606 where hierarchy_id = c3_cur.selection_value
2607
2608 and nvl(plan_type,'DP') = decode(l_plan_type,null,'DP','SOP','DP','EOL','DP',l_plan_type);
2609
2610
2611
2612 -- if removed then
2613
2614 if l_count = 0 then
2615
2616
2617
2618 -- get any other hierarachy in plan that exists
2619
2620 open c5(l_plan_type, c3_cur.dimension_code);
2621
2622
2623
2624 fetch c5 into l_hierarchy_id;
2625
2626
2627
2628 -- if no such hierarchy then
2629
2630 if c5%notfound then
2631
2632
2633
2634 -- disable the selection
2635
2636 update msd_dp_doc_dim_selections
2637
2638 set enabled_flag = 'N'
2639
2640 where demand_plan_id = p_new_dp_id
2641
2642 and selection_type = 'H'
2643
2644 and selection_value = c3_cur.selection_value
2645
2646 and dimension_code = c3_cur.dimension_code;
2647
2648
2649
2650 else
2651
2652
2653
2654 -- change the hierarchy
2655
2656 update msd_dp_doc_dim_selections
2657
2658 set selection_value = l_hierarchy_id
2659
2660 where demand_plan_id = p_new_dp_id
2661
2662 and selection_type = 'H'
2663
2664 and selection_value = c3_cur.selection_value
2665
2666 and dimension_code = c3_cur.dimension_code;
2667
2668
2669
2670 end if;
2671
2672
2673
2674 close c5;
2675
2676
2677
2678 end if;
2679
2680
2681
2682 end loop;
2683
2684
2685
2686
2687
2688 END validate_doc_dim_selections;
2689
2690
2691
2692 /*******************************************************
2693
2694 This Procedure checks if any of mandatory selections for the dimension is disabled and enables the dimensions.
2695
2696 Called by create_seeded_definitions.
2697
2698 ********************************************************/
2699
2700
2701
2702 Procedure validate_doc_dimensions(p_new_dp_id in number)
2703
2704 is
2705
2706
2707
2708 cursor c1 is
2709
2710 select distinct document_id, dimension_code
2711
2712 from msd_dp_doc_dim_selections
2713
2714 where demand_plan_id = p_new_dp_id
2715
2716 and mandatory_flag = 'Y'
2717
2718 and enabled_flag = 'N'
2719
2720 and dimension_code <> 'MEAS'
2721
2722 order by document_id, dimension_code;
2723
2724
2725
2726 cursor c2 is
2727
2728 select distinct document_id
2729 from msd_dp_doc_dim_selections mdds,
2730 msd_dp_parameters mdp
2731 where mdds.demand_plan_id = p_new_dp_id
2732 and mdp.demand_plan_id=p_new_dp_id
2733 and mdp.parameter_type=mdds.selection_value
2734 and nvl(mdp.parameter_name,'ABCD')=nvl(mdds.supply_plan_name,'ABCD')
2735 and dimension_code = 'MEAS';
2736
2737
2738
2739 BEGIN
2740
2741
2742
2743 -- enable all dimensions except measures first
2744
2745 update msd_dp_seeded_doc_dimensions
2746
2747 set enabled_flag = 'Y'
2748
2749 where (document_id, dimension_code)
2750
2751 in
2752
2753 /*------Fix for bug 4550732--------*/
2754
2755 (select document_id, dimension_code from msd_dp_doc_dim_selections where demand_plan_id=p_new_dp_id)
2756
2757 and dimension_code <> 'MEAS';
2758
2762
2759
2760
2761 /* Bug 4288109 */
2763 -- disable all dimensions measures
2764
2765 update msd_dp_seeded_doc_dimensions dpdim
2766
2767 set enabled_flag = decode(dpdim.selection_type, 'S', 'Y', 'N')
2768
2769 where demand_plan_id = p_new_dp_id
2770
2771 and dimension_code = 'MEAS'
2772 and document_id <> (select document_id from msd_dp_seeded_documents where demand_plan_id=p_new_dp_id and
2773 document_name='MSD_EOL_WHEREUSED_RE');
2774
2775
2776
2777 for c1_cur in c1 loop
2778
2779
2780
2781 -- disable dimensions except measure which have any of the mandatory selection disabled
2782
2783 update msd_dp_seeded_doc_dimensions
2784
2785 set enabled_flag = 'N'
2786
2787 where demand_plan_id = p_new_dp_id
2788
2789 and document_id = c1_cur.document_id
2790
2791 and dimension_code = c1_cur.dimension_code;
2792
2793
2794
2795 end loop;
2796
2797
2798
2799 for c2_cur in c2 loop
2800
2801
2802
2803 -- enable measure dimension if one of the measures is enabled
2804
2805 update msd_dp_seeded_doc_dimensions
2806
2807 set enabled_flag = 'Y'
2808
2809 where demand_plan_id = p_new_dp_id
2810
2811 and document_id = c2_cur.document_id
2812
2813 and dimension_code = 'MEAS';
2814
2815
2816
2817 end loop;
2818
2819
2820 END validate_doc_dimensions;
2821
2822
2823
2824 /*******************************************************
2825
2826 This Procedure checks if any of mandatory dimensions for the document is disabled and validates the documents.
2827
2828 Called by create_seeded_definitions.
2829
2830 ********************************************************/
2831
2832
2833
2834 Procedure validate_documents(p_new_dp_id in number)
2835
2836 is
2837
2838
2839
2840 cursor c1 is
2841
2842 select distinct document_id
2843
2844 from msd_dp_seeded_doc_dimensions
2845
2846 where demand_plan_id = p_new_dp_id
2847
2848 and mandatory_flag = 'Y'
2849
2850 and enabled_flag = 'N';
2851
2852
2853
2854 BEGIN
2855
2856
2857
2858 -- validate all documents first
2859
2860 update msd_dp_seeded_documents
2861
2862 set valid_flag = 'Y'
2863
2864 where demand_plan_id = p_new_dp_id;
2865
2866
2867
2868 for c1_cur in c1 loop
2869
2870
2871
2872 -- invalidate documents if any of the mandatory dimension is disabled
2873
2874 update msd_dp_seeded_documents
2875
2876 set valid_flag = 'N'
2877
2878 where demand_plan_id = p_new_dp_id
2879
2880 and document_id = c1_cur.document_id;
2881
2882
2883
2884 end loop;
2885
2886
2887
2888 END validate_documents;
2889
2890
2891
2892 /*******************************************************
2893
2894 This Procedure changes the seeded defintions relevant for the dimension.
2895
2896 Called from form whenever user deletes a dimension.
2897
2898 ********************************************************/
2899
2900
2901
2902 procedure remove_dimension(
2903
2904 p_demand_plan_id in number,
2905
2906 p_dimension_code in varchar2,
2907
2908 p_dp_dimension_code in varchar2)
2909
2910
2911
2912 is
2913
2914
2915
2916 BEGIN
2917
2918 savepoint sp;
2919
2920
2921
2922 -- disable the dimension
2923
2924 update msd_dp_seeded_doc_dimensions
2925
2926 set enabled_flag = 'N'
2927
2928 where demand_plan_id = p_demand_plan_id
2929
2930 and dimension_code = p_dp_dimension_code;
2931
2932
2933
2934 -- disable all dimension selections which use related hierarchies and levels
2935
2936 update msd_dp_doc_dim_selections
2937
2938 set enabled_flag = 'N'
2939
2940 where demand_plan_id = p_demand_plan_id
2941
2942 and ((selection_type = 'H'
2943
2944 and selection_value in
2945
2946 (select distinct hierarchy_id from msd_hierarchies
2947
2948 where dimension_code = p_dp_dimension_code))
2949
2950 or (selection_type = 'L'
2951
2952 and selection_value in
2953
2954 (select distinct level_id from msd_levels
2955
2956 where dimension_code = p_dp_dimension_code)));
2957
2958
2959
2960 -- disable formula_parameters
2961
2962 update msd_dp_formula_parameters
2963
2964 set enabled_flag = 'N'
2965
2966 where demand_plan_id = p_demand_plan_id
2967
2968 and parameter_type = 'D'
2969
2970 and parameter_value = p_dp_dimension_code;
2971
2972
2973
2974 EXCEPTION
2975
2976
2977
2978 WHEN OTHERS THEN
2979
2980 rollback to sp;
2981
2982
2983
2984 END remove_dimension;
2985
2986
2987
2988 /*******************************************************
2989
2990 This Procedure changes the seeded defintions relevant for the parameter.
2991
2992 Called from form whenever user deletes an input parameter.
2993
2994 ********************************************************/
2995
2996
2997
2998 procedure remove_parameter(
2999
3000 p_demand_plan_id in number,
3001
3005
3002 p_parameter_id in number)
3003
3004
3006 is
3007
3008
3009
3010 cursor c1 is
3011
3012 select parameter_type, parameter_name
3013
3014 from msd_dp_parameters
3015
3016 where demand_plan_id = p_demand_plan_id
3017
3018 and parameter_id = p_parameter_id;
3019
3020
3021
3022 l_parameter_type varchar2(240);
3023
3024 l_parameter_name varchar2(240);
3025
3026
3027
3028 BEGIN
3029
3030 savepoint sp;
3031
3032
3033
3034
3035
3036 -- disable doc dim selections
3037
3038 update msd_dp_doc_dim_selections
3039
3040 set enabled_flag = 'N'
3041
3042 where demand_plan_id = p_demand_plan_id
3043
3044 and selection_value = l_parameter_type
3045
3046 and nvl(supply_plan_name,'~!#$%^&*') = nvl(l_parameter_name,'~!#$%^&*')
3047
3048 and selection_type = 'I';
3049
3050
3051
3052 -- disable formula parameters
3053
3054 update msd_dp_formula_parameters
3055
3056 set enabled_flag = 'N'
3057
3058 where demand_plan_id = p_demand_plan_id
3059
3060 and parameter_type = 'I'
3061
3062 and nvl(supply_plan_name,'~!#$%^&*') = nvl(l_parameter_name,'~!#$%^&*')
3063
3064 and parameter_value = l_parameter_type;
3065
3066
3067
3068 EXCEPTION
3069
3070
3071
3072 WHEN OTHERS THEN
3073
3074 rollback to sp;
3075
3076
3077
3078 END remove_parameter;
3079
3080
3081
3082 /*******************************************************
3083
3084 This Procedure changes the seeded defintions relevant for the scenario.
3085
3086 Called from form whenever user deletes a scenario.
3087
3088 FOR FUTURE USE
3089
3090 ********************************************************/
3091
3092
3093
3094 procedure remove_scenario(
3095
3096 p_demand_plan_id in number,
3097
3098 p_scenario_id in number)
3099
3100
3101
3102 is
3103
3104
3105
3106 cursor c1 is
3107
3108 select supply_plan_name, forecast_based_on, parameter_name
3109
3110 from msd_dp_scenarios
3111
3112 where demand_plan_id = p_demand_plan_id
3113
3114 and scenario_id = p_scenario_id;
3115
3116
3117
3118 l_supply_plan_name varchar2(80);
3119
3120 l_forecast_based_on varchar2(80);
3121
3122 l_parameter_name varchar2(80);
3123
3124
3125
3126 BEGIN
3127
3128 savepoint sp;
3129
3130
3131
3132
3133
3134 open c1;
3135
3136 fetch c1 into l_supply_plan_name, l_forecast_based_on, l_parameter_name;
3137
3138 close c1;
3139
3140
3141
3142 update msd_dp_formula_parameters
3143
3144 set enabled_flag = 'N'
3145
3146 where demand_plan_id = p_demand_plan_id
3147
3148 and parameter_component = 'SN'
3149
3150 and parameter_value = l_forecast_based_on
3151
3152 and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
3153
3154
3155
3156 update msd_dp_doc_dim_selections
3157
3158 set enabled_flag = 'N'
3159
3160 where demand_plan_id = p_demand_plan_id
3161
3162 and selection_component = 'SN'
3163
3164 and selection_value = l_forecast_based_on
3165
3166 and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
3167
3168
3169
3170 if l_supply_plan_name is not null then
3171
3172
3173
3174 delete from msd_dp_parameters
3175
3176 where demand_plan_id = p_demand_plan_id
3177
3178 and parameter_name = l_supply_plan_name;
3179
3180
3181
3182 end if;
3183
3184
3185
3186 EXCEPTION
3187
3188
3189
3190 WHEN OTHERS THEN
3191
3192 rollback to sp;
3193
3194
3195
3196 END remove_scenario;
3197
3198
3199
3200 procedure remove_scenario_event(
3201
3202 p_demand_plan_id in number,
3203
3204 p_scenario_id in number,
3205
3206 p_event_id in number)
3207
3208
3209
3210 is
3211
3212 BEGIN
3213
3214 null;
3215
3216 END remove_scenario_event;
3217
3218
3219
3220 /*******************************************************
3221
3222 This Procedure changes the seeded defintions relevant for the scenario output level.
3223
3224 Called from form whenever user deletes a scenario output level.
3225
3226 ********************************************************/
3227
3228
3229
3230 procedure remove_scenario_output_lvl(
3231
3232 p_demand_plan_id in number,
3233
3234 p_scenario_id in number,
3235
3236 p_level_id in number)
3237
3238
3239
3240 is
3241
3242
3243
3244 cursor c1 is
3245
3246 select enable_nonseed_flag
3247
3248 from msd_dp_scenarios
3249
3250 where demand_plan_id = p_demand_plan_id
3251
3252 and scenario_id = p_scenario_id;
3253
3254
3255
3256 l_nonseed_flag varchar2(15);
3257
3258
3259
3260 BEGIN
3261
3262 savepoint sp;
3263
3264
3265
3266 open c1;
3267
3268 fetch c1 into l_nonseed_flag;
3269
3273
3270 close c1;
3271
3272
3274 -- do only for seeded scenarios
3275
3276 if l_nonseed_flag is null or l_nonseed_flag <> 'Y' then
3277
3278
3279
3280 -- disable doc dim selections
3281
3282 update msd_dp_doc_dim_selections
3283
3284 set enabled_flag = 'N'
3285
3286 where demand_plan_id = p_demand_plan_id
3287
3288 and selection_type = 'L'
3289
3290 and selection_value = p_level_id
3291
3292 and enabled_flag = 'Y';
3293
3294
3295
3296 end if;
3297
3298
3299
3300 EXCEPTION
3301
3302
3303
3304 WHEN OTHERS THEN
3305
3306 rollback to sp;
3307
3308
3309
3310 END remove_scenario_output_lvl;
3311
3312
3313
3314 /*******************************************************
3315
3316 This Procedure changes the seeded defintions relevant for the scenario event.
3317
3318 Called from form whenever user deletes a scenario event.
3319
3320 FOR FUTURE USE
3321
3322 ********************************************************/
3323
3324
3325
3326 procedure remove_event(
3327
3328 p_demand_plan_id in number,
3329
3330 p_dp_event_id in number)
3331
3332
3333
3334 is
3335
3336 BEGIN
3337
3338 null;
3339
3340 END remove_event;
3341
3342
3343
3344
3345
3346 procedure remove_price_list(
3347
3348 p_demand_plan_id in number,
3349
3350 p_dp_price_list_id in number)
3351
3352
3353
3354 is
3355
3356 BEGIN
3357
3358 null;
3359
3360 END remove_price_list;
3361
3362
3363
3364 /*******************************************************
3365
3366 This Procedure changes the seeded defintions relevant for the calendar.
3367
3368 Called from form whenever user deletes a calendar.
3369
3370 FOR FUTURE USE
3371
3372 ********************************************************/
3373
3374
3375
3376 procedure remove_calendar(
3377
3378 p_demand_plan_id in number,
3379
3380 p_calendar_type in varchar2,
3381
3382 p_calendar_code in varchar2)
3383
3384
3385
3386 is
3387
3388
3389
3390 BEGIN
3391
3392 null;
3393
3394 END remove_calendar;
3395
3396
3397
3398 /*******************************************************
3399
3400 This Procedure changes the seeded defintions relevant for the hierarchy.
3401
3402 Called from form whenever user deletes a hierarchy.
3403
3404 ********************************************************/
3405
3406
3407
3408 procedure remove_hierarchy(
3409
3410 p_demand_plan_id in number,
3411
3412 p_dp_dimension_code in varchar2,
3413
3414 p_hierarchy_id in number)
3415
3416 is
3417
3418 BEGIN
3419
3420 savepoint sp;
3421
3422
3423
3424 -- disable doc dim selections
3425
3426 update msd_dp_doc_dim_selections
3427
3428 set enabled_flag = 'N'
3429
3430 where demand_plan_id = p_demand_plan_id
3431
3432 and selection_type = 'H'
3433
3434 and selection_value = p_hierarchy_id;
3435
3436
3437
3438 -- disable formula parameters
3439
3440 update msd_dp_formula_parameters
3441
3442 set enabled_flag = 'N'
3443
3444 where demand_plan_id = p_demand_plan_id
3445
3446 and parameter_type = 'H'
3447
3448 and parameter_value = p_hierarchy_id;
3449
3450
3451
3452 EXCEPTION
3453
3454
3455
3456 WHEN OTHERS THEN
3457
3458 rollback to sp;
3459
3460
3461
3462 END remove_hierarchy;
3463
3464
3465
3466 /*******************************************************
3467
3468 This Procedure changes the seeded defintions relevant for the dimension.
3469
3470 Called from form whenever user adds a dimension.
3471
3472 ********************************************************/
3473
3474
3475
3476 procedure add_dimension(
3477
3478 p_demand_plan_id in number,
3479
3480 p_dimension_code in varchar2,
3481
3482 p_dp_dimension_code in varchar2)
3483
3484 is
3485
3486
3487
3488 BEGIN
3489
3490 savepoint sp;
3491
3492
3493
3494 -- enable dimensions
3495
3496 update msd_dp_seeded_doc_dimensions
3497
3498 set enabled_flag = 'Y'
3499
3500 where demand_plan_id = p_Demand_plan_id
3501
3502 and dimension_code = p_dp_dimension_code
3503
3504 and enabled_flag = 'N';
3505
3506
3507
3508 -- enable document selections that use related hierarchies and levels
3509
3510 update msd_dp_doc_dim_selections
3511
3512 set enabled_flag = 'Y'
3513
3514 where demand_plan_id = p_Demand_plan_id
3515
3516 and ((selection_type = 'H'
3517
3518 and selection_value in
3519
3520 (select distinct hierarchy_id from msd_hierarchies
3521
3522 where dimension_code = p_dp_dimension_code))
3523
3524 or (selection_type = 'L'
3525
3526 and selection_value in
3527
3528 (select distinct level_id from msd_levels
3529
3530 where dimension_code = p_dp_dimension_code)));
3531
3532
3536 update msd_dp_formula_parameters
3533
3534 -- enable formula paraneters
3535
3537
3538 set enabled_flag = 'Y'
3539
3540 where demand_plan_id = p_Demand_plan_id
3541
3542 and parameter_type = 'D'
3543
3544 and parameter_value = p_dp_dimension_code
3545
3546 and enabled_flag = 'N';
3547
3548
3549
3550 EXCEPTION
3551
3552
3553
3554 WHEN OTHERS THEN
3555
3556 rollback to sp;
3557
3558
3559
3560 END add_dimension;
3561
3562
3563
3564 /*******************************************************
3565
3566 This Procedure changes the seeded defintions relevant for the parameter.
3567
3568 Called from form whenever user adds an input parameter.
3569
3570 ********************************************************/
3571
3572
3573
3574 procedure add_parameter(p_demand_plan_id in number,
3575
3576 p_parameter_type in varchar2,
3577
3578 p_parameter_name in varchar2)
3579
3580
3581
3582 is
3583
3584 BEGIN
3585
3586 savepoint sp;
3587
3588
3589
3590 -- enable doc dim selections
3591
3592 update msd_dp_doc_dim_selections
3593
3594 set enabled_flag = 'Y'
3595
3596 where demand_plan_id = p_Demand_plan_id and selection_type = 'I'
3597
3598 and selection_value = p_parameter_type
3599
3600 and nvl(supply_plan_name,'~!#$%^&*') = nvl(p_parameter_name,'~!#$%^&*')
3601
3602 and enabled_flag = 'N';
3603
3604
3605
3606 -- enable formula parameters
3607
3608 update msd_dp_formula_parameters
3609
3610 set enabled_flag = 'Y'
3611
3612 where demand_plan_id = p_Demand_plan_id
3613
3614 and parameter_type = 'I'
3615
3616 and parameter_value = p_parameter_type
3617
3618 and nvl(supply_plan_name,'~!#$%^&*') = nvl(p_parameter_name,'~!#$%^&*')
3619
3620 and enabled_flag = 'N';
3621
3622
3623
3624 EXCEPTION
3625
3626
3627
3628 WHEN OTHERS THEN
3629
3630 rollback to sp;
3631
3632
3633
3634 END add_parameter;
3635
3636
3637
3638 /*******************************************************
3639
3640 This Procedure changes the seeded defintions relevant for the scenario.
3641
3642 Called from form whenever user adds a scenario.
3643
3644 FOR FUTURE USE
3645
3646 ********************************************************/
3647
3648
3649
3650 procedure add_scenario(
3651
3652 p_demand_plan_id in number,
3653
3654 p_scenario_name in varchar2)
3655
3656
3657
3658 is
3659
3660 cursor c1 is
3661
3662 select supply_plan_name, forecast_based_on, parameter_name
3663
3664 from msd_dp_scenarios
3665
3666 where demand_plan_id = p_demand_plan_id
3667
3668 and scenario_name = p_scenario_name;
3669
3670
3671
3672 l_supply_plan_name varchar2(80);
3673
3674 l_forecast_based_on varchar2(80);
3675
3676 l_parameter_name varchar2(80);
3677
3678
3679
3680 BEGIN
3681
3682 savepoint sp;
3683
3684
3685
3686 open c1;
3687
3688 fetch c1 into l_supply_plan_name, l_forecast_based_on, l_parameter_name;
3689
3690 close c1;
3691
3692
3693
3694 update msd_dp_formula_parameters
3695
3696 set enabled_flag = 'Y'
3697
3698 where demand_plan_id = p_demand_plan_id
3699
3700 and parameter_component = 'SN'
3701
3702 and parameter_value = l_forecast_based_on
3703
3704 and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
3705
3706
3707
3708 update msd_dp_doc_dim_selections
3709
3710 set enabled_flag = 'Y'
3711
3712 where demand_plan_id = p_demand_plan_id
3713
3714 and selection_component = 'SN'
3715
3716 and selection_value = l_forecast_based_on
3717
3718 and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
3719
3720
3721
3722 EXCEPTION
3723
3724
3725
3726 WHEN OTHERS THEN
3727
3728 rollback to sp;
3729
3730
3731
3732 END add_scenario;
3733
3734
3735
3736
3737
3738 /*******************************************************
3739
3740 This Procedure changes the seeded defintions relevant for the scenario event.
3741
3742 Called from form whenever user adds a scenario event.
3743
3744 FOR FUTURE USE
3745
3746 ********************************************************/
3747
3748
3749
3750 procedure add_event(
3751
3752 p_demand_plan_id in number,
3753
3754 p_event_id in number)
3755
3756
3757
3758 is
3759
3760 BEGIN
3761
3762 null;
3763
3764 END add_event;
3765
3766
3767
3768 procedure add_price_list(
3769
3770 p_demand_plan_id in number,
3771
3772 p_dp_price_list_id in number) /*--Bug # 4549068-- Instead of price_list_name, price_list_id will be passed.---*/
3773
3774
3775
3776 is
3777
3778 BEGIN
3779
3780 null;
3781
3782 END add_price_list;
3783
3784
3785
3786 /*******************************************************
3787
3791
3788 This Procedure changes the seeded defintions relevant for the calendar.
3789
3790 Called from form whenever user adds a calendar.
3792 FOR FUTURE USE
3793
3794 ********************************************************/
3795
3796
3797
3798 procedure add_calendar(
3799
3800 p_demand_plan_id in number,
3801
3802 p_calendar_type in varchar2,
3803
3804 p_calendar_code in varchar2)
3805
3806
3807
3808 is
3809
3810 BEGIN
3811
3812 null;
3813
3814 END add_calendar;
3815
3816
3817
3818 /*******************************************************
3819
3820 This Procedure changes the seeded defintions relevant for the hierarchy.
3821
3822 Called from form whenever user adds a hierarchy.
3823
3824 ********************************************************/
3825
3826
3827
3828 procedure add_hierarchy(
3829
3830 p_demand_plan_id in number,
3831
3832 p_dp_dimension_code in varchar2,
3833
3834 p_hierarchy_id in number)
3835
3836
3837
3838 is
3839
3840 /*----Bug 4550732----If a dimension is added first time, it will be inserted into seeded dimensions----*/
3841
3842
3843
3844
3845
3846 cursor c1(p_document_id in number)is
3847
3848 select max(sequence_number)+1
3849
3850 from msd_dp_seeded_doc_dimensions
3851
3852 where demand_plan_id = p_demand_plan_id
3853
3854 and document_id = p_document_id
3855
3856 and axis = 'Z';
3857
3858
3859
3860 cursor c2 is
3861
3862 select distinct document_id
3863
3864 from msd_dp_seeded_documents
3865
3866 where demand_plan_id = p_demand_plan_id;
3867
3868
3869
3870
3871 l_sequence_number number;
3872
3873 l_count number;
3874
3875 l_dimension_code varchar2(30);
3876
3877 l_coll_dim number;
3878
3879
3880
3881
3882 BEGIN
3883
3884 savepoint sp;
3885
3886
3887
3888 -- enable doc dim selections
3889
3890 update msd_dp_doc_dim_selections
3891
3892 set enabled_flag = 'Y'
3893
3894 where demand_plan_id = p_demand_plan_id
3895
3896 and selection_type = 'H'
3897
3898 and selection_value = p_hierarchy_id
3899
3900 and enabled_flag = 'N';
3901
3902
3903
3904 /*----Bug 4550732----If a dimension is added first time, it will be inserted into seeded dimensions----*/
3905
3906
3907
3908 l_dimension_code:=p_dp_dimension_code;
3909
3910
3911
3912 select count(*) into l_count
3913
3914 from msd_dp_seeded_doc_dimensions
3915
3916 where demand_plan_id = p_demand_plan_id
3917
3918 and dimension_code = l_dimension_code;
3919
3920 l_coll_dim := 0;
3921
3922 select count(*) into l_coll_dim
3923 from msd_dp_dimensions
3924 where demand_plan_id=p_demand_plan_id
3925 and dimension_code = l_dimension_code
3926 and dp_dimension_code=l_dimension_code;
3927
3928 -- if not then
3929
3930 if l_count = 0 and l_coll_dim <> 0 then
3931
3932
3933
3934
3935
3936
3937
3938 -- for all the documents in the plan
3939
3940 for c2_cur in c2 loop
3941
3942
3943
3944 -- get the next sequence number in 'z' axis for the document
3945
3946 open c1(c2_cur.document_id);
3947
3948 fetch c1 into l_sequence_number;
3949
3950 close c1;
3951
3952
3953
3954 -- include the dimension in the document at 'z' axis
3955
3956 insert into msd_dp_seeded_doc_dimensions
3957
3958 (
3959
3960 DEMAND_PLAN_ID
3961
3962 ,DOCUMENT_ID
3963
3964 ,DIMENSION_CODE
3965
3966 ,SEQUENCE_NUMBER
3967
3968 ,AXIS
3969
3970 ,HIERARCHY_ID
3971
3972 ,SELECTION_TYPE
3973
3974 ,SELECTION_SCRIPT
3975
3976 ,ENABLED_FLAG
3977
3978 ,MANDATORY_FLAG
3979
3980 ,LAST_UPDATED_BY
3981
3982 ,CREATION_DATE
3983
3984 ,CREATED_BY
3985
3986 ,LAST_UPDATE_LOGIN
3987
3988 ,REQUEST_ID
3989
3990 ,PROGRAM_APPLICATION_ID
3991
3992 ,PROGRAM_ID
3993
3994 ,PROGRAM_UPDATE_DATE
3995
3996 ,LAST_UPDATE_DATE
3997
3998 )
3999
4000 VALUES
4001
4002 (
4003
4004 p_demand_plan_id
4005
4006 ,c2_cur.document_id
4007
4008 ,l_dimension_code
4009
4010 ,l_sequence_number
4011
4012 ,'Z'
4013
4014 ,p_hierarchy_id
4015
4016 ,'S'
4017
4018 ,'limit '||l_dimension_code||' to '||l_dimension_code||'.L.REL eq 1'
4019
4020 ,'Y'
4021
4022 ,'N'
4023
4024 ,fnd_global.user_id
4025
4026 ,SYSDATE
4027
4028 ,fnd_global.user_id
4029
4030 ,fnd_global.login_id
4031
4032 ,NULL
4033
4034 ,NULL
4035
4036 ,NULL
4037
4038 ,SYSDATE
4039
4040 ,SYSDATE
4041
4042 );
4043
4044
4045
4046 end loop;
4047
4048
4049
4050 end if;
4051
4052
4053
4054
4055
4056 EXCEPTION
4057
4058
4059
4063
4060 WHEN OTHERS THEN
4061
4062 rollback to sp;
4064
4065
4066 END add_hierarchy;
4067
4068
4069
4070 /*******************************************************
4071
4072 This Procedure changes the seeded defintions relevant for the scenario event.
4073
4074 Called from form whenever user adds a scenario event.
4075
4076 FOR FUTURE USE
4077
4078 ********************************************************/
4079
4080
4081
4082 procedure add_scenario_event(
4083
4084 p_demand_plan_id in number,
4085
4086 p_scenario_id in number,
4087
4088 p_event_id in number)
4089
4090
4091
4092 is
4093
4094 BEGIN
4095
4096 null;
4097
4098 END add_scenario_event;
4099
4100
4101
4102 /*******************************************************
4103
4104 This Procedure changes the seeded defintions relevant for the scenario output level.
4105
4106 Called from form whenever user adds a scenario output level.
4107
4108 ********************************************************/
4109
4110
4111
4112 procedure add_scenario_output_lvl(
4113
4114 p_demand_plan_id in number,
4115
4116 p_scenario_id in number,
4117
4118 p_level_id in number)
4119
4120
4121
4122 is
4123
4124
4125
4126 cursor c1 is
4127
4128 select enable_nonseed_flag
4129
4130 from msd_dp_scenarios
4131
4132 where demand_plan_id = p_demand_plan_id
4133
4134 and scenario_id = p_scenario_id;
4135
4136
4137
4138 cursor c2 is
4139
4140 select dimension_code
4141
4142 from msd_levels
4143
4144 where level_id = p_level_id;
4145
4146
4147
4148 cursor c3(p_dimension_code in varchar2) is
4149
4150 select distinct hierarchy_id
4151
4152 from msd_dp_hierarchies
4153
4154 where demand_plan_id = p_demand_plan_id
4155
4156 and dp_dimension_code = p_dimension_code;
4157
4158
4159
4160 cursor c4(p_document_id in number)is
4161
4162 select max(sequence_number)+1
4163
4164 from msd_dp_seeded_doc_dimensions
4165
4166 where demand_plan_id = p_demand_plan_id
4167
4168 and document_id = p_document_id
4169
4170 and axis = 'Z';
4171
4172
4173
4174 cursor c5 is
4175
4176 select distinct document_id
4177
4178 from msd_dp_seeded_documents
4179
4180 where demand_plan_id = p_demand_plan_id;
4181
4182
4183
4184 l_hierarchy_id number;
4185
4186 l_sequence_number number;
4187
4188 l_count number;
4189
4190 l_nonseed_flag varchar2(15);
4191
4192 l_dimension_code varchar2(30);
4193
4194
4195
4196 BEGIN
4197
4198 savepoint sp;
4199
4200
4201
4202 open c1;
4203
4204 fetch c1 into l_nonseed_flag;
4205
4206 close c1;
4207
4208
4209
4210 open c2;
4211
4212 fetch c2 into l_dimension_code;
4213
4214 close c2;
4215
4216
4217
4218 if l_nonseed_flag is null or l_nonseed_flag <> 'Y' then
4219
4220
4221
4222 -- enable doc dim selections
4223
4224 update msd_dp_doc_dim_selections
4225
4226 set enabled_flag = 'Y'
4227
4228 where demand_plan_id = p_demand_plan_id
4229
4230 and selection_type = 'L'
4231
4232 and selection_value = p_level_id
4233
4234 and enabled_flag = 'N';
4235
4236
4237
4238 -- find out if the dimension of the level is included in all documents. assuming that if included in one means included in all.
4239
4240 select count(*) into l_count
4241
4242 from msd_dp_seeded_doc_dimensions
4243
4244 where demand_plan_id = p_demand_plan_id
4245
4246 and dimension_code = l_dimension_code;
4247
4248
4249
4250 /*------------bug 4610798--------*/
4251
4252 -- Dimension is added when a hierachy is added.
4253
4254 /*
4255
4256 -- if not then
4257
4258 if l_count = 0 then
4259
4260
4261
4262 -- get the first hierarchy for the dimension in the plan
4263
4264 open c3(l_dimension_code);
4265
4266 fetch c3 into l_hierarchy_id;
4267
4268 close c3;
4269
4270
4271
4272 -- for all the documents in the plan
4273
4274 for c5_cur in c5 loop
4275
4276
4277
4278 -- get the next sequence number in 'z' axis for the document
4279
4280 open c4(c5_cur.document_id);
4281
4282 fetch c4 into l_sequence_number;
4283
4284 close c4;
4285
4286
4287
4288 -- include the dimension in the document at 'z' axis
4289
4290 insert into msd_dp_seeded_doc_dimensions
4291
4292 (
4293
4294 DEMAND_PLAN_ID
4295
4296 ,DOCUMENT_ID
4297
4298 ,DIMENSION_CODE
4299
4300 ,SEQUENCE_NUMBER
4301
4302 ,AXIS
4303
4304 ,HIERARCHY_ID
4305
4306 ,SELECTION_TYPE
4307
4308 ,SELECTION_SCRIPT
4309
4310 ,ENABLED_FLAG
4311
4312 ,MANDATORY_FLAG
4313
4314 ,LAST_UPDATED_BY
4315
4316 ,CREATION_DATE
4317
4318 ,CREATED_BY
4319
4320 ,LAST_UPDATE_LOGIN
4321
4322 ,REQUEST_ID
4323
4327
4324 ,PROGRAM_APPLICATION_ID
4325
4326 ,PROGRAM_ID
4328 ,PROGRAM_UPDATE_DATE
4329
4330 ,LAST_UPDATE_DATE
4331
4332 )
4333
4334 VALUES
4335
4336 (
4337
4338 p_demand_plan_id
4339
4340 ,c5_cur.document_id
4341
4342 ,l_dimension_code
4343
4344 ,l_sequence_number
4345
4346 ,'Z'
4347
4348 ,l_hierarchy_id
4349
4350 ,'S'
4351
4352 ,'limit '||l_dimension_code||' to '||l_dimension_code||'.L.REL eq 1'
4353
4354 ,'Y'
4355
4356 ,'N'
4357
4358 ,fnd_global.user_id
4359
4360 ,SYSDATE
4361
4362 ,fnd_global.user_id
4363
4364 ,fnd_global.login_id
4365
4366 ,NULL
4367
4368 ,NULL
4369
4370 ,NULL
4371
4372 ,SYSDATE
4373
4374 ,SYSDATE
4375
4376 );
4377
4378
4379
4380 end loop;
4381
4382
4383
4384 end if;
4385
4386 */
4387
4388 end if;
4389
4390
4391
4392 EXCEPTION
4393
4394
4395
4396 WHEN OTHERS THEN
4397
4398 rollback to sp;
4399
4400
4401
4402 END add_scenario_output_lvl;
4403
4404
4405
4406 /*******************************************************
4407
4408 This Procedure changes the seeded defintions relevant for the output period.
4409
4410 Time dimensions uses function of the form call SL.LIMIT.ROLLTIM(%1%, %2%, %3%) for SNOP Plans
4411
4412 %1% is used for start bucket, %2% is end bucket and %3% is period type id.
4413
4414 Called from form whenever user changes an output period type.
4415
4416 ********************************************************/
4417
4418
4419
4420 procedure change_output_period(
4421
4422 p_demand_plan_id in varchar2,
4423
4424 p_scenario_id in varchar2,
4425
4426 p_output_period_type_id in varchar2,
4427
4428 p_old_output_period_type_id in varchar2)
4429
4430
4431
4432 is
4433
4434
4435
4436 cursor c1 is
4437
4438 select enable_nonseed_flag
4439
4440 from msd_dp_scenarios
4441
4442 where demand_plan_id = p_demand_plan_id
4443
4444 and scenario_id = p_scenario_id;
4445
4446
4447
4448 cursor c2 is
4449
4450 select distinct document_id
4451
4452 from msd_dp_seeded_doc_dimensions
4453
4454 where demand_plan_id = p_demand_plan_id
4455
4456 and dimension_code = 'TIM'
4457
4458 and upper(selection_script) like 'CALL SL.LIMIT.ROLLTIM(%'||p_old_output_period_type_id||')';
4459
4460
4461
4462 cursor c3(p_document_id in number) is
4463
4464 select selection_value, selection_sequence
4465
4466 from msd_dp_doc_dim_selections
4467
4468 where demand_plan_id = p_demand_plan_id
4469
4470 and dimension_code = 'TIM'
4471
4472 and selection_sequence in (1,2)
4473
4474 and document_id = p_document_id;
4475
4476
4477
4478 l_nonseed_flag varchar2(15);
4479
4480 l_calendar_code varchar2(240);
4481
4482 l_start_date date;
4483
4484 l_end_date date;
4485
4486 l_new_selection_value number;
4487
4488 l_errcode varchar2(240);
4489
4490
4491
4492 BEGIN
4493
4494 savepoint sp;
4495
4496
4497
4498 open c1;
4499
4500 fetch c1 into l_nonseed_flag;
4501
4502 close c1;
4503
4504
4505
4506 -- do only for seeded scenarios
4507
4508 if (l_nonseed_flag is null or l_nonseed_flag <> 'Y' ) and nvl(p_old_output_period_type_id,0) <> nvl(p_output_period_type_id,0) then
4509
4510
4511
4512
4513
4514 -- for all documents having time in rolling buckets
4515
4516 for c2_cur in c2 loop
4517
4518
4519
4520
4521
4522 -- for first 2 parameters of the rolling time fucntion
4523
4524 for c3_cur in c3(c2_cur.document_id) loop
4525
4526
4527
4528 -- get the first calendar attached to the demand plan for the given output period type
4529
4530 l_calendar_code := get_calendar_code(p_demand_plan_id,p_old_output_period_type_id);
4531
4532
4533
4534 -- first value is the start bucket. get the start date for the old bucket and then get the new start bucket
4535
4536 if c3_cur.selection_sequence = 1 then
4537
4538 l_start_date := msd_common_utilities.get_bucket_start_date(sysdate,c3_cur.selection_value,p_old_output_period_type_id,l_calendar_code);
4539
4540 l_new_selection_value := msd_common_utilities.get_age_in_buckets(l_start_date,sysdate,p_output_period_type_id,l_calendar_code);
4541
4542 -- second value is the end bucket. get the end date for the old bucket and then get the new end bucket
4543
4544 elsif c3_cur.selection_sequence = 2 then
4545
4546 l_end_date := msd_common_utilities.get_bucket_end_date(sysdate,c3_cur.selection_value,p_old_output_period_type_id,l_calendar_code);
4547
4548 l_new_selection_value := msd_common_utilities.get_age_in_buckets(sysdate,l_end_date,p_output_period_type_id,l_calendar_code);
4549
4550 end if;
4551
4552
4553
4554 -- update with new values
4555
4556 update msd_dp_doc_dim_selections
4557
4558 set selection_value = l_new_selection_value*sign(c3_cur.selection_value)
4559
4560 where demand_plan_id = p_demand_plan_id
4561
4565
4562 and document_id = c2_cur.document_id
4563
4564 and dimension_code = 'TIM'
4566 and selection_type = 'TL'
4567
4568 and selection_value = c3_cur.selection_value
4569
4570 and selection_sequence = c3_cur.selection_sequence;
4571
4572
4573
4574 end loop;
4575
4576
4577
4578 end loop;
4579
4580
4581
4582 -- update the selection value with new data
4583
4584 update msd_dp_doc_dim_selections
4585
4586 set selection_value = p_output_period_type_id
4587
4588 where demand_plan_id = p_demand_plan_id
4589
4590 and selection_type = 'TL'
4591
4592 and selection_value = p_old_output_period_type_id;
4593
4594
4595
4596 -- create_seeded_definitions(p_demand_plan_id,l_errcode);
4597
4598
4599
4600 end if;
4601
4602
4603
4604 EXCEPTION
4605
4606
4607
4608 WHEN OTHERS THEN
4609
4610 rollback to sp;
4611
4612
4613
4614 end change_output_period;
4615
4616
4617
4618 /*******************************************************
4619
4620 This Procedure changes the seeded defintions relevant for the hierarhcy.
4621
4622 Called from form whenever user changes a hierarchy
4623
4624 ********************************************************/
4625
4626
4627
4628 procedure change_hierarchy(
4629
4630 p_demand_plan_id in varchar2,
4631
4632 p_hierarchy_id in varchar2,
4633
4634 p_old_hierarchy_id in varchar2)
4635
4636
4637
4638 is
4639
4640
4641
4642 cursor c1 is
4643
4644 select enable_nonseed_flag
4645
4646 from msd_dp_hierarchies
4647
4648 where demand_plan_id = p_demand_plan_id
4649
4650 and hierarchy_id = p_old_hierarchy_id;
4651
4652
4653
4654 l_nonseed_flag varchar2(15);
4655
4656 l_errcode varchar2(240);
4657
4658
4659
4660 BEGIN
4661
4662 savepoint sp;
4663
4664
4665
4666 open c1;
4667
4668 fetch c1 into l_nonseed_flag;
4669
4670 close c1;
4671
4672
4673
4674 -- do only for seeded hierarchies
4675
4676 if l_nonseed_flag is null or l_nonseed_flag <> 'Y' then
4677
4678
4679
4680 update msd_dp_doc_dim_selections
4681
4682 set selection_value = p_hierarchy_id
4683
4684 where demand_plan_id = p_demand_plan_id
4685
4686 and selection_type = 'H'
4687
4688 and selection_value = p_old_hierarchy_id;
4689
4690
4691
4692 -- create_seeded_definitions(p_demand_plan_id,l_errcode);
4693
4694
4695
4696 end if;
4697
4698
4699
4700 EXCEPTION
4701
4702
4703
4704 WHEN OTHERS THEN
4705
4706 rollback to sp;
4707
4708
4709
4710 end change_hierarchy;
4711
4712
4713
4714 procedure change_output_level(
4715
4716 p_demand_plan_id in varchar2,
4717
4718 p_scenario_id in varchar2,
4719
4720 p_level_id in varchar2,
4721
4722 p_old_level_id in varchar2)
4723
4724
4725
4726 is
4727
4728
4729
4730 cursor c1 is
4731
4732 select enable_nonseed_flag
4733
4734 from msd_dp_scenario_output_levels
4735
4736 where demand_plan_id = p_demand_plan_id
4737
4738 and scenario_id = p_scenario_id
4739
4740 and level_id = p_old_level_id;
4741
4742
4743
4744 l_nonseed_flag varchar2(15);
4745
4746 l_errcode varchar2(240);
4747
4748
4749
4750 BEGIN
4751
4752
4753
4754 savepoint sp;
4755
4756
4757
4758
4759
4760 open c1;
4761
4762 fetch c1 into l_nonseed_flag;
4763
4764 close c1;
4765
4766
4767
4768 -- do only for seeded values
4769
4770 if l_nonseed_flag is null or l_nonseed_flag <> 'Y' then
4771
4772
4773
4774 update msd_dp_doc_dim_selections
4775
4776 set selection_value = p_level_id
4777
4778 where demand_plan_id = p_demand_plan_id
4779
4780 and selection_type = 'L'
4781
4782 and selection_value = p_old_level_id;
4783
4784
4785
4786 -- create_seeded_definitions(p_demand_plan_id,l_errcode);
4787
4788
4789
4790 end if;
4791
4792
4793
4794 EXCEPTION
4795
4796
4797
4798 WHEN OTHERS THEN
4799
4800 rollback to sp;
4801
4802
4803
4804 end change_output_level;
4805
4806
4807
4808 procedure change_scenario_stream(
4809
4810 p_demand_plan_id in varchar2,
4811
4812 p_scenario_id in varchar2,
4813
4814 p_stream_type in varchar2,
4815
4816 p_stream_name in varchar2,
4817
4818 p_old_stream_type in varchar2,
4819
4820 p_old_stream_name in varchar2)
4821
4822
4823
4824 is
4825
4826 cursor c1 is
4827
4828 select enable_nonseed_flag
4829
4830 from msd_dp_scenarios
4831
4832 where demand_plan_id = p_demand_plan_id
4833
4834 and scenario_id = p_scenario_id;
4835
4836
4837
4838 l_nonseed_flag varchar2(15);
4839
4840
4841
4842 BEGIN
4846
4843
4844 savepoint sp;
4845
4847
4848 open c1;
4849
4850 fetch c1 into l_nonseed_flag;
4851
4852 close c1;
4853
4854
4855
4856 -- do only for seeded scenarios
4857
4858 if (l_nonseed_flag is null or l_nonseed_flag <> 'Y' ) and
4859
4860 (nvl(p_old_stream_type,'123456789') <> nvl(p_stream_type,'123456789') or nvl(p_old_stream_name,'123456789') <> nvl(p_stream_name,'123456789')) then
4861
4862
4863
4864 -- if there is already a stream attached
4865
4866 if p_old_stream_type is not null then
4867
4868
4869
4870 -- if the stream is being removed
4871
4872 if p_stream_type is null then
4873
4874
4875
4876 -- disable the doc dim selections
4877
4878 update msd_dp_doc_dim_selections
4879
4880 set enabled_flag = 'N'
4881
4882 where demand_plan_id = p_demand_plan_id
4883
4884 and selection_component = 'SN'
4885
4886 and selection_value = p_old_stream_type;
4887
4888
4889
4890 -- disable the formula parameters
4891
4892 update msd_dp_formula_parameters
4893
4894 set enabled_flag = 'N'
4895
4896 where demand_plan_id = p_demand_plan_id
4897
4898 and parameter_component = 'SN'
4899
4900 and parameter_value = p_old_stream_type;
4901
4902
4903
4904 -- if the stream is being changed
4905
4906 else
4907
4908
4909
4910 -- change the doc dim selections
4911
4912 update msd_dp_doc_dim_selections
4913
4914 set selection_value = p_stream_type
4915
4916 where demand_plan_id = p_demand_plan_id
4917
4918 and selection_component = 'SN'
4919
4920 and selection_value = p_old_stream_type;
4921
4922
4923
4924 -- change the formula parameters
4925
4926 update msd_dp_formula_parameters
4927
4928 set parameter_value = p_stream_type
4929
4930 where demand_plan_id = p_demand_plan_id
4931
4932 and parameter_component = 'SN'
4933
4934 and parameter_value = p_old_stream_type;
4935
4936
4937
4938 end if;
4939
4940
4941
4942 -- if new stream is being attached
4943
4944 elsif p_stream_type is not null then
4945
4946
4947
4948 -- enable the doc dim selections
4949
4950 update msd_dp_doc_dim_selections
4951
4952 set enabled_flag = 'Y'
4953
4954 where demand_plan_id = p_demand_plan_id
4955
4956 and selection_component = 'SN'
4957
4958 and selection_value = p_old_stream_type;
4959
4960
4961
4962 -- enable the formula parameters
4963
4964 update msd_dp_formula_parameters
4965
4966 set enabled_flag = 'Y'
4967
4968 where demand_plan_id = p_demand_plan_id
4969
4970 and parameter_component = 'SN'
4971
4972 and parameter_value = p_old_stream_type;
4973
4974
4975
4976 end if;
4977
4978
4979
4980 end if;
4981
4982
4983
4984 EXCEPTION
4985
4986
4987
4988 WHEN OTHERS THEN
4989
4990 rollback to sp;
4991
4992
4993
4994 END change_scenario_stream;
4995
4996
4997
4998
4999
5000 /*******************************************************
5001
5002 This Procedure creates input parameters,formulas and adds measures to seeded documents.
5003
5004 Called from form whenever user attahces a supply plan to a scenario.
5005
5006 Calls add_ascp_input_parameter, add_ascp_formula, add_ascp_measure, create_seeded_definitions.
5007
5008 ********************************************************/
5009
5010
5011
5012 Procedure attach_supply_plan(p_new_dp_id in number,p_supply_plan_id in number,p_supply_plan_name in varchar2,
5013
5014 p_old_supply_plan_id in number default null, p_old_supply_plan_name in varchar2 default null) -- Bug 4729854
5015
5016 is
5017
5018
5019 cursor get_plan_type is
5020 select plan_type
5021 from msd_demand_plans
5022 where demand_plan_id=p_new_dp_id;
5023
5024 p_plan_type varchar2(10);
5025
5026 p_errcode varchar2(2000);
5027
5028 l_template_id number;
5029
5030
5031
5032 cursor c1 is
5033
5034 select template_id
5035
5036 from msd_demand_plans
5037
5038 where demand_plan_id=p_new_dp_id;
5039
5040
5041
5042 Begin
5043
5044
5045 open get_plan_type;
5046 fetch get_plan_type into p_plan_type;
5047 close get_plan_type;
5048
5049 if p_plan_type='EOL' then
5050 add_ascp_scenario_for_eol(p_new_dp_id, p_supply_plan_id, p_supply_plan_name);
5051 update msd_demand_plans
5052 set liab_plan_id=p_supply_plan_id
5053 where demand_plan_id=p_new_dp_id;
5054 end if;
5055
5056
5057 open c1;
5058
5059 fetch c1 into l_template_id;
5060
5061 close c1;
5062
5063
5064
5065 add_ascp_input_parameter(p_new_dp_id, p_supply_plan_id, p_supply_plan_name, p_old_supply_plan_id, p_old_supply_plan_name); -- Bug 4729854
5066
5067
5068
5069 if l_template_id is not null then -- Bug 4745052
5070
5071
5072
5073 add_ascp_formula(p_new_dp_id, p_supply_plan_id,p_supply_plan_name, p_old_supply_plan_id, p_old_supply_plan_name); -- Bug 4729854
5074
5075
5076
5080
5077 -- add measure although it may not be used immidiately. it may be used if this is the first ascp measure or any other measure is removed.
5078
5079 add_ascp_measure(p_new_dp_id, p_supply_plan_id,p_supply_plan_name, p_old_supply_plan_id, p_old_supply_plan_name); -- Bug 4729854
5081
5082
5083 -- create_seeded_definitions(p_new_dp_id,p_errcode);
5084
5085 end if;
5086
5087
5088
5089 --EXCEPTION
5090
5091
5092
5093 -- WHEN OTHERS THEN
5094
5095 -- null;
5096
5097
5098
5099 End attach_supply_plan;
5100
5101
5102
5103 /*******************************************************
5104
5105 This Procedure creates scenario related to supply plan.
5106
5107 Called from update_ascp_related_data
5108
5109 ********************************************************/
5110
5111
5112
5113 Procedure add_ascp_scenario(p_new_dp_id in number,p_supply_plan_id in number, p_supply_plan_name in varchar2) -- Bug 4729854
5114
5115 is
5116
5117
5118
5119 cursor c1 is
5120
5121 select count(*) from
5122
5123 msd_dp_scenarios
5124
5125 where demand_plan_id = p_new_dp_id
5126
5127 and supply_plan_flag = 'Y';
5128
5129
5130
5131 cursor c2 is
5132
5133 select scenario_name, description, demand_plan_id, scenario_id
5134
5135 from msd_dp_scenarios
5136
5137 where supply_plan_flag = 'Y'
5138
5139 and demand_plan_id =
5140
5141 (select demand_plan_id
5142
5143 from msd_demand_plans
5144
5145 where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
5146
5147 and template_flag = 'Y'
5148
5149 and default_template = 'Y'
5150
5151 );
5152
5153
5154
5155 l_scenario_id number;
5156
5157 l_count number;
5158
5159 l_description varchar2(240);
5160
5161 l_name varchar2(240);
5162
5163
5164
5165 BEGIN
5166
5167 open c1;
5168
5169 fetch c1 into l_count;
5170
5171 close c1;
5172
5173
5174
5175
5176
5177 for c2_cur in c2 loop
5178
5179
5180
5181
5182
5183
5184
5185
5186
5187 fnd_message.set_name('MSD',c2_cur.description);
5188
5189
5190
5191 l_description := fnd_message.get;
5192
5193
5194
5195
5196
5197 fnd_message.set_name('MSD',c2_cur.scenario_name);
5198
5199
5200
5201 -- if there exist scenarios with ascp plan attached then name the new scenario properly replacing 2 with appropriate number
5202
5203 if l_count > 0 then
5204
5205 l_name := replace(fnd_message.get,'2',l_count+2);
5206
5207 else
5208
5209 l_name := fnd_message.get;
5210
5211 end if;
5212
5213
5214
5215 select msd_dp_scenarios_s.nextval into l_scenario_id from dual;
5216
5217
5218
5219 insert into msd_dp_scenarios
5220
5221 (demand_plan_id
5222
5223 ,scenario_id
5224
5225 ,scenario_name
5226
5227 ,description
5228
5229 ,output_period_type
5230
5231 ,horizon_start_date
5232
5233 ,horizon_end_date
5234
5235 ,forecast_date_used
5236
5237 ,forecast_based_on
5238
5239 ,last_update_date
5240
5241 ,last_updated_by
5242
5243 ,creation_date
5244
5245 ,created_by
5246
5247 ,last_update_login
5248
5249 ,request_id
5250
5251 ,program_application_id
5252
5253 ,program_id
5254
5255 ,program_update_date
5256
5257 ,attribute_category
5258
5259 ,attribute1
5260
5261 ,attribute2
5262
5263 ,attribute3
5264
5265 ,attribute4
5266
5267 ,attribute5
5268
5269 ,attribute6
5270
5271 ,attribute7
5272
5273 ,attribute8
5274
5275 ,attribute9
5276
5277 ,attribute10
5278
5279 ,attribute11
5280
5281 ,attribute12
5282
5283 ,attribute13
5284
5285 ,attribute14
5286
5287 ,attribute15
5288
5289 ,scenario_type
5290
5291 ,status
5292
5293 ,history_start_date
5294
5295 ,history_end_date
5296
5297 ,publish_flag
5298
5299 ,enable_flag
5300
5301 ,price_list_name
5302
5303 ,last_revision
5304
5305 ,parameter_name
5306
5307 ,consume_flag
5308
5309 ,error_type
5310
5311 ,supply_plan_id
5312
5313 ,deleteable_flag
5314
5315 ,supply_plan_flag
5316
5317 ,supply_plan_name
5318
5319 ,dmd_priority_scenario_id -- Bug 4710963
5320
5321 ,scenario_designator
5322
5323 ,associate_parameter
5324
5325 ,sc_type)
5326
5327 (select
5328
5329 p_new_dp_id
5330
5331 ,l_scenario_id
5332
5333 ,l_name
5334
5335 ,l_description
5336
5337 ,output_period_type
5338
5339 ,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_start_date(sysdate,2,6,'GREGORIAN'), null)
5340
5341 ,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_end_date(sysdate,19,6,'GREGORIAN'), null)
5342
5346
5343 ,forecast_date_used
5344
5345 ,forecast_based_on
5347 ,SYSDATE
5348
5349 ,fnd_global.user_id
5350
5351 ,SYSDATE
5352
5353 ,fnd_global.user_id
5354
5355 ,fnd_global.login_id
5356
5357 ,NULL
5358
5359 ,NULL
5360
5361 ,NULL
5362
5363 ,SYSDATE
5364
5365 ,attribute_category
5366
5367 ,attribute1
5368
5369 ,attribute2
5370
5371 ,attribute3
5372
5373 ,attribute4
5374
5375 ,attribute5
5376
5377 ,attribute6
5378
5379 ,attribute7
5380
5381 ,attribute8
5382
5383 ,attribute9
5384
5385 ,attribute10
5386
5387 ,attribute11
5388
5389 ,attribute12
5390
5391 ,attribute13
5392
5393 ,attribute14
5394
5395 ,attribute15
5396
5397 ,scenario_type
5398
5399 ,status
5400
5401 ,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_start_date(sysdate,-18,6,'GREGORIAN'), null)
5402
5403 ,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN'), null)
5404
5405 ,publish_flag
5406
5407 ,enable_flag
5408
5409 ,price_list_name
5410
5411 ,last_revision
5412
5413 ,parameter_name
5414
5415 ,consume_flag
5416
5417 ,error_type
5418
5419 ,p_supply_plan_id
5420
5421 ,deleteable_flag
5422
5423 ,supply_plan_flag
5424
5425 ,p_supply_plan_name -- Bug 4729854
5426
5427 ,dmd_priority_scenario_id -- Bug 4710963
5428
5429 ,scenario_designator
5430
5431 ,associate_parameter
5432
5433 ,sc_type
5434
5435 from msd_dp_scenarios,
5436
5437 (select nvl(plan_type,'DP') plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id) p_type
5438
5439 where scenario_id = c2_cur.scenario_id
5440
5441 and demand_plan_id = c2_cur.demand_plan_id);
5442
5443
5444
5445
5446
5447 insert into msd_dp_scenarios_tl
5448
5449 ( demand_plan_id
5450
5451 ,scenario_id
5452
5453 ,description
5454
5455 ,language
5456
5457 ,source_lang
5458
5459 ,creation_date
5460
5461 ,created_by
5462
5463 ,last_update_date
5464
5465 ,last_updated_by
5466
5467 ,last_update_login
5468
5469 ,request_id
5470
5471 ,program_application_id
5472
5473 ,program_id
5474
5475 ,program_update_date
5476
5477 )
5478
5479 select
5480
5481 p_new_dp_id
5482
5483 ,l_scenario_id
5484
5485 ,l_description
5486
5487 ,USERENV('LANG')
5488
5489 ,USERENV('LANG')
5490
5491 ,SYSDATE
5492
5493 ,fnd_global.user_id
5494
5495 ,SYSDATE
5496
5497 ,fnd_global.user_id
5498
5499 ,fnd_global.login_id
5500
5501 ,NULL
5502
5503 ,NULL
5504
5505 ,NULL
5506
5507 ,SYSDATE
5508
5509 from dual;
5510
5511
5512
5513
5514
5515 insert into msd_dp_scenario_events
5516
5517 (
5518
5519 demand_plan_id
5520
5521 ,scenario_id
5522
5523 ,event_id
5524
5525 ,last_update_date
5526
5527 ,last_updated_by
5528
5529 ,creation_date
5530
5531 ,created_by
5532
5533 ,last_update_login
5534
5535 ,request_id
5536
5537 ,program_application_id
5538
5539 ,program_id
5540
5541 ,program_update_date
5542
5543 ,event_association_priority)
5544
5545 (select
5546
5547 p_new_dp_id
5548
5549 ,l_scenario_id
5550
5551 ,event_id
5552
5553 ,last_update_date
5554
5555 ,last_updated_by
5556
5557 ,creation_date
5558
5559 ,created_by
5560
5561 ,last_update_login
5562
5563 ,request_id
5564
5565 ,program_application_id
5566
5567 ,program_id
5568
5569 ,program_update_date
5570
5571 ,event_association_priority
5572
5573 from msd_dp_scenario_events
5574
5575 where scenario_id = c2_cur.scenario_id
5576
5577 and demand_plan_id = c2_cur.demand_plan_id);
5578
5579
5580
5581
5582
5583 insert into msd_dp_scenario_output_levels
5584
5585 (
5586
5587 demand_plan_id
5588
5589 ,scenario_id
5590
5591 ,level_id
5592
5593 ,last_update_date
5594
5595 ,last_updated_by
5596
5597 ,creation_date
5598
5599 ,created_by
5600
5601 ,last_update_login
5602
5603 ,request_id
5604
5605 ,program_application_id
5606
5607 ,program_id
5608
5609 ,program_update_date)
5610
5611 (select
5612
5613 p_new_dp_id
5614
5615 ,l_scenario_id
5616
5617 ,level_id
5618
5619 ,SYSDATE
5620
5621 ,fnd_global.user_id
5622
5623 ,SYSDATE
5624
5625 ,fnd_global.user_id
5629 ,NULL
5626
5627 ,fnd_global.login_id
5628
5630
5631 ,NULL
5632
5633 ,NULL
5634
5635 ,SYSDATE
5636
5637 from msd_dp_scenario_output_levels
5638
5639 where scenario_id = c2_cur.scenario_id
5640
5641 and demand_plan_id = c2_cur.demand_plan_id);
5642
5643
5644
5645
5646
5647 l_count := l_count +1;
5648
5649
5650
5651 end loop;
5652
5653
5654
5655 END add_ascp_scenario;
5656
5657
5658
5659 /*******************************************************
5660
5661 This Procedure creates input parameters related to supply plan.
5662
5663 Called from update_ascp_related_data, attach_supply_plan.
5664
5665 ********************************************************/
5666
5667
5668
5669 Procedure add_ascp_input_parameter(p_new_dp_id in number,p_supply_plan_id in number,p_supply_plan_name in varchar2,
5670
5671 p_old_supply_plan_id in number default null, p_old_supply_plan_name in varchar2 default null) -- Bug 4729854
5672
5673 is
5674
5675 BEGIN
5676
5677
5678
5679
5680
5681 if p_old_supply_plan_id is not null then
5682
5683
5684
5685 -- change the parameters if change in ascp plan attahced
5686
5687 if p_supply_plan_id is not null then
5688
5689
5690
5691 update msd_dp_parameters
5692
5693 set parameter_name = p_supply_plan_name, -- Bug 4729854
5694
5695 --Bug 4549059
5696
5697 capacity_usage_ratio = decode(parameter_type,'MSD_SUPPLY_PLANS',p_supply_plan_name,
5698 'MSD_SIM_END_ITEM_DEMAND',p_supply_plan_name,
5699 capacity_usage_ratio) -- Bug 4729854
5700
5701 where demand_plan_id = p_new_dp_id
5702
5703 and parameter_name = p_old_supply_plan_name; -- Bug 4729854
5704
5705
5706
5707 -- delete if ascp plan is detached
5708
5709 else
5710
5711 delete from msd_dp_parameters
5712
5713 where demand_plan_id = p_new_dp_id
5714
5715 and parameter_name = p_old_supply_plan_name; -- Bug 4729854
5716
5717
5718
5719 end if;
5720
5721
5722
5723 elsif p_supply_plan_id is not null then
5724
5725
5726
5727 insert into msd_dp_parameters
5728
5729 (
5730
5731 demand_plan_id
5732
5733 ,parameter_id
5734
5735 ,parameter_type
5736
5737 ,parameter_name
5738
5739 ,start_date
5740
5741 ,end_date
5742
5743 ,output_scenario_id
5744
5745 ,input_scenario_id
5746
5747 ,input_demand_plan_id
5748
5749 ,forecast_date_used
5750
5751 ,forecast_based_on
5752
5753 ,quantity_used
5754
5755 ,amount_used
5756
5757 ,forecast_used
5758
5759 ,period_type
5760
5761 ,fact_type
5762
5763 ,view_name
5764
5765 ,last_update_date
5766
5767 ,last_updated_by
5768
5769 ,creation_date
5770
5771 ,created_by
5772
5773 ,last_update_login
5774
5775 ,request_id
5776
5777 ,program_application_id
5778
5779 ,program_id
5780
5781 ,program_update_date
5782
5783 ,revision
5784
5785 ,allo_agg_basis_stream_id
5786
5787 ,number_of_period
5788
5789 ,exclude_from_rolling_cycle
5790
5791 ,scn_build_refresh_num
5792
5793 ,rounding_flag
5794
5795 ,deleteable_flag
5796
5797 ,capacity_usage_ratio
5798
5799 ,supply_plan_flag
5800 , equation
5801 ,stream_type
5802 ,calculated_order
5803 ,post_calculation
5804 ,price_list_name)
5805
5806 (select
5807
5808 p_new_dp_id
5809
5810 ,msd_dp_parameters_s.nextval
5811
5812 ,parameter_type
5813
5814 ,p_supply_plan_name -- Bug 4729854
5815
5816 ,decode(p_type.plan_type, 'SOP', get_supply_plan_start_date(p_supply_plan_id), null)
5817
5818 ,decode(p_type.plan_type, 'SOP', get_supply_plan_end_date(p_supply_plan_id), null)
5819
5820 ,output_scenario_id
5821
5822 ,input_scenario_id
5823
5824 ,input_demand_plan_id
5825
5826 ,forecast_date_used
5827
5828 ,forecast_based_on
5829
5830 ,quantity_used
5831
5832 ,amount_used
5833
5834 ,forecast_used
5835
5836 ,period_type
5837
5838 ,fact_type
5839
5840 ,view_name
5841
5842 ,SYSDATE
5843
5844 ,fnd_global.user_id
5845
5846 ,SYSDATE
5847
5848 ,fnd_global.user_id
5849
5850 ,fnd_global.login_id
5851
5852 ,NULL
5853
5854 ,NULL
5855
5856 ,NULL
5857
5858 ,SYSDATE
5859
5860 ,revision
5861
5862 ,allo_agg_basis_stream_id
5863
5864 ,number_of_period
5865
5866 ,exclude_from_rolling_cycle
5867
5868 ,scn_build_refresh_num
5869
5870 ,rounding_flag
5871
5872 ,deleteable_flag
5873
5874 ,decode(p_type.plan_type, 'SOP', decode(parameter_type,'MSD_SUPPLY_PLANS',p_supply_plan_name,capacity_usage_ratio)
5875 , 'EOL', decode(parameter_type,'MSD_SIM_END_ITEM_DEMAND',p_supply_plan_name,capacity_usage_ratio)) -- Bug 4729854
5876
5880 ,calculated_order
5877 ,supply_plan_flag
5878 ,equation
5879 ,stream_type
5881 ,post_calculation
5882 ,price_list_name
5883
5884 from msd_dp_parameters,
5885
5886 (select nvl(plan_type,'DP') plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id) p_type
5887
5888 where supply_plan_flag = 'Y'
5889
5890 and nvl(stream_type,'ABCD') not in ('ARCHIVED','ARCHIVED_TIM')
5891
5892 and demand_plan_id =
5893
5894 (select demand_plan_id
5895
5896 from msd_demand_plans
5897
5898 where plan_type = p_type.plan_type
5899
5900 and template_flag = 'Y'
5901
5902 and default_template = 'Y'
5903
5904 ));
5905
5906
5907
5908 end if;
5909
5910
5911
5912 END add_ascp_input_parameter;
5913
5914
5915
5916 /*******************************************************
5917
5918 This Procedure creates formulas related to supply plan.
5919
5920 Called from update_ascp_related_data, attach_supply_plan.
5921
5922 ********************************************************/
5923
5924
5925
5926 Procedure add_ascp_formula(p_new_dp_id in number,p_supply_plan_id in number,p_supply_plan_name in varchar2,
5927
5928 p_old_supply_plan_id in number default null,p_old_supply_plan_name in varchar2 default null) -- Bug 4729854
5929
5930 is
5931
5932
5933
5934 l_formula_id number;
5935
5936
5937
5938 cursor c1 is
5939
5940 select
5941
5942 formula_id
5943
5944 ,creation_sequence
5945
5946 ,formula_name
5947
5948 ,formula_desc
5949
5950 ,custom_type
5951
5952 ,equation
5953
5954 ,custom_field1
5955
5956 ,custom_field2
5957
5958 ,custom_subtype
5959
5960 ,custom_addtlcalc
5961
5962 ,isby
5963
5964 ,valid_flag
5965
5966 ,numerator
5967
5968 ,denominator
5969
5970 ,supply_plan_flag
5971
5972 ,p_supply_plan_name -- Bug 4729854
5973
5974 ,FORMAT /* ADDED NEW COLUMN FOR THE BUG#4373422 */
5975
5976 ,START_PERIOD /* ADDED NEW COLUMN FOR THE BUG#4744717 */
5977
5978 from msd_dp_formulas
5979
5980 where demand_plan_id =
5981
5982 (select demand_plan_id
5983
5984 from msd_demand_plans
5985
5986 where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
5987
5988 and template_flag = 'Y'
5989
5990 and default_template = 'Y'
5991
5992 )
5993
5994 and supply_plan_flag = 'Y';
5995
5996
5997 cursor plan_type is
5998 select plan_type
5999 from msd_demand_plans
6000 where demand_plan_id=p_new_dp_id;
6001
6002 p_plan_type varchar2(10);
6003
6004 BEGIN
6005
6006
6007 open plan_type;
6008 fetch plan_type into p_plan_type;
6009 close plan_type;
6010
6011 if p_old_supply_plan_id is not null then
6012
6013
6014
6015 -- change the formula if different ascp plan attached
6016
6017 if p_supply_plan_id is not null then
6018
6019
6020
6021 update msd_dp_formulas
6022
6023 set supply_plan_name = p_supply_plan_name -- Bug 4729854
6024
6025 where demand_plan_id = p_new_dp_id
6026
6027 and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
6028
6029
6030
6031 update msd_dp_formula_parameters
6032
6033 set supply_plan_name = p_supply_plan_name -- Bug 4729854
6034
6035 where demand_plan_id = p_new_dp_id
6036
6037 and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
6038
6039
6040
6041 -- delete the formula if ascp plan is detached
6042
6043 else
6044
6045
6046
6047 delete from msd_dp_formulas
6048
6049 where demand_plan_id = p_new_dp_id
6050
6051 and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
6052
6053 /*-----Added for the bug 4605807----*/
6054
6055 delete from msd_dp_formula_parameters
6056
6057 where demand_plan_id = p_new_dp_id
6058
6059 and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
6060
6061
6062
6063 end if;
6064
6065
6066
6067 elsif p_supply_plan_id is not null then
6068
6069
6070
6071 for c1_cur in c1 loop
6072
6073
6074
6075 select msd_dp_parameters_s.nextval into l_formula_id from dual;
6076
6077
6078
6079 insert into msd_dp_formulas
6080
6081 (
6082
6083 demand_plan_id
6084
6085 ,formula_id
6086
6087 ,creation_sequence
6088
6089 ,formula_name
6090
6091 ,formula_desc
6092
6093 ,custom_type
6094
6095 ,equation
6096
6097 ,custom_field1
6098
6099 ,custom_field2
6100
6101 ,custom_subtype
6102
6103 ,custom_addtlcalc
6104
6105 ,isby
6106
6107 ,valid_flag
6108
6109 ,numerator
6110
6111 ,denominator
6112
6113 ,supply_plan_flag
6114
6115 ,supply_plan_name
6116
6117 ,last_update_date
6118
6119 ,FORMAT /*----NEW COLUMN ADDED FOR THE BUG# 4373422-----*/
6120
6121 ,START_PERIOD /* ADDED NEW COLUMN FOR THE BUG#4744717 */
6122
6123 ,last_updated_by
6127 ,created_by
6124
6125 ,creation_date
6126
6128
6129 ,last_update_login
6130
6131 ,request_id
6132
6133 ,program_application_id
6134
6135 ,program_id
6136
6137 ,program_update_date
6138
6139 )
6140
6141 values
6142
6143 (
6144
6145 p_new_dp_id
6146
6147 ,l_formula_id
6148
6149 ,c1_cur.creation_sequence
6150
6151 ,c1_cur.formula_name
6152
6153 ,c1_cur.formula_desc
6154
6155 ,c1_cur.custom_type
6156
6157 ,c1_cur.equation
6158
6159 ,c1_cur.custom_field1
6160
6161 ,c1_cur.custom_field2
6162
6163 ,c1_cur.custom_subtype
6164
6165 ,c1_cur.custom_addtlcalc
6166
6167 ,c1_cur.isby
6168
6169 ,c1_cur.valid_flag
6170
6171 ,c1_cur.numerator
6172
6173 ,c1_cur.denominator
6174
6175 ,c1_cur.supply_plan_flag
6176
6177 ,p_supply_plan_name -- Bug 4729854
6178
6179 ,SYSDATE
6180
6181 ,c1_cur.FORMAT /*----NEW COLUMN ADDED FOR THE BUG# 4373422-----*/
6182
6183 ,c1_cur.START_PERIOD /* ADDED NEW COLUMN FOR THE BUG#4744717 */
6184
6185 ,fnd_global.user_id
6186
6187 ,SYSDATE
6188
6189 ,fnd_global.user_id
6190
6191 ,fnd_global.login_id
6192
6193 ,NULL
6194
6195 ,NULL
6196
6197 ,NULL
6198
6199 ,SYSDATE);
6200
6201
6202
6203 insert into msd_dp_formula_parameters
6204
6205 (
6206
6207 demand_plan_id
6208
6209 ,formula_id
6210
6211 ,where_used
6212
6213 ,parameter_sequence
6214
6215 ,enabled_flag
6216
6217 ,mandatory_flag
6218
6219 ,parameter_type
6220
6221 ,parameter_component
6222
6223 ,parameter_value
6224
6225 ,supply_plan_flag
6226
6227 ,supply_plan_name
6228
6229 ,last_update_date
6230
6231 ,last_updated_by
6232
6233 ,creation_date
6234
6235 ,created_by
6236
6237 ,last_update_login
6238
6239 ,request_id
6240
6241 ,program_application_id
6242
6243 ,program_id
6244
6245 ,program_update_date
6246
6247 )
6248
6249 (select
6250
6251 p_new_dp_id
6252
6253 ,l_formula_id
6254
6255 ,where_used
6256
6257 ,parameter_sequence
6258
6259 ,enabled_flag
6260
6261 ,mandatory_flag
6262
6263 ,parameter_type
6264
6265 ,parameter_component
6266
6267 ,parameter_value
6268
6269 ,supply_plan_flag
6270
6271 ,decode(supply_plan_flag,'Y',p_supply_plan_name,null) -- Bug 4729854
6272
6273 ,SYSDATE
6274
6275 ,fnd_global.user_id
6276
6277 ,SYSDATE
6278
6279 ,fnd_global.user_id
6280
6281 ,fnd_global.login_id
6282
6283 ,NULL
6284
6285 ,NULL
6286
6287 ,NULL
6288
6289 ,SYSDATE
6290
6291 from msd_dp_formula_parameters
6292
6293 where demand_plan_id =
6294
6295 (select demand_plan_id
6296
6297 from msd_demand_plans
6298
6299 where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
6300
6301 and template_flag = 'Y'
6302
6303 and default_template = 'Y'
6304
6305 )
6306
6307 and formula_id = c1_cur.formula_id);
6308
6309
6310
6311 end loop;
6312
6313
6314 if p_plan_type='EOL' then
6315
6316 insert into msd_dp_formula_parameters
6317
6318 (
6319
6320 demand_plan_id
6321
6322 ,formula_id
6323
6324 ,where_used
6325
6326 ,parameter_sequence
6327
6328 ,enabled_flag
6329
6330 ,mandatory_flag
6331
6332 ,parameter_type
6333
6334 ,parameter_component
6335
6336 ,parameter_value
6337
6338 ,supply_plan_flag
6339
6340 ,supply_plan_name
6341
6342 ,last_update_date
6343
6344 ,last_updated_by
6345
6346 ,creation_date
6347
6348 ,created_by
6349
6350 ,last_update_login
6351
6352 ,request_id
6353
6354 ,program_application_id
6355
6356 ,program_id
6357
6358 ,program_update_date
6359
6360 )
6361
6362 (select
6363
6364 p_new_dp_id
6365
6366 ,mdp1.parameter_id
6367
6368 ,mdfp.where_used
6369
6370 ,mdfp.parameter_sequence
6371
6372 ,mdfp.enabled_flag
6373
6374 ,mdfp.mandatory_flag
6375
6376 ,mdfp.parameter_type
6377
6378 ,mdfp.parameter_component
6379
6380 ,mdfp.parameter_value
6381
6382 ,mdfp.supply_plan_flag
6383
6384 ,decode(mdfp.supply_plan_flag,'Y',p_supply_plan_name,null) -- Bug 4729854
6385
6386 ,SYSDATE
6387
6388 ,fnd_global.user_id
6389
6390 ,SYSDATE
6391
6392 ,fnd_global.user_id
6393
6394 ,fnd_global.login_id
6395
6396 ,NULL
6397
6398 ,NULL
6399
6400 ,NULL
6401
6402 ,SYSDATE
6403
6404 from msd_dp_formula_parameters mdfp,
6405 msd_dp_parameters mdp,
6406 msd_dp_parameters mdp1
6407
6408 where mdfp.demand_plan_id =
6409
6410 (select demand_plan_id
6411
6415
6412 from msd_demand_plans
6413
6414 where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
6416 and template_flag = 'Y'
6417
6418 and default_template = 'Y')
6419 and mdp.demand_plan_id=mdfp.demand_plan_id
6420 and mdp.parameter_id=mdfp.formula_id
6421 and mdp1.demand_plan_id=p_new_dp_id
6422 and mdp1.parameter_type=mdp.parameter_type);
6423
6424
6425 end if;
6426
6427
6428 /*
6429
6430 * Bug# 4563958
6431
6432 * This procedure 'update_formula_names' should always be called whenever a
6433
6434 * supply plan is attached to a scenario.
6435
6436 * Note: This procedure 'update_formula_names' will not be called whenever a
6437
6438 * supply plan attached to a scenario is changed/deleted.
6439
6440 */
6441
6442 -- if not g_call then
6443
6444 update_formula_names(p_new_dp_id);
6445
6446 -- end if;
6447
6448
6449
6450 end if;
6451
6452 END add_ascp_formula;
6453
6454
6455
6456 /*******************************************************
6457
6458 This Procedure adds measures related to supply plan to seeded documents .
6459
6460 Called from update_ascp_related_data, attach_supply_plan.
6461
6462 ********************************************************/
6463
6464
6465
6466 Procedure add_ascp_measure(p_new_dp_id in number,p_supply_plan_id in number,p_supply_plan_name in varchar2,
6467
6468 p_old_supply_plan_id in number default null,p_old_supply_plan_name in varchar2 default null) -- Bug 4729854
6469
6470 is
6471
6472
6473
6474 cursor c1 is
6475
6476 select msd.document_id, msd.document_name
6477
6478 from msd_dp_seeded_documents msd
6479
6480 where msd.demand_plan_id = p_new_dp_id;
6481
6482
6483
6484 cursor c2(p_document_name in varchar2) is
6485
6486 select document_id
6487
6488 from msd_dp_seeded_documents
6489
6490 where document_name = p_document_name
6491
6492 and demand_plan_id =
6493
6494 (select demand_plan_id
6495
6496 from msd_demand_plans
6497
6498 where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
6499
6500 and template_flag = 'Y'
6501
6502 and default_template = 'Y');
6503
6504
6505
6506 cursor c3 is
6507
6508 select count(*)
6509
6510 from msd_dp_doc_dim_selections
6511
6512 where demand_plan_id = p_new_dp_id
6513
6514 and supply_plan_flag = 'Y';
6515
6516
6517
6518 cursor c4 is
6519
6520 select distinct document_id, dimension_code
6521
6522 from msd_dp_seeded_doc_dimensions
6523
6524 where demand_plan_id = p_new_dp_id
6525
6526 order by document_id, dimension_code;
6527
6528
6529
6530 cursor c5(p_document_id in number, p_dimension_code in varchar2) is
6531
6532 select selection_sequence
6533
6534 from msd_dp_doc_dim_selections
6535
6536 where demand_plan_id = p_new_dp_id
6537
6538 and supply_plan_name = p_old_supply_plan_name -- Bug 4729854
6539
6540 and document_id = p_document_id
6541
6542 and dimension_code = p_dimension_code
6543
6544 order by selection_sequence;
6545
6546
6547
6548 cursor c6(p_document_id in number, p_dimension_code in varchar2, p_selection_sequence in number) is
6549
6550 select supply_plan_name, min(selection_sequence)
6551
6552 from msd_dp_doc_dim_selections
6553
6554 where demand_plan_id = p_new_dp_id
6555
6556 and supply_plan_name is not null
6557
6558 and supply_plan_name <> p_old_supply_plan_name -- Bug 4729854
6559
6560 and document_id = p_document_id
6561
6562 and dimension_code = p_dimension_code
6563
6564 and selection_sequence > p_selection_sequence
6565
6566 group by supply_plan_name
6567
6568 order by min(selection_sequence);
6569
6570
6571
6572
6573
6574 l_max_selection_sequence number;
6575
6576 l_document_id number;
6577
6578 l_count number;
6579
6580 l_supply_plan_name varchar2(80);
6581
6582 l_selection_sequence number;
6583
6584 l_seq_diff number := 0;
6585
6586 l_selection_count number;
6587
6588 BEGIN
6589
6590
6591
6592 if p_old_supply_plan_id is not null then
6593
6594
6595
6596 -- change the measure if different ascp plan attached
6597
6598 if p_supply_plan_id is not null then
6599
6600
6601
6602 update msd_dp_doc_dim_selections
6603
6604 set supply_plan_name = p_supply_plan_name -- Bug 4729854
6605
6606 where demand_plan_id = p_new_dp_id
6607
6608 and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
6609
6610
6611
6612 -- delete the measure if ascp plan is detached and update any other ascp specific measure to be first measure if this was the first measure
6613
6614 else
6615
6616
6617
6618
6619
6620 -- for each document, dimension in plan
6621
6622 for c4_cur in c4 loop
6623
6624
6625
6626 l_selection_count := 0;
6627
6628
6629
6630 -- for each selection sequence for the detached plan
6631
6632 for c5_cur in c5(c4_cur.document_id, c4_cur.dimension_code) loop
6633
6634
6635
6639
6636 -- get the next supply plan name if any and difference in selection sequences for the document and dimension.
6637
6638 -- do this only once for a document and dimension
6640 if l_selection_count = 0 then
6641
6642 open c6(c4_cur.document_id, c4_cur.dimension_code,c5_cur.selection_sequence);
6643
6644 fetch c6 into l_supply_plan_name, l_selection_sequence;
6645
6646 close c6;
6647
6648
6649
6650 l_seq_diff := l_selection_sequence - c5_cur.selection_sequence;
6651
6652 end if;
6653
6654
6655
6656 -- delete the selection first
6657
6658 delete from msd_dp_doc_dim_selections
6659
6660 where demand_plan_id = p_new_dp_id
6661
6662 and supply_plan_name = p_old_supply_plan_name -- Bug 4729854
6663
6664 and selection_sequence = c5_cur.selection_sequence
6665
6666 and document_id = c4_cur.document_id
6667
6668 and dimension_code = c4_cur.dimension_code;
6669
6670
6671
6672 -- set the selection sequence of the next ascp measure to the selection sequence of the measure being deleted and increase by 1000 to avoid uinque constraint violation
6673
6674 update msd_dp_doc_dim_selections
6675
6676 set selection_sequence = c5_cur.selection_sequence
6677
6678 where demand_plan_id = p_new_dp_id
6679
6680 and supply_plan_name = l_supply_plan_name
6681
6682 and selection_sequence = l_seq_diff + c5_cur.selection_sequence
6683
6684 and document_id = c4_cur.document_id
6685
6686 and dimension_code = c4_cur.dimension_code;
6687
6688
6689
6690 l_selection_count := l_selection_count +1;
6691
6692
6693
6694 end loop;
6695
6696
6697
6698 end loop;
6699
6700
6701
6702 end if;
6703
6704
6705
6706 elsif p_supply_plan_id is not null then
6707
6708
6709
6710 open c3;
6711
6712 fetch c3 into l_count;
6713
6714 close c3;
6715
6716
6717
6718 -- increase selection sequence if not first first plan attached
6719
6720 if l_count > 0 then
6721
6722
6723
6724 -- for each document for the plan
6725
6726 for c1_cur in c1 loop
6727
6728
6729
6730 -- get the document id of the same documnet in template
6731
6732 open c2(c1_cur.document_name);
6733
6734 fetch c2 into l_document_id;
6735
6736 close c2;
6737
6738
6739
6740 select max(selection_sequence) into l_max_selection_sequence from msd_dp_doc_dim_selections
6741
6742 where demand_plan_id = p_new_dp_id;
6743
6744
6745
6746 -- create a new measure but it will not be used as it is not first plan attached
6747
6748 insert into msd_dp_doc_dim_selections
6749
6750 (
6751
6752 demand_plan_id
6753
6754 ,document_id
6755
6756 ,dimension_code
6757
6758 ,enabled_flag
6759
6760 ,mandatory_flag
6761
6762 ,selection_sequence
6763
6764 ,selection_type
6765
6766 ,selection_component
6767
6768 ,selection_value
6769
6770 ,supply_plan_flag
6771
6772 ,supply_plan_name
6773
6774 ,last_update_date
6775
6776 ,last_updated_by
6777
6778 ,creation_date
6779
6780 ,created_by
6781
6782 ,last_update_login
6783
6784 ,request_id
6785
6786 ,program_application_id
6787
6788 ,program_id
6789
6790 ,program_update_date
6791
6792 )
6793
6794 (select
6795
6796 p_new_dp_id
6797
6798 ,c1_cur.document_id
6799
6800 ,dimension_code
6801
6802 ,enabled_flag
6803
6804 ,mandatory_flag
6805
6806 ,l_max_selection_sequence + selection_sequence
6807
6808 ,selection_type
6809
6810 ,selection_component
6811
6812 ,selection_value
6813
6814 ,supply_plan_flag
6815
6816 ,p_supply_plan_name -- Bug 4729854
6817
6818 ,SYSDATE
6819
6820 ,fnd_global.user_id
6821
6822 ,SYSDATE
6823
6824 ,fnd_global.user_id
6825
6826 ,fnd_global.login_id
6827
6828 ,NULL
6829
6830 ,NULL
6831
6832 ,NULL
6833
6834 ,SYSDATE
6835
6836 from msd_dp_doc_dim_selections
6837
6838 where supply_plan_flag = 'Y'
6839
6840 and demand_plan_id =
6841
6842 (select demand_plan_id
6843
6844 from msd_demand_plans
6845
6846 where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
6847
6848 and template_flag = 'Y'
6849
6850 and default_template = 'Y'
6851
6852 )
6853
6854 and document_id = l_document_id);
6855
6856
6857
6858 end loop;
6859
6860
6861
6862 -- use seeded selection sequence if first plan attached
6863
6864 else /* l_count >0 */
6865
6866
6867
6868 -- for each document for the plan
6869
6870 for c1_cur in c1 loop
6871
6872
6873
6874
6875
6876 -- get the document id of the same documnet in template
6877
6878 open c2(c1_cur.document_name);
6879
6880 fetch c2 into l_document_id;
6881
6882 close c2;
6883
6884
6885
6886 -- create a new measure and it will be used as it is the first plan attached
6887
6891
6888 insert into msd_dp_doc_dim_selections
6889
6890 (
6892 demand_plan_id
6893
6894 ,document_id
6895
6896 ,dimension_code
6897
6898 ,enabled_flag
6899
6900 ,mandatory_flag
6901
6902 ,selection_sequence
6903
6904 ,selection_type
6905
6906 ,selection_component
6907
6908 ,selection_value
6909
6910 ,supply_plan_flag
6911
6912 ,supply_plan_name
6913
6914 ,last_update_date
6915
6916 ,last_updated_by
6917
6918 ,creation_date
6919
6920 ,created_by
6921
6922 ,last_update_login
6923
6924 ,request_id
6925
6926 ,program_application_id
6927
6928 ,program_id
6929
6930 ,program_update_date
6931
6932 )
6933
6934 (select
6935
6936 p_new_dp_id
6937
6938 ,c1_cur.document_id
6939
6940 ,dimension_code
6941
6942 ,enabled_flag
6943
6944 ,mandatory_flag
6945
6946 ,selection_sequence
6947
6948 ,selection_type
6949
6950 ,selection_component
6951
6952 ,selection_value
6953
6954 ,supply_plan_flag
6955
6956 ,p_supply_plan_name -- Bug 4729854
6957
6958 ,SYSDATE
6959
6960 ,fnd_global.user_id
6961
6962 ,SYSDATE
6963
6964 ,fnd_global.user_id
6965
6966 ,fnd_global.login_id
6967
6968 ,NULL
6969
6970 ,NULL
6971
6972 ,NULL
6973
6974 ,SYSDATE
6975
6976 from msd_dp_doc_dim_selections
6977
6978 where supply_plan_flag = 'Y'
6979
6980 and demand_plan_id =
6981
6982 (select demand_plan_id
6983
6984 from msd_demand_plans
6985
6986 where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
6987
6988 and template_flag = 'Y'
6989
6990 and default_template = 'Y'
6991
6992 )
6993
6994 and document_id = l_document_id);
6995
6996
6997
6998 end loop;
6999
7000
7001
7002 end if;
7003
7004
7005
7006 end if;
7007
7008
7009
7010 END add_ascp_measure;
7011
7012
7013
7014
7015
7016 Procedure set_prd_lvl_for_liab_reports(p_demand_plan_id in number, p_errcode in out nocopy varchar2)
7017
7018 is
7019
7020
7021
7022 l_level varchar2(240);
7023
7024
7025
7026 begin
7027
7028
7029
7030 select fnd_profile.value('MSC_LIABILITY_CALC_LEVEL') into l_level from dual;
7031
7032
7033
7034 update msd_dp_doc_dim_selections
7035
7036 set selection_value = nvl(fnd_profile.value('MSC_LIABILITY_CALC_LEVEL'),1)
7037
7038 where demand_plan_id = p_demand_plan_id
7039
7040 and document_id in
7041
7042 (select document_id
7043
7044 from msd_dp_seeded_documents
7045
7046 where demand_plan_id = p_demand_plan_id
7047
7048 and document_name in ('MSD_LB_DETAILED_REPORT','MSD_LB_SUMMARY_REPORT'))
7049
7050 and dimension_code = 'PRD'
7051
7052 and selection_type = 'L'
7053
7054 and selection_value in ('1','2');
7055
7056
7057
7058 exception
7059
7060 when others then
7061
7062 p_errcode := substr(sqlerrm,1,150);
7063
7064
7065
7066 end set_prd_lvl_for_liab_reports;
7067
7068
7069
7070 /*******************************************************
7071
7072 This Fucntion gets the name of the Supply Plan name for the given plan ID.
7073
7074 Called from add_ascp_scenario, add_ascp_input_parameter, add_ascp_formula, add_ascp_measure.
7075
7076 Returns Plan Name.
7077
7078 ********************************************************/
7079
7080 /* -- Bug 4729854
7081
7082 Function get_supply_plan_name(p_supply_plan_id in number) return varchar2
7083
7084 is
7085
7086 l_plan_name varchar2(240);
7087
7088
7089
7090 cursor c1 is
7091
7092 select compile_designator
7093
7094 from msc_plans
7095
7096 where plan_id = p_supply_plan_id;
7097
7098
7099
7100 BEGIN
7101
7102
7103
7104 open c1;
7105
7106 fetch c1 into l_plan_name;
7107
7108 close c1;
7109
7110
7111
7112 -- bug # 4723901
7113
7114
7115
7116
7117
7118 return l_plan_name;
7119
7120
7121
7122 END;
7123
7124 */
7125
7126 /*******************************************************
7127
7128 This Fucntion gets the start date of the Supply Plan for the given plan ID.
7129
7130 Called from add_ascp_input_parameter.
7131
7132 Returns Start Date or Sysdate if Start Date is null( Means Plan has not been run)
7133
7134 ********************************************************/
7135
7136
7137
7138 Function get_supply_plan_start_date(p_supply_plan_id in number) return date -- Bug 4729854
7139
7140 is
7141
7142 l_start_date date;
7143
7144
7145
7146 cursor c1 is
7147
7148 select curr_start_date
7149
7150 from msc_plans
7151
7152 where plan_id = p_supply_plan_id;
7153
7154
7155
7156 BEGIN
7157
7158
7159
7163
7160 if(p_supply_plan_id <>-99) then -- Bug 4729854
7161
7162 open c1;
7164 fetch c1 into l_start_date;
7165
7166 close c1;
7167
7168
7169
7170 else l_start_date := msd_common_utilities.get_bucket_start_date(sysdate,2,6,'GREGORIAN'); -- Bug 4729854
7171
7172
7173
7174 end if; -- Bug 4729854
7175
7176
7177
7178 if l_start_date is not null then
7179
7180 return trunc(l_start_date);
7181
7182 else
7183
7184 return trunc(sysdate);
7185
7186 end if;
7187
7188
7189
7190 END;
7191
7192
7193
7194 /*******************************************************
7195
7196 This Fucntion gets the end date of the Supply Plan for the given plan ID.
7197
7198 Called from add_ascp_input_parameter.
7199
7200 Returns End Date or Sysdate if End Date is null( Means Plan has not been run)
7201
7202 ********************************************************/
7203
7204
7205
7206 Function get_supply_plan_end_date(p_supply_plan_id in number) return date -- Bug 4729854
7207
7208 is
7209
7210 l_end_date date;
7211
7212
7213
7214 cursor c1 is
7215
7216 select curr_cutoff_date
7217
7218 from msc_plans
7219
7220 where plan_id = p_supply_plan_id;
7221
7222
7223
7224 BEGIN
7225
7226
7227
7228 if(p_supply_plan_id <>-99) then -- Bug 4729854
7229
7230 open c1;
7231
7232 fetch c1 into l_end_date;
7233
7234 close c1;
7235
7236 else l_end_date := msd_common_utilities.get_bucket_end_date(sysdate,19,6,'GREGORIAN'); -- Bug 4729854
7237
7238 end if;
7239
7240 if l_end_date is not null then
7241
7242 return trunc(l_end_date);
7243
7244 else
7245
7246 return trunc(sysdate);
7247
7248 end if;
7249
7250
7251
7252 END;
7253
7254
7255
7256 /*******************************************************
7257
7258 This Fucntion gets the parameter ID of the input parameter for the given Input Parameter Type and Name.
7259
7260 Called from Replace_formula_tokens, Replace_dimension_tokens.
7261
7262 Returns Parameter ID.
7263
7264 ********************************************************/
7265
7266
7267
7268 Function get_parameter_id(p_demand_plan_id in number, p_parameter_type in varchar2, p_parameter_name in varchar2, p_parameter_component in varchar2)
7269
7270 return number
7271
7272 is
7273
7274
7275
7276 l_parameter_id number;
7277
7278
7279
7280 cursor c1 is
7281
7282 select parameter_id
7283
7284 from msd_dp_parameters
7285
7286 where demand_plan_id = p_demand_plan_id
7287
7288 and parameter_type = p_parameter_type
7289
7290 and nvl(parameter_name,'123456789') = nvl(p_parameter_name,'123456789');
7291
7292
7293
7294 cursor c2 is
7295
7296 select scenario_id
7297
7298 from msd_dp_scenarios
7299
7300 where demand_plan_id = p_demand_plan_id
7301
7302 and forecast_based_on = p_parameter_type
7303
7304 and nvl(parameter_name,'123456789') = nvl(p_parameter_name,'123456789');
7305
7306
7307
7308 BEGIN
7309
7310
7311
7312 if p_parameter_component like '%.SN' then
7313
7314
7315
7316 open c2;
7317
7318 fetch c2 into l_parameter_id;
7319
7320 close c2;
7321
7322
7323
7324 else
7325
7326
7327
7328 open c1;
7329
7330 fetch c1 into l_parameter_id;
7331
7332 close c1;
7333
7334
7335
7336 end if;
7337
7338
7339
7340 return l_parameter_id;
7341
7342
7343
7344 END;
7345
7346
7347
7348 /*******************************************************
7349
7350 This Fucntion gets the formula ID of the formual for the given formula Namea and supply plan name.
7351
7352 Called from Replace_formula_tokens, Replace_dimension_tokens.
7353
7354 Returns Formula ID.
7355
7356 ********************************************************/
7357
7358
7359
7360 Function get_formula_id(p_demand_plan_id in number, p_formula_name in varchar2, p_supply_plan_name in varchar2) return number
7361
7362 is
7363
7364
7365
7366 l_formula_id number;
7367
7368
7369
7370 cursor c1 is
7371
7372 select formula_id
7373
7374 from msd_dp_formulas
7375
7376 where demand_plan_id = p_demand_plan_id
7377
7378 and formula_name = p_formula_name
7379
7380 and nvl(supply_plan_name,'123456789') = nvl(p_supply_plan_name,'123456789');
7381
7382
7383
7384 BEGIN
7385
7386
7387
7388 open c1;
7389
7390 fetch c1 into l_formula_id;
7391
7392 close c1;
7393
7394
7395
7396 return l_formula_id;
7397
7398
7399
7400 END;
7401
7402
7403
7404 /*******************************************************
7405
7406 This Fucntion gets the First Calendar Code attahced to the demand plan for the given Output Period.
7407
7408 Thsi is used in finding Bucket start and end dates.
7409
7410 Called from change_output_period.
7411
7412 Returns Calendar Code.
7413
7417
7414 ********************************************************/
7415
7416
7418 Function get_calendar_code(p_demand_plan_id in number,p_old_output_period_type_id in number) return varchar2
7419
7420 is
7421
7422
7423
7424 cursor c1 is
7425
7426 select calendar_code
7427
7428 from msd_dp_calendars
7429
7430 where demand_plan_id = p_demand_plan_id
7431
7432 and calendar_type = decode(p_old_output_period_type_id,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4);
7433
7434
7435
7436 l_calendar_code varchar2(240);
7437
7438
7439
7440 BEGIN
7441
7442
7443
7444 open c1;
7445
7446 fetch c1 into l_calendar_code;
7447
7448 close c1;
7449
7450
7451
7452 return l_calendar_code;
7453
7454
7455
7456 END;
7457
7458
7459
7460 function get_hierarchy_id (p_demand_plan_id varchar2
7461
7462 ,p_hierarchy_id varchar2)
7463
7464 return number
7465
7466 as
7467
7468
7469
7470 cursor c_rep_hierarchy_id(p_demand_plan_id in number, p_hierarchy_id in number) is
7471
7472 select min(mdh.hierarchy_id)
7473
7474 from msd_dp_dimensions mdd,
7475
7476 msd_dp_hierarchies mdh,
7477
7478 msd_hierarchies mh
7479
7480 where mdd.dimension_code =
7481
7482 (select dimension_code
7483
7484 from msd_hierarchies
7485
7486 where hierarchy_id = p_hierarchy_id) and
7487
7488 mdh.dp_dimension_code=mdd.dp_dimension_code and
7489
7490 mdh.demand_plan_id=p_demand_plan_id and
7491
7492 mdd.demand_plan_id=p_demand_plan_id and
7493
7494 mdd.dimension_code=mh.dimension_code and
7495
7496 mh.hierarchy_id=mdh.hierarchy_id;
7497
7498
7499
7500 cursor c_hierarchy_id(p_demand_plan_id number, p_hierarchy_id number) is
7501
7502 select count(mdh.hierarchy_id)
7503
7504 from msd_dp_hierarchies mdh
7505
7506 where
7507
7508 mdh.demand_plan_id=p_demand_plan_id and
7509
7510 mdh.hierarchy_id=p_hierarchy_id;
7511
7512
7513
7514 l_exists number;
7515
7516 l_rep_hierarchy number;
7517
7518
7519
7520 begin
7521
7522
7523
7524 open c_hierarchy_id(to_number(p_demand_plan_id), to_number(p_hierarchy_id));
7525
7526 fetch c_hierarchy_id into l_exists;
7527
7528 close c_hierarchy_id;
7529
7530
7531
7532 if l_exists>0 then
7533
7534 return p_hierarchy_id;
7535
7536 end if;
7537
7538
7539
7540 open c_rep_hierarchy_id(to_number(p_demand_plan_id), to_number(p_hierarchy_id));
7541
7542 fetch c_rep_hierarchy_id into l_rep_hierarchy;
7543
7544 close c_rep_hierarchy_id;
7545
7546
7547
7548 return l_rep_hierarchy;
7549
7550
7551
7552 end get_hierarchy_id;
7553
7554
7555
7556 function get_level_id (p_demand_plan_id number
7557
7558 ,p_level_id number)
7559
7560 return number
7561
7562 as
7563
7564
7565
7566 cursor c_rep_level_id(p_demand_plan_id varchar2, p_level_id varchar2) is
7567
7568 select min(ml.level_id)
7569
7570 from msd_dp_dimensions mdd,
7571
7572 msd_dp_hierarchies mdh,
7573
7574 msd_hierarchies mh,
7575
7576 msd_hierarchy_levels mhl,
7577
7578 msd_levels ml
7579
7580 where mdd.dimension_code =
7581
7582 (select dimension_code
7583
7584 from msd_hierarchies
7585
7586 where hierarchy_id = mh.hierarchy_id) and
7587
7588 mdh.dp_dimension_code=mdd.dp_dimension_code and
7589
7590 mdh.demand_plan_id=p_level_id and
7591
7592 mdd.demand_plan_id=p_level_id and
7593
7594 mdd.dimension_code=mh.dimension_code and
7595
7596 mh.hierarchy_id=mdh.hierarchy_id and
7597
7598 mhl.hierarchy_id=mh.hierarchy_id and
7599
7600 (mhl.level_id=ml.level_id or mhl.parent_level_id=ml.level_id) and
7601
7602 ml.level_type_code = (select distinct level_type_code from msd_levels where level_id=p_level_id);
7603
7604
7605
7606 cursor c_level_id(p_demand_plan_id number, p_level_id number) is
7607
7608 select count(mdh.hierarchy_id)
7609
7610 from msd_dp_hierarchies mdh,
7611
7612 msd_hierarchy_levels mhl
7613
7614 where
7615
7616 mdh.demand_plan_id=p_demand_plan_id and
7617
7618 mhl.hierarchy_id=mdh.hierarchy_id and
7619
7620 (mhl.level_id=p_level_id or mhl.parent_level_id=p_level_id);
7621
7622
7623
7624 l_exists number;
7625
7626 l_rep_level number;
7627
7628
7629
7630 begin
7631
7632
7633
7634 open c_level_id(to_number(p_demand_plan_id), to_number(p_level_id));
7635
7636 fetch c_level_id into l_exists;
7637
7638 close c_level_id;
7639
7640
7641
7642 if l_exists>0 then
7643
7644 return p_level_id;
7645
7646 end if;
7647
7648
7649
7650 open c_rep_level_id(to_number(p_demand_plan_id), to_number(p_level_id));
7651
7652 fetch c_rep_level_id into l_rep_level;
7653
7654 close c_rep_level_id;
7655
7656
7657
7658 return l_rep_level;
7659
7660
7661
7662 end get_level_id;
7663
7664
7665
7669
7666 function get_dimension_code( p_demand_plan_id varchar2,
7667
7668 p_dimension_code varchar2)
7670 return varchar2
7671
7672 as
7673
7674
7675
7676 cursor c1(p_demand_plan_id number, p_dimension_code varchar2) is
7677
7678 select count(*)
7679
7680 from msd_dp_dimensions
7681
7682 where dimension_code=p_dimension_code and
7683
7684 demand_plan_id=p_demand_plan_id;
7685
7686
7687
7688 l_exists number;
7689
7690
7691
7692 begin
7693
7694
7695
7696 open c1(to_number(p_demand_plan_id),p_dimension_code);
7697
7698 fetch c1 into l_exists;
7699
7700 close c1;
7701
7702
7703
7704 if l_exists > 0 then
7705
7706 return p_dimension_code;
7707
7708 else
7709
7710 return null;
7711
7712 end if;
7713
7714
7715
7716 end get_dimension_code;
7717
7718
7719
7720 function get_dimension_script( p_demand_plan_id varchar2,
7721
7722 p_dimension_code varchar2,
7723
7724 p_dimension_script varchar2)
7725
7726 return varchar2
7727
7728 as
7729
7730
7731
7732 cursor c1(p_demand_plan_id number, p_dimension_code varchar2) is
7733
7734 select count(*)
7735
7736 from msd_dp_dimensions
7737
7738 where dimension_code=p_dimension_code and
7739
7740 demand_plan_id=p_demand_plan_id;
7741
7742
7743
7744 l_exists number;
7745
7746
7747
7748 begin
7749
7750
7751
7752 open c1(to_number(p_demand_plan_id),p_dimension_code);
7753
7754 fetch c1 into l_exists;
7755
7756 close c1;
7757
7758
7759
7760 if l_exists > 0 then
7761
7762 return p_dimension_script;
7763
7764 else
7765
7766 return null;
7767
7768 end if;
7769
7770
7771
7772 end get_dimension_script;
7773
7774
7775 Procedure add_ascp_scenario_for_eol(p_new_dp_id in number,p_supply_plan_id in number, p_supply_plan_name in varchar2) -- Bug 4729854
7776
7777 is
7778
7779 cursor c1 is
7780 select count(*) from
7781 msd_dp_scenarios
7782 where demand_plan_id = p_new_dp_id
7783 and supply_plan_flag = 'Y'
7784 and associate_parameter is not null;
7785
7786 cursor c2 is
7787 select scenario_name, description, demand_plan_id, scenario_id
7788 from msd_dp_scenarios
7789 where supply_plan_flag = 'Y'
7790 and demand_plan_id =
7791 (select demand_plan_id
7792 from msd_demand_plans
7793 where plan_type = 'EOL'
7794 and template_flag = 'Y'
7795 and default_template = 'Y'
7796 );
7797
7798
7799
7800 l_scenario_id number;
7801 l_count number;
7802 l_description varchar2(240);
7803 l_name varchar2(240);
7804
7805 BEGIN
7806
7807 open c1;
7808 fetch c1 into l_count;
7809 close c1;
7810
7811 if l_count > 0 then
7812 update msd_dp_scenarios set supply_plan_name=p_supply_plan_name, supply_plan_id=p_supply_plan_id
7813 where demand_plan_id=p_new_dp_id
7814 and supply_plan_flag='Y'
7815 and associate_parameter is not null
7816 and supply_plan_id<>p_supply_plan_id;
7817 else
7818 for c2_rec in c2
7819 loop
7820 select msd_dp_scenarios_s.nextval into l_scenario_id from dual;
7821 fnd_message.set_name('MSD',c2_rec.scenario_name);
7822 l_name := fnd_message.get;
7823 fnd_message.set_name('MSD',c2_rec.description);
7824 l_description := fnd_message.get;
7825
7826 insert into msd_dp_scenarios
7827 ( demand_plan_id
7828 ,scenario_id
7829 ,scenario_name
7830 ,description
7831 ,output_period_type
7832 ,horizon_start_date
7833 ,horizon_end_date
7834 ,forecast_date_used
7835 ,forecast_based_on
7836 ,last_update_date
7837 ,last_updated_by
7838 ,creation_date
7839 ,created_by
7840 ,last_update_login
7841 ,request_id
7842 ,program_application_id
7843 ,program_id
7844 ,program_update_date
7845 ,attribute_category
7846 ,attribute1
7847 ,attribute2
7848 ,attribute3
7849 ,attribute4
7850 ,attribute5
7851 ,attribute6
7852 ,attribute7
7853 ,attribute8
7854 ,attribute9
7855 ,attribute10
7856 ,attribute11
7857 ,attribute12
7858 ,attribute13
7859 ,attribute14
7860 ,attribute15
7861 ,scenario_type
7862 ,status
7863 ,history_start_date
7864 ,history_end_date
7865 ,publish_flag
7866 ,enable_flag
7867 ,price_list_name
7868 ,last_revision
7869 ,parameter_name
7870 ,consume_flag
7871 ,error_type
7872 ,supply_plan_id
7873 ,deleteable_flag
7874 ,supply_plan_flag
7875 ,supply_plan_name
7876 ,dmd_priority_scenario_id -- Bug 4710963
7877 ,associate_parameter
7878 ,sc_type)
7879 (select
7880 p_new_dp_id
7881 ,l_scenario_id
7882 ,l_name
7883 ,l_description
7884 ,output_period_type
7885 ,null
7886 ,null
7887 ,forecast_date_used
7888 ,forecast_based_on
7889 ,SYSDATE
7890 ,fnd_global.user_id
7891 ,SYSDATE
7892 ,fnd_global.user_id
7893 ,fnd_global.login_id
7894 ,NULL
7895 ,NULL
7896 ,NULL
7897 ,SYSDATE
7898 ,attribute_category
7899 ,attribute1
7900 ,attribute2
7901 ,attribute3
7902 ,attribute4
7903 ,attribute5
7904 ,attribute6
7905 ,attribute7
7906 ,attribute8
7907 ,attribute9
7908 ,attribute10
7909 ,attribute11
7910 ,attribute12
7911 ,attribute13
7912 ,attribute14
7913 ,attribute15
7914 ,scenario_type
7915 ,status
7916 ,null
7917 ,null
7918 ,publish_flag
7919 ,enable_flag
7920 ,price_list_name
7921 ,last_revision
7922 ,parameter_name
7923 ,consume_flag
7924 ,error_type
7925 ,p_supply_plan_id
7926 ,deleteable_flag
7927 ,supply_plan_flag
7928 ,p_supply_plan_name -- Bug 4729854
7929 ,dmd_priority_scenario_id -- Bug 4710963
7930 ,associate_parameter
7931 ,sc_type
7932 from msd_dp_scenarios
7933 where scenario_id = c2_rec.scenario_id
7934 and demand_plan_id = c2_rec.demand_plan_id);
7935
7936 insert into msd_dp_scenarios_tl
7937 ( demand_plan_id
7938 ,scenario_id
7939 ,description
7940 ,language
7941 ,source_lang
7942 ,creation_date
7943 ,created_by
7944 ,last_update_date
7945 ,last_updated_by
7946 ,last_update_login
7947 ,request_id
7948 ,program_application_id
7949 ,program_id
7950 ,program_update_date
7951 )
7952 select
7953 p_new_dp_id
7954 ,l_scenario_id
7955 ,l_description
7956 ,USERENV('LANG')
7957 ,USERENV('LANG')
7958 ,SYSDATE
7959 ,fnd_global.user_id
7960 ,SYSDATE
7961 ,fnd_global.user_id
7962 ,fnd_global.login_id
7963 ,NULL
7964 ,NULL
7965 ,NULL
7966 ,SYSDATE
7967 from dual;
7968
7969
7970 insert into msd_dp_scenario_events
7971 (
7972 demand_plan_id
7973 ,scenario_id
7974 ,event_id
7975 ,last_update_date
7976 ,last_updated_by
7977 ,creation_date
7978 ,created_by
7979 ,last_update_login
7980 ,request_id
7981 ,program_application_id
7982 ,program_id
7983 ,program_update_date
7984 ,event_association_priority)
7985 (select
7986 p_new_dp_id
7987 ,l_scenario_id
7988 ,event_id
7989 ,last_update_date
7990 ,last_updated_by
7991 ,creation_date
7992 ,created_by
7993 ,last_update_login
7994 ,request_id
7995 ,program_application_id
7996 ,program_id
7997 ,program_update_date
7998 ,event_association_priority
7999 from msd_dp_scenario_events
8000 where scenario_id = c2_rec.scenario_id
8001 and demand_plan_id = c2_rec.demand_plan_id);
8002
8003 insert into msd_dp_scenario_output_levels
8004 (
8005 demand_plan_id
8006 ,scenario_id
8007 ,level_id
8008 ,last_update_date
8009 ,last_updated_by
8010 ,creation_date
8011 ,created_by
8012 ,last_update_login
8013 ,request_id
8014 ,program_application_id
8015 ,program_id
8016 ,program_update_date)
8017 (select
8018 p_new_dp_id
8019 ,l_scenario_id
8020 ,level_id
8021 ,SYSDATE
8022 ,fnd_global.user_id
8023 ,SYSDATE
8024 ,fnd_global.user_id
8025 ,fnd_global.login_id
8026 ,NULL
8027 ,NULL
8028 ,NULL
8029 ,SYSDATE
8030 from msd_dp_scenario_output_levels
8031 where scenario_id = c2_rec.scenario_id
8032 and demand_plan_id = c2_rec.demand_plan_id);
8033
8034 end loop;
8035
8036 end if;
8037
8038
8039 END add_ascp_scenario_for_eol;
8040
8041
8042 END MSD_APPLY_TEMPLATE_DEMAND_PLAN;