[Home] [Help]
PACKAGE BODY: APPS.PJI_PJP_SUM_MAIN
Source
1 package body PJI_PJP_SUM_MAIN as
2 /* $Header: PJISP01B.pls 120.38.12010000.2 2008/12/11 09:30:29 vgovvala ship $ */
3
4
5 -- -----------------------------------------------------
6 -- function WORKER_STATUS
7 --
8 -- History
9 -- 19-MAR-2004 SVERMETT Created
10 --
11 -- Internal PJP Summarization API.
12 --
13 -- -----------------------------------------------------
14 function WORKER_STATUS (p_worker_id in number,
15 p_mode in varchar2) return boolean is
16
17 l_process varchar2(30);
18 l_request_id number;
19
20 begin
21
22 l_process := g_process || p_worker_id;
23
24 l_request_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
25 l_process);
26
27 if (PJI_PROCESS_UTIL.REQUEST_STATUS(p_mode,
28 l_request_id,
29 g_full_disp_name) or
30 PJI_PROCESS_UTIL.REQUEST_STATUS(p_mode,
31 l_request_id,
32 g_incr_disp_name) or
33 PJI_PROCESS_UTIL.REQUEST_STATUS(p_mode,
34 l_request_id,
35 g_prtl_disp_name) or
36 PJI_PROCESS_UTIL.REQUEST_STATUS(p_mode,
37 l_request_id,
38 g_rbs_disp_name)) then
39
40 return true;
41
42 else
43
44 return false;
45
46 end if;
47
48 end WORKER_STATUS;
49
50
51 -- -----------------------------------------------------
52 -- function MY_PAD
53 -- -----------------------------------------------------
54 function MY_PAD (p_length in number,
55 p_char in varchar2) return varchar2 is
56
57 l_stmt varchar2(2000) := '';
58
59 begin
60
61 for x in 1 .. p_length loop
62
63 l_stmt := l_stmt || p_char;
64
65 end loop;
66
67 return l_stmt;
68
69 end MY_PAD;
70
71 -- -----------------------------------------------------
72 -- Function SUBMIT_REQUEST
73 --
74 -- History
75 -- 8-Aug-2006 DEGUPTA Created
76 --
77 -- Internal PJP Summarization API.
78 --
79 -- -----------------------------------------------------
80 procedure SUBMIT_REQUEST(
81 p_worker_id number,
82 p_request_id OUT nocopy number,
83 p_prog_name OUT nocopy varchar2)
84 IS
85 pragma autonomous_transaction;
86
87 l_e_process varchar2(30);
88 l_e_extraction_type varchar2(30);
89 l_e_run_mode varchar2(30);
90 l_e_program varchar2(240);
91 l_e_project_operating_unit number;
92 l_e_project_organization_id number;
93 l_e_project_type varchar2(50);
94 l_e_from_project varchar2(50);
95 l_e_to_project varchar2(50);
96 l_e_plan_type_id number;
97 l_e_rbs_header_id number;
98 l_e_transaction_type varchar2(40);
99 l_e_plan_versions varchar2 (40);
100
101 begin
102 l_e_process := g_process || p_worker_id;
103
104 l_e_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
105 (l_e_process, 'EXTRACTION_TYPE');
106
107 If PJI_PJP_SUM_MAIN.WORKER_STATUS(p_worker_id, 'RUNNING') then
108
109 -- Means its already running so get the request id and wait to complete that request
110
111 select PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER (l_e_process,l_e_process),
112 DECODE(l_e_extraction_type,'PARTIAL',g_prtl_disp_name,'RBS',g_rbs_disp_name,NULL,NULL,g_incr_disp_name)
113 into p_request_id,p_prog_name
114 from dual;
115
116 else
117
118 -- Need to submit the existing failed worker request
119
120 l_e_project_operating_unit := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
121 (l_e_process, 'PROJECT_OPERATING_UNIT');
122
123 l_e_project_organization_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
124 (l_e_process, 'PROJECT_ORGANIZATION_ID');
125
126 l_e_project_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
127 (l_e_process, 'PROJECT_TYPE');
128
129 l_e_from_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
130 (l_e_process, 'FROM_PROJECT');
131
132 l_e_to_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
133 (l_e_process, 'TO_PROJECT');
134
135 l_e_plan_type_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
136 (l_e_process, 'PLAN_TYPE_ID');
137
138 l_e_rbs_header_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
139 (l_e_process, 'RBS_HEADER_ID');
140
141 l_e_transaction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
142 (l_e_process, 'TRANSACTION_TYPE');
143
144 l_e_plan_versions := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
145 (l_e_process, 'PLAN_VERSION');
146
147 SELECT
148 DECODE(l_e_extraction_type,'PARTIAL','P','RBS','R',NULL,NULL,'I'),
149 DECODE(l_e_extraction_type,'PARTIAL',g_prtl_disp_name,'RBS',g_rbs_disp_name,NULL,NULL,g_incr_disp_name),
150 DECODE(l_e_project_operating_unit,-1,NULL,l_e_project_operating_unit),
151 DECODE(l_e_project_organization_id,-1,NULL,l_e_project_organization_id),
152 DECODE(l_e_project_type,'PJI$NULL',NULL,l_e_project_type),
153 DECODE(l_e_from_project,'PJI$NULL',NULL,l_e_from_project),
154 DECODE(l_e_to_project,'PJI$NULL',NULL,l_e_to_project),
155 DECODE(l_e_plan_type_id,-1,NULL,l_e_plan_type_id),
156 DECODE(l_e_rbs_header_id,-1,NULL,l_e_rbs_header_id),
157 DECODE(l_e_transaction_type,'PJI$NULL',NULL,l_e_transaction_type),
158 DECODE(l_e_plan_versions,'PJI$NULL',NULL,l_e_plan_versions)
159 INTO
160 l_e_run_mode,
161 l_e_program,
162 l_e_project_operating_unit,
163 l_e_project_organization_id,
164 l_e_project_type,
165 l_e_from_project,
166 l_e_to_project,
167 l_e_plan_type_id,
168 l_e_rbs_header_id,
169 l_e_transaction_type,
170 l_e_plan_versions
171 FROM
172 DUAL;
173
174
175 IF l_e_program is not null then
176 p_request_id := FND_REQUEST.SUBMIT_REQUEST(
177 application => PJI_UTILS.GET_PJI_SCHEMA_NAME , -- Application Name
178 program => l_e_program, -- Program Name
179 sub_request => FALSE, -- Sub Request
180 argument1 => l_e_run_mode, -- p_run_mode
181 argument2 => NVL(to_char(l_e_project_operating_unit),''), -- p_operating_unit
182 argument3 => NVL(to_char(l_e_project_organization_id),''), -- p_project_organization_id
183 argument4 => l_e_project_type, -- p_project_type
184 argument5 => l_e_from_project , -- p_from_project_num
185 argument6 => l_e_to_project , -- p_to_project_num
186 argument7 => NVL(to_char(l_e_plan_type_id),'') , -- p_plan_type_id
187 argument8 => NVL(to_char(l_e_rbs_header_id),''), -- p_rbs_header_id
188 argument9 => l_e_transaction_type, -- p_transaction_type_id
189 argument10 => l_e_plan_versions);
190 pa_debug.log_message('Current Program is submitting failed related Concurrent request with the request no: '||p_request_id, 1);
191 p_prog_name := l_e_program;
192 else
193 p_request_id := -1;
194 p_prog_name := NULL;
195 end if;
196 end if;
197 Commit;
198 end;
199
200 -- -----------------------------------------------------
201 -- procedure NO_WORK_RUNS
202 --
203 -- History
204 -- 20-SEP-2005 DEGUPTA Created
205 --
206 -- Internal PJP Summarization API.
207 --
208 -- -----------------------------------------------------
209 procedure NO_WORK_RUNS (
210 p_operating_unit in number default null,
211 p_project_organization_id in number default null,
212 p_project_type in varchar2 default null,
213 p_from_project in varchar2 default null,
214 p_to_project in varchar2 default null,
215 p_extraction_type in varchar2 default null )
216 is
217
218 l_newline varchar2(10) := '
219 ';
220 l_no_selection varchar2(50);
221
222 l_project_type_tg varchar2(40);
223 l_project_organization_tg varchar2(40);
224 l_from_project_tg varchar2(40);
225 l_to_project_tg varchar2(40);
226 l_project_operating_unit_tg varchar2(40);
227
228 l_project_type varchar2(50);
229 l_project_organization varchar2(300);
230 l_from_project varchar2(50);
231 l_to_project varchar2(50);
232 l_project_operating_unit_name varchar2(240);
233
234
235 begin
236
237 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_NO_SUMM_WORK');
238
239 PJI_UTILS.WRITE2OUT(l_newline ||
240 l_newline ||
241 FND_MESSAGE.GET ||
242 l_newline);
243
244 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_NO_SELECTION');
245
246 l_no_selection := FND_MESSAGE.GET;
247
248
249 if (nvl(p_operating_unit, -1) = -1) then
250 l_project_operating_unit_name := l_no_selection;
251 else
252 select NAME
253 into l_project_operating_unit_name
254 from HR_OPERATING_UNITS
255 where ORGANIZATION_ID = p_operating_unit;
256 end if;
257
258 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PRJ_OP_UNIT');
259
260 l_project_operating_unit_tg := substr(FND_MESSAGE.GET, 1, 30);
261
262 PJI_UTILS.WRITE2OUT(l_project_operating_unit_tg ||
263 my_pad(30 - length(l_project_operating_unit_tg),
264 ' ') ||
265 ': ' ||
266 l_project_operating_unit_name ||
267 l_newline);
268
269 if (p_extraction_type in ('FULL', 'INCREMENTAL')) then
270 if (nvl(p_project_type, 'PJI$NULL') = 'PJI$NULL') then
271
272 l_project_type := l_no_selection;
273 else
274 l_project_type := p_project_type;
275 end if;
276
277 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PRJ_TYPE');
278
279 l_project_type_tg := substr(FND_MESSAGE.GET, 1, 30);
280
281 PJI_UTILS.WRITE2OUT(l_project_type_tg ||
282 my_pad(30 - length(l_project_type_tg), ' ') ||
283 ': ' ||
284 l_project_type ||
285 l_newline);
286
287
288 if (nvl(p_project_organization_id, -1) = -1) then
289
290 l_project_organization := l_no_selection;
291
292 else
293
294 select NAME
295 into l_project_organization
296 from HR_ALL_ORGANIZATION_UNITS_VL
297 where ORGANIZATION_ID = p_project_organization_id;
298
299 end if;
300
301 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PRJ_ORG');
302
303 l_project_organization_tg := substr(FND_MESSAGE.GET, 1, 30);
304
305 PJI_UTILS.WRITE2OUT(l_project_organization_tg ||
306 my_pad(30 - length(l_project_organization_tg),
307 ' ') ||
308 ': ' ||
309 l_project_organization ||
310 l_newline);
311 end if;
312
313 if (nvl(p_from_project, 'PJI$NULL') = 'PJI$NULL') then
314
315 l_from_project := l_no_selection;
316 else
317 l_from_project := p_from_project;
318 end if;
319
320 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_FROM_PRJ');
321
322 l_from_project_tg := substr(FND_MESSAGE.GET, 1, 30);
323
324 PJI_UTILS.WRITE2OUT(l_from_project_tg ||
325 my_pad(30 - length(l_from_project_tg), ' ') ||
326 ': ' ||
327 l_from_project ||
328 l_newline);
329
330
331 if (nvl(p_to_project, 'PJI$NULL') = 'PJI$NULL') then
332
333 l_to_project := l_no_selection;
334 else
335 l_to_project := p_to_project;
336 end if;
337
338 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_TO_PRJ');
339
340 l_to_project_tg := substr(FND_MESSAGE.GET, 1, 30);
341
342 PJI_UTILS.WRITE2OUT(l_to_project_tg ||
343 my_pad(30 - length(l_to_project_tg), ' ') ||
344 ': ' ||
345 l_to_project ||
346 l_newline);
347
348
349 end NO_WORK_RUNS;
350
351 -- -----------------------------------------------------
352 -- procedure OUTPUT_FAILED_RUNS
353 --
354 -- History
355 -- 19-MAR-2004 SVERMETT Created
356 --
357 -- Internal PJP Summarization API.
358 --
359 -- -----------------------------------------------------
360 procedure OUTPUT_FAILED_RUNS is
361
362 l_process varchar2(30);
363 l_extraction_type varchar2(30);
364
365 l_newline varchar2(10) := '
366 ';
367 l_no_selection varchar2(50);
368 l_header_flag varchar2(1);
369
370 l_extraction_type_tg varchar2(40);
371 l_project_type_tg varchar2(40);
372 l_project_organization_tg varchar2(40);
373 l_from_project_tg varchar2(40);
374 l_to_project_tg varchar2(40);
375 l_plan_type_tg varchar2(40);
376 l_rbs_header_tg varchar2(40);
377 l_only_pt_projects_tg varchar2(40);
378
379 l_project_organization_id number;
380 l_from_project_id number;
381 l_to_project_id number;
382 l_plan_type_id number;
383 l_rbs_header_id number;
384
385 l_project_type varchar2(50);
386 l_project_organization varchar2(300);
387 l_from_project varchar2(50);
388 l_to_project varchar2(50);
389 l_plan_type varchar2(200);
390 l_rbs_header varchar2(300);
391 l_only_pt_projects varchar2(50);
392 l_request_id number;
393 l_request_id_tg varchar2(40);
394 l_project_operating_unit number;
395 l_project_operating_unit_name varchar2(240);
396 l_project_operating_unit_tg varchar2(40);
397
398 l_transaction_type varchar2(40); -- Bug#5099574 - New parameter for Partial Refresh
399 l_plan_versions varchar2 (40);
400 l_transaction_type_tg varchar2(40);
401 l_plan_versions_tg varchar2 (40);
402 l_transaction_type_id varchar2(40);
403 l_plan_versions_id varchar2 (40);
404
405
406
407 begin
408
409 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_NO_SELECTION');
410
411 l_no_selection := FND_MESSAGE.GET;
412
413 l_header_flag := 'Y';
414
415 for x in 1 .. PJI_PJP_SUM_MAIN.g_parallel_processes loop
416
417 l_process := PJI_PJP_SUM_MAIN.g_process || x;
418
419 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
420 (l_process, 'EXTRACTION_TYPE');
421
422 if (l_extraction_type is not null and
423 not PJI_PJP_SUM_MAIN.WORKER_STATUS(x, 'RUNNING')) then
424
425 if (l_header_flag = 'Y') then
426
427 l_header_flag := 'N';
428
429 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_HEADER');
430
431 PJI_UTILS.WRITE2OUT(l_newline ||
432 l_newline ||
433 FND_MESSAGE.GET ||
434 l_newline);
435
436 end if;
437
438 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_LINE');
439
440 PJI_UTILS.WRITE2OUT(l_newline ||
441 FND_MESSAGE.GET ||
442 l_newline ||
443 l_newline);
444
445 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_EXTR_TYPE');
446
447 l_extraction_type_tg := substr(FND_MESSAGE.GET, 1, 30);
448
449 PJI_UTILS.WRITE2OUT(l_extraction_type_tg ||
450 my_pad(30 - length(l_extraction_type_tg), ' ') ||
451 ': ');
452
453 if (l_extraction_type = 'FULL') then
454
455 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_EXTR_TYPE_FULL');
456
457 elsif (l_extraction_type = 'INCREMENTAL') then
458
459 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_EXTR_TYPE_INCR');
460
461 elsif (l_extraction_type = 'PARTIAL') then
462
463 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_EXTR_TYPE_PRTL');
464
465 elsif (l_extraction_type = 'RBS') then
466
467 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_EXTR_TYPE_RBS');
468
469 end if;
470
471 PJI_UTILS.WRITE2OUT(FND_MESSAGE.GET ||
472 l_newline);
473
474 if (l_extraction_type in ('FULL', 'INCREMENTAL', 'PARTIAL')) then
475
476 l_request_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
477 (l_process, l_process);
478
479 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_REQ_ID');
480
481 l_request_id_tg := substr(FND_MESSAGE.GET, 1, 30);
482
483 PJI_UTILS.WRITE2OUT(l_request_id_tg ||
484 my_pad(30 - length(l_request_id_tg),
485 ' ') ||
486 ': ' ||
487 l_request_id ||
488 l_newline);
489
490 end if;
491
492
493 if (l_extraction_type in ('FULL', 'INCREMENTAL', 'PARTIAL')) then
494
495 l_project_operating_unit := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
496 (l_process, 'PROJECT_OPERATING_UNIT');
497
498 if (nvl(l_project_operating_unit, -1) = -1) then
499
500 l_project_operating_unit_name := l_no_selection;
501
502 else
503
504 select NAME
505 into l_project_operating_unit_name
506 from HR_OPERATING_UNITS
507 where ORGANIZATION_ID = l_project_operating_unit;
508
509 end if;
510
511 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PRJ_OP_UNIT');
512
513 l_project_operating_unit_tg := substr(FND_MESSAGE.GET, 1, 30);
514
515 PJI_UTILS.WRITE2OUT(l_project_operating_unit_tg ||
516 my_pad(30 - length(l_project_operating_unit_tg),
517 ' ') ||
518 ': ' ||
519 l_project_operating_unit_name ||
520 l_newline);
521
522 end if;
523
524 if (l_extraction_type in ('FULL', 'INCREMENTAL')) then
525
526 l_project_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
527 (l_process, 'PROJECT_TYPE');
528
529 if (nvl(l_project_type, 'PJI$NULL') = 'PJI$NULL') then
530
531 l_project_type := l_no_selection;
532
533 end if;
534
535 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PRJ_TYPE');
536
537 l_project_type_tg := substr(FND_MESSAGE.GET, 1, 30);
538
539 PJI_UTILS.WRITE2OUT(l_project_type_tg ||
540 my_pad(30 - length(l_project_type_tg), ' ') ||
541 ': ' ||
542 l_project_type ||
543 l_newline);
544
545 end if;
546
547 if (l_extraction_type in ('FULL', 'INCREMENTAL')) then
548
549 l_project_organization_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
550 (l_process, 'PROJECT_ORGANIZATION_ID');
551
552 if (nvl(l_project_organization_id, -1) = -1) then
553
554 l_project_organization := l_no_selection;
555
556 else
557
558 select NAME
559 into l_project_organization
560 from HR_ALL_ORGANIZATION_UNITS_VL
561 where ORGANIZATION_ID = l_project_organization_id;
562
563 end if;
564
565 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PRJ_ORG');
566
567 l_project_organization_tg := substr(FND_MESSAGE.GET, 1, 30);
568
569 PJI_UTILS.WRITE2OUT(l_project_organization_tg ||
570 my_pad(30 - length(l_project_organization_tg),
571 ' ') ||
572 ': ' ||
573 l_project_organization ||
574 l_newline);
575
576 end if;
577
578 if (l_extraction_type in ('FULL', 'INCREMENTAL', 'PARTIAL')) then
579
580 l_from_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
581 (l_process, 'FROM_PROJECT');
582
583 if (nvl(l_from_project, 'PJI$NULL') = 'PJI$NULL') then
584
585 l_from_project := l_no_selection;
586
587 end if;
588
589 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_FROM_PRJ');
590
591 l_from_project_tg := substr(FND_MESSAGE.GET, 1, 30);
592
593 PJI_UTILS.WRITE2OUT(l_from_project_tg ||
594 my_pad(30 - length(l_from_project_tg), ' ') ||
595 ': ' ||
596 l_from_project ||
597 l_newline);
598
599 end if;
600
601 if (l_extraction_type in ('FULL', 'INCREMENTAL', 'PARTIAL')) then
602
603 l_to_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
604 (l_process, 'TO_PROJECT');
605
606 if (nvl(l_to_project, 'PJI$NULL') = 'PJI$NULL') then
607
608 l_to_project := l_no_selection;
609
610 end if;
611
612 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_TO_PRJ');
613
614 l_to_project_tg := substr(FND_MESSAGE.GET, 1, 30);
615
616 PJI_UTILS.WRITE2OUT(l_to_project_tg ||
617 my_pad(30 - length(l_to_project_tg), ' ') ||
618 ': ' ||
619 l_to_project ||
620 l_newline);
621
622 end if;
623
624 if (l_extraction_type in ('PARTIAL')) then
625
626 l_plan_type_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
627 (l_process, 'PLAN_TYPE_ID');
628
629 if (nvl(l_plan_type_id, -1) = -1) then
630
631 l_plan_type := l_no_selection;
632
633 else
634
635 select NAME
636 into l_plan_type
637 from PA_FIN_PLAN_TYPES_VL
638 where FIN_PLAN_TYPE_ID = l_plan_type_id;
639
640 end if;
641
642 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PLAN_TYPE');
643
644 l_plan_type_tg := substr(FND_MESSAGE.GET, 1, 30);
645
646 PJI_UTILS.WRITE2OUT(l_plan_type_tg ||
647 my_pad(30 - length(l_plan_type_tg), ' ') ||
648 ': ' ||
649 l_plan_type ||
650 l_newline);
651
652 end if;
653
654 if (l_extraction_type in ('RBS')) then
655
656 l_rbs_header_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
657 (l_process, 'RBS_HEADER_ID');
658
659 if (nvl(l_rbs_header_id, -1) = -1) then
660
661 l_rbs_header := l_no_selection;
662
663 else
664
665 select NAME
666 into l_rbs_header
667 from PA_RBS_HEADERS_VL
668 where RBS_HEADER_ID = l_rbs_header_id;
669
670 end if;
671
672 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_RBS_HDR');
673
674 l_rbs_header_tg := substr(FND_MESSAGE.GET, 1, 30);
675
676 PJI_UTILS.WRITE2OUT(l_rbs_header_tg ||
677 my_pad(30 - length(l_rbs_header_tg), ' ') ||
678 ': ' ||
679 l_rbs_header ||
680 l_newline);
681
682 end if;
683
684 if (l_extraction_type in ('PARTIAL')) then -- Bug#5099574 Start
685
686 l_transaction_type_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
687 (l_process, 'TRANSACTION_TYPE');
688
689 l_plan_versions_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
690 (l_process, 'PLAN_VERSION');
691
692 if (nvl(l_transaction_type_id, 'PJI$NULL') = 'PJI$NULL') then
693
694 l_transaction_type := l_no_selection;
695
696 else
697
698 select MEANING
699 into l_transaction_type
700 from fnd_lookup_values_vl
701 where LOOKUP_TYPE = 'PJI_REF_TXN_TYPE' and
702 LOOKUP_CODE =l_transaction_type_id;
703
704 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_TXN_TYPE');
705
706 l_transaction_type_tg := substr(FND_MESSAGE.GET, 1, 30);
707
708 PJI_UTILS.WRITE2OUT(l_transaction_type_tg ||
709 my_pad(30 - length(l_transaction_type_tg), ' ') ||
710 ': ' ||
711 l_transaction_type ||
712 l_newline);
713
714
715 end if;
716
717 if (nvl(l_plan_versions_id, 'PJI$NULL') = 'PJI$NULL') then
718
719 l_plan_versions := l_no_selection;
720
721 else
722
723 select MEANING
724 into l_plan_versions
725 from fnd_lookup_values_vl
726 where LOOKUP_TYPE = 'PJI_REF_PLAN_VERSION' and
727 LOOKUP_CODE =l_plan_versions_id;
728
729 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PLAN_VER');
730
731 l_plan_versions_tg := substr(FND_MESSAGE.GET, 1, 30);
732
733 PJI_UTILS.WRITE2OUT(l_plan_versions_tg ||
734 my_pad(30 - length(l_plan_versions_tg), ' ') ||
735 ': ' ||
736 l_plan_versions ||
737 l_newline);
738
739 end if;
740
741
742 end if; -- Bug#5099574 Ends
743
744 if (l_extraction_type in ('FULL', 'INCREMENTAL') and 1 = 2) then
745
746 l_only_pt_projects := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
747 (l_process, 'ONLY_PT_PROJECTS_FLAG');
748
749 if (nvl(l_only_pt_projects, 'PJI$NULL') = 'PJI$NULL') then
750
751 l_only_pt_projects := l_no_selection;
752
753 else
754
755 select MEANING
756 into l_only_pt_projects
757 from FND_LOOKUPS
758 where LOOKUP_TYPE = 'YES_NO' and
759 LOOKUP_CODE = l_only_pt_projects;
760
761 end if;
762
763 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_ONLY_PT_PRJ');
764
765 l_only_pt_projects_tg := substr(FND_MESSAGE.GET, 1, 30);
766
767 PJI_UTILS.WRITE2OUT(l_only_pt_projects_tg ||
768 my_pad(30 - length(l_only_pt_projects_tg),
769 ' ') ||
770 ': ' ||
771 l_only_pt_projects ||
772 l_newline);
773
774 end if;
775
776 end if;
777
778 end loop;
779
780 end OUTPUT_FAILED_RUNS;
781
782
783 procedure OUTPUT_ACT_FAILED_RUNS(p_worker_id NUMBER) is
784
785 l_process varchar2(30);
786 l_extraction_type varchar2(30);
787
788 l_newline varchar2(10) := '
789 ';
790 l_report_exists varchar2(1):='N';
791 l_segment1 varchar2(25);--SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
792 l_act_err_msg varchar2(2000);--SYSTEM.pa_varchar2_2000_tbl_type := SYSTEM.pa_varchar2_2000_tbl_type();
793 l_rpt_header varchar2(60);
794 l_rpt_info varchar2(500);
795 l_proj_number varchar2(30);
796 l_rpt_excep_reason varchar2(30);
797 l_rpt_footer varchar2(60);
798 cursor c_err_report is
799 select pa.segment1,map.act_err_msg
800 from pji_pjp_proj_batch_map map,pa_projects_all pa
801 where pa.project_id=map.project_id
802 and map.worker_id=p_worker_id
803 and map.act_err_msg is not null
804 order by segment1;
805 -- and map.act_err_msg is not null;
806
807 begin
808
809
810 open c_err_report;
811 fetch c_err_report into l_segment1, l_act_err_msg;
812 IF c_err_report%FOUND THEN
813 l_report_exists :='Y';
814 g_retcode := 1;
815 END IF;
816 close c_err_report;
817 IF l_report_exists ='Y' THEN
818 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_FAILED_RPT_HEADER');
819 l_rpt_header := FND_MESSAGE.GET;
820 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_FAILED_RPT_INFO');
821 l_rpt_info := FND_MESSAGE.GET;
822 FND_MESSAGE.SET_NAME('PA', 'PA_XC_PROJECT_NUMBER');
823 l_proj_number := FND_MESSAGE.GET;
824 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_EXCEPTION_REASON');
825 l_rpt_excep_reason := FND_MESSAGE.GET;
826 /* The following projects may have incorrect actuals on workplan pages, please run summarization after rectifying the mentioned exception reason:*/
827
828 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_RPT_FOOTER');
829 l_rpt_footer := FND_MESSAGE.GET;
830 PJI_UTILS.WRITE2OUT('-----------------------------------------------------------------------------------------------------------------------------------------' ||
831 fnd_global.local_chr(10));
832
833
834 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
835 ' '||l_rpt_header);
836 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
837 '-----------------------------------------------------------------------------------------------------------------------------------------' ||
838 fnd_global.local_chr(10));
839
840 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) || l_rpt_info ||
841 fnd_global.local_chr(10));
842
843 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||l_proj_number||' '||l_rpt_excep_reason );
844 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
845 '===================== =================' );
846 for i in c_err_report LOOP
847
848 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||rpad(i.segment1,27,' ')||i.act_err_msg);
849
850 END LOOp;
851
852 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
853 '----------------------------------------------------------'||l_rpt_footer||'----------------------------------------------------------');
854 END IF;
855 end OUTPUT_ACT_FAILED_RUNS;
856
857 procedure OUTPUT_ACT_PASSED_RUNS(p_worker_id NUMBER) is
858
859 l_process varchar2(30);
860 l_extraction_type varchar2(30);
861
862 l_newline varchar2(10) := '
863 ';
864 l_report_exists varchar2(1):='N';
865 l_segment1 varchar2(25);--SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
866 l_name varchar2(50);--SYSTEM.pa_varchar2_2000_tbl_type := SYSTEM.pa_varchar2_2000_tbl_type();
867 l_rpt_header varchar2(60);
868 l_proj_number varchar2(30);
869 l_proj_name varchar2(30);
870 l_rpt_footer varchar2(60);
871
872 cursor c_err_report is
873 select pa.segment1,pa.name
874 from pji_pjp_proj_batch_map map,pa_projects_all pa
875 where pa.project_id=map.project_id
876 and map.worker_id=p_worker_id
877 and map.act_err_msg is null
878 order by segment1;
879
880 begin
881
882 open c_err_report;
883 fetch c_err_report into l_segment1, l_name;
884 IF c_err_report%FOUND THEN
885 l_report_exists :='Y';
886 END IF;
887 close c_err_report;
888
889 IF l_report_exists ='Y' THEN
890 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_PASSED_RPT_HEADER');
891 l_rpt_header := FND_MESSAGE.GET;
892 FND_MESSAGE.SET_NAME('PA', 'PA_XC_PROJECT_NUMBER');
893 l_proj_number := FND_MESSAGE.GET;
894
895 FND_MESSAGE.SET_NAME('PA', 'PA_XC_PROJECT_NAME');
896 l_proj_name := FND_MESSAGE.GET;
897 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_RPT_FOOTER');
898 l_rpt_footer := FND_MESSAGE.GET;
899
900 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
901 '-----------------------------------------------------------------------------------------------------------------------------------------' );
902
903 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
904 ' '||l_rpt_header);
905 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
906 '-----------------------------------------------------------------------------------------------------------------------------------------' );
907
908 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
909 l_Proj_Number||' '||l_Proj_Name );
910 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
911 '====================== =================' );
912
913 for i in c_err_report LOOP
914
915 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||rpad(i.segment1,27,' ')||i.name);
916
917 END LOOp;
918
919 PJI_UTILS.WRITE2OUT(fnd_global.local_chr(10) ||
920 '----------------------------------------------------------'||l_rpt_footer||'----------------------------------------------------------');
921 END IF;
922 end OUTPUT_ACT_PASSED_RUNS;
923
924 -- -----------------------------------------------------
925 -- procedure PLAN_TYPE_CODE_CHANGES
926 --
927 -- History
928 -- 4-APR-2006 DEGUPTA Created
929 --
930 -- Internal PJP Summarization API for Plan type code enhancement upgradation.
931 --
932 -- -----------------------------------------------------
933
934 Procedure PLAN_TYPE_CODE_CHANGES(p_worker_id in number) is
935 l_count number(10) := 0;
936 l_level number(10);
937 l_creation_date date := sysdate;
938 l_created_by number := FND_GLOBAL.USER_ID;
939 l_last_update_date date := SYSDATE;
940 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
941 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
942
943 cursor C_BOTTOM_UP is
944 select /*+ INDEX_FFS (den PJI_XBS_DENORM_N3) */
945 SUP_LEVEL
946 from
947 PJI_XBS_DENORM den
948 where
949 STRUCT_TYPE = 'PRG' and
950 SUB_LEVEL = SUP_LEVEL and
951 EXISTS ( SELECT 1 from PJI_FM_EXTR_PLNVER4 ver where ver.worker_id = p_worker_id
952 and ver.project_id = den.SUP_PROJECT_ID)
953 group by
954 SUP_LEVEL
955 order by
956 SUP_LEVEL desc;
957
958 BEGIN
959 -- pji_utils.write2log('In Procedure plan type code changes');
960
961 select count(bmap.project_id) into l_count
962 from PA_PJI_PROJ_EVENTS_LOG elog,
963 PJI_PJP_PROJ_BATCH_MAP bmap
964 where elog.event_object = to_char(bmap.project_id)
965 and elog.event_type = 'PLANTYPE_UPG'
966 and bmap.worker_id = p_worker_id;
967
968 -- pji_utils.write2log('plan_type_code changes exist count'||l_count);
969
970 if l_count > 0 then
971
972 DELETE PJI_FM_EXTR_PLNVER4 where worker_id = p_worker_id;
973 DELETE PJI_FP_AGGR_PJP1 where worker_id = p_worker_id;
974
975 INSERT INTO PJI_FM_EXTR_PLNVER4
976 (
977 WORKER_ID ,
978 PROJECT_ID ,
979 PLAN_VERSION_ID ,
980 WBS_STRUCT_VERSION_ID ,
981 RBS_STRUCT_VERSION_ID ,
982 PLAN_TYPE_CODE ,
983 PLAN_TYPE_ID ,
984 TIME_PHASED_TYPE_CODE ,
985 TIME_DANGLING_FLAG ,
986 RATE_DANGLING_FLAG ,
987 PROJECT_TYPE_CLASS ,
988 WP_FLAG ,
989 CURRENT_FLAG ,
990 ORIGINAL_FLAG ,
991 CURRENT_ORIGINAL_FLAG ,
992 BASELINED_FLAG ,
993 SECONDARY_RBS_FLAG ,
994 LP_FLAG
995 )
996 SELECT
997 DISTINCT
998 worker_id
999 , project_id
1000 , plan_version_id
1001 , wbs_struct_version_id
1002 , rbs_struct_version_id
1003 , plan_type_code
1004 , plan_type_id
1005 , time_phased_type_code
1006 , NULL time_dangling_flag
1007 , NULL rate_dangling_flag
1008 , NULL PROJECT_TYPE_CLASS
1009 , is_wp_flag
1010 , current_flag , original_flag
1011 , current_original_flag
1012 , baselined_flag
1013 , SECONDARY_RBS_FLAG
1014 , lp_flag
1015 FROM
1016 (
1017 SELECT p_worker_id worker_id,
1018 bv.project_id project_id
1019 , bv.budget_version_id plan_version_id
1020 , DECODE ( NVL(bv.wp_version_flag, 'N')
1021 , 'Y', bv.project_structure_version_id
1022 , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id)
1023 ) wbs_struct_version_id
1024 , fpo.rbs_version_id rbs_struct_version_id -- extract for this rbs version id
1025 , DECODE (bv.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code
1026 , fpo.fin_plan_type_id plan_type_id
1027 , DECODE(bv.version_type
1028 , 'ALL', fpo.all_time_phased_code
1029 , 'COST', fpo.cost_time_phased_code
1030 , 'REVENUE', fpo.revenue_time_phased_code
1031 ) time_phased_type_code
1032 , NVL(bv.wp_version_flag, 'N') is_wp_flag
1033 , bv.current_flag current_flag
1034 , bv.original_flag original_flag
1035 , bv.current_original_flag current_original_flag
1036 , DECODE(bv.baselined_date, NULL, 'N', 'Y') baselined_flag
1037 , 'N' SECONDARY_RBS_FLAG
1038 , DECODE( NVL(bv.wp_version_flag, 'N')
1039 , 'Y'
1040 , DECODE(bv.project_structure_version_id
1041 , PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION( bv.project_id) -- IN NUMBER
1042 -- , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id)
1043 , 'Y'
1044 , 'N')
1045 , 'N'
1046 ) lp_flag
1047 FROM
1048 pa_budget_versions bv
1049 , pa_proj_fp_options fpo
1050 , (select distinct bmap.project_id,elog.ATTRIBUTE1 plan_type_id from
1051 PA_PJI_PROJ_EVENTS_LOG elog,
1052 PJI_PJP_PROJ_BATCH_MAP bmap
1053 where elog.event_object = to_char(bmap.project_id)
1054 and elog.EVENT_TYPE = 'PLANTYPE_UPG'
1055 and bmap.worker_id = p_worker_id) logmap
1056 WHERE 1=1
1057 AND logmap.project_id = bv.project_id
1058 AND bv.fin_plan_type_id = logmap.plan_type_id
1059 AND bv.version_type is not NULL
1060 AND bv.fin_plan_type_id is not NULL
1061 AND fpo.project_id = bv.project_id
1062 AND bv.fin_plan_type_id = fpo.fin_plan_type_id
1063 AND bv.budget_version_id = fpo.fin_plan_version_id
1064 AND (bv.current_original_flag = 'Y'
1065 OR (bv.current_flag||DECODE(bv.baselined_date, NULL, 'N', 'Y')) = 'YY')
1066 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION'
1067 AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE'));
1068
1069 -- pji_utils.write2log('Inserted +ve records into ver4'||sql%rowcount);
1070 -- This sql will insert all the -3,-4 plan versions for Cost and Rev seperate plan_type_id
1071
1072 INSERT INTO PJI_FM_EXTR_PLNVER4
1073 (
1074 WORKER_ID ,
1075 PROJECT_ID ,
1076 PLAN_VERSION_ID ,
1077 WBS_STRUCT_VERSION_ID ,
1078 RBS_STRUCT_VERSION_ID ,
1079 PLAN_TYPE_CODE ,
1080 PLAN_TYPE_ID ,
1081 TIME_PHASED_TYPE_CODE ,
1082 TIME_DANGLING_FLAG ,
1083 RATE_DANGLING_FLAG ,
1084 PROJECT_TYPE_CLASS ,
1085 WP_FLAG ,
1086 CURRENT_FLAG ,
1087 ORIGINAL_FLAG ,
1088 CURRENT_ORIGINAL_FLAG ,
1089 BASELINED_FLAG ,
1090 SECONDARY_RBS_FLAG ,
1091 LP_FLAG
1092 )
1093 SELECT DISTINCT bv.worker_id worker_id
1094 , den.sup_project_id project_id
1095 , cbco.plan_version_id
1096 , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(den.sup_project_id) wbs_struct_version_id
1097 , bv.rbs_struct_version_id
1098 , bv.plan_type_code
1099 , bv.plan_type_id
1100 , bv.time_phased_type_code
1101 , NULL -- time dangl flg
1102 , NULL -- rate dangl flg
1103 , NULL -- project type class
1104 , 'N' -- wp flag
1105 , DECODE(cbco.plan_version_id, -3, 'Y', 'N') current_flag
1106 , DECODE(cbco.plan_version_id, -4, 'Y', 'N') original_flag
1107 , DECODE(cbco.plan_version_id, -4, 'Y', 'N') curr_original_flag
1108 , DECODE(cbco.plan_version_id, -3, 'Y', 'N') baselined_flag
1109 , bv.SECONDARY_RBS_FLAG
1110 , bv.lp_flag
1111 FROM PJI_FM_EXTR_PLNVER4 bv
1112 , pji_xbs_denorm den
1113 , ( SELECT -3 plan_version_id FROM DUAL
1114 UNION ALL
1115 SELECT -4 FROM DUAL ) cbco
1116 WHERE 1=1
1117 AND bv.plan_version_id > 0
1118 AND bv.wp_flag = 'N'
1119 AND bv.baselined_flag = 'Y'
1120 AND den.struct_version_id IS NULL
1121 AND den.struct_type = 'PRG'
1122 AND den.sub_id = bv.wbs_struct_version_id
1123 AND NVL(den.relationship_type, 'WF') IN ('LF', 'WF');
1124
1125 -- pji_utils.write2log('Inserted -3,-4 records into ver4'||sql%rowcount);
1126 -- Delete all the -3,-4 lines from the pji_fp_xbs_accum_f for Cost and Rev plan type having plan type code is null
1127 -- l_Stage := ' Delete all the -3,-4 lines from the pji_fp_xbs_accum_f for Cost and Rev plan type';
1128
1129 DELETE pji_rollup_level_status hdr
1130 where hdr.plan_version_id < -1
1131 and exists (select 1 from pji_fm_extr_plnver4 ver3
1132 where ver3.worker_id = p_worker_id
1133 and ver3.project_id = hdr.project_id
1134 and ver3.plan_version_id = hdr.plan_version_id
1135 and ver3.plan_version_id < -1);
1136
1137
1138 DELETE FROM pji_fp_xbs_accum_f fact
1139 WHERE fact.plan_version_id < -1
1140 and exists (select 1 from pji_fm_extr_plnver4 ver3
1141 where ver3.worker_id = p_worker_id
1142 and ver3.project_id = fact.project_id
1143 and ver3.plan_version_id = fact.plan_version_id
1144 and ver3.plan_type_id = fact.plan_type_id
1145 and ver3.plan_version_id < -1);
1146
1147 /*************************************************Rollup for Program Reporting and creating -3 and -4 lines for COST_AND_REV_SEP*/
1148 -- Inserting all the positive plan version data into the PJP1 table for futher
1149 -- creation of wbs_rollup_flag = 'Y' lines and
1150 -- prg_rollup_flag = 'Y' lines if required
1151 -- Commit;
1152 -- pji_utils.write2log('Inserting all the positive plan version data into the PJP1 table for futher ');
1153
1154 INSERT INTO PJI_FP_AGGR_PJP1
1155 (
1156 WORKER_ID
1157 , PROJECT_ID
1158 , PROJECT_ORG_ID
1159 , PROJECT_ORGANIZATION_ID
1160 , PROJECT_ELEMENT_ID
1161 , TIME_ID
1162 , PERIOD_TYPE_ID
1163 , CALENDAR_TYPE
1164 , RBS_AGGR_LEVEL
1165 , WBS_ROLLUP_FLAG
1166 , PRG_ROLLUP_FLAG
1167 , CURR_RECORD_TYPE_ID
1168 , CURRENCY_CODE
1169 , RBS_ELEMENT_ID
1170 , RBS_VERSION_ID
1171 , PLAN_VERSION_ID
1172 , PLAN_TYPE_ID
1173 , RAW_COST
1174 , BRDN_COST
1175 , REVENUE
1176 , BILL_RAW_COST
1177 , BILL_BRDN_COST
1178 , BILL_LABOR_RAW_COST
1179 , BILL_LABOR_BRDN_COST
1180 , BILL_LABOR_HRS
1181 , EQUIPMENT_RAW_COST
1182 , EQUIPMENT_BRDN_COST
1183 , CAPITALIZABLE_RAW_COST
1184 , CAPITALIZABLE_BRDN_COST
1185 , LABOR_RAW_COST
1186 , LABOR_BRDN_COST
1187 , LABOR_HRS
1188 , LABOR_REVENUE
1189 , EQUIPMENT_HOURS
1190 , BILLABLE_EQUIPMENT_HOURS
1191 , SUP_INV_COMMITTED_COST
1192 , PO_COMMITTED_COST
1193 , PR_COMMITTED_COST
1194 , OTH_COMMITTED_COST
1195 , ACT_LABOR_HRS
1196 , ACT_EQUIP_HRS
1197 , ACT_LABOR_BRDN_COST
1198 , ACT_EQUIP_BRDN_COST
1199 , ACT_BRDN_COST
1200 , ACT_RAW_COST
1201 , ACT_REVENUE
1202 , ACT_LABOR_RAW_COST
1203 , ACT_EQUIP_RAW_COST
1204 , ETC_LABOR_HRS
1205 , ETC_EQUIP_HRS
1206 , ETC_LABOR_BRDN_COST
1207 , ETC_EQUIP_BRDN_COST
1208 , ETC_BRDN_COST
1209 , ETC_RAW_COST
1210 , ETC_LABOR_RAW_COST
1211 , ETC_EQUIP_RAW_COST
1212 , CUSTOM1
1213 , CUSTOM2
1214 , CUSTOM3
1215 , CUSTOM4
1216 , CUSTOM5
1217 , CUSTOM6
1218 , CUSTOM7
1219 , CUSTOM8
1220 , CUSTOM9
1221 , CUSTOM10
1222 , CUSTOM11
1223 , CUSTOM12
1224 , CUSTOM13
1225 , CUSTOM14
1226 , CUSTOM15
1227 , LINE_TYPE
1228 , RATE_DANGLING_FLAG
1229 , TIME_DANGLING_FLAG
1230 , START_DATE
1231 , END_DATE
1232 , PRG_LEVEL
1233 , PLAN_TYPE_CODE
1234 )
1235 select WORKER_ID
1236 , F.PROJECT_ID
1237 , PROJECT_ORG_ID
1238 , PROJECT_ORGANIZATION_ID
1239 , PROJECT_ELEMENT_ID
1240 , TIME_ID
1241 , PERIOD_TYPE_ID
1242 , CALENDAR_TYPE
1243 , RBS_AGGR_LEVEL
1244 , WBS_ROLLUP_FLAG
1245 , PRG_ROLLUP_FLAG
1246 , CURR_RECORD_TYPE_ID
1247 , CURRENCY_CODE
1248 , RBS_ELEMENT_ID
1249 , RBS_VERSION_ID
1250 , F.PLAN_VERSION_ID
1251 , f.PLAN_TYPE_ID
1252 , RAW_COST
1253 , BRDN_COST
1254 , REVENUE
1255 , BILL_RAW_COST
1256 , BILL_BRDN_COST
1257 , BILL_LABOR_RAW_COST
1258 , BILL_LABOR_BRDN_COST
1259 , BILL_LABOR_HRS
1260 , EQUIPMENT_RAW_COST
1261 , EQUIPMENT_BRDN_COST
1262 , CAPITALIZABLE_RAW_COST
1263 , CAPITALIZABLE_BRDN_COST
1264 , LABOR_RAW_COST
1265 , LABOR_BRDN_COST
1266 , LABOR_HRS
1267 , LABOR_REVENUE
1268 , EQUIPMENT_HOURS
1269 , BILLABLE_EQUIPMENT_HOURS
1270 , SUP_INV_COMMITTED_COST
1271 , PO_COMMITTED_COST
1272 , PR_COMMITTED_COST
1273 , OTH_COMMITTED_COST
1274 , ACT_LABOR_HRS
1275 , ACT_EQUIP_HRS
1276 , ACT_LABOR_BRDN_COST
1277 , ACT_EQUIP_BRDN_COST
1278 , ACT_BRDN_COST
1279 , ACT_RAW_COST
1280 , ACT_REVENUE
1281 , ACT_LABOR_RAW_COST
1282 , ACT_EQUIP_RAW_COST
1283 , ETC_LABOR_HRS
1284 , ETC_EQUIP_HRS
1285 , ETC_LABOR_BRDN_COST
1286 , ETC_EQUIP_BRDN_COST
1287 , ETC_BRDN_COST
1288 , ETC_RAW_COST
1289 , ETC_LABOR_RAW_COST
1290 , ETC_EQUIP_RAW_COST
1291 , CUSTOM1
1292 , CUSTOM2
1293 , CUSTOM3
1294 , CUSTOM4
1295 , CUSTOM5
1296 , CUSTOM6
1297 , CUSTOM7
1298 , CUSTOM8
1299 , CUSTOM9
1300 , CUSTOM10
1301 , CUSTOM11
1302 , CUSTOM12
1303 , CUSTOM13
1304 , CUSTOM14
1305 , CUSTOM15
1306 , 'UPD'
1307 , RATE_DANGLING_FLAG
1308 , TIME_DANGLING_FLAG
1309 , SYSDATE
1310 , SYSDATE
1311 , 0
1312 ,ver.PLAN_TYPE_CODE
1313 FROM pji_fp_xbs_accum_f f
1314 , pji_fm_extr_plnver4 ver
1315 WHERE 1 = 1
1316 AND ver.project_id = f.project_id
1317 AND ver.plan_version_id = f.plan_version_id
1318 AND ver.plan_type_id = f.plan_type_id
1319 AND ver.plan_version_id > 0
1320 AND f.rbs_aggr_level IN ( 'L', 'T' )
1321 AND f.wbs_rollup_flag = 'N'
1322 AND f.prg_rollup_flag = 'N'
1323 AND ver.worker_id = p_worker_id;
1324
1325 -- pji_utils.write2log('Inserted records into pjp1'||sql%rowcount);
1326 -- for -3,-4 lines having prg_rollup_flag = 'N'
1327 -- pji_utils.write2log('Before rollup_fpr_wbs');
1328
1329 --- pji_pjp_sum_rollup.rollup_fpr_wbs(null);
1330 for c in C_BOTTOM_UP loop
1331
1332 l_level := c.SUP_LEVEL;
1333
1334 -- pji_utils.write2log('In loop of rollup_fpr_wbs'||l_level);
1335 -- rollup project hiearchy
1336
1337 insert into PJI_FP_AGGR_PJP1
1338 (
1339 WORKER_ID,
1340 RECORD_TYPE,
1341 PRG_LEVEL,
1342 LINE_TYPE,
1343 PROJECT_ID,
1344 PROJECT_ORG_ID,
1345 PROJECT_ORGANIZATION_ID,
1346 PROJECT_ELEMENT_ID,
1347 TIME_ID,
1348 PERIOD_TYPE_ID,
1349 CALENDAR_TYPE,
1350 RBS_AGGR_LEVEL,
1351 WBS_ROLLUP_FLAG,
1352 PRG_ROLLUP_FLAG,
1353 CURR_RECORD_TYPE_ID,
1354 CURRENCY_CODE,
1355 RBS_ELEMENT_ID,
1356 RBS_VERSION_ID,
1357 PLAN_VERSION_ID,
1358 PLAN_TYPE_ID,
1359 PLAN_TYPE_CODE,
1360 RAW_COST,
1361 BRDN_COST,
1362 REVENUE,
1363 BILL_RAW_COST,
1364 BILL_BRDN_COST,
1365 BILL_LABOR_RAW_COST,
1366 BILL_LABOR_BRDN_COST,
1367 BILL_LABOR_HRS,
1368 EQUIPMENT_RAW_COST,
1369 EQUIPMENT_BRDN_COST,
1370 CAPITALIZABLE_RAW_COST,
1371 CAPITALIZABLE_BRDN_COST,
1372 LABOR_RAW_COST,
1373 LABOR_BRDN_COST,
1374 LABOR_HRS,
1375 LABOR_REVENUE,
1376 EQUIPMENT_HOURS,
1377 BILLABLE_EQUIPMENT_HOURS,
1378 SUP_INV_COMMITTED_COST,
1379 PO_COMMITTED_COST,
1380 PR_COMMITTED_COST,
1381 OTH_COMMITTED_COST,
1382 ACT_LABOR_HRS,
1383 ACT_EQUIP_HRS,
1384 ACT_LABOR_BRDN_COST,
1385 ACT_EQUIP_BRDN_COST,
1386 ACT_BRDN_COST,
1387 ACT_RAW_COST,
1388 ACT_REVENUE,
1389 ACT_LABOR_RAW_COST,
1390 ACT_EQUIP_RAW_COST,
1391 ETC_LABOR_HRS,
1392 ETC_EQUIP_HRS,
1393 ETC_LABOR_BRDN_COST,
1394 ETC_EQUIP_BRDN_COST,
1395 ETC_BRDN_COST,
1396 ETC_RAW_COST,
1397 ETC_LABOR_RAW_COST,
1398 ETC_EQUIP_RAW_COST,
1399 CUSTOM1,
1400 CUSTOM2,
1401 CUSTOM3,
1402 CUSTOM4,
1403 CUSTOM5,
1404 CUSTOM6,
1405 CUSTOM7,
1406 CUSTOM8,
1407 CUSTOM9,
1408 CUSTOM10,
1409 CUSTOM11,
1410 CUSTOM12,
1411 CUSTOM13,
1412 CUSTOM14,
1413 CUSTOM15
1414 )
1415 select
1416 pjp1_i.WORKER_ID,
1417 pjp1_i.RECORD_TYPE,
1418 pjp1_i.PRG_LEVEL,
1419 pjp1_i.LINE_TYPE,
1420 pjp1_i.PROJECT_ID,
1421 pjp1_i.PROJECT_ORG_ID,
1422 pjp1_i.PROJECT_ORGANIZATION_ID,
1423 pjp1_i.PROJECT_ELEMENT_ID,
1424 pjp1_i.TIME_ID,
1425 pjp1_i.PERIOD_TYPE_ID,
1426 pjp1_i.CALENDAR_TYPE,
1427 pjp1_i.RBS_AGGR_LEVEL,
1428 pjp1_i.WBS_ROLLUP_FLAG,
1429 pjp1_i.PRG_ROLLUP_FLAG,
1430 pjp1_i.CURR_RECORD_TYPE_ID,
1431 pjp1_i.CURRENCY_CODE,
1432 pjp1_i.RBS_ELEMENT_ID,
1433 pjp1_i.RBS_VERSION_ID,
1434 pjp1_i.PLAN_VERSION_ID,
1435 pjp1_i.PLAN_TYPE_ID,
1436 pjp1_i.PLAN_TYPE_CODE,
1437 decode(pjp1_i.RELATIONSHIP_TYPE
1438 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1439 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1440 || '_' || pjp1_i.SUB_STATUS_CODE
1441 || '_' || pjp1_i.SUP_STATUS_CODE,
1442 'LW_N_Y__', to_number(null),
1443 decode(pjp1_i.RELATIONSHIP_TYPE
1444 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1445 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1446 || '_' || pjp1_i.SUP_VER_ENABLED,
1447 'LW_N_Y_Y', to_number(null),
1448 pjp1_i.RAW_COST)) RAW_COST,
1449 decode(pjp1_i.RELATIONSHIP_TYPE
1450 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1451 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1452 || '_' || pjp1_i.SUB_STATUS_CODE
1453 || '_' || pjp1_i.SUP_STATUS_CODE,
1454 'LW_N_Y__', to_number(null),
1455 decode(pjp1_i.RELATIONSHIP_TYPE
1456 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1457 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1458 || '_' || pjp1_i.SUP_VER_ENABLED,
1459 'LW_N_Y_Y', to_number(null),
1460 pjp1_i.BRDN_COST)) BRDN_COST,
1461 decode(pjp1_i.RELATIONSHIP_TYPE
1462 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1463 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1464 || '_' || pjp1_i.SUB_STATUS_CODE
1465 || '_' || pjp1_i.SUP_STATUS_CODE,
1466 'LW_N_Y__', to_number(null),
1467 decode(pjp1_i.RELATIONSHIP_TYPE
1468 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1469 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1470 || '_' || pjp1_i.SUP_VER_ENABLED,
1471 'LW_N_Y_Y', to_number(null),
1472 pjp1_i.REVENUE)) REVENUE,
1473 decode(pjp1_i.RELATIONSHIP_TYPE
1474 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1475 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1476 || '_' || pjp1_i.SUB_STATUS_CODE
1477 || '_' || pjp1_i.SUP_STATUS_CODE,
1478 'LW_N_Y__', to_number(null),
1479 decode(pjp1_i.RELATIONSHIP_TYPE
1480 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1481 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1482 || '_' || pjp1_i.SUP_VER_ENABLED,
1483 'LW_N_Y_Y', to_number(null),
1484 pjp1_i.BILL_RAW_COST)) BILL_RAW_COST,
1485 decode(pjp1_i.RELATIONSHIP_TYPE
1486 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1487 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1488 || '_' || pjp1_i.SUB_STATUS_CODE
1489 || '_' || pjp1_i.SUP_STATUS_CODE,
1490 'LW_N_Y__', to_number(null),
1491 decode(pjp1_i.RELATIONSHIP_TYPE
1492 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1493 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1494 || '_' || pjp1_i.SUP_VER_ENABLED,
1495 'LW_N_Y_Y', to_number(null),
1496 pjp1_i.BILL_BRDN_COST)) BILL_BRDN_COST,
1497 decode(pjp1_i.RELATIONSHIP_TYPE
1498 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1499 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1500 || '_' || pjp1_i.SUB_STATUS_CODE
1501 || '_' || pjp1_i.SUP_STATUS_CODE,
1502 'LW_N_Y__', to_number(null),
1503 decode(pjp1_i.RELATIONSHIP_TYPE
1504 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1505 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1506 || '_' || pjp1_i.SUP_VER_ENABLED,
1507 'LW_N_Y_Y', to_number(null),
1508 pjp1_i.BILL_LABOR_RAW_COST)) BILL_LABOR_RAW_COST,
1509 decode(pjp1_i.RELATIONSHIP_TYPE
1510 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1511 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1512 || '_' || pjp1_i.SUB_STATUS_CODE
1513 || '_' || pjp1_i.SUP_STATUS_CODE,
1514 'LW_N_Y__', to_number(null),
1515 decode(pjp1_i.RELATIONSHIP_TYPE
1516 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1517 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1518 || '_' || pjp1_i.SUP_VER_ENABLED,
1519 'LW_N_Y_Y', to_number(null),
1520 pjp1_i.BILL_LABOR_BRDN_COST)) BILL_LABOR_BRDN_COST,
1521 decode(pjp1_i.RELATIONSHIP_TYPE
1522 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1523 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1524 || '_' || pjp1_i.SUB_STATUS_CODE
1525 || '_' || pjp1_i.SUP_STATUS_CODE,
1526 'LW_N_Y__', to_number(null),
1527 decode(pjp1_i.RELATIONSHIP_TYPE
1528 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1529 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1530 || '_' || pjp1_i.SUP_VER_ENABLED,
1531 'LW_N_Y_Y', to_number(null),
1532 pjp1_i.BILL_LABOR_HRS)) BILL_LABOR_HRS,
1533 decode(pjp1_i.RELATIONSHIP_TYPE
1534 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1535 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1536 || '_' || pjp1_i.SUB_STATUS_CODE
1537 || '_' || pjp1_i.SUP_STATUS_CODE,
1538 'LW_N_Y__', to_number(null),
1539 decode(pjp1_i.RELATIONSHIP_TYPE
1540 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1541 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1542 || '_' || pjp1_i.SUP_VER_ENABLED,
1543 'LW_N_Y_Y', to_number(null),
1544 pjp1_i.EQUIPMENT_RAW_COST)) EQUIPMENT_RAW_COST,
1545 decode(pjp1_i.RELATIONSHIP_TYPE
1546 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1547 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1548 || '_' || pjp1_i.SUB_STATUS_CODE
1549 || '_' || pjp1_i.SUP_STATUS_CODE,
1550 'LW_N_Y__', to_number(null),
1551 decode(pjp1_i.RELATIONSHIP_TYPE
1552 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1553 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1554 || '_' || pjp1_i.SUP_VER_ENABLED,
1555 'LW_N_Y_Y', to_number(null),
1556 pjp1_i.EQUIPMENT_BRDN_COST)) EQUIPMENT_BRDN_COST,
1557 decode(pjp1_i.RELATIONSHIP_TYPE
1558 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1559 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1560 || '_' || pjp1_i.SUB_STATUS_CODE
1561 || '_' || pjp1_i.SUP_STATUS_CODE,
1562 'LW_N_Y__', to_number(null),
1563 decode(pjp1_i.RELATIONSHIP_TYPE
1564 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1565 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1566 || '_' || pjp1_i.SUP_VER_ENABLED,
1567 'LW_N_Y_Y', to_number(null),
1568 pjp1_i.CAPITALIZABLE_RAW_COST)) CAPITALIZABLE_RAW_COST,
1569 decode(pjp1_i.RELATIONSHIP_TYPE
1570 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1571 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1572 || '_' || pjp1_i.SUB_STATUS_CODE
1573 || '_' || pjp1_i.SUP_STATUS_CODE,
1574 'LW_N_Y__', to_number(null),
1575 decode(pjp1_i.RELATIONSHIP_TYPE
1576 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1577 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1578 || '_' || pjp1_i.SUP_VER_ENABLED,
1579 'LW_N_Y_Y', to_number(null),
1580 pjp1_i.CAPITALIZABLE_BRDN_COST)) CAPITALIZABLE_BRDN_COST,
1581 decode(pjp1_i.RELATIONSHIP_TYPE
1582 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1583 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1584 || '_' || pjp1_i.SUB_STATUS_CODE
1585 || '_' || pjp1_i.SUP_STATUS_CODE,
1586 'LW_N_Y__', to_number(null),
1587 decode(pjp1_i.RELATIONSHIP_TYPE
1588 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1589 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1590 || '_' || pjp1_i.SUP_VER_ENABLED,
1591 'LW_N_Y_Y', to_number(null),
1592 pjp1_i.LABOR_RAW_COST)) LABOR_RAW_COST,
1593 decode(pjp1_i.RELATIONSHIP_TYPE
1594 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1595 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1596 || '_' || pjp1_i.SUB_STATUS_CODE
1597 || '_' || pjp1_i.SUP_STATUS_CODE,
1598 'LW_N_Y__', to_number(null),
1599 decode(pjp1_i.RELATIONSHIP_TYPE
1600 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1601 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1602 || '_' || pjp1_i.SUP_VER_ENABLED,
1603 'LW_N_Y_Y', to_number(null),
1604 pjp1_i.LABOR_BRDN_COST)) LABOR_BRDN_COST,
1605 decode(pjp1_i.RELATIONSHIP_TYPE
1606 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1607 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1608 || '_' || pjp1_i.SUB_STATUS_CODE
1609 || '_' || pjp1_i.SUP_STATUS_CODE,
1610 'LW_N_Y__', to_number(null),
1611 decode(pjp1_i.RELATIONSHIP_TYPE
1612 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1613 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1614 || '_' || pjp1_i.SUP_VER_ENABLED,
1615 'LW_N_Y_Y', to_number(null),
1616 pjp1_i.LABOR_HRS)) LABOR_HRS,
1617 decode(pjp1_i.RELATIONSHIP_TYPE
1618 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1619 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1620 || '_' || pjp1_i.SUB_STATUS_CODE
1621 || '_' || pjp1_i.SUP_STATUS_CODE,
1622 'LW_N_Y__', to_number(null),
1623 decode(pjp1_i.RELATIONSHIP_TYPE
1624 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1625 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1626 || '_' || pjp1_i.SUP_VER_ENABLED,
1627 'LW_N_Y_Y', to_number(null),
1628 pjp1_i.LABOR_REVENUE)) LABOR_REVENUE,
1629 decode(pjp1_i.RELATIONSHIP_TYPE
1630 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1631 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1632 || '_' || pjp1_i.SUB_STATUS_CODE
1633 || '_' || pjp1_i.SUP_STATUS_CODE,
1634 'LW_N_Y__', to_number(null),
1635 decode(pjp1_i.RELATIONSHIP_TYPE
1636 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1637 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1638 || '_' || pjp1_i.SUP_VER_ENABLED,
1639 'LW_N_Y_Y', to_number(null),
1640 pjp1_i.EQUIPMENT_HOURS)) EQUIPMENT_HOURS,
1641 decode(pjp1_i.RELATIONSHIP_TYPE
1642 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1643 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1644 || '_' || pjp1_i.SUB_STATUS_CODE
1645 || '_' || pjp1_i.SUP_STATUS_CODE,
1646 'LW_N_Y__', to_number(null),
1647 decode(pjp1_i.RELATIONSHIP_TYPE
1648 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1649 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1650 || '_' || pjp1_i.SUP_VER_ENABLED,
1651 'LW_N_Y_Y', to_number(null),
1652 pjp1_i.BILLABLE_EQUIPMENT_HOURS)) BILLABLE_EQUIPMENT_HOURS,
1653 decode(pjp1_i.RELATIONSHIP_TYPE
1654 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1655 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1656 || '_' || pjp1_i.SUB_STATUS_CODE
1657 || '_' || pjp1_i.SUP_STATUS_CODE,
1658 'LW_N_Y__', to_number(null),
1659 decode(pjp1_i.RELATIONSHIP_TYPE
1660 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1661 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1662 || '_' || pjp1_i.SUP_VER_ENABLED,
1663 'LW_N_Y_Y', to_number(null),
1664 pjp1_i.SUP_INV_COMMITTED_COST)) SUP_INV_COMMITTED_COST,
1665 decode(pjp1_i.RELATIONSHIP_TYPE
1666 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1667 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1668 || '_' || pjp1_i.SUB_STATUS_CODE
1669 || '_' || pjp1_i.SUP_STATUS_CODE,
1670 'LW_N_Y__', to_number(null),
1671 decode(pjp1_i.RELATIONSHIP_TYPE
1672 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1673 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1674 || '_' || pjp1_i.SUP_VER_ENABLED,
1675 'LW_N_Y_Y', to_number(null),
1676 pjp1_i.PO_COMMITTED_COST)) PO_COMMITTED_COST,
1677 decode(pjp1_i.RELATIONSHIP_TYPE
1678 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1679 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1680 || '_' || pjp1_i.SUB_STATUS_CODE
1681 || '_' || pjp1_i.SUP_STATUS_CODE,
1682 'LW_N_Y__', to_number(null),
1683 decode(pjp1_i.RELATIONSHIP_TYPE
1684 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1685 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1686 || '_' || pjp1_i.SUP_VER_ENABLED,
1687 'LW_N_Y_Y', to_number(null),
1688 pjp1_i.PR_COMMITTED_COST)) PR_COMMITTED_COST,
1689 decode(pjp1_i.RELATIONSHIP_TYPE
1690 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1691 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1692 || '_' || pjp1_i.SUB_STATUS_CODE
1693 || '_' || pjp1_i.SUP_STATUS_CODE,
1694 'LW_N_Y__', to_number(null),
1695 decode(pjp1_i.RELATIONSHIP_TYPE
1696 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1697 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1698 || '_' || pjp1_i.SUP_VER_ENABLED,
1699 'LW_N_Y_Y', to_number(null),
1700 pjp1_i.OTH_COMMITTED_COST)) OTH_COMMITTED_COST,
1701 decode(pjp1_i.RELATIONSHIP_TYPE
1702 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1703 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1704 || '_' || pjp1_i.SUP_STATUS_CODE,
1705 'LW_N_Y_', to_number(null),
1706 pjp1_i.ACT_LABOR_HRS) ACT_LABOR_HRS,
1707 decode(pjp1_i.RELATIONSHIP_TYPE
1708 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1709 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1710 || '_' || pjp1_i.SUP_STATUS_CODE,
1711 'LW_N_Y_', to_number(null),
1712 pjp1_i.ACT_EQUIP_HRS) ACT_EQUIP_HRS,
1713 decode(pjp1_i.RELATIONSHIP_TYPE
1714 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1715 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1716 || '_' || pjp1_i.SUP_STATUS_CODE,
1717 'LW_N_Y_', to_number(null),
1718 pjp1_i.ACT_LABOR_BRDN_COST) ACT_LABOR_BRDN_COST,
1719 decode(pjp1_i.RELATIONSHIP_TYPE
1720 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1721 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1722 || '_' || pjp1_i.SUP_STATUS_CODE,
1723 'LW_N_Y_', to_number(null),
1724 pjp1_i.ACT_EQUIP_BRDN_COST) ACT_EQUIP_BRDN_COST,
1725 decode(pjp1_i.RELATIONSHIP_TYPE
1726 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1727 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1728 || '_' || pjp1_i.SUP_STATUS_CODE,
1729 'LW_N_Y_', to_number(null),
1730 pjp1_i.ACT_BRDN_COST) ACT_BRDN_COST,
1731 decode(pjp1_i.RELATIONSHIP_TYPE
1732 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1733 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1734 || '_' || pjp1_i.SUP_STATUS_CODE,
1735 'LW_N_Y_', to_number(null),
1736 pjp1_i.ACT_RAW_COST) ACT_RAW_COST,
1737 decode(pjp1_i.RELATIONSHIP_TYPE
1738 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1739 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1740 || '_' || pjp1_i.SUP_STATUS_CODE,
1741 'LW_N_Y_', to_number(null),
1742 pjp1_i.ACT_REVENUE) ACT_REVENUE,
1743 decode(pjp1_i.RELATIONSHIP_TYPE
1744 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1745 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1746 || '_' || pjp1_i.SUP_STATUS_CODE,
1747 'LW_N_Y_', to_number(null),
1748 pjp1_i.ACT_LABOR_RAW_COST) ACT_LABOR_RAW_COST,
1749 decode(pjp1_i.RELATIONSHIP_TYPE
1750 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1751 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1752 || '_' || pjp1_i.SUP_STATUS_CODE,
1753 'LW_N_Y_', to_number(null),
1754 pjp1_i.ACT_EQUIP_RAW_COST) ACT_EQUIP_RAW_COST,
1755 decode(pjp1_i.RELATIONSHIP_TYPE
1756 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1757 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1758 || '_' || pjp1_i.SUP_STATUS_CODE,
1759 'LW_N_Y_', to_number(null),
1760 pjp1_i.ETC_LABOR_HRS) ETC_LABOR_HRS,
1761 decode(pjp1_i.RELATIONSHIP_TYPE
1762 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1763 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1764 || '_' || pjp1_i.SUP_STATUS_CODE,
1765 'LW_N_Y_', to_number(null),
1766 pjp1_i.ETC_EQUIP_HRS) ETC_EQUIP_HRS,
1767 decode(pjp1_i.RELATIONSHIP_TYPE
1768 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1769 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1770 || '_' || pjp1_i.SUP_STATUS_CODE,
1771 'LW_N_Y_', to_number(null),
1772 pjp1_i.ETC_LABOR_BRDN_COST) ETC_LABOR_BRDN_COST,
1773 decode(pjp1_i.RELATIONSHIP_TYPE
1774 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1775 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1776 || '_' || pjp1_i.SUP_STATUS_CODE,
1777 'LW_N_Y_', to_number(null),
1778 pjp1_i.ETC_EQUIP_BRDN_COST) ETC_EQUIP_BRDN_COST,
1779 decode(pjp1_i.RELATIONSHIP_TYPE
1780 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1781 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1782 || '_' || pjp1_i.SUP_STATUS_CODE,
1783 'LW_N_Y_', to_number(null),
1784 pjp1_i.ETC_BRDN_COST) ETC_BRDN_COST,
1785 decode(pjp1_i.RELATIONSHIP_TYPE
1786 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1787 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1788 || '_' || pjp1_i.SUP_STATUS_CODE,
1789 'LW_N_Y_', to_number(null),
1790 pjp1_i.ETC_RAW_COST) ETC_RAW_COST,
1791 decode(pjp1_i.RELATIONSHIP_TYPE
1792 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1793 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1794 || '_' || pjp1_i.SUP_STATUS_CODE,
1795 'LW_N_Y_', to_number(null),
1796 pjp1_i.ETC_LABOR_RAW_COST) ETC_LABOR_RAW_COST,
1797 decode(pjp1_i.RELATIONSHIP_TYPE
1798 || '_' || pjp1_i.WBS_ROLLUP_FLAG
1799 || '_' || pjp1_i.PRG_ROLLUP_FLAG
1800 || '_' || pjp1_i.SUP_STATUS_CODE,
1801 'LW_N_Y_', to_number(null),
1802 pjp1_i.ETC_EQUIP_RAW_COST) ETC_EQUIP_RAW_COST,
1803 pjp1_i.CUSTOM1,
1804 pjp1_i.CUSTOM2,
1805 pjp1_i.CUSTOM3,
1806 pjp1_i.CUSTOM4,
1807 pjp1_i.CUSTOM5,
1808 pjp1_i.CUSTOM6,
1809 pjp1_i.CUSTOM7,
1810 pjp1_i.CUSTOM8,
1811 pjp1_i.CUSTOM9,
1812 pjp1_i.CUSTOM10,
1813 pjp1_i.CUSTOM11,
1814 pjp1_i.CUSTOM12,
1815 pjp1_i.CUSTOM13,
1816 pjp1_i.CUSTOM14,
1817 pjp1_i.CUSTOM15
1818 from
1819 (
1820 select
1821 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1822 pjp.INSERT_FLAG, 'Y') INSERT_FLAG,
1823 pjp.RELATIONSHIP_TYPE,
1824 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1825 null, sub_ver.STATUS_CODE) SUB_STATUS_CODE,
1826 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1827 null, sup_ver.STATUS_CODE) SUP_STATUS_CODE,
1828 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1829 null, sup_wpa.WP_ENABLE_VERSION_FLAG)SUP_VER_ENABLED,
1830 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1831 null, decode(pjp.PLAN_VERSION_ID,
1832 -1, prg.SUP_ID,
1833 -3, prg.SUP_ID,
1834 -4, prg.SUP_ID,
1835 null)) SUP_ID,
1836 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1837 null, decode(pjp.PLAN_VERSION_ID,
1838 -1, prg.SUP_EMT_ID,
1839 -3, prg.SUP_EMT_ID,
1840 -4, prg.SUP_EMT_ID,
1841 null)) SUP_EMT_ID,
1842 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1843 null, decode(pjp.PLAN_VERSION_ID,
1844 -1, prg.WP_FLAG,
1845 -3, prg.WP_FLAG,
1846 -4, prg.WP_FLAG,
1847 null)) SUP_WP_FLAG,
1848 -- 1 WORKER_ID,
1849 p_worker_id WORKER_ID,
1850 'W' RECORD_TYPE,
1851 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1852 l_level, prg.SUP_LEVEL) PRG_LEVEL,
1853 pjp.LINE_TYPE,
1854 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1855 pjp.PROJECT_ID, prg.SUP_PROJECT_ID) PROJECT_ID,
1856 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1857 pjp.PROJECT_ORG_ID,
1858 prg.SUP_PROJECT_ORG_ID) PROJECT_ORG_ID,
1859 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1860 pjp.PROJECT_ORGANIZATION_ID,
1861 prg.SUP_PROJECT_ORGANIZATION_ID) PROJECT_ORGANIZATION_ID,
1862 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1863 pjp.PROJECT_ELEMENT_ID,
1864 prg.SUB_ROLLUP_ID) PROJECT_ELEMENT_ID,
1865 pjp.TIME_ID,
1866 pjp.PERIOD_TYPE_ID,
1867 pjp.CALENDAR_TYPE,
1868 pjp.RBS_AGGR_LEVEL,
1869 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1870 pjp.WBS_ROLLUP_FLAG, 'N') WBS_ROLLUP_FLAG,
1871 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1872 pjp.PRG_ROLLUP_FLAG, 'Y') PRG_ROLLUP_FLAG,
1873 pjp.CURR_RECORD_TYPE_ID,
1874 pjp.CURRENCY_CODE,
1875 pjp.RBS_ELEMENT_ID,
1876 pjp.RBS_VERSION_ID,
1877 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1878 pjp.PLAN_VERSION_ID,
1879 decode(pjp.PLAN_VERSION_ID,
1880 -1, pjp.PLAN_VERSION_ID,
1881 -2, pjp.PLAN_VERSION_ID,
1882 -3, pjp.PLAN_VERSION_ID,
1883 -4, pjp.PLAN_VERSION_ID,
1884 wbs_hdr.PLAN_VERSION_ID)) PLAN_VERSION_ID,
1885 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
1886 pjp.PLAN_TYPE_ID,
1887 decode(pjp.PLAN_VERSION_ID,
1888 -1, pjp.PLAN_TYPE_ID,
1889 -2, pjp.PLAN_TYPE_ID,
1890 -3, pjp.PLAN_TYPE_ID,
1891 -4, pjp.PLAN_TYPE_ID,
1892 wbs_hdr.PLAN_TYPE_ID)) PLAN_TYPE_ID,
1893 pjp.PLAN_TYPE_CODE,
1894 sum(pjp.RAW_COST) RAW_COST,
1895 sum(pjp.BRDN_COST) BRDN_COST,
1896 sum(pjp.REVENUE) REVENUE,
1897 sum(pjp.BILL_RAW_COST) BILL_RAW_COST,
1898 sum(pjp.BILL_BRDN_COST) BILL_BRDN_COST,
1899 sum(pjp.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1900 sum(pjp.BILL_LABOR_BRDN_COST) BILL_LABOR_BRDN_COST,
1901 sum(pjp.BILL_LABOR_HRS) BILL_LABOR_HRS,
1902 sum(pjp.EQUIPMENT_RAW_COST) EQUIPMENT_RAW_COST,
1903 sum(pjp.EQUIPMENT_BRDN_COST) EQUIPMENT_BRDN_COST,
1904 sum(pjp.CAPITALIZABLE_RAW_COST) CAPITALIZABLE_RAW_COST,
1905 sum(pjp.CAPITALIZABLE_BRDN_COST) CAPITALIZABLE_BRDN_COST,
1906 sum(pjp.LABOR_RAW_COST) LABOR_RAW_COST,
1907 sum(pjp.LABOR_BRDN_COST) LABOR_BRDN_COST,
1908 sum(pjp.LABOR_HRS) LABOR_HRS,
1909 sum(pjp.LABOR_REVENUE) LABOR_REVENUE,
1910 sum(pjp.EQUIPMENT_HOURS) EQUIPMENT_HOURS,
1911 sum(pjp.BILLABLE_EQUIPMENT_HOURS) BILLABLE_EQUIPMENT_HOURS,
1912 sum(pjp.SUP_INV_COMMITTED_COST) SUP_INV_COMMITTED_COST,
1913 sum(pjp.PO_COMMITTED_COST) PO_COMMITTED_COST,
1914 sum(pjp.PR_COMMITTED_COST) PR_COMMITTED_COST,
1915 sum(pjp.OTH_COMMITTED_COST) OTH_COMMITTED_COST,
1916 sum(pjp.ACT_LABOR_HRS) ACT_LABOR_HRS,
1917 sum(pjp.ACT_EQUIP_HRS) ACT_EQUIP_HRS,
1918 sum(pjp.ACT_LABOR_BRDN_COST) ACT_LABOR_BRDN_COST,
1919 sum(pjp.ACT_EQUIP_BRDN_COST) ACT_EQUIP_BRDN_COST,
1920 sum(pjp.ACT_BRDN_COST) ACT_BRDN_COST,
1921 sum(pjp.ACT_RAW_COST) ACT_RAW_COST,
1922 sum(pjp.ACT_REVENUE) ACT_REVENUE,
1923 sum(pjp.ACT_LABOR_RAW_COST) ACT_LABOR_RAW_COST,
1924 sum(pjp.ACT_EQUIP_RAW_COST) ACT_EQUIP_RAW_COST,
1925 sum(pjp.ETC_LABOR_HRS) ETC_LABOR_HRS,
1926 sum(pjp.ETC_EQUIP_HRS) ETC_EQUIP_HRS,
1927 sum(pjp.ETC_LABOR_BRDN_COST) ETC_LABOR_BRDN_COST,
1928 sum(pjp.ETC_EQUIP_BRDN_COST) ETC_EQUIP_BRDN_COST,
1929 sum(pjp.ETC_BRDN_COST) ETC_BRDN_COST,
1930 sum(pjp.ETC_RAW_COST) ETC_RAW_COST,
1931 sum(pjp.ETC_LABOR_RAW_COST) ETC_LABOR_RAW_COST,
1932 sum(pjp.ETC_EQUIP_RAW_COST) ETC_EQUIP_RAW_COST,
1933 sum(pjp.CUSTOM1) CUSTOM1,
1934 sum(pjp.CUSTOM2) CUSTOM2,
1935 sum(pjp.CUSTOM3) CUSTOM3,
1936 sum(pjp.CUSTOM4) CUSTOM4,
1937 sum(pjp.CUSTOM5) CUSTOM5,
1938 sum(pjp.CUSTOM6) CUSTOM6,
1939 sum(pjp.CUSTOM7) CUSTOM7,
1940 sum(pjp.CUSTOM8) CUSTOM8,
1941 sum(pjp.CUSTOM9) CUSTOM9,
1942 sum(pjp.CUSTOM10) CUSTOM10,
1943 sum(pjp.CUSTOM11) CUSTOM11,
1944 sum(pjp.CUSTOM12) CUSTOM12,
1945 sum(pjp.CUSTOM13) CUSTOM13,
1946 sum(pjp.CUSTOM14) CUSTOM14,
1947 sum(pjp.CUSTOM15) CUSTOM15
1948 from
1949 (
1950 select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
1951 -- get incremental task level amounts from source and
1952 -- program rollup amounts from interim
1953 to_char(null) LINE_TYPE,
1954 wbs_hdr.WBS_VERSION_ID,
1955 decode(wbs_hdr.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE,
1956 decode(wbs_hdr.WP_FLAG
1957 || '_' || to_char(sign(pjp1.PLAN_VERSION_ID))
1958 || '_' || nvl(fin_plan.INVERT_ID, 'PRJ'),
1959 'N_1_PRJ', 'N',
1960 'N_-1_PRG', 'N',
1961 decode(top_slice.INVERT_ID,
1962 'PRJ', 'Y',
1963 decode(wbs.SUB_LEVEL,
1964 1, 'Y', 'N'))) PUSHUP_FLAG,
1965 decode(pjp1.RBS_AGGR_LEVEL,
1966 'L', 'N',
1967 decode(wbs_hdr.WP_FLAG
1968 || '_' || to_char(sign(pjp1.PLAN_VERSION_ID))
1969 || '_' || fin_plan.INVERT_ID,
1970 'N_1_PRG', decode(top_slice.INVERT_ID,
1971 'PRJ', 'Y',
1972 decode(wbs.SUB_LEVEL,
1973 1, 'Y', 'N')),
1974 'N_-1_PRG', 'N',
1975 decode(wbs_hdr.WP_FLAG
1976 || '_' || fin_plan.INVERT_ID
1977 || '_' || fin_plan.CB
1978 || '_' || fin_plan.CO
1979 || '_'
1980 || to_char(fin_plan.PLAN_VERSION_ID),
1981 'N_PRJ_Y_Y_-4', 'N',
1982 'Y'))
1983 ) INSERT_FLAG,
1984 pjp1.PROJECT_ID,
1985 pjp1.PROJECT_ORG_ID,
1986 pjp1.PROJECT_ORGANIZATION_ID,
1987 decode(top_slice.INVERT_ID,
1988 'PRJ', prg.SUP_EMT_ID,
1989 decode(wbs.SUB_LEVEL,
1990 1, prg.SUP_EMT_ID,
1991 wbs.SUP_EMT_ID)) PROJECT_ELEMENT_ID,
1992 pjp1.TIME_ID,
1993 pjp1.PERIOD_TYPE_ID,
1994 pjp1.CALENDAR_TYPE,
1995 pjp1.RBS_AGGR_LEVEL,
1996 'Y' WBS_ROLLUP_FLAG,
1997 pjp1.PRG_ROLLUP_FLAG,
1998 pjp1.CURR_RECORD_TYPE_ID,
1999 pjp1.CURRENCY_CODE,
2000 pjp1.RBS_ELEMENT_ID,
2001 pjp1.RBS_VERSION_ID,
2002 decode(wbs_hdr.WP_FLAG || '_' || fin_plan.INVERT_ID,
2003 'N_PRG', fin_plan.PLAN_VERSION_ID,
2004 pjp1.PLAN_VERSION_ID) PLAN_VERSION_ID,
2005 pjp1.PLAN_TYPE_ID,
2006 pjp1.PLAN_TYPE_CODE,
2007 pjp1.RAW_COST,
2008 pjp1.BRDN_COST,
2009 pjp1.REVENUE,
2010 pjp1.BILL_RAW_COST,
2011 pjp1.BILL_BRDN_COST,
2012 pjp1.BILL_LABOR_RAW_COST,
2013 pjp1.BILL_LABOR_BRDN_COST,
2014 pjp1.BILL_LABOR_HRS,
2015 pjp1.EQUIPMENT_RAW_COST,
2016 pjp1.EQUIPMENT_BRDN_COST,
2017 pjp1.CAPITALIZABLE_RAW_COST,
2018 pjp1.CAPITALIZABLE_BRDN_COST,
2019 pjp1.LABOR_RAW_COST,
2020 pjp1.LABOR_BRDN_COST,
2021 pjp1.LABOR_HRS,
2022 pjp1.LABOR_REVENUE,
2023 pjp1.EQUIPMENT_HOURS,
2024 pjp1.BILLABLE_EQUIPMENT_HOURS,
2025 pjp1.SUP_INV_COMMITTED_COST,
2026 pjp1.PO_COMMITTED_COST,
2027 pjp1.PR_COMMITTED_COST,
2028 pjp1.OTH_COMMITTED_COST,
2029 pjp1.ACT_LABOR_HRS,
2030 pjp1.ACT_EQUIP_HRS,
2031 pjp1.ACT_LABOR_BRDN_COST,
2032 pjp1.ACT_EQUIP_BRDN_COST,
2033 pjp1.ACT_BRDN_COST,
2034 pjp1.ACT_RAW_COST,
2035 pjp1.ACT_REVENUE,
2036 pjp1.ACT_LABOR_RAW_COST,
2037 pjp1.ACT_EQUIP_RAW_COST,
2038 pjp1.ETC_LABOR_HRS,
2039 pjp1.ETC_EQUIP_HRS,
2040 pjp1.ETC_LABOR_BRDN_COST,
2041 pjp1.ETC_EQUIP_BRDN_COST,
2042 pjp1.ETC_BRDN_COST,
2043 pjp1.ETC_RAW_COST,
2044 pjp1.ETC_LABOR_RAW_COST,
2045 pjp1.ETC_EQUIP_RAW_COST,
2046 pjp1.CUSTOM1,
2047 pjp1.CUSTOM2,
2048 pjp1.CUSTOM3,
2049 pjp1.CUSTOM4,
2050 pjp1.CUSTOM5,
2051 pjp1.CUSTOM6,
2052 pjp1.CUSTOM7,
2053 pjp1.CUSTOM8,
2054 pjp1.CUSTOM9,
2055 pjp1.CUSTOM10,
2056 pjp1.CUSTOM11,
2057 pjp1.CUSTOM12,
2058 pjp1.CUSTOM13,
2059 pjp1.CUSTOM14,
2060 pjp1.CUSTOM15
2061 from
2062 PJI_FP_AGGR_PJP1 pjp1,
2063 PJI_PJP_WBS_HEADER wbs_hdr,
2064 PA_XBS_DENORM wbs,
2065 PJI_XBS_DENORM prg,
2066 (
2067 select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
2068 from DUAL union all
2069 select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
2070 from DUAL union all
2071 select 'N' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID
2072 from DUAL union all
2073 select 'N' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID
2074 from DUAL union all
2075 select 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
2076 from DUAL union all
2077 select 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
2078 from DUAL union all
2079 select 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID
2080 from DUAL union all
2081 select 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID
2082 from DUAL
2083 ) fin_plan,
2084 (
2085 select 1 WBS_SUP_LEVEL,
2086 1 WBS_SUB_LEVEL,
2087 'PRJ' INVERT_ID
2088 from DUAL
2089 union all
2090 select 1 WBS_SUP_LEVEL,
2091 1 WBS_SUB_LEVEL,
2092 'WBS' INVERT_ID
2093 from DUAL
2094 ) top_slice
2095 where
2096 prg.STRUCT_TYPE = 'PRG' and
2097 prg.SUP_LEVEL = l_level and
2098 prg.SUB_LEVEL = l_level and
2099 wbs.STRUCT_TYPE = 'WBS' and
2100 ((wbs.SUP_LEVEL = 1 and
2101 wbs.SUB_LEVEL = 1) or
2102 (wbs.SUP_LEVEL <> wbs.SUB_LEVEL)) and
2103 wbs.STRUCT_VERSION_ID = prg.SUP_ID and
2104 wbs.SUP_PROJECT_ID = prg.SUP_PROJECT_ID and
2105 pjp1.WORKER_ID = p_worker_id and
2106 pjp1.PRG_LEVEL in (0, l_level) and
2107 pjp1.RBS_AGGR_LEVEL in ('T', 'L') and
2108 pjp1.WBS_ROLLUP_FLAG = 'N' and
2109 pjp1.PRG_ROLLUP_FLAG in ('Y', 'N') and
2110 pjp1.PROJECT_ID = wbs_hdr.PROJECT_ID and
2111 pjp1.PLAN_VERSION_ID = wbs_hdr.PLAN_VERSION_ID and
2112 pjp1.PLAN_TYPE_CODE = wbs_hdr.PLAN_TYPE_CODE and
2113 decode(pjp1.PLAN_VERSION_ID,
2114 -3, pjp1.PLAN_TYPE_ID,
2115 -4, pjp1.PLAN_TYPE_ID,
2116 -1) = decode(pjp1.PLAN_VERSION_ID,
2117 -3, wbs_hdr.PLAN_TYPE_ID,
2118 -4, wbs_hdr.PLAN_TYPE_ID,
2119 -1) and
2120 wbs.STRUCT_VERSION_ID = wbs_hdr.WBS_VERSION_ID and
2121 pjp1.PROJECT_ELEMENT_ID = wbs.SUB_EMT_ID and
2122 wbs_hdr.CB_FLAG = fin_plan.CB (+) and
2123 wbs_hdr.CO_FLAG = fin_plan.CO (+) and
2124 wbs.SUP_LEVEL = top_slice.WBS_SUP_LEVEL (+) and
2125 wbs.SUB_LEVEL <> top_slice.WBS_SUB_LEVEL (+)
2126 union all
2127 select /*+ ordered */
2128 -- get incremental project level amounts from source
2129 to_char(null) LINE_TYPE,
2130 wbs_hdr.WBS_VERSION_ID,
2131 decode(wbs_hdr.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE,
2132 'Y' PUSHUP_FLAG,
2133 decode(pjp1.RBS_AGGR_LEVEL,
2134 'L', 'N',
2135 decode(fin_plan.PLAN_VERSION_ID,
2136 null, 'N', 'Y')) INSERT_FLAG,
2137 pjp1.PROJECT_ID,
2138 pjp1.PROJECT_ORG_ID,
2139 pjp1.PROJECT_ORGANIZATION_ID,
2140 pjp1.PROJECT_ELEMENT_ID,
2141 pjp1.TIME_ID,
2142 pjp1.PERIOD_TYPE_ID,
2143 pjp1.CALENDAR_TYPE,
2144 pjp1.RBS_AGGR_LEVEL,
2145 'Y' WBS_ROLLUP_FLAG,
2146 pjp1.PRG_ROLLUP_FLAG,
2147 pjp1.CURR_RECORD_TYPE_ID,
2148 pjp1.CURRENCY_CODE,
2149 pjp1.RBS_ELEMENT_ID,
2150 pjp1.RBS_VERSION_ID,
2151 decode(wbs_hdr.WP_FLAG,
2152 'N', decode(pjp1.PLAN_VERSION_ID,
2153 -1, pjp1.PLAN_VERSION_ID,
2154 -2, pjp1.PLAN_VERSION_ID,
2155 -3, pjp1.PLAN_VERSION_ID, -- won't exist
2156 -4, pjp1.PLAN_VERSION_ID, -- won't exist
2157 fin_plan.PLAN_VERSION_ID),
2158 pjp1.PLAN_VERSION_ID) PLAN_VERSION_ID,
2159 pjp1.PLAN_TYPE_ID,
2160 pjp1.PLAN_TYPE_CODE,
2161 pjp1.RAW_COST,
2162 pjp1.BRDN_COST,
2163 pjp1.REVENUE,
2164 pjp1.BILL_RAW_COST,
2165 pjp1.BILL_BRDN_COST,
2166 pjp1.BILL_LABOR_RAW_COST,
2167 pjp1.BILL_LABOR_BRDN_COST,
2168 pjp1.BILL_LABOR_HRS,
2169 pjp1.EQUIPMENT_RAW_COST,
2170 pjp1.EQUIPMENT_BRDN_COST,
2171 pjp1.CAPITALIZABLE_RAW_COST,
2172 pjp1.CAPITALIZABLE_BRDN_COST,
2173 pjp1.LABOR_RAW_COST,
2174 pjp1.LABOR_BRDN_COST,
2175 pjp1.LABOR_HRS,
2176 pjp1.LABOR_REVENUE,
2177 pjp1.EQUIPMENT_HOURS,
2178 pjp1.BILLABLE_EQUIPMENT_HOURS,
2179 pjp1.SUP_INV_COMMITTED_COST,
2180 pjp1.PO_COMMITTED_COST,
2181 pjp1.PR_COMMITTED_COST,
2182 pjp1.OTH_COMMITTED_COST,
2183 pjp1.ACT_LABOR_HRS,
2184 pjp1.ACT_EQUIP_HRS,
2185 pjp1.ACT_LABOR_BRDN_COST,
2186 pjp1.ACT_EQUIP_BRDN_COST,
2187 pjp1.ACT_BRDN_COST,
2188 pjp1.ACT_RAW_COST,
2189 pjp1.ACT_REVENUE,
2190 pjp1.ACT_LABOR_RAW_COST,
2191 pjp1.ACT_EQUIP_RAW_COST,
2192 pjp1.ETC_LABOR_HRS,
2193 pjp1.ETC_EQUIP_HRS,
2194 pjp1.ETC_LABOR_BRDN_COST,
2195 pjp1.ETC_EQUIP_BRDN_COST,
2196 pjp1.ETC_BRDN_COST,
2197 pjp1.ETC_RAW_COST,
2198 pjp1.ETC_LABOR_RAW_COST,
2199 pjp1.ETC_EQUIP_RAW_COST,
2200 pjp1.CUSTOM1,
2201 pjp1.CUSTOM2,
2202 pjp1.CUSTOM3,
2203 pjp1.CUSTOM4,
2204 pjp1.CUSTOM5,
2205 pjp1.CUSTOM6,
2206 pjp1.CUSTOM7,
2207 pjp1.CUSTOM8,
2208 pjp1.CUSTOM9,
2209 pjp1.CUSTOM10,
2210 pjp1.CUSTOM11,
2211 pjp1.CUSTOM12,
2212 pjp1.CUSTOM13,
2213 pjp1.CUSTOM14,
2214 pjp1.CUSTOM15
2215 from
2216 PJI_FP_AGGR_PJP1 pjp1,
2217 PJI_PJP_WBS_HEADER wbs_hdr,
2218 PJI_XBS_DENORM prg,
2219 (
2220 select 'Y' CB_FLAG,
2221 'N' CO_FLAG,
2222 -3 PLAN_VERSION_ID
2223 from DUAL union all
2224 select 'N' CB_FLAG,
2225 'Y' CO_FLAG,
2226 -4 PLAN_VERSION_ID
2227 from DUAL union all
2228 select 'Y' CB_FLAG,
2229 'Y' CO_FLAG,
2230 -3 PLAN_VERSION_ID
2231 from DUAL union all
2232 select 'Y' CB_FLAG,
2233 'Y' CO_FLAG,
2234 -4 PLAN_VERSION_ID
2235 from DUAL
2236 ) fin_plan
2237 where
2238 prg.STRUCT_TYPE = 'PRG' and
2239 prg.SUP_LEVEL = l_level and
2240 prg.SUB_LEVEL = l_level and
2241 pjp1.WORKER_ID = p_worker_id and
2242 pjp1.PROJECT_ID = prg.SUP_PROJECT_ID and
2243 pjp1.PROJECT_ELEMENT_ID = prg.SUP_EMT_ID and
2244 pjp1.PRG_LEVEL = 0 and
2245 pjp1.RBS_AGGR_LEVEL in ('T', 'L') and
2246 pjp1.WBS_ROLLUP_FLAG = 'N' and
2247 pjp1.PRG_ROLLUP_FLAG = 'N' and
2248 wbs_hdr.PROJECT_ID = pjp1.PROJECT_ID and
2249 wbs_hdr.PLAN_VERSION_ID = pjp1.PLAN_VERSION_ID and
2250 wbs_hdr.PLAN_TYPE_CODE = pjp1.PLAN_TYPE_CODE and
2251 decode(wbs_hdr.WP_FLAG,
2252 'N', decode(pjp1.PLAN_VERSION_ID,
2253 -1, 'Y',
2254 -2, 'Y',
2255 -3, 'Y', -- won't exist
2256 -4, 'Y', -- won't exist
2257 decode(wbs_hdr.CB_FLAG || '_' ||
2258 wbs_hdr.CO_FLAG,
2259 'Y_Y', 'Y',
2260 'N_Y', 'Y',
2261 'Y_N', 'Y',
2262 'N')),
2263 'Y') = 'Y' and
2264 wbs_hdr.WBS_VERSION_ID = prg.SUP_ID and
2265 wbs_hdr.CB_FLAG = fin_plan.CB_FLAG (+) and
2266 wbs_hdr.CO_FLAG = fin_plan.CO_FLAG (+)
2267 ) pjp,
2268 (
2269 select /*+ index(prg PJI_XBS_DENORM_N3)
2270 index(map PA_PROJECTS_U1) */
2271 prg.SUP_PROJECT_ID,
2272 map.ORG_ID SUP_PROJECT_ORG_ID,
2273 map.CARRYING_OUT_ORGANIZATION_ID SUP_PROJECT_ORGANIZATION_ID,
2274 prg.SUP_ID,
2275 prg.SUP_EMT_ID,
2276 prg.SUP_LEVEL,
2277 prg.SUB_ID,
2278 prg.SUB_EMT_ID,
2279 prg.SUB_ROLLUP_ID,
2280 invert.INVERT_VALUE RELATIONSHIP_TYPE,
2281 decode(prg.RELATIONSHIP_TYPE,
2282 'LW', 'Y',
2283 'LF', 'N') WP_FLAG,
2284 'Y' PUSHUP_FLAG
2285 from
2286 PJI_XBS_DENORM prg,
2287 PA_PROJECTS_ALL map,
2288 (
2289 select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
2290 select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
2291 select 'A' INVERT_ID, 'LF' INVERT_VALUE from dual union all
2292 select 'A' INVERT_ID, 'LW' INVERT_VALUE from dual
2293 ) invert
2294 where
2295 prg.STRUCT_TYPE = 'PRG' and
2296 prg.SUB_ROLLUP_ID is not null and
2297 prg.SUB_LEVEL = l_level and
2298 -- map.WORKER_ID = p_worker_id and
2299 map.PROJECT_ID = prg.SUP_PROJECT_ID and
2300 decode(prg.SUB_LEVEL,
2301 prg.SUP_LEVEL, 'A',
2302 prg.RELATIONSHIP_TYPE) = invert.INVERT_ID
2303 ) prg,
2304 PJI_PJP_WBS_HEADER wbs_hdr,
2305 PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
2306 PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
2307 PA_PROJ_WORKPLAN_ATTR sup_wpa
2308 where
2309 pjp.PROJECT_ID = sub_ver.PROJECT_ID (+) and
2310 pjp.WBS_VERSION_ID = sub_ver.ELEMENT_VERSION_ID (+) and
2311 'STRUCTURE_PUBLISHED' = sub_ver.STATUS_CODE (+) and
2312 pjp.WBS_VERSION_ID = prg.SUB_ID (+) and
2313 pjp.RELATIONSHIP_TYPE = prg.RELATIONSHIP_TYPE (+) and
2314 pjp.PUSHUP_FLAG = prg.PUSHUP_FLAG (+) and
2315 prg.SUP_PROJECT_ID = wbs_hdr.PROJECT_ID (+) and
2316 prg.SUP_ID = wbs_hdr.WBS_VERSION_ID (+) and
2317 prg.WP_FLAG = wbs_hdr.WP_FLAG (+) and
2318 'Y' = wbs_hdr.WP_FLAG (+) and
2319 wbs_hdr.PROJECT_ID = sup_ver.PROJECT_ID (+) and
2320 wbs_hdr.WBS_VERSION_ID = sup_ver.ELEMENT_VERSION_ID (+) and
2321 'STRUCTURE_PUBLISHED' = sup_ver.STATUS_CODE (+) and
2322 'Y' = sup_ver.LATEST_EFF_PUBLISHED_FLAG (+) and
2323 prg.SUP_EMT_ID = sup_wpa.PROJ_ELEMENT_ID (+)
2324 group by
2325 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2326 pjp.INSERT_FLAG, 'Y'),
2327 pjp.RELATIONSHIP_TYPE,
2328 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2329 null, sub_ver.STATUS_CODE),
2330 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2331 null, sup_ver.STATUS_CODE),
2332 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2333 null, sup_wpa.WP_ENABLE_VERSION_FLAG),
2334 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2335 null, decode(pjp.PLAN_VERSION_ID,
2336 -1, prg.SUP_ID,
2337 -3, prg.SUP_ID,
2338 -4, prg.SUP_ID,
2339 null)),
2340 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2341 null, decode(pjp.PLAN_VERSION_ID,
2342 -1, prg.SUP_EMT_ID,
2343 -3, prg.SUP_EMT_ID,
2344 -4, prg.SUP_EMT_ID,
2345 null)),
2346 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2347 null, decode(pjp.PLAN_VERSION_ID,
2348 -1, prg.WP_FLAG,
2349 -3, prg.WP_FLAG,
2350 -4, prg.WP_FLAG,
2351 null)),
2352 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2353 l_level, prg.SUP_LEVEL),
2354 pjp.LINE_TYPE,
2355 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2356 pjp.PROJECT_ID, prg.SUP_PROJECT_ID),
2357 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2358 pjp.PROJECT_ORG_ID,
2359 prg.SUP_PROJECT_ORG_ID),
2360 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2361 pjp.PROJECT_ORGANIZATION_ID,
2362 prg.SUP_PROJECT_ORGANIZATION_ID),
2363 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2364 pjp.PROJECT_ELEMENT_ID,
2365 prg.SUB_ROLLUP_ID),
2366 pjp.TIME_ID,
2367 pjp.PERIOD_TYPE_ID,
2368 pjp.CALENDAR_TYPE,
2369 pjp.RBS_AGGR_LEVEL,
2370 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2371 pjp.WBS_ROLLUP_FLAG, 'N'),
2372 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2373 pjp.PRG_ROLLUP_FLAG, 'Y'),
2374 pjp.CURR_RECORD_TYPE_ID,
2375 pjp.CURRENCY_CODE,
2376 pjp.RBS_ELEMENT_ID,
2377 pjp.RBS_VERSION_ID,
2378 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2379 pjp.PLAN_VERSION_ID,
2380 decode(pjp.PLAN_VERSION_ID,
2381 -1, pjp.PLAN_VERSION_ID,
2382 -2, pjp.PLAN_VERSION_ID,
2383 -3, pjp.PLAN_VERSION_ID,
2384 -4, pjp.PLAN_VERSION_ID,
2385 wbs_hdr.PLAN_VERSION_ID)),
2386 decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
2387 pjp.PLAN_TYPE_ID,
2388 decode(pjp.PLAN_VERSION_ID,
2389 -1, pjp.PLAN_TYPE_ID,
2390 -2, pjp.PLAN_TYPE_ID,
2391 -3, pjp.PLAN_TYPE_ID,
2392 -4, pjp.PLAN_TYPE_ID,
2393 wbs_hdr.PLAN_TYPE_ID)),
2394 pjp.PLAN_TYPE_CODE
2395 ) pjp1_i,
2396 PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
2397 PA_PROJ_WORKPLAN_ATTR sup_wpa
2398 where
2399 pjp1_i.INSERT_FLAG = 'Y' and
2400 pjp1_i.PROJECT_ID = sup_fin_ver.PROJECT_ID (+) and
2401 pjp1_i.SUP_ID = sup_fin_ver.ELEMENT_VERSION_ID (+) and
2402 'STRUCTURE_WORKING' = sup_fin_ver.STATUS_CODE (+) and
2403 pjp1_i.SUP_EMT_ID = sup_wpa.PROJ_ELEMENT_ID (+) and
2404 'N' = sup_wpa.WP_ENABLE_VERSION_FLAG (+) and
2405 (pjp1_i.SUP_ID is null or
2406 (pjp1_i.SUP_ID is not null and
2407 (sup_fin_ver.PROJECT_ID is not null or
2408 sup_wpa.PROJ_ELEMENT_ID is not null)));
2409
2410 end loop;
2411
2412
2413 -- pji_utils.write2log('After Rollup_fpr_wbs');
2414
2415 --below call is required for updating
2416 --the date columns on the wbs header
2417
2418 UPDATE /*+ index(whdr,PJI_PJP_WBS_HEADER_N1) */
2419 PJI_PJP_WBS_HEADER whdr
2420 SET ( MIN_TXN_DATE
2421 , MAX_TXN_DATE
2422 , LAST_UPDATE_DATE
2423 , LAST_UPDATED_BY
2424 , LAST_UPDATE_LOGIN
2425 ) = (
2426 SELECT MIN(LEAST(cal.start_date, NVL(whdr.min_txn_date, cal.start_date))) start_date
2427 , MAX(GREATEST(cal.end_date, NVL(whdr.max_txn_date, cal.end_date))) end_date
2428 , l_last_update_date
2429 , l_last_updated_by
2430 , l_last_update_login
2431 FROM PJI_FP_AGGR_PJP1 pjp1
2432 , pji_time_cal_period_v cal
2433 WHERE
2434 pjp1.worker_id = p_worker_id
2435 AND pjp1.plan_version_id = whdr.plan_version_id
2436 AND pjp1.project_id = whdr.project_id
2437 AND pjp1.plan_type_id = whdr.plan_type_id
2438 AND pjp1.time_id = cal.cal_period_id
2439 AND pjp1.calendar_type IN ('P', 'G') -- Non time ph and ent cals don't need to be considered.
2440 )
2441 WHERE exists (select 1 from pji_fp_aggr_pjp1 ver where worker_id = p_worker_id
2442 and ver.project_id = whdr.project_id
2443 and ver.plan_version_id = whdr.plan_version_id
2444 and ver.plan_type_id = whdr.plan_type_id);
2445
2446 -- pji_utils.write2log('Updated records in wbs_header'||sql%rowcount);
2447
2448
2449 INSERT INTO pji_fp_xbs_accum_f fact
2450 (
2451 PROJECT_ID
2452 , PROJECT_ORG_ID
2453 , PROJECT_ORGANIZATION_ID
2454 , PROJECT_ELEMENT_ID
2455 , TIME_ID
2456 , PERIOD_TYPE_ID
2457 , CALENDAR_TYPE
2458 , RBS_AGGR_LEVEL
2459 , WBS_ROLLUP_FLAG
2460 , PRG_ROLLUP_FLAG
2461 , CURR_RECORD_TYPE_ID
2462 , CURRENCY_CODE
2463 , RBS_ELEMENT_ID
2464 , RBS_VERSION_ID
2465 , PLAN_VERSION_ID
2466 , PLAN_TYPE_ID
2467 , LAST_UPDATE_DATE
2468 , LAST_UPDATED_BY
2469 , CREATION_DATE
2470 , CREATED_BY
2471 , LAST_UPDATE_LOGIN
2472 , RAW_COST
2473 , BRDN_COST
2474 , REVENUE
2475 , BILL_RAW_COST
2476 , BILL_BRDN_COST
2477 , BILL_LABOR_RAW_COST
2478 , BILL_LABOR_BRDN_COST
2479 , BILL_LABOR_HRS
2480 , EQUIPMENT_RAW_COST
2481 , EQUIPMENT_BRDN_COST
2482 , CAPITALIZABLE_RAW_COST
2483 , CAPITALIZABLE_BRDN_COST
2484 , LABOR_RAW_COST
2485 , LABOR_BRDN_COST
2486 , LABOR_HRS
2487 , LABOR_REVENUE
2488 , EQUIPMENT_HOURS
2489 , BILLABLE_EQUIPMENT_HOURS
2490 , SUP_INV_COMMITTED_COST
2491 , PO_COMMITTED_COST
2492 , PR_COMMITTED_COST
2493 , OTH_COMMITTED_COST
2494 , ACT_LABOR_HRS
2495 , ACT_EQUIP_HRS
2496 , ACT_LABOR_BRDN_COST
2497 , ACT_EQUIP_BRDN_COST
2498 , ACT_BRDN_COST
2499 , ACT_RAW_COST
2500 , ACT_REVENUE
2501 , ACT_LABOR_RAW_COST
2502 , ACT_EQUIP_RAW_COST
2503 , ETC_LABOR_HRS
2504 , ETC_EQUIP_HRS
2505 , ETC_LABOR_BRDN_COST
2506 , ETC_EQUIP_BRDN_COST
2507 , ETC_BRDN_COST
2508 , ETC_RAW_COST
2509 , ETC_LABOR_RAW_COST
2510 , ETC_EQUIP_RAW_COST
2511 , CUSTOM1
2512 , CUSTOM2
2513 , CUSTOM3
2514 , CUSTOM4
2515 , CUSTOM5
2516 , CUSTOM6
2517 , CUSTOM7
2518 , CUSTOM8
2519 , CUSTOM9
2520 , CUSTOM10
2521 , CUSTOM11
2522 , CUSTOM12
2523 , CUSTOM13
2524 , CUSTOM14
2525 , CUSTOM15
2526 , PLAN_TYPE_CODE
2527 )
2528 (
2529 SELECT
2530 tmp.PROJECT_ID
2531 , tmp.PROJECT_ORG_ID
2532 , tmp.PROJECT_ORGANIZATION_ID
2533 , tmp.PROJECT_ELEMENT_ID
2534 , tmp.TIME_ID
2535 , tmp.PERIOD_TYPE_ID
2536 , tmp.CALENDAR_TYPE
2537 , tmp.RBS_AGGR_LEVEL
2538 , tmp.WBS_ROLLUP_FLAG
2539 , tmp.PRG_ROLLUP_FLAG
2540 , tmp.CURR_RECORD_TYPE_ID
2541 , tmp.CURRENCY_CODE
2542 , tmp.RBS_ELEMENT_ID
2543 , tmp.RBS_VERSION_ID
2544 , ver3.PLAN_VERSION_ID
2545 , tmp.PLAN_TYPE_ID
2546 , l_last_update_date
2547 , l_last_updated_by
2548 , l_creation_date
2549 , l_created_by
2550 , l_last_update_login
2551 , RAW_COST
2552 , BRDN_COST
2553 , REVENUE
2554 , BILL_RAW_COST
2555 , BILL_BRDN_COST
2556 , BILL_LABOR_RAW_COST
2557 , BILL_LABOR_BRDN_COST
2558 , BILL_LABOR_HRS
2559 , EQUIPMENT_RAW_COST
2560 , EQUIPMENT_BRDN_COST
2561 , CAPITALIZABLE_RAW_COST
2562 , CAPITALIZABLE_BRDN_COST
2563 , LABOR_RAW_COST
2564 , LABOR_BRDN_COST
2565 , LABOR_HRS
2566 , LABOR_REVENUE
2567 , EQUIPMENT_HOURS
2568 , BILLABLE_EQUIPMENT_HOURS
2569 , SUP_INV_COMMITTED_COST
2570 , PO_COMMITTED_COST
2571 , PR_COMMITTED_COST
2572 , OTH_COMMITTED_COST
2573 , ACT_LABOR_HRS
2574 , ACT_EQUIP_HRS
2575 , ACT_LABOR_BRDN_COST
2576 , ACT_EQUIP_BRDN_COST
2577 , ACT_BRDN_COST
2578 , ACT_RAW_COST
2579 , ACT_REVENUE
2580 , ACT_LABOR_RAW_COST
2581 , ACT_EQUIP_RAW_COST
2582 , DECODE ( ver3.wp_flag
2583 , 'Y'
2584 , DECODE(TO_CHAR(tmp.ETC_LABOR_HRS) -- For Workplan
2585 , NULL
2586 , NVL(tmp.labor_hrs, 0)
2587 , NVL(tmp.ETC_LABOR_HRS, 0)
2588 )
2589 , NVL(tmp.ETC_LABOR_HRS, 0)
2590 ) ETC_LABOR_HRS
2591 , DECODE ( ver3.wp_flag
2592 , 'Y'
2593 , DECODE(TO_CHAR(tmp.ETC_EQUIP_HRS)
2594 , NULL
2595 , NVL(tmp.EQUIPMENT_hours, 0)
2596 , NVL(tmp.ETC_EQUIP_HRS, 0)
2597 )
2598 , NVL(tmp.ETC_EQUIP_HRS, 0)
2599 ) ETC_EQUIP_HRS
2600 , DECODE ( ver3.wp_flag
2601 , 'Y'
2602 , DECODE(TO_CHAR(tmp.ETC_LABOR_BRDN_COST)
2603 , NULL
2604 , NVL(tmp.labor_BRDN_COST, 0)
2605 , NVL(tmp.ETC_LABOR_BRDN_COST, 0)
2606 )
2607 , NVL(tmp.ETC_LABOR_BRDN_COST, 0)
2608 ) ETC_LABOR_BRDN_COST
2609 , DECODE ( ver3.wp_flag
2610 , 'Y'
2611 , DECODE(TO_CHAR(tmp.ETC_EQUIP_BRDN_COST)
2612 , NULL
2613 , NVL(tmp.EQUIPment_BRDN_COST, 0)
2614 , NVL(tmp.ETC_equip_BRDN_COST, 0)
2615 )
2616 , NVL(tmp.ETC_EQUIP_BRDN_COST, 0)
2617 ) ETC_equip_BRDN_COST
2618 , DECODE ( ver3.wp_flag
2619 , 'Y'
2620 , DECODE(TO_CHAR(tmp.ETC_BRDN_COST)
2621 , NULL
2622 , NVL(tmp.BRDN_COST, 0)
2623 , NVL(tmp.ETC_BRDN_COST, 0)
2624 )
2625 , NVL(tmp.ETC_BRDN_COST, 0)
2626 ) ETC_BRDN_COST
2627 , DECODE ( ver3.wp_flag
2628 , 'Y'
2629 , DECODE(TO_CHAR(tmp.ETC_raw_COST)
2630 , NULL
2631 , NVL(tmp.raw_COST, 0)
2632 , NVL(tmp.ETC_raw_COST, 0)
2633 )
2634 , NVL(tmp.ETC_raw_COST, 0)
2635 ) ETC_raw_COST
2636 , DECODE ( ver3.wp_flag
2637 , 'Y'
2638 , DECODE(TO_CHAR(tmp.ETC_labor_raw_COST)
2639 , NULL
2640 , NVL(tmp.labor_raw_COST, 0)
2641 , NVL(tmp.ETC_labor_raw_COST, 0)
2642 )
2643 , NVL(tmp.ETC_labor_raw_COST, 0)
2644 ) ETC_labor_raw_COST
2645 , DECODE ( ver3.wp_flag
2646 , 'Y'
2647 , DECODE(TO_CHAR(tmp.ETC_equip_raw_COST)
2648 , NULL
2649 , NVL(tmp.equipment_raw_COST, 0)
2650 , NVL(tmp.ETC_equip_raw_COST, 0)
2651 )
2652 , NVL(tmp.ETC_equip_raw_COST, 0)
2653 ) ETC_equip_raw_COST
2654 , CUSTOM1
2655 , CUSTOM2
2656 , CUSTOM3
2657 , CUSTOM4
2658 , CUSTOM5
2659 , CUSTOM6
2660 , CUSTOM7
2661 , CUSTOM8
2662 , CUSTOM9
2663 , CUSTOM10
2664 , CUSTOM11
2665 , CUSTOM12
2666 , CUSTOM13
2667 , CUSTOM14
2668 , CUSTOM15
2669 , tmp.plan_type_code
2670 FROM pji_fp_aggr_pjp1 tmp
2671 , pji_pjp_wbs_header ver3
2672 WHERE 1 = 1
2673 AND ver3.plan_version_id = tmp.plan_version_id
2674 AND ver3.plan_type_code = tmp.plan_type_code /* 4471527 */
2675 AND tmp.project_id = ver3.project_id -- use index.
2676 AND tmp.plan_type_id = NVL(ver3.plan_type_id, -1)
2677 AND tmp.plan_version_id in (-3, -4)
2678 AND tmp.worker_id = p_worker_id
2679 );
2680
2681 -- pji_utils.write2log('Inserted records into fact'||sql%rowcount);
2682
2683 DELETE pa_pji_proj_events_log log
2684 where log.event_type = 'PLANTYPE_UPG'
2685 and exists (select 1 from pji_fm_extr_plnver4 ver
2686 where ver.worker_id = p_worker_id and
2687 to_char(ver.project_id) = log.event_object);
2688
2689
2690 --pji_utils.write2log('Deleted records from pa_pji_proj_events_log'||sql%rowcount);
2691
2692 DELETE FROM pji_fm_extr_plnver4 where worker_id = p_worker_id;
2693
2694 --pji_utils.write2log('Deleted records from pji_fm_extr_plnver4'||sql%rowcount);
2695
2696 DELETE FROM pji_fp_aggr_pjp1 where worker_id = p_worker_id;
2697
2698 --pji_utils.write2log('Deleted records from pji_fp_aggr_pjp1'||sql%rowcount);
2699
2700 end if;
2701
2702 Begin
2703 select 1 into l_count from dual
2704 where exists ( select event_type
2705 from PA_PJI_PROJ_EVENTS_LOG elog
2706 where elog.event_type = 'PLANTYPE_UPG');
2707 exception when NO_DATA_FOUND then
2708 PJI_UTILS.SET_PARAMETER ('PJI_PTC_UPGRADE', 'C');
2709 end;
2710
2711 --dbms_output.put_line('Script end time..'||to_char(sysdate,'MM/DD/YYYY HH:MI:SS'));
2712 /* End of processing for all -3,-4 plans with plan_type=COST_AND_REV_SEP */
2713 COMMIT;
2714
2715 End;
2716
2717 -- -----------------------------------------------------
2718 -- procedure INIT_PROCESS
2719 --
2720 -- History
2721 -- 19-MAR-2004 SVERMETT Created
2722 --
2723 -- Internal PJP Summarization API.
2724 --
2725 -- -----------------------------------------------------
2726 procedure INIT_PROCESS(
2727 p_worker_id in out nocopy number,
2728 p_run_mode in varchar2,
2729 p_operating_unit in number default null,
2730 p_project_type in varchar2 default null,
2731 p_project_organization_id in number default null,
2732 p_from_project in varchar2 default null,
2733 p_to_project in varchar2 default null,
2734 p_plan_type_id in number default null,
2735 p_rbs_header_id in number default null,
2736 p_only_pt_projects_flag in varchar2 default null,
2737 p_transaction_type in varchar2 default null, -- Bug#5099574 - New parameter for Partial Refresh
2738 p_plan_versions in varchar2 default null -- Bug#5099574 - New parameter for Partial Refresh
2739 ) is
2740
2741 cursor lock_headers (p_worker_id in number) is
2742 select
2743 wbs_hdr.ROWID HDR_ROWID
2744 from
2745 PJI_PJP_PROJ_BATCH_MAP map,
2746 PJI_PJP_WBS_HEADER wbs_hdr
2747 where
2748 map.WORKER_ID = p_worker_id and
2749 wbs_hdr.PROJECT_ID = map.PROJECT_ID and
2750 wbs_hdr.PLAN_VERSION_ID = -1
2751 for update;
2752
2753 l_process varchar2(30);
2754 l_extraction_type varchar2(30);
2755
2756 l_preload varchar2(30);
2757
2758 l_settings_proj_perf_flag varchar2(1);
2759 l_type number;
2760 l_worker_id number;
2761 l_incomplete_partial_count number;
2762 l_failed_process_count number;
2763 l_count number;
2764 l_rbs_version_id number := null;
2765
2766 l_from_project_num varchar2(25);
2767 l_to_project_num varchar2(25);
2768 l_project_num varchar2(25);
2769 p_from_project_id number ;
2770 p_to_project_id number ;
2771 l_newline varchar2(10) := '
2772 ';
2773 l_refresh_code number := null; -- Bug#5099574 - New parameter for Partial Refresh
2774 l_prg_exists varchar2(25);
2775 l_rbs_exists varchar2(1):='N' ;
2776 l_existing_worker varchar2(1);
2777 request_id_table SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2778 prog_name_table SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
2779 begin
2780
2781
2782 if (PJI_UTILS.GET_PARAMETER('PJI_FPM_UPGRADE') = 'P') then
2783 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_FPM_UPG_RUN');
2784 dbms_standard.raise_application_error(-20010, FND_MESSAGE.GET);
2785 end if;
2786 /*Check for from project and to project based on the run , idea is to recreate the same check which was done thru mandatory parameter*/
2787 if (p_operating_unit is null and p_project_type is null and p_project_organization_id is null and
2788 p_from_project is null and p_to_project is null and p_run_mode <> 'R') then
2789 FND_MESSAGE.SET_NAME('PJI', 'PJI_NO_PARAMETER');
2790 dbms_standard.raise_application_error(-20090, FND_MESSAGE.GET);
2791 end if;
2792
2793 if p_from_project > p_to_project then
2794 FND_MESSAGE.SET_NAME('PJI', 'PJI_INVALID_RANGE');
2795 dbms_standard.raise_application_error(-20091, FND_MESSAGE.GET);
2796 end if;
2797
2798 IF p_from_project is not null
2799 or p_to_project is not null then
2800 select min(segment1) ,max(segment1)
2801 into l_from_project_num, l_to_project_num
2802 from pa_projects_all
2803 where segment1 between nvl(p_from_project,segment1) and nvl(p_to_project,segment1);
2804 END if;
2805 /* Get the Project Ids ,this is required to keep the impact minimum , these values will be updated in pji_system_parameters Table */
2806 IF l_from_project_num is not null and p_from_project is not null THEN
2807 select project_id
2808 into p_from_project_id
2809 from pa_projects_all
2810 where segment1= l_from_project_num;
2811 else
2812 p_from_project_id:=-1;
2813 END IF;
2814 IF l_to_project_num is not null and p_to_project is not null THEN
2815 select project_id
2816 into p_to_project_id
2817 from pa_projects_all
2818 where segment1= l_to_project_num;
2819 else
2820 p_to_project_id:=-1;
2821 END IF;
2822
2823 select
2824 nvl(CONFIG_PROJ_PERF_FLAG, 'N')
2825 into
2826 l_settings_proj_perf_flag
2827 from
2828 PJI_SYSTEM_SETTINGS;
2829
2830 if (l_settings_proj_perf_flag = 'N') then
2831 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_NOT_ENABLED');
2832 dbms_standard.raise_application_error(-20010, FND_MESSAGE.GET);
2833 end if;
2834
2835 lock table PJI_PJP_PROJ_BATCH_MAP in exclusive mode;
2836
2837 if (p_run_mode = 'F') then
2838 l_extraction_type := 'FULL';
2839 elsif (p_run_mode = 'I' or
2840 p_run_mode = 'NO_PRELOAD') then
2841 l_extraction_type := 'INCREMENTAL';
2842 elsif (p_run_mode = 'P') then
2843 l_extraction_type := 'PARTIAL';
2844 elsif (p_run_mode = 'R') then
2845 l_extraction_type := 'RBS';
2846 else
2847 commit;
2848 dbms_standard.raise_application_error(-20010, 'Invalid run type');
2849 end if;
2850
2851 p_worker_id := 0;
2852 l_type := 0;
2853 l_worker_id := 0;
2854 l_incomplete_partial_count := 0;
2855 l_failed_process_count := 0;
2856 l_existing_worker := 'N'; -- To check the resubmit of failed run Bug 5057835
2857
2858 while l_worker_id < PJI_PJP_SUM_MAIN.g_parallel_processes loop
2859
2860 l_worker_id := l_worker_id + 1;
2861
2862 l_process := g_process || l_worker_id;
2863
2864 l_preload := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,'PRELOAD');
2865
2866 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2867 (l_process, l_process) is null) then
2868
2869 if (p_worker_id = 0) then
2870 p_worker_id := l_worker_id;
2871 end if;
2872
2873 elsif (not PJI_PJP_SUM_MAIN.WORKER_STATUS(l_worker_id, 'RUNNING') and
2874 ((l_extraction_type = 'FULL' and
2875 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2876 (l_process, 'EXTRACTION_TYPE') = 'FULL') or
2877 (l_extraction_type = 'INCREMENTAL' and
2878 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2879 (l_process, 'EXTRACTION_TYPE') = 'FULL') or
2880 (l_extraction_type = 'INCREMENTAL' and
2881 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2882 (l_process, 'EXTRACTION_TYPE') = 'INCREMENTAL') or
2883 (l_extraction_type = 'PARTIAL' and
2884 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2885 (l_process, 'EXTRACTION_TYPE') = 'PARTIAL') or
2886 (l_extraction_type = 'RBS' and
2887 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2888 (l_process, 'EXTRACTION_TYPE') = 'RBS')) and
2889 nvl(p_operating_unit, -1) =
2890 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2891 'PROJECT_OPERATING_UNIT') and
2892 nvl(p_project_type, 'PJI$NULL') =
2893 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2894 'PROJECT_TYPE') and
2895 nvl(p_project_organization_id, -1) =
2896 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2897 'PROJECT_ORGANIZATION_ID') and
2898 nvl(p_from_project, 'PJI$NULL') =
2899 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2900 'FROM_PROJECT') and
2901 nvl(p_to_project, 'PJI$NULL') =
2902 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2903 'TO_PROJECT') and
2904 nvl(p_plan_type_id, -1) =
2905 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2906 'PLAN_TYPE_ID') and
2907 nvl(p_rbs_header_id, -1) =
2908 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2909 'RBS_HEADER_ID') and
2910 nvl(p_transaction_type, 'PJI$NULL') =
2911 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, -- Bug#5099574
2912 'TRANSACTION_TYPE') and
2913 nvl(p_plan_versions, 'PJI$NULL') =
2914 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, -- Bug#5099574
2915 'PLAN_VERSION') and
2916 nvl(p_only_pt_projects_flag, 'PJI$NULL') =
2917 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2918 'ONLY_PT_PROJECTS_FLAG')) then
2919
2920 p_worker_id := l_worker_id;
2921 l_existing_worker := 'Y'; -- To check the resubmit of failed run Bug 5057835
2922 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2923 'EXTRACTION_TYPE')
2924 = 'PARTIAL') then
2925 l_type := 1;
2926 l_incomplete_partial_count := l_incomplete_partial_count + 1;
2927 else
2928 l_type := 2;
2929 l_failed_process_count := l_failed_process_count + 1;
2930 end if;
2931
2932 elsif (not PJI_PJP_SUM_MAIN.WORKER_STATUS(l_worker_id, 'RUNNING')) then
2933
2934 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2935 'EXTRACTION_TYPE')
2936 = 'PARTIAL') then
2937 l_incomplete_partial_count := l_incomplete_partial_count + 1;
2938 else
2939 l_failed_process_count := l_failed_process_count + 1;
2940 end if;
2941
2942 end if;
2943
2944 end loop;
2945
2946 -- if (p_worker_id > 0 and
2947 -- ((l_incomplete_partial_count + l_failed_process_count > 0 and l_type = 0) or
2948 -- (l_type = 1 and l_failed_process_count > 0)
2949 -- )
2950 -- ) then
2951 -- OUTPUT_FAILED_RUNS;
2952 -- elsif (p_worker_id = 0) then
2953 if (p_worker_id = 0) then
2954 rollback;
2955 OUTPUT_FAILED_RUNS;
2956 FND_MESSAGE.SET_NAME('PJI', 'PJI_NO_PRC_AVAILABLE');
2957 dbms_standard.raise_application_error(-20030, FND_MESSAGE.GET);
2958 end if;
2959
2960 l_process := g_process || p_worker_id;
2961
2962 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process, l_process,
2963 FND_GLOBAL.CONC_REQUEST_ID);
2964
2965 PJI_PJP_EXTRACTION_UTILS.SET_WORKER_ID(p_worker_id);
2966
2967 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process, 'PROCESS_RUNNING', 'Y');
2968 -- Putting this insert here, Config Hist in case of resubmit of failed run Bug 5057835
2969 insert into PJI_SYSTEM_CONFIG_HIST
2970 (
2971 REQUEST_ID,
2972 USER_NAME,
2973 PROCESS_NAME,
2974 RUN_TYPE,
2975 PARAMETERS,
2976 CONFIG_PROJ_PERF_FLAG,
2977 CONFIG_COST_FLAG,
2978 CONFIG_PROFIT_FLAG,
2979 CONFIG_UTIL_FLAG,
2980 START_DATE,
2981 END_DATE,
2982 COMPLETION_TEXT
2983 )
2984 select
2985 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
2986 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
2987 l_process PROCESS_NAME,
2988 l_extraction_type RUN_TYPE,
2989 substr(p_run_mode || ', ' ||
2990 to_char(p_operating_unit) || ', ' ||
2991 p_project_type || ', ' ||
2992 to_char(p_project_organization_id) || ', ' ||
2993 p_from_project || ', ' ||
2994 p_to_project || ', ' ||
2995 to_char(p_plan_type_id) || ', ' ||
2996 to_char(p_rbs_header_id) || ', ' ||
2997 p_transaction_type || ', ' ||
2998 p_plan_versions || ', ' ||
2999 p_only_pt_projects_flag, 1, 240) PARAMETERS,
3000 null CONFIG_PROJ_PERF_FLAG,
3001 null CONFIG_COST_FLAG,
3002 null CONFIG_PROFIT_FLAG,
3003 null CONFIG_UTIL_FLAG,
3004 sysdate START_DATE,
3005 null END_DATE,
3006 null COMPLETION_TEXT
3007 from
3008 dual;
3009
3010 -- If this is the resubmit of already existing failed run then Bug 5057835
3011 if l_existing_worker = 'Y' and
3012 (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_PJP_SUM_MAIN.INIT_PROCESS(p_worker_id, p_run_mode);')) then
3013 commit; -- To release lock/ stamp parameters / config hist
3014 return;
3015 end if;
3016
3017 PJI_PROCESS_UTIL.REFRESH_STEP_TABLE;
3018
3019 savepoint no_need_to_run_step; --Bug#5171542 Moving up, preload should not set when rollback
3020
3021 if (p_run_mode = 'NO_PRELOAD') then
3022 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process, 'PRELOAD', 'N');
3023 end if;
3024
3025 l_preload := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'PRELOAD');
3026
3027 PJI_PJP_EXTRACTION_UTILS.UPDATE_EXTR_SCOPE;
3028
3029 -- Moving down the query for l_rbs_ for the stamping of parameters Bug 5057835
3030
3031 if (l_extraction_type = 'FULL' or
3032 l_extraction_type = 'INCREMENTAL' or
3033 l_extraction_type = 'PARTIAL') then
3034
3035 -- identify all projects that fit the concurrent program parameters
3036 /* No Need to check in the table PA_PROJ_ELEMENT_VERSIONS Bug 5057835
3037 begin -- bug 5356051
3038
3039 select 'Y'
3040 into l_prg_exists
3041 from DUAL
3042 where exists (select 1
3043 from PA_PROJ_ELEMENT_VERSIONS proj
3044 where proj.OBJECT_TYPE = 'PA_STRUCTURES' and
3045 proj.PRG_GROUP is not null and
3046 ROWNUM = 1);
3047
3048 exception when NO_DATA_FOUND then
3049
3050 l_prg_exists := 'N';
3051
3052 end;
3053
3054 if (l_prg_exists = 'N') then
3055 */
3056 insert into PJI_PJP_PROJ_BATCH_MAP
3057 (
3058 WORKER_ID,
3059 PROJECT_ID,
3060 PJI_PROJECT_STATUS,
3061 EXTRACTION_TYPE,
3062 EXTRACTION_STATUS,
3063 PROJECT_TYPE,
3064 PROJECT_ORG_ID,
3065 PROJECT_ORGANIZATION_ID,
3066 PROJECT_TYPE_CLASS,
3067 PRJ_CURRENCY_CODE,
3068 PROJECT_ACTIVE_FLAG
3069 )
3070 select
3071 p_worker_id,
3072 status.PROJECT_ID,
3073 null PJI_PROJECT_STATUS,
3074 null EXTRACTION_TYPE,
3075 status.EXTRACTION_STATUS,
3076 prj.PROJECT_TYPE,
3077 prj.ORG_ID PROJECT_ORG_ID,
3078 status.PROJECT_ORGANIZATION_ID,
3079 status.PROJECT_TYPE_CLASS,
3080 prj.PROJECT_CURRENCY_CODE,
3081 'Y' PROJECT_ACTIVE_FLAG
3082 /* Processing is not depending on Project status Bug 5057835
3083 decode(active_projects.PROJECT_STATUS_CODE,
3084 null, 'N', 'Y') PROJECT_ACTIVE_FLAG */
3085 from
3086 PJI_PJP_PROJ_EXTR_STATUS status,
3087 PA_PROJECTS_ALL prj /*,
3088 ( Processing is not depending on Project status Bug 5057835
3089 select
3090 distinct
3091 stat.PROJECT_STATUS_CODE
3092 from
3093 PA_PROJECT_STATUSES stat
3094 where
3095 stat.STATUS_TYPE = 'PROJECT' and
3096 stat.PROJECT_SYSTEM_STATUS_CODE not in ('CLOSED',
3097 'PENDING_CLOSE',
3098 'PENDING_PURGE',
3099 'PURGED')
3100 ) active_projects */
3101 where
3102 status.PROJECT_ID = prj.PROJECT_ID and
3103 prj.PROJECT_TYPE = nvl(p_project_type, prj.PROJECT_TYPE) and
3104 nvl(prj.ORG_ID, -99) = nvl(p_operating_unit, nvl(prj.ORG_ID, -99)) and
3105 status.PROJECT_ORGANIZATION_ID = nvl(p_project_organization_id,
3106 status.PROJECT_ORGANIZATION_ID) and
3107 prj.SEGMENT1 between nvl(p_from_project, prj.SEGMENT1) and
3108 nvl(p_to_project, prj.SEGMENT1) /*and
3109 prj.PROJECT_STATUS_CODE = active_projects.PROJECT_STATUS_CODE (+) */;
3110
3111 /* Not required changed the logic of l_prg_exist Bug 5057835
3112 elsif (l_prg_exists = 'Y') then
3113
3114 insert into PJI_PJP_PROJ_BATCH_MAP
3115 (
3116 WORKER_ID,
3117 PROJECT_ID,
3118 PJI_PROJECT_STATUS,
3119 EXTRACTION_TYPE,
3120 EXTRACTION_STATUS,
3121 PROJECT_TYPE,
3122 PROJECT_ORG_ID,
3123 PROJECT_ORGANIZATION_ID,
3124 PROJECT_TYPE_CLASS,
3125 PRJ_CURRENCY_CODE,
3126 PROJECT_ACTIVE_FLAG
3127 )
3128 select
3129 p_worker_id,
3130 status.PROJECT_ID,
3131 null PJI_PROJECT_STATUS,
3132 null EXTRACTION_TYPE,
3133 status.EXTRACTION_STATUS,
3134 prj.PROJECT_TYPE,
3135 prj.ORG_ID PROJECT_ORG_ID,
3136 status.PROJECT_ORGANIZATION_ID,
3137 status.PROJECT_TYPE_CLASS,
3138 prj.PROJECT_CURRENCY_CODE,
3139 decode(active_projects.PROJECT_ID, null, 'N', 'Y') PROJECT_ACTIVE_FLAG
3140 from
3141 PJI_PJP_PROJ_EXTR_STATUS status,
3142 PA_PROJECTS_ALL prj,
3143 (
3144 select /*+ ordered
3145 index(prg, PA_XBS_DENORM_N3)
3146 distinct
3147 emt.PROJECT_ID
3148 from
3149 PA_PROJECT_STATUSES stat,
3150 PA_PROJECTS_ALL prj,
3151 PA_XBS_DENORM prg,
3152 PA_PROJ_ELEMENTS emt
3153 where
3154 stat.STATUS_TYPE = 'PROJECT' and
3155 stat.PROJECT_SYSTEM_STATUS_CODE not in ('CLOSED',
3156 'PENDING_CLOSE',
3157 'PENDING_PURGE',
3158 'PURGED') and
3159 prj.PROJECT_STATUS_CODE = stat.PROJECT_STATUS_CODE and
3160 prg.STRUCT_TYPE = 'PRG' and
3161 prg.SUP_PROJECT_ID = prj.PROJECT_ID and
3162 emt.PROJ_ELEMENT_ID = prg.SUB_EMT_ID
3163 ) active_projects
3164 where
3165 status.PROJECT_ID = prj.PROJECT_ID and
3166 prj.PROJECT_TYPE = nvl(p_project_type, prj.PROJECT_TYPE) and
3167 nvl(prj.org_id,-99) = nvl(p_operating_unit, nvl(prj.org_id,-99)) and
3168 status.PROJECT_ORGANIZATION_ID = nvl(p_project_organization_id,
3169 status.PROJECT_ORGANIZATION_ID) and
3170 prj.segment1 between nvl(p_from_project,prj.segment1) and nvl(p_to_project,prj.segment1) and
3171 status.PROJECT_ID = active_projects.PROJECT_ID (+); */
3172
3173 -- identify all projects in the same program groups as the above projects
3174
3175 begin -- bug 5356051
3176
3177 select /*+ ordered */
3178 'Y'
3179 into
3180 l_prg_exists
3181 from
3182 PJI_PJP_PROJ_BATCH_MAP map,
3183 PA_PROJ_ELEMENT_VERSIONS proj
3184 where
3185 map.WORKER_ID = p_worker_id and
3186 map.PROJECT_ID = proj.PROJECT_ID and
3187 proj.OBJECT_TYPE = 'PA_STRUCTURES' and
3188 proj.PRG_GROUP is not null and
3189 ROWNUM = 1;
3190
3191 exception when NO_DATA_FOUND then
3192
3193 l_prg_exists := 'N';
3194
3195 end;
3196
3197 if (l_prg_exists = 'Y') then
3198
3199 insert into PJI_PJP_PROJ_BATCH_MAP
3200 (
3201 WORKER_ID,
3202 PROJECT_ID,
3203 PJI_PROJECT_STATUS,
3204 EXTRACTION_TYPE,
3205 EXTRACTION_STATUS,
3206 PROJECT_TYPE,
3207 PROJECT_ORG_ID,
3208 PROJECT_ORGANIZATION_ID,
3209 PROJECT_TYPE_CLASS,
3210 PRJ_CURRENCY_CODE,
3211 PROJECT_ACTIVE_FLAG
3212 )
3213 select /*+ ordered */
3214 p_worker_id,
3215 status.PROJECT_ID,
3216 null PJI_PROJECT_STATUS,
3217 null EXTRACTION_TYPE,
3218 status.EXTRACTION_STATUS,
3219 prj.PROJECT_TYPE,
3220 prj.ORG_ID PROJECT_ORG_ID,
3221 status.PROJECT_ORGANIZATION_ID,
3222 status.PROJECT_TYPE_CLASS,
3223 prj.PROJECT_CURRENCY_CODE,
3224 'Y' PROJECT_ACTIVE_FLAG
3225 from
3226 (
3227 select /*+ ordered */
3228 distinct
3229 ver2.PROJECT_ID
3230 from
3231 PJI_PJP_PROJ_BATCH_MAP map,
3232 PA_PROJ_ELEMENT_VERSIONS ver1,
3233 PA_PROJ_ELEMENT_VERSIONS ver2
3234 where
3235 map.WORKER_ID = p_worker_id and
3236 ver1.PROJECT_ID = map.PROJECT_ID and
3237 ver1.PRG_GROUP is not null and
3238 ver2.OBJECT_TYPE = 'PA_STRUCTURES' and
3239 ver2.PRG_GROUP = ver1.PRG_GROUP
3240 union
3241 select /*+ ordered
3242 index(prg1 PJI_XBS_DENORM_N3)
3243 index(prg2 PJI_XBS_DENORM_N3) */
3244 distinct
3245 prg2.SUP_PROJECT_ID PROJECT_ID
3246 from
3247 PJI_PJP_PROJ_BATCH_MAP map,
3248 PJI_XBS_DENORM prg1,
3249 PJI_XBS_DENORM prg2
3250 where
3251 map.WORKER_ID = p_worker_id and
3252 prg1.STRUCT_TYPE = 'PRG' and
3253 prg1.SUP_PROJECT_ID = map.PROJECT_ID and
3254 prg1.PRG_GROUP is not null and
3255 prg2.STRUCT_TYPE = 'PRG' and
3256 prg2.SUB_LEVEL = prg2.SUP_LEVEL and
3257 prg2.PRG_GROUP = prg1.PRG_GROUP
3258 ) map,
3259 PJI_PJP_PROJ_BATCH_MAP existing_projects,
3260 PJI_PJP_PROJ_EXTR_STATUS status,
3261 PA_PROJECTS_ALL prj
3262 where
3263 p_worker_id = existing_projects.WORKER_ID (+) and
3264 map.PROJECT_ID = existing_projects.PROJECT_ID (+) and
3265 existing_projects.PROJECT_ID is null and
3266 map.PROJECT_ID = status.PROJECT_ID and
3267 map.PROJECT_ID = prj.PROJECT_ID;
3268
3269 end if;
3270
3271 -- end if; -- Moving this because NO_work_run check is for all projects (including non-prg)
3272
3273 select
3274 count(*)
3275 into
3276 l_count
3277 from
3278 PJI_PJP_PROJ_BATCH_MAP new_worker
3279 where
3280 new_worker.WORKER_ID = p_worker_id;
3281
3282 if (l_count = 0) then
3283
3284 rollback ;
3285
3286 NO_WORK_RUNS(p_operating_unit,
3287 p_project_organization_id,
3288 p_project_type,
3289 p_from_project,
3290 p_to_project,
3291 l_extraction_type);
3292 FND_MESSAGE.SET_NAME('PJI', 'PJI_NO_WORK');
3293 dbms_standard.raise_application_error(-20041,FND_MESSAGE.GET);
3294
3295 end if;
3296
3297 elsif (l_extraction_type = 'RBS') then
3298
3299 insert into PJI_PJP_PROJ_BATCH_MAP
3300 (
3301 WORKER_ID,
3302 PROJECT_ID,
3303 PJI_PROJECT_STATUS,
3304 EXTRACTION_TYPE,
3305 EXTRACTION_STATUS,
3306 PROJECT_TYPE,
3307 PROJECT_ORG_ID,
3308 PROJECT_ORGANIZATION_ID,
3309 PROJECT_TYPE_CLASS,
3310 PRJ_CURRENCY_CODE,
3311 PROJECT_ACTIVE_FLAG
3312 )
3313 select /*+ ordered
3314 index(log, PA_PJI_PROJ_EVENTS_LOG_N1)
3315 index(rbs_asg, PA_RBS_PRJ_ASSIGNMENTS_N1) */
3316 distinct
3317 p_worker_id,
3318 rbs_asg.PROJECT_ID,
3319 null PJI_PROJECT_STATUS,
3320 null EXTRACTION_TYPE,
3321 'R' EXTRACTION_STATUS,
3322 prj.PROJECT_TYPE,
3323 prj.ORG_ID PROJECT_ORG_ID,
3324 prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
3325 decode(pt.PROJECT_TYPE_CLASS_CODE,
3326 'CAPITAL', 'C',
3327 'CONTRACT', 'B',
3328 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
3329 prj.PROJECT_CURRENCY_CODE,
3330 'Y' PROJECT_ACTIVE_FLAG
3331 /* decode(active_projects.PROJECT_ID,
3332 null, 'N', 'Y') PROJECT_ACTIVE_FLAG */
3333 from
3334 PA_PJI_PROJ_EVENTS_LOG log,
3335 PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
3336 PA_PROJECTS_ALL prj,
3337 PA_PROJECT_TYPES_ALL pt /*,
3338 ( Processing is now not depending on status Bug 5057835
3339 select /*+ ordered
3340 index(prg, PA_XBS_DENORM_N3)
3341 distinct
3342 emt.PROJECT_ID
3343 from
3344 PA_PROJECT_STATUSES stat,
3345 PA_PROJECTS_ALL prj,
3346 PA_XBS_DENORM prg,
3347 PA_PROJ_ELEMENTS emt
3348 where
3349 stat.STATUS_TYPE = 'PROJECT' and
3350 stat.PROJECT_SYSTEM_STATUS_CODE not in ('CLOSED',
3351 'PENDING_CLOSE',
3352 'PENDING_PURGE',
3353 'PURGED') and
3354 prj.PROJECT_STATUS_CODE = stat.PROJECT_STATUS_CODE and
3355 prg.STRUCT_TYPE = 'PRG' and
3356 prg.SUP_PROJECT_ID = prj.PROJECT_ID and
3357 emt.PROJ_ELEMENT_ID = prg.SUB_EMT_ID
3358 ) active_projects */
3359 where
3360 log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
3361 -- rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and --Commented for Bug#5728852 by VVJOSHI
3362 rbs_asg.RBS_HEADER_ID = nvl(p_rbs_header_id,
3363 rbs_asg.RBS_HEADER_ID) and
3364 nvl(prj.org_id,-99) = nvl(p_operating_unit, nvl(prj.org_id,-99)) and
3365 rbs_asg.PROJECT_ID = prj.PROJECT_ID and
3366 nvl(prj.ORG_ID, -1) = nvl(pt.ORG_ID, -1) and
3367 prj.PROJECT_TYPE = pt.PROJECT_TYPE ; /* and
3368 rbs_asg.PROJECT_ID = active_projects.PROJECT_ID (+); */
3369
3370 end if;
3371
3372 if (l_extraction_type ='INCREMENTAL' and p_run_mode <> 'NO_PRELOAD') then --Bug#5171542 - Start
3373
3374 begin
3375 SELECT 'FULL' INTO l_extraction_type FROM DUAL
3376 WHERE EXISTS
3377 (
3378 SELECT 1
3379 FROM pji_pjp_proj_extr_status extr,
3380 PJI_PJP_PROJ_BATCH_MAP map
3381 WHERE map.project_id=extr.project_id
3382 AND extr.extraction_status='F'
3383 AND WORKER_ID = p_worker_id
3384 );
3385 exception
3386 WHEN no_data_found THEN
3387 l_extraction_type:='INCREMENTAL';
3388 end;
3389
3390
3391 if( l_extraction_type = 'FULL' ) then
3392 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3393 'PRELOAD',
3394 'Y');
3395
3396 end if;
3397
3398
3399
3400 end if;
3401 -- Code for delete moved down Bug 5057835
3402 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3403 'EXTRACTION_TYPE',
3404 l_extraction_type);
3405
3406
3407 PJI_PROCESS_UTIL.ADD_STEPS(l_process, 'PJI_PJP', l_extraction_type);
3408
3409
3410 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_PJP_SUM_MAIN.INIT_PROCESS(p_worker_id, p_run_mode);')) then
3411 rollback to no_need_to_run_step;
3412 Commit; -- To release lock/ stamp parameters / config hist
3413 return;
3414 end if;
3415
3416 --Bug#5171542 - End
3417 -- start of overlapping work check Bug 5057835
3418
3419 l_count := 0;
3420 for c in
3421 (
3422 select distinct
3423 existing_workers.WORKER_ID
3424 from PJI_PJP_PROJ_BATCH_MAP existing_workers
3425 where existing_workers.WORKER_ID <> p_worker_id and
3426 exists (select 1
3427 from PJI_PJP_PROJ_BATCH_MAP new_worker
3428 where new_worker.WORKER_ID = p_worker_id and
3429 new_worker.PROJECT_ID = existing_workers.PROJECT_ID)
3430 ) loop
3431
3432 l_count := l_count + 1;
3433 request_id_table.EXTEND;
3434 prog_name_table.EXTEND;
3435 -- SUBMIT_REQUEST should check if the request is already running and
3436 -- just return the request id and program name if it is.
3437
3438 -- IF PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER( g_process || c.WORKER_ID, 'EXTRACTION_TYPE')<> 'RBS' THEN
3439
3440 SUBMIT_REQUEST(c.WORKER_ID, -- to retrieve the parameters
3441 request_id_table(l_count), -- in / out
3442 prog_name_table(l_count) -- in / out
3443 );
3444 -- ELSE
3445 -- l_rbs_exists:='Y';
3446 -- END IF;
3447 end loop;
3448
3449 if (l_count > 0) then
3450
3451 rollback; -- unlock PJI_PJP_PROJ_BATCH_MAP
3452
3453 for i in 1..l_count loop
3454 if request_id_table(i) <> -1 then
3455 pa_debug.log_message('Current Program is waiting for the Concurrent request with the request no: '||request_id_table(i), 1);
3456 PJI_PROCESS_UTIL.WAIT_FOR_REQUEST(request_id_table(i), 10);
3457
3458 if (not PJI_PROCESS_UTIL.REQUEST_STATUS('OKAY',
3459 request_id_table(i),
3460 prog_name_table(i))) then
3461
3462 -- We may want to add, to the error message, the request
3463 -- ID that is causing the program to fail
3464 OUTPUT_FAILED_RUNS;
3465 FND_MESSAGE.SET_NAME('PJI', 'PJI_OVERLAPPING_WORK');
3466 dbms_standard.raise_application_error(-20040, FND_MESSAGE.GET);
3467
3468 end if;
3469 end if;
3470 end loop;
3471 -- IF l_rbs_exists ='Y' THEN
3472 -- FND_MESSAGE.SET_NAME('PJI', 'PJI_OVERLAPPING_WORK');
3473 -- dbms_standard.raise_application_error(-20040, FND_MESSAGE.GET);
3474 -- END IF;
3475 INIT_PROCESS(p_worker_id,
3476 p_run_mode,
3477 p_operating_unit,
3478 p_project_type,
3479 p_project_organization_id,
3480 p_from_project,
3481 p_to_project,
3482 p_plan_type_id,
3483 p_rbs_header_id,
3484 p_only_pt_projects_flag,
3485 p_transaction_type,
3486 p_plan_versions);
3487
3488 return;
3489 else
3490 if (p_worker_id > 0 and
3491 ((l_incomplete_partial_count + l_failed_process_count > 0 and l_type = 0) or
3492 (l_type = 1 and l_failed_process_count > 0)
3493 )
3494 ) then
3495 OUTPUT_FAILED_RUNS;
3496 end if;
3497 end if;
3498
3499 -- end of overlapping work check Bug 5057835
3500
3501
3502 if (l_extraction_type = 'FULL' or
3503 l_extraction_type = 'INCREMENTAL' or
3504 l_extraction_type = 'PARTIAL') then
3505
3506 delete
3507 from PJI_PJP_PROJ_BATCH_MAP
3508 where WORKER_ID = p_worker_id and
3509 ((l_extraction_type = 'FULL' and
3510 EXTRACTION_STATUS <> 'F') or
3511 (l_extraction_type = 'INCREMENTAL' and
3512 EXTRACTION_STATUS <> 'I') or
3513 (l_extraction_type = 'PARTIAL' and
3514 EXTRACTION_STATUS <> 'I'));
3515
3516 delete
3517 from PA_PJI_PROJ_EVENTS_LOG log
3518 where log.EVENT_TYPE in ('WBS_CHANGE',
3519 'WBS_PUBLISH'/*,
3520 'RBS_ASSOC',
3521 'RBS_PRG'*/ --Commented for bug#6113807 by VVJOSHI
3522 ) and
3523 log.ATTRIBUTE1 in (select stat.PROJECT_ID
3524 from PJI_PJP_PROJ_BATCH_MAP map,
3525 PJI_PJP_PROJ_EXTR_STATUS stat
3526 where map.WORKER_ID = p_worker_id and
3527 stat.PROJECT_ID = map.PROJECT_ID and
3528 stat.EXTRACTION_STATUS = 'F');
3529
3530 end if;
3531
3532 update PJI_PJP_PROJ_EXTR_STATUS
3533 set EXTRACTION_STATUS = 'I',
3534 LAST_UPDATE_DATE = sysdate
3535 where l_extraction_type = 'FULL' and
3536 EXTRACTION_STATUS = 'F' and
3537 PROJECT_ID in (select PROJECT_ID
3538 from PJI_PJP_PROJ_BATCH_MAP
3539 where WORKER_ID = p_worker_id);
3540
3541 for c in lock_headers(p_worker_id) loop
3542 update PJI_PJP_WBS_HEADER wbs_hdr
3543 set wbs_hdr.LOCK_FLAG = 'P'
3544 where wbs_hdr.ROWID = c.HDR_ROWID;
3545 end loop;
3546
3547
3548 -- Bug#5099574 - Changes for Partial Refresh - Start - Populating the Refresh Code
3549
3550 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,'EXTRACTION_TYPE') = 'PARTIAL') then
3551
3552 -- If p_transaction_type is null then l_refresh_code is set to 63 . So All Actuals and Plans get refreshed
3553 -- This change is done to make sure the code is compatible with R12 Code Bug# 5453009
3554
3555 if p_transaction_type is not null then
3556 SELECT SUM(REFRESH_CODE) INTO l_refresh_code
3557 FROM (
3558 SELECT
3559 DECODE(p_transaction_type,'ALL_TXN_TYPE',1,'ACTUAL_TXN_TYPE',1,0) REFRESH_CODE
3560 FROM DUAL
3561 UNION ALL
3562 SELECT
3563 (CASE p_plan_versions
3564 WHEN 'ALL_PLAN_VERSION' THEN 62
3565 WHEN 'CB_VERSION' THEN 2
3566 WHEN 'CO_VERSION' THEN 4
3567 WHEN 'LP_VERSION' THEN 8
3568 WHEN 'WK_VERSION' THEN 16
3569 WHEN 'LAT_VERSION' THEN 30
3570 ELSE 0
3571 END) REFRESH_CODE
3572 FROM DUAL
3573 );
3574
3575 else
3576 l_refresh_code :=63;
3577 end if;
3578
3579 end if;
3580
3581 -- Bug#5099574 - Changes for Partial Refresh - End
3582
3583 -- Set global process parameters
3584 -- Reshuffle the following query
3585 if (p_rbs_header_id is not null) then
3586
3587 select max(ver.RBS_VERSION_ID)
3588 into l_rbs_version_id
3589 from PA_RBS_VERSIONS_B ver,
3590 PJI_PJP_RBS_HEADER rbs_hdr
3591 where ver.RBS_HEADER_ID = p_rbs_header_id and
3592 ver.STATUS_CODE = 'FROZEN' and
3593 ver.RBS_VERSION_ID = rbs_hdr.RBS_VERSION_ID;
3594
3595 end if;
3596
3597
3598 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3599 (l_process, 'PROJECT_TYPE', nvl(p_project_type, 'PJI$NULL'));
3600 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3601 (l_process, 'PROJECT_OPERATING_UNIT', nvl(p_operating_unit, -1));
3602 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3603 (l_process,
3604 'PROJECT_ORGANIZATION_ID', nvl(p_project_organization_id, -1));
3605 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3606 (l_process, 'FROM_PROJECT', nvl(p_from_project, 'PJI$NULL'));
3607 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3608 (l_process, 'TO_PROJECT', nvl(p_to_project, 'PJI$NULL'));
3609 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3610 (l_process, 'FROM_PROJECT_ID', nvl(p_from_project_id, -1));
3611 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3612 (l_process, 'TO_PROJECT_ID', nvl(p_to_project_id, -1));
3613 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3614 (l_process, 'PLAN_TYPE_ID', nvl(p_plan_type_id, -1));
3615 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3616 (l_process, 'RBS_HEADER_ID', nvl(p_rbs_header_id, -1));
3617 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3618 (l_process, 'RBS_VERSION_ID', nvl(l_rbs_version_id, -1));
3619 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3620 (l_process,
3621 'ONLY_PT_PROJECTS_FLAG', nvl(p_only_pt_projects_flag, 'PJI$NULL'));
3622
3623 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3624 (l_process,
3625 'TRANSACTION_TYPE', nvl(p_transaction_type, 'PJI$NULL'));
3626 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3627 (l_process,
3628 'PLAN_VERSION', nvl(p_plan_versions, 'PJI$NULL'));
3629
3630
3631 if (PJI_UTILS.GET_SETUP_PARAMETER('PA_PERIOD_FLAG') = 'N') then
3632 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3633 'PA_CALENDAR_FLAG',
3634 'N');
3635 else
3636 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3637 'PA_CALENDAR_FLAG',
3638 'Y');
3639 end if;
3640
3641 if (PJI_UTILS.GET_SETUP_PARAMETER('GL_PERIOD_FLAG') = 'N') then
3642 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3643 'GL_CALENDAR_FLAG',
3644 'N');
3645 else
3646 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3647 'GL_CALENDAR_FLAG',
3648 'Y');
3649 end if;
3650
3651 -- Bug#5099574 - Changes for Partial Refresh - Start - setting up the global process parameters
3652
3653
3654 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,'EXTRACTION_TYPE') = 'PARTIAL') then
3655 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3656 (l_process,
3657 'REFRESH_CODE', nvl(l_refresh_code, -1));
3658 end if;
3659
3660 -- Bug#5099574 - Changes for Partial Refresh - End -
3661
3662 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
3663 (l_process, 'PROGRAM_EXISTS', l_prg_exists);
3664
3665 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_PJP_SUM_MAIN.INIT_PROCESS(p_worker_id, p_run_mode);');
3666
3667 commit;
3668
3669 end INIT_PROCESS;
3670
3671
3672 -- -----------------------------------------------------
3673 -- procedure RUN_PROCESS
3674 --
3675 -- History
3676 -- 19-MAR-2004 SVERMETT Created
3677 --
3678 -- Internal PJP Summarization API.
3679 --
3680 -- -----------------------------------------------------
3681 procedure RUN_PROCESS (p_worker_id in number) is
3682
3683 l_process varchar2(30);
3684 l_extraction_type varchar2(30);
3685
3686 begin
3687
3688 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
3689
3690 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_PJP_SUM_MAIN.RUN_PROCESS(p_worker_id);')) then
3691 return;
3692 end if;
3693
3694 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3695 (l_process, 'EXTRACTION_TYPE');
3696
3697 PJI_PJP_EXTRACTION_UTILS.SEED_PJI_PJP_STATS(p_worker_id);
3698
3699 PJI_PJP_SUM_ROLLUP.POPULATE_TIME_DIMENSION(p_worker_id);
3700
3701 PJI_PJP_EXTRACTION_UTILS.POPULATE_ORG_EXTR_INFO;
3702
3703 -- implicit commit
3704 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3705 tabname => 'PJI_ORG_EXTR_INFO',
3706 percent => 10,
3707 degree => PJI_UTILS.
3708 GET_DEGREE_OF_PARALLELISM);
3709 -- implicit commit
3710 FND_STATS.GATHER_INDEX_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3711 indname => 'PJI_ORG_EXTR_INFO_N1',
3712 percent => 10);
3713
3714 PJI_PJP_SUM_ROLLUP.SET_ONLINE_CONTEXT(null, null, null, null, null, null,
3715 null, null, null, null);
3716
3717 PJI_PJP_SUM_ROLLUP.CREATE_EVENTS_SNAPSHOT(p_worker_id);
3718
3719 PJI_PJP_SUM_ROLLUP.LOCK_HEADERS(p_worker_id);
3720
3721 /* plan type code changes (4882640) This API call will be shifted to step entry */
3722 if (PJI_UTILS.GET_PARAMETER('PJI_PTC_UPGRADE') = 'P') and l_extraction_type <> 'PARTIAL' then
3723 PLAN_TYPE_CODE_CHANGES (p_worker_id);
3724 end if;
3725 /*---------- plan type code changes (4882640) ------------*/
3726
3727
3728 PJI_PJP_SUM_ROLLUP.PROCESS_RBS_CHANGES(p_worker_id);
3729
3730 PJI_PJP_SUM_DENORM.POPULATE_XBS_DENORM(p_worker_id, 'ALL',
3731 null, null, null);
3732
3733 PJI_PJP_SUM_ROLLUP.UPDATE_XBS_DENORM_FULL(p_worker_id);
3734
3735 PJI_PJP_SUM_ROLLUP.UPDATE_PROGRAM_WBS(p_worker_id);
3736 PJI_PJP_SUM_ROLLUP.PURGE_EVENT_DATA(p_worker_id);
3737 PJI_PJP_SUM_ROLLUP.UPDATE_PROGRAM_RBS(p_worker_id);
3738
3739 PJI_PJP_SUM_ROLLUP.CREATE_MAPPING_RULES(p_worker_id);
3740 PJI_PJP_SUM_ROLLUP.MAP_RBS_HEADERS(p_worker_id);
3741
3742 PJI_PJP_SUM_DENORM.POPULATE_RBS_DENORM(p_worker_id, 'ALL', null);
3743
3744 PJI_PJP_SUM_ROLLUP.POPULATE_XBS_DENORM_DELTA(p_worker_id);
3745 PJI_PJP_SUM_ROLLUP.POPULATE_RBS_DENORM_DELTA(p_worker_id);
3746
3747 PJI_FM_SUM_PSI.BALANCES_ROWID_TABLE(p_worker_id);
3748 PJI_FM_SUM_PSI.ACT_ROWID_TABLE(p_worker_id);
3749
3750 PJI_PJP_SUM_ROLLUP.AGGREGATE_FP_SLICES(p_worker_id);
3751 PJI_PJP_SUM_ROLLUP.AGGREGATE_AC_SLICES(p_worker_id);
3752
3753 PJI_PJP_SUM_ROLLUP.MARK_EXTRACTED_PROJECTS(p_worker_id);
3754
3755 PJI_PJP_SUM_CUST.PJP_CUSTOM_FPR_API(p_worker_id);
3756 PJI_PJP_SUM_CUST.PJP_CUSTOM_ACR_API(p_worker_id);
3757
3758 PJI_PJP_SUM_ROLLUP.AGGREGATE_FP_CUST_SLICES(p_worker_id);
3759 PJI_PJP_SUM_ROLLUP.AGGREGATE_AC_CUST_SLICES(p_worker_id);
3760
3761 PJI_PJP_SUM_ROLLUP.GET_PLANRES_ACTUALS(p_worker_id);
3762 PJI_PJP_SUM_ROLLUP.PULL_PLANS_FOR_PR(p_worker_id);
3763 PJI_PJP_SUM_ROLLUP.PULL_PLANS_FOR_RBS(p_worker_id);
3764 PJI_PJP_SUM_ROLLUP.PULL_DANGLING_PLANS(p_worker_id);
3765 PJI_PJP_SUM_ROLLUP.PROCESS_PENDING_PLAN_UPDATES(p_worker_id);
3766
3767 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_CAL_ALL(p_worker_id);
3768 PJI_PJP_SUM_ROLLUP.ROLLUP_ACR_CAL_ALL(p_worker_id);
3769
3770 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_CAL_NONTP(p_worker_id);
3771 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_CAL_PA(p_worker_id);
3772 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_CAL_GL(p_worker_id);
3773 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_CAL_EN(p_worker_id);
3774 PJI_PJP_SUM_ROLLUP.ROLLUP_ACR_CAL_PA(p_worker_id);
3775 PJI_PJP_SUM_ROLLUP.ROLLUP_ACR_CAL_GL(p_worker_id);
3776 PJI_PJP_SUM_ROLLUP.ROLLUP_ACR_CAL_EN(p_worker_id);
3777
3778 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_TOP(p_worker_id);
3779 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_WBS(p_worker_id);
3780 PJI_PJP_SUM_ROLLUP.ROLLUP_ACR_WBS(p_worker_id);
3781
3782 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_PRG(p_worker_id);
3783 PJI_PJP_SUM_ROLLUP.ROLLUP_ACR_PRG(p_worker_id);
3784
3785 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES(p_worker_id);
3786
3787 PJI_PJP_SUM_ROLLUP.AGGREGATE_PLAN_DATA(p_worker_id);
3788 PJI_PJP_SUM_ROLLUP.PURGE_PLAN_DATA(p_worker_id);
3789
3790 PJI_PJP_SUM_ROLLUP.UPDATE_WBS_HDR(p_worker_id);
3791
3792 /* Bug 6063826. Commented and used MERGE
3793 PJI_PJP_SUM_ROLLUP.GET_FPR_ROWIDS(p_worker_id);
3794 PJI_PJP_SUM_ROLLUP.UPDATE_FPR_ROWS(p_worker_id);
3795 PJI_PJP_SUM_ROLLUP.INSERT_FPR_ROWS(p_worker_id);
3796 PJI_PJP_SUM_ROLLUP.CLEANUP_FPR_ROWID_TABLE(p_worker_id);
3797 */
3798 PJI_PJP_SUM_ROLLUP.MERGE_INTO_FP_FACTS(p_worker_id);
3799 PJI_PJP_SUM_ROLLUP.GET_ACR_ROWIDS(p_worker_id);
3800 PJI_PJP_SUM_ROLLUP.UPDATE_ACR_ROWS(p_worker_id);
3801 PJI_PJP_SUM_ROLLUP.INSERT_ACR_ROWS(p_worker_id);
3802 PJI_PJP_SUM_ROLLUP.CLEANUP_ACR_ROWID_TABLE(p_worker_id);
3803
3804 PJI_PJP_SUM_ROLLUP.UPDATE_XBS_DENORM(p_worker_id);
3805 PJI_PJP_SUM_ROLLUP.UPDATE_RBS_DENORM(p_worker_id);
3806
3807 PJI_PJP_SUM_ROLLUP.PROCESS_PENDING_EVENTS(p_worker_id);
3808 PJI_PJP_SUM_ROLLUP.GET_TASK_ROLLUP_ACTUALS(p_worker_id);
3809
3810 PJI_FM_SUM_PSI.BALANCES_UPDATE_DELTA(p_worker_id);
3811 PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA(p_worker_id);
3812 PJI_FM_SUM_PSI.PURGE_BALANCES_CMT(p_worker_id);
3813 PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA_CMT(p_worker_id);
3814 PJI_FM_SUM_PSI.PURGE_INCREMENTAL_BALANCES(p_worker_id);
3815 PJI_FM_SUM_PSI.PURGE_BALANCES_ACT(p_worker_id);
3816
3817 PJI_PJP_SUM_ROLLUP.UNLOCK_ALL_HEADERS(p_worker_id);
3818 PJI_PJP_SUM_ROLLUP.CLEANUP(p_worker_id);
3819
3820 -- if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3821 -- (l_process, 'FROM_PROJECT_ID') = -1 and
3822 -- PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3823 -- (l_process, 'TO_PROJECT_ID') = -1) then
3824 -- PJI_PJP_EXTRACTION_UTILS.ANALYZE_PJP_FACTS;
3825 -- end if;
3826
3827 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_PJP_SUM_MAIN.RUN_PROCESS(p_worker_id);');
3828
3829 commit;
3830
3831 end RUN_PROCESS;
3832
3833
3834 -- -----------------------------------------------------
3835 -- procedure WRAPUP_PROCESS
3836 --
3837 -- History
3838 -- 19-MAR-2004 SVERMETT Created
3839 --
3840 -- Internal PJP Summarization API.
3841 --
3842 -- -----------------------------------------------------
3843 procedure WRAPUP_PROCESS (p_worker_id in number) is
3844
3845 l_process varchar2(30);
3846 l_pji_schema varchar2(30);
3847
3848 l_worker_id number;
3849 l_preload varchar2(30);
3850 l_project_type varchar2(255);
3851 l_project_organization_id number;
3852 l_from_project_id number;
3853 l_to_project_id number;
3854 l_plan_type_id number;
3855 l_rbs_header_id number;
3856 l_only_pt_projects_flag varchar2(255);
3857 l_operating_unit number;
3858 l_from_project pa_projects_all.segment1%TYPE;
3859 l_to_project pa_projects_all.segment1%TYPE;
3860
3861 begin
3862
3863 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
3864
3865 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_PJP_SUM_MAIN.WRAPUP_PROCESS(p_worker_id);')) then
3866 return;
3867 end if;
3868 -- Changes for bug 6266824
3869 /* Commented for bug 6266824
3870 -- lock table PJI_PJP_PROJ_BATCH_MAP in exclusive mode;
3871 if p_worker_id = 1 then
3872 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P1) in exclusive mode;
3873 elsif p_worker_id = 2 then
3874 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P2) in exclusive mode;
3875 elsif p_worker_id = 3 then
3876 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P3) in exclusive mode;
3877 elsif p_worker_id = 4 then
3878 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P4) in exclusive mode;
3879 elsif p_worker_id = 5 then
3880 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P5) in exclusive mode;
3881 elsif p_worker_id = 6 then
3882 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P6) in exclusive mode;
3883 elsif p_worker_id = 7 then
3884 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P7) in exclusive mode;
3885 elsif p_worker_id = 8 then
3886 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P8) in exclusive mode;
3887 elsif p_worker_id = 9 then
3888 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P9) in exclusive mode;
3889 elsif p_worker_id = 10 then
3890 lock table PJI_PJP_PROJ_BATCH_MAP PARTITION(P10) in exclusive mode;
3891 end if;
3892 */
3893 l_preload := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'PRELOAD');
3894
3895 if (l_preload = 'Y') then
3896
3897 l_project_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3898 (l_process, 'PROJECT_TYPE');
3899
3900 if (l_project_type = 'PJI$NULL') then
3901 l_project_type := null;
3902 end if;
3903
3904 l_operating_unit := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3905 (l_process, 'PROJECT_OPERATING_UNIT');
3906
3907 if (l_operating_unit = -1) then
3908 l_operating_unit := null;
3909 end if;
3910
3911 l_project_organization_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3912 (l_process, 'PROJECT_ORGANIZATION_ID');
3913
3914 if (l_project_organization_id = -1) then
3915 l_project_organization_id := null;
3916 end if;
3917
3918 l_from_project_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3919 (l_process, 'FROM_PROJECT_ID');
3920
3921 if (l_from_project_id = -1) then
3922 l_from_project_id := null;
3923 end if;
3924
3925 l_to_project_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3926 (l_process, 'TO_PROJECT_ID');
3927
3928 if (l_to_project_id = -1) then
3929 l_to_project_id := null;
3930 end if;
3931
3932 l_from_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3933 (l_process, 'FROM_PROJECT');
3934
3935 if (l_from_project = 'PJI$NULL') then
3936 l_from_project := null;
3937 end if;
3938
3939 l_to_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3940 (l_process, 'TO_PROJECT');
3941
3942 if (l_to_project = 'PJI$NULL') then
3943 l_to_project := null;
3944 end if;
3945
3946 l_plan_type_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3947 (l_process, 'PLAN_TYPE_ID');
3948
3949 if (l_plan_type_id = -1) then
3950 l_plan_type_id := null;
3951 end if;
3952
3953 l_rbs_header_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3954 (l_process, 'RBS_HEADER_ID');
3955
3956 if (l_rbs_header_id = -1) then
3957 l_rbs_header_id := null;
3958 end if;
3959
3960 l_only_pt_projects_flag := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3961 (l_process, 'ONLY_PT_PROJECTS_FLAG');
3962
3963 if (l_only_pt_projects_flag = 'PJI$NULL') then
3964 l_only_pt_projects_flag := null;
3965 end if;
3966
3967 end if;
3968
3969 -- clean up worker tables
3970
3971 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3972
3973 if (NVL(l_preload,'N') <> 'Y') then
3974 OUTPUT_ACT_FAILED_RUNS(p_worker_id);
3975 OUTPUT_ACT_PASSED_RUNS(p_worker_id);
3976 end if;
3977
3978 update PJI_PJP_PROJ_EXTR_STATUS
3979 set LAST_UPDATE_DATE = sysdate
3980 where PROJECT_ID in (select map.PROJECT_ID
3981 from PJI_PJP_PROJ_BATCH_MAP map
3982 where map.WORKER_ID = p_worker_id);
3983
3984 delete from PJI_PJP_PROJ_BATCH_MAP where WORKER_ID = p_worker_id;
3985
3986 -- mark current iteration as successful
3987
3988 PJI_PROCESS_UTIL.WRAPUP_PROCESS(l_process);
3989
3990 update PJI_SYSTEM_CONFIG_HIST
3991 set END_DATE = sysdate,
3992 COMPLETION_TEXT = 'Normal completion'
3993 where PROCESS_NAME = l_process and
3994 END_DATE is null;
3995
3996 -- update default report as-of date
3997
3998 PJI_UTILS.SET_PARAMETER('LAST_PJP_EXTR_DATE_' || l_process,
3999 to_char(sysdate, PJI_PJP_SUM_MAIN.g_date_mask));
4000
4001 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_PJP_SUM_MAIN.WRAPUP_PROCESS(p_worker_id);');
4002
4003 if (l_preload = 'Y') then
4004
4005 INIT_PROCESS(l_worker_id,
4006 'NO_PRELOAD',
4007 l_operating_unit,
4008 l_project_type,
4009 l_project_organization_id,
4010 l_from_project,
4011 l_to_project,
4012 /* l_from_project_id,
4013 l_to_project_id, */
4014 l_plan_type_id,
4015 l_rbs_header_id,
4016 l_only_pt_projects_flag);
4017
4018 RUN_PROCESS(l_worker_id);
4019 WRAPUP_PROCESS(l_worker_id);
4020
4021 end if;
4022
4023 commit;
4024
4025 end WRAPUP_PROCESS;
4026
4027
4028 -- -----------------------------------------------------
4029 -- procedure WRAPUP_FAILURE
4030 --
4031 -- History
4032 -- 19-MAR-2004 SVERMETT Created
4033 --
4034 -- Internal PJP Summarization API.
4035 --
4036 -- -----------------------------------------------------
4037 procedure WRAPUP_FAILURE (p_worker_id in number) is
4038
4039 l_process_running varchar2(240);
4040 l_sqlerrm varchar2(240);
4041
4042 begin
4043
4044 rollback;
4045
4046 l_process_running := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
4047 (g_process || p_worker_id, 'PROCESS_RUNNING');
4048
4049 if (l_process_running is not null) then
4050
4051 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process || p_worker_id,
4052 'PROCESS_RUNNING',
4053 'F');
4054
4055 end if;
4056
4057 l_sqlerrm := substr(sqlerrm, 1, 240);
4058
4059 update PJI_SYSTEM_CONFIG_HIST
4060 set END_DATE = sysdate,
4061 COMPLETION_TEXT = l_sqlerrm
4062 where PROCESS_NAME = g_process || p_worker_id and
4063 END_DATE is null;
4064
4065 commit;
4066
4067 pji_utils.write2log(sqlerrm, true, 0);
4068
4069 commit;
4070
4071 end WRAPUP_FAILURE;
4072
4073
4074 -- -----------------------------------------------------
4075 -- procedure SUMMARIZE
4076 --
4077 -- This the the main procedure, it is invoked from
4078 -- a concurrent program.
4079 --
4080 -- History
4081 -- 19-MAR-2004 SVERMETT Created
4082 --
4083 -- Internal PJP Summarization API.
4084 --
4085 -- -----------------------------------------------------
4086 procedure SUMMARIZE
4087 (
4088 errbuf out nocopy varchar2,
4089 retcode out nocopy varchar2,
4090 p_run_mode in varchar2,
4091 p_operating_unit in number default null,
4092 p_project_organization_id in number default null,
4093 p_project_type in varchar2 default null,
4094 p_from_project in varchar2 default null,
4095 p_to_project in varchar2 default null,
4096 p_plan_type_id in number default null,
4097 p_rbs_header_id in number default null,
4098 p_transaction_type in varchar2 default null, -- Bug#5099574 - New parameter for Partial Refresh
4099 p_plan_versions in varchar2 default null, -- Bug#5099574 - New parameter for Partial Refresh
4100 p_only_pt_projects_flag in varchar2 default null
4101 ) is
4102
4103 l_pji_not_licensed exception;
4104 pragma exception_init(l_pji_not_licensed, -20020);
4105
4106 l_worker_id number;
4107 l_from_project_id number;
4108 l_to_project_id number;
4109
4110 begin
4111
4112 -- if (PA_INSTALL.is_pji_licensed = 'N') then
4113 -- pji_utils.write2log('Error: PJI is not licensed.');
4114 -- commit;
4115 -- raise l_pji_not_licensed;
4116 -- end if;
4117
4118 commit;
4119 execute immediate 'alter session enable parallel query';
4120 execute immediate 'alter session enable parallel dml';
4121
4122 g_retcode := 0;
4123
4124 pa_debug.set_process('PLSQL'); /* start 4893117 */
4125 IF p_run_mode IN ('I','F') then
4126 pa_debug.log_message('=======Concurrent Program Parameters Start =======', 1);
4127 pa_debug.log_message('Argument => Operating Unit ['||p_operating_unit||']', 1);
4128 pa_debug.log_message('Argument => Project Organization ['||p_project_organization_id||']', 1);
4129 pa_debug.log_message('Argument => Project Type ['||p_project_type||']', 1);
4130 pa_debug.log_message('Argument => From Project Number ['||p_from_project||']', 1);
4131 pa_debug.log_message('Argument => To Project Number ['||p_to_project||']', 1);
4132 pa_debug.log_message('=======Concurrent Program Parameters End =======', 1);
4133 ELSIF p_run_mode in ('P') then
4134 pa_debug.log_message('=======Concurrent Program Parameters Start =======', 1);
4135 pa_debug.log_message('Argument => Operating Unit ['||p_operating_unit||']', 1);
4136 pa_debug.log_message('Argument => From Project Number ['||p_from_project||']', 1);
4137 pa_debug.log_message('Argument => To Project Number ['||p_to_project||']', 1);
4138 pa_debug.log_message('Argument => Plan Type ['||p_plan_type_id||']', 1);
4139 pa_debug.log_message('Argument => Transaction Type ['||p_transaction_type||']', 1);
4140 pa_debug.log_message('Argument => Plan Version ['||p_plan_versions||']', 1);
4141 pa_debug.log_message('=======Concurrent Program Parameters End =======', 1);
4142 ELSIF p_run_mode in ('R') then
4143 pa_debug.log_message('=======Concurrent Program Parameters Start =======', 1);
4144 pa_debug.log_message('Argument => RBS Header Name ['||p_rbs_header_id||']', 1);
4145 pa_debug.log_message('=======Concurrent Program Parameters End =======', 1);
4146 END IF; /* end 4893117 */
4147
4148 INIT_PROCESS(l_worker_id,
4149 p_run_mode,
4150 p_operating_unit,
4151 p_project_type,
4152 p_project_organization_id,
4153 p_from_project,
4154 p_to_project,
4155 p_plan_type_id,
4156 p_rbs_header_id,
4157 p_only_pt_projects_flag,
4158 p_transaction_type , -- Bug#5099574 - New parameter for Partial Refresh
4159 p_plan_versions -- Bug#5099574 - New parameter for Partial Refresh
4160 );
4161
4162 begin
4163
4164 RUN_PROCESS(l_worker_id);
4165 WRAPUP_PROCESS(l_worker_id);
4166
4167 exception when others then
4168
4169 WRAPUP_FAILURE(l_worker_id);
4170 execute immediate 'alter session disable parallel dml';
4171 retcode := 2;
4172 errbuf := sqlerrm;
4173 raise;
4174
4175 end;
4176
4177 commit;
4178 execute immediate 'alter session disable parallel dml';
4179
4180 retcode := g_retcode;
4181
4182 exception when others then
4183 rollback;
4184 IF SQLCODE = -20041 then
4185 retcode := 1;
4186 ELSE
4187 retcode := 2;
4188 errbuf := sqlerrm;
4189 -- raise; commented for bug 6015217
4190 END IF;
4191
4192 end SUMMARIZE;
4193
4194 end PJI_PJP_SUM_MAIN;