[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_XBS_ACCUM_MAINT
Source
1 PACKAGE BODY Pji_Fm_Xbs_Accum_Maint AS
2 /* $Header: PJIPMNTB.pls 120.54.12010000.5 2008/12/12 03:36:10 sugupta ship $ */
3
4
5 ---------------------------------------------------------------------------
6 ------ Global vars..
7 ---------------------------------------------------------------------------
8
9 g_package_name VARCHAR2(100) := 'PJI_FM_XBS_ACCUM_MAINT';
10 g_update_num_rows_limit NUMBER := 1; -- 1000;
11 g_smart_rows_deleted NUMBER :=0; --maintains the number of rows deleted in delete smart slice api
12 g_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
13 g_deffered_mode VARCHAR2(1) :='N';
14 g_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
15 Invalid_Excep Exception ;
16
17
18 ---------------------------------------------------------------------------
19 ------ Specs of internal apis..
20 ---------------------------------------------------------------------------
21
22 PROCEDURE CHECK_BUDGET_VERSION_EXISTS (
23 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type() ) ;
24
25 PROCEDURE PRINT_TIME(p_tag IN VARCHAR2 := NULL);
26
27 PROCEDURE CLEANUP_TEMP_TABLES;
28
29 PROCEDURE WBS_HEADERS_LOCK (
30 p_fp_version_ids IN OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
31 , p_context IN VARCHAR2
32 , x_return_status OUT NOCOPY VARCHAR2
33 , x_msg_code OUT NOCOPY VARCHAR2 );
34
35
36 PROCEDURE GET_HDRS_TOLOCK_FOR_UPDATE (
37 x_fp_version_ids OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
38 , x_return_status OUT NOCOPY VARCHAR2
39 , x_msg_code OUT NOCOPY VARCHAR2 );
40
41
42 PROCEDURE GET_EVENT_IDS (
43 p_fp_version_ids IN SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
44 , p_operation_type IN VARCHAR2 := NULL
45 , x_event_ids OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
46 , x_return_status OUT NOCOPY VARCHAR2
47 , x_msg_code OUT NOCOPY VARCHAR2 );
48
49
50 PROCEDURE DELETE_EVENTS (
51 p_event_ids IN SYSTEM.pa_num_tbl_type
52 , x_return_status OUT NOCOPY VARCHAR2
53 , x_msg_code OUT NOCOPY VARCHAR2 );
54
55
56 PROCEDURE COPY_PJI_SUMMRZD_FLAG (
57 p_source_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
58 , p_dest_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type() );
59
60
61 PROCEDURE MARK_PLAN_ORIGINAL (
62 p_original_version_id IN NUMBER,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_code OUT NOCOPY VARCHAR2 );
65
66 PROCEDURE PLAN_EXTR_LINES_LOCK (
67 x_return_status OUT NOCOPY VARCHAR2
68 , x_msg_code OUT NOCOPY VARCHAR2 );
69
70
71 PROCEDURE COPY_INTO_BASELINE_ORIGINAL(
72 p_project_id IN NUMBER
73 , p_plan_type_id IN NUMBER
74 , p_plan_version_id IN NUMBER
75 , x_processing_code OUT NOCOPY VARCHAR2
76 );
77
78 PROCEDURE proces_event_pvt (
79 p_event_id IN NUMBER,
80 p_event_type IN VARCHAR2,
81 x_processing_code OUT NOCOPY VARCHAR2,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_data OUT NOCOPY VARCHAR2 );
84
85 PROCEDURE INSERT_APPLY_PROG_VD;
86
87 function submit_request(p_project_id IN NUMBER)
88 return NUMBER
89 IS
90 pragma autonomous_transaction;
91 l_request_id NUMBER;
92 l_project pa_projects_all.segment1%TYPE; /* 4604355 */
93 begin
94 begin
95 select segment1
96 into l_project
97 from pa_projects_all
98 where project_id = p_project_id;
99 exception
100 when no_data_found then null;
101 end;
102 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
103 application => PJI_UTILS.GET_PJI_SCHEMA_NAME ,-- Application Name
104 program => 'PJI_PJP_SUMMARIZE_INCR', -- Program Name
105 sub_request => FALSE, -- Sub Request
106 argument1 => 'I', -- p_run_mode varchar2
107 argument2 => '',
108 argument3 => '',
109 argument4 => '', -- p_run_mode varchar2
110 argument5 => l_project , -- to_char(p_project_id) , -- p_from_project_id
111 argument6 => l_project ); -- to_char(p_project_id) ); -- p_to_project_id
112
113 commit;
114 return(l_request_id);
115 END submit_request;
116 ---------------------------------------------------------------------------
117 ------ Implementation of apis declared in package spec.
118 ---------------------------------------------------------------------------
119
120 -- added procedure for bug#3993830
121 PROCEDURE debug_plan_lines
122 IS
123 BEGIN
124
125 IF NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N') = 'Y' THEN
126
127 INSERT INTO pji_fm_extr_plan_lines_debug
128 (
129 PROJECT_ID ,
130 PROJECT_ORG_ID ,
131 PROJECT_ELEMENT_ID ,
132 STRUCT_VER_ID ,
133 PERIOD_NAME ,
134 CALENDAR_TYPE ,
135 START_DATE ,
136 END_DATE ,
137 RBS_ELEMENT_ID ,
138 RBS_VERSION_ID ,
139 PLAN_VERSION_ID ,
140 PLAN_TYPE_ID ,
141 WP_VERSION_FLAG ,
142 ROLLUP_TYPE ,
143 TXN_CURRENCY_CODE ,
144 TXN_RAW_COST ,
145 TXN_BURDENED_COST ,
146 TXN_REVENUE ,
147 PRJ_CURRENCY_CODE ,
148 PRJ_RAW_COST ,
149 PRJ_BURDENED_COST ,
150 PRJ_REVENUE ,
151 PFC_CURRENCY_CODE ,
152 PFC_RAW_COST ,
153 PFC_BURDENED_COST ,
154 PFC_REVENUE ,
155 QUANTITY ,
156 RESOURCE_CLASS_CODE ,
157 RATE_BASED_FLAG ,
158 ACT_QUANTITY ,
159 ACT_TXN_BURDENED_COST ,
160 ACT_PRJ_BURDENED_COST ,
161 ACT_PFC_BURDENED_COST ,
162 ACT_TXN_RAW_COST ,
163 ACT_PRJ_RAW_COST ,
164 ACT_PFC_RAW_COST ,
165 ACT_TXN_REVENUE ,
166 ACT_PRJ_REVENUE ,
167 ACT_PFC_REVENUE ,
168 ETC_QUANTITY ,
169 ETC_TXN_BURDENED_COST ,
170 ETC_PRJ_BURDENED_COST ,
171 ETC_PFC_BURDENED_COST ,
172 ETC_TXN_RAW_COST ,
173 ETC_PRJ_RAW_COST ,
174 ETC_PFC_RAW_COST ,
175 CREATION_DATE
176 )
177 SELECT
178 PROJECT_ID ,
179 PROJECT_ORG_ID ,
180 PROJECT_ELEMENT_ID ,
181 STRUCT_VER_ID ,
182 PERIOD_NAME ,
183 CALENDAR_TYPE ,
184 START_DATE ,
185 END_DATE ,
186 RBS_ELEMENT_ID ,
187 RBS_VERSION_ID ,
188 PLAN_VERSION_ID ,
189 PLAN_TYPE_ID ,
190 WP_VERSION_FLAG ,
191 ROLLUP_TYPE ,
192 TXN_CURRENCY_CODE ,
193 TXN_RAW_COST ,
194 TXN_BURDENED_COST ,
195 TXN_REVENUE ,
196 PRJ_CURRENCY_CODE ,
197 PRJ_RAW_COST ,
198 PRJ_BURDENED_COST ,
199 PRJ_REVENUE ,
200 PFC_CURRENCY_CODE ,
201 PFC_RAW_COST ,
202 PFC_BURDENED_COST ,
203 PFC_REVENUE ,
204 QUANTITY ,
205 RESOURCE_CLASS_CODE ,
206 RATE_BASED_FLAG ,
207 ACT_QUANTITY ,
208 ACT_TXN_BURDENED_COST ,
209 ACT_PRJ_BURDENED_COST ,
210 ACT_PFC_BURDENED_COST ,
211 ACT_TXN_RAW_COST ,
212 ACT_PRJ_RAW_COST ,
213 ACT_PFC_RAW_COST ,
214 ACT_TXN_REVENUE ,
215 ACT_PRJ_REVENUE ,
216 ACT_PFC_REVENUE ,
217 ETC_QUANTITY ,
218 ETC_TXN_BURDENED_COST ,
219 ETC_PRJ_BURDENED_COST ,
220 ETC_PFC_BURDENED_COST ,
221 ETC_TXN_RAW_COST ,
222 ETC_PRJ_RAW_COST ,
223 ETC_PFC_RAW_COST ,
224 SYSDATE
225 FROM
226 pji_fm_extr_plan_lines ;
227
228 END IF;
229
230 END;
231
232
233 PROCEDURE PLAN_DELETE (
234 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_code OUT NOCOPY VARCHAR2 ) IS
237
238 l_fp_version_ids SYSTEM.pa_num_tbl_type := p_fp_version_ids;
239 l_fp_version_ids1 SYSTEM.pa_num_tbl_type := p_fp_version_ids;
240 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
241 l_event_id NUMBER := NULL;
242 l_return_status VARCHAR2(1);
243 l_msg_code VARCHAR2(100);
244 l_processing_code VARCHAR2(12000);
245 l_event_rec pa_pji_proj_events_log%ROWTYPE;
246
247 BEGIN
248
249 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
250 ( p_package_name => g_package_name
251 , x_return_status => x_return_status ) ;
252
253 print_time ( ' PLAN_DELETE api ' );
254
255 PRINT_PLAN_VERSION_ID_LIST(p_fp_version_ids);
256 CHECK_BUDGET_VERSION_EXISTS(p_fp_version_ids);
257
258 print_time ( ' plan delete 001 ' );
259
260 FOR i IN l_fp_version_ids.FIRST..l_fp_version_ids.LAST LOOP -- Now, process each version as follows..
261
262 print_time ( ' plan delete 002 ' );
263
264 --
265 -- Create event.
266 --
267 l_event_rec.event_type := 'PLAN_DELETE';
268 l_event_rec.event_object := l_fp_version_ids(i);
269 l_event_rec.operation_type := 'D';
270 l_event_rec.status := 'X';
271
272 CREATE_EVENT(l_event_rec);
273
274 print_time ( ' plan delete 003 ' );
275
276
277 --
278 -- Plan delete pvt api.
279 --
280 l_fp_version_ids1 := SYSTEM.pa_num_tbl_type (l_fp_version_ids(i));
281
282 PLAN_DELETE_PVT (
283 p_event_id => l_event_rec.event_id
284 , x_return_status => x_return_status
285 , x_processing_code => x_msg_code);
286
287 print_time ( ' plan delete 004 ' );
288
289 END LOOP;
290
291 print_time ( ' plan delete 005 ' );
292
293 -- COMMIT;
294
295 EXCEPTION
296
297 WHEN OTHERS THEN
298 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
299 ( p_package_name => g_package_name
300 , p_procedure_name => 'PLAN_DELETE'
301 , x_return_status => x_return_status ) ;
302
303 RAISE;
304
305 END;
306
307
308 PROCEDURE PLAN_DELETE_PVT (
309 p_event_id IN NUMBER
310 , x_return_status OUT NOCOPY VARCHAR2
311 , x_processing_code OUT NOCOPY VARCHAR2 ) IS
312
313 l_plan_version_id NUMBER := NULL;
314 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
315 l_return_status VARCHAR2(1);
316 l_msg_code VARCHAR2(100);
317 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
318
319 BEGIN
320
321 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
322 ( p_package_name => g_package_name
323 , x_return_status => x_return_status );
324
325 BEGIN
326
327 SELECT event_object
328 INTO l_plan_version_id
329 FROM PA_PJI_PROJ_EVENTS_LOG
330 WHERE event_id = p_event_id;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 NULL;
335 END;
336
337 print_time ( ' plan delete pvt 002 ' );
338
339 l_fp_version_ids := SYSTEM.pa_num_tbl_type (l_plan_version_id);
340
341 print_time ( ' plan delete pvt 003 ' );
342
343 WBS_HEADERS_LOCK (
344 p_fp_version_ids => l_fp_version_ids
345 , p_context => 'DELETE'
346 , x_return_status => x_return_status
347 , x_msg_code => x_processing_code );
348
349 IF (l_fp_version_ids.COUNT <= 0) THEN
350 print_time ( ' plan delete pvt 004 ' );
351 x_processing_code := 'F';
352 RETURN;
353 END IF;
354
355 print_time ( ' plan delete pvt 005 ' );
356
357 PJI_FM_PLAN_MAINT.DELETE_ALL_PVT ( p_fp_version_ids => l_fp_version_ids);
358
359 print_time ( ' plan delete pvt 006 ' );
360
361 l_event_ids := SYSTEM.pa_num_tbl_type (p_event_id);
362
363 print_time ( ' plan delete pvt 007 ' );
364
365 DELETE_EVENTS (
366 p_event_ids => l_event_ids
367 , x_return_status => x_return_status
368 , x_msg_code => x_processing_code );
369
370 print_time ( ' plan delete pvt 008 ' );
371
372 EXCEPTION
373
374 WHEN OTHERS THEN
375
376 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
377 ( p_package_name => g_package_name
378 , p_procedure_name => 'PLAN_DELETE_PVT'
379 , x_return_status => x_return_status ) ;
380
381 RAISE;
382
383 END;
384
385
386 PROCEDURE PLAN_CREATE (
387 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
388 x_return_status OUT NOCOPY VARCHAR2,
389 x_msg_code OUT NOCOPY VARCHAR2,
390 p_fp_src_version_ids IN SYSTEM.pa_num_tbl_type :=SYSTEM.pa_num_tbl_type(),
391 p_copy_mode in varchar2 :=NULL) IS
392 BEGIN
393
394 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
395 ( p_package_name => g_package_name
396 , x_return_status => x_return_status );
397
398 print_time ( ' PLAN_CREATE api ' );
399
400 PRINT_PLAN_VERSION_ID_LIST(p_fp_version_ids);
401 CHECK_BUDGET_VERSION_EXISTS(p_fp_version_ids);
402
403
404 IF (p_fp_version_ids.COUNT = 0) THEN
405 RETURN;
406 END IF;
407
408 CLEANUP_TEMP_TABLES;
409
410 /*
411 INSERT INTO pji_event_log_debug
412 ( event_type
413 , event_id
414 , event_object
415 , operation_type
416 , status
417 , last_update_date
418 , last_updated_by
419 , creation_date
420 , created_by
421 , last_update_login)
422 VALUES
423 ( 'Create'
424 , pa_pji_proj_events_log_s.NEXTVAL
425 , 'x'
426 , 'x'
427 , 'x'
428 , SYSDATE
429 , 1
430 , SYSDATE
431 , 1
432 , 1);
433 */
434
435 Pji_Fm_Plan_Maint.CREATE_PRIMARY_PVT(
436 p_fp_version_ids => p_fp_version_ids
437 , p_is_primary_rbs => 'T'
438 , p_commit => 'F'
439 , p_fp_src_version_ids => p_fp_src_version_ids
440 , p_Copy_mode=>p_copy_mode);
441
442 -- COMMIT;
443
444 EXCEPTION
445
446 WHEN OTHERS THEN
447
448 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
449 ( p_package_name => g_package_name
450 , p_procedure_name => 'PLAN_CREATE'
451 , x_return_status => x_return_status ) ;
452
453 RAISE;
454
455 END;
456
457
458 PROCEDURE PLAN_UPDATE (
459 p_plan_version_id IN NUMBER := NULL,
460 x_msg_code OUT NOCOPY VARCHAR2,
461 x_return_status OUT NOCOPY VARCHAR2 ) IS
462 l_processing_code VARCHAR2(12000);
463 BEGIN
464
465 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
466 ( p_package_name => g_package_name
467 , x_return_status => x_return_status );
468
469 x_msg_code := Fnd_Api.G_RET_STS_SUCCESS;
470
471 IF NVL(Pa_Task_Pub1.G_CALL_PJI_ROLLUP, 'Y') = 'N' THEN
472 RETURN;
473 END IF;
474
475 print_time ( 'PLAN_UPDATE begin');
476
477 INSERT_APPLY_PROG_VD;
478
479 /*
480 INSERT INTO pji_event_log_debug
481 ( event_type
482 , event_id
483 , event_object
484 , operation_type
485 , status
486 , last_update_date
487 , last_updated_by
488 , creation_date
489 , created_by
490 , last_update_login)
491 VALUES
492 ( 'Update'
493 , pa_pji_proj_events_log_s.NEXTVAL
494 , 'x'
495 , 'x'
496 , 'x'
497 , SYSDATE
498 , 1
499 , SYSDATE
500 , 1
501 , 1);
502 */
503
504 PLAN_UPDATE_PVT(
505 p_plan_version_id => p_plan_version_id,
506 x_return_status => x_return_status,
507 x_processing_code => x_msg_code);
508
509 print_time ( 'PLAN_UPDATE end');
510
511 EXCEPTION
512
513 WHEN OTHERS THEN
514
515 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
516 ( p_package_name => g_package_name
517 , p_procedure_name => 'PLAN_UPDATE'
518 , x_return_status => x_return_status ) ;
519
520 RAISE;
521
522 END;
523
524
525 PROCEDURE PLAN_UPDATE_PVT
526 ( p_plan_version_id IN NUMBER := NULL,
527 x_return_status OUT NOCOPY VARCHAR2,
528 x_processing_code OUT NOCOPY VARCHAR2 ) IS
529
530 CURSOR GET_MAINT_SMART_SLICE_PARAMS
531 IS SELECT DISTINCT rbs_version_id,plan_version_id,struct_ver_id
532 FROM pji_fm_extr_plan_lines
533 WHERE plan_version_id = NVL(p_plan_version_id,plan_version_id)
534 AND ROWID IN ( SELECT extr_lines_rowid FROM pji_fp_rmap_fpr_update_t)
535 ORDER BY rbs_version_id,struct_ver_id;
536
537 l_num_rows_extr_lines NUMBER := NULL;
538 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
539 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
540 l_return_status VARCHAR2(1);
541 l_msg_code VARCHAR2(100);
542 l_num_need_to_lock NUMBER;
543 l_temp NUMBER;
544
545 l_update_id NUMBER;
546
547 p_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
548 p_rbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
549 p_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
550
551 l_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
552
553 l_new_rbs_version_id number;
554 l_new_wbs_version_id number;
555 l_new_plan_version_id number;
556 l_prev_rbs_version_id number;
557 l_prev_wbs_version_id number;
558 l_prev_plan_version_id number;
559 l_msg_data varchar2(2000);
560 l_msg_count number;
561
562 BEGIN
563
564 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
565 ( p_package_name => g_package_name
566 , x_return_status => x_return_status );
567
568 x_processing_code := Fnd_Api.G_RET_STS_SUCCESS;
569
570 print_time ( 'PLAN_UPDATE_PVT begin ' );
571
572 CLEANUP_TEMP_TABLES;
573
574 IF (p_plan_version_id IS NULL) THEN
575 INSERT INTO pji_fp_rmap_fpr_update_t (EXTR_LINES_ROWID)
576 SELECT ROWID FROM pji_fm_extr_plan_lines;
577 ELSE
578 INSERT INTO pji_fp_rmap_fpr_update_t (EXTR_LINES_ROWID)
579 SELECT ROWID FROM pji_fm_extr_plan_lines
580 WHERE plan_version_id = p_plan_version_id;
581 END IF;
582
583 -------------------------------------------------------------------------
584 -- Lock all records.
585 -------------------------------------------------------------------------
586
587 print_time ( 'PLAN_UPDATE_PVT 2 ');
588
589 PLAN_EXTR_LINES_LOCK (
590 x_return_status => x_return_status
591 , x_msg_code => x_processing_code);
592
593 IF (x_return_status IN (FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR)) THEN
594 RETURN;
595 END IF;
596
597
598 -------------------------------------------------------------------------
599 -- Get list of plan versions present in plan extr lines.
600 -------------------------------------------------------------------------
601
602 print_time ( 'PLAN_UPDATE_PVT 3 ');
603
604 GET_HDRS_TOLOCK_FOR_UPDATE (
605 x_fp_version_ids => l_fp_version_ids
606 , x_return_status => x_return_status
607 , x_msg_code => x_processing_code );
608
609 /*
610 IF (l_fp_version_ids.COUNT = 0) THEN
611 RETURN;
612 END IF;
613 */
614
615 -------------------------------------------------------------------------
616 -- Try to lock these records in wbs header table.
617 -- If all records cannot be locked, then return.
618 -------------------------------------------------------------------------
619
620 print_time ( 'PLAN_UPDATE_PVT 4 ');
621
622 l_num_need_to_lock := l_fp_version_ids.COUNT;
623
624 WBS_HEADERS_LOCK (
625 p_fp_version_ids => l_fp_version_ids
626 , p_context => 'UPDATE'
627 , x_return_status => l_return_status
628 , x_msg_code => l_msg_code );
629
630 /*
631 IF (l_fp_version_ids.COUNT <> l_num_need_to_lock) THEN
632 RETURN;
633 END IF;
634 */
635
636 IF (p_plan_version_id IS NOT NULL) THEN
637 l_fp_version_ids := SYSTEM.pa_num_tbl_type(p_plan_version_id);
638 print_time ( 'PLAN_UPDATE_PVT 4.999 ');
639 END IF;
640
641 -------------------------------------------------------------------------
642 -- Call update api and delete the processed records.
643 -------------------------------------------------------------------------
644 print_time ( 'PLAN_UPDATE_PVT 5 ');
645
646 Pji_Fm_Plan_Maint.UPDATE_PRIMARY_PVT (
647 p_plan_version_ids => l_fp_version_ids
648 , p_commit => 'F' );
649
650 print_time ( 'PLAN_UPDATE_PfVT 5.11 ');
651
652 /* commenting as the incremental smart slice will be created */
653 /*start
654 DELETE_SMART_SLICE (
655 p_online_flag => 'Y'
656 , x_return_status => x_return_status ) ;
657 end */
658 /* We get the list of rbs_version,wbs_version and plan_version for which smart slice needs to be created.
659 The PJI_FM_EXTR_PLAN_LINES needs to be deleted before calling maintain_smart_slice as there is a commit
660 in maintain_smart_slice which nulls out pji_fp_rmap_fpr_update_t */
661 /* commenting as the incremental smart slice will be created */
662 /*start
663 OPEN GET_MAINT_SMART_SLICE_PARAMS;
664
665 FETCH GET_MAINT_SMART_SLICE_PARAMS BULK COLLECT INTO p_rbs_version_id_tbl,p_plan_version_id_tbl,p_wbs_version_id_tbl;
666
667 CLOSE GET_MAINT_SMART_SLICE_PARAMS;
668 end */
669 debug_plan_lines ; /* bug#3993830 */
670
671
672 DELETE FROM PJI_FM_EXTR_PLAN_LINES
673 WHERE 1 = 1
674 AND ROWID IN ( SELECT extr_lines_rowid FROM pji_fp_rmap_fpr_update_t)
675 AND TXN_CURRENCY_CODE IS NOT NULL
676 AND prj_currency_code IS NOT NULL
677 AND pfc_currency_code IS NOT NULL;
678
679 /* Smart slice was existing for this project. Recreate the same */
680 /* commenting as the incremental smart slice will be created */
681 /*start
682
683 IF (g_smart_rows_deleted >0) THEN
684
685 FOR i IN 1..p_rbs_version_id_tbl.COUNT LOOP
686
687 l_new_rbs_version_id := p_rbs_version_id_tbl(i);
688 l_new_wbs_version_id := p_wbs_version_id_tbl(i);
689 l_new_plan_version_id := p_plan_version_id_tbl(i);
690
691
692 IF (((l_new_rbs_version_id <>l_prev_rbs_version_id) OR (l_new_wbs_version_id <>l_prev_wbs_version_id)) AND i >1) THEN
693
694 maintain_smart_slice (
695 p_rbs_version_id => l_prev_rbs_version_id,
696 p_plan_version_id_tbl => l_plan_version_id_tbl,
697 p_wbs_element_id => null,
698 p_rbs_element_id => null,
699 p_prg_rollup_flag => 'N',
700 p_curr_record_type_id => null,
701 p_calendar_type =>null,
702 p_wbs_version_id =>l_prev_wbs_version_id,
703 p_commit => 'N',
704 x_msg_count =>l_msg_count ,
705 x_msg_data =>l_msg_data,
706 x_return_status =>l_return_status );
707
708 l_plan_version_id_tbl.DELETE;
709 END IF;
710
711 l_prev_rbs_version_id := l_new_rbs_version_id;
712 l_prev_wbs_version_id := l_new_wbs_version_id;
713 l_plan_version_id_tbl.EXTEND;
714 l_plan_version_id_tbl(l_plan_version_id_tbl.COUNT) := l_new_plan_version_id;
715
716 END LOOP;
717 -- The following call takes care of the last set of rbs version and wbs_version
718 maintain_smart_slice (
719 p_rbs_version_id => l_prev_rbs_version_id,
720 p_plan_version_id_tbl => l_plan_version_id_tbl,
721 p_wbs_element_id => null,
722 p_rbs_element_id => null,
723 p_prg_rollup_flag => 'N',
724 p_curr_record_type_id => null,
725 p_calendar_type =>null,
726 p_wbs_version_id =>l_prev_wbs_version_id,
727 p_commit => 'N',
728 x_msg_count =>l_msg_count ,
729 x_msg_data =>l_msg_data,
730 x_return_status =>l_return_status );
731
732 l_plan_version_id_tbl.DELETE;
733
734 --PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
735 --Pji_Fm_Plan_Maint_T_Pvt.MERGE_INTO_FP_FACT;
736
737 CLEANUP_TEMP_TABLES;
738 --maintain_smart_slice does not clean data populated by itself
739
740 END IF;
741 ends*/
742 /*g_smart_rows_deleted > 0)*/
743
744 print_time ( 'PLAN_UPDATE_PVT end ' );
745
746 EXCEPTION
747 WHEN OTHERS THEN
748
749 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
750 ( p_package_name => g_package_name
751 , p_procedure_name => 'PLAN_UPDATE_PVT'
752 , x_return_status => x_return_status ) ;
753
754 RAISE;
755 END;
756
757
758 PROCEDURE DELETE_SMART_SLICE (
759 p_online_flag IN VARCHAR2 := 'Y'
760 , x_return_status OUT NOCOPY VARCHAR2 ) IS
761 BEGIN
762
763 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
764 ( p_package_name => g_package_name
765 , x_return_status => x_return_status );
766
767 IF (p_online_flag NOT IN ('Y', 'N')) THEN
768 print_time (' Online flag value ' || p_online_flag || ' is not correct. Returning. ');
769 RETURN;
770 END IF;
771
772 IF (p_online_flag = 'Y') THEN
773
774 /* DELETE FROM pji_rollup_level_status
775 WHERE plan_version_id IN (SELECT plan_version_id FROM pji_fm_extr_plnver3_t);
776 Bug No .4538102*/
777
778 DELETE FROM pji_rollup_level_status
779 WHERE (project_id,plan_version_id,plan_type_code) IN
780 (SELECT project_id,plan_version_id,plan_type_code FROM pji_fm_extr_plnver3_t); /*4771527 */
781
782 IF (SQL%ROWCOUNT > 0) THEN
783
784 pji_pjp_fp_curr_wrap.set_table_stats('PJI','PJI_FM_EXTR_PLNVER3_T', 1 , 1 , 100);
785
786 /*
787 FND_STATS.SET_TABLE_STATS(
788 ownname => 'PJI', -- IN VARCHAR2,
789 tabname => 'PJI_FM_EXTR_PLNVER3_T', -- IN VARCHAR2,
790 numrows => 1, -- IN NUMBER,
791 numblks => 1, -- IN NUMBER,
792 avgrlen => 100 --, -- IN NUMBER,
793 );
794 */
795
796 DELETE FROM pji_fp_xbs_accum_f
797 WHERE (project_id,plan_version_id,plan_type_code) IN /*4771527 */
798 (SELECT project_id, plan_version_id,plan_type_code FROM pji_fm_extr_plnver3_t)
799 AND (rbs_aggr_level = 'R'
800 OR (rbs_aggr_level = 'L'
801 AND wbs_rollup_flag = 'Y')) ;
802 g_smart_rows_deleted := SQL%ROWCOUNT;
803 END IF;
804
805 ELSIF (p_online_flag = 'N') THEN
806
807 /* DELETE FROM pji_rollup_level_status
808 WHERE plan_version_id IN (SELECT plan_version_id FROM pji_fm_extr_plnver4);
809 Bug No. 4538102*/
810 DELETE FROM pji_rollup_level_status
811 WHERE (project_id,plan_version_id,plan_type_code ) IN
812 (SELECT project_id,plan_version_id,plan_type_code FROM pji_fm_extr_plnver4); /*4771527 */
813
814 IF (SQL%ROWCOUNT > 0) THEN
815
816 /*
817 FND_STATS.SET_TABLE_STATS(
818 ownname => 'PJI', -- IN VARCHAR2,
819 tabname => 'PJI_FM_EXTR_PLNVER4', -- IN VARCHAR2,
820 numrows => 1, -- IN NUMBER,
821 numblks => 1, -- IN NUMBER,
822 avgrlen => 100 --, -- IN NUMBER,
823 );
824 */
825
826 DELETE FROM pji_fp_xbs_accum_f
827 WHERE (project_id,plan_version_id,plan_type_code ) IN
828 (SELECT project_id, plan_version_id,plan_type_code FROM pji_fm_extr_plnver4) /*4771527 */
829 AND (rbs_aggr_level = 'R'
830 OR (rbs_aggr_level = 'L'
831 AND wbs_rollup_flag = 'Y')) ;
832 g_smart_rows_deleted := SQL%ROWCOUNT;
833 END IF;
834
835 END IF;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839
840 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
841 ( p_package_name => g_package_name
842 , p_procedure_name => 'DELETE_SMART_SLICE'
843 , x_return_status => x_return_status ) ;
844
845 RAISE;
846 END;
847
848
849 PROCEDURE PLAN_UPDATE_ACT_ETC (
850 p_plan_wbs_ver_id IN NUMBER
851 , p_prev_pub_wbs_ver_id IN NUMBER := NULL
852 -- p_plan_version_id IN NUMBER
853 -- , p_prev_pub_version_id IN NUMBER := NULL
854 , x_msg_code OUT NOCOPY VARCHAR2
855 , x_return_status OUT NOCOPY VARCHAR2 ) IS
856
857 l_plan_version_id NUMBER := NULL;
858 l_prev_pub_version_id NUMBER := NULL;
859 l_processing_code VARCHAR2(12000);
860 l_temp NUMBER;
861
862 BEGIN
863
864 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
865 ( p_package_name => g_package_name
866 , x_return_status => x_return_status );
867
868 x_msg_code := Fnd_Api.G_RET_STS_SUCCESS;
869
870 print_time ( 'PLAN_UPDATE_ACT_ETC begin ');
871
872 print_time ( 'PLAN_UPDATE_ACT_ETC p_plan_wbs_ver_id ' || p_plan_wbs_ver_id || ' p_prev_pub_wbs_ver_id ' || p_prev_pub_wbs_ver_id );
873
874
875 IF (p_plan_wbs_ver_id IS NOT NULL) THEN
876 BEGIN
877 SELECT plan_version_id
878 INTO l_plan_version_id
879 FROM pji_pjp_wbs_header
880 WHERE wbs_version_id = p_plan_wbs_ver_id
881 AND wp_flag = 'Y'; -- AND plan_version_id > 0;
882 EXCEPTION
883 WHEN OTHERS THEN
884 print_time ( 'PLAN_UPDATE_ACT_ETC.. new plan structure version error p_plan_wbs_ver_id = ' || p_plan_wbs_ver_id || ' ' || SQLERRM );
885 RAISE;
886 END;
887 ELSE
888 print_time ( 'PLAN_UPDATE_ACT_ETC new structure version id is null, returning.');
889 RETURN;
890 END IF;
891 /* commenting out as this not required any more for bug#4719016
892
893 IF (p_prev_pub_wbs_ver_id IS NOT NULL) THEN
894 BEGIN
895 SELECT plan_version_id
896 INTO l_prev_pub_version_id
897 FROM pji_pjp_wbs_header
898 WHERE wbs_version_id = p_prev_pub_wbs_ver_id
899 AND wp_flag = 'Y'; -- AND plan_version_id > 0;
900 EXCEPTION
901 WHEN OTHERS THEN
902 print_time ( 'PLAN_UPDATE_ACT_ETC.. new plan structure version error p_prev_pub_wbs_ver_id = ' || p_prev_pub_wbs_ver_id || ' ' || SQLERRM );
903 RAISE;
904 END;
905 ELSE
906 l_prev_pub_version_id := -1;
907 print_time ( 'PLAN_UPDATE_ACT_ETC prev published structure version id is null');
908 END IF;
909 */
910
911 SELECT COUNT(1)
912 INTO l_temp
913 FROM pa_budget_versions
914 WHERE 1=1
915 AND budget_version_id IN (l_plan_version_id , l_prev_pub_version_id );
916
917
918 IF (l_temp = 0) THEN
919 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC invalid plan version ids.. l_temp = ' || l_temp || '. Returning..' );
920 RETURN;
921 END IF;
922
923 /*
924 INSERT INTO pji_event_log_debug
925 ( event_type
926 , event_id
927 , event_object
928 , operation_type
929 , status
930 , last_update_date
931 , last_updated_by
932 , creation_date
933 , created_by
934 , last_update_login)
935 VALUES
936 ( 'Update ACT ETC'
937 , pa_pji_proj_events_log_s.NEXTVAL
938 , 'x'
939 , 'x'
940 , 'x'
941 , SYSDATE
942 , 1
943 , SYSDATE
944 , 1
945 , 1);
946 */
947
948 PLAN_UPDATE_PVT_ACT_ETC(
949 p_plan_version_id => l_plan_version_id,
950 p_prev_pub_version_id => l_prev_pub_version_id,
951 x_return_status => x_return_status,
952 x_processing_code => x_msg_code);
953
954 print_time ( 'PLAN_UPDATE_ACT_ETC end');
955
956 EXCEPTION
957
958 WHEN OTHERS THEN
959
960 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
961 ( p_package_name => g_package_name
962 , p_procedure_name => 'PLAN_UPDATE_ACT_ETC'
963 , x_return_status => x_return_status ) ;
964
965 RAISE;
966
967 END;
968
969
970 PROCEDURE PLAN_UPDATE_PVT_ACT_ETC (
971 p_plan_version_id IN NUMBER
972 , p_prev_pub_version_id IN NUMBER := NULL
973 , x_return_status OUT NOCOPY VARCHAR2
974 , x_processing_code OUT NOCOPY VARCHAR2) IS
975
976 l_num_rows_extr_lines NUMBER := NULL;
977 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
978 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
979 l_return_status VARCHAR2(1);
980 l_msg_code VARCHAR2(100);
981 l_num_need_to_lock NUMBER;
982 l_temp NUMBER;
983
984 l_update_id NUMBER;
985
986 BEGIN
987
988 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
989 ( p_package_name => g_package_name
990 , x_return_status => x_return_status );
991
992 x_processing_code := Fnd_Api.G_RET_STS_SUCCESS;
993
994 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC begin ' );
995
996 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC p_plan_version_id ' || p_plan_version_id || ' p_prev_pub_version_id ' || p_prev_pub_version_id );
997
998 CLEANUP_TEMP_TABLES;
999
1000 IF (p_plan_version_id IS NULL) THEN
1001 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 1 plan version id is null.. returning ' );
1002 RETURN;
1003 ELSE
1004 INSERT INTO pji_fp_rmap_fpr_update_t (EXTR_LINES_ROWID)
1005 SELECT ROWID FROM pji_fm_extr_plan_lines
1006 WHERE plan_version_id = p_plan_version_id;
1007 END IF;
1008
1009 SELECT COUNT(1)
1010 INTO l_temp
1011 FROM pji_fm_extr_plan_lines
1012 WHERE ROWNUM=1; /*Added for bug 3928020*/
1013 -------------------------------------------------------------------------
1014 -- Process records only if # of lines in plan lines table > threshold #.
1015 -- They will be processed in the next call to plan update api if the above
1016 -- condition is met.
1017 -------------------------------------------------------------------------
1018 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 1 number of rows in plan lines is .. ' || l_temp);
1019
1020 SELECT COUNT(1)
1021 INTO l_num_rows_extr_lines
1022 FROM pji_fp_rmap_fpr_update_t
1023 WHERE ROWNUM=1; /*Added for bug 3928020*/
1024
1025 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 1.5 number of rows in rowid table is.. ' || l_num_rows_extr_lines );
1026
1027 IF (l_temp > 0) THEN
1028
1029 -------------------------------------------------------------------------
1030 -- Lock all records.
1031 -------------------------------------------------------------------------
1032
1033 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 2 ');
1034
1035 PLAN_EXTR_LINES_LOCK (
1036 x_return_status => l_return_status
1037 , x_msg_code => l_msg_code);
1038
1039 IF (l_msg_code = 'F') THEN
1040 RETURN;
1041 END IF;
1042
1043
1044 -------------------------------------------------------------------------
1045 -- Get list of plan versions present in plan extr lines.
1046 -------------------------------------------------------------------------
1047
1048 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 3 ');
1049
1050 GET_HDRS_TOLOCK_FOR_UPDATE (
1051 x_fp_version_ids => l_fp_version_ids
1052 , x_return_status => x_return_status
1053 , x_msg_code => x_processing_code );
1054
1055 IF (l_fp_version_ids.COUNT = 0) THEN
1056 RETURN;
1057 END IF;
1058
1059
1060 -------------------------------------------------------------------------
1061 -- Try to lock these records in wbs header table.
1062 -- If all records cannot be locked, then return.
1063 -------------------------------------------------------------------------
1064
1065 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 4 ');
1066
1067 l_num_need_to_lock := l_fp_version_ids.COUNT;
1068
1069 WBS_HEADERS_LOCK (
1070 p_fp_version_ids => l_fp_version_ids
1071 , p_context => 'UPDATE' -- 'PUBLISH'
1072 , x_return_status => l_return_status
1073 , x_msg_code => l_msg_code );
1074
1075 IF (l_fp_version_ids.COUNT <> l_num_need_to_lock) THEN
1076 print_time ( ' PLAN_UPDATE_PVT_ACT_ETC .. Could not lock all headers.. Exitting..');
1077 RETURN;
1078 END IF;
1079
1080 ELSE
1081 print_time ( ' PLAN_UPDATE_PVT_ACT_ETC .. no lines, so no lines/headers to lock.');
1082 END IF;
1083
1084
1085 -------------------------------------------------------------------------
1086 -- Call update api and delete the processed records.
1087 -------------------------------------------------------------------------
1088 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 5 ');
1089
1090 Pji_Fm_Plan_Maint.UPDATE_PRIMARY_PVT_ACT_ETC (
1091 p_plan_version_id => p_plan_version_id
1092 , p_prev_pub_version_id => p_prev_pub_version_id
1093 , x_return_status => x_return_status
1094 , x_processing_code => x_processing_code);
1095
1096 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 5.11 ');
1097
1098 debug_plan_lines ; /* bug#3993830 */
1099
1100 DELETE FROM PJI_FM_EXTR_PLAN_LINES
1101 WHERE 1 = 1
1102 AND ROWID IN ( SELECT extr_lines_rowid FROM pji_fp_rmap_fpr_update_t)
1103 AND TXN_CURRENCY_CODE IS NOT NULL
1104 AND prj_currency_code IS NOT NULL
1105 AND pfc_currency_code IS NOT NULL;
1106
1107 print_time ( 'PLAN_UPDATE_PVT_ACT_ETC end ' );
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111
1112 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1113 ( p_package_name => g_package_name
1114 , p_procedure_name => 'PLAN_UPDATE_PVT_ACT_ETC'
1115 , x_return_status => x_return_status ) ;
1116
1117 RAISE;
1118 END;
1119
1120
1121 PROCEDURE FINPLAN_COPY (
1122 p_source_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
1123 p_dest_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
1124 p_source_fp_version_types IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type(),
1125 p_dest_fp_version_types IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type(),
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_code OUT NOCOPY VARCHAR2 ) IS
1128
1129 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1130 l_s_wking_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); -- s == Source
1131 l_s_bslnd_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); -- d == Desination
1132 l_d_wking_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1133 l_d_bslnd_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1134 l_num_wking_fp_ver_ids NUMBER := NULL;
1135 l_return_status VARCHAR2(1);
1136 l_processing_code VARCHAR2(100);
1137 l_msg_code VARCHAR2(100);
1138 l_s_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1139 l_d_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1140 l_event_rec pa_pji_proj_events_log%ROWTYPE;
1141
1142 l_num_src_fp_ver_ids NUMBER := NULL;
1143 l_num_dest_fp_ver_ids NUMBER := NULL;
1144 l_num_src_fp_ver_types NUMBER := NULL;
1145 l_num_dest_fp_ver_types NUMBER := NULL;
1146
1147 BEGIN
1148
1149 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1150 ( p_package_name => g_package_name
1151 , x_return_status => x_return_status );
1152
1153 print_time ( ' plan copy api ' );
1154 print_time ( ' plan copy api .. source plan version ids ' );
1155 PRINT_PLAN_VERSION_ID_LIST(p_source_fp_version_ids);
1156 print_time ( ' plan copy api .. dest plan version ids ' );
1157 PRINT_PLAN_VERSION_ID_LIST(p_dest_fp_version_ids);
1158 print_time ( ' plan copy api .. source plan version types ' );
1159 PRINT_PLAN_VERSION_TYPE_LIST(p_source_fp_version_types);
1160 print_time ( ' plan copy api .. source plan version types ' );
1161 PRINT_PLAN_VERSION_TYPE_LIST(p_source_fp_version_types);
1162 print_time ( ' plan copy api .. checking source plan version ids are valid.. ' );
1163 CHECK_BUDGET_VERSION_EXISTS(p_source_fp_version_ids);
1164 print_time ( ' plan copy api .. checking dest plan version ids are valid.. ' );
1165
1166
1167 CHECK_BUDGET_VERSION_EXISTS(p_dest_fp_version_ids);
1168
1169 print_time ( ' plan copy api .. 001 ' );
1170
1171 --
1172 -- Note: As per Venkatesh's email, only cases to support are: B-W, W-W (create working).
1173 -- W-B will be done in PLAN_BASELINE api. B-B is not valid biz scenario.
1174 --
1175
1176 l_num_src_fp_ver_ids := p_source_fp_version_ids.COUNT;
1177 l_num_dest_fp_ver_ids := p_dest_fp_version_ids.COUNT;
1178 l_num_src_fp_ver_types := p_source_fp_version_types.COUNT;
1179 l_num_dest_fp_ver_types := p_dest_fp_version_types.COUNT;
1180
1181 IF ( (l_num_src_fp_ver_ids IS NULL )
1182 OR (l_num_src_fp_ver_ids = 0 )
1183 OR (l_num_src_fp_ver_ids <> l_num_dest_fp_ver_ids )
1184 OR (l_num_src_fp_ver_ids <> l_num_src_fp_ver_types)
1185 OR (l_num_src_fp_ver_ids <> l_num_dest_fp_ver_types ) ) THEN
1186 RETURN;
1187 END IF;
1188
1189 print_time ( ' plan copy api .. 002 ' );
1190
1191 FOR i IN p_source_fp_version_ids.FIRST..p_source_fp_version_ids.LAST LOOP
1192
1193 print_time ( ' plan copy api .. 003 ' );
1194
1195 IF (p_source_fp_version_types(i) = 'W') THEN
1196
1197 print_time ( ' plan copy api .. 004 ' );
1198
1199 --
1200 -- Create event.
1201 --
1202 l_event_rec.event_type := 'PLAN_COPY';
1203 l_event_rec.event_object := p_dest_fp_version_ids(i);
1204
1205 l_event_rec.operation_type := '1';
1206 l_event_rec.status := 'X';
1207 l_event_rec.attribute1 := p_source_fp_version_ids(i);
1208 -- l_event_rec.attribute2 := p_source_fp_version_types(i); -- W/B
1209
1210 CREATE_EVENT(l_event_rec);
1211
1212 print_time ( ' plan copy api .. 005 ' );
1213
1214 --
1215 -- Call the pvt api to Copy the plan.
1216 --
1217 PLAN_COPY_PVT (
1218 p_event_id => l_event_rec.event_id
1219 , x_processing_code => l_processing_code
1220 , x_return_status => x_return_status );
1221
1222 print_time ( ' plan copy api .. 006 ' );
1223
1224 ELSIF (p_source_fp_version_types(i) = 'B') THEN
1225
1226 print_time ( ' plan copy api .. 007 ' );
1227
1228 --
1229 -- Copy the plan without creating events.
1230 --
1231
1232 l_s_fp_version_ids := SYSTEM.pa_num_tbl_type (p_source_fp_version_ids(i) );
1233 l_d_fp_version_ids := SYSTEM.pa_num_tbl_type (p_dest_fp_version_ids(i) );
1234
1235 print_time ( ' plan copy api .. 008 ' );
1236
1237 Pji_Fm_Plan_Maint_T_Pvt.COPY_PRIMARY
1238 (
1239 p_source_fp_version_ids => l_s_fp_version_ids
1240 , p_dest_fp_version_ids => l_d_fp_version_ids
1241 , p_commit => 'F'
1242 );
1243
1244 print_time ( ' plan copy api .. 009 ' );
1245
1246 COPY_PJI_SUMMRZD_FLAG (
1247 p_source_fp_version_ids => l_s_fp_version_ids
1248 , p_dest_fp_version_ids => l_d_fp_version_ids);
1249
1250 print_time ( ' plan copy api .. 010 ' );
1251
1252 END IF;
1253
1254 print_time ( ' plan copy api .. 011 ' );
1255
1256 END LOOP;
1257
1258 print_time ( ' plan copy api .. 012 ' );
1259
1260 --COMMIT;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1265 ( p_package_name => g_package_name
1266 , p_procedure_name => 'PLAN_COPY'
1267 , x_return_status => x_return_status ) ;
1268
1269 RAISE;
1270 END;
1271
1272
1273 PROCEDURE PLAN_COPY_PVT (
1274 p_event_id IN NUMBER
1275 , x_return_status OUT NOCOPY VARCHAR2
1276 , x_processing_code OUT NOCOPY VARCHAR2) IS
1277
1278 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1279 l_s_plan_version_id NUMBER := NULL;
1280 l_d_plan_version_id NUMBER := NULL;
1281 l_s_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1282 l_d_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1283 l_working_or_baselined VARCHAR2(30) := 'N';
1284 l_return_status VARCHAR2(1);
1285 l_msg_code VARCHAR2(100);
1286
1287 BEGIN
1288
1289 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1290 ( p_package_name => g_package_name
1291 , x_return_status => x_return_status );
1292
1293 x_processing_code := Fnd_Api.G_RET_STS_SUCCESS;
1294
1295 print_time ( ' plan copy PVT api .. 001 ' );
1296
1297 SELECT attribute1, event_object, attribute2
1298 INTO l_s_plan_version_id, l_d_plan_version_id, l_working_or_baselined
1299 FROM PA_PJI_PROJ_EVENTS_LOG
1300 WHERE event_id = p_event_id;
1301
1302 l_s_fp_version_ids := SYSTEM.pa_num_tbl_type (l_s_plan_version_id);
1303 l_d_fp_version_ids := SYSTEM.pa_num_tbl_type (l_d_plan_version_id);
1304
1305 print_time ( ' plan copy PVT api .. 002 ' );
1306
1307 WBS_HEADERS_LOCK (
1308 p_fp_version_ids => l_s_fp_version_ids
1309 , p_context => 'COPY'
1310 , x_return_status => l_return_status
1311 , x_msg_code => l_msg_code );
1312
1313 print_time ( ' plan copy PVT api .. 003 ' );
1314
1315 IF (l_s_fp_version_ids.COUNT < 1) THEN
1316 x_processing_code := 'F';
1317 print_time ( ' plan copy PVT api .. 004 ' );
1318 RETURN;
1319 END IF;
1320
1321 print_time ( ' plan copy PVT api .. 005 ' );
1322
1323 Pji_Fm_Plan_Maint_T_Pvt.COPY_PRIMARY
1324 (
1325 p_source_fp_version_ids => l_s_fp_version_ids
1326 , p_dest_fp_version_ids => l_d_fp_version_ids
1327 , p_commit => 'F'
1328 );
1329
1330 print_time ( ' plan copy PVT api .. 006 ' );
1331
1332 COPY_PJI_SUMMRZD_FLAG (
1333 p_source_fp_version_ids => l_s_fp_version_ids
1334 , p_dest_fp_version_ids => l_d_fp_version_ids);
1335
1336 print_time ( ' plan copy PVT api .. 007 ' );
1337
1338 --
1339 -- Delete from events table.
1340 --
1341 l_event_ids := SYSTEM.pa_num_tbl_type (p_event_id);
1342
1343 DELETE_EVENTS (
1344 p_event_ids => l_event_ids
1345 , x_return_status => l_return_status
1346 , x_msg_code => l_msg_code );
1347
1348 print_time ( ' plan copy PVT api .. 008 ' );
1349
1350 x_processing_code := l_return_status;
1351
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1355 ( p_package_name => g_package_name
1356 , p_procedure_name => 'PLAN_COPY_PVT'
1357 , x_return_status => x_return_status ) ;
1358
1359 RAISE;
1360 END;
1361
1362
1363 PROCEDURE PLAN_BASELINE (
1364 p_baseline_version_id IN NUMBER,
1365 p_new_version_id IN NUMBER,
1366 x_return_status OUT NOCOPY VARCHAR2,
1367 x_msg_code OUT NOCOPY VARCHAR2 ) IS
1368
1369 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1370 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1371 l_return_status VARCHAR2(1);
1372 l_msg_code VARCHAR2(100);
1373 l_event_id NUMBER;
1374 l_event_rec pa_pji_proj_events_log%ROWTYPE;
1375 l_project_id NUMBER := NULL;
1376 l_curr_bl_ver_id NUMBER := NULL;
1377 l_plan_type_id NUMBER := NULL;
1378 l_new_project_type pa_projects_all.project_type%TYPE;
1379 l_new_plan_type_code pa_budget_versions.budget_type_code%TYPE;
1380 l_plan_type_code CHAR(1);
1381 l_processing_code VARCHAR2(12000);
1382 l_wp_version_flag VARCHAR2(1) := NULL;
1383
1384 l_s_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1385 l_d_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1386
1387 BEGIN
1388
1389 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1390 ( p_package_name => g_package_name
1391 , x_return_status => x_return_status );
1392
1393
1394 print_time ( ' PLAN_BASELINE api begin ' );
1395 print_time ( ' baseline version id is .. ' || p_baseline_version_id );
1396 print_time ( ' new version id is .. ' || p_new_version_id );
1397
1398 -------------------------------------------------------------------------
1399 -- Get plan type id and project id for the newly baselined version.
1400 -------------------------------------------------------------------------
1401 BEGIN
1402 SELECT bv1.project_id, bv1.fin_plan_type_id, bv1.wp_version_flag,
1403 DECODE(bv1.version_type, 'COST' , 'C' , 'REVENUE' , 'R' , 'A')
1404 INTO l_project_id, l_plan_type_id, l_wp_version_flag,l_plan_type_code
1405 FROM pa_budget_versions bv1
1406 WHERE bv1.budget_version_id = p_baseline_version_id;
1407 EXCEPTION
1408 WHEN OTHERS THEN
1409 NULL;
1410 END;
1411
1412 print_time ( ' PLAN_BASELINE api 001 ' ||l_project_id || ' ' || l_plan_type_id || ' ' || l_wp_version_flag );
1413
1414 -------------------------------------------------------------------------
1415 -- Only financial plans are baselined.
1416 -------------------------------------------------------------------------
1417 IF (l_wp_version_flag = 'Y') THEN
1418 RETURN;
1419 END IF;
1420
1421 -------------------------------------------------------------------------
1422 -- Get the current baselined plan version for this project for this line type.
1423 -------------------------------------------------------------------------
1424 BEGIN
1425 SELECT plan_version_id
1426 INTO l_curr_bl_ver_id
1427 FROM PJI_PJP_WBS_HEADER wbs_hdr
1428 WHERE 1 = 1
1429 AND project_id = l_project_id
1430 AND plan_type_id = l_plan_type_id
1431 AND plan_type_code = l_plan_type_code
1432 AND cb_flag = 'Y'
1433 AND plan_version_id > 0 ;
1434 EXCEPTION
1435 WHEN NO_DATA_FOUND THEN
1436 print_time ( ' PLAN_BASELINE api 001.11 : Currently, there are no baslined plans for this plan type.');
1437 WHEN OTHERS THEN
1438 RAISE;
1439 END;
1440
1441 print_time ( ' PLAN_BASELINE api 001.2 ' || l_curr_bl_ver_id );
1442
1443 ------------------------------------------------------------------------------
1444 -- Create a working plan version using the newly baselined plan.
1445 -- Copy pji summarized flag from source to destination plan version ids.
1446 ------------------------------------------------------------------------------
1447
1448 print_time ( ' PLAN_BASELINE api 001.1 ' || p_baseline_version_id || ' ' || p_new_version_id );
1449
1450 l_s_fp_version_ids := SYSTEM.pa_num_tbl_type (p_baseline_version_id );
1451 l_d_fp_version_ids := SYSTEM.pa_num_tbl_type (p_new_version_id);
1452
1453 COPY_PJI_SUMMRZD_FLAG (
1454 p_source_fp_version_ids => l_s_fp_version_ids
1455 , p_dest_fp_version_ids => l_d_fp_version_ids);
1456
1457
1458 print_time ( ' PLAN_BASELINE api 001.22 successfully copied pji summarized flag. ' );
1459
1460
1461 -------------------------------------------------------------------------
1462 -- Create an event..
1463 -------------------------------------------------------------------------
1464 l_event_rec.event_type := 'PLAN_BASELINE';
1465 l_event_rec.event_object := p_new_version_id;
1466 l_event_rec.operation_type := 'U';
1467 l_event_rec.status := 'X';
1468 l_event_rec.attribute1 := l_project_id;
1469 l_event_rec.attribute2 := l_plan_type_id;
1470 l_event_rec.attribute3 := l_curr_bl_ver_id;
1471 l_event_rec.attribute4 := l_plan_type_code;
1472
1473 CREATE_EVENT(l_event_rec);
1474
1475
1476 -------------------------------------------------------------------------
1477 -- Call the private api that actually handles the baseline process.
1478 -------------------------------------------------------------------------
1479
1480 PLAN_BASELINE_PVT (
1481 p_event_id => l_event_rec.event_id
1482 , x_processing_code => l_processing_code
1483 , x_return_status => x_return_status );
1484
1485
1486 print_time ( ' PLAN_BASELINE api 002 End ' );
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1491 ( p_package_name => g_package_name
1492 , p_procedure_name => 'PLAN_BASELINE'
1493 , x_return_status => x_return_status ) ;
1494
1495 RAISE;
1496 END;
1497
1498
1499
1500 ----------------------------------------------------------------------------------------
1501 --
1502 -- Mark plan as baselined.
1503 -- In the reporting lines fact, baslined slice corresponds to plan version id -3.
1504 -- There is a unique baselined plan version for a given combination of project and plan type ids.
1505 --
1506 ----------------------------------------------------------------------------------------
1507 PROCEDURE PLAN_BASELINE_PVT (
1508 p_event_id IN NUMBER
1509 , x_return_status OUT NOCOPY VARCHAR2
1510 , x_processing_code OUT NOCOPY VARCHAR2) IS
1511
1512 l_project_id NUMBER;
1513 l_count NUMBER;
1514 l_working_version_id NUMBER;
1515 l_baseline_version_id NUMBER;
1516 l_old_baseline_version_id NUMBER;
1517 l_new_version_id NUMBER;
1518 l_plan_type_id NUMBER;
1519 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1520 l_source_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1521 l_dest_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1522 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1523 l_return_status VARCHAR2(12000);
1524 l_msg_code VARCHAR2(100);
1525 l_baselined_exists NUMBER;
1526 l_new_working_ver_id NUMBER;
1527 l_processing_code VARCHAR2(1);
1528
1529 l_s_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1530 l_d_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1531
1532 l_last_update_date date := SYSDATE;
1533 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
1534 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
1535 l_plan_type_code char(1);
1536
1537 BEGIN
1538
1539 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1540 ( p_package_name => g_package_name
1541 , x_return_status => x_return_status );
1542
1543 x_processing_code := Fnd_Api.G_RET_STS_SUCCESS;
1544
1545 print_time ( ' PLAN_BASELINE_PVT api 001 Begin event id is.. ' || p_event_id );
1546
1547 -----------------------------------------------------------------------------------
1548 -- Get event info.
1549 -----------------------------------------------------------------------------------
1550
1551 SELECT attribute1
1552 , event_object
1553 , attribute3
1554 , attribute2
1555 , attribute4
1556 INTO l_project_id
1557 , l_new_version_id
1558 , l_old_baseline_version_id
1559 , l_plan_type_id
1560 , l_plan_type_code /*4771527*/
1561 FROM PA_PJI_PROJ_EVENTS_LOG
1562 WHERE event_id = p_event_id;
1563
1564
1565 l_dest_fp_version_ids := SYSTEM.pa_num_tbl_type (l_new_version_id);
1566
1567
1568 ----------------------------------------------------------------------------------------
1569 -- Insert the missing -3, -4 header records and lock the appropriate versions.
1570 ----------------------------------------------------------------------------------------
1571
1572 WBS_HEADERS_LOCK( -- _BASELINE (
1573 p_fp_version_ids => l_dest_fp_version_ids -- l_new_version_id
1574 , p_context => 'BASELINE'
1575 , x_return_status => l_return_status
1576 , x_msg_code => l_msg_code );
1577
1578 x_processing_code := l_msg_code;
1579
1580 IF (l_dest_fp_version_ids.COUNT <1) THEN
1581 print_time(' Baseline flow. # hdrs locked is not valid, returning. # = ' || l_dest_fp_version_ids.COUNT );
1582 RETURN;
1583 END IF;
1584
1585 ----------------------------------------------------------------------------------------
1586 -- Update the WBS header table with the correct baselined version.
1587 ----------------------------------------------------------------------------------------
1588
1589 UPDATE pji_pjp_wbs_header
1590 SET cb_flag = DECODE( plan_version_id
1591 , l_old_baseline_version_id, 'N'
1592 , l_new_version_id, 'Y'
1593 , -3, 'Y' )
1594 , LAST_UPDATE_DATE = l_last_update_date
1595 , LAST_UPDATED_BY = l_last_updated_by
1596 , LAST_UPDATE_LOGIN = l_last_update_login
1597 WHERE plan_version_id IN (l_old_baseline_version_id
1598 , l_new_version_id
1599 , -3)
1600 AND project_id = l_project_id
1601 AND plan_type_id = l_plan_type_id
1602 AND plan_type_code = l_plan_type_code; /*4771527 */
1603
1604 print_time ( ' PLAN_BASELINE_PVT api 004: marked the latest baselined version in header table. ' );
1605
1606
1607 ----------------------------------------------------------------------------------------
1608 -- Set the online context.
1609 ----------------------------------------------------------------------------------------
1610
1611 Pji_Pjp_Sum_Rollup.set_online_context (
1612 p_event_id => p_event_id,
1613 p_project_id => l_project_id,
1614 p_plan_type_id => l_plan_type_id,
1615 p_old_baselined_version => l_old_baseline_version_id,
1616 p_new_baselined_version => l_new_version_id ,
1617 p_old_original_version => NULL,
1618 p_new_original_version => NULL,
1619 p_old_struct_version => NULL,
1620 p_new_struct_version => NULL);
1621
1622
1623 ----------------------------------------------------------------------------------------
1624 -- Assume primary slice exists.
1625 -- Create primary slice for reporting RBSes and secondary slice.
1626 ----------------------------------------------------------------------------------------
1627
1628 Pji_Fm_Plan_Maint.CREATE_SECONDARY_T_PVT(
1629 p_fp_version_ids => l_dest_fp_version_ids
1630 , p_commit => 'N');
1631
1632
1633 print_time ( ' PLAN_BASELINE_PVT api 006: Created secondary slice for all RBSes.' );
1634
1635
1636 ------------------------------------------------------------------------------
1637 -- Delete event.
1638 ------------------------------------------------------------------------------
1639
1640 l_event_ids := SYSTEM.pa_num_tbl_type (p_event_id);
1641
1642 DELETE_EVENTS (
1643 p_event_ids => l_event_ids
1644 , x_return_status => l_return_status
1645 , x_msg_code => l_msg_code );
1646
1647 print_time ( ' PLAN_BASELINE_PVT api 008: Deleted events. ' );
1648
1649 EXCEPTION
1650 WHEN OTHERS THEN
1651 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1652 ( p_package_name => g_package_name
1653 , p_procedure_name => 'PLAN_BASELINE_PVT'
1654 , x_return_status => x_return_status ) ;
1655
1656 RAISE;
1657 END;
1658
1659
1660 PROCEDURE PLAN_ORIGINAL (
1661 p_original_version_id IN NUMBER,
1662 x_return_status OUT NOCOPY VARCHAR2,
1663 x_msg_code OUT NOCOPY VARCHAR2 ) IS
1664
1665 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1666 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1667 l_wp_flag VARCHAR2(1);
1668 l_return_status VARCHAR2(1);
1669 l_msg_code VARCHAR2(100);
1670 l_event_id NUMBER;
1671 l_event_rec pa_pji_proj_events_log%ROWTYPE;
1672 l_project_id NUMBER := NULL;
1673 l_curr_or_ver_id NUMBER := NULL;
1674 l_plan_type_id NUMBER := NULL;
1675 l_plan_type_code char(1);
1676 l_new_project_type pa_projects_all.project_type%TYPE;
1677 l_new_plan_type_code pa_budget_versions.budget_type_code%TYPE;
1678 l_processing_code VARCHAR2(1500) := NULL;
1679
1680 BEGIN
1681
1682 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1683 ( p_package_name => g_package_name
1684 , x_return_status => x_return_status );
1685
1686 print_time ( ' PLAN_ORIGINAL api ' );
1687 print_time ( ' original version id is .. ' || p_original_version_id );
1688
1689
1690 ---------------------------------------------------------
1691 -- Mark as original only financial plans.
1692 ---------------------------------------------------------
1693
1694 BEGIN
1695 SELECT wp_flag
1696 INTO l_wp_flag
1697 FROM pji_pjp_wbs_header
1698 WHERE plan_version_id = p_original_version_id;
1699 EXCEPTION
1700 WHEN OTHERS THEN
1701 NULL;
1702 END;
1703
1704 IF ( l_wp_flag = 'Y' ) THEN
1705 RETURN;
1706 END IF;
1707
1708
1709 ---------------------------------------------------------------------------
1710 -- Get plan type id and project id for the version tb marked original.
1711 ---------------------------------------------------------------------------
1712
1713 BEGIN
1714
1715 SELECT bv1.project_id, bv1.fin_plan_type_id,
1716 DECODE(bv1.version_type, 'COST' , 'C' , 'REVENUE' , 'R' , 'A')
1717 INTO l_project_id, l_plan_type_id, l_plan_type_code
1718 FROM pa_budget_versions bv1
1719 WHERE bv1.budget_version_id = p_original_version_id;
1720
1721 EXCEPTION
1722 WHEN OTHERS THEN
1723 NULL;
1724 END;
1725
1726 --
1727 -- Get plan version id for the previous original version.
1728 --
1729 BEGIN
1730 SELECT plan_version_id
1731 INTO l_curr_or_ver_id
1732 FROM PJI_PJP_WBS_HEADER wbs_hdr
1733 WHERE 1 = 1
1734 AND project_id = l_project_id
1735 AND plan_type_id = l_plan_type_id
1736 AND co_flag = 'Y'
1737 AND plan_version_id > 0
1738 AND plan_type_code = l_plan_type_code ; /* 4471527 */
1739 EXCEPTION
1740 WHEN NO_DATA_FOUND THEN
1741 print_time('Currently, there is no original baseline version so far for this plan type.');
1742 NULL;
1743 WHEN OTHERS THEN
1744 RAISE;
1745 END;
1746
1747
1748 --
1749 -- Create an event..
1750 --
1751 l_event_rec.event_type := 'PLAN_ORIGINAL';
1752 l_event_rec.event_object := p_original_version_id;
1753 l_event_rec.operation_type := 'U';
1754 l_event_rec.status := 'X';
1755 l_event_rec.attribute1 := l_project_id;
1756 l_event_rec.attribute2 := l_plan_type_id;
1757 l_event_rec.attribute3 := l_curr_or_ver_id;
1758 l_event_rec.attribute4 := l_plan_type_code; /* 4771527 */
1759
1760 CREATE_EVENT(l_event_rec);
1761
1762
1763 --
1764 -- Call the private api that actually handles the mark original process.
1765 --
1766 PLAN_ORIGINAL_PVT(
1767 p_event_id => l_event_rec.event_id
1768 , x_processing_code => l_processing_code
1769 , x_return_status => x_return_status );
1770
1771 --COMMIT;
1772
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1776 ( p_package_name => g_package_name
1777 , p_procedure_name => 'PLAN_ORIGINAL'
1778 , x_return_status => x_return_status ) ;
1779
1780 RAISE;
1781 END;
1782
1783
1784 PROCEDURE PLAN_ORIGINAL_PVT (
1785 p_event_id IN NUMBER
1786 , x_return_status OUT NOCOPY VARCHAR2
1787 , x_processing_code OUT NOCOPY VARCHAR2) IS
1788
1789 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1790 l_source_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1791 l_dest_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1792 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1793 l_wp_flag VARCHAR2(1);
1794 l_return_status VARCHAR2(1);
1795 l_msg_code VARCHAR2(100);
1796 l_event_id NUMBER;
1797 l_event_rec pa_pji_proj_events_log%ROWTYPE;
1798 l_project_id NUMBER := NULL;
1799 l_old_orig_ver_id NUMBER := NULL;
1800 l_new_orig_ver_id NUMBER := NULL;
1801 l_plan_type_id NUMBER := NULL;
1802 l_new_project_type pa_projects_all.project_type%TYPE;
1803 l_new_plan_type_code pa_budget_versions.budget_type_code%TYPE;
1804 l_original_exists NUMBER := NULL;
1805 l_processing_code VARCHAR2(1) := NULL;
1806
1807 l_last_update_date date := SYSDATE;
1808 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
1809 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
1810 l_plan_type_code char(1) ; /*4771527 */
1811
1812 BEGIN
1813
1814 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1815 ( p_package_name => g_package_name
1816 , x_return_status => x_return_status );
1817
1818 x_processing_code := Fnd_Api.G_RET_STS_SUCCESS;
1819
1820
1821 -----------------------------------------------------------------------------------
1822 -- Get event info.
1823 -----------------------------------------------------------------------------------
1824
1825 SELECT attribute1, attribute3, event_object, attribute2,attribute4
1826 INTO l_project_id, l_old_orig_ver_id, l_new_orig_ver_id, l_plan_type_id,l_plan_type_code /*4771527 */
1827 FROM PA_PJI_PROJ_EVENTS_LOG
1828 WHERE event_id = p_event_id;
1829
1830
1831 ----------------------------------------------------------------------------------------
1832 -- First, need to lock all relevant plan versions, if this is not possible, return.
1833 ----------------------------------------------------------------------------------------
1834
1835 l_fp_version_ids := SYSTEM.pa_num_tbl_type (l_new_orig_ver_id);
1836
1837 /*
1838 CHECK_BUDGET_VERSION_EXISTS(l_fp_version_ids);
1839
1840 WBS_HEADERS_LOCK (
1841 p_fp_version_ids => l_fp_version_ids
1842 , x_return_status => l_return_status
1843 , x_msg_code => l_msg_code ) ;
1844
1845 x_processing_code := l_msg_code;
1846
1847 IF (l_fp_version_ids.COUNT <1) THEN
1848 RETURN;
1849 END IF;
1850 */
1851
1852 WBS_HEADERS_LOCK( -- _BASELINE (
1853 p_fp_version_ids => l_fp_version_ids
1854 , p_context => 'ORIGINAL'
1855 , x_return_status => l_return_status
1856 , x_msg_code => l_msg_code );
1857
1858 x_processing_code := l_msg_code;
1859
1860 IF (l_fp_version_ids.COUNT <1) THEN
1861 print_time(' Original flow. # hdrs locked is not valid, returning. # = ' || l_fp_version_ids.COUNT );
1862 RETURN;
1863 END IF;
1864
1865
1866 -----------------------------------------------------------------------------------
1867 -- Update header table.
1868 -----------------------------------------------------------------------------------
1869
1870 UPDATE pji_pjp_wbs_header
1871 SET co_flag = DECODE(plan_version_id
1872 , l_old_orig_ver_id, 'N'
1873 , l_new_orig_ver_id, 'Y'
1874 , -4, 'Y')
1875 , LAST_UPDATE_DATE = l_last_update_date
1876 , LAST_UPDATED_BY = l_last_updated_by
1877 , LAST_UPDATE_LOGIN = l_last_update_login
1878 WHERE plan_version_id IN (l_old_orig_ver_id, l_new_orig_ver_id, -4)
1879 AND project_id = l_project_id
1880 AND plan_type_id = l_plan_type_id
1881 AND plan_type_code = l_plan_type_code ; /* 4771527 */
1882
1883 print_time('Marked the cb and co flags in WBS header correctly.');
1884
1885
1886 -----------------------------------------------------------------------------------
1887 -- Set online context.
1888 -----------------------------------------------------------------------------------
1889
1890 Pji_Pjp_Sum_Rollup.set_online_context (
1891 p_event_id => p_event_id,
1892 p_project_id => l_project_id,
1893 p_plan_type_id => NULL, -- l_plan_type_id
1894 p_old_baselined_version => NULL,
1895 p_new_baselined_version => NULL ,
1896 p_old_original_version => l_old_orig_ver_id,
1897 p_new_original_version => l_new_orig_ver_id,
1898 p_old_struct_version => NULL,
1899 p_new_struct_version => NULL);
1900
1901 Pji_Fm_Plan_Maint_T_Pvt.CLEANUP_INTERIM_TABLES; -- Clean up interim tables.
1902 print_time(' Mark original private: Cleaned up interim tables. ');
1903
1904 -- Populated ver3 just to get the level info in rollup_fpr_wbs Bug 5528058
1905 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(l_fp_version_ids, 'PRI');
1906 -- PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(l_fp_version_ids, 'SECRBS');
1907 print_time(' Populated ver3. ');
1908
1909 PJI_FM_PLAN_MAINT_T_PVT.CREATE_WBSRLP;
1910 print_time(' Mark original private: Created WBS rollups. ');
1911
1912 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
1913
1914 PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
1915 print_time('Updated the WBS header table with min max txn dates.');
1916
1917 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT;
1918 print_time(' Mark original private: Merged into fact. ');
1919
1920
1921 ------------------------------------------
1922 -- Delete from events table.
1923 ------------------------------------------
1924
1925 l_event_ids := SYSTEM.pa_num_tbl_type (p_event_id);
1926
1927 DELETE_EVENTS (
1928 p_event_ids => l_event_ids
1929 , x_return_status => l_return_status
1930 , x_msg_code => l_msg_code );
1931
1932 Pji_Fm_Plan_Maint_T_Pvt.CLEANUP_INTERIM_TABLES;
1933 print_time(' Mark original private: Cleaned up interim tables. ');
1934
1935 EXCEPTION
1936 WHEN OTHERS THEN
1937 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1938 ( p_package_name => g_package_name
1939 , p_procedure_name => 'PLAN_ORIGINAL_PVT'
1940 , x_return_status => x_return_status ) ;
1941
1942 RAISE;
1943 END;
1944
1945
1946 PROCEDURE PRG_CHANGE (
1947 p_prg_grp_id IN NUMBER,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_code OUT NOCOPY VARCHAR2 ) IS
1950
1951 l_event_rec pa_pji_proj_events_log%ROWTYPE;
1952 l_return_status VARCHAR2(1);
1953 l_msg_code VARCHAR2(100);
1954
1955 BEGIN
1956
1957 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1958 ( p_package_name => g_package_name
1959 , x_return_status => x_return_status );
1960
1961
1962 --
1963 -- Create an event..
1964 --
1965 l_event_rec.event_type := 'PRG_CHANGE';
1966 l_event_rec.event_object := p_prg_grp_id;
1967 l_event_rec.operation_type := 'I';
1968 l_event_rec.status := 'X';
1969
1970 create_event(l_event_rec);
1971
1972 --COMMIT;
1973
1974 -- Summarization process will pick up this event.
1975
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
1979 ( p_package_name => g_package_name
1980 , p_procedure_name => 'PRG_CHANGE'
1981 , x_return_status => x_return_status ) ;
1982
1983 RAISE;
1984 END;
1985
1986
1987
1988 PROCEDURE RBS_PUSH (
1989 p_old_rbs_version_id IN NUMBER DEFAULT NULL
1990 , p_new_rbs_version_id IN NUMBER
1991 , p_project_id IN NUMBER DEFAULT NULL
1992 , p_program_flag IN VARCHAR2 DEFAULT 'N'
1993 , x_return_status OUT NOCOPY VARCHAR2
1994 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
1995
1996 l_event_rec pa_pji_proj_events_log%ROWTYPE;
1997 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1998 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1999 l_return_status VARCHAR2(1);
2000 l_msg_code VARCHAR2(100);
2001 l_template_flag VARCHAR2(1);
2002
2003 BEGIN
2004
2005 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
2006 ( p_package_name => g_package_name
2007 , x_return_status => x_return_status );
2008
2009
2010 l_event_rec.event_object := p_new_rbs_version_id;
2011 l_event_rec.operation_type := 'I';
2012 l_event_rec.status := 'X';
2013
2014
2015 IF ( (p_program_flag = 'N') AND (p_old_rbs_version_id IS NOT NULL) ) THEN -- RBS version is frozen.
2016
2017 l_event_rec.event_type := 'RBS_PUSH';
2018 l_event_rec.attribute2 := p_old_rbs_version_id;
2019
2020 create_event(l_event_rec);
2021
2022 --
2023 -- Launch conc program. Todo..!!
2024 --
2025
2026 ELSIF ( (p_program_flag = 'N')
2027 AND (p_project_id IS NOT NULL) ) THEN -- RBS is associated with a project.
2028
2029 select TEMPLATE_FLAG
2030 into l_template_flag
2031 from PA_PROJECTS_ALL
2032 where PROJECT_ID = p_project_id;
2033 if (l_template_flag = 'Y') then
2034 return;
2035 end if;
2036
2037 l_event_rec.event_type := 'RBS_ASSOC';
2038 l_event_rec.attribute1 := p_project_id;
2039
2040 create_event(l_event_rec);
2041
2042 --
2043 -- Will be picked up by summarization program.
2044 --
2045
2046 ELSIF ( (p_program_flag = 'Y')
2047 AND (p_project_id IS NOT NULL) ) THEN -- RBS is associated with a program.
2048
2049 select TEMPLATE_FLAG
2050 into l_template_flag
2051 from PA_PROJECTS_ALL
2052 where PROJECT_ID = p_project_id;
2053 if (l_template_flag = 'Y') then
2054 return;
2055 end if;
2056
2057 l_event_rec.event_type := 'RBS_PRG';
2058 l_event_rec.attribute1 := p_project_id;
2059
2060 create_event(l_event_rec);
2061
2062 --
2063 -- Will be picked up by summarization program.
2064 --
2065
2066 ELSE
2067 RETURN;
2068 END IF;
2069
2070
2071 --COMMIT;
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
2076 ( p_package_name => g_package_name
2077 , p_procedure_name => 'RBS_PUSH'
2078 , x_return_status => x_return_status ) ;
2079
2080 RAISE;
2081 END;
2082
2083
2084 PROCEDURE RBS_DELETE (
2085 p_rbs_version_id IN NUMBER
2086 , p_project_id IN NUMBER
2087 , x_return_status OUT NOCOPY VARCHAR2
2088 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
2089
2090 l_event_rec pa_pji_proj_events_log%ROWTYPE;
2091 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2092 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2093 l_return_status VARCHAR2(1);
2094 l_msg_code VARCHAR2(100);
2095
2096 BEGIN
2097
2098 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
2099 ( p_package_name => g_package_name
2100 , x_return_status => x_return_status );
2101
2102 l_event_rec.event_type := 'RBS_DELETE';
2103 l_event_rec.event_object := p_rbs_version_id;
2104 l_event_rec.operation_type := 'D';
2105 l_event_rec.status := 'X';
2106
2107 create_event(l_event_rec);
2108
2109 /* This code goes in summarization program...
2110 WBS_HEADERS_LOCK (
2111 p_fp_version_ids => l_fp_version_ids
2112 , x_return_status => l_return_status
2113 , x_msg_code => l_msg_code );
2114
2115 IF (l_fp_version_ids.COUNT = 0) THEN RETURN; END IF;
2116
2117 GET_EVENT_IDS (
2118 p_fp_version_ids => l_fp_version_ids
2119 , p_operation_type => 'D'
2120 , x_event_ids => l_event_ids
2121 , x_return_status => l_return_status
2122 , x_msg_code => l_msg_code );
2123
2124 PJI_FM_PLAN_MAINT.FINPLAN_DELETE(
2125 p_fp_version_ids => l_fp_version_ids
2126 , p_commit => 'F' );
2127
2128 DELETE_EVENTS (
2129 p_event_ids => l_event_ids
2130 , x_return_status => l_return_status
2131 , x_msg_code => l_msg_code );
2132 */
2133
2134 --COMMIT;
2135
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
2139 ( p_package_name => g_package_name
2140 , p_procedure_name => 'RBS_DELETE'
2141 , x_return_status => x_return_status ) ;
2142
2143 RAISE;
2144 END;
2145
2146
2147
2148 PROCEDURE CREATE_EVENT( p_event_rec IN OUT NOCOPY pa_pji_proj_events_log%ROWTYPE) IS
2149 -- l_event_rec pa_pji_proj_events_log%ROWTYPE;
2150 event_already_exists VARCHAR2(1) := 'N'; -- Added for Bug#6145813 by vvjoshi
2151 l_rbs_header_id PA_RBS_VERSIONS_B.rbs_header_id%TYPE; -- Added for Bug#6145813 by vvjoshi
2152 l_return_status VARCHAR2(1);
2153 BEGIN
2154
2155 -- l_event_rec := p_event_rec;
2156 p_event_rec.creation_date := SYSDATE;
2157 p_event_rec.last_update_date := SYSDATE;
2158 p_event_rec.last_updated_by := Fnd_Global.USER_ID;
2159 p_event_rec.created_by := Fnd_Global.USER_ID;
2160 p_event_rec.last_update_login := Fnd_Global.LOGIN_ID;
2161
2162 IF (p_event_rec.event_id IS NULL) THEN
2163 SELECT pa_pji_proj_events_log_s.NEXTVAL
2164 INTO p_event_rec.event_id
2165 FROM DUAL;
2166 END IF;
2167
2168 /* Added for Bug#6145813 by vvjoshi - START*/
2169
2170 IF (p_event_rec.event_type = 'RBS_PUSH') THEN
2171
2172 begin
2173 select 'Y'
2174 into event_already_exists
2175 from (select attribute2
2176 from pa_pji_proj_events_log
2177 where event_type = 'RBS_PUSH'
2178 )a ,
2179 pa_rbs_versions_b b
2180 where to_number(a.attribute2) = b.rbs_version_id
2181 and b.rbs_header_id =
2182 (select rbs_header_id
2183 from pa_rbs_versions_b
2184 where rbs_version_id = p_event_rec.attribute2)
2185 and rownum = 1;
2186 exception
2187 when NO_DATA_FOUND then
2188 event_already_exists := 'N';
2189 end;
2190
2191 END IF;
2192
2193 IF event_already_exists = 'Y' THEN
2194
2195 select rbs_header_id
2196 into l_rbs_header_id
2197 from pa_rbs_versions_b
2198 where rbs_version_id = to_number(p_event_rec.attribute2);
2199
2200 UPDATE pa_pji_proj_events_log
2201 SET event_object = p_event_rec.event_object,
2202 last_update_date = p_event_rec.last_update_date,
2203 last_updated_by = p_event_rec.last_updated_by
2204 WHERE event_type = 'RBS_PUSH'
2205 AND attribute2 in (select rbs_version_id
2206 from pa_rbs_versions_b
2207 where rbs_header_id = l_rbs_header_id);
2208
2209 ELSE
2210 /* Added for Bug#6145813 by vvjoshi - END*/
2211
2212 INSERT INTO pa_pji_proj_events_log
2213 ( event_type
2214 , event_id
2215 , event_object
2216 , operation_type
2217 , status
2218 , last_update_date
2219 , last_updated_by
2220 , creation_date
2221 , created_by
2222 , last_update_login
2223 , attribute_category
2224 , attribute1
2225 , attribute2
2226 , attribute3
2227 , attribute4
2228 , attribute5
2229 , attribute6
2230 , attribute7
2231 , attribute8
2232 , attribute9
2233 , attribute10
2234 , attribute11
2235 , attribute12
2236 , attribute13
2237 , attribute14
2238 , attribute15
2239 , attribute16
2240 , attribute17
2241 , attribute18
2242 , attribute19
2243 , attribute20
2244 )
2245 VALUES (
2246 p_event_rec.event_type
2247 , p_event_rec.event_id
2248 , p_event_rec.event_object
2249 , p_event_rec.operation_type
2250 , p_event_rec.status
2251 , p_event_rec.last_update_date
2252 , p_event_rec.last_updated_by
2253 , p_event_rec.creation_date
2254 , p_event_rec.created_by
2255 , p_event_rec.last_update_login
2256 , p_event_rec.attribute_category
2257 , p_event_rec.attribute1
2258 , p_event_rec.attribute2
2259 , p_event_rec.attribute3
2260 , p_event_rec.attribute4
2261 , p_event_rec.attribute5
2262 , p_event_rec.attribute6
2263 , p_event_rec.attribute7
2264 , p_event_rec.attribute8
2265 , p_event_rec.attribute9
2266 , p_event_rec.attribute10
2267 , p_event_rec.attribute11
2268 , p_event_rec.attribute12
2269 , p_event_rec.attribute13
2270 , p_event_rec.attribute14
2271 , p_event_rec.attribute15
2272 , p_event_rec.attribute16
2273 , p_event_rec.attribute17
2274 , p_event_rec.attribute18
2275 , p_event_rec.attribute19
2276 , p_event_rec.attribute20
2277 );
2278
2279 END IF; -- Added for Bug#6145813 by vvjoshi
2280
2281 -- p_event_rec := l_event_rec ;
2282
2283 -- INSERT INTO pa_pji_proj_events_log
2284 -- VALUES l_event_rec;
2285
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
2289 ( p_package_name => g_package_name
2290 , p_procedure_name => 'CREATE_EVENT'
2291 , x_return_status => l_return_status ) ;
2292
2293 RAISE;
2294 END;
2295
2296
2297 ----------
2298 -- API to lock headers, used by online and bulk summarization apis.
2299 ----------
2300 PROCEDURE WBS_HEADERS_LOCK (
2301 p_fp_version_ids IN OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
2302 , p_context IN VARCHAR2
2303 , x_return_status OUT NOCOPY VARCHAR2
2304 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
2305
2306 i NUMBER := NULL;
2307 l_lock_flag VARCHAR2(1);
2308 l_count NUMBER := 0;
2309 l_count1 NUMBER := 0;
2310 l_num_locked NUMBER := 0;
2311
2312 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); --initialized the collection bug#4001139
2313 l_project_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); --initialized the collection bug#4001139
2314 l_baseline_fp_ver_id SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); --initialized the collection bug#4001139
2315 l_wp_flags SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
2316 l_latest_pub_flags SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
2317 l_baselined_flags SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
2318 l_published_flags SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
2319 l_vers_enabled_flags SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
2320
2321 l_fp_version_id NUMBER;
2322 l_wp_flag VARCHAR2(1);
2323 l_latest_pub_flag VARCHAR2(1);
2324 l_baselined_flag VARCHAR2(1);
2325 l_published_flag VARCHAR2(1);
2326 l_vers_enabled_flag VARCHAR2(1);
2327
2328 l_wbs_version_id NUMBER := NULL;
2329
2330 l_last_update_date DATE := SYSDATE;
2331 l_last_updated_by NUMBER := Fnd_Global.USER_ID;
2332 l_creation_date DATE := SYSDATE;
2333 l_created_by NUMBER := Fnd_Global.USER_ID;
2334 l_last_update_login NUMBER := Fnd_Global.LOGIN_ID;
2335
2336 excp_resource_busy EXCEPTION;
2337 PRAGMA EXCEPTION_INIT(excp_resource_busy, -54);
2338
2339 -- Generic lock for any 1 given plan version.
2340 CURSOR c_wh_generic_lock_cur (p_plan_version_id IN NUMBER) IS
2341 SELECT project_id
2342 FROM pji_pjp_wbs_header
2343 WHERE plan_version_id = p_plan_version_id
2344 AND lock_flag IS NULL
2345 FOR UPDATE NOWAIT;
2346
2347 --
2348 -- Lock for progress update.
2349 -- Locks all latest published workplans above.
2350 --
2351 cursor c_wh_lock_update_cur (p_plan_version_id IN NUMBER,
2352 p_project_id IN NUMBER) IS
2353 select /*+ use_nl(sup_wbs_hdr)
2354 index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
2355 sup_wbs_hdr.PLAN_VERSION_ID pvi
2356 from
2357 PJI_PJP_WBS_HEADER sup_wbs_hdr
2358 where
2359 (sup_wbs_hdr.PROJECT_ID,
2360 sup_wbs_hdr.WBS_VERSION_ID) in
2361 (
2362 select /*+ ordered
2363 index(prg PJI_XBS_DENORM_N1) */
2364 prg.SUP_PROJECT_ID,
2365 prg.SUP_ID
2366 from
2367 PJI_PJP_WBS_HEADER sub_wbs_hdr,
2368 PA_PROJ_ELEM_VER_STRUCTURE ppevs2,
2369 PJI_XBS_DENORM prg
2370 where
2371 prg.struct_version_id is null and
2372 sub_wbs_hdr.PLAN_VERSION_ID = p_plan_version_id and
2373 sub_wbs_hdr.PROJECT_ID = p_project_id and
2374 sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
2375 prg.STRUCT_TYPE = 'PRG' and
2376 nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LW', 'WF') and
2377 sub_wbs_hdr.WP_FLAG = 'Y' and
2378 ppevs2.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
2379 ppevs2.ELEMENT_VERSION_ID = sub_wbs_hdr.WBS_VERSION_ID and
2380 ppevs2.LATEST_EFF_PUBLISHED_FLAG = 'Y'
2381 ) and
2382 sup_wbs_hdr.WP_FLAG = 'Y' and
2383 sup_wbs_hdr.LOCK_FLAG is null and
2384 exists
2385 (
2386 select
2387 1
2388 from
2389 PA_PROJ_ELEM_VER_STRUCTURE ppevs1
2390 where
2391 ppevs1.PROJECT_ID = sup_wbs_hdr.PROJECT_ID and
2392 ppevs1.ELEMENT_VERSION_ID = sup_wbs_hdr.WBS_VERSION_ID and
2393 ppevs1.LATEST_EFF_PUBLISHED_FLAG = 'Y'
2394 )
2395 for update nowait;
2396
2397 --
2398 -- Lock for plan baseline and original flows.
2399 -- Locks all -3/-4s above striped by plan type id.
2400 --
2401 cursor c_wh_base_orig_lock_cur (p_plan_version_id in number) is
2402 select /*+ use_nl(sup_wbs_hdr)
2403 index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
2404 sup_wbs_hdr.PROJECT_ID,
2405 sup_wbs_hdr.PLAN_VERSION_ID,
2406 sup_wbs_hdr.PLAN_TYPE_ID
2407 from
2408 PJI_PJP_WBS_HEADER sup_wbs_hdr
2409 where
2410 (sup_wbs_hdr.PROJECT_ID,
2411 sup_wbs_hdr.WBS_VERSION_ID,
2412 sup_wbs_hdr.PLAN_VERSION_ID,
2413 sup_wbs_hdr.PLAN_TYPE_ID,
2414 sup_wbs_hdr.PLAN_TYPE_CODE) in
2415 (
2416 select /*+ ordered
2417 index(prg PJI_XBS_DENORM_N1) */
2418 prg.SUP_PROJECT_ID,
2419 prg.SUP_ID,
2420 ver.BUDGET_VERSION_ID,
2421 ver.FIN_PLAN_TYPE_ID,
2422 sub_wbs_hdr.PLAN_TYPE_CODE
2423 from
2424 PA_BUDGET_VERSIONS ver,
2425 PJI_PJP_WBS_HEADER sub_wbs_hdr,
2426 PJI_XBS_DENORM prg
2427 where
2428 ver.BUDGET_VERSION_ID = p_plan_version_id and
2429 ver.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
2430 ver.FIN_PLAN_TYPE_ID = sub_wbs_hdr.PLAN_TYPE_ID and
2431 decode(ver.VERSION_TYPE,
2432 'COST', 'C',
2433 'REVENUE', 'R',
2434 'A') = sub_wbs_hdr.PLAN_TYPE_CODE and
2435 sub_wbs_hdr.PLAN_VERSION_ID in (-3, -4) and
2436 sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
2437 prg.STRUCT_TYPE = 'PRG' and
2438 prg.STRUCT_VERSION_ID is null and
2439 nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LF', 'WF')
2440 )
2441 for update nowait;
2442
2443 BEGIN
2444
2445 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
2446 ( p_package_name => g_package_name
2447 , x_return_status => x_return_status );
2448
2449 print_time ( ' wbs hdrs lock 1 p_context = ' || p_context );
2450
2451
2452 -- Validation of context.
2453 IF (p_context NOT IN ('DELETE', 'UPDATE', 'BASELINE', 'ORIGINAL', 'COPY')) THEN -- 'PUBLISH', 'STRUCT_CHANGE',
2454 print_time (' WBS_HEADERS_LOCK: The following value of p_context is invalid: ' || p_context);
2455 RETURN;
2456 END IF;
2457
2458
2459 -- About to process these plans.
2460 FOR i IN 1..p_fp_version_ids.COUNT LOOP
2461 print_time(' i = ' || i || ' pl. ver id = ' || p_fp_version_ids(i));
2462 END LOOP;
2463
2464
2465 --
2466 -- Validation of input plan versions, whether or not it exists in hdr tbl.
2467 --
2468
2469 l_count := 0;
2470
2471 FOR i IN 1..p_fp_version_ids.COUNT LOOP
2472 BEGIN
2473 SELECT /*+ index_ffs(wbs_hdr PJI_PJP_WBS_HEADER_N1) */ l_count+1
2474 INTO l_count
2475 FROM pji_pjp_wbs_header wbs_hdr
2476 WHERE plan_version_id = p_fp_version_ids(i);
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479 NULL;
2480 END;
2481 END LOOP;
2482 print_time ( ' wbs hdrs lock 1.001: # rcds in whdr is '|| l_count || ' # plan vers is ' || p_fp_version_ids.COUNT );
2483
2484 IF ( (l_count <> p_fp_version_ids.COUNT)
2485 AND (p_context NOT IN ('BASELINE'))
2486 ) THEN
2487 print_time ( ' wbs hdrs lock 1.01: Not all of these plan versions exist. Pl. pass valid plan vers in plan ver id tbl. Returning.' );
2488 RETURN;
2489 END IF;
2490
2491
2492 --
2493 -- Validation of input plan versions, if they are already locked in hdr tbl.
2494 --
2495
2496 l_count := 0;
2497
2498 FOR i IN 1..p_fp_version_ids.COUNT LOOP
2499 BEGIN
2500 SELECT l_count+1
2501 INTO l_count
2502 FROM pji_pjp_wbs_header
2503 WHERE plan_version_id = p_fp_version_ids(i)
2504 AND lock_flag IS NULL;
2505 EXCEPTION
2506 WHEN OTHERS THEN
2507 NULL;
2508 END;
2509 END LOOP;
2510 print_time ( ' wbs hdrs lock 1.1: l_count is '|| l_count );
2511
2512 IF ( (l_count <> p_fp_version_ids.COUNT)
2513 AND (p_context NOT IN ('BASELINE'))
2514 ) THEN
2515 print_time ( ' wbs hdrs lock 1.11: some plans in the program are already locked, raising exception. ' );
2516 RAISE excp_resource_busy;
2517 END IF;
2518
2519
2520 --
2521 -- Process each plan version for this context.
2522 --
2523 FOR i IN 1..p_fp_version_ids.COUNT LOOP -- 1
2524
2525 print_time( ' wbs hdrs lock 1.11 inside loop.' );
2526
2527 l_fp_version_ids.EXTEND;
2528 l_project_ids.EXTEND;
2529 l_wp_flags.EXTEND;
2530 l_latest_pub_flags.EXTEND;
2531 l_baselined_flags.EXTEND;
2532 l_published_flags.EXTEND;
2533 l_vers_enabled_flags.EXTEND;
2534
2535 l_count := l_fp_version_ids.COUNT ;
2536
2537 print_time( ' wbs hdrs lock 1.2:l_count ' || l_count || ' i ' || i || ' p_fp_version_ids(i) ' || p_fp_version_ids(i));
2538
2539 IF (p_context NOT IN ('BASELINE', 'DELETE')) THEN
2540
2541 -- Get the plan version properties: is it wp/fp, is it versioning enabled
2542 -- , is the structure working or published, is it latest pub, is it the baselined version?
2543 SELECT DISTINCT
2544 wh.plan_version_id pv
2545 , wh.project_id pi
2546 , wh.wp_flag wf
2547 , ppwa.wp_enable_version_flag vef
2548 , DECODE(ppevs.status_code, 'STRUCTURE_PUBLISHED', 'P', 'STRUCTURE_WORKING', 'W') pf
2549 , ppevs.latest_eff_published_flag lpf
2550 , DECODE(wh.cb_flag, 'Y', 'B', 'W') bf
2551 INTO
2552 l_fp_version_ids(l_count)
2553 , l_project_ids(l_count)
2554 , l_wp_flags(l_count)
2555 , l_vers_enabled_flags(l_count)
2556 , l_published_flags(l_count)
2557 , l_latest_pub_flags(l_count)
2558 , l_baselined_flags(l_count)
2559 FROM PA_PROJ_WORKPLAN_ATTR ppwa
2560 , PA_PROJ_ELEMENTS ppe
2561 , PA_PROJ_STRUCTURE_TYPES ppst
2562 , PA_STRUCTURE_TYPES pst
2563 , PA_PROJ_ELEM_VER_STRUCTURE ppevs
2564 , pji_pjp_wbs_header wh
2565 -- , pa_budget_versions bv -- can't depend on bv in plan delete flow.
2566 WHERE 1=1
2567 AND ppe.project_id = ppwa.project_id
2568 AND ppe.proj_element_id = ppwa.proj_element_id
2569 AND ppe.proj_element_id = ppst.proj_element_id
2570 AND ppe.object_type = 'PA_STRUCTURES' -- Added for perf improvement bug 6430959
2571 AND ppst.structure_type_id = pst.structure_type_id
2572 AND pst.structure_type_class_code = DECODE (wh.wp_flag, 'Y', 'WORKPLAN', 'FINANCIAL')
2573 AND ppevs.project_id = ppe.project_id
2574 AND ppevs.project_id = wh.project_id
2575 AND ppevs.element_version_id = wh.wbs_version_id
2576 AND ppevs.status_code IN ('STRUCTURE_PUBLISHED', 'STRUCTURE_WORKING')
2577 AND wh.plan_version_id = p_fp_version_ids(i) ;
2578
2579 ELSE
2580
2581 l_fp_version_ids(l_count) := p_fp_version_ids(l_count);
2582
2583 END IF;
2584
2585
2586 /*
2587 l_fp_version_ids(l_count) := l_fp_version_id;
2588 l_wp_flags(l_count) := l_wp_flag;
2589 l_vers_enabled_flags(l_count) := l_vers_enabled_flag;
2590 l_published_flags(l_count) := l_published_flag;
2591 l_latest_pub_flags(l_count) := l_latest_pub_flag;
2592 l_baselined_flags(l_count) := l_baselined_flag;
2593 */
2594
2595 print_time( ' wbs hdrs lock 1.3: l_fp_version_id ' || l_fp_version_ids(l_count));
2596 print_time( ' wbs hdrs lock 1.3: l_project_id ' || l_project_ids(l_count));
2597 print_time( ' wbs hdrs lock 1.3: l_wp_flag ' || l_wp_flags(l_count));
2598 print_time( ' wbs hdrs lock 1.3: l_vers_enabled_flag ' || l_vers_enabled_flags(l_count));
2599 print_time( ' wbs hdrs lock 1.3: l_published_flag ' || l_published_flags(l_count));
2600 print_time( ' wbs hdrs lock 1.3: l_latest_pub_flag ' || l_latest_pub_flags(l_count));
2601 print_time( ' wbs hdrs lock 1.3: l_baselined_flag ' || l_baselined_flags(l_count));
2602
2603 IF ( (p_context = 'UPDATE')
2604 AND (l_fp_version_ids(l_count) > 0)) THEN -- 2
2605
2606 IF ( -- 3
2607 (
2608 ( l_wp_flags(l_count) = 'Y' )
2609 AND (
2610 ( l_published_flags(l_count) = 'N' )
2611 OR ( l_latest_pub_flags(l_count) = 'Y' )
2612 OR ( l_vers_enabled_flags(l_count) = 'N' )
2613 )
2614 )
2615 OR
2616 (
2617 ( l_wp_flags(l_count) = 'N' )
2618 AND ( l_baselined_flags(l_count) = 'N')
2619 )
2620 ) THEN
2621
2622 IF ( -- 4
2623 ( l_wp_flags(l_count) = 'Y' )
2624 AND (
2625 ( l_latest_pub_flags(l_count) = 'Y' )
2626 OR ( l_vers_enabled_flags(l_count) = 'N' )
2627 )
2628 ) THEN
2629
2630
2631 l_num_locked := 0;
2632
2633
2634 -- Are any of the relevant WPs locked? Refer Program Reporting Tech Arch for biz rules.
2635 SELECT COUNT(1)
2636 INTO l_num_locked
2637 FROM pji_xbs_denorm den
2638 , pji_pjp_wbs_header hd1 -- SUP
2639 , pji_pjp_wbs_header hd2 -- SUB
2640 , PA_PROJ_ELEM_VER_STRUCTURE ppevs1
2641 , PA_PROJ_ELEM_VER_STRUCTURE ppevs2
2642 WHERE
2643 den.struct_version_id IS NULL
2644 AND hd2.plan_version_id = l_fp_version_ids(l_count)
2645 AND hd2.project_id = l_project_ids(l_count)
2646 AND hd2.plan_type_id = hd1.plan_type_id
2647 AND hd2.wbs_version_id = den.sub_id -- struct_version_id
2648 AND hd1.wbs_version_id = den.sup_id
2649 AND den.struct_type = 'PRG'
2650 AND NVL(den.relationship_type, 'WF') IN ('LW', 'WF') -- 'LW',
2651 AND hd1.wp_flag = 'Y'
2652 AND ppevs1.element_version_id = hd1.wbs_version_id
2653 AND ppevs1.project_id = hd1.project_id
2654 AND ppevs1.latest_eff_published_flag = 'Y'
2655 AND hd2.wp_flag = 'Y'
2656 AND ppevs2.project_id = hd2.project_id
2657 AND ppevs2.element_version_id = hd2.wbs_version_id
2658 AND ppevs2.latest_eff_published_flag = 'Y'
2659 AND hd1.lock_flag IS NOT NULL;
2660
2661
2662 IF (l_num_locked = 0) THEN -- 5
2663
2664 OPEN c_wh_lock_update_cur (
2665 p_plan_version_id => l_fp_version_ids(l_count)
2666 , p_project_id => l_project_ids(l_count));
2667
2668 l_num_locked := SQL%ROWCOUNT;
2669
2670 CLOSE c_wh_lock_update_cur;
2671
2672 ELSE
2673
2674 print_time ( ' wbs hdrs lock 1.12: some plans in the program are already locked, raising exception. ' );
2675 RAISE excp_resource_busy;
2676
2677 END IF; -- 5
2678
2679 ELSIF -- 4
2680 (
2681 (
2682 ( l_wp_flags(l_count) = 'Y' )
2683 AND ( l_published_flags(l_count) = 'N' )
2684 AND ( l_vers_enabled_flags(l_count) = 'Y' )
2685 )
2686 OR
2687 ( l_wp_flags(l_count) = 'N' )
2688 ) THEN
2689
2690 SELECT COUNT(1)
2691 INTO l_num_locked
2692 FROM pji_pjp_wbs_header
2693 WHERE plan_version_id = l_fp_version_ids(l_count)
2694 AND lock_flag IS NOT NULL;
2695
2696 IF (l_num_locked = 0) THEN -- 5
2697
2698 OPEN c_wh_generic_lock_cur(
2699 p_plan_version_id => l_fp_version_ids(l_count));
2700
2701 l_num_locked := SQL%ROWCOUNT;
2702
2703 CLOSE c_wh_generic_lock_cur;
2704
2705 ELSE
2706
2707 print_time ( ' wbs hdrs lock 1.13: some plans in the program are already locked, raising exception. ' );
2708 RAISE excp_resource_busy;
2709
2710 END IF; -- 5
2711
2712 END IF; -- 4
2713
2714 END IF; -- 3
2715
2716 ELSIF ( (p_context IN ('COPY', 'DELETE'))
2717 AND (l_fp_version_ids(l_count) > 0) ) THEN -- 2
2718 -- Only working FPs/WPs can be deleted.
2719
2720 SELECT COUNT(1)
2721 INTO l_num_locked
2722 FROM pji_pjp_wbs_header
2723 WHERE plan_version_id = l_fp_version_ids(l_count)
2724 AND lock_flag IS NOT NULL;
2725
2726 IF (l_num_locked = 0) THEN -- 3
2727
2728 OPEN c_wh_generic_lock_cur(
2729 p_plan_version_id => l_fp_version_ids(l_count));
2730
2731 l_num_locked := SQL%ROWCOUNT;
2732
2733 CLOSE c_wh_generic_lock_cur;
2734
2735 ELSE
2736
2737 print_time ( ' wbs hdrs lock 1.14: some plans in the program are already locked, raising exception. ' );
2738 RAISE excp_resource_busy;
2739
2740 END IF; -- 3
2741
2742 ELSIF ( (p_context = 'PUBLISH')
2743 AND (l_fp_version_ids(l_count) > 0)) THEN -- 2
2744 -- Only working FPs/WPs can be deleted.
2745
2746 SELECT COUNT(1)
2747 INTO l_num_locked
2748 FROM pji_pjp_wbs_header
2749 WHERE plan_version_id = l_fp_version_ids(l_count)
2750 AND lock_flag IS NOT NULL;
2751
2752 IF (l_num_locked = 0) THEN -- 3
2753
2754 OPEN c_wh_generic_lock_cur(
2755 p_plan_version_id => l_fp_version_ids(l_count));
2756
2757 l_num_locked := SQL%ROWCOUNT;
2758
2759 CLOSE c_wh_generic_lock_cur;
2760
2761 ELSE
2762
2763 print_time ( ' wbs hdrs lock 1.15: some plans in the program are already locked, raising exception. ' );
2764 RAISE excp_resource_busy;
2765
2766 END IF; -- 3
2767
2768 ELSIF ( (p_context IN ('BASELINE', 'ORIGINAL') )
2769 AND (l_fp_version_ids(l_count) > 0)) THEN -- 2
2770
2771 SELECT PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id)
2772 INTO l_wbs_version_id
2773 FROM pa_budget_versions bv
2774 WHERE budget_version_id = l_fp_version_ids(l_count);
2775
2776 print_time ( ' wbs hdrs baseline lock 1.1 wbs version id is .. ' || l_wbs_version_id );
2777
2778 l_baseline_fp_ver_id.EXTEND;
2779 l_baseline_fp_ver_id(l_baseline_fp_ver_id.COUNT) := l_fp_version_ids(l_count);
2780
2781
2782 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES;
2783 print_time(' Cleaned up ver3_t table. ');
2784
2785
2786 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(l_baseline_fp_ver_id, 'PRI');
2787 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(l_baseline_fp_ver_id, 'SECRBS');
2788 print_time(' Populated ver3. ');
2789
2790
2791 INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
2792 (
2793 PROJECT_ID ,
2794 PLAN_VERSION_ID ,
2795 WBS_STRUCT_VERSION_ID ,
2796 RBS_STRUCT_VERSION_ID ,
2797 PLAN_TYPE_CODE ,
2798 PLAN_TYPE_ID ,
2799 TIME_PHASED_TYPE_CODE ,
2800 TIME_DANGLING_FLAG ,
2801 RATE_DANGLING_FLAG ,
2802 PROJECT_TYPE_CLASS ,
2803 WP_FLAG ,
2804 CURRENT_FLAG ,
2805 ORIGINAL_FLAG ,
2806 CURRENT_ORIGINAL_FLAG ,
2807 BASELINED_FLAG ,
2808 SECONDARY_RBS_FLAG ,
2809 LP_FLAG
2810 )
2811 SELECT -- DISTINCT
2812 den.sup_project_id project_id
2813 , cbco.plan_version_id -- bv.budget_version_id -- -3 --
2814 , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(den.sup_project_id) wbs_struct_version_id
2815 , bv.rbs_struct_version_id
2816 , bv.plan_type_code
2817 , bv.plan_type_id
2818 , bv.time_phased_type_code
2819 , NULL -- time dangl flg
2820 , NULL -- rate dangl flg
2821 , NULL -- project type class
2822 , 'N' -- wp flag
2823 , DECODE(cbco.plan_version_id, -3, 'Y', 'N') current_flag
2824 , DECODE(cbco.plan_version_id, -4, 'Y', 'N') original_flag
2825 , DECODE(cbco.plan_version_id, -4, 'Y', 'N') -- curr_original flag
2826 , 'Y' -- baselined flag.
2827 , bv.SECONDARY_RBS_FLAG
2828 , bv.lp_flag
2829 FROM pji_fm_extr_plnver3_T bv
2830 , pji_xbs_denorm den
2831 , ( SELECT -3 plan_version_id FROM DUAL
2832 UNION ALL
2833 SELECT -4 FROM DUAL ) cbco
2834 WHERE 1=1
2835 AND bv.plan_version_id = l_fp_version_ids(l_count)
2836 AND bv.wp_flag = 'N'
2837 AND bv.baselined_flag = 'Y'
2838 AND den.struct_version_id IS NULL
2839 AND den.struct_type = 'PRG'
2840 AND den.sub_id = bv.wbs_struct_version_id
2841 AND NVL(den.relationship_type, 'WF') IN ('LF', 'WF') -- Excluding 'LW'
2842 ;
2843
2844 DELETE FROM pji_fm_extr_plnver3_t
2845 WHERE plan_version_id > 0;
2846 print_time(' Need only -3, -4 records, deleted other plan version. ');
2847
2848
2849 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_RBS_HDR;
2850 print_time ( ' Inserted -3, -4 RBS headers inserted. ' );
2851
2852 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_WBS_HDR;
2853 print_time ( ' Inserted -3, -4 WBS headers inserted. ' );
2854
2855
2856 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES;
2857 print_time ( ' Cleaned up ver3. ' );
2858
2859
2860 SELECT COUNT(1)
2861 INTO l_count1
2862 FROM pji_xbs_denorm den
2863 , pji_pjp_wbs_header hd1 -- SUB
2864 , pji_pjp_wbs_header hd2 -- SUP
2865 , pa_budget_versions hd3 -- to get plan type id
2866 WHERE
2867 den.struct_version_id IS NULL
2868 AND den.struct_type = 'PRG'
2869 AND hd1.wbs_version_id = den.sub_id -- struct_version_id
2870 AND hd2.wbs_version_id = den.sup_id
2871 -- AND den.sup_level <= den.sub_level
2872 AND hd3.budget_version_id = l_fp_version_ids(l_count)
2873 AND hd3.project_id = hd1.project_id
2874 AND hd3.fin_plan_type_id = hd2.plan_type_id
2875 AND hd3.fin_plan_type_id = hd1.plan_type_id
2876 AND DECODE(hd3.version_type,'COST','C','REVENUE','R','A') = hd1.plan_type_code
2877 AND hd1.plan_type_code = hd2.plan_type_code /*4471527*/
2878 AND NVL(den.relationship_type, 'WF') IN ('LF', 'WF') -- Excluding 'LW'
2879 AND hd1.plan_version_id IN (-3, -4)
2880 AND hd2.plan_version_id IN (-3, -4)
2881 AND hd1.plan_version_id = hd2.plan_version_id
2882 AND hd2.lock_flag IS NOT NULL;
2883
2884 print_time ( ' # of locked -3, -4 headers for this project is '|| l_count1 );
2885
2886 IF (l_count1 > 0) THEN -- Summarization is locking the -3, -4 headers above this project.
2887 print_time ( ' Summarization is locking the -3, -4 headers above this project. # locks is '|| l_count1 );
2888 RAISE excp_resource_busy;
2889 END IF;
2890
2891 print_time ( ' wbs hdrs baseline lock 2 ');
2892
2893 OPEN c_wh_base_orig_lock_cur (p_plan_version_id => l_fp_version_ids(l_count));
2894 CLOSE c_wh_base_orig_lock_cur;
2895
2896 print_time ( ' wbs hdrs baseline lock 3 ');
2897
2898 END IF; -- 2
2899
2900 END LOOP; -- 1
2901
2902 print_time(' l_num_locked finally = ' || l_num_locked );
2903
2904 p_fp_version_ids := l_fp_version_ids;
2905
2906 print_time ( ' wbs hdrs lock 3 ');
2907
2908 EXCEPTION
2909 WHEN excp_resource_busy THEN
2910
2911 x_return_status := FND_API.G_RET_STS_ERROR;
2912
2913 x_msg_code := FND_MESSAGE.GET_STRING( APPIN => 'PJI'
2914 , NAMEIN => 'PJI_LOCK_NOT_OBTAINED');
2915
2916 -- PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PJI'
2917 -- , p_msg_name => 'PJI_LOCK_NOT_OBTAINED');
2918 FND_MESSAGE.SET_NAME('PJI', 'PJI_LOCK_NOT_OBTAINED');
2919 Fnd_Msg_Pub.add_detail(p_message_type=>FND_API.G_RET_STS_ERROR);
2920
2921 print_time ( ' wbs hdrs lock exception ' || SQLERRM);
2922
2923 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
2924 ( p_package_name => g_package_name
2925 , p_procedure_name => 'WBS_HEADERS_LOCK'
2926 , x_return_status => x_return_status ) ;
2927
2928 RAISE;
2929
2930 WHEN OTHERS THEN
2931 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
2932 ( p_package_name => g_package_name
2933 , p_procedure_name => 'WBS_HEADERS_LOCK'
2934 , x_return_status => x_return_status ) ;
2935
2936 RAISE;
2937 END;
2938
2939
2940 PROCEDURE PLAN_EXTR_LINES_LOCK (
2941 x_return_status OUT NOCOPY VARCHAR2
2942 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
2943
2944 l_project_id NUMBER;
2945
2946 excp_resource_busy EXCEPTION;
2947 PRAGMA EXCEPTION_INIT(excp_resource_busy, -54);
2948
2949 CURSOR c_lines IS
2950 SELECT project_id
2951 FROM pji_fm_extr_plan_lines
2952 WHERE ROWID IN
2953 ( SELECT extr_lines_rowid
2954 FROM pji_fp_rmap_fpr_update_t)
2955 FOR UPDATE NOWAIT;
2956
2957 BEGIN
2958
2959 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
2960 ( p_package_name => g_package_name
2961 , x_return_status => x_return_status );
2962
2963 x_msg_code := Fnd_Api.G_RET_STS_SUCCESS;
2964
2965 print_time ( ' PLAN_EXTR_LINES_LOCK begin ' );
2966
2967
2968 OPEN c_lines;
2969 CLOSE c_lines;
2970
2971 print_time ( ' PLAN_EXTR_LINES_LOCK end ' );
2972
2973 EXCEPTION
2974 WHEN excp_resource_busy THEN
2975 x_return_status := FND_API.G_RET_STS_ERROR;
2976 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name ,
2977 p_procedure_name => 'PJI_EXTR_LINES_LOCK' );
2978 x_msg_code := FND_MESSAGE.GET_STRING( APPIN => 'PJI'
2979 , NAMEIN => 'PJI_LOCK_NOT_OBTAINED');
2980 -- PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PJI'
2981 -- , p_msg_name => 'PJI_LOCK_NOT_OBTAINED');
2982
2983 FND_MESSAGE.SET_NAME('PJI', 'PJI_LOCK_NOT_OBTAINED');
2984 Fnd_Msg_Pub.add_detail(p_message_type=>FND_API.G_RET_STS_ERROR);
2985
2986 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
2987 ( p_package_name => g_package_name
2988 , p_procedure_name => 'PJI_EXTR_LINES_LOCK'
2989 , x_return_status => x_return_status ) ;
2990
2991 RAISE;
2992
2993 WHEN NO_DATA_FOUND THEN
2994 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
2995 ( p_package_name => g_package_name
2996 , p_procedure_name => 'PJI_EXTR_LINES_LOCK'
2997 , x_return_status => x_return_status ) ;
2998
2999 RAISE;
3000
3001 WHEN OTHERS THEN
3002 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3003 ( p_package_name => g_package_name
3004 , p_procedure_name => 'PJI_EXTR_LINES_LOCK'
3005 , x_return_status => x_return_status ) ;
3006
3007 RAISE;
3008
3009 END;
3010
3011
3012 PROCEDURE GET_HDRS_TOLOCK_FOR_UPDATE (
3013 x_fp_version_ids OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
3014 , x_return_status OUT NOCOPY VARCHAR2
3015 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
3016
3017 l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3018
3019 CURSOR c_plan_version_ids_cur IS
3020 SELECT DISTINCT plan_version_id
3021 FROM PJI_FM_EXTR_PLAN_LINES
3022 WHERE ROWID IN
3023 ( SELECT extr_lines_rowid
3024 FROM pji_fp_rmap_fpr_update_t);
3025
3026 BEGIN
3027
3028 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
3029 ( p_package_name => g_package_name
3030 , x_return_status => x_return_status );
3031
3032 FOR i IN c_plan_version_ids_cur LOOP
3033 l_fp_version_ids.EXTEND;
3034 l_fp_version_ids(l_fp_version_ids.COUNT) := i.plan_version_id;
3035 END LOOP;
3036
3037 -- IF c_plan_version_ids_cur%ISOPEN THEN c_plan_version_ids_cur.CLOSE; END IF;
3038
3039 x_fp_version_ids := l_fp_version_ids ;
3040
3041 EXCEPTION
3042 WHEN OTHERS THEN
3043 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3044 ( p_package_name => g_package_name
3045 , p_procedure_name => 'GET_HDRS_TOLOCK_FOR_UPDATE '
3046 , x_return_status => x_return_status ) ;
3047
3048 RAISE;
3049 END;
3050
3051
3052 PROCEDURE GET_EVENT_IDS (
3053 p_fp_version_ids IN SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
3054 , p_operation_type IN VARCHAR2 := NULL
3055 , x_event_ids OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
3056 , x_return_status OUT NOCOPY VARCHAR2
3057 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
3058
3059 l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3060 i NUMBER := NULL;
3061 j NUMBER := NULL;
3062
3063 CURSOR c_event_id_cur (
3064 p_event_object NUMBER
3065 , p_operation_type VARCHAR2
3066 ) IS
3067 SELECT event_id
3068 FROM pa_pji_proj_events_log
3069 WHERE event_object = p_event_object
3070 AND operation_type = p_operation_type;
3071
3072 BEGIN
3073
3074 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
3075 ( p_package_name => g_package_name
3076 , x_return_status => x_return_status );
3077
3078 IF (p_operation_type IS NULL) THEN RETURN; END IF;
3079
3080 FOR i IN p_fp_version_ids.FIRST..p_fp_version_ids.LAST LOOP
3081
3082 -- IF c_event_id_cur%ISOPEN THEN c_event_id_cur.CLOSE; END IF;
3083
3084 FOR j IN c_event_id_cur(p_fp_version_ids(i), p_operation_type ) LOOP
3085 l_event_ids.EXTEND;
3086 l_event_ids(l_event_ids.COUNT) := j.event_id;
3087 END LOOP;
3088
3089 END LOOP;
3090
3091 -- IF c_event_id_cur%ISOPEN THEN c_event_id_cur.CLOSE; END IF;
3092
3093 EXCEPTION
3094 WHEN OTHERS THEN
3095 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3096 ( p_package_name => g_package_name
3097 , p_procedure_name => 'GET_EVENT_IDS'
3098 , x_return_status => x_return_status ) ;
3099
3100 RAISE;
3101 END;
3102
3103
3104 PROCEDURE DELETE_EVENTS (
3105 p_event_ids IN SYSTEM.pa_num_tbl_type
3106 , x_return_status OUT NOCOPY VARCHAR2
3107 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
3108 BEGIN
3109
3110 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
3111 ( p_package_name => g_package_name
3112 , x_return_status => x_return_status );
3113
3114 print_time ( ' delete events 001 ' );
3115
3116 FORALL i IN p_event_ids.FIRST..p_event_ids.LAST
3117 DELETE FROM pa_pji_proj_events_log
3118 WHERE event_id = p_event_ids(i);
3119
3120 print_time ( ' delete events 002 ' );
3121
3122 EXCEPTION
3123 WHEN OTHERS THEN
3124 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3125 ( p_package_name => g_package_name
3126 , p_procedure_name => 'DELETE_EVENTS'
3127 , x_return_status => x_return_status ) ;
3128
3129 RAISE;
3130 END;
3131
3132
3133 PROCEDURE COPY_PJI_SUMMRZD_FLAG (
3134 p_source_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3135 , p_dest_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type() ) IS
3136
3137 l_pji_summarized_flag VARCHAR2(1) := NULL;
3138 l_return_status VARCHAR2(1);
3139
3140 BEGIN
3141
3142 IF (p_source_fp_version_ids.COUNT <> p_dest_fp_version_ids.COUNT) THEN
3143 RETURN;
3144 END IF;
3145
3146 FOR i IN p_source_fp_version_ids.FIRST..p_source_fp_version_ids.LAST LOOP
3147
3148 BEGIN
3149 --Introduced below if condition for bug 7187487
3150 if p_source_fp_version_ids(i) <> p_dest_fp_version_ids(i) then
3151 SELECT pji_summarized_flag
3152 INTO l_pji_summarized_flag
3153 FROM pa_budget_versions
3154 WHERE budget_version_id = p_source_fp_version_ids(i);
3155
3156 UPDATE pa_budget_versions
3157 SET pji_summarized_flag = l_pji_summarized_flag
3158 WHERE budget_version_id = p_dest_fp_version_ids(i);
3159 elsif p_source_fp_version_ids(i) = p_dest_fp_version_ids(i) then
3160 UPDATE pa_budget_versions
3161 SET pji_summarized_flag = 'Y'
3162 WHERE budget_version_id = p_dest_fp_version_ids(i);
3163 end if;
3164
3165
3166 EXCEPTION
3167 WHEN OTHERS THEN
3168 NULL;
3169 END;
3170
3171 END LOOP;
3172
3173 EXCEPTION
3174 WHEN OTHERS THEN
3175 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3176 ( p_package_name => g_package_name
3177 , p_procedure_name => 'COPY_PJI_SUMMARIZED_FLAG'
3178 , x_return_status => l_return_status ) ;
3179
3180 RAISE;
3181 END;
3182
3183
3184 PROCEDURE MARK_PLAN_ORIGINAL (
3185 p_original_version_id IN NUMBER,
3186 x_return_status OUT NOCOPY VARCHAR2,
3187 x_msg_code OUT NOCOPY VARCHAR2 ) IS
3188 BEGIN
3189
3190 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
3191 ( p_package_name => g_package_name
3192 , x_return_status => x_return_status );
3193
3194 -- Todo: verifiy logic with VR.
3195 UPDATE pa_budget_versions
3196 SET original_flag = 'Y'
3197 , last_update_date = SYSDATE
3198 , last_updated_by = Fnd_Global.USER_ID
3199 , last_update_login = Fnd_Global.LOGIN_ID
3200 WHERE budget_version_id = p_original_version_id ;
3201
3202 EXCEPTION
3203 WHEN OTHERS THEN
3204 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3205 ( p_package_name => g_package_name
3206 , p_procedure_name => 'MARK_PLAN_ORIGINAL'
3207 , x_return_status => x_return_status ) ;
3208
3209 RAISE;
3210 END;
3211
3212
3213 PROCEDURE COPY_INTO_BASELINE_ORIGINAL(
3214 p_project_id IN NUMBER
3215 , p_plan_type_id IN NUMBER
3216 , p_plan_version_id IN NUMBER
3217 , x_processing_code OUT NOCOPY VARCHAR2
3218 ) IS
3219 l_last_update_date DATE := SYSDATE;
3220 l_last_updated_by NUMBER := Fnd_Global.USER_ID;
3221 l_creation_date DATE := SYSDATE;
3222 l_created_by NUMBER := Fnd_Global.USER_ID;
3223 l_last_update_login NUMBER := Fnd_Global.LOGIN_ID;
3224 l_return_status VARCHAR2(1);
3225 BEGIN
3226
3227 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
3228 ( p_package_name => g_package_name
3229 , x_return_status => x_processing_code );
3230
3231 INSERT INTO pji_fp_xbs_accum_f
3232 (
3233 PROJECT_ID
3234 , PROJECT_ORG_ID
3235 , PROJECT_ORGANIZATION_ID
3236 , PROJECT_ELEMENT_ID
3237 , TIME_ID
3238 , PERIOD_TYPE_ID
3239 , CALENDAR_TYPE
3240 , RBS_AGGR_LEVEL
3241 , WBS_ROLLUP_FLAG
3242 , PRG_ROLLUP_FLAG
3243 , CURR_RECORD_TYPE_ID
3244 , CURRENCY_CODE
3245 , RBS_ELEMENT_ID
3246 , RBS_VERSION_ID
3247 , PLAN_VERSION_ID
3248 , PLAN_TYPE_ID
3249 , LAST_UPDATE_DATE
3250 , LAST_UPDATED_BY
3251 , CREATION_DATE
3252 , CREATED_BY
3253 , LAST_UPDATE_LOGIN
3254 , RAW_COST
3255 , BRDN_COST
3256 , REVENUE
3257 , BILL_RAW_COST
3258 , BILL_BRDN_COST
3259 , BILL_LABOR_RAW_COST
3260 , BILL_LABOR_BRDN_COST
3261 , BILL_LABOR_HRS
3262 , EQUIPMENT_RAW_COST
3263 , EQUIPMENT_BRDN_COST
3264 , CAPITALIZABLE_RAW_COST
3265 , CAPITALIZABLE_BRDN_COST
3266 , LABOR_RAW_COST
3267 , LABOR_BRDN_COST
3268 , LABOR_HRS
3269 , LABOR_REVENUE
3270 , EQUIPMENT_HOURS
3271 , BILLABLE_EQUIPMENT_HOURS
3272 , SUP_INV_COMMITTED_COST
3273 , PO_COMMITTED_COST
3274 , PR_COMMITTED_COST
3275 , OTH_COMMITTED_COST
3276 , ACT_LABOR_HRS
3277 , ACT_EQUIP_HRS
3278 , ACT_LABOR_BRDN_COST
3279 , ACT_EQUIP_BRDN_COST
3280 , ACT_BRDN_COST
3281 , ACT_RAW_COST
3282 , ACT_REVENUE
3283 , ACT_LABOR_RAW_COST
3284 , ACT_EQUIP_RAW_COST
3285 , ETC_LABOR_HRS
3286 , ETC_EQUIP_HRS
3287 , ETC_LABOR_BRDN_COST
3288 , ETC_EQUIP_BRDN_COST
3289 , ETC_BRDN_COST
3290 , ETC_RAW_COST
3291 , ETC_LABOR_RAW_COST
3292 , ETC_EQUIP_RAW_COST
3293 , CUSTOM1
3294 , CUSTOM2
3295 , CUSTOM3
3296 , CUSTOM4
3297 , CUSTOM5
3298 , CUSTOM6
3299 , CUSTOM7
3300 , CUSTOM8
3301 , CUSTOM9
3302 , CUSTOM10
3303 , CUSTOM11
3304 , CUSTOM12
3305 , CUSTOM13
3306 , CUSTOM14
3307 , CUSTOM15
3308 , PLAN_TYPE_CODE /*4471527 */
3309 )
3310 (
3311 SELECT
3312 rl.PROJECT_ID
3313 , rl.PROJECT_ORG_ID
3314 , rl.PROJECT_ORGANIZATION_ID
3315 , rl.PROJECT_ELEMENT_ID
3316 , rl.TIME_ID
3317 , rl.PERIOD_TYPE_ID
3318 , rl.CALENDAR_TYPE
3319 , rl.RBS_AGGR_LEVEL
3320 , rl.WBS_ROLLUP_FLAG
3321 , rl.PRG_ROLLUP_FLAG
3322 , rl.CURR_RECORD_TYPE_ID
3323 , rl.CURRENCY_CODE
3324 , rl.RBS_ELEMENT_ID
3325 , rl.RBS_VERSION_ID
3326 , p_plan_version_id
3327 , rl.PLAN_TYPE_ID
3328 , l_last_update_date
3329 , l_last_updated_by
3330 , l_creation_date
3331 , l_created_by
3332 , l_last_update_login
3333 , rl.RAW_COST
3334 , rl.BRDN_COST
3335 , rl.REVENUE
3336 , rl.BILL_RAW_COST
3337 , rl.BILL_BRDN_COST
3338 , rl.BILL_LABOR_RAW_COST
3339 , rl.BILL_LABOR_BRDN_COST
3340 , rl.BILL_LABOR_HRS
3341 , rl.EQUIPMENT_RAW_COST
3342 , rl.EQUIPMENT_BRDN_COST
3343 , rl.CAPITALIZABLE_RAW_COST
3344 , rl.CAPITALIZABLE_BRDN_COST
3345 , rl.LABOR_RAW_COST
3346 , rl.LABOR_BRDN_COST
3347 , rl.LABOR_HRS
3348 , rl.LABOR_REVENUE
3349 , rl.EQUIPMENT_HOURS
3350 , rl.BILLABLE_EQUIPMENT_HOURS
3351 , rl.SUP_INV_COMMITTED_COST
3352 , rl.PO_COMMITTED_COST
3353 , rl.PR_COMMITTED_COST
3354 , rl.OTH_COMMITTED_COST
3355 , rl.ACT_LABOR_HRS
3356 , rl.ACT_EQUIP_HRS
3357 , rl.ACT_LABOR_BRDN_COST
3358 , rl.ACT_EQUIP_BRDN_COST
3359 , rl.ACT_BRDN_COST
3360 , rl.ACT_RAW_COST
3361 , rl.ACT_REVENUE
3362 , rl.ACT_LABOR_RAW_COST
3363 , rl.ACT_EQUIP_RAW_COST
3364 , rl.ETC_LABOR_HRS
3365 , rl.ETC_EQUIP_HRS
3366 , rl.ETC_LABOR_BRDN_COST
3367 , rl.ETC_EQUIP_BRDN_COST
3368 , rl.ETC_BRDN_COST
3369 , rl.ETC_RAW_COST
3370 , rl.ETC_LABOR_RAW_COST
3371 , rl.ETC_EQUIP_RAW_COST
3372 , rl.CUSTOM1
3373 , rl.CUSTOM2
3374 , rl.CUSTOM3
3375 , rl.CUSTOM4
3376 , rl.CUSTOM5
3377 , rl.CUSTOM6
3378 , rl.CUSTOM7
3379 , rl.CUSTOM8
3380 , rl.CUSTOM9
3381 , rl.CUSTOM10
3382 , rl.CUSTOM11
3383 , rl.CUSTOM12
3384 , rl.CUSTOM13
3385 , rl.CUSTOM14
3386 , rl.CUSTOM15
3387 , rl.PLAN_TYPE_CODE /*4471527 */
3388 FROM
3389 pji_fp_xbs_accum_f rl
3390 , pji_pjp_wbs_header wh
3391 WHERE rl.project_id = wh.project_id
3392 AND rl.project_id = p_project_id
3393 AND rl.plan_type_id = p_plan_type_id
3394 AND wh.plan_version_id = rl.plan_version_id
3395 AND wh.plan_type_code = rl.plan_type_code /*4471527 */
3396 AND DECODE(p_plan_version_id,
3397 -3, wh.cb_flag
3398 -4, wh.co_flag) = 'Y'
3399 );
3400
3401 EXCEPTION
3402 WHEN OTHERS THEN
3403 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3404 ( p_package_name => g_package_name
3405 , p_procedure_name => 'COPY_INTO_BASELINE_ORIGINAL'
3406 , x_return_status => x_processing_code ) ;
3407
3408 RAISE;
3409 END;
3410
3411
3412 PROCEDURE WBS_LOCK_PVT (
3413 p_event_id IN NUMBER DEFAULT NULL,
3414 p_online_flag IN VARCHAR2,
3415 p_request_id IN NUMBER DEFAULT NULL,
3416 x_lock_mode OUT NOCOPY VARCHAR2,
3417 x_return_status OUT NOCOPY VARCHAR2 )
3418 IS
3419
3420 resource_busy EXCEPTION;
3421 PRAGMA EXCEPTION_INIT(resource_busy,-00054);
3422
3423 l_project_id NUMBER;
3424 l_struct_version_id NUMBER;
3425
3426 l_plan_version_id NUMBER;
3427 l_wp_flag VARCHAR2(1);
3428 l_latest_pub_flag VARCHAR2(1);
3429 l_baselined_flag VARCHAR2(1);
3430 l_published_flag VARCHAR2(1);
3431 l_vers_enabled_flag VARCHAR2(1);
3432
3433 -- Todo: Structure change, version disabled case program lock.
3434
3435 /* -- original code.
3436 CURSOR c_plan_versions_online ( l_event_id NUMBER) IS
3437 SELECT
3438 head.PLAN_VERSION_ID
3439 FROM
3440 pji_pjp_wbs_header head,
3441 PA_PJI_PROJ_EVENTS_LOG elog
3442 WHERE
3443 elog.EVENT_ID = l_event_id AND
3444 head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3445 head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3446 head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
3447 head.LOCK_FLAG IS NULL
3448 FOR UPDATE NOWAIT;
3449 */
3450
3451 -- Handles online locking of
3452 -- a. Structure Change : All WP/FP/cb/co (only working structure can change
3453 -- based on a very strict set of rules. Like program rollup amts shd not change
3454 -- tasks with planning assignments/actuals should not be deleted, etc).
3455 -- b. Publish: All FP.
3456 CURSOR c_wh_lock_online_str_chng_cur ( l_event_id NUMBER) IS
3457 SELECT
3458 head.PLAN_VERSION_ID
3459 FROM
3460 pji_pjp_wbs_header head,
3461 PA_PJI_PROJ_EVENTS_LOG elog
3462 WHERE
3463 elog.EVENT_ID = l_event_id AND
3464 head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3465 head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3466 head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
3467 -- elog.EVENT_TYPE = 'WBS_CHANGE' AND
3468 -- need to process both wbs change and publish.
3469 -- publish needs to change wbs version id for fin plans.
3470 head.LOCK_FLAG IS NULL
3471 FOR UPDATE NOWAIT;
3472
3473
3474 -- Handles online locking of
3475 -- a. Published ver and one working ver above for publish.
3476 cursor c_online_pub_wp_cur (l_event_id number) IS
3477 select /*+ use_nl(sup_wbs_hdr)
3478 index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
3479 sup_wbs_hdr.PLAN_VERSION_ID
3480 from
3481 PJI_PJP_WBS_HEADER sup_wbs_hdr
3482 where
3483 (sup_wbs_hdr.PROJECT_ID,
3484 sup_wbs_hdr.WBS_VERSION_ID) in
3485 (
3486 select /*+ ordered
3487 index(prg PJI_XBS_DENORM_N1) */
3488 prg.SUP_PROJECT_ID,
3489 prg.SUP_ID
3490 from
3491 PA_PJI_PROJ_EVENTS_LOG log,
3492 PJI_PJP_WBS_HEADER sub_wbs_hdr,
3493 PA_PROJ_ELEM_VER_STRUCTURE ppevs2,
3494 PJI_XBS_DENORM prg
3495 where
3496 prg.STRUCT_VERSION_ID is null and
3497 sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
3498 sup_wbs_hdr.WBS_VERSION_ID = prg.SUP_ID and
3499 nvl(prg.SUB_ROLLUP_ID,
3500 prg.SUP_EMT_ID) <> prg.SUP_EMT_ID and
3501 prg.STRUCT_TYPE = 'PRG' and
3502 nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LW', 'WF') and
3503 sub_wbs_hdr.WP_FLAG = 'Y' and
3504 ppevs2.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
3505 ppevs2.ELEMENT_VERSION_ID = sub_wbs_hdr.WBS_VERSION_ID and
3506 ppevs2.STATUS_CODE = 'STRUCTURE_PUBLISHED' and
3507 sub_wbs_hdr.LOCK_FLAG is not null and
3508 sub_wbs_hdr.PROJECT_ID = to_number(log.ATTRIBUTE1) and
3509 sub_wbs_hdr.WBS_VERSION_ID = to_number(log.ATTRIBUTE2) and
3510 sub_wbs_hdr.PLAN_VERSION_ID = to_number(log.ATTRIBUTE3) and
3511 log.EVENT_TYPE = 'WBS_PUBLISH' and
3512 log.EVENT_ID = l_event_id
3513 ) and
3514 sup_wbs_hdr.WP_FLAG = 'Y' and
3515 sup_wbs_hdr.LOCK_FLAG is not null and
3516 exists
3517 (
3518 select
3519 1
3520 from
3521 PA_PROJ_ELEM_VER_STRUCTURE ppevs1
3522 where
3523 ppevs1.PROJECT_ID = sup_wbs_hdr.PROJECT_ID and
3524 ppevs1.ELEMENT_VERSION_ID = sup_wbs_hdr.WBS_VERSION_ID and
3525 ppevs1.STATUS_CODE = 'STRUCTURE_WORKING'
3526 )
3527 for update nowait;
3528
3529 -- Handles online locking of
3530 -- a. All cb/co for publish.
3531 cursor c_online_pub_cbco_lock_cur (l_event_id number) is
3532 select /*+ use_nl(sup_wbs_hdr) index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
3533 sup_wbs_hdr.PROJECT_ID,
3534 sup_wbs_hdr.PLAN_VERSION_ID,
3535 sup_wbs_hdr.PLAN_TYPE_ID
3536 from
3537 PJI_PJP_WBS_HEADER sup_wbs_hdr
3538 where
3539 (sup_wbs_hdr.PROJECT_ID,
3540 sup_wbs_hdr.WBS_VERSION_ID,
3541 sup_wbs_hdr.PLAN_VERSION_ID,
3542 sup_wbs_hdr.PLAN_TYPE_ID,
3543 sup_wbs_hdr.PLAN_TYPE_CODE) in
3544 (
3545 select /*+ ordered
3546 index(prg PJI_XBS_DENORM_N1) */
3547 prg.SUP_PROJECT_ID,
3548 prg.SUP_ID,
3549 sub_wbs_hdr.PLAN_VERSION_ID,
3550 sub_wbs_hdr.PLAN_TYPE_ID,
3551 sub_wbs_hdr.PLAN_TYPE_CODE
3552 from
3553 PA_PJI_PROJ_EVENTS_LOG log,
3554 PJI_PJP_WBS_HEADER wbs_hdr,
3555 PJI_PJP_WBS_HEADER sub_wbs_hdr,
3556 PJI_XBS_DENORM prg
3557 where
3558 prg.STRUCT_VERSION_ID is null and
3559 sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
3560 prg.STRUCT_TYPE = 'PRG' and
3561 nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LF', 'WF') and
3562 sub_wbs_hdr.PLAN_VERSION_ID in (-3, -4) and
3563 wbs_hdr.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
3564 wbs_hdr.PLAN_TYPE_ID = sub_wbs_hdr.PLAN_TYPE_ID and
3565 wbs_hdr.PLAN_TYPE_CODE = sub_wbs_hdr.PLAN_TYPE_CODE and
3566 wbs_hdr.LOCK_FLAG is not null and
3567 wbs_hdr.PROJECT_ID = to_number(log.ATTRIBUTE1) and
3568 wbs_hdr.WBS_VERSION_ID = to_number(log.ATTRIBUTE2) and
3569 wbs_hdr.PLAN_VERSION_ID in (-3, -4) and
3570 log.EVENT_TYPE = 'WBS_PUBLISH' and
3571 log.EVENT_ID = l_event_id
3572 )
3573 for update nowait;
3574
3575 -- original code.
3576 CURSOR c_plan_versions_deferred IS
3577 SELECT
3578 head.PLAN_VERSION_ID
3579 FROM
3580 pji_pjp_wbs_header head,
3581 PA_PJI_PROJ_EVENTS_LOG elog
3582 WHERE
3583 head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3584 head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3585 head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
3586 elog.EVENT_TYPE IN ('WBS_CHANGE', 'WBS_PUBLISH') AND
3587 head.LOCK_FLAG IS NULL
3588 FOR UPDATE;
3589
3590
3591 /*
3592 CURSOR c_wh_lock_sumz_str_chng_cur IS
3593 SELECT
3594 head.PLAN_VERSION_ID
3595 FROM
3596 pji_pjp_wbs_header head,
3597 PA_PJI_PROJ_EVENTS_LOG elog
3598 WHERE
3599 head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3600 head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3601 head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
3602 elog.EVENT_TYPE = 'WBS_CHANGE' AND
3603 head.LOCK_FLAG IS NULL
3604 FOR UPDATE;
3605
3606 CURSOR c_wh_lock_sumz_pub_cur IS
3607 SELECT hd1.plan_version_id
3608 FROM pji_pjp_wbs_header hd1 -- SUP
3609 WHERE EXISTS (
3610 SELECT 1
3611 FROM pji_xbs_denorm den
3612 , pji_pjp_wbs_header hd2 -- SUB
3613 , PA_PROJ_WORKPLAN_ATTR ppwa1
3614 , PA_PROJ_ELEMENTS ppe1
3615 , PA_PROJ_STRUCTURE_TYPES ppst1
3616 , PA_STRUCTURE_TYPES pst1
3617 , PA_PROJ_ELEM_VER_STRUCTURE ppevs1
3618 , PA_PROJ_WORKPLAN_ATTR ppwa2
3619 , PA_PROJ_ELEMENTS ppe2
3620 , PA_PROJ_STRUCTURE_TYPES ppst2
3621 , PA_STRUCTURE_TYPES pst2
3622 , PA_PROJ_ELEM_VER_STRUCTURE ppevs2
3623 , PA_PJI_PROJ_EVENTS_LOG elog
3624 WHERE
3625 den.struct_version_id IS NULL
3626 AND hd2.wbs_version_id = den.sub_id -- struct_version_id
3627 AND hd1.wbs_version_id = den.sup_id
3628 AND NVL(den.sub_rollup_id, den.sup_emt_id) <> den.sup_emt_id
3629 AND den.struct_type = 'PRG'
3630 AND NVL(den.relationship_type, 'WF') IN ('LW', 'WF') -- 'LW',
3631 AND hd1.wp_flag = 'Y'
3632 AND ppe1.project_id = ppwa1.project_id
3633 AND ppe1.proj_element_id = ppwa1.proj_element_id
3634 AND ppe1.proj_element_id = ppst1.proj_element_id
3635 AND ppst1.structure_type_id = pst1.structure_type_id
3636 AND pst1.structure_type_class_code = 'WORKPLAN'
3637 AND ppevs1.project_id = ppe1.project_id
3638 AND ppevs1.project_id = hd1.project_id
3639 AND ppevs1.element_version_id = hd1.wbs_version_id
3640 AND ppevs1.status_code = 'STRUCTURE_WORKING'
3641 AND hd1.lock_flag IS NOT NULL
3642 AND hd2.wp_flag = 'Y'
3643 AND ppe2.project_id = ppwa2.project_id
3644 AND ppe2.proj_element_id = ppwa2.proj_element_id
3645 AND ppe2.proj_element_id = ppst2.proj_element_id
3646 AND ppst2.structure_type_id = pst2.structure_type_id
3647 AND pst2.structure_type_class_code = 'WORKPLAN'
3648 AND ppevs2.project_id = ppe2.project_id
3649 AND ppevs2.project_id = hd2.project_id
3650 AND ppevs2.element_version_id = hd2.wbs_version_id
3651 -- AND ppwa2.wp_enable_version_flag = 'N' -- Todo: to consider version disabled case.
3652 AND ppevs2.status_code = 'STRUCTURE_PUBLISHED'
3653 AND hd2.lock_flag IS NOT NULL
3654 AND hd2.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1)
3655 AND hd2.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2)
3656 AND hd2.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3)
3657 AND elog.EVENT_TYPE = 'WBS_PUBLISH'
3658 )
3659 FOR UPDATE;
3660 */
3661
3662
3663 BEGIN
3664
3665 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
3666 ( p_package_name => g_package_name
3667 , x_return_status => x_return_status );
3668
3669
3670 IF p_online_flag = 'Y' THEN
3671 --Try to acquire lock for affected plan versions
3672 BEGIN
3673
3674 -- Structure change + publish.
3675 -- Affected plan vers: All plan vers with this structure on this project only.
3676 OPEN c_wh_lock_online_str_chng_cur (p_event_id);
3677 print_time(' str chg + publish l_num_locked = ' || SQL%ROWCOUNT );
3678 CLOSE c_wh_lock_online_str_chng_cur;
3679
3680 -- Publish.
3681 -- Affected plan vers: The new published ver and all workplans one level above.
3682 OPEN c_online_pub_wp_cur (p_event_id);
3683 print_time(' publish wps lock only l_num_locked = ' || SQL%ROWCOUNT );
3684 CLOSE c_online_pub_wp_cur;
3685
3686 -- Publish.
3687 -- Affected plan vers: All cb/co fin plans in the structure.
3688 OPEN c_online_pub_cbco_lock_cur (p_event_id);
3689 print_time(' publish cb cos lock only l_num_locked = ' || SQL%ROWCOUNT );
3690 CLOSE c_online_pub_cbco_lock_cur;
3691
3692 x_lock_mode := 'S';
3693
3694 RETURN;
3695
3696 EXCEPTION
3697 WHEN resource_busy THEN
3698 --IF launched from conc. request
3699 --end conc. request with warning status
3700 SELECT
3701 TO_NUMBER(elog.ATTRIBUTE1),
3702 TO_NUMBER(elog.EVENT_OBJECT)
3703 INTO
3704 l_project_id,
3705 l_struct_version_id
3706 FROM
3707 pa_pji_proj_events_log elog
3708 WHERE
3709 elog.EVENT_ID = p_event_id AND
3710 ROWNUM <= 1;
3711
3712 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER /* 5138049 */
3713 ( p_package_name => g_package_name
3714 , p_procedure_name => 'WBS_LOCK_PVT'
3715 , x_return_status => x_return_status ) ;
3716 x_lock_mode := 'F';
3717 RETURN;
3718 END; --end of resource_busy exception block
3719
3720 ELSE --for deferred case
3721
3722 OPEN c_plan_versions_deferred;
3723 CLOSE c_plan_versions_deferred;
3724
3725 --Need to update PJI_PJP_WBS_HEADER to set LOCK flag = Y
3726 ------------------------------------
3727 --Below locking is not required
3728 -----------------------------------
3729 /* UPDATE pji_pjp_wbs_header head
3730 SET LOCK_FLAG = 'P'
3731 WHERE
3732 head.PLAN_VERSION_ID IN (SELECT
3733 TO_NUMBER(elog.ATTRIBUTE3)
3734 FROM
3735 pa_pji_proj_events_log elog
3736 WHERE
3737 head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3738 head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3739 head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
3740 elog.EVENT_TYPE IN ('WBS_CHANGE', 'WBS_PUBLISH'));*/
3741 x_lock_mode := 'S';
3742 RETURN;
3743
3744 END IF; --end deferred case
3745
3746 EXCEPTION
3747 WHEN OTHERS THEN
3748 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
3749 ( p_package_name => g_package_name
3750 , p_procedure_name => 'WBS_LOCK_PVT'
3751 , x_return_status => x_return_status ) ;
3752
3753 RAISE;
3754 END; --end WBS_LOCK_PVT
3755
3756 ------------------------------------------------------------------
3757 --TODO: Need to add processing for publish event. All financial plans
3758 --have to rollup based on latest published version
3759 --currently we only update the wbs header table
3760 -------------------------------------------------------------------
3761 PROCEDURE WBS_MAINT_PVT (
3762 p_event_id IN NUMBER,
3763 p_versioned_flag IN VARCHAR2,
3764 p_struct_type IN VARCHAR2,
3765 p_publish_flag IN VARCHAR2,
3766 p_calling_context IN VARCHAR2,
3767 p_deffered_mode IN VARCHAR2,
3768 p_rerun_flag IN VARCHAR2 :=NULL,
3769 x_return_status OUT NOCOPY VARCHAR2 )
3770 IS
3771 l_project_id NUMBER;
3772 l_return_status VARCHAR2(1);
3773 l_msg_count NUMBER;
3774 l_msg_data VARCHAR2(2000);
3775 l_old_wbs_version_id NUMBER;
3776 l_new_wbs_version_id NUMBER;
3777 l_prg_event_id NUMBER;
3778 l_request_id NUMBER;
3779 l_chd_phase VARCHAR2(400);
3780 l_chd_status VARCHAR2(400);
3781 l_chd_dev_phase VARCHAR2(400);
3782 l_chd_dev_status VARCHAR2(400);
3783 l_chd_message VARCHAR2(2000);
3784 l_plan_version_id NUMBER;
3785
3786 l_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3787 l_struct_sharing_code pa_projects_all.structure_sharing_code%TYPE;
3788 CURSOR c_pln_ver_list (p_old_struct_ver_id IN NUMBER) IS
3789 SELECT project_id, plan_version_id
3790 FROM pji_pjp_wbs_header
3791 WHERE 1 = 1
3792 AND wbs_version_id = p_old_struct_ver_id
3793 AND plan_version_id > 0;
3794
3795 BEGIN
3796
3797 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
3798 ( p_package_name => g_package_name
3799 , x_return_status => x_return_status );
3800 IF g_debug_mode='Y' THEN
3801 Pji_Utils.write2log('calling WBS_MAINT_PVT :p_versioned_flag: '||p_versioned_flag||'p_struct_type:'||p_struct_type||'p_publish_flag:'||p_publish_flag||'p_rerun_flag :' || p_rerun_flag ,null,3);
3802 Pji_Utils.write2log('calling WBS_MAINT_PVT :p_calling_Context: '||p_calling_Context||'p_deffered_mode:'||p_deffered_mode||'Event Id:' || p_event_id,null,3);
3803 end if;
3804
3805 cleanup_temp_tables;
3806
3807
3808 SELECT
3809 TO_NUMBER(elog.ATTRIBUTE1),
3810 TO_NUMBER(elog.EVENT_OBJECT),
3811 TO_NUMBER(elog.ATTRIBUTE2)
3812 INTO
3813 l_project_id,
3814 l_new_wbs_version_id,
3815 l_old_wbs_version_id
3816 FROM
3817 pa_pji_proj_events_log elog
3818 WHERE
3819 elog.EVENT_ID = p_event_id AND
3820 ROWNUM <= 1;
3821 IF g_debug_mode='Y' THEN
3822 Pji_Utils.write2log('WBS_Maint_Pvt: l_new_wbs_version_id: '||l_new_wbs_version_id||'l_old_wbs_version_id: '||l_old_wbs_version_id,null,3);
3823 end if;
3824 -----------------------------------------------
3825 --Determine the plan_version_id for the struct
3826 --for which we are running wbs_maint
3827 -----------------------------------------------
3828 begin
3829 select
3830 PLAN_VERSION_ID
3831 into
3832 l_plan_version_id
3833 from
3834 pji_pjp_wbs_header
3835 where
3836 PROJECT_ID = l_project_id AND
3837 WBS_VERSION_ID = l_new_wbs_version_id AND
3838 WP_FLAG = 'Y';
3839 exception
3840 when no_data_found then
3841 null;
3842 IF g_debug_mode='Y' THEN
3843 Pji_Utils.write2log('WBS_Maint_Pvt: No data found for plan version');
3844 END IF;
3845 end;
3846
3847 IF g_debug_mode='Y' THEN
3848 Pji_Utils.write2log('WBS_Maint_Pvt:' || 'Plan version Id:' || l_plan_version_id);
3849 END IF;
3850
3851
3852 ----------------------------------------------
3853 --Identify if any pending program links exist
3854 --for the program group of this project
3855 ----------------------------------------------
3856 BEGIN
3857 IF p_rerun_flag='Y' THEN
3858 select 1
3859 into l_prg_event_id
3860 from dual
3861 where exists (select log.event_id
3862 from pji_pa_proj_events_log log,pa_proj_element_versions ver
3863 where log.event_type='PRG_CHANGE'
3864 and log.event_object =to_char(ver.prg_group)
3865 and ver.project_id=l_project_id
3866 union all
3867 select log.event_id
3868 from pa_pji_proj_events_log log,pa_proj_element_versions ver
3869 where log.event_type='PRG_CHANGE'
3870 and log.event_object =to_char(ver.prg_group)
3871 and ver.project_id=l_project_id);
3872 else
3873 select 1
3874 into l_prg_event_id
3875 from dual
3876 where exists (select log.event_id
3877 from pa_pji_proj_events_log log,pa_proj_element_versions ver
3878 where log.event_type='PRG_CHANGE'
3879 and log.event_object =to_char(ver.prg_group)
3880 and ver.project_id=l_project_id);
3881
3882 end if;
3883 EXCEPTION
3884 WHEN NO_DATA_FOUND THEN
3885 IF g_debug_mode='Y' THEN
3886 Pji_Utils.write2log( 'WBS_MAINT_PVT NO DATA FOUND coming for the project _Id:'||l_project_id,null,3);
3887 END IF;
3888 l_prg_event_id:=null;
3889 END;
3890
3891 l_struct_sharing_code := Pa_Project_Structure_Utils.get_structure_sharing_code (l_project_id);
3892 IF l_old_wbs_version_id IS NULL THEN
3893 -- Fix for bug : 4191390
3894 If l_struct_sharing_code LIKE '%SHARE%' then
3895 UPDATE pji_pjp_wbs_header SET
3896 wbs_version_id = l_new_wbs_version_id
3897 WHERE
3898 wp_flag = 'N' AND
3899 project_id = l_project_id;
3900
3901 elsif l_struct_sharing_code LIKE '%SPLIT%' THEN
3902
3903 UPDATE pji_pjp_wbs_header SET
3904 wbs_version_id = l_new_wbs_version_id
3905 WHERE
3906 wp_flag = 'N' AND
3907 p_struct_type = 'FINANCIAL' AND
3908 project_id = l_project_id;
3909 end if ;
3910
3911 IF g_debug_mode='Y' THEN
3912 Pji_Utils.write2log( 'WBS_MAINT_PVT l_prg_event_id :'||l_prg_event_id||'l_struct_sharing_code'||l_struct_sharing_code ,null,3);
3913 END IF;
3914 IF l_prg_event_id is not null then
3915
3916 --Bug 4626803: Need this for preventing doubling
3917 --of actuals
3918 UPDATE pji_fm_extr_plan_lines
3919 SET ACT_QUANTITY = NULL,
3920 ACT_TXN_BURDENED_COST = NULL,
3921 ACT_PRJ_BURDENED_COST = NULL,
3922 ACT_PFC_BURDENED_COST = NULL,
3923 ACT_TXN_RAW_COST = NULL,
3924 ACT_PRJ_RAW_COST = NULL,
3925 ACT_PFC_RAW_COST = NULL,
3926 ACT_TXN_REVENUE = NULL,
3927 ACT_PRJ_REVENUE = NULL,
3928 ACT_PFC_REVENUE = NULL
3929 WHERE
3930 plan_version_id = l_plan_version_id;
3931
3932 --Callingt plan_update to process data in PJI_FM_EXTR_PLAN_LINES
3933 pa_task_pub1.G_CALL_PJI_ROLLUP := 'Y';
3934 pji_fm_xbs_accum_maint.plan_update (
3935 p_plan_version_id => l_plan_version_id,
3936 x_msg_code => l_msg_data,
3937 x_return_status => l_return_status );
3938
3939 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
3940 x_return_status := l_return_status;
3941 IF g_debug_mode='Y' THEN
3942 Pji_Utils.write2log('WBS Maint Pvt:' || 'Failure in plan_update call');
3943 END IF;
3944 RETURN;
3945 END IF;
3946 pa_task_pub1.G_CALL_PJI_ROLLUP := 'N';
3947
3948 ----------------------------------------------------------------------------------------
3949 --The below COMMIT is as per design. It is required for releasing locks on denorm and
3950 --header table that will be accessed by the summarization program launched in step below
3951 COMMIT;
3952 savepoint process_wbs_updates_conc;
3953 savepoint process_wbs_updates;
3954 savepoint process_proj_sum_conc;
3955 ----------------------------------------------------------------------------------------
3956 -- Added If condition for Bug 5999999
3957
3958 IF NVL(FND_PROFILE.value('PA_ROLLUP_PROGRAM_AMOUNTS'),'AUTOMATIC') = 'AUTOMATIC' THEN
3959
3960 l_request_id:=submit_request(l_project_id);
3961 if (FND_CONCURRENT.WAIT_FOR_REQUEST (
3962 l_request_id,
3963 20,
3964 0, -- wait forever
3965 l_chd_phase,
3966 l_chd_status,
3967 l_chd_dev_phase,
3968 l_chd_dev_status,
3969 l_chd_message
3970 )) then
3971
3972 Pji_Utils.write2log( '*********22Submitted status the l_dev_phase: '||l_chd_dev_phase||'l_dev_status'||l_chd_dev_status ,null,3);
3973
3974 end if;
3975
3976 if l_chd_dev_status ='ERROR' THEN
3977 l_return_status:='E';
3978 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUMM_ERR');
3979 FND_MESSAGE.SET_TOKEN ('REQUEST_ID', l_request_id);
3980 FND_MESSAGE.SET_TOKEN ('SQLERRM', l_chd_message); --bug#5524224, passed completion text
3981
3982 fnd_msg_pub.add_exc_msg(p_pkg_name => g_package_name,
3983 p_procedure_name => 'WBS_MAINT_PVT',
3984 p_error_text => SUBSTRB(FND_MESSAGE.GET,1,240));
3985
3986 IF g_debug_mode='Y' THEN null;
3987 Pji_Utils.write2log(SUBSTRB(FND_MESSAGE.GET,1,240));
3988 end if;
3989 elsif l_chd_dev_status in ('CANCELLED','TERMINATED','DELETED') THEN
3990 l_return_status:='E';
3991 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUMM_ERR');
3992 FND_MESSAGE.SET_TOKEN ('REQUEST_ID', l_request_id);
3993 FND_MESSAGE.SET_TOKEN ('SQLERRM', l_chd_status);
3994 fnd_msg_pub.add_exc_msg(p_pkg_name => g_package_name,
3995 p_procedure_name => 'WBS_MAINT_PVT',
3996 p_error_text => SUBSTRB(FND_MESSAGE.GET,1,240));
3997 IF g_debug_mode='Y' THEN null;
3998 Pji_Utils.write2log(SUBSTRB(FND_MESSAGE.GET,1,240));
3999 end if;
4000
4001 end if;
4002 END IF; -- Bug 5999999
4003 END IF;
4004
4005 x_return_status:=l_return_status;
4006
4007 ------------------------------------------------
4008 --As per current implementation the first publish
4009 --is treated differently
4010 ------------------------------------------------
4011 RETURN;
4012 END IF;
4013
4014
4015 FOR i IN c_pln_ver_list(l_old_wbs_version_id) LOOP
4016
4017 l_plan_version_id_tbl.EXTEND;
4018 l_plan_version_id_tbl(l_plan_version_id_tbl.COUNT) := i.plan_version_id;
4019
4020 END LOOP;
4021 IF g_debug_mode='Y' THEN
4022 Pji_Utils.write2log( 'WBS_MAINT_PVT l_plan_version_id_tbl.COUNT :'||l_plan_version_id_tbl.COUNT ,null,3);
4023 END IF;
4024 /*bug5353559*/
4025 IF (l_plan_version_id_tbl.COUNT <= 0) THEN
4026 --RETURN;
4027 NULL;
4028 ELSE
4029
4030 Pji_Fm_Plan_Maint_T_Pvt.EXTRACT_FIN_PLAN_VERSIONS(
4031 p_fp_version_ids => l_plan_version_id_tbl
4032 , p_slice_type => 'PRI'
4033 );
4034
4035 END IF;
4036
4037 IF p_publish_flag = 'N' THEN
4038
4039 Pji_Pjp_Sum_Denorm.populate_xbs_denorm(
4040 p_worker_id => 1,
4041 p_denorm_type => 'WBS',
4042 p_wbs_version_id => l_old_wbs_version_id,
4043 p_prg_group1 => NULL,
4044 p_prg_group2 => NULL
4045 );
4046
4047 IF g_debug_mode='Y' THEN
4048 Pji_Utils.write2log('WBS Maint Pvt:' || 'New WBS Version ID:' || l_new_wbs_version_id);
4049 Pji_Utils.write2log('WBS Maint Pvt:' || 'Old WBS Version ID:' || l_old_wbs_version_id);
4050 end if;
4051
4052
4053 Pji_Pjp_Sum_Rollup.set_online_context (
4054 p_event_id => p_event_id,
4055 p_project_id => l_project_id,
4056 p_plan_type_id => NULL,
4057 p_old_baselined_version => NULL,
4058 p_new_baselined_version => NULL,
4059 p_old_original_version => NULL,
4060 p_new_original_version => NULL,
4061 p_old_struct_version => l_old_wbs_version_id,
4062 p_new_struct_version => l_new_wbs_version_id );
4063
4064 Pji_Pjp_Sum_Rollup.populate_xbs_denorm_delta;
4065
4066 Pji_Pjp_Sum_Rollup.rollup_fpr_wbs;
4067 IF g_debug_mode='Y' THEN
4068 Pji_Utils.write2log(' WBS_MAINT_PVT: Pji_Pjp_Sum_Rollup.rollup_fpr_wbs',null,3);
4069 end if;
4070 Pji_Pjp_Sum_Rollup.rollup_acr_wbs;
4071
4072 Pji_Pjp_Sum_Rollup.update_xbs_denorm;
4073
4074 Pji_Pjp_Sum_Denorm.cleanup_xbs_denorm(
4075 p_worker_id => 1
4076 ,p_extraction_type => 'ONLINE');
4077
4078 IF g_debug_mode='Y' THEN
4079 Pji_Utils.write2log(' WBS_MAINT_PVT: Pji_Fm_Plan_Maint_T_Pvt.MERGE_INTO_FP_FACT',null,3);
4080 end if;
4081 Pji_Fm_Plan_Maint_T_Pvt.MERGE_INTO_FP_FACT;
4082
4083
4084 Pji_Fm_Plan_Maint_T_Pvt.MERGE_INTO_AC_FACT;
4085
4086
4087
4088 END IF;
4089
4090 IF p_publish_flag = 'Y' THEN
4091 -- Fix for bug : 4191390
4092 If l_struct_sharing_code LIKE '%SHARE%' then
4093 UPDATE pji_pjp_wbs_header SET
4094 wbs_version_id = l_new_wbs_version_id
4095 WHERE
4096 wp_flag = 'N' AND
4097 project_id = l_project_id;
4098 elsif l_struct_sharing_code LIKE '%SPLIT%' THEN
4099 UPDATE pji_pjp_wbs_header SET
4100 wbs_version_id = l_new_wbs_version_id
4101 WHERE
4102 wp_flag = 'N' AND
4103 p_struct_type = 'FINANCIAL' AND
4104 project_id = l_project_id;
4105 end if ;
4106 END IF;
4107 /* Bug No.4567424
4108 Pa_Proj_Task_Struc_Pub.set_update_wbs_flag (
4109 p_project_id => l_project_id,
4110 p_structure_version_id => l_new_wbs_version_id,
4111 p_update_wbs_flag => 'N',
4112 x_return_status => l_return_status,
4113 x_msg_count => l_msg_count,
4114 x_msg_data => l_msg_data );
4115 */
4116 /* 5138049 as updated by MAANSARI
4117 Pa_Proj_Task_Struc_Pub.process_task_weightage (
4118 p_project_id => l_project_id,
4119 p_structure_version_id => l_new_wbs_version_id,
4120 x_return_status => l_return_status,
4121 x_msg_count => l_msg_count,
4122 x_msg_data => l_msg_data );
4123 */
4124 DELETE
4125 FROM pa_pji_proj_events_log LOG
4126 WHERE LOG.EVENT_ID = p_event_id;
4127
4128 cleanup_temp_tables;
4129 /* bug5353559*/
4130 /* Bug 5609109
4131 IF (l_plan_version_id_tbl.COUNT <= 0) THEN
4132 IF g_debug_mode='Y' THEN
4133 Pji_Utils.write2log(' WBS_MAINT_PVT: RETURNING as table count is Zero',null,3);
4134 end if;
4135 RETURN;
4136 end if; */
4137
4138 IF g_debug_mode='Y' THEN
4139 Pji_Utils.write2log( 'calling APPLY_LP_PROG_ON_CWV :p_calling_Context: '||p_calling_Context||'p_deffered_mode:'||p_deffered_mode||'l_new_wbs_version_id:'||l_new_wbs_version_id ,null,3);
4140 end if;
4141 IF p_calling_Context='APPLY_PROGRESS' and p_deffered_mode='Y' THEN
4142 PA_PROGRESS_PUB.APPLY_LP_PROG_ON_CWV(
4143 p_project_id => l_project_id,
4144 p_working_str_version_id => l_new_wbs_version_id,
4145 x_return_status => l_return_status,
4146 x_msg_count => l_msg_count ,
4147 x_msg_data => l_msg_data);
4148 IF g_debug_mode='Y' THEN
4149 Pji_Utils.write2log( 'after call to APPLY_LP_PROG_ON_CWV :l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data||'CONC_REQUEST_Id:'||Fnd_Global.CONC_REQUEST_ID,null,3);
4150 end if;
4151 END IF;
4152
4153
4154
4155 IF l_prg_event_id is not null then
4156
4157 --Bug 4626803: Need this for preventing doubling
4158 --of actuals
4159 UPDATE pji_fm_extr_plan_lines
4160 SET ACT_QUANTITY = NULL,
4161 ACT_TXN_BURDENED_COST = NULL,
4162 ACT_PRJ_BURDENED_COST = NULL,
4163 ACT_PFC_BURDENED_COST = NULL,
4164 ACT_TXN_RAW_COST = NULL,
4165 ACT_PRJ_RAW_COST = NULL,
4166 ACT_PFC_RAW_COST = NULL,
4167 ACT_TXN_REVENUE = NULL,
4168 ACT_PRJ_REVENUE = NULL,
4169 ACT_PFC_REVENUE = NULL
4170 WHERE
4171 plan_version_id = l_plan_version_id;
4172
4173 --Callingt plan_update to process data in PJI_FM_EXTR_PLAN_LINES
4174 pa_task_pub1.G_CALL_PJI_ROLLUP := 'Y';
4175 pji_fm_xbs_accum_maint.plan_update (
4176 p_plan_version_id => l_plan_version_id,
4177 x_msg_code => l_msg_data,
4178 x_return_status => l_return_status );
4179
4180 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
4181 x_return_status := l_return_status;
4182 IF g_debug_mode='Y' THEN
4183 Pji_Utils.write2log('WBS Maint Pvt:' || 'Failure in plan_update call');
4184 END IF;
4185 RETURN;
4186 END IF;
4187 pa_task_pub1.G_CALL_PJI_ROLLUP := 'N';
4188
4189
4190 ----------------------------------------------------------------------------------------
4191 --The below COMMIT is as per design. It is required for releasing locks on denorm and
4192 --header table that will be accessed by the summarization program launched in step below
4193 COMMIT;
4194 savepoint process_wbs_updates_conc;
4195 savepoint process_wbs_updates;
4196 savepoint process_proj_sum_conc;
4197 ----------------------------------------------------------------------------------------
4198 -- Added If condition for Bug 5999999
4199
4200 IF NVL(FND_PROFILE.value('PA_ROLLUP_PROGRAM_AMOUNTS'),'AUTOMATIC') = 'AUTOMATIC' THEN
4201
4202 l_request_id:=submit_request(l_project_id);
4203 if (FND_CONCURRENT.WAIT_FOR_REQUEST (
4204 l_request_id,
4205 20,
4206 0, -- wait forever
4207 l_chd_phase,
4208 l_chd_status,
4209 l_chd_dev_phase,
4210 l_chd_dev_status,
4211 l_chd_message
4212 )) then
4213
4214 Pji_Utils.write2log( '*********22Submitted status the l_dev_phase: '||l_chd_dev_phase||'l_dev_status'||l_chd_dev_status ,null,3);
4215
4216 end if;
4217 if l_chd_dev_status ='ERROR' THEN
4218 l_return_status:='E';
4219 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUMM_ERR');
4220 FND_MESSAGE.SET_TOKEN ('REQUEST_ID', l_request_id);
4221 FND_MESSAGE.SET_TOKEN ('SQLERRM', l_chd_message); --bug#5524224, passed completion text
4222
4223 fnd_msg_pub.add_exc_msg(p_pkg_name => g_package_name,
4224 p_procedure_name => 'WBS_MAINT_PVT',
4225 p_error_text => SUBSTRB(FND_MESSAGE.GET,1,240));
4226
4227 IF g_debug_mode='Y' THEN null;
4228 Pji_Utils.write2log(SUBSTRB(FND_MESSAGE.GET,1,240));
4229 end if;
4230 elsif l_chd_dev_status in ('CANCELLED','TERMINATED','DELETED') THEN
4231 l_return_status:='E';
4232 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUMM_ERR');
4233 FND_MESSAGE.SET_TOKEN ('REQUEST_ID', l_request_id);
4234 FND_MESSAGE.SET_TOKEN ('SQLERRM', l_chd_status);
4235 fnd_msg_pub.add_exc_msg(p_pkg_name => g_package_name,
4236 p_procedure_name => 'WBS_MAINT_PVT',
4237 p_error_text => SUBSTRB(FND_MESSAGE.GET,1,240));
4238 IF g_debug_mode='Y' THEN null;
4239 Pji_Utils.write2log(SUBSTRB(FND_MESSAGE.GET,1,240));
4240 end if;
4241
4242 end if;
4243 END IF ; --Bug 5999999
4244 END IF;
4245
4246
4247 x_return_status:=l_return_status;
4248 IF g_debug_mode='Y' THEN
4249 Pji_Utils.write2log( ' wbs maint pvt .. 0002 ::l_return_status'||l_return_status ,null,3);
4250 end if;
4251
4252 EXCEPTION
4253 WHEN OTHERS THEN
4254 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
4255 ( p_package_name => g_package_name
4256 , p_procedure_name => 'WBS_MAINT_PVT'
4257 , x_return_status => x_return_status ) ;
4258
4259 RAISE;
4260 END;
4261
4262 PROCEDURE LAUNCH_WBS_REQ_PVT (
4263 p_event_id IN NUMBER,
4264 p_calling_context IN VARCHAR2,
4265 p_rerun_flag IN VARCHAR2 :=NULL,
4266 x_return_status OUT NOCOPY VARCHAR2 )
4267 IS
4268 l_request_id NUMBER;
4269 l_project_id NUMBER;
4270 l_struct_version_id NUMBER;
4271 l_return_status VARCHAR2(1);
4272
4273 BEGIN
4274
4275 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
4276 ( p_package_name => g_package_name
4277 , x_return_status => x_return_status );
4278
4279 SELECT
4280 TO_NUMBER(elog.ATTRIBUTE1),
4281 TO_NUMBER(elog.EVENT_OBJECT)
4282 INTO
4283 l_project_id,
4284 l_struct_version_id
4285 FROM
4286 pa_pji_proj_events_log elog
4287 WHERE
4288 elog.EVENT_ID = p_event_id AND
4289 ROWNUM <= 1;
4290
4291 l_request_id:=
4292 Fnd_Request.SUBMIT_REQUEST (
4293 Pji_Utils.GET_PJI_SCHEMA_NAME, -- Application name
4294 'PJI_FM_SUM_CHANGE', -- concurrent program name
4295 NULL, -- description (optional)
4296 NULL, -- Start Time (optional)
4297 FALSE, -- called from another conc. request
4298 p_event_id, -- first parameter
4299 p_calling_context, -- second parameter
4300 p_rerun_flag );
4301
4302
4303 PA_PROJECT_STRUCTURE_UTILS.SET_PROCESS_CODE_IN_PROC(
4304 p_project_id => l_project_id,
4305 p_structure_version_id => l_struct_version_id,
4306 p_calling_context => p_calling_context,
4307 p_conc_request_id => l_request_id,
4308 x_return_status => l_return_status );
4309
4310 EXCEPTION
4311 WHEN OTHERS THEN
4312
4313 /*UPDATE pa_proj_elem_ver_structure
4314 SET PROCESS_CODE = 'WUE',
4315 CONC_REQUEST_ID = l_request_id
4316 WHERE ELEMENT_VERSION_ID = l_struct_version_id
4317 AND PROJECT_ID = l_project_id;
4318 */
4319 PA_PROJECT_STRUCTURE_UTILS.SET_PROCESS_CODE_ERR(
4320 p_project_id => l_project_id,
4321 p_structure_version_id => l_struct_version_id,
4322 p_calling_context => p_calling_context,
4323 p_conc_request_id => l_request_id,
4324 x_return_status => l_return_status );
4325
4326 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
4327 ( p_package_name => g_package_name
4328 , p_procedure_name => 'LAUNCH_WBS_REQ_PVT'
4329 , x_return_status => x_return_status ) ;
4330
4331 RAISE;
4332 END;
4333
4334 ---------------------WBS_MAINT------------------------
4335 --There are 4 different cases when this API gets called
4336 -- 1. Non-Versioned structure
4337 -- 2. Split versioned
4338 -- 3. Shared versioned and publish = N
4339 -- 4. Shared versioned and publish = Y
4340 --------------------------------------------------------
4341 PROCEDURE WBS_MAINT (
4342 p_new_struct_ver_id IN NUMBER,
4343 p_old_struct_ver_id IN NUMBER,
4344 p_project_id IN NUMBER,
4345 p_publish_flag IN VARCHAR2 DEFAULT 'N',
4346 p_online_flag IN VARCHAR2,
4347 p_calling_Context IN VARCHAR2 :=NULL,
4348 p_rerun_flag IN VARCHAR2 :=NULL,
4349 x_request_id OUT NOCOPY NUMBER,
4350 x_processing_code OUT NOCOPY VARCHAR2,
4351 x_msg_code OUT NOCOPY VARCHAR2,
4352 x_return_status OUT NOCOPY VARCHAR2,
4353 x_online_flag OUT NOCOPY VARCHAR2)
4354 IS
4355
4356 l_shared_flag VARCHAR2(1) := 'A'; --Default value implies value is null in source system
4357 l_versioned_flag VARCHAR2(1):='N';
4358 l_struct_type VARCHAR2(30);
4359 l_struct_sharing_code pa_projects_all.structure_sharing_code%TYPE;
4360 l_event_id NUMBER;
4361 l_return_status VARCHAR2(1);
4362 l_lock_mode VARCHAR2(1);
4363 l_working_version_id number;
4364 l_request_id NUMBER;
4365 BEGIN
4366
4367
4368 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
4369 ( p_package_name => g_package_name
4370 , x_return_status => x_return_status );
4371 IF g_debug_mode='Y' THEN
4372 Pji_Utils.write2log('Coming IN WBS_MAINT :p_new_struct_ver_id: '||p_new_struct_ver_id||'p_old_struct_ver_id:'||p_old_struct_ver_id||'p_project_id: '||p_project_id||'p_publish_flag :' || p_publish_flag ,null,3);
4373 Pji_Utils.write2log('Coming IN WBS_MAINT :p_online_flag: '||p_online_flag||'p_calling_Context : '||p_calling_Context ||'p_rerun_flag: '|| p_rerun_flag,null,3);
4374 end if;
4375
4376 --Identify if the project has shared/split
4377 --versioned/non-versioned structure
4378 --l_struct_type :=
4379 -- Fix for bug : 4191390
4380 l_struct_sharing_code :=
4381 Pa_Project_Structure_Utils.get_structure_sharing_code (p_project_id);
4382
4383
4384 IF l_struct_sharing_code LIKE '%SHARE%' THEN
4385 l_shared_flag := 'Y';
4386 ELSIF l_struct_sharing_code LIKE '%SPLIT%' THEN
4387 l_shared_flag := 'N';
4388 IF p_publish_flag = 'Y' THEN
4389 SELECT
4390 typ.STRUCTURE_TYPE
4391 INTO
4392 l_struct_type
4393 FROM
4394 pa_structure_types typ,
4395 pa_proj_structure_types ptyp,
4396 pa_proj_element_versions ver
4397 WHERE
4398 typ.STRUCTURE_TYPE_ID = ptyp.STRUCTURE_TYPE_ID AND
4399 ptyp.PROJ_ELEMENT_ID = ver.PROJ_ELEMENT_ID AND
4400 ver.ELEMENT_VERSION_ID = p_new_struct_ver_id ; --p_old_struct_ver_id; fix for bug : 4191390
4401 END IF;
4402 END IF;
4403
4404 /* bug5353559*/
4405 l_versioned_flag := NVL( Pa_Workplan_Attr_Utils.check_wp_versioning_enabled (p_project_id),'N');
4406
4407
4408
4409 --------------------------------------
4410 --The first step is to LOG the event
4411 -------------------------------------
4412 --Initialize Event sequence
4413 SELECT pa_pji_proj_events_log_s.NEXTVAL
4414 INTO l_event_id
4415 FROM sys.dual;
4416
4417 IF l_versioned_flag = 'N' THEN
4418 --CASE 1: Non-Versioned (Work plans + actuals + fin plans)
4419
4420 INSERT INTO pa_pji_proj_events_log (
4421 EVENT_TYPE,
4422 EVENT_ID,
4423 EVENT_OBJECT,
4424 OPERATION_TYPE,
4425 STATUS,
4426 LAST_UPDATE_DATE,
4427 LAST_UPDATED_BY,
4428 CREATION_DATE,
4429 CREATED_BY,
4430 LAST_UPDATE_LOGIN,
4431 ATTRIBUTE1,
4432 ATTRIBUTE2,
4433 ATTRIBUTE3 )
4434 SELECT
4435 DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
4436 l_event_id,
4437 p_new_struct_ver_id,
4438 'I',
4439 'X',
4440 SYSDATE,
4441 -1,
4442 SYSDATE,
4443 -1,
4444 -1,
4445 p_project_id,
4446 p_old_struct_ver_id,
4447 head.PLAN_VERSION_ID
4448 FROM
4449 pji_pjp_wbs_header head
4450 WHERE
4451 head.PROJECT_ID = p_project_id AND
4452 head.WBS_VERSION_ID = p_old_struct_ver_id;
4453
4454 IF SQL%rowcount = 0 THEN
4455 INSERT INTO pa_pji_proj_events_log (
4456 EVENT_TYPE,
4457 EVENT_ID,
4458 EVENT_OBJECT,
4459 OPERATION_TYPE,
4460 STATUS,
4461 LAST_UPDATE_DATE,
4462 LAST_UPDATED_BY,
4463 CREATION_DATE,
4464 CREATED_BY,
4465 LAST_UPDATE_LOGIN,
4466 ATTRIBUTE1,
4467 ATTRIBUTE2,
4468 ATTRIBUTE3 )
4469 SELECT
4470 DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
4471 l_event_id,
4472 p_new_struct_ver_id,
4473 'I',
4474 'X',
4475 SYSDATE,
4476 -1,
4477 SYSDATE,
4478 -1,
4479 -1,
4480 p_project_id,
4481 p_old_struct_ver_id,
4482 NULL
4483 FROM dual;
4484 END IF;
4485
4486 -- Commented the logic to always process the request online
4487 -- this code change was done for R12 bug 5198662.
4488 -- If this is called in online mode then defer this processing
4489 -- by launching a concurrent request
4490 --IF p_online_flag = 'Y' THEN
4491 -- g_deffered_mode:='Y';
4492 --
4493 -- LAUNCH_WBS_REQ_PVT (
4494 -- p_event_id => l_event_id,
4495 -- p_calling_context => p_calling_Context,
4496 -- p_rerun_flag => p_rerun_flag,
4497 -- x_return_status => l_return_status );
4498 --
4499 -- x_return_status := l_return_status;
4500 -- x_processing_code := 'D';
4501
4502 --ELSE --Already in concurrent request
4503
4504 WBS_LOCK_PVT (
4505 p_event_id => l_event_id,
4506 p_online_flag => 'Y',
4507 p_request_id => Fnd_Global.CONC_REQUEST_ID,
4508 x_lock_mode => l_lock_mode,
4509 x_return_status => l_return_status );
4510
4511 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4512 x_return_status := l_return_status;
4513 x_msg_code := SQLERRM;
4514 END IF;
4515
4516 IF l_lock_mode = 'S' THEN
4517 WBS_MAINT_PVT (
4518 p_event_id => l_event_id,
4519 p_versioned_flag => l_versioned_flag,
4520 p_struct_type => l_struct_type , --l_shared_flag, fix for bug : 4191390
4521 p_publish_flag => p_publish_flag,
4522 p_Calling_Context=>p_Calling_Context,
4523 p_deffered_mode =>'N',
4524 p_rerun_flag => p_rerun_flag,
4525 x_return_status => l_return_status );
4526
4527 x_return_status := l_return_status;
4528 x_processing_code := 'S';
4529 END IF;
4530
4531 --END IF;--End deferred processing
4532
4533 ELSIF ((l_versioned_flag = 'Y' AND l_shared_flag = 'N') OR
4534 (l_versioned_flag = 'Y' AND l_shared_flag = 'Y' AND
4535 p_publish_flag = 'N') OR
4536 l_struct_type = 'WORKPLAN') THEN
4537 --CASE 2: Only Workplans
4538 INSERT INTO pa_pji_proj_events_log (
4539 EVENT_TYPE,
4540 EVENT_ID,
4541 EVENT_OBJECT,
4542 OPERATION_TYPE,
4543 STATUS,
4544 LAST_UPDATE_DATE,
4545 LAST_UPDATED_BY,
4546 CREATION_DATE,
4547 CREATED_BY,
4548 LAST_UPDATE_LOGIN,
4549 ATTRIBUTE1,
4550 ATTRIBUTE2,
4551 ATTRIBUTE3 )
4552 SELECT
4553 DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
4554 l_event_id,
4555 p_new_struct_ver_id,
4556 'I',
4557 'X',
4558 SYSDATE,
4559 -1,
4560 SYSDATE,
4561 -1,
4562 -1,
4563 p_project_id,
4564 p_old_struct_ver_id,
4565 head.PLAN_VERSION_ID
4566 FROM
4567 pji_pjp_wbs_header head
4568 WHERE
4569 head.PROJECT_ID = p_project_id AND
4570 head.WBS_VERSION_ID = p_old_struct_ver_id AND
4571 head.WP_FLAG = 'Y';
4572
4573 IF SQL%rowcount = 0 THEN
4574 INSERT INTO pa_pji_proj_events_log (
4575 EVENT_TYPE,
4576 EVENT_ID,
4577 EVENT_OBJECT,
4578 OPERATION_TYPE,
4579 STATUS,
4580 LAST_UPDATE_DATE,
4581 LAST_UPDATED_BY,
4582 CREATION_DATE,
4583 CREATED_BY,
4584 LAST_UPDATE_LOGIN,
4585 ATTRIBUTE1,
4586 ATTRIBUTE2,
4587 ATTRIBUTE3 )
4588 SELECT
4589 DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
4590 l_event_id,
4591 p_new_struct_ver_id,
4592 'I',
4593 'X',
4594 SYSDATE,
4595 -1,
4596 SYSDATE,
4597 -1,
4598 -1,
4599 p_project_id,
4600 p_old_struct_ver_id,
4601 NULL
4602 FROM dual;
4603 END IF;
4604
4605 WBS_LOCK_PVT (
4606 p_event_id => l_event_id,
4607 p_online_flag => 'Y',
4608 p_request_id => Fnd_Global.CONC_REQUEST_ID,
4609 x_lock_mode => l_lock_mode,
4610 x_return_status => l_return_status );
4611
4612 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4613 x_return_status := l_return_status;
4614 x_msg_code := SQLERRM;
4615 END IF;
4616
4617 IF l_lock_mode = 'S' THEN
4618
4619 WBS_MAINT_PVT (
4620 p_event_id => l_event_id,
4621 p_versioned_flag => l_versioned_flag,
4622 p_struct_type => l_struct_type , --l_shared_flag, fix for bug : 4191390
4623 p_publish_flag => p_publish_flag,
4624 p_Calling_Context=>p_Calling_Context,
4625 p_deffered_mode =>'N',
4626 p_rerun_flag => p_rerun_flag,
4627 x_return_status => l_return_status );
4628
4629 x_return_status := l_return_status;
4630 x_processing_code := 'S';
4631
4632 END IF;
4633 --Note: If lock is not acquired then no processing is done for event
4634
4635 ELSIF ((l_versioned_flag = 'Y' AND l_shared_flag = 'Y' AND
4636 p_publish_flag = 'Y') OR
4637 l_struct_type = 'FINANCIAL' ) THEN
4638 --CASE 3: Only for Financial plans and Actuals
4639 INSERT INTO pa_pji_proj_events_log (
4640 EVENT_TYPE,
4641 EVENT_ID,
4642 EVENT_OBJECT,
4643 OPERATION_TYPE,
4644 STATUS,
4645 LAST_UPDATE_DATE,
4646 LAST_UPDATED_BY,
4647 CREATION_DATE,
4648 CREATED_BY,
4649 LAST_UPDATE_LOGIN,
4650 ATTRIBUTE1,
4651 ATTRIBUTE2,
4652 ATTRIBUTE3 )
4653 SELECT
4654 'WBS_PUBLISH',
4655 l_event_id,
4656 p_new_struct_ver_id,
4657 'I',
4658 'X',
4659 SYSDATE,
4660 -1,
4661 SYSDATE,
4662 -1,
4663 -1,
4664 p_project_id,
4665 p_old_struct_ver_id,
4666 head.PLAN_VERSION_ID
4667 FROM
4668 pji_pjp_wbs_header head
4669 WHERE
4670 head.PROJECT_ID = p_project_id AND
4671 head.WBS_VERSION_ID = p_old_struct_ver_id AND
4672 head.WP_FLAG = 'N';
4673
4674 IF SQL%rowcount = 0 THEN
4675 INSERT INTO pa_pji_proj_events_log (
4676 EVENT_TYPE,
4677 EVENT_ID,
4678 EVENT_OBJECT,
4679 OPERATION_TYPE,
4680 STATUS,
4681 LAST_UPDATE_DATE,
4682 LAST_UPDATED_BY,
4683 CREATION_DATE,
4684 CREATED_BY,
4685 LAST_UPDATE_LOGIN,
4686 ATTRIBUTE1,
4687 ATTRIBUTE2,
4688 ATTRIBUTE3 )
4689 SELECT
4690 DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
4691 l_event_id,
4692 p_new_struct_ver_id,
4693 'I',
4694 'X',
4695 SYSDATE,
4696 -1,
4697 SYSDATE,
4698 -1,
4699 -1,
4700 p_project_id,
4701 p_old_struct_ver_id,
4702 NULL
4703 FROM dual;
4704 END IF;
4705
4706 -- Commented the logic to always process the request online
4707 -- this code change was done for R12 bug 5198662.
4708 -- If this is called in online mode then defer this processing
4709 -- by launching a concurrent request
4710 --IF p_online_flag = 'Y' THEN
4711 -- g_deffered_mode:='Y';
4712 --
4713 -- LAUNCH_WBS_REQ_PVT (
4714 -- p_event_id => l_event_id,
4715 -- p_calling_context =>p_calling_Context,
4716 -- p_rerun_flag => p_rerun_flag,
4717 -- x_return_status => l_return_status );
4718 --
4719 -- x_return_status := l_return_status;
4720 -- x_processing_code := 'D';
4721 --
4722 --ELSE
4723
4724 WBS_LOCK_PVT (
4725 p_event_id => l_event_id,
4726 p_online_flag => 'Y',
4727 p_request_id => Fnd_Global.CONC_REQUEST_ID,
4728 x_lock_mode => l_lock_mode,
4729 x_return_status => l_return_status );
4730
4731 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4732 x_return_status := l_return_status;
4733 x_msg_code := SQLERRM;
4734 END IF;
4735
4736 IF l_lock_mode = 'S' THEN
4737 WBS_MAINT_PVT (
4738 p_event_id => l_event_id,
4739 p_versioned_flag => l_versioned_flag,
4740 p_struct_type => l_struct_type , --l_shared_flag, fix for bug : 4191390
4741 p_publish_flag => p_publish_flag,
4742 p_Calling_Context=>p_Calling_Context,
4743 p_deffered_mode =>'N',
4744 p_rerun_flag => p_rerun_flag,
4745 x_return_status => l_return_status );
4746
4747 x_return_status := l_return_status;
4748 x_processing_code := 'S';
4749
4750 END IF;--end lock mode
4751
4752 END IF;
4753
4754 --END IF;
4755
4756 -- IF g_deffered_mode='Y' THEN
4757 -- x_online_flag:='N';
4758 -- END IF;
4759 IF g_debug_mode='Y' THEN
4760 Pji_Utils.write2log('Going out WBS_MAINT :x_request_id: '||x_request_id||'x_processing_code:'||x_processing_code||'x_msg_code: '||x_msg_code ,null,3);
4761 Pji_Utils.write2log('Going out WBS_MAINT :x_return_status: '||x_return_status||'x_online_flag :'||x_online_flag ,null,3);
4762 end if;
4763 EXCEPTION
4764 WHEN OTHERS THEN
4765 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
4766 ( p_package_name => g_package_name
4767 , p_procedure_name => 'WBS_MAINT'
4768 , x_return_status => x_return_status ) ;
4769
4770 RAISE;
4771 END;
4772
4773 --This API is called from the concurrent request
4774 --PRC: Process project summary changes
4775 PROCEDURE PROCESS_PROJ_SUM_CHANGES (
4776 errbuf OUT NOCOPY VARCHAR2,
4777 retcode OUT NOCOPY VARCHAR2,
4778 p_event_id IN NUMBER,
4779 p_calling_context IN VARCHAR2,
4780 p_rerun_flag IN VARCHAR2 := NULL )
4781 IS
4782
4783 l_shared_flag VARCHAR2(1);
4784 l_versioned_flag VARCHAR2(1);
4785 l_struct_type VARCHAR2(30);
4786 l_return_status VARCHAR2(1);
4787 l_project_id NUMBER;
4788 l_lock_mode VARCHAR2(1);
4789 l_publish_flag VARCHAR2(1);
4790 l_struct_version_id NUMBER;
4791 l_calling_context VARCHAR2(20);
4792
4793 BEGIN
4794 IF g_debug_mode='Y' THEN
4795 Pji_Utils.write2log( 'PROCESS_PROJ_SUM_CHANGES :p_event_id '||p_event_id ||'p_calling_context'||p_calling_context ,null,3);
4796 end if;
4797 select decode(p_calling_context, 'ONLINE_PUBLISH', 'CONC_PUBLISH'
4798 , 'ONLINE_UPDATE', 'CONC_UPDATE', p_calling_context)
4799 into l_calling_context
4800 from dual;
4801 savepoint process_proj_sum_conc;
4802 BEGIN
4803 SELECT
4804 TO_NUMBER(elog.ATTRIBUTE1),
4805 DECODE(elog.EVENT_TYPE, 'WBS_PUBLISH', 'Y', 'N'),
4806 elog.EVENT_OBJECT
4807 INTO
4808 l_project_id,
4809 l_publish_flag,
4810 l_struct_version_id
4811 FROM
4812 pa_pji_proj_events_log elog
4813 WHERE
4814 elog.EVENT_ID = p_event_id AND
4815 ROWNUM <= 1;
4816 EXCEPTION
4817 WHEN NO_DATA_FOUND THEN
4818 retcode := 0;
4819 RETURN;
4820 END;
4821
4822 --Identify if the project has shared/split
4823 --versioned/non-versioned structure
4824 l_struct_type :=
4825 Pa_Project_Structure_Utils.get_structure_sharing_code (l_project_id);
4826 IF l_struct_type LIKE 'SHARE%' THEN
4827 l_shared_flag := 'Y';
4828 ELSE
4829 l_shared_flag := 'N';
4830 END IF;
4831
4832 l_versioned_flag := NVL( Pa_Workplan_Attr_Utils.check_wp_versioning_enabled (l_project_id),'N'); /*bug5353559*/
4833
4834 WBS_LOCK_PVT (
4835 p_event_id => p_event_id,
4836 p_online_flag => 'N', --- 5138049 because in online NOWAIT is used
4837 p_request_id => Fnd_Global.CONC_REQUEST_ID,
4838 x_lock_mode => l_lock_mode ,
4839 x_return_status => l_return_status );
4840 IF g_debug_mode='Y' THEN
4841 Pji_Utils.write2log( 'PROCESS_PROJ_SUM_CHANGES :call to WBS_LOCK_PVT: l_lock_mode '||l_lock_mode ,null,3);
4842 end if;
4843 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4844 UPDATE pa_proj_elem_ver_structure
4845 SET PROCESS_CODE =decode(l_calling_context, 'APPLY_PROGRESS', 'APE'
4846 , 'CONC_PUBLISH', 'PUE'
4847 , 'CONC_UPDATE', 'WUE', null),
4848 process_update_wbs_flag = 'Y', /* 5138049 */
4849 CONC_REQUEST_ID = Fnd_Global.CONC_REQUEST_ID
4850 WHERE ELEMENT_VERSION_ID = l_struct_version_id
4851 AND PROJECT_ID = l_project_id;
4852 retcode := 2;
4853 errbuf := SQLERRM;
4854 ELSE
4855 retcode := 0;
4856 END IF;
4857
4858 IF l_lock_mode = 'S' THEN
4859
4860 WBS_MAINT_PVT (
4861 p_event_id => p_event_id,
4862 p_versioned_flag => l_versioned_flag,
4863 p_struct_type => l_shared_flag,
4864 p_publish_flag => l_publish_flag,
4865 p_calling_context=> l_calling_context,
4866 p_deffered_mode =>'Y',
4867 p_rerun_flag => p_rerun_flag,
4868 x_return_status => l_return_status );
4869 IF g_debug_mode='Y' THEN
4870 Pji_Utils.write2log( 'PROCESS_PROJ_SUM_CHANGES :call to WBS_MAINT_PVT: l_return_status '||l_return_status ,null,3);
4871 end if;
4872 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4873
4874 IF Fnd_Global.CONC_REQUEST_ID IS NOT NULL THEN
4875 rollback to process_proj_sum_conc;
4876 UPDATE pa_proj_elem_ver_structure
4877 SET PROCESS_CODE =decode(l_calling_context, 'APPLY_PROGRESS', 'APE'
4878 , 'CONC_PUBLISH', 'PUE'
4879 , 'CONC_UPDATE', 'WUE', null),
4880 process_update_wbs_flag = 'Y', /* 5138049 */
4881 CONC_REQUEST_ID = Fnd_Global.CONC_REQUEST_ID
4882 WHERE ELEMENT_VERSION_ID = l_struct_version_id
4883 AND PROJECT_ID = l_project_id;
4884 /*
4885 PA_PROJECT_STRUCTURE_UTILS.SET_PROCESS_CODE_ERR(
4886 p_project_id => l_project_id,
4887 p_structure_version_id => l_struct_version_id,
4888 p_calling_context => p_calling_context,
4889 p_conc_request_id => Fnd_Global.CONC_REQUEST_ID,
4890 x_return_status => l_return_status );
4891 */
4892 END IF;
4893
4894 retcode := 2;
4895 errbuf := SQLERRM;
4896 ELSE
4897 IF Fnd_Global.CONC_REQUEST_ID IS NOT NULL THEN
4898 UPDATE pa_proj_elem_ver_structure
4899 SET PROCESS_CODE = NULL,
4900 process_update_wbs_flag='N',
4901 CONC_REQUEST_ID = Fnd_Global.CONC_REQUEST_ID
4902 WHERE ELEMENT_VERSION_ID = l_struct_version_id
4903 AND PROJECT_ID = l_project_id;
4904 END IF;
4905 retcode := 0;
4906 END IF;
4907
4908 ELSIF l_lock_mode = 'F' THEN
4909 IF g_debug_mode='Y' THEN
4910 Pji_Utils.write2log( 'PROCESS_PROJ_SUM_CHANGES :Not able to take the lock showing the warning' ,null,4);
4911 end if;
4912 retcode := 1; --Unable to acquire lock and hence complete request as warning
4913
4914 END IF;
4915
4916 COMMIT;
4917
4918 EXCEPTION
4919 WHEN OTHERS THEN
4920 rollback to process_proj_sum_conc;
4921 IF Fnd_Global.CONC_REQUEST_ID IS NOT NULL THEN
4922 PA_PROJECT_STRUCTURE_UTILS.SET_PROCESS_CODE_ERR(
4923 p_project_id => l_project_id,
4924 p_structure_version_id => l_struct_version_id,
4925 p_calling_context => p_calling_context,
4926 p_conc_request_id => Fnd_Global.CONC_REQUEST_ID,
4927 x_return_status => l_return_status );
4928 END IF;
4929 IF g_debug_mode='Y' THEN
4930 Pji_Utils.write2log( 'PROCESS_PROJ_SUM_CHANGES : COMING to the exception' ,null,5);
4931 end if;
4932 retcode := 2;
4933 errbuf := SQLERRM;
4934 END;
4935
4936
4937 --
4938 -- Called from summarization programs.
4939 --
4940 PROCEDURE process_pending_events (
4941 x_return_status OUT NOCOPY VARCHAR2,
4942 x_msg_data OUT NOCOPY VARCHAR2 )
4943 IS
4944
4945 CURSOR c_pending_events IS
4946 SELECT
4947 DISTINCT
4948 elog.EVENT_TYPE,
4949 elog.EVENT_ID
4950 FROM
4951 pa_pji_proj_events_log elog
4952 WHERE
4953 elog.EVENT_TYPE IN ('WBS_CHANGE',
4954 'WBS_PUBLISH',
4955 'PLAN_DELETE',
4956 'PLAN_BASELINE',
4957 'PLAN_ORIGINAL',
4958 'PLAN_COPY' );
4959
4960 l_processing_code VARCHAR2(1);
4961
4962 BEGIN
4963
4964 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
4965 ( p_package_name => g_package_name
4966 , x_return_status => x_return_status );
4967
4968 /* FOR rec IN c_pending_events LOOP
4969
4970 proces_event_pvt (
4971 p_event_id => rec.EVENT_ID,
4972 p_event_type => rec.EVENT_TYPE,
4973 x_processing_code => l_processing_code,
4974 x_return_status => x_return_status,
4975 x_msg_data => x_msg_data);
4976
4977 END LOOP;--loop for pending events */
4978
4979 EXCEPTION
4980 WHEN OTHERS THEN
4981
4982 x_msg_data := SQLERRM;
4983
4984 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
4985 ( p_package_name => g_package_name
4986 , p_procedure_name => 'PROCESS_PENDING_EVENTS'
4987 , x_return_status => x_return_status ) ;
4988
4989 RAISE;
4990
4991 END;
4992
4993
4994 PROCEDURE proces_event_pvt (
4995 p_event_id IN NUMBER,
4996 p_event_type IN VARCHAR2,
4997 x_processing_code OUT NOCOPY VARCHAR2,
4998 x_return_status OUT NOCOPY VARCHAR2,
4999 x_msg_data OUT NOCOPY VARCHAR2 )
5000 IS
5001
5002 l_processing_code VARCHAR2(1);
5003 l_lock_mode VARCHAR2(1);
5004 l_return_status VARCHAR2(1);
5005 l_shared_flag VARCHAR2(1);
5006 l_versioned_flag VARCHAR2(1);
5007 l_struct_type VARCHAR2(30);
5008 l_project_id NUMBER;
5009 l_publish_flag VARCHAR2(1);
5010
5011 BEGIN
5012
5013 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
5014 ( p_package_name => g_package_name
5015 , x_return_status => x_return_status );
5016
5017
5018 IF p_event_type LIKE 'WBS%' THEN
5019
5020 WBS_LOCK_PVT (
5021 p_event_id => p_event_id,
5022 p_online_flag => 'Y',
5023 p_request_id => Fnd_Global.CONC_REQUEST_ID,
5024 x_lock_mode => l_lock_mode,
5025 x_return_status => l_return_status );
5026
5027 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
5028 x_return_status := l_return_status;
5029 x_msg_data := SQLERRM;
5030 END IF;
5031
5032 IF l_lock_mode = 'S' THEN
5033
5034 BEGIN
5035 SELECT
5036 TO_NUMBER(elog.ATTRIBUTE1)
5037 INTO
5038 l_project_id
5039 FROM
5040 pa_pji_proj_events_log elog
5041 WHERE
5042 elog.EVENT_ID = p_event_id AND
5043 ROWNUM <= 1;
5044 EXCEPTION
5045 WHEN NO_DATA_FOUND THEN
5046 RETURN;
5047 END;
5048
5049 --Identify if the project has shared/split
5050 --versioned/non-versioned structure
5051 l_struct_type :=
5052 Pa_Project_Structure_Utils.get_structure_sharing_code (l_project_id);
5053
5054 IF l_struct_type LIKE 'SHARE%' THEN
5055 l_shared_flag := 'Y';
5056 ELSE
5057 l_shared_flag := 'N';
5058 END IF;
5059
5060 l_versioned_flag :=
5061 Pa_Workplan_Attr_Utils.check_wp_versioning_enabled (l_project_id);
5062
5063 SELECT
5064 DECODE (p_event_type, 'WBS_PUBLISH', 'Y', 'N')
5065 INTO
5066 l_publish_flag
5067 FROM dual;
5068
5069 WBS_MAINT_PVT (
5070 p_event_id => p_event_id,
5071 p_versioned_flag => l_versioned_flag,
5072 p_struct_type => l_shared_flag,
5073 p_publish_flag => l_publish_flag,
5074 p_calling_context=>NULL,
5075 p_deffered_mode =>'N',
5076 p_rerun_flag => NULL,
5077 x_return_status => l_return_status );
5078
5079 x_return_status := l_return_status;
5080 l_processing_code := 'S';
5081
5082 END IF;
5083
5084 ELSIF p_event_type = 'PLAN_DELETE' THEN
5085
5086 PLAN_DELETE_PVT ( p_event_id => p_event_id,
5087 x_processing_code => l_processing_code ,
5088 x_return_status => x_return_status);
5089
5090 ELSIF p_event_type = 'PLAN_BASELINE' THEN
5091
5092 PLAN_BASELINE_PVT ( p_event_id => p_event_id,
5093 x_processing_code => l_processing_code ,
5094 x_return_status => x_return_status);
5095
5096 ELSIF p_event_type = 'PLAN_ORIGINAL' THEN
5097
5098 PLAN_ORIGINAL_PVT ( p_event_id => p_event_id,
5099 x_processing_code => l_processing_code ,
5100 x_return_status => x_return_status);
5101
5102 ELSIF p_event_type = 'PLAN_COPY' THEN
5103
5104 PLAN_COPY_PVT ( p_event_id => p_event_id,
5105 x_processing_code => l_processing_code ,
5106 x_return_status => x_return_status);
5107
5108 END IF; --end processing of event
5109
5110 x_processing_code := l_processing_code;
5111
5112
5113 EXCEPTION
5114 WHEN OTHERS THEN
5115
5116 x_msg_data := SQLERRM;
5117
5118 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
5119 ( p_package_name => g_package_name
5120 , p_procedure_name => 'PROCESS_EVENT_PVT'
5121 , x_return_status => x_return_status ) ;
5122
5123 RAISE;
5124 END;
5125
5126 PROCEDURE process_pending_plan_updates (
5127 x_return_status OUT NOCOPY VARCHAR2,
5128 x_msg_data OUT NOCOPY VARCHAR2 )
5129 IS
5130
5131 l_processing_code VARCHAR2(1);
5132
5133 BEGIN
5134
5135 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
5136 ( p_package_name => g_package_name
5137 , x_return_status => x_return_status );
5138
5139 -- PLAN_UPDATE_PVT(
5140 -- x_processing_code => l_processing_code
5141 -- , x_return_status => x_return_status );
5142
5143 EXCEPTION
5144 WHEN OTHERS THEN
5145 x_msg_data := SQLERRM;
5146 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
5147 ( p_package_name => g_package_name
5148 , p_procedure_name => 'PROCESS_PENDING_PLAN_UPDATES'
5149 , x_return_status => x_return_status ) ;
5150
5151 RAISE;
5152 END;
5153
5154
5155 ---------------------------------------------------------------
5156 --This API is called in the beginning of any report to ensure
5157 --there are no pending event for the plan version
5158 ---------------------------------------------------------------
5159 PROCEDURE process_plan_events (
5160 p_project_id IN NUMBER,
5161 p_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
5162 x_processing_code OUT NOCOPY VARCHAR2,
5163 x_msg_count OUT NOCOPY NUMBER,
5164 x_msg_data OUT NOCOPY VARCHAR2,
5165 x_return_status OUT NOCOPY VARCHAR2 )
5166 IS
5167
5168 CURSOR c_pending_plan_events ( p_plan_version_id NUMBER)
5169 IS
5170 SELECT
5171 evt.EVENT_ID,
5172 evt.EVENT_TYPE
5173 FROM
5174 pa_pji_proj_events_log evt
5175 WHERE
5176 evt.EVENT_TYPE IN ( 'PLAN_DELETE',
5177 'PLAN_BASELINE',
5178 'PLAN_ORIGINAL',
5179 'PLAN_COPY') AND
5180 evt.EVENT_OBJECT = to_char(p_plan_version_id)
5181 ORDER BY evt.EVENT_ID ASC;
5182
5183 CURSOR c_pending_wbs_events ( p_plan_version_id NUMBER)
5184 IS
5185 SELECT
5186 evt.EVENT_ID,
5187 evt.EVENT_TYPE
5188 FROM
5189 pa_pji_proj_events_log evt,
5190 pji_pjp_wbs_header head
5191 WHERE
5192 evt.EVENT_TYPE IN ( 'WBS_CHANGE',
5193 'WBS_PUBLISH') AND
5194 evt.EVENT_OBJECT = to_char(head.WBS_VERSION_ID) AND
5195 head.PLAN_VERSION_ID = p_plan_version_id AND
5196 to_char(head.PROJECT_ID ) = evt.ATTRIBUTE1 AND --Bug 7591055
5197 to_char(head.plan_version_id) = evt.ATTRIBUTE3 --Bug 7591055
5198 ORDER BY evt.EVENT_ID ASC;
5199
5200
5201 l_child_task_check NUMBER := 0;
5202
5203 l_plan_version_id NUMBER;
5204
5205 l_child_exist Boolean := TRUE; -- Added for bug 3899810
5206 l_wbs_version_id NUMBER := NULL; -- Added for bug 3899810
5207
5208 BEGIN
5209
5210 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
5211 ( p_package_name => g_package_name
5212 , x_return_status => x_return_status );
5213
5214
5215 /* Begin :
5216 Bug# 3740751 - If child level task structure is not exists then raise the
5217 error message */
5218
5219
5220 /* Get the plan version which is from the table and check if any stcuture is exists */
5221
5222
5223 /* Commented out for bug 3899810 skannoji
5224 IF p_plan_version_id_tbl(1) IS NOT NULL THEN
5225
5226 l_plan_version_id := p_plan_version_id_tbl(1);
5227
5228 END IF;
5229
5230
5231
5232 SELECT COUNT(*)
5233 INTO l_child_task_check
5234 FROM pa_xbs_denorm denom,
5235 pji_pjp_wbs_header headr
5236 WHERE denom.sup_project_id = p_project_id
5237 AND headr.project_id = denom.sup_project_id
5238 AND headr.plan_version_id = l_plan_version_id
5239 AND headr.wbs_version_id = denom.struct_version_id
5240 AND struct_type = 'XBS'
5241 AND ROWNUM =1;
5242 */
5243
5244
5245 -- Bug 3899810 : Added p_plan_version_id_tbl.count check
5246 IF p_plan_version_id_tbl.count > 0 THEN -- Count if
5247 FOR i IN p_plan_version_id_tbl.first..p_plan_version_id_tbl.last LOOP
5248 l_plan_version_id := NULL;
5249
5250 IF ( ( p_plan_version_id_tbl.EXISTS(i))
5251 ) THEN
5252 l_plan_version_id := p_plan_version_id_tbl(i);
5253
5254 l_child_task_check := 0;
5255
5256 BEGIN
5257 l_wbs_version_id:=NULL;
5258
5259 SELECT wbs_version_id INTO l_wbs_version_id
5260 FROM pji_pjp_wbs_header
5261 WHERE plan_version_id = l_plan_version_id
5262 AND project_id = p_project_id;
5263 EXCEPTION
5264 WHEN NO_DATA_FOUND THEN
5265 NULL;
5266 END;
5267
5268 IF (l_wbs_version_id IS NOT NULL)
5269 THEN
5270 SELECT COUNT(*)
5271 INTO l_child_task_check
5272 FROM pa_object_relationships por
5273 WHERE por.object_id_from1 = l_wbs_version_id
5274 AND por.relationship_type = 'S'
5275 AND rownum = 1;
5276
5277 IF (l_child_task_check = 0)
5278 THEN
5279 l_child_exist := FALSE;
5280 ELSE
5281 l_child_exist := TRUE;
5282 EXIT;
5283 END IF;
5284 END IF; -- execute only if plan version is not null
5285
5286 /* Commented for bug 3899810
5287 IF (NVL(l_child_task_check,0) <> 0 ) THEN
5288 EXIT;
5289 END IF;
5290 */
5291 END IF;
5292
5293 END LOOP;
5294 /* till here for bug 3899810 */
5295
5296 /* If structure not found for the lowest task then come out the procedure otherwies
5297 process further */
5298
5299 -- IF (l_child_task_check = 0) THEN
5300 IF (NOT l_child_exist) THEN -- added for bug 3899810 false
5301
5302 pji_rep_util.Add_Message(p_app_short_name=> 'PJI',
5303 p_msg_name=> 'PJI_REP_NO_TASK_DEFINED',
5304 p_msg_type=>Pji_Rep_Util.G_RET_STS_WARNING);
5305
5306
5307 RETURN;
5308
5309 END IF;
5310
5311 END IF; -- Added this Count end if for bug 3899810
5312
5313
5314 /* End Bug#3740751 */
5315
5316
5317
5318 <<OUTER>>
5319 FOR i IN 1..p_plan_version_id_tbl.COUNT LOOP
5320
5321 FOR rec IN c_pending_plan_events( p_plan_version_id_tbl(i)) LOOP
5322
5323 proces_event_pvt (
5324 p_event_id => rec.EVENT_ID,
5325 p_event_type => rec.EVENT_TYPE,
5326 x_processing_code => x_processing_code,
5327 x_return_status => x_return_status,
5328 x_msg_data => x_msg_data );
5329
5330 IF x_processing_code = 'F' THEN
5331 EXIT OUTER;
5332 END IF;
5333 END LOOP;
5334
5335 --Logic for WBS events
5336 FOR rec IN c_pending_wbs_events( p_plan_version_id_tbl(i)) LOOP
5337
5338 proces_event_pvt (
5339 p_event_id => rec.EVENT_ID,
5340 p_event_type => rec.EVENT_TYPE,
5341 x_processing_code => x_processing_code,
5342 x_return_status => x_return_status,
5343 x_msg_data => x_msg_data );
5344
5345 IF x_processing_code = 'F' THEN
5346 EXIT OUTER;
5347 END IF;
5348 END LOOP;
5349
5350
5351 /* PLAN_UPDATE_PVT
5352 ( p_plan_version_id => p_plan_version_id_tbl(i),
5353 x_processing_code => x_processing_code,
5354 x_return_status => x_return_status );*/
5355
5356 IF x_processing_code = 'F' THEN
5357 EXIT;
5358 END IF;
5359
5360 END LOOP;
5361
5362 COMMIT;
5363
5364 EXCEPTION
5365 WHEN OTHERS THEN
5366 x_msg_data := SQLERRM;
5367 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
5368 ( p_package_name => g_package_name
5369 , p_procedure_name => 'PROCESS_PLAN_EVENTS'
5370 , x_return_status => x_return_status ) ;
5371
5372 RAISE;
5373 END;
5374
5375
5376 --
5377 -- Create on demand slices.
5378 --
5379 PROCEDURE maintain_smart_slice (
5380 p_rbs_version_id IN NUMBER :=NULL,
5381 p_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
5382 p_wbs_element_id IN NUMBER,
5383 p_rbs_element_id IN NUMBER,
5384 p_prg_rollup_flag IN VARCHAR2,
5385 p_curr_record_type_id IN NUMBER,
5386 p_calendar_type IN VARCHAR2,
5387 p_wbs_version_id IN NUMBER,
5388 p_commit IN VARCHAR2 := 'Y',
5389 p_rbs_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
5390 x_msg_count OUT NOCOPY NUMBER,
5391 x_msg_data OUT NOCOPY VARCHAR2,
5392 x_return_status OUT NOCOPY VARCHAR2) IS
5393
5394 -- l_fact_act_rlp_exists VARCHAR2(1) := 'N';
5395 -- l_rollup_status_exists VARCHAR2(1) := 'N';
5396 l_exists VARCHAR2(1) := 'N';
5397 l_struct_element_id NUMBER := -1;
5398 l_project_id NUMBER := 1;
5399 l_proj_element_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5400 l_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5401 l_plan_type_code_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
5402 l_project_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5403 l_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5404 l_count NUMBER := 1;
5405
5406 l_last_update_date date := SYSDATE;
5407 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
5408 l_creation_date date := SYSDATE;
5409 l_created_by NUMBER := FND_GLOBAL.USER_ID;
5410 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
5411 il_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5412 il_plan_type_code_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type(); /*4471527 */
5413 il_project_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5414 il_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5415 l_prg_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5416 l_prg_plan_type_code_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type(); /*4471527 */
5417 l_prg_project_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5418 l_prg_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5419 l_prg_count NUMBER := 1;
5420 l_roll_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5421 l_get_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5422 l_get_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5423 l_get_plan_type_code_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type(); /*4471527 */
5424 l_wbs_count NUMBER:=2;
5425 l_wbs_exists NUMBER :=0;
5426 validcount NUMBER:=1;
5427 jl_get_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); /*4471527 */
5428 jl_get_plan_type_code_tbl SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type(); /*4471527 */
5429 jl_get_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); /*4471527 */
5430 l_rbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5431 BEGIN
5432
5433 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
5434 ( p_package_name => g_package_name
5435 , x_return_status => x_return_status );
5436 /* overriding the parameter p_rbs_version_id for Public API with PLSQl table */
5437 IF ( p_rbs_version_id is NOT NULL) THEN
5438 l_rbs_version_id_tbl.EXTEND;
5439 l_rbs_version_id_tbl(1) := p_rbs_version_id;
5440 else
5441 FOR i IN 1..p_rbs_version_id_tbl.COUNT LOOP
5442 l_rbs_version_id_tbl.EXTEND;
5443 l_rbs_version_id_tbl(i) := p_rbs_version_id_tbl(i);
5444 end loop;
5445 end if;
5446
5447 IF g_debug_mode='Y' THEN
5448 -- write_log ( ' maintain smart slice .. 0001 :p_wbs_version_id'||p_wbs_version_id ||'p_rbs_version_id'||p_rbs_version_id||'p_prg_rollup_flag'||p_prg_rollup_flag);
5449 Pji_Utils.write2log('maintain smart slice .. 0001 :p_wbs_version_id'||p_wbs_version_id ||
5450 'p_rbs_version_id'||p_rbs_version_id||'p_prg_rollup_flag'||p_prg_rollup_flag,null,3);
5451 end if;
5452 IF (l_rbs_version_id_tbl.COUNT=0) THEN
5453 Pji_Utils.write2log('p_rbs_version_id is NULL. Nothing to rollup by. Returning.',null,3);
5454 RETURN;
5455 END IF;
5456
5457 BEGIN
5458 SELECT
5459 PROJ_ELEMENT_ID, project_id
5460 INTO
5461 l_struct_element_id, l_project_id
5462 FROM
5463 pa_proj_element_versions
5464 WHERE
5465 ELEMENT_VERSION_ID = p_wbs_version_id;
5466 EXCEPTION
5467 WHEN NO_DATA_FOUND THEN
5468 NULL;
5469 END;
5470 /* Based on the input plan version id of the parent project getting all the corresponding wbs_version_ids */
5471 FOR i IN 1..p_plan_version_id_tbl.COUNT LOOP
5472 BEGIN
5473 select distinct head.wbs_version_id,head.plan_version_id,head.plan_type_code BULK COLLECT
5474 into jl_get_wbs_version_id_tbl,jl_get_plan_version_id_tbl,jl_get_plan_type_code_tbl /*4471527 */
5475 from pji_pjp_wbs_header head
5476 where head.plan_version_id = p_plan_version_id_tbl(i)
5477 and head.project_id in (select project_id from pji_pjp_wbs_header head1
5478 where head1.wbs_version_id=p_wbs_version_id );
5479 FOR j IN 1..jl_get_plan_version_id_tbl.COUNT LOOP
5480 l_get_plan_version_id_tbl.EXTEND;
5481 l_get_plan_version_id_tbl(validcount) := jl_get_plan_version_id_tbl(j);
5482 l_get_plan_type_code_tbl.EXTEND;
5483 l_get_plan_type_code_tbl(validcount) := jl_get_plan_type_code_tbl(j);
5484 l_get_wbs_version_id_tbl.EXTEND;
5485 l_get_wbs_version_id_tbl(validcount) := jl_get_wbs_version_id_tbl(j);
5486
5487 /* l_get_plan_version_id_tbl.EXTEND;
5488 l_get_wbs_version_id_tbl.EXTEND;
5489 l_get_plan_type_code_tbl.EXTEND;
5490 l_get_wbs_version_id_tbl(validcount) :=l_get_wbs_version_id;
5491 l_get_plan_version_id_tbl(validcount):=p_plan_version_id_tbl(i);
5492 l_get_plan_type_code_tbl(validcount):=l_get_plan_type_code; /*4471527 */
5493 validcount:=validcount+1;
5494 END LOOP;
5495 EXCEPTION
5496 WHEN NO_DATA_FOUND THEN
5497 NULL;
5498 WHEN TOO_MANY_ROWS THEN
5499 RAISE Invalid_excep;
5500 END;
5501 END LOOP;
5502 /* Converting the wbs_version_id to maxmimum allowed numbers to use in the below SQL */
5503 FOR i IN 1..31 LOOP
5504 IF ( l_get_wbs_version_id_tbl.EXISTS(i)) THEN
5505 null;
5506 ELSE
5507 l_get_wbs_version_id_tbl.EXTEND;
5508 l_get_wbs_version_id_tbl(i) := 0;
5509 END IF;
5510 END LOOP;
5511 /* Getting all the sub-project linked plan_version ids , struct_version_ids,
5512 and the project_ids for the Program Project when the the call is made in Program Mode */
5513 IF p_prg_rollup_flag='Y' THEN
5514 BEGIN
5515 SELECT distinct head.plan_version_id,head.project_id ,pji.sub_id wbs_version_id,head.plan_type_code BULK COLLECT
5516 into il_plan_version_id_tbl, il_project_id_tbl,il_wbs_version_id_tbl,il_plan_type_code_tbl /*4471527 */
5517 FROM pji_xbs_Denorm pji
5518 ,pa_proj_element_versions pa
5519 ,pji_pjp_wbs_header head
5520 WHERE pji.struct_type='PRG'
5521 and pji.sup_level<>pji.sub_level
5522 and pji.sup_id in (l_get_wbs_version_id_tbl(1),l_get_wbs_version_id_tbl(2),l_get_wbs_version_id_tbl(3)
5523 ,l_get_wbs_version_id_tbl(4),l_get_wbs_version_id_tbl(5),l_get_wbs_version_id_tbl(6)
5524 ,l_get_wbs_version_id_tbl(7),l_get_wbs_version_id_tbl(8),l_get_wbs_version_id_tbl(9)
5525 ,l_get_wbs_version_id_tbl(10),l_get_wbs_version_id_tbl(11),l_get_wbs_version_id_tbl(12)
5526 ,l_get_wbs_version_id_tbl(13),l_get_wbs_version_id_tbl(14),l_get_wbs_version_id_tbl(15)
5527 ,l_get_wbs_version_id_tbl(16),l_get_wbs_version_id_tbl(17),l_get_wbs_version_id_tbl(18)
5528 ,l_get_wbs_version_id_tbl(19),l_get_wbs_version_id_tbl(20),l_get_wbs_version_id_tbl(21)
5529 ,l_get_wbs_version_id_tbl(22),l_get_wbs_version_id_tbl(23),l_get_wbs_version_id_tbl(24)
5530 ,l_get_wbs_version_id_tbl(25),l_get_wbs_version_id_tbl(26),l_get_wbs_version_id_tbl(27)
5531 ,l_get_wbs_version_id_tbl(28),l_get_wbs_version_id_tbl(29),l_get_wbs_version_id_tbl(30)
5532 ,l_get_wbs_version_id_tbl(31))
5533 and pa.ELEMENT_VERSION_ID=pji.SUB_ID
5534 AND head.project_id=pa.project_id
5535 AND pji.sub_id=head.wbs_version_id
5536 and (head.cb_flag='Y'
5537 OR head.co_flag='Y'
5538 OR head.wp_flag='Y'
5539 OR (head.wp_flag='N' and head.plan_version_id=-1)
5540 );
5541 EXCEPTION
5542 WHEN NO_DATA_FOUND THEN Null;
5543 WHEN OTHERS THEN
5544 RAISE Invalid_excep;
5545 END;
5546 END IF;
5547 /* Adding the two PLSQL table to one which will be used as the final plsql table for processing,
5548 l_prg_plan_version_id_tbl=l_get_plan_version_id_tbl+ il_plan_version_id_tbl*/
5549 FOR i IN 1..l_get_plan_version_id_tbl.COUNT LOOP
5550 l_prg_plan_version_id_tbl.EXTEND;
5551 l_prg_plan_version_id_tbl(l_prg_count) := l_get_plan_version_id_tbl(i);
5552
5553 l_prg_plan_type_code_tbl.EXTEND;
5554 l_prg_plan_type_code_tbl(l_prg_count) := l_get_plan_type_code_tbl(i);
5555
5556 l_prg_wbs_version_id_tbl.EXTEND;
5557 l_prg_wbs_version_id_tbl(l_prg_count) := l_get_wbs_version_id_tbl(i);
5558
5559 l_prg_project_id_tbl.EXTEND;
5560 l_prg_project_id_tbl(l_prg_count) := l_project_id;
5561 IF g_debug_mode='Y' THEN
5562 Pji_Utils.write2log(' maintain smart slice11: '||l_prg_count||':l_prg_plan_version_id_tbl'||
5563 l_prg_plan_version_id_tbl(l_prg_count)||'l_prg_wbs_version_id_tbl'||l_prg_wbs_version_id_tbl(l_prg_count)||
5564 'l_prg_project_id_tbl'||l_prg_project_id_tbl(l_prg_count) ,null,3);
5565 end if;
5566 l_prg_count := l_prg_count + 1;
5567 END LOOP;
5568
5569 FOR i IN 1..il_plan_version_id_tbl.COUNT LOOP
5570 l_prg_plan_version_id_tbl.EXTEND;
5571 l_prg_plan_version_id_tbl(l_prg_count) := il_plan_version_id_tbl(i);
5572
5573 l_prg_plan_type_code_tbl.EXTEND;
5574 l_prg_plan_type_code_tbl(l_prg_count) := il_plan_type_code_tbl(i);
5575
5576 l_prg_wbs_version_id_tbl.EXTEND;
5577 l_prg_wbs_version_id_tbl(l_prg_count) := il_wbs_version_id_tbl(i);
5578
5579 l_prg_project_id_tbl.EXTEND;
5580 l_prg_project_id_tbl(l_prg_count) := il_project_id_tbl(i);
5581 IF g_debug_mode='Y' THEN
5582 Pji_Utils.write2log(' maintain smart slice11: '||l_prg_count||':l_prg_plan_version_id_tbl'||
5583 l_prg_plan_version_id_tbl(l_prg_count)||'l_prg_wbs_version_id_tbl'||l_prg_wbs_version_id_tbl(l_prg_count)||
5584 'l_prg_project_id_tbl'||l_prg_project_id_tbl(l_prg_count) ,null,3);
5585 end if;
5586 l_prg_count := l_prg_count + 1;
5587 END LOOP;
5588 /* For the list of Plan versions checking the rollup table to see if the smart slice is already created */
5589 FOR k IN 1..l_rbs_version_id_tbl.COUNT LOOP
5590 FOR i IN 1..l_prg_plan_version_id_tbl.COUNT LOOP
5591
5592 -- Reset flag values.
5593 -- l_rollup_status_exists := 'N';
5594 l_exists := 'N';
5595
5596 BEGIN
5597
5598 SELECT 'Y' -- , 'Y'
5599 INTO l_exists -- , l_rollup_status_exists
5600 FROM pji_rollup_level_status rst
5601 WHERE rst.RBS_VERSION_ID = l_rbs_version_id_tbl(k) AND
5602 rst.PLAN_VERSION_ID = l_prg_plan_version_id_tbl(i) AND
5603 rst.PLAN_TYPE_CODE = l_prg_plan_type_code_tbl(i) AND
5604 rst.project_id = l_prg_project_id_tbl(i);
5605
5606 EXCEPTION
5607 WHEN NO_DATA_FOUND THEN
5608 NULL;
5609 END;
5610 IF g_debug_mode='Y' THEN
5611 Pji_Utils.write2log(' maintain smart slice ..1.2 p_plan_version_id_tbl ('||i||') : ' || l_prg_plan_version_id_tbl(i) ,null,3);
5612 Pji_Utils.write2log(' maintain smart slice ..1.3 l_exists :' || l_exists ,null,3);
5613 end if;
5614
5615 --If smart slice does not exist create the same
5616 IF ( l_exists = 'N' ) THEN
5617 l_plan_version_id_tbl.EXTEND;
5618 l_plan_version_id_tbl(l_count) := l_prg_plan_version_id_tbl(i);
5619 l_plan_type_code_tbl.EXTEND;
5620 l_plan_type_code_tbl(l_count) := l_prg_plan_type_code_tbl(i);
5621 l_project_id_tbl.EXTEND;
5622 l_project_id_tbl(l_count) := l_prg_project_id_tbl(i);
5623 l_wbs_version_id_tbl.EXTEND;
5624 l_wbs_version_id_tbl(l_count) := l_prg_wbs_version_id_tbl(i);
5625 IF g_debug_mode='Y' THEN
5626 Pji_Utils.write2log(' maintain smart slice44: '||l_count||':l_plan_version_id_tbl'||l_plan_version_id_tbl(l_count)||'l_wbs_version_id_tbl'
5627 ||l_wbs_version_id_tbl(l_count)||'l_project_id_tbl'||l_project_id_tbl(l_count) ,null,3);
5628 end if;
5629 l_count := l_count + 1;
5630
5631 --Populate rollup level status table
5632 INSERT INTO pji_rollup_level_status (
5633 PROJECT_ID,
5634 RBS_VERSION_ID,
5635 PLAN_VERSION_ID,
5636 WBS_ELEMENT_ID,
5637 RBS_AGGR_LEVEL,
5638 WBS_ROLLUP_FLAG,
5639 PRG_ROLLUP_FLAG,
5640 CURR_RECORD_TYPE_ID,
5641 CALENDAR_TYPE,
5642 LAST_UPDATE_DATE,
5643 LAST_UPDATED_BY,
5644 CREATION_DATE,
5645 CREATED_BY,
5646 LAST_UPDATE_LOGIN,
5647 PLAN_TYPE_CODE /*4471527 */
5648 )
5649 SELECT
5650 l_prg_project_id_tbl(i) project_id,
5651 l_rbs_version_id_tbl(k) RBS_VERSION_ID,
5652 l_prg_plan_version_id_tbl(i) PLAN_VERSION_ID,
5653 -1 WBS_ELEMENT_ID,
5654 'R' RBS_AGGR_LEVEL,
5655 'Y' WBS_ROLLUP_FLAG,
5656 'N' PRG_ROLLUP_FLAG,
5657 31 CURR_RECORD_TYPE_ID,
5658 'X' CALENDAR_TYPE,
5659 l_last_update_date LAST_UPDATE_DATE,
5660 l_last_updated_by LAST_UPDATED_BY,
5661 l_creation_date CREATION_DATE,
5662 l_created_by CREATED_BY,
5663 l_last_update_login LAST_UPDATE_LOGIN,
5664 l_prg_plan_type_code_tbl(i) PLAN_TYPE_CODE /*4471527 */
5665 FROM dual;
5666
5667 END IF;
5668
5669 END LOOP;
5670 END LOOP; /* En dof K loop for RBS version_id*/
5671 -- This code will remove all the duplicate entries from l_wbs_version_id_tbl which will be used to do the WBS and XBS rollup
5672 BEGIN
5673 FOR j IN 1..l_wbs_version_id_tbl.COUNT LOOP
5674 if j=1 then
5675 l_roll_wbs_version_id_tbl.EXTEND;
5676 l_roll_wbs_version_id_tbl(j) := l_wbs_version_id_tbl(j);
5677 end if;
5678 FOR i IN 1..l_roll_wbs_version_id_tbl.COUNT LOOP
5679 if l_roll_wbs_version_id_tbl(i) = l_wbs_version_id_tbl(j)
5680 and j>1 then
5681 l_wbs_exists:=1;
5682 exit;
5683 else
5684 l_wbs_exists:=0;
5685 END IF;
5686 END LOOP;
5687 IF l_wbs_exists <>1 and j<>1 THEN
5688 l_roll_wbs_version_id_tbl.EXTEND;
5689 l_roll_wbs_version_id_tbl(l_wbs_count) := l_wbs_version_id_tbl(j);
5690 l_wbs_count := l_wbs_count + 1;
5691 END IF;
5692 END LOOP;
5693 END;
5694
5695 CLEANUP_TEMP_TABLES;
5696
5697 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(
5698 p_fp_version_ids => l_plan_version_id_tbl
5699 , p_slice_type => 'PRI'
5700 );
5701 IF g_debug_mode='Y' THEN
5702 Pji_Utils.write2log(' maintain smart slice44.02:PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS' ,null,3);
5703 end if;
5704
5705 IF g_debug_mode='Y' THEN
5706 Pji_Utils.write2log(' maintain smart slice55:l_plan_version_id_tbl.COUNT: '||l_plan_version_id_tbl.COUNT||
5707 '::l_roll_wbs_version_id_tbl.COUNT: '||l_roll_wbs_version_id_tbl.COUNT ,null,3);
5708 end if;
5709 --Inserting 'L' 'N' 'N' / 'L' 'N' 'Y' slices
5710 FOR k IN 1..l_rbs_version_id_tbl.COUNT LOOP
5711 FORALL j IN 1..l_plan_version_id_tbl.COUNT
5712
5713 INSERT INTO pji_fp_aggr_pjp1_t (
5714 WORKER_ID,
5715 PRG_LEVEL,
5716 PROJECT_ID,
5717 PROJECT_ORG_ID,
5718 PROJECT_ORGANIZATION_ID,
5719 PROJECT_ELEMENT_ID,
5720 TIME_ID, PERIOD_TYPE_ID,
5721 CALENDAR_TYPE,
5722 RBS_AGGR_LEVEL,
5723 WBS_ROLLUP_FLAG,
5724 PRG_ROLLUP_FLAG,
5725 CURR_RECORD_TYPE_ID,
5726 CURRENCY_CODE,
5727 RBS_ELEMENT_ID,
5728 RBS_VERSION_ID,
5729 PLAN_VERSION_ID,
5730 PLAN_TYPE_ID,
5731 RAW_COST,
5732 BRDN_COST,
5733 REVENUE,
5734 BILL_RAW_COST,
5735 BILL_BRDN_COST,
5736 BILL_LABOR_RAW_COST,
5737 BILL_LABOR_BRDN_COST,
5738 BILL_LABOR_HRS,
5739 EQUIPMENT_RAW_COST,
5740 EQUIPMENT_BRDN_COST,
5741 CAPITALIZABLE_RAW_COST,
5742 CAPITALIZABLE_BRDN_COST,
5743 LABOR_RAW_COST,
5744 LABOR_BRDN_COST,
5745 LABOR_HRS,
5746 LABOR_REVENUE,
5747 EQUIPMENT_HOURS,
5748 BILLABLE_EQUIPMENT_HOURS,
5749 SUP_INV_COMMITTED_COST,
5750 PO_COMMITTED_COST,
5751 PR_COMMITTED_COST,
5752 OTH_COMMITTED_COST,
5753 ACT_LABOR_HRS,
5754 ACT_EQUIP_HRS,
5755 ACT_LABOR_BRDN_COST,
5756 ACT_EQUIP_BRDN_COST,
5757 ACT_BRDN_COST,
5758 ACT_RAW_COST,
5759 ACT_REVENUE,
5760 ACT_LABOR_RAW_COST,
5761 ACT_EQUIP_RAW_COST,
5762 ETC_LABOR_HRS,
5763 ETC_EQUIP_HRS,
5764 ETC_LABOR_BRDN_COST,
5765 ETC_EQUIP_BRDN_COST,
5766 ETC_BRDN_COST,
5767 ETC_RAW_COST,
5768 ETC_LABOR_RAW_COST,
5769 ETC_EQUIP_RAW_COST,
5770 CUSTOM1,
5771 CUSTOM2,
5772 CUSTOM3,
5773 CUSTOM4,
5774 CUSTOM5,
5775 CUSTOM6,
5776 CUSTOM7,
5777 CUSTOM8,
5778 CUSTOM9,
5779 CUSTOM10,
5780 CUSTOM11,
5781 CUSTOM12,
5782 CUSTOM13,
5783 CUSTOM14,
5784 CUSTOM15 ,
5785 PLAN_TYPE_CODE) /*4471527 */
5786 SELECT
5787 -1,
5788 0,
5789 fact.PROJECT_ID,
5790 fact.PROJECT_ORG_ID,
5791 fact.PROJECT_ORGANIZATION_ID,
5792 fact.PROJECT_ELEMENT_ID,
5793 fact.TIME_ID,
5794 fact.PERIOD_TYPE_ID,
5795 fact.CALENDAR_TYPE,
5796 fact.RBS_AGGR_LEVEL,
5797 fact.WBS_ROLLUP_FLAG,
5798 fact.PRG_ROLLUP_FLAG,
5799 fact.CURR_RECORD_TYPE_ID,
5800 fact.CURRENCY_CODE,
5801 fact.RBS_ELEMENT_ID,
5802 fact.RBS_VERSION_ID,
5803 fact.PLAN_VERSION_ID,
5804 fact.PLAN_TYPE_ID,
5805 fact.RAW_COST,
5806 fact.BRDN_COST,
5807 fact.REVENUE,
5808 fact.BILL_RAW_COST,
5809 fact.BILL_BRDN_COST,
5810 fact.BILL_LABOR_RAW_COST,
5811 fact.BILL_LABOR_BRDN_COST,
5812 fact.BILL_LABOR_HRS,
5813 fact.EQUIPMENT_RAW_COST,
5814 fact.EQUIPMENT_BRDN_COST,
5815 fact.CAPITALIZABLE_RAW_COST,
5816 fact.CAPITALIZABLE_BRDN_COST,
5817 fact.LABOR_RAW_COST,
5818 fact.LABOR_BRDN_COST,
5819 fact.LABOR_HRS,
5820 fact.LABOR_REVENUE,
5821 fact.EQUIPMENT_HOURS,
5822 fact.BILLABLE_EQUIPMENT_HOURS,
5823 fact.SUP_INV_COMMITTED_COST,
5824 fact.PO_COMMITTED_COST,
5825 fact.PR_COMMITTED_COST,
5826 fact.OTH_COMMITTED_COST,
5827 fact.ACT_LABOR_HRS,
5828 fact.ACT_EQUIP_HRS,
5829 fact.ACT_LABOR_BRDN_COST,
5830 fact.ACT_EQUIP_BRDN_COST,
5831 fact.ACT_BRDN_COST,
5832 fact.ACT_RAW_COST,
5833 fact.ACT_REVENUE,
5834 fact.ACT_LABOR_RAW_COST,
5835 fact.ACT_EQUIP_RAW_COST,
5836 fact.ETC_LABOR_HRS,
5837 fact.ETC_EQUIP_HRS,
5838 fact.ETC_LABOR_BRDN_COST,
5839 fact.ETC_EQUIP_BRDN_COST,
5840 fact.ETC_BRDN_COST,
5841 fact.ETC_RAW_COST,
5842 fact.ETC_LABOR_RAW_COST,
5843 fact.ETC_EQUIP_RAW_COST,
5844 fact.CUSTOM1,
5845 fact.CUSTOM2,
5846 fact.CUSTOM3,
5847 fact.CUSTOM4,
5848 fact.CUSTOM5,
5849 fact.CUSTOM6,
5850 fact.CUSTOM7,
5851 fact.CUSTOM8,
5852 fact.CUSTOM9,
5853 fact.CUSTOM10,
5854 fact.CUSTOM11,
5855 fact.CUSTOM12,
5856 fact.CUSTOM13,
5857 fact.CUSTOM14,
5858 fact.CUSTOM15,
5859 fact.PLAN_TYPE_CODE /*4471527 */
5860 FROM
5861 pji_fp_xbs_accum_f fact,
5862 pji_pjp_wbs_header head
5863 WHERE
5864 fact.RBS_AGGR_LEVEL = 'L' AND
5865 fact.WBS_ROLLUP_FLAG = 'N' AND
5866 fact.PRG_ROLLUP_FLAG in ('Y', 'N') AND
5867 fact.PROJECT_ID = head.PROJECT_ID AND
5868 fact.PLAN_VERSION_ID = head.PLAN_VERSION_ID AND
5869 fact.PLAN_TYPE_CODE = head.PLAN_TYPE_CODE AND /*4471527 */
5870 decode(fact.PLAN_VERSION_ID,
5871 -3, fact.PLAN_TYPE_ID,
5872 -4, fact.PLAN_TYPE_ID,
5873 -1) = decode(fact.PLAN_VERSION_ID,
5874 -3, head.PLAN_TYPE_ID,
5875 -4, head.PLAN_TYPE_ID,
5876 -1) AND
5877 head.WBS_VERSION_ID = l_wbs_version_id_tbl(j) AND
5878 head.project_id = l_project_id_tbl(j) AND
5879 fact.rbs_version_id = l_rbs_version_id_tbl(k) AND
5880 head.PLAN_VERSION_ID = l_plan_version_id_tbl(j) AND
5881 head.PLAN_TYPE_CODE = l_plan_type_code_tbl(j) ;
5882 END LOOP;/* end of RBS loop*/
5883
5884 IF g_debug_mode='Y' THEN
5885 Pji_Utils.write2log(' Inserted L N N / L N Y slices ' || SQL%ROWCOUNT ,null,3);
5886 end if;
5887
5888 --WBS rollup for LNN / LNY slices
5889 FORALL j IN 1..l_roll_wbs_version_id_tbl.COUNT
5890 INSERT INTO pji_fp_aggr_pjp1_t (
5891 WORKER_ID,
5892 PRG_LEVEL,
5893 PROJECT_ID,
5894 PROJECT_ORG_ID,
5895 PROJECT_ORGANIZATION_ID,
5896 PROJECT_ELEMENT_ID,
5897 TIME_ID,
5898 PERIOD_TYPE_ID,
5899 CALENDAR_TYPE,
5900 RBS_AGGR_LEVEL,
5901 WBS_ROLLUP_FLAG,
5902 PRG_ROLLUP_FLAG,
5903 CURR_RECORD_TYPE_ID,
5904 CURRENCY_CODE,
5905 RBS_ELEMENT_ID,
5906 RBS_VERSION_ID,
5907 PLAN_VERSION_ID,
5908 PLAN_TYPE_ID,
5909 RAW_COST,
5910 BRDN_COST,
5911 REVENUE,
5912 BILL_RAW_COST,
5913 BILL_BRDN_COST,
5914 BILL_LABOR_RAW_COST,
5915 BILL_LABOR_BRDN_COST,
5916 BILL_LABOR_HRS,
5917 EQUIPMENT_RAW_COST,
5918 EQUIPMENT_BRDN_COST,
5919 CAPITALIZABLE_RAW_COST,
5920 CAPITALIZABLE_BRDN_COST,
5921 LABOR_RAW_COST,
5922 LABOR_BRDN_COST,
5923 LABOR_HRS,
5924 LABOR_REVENUE,
5925 EQUIPMENT_HOURS,
5926 BILLABLE_EQUIPMENT_HOURS,
5927 SUP_INV_COMMITTED_COST,
5928 PO_COMMITTED_COST,
5929 PR_COMMITTED_COST,
5930 OTH_COMMITTED_COST,
5931 ACT_LABOR_HRS,
5932 ACT_EQUIP_HRS,
5933 ACT_LABOR_BRDN_COST,
5934 ACT_EQUIP_BRDN_COST,
5935 ACT_BRDN_COST,
5936 ACT_RAW_COST,
5937 ACT_REVENUE,
5938 ACT_LABOR_RAW_COST,
5939 ACT_EQUIP_RAW_COST,
5940 ETC_LABOR_HRS,
5941 ETC_EQUIP_HRS,
5942 ETC_LABOR_BRDN_COST,
5943 ETC_EQUIP_BRDN_COST,
5944 ETC_BRDN_COST,
5945 ETC_RAW_COST,
5946 ETC_LABOR_RAW_COST,
5947 ETC_EQUIP_RAW_COST,
5948 CUSTOM1,
5949 CUSTOM2,
5950 CUSTOM3,
5951 CUSTOM4,
5952 CUSTOM5,
5953 CUSTOM6,
5954 CUSTOM7,
5955 CUSTOM8,
5956 CUSTOM9,
5957 CUSTOM10,
5958 CUSTOM11,
5959 CUSTOM12,
5960 CUSTOM13,
5961 CUSTOM14,
5962 CUSTOM15,
5963 PLAN_TYPE_CODE ) /*4471527 */
5964 SELECT /*+ ORDERED INDEX(XBS PJI_XBS_DENORM_N2) */
5965 1,
5966 0,
5967 fact.PROJECT_ID,
5968 fact.PROJECT_ORG_ID,
5969 fact.PROJECT_ORGANIZATION_ID,
5970 xbs.SUP_EMT_ID,
5971 fact.TIME_ID,
5972 fact.PERIOD_TYPE_ID,
5973 fact.CALENDAR_TYPE,
5974 fact.RBS_AGGR_LEVEL,
5975 'Y', --fact.WBS_ROLLUP_FLAG,
5976 fact.PRG_ROLLUP_FLAG,
5977 fact.CURR_RECORD_TYPE_ID,
5978 fact.CURRENCY_CODE,
5979 fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
5980 fact.RBS_VERSION_ID,
5981 fact.PLAN_VERSION_ID,
5982 fact.PLAN_TYPE_ID,
5983 SUM(fact.RAW_COST),
5984 SUM(fact.BRDN_COST),
5985 SUM(fact.REVENUE),
5986 SUM(fact.BILL_RAW_COST),
5987 SUM(fact.BILL_BRDN_COST),
5988 SUM(fact.BILL_LABOR_RAW_COST),
5989 SUM(fact.BILL_LABOR_BRDN_COST),
5990 SUM(fact.BILL_LABOR_HRS),
5991 SUM(fact.EQUIPMENT_RAW_COST),
5992 SUM(fact.EQUIPMENT_BRDN_COST),
5993 SUM(fact.CAPITALIZABLE_RAW_COST),
5994 SUM(fact.CAPITALIZABLE_BRDN_COST),
5995 SUM(fact.LABOR_RAW_COST),
5996 SUM(fact.LABOR_BRDN_COST),
5997 SUM(fact.LABOR_HRS),
5998 SUM(fact.LABOR_REVENUE),
5999 SUM(fact.EQUIPMENT_HOURS),
6000 SUM(fact.BILLABLE_EQUIPMENT_HOURS),
6001 SUM(fact.SUP_INV_COMMITTED_COST),
6002 SUM(fact.PO_COMMITTED_COST),
6003 SUM(fact.PR_COMMITTED_COST),
6004 SUM(fact.OTH_COMMITTED_COST),
6005 SUM(fact.ACT_LABOR_HRS ),
6006 SUM(fact.ACT_EQUIP_HRS ),
6007 SUM(fact.ACT_LABOR_BRDN_COST ),
6008 SUM(fact.ACT_EQUIP_BRDN_COST ),
6009 SUM(fact.ACT_BRDN_COST ),
6010 SUM(fact.ACT_RAW_COST ),
6011 SUM(fact.ACT_REVENUE ),
6012 SUM(fact.ACT_LABOR_RAW_COST),
6013 SUM(fact.ACT_EQUIP_RAW_COST),
6014 SUM(fact.ETC_LABOR_HRS ),
6015 SUM(fact.ETC_EQUIP_HRS ),
6016 SUM(fact.ETC_LABOR_BRDN_COST ),
6017 SUM(fact.ETC_EQUIP_BRDN_COST ),
6018 SUM(fact.ETC_BRDN_COST ),
6019 SUM(fact.ETC_RAW_COST ),
6020 SUM(fact.ETC_LABOR_RAW_COST),
6021 SUM(fact.ETC_EQUIP_RAW_COST),
6022 SUM(fact.CUSTOM1),
6023 SUM(fact.CUSTOM2),
6024 SUM(fact.CUSTOM3),
6025 SUM(fact.CUSTOM4),
6026 SUM(fact.CUSTOM5),
6027 SUM(fact.CUSTOM6),
6028 SUM(fact.CUSTOM7),
6029 SUM(fact.CUSTOM8),
6030 SUM(fact.CUSTOM9),
6031 SUM(fact.CUSTOM10),
6032 SUM(fact.CUSTOM11),
6033 SUM(fact.CUSTOM12),
6034 SUM(fact.CUSTOM13),
6035 SUM(fact.CUSTOM14),
6036 SUM(fact.CUSTOM15),
6037 fact.PLAN_TYPE_CODE /*4471527 */
6038 FROM
6039 pji_fp_aggr_pjp1_t fact,
6040 pji_pjp_wbs_header head,
6041 pji_xbs_denorm xbs
6042 WHERE
6043 xbs.STRUCT_VERSION_ID = head.WBS_VERSION_ID AND
6044 xbs.STRUCT_TYPE = 'WBS' AND
6045 xbs.SUP_LEVEL <> xbs.SUB_LEVEL AND
6046 xbs.SUB_EMT_ID = fact.PROJECT_ELEMENT_ID AND
6047 fact.PROJECT_ID = head.PROJECT_ID AND
6048 fact.PLAN_VERSION_ID = head.PLAN_VERSION_ID AND
6049 fact.PLAN_TYPE_CODE = head.PLAN_TYPE_CODE AND /*4471527 */
6050 head.WBS_VERSION_ID = l_roll_wbs_version_id_tbl(j) AND
6051 decode(fact.PLAN_VERSION_ID,
6052 -3, fact.PLAN_TYPE_ID,
6053 -4, fact.PLAN_TYPE_ID,
6054 -1) = decode(fact.PLAN_VERSION_ID,
6055 -3, head.PLAN_TYPE_ID,
6056 -4, head.PLAN_TYPE_ID,
6057 -1)
6058 GROUP BY
6059 fact.PROJECT_ID,
6060 fact.PROJECT_ORG_ID,
6061 fact.PROJECT_ORGANIZATION_ID,
6062 xbs.SUP_EMT_ID,
6063 fact.TIME_ID,
6064 fact.PERIOD_TYPE_ID,
6065 fact.CALENDAR_TYPE,
6066 fact.RBS_AGGR_LEVEL,
6067 'Y', --fact.WBS_ROLLUP_FLAG,
6068 fact.PRG_ROLLUP_FLAG,
6069 fact.CURR_RECORD_TYPE_ID,
6070 fact.CURRENCY_CODE,
6071 fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
6072 fact.RBS_VERSION_ID,
6073 fact.PLAN_VERSION_ID,
6074 fact.PLAN_TYPE_ID,
6075 fact.PLAN_TYPE_CODE; /*4471527 */
6076
6077
6078 IF g_debug_mode='Y' THEN
6079 Pji_Utils.write2log(' Inserted WBS rollup L N N / L N Y slices ' || SQL%ROWCOUNT ,null,3);
6080 end if;
6081
6082 --XBS Rollup for WBS slice
6083 FORALL j IN 1..l_roll_wbs_version_id_tbl.COUNT
6084 INSERT INTO pji_fp_aggr_pjp1_t (
6085 WORKER_ID,
6086 PRG_LEVEL,
6087 PROJECT_ID,
6088 PROJECT_ORG_ID,
6089 PROJECT_ORGANIZATION_ID,
6090 PROJECT_ELEMENT_ID,
6091 TIME_ID,
6092 PERIOD_TYPE_ID,
6093 CALENDAR_TYPE,
6094 RBS_AGGR_LEVEL,
6095 WBS_ROLLUP_FLAG,
6096 PRG_ROLLUP_FLAG,
6097 CURR_RECORD_TYPE_ID,
6098 CURRENCY_CODE,
6099 RBS_ELEMENT_ID,
6100 RBS_VERSION_ID,
6101 PLAN_VERSION_ID,
6102 PLAN_TYPE_ID,
6103 RAW_COST,
6104 BRDN_COST,
6105 REVENUE,
6106 BILL_RAW_COST,
6107 BILL_BRDN_COST,
6108 BILL_LABOR_RAW_COST,
6109 BILL_LABOR_BRDN_COST,
6110 BILL_LABOR_HRS,
6111 EQUIPMENT_RAW_COST,
6112 EQUIPMENT_BRDN_COST,
6113 CAPITALIZABLE_RAW_COST,
6114 CAPITALIZABLE_BRDN_COST,
6115 LABOR_RAW_COST,
6116 LABOR_BRDN_COST,
6117 LABOR_HRS,
6118 LABOR_REVENUE,
6119 EQUIPMENT_HOURS,
6120 BILLABLE_EQUIPMENT_HOURS,
6121 SUP_INV_COMMITTED_COST,
6122 PO_COMMITTED_COST,
6123 PR_COMMITTED_COST,
6124 OTH_COMMITTED_COST,
6125 ACT_LABOR_HRS,
6126 ACT_EQUIP_HRS,
6127 ACT_LABOR_BRDN_COST,
6128 ACT_EQUIP_BRDN_COST,
6129 ACT_BRDN_COST,
6130 ACT_RAW_COST,
6131 ACT_REVENUE,
6132 ACT_LABOR_RAW_COST,
6133 ACT_EQUIP_RAW_COST,
6134 ETC_LABOR_HRS,
6135 ETC_EQUIP_HRS,
6136 ETC_LABOR_BRDN_COST,
6137 ETC_EQUIP_BRDN_COST,
6138 ETC_BRDN_COST,
6139 ETC_RAW_COST,
6140 ETC_LABOR_RAW_COST,
6141 ETC_EQUIP_RAW_COST,
6142 CUSTOM1,
6143 CUSTOM2,
6144 CUSTOM3,
6145 CUSTOM4,
6146 CUSTOM5,
6147 CUSTOM6,
6148 CUSTOM7,
6149 CUSTOM8,
6150 CUSTOM9,
6151 CUSTOM10,
6152 CUSTOM11,
6153 CUSTOM12,
6154 CUSTOM13,
6155 CUSTOM14,
6156 CUSTOM15,
6157 PLAN_TYPE_CODE ) /*4471527 */
6158 SELECT /*+ ORDERED INDEX(XBS PJI_XBS_DENORM_N2) */
6159 1,
6160 0,
6161 fact.PROJECT_ID,
6162 fact.PROJECT_ORG_ID,
6163 fact.PROJECT_ORGANIZATION_ID,
6164 xbs.SUP_EMT_ID,
6165 fact.TIME_ID,
6166 fact.PERIOD_TYPE_ID,
6167 fact.CALENDAR_TYPE,
6168 fact.RBS_AGGR_LEVEL,
6169 'Y', --fact.WBS_ROLLUP_FLAG,
6170 fact.PRG_ROLLUP_FLAG,
6171 fact.CURR_RECORD_TYPE_ID,
6172 fact.CURRENCY_CODE,
6173 fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
6174 fact.RBS_VERSION_ID,
6175 fact.PLAN_VERSION_ID,
6176 fact.PLAN_TYPE_ID,
6177 SUM(fact.RAW_COST),
6178 SUM(fact.BRDN_COST),
6179 SUM(fact.REVENUE),
6180 SUM(fact.BILL_RAW_COST),
6181 SUM(fact.BILL_BRDN_COST),
6182 SUM(fact.BILL_LABOR_RAW_COST),
6183 SUM(fact.BILL_LABOR_BRDN_COST),
6184 SUM(fact.BILL_LABOR_HRS),
6185 SUM(fact.EQUIPMENT_RAW_COST),
6186 SUM(fact.EQUIPMENT_BRDN_COST),
6187 SUM(fact.CAPITALIZABLE_RAW_COST),
6188 SUM(fact.CAPITALIZABLE_BRDN_COST),
6189 SUM(fact.LABOR_RAW_COST),
6190 SUM(fact.LABOR_BRDN_COST),
6191 SUM(fact.LABOR_HRS),
6192 SUM(fact.LABOR_REVENUE),
6193 SUM(fact.EQUIPMENT_HOURS),
6194 SUM(fact.BILLABLE_EQUIPMENT_HOURS),
6195 SUM(fact.SUP_INV_COMMITTED_COST),
6196 SUM(fact.PO_COMMITTED_COST),
6197 SUM(fact.PR_COMMITTED_COST),
6198 SUM(fact.OTH_COMMITTED_COST),
6199 SUM(fact.ACT_LABOR_HRS ),
6200 SUM(fact.ACT_EQUIP_HRS ),
6201 SUM(fact.ACT_LABOR_BRDN_COST ),
6202 SUM(fact.ACT_EQUIP_BRDN_COST ),
6203 SUM(fact.ACT_BRDN_COST ),
6204 SUM(fact.ACT_RAW_COST ),
6205 SUM(fact.ACT_REVENUE ),
6206 SUM(fact.ACT_LABOR_RAW_COST),
6207 SUM(fact.ACT_EQUIP_RAW_COST),
6208 SUM(fact.ETC_LABOR_HRS ),
6209 SUM(fact.ETC_EQUIP_HRS ),
6210 SUM(fact.ETC_LABOR_BRDN_COST ),
6211 SUM(fact.ETC_EQUIP_BRDN_COST ),
6212 SUM(fact.ETC_BRDN_COST ),
6213 SUM(fact.ETC_RAW_COST ),
6214 SUM(fact.ETC_LABOR_RAW_COST),
6215 SUM(fact.ETC_EQUIP_RAW_COST),
6216 SUM(fact.CUSTOM1),
6217 SUM(fact.CUSTOM2),
6218 SUM(fact.CUSTOM3),
6219 SUM(fact.CUSTOM4),
6220 SUM(fact.CUSTOM5),
6221 SUM(fact.CUSTOM6),
6222 SUM(fact.CUSTOM7),
6223 SUM(fact.CUSTOM8),
6224 SUM(fact.CUSTOM9),
6225 SUM(fact.CUSTOM10),
6226 SUM(fact.CUSTOM11),
6227 SUM(fact.CUSTOM12),
6228 SUM(fact.CUSTOM13),
6229 SUM(fact.CUSTOM14),
6230 SUM(fact.CUSTOM15),
6231 fact.PLAN_TYPE_CODE /*4471527 */
6232 FROM
6233 pji_fp_aggr_pjp1_t fact,
6234 pji_pjp_wbs_header head,
6235 pji_xbs_denorm xbs
6236
6237 WHERE
6238 xbs.STRUCT_VERSION_ID = head.WBS_VERSION_ID AND
6239 xbs.STRUCT_TYPE = 'XBS' AND
6240 xbs.SUP_LEVEL <> xbs.SUB_LEVEL AND
6241 xbs.SUB_EMT_ID = fact.PROJECT_ELEMENT_ID AND
6242 fact.PROJECT_ID = head.PROJECT_ID AND
6243 fact.PLAN_VERSION_ID = head.PLAN_VERSION_ID AND
6244 fact.PLAN_TYPE_CODE = head.PLAN_TYPE_CODE AND /*4471527 */
6245 head.WBS_VERSION_ID = l_roll_wbs_version_id_tbl(j) AND
6246 decode(fact.PLAN_VERSION_ID,
6247 -3, fact.PLAN_TYPE_ID,
6248 -4, fact.PLAN_TYPE_ID,
6249 -1) = decode(fact.PLAN_VERSION_ID,
6250 -3, head.PLAN_TYPE_ID,
6251 -4, head.PLAN_TYPE_ID,
6252 -1)
6253 GROUP BY
6254 fact.PROJECT_ID,
6255 fact.PROJECT_ORG_ID,
6256 fact.PROJECT_ORGANIZATION_ID,
6257 xbs.SUP_EMT_ID,
6258 fact.TIME_ID,
6259 fact.PERIOD_TYPE_ID,
6260 fact.CALENDAR_TYPE,
6261 fact.RBS_AGGR_LEVEL,
6262 'Y', --fact.WBS_ROLLUP_FLAG,
6263 fact.PRG_ROLLUP_FLAG,
6264 fact.CURR_RECORD_TYPE_ID,
6265 fact.CURRENCY_CODE,
6266 fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
6267 fact.RBS_VERSION_ID,
6268 fact.PLAN_VERSION_ID,
6269 fact.PLAN_TYPE_ID,
6270 fact.PLAN_TYPE_CODE; /*4471527 */
6271
6272
6273
6274 IF g_debug_mode='Y' THEN
6275 Pji_Utils.write2log(' Inserted XBS rollup for WBS slices ' || SQL%ROWCOUNT ,null,3);
6276 end if;
6277 --RBS Rollup for all slices
6278 FORALL j IN 1..l_rbs_version_id_tbl.COUNT
6279 INSERT INTO pji_fp_aggr_pjp1_t (
6280 WORKER_ID,
6281 PRG_LEVEL,
6282 PROJECT_ID,
6283 PROJECT_ORG_ID,
6284 PROJECT_ORGANIZATION_ID,
6285 PROJECT_ELEMENT_ID,
6286 TIME_ID,
6287 PERIOD_TYPE_ID,
6288 CALENDAR_TYPE,
6289 RBS_AGGR_LEVEL,
6290 WBS_ROLLUP_FLAG,
6291 PRG_ROLLUP_FLAG,
6292 CURR_RECORD_TYPE_ID,
6293 CURRENCY_CODE,
6294 RBS_ELEMENT_ID,
6295 RBS_VERSION_ID,
6296 PLAN_VERSION_ID,
6297 PLAN_TYPE_ID,
6298 RAW_COST,
6299 BRDN_COST,
6300 REVENUE,
6301 BILL_RAW_COST,
6302 BILL_BRDN_COST,
6303 BILL_LABOR_RAW_COST,
6304 BILL_LABOR_BRDN_COST,
6305 BILL_LABOR_HRS,
6306 EQUIPMENT_RAW_COST,
6307 EQUIPMENT_BRDN_COST,
6308 CAPITALIZABLE_RAW_COST,
6309 CAPITALIZABLE_BRDN_COST,
6310 LABOR_RAW_COST,
6311 LABOR_BRDN_COST,
6312 LABOR_HRS,
6313 LABOR_REVENUE,
6314 EQUIPMENT_HOURS,
6315 BILLABLE_EQUIPMENT_HOURS,
6316 SUP_INV_COMMITTED_COST,
6317 PO_COMMITTED_COST,
6318 PR_COMMITTED_COST,
6319 OTH_COMMITTED_COST,
6320 ACT_LABOR_HRS,
6321 ACT_EQUIP_HRS,
6322 ACT_LABOR_BRDN_COST,
6323 ACT_EQUIP_BRDN_COST,
6324 ACT_BRDN_COST,
6325 ACT_RAW_COST,
6326 ACT_REVENUE,
6327 ACT_LABOR_RAW_COST,
6328 ACT_EQUIP_RAW_COST,
6329 ETC_LABOR_HRS,
6330 ETC_EQUIP_HRS,
6331 ETC_LABOR_BRDN_COST,
6332 ETC_EQUIP_BRDN_COST,
6333 ETC_BRDN_COST,
6334 ETC_RAW_COST,
6335 ETC_LABOR_RAW_COST,
6336 ETC_EQUIP_RAW_COST,
6337 CUSTOM1,
6338 CUSTOM2,
6339 CUSTOM3,
6340 CUSTOM4,
6341 CUSTOM5,
6342 CUSTOM6,
6343 CUSTOM7,
6344 CUSTOM8,
6345 CUSTOM9,
6346 CUSTOM10,
6347 CUSTOM11,
6348 CUSTOM12,
6349 CUSTOM13,
6350 CUSTOM14,
6351 CUSTOM15,
6352 PLAN_TYPE_CODE ) /*4471527 */
6353 SELECT
6354 1,
6355 0,
6356 fact.PROJECT_ID,
6357 fact.PROJECT_ORG_ID,
6358 fact.PROJECT_ORGANIZATION_ID,
6359 fact.PROJECT_ELEMENT_ID,
6360 fact.TIME_ID,
6361 fact.PERIOD_TYPE_ID,
6362 fact.CALENDAR_TYPE,
6363 'R', --fact.RBS_AGGR_LEVEL,
6364 fact.WBS_ROLLUP_FLAG,
6365 fact.PRG_ROLLUP_FLAG,
6366 fact.CURR_RECORD_TYPE_ID,
6367 fact.CURRENCY_CODE,
6368 rbs.SUP_ID,
6369 fact.RBS_VERSION_ID,
6370 fact.PLAN_VERSION_ID,
6371 fact.PLAN_TYPE_ID,
6372 SUM(fact.RAW_COST),
6373 SUM(fact.BRDN_COST),
6374 SUM(fact.REVENUE),
6375 SUM(fact.BILL_RAW_COST),
6376 SUM(fact.BILL_BRDN_COST),
6377 SUM(fact.BILL_LABOR_RAW_COST),
6378 SUM(fact.BILL_LABOR_BRDN_COST),
6379 SUM(fact.BILL_LABOR_HRS),
6380 SUM(fact.EQUIPMENT_RAW_COST),
6381 SUM(fact.EQUIPMENT_BRDN_COST),
6382 SUM(fact.CAPITALIZABLE_RAW_COST),
6383 SUM(fact.CAPITALIZABLE_BRDN_COST),
6384 SUM(fact.LABOR_RAW_COST),
6385 SUM(fact.LABOR_BRDN_COST),
6386 SUM(fact.LABOR_HRS),
6387 SUM(fact.LABOR_REVENUE),
6388 SUM(fact.EQUIPMENT_HOURS),
6389 SUM(fact.BILLABLE_EQUIPMENT_HOURS),
6390 SUM(fact.SUP_INV_COMMITTED_COST),
6391 SUM(fact.PO_COMMITTED_COST),
6392 SUM(fact.PR_COMMITTED_COST),
6393 SUM(fact.OTH_COMMITTED_COST),
6394 SUM(fact.ACT_LABOR_HRS ),
6395 SUM(fact.ACT_EQUIP_HRS ),
6396 SUM(fact.ACT_LABOR_BRDN_COST ),
6397 SUM(fact.ACT_EQUIP_BRDN_COST ),
6398 SUM(fact.ACT_BRDN_COST ),
6399 SUM(fact.ACT_RAW_COST ),
6400 SUM(fact.ACT_REVENUE ),
6401 SUM(fact.ACT_LABOR_RAW_COST),
6402 SUM(fact.ACT_EQUIP_RAW_COST),
6403 SUM(fact.ETC_LABOR_HRS ),
6404 SUM(fact.ETC_EQUIP_HRS ),
6405 SUM(fact.ETC_LABOR_BRDN_COST ),
6406 SUM(fact.ETC_EQUIP_BRDN_COST ),
6407 SUM(fact.ETC_BRDN_COST ),
6408 SUM(fact.ETC_RAW_COST ),
6409 SUM(fact.ETC_LABOR_RAW_COST),
6410 SUM(fact.ETC_EQUIP_RAW_COST),
6411 SUM(fact.CUSTOM1),
6412 SUM(fact.CUSTOM2),
6413 SUM(fact.CUSTOM3),
6414 SUM(fact.CUSTOM4),
6415 SUM(fact.CUSTOM5),
6416 SUM(fact.CUSTOM6),
6417 SUM(fact.CUSTOM7),
6418 SUM(fact.CUSTOM8),
6419 SUM(fact.CUSTOM9),
6420 SUM(fact.CUSTOM10),
6421 SUM(fact.CUSTOM11),
6422 SUM(fact.CUSTOM12),
6423 SUM(fact.CUSTOM13),
6424 SUM(fact.CUSTOM14),
6425 SUM(fact.CUSTOM15),
6426 fact.PLAN_TYPE_CODE /*4471527 */
6427 FROM
6428 pji_fp_aggr_pjp1_t fact,
6429 pji_rbs_denorm rbs
6430 WHERE
6431 rbs.STRUCT_VERSION_ID = l_rbs_version_id_tbl(j) AND
6432 rbs.SUP_LEVEL <> rbs.SUB_LEVEL AND
6433 rbs.SUB_ID = fact.RBS_ELEMENT_ID
6434 GROUP BY
6435 fact.PROJECT_ID,
6436 fact.PROJECT_ORG_ID,
6437 fact.PROJECT_ORGANIZATION_ID,
6438 fact.PROJECT_ELEMENT_ID,--xbs.SUB_ELEMENT_ID,
6439 fact.TIME_ID,
6440 fact.PERIOD_TYPE_ID,
6441 fact.CALENDAR_TYPE,
6442 'R',
6443 fact.WBS_ROLLUP_FLAG,
6444 fact.PRG_ROLLUP_FLAG,
6445 fact.CURR_RECORD_TYPE_ID,
6446 fact.CURRENCY_CODE,
6447 rbs.SUP_ID,
6448 fact.RBS_VERSION_ID,
6449 fact.PLAN_VERSION_ID,
6450 fact.PLAN_TYPE_ID,
6451 fact.PLAN_TYPE_CODE;
6452
6453 IF g_debug_mode='Y' THEN
6454 Pji_Utils.write2log(' Inserted RBS rollup for all slices ' || SQL%ROWCOUNT ,null,3);
6455 end if;
6456
6457 Pji_Fm_Plan_Maint_T_Pvt.MERGE_INTO_FP_FACT;
6458
6459 IF p_commit = 'Y' THEN
6460 COMMIT;
6461 END IF;
6462
6463 IF g_debug_mode='Y' THEN
6464 Pji_Utils.write2log(' maintain smart slice .. 0003 ' ,null,3);
6465 end if;
6466
6467 EXCEPTION
6468 WHEN Invalid_Excep THEN
6469 x_msg_data := 'unexcepted error';
6470 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
6471 ( p_package_name => g_package_name
6472 , p_procedure_name => 'MAINTAIN_SMART_SLICE'
6473 , x_return_status => x_return_status ) ;
6474
6475 WHEN OTHERS THEN
6476 x_msg_data := SQLERRM;
6477 ROLLBACK;
6478
6479 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
6480 ( p_package_name => g_package_name
6481 , p_procedure_name => 'MAINTAIN_SMART_SLICE'
6482 , x_return_status => x_return_status ) ;
6483
6484 RAISE;
6485 END;
6486
6487
6488 PROCEDURE CLEANUP_TEMP_TABLES IS
6489 l_return_status VARCHAR2(1);
6490 BEGIN
6491
6492 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
6493 ( p_package_name => g_package_name
6494 , x_return_status => l_return_status );
6495
6496 DELETE FROM pji_fp_aggr_pjp1_t;
6497 DELETE FROM PJI_FM_EXTR_PLNVER3_T;
6498 DELETE FROM pji_fp_rmap_fpr_update_t;
6499 EXCEPTION
6500 WHEN OTHERS THEN
6501 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
6502 ( p_package_name => g_package_name
6503 , p_procedure_name => 'CLEANUP_TEMP_TABLES'
6504 , x_return_status => l_return_status ) ;
6505
6506 RAISE;
6507 END;
6508
6509
6510 --
6511 -- Checks if list of plan versions exist in budget versions table.
6512 --
6513 PROCEDURE CHECK_BUDGET_VERSION_EXISTS (
6514 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type() ) IS
6515 i NUMBER;
6516 l_project_id NUMBER;
6517 l_return_status VARCHAR2(1);
6518 BEGIN
6519
6520 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
6521 ( p_package_name => g_package_name
6522 , x_return_status => l_return_status );
6523
6524 print_time ( ' inside CHECK_BUDGET_VERSION_EXISTS ' ) ;
6525 FOR i IN p_fp_version_ids.FIRST..p_fp_version_ids.LAST LOOP
6526
6527 BEGIN
6528
6529 SELECT PROJECT_ID
6530 INTO l_project_id
6531 FROM pa_budget_versions
6532 WHERE budget_version_id = p_fp_version_ids(i);
6533
6534 print_time ( ' plan version id # ' || i || ' is ' || p_fp_version_ids(i) || ' exists in budget versions.');
6535
6536 EXCEPTION
6537 WHEN OTHERS THEN
6538 print_time ( ' plan version id # ' || i || ' is ' || p_fp_version_ids(i) || ' does not exist in budget versions.');
6539 END;
6540
6541 BEGIN
6542
6543 SELECT /*+ index_ffs(wbs_hdr PJI_PJP_WBS_HEADER_N1) */ PROJECT_ID
6544 INTO l_project_id
6545 FROM pji_pjp_wbs_header wbs_hdr
6546 WHERE plan_version_id = p_fp_version_ids(i);
6547
6548 print_time ( ' plan version id # ' || i || ' is ' || p_fp_version_ids(i) || ' exists in budget versions.');
6549
6550 EXCEPTION
6551 WHEN OTHERS THEN
6552 print_time ( ' plan version id # ' || i || ' is ' || p_fp_version_ids(i) || ' does not exist in budget versions.');
6553 END;
6554
6555 END LOOP;
6556
6557 EXCEPTION
6558 WHEN OTHERS THEN
6559 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
6560 ( p_package_name => g_package_name
6561 , p_procedure_name => 'CHECK_BUDGET_VERSION_EXISTS'
6562 , x_return_status => l_return_status ) ;
6563
6564 RAISE;
6565 END;
6566
6567
6568 ----------
6569 -- Prints the list of plan versions in a given table of plan versions.
6570 ----------
6571 PROCEDURE PRINT_PLAN_VERSION_ID_LIST
6572 ( p_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type() ) IS
6573 i NUMBER;
6574 l_return_status VARCHAR2(1);
6575 BEGIN
6576
6577 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
6578 ( p_package_name => g_package_name
6579 , x_return_status => l_return_status );
6580
6581 print_time ( ' There are ' || p_fp_version_ids.COUNT || ' plan versions.');
6582 FOR i IN p_fp_version_ids.FIRST..p_fp_version_ids.LAST LOOP
6583 print_time ( ' .... Plan version ' || i || ' is ' || p_fp_version_ids(i));
6584 END LOOP;
6585
6586 EXCEPTION
6587 WHEN OTHERS THEN
6588 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
6589 ( p_package_name => g_package_name
6590 , p_procedure_name => 'PRINT_PLAN_VERSION_ID_LIST'
6591 , x_return_status => l_return_status ) ;
6592
6593 RAISE;
6594 END;
6595
6596
6597 ----------
6598 -- Prints the list of plan versions in a given table of plan versions.
6599 ----------
6600 PROCEDURE PRINT_PLAN_VERSION_TYPE_LIST
6601 ( p_fp_version_types IN SYSTEM.pa_varchar2_30_tbl_type ) IS
6602 i NUMBER;
6603 l_return_status VARCHAR2(1);
6604 BEGIN
6605
6606 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
6607 ( p_package_name => g_package_name
6608 , x_return_status => l_return_status );
6609
6610 print_time ( ' There are ' || p_fp_version_types.COUNT || ' plan versions.');
6611 FOR i IN p_fp_version_types.FIRST..p_fp_version_types.LAST LOOP
6612 print_time ( ' .... Plan version ' || i || ' is ' || p_fp_version_types(i));
6613 END LOOP;
6614
6615 EXCEPTION
6616 WHEN OTHERS THEN
6617 Pji_Pjp_Fp_Curr_Wrap.EXCP_HANDLER
6618 ( p_package_name => g_package_name
6619 , p_procedure_name => 'PRINT_PLAN_VERSION_TYPE_LIST'
6620 , x_return_status => l_return_status ) ;
6621
6622 RAISE;
6623 END;
6624
6625 -----------------------------------------------------------------
6626 --This API inserts rows for handling ETC calculations in apply
6627 --progress flow for version disabled workplan structures
6628 --If the global variable PA_PROGRESS_PUB.G_WBS_APPLY_PROG
6629 --is set, it means plan_update API is being called in apply
6630 --flow for version disabled workplan structure
6631 -----------------------------------------------------------------
6632 PROCEDURE INSERT_APPLY_PROG_VD IS
6633 BEGIN
6634
6635 IF PA_PROGRESS_PUB.G_WBS_APPLY_PROG IS NOT NULL THEN
6636
6637 --------------------------------------------------------
6638 --If the ETC column is null and the PLAN column has some
6639 --value, we copy the plan value to the ETC columns
6640 --The 0 rows for ETC in this case will ensure that in the
6641 --incremental change in plan value is not copied to the
6642 --ETC columns
6643 --------------------------------------------------------
6644
6645 INSERT INTO pji_fm_extr_plan_lines (
6646 PROJECT_ID, PROJECT_ORG_ID, PROJECT_ELEMENT_ID, STRUCT_VER_ID,
6647 PERIOD_NAME, CALENDAR_TYPE, START_DATE, END_DATE, RBS_ELEMENT_ID,
6648 RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, WP_VERSION_FLAG,
6649 ROLLUP_TYPE, TXN_CURRENCY_CODE, PRJ_CURRENCY_CODE, PFC_CURRENCY_CODE,
6650 RESOURCE_CLASS_CODE, RATE_BASED_FLAG,
6651 ETC_PFC_BURDENED_COST,ETC_PFC_RAW_COST, ETC_PRJ_BURDENED_COST,
6652 ETC_PRJ_RAW_COST, ETC_QUANTITY, ETC_TXN_BURDENED_COST, ETC_TXN_RAW_COST )
6653 SELECT
6654 PROJECT_ID, PROJECT_ORG_ID, PROJECT_ELEMENT_ID, STRUCT_VER_ID,
6655 PERIOD_NAME, CALENDAR_TYPE, START_DATE, END_DATE, RBS_ELEMENT_ID,
6656 RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, WP_VERSION_FLAG,
6657 ROLLUP_TYPE, TXN_CURRENCY_CODE, PRJ_CURRENCY_CODE, PFC_CURRENCY_CODE,
6658 RESOURCE_CLASS_CODE, RATE_BASED_FLAG,
6659 0, 0, 0,
6660 0, 0, 0, 0
6661 FROM
6662 pji_fm_extr_plan_lines
6663 WHERE
6664 STRUCT_VER_ID = PA_PROGRESS_PUB.G_WBS_APPLY_PROG
6665 GROUP BY
6666 PROJECT_ID, PROJECT_ORG_ID, PROJECT_ELEMENT_ID, STRUCT_VER_ID,
6667 PERIOD_NAME, CALENDAR_TYPE, START_DATE, END_DATE, RBS_ELEMENT_ID,
6668 RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, WP_VERSION_FLAG,
6669 ROLLUP_TYPE, TXN_CURRENCY_CODE, PRJ_CURRENCY_CODE, PFC_CURRENCY_CODE,
6670 RESOURCE_CLASS_CODE, RATE_BASED_FLAG;
6671
6672 END IF;
6673
6674 EXCEPTION
6675 WHEN OTHERS THEN
6676 null;
6677 END;
6678
6679 ----------
6680 -- Print time API to measure time taken by each api. Also useful for debugging.
6681 ----------
6682 PROCEDURE PRINT_TIME(p_tag IN VARCHAR2) IS
6683 BEGIN
6684 Pji_Pjp_Fp_Curr_Wrap.print_time(p_tag);
6685 EXCEPTION
6686 WHEN OTHERS THEN
6687 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => g_package_name ,
6688 p_procedure_name => 'PRINT_TIME');
6689 -- RAISE;
6690 END;
6691
6692
6693 END Pji_Fm_Xbs_Accum_Maint;