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