[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_PLAN_MAINT
Source
1 PACKAGE BODY PJI_FM_PLAN_MAINT AS
2 /* $Header: PJIPP01B.pls 120.37.12010000.3 2009/02/02 16:23:50 kmaddi ship $ */
3
4
5 g_package_name VARCHAR2(100) := 'PJI_FM_PLAN_MAINT';
6
7 g_worker_id NUMBER := PJI_PJP_FP_CURR_WRAP.GET_WORKER_ID; -- NULL;
8 g_event_rec PA_PJI_PROJ_EVENTS_LOG%ROWTYPE;
9 g_process_update_wbs_flag VARCHAR2(1) := 'N' ;
10
11
12 -- TYPE plan_version_id_rec IS RECORD (plan_version_id pa_budget_versions.budget_version_id%TYPE);
13
14 -- TYPE c_plnverid_cur IS REF CURSOR RETURN plan_version_id_rec;
15
16 -- FUNCTION get_plan_version_ids_cursor ( p_context VARCHAR2 ) RETURN c_plnverid_cur;
17
18 --
19 -- Populate wbs denorm table for plan create case.
20 --
21 PROCEDURE POPULATE_WBS_DENORM(
22 p_online IN VARCHAR2 := 'Y'
23 , p_tar_denorm_not_exists_tbl IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
24 , x_return_status OUT NOCOPY VARCHAR2
25 );
26
27
28 --
29 -- With data in ver3 table and pjp1 tables,
30 -- create rollup slices for pri slice and insert into RL fact.
31 --
32 PROCEDURE PRI_SLICE_CREATE_T;
33
34 --
35 -- Contains apis to set online context to perform WBS rollups
36 -- if WBS changes (changes to structure) have been made.
37 --
38 PROCEDURE WBSCHANGEPROC_PRERLP_SETUPS;
39
40 --
41 -- Contains cleanup logic after performing WBS rollups if WBS
42 -- changes (changes to structure) have been made.
43 --
44 PROCEDURE WBSCHANGEPROC_POSTRLP_CLEANUP;
45
46 --
47 -- With data in ver3 table and pjp1 tables,
48 -- create rollup slices for secondary slice and insert into RL fact.
49 --
50 PROCEDURE SEC_SLICE_CREATE_T;
51
52 PROCEDURE CHECK_PRIMARY_ALREADY_EXISTS (
53 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
54 , p_is_primary_rbs IN VARCHAR2
55 , x_return_status OUT NOCOPY VARCHAR2 );
56
57 --
58 -- Performance fix: To avoid re-populating pa/pji denorm tables if
59 -- denorm definition already exists, function checks if denorm definition
60 -- exists.
61 --
62 PROCEDURE DENORM_DEFINITION_EXISTS(
63 p_tar_wbs_version_id_tbl IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
64 , x_tar_denorm_not_exists_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
65 );
66
67 --
68 -- Performance tuning and debug api.
69 --
70 PROCEDURE PRINT_TIME(p_tag IN VARCHAR2);
71
72 ------------------------------------------------------------------
73 ------------------------------------------------------------------
74 -- Private Apis Implementation --
75 ------------------------------------------------------------------
76 ------------------------------------------------------------------
77
78
79 PROCEDURE MAP_RESOURCE_LIST (
80 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
81 , p_context IN VARCHAR2 := 'BULK') IS
82
83 l_return_status VARCHAR2(1) := NULL;
84 l_msg_count NUMBER := NULL;
85 l_msg_data VARCHAR2(300) := NULL;
86
87 i NUMBER(15) := NULL;
88 j NUMBER(15) := NULL;
89
90 CURSOR c_res_list_id_bulk_cur (p_budget_version_id IN NUMBER) IS
91 SELECT rpa.rbs_version_id, bv.plan_version_id
92 FROM pa_rbs_prj_assignments rpa
93 , PJI_FM_EXTR_PLNVER4 bv
94 WHERE 1 = 1
95 AND bv.project_id = rpa.project_id
96 AND bv.rbs_struct_version_id <> rpa.rbs_version_id
97 AND bv.plan_version_id = p_budget_version_id
98 AND bv.worker_id = g_worker_id;
99
100 CURSOR c_res_list_id_online_cur (p_budget_version_id IN NUMBER) IS
101 SELECT rpa.rbs_version_id, bv.plan_version_id
102 FROM pa_rbs_prj_assignments rpa
103 , PJI_FM_EXTR_PLNVER3_T bv
104 WHERE 1 = 1
105 AND bv.project_id = rpa.project_id
106 AND bv.rbs_struct_version_id <> rpa.rbs_version_id
107 AND bv.plan_version_id = p_budget_version_id;
108
109 BEGIN
110
111 print_time(' MAP_RESOURCE_LIST : begin.. ');
112
113 FOR i IN p_fp_version_ids.FIRST..p_fp_version_ids.LAST LOOP
114
115 print_time( ' 1 ' );
116
117 IF (p_context = 'ONLINE') THEN
118
119 print_time( ' 2 ' );
120
121 IF c_res_list_id_online_cur%ISOPEN THEN CLOSE c_res_list_id_online_cur; END IF;
122
123 print_time( ' 3 ' );
124
125 FOR j IN c_res_list_id_online_cur(p_fp_version_ids(i)) LOOP
126
127 print_time( ' 4 i = ' || i );
128
129 BEGIN
130
131 print_time( ' 5 ' );
132
133 PA_RLMI_RBS_MAP_PUB.populate_rbsmap_tmp
134 ( p_budget_version_id => j.plan_version_id
135 , p_calling_mode => 'BUDGET_VERSION'
136 , x_return_status => l_return_status );
137
138 print_time( ' 6 ' );
139
140 PA_RBS_MAPPING.map_rbs_plans (
141 p_rbs_version_id => j.rbs_version_id
142 , x_return_status => l_return_status
143 , x_msg_count => l_msg_count
144 , x_msg_data => l_msg_data
145 ) ;
146
147 print_time( ' 7 ' );
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 print_time( ' 8 ' );
152 RAISE;
153 END;
154
155 print_time(' l_return_status ' || l_return_status || ' l_msg_count ' || l_msg_count || ' l_msg_data ' || l_msg_data);
156
157 END LOOP;
158
159 IF c_res_list_id_online_cur%ISOPEN THEN CLOSE c_res_list_id_online_cur; END IF;
160
161 ELSIF (p_context = 'BULK') THEN
162
163 IF c_res_list_id_bulk_cur%ISOPEN THEN CLOSE c_res_list_id_bulk_cur; END IF;
164
165 FOR j IN c_res_list_id_bulk_cur(p_fp_version_ids(i)) LOOP
166
167 BEGIN
168 PA_RLMI_RBS_MAP_PUB.populate_rbsmap_tmp
169 ( p_budget_version_id => j.plan_version_id
170 , p_calling_mode => 'BUDGET_VERSION'
171 , x_return_status => l_return_status );
172
173 PA_RBS_MAPPING.map_rbs_plans (
174 p_rbs_version_id => j.rbs_version_id
175 , x_return_status => l_return_status
176 , x_msg_count => l_msg_count
177 , x_msg_data => l_msg_data
178 ) ;
179 EXCEPTION
180 WHEN OTHERS THEN
181 RAISE;
182 END;
183
184 print_time(' l_return_status ' || l_return_status || ' l_msg_count ' || l_msg_count || ' l_msg_data ' || l_msg_data);
185
186 END LOOP;
187
188 IF c_res_list_id_bulk_cur%ISOPEN THEN CLOSE c_res_list_id_bulk_cur; END IF;
189
190 END IF;
191
192 END LOOP;
193
194 IF c_res_list_id_bulk_cur%ISOPEN THEN CLOSE c_res_list_id_bulk_cur; END IF;
195
196 print_time(' MAP_RESOURCE_LIST : end.. ');
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 IF c_res_list_id_bulk_cur%ISOPEN THEN CLOSE c_res_list_id_bulk_cur; END IF;
201 IF c_res_list_id_online_cur%ISOPEN THEN CLOSE c_res_list_id_online_cur; END IF;
202 print_time(' MAP_RESOURCE_LIST : exception.. ' || sqlerrm);
203 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
204 p_procedure_name => 'MAP_RESOURCE_LIST');
205 RAISE;
206 END;
207
208
209
210
211
212 --
213 -- FPM Upgrade script new, using bulk apis..
214 -- Will create Plan data primary slice summaries for all plan data.
215 --
216 PROCEDURE CREATE_PRIMARY_UPGRD_PVT
217 (p_context IN VARCHAR2 := 'FPM_UPGRADE' -- Valid values: 'FPM_UPGRADE' and 'TRUNCATE'
218 ) IS
219
220 c_upgr_proc_name VARCHAR2(30) := 'PJI_FPM_UPGRADE';
221 l_fpm_upgr_status VARCHAR2(100) := NULL;
222 l_worker_id NUMBER;
223 l_process VARCHAR2(30);
224 l_extr_start_date DATE;
225 l_count NUMBER := 10;
226 v1 VARCHAR2(80);
227 v2 VARCHAR2(80);
228 l_pa_schema varchar2(30);
229 l_pji_schema varchar2(30);
230 l_degree number;
231
232 BEGIN
233
234
235 l_count := 1;
236 print_time('FPM Upgr x ' || l_count);
237
238
239 IF (p_context NOT IN ('FPM_UPGRADE', 'TRUNCATE')) THEN
240 print_time('CREATE_PRIMARY_UPGRD_PVT : Invalid p_context ' || p_context );
241 RETURN;
242 END IF;
243
244
245 --
246 -- TB deleted after testing.
247 --
248 -- fnd_profile.put('PJI_SUM_CLEANALL', 'Y');
249 -- pji_pjp_extraction_utils.truncate_pjp_tables(v1,v2,'Y');
250 -- Pji_utils.set_parameter('PJI_FPM_UPGRADE', NULL);
251
252
253 --
254 -- Initialize variables..
255 --
256 -- For FPM upgrade script process, worker id is 1.
257 -- l_process value is 'PJI_EXTR1'
258 -- l_extr_start_date value is based on PJI_GLOBAL_START_DATE_OVERRIDE if its
259 -- profile option value is not null, else it is BIS_GLOBAL_START_DATE.
260 -- Gets the FPM upgrade process status.
261 -- Valid values are P (partial), C (complete).
262
263 l_worker_id := 1;
264 l_process := PJI_PJP_SUM_MAIN.g_process || l_worker_id;
265 l_extr_start_date := PJI_UTILS.GET_EXTRACTION_START_DATE;
266 l_fpm_upgr_status := Pji_utils.get_parameter(c_upgr_proc_name);
267 l_count := l_count + 1;
268 print_time('FPM Upgr y ' || l_count || ' l_fpm_upgr_status ' || l_fpm_upgr_status );
269
270
271 --------------------------------------------------------------------------------
272 -- If FPM upgrade has already been run successfully ('C'),
273 -- then don't start it again.
274 -- If not, mark it as partially complete ('P') during call to initialize api.
275 --------------------------------------------------------------------------------
276
277 IF (l_fpm_upgr_status = 'C') THEN
278 l_count := l_count + 1;
279 print_time('FPM Upgr c1 ' || l_count);
280 RETURN;
281 ELSIF (l_fpm_upgr_status = 'P') THEN
282 l_count := l_count + 1;
283 print_time('FPM Upgr c2 ' || l_count);
284 NULL;
285 ELSIF (l_fpm_upgr_status IS NULL) THEN
286 l_count := l_count + 1;
287 print_time('FPM Upgr c3 ' || l_count);
288 -- Description of this api is given below.
289 PJI_FM_XBS_ACCUM_UTILS.FPM_UPGRADE_INITIALIZE;
290 print_time('FPM Upgr c4 ' || l_count );
291 END IF;
292
293
294 --
295 -- PJI_FM_XBS_ACCUM_UTILS.FPM_UPGRADE_INITIALIZE;
296 --
297 --
298 -- The call to the PJI_FM_XBS_ACCUM_UTILS.FPM_UPGRADE_INITIALIZE api below does the following:
299 --
300 --
301 -- Update pji system settings table and system parameter CONFIG_PROJ_PERF_FLAG to 'Y'.
302 -- Set parameter PJI_EXTR1$EXTRACTION_TYPE to FULL.
303 -- Set parameter PJI_EXTR1$PROCESS_RUNNING to Y.
304 -- Set PJI_PJP_EXTRACTION_UTILS to 1.
305 -- Store PJP summarization state by
306 -- a. inserting a row in PJI_SYSTEM_CONFIG_HIST with process name (PJI_EXTR1) and
307 -- extraction type (FULL) with system date as start date.
308 -- b. inserting rows in PJI_PJP_PROJ_BATCH_MAP with worker id and project id information.
309 -- Set values of system parameters PA_CALENDAR_FLAG and GL_CALENDAR_FLAG with that in pji_system_settings table.
310 -- Commit these changes.
311 --
312
313
314 l_count := l_count + 1;
315 print_time('FPM Upgr z1 ' || l_count);
316
317
318 -- Truncate PJI_MT_PRC_STEPS.
319 -- Insert data into PJI_MT_PRC_STEPS with foll info.
320 -- a. Step seq 10, process name 'PJI_EXTR', step name 'PJI_FM_SUM_MAIN.INIT_PROCESS', step type 'GENERIC'.
321 -- b. Step seq 20, process name 'PJI_EXTR', step name 'PJI_FM_SUM_MAIN.RUN_PROCESS', step type 'GENERIC'.
322 -- c. Step seq 30, process name 'PJI_EXTR', step name 'PJI_FM_SUM_MAIN.WRAPUP_PROCESS', step type 'GENERIC'.
323 -- d. Step seq 40, process name 'PJI_EXTR', step name 'PJI_FM_SUM_EXTR.GET_NEXT_BATCH(p_worker_id)', step type 'GENERIC'.
324 --
325 --
326 PJI_PROCESS_UTIL.REFRESH_STEP_TABLE;
327 l_count := l_count + 1;
328 print_time('FPM Upgr 1.1 ' || l_count);
329
330 -- ... register the 14 following steps into PJI_MT_PRC_STEPS/PJI_SYSTEM_PRC_STATUS.
331 PJI_PROCESS_UTIL.ADD_STEPS(l_process, 'PJI_PJP_FPM_UPGRADE', 'FULL');
332 l_count := l_count + 1;
333 print_time('FPM Upgr 1.2 ' || l_count);
334
335 --
336 -- 0. ... Remap txn accum headers with new rbs element ids.
337 --
338 IF (p_context = 'TRUNCATE') THEN
339 PJI_PJP_SUM_ROLLUP.REMAP_RBS_TXN_ACCUM_HDRS(l_worker_id);
340 PJI_PJP_SUM_DENORM.POPULATE_RBS_DENORM(l_worker_id, 'ALL', NULL);
341 PJI_PJP_SUM_ROLLUP.UPDATE_RBS_DENORM(l_worker_id);
342 END IF;
343
344 --
345 -- 1. ... populate pji_time% tables.
346 --
347 PJI_PJP_SUM_ROLLUP.POPULATE_TIME_DIMENSION(l_worker_id);
348 l_count := l_count + 1;
349 print_time('FPM Upgr a ' || l_count);
350
351 --
352 -- populate pji org extr info table with pa/gl cal info for orgs.
353 --
354 PJI_PJP_EXTRACTION_UTILS.POPULATE_ORG_EXTR_INFO;
355 l_count := l_count + 1;
356 print_time('FPM Upgr z2 ' || l_count);
357
358
359 --
360 -- 2. ... populate pa xbs denorm table for wbs/programs.
361 --
362
363 l_count := l_count + 1;
364 print_time('FPM Upgr b ' || l_count);
365
366 PJI_PJP_SUM_DENORM.POPULATE_XBS_DENORM(
367 p_worker_id => l_worker_id
368 , p_denorm_type => 'ALL'
369 , p_wbs_version_id => NULL
370 , p_prg_group1 => NULL
371 , p_prg_group2 => NULL);
372
373 --#bug 5356051
374 l_pa_schema := PJI_UTILS.GET_PA_SCHEMA_NAME;
375 l_degree := PJI_UTILS.GET_DEGREE_OF_PARALLELISM();
376
377 FND_STATS.GATHER_TABLE_STATS(
378 ownname => l_pa_schema
379 , tabname => 'PA_XBS_DENORM'
380 , percent => 10
381 , degree => l_degree
382 );
383
384 FND_STATS.GATHER_INDEX_STATS(ownname => l_pa_schema,
385 indname => 'PA_XBS_DENORM_N1',
386 percent => 10);
387
388 FND_STATS.GATHER_INDEX_STATS(ownname => l_pa_schema,
389 indname => 'PA_XBS_DENORM_N2',
390 percent => 10);
391
392 FND_STATS.GATHER_INDEX_STATS(ownname => l_pa_schema,
393 indname => 'PA_XBS_DENORM_N3',
394 percent => 10);
395
396 --#bug 5356051
397 --
398 -- 3. ... populate pji xbs denorm table for wbs/programs.
399 --
400 PJI_PJP_SUM_ROLLUP.UPDATE_XBS_DENORM_FULL(l_worker_id);
401 l_count := 31;
402 print_time('FPM Upgr c ' || l_count);
403
404 --#bug 5356051
405 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
406
407 FND_STATS.GATHER_TABLE_STATS(
408 ownname => l_pji_schema
409 , tabname => 'PJI_XBS_DENORM'
410 , percent => 10
411 , degree => l_degree
412 );
413
414 FND_STATS.GATHER_INDEX_STATS(ownname => l_pji_schema,
415 indname => 'PJI_XBS_DENORM_N1',
416 percent => 10);
417
418 FND_STATS.GATHER_INDEX_STATS(ownname => l_pji_schema,
419 indname => 'PJI_XBS_DENORM_N2',
420 percent => 10);
421
422 FND_STATS.GATHER_INDEX_STATS(ownname => l_pji_schema,
423 indname => 'PJI_XBS_DENORM_N3',
424 percent => 10);
425
426 --#bug 5356051
427
428 --
429 -- 4. ... cache plan version related info like time phased code type (pa/gl cal),
430 -- whether it is workplan/financial plan, is it current original/baselined
431 -- baselined version, etc in ver3.
432 --
433 PJI_PJP_SUM_ROLLUP.EXTRACT_FIN_PLAN_VERS_BULK(l_worker_id);
434 l_count := l_count + 1;
435 print_time('FPM Upgr d ' || l_count);
436 -- PRINT_TBL_SIZE;
437
438 --
439 -- 5. ... populate the wbs header table.
440 --
441 PJI_PJP_SUM_ROLLUP.POPULATE_WBS_HDR(l_worker_id);
442 l_count := l_count + 1;
443 print_time('FPM Upgr e ' || l_count);
444
445
446 --
447 -- 6. ... populate the rbs header table.
448 --
449 PJI_PJP_SUM_ROLLUP.POPULATE_RBS_HDR(l_worker_id);
450 l_count := l_count + 1;
451 print_time('FPM Upgr f ' || l_count);
452
453 --
454 -- 7. ... extract the plan amounts from budget lines table into pjp1.
455 --
456
457 IF (p_context = 'TRUNCATE') THEN
458
459 -- Extract plan lines.
460 PJI_PJP_SUM_ROLLUP.RETRIEVE_OVERRIDDEN_WP_ETC(l_worker_id);
461 l_count := l_count + 1;
462 -- print_time('FPM Upgr g ' || l_count);
463
464 -- Extract plans and etc (=plan).
465 PJI_PJP_SUM_ROLLUP.EXTRACT_PLAN_ETC_PRIRBS(l_worker_id);
466 l_count := l_count + 1;
467 print_time('FPM Upgr g ' || l_count);
468
469 -- Delete plan lines.
470 -- PJI_PJP_SUM_ROLLUP.DELETE_PLAN_LINES(l_worker_id);
471 -- l_count := l_count + 1;
472 -- print_time('FPM Upgr g ' || l_count);
473
474 ELSIF (p_context = 'FPM_UPGRADE') THEN
475
476 PJI_PJP_SUM_ROLLUP.EXTRACT_PLAN_AMOUNTS_PRIRBS(l_worker_id);
477 l_count := l_count + 1;
478 print_time('FPM Upgr g ' || l_count);
479
480 END IF;
481
482
483 --
484 -- 9. ... update the wbs header table.
485 --
486 PJI_PJP_SUM_ROLLUP.UPDATE_WBS_HDR(l_worker_id);
487 l_count := l_count + 1;
488 print_time('FPM Upgr e ' || l_count);
489
490
491 --
492 -- 11. ... perform time rollup for 'PA' calendar and insert data into pjp1.
493 --
494 PJI_PJP_SUM_ROLLUP.CREATE_FP_PA_PRI_ROLLUP(l_worker_id);
495 l_count := l_count + 1;
496 print_time('FPM Upgr k ' || l_count);
497
498 --
499 -- 12. ... perform time rollup for 'GL' calendar and insert data into pjp1.
500 --
501 PJI_PJP_SUM_ROLLUP.CREATE_FP_GL_PRI_ROLLUP(l_worker_id);
502 l_count := l_count + 1;
503 print_time('FPM Upgr l ' || l_count);
504 -- PRINT_TBL_SIZE;
505
506 --
507 -- 12.1 ... perform 2048 slice time rollup for PA and GL calendarS and insert data into pjp1.
508 --
509 PJI_PJP_SUM_ROLLUP.CREATE_FP_ALL_PRI_ROLLUP(l_worker_id);
510 l_count := l_count + 1;
511 print_time('FPM Upgr l ' || l_count);
512 -- PRINT_TBL_SIZE;
513
514
515 --
516 -- 10. ... perform rbs rollup for 'T' slice alone and insert data into pjp1.
517 --
518 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_T_SLICE(l_worker_id);
519 l_count := 50;
520 l_count := l_count + 1;
521 print_time('FPM Upgr j ' || l_count);
522
523 --
524 -- 9. ... perform wbs rollup and insert data into pjp1.
525 --
526 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_WBS(l_worker_id);
527 l_count := l_count + 1;
528 print_time('FPM Upgr i ' || l_count);
529 -- PRINT_TBL_SIZE;
530
531
532 --
533 -- 13. ... insert data in pjp1 into pji_fp_xbs_accum_f.
534 --
535 PJI_PJP_SUM_ROLLUP.INSERT_INTO_FP_FACT(l_worker_id);
536 l_count := l_count + 1;
537 print_time('FPM Upgr ' || l_count);
538
539 --
540 -- 14. ... mark all the extracted financial plan versions as 'P' and work plans as 'Y'.
541 --
542 PJI_PJP_SUM_ROLLUP.MARK_EXTRACTED_PLANS(l_worker_id);
543 l_count := l_count + 1;
544 print_time('FPM Upgr ' || l_count);
545
546
547 --
548 -- ... call the FPM upgrade script end api. This does..
549 -- a0. Truncate pjp1 tables and insert rcds in PJI_SYSTEM_PRC_STATUS into
550 -- PJI_SYSTEM_PRC_HIST for process PJI_EXTR1.
551 -- a1. FPM upgrade script run is Complete, set system parameter PJI_FPM_UPGRADE as C.
552 -- a2. Insert records existing in PJI_SYSTEM_PRC_STATUS into PJI_SYSTEM_PRC_HIST.
553 -- b. Delete records in PJI_SYSTEM_PRC_STATUS for process PJI_EXTR.
554 -- c. Delete records in PJI_SYSTEM_PARAMETERS for process PJI_EXTR1.
555 -- d. Set end date on PJI_SYSTEM_CONFIG_HIST for this process.
556 -- e. Set system parameter PJP_FPM_UPGRADE_DATE to sysdate.
557 -- f. Delete data from PJI_PJP_PROJ_BATCH_MAP for worker id l_worker_id = 1.
558 --
559
560 PJI_FM_XBS_ACCUM_UTILS.FPM_UPGRADE_END;
561 l_count := l_count + 1;
562 print_time('FPM Upgr ' || l_count);
563
564 COMMIT;
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 print_time ( ' FPM upgrade script... exception ' || SQLERRM);
569 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
570 p_procedure_name => 'CREATE_PRIMARY_UPGRD_PVT');
571 ROLLBACK; l_count := l_count + 1; print_time('FPM Upgr ' || l_count || ' Exception happened ' || SQLERRM );
572 RAISE;
573 -- RAISE_APPLICATION_ERROR(-20001, SQLERRM);
574
575 END;
576
577
578 --
579 -- Will create Plan data summaries for all plan data.
580 -- p_is_primary_rbs = 'T' will be used when a plan is baselined.
581 -- Processing for this condition should be moved to another api.
582 --
583 -- CREATE_PRIMARY_PVT : ****internal only**** summarization api.
584 -- p_fp_version_ids -> table of destination plan version ids.
585 -- p_is_primary_rbs -> only 'T' is supported.
586 -- p_commit -> only 'F' is supported.
587 -- p_fp_src_version_ids -> table of source plan version ids.
588 -- p_copy_mode -> If NULL, denorm table is created based on task relationships
589 -- If NOT NULL, denorm table is created based denorm copy of source structure
590 --
591 PROCEDURE CREATE_PRIMARY_PVT(
592 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
593 , p_is_primary_rbs IN VARCHAR2 -- Valid values are T/F.
594 , p_commit IN VARCHAR2 := 'F'
595 , p_fp_src_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
596 , p_Copy_mode IN VARCHAR2 :=NULL
597 ) IS
598
599 CURSOR c_src_struct_version_id (
600 c_fp_src_version_id pa_budget_versions.budget_version_id%TYPE
601 ) IS
602 (
603 SELECT DECODE ( src.wp_version_flag
604 , 'Y'
605 , src.project_structure_version_id
606 , Pa_Project_Structure_Utils.GET_FIN_STRUC_VER_ID(src.project_id)
607 ) src_struct_version_id
608 FROM pa_budget_versions src
609 WHERE src.budget_version_id = c_fp_src_version_id
610 );
611
612 CURSOR c_dest_struct_version_id (
613 c_fp_tar_version_id pa_budget_versions.budget_version_id%TYPE
614 ) IS
615 (
616 SELECT DECODE ( tar.wp_version_flag
617 , 'Y'
618 , tar.project_structure_version_id
619 , Pa_Project_Structure_Utils.GET_FIN_STRUC_VER_ID(tar.project_id)
620 ) tar_struct_version_id
621 FROM pa_budget_versions tar
622 WHERE tar.budget_version_id = c_fp_tar_version_id
623 );
624
625 l_wbs_src_struct_version_id pa_budget_versions.project_structure_version_id%TYPE;
626 l_wbs_tar_struct_version_id pa_budget_versions.project_structure_version_id%TYPE;
627 l_return_status VARCHAR2(1) ;
628
629 -- Avoid re-populating pa/pji denorm tables if denorm definition already exists.
630 l_tar_denorm_exists_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
631
632 l_wbs_tar_wvi_valid_tbl SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
633 l_src_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
634 l_tar_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
635 l_tar_denorm_not_exists_tbl SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
636
637 l_src_pln_ver_id NUMBER;
638
639 BEGIN
640
641 print_time('CREATE_PRIMARY_PVT 2 : begin.. ');
642
643 FOR i IN 1..p_fp_version_ids.COUNT LOOP
644 print_time ( 'p_fp_version_ids(i) ' || p_fp_version_ids(i));
645 END LOOP;
646
647 FOR i IN 1..p_fp_src_version_ids.COUNT LOOP
648 print_time ( 'p_fp_src_version_ids(i) ' || p_fp_src_version_ids(i));
649 END LOOP;
650
651 print_time(' p_is_pri_rbs ' || p_is_primary_rbs);
652 print_time(' p_Copy_mode ' || p_copy_mode );
653
654 print_time('CREATE_PRIMARY_PVT 2 : l_count is ... ' || p_fp_version_ids.COUNT );
655
656 pji_pjp_fp_curr_wrap.set_table_stats('PJI','PJI_FM_EXTR_PLNVER3_T',10,10,10); -- Seeding the table Ver3_t
657
658 IF (p_is_primary_rbs NOT IN ('T', 'F')) THEN
659 RETURN;
660 END IF;
661
662
663 CHECK_PRIMARY_ALREADY_EXISTS (
664 p_fp_version_ids => p_fp_version_ids
665 , p_is_primary_rbs => p_is_primary_rbs
666 , x_return_status => l_return_status );
667
668 -------------------------------------------------------------------------
669 -- Preparing for extraction and extracting from budget lines.
670 -------------------------------------------------------------------------
671
672 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES; print_time(' 3 ..1 ');
673
674
675 IF (p_is_primary_rbs = 'T') THEN
676
677 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(
678 p_fp_version_ids => p_fp_version_ids
679 , p_slice_type => 'PRI');
680
681 ELSE
682
683 NULL; -- Sec RBSes are extracted in create sec pvt.
684
685 END IF;
686
687 IF (CHECK_VER3_NOT_EMPTY(p_online => 'T') = 'F') THEN
688 RETURN;
689 END IF;
690
691 PJI_FM_PLAN_MAINT_T_PVT.MAP_ORG_CAL_INFO('N');
692 print_time(' 2.. Update org/cal tables. ');
693
694
695 print_time(' ************** Denorm processing begin ************** ');
696 print_time(' ***************************************************** ');
697
698 print_time(' p_Copy_mode ' || p_copy_mode );
699
700 -- Loop for all destination versions.
701 FOR i IN 1..p_fp_version_ids.COUNT LOOP
702
703 print_time(' i loop start' || i ) ; -- : dest plan version id is.. ' || p_fp_version_ids(i));
704
705 -- Padding source plan version id with null wbs version id for destination
706 -- plans with non-existent source. For these plans wbs denorm can only be created
707 -- and not copied. This is for copy=NULL mode only (create the wbs denorm using
708 -- the wbs assigned to this plan version).
709
710 IF (i > p_fp_src_version_ids.COUNT OR p_fp_src_version_ids(i) IS NULL ) THEN
711 l_src_pln_ver_id := NULL;
712 l_src_wbs_version_id_tbl.EXTEND;
713 l_src_wbs_version_id_tbl(l_src_wbs_version_id_tbl.COUNT) := NULL;
714 print_time(' There is no corresponding source WBS version id.' ) ;
715 ELSE
716
717 FOR j IN c_src_struct_version_id(p_fp_src_version_ids(i)) LOOP
718 l_src_wbs_version_id_tbl.EXTEND;
719 l_src_wbs_version_id_tbl(l_src_wbs_version_id_tbl.COUNT) := j.src_struct_version_id;
720 print_time(' source wbs version id ' || l_src_wbs_version_id_tbl(l_src_wbs_version_id_tbl.COUNT) );
721 END LOOP;
722
723 END IF;
724
725 FOR j IN c_dest_struct_version_id(p_fp_version_ids(i)) LOOP
726 l_tar_wbs_version_id_tbl.EXTEND;
727 l_tar_wbs_version_id_tbl(l_tar_wbs_version_id_tbl.COUNT) := j.tar_struct_version_id;
728 print_time(' Destination wbs version id = ' || j.tar_struct_version_id);
729 END LOOP;
730
731 print_time(' i loop end ');
732
733 END LOOP;
734
735
736 IF p_copy_mode IS NULL THEN
737
738 DENORM_DEFINITION_EXISTS(
739 p_tar_wbs_version_id_tbl => l_tar_wbs_version_id_tbl
740 , x_tar_denorm_not_exists_tbl => l_tar_denorm_not_exists_tbl
741 );
742
743 POPULATE_WBS_DENORM(
744 p_online => 'Y'
745 , p_tar_denorm_not_exists_tbl => l_tar_denorm_not_exists_tbl
746 , x_return_status => l_return_status );
747
748 ELSE
749
750 -- this will be called only if its Copy Mode is not null i,e either Project(P) or Version(V)
751
752 FOR i IN p_fp_src_version_ids.first..p_fp_src_version_ids.last LOOP
753
754 print_time(' ----------- Begin denorm copy..' );
755
756 Pji_Pjp_Sum_Denorm.copy_xbs_denorm(
757 p_worker_id => 1 ,
758 p_wbs_version_id_from => l_src_wbs_version_id_tbl(i),
759 p_wbs_version_id_to => l_tar_wbs_version_id_tbl(i),
760 p_copy_mode => p_copy_mode
761 );
762
763 print_time(' ----------- End denorm copy..' );
764
765 END LOOP;
766
767 END IF;
768 -- end of for Copy populate startegy change
769
770 print_time(' ***************************************************** ');
771 print_time(' ************** Denorm processing end **************** ');
772
773 --Introduced below api call for bug 7187487
774 --PJI_FM_PLAN_MAINT_T_PVT.GET_GLOBAL_EXCHANGE_RATES; -- Bug 7681331
775
776 IF (p_is_primary_rbs = 'T') THEN
777 --Introduced below api call for bug 7187487
778 -- PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_PLAN_AMTS_PRIRBS_GLC12; --Bug 7681331
779 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_PLAN_AMOUNTS_PRIRBS;
780 print_time(' 5.1 .. ');
781
782 ELSE
783
784 MAP_RESOURCE_LIST (p_fp_version_ids);
785 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_PLAN_AMOUNTS_SECRBS; print_time(' 5.2 .. ');
786
787 END IF;
788
789 -- With data in ver3 table and pjp1 tables,
790 -- create rollup slices and insert into RL fact.
791 -- Mark extracted versions, clean up the interim tables and insert into FP RL fact.
792
793
794 PRI_SLICE_CREATE_T; print_time(' 6 .. ');
795 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
796 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT; print_time(' 14 .. ');
797
798 PJI_FM_PLAN_MAINT_T_PVT.MARK_EXTRACTED_PLANS('PRI'); print_time(' 15 .. ');
799
800
801 IF (p_commit = 'T') THEN
802 COMMIT;
803 END IF;
804
805 print_time('CREATE_PRIMARY_PVT 2 : end.. ');
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 IF c_src_struct_version_id%ISOPEN THEN
810 CLOSE c_src_struct_version_id;
811 END IF;
812
813 IF c_dest_struct_version_id%ISOPEN THEN
814 CLOSE c_dest_struct_version_id;
815 END IF;
816
817 print_time('CREATE_PRIMARY_PVT 2 : exception ' || SQLERRM);
818 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
819 p_procedure_name => 'CREATE_PRIMARY_PVT');
820 RAISE;
821 END;
822
823
824 PROCEDURE CREATE_SECONDARY_PVT(
825 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
826 , p_commit IN VARCHAR2 := 'F'
827 , p_process_all IN VARCHAR2 := 'F') IS
828
829 l_count NUMBER;
830 l_return_status VARCHAR2(1);
831
832 BEGIN
833
834 print_time('CREATE_SECONDARY_PVT: begin.. ');
835
836
837 Pji_Fm_Plan_Maint_Pvt.EXTRACT_FIN_PLAN_VERS_BULK(p_slice_type => 'SEC_PROJ');
838 Pji_Fm_Plan_Maint_Pvt.EXTRACT_FIN_PLAN_VERS_BULK(p_slice_type => 'SECRBS_PROJ');
839 print_time(' Identified plan versions to be extracted. ');
840
841 IF (CHECK_VER3_NOT_EMPTY(p_online => 'F') = 'F') THEN
842 print_time(' No data in ver3, returning. ');
843 RETURN;
844 END IF;
845
846 Pji_Fm_Plan_Maint_Pvt.GET_GLOBAL_EXCHANGE_RATES;
847 print_time(' Got global exchange rates. ');
848
849 Pji_Fm_Plan_Maint_Pvt.POPULATE_RBS_HDR;
850 print_time('Populated RBS header table.');
851
852 Pji_Fm_Plan_Maint_Pvt.POPULATE_WBS_HDR;
853 print_time('Populated WBS header table.');
854
855 Pji_Fm_Plan_Maint_Pvt.EXTRACT_PLAN_AMTS_PRIRBS_GLC12;
856 print_time(' Extracted plan data for primary RBSes.');
857
858 Pji_Fm_Plan_Maint_Pvt.EXTRACT_PLAN_AMTS_SECRBS_GLC12;
859 print_time(' Extracted plan data for primary RBSes.');
860
861 Pji_Fm_Plan_Maint_Pvt.DELETE_GLOBAL_EXCHANGE_RATES;
862 print_time(' Deleted global exchange rates. ');
863
864 Pji_Fm_Plan_Maint_Pvt.PRORATE_TO_ALL_CALENDARS;
865 print_time(' Prorated to all calendars. ');
866
867 PJI_FM_PLAN_MAINT_PVT.UPDATE_WBS_HDR;
868 print_time(' Updated the WBS header table with min max txn dates.');
869
870 Pji_Fm_Plan_Maint_Pvt.EXTRACT_DANGL_REVERSAL;
871 print_time(' Inserted Reversal records for dangling plans.');
872
873 Pji_Fm_Plan_Maint_Pvt.MARK_EXTRACTED_PLANS('SEC');
874 print_time('Marked dangling versions.. ');
875 -- Back from unlock headers because at the time of dangling its not marking
876 -- due to purge before unlock headers bug 5155692
877
878 print_time('CREATE_SECONDARY_PVT: end. successful.. ');
879
880 EXCEPTION
881 WHEN OTHERS THEN
882 print_time('CREATE_SECONDARY_PVT: exception. '|| SQLERRM);
883 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
884 p_procedure_name => 'CREATE_SECONDARY_PVT');
885 RAISE;
886 END;
887
888
889 PROCEDURE CREATE_SECONDARY_T_PVT(
890 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
891 , p_commit IN VARCHAR2 := 'F'
892 , p_process_all IN VARCHAR2 := 'F') IS
893
894 l_return_status VARCHAR2(1);
895
896 BEGIN
897
898 print_time('CREATE_SECONDARY_T_PVT: begin.. ');
899
900 -------------------------------------------------------------------------
901 -- Re-extract pri/sec rbs data with amounts converted to Global1 and
902 -- Global2 currencies also.
903 -------------------------------------------------------------------------
904
905 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES; -- Clean up interim tables.
906 print_time(' Cleaned up interim tables. ');
907
908 print_time(' !!!!!!!!!!!! Begin reextraction !!!!!!!! ');
909
910
911 IF (p_process_all = 'F') THEN
912 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(p_fp_version_ids, 'PRI');
913 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(p_fp_version_ids, 'SECRBS');
914 -- PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(p_fp_version_ids, '-3-4');
915 ELSE
916 print_time(' Incorrect parameters to CREATE_SECONDARY_T_PVT: p_process_all = ' || p_process_all );
917 RETURN;
918 END IF;
919 print_time(' Populated ver3 table. ');
920
921 IF (CHECK_VER3_NOT_EMPTY(p_online => 'T') = 'F') THEN
922 print_time(' No data in ver3, returning. ');
923 RETURN;
924 END IF;
925
926 PJI_FM_PLAN_MAINT_T_PVT.MAP_ORG_CAL_INFO('N') ;
927 print_time(' Updated calendar and org tables. ');
928
929 PJI_FM_PLAN_MAINT_T_PVT.GET_GLOBAL_EXCHANGE_RATES;
930 print_time(' Got global exchange rates. ');
931
932 SEC_SLICE_CREATE_T;
933 print_time('Proration done and rollups created.');
934
935 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
936
937 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT;
938 print_time('Merged reversed data.');
939
940 PJI_FM_PLAN_MAINT_T_PVT.MARK_EXTRACTED_PLANS('SEC');
941 print_time('Marked ');
942
943 PJI_FM_PLAN_MAINT_T_PVT.DELETE_GLOBAL_EXCHANGE_RATES;
944 print_time(' Deleted global exchange rates. ');
945
946 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES; -- Clean up interim tables.
947
948 IF (p_commit = 'T') THEN
949 COMMIT;
950 END IF;
951
952 print_time('CREATE_SECONDARY_T_PVT: end. successful.. ');
953
954 EXCEPTION
955 WHEN OTHERS THEN
956 print_time('CREATE_SECONDARY_T_PVT: exception. ');
957 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
958 p_procedure_name => 'CREATE_SECONDARY_T_PVT');
959 RAISE;
960 END;
961
962
963 PROCEDURE UPDATE_PRIMARY_PVT (
964 p_plan_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
965 -- p_plan_version_id IN NUMBER := NULL
966 , p_commit IN VARCHAR2 := 'F'
967 )
968 IS
969 -- l_plan_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(p_plan_version_id);
970 BEGIN
971
972 print_time('UPDATE_PRIMARY_PVT: begin.. ');
973
974 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(
975 p_fp_version_ids => p_plan_version_ids
976 , p_slice_type => 'PRI');
977
978 print_time ( ' 1.1 ');
979
980 IF (CHECK_VER3_NOT_EMPTY(p_online => 'T') = 'F') THEN
981 RETURN;
982 END IF;
983
984 WBSCHANGEPROC_PRERLP_SETUPS;
985 print_time(' Setups before calling WBS rollup code. ');
986
987 PJI_FM_PLAN_MAINT_T_PVT.MAP_ORG_CAL_INFO('N');
988 print_time(' 1.a3 Updated org/cal tables. ');
989
990 PJI_FM_PLAN_MAINT_T_PVT.RETRIEVE_DELTA_SLICE; print_time ( ' 2 ');
991 print_time(' 1.a4 Extracted data in plan lines. ');
992
993
994 -- With data in ver3 table and pjp1 tables,
995 -- create rollup slices for primary slice.
996 -- Mark extracted versions, clean up the interim tables and insert into FP RL fact.
997
998 PRI_SLICE_CREATE_T;
999 print_time ( ' 3 PRI_SLICE_CREATE_T ');
1000 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
1001 WBSCHANGEPROC_POSTRLP_CLEANUP;
1002 print_time(' Cleanup after calling WBS rollup code. ');
1003
1004 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT;
1005 print_time(' Merged into FP fact. ');
1006
1007 PJI_FM_PLAN_MAINT_T_PVT.MARK_EXTRACTED_PLANS('PRI');
1008 print_time(' Marked extracted plans ');
1009
1010 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES;
1011 print_time(' Cleaned up interim tables.');
1012
1013 print_time('UPDATE_PRIMARY_PVT: end. successful.. ');
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 print_time('UPDATE_PRIMARY_PVT: exception.. ');
1018 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1019 p_procedure_name => 'UPDATE_PRIMARY_PVT');
1020 RAISE;
1021 END;
1022
1023
1024 ------------------------------------------------------------------------------------------
1025 -- When this api is called during publish flow, budgeting api has already created plan
1026 -- and default etc (=plan) data for new published version through update api.
1027 -- Data exists for etc values in plan lines.
1028 ------------------------------------------------------------------------------------------
1029 PROCEDURE UPDATE_PRIMARY_PVT_ACT_ETC (
1030 p_commit IN VARCHAR2 := 'F'
1031 , p_plan_version_id IN NUMBER := NULL
1032 , p_prev_pub_version_id IN NUMBER := NULL
1033 , x_return_status OUT NOCOPY VARCHAR2
1034 , x_processing_code OUT NOCOPY VARCHAR2) IS
1035
1036 l_fp_version_ids SYSTEM.pa_num_tbl_type;
1037
1038 BEGIN
1039
1040 print_time ( 'UPDATE_PRIMARY_PVT_ACT_ETC: begin.. ');
1041 print_time ( 'UPDATE_PRIMARY_PVT_ACT_ETC: p_plan_version_id ' || p_plan_version_id );
1042 print_time ( ' p_prev_pub_version_id ' || p_prev_pub_version_id );
1043
1044 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1045 ( p_package_name => g_package_name
1046 , x_return_status => x_return_status );
1047
1048
1049 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES; -- Clean up interim tables.
1050
1051 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_PLN_VER_TABLE; print_time ( ' 1 ');
1052
1053 -- WBSCHANGEPROC_PRERLP_SETUPS;
1054 -- print_time(' Setups before calling WBS rollup code. ');
1055
1056 PJI_FM_PLAN_MAINT_T_PVT.REVERSE_ETC(
1057 p_new_pub_version_id => p_plan_version_id
1058 , p_prev_pub_version_id => p_prev_pub_version_id ) ; print_time ( ' 0.2 ');
1059
1060 PRI_SLICE_CREATE_T; print_time ( ' 3 ');
1061 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
1062 -- WBSCHANGEPROC_POSTRLP_CLEANUP;
1063 -- print_time(' Cleanup after calling WBS rollup code. ');
1064
1065 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT; print_time(' 4 .. ');
1066
1067 print_time('UPDATE_PRIMARY_PVT_ACT_ETC: end. successful.. ');
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1072 ( p_package_name => g_package_name
1073 , p_procedure_name => 'UPDATE_PRIMARY_PVT_ACT_ETC'
1074 , x_return_status => x_return_status ) ;
1075
1076 RAISE;
1077 END;
1078
1079
1080 PROCEDURE DELETE_ALL_PVT (
1081 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1082 , p_commit IN VARCHAR2 := 'F') IS
1083
1084 l_project_ids SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
1085 l_wbs_version_ids SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
1086 l_wp_flags SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
1087
1088 BEGIN
1089
1090 print_time('DELETE_ALL_PVT: begin.. ');
1091
1092 l_project_ids.EXTEND(p_fp_version_ids.COUNT);
1093 l_wbs_version_ids.EXTEND(p_fp_version_ids.COUNT);
1094 l_wp_flags.EXTEND(p_fp_version_ids.COUNT);
1095
1096 FOR i IN 1..p_fp_version_ids.COUNT LOOP
1097 BEGIN --bug#4100852
1098 SELECT /*+ index(wbs_hdr PJI_PJP_WBS_HEADER_N1) */ project_id,wbs_version_id, wp_flag
1099 INTO l_project_ids(i),l_wbs_version_ids(i),l_wp_flags(i)
1100 FROM PJI_PJP_WBS_HEADER wbs_hdr
1101 WHERE plan_version_id = p_fp_version_ids(i);
1102
1103 print_time ( ' i = ' || i || ' p_fp_version_ids(i) = ' || p_fp_version_ids(i) || ' l_project_ids(i) ' || l_project_ids(i)
1104 ||' l_wbs_version_id(i)= '||l_wbs_version_ids(i) ||' l_wp_flag(i) = '||l_wp_flags(i) );
1105 EXCEPTION
1106 WHEN NO_DATA_FOUND THEN
1107 NULL;
1108 END;
1109 END LOOP;
1110
1111 FORALL i IN 1..p_fp_version_ids.COUNT
1112 DELETE FROM PJI_FP_XBS_ACCUM_F
1113 WHERE plan_version_id = p_fp_version_ids(i)
1114 AND project_id = l_project_ids(i);
1115
1116 print_time ( 'deleted from fact. # rows = ' || SQL%ROWCOUNT ) ;
1117
1118 FORALL i IN 1..p_fp_version_ids.COUNT
1119 DELETE FROM PJI_PJP_WBS_HEADER
1120 WHERE plan_version_id = p_fp_version_ids(i)
1121 AND project_id = l_project_ids(i);
1122
1123 print_time ( 'deleted from wbs hdr table. # rows = ' || SQL%ROWCOUNT ) ;
1124
1125 FORALL i IN 1..p_fp_version_ids.COUNT
1126 DELETE FROM PJI_PJP_RBS_HEADER
1127 WHERE plan_version_id = p_fp_version_ids(i)
1128 AND project_id = l_project_ids(i);
1129
1130 print_time ( 'deleted from rbs header. # rows = ' || SQL%ROWCOUNT ) ;
1131
1132 FORALL i IN 1..p_fp_version_ids.COUNT
1133 DELETE FROM PJI_ROLLUP_LEVEL_STATUS
1134 WHERE plan_version_id = p_fp_version_ids(i);
1135
1136 print_time ( 'deleted from rollup level status. # rows = ' || SQL%ROWCOUNT ) ;
1137
1138 FORALL i IN 1..p_fp_version_ids.COUNT
1139 DELETE FROM PA_XBS_DENORM
1140 WHERE sup_project_id = l_project_ids(i)
1141 AND l_wp_flags(i) = 'Y'
1142 AND ( struct_version_id = l_wbs_version_ids(i)
1143 OR (struct_type = 'PRG' AND sup_id = l_wbs_version_ids(i) AND sub_id = l_wbs_version_ids(i)));
1144
1145 print_time ( 'deleted from pa_xbs_denorm table. # rows = ' || SQL%ROWCOUNT ) ;
1146
1147 FORALL i IN 1..p_fp_version_ids.COUNT
1148 DELETE FROM PJI_XBS_DENORM
1149 WHERE sup_project_id = l_project_ids(i)
1150 AND l_wp_flags(i) = 'Y'
1151 AND ( struct_version_id = l_wbs_version_ids(i)
1152 OR (struct_type = 'PRG' AND sup_id = l_wbs_version_ids(i) AND sub_id = l_wbs_version_ids(i)));
1153
1154 print_time ( 'deleted from pji_xbs_denorm table. # rows = ' || SQL%ROWCOUNT ) ;
1155
1156 IF (p_commit = 'T') THEN COMMIT; END IF;
1157
1158 print_time('DELETE_ALL_PVT: end.. successful.. ');
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 print_time('DELETE_ALL_PVT: exception.. ');
1163 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1164 p_procedure_name => 'DELETE_ALL_PVT');
1165 RAISE;
1166 END;
1167
1168
1169 --
1170 -- Summarization API for common processing between various online flows.
1171 -- With data in ver3 table and pjp1 tables,
1172 -- create rollup slices and insert into RL fact.
1173 --
1174 PROCEDURE PRI_SLICE_CREATE_T IS
1175 BEGIN
1176
1177 --------------------------------
1178 -- Header table population.
1179 --------------------------------
1180 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_RBS_HDR;
1181 print_time('Populated new records into RBS Header Table.');
1182
1183 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_WBS_HDR;
1184 print_time('Populated new records into WBS Header Table.');
1185
1186 --------------------------------
1187 -- Time rollups.
1188 --------------------------------
1189
1190 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_PA_PRI_ROLLUP(p_honor_rbs => 'N');
1191 print_time('PA calendar rollups done.');
1192
1193 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_GL_PRI_ROLLUP(p_honor_rbs => 'N');
1194 print_time('GL calendar rollups done.');
1195
1196 -- PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ALL_T_PRI_ROLLUP(p_honor_rbs => 'N'); /* Bug 4604617 */
1197 print_time('All time calendar rollups done 1.');
1198
1199 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ALL_T_PRI_ROLLUP(
1200 p_honor_rbs => 'N'
1201 , p_calendar_type => 'C'); /* For Time phase None Bug 4604617 */
1202 print_time('All time calendar rollups done 2.'); /* Added for bug 3871783 */
1203
1204 --------------------------------
1205 -- RBS/WBS/Program rollups.
1206 --------------------------------
1207
1208 PJI_FM_PLAN_MAINT_T_PVT.ROLLUP_FPR_RBS_T_SLICE;
1209 print_time('RBS rollup done.');
1210
1211 PJI_FM_PLAN_MAINT_T_PVT.CREATE_WBSRLP;
1212 print_time('WBS rollup done.');
1213
1214 PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
1215 print_time('Updated the WBS header table with min max txn dates.');
1216
1217 EXCEPTION
1218 WHEN OTHERS THEN
1219 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1220 p_procedure_name => 'PRI_SLICE_CREATE_T');
1221 RAISE;
1222 END;
1223
1224
1225
1226 --
1227 -- With data in ver3 table and pjp1 tables,
1228 -- create rollup slices and insert into RL fact.
1229 --
1230 PROCEDURE SEC_SLICE_CREATE_T IS
1231 BEGIN
1232
1233 --------------------------------
1234 -- Header table population.
1235 --------------------------------
1236
1237 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_RBS_HDR;
1238 print_time('Populated new records into RBS Header Table.');
1239
1240 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_WBS_HDR;
1241 print_time('Populated new records into WBS Header Table.');
1242
1243 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_PLAN_AMTS_PRIRBS_GLC12;
1244 print_time(' Extracted plan data for primary RBSes.');
1245
1246 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_PLAN_AMTS_SECRBS_GLC12;
1247 print_time(' Extracted plan data for secondary RBSes.');
1248
1249 --
1250 -- Prorate
1251 -- 1. GL cal and Non time phased entries into PA cal.
1252 -- 2. PA cal and Non time phased entries into GL cal.
1253 -- 3. PA, GL cals and Non time phased entries into Ent cal.
1254 --
1255 PJI_FM_PLAN_MAINT_T_PVT.PRORATE_TO_ALL_CALENDARS;
1256 print_time(' Prorated to all calendars. ');
1257
1258
1259 --------------------------------
1260 -- Time rollups.
1261 --------------------------------
1262
1263 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_PA_PRI_ROLLUP;
1264 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_GL_PRI_ROLLUP;
1265 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ENT_ROLLUP;
1266 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ALL_T_PRI_ROLLUP;
1267 print_time(' Time rollups done. ');
1268
1269
1270 --------------------------------
1271 -- RBS/WBS/Program rollups.
1272 --------------------------------
1273
1274 PJI_FM_PLAN_MAINT_T_PVT.ROLLUP_FPR_RBS_T_SLICE;
1275 print_time(' RBS rollups done. ');
1276
1277 PJI_FM_PLAN_MAINT_T_PVT.CREATE_WBSRLP;
1278 print_time(' WBS rollups done. ');
1279
1280 PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
1281 print_time('Updated the WBS header table with min max txn dates.');
1282
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 print_time(' SEC_SLICE_CREATE_T exception ' || SQLERRM );
1286 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1287 p_procedure_name => 'SEC_SLICE_CREATE_T');
1288 RAISE;
1289 END;
1290
1291
1292 PROCEDURE CHECK_PRIMARY_ALREADY_EXISTS (
1293 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1294 , p_is_primary_rbs IN VARCHAR2
1295 , x_return_status OUT NOCOPY VARCHAR2 ) IS
1296 i NUMBER;
1297 l_project_id NUMBER;
1298 excp_plan_already_summarized EXCEPTION;
1299 PRAGMA EXCEPTION_INIT(excp_plan_already_summarized, -1422);
1300 BEGIN
1301
1302 x_return_status := FND_API.G_RET_STS_SUCCESS;
1303
1304 FOR i IN p_fp_version_ids.FIRST..p_fp_version_ids.LAST LOOP
1305
1306 print_time('plan version id.. ' || p_fp_version_ids(i) );
1307
1308 BEGIN
1309
1310 SELECT /*+ index_ffs(wbs_hdr PJI_PJP_WBS_HEADER_N1) */
1311 DISTINCT PROJECT_ID
1312 INTO l_project_id
1313 FROM pji_pjp_wbs_header wbs_hdr
1314 WHERE plan_version_id = p_fp_version_ids(i);
1315
1316 IF (p_is_primary_rbs = 'T') THEN
1317 RAISE excp_plan_already_summarized;
1318 END IF;
1319
1320 EXCEPTION
1321 WHEN NO_DATA_FOUND
1322 THEN NULL;
1323 END;
1324
1325 END LOOP;
1326
1327 EXCEPTION
1328 WHEN excp_plan_already_summarized THEN
1329 x_return_status := FND_API.G_RET_STS_ERROR;
1330 print_time(' CHECK_PRIMARY_ALREADY_EXISTS exception ' || SQLERRM );
1331 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1332 p_procedure_name => 'CHECK_PRIMARY_ALREADY_EXISTS');
1333 RAISE;
1334
1335 WHEN OTHERS THEN
1336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1337 print_time(' SEC_SLICE_CREATE_T exception ' || SQLERRM );
1338 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1339 p_procedure_name => 'CHECK_PRIMARY_ALREADY_EXISTS');
1340 RAISE;
1341 END;
1342
1343 --
1344 -- Performance fix: To avoid re-populating pa/pji denorm tables if
1345 -- denorm definition already exists, function checks if denorm definition
1346 -- exists.
1347 --
1348 PROCEDURE DENORM_DEFINITION_EXISTS(
1349 p_tar_wbs_version_id_tbl IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1350 , x_tar_denorm_not_exists_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
1351 ) IS
1352
1353 CURSOR c_wbs_vers_exist IS
1354 SELECT DISTINCT wbs_struct_version_id
1355 FROM pji_fm_extr_plnver3_t ver3
1356 WHERE EXISTS (
1357 SELECT /*+ index(pxd PA_XBS_DENORM_N1) */ 1
1358 FROM pa_xbs_denorm pxd
1359 WHERE 1=1
1360 AND struct_version_id IS NULL
1361 AND sup_id = ver3.wbs_struct_version_id
1362 );
1363
1364 l_tar_wbs_ver_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1365 l_tar_denorm_not_exists_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1366 l_tar_wbs_ver_id1 NUMBER;
1367 l_tar_wbs_ver_id2 NUMBER;
1368
1369 BEGIN
1370
1371 print_time(' begin list of all dest wbs version ids.. ' );
1372 FOR i IN 1..p_tar_wbs_version_id_tbl.COUNT LOOP
1373 print_time(' i = ' || i || ' ' || p_tar_wbs_version_id_tbl(i));
1374 END LOOP;
1375 print_time(' end list of all dest wbs version ids.. ' );
1376
1377
1378 IF (c_wbs_vers_exist%isopen) THEN
1379 CLOSE c_wbs_vers_exist;
1380 END IF;
1381
1382 -- l_tar_wbs_ver_id_tbl.EXTEND(p_tar_wbs_version_id_tbl.COUNT);
1383
1384 OPEN c_wbs_vers_exist;
1385 FETCH c_wbs_vers_exist
1386 BULK COLLECT
1387 INTO l_tar_wbs_ver_id_tbl;
1388 CLOSE c_wbs_vers_exist;
1389
1390 print_time(' begin list of wbs version ids with denorm already populated.. ' );
1391 FOR i IN 1..l_tar_wbs_ver_id_tbl.COUNT LOOP
1392 print_time(' i = ' || i || ' ' || l_tar_wbs_ver_id_tbl(i));
1393 END LOOP;
1394 print_time(' end list of wbs version ids with denorm already populated.. ' );
1395
1396 print_time( ' p_tar_wbs_version_id_tbl.COUNT ' || p_tar_wbs_version_id_tbl.COUNT );
1397 print_time( ' l_tar_wbs_ver_id_tbl.COUNT ' || l_tar_wbs_ver_id_tbl.COUNT );
1398
1399 <<i_loop>>
1400 FOR i IN 1..p_tar_wbs_version_id_tbl.COUNT LOOP
1401 -- print_time( ' p_tar_wbs_version_id_tbl.(i) ' || p_tar_wbs_version_id_tbl(i) );
1402
1403 l_tar_wbs_ver_id1 := p_tar_wbs_version_id_tbl(i);
1404 l_tar_wbs_ver_id2 := -99999999;
1405
1406 <<j_loop>>
1407 FOR j IN 1..l_tar_wbs_ver_id_tbl.COUNT LOOP
1408
1409 l_tar_wbs_ver_id2 := l_tar_wbs_ver_id_tbl(j);
1410 -- print_time( ' p_tar_wbs_version_id_tbl(i) ' || p_tar_wbs_version_id_tbl(i) );
1411 -- print_time( ' l_tar_wbs_ver_id_tbl(j) ' || l_tar_wbs_ver_id_tbl(j) );
1412 -- print_time( ' l_tar_wbs_ver_id1 ' || l_tar_wbs_ver_id1 );
1413 -- print_time( ' l_tar_wbs_ver_id2 ' || l_tar_wbs_ver_id2 );
1414
1415 IF ( l_tar_wbs_ver_id1 = l_tar_wbs_ver_id2 ) THEN
1416 EXIT j_loop;
1417 END IF;
1418 END LOOP;
1419
1420 IF ( l_tar_wbs_ver_id1 <> l_tar_wbs_ver_id2 ) THEN
1421 l_tar_denorm_not_exists_tbl.EXTEND;
1422 l_tar_denorm_not_exists_tbl(l_tar_denorm_not_exists_tbl.COUNT) := l_tar_wbs_ver_id1;
1423 END IF;
1424
1425 END LOOP;
1426
1427 print_time(' begin list of wbs version ids without denorm populated ' );
1428 FOR i IN 1..l_tar_denorm_not_exists_tbl.COUNT LOOP
1429 print_time(' i = ' || i || ' ' || l_tar_denorm_not_exists_tbl(i));
1430 END LOOP;
1431 print_time(' end list of wbs version ids without denorm populated ' );
1432
1433 x_tar_denorm_not_exists_tbl := l_tar_denorm_not_exists_tbl;
1434
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 print_time(' DENORM_DEFINITION_EXISTS exception ' || SQLERRM );
1438 IF (c_wbs_vers_exist%isopen) THEN
1439 CLOSE c_wbs_vers_exist;
1440 END IF;
1441 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => g_package_name ,
1442 p_procedure_name => 'DENORM_DEFINITION_EXISTS');
1443 RAISE;
1444 END;
1445 --
1446 -- Populate wbs denorm table for plan create case.
1447 --
1448 PROCEDURE POPULATE_WBS_DENORM(
1449 p_online IN VARCHAR2 := 'Y'
1450 , p_tar_denorm_not_exists_tbl IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1451 , x_return_status OUT NOCOPY VARCHAR2
1452 ) IS
1453
1454 CURSOR c_wbs_struct_ver_id_online (
1455 p_wbs_version_id NUMBER
1456 ) IS
1457 SELECT DISTINCT wbs_struct_version_id, project_id
1458 FROM pji_fm_extr_plnver3_t
1459 WHERE wbs_struct_version_id = p_wbs_version_id;
1460
1461 -- CURSOR c_wbs_struct_ver_id_bulk IS
1462 -- SELECT wbs_struct_version_id, project_id
1463 -- FROM pji_fm_extr_plnver4
1464 -- WHERE worker_id = g_worker_id;
1465
1466 BEGIN
1467
1468 x_return_status := 'S';
1469
1470 IF (p_online = 'Y') THEN
1471
1472 ------------------------------------------------------------------
1473 --Below code is commented out for publish performance fixes
1474 -------------------------------------------------------------------
1475 /*
1476 Bug 4075697:
1477 Below code is added to avoid changes to PA/PJI xbs denorm tables
1478 during copy/move/indent/outdent flows during plan update
1479 Structure changes will get processed in process updates flow
1480 IF NVL(Pa_Task_Pub1.G_CALL_PJI_ROLLUP, 'Y') = 'N' THEN
1481 Pa_Task_Pub1.G_CALL_PJI_ROLLUP := NULL;
1482 RETURN;
1483 END IF;
1484 */
1485
1486 IF (p_tar_denorm_not_exists_tbl.COUNT = 0) THEN
1487 print_time(' All denorm definitions alreadey exist, need not be created again.' );
1488 RETURN;
1489 ELSE
1490 print_time(' ----------- Begin denorm population..' );
1491 END IF;
1492
1493 --IF c_wbs_struct_ver_id_online%ISOPEN THEN CLOSE c_wbs_struct_ver_id_online; END IF;
1494
1495 --FOR j IN c_wbs_struct_ver_id_online LOOP
1496 FOR i IN 1..p_tar_denorm_not_exists_tbl.COUNT LOOP
1497
1498 print_time(' i = ' || i || ' wbs_version_id = ' || p_tar_denorm_not_exists_tbl(i));
1499
1500 FOR j IN c_wbs_struct_ver_id_online(
1501 p_wbs_version_id => p_tar_denorm_not_exists_tbl(i)
1502 ) LOOP
1503
1504 print_time('project_id = ' || j.project_id);
1505 print_time('wbs_version_id = ' || j.wbs_struct_version_id);
1506
1507 Pji_Pjp_Sum_Rollup.set_online_context (
1508 p_event_id => NULL,
1509 p_project_id => j.project_id,
1510 p_plan_type_id => NULL,
1511 p_old_baselined_version => NULL,
1512 p_new_baselined_version => NULL,
1513 p_old_original_version => NULL,
1514 p_new_original_version => NULL,
1515 p_old_struct_version => NULL,
1516 p_new_struct_version => j.wbs_struct_version_id );
1517
1518 print_time(' row cnt after online context setting ' || SQL%ROWCOUNT );
1519
1520 Pji_Pjp_Sum_Denorm.populate_xbs_denorm(
1521 p_worker_id => 1,
1522 p_denorm_type => 'WBS',
1523 p_wbs_version_id => j.wbs_struct_version_id,
1524 p_prg_group1 => NULL,
1525 p_prg_group2 => NULL
1526 );
1527
1528 print_time(' row cnt after populating xbs denorm ' || SQL%ROWCOUNT );
1529
1530 Pji_Pjp_Sum_Rollup.update_xbs_denorm;
1531
1532 print_time(' row cnt after updating xbs denorm ' || SQL%ROWCOUNT );
1533
1534 Pji_Pjp_Sum_Denorm.cleanup_xbs_denorm(
1535 p_worker_id => 1
1536 , p_extraction_type => 'ONLINE');
1537
1538 print_time(' row cnt after cleaning up xbs denorm ' || SQL%ROWCOUNT );
1539
1540 END LOOP;
1541
1542 END LOOP;
1543
1544 print_time(' ----------- End denorm population..' );
1545
1546 ELSE
1547 RETURN;
1548 END IF;
1549
1550 EXCEPTION
1551 WHEN OTHERS THEN
1552 IF c_wbs_struct_ver_id_online%ISOPEN THEN CLOSE c_wbs_struct_ver_id_online; END IF;
1553 --IF c_wbs_struct_ver_id_bulk%ISOPEN THEN CLOSE c_wbs_struct_ver_id_bulk; END IF;
1554 x_return_status := 'F';
1555 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1556 p_procedure_name => 'POPULATE_WBS_DENORM');
1557 RAISE;
1558
1559 END;
1560
1561
1562 -----------------------------------------------------------------------
1563 ----- Partial refresh of plan data... -----
1564 -- PR can be either for a given plan type or a given rbs version id.
1565 -----------------------------------------------------------------------
1566 PROCEDURE PULL_PLANS_FOR_PR (
1567 p_rbs_version_id IN NUMBER
1568 , p_plan_type_id IN NUMBER
1569 , p_context IN VARCHAR2 -- Valid values are 'RBS' or 'PLANTYPE'.
1570 , x_return_status OUT NOCOPY VARCHAR2
1571 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
1572 l_count NUMBER;
1573 l_num_rows NUMBER := 0;
1574 BEGIN
1575
1576 print_time(' PULL_PLANS_FOR_PR : begin.. ');
1577
1578
1579 PJI_PJP_FP_CURR_WRAP.INIT_ERR_STACK
1580 ( p_package_name => g_package_name
1581 , x_return_status => x_return_status );
1582
1583 print_time(' PULL_PLANS_FOR_PR value of p_plan_type_id is ' || p_plan_type_id || ' and p_rbs_version_id is ' || p_rbs_version_id );
1584
1585
1586 PJI_FM_PLAN_MAINT_PVT.VALIDATE_SET_PR_PARAMS(
1587 p_rbs_version_id => p_rbs_version_id
1588 , p_plan_type_id => p_plan_type_id
1589 , p_context => p_context
1590 , x_num_rows => l_num_rows
1591 , x_return_status => x_return_status
1592 , x_msg_code => x_msg_code) ;
1593
1594 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1595 print_time(' PULL_PLANS_FOR_PR input parameters did not validate. Exitting.');
1596 RETURN;
1597 ELSIF (l_num_rows = 0) THEN
1598 print_time ( 'GET_ACTUALS_SUMM : no rows in ver3 to process, RETURNING.' );
1599 RETURN;
1600 END IF;
1601
1602
1603 Pji_Fm_Plan_Maint_Pvt.POPULATE_RBS_HDR;
1604 print_time('Populated new records into RBS Header Table.');
1605
1606 -- Retrieve overridden ETC values for latest published WP structures in budget lines table.
1607 -- PJI_FM_PLAN_MAINT_PVT.RETRIEVE_OVERRIDDEN_WP_ETC;
1608 print_time('Retrieved overridden ETC values.');
1609
1610 -- Pull primary slice for workplans and relevant fin plans.
1611 -- Do not pull actuals/overriddend ETC values, they are extracted in "get plan res actuals" flow.
1612 PJI_FM_PLAN_MAINT_PVT.EXTRACT_PLAN_ETC_PRIRBS ;
1613 print_time('Extracted workplan data.');
1614
1615 -- Plan lines for ETCs are populated in get plan res actuals flow.
1616 -- PJI_FM_PLAN_MAINT_PVT.DELETE_PLAN_LINES ( x_return_status => x_return_status );
1617 -- print_time('Delete processed plan lines.');
1618
1619 PJI_FM_PLAN_MAINT_PVT.GET_GLOBAL_EXCHANGE_RATES;
1620 print_time(' Got global exchange rates. ');
1621
1622 PJI_FM_PLAN_MAINT_PVT.EXTRACT_PLAN_AMTS_PRIRBS_GLC12
1623 (p_pull_dangling_flag => 'N');
1624 print_time('Extracted finplan data for pri RBS for secondary currencies as well.');
1625
1626 PJI_FM_PLAN_MAINT_PVT.EXTRACT_PLAN_AMTS_SECRBS_GLC12
1627 (p_pull_dangling_flag => 'N');
1628 print_time('Extracted finplan data for Secondary RBS for secondary currencies as well.');
1629
1630 PJI_FM_PLAN_MAINT_PVT.PRORATE_TO_ALL_CALENDARS ;
1631 print_time('Prorated Fin plan data to all calendars.');
1632
1633
1634 --
1635 -- All rollups are done for plans along with actuals from the calling
1636 -- concurrent program in the following steps.
1637 --
1638
1639 print_time('CREATE_PRIMARY_PVT 2 : end.. ');
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1644 ( p_package_name => g_package_name
1645 , p_procedure_name => ' PULL_PLANS_FOR_PR '
1646 , x_return_status => x_return_status ) ;
1647
1648 RAISE;
1649 END;
1650
1651
1652 ---------------------------------------------------------
1653 ----- Full and Incr summarization of actuals ... -----
1654 ---------------------------------------------------------
1655
1656 PROCEDURE GET_ACTUALS_SUMM (
1657 p_extr_type IN VARCHAR2 -- Valid values are 'FULL' and 'INCREMENTAL'.
1658 , x_return_status OUT NOCOPY VARCHAR2
1659 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
1660
1661 l_num_rows NUMBER := 0;
1662
1663 BEGIN
1664
1665 print_time('GET_ACTUALS_SUMM : start.. ');
1666
1667 print_time ( 'GET_ACTUALS_SUMM : extrn type is: ' || NVL(p_extr_type, 'AA'));
1668
1669 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1670 ( p_package_name => g_package_name
1671 , x_return_status => x_return_status ) ;
1672
1673 g_worker_id := PJI_PJP_FP_CURR_WRAP.GET_WORKER_ID;
1674
1675 print_time ( ' p_extr_type = ' || p_extr_type || ' g_worker_id = ' || g_worker_id );
1676
1677 IF (p_extr_type NOT IN ('FULL', 'INCREMENTAL')) THEN
1678 print_time ( ' p_extr_type value ' || p_extr_type || ' is not valid. ');
1679 RETURN;
1680 END IF;
1681
1682
1683 -- Record plan lines into debug tables.
1684 Pji_Fm_Xbs_Accum_Maint.debug_plan_lines;
1685
1686
1687 -- Populate ver3 table.
1688 PJI_FM_PLAN_MAINT_PVT.VALIDATE_SET_PR_PARAMS(
1689 p_rbs_version_id => NULL
1690 , p_plan_type_id => NULL
1691 , p_context => p_extr_type
1692 , x_num_rows => l_num_rows
1693 , x_return_status => x_return_status
1694 , x_msg_code => x_msg_code) ;
1695
1696
1697 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1698 print_time(' PULL_PLANS_FOR_PR input parameters did not validate. Exitting.');
1699 RETURN;
1700 ELSIF (l_num_rows = 0) THEN
1701 print_time ( 'GET_ACTUALS_SUMM : no rows in ver3 to process, RETURNING.' );
1702 RETURN;
1703 END IF;
1704
1705
1706 Pji_Fm_Plan_Maint_Pvt.POPULATE_RBS_HDR;
1707 print_time('Populated new records into RBS Header Table.');
1708
1709
1710 -- Extract actuals from budget lines for FULL summarization.
1711 pji_fm_plan_maint_pvt.EXTRACT_ACTUALS(
1712 p_extrn_type => p_extr_type
1713 );
1714 print_time('Actuals/etc extraction.');
1715
1716 --
1717 -- WBS and RBS headers will be populated in PROCESS_PENDING_PLAN_UPDATES
1718 --
1719
1720 --PJI_FM_PLAN_MAINT_PVT.DELETE_PLAN_LINES ( x_return_status => x_return_status );
1721 --print_time('Delete processed plan lines for this worker id.');
1722
1723 --
1724 -- All rollups are done for plans along with actuals from the calling
1725 -- concurrent program in the following steps.
1726 --
1727
1728 print_time('GET_ACTUALS_SUMM : end.. ');
1729
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1733 ( p_package_name => g_package_name
1734 , p_procedure_name => 'GET_ACTUALS_SUMM'
1735 , x_return_status => x_return_status ) ;
1736
1737 RAISE;
1738 END;
1739
1740
1741 FUNCTION CHECK_VER3_NOT_EMPTY ( p_online IN VARCHAR2 := 'T') -- Valid values T/F
1742 RETURN VARCHAR2 IS
1743 l_count NUMBER;
1744 l_return_status VARCHAR2(1);
1745 BEGIN
1746
1747 IF (p_online = 'T') THEN
1748 SELECT COUNT(1)
1749 INTO l_count
1750 FROM pji_fm_extr_plnver3_t;
1751 ELSE
1752 SELECT COUNT(1)
1753 INTO l_count
1754 FROM pji_fm_extr_plnver4
1755 WHERE worker_id = g_worker_id;
1756 END IF;
1757
1758 print_time ( ' ver3 table has ' || l_count || ' records. ');
1759
1760 IF (l_count > 0) THEN
1761 RETURN 'T';
1762 ELSE
1763 print_time ( ' # rcds in ver3 is 0, returning from this procedure.... ' );
1764 RETURN 'F';
1765 END IF;
1766
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1770 ( p_package_name => g_package_name
1771 , p_procedure_name => 'CHECK_VER3_NOT_EMPTY'
1772 , x_return_status => l_return_status ) ;
1773
1774 RAISE;
1775 END;
1776
1777
1778 --
1779 -- Contains apis to set online context to perform WBS rollups
1780 -- if WBS changes (changes to structure) have been made.
1781 --
1782 PROCEDURE WBSCHANGEPROC_PRERLP_SETUPS IS
1783
1784 l_project_id NUMBER;
1785 l_plan_version_id NUMBER;
1786 l_wbs_version_id NUMBER;
1787 l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
1788
1789 BEGIN
1790
1791 -- Fix for bug : 4027718
1792 BEGIN
1793
1794 SELECT a.process_update_wbs_flag, a.element_version_id
1795 , a.project_id, b.plan_version_id
1796 INTO g_process_update_wbs_flag, l_wbs_version_id
1797 , l_project_id, l_plan_version_id
1798 FROM pa_proj_elem_ver_structure a ,
1799 PJI_FM_EXTR_PLNVER3_T b
1800 WHERE a.element_version_id = b.wbs_struct_version_id
1801 AND a.project_id = b.project_id -- 4902584
1802 AND ROWNUM <= 1;
1803
1804 EXCEPTION
1805 WHEN NO_DATA_FOUND THEN
1806 RAISE ;
1807 END ;
1808
1809 --
1810 -- Process structure changes if dirty flag is set to 'Y'.
1811 --
1812 IF g_process_update_wbs_flag = 'Y' THEN
1813
1814 --
1815 -- Create an event..
1816 --
1817 g_event_rec.event_type := 'WBS_CHANGE';
1818 g_event_rec.event_object := l_wbs_version_id;
1819 g_event_rec.operation_type := 'I';
1820 g_event_rec.status := 'X';
1821 g_event_rec.attribute1 := l_project_id;
1822 g_event_rec.attribute2 := l_wbs_version_id;
1823 g_event_rec.attribute3 := l_plan_version_id;
1824
1825 PJI_FM_XBS_ACCUM_MAINT.CREATE_EVENT(g_event_rec);
1826
1827 Pji_Pjp_Sum_Denorm.populate_xbs_denorm(
1828 p_worker_id => 1,
1829 p_denorm_type => 'WBS',
1830 p_wbs_version_id => l_wbs_version_id,
1831 p_prg_group1 => NULL,
1832 p_prg_group2 => NULL
1833 );
1834 print_time(' PA denorm table data created. ');
1835
1836 Pji_Pjp_Sum_Rollup.set_online_context (
1837 p_event_id => g_event_rec.event_id,
1838 p_project_id => l_project_id,
1839 p_plan_type_id => NULL,
1840 p_old_baselined_version => NULL,
1841 p_new_baselined_version => NULL,
1842 p_old_original_version => NULL,
1843 p_new_original_version => NULL,
1844 p_old_struct_version => l_wbs_version_id,
1845 p_new_struct_version => l_wbs_version_id
1846 );
1847
1848 Pji_Pjp_Sum_Rollup.populate_xbs_denorm_delta;
1849 print_time(' PA denorm delta table data created. ');
1850
1851 END IF;
1852
1853 END;
1854
1855
1856 --
1857 -- Contains cleanup logic after performing WBS rollups if WBS
1858 -- changes (changes to structure) have been made.
1859 --
1860 PROCEDURE WBSCHANGEPROC_POSTRLP_CLEANUP IS
1861 l_return_status VARCHAR2(300);
1862 l_msg_count NUMBER;
1863 l_msg_data VARCHAR2(100);
1864 BEGIN
1865
1866 IF g_process_update_wbs_flag = 'Y' THEN
1867
1868 Pji_Pjp_Sum_Rollup.rollup_acr_wbs;
1869 print_time(' Did AC lines rollup. ');
1870
1871 Pji_Pjp_Sum_Rollup.update_xbs_denorm;
1872 print_time(' Updated PJI xbs denorm. ');
1873
1874 Pji_Pjp_Sum_Denorm.cleanup_xbs_denorm(
1875 p_worker_id => 1
1876 , p_extraction_type => 'ONLINE');
1877 print_time(' Cleaned up xbs delta denorm. ');
1878
1879 Pji_Fm_Plan_Maint_T_Pvt.MERGE_INTO_AC_FACT;
1880 print_time(' Merged into AC fact. ');
1881
1882
1883 /*
1884 Pa_Proj_Task_Struc_Pub.set_update_wbs_flag (
1885 p_project_id => TO_NUMBER(g_event_rec.attribute1),
1886 p_structure_version_id => TO_NUMBER(g_event_rec.attribute2),
1887 p_update_wbs_flag => 'N',
1888 x_return_status => l_return_status,
1889 x_msg_count => l_msg_count,
1890 x_msg_data => l_msg_data );
1891 print_time(' Reset dirty flag to No. ');
1892 */
1893 /* 5138049 as update by MAANSARI
1894 Pa_Proj_Task_Struc_Pub.process_task_weightage (
1895 p_project_id => TO_NUMBER(g_event_rec.attribute1),
1896 p_structure_version_id => TO_NUMBER(g_event_rec.attribute2),
1897 x_return_status => l_return_status,
1898 x_msg_count => l_msg_count,
1899 x_msg_data => l_msg_data );
1900 */
1901 DELETE FROM PA_PJI_PROJ_EVENTS_LOG
1902 WHERE EVENT_ID = g_event_rec.event_id;
1903
1904 END IF;
1905
1906 END;
1907
1908 -- -----------------------------------------------------
1909 -- procedure POPULATE_FIN8
1910 --
1911 -- History
1912 -- 16-JUN-2005 ANAGARAT Created
1913 --
1914 -- This procedure populates PJI_FM_AGGR_FIN8 table
1915 -- for PJI_FM_XBA_ACCUM_UTILS.get_summarized_data () api
1916 -- -----------------------------------------------------
1917
1918 PROCEDURE POPULATE_FIN8 (p_worker_id IN NUMBER
1919 , p_extraction_type IN VARCHAR2
1920 ,x_return_status OUT NOCOPY VARCHAR2
1921 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1922 BEGIN
1923
1924 print_time('POPULATE_FIN8 : start.. ');
1925
1926 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1927 ( p_package_name => g_package_name
1928 , x_return_status => x_return_status ) ;
1929
1930
1931 IF (p_worker_id IS NOT NULL) THEN
1932
1933 IF ( p_extraction_type='INCREMENTAL' ) THEN
1934
1935 INSERT INTO pji_fm_aggr_fin8
1936 (
1937 WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
1938 RESOURCE_CLASS_ID , PROJECT_ID , PROJECT_ORG_ID ,
1939 PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS , TASK_ID ,
1940 RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
1941 TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
1942 TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
1943 TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
1944 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
1945 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
1946 PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
1947 PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
1948 POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
1949 POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
1950 POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
1951 EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
1952 EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
1953 EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
1954 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
1955 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
1956 G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
1957 G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
1958 G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
1959 G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
1960 G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
1961 ASSIGNMENT_ID, NAMED_ROLE --Bug#4590810
1962 )
1963 SELECT
1964 tmp.WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
1965 RESOURCE_CLASS_ID , tmp.PROJECT_ID , tmp.PROJECT_ORG_ID ,
1966 tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS , TASK_ID ,
1967 RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
1968 TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
1969 TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
1970 TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
1971 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
1972 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
1973 PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
1974 PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
1975 POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
1976 POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
1977 POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
1978 EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
1979 EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
1980 EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
1981 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
1982 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
1983 G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
1984 G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
1985 G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
1986 G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
1987 G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
1988 ASSIGNMENT_ID , NAMED_ROLE --Bug#4590810
1989 FROM pji_fm_aggr_fin7 tmp
1990 ,PJI_PJP_PROJ_BATCH_MAP map
1991 ,pa_proj_fp_options ppfo
1992 WHERE
1993 tmp.PROJECT_ID=map.PROJECT_ID AND
1994 map.WORKER_ID = p_worker_id AND
1995 map.PJI_PROJECT_STATUS = 'Y' AND
1996 ppfo.PROJECT_ID=tmp.PROJECT_ID AND
1997 SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
1998 ppfo.FIN_PLAN_TYPE_ID = (
1999 SELECT fin_plan_type_id
2000 FROM pa_fin_plan_types_b
2001 WHERE use_for_workplan_flag = 'Y'
2002 ) AND
2003 ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
2004
2005 ELSE
2006
2007 INSERT INTO pji_fm_aggr_fin8
2008 (
2009 TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , PROJECT_ID ,
2010 PROJECT_ORG_ID , PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS ,
2011 TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
2012 TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
2013 TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
2014 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
2015 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
2016 POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
2017 POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
2018 EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
2019 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
2020 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
2021 G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
2022 G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
2023 WORKER_ID , RECORD_TYPE, NAMED_ROLE --Bug#4590810
2024 )
2025 SELECT
2026 TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , tmp.PROJECT_ID ,
2027 tmp.PROJECT_ORG_ID , tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS ,
2028 TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
2029 TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
2030 TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
2031 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
2032 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
2033 POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
2034 POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
2035 EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
2036 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
2037 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
2038 G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
2039 G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
2040 p_worker_id , 'A' , NAMED_ROLE --Bug#4590810
2041 FROM pji_fp_txn_accum tmp
2042 ,PJI_PJP_PROJ_BATCH_MAP map
2043 ,pa_proj_fp_options ppfo
2044 WHERE
2045 tmp.PROJECT_ID=map.PROJECT_ID AND
2046 map.WORKER_ID = p_worker_id AND
2047 map.PJI_PROJECT_STATUS = 'Y' AND
2048 ppfo.PROJECT_ID=tmp.PROJECT_ID AND
2049 SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
2050 ppfo.FIN_PLAN_TYPE_ID = (
2051 SELECT fin_plan_type_id
2052 FROM pa_fin_plan_types_b
2053 WHERE use_for_workplan_flag = 'Y'
2054 ) AND
2055 ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
2056
2057
2058 END IF;
2059 END IF;
2060
2061 print_time('POPULATE_FIN8 : end.. ');
2062
2063 EXCEPTION
2064 WHEN OTHERS THEN
2065 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
2066 ( p_package_name => g_package_name
2067 , p_procedure_name => 'POPULATE_FIN8'
2068 , x_return_status => x_return_status ) ;
2069
2070 RAISE;
2071 END;
2072
2073 ----------
2074 -- Print time API to measure time taken by each api. Also useful for debugging.
2075 ----------
2076 PROCEDURE PRINT_TIME(
2077 p_tag IN VARCHAR2
2078 ) IS
2079 BEGIN
2080 PJI_PJP_FP_CURR_WRAP.print_time(p_tag);
2081 EXCEPTION
2082 WHEN OTHERS THEN
2083 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
2084 p_procedure_name => 'PRINT_TIME');
2085 RAISE;
2086 END;
2087
2088
2089 END PJI_FM_PLAN_MAINT;