DBA Data[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 ;