[Home] [Help]
PACKAGE BODY: APPS.PJI_PJP_SUM_DENORM
Source
1 package body PJI_PJP_SUM_DENORM as
2 /* $Header: PJISP03B.pls 120.11 2007/01/25 23:02:45 degupta 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 -- -----------------------------------------------------
1365
1366 begin
1367
1368 l_denorm_type := nvl(p_denorm_type, 'ALL');
1369
1370
1371 if g_pa_debug_mode = 'Y'
1372 then
1373 PJI_UTILS.WRITE2LOG(
1374 'PJI_PJP - Begin: populate_rbs_denorm -'
1375 || ' p_worker_id = '
1376 || p_worker_id,
1377 null,
1378 g_msg_level_proc_call
1379 );
1380 end if;
1381
1382 -- -----------------------------------------------------
1383 -- Variable assignments --
1384
1385 l_process := PJI_PJP_SUM_MAIN.g_process
1386 || to_char(p_worker_id);
1387
1388 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(
1389 l_process,
1390 'EXTRACTION_TYPE'
1391 );
1392
1393 -- ----------------------------------------
1394
1395 if g_pa_debug_mode = 'Y'
1396 then
1397 PJI_UTILS.WRITE2LOG(
1398 'PJI_PJP - Variables -'
1399 || ' l_process = '
1400 || l_process
1401 || ' l_extraction_type = '
1402 || l_extraction_type,
1403 null,
1404 g_msg_level_high_detail
1405 );
1406 end if;
1407
1408 -- ----------------------------------------
1409
1410 -- -----------------------------------------------------
1411 -- Online Mode --
1412
1413 if p_denorm_type = 'RBS'
1414
1415 then
1416
1417 -- ----------------------------------------------
1418 -- delete RBS slices for specific struct_version_id -- ###delete###
1419
1420 if g_pa_debug_mode = 'Y'
1421 then
1422 PJI_UTILS.WRITE2LOG(
1423 'PJI_PJP - Delete specific RBS slices -'
1424 || ' p_rbs_version_id = '
1425 || p_rbs_version_id,
1426 null,
1427 g_msg_level_high_detail
1428 );
1429 end if;
1430
1431 delete
1432 from PA_RBS_DENORM
1433 where 1=1
1434 and STRUCT_VERSION_ID = p_rbs_version_id;
1435
1436 -- ----------------------------------------------
1437 -- Repopulate PA_RBS_DENORM for specific struct_version_id
1438
1439 cleanup_rbs_denorm(
1440 p_worker_id,
1441 'ONLINE'
1442 );
1443
1444 rbs_denorm_online(
1445 p_worker_id,
1446 'ONLINE',
1447 p_rbs_version_id
1448 );
1449
1450 merge_rbs_denorm(
1451 p_worker_id,
1452 'ONLINE'
1453 );
1454
1455 -- don't delete contents from PJI_FP_AGGR_RBS_T
1456
1457 -- Sadiq will call cleanup_xbs_denorm(p_worker_id, 'ONLINE')
1458
1459
1460
1461 -- -----------------------------------------------------
1462 -- Bulk Mode --
1463
1464 elsif p_denorm_type = 'ALL'
1465
1466 then
1467
1468 -- ----------------------------------------------
1469 -- process RBS hiearchies during PJP summarization
1470
1471 if (
1472 not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(
1473 l_process,
1474 'PJI_PJP_SUM_DENORM.POPULATE_RBS_DENORM(p_worker_id);'
1475 )
1476 )
1477 then
1478 return;
1479 end if;
1480
1481 -- -------------------------------------------------------
1482 -- cleanup interim tables
1483 cleanup_rbs_denorm(
1484 p_worker_id,
1485 l_extraction_type
1486 );
1487
1488 -- -------------------------------------------------------
1489 --Bug 4293903: In FULL mode if FPM upgrade is done treat as
1490 --incremental
1491 ----------------------------------------------------------
1492 l_fpm_upgrade := PJI_UTILS.GET_PARAMETER('PJI_FPM_UPGRADE');
1493
1494 IF (nvl(l_fpm_upgrade,'P') = 'C' and
1495 l_extraction_type = 'FULL') THEN
1496
1497 l_extraction_type := 'INCREMENTAL';
1498
1499 END IF;
1500
1501 -- Bulk Full mode --
1502
1503 if l_extraction_type = 'FULL'
1504
1505 then
1506
1507
1508
1509 -- PA_RBS_DENORM is empty, populate from scratch
1510
1511 --
1512 -- RBS normalization algorithm for all data in
1513 -- the structures tables.
1514 --
1515
1516 rbs_denorm(
1517 p_worker_id,
1518 l_extraction_type,
1519 null
1520 );
1521
1522 merge_rbs_denorm(
1523 p_worker_id,
1524 l_extraction_type
1525 );
1526
1527 cleanup_rbs_denorm(
1528 p_worker_id,
1529 l_extraction_type
1530 );
1531
1532 -- ----------------------------------
1533 -- Bulk Incremental/Partial mode --
1534
1535 elsif (
1536 l_extraction_type = 'INCREMENTAL'
1537 or
1538 l_extraction_type = 'PARTIAL'
1539 or
1540 l_extraction_type = 'RBS'
1541 )
1542 then
1543 -- PA_RBS_DENORM contains data, repopulate a portion of it
1544
1545 delete
1546 from PA_RBS_DENORM
1547 where STRUCT_VERSION_ID in
1548 (
1549 select EVENT_OBJECT
1550 from PJI_PA_PROJ_EVENTS_LOG
1551 where 1=1
1552 and EVENT_TYPE = 'PJI_RBS_CHANGE'
1553 and WORKER_ID = P_WORKER_ID
1554 );
1555
1556 --
1557 -- RBS normalization algorithm based on:
1558 --
1559 -- EVENT_TYPE = 'PJI_RBS_CHANGE' ==> EVENT_OBJECT = structure version
1560 --
1561 --
1562 --
1563 -- *** Use PJI_PA_PROJ_EVENTS_LOG *** to determine which RBS
1564 -- data to process. ***
1565 --
1566
1567 rbs_denorm(
1568 p_worker_id,
1569 l_extraction_type,
1570 null
1571 );
1572
1573 merge_rbs_denorm(
1574 p_worker_id,
1575 l_extraction_type
1576 );
1577
1578 cleanup_rbs_denorm(
1579 p_worker_id,
1580 l_extraction_type
1581 );
1582
1583 end if;
1584
1585 -- -----------------------------------------------------
1586
1587 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(
1588 l_process,
1589 'PJI_PJP_SUM_DENORM.POPULATE_RBS_DENORM(p_worker_id);'
1590 );
1591
1592 commit;
1593 -- -----------------------------------------------------
1594
1595 end if;
1596
1597 if g_pa_debug_mode = 'Y'
1598 then
1599 PJI_UTILS.WRITE2LOG(
1600 'PJI_PJP - End: populate_rbs_denorm',
1601 null,
1602 g_msg_level_proc_call
1603 );
1604 end if;
1605 -- -----------------------------------------------------
1606
1607 end POPULATE_RBS_DENORM;
1608
1609 -- -----------------------------------------------------------------------
1610 -- -----------------------------------------------------------------------
1611
1612
1613
1614
1615
1616
1617 -- -----------------------------------------------------
1618 -- procedure POPULATE_RBS_DENORM_UPGRADE
1619 -- -----------------------------------------------------
1620
1621 --
1622 -- NOTE: Since this API updates both PA_RBS_DENORM and PJI_RBS_DENORM, it
1623 -- should only be used during upgrade since change context between
1624 -- PA_RBS_DENORM and PJI_RBS_DENORM is lost for the given RBS version.
1625 --
1626
1627 -- 30-JUL-2004 jwhite Bug 3802762
1628 -- Because of savepoint issues with
1629 -- RES_LIST_TO_PLAN_RES_LIST api, commented-out
1630 -- commit in POPULATE_RBS_DENORM_UPGRADE
1631 --
1632 -- -----------------------------------------------------
1633
1634 procedure POPULATE_RBS_DENORM_UPGRADE(
1635 p_rbs_version_id in number,
1636 x_return_status out nocopy varchar2,
1637 x_msg_count out nocopy number,
1638 x_msg_data out nocopy varchar2
1639 ) is
1640
1641 -- -----------------------------------------------------
1642 -- Declare staments
1643
1644 l_worker_id number;
1645
1646 l_last_update_date date;
1647 l_last_updated_by number;
1648 l_creation_date date;
1649 l_created_by number;
1650 l_last_update_login number;
1651
1652
1653 -- -----------------------------------------------------
1654
1655 begin
1656
1657 l_worker_id := 1;
1658
1659
1660 if g_pa_debug_mode = 'Y'
1661 then
1662 PJI_UTILS.WRITE2LOG(
1663 'PJI_PJP - Begin: populate_rbs_denorm_upgrade -'
1664 || ' p_rbs_version_id = '
1665 || p_rbs_version_id,
1666 null,
1667 g_msg_level_proc_call
1668 );
1669 end if;
1670
1671 -- -----------------------------------------------------
1672
1673 delete
1674 from PA_RBS_DENORM
1675 where STRUCT_VERSION_ID = p_rbs_version_id;
1676
1677 delete
1678 from PJI_RBS_DENORM
1679 where STRUCT_VERSION_ID = p_rbs_version_id;
1680
1681 -- -----------------------------------------------------
1682 -- Populate PA_RBS_DENORM for a single RBS version (should not commit)
1683
1684 rbs_denorm(
1685 l_worker_id,
1686 'UPGRADE',
1687 p_rbs_version_id
1688 );
1689
1690 merge_rbs_denorm(
1691 l_worker_id,
1692 'UPGRADE'
1693 );
1694
1695 cleanup_rbs_denorm(
1696 l_worker_id,
1697 'UPGRADE'
1698 );
1699
1700 -- -----------------------------------------------------
1701
1702 l_last_update_date := sysdate;
1703 l_last_updated_by := FND_GLOBAL.USER_ID;
1704 l_creation_date := sysdate;
1705 l_created_by := FND_GLOBAL.USER_ID;
1706 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1707
1708
1709 -- -----------------------------------------------------
1710
1711 insert
1712 into PJI_RBS_DENORM
1713 (
1714 struct_version_id,
1715 sup_id,
1716 subro_id,
1717 sub_id,
1718 sup_level,
1719 sub_level,
1720 sub_leaf_flag,
1721 LAST_UPDATE_DATE,
1722 LAST_UPDATED_BY,
1723 CREATION_DATE,
1724 CREATED_BY,
1725 LAST_UPDATE_LOGIN
1726 )
1727 select
1728 struct_version_id,
1729 sup_id,
1730 subro_id,
1731 sub_id,
1732 sup_level,
1733 sub_level,
1734 sub_leaf_flag,
1735 l_last_update_date,
1736 l_last_updated_by,
1737 l_creation_date,
1738 l_created_by,
1739 l_last_update_login
1740 from PA_RBS_DENORM
1741 where STRUCT_VERSION_ID = p_rbs_version_id;
1742
1743 -- -----------------------------------------------------
1744
1745 x_return_status := FND_API.G_RET_STS_SUCCESS;
1746 x_msg_count := 0;
1747 x_msg_data := null;
1748
1749 -- -----------------------------------------------------
1750 -- Bug 3802762, 30-JUL-2004, jwhite -----------------------------
1751 -- Commented-out commit becuase of savepoint issue with a calling api
1752
1753 --commit;
1754
1755 -- End Bug 3802762, 30-JUL-2004, jwhite --------------------------
1756
1757 -- -----------------------------------------------------
1758 if g_pa_debug_mode = 'Y'
1759 then
1760 PJI_UTILS.WRITE2LOG(
1761 'PJI_PJP - End: populate_rbs_denorm_upgrade',
1762 null,
1763 g_msg_level_proc_call
1764 );
1765 end if;
1766 -- -----------------------------------------------------
1767
1768 end POPULATE_RBS_DENORM_UPGRADE;
1769
1770 -- -----------------------------------------------------------------------
1771 -- -----------------------------------------------------------------------
1772
1773
1774
1775
1776
1777
1778 -- -----------------------------------------------------------------------
1779
1780 procedure prg_denorm(
1781 p_worker_id in number,
1782 p_extraction_type in varchar2
1783 ) as
1784
1785 -- -----------------------------------------------------------------------
1786 --
1787 -- History
1788 -- 19-MAR-2004 aartola Created
1789 --
1790 --
1791 -- *** This API assumes that the following tables exist and that they are
1792 -- properly populated (no cycles, correct relationships, etc)
1793 --
1794 -- PA_PROJ_ELEMENT_VERSIONS
1795 -- PA_OBJECT_RELATIONSHIPS
1796 --
1797 -- Then, this API populates output values in the following existing
1798 -- table:
1799 -- PJI_FP_AGGR_XBS
1800 --
1801 -- When P_EXTRACTION_TYPE equals 'FULL', this API calls the following
1802 -- procedure:
1803 -- wbs_denorm
1804 --
1805 -- -----------------------------------------------------------------------
1806
1807 -- -----------------------------------------------------
1808 -- Declare statements --
1809
1810
1811 l_prg_temp_parent number;
1812 l_prg_temp_level number;
1813 l_prg_node_count number;
1814 l_prg_leaf_flag_id number;
1815 l_prg_leaf_flag varchar2(1);
1816
1817 l_prj_temp_parent number;
1818 l_prg_temp_rollup number;
1819 l_prg_temp_sup_emt number;
1820 l_prg_temp_sub_emt number;
1821
1822 l_prg_element_version_count number;
1823
1824 l_prg_dummy_rollup number;
1825 l_prg_dummy_task_flag varchar2(1);
1826
1827 l_project_id number;
1828
1829 -- -----------------------------------------------------
1830
1831 begin
1832
1833 -- (PRG node = program)
1834
1835
1836 if g_pa_debug_mode = 'Y'
1837 then
1838 PJI_UTILS.WRITE2LOG(
1839 'PJI_PJP - Begin: prg_denorm -'
1840 || ' p_worker_id = '
1841 || P_WORKER_ID
1842 || ' p_extraction_type = '
1843 || P_EXTRACTION_TYPE,
1844 null,
1845 g_msg_level_proc_call
1846 );
1847 end if;
1848 -- --------------------------------------------------------
1849 -- Get all PRG nodes from a certain level --
1850
1851 -- Look only at the data to be processed
1852 -- 1) FULL - All data
1853 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs
1854 -- table
1855 -- 2.1) PRG nodes changes
1856 -- 2.2) PRG nodes additions
1857
1858 l_project_id := null;
1859
1860 FOR PRG_NODE IN
1861 (
1862 select
1863 distinct
1864 PRG_LEVEL,
1865 PROJECT_ID,
1866 PROJ_ELEMENT_ID,
1867 ELEMENT_VERSION_ID,
1868 PARENT_STRUCTURE_VERSION_ID,
1869 PRG_GROUP
1870 from
1871 (
1872 select /*+ ordered */ -- all Projects in batch_map
1873 distinct
1874 1 prg_level,
1875 ver.project_id,
1876 ver.proj_element_id,
1877 ver.element_version_id,
1878 ver.parent_structure_version_id,
1879 ver.prg_group
1880 from
1881 PJI_PJP_PROJ_BATCH_MAP map,
1882 PA_PROJ_ELEMENT_VERSIONS ver
1883 where
1884 p_extraction_type in ('FULL', 'UPGRADE') and
1885 ver.object_type = 'PA_STRUCTURES' and
1886 ver.prg_group is null and
1887 map.worker_id = p_worker_id and
1888 map.PJI_PROJECT_STATUS is null and
1889 ver.project_id = map.project_id
1890 UNION ALL
1891 select /*+ ordered */
1892 -- Programs (UPGRADE only. In FULL, batch_map has necessary projects)
1893 distinct
1894 pvt_nodes1.prg_level,
1895 pvt_nodes1.project_id,
1896 pvt_nodes1.proj_element_id,
1897 pvt_nodes1.element_version_id,
1898 pvt_nodes1.parent_structure_version_id,
1899 pvt_nodes1.prg_group
1900 from
1901 (
1902 select /*+ ordered */
1903 distinct
1904 ver.prg_group
1905 from
1906 PA_PROJ_ELEMENT_VERSIONS ver,
1907 PJI_PJP_PROJ_BATCH_MAP map
1908 where
1909 p_extraction_type in ('FULL', 'UPGRADE') and
1910 ver.object_type = 'PA_STRUCTURES' and
1911 ver.prg_group is not null and
1912 map.worker_id = p_worker_id and
1913 map.PJI_PROJECT_STATUS is null and
1914 ver.project_id = map.project_id
1915 ) batch_map,
1916 PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
1917 where
1918 p_extraction_type in ('FULL', 'UPGRADE') and
1919 pvt_nodes1.object_type = 'PA_STRUCTURES' and
1920 pvt_nodes1.prg_group is not null and
1921 pvt_nodes1.prg_group = batch_map.prg_group
1922 UNION ALL
1923 select
1924 distinct
1925 pvt_nodes2.prg_level,
1926 pvt_nodes2.project_id,
1927 pvt_nodes2.proj_element_id,
1928 pvt_nodes2.element_version_id,
1929 pvt_nodes2.parent_structure_version_id,
1930 pvt_nodes2.prg_group
1931 from PA_PROJ_ELEMENT_VERSIONS pvt_nodes2,
1932 (
1933 select
1934 distinct
1935 decode( invert.id,
1936 1, i_log.event_object,
1937 2, i_log.attribute1
1938 ) event_object_id
1939 from PJI_PA_PROJ_EVENTS_LOG i_log,
1940 (
1941 select 1 id
1942 from dual
1943 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
1944 UNION ALL
1945 select 2 id
1946 from dual
1947 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
1948 ) invert
1949 where 1=1
1950 and i_log.worker_id = P_WORKER_ID
1951 and i_log.event_type = 'PRG_CHANGE'
1952 ) log11
1953 where 1=1
1954 and pvt_nodes2.prg_group = log11.event_object_id
1955 and pvt_nodes2.object_type = 'PA_STRUCTURES'
1956 and pvt_nodes2.prg_group IS NOT NULl /* 4904076 */
1957 UNION ALL
1958 select
1959 distinct
1960 1 prg_level,
1961 pvt_nodes3.project_id,
1962 pvt_nodes3.proj_element_id,
1963 pvt_nodes3.element_version_id,
1964 pvt_nodes3.parent_structure_version_id,
1965 pvt_nodes3.prg_group
1966 from PA_PROJ_ELEMENT_VERSIONS pvt_nodes3,
1967 (
1968 select
1969 distinct
1970 decode( invert.id,
1971 1, i_log.event_object,
1972 2, i_log.attribute1
1973 ) event_object_id
1974 from PJI_PA_PROJ_EVENTS_LOG i_log,
1975 (
1976 select 1 id
1977 from dual
1978 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
1979 UNION ALL
1980 select 2 id
1981 from dual
1982 where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
1983 ) invert
1984 where 1=1
1985 and i_log.worker_id = P_WORKER_ID
1986 and i_log.event_type = 'PRG_CHANGE'
1987 and i_log.event_object = -1
1988 ) log22
1989 where 1=1
1990 and pvt_nodes3.project_id = log22.event_object_id -- log22.attribute2
1991 and pvt_nodes3.object_type = 'PA_STRUCTURES'
1992 and pvt_nodes3.prg_group is null
1993 )
1994 order by
1995 PRG_LEVEL DESC, /* This DESC order by will take care the hierarchy of projects*/
1996 PROJECT_ID,
1997 PROJ_ELEMENT_ID,
1998 ELEMENT_VERSION_ID,
1999 PARENT_STRUCTURE_VERSION_ID,
2000 PRG_GROUP
2001 ) LOOP
2002
2003 if (p_extraction_type = 'FULL' or p_extraction_type = 'UPGRADE') then
2004
2005 if (l_project_id is null) then
2006
2007 l_project_id := prg_node.PROJECT_ID;
2008
2009 elsif (l_project_id <> prg_node.PROJECT_ID) then
2010
2011 update PJI_PJP_PROJ_BATCH_MAP
2012 set PJI_PROJECT_STATUS = 'C'
2013 where WORKER_ID = p_worker_id and
2014 PROJECT_ID = l_project_id;
2015
2016 commit;
2017
2018 l_project_id := prg_node.PROJECT_ID;
2019
2020 end if;
2021
2022 end if;
2023
2024 IF PRG_NODE.prg_level > 1 -- ###prg_group_is_null###
2025 and
2026 PRG_NODE.prg_group is null
2027
2028 THEN
2029 if g_pa_debug_mode = 'Y'
2030 then
2031 PJI_UTILS.WRITE2LOG(
2032 'PJI_PJP - PRG Group is null Data Bug - element_version_id = '
2033 || PRG_NODE.element_version_id,
2034 null,
2035 g_msg_level_data_bug
2036 );
2037 end if;
2038 ELSE
2039
2040 -- --------------------------------------------------------
2041 -- Check program self --
2042
2043 -- Determine if the node to be inserted is a leaf
2044 -- If the node to be inserted has not been inserted before,
2045 -- then we know that the node is a leaf
2046 if PRG_NODE.prg_group is not null then
2047 select count(*)
2048 into l_prg_node_count
2049 from PJI_FP_AGGR_XBS pdt_count
2050 where 1=1
2051 and pdt_count.sup_id = PRG_NODE.element_version_id
2052 and pdt_count.worker_id = P_WORKER_ID
2053 and rownum = 1;
2054 else
2055 l_prg_node_count := 0;
2056 end if;
2057 -- l_prg_leaf_flag_id --
2058 if l_prg_node_count > 0
2059 then
2060 l_prg_leaf_flag_id := 0;
2061 else
2062 l_prg_leaf_flag_id := 1;
2063 end if;
2064
2065 -- l_prg_leaf_flag -- (business rule)
2066 if (
2067 PRG_NODE.element_version_id = PRG_NODE.element_version_id
2068 or
2069 l_prg_leaf_flag_id = 1
2070 )
2071 then
2072 l_prg_leaf_flag := 'Y';
2073 else
2074 l_prg_leaf_flag := 'N';
2075 end if;
2076
2077
2078 if g_pa_debug_mode = 'Y'
2079 then
2080 PJI_UTILS.WRITE2LOG(
2081 'PJI_PJP - Inserting PRG node self -'
2082 || ' sup_id = '
2083 || PRG_NODE.element_version_id,
2084 null,
2085 g_msg_level_low_detail
2086 );
2087 end if;
2088
2089
2090 -- Insert PRG node self --
2091 insert
2092 into PJI_FP_AGGR_XBS
2093 (
2094 struct_type,
2095 prg_group,
2096 struct_version_id,
2097 sup_project_id,
2098 sup_id,
2099 sup_emt_id,
2100 subro_id,
2101 sub_id,
2102 sub_emt_id,
2103 sup_level,
2104 sub_level,
2105 sub_rollup_id,
2106 sub_leaf_flag_id,
2107 sub_leaf_flag,
2108 status_id,
2109 worker_id
2110 )
2111 values (
2112 'PRG', -- structure type
2113 PRG_NODE.prg_group, -- prg group
2114 null, -- structure version id
2115 PRG_NODE.project_id, -- parent project id
2116 PRG_NODE.element_version_id, -- parent id
2117 PRG_NODE.proj_element_id, -- sup emt id
2118 null, -- immediate child id
2119 PRG_NODE.element_version_id, -- child id
2120 PRG_NODE.proj_element_id, -- sub emt_id
2121 PRG_NODE.prg_level, -- parent level
2122 PRG_NODE.prg_level, -- child level
2123 PRG_NODE.proj_element_id, -- child rollup id
2124 l_prg_leaf_flag_id, -- child leaf flag id
2125 l_prg_leaf_flag, -- child leaf flag
2126 'self', -- status id
2127 P_WORKER_ID -- worker id
2128 );
2129
2130 -- --------------------------------------------------------
2131 -- Check for PRG node's parents --
2132 -- Check only if the node is not a top most node (level = 1)
2133
2134 IF PRG_NODE.prg_level <> 1
2135
2136 THEN
2137
2138 FOR PRG_PARENT_NODE IN
2139 (
2140 select
2141 distinct
2142 prt_parent.object_id_from1,
2143 prt_parent.relationship_type,
2144 ver.prg_level
2145 from PA_OBJECT_RELATIONSHIPS prt_parent,
2146 PA_PROJ_ELEMENT_VERSIONS ver
2147 where 1=1
2148 and prt_parent.object_id_to1 = PRG_NODE.element_version_id
2149 and prt_parent.object_type_from = 'PA_TASKS'
2150 and prt_parent.object_type_to = 'PA_STRUCTURES'
2151 and (
2152 prt_parent.relationship_type = 'LF'
2153 or
2154 prt_parent.relationship_type = 'LW'
2155 )
2156 and ver.element_version_id = prt_parent.object_id_from1
2157 ) LOOP
2158
2159 select pvt_parent1.parent_structure_version_id,
2160 pvt_parent1.project_id,
2161 pvt_parent1.proj_element_id
2162 into l_prg_temp_parent,
2163 l_prj_temp_parent,
2164 l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
2165 from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
2166 where 1=1
2167 and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2168
2169 -- l_prg_dummy_task_flag -- ###dummy###
2170 select link_task_flag
2171 into l_prg_dummy_task_flag
2172 from pa_proj_elements
2173 where 1=1
2174 and proj_element_id = l_prg_dummy_rollup;
2175
2176 -- l_prg_temp_rollup
2177 if l_prg_dummy_task_flag = 'N'
2178
2179 then
2180 l_prg_temp_rollup := l_prg_dummy_rollup;
2181
2182 else
2183 /*
2184 select dt_ver1.proj_element_id
2185 into l_prg_temp_rollup
2186 from pa_object_relationships dt_rel,
2187 pa_proj_element_versions dt_ver1,
2188 pa_proj_element_versions dt_ver2
2189 where 1=1
2190 and dt_ver1.element_version_id = dt_rel.object_id_from1
2191 and dt_rel.object_type_from = 'PA_TASKS'
2192 and dt_rel.object_type_to = 'PA_TASKS'
2193 and dt_rel.object_id_to1 = dt_ver2.element_version_id
2194 and dt_ver2.proj_element_id = l_prg_dummy_rollup;
2195 */
2196
2197 -- Bug 3838523
2198 select dt_ver1.proj_element_id
2199 into l_prg_temp_rollup
2200 from pa_object_relationships dt_rel,
2201 pa_proj_element_versions dt_ver1
2202 where 1=1
2203 and dt_ver1.element_version_id = dt_rel.object_id_from1
2204 and dt_rel.object_type_from = 'PA_TASKS'
2205 and dt_rel.object_type_to = 'PA_TASKS'
2206 and dt_rel.object_id_to1 = PRG_PARENT_NODE.object_id_from1;
2207
2208 end if;
2209
2210 -- l_prg_temp_sup_emt --
2211 select pvt_parent4.proj_element_id
2212 into l_prg_temp_sup_emt
2213 from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
2214 where 1=1
2215 and pvt_parent4.element_version_id = l_prg_temp_parent;
2216
2217 -- l_prg_temp_sub_emt --
2218 select pvt_parent5.proj_element_id
2219 into l_prg_temp_sub_emt
2220 from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
2221 where 1=1
2222 and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
2223
2224 -- l_prg_leaf_flag --
2225 if (
2226 l_prg_temp_parent = PRG_NODE.element_version_id
2227 or
2228 l_prg_leaf_flag_id = 1
2229 )
2230 then
2231 l_prg_leaf_flag := 'Y';
2232 else
2233 l_prg_leaf_flag := 'N';
2234 end if;
2235
2236
2237 if g_pa_debug_mode = 'Y'
2238 then
2239 PJI_UTILS.WRITE2LOG(
2240 'PJI_PJP - Inserting PRG node parent -'
2241 || ' element_version_id = '
2242 || PRG_NODE.element_version_id
2243 || ' sup_id = '
2244 || l_prg_temp_parent
2245 || ' sub_rollup_id = '
2246 || l_prg_temp_rollup,
2247 null,
2248 g_msg_level_low_detail
2249 );
2250 end if;
2251
2252 -- Insert PRG node's parent --
2253 insert
2254 into PJI_FP_AGGR_XBS
2255 (
2256 struct_type,
2257 prg_group,
2258 struct_version_id,
2259 sup_project_id,
2260 sup_id,
2261 sup_emt_id,
2262 subro_id,
2263 sub_id,
2264 sub_emt_id,
2265 sup_level,
2266 sub_level,
2267 sub_rollup_id,
2268 sub_leaf_flag_id,
2269 sub_leaf_flag,
2270 relationship_type,
2271 status_id,
2272 worker_id
2273 )
2274 values (
2275 'PRG', -- structure type
2276 PRG_NODE.prg_group, -- prg group
2277 null, -- structure version id
2278 l_prj_temp_parent, -- parent project id
2279 l_prg_temp_parent, -- parent id
2280 l_prg_temp_sup_emt, -- sup emt_id
2281 PRG_NODE.proj_element_id, -- immediate child id
2282 PRG_NODE.element_version_id, -- child id
2283 l_prg_temp_sub_emt, -- sub emt_id
2284 PRG_PARENT_NODE.prg_level, -- parent level
2285 PRG_NODE.prg_level, -- child level
2286 l_prg_temp_rollup, -- child rollup id
2287 l_prg_leaf_flag_id, -- child leaf flag id
2288 l_prg_leaf_flag, -- child leaf flag
2289 PRG_PARENT_NODE.relationship_type, -- relationship type (new)
2290 'parent', -- status id
2291 P_WORKER_ID -- worker id
2292 );
2293
2294
2295 -- --------------------------------------------------------
2296 -- Check for PRG node's children --
2297 -- Filter nodes to see if the node has children
2298
2299 FOR PRG_CHILDREN_NODE IN
2300 (
2301 select
2302 distinct
2303 pdt_child.sup_id,
2304 pdt_child.sub_id,
2305 pdt_child.sub_leaf_flag_id
2306 from PJI_FP_AGGR_XBS pdt_child
2307 where 1=1
2308 and pdt_child.sup_id = PRG_NODE.element_version_id
2309 and pdt_child.sup_id <> pdt_child.sub_id
2310 and pdt_child.worker_id = P_WORKER_ID
2311 ) LOOP
2312
2313 -- l_prg_temp_level --
2314 select pdt_child1.sub_level
2315 into l_prg_temp_level
2316 from PJI_FP_AGGR_XBS pdt_child1
2317 where 1=1
2318 and pdt_child1.sup_id = PRG_CHILDREN_NODE.sub_id
2319 and pdt_child1.sup_id = pdt_child1.sub_id
2320 and pdt_child1.worker_id = P_WORKER_ID;
2321
2322 -- l_prj_temp_parent --
2323 select pvt_child1.project_id
2324 into l_prj_temp_parent
2325 from PA_PROJ_ELEMENT_VERSIONS pvt_child1
2326 where 1=1
2327 and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
2328
2329 -- l_prg_temp_sup_emt --
2330 select pvt_child2.proj_element_id
2331 into l_prg_temp_sup_emt
2332 from PA_PROJ_ELEMENT_VERSIONS pvt_child2
2333 where 1=1
2334 and pvt_child2.element_version_id = l_prg_temp_parent;
2335
2336 -- l_prg_temp_sub_emt --
2337 select pvt_child3.proj_element_id
2338 into l_prg_temp_sub_emt
2339 from PA_PROJ_ELEMENT_VERSIONS pvt_child3
2340 where 1=1
2341 and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
2342
2343 -- l_prg_leaf_flag --
2344 if (
2345 l_prg_temp_parent = PRG_CHILDREN_NODE.sub_id
2346 or
2347 PRG_CHILDREN_NODE.sub_leaf_flag_id = 1
2348 )
2349 then
2350 l_prg_leaf_flag := 'Y';
2351 else
2352 l_prg_leaf_flag := 'N';
2353 end if;
2354
2355
2356 if g_pa_debug_mode = 'Y'
2357 then
2358 PJI_UTILS.WRITE2LOG(
2359 'PJI_PJP - Inserting PRG node child -'
2360 || ' sup_id = '
2361 || l_prg_temp_parent
2362 || ' sub_emt_id = '
2363 || l_prg_temp_sub_emt
2364 || ' sub_level = '
2365 || l_prg_temp_level,
2366 -- || PRG_CHILDREN_NODE.sup_id,
2367 null,
2368 g_msg_level_low_detail
2369 );
2370 end if;
2371
2372 -- Insert PRG node's child --
2373 insert
2374 into PJI_FP_AGGR_XBS
2375 (
2376 struct_type,
2377 prg_group,
2378 struct_version_id,
2379 sup_project_id,
2380 sup_id,
2381 sup_emt_id,
2382 subro_id,
2383 sub_id,
2384 sub_emt_id,
2385 sup_level,
2386 sub_level,
2387 sub_rollup_id,
2388 sub_leaf_flag_id,
2389 sub_leaf_flag,
2390 status_id,
2391 worker_id
2392 )
2393 values (
2394 'PRG', -- structure type
2395 PRG_NODE.prg_group, -- prg group
2396 null, -- struct_version_id
2397 l_prj_temp_parent, -- parent project id
2398 l_prg_temp_parent, -- parent id
2399 l_prg_temp_sup_emt, -- sup emt_id
2400 PRG_NODE.proj_element_id, -- immediate child id
2401 PRG_CHILDREN_NODE.sub_id, -- child id
2402 l_prg_temp_sub_emt, -- sub emt_id
2403 PRG_PARENT_NODE.prg_level, -- parent level
2404 l_prg_temp_level, -- child level
2405 null, -- child rollup id
2406 PRG_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
2407 l_prg_leaf_flag, -- child leaf flag
2408 'children', -- status id
2409 P_WORKER_ID -- worker id
2410 );
2411
2412 END LOOP; -- FOR PRG_CHILD_NODE
2413
2414 END LOOP; -- FOR PRG_PARENT_NODE
2415
2416 END IF; -- if l_prg_level_id <> 1
2417
2418 -- Call wbs_denorm
2419
2420 -- -----------------------------------------------------
2421 -- Do not call wbs_denorm procedures for WBS nodes
2422 -- that don't exist or call more than once.
2423
2424 select count(*)
2425 into l_prg_element_version_count
2426 from PA_PROJ_ELEMENT_VERSIONS cv
2427 where cv.parent_structure_version_id = PRG_NODE.element_version_id
2428 and rownum = 1;
2429
2430 if (
2431 1=1 -- P_EXTRACTION_TYPE = 'FULL'
2432 and
2433 l_prg_element_version_count > 0
2434 )
2435 then
2436
2437 if g_pa_debug_mode = 'Y'
2438 then
2439 PJI_UTILS.WRITE2LOG(
2440 'PJI_PJP - Calling wbs_denorm - element_version_id = '
2441 || PRG_NODE.element_version_id,
2442 null,
2443 g_msg_level_low_detail
2444 );
2445 end if;
2446
2447
2448 wbs_denorm(
2449 P_WORKER_ID,
2450 'FULL', -- P_EXTRACTION_TYPE,
2451 PRG_NODE.element_version_id
2452 -- PRG_NODE.proj_element_id
2453 );
2454
2455 else
2456
2457 if g_pa_debug_mode = 'Y'
2458 then
2459 PJI_UTILS.WRITE2LOG(
2460 'PJI_PJP - Not calling wbs_denorm, because WBS node does not exist - element_version_id = '
2461 || PRG_NODE.element_version_id,
2462 null,
2463 g_msg_level_low_detail
2464 );
2465 end if;
2466
2467 end if;
2468
2469 END IF; -- prg group is null
2470
2471 END LOOP; -- FOR PRG_NODE
2472
2473 if (p_extraction_type = 'FULL' or p_extraction_type = 'UPGRADE') then
2474
2475 update PJI_PJP_PROJ_BATCH_MAP
2476 set PJI_PROJECT_STATUS = null
2477 where WORKER_ID = p_worker_id and
2478 PJI_PROJECT_STATUS = 'C';
2479
2480 end if;
2481
2482 -- ------------------------------------------
2483 if g_pa_debug_mode = 'Y'
2484 then
2485 PJI_UTILS.WRITE2LOG(
2486 'PJI_PJP - End: prg_denorm',
2487 null,
2488 g_msg_level_proc_call
2489 );
2490 end if;
2491 -- ------------------------------------------
2492
2493
2494 end prg_denorm;
2495
2496 -- -----------------------------------------------------------------------
2497 -- -----------------------------------------------------------------------
2498
2499
2500
2501
2502 -- -----------------------------------------------------------------------
2503
2504 procedure wbs_denorm(
2505 p_worker_id in number,
2506 p_extraction_type in varchar2,
2507 p_wbs_version_id in number
2508 -- P_PRG_SUP_EMT_ID IN NUMBER, -- ###xbs###
2509 ) as
2510
2511 -- -----------------------------------------------------------------------
2512 --
2513 -- History
2514 -- 19-MAR-2004 aartola Created
2515 --
2516 --
2517 -- *** This API assumes that the following tables exist and that they are
2518 -- properly populated (no cycles, correct relationships, etc)
2519 --
2520 -- PA_PROJ_ELEMENT_VERSIONS
2521 -- PA_OBJECT_RELATIONSHIPS
2522 --
2523 -- Then, this API populates output values in the following existing
2524 -- table:
2525 -- PJI_FP_AGGR_XBS
2526 --
2527 -- -----------------------------------------------------------------------
2528
2529 -- -----------------------------------------------------
2530 -- Declare statements --
2531
2532 l_wbs_count number;
2533 l_wbs_temp_parent number;
2534 l_wbs_temp_level number;
2535 l_wbs_node_count number;
2536 l_wbs_leaf_flag_id number;
2537 l_wbs_leaf_flag varchar2(1);
2538
2539 l_wbs_temp_sup_emt number;
2540 l_wbs_temp_sub_emt number;
2541
2542 l_wbs_test_node number;
2543
2544 l_struct_emt_id number;
2545
2546 l_sharing_code varchar2(80); -- ###financial###
2547 l_project_id number;
2548
2549 -- -----------------------------------------------------
2550
2551 begin
2552
2553 -- (WBS node = task)
2554
2555 if g_pa_debug_mode = 'Y'
2556 then
2557 PJI_UTILS.WRITE2LOG(
2558 'PJI_PJP - Begin: wbs_denorm -'
2559 || ' p_worker_id = '
2560 || P_WORKER_ID
2561 || ' p_extraction_type = '
2562 || P_EXTRACTION_TYPE
2563 || ' p_wbs_version_id = '
2564 || P_WBS_VERSION_ID,
2565 null,
2566 g_msg_level_proc_call
2567 );
2568 end if;
2569
2570 -- -----------------------------------------------------
2571 -- l_struct_emt_id -- -- ###sup_emt###
2572 select
2573 distinct
2574 sup_emt_id
2575 into l_struct_emt_id
2576 from pji_fp_aggr_xbs
2577 where 1=1
2578 and struct_type = 'PRG'
2579 and sup_id = P_WBS_VERSION_ID
2580 and worker_id = P_WORKER_ID;
2581
2582
2583 -- -----------------------------------------------------
2584 -- l_sharing_code -- ###financial###
2585
2586 begin
2587 select projects.structure_sharing_code,projects.project_id
2588 into l_sharing_code,l_project_id
2589 from pa_projects_all projects,
2590 pa_proj_element_versions versions
2591 where 1=1
2592 and projects.project_id = versions.project_id
2593 and versions.object_type = 'PA_STRUCTURES'
2594 and versions.element_version_id = P_WBS_VERSION_ID;
2595 exception
2596 when no_data_found
2597 then
2598 l_sharing_code := 'PJI$NULL';
2599 l_project_id := -1;
2600 end;
2601
2602 if l_sharing_code is null
2603 then
2604 l_sharing_code := 'PJI$NULL';
2605 end if;
2606
2607
2608 -- -----------------------------------------------------
2609 -- Get all WBS nodes from a certain level and from a certain project --
2610
2611 -- Look only at the data to be processed
2612 -- 1) FULL - All data
2613 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs
2614 -- table
2615
2616 FOR WBS_NODE IN
2617 (
2618 select wvt_nodes.wbs_level,
2619 wvt_nodes.project_id,
2620 wvt_nodes.proj_element_id,
2621 wvt_nodes.element_version_id,
2622 wvt_nodes.parent_structure_version_id,
2623 wvt_nodes.financial_task_flag -- ###financial###
2624 from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
2625 where 1=1
2626 and (
2627 P_EXTRACTION_TYPE = 'FULL'
2628 or
2629 P_EXTRACTION_TYPE = 'UPGRADE'
2630 )
2631 and wvt_nodes.object_type = 'PA_TASKS'
2632 and exists -- ###dummy###
2633 (
2634 select 1
2635 from pa_proj_elements ele
2636 where link_task_flag = 'N'
2637 and ele.project_id = l_project_id
2638 and ele.proj_element_id = wvt_nodes.proj_element_id
2639 and rownum <= 1
2640 )
2641 and wvt_nodes.parent_structure_version_id = P_WBS_VERSION_ID
2642 and wvt_nodes.wbs_level is not null
2643 ORDER BY wbs_level DESC
2644 ) LOOP
2645
2646
2647 -- -----------------------------------------------------
2648 -- Check WBS node self --
2649
2650 -- Determine if the node to be inserted is a leaf
2651 -- If the node to be inserted has not been inserted before,
2652 -- then we know that the node is a leaf
2653
2654 select count(*)
2655 into l_wbs_node_count
2656 from PJI_FP_AGGR_XBS wdt_count
2657 where wdt_count.sup_id = WBS_NODE.element_version_id
2658 and wdt_count.worker_id = P_WORKER_ID
2659 and rownum = 1;
2660
2661 -- l_wbs_leaf_flag_id --
2662 if l_wbs_node_count > 0
2663 then
2664 l_wbs_leaf_flag_id := 0;
2665 else
2666 l_wbs_leaf_flag_id := 1;
2667 end if;
2668
2669 -- l_wbs_leaf_flag -- (business rule)
2670 if (
2671 WBS_NODE.proj_element_id = WBS_NODE.proj_element_id
2672 or
2673 l_wbs_leaf_flag_id = 1
2674 )
2675 then
2676 l_wbs_leaf_flag := 'Y';
2677 else
2678 l_wbs_leaf_flag := 'N';
2679 end if;
2680
2681
2682 if g_pa_debug_mode = 'Y'
2683 then
2684 PJI_UTILS.WRITE2LOG(
2685 'PJI_PJP - Inserting WBS node self - element_version_id = '
2686 || WBS_NODE.element_version_id,
2687 null,
2688 g_msg_level_low_detail
2689 );
2690 end if;
2691
2692 -- Insert WBS node self --
2693 insert
2694 into PJI_FP_AGGR_XBS
2695 (
2696 struct_type,
2697 prg_group,
2698 struct_emt_id,
2699 struct_version_id,
2700 sup_project_id,
2701 sup_id,
2702 sup_emt_id,
2703 subro_id,
2704 sub_id,
2705 sub_emt_id,
2706 sup_level,
2707 sub_level,
2708 sub_rollup_id,
2709 sub_leaf_flag_id,
2710 sub_leaf_flag,
2711 relationship_type,
2712 status_id,
2713 worker_id
2714 )
2715 values (
2716 'WBS', -- structure type
2717 null, -- prg group
2718 l_struct_emt_id, -- structure element id
2719 P_WBS_VERSION_ID, -- structure version id
2720 WBS_NODE.project_id, -- parent project id
2721 WBS_NODE.element_version_id, -- parent id
2722 WBS_NODE.proj_element_id, -- sup emt id
2723 null, -- immediate child id
2724 WBS_NODE.element_version_id, -- child id
2725 WBS_NODE.proj_element_id, -- sub emt_id
2726 WBS_NODE.wbs_level, -- parent level
2727 WBS_NODE.wbs_level, -- child level
2728 null, -- child rollup id
2729 l_wbs_leaf_flag_id, -- child leaf flag id
2730 l_wbs_leaf_flag, -- child leaf flag
2731 decode(l_sharing_code,
2732 'SHARE_FULL', 'WF',
2733 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
2734 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2735 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2736 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
2737 -- sub financial task flag -- ###financial###
2738 'self', -- status id
2739 P_WORKER_ID -- worker id
2740 );
2741
2742 -- --------------------------------------------------------
2743 -- Check for WBS node's parent --
2744 -- Check only if the node is not a top most node (level = 1)
2745
2746 IF WBS_NODE.wbs_level <> 1
2747 THEN
2748
2749
2750 -- -----------------------------------------------------
2751 -- Filter WBS nodes to those that have one and only one parent
2752 -- if not, the node is invalid. Cases with no parents or two parents
2753 -- have appeared with corrupted data
2754 -- Removed the call (was there for M QA builds) as disscussed with sadiq
2755
2756 -- l_wbs_temp_parent --
2757 select wrt_parent.object_id_from1
2758 into l_wbs_temp_parent
2759 from PA_OBJECT_RELATIONSHIPS wrt_parent
2760 where 1=1
2761 and wrt_parent.object_id_to1 = WBS_NODE.element_version_id
2762 and wrt_parent.object_type_from = 'PA_TASKS'
2763 and wrt_parent.object_type_to = 'PA_TASKS'
2764 and wrt_parent.relationship_type = 'S';
2765
2766 -- l_wbs_temp_sup_emt --
2767 select wvt_parent1.proj_element_id
2768 into l_wbs_temp_sup_emt
2769 from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
2770 where 1=1
2771 and wvt_parent1.element_version_id = l_wbs_temp_parent;
2772
2773 -- l_wbs_temp_sub_emt --
2774 select wvt_parent2.proj_element_id
2775 into l_wbs_temp_sub_emt
2776 from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
2777 where 1=1
2778 and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
2779
2780 -- l_wbs_leaf_flag --
2781 if (
2782 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
2783 or
2784 l_wbs_leaf_flag_id = 1
2785 )
2786 then
2787 l_wbs_leaf_flag := 'Y';
2788 else
2789 l_wbs_leaf_flag := 'N';
2790 end if;
2791
2792
2793 if g_pa_debug_mode = 'Y'
2794 then
2795 PJI_UTILS.WRITE2LOG(
2796 'PJI_PJP - Inserting WBS node parent - l_wbs_temp_parent = '
2797 || l_wbs_temp_parent,
2798 null,
2799 g_msg_level_low_detail
2800 );
2801 end if;
2802
2803
2804 -- Insert WBS node's parent --
2805 insert
2806 into PJI_FP_AGGR_XBS
2807 (
2808 struct_type,
2809 prg_group,
2810 struct_emt_id,
2811 struct_version_id,
2812 sup_project_id,
2813 sup_id,
2814 sup_emt_id,
2815 subro_id,
2816 sub_id,
2817 sub_emt_id,
2818 sup_level,
2819 sub_level,
2820 sub_rollup_id,
2821 sub_leaf_flag_id,
2822 sub_leaf_flag,
2823 relationship_type,
2824 status_id,
2825 worker_id
2826 )
2827 values (
2828 'WBS', -- structure type
2829 null, -- prg group
2830 l_struct_emt_id, -- structure element id
2831 P_WBS_VERSION_ID, -- structure version id
2832 WBS_NODE.project_id, -- parent project id
2833 l_wbs_temp_parent, -- parent id
2834 l_wbs_temp_sup_emt, -- sup_emt_id
2835 WBS_NODE.proj_element_id, -- immediate child id
2836 WBS_NODE.element_version_id, -- child id
2837 l_wbs_temp_sub_emt, -- sub_emt_id
2838 WBS_NODE.wbs_level -1, -- parent level
2839 WBS_NODE.wbs_level, -- child level
2840 null, -- child rollup id
2841 l_wbs_leaf_flag_id, -- child leaf flag id
2842 l_wbs_leaf_flag, -- child leaf flag
2843 decode(l_sharing_code,
2844 'SHARE_FULL', 'WF',
2845 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
2846 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2847 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
2848 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
2849 -- sub financial task flag -- ###financial###
2850 'parent', -- status id
2851 P_WORKER_ID -- worker id
2852 );
2853
2854 -- --------------------------------------------------------
2855 -- Check for WBS node's children --
2856 -- Filter nodes to see if the node has children
2857
2858 FOR WBS_CHIlDREN_NODE IN
2859 (
2860 select wdt_child.sup_id,
2861 wdt_child.sub_id,
2862 wdt_child.sub_leaf_flag_id,
2863 wdt_child.relationship_type
2864 from PJI_FP_AGGR_XBS wdt_child
2865 where 1=1
2866 and wdt_child.sup_id = WBS_NODE.element_version_id
2867 and wdt_child.sup_id <> wdt_child.sub_id
2868 and wdt_child.worker_id = P_WORKER_ID
2869 ) LOOP
2870
2871 -- l_wbs_temp_level --
2872 select wdt_child1.sub_level
2873 into l_wbs_temp_level
2874 from PJI_FP_AGGR_XBS wdt_child1
2875 where 1=1
2876 and wdt_child1.sup_id = WBS_CHILDREN_NODE.sub_id
2877 and wdt_child1.sup_id = wdt_child1.sub_id
2878 and wdt_child1.worker_id = P_WORKER_ID;
2879
2880 -- l_wbs_temp_sup_emt --
2881 select wvt_child1.proj_element_id
2882 into l_wbs_temp_sup_emt
2883 from PA_PROJ_ELEMENT_VERSIONS wvt_child1
2884 where 1=1
2885 and wvt_child1.element_version_id = l_wbs_temp_parent;
2886
2887 -- l_wbs_temp_sub_emt --
2888 select wvt_child2.proj_element_id
2889 into l_wbs_temp_sub_emt
2890 from PA_PROJ_ELEMENT_VERSIONS wvt_child2
2891 where 1=1
2892 and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
2893
2894 -- l_wbs_leaf_flag --
2895 if (
2896 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
2897 or
2898 WBS_CHILDREN_NODE.sub_leaf_flag_id = 1
2899 )
2900 then
2901 l_wbs_leaf_flag := 'Y';
2902 else
2903 l_wbs_leaf_flag := 'N';
2904 end if;
2905
2906
2907 if g_pa_debug_mode = 'Y'
2908 then
2909 PJI_UTILS.WRITE2LOG(
2910 'PJI_PJP - Inserting WBS node child - sup_id = '
2911 || WBS_CHILDREN_NODE.sup_id,
2912 null,
2913 g_msg_level_low_detail
2914 );
2915 end if;
2916
2917 -- Insert WBS node's child --
2918 insert
2919 into PJI_FP_AGGR_XBS
2920 (
2921 struct_type,
2922 prg_group,
2923 struct_emt_id,
2924 struct_version_id,
2925 sup_project_id,
2926 sup_id,
2927 sup_emt_id,
2928 subro_id,
2929 sub_id,
2930 sub_emt_id,
2931 sup_level,
2932 sub_level,
2933 sub_rollup_id,
2934 sub_leaf_flag_id,
2935 sub_leaf_flag,
2936 relationship_type,
2937 status_id,
2938 worker_id
2939 )
2940 values (
2941 'WBS', -- structure type
2942 null, -- prg group
2943 l_struct_emt_id, -- structure element id
2944 P_WBS_VERSION_ID, -- structure version id
2945 WBS_NODE.project_id, -- parent project id
2946 l_wbs_temp_parent, -- parent id
2947 l_wbs_temp_sup_emt, -- sup emt_id
2948 WBS_NODE.proj_element_id, -- immediate child id
2949 WBS_CHILDREN_NODE.sub_id, -- child id
2950 l_wbs_temp_sub_emt, -- sub emt_id
2951 WBS_NODE.wbs_level - 1, -- parent level
2952 l_wbs_temp_level, -- child level
2953 null, -- child rollup id
2954 WBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag id
2955 l_wbs_leaf_flag, -- child leaf flag
2956 decode(l_sharing_code,
2957 'SHARE_FULL', 'WF',
2958 'SHARE_PARTIAL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'WF', 'LW'),
2959 'SPLIT_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
2960 'SPLIT_NO_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
2961 'PJI$NULL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW')),
2962 -- sub financial task flag -- ###financial###
2963 'children', -- status id
2964 P_WORKER_ID -- worker id
2965 );
2966
2967 END LOOP; -- FOR WBS_CHIlDREN_NODE
2968
2969 ELSE
2970
2971 -- l_wbs_leaf_flag -- (business rule)
2972 if (
2973 P_WBS_VERSION_ID = WBS_NODE.element_version_id
2974 or
2975 l_wbs_leaf_flag_id = 1
2976 )
2977 then
2978 l_wbs_leaf_flag := 'Y';
2979 else
2980 l_wbs_leaf_flag := 'N';
2981 end if;
2982
2983
2984 if g_pa_debug_mode = 'Y'
2985 then
2986 PJI_UTILS.WRITE2LOG(
2987 'PJI_PJP - Inserting XBS node self - sup_id = '
2988 || P_WBS_VERSION_ID,
2989 null,
2990 g_msg_level_low_detail
2991 );
2992 end if;
2993
2994 -- Insert XBS node --
2995 insert
2996 into PJI_FP_AGGR_XBS
2997 (
2998 struct_type,
2999 prg_group,
3000 struct_version_id,
3001 sup_project_id,
3002 sup_id,
3003 sup_emt_id,
3004 subro_id,
3005 sub_id,
3006 sub_emt_id,
3007 sup_level,
3008 sub_level,
3009 sub_rollup_id,
3010 sub_leaf_flag_id,
3011 sub_leaf_flag,
3012 relationship_type,
3013 status_id,
3014 worker_id
3015 )
3016 values (
3017 'XBS', -- structure type
3018 null, -- prg group
3019 P_WBS_VERSION_ID, -- structure version id
3020 WBS_NODE.project_id, -- parent project id
3021 P_WBS_VERSION_ID, -- parent id
3022 l_struct_emt_id, -- sup emt id
3023 null, -- immediate child id
3024 WBS_NODE.element_version_id, -- child id
3025 WBS_NODE.proj_element_id, -- sub emt_id
3026 0, -- parent level (l_wbs_level_id)
3027 WBS_NODE.wbs_level, -- child level
3028 null, -- child rollup id
3029 l_wbs_leaf_flag_id, -- child leaf flag id
3030 l_wbs_leaf_flag, -- child leaf flag
3031 decode(l_sharing_code,
3032 'SHARE_FULL', 'WF',
3033 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
3034 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
3035 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
3036 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
3037 -- sub financial task flag -- ###financial###
3038 'self', -- status id
3039 P_WORKER_ID -- worker id
3040 );
3041
3042
3043 END IF; -- IF l_wbs_level_id <> 1
3044
3045 END LOOP; -- FOR WBS_NODE
3046
3047
3048 -- ----------------------------------------------
3049
3050 if g_pa_debug_mode = 'Y'
3051 then
3052 PJI_UTILS.WRITE2LOG(
3053 'PJI_PJP - End: wbs_denorm',
3054 null,
3055 g_msg_level_proc_call
3056 );
3057 end if;
3058
3059 -- -----------------------------------------------
3060
3061 end wbs_denorm;
3062
3063 -- -----------------------------------------------------------------------
3064 -- -----------------------------------------------------------------------
3065
3066
3067
3068
3069
3070
3071 -- -----------------------------------------------------------------------
3072
3073 procedure prg_denorm_online(
3074 p_worker_id in number,
3075 p_extraction_type in varchar2,
3076 p_prg_group_id in number,
3077 p_wbs_version_id in number
3078 ) as
3079
3080 -- -----------------------------------------------------------------------
3081 --
3082 -- History
3083 -- 19-MAR-2004 aartola Created
3084 --
3085 --
3086 -- *** This API assumes that the following tables exist and that they are
3087 -- properly populated (no cycles, correct relationships, etc)
3088 --
3089 -- PA_PROJ_ELEMENT_VERSIONS
3090 -- PA_OBJECT_RELATIONSHIPS
3091 --
3092 -- Then, this API populates output values in the following existing
3093 -- table:
3094 -- PJI_FP_AGGR_XBS
3095 --
3096 -- When P_EXTRACTION_TYPE equals 'FULL', this API calls the following
3097 -- procedure:
3098 -- wbs_denorm
3099 --
3100 -- -----------------------------------------------------------------------
3101
3102 -- -----------------------------------------------------
3103 -- Declare statements --
3104
3105 l_prg_level_id number;
3106 l_prg_temp_parent number;
3107 l_prg_temp_level number;
3108 l_prg_node_count number;
3109 l_prg_leaf_flag_id number;
3110 l_prg_leaf_flag varchar2(1);
3111
3112 l_prj_temp_parent number;
3113 l_prg_temp_rollup number;
3114 l_prg_temp_sup_emt number;
3115 l_prg_temp_sub_emt number;
3116
3117 l_prg_element_version_count number;
3118
3119 l_prg_dummy_rollup number;
3120 l_prg_dummy_task_flag varchar2(1);
3121
3122 -- -----------------------------------------------------
3123
3124 begin
3125
3126 -- (PRG node = program)
3127
3128 if g_pa_debug_mode = 'Y'
3129 then
3130 PJI_UTILS.WRITE2LOG(
3131 'PJI_PJP - Begin: prg_denorm_online -'
3132 || ' p_worker_id = '
3133 || P_WORKER_ID
3134 || ' p_extraction_type = '
3135 || P_EXTRACTION_TYPE
3136 || ' p_prg_group_id = '
3137 || p_prg_group_id
3138 || ' p_wbs_version_id = '
3139 || p_wbs_version_id,
3140 null,
3141 g_msg_level_proc_call
3142 );
3143 end if;
3144
3145 -- -----------------------------------------------------
3146
3147 -- Get deepest PRG node level --
3148
3149 -- Look only at the data to be processed
3150
3151 if p_prg_group_id is not null
3152 then
3153
3154 select max(pvt_level.prg_level)
3155 into l_prg_level_id
3156 from PA_PROJ_ELEMENT_VERSIONS pvt_level
3157 where 1=1
3158 and pvt_level.object_type = 'PA_STRUCTURES'
3159 and pvt_level.prg_group IS NOT NULL /* 4904076 */
3160 and prg_group = p_prg_group_id;
3161
3162 else
3163
3164 l_prg_level_id := 1; -- and element_version_id = p_wbs_version_id;
3165
3166 end if;
3167
3168 -- --------------------------------------------------------
3169 -- PRG nodes with no prg_level (is null) are valid. Therefore,
3170 -- treat these nodes as level 1.
3171
3172 if l_prg_level_id is null
3173 then
3174 l_prg_level_id := 1;
3175 end if;
3176
3177 -- --------------------------------------------------------
3178
3179 LOOP
3180
3181 if g_pa_debug_mode = 'Y'
3182 then
3183 PJI_UTILS.WRITE2LOG(
3184 'PJI_PJP - PRG Inserts - l_prg_level_id = '
3185 || l_prg_level_id,
3186 null,
3187 g_msg_level_high_detail
3188 );
3189 end if;
3190
3191 -- --------------------------------------------------------
3192 -- Get all PRG nodes from a certain level --
3193
3194 -- Look only at the data to be processed
3195
3196
3197 FOR PRG_NODE IN
3198 (
3199 select
3200 distinct
3201 pvt_nodes1.project_id,
3202 pvt_nodes1.proj_element_id,
3203 pvt_nodes1.element_version_id,
3204 pvt_nodes1.parent_structure_version_id,
3205 pvt_nodes1.prg_group,
3206 pvt_nodes1.prg_level /*4625702*/
3207 from PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
3208 where 1=1
3209 and pvt_nodes1.object_type = 'PA_STRUCTURES'
3210 and pvt_nodes1.element_version_id = p_wbs_version_id
3211 ) LOOP
3212
3213
3214 IF l_prg_level_id > 1 -- ###prg_group_is_null###
3215 and
3216 PRG_NODE.prg_group is null
3217
3218 THEN
3219 if g_pa_debug_mode = 'Y'
3220 then
3221 PJI_UTILS.WRITE2LOG(
3222 'PJI_PJP - PRG Group is null Data Bug - element_version_id = '
3223 || PRG_NODE.element_version_id,
3224 null,
3225 g_msg_level_data_bug
3226 );
3227 end if;
3228 ELSE
3229
3230 -- --------------------------------------------------------
3231 -- Check program self --
3232
3233 -- Determine if the node to be inserted is a leaf
3234 -- If the node to be inserted has not been inserted before,
3235 -- then we know that the node is a leaf
3236
3237 select count(*)
3238 into l_prg_node_count
3239 from PJI_FP_AGGR_XBS_T pdt_count
3240 where 1=1
3241 and pdt_count.sup_id = PRG_NODE.element_version_id
3242 and pdt_count.worker_id = P_WORKER_ID
3243 and rownum = 1;
3244
3245 -- l_prg_leaf_flag_id --
3246 if l_prg_node_count > 0
3247 then
3248 l_prg_leaf_flag_id := 0;
3249 else
3250 l_prg_leaf_flag_id := 1;
3251 end if;
3252
3253 -- l_prg_leaf_flag -- (business rule)
3254 if (
3255 PRG_NODE.element_version_id = PRG_NODE.element_version_id
3256 or
3257 l_prg_leaf_flag_id = 1
3258 )
3259 then
3260 l_prg_leaf_flag := 'Y';
3261 else
3262 l_prg_leaf_flag := 'N';
3263 end if;
3264
3265
3266 if g_pa_debug_mode = 'Y'
3267 then
3268 PJI_UTILS.WRITE2LOG(
3269 'PJI_PJP - Inserting PRG node self - sup_id = '
3270 || PRG_NODE.element_version_id,
3271 null,
3272 g_msg_level_low_detail
3273 );
3274 end if;
3275
3276
3277 -- Insert PRG node self --
3278 insert
3279 into PJI_FP_AGGR_XBS_T
3280 (
3281 struct_type,
3282 prg_group,
3283 struct_version_id,
3284 sup_project_id,
3285 sup_id,
3286 sup_emt_id,
3287 subro_id,
3288 sub_id,
3289 sub_emt_id,
3290 sup_level,
3291 sub_level,
3292 sub_rollup_id,
3293 sub_leaf_flag_id,
3294 sub_leaf_flag,
3295 status_id,
3296 worker_id
3297 )
3298 values (
3299 'PRG', -- structure type
3300 PRG_NODE.prg_group, -- prg group
3301 null, -- structure version id
3302 PRG_NODE.project_id, -- parent project id
3303 PRG_NODE.element_version_id, -- parent id
3304 PRG_NODE.proj_element_id, -- sup emt id
3305 null, -- immediate child id
3306 PRG_NODE.element_version_id, -- child id
3307 PRG_NODE.proj_element_id, -- sub emt_id
3308 nvl(PRG_NODE.prg_level,1) , -- 4625702 l_prg_level_id, -- parent level
3309 nvl(PRG_NODE.prg_level,1) , -- 4625702 l_prg_level_id, -- child level
3310 PRG_NODE.proj_element_id, -- child rollup id
3311 l_prg_leaf_flag_id, -- child leaf flag id
3312 l_prg_leaf_flag, -- child leaf flag
3313 'self', -- status id
3314 P_WORKER_ID -- worker id
3315 );
3316
3317
3318 -- --------------------------------------------------------
3319 -- Check for PRG node's parents --
3320 -- Check only if the node is not a top most node (level = 1)
3321
3322 IF l_prg_level_id <> 1
3323
3324 THEN
3325
3326 FOR PRG_PARENT_NODE IN
3327 (
3328 select
3329 distinct
3330 prt_parent.object_id_from1,
3331 prt_parent.relationship_type,
3332 ver.prg_level
3333 from PA_OBJECT_RELATIONSHIPS prt_parent,
3334 PA_PROJ_ELEMENT_VERSIONS ver
3335 where 1=1
3336 and prt_parent.object_id_to1 = PRG_NODE.element_version_id
3337 and prt_parent.object_type_from = 'PA_TASKS'
3338 and prt_parent.object_type_to = 'PA_STRUCTURES'
3339 and (
3340 prt_parent.relationship_type = 'LF'
3341 or
3342 prt_parent.relationship_type = 'LW'
3343 )
3344 and ver.element_version_id = prt_parent.object_id_from1
3345 ) LOOP
3346
3347 -- l_prg_temp_parent --
3348 -- l_prj_temp_parent --
3349 -- l_prg_dummy_rollup --
3350 select pvt_parent1.parent_structure_version_id,
3351 pvt_parent1.project_id,
3352 pvt_parent1.proj_element_id
3353 into l_prg_temp_parent,
3354 l_prj_temp_parent,
3355 l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
3356 from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
3357 where 1=1
3358 and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3359
3360 -- l_prg_dummy_task_flag -- ###dummy###
3361 select link_task_flag
3362 into l_prg_dummy_task_flag
3363 from pa_proj_elements
3364 where 1=1
3365 and proj_element_id = l_prg_dummy_rollup;
3366
3367 -- l_prg_temp_rollup
3368 if l_prg_dummy_task_flag = 'N'
3369
3370 then
3371 l_prg_temp_rollup := l_prg_dummy_rollup;
3372
3373 else
3374 select dt_ver1.proj_element_id
3375 into l_prg_temp_rollup
3376 from pa_object_relationships dt_rel,
3377 pa_proj_element_versions dt_ver1
3378 /* commented for bug 3838523 pa_proj_element_versions dt_ver2*/
3379 where 1=1
3380 and dt_ver1.element_version_id = dt_rel.object_id_from1
3381 and dt_rel.object_type_from = 'PA_TASKS'
3382 and dt_rel.object_type_to = 'PA_TASKS'
3383 and dt_rel.object_id_to1 = PRG_PARENT_NODE.object_id_from1;
3384
3385 /* Commented for bug 3838523 and added above line
3386 and dt_rel.object_id_to1 = dt_ver2.element_version_id
3387 and dt_ver2.proj_element_id = l_prg_dummy_rollup; */
3388 end if;
3389
3390 -- l_prg_temp_sup_emt --
3391 select pvt_parent4.proj_element_id
3392 into l_prg_temp_sup_emt
3393 from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
3394 where 1=1
3395 and pvt_parent4.element_version_id = l_prg_temp_parent;
3396
3397 -- l_prg_temp_sub_emt --
3398 select pvt_parent5.proj_element_id
3399 into l_prg_temp_sub_emt
3400 from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
3401 where 1=1
3402 and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
3403
3404 -- l_prg_leaf_flag --
3405 if (
3406 l_prg_temp_parent = PRG_NODE.element_version_id
3407 or
3408 l_prg_leaf_flag_id = 1
3409 )
3410 then
3411 l_prg_leaf_flag := 'Y';
3412 else
3413 l_prg_leaf_flag := 'N';
3414 end if;
3415
3416
3417 if g_pa_debug_mode = 'Y'
3418 then
3419 PJI_UTILS.WRITE2LOG(
3420 'PJI_PJP - Inserting PRG node parent -'
3421 || ' element_version_id = '
3422 || PRG_NODE.element_version_id
3423 || ' sup_id = '
3424 || l_prg_temp_parent
3425 || ' sub_rollup_id = '
3426 || l_prg_temp_rollup,
3427 null,
3428 g_msg_level_low_detail
3429 );
3430 end if;
3431
3432
3433 -- Insert PRG node's parent --
3434 insert
3435 into PJI_FP_AGGR_XBS_T
3436 (
3437 struct_type,
3438 prg_group,
3439 struct_version_id,
3440 sup_project_id,
3441 sup_id,
3442 sup_emt_id,
3443 subro_id,
3444 sub_id,
3445 sub_emt_id,
3446 sup_level,
3447 sub_level,
3448 sub_rollup_id,
3449 sub_leaf_flag_id,
3450 sub_leaf_flag,
3451 relationship_type,
3452 status_id,
3453 worker_id
3454 )
3455 values (
3456 'PRG', -- structure type
3457 PRG_NODE.prg_group, -- prg group
3458 null, -- structure version id
3459 l_prj_temp_parent, -- parent project id
3460 l_prg_temp_parent, -- parent id
3461 l_prg_temp_sup_emt, -- sup emt_id
3462 PRG_NODE.proj_element_id, -- immediate child id
3463 PRG_NODE.element_version_id, -- child id
3464 l_prg_temp_sub_emt, -- sub emt_id
3465 PRG_PARENT_NODE.prg_level, -- parent level
3466 l_prg_level_id, -- child level
3467 l_prg_temp_rollup, -- child rollup id
3468 l_prg_leaf_flag_id, -- child leaf flag id
3469 l_prg_leaf_flag, -- child leaf flag
3470 PRG_PARENT_NODE.relationship_type, -- relationship type (new)
3471 'parent', -- status id
3472 P_WORKER_ID -- worker id
3473 );
3474
3475
3476 -- --------------------------------------------------------
3477 -- Check for PRG node's children --
3478 -- Filter nodes to see if the node has children
3479
3480 FOR PRG_CHILDREN_NODE IN
3481 (
3482 select
3483 distinct
3484 pdt_child.sup_id,
3485 pdt_child.sub_id,
3486 pdt_child.sub_leaf_flag_id
3487 from PJI_FP_AGGR_XBS_T pdt_child
3488 where 1=1
3489 and pdt_child.sup_id = PRG_NODE.element_version_id
3490 and pdt_child.sup_id <> pdt_child.sub_id
3491 and pdt_child.worker_id = P_WORKER_ID
3492 ) LOOP
3493
3494 -- l_prg_temp_level --
3495 select pdt_child1.sub_level
3496 into l_prg_temp_level
3497 from PJI_FP_AGGR_XBS_T pdt_child1
3498 where 1=1
3499 --and pdt_child1.struct_type = 'PRG'
3500 and pdt_child1.sup_id = PRG_CHILDREN_NODE.sub_id
3501 and pdt_child1.sub_id = PRG_CHILDREN_NODE.sub_id
3502 and pdt_child1.worker_id = P_WORKER_ID;
3503
3504 -- l_prj_temp_parent --
3505 select pvt_child1.project_id
3506 into l_prj_temp_parent
3507 from PA_PROJ_ELEMENT_VERSIONS pvt_child1
3508 where 1=1
3509 and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
3510
3511 -- l_prg_temp_sup_emt --
3512 select pvt_child2.proj_element_id
3513 into l_prg_temp_sup_emt
3514 from PA_PROJ_ELEMENT_VERSIONS pvt_child2
3515 where 1=1
3516 and pvt_child2.element_version_id = l_prg_temp_parent;
3517
3518 -- l_prg_temp_sub_emt --
3519 select pvt_child3.proj_element_id
3520 into l_prg_temp_sub_emt
3521 from PA_PROJ_ELEMENT_VERSIONS pvt_child3
3522 where 1=1
3523 and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
3524
3525 -- l_prg_leaf_flag --
3526 if (
3527 l_prg_temp_parent = PRG_CHILDREN_NODE.sub_id
3528 or
3529 PRG_CHILDREN_NODE.sub_leaf_flag_id = 1
3530 )
3531 then
3532 l_prg_leaf_flag := 'Y';
3533 else
3534 l_prg_leaf_flag := 'N';
3535 end if;
3536
3537
3538 if g_pa_debug_mode = 'Y'
3539 then
3540 PJI_UTILS.WRITE2LOG(
3541 'PJI_PJP - Inserting PRG node child -'
3542 || ' element_version_id = '
3543 || PRG_NODE.element_version_id
3544 || ' sup_id = '
3545 || l_prg_temp_parent
3546 || ' sub_emt_id = '
3547 || l_prg_temp_sub_emt
3548 || ' sub_level = '
3549 || l_prg_temp_level,
3550 -- || PRG_CHILDREN_NODE.sup_id,
3551 null,
3552 g_msg_level_low_detail
3553 );
3554 end if;
3555
3556 -- Insert PRG node's child --
3557 insert
3558 into PJI_FP_AGGR_XBS_T
3559 (
3560 struct_type,
3561 prg_group,
3562 struct_version_id,
3563 sup_project_id,
3564 sup_id,
3565 sup_emt_id,
3566 subro_id,
3567 sub_id,
3568 sub_emt_id,
3569 sup_level,
3570 sub_level,
3571 sub_rollup_id,
3572 sub_leaf_flag_id,
3573 sub_leaf_flag,
3574 status_id,
3575 worker_id
3576 )
3577 values (
3578 'PRG', -- structure type
3579 PRG_NODE.prg_group, -- prg group
3580 null, -- struct_version_id
3581 l_prj_temp_parent, -- parent project id
3582 l_prg_temp_parent, -- parent id
3583 l_prg_temp_sup_emt, -- sup emt id
3584 PRG_NODE.proj_element_id, -- immediate child id
3585 PRG_CHILDREN_NODE.sub_id, -- child id
3586 l_prg_temp_sub_emt, -- sub emt id
3587 PRG_PARENT_NODE.prg_level, -- parent level
3588 l_prg_temp_level, -- child level
3589 null, -- child rollup id
3590 PRG_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
3591 l_prg_leaf_flag, -- child leaf flag
3592 'children', -- status id
3593 P_WORKER_ID -- worker id
3594 );
3595
3596 END LOOP; -- FOR PRG_CHILD_NODE
3597
3598 END LOOP; -- FOR PRG_PARENT_NODE
3599
3600 END IF; -- if l_prg_level_id <> 1
3601
3602 -- Call wbs_denorm
3603
3604 -- #change: do not call wbs_denorm in online version
3605
3606 END IF; -- prg group is null
3607
3608 END LOOP; -- FOR PRG_NODE
3609
3610 -- Decrease PRG node level --
3611
3612 l_prg_level_id := l_prg_level_id - 1;
3613 exit when l_prg_level_id = 0;
3614
3615 END LOOP; -- PRG_LEVEL
3616
3617 -- -----------------------------------------
3618
3619 if g_pa_debug_mode = 'Y'
3620 then
3621 PJI_UTILS.WRITE2LOG(
3622 'PJI_PJP - End: prg_denorm_online',
3623 null,
3624 g_msg_level_proc_call
3625 );
3626 end if;
3627
3628 -- -----------------------------------------
3629
3630 end prg_denorm_online;
3631
3632 -- -----------------------------------------------------------------------
3633 -- -----------------------------------------------------------------------
3634
3635
3636
3637
3638
3639
3640 -- -----------------------------------------------------------------------
3641
3642 procedure wbs_denorm_online(
3643 p_worker_id in number,
3644 p_extraction_type in varchar2,
3645 p_wbs_version_id in number
3646 -- P_PRG_SUP_EMT_ID IN NUMBER, -- ###xbs###
3647 ) as
3648
3649 -- -----------------------------------------------------------------------
3650 --
3651 -- History
3652 -- 31-MAR-2004 aartola Created
3653 --
3654 --
3655 -- *** This API assumes that the following tables exist and that they are
3656 -- properly populated (no cycles, correct relationships, etc)
3657 --
3658 -- PA_PROJ_ELEMENT_VERSIONS
3659 -- PA_OBJECT_RELATIONSHIPS
3660 --
3661 -- Then, this API populates output values in the following existing
3662 -- table:
3663 -- PJI_FP_AGGR_XBS_T
3664 --
3665 -- -----------------------------------------------------------------------
3666
3667 -- -----------------------------------------------------
3668 -- Declare statements --
3669
3670 l_wbs_count number;
3671 l_wbs_level_id number;
3672 l_wbs_temp_parent number;
3673 l_wbs_temp_level number;
3674 l_wbs_node_count number;
3675 l_wbs_leaf_flag_id number;
3676 l_wbs_leaf_flag varchar2(1);
3677
3678 l_wbs_temp_sup_emt number;
3679 l_wbs_temp_sub_emt number;
3680
3681 l_wbs_test_node number;
3682
3683 l_struct_emt_id number;
3684 l_struct_emt_id_count number;
3685
3686 l_sharing_code varchar2(80); -- ###financial###
3687
3688 -- -----------------------------------------------------
3689
3690 begin
3691
3692 -- (WBS node = task)
3693
3694 if g_pa_debug_mode = 'Y'
3695 then
3696 PJI_UTILS.WRITE2LOG(
3697 'PJI_PJP - Begin wbs_denorm_online -'
3698 || ' p_worker_id = '
3699 || P_WORKER_ID
3700 || ' p_extraction_type = '
3701 || P_EXTRACTION_TYPE
3702 || ' p_wbs_version_id = '
3703 || P_WBS_VERSION_ID,
3704 null,
3705 g_msg_level_proc_call
3706 );
3707 end if;
3708
3709 -- -----------------------------------------------------
3710 -- get a node count
3711
3712 select count(*)
3713 into l_wbs_count
3714 from PA_PROJ_ELEMENT_VERSIONS wvt_count
3715 where 1=1
3716 and wvt_count.object_type = 'PA_TASKS'
3717 and wvt_count.proj_element_id in -- ###dummy###
3718 (
3719 select proj_element_id
3720 from pa_proj_elements
3721 where link_task_flag = 'N'
3722 )
3723 and wvt_count.parent_structure_version_id = P_WBS_VERSION_ID
3724 and rownum = 1;
3725
3726 -- -----------------------------------------------------
3727
3728 IF l_wbs_count = 0
3729
3730 THEN
3731
3732 if g_pa_debug_mode = 'Y'
3733 then
3734 PJI_UTILS.WRITE2LOG(
3735 'PJI_PJP - Wbs node as task does not exist - p_wbs_version_id = '
3736 || P_WBS_VERSION_ID,
3737 null,
3738 g_msg_level_low_detail
3739 );
3740 end if;
3741
3742 ELSE
3743
3744
3745 -- -----------------------------------------------------
3746 -- Get deepest WBS node level --
3747 -- Look only at the data to be processed
3748 -- 1) ONLINE
3749
3750 select max(wvt_level.wbs_level)
3751 into l_wbs_level_id
3752 from PA_PROJ_ELEMENT_VERSIONS wvt_level
3753 where 1=1
3754 and wvt_level.object_type = 'PA_TASKS'
3755 and wvt_level.proj_element_id in -- ###dummy###
3756 (
3757 select proj_element_id
3758 from pa_proj_elements
3759 where link_task_flag = 'N'
3760 )
3761 and wvt_level.parent_structure_version_id = P_WBS_VERSION_ID;
3762
3763
3764 -- --------------------------------------------------------
3765 -- WBS nodes with no level (is null) are INVALID.
3766
3767 if l_wbs_level_id is null -- ###level_is_null###
3768 then
3769
3770 if g_pa_debug_mode = 'Y'
3771 then
3772 PJI_UTILS.WRITE2LOG(
3773 'PJI_PJP - Level is null Data Corruption - p_wbs_version_id = '
3774 || P_WBS_VERSION_ID,
3775 null,
3776 g_msg_level_data_corruption
3777 );
3778 end if;
3779
3780 l_wbs_level_id := 1;
3781 end if;
3782
3783 -- -----------------------------------------------------
3784 -- l_struct_emt_id -- -- ###sup_emt###
3785
3786 select
3787 distinct
3788 sup_emt_id
3789 into l_struct_emt_id
3790 from pji_fp_aggr_xbs_t
3791 where 1=1
3792 and struct_type = 'PRG'
3793 and sup_id = P_WBS_VERSION_ID
3794 and worker_id = P_WORKER_ID;
3795
3796
3797 -- -----------------------------------------------------
3798 -- l_sharing_code
3799
3800 begin
3801 select structure_sharing_code
3802 into l_sharing_code
3803 from pa_projects_all projects,
3804 pa_proj_element_versions versions
3805 where 1=1
3806 and projects.project_id = versions.project_id
3807 and versions.object_type = 'PA_STRUCTURES'
3808 and versions.element_version_id = P_WBS_VERSION_ID;
3809 exception
3810 when no_data_found
3811 then
3812 l_sharing_code := 'PJI$NULL';
3813 end;
3814
3815 if l_sharing_code is null
3816 then
3817 l_sharing_code := 'PJI$NULL';
3818 end if;
3819
3820
3821 -- -----------------------------------------------------
3822
3823 LOOP
3824
3825 if g_pa_debug_mode = 'Y'
3826 then
3827 PJI_UTILS.WRITE2LOG(
3828 'PJI_PJP - WBS Inserts - l_wbs_level_id = '
3829 || l_wbs_level_id,
3830 null,
3831 g_msg_level_high_detail
3832 );
3833 end if;
3834
3835 -- -----------------------------------------------------
3836 -- Get all WBS nodes from a certain level and from a certain project --
3837 -- Look only at the data to be processed
3838 -- 1) ONLINE
3839
3840 FOR WBS_NODE IN
3841 (
3842 select wvt_nodes.project_id,
3843 wvt_nodes.proj_element_id,
3844 wvt_nodes.element_version_id,
3845 wvt_nodes.parent_structure_version_id,
3846 wvt_nodes.financial_task_flag -- ###financial###
3847 from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
3848 where 1=1
3849 and wvt_nodes.object_type = 'PA_TASKS'
3850 and wvt_nodes.proj_element_id in -- ###dummy###
3851 (
3852 select proj_element_id
3853 from pa_proj_elements
3854 where link_task_flag = 'N'
3855 )
3856 and wvt_nodes.parent_structure_version_id = P_WBS_VERSION_ID
3857 and wvt_nodes.wbs_level = l_wbs_level_id
3858 ) LOOP
3859
3860
3861 -- -----------------------------------------------------
3862 -- Check WBS node self --
3863
3864 -- Determine if the node to be inserted is a leaf
3865 -- If the node to be inserted has not been inserted before,
3866 -- then we know that the node is a leaf
3867
3868 select count(*)
3869 into l_wbs_node_count
3870 from PJI_FP_AGGR_XBS_T wdt_count
3871 where wdt_count.sup_id = WBS_NODE.element_version_id
3872 and wdt_count.worker_id = P_WORKER_ID
3873 and rownum = 1;
3874
3875 -- l_wbs_leaf_flag_id --
3876 if l_wbs_node_count > 0
3877 then
3878 l_wbs_leaf_flag_id := 0;
3879 else
3880 l_wbs_leaf_flag_id := 1;
3881 end if;
3882
3883 -- l_wbs_leaf_flag -- (business rule)
3884 if (
3885 WBS_NODE.proj_element_id = WBS_NODE.proj_element_id
3886 or
3887 l_wbs_leaf_flag_id = 1
3888 )
3889 then
3890 l_wbs_leaf_flag := 'Y';
3891 else
3892 l_wbs_leaf_flag := 'N';
3893 end if;
3894
3895
3896 if g_pa_debug_mode = 'Y'
3897 then
3898 PJI_UTILS.WRITE2LOG(
3899 'PJI_PJP - Inserting WBS node self - element_version_id = '
3900 || WBS_NODE.element_version_id,
3901 null,
3902 g_msg_level_low_detail
3903 );
3904 end if;
3905
3906
3907 -- Insert WBS node self --
3908 insert
3909 into PJI_FP_AGGR_XBS_T
3910 (
3911 struct_type,
3912 prg_group,
3913 struct_emt_id,
3914 struct_version_id,
3915 sup_project_id,
3916 sup_id,
3917 sup_emt_id,
3918 subro_id,
3919 sub_id,
3920 sub_emt_id,
3921 sup_level,
3922 sub_level,
3923 sub_rollup_id,
3924 sub_leaf_flag_id,
3925 sub_leaf_flag,
3926 relationship_type,
3927 status_id,
3928 worker_id
3929 )
3930 values (
3931 'WBS', -- structure type
3932 null, -- prg group
3933 l_struct_emt_id, -- structure element id
3934 P_WBS_VERSION_ID, -- structure version id
3935 WBS_NODE.project_id, -- parent project id
3936 WBS_NODE.element_version_id, -- parent id
3937 WBS_NODE.proj_element_id, -- sup emt_id
3938 null, -- immediate child id
3939 WBS_NODE.element_version_id, -- child id
3940 WBS_NODE.proj_element_id, -- sub emt_id
3941 l_wbs_level_id, -- parent level
3942 l_wbs_level_id, -- child level
3943 null, -- child rollup id
3944 l_wbs_leaf_flag_id, -- child leaf flag id
3945 l_wbs_leaf_flag, -- child leaf flag
3946 decode(l_sharing_code,
3947 'SHARE_FULL', 'WF',
3948 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
3949 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
3950 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
3951 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
3952 -- sub financial task flag -- ###financial###
3953 'self', -- status id
3954 P_WORKER_ID -- worker id
3955 );
3956
3957 -- --------------------------------------------------------
3958 -- Check for WBS node's parent --
3959 -- Check only if the node is not a top most node (level = 1)
3960
3961 IF l_wbs_level_id <> 1
3962 THEN
3963
3964
3965 -- -----------------------------------------------------
3966 -- Filter WBS nodes to those that have one and only one parent
3967 -- if not, the node is invalied. Cases with no parents or two parents
3968 -- have appeared with corrupted data
3969
3970 select count(*) -- ###parent_is_one###
3971 into l_wbs_test_node
3972 from PA_OBJECT_RELATIONSHIPS rel,
3973 (
3974 select element_version_id
3975 from PA_PROJ_ELEMENT_VERSIONS
3976 where 1=1
3977 and WBS_LEVEL > 1
3978 and element_version_id = WBS_NODE.element_version_id
3979 ) ver
3980 where 1=1
3981 and rel.OBJECT_TYPE_FROM = 'PA_TASKS'
3982 and rel.OBJECT_TYPE_TO = 'PA_TASKS'
3983 and rel.RELATIONSHIP_TYPE = 'S'
3984 and ver.ELEMENT_VERSION_ID = rel.OBJECT_ID_to1 (+);
3985
3986 IF l_wbs_test_node = 1
3987
3988 THEN
3989
3990 --
3991 -- Discrepancy between prg_denorm and wbs_denorm
3992 -- As opposed to PRG nodes, WBS nodes cannot have more that one parent
3993 --
3994
3995 -- l_wbs_temp_parent --
3996 select wrt_parent.object_id_from1
3997 into l_wbs_temp_parent
3998 from PA_OBJECT_RELATIONSHIPS wrt_parent
3999 where 1=1
4000 and wrt_parent.object_id_to1 = WBS_NODE.element_version_id
4001 and wrt_parent.object_type_from = 'PA_TASKS'
4002 and wrt_parent.object_type_to = 'PA_TASKS'
4003 and wrt_parent.relationship_type = 'S';
4004
4005 -- l_wbs_temp_sup_emt --
4006 select wvt_parent1.proj_element_id
4007 into l_wbs_temp_sup_emt
4008 from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
4009 where 1=1
4010 and wvt_parent1.element_version_id = l_wbs_temp_parent;
4011
4012 -- l_wbs_temp_sub_emt --
4013 select wvt_parent2.proj_element_id
4014 into l_wbs_temp_sub_emt
4015 from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
4016 where 1=1
4017 and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
4018
4019 -- l_wbs_leaf_flag --
4020 if (
4021 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
4022 or
4023 l_wbs_leaf_flag_id = 1
4024 )
4025 then
4026 l_wbs_leaf_flag := 'Y';
4027 else
4028 l_wbs_leaf_flag := 'N';
4029 end if;
4030
4031
4032 if g_pa_debug_mode = 'Y'
4033 then
4034 PJI_UTILS.WRITE2LOG(
4035 'PJI_PJP - Inserting WBS node parent - l_wbs_temp_parent = '
4036 || l_wbs_temp_parent,
4037 null,
4038 g_msg_level_low_detail
4039 );
4040 end if;
4041
4042 -- Insert WBS node's parent --
4043 insert
4044 into PJI_FP_AGGR_XBS_T
4045 (
4046 struct_type,
4047 prg_group,
4048 struct_emt_id,
4049 struct_version_id,
4050 sup_project_id,
4051 sup_id,
4052 sup_emt_id,
4053 subro_id,
4054 sub_id,
4055 sub_emt_id,
4056 sup_level,
4057 sub_level,
4058 sub_rollup_id,
4059 sub_leaf_flag_id,
4060 sub_leaf_flag,
4061 relationship_type,
4062 status_id,
4063 worker_id
4064 )
4065 values (
4066 'WBS', -- structure type
4067 null, -- prg group
4068 l_struct_emt_id, -- structure element id
4069 P_WBS_VERSION_ID, -- structure version id
4070 WBS_NODE.project_id, -- parent project id
4071 l_wbs_temp_parent, -- parent id
4072 l_wbs_temp_sup_emt, -- sup_emt_id
4073 WBS_NODE.proj_element_id, -- immediate child id
4074 WBS_NODE.element_version_id, -- child id
4075 l_wbs_temp_sub_emt, -- sub_emt_id
4076 l_wbs_level_id - 1, -- parent level
4077 l_wbs_level_id, -- child level
4078 null, -- child rollup id
4079 l_wbs_leaf_flag_id, -- child leaf flag id
4080 l_wbs_leaf_flag, -- child leaf flag
4081 decode(l_sharing_code,
4082 'SHARE_FULL', 'WF',
4083 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
4084 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4085 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4086 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
4087 -- sub financial task flag -- ###financial###
4088 'parent', -- status id
4089 P_WORKER_ID -- worker id
4090 );
4091
4092
4093 -- --------------------------------------------------------
4094 -- Check for WBS node's children --
4095 -- Filter nodes to see if the node has children
4096
4097 FOR WBS_CHILDREN_NODE IN
4098 (
4099 select wdt_child.sup_id,
4100 wdt_child.sub_id,
4101 wdt_child.sub_leaf_flag_id,
4102 wdt_child.relationship_type
4103 from PJI_FP_AGGR_XBS_T wdt_child
4104 where 1=1
4105 and wdt_child.sup_id = WBS_NODE.element_version_id
4106 and wdt_child.sup_id <> wdt_child.sub_id
4107 and wdt_child.worker_id = P_WORKER_ID
4108 ) LOOP
4109
4110 -- l_wbs_temp_level --
4111 select wdt_child1.sub_level
4112 into l_wbs_temp_level
4113 from PJI_FP_AGGR_XBS_T wdt_child1
4114 where 1=1
4115 and wdt_child1.sup_id = WBS_CHILDREN_NODE.sub_id
4116 and wdt_child1.sup_id = wdt_child1.sub_id
4117 and wdt_child1.worker_id = P_WORKER_ID;
4118
4119 -- l_wbs_temp_sup_emt --
4120 select wvt_child1.proj_element_id
4121 into l_wbs_temp_sup_emt
4122 from PA_PROJ_ELEMENT_VERSIONS wvt_child1
4123 where 1=1
4124 and wvt_child1.element_version_id = l_wbs_temp_parent;
4125
4126 -- l_wbs_temp_sub_emt --
4127 select wvt_child2.proj_element_id
4128 into l_wbs_temp_sub_emt
4129 from PA_PROJ_ELEMENT_VERSIONS wvt_child2
4130 where 1=1
4131 and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
4132
4133 -- l_wbs_leaf_flag --
4134 if (
4135 l_wbs_temp_sup_emt = l_wbs_temp_sub_emt
4136 or
4137 WBS_CHILDREN_NODE.sub_leaf_flag_id = 1
4138 )
4139 then
4140 l_wbs_leaf_flag := 'Y';
4141 else
4142 l_wbs_leaf_flag := 'N';
4143 end if;
4144
4145
4146 if g_pa_debug_mode = 'Y'
4147 then
4148 PJI_UTILS.WRITE2LOG(
4149 'PJI_PJP - Inserting WBS node child - sup_id = '
4150 || WBS_CHILDREN_NODE.sup_id,
4151 null,
4152 g_msg_level_low_detail
4153 );
4154 end if;
4155
4156
4157 -- Insert WBS node's child --
4158 insert
4159 into PJI_FP_AGGR_XBS_T
4160 (
4161 struct_type,
4162 prg_group,
4163 struct_emt_id,
4164 struct_version_id,
4165 sup_project_id,
4166 sup_id,
4167 sup_emt_id,
4168 subro_id,
4169 sub_id,
4170 sub_emt_id,
4171 sup_level,
4172 sub_level,
4173 sub_rollup_id,
4174 sub_leaf_flag_id,
4175 sub_leaf_flag,
4176 relationship_type,
4177 status_id,
4178 worker_id
4179 )
4180 values (
4181 'WBS', -- structure type
4182 null, -- prg group
4183 l_struct_emt_id, -- structure element id
4184 P_WBS_VERSION_ID, -- structure version id
4185 WBS_NODE.project_id, -- parent project id
4186 l_wbs_temp_parent, -- parent id
4187 l_wbs_temp_sup_emt, -- sup emt_id
4188 WBS_NODE.proj_element_id, -- immediate child id
4189 WBS_CHILDREN_NODE.sub_id, -- child id
4190 l_wbs_temp_sub_emt, -- sub emt_id
4191 l_wbs_level_id - 1, -- parent level
4192 l_wbs_temp_level, -- child level
4193 null, -- child rollup id
4194 WBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag id
4195 l_wbs_leaf_flag, -- child leaf flag
4196 decode(l_sharing_code,
4197 'SHARE_FULL', 'WF',
4198 'SHARE_PARTIAL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'WF', 'LW'),
4199 'SPLIT_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
4200 'SPLIT_NO_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
4201 'PJI$NULL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW')),
4202 -- sub financial task flag -- ###financial###
4203 'children', -- status id
4204 P_WORKER_ID -- worker id
4205 );
4206
4207 END LOOP; -- FOR WBS_CHILDREN_NODE
4208
4209 ELSE
4210
4211 if g_pa_debug_mode = 'Y'
4212 then
4213 PJI_UTILS.WRITE2LOG(
4214 'PJI_PJP - Parent Data Corruption - element_version_id = '
4215 || WBS_NODE.element_version_id,
4216 null,
4217 g_msg_level_data_corruption
4218 );
4219 end if;
4220
4221 END IF; -- l_wbs_test_node = 1 -- ###parent_is_one###
4222
4223 ELSE
4224
4225 select count(*)
4226 into l_struct_emt_id_count
4227 from pa_proj_element_versions
4228 where 1=1
4229 and element_version_id = P_WBS_VERSION_ID
4230 and rownum = 1;
4231
4232 if l_struct_emt_id_count <> 0
4233 then
4234
4235 -- l_struct_emt_id -- -- ###xbs###
4236 select
4237 distinct
4238 proj_element_id
4239 into l_struct_emt_id
4240 from pa_proj_element_versions
4241 where 1=1
4242 and element_version_id = P_WBS_VERSION_ID;
4243
4244
4245 -- l_wbs_leaf_flag -- (business rule)
4246 if (
4247 P_WBS_VERSION_ID = WBS_NODE.element_version_id
4248 or
4249 l_wbs_leaf_flag_id = 1
4250 )
4251 then
4252 l_wbs_leaf_flag := 'Y';
4253 else
4254 l_wbs_leaf_flag := 'N';
4255 end if;
4256
4257
4258 if g_pa_debug_mode = 'Y'
4259 then
4260 PJI_UTILS.WRITE2LOG(
4261 'PJI_PJP - Inserting XBS node self - sup_id = '
4262 || P_WBS_VERSION_ID,
4263 null,
4264 g_msg_level_low_detail
4265 );
4266 end if;
4267
4268 -- Insert XBS node --
4269 insert
4270 into PJI_FP_AGGR_XBS_T
4271 (
4272 struct_type,
4273 prg_group,
4274 struct_version_id,
4275 sup_project_id,
4276 sup_id,
4277 sup_emt_id,
4278 subro_id,
4279 sub_id,
4280 sub_emt_id,
4281 sup_level,
4282 sub_level,
4283 sub_rollup_id,
4284 sub_leaf_flag_id,
4285 sub_leaf_flag,
4286 relationship_type,
4287 status_id,
4288 worker_id
4289 )
4290 values (
4291 'XBS', -- structure type
4292 null, -- prg group
4293 P_WBS_VERSION_ID, -- structure version id
4294 WBS_NODE.project_id, -- parent project id
4295 P_WBS_VERSION_ID, -- parent id
4296 l_struct_emt_id, -- sup emt_id
4297 null, -- immediate child id
4298 WBS_NODE.element_version_id, -- child id
4299 WBS_NODE.proj_element_id, -- sub emt_id
4300 0, -- parent level
4301 l_wbs_level_id, -- child level
4302 null, -- child rollup id
4303 l_wbs_leaf_flag_id, -- child leaf flag id
4304 l_wbs_leaf_flag, -- child leaf flag
4305 decode(l_sharing_code,
4306 'SHARE_FULL', 'WF',
4307 'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
4308 'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4309 'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
4310 'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
4311 -- sub financial task flag -- ###financial###
4312 'self', -- status id
4313 P_WORKER_ID -- worker id
4314 );
4315 else
4316
4317 if g_pa_debug_mode = 'Y'
4318 then
4319 PJI_UTILS.WRITE2LOG(
4320 'PJI_PJP - DATA BUG - element_version_id = '
4321 || WBS_NODE.element_version_id
4322 || ' struct_version_id = '
4323 || P_WBS_VERSION_ID,
4324 null,
4325 g_msg_level_high_detail
4326 );
4327 end if;
4328
4329 end if;
4330
4331 END IF; -- IF l_wbs_level_id <> 1
4332
4333 END LOOP; -- FOR WBS_NODE
4334
4335 -- Decrease WBS node level --
4336 l_wbs_level_id := l_wbs_level_id - 1;
4337 exit when l_wbs_level_id = 0;
4338
4339 END LOOP; -- WBS_LEVEL
4340
4341 END IF; -- WBS COUNT
4342
4343 -- -------------------------------------------------
4344 if g_pa_debug_mode = 'Y'
4345 then
4346 PJI_UTILS.WRITE2LOG(
4347 'PJI_PJP - End: wbs_denorm_online',
4348 null,
4349 g_msg_level_proc_call
4350 );
4351 end if;
4352 -- -------------------------------------------------
4353
4354 end wbs_denorm_online;
4355
4356 -- -----------------------------------------------------------------------
4357 -- -----------------------------------------------------------------------
4358
4359
4360
4361
4362
4363
4364 -- -----------------------------------------------------------------------
4365
4366 procedure rbs_denorm(
4367 p_worker_id in number,
4368 p_extraction_type in varchar2,
4369 p_rbs_version_id in number
4370 ) as
4371
4372
4373 -- -----------------------------------------------------------------------
4374 --
4375 -- History
4376 -- 19-MAR-2004 aartola Created
4377 --
4378 --
4379 -- *** This API assumes that the following tables exist and that they are
4380 -- properly populated (no cycles, correct relationships, etc)
4381 --
4382 -- PA_RBS_ELEMENTS
4383 --
4384 -- Then, this API populates output values in the following existing
4385 -- table:
4386 -- PJI_FP_AGGR_RBS
4387 --
4388 -- -----------------------------------------------------------------------
4389
4390 -- -----------------------------------------------------
4391 -- Declare statements --
4392
4393 l_rbs_level_id number;
4394 l_rbs_temp_parent number;
4395 l_rbs_temp_level number;
4396 l_rbs_node_count number;
4397 l_rbs_leaf_flag_id number;
4398 l_rbs_leaf_flag varchar2(1);
4399
4400 -- -----------------------------------------------------
4401
4402 begin
4403
4404 -- (RBS node = resource)
4405
4406 if g_pa_debug_mode = 'Y'
4407 then
4408 PJI_UTILS.WRITE2LOG(
4409 'PJI_PJP - Begin: rbs_denorm -'
4410 || ' p_worker_id = '
4411 || P_WORKER_ID
4412 || ' p_extraction_type = '
4413 || P_EXTRACTION_TYPE
4414 || ' p_rbs_version_id = '
4415 || P_RBS_VERSION_ID,
4416 null,
4417 g_msg_level_proc_call
4418 );
4419 end if;
4420
4421 -- -----------------------------------------------------
4422 -- Get deepest RBS node level --
4423 -- Look only at the data to be processed
4424 -- 1) FULL - All data
4425 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs table
4426
4427 if P_EXTRACTION_TYPE = 'FULL'
4428 then
4429 select max(pvt_level.rbs_level)
4430 into l_rbs_level_id
4431 from PA_RBS_ELEMENTS pvt_level
4432 where 1=1
4433 and pvt_level.user_created_flag = 'N';
4434
4435 elsif (
4436 P_EXTRACTION_TYPE = 'INCREMENTAL'
4437 or
4438 P_EXTRACTION_TYPE = 'PARTIAL'
4439 or
4440 P_EXTRACTION_TYPE = 'RBS'
4441 )
4442 then
4443 select max(pvt_level.rbs_level)
4444 into l_rbs_level_id
4445 from PA_RBS_ELEMENTS pvt_level,
4446 (
4447 select distinct event_type, event_object
4448 from PJI_PA_PROJ_EVENTS_LOG
4449 where 1=1
4450 and event_type = 'PJI_RBS_CHANGE'
4451 and worker_id = P_WORKER_ID
4452 ) log
4453 where 1=1
4454 and pvt_level.user_created_flag = 'N'
4455 and pvt_level.rbs_version_id = log.event_object;
4456
4457 elsif P_EXTRACTION_TYPE = 'UPGRADE'
4458 then
4459 select max(pvt_level.rbs_level)
4460 into l_rbs_level_id
4461 from PA_RBS_ELEMENTS pvt_level
4462 where 1=1
4463 and pvt_level.user_created_flag = 'N'
4464 and pvt_level.rbs_version_id = P_RBS_VERSION_ID;
4465
4466 else
4467 if g_pa_debug_mode = 'Y'
4468 then
4469 PJI_UTILS.WRITE2LOG(
4470 'PJI_PJP - No maximum level found - p_rbs_version_id = '
4471 || P_RBS_VERSION_ID,
4472 null,
4473 g_msg_level_data_corruption
4474 );
4475 end if;
4476
4477 l_rbs_level_id := 1; -- ###level_is_null###
4478 end if;
4479
4480
4481 -- --------------------------------------------------------
4482 -- RBS nodes with no level (is null) are INVALID.
4483
4484 if l_rbs_level_id is null -- ###level_is_null###
4485 then
4486 if g_pa_debug_mode = 'Y'
4487 then
4488 PJI_UTILS.WRITE2LOG(
4489 'PJI_PJP - Level is null Data Corruption - p_rbs_version_id = '
4490 || P_RBS_VERSION_ID,
4491 null,
4492 g_msg_level_data_corruption
4493 );
4494 end if;
4495
4496 l_rbs_level_id := 1;
4497 end if;
4498
4499 -- -----------------------------------------------------
4500
4501
4502
4503 -- -----------------------------------------------------
4504
4505 LOOP
4506
4507 if g_pa_debug_mode = 'Y'
4508 then
4509 PJI_UTILS.WRITE2LOG(
4510 'PJI_PJP - RBS Inserts - l_rbs_level_id = '
4511 || l_rbs_level_id,
4512 null,
4513 g_msg_level_high_detail
4514 );
4515 end if;
4516
4517 -- Get all RBS nodes from a certain level --
4518 -- Look only at the data to be processed
4519 -- 1) FULL - All data
4520 -- 2) INCREMENTAL or PARTIAL - Filter data by looking at the logs
4521 -- table
4522 -- 2.1) RBS node's changes
4523
4524 FOR RBS_NODE IN
4525 (
4526 select
4527 distinct
4528 -- pvt_nodes.project_id,
4529 pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
4530 pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
4531 pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
4532 -- pvt_nodes.rbs_group
4533 from PA_RBS_ELEMENTS pvt_nodes1,
4534 pji_pjp_proj_batch_map map,
4535 pa_rbs_prj_assignments assignments
4536 where 1=1
4537 and P_EXTRACTION_TYPE = 'FULL'
4538 and pvt_nodes1.user_created_flag = 'N'
4539 and (
4540 pvt_nodes1.rbs_level = l_rbs_level_id
4541 or
4542 (
4543 l_rbs_level_id = 1
4544 and
4545 pvt_nodes1.rbs_level is null
4546 )
4547 )
4548 and map.project_id = assignments.project_id
4549 and assignments.rbs_version_id = pvt_nodes1.rbs_version_id
4550 UNION ALL
4551 select
4552 distinct
4553 -- pvt_nodes.project_id,
4554 pvt_nodes2.rbs_version_id, -- pvt_nodes.proj_element_id,
4555 pvt_nodes2.rbs_element_id, -- pvt_nodes.element_version_id,
4556 pvt_nodes2.parent_element_id -- pvt_nodes.parent_structure_version_id,
4557 -- pvt_nodes.rbs_group
4558 from PA_RBS_ELEMENTS pvt_nodes2,
4559 (
4560 select
4561 distinct
4562 log1.event_type, log1.event_object
4563 from PJI_PA_PROJ_EVENTS_LOG log1
4564 where 1=1
4565 and log1.event_type = 'PJI_RBS_CHANGE'
4566 and worker_id = P_WORKER_ID
4567 ) log11
4568 where 1=1
4569 and (
4570 P_EXTRACTION_TYPE = 'INCREMENTAL'
4571 or
4572 P_EXTRACTION_TYPE = 'PARTIAL'
4573 or
4574 P_EXTRACTION_TYPE = 'RBS'
4575 )
4576 and pvt_nodes2.user_created_flag = 'N'
4577 and pvt_nodes2.rbs_version_id = log11.event_object
4578 and pvt_nodes2.rbs_level = l_rbs_level_id
4579 UNION ALL
4580 select
4581 distinct
4582 -- pvt_nodes.project_id,
4583 pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
4584 pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
4585 pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
4586 -- pvt_nodes.rbs_group
4587 from PA_RBS_ELEMENTS pvt_nodes1
4588 where 1=1
4589 and P_EXTRACTION_TYPE = 'UPGRADE'
4590 and pvt_nodes1.user_created_flag = 'N'
4591 and (
4592 pvt_nodes1.rbs_level = l_rbs_level_id
4593 or
4594 (
4595 l_rbs_level_id = 1
4596 and
4597 pvt_nodes1.rbs_level is null
4598 )
4599 )
4600 and pvt_nodes1.rbs_version_id = P_RBS_VERSION_ID
4601 ) LOOP
4602
4603
4604 -- -----------------------------------------------------
4605 -- Check RBS node self --
4606
4607 -- Determine if the node to be inserted is a leaf
4608 -- If the node to be inserted has not been inserted before,
4609 -- then we know that the node is a leaf
4610
4611 select count(*)
4612 into l_rbs_node_count
4613 from PJI_FP_AGGR_RBS pdt_count
4614 where 1=1
4615 and pdt_count.sup_id = RBS_NODE.rbs_element_id
4616 and pdt_count.worker_id = P_WORKER_ID
4617 and rownum = 1;
4618
4619 -- l_rbs_leaf_flag_id --
4620 if l_rbs_node_count > 0
4621 then
4622 l_rbs_leaf_flag_id := 0;
4623 else
4624 l_rbs_leaf_flag_id := 1;
4625 end if;
4626
4627 -- l_rbs_leaf_flag -- (business rule)
4628 if (
4629 RBS_NODE.rbs_version_id = RBS_NODE.rbs_version_id
4630 or
4631 l_rbs_leaf_flag_id = 1
4632 )
4633 then
4634 l_rbs_leaf_flag := 'Y';
4635 else
4636 l_rbs_leaf_flag := 'N';
4637 end if;
4638
4639
4640 if g_pa_debug_mode = 'Y'
4641 then
4642 PJI_UTILS.WRITE2LOG(
4643 'PJI_PJP - Inserting RBS node self - rbs_element_id = '
4644 || RBS_NODE.rbs_element_id,
4645 null,
4646 g_msg_level_low_detail
4647 );
4648 end if;
4649
4650 -- Insert RBS node self --
4651 insert
4652 into PJI_FP_AGGR_RBS
4653 (
4654 struct_version_id,
4655 sup_id,
4656 subro_id,
4657 sub_id,
4658 sup_level,
4659 sub_level,
4660 sub_leaf_flag_id,
4661 sub_leaf_flag,
4662 status_id,
4663 worker_id
4664 )
4665 values (
4666 RBS_NODE.rbs_version_id, -- rbs version id
4667 RBS_NODE.rbs_element_id, -- parent id
4668 null, -- immediate child id
4669 RBS_NODE.rbs_element_id, -- child id
4670 l_rbs_level_id, -- parent level
4671 l_rbs_level_id, -- child level
4672 l_rbs_leaf_flag_id, -- child leaf flag id
4673 l_rbs_leaf_flag, -- child leaf flag
4674 'self', -- status id
4675 P_WORKER_ID -- worker id
4676 );
4677
4678 -- --------------------------------------------------------
4679 -- Check for RBS node's parent --
4680 -- Check only if the node is not a top most node (level = 1)
4681
4682 IF l_rbs_level_id <> 1
4683 THEN
4684
4685 FOR RBS_PARENT_NODE IN
4686 (
4687 select
4688 distinct
4689 prt_parent.parent_element_id
4690 from PA_RBS_ELEMENTS prt_parent
4691 where 1=1
4692 and prt_parent.user_created_flag = 'N'
4693 and prt_parent.rbs_element_id = RBS_NODE.rbs_element_id -- prt_parent.child_id
4694 ) LOOP
4695
4696
4697 -- l_rbs_temp_parent --
4698 l_rbs_temp_parent := RBS_PARENT_NODE.parent_element_id;
4699
4700
4701 -- Filter data corruption ###parent_is_null###
4702
4703 IF l_rbs_temp_parent is not null
4704 THEN
4705
4706
4707 -- l_rbs_leaf_flag --
4708 if (
4709 l_rbs_temp_parent = RBS_NODE.rbs_element_id
4710 or
4711 l_rbs_leaf_flag_id = 1
4712 )
4713 then
4714 l_rbs_leaf_flag := 'Y';
4715 else
4716 l_rbs_leaf_flag := 'N';
4717 end if;
4718
4719 if g_pa_debug_mode = 'Y'
4720 then
4721 PJI_UTILS.WRITE2LOG(
4722 'PJI_PJP - Inserting RBS node parent - l_rbs_temp_parent - = '
4723 || l_rbs_temp_parent,
4724 null,
4725 g_msg_level_low_detail
4726 );
4727 end if;
4728
4729 -- Insert RBS node's parent --
4730 insert
4731 into PJI_FP_AGGR_RBS
4732 (
4733 struct_version_id,
4734 sup_id,
4735 subro_id,
4736 sub_id,
4737 sup_level,
4738 sub_level,
4739 sub_leaf_flag_id,
4740 sub_leaf_flag,
4741 status_id,
4742 worker_id
4743 )
4744 values (
4745 RBS_NODE.rbs_version_id, -- rbs version id
4746 l_rbs_temp_parent, -- parent id
4747 RBS_NODE.rbs_element_id, -- immediate child id
4748 RBS_NODE.rbs_element_id, -- child id
4749 l_rbs_level_id - 1, -- parent level
4750 l_rbs_level_id, -- child level
4751 l_rbs_leaf_flag_id, -- child leaf flag id
4752 l_rbs_leaf_flag, -- child leaf flag
4753 'parent', -- status id
4754 P_WORKER_ID -- worker id
4755 );
4756
4757
4758 -- --------------------------------------------------------
4759 -- Check for RBS node's children --
4760 -- Filter nodes to see if the node has children
4761
4762 FOR RBS_CHILDREN_NODE IN
4763 (
4764 select
4765 distinct
4766 pdt_child.sup_id,
4767 pdt_child.sub_id,
4768 pdt_child.sub_leaf_flag_id
4769 from PJI_FP_AGGR_RBS pdt_child
4770 where 1=1
4771 and pdt_child.sup_id = RBS_NODE.rbs_element_id
4772 and pdt_child.sup_id <> pdt_child.sub_id
4773 and pdt_child.worker_id = P_WORKER_ID
4774 ) LOOP
4775
4776 -- l_rbs_temp_level --
4777 select pdt_child1.sub_level
4778 into l_rbs_temp_level
4779 from PJI_FP_AGGR_RBS pdt_child1
4780 where 1=1
4781 and pdt_child1.sup_id = RBS_CHILDREN_NODE.sub_id
4782 and pdt_child1.sup_id = pdt_child1.sub_id
4783 and pdt_child1.worker_id = P_WORKER_ID;
4784
4785 -- l_rbs_leaf_flag --
4786 if (
4787 l_rbs_temp_parent = RBS_CHILDREN_NODE.sub_id
4788 or
4789 RBS_CHILDREN_NODE.sub_leaf_flag_id = 1
4790 )
4791 then
4792 l_rbs_leaf_flag := 'Y';
4793 else
4794 l_rbs_leaf_flag := 'N';
4795 end if;
4796
4797
4798 if g_pa_debug_mode = 'Y'
4799 then
4800 PJI_UTILS.WRITE2LOG(
4801 'PJI_PJP - Inserting RBS node child - sup_id = '
4802 || RBS_CHILDREN_NODE.sup_id,
4803 null,
4804 g_msg_level_low_detail
4805 );
4806 end if;
4807
4808 -- Insert RBS node's child --
4809 insert
4810 into PJI_FP_AGGR_RBS
4811 (
4812 struct_version_id,
4813 sup_id,
4814 subro_id,
4815 sub_id,
4816 sup_level,
4817 sub_level,
4818 sub_leaf_flag_id,
4819 sub_leaf_flag,
4820 status_id,
4821 worker_id
4822 )
4823 values (
4824 RBS_NODE.rbs_version_id, -- rbs version id
4825 l_rbs_temp_parent, -- parent id
4826 RBS_NODE.rbs_element_id, -- immediate child id
4827 RBS_CHILDREN_NODE.sub_id, -- child id
4828 l_rbs_level_id - 1, -- parent level
4829 l_rbs_temp_level, -- child level
4830 RBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
4831 l_rbs_leaf_flag, -- child leaf flag
4832 'children', -- status id
4833 P_WORKER_ID -- worker id
4834 );
4835
4836 END LOOP; -- FOR RBS_CHILD_NODE
4837
4838 ELSE
4839
4840 if g_pa_debug_mode = 'Y'
4841 then
4842 PJI_UTILS.WRITE2LOG(
4843 'PJI_PJP - Parent Data Corruption - rbs_element_id = '
4844 || RBS_NODE.rbs_element_id,
4845 null,
4846 g_msg_level_data_corruption
4847 );
4848 end if;
4849
4850 END IF; -- ###parent_is_null###
4851
4852 END LOOP; -- FOR RBS_PARENT_NODE
4853
4854 END IF; -- if RBS_LEVEL <> 1
4855
4856 END LOOP; -- FOR RBS_NODE
4857
4858 -- Decrease rbs level --
4859 l_rbs_level_id := l_rbs_level_id - 1;
4860 exit when l_rbs_level_id = 0;
4861
4862 END LOOP; -- RBS_LEVEL
4863
4864 -- -----------------------------------------
4865 if g_pa_debug_mode = 'Y'
4866 then
4867 PJI_UTILS.WRITE2LOG(
4868 'PJI_PJP - End: rbs_denorm',
4869 null,
4870 g_msg_level_proc_call
4871 );
4872 end if;
4873 -- -----------------------------------------
4874
4875 end rbs_denorm;
4876
4877 -- -----------------------------------------------------------------------
4878 -- -----------------------------------------------------------------------
4879
4880
4881
4882
4883
4884
4885
4886 -- -----------------------------------------------------------------------
4887
4888 procedure rbs_denorm_online(
4889 p_worker_id in number,
4890 p_extraction_type in varchar2,
4891 p_rbs_version_id in number
4892 ) as
4893
4894
4895 -- -----------------------------------------------------------------------
4896 --
4897 -- History
4898 -- 19-MAR-2004 aartola Created
4899 --
4900 --
4901 -- *** This API assumes that the following tables exist and that they are
4902 -- properly populated (no cycles, correct relationships, etc)
4903 --
4904 -- PA_RBS_ELEMENTS
4905 --
4906 -- Then, this API populates output values in the following existing
4907 -- table:
4908 -- PJI_FP_AGGR_RBS_T
4909 --
4910 -- -----------------------------------------------------------------------
4911
4912 -- -----------------------------------------------------
4913 -- Declare statements --
4914
4915 l_rbs_level_id number;
4916 l_rbs_temp_parent number;
4917 l_rbs_temp_level number;
4918 l_rbs_node_count number;
4919 l_rbs_leaf_flag_id number;
4920 l_rbs_leaf_flag varchar2(1);
4921
4922 -- -----------------------------------------------------
4923
4924 begin
4925
4926 -- (RBS node = resource)
4927
4928 if g_pa_debug_mode = 'Y'
4929 then
4930 PJI_UTILS.WRITE2LOG(
4931 'PJI_PJP - Begin: rbs_denorm_online -'
4932 || ' p_worker_id = '
4933 || P_WORKER_ID
4934 || ' p_extraction_type = '
4935 || P_EXTRACTION_TYPE
4936 || ' p_rbs_version_id = '
4937 || P_RBS_VERSION_ID,
4938 null,
4939 g_msg_level_proc_call
4940 );
4941 end if;
4942
4943 -- -----------------------------------------------------
4944
4945 -- Get deepest RBS node level --
4946
4947 select max(pvt_level.rbs_level)
4948 into l_rbs_level_id
4949 from PA_RBS_ELEMENTS pvt_level
4950 where 1=1
4951 and pvt_level.user_created_flag = 'N'
4952 and pvt_level.rbs_version_id = P_RBS_VERSION_ID;
4953
4954
4955 -- --------------------------------------------------------
4956 -- RBS nodes with no level (is null) are INVALID.
4957
4958 if l_rbs_level_id is null -- ###level_is_null###
4959 then
4960 if g_pa_debug_mode = 'Y'
4961 then
4962 PJI_UTILS.WRITE2LOG(
4963 'PJI_PJP - Level is null Data Corruption - p_rbs_version_id = '
4964 || P_RBS_VERSION_ID,
4965 null,
4966 g_msg_level_data_corruption
4967 );
4968 end if;
4969
4970 l_rbs_level_id := 1;
4971 end if;
4972
4973 -- -----------------------------------------------------
4974
4975
4976
4977 -- -----------------------------------------------------
4978
4979 LOOP
4980
4981 if g_pa_debug_mode = 'Y'
4982 then
4983 PJI_UTILS.WRITE2LOG(
4984 'PJI_PJP - RBS Inserts - l_rbs_level_id = '
4985 || l_rbs_level_id,
4986 null,
4987 g_msg_level_high_detail
4988 );
4989 end if;
4990
4991 -- Get all RBS nodes from a certain level --
4992
4993 FOR RBS_NODE IN
4994 (
4995 select
4996 distinct
4997 -- pvt_nodes.project_id,
4998 pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
4999 pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
5000 pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
5001 -- pvt_nodes.rbs_group
5002 from PA_RBS_ELEMENTS pvt_nodes1
5003 where 1=1
5004 and pvt_nodes1.user_created_flag = 'N'
5005 and (
5006 pvt_nodes1.rbs_level = l_rbs_level_id
5007 or
5008 (
5009 l_rbs_level_id = 1
5010 and
5011 pvt_nodes1.rbs_level is null
5012 )
5013 )
5014 and pvt_nodes1.rbs_version_id = P_RBS_VERSION_ID
5015 ) LOOP
5016
5017
5018 -- -----------------------------------------------------
5019 -- Check RBS node self --
5020
5021 -- Determine if the node to be inserted is a leaf
5022 -- If the node to be inserted has not been inserted before,
5023 -- then we know that the node is a leaf
5024
5025 select count(*)
5026 into l_rbs_node_count
5027 from PJI_FP_AGGR_RBS_T pdt_count
5028 where 1=1
5029 and pdt_count.sup_id = RBS_NODE.rbs_element_id
5030 and pdt_count.worker_id = P_WORKER_ID
5031 and rownum = 1;
5032
5033 -- l_rbs_leaf_flag_id --
5034 if l_rbs_node_count > 0
5035 then
5036 l_rbs_leaf_flag_id := 0;
5037 else
5038 l_rbs_leaf_flag_id := 1;
5039 end if;
5040
5041 -- l_rbs_leaf_flag -- (business rule)
5042 if (
5043 RBS_NODE.rbs_version_id = RBS_NODE.rbs_version_id
5044 or
5045 l_rbs_leaf_flag_id = 1
5046 )
5047 then
5048 l_rbs_leaf_flag := 'Y';
5049 else
5050 l_rbs_leaf_flag := 'N';
5051 end if;
5052
5053
5054 if g_pa_debug_mode = 'Y'
5055 then
5056 PJI_UTILS.WRITE2LOG(
5057 'PJI_PJP - Inserting RBS node self - rbs_element_id = '
5058 || RBS_NODE.rbs_element_id,
5059 null,
5060 g_msg_level_low_detail
5061 );
5062 end if;
5063
5064 -- Insert RBS node self --
5065 insert
5066 into PJI_FP_AGGR_RBS_T
5067 (
5068 struct_version_id,
5069 sup_id,
5070 subro_id,
5071 sub_id,
5072 sup_level,
5073 sub_level,
5074 sub_leaf_flag_id,
5075 sub_leaf_flag,
5076 status_id,
5077 worker_id
5078 )
5079 values (
5080 RBS_NODE.rbs_version_id, -- rbs version id
5081 RBS_NODE.rbs_element_id, -- parent id
5082 null, -- immediate child id
5083 RBS_NODE.rbs_element_id, -- child id
5084 l_rbs_level_id, -- parent level
5085 l_rbs_level_id, -- child level
5086 l_rbs_leaf_flag_id, -- child leaf flag id
5087 l_rbs_leaf_flag, -- child leaf flag
5088 'self', -- status id
5089 P_WORKER_ID -- worker id
5090 );
5091
5092 -- --------------------------------------------------------
5093 -- Check for RBS node's parent --
5094 -- Check only if the node is not a top most node (level = 1)
5095
5096 IF l_rbs_level_id <> 1
5097 THEN
5098
5099 FOR RBS_PARENT_NODE IN
5100 (
5101 select
5102 distinct
5103 prt_parent.parent_element_id
5104 from PA_RBS_ELEMENTS prt_parent
5105 where 1=1
5106 and prt_parent.user_created_flag = 'N'
5107 and prt_parent.rbs_element_id = RBS_NODE.rbs_element_id -- prt_parent.child_id
5108 ) LOOP
5109
5110
5111 -- l_rbs_temp_parent --
5112 l_rbs_temp_parent := RBS_PARENT_NODE.parent_element_id;
5113
5114
5115 -- Filter data corruption ###parent_is_null###
5116
5117 IF l_rbs_temp_parent is not null
5118 THEN
5119
5120
5121 -- l_rbs_leaf_flag --
5122 if (
5123 l_rbs_temp_parent = RBS_NODE.rbs_element_id
5124 or
5125 l_rbs_leaf_flag_id = 1
5126 )
5127 then
5128 l_rbs_leaf_flag := 'Y';
5129 else
5130 l_rbs_leaf_flag := 'N';
5131 end if;
5132
5133 if g_pa_debug_mode = 'Y'
5134 then
5135 PJI_UTILS.WRITE2LOG(
5136 'PJI_PJP - Inserting RBS node parent - l_rbs_temp_parent - = '
5137 || l_rbs_temp_parent,
5138 null,
5139 g_msg_level_low_detail
5140 );
5141 end if;
5142
5143 -- Insert RBS node's parent --
5144 insert
5145 into PJI_FP_AGGR_RBS_T
5146 (
5147 struct_version_id,
5148 sup_id,
5149 subro_id,
5150 sub_id,
5151 sup_level,
5152 sub_level,
5153 sub_leaf_flag_id,
5154 sub_leaf_flag,
5155 status_id,
5156 worker_id
5157 )
5158 values (
5159 RBS_NODE.rbs_version_id, -- rbs version id
5160 l_rbs_temp_parent, -- parent id
5161 RBS_NODE.rbs_element_id, -- immediate child id
5162 RBS_NODE.rbs_element_id, -- child id
5163 l_rbs_level_id - 1, -- parent level
5164 l_rbs_level_id, -- child level
5165 l_rbs_leaf_flag_id, -- child leaf flag id
5166 l_rbs_leaf_flag, -- child leaf flag
5167 'parent', -- status id
5168 P_WORKER_ID -- worker id
5169 );
5170
5171
5172 -- --------------------------------------------------------
5173 -- Check for RBS node's children --
5174 -- Filter nodes to see if the node has children
5175
5176 FOR RBS_CHILDREN_NODE IN
5177 (
5178 select
5179 distinct
5180 pdt_child.sup_id,
5181 pdt_child.sub_id,
5182 pdt_child.sub_leaf_flag_id
5183 from PJI_FP_AGGR_RBS_T pdt_child
5184 where 1=1
5185 and pdt_child.sup_id = RBS_NODE.rbs_element_id
5186 and pdt_child.sup_id <> pdt_child.sub_id
5187 and pdt_child.worker_id = P_WORKER_ID
5188 ) LOOP
5189
5190 -- l_rbs_temp_level --
5191 select pdt_child1.sub_level
5192 into l_rbs_temp_level
5193 from PJI_FP_AGGR_RBS_T pdt_child1
5194 where 1=1
5195 and pdt_child1.sup_id = RBS_CHILDREN_NODE.sub_id
5196 and pdt_child1.sup_id = pdt_child1.sub_id
5197 and pdt_child1.worker_id = P_WORKER_ID;
5198
5199 -- l_rbs_leaf_flag --
5200 if (
5201 l_rbs_temp_parent = RBS_CHILDREN_NODE.sub_id
5202 or
5203 RBS_CHILDREN_NODE.sub_leaf_flag_id = 1
5204 )
5205 then
5206 l_rbs_leaf_flag := 'Y';
5207 else
5208 l_rbs_leaf_flag := 'N';
5209 end if;
5210
5211
5212 if g_pa_debug_mode = 'Y'
5213 then
5214 PJI_UTILS.WRITE2LOG(
5215 'PJI_PJP - Inserting RBS node child - sup_id = '
5216 || RBS_CHILDREN_NODE.sup_id,
5217 null,
5218 g_msg_level_low_detail
5219 );
5220 end if;
5221
5222 -- Insert RBS node's child --
5223 insert
5224 into PJI_FP_AGGR_RBS_T
5225 (
5226 struct_version_id,
5227 sup_id,
5228 subro_id,
5229 sub_id,
5230 sup_level,
5231 sub_level,
5232 sub_leaf_flag_id,
5233 sub_leaf_flag,
5234 status_id,
5235 worker_id
5236 )
5237 values (
5238 RBS_NODE.rbs_version_id, -- rbs version id
5239 l_rbs_temp_parent, -- parent id
5240 RBS_NODE.rbs_element_id, -- immediate child id
5241 RBS_CHILDREN_NODE.sub_id, -- child id
5242 l_rbs_level_id - 1, -- parent level
5243 l_rbs_temp_level, -- child level
5244 RBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
5245 l_rbs_leaf_flag, -- child leaf flag
5246 'children', -- status id
5247 P_WORKER_ID -- worker id
5248 );
5249
5250 END LOOP; -- FOR RBS_CHILD_NODE
5251
5252 ELSE
5253
5254 if g_pa_debug_mode = 'Y'
5255 then
5256 PJI_UTILS.WRITE2LOG(
5257 'PJI_PJP - Parent Data Corruption - rbs_element_id = '
5258 || RBS_NODE.rbs_element_id,
5259 null,
5260 g_msg_level_data_corruption
5261 );
5262 end if;
5263
5264 END IF; -- ###parent_is_null###
5265
5266 END LOOP; -- FOR RBS_PARENT_NODE
5267
5268 END IF; -- if RBS_LEVEL <> 1
5269
5270 END LOOP; -- FOR RBS_NODE
5271
5272 -- Decrease rbs level --
5273 l_rbs_level_id := l_rbs_level_id - 1;
5274 exit when l_rbs_level_id = 0;
5275
5276 END LOOP; -- RBS_LEVEL
5277
5278 -- -------------------------------------------------
5279 if g_pa_debug_mode = 'Y'
5280 then
5281 PJI_UTILS.WRITE2LOG(
5282 'PJI_PJP - End: rbs_denorm_online',
5283 null,
5284 g_msg_level_proc_call
5285 );
5286 end if;
5287 -- -------------------------------------------------
5288
5289 end rbs_denorm_online;
5290
5291 -- -----------------------------------------------------------------------
5292 -- -----------------------------------------------------------------------
5293
5294
5295
5296
5297
5298
5299 -- -----------------------------------------------------------------------
5300
5301 procedure merge_xbs_denorm(
5302 p_worker_id in number,
5303 p_extraction_type in varchar2
5304 ) as
5305
5306 -- -----------------------------------------------------------------------
5307 --
5308 -- History
5309 -- 19-MAR-2004 aartola Created
5310 --
5311 --
5312 -- *** This procedure merges data from the denorm interim table
5313 -- (PJI_FP_AGGR_XBS) to the actual denorm table (PA_XBS_DENORM)
5314 --
5315 -- After calling this procedure, the contents of the interim table
5316 -- need to be deleted.
5317 --
5318 -- -----------------------------------------------------------------------
5319
5320 -- -----------------------------------------------------------------------
5321 -- Declare statements --
5322
5323 l_last_update_date date;
5324 l_last_updated_by number;
5325 l_creation_date date;
5326 l_created_by number;
5327 l_last_update_login number;
5328
5329 -- -----------------------------------------------------
5330
5331 begin
5332
5333 if g_pa_debug_mode = 'Y'
5334 then
5335 PJI_UTILS.WRITE2LOG(
5336 'PJI_PJP - Begin: merge_xbs_denorm -'
5337 || ' p_worker_id = '
5338 || P_WORKER_ID
5339 || ' p_extraction_type = '
5340 || P_EXTRACTION_TYPE,
5341 null,
5342 g_msg_level_proc_call
5343 );
5344 end if;
5345
5346 -- -----------------------------------------------------
5347 -- Variable Assignments --
5348
5349 l_last_update_date := sysdate;
5350 l_last_updated_by := FND_GLOBAL.USER_ID;
5351 l_creation_date := sysdate;
5352 l_created_by := FND_GLOBAL.USER_ID;
5353 l_last_update_login := FND_GLOBAL.LOGIN_ID;
5354
5355 -- -----------------------------------------------------
5356
5357 if p_extraction_type = 'FULL'
5358
5359 then
5360
5361 insert
5362 into PA_XBS_DENORM
5363 (
5364 struct_type,
5365 prg_group,
5366 struct_emt_id,
5367 struct_version_id,
5368 sup_project_id,
5369 sup_id,
5370 sup_emt_id,
5371 subro_id,
5372 sub_id,
5373 sub_emt_id,
5374 sup_level,
5375 sub_level,
5376 sub_rollup_id,
5377 sub_leaf_flag,
5378 relationship_type,
5379 LAST_UPDATE_DATE,
5380 LAST_UPDATED_BY,
5381 CREATION_DATE,
5382 CREATED_BY,
5383 LAST_UPDATE_LOGIN
5384 )
5385 select
5386 distinct
5387 interim.struct_type,
5388 interim.prg_group,
5389 interim.struct_emt_id,
5390 interim.struct_version_id,
5391 interim.sup_project_id,
5392 interim.sup_id,
5393 interim.sup_emt_id,
5394 interim.subro_id,
5395 interim.sub_id,
5396 interim.sub_emt_id,
5397 interim.sup_level,
5398 interim.sub_level,
5399 interim.sub_rollup_id,
5400 interim.sub_leaf_flag,
5401 interim.relationship_type,
5402 l_last_update_date,
5403 l_last_updated_by,
5404 l_creation_date,
5405 l_created_by,
5406 l_last_update_login
5407 from PJI_FP_AGGR_XBS interim,
5408 PA_XBS_DENORM denorm
5409 where 1=1
5410 and interim.worker_id = p_worker_id
5411 and nvl(interim.struct_type, -1) = nvl(denorm.struct_type (+), -1)
5412 and nvl(interim.prg_group, -1) = nvl(denorm.prg_group (+), -1)
5413 and nvl(interim.struct_emt_id, -1) = nvl(denorm.struct_emt_id (+), -1)
5414 and nvl(interim.struct_version_id, -1) = nvl(denorm.struct_version_id (+), -1)
5415 and nvl(interim.sup_project_id, -1) = nvl(denorm.sup_project_id (+), -1)
5416 and nvl(interim.sup_id, -1) = nvl(denorm.sup_id (+), -1)
5417 and nvl(interim.sup_emt_id, -1) = nvl(denorm.sup_emt_id (+), -1)
5418 and nvl(interim.subro_id, -1) = nvl(denorm.subro_id (+), -1)
5419 and nvl(interim.sub_id, -1) = nvl(denorm.sub_id (+), -1)
5420 and nvl(interim.sub_emt_id, -1) = nvl(denorm.sub_emt_id (+), -1)
5421 and nvl(interim.sup_level, -1) = nvl(denorm.sup_level (+), -1)
5422 and nvl(interim.sub_level, -1) = nvl(denorm.sub_level (+), -1)
5423 and nvl(interim.sub_rollup_id, -1) = nvl(denorm.sub_rollup_id (+), -1)
5424 and nvl(interim.sub_leaf_flag, -1) = nvl(denorm.sub_leaf_flag (+), -1)
5425 and nvl(interim.relationship_type, -1) = nvl(denorm.relationship_type (+), -1)
5426 and denorm.struct_type is null
5427 order by
5428 interim.struct_version_id,
5429 interim.sup_id,
5430 interim.sub_id;
5431
5432 -- -------------------------
5433
5434 elsif p_extraction_type = 'ONLINE'
5435
5436 then
5437
5438 insert
5439 into PA_XBS_DENORM
5440 (
5441 struct_type,
5442 prg_group,
5443 struct_emt_id,
5444 struct_version_id,
5445 sup_project_id,
5446 sup_id,
5447 sup_emt_id,
5448 subro_id,
5449 sub_id,
5450 sub_emt_id,
5451 sup_level,
5452 sub_level,
5453 sub_rollup_id,
5454 sub_leaf_flag,
5455 relationship_type,
5456 LAST_UPDATE_DATE,
5457 LAST_UPDATED_BY,
5458 CREATION_DATE,
5459 CREATED_BY,
5460 LAST_UPDATE_LOGIN
5461 )
5462 select
5463 distinct
5464 interim.struct_type,
5465 interim.prg_group,
5466 interim.struct_emt_id,
5467 interim.struct_version_id,
5468 interim.sup_project_id,
5469 interim.sup_id,
5470 interim.sup_emt_id,
5471 interim.subro_id,
5472 interim.sub_id,
5473 interim.sub_emt_id,
5474 interim.sup_level,
5475 interim.sub_level,
5476 interim.sub_rollup_id,
5477 interim.sub_leaf_flag,
5478 interim.relationship_type,
5479 l_last_update_date,
5480 l_last_updated_by,
5481 l_creation_date,
5482 l_created_by,
5483 l_last_update_login
5484 from PJI_FP_AGGR_XBS_T interim
5485 where interim.worker_id = p_worker_id
5486 order by
5487 interim.struct_version_id,
5488 interim.sup_id,
5489 interim.sub_id;
5490
5491 -- -------------------------
5492
5493 else -- UPGRADE, PARTIAL, INCREMENTAL
5494
5495 insert
5496 into PA_XBS_DENORM
5497 (
5498 struct_type,
5499 prg_group,
5500 struct_emt_id,
5501 struct_version_id,
5502 sup_project_id,
5503 sup_id,
5504 sup_emt_id,
5505 subro_id,
5506 sub_id,
5507 sub_emt_id,
5508 sup_level,
5509 sub_level,
5510 sub_rollup_id,
5511 sub_leaf_flag,
5512 relationship_type,
5513 LAST_UPDATE_DATE,
5514 LAST_UPDATED_BY,
5515 CREATION_DATE,
5516 CREATED_BY,
5517 LAST_UPDATE_LOGIN
5518 )
5519 select
5520 distinct
5521 interim.struct_type,
5522 interim.prg_group,
5523 interim.struct_emt_id,
5524 interim.struct_version_id,
5525 interim.sup_project_id,
5526 interim.sup_id,
5527 interim.sup_emt_id,
5528 interim.subro_id,
5529 interim.sub_id,
5530 interim.sub_emt_id,
5531 interim.sup_level,
5532 interim.sub_level,
5533 interim.sub_rollup_id,
5534 interim.sub_leaf_flag,
5535 interim.relationship_type,
5536 l_last_update_date,
5537 l_last_updated_by,
5538 l_creation_date,
5539 l_created_by,
5540 l_last_update_login
5541 from PJI_FP_AGGR_XBS interim
5542 where interim.worker_id = p_worker_id
5543 order by
5544 interim.struct_version_id,
5545 interim.sup_id,
5546 interim.sub_id;
5547
5548 -- -------------------------
5549
5550 end if;
5551
5552 -- -----------------------------------------------------
5553 if g_pa_debug_mode = 'Y'
5554 then
5555 PJI_UTILS.WRITE2LOG(
5556 'PJI_PJP - End: merge_xbs_denorm',
5557 null,
5558 g_msg_level_proc_call
5559 );
5560 end if;
5561 -- -----------------------------------------------------
5562
5563 end merge_xbs_denorm;
5564
5565 -- -----------------------------------------------------------------------
5566 -- -----------------------------------------------------------------------
5567
5568
5569
5570
5571
5572
5573 -- -----------------------------------------------------------------------
5574
5575 procedure merge_rbs_denorm(
5576 p_worker_id in number,
5577 p_extraction_type in varchar2
5578 ) as
5579
5580 -- -----------------------------------------------------------------------
5581 --
5582 -- History
5583 -- 19-MAR-2004 aartola Created
5584 --
5585 --
5586 -- *** This procedure merges data from the denorm interim table
5587 -- (PJI_FP_AGGR_RBS) to the actual denorm table (PA_RBS_DENORM)
5588 --
5589 -- After calling this procedure, the contents of the interim table
5590 -- need to be deleted.
5591 --
5592 -- -----------------------------------------------------------------------
5593
5594 -- -----------------------------------------------------
5595 -- Declare statements --
5596
5597 l_last_update_date date;
5598 l_last_updated_by number;
5599 l_creation_date date;
5600 l_created_by number;
5601 l_last_update_login number;
5602
5603 -- -----------------------------------------------------
5604
5605 begin
5606
5607 if g_pa_debug_mode = 'Y'
5608 then
5609 PJI_UTILS.WRITE2LOG(
5610 'PJI_PJP - Begin: merge_rbs_denorm -'
5611 || ' p_worker_id = '
5612 || P_WORKER_ID
5613 || ' p_extraction_type = '
5614 || P_EXTRACTION_TYPE,
5615 null,
5616 g_msg_level_proc_call
5617 );
5618 end if;
5619
5620 -- -----------------------------------------------------
5621 -- Variable assignments --
5622
5623 l_last_update_date := sysdate;
5624 l_last_updated_by := FND_GLOBAL.USER_ID;
5625 l_creation_date := sysdate;
5626 l_created_by := FND_GLOBAL.USER_ID;
5627 l_last_update_login := FND_GLOBAL.LOGIN_ID;
5628
5629 -- -----------------------------------------------------
5630
5631 if p_extraction_type = 'FULL'
5632
5633 then
5634
5635 insert
5636 into PA_RBS_DENORM
5637 (
5638 struct_version_id,
5639 sup_id,
5640 subro_id,
5641 sub_id,
5642 sup_level,
5643 sub_level,
5644 sub_leaf_flag,
5645 LAST_UPDATE_DATE,
5646 LAST_UPDATED_BY,
5647 CREATION_DATE,
5648 CREATED_BY,
5649 LAST_UPDATE_LOGIN
5650 )
5651 select
5652 interim.struct_version_id,
5653 interim.sup_id,
5654 interim.subro_id,
5655 interim.sub_id,
5656 interim.sup_level,
5657 interim.sub_level,
5658 interim.sub_leaf_flag,
5659 l_last_update_date,
5660 l_last_updated_by,
5661 l_creation_date,
5662 l_created_by,
5663 l_last_update_login
5664 from PJI_FP_AGGR_RBS interim,
5665 PA_RBS_DENORM denorm
5666 where 1=1
5667 and interim.worker_id = p_worker_id
5668 and nvl(interim.struct_version_id, -1) = nvl(denorm.struct_version_id (+), -1)
5669 and nvl(interim.sup_id, -1) = nvl(denorm.sup_id (+), -1)
5670 and nvl(interim.subro_id, -1) = nvl(denorm.subro_id (+), -1)
5671 and nvl(interim.sub_id, -1) = nvl(denorm.sub_id (+), -1)
5672 and nvl(interim.sup_level, -1) = nvl(denorm.sup_level (+), -1)
5673 and nvl(interim.sub_level, -1) = nvl(denorm.sub_level (+), -1)
5674 and nvl(interim.sub_leaf_flag, -1) = nvl(denorm.sub_leaf_flag (+), -1)
5675 and denorm.struct_version_id is null;
5676
5677 -- -------------------------
5678
5679 elsif p_extraction_type = 'ONLINE'
5680
5681 then
5682
5683 insert
5684 into PA_RBS_DENORM
5685 (
5686 struct_version_id,
5687 sup_id,
5688 subro_id,
5689 sub_id,
5690 sup_level,
5691 sub_level,
5692 sub_leaf_flag,
5693 LAST_UPDATE_DATE,
5694 LAST_UPDATED_BY,
5695 CREATION_DATE,
5696 CREATED_BY,
5697 LAST_UPDATE_LOGIN
5698 )
5699 select
5700 interim.struct_version_id,
5701 interim.sup_id,
5702 interim.subro_id,
5703 interim.sub_id,
5704 interim.sup_level,
5705 interim.sub_level,
5706 interim.sub_leaf_flag,
5707 l_last_update_date,
5708 l_last_updated_by,
5709 l_creation_date,
5710 l_created_by,
5711 l_last_update_login
5712 from PJI_FP_AGGR_RBS_T interim
5713 where interim.worker_id = p_worker_id;
5714
5715 -- -------------------------
5716
5717 else -- UPGRADE, PARTIAL, INCREMENTAL
5718
5719 insert
5720 into PA_RBS_DENORM
5721 (
5722 struct_version_id,
5723 sup_id,
5724 subro_id,
5725 sub_id,
5726 sup_level,
5727 sub_level,
5728 sub_leaf_flag,
5729 LAST_UPDATE_DATE,
5730 LAST_UPDATED_BY,
5731 CREATION_DATE,
5732 CREATED_BY,
5733 LAST_UPDATE_LOGIN
5734 )
5735 select
5736 interim.struct_version_id,
5737 interim.sup_id,
5738 interim.subro_id,
5739 interim.sub_id,
5740 interim.sup_level,
5741 interim.sub_level,
5742 interim.sub_leaf_flag,
5743 l_last_update_date,
5744 l_last_updated_by,
5745 l_creation_date,
5746 l_created_by,
5747 l_last_update_login
5748 from PJI_FP_AGGR_RBS interim
5749 where interim.worker_id = p_worker_id;
5750
5751 -- -------------------------
5752
5753 end if;
5754
5755 -- -----------------------------------------------------
5756 if g_pa_debug_mode = 'Y'
5757 then
5758 PJI_UTILS.WRITE2LOG(
5759 'PJI_PJP - End: merge_rbs_denorm',
5760 null,
5761 g_msg_level_proc_call
5762 );
5763 end if;
5764 -- -----------------------------------------------------
5765
5766 end merge_rbs_denorm;
5767
5768 -- -----------------------------------------------------------------------
5769 -- -----------------------------------------------------------------------
5770
5771
5772
5773
5774 -- -----------------------------------------------------------------------
5775
5776 procedure cleanup_xbs_denorm(
5777 p_worker_id in number,
5778 p_extraction_type in varchar2
5779 ) as
5780
5781 -- -----------------------------------------------------
5782
5783 begin
5784
5785 if g_pa_debug_mode = 'Y'
5786 then
5787 PJI_UTILS.WRITE2LOG(
5788 'PJI_PJP - Begin: cleanup_xbs_denorm -'
5789 || ' p_worker_id = '
5790 || P_WORKER_ID
5791 || ' p_extraction_type = '
5792 || P_EXTRACTION_TYPE,
5793 null,
5794 g_msg_level_proc_call
5795 );
5796 end if;
5797
5798 -- -----------------------------------------------------
5799
5800
5801 if p_extraction_type = 'ONLINE'
5802
5803 then
5804 delete
5805 from PJI_FP_AGGR_XBS_T
5806 where worker_id = p_worker_id;
5807
5808 else -- FULL, INCREMENTAL, PARTIAL, UPGRADE
5809
5810 delete
5811 from PJI_FP_AGGR_XBS
5812 where worker_id = p_worker_id;
5813
5814 end if;
5815
5816 -- -----------------------------------------------------
5817 if g_pa_debug_mode = 'Y'
5818 then
5819 PJI_UTILS.WRITE2LOG(
5820 'PJI_PJP - End: cleanup_xbs_denorm',
5821 null,
5822 g_msg_level_proc_call
5823 );
5824 end if;
5825 -- -----------------------------------------------------
5826
5827 end cleanup_xbs_denorm;
5828
5829 -- -----------------------------------------------------------------------
5830 -- -----------------------------------------------------------------------
5831
5832
5833
5834
5835
5836 -- -----------------------------------------------------------------------
5837
5838 procedure cleanup_rbs_denorm(
5839 p_worker_id in number,
5840 p_extraction_type in varchar2
5841 ) as
5842
5843 -- -----------------------------------------------------
5844
5845 begin
5846
5847 if g_pa_debug_mode = 'Y'
5848 then
5849 PJI_UTILS.WRITE2LOG(
5850 'PJI_PJP - Begin: cleanup_rbs_denorm -'
5851 || ' p_worker_id = '
5852 || P_WORKER_ID
5853 || ' p_extraction_type = '
5854 || P_EXTRACTION_TYPE,
5855 null,
5856 g_msg_level_proc_call
5857 );
5858 end if;
5859
5860 -- -----------------------------------------------------
5861
5862
5863 if p_extraction_type = 'ONLINE'
5864
5865 then
5866 delete
5867 from PJI_FP_AGGR_RBS_T
5868 where worker_id = p_worker_id;
5869
5870 else -- FULL, INCREMENTAL, PARTIAL, UPGRADE
5871
5872 delete
5873 from PJI_FP_AGGR_RBS
5874 where worker_id = p_worker_id;
5875 end if;
5876
5877 -- -----------------------------------------------------
5878 if g_pa_debug_mode = 'Y'
5879 then
5880 PJI_UTILS.WRITE2LOG(
5881 'PJI_PJP - End: cleanup_rbs_denorm',
5882 null,
5883 g_msg_level_proc_call
5884 );
5885 end if;
5886 -- -----------------------------------------------------
5887
5888 end cleanup_rbs_denorm;
5889
5890 -- -----------------------------------------------------------------------
5891 -- -----------------------------------------------------------------------
5892
5893
5894
5895
5896
5897 -- -----------------------------------------------------------------------
5898
5899 begin
5900
5901 -- -----------------------------------------------------
5902
5903 -- Declare global variables
5904
5905 g_pa_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
5906 g_msg_level_data_bug := 6;
5907 g_msg_level_data_corruption := 5;
5908 g_msg_level_proc_call := 3;
5909 g_msg_level_high_detail := 2;
5910 g_msg_level_low_detail := 1;
5911
5912 -- -----------------------------------------------------
5913
5914
5915 end PJI_PJP_SUM_DENORM;