[Home] [Help]
PACKAGE BODY: APPS.PJI_LAUNCH_UPP_MAIN
Source
1 package body PJI_LAUNCH_UPP_MAIN as
2 /* $Header: PJILN01B.pls 120.11.12020000.4 2013/02/06 05:57:28 udshanmu ship $ */
3
4 -- Bug 13076374
5 g_refresh_process varchar2(1):= 'N';
6 g_process_name varchar2(60) ;
7 g_validation_process varchar2(1) := 'N';
8 g_launch_type varchar2(60); -- sridhar_phase_1
9
10 PROCEDURE print_line
11 (
12 p_line in varchar2
13 );
14
15 PROCEDURE generate_validation_report
16 (
17 p_operating_unit in number
18 );
19
20 PROCEDURE create_refresh_proj_list
21 (
22 p_operating_unit in number
23 );
24
25 PROCEDURE Fact_deletion
26 (
27 p_batch_name in varchar2
28 );
29
30 -- End Bug 13076374
31
32 PROCEDURE log1
33 (p_msg IN VARCHAR2 ,
34 p_module IN VARCHAR2 DEFAULT '5701238')
35 IS
36 pragma autonomous_transaction;
37 BEGIN
38 insert into FND_LOG_MESSAGES
39 (MODULE, LOG_LEVEL, MESSAGE_TEXT
40 , SESSION_ID, USER_ID, TIMESTAMP
41 , LOG_SEQUENCE, ENCODED, NODE
42 , NODE_IP_ADDRESS, PROCESS_ID, JVM_ID
43 , THREAD_ID, AUDSID, DB_INSTANCE
44 , TRANSACTION_CONTEXT_ID)
45 values
46 (p_module, 6, p_msg, -1, 0, sysdate
47 , FND_LOG_MESSAGES_S.NEXTVAL, 'Y', null
48 , null, NULL, NULL, NULL, NULL, 1, NULL);
49 COMMIT;
50 END log1;
51
52
53 PROCEDURE UPDATE_BATCH_CONC_STATUS
54 (
55 p_first_time_flag in varchar2,
56 x_count_batches out nocopy number,
57 x_count_running out nocopy number,
58 x_count_errored out nocopy number,
59 x_count_completed out nocopy number,
60 x_count_pending out nocopy number
61 )
62 IS
63
64 TYPE Prg_Batch_t IS
65 TABLE OF pji_prg_batch%ROWTYPE
66 INDEX BY BINARY_INTEGER;
67
68
69 l_Prg_Batch_t Prg_Batch_t;
70
71 CURSOR prg_group IS
72 SELECT
73 *
74 FROM
75 pji_prg_batch
76 WHERE nvl(curr_request_status,'PENDING') <> 'COMPLETED' /* added for bug#10031149 */
77 ORDER BY
78 DECODE(nvl(curr_request_status,'PENDING') ,
79 'PENDING' , 1,
80 'ERRORED' , 2, 3 ) ;
81
82 l_count_batches number := 0;
83 l_count_running number := 0;
84 l_count_errored number := 0;
85 l_count_completed number := 0;
86 l_count_pending number := 0;
87
88 l_first_time_flag varchar2(1) := 'N';
89
90 l_rowcount number := 0;
91
92 BEGIN
93
94 l_first_time_flag := nvl(p_first_time_flag,'N' );
95
96 l_Prg_Batch_t.delete;
97 OPEN prg_group;
98 FETCH prg_group BULK COLLECT
99 INTO l_Prg_Batch_t;
100
101 l_rowcount := prg_group%rowcount;
102
103 CLOSE prg_group;
104
105 --- Get the status of the already launched concurrent request and update the
106 --- status into the local table and also database table.
107
108 l_count_running := 0;
109 l_count_errored := 0;
110 l_count_pending := 0;
111
112 IF l_rowcount > 0 THEN
113
114 FOR i IN l_Prg_Batch_t.first..l_Prg_Batch_t.last LOOP
115
116
117 IF ( nvl(l_Prg_Batch_t(i).curr_request_id,-1) > 0 ) THEN
118 IF ( PJI_PROCESS_UTIL.REQUEST_STATUS(
119 'OKAY_WARNING',
120 l_Prg_Batch_t(i).curr_request_id,
121 g_process_name) )
122 THEN
123 IF ( PJI_PROCESS_UTIL.REQUEST_STATUS(
124 'RUNNING',
125 l_Prg_Batch_t(i).curr_request_id,
126 g_process_name) )
127 THEN
128 l_Prg_Batch_t(i).curr_request_status := 'RUNNING';
129 l_count_running := l_count_running + 1;
130
131 l_count_batches := l_count_batches + 1;
132
133 ELSE
134 l_Prg_Batch_t(i).curr_request_status := 'COMPLETED';
135 l_count_completed := l_count_completed + 1;
136 END IF;
137
138 ELSE
139
140 -- l_first_time_flag is set to 'Y' means that
141 -- the status are re-update , the earlier failed
142 -- process should be marked as ERRORED
143 --
144 -- l_first_time_flag is set to 'N' means that
145 -- the status are re-update during the running loop,
146 -- f concurrent request is failed means the process
147 -- should be marked as R-ERRORED so that this is not
148 -- re-submitted during this run only.
149
150 if (l_first_time_flag = 'Y' ) then
151 l_Prg_Batch_t(i).curr_request_status := 'ERRORED';
152 else
153 l_Prg_Batch_t(i).curr_request_status := 'R-ERRORED';
154 end if;
155
156 l_count_errored := l_count_errored + 1;
157 END IF;
158
159 ELSE
160
161 -- when you come first time then only you need to update
162 -- the status to PENDING when either the request_id is null
163 -- or -1.
164 -- During the second run they should not be marked as pending
165 --- as these might be updated as SUBMIT-ERRORED.
166 -- due to some reason if the concurrent program is not getting
167 -- submitted then we should not submit in infinite loop.
168
169 if (l_first_time_flag = 'Y' ) then
170
171
172 l_Prg_Batch_t(i).curr_request_status := 'PENDING';
173 l_count_pending := l_count_pending + 1;
174
175 end if;
176
177
178 END IF;
179
180 -- Update the current status of the concurrent request back
181 -- into the table . for the request that are marked as
182 -- ERRORED we should not mark then back to R-ERRORED.
183 -- R-ERRORED is mainly for the process that are errored
184 -- during this run , so that these need not be picked up for
185 -- re-submission
186
187 UPDATE
188 pji_prg_batch
189 SET
190 curr_request_status = decode(nvl(curr_request_status,'PENDING'),
191 'ERRORED',
192 decode(l_Prg_Batch_t(i).curr_request_status,
193 'R-ERRORED',curr_request_status,
194 l_Prg_Batch_t(i).curr_request_status ),
195 l_Prg_Batch_t(i).curr_request_status )
196 WHERE
197 batch_name = l_Prg_Batch_t(i).batch_name;
198
199 END LOOP;
200
201 commit;
202
203 --Sridhar July - 10th change
204
205 BEGIN
206
207 UPDATE pji_prg_batch
208 SET curr_request_status = 'ERRORED'
209 WHERE nvl(curr_request_id,-1) > 0
210 and curr_request_status = 'COMPLETED'
211 and not exists
212 ( select 'x' from fnd_concurrent_requests x where x.request_id = curr_request_id );
213
214 EXCEPTION
215 WHEN no_data_found THEN
216 null;
217 END;
218
219 END IF; -- l_rowcount if statement
220
221
222 x_count_batches := l_count_batches;
223 x_count_running := l_count_running;
224 x_count_errored := l_count_errored;
225 x_count_completed := l_count_completed;
226 x_count_pending := l_count_pending;
227
228 end UPDATE_BATCH_CONC_STATUS;
229
230 PROCEDURE LAUNCH_UPP_PROCESS
231 (
232 errbuf out NOCOPY varchar2,
233 retcode out NOCOPY varchar2,
234 p_num_of_projects in number ,
235 p_temp_table_size in number ,
236 p_num_parallel_runs in number ,
237 p_num_of_batches in number ,
238 p_wait_time_seconds in number ,
239 p_regenerate_batches in varchar2 ,
240 p_incremental_mode in varchar2 default 'Y',
241 P_OPERATING_UNIT in number,
242 P_SUBMIT_UPPD_DURING_LPPD in varchar2 default 'Y',
243 p_launch_type in varchar2 default 'UPPD', -- sridhar_phase_1
244 p_project_status in varchar2
245 )
246 is
247 TYPE Prg_Batch_t IS
248 TABLE OF pji_prg_batch%ROWTYPE
249 INDEX BY BINARY_INTEGER;
250
251 l_Prg_Batch_t Prg_Batch_t;
252
253 curr_count_batches number := 0;
254
255 l_count_batches number := 0;
256 l_count_running number := 0;
257 l_count_errored number := 0;
258 l_count_completed number := 0;
259 l_count_pending number := 0;
260 l_count_rerrored number := 0; /* Added for bug 8416116 */
261
262 l_num_parallel_runs number := 0;
263 l_test number := 0;
264 l_request_id number := -1;
265 l_first_time_flag varchar2(1) := 'Y';
266 l_no_running_request varchar2(1) := 'N';
267 l_num_of_batches number := 0;
268
269 CURSOR prg_group IS
270 SELECT *
271 FROM pji_prg_batch
272 ORDER BY DECODE(nvl(curr_request_status,'PENDING'),
273 'ERRORED',curr_request_status),
274 to_number(rtrim(substrb(replace(batch_name,'-ERR',''),19,10))); /* Modified for bug 9109118 */ /* Modified for bug 14760728 */
275 /* Modified substrb value from 11 to 19 for bug 12570872 */
276
277 l_time_in_seconds number ;
278
279 l_reg_flag varchar2(1);
280 l_reg_num number ;
281 l_req_count number;
282
283 BEGIN
284
285 -- Bug 14138486
286 -- Need to delete since there may be a previous LUPPD request which may have completed in error.
287 delete from pji_system_parameters
288 where name = 'PJI_STAGE3_REQ_LUPPD';
289 commit;
290
291 IF p_submit_uppd_during_lppd = 'N' THEN
292 PJI_UTILS.SET_PARAMETER ('PJI_STAGE3_REQ_LUPPD', p_submit_uppd_during_lppd);
293 commit;
294 -- Need to check if any stage 3 program has been submitted and running. If so, error out.
295 Select count(*)
296 Into l_req_count
297 From Fnd_Concurrent_Requests a, Fnd_Concurrent_Programs P, Fnd_Application c
298 Where a.Program_Application_ID = P.Application_ID
299 And a.Concurrent_Program_ID = P.Concurrent_Program_ID
300 AND P.Concurrent_Program_Name in ('PJI_PJP_SUMMARIZE_RBS', 'PJI_PJP_SUMMARIZE_PRTL',
301 'PJI_PJP_SUMMARIZE_INCR', 'PJI_PJP_SUMMARIZE_FULL','PJI_PJP_SUM_CLEANALL')
302 And P.Application_ID = c.Application_ID
303 And c.Application_Short_Name = 'PJI'
304 AND a.phase_code in ('R','P','I')
305 AND rownum = 1;
306
307 IF l_req_count = 1 THEN
308 delete from pji_system_parameters
309 where name = 'PJI_STAGE3_REQ_LUPPD';
310 commit;
311
312 FND_MESSAGE.SET_NAME('PJI', 'PJI_NO_PRC_AVAILABLE');
313 dbms_standard.raise_application_error(-20010, FND_MESSAGE.GET);
314
315 END IF;
316 END IF;
317
318
319 -- End Bug 14138486
320 g_launch_type := p_launch_type; -- sridhar_phase_1
321
322 /**
323
324 FND_FILE.PUT(FND_FILE.OUTPUT,' Test Message 1 ' );
325 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
326
327 FND_FILE.PUT(FND_FILE.OUTPUT,'p_launch_type ' || p_launch_type );
328 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
329
330
331 raise_application_error(-20000,'sri Failing the process');
332 **/
333
334 --If ( p_temp_table_size = 888888 ) then -- sridhar_phase_1 changes
335 IF (g_launch_type = 'VPPD') THEN
336
337 g_validation_process := 'Y';
338 create_refresh_proj_list(p_operating_unit);
339 generate_validation_report(p_operating_unit);
340 ROLLBACK;
341 RETURN;
342
343 ELSE
344 g_validation_process := 'N';
345 --If (p_temp_table_size = 666666) then
346 IF (g_launch_type = 'RPPD') THEN -- sridhar_phase_1
347 g_refresh_process := 'Y';
348 g_process_name := 'PJI_PJP_SUMMARIZE_PRTL';
349 elsif (g_launch_type = 'UPPD') THEN
350 g_refresh_process := 'N';
351 g_process_name := 'PJI_PJP_SUMMARIZE_INCR';
352 ELSE
353 raise_application_error(-20000,' Invalid p_launch_type Failing the process : '||p_launch_type);
354 END IF;
355
356 END IF ;
357
358
359 FND_FILE.PUT(FND_FILE.OUTPUT,'g_process_name ' || g_process_name );
360 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
361
362
363 --raise_application_error(-20000,'sri Failing the process');
364
365 if ( nvl(p_regenerate_batches,'N') = 'Y' ) then
366
367 l_reg_num := 10000;
368
369 begin
370
371 select 'Y' into l_reg_flag
372 from dual
373 where exists
374 ( select 'x' from pji_prg_batch where substr(batch_name,10,3) = '-R-' );
375 select to_number(substr(batch_name,13,5)) into l_reg_num
376 from pji_prg_batch
377 where substr(batch_name,10,3) = '-R-'
378 and rownum = 1;
379
380 if ( l_reg_num >= 99999 ) then
381 l_reg_num := 10000;
382 else
383 l_reg_num := l_reg_num + 1;
384 end if;
385
386 exception
387 when no_data_found then
388 l_reg_flag := 'N';
389 end;
390
391 create_upp_batches(p_temp_table_size,
392 p_num_of_projects,
393 p_incremental_mode,
394 nvl(p_regenerate_batches,'N'), -- sridhar_refresh
395 P_OPERATING_UNIT,
396 p_project_status) ; -- Call here the batch creation procedure
397
398 update pji_prg_group
399 set batch_name = 'UPP-BATCH'||'-R-'||l_reg_num||substr(batch_name,10,5) ;
400
401 update pji_prg_batch
402 set batch_name = 'UPP-BATCH'||'-R-'||l_reg_num||substr(batch_name,10,5) ;
403
404 --bug 7121511 start
405 update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
406 where c.prg_group in
407 ( select distinct b.prg_group
408 from pji_pjp_proj_batch_map a,
409 pji_prg_group b
410 where a.project_id = b.project_id
411 and b.prg_group is not null
412 );
413
414 update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
415 where c.project_id in
416 ( select distinct b.project_id
417 from pji_pjp_proj_batch_map a,
418 pji_prg_group b
419 where a.project_id = b.project_id
420 and b.prg_group is null
421 );
422 --bug 7121511 end
423
424 Insert into pji_prg_batch
425 ( batch_name,
426 wbs_total,
427 prg_total,
428 delta_total,
429 total_count,
430 project_count
431 )
432 select distinct batch_name ,0,0,0,0,0
433 from pji_prg_group where batch_name like '%-ERR';
434
435 -- bug 6276970 & 6505683
436 -- Sridhar Added below insert and update statement
437
438 update pji_prg_group c set c.batch_name = ( select d1.value
439 from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
440 pji_prg_group b1
441 where a1.project_id = b1.project_id
442 and c.batch_name like '%-ERR'
443 and b1.prg_group = c.prg_group
444 and to_number(substr(d1.name,8,instr(d1.name,'$',1) - 8)) = a1.worker_id -- Sridhar changed added substr june-11th V1_CHANGE
445 and d1.name like '%FROM_PROJECT'
446 and d1.value like 'UPP-BATCH%'
447 and b1.prg_group is not null -- Sridhar changed added not null condition june-11th V1_CHANGE
448 and rownum=1)
449 where
450 c.batch_name like '%-ERR'
451 and exists
452 ( select 'x'
453 from pji_system_parameters d2 , pji_pjp_proj_batch_map a2,
454 pji_prg_group b2
455 where a2.project_id = b2.project_id
456 and b2.prg_group = c.prg_group
457 and to_number(substr(d2.name,8,instr(d2.name,'$',1) - 8)) = a2.worker_id -- Sridhar changed added substr june-11th V1_CHANGE
458 and d2.name like '%FROM_PROJECT'
459 and b2.prg_group is not null -- Sridhar changed added not null condition june-11th V1_CHANGE
460 and d2.value like 'UPP-BATCH%' );
461
462 -- Sridhar changed june-11th Added new Update Statement START V1_CHANGE
463
464 update pji_prg_group c set c.batch_name = ( select d1.value
465 from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
466 pji_prg_group b1
467 where a1.project_id = b1.project_id
468 and c.batch_name like '%-ERR'
469 and b1.project_id = c.project_id
470 and to_number(substr(d1.name,8,instr(d1.name,'$',1) - 8)) = a1.worker_id
471 and d1.name like '%FROM_PROJECT'
472 and d1.value like 'UPP-BATCH%'
473 and b1.prg_group is null
474 and rownum=1)
475 where
476 c.batch_name like '%-ERR'
477 and exists
478 ( select 'x'
479 from pji_system_parameters d2 , pji_pjp_proj_batch_map a2,
480 pji_prg_group b2
481 where a2.project_id = b2.project_id
482 and b2.project_id = c.project_id
483 and to_number(substr(d2.name,8,instr(d2.name,'$',1) - 8)) = a2.worker_id
484 and d2.name like '%FROM_PROJECT'
485 and b2.prg_group is null
486 and d2.value like 'UPP-BATCH%' );
487
488 Insert into pji_prg_batch
489 ( batch_name,
490 wbs_total,
491 prg_total,
492 delta_total,
493 total_count,
494 project_count
495 )
496 select distinct batch_name ,0,0,0,0,0
497 from pji_prg_group a2 where a2.batch_name not in ( select c1.batch_name from pji_prg_batch c1 );
498
499 UPDATE pji_prg_batch c
500 SET c.curr_request_id =
501 (
502 select b.value
503 from pji_system_parameters a , pji_system_parameters b
504 where a.name like 'PJI_PJP%FROM_PROJECT'
505 and to_number(substr(a.name,8,instr(a.name,'$',1) - 8)) = to_number(substr(b.name,8,instr(b.name,'$',1) - 8))
506 and b.name like 'PJI_PJP%PJI_PJP%'
507 and c.batch_name = a.value
508 and b.value is not null
509 )
510 WHERE exists
511 ( select b.value
512 from pji_system_parameters a , pji_system_parameters b
513 where a.name like 'PJI_PJP%FROM_PROJECT'
514 and to_number(substr(a.name,8,instr(a.name,'$',1) - 8)) = to_number(substr(b.name,8,instr(b.name,'$',1) - 8))
515 and b.name like 'PJI_PJP%PJI_PJP%'
516 and c.batch_name = a.value
517 and b.value is not null ) ;
518
519 delete from pji_prg_batch a
520 where not exists
521 (
522 select 'x'
523 from pji_prg_group b
524 where a.batch_name = b.batch_name );
525
526 commit;
527 end if; /* if statement for re-generate batches */
528
529 l_time_in_seconds := nvl(p_wait_time_seconds,60*5) ;
530
531 if ( l_time_in_seconds <= 0 ) then
532 l_time_in_seconds := 60*5;
533 end if;
534
535 if ( nvl(p_num_of_batches,0) <= 0 ) then
536 l_num_of_batches := 50; /* Changed the default value from 3 to 50 for bug 7639329 */
537 else
538 l_num_of_batches := p_num_of_batches;
539 end if;
540
541 if ( nvl(p_num_parallel_runs,0) <= 0 ) then
542 l_num_parallel_runs := 3;
543 else
544 l_num_parallel_runs := p_num_parallel_runs;
545 end if;
546
547 l_count_batches := 0;
548
549 UPDATE_BATCH_CONC_STATUS
550 (
551 'Y' ,
552 l_count_batches ,
553 l_count_running ,
554 l_count_errored ,
555 l_count_completed ,
556 l_count_pending
557 );
558
559 curr_count_batches := l_count_batches;
560
561 --- Check if any rows exists in the pji_prg_batches that
562 --- are still pending or errored or the table is empty
563 ---- if YES then call the procedure that creates the batches.
564
565 BEGIN
566
567 SELECT count(*) into l_test
568 FROM pji_prg_batch
569 WHERE nvl(curr_request_status,'PENDING') <> 'COMPLETED'
570 and( exists -- added sridhar_refresh
571 ( select 'x' from PJI_LAUNCH_INCR
572 where incr_type = 'REFRESH' and g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_temp_table_size = 666666 )
573 or
574 exists -- added sridhar_refresh
575 ( select 'x' from PJI_LAUNCH_INCR
576 where incr_type <> 'REFRESH' and g_launch_type = 'UPPD' ) -- sridhar_phase_1 and p_temp_table_size <> 666666 )
577 );
578 IF ( l_test = 0 ) THEN
579
580 create_upp_batches(p_temp_table_size,
581 p_num_of_projects,
582 p_incremental_mode,
583 nvl(p_regenerate_batches,'N'), -- sridhar_refresh
584 P_OPERATING_UNIT,
585 p_project_status) ; -- Call here the batch creation procedure
586
587 UPDATE_BATCH_CONC_STATUS -- after creating
588 (
589 'Y' , -- value has to 'Y'
590 l_count_batches ,
591 l_count_running ,
592 l_count_errored ,
593 l_count_completed ,
594 l_count_pending
595 );
596
597 curr_count_batches := l_count_batches;
598
599 END IF;
600
601 EXCEPTION
602 WHEN NO_DATA_FOUND THEN
603 null;
604 WHEN OTHERS THEN
605 raise;
606 END ;
607
608
609 l_first_time_flag := 'N';
610
611 -- This is the loop will will run till the input number of batches are launched
612 --- or there are no batches left to be processed.
613
614 l_no_running_request := 'N';
615
616 LOOP
617
618 -- Check if the total number of current running process is equal or greater than
619 -- then number of batches that is requested via input parameters.
620 -- If yes then come-out and generate report saying currenly running/newly launched
621 -- batches equals or exceeds the input parameter
622
623 EXIT WHEN (curr_count_batches >= l_num_of_batches AND l_no_running_request = 'Y' )
624 OR (l_count_running = 0 AND l_count_rerrored >= PJI_PJP_SUM_MAIN.g_parallel_processes);
625 /* Added above condition for bug 8416116 */
626
627 -- Re-open the cursor and re-initialze the l_prg_batch_t record set
628 -- This is done to re-order the rows and get the PENDING and ERRORED
629 -- on top of the array.
630
631 l_Prg_Batch_t.delete;
632 OPEN prg_group;
633 FETCH prg_group BULK COLLECT
634 INTO l_Prg_Batch_t;
635
636 CLOSE prg_group;
637
638 --- This for loop will launch the concurrent program for the batches
639 --- that are PENDING or ERRORED earlier.
640 ---
641
642 if ( ( curr_count_batches < l_num_of_batches )
643 AND ( l_count_running < l_num_parallel_runs )
644 AND ((l_count_running+l_count_rerrored) < PJI_PJP_SUM_MAIN.g_parallel_processes)) THEN
645 /* Added above condition for bug 8416116 */
646
647 FOR i IN l_Prg_Batch_t.first..l_Prg_Batch_t.last LOOP
648
649 IF ( ( curr_count_batches < l_num_of_batches )
650 AND ( l_Prg_Batch_t(i).curr_request_status in ( 'PENDING','ERRORED' ))
651 AND ( l_count_running < l_num_parallel_runs )
652 AND ((l_count_running+l_count_rerrored) < PJI_PJP_SUM_MAIN.g_parallel_processes)) THEN
653 /* Added above condition for bug 8416116 */
654
655 l_request_id := -1;
656
657 DBMS_LOCK.SLEEP(60); --Bug 7235411 Added sleep to delay conc. request process to avoid deadlock.
658
659 if ( g_launch_type = 'RPPD' ) then -- sridhar_phase_1 ( p_temp_table_size = 666666 ) then -- sridhar_refresh add the call
660
661 --raise_application_error(-20000,'sri Failing the process RPPD ');
662
663 Fact_deletion(l_Prg_Batch_t(i).batch_name);
664 l_request_id := fnd_request.submit_request('PJI','PJI_PJP_SUMMARIZE_PRTL','','',FALSE,
665 'P', --p_run_type
666 null, --p_operating_unit
667 null, --p_project_organization_id
668 null, --p_project_type
669 l_Prg_Batch_t(i).batch_name ,
670 l_Prg_Batch_t(i).batch_name ,
671 null, --p_plan_type_id
672 null, --p_rbs_header_id
673 'ALL_TXN_TYPE', --p_transaction_type
674 'ALL_PLAN_VERSION' --p_plan_versions
675 );
676 -- sridhar_refresh add the call.
677
678 else
679 l_request_id := FND_REQUEST.SUBMIT_REQUEST
680 (
681 application => PJI_UTILS.GET_PJI_SCHEMA_NAME , -- Application Name
682 program => g_incr_disp_name, -- Program Name
683 sub_request => FALSE, -- Sub Request
684 argument1 => 'I', -- p_run_mode
685 argument2 => P_OPERATING_UNIT, -- p_operating_unit /* added for bug 9059519 */
686 argument3 => NULL, -- p_project_organization_id
687 argument4 => NULL, -- p_project_type
688 argument5 => l_Prg_Batch_t(i).batch_name , -- p_from_project_num
689 argument6 => l_Prg_Batch_t(i).batch_name , -- p_to_project_num
690 argument7 => NULL , -- p_plan_type_id
691 argument8 => NULL, -- p_rbs_header_id
692 argument9 => NULL, -- p_transaction_type
693 argument10 =>NULL, -- p_plan_version
694 argument11 => p_project_status -- new parameter Project Status
695 );
696 end if;
697
698 IF ( l_request_id > 0 ) THEN
699
700 UPDATE
701 pji_prg_batch
702 SET
703 curr_request_id = l_request_id ,
704 curr_request_status = 'RUNNING'
705 WHERE
706 batch_name = l_Prg_Batch_t(i).batch_name ;
707
708 INSERT INTO pji_prg_batch_log
709 ( run_date_key, run_date , request_id, batch_name , wbs_total, prg_total,
710 delta_total, total_count, project_count, custom1, custom2, custom3
711 )
712 values
713 ( to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS'), sysdate , l_request_id,
714 l_Prg_Batch_t(i).batch_name , l_Prg_Batch_t(i).wbs_total,
715 l_Prg_Batch_t(i).prg_total, l_Prg_Batch_t(i).delta_total,
716 l_Prg_Batch_t(i).total_count, l_Prg_Batch_t(i).project_count,
717 l_Prg_Batch_t(i).custom1, l_Prg_Batch_t(i).custom2,
718 l_Prg_Batch_t(i).custom3
719 );
720
721 curr_count_batches := curr_count_batches + 1;
722 l_count_running := l_count_running + 1;
723
724 ELSE
725
726 UPDATE
727 pji_prg_batch
728 SET
729 curr_request_id = l_request_id ,
730 message = 'Error calling FND_REQUEST.SUBMIT_REQUEST',
731 curr_request_status = 'SUBMIT-ERRORED'
732 WHERE
733 batch_name = l_Prg_Batch_t(i).batch_name ;
734
735 END IF ;
736
737 commit; -- commit the status after each update. This will allow
738 -- to check the status from outside.
739
740 g_stat_count := g_stat_count+1; /* Added for bug 8416116 */
741
742 END IF;
743
744 END LOOP; -- end of for loop for the batches
745
746 END IF;
747
748 /* Code added for bug 8416116 starts */
749 if (mod(g_stat_count,10) = 0) then
750
751 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
752 tabname => 'PJI_FP_XBS_ACCUM_F',
753 percent => 5,
754 degree => PJI_UTILS.GET_DEGREE_OF_PARALLELISM());
755
756 end if;
757 /* Code added for bug 8416116 ends */
758
759 -- After the loop the following two scenarios exists :
760 --- (1) All the batches are either running or completed
761 --- (2) Some batches might be pending or errored but the
762 --- EITHER the total batches submitted reached the
763 --- input parameter l_num_of_batches OR the total
764 --- number of process running parallely has reached
765 --- the input parameter l_num_parallel_runs
766
767
768 -- Check if any batches that are left pending or errored
769 -- if not then it means either all the batches are in completed or running status or
770 -- has issue submittiing now ( submit-errored ) .
771 -- then come out of the loop and complete the process
772
773 IF ( curr_count_batches < l_num_of_batches ) THEN
774
775 SELECT count(*) into l_test
776 FROM pji_prg_batch
777 WHERE nvl(curr_request_status,'PENDING') not in ( 'COMPLETED' ,'SUBMIT-ERRORED' ,'R-ERRORED') ;
778
779 IF ( l_test = 0 ) THEN
780
781 -- this means there are no batches that are left pending or errored and
782 -- which means we can set the curr_count_batches to its l_num_of_batches
783 -- and meet one of the exit criteria
784 -- But you have to be in the loop for any batch that is in running state
785 -- so that we can update the status and then exit
786
787 curr_count_batches := l_num_of_batches;
788
789 END IF;
790
791 END IF;
792
793 -- l_test > 0 means there are still batches that are either PENDING OR ERRORED
794 -- and these batches need to be submiited for processing
795
796 -- Now check if the total number of batches that are running are more than the
797 -- the input parameter , if YES then one exit criteria of the loop can is met
798 -- and we need to check the second criteria that is if there are any still running
799
800 IF ( curr_count_batches >= l_num_of_batches ) THEN
801
802 -- now check if there are any process that are running
803 -- if yes then we have continue in the loop and
804
805 SELECT count(*) into l_test
806 FROM pji_prg_batch
807 WHERE nvl(curr_request_status,'PENDING') in ( 'RUNNING') ;
808
809 IF ( l_test = 0 ) THEN
810 l_no_running_request := 'Y';
811 END IF;
812
813 END IF;
814
815 -- If l_test > 0 in the above if statement then it means that there are still
816 -- some batches that in running state
817 -- then continue the loop, else here both the exit criteria are met.
818 -- the loop should exit after updating the status
819
820 IF ( l_no_running_request = 'N' ) THEN
821 DBMS_LOCK.SLEEP(l_time_in_seconds);
822 END IF;
823
824 -- After Sleeping for the above set time , now re-update the status of the
825 -- current running processes.
826
827 -- the p_first_time_flag has to N here as we need not update the pending status
828 -- we should not change the count of the batches
829 UPDATE_BATCH_CONC_STATUS
830 (
831 'N' ,
832 l_count_batches ,
833 l_count_running ,
834 l_count_errored ,
835 l_count_completed ,
836 l_count_pending
837 );
838
839 /* Code added for bug 8416116 starts */
840 begin
841 select count(*) into
842 l_count_rerrored /* Modified for bug 9387564 */
843 from pji_prg_batch
844 where curr_request_status = 'R-ERRORED';
845
846 exception
847 when others then
848 l_count_rerrored := 0;
849 end;
850 /* Code added for bug 8416116 ends */
851
852 END LOOP;
853
854 -- call the upodate_batch_conc_status at the end
855 -- this is to convert the R-ERRORED into ERRORED
856
857 UPDATE_BATCH_CONC_STATUS
858 (
859 'Y' ,
860 l_count_batches ,
861 l_count_running ,
862 l_count_errored ,
863 l_count_completed ,
864 l_count_pending
865 );
866
867 -- Bug 14138486
868 delete from pji_system_parameters
869 where name = 'PJI_STAGE3_REQ_LUPPD';
870 commit;
871 -- End Bug 14138486
872
873 exception when others then
874
875 rollback;
876 retcode := 2;
877 errbuf := sqlerrm;
878 raise;
879
880 END LAUNCH_UPP_PROCESS;
881
882 PROCEDURE CREATE_UPP_BATCHES
883 (
884 p_wbs_temp_table_size in number ,
885 p_num_of_projects in number ,
886 p_incremental_mode in varchar2,
887 p_regenerate_batches in varchar2, --Goodman
888 P_OPERATING_UNIT in number ,
889 p_project_status in varchar2
890 )
891 is
892 pragma autonomous_transaction;
893
894 Cursor c_program_count is
895 select prg_group, count(distinct project_id) cnt, 'ALL' prg_type
896 from pa_proj_element_versions ver
897 where g_launch_type = 'UPPD' and -- sridhar_phase_1 p_wbs_temp_table_size <> 666666 and -- sridhar refresh
898 nvl(p_incremental_mode,'N') = 'N'
899 and object_type = 'PA_STRUCTURES'
900 and prg_group is not null
901 and exists ( select 'x' from
902 pa_projects_all p1
903 where p1.project_id = ver.project_id
904 and nvl(p1.org_id,-1) = nvl(P_OPERATING_UNIT,nvl(p1.org_id,-1)))
905 group by prg_group
906 union all
907 select /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N5) */
908 ver.PRG_GROUP , count( distinct ver.project_id ) cnt, 'PRG_CHANGE' prg_type
909 from
910 PJI_LAUNCH_INCR grp ,
911 PA_PROJ_ELEMENT_VERSIONS ver
912 where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) and -- sridhar_phase_1 p_wbs_temp_table_size = 666666 ) and -- sridhar refresh and
913 ver.object_type = 'PA_STRUCTURES' and
914 grp.incr_type in ( 'PRG_BASE', 'REFRESH') and
915 ver.prg_group = grp.prg_group
916 group by ver.prg_group
917 union all
918 select grp.prg_group ,count( distinct grp.project_id ) cnt, 'PRG_PARENT' prg_type
919 from
920 PJI_LAUNCH_INCR grp
921 where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) and -- sridhar_phase_1 p_wbs_temp_table_size = 666666 ) and -- sridhar refresh and
922 grp.incr_type not in ( 'PRG_BASE' , 'REFRESH') and
923 grp.prg_group > 0
924 group by grp.prg_group;
925
926 cursor c_program(x_prg_group number, x_prg_type varchar2 ) is
927 select prg_group, prg_level, project_id
928 from pa_proj_element_versions
929 where x_prg_type <> 'PRG_PARENT'
930 and object_type = 'PA_STRUCTURES'
931 and prg_group IS NOT NULL
932 and prg_group = x_prg_group
933 UNION
934 select /*+ index(pji_xbs_denorm pji_xbs_denorm_n5) */
935 prg_group, sup_level, sup_project_id
936 from pji_xbs_denorm
937 where x_prg_type <> 'PRG_PARENT'
938 and struct_type = 'PRG_BASE'
939 and prg_group is not null
940 and struct_type is null
941 and sub_level = sup_level
942 and prg_group = x_prg_group
943 UNION
944 select prg_group , prg_level , project_id
945 from PJI_LAUNCH_INCR grp
946 where grp.prg_group = x_prg_group
947 and x_prg_type = 'PRG_PARENT'
948 and grp.incr_type not in ( 'PRG_BASE' , 'REFRESH')
949 and grp.prg_group > 0 ;
950
951 l_batch_name varchar2(30):= 'UPP-BATCH-';
952 l_batch_var number := 1;
953 l_batch_size number := 5000;
954 l_cnt number:=0;
955 l_flag varchar2(1) := 'N';
956
957 l_wbs_prg_size number := 500000;
958
959 l_wbs_count number := 0;
960 l_prg_count number := 0;
961 l_delta_count number := 0;
962
963 l_wbs_total number := 0;
964 l_prg_total number := 0;
965 l_delta_total number := 0;
966
967 l_batch_count number := 0;
968
969 l_count number := 0;
970
971
972 l_prg_event_exists varchar2(1) := 'N';
973 l_prg_event_total number := 0;
974
975 /* Added for bug 8416116 */
976 l_prj_list PJI_LAUNCH_EXT.prg_proj_tbl;
977 l_curr_lines_cnt number := 0;
978 l_budget_lines_cnt number := 0;
979 L_context varchar2(20);
980 L_budget_lines_count number := 0;
981
982 --sridhar_refresh
983 r_count number;
984 u_count number;
985 Begin
986
987 -- sridhar_refresh start change
988
989
990
991 select count(1) into r_count
992 from dual
993 where exists
994 ( select 'x' from PJI_LAUNCH_INCR where incr_type = 'REFRESH' ) ;
995
996 select count(1) into u_count
997 from dual
998 where exists
999 ( select 'x' from PJI_LAUNCH_INCR where incr_type <> 'REFRESH' ) ;
1000
1001
1002
1003
1004 if ( ( g_launch_type = 'RPPD' ) ) then -- sridhar_phase_1 p_wbs_temp_table_size = 666666
1005
1006 if ( ( p_regenerate_batches = 'Y' ) or ( r_count = 0 ) ) then
1007
1008 create_refresh_proj_list(p_operating_unit);
1009
1010 end if;
1011 else
1012
1013 if ( ( p_incremental_mode = 'Y' ) or ( u_count = 0 ) ) then
1014 create_incr_project_list(p_operating_unit);
1015 end if;
1016
1017 end if ;
1018
1019 -- sridhar_refresh end change
1020
1021 delete from pji_prg_group;
1022 delete from pji_prg_batch;
1023
1024 commit;
1025
1026 if ( nvl(p_wbs_temp_table_size,0) <= 0 ) then
1027 l_wbs_prg_size := 200000;
1028 else
1029 l_wbs_prg_size := p_wbs_temp_table_size;
1030 end if;
1031
1032
1033 if ( nvl(p_num_of_projects,0) <= 0 ) then
1034 l_batch_size := 5000;
1035 else
1036 l_batch_size := p_num_of_projects;
1037 end if;
1038
1039 l_flag := 'N';
1040 For i in c_program_count Loop
1041
1042 begin
1043
1044 select 'Y'
1045 into l_prg_event_exists
1046 from dual
1047 where exists
1048 ( select 'x'
1049 from PA_PJI_PROJ_EVENTS_LOG log
1050 where
1051 log.EVENT_TYPE = 'PRG_CHANGE' and
1052 log.EVENT_OBJECT <> -1 and
1053 i.PRG_GROUP in (log.EVENT_OBJECT, log.ATTRIBUTE1)
1054 );
1055
1056 exception
1057 when no_data_found then
1058 l_prg_event_exists := 'N';
1059
1060 end;
1061
1062 select count(*)
1063 into l_wbs_count
1064 from pa_proj_element_versions A
1065 where l_prg_event_exists = 'Y' and
1066 a.parent_structure_version_id in
1067 ( select B.element_version_id
1068 from pa_proj_element_versions B
1069 where B.prg_group = i.prg_group
1070 and B.object_type = 'PA_STRUCTURES'
1071 )
1072 and A.object_type = 'PA_TASKS';
1073
1074 select lw_lf.lw_lf_count + pa_struct.pa_struct_count
1075 into l_prg_count
1076 from
1077 ( select count(*) lw_lf_count from
1078 (
1079 select
1080 distinct
1081 prg_node.prg_group,
1082 PRG_NODE.element_version_id sub_id,
1083 pvt_parent1.parent_structure_version_id sup_id ,
1084 pvt_parent1.project_id ,
1085 pvt_parent1.proj_element_id ,
1086 prt_parent.object_id_from1,
1087 prt_parent.relationship_type,
1088 ver.prg_level
1089 from PA_OBJECT_RELATIONSHIPS prt_parent,
1090 PA_PROJ_ELEMENT_VERSIONS ver ,
1091 PA_PROJ_ELEMENT_VERSIONS pvt_parent1 ,
1092 pa_proj_element_versions PRG_NODE
1093 where 1=1
1094 and PRG_NODE.prg_group = i.prg_group
1095 and prt_parent.object_id_to1 = PRG_NODE.element_version_id
1096 and PRG_NODE.object_type = 'PA_STRUCTURES'
1097 and prt_parent.object_type_from = 'PA_TASKS'
1098 and prt_parent.object_type_to = 'PA_STRUCTURES'
1099 and (
1100 prt_parent.relationship_type = 'LF'
1101 or
1102 prt_parent.relationship_type = 'LW'
1103 )
1104 and ver.element_version_id = prt_parent.object_id_from1
1105 and pvt_parent1.element_version_id = prt_parent.object_id_from1
1106 )
1107 where l_prg_event_exists = 'Y' ) lw_lf,
1108 (
1109 select count(*) pa_struct_count
1110 from pa_proj_element_versions a
1111 where l_prg_event_exists = 'Y'
1112 and a.prg_group = i.prg_group
1113 and a.object_type = 'PA_STRUCTURES'
1114 ) pa_struct ;
1115
1116 For j in c_program(i.prg_group,i.prg_type) loop
1117 Insert into pji_prg_group
1118 ( batch_name,
1119 prg_group,
1120 prg_level,
1121 project_id,
1122 parent_program_id
1123 ) values (
1124 l_batch_name||l_batch_var,
1125 i.prg_group,
1126 j.prg_level,
1127 j.project_id,
1128 null);
1129 End Loop;
1130 commit;
1131
1132 l_cnt := l_cnt + i.cnt;
1133
1134 l_delta_count := trunc((l_wbs_count + l_prg_count ) * .25 );
1135
1136 l_wbs_total := l_wbs_total + l_wbs_count ;
1137 l_prg_total := l_prg_total + l_prg_count ;
1138 l_delta_total := l_delta_total + l_delta_count ;
1139
1140 l_batch_count := l_batch_count + l_delta_count + l_wbs_count + l_prg_count ;
1141
1142 if ( l_prg_event_exists = 'Y' ) then
1143 l_prg_event_total := l_prg_event_total + l_delta_count + l_wbs_count + l_prg_count ;
1144 end if;
1145
1146 If ( ( l_cnt >= l_batch_size ) or ( l_batch_count >= l_wbs_prg_size ) ) then
1147
1148 Insert into pji_prg_batch
1149 ( batch_name,
1150 wbs_total,
1151 prg_total,
1152 delta_total,
1153 total_count,
1154 project_count,
1155 custom1
1156 ) values (
1157 l_batch_name||l_batch_var,
1158 l_wbs_total,
1159 l_prg_total,
1160 l_delta_total,
1161 l_batch_count,
1162 l_cnt,
1163 l_prg_event_total);
1164
1165 l_cnt := 0;
1166 l_batch_count := 0;
1167 l_wbs_total := 0;
1168 l_prg_total := 0;
1169 l_delta_total := 0;
1170
1171 l_batch_var := l_batch_var+1;
1172
1173 l_prg_event_total := 0;
1174
1175 commit;
1176
1177 End if;
1178
1179 End loop;
1180
1181 if ( l_cnt > 0 ) then
1182
1183 Insert into pji_prg_batch
1184 ( batch_name,
1185 wbs_total,
1186 prg_total,
1187 delta_total,
1188 total_count,
1189 project_count
1190 ) values (
1191 l_batch_name||l_batch_var,
1192 l_wbs_total,
1193 l_prg_total,
1194 l_delta_total,
1195 l_batch_count,
1196 l_cnt);
1197
1198 l_cnt := 0;
1199
1200 commit;
1201
1202 end if ;
1203
1204 l_batch_count := null;
1205 l_wbs_total := null;
1206 l_prg_total := null;
1207 l_delta_total := null;
1208
1209
1210 l_cnt := 0;
1211 l_batch_var := l_batch_var + 1;
1212
1213 /* Call to Launch process client extension PJI_LAUNCH_EXT.PROJ_LIST
1214 The client extension returns the following parameters :
1215 p_prg_proj_tbl - plsql tables containing the project_id's to be processed
1216 p_context - UPGRADE or INCREMENTAL
1217 p_budget_lines_count - If p_context = UPGRADE, this is the number of budget
1218 lines that should be used to divide batches. This
1219 value will ONLY be conidered when p_context = UPGRADE.
1220 The following combination of extension parameters will be considered valid
1221 and will be used for creating batches :
1222 1. p_context = UPGRADE, p_budget_lines_count > 0, p_prg_proj_tbl.count > 0
1223 2. p_context = UPGRADE, p_budget_lines_count > 0, p_prg_proj_tbl.count = 0
1224 3. p_context = INCREMENTAL/UPGRADE(p_budget_lines_count=0), p_prg_proj_tbl.count > 0
1225 4. p_context = INCREMENTAL(p_budget_lines_count=0), p_prg_proj_tbl.count = 0
1226 */
1227
1228 /* Code added for bug 8416116 starts */
1229 PJI_LAUNCH_EXT.PROJ_LIST(p_prg_proj_tbl => l_prj_list,
1230 p_context => l_context,
1231 p_budget_lines_count => l_budget_lines_count);
1232
1233
1234 if (l_prj_list.count > 0 and
1235 l_context = 'UPGRADE' and
1236 l_budget_lines_count > 0 ) then
1237
1238 l_budget_lines_cnt := 0;
1239
1240 FOR a IN l_prj_list.first..l_prj_list.last LOOP
1241
1242 l_curr_lines_cnt := 0;
1243 select count(*)
1244 into l_curr_lines_cnt
1245 from pa_budget_versions pbv,
1246 pa_budget_lines pbl
1247 where pbv.project_id = l_prj_list(a) and
1248 pbv.budget_version_id = pbl.budget_version_id;
1249
1250 if (( l_budget_lines_cnt + l_curr_lines_cnt
1251 <= l_budget_lines_count) or l_cnt = 0) THEN
1252
1253 Insert into pji_prg_group
1254 ( batch_name,
1255 prg_group,
1256 prg_level,
1257 project_id,
1258 parent_program_id
1259 ) values (
1260 l_batch_name||l_batch_var,
1261 Null,
1262 Null,
1263 l_prj_list(a),
1264 null );
1265
1266 l_budget_lines_cnt := l_budget_lines_cnt + l_curr_lines_cnt;
1267 l_cnt := l_cnt + 1;
1268
1269 else
1270 Insert into pji_prg_batch
1271 ( batch_name,
1272 wbs_total,
1273 prg_total,
1274 delta_total,
1275 total_count,
1276 project_count
1277 ) values (
1278 l_batch_name||l_batch_var,
1279 l_wbs_total,
1280 l_prg_total,
1281 l_delta_total,
1282 l_batch_count,
1283 l_cnt);
1284
1285 l_cnt := 0;
1286 l_batch_var := l_batch_var + 1;
1287 l_budget_lines_cnt := 0;
1288
1289 Insert into pji_prg_group
1290 ( batch_name,
1291 prg_group,
1292 prg_level,
1293 project_id,
1294 parent_program_id
1295 ) values (
1296 l_batch_name||l_batch_var,
1297 Null,
1298 Null,
1299 l_prj_list(a),
1300 null );
1301
1302 l_budget_lines_cnt := l_budget_lines_cnt + l_curr_lines_cnt;
1303 l_cnt := l_cnt + 1;
1304
1305 end if; /* if (( l_budget_lines_cnt + l_curr_lines_cnt
1306 <= l_budget_lines_count) or l_cnt = 0) */
1307
1308 end loop;
1309
1310 elsif (l_prj_list.count = 0 and l_context = 'UPGRADE' and
1311 l_budget_lines_count > 0 ) then
1312
1313 l_budget_lines_cnt := 0;
1314
1315 For k in (select a.project_id
1316 from pa_projects_all a
1317 where nvl(p_incremental_mode,'N') = 'N'
1318 and template_flag = 'N' -- Bug 9059688
1319 and nvl(a.org_id,-1) = nvl(p_operating_unit,nvl(a.org_id,-1))
1320 --added for 12.1.3 feature for new parameter Project Status
1321 --commenting out the below clause as not needed in this scenario
1322 /* and nvl(a.project_status_code,'PS') =
1323 nvl(p_project_status,nvl(a.project_status_code,'PS'))*/
1324 and not exists
1325 (select 'x' from pji_prg_group b
1326 where a.project_id = b.project_id)
1327 union all
1328 select project_id
1329 from pji_launch_incr a
1330 where nvl(p_incremental_mode,'N') = 'Y'
1331 and incr_type like 'PROJ%'
1332 and prg_group = -1
1333 and not exists
1334 (select 'x' from pji_prg_group b
1335 where a.project_id = b.project_id )) loop
1336
1337 l_curr_lines_cnt := 0;
1338
1339 select count(*)
1340 into l_curr_lines_cnt
1341 from pa_budget_versions pbv,
1342 pa_budget_lines pbl
1343 where pbv.project_id = k.project_id
1344 and pbv.budget_version_id = pbl.budget_version_id;
1345
1346 if (( l_budget_lines_cnt + l_curr_lines_cnt
1347 <= l_budget_lines_count) or l_cnt = 0) THEN
1348
1349 Insert into pji_prg_group
1350 ( batch_name,
1351 prg_group,
1352 prg_level,
1353 project_id,
1354 parent_program_id
1355 ) values (
1356 l_batch_name||l_batch_var,
1357 Null,
1358 Null,
1359 k.project_id,
1360 null );
1361
1362 l_budget_lines_cnt := l_budget_lines_cnt + l_curr_lines_cnt;
1363 l_cnt := l_cnt + 1;
1364
1365 else
1366 Insert into pji_prg_batch
1367 ( batch_name,
1368 wbs_total,
1369 prg_total,
1370 delta_total,
1371 total_count,
1372 project_count
1373 ) values (
1374 l_batch_name||l_batch_var,
1375 l_wbs_total,
1376 l_prg_total,
1377 l_delta_total,
1378 l_batch_count,
1379 l_cnt);
1380
1381 l_cnt := 0;
1382 l_batch_var := l_batch_var + 1;
1383 l_budget_lines_cnt := 0;
1384
1385 Insert into pji_prg_group
1386 ( batch_name,
1387 prg_group,
1388 prg_level,
1389 project_id,
1390 parent_program_id
1391 ) values (
1392 l_batch_name||l_batch_var,
1393 Null,
1394 Null,
1395 k.project_id,
1396 null );
1397
1398 l_budget_lines_cnt := l_budget_lines_cnt + l_curr_lines_cnt;
1399 l_cnt := l_cnt + 1;
1400
1401 end if; /* if (( l_budget_lines_cnt + l_curr_lines_cnt
1402 <= l_budget_lines_count) or l_cnt = 0) */
1403
1404 end loop;
1405
1406 elsif (l_prj_list.count > 0 and (l_context = 'INCREMENTAL' or l_context = 'UPGRADE')) then
1407
1408 FOR a IN l_prj_list.first..l_prj_list.last LOOP
1409
1410 Insert into pji_prg_group
1411 ( batch_name,
1412 prg_group,
1413 prg_level,
1414 project_id,
1415 parent_program_id
1416 ) values (
1417 l_batch_name||l_batch_var,
1418 Null,
1419 Null,
1420 l_prj_list(a),
1421 null );
1422
1423 l_cnt := l_cnt + 1;
1424
1425 if l_cnt >= l_batch_size then
1426
1427 Insert into pji_prg_batch
1428 ( batch_name,
1429 wbs_total,
1430 prg_total,
1431 delta_total,
1432 total_count,
1433 project_count
1434 ) values (
1435 l_batch_name||l_batch_var,
1436 l_wbs_total,
1437 l_prg_total,
1438 l_delta_total,
1439 l_batch_count,
1440 l_cnt);
1441
1442 l_cnt := 0;
1443 l_batch_var := l_batch_var + 1;
1444
1445 end if;
1446
1447 end loop;
1448
1449 else
1450 /* Code added for bug 8416116 ends */
1451
1452 For k in ( select a.project_id
1453 from pa_projects_all a
1454 where ( nvl(p_incremental_mode,'N') = 'N' and g_launch_type <> 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size <> 666666
1455 and template_flag = 'N' -- Bug 9059688
1456 and nvl(a.org_id,-1) = nvl(p_operating_unit,nvl(a.org_id,-1))
1457 and
1458 nvl(a.project_status_code,'PS')=nvl(p_project_status,nvl(a.project_status_code,'PS'))
1459 and not exists
1460 ( select 'x'
1461 from pji_prg_group b where a.project_id = b.project_id )
1462 union all
1463 select a.project_id
1464 from pji_launch_incr a, pa_projects_all b /* added for bug 9712797 */
1465 where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size = 666666
1466 and a. project_id = b.project_id /* added for bug 9712797 */
1467 -- Moved following condition to the next line for UPPD case. Not applicable for RPPD case
1468 -- and nvl(b.project_status_code,'PS') = nvl(p_project_status,nvl(b.project_status_code,'PS')) /* added for bug 9712797 */
1469 and (
1470 ( incr_type like 'PROJ%' and g_launch_type = 'UPPD'
1471 and nvl(b.project_status_code,'PS') = nvl(p_project_status,nvl(b.project_status_code,'PS')) /* added for bug 9712797 */
1472
1473 ) -- sridhar_phase_1 p_wbs_temp_table_size <> 666666
1474 or
1475 ( incr_type like 'REF%' and g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size = 666666
1476 )
1477 and prg_group = -1
1478 and not exists
1479 ( select 'x'
1480 from pji_prg_group b where a.project_id = b.project_id )
1481 )
1482 loop
1483 Insert into pji_prg_group
1484 ( batch_name,
1485 prg_group,
1486 prg_level,
1487 project_id,
1488 parent_program_id
1489 ) values (
1490 l_batch_name||l_batch_var,
1491 Null,
1492 Null,
1493 k.project_id,
1494 null );
1495
1496 l_cnt := l_cnt + 1;
1497
1498 if l_cnt >= l_batch_size then
1499
1500 Insert into pji_prg_batch
1501 ( batch_name,
1502 wbs_total,
1503 prg_total,
1504 delta_total,
1505 total_count,
1506 project_count
1507 ) values (
1508 l_batch_name||l_batch_var,
1509 l_wbs_total,
1510 l_prg_total,
1511 l_delta_total,
1512 l_batch_count,
1513 l_cnt);
1514
1515 l_cnt := 0;
1516 l_batch_var := l_batch_var + 1;
1517 end if;
1518 End loop;
1519
1520 end if; /* Added for bug 8416116 */
1521
1522 if l_cnt >= 0 then
1523
1524 Insert into pji_prg_batch
1525 ( batch_name,
1526 wbs_total,
1527 prg_total,
1528 delta_total,
1529 total_count,
1530 project_count
1531 ) values (
1532 l_batch_name||l_batch_var,
1533 l_wbs_total,
1534 l_prg_total,
1535 l_delta_total,
1536 l_batch_count,
1537 l_cnt);
1538
1539 l_cnt := 0;
1540 end if;
1541
1542 commit;
1543
1544 Exception
1545 When Others then
1546 rollback;
1547 raise;
1548 End CREATE_UPP_BATCHES;
1549
1550 PROCEDURE print_line(p_line in varchar2) IS
1551 BEGIN
1552
1553 FND_FILE.PUT(FND_FILE.OUTPUT,p_line );
1554 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
1555
1556 END;
1557
1558
1559 PROCEDURE generate_validation_report(p_operating_unit in number) IS
1560 -- Reporting script for Activity data:
1561
1562 l_data_type varchar2(30);
1563 l_run_type varchar2(30);
1564 l_data_type_parent varchar2(30);
1565
1566 l_ATC_COST number ;
1567 l_BACKLOG number ;
1568 l_BUD_COST number ;
1569 l_BUD_REV number ;
1570 l_WP_COST number ; /* Added for bug 12328939 */
1571 l_FC_COST number ; /* Added for bug 12328939 */
1572 l_proj_count number ;
1573
1574 CURSOR get_proj_val IS
1575 select rpad(p1.segment1,30)||','||
1576 decode(ATC_COST,0,'--','ITD Actual Cost')||
1577 decode(BACKLOG,0,',--',', ITD Backlog')||
1578 decode(BUD_COST,0,',--',', At Completion Cost Budget')||
1579 decode(BUD_REV,0,',--',', At Completion Revenue Budget') ||
1580 decode(WP_COST,0,',--',', Workplan Actuals') || /* Added for bug 12328939 */
1581 decode(FC_COST,0,',--',', Cost Forecast Actuals') project_line /* Added for bug 12328939 */
1582 from
1583 (select
1584 project_id ,
1585 sum(decode(incr_type, 'VAL_ACT_COST',1,0)) ATC_COST ,
1586 sum(decode(incr_type, 'VAL_BACKLOG',1,0)) BACKLOG ,
1587 sum(decode(incr_type, 'VAL_BUD_COST',1,0)) BUD_COST ,
1588 sum(decode(incr_type, 'VAL_BUD_REV',1,0)) BUD_REV,
1589 sum(decode(incr_type, 'VAL_ACT_WP_COST',1,0)) WP_COST, /* Added for bug 12328939 */
1590 sum(decode(incr_type, 'VAL_ACT_FC_COST',1,0)) FC_COST /* Added for bug 12328939 */
1591 from
1592 ( select distinct incr_type , project_id
1593 FROM PJI_LAUNCH_INCR where incr_type like 'VAL%'
1594 ) GROUP BY project_id ) t1 , pa_projects_all p1
1595 where p1.project_id = t1.project_id
1596 order by p1.segment1 ;
1597
1598 BEGIN
1599
1600 select
1601 sum(decode(incr_type, 'VAL_ACT_COST',1,0)) ATC_COST ,
1602 sum(decode(incr_type, 'VAL_BACKLOG',1,0)) BACKLOG ,
1603 sum(decode(incr_type, 'VAL_BUD_COST',1,0)) BUD_COST ,
1604 sum(decode(incr_type, 'VAL_BUD_REV',1,0)) BUD_REV ,
1605 sum(decode(incr_type, 'VAL_ACT_WP_COST',1,0)) WP_COST , /* Added for bug 12328939 */
1606 sum(decode(incr_type, 'VAL_ACT_FC_COST',1,0)) FC_COST , /* Added for bug 12328939 */
1607 count( distinct project_id ) proj_count
1608 into
1609 l_ATC_COST,
1610 l_BACKLOG ,
1611 l_BUD_COST ,
1612 l_BUD_REV ,
1613 l_WP_COST , /* Added for bug 12328939 */
1614 l_FC_COST , /* Added for bug 12328939 */
1615 l_proj_count
1616 from
1617 ( select distinct incr_type , project_id
1618 FROM PJI_LAUNCH_INCR where incr_type like 'VAL%'
1619 );
1620
1621 print_line('----------------------------------------------------------------------------');
1622 print_line(' Reporting Measures Validation Report ');
1623 print_line('----------------------------------------------------------------------------');
1624
1625 print_line(' ');
1626 print_line('------------------------- ');
1627 print_line('Scope of the Validations: ');
1628 print_line('------------------------- ');
1629 print_line(' ');
1630 print_line(' This report validates the following measures as shown on the overview page : ');
1631 print_line(' (a) ITD Actual Cost ');
1632 print_line(' (b) ITD Backlog ');
1633 print_line(' (c) At Completion Cost Budget ');
1634 print_line(' (d) At Completion Revenue Budget ');
1635
1636 print_line(' ');
1637 print_line('---------------------------- ');
1638 print_line('Validations Summary Report : ');
1639 print_line('---------------------------- ');
1640 print_line(' ');
1641 print_line(' No. of Projects Having inconsistency in one or more aboves measures are : '||l_proj_count);
1642 print_line(' ');
1643 print_line(' No. of Projects having inconsistency in ITD Actual Cost : '||l_ATC_COST);
1644 print_line(' No. of Projects having inconsistency in ITD Backlog : '||l_BACKLOG);
1645 print_line(' No. of Projects having inconsistency in At Completion Cost Budget : '||l_BUD_COST);
1646 print_line(' No. of Projects having inconsistency in At Completion Revenue Budget : '||l_BUD_REV);
1647 print_line(' No. of Projects having inconsistency in Workplan Actuals : '||l_WP_COST); /* Added for bug 12328939 */
1648 print_line(' No. of Projects having inconsistency in Cost Forecast Actuals : '||l_FC_COST); /* Added for bug 12328939 */
1649
1650 print_line(' ');
1651 print_line('----------------------------------------------------------------------------- ');
1652 print_line('Detail List of Projects having incosistency in one or more above measures : ');
1653 print_line('----------------------------------------------------------------------------- ');
1654 print_line(' ');
1655
1656 print_line(' ');
1657 print_line('----------------------------------------------------------------------------- ');
1658 print_line('Project Number , Inconistent Measures ');
1659 print_line('----------------------------------------------------------------------------- ');
1660 print_line(' ');
1661
1662 FOR get_proj_rec in get_proj_val
1663 LOOP
1664 print_line(get_proj_rec.project_line);
1665 END LOOP;
1666
1667 DELETE FROM PJI_LAUNCH_INCR where incr_type like 'VAL%';
1668
1669 END generate_validation_report;
1670
1671 PROCEDURE Create_refresh_proj_list(p_operating_unit in number) IS
1672 -- Reporting script for Activity data:
1673
1674 l_data_type varchar2(30);
1675 l_run_type varchar2(30);
1676 l_data_type_parent varchar2(30);
1677 l_tolerance_amt number := 0;
1678
1679 CURSOR get_proj_backlog IS
1680 SELECT DISTINCT pa.project_id project_id
1681 FROM (SELECT a.project_id ,
1682 SUM (a.total_accrued_amount) total_rev
1683 FROM pa_summary_project_fundings a,
1684 pa_projects_all ppa
1685 where PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
1686 and a.project_id = ppa.project_id
1687 GROUP BY a.project_id
1688 )
1689 base_rev,
1690 (SELECT pf.project_id
1691 ,sum(pf.projfunc_allocated_amount) Net_fund
1692 FROM pa_project_fundings pf
1693 ,pa_agreements_all agr
1694 ,pa_project_customers cust,
1695 pa_projects_all ppa
1696 WHERE 1=1
1697 and pf.project_id = ppa.project_id
1698 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
1699 AND pf.agreement_id = agr.agreement_id
1700 AND pf.project_id = cust.project_id
1701 AND pf.BUDGET_TYPE_CODE = 'BASELINE'
1702 AND agr.customer_id = cust.customer_id
1703 AND NVL(cust.bill_another_project_flag,'N') <> 'Y'
1704 AND pf.date_allocated is not null
1705 GROUP BY pf.project_id
1706 ) base_fund,
1707 (SELECT f.project_id,
1708 sum(initial_funding_amount
1709 + additional_funding_amount
1710 + cancelled_funding_amount
1711 + funding_adjustment_amount) total_fund
1712 FROM pji_ac_xbs_accum_f f,
1713 pa_projects_all ppa
1714 WHERE f.project_id = ppa.project_id
1715 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
1716 AND f.currency_code = ppa.projfunc_currency_code
1717 AND time_id = -1
1718 AND wbs_rollup_flag = 'N'
1719 AND initial_funding_amount IS NOT NULL
1720 GROUP BY f.project_id
1721 )
1722 report ,
1723 pa_projects_all pa,
1724 pa_project_statuses pps
1725 WHERE pa.project_id = base_fund.project_id
1726 and pa.project_id = base_rev.project_id
1727 AND base_rev.project_id = report.project_id
1728 AND base_fund.project_id = report.project_id
1729 AND pa.project_status_code = pps.project_status_code
1730 AND pa.org_id = nvl(p_operating_unit, pa.org_id )
1731 AND pa.template_flag = 'N'
1732 and nvl((base_fund.net_fund-base_rev.total_rev), -99) <>
1733 nvl((report.total_fund - base_rev.total_rev),-99) ;
1734
1735 -- Reporting script for Approved Cost Budgets:
1736 CURSOR get_proj_budget_cost IS
1737 select distinct costbud.project_id project_id
1738 from (SELECT budgets.project_id, reporting.project_status_code,
1739 budgets.cost BUDGET_COST,
1740 reporting.cost REPORTING_COST,
1741 budgets.cost - reporting.cost cost_diff_budgets
1742 FROM (SELECT ver.project_id,
1743 sum(ver.burdened_cost) cost
1744 FROM pa_budget_versions ver,
1745 pa_projects_all ppa
1746 WHERE ver.budget_status_code = 'B'
1747 and ver.project_id = ppa.project_id
1748 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
1749 AND ver.current_flag = 'Y'
1750 AND ver.fin_plan_type_id IN (SELECT fin_plan_type_id
1751 FROM pa_fin_plan_types_b
1752 WHERE approved_cost_plan_type_flag ='Y')
1753 group by ver.project_id) budgets
1754 LEFT OUTER JOIN
1755 (SELECT f.project_id, p.project_status_code,
1756 Sum(brdn_cost) cost
1757 FROM pji_fp_xbs_accum_f f,
1758 pa_projects_all p
1759 WHERE plan_version_id = -3
1760 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(p.project_status_code,'STATUS_REPORTING') = 'Y'
1761 AND rbs_aggr_level = 'T'
1762 AND wbs_rollup_flag = 'Y'
1763 AND calendar_type = 'A'
1764 AND time_id = -1
1765 AND project_element_id = apps.pa_project_structure_utils.Get_fin_structure_id(f.project_id)
1766 AND plan_type_id IN (SELECT fin_plan_type_id
1767 FROM pa_fin_plan_types_b
1768 WHERE approved_cost_plan_type_flag = 'Y')
1769 AND f.currency_code = p.projfunc_currency_code
1770 AND f.project_id = p.project_id
1771 GROUP BY f.project_id, p.project_status_code) reporting
1772 ON budgets.project_id = reporting.project_id ) costbud, pa_projects_all pa
1773 where nvl(costbud.BUDGET_COST, 0) <> nvl(costbud.REPORTING_COST, 0)
1774 AND pa.project_id = costbud.project_id
1775 AND pa.template_flag = 'N'
1776 AND pa.org_id = nvl(p_operating_unit, pa.org_id )
1777 AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = pa.project_id )
1778 or l_run_type <> 'REFRESH');
1779
1780 -- Reporting script for Approved Revenue Budgets:
1781 CURSOR get_proj_budget_rev IS
1782 select distinct revbud.project_id project_id from (
1783 SELECT budgets.project_id, reporting.project_status_code,
1784 budgets.revenue BUDGET_REVENUE,
1785 reporting.revenue REPORTING_REVENUE,
1786 budgets.revenue - reporting.revenue rev_diff_budgets
1787 FROM (SELECT ver.project_id,
1788 sum(ver.revenue) revenue
1789 FROM pa_budget_versions ver,
1790 pa_projects_all ppa
1791 WHERE ver.budget_status_code = 'B'
1792 and ver.project_id = ppa.project_id
1793 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
1794 AND ver.current_flag = 'Y'
1795 AND ver.fin_plan_type_id IN (SELECT fin_plan_type_id
1796 FROM pa_fin_plan_types_b
1797 WHERE approved_rev_plan_type_flag ='Y')
1798 group by ver.project_id) budgets
1799 LEFT OUTER JOIN
1800 (SELECT f.project_id, p.project_status_code,
1801 Sum(revenue) revenue
1802 FROM pji_fp_xbs_accum_f f,
1803 pa_projects_all p
1804 WHERE plan_version_id = -3
1805 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(p.project_status_code,'STATUS_REPORTING') = 'Y'
1806 AND rbs_aggr_level = 'T'
1807 AND wbs_rollup_flag = 'Y'
1808 AND calendar_type = 'A'
1809 AND time_id = -1
1810 AND project_element_id = apps.pa_project_structure_utils.Get_fin_structure_id(f.project_id)
1811 AND plan_type_id IN (SELECT fin_plan_type_id
1812 FROM pa_fin_plan_types_b
1813 WHERE approved_rev_plan_type_flag = 'Y')
1814 AND f.currency_code = p.projfunc_currency_code
1815 AND f.project_id = p.project_id
1816 group by f.project_id, p.project_status_code) reporting
1817 ON budgets.project_id = reporting.project_id ) revbud, pa_projects_all pa
1818 where nvl(revbud.BUDGET_REVENUE, 0) <> nvl(revbud.REPORTING_REVENUE, 0)
1819 AND pa.project_id = revbud.project_id
1820 AND pa.template_flag = 'N'
1821 AND pa.org_id = nvl(p_operating_unit, pa.org_id )
1822 AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = pa.project_id )
1823 or l_run_type <> 'REFRESH');
1824
1825 -- For Actuals
1826 CURSOR get_proj_cost IS
1827 SELECT distinct p1.project_id
1828 FROM pa_projects_all p1 ,
1829 (SELECT
1830 /*+ ordered */
1831 SUM(cdl.burdened_cost) amt,
1832 pa.project_id
1833 FROM pa_projects_all pa,
1834 pa_cost_distribution_lines_all cdl
1835 WHERE pa.template_flag = 'N'
1836 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(pa.project_status_code,'STATUS_REPORTING') = 'Y'
1837 AND pa.project_id = cdl.project_id
1838 AND cdl.line_type = 'R'
1839 AND cdl.pji_summarized_flag IS NULL
1840 AND pa.org_id = nvl(p_operating_unit, pa.org_id )
1841 GROUP BY pa.project_id
1842 ) cdl1 ,
1843 (SELECT
1844 /*+ ordered */
1845 brdn_cost amt,
1846 f.project_id
1847 FROM pa_projects_all p ,
1848 pji_fp_xbs_accum_f f
1849 WHERE p.project_id = f.project_id
1850 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(p.project_status_code,'STATUS_REPORTING') = 'Y'
1851 AND f.currency_code = p.projfunc_currency_code
1852 AND f.plan_type_id = -1
1853 AND f.rbs_version_id = -1
1854 AND f.calendar_type = 'A'
1855 AND f.rbs_aggr_level = 'T'
1856 AND f.wbs_rollup_flag = 'Y'
1857 AND f.prg_rollup_flag = 'N'
1858 AND period_type_id = 2048
1859 AND time_id = -1
1860 AND project_element_id = pa_project_structure_utils.Get_fin_structure_id(f.project_id)
1861 AND p.template_flag = 'N'
1862 AND p.org_id = nvl(p_operating_unit, p.org_id )
1863 ) fact1
1864 WHERE p1.project_id = cdl1.project_id (+)
1865 AND p1.project_id = fact1.project_id (+)
1866 AND NVL(cdl1.amt,0) <> NVL(fact1.amt,0)
1867 AND (abs(NVL(cdl1.amt,0) - NVL(fact1.amt,0)) > l_tolerance_amt)
1868 AND p1.org_id = nvl(p_operating_unit, p1.org_id )
1869 AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
1870 or l_run_type <> 'REFRESH') ;
1871
1872 /* Added for bug 12328939 starts */
1873 -- For Workplan Actuals
1874 CURSOR get_proj_wp_cost IS
1875 SELECT distinct p1.project_id
1876 FROM pa_projects_all p1 ,
1877 (SELECT project_id,
1878 Sum(Nvl(act_labor_hrs,0)) act_ppl_effort,
1879 Sum(Nvl(act_equip_hrs,0)) act_eq_effort,
1880 Sum(Nvl(act_raw_cost,0)) act_raw_cost,
1881 Sum(Nvl(act_brdn_cost,0)) act_brdn_cost
1882 FROM pji_fp_xbs_accum_f f
1883 WHERE plan_version_id IN (SELECT budget_version_id
1884 FROM pa_budget_versions bv
1885 WHERE bv.project_id = f.project_id
1886 AND project_structure_version_id =
1887 PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(f.project_id))
1888 AND rbs_version_id = -1 AND time_id = -1
1889 AND rbs_aggr_level = 'T' AND wbs_rollup_flag = 'Y' AND prg_rollup_flag = 'N'
1890 AND bitand(curr_record_type_id,4) = 4
1891 AND project_element_id = (SELECT proj_element_id
1892 from pa_proj_element_versions e
1893 where e.project_id = f.project_id
1894 and element_version_id =
1895 PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(f.project_id))
1896 GROUP BY project_id) rep,
1897 (SELECT res.project_id project_id,
1898 Sum(Decode(res.resource_class_code,'PEOPLE',Nvl(init_quantity,0),0)) act_ppl_effort,
1899 Sum(Decode(res.resource_class_code,'EQUIPMENT',Nvl(init_quantity,0),0)) act_eq_effort,
1900 Sum(Nvl(init_raw_cost,0)) act_raw_cost,
1901 Sum(Nvl(init_burdened_cost,0)) act_brdn_cost
1902 FROM pa_budget_lines bl, pa_resource_assignments res
1903 WHERE bl.budget_version_id IN (SELECT budget_version_id
1904 FROM pa_budget_versions bv
1905 WHERE bv.project_id = res.project_id
1906 AND project_structure_version_id =
1907 PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(bv.project_id))
1908 AND bl.budget_version_id = res.budget_version_id
1909 AND bl.resource_assignment_id = res.resource_assignment_id
1910 GROUP BY res.project_id) base
1911 WHERE base.project_id = rep.project_id(+)
1912 AND p1.project_id = base.project_id
1913 AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (p1.project_status_code,
1914 'STATUS_REPORTING') = 'Y'
1915 AND p1.template_flag = 'N'
1916 AND (base.act_ppl_effort <> nvl(rep.act_ppl_effort,0)
1917 OR base.act_eq_effort <> nvl(rep.act_eq_effort,0)
1918 OR base.act_raw_cost <> nvl(rep.act_raw_cost,0)
1919 OR base.act_brdn_cost <> nvl(rep.act_brdn_cost,0))
1920 AND p1.org_id = nvl(p_operating_unit, p1.org_id )
1921 AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
1922 or l_run_type <> 'REFRESH') ;
1923 -- For Cost Forecast Actuals
1924 CURSOR get_proj_fc_cost IS
1925 SELECT distinct p1.project_id
1926 FROM pa_projects_all p1 ,
1927 (SELECT project_id,
1928 Sum(Nvl(act_labor_hrs,0)) act_ppl_effort,
1929 Sum(Nvl(act_equip_hrs,0)) act_eq_effort,
1930 Sum(Nvl(act_raw_cost,0)) act_raw_cost,
1931 Sum(Nvl(act_brdn_cost,0)) act_brdn_cost
1932 FROM pji_fp_xbs_accum_f f
1933 WHERE plan_type_id IN (SELECT fin_plan_type_id
1934 FROM pa_fin_plan_types_b
1935 WHERE primary_cost_forecast_flag = 'Y')
1936 AND plan_version_id = -3
1937 AND rbs_version_id = -1 AND time_id = -1
1938 AND rbs_aggr_level = 'T' AND wbs_rollup_flag = 'Y' AND prg_rollup_flag = 'N'
1939 AND bitand(curr_record_type_id,4) = 4
1940 AND project_element_id = pa_project_structure_utils.Get_fin_structure_id(f.project_id)
1941 GROUP BY project_id) rep,
1942 (SELECT res.project_id project_id,
1943 Sum(Decode(res.resource_class_code,'PEOPLE',Nvl(init_quantity,0),0)) act_ppl_effort,
1944 Sum(Decode(res.resource_class_code,'EQUIPMENT',Nvl(init_quantity,0),0)) act_eq_effort,
1945 Sum(Nvl(init_raw_cost,0)) act_raw_cost,
1946 Sum(Nvl(init_burdened_cost,0)) act_brdn_cost
1947 FROM pa_budget_lines bl, pa_resource_assignments res
1948 WHERE bl.budget_version_id IN (SELECT budget_version_id
1949 FROM pa_budget_versions bv
1950 WHERE bv.project_id = res.project_id
1951 AND budget_status_code = 'B'
1952 AND current_flag = 'Y'
1953 AND fin_plan_type_id IN (SELECT fin_plan_type_id
1954 FROM pa_fin_plan_types_b
1955 WHERE primary_cost_forecast_flag = 'Y'))
1956 AND bl.budget_version_id = res.budget_version_id
1957 AND bl.resource_assignment_id = res.resource_assignment_id
1958 GROUP BY res.project_id) base
1959 WHERE base.project_id = rep.project_id(+)
1960 AND p1.project_id = base.project_id
1961 AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (p1.project_status_code, 'STATUS_REPORTING') = 'Y'
1962 AND p1.template_flag = 'N'
1963 AND (base.act_ppl_effort <> nvl(rep.act_ppl_effort,0)
1964 OR base.act_eq_effort <> nvl(rep.act_eq_effort,0)
1965 OR base.act_raw_cost <> nvl(rep.act_raw_cost,0)
1966 OR base.act_brdn_cost <> nvl(rep.act_brdn_cost,0))
1967 AND p1.org_id = nvl(p_operating_unit, p1.org_id )
1968 AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
1969 or l_run_type <> 'REFRESH') ;
1970 /* Added for bug 12328939 ends */
1971
1972 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1973 v_project_id num_tbl_type;
1974
1975 i number :=0;
1976
1977 l_prg_count number ;
1978 l_prg_parent number;
1979
1980
1981
1982 BEGIN
1983 if ( g_validation_process = 'N') then
1984
1985 DELETE FROM PJI_LAUNCH_INCR;
1986
1987 l_run_type := 'REFRESH';
1988 l_data_type := 'REFRESH';
1989 l_data_type_parent := 'REF_PARENT';
1990
1991 else
1992 DELETE FROM PJI_LAUNCH_INCR; /* Added for bug 12328939 */
1993
1994 l_run_type := 'VALIDATION';
1995 l_data_type := 'VAL';
1996 l_data_type_parent := 'VAL_PARENT';
1997
1998 end if ;
1999
2000 if ( l_run_type = 'VALIDATION') then
2001
2002 l_data_type := 'VAL_BACKLOG';
2003
2004 end if;
2005
2006 OPEN get_proj_backlog;
2007 FETCH get_proj_backlog bulk collect into v_project_id;
2008
2009 IF v_project_id.COUNT > 0 THEN
2010 FORALL i in v_project_id.FIRST..v_project_id.LAST
2011 INSERT INTO PJI_LAUNCH_INCR
2012 (incr_type, project_id, prg_group ,prg_level )
2013 values
2014 (l_data_type,
2015 v_project_id(i),
2016 -1,
2017 -1);
2018 COMMIT;
2019 END IF;
2020 CLOSE get_proj_backlog;
2021
2022 v_project_id.delete;
2023
2024
2025 if ( l_run_type = 'VALIDATION') then
2026
2027 l_data_type := 'VAL_BUD_COST';
2028
2029 end if;
2030
2031
2032 OPEN get_proj_budget_cost;
2033 FETCH get_proj_budget_cost bulk collect into v_project_id;
2034
2035 IF v_project_id.COUNT > 0 THEN
2036 FORALL i in v_project_id.FIRST..v_project_id.LAST
2037 INSERT INTO PJI_LAUNCH_INCR
2038 (incr_type, project_id,prg_group , prg_level )
2039 values
2040 (l_data_type,
2041 v_project_id(i),
2042 -1,
2043 -1);
2044 COMMIT;
2045 END IF;
2046 CLOSE get_proj_budget_cost;
2047
2048 v_project_id.delete;
2049
2050
2051 if ( l_run_type = 'VALIDATION') then
2052
2053 l_data_type := 'VAL_BUD_REV';
2054
2055 end if;
2056
2057
2058 OPEN get_proj_budget_rev;
2059 FETCH get_proj_budget_rev bulk collect into v_project_id;
2060
2061 IF v_project_id.COUNT > 0 THEN
2062 FORALL i in v_project_id.FIRST..v_project_id.LAST
2063 INSERT INTO PJI_LAUNCH_INCR
2064 (incr_type, project_id, prg_group ,prg_level )
2065 values
2066 (l_data_type,
2067 v_project_id(i),
2068 -1,
2069 -1);
2070 COMMIT;
2071 END IF;
2072 CLOSE get_proj_budget_rev;
2073
2074 v_project_id.delete;
2075
2076
2077 if ( l_run_type = 'VALIDATION') then
2078
2079 l_data_type := 'VAL_ACT_COST';
2080
2081 end if;
2082
2083 l_tolerance_amt := nvl(to_number(PJI_UTILS.GET_PARAMETER('PJI_TOLERANCE_AMT')),0);
2084
2085 OPEN get_proj_cost;
2086 FETCH get_proj_cost bulk collect into v_project_id;
2087
2088 IF v_project_id.COUNT > 0 THEN
2089 FORALL i in v_project_id.FIRST..v_project_id.LAST
2090 INSERT INTO PJI_LAUNCH_INCR
2091 (incr_type, project_id, prg_group ,prg_level )
2092 values
2093 (l_data_type,
2094 v_project_id(i),
2095 -1,
2096 -1);
2097 COMMIT;
2098 END IF;
2099 CLOSE get_proj_cost;
2100
2101 v_project_id.delete;
2102
2103 /* Added for bug 12328939 */
2104 if ( l_run_type = 'VALIDATION') then
2105
2106 l_data_type := 'VAL_ACT_WP_COST';
2107
2108 end if;
2109
2110
2111 OPEN get_proj_wp_cost;
2112 FETCH get_proj_wp_cost bulk collect into v_project_id;
2113
2114 IF v_project_id.COUNT > 0 THEN
2115 FORALL i in v_project_id.FIRST..v_project_id.LAST
2116 INSERT INTO PJI_LAUNCH_INCR
2117 (incr_type, project_id, prg_group ,prg_level )
2118 values
2119 (l_data_type,
2120 v_project_id(i),
2121 -1,
2122 -1);
2123 COMMIT;
2124 END IF;
2125 CLOSE get_proj_wp_cost;
2126
2127 v_project_id.delete;
2128
2129 if ( l_run_type = 'VALIDATION') then
2130
2131 l_data_type := 'VAL_ACT_FC_COST';
2132
2133 end if;
2134
2135 OPEN get_proj_fc_cost;
2136 FETCH get_proj_fc_cost bulk collect into v_project_id;
2137
2138 IF v_project_id.COUNT > 0 THEN
2139 FORALL i in v_project_id.FIRST..v_project_id.LAST
2140 INSERT INTO PJI_LAUNCH_INCR
2141 (incr_type, project_id, prg_group ,prg_level )
2142 values
2143 (l_data_type,
2144 v_project_id(i),
2145 -1,
2146 -1);
2147 COMMIT;
2148 END IF;
2149 CLOSE get_proj_fc_cost;
2150
2151 v_project_id.delete;
2152 /* Added for bug 12328939 */
2153
2154 if ( l_run_type = 'REFRESH' ) then
2155
2156
2157 UPDATE PJI_LAUNCH_INCR a
2158 SET a.INCR_TYPE = 'PROJ_WRK'
2159 WHERE
2160 exists
2161 ( select 'x'
2162 from pa_proj_element_versions c
2163 where a.project_id = c.project_id
2164 and c.object_type = 'PA_STRUCTURES'
2165 AND c.prg_group IS NOT NULL ) ;
2166
2167 l_prg_count := 0;
2168 LOOP
2169
2170 l_prg_parent := 0;
2171
2172 FOR PRG_PARENT_NODE IN
2173 (
2174 SELECT /*+ ordered index(a PA_PROJ_ELEMENT_VERSIONS_N6) index(b PA_OBJECT_RELATIONSHIPS_N4) */
2175 DISTINCT
2176 a0.project_id child_proj_id , a.prg_level child_prg_level,
2177 c.project_id parent_proj_id , c.prg_group , c.prg_level parent_prg_level
2178 FROM pji_launch_incr a0,
2179 pa_proj_element_versions a,
2180 pa_object_relationships b,
2181 pa_proj_element_versions c
2182 WHERE a0.incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' )
2183 AND a.project_id = a0.project_id --51956 --5062 --5269 --51954
2184 AND a.prg_group IS NOT NULL
2185 AND a.object_type = 'PA_STRUCTURES'
2186 AND a.object_type = b.object_type_to
2187 AND a.element_version_id = b.object_id_to1
2188 AND c.element_version_id = b.object_id_from1
2189 AND b.relationship_type IN ('LW', 'LF')
2190 ) LOOP
2191
2192 l_prg_parent := 1;
2193
2194 UPDATE PJI_LAUNCH_INCR
2195 SET prg_group = PRG_PARENT_NODE.prg_group,
2196 incr_type = decode(incr_type,'PROJ_WRK','PROJ_B_PARENT','PROJ_PARENT'),
2197 prg_level = PRG_PARENT_NODE.child_prg_level
2198 WHERE project_id = PRG_PARENT_NODE.child_proj_id
2199 AND incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2200
2201 INSERT INTO PJI_LAUNCH_INCR a
2202 (a.incr_type, a.prg_group , a.project_id , a.prg_level)
2203 select
2204 'PROJ_WRK_NEW',
2205 PRG_PARENT_NODE.prg_group,
2206 PRG_PARENT_NODE.parent_proj_id ,
2207 PRG_PARENT_NODE.parent_prg_level
2208 from dual
2209 where not exists
2210 (
2211 select 'x'
2212 from PJI_LAUNCH_INCR b
2213 where PRG_PARENT_NODE.parent_proj_id = b.project_id
2214 and PRG_PARENT_NODE.prg_group = b.prg_group
2215 and PRG_PARENT_NODE.parent_prg_level = b.prg_level
2216 and b.incr_type in ('PROJ_PARENT','PROJ_B_PARENT','PROJ_WRK_NEW')
2217 );
2218
2219 commit;
2220
2221 END LOOP;
2222
2223 exit when l_prg_parent = 0;
2224
2225 l_prg_count := l_prg_count+1;
2226
2227 END LOOP;
2228
2229 UPDATE PJI_LAUNCH_INCR
2230 SET incr_type = decode(incr_type,'PROJ_WRK',l_data_type,l_data_type_parent)
2231 WHERE incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2232
2233
2234 end if; -- if run_type = 'REFRESH'
2235
2236 /**
2237 if ( l_run_type = 'VALIDATION') then
2238
2239 DELETE FROM pji_data_validation;
2240 insert into pji_data_validation select * from pji_launch_incr where incr_type like 'VAL%';
2241 DELETE FROM PJI_LAUNCH_INCR where incr_type like 'VAL%';
2242
2243 end if ; -- if run_type = 'VALIDATION'
2244 **/
2245
2246 END Create_refresh_proj_list;
2247
2248 PROCEDURE Fact_Deletion ( p_batch_name varchar2 ) IS
2249
2250
2251 cursor get_projects_for_fact_deletion(p_batchname varchar2) is
2252 select project_id
2253 from pji_prg_group
2254 where batch_name = p_batchname;
2255
2256 TYPE vr1_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2257 v_batchname vr1_tbl_type;
2258
2259 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2260 v_project_id num_tbl_type;
2261
2262 j number :=0;
2263 i number :=0;
2264 Begin
2265
2266
2267
2268 open get_projects_for_fact_deletion(p_batch_name);
2269 fetch get_projects_for_fact_deletion bulk collect into v_project_id;
2270
2271
2272
2273 If v_project_id.COUNT > 0 THEN
2274 FOR i in v_project_id.FIRST..v_project_id.LAST loop
2275 delete PJI_FP_XBS_ACCUM_F where project_id = v_project_id(i);
2276 commit;
2277
2278 delete PJI_AC_XBS_ACCUM_F where project_id = v_project_id(i);
2279 commit;
2280
2281 END LOOP;
2282 close get_projects_for_fact_deletion;
2283 END IF;
2284
2285 commit;
2286
2287
2288
2289
2290 End Fact_Deletion;
2291
2292 PROCEDURE CREATE_INCR_PROJECT_LIST (p_operating_unit in number) IS
2293
2294 l_prg_parent number;
2295 l_prg_count number;
2296
2297 BEGIN
2298
2299 -- NOTES :
2300 --- This procedure created rows into the table pji_launch_incr
2301 --- Following type of rows are created
2302 -- INCR_TYPE :
2303 --- PRG_BASE :-> These are PRG_CHANGE events
2304 -- PROJ_BASE :-> These are proejcts that has incremental data
2305 -- PROJ_PRG :-> These are the updated rows of PROJ_BASE to
2306 -- PROJ_PRG , which belongs to program and the
2307 -- corresponding program is part of the
2308 -- PRG_BASE rows.
2309 -- PROJ_B_PARENT :-> These are the updated rows of PROJ_BASE to
2310 -- PROJ_PRG , which belongs to program and the
2311 -- corresponding program is not part of the
2312 -- PRG_BASE rows.
2313 -- PROJ_PARENT :-> These are the rows that parents of the rows
2314 -- of type PROJ_PRG.
2315 --
2316 --
2317 DELETE FROM PJI_LAUNCH_INCR;
2318 --
2319 --
2320 COMMIT;
2321
2322 --
2323 -- INSERT 001
2324 --
2325 INSERT INTO PJI_LAUNCH_INCR
2326 (incr_type, prg_group , project_id, prg_level )
2327 SELECT /*+ ordered use_nl(log ver ) index(ver PA_PROJ_ELEMENT_VERSIONS_N5) */
2328 DISTINCT
2329 'PRG_BASE' incr_type ,
2330 ver.PRG_GROUP ,
2331 - 1 project_id ,
2332 -1 prg_level
2333 FROM
2334 PA_PJI_PROJ_EVENTS_LOG LOG ,
2335 PA_PROJ_ELEMENT_VERSIONS ver
2336 WHERE
2337 ver.object_type = 'PA_STRUCTURES' AND
2338 log.EVENT_TYPE = 'PRG_CHANGE' AND
2339 log.EVENT_OBJECT <> - 1 AND
2340 ver.PRG_GROUP IN (log.EVENT_OBJECT, log.ATTRIBUTE1)
2341 and exists ( select 'x' from
2342 pa_projects_all p1
2343 where p1.project_id = ver.project_id
2344 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2345
2346 --
2347 --
2348 -- INSERT 002
2349 --
2350 --
2351 INSERT INTO PJI_LAUNCH_INCR
2352 (incr_type, prg_group , project_id , prg_level)
2353 SELECT
2354 DISTINCT
2355 'PROJ_BASE',
2356 - 1,
2357 to_number(log.attribute1),
2358 - 1
2359 FROM
2360 PA_PJI_PROJ_EVENTS_LOG LOG
2361 WHERE
2362 log.EVENT_TYPE = 'PRG_CHANGE' AND
2363 log.EVENT_OBJECT = - 1
2364 AND NOT EXISTS
2365 (SELECT 'x' FROM
2366 PJI_LAUNCH_INCR grp2
2367 WHERE grp2.incr_type = 'PROJ_BASE'
2368 AND grp2.prg_group = - 1
2369 AND grp2.project_id = to_number(log.attribute1) )
2370 and exists ( select 'x' from
2371 pa_projects_all p1
2372 where p1.project_id = to_number(log.attribute1)
2373 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2374
2375 --
2376 -- INSERT 003
2377 --
2378 --
2379 INSERT INTO PJI_LAUNCH_INCR
2380 (incr_type, prg_group , project_id, prg_level )
2381 SELECT
2382 DISTINCT
2383 'PROJ_BASE',
2384 - 1,
2385 to_number(log.attribute1),
2386 -1
2387 FROM
2388 PA_PJI_PROJ_EVENTS_LOG LOG
2389 WHERE
2390 log.EVENT_TYPE IN ('RBS_ASSOC', 'RBS_PRG' )
2391 AND NOT EXISTS
2392 (SELECT 'x' FROM
2393 PJI_LAUNCH_INCR grp2
2394 WHERE grp2.incr_type = 'PROJ_BASE'
2395 AND grp2.prg_group = - 1
2396 AND grp2.project_id = to_number(log.attribute1) )
2397 and exists ( select 'x' from
2398 pa_projects_all p1
2399 where p1.project_id = to_number(log.attribute1)
2400 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2401
2402 --
2403 -- INSERT 003
2404 --
2405 --
2406 INSERT INTO PJI_LAUNCH_INCR
2407 (incr_type, prg_group , project_id , prg_level)
2408 SELECT
2409 DISTINCT
2410 'PROJ_BASE',
2411 - 1,
2412 asg.project_id,
2413 -1
2414 FROM
2415 PA_PJI_PROJ_EVENTS_LOG LOG,
2416 PA_RBS_PRJ_ASSIGNMENTS asg
2417 WHERE
2418 log.EVENT_TYPE = 'RBS_PUSH' AND
2419 asg.RBS_VERSION_ID IN (log.EVENT_OBJECT, log.ATTRIBUTE2)
2420 AND NOT EXISTS
2421 (SELECT 'x' FROM
2422 PJI_LAUNCH_INCR grp2
2423 WHERE grp2.incr_type = 'PROJ_BASE'
2424 AND grp2.prg_group = - 1
2425 AND grp2.project_id = asg.project_id )
2426 and exists ( select 'x' from
2427 pa_projects_all p1
2428 where p1.project_id = asg.project_id
2429 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2430
2431 --
2432 -- INSERT 004
2433 --
2434 --
2435 INSERT INTO PJI_LAUNCH_INCR
2436 (incr_type, prg_group , project_id , prg_level)
2437 SELECT
2438 DISTINCT
2439 'PROJ_BASE',
2440 - 1,
2441 asg.project_id,
2442 - 1
2443 FROM
2444 PA_PJI_PROJ_EVENTS_LOG LOG,
2445 PA_RBS_PRJ_ASSIGNMENTS asg
2446 WHERE
2447 log.EVENT_TYPE = 'RBS_DELETE' AND
2448 asg.RBS_VERSION_ID = log.EVENT_OBJECT
2449 AND NOT EXISTS
2450 (SELECT 'x' FROM
2451 PJI_LAUNCH_INCR grp2
2452 WHERE grp2.incr_type = 'PROJ_BASE'
2453 AND grp2.prg_group = - 1
2454 AND grp2.project_id = asg.project_id )
2455 and exists ( select 'x' from
2456 pa_projects_all p1
2457 where p1.project_id = asg.project_id
2458 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1))) ;
2459
2460 -- INSERT 005
2461 --
2462 --
2463 /* Not required. based on the reply from Kranthi
2464
2465 insert into PJI_LAUNCH_INCR
2466 ( incr_type, prg_group , project_id )
2467 select
2468 distinct
2469 'PROJ_BASE',
2470 -1 ,
2471 project_id
2472 from
2473 pji_fm_extr_plan_lines a1
2474 where not exists
2475 ( select 'x' from
2476 PJI_LAUNCH_INCR grp2
2477 where grp2.incr_type = 'PROJ_BASE'
2478 and grp2.prg_group = -1
2479 and grp2.project_id = a1.project_id );
2480
2481 */
2482 --
2483 --
2484 -- INSERT 006
2485 --
2486 --
2487 INSERT INTO PJI_LAUNCH_INCR
2488 (incr_type, prg_group , project_id , prg_level)
2489 SELECT
2490 DISTINCT
2491 'PROJ_BASE',
2492 - 1,
2493 project_id,
2494 - 1
2495 FROM PJI_FM_AGGR_FIN7 a1
2496 WHERE NOT EXISTS
2497 (SELECT 'x' FROM
2498 PJI_LAUNCH_INCR grp2
2499 WHERE grp2.incr_type = 'PROJ_BASE'
2500 AND grp2.prg_group = - 1
2501 AND grp2.project_id = a1.project_id )
2502 and exists ( select 'x' from
2503 pa_projects_all p1
2504 where p1.project_id = a1.project_id
2505 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2506
2507 -- INSERT 007
2508 --
2509 INSERT INTO PJI_LAUNCH_INCR
2510 (incr_type, prg_group , project_id , prg_level )
2511 SELECT
2512 DISTINCT
2513 'PROJ_BASE',
2514 - 1,
2515 project_id ,
2516 - 1
2517 FROM PJI_FM_AGGR_ACT4 a1
2518 WHERE NOT EXISTS
2519 (SELECT 'x' FROM
2520 PJI_LAUNCH_INCR grp2
2521 WHERE grp2.incr_type = 'PROJ_BASE'
2522 AND grp2.prg_group = - 1
2523 AND grp2.project_id = a1.project_id )
2524 and exists ( select 'x' from
2525 pa_projects_all p1
2526 where p1.project_id = a1.project_id
2527 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2528
2529 INSERT INTO PJI_LAUNCH_INCR
2530 (incr_type, prg_group , project_id , prg_level )
2531 SELECT
2532 DISTINCT
2533 'PROJ_BASE',
2534 - 1,
2535 project_id,
2536 - 1
2537 FROM PA_BUDGET_VERSIONS a1
2538 WHERE budget_status_code = 'B' AND
2539 pji_summarized_flag = 'P' AND
2540 NOT EXISTS
2541 (SELECT 'x' FROM
2542 PJI_LAUNCH_INCR grp2
2543 WHERE grp2.incr_type = 'PROJ_BASE'
2544 AND grp2.prg_group = - 1
2545 AND grp2.project_id = a1.project_id )
2546 and exists ( select 'x' from
2547 pa_projects_all p1
2548 where p1.project_id = a1.project_id
2549 and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
2550
2551 --
2552 /*
2553 -- pji_fm_aggr_fin8 table is not required , based on the reply from shane.
2554
2555 insert into PJI_LAUNCH_INCR
2556 ( incr_type, prg_group , project_id )
2557 select
2558 distinct
2559 'PROJ_BASE',
2560 -1 ,
2561 project_id
2562 from pji_fm_aggr_fin8 a1
2563 where
2564 not exists
2565 ( select 'x' from
2566 PJI_LAUNCH_INCR grp2
2567 where grp2.incr_type = 'PROJ_BASE'
2568 and grp2.prg_group = -1
2569 and grp2.project_id = a1.project_id );
2570 */
2571 --
2572 ---
2573 COMMIT;
2574
2575 /** New Changes July-9th START */
2576 --
2577 --
2578 --
2579 BEGIN
2580
2581 UPDATE PJI_LAUNCH_INCR a
2582 SET a.INCR_TYPE = 'PROJ_PRG'
2583 WHERE a.INCR_TYPE = 'PROJ_BASE'
2584 AND EXISTS
2585 (SELECT /*+ ordered index(c PA_PROJ_ELEMENT_VERSIONS_N6) */ 'x'
2586 FROM
2587 pa_proj_element_versions c,
2588 PJI_LAUNCH_INCR b
2589 WHERE a.project_id = c.project_id
2590 and c.object_type = 'PA_STRUCTURES'
2591 AND c.prg_group IS NOT NULL
2592 AND c.prg_group = b.prg_group
2593 AND b.incr_type = 'PRG_BASE'
2594 );
2595
2596 COMMIT;
2597
2598 EXCEPTION
2599 WHEN no_data_found THEN
2600 NULL;
2601 END ;
2602
2603 -- Update the rows to working rows, since the corresponding parent need to be fetched.
2604 --
2605
2606 UPDATE PJI_LAUNCH_INCR a
2607 SET a.INCR_TYPE = 'PROJ_WRK'
2608 WHERE a.INCR_TYPE = 'PROJ_BASE' and
2609 exists
2610 ( select 'x'
2611 from pa_proj_element_versions c
2612 where a.project_id = c.project_id
2613 and c.object_type = 'PA_STRUCTURES'
2614 AND c.prg_group IS NOT NULL ) ;
2615
2616 commit;
2617
2618 l_prg_count := 0;
2619 LOOP
2620
2621 l_prg_parent := 0;
2622
2623 FOR PRG_PARENT_NODE IN
2624 (
2625 SELECT /*+ ordered index(a PA_PROJ_ELEMENT_VERSIONS_N6) index(b PA_OBJECT_RELATIONSHIPS_N4) */
2626 DISTINCT
2627 a0.project_id child_proj_id , a.prg_level child_prg_level,
2628 c.project_id parent_proj_id , c.prg_group , c.prg_level parent_prg_level
2629 FROM pji_launch_incr a0,
2630 pa_proj_element_versions a,
2631 pa_object_relationships b,
2632 pa_proj_element_versions c
2633 WHERE a0.incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' )
2634 AND a.project_id = a0.project_id --51956 --5062 --5269 --51954
2635 AND a.prg_group IS NOT NULL
2636 AND a.object_type = 'PA_STRUCTURES'
2637 AND a.object_type = b.object_type_to
2638 AND a.element_version_id = b.object_id_to1
2639 AND c.element_version_id = b.object_id_from1
2640 AND b.relationship_type IN ('LW', 'LF')
2641 ) LOOP
2642
2643 l_prg_parent := 1;
2644
2645 UPDATE PJI_LAUNCH_INCR
2646 SET prg_group = PRG_PARENT_NODE.prg_group,
2647 incr_type = decode(incr_type,'PROJ_WRK','PROJ_B_PARENT','PROJ_PARENT'),
2648 prg_level = PRG_PARENT_NODE.child_prg_level
2649 WHERE project_id = PRG_PARENT_NODE.child_proj_id
2650 AND incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2651
2652 INSERT INTO PJI_LAUNCH_INCR a
2653 (a.incr_type, a.prg_group , a.project_id , a.prg_level)
2654 select
2655 'PROJ_WRK_NEW',
2656 PRG_PARENT_NODE.prg_group,
2657 PRG_PARENT_NODE.parent_proj_id ,
2658 PRG_PARENT_NODE.parent_prg_level
2659 from dual
2660 where not exists
2661 (
2662 select 'x'
2663 from PJI_LAUNCH_INCR b
2664 where PRG_PARENT_NODE.parent_proj_id = b.project_id
2665 and PRG_PARENT_NODE.prg_group = b.prg_group
2666 and PRG_PARENT_NODE.parent_prg_level = b.prg_level
2667 and b.incr_type in ('PROJ_PARENT','PROJ_B_PARENT','PROJ_WRK_NEW')
2668 );
2669
2670 commit;
2671
2672 END LOOP;
2673
2674 exit when l_prg_parent = 0;
2675
2676 l_prg_count := l_prg_count+1;
2677
2678 END LOOP;
2679
2680 UPDATE PJI_LAUNCH_INCR
2681 SET incr_type = decode(incr_type,'PROJ_WRK','PROJ_BASE','PROJ_PARENT')
2682 WHERE incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
2683
2684
2685 commit;
2686
2687 --
2688 -- INSERT 008
2689 --
2690 --
2691 --
2692 INSERT INTO PJI_LAUNCH_INCR
2693 (incr_type, prg_group , project_id ,prg_level)
2694 SELECT /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N6) */
2695 DISTINCT
2696 'PROJ_BASE_MAP' incr_type ,
2697 nvl(ver.PRG_GROUP,-1) ,
2698 map1.project_id project_id ,
2699 nvl(ver.prg_level,-1)
2700 FROM
2701 PJI_PJP_PROJ_BATCH_MAP map1 ,
2702 PA_PROJ_ELEMENT_VERSIONS ver
2703 WHERE
2704 ver.object_type = 'PA_STRUCTURES' AND
2705 ver.project_id = map1.project_id ;
2706 --AND
2707 -- NOT EXISTS (
2708 -- SELECT 'x' FROM PJI_LAUNCH_INCR grp2
2709 -- WHERE grp2.prg_group = nvl(ver.prg_group,-1) AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) and
2710 -- NOT EXISTS (
2711 -- SELECT 'x' FROM PJI_LAUNCH_INCR grp3
2712 -- WHERE grp3.project_id = map1.project_id AND grp3.incr_type like 'PROJ%' ) ;
2713 --
2714 --
2715 --
2716
2717 UPDATE PJI_LAUNCH_INCR a
2718 set a.prg_group = -2
2719 where incr_type = 'PROJ_BASE_MAP'
2720 and exists
2721 (
2722 SELECT 'x' FROM PJI_LAUNCH_INCR grp2
2723 WHERE grp2.prg_group = nvl(a.prg_group,-1) AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) ;
2724
2725 /** New Changes July-9th END */
2726
2727 --test_logmessage('LAUNCH', 300,'Before Final Insert CREATE_INCR_PROJECT_LIST');
2728 --
2729 -- commented , since this is not required.
2730 -- as the strategy is to pickup only those parent projects for all those projects which has
2731 -- incremental runs and the program to which these projects belongs does not have PRG_CHANGE
2732 -- event.
2733 --
2734 --
2735 -- INSERT 009
2736 --
2737 -- INSERT INTO PJI_LAUNCH_INCR
2738 -- (incr_type, prg_group , project_id )
2739 -- SELECT /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N3) */
2740 -- DISTINCT
2741 -- 'PRG_BASE' incr_type ,
2742 -- ver.PRG_GROUP ,
2743 -- - 1 project_id
2744 -- FROM
2745 -- PJI_LAUNCH_INCR grp ,
2746 -- PA_PROJ_ELEMENT_VERSIONS ver
2747 -- WHERE
2748 -- ver.object_type = 'PA_STRUCTURES' AND
2749 -- grp.incr_type = 'PROJ_BASE' AND
2750 -- grp.prg_group = - 1 AND
2751 -- ver.project_id = grp.project_id AND
2752 -- ver.prg_group IS NOT NULL AND
2753 -- NOT EXISTS (
2754 -- SELECT 'x' FROM PJI_LAUNCH_INCR grp2
2755 -- WHERE grp2.prg_group = ver.prg_group AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) ;
2756 ----
2757 ----
2758 ----
2759 COMMIT;
2760
2761 EXCEPTION
2762 WHEN OTHERS THEN
2763 ROLLBACK;
2764 RAISE;
2765 END CREATE_INCR_PROJECT_LIST;
2766
2767
2768 end PJI_LAUNCH_UPP_MAIN;