1 PACKAGE BODY MSD_LIABILITY AS
2 /* $Header: msdliabb.pls 120.2 2005/09/12 05:38:32 anwroy noship $ */
3
4 FATAL_ERROR Constant varchar2(30):='FATAL_ERROR';
5 ERROR Constant varchar2(30):='ERROR';
6 WARNING Constant varchar2(30):='WARNING';
7 INFORMATION Constant varchar2(30):='INFORMATION';
8 SUCCESS Constant varchar2(30):='SUCCESS';
9 DEBUG Constant varchar2(30):='DEBUG';
10
11 /* Private Procedures of the package */
12
13
14 Procedure show_message(p_text in varchar2) is
15
16 Begin
17
18 if (p_text is not NULL) then
19 fnd_file.put_line(fnd_file.log, p_text);
20
21 end if;
22
23
24 end show_message ;
25
26
27 /* This procedure Logs the Debug message if MRP_DEBUG is true*/
28 /* This procedure displays message */
29 /* The idea behind having this function is to take care of proper and uniform formatting of messages */
30 Procedure display_message(p_text varchar2, p_msg_type varchar2 default null) is
31
32 Begin
33
34 if p_msg_type = DEBUG and C_MSC_DEBUG = 'Y' then
35
36 fnd_file.put_line(fnd_file.log, 'DEBUG:'||p_text);
37
38 elsif p_msg_type = ERROR then
39
40 fnd_file.put_line(fnd_file.log, '**ERROR**:'||p_text);
41
42 elsif p_msg_type = WARNING then
43
44 fnd_file.put_line(fnd_file.log, 'WARNING:'||p_text);
45
46 elsif p_msg_type = INFORMATION then
47
48 fnd_file.put_line(fnd_file.log, 'INFO:'||p_text);
49
50 elsif p_msg_type =SUCCESS then
51
52 fnd_file.put_line(fnd_file.log, 'SUCCESS:'||p_text);
53
54 elsif p_msg_type = DEBUG then
55
56 fnd_file.put_line(fnd_file.log, 'DEBUG:'||p_text);
57
58 else
59
60 fnd_file.put_line(fnd_file.log, 'FATAL_ERROR :'||p_text);
61
62 end if ;
63
64 End;
65
66
67 Procedure demand_plan_defn_validation
68 ( errbuf OUT NOCOPY VARCHAR2,
69 retcode OUT NOCOPY VARCHAR2,
70 p_plan_id IN NUMBER
71 ) ;
72
73 procedure clean_liability_level_values(
74 errbuf OUT NOCOPY VARCHAR2,
75 retcode OUT NOCOPY VARCHAR2
76 ) ;
77
78
79
80
81 procedure collect_mfg_time_data(
82 errbuf OUT NOCOPY VARCHAR2,
83 retcode OUT NOCOPY VARCHAR2,
84 p_demand_plan_id IN NUMBER
85 ) ;
86
87
88
89
90
91
92
93 /* This procedure Locks the demand Plan record when the validation is taking place */
94 /* So that user cannot change any of the parameters of the Demand Plan */
95 Procedure Lock_Row(p_demand_plan_id in number) ;
96
97
98 /* This procedure is part of the Collection code and collects data for the Level */
99
100
101 procedure collect_level_parent_data(
102 errbuf OUT NOCOPY VARCHAR2,
103 retcode OUT NOCOPY VARCHAR2,
104 p_plan_id IN NUMBER,
105 p_level_id IN NUMBER,
106 p_parent_level_id IN NUMBER,
107 p_update_lvl_table IN NUMBER
108 );
109
110 /* This procedure validates the demand Plan */
111 Procedure validate_demand_plan( errbuf OUT NOCOPY VARCHAR2,
112 retcode OUT NOCOPY VARCHAR2,
113 p_demand_plan_id in number ) ;
114
115
116 /* This procedure validates the Setp data */
117 /* This procedure sets the various paarmeters of the demand plan */
118 /* Base Uom Measure*/
119 /* Item Category Profile */
120 Procedure setup_validation ( errbuf OUT NOCOPY VARCHAR2,
121 retcode OUT NOCOPY VARCHAR2,
122 p_plan_id IN NUMBER
123 ) ;
124
125
126
127 /* This procedure deletes duplicate level Values in the Level Association Table*/
128 Procedure Delete_duplicate_lvl_assoc( errbuf OUT NOCOPY VARCHAR2,
129 retcode OUT NOCOPY VARCHAR2,
130 p_plan_id in number);
131
132 /* This procedure deletes duplicate level Values in the Level Value Table*/
133 Procedure Delete_duplicate(p_plan_id in number, p_dest_table in varchar2);
134
135
136 /* This procedure is for collection of level values */
137 procedure collect_liability_level_values(
138 errbuf OUT NOCOPY VARCHAR2,
139 retcode OUT NOCOPY VARCHAR2,
140 p_plan_id IN NUMBER
141 );
142
143
144 /* This procedure pulls the level values from staging to fact */
145 Procedure pull_level_values_data(
146 errbuf OUT NOCOPY VARCHAR2,
147 retcode OUT NOCOPY VARCHAR2,
148 p_plan_id IN NUMBER) ;
149
150 /* This procedure does the actual insert of Level values into fact and association table */
151 Procedure translate_level_parent_values(
152 errbuf OUT NOCOPY VARCHAR2,
153 retcode OUT NOCOPY VARCHAR2,
154 p_source_table IN VARCHAR2,
155 p_dest_table IN VARCHAR2,
156 p_plan_id IN NUMBER,
157 p_level_id IN NUMBER,
158 p_level_value_column IN VARCHAR2,
159 p_level_value_pk_column IN VARCHAR2,
160 p_level_value_desc_column IN VARCHAR2,
161 p_parent_level_id IN NUMBER,
162 p_parent_value_column IN VARCHAR2,
163 p_parent_value_pk_column IN VARCHAR2,
164 p_parent_value_desc_column IN VARCHAR2,
165 p_update_lvl_table IN NUMBER,
166 p_delete_flag IN VARCHAR2,
167 p_seq_num IN NUMBER);
168
169 /* This is called by pull to move data from staging to fact */
170 PROCEDURE PROCESS_LEVEL_VALUE_PER_ROW(
171 errbuf OUT NOCOPY VARCHAR2,
172 retcode OUT NOCOPY VARCHAR2,
173 p_plan_id IN VARCHAR2,
174 p_level_id IN NUMBER,
175 p_seq_num IN NUMBER);
176
177 /* This is called by pull to move data from staging to Association Table */
178 PROCEDURE PROCESS_LEVEL_ASSOCIATION(
179 errbuf OUT NOCOPY VARCHAR2,
180 retcode OUT NOCOPY VARCHAR2,
181 p_plan_id IN VARCHAR2,
182 p_level_id IN NUMBER,
183 p_parent_level_id IN NUMBER,
184 p_seq_num IN NUMBER);
185
186
187 /* This procedure moves the top level values form Source view to Staging */
188 PROCEDURE PROCESS_TOP_LEVEL_VALUES (
189 errbuf OUT NOCOPY VARCHAR2,
190 retcode OUT NOCOPY VARCHAR2,
191 p_source_table IN VARCHAR2,
192 p_dest_table IN VARCHAR2,
193 p_plan_id IN VARCHAR2,
194 p_parent_level_id IN NUMBER,
195 p_parent_value_column IN VARCHAR2,
196 p_parent_value_pk_column IN VARCHAR2,
197 p_parent_value_desc_column IN VARCHAR2,
198 p_seq_num IN NUMBER,
199 p_delete_flag IN VARCHAR2);
200
201
202
203 /* This part of collect_liability_level_values */
204 Procedure collect_dimension_data(
205 errbuf OUT NOCOPY VARCHAR2,
206 retcode OUT NOCOPY VARCHAR2,
207 p_plan_id IN NUMBER,
208 p_dimension_code IN VARCHAR2) ;
209
210
211
212 /* Public Procedures */
213
214
215 /* This procedure is called from MSC_GET_BIS_VALUES.UI_POST_PLAN.
216 * It checks to see if the user is going to run Liability for PDS plan.
217 */
218
219 procedure run_liability_flow_ascp(
220 errbuf OUT NOCOPY VARCHAR2,
221 retcode OUT NOCOPY VARCHAR2,
222 p_plan_id IN NUMBER
223 ) IS
224
225 x_calculate_liability number;
226
227 cursor get_liab_flag is
228 select calculate_liability
229 from msc_plans
230 where plan_id = p_plan_id;
231
232 begin
233
234 open get_liab_flag;
235 fetch get_liab_flag into x_calculate_liability;
236 close get_liab_flag;
237
238 if x_calculate_liability <> 1 then
239 return;
240 end if;
241
242 run_liability_flow(errbuf, retcode, p_plan_id);
243
244 end;
245
246
247
248 /* This procedure is a wrapper over the private procedure
249 * 'validate_demand_plan'.
250 * This procedure will be called from the procedure
251 * msd_validate_demand_plan.validate_demand_plan
252 * Bug# 4345323 User will now be able to validate an
253 * existing liability plan from the UI
254 */
255 PROCEDURE validate_liability_plan (
256 errbuf OUT NOCOPY VARCHAR2,
257 retcode OUT NOCOPY VARCHAR2,
258 p_liability_plan_id IN NUMBER)
259 IS
260 BEGIN
261
262 display_message('Liability Plan Validation: BEGIN' ,INFORMATION );
263
264 /* Set the liability plan to INVALID */
265 update msd_demand_plans
266 set valid_flag = 1
267 where demand_plan_id = p_liability_plan_id ;
268
269 /* Validate the plan */
270 validate_demand_plan (
271 errbuf,
272 retcode,
273 p_liability_plan_id );
274
275 /* If Validation Fails, Return */
276 IF retcode = 2 THEN
277 RETURN;
278 ELSE /* Set the plan status to VALID */
279 update msd_demand_plans
280 set valid_flag = 0
281 where demand_plan_id = p_liability_plan_id ;
282 END IF;
283
284 display_message('Liability Plan Validation: END' ,INFORMATION );
285
286 RETURN;
287
288 EXCEPTION
289
290 WHEN OTHERS THEN
291 retcode := 2;
292 errbuf := substr( sqlerrm, 1, 80);
293
294 END validate_liability_plan;
295
296
297
298 /* The wrapper program that is called from the concurrent program */
299 /*1. This program Creates the Liability Demand Plan if it does not exist */
300 /* or updates an existing Plan */
301 /*2. Validates the demand Plan*/
302 /*3. Collects Liability Level Values */
303 /*4. Checks if a Gregoria Calendar exist for plan Start Date and end Date */
304 /*and generates if not available */
305 /* 5. Calls the demand Plan buildApi to buid the Demand Plan Cube in Olap */
306
307
308 Procedure run_liability_flow(
309 errbuf OUT NOCOPY VARCHAR2,
310 retcode OUT NOCOPY VARCHAR2,
311 p_plan_id IN NUMBER
312 ) IS
313
314
315 x_liab_plan_id NUMBER ;
316 x_code BOOLEAN ;
317 x_cal NUMBER ;
318 x_plan_start_date date ;
319 x_plan_end_date DATE ;
320 x_liab_plan_name VARCHAR2(200) ;
321 x_cal_start_date DATE ;
322 x_cal_end_date DATE ;
323 x_plan_owning_org NUMBER ;
324 x_plan_owning_instance NUMBER ;
325 x_default_mfg_cal VARCHAR2(200) ;
326 x_no_of_days NUMBER ;
327 x_cal_no_of_days NUMBER ;
328 strCmd varchar2(200) ;
329 strCodeAW varchar2(200) ;
330 x_DP_BUILD_ERROR_FLAG varchar2(200) ;
331 x_liab_calc_level NUMBER ;
332 x_prev_liab_calc_level NUMBER ;
333 x_scenario_id NUMBER ;
334
335 Begin
336 retcode := 0 ;
337
338 display_message( 'Start of Liability Run ' , INFORMATION);
339
340 /* Validate setup Data */
341
342
343 x_liab_calc_level := FND_PROFILE.Value('MSC_LIABILITY_CALC_LEVEL') ;
344
345
346
347
348 display_message( 'Calling procedure - Clean Liability Level Values ', DEBUG );
349
350 clean_liability_level_values( errbuf , retcode ) ;
351
352
353 x_plan_start_date := MSD_COMMON_UTILITIES_LB.get_supply_plan_start_date( p_plan_id) ;
354
355
356 IF p_plan_id <> -1 THEN
357
358 x_plan_end_date := MSD_COMMON_UTILITIES_LB.get_supply_plan_end_date( p_plan_id) ;
359
360 ELSE
361
362 /* Weak Link */
363 x_plan_end_date := MSD_COMMON_UTILITIES_LB.get_cp_end_date ;
364
365 END IF ;
366
367 /* This preprocessor updates the Agreement details for the forecast liability views */
368
369 MSD_COMMON_UTILITIES_LB. liability_preprocessor ( p_plan_id ) ;
370
371
372
373
374 /* Finding out if demand plan for the given Liability Plan Exists */
375
376 x_liab_plan_id := MSD_COMMON_UTILITIES_LB.get_demand_plan_id( p_plan_id) ;
377
378 IF x_liab_plan_id IS NULL THEN
379
380 /* Inputs to Template */
381
382 x_liab_plan_name := MSD_COMMON_UTILITIES_LB.get_supply_plan_name( p_plan_id ) ;
383 x_plan_owning_org := MSD_COMMON_UTILITIES_LB.get_plan_owning_org(p_plan_id) ;
384 x_plan_owning_instance := MSD_COMMON_UTILITIES_LB.get_plan_owning_instance( p_plan_id) ;
385 x_default_mfg_cal := MSD_COMMON_UTILITIES_LB.get_default_mfg_cal( x_plan_owning_org ,x_plan_owning_instance) ;
386
387 display_message( ' x_plan_owning_org:'|| x_plan_owning_org , DEBUG ) ;
388
389 display_message( ' x_plan_owning_instance :'||x_plan_owning_instance ,DEBUG );
390
391 /* Calling Template API */
392
393
394
395 display_message('Calling procedure - MSD_APPLY_TEMPLATE_DEMAND_PLAN.create_plan_using_template', DEBUG);
396
397 x_code := MSD_APPLY_TEMPLATE_DEMAND_PLAN.create_plan_using_template
398 (
399 x_liab_plan_id, ---p_new_dp_id out nocopy number,
400 x_liab_plan_name,-- p_target_demand_plan_nameinVARCHAR2,
401 x_liab_plan_name,--p_target_demand_plan_descr in VARCHAR2,
402 'LIABILITY' ,--p_plan_type in VARCHAR2
403 x_plan_start_date,--p_plan_start_date in date
404 x_plan_end_date,--p_plan_end_date in date
405 p_plan_id,--p_supply_plan_id in number
406 x_liab_plan_name,---p_supply_plan_name in VARCHAR2,
407 x_plan_owning_org ,--p_organization_id in number
408 x_plan_owning_instance,---p_instance_id in number
409 retcode --p_errcode in out nocopy varchar2
410 ) ;
411
412
413
414
415
416 x_liab_plan_id := MSD_COMMON_UTILITIES_LB.get_demand_plan_id( p_plan_id) ;
417
418 /* Updating the output Levels of the Total Liability */
419 /* If there is a change of profile in two consecutive run the program will give a warning */
420 /* If the profile value is null then a warning will be given and the output level will be defaulted to Item */
421
422 select mdsol.LEVEL_ID , mds.scenario_id into x_prev_liab_calc_level , x_scenario_id from
423 MSD_DP_SCENARIO_OUTPUT_LEVELS mdsol ,
424 msd_dp_scenarios mds
425 where
426 mds.demand_plan_id = mdsol.demand_plan_id and
427 mds.scenario_id = mdsol.scenario_id
428 and mds.demand_plan_id =x_liab_plan_id
429 and mds.scenario_designator = 'TOTAL_LIABILITY'
430 and mdsol.level_id in ( 1,2) ;
431
432
433 IF x_liab_calc_level is not null THEN
434 /* update the output level of the scenario */
435 update MSD_DP_SCENARIO_OUTPUT_LEVELS
436 set level_id = x_liab_calc_level
437 where level_id = x_prev_liab_calc_level
438 and demand_plan_id = x_liab_plan_id
439 and scenario_id = x_scenario_id ;
440
441 display_message( 'The the output level of the scenario will be updated ' ,WARNING );
442 else
443 /* Warning that the output level is set to item by default */
444
445 display_message( 'The the output level of the scenario will be updated ' ,WARNING );
446
447 END IF ;
448
449
450
451
452
453
454
455
456 ELSE
457 select mdsol.LEVEL_ID , mds.scenario_id into x_prev_liab_calc_level , x_scenario_id from
458 MSD_DP_SCENARIO_OUTPUT_LEVELS mdsol ,
459 msd_dp_scenarios mds
460 where
461 mds.demand_plan_id = mdsol.demand_plan_id and
462 mds.scenario_id = mdsol.scenario_id
463 and mds.demand_plan_id =x_liab_plan_id
464 and mds.scenario_designator = 'TOTAL_LIABILITY'
465 and mdsol.level_id in ( 1,2) ;
466
467
468
469 IF x_liab_calc_level is not null THEN
470 /* update the output level of the scenario */
471 update MSD_DP_SCENARIO_OUTPUT_LEVELS
472 set level_id = x_liab_calc_level
473 where level_id = x_prev_liab_calc_level
474 and demand_plan_id = x_liab_plan_id
475 and scenario_id = x_scenario_id ;
476 commit ;
477
478 IF x_prev_liab_calc_level <> x_liab_calc_level THEN
479 /* Warn the user that the level of calculation of liability is changing */
480 display_message( 'level of calculation of liability is changing' ,WARNING );
481 END IF ;
482
483 END IF ;
484
485
486
487
488
489 /* Updating the existing demand Plan */
490 display_message(' Updating the existing demand Plan ', DEBUG ) ;
491
492
493 /* Set the Plan Start Date and End Date in MSD_DEMAND_PLAN */
494
495 /* IF p_plan_id = -1 THEN
496 x_plan_end_date := MSD_COMMON_UTILITIES_LB.get_cp_end_date ;
497 END IF ;
498 */
499 ---display_message('CS_NAME ', x_liab_plan_name ) ;
500
501 update msd_demand_plans set plan_start_date = x_plan_start_date , plan_end_date = x_plan_end_date
502 where demand_plan_id = x_liab_plan_id ;
503
504 UPDATE msd_dp_parameters set start_date = x_plan_start_date , end_date = x_plan_end_date
505 WHERE parameter_type in ( 'MSD_LIAB_OPEN_PO' , 'MSD_LIAB_FORECAST' ,'MSD_LIAB_FCST_DEMAND')
506 and demand_plan_id = x_liab_plan_id ;
507
508 commit ;
509 /*UPDATE msd_dp_parameters SET PARAMETER_NAME = x_liab_plan_name
510 WHERE demand_plan_id = x_liab_plan_id ; */
511
512
513
514 END IF;
515
516 display_message( 'Setup Validation ' , DEBUG) ;
517 setup_validation ( errbuf ,
518 retcode,p_plan_id
519 ) ;
520
521
522 /* If Set up Validation Fails Exit with Error */
523 IF retcode = 2 THEN
524 update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
525 RETURN ;
526
527
528 display_message(' Validating Demand Plan Definition' , DEBUG ) ;
529
530 demand_plan_defn_validation( errbuf ,retcode,p_plan_id ) ;
531
532 IF retcode = 2 THEN
533
534 RETURN ;
535 update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
536
537 END IF;
538
539 /* Set the Previous Plan dates */
540
541 display_message('Set the Previous Plan dates' , DEBUG) ;
542
543 retcode := MSD_COMMON_UTILITIES_LB.liability_plan_update( x_liab_plan_id ) ;
544
545 commit ;
546
547 /* IF LIABILITY run happens without intermediate ascp plan run and take the uploaded liability
548 from previous plan start date and change the liability_plan_pub stsrt date and rev num*/
549 /* other wise previous plan_start_date = prev plan fro which liability is published */
550
551
552
553 END IF ;
554
555 display_message('Plan Details ' , DEBUG);
556
557 display_message('Plan Id: '||p_plan_id, DEBUG );
558
559 display_message('Plan Name: '||MSD_COMMON_UTILITIES_LB.get_supply_plan_name(p_plan_id), DEBUG );
560
561 display_message('Plan Start Date : '||x_plan_start_date , DEBUG );
562
563 display_message('Plan End Date : '||x_plan_end_date , DEBUG );
564
565
566 collect_mfg_time_data( errbuf , retcode , x_liab_plan_id ) ;
567
568
569 /* Collection of Level Values */
570
571
572
573 collect_liability_level_values(
574 errbuf => errbuf,
575 retcode => retcode,
576 p_plan_id => p_plan_id);
577
578
579 IF p_plan_id = -1 THEN
580
581 x_plan_end_date := MSD_COMMON_UTILITIES_LB.get_cp_end_date ;
582
583
584
585 UPDATE msd_dp_parameters set start_date = x_plan_start_date , end_date = x_plan_end_date
586 WHERE parameter_type in ( 'MSD_LIAB_OPEN_PO' , 'MSD_LIAB_FORECAST' ,'MSD_LIAB_FCST_DEMAND')
587 and demand_plan_id = x_liab_plan_id ;
588
589 END IF ;
590
591
592
593 /* Validation and Generation of Gregorian Calendar Data */
594
595 display_message('Validation of Gregorian Calendar' , DEBUG );
596
597 x_no_of_days := trunc( x_plan_end_date,'DD') - trunc( x_plan_start_date, 'DD') ;
598
599 Select count(*) into x_cal_no_of_days
600 from msd_time
601 where calendar_type = 1 and day between
602 x_plan_start_date and x_plan_end_date
603 ;
604
605 If x_no_of_days + 1 <> x_cal_no_of_days
606 Then
607 display_message( 'Generating Gergorian Calendar ' , INFORMATION);
608 display_message( 'Deleteing Gergorian Calendar between '||x_plan_start_date||'---'||x_plan_end_date , DEBUG);
609 delete from msd_time where calendar_type = 1 and trunc( day, 'DD') between trunc( x_plan_start_date, 'DD') and trunc( x_plan_end_date , 'DD') ;
610
611 display_message( 'Generating Gergorian Calendar between '||x_plan_start_date||'---'||x_plan_end_date , DEBUG);
612 MSD_TRANSLATE_TIME_DATA.Generate_Gregorian( errbuf,
613 retcode,
614 'GREGORIAN',
615 trunc(x_plan_start_date,'DD'),
616 trunc( x_plan_end_date, 'DD' )) ;
617
618 END IF ;
619
620
621 /* Validation of Demand Plan */
622
623 x_liab_plan_id := MSD_COMMON_UTILITIES_LB.get_demand_plan_id( p_plan_id) ;
624
625 /* The Demand Plan Plan Definition is locked so that the user cannot change any details */
626
627
628 /* Validate Procedure Called*/
629
630 display_message('Demand Plan Validation' ,DEBUG );
631
632 validate_demand_plan( errbuf ,retcode ,x_liab_plan_id ) ;
633
634 /* If Validation Fails Exit with Error */
635 IF retcode = 2 THEN
636 RETURN ;
637 update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
638 END IF;
639
640
641
642
643 /* Here set the Demand Plan to Valid or Invalid */
644 update msd_demand_plans set valid_flag = 0 where liab_plan_id = p_plan_id ;
645
646
647 /* Start:DPE Download */
648 /* Place Holder for DPE API */
649 display_message('Before the call of Build API' ,DEBUG );
650
651 --- strCmd := 'aw attach FALIU_ODP ro; call BLD.LIABILITY(''' || MSD_COMMON_UTILITIES_LB.get_demand_plan_id(p_plan_id) || ''' ;)';
652 strCodeAW := nvl( fnd_profile.value('MSD_CODE_AW'), 'ODPCODE') ;
653 strCmd := 'aw attach '|| strCodeAW ||' ro; call BLD.LIABILITY(''' || MSD_COMMON_UTILITIES_LB.get_demand_plan_id(p_plan_id) || ''');';
654
655 display_message(strCmd ,DEBUG );
656
657 dbms_aw.execute(strCmd);
658
659 display_message('After the call of Build API' ,DEBUG );
660
661 /* IF the build errors out then the error out handling is done */
662
663 -- retcode := MSD_COMMON_UTILITIES_LB.liability_post_process( 97608 , 'TOTAL_LIABILITY', 1) ;
664 Select DP_BUILD_ERROR_FLAG into x_DP_BUILD_ERROR_FLAG
665 from msd_demand_plans
666 where demand_plan_id = MSD_COMMON_UTILITIES_LB.get_demand_plan_id(p_plan_id) ;
667
668 IF nvl( x_DP_BUILD_ERROR_FLAG , 'NO') = 'YES' THEN
669 display_message('Plan Build Unsuccessful' ,ERROR );
670 END IF ;
671
672 /* End: DPE Download */
673
674 Commit;
675
676 EXCEPTION
677
678 WHEN others THEN
679 BEGIN
680 Delete_duplicate(p_plan_id , MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE);
681 Delete_duplicate_lvl_assoc(errbuf, retcode, p_plan_id);
682 COMMIT;
683 EXCEPTION
684 WHEN others THEN
685 retcode := -1;
686 errbuf := substr(SQLERRM,1,150);
687 fnd_file.put_line(fnd_file.log , sqlerrm );
688
689 END;
690 retcode := -1 ;
691 errbuf := substr(SQLERRM,1,150);
692
693
694 End run_liability_flow ;
695
696 Procedure validate_demand_plan( errbuf OUT NOCOPY VARCHAR2,
697 retcode OUT NOCOPY VARCHAR2,
698 p_demand_plan_id in number)
699
700 IS
701
702 /* This cursor returns the Hierarchy ,Level and relationship view for which No data is there is level Values Table */
703
704 CURSOR get_dim_no_lvl( p_plan_id IN NUMBER)
705 IS
706 SELECT DISTINCT dp_dimension_code,
707 hl.hierarchy_name,
708 hl.level_name,
709 hl.relationship_view
710 FROM msd_dp_hierarchies dh,
711 msd_hierarchy_levels_lb_v hl
712 WHERE demand_plan_id = p_demand_plan_id
713 AND dp_dimension_code <> 'TIM'
714 AND dh.hierarchy_id = hl.hierarchy_id
715 AND level_id NOT IN
716 (select distinct level_id
717 from msd_level_values_lb lv
718 where lv.plan_id = p_plan_id
719 );
720
721
722 /* This cursor contains the calendar details of the Calendar Associated with a demand Plan */
723
724 CURSOR get_dp_cal
725 IS
726 SELECT calendar_type, calendar_code, decode(calendar_type,
727 1, initcap(calendar_code),
728 calendar_code) op_cal_code
729 FROM msd_dp_calendars
730 WHERE demand_plan_id = p_demand_plan_id
731 and calendar_type <> 1; -- To Prevent the validation of Gregorian calendar because validation of Gregorian Calendar is not required
732
733
734
735 /* This cursor returns the maximum and minimum dates of the input parameters */
736 CURSOR get_input_date is
737 SELECT
738 min(start_date), max(end_date)
739 FROM msd_dp_parameters_cs_v
740 WHERE demand_plan_id = p_demand_plan_id;
741
742
743 /* This cursor returns the start date and end date of a given calendar */
744 CURSOR get_tim(p_calendar_type VARCHAR2, p_calendar_code VARCHAR2,
745 p_start_date DATE, p_end_date DATE) IS
746 SELECT MIN(day) min_date, MAX(day) max_date
747 FROM msd_time_lb_v dp
748 WHERE dp.calendar_type = p_calendar_type
749 AND dp.calendar_code = p_calendar_code ;
750 --AND day between p_start_date and p_end_date;
751
752
753 /* This returns the input parameter asscociated with */
754 CURSOR c_input_params IS
755 SELECT
756 distinct
757 mdp.parameter_type ,
758 mcd.planning_server_view_name ,
759 mcd.description
760 FROM msd_dp_parameters mdp , msd_cs_definitions mcd
761 where mdp.demand_plan_id =p_demand_plan_id
762 and parameter_type =mcd.name
763 and nvl( mcd.planning_server_view_name, 'NA') <> 'NA'
764 and nvl(mcd.liability_user_flag , 'N') <> 'Y' ;
765
766 x_no_of_recs NUMBER := 0 ;
767 x_min_date DATE ;
768 x_max_date DATE ;
769 x_dp_min_date DATE ;
770 x_dp_max_date DATE ;
771 x_plan_id NUMBER ;
772 v_sql_stmt varchar2(200) ;
773 x_plan_name varchar2(100) ;
774
775
776 BEGIN
777
778
779 /* START: Level Values Validation */
780
781
782
783
784 retcode := 0 ;
785
786
787 x_plan_id := MSD_COMMON_UTILITIES_LB.get_supply_plan_id( p_demand_plan_id) ;
788
789 x_plan_name := MSD_COMMON_UTILITIES_LB.get_supply_plan_name( x_plan_id ) ;
790
791 Lock_Row( p_demand_plan_id ) ;
792
793 /* Loop through the levels that do not have Level Value data in the level Values Table */
794
795 FOR get_dim_no_lvl_rec IN get_dim_no_lvl(x_plan_id)
796
797
798 LOOP
799 --- fnd_file.put_line(fnd_file.log,'ERROR: Dim- '||get_dim_no_lvl_rec.dp_dimension_code||' Hierarchy- '||get_dim_no_lvl_rec.hierarchy_name||' Level-'||get_dim_no_lvl_rec.level_name||' No Data') ;
800
801 display_message( ' Dim '||get_dim_no_lvl_rec.dp_dimension_code||' Hierarchy- '||get_dim_no_lvl_rec.hierarchy_name||' Level-'||get_dim_no_lvl_rec.level_name||' No Data', ERROR) ;
802
803
804 retcode := 2 ;
805 /* IF the data for any of the level does not exist then the validation will fail */
806
807 display_message( get_dim_no_lvl_rec.relationship_view||' does not contain data for Plan_id' ||x_plan_id, ERROR) ;
808
809 END LOOP;
810
811 IF retcode = 2 THEN
812
813 --- fnd_file.put_line(fnd_file.log, 'Note : Possible Causes of Error ') ;
814
815 display_message( 'Possible Causes of Error ' , INFORMATION ) ;
816
817 ---fnd_file.put_line(fnd_file.log, '1. There is no Agreement defined for the any of the item/suppliers/org included in this Plan ') ;
818
819 display_message( '1. There is no Agreement defined for the any of the item/suppliers/org included in this Plan ',INFORMATION) ;
820
821
822 --- fnd_file.put_line(fnd_file.log,' 2. The ASCP Plan has not been run') ;
823
824 display_message(' 2. The ASCP Plan has not been run',INFORMATION) ;
825
826 --fnd_file.put_line(fnd_file.log, ' 3. The Plan out put of the given ASCP Plan has been purged ') ;
827
828 display_message(' 3. The Plan out put of the given ASCP Plan has been purged ',INFORMATION) ;
829
830
831 END IF ;
832
833
834
835
836 /* END:Check Level Values */
837
838
839 /* made incompatible with ASCP Collection Program */
840 /* Stream Validation */
841
842 /* Loop through Every Stream and find out if at least one record exists or not */
843 FOR x_input_param_rec IN c_input_params
844
845 LOOP
846
847 IF x_input_param_rec.planning_server_view_name is NOT NULL THEN
848
849
850 v_sql_stmt := 'select count(1) from dual where exists (select 1 from '
851 ||x_input_param_rec.planning_server_view_name
852 ||' where cs_name = '
853 ||''''||x_plan_name||''''||' ) ' ;
854
855
856
857 execute immediate v_sql_stmt into x_no_of_recs ;
858
859 /* If No record exists for the given stream then give warning to the user */
860 IF x_no_of_recs = 0 THEN
861
862 --Fnd_file.put_line(fnd_file.log,'Warning:No data in '||x_input_param_rec.description) ;
863
864 display_message('No data in '||x_input_param_rec.description ,WARNING) ;
865
866 IF retcode <> 2 THEN
867 retcode := 1 ;
868 END IF ;
869
870
871 END IF ;
872 END IF ;
873
874
875 END LOOP ;
876
877 /* End of Stream Validation */
878
879
880 /* START : Time validation of user attached Calendar */
881
882 OPEN get_input_date ;
883
884 FETCH get_input_date INTO x_dp_min_date, x_dp_max_date ;
885
886 CLOSE get_input_date;
887
888
889
890 FOR cal_rec IN get_dp_cal
891
892 LOOP
893
894 x_min_date := null;
895
896 x_max_date := null;
897
898 --- Fnd_file.put_line(fnd_file.log,'Validating '||cal_rec.calendar_code) ;
899
900
901
902 FOR x_cal_rec IN get_tim(cal_rec.calendar_type, cal_rec.calendar_code,x_dp_min_date,x_dp_max_date)
903
904 LOOP
905
906 x_min_date := x_cal_rec.min_date ;
907 x_max_date := x_cal_rec.max_date ;
908
909 IF x_min_date IS NULL OR x_max_date IS NULL THEN
910
911
912 retcode := 2 ;
913
914
915 -- fnd_file.put_line(fnd_file.log,'Error:No Time data in '||cal_rec.calendar_code||' between Plan Start Date and End Date');
916 display_message( 'No Time data in '||cal_rec.calendar_code||' between Plan Start Date and End Date',ERROR) ;
917 --- fnd_file.put_line(fnd_file.log, 'Note : Remove the Calendar ' ||cal_rec.calendar_code||' from the Liability Plan and rerun the concurrent program ') ;
918 display_message( 'Remove the Calendar ' ||cal_rec.calendar_code||' from the Liability Plan and rerun the concurrent program ',INFORMATION) ;
919
920 END IF ;
921
922 IF x_min_date > x_dp_min_date THEN
923
924 retcode := 2 ;
925
926 -- fnd_file.put_line(fnd_file.log,'Error: '||cal_rec.calendar_code||' Cal Start Date after plan start date' );
927 display_message( cal_rec.calendar_code||' Cal Start Date after plan start date', ERROR ) ;
928
929 -- fnd_file.put_line(fnd_file.log, 'Note : Remove the Calendar ' ||cal_rec.calendar_code||' from the Liability Plan and rerun the concurrent program ') ;
930 display_message(' Remove the Calendar ' ||cal_rec.calendar_code||' from the Liability Plan and rerun the concurrent program ',INFORMATION) ;
931
932 END IF ;
933
934 IF x_max_date < x_dp_max_date THEN
935
936 retcode := 2 ;
937 --fnd_file.put_line(fnd_file.log,'Error: '||cal_rec.calendar_code||':Cal date ends before plan end date' ) ;
938
939 display_message( cal_rec.calendar_code||':Cal date ends before plan end date',ERROR ) ;
940
941 -- fnd_file.put_line(fnd_file.log, 'Note : Remove the Calendar ' ||cal_rec.calendar_code||' from the Liability Plan and rerun the concurrent program ') ;
942
943 display_message( 'Remove the Calendar ' ||cal_rec.calendar_code||' from the Liability Plan and rerun the concurrent program ',INFORMATION) ;
944
945 END IF ;
946
947 END LOOP;
948
949 END LOOP ;
950
951
952 IF retcode = 2 THEN
953
954 --fnd_file.put_line(fnd_file.log,'Error: Demand Plan Validation Failed ' ) ;
955 display_message( 'Demand Plan Validation Failed ',ERROR) ;
956
957 --fnd_file.put_line(fnd_file.log,'Note: Fix the error and rerun the Concurrent Program' ) ;
958 display_message( ' Fix the error and rerun the Concurrent Program',INFORMATION ) ;
959
960 END IF ;
961
962
963 EXCEPTION
964
965 when others then
966 retcode := 2;
967 errbuf := substr(SQLERRM,1,150);
968 fnd_file.put_line(fnd_file.log,'*****'||v_sql_stmt||'*****');
969
970
971
972
973
974 END validate_demand_plan;
975
976
977
978
979
980 procedure collect_level_parent_data(
981 errbuf OUT NOCOPY VARCHAR2,
982 retcode OUT NOCOPY VARCHAR2,
983 p_plan_id IN NUMBER,
984 p_level_id IN NUMBER,
985 p_parent_level_id IN NUMBER,
986 p_update_lvl_table IN NUMBER) IS
987
988 x_source_table VARCHAR2(50) ;
989 x_dest_table varchar2(50) ;
990 v_dest_ass_table varchar2(240) ;
991 v_sql_stmt varchar2(4000);
992 x_delete_flag varchar2(1) := 'Y' ;
993 p_level_name VARCHAR2(30);
994 p_parent_level_name VARCHAR2(30);
995 p_hierarchy_name VARCHAR2(30);
996
997 p_seq_num NUMBER;
998
999 /************************************************************************
1000 Cursor to get distinct relationship view and the corresponding columns
1001 *************************************************************************/
1002 /* Include hierarchy_id in this cursor. We need hierarchy_id info
1003 for reporting error when there is no relationship_view defined */
1004 Cursor Relationship (p_level_id in number, p_parent_level_id in number) is
1005 select distinct
1006 hierarchy_id,
1007 relationship_view,
1008 level_value_column,
1009 level_value_pk_column,
1010 nvl(level_value_desc_column,level_value_column) level_value_desc_column,
1011 parent_value_column,
1012 parent_value_pk_column,
1013 nvl(parent_value_desc_column, parent_value_column) parent_value_desc_column
1014 from msd_hierarchy_levels
1015 where level_id = p_level_id
1016 and parent_level_id = p_parent_level_id
1017 and plan_type = 'LIABILITY';
1018
1019 g_retcode varchar2(5) := '0';
1020
1021 Begin
1022
1023
1024
1025 /* Always 2 step collection */
1026 x_dest_table := MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE ;
1027 v_dest_ass_table := MSD_COMMON_UTILITIES_LB.LEVEL_ASSOC_STAGING_TABLE;
1028
1029
1030 /* Relationship LOOP */
1031 For Relationship_Rec IN Relationship(p_level_id, p_parent_level_id) LOOP
1032 --fnd_file.put_line(fnd_file.log,'1:collect_level_parent_data ' );
1033 /* Check whether relationship_view is NULL or not.
1034 IF NULL then give WARNING message and go to the next cursor.
1035 Do not try to translate level values if the relationship_view is NULL */
1036
1037 /* Begining of IF 1 */
1038 IF ( Relationship_Rec.relationship_view IS NULL ) THEN
1039 SELECT hierarchy_name INTO p_hierarchy_name
1040 FROM msd_hierarchies
1041 WHERE hierarchy_id = Relationship_Rec.hierarchy_id;
1042
1043 p_level_name := MSD_COMMON_UTILITIES.get_level_name(p_level_id);
1044 p_parent_level_name := MSD_COMMON_UTILITIES.get_level_name(p_parent_level_id);
1045
1046 fnd_file.put_line(fnd_file.log, ' ');
1047 fnd_file.put_line(fnd_file.log, 'Relationship view is not defined for ' ||
1048 'Hierarchy : '|| p_hierarchy_name || '. (No Data Collected.)');
1049 fnd_file.put_line(fnd_file.log, ' Level : ' || p_level_name );
1050 fnd_file.put_line(fnd_file.log, ' Parent Level : ' || p_parent_level_name );
1051
1052 /* IF we have relationship_view name then proceed the following codes */
1053 ELSE
1054 x_source_table := Relationship_Rec.relationship_view ;
1055
1056 translate_level_parent_values(
1057 errbuf => errbuf,
1058 retcode => retcode,
1059 p_source_table => x_source_table,
1060 p_dest_table => x_dest_table,
1061 p_plan_id => p_plan_id,
1062 p_level_id => p_level_id,
1063 p_level_value_column => Relationship_Rec.level_value_column,
1064 p_level_value_pk_column => Relationship_Rec.level_value_pk_column,
1065 p_level_value_desc_column => Relationship_Rec.level_value_desc_column,
1066 p_parent_level_id => p_parent_level_id,
1067 p_parent_value_column => Relationship_Rec.parent_value_column,
1068 p_parent_value_pk_column => Relationship_Rec.parent_value_pk_column,
1069 p_parent_value_desc_column => Relationship_Rec.parent_value_desc_column,
1070 p_update_lvl_table => p_update_lvl_table,
1071 p_delete_flag => x_delete_flag,
1072 p_seq_num => p_seq_num);
1073
1074 --update return code
1075 IF retcode <> '0' THEN
1076 g_retcode := retcode;
1077 END IF;
1078 END IF ;
1079 END Loop ;
1080
1081
1082
1083 exception
1084
1085 when others then
1086 retcode := -1 ;
1087 errbuf := substr(SQLERRM,1,150);
1088 -- insert into msd_test values('Error: ' || errbuf) ;
1089 fnd_file.put_line(fnd_file.log,'*****'||errbuf||'*****');
1090
1091 End collect_level_parent_data ;
1092
1093
1094
1095
1096
1097
1098
1099 procedure collect_dimension_data(
1100 errbuf OUT NOCOPY VARCHAR2,
1101 retcode OUT NOCOPY VARCHAR2,
1102 p_plan_id IN NUMBER,
1103 p_dimension_code IN VARCHAR2) IS
1104
1105 x_level_id NUMBER := 0;
1106 /******************************************************
1107 Cursor to get distinct level parent combinations
1108 in a dimension
1109 ******************************************************/
1110 Cursor Dim_Level_Parent(p_dimension_code IN VARCHAR2) is
1111 select distinct level_id, parent_level_id, level_type_code
1112 from msd_hierarchy_levels_lb_v
1113 where owning_dimension_code = p_dimension_code
1114 order by level_type_code, level_id;
1115
1116 /* Cursor to get levels alone */
1117 Cursor Level_Cursor(p_dimension_code IN VARCHAR2) is
1118 select distinct level_id, level_type_code
1119 from msd_hierarchy_levels_lb_v
1120 where owning_dimension_code = p_dimension_code
1121 order by level_type_code, level_id;
1122
1123 g_retcode varchar2(5) := '0';
1124
1125
1126
1127 Begin
1128
1129 --fnd_file.put_line(fnd_file.log,'1:Inside Collect dimension data ' );
1130 For Dim_Level_Parent_Rec IN Dim_Level_Parent (p_dimension_code) LOOP
1131
1132
1133 if (x_level_id = Dim_Level_Parent_Rec.level_id) then
1134
1135 collect_level_parent_data(
1136 errbuf => errbuf,
1137 retcode => retcode,
1138 p_plan_id => p_plan_id,
1139 p_level_id => Dim_Level_Parent_Rec.level_id,
1140 p_parent_level_id => Dim_Level_Parent_Rec.parent_level_id,
1141 p_update_lvl_table => 0);
1142 -- fnd_file.put_line(fnd_file.log,'2:Inside Collect dimension data 2 ' );
1143 else
1144 collect_level_parent_data(
1145 errbuf => errbuf,
1146 retcode => retcode,
1147 p_plan_id => p_plan_id,
1148 p_level_id => Dim_Level_Parent_Rec.level_id,
1149 p_parent_level_id => Dim_Level_Parent_Rec.parent_level_id,
1150 p_update_lvl_table => 1);
1151 -- fnd_file.put_line(fnd_file.log,'3:Inside Collect dimension data 3 ' );
1152 end if;
1153
1154 x_level_id := Dim_Level_Parent_Rec.level_id;
1155
1156 end loop ;
1157 --- fnd_file.put_line(fnd_file.log,'4:Inside Collect dimension data ' );
1158
1159
1160
1161
1162
1163 retcode := g_retcode;
1164
1165 EXCEPTION
1166
1167 when others then
1168 retcode := 2 ;
1169 errbuf := substr(SQLERRM,1,150);
1170
1171
1172 End collect_dimension_data ;
1173
1174 procedure collect_liability_level_values(
1175 errbuf OUT NOCOPY VARCHAR2,
1176 retcode OUT NOCOPY VARCHAR2,
1177 p_plan_id IN NUMBER) IS
1178 /******************************************************
1179 Cursor to get ALL Dimensions
1180 ******************************************************/
1181 Cursor Dimensions is
1182 select lookup_code
1183 from fnd_lookup_values_vl
1184 where lookup_type = 'MSD_DIMENSIONS_LB' ;
1185
1186 g_retcode varchar2(5) := '0';
1187
1188 Begin
1189
1190 For Dimensions_Rec IN Dimensions LOOP
1191
1192 collect_dimension_data(
1193 errbuf => errbuf,
1194 retcode => retcode,
1195 p_plan_id => p_plan_id,
1196 p_dimension_code => Dimensions_Rec.lookup_code);
1197
1198 --update return code
1199 if retcode <> '0' then
1200 g_retcode := retcode;
1201 end if;
1202
1203 /* Commit for every Dimension, so user can see the progress */
1204 commit;
1205
1206 end loop ;
1207
1208 /* delete duplicate data */
1209 Delete_duplicate(p_plan_id, MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE);
1210
1211 /* Delete duplicate level association from staging table */
1212 Delete_duplicate_lvl_assoc(errbuf, retcode, p_plan_id);
1213
1214
1215 ---- fnd_file.put_line(fnd_file.log,' ********* PULLING Data********* ' );
1216
1217 pull_level_values_data( errbuf => errbuf,
1218 retcode => retcode,
1219 p_plan_id => p_plan_id );
1220 Commit;
1221 /*-----------------------------*/
1222
1223
1224 retcode := g_retcode;
1225
1226 End collect_liability_level_values ;
1227
1228
1229 Procedure Delete_duplicate(p_plan_id in number, p_dest_table in varchar2) is
1230 Begin
1231
1232 /* This procedure deletes duplicate records from staging level_values
1233 Key - Plan_id + Level_Id + SR_LEVEL_PK
1234 */
1235
1236 if p_dest_table = MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE then
1237 delete from msd_st_level_values_lb a where
1238 a.plan_id = p_plan_id and
1239 rowid <> (select max(rowid) from msd_st_level_values_lb b
1240 where a.plan_id = b.plan_id and a.level_id = b.level_id and a.sr_level_pk = b.sr_level_pk);
1241 END IF ;
1242
1243 End;
1244
1245 Procedure Delete_duplicate_lvl_assoc( errbuf OUT NOCOPY VARCHAR2,
1246 retcode OUT NOCOPY VARCHAR2,
1247 p_plan_id in number) is
1248
1249 cursor c_duplicate is
1250 select level_id, sr_level_pk, parent_level_id
1251 from msd_st_level_associations_lb
1252 where plan_id = p_plan_id
1253 group by level_id, sr_level_pk, parent_level_id
1254 having count(*) > 1;
1255
1256 TYPE level_id_tab is table of msd_st_level_associations.level_id%TYPE;
1257 TYPE sr_level_pk_tab IS TABLE OF msd_st_level_associations.sr_level_pk%TYPE;
1258
1259 a_child_level_id level_id_tab;
1260 a_parent_level_id level_id_tab;
1261 a_sr_level_pk sr_level_pk_tab;
1262
1263
1264 Begin
1265
1266 /* This procedure deletes duplicate records from staging level association
1267 Key - Plan_id + Child_Level_Id + SR_LEVEL_PK + Parent_Level_ID
1268 */
1269
1270 OPEN c_duplicate;
1271 FETCH c_duplicate BULK COLLECT INTO a_child_level_id, a_sr_level_pk, a_parent_level_id ;
1272 CLOSE c_duplicate;
1273
1274 IF (a_child_level_id.exists(1)) THEN
1275 FOR i IN a_child_level_id.FIRST..a_child_level_id.LAST LOOP
1276 delete from msd_st_level_associations_lb a where
1277 a.plan_id = p_plan_id and
1278 a.level_id = a_child_level_id(i) and
1279 a.sr_level_pk = a_sr_level_pk(i) and
1280 a.parent_level_id = a_parent_level_id(i) and
1281 rowid <> (select rowid from msd_st_level_associations_lb b
1282 where b.plan_id = p_plan_id and
1283 b.level_id = a_child_level_id(i) and
1284 b.sr_level_pk = a_sr_level_pk(i) and
1285 b.parent_level_id = a_parent_level_id(i) and
1286 rownum < 2);
1287 END LOOP;
1288 END IF;
1289
1290 NULL ;
1291
1292 EXCEPTION
1293 when others then
1294 errbuf := substr(SQLERRM,1,150);
1295 retcode := -1;
1296 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1297
1298
1299 END Delete_duplicate_lvl_assoc;
1300
1301
1302 procedure translate_level_parent_values(
1303 errbuf OUT NOCOPY VARCHAR2,
1304 retcode OUT NOCOPY VARCHAR2,
1305 p_source_table IN VARCHAR2,
1306 p_dest_table IN VARCHAR2,
1307 p_plan_id IN NUMBER,
1308 p_level_id IN NUMBER,
1309 p_level_value_column IN VARCHAR2,
1310 p_level_value_pk_column IN VARCHAR2,
1311 p_level_value_desc_column IN VARCHAR2,
1312 p_parent_level_id IN NUMBER,
1313 p_parent_value_column IN VARCHAR2,
1314 p_parent_value_pk_column IN VARCHAR2,
1315 p_parent_value_desc_column IN VARCHAR2,
1316 p_update_lvl_table IN NUMBER,
1317 p_delete_flag IN VARCHAR2,
1318 p_seq_num IN NUMBER ) IS
1319
1320 v_plan_id varchar2(40);
1321 v_retcode number;
1322 v_sql_stmt varchar2(4000);
1323 v_dest_ass_table varchar2(240) ;
1324 v_sr_ass_table varchar2(240) ;
1325 v_parent_lvl_type varchar2(3);
1326 v_lvl_type varchar2(1);
1327 v_dim_code varchar2(3);
1328 v_org_view varchar2(30);
1329 v_up number;
1330 ---x_dblink VARCHAR2(128);
1331
1332 Begin
1333
1334 -- fnd_file.put_line(fnd_file.log,'1:translate Level Values ' );
1335
1336 v_up := p_update_lvl_table;
1337
1338
1339 retcode :=0;
1340
1341 Savepoint Before_Delete ;
1342
1343 /* Beginning of IF 1 */
1344 -- fnd_file.put_line(fnd_file.log,'***********'||p_dest_table );
1345 IF (p_dest_table = MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_FACT_TABLE) THEN
1346 -- fnd_file.put_line(fnd_file.log,'***********'||p_dest_table );
1347 v_dest_ass_table := MSD_COMMON_UTILITIES_LB.LEVEL_ASSOC_FACT_TABLE ;
1348 v_sr_ass_table := MSD_COMMON_UTILITIES_LB.LEVEL_ASSOC_STAGING_TABLE ;
1349
1350 /* First time to process this level_id */
1351 IF (p_update_lvl_table = 1) THEN
1352 /* Insert deleted level values into deleted_level_value table and delete it
1353 from the fact level value table */
1354 /* For Incremental Level Value Collection, p_delete_flag = 'N'
1355 So, we don't delete existing level values */
1356
1357 /* Process row by row from staging level values table */
1358 -- fnd_file.put_line(fnd_file.log,'3:translate Level Values ' );
1359 PROCESS_LEVEL_VALUE_PER_ROW( errbuf,
1360 retcode,
1361 p_plan_id,
1362 p_level_id,
1363 p_seq_num);
1364 END IF;
1365
1366
1367
1368 /* Process from staging level associations table */
1369 -- fnd_file.put_line(fnd_file.log,'4:translate Level Values ' );
1370 PROCESS_LEVEL_ASSOCIATION(
1371 errbuf,
1372 retcode,
1373 p_plan_id,
1374 p_level_id,
1375 p_parent_level_id,
1376 p_seq_num);
1377
1378 /* ELSE for IF 1. COLLECTION */
1379 ELSIF (p_dest_table = MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE) THEN
1380 --- fnd_file.put_line(fnd_file.log,'5:translate Level Values ' );
1381 v_dest_ass_table := MSD_COMMON_UTILITIES_LB.LEVEL_ASSOC_STAGING_TABLE;
1382
1383 /* Delete Staging Table only if delete flag = Yes */
1384 IF (p_delete_flag = 'Y') THEN
1385 /* First time to process this level_id */
1386 IF (p_update_lvl_table = 1) THEN
1387 DELETE FROM msd_st_level_values_lb
1388 WHERE plan_id = p_plan_id AND level_id = p_level_id;
1389 END IF;
1390
1391 -- fnd_file.put_line(fnd_file.log,'5.1:translate Level Values ' );
1392 DELETE FROM msd_st_level_associations_lb
1393 WHERE plan_id = p_plan_id AND
1394 level_id = p_level_id
1395 AND parent_level_id = p_parent_level_id;
1396 -- fnd_file.put_line(fnd_file.log,'5.2:translate Level Values ' );
1397 END IF;
1398 --- fnd_file.put_line(fnd_file.log,'2:translate Level Values ' );
1399 /* Insert Level Values into staging table */
1400 v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( '
1401 ||'plan_id, ' ||
1402 'level_id, ' ||
1403 'level_value, ' ||
1404 'sr_level_pk, ' ||
1405 'level_value_desc, ' ||
1406 'last_update_date, ' ||
1407 'last_updated_by, ' ||
1408 'creation_date, ' ||
1409 'created_by ) ' ||
1410 'select ' ||
1411 p_plan_id ||', ' ||
1412 p_level_id || ', ' ||
1413 p_level_value_column||', ' ||
1414 p_level_value_pk_column||', ' ||
1415 p_level_value_desc_column||', ' ||
1416 'sysdate, ' ||
1417 FND_GLOBAL.USER_ID || ', ' ||
1418 'sysdate, ' ||
1419 FND_GLOBAL.USER_ID || ' ' ||
1420 'from ' ||
1421 p_source_table||
1422 ' where '||
1423 'plan_id = '||
1424 p_plan_id ;
1425
1426
1427 display_message( v_sql_stmt ,DEBUG) ;
1428 --- fnd_file.put_line(fnd_file.log,v_sql_stmt );
1429
1430
1431 EXECUTE IMMEDIATE v_sql_stmt;
1432
1433 /* Insert Level Associations into staging table */
1434 v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || v_dest_ass_table || ' ( ' ||
1435 'plan_id, ' ||
1436 'level_id, ' ||
1437 'sr_level_pk, ' ||
1438 'parent_level_id, ' ||
1439 'sr_parent_level_pk, ' ||
1440 'last_update_date, ' ||
1441 'last_updated_by, ' ||
1442 'creation_date, ' ||
1443 'created_by ) ' ||
1444 'select ' ||
1445 p_plan_id||', ' ||
1446 p_level_id || ', ' ||
1447 p_level_value_pk_column||', ' ||
1448 p_parent_level_id || ', ' ||
1449 p_parent_value_pk_column ||', ' ||
1450 'sysdate, ' ||
1451 FND_GLOBAL.USER_ID || ', ' ||
1452 'sysdate, ' ||
1453 FND_GLOBAL.USER_ID || ' ' ||
1454 ' from ' ||
1455 p_source_table||
1456 ' where '||
1457 'plan_id = '||
1458 p_plan_id ;
1459
1460 display_message( v_sql_stmt,DEBUG ) ;
1461
1462 EXECUTE IMMEDIATE v_sql_stmt;
1463 ---fnd_file.put_line(fnd_file.log,'3:translate Level Values : after insert into ');
1464
1465 END IF; /* End of IF 1 */
1466
1467 /* Get the Parent Level Type */
1468 begin
1469 select level_type_code into v_parent_lvl_type
1470 from msd_levels
1471 where level_id = p_parent_level_id
1472 and plan_type = 'LIABILITY' ;
1473
1474 exception
1475 when NO_DATA_FOUND then
1476 null;
1477 WHEN others THEN
1478 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1479 errbuf := substr(SQLERRM,1,150);
1480 end ;
1481
1482
1483
1484 /* dbms_output.put_line('Parent Level : ' || p_parent_level_id ) ;
1485 dbms_output.put_line('Parent Level Type : ' || v_parent_lvl_type ) ; */
1486
1487 ---fnd_file.put_line(fnd_file.log,'********'||p_update_lvl_table||'*************');
1488 /* Process parent level value only if it is TOP level value*/
1489 IF (v_parent_lvl_type = '1' AND p_update_lvl_table = 1) THEN
1490
1491 --fnd_file.put_line(fnd_file.log,'1: Before PROCESS_TOP_LEVEL_VALUES ');
1492 PROCESS_TOP_LEVEL_VALUES (
1493 errbuf,
1494 retcode,
1495 p_source_table,
1496 p_dest_table,
1497 p_plan_id,
1498 p_parent_level_id,
1499 p_parent_value_column,
1500 p_parent_value_pk_column,
1501 p_parent_value_desc_column,
1502 p_seq_num,
1503 p_delete_flag);
1504
1505
1506 END IF;
1507
1508 COMMIT;
1509
1510 exception
1511 when others then
1512 --write to log an back out
1513 errbuf := substr(SQLERRM,1,150);
1514 retcode := 1 ; --warning
1515 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1516 fnd_file.put_line(fnd_file.log, 'The offending sql is:');
1517 fnd_file.put_line(fnd_file.log, v_sql_stmt);
1518 rollback;
1519 -- rollback to Savepoint Before_Delete ;
1520
1521 End translate_level_parent_values ;
1522
1523
1524 /***********************************************************
1525
1526 PROCEDURE PROCESS_LEVEL_VALUE_PER_ROW
1527
1528 ***********************************************************/
1529 PROCEDURE PROCESS_LEVEL_VALUE_PER_ROW(
1530 errbuf OUT NOCOPY VARCHAR2,
1531 retcode OUT NOCOPY VARCHAR2,
1532 p_plan_id IN VARCHAR2,
1533 p_level_id IN NUMBER,
1534 p_seq_num IN NUMBER) IS
1535
1536 /* New Level values will be inserted into fact table
1537 and will get deleted from the staging */
1538 CURSOR c_insert IS
1539 select sr_level_pk
1540 from msd_st_level_values_lb
1541 where plan_id = p_plan_id and level_id = p_level_id
1542 MINUS
1543 select sr_level_pk
1544 from msd_level_values_lb
1545 where plan_id = p_plan_id and level_id = p_level_id;
1546
1547 /* Cursor to find modified level values */
1548 /* This cursor needs to be opend only after
1549 new level values are deleted from the staging table
1550 */
1551 CURSOR c_update IS
1552 (select sr_level_pk, level_value,
1553 level_value_desc
1554 from msd_st_level_values_lb
1555 where plan_id = p_plan_id and level_id = p_level_id
1556 MINUS
1557 select sr_level_pk, level_value,
1558 level_value_desc
1559 from msd_level_values_lb
1560 where plan_id = p_plan_id and level_id = p_level_id);
1561
1562
1563
1564 TYPE sr_level_pk_tab IS TABLE OF msd_st_level_values.sr_level_pk%TYPE;
1565 TYPE level_val_tab IS TABLE OF msd_st_level_values.level_value%TYPE;
1566 TYPE level_attribute_tab IS TABLE OF msd_st_level_values.level_value_desc%TYPE;
1567
1568 x_level_pk NUMBER ;
1569 a_sr_level_pk sr_level_pk_tab;
1570 a_level_pk sr_level_pk_tab:= sr_level_pk_tab();
1571 a_level_value level_val_tab;
1572 a_level_value_desc level_attribute_tab;
1573
1574 BEGIN
1575 ---fnd_file.put_line(fnd_file.log,'** In PROCESS_LEVEL_VALUE_PER_ROW ********');
1576 OPEN c_insert;
1577 FETCH c_insert BULK COLLECT INTO a_sr_level_pk;
1578 CLOSE c_insert;
1579
1580 IF (a_sr_level_pk.exists(1)) THEN
1581 /* First Delete fetched rows from staging, and then
1582 Insert them into Fact Table.
1583 */
1584 ---fnd_file.put_line(fnd_file.log,'**In 2 PROCESS_LEVEL_VALUE_PER_ROW********');
1585 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1586 DELETE FROM msd_st_level_values_lb
1587 WHERE plan_id = p_plan_id and
1588 level_id = p_level_id and
1589 sr_level_pk = a_sr_level_pk(i)
1590 RETURNING level_value, level_value_desc
1591 BULK COLLECT INTO a_level_value,
1592 a_level_value_desc;
1593
1594
1595 /* Generate Level_pk */
1596 FOR k IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST LOOP
1597 -- fnd_file.put_line(fnd_file.log,'**Inside level_pk generation 1********');
1598 a_level_pk.extend ;
1599 a_level_pk( k ) := MSD_COMMON_UTILITIES_LB.get_level_pk( p_level_id , a_sr_level_pk(k)) ;
1600 --- fnd_file.put_line(fnd_file.log,'**Inside level_pk generation 2********');
1601 END LOOP ;
1602
1603 ---fnd_file.put_line(fnd_file.log,'**inserting into fact********');
1604 --- fnd_file.put_line(fnd_file.log,'--'||p_plan_id||':'||p_level_id);
1605
1606 /* Insert new rows into fact table */
1607 FORALL j IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1608
1609
1610 INSERT INTO msd_level_values_lb(
1611 plan_id, level_id, level_value,
1612 sr_level_pk, level_pk, level_value_desc,
1613 action_code, created_by_refresh_num, last_refresh_num,
1614 last_update_date, last_updated_by,
1615 creation_date, created_by,
1616 last_update_login)
1617 VALUES(
1618 p_plan_id,
1619 p_level_id,
1620 a_level_value(j),
1621 a_sr_level_pk(j),
1622 a_level_pk(j),
1623 a_level_value_desc(j),
1624 'I', p_seq_num, p_seq_num,
1625 sysdate, FND_GLOBAL.USER_ID,
1626 sysdate, FND_GLOBAL.USER_ID,
1627 FND_GLOBAL.LOGIN_ID );
1628 END IF;
1629
1630
1631 /* Fetch updated rows from staging */
1632 OPEN c_update;
1633 FETCH c_update BULK COLLECT INTO a_sr_level_pk, a_level_value,
1634 a_level_value_desc;
1635 CLOSE c_update;
1636
1637 IF (a_sr_level_pk.exists(1)) THEN
1638 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1639 UPDATE msd_level_values_lb
1640 SET level_value = a_level_value(i),
1641 level_value_desc = a_level_value_desc(i),
1642 action_code = 'U',
1643 last_refresh_num = p_seq_num,
1644 last_update_date = sysdate
1645 WHERE plan_id = p_plan_id and
1646 level_id = p_level_id and
1647 sr_level_pk = a_sr_level_pk(i);
1648 END IF;
1649
1650
1651
1652 EXCEPTION
1653 when others then
1654 errbuf := substr(SQLERRM,1,150);
1655 retcode := -1;
1656 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1657
1658
1659
1660 END PROCESS_LEVEL_VALUE_PER_ROW;
1661
1662
1663
1664
1665
1666 /***********************************************************
1667
1668 PROCEDURE PROCESS_LEVEL_ASSOCIATION
1669
1670 ***********************************************************/
1671 PROCEDURE PROCESS_LEVEL_ASSOCIATION(
1672 errbuf OUT NOCOPY VARCHAR2,
1673 retcode OUT NOCOPY VARCHAR2,
1674 p_plan_id IN VARCHAR2,
1675 p_level_id IN NUMBER,
1676 p_parent_level_id IN NUMBER,
1677 p_seq_num IN NUMBER) IS
1678
1679 /* This cursur will select only new level associations */
1680 CURSOR c_new_rows IS
1681 (select sr_level_pk
1682 from msd_st_level_associations_lb
1683 where plan_id = p_plan_id and level_id = p_level_id and
1684 parent_level_id = p_parent_level_id
1685 MINUS
1686 select sr_level_pk
1687 from msd_level_associations_lb
1688 where plan_id = p_plan_id and level_id = p_level_id and
1689 parent_level_id = p_parent_level_id);
1690
1691
1692 /* Cursor for updated level association */
1693 /* This cursor need to be opened only after
1694 new associations are deleted from the staging table */
1695 CURSOR c_update_rows IS
1696 (select sr_level_pk, sr_parent_level_pk
1697 from msd_st_level_associations_lb
1698 where plan_id = p_plan_id and level_id = p_level_id and
1699 parent_level_id = p_parent_level_id
1700 MINUS
1701 select sr_level_pk, sr_parent_level_pk
1702 from msd_level_associations_lb
1703 where plan_id = p_plan_id and level_id = p_level_id and
1704 parent_level_id = p_parent_level_id);
1705
1706
1707
1708 TYPE sr_level_pk_tab is table of msd_level_associations.sr_level_pk%TYPE;
1709 TYPE sr_parent_level_pk_tab is table of msd_level_associations.sr_parent_level_pk%TYPE;
1710
1711 a_sr_level_pk SR_LEVEL_PK_TAB;
1712 a_sr_parent_level_pk SR_PARENT_LEVEL_PK_TAB;
1713
1714 l_count NUMBER := 0;
1715
1716 BEGIN
1717 OPEN c_new_rows;
1718 FETCH c_new_rows BULK COLLECT INTO a_sr_level_pk;
1719 CLOSE c_new_rows;
1720
1721 /* For new level association */
1722 IF (a_sr_level_pk.exists(1)) THEN
1723 /* First Delete fetched rows(new level associations) from staging,
1724 and then Insert them into Fact Table.
1725 */
1726 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1727 DELETE FROM msd_st_level_associations_lb
1728 WHERE plan_id = p_plan_id and
1729 level_id = p_level_id and
1730 sr_level_pk = a_sr_level_pk(i) and
1731 parent_level_id = p_parent_level_id
1732 RETURNING sr_parent_level_pk
1733 BULK COLLECT INTO a_sr_parent_level_pk;
1734
1735 /* Insert new rows into fact table */
1736 IF (a_sr_parent_level_pk.exists(1)) THEN
1737 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1738 INSERT INTO msd_level_associations_lb(
1739 plan_id, level_id, sr_level_pk,
1740 parent_level_id, sr_parent_level_pk,
1741 last_update_date, last_updated_by,
1742 creation_date, created_by, last_update_login,
1743 created_by_refresh_num, last_refresh_num, action_code)
1744 VALUES( p_plan_id, p_level_id, a_sr_level_pk(i),
1745 p_parent_level_id, a_sr_parent_level_pk(i),
1746 sysdate, FND_GLOBAL.USER_ID,
1747 sysdate,FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
1748 p_seq_num, p_seq_num, 'I');
1749 END IF;
1750 END IF; /* End of New Association */
1751
1752 OPEN c_update_rows;
1753 FETCH c_update_rows BULK COLLECT INTO a_sr_level_pk, a_sr_parent_level_pk;
1754 CLOSE c_update_rows;
1755
1756 /* For updated level association */
1757 IF (a_sr_level_pk.exists(1) and a_sr_parent_level_pk.exists(1)) THEN
1758 FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
1759 UPDATE msd_level_associations_lb
1760 SET
1761 sr_parent_level_pk = a_sr_parent_level_pk(i),
1762 action_code = 'U',
1763 last_refresh_num = p_seq_num,
1764 last_update_date = sysdate
1765 WHERE plan_id = p_plan_id and
1766 level_id = p_level_id and
1767 sr_level_pk = a_sr_level_pk(i) and
1768 parent_level_id = p_parent_level_id;
1769 END IF;
1770
1771 EXCEPTION
1772 when others then
1773 errbuf := substr(SQLERRM,1,150);
1774 retcode := -1;
1775 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1776
1777
1778 END PROCESS_LEVEL_ASSOCIATION;
1779
1780
1781
1782
1783 /***********************************************************
1784
1785 PROCEDURE PROCESS_TOP_LEVEL_VALUES
1786
1787 ***********************************************************/
1788 PROCEDURE PROCESS_TOP_LEVEL_VALUES (
1789 errbuf OUT NOCOPY VARCHAR2,
1790 retcode OUT NOCOPY VARCHAR2,
1791 p_source_table IN VARCHAR2,
1792 p_dest_table IN VARCHAR2,
1793 p_plan_id IN VARCHAR2,
1794 p_parent_level_id IN NUMBER,
1795 p_parent_value_column IN VARCHAR2,
1796 p_parent_value_pk_column IN VARCHAR2,
1797 p_parent_value_desc_column IN VARCHAR2,
1798 p_seq_num IN NUMBER,
1799 p_delete_flag IN VARCHAR2) IS
1800
1801
1802 v_sql_stmt varchar2(4000);
1803
1804 BEGIN
1805
1806
1807 /* dbms_output.put_line('Parent Level : ' || p_parent_level_id ) ; */
1808
1809 /* Note that we will not be able to get the attributes 1 - 5 for the
1810 Top level as we will not have a separate view for the top level */
1811 ---fnd_file.put_line(fnd_file.log,'2:PROCESS_TOP_LEVEL_VALUES '||p_parent_level_id );
1812 /* For PULL */
1813 IF (p_dest_table = MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_FACT_TABLE) THEN
1814 PROCESS_LEVEL_VALUE_PER_ROW( errbuf,
1815 retcode,
1816 p_plan_id,
1817 p_parent_level_id,
1818 p_seq_num);
1819 ELSE
1820 /* Collect into Staging table*/
1821 --fnd_file.put_line(fnd_file.log,'2:PROCESS_TOP_LEVEL_VALUES '||p_parent_level_id );
1822 delete from msd_st_level_values_lb
1823 where plan_id = p_plan_id
1824 and level_id = p_parent_level_id ;
1825 --- fnd_file.put_line(fnd_file.log,'3:PROCESS_TOP_LEVEL_VALUES ' );
1826
1827 v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( ' ||
1828 'plan_id, ' ||
1829 'level_value, ' ||
1830 'sr_level_pk, ' ||
1831 'level_id, ' ||
1832 'level_value_desc, ' ||
1833 'last_update_date, ' ||
1834 'last_updated_by, ' ||
1835 'creation_date, ' ||
1836 'created_by ) ' ||
1837 'SELECT ''' ||
1838 p_plan_id ||''', ' ||
1839 p_parent_value_column || ', ' ||
1840 p_parent_value_pk_column ||', ' ||
1841 p_parent_level_id || ', ' ||
1842 'parent_desc_alias' ||', ' ||
1843 'sysdate, ' || FND_GLOBAL.USER_ID || ', ' ||
1844 'sysdate, ' || FND_GLOBAL.USER_ID || ' ' ||
1845 'FROM ' ||
1846 '(select distinct ' || p_parent_value_column || ', ' ||
1847 p_parent_value_pk_column || ', ' ||
1848 p_parent_level_id || ', '||
1849 p_parent_value_desc_column || ' parent_desc_alias ' || ' from ' ||
1850 p_source_table||' where plan_id = '||p_plan_id|| ') src ';
1851
1852
1853 --- fnd_file.put_line(fnd_file.log ,v_sql_stmt) ;
1854 EXECUTE IMMEDIATE v_sql_stmt;
1855
1856 END IF;
1857
1858
1859
1860 EXCEPTION
1861 when others then
1862 errbuf := substr(SQLERRM,1,150);
1863 retcode := -1;
1864 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1865
1866
1867
1868 END PROCESS_TOP_LEVEL_VALUES;
1869
1870 procedure pull_level_values_data(
1871 errbuf OUT NOCOPY VARCHAR2,
1872 retcode OUT NOCOPY VARCHAR2,
1873 p_plan_id IN NUMBER)
1874 IS
1875
1876 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE ;
1877 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_FACT_TABLE ;
1878 x_plan_id NUMBER := 0;
1879 x_level_id NUMBER := 0;
1880 v_sql_stmt varchar2(4000);
1881 g_retcode varchar2(5) := '0';
1882
1883 l_seq_num NUMBER := 0;
1884
1885 x_delete_flag VARCHAR2(1);
1886
1887
1888 Cursor Relationship is
1889 select distinct
1890 mla.plan_id,
1891 ml.level_type_code,
1892 mla.level_id,
1893 mla.parent_level_id
1894 from msd_st_level_associations_lb mla, msd_levels ml
1895 where mla.level_id = ml.level_id
1896 and ml.plan_type = 'LIABILITY'
1897 and mla.plan_id = p_plan_id ;
1898
1899 Begin
1900
1901 retcode :=0;
1902
1903 x_delete_flag := 'N';
1904
1905
1906
1907
1908
1909 /* Fetch new seq number for deleted level values */
1910 SELECT msd.msd_last_refresh_number_s.nextval
1911 INTO l_seq_num from dual;
1912
1913
1914
1915 For Relationship_Rec IN Relationship LOOP
1916
1917 if (Relationship_Rec.plan_id = x_plan_id AND Relationship_Rec.level_id = x_level_id) then
1918
1919
1920
1921 translate_level_parent_values(
1922 errbuf => errbuf,
1923 retcode => retcode,
1924 p_source_table => x_source_table,
1925 p_dest_table => x_dest_table,
1926 p_plan_id => Relationship_Rec.plan_id,
1927 p_level_id => Relationship_Rec.level_id,
1928 p_level_value_column => MSD_COMMON_UTILITIES_LB.LEVEL_VALUE_COLUMN,
1929 p_level_value_pk_column => MSD_COMMON_UTILITIES_LB.LEVEL_VALUE_PK_COLUMN,
1930 p_level_value_desc_column => MSD_COMMON_UTILITIES_LB.LEVEL_VALUE_DESC_COLUMN,
1931 p_parent_level_id => Relationship_Rec.parent_level_id,
1932 p_parent_value_column => MSD_COMMON_UTILITIES_LB.PARENT_LEVEL_VALUE_COLUMN,
1933 p_parent_value_pk_column => MSD_COMMON_UTILITIES_LB.PARENT_LEVEL_VALUE_PK_COLUMN,
1934 p_parent_value_desc_column => MSD_COMMON_UTILITIES_LB.PARENT_LEVEL_VALUE_DESC_COLUMN,
1935 p_update_lvl_table => 0,
1936
1937 p_delete_flag => x_delete_flag,
1938 p_seq_num => l_seq_num
1939 ) ;
1940
1941 --update return code
1942 if nvl(retcode,'0') <> '0' then
1943 g_retcode := retcode;
1944 end if;
1945
1946
1947 if (nvl(retcode,0) = 0 ) then
1948
1949 Delete from msd_st_level_associations_lb
1950 where plan_id = Relationship_Rec.plan_id
1951 and level_id = Relationship_Rec.level_id
1952 and parent_level_id = Relationship_Rec.parent_level_id ;
1953
1954 end if ;
1955
1956 commit ;
1957
1958 else
1959
1960 translate_level_parent_values(
1961 errbuf => errbuf,
1962 retcode => retcode,
1963 p_source_table => x_source_table,
1964 p_dest_table => x_dest_table,
1965 p_plan_id => Relationship_Rec.plan_id,
1966 p_level_id => Relationship_Rec.level_id,
1967 p_level_value_column => MSD_COMMON_UTILITIES_LB.LEVEL_VALUE_COLUMN,
1968 p_level_value_pk_column => MSD_COMMON_UTILITIES_LB.LEVEL_VALUE_PK_COLUMN,
1969 p_level_value_desc_column => MSD_COMMON_UTILITIES_LB.LEVEL_VALUE_DESC_COLUMN,
1970 p_parent_level_id => Relationship_Rec.parent_level_id,
1971 p_parent_value_column => MSD_COMMON_UTILITIES_LB.PARENT_LEVEL_VALUE_COLUMN,
1972 p_parent_value_pk_column => MSD_COMMON_UTILITIES_LB.PARENT_LEVEL_VALUE_PK_COLUMN,
1973 p_parent_value_desc_column => MSD_COMMON_UTILITIES_LB.PARENT_LEVEL_VALUE_DESC_COLUMN,
1974 p_update_lvl_table => 1,
1975 p_delete_flag => x_delete_flag,
1976 p_seq_num => l_seq_num
1977 ) ;
1978
1979
1980 -- update return code
1981 if nvl(retcode,'0') <> '0' then
1982 g_retcode := retcode;
1983 end if;
1984
1985 if (nvl(retcode,0) = 0 ) then
1986
1987
1988 Delete from msd_st_level_values_lb
1989 where plan_id = Relationship_Rec.plan_id
1990 and level_id = Relationship_Rec.level_id ;
1991
1992 Delete from msd_st_level_associations_lb
1993 where plan_id = Relationship_Rec.plan_id
1994 and level_id = Relationship_Rec.level_id
1995 and parent_level_id = Relationship_Rec.parent_level_id ;
1996
1997 end if ;
1998 commit ;
1999
2000 end if;
2001
2002 x_plan_id := Relationship_Rec.plan_id;
2003 x_level_id := Relationship_Rec.level_id;
2004
2005 End Loop ;
2006
2007
2008
2009 Delete from msd_st_level_values_lb
2010 where level_id in (
2011 select level_id
2012 from msd_levels
2013 where level_type_code = '1'
2014 and plan_type = 'LIABILITY') ;
2015
2016
2017
2018
2019 exception
2020 when others then
2021 errbuf := substr(SQLERRM,1,150);
2022 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
2023 retcode := -1 ;
2024
2025
2026 End pull_level_values_data ;
2027
2028
2029 Procedure Lock_Row(p_demand_plan_id in number) Is
2030
2031
2032 Counter NUMBER;
2033
2034 CURSOR C IS
2035 SELECT demand_plan_name
2036 FROM msd_demand_plans
2037 WHERE demand_plan_id = p_demand_plan_id
2038 FOR UPDATE of demand_plan_name NOWAIT;
2039 Recinfo C%ROWTYPE;
2040
2041 BEGIN
2042 OPEN C;
2043 FETCH C INTO Recinfo;
2044 if (C%NOTFOUND) then
2045 CLOSE C;
2046 return;
2047 end if;
2048
2049 CLOSE C;
2050
2051 EXCEPTION
2052 When APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION then
2053 IF (C% ISOPEN) THEN
2054 close C;
2055 END IF;
2056 fnd_file.put_line(fnd_file.log,'Error: Cannot Obtain a Lock on this Demand Plan ' );
2057 return;
2058 END Lock_Row ;
2059
2060
2061
2062
2063 procedure clean_liability_level_values(
2064 errbuf OUT NOCOPY VARCHAR2,
2065 retcode OUT NOCOPY VARCHAR2
2066 )
2067
2068 IS
2069 TYPE plan_id_tab IS TABLE OF msd_level_values_lb.plan_id%TYPE;
2070
2071
2072 /*This cursor return all the plan ids that are there in levels values table but do not have a plan defined for them */
2073 CURSOR c_plans
2074 is
2075 select
2076 plan_id plan_id
2077 from msd_level_values_lb
2078 minus
2079 Select
2080 liab_plan_id plan_id
2081 from msd_demand_plans ;
2082
2083 a_plan_id plan_id_tab;
2084
2085 BEGIN
2086
2087
2088
2089 OPEN c_plans;
2090 FETCH c_plans BULK COLLECT INTO a_plan_id ;
2091 CLOSE c_plans;
2092
2093
2094 FOR i IN 1..a_plan_id.count
2095
2096 loop
2097 Delete from msd_level_values_lb where plan_id = to_number (a_plan_id(i) );
2098
2099 Delete from msd_level_associations_lb where plan_id = to_number (a_plan_id(i) );
2100 commit ;
2101
2102 end loop ;
2103
2104 display_message('Deleting Level Values for following plans ', DEBUG) ;
2105
2106 FOR x_plan_rec IN c_plans
2107 LOOP
2108 display_message( to_char(x_plan_rec.plan_id ), INFORMATION ) ;
2109 END LOOP ;
2110
2111 exception
2112 when others then
2113 errbuf := substr(SQLERRM,1,150);
2114 fnd_file.put_line(fnd_file.log, 'Inside clean ');
2115 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
2116 retcode := -1 ;
2117
2118
2119
2120 END ;
2121
2122 /* This procedure will error out the concurrent program or result in warning in following conditions */
2123 /* 1. If MSC_LIABILITY_BASE_UOM Profile is null */
2124 --BUG # 4283643. No agreement will give warning instead of error.
2125 /* 2. If there is no agreement defined for the item supplier org combination in the Agrrment tables */
2126 /* 3. It will end in warning if the MSC_CATEGORY_SET_NAME is not set */
2127 /* 4. It will end in warning if the MSC: Level for Calculating Liability is not set */
2128
2129 Procedure setup_validation ( errbuf OUT NOCOPY VARCHAR2,
2130 retcode OUT NOCOPY VARCHAR2,
2131 p_plan_id IN NUMBER
2132 )
2133 IS
2134
2135 /* This cursor returns the number of agreement records in the agreement table*/
2136 CURSOR c_agreement
2137 IS
2138 select
2139 count(*) no_of_agreements
2140 from
2141 msc_item_suppliers mis ,
2142 MSC_ASL_AUTH_DETAILS maad
2143 where
2144 maad.sr_instance_id = mis.sr_instance_id
2145 and maad.organization_id = mis.organization_id
2146 and maad.supplier_id = mis.supplier_id
2147 and maad.inventory_item_id = mis.inventory_item_id
2148 and mis.plan_id = p_plan_id
2149 and maad.plan_id = -1 ;
2150
2151 x_base_uom varchar2(100) ;
2152 x_category_set_id NUMBER ;
2153 x_no_of_agreements NUMBER ;
2154
2155
2156 BEGIN
2157
2158 retcode := 0 ;
2159
2160 /* Check if Base UOM Profile is set or not , If it is not set error out the program */
2161
2162 x_base_uom := FND_PROFILE.Value('MSC_LIABILITY_BASE_UOM') ;
2163
2164 x_category_set_id := fnd_profile.value('MSC_CATEGORY_SET_NAME') ;
2165
2166 IF x_category_set_id is NULL THEN retcode := 1 ;
2167
2168 display_message('Profile MSC: Category Set for Liability Analysis is not set all the items will roll to Others Category ',WARNING) ;
2169
2170 END IF ;
2171
2172 IF x_base_uom is NULL THEN
2173
2174 display_message('Profile MSC: Liability Base UOM not set ' ,WARNING) ;
2175
2176
2177
2178 x_base_uom := MSD_COMMON_UTILITIES_LB .get_default_uom ;
2179
2180 IF x_base_uom is NULL THEN
2181 display_message( 'Could not default the Liability Base UOM ', ERROR) ;
2182 retcode := 2 ;
2183 ELSE
2184 display_message( 'Defaulting Liability Plan UOM to : '|| x_base_uom , INFORMATION) ;
2185 retcode := 1;
2186 END IF ;
2187
2188 ELSE
2189
2190 display_message( 'Setting Liability Plan UOM to : '|| x_base_uom , INFORMATION) ;
2191
2192 END IF ;
2193
2194
2195
2196
2197
2198
2199 update msd_demand_plans set base_uom = x_base_uom where liab_plan_id = p_plan_id ;
2200
2201 OPEN c_agreement ;
2202 FETCH c_agreement into x_no_of_agreements ;
2203 CLOSE c_agreement ;
2204
2205 /* BUG # 4283643-----Plan can validate without any agreement, it will give warning and make the plan INVALID.*/
2206 IF x_no_of_agreements = 0 THEN
2207 retcode := 1 ;
2208 update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
2209 display_message( 'No valid Liability agreements were found for the items in this plan.',WARNING);
2210
2211 -- display_message( 'No Demand Plan has been created for the Given Supply Plan ', INFORMATION) ;
2212
2213 END IF ;
2214
2215 EXCEPTION
2216
2217 when others then
2218 retcode := 2 ;
2219 errbuf := substr(SQLERRM,1,150);
2220 fnd_file.put_line(fnd_file.log,errbuf );
2221
2222
2223 END setup_validation ;
2224
2225 Procedure demand_plan_defn_validation
2226 ( errbuf OUT NOCOPY VARCHAR2,
2227 retcode OUT NOCOPY VARCHAR2,
2228 p_plan_id IN NUMBER
2229 )
2230 IS
2231 CURSOR get_dup_dim_output_levels IS
2232 SELECT scen.scenario_name,ml.dimension_code,count(*)
2233 FROM
2234 msd_dp_scenario_output_levels a,
2235 msd_levels ml,
2236 msd_dp_scenarios scen,
2237 msd_demand_plans mdp
2238 WHERE a.level_id = ml.level_id
2239 AND a.scenario_id = scen.scenario_id
2240 AND scen.enable_flag = 'Y'
2241 and scen.demand_plan_id = mdp.demand_plan_id
2242 and a.demand_plan_id = mdp.demand_plan_id
2243 and mdp.liab_plan_id = p_plan_id
2244 and ml.plan_type = 'LIABILITY'
2245 group by scen.scenario_name,ml.dimension_code
2246 having count(*) >1 ;
2247
2248
2249
2250 CURSOR c_lowest_time_lvl
2251 IS
2252 select m_min_tim_lvl_id from
2253 msd_demand_plans mdp
2254 where
2255 mdp.liab_plan_id =p_plan_id ;
2256
2257
2258 CURSOR c_mfg_calendar
2259 IS
2260 select mdc.calendar_type
2261 from
2262 msd_dp_calendars mdc ,
2263 msd_demand_plans mdp
2264 where
2265 mdc.demand_plan_id = mdp.demand_plan_id
2266 and mdp.liab_plan_id = p_plan_id
2267 and mdc.calendar_type = 2 ;
2268
2269
2270
2271
2272
2273
2274
2275 x_lowest_tim_lvl NUMBER ;
2276 x_cal_type NUMBER ;
2277
2278 BEGIN
2279
2280 display_message('Checking Duplicate Dimensions in Output Levels ', INFORMATION );
2281
2282 FOR get_dup_dim_output_levels_rec in get_dup_dim_output_levels
2283
2284
2285
2286 LOOP
2287
2288 display_message('Scenario '||get_dup_dim_output_levels_rec.scenario_name||' has more than one Output Level selected ' , ERROR) ;
2289
2290 retcode := 2 ;
2291
2292
2293
2294 END LOOP ;
2295
2296 OPEN c_lowest_time_lvl ;
2297 FETCH c_lowest_time_lvl into x_lowest_tim_lvl ;
2298 CLOSE c_lowest_time_lvl ;
2299
2300
2301 OPEN c_mfg_calendar ;
2302 FETCH c_mfg_calendar into x_cal_type ;
2303 CLOSE c_mfg_calendar ;
2304
2305
2306 IF x_cal_type is NULL and x_lowest_tim_lvl is NOT NULL
2307 THEN
2308 display_message(' The lowest time level for the manufacturing Calendar is defined but there is no manufacturing Calendar Attached ' , ERROR) ;
2309 retcode := 2 ;
2310 END IF ;
2311
2312
2313
2314
2315 END ;
2316
2317
2318
2319 procedure collect_mfg_time_data(
2320 errbuf OUT NOCOPY VARCHAR2,
2321 retcode OUT NOCOPY VARCHAR2 ,
2322 p_demand_plan_id IN NUMBER )
2323
2324 IS
2325
2326 BEGIN
2327
2328 delete from msd_time_lb where CALENDAR_CODE in
2329 ( select CALENDAR_CODE from msd_dp_calendars where demand_plan_id = p_demand_plan_id)
2330 ;
2331
2332 commit ;
2333
2334 insert into msd_time_lb (
2335 CALENDAR_TYPE,
2336 CALENDAR_CODE,
2337 SEQ_NUM,
2338 YEAR,
2339 YEAR_DESCRIPTION,
2340 YEAR_START_DATE,
2341 YEAR_END_DATE,
2342 QUARTER,
2343 QUARTER_DESCRIPTION,
2344 QUARTER_START_DATE,
2345 QUARTER_END_DATE,
2346 MONTH,
2347 MONTH_DESCRIPTION,
2348 MONTH_START_DATE,
2349 MONTH_END_DATE,
2350 WEEK,
2351 WEEK_DESCRIPTION,
2352 WEEK_START_DATE,
2353 WEEK_END_DATE,
2354 DAY,
2355 DAY_DESCRIPTION,
2356 WORKING_DAY )
2357 select * from msd_sr_time_lb_v
2358 where CALENDAR_CODE
2359 in ( select CALENDAR_CODE from msd_dp_calendars where demand_plan_id = p_demand_plan_id and calendar_type <> 1 ) ;
2360
2361 commit ;
2362
2363
2364
2365 EXCEPTION
2366
2367 when others then
2368 retcode := 2 ;
2369 errbuf := substr(SQLERRM,1,150);
2370 fnd_file.put_line(fnd_file.log,errbuf );
2371
2372
2373 END ;
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393 END ;
[Home] [Help]