[Home] [Help]
PACKAGE BODY: APPS.MSD_COLLECT_LEVEL_VALUES
Source
1 PACKAGE BODY MSD_COLLECT_LEVEL_VALUES AS
2 /* $Header: msdclvlb.pls 120.4 2006/09/18 05:53:19 sjagathe noship $ */
3
4 --v_launched_from NUMBER := to_number(NULL); --jarorad
5
6 /* Private Procedure */
7
8 procedure log_debug( pBUFF in varchar2)
9 is
10 begin
11
12 if C_MSC_DEBUG = 'Y' then
13 fnd_file.put_line( fnd_file.log, pBUFF);
14 else
15 null;
16 --dbms_output.put_line( pBUFF);
17 end if;
18
19 end log_debug;
20
21 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
22 IS
23 BEGIN
24
25 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
26
27 END LOG_MESSAGE;
28
29 Procedure Delete_duplicate(p_instance_id in number, p_dest_table in varchar2);
30
31 Procedure Delete_Childless_Parent_All ( errbuf OUT NOCOPY VARCHAR2,
32 retcode OUT NOCOPY VARCHAR2,
33 p_instance in VARCHAR2);
34
35 Procedure Delete_Childless_Parent (
36 errbuf OUT NOCOPY VARCHAR2,
37 retcode OUT NOCOPY VARCHAR,
38 p_instance_id in number,
39 p_level_id in number);
40 -- p_dest_table in varchar2); Bug# 4919130 - Always delete childless parents from staging table.
41
42
43
44 /* Public Procedures */
45
46 /* The wrapper program that is called from the concurrent program */
47 procedure collect_data(
48 errbuf OUT NOCOPY VARCHAR2,
49 retcode OUT NOCOPY VARCHAR2,
50 p_instance_id IN NUMBER,
51 p_collection_type IN VARCHAR2,
52 p_collection_var IN VARCHAR2) IS
53 -- ,p_launched_from IN NUMBER DEFAULT NULL) IS --jarorad
54
55
56
57 begin
58 retcode := 0 ;
59
60 --v_launched_from := nvl(p_launched_from,C_DP); --jarorad
61
62 /* Check and push setup parameters if it is not done so previously */
63 MSD_PUSH_SETUP_DATA.chk_push_setup( errbuf,
64 retcode,
65 p_instance_id);
66 IF (nvl(retcode, 0) <> 0) THEN
67 return;
68 END IF;
69
70
71 IF ( nvl(p_collection_type, MSD_COMMON_UTILITIES.COLLECT_ALL) =
72 MSD_COMMON_UTILITIES.COLLECT_ALL ) then
73
74 collect_all_data(
75 errbuf => errbuf,
76 retcode => retcode,
77 p_instance_id => p_instance_id);
78
79 ELSIF ( p_collection_type = MSD_COMMON_UTILITIES.COLLECT_DP ) then
80
81 collect_demand_plan_data(
82 errbuf => errbuf,
83 retcode => retcode,
84 p_instance_id => p_instance_id,
85 p_demand_plan_id => to_number(p_collection_var) );
86
87
88 elsif ( p_collection_type = MSD_COMMON_UTILITIES.COLLECT_DIMENSION ) then
89
90 collect_dimension_data(
91 errbuf => errbuf,
92 retcode => retcode,
93 p_instance_id => p_instance_id,
94 p_dimension_code => p_collection_var);
95
96
97 elsif ( p_collection_type = MSD_COMMON_UTILITIES.COLLECT_HIERARCHY ) then
98
99 collect_hierarchy_data(
100 errbuf => errbuf,
101 retcode => retcode,
102 p_instance_id => p_instance_id,
103 p_hierarchy_id => to_number(p_collection_var) );
104
105 elsif ( p_collection_type = MSD_COMMON_UTILITIES.COLLECT_LEVEL ) then
106
107 collect_level_data(
108 errbuf => errbuf,
109 retcode => retcode,
110 p_instance_id => p_instance_id,
111 p_level_id => to_number(p_collection_var));
112
113 end if ;
114
115
116
117 /* Added logic to delete duplicate data */
118 Delete_duplicate(p_instance_id, MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE);
119
120 /* Delete duplicate level association from staging table */
121 Delete_duplicate_lvl_assoc(errbuf, retcode, p_instance_id);
122
123 /* esubrama - Item Supersession Collection
124 msd_item_relationships_pkg.collect_supersession_data (
125 errbuf => errbuf,
126 retcode => retcode,
127 p_instance_id => p_instance_id );
128 */
129
130 /* Analyze staging table after collection */
131 MSD_ANALYZE_TABLES.analyze_table(null,1);
132
133
134 /* IF this is one step collection then
135 call PULL internally and execute 2 step collection */
136
137 IF (fnd_profile.value('MSD_ONE_STEP_COLLECTION') = 'Y') THEN
138 msd_pull_level_values.pull_level_values_data( errbuf => errbuf,
139 retcode => retcode,
140 p_comp_refresh => 1);
141 END IF;
142
143 Commit;
144
145 EXCEPTION
146
147 WHEN others THEN
148 BEGIN
149 Delete_duplicate(p_instance_id ,
150 MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE);
151 Delete_duplicate_lvl_assoc(errbuf, retcode, p_instance_id);
152 COMMIT;
153 EXCEPTION
154 WHEN others THEN
155 retcode := -1;
156 errbuf := substr(SQLERRM,1,150);
157 END;
158 retcode := -1 ;
159 errbuf := substr(SQLERRM,1,150);
160
161
162 End collect_data ;
163
164 procedure collect_level_parent_data(
165 errbuf OUT NOCOPY VARCHAR2,
166 retcode OUT NOCOPY VARCHAR2,
167 p_instance_id IN NUMBER,
168 p_level_id IN NUMBER,
169 p_parent_level_id IN NUMBER,
170 p_update_lvl_table IN NUMBER) IS
171 x_statement VARCHAR2(2000) := NULL ;
172 x_view_name VARCHAR2(40) := NULL ;
173 x_dblink VARCHAR2(128);
174 x_direct_load_profile boolean;
175 x_source_table VARCHAR2(50) ;
176 x_dest_table varchar2(50) ;
177 v_lvl_type varchar2(1);
178 v_dest_ass_table varchar2(240) ;
179 v_sql_stmt varchar2(4000);
180
181 /* OPM Comment Rajesh Patangya */
182 x_delete_flag varchar2(1) := 'Y' ;
183 o_source_table VARCHAR2(50) := NULL ;
184 o_dblink varchar2(128);
185 o_icode varchar2(128);
186 o_retcode number;
187 o_instance_type number;
188 o_dgmt number;
189 o_apps_ver number;
190 o_dimension_code VARCHAR2(10) := 'XXXX' ;
191
192
193 /* DWK new variable for error report */
194 p_level_name VARCHAR2(30);
195 p_parent_level_name VARCHAR2(30);
196 p_hierarchy_name VARCHAR2(30);
197
198 p_seq_num NUMBER;
199
200 /************************************************************************
201 Cursor to get distinct relationship view and the corresponding columns
202 *************************************************************************/
203 /* DWK Include hierarchy_id in this cursor. We need hierarchy_id info
204 for reporting error when there is no relationship_view defined */
205 Cursor Relationship (p_level_id in number, p_parent_level_id in number) is
206 select distinct
207 hierarchy_id,
208 relationship_view,
209 level_value_column,
210 level_value_pk_column,
211 nvl(level_value_desc_column,level_value_column) level_value_desc_column,
212 parent_value_column,
213 parent_value_pk_column,
214 nvl(parent_value_desc_column, parent_value_column) parent_value_desc_column
215 from msd_hierarchy_levels
216 where level_id = p_level_id
217 and parent_level_id = p_parent_level_id
218 and plan_type is null; --vinekuma
219
220 g_retcode varchar2(5) := '0';
221
222 Begin
223
224 log_debug('In procedure COLLECT_LEVEL_PARENT_DATA');
225
226 /***********************************************************
227 * 1. Get the DB Link for the Instance ID
228 * 2. Get the Profile MSD_ONE_STEP_COLLECTION to find out
229 * which table to insert into - whether the staging
230 * table or the fact table
231 * 3. Get the view that hold the association and the
232 * corresponding column names.
233 * 4. Set the Save Point and delete the already existing
234 * data in the level values are the staging table.
235 * 5. Insert the new values from the association views
236 * 6. Commit
237 ************************************************************/
238
239 retcode :=0;
240
241
242 msd_common_utilities.get_db_link(p_instance_id, x_dblink, retcode);
243 if (retcode = -1) then
244 retcode :=-1;
245 errbuf := 'Error while getting db_link';
246 --dbms_output.put_line('Error while getting db_link');
247 return;
248 end if;
249
250
251 /* OPM Comment By Rajesh Patangya */
252 msd_common_utilities.get_inst_info(p_instance_id, o_dblink, o_icode,
253 o_apps_ver, o_dgmt, o_instance_type, o_retcode) ;
254 if (o_retcode = -1) then
255 retcode :=-1;
256 errbuf := 'Error while getting instance_info';
257 return;
258 end if;
259
260
261 /* DWK Always 2 step collection */
262 x_dest_table := MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE ;
263 v_dest_ass_table := MSD_COMMON_UTILITIES.LEVEL_ASSOC_STAGING_TABLE;
264
265
266 /* DWK Relationship LOOP */
267 For Relationship_Rec IN Relationship(p_level_id, p_parent_level_id) LOOP
268 log_debug('In Cursor Relationship');
269
270 /* DWK Check whether relationship_view is NULL or not.
271 IF NULL then give WARNING message and go to the next cursor.
272 Do not try to translate level values if the relationship_view is NULL */
273
274 /* DWK Begining of IF 1 */
275 IF ( Relationship_Rec.relationship_view IS NULL ) THEN
276
277 log_debug('Error Condition : Relationship View is Null');
278 SELECT hierarchy_name INTO p_hierarchy_name
279 FROM msd_hierarchies
280 WHERE hierarchy_id = Relationship_Rec.hierarchy_id
281 AND plan_type is null; --vinekuma
282
283 p_level_name := MSD_COMMON_UTILITIES.get_level_name(p_level_id);
284 p_parent_level_name := MSD_COMMON_UTILITIES.get_level_name(p_parent_level_id);
285
286 fnd_file.put_line(fnd_file.log, ' ');
287 fnd_file.put_line(fnd_file.log, 'Relationship view is not defined for ' ||
288 'Hierarchy : '|| p_hierarchy_name || '. (No Data Collected.)');
289 fnd_file.put_line(fnd_file.log, ' Level : ' || p_level_name );
290 fnd_file.put_line(fnd_file.log, ' Parent Level : ' || p_parent_level_name );
291
292 /* DWK. IF we have relationship_view name then proceed the following codes */
293 ELSE
294
295 x_source_table := Relationship_Rec.relationship_view || x_dblink ;
296
297 /* OPM Comment Rajesh Patangya */
298 IF (o_instance_type in (2,4) AND o_apps_ver = 3) THEN
299 msd_common_utilities.get_dimension_code (p_level_id,
300 o_dimension_code,
301 o_retcode );
302 IF (o_retcode = -1) THEN
303 log_debug('Error Condition : Error while getting dimension Code');
304 retcode := -1;
305 errbuf := 'Error while getting dimension code';
306 return;
307 END IF;
308 END IF;
309
310 /* For a process 11i instance, call translate to extract */
311 /* process organizations only create source table name */
312
313 IF (o_dimension_code = 'ORG' and o_instance_type = 2 AND o_apps_ver = 3) THEN
314 o_source_table := REPLACE(x_source_table , 'MSD','GMP') ;
315 x_source_table := o_source_table;
316
317 END IF;
318
319 log_debug('Calling procedure TRANSLATE_LEVEL_PARENT_VALUE');
320 MSD_TRANSLATE_LEVEL_VALUES.translate_level_parent_values(
321 errbuf => errbuf,
322 retcode => retcode,
323 p_source_table => x_source_table,
324 p_dest_table => x_dest_table,
325 p_instance_id => p_instance_id,
326 p_level_id => p_level_id,
327 p_level_value_column => Relationship_Rec.level_value_column,
328 p_level_value_pk_column => Relationship_Rec.level_value_pk_column,
329 p_level_value_desc_column => Relationship_Rec.level_value_desc_column,
330 p_parent_level_id => p_parent_level_id,
331 p_parent_value_column => Relationship_Rec.parent_value_column,
332 p_parent_value_pk_column => Relationship_Rec.parent_value_pk_column,
333 p_parent_value_desc_column => Relationship_Rec.parent_value_desc_column,
334 p_update_lvl_table => p_update_lvl_table,
335 /* OPM Comment Rajesh Patangya */
336 p_delete_flag => x_delete_flag,
337 p_seq_num => p_seq_num );
338 -- ,p_launched_from => v_launched_from); --jarorad
339
340 --update return code
341 IF retcode <> '0' THEN
342 g_retcode := retcode;
343 END IF;
344
345 /* For a discrete process 11i instance, call translate again to extract */
346 /* process organizations only */
347 /* DWK Beginning of IF 2 */
348 IF (o_dimension_code = 'ORG' AND o_instance_type = 4 AND o_apps_ver = 3) THEN
349 o_source_table := REPLACE(x_source_table , 'MSD','GMP') ;
350 x_source_table := o_source_table ;
351 x_delete_flag := 'N' ;
352
353 log_debug('Calling procedure TRANSLATE_LEVEL_PARENT_VALUE for process 11i instance, for process orgs ');
354 MSD_TRANSLATE_LEVEL_VALUES.translate_level_parent_values(
355 errbuf => errbuf,
356 retcode => retcode,
357 p_source_table => x_source_table,
358 p_dest_table => x_dest_table,
359 p_instance_id => p_instance_id,
360 p_level_id => p_level_id,
361 p_level_value_column => Relationship_Rec.level_value_column,
362 p_level_value_pk_column => Relationship_Rec.level_value_pk_column,
363 p_level_value_desc_column => Relationship_Rec.level_value_desc_column,
364 p_parent_level_id => p_parent_level_id,
365 p_parent_value_column => Relationship_Rec.parent_value_column,
366 p_parent_value_pk_column => Relationship_Rec.parent_value_pk_column,
367 p_parent_value_desc_column => Relationship_Rec.parent_value_desc_column,
368 p_update_lvl_table => p_update_lvl_table,
369 /* OPM Comment Rajesh Patangya */
370 p_delete_flag => x_delete_flag,
371 p_seq_num => p_seq_num );
372 -- ,p_launched_from => v_launched_from); --jarorad
373
374 /* OPM Comment Rajesh Patangya */
375 o_source_table := NULL ;
376
377 --update return code
378 IF retcode <> '0' THEN
379 g_retcode := retcode;
380 END IF;
381
382 END IF; /* DWK End of IF 2 */
383 END IF; /* DWK End of IF 1 */
384 End LOOP ; /* DWK End of Relationship LOOP */
385
386 retcode := g_retcode;
387
388 log_debug('Exiting procedure COLLECT_LEVEL_PARENT_DATA');
389
390 exception
391
392 when others then
393 retcode := -1 ;
394 errbuf := substr(SQLERRM,1,150);
395 -- insert into msd_test values('Error: ' || errbuf) ;
396
397 End collect_level_parent_data ;
398
399
400 procedure collect_level_data(
401 errbuf OUT NOCOPY VARCHAR2,
402 retcode OUT NOCOPY VARCHAR2,
403 p_instance_id IN NUMBER,
404 p_level_id IN NUMBER ) IS
405 /* lvl_table needs to be updated only once - this is the indicator */
406 x_update_lvl_table NUMBER := 1;
407 g_retcode varchar2(5) := '0';
408
409 /******************************************************
410 Cursor to get distinct Level, Parent Combinations
411 ******************************************************/
412 Cursor Level_Parent(p_level_id IN NUMBER) is
413 select distinct level_id, parent_level_id, level_type_code
414 from msd_hierarchy_levels_v
415 where level_id = p_level_id
416 order by level_type_code, level_id;
417 Begin
418
419 log_debug('In procedure COLLECT_LEVEL_DATA');
420 log_debug('Level ID :'||p_level_id);
421
422
423 For Level_Parent_Rec IN Level_Parent(p_level_id) LOOP
424
425 log_debug('Parent Level ID :'||Level_Parent_Rec.parent_level_id);
426 collect_level_parent_data(
427 errbuf => errbuf,
428 retcode => retcode,
429 p_instance_id => p_instance_id,
430 p_level_id => p_level_id,
431 p_parent_level_id => Level_Parent_Rec.parent_level_id,
432 p_update_lvl_table => x_update_lvl_table);
433
434 x_update_lvl_table := 0;
435
436 --update return code
437 if retcode <> '0' then
438 g_retcode := retcode;
439 end if;
440
441 end loop;
442
443 /* zia bug #1610855: Fix parentless children */
444 fix_orphans(p_instance_id, p_level_id,
445 MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE,
446 MSD_COMMON_UTILITIES.LEVEL_ASSOC_STAGING_TABLE,
447 null);
448
449 /* Bug# 5530511 - Delete_Childless_Parent_All should be called after
450 * the call to fix_orphans
451 */
452 Delete_Childless_Parent_All ( errbuf, retcode, p_instance_id);
453
454 retcode := g_retcode;
455
456 commit ;
457
458 /* esubrama - Item Supersession Collection */
459 if (p_level_id = 1) then
460
461 msd_item_relationships_pkg.collect_supersession_data (
462 errbuf => errbuf,
463 retcode => retcode,
464 p_instance_id => p_instance_id );
465 end if;
466
467 log_debug('Exiting procedure COLLECT_LEVEL_DATA');
468
469 End collect_level_data ;
470
471 procedure collect_hierarchy_data(
472 errbuf OUT NOCOPY VARCHAR2,
473 retcode OUT NOCOPY VARCHAR2,
474 p_instance_id IN NUMBER,
475 p_hierarchy_id IN NUMBER) IS
476 x_level_id NUMBER := 0;
477 /******************************************************
478 Cursor to get Distinct Level Parent Combination in
479 a hierarchy
480 ******************************************************/
481 Cursor Hierarchy_Levels(p_hierarchy_id IN NUMBER) is
482 select level_id, parent_level_id, level_type_code
483 from msd_hierarchy_levels_v
484 where hierarchy_id = p_hierarchy_id
485 order by level_type_code, level_id;
486
487 /* Cursor to get distinct levels in the hierarchy */
488 Cursor Level_Cursor(p_hierarchy_id IN NUMBER) is
489 select distinct level_id, level_type_code
490 from msd_hierarchy_levels_v
491 where hierarchy_id = p_hierarchy_id
492 order by level_type_code, level_id;
493
494 Cursor hierarcy_dimension(p_hierarchy_id IN NUMBER) is
495 select distinct OWNING_DIMENSION_CODE
496 from msd_hierarchy_levels_v
497 where hierarchy_id = p_hierarchy_id;
498
499 l_dim_code varchar(5) := null;
500 g_retcode varchar2(5) := '0';
501
502 Begin
503
504 For Hierarchy_Levels_Rec IN Hierarchy_Levels (p_hierarchy_id) LOOP
505
506 if (x_level_id = Hierarchy_Levels_Rec.level_id) then
507
508 collect_level_parent_data(
509 errbuf => errbuf,
510 retcode => retcode,
511 p_instance_id => p_instance_id,
512 p_level_id => Hierarchy_Levels_Rec.level_id,
513 p_parent_level_id => Hierarchy_Levels_Rec.parent_level_id,
514 p_update_lvl_table => 0);
515
516 else
517
518 collect_level_parent_data(
519 errbuf => errbuf,
520 retcode => retcode,
521 p_instance_id => p_instance_id,
522 p_level_id => Hierarchy_Levels_Rec.level_id,
523 p_parent_level_id => Hierarchy_Levels_Rec.parent_level_id,
524 p_update_lvl_table => 1);
525
526 end if;
527
528
529 x_level_id := Hierarchy_Levels_Rec.level_id;
530
531 --update return code
532 if retcode <> '0' then
533 g_retcode := retcode;
534 end if;
535
536 end loop ;
537
538 For Level_Rec IN Level_Cursor (p_hierarchy_id) LOOP
539 /* zia bug #1610855: Fix parentless children */
540 fix_orphans(p_instance_id, Level_Rec.level_id,
541 MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE,
542 MSD_COMMON_UTILITIES.LEVEL_ASSOC_STAGING_TABLE,
543 p_hierarchy_id);
544 end loop;
545
546 /* Bug# 5530511 - Delete_Childless_Parent_All should be called after
547 * the call to fix_orphans
548 */
549 Delete_Childless_Parent_ALL ( errbuf, retcode, p_instance_id);
550
551
552
553 /* esubrama - Item Supersession Collection */
554 open hierarcy_dimension(p_hierarchy_id);
555 fetch hierarcy_dimension into l_dim_code;
556 close hierarcy_dimension;
557
558 if (l_dim_code = 'PRD') then
559
560 msd_item_relationships_pkg.collect_supersession_data (
561 errbuf => errbuf,
562 retcode => retcode,
563 p_instance_id => p_instance_id );
564 end if;
565
566 retcode := g_retcode;
567
568 End collect_hierarchy_data ;
569
570
571 procedure collect_dimension_data(
572 errbuf OUT NOCOPY VARCHAR2,
573 retcode OUT NOCOPY VARCHAR2,
574 p_instance_id IN NUMBER,
575 p_dimension_code IN VARCHAR2) IS
576
577 x_level_id NUMBER := 0;
578 /******************************************************
579 Cursor to get distinct level parent combinations
580 in a dimension
581 ******************************************************/
582 Cursor Dim_Level_Parent(p_dimension_code IN VARCHAR2) is
583 select distinct level_id, parent_level_id, level_type_code
584 from msd_hierarchy_levels_v
585 where owning_dimension_code = p_dimension_code
586 order by level_type_code, level_id;
587
588 /* Cursor to get levels alone */
589 Cursor Level_Cursor(p_dimension_code IN VARCHAR2) is
590 select distinct level_id, level_type_code
591 from msd_hierarchy_levels_v
592 where owning_dimension_code = p_dimension_code
593 order by level_type_code, level_id;
594
595 g_retcode varchar2(5) := '0';
596
597 Begin
598
599 For Dim_Level_Parent_Rec IN Dim_Level_Parent (p_dimension_code) LOOP
600
601
602 if (x_level_id = Dim_Level_Parent_Rec.level_id) then
603
604 collect_level_parent_data(
605 errbuf => errbuf,
606 retcode => retcode,
607 p_instance_id => p_instance_id,
608 p_level_id => Dim_Level_Parent_Rec.level_id,
609 p_parent_level_id => Dim_Level_Parent_Rec.parent_level_id,
610 p_update_lvl_table => 0);
611
612 else
613 collect_level_parent_data(
614 errbuf => errbuf,
615 retcode => retcode,
616 p_instance_id => p_instance_id,
617 p_level_id => Dim_Level_Parent_Rec.level_id,
618 p_parent_level_id => Dim_Level_Parent_Rec.parent_level_id,
619 p_update_lvl_table => 1);
620
621 end if;
622
623 x_level_id := Dim_Level_Parent_Rec.level_id;
624
625
626 --update return code
627 if retcode <> '0' then
628 g_retcode := retcode;
629 end if;
630
631 end loop ;
632
633 For Level_Rec IN Level_Cursor(p_dimension_code) LOOP
634
635 /* zia bug #1610855: Fix parentless children */
636 fix_orphans(p_instance_id, Level_Rec.level_id,
637 MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE,
638 MSD_COMMON_UTILITIES.LEVEL_ASSOC_STAGING_TABLE,
639 null);
640 end loop;
641
642 /* Bug# 5530511 - Delete_Childless_Parent_All should be called after
643 * the call to fix_orphans
644 */
645 Delete_Childless_Parent_ALL ( errbuf, retcode, p_instance_id);
646
647
648 /* esubrama - Item Supersession Collection */
649 if (p_dimension_code = 'PRD') then
650
651 msd_item_relationships_pkg.collect_supersession_data (
652 errbuf => errbuf,
653 retcode => retcode,
654 p_instance_id => p_instance_id );
655 end if;
656
657 retcode := g_retcode;
658
659 End collect_dimension_data ;
660
661
662 procedure collect_dp_dimension_data(
663 errbuf OUT NOCOPY VARCHAR2,
664 retcode OUT NOCOPY VARCHAR2,
665 p_instance_id IN NUMBER,
666 p_demand_plan_id IN NUMBER,
667 p_dimension_code IN VARCHAR2) IS
668 /******************************************************
669 Cursor to get distinct owning dimensions in a dp dimension
670 ******************************************************/
671 Cursor Dp_Dim_Dimensions(p_dimension_code IN VARCHAR2) is
672 select distinct owning_dimension_code
673 from msd_dp_hierarchies_v
674 where demand_plan_id = p_demand_plan_id
675 and dp_dimension_code = p_dimension_code ;
676
677 g_retcode varchar2(5) := '0';
678
679 Begin
680
681 For Dp_Dim_Dimensions_Rec IN Dp_Dim_Dimensions (p_dimension_code) LOOP
682
683 collect_dimension_data(
684 errbuf => errbuf,
685 retcode => retcode,
686 p_instance_id => p_instance_id,
687 p_dimension_code =>
688 Dp_Dim_Dimensions_Rec.owning_dimension_code);
689
690 --update return code
691 if retcode <> '0' then
692 g_retcode := retcode;
693 end if;
694
695 end loop ;
696
697 retcode := g_retcode;
698
699 End collect_dp_dimension_data ;
700
701
702 procedure collect_demand_plan_data(
703 errbuf OUT NOCOPY VARCHAR2,
704 retcode OUT NOCOPY VARCHAR2,
705 p_instance_id IN NUMBER,
706 p_demand_plan_id IN NUMBER) IS
707
708 /******************************************************
709 Cursor to get DP_Dimensions in a demand plan
710 ******************************************************/
711 Cursor Dp_Dimensions(p_demand_plan_id IN NUMBER) is
712 select distinct dp_dimension_code
713 from msd_dp_hierarchies_v
714 where demand_plan_id = p_demand_plan_id ;
715
716 g_retcode varchar2(5) := '0';
717
718 Begin
719
720 For Dp_Dimensions_Rec IN Dp_Dimensions (p_demand_plan_id) LOOP
721
722 collect_dp_dimension_data(
723 errbuf => errbuf,
724 retcode => retcode,
725 p_instance_id => p_instance_id,
726 p_demand_plan_id => p_demand_plan_id,
727 p_dimension_code => Dp_DimensionS_Rec.dp_dimension_code);
728
729
730 --update return code
731 if retcode <> '0' then
732 g_retcode := retcode;
733 end if;
734
735 end loop ;
736
737 retcode := g_retcode;
738
739 End collect_demand_plan_data ;
740
741
742 procedure collect_all_data(
743 errbuf OUT NOCOPY VARCHAR2,
744 retcode OUT NOCOPY VARCHAR2,
745 p_instance_id IN NUMBER) IS
746 /******************************************************
747 Cursor to get ALL Dimensions
748 ******************************************************/
749 Cursor Dimensions is
750 select lookup_code
751 from fnd_lookup_values_vl
752 where lookup_type = 'MSD_DIMENSIONS' ;
753
754 g_retcode varchar2(5) := '0';
755
756 Begin
757
758 For Dimensions_Rec IN Dimensions LOOP
759
760 collect_dimension_data(
761 errbuf => errbuf,
762 retcode => retcode,
763 p_instance_id => p_instance_id,
764 p_dimension_code => Dimensions_Rec.lookup_code);
765
766 --update return code
767 if retcode <> '0' then
768 g_retcode := retcode;
769 end if;
770
771 /* DWK. Commit for every Dimension, so user can see the progress */
772 commit;
773
774 end loop ;
775
776 retcode := g_retcode;
777
778 End collect_all_data ;
779
780
781 /**************************************************************
782 Procedure to fix parentless level values.
783
784 Should only be called after all level values for a level have
785 been inserted in the desination table.
786 ***************************************************************/
787 procedure fix_orphans(p_instance_id in number,
788 p_level_id in number,
789 p_dest_table in varchar2,
790 p_dest_ass_table in varchar2,
791 p_hierarchy_id in number) IS
792
793 Cursor Parent_Levels(p_lvl_id IN NUMBER) is
794 select distinct parent_level_id
795 from msd_hierarchy_levels
796 where level_id = p_lvl_id
797 and hierarchy_id = nvl(p_hierarchy_id, hierarchy_id);
798
799 v_sql_stmt varchar2(4000);
800 v_parent_level_id number;
801 v_other_pk VARCHAR2(240);
802
803 begin
804 /* zia bug #1610855: Associate parentless
805 values with the 'Other' level value at the parent level
806 */
807
808 For Parent_Levels_Rec IN Parent_Levels (p_level_id) LOOP
809 /* get pk of 'Other'
810 Note that even though this level value has not yet been inserted
811 into the destination table, it is okay to add an association
812 because the value itself will certainly be added in the next
813 pass over the parent level.
814 */
815 v_other_pk := to_char(msd_sr_util.get_null_pk);
816 v_parent_level_id := Parent_Levels_Rec.parent_level_id;
817
818 /* Insert association for orphans */
819 /* VM Logic : Find orphans using MINUS set between
820 records in level_values for Level in consideration and
821 records in level_Association for level and parent level in consideration
822 */
823
824 v_sql_stmt := 'insert into ' || p_dest_ass_table || ' (' ||
825 'instance, ' ||
826 'level_id, ' ||
827 'sr_level_pk, ' ||
828 'parent_level_id, ' ||
829 'sr_parent_level_pk, ' ||
830 'last_update_date, ' ||
831 'last_updated_by, ' ||
832 'creation_date, ' ||
833 'created_by ) ' ||
834 'select ''' ||
835 p_instance_id ||''', ' ||
836 p_level_id || ', ' ||
837 'sr_level_pk, ' ||
838 v_parent_level_id || ', ''' ||
839 v_other_pk || ''', ' ||
840 'sysdate, ' ||
841 FND_GLOBAL.USER_ID || ', ' ||
842 'sysdate, ' ||
843 FND_GLOBAL.USER_ID ||
844 ' from ' || p_dest_table || ' mlv ' ||
845 ' where level_id = ' || p_level_id ||
846 ' and instance = ' || p_instance_id ||
847 ' minus ' ||
848 'select ''' ||
849 p_instance_id ||''', ' ||
850 p_level_id || ', ' ||
851 'sr_level_pk, ' ||
852 v_parent_level_id || ', ''' ||
853 v_other_pk || ''', ' ||
854 'sysdate, ' ||
855 FND_GLOBAL.USER_ID || ', ' ||
856 'sysdate, ' ||
857 FND_GLOBAL.USER_ID ||
858 ' from ' || p_dest_ass_table || ' amlv ' ||
859 ' where level_id = ' || p_level_id ||
860 ' and instance = ' || p_instance_id ||
861 ' and parent_level_id = ' || v_parent_level_id;
862
863 -- insert into msd_test values(v_sql_stmt) ;
864 EXECUTE IMMEDIATE v_sql_stmt ;
865 END LOOP;
866 END fix_orphans;
867
868
869 FUNCTION get_dest_table return varchar2 IS
870 x_direct_load_profile boolean;
871 BEGIN
872 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
873
874 if (x_direct_load_profile) then
875 return MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE ;
876 else
877 return MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE ;
878 end if;
879 END get_dest_table;
880
881
882 FUNCTION get_assoc_table return varchar2 IS
883 x_direct_load_profile boolean;
884 BEGIN
885 x_direct_load_profile := (fnd_profile.value('MSD_ONE_STEP_COLLECTION')='Y');
886
887 if (x_direct_load_profile) then
888 return MSD_COMMON_UTILITIES.LEVEL_ASSOC_FACT_TABLE ;
889 else
890 return MSD_COMMON_UTILITIES.LEVEL_ASSOC_STAGING_TABLE;
891 end if;
892 END get_assoc_table;
893
894
895 Procedure Delete_duplicate(p_instance_id in number, p_dest_table in varchar2) is
896
897 lb_FetchComplete BOOLEAN := FALSE;
898 ln_rows_to_fetch Number := nvl(TO_NUMBER(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
899
900 TYPE CharTblTyp IS TABLE OF VARCHAR2(240);
901 TYPE NumTblTyp IS TABLE OF NUMBER;
902
903 lb_level_id NumTblTyp;
904 lb_sr_level_pk CharTblTyp;
905 lb_system_attribute1 CharTblTyp;
906 lb_system_attribute2 CharTblTyp;
907 lb_dp_enabled_flag NumTblTyp;
908
909 Cursor c_Update_Level_Values
910 is
911 select level_id, sr_level_pk,system_attribute1,system_attribute2,dp_enabled_flag
912 from msd_st_level_values a
913 where a.instance = p_instance_id
914 and rowid = ( select max(rowid)
915 from msd_st_level_values b
916 where a.instance = b.instance
917 and a.level_id = b.level_id
918 and a.sr_level_pk = b.sr_level_pk
919 and b.system_attribute1 is not null); -- assuming here that if there exist more than one record
920 -- with system_attribute1 as not null for same level_value
921 -- (i.e same level_id, and sr_level_pk), the the value for
922 -- system_attribute1, system_attribute2 and dp_enabled_flag
923 -- will be same, in all such records
924 Begin
925
926 /* BUG# 5383368 - SOP and EOL code cleanup
927
928 -- This piece of code is written as part of SOP Project.We are here populating the all additional
929 -- level value attributes collected as part of the SOP.
930 -- SOP code changes required to chnage the few hierarchies, whereas certain hierarchies are not got changed
931 -- for the level values collected at the same level_id.Now, since delete duplicate code may delete the
932 -- record which contains the relevant level_value attributes collected for SOP.
933
934
935 OPEN c_Update_Level_Values;
936 IF (c_Update_Level_Values%ISOPEN) THEN
937
938 LOOP
939
940 IF (lb_FetchComplete) THEN
941 EXIT;
942 END IF;
943
944 FETCH c_Update_Level_Values BULK COLLECT INTO
945 lb_level_id,
946 lb_sr_level_pk,
947 lb_system_attribute1,
948 lb_system_attribute2,
949 lb_dp_enabled_flag
950 LIMIT ln_rows_to_fetch;
951
952
953 IF (c_Update_Level_Values%NOTFOUND) THEN
954 lb_FetchComplete := TRUE;
955 END IF;
956
957
958 if c_Update_Level_Values%ROWCOUNT > 0 then
959
960 FORALL j IN lb_level_id.FIRST..lb_level_id.LAST
961 update msd_st_level_values
962 set system_attribute1 = lb_system_attribute1(j),
963 system_attribute2 = lb_system_attribute2(j),
964 dp_enabled_flag = lb_dp_enabled_flag(j)
965 where instance = p_instance_id
966 and level_id = lb_level_id(j)
967 and sr_level_pk = lb_sr_level_pk(j)
968 and system_attribute1 is null;
969 --and system_attribute2 is null
970 --and dp_enabled_flag is null ;
971
972 end if;
973
974 END LOOP; --LOOP
975
976 END IF; --IF (c_Update_Level_Values%ISOPEN) THEN
977 CLOSE c_Update_Level_Values;
978
979 */
980
981 /* This procedure deletes duplicate records from staging level_values
982 Key - Instance + Level_Id + SR_LEVEL_PK
983 */
984
985 if p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE then
986 delete from msd_st_level_values a where
987 a.instance = p_instance_id and
988 rowid <> (select max(rowid) from msd_st_level_values b
989 where a.instance = b.instance
990 and a.level_id = b.level_id
991 and a.sr_level_pk = b.sr_level_pk);
992 end if;
993
994 End;
995
996 Procedure Delete_duplicate_lvl_assoc( errbuf OUT NOCOPY VARCHAR2,
997 retcode OUT NOCOPY VARCHAR2,
998 p_instance_id in number) is
999
1000 cursor c_duplicate is
1001 select level_id, sr_level_pk, parent_level_id
1002 from msd_st_level_associations
1003 where instance = p_instance_id
1004 group by level_id, sr_level_pk, parent_level_id
1005 having count(*) > 1;
1006
1007 TYPE level_id_tab is table of msd_st_level_associations.level_id%TYPE;
1008 TYPE sr_level_pk_tab IS TABLE OF msd_st_level_associations.sr_level_pk%TYPE;
1009
1010 a_child_level_id level_id_tab;
1011 a_parent_level_id level_id_tab;
1012 a_sr_level_pk sr_level_pk_tab;
1013
1014
1015 Begin
1016
1017 /* This procedure deletes duplicate records from staging level association
1018 Key - Instance + Child_Level_Id + SR_LEVEL_PK + Parent_Level_ID
1019 */
1020
1021 OPEN c_duplicate;
1022 FETCH c_duplicate BULK COLLECT INTO a_child_level_id, a_sr_level_pk, a_parent_level_id ;
1023 CLOSE c_duplicate;
1024
1025 IF (a_child_level_id.exists(1)) THEN
1026 FOR i IN a_child_level_id.FIRST..a_child_level_id.LAST LOOP
1027 delete from msd_st_level_associations a where
1028 a.instance = p_instance_id and
1029 a.level_id = a_child_level_id(i) and
1030 a.sr_level_pk = a_sr_level_pk(i) and
1031 a.parent_level_id = a_parent_level_id(i) and
1032 rowid <> (select rowid from msd_st_level_associations b
1033 where b.instance = p_instance_id and
1034 b.level_id = a_child_level_id(i) and
1035 b.sr_level_pk = a_sr_level_pk(i) and
1036 b.parent_level_id = a_parent_level_id(i) and
1037 rownum < 2);
1038 END LOOP;
1039 END IF;
1040
1041 EXCEPTION
1042 when others then
1043 errbuf := substr(SQLERRM,1,150);
1044 retcode := -1;
1045 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1046
1047
1048 END Delete_duplicate_lvl_assoc;
1049
1050 /*****************************************************************************************
1051 Procedure Delete_Childless_Parent_ALL
1052
1053 This procedure will call delete_childless_parent for all level_id and
1054 instance.
1055
1056 ******************************************************************************************/
1057 Procedure Delete_Childless_Parent_ALL ( errbuf OUT NOCOPY VARCHAR2,
1058 retcode OUT NOCOPY VARCHAR2,
1059 p_instance in VARCHAR2) IS
1060
1061 /* Cursor for staging table */
1062 CURSOR c_st_level is
1063 select distinct a.instance, a.level_id
1064 from msd_st_level_values a, msd_levels b
1065 where a.level_id = b.level_id and
1066 a.instance <> 0 and
1067 b.level_type_code = 3 and
1068 a.instance = p_instance;
1069
1070
1071 /* Cursor for fact table */
1072 /* Bug# 4919130 - Always delete childless parents from staging table.
1073 CURSOR c_level is
1074 select distinct a.instance, a.level_id
1075 from msd_level_values a, msd_levels b
1076 where a.level_id = b.level_id and
1077 a.instance <> 0 and
1078 b.level_type_code = 3 and
1079 a.instance = p_instance;
1080
1081 l_dest_table VARCHAR2(40);
1082 */
1083
1084
1085 BEGIN
1086
1087 /* 1 step collection */
1088 /* Bug# 4919130 - Always delete childless parents from staging table.
1089 IF (fnd_profile.value('MSD_ONE_STEP_COLLECTION') = 'Y') THEN
1090
1091 l_dest_table := MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE;
1092
1093 FOR Level_Rec IN c_level LOOP
1094 Delete_Childless_Parent ( errbuf,
1095 retcode,
1096 Level_Rec.instance,
1097 Level_Rec.level_id,
1098 l_dest_table);
1099 END LOOP;
1100 */
1101 /* 2 step collection */
1102 /* Bug# 4919130 - Always delete childless parents from staging table.
1103 ELSE
1104 l_dest_table := MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE ;
1105 */
1106
1107 FOR Level_Rec IN c_st_level LOOP
1108 Delete_Childless_Parent ( errbuf,
1109 retcode,
1110 Level_Rec.instance,
1111 Level_Rec.level_id);
1112 -- l_dest_table); Bug# 4919130 - Always delete childless parents from staging table.
1113 END LOOP;
1114 /* Bug# 4919130 - Always delete childless parents from staging table.
1115 END IF;
1116 */
1117
1118
1119 END Delete_Childless_Parent_ALL;
1120
1121
1122
1123
1124
1125 /*****************************************************************************************
1126 Procedure Delete_Childless_Parent
1127
1128 This procedure will delete any childless parent level value.
1129 First, We will determine whether destination talbe is Fact or Staging, then
1130 Navigate level_value from either (msd_st_level_values or msd_level_values).
1131 Check whether that level_id exist in level association table as
1132 parent level id.
1133 If it does, then navigate next level id, otherwise, delete it.
1134
1135 ******************************************************************************************/
1136 Procedure Delete_Childless_Parent (
1137 errbuf OUT NOCOPY VARCHAR2,
1138 retcode OUT NOCOPY VARCHAR,
1139 p_instance_id in number,
1140 p_level_id in number) IS
1141 -- p_dest_table in varchar2) IS Bug# 4919130 - Always delete childless parents from staging table.
1142
1143 CURSOR c_childless_parent is
1144 select level_id, sr_level_pk
1145 from msd_backup_level_values
1146 where instance = '-999' and level_pk = -999;
1147
1148 l_count NUMBER(10) := 0;
1149 l_print_title BOOLEAN := TRUE;
1150
1151 BEGIN
1152
1153 /*----------------- Clear msd_backup_level_values table -------------------*/
1154 delete from msd_backup_level_values
1155 where instance = '-999' and level_pk = -999;
1156
1157 /* For 2 step collection */
1158 /* Bug# 4919130 - Always delete childless parents from staging table.
1159 IF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) THEN
1160 */
1161
1162 insert into msd_backup_level_values (instance , level_id, sr_level_pk, level_pk )
1163 select '-999' , level_id, sr_level_pk, -999
1164 from msd_st_level_values
1165 where instance = p_instance_id and level_id = p_level_id
1166 minus
1167 select '-999' , parent_level_id, sr_parent_level_pk, -999
1168 from msd_st_level_associations
1169 where instance = p_instance_id and parent_level_id = p_level_id ;
1170
1171 delete from msd_st_level_values a
1172 where
1173 instance = p_instance_id and
1174 level_id = p_level_id and
1175 exists (select 1 from msd_backup_level_values b
1176 where b.instance = '-999' and b.level_id = a.level_id and
1177 b.sr_level_pk = a.sr_level_pk and level_pk = -999);
1178
1179 /* For 1 step collection */
1180 /* Bug# 4919130 - Always delete childless parents from staging table.
1181 ELSIF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE) THEN
1182
1183 insert into msd_backup_level_values (instance , level_id, sr_level_pk, level_pk )
1184 select '-999' , level_id, sr_level_pk, -999
1185 from msd_level_values
1186 where instance = p_instance_id and level_id = p_level_id
1187 minus
1188 select '-999' , parent_level_id, sr_parent_level_pk, -999
1189 from msd_level_associations
1190 where instance = p_instance_id and parent_level_id = p_level_id ;
1191
1192 delete from msd_level_values a
1193 where
1194 instance = p_instance_id and
1195 level_id = p_level_id and
1196 exists (select 1 from msd_backup_level_values b
1197 where b.instance = '-999' and b.level_id = a.level_id and
1198 b.sr_level_pk = a.sr_level_pk and level_pk = -999);
1199
1200 END IF;
1201 */
1202
1203
1204 /*--------------- Report childless parent to the log file ------------------------*/
1205 FOR Childless_rec IN c_childless_parent LOOP
1206 l_count := l_count + 1;
1207
1208 IF ( l_print_title ) THEN
1209 fnd_file.put_line(fnd_file.log, ' ');
1210 fnd_file.put_line(fnd_file.log, 'Following Childless Level Values for Level ID : '||
1211 Childless_rec.level_id || ' were deleted.');
1212 fnd_file.put_line(fnd_file.log, 'SR Level PK ' );
1213 fnd_file.put_line(fnd_file.log, '---------------' );
1214 l_print_title := FALSE;
1215 END IF;
1216 fnd_file.put_line(fnd_file.log, ' ' || Childless_rec.sr_level_pk);
1217
1218 END LOOP;
1219
1220 IF (l_count > 0) THEN
1221 fnd_file.put_line(fnd_file.log, l_count ||' childless level values deleted.' );
1222 END IF;
1223
1224 /*------------------------- Clean up msd_backup_level_values table ---------------*/
1225 delete from msd_backup_level_values
1226 where level_pk = -999 and instance = '-999';
1227
1228 EXCEPTION
1229 when others then
1230 retcode := -1 ;
1231 errbuf := substr(SQLERRM,1,150);
1232 fnd_file.put_line(fnd_file.log, 'Error in Delete_Childless_Parent.');
1233 fnd_file.put_line(fnd_file.log, substr(SQLERRM,1,1000) );
1234
1235 END Delete_Childless_Parent;
1236
1237
1238
1239 END ;