[Home] [Help]
PACKAGE BODY: APPS.PJI_PJP_SUM_DENORM
Source
1 package body PJI_PJP_SUM_DENORM as
2 /* $Header: PJISP03B.pls 120.23.12020000.3 2013/03/07 23:43:11 sachandr ship $ */
3
4 -- -----------------------------------------------------------------------
5
6 g_pa_debug_mode varchar2(1);
7
8 g_msg_level_data_bug number;
9 g_msg_level_data_corruption number;
10 g_msg_level_proc_call number;
11 g_msg_level_high_detail number;
12 g_msg_level_low_detail number;
13
14
15 -- -----------------------------------------------------
16 -- procedure POPULATE_XBS_DENORM
17 -- -----------------------------------------------------
18
19 procedure POPULATE_XBS_DENORM(
20 p_worker_id in number,
21 p_denorm_type in varchar2,
22 p_wbs_version_id in number,
23 p_prg_group1 in number,
24 p_prg_group2 in number
25 ) is
26
27 -- p_denorm_type in ('WBS', 'PRG', 'ALL')
28 -- p_wbs_version_id = single project structure version
29 -- p_prg_group1 These parameters will be used if online program
30 -- p_prg_group2 change processing is required in the future.
31
32
33 -- -----------------------------------------------------------------------
34 --
35 -- History
36 -- 19-MAR-2004 aartola Created
37 --
38 --
39 -- *** This API populates data for xbs data. This API calls the
40 -- following procedures:
41 --
42 -- prg_denorm
43 -- wbs_denorm
44 -- merge_xbs_denorm
45 --
46 -- -----------------------------------------------------------------------
47
48
49 -- -----------------------------------------------------
50 -- Declare statements --
51
52 l_process varchar2(30);
53 l_extraction_type varchar2(30);
54 l_fpm_upgrade varchar2(30);
55 l_denorm_type varchar2(30);
56 l_prg_group_id number;
57
58 -- -----------------------------------------------------
59
60 begin
61
62 l_denorm_type := nvl(p_denorm_type, 'ALL');
63
64 if g_pa_debug_mode = 'Y'
65 then
66 Pji_Utils.WRITE2LOG(
67 'PJI_PJP '
68 || '- Begin: populate_xbs_denorm '
69 || '- p_worker_id = '
70 || p_worker_id
71 || ' l_denorm_type = '
72 || l_denorm_type
73 || ' p_wbs_version_id = '
74 || p_wbs_version_id,
75 null,
76 g_msg_level_proc_call
77 );
78 end if;
79
80 -- -----------------------------------------------------
81 -- Variable assignments --
82
83 l_process := PJI_PJP_SUM_MAIN.g_process
84 || to_char(p_worker_id);
85
86 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(
87 l_process,
88 'EXTRACTION_TYPE'
89 );
90
91 l_fpm_upgrade := PJI_UTILS.GET_PARAMETER(
92 'PJI_FPM_UPGRADE'
93 );
94
95 -- ----------------------------------------
96
97 if g_pa_debug_mode = 'Y'
98 then
99 PJI_UTILS.WRITE2LOG(
100 'PJI_PJP - Variables -'
101 || ' l_process = '
102 || l_process
103 || ' l_extraction_type = '
104 || l_extraction_type,
105 null,
106 g_msg_level_high_detail
107 );
108 end if;
109
110 -- ----------------------------------------
111
112 -- -----------------------------------------------------
113 -- WBS Online Mode --
114
115 if l_denorm_type = 'WBS'
116 then
117
118 -- ----------------------------------------------
119 -- cleanup interim tables
120 cleanup_xbs_denorm(
121 p_worker_id,
122 'ONLINE'
123 );
124
125 -- ----------------------------------------------
126
127 -- delete WBS/XBS slices for specific struct_version_id -- ###delete###
128
129 if g_pa_debug_mode = 'Y'
130 then
131 PJI_UTILS.WRITE2LOG(
132 'PJI_PJP - Delete specific WBS/XBS slices -'
133 || ' p_wbs_version_id = '
134 || p_wbs_version_id,
135 null,
136 g_msg_level_high_detail
137 );
138 end if;
139
140 -- ###perf_bug### Bug 3828726
141 delete /*+ index(xbs, PA_XBS_DENORM_N2) */
142 from pa_xbs_denorm xbs
143 where struct_type in ('WBS', 'XBS')
144 and struct_version_id = p_wbs_version_id;
145
146 -- delete PRG slices for specific prg_group and p_wbs_version_id
147 begin
148 -- get prg_group information from versions table
149 select
150 distinct
151 prg_group
152 into l_prg_group_id
153 from pa_proj_element_versions
154 where element_version_id = p_wbs_version_id;
155
156 exception
157 when no_data_found
158 then
159 null;
160 end;
161
162 if g_pa_debug_mode = 'Y'
163 then
164 PJI_UTILS.WRITE2LOG(
165 'PJI_PJP - Deleted specific PRG slices -'
166 || ' p_wbs_version_id = '
167 || p_wbs_version_id
168 || ' prg_group_id = '
169 || l_prg_group_id,
170 null,
171 g_msg_level_high_detail
172 );
173 end if;
174
175 l_prg_group_id := null;
176
177 if l_prg_group_id is not null
178 then
179
180 -- ###performance###
181 delete
182 from pa_xbs_denorm
183 where struct_type = 'PRG'
184 and prg_group = l_prg_group_id;
185
186 -- repopulate PRG slices PA_XBS_DENORM for a prg group
187 prg_denorm_online(
188 p_worker_id,
189 'ONLINE',
190 l_prg_group_id,
191 null
192 );
193
194 else
195
196 -- ###perf_bug### Bug 3828726
197 delete /*+ index(prg, PA_XBS_DENORM_N1) */
198 from pa_xbs_denorm prg
199 where struct_type = 'PRG'
200 and struct_version_id is null
201 and sup_id = p_wbs_version_id
202 and sub_id = p_wbs_version_id;
203
204 -- repopulate PRG slices PA_XBS_DENORM for a wbs_version_id
205 prg_denorm_online(
206 p_worker_id,
207 'ONLINE',
208 null,
209 p_wbs_version_id
210 );
211 end if;
212
213 -- ----------------------------------------------
214 -- repopulate WBS/XBS slices PA_XBS_DENORM for a wbs_version_id
215
216 wbs_denorm_online(
217 p_worker_id,
218 'ONLINE',
219 p_wbs_version_id
220 );
221
222 -- merge results
223 merge_xbs_denorm(
224 p_worker_id,
225 'ONLINE'
226 );
227
228 -- don't delete contents from PJI_FP_AGGR_XBS_T
229
230 -- Sadiq will call cleanup_xbs_denorm(p_worker_id, 'ONLINE')
231
232
233 -- -----------------------------------------------------
234 -- PRG Online Mode --
235
236 elsif l_denorm_type = 'PRG'
237 then
238
239 null; -- do nothing for now
240
241
242 -- -----------------------------------------------------
243 -- Bulk Mode --
244
245 elsif l_denorm_type = 'ALL'
246 then
247
248 -- ----------------------------------------------
249 -- process PRG / WBS hiearchies during PJP summarization
250
251 if (
252 not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(
253 l_process,
254 'PJI_PJP_SUM_DENORM.POPULATE_XBS_DENORM(p_worker_id);'
255 )
256 )
257 then
258 return;
259 end if;
260
261 -- ----------------------------------------------
262 -- cleanup interim tables
263 cleanup_xbs_denorm(
264 p_worker_id,
265 l_extraction_type
266 );
267
268 if (l_extraction_type = 'FULL' and l_fpm_upgrade = 'C') then
269 -- Since 'FULL' mode of denorm table population does not handle
270 -- new projects after the first time it is run, we need to run
271 -- in 'INCREMENTAL' mode.
272 l_extraction_type := 'INCREMENTAL';
273 end if;
274
275 -- ----------------------------------------------
276 -- Bulk Full mode --
277
278 if (
279 (
280 l_extraction_type = 'FULL'
281 and
282 l_fpm_upgrade is null
283 )
284 or
285 l_fpm_upgrade = 'P'
286 )
287 then
288
289 -- PA_XBS_DENORM is empty, populate from scratch
290
291 -- XBS normalization algorithm for all data in
292 -- the structures tables.
293
294 prg_denorm(
295 p_worker_id,
296 l_extraction_type
297 );
298
299 merge_xbs_denorm(
300 p_worker_id,
301 l_extraction_type
302 );
303
304 cleanup_xbs_denorm(
305 p_worker_id,
306 l_extraction_type
307 );
308
309 -- ----------------------------------------------
310 -- Bulk Incremental/Partial mode --
311
312 elsif (
313 l_extraction_type = 'INCREMENTAL'
314 or
315 l_extraction_type = 'PARTIAL'
316 )
317 then
318 -- PA_XBS_DENORM contains data, repopulate a portion of it
319
320 -- delete WBS/XBS slices for specific struct_version_id -- ###delete###
321 FOR WBS_DELETE_NODE IN
322 (
323 select
324 distinct
325 decode( invert.id,
326 1, i_log.event_object,
327 2, i_log.attribute2
328 ) event_object_id
329 from PJI_PA_PROJ_EVENTS_LOG i_log,
330 (
331 select 1 id
332 from dual
333 UNION ALL
334 select 2 id
335 from dual
336 ) invert
337 where 1=1
338 and i_log.worker_id = P_WORKER_ID
339 and i_log.event_type in ('WBS_CHANGE', 'WBS_PUBLISH')
340 ) LOOP
341
342 if g_pa_debug_mode = 'Y'
343 then
344 PJI_UTILS.WRITE2LOG(
345 'PJI_PJP - Delete specific WBS/XBS slices -'
346 || ' struct_version_id = '
347 || WBS_DELETE_NODE.event_object_id,
348 null,
349 g_msg_level_high_detail
350 );
351 end if;
352
353 delete
354 from PA_XBS_DENORM
355 where 1=1
356 and STRUCT_TYPE in ('WBS', 'XBS')
357 and STRUCT_VERSION_ID = WBS_DELETE_NODE.event_object_id;
358
359 END LOOP;
360
361 -- Event Type 1
362 -- delete PRG slices for specific prg_group
363
364 FOR PRG_DELETE_NODE IN
365 (
366 select
367 distinct
368 decode( invert.id,
369 1, i_log.event_object,
370 2, i_log.attribute1
371 ) event_object_id
372 from PJI_PA_PROJ_EVENTS_LOG i_log,
373 (
374 select 1 id
375 from dual
376 UNION ALL
377 select 2 id
378 from dual
379 ) invert
380 where 1=1
381 and i_log.worker_id = P_WORKER_ID
382 and i_log.event_type = 'PRG_CHANGE'
383 and i_log.event_object <> -1
384 ) LOOP
385
386 if g_pa_debug_mode = 'Y'
387 then
388 PJI_UTILS.WRITE2LOG(
389 'PJI_PJP - Program groups to be deleted -'
390 || ' prg_group = '
391 || PRG_DELETE_NODE.event_object_id,
392 null,
393 g_msg_level_high_detail
394 );
395 end if;
396
397 -- check projects that are outdated
398
399 FOR PRG_PROJECT_NODE IN
400 (
401 select
402 distinct
403 project_id
404 from pa_proj_element_versions
405 where 1=1
406 and object_type = 'PA_STRUCTURES'
407 and prg_group IS NOT NULL /* 4904076 */
408 and prg_group = PRG_DELETE_NODE.event_object_id
409 ) LOOP
410
411 -- delete all slices of all projects belonging
412 -- to specific projects in a group
413
414 if g_pa_debug_mode = 'Y'
415 then
416 PJI_UTILS.WRITE2LOG(
417 'PJI_PJP - Delete -'
418 || ' sup_project_id = '
419 || PRG_PROJECT_NODE.project_id,
420 null,
421 g_msg_level_high_detail
422 );
423 end if;
424
425 delete
426 from PA_XBS_DENORM
427 where 1=1
428 -- and struct_type = 'PRG'
429 and sup_project_id = PRG_PROJECT_NODE.project_id;
430
431 END LOOP;
432
433 END LOOP;
434
435
436 -- Event Type 2
437 -- delete All slices for sup_project_id
438
439 FOR PRG_2_DELETE_NODE IN
440 (
441 select
442 distinct
443 i_log.ATTRIBUTE1
444 from PJI_PA_PROJ_EVENTS_LOG i_log
445 where 1=1
446 and i_log.worker_id = P_WORKER_ID
447 and i_log.event_type = 'PRG_CHANGE'
448 and i_log.event_object = -1
449 ) LOOP
450
451 /* Bug 5006375 , 5608947 */
452 FOR PRG_PROJECT_NODE IN
453 (
454 select
455 distinct
456 project_id
457 from pa_proj_element_versions
458 where 1=1
459 and object_type = 'PA_STRUCTURES'
460 and prg_group IS NULL /* 4904076 */
461 and project_id = PRG_2_DELETE_NODE.attribute1
462 ) LOOP
463
464 if g_pa_debug_mode = 'Y'
465 then
466 PJI_UTILS.WRITE2LOG(
467 'PJI_PJP - Delete specific ALL slices -'
468 || ' sup_project_id = '
469 || PRG_PROJECT_NODE.project_id,
470 null,
471 g_msg_level_high_detail
472 );
473 end if;
474
475 delete
476 from PA_XBS_DENORM
477 where 1=1
478 -- and struct_type = 'PRG'
479 and sup_project_id = PRG_PROJECT_NODE.project_id;
480
481 -- PRG_2_DELETE_NODE.attribute1;
482
483 END LOOP;
484 /* Bug 5006375 , 5608947 */
485
486 END LOOP;
487
488 --
489 -- XBS normalization algorithm based on:
490 --
491 -- EVENT_TYPE in ('WBS_CHANGE', 'WBS_PUBLISH')
492 -- ==> EVENT_OBJECT = structure version
493 -- EVENT_TYPE = 'PRG_CHANGE' ==> EVENT_OBJECT = program group
494 --
495 -- OPERATION_TYPE in ('I', 'U', 'D') ==> since we delete the entire
496 --version or program we do not
497 --use this parameter for now
498 --
499 --
500
501 -- *** Use PJI_PA_PROJ_EVENTS_LOG *** to determine which PRG/WBS
502 -- data to process. ***
503
504 prg_denorm(
505 p_worker_id,
506 l_extraction_type
507 );
508
509 merge_xbs_denorm(
510 p_worker_id,
511 l_extraction_type
512 );
513
514 cleanup_xbs_denorm(
515 p_worker_id,
516 l_extraction_type
517 );
518
519 -- -----------------------------------------------------
520
521 end IF;
522
523 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(
524 l_process,
525 'PJI_PJP_SUM_DENORM.POPULATE_XBS_DENORM(p_worker_id);'
526 );
527
528 commit;
529
530 end IF;
531
532 -- -----------------------------------------------------
533
534 if g_pa_debug_mode = 'Y'
535 then
536 PJI_UTILS.WRITE2LOG(
537 'PJI_PJP - End: populate_xbs_denorm',
538 null,
539 g_msg_level_proc_call
540 );
541 end if;
542 -- -----------------------------------------------------
543
544 end POPULATE_XBS_DENORM;
545
546 -- -----------------------------------------------------------------------
547 -- -----------------------------------------------------------------------
548 -- -----------------------------------------------------------------------
549
550 procedure COPY_XBS_DENORM(
551 p_worker_id in number,
552 p_wbs_version_id_from in number,
553 p_wbs_version_id_to in number,
554 p_copy_mode in varchar2
555 ) is
556
557
558 -- p_wbs_version_id_from = single project structure version from which you are copying
559 -- p_wbs_version_id_to = single project structure version to which you are copying
560
561
562 -- -----------------------------------------------------------------------
563 --
564 -- History
565 -- 4-MAR-2005 ajdas Created
566 -- *** This API populates data for xbs data. This API calls the
567 --
568 -- -----------------------------------------------------------------------
569
570
571 -- -----------------------------------------------------
572 -- Declare statements --
573 /*bug#4590082, changed the WBS check to PRG slice check to see if any valid source version exists*/
574 cursor c_version_exists is select 1 from dual
575 where exists (select 1
576 from pa_xbs_denorm
577 where struct_version_id is null
578 and sup_id=p_wbs_version_id_from
579 and sub_id=p_wbs_version_id_from );
580 l_dummy NUMBER;
581 l_process varchar2(30);
582 l_extraction_type varchar2(30);
583 l_fpm_upgrade varchar2(30);
584 l_denorm_type varchar2(30);
585 l_prg_group_id number;
586 l_last_update_date date;
587 l_last_updated_by number;
588 l_creation_date date;
589 l_created_by number;
590 l_last_update_login number;
591 l_top_proj_element_id number;
592 l_target_project_id number;
593 l_prg_exists number;
594 -- -----------------------------------------------------
595 begin
596
597 if g_pa_debug_mode = 'Y'
598 then
599 Pji_Utils.WRITE2LOG(
600 'PJI_PJP '
601 || '- Begin: copy_xbs_denorm '
602 || '- p_worker_id = '
603 || p_worker_id
604 || '- p_wbs_version_id_from = '
605 || p_wbs_version_id_from
606 || ' p_wbs_version_id_to = '
607 || p_wbs_version_id_to,
608 null,
609 g_msg_level_proc_call
610 );
611 end if;
612
613 -- -----------------------------------------------------
614 -- Variable assignments --
615 -- Variable Assignments --
616
617 -- Checking if the target is already populated then not popultaing the denorm data
618 begin
619 select 1
620 into l_prg_exists
621 from dual
622 where exists (select 1
623 from pa_xbs_denorm
624 where struct_version_id is null
625 and sup_id=p_wbs_version_id_to
626 and sub_id=p_wbs_version_id_to);
627
628 If l_prg_exists=1 then
629 return;
630 end if;
631 exception when no_data_found then
632 null;
633 end;
634
635 l_last_update_date := sysdate;
636 l_last_updated_by := FND_GLOBAL.USER_ID;
637 l_creation_date := sysdate;
638 l_created_by := FND_GLOBAL.USER_ID;
639 l_last_update_login := FND_GLOBAL.LOGIN_ID;
640
641 begin
642 select proj_element_id ,project_id
643 into l_top_proj_element_id ,l_target_project_id
644 from PA_PROJ_ELEMENT_VERSIONS
645 where parent_structure_version_id= p_wbs_version_id_to
646 and object_type='PA_STRUCTURES';
647 exception
648 when no_data_found then null;
649 end;
650
651 open c_version_exists ;
652 fetch c_version_exists into l_dummy;
653 if c_version_exists%found then
654
655 If p_copy_mode='P' THEN
656
657 /* First inserting all the records of the old version changing the project_element_id mapping to element_number i.e
658 sup_emt_id,sub_emt_id,subro_id to sup_element_numbner,sub_element_numbner,subroelement_numbner ,
659 once that is done getting the pro_element_id back for the new version by mapping the same element number
660 to the new proj_element_ids*/
661 declare
662 cursor c_temp(p_wbs_version_id_from in number,p_wbs_version_id_to in number,l_top_proj_element_id in number) is
663 select temp.struct_type,temp.prg_group,projv1s.project_id sup_project_id, projv1s.proj_element_id sup_emt_id
664 ,projv1s.element_version_id sup_id,projv1s.parent_structure_version_id struct_version_id
665 ,temp.sub_leaf_flag, temp.sup_level, temp.sub_level,temp.relationship_type
666 , decode( temp.struct_type,'WBS',l_top_proj_element_id,null) struct_emt_id
667 ,temp.sub_rollup_id,0,0,0,temp.sub_element_number,temp.subro_element_number
668 from pa_xbs_denorm_temp temp
669 ,PA_PROJ_ELEMENT_VERSIONS projv1s
670 ,pa_proj_elements proje1s
671 where proje1s.element_number= temp.sup_element_number
672 and proje1s.proj_element_id =projv1s.proj_element_id
673 and projv1s.parent_structure_version_id=temp.struct_version_id
674 and projv1s.parent_structure_version_id=p_wbs_version_id_to;
675
676 struct_typetab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
677 prg_grouptab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
678 sup_project_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
679 sup_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
680 sup_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
681 struct_version_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
682 sub_leaf_flagtab SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
683 sup_leveltab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
684 sub_leveltab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
685 relationship_typetab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
686 struct_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
687 sub_rollup_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
688 sub_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
689 sub_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
690 subro_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
691 sub_element_numbertab SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
692 subro_element_numbertab SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
693
694 begin
695 insert into PA_XBS_DENORM_temp
696 (
697 struct_type, prg_group, sup_project_id,
698 sup_emt_id, sup_id, struct_version_id,
699 sub_leaf_flag, sup_level, sub_level,
700 relationship_type, struct_emt_id, sub_rollup_id,
701 sub_id, sub_emt_id, subro_id,
702 sub_element_number,
703 sup_element_number,
704 subro_element_number
705 )
706 select xbs.struct_type,
707 null prg_group
708 ,xbs.sup_project_id
709 ,xbs.sup_emt_id,xbs.sup_id,p_wbs_version_id_to struct_version_id
710 , xbs.sub_leaf_flag,xbs.sup_level, xbs.sub_level
711 , xbs.relationship_type,xbs.struct_emt_id,xbs.sub_rollup_id
712 ,xbs.sub_id,xbs.sub_emt_id,xbs.subro_id
713 ,(select projeb.element_number from pa_proj_elements projeb
714 where projeb.proj_element_id= xbs.sub_emt_id ) sub_element_number
715 ,(select decode( proje.object_type,'PA_TASKS',proje.element_number,'PA_STRUCTURES',to_char(l_top_proj_element_id ),null) from pa_proj_elements proje
716 where proje.proj_element_id= xbs.sup_emt_id ) sup_element_number
717 ,(select projec.element_number from pa_proj_elements projec
718 where projec.proj_element_id= xbs.subro_id) subro_element_number
719 from pa_xbs_denorm xbs
720 ,PA_PROJ_ELEMENT_VERSIONS projsup
721 where xbs.sup_emt_id =projsup.proj_element_id
722 and xbs.sup_id=projsup.element_version_id
723 and xbs.struct_version_id =p_wbs_version_id_from
724 and projsup.parent_structure_version_id=xbs.struct_version_id;
725
726 OPEN c_temp( p_wbs_version_id_from,p_wbs_version_id_to,l_top_proj_element_id );
727 FETCH c_temp bulk collect into
728 struct_typetab,
729 prg_grouptab,
730 sup_project_idtab,
731 sup_emt_idtab,
732 sup_idtab,
733 struct_version_idtab,
734 sub_leaf_flagtab,
735 sup_leveltab,
736 sub_leveltab,
737 relationship_typetab,
738 struct_emt_idtab,
739 sub_rollup_idtab,
740 sub_idtab,
741 sub_emt_idtab,
742 subro_idtab,
743 sub_element_numbertab,
744 subro_element_numbertab;
745
746 delete from pa_xbs_denorm_temp;
747 IF struct_typetab.COUNT <>0 then
748 FORALL i IN struct_typetab.FIRST..struct_typetab.LAST
749 insert into PA_XBS_DENORM_temp
750 (
751 struct_type
752 ,prg_group
753 ,sup_project_id
754 ,sup_emt_id
755 ,sup_id
756 ,struct_version_id
757 ,sub_leaf_flag
758 ,sup_level
759 ,sub_level
760 ,relationship_type
761 ,struct_emt_id
762 ,sub_rollup_id
763 ,sub_id
764 ,sub_emt_id
765 ,subro_id
766 ,sub_element_number
767 ,subro_element_number
768 )
769 VALUES
770 ( struct_typetab(i)
771 ,prg_grouptab(i)
772 ,sup_project_idtab(i)
773 ,sup_emt_idtab(i)
774 ,sup_idtab(i)
775 ,struct_version_idtab(i)
776 ,sub_leaf_flagtab(i)
777 ,sup_leveltab(i)
778 ,sub_leveltab(i)
779 ,relationship_typetab(i)
780 ,struct_emt_idtab(i)
781 ,sub_rollup_idtab(i)
782 ,sub_idtab(i)
783 ,sub_emt_idtab(i)
784 ,subro_idtab(i)
785 ,sub_element_numbertab(i)
786 ,subro_element_numbertab(i)
787 );
788 END IF;
789 close c_temp;
790 --write_log('insert bulk into temp done 1');
791 end;
792 /* getting the pro_element_id back for the new version by mapping the same element number
793 to the new proj_element_ids*/
794 declare
795 cursor c_temp(p_wbs_version_id_to in number) is
796 select temp.struct_type,temp.prg_group
797 ,temp.struct_version_id ,temp.sup_project_id
798 ,temp.sup_emt_id , projv1b.proj_element_id sub_emt_id
799 ,temp.sup_id ,projv1b.element_version_id sub_id
800 ,temp.sup_level, temp.sub_level
801 ,temp.relationship_type ,temp.sub_leaf_flag
802 ,temp.struct_emt_id,temp.sub_rollup_id
803 ,(select proje1.proj_element_id from pa_proj_elements proje1
804 ,PA_PROJ_ELEMENT_VERSIONS projv1
805 WHERE PROJE1.PROJECT_ID=TEMP.SUP_PROJECT_ID
806 AND PROJE1.OBJECT_TYPE='PA_TASKS'
807 AND PROJE1.ELEMENT_NUMBER= TEMP.SUBRO_ELEMENT_NUMBER
808 AND PROJE1.PROJ_ELEMENT_ID= PROJV1.PROJ_ELEMENT_ID
809 AND PROJV1.PARENT_STRUCTURE_VERSION_ID= TEMP.STRUCT_VERSION_ID
810 AND PROJV1.PARENT_STRUCTURE_VERSION_ID=p_wbs_version_id_to
811 ) subro_id
812 from pa_xbs_denorm_temp temp
813 ,PA_PROJ_ELEMENT_VERSIONS projv1b
814 ,pa_proj_elements proje1b
815 where proje1b.element_number= temp.sub_element_number
816 and proje1b.proj_element_id =projv1b.proj_element_id
817 and projv1b.parent_structure_version_id=temp.struct_version_id
818 and projv1b.parent_structure_version_id=p_wbs_version_id_to;
819
820 struct_typetab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
821 prg_grouptab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
822 sup_project_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
823 sup_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
824 sup_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
825 struct_version_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
826 sub_leaf_flagtab SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
827 sup_leveltab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
828 sub_leveltab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
829 relationship_typetab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
830 struct_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
831 sub_rollup_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
832 sub_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
833 sub_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
834 subro_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
835 sub_element_numbertab SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
836 subro_element_numbertab SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
837
838 begin
839 OPEN c_temp( p_wbs_version_id_to );
840 FETCH c_temp bulk collect into
841 struct_typetab
842 ,prg_grouptab
843 ,struct_version_idtab
844 ,sup_project_idtab
845 ,sup_emt_idtab
846 ,sub_emt_idtab
847 ,sup_idtab
848 ,sub_idtab
849 ,sup_leveltab
850 ,sub_leveltab
851 ,relationship_typetab
852 ,sub_leaf_flagtab
853 ,struct_emt_idtab
854 ,sub_rollup_idtab
855 ,subro_idtab ;
856 --write_log('second fetch done 1');
857 IF struct_typetab.COUNT <>0 then
858 FORALL i IN struct_typetab.FIRST..struct_typetab.LAST
859 insert into PA_XBS_DENORM(
860 struct_type
861 ,prg_group
862 ,struct_version_id
863 ,sup_project_id
864 ,sup_emt_id
865 ,sub_emt_id
866 ,sup_id
867 ,sub_id
868 ,sup_level
869 ,sub_level
870 ,relationship_type
871 ,sub_leaf_flag
872 ,struct_emt_id
873 ,sub_rollup_id
874 ,subro_id
875 ,LAST_UPDATE_DATE
876 ,LAST_UPDATED_BY
877 ,CREATION_DATE
878 ,CREATED_BY
879 ,LAST_UPDATE_LOGIN
880 )
881 VALUES
882 ( struct_typetab(i)
883 ,prg_grouptab(i)
884 ,struct_version_idtab(i)
885 ,sup_project_idtab(i)
886 ,sup_emt_idtab(i)
887 ,sub_emt_idtab(i)
888 ,sup_idtab(i)
889 ,sub_idtab(i)
890 ,sup_leveltab(i)
891 ,sub_leveltab(i)
892 ,relationship_typetab(i)
893 ,sub_leaf_flagtab(i)
894 ,struct_emt_idtab(i)
895 ,sub_rollup_idtab(i)
896 ,subro_idtab(i)
897 ,l_last_update_date
898 ,l_last_updated_by
899 ,l_creation_date
900 ,l_created_by
901 ,l_last_update_login
902 );
903 END IF;
904 /* The below insert replaces the call to Pji_Pjp_Sum_Rollup.update_xbs_denorm
905 it inserts all the data to pji_xbs_Denorm from pa_xbs_Denorm*/
906 IF struct_typetab.COUNT <>0 then
907 FORALL i IN struct_typetab.FIRST..struct_typetab.LAST
908 insert into PJI_XBS_DENORM(
909 struct_type
910 ,prg_group
911 ,struct_version_id
912 ,sup_project_id
913 ,sup_emt_id
914 ,sub_emt_id
915 ,sup_id
916 ,sub_id
917 ,sup_level
918 ,sub_level
919 ,relationship_type
920 ,sub_leaf_flag
921 -- ,struct_emt_id
922 ,sub_rollup_id
923 ,subro_id
924 ,LAST_UPDATE_DATE
925 ,LAST_UPDATED_BY
926 ,CREATION_DATE
927 ,CREATED_BY
928 ,LAST_UPDATE_LOGIN
929 )
930 VALUES
931 ( struct_typetab(i)
932 ,prg_grouptab(i)
933 ,struct_version_idtab(i)
934 ,sup_project_idtab(i)
935 ,sup_emt_idtab(i)
936 ,sub_emt_idtab(i)
937 ,sup_idtab(i)
938 ,sub_idtab(i)
939 ,sup_leveltab(i)
940 ,sub_leveltab(i)
941 ,relationship_typetab(i)
942 ,sub_leaf_flagtab(i)
943 -- ,struct_emt_idtab(i)
944 ,sub_rollup_idtab(i)
945 ,subro_idtab(i)
946 ,l_last_update_date
947 ,l_last_updated_by
948 ,l_creation_date
949 ,l_created_by
950 ,l_last_update_login
951 );
952 END IF;
953 close c_temp;
954 -- write_log('insert bulk into xbs_Denorm done 1');
955 end;
956 /* inserting the PRG lines*/
957 insert
958 into PA_XBS_DENORM
959 (
960 struct_type,
961 prg_group,
962 struct_version_id,
963 sup_project_id,
964 sup_emt_id,
965 sub_emt_id,
966 subro_id,
967 sup_level,
968 sub_rollup_id,
969 sub_leaf_flag,
970 sub_level,
971 relationship_type,
972 struct_emt_id,
973 sup_id,
974 sub_id,
975 LAST_UPDATE_DATE,
976 LAST_UPDATED_BY,
977 CREATION_DATE,
978 CREATED_BY,
979 LAST_UPDATE_LOGIN
980 )
981 select xbs.struct_type,
982 null prg_group,
983 xbs.struct_version_id,l_target_project_id sup_project_id,
984 l_top_proj_element_id sup_emt_id,l_top_proj_element_id sub_emt_id,
985 xbs.subro_id,1,
986 l_top_proj_element_id sub_rollup_id,xbs.sub_leaf_flag,
987 1,xbs.relationship_type,
988 xbs.struct_emt_id,to_number(p_wbs_version_id_to) wsup_id,
989 to_number(p_wbs_version_id_to) wsub_id,l_last_update_date,
990 l_last_updated_by,l_creation_date,
991 l_created_by ,l_last_update_login
992 from PA_XBS_DENORM xbs
993 where xbs.struct_version_id is null
994 and xbs.sup_id=p_wbs_version_id_from
995 and xbs.sub_id=p_wbs_version_id_from;
996 --write_log('inert of PRG line done 1');
997
998 -- write_log(' first insert into pji 1');
999 insert into PJI_XBS_DENORM
1000 (
1001 STRUCT_TYPE,
1002 PRG_GROUP,
1003 STRUCT_VERSION_ID,
1004 SUP_PROJECT_ID,
1005 SUP_ID,
1006 SUP_EMT_ID,
1007 SUBRO_ID,
1008 SUB_ID,
1009 SUB_EMT_ID,
1010 SUP_LEVEL,
1011 SUB_LEVEL,
1012 SUB_ROLLUP_ID,
1013 SUB_LEAF_FLAG,
1014 RELATIONSHIP_TYPE,
1015 LAST_UPDATE_DATE,
1016 LAST_UPDATED_BY,
1017 CREATION_DATE,
1018 CREATED_BY,
1019 LAST_UPDATE_LOGIN
1020 )
1021 select
1022 den.STRUCT_TYPE,
1023 den.PRG_GROUP,
1024 den.STRUCT_VERSION_ID,
1025 den.SUP_PROJECT_ID,
1026 den.SUP_ID,
1027 den.SUP_EMT_ID,
1028 den.SUBRO_ID,
1029 den.SUB_ID,
1030 den.SUB_EMT_ID,
1031 1 SUP_LEVEL,
1032 1 SUB_LEVEL,
1033 den.SUB_ROLLUP_ID,
1034 den.SUB_LEAF_FLAG,
1035 den.RELATIONSHIP_TYPE,
1036 l_last_update_date,
1037 l_last_updated_by,
1038 l_creation_date,
1039 l_created_by,
1040 l_last_update_login
1041 from
1042 PA_XBS_DENORM den
1043 where den.struct_version_id is null
1044 and den.sup_id=p_wbs_version_id_to
1045 and den.sub_id=p_wbs_version_id_to;
1046
1047
1048 elsIf p_copy_mode='V' THEN
1049
1050 /* this code executes for only pulishing or making working version when the version id changes between the same prtoject id */
1051
1052 declare
1053 cursor c_temp(p_wbs_version_id_from in number,p_wbs_version_id_to in number) is
1054 select xbs.struct_type,xbs.prg_group,
1055 to_number(p_wbs_version_id_to) wstruct_version_id,xbs.sup_project_id,
1056 xbs.sup_emt_id,xbs.sub_emt_id,
1057 projsup.element_version_id sup_id,
1058 (select projsub.element_version_id from PA_PROJ_ELEMENT_VERSIONS projsub
1059 where projsub.proj_element_id= xbs.sub_emt_id
1060 and projsup.parent_structure_version_id=projsub.parent_structure_version_id) sub_id,
1061 xbs.sup_level,xbs.sub_level,
1062 xbs.relationship_type,xbs.sub_leaf_flag,
1063 xbs.struct_emt_id,xbs.sub_rollup_id,
1064 xbs.subro_id
1065 from pa_xbs_denorm xbs,
1066 PA_PROJ_ELEMENT_VERSIONS projsup
1067 where projsup.parent_structure_version_id= p_wbs_version_id_to
1068 and projsup.proj_element_id= xbs.sup_emt_id
1069 and xbs.struct_version_id= p_wbs_version_id_from;
1070
1071 struct_typetab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1072 prg_grouptab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1073 sup_project_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1074 sup_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1075 sup_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1076 struct_version_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1077 sub_leaf_flagtab SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
1078 sup_leveltab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1079 sub_leveltab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1080 relationship_typetab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1081 struct_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1082 sub_rollup_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1083 sub_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1084 sub_emt_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1085 subro_idtab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1086 sub_element_numbertab SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
1087 subro_element_numbertab SYSTEM.PA_VARCHAR2_100_TBL_TYPE := SYSTEM.PA_VARCHAR2_100_TBL_TYPE();
1088
1089 begin
1090 OPEN c_temp( p_wbs_version_id_from,p_wbs_version_id_to );
1091 FETCH c_temp bulk collect into
1092 struct_typetab
1093 ,prg_grouptab
1094 ,struct_version_idtab
1095 ,sup_project_idtab
1096 ,sup_emt_idtab
1097 ,sub_emt_idtab
1098 ,sup_idtab
1099 ,sub_idtab
1100 ,sup_leveltab
1101 ,sub_leveltab
1102 ,relationship_typetab
1103 ,sub_leaf_flagtab
1104 ,struct_emt_idtab
1105 ,sub_rollup_idtab
1106 ,subro_idtab ;
1107 IF struct_typetab.COUNT <>0 then
1108 FORALL i IN struct_typetab.FIRST..struct_typetab.LAST
1109 insert into PA_XBS_DENORM(
1110 struct_type
1111 ,prg_group
1112 ,struct_version_id
1113 ,sup_project_id
1114 ,sup_emt_id
1115 ,sub_emt_id
1116 ,sup_id
1117 ,sub_id
1118 ,sup_level
1119 ,sub_level
1120 ,relationship_type
1121 ,sub_leaf_flag
1122 ,struct_emt_id
1123 ,sub_rollup_id
1124 ,subro_id
1125 ,LAST_UPDATE_DATE
1126 ,LAST_UPDATED_BY
1127 ,CREATION_DATE
1128 ,CREATED_BY
1129 ,LAST_UPDATE_LOGIN
1130 )
1131 VALUES
1132 ( struct_typetab(i)
1133 ,prg_grouptab(i)
1134 ,struct_version_idtab(i)
1135 ,sup_project_idtab(i)
1136 ,sup_emt_idtab(i)
1137 ,sub_emt_idtab(i)
1138 ,sup_idtab(i)
1139 ,sub_idtab(i)
1140 ,sup_leveltab(i)
1141 ,sub_leveltab(i)
1142 ,relationship_typetab(i)
1143 ,sub_leaf_flagtab(i)
1144 ,struct_emt_idtab(i)
1145 ,sub_rollup_idtab(i)
1146 ,subro_idtab(i)
1147 ,l_last_update_date
1148 ,l_last_updated_by
1149 ,l_creation_date
1150 ,l_created_by
1151 ,l_last_update_login
1152 );
1153 END IF;
1154 /* The below insert replaces the call to Pji_Pjp_Sum_Rollup.update_xbs_denorm
1155 it inserts all the data to pji_xbs_Denorm from pa_xbs_Denorm*/
1156 IF struct_typetab.COUNT <>0 then
1157 FORALL i IN struct_typetab.FIRST..struct_typetab.LAST
1158 insert into PJI_XBS_DENORM(
1159 struct_type
1160 ,prg_group
1161 ,struct_version_id
1162 ,sup_project_id
1163 ,sup_emt_id
1164 ,sub_emt_id
1165 ,sup_id
1166 ,sub_id
1167 ,sup_level
1168 ,sub_level
1169 ,relationship_type
1170 ,sub_leaf_flag
1171 -- ,struct_emt_id
1172 ,sub_rollup_id
1173 ,subro_id
1174 ,LAST_UPDATE_DATE
1175 ,LAST_UPDATED_BY
1176 ,CREATION_DATE
1177 ,CREATED_BY
1178 ,LAST_UPDATE_LOGIN
1179 )
1180 VALUES
1181 ( struct_typetab(i)
1182 ,prg_grouptab(i)
1183 ,struct_version_idtab(i)
1184 ,sup_project_idtab(i)
1185 ,sup_emt_idtab(i)
1186 ,sub_emt_idtab(i)
1187 ,sup_idtab(i)
1188 ,sub_idtab(i)
1189 ,sup_leveltab(i)
1190 ,sub_leveltab(i)
1191 ,relationship_typetab(i)
1192 ,sub_leaf_flagtab(i)
1193 -- ,struct_emt_idtab(i)
1194 ,sub_rollup_idtab(i)
1195 ,subro_idtab(i)
1196 ,l_last_update_date
1197 ,l_last_updated_by
1198 ,l_creation_date
1199 ,l_created_by
1200 ,l_last_update_login
1201 );
1202 END IF;
1203 close c_temp;
1204 -- write_log('insert bulk into xbs_Denorm done 1');
1205 end;
1206 /* inserting the PRG lines*/
1207 insert
1208 into PA_XBS_DENORM
1209 (
1210 struct_type,
1211 prg_group,
1212 struct_version_id,
1213 sup_project_id,
1214 sup_emt_id,
1215 sub_emt_id,
1216 subro_id,
1217 sup_level,
1218 sub_rollup_id,
1219 sub_leaf_flag,
1220 sub_level,
1221 relationship_type,
1222 struct_emt_id,
1223 sup_id,
1224 sub_id,
1225 LAST_UPDATE_DATE,
1226 LAST_UPDATED_BY,
1227 CREATION_DATE,
1228 CREATED_BY,
1229 LAST_UPDATE_LOGIN
1230 )
1231 select xbs.struct_type,xbs.prg_group,
1232 xbs.struct_version_id,xbs.sup_project_id,
1233 xbs.sup_emt_id,xbs.sub_emt_id,
1234 xbs.subro_id,1,
1235 xbs.sub_rollup_id,xbs.sub_leaf_flag,
1236 1,xbs.relationship_type,
1237 xbs.struct_emt_id,to_number(p_wbs_version_id_to) wsup_id,
1238 to_number(p_wbs_version_id_to) wsub_id,l_last_update_date,
1239 l_last_updated_by,l_creation_date,
1240 l_created_by ,l_last_update_login
1241 from PA_XBS_DENORM xbs
1242 where xbs.struct_version_id is null
1243 and xbs.sup_id=p_wbs_version_id_from
1244 and xbs.sub_id=p_wbs_version_id_from;
1245
1246 insert into PJI_XBS_DENORM
1247 (
1248 STRUCT_TYPE,
1249 PRG_GROUP,
1250 STRUCT_VERSION_ID,
1251 SUP_PROJECT_ID,
1252 SUP_ID,
1253 SUP_EMT_ID,
1254 SUBRO_ID,
1255 SUB_ID,
1256 SUB_EMT_ID,
1257 SUP_LEVEL,
1258 SUB_LEVEL,
1259 SUB_ROLLUP_ID,
1260 SUB_LEAF_FLAG,
1261 RELATIONSHIP_TYPE,
1262 LAST_UPDATE_DATE,
1263 LAST_UPDATED_BY,
1264 CREATION_DATE,
1265 CREATED_BY,
1266 LAST_UPDATE_LOGIN
1267 )
1268 select
1269 den.STRUCT_TYPE,
1270 den.PRG_GROUP,
1271 den.STRUCT_VERSION_ID,
1272 den.SUP_PROJECT_ID,
1273 den.SUP_ID,
1274 den.SUP_EMT_ID,
1275 den.SUBRO_ID,
1276 den.SUB_ID,
1277 den.SUB_EMT_ID,
1278 den.SUP_LEVEL,
1279 den.SUB_LEVEL,
1280 den.SUB_ROLLUP_ID,
1281 den.SUB_LEAF_FLAG,
1282 den.RELATIONSHIP_TYPE,
1283 l_last_update_date,
1284 l_last_updated_by,
1285 l_creation_date,
1286 l_created_by,
1287 l_last_update_login
1288 from
1289 PA_XBS_DENORM den
1290 where den.struct_version_id is null
1291 and den.sup_id=p_wbs_version_id_to
1292 and den.sub_id=p_wbs_version_id_to;
1293 end if;
1294 else
1295 /* If there are no record in the source version itself then call the existing API for
1296 the norma flow, so calling the populate_WBS_denorm ,
1297 But commenting this as this is a corner exception case, in which at present
1298 will not do any processing to find the exce[tional handling*/
1299 /* PJI_FM_PLAN_MAINT.POPULATE_WBS_DENORM(
1300 p_online => 'Y'
1301 , x_return_status => l_return_status );
1302 */
1303 null;
1304 end IF;
1305 close c_version_exists;
1306 if g_pa_debug_mode = 'Y'
1307 then
1308 PJI_UTILS.WRITE2LOG(
1309 'PJI_PJP - End: copy_xbs_denorm',
1310 null,
1311 g_msg_level_proc_call
1312 );
1313 end if;
1314 -- -----------------------------------------------------
1315 -- write_log(' end of copy_xbs_Denorm 1');
1316
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PJI_PJP_SUM_DENORM' ,
1320 p_procedure_name => 'COPY_XBS_DENORM');
1321 RAISE;
1322
1323 end COPY_XBS_DENORM;
1324
1325
1326
1327
1328
1329 -- -----------------------------------------------------------------------
1330
1331 -- -----------------------------------------------------
1332 -- procedure POPULATE_RBS_DENORM
1333 -- -----------------------------------------------------
1334
1335 procedure POPULATE_RBS_DENORM(
1336 p_worker_id in number,
1337 p_denorm_type in varchar2,
1338 p_rbs_version_id in number
1339 ) is
1340
1341
1342 -- -----------------------------------------------------------------------
1343 --
1344 -- History
1345 -- 19-MAR-2004 aartola Created
1346 --
1347 --
1348 -- *** This API populates data for rbs data. This API calls the
1349 -- following procedures:
1350 --
1351 -- rbs_denorm
1352 -- merge_rbs_denorm
1353 --
1354 -- -----------------------------------------------------------------------
1355
1356
1357 -- -----------------------------------------------------
1358 -- Declare stataments --
1359
1360 l_process varchar2(30);
1361 l_extraction_type varchar2(30);
1362 l_denorm_type varchar2(30);
1363 l_fpm_upgrade varchar2(1);
1364 l_count NUMBER; -- Bug 13839343
1365 -- -----------------------------------------------------
1366
1367 begin
1368
1369 l_denorm_type := nvl(p_denorm_type, 'ALL');
1370
1371
1372 if g_pa_debug_mode = 'Y'
1373 then
1374 PJI_UTILS.WRITE2LOG(
1375 'PJI_PJP - Begin: populate_rbs_denorm -'
1376 || ' p_worker_id = '
1377 || p_worker_id,
1378 null,
1379 g_msg_level_proc_call
1380 );
1381 end if;
1382
1383 -- -----------------------------------------------------
1384 -- Variable assignments --
1385
1386 l_process := PJI_PJP_SUM_MAIN.g_process
1387 || to_char(p_worker_id);
1388
1389 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(
1390 l_process,
1391 'EXTRACTION_TYPE'
1392 );
1393
1394 -- ----------------------------------------
1395
1396 if g_pa_debug_mode = 'Y'
1397 then
1398 PJI_UTILS.WRITE2LOG(
1399 'PJI_PJP - Variables -'
1400 || ' l_process = '
1401 || l_process
1402 || ' l_extraction_type = '
1403 || l_extraction_type,
1404 null,
1405 g_msg_level_high_detail
1406 );
1407 end if;
1408
1409 -- ----------------------------------------
1410
1411 -- -----------------------------------------------------
1412 -- Online Mode --
1413
1414 if p_denorm_type = 'RBS'
1415
1416 then
1417
1418 -- ----------------------------------------------
1419 -- delete RBS slices for specific struct_version_id -- ###delete###
1420
1421 if g_pa_debug_mode = 'Y'
1422 then
1423 PJI_UTILS.WRITE2LOG(
1424 'PJI_PJP - Delete specific RBS slices -'
1425 || ' p_rbs_version_id = '
1426 || p_rbs_version_id,
1427 null,
1428 g_msg_level_high_detail
1429 );
1430 end if;
1431
1432 /* Commented for Bug 9099240
1433 delete
1434 from PA_RBS_DENORM
1435 where 1=1
1436 and STRUCT_VERSION_ID = p_rbs_version_id;
1437 */
1438 -- ----------------------------------------------
1439 -- Repopulate PA_RBS_DENORM for specific struct_version_id
1440
1441 -- OLAP START
1442
1443 PA_ppr_rollup_PVT.rbs_denorm ( p_rbs_version_id );
1444
1445 -- OLAP END
1446
1447 cleanup_rbs_denorm(
1448 p_worker_id,
1449 'ONLINE'
1450 );
1451
1452 rbs_denorm_online(
1453 p_worker_id,
1454 'ONLINE',
1455 p_rbs_version_id
1456 );
1457
1458 merge_rbs_denorm(
1459 p_worker_id,
1460 'ONLINE'
1461 );
1462
1463 -- don't delete contents from PJI_FP_AGGR_RBS_T
1464
1465 -- Sadiq will call cleanup_xbs_denorm(p_worker_id, 'ONLINE')
1466
1467
1468
1469 -- -----------------------------------------------------
1470 -- Bulk Mode --
1471
1472 elsif p_denorm_type = 'ALL'
1473
1474 then
1475 -- ----------------------------------------------
1476 -- process RBS hiearchies during PJP summarization
1477
1478 if (
1479 not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(
1480 l_process,
1481 'PJI_PJP_SUM_DENORM.POPULATE_RBS_DENORM(p_worker_id);'
1482 )
1483 )
1484 then
1485 return;
1486 end if;
1487
1488 -- -------------------------------------------------------
1489 -- cleanup interim tables
1490 cleanup_rbs_denorm(
1491 p_worker_id,
1492 l_extraction_type
1493 );
1494
1495 -- -------------------------------------------------------
1496 --Bug 4293903: In FULL mode if FPM upgrade is done treat as
1497 --incremental
1498 ----------------------------------------------------------
1499 l_fpm_upgrade := PJI_UTILS.GET_PARAMETER('PJI_FPM_UPGRADE');
1500
1501 IF (nvl(l_fpm_upgrade,'P') = 'C' and
1502 l_extraction_type = 'FULL') THEN
1503
1504 l_extraction_type := 'INCREMENTAL';
1505
1506 END IF;
1507
1508 -- Bulk Full mode --
1509
1510 select count(*) -- Added for bug 13839343
1511 into l_count
1512 from PA_RBS_DENORM
1513 where STRUCT_VERSION_ID in
1514 (
1515 select EVENT_OBJECT
1516 from PJI_PA_PROJ_EVENTS_LOG
1517 where 1=1
1518 and EVENT_TYPE = 'PJI_RBS_CHANGE'
1519 and WORKER_ID = P_WORKER_ID
1520 )
1521 and rownum=1;
1522
1523 if l_extraction_type = 'FULL'
1524
1525 then
1526
1527
1528
1529 -- PA_RBS_DENORM is empty, populate from scratch
1530
1531 --
1532 -- RBS normalization algorithm for all data in
1533 -- the structures tables.
1534 --
1535
1536 rbs_denorm(
1537 p_worker_id,
1538 l_extraction_type,
1539 null
1540 );
1541
1542 merge_rbs_denorm(
1543 p_worker_id,
1544 l_extraction_type
1545 );
1546
1547 cleanup_rbs_denorm(
1548 p_worker_id,
1549 l_extraction_type
1550 );
1551
1552 -- ----------------------------------
1553 -- Bulk Incremental/Partial mode --
1554
1555 elsif (
1556 l_extraction_type = 'INCREMENTAL'
1557 or
1558 l_extraction_type = 'PARTIAL'
1559 /*or
1560 l_extraction_type = 'RBS' commented for bug 10338478 */
1561 ) and l_count > 0 -- Bug 13839343
1562 then
1563 -- PA_RBS_DENORM contains data, repopulate a portion of it
1564
1565 /* Commented for bug 9099240
1566 delete
1567 from PA_RBS_DENORM
1568 where STRUCT_VERSION_ID in
1569 (
1570 select EVENT_OBJECT
1571 from PJI_PA_PROJ_EVENTS_LOG
1572 where 1=1
1573 and EVENT_TYPE = 'PJI_RBS_CHANGE'
1574 and WORKER_ID = P_WORKER_ID
1575 );
1576 */
1577
1578 --
1579 -- RBS normalization algorithm based on:
1580 --
1581 -- EVENT_TYPE = 'PJI_RBS_CHANGE' ==> EVENT_OBJECT = structure version
1582 --
1583 --
1584 --
1585 -- *** Use PJI_PA_PROJ_EVENTS_LOG *** to determine which RBS
1586 -- data to process. ***
1587 --
1588
1589 rbs_denorm(
1590 p_worker_id,
1591 l_extraction_type,
1592 null
1593 );
1594
1595 merge_rbs_denorm(
1596 p_worker_id,
1597 l_extraction_type
1598 );
1599
1600 /* Commented for bug 9099240
1601 cleanup_rbs_denorm(
1602 p_worker_id,
1603 l_extraction_type
1604 );
1605 */
1606 /* Added for Bug 10338478 Start */
1607 elsif l_extraction_type = 'RBS' or l_count = 0 then -- bug 13839343 added l_count check
1608 -- PA_RBS_DENORM contains data, repopulate a portion of it
1609
1610 delete
1611 from PA_RBS_DENORM
1612 where STRUCT_VERSION_ID in
1613 (
1614 select EVENT_OBJECT
1615 from PJI_PA_PROJ_EVENTS_LOG
1616 where 1=1
1617 and EVENT_TYPE = 'PJI_RBS_CHANGE'
1618 and WORKER_ID = P_WORKER_ID
1619 );
1620
1621 IF l_count = 0 THEN -- Added for bug 13839343
1622 l_extraction_type := 'RBS';
1623
1624 END IF;
1625
1626 rbs_denorm_rbs(
1627 p_worker_id,
1628 l_extraction_type,
1629 null
1630 );
1631
1632 merge_rbs_denorm(
1633 p_worker_id,
1634 l_extraction_type
1635 );
1636
1637 cleanup_rbs_denorm(
1638 p_worker_id,
1639 l_extraction_type
1640 );
1641 /* Added for Bug 10338478 End */
1642
1643 end if;
1644
1645 -- -----------------------------------------------------
1646
1647 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(
1648 l_process,
1649 'PJI_PJP_SUM_DENORM.POPULATE_RBS_DENORM(p_worker_id);'
1650 );
1651
1652 commit;
1653 -- -----------------------------------------------------
1654
1655 end if;
1656
1657 if g_pa_debug_mode = 'Y'
1658 then
1659 PJI_UTILS.WRITE2LOG(
1660 'PJI_PJP - End: populate_rbs_denorm',
1661 null,
1662 g_msg_level_proc_call
1663 );
1664 end if;
1665 -- -----------------------------------------------------
1666
1667 end POPULATE_RBS_DENORM;
1668
1669 -- -----------------------------------------------------------------------
1670 -- -----------------------------------------------------------------------
1671
1672
1673
1674
1675
1676
1677 -- -----------------------------------------------------
1678 -- procedure POPULATE_RBS_DENORM_UPGRADE
1679 -- -----------------------------------------------------
1680
1681 --
1682 -- NOTE: Since this API updates both PA_RBS_DENORM and PJI_RBS_DENORM, it
1683 -- should only be used during upgrade since change context between
1684 -- PA_RBS_DENORM and PJI_RBS_DENORM is lost for the given RBS version.
1685 --
1686
1687 -- 30-JUL-2004 jwhite Bug 3802762
1688 -- Because of savepoint issues with
1689 -- RES_LIST_TO_PLAN_RES_LIST api, commented-out
1690 -- commit in POPULATE_RBS_DENORM_UPGRADE
1691 --
1692 -- -----------------------------------------------------
1693
1694 procedure POPULATE_RBS_DENORM_UPGRADE(
1695 p_rbs_version_id in number,
1696 x_return_status out nocopy varchar2,
1697 x_msg_count out nocopy number,
1698 x_msg_data out nocopy varchar2
1699 ) is
1700
1701 -- -----------------------------------------------------
1702 -- Declare staments
1703
1704 l_worker_id number;
1705
1706 l_last_update_date date;
1707 l_last_updated_by number;
1708 l_creation_date date;
1709 l_created_by number;
1710 l_last_update_login number;
1711
1712
1713 -- -----------------------------------------------------
1714
1715 begin
1716
1717 l_worker_id := 1;
1718
1719
1720 if g_pa_debug_mode = 'Y'
1721 then
1722 PJI_UTILS.WRITE2LOG(
1723 'PJI_PJP - Begin: populate_rbs_denorm_upgrade -'
1724 || ' p_rbs_version_id = '
1725 || p_rbs_version_id,
1726 null,
1727 g_msg_level_proc_call
1728 );
1729 end if;
1730
1731 -- -----------------------------------------------------
1732
1733 delete
1734 from PA_RBS_DENORM
1735 where STRUCT_VERSION_ID = p_rbs_version_id;
1736
1737 delete
1738 from PJI_RBS_DENORM
1739 where STRUCT_VERSION_ID = p_rbs_version_id;
1740
1741 -- -----------------------------------------------------
1742 -- Populate PA_RBS_DENORM for a single RBS version (should not commit)
1743
1744 rbs_denorm(
1745 l_worker_id,
1746 'UPGRADE',
1747 p_rbs_version_id
1748 );
1749
1750 merge_rbs_denorm(
1751 l_worker_id,
1752 'UPGRADE'
1753 );
1754
1755 cleanup_rbs_denorm(
1756 l_worker_id,
1757 'UPGRADE'
1758 );
1759
1760 -- -----------------------------------------------------
1761
1762 l_last_update_date := sysdate;
1763 l_last_updated_by := FND_GLOBAL.USER_ID;
1764 l_creation_date := sysdate;
1765 l_created_by := FND_GLOBAL.USER_ID;
1766 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1767
1768
1769 -- -----------------------------------------------------
1770
1771 insert
1772 into PJI_RBS_DENORM
1773 (
1774 struct_version_id,
1775 sup_id,
1776 subro_id,
1777 sub_id,
1778 sup_level,
1779 sub_level,
1780 sub_leaf_flag,
1781 LAST_UPDATE_DATE,
1782 LAST_UPDATED_BY,
1783 CREATION_DATE,
1784 CREATED_BY,
1785 LAST_UPDATE_LOGIN
1786 )
1787 select
1788 struct_version_id,
1789 sup_id,
1790 subro_id,
1791 sub_id,
1792 sup_level,
1793 sub_level,
1794 sub_leaf_flag,
1795 l_last_update_date,
1796 l_last_updated_by,
1797 l_creation_date,
1798 l_created_by,
1799 l_last_update_login
1800 from PA_RBS_DENORM
1801 where STRUCT_VERSION_ID = p_rbs_version_id;
1802
1803 -- -----------------------------------------------------
1804
1805 x_return_status := FND_API.G_RET_STS_SUCCESS;
1806 x_msg_count := 0;
1807 x_msg_data := null;
1808
1809 -- -----------------------------------------------------
1810 -- Bug 3802762, 30-JUL-2004, jwhite -----------------------------
1811 -- Commented-out commit becuase of savepoint issue with a calling api
1812
1813 --commit;
1814
1815 -- End Bug 3802762, 30-JUL-2004, jwhite --------------------------
1816
1817 -- -----------------------------------------------------
1818 if g_pa_debug_mode = 'Y'
1819 then
1820 PJI_UTILS.WRITE2LOG(
1821 'PJI_PJP - End: populate_rbs_denorm_upgrade',
1822 null,
1823 g_msg_level_proc_call
1824 );
1825 end if;
1826 -- -----------------------------------------------------
1827
1828 end POPULATE_RBS_DENORM_UPGRADE;
1829
1830 -- -----------------------------------------------------------------------
1831 -- -----------------------------------------------------------------------
1832
1833
1834
1835
1836
1837
1838 -- -----------------------------------------------------------------------
1839
1840 procedure prg_denorm(
1841 p_worker_id in number,
1842 p_extraction_type in varchar2
1843 ) as
1844
1845 -- -----------------------------------------------------------------------
1846 --
1847 -- History
1848 -- 19-MAR-2004 aartola Created
1849 --
1850 --
1851 -- *** This API assumes that the following tables exist and that they are
1852 -- properly populated (no cycles, correct relationships, etc)
1853 --
1854 -- PA_PROJ_ELEMENT_VERSIONS
1855 -- PA_OBJECT_RELATIONSHIPS
1856 --
1857 -- Then, this API populates output values in the following existing
1858 -- table:
1859 -- PJI_FP_AGGR_XBS
1860 --
1861 -- When P_EXTRACTION_TYPE equals 'FULL', this API calls the following
1862 -- procedure:
1863 -- wbs_denorm
1864 --
1865 -- -----------------------------------------------------------------------
1866
1867 -- -----------------------------------------------------
1868 -- Declare statements --
1869
1870
1871 l_prg_temp_parent number;
1872 l_prg_temp_level number;
1873 l_prg_node_count number;
1874 l_prg_leaf_flag_id number;
1875 l_prg_leaf_flag varchar2(1);
1876
1877 l_prj_temp_parent number;
1878 l_prg_temp_rollup number;
1879 l_prg_temp_sup_emt number;
1880 l_prg_temp_sub_emt number;
1881
1882 l_prg_element_version_count number;
1883
1884 l_prg_dummy_rollup number;
1885 l_prg_dummy_task_flag varchar2(1);
1886
1887 l_project_id number;
1888
1889 -- -----------------------------------------------------
1890
1891 begin
1892
1893 -- (PRG node = program)
1894
1895
1896 if g_pa_debug_mode = 'Y'
1897 then
1898 PJI_UTILS.WRITE2LOG(
1899 'PJI_PJP - Begin: prg_denorm -'
1900 || ' p_worker_id = '
1901 || P_WORKER_ID
1902 || ' p_extraction_type = '
1903 || P_EXTRACTION_TYPE,
1904 null,
1905 g_msg_level_proc_call
1906 );
1907 end if;
1908 -- --------------------------------------------------------
1909 -- Get all PRG nodes from a certain level --
1910
1911 -- Look only at the data to be processed
1912 -- 1) FULL - All data
1913 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs
1914 -- table
1915 -- 2.1) PRG nodes changes
1916 -- 2.2) PRG nodes additions
1917
1918 l_project_id := null;
1919
1920 FOR PRG_NODE IN
1921 (
1922 select
1923 distinct
1924 PRG_LEVEL,
1925 PROJECT_ID,
1926 PROJ_ELEMENT_ID,
1927 ELEMENT_VERSION_ID,
1928 PARENT_STRUCTURE_VERSION_ID,
1929 PRG_GROUP
1930 from
1931 (
1932 select /*+ ordered */ -- all Projects in batch_map
1933 distinct
1934 1 prg_level,
1935 ver.project_id,
1936 ver.proj_element_id,
1937 ver.element_version_id,
1938 ver.parent_structure_version_id,
1939 ver.prg_group
1940 from
1941 PJI_PJP_PROJ_BATCH_MAP map,
1942 PA_PROJ_ELEMENT_VERSIONS ver
1943 where
1944 p_extraction_type in ('FULL', 'UPGRADE') and
1945 ver.object_type = 'PA_STRUCTURES' and
1946 ver.prg_group is null and
1947 map.worker_id = p_worker_id and
1948 map.PJI_PROJECT_STATUS is null and
1949 ver.project_id = map.project_id
1950 UNION ALL
1951 select /*+ ordered */
1952 -- Programs (UPGRADE only. In FULL, batch_map has necessary projects)
1953 distinct
1954 pvt_nodes1.prg_level,
1955 pvt_nodes1.project_id,
1956 pvt_nodes1.proj_element_id,
1957 pvt_nodes1.element_version_id,
1958 pvt_nodes1.parent_structure_version_id,
1959 pvt_nodes1.prg_group
1960 from
1961 (
1962 select /*+ ordered */
1963 distinct
1964 ver.prg_group
1965 from
1966 PA_PROJ_ELEMENT_VERSIONS ver,
1967 PJI_PJP_PROJ_BATCH_MAP map
1968 where
1969 p_extraction_type in ('FULL', 'UPGRADE') and
1970 ver.object_type = 'PA_STRUCTURES' and
1971 ver.prg_group is not null and
1972 map.worker_id = p_worker_id and
1973 map.PJI_PROJECT_STATUS is null and
1974 ver.project_id = map.project_id
1975 ) batch_map,
1976 PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
1977 where
1978 p_extraction_type in ('FULL', 'UPGRADE') and
1979 pvt_nodes1.object_type = 'PA_STRUCTURES' and
1980 pvt_nodes1.prg_group is not null and
1981 pvt_nodes1.prg_group = batch_map.prg_group
1982 UNION ALL
1983 select
1984 distinct
1985 pvt_nodes2.prg_level,
1986 pvt_nodes2.project_id,
1987 pvt_nodes2.proj_element_id,
1988 pvt_nodes2.element_version_id,
1989 pvt_nodes2.parent_structure_version_id,
1990 pvt_nodes2.prg_group
1991 from PA_PROJ_ELEMENT_VERSIONS pvt_nodes2,
1992 (
1993 select
1994 distinct
1995 decode( invert.id,
1996 1, i_log.event_object,
1997 2, i_log.attribute1
1998 ) event_object_id
1999 from PJI_PA_PROJ_EVENTS_LOG i_log,
2000 (
2001 select 1 id
2002 from dual
2003 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
2004 UNION ALL
2005 select 2 id
2006 from dual
2007 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
2008 ) invert
2009 where 1=1
2010 and i_log.worker_id = P_WORKER_ID
2011 and i_log.event_type = 'PRG_CHANGE'
2012 ) log11
2013 where 1=1
2014 and pvt_nodes2.prg_group = log11.event_object_id
2015 and pvt_nodes2.object_type = 'PA_STRUCTURES'
2016 and pvt_nodes2.prg_group IS NOT NULl /* 4904076 */
2017 UNION ALL
2018 select
2019 distinct
2020 1 prg_level,
2021 pvt_nodes3.project_id,
2022 pvt_nodes3.proj_element_id,
2023 pvt_nodes3.element_version_id,
2024 pvt_nodes3.parent_structure_version_id,
2025 pvt_nodes3.prg_group
2026 from PA_PROJ_ELEMENT_VERSIONS pvt_nodes3,
2027 (
2028 select
2029 distinct
2030 decode( invert.id,
2031 1, i_log.event_object,
2032 2, i_log.attribute1
2033 ) event_object_id
2034 from PJI_PA_PROJ_EVENTS_LOG i_log,
2035 (
2036 select 1 id
2037 from dual
2038 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
2039 UNION ALL
2040 select 2 id
2041 from dual
2042 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
2043 ) invert
2044 where 1=1
2045 and i_log.worker_id = P_WORKER_ID
2046 and i_log.event_type = 'PRG_CHANGE'
2047 and i_log.event_object = -1
2048 ) log22
2049 where 1=1
2050 and pvt_nodes3.project_id = log22.event_object_id -- log22.attribute2
2051 and pvt_nodes3.object_type = 'PA_STRUCTURES'
2052 and pvt_nodes3.prg_group is null
2053 )
2054 order by
2055 PRG_LEVEL DESC, /* This DESC order by will take care the hierarchy of projects*/
2056 PROJECT_ID,
2057 PROJ_ELEMENT_ID,
2058 ELEMENT_VERSION_ID,
2059 PARENT_STRUCTURE_VERSION_ID,
2060 PRG_GROUP
2061 ) LOOP
2062
2063 if (p_extraction_type = 'FULL' or p_extraction_type = 'UPGRADE') then
2064
2065 if (l_project_id is null) then
2066
2067 l_project_id := prg_node.PROJECT_ID;
2068
2069 elsif (l_project_id <> prg_node.PROJECT_ID) then
2070
2071 update PJI_PJP_PROJ_BATCH_MAP
2072 set PJI_PROJECT_STATUS = 'C'
2073 where WORKER_ID = p_worker_id and
2074 PROJECT_ID = l_project_id;
2075
2076 commit;
2077
2078 l_project_id := prg_node.PROJECT_ID;
2079
2080 end if;
2081
2082 end if;
2083
2084 IF PRG_NODE.prg_level > 1 -- ###prg_group_is_null###
2085 and
2086 PRG_NODE.prg_group is null
2087
2088 THEN
2089 if g_pa_debug_mode = 'Y'
2090 then
2091 PJI_UTILS.WRITE2LOG(
2092 'PJI_PJP - PRG Group is null Data Bug - element_version_id = '
2093 || PRG_NODE.element_version_id,
2094 null,
2095 g_msg_level_data_bug
2096 );
2097 end if;
2098 ELSE
2099
2100 -- --------------------------------------------------------
2101 -- Check program self --
2102
2103 -- Determine if the node to be inserted is a leaf
2104 -- If the node to be inserted has not been inserted before,
2105 -- then we know that the node is a leaf
2106 if PRG_NODE.prg_group is not null then
2107 select count(*)
2108 into l_prg_node_count
2109 from PJI_FP_AGGR_XBS pdt_count
2110 where 1=1
2111 and pdt_count.sup_id = PRG_NODE.element_version_id
2112 and pdt_count.worker_id = P_WORKER_ID
2113 and rownum = 1;
2114 else
2115 l_prg_node_count := 0;
2116 end if;
2117 -- l_prg_leaf_flag_id --
2118 if l_prg_node_count > 0
2119 then
2120 l_prg_leaf_flag_id := 0;
2121 else
2122 l_prg_leaf_flag_id := 1;
2123 end if;
2124
2125 -- l_prg_leaf_flag -- (business rule)
2126 if (
2127 PRG_NODE.element_version_id = PRG_NODE.element_version_id
2128 or
2129 l_prg_leaf_flag_id = 1
2130 )
2131 then
2132 l_prg_leaf_flag := 'Y';
2133 else
2134 l_prg_leaf_flag := 'N';
2135 end if;
2136
2137
2138 if g_pa_debug_mode = 'Y'
2139 then
2140 PJI_UTILS.WRITE2LOG(
2141 'PJI_PJP - Inserting PRG node self -'
2142 || ' sup_id = '
2143 || PRG_NODE.element_version_id,
2144 null,
2145 g_msg_level_low_detail
2146 );
2147 end if;
2148
2149
2150 -- Insert PRG node self --
2151 insert
2152 into PJI_FP_AGGR_XBS
2153 (
2154 struct_type,
2155 prg_group,
2156 struct_version_id,
2157 sup_project_id,
2158 sup_id,
2159 sup_emt_id,
2160 subro_id,
2161 sub_id,
2162 sub_emt_id,
2163 sup_level,
2164 sub_level,
2165 sub_rollup_id,
2166 sub_leaf_flag_id,
2167 sub_leaf_flag,
2168 status_id,
2169 worker_id
2170 )
2171 values (
2172 'PRG', -- structure type
2173 PRG_NODE.prg_group, -- prg group
2174 null, -- structure version id
2175 PRG_NODE.project_id, -- parent project id
2176 PRG_NODE.element_version_id, -- parent id
2177 PRG_NODE.proj_element_id, -- sup emt id
2178 null, -- immediate child id
2179 PRG_NODE.element_version_id, -- child id
2180 PRG_NODE.proj_element_id, -- sub emt_id
2181 PRG_NODE.prg_level, -- parent level
2182 PRG_NODE.prg_level, -- child level
2183 PRG_NODE.proj_element_id, -- child rollup id
2184 l_prg_leaf_flag_id, -- child leaf flag id
2185 l_prg_leaf_flag, -- child leaf flag
2186 'self', -- status id
2187 P_WORKER_ID -- worker id
2188 );
2189
2190 -- --------------------------------------------------------
2191 -- Check for PRG node's parents --
2192 -- Check only if the node is not a top most node (level = 1)
2193
2194 IF PRG_NODE.prg_level <> 1
2195
2196 THEN
2197
2198 FOR PRG_PARENT_NODE IN
2199 (
2200 select
2201 distinct
2202 prt_parent.object_id_from1,
2203 prt_parent.relationship_type,
2204 ver.prg_level
2205 from PA_OBJECT_RELATIONSHIPS prt_parent,
2206 PA_PROJ_ELEMENT_VERSIONS ver
2207 where 1=1
2208 and prt_parent.object_id_to1 = PRG_NODE.element_version_id
2209 and prt_parent.object_type_from = 'PA_TASKS'
2210 and prt_parent.object_type_to = 'PA_STRUCTURES'
2211 and (
2212 prt_parent.relationship_type = 'LF'
2213 or
2214 prt_parent.relationship_type = 'LW'
2215 )
2216 and ver.element_version_id = prt_parent.object_id_from1
2217 ) LOOP
2218
2219 select pvt_parent1.parent_structure_version_id,
2220 pvt_parent1.project_id,
2221 pvt_parent1.proj_element_id
2222 into l_prg_temp_parent,
2223 l_prj_temp_parent,
2224 l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
2225 from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
2226 where 1=1
2227 and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2228
2229 -- l_prg_dummy_task_flag -- ###dummy###
2230 select link_task_flag
2231 into l_prg_dummy_task_flag
2232 from pa_proj_elements
2233 where 1=1
2234 and proj_element_id = l_prg_dummy_rollup;
2235
2236 -- l_prg_temp_rollup
2237 if l_prg_dummy_task_flag = 'N'
2238
2239 then
2240 l_prg_temp_rollup := l_prg_dummy_rollup;
2241
2242 else
2243 /*
2244 select dt_ver1.proj_element_id
2245 into l_prg_temp_rollup
2246 from pa_object_relationships dt_rel,
2247 pa_proj_element_versions dt_ver1,
2248 pa_proj_element_versions dt_ver2
2249 where 1=1
2250 and dt_ver1.element_version_id = dt_rel.object_id_from1
2251 and dt_rel.object_type_from = 'PA_TASKS'
2252 and dt_rel.object_type_to = 'PA_TASKS'
2253 and dt_rel.object_id_to1 = dt_ver2.element_version_id
2254 and dt_ver2.proj_element_id = l_prg_dummy_rollup;
2255 */
2256
2257 -- Bug 3838523
2258 select dt_ver1.proj_element_id
2259 into l_prg_temp_rollup
2260 from pa_object_relationships dt_rel,
2261 pa_proj_element_versions dt_ver1
2262 where 1=1
2263 and dt_ver1.element_version_id = dt_rel.object_id_from1
2264 and dt_rel.object_type_from = 'PA_TASKS'
2265 and dt_rel.object_type_to = 'PA_TASKS'
2266 and dt_rel.object_id_to1 = PRG_PARENT_NODE.object_id_from1;
2267
2268 end if;
2269
2270 -- l_prg_temp_sup_emt --
2271 select pvt_parent4.proj_element_id
2272 into l_prg_temp_sup_emt
2273 from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
2274 where 1=1
2275 and pvt_parent4.element_version_id = l_prg_temp_parent;
2276
2277 -- l_prg_temp_sub_emt --
2278 select pvt_parent5.proj_element_id
2279 into l_prg_temp_sub_emt
2280 from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
2281 where 1=1
2282 and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
2283
2284 -- l_prg_leaf_flag --
2285 if (
2286 l_prg_temp_parent = PRG_NODE.element_version_id
2287 or
2288 l_prg_leaf_flag_id = 1
2289 )
2290 then
2291 l_prg_leaf_flag := 'Y';
2292 else
2293 l_prg_leaf_flag := 'N';
2294 end if;
2295
2296
2297 if g_pa_debug_mode = 'Y'
2298 then
2299 PJI_UTILS.WRITE2LOG(
2300 'PJI_PJP - Inserting PRG node parent -'
2301 || ' element_version_id = '
2302 || PRG_NODE.element_version_id
2303 || ' sup_id = '
2304 || l_prg_temp_parent
2305 || ' sub_rollup_id = '
2306 || l_prg_temp_rollup,
2307 null,
2308 g_msg_level_low_detail
2309 );
2310 end if;
2311
2312 -- Insert PRG node's parent --
2313 insert
2314 into PJI_FP_AGGR_XBS
2315 (
2316 struct_type,
2317 prg_group,
2318 struct_version_id,
2319 sup_project_id,
2320 sup_id,
2321 sup_emt_id,
2322 subro_id,
2323 sub_id,
2324 sub_emt_id,
2325 sup_level,
2326 sub_level,
2327 sub_rollup_id,
2328 sub_leaf_flag_id,
2329 sub_leaf_flag,
2330 relationship_type,
2331 status_id,
2332 worker_id
2333 )
2334 values (
2335 'PRG', -- structure type
2336 PRG_NODE.prg_group, -- prg group
2337 null, -- structure version id
2338 l_prj_temp_parent, -- parent project id
2339 l_prg_temp_parent, -- parent id
2340 l_prg_temp_sup_emt, -- sup emt_id
2341 PRG_NODE.proj_element_id, -- immediate child id
2342 PRG_NODE.element_version_id, -- child id
2343 l_prg_temp_sub_emt, -- sub emt_id
2344 PRG_PARENT_NODE.prg_level, -- parent level
2345 PRG_NODE.prg_level, -- child level
2346 l_prg_temp_rollup, -- child rollup id
2347 l_prg_leaf_flag_id, -- child leaf flag id
2348 l_prg_leaf_flag, -- child leaf flag
2349 PRG_PARENT_NODE.relationship_type, -- relationship type (new)
2350 'parent', -- status id
2351 P_WORKER_ID -- worker id
2352 );
2353
2354
2355 -- --------------------------------------------------------
2356 -- Check for PRG node's children --
2357 -- Filter nodes to see if the node has children
2358
2359 FOR PRG_CHILDREN_NODE IN
2360 (
2361 select
2362 distinct
2363 pdt_child.sup_id,
2364 pdt_child.sub_id,
2365 pdt_child.sub_leaf_flag_id
2366 from PJI_FP_AGGR_XBS pdt_child
2367 where 1=1
2368 and pdt_child.sup_id = PRG_NODE.element_version_id
2369 and pdt_child.sup_id <> pdt_child.sub_id
2370 and pdt_child.worker_id = P_WORKER_ID
2371 ) LOOP
2372
2373 -- l_prg_temp_level --
2374 select pdt_child1.sub_level
2375 into l_prg_temp_level
2376 from PJI_FP_AGGR_XBS pdt_child1
2377 where 1=1
2378 and pdt_child1.sup_id = PRG_CHILDREN_NODE.sub_id
2379 and pdt_child1.sup_id = pdt_child1.sub_id
2380 and pdt_child1.worker_id = P_WORKER_ID;
2381
2382 -- l_prj_temp_parent --
2383 select pvt_child1.project_id
2384 into l_prj_temp_parent
2385 from PA_PROJ_ELEMENT_VERSIONS pvt_child1
2386 where 1=1
2387 and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2388
2389 -- l_prg_temp_sup_emt --
2390 select pvt_child2.proj_element_id
2391 into l_prg_temp_sup_emt
2392 from PA_PROJ_ELEMENT_VERSIONS pvt_child2
2393 where 1=1
2394 and pvt_child2.element_version_id = l_prg_temp_parent;
2395
2396 -- l_prg_temp_sub_emt --
2397 select pvt_child3.proj_element_id
2398 into l_prg_temp_sub_emt
2399 from PA_PROJ_ELEMENT_VERSIONS pvt_child3
2400 where 1=1
2401 and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
2402
2403 -- l_prg_leaf_flag --
2404 if (
2405 l_prg_temp_parent = PRG_CHILDREN_NODE.sub_id
2406 or
2407 PRG_CHILDREN_NODE.sub_leaf_flag_id = 1
2408 )
2409 then
2410 l_prg_leaf_flag := 'Y';
2411 else
2412 l_prg_leaf_flag := 'N';
2413 end if;
2414
2415
2416 if g_pa_debug_mode = 'Y'
2417 then
2418 PJI_UTILS.WRITE2LOG(
2419 'PJI_PJP - Inserting PRG node child -'
2420 || ' sup_id = '
2421 || l_prg_temp_parent
2422 || ' sub_emt_id = '
2423 || l_prg_temp_sub_emt
2424 || ' sub_level = '
2425 || l_prg_temp_level,
2426 -- || PRG_CHILDREN_NODE.sup_id,
2427 null,
2428 g_msg_level_low_detail
2429 );
2430 end if;
2431
2432 -- Insert PRG node's child --
2433 insert
2434 into PJI_FP_AGGR_XBS
2435 (
2436 struct_type,
2437 prg_group,
2438 struct_version_id,
2439 sup_project_id,
2440 sup_id,
2441 sup_emt_id,
2442 subro_id,
2443 sub_id,
2444 sub_emt_id,
2445 sup_level,
2446 sub_level,
2447 sub_rollup_id,
2448 sub_leaf_flag_id,
2449 sub_leaf_flag,
2450 status_id,
2451 worker_id
2452 )
2453 values (
2454 'PRG', -- structure type
2455 PRG_NODE.prg_group, -- prg group
2456 null, -- struct_version_id
2457 l_prj_temp_parent, -- parent project id
2458 l_prg_temp_parent, -- parent id
2459 l_prg_temp_sup_emt, -- sup emt_id
2460 PRG_NODE.proj_element_id, -- immediate child id
2461 PRG_CHILDREN_NODE.sub_id, -- child id
2462 l_prg_temp_sub_emt, -- sub emt_id
2463 PRG_PARENT_NODE.prg_level, -- parent level
2464 l_prg_temp_level, -- child level
2465 null, -- child rollup id
2466 PRG_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
2467 l_prg_leaf_flag, -- child leaf flag
2468 'children', -- status id
2469 P_WORKER_ID -- worker id
2470 );
2471
2472 END LOOP; -- FOR PRG_CHILD_NODE
2473
2474 END LOOP; -- FOR PRG_PARENT_NODE
2475
2476 END IF; -- if l_prg_level_id <> 1
2477
2478 -- Call wbs_denorm
2479
2480 -- -----------------------------------------------------
2481 -- Do not call wbs_denorm procedures for WBS nodes
2482 -- that don't exist or call more than once.
2483
2484 select count(*)
2485 into l_prg_element_version_count
2486 from PA_PROJ_ELEMENT_VERSIONS cv
2487 where cv.parent_structure_version_id = PRG_NODE.element_version_id
2488 and rownum = 1;
2489
2490 if (
2491 1=1 -- P_EXTRACTION_TYPE = 'FULL'
2492 and
2493 l_prg_element_version_count > 0
2494 )
2495 then
2496
2497 if g_pa_debug_mode = 'Y'
2498 then
2499 PJI_UTILS.WRITE2LOG(
2500 'PJI_PJP - Calling wbs_denorm - element_version_id = '
2501 || PRG_NODE.element_version_id,
2502 null,
2503 g_msg_level_low_detail
2504 );
2505 end if;
2506
2507
2508 wbs_denorm(
2509 P_WORKER_ID,
2510 'FULL', -- P_EXTRACTION_TYPE,
2511 PRG_NODE.element_version_id
2512 -- PRG_NODE.proj_element_id
2513 );
2514
2515 else
2516
2517 if g_pa_debug_mode = 'Y'
2518 then
2519 PJI_UTILS.WRITE2LOG(
2520 'PJI_PJP - Not calling wbs_denorm, because WBS node does not exist - element_version_id = '
2521 || PRG_NODE.element_version_id,
2522 null,
2523 g_msg_level_low_detail
2524 );
2525 end if;
2526
2527 end if;
2528
2529 END IF; -- prg group is null
2530
2531 END LOOP; -- FOR PRG_NODE
2532
2533 if (p_extraction_type = 'FULL' or p_extraction_type = 'UPGRADE') then
2534
2535 update PJI_PJP_PROJ_BATCH_MAP
2536 set PJI_PROJECT_STATUS = null
2537 where WORKER_ID = p_worker_id and
2538 PJI_PROJECT_STATUS = 'C';
2539
2540 end if;
2541
2542 -- ------------------------------------------
2543 if g_pa_debug_mode = 'Y'
2544 then
2545 PJI_UTILS.WRITE2LOG(
2546 'PJI_PJP - End: prg_denorm',
2547 null,
2548 g_msg_level_proc_call
2549 );
2550 end if;
2551 -- ------------------------------------------
2552
2553
2554 end prg_denorm;
2555
2556 -- -----------------------------------------------------------------------
2557 -- -----------------------------------------------------------------------
2558
2559
2560
2561
2562 -- -----------------------------------------------------------------------
2563
2564 procedure wbs_denorm(
2565 p_worker_id in number,
2566 p_extraction_type in varchar2,
2567 p_wbs_version_id in number
2568 -- P_PRG_SUP_EMT_ID IN NUMBER, -- ###xbs###
2569 ) as
2570
2571 -- -----------------------------------------------------------------------
2572 --
2573 -- History
2574 -- 19-MAR-2004 aartola Created
2575 --
2576 --
2577 -- *** This API assumes that the following tables exist and that they are
2578 -- properly populated (no cycles, correct relationships, etc)
2579 --
2580 -- PA_PROJ_ELEMENT_VERSIONS
2581 -- PA_OBJECT_RELATIONSHIPS
2582 --
2583 -- Then, this API populates output values in the following existing
2584 -- table:
2585 -- PJI_FP_AGGR_XBS
2586 --
2587 -- -----------------------------------------------------------------------
2588
2589 -- -----------------------------------------------------
2590 -- Declare statements --
2591
2592 l_wbs_count number;
2593 l_wbs_temp_parent number;
2594 l_wbs_temp_level number;
2595 l_wbs_node_count number;
2596 l_wbs_leaf_flag_id number;
2597 l_wbs_leaf_flag varchar2(1);
2598
2599 l_wbs_temp_sup_emt number;
2600 l_wbs_temp_sub_emt number;
2601
2602 l_wbs_test_node number;
2603
2604 l_struct_emt_id number;
2605
2606 l_sharing_code varchar2(80); -- ###financial###
2607 l_project_id number;
2608
2609 -- -----------------------------------------------------
2610
2611 begin
2612
2613 -- (WBS node = task)
2614
2615 if g_pa_debug_mode = 'Y'
2616 then
2617 PJI_UTILS.WRITE2LOG(
2618 'PJI_PJP - Begin: wbs_denorm -'
2619 || ' p_worker_id = '
2620 || P_WORKER_ID
2621 || ' p_extraction_type = '
2622 || P_EXTRACTION_TYPE
2623 || ' p_wbs_version_id = '
2624 || P_WBS_VERSION_ID,
2625 null,
2626 g_msg_level_proc_call
2627 );
2628 end if;
2629
2630 -- -----------------------------------------------------
2631 -- l_struct_emt_id -- -- ###sup_emt###
2632 select
2633 distinct
2634 sup_emt_id
2635 into l_struct_emt_id
2636 from pji_fp_aggr_xbs
2637 where 1=1
2638 and struct_type = 'PRG'
2639 and sup_id = P_WBS_VERSION_ID
2640 and worker_id = P_WORKER_ID;
2641
2642
2643 -- -----------------------------------------------------
2644 -- l_sharing_code -- ###financial###
2645
2646 begin
2647 select projects.structure_sharing_code,projects.project_id
2648 into l_sharing_code,l_project_id
2649 from pa_projects_all projects,
2650 pa_proj_element_versions versions
2651 where 1=1
2652 and projects.project_id = versions.project_id
2653 and versions.object_type = 'PA_STRUCTURES'
2654 and versions.element_version_id = P_WBS_VERSION_ID;
2655 exception
2656 when no_data_found
2657 then
2658 l_sharing_code := 'PJI$NULL';
2659 l_project_id := -1;
2660 end;
2661
2662 if l_sharing_code is null
2663 then
2664 l_sharing_code := 'PJI$NULL';
2665 end if;
2666
2667
2668 -- -----------------------------------------------------
2669 -- Get all WBS nodes from a certain level and from a certain project --
2670
2671 -- Look only at the data to be processed
2672 -- 1) FULL - All data
2673 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs
2674 -- table
2675
2676 FOR WBS_NODE IN
2677 (
2678 select wvt_nodes.wbs_level,
2679 wvt_nodes.project_id,
2680 wvt_nodes.proj_element_id,
2681 wvt_nodes.element_version_id,
2682 wvt_nodes.parent_structure_version_id,
2683 wvt_nodes.financial_task_flag -- ###financial###
2684 from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
2685 where 1=1
2686 and (
2687 P_EXTRACTION_TYPE = 'FULL'
2688 or
2689 P_EXTRACTION_TYPE = 'UPGRADE'
2690 )
2691 and wvt_nodes.object_type = 'PA_TASKS'
2692 and exists -- ###dummy###
2693 (
2694 select 1
2695 from pa_proj_elements ele
2696 where link_task_flag = 'N'
2697 and ele.project_id = l_project_id
2698 and ele.proj_element_id = wvt_nodes.proj_element_id
2699 and rownum <= 1
2700 )
2701 and wvt_nodes.parent_structure_version_id = P_WBS_VERSION_ID
2702 and wvt_nodes.wbs_level is not null
2703 ORDER BY wbs_level DESC
2704 ) LOOP
2705
2706
2707 -- -----------------------------------------------------
2708 -- Check WBS node self --
2709
2710 -- Determine if the node to be inserted is a leaf
2711 -- If the node to be inserted has not been inserted before,
2712 -- then we know that the node is a leaf
2713
2714 -- Bug 12731239 starts
2715 BEGIN
2716 select 1 into l_wbs_node_count from dual
2717 where exists (select 1 from PJI_FP_AGGR_XBS wdt_count
2718 where wdt_count.sup_id = WBS_NODE.element_version_id
2719 and wdt_count.worker_id = P_WORKER_ID);
2720 EXCEPTION
2721 WHEN NO_DATA_FOUND THEN
2722 l_wbs_node_count := 0;
2723 END;
2724 -- Bug 12731239 ends
2725
2726 -- l_wbs_leaf_flag_id --
2727 if l_wbs_node_count > 0
2728 then
2729 l_wbs_leaf_flag_id := 0;
2730 else
2731 l_wbs_leaf_flag_id := 1;
2732 end if;
2733
2734 -- l_wbs_leaf_flag -- (business rule)
2735 if (
2736 WBS_NODE.proj_element_id = WBS_NODE.proj_element_id
2737 or
2738 l_wbs_leaf_flag_id = 1
2739 )
2740 then
2741 l_wbs_leaf_flag := 'Y';
2742 else
2743 l_wbs_leaf_flag := 'N';
2744 end if;
2745
2746
2747 if g_pa_debug_mode = 'Y'
2748 then
2749 PJI_UTILS.WRITE2LOG(
2750 'PJI_PJP - Inserting WBS node self - element_version_id = '
2751 || WBS_NODE.element_version_id,
2752 null,
2753 g_msg_level_low_detail
2754 );
2755 end if;
2756
2757 -- Insert WBS node self --
2758 insert
2759 into PJI_FP_AGGR_XBS
2760 (
2761 struct_type,
2762 prg_group,
2763 struct_emt_id,
2764 struct_version_id,
2765 sup_project_id,
2766 sup_id,
2767 sup_emt_id,
2768 subro_id,
2769 sub_id,
2770 sub_emt_id,
2771 sup_level,
2772 sub_level,
2773 sub_rollup_id,
2774 sub_leaf_flag_id,
2775 sub_leaf_flag,
2776 relationship_type,
2777 status_id,
2778 worker_id
2779 )
2780 values (
2781 'WBS', -- structure type
2782 null, -- prg group
2783 l_struct_emt_id, -- structure element id
2784 P_WBS_VERSION_ID, -- structure version id
2785 WBS_NODE.project_id, -- parent project id
2786 WBS_NODE.element_version_id, -- parent id
2787 WBS_NODE.proj_element_id, -- sup emt id
2788 null, -- immediate child id
2789 WBS_NODE.element_version_id, -- child id
2790 WBS_NODE.proj_element_id, -- sub emt_id
2791 WBS_NODE.wbs_level, -- parent level
2792 WBS_NODE.wbs_level, -- child level
2793 null, -- child rollup id
2794 l_wbs_leaf_flag_id, -- child leaf flag id
2795 l_wbs_leaf_flag, -- child leaf flag
2796 decode(l_sharing_code,
2797 'SHARE_FULL', 'WF',
2798 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
2799 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2800 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2801 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
2802 -- sub financial task flag -- ###financial###
2803 'self', -- status id
2804 P_WORKER_ID -- worker id
2805 );
2806
2807 -- --------------------------------------------------------
2808 -- Check for WBS node's parent --
2809 -- Check only if the node is not a top most node (level = 1)
2810
2811 IF WBS_NODE.wbs_level <> 1
2812 THEN
2813
2814
2815 -- -----------------------------------------------------
2816 -- Filter WBS nodes to those that have one and only one parent
2817 -- if not, the node is invalid. Cases with no parents or two parents
2818 -- have appeared with corrupted data
2819 -- Removed the call (was there for M QA builds) as disscussed with sadiq
2820
2821 -- l_wbs_temp_parent --
2822 select wrt_parent.object_id_from1
2823 into l_wbs_temp_parent
2824 from PA_OBJECT_RELATIONSHIPS wrt_parent
2825 where 1=1
2826 and wrt_parent.object_id_to1 = WBS_NODE.element_version_id
2827 and wrt_parent.object_type_from = 'PA_TASKS'
2828 and wrt_parent.object_type_to = 'PA_TASKS'
2829 and wrt_parent.relationship_type = 'S';
2830
2831 -- l_wbs_temp_sup_emt --
2832 select wvt_parent1.proj_element_id
2833 into l_wbs_temp_sup_emt
2834 from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
2835 where 1=1
2836 and wvt_parent1.element_version_id = l_wbs_temp_parent;
2837
2838 -- l_wbs_temp_sub_emt --
2839 select wvt_parent2.proj_element_id
2840 into l_wbs_temp_sub_emt
2841 from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
2842 where 1=1
2843 and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
2844
2845 -- l_wbs_leaf_flag --
2846 if (
2847 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
2848 or
2849 l_wbs_leaf_flag_id = 1
2850 )
2851 then
2852 l_wbs_leaf_flag := 'Y';
2853 else
2854 l_wbs_leaf_flag := 'N';
2855 end if;
2856
2857
2858 if g_pa_debug_mode = 'Y'
2859 then
2860 PJI_UTILS.WRITE2LOG(
2861 'PJI_PJP - Inserting WBS node parent - l_wbs_temp_parent = '
2862 || l_wbs_temp_parent,
2863 null,
2864 g_msg_level_low_detail
2865 );
2866 end if;
2867
2868
2869 -- Insert WBS node's parent --
2870 insert
2871 into PJI_FP_AGGR_XBS
2872 (
2873 struct_type,
2874 prg_group,
2875 struct_emt_id,
2876 struct_version_id,
2877 sup_project_id,
2878 sup_id,
2879 sup_emt_id,
2880 subro_id,
2881 sub_id,
2882 sub_emt_id,
2883 sup_level,
2884 sub_level,
2885 sub_rollup_id,
2886 sub_leaf_flag_id,
2887 sub_leaf_flag,
2888 relationship_type,
2889 status_id,
2890 worker_id
2891 )
2892 values (
2893 'WBS', -- structure type
2894 null, -- prg group
2895 l_struct_emt_id, -- structure element id
2896 P_WBS_VERSION_ID, -- structure version id
2897 WBS_NODE.project_id, -- parent project id
2898 l_wbs_temp_parent, -- parent id
2899 l_wbs_temp_sup_emt, -- sup_emt_id
2900 WBS_NODE.proj_element_id, -- immediate child id
2901 WBS_NODE.element_version_id, -- child id
2902 l_wbs_temp_sub_emt, -- sub_emt_id
2903 WBS_NODE.wbs_level -1, -- parent level
2904 WBS_NODE.wbs_level, -- child level
2905 null, -- child rollup id
2906 l_wbs_leaf_flag_id, -- child leaf flag id
2907 l_wbs_leaf_flag, -- child leaf flag
2908 decode(l_sharing_code,
2909 'SHARE_FULL', 'WF',
2910 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
2911 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2912 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2913 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
2914 -- sub financial task flag -- ###financial###
2915 'parent', -- status id
2916 P_WORKER_ID -- worker id
2917 );
2918
2919 -- --------------------------------------------------------
2920 -- Check for WBS node's children --
2921 -- Filter nodes to see if the node has children
2922
2923 FOR WBS_CHIlDREN_NODE IN
2924 (
2925 select wdt_child.sup_id,
2926 wdt_child.sub_id,
2927 wdt_child.sub_leaf_flag_id,
2928 wdt_child.relationship_type
2929 from PJI_FP_AGGR_XBS wdt_child
2930 where 1=1
2931 and wdt_child.sup_id = WBS_NODE.element_version_id
2932 and wdt_child.sup_id <> wdt_child.sub_id
2933 and wdt_child.worker_id = P_WORKER_ID
2934 ) LOOP
2935
2936 -- l_wbs_temp_level --
2937 select wdt_child1.sub_level
2938 into l_wbs_temp_level
2939 from PJI_FP_AGGR_XBS wdt_child1
2940 where 1=1
2941 and wdt_child1.sup_id = WBS_CHILDREN_NODE.sub_id
2942 and wdt_child1.sup_id = wdt_child1.sub_id
2943 and wdt_child1.worker_id = P_WORKER_ID;
2944
2945 -- l_wbs_temp_sup_emt --
2946 select wvt_child1.proj_element_id
2947 into l_wbs_temp_sup_emt
2948 from PA_PROJ_ELEMENT_VERSIONS wvt_child1
2949 where 1=1
2950 and wvt_child1.element_version_id = l_wbs_temp_parent;
2951
2952 -- l_wbs_temp_sub_emt --
2953 select wvt_child2.proj_element_id
2954 into l_wbs_temp_sub_emt
2955 from PA_PROJ_ELEMENT_VERSIONS wvt_child2
2956 where 1=1
2957 and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
2958
2959 -- l_wbs_leaf_flag --
2960 if (
2961 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
2962 or
2963 WBS_CHILDREN_NODE.sub_leaf_flag_id = 1
2964 )
2965 then
2966 l_wbs_leaf_flag := 'Y';
2967 else
2968 l_wbs_leaf_flag := 'N';
2969 end if;
2970
2971
2972 if g_pa_debug_mode = 'Y'
2973 then
2974 PJI_UTILS.WRITE2LOG(
2975 'PJI_PJP - Inserting WBS node child - sup_id = '
2976 || WBS_CHILDREN_NODE.sup_id,
2977 null,
2978 g_msg_level_low_detail
2979 );
2980 end if;
2981
2982 -- Insert WBS node's child --
2983 insert
2984 into PJI_FP_AGGR_XBS
2985 (
2986 struct_type,
2987 prg_group,
2988 struct_emt_id,
2989 struct_version_id,
2990 sup_project_id,
2991 sup_id,
2992 sup_emt_id,
2993 subro_id,
2994 sub_id,
2995 sub_emt_id,
2996 sup_level,
2997 sub_level,
2998 sub_rollup_id,
2999 sub_leaf_flag_id,
3000 sub_leaf_flag,
3001 relationship_type,
3002 status_id,
3003 worker_id
3004 )
3005 values (
3006 'WBS', -- structure type
3007 null, -- prg group
3008 l_struct_emt_id, -- structure element id
3009 P_WBS_VERSION_ID, -- structure version id
3010 WBS_NODE.project_id, -- parent project id
3011 l_wbs_temp_parent, -- parent id
3012 l_wbs_temp_sup_emt, -- sup emt_id
3013 WBS_NODE.proj_element_id, -- immediate child id
3014 WBS_CHILDREN_NODE.sub_id, -- child id
3015 l_wbs_temp_sub_emt, -- sub emt_id
3016 WBS_NODE.wbs_level - 1, -- parent level
3017 l_wbs_temp_level, -- child level
3018 null, -- child rollup id
3019 WBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag id
3020 l_wbs_leaf_flag, -- child leaf flag
3021 decode(l_sharing_code,
3022 'SHARE_FULL', 'WF',
3023 'SHARE_PARTIAL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'WF', 'LW'),
3024 'SPLIT_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
3025 'SPLIT_NO_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
3026 'PJI$NULL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW')),
3027 -- sub financial task flag -- ###financial###
3028 'children', -- status id
3029 P_WORKER_ID -- worker id
3030 );
3031
3032 END LOOP; -- FOR WBS_CHIlDREN_NODE
3033
3034 ELSE
3035
3036 -- l_wbs_leaf_flag -- (business rule)
3037 if (
3038 P_WBS_VERSION_ID = WBS_NODE.element_version_id
3039 or
3040 l_wbs_leaf_flag_id = 1
3041 )
3042 then
3043 l_wbs_leaf_flag := 'Y';
3044 else
3045 l_wbs_leaf_flag := 'N';
3046 end if;
3047
3048
3049 if g_pa_debug_mode = 'Y'
3050 then
3051 PJI_UTILS.WRITE2LOG(
3052 'PJI_PJP - Inserting XBS node self - sup_id = '
3053 || P_WBS_VERSION_ID,
3054 null,
3055 g_msg_level_low_detail
3056 );
3057 end if;
3058
3059 -- Insert XBS node --
3060 insert
3061 into PJI_FP_AGGR_XBS
3062 (
3063 struct_type,
3064 prg_group,
3065 struct_version_id,
3066 sup_project_id,
3067 sup_id,
3068 sup_emt_id,
3069 subro_id,
3070 sub_id,
3071 sub_emt_id,
3072 sup_level,
3073 sub_level,
3074 sub_rollup_id,
3075 sub_leaf_flag_id,
3076 sub_leaf_flag,
3077 relationship_type,
3078 status_id,
3079 worker_id
3080 )
3081 values (
3082 'XBS', -- structure type
3083 null, -- prg group
3084 P_WBS_VERSION_ID, -- structure version id
3085 WBS_NODE.project_id, -- parent project id
3086 P_WBS_VERSION_ID, -- parent id
3087 l_struct_emt_id, -- sup emt id
3088 null, -- immediate child id
3089 WBS_NODE.element_version_id, -- child id
3090 WBS_NODE.proj_element_id, -- sub emt_id
3091 0, -- parent level (l_wbs_level_id)
3092 WBS_NODE.wbs_level, -- child level
3093 null, -- child rollup id
3094 l_wbs_leaf_flag_id, -- child leaf flag id
3095 l_wbs_leaf_flag, -- child leaf flag
3096 decode(l_sharing_code,
3097 'SHARE_FULL', 'WF',
3098 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
3099 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
3100 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
3101 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
3102 -- sub financial task flag -- ###financial###
3103 'self', -- status id
3104 P_WORKER_ID -- worker id
3105 );
3106
3107
3108 END IF; -- IF l_wbs_level_id <> 1
3109
3110 END LOOP; -- FOR WBS_NODE
3111
3112
3113 -- ----------------------------------------------
3114
3115 if g_pa_debug_mode = 'Y'
3116 then
3117 PJI_UTILS.WRITE2LOG(
3118 'PJI_PJP - End: wbs_denorm',
3119 null,
3120 g_msg_level_proc_call
3121 );
3122 end if;
3123
3124 -- -----------------------------------------------
3125
3126 end wbs_denorm;
3127
3128 -- -----------------------------------------------------------------------
3129 -- -----------------------------------------------------------------------
3130
3131
3132
3133
3134
3135
3136 -- -----------------------------------------------------------------------
3137
3138 procedure prg_denorm_online(
3139 p_worker_id in number,
3140 p_extraction_type in varchar2,
3141 p_prg_group_id in number,
3142 p_wbs_version_id in number
3143 ) as
3144
3145 -- -----------------------------------------------------------------------
3146 --
3147 -- History
3148 -- 19-MAR-2004 aartola Created
3149 --
3150 --
3151 -- *** This API assumes that the following tables exist and that they are
3152 -- properly populated (no cycles, correct relationships, etc)
3153 --
3154 -- PA_PROJ_ELEMENT_VERSIONS
3155 -- PA_OBJECT_RELATIONSHIPS
3156 --
3157 -- Then, this API populates output values in the following existing
3158 -- table:
3159 -- PJI_FP_AGGR_XBS
3160 --
3161 -- When P_EXTRACTION_TYPE equals 'FULL', this API calls the following
3162 -- procedure:
3163 -- wbs_denorm
3164 --
3165 -- -----------------------------------------------------------------------
3166
3167 -- -----------------------------------------------------
3168 -- Declare statements --
3169
3170 l_prg_level_id number;
3171 l_prg_temp_parent number;
3172 l_prg_temp_level number;
3173 l_prg_node_count number;
3174 l_prg_leaf_flag_id number;
3175 l_prg_leaf_flag varchar2(1);
3176
3177 l_prj_temp_parent number;
3178 l_prg_temp_rollup number;
3179 l_prg_temp_sup_emt number;
3180 l_prg_temp_sub_emt number;
3181
3182 l_prg_element_version_count number;
3183
3184 l_prg_dummy_rollup number;
3185 l_prg_dummy_task_flag varchar2(1);
3186
3187 -- -----------------------------------------------------
3188
3189 begin
3190
3191 -- (PRG node = program)
3192
3193 if g_pa_debug_mode = 'Y'
3194 then
3195 PJI_UTILS.WRITE2LOG(
3196 'PJI_PJP - Begin: prg_denorm_online -'
3197 || ' p_worker_id = '
3198 || P_WORKER_ID
3199 || ' p_extraction_type = '
3200 || P_EXTRACTION_TYPE
3201 || ' p_prg_group_id = '
3202 || p_prg_group_id
3203 || ' p_wbs_version_id = '
3204 || p_wbs_version_id,
3205 null,
3206 g_msg_level_proc_call
3207 );
3208 end if;
3209
3210 -- -----------------------------------------------------
3211
3212 -- Get deepest PRG node level --
3213
3214 -- Look only at the data to be processed
3215
3216 if p_prg_group_id is not null
3217 then
3218
3219 select max(pvt_level.prg_level)
3220 into l_prg_level_id
3221 from PA_PROJ_ELEMENT_VERSIONS pvt_level
3222 where 1=1
3223 and pvt_level.object_type = 'PA_STRUCTURES'
3224 and pvt_level.prg_group IS NOT NULL /* 4904076 */
3225 and prg_group = p_prg_group_id;
3226
3227 else
3228
3229 l_prg_level_id := 1; -- and element_version_id = p_wbs_version_id;
3230
3231 end if;
3232
3233 -- --------------------------------------------------------
3234 -- PRG nodes with no prg_level (is null) are valid. Therefore,
3235 -- treat these nodes as level 1.
3236
3237 if l_prg_level_id is null
3238 then
3239 l_prg_level_id := 1;
3240 end if;
3241
3242 -- --------------------------------------------------------
3243
3244 LOOP
3245
3246 if g_pa_debug_mode = 'Y'
3247 then
3248 PJI_UTILS.WRITE2LOG(
3249 'PJI_PJP - PRG Inserts - l_prg_level_id = '
3250 || l_prg_level_id,
3251 null,
3252 g_msg_level_high_detail
3253 );
3254 end if;
3255
3256 -- --------------------------------------------------------
3257 -- Get all PRG nodes from a certain level --
3258
3259 -- Look only at the data to be processed
3260
3261
3262 FOR PRG_NODE IN
3263 (
3264 select
3265 distinct
3266 pvt_nodes1.project_id,
3267 pvt_nodes1.proj_element_id,
3268 pvt_nodes1.element_version_id,
3269 pvt_nodes1.parent_structure_version_id,
3270 pvt_nodes1.prg_group,
3271 pvt_nodes1.prg_level /*4625702*/
3272 from PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
3273 where 1=1
3274 and pvt_nodes1.object_type = 'PA_STRUCTURES'
3275 and pvt_nodes1.element_version_id = p_wbs_version_id
3276 ) LOOP
3277
3278
3279 IF l_prg_level_id > 1 -- ###prg_group_is_null###
3280 and
3281 PRG_NODE.prg_group is null
3282
3283 THEN
3284 if g_pa_debug_mode = 'Y'
3285 then
3286 PJI_UTILS.WRITE2LOG(
3287 'PJI_PJP - PRG Group is null Data Bug - element_version_id = '
3288 || PRG_NODE.element_version_id,
3289 null,
3290 g_msg_level_data_bug
3291 );
3292 end if;
3293 ELSE
3294
3295 -- --------------------------------------------------------
3296 -- Check program self --
3297
3298 -- Determine if the node to be inserted is a leaf
3299 -- If the node to be inserted has not been inserted before,
3300 -- then we know that the node is a leaf
3301
3302 select count(*)
3303 into l_prg_node_count
3304 from PJI_FP_AGGR_XBS_T pdt_count
3305 where 1=1
3306 and pdt_count.sup_id = PRG_NODE.element_version_id
3307 and pdt_count.worker_id = P_WORKER_ID
3308 and rownum = 1;
3309
3310 -- l_prg_leaf_flag_id --
3311 if l_prg_node_count > 0
3312 then
3313 l_prg_leaf_flag_id := 0;
3314 else
3315 l_prg_leaf_flag_id := 1;
3316 end if;
3317
3318 -- l_prg_leaf_flag -- (business rule)
3319 if (
3320 PRG_NODE.element_version_id = PRG_NODE.element_version_id
3321 or
3322 l_prg_leaf_flag_id = 1
3323 )
3324 then
3325 l_prg_leaf_flag := 'Y';
3326 else
3327 l_prg_leaf_flag := 'N';
3328 end if;
3329
3330
3331 if g_pa_debug_mode = 'Y'
3332 then
3333 PJI_UTILS.WRITE2LOG(
3334 'PJI_PJP - Inserting PRG node self - sup_id = '
3335 || PRG_NODE.element_version_id,
3336 null,
3337 g_msg_level_low_detail
3338 );
3339 end if;
3340
3341
3342 -- Insert PRG node self --
3343 insert
3344 into PJI_FP_AGGR_XBS_T
3345 (
3346 struct_type,
3347 prg_group,
3348 struct_version_id,
3349 sup_project_id,
3350 sup_id,
3351 sup_emt_id,
3352 subro_id,
3353 sub_id,
3354 sub_emt_id,
3355 sup_level,
3356 sub_level,
3357 sub_rollup_id,
3358 sub_leaf_flag_id,
3359 sub_leaf_flag,
3360 status_id,
3361 worker_id
3362 )
3363 values (
3364 'PRG', -- structure type
3365 PRG_NODE.prg_group, -- prg group
3366 null, -- structure version id
3367 PRG_NODE.project_id, -- parent project id
3368 PRG_NODE.element_version_id, -- parent id
3369 PRG_NODE.proj_element_id, -- sup emt id
3370 null, -- immediate child id
3371 PRG_NODE.element_version_id, -- child id
3372 PRG_NODE.proj_element_id, -- sub emt_id
3373 nvl(PRG_NODE.prg_level,1) , -- 4625702 l_prg_level_id, -- parent level
3374 nvl(PRG_NODE.prg_level,1) , -- 4625702 l_prg_level_id, -- child level
3375 PRG_NODE.proj_element_id, -- child rollup id
3376 l_prg_leaf_flag_id, -- child leaf flag id
3377 l_prg_leaf_flag, -- child leaf flag
3378 'self', -- status id
3379 P_WORKER_ID -- worker id
3380 );
3381
3382
3383 -- --------------------------------------------------------
3384 -- Check for PRG node's parents --
3385 -- Check only if the node is not a top most node (level = 1)
3386
3387 IF l_prg_level_id <> 1
3388
3389 THEN
3390
3391 FOR PRG_PARENT_NODE IN
3392 (
3393 select
3394 distinct
3395 prt_parent.object_id_from1,
3396 prt_parent.relationship_type,
3397 ver.prg_level
3398 from PA_OBJECT_RELATIONSHIPS prt_parent,
3399 PA_PROJ_ELEMENT_VERSIONS ver
3400 where 1=1
3401 and prt_parent.object_id_to1 = PRG_NODE.element_version_id
3402 and prt_parent.object_type_from = 'PA_TASKS'
3403 and prt_parent.object_type_to = 'PA_STRUCTURES'
3404 and (
3405 prt_parent.relationship_type = 'LF'
3406 or
3407 prt_parent.relationship_type = 'LW'
3408 )
3409 and ver.element_version_id = prt_parent.object_id_from1
3410 ) LOOP
3411
3412 -- l_prg_temp_parent --
3413 -- l_prj_temp_parent --
3414 -- l_prg_dummy_rollup --
3415 select pvt_parent1.parent_structure_version_id,
3416 pvt_parent1.project_id,
3417 pvt_parent1.proj_element_id
3418 into l_prg_temp_parent,
3419 l_prj_temp_parent,
3420 l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
3421 from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
3422 where 1=1
3423 and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3424
3425 -- l_prg_dummy_task_flag -- ###dummy###
3426 select link_task_flag
3427 into l_prg_dummy_task_flag
3428 from pa_proj_elements
3429 where 1=1
3430 and proj_element_id = l_prg_dummy_rollup;
3431
3432 -- l_prg_temp_rollup
3433 if l_prg_dummy_task_flag = 'N'
3434
3435 then
3436 l_prg_temp_rollup := l_prg_dummy_rollup;
3437
3438 else
3439 select dt_ver1.proj_element_id
3440 into l_prg_temp_rollup
3441 from pa_object_relationships dt_rel,
3442 pa_proj_element_versions dt_ver1
3443 /* commented for bug 3838523 pa_proj_element_versions dt_ver2*/
3444 where 1=1
3445 and dt_ver1.element_version_id = dt_rel.object_id_from1
3446 and dt_rel.object_type_from = 'PA_TASKS'
3447 and dt_rel.object_type_to = 'PA_TASKS'
3448 and dt_rel.object_id_to1 = PRG_PARENT_NODE.object_id_from1;
3449
3450 /* Commented for bug 3838523 and added above line
3451 and dt_rel.object_id_to1 = dt_ver2.element_version_id
3452 and dt_ver2.proj_element_id = l_prg_dummy_rollup; */
3453 end if;
3454
3455 -- l_prg_temp_sup_emt --
3456 select pvt_parent4.proj_element_id
3457 into l_prg_temp_sup_emt
3458 from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
3459 where 1=1
3460 and pvt_parent4.element_version_id = l_prg_temp_parent;
3461
3462 -- l_prg_temp_sub_emt --
3463 select pvt_parent5.proj_element_id
3464 into l_prg_temp_sub_emt
3465 from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
3466 where 1=1
3467 and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
3468
3469 -- l_prg_leaf_flag --
3470 if (
3471 l_prg_temp_parent = PRG_NODE.element_version_id
3472 or
3473 l_prg_leaf_flag_id = 1
3474 )
3475 then
3476 l_prg_leaf_flag := 'Y';
3477 else
3478 l_prg_leaf_flag := 'N';
3479 end if;
3480
3481
3482 if g_pa_debug_mode = 'Y'
3483 then
3484 PJI_UTILS.WRITE2LOG(
3485 'PJI_PJP - Inserting PRG node parent -'
3486 || ' element_version_id = '
3487 || PRG_NODE.element_version_id
3488 || ' sup_id = '
3489 || l_prg_temp_parent
3490 || ' sub_rollup_id = '
3491 || l_prg_temp_rollup,
3492 null,
3493 g_msg_level_low_detail
3494 );
3495 end if;
3496
3497
3498 -- Insert PRG node's parent --
3499 insert
3500 into PJI_FP_AGGR_XBS_T
3501 (
3502 struct_type,
3503 prg_group,
3504 struct_version_id,
3505 sup_project_id,
3506 sup_id,
3507 sup_emt_id,
3508 subro_id,
3509 sub_id,
3510 sub_emt_id,
3511 sup_level,
3512 sub_level,
3513 sub_rollup_id,
3514 sub_leaf_flag_id,
3515 sub_leaf_flag,
3516 relationship_type,
3517 status_id,
3518 worker_id
3519 )
3520 values (
3521 'PRG', -- structure type
3522 PRG_NODE.prg_group, -- prg group
3523 null, -- structure version id
3524 l_prj_temp_parent, -- parent project id
3525 l_prg_temp_parent, -- parent id
3526 l_prg_temp_sup_emt, -- sup emt_id
3527 PRG_NODE.proj_element_id, -- immediate child id
3528 PRG_NODE.element_version_id, -- child id
3529 l_prg_temp_sub_emt, -- sub emt_id
3530 PRG_PARENT_NODE.prg_level, -- parent level
3531 l_prg_level_id, -- child level
3532 l_prg_temp_rollup, -- child rollup id
3533 l_prg_leaf_flag_id, -- child leaf flag id
3534 l_prg_leaf_flag, -- child leaf flag
3535 PRG_PARENT_NODE.relationship_type, -- relationship type (new)
3536 'parent', -- status id
3537 P_WORKER_ID -- worker id
3538 );
3539
3540
3541 -- --------------------------------------------------------
3542 -- Check for PRG node's children --
3543 -- Filter nodes to see if the node has children
3544
3545 FOR PRG_CHILDREN_NODE IN
3546 (
3547 select
3548 distinct
3549 pdt_child.sup_id,
3550 pdt_child.sub_id,
3551 pdt_child.sub_leaf_flag_id
3552 from PJI_FP_AGGR_XBS_T pdt_child
3553 where 1=1
3554 and pdt_child.sup_id = PRG_NODE.element_version_id
3555 and pdt_child.sup_id <> pdt_child.sub_id
3556 and pdt_child.worker_id = P_WORKER_ID
3557 ) LOOP
3558
3559 -- l_prg_temp_level --
3560 select pdt_child1.sub_level
3561 into l_prg_temp_level
3562 from PJI_FP_AGGR_XBS_T pdt_child1
3563 where 1=1
3564 --and pdt_child1.struct_type = 'PRG'
3565 and pdt_child1.sup_id = PRG_CHILDREN_NODE.sub_id
3566 and pdt_child1.sub_id = PRG_CHILDREN_NODE.sub_id
3567 and pdt_child1.worker_id = P_WORKER_ID;
3568
3569 -- l_prj_temp_parent --
3570 select pvt_child1.project_id
3571 into l_prj_temp_parent
3572 from PA_PROJ_ELEMENT_VERSIONS pvt_child1
3573 where 1=1
3574 and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3575
3576 -- l_prg_temp_sup_emt --
3577 select pvt_child2.proj_element_id
3578 into l_prg_temp_sup_emt
3579 from PA_PROJ_ELEMENT_VERSIONS pvt_child2
3580 where 1=1
3581 and pvt_child2.element_version_id = l_prg_temp_parent;
3582
3583 -- l_prg_temp_sub_emt --
3584 select pvt_child3.proj_element_id
3585 into l_prg_temp_sub_emt
3586 from PA_PROJ_ELEMENT_VERSIONS pvt_child3
3587 where 1=1
3588 and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
3589
3590 -- l_prg_leaf_flag --
3591 if (
3592 l_prg_temp_parent = PRG_CHILDREN_NODE.sub_id
3593 or
3594 PRG_CHILDREN_NODE.sub_leaf_flag_id = 1
3595 )
3596 then
3597 l_prg_leaf_flag := 'Y';
3598 else
3599 l_prg_leaf_flag := 'N';
3600 end if;
3601
3602
3603 if g_pa_debug_mode = 'Y'
3604 then
3605 PJI_UTILS.WRITE2LOG(
3606 'PJI_PJP - Inserting PRG node child -'
3607 || ' element_version_id = '
3608 || PRG_NODE.element_version_id
3609 || ' sup_id = '
3610 || l_prg_temp_parent
3611 || ' sub_emt_id = '
3612 || l_prg_temp_sub_emt
3613 || ' sub_level = '
3614 || l_prg_temp_level,
3615 -- || PRG_CHILDREN_NODE.sup_id,
3616 null,
3617 g_msg_level_low_detail
3618 );
3619 end if;
3620
3621 -- Insert PRG node's child --
3622 insert
3623 into PJI_FP_AGGR_XBS_T
3624 (
3625 struct_type,
3626 prg_group,
3627 struct_version_id,
3628 sup_project_id,
3629 sup_id,
3630 sup_emt_id,
3631 subro_id,
3632 sub_id,
3633 sub_emt_id,
3634 sup_level,
3635 sub_level,
3636 sub_rollup_id,
3637 sub_leaf_flag_id,
3638 sub_leaf_flag,
3639 status_id,
3640 worker_id
3641 )
3642 values (
3643 'PRG', -- structure type
3644 PRG_NODE.prg_group, -- prg group
3645 null, -- struct_version_id
3646 l_prj_temp_parent, -- parent project id
3647 l_prg_temp_parent, -- parent id
3648 l_prg_temp_sup_emt, -- sup emt id
3649 PRG_NODE.proj_element_id, -- immediate child id
3650 PRG_CHILDREN_NODE.sub_id, -- child id
3651 l_prg_temp_sub_emt, -- sub emt id
3652 PRG_PARENT_NODE.prg_level, -- parent level
3653 l_prg_temp_level, -- child level
3654 null, -- child rollup id
3655 PRG_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
3656 l_prg_leaf_flag, -- child leaf flag
3657 'children', -- status id
3658 P_WORKER_ID -- worker id
3659 );
3660
3661 END LOOP; -- FOR PRG_CHILD_NODE
3662
3663 END LOOP; -- FOR PRG_PARENT_NODE
3664
3665 END IF; -- if l_prg_level_id <> 1
3666
3667 -- Call wbs_denorm
3668
3669 -- #change: do not call wbs_denorm in online version
3670
3671 END IF; -- prg group is null
3672
3673 END LOOP; -- FOR PRG_NODE
3674
3675 -- Decrease PRG node level --
3676
3677 l_prg_level_id := l_prg_level_id - 1;
3678 exit when l_prg_level_id = 0;
3679
3680 END LOOP; -- PRG_LEVEL
3681
3682 -- -----------------------------------------
3683
3684 if g_pa_debug_mode = 'Y'
3685 then
3686 PJI_UTILS.WRITE2LOG(
3687 'PJI_PJP - End: prg_denorm_online',
3688 null,
3689 g_msg_level_proc_call
3690 );
3691 end if;
3692
3693 -- -----------------------------------------
3694
3695 end prg_denorm_online;
3696
3697 -- -----------------------------------------------------------------------
3698 -- -----------------------------------------------------------------------
3699
3700
3701
3702
3703
3704
3705 -- -----------------------------------------------------------------------
3706
3707 procedure wbs_denorm_online(
3708 p_worker_id in number,
3709 p_extraction_type in varchar2,
3710 p_wbs_version_id in number
3711 -- P_PRG_SUP_EMT_ID IN NUMBER, -- ###xbs###
3712 ) as
3713
3714 -- -----------------------------------------------------------------------
3715 --
3716 -- History
3717 -- 31-MAR-2004 aartola Created
3718 --
3719 --
3720 -- *** This API assumes that the following tables exist and that they are
3721 -- properly populated (no cycles, correct relationships, etc)
3722 --
3723 -- PA_PROJ_ELEMENT_VERSIONS
3724 -- PA_OBJECT_RELATIONSHIPS
3725 --
3726 -- Then, this API populates output values in the following existing
3727 -- table:
3728 -- PJI_FP_AGGR_XBS_T
3729 --
3730 -- -----------------------------------------------------------------------
3731
3732 -- -----------------------------------------------------
3733 -- Declare statements --
3734
3735 l_wbs_count number;
3736 l_wbs_level_id number;
3737 l_wbs_temp_parent number;
3738 l_wbs_temp_level number;
3739 l_wbs_node_count number;
3740 l_wbs_leaf_flag_id number;
3741 l_wbs_leaf_flag varchar2(1);
3742
3743 l_wbs_temp_sup_emt number;
3744 l_wbs_temp_sub_emt number;
3745
3746 l_wbs_test_node number;
3747
3748 l_struct_emt_id number;
3749 l_struct_emt_id_count number;
3750
3751 l_sharing_code varchar2(80); -- ###financial###
3752
3753 -- -----------------------------------------------------
3754
3755 begin
3756
3757 -- (WBS node = task)
3758
3759 if g_pa_debug_mode = 'Y'
3760 then
3761 PJI_UTILS.WRITE2LOG(
3762 'PJI_PJP - Begin wbs_denorm_online -'
3763 || ' p_worker_id = '
3764 || P_WORKER_ID
3765 || ' p_extraction_type = '
3766 || P_EXTRACTION_TYPE
3767 || ' p_wbs_version_id = '
3768 || P_WBS_VERSION_ID,
3769 null,
3770 g_msg_level_proc_call
3771 );
3772 end if;
3773
3774 -- -----------------------------------------------------
3775 -- get a node count
3776
3777 select count(*)
3778 into l_wbs_count
3779 from PA_PROJ_ELEMENT_VERSIONS wvt_count
3780 where 1=1
3781 and wvt_count.object_type = 'PA_TASKS'
3782 and wvt_count.proj_element_id in -- ###dummy###
3783 (
3784 select proj_element_id
3785 from pa_proj_elements
3786 where link_task_flag = 'N'
3787 )
3788 and wvt_count.parent_structure_version_id = P_WBS_VERSION_ID
3789 and rownum = 1;
3790
3791 -- -----------------------------------------------------
3792
3793 IF l_wbs_count = 0
3794
3795 THEN
3796
3797 if g_pa_debug_mode = 'Y'
3798 then
3799 PJI_UTILS.WRITE2LOG(
3800 'PJI_PJP - Wbs node as task does not exist - p_wbs_version_id = '
3801 || P_WBS_VERSION_ID,
3802 null,
3803 g_msg_level_low_detail
3804 );
3805 end if;
3806
3807 ELSE
3808
3809
3810 -- -----------------------------------------------------
3811 -- Get deepest WBS node level --
3812 -- Look only at the data to be processed
3813 -- 1) ONLINE
3814
3815 select max(wvt_level.wbs_level)
3816 into l_wbs_level_id
3817 from PA_PROJ_ELEMENT_VERSIONS wvt_level
3818 where 1=1
3819 and wvt_level.object_type = 'PA_TASKS'
3820 and wvt_level.proj_element_id in -- ###dummy###
3821 (
3822 select proj_element_id
3823 from pa_proj_elements
3824 where link_task_flag = 'N'
3825 )
3826 and wvt_level.parent_structure_version_id = P_WBS_VERSION_ID;
3827
3828
3829 -- --------------------------------------------------------
3830 -- WBS nodes with no level (is null) are INVALID.
3831
3832 if l_wbs_level_id is null -- ###level_is_null###
3833 then
3834
3835 if g_pa_debug_mode = 'Y'
3836 then
3837 PJI_UTILS.WRITE2LOG(
3838 'PJI_PJP - Level is null Data Corruption - p_wbs_version_id = '
3839 || P_WBS_VERSION_ID,
3840 null,
3841 g_msg_level_data_corruption
3842 );
3843 end if;
3844
3845 l_wbs_level_id := 1;
3846 end if;
3847
3848 -- -----------------------------------------------------
3849 -- l_struct_emt_id -- -- ###sup_emt###
3850
3851 select
3852 distinct
3853 sup_emt_id
3854 into l_struct_emt_id
3855 from pji_fp_aggr_xbs_t
3856 where 1=1
3857 and struct_type = 'PRG'
3858 and sup_id = P_WBS_VERSION_ID
3859 and worker_id = P_WORKER_ID;
3860
3861
3862 -- -----------------------------------------------------
3863 -- l_sharing_code
3864
3865 begin
3866 select structure_sharing_code
3867 into l_sharing_code
3868 from pa_projects_all projects,
3869 pa_proj_element_versions versions
3870 where 1=1
3871 and projects.project_id = versions.project_id
3872 and versions.object_type = 'PA_STRUCTURES'
3873 and versions.element_version_id = P_WBS_VERSION_ID;
3874 exception
3875 when no_data_found
3876 then
3877 l_sharing_code := 'PJI$NULL';
3878 end;
3879
3880 if l_sharing_code is null
3881 then
3882 l_sharing_code := 'PJI$NULL';
3883 end if;
3884
3885
3886 -- -----------------------------------------------------
3887
3888 LOOP
3889
3890 if g_pa_debug_mode = 'Y'
3891 then
3892 PJI_UTILS.WRITE2LOG(
3893 'PJI_PJP - WBS Inserts - l_wbs_level_id = '
3894 || l_wbs_level_id,
3895 null,
3896 g_msg_level_high_detail
3897 );
3898 end if;
3899
3900 -- -----------------------------------------------------
3901 -- Get all WBS nodes from a certain level and from a certain project --
3902 -- Look only at the data to be processed
3903 -- 1) ONLINE
3904
3905 FOR WBS_NODE IN
3906 (
3907 select wvt_nodes.project_id,
3908 wvt_nodes.proj_element_id,
3909 wvt_nodes.element_version_id,
3910 wvt_nodes.parent_structure_version_id,
3911 wvt_nodes.financial_task_flag -- ###financial###
3912 from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
3913 where 1=1
3914 and wvt_nodes.object_type = 'PA_TASKS'
3915 and wvt_nodes.proj_element_id in -- ###dummy###
3916 (
3917 select proj_element_id
3918 from pa_proj_elements
3919 where link_task_flag = 'N'
3920 )
3921 and wvt_nodes.parent_structure_version_id = P_WBS_VERSION_ID
3922 and wvt_nodes.wbs_level = l_wbs_level_id
3923 ) LOOP
3924
3925
3926 -- -----------------------------------------------------
3927 -- Check WBS node self --
3928
3929 -- Determine if the node to be inserted is a leaf
3930 -- If the node to be inserted has not been inserted before,
3931 -- then we know that the node is a leaf
3932
3933 select count(*)
3934 into l_wbs_node_count
3935 from PJI_FP_AGGR_XBS_T wdt_count
3936 where wdt_count.sup_id = WBS_NODE.element_version_id
3937 and wdt_count.worker_id = P_WORKER_ID
3938 and rownum = 1;
3939
3940 -- l_wbs_leaf_flag_id --
3941 if l_wbs_node_count > 0
3942 then
3943 l_wbs_leaf_flag_id := 0;
3944 else
3945 l_wbs_leaf_flag_id := 1;
3946 end if;
3947
3948 -- l_wbs_leaf_flag -- (business rule)
3949 if (
3950 WBS_NODE.proj_element_id = WBS_NODE.proj_element_id
3951 or
3952 l_wbs_leaf_flag_id = 1
3953 )
3954 then
3955 l_wbs_leaf_flag := 'Y';
3956 else
3957 l_wbs_leaf_flag := 'N';
3958 end if;
3959
3960
3961 if g_pa_debug_mode = 'Y'
3962 then
3963 PJI_UTILS.WRITE2LOG(
3964 'PJI_PJP - Inserting WBS node self - element_version_id = '
3965 || WBS_NODE.element_version_id,
3966 null,
3967 g_msg_level_low_detail
3968 );
3969 end if;
3970
3971
3972 -- Insert WBS node self --
3973 insert
3974 into PJI_FP_AGGR_XBS_T
3975 (
3976 struct_type,
3977 prg_group,
3978 struct_emt_id,
3979 struct_version_id,
3980 sup_project_id,
3981 sup_id,
3982 sup_emt_id,
3983 subro_id,
3984 sub_id,
3985 sub_emt_id,
3986 sup_level,
3987 sub_level,
3988 sub_rollup_id,
3989 sub_leaf_flag_id,
3990 sub_leaf_flag,
3991 relationship_type,
3992 status_id,
3993 worker_id
3994 )
3995 values (
3996 'WBS', -- structure type
3997 null, -- prg group
3998 l_struct_emt_id, -- structure element id
3999 P_WBS_VERSION_ID, -- structure version id
4000 WBS_NODE.project_id, -- parent project id
4001 WBS_NODE.element_version_id, -- parent id
4002 WBS_NODE.proj_element_id, -- sup emt_id
4003 null, -- immediate child id
4004 WBS_NODE.element_version_id, -- child id
4005 WBS_NODE.proj_element_id, -- sub emt_id
4006 l_wbs_level_id, -- parent level
4007 l_wbs_level_id, -- child level
4008 null, -- child rollup id
4009 l_wbs_leaf_flag_id, -- child leaf flag id
4010 l_wbs_leaf_flag, -- child leaf flag
4011 decode(l_sharing_code,
4012 'SHARE_FULL', 'WF',
4013 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
4014 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4015 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4016 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
4017 -- sub financial task flag -- ###financial###
4018 'self', -- status id
4019 P_WORKER_ID -- worker id
4020 );
4021
4022 -- --------------------------------------------------------
4023 -- Check for WBS node's parent --
4024 -- Check only if the node is not a top most node (level = 1)
4025
4026 IF l_wbs_level_id <> 1
4027 THEN
4028
4029
4030 -- -----------------------------------------------------
4031 -- Filter WBS nodes to those that have one and only one parent
4032 -- if not, the node is invalied. Cases with no parents or two parents
4033 -- have appeared with corrupted data
4034
4035 select count(*) -- ###parent_is_one###
4036 into l_wbs_test_node
4037 from PA_OBJECT_RELATIONSHIPS rel,
4038 (
4039 select element_version_id
4040 from PA_PROJ_ELEMENT_VERSIONS
4041 where 1=1
4042 and WBS_LEVEL > 1
4043 and element_version_id = WBS_NODE.element_version_id
4044 ) ver
4045 where 1=1
4046 and rel.OBJECT_TYPE_FROM = 'PA_TASKS'
4047 and rel.OBJECT_TYPE_TO = 'PA_TASKS'
4048 and rel.RELATIONSHIP_TYPE = 'S'
4049 and ver.ELEMENT_VERSION_ID = rel.OBJECT_ID_to1 (+);
4050
4051 IF l_wbs_test_node = 1
4052
4053 THEN
4054
4055 --
4056 -- Discrepancy between prg_denorm and wbs_denorm
4057 -- As opposed to PRG nodes, WBS nodes cannot have more that one parent
4058 --
4059
4060 -- l_wbs_temp_parent --
4061 select wrt_parent.object_id_from1
4062 into l_wbs_temp_parent
4063 from PA_OBJECT_RELATIONSHIPS wrt_parent
4064 where 1=1
4065 and wrt_parent.object_id_to1 = WBS_NODE.element_version_id
4066 and wrt_parent.object_type_from = 'PA_TASKS'
4067 and wrt_parent.object_type_to = 'PA_TASKS'
4068 and wrt_parent.relationship_type = 'S';
4069
4070 -- l_wbs_temp_sup_emt --
4071 select wvt_parent1.proj_element_id
4072 into l_wbs_temp_sup_emt
4073 from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
4074 where 1=1
4075 and wvt_parent1.element_version_id = l_wbs_temp_parent;
4076
4077 -- l_wbs_temp_sub_emt --
4078 select wvt_parent2.proj_element_id
4079 into l_wbs_temp_sub_emt
4080 from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
4081 where 1=1
4082 and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
4083
4084 -- l_wbs_leaf_flag --
4085 if (
4086 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
4087 or
4088 l_wbs_leaf_flag_id = 1
4089 )
4090 then
4091 l_wbs_leaf_flag := 'Y';
4092 else
4093 l_wbs_leaf_flag := 'N';
4094 end if;
4095
4096
4097 if g_pa_debug_mode = 'Y'
4098 then
4099 PJI_UTILS.WRITE2LOG(
4100 'PJI_PJP - Inserting WBS node parent - l_wbs_temp_parent = '
4101 || l_wbs_temp_parent,
4102 null,
4103 g_msg_level_low_detail
4104 );
4105 end if;
4106
4107 -- Insert WBS node's parent --
4108 insert
4109 into PJI_FP_AGGR_XBS_T
4110 (
4111 struct_type,
4112 prg_group,
4113 struct_emt_id,
4114 struct_version_id,
4115 sup_project_id,
4116 sup_id,
4117 sup_emt_id,
4118 subro_id,
4119 sub_id,
4120 sub_emt_id,
4121 sup_level,
4122 sub_level,
4123 sub_rollup_id,
4124 sub_leaf_flag_id,
4125 sub_leaf_flag,
4126 relationship_type,
4127 status_id,
4128 worker_id
4129 )
4130 values (
4131 'WBS', -- structure type
4132 null, -- prg group
4133 l_struct_emt_id, -- structure element id
4134 P_WBS_VERSION_ID, -- structure version id
4135 WBS_NODE.project_id, -- parent project id
4136 l_wbs_temp_parent, -- parent id
4137 l_wbs_temp_sup_emt, -- sup_emt_id
4138 WBS_NODE.proj_element_id, -- immediate child id
4139 WBS_NODE.element_version_id, -- child id
4140 l_wbs_temp_sub_emt, -- sub_emt_id
4141 l_wbs_level_id - 1, -- parent level
4142 l_wbs_level_id, -- child level
4143 null, -- child rollup id
4144 l_wbs_leaf_flag_id, -- child leaf flag id
4145 l_wbs_leaf_flag, -- child leaf flag
4146 decode(l_sharing_code,
4147 'SHARE_FULL', 'WF',
4148 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
4149 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4150 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4151 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
4152 -- sub financial task flag -- ###financial###
4153 'parent', -- status id
4154 P_WORKER_ID -- worker id
4155 );
4156
4157
4158 -- --------------------------------------------------------
4159 -- Check for WBS node's children --
4160 -- Filter nodes to see if the node has children
4161
4162 FOR WBS_CHILDREN_NODE IN
4163 (
4164 SELECT /*+ index(wdt_child PJI_FP_AGGR_XBS_T_N1) */ --9796902
4165 wdt_child.sup_id,
4166 wdt_child.sub_id,
4167 wdt_child.sub_leaf_flag_id,
4168 wdt_child.relationship_type
4169 from PJI_FP_AGGR_XBS_T wdt_child
4170 where 1=1
4171 and wdt_child.sup_id = WBS_NODE.element_version_id
4172 and wdt_child.sup_id <> wdt_child.sub_id
4173 and wdt_child.worker_id = P_WORKER_ID
4174 ) LOOP
4175
4176 -- l_wbs_temp_level --
4177 select wdt_child1.sub_level
4178 into l_wbs_temp_level
4179 from PJI_FP_AGGR_XBS_T wdt_child1
4180 where 1=1
4181 and wdt_child1.sup_id = WBS_CHILDREN_NODE.sub_id
4182 and wdt_child1.sup_id = wdt_child1.sub_id
4183 and wdt_child1.worker_id = P_WORKER_ID;
4184
4185 -- l_wbs_temp_sup_emt --
4186 select wvt_child1.proj_element_id
4187 into l_wbs_temp_sup_emt
4188 from PA_PROJ_ELEMENT_VERSIONS wvt_child1
4189 where 1=1
4190 and wvt_child1.element_version_id = l_wbs_temp_parent;
4191
4192 -- l_wbs_temp_sub_emt --
4193 select wvt_child2.proj_element_id
4194 into l_wbs_temp_sub_emt
4195 from PA_PROJ_ELEMENT_VERSIONS wvt_child2
4196 where 1=1
4197 and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
4198
4199 -- l_wbs_leaf_flag --
4200 if (
4201 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
4202 or
4203 WBS_CHILDREN_NODE.sub_leaf_flag_id = 1
4204 )
4205 then
4206 l_wbs_leaf_flag := 'Y';
4207 else
4208 l_wbs_leaf_flag := 'N';
4209 end if;
4210
4211
4212 if g_pa_debug_mode = 'Y'
4213 then
4214 PJI_UTILS.WRITE2LOG(
4215 'PJI_PJP - Inserting WBS node child - sup_id = '
4216 || WBS_CHILDREN_NODE.sup_id,
4217 null,
4218 g_msg_level_low_detail
4219 );
4220 end if;
4221
4222
4223 -- Insert WBS node's child --
4224 insert
4225 into PJI_FP_AGGR_XBS_T
4226 (
4227 struct_type,
4228 prg_group,
4229 struct_emt_id,
4230 struct_version_id,
4231 sup_project_id,
4232 sup_id,
4233 sup_emt_id,
4234 subro_id,
4235 sub_id,
4236 sub_emt_id,
4237 sup_level,
4238 sub_level,
4239 sub_rollup_id,
4240 sub_leaf_flag_id,
4241 sub_leaf_flag,
4242 relationship_type,
4243 status_id,
4244 worker_id
4245 )
4246 values (
4247 'WBS', -- structure type
4248 null, -- prg group
4249 l_struct_emt_id, -- structure element id
4250 P_WBS_VERSION_ID, -- structure version id
4251 WBS_NODE.project_id, -- parent project id
4252 l_wbs_temp_parent, -- parent id
4253 l_wbs_temp_sup_emt, -- sup emt_id
4254 WBS_NODE.proj_element_id, -- immediate child id
4255 WBS_CHILDREN_NODE.sub_id, -- child id
4256 l_wbs_temp_sub_emt, -- sub emt_id
4257 l_wbs_level_id - 1, -- parent level
4258 l_wbs_temp_level, -- child level
4259 null, -- child rollup id
4260 WBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag id
4261 l_wbs_leaf_flag, -- child leaf flag
4262 decode(l_sharing_code,
4263 'SHARE_FULL', 'WF',
4264 'SHARE_PARTIAL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'WF', 'LW'),
4265 'SPLIT_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
4266 'SPLIT_NO_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
4267 'PJI$NULL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW')),
4268 -- sub financial task flag -- ###financial###
4269 'children', -- status id
4270 P_WORKER_ID -- worker id
4271 );
4272
4273 END LOOP; -- FOR WBS_CHILDREN_NODE
4274
4275 ELSE
4276
4277 if g_pa_debug_mode = 'Y'
4278 then
4279 PJI_UTILS.WRITE2LOG(
4280 'PJI_PJP - Parent Data Corruption - element_version_id = '
4281 || WBS_NODE.element_version_id,
4282 null,
4283 g_msg_level_data_corruption
4284 );
4285 end if;
4286
4287 END IF; -- l_wbs_test_node = 1 -- ###parent_is_one###
4288
4289 ELSE
4290
4291 select count(*)
4292 into l_struct_emt_id_count
4293 from pa_proj_element_versions
4294 where 1=1
4295 and element_version_id = P_WBS_VERSION_ID
4296 and rownum = 1;
4297
4298 if l_struct_emt_id_count <> 0
4299 then
4300
4301 -- l_struct_emt_id -- -- ###xbs###
4302 select
4303 distinct
4304 proj_element_id
4305 into l_struct_emt_id
4306 from pa_proj_element_versions
4307 where 1=1
4308 and element_version_id = P_WBS_VERSION_ID;
4309
4310
4311 -- l_wbs_leaf_flag -- (business rule)
4312 if (
4313 P_WBS_VERSION_ID = WBS_NODE.element_version_id
4314 or
4315 l_wbs_leaf_flag_id = 1
4316 )
4317 then
4318 l_wbs_leaf_flag := 'Y';
4319 else
4320 l_wbs_leaf_flag := 'N';
4321 end if;
4322
4323
4324 if g_pa_debug_mode = 'Y'
4325 then
4326 PJI_UTILS.WRITE2LOG(
4327 'PJI_PJP - Inserting XBS node self - sup_id = '
4328 || P_WBS_VERSION_ID,
4329 null,
4330 g_msg_level_low_detail
4331 );
4332 end if;
4333
4334 -- Insert XBS node --
4335 insert
4336 into PJI_FP_AGGR_XBS_T
4337 (
4338 struct_type,
4339 prg_group,
4340 struct_version_id,
4341 sup_project_id,
4342 sup_id,
4343 sup_emt_id,
4344 subro_id,
4345 sub_id,
4346 sub_emt_id,
4347 sup_level,
4348 sub_level,
4349 sub_rollup_id,
4350 sub_leaf_flag_id,
4351 sub_leaf_flag,
4352 relationship_type,
4353 status_id,
4354 worker_id
4355 )
4356 values (
4357 'XBS', -- structure type
4358 null, -- prg group
4359 P_WBS_VERSION_ID, -- structure version id
4360 WBS_NODE.project_id, -- parent project id
4361 P_WBS_VERSION_ID, -- parent id
4362 l_struct_emt_id, -- sup emt_id
4363 null, -- immediate child id
4364 WBS_NODE.element_version_id, -- child id
4365 WBS_NODE.proj_element_id, -- sub emt_id
4366 0, -- parent level
4367 l_wbs_level_id, -- child level
4368 null, -- child rollup id
4369 l_wbs_leaf_flag_id, -- child leaf flag id
4370 l_wbs_leaf_flag, -- child leaf flag
4371 decode(l_sharing_code,
4372 'SHARE_FULL', 'WF',
4373 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
4374 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4375 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4376 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
4377 -- sub financial task flag -- ###financial###
4378 'self', -- status id
4379 P_WORKER_ID -- worker id
4380 );
4381 else
4382
4383 if g_pa_debug_mode = 'Y'
4384 then
4385 PJI_UTILS.WRITE2LOG(
4386 'PJI_PJP - DATA BUG - element_version_id = '
4387 || WBS_NODE.element_version_id
4388 || ' struct_version_id = '
4389 || P_WBS_VERSION_ID,
4390 null,
4391 g_msg_level_high_detail
4392 );
4393 end if;
4394
4395 end if;
4396
4397 END IF; -- IF l_wbs_level_id <> 1
4398
4399 END LOOP; -- FOR WBS_NODE
4400
4401 -- Decrease WBS node level --
4402 l_wbs_level_id := l_wbs_level_id - 1;
4403 exit when l_wbs_level_id = 0;
4404
4405 END LOOP; -- WBS_LEVEL
4406
4407 END IF; -- WBS COUNT
4408
4409 -- -------------------------------------------------
4410 if g_pa_debug_mode = 'Y'
4411 then
4412 PJI_UTILS.WRITE2LOG(
4413 'PJI_PJP - End: wbs_denorm_online',
4414 null,
4415 g_msg_level_proc_call
4416 );
4417 end if;
4418 -- -------------------------------------------------
4419
4420 end wbs_denorm_online;
4421
4422 -- -----------------------------------------------------------------------
4423 -- -----------------------------------------------------------------------
4424
4425
4426
4427
4428
4429
4430 -- -----------------------------------------------------------------------
4431
4432 procedure rbs_denorm(
4433 p_worker_id in number,
4434 p_extraction_type in varchar2,
4435 p_rbs_version_id in number
4436 ) as
4437
4438
4439 -- -----------------------------------------------------------------------
4440 --
4441 -- History
4442 -- 19-MAR-2004 aartola Created
4443 --
4444 --
4445 -- *** This API assumes that the following tables exist and that they are
4446 -- properly populated (no cycles, correct relationships, etc)
4447 --
4448 -- PA_RBS_ELEMENTS
4449 --
4450 -- Then, this API populates output values in the following existing
4451 -- table:
4452 -- PJI_FP_AGGR_RBS
4453 --
4454 -- -----------------------------------------------------------------------
4455
4456 -- -----------------------------------------------------
4457 -- Declare statements --
4458
4459 l_rbs_level_id number;
4460 l_rbs_temp_parent number;
4461 l_rbs_temp_level number;
4462 l_rbs_node_count number;
4463 l_rbs_leaf_flag_id number;
4464 l_rbs_leaf_flag varchar2(1);
4465 /* Added for Bug 9099240 Start*/
4466 l_max_rbs_level_id number;
4467 l_rbs_exists1 number;
4468 l_rbs_exists2 number;
4469 l_rbs_exists3 number;
4470 /* Added for Bug 9099240 End */
4471
4472 -- -----------------------------------------------------
4473
4474 begin
4475
4476 -- (RBS node = resource)
4477
4478 if g_pa_debug_mode = 'Y'
4479 then
4480 PJI_UTILS.WRITE2LOG(
4481 'PJI_PJP - Begin: rbs_denorm -'
4482 || ' p_worker_id = '
4483 || P_WORKER_ID
4484 || ' p_extraction_type = '
4485 || P_EXTRACTION_TYPE
4486 || ' p_rbs_version_id = '
4487 || P_RBS_VERSION_ID,
4488 null,
4489 g_msg_level_proc_call
4490 );
4491 end if;
4492
4493 /* Added for Bug 9099240 Start*/
4494 l_rbs_exists1 := 0;
4495 l_rbs_exists2 := 0;
4496 l_rbs_exists3 := 0;
4497 /* Added for Bug 9099240 End */
4498
4499 -- -----------------------------------------------------
4500 -- Get deepest RBS node level --
4501 -- Look only at the data to be processed
4502 -- 1) FULL - All data
4503 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs table
4504
4505 if P_EXTRACTION_TYPE = 'FULL'
4506 then
4507 select max(pvt_level.rbs_level)
4508 into l_rbs_level_id
4509 from PA_RBS_ELEMENTS pvt_level
4510 where 1=1
4511 and pvt_level.user_created_flag = 'N';
4512
4513 elsif (
4514 P_EXTRACTION_TYPE = 'INCREMENTAL'
4515 or
4516 P_EXTRACTION_TYPE = 'PARTIAL'
4517 or
4518 P_EXTRACTION_TYPE = 'RBS'
4519 )
4520 then
4521 select max(pvt_level.rbs_level)
4522 into l_rbs_level_id
4523 from PA_RBS_ELEMENTS pvt_level,
4524 (
4525 select distinct event_type, event_object
4526 from PJI_PA_PROJ_EVENTS_LOG
4527 where 1=1
4528 and event_type = 'PJI_RBS_CHANGE'
4529 and worker_id = P_WORKER_ID
4530 ) log
4531 where 1=1
4532 and pvt_level.user_created_flag = 'N'
4533 and pvt_level.rbs_version_id = log.event_object;
4534
4535 elsif P_EXTRACTION_TYPE = 'UPGRADE'
4536 then
4537 select max(pvt_level.rbs_level)
4538 into l_rbs_level_id
4539 from PA_RBS_ELEMENTS pvt_level
4540 where 1=1
4541 and pvt_level.user_created_flag = 'N'
4542 and pvt_level.rbs_version_id = P_RBS_VERSION_ID;
4543
4544 else
4545 if g_pa_debug_mode = 'Y'
4546 then
4547 PJI_UTILS.WRITE2LOG(
4548 'PJI_PJP - No maximum level found - p_rbs_version_id = '
4549 || P_RBS_VERSION_ID,
4550 null,
4551 g_msg_level_data_corruption
4552 );
4553 end if;
4554
4555 l_rbs_level_id := 1; -- ###level_is_null###
4556 end if;
4557
4558
4559 -- --------------------------------------------------------
4560 -- RBS nodes with no level (is null) are INVALID.
4561
4562 if l_rbs_level_id is null -- ###level_is_null###
4563 then
4564 if g_pa_debug_mode = 'Y'
4565 then
4566 PJI_UTILS.WRITE2LOG(
4567 'PJI_PJP - Level is null Data Corruption - p_rbs_version_id = '
4568 || P_RBS_VERSION_ID,
4569 null,
4570 g_msg_level_data_corruption
4571 );
4572 end if;
4573
4574 l_rbs_level_id := 1;
4575 end if;
4576
4577 -- -----------------------------------------------------
4578
4579 l_max_rbs_level_id := l_rbs_level_id; /* Added for Bug 9099240 */
4580
4581 -- -----------------------------------------------------
4582
4583 LOOP
4584
4585 if g_pa_debug_mode = 'Y'
4586 then
4587 PJI_UTILS.WRITE2LOG(
4588 'PJI_PJP - RBS Inserts - l_rbs_level_id = '
4589 || l_rbs_level_id,
4590 null,
4591 g_msg_level_high_detail
4592 );
4593 end if;
4594
4595 -- Get all RBS nodes from a certain level --
4596 -- Look only at the data to be processed
4597 -- 1) FULL - All data
4598 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs
4599 -- table
4600 -- 2.1) RBS node's changes
4601
4602 FOR RBS_NODE IN
4603 (
4604 select
4605 distinct
4606 -- pvt_nodes.project_id,
4607 pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
4608 pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
4609 pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
4610 -- pvt_nodes.rbs_group
4611 from PA_RBS_ELEMENTS pvt_nodes1 /* Modified for bug 11843459 */
4612
4613
4614 where 1=1
4615 and P_EXTRACTION_TYPE = 'FULL'
4616 and pvt_nodes1.user_created_flag = 'N'
4617 and (
4618 pvt_nodes1.rbs_level = l_rbs_level_id
4619 or
4620 (
4621 l_rbs_level_id = 1
4622 and
4623 pvt_nodes1.rbs_level is null
4624 )
4625 )
4626 and exists (select 1 /* Modified for bug 11843459 */
4627 from pa_rbs_prj_assignments assignments,
4628 pji_pjp_proj_batch_map map
4629 where map.project_id = assignments.project_id
4630 and assignments.rbs_version_id = pvt_nodes1.rbs_version_id)
4631 UNION ALL
4632 select
4633 distinct
4634 -- pvt_nodes.project_id,
4635 pvt_nodes2.rbs_version_id, -- pvt_nodes.proj_element_id,
4636 pvt_nodes2.rbs_element_id, -- pvt_nodes.element_version_id,
4637 pvt_nodes2.parent_element_id -- pvt_nodes.parent_structure_version_id,
4638 -- pvt_nodes.rbs_group
4639 from PA_RBS_ELEMENTS pvt_nodes2,
4640 (
4641 select
4642 distinct
4643 log1.event_type, log1.event_object
4644 from PJI_PA_PROJ_EVENTS_LOG log1
4645 where 1=1
4646 and log1.event_type = 'PJI_RBS_CHANGE'
4647 and worker_id = P_WORKER_ID
4648 ) log11
4649 where 1=1
4650 and (
4651 P_EXTRACTION_TYPE = 'INCREMENTAL'
4652 or
4653 P_EXTRACTION_TYPE = 'PARTIAL'
4654 or
4655 P_EXTRACTION_TYPE = 'RBS'
4656 )
4657 and pvt_nodes2.user_created_flag = 'N'
4658 and pvt_nodes2.rbs_version_id = log11.event_object
4659 and pvt_nodes2.rbs_level = l_rbs_level_id
4660 /* Added for Bug 9099240 Start */
4661 and l_rbs_level_id = l_max_rbs_level_id
4662 and ( pvt_nodes2.rbs_element_id between PA_RBS_MAPPING.g_max_rbs_id1 and PA_RBS_MAPPING.g_max_rbs_id2
4663 or (PA_RBS_MAPPING.g_max_rbs_id1 is null
4664 and PA_RBS_MAPPING.g_max_rbs_id2 is null)) --Added for 10181391
4665 UNION ALL
4666 select
4667 distinct
4668 pvt_nodes2.rbs_version_id,
4669 pvt_nodes2.rbs_element_id,
4670 pvt_nodes2.parent_element_id
4671 from PA_RBS_ELEMENTS pvt_nodes2,
4672 (
4673 select
4674 distinct
4675 log1.event_type, log1.event_object
4676 from PJI_PA_PROJ_EVENTS_LOG log1
4677 where 1=1
4678 and log1.event_type = 'PJI_RBS_CHANGE'
4679 and worker_id = P_WORKER_ID
4680 ) log11
4681 where 1=1
4682 and (
4683 P_EXTRACTION_TYPE = 'INCREMENTAL'
4684 or
4685 P_EXTRACTION_TYPE = 'PARTIAL'
4686 or
4687 P_EXTRACTION_TYPE = 'RBS'
4688 )
4689 and pvt_nodes2.user_created_flag = 'N'
4690 and pvt_nodes2.rbs_version_id = log11.event_object
4691 and pvt_nodes2.rbs_level = l_rbs_level_id
4692 and l_rbs_level_id <> l_max_rbs_level_id
4693 /* Added for Bug 9099240 End */
4694 UNION ALL
4695 select
4696 distinct
4697 -- pvt_nodes.project_id,
4698 pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
4699 pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
4700 pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
4701 -- pvt_nodes.rbs_group
4702 from PA_RBS_ELEMENTS pvt_nodes1
4703 where 1=1
4704 and P_EXTRACTION_TYPE = 'UPGRADE'
4705 and pvt_nodes1.user_created_flag = 'N'
4706 and (
4707 pvt_nodes1.rbs_level = l_rbs_level_id
4708 or
4709 (
4710 l_rbs_level_id = 1
4711 and
4712 pvt_nodes1.rbs_level is null
4713 )
4714 )
4715 and pvt_nodes1.rbs_version_id = P_RBS_VERSION_ID
4716 ) LOOP
4717
4718
4719 -- -----------------------------------------------------
4720 -- Check RBS node self --
4721
4722 -- Determine if the node to be inserted is a leaf
4723 -- If the node to be inserted has not been inserted before,
4724 -- then we know that the node is a leaf
4725
4726 select count(*)
4727 into l_rbs_node_count
4728 from PJI_FP_AGGR_RBS pdt_count
4729 where 1=1
4730 and pdt_count.sup_id = RBS_NODE.rbs_element_id
4731 and pdt_count.worker_id = P_WORKER_ID
4732 and rownum = 1;
4733
4734 -- l_rbs_leaf_flag_id --
4735 if l_rbs_node_count > 0
4736 then
4737 l_rbs_leaf_flag_id := 0;
4738 else
4739 l_rbs_leaf_flag_id := 1;
4740 end if;
4741
4742 -- l_rbs_leaf_flag -- (business rule)
4743 if (
4744 RBS_NODE.rbs_version_id = RBS_NODE.rbs_version_id
4745 or
4746 l_rbs_leaf_flag_id = 1
4747 )
4748 then
4749 l_rbs_leaf_flag := 'Y';
4750 else
4751 l_rbs_leaf_flag := 'N';
4752 end if;
4753
4754
4755 if g_pa_debug_mode = 'Y'
4756 then
4757 PJI_UTILS.WRITE2LOG(
4758 'PJI_PJP - Inserting RBS node self - rbs_element_id = '
4759 || RBS_NODE.rbs_element_id,
4760 null,
4761 g_msg_level_low_detail
4762 );
4763 end if;
4764
4765 /* Added for Bug 9099240 Start */
4766 begin
4767 select count(*) into l_rbs_exists1
4768 from pa_rbs_denorm den,
4769 (select distinct log1.event_object
4770 from PJI_PA_PROJ_EVENTS_LOG log1
4771 where 1=1
4772 and log1.event_type = 'PJI_RBS_CHANGE'
4773 and worker_id = P_WORKER_ID)
4774 log11
4775 where den.struct_version_id = log11.event_object
4776 and den.sup_id = RBS_NODE.rbs_element_id
4777 and den.sub_id = RBS_NODE.rbs_element_id
4778 and den.subro_id is null;
4779 exception
4780 when NO_DATA_FOUND then
4781 l_rbs_exists1 := 0;
4782 end;
4783 /* Added for Bug 9099240 End */
4784
4785 -- Insert RBS node self --
4786 if l_rbs_exists1 = 0 then /* Added for Bug 9099240 */
4787 insert
4788 into PJI_FP_AGGR_RBS
4789 (
4790 struct_version_id,
4791 sup_id,
4792 subro_id,
4793 sub_id,
4794 sup_level,
4795 sub_level,
4796 sub_leaf_flag_id,
4797 sub_leaf_flag,
4798 status_id,
4799 worker_id
4800 )
4801 values (
4802 RBS_NODE.rbs_version_id, -- rbs version id
4803 RBS_NODE.rbs_element_id, -- parent id
4804 null, -- immediate child id
4805 RBS_NODE.rbs_element_id, -- child id
4806 l_rbs_level_id, -- parent level
4807 l_rbs_level_id, -- child level
4808 l_rbs_leaf_flag_id, -- child leaf flag id
4809 l_rbs_leaf_flag, -- child leaf flag
4810 'self', -- status id
4811 P_WORKER_ID -- worker id
4812 );
4813 end if; /* Added for Bug 9099240 */
4814
4815 -- --------------------------------------------------------
4816 -- Check for RBS node's parent --
4817 -- Check only if the node is not a top most node (level = 1)
4818
4819 IF l_rbs_level_id <> 1
4820 THEN
4821
4822 FOR RBS_PARENT_NODE IN
4823 (
4824 select
4825 distinct
4826 prt_parent.parent_element_id
4827 from PA_RBS_ELEMENTS prt_parent
4828 where 1=1
4829 and prt_parent.user_created_flag = 'N'
4830 and prt_parent.rbs_element_id = RBS_NODE.rbs_element_id -- prt_parent.child_id
4831 ) LOOP
4832
4833
4834 -- l_rbs_temp_parent --
4835 l_rbs_temp_parent := RBS_PARENT_NODE.parent_element_id;
4836
4837
4838 -- Filter data corruption ###parent_is_null###
4839
4840 IF l_rbs_temp_parent is not null
4841 THEN
4842
4843
4844 -- l_rbs_leaf_flag --
4845 if (
4846 l_rbs_temp_parent = RBS_NODE.rbs_element_id
4847 or
4848 l_rbs_leaf_flag_id = 1
4849 )
4850 then
4851 l_rbs_leaf_flag := 'Y';
4852 else
4853 l_rbs_leaf_flag := 'N';
4854 end if;
4855
4856 if g_pa_debug_mode = 'Y'
4857 then
4858 PJI_UTILS.WRITE2LOG(
4859 'PJI_PJP - Inserting RBS node parent - l_rbs_temp_parent - = '
4860 || l_rbs_temp_parent,
4861 null,
4862 g_msg_level_low_detail
4863 );
4864 end if;
4865
4866 /* Added for Bug 9099240 Start */
4867 begin
4868 select count(*) into l_rbs_exists2
4869 from pa_rbs_denorm den,
4870 (select distinct log1.event_object
4871 from PJI_PA_PROJ_EVENTS_LOG log1
4872 where 1=1
4873 and log1.event_type = 'PJI_RBS_CHANGE'
4874 and worker_id = P_WORKER_ID)
4875 log11
4876 where den.struct_version_id = log11.event_object
4877 and den.sup_id = l_rbs_temp_parent
4878 and den.sub_id = RBS_NODE.rbs_element_id
4879 and den.subro_id = RBS_NODE.rbs_element_id;
4880 exception
4881 when NO_DATA_FOUND then
4882 l_rbs_exists2 := 0;
4883 end;
4884 /* Added for Bug 9099240 End */
4885
4886 -- Insert RBS node's parent --
4887 if l_rbs_exists2 = 0 then /* Added for Bug 9099240 */
4888 insert
4889 into PJI_FP_AGGR_RBS
4890 (
4891 struct_version_id,
4892 sup_id,
4893 subro_id,
4894 sub_id,
4895 sup_level,
4896 sub_level,
4897 sub_leaf_flag_id,
4898 sub_leaf_flag,
4899 status_id,
4900 worker_id
4901 )
4902 values (
4903 RBS_NODE.rbs_version_id, -- rbs version id
4904 l_rbs_temp_parent, -- parent id
4905 RBS_NODE.rbs_element_id, -- immediate child id
4906 RBS_NODE.rbs_element_id, -- child id
4907 l_rbs_level_id - 1, -- parent level
4908 l_rbs_level_id, -- child level
4909 l_rbs_leaf_flag_id, -- child leaf flag id
4910 l_rbs_leaf_flag, -- child leaf flag
4911 'parent', -- status id
4912 P_WORKER_ID -- worker id
4913 );
4914 end if; /* Added for Bug 9099240 */
4915
4916
4917 -- --------------------------------------------------------
4918 -- Check for RBS node's children --
4919 -- Filter nodes to see if the node has children
4920
4921 FOR RBS_CHILDREN_NODE IN
4922 (
4923 select
4924 distinct
4925 pdt_child.sup_id,
4926 pdt_child.sub_id,
4927 pdt_child.sub_leaf_flag_id
4928 from PJI_FP_AGGR_RBS pdt_child
4929 where 1=1
4930 and pdt_child.sup_id = RBS_NODE.rbs_element_id
4931 and pdt_child.sup_id <> pdt_child.sub_id
4932 and pdt_child.worker_id = P_WORKER_ID
4933 ) LOOP
4934
4935 -- l_rbs_temp_level --
4936 select pdt_child1.sub_level
4937 into l_rbs_temp_level
4938 from PJI_FP_AGGR_RBS pdt_child1
4939 where 1=1
4940 and pdt_child1.sup_id = RBS_CHILDREN_NODE.sub_id
4941 and pdt_child1.sup_id = pdt_child1.sub_id
4942 and pdt_child1.worker_id = P_WORKER_ID;
4943
4944 -- l_rbs_leaf_flag --
4945 if (
4946 l_rbs_temp_parent = RBS_CHILDREN_NODE.sub_id
4947 or
4948 RBS_CHILDREN_NODE.sub_leaf_flag_id = 1
4949 )
4950 then
4951 l_rbs_leaf_flag := 'Y';
4952 else
4953 l_rbs_leaf_flag := 'N';
4954 end if;
4955
4956
4957 if g_pa_debug_mode = 'Y'
4958 then
4959 PJI_UTILS.WRITE2LOG(
4960 'PJI_PJP - Inserting RBS node child - sup_id = '
4961 || RBS_CHILDREN_NODE.sup_id,
4962 null,
4963 g_msg_level_low_detail
4964 );
4965 end if;
4966
4967 /* Added for Bug 9099240 Start */
4968 begin
4969 select count(*) into l_rbs_exists3
4970 from pa_rbs_denorm den,
4971 (select distinct log1.event_object
4972 from PJI_PA_PROJ_EVENTS_LOG log1
4973 where 1=1
4974 and log1.event_type = 'PJI_RBS_CHANGE'
4975 and worker_id = P_WORKER_ID)
4976 log11
4977 where den.struct_version_id = log11.event_object
4978 and den.sup_id = l_rbs_temp_parent
4979 and den.sub_id = RBS_CHILDREN_NODE.sub_id
4980 and den.subro_id = RBS_NODE.rbs_element_id;
4981 exception
4982 when NO_DATA_FOUND then
4983 l_rbs_exists3 := 0;
4984 end;
4985 /* Added for Bug 9099240 End */
4986
4987 -- Insert RBS node's child --
4988 if l_rbs_exists3 = 0 then /* Added for Bug 9099240 */
4989 insert
4990 into PJI_FP_AGGR_RBS
4991 (
4992 struct_version_id,
4993 sup_id,
4994 subro_id,
4995 sub_id,
4996 sup_level,
4997 sub_level,
4998 sub_leaf_flag_id,
4999 sub_leaf_flag,
5000 status_id,
5001 worker_id
5002 )
5003 values (
5004 RBS_NODE.rbs_version_id, -- rbs version id
5005 l_rbs_temp_parent, -- parent id
5006 RBS_NODE.rbs_element_id, -- immediate child id
5007 RBS_CHILDREN_NODE.sub_id, -- child id
5008 l_rbs_level_id - 1, -- parent level
5009 l_rbs_temp_level, -- child level
5010 RBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
5011 l_rbs_leaf_flag, -- child leaf flag
5012 'children', -- status id
5013 P_WORKER_ID -- worker id
5014 );
5015 end if; /* Added for Bug 9099240 */
5016
5017 END LOOP; -- FOR RBS_CHILD_NODE
5018
5019 ELSE
5020
5021 if g_pa_debug_mode = 'Y'
5022 then
5023 PJI_UTILS.WRITE2LOG(
5024 'PJI_PJP - Parent Data Corruption - rbs_element_id = '
5025 || RBS_NODE.rbs_element_id,
5026 null,
5027 g_msg_level_data_corruption
5028 );
5029 end if;
5030
5031 END IF; -- ###parent_is_null###
5032
5033 END LOOP; -- FOR RBS_PARENT_NODE
5034
5035 END IF; -- if RBS_LEVEL <> 1
5036
5037 END LOOP; -- FOR RBS_NODE
5038
5039 -- Decrease rbs level --
5040 l_rbs_level_id := l_rbs_level_id - 1;
5041 exit when l_rbs_level_id = 0;
5042
5043 END LOOP; -- RBS_LEVEL
5044
5045 -- -----------------------------------------
5046 if g_pa_debug_mode = 'Y'
5047 then
5048 PJI_UTILS.WRITE2LOG(
5049 'PJI_PJP - End: rbs_denorm',
5050 null,
5051 g_msg_level_proc_call
5052 );
5053 end if;
5054 -- -----------------------------------------
5055 /* Added for Bug 9099240 Start */
5056 PA_RBS_MAPPING.g_max_rbs_id1 := PA_RBS_MAPPING.g_max_rbs_id2 + 1;
5057 PA_RBS_MAPPING.g_max_rbs_id2 := 0;
5058 /* Added for Bug 9099240 End */
5059
5060 end rbs_denorm;
5061
5062 -- -----------------------------------------------------------------------
5063 -- -----------------------------------------------------------------------
5064
5065
5066
5067
5068
5069
5070
5071 -- -----------------------------------------------------------------------
5072
5073 procedure rbs_denorm_online(
5074 p_worker_id in number,
5075 p_extraction_type in varchar2,
5076 p_rbs_version_id in number
5077 ) as
5078
5079
5080 -- -----------------------------------------------------------------------
5081 --
5082 -- History
5083 -- 19-MAR-2004 aartola Created
5084 --
5085 --
5086 -- *** This API assumes that the following tables exist and that they are
5087 -- properly populated (no cycles, correct relationships, etc)
5088 --
5089 -- PA_RBS_ELEMENTS
5090 --
5091 -- Then, this API populates output values in the following existing
5092 -- table:
5093 -- PJI_FP_AGGR_RBS_T
5094 --
5095 -- -----------------------------------------------------------------------
5096
5097 -- -----------------------------------------------------
5098 -- Declare statements --
5099
5100 l_rbs_level_id number;
5101 l_rbs_temp_parent number;
5102 l_rbs_temp_level number;
5103 l_rbs_node_count number;
5104 l_rbs_leaf_flag_id number;
5105 l_rbs_leaf_flag varchar2(1);
5106 l_rbs_leaf_flag1 varchar2(1); /* Added for bug 13421956 */
5107 /* Added for Bug 9099240 Start */
5108 l_rbs_id1 number;
5109 l_rbs_id2 number;
5110 l_max_rbs_level_id number;
5111 l_rbs_exists1 number;
5112 l_rbs_exists2 number;
5113 l_rbs_exists3 number;
5114 /* Added for Bug 9099240 End */
5115 l_denorm_count number := 1; /* Added for bug 12360547 */
5116 -- -----------------------------------------------------
5117
5118 begin
5119
5120 -- (RBS node = resource)
5121
5122 if g_pa_debug_mode = 'Y'
5123 then
5124 PJI_UTILS.WRITE2LOG(
5125 'PJI_PJP - Begin: rbs_denorm_online -'
5126 || ' p_worker_id = '
5127 || P_WORKER_ID
5128 || ' p_extraction_type = '
5129 || P_EXTRACTION_TYPE
5130 || ' p_rbs_version_id = '
5131 || P_RBS_VERSION_ID,
5132 null,
5133 g_msg_level_proc_call
5134 );
5135 end if;
5136
5137 /* Added for Bug 9099240 Start */
5138 l_rbs_id1 := NVL(PA_RBS_MAPPING.g_max_rbs_id1,0); -- Fix for Bug 13107900
5139 l_rbs_id2 := PA_RBS_MAPPING.g_max_rbs_id2;
5140 l_rbs_exists1 := 0;
5141 l_rbs_exists2 := 0;
5142 l_rbs_exists3 := 0;
5143 /* Added for Bug 9099240 End */
5144 -- -----------------------------------------------------
5145 /* Added for bug 12360547 starts */
5146 Select count(1) into l_denorm_count from pa_rbs_denorm where struct_version_id = p_rbs_version_id;
5147 /* Added for bug 12360547 ends */
5148
5149 -- Get deepest RBS node level --
5150
5151 select max(pvt_level.rbs_level)
5152 into l_rbs_level_id
5153 from PA_RBS_ELEMENTS pvt_level
5154 where 1=1
5155 and pvt_level.user_created_flag = 'N'
5156 and pvt_level.rbs_version_id = P_RBS_VERSION_ID;
5157
5158
5159 -- --------------------------------------------------------
5160 -- RBS nodes with no level (is null) are INVALID.
5161
5162 if l_rbs_level_id is null -- ###level_is_null###
5163 then
5164 if g_pa_debug_mode = 'Y'
5165 then
5166 PJI_UTILS.WRITE2LOG(
5167 'PJI_PJP - Level is null Data Corruption - p_rbs_version_id = '
5168 || P_RBS_VERSION_ID,
5169 null,
5170 g_msg_level_data_corruption
5171 );
5172 end if;
5173
5174 l_rbs_level_id := 1;
5175 end if;
5176
5177 -- -----------------------------------------------------
5178
5179 l_max_rbs_level_id := l_rbs_level_id; /* Added for Bug 9099240 */
5180
5181 -- -----------------------------------------------------
5182
5183 LOOP
5184
5185 if g_pa_debug_mode = 'Y'
5186 then
5187 PJI_UTILS.WRITE2LOG(
5188 'PJI_PJP - RBS Inserts - l_rbs_level_id = '
5189 || l_rbs_level_id,
5190 null,
5191 g_msg_level_high_detail
5192 );
5193 end if;
5194
5195 -- Get all RBS nodes from a certain level --
5196
5197 FOR RBS_NODE IN
5198 (
5199 select
5200 distinct
5201 -- pvt_nodes.project_id,
5202 pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
5203 pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
5204 pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
5205 -- pvt_nodes.rbs_group
5206 from PA_RBS_ELEMENTS pvt_nodes1
5207 where 1=1
5208 and pvt_nodes1.user_created_flag = 'N'
5209 and (
5210 pvt_nodes1.rbs_level = l_rbs_level_id
5211 or
5212 (
5213 l_rbs_level_id = 1
5214 and
5215 pvt_nodes1.rbs_level is null
5216 )
5217 )
5218 and pvt_nodes1.rbs_version_id = P_RBS_VERSION_ID
5219 and l_max_rbs_level_id = l_rbs_level_id /* Added for bug 10338478 */
5220 /* Added for Bug 9099240 Start */
5221 and ((pvt_nodes1.rbs_element_id between l_rbs_id1 and l_rbs_id2)
5222 or l_denorm_count = 0) /* Added for bug 12360547 */
5223 union all
5224 select
5225 distinct
5226 pvt_nodes1.rbs_version_id,
5227 pvt_nodes1.rbs_element_id,
5228 pvt_nodes1.parent_element_id
5229 from PA_RBS_ELEMENTS pvt_nodes1
5230 where 1=1
5231 and pvt_nodes1.user_created_flag = 'N'
5232 and (
5233 pvt_nodes1.rbs_level = l_rbs_level_id
5234 or
5235 (
5236 l_rbs_level_id = 1
5237 and
5238 pvt_nodes1.rbs_level is null
5239 )
5240 )
5241 and pvt_nodes1.rbs_version_id = P_RBS_VERSION_ID
5242 and l_max_rbs_level_id <> l_rbs_level_id
5243 /* Added for Bug 9099240 End */
5244 ) LOOP
5245
5246
5247 -- -----------------------------------------------------
5248 -- Check RBS node self --
5249
5250 -- Determine if the node to be inserted is a leaf
5251 -- If the node to be inserted has not been inserted before,
5252 -- then we know that the node is a leaf
5253
5254 select count(*)
5255 into l_rbs_node_count
5256 from PJI_FP_AGGR_RBS_T pdt_count
5257 where 1=1
5258 and pdt_count.sup_id = RBS_NODE.rbs_element_id
5259 and pdt_count.worker_id = P_WORKER_ID
5260 and rownum = 1;
5261
5262 -- l_rbs_leaf_flag_id --
5263 if l_rbs_node_count > 0
5264 then
5265 l_rbs_leaf_flag_id := 0;
5266 else
5267 l_rbs_leaf_flag_id := 1;
5268 end if;
5269
5270 -- l_rbs_leaf_flag -- (business rule)
5271 if (
5272 RBS_NODE.rbs_version_id = RBS_NODE.rbs_version_id
5273 or
5274 l_rbs_leaf_flag_id = 1
5275 )
5276 then
5277 l_rbs_leaf_flag := 'Y';
5278 else
5279 l_rbs_leaf_flag := 'N';
5280 end if;
5281
5282
5283 if g_pa_debug_mode = 'Y'
5284 then
5285 PJI_UTILS.WRITE2LOG(
5286 'PJI_PJP - Inserting RBS node self - rbs_element_id = '
5287 || RBS_NODE.rbs_element_id,
5288 null,
5289 g_msg_level_low_detail
5290 );
5291 end if;
5292
5293 /* Added for Bug 9099240 Start */
5294 begin
5295 select count(*) into l_rbs_exists1
5296 from pa_rbs_denorm den
5297 where den.struct_version_id = P_RBS_VERSION_ID
5298 and den.sup_id = RBS_NODE.rbs_element_id
5299 and den.sub_id = RBS_NODE.rbs_element_id
5300 and den.subro_id is null;
5301 exception
5302 when NO_DATA_FOUND then
5303 l_rbs_exists1 := 0;
5304 end;
5305 /* Added for Bug 9099240 End */
5306
5307 -- Insert RBS node self --
5308 if l_rbs_exists1 = 0 then /* Added for Bug 9099240 */
5309 insert
5310 into PJI_FP_AGGR_RBS_T
5311 (
5312 struct_version_id,
5313 sup_id,
5314 subro_id,
5315 sub_id,
5316 sup_level,
5317 sub_level,
5318 sub_leaf_flag_id,
5319 sub_leaf_flag,
5320 status_id,
5321 worker_id
5322 )
5323 values (
5324 RBS_NODE.rbs_version_id, -- rbs version id
5325 RBS_NODE.rbs_element_id, -- parent id
5326 null, -- immediate child id
5327 RBS_NODE.rbs_element_id, -- child id
5328 l_rbs_level_id, -- parent level
5329 l_rbs_level_id, -- child level
5330 l_rbs_leaf_flag_id, -- child leaf flag id
5331 l_rbs_leaf_flag, -- child leaf flag
5332 'self', -- status id
5333 P_WORKER_ID -- worker id
5334 );
5335 end if; /* Added for Bug 9099240 */
5336
5337 -- --------------------------------------------------------
5338 -- Check for RBS node's parent --
5339 -- Check only if the node is not a top most node (level = 1)
5340
5341 IF l_rbs_level_id <> 1
5342 THEN
5343
5344 FOR RBS_PARENT_NODE IN
5345 (
5346 select
5347 distinct
5348 prt_parent.parent_element_id
5349 from PA_RBS_ELEMENTS prt_parent
5350 where 1=1
5351 and prt_parent.user_created_flag = 'N'
5352 and prt_parent.rbs_element_id = RBS_NODE.rbs_element_id -- prt_parent.child_id
5353 ) LOOP
5354
5355
5356 -- l_rbs_temp_parent --
5357 l_rbs_temp_parent := RBS_PARENT_NODE.parent_element_id;
5358
5359
5360 -- Filter data corruption ###parent_is_null###
5361
5362 IF l_rbs_temp_parent is not null
5363 THEN
5364
5365
5366 -- l_rbs_leaf_flag --
5367 if (
5368 l_rbs_temp_parent = RBS_NODE.rbs_element_id
5369 or
5370 l_rbs_leaf_flag_id = 1
5371 )
5372 then
5373 l_rbs_leaf_flag := 'Y';
5374 else
5375 l_rbs_leaf_flag := 'N';
5376 end if;
5377
5378 if g_pa_debug_mode = 'Y'
5379 then
5380 PJI_UTILS.WRITE2LOG(
5381 'PJI_PJP - Inserting RBS node parent - l_rbs_temp_parent - = '
5382 || l_rbs_temp_parent,
5383 null,
5384 g_msg_level_low_detail
5385 );
5386 end if;
5387
5388 /* Added for Bug 9099240 Start */
5389 begin
5390 select count(*) into l_rbs_exists2
5391 from pa_rbs_denorm den
5392 where den.struct_version_id = P_RBS_VERSION_ID
5393 and den.sup_id = l_rbs_temp_parent
5394 and den.sub_id = RBS_NODE.rbs_element_id
5395 and den.subro_id = RBS_NODE.rbs_element_id;
5396 exception
5397 when NO_DATA_FOUND then
5398 l_rbs_exists2 := 0;
5399 end;
5400 /* Added for Bug 9099240 End */
5401
5402 -- Insert RBS node's parent --
5403 if l_rbs_exists2 = 0 then /* Added for Bug 9099240 */
5404 insert
5405 into PJI_FP_AGGR_RBS_T
5406 (
5407 struct_version_id,
5408 sup_id,
5409 subro_id,
5410 sub_id,
5411 sup_level,
5412 sub_level,
5413 sub_leaf_flag_id,
5414 sub_leaf_flag,
5415 status_id,
5416 worker_id
5417 )
5418 values (
5419 RBS_NODE.rbs_version_id, -- rbs version id
5420 l_rbs_temp_parent, -- parent id
5421 RBS_NODE.rbs_element_id, -- immediate child id
5422 RBS_NODE.rbs_element_id, -- child id
5423 l_rbs_level_id - 1, -- parent level
5424 l_rbs_level_id, -- child level
5425 l_rbs_leaf_flag_id, -- child leaf flag id
5426 l_rbs_leaf_flag, -- child leaf flag
5427 'parent', -- status id
5428 P_WORKER_ID -- worker id
5429 );
5430 end if; /*Added for Bug 9099240 */
5431
5432 /* Added for Bug 13421956 Start */
5433 begin
5434 select sub_leaf_flag into l_rbs_leaf_flag1
5435 from pa_rbs_denorm
5436 where struct_version_id = P_RBS_VERSION_ID
5437 and sub_id = l_rbs_temp_parent
5438 and subro_id = l_rbs_temp_parent;
5439 exception
5440 when NO_DATA_FOUND then
5441 null;
5442 end;
5443
5444 if l_rbs_leaf_flag1='Y' then
5445
5446 update pa_rbs_denorm den set sub_leaf_flag='N'
5447 where den.struct_version_id = P_RBS_VERSION_ID
5448 and den.sub_id = l_rbs_temp_parent
5449 and den.subro_id = l_rbs_temp_parent;
5450
5451 update pji_rbs_denorm den set sub_leaf_flag='N'
5452 where den.struct_version_id = P_RBS_VERSION_ID
5453 and den.sub_id = l_rbs_temp_parent
5454 and den.subro_id = l_rbs_temp_parent;
5455
5456 end if;
5457 /* Added for Bug 13421956 End */
5458
5459 -- --------------------------------------------------------
5460 -- Check for RBS node's children --
5461 -- Filter nodes to see if the node has children
5462
5463 FOR RBS_CHILDREN_NODE IN
5464 (
5465 select
5466 distinct
5467 pdt_child.sup_id,
5468 pdt_child.sub_id,
5469 pdt_child.sub_leaf_flag_id
5470 from PJI_FP_AGGR_RBS_T pdt_child
5471 where 1=1
5472 and pdt_child.sup_id = RBS_NODE.rbs_element_id
5473 and pdt_child.sup_id <> pdt_child.sub_id
5474 and pdt_child.worker_id = P_WORKER_ID
5475 ) LOOP
5476
5477 -- l_rbs_temp_level --
5478 select pdt_child1.sub_level
5479 into l_rbs_temp_level
5480 from PJI_FP_AGGR_RBS_T pdt_child1
5481 where 1=1
5482 and pdt_child1.sup_id = RBS_CHILDREN_NODE.sub_id
5483 and pdt_child1.sup_id = pdt_child1.sub_id
5484 and pdt_child1.worker_id = P_WORKER_ID;
5485
5486 -- l_rbs_leaf_flag --
5487 if (
5488 l_rbs_temp_parent = RBS_CHILDREN_NODE.sub_id
5489 or
5490 RBS_CHILDREN_NODE.sub_leaf_flag_id = 1
5491 )
5492 then
5493 l_rbs_leaf_flag := 'Y';
5494 else
5495 l_rbs_leaf_flag := 'N';
5496 end if;
5497
5498
5499 if g_pa_debug_mode = 'Y'
5500 then
5501 PJI_UTILS.WRITE2LOG(
5502 'PJI_PJP - Inserting RBS node child - sup_id = '
5503 || RBS_CHILDREN_NODE.sup_id,
5504 null,
5505 g_msg_level_low_detail
5506 );
5507 end if;
5508
5509 /* Added for Bug 9099240 Start */
5510 begin
5511 select count(*) into l_rbs_exists3
5512 from pa_rbs_denorm den
5513 where den.struct_version_id = P_RBS_VERSION_ID
5514 and den.sup_id = l_rbs_temp_parent
5515 and den.sub_id = RBS_CHILDREN_NODE.sub_id
5516 and den.subro_id = RBS_NODE.rbs_element_id
5517 and rownum = 1;
5518 exception
5519 when NO_DATA_FOUND then
5520 l_rbs_exists3 := 0;
5521 end;
5522 /* Added for Bug 9099240 End */
5523
5524 -- Insert RBS node's child --
5525 if l_rbs_exists3 = 0 then /* Added for Bug 9099240 */
5526 insert
5527 into PJI_FP_AGGR_RBS_T
5528 (
5529 struct_version_id,
5530 sup_id,
5531 subro_id,
5532 sub_id,
5533 sup_level,
5534 sub_level,
5535 sub_leaf_flag_id,
5536 sub_leaf_flag,
5537 status_id,
5538 worker_id
5539 )
5540 values (
5541 RBS_NODE.rbs_version_id, -- rbs version id
5542 l_rbs_temp_parent, -- parent id
5543 RBS_NODE.rbs_element_id, -- immediate child id
5544 RBS_CHILDREN_NODE.sub_id, -- child id
5545 l_rbs_level_id - 1, -- parent level
5546 l_rbs_temp_level, -- child level
5547 RBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
5548 l_rbs_leaf_flag, -- child leaf flag
5549 'children', -- status id
5550 P_WORKER_ID -- worker id
5551 );
5552 end if; /* Added for Bug 9099240 */
5553
5554 END LOOP; -- FOR RBS_CHILD_NODE
5555
5556 ELSE
5557
5558 if g_pa_debug_mode = 'Y'
5559 then
5560 PJI_UTILS.WRITE2LOG(
5561 'PJI_PJP - Parent Data Corruption - rbs_element_id = '
5562 || RBS_NODE.rbs_element_id,
5563 null,
5564 g_msg_level_data_corruption
5565 );
5566 end if;
5567
5568 END IF; -- ###parent_is_null###
5569
5570 END LOOP; -- FOR RBS_PARENT_NODE
5571
5572 END IF; -- if RBS_LEVEL <> 1
5573
5574 END LOOP; -- FOR RBS_NODE
5575
5576 -- Decrease rbs level --
5577 l_rbs_level_id := l_rbs_level_id - 1;
5578 exit when l_rbs_level_id = 0;
5579
5580 END LOOP; -- RBS_LEVEL
5581
5582 -- -------------------------------------------------
5583 if g_pa_debug_mode = 'Y'
5584 then
5585 PJI_UTILS.WRITE2LOG(
5586 'PJI_PJP - End: rbs_denorm_online',
5587 null,
5588 g_msg_level_proc_call
5589 );
5590 end if;
5591 -- -------------------------------------------------
5592 /* Added for Bug 9099240 Start */
5593 PA_RBS_MAPPING.g_max_rbs_id1 := PA_RBS_MAPPING.g_max_rbs_id2 + 1;
5594 PA_RBS_MAPPING.g_max_rbs_id2 := 0;
5595 /* Added for Bug 9099240 End */
5596
5597 end rbs_denorm_online;
5598
5599 -- -----------------------------------------------------------------------
5600 -- -----------------------------------------------------------------------
5601
5602
5603
5604
5605
5606
5607 -- -----------------------------------------------------------------------
5608
5609 procedure merge_xbs_denorm(
5610 p_worker_id in number,
5611 p_extraction_type in varchar2
5612 ) as
5613
5614 -- -----------------------------------------------------------------------
5615 --
5616 -- History
5617 -- 19-MAR-2004 aartola Created
5618 --
5619 --
5620 -- *** This procedure merges data from the denorm interim table
5621 -- (PJI_FP_AGGR_XBS) to the actual denorm table (PA_XBS_DENORM)
5622 --
5623 -- After calling this procedure, the contents of the interim table
5624 -- need to be deleted.
5625 --
5626 -- -----------------------------------------------------------------------
5627
5628 -- -----------------------------------------------------------------------
5629 -- Declare statements --
5630
5631 l_last_update_date date;
5632 l_last_updated_by number;
5633 l_creation_date date;
5634 l_created_by number;
5635 l_last_update_login number;
5636
5637 -- -----------------------------------------------------
5638
5639 begin
5640
5641 if g_pa_debug_mode = 'Y'
5642 then
5643 PJI_UTILS.WRITE2LOG(
5644 'PJI_PJP - Begin: merge_xbs_denorm -'
5645 || ' p_worker_id = '
5646 || P_WORKER_ID
5647 || ' p_extraction_type = '
5648 || P_EXTRACTION_TYPE,
5649 null,
5650 g_msg_level_proc_call
5651 );
5652 end if;
5653
5654 -- -----------------------------------------------------
5655 -- Variable Assignments --
5656
5657 l_last_update_date := sysdate;
5658 l_last_updated_by := FND_GLOBAL.USER_ID;
5659 l_creation_date := sysdate;
5660 l_created_by := FND_GLOBAL.USER_ID;
5661 l_last_update_login := FND_GLOBAL.LOGIN_ID;
5662
5663 -- -----------------------------------------------------
5664
5665 if (p_extraction_type = 'FULL' or p_extraction_type = 'INCREMENTAL') -- bug 13448040
5666
5667 then
5668
5669 insert
5670 into PA_XBS_DENORM
5671 (
5672 struct_type,
5673 prg_group,
5674 struct_emt_id,
5675 struct_version_id,
5676 sup_project_id,
5677 sup_id,
5678 sup_emt_id,
5679 subro_id,
5680 sub_id,
5681 sub_emt_id,
5682 sup_level,
5683 sub_level,
5684 sub_rollup_id,
5685 sub_leaf_flag,
5686 relationship_type,
5687 LAST_UPDATE_DATE,
5688 LAST_UPDATED_BY,
5689 CREATION_DATE,
5690 CREATED_BY,
5691 LAST_UPDATE_LOGIN
5692 )
5693 select
5694 distinct
5695 interim.struct_type,
5696 interim.prg_group,
5697 interim.struct_emt_id,
5698 interim.struct_version_id,
5699 interim.sup_project_id,
5700 interim.sup_id,
5701 interim.sup_emt_id,
5702 interim.subro_id,
5703 interim.sub_id,
5704 interim.sub_emt_id,
5705 interim.sup_level,
5706 interim.sub_level,
5707 interim.sub_rollup_id,
5708 interim.sub_leaf_flag,
5709 interim.relationship_type,
5710 l_last_update_date,
5711 l_last_updated_by,
5712 l_creation_date,
5713 l_created_by,
5714 l_last_update_login
5715 from PJI_FP_AGGR_XBS interim,
5716 PA_XBS_DENORM denorm
5717 where 1=1
5718 and interim.worker_id = p_worker_id
5719 and nvl(interim.struct_type, -1) = nvl(denorm.struct_type (+), -1)
5720 and nvl(interim.prg_group, -1) = nvl(denorm.prg_group (+), -1)
5721 and nvl(interim.struct_emt_id, -1) = nvl(denorm.struct_emt_id (+), -1)
5722 and nvl(interim.struct_version_id, -1) = nvl(denorm.struct_version_id (+), -1)
5723 and nvl(interim.sup_project_id, -1) = nvl(denorm.sup_project_id (+), -1)
5724 and nvl(interim.sup_id, -1) = nvl(denorm.sup_id (+), -1)
5725 and nvl(interim.sup_emt_id, -1) = nvl(denorm.sup_emt_id (+), -1)
5726 and nvl(interim.subro_id, -1) = nvl(denorm.subro_id (+), -1)
5727 and nvl(interim.sub_id, -1) = nvl(denorm.sub_id (+), -1)
5728 and nvl(interim.sub_emt_id, -1) = nvl(denorm.sub_emt_id (+), -1)
5729 and nvl(interim.sup_level, -1) = nvl(denorm.sup_level (+), -1)
5730 and nvl(interim.sub_level, -1) = nvl(denorm.sub_level (+), -1)
5731 and nvl(interim.sub_rollup_id, -1) = nvl(denorm.sub_rollup_id (+), -1)
5732 and nvl(interim.sub_leaf_flag, -1) = nvl(denorm.sub_leaf_flag (+), -1)
5733 and nvl(interim.relationship_type, -1) = nvl(denorm.relationship_type (+), -1)
5734 and denorm.struct_type is null
5735 order by
5736 interim.struct_version_id,
5737 interim.sup_id,
5738 interim.sub_id;
5739
5740 -- -------------------------
5741
5742 elsif p_extraction_type = 'ONLINE'
5743
5744 then
5745
5746 insert
5747 into PA_XBS_DENORM
5748 (
5749 struct_type,
5750 prg_group,
5751 struct_emt_id,
5752 struct_version_id,
5753 sup_project_id,
5754 sup_id,
5755 sup_emt_id,
5756 subro_id,
5757 sub_id,
5758 sub_emt_id,
5759 sup_level,
5760 sub_level,
5761 sub_rollup_id,
5762 sub_leaf_flag,
5763 relationship_type,
5764 LAST_UPDATE_DATE,
5765 LAST_UPDATED_BY,
5766 CREATION_DATE,
5767 CREATED_BY,
5768 LAST_UPDATE_LOGIN
5769 )
5770 select /*+ index(interim PJI_FP_AGGR_XBS_T_N1) */ -- Performance fix for bug 13109678
5771 distinct
5772 interim.struct_type,
5773 interim.prg_group,
5774 interim.struct_emt_id,
5775 interim.struct_version_id,
5776 interim.sup_project_id,
5777 interim.sup_id,
5778 interim.sup_emt_id,
5779 interim.subro_id,
5780 interim.sub_id,
5781 interim.sub_emt_id,
5782 interim.sup_level,
5783 interim.sub_level,
5784 interim.sub_rollup_id,
5785 interim.sub_leaf_flag,
5786 interim.relationship_type,
5787 l_last_update_date,
5788 l_last_updated_by,
5789 l_creation_date,
5790 l_created_by,
5791 l_last_update_login
5792 from PJI_FP_AGGR_XBS_T interim
5793 where interim.worker_id = p_worker_id
5794 order by
5795 interim.struct_version_id,
5796 interim.sup_id,
5797 interim.sub_id;
5798
5799 -- -------------------------
5800
5801 else -- UPGRADE, PARTIAL, INCREMENTAL
5802
5803 insert
5804 into PA_XBS_DENORM
5805 (
5806 struct_type,
5807 prg_group,
5808 struct_emt_id,
5809 struct_version_id,
5810 sup_project_id,
5811 sup_id,
5812 sup_emt_id,
5813 subro_id,
5814 sub_id,
5815 sub_emt_id,
5816 sup_level,
5817 sub_level,
5818 sub_rollup_id,
5819 sub_leaf_flag,
5820 relationship_type,
5821 LAST_UPDATE_DATE,
5822 LAST_UPDATED_BY,
5823 CREATION_DATE,
5824 CREATED_BY,
5825 LAST_UPDATE_LOGIN
5826 )
5827 select
5828 distinct
5829 interim.struct_type,
5830 interim.prg_group,
5831 interim.struct_emt_id,
5832 interim.struct_version_id,
5833 interim.sup_project_id,
5834 interim.sup_id,
5835 interim.sup_emt_id,
5836 interim.subro_id,
5837 interim.sub_id,
5838 interim.sub_emt_id,
5839 interim.sup_level,
5840 interim.sub_level,
5841 interim.sub_rollup_id,
5842 interim.sub_leaf_flag,
5843 interim.relationship_type,
5844 l_last_update_date,
5845 l_last_updated_by,
5846 l_creation_date,
5847 l_created_by,
5848 l_last_update_login
5849 from PJI_FP_AGGR_XBS interim
5850 where interim.worker_id = p_worker_id
5851 order by
5852 interim.struct_version_id,
5853 interim.sup_id,
5854 interim.sub_id;
5855
5856 -- -------------------------
5857
5858 end if;
5859
5860 -- -----------------------------------------------------
5861 if g_pa_debug_mode = 'Y'
5862 then
5863 PJI_UTILS.WRITE2LOG(
5864 'PJI_PJP - End: merge_xbs_denorm',
5865 null,
5866 g_msg_level_proc_call
5867 );
5868 end if;
5869 -- -----------------------------------------------------
5870
5871 end merge_xbs_denorm;
5872
5873 -- -----------------------------------------------------------------------
5874 -- -----------------------------------------------------------------------
5875
5876
5877
5878
5879
5880
5881 -- -----------------------------------------------------------------------
5882
5883 procedure merge_rbs_denorm(
5884 p_worker_id in number,
5885 p_extraction_type in varchar2
5886 ) as
5887
5888 -- -----------------------------------------------------------------------
5889 --
5890 -- History
5891 -- 19-MAR-2004 aartola Created
5892 --
5893 --
5894 -- *** This procedure merges data from the denorm interim table
5895 -- (PJI_FP_AGGR_RBS) to the actual denorm table (PA_RBS_DENORM)
5896 --
5897 -- After calling this procedure, the contents of the interim table
5898 -- need to be deleted.
5899 --
5900 -- -----------------------------------------------------------------------
5901
5902 -- -----------------------------------------------------
5903 -- Declare statements --
5904
5905 l_last_update_date date;
5906 l_last_updated_by number;
5907 l_creation_date date;
5908 l_created_by number;
5909 l_last_update_login number;
5910
5911 -- -----------------------------------------------------
5912
5913 begin
5914
5915 if g_pa_debug_mode = 'Y'
5916 then
5917 PJI_UTILS.WRITE2LOG(
5918 'PJI_PJP - Begin: merge_rbs_denorm -'
5919 || ' p_worker_id = '
5920 || P_WORKER_ID
5921 || ' p_extraction_type = '
5922 || P_EXTRACTION_TYPE,
5923 null,
5924 g_msg_level_proc_call
5925 );
5926 end if;
5927
5928 -- -----------------------------------------------------
5929 -- Variable assignments --
5930
5931 l_last_update_date := sysdate;
5932 l_last_updated_by := FND_GLOBAL.USER_ID;
5933 l_creation_date := sysdate;
5934 l_created_by := FND_GLOBAL.USER_ID;
5935 l_last_update_login := FND_GLOBAL.LOGIN_ID;
5936
5937 -- -----------------------------------------------------
5938
5939 if p_extraction_type = 'FULL'
5940
5941 then
5942
5943 insert
5944 into PA_RBS_DENORM
5945 (
5946 struct_version_id,
5947 sup_id,
5948 subro_id,
5949 sub_id,
5950 sup_level,
5951 sub_level,
5952 sub_leaf_flag,
5953 LAST_UPDATE_DATE,
5954 LAST_UPDATED_BY,
5955 CREATION_DATE,
5956 CREATED_BY,
5957 LAST_UPDATE_LOGIN
5958 )
5959 select
5960 interim.struct_version_id,
5961 interim.sup_id,
5962 interim.subro_id,
5963 interim.sub_id,
5964 interim.sup_level,
5965 interim.sub_level,
5966 interim.sub_leaf_flag,
5967 l_last_update_date,
5968 l_last_updated_by,
5969 l_creation_date,
5970 l_created_by,
5971 l_last_update_login
5972 from PJI_FP_AGGR_RBS interim,
5973 PA_RBS_DENORM denorm
5974 where 1=1
5975 and interim.worker_id = p_worker_id
5976 and nvl(interim.struct_version_id, -1) = nvl(denorm.struct_version_id (+), -1)
5977 and nvl(interim.sup_id, -1) = nvl(denorm.sup_id (+), -1)
5978 and nvl(interim.subro_id, -1) = nvl(denorm.subro_id (+), -1)
5979 and nvl(interim.sub_id, -1) = nvl(denorm.sub_id (+), -1)
5980 and nvl(interim.sup_level, -1) = nvl(denorm.sup_level (+), -1)
5981 and nvl(interim.sub_level, -1) = nvl(denorm.sub_level (+), -1)
5982 and nvl(interim.sub_leaf_flag, -1) = nvl(denorm.sub_leaf_flag (+), -1)
5983 and denorm.struct_version_id is null;
5984
5985 -- -------------------------
5986
5987 elsif p_extraction_type = 'ONLINE'
5988
5989 then
5990
5991 insert
5992 into PA_RBS_DENORM
5993 (
5994 struct_version_id,
5995 sup_id,
5996 subro_id,
5997 sub_id,
5998 sup_level,
5999 sub_level,
6000 sub_leaf_flag,
6001 LAST_UPDATE_DATE,
6002 LAST_UPDATED_BY,
6003 CREATION_DATE,
6004 CREATED_BY,
6005 LAST_UPDATE_LOGIN
6006 )
6007 select
6008 interim.struct_version_id,
6009 interim.sup_id,
6010 interim.subro_id,
6011 interim.sub_id,
6012 interim.sup_level,
6013 interim.sub_level,
6014 interim.sub_leaf_flag,
6015 l_last_update_date,
6016 l_last_updated_by,
6017 l_creation_date,
6018 l_created_by,
6019 l_last_update_login
6020 from PJI_FP_AGGR_RBS_T interim
6021 where interim.worker_id = p_worker_id;
6022
6023 -- -------------------------
6024
6025 else -- UPGRADE, PARTIAL, INCREMENTAL
6026
6027 insert
6028 into PA_RBS_DENORM
6029 (
6030 struct_version_id,
6031 sup_id,
6032 subro_id,
6033 sub_id,
6034 sup_level,
6035 sub_level,
6036 sub_leaf_flag,
6037 LAST_UPDATE_DATE,
6038 LAST_UPDATED_BY,
6039 CREATION_DATE,
6040 CREATED_BY,
6041 LAST_UPDATE_LOGIN
6042 )
6043 select
6044 interim.struct_version_id,
6045 interim.sup_id,
6046 interim.subro_id,
6047 interim.sub_id,
6048 interim.sup_level,
6049 interim.sub_level,
6050 interim.sub_leaf_flag,
6051 l_last_update_date,
6052 l_last_updated_by,
6053 l_creation_date,
6054 l_created_by,
6055 l_last_update_login
6056 from PJI_FP_AGGR_RBS interim
6057 where interim.worker_id = p_worker_id;
6058
6059 -- -------------------------
6060
6061 end if;
6062
6063 -- -----------------------------------------------------
6064 if g_pa_debug_mode = 'Y'
6065 then
6066 PJI_UTILS.WRITE2LOG(
6067 'PJI_PJP - End: merge_rbs_denorm',
6068 null,
6069 g_msg_level_proc_call
6070 );
6071 end if;
6072 -- -----------------------------------------------------
6073
6074 end merge_rbs_denorm;
6075
6076 -- -----------------------------------------------------------------------
6077 -- -----------------------------------------------------------------------
6078
6079
6080
6081
6082 -- -----------------------------------------------------------------------
6083
6084 procedure cleanup_xbs_denorm(
6085 p_worker_id in number,
6086 p_extraction_type in varchar2
6087 ) as
6088
6089 -- -----------------------------------------------------
6090
6091 begin
6092
6093 if g_pa_debug_mode = 'Y'
6094 then
6095 PJI_UTILS.WRITE2LOG(
6096 'PJI_PJP - Begin: cleanup_xbs_denorm -'
6097 || ' p_worker_id = '
6098 || P_WORKER_ID
6099 || ' p_extraction_type = '
6100 || P_EXTRACTION_TYPE,
6101 null,
6102 g_msg_level_proc_call
6103 );
6104 end if;
6105
6106 -- -----------------------------------------------------
6107
6108
6109 if p_extraction_type = 'ONLINE'
6110
6111 then
6112 delete
6113 from PJI_FP_AGGR_XBS_T
6114 where worker_id = p_worker_id;
6115
6116 else -- FULL, INCREMENTAL, PARTIAL, UPGRADE
6117
6118 delete
6119 from PJI_FP_AGGR_XBS
6120 where worker_id = p_worker_id;
6121
6122 end if;
6123
6124 -- -----------------------------------------------------
6125 if g_pa_debug_mode = 'Y'
6126 then
6127 PJI_UTILS.WRITE2LOG(
6128 'PJI_PJP - End: cleanup_xbs_denorm',
6129 null,
6130 g_msg_level_proc_call
6131 );
6132 end if;
6133 -- -----------------------------------------------------
6134
6135 end cleanup_xbs_denorm;
6136
6137 -- -----------------------------------------------------------------------
6138 -- -----------------------------------------------------------------------
6139
6140
6141
6142
6143
6144 -- -----------------------------------------------------------------------
6145
6146 procedure cleanup_rbs_denorm(
6147 p_worker_id in number,
6148 p_extraction_type in varchar2
6149 ) as
6150
6151 -- -----------------------------------------------------
6152
6153 begin
6154
6155 if g_pa_debug_mode = 'Y'
6156 then
6157 PJI_UTILS.WRITE2LOG(
6158 'PJI_PJP - Begin: cleanup_rbs_denorm -'
6159 || ' p_worker_id = '
6160 || P_WORKER_ID
6161 || ' p_extraction_type = '
6162 || P_EXTRACTION_TYPE,
6163 null,
6164 g_msg_level_proc_call
6165 );
6166 end if;
6167
6168 -- -----------------------------------------------------
6169
6170
6171 if p_extraction_type = 'ONLINE'
6172
6173 then
6174 delete
6175 from PJI_FP_AGGR_RBS_T
6176 where worker_id = p_worker_id;
6177
6178 else -- FULL, INCREMENTAL, PARTIAL, UPGRADE
6179
6180 delete
6181 from PJI_FP_AGGR_RBS
6182 where worker_id = p_worker_id;
6183 end if;
6184
6185 -- -----------------------------------------------------
6186 if g_pa_debug_mode = 'Y'
6187 then
6188 PJI_UTILS.WRITE2LOG(
6189 'PJI_PJP - End: cleanup_rbs_denorm',
6190 null,
6191 g_msg_level_proc_call
6192 );
6193 end if;
6194 -- -----------------------------------------------------
6195
6196 end cleanup_rbs_denorm;
6197
6198 -- -----------------------------------------------------------------------
6199 -- -----------------------------------------------------------------------
6200 /* Added for bug 10338478 Start */
6201 procedure rbs_denorm_rbs(
6202 p_worker_id in number,
6203 p_extraction_type in varchar2,
6204 p_rbs_version_id in number
6205 ) as
6206
6207 l_rbs_level_id number;
6208 l_rbs_temp_parent number;
6209 l_rbs_temp_level number;
6210 l_rbs_node_count number;
6211 l_rbs_leaf_flag_id number;
6212 l_rbs_leaf_flag varchar2(1);
6213
6214 begin
6215
6216 if P_EXTRACTION_TYPE = 'RBS' then
6217
6218 select max(pvt_level.rbs_level)
6219 into l_rbs_level_id
6220 from PA_RBS_ELEMENTS pvt_level,
6221 (
6222 select distinct event_type, event_object
6223 from PJI_PA_PROJ_EVENTS_LOG
6224 where 1=1
6225 and event_type = 'PJI_RBS_CHANGE'
6226 and worker_id = P_WORKER_ID
6227 ) log
6228 where 1=1
6229 and pvt_level.user_created_flag = 'N'
6230 and pvt_level.rbs_version_id = log.event_object;
6231
6232 else
6233 if g_pa_debug_mode = 'Y'
6234 then
6235 PJI_UTILS.WRITE2LOG(
6236 'PJI_PJP - No maximum level found - p_rbs_version_id = '
6237 || P_RBS_VERSION_ID,
6238 null,
6239 g_msg_level_data_corruption
6240 );
6241 end if;
6242
6243 l_rbs_level_id := 1; -- ###level_is_null###
6244 end if;
6245
6246
6247 -- --------------------------------------------------------
6248 -- RBS nodes with no level (is null) are INVALID.
6249
6250 if l_rbs_level_id is null -- ###level_is_null###
6251 then
6252 if g_pa_debug_mode = 'Y'
6253 then
6254 PJI_UTILS.WRITE2LOG(
6255 'PJI_PJP - Level is null Data Corruption - p_rbs_version_id = '
6256 || P_RBS_VERSION_ID,
6257 null,
6258 g_msg_level_data_corruption
6259 );
6260 end if;
6261
6262 l_rbs_level_id := 1;
6263 end if;
6264
6265 LOOP
6266
6267 if g_pa_debug_mode = 'Y'
6268 then
6269 PJI_UTILS.WRITE2LOG(
6270 'PJI_PJP - RBS Inserts - l_rbs_level_id = '
6271 || l_rbs_level_id,
6272 null,
6273 g_msg_level_high_detail
6274 );
6275 end if;
6276
6277 FOR RBS_NODE IN
6278 (
6279 select
6280 distinct
6281 -- pvt_nodes.project_id,
6282 pvt_nodes2.rbs_version_id, -- pvt_nodes.proj_element_id,
6283 pvt_nodes2.rbs_element_id, -- pvt_nodes.element_version_id,
6284 pvt_nodes2.parent_element_id -- pvt_nodes.parent_structure_version_id,
6285 -- pvt_nodes.rbs_group
6286 from PA_RBS_ELEMENTS pvt_nodes2,
6287 (
6288 select
6289 distinct
6290 log1.event_type, log1.event_object
6291 from PJI_PA_PROJ_EVENTS_LOG log1
6292 where 1=1
6293 and log1.event_type = 'PJI_RBS_CHANGE'
6294 and worker_id = P_WORKER_ID
6295 ) log11
6296 where 1=1
6297 and P_EXTRACTION_TYPE = 'RBS'
6298 and pvt_nodes2.user_created_flag = 'N'
6299 and pvt_nodes2.rbs_version_id = log11.event_object
6300 and pvt_nodes2.rbs_level = l_rbs_level_id
6301 ) LOOP
6302
6303
6304 select count(*)
6305 into l_rbs_node_count
6306 from PJI_FP_AGGR_RBS pdt_count
6307 where 1=1
6308 and pdt_count.sup_id = RBS_NODE.rbs_element_id
6309 and pdt_count.worker_id = P_WORKER_ID
6310 and rownum = 1;
6311
6312 -- l_rbs_leaf_flag_id --
6313 if l_rbs_node_count > 0
6314 then
6315 l_rbs_leaf_flag_id := 0;
6316 else
6317 l_rbs_leaf_flag_id := 1;
6318 end if;
6319
6320 -- l_rbs_leaf_flag -- (business rule)
6321 if (
6322 RBS_NODE.rbs_version_id = RBS_NODE.rbs_version_id
6323 or
6324 l_rbs_leaf_flag_id = 1
6325 )
6326 then
6327 l_rbs_leaf_flag := 'Y';
6328 else
6329 l_rbs_leaf_flag := 'N';
6330 end if;
6331
6332
6333 if g_pa_debug_mode = 'Y'
6334 then
6335 PJI_UTILS.WRITE2LOG(
6336 'PJI_PJP - Inserting RBS node self - rbs_element_id = '
6337 || RBS_NODE.rbs_element_id,
6338 null,
6339 g_msg_level_low_detail
6340 );
6341 end if;
6342
6343 -- Insert RBS node self --
6344 insert
6345 into PJI_FP_AGGR_RBS
6346 (
6347 struct_version_id,
6348 sup_id,
6349 subro_id,
6350 sub_id,
6351 sup_level,
6352 sub_level,
6353 sub_leaf_flag_id,
6354 sub_leaf_flag,
6355 status_id,
6356 worker_id
6357 )
6358 values (
6359 RBS_NODE.rbs_version_id, -- rbs version id
6360 RBS_NODE.rbs_element_id, -- parent id
6361 null, -- immediate child id
6362 RBS_NODE.rbs_element_id, -- child id
6363 l_rbs_level_id, -- parent level
6364 l_rbs_level_id, -- child level
6365 l_rbs_leaf_flag_id, -- child leaf flag id
6366 l_rbs_leaf_flag, -- child leaf flag
6367 'self', -- status id
6368 P_WORKER_ID -- worker id
6369 );
6370
6371 -- --------------------------------------------------------
6372 -- Check for RBS node's parent --
6373 -- Check only if the node is not a top most node (level = 1)
6374
6375 IF l_rbs_level_id <> 1
6376 THEN
6377
6378 FOR RBS_PARENT_NODE IN
6379 (
6380 select
6381 distinct
6382 prt_parent.parent_element_id
6383 from PA_RBS_ELEMENTS prt_parent
6384 where 1=1
6385 and prt_parent.user_created_flag = 'N'
6386 and prt_parent.rbs_element_id = RBS_NODE.rbs_element_id -- prt_parent.child_id
6387 ) LOOP
6388
6389
6390 -- l_rbs_temp_parent --
6391 l_rbs_temp_parent := RBS_PARENT_NODE.parent_element_id;
6392
6393
6394 -- Filter data corruption ###parent_is_null###
6395
6396 IF l_rbs_temp_parent is not null
6397 THEN
6398
6399
6400 -- l_rbs_leaf_flag --
6401 if (
6402 l_rbs_temp_parent = RBS_NODE.rbs_element_id
6403 or
6404 l_rbs_leaf_flag_id = 1
6405 )
6406 then
6407 l_rbs_leaf_flag := 'Y';
6408 else
6409 l_rbs_leaf_flag := 'N';
6410 end if;
6411
6412 if g_pa_debug_mode = 'Y'
6413 then
6414 PJI_UTILS.WRITE2LOG(
6415 'PJI_PJP - Inserting RBS node parent - l_rbs_temp_parent - = '
6416 || l_rbs_temp_parent,
6417 null,
6418 g_msg_level_low_detail
6419 );
6420 end if;
6421
6422 -- Insert RBS node's parent --
6423 insert
6424 into PJI_FP_AGGR_RBS
6425 (
6426 struct_version_id,
6427 sup_id,
6428 subro_id,
6429 sub_id,
6430 sup_level,
6431 sub_level,
6432 sub_leaf_flag_id,
6433 sub_leaf_flag,
6434 status_id,
6435 worker_id
6436 )
6437 values (
6438 RBS_NODE.rbs_version_id, -- rbs version id
6439 l_rbs_temp_parent, -- parent id
6440 RBS_NODE.rbs_element_id, -- immediate child id
6441 RBS_NODE.rbs_element_id, -- child id
6442 l_rbs_level_id - 1, -- parent level
6443 l_rbs_level_id, -- child level
6444 l_rbs_leaf_flag_id, -- child leaf flag id
6445 l_rbs_leaf_flag, -- child leaf flag
6446 'parent', -- status id
6447 P_WORKER_ID -- worker id
6448 );
6449
6450
6451 -- --------------------------------------------------------
6452 -- Check for RBS node's children --
6453 -- Filter nodes to see if the node has children
6454
6455 FOR RBS_CHILDREN_NODE IN
6456 (
6457 select
6458 distinct
6459 pdt_child.sup_id,
6460 pdt_child.sub_id,
6461 pdt_child.sub_leaf_flag_id
6462 from PJI_FP_AGGR_RBS pdt_child
6463 where 1=1
6464 and pdt_child.sup_id = RBS_NODE.rbs_element_id
6465 and pdt_child.sup_id <> pdt_child.sub_id
6466 and pdt_child.worker_id = P_WORKER_ID
6467 ) LOOP
6468
6469 -- l_rbs_temp_level --
6470 select pdt_child1.sub_level
6471 into l_rbs_temp_level
6472 from PJI_FP_AGGR_RBS pdt_child1
6473 where 1=1
6474 and pdt_child1.sup_id = RBS_CHILDREN_NODE.sub_id
6475 and pdt_child1.sup_id = pdt_child1.sub_id
6476 and pdt_child1.worker_id = P_WORKER_ID;
6477
6478 -- l_rbs_leaf_flag --
6479 if (
6480 l_rbs_temp_parent = RBS_CHILDREN_NODE.sub_id
6481 or
6482 RBS_CHILDREN_NODE.sub_leaf_flag_id = 1
6483 )
6484 then
6485 l_rbs_leaf_flag := 'Y';
6486 else
6487 l_rbs_leaf_flag := 'N';
6488 end if;
6489
6490
6491 if g_pa_debug_mode = 'Y'
6492 then
6493 PJI_UTILS.WRITE2LOG(
6494 'PJI_PJP - Inserting RBS node child - sup_id = '
6495 || RBS_CHILDREN_NODE.sup_id,
6496 null,
6497 g_msg_level_low_detail
6498 );
6499 end if;
6500
6501 -- Insert RBS node's child --
6502 insert
6503 into PJI_FP_AGGR_RBS
6504 (
6505 struct_version_id,
6506 sup_id,
6507 subro_id,
6508 sub_id,
6509 sup_level,
6510 sub_level,
6511 sub_leaf_flag_id,
6512 sub_leaf_flag,
6513 status_id,
6514 worker_id
6515 )
6516 values (
6517 RBS_NODE.rbs_version_id, -- rbs version id
6518 l_rbs_temp_parent, -- parent id
6519 RBS_NODE.rbs_element_id, -- immediate child id
6520 RBS_CHILDREN_NODE.sub_id, -- child id
6521 l_rbs_level_id - 1, -- parent level
6522 l_rbs_temp_level, -- child level
6523 RBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
6524 l_rbs_leaf_flag, -- child leaf flag
6525 'children', -- status id
6526 P_WORKER_ID -- worker id
6527 );
6528
6529 END LOOP; -- FOR RBS_CHILD_NODE
6530
6531 ELSE
6532
6533 if g_pa_debug_mode = 'Y'
6534 then
6535 PJI_UTILS.WRITE2LOG(
6536 'PJI_PJP - Parent Data Corruption - rbs_element_id = '
6537 || RBS_NODE.rbs_element_id,
6538 null,
6539 g_msg_level_data_corruption
6540 );
6541 end if;
6542
6543 END IF; -- ###parent_is_null###
6544
6545 END LOOP; -- FOR RBS_PARENT_NODE
6546
6547 END IF; -- if RBS_LEVEL <> 1
6548
6549 END LOOP; -- FOR RBS_NODE
6550
6551 -- Decrease rbs level --
6552 l_rbs_level_id := l_rbs_level_id - 1;
6553 exit when l_rbs_level_id = 0;
6554
6555 END LOOP; -- RBS_LEVEL
6556
6557 -- -----------------------------------------
6558 if g_pa_debug_mode = 'Y'
6559 then
6560 PJI_UTILS.WRITE2LOG(
6561 'PJI_PJP - End: rbs_denorm',
6562 null,
6563 g_msg_level_proc_call
6564 );
6565 end if;
6566 -- -----------------------------------------
6567 end rbs_denorm_rbs;
6568 /* Added for bug 10338478 End */
6569
6570
6571
6572 -- -----------------------------------------------------------------------
6573
6574 begin
6575
6576 -- -----------------------------------------------------
6577
6578 -- Declare global variables
6579
6580 g_pa_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
6581 g_msg_level_data_bug := 6;
6582 g_msg_level_data_corruption := 5;
6583 g_msg_level_proc_call := 3;
6584 g_msg_level_high_detail := 2;
6585 g_msg_level_low_detail := 1;
6586
6587 -- -----------------------------------------------------
6588
6589
6590 end PJI_PJP_SUM_DENORM;