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