[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_PLAN_MAINT
Source
1 PACKAGE BODY PJI_FM_PLAN_MAINT AS
2 /* $Header: PJIPP01B.pls 120.44.12020000.7 2013/04/26 06:55:27 sachandr 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 l_time_phase_flag varchar2(2); /* Added for bug 8708651 */
832 l_pji_schema varchar2(30); /* Added for bug 13897252 */
833
834 BEGIN
835
836 print_time('CREATE_SECONDARY_PVT: begin.. ');
837
838 l_pji_schema := pji_utils.get_pji_schema_name; /* Added for bug 13897252 */
839
840 Pji_Fm_Plan_Maint_Pvt.EXTRACT_FIN_PLAN_VERS_BULK(p_slice_type => 'SEC_PROJ');
841 Pji_Fm_Plan_Maint_Pvt.EXTRACT_FIN_PLAN_VERS_BULK(p_slice_type => 'SECRBS_PROJ');
842 print_time(' Identified plan versions to be extracted. ');
843
844 /* Added for bug 13897252 starts */
845 IF PJI_UTILS.GET_PARAMETER('PJP_SIN_PRG') = 'Y' then
846 PJI_PJP_PRG_PERF_ALL.GATHER_TEMP_STATS(p_ownname => l_pji_schema
847 ,p_tabname => 'PJI_FM_EXTR_PLNVER4'
848 ,p_partname => 'P'||g_worker_id
849 ,p_percent => 10);
850 END IF;
851 /* Added for bug 13897252 ends */
852
853 IF (CHECK_VER3_NOT_EMPTY(p_online => 'F') = 'F') THEN
854 print_time(' No data in ver3, returning. ');
855 RETURN;
856 END IF;
857
858 Pji_Fm_Plan_Maint_Pvt.GET_GLOBAL_EXCHANGE_RATES;
859 print_time(' Got global exchange rates. ');
860
861 Pji_Fm_Plan_Maint_Pvt.POPULATE_RBS_HDR;
862 print_time('Populated RBS header table.');
863
864 Pji_Fm_Plan_Maint_Pvt.POPULATE_WBS_HDR;
865 print_time('Populated WBS header table.');
866
867 -- Bug 15881881
868 -- Commented for bug 16360282
869 /*fnd_stats.gather_table_stats('PA','PA_RESOURCE_ASSIGNMENTS',PERCENT=>40);
870 fnd_stats.gather_table_stats('PA','PA_BUDGET_LINES',PERCENT=>40); */
871 -- End Bug 15881881
872
873 Pji_Fm_Plan_Maint_Pvt.EXTRACT_PLAN_AMTS_PRIRBS_GLC12;
874 print_time(' Extracted plan data for primary RBSes.');
875
876 Pji_Fm_Plan_Maint_Pvt.EXTRACT_PLAN_AMTS_SECRBS_GLC12;
877 print_time(' Extracted plan data for primary RBSes.');
878
879 Pji_Fm_Plan_Maint_Pvt.DELETE_GLOBAL_EXCHANGE_RATES;
880 print_time(' Deleted global exchange rates. ');
881
882 /* Added for bug 8708651 */
883 l_time_phase_flag := PJI_UTILS.GET_SETUP_PARAMETER('TIME_PHASE_FLAG');
884
885 -- Bug 15881881
886 fnd_stats.gather_table_stats('PJI','PJI_FP_AGGR_PJP1',PERCENT=>40);
887 -- End Bug 15881881
888 if l_time_phase_flag = 'Y' then
889 null;
890 else
891 PJI_FM_PLAN_MAINT_PVT.PRORATE_TO_ALL_CALENDARS; /* Modified to PJI_FM_PLAN_MAINT_PVT
892 for bug 12996111 */
893 end if;
894 /* Added for bug 8708651 */
895 print_time(' Prorated to all calendars. ');
896
897 -- PJI_FM_PLAN_MAINT_PVT.UPDATE_WBS_HDR; /* Commented for bug 13897252 */
898 -- print_time(' Updated the WBS header table with min max txn dates.');
899
900 -- Bug 15881881
901 fnd_stats.gather_table_stats('PJI','PJI_FP_AGGR_PJP1',PERCENT=>40);
902 -- Commented for bug 16360282
903 -- fnd_stats.gather_table_stats('PJI','PJI_FP_XBS_ACCUM_F',PERCENT=>40);
904 -- End Bug 15881881
905
906 Pji_Fm_Plan_Maint_Pvt.EXTRACT_DANGL_REVERSAL;
907 print_time(' Inserted Reversal records for dangling plans.');
908
909 Pji_Fm_Plan_Maint_Pvt.MARK_EXTRACTED_PLANS('SEC');
910 print_time('Marked dangling versions.. ');
911 -- Back from unlock headers because at the time of dangling its not marking
912 -- due to purge before unlock headers bug 5155692
913
914 print_time('CREATE_SECONDARY_PVT: end. successful.. ');
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 print_time('CREATE_SECONDARY_PVT: exception. '|| SQLERRM);
919 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
920 p_procedure_name => 'CREATE_SECONDARY_PVT');
921 RAISE;
922 END;
923
924
925 PROCEDURE CREATE_SECONDARY_T_PVT(
926 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
927 , p_commit IN VARCHAR2 := 'F'
928 , p_process_all IN VARCHAR2 := 'F') IS
929
930 l_return_status VARCHAR2(1);
931
932 BEGIN
933
934 print_time('CREATE_SECONDARY_T_PVT: begin.. ');
935
936 -------------------------------------------------------------------------
937 -- Re-extract pri/sec rbs data with amounts converted to Global1 and
938 -- Global2 currencies also.
939 -------------------------------------------------------------------------
940
941 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES; -- Clean up interim tables.
942 print_time(' Cleaned up interim tables. ');
943
944 print_time(' !!!!!!!!!!!! Begin reextraction !!!!!!!! ');
945
946
947 IF (p_process_all = 'F') THEN
948 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(p_fp_version_ids, 'PRI');
949 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(p_fp_version_ids, 'SECRBS');
950 -- PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(p_fp_version_ids, '-3-4');
951 ELSE
952 print_time(' Incorrect parameters to CREATE_SECONDARY_T_PVT: p_process_all = ' || p_process_all );
953 RETURN;
954 END IF;
955 print_time(' Populated ver3 table. ');
956
957 IF (CHECK_VER3_NOT_EMPTY(p_online => 'T') = 'F') THEN
958 print_time(' No data in ver3, returning. ');
959 RETURN;
960 END IF;
961
962 PJI_FM_PLAN_MAINT_T_PVT.MAP_ORG_CAL_INFO('N') ;
963 print_time(' Updated calendar and org tables. ');
964
965 PJI_FM_PLAN_MAINT_T_PVT.GET_GLOBAL_EXCHANGE_RATES;
966 print_time(' Got global exchange rates. ');
967
968 SEC_SLICE_CREATE_T;
969 print_time('Proration done and rollups created.');
970
971 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
972
973 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT;
974 print_time('Merged reversed data.');
975
976 PJI_FM_PLAN_MAINT_T_PVT.MARK_EXTRACTED_PLANS('SEC');
977 print_time('Marked ');
978
979 PJI_FM_PLAN_MAINT_T_PVT.DELETE_GLOBAL_EXCHANGE_RATES;
980 print_time(' Deleted global exchange rates. ');
981
982 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES; -- Clean up interim tables.
983
984 IF (p_commit = 'T') THEN
985 COMMIT;
986 END IF;
987
988 print_time('CREATE_SECONDARY_T_PVT: end. successful.. ');
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 print_time('CREATE_SECONDARY_T_PVT: exception. ');
993 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
994 p_procedure_name => 'CREATE_SECONDARY_T_PVT');
995 RAISE;
996 END;
997
998
999 PROCEDURE UPDATE_PRIMARY_PVT (
1000 p_plan_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
1001 -- p_plan_version_id IN NUMBER := NULL
1002 , p_commit IN VARCHAR2 := 'F'
1003 )
1004 IS
1005 -- l_plan_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(p_plan_version_id);
1006 BEGIN
1007
1008 print_time('UPDATE_PRIMARY_PVT: begin.. ');
1009
1010 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_FIN_PLAN_VERSIONS(
1011 p_fp_version_ids => p_plan_version_ids
1012 , p_slice_type => 'PRI');
1013
1014 print_time ( ' 1.1 ');
1015
1016 IF (CHECK_VER3_NOT_EMPTY(p_online => 'T') = 'F') THEN
1017 RETURN;
1018 END IF;
1019
1020 WBSCHANGEPROC_PRERLP_SETUPS;
1021 print_time(' Setups before calling WBS rollup code. ');
1022
1023 PJI_FM_PLAN_MAINT_T_PVT.MAP_ORG_CAL_INFO('N');
1024 print_time(' 1.a3 Updated org/cal tables. ');
1025
1026 PJI_FM_PLAN_MAINT_T_PVT.RETRIEVE_DELTA_SLICE; print_time ( ' 2 ');
1027 print_time(' 1.a4 Extracted data in plan lines. ');
1028
1029
1030 -- With data in ver3 table and pjp1 tables,
1031 -- create rollup slices for primary slice.
1032 -- Mark extracted versions, clean up the interim tables and insert into FP RL fact.
1033
1034 PRI_SLICE_CREATE_T;
1035 print_time ( ' 3 PRI_SLICE_CREATE_T ');
1036 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
1037 WBSCHANGEPROC_POSTRLP_CLEANUP;
1038 print_time(' Cleanup after calling WBS rollup code. ');
1039
1040 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT;
1041 print_time(' Merged into FP fact. ');
1042
1043 PJI_FM_PLAN_MAINT_T_PVT.MARK_EXTRACTED_PLANS('PRI');
1044 print_time(' Marked extracted plans ');
1045
1046 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES;
1047 print_time(' Cleaned up interim tables.');
1048
1049 print_time('UPDATE_PRIMARY_PVT: end. successful.. ');
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 print_time('UPDATE_PRIMARY_PVT: exception.. ');
1054 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1055 p_procedure_name => 'UPDATE_PRIMARY_PVT');
1056 RAISE;
1057 END;
1058
1059
1060 ------------------------------------------------------------------------------------------
1061 -- When this api is called during publish flow, budgeting api has already created plan
1062 -- and default etc (=plan) data for new published version through update api.
1063 -- Data exists for etc values in plan lines.
1064 ------------------------------------------------------------------------------------------
1065 PROCEDURE UPDATE_PRIMARY_PVT_ACT_ETC (
1066 p_commit IN VARCHAR2 := 'F'
1067 , p_plan_version_id IN NUMBER := NULL
1068 , p_prev_pub_version_id IN NUMBER := NULL
1069 , x_return_status OUT NOCOPY VARCHAR2
1070 , x_processing_code OUT NOCOPY VARCHAR2) IS
1071
1072 l_fp_version_ids SYSTEM.pa_num_tbl_type;
1073
1074 BEGIN
1075
1076 print_time ( 'UPDATE_PRIMARY_PVT_ACT_ETC: begin.. ');
1077 print_time ( 'UPDATE_PRIMARY_PVT_ACT_ETC: p_plan_version_id ' || p_plan_version_id );
1078 print_time ( ' p_prev_pub_version_id ' || p_prev_pub_version_id );
1079
1080 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1081 ( p_package_name => g_package_name
1082 , x_return_status => x_return_status );
1083
1084
1085 PJI_FM_PLAN_MAINT_T_PVT.CLEANUP_INTERIM_TABLES; -- Clean up interim tables.
1086
1087 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_PLN_VER_TABLE; print_time ( ' 1 ');
1088
1089 -- WBSCHANGEPROC_PRERLP_SETUPS;
1090 -- print_time(' Setups before calling WBS rollup code. ');
1091
1092 PJI_FM_PLAN_MAINT_T_PVT.REVERSE_ETC(
1093 p_new_pub_version_id => p_plan_version_id
1094 , p_prev_pub_version_id => p_prev_pub_version_id ) ; print_time ( ' 0.2 ');
1095
1096 PRI_SLICE_CREATE_T; print_time ( ' 3 ');
1097 PJI_PJP_SUM_ROLLUP.ROLLUP_FPR_RBS_SMART_SLICES;
1098 -- WBSCHANGEPROC_POSTRLP_CLEANUP;
1099 -- print_time(' Cleanup after calling WBS rollup code. ');
1100
1101 PJI_FM_PLAN_MAINT_T_PVT.MERGE_INTO_FP_FACT; print_time(' 4 .. ');
1102
1103 print_time('UPDATE_PRIMARY_PVT_ACT_ETC: end. successful.. ');
1104
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1108 ( p_package_name => g_package_name
1109 , p_procedure_name => 'UPDATE_PRIMARY_PVT_ACT_ETC'
1110 , x_return_status => x_return_status ) ;
1111
1112 RAISE;
1113 END;
1114
1115
1116 PROCEDURE DELETE_ALL_PVT (
1117 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1118 , p_commit IN VARCHAR2 := 'F') IS
1119
1120 l_project_ids SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
1121 l_wbs_version_ids SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
1122 l_wp_flags SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
1123
1124 BEGIN
1125
1126 print_time('DELETE_ALL_PVT: begin.. ');
1127
1128 l_project_ids.EXTEND(p_fp_version_ids.COUNT);
1129 l_wbs_version_ids.EXTEND(p_fp_version_ids.COUNT);
1130 l_wp_flags.EXTEND(p_fp_version_ids.COUNT);
1131
1132 FOR i IN 1..p_fp_version_ids.COUNT LOOP
1133 BEGIN --bug#4100852
1134 SELECT /*+ index(wbs_hdr PJI_PJP_WBS_HEADER_N1) */ project_id,wbs_version_id, wp_flag
1135 INTO l_project_ids(i),l_wbs_version_ids(i),l_wp_flags(i)
1136 FROM PJI_PJP_WBS_HEADER wbs_hdr
1137 WHERE plan_version_id = p_fp_version_ids(i);
1138
1139 print_time ( ' i = ' || i || ' p_fp_version_ids(i) = ' || p_fp_version_ids(i) || ' l_project_ids(i) ' || l_project_ids(i)
1140 ||' l_wbs_version_id(i)= '||l_wbs_version_ids(i) ||' l_wp_flag(i) = '||l_wp_flags(i) );
1141 EXCEPTION
1142 WHEN NO_DATA_FOUND THEN
1143 NULL;
1144 END;
1145 END LOOP;
1146
1147 /* Added for bug 8708651 */
1148 BEGIN
1149 FORALL i IN 1..p_fp_version_ids.COUNT
1150 DELETE FROM PJI_FP_XBS_ACCUM_F
1151 WHERE plan_version_id = p_fp_version_ids(i)
1152 AND project_id = l_project_ids(i);
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN
1155 NULL;
1156 END;
1157 /* Added for bug 8708651 */
1158
1159 print_time ( 'deleted from fact. # rows = ' || SQL%ROWCOUNT ) ;
1160
1161 FORALL i IN 1..p_fp_version_ids.COUNT
1162 DELETE FROM PJI_PJP_WBS_HEADER
1163 WHERE plan_version_id = p_fp_version_ids(i)
1164 AND project_id = l_project_ids(i);
1165
1166 print_time ( 'deleted from wbs hdr table. # rows = ' || SQL%ROWCOUNT ) ;
1167
1168 FORALL i IN 1..p_fp_version_ids.COUNT
1169 DELETE FROM PJI_PJP_RBS_HEADER
1170 WHERE plan_version_id = p_fp_version_ids(i)
1171 AND project_id = l_project_ids(i);
1172
1173 print_time ( 'deleted from rbs header. # rows = ' || SQL%ROWCOUNT ) ;
1174
1175 FORALL i IN 1..p_fp_version_ids.COUNT
1176 DELETE FROM PJI_ROLLUP_LEVEL_STATUS
1177 WHERE plan_version_id = p_fp_version_ids(i);
1178
1179 print_time ( 'deleted from rollup level status. # rows = ' || SQL%ROWCOUNT ) ;
1180
1181 FORALL i IN 1..p_fp_version_ids.COUNT
1182 DELETE FROM PA_XBS_DENORM
1183 WHERE sup_project_id = l_project_ids(i)
1184 AND l_wp_flags(i) = 'Y'
1185 AND ( struct_version_id = l_wbs_version_ids(i)
1186 OR (struct_type = 'PRG' AND sup_id = l_wbs_version_ids(i) AND sub_id = l_wbs_version_ids(i)));
1187
1188 print_time ( 'deleted from pa_xbs_denorm table. # rows = ' || SQL%ROWCOUNT ) ;
1189
1190 FORALL i IN 1..p_fp_version_ids.COUNT
1191 DELETE FROM PJI_XBS_DENORM
1192 WHERE sup_project_id = l_project_ids(i)
1193 AND l_wp_flags(i) = 'Y'
1194 AND ( struct_version_id = l_wbs_version_ids(i)
1195 OR (struct_type = 'PRG' AND sup_id = l_wbs_version_ids(i) AND sub_id = l_wbs_version_ids(i)));
1196
1197 print_time ( 'deleted from pji_xbs_denorm table. # rows = ' || SQL%ROWCOUNT ) ;
1198
1199 IF (p_commit = 'T') THEN COMMIT; END IF;
1200
1201 print_time('DELETE_ALL_PVT: end.. successful.. ');
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 print_time('DELETE_ALL_PVT: exception.. ');
1206 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1207 p_procedure_name => 'DELETE_ALL_PVT');
1208 RAISE;
1209 END;
1210
1211
1212 --
1213 -- Summarization API for common processing between various online flows.
1214 -- With data in ver3 table and pjp1 tables,
1215 -- create rollup slices and insert into RL fact.
1216 --
1217 PROCEDURE PRI_SLICE_CREATE_T IS
1218 BEGIN
1219
1220 --------------------------------
1221 -- Header table population.
1222 --------------------------------
1223 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_RBS_HDR;
1224 print_time('Populated new records into RBS Header Table.');
1225
1226 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_WBS_HDR;
1227 print_time('Populated new records into WBS Header Table.');
1228
1229 --------------------------------
1230 -- Time rollups.
1231 --------------------------------
1232
1233 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_PA_PRI_ROLLUP(p_honor_rbs => 'N');
1234 print_time('PA calendar rollups done.');
1235
1236 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_GL_PRI_ROLLUP(p_honor_rbs => 'N');
1237 print_time('GL calendar rollups done.');
1238
1239 -- PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ALL_T_PRI_ROLLUP(p_honor_rbs => 'N'); /* Bug 4604617 */
1240 print_time('All time calendar rollups done 1.');
1241
1242 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ALL_T_PRI_ROLLUP(
1243 p_honor_rbs => 'N'
1244 , p_calendar_type => 'C'); /* For Time phase None Bug 4604617 */
1245 print_time('All time calendar rollups done 2.'); /* Added for bug 3871783 */
1246
1247 --------------------------------
1248 -- RBS/WBS/Program rollups.
1249 --------------------------------
1250
1251 PJI_FM_PLAN_MAINT_T_PVT.ROLLUP_FPR_RBS_T_SLICE;
1252 print_time('RBS rollup done.');
1253
1254 PJI_FM_PLAN_MAINT_T_PVT.CREATE_WBSRLP;
1255 print_time('WBS rollup done.');
1256
1257 PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
1258 print_time('Updated the WBS header table with min max txn dates.');
1259
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1263 p_procedure_name => 'PRI_SLICE_CREATE_T');
1264 RAISE;
1265 END;
1266
1267
1268
1269 --
1270 -- With data in ver3 table and pjp1 tables,
1271 -- create rollup slices and insert into RL fact.
1272 --
1273 PROCEDURE SEC_SLICE_CREATE_T IS
1274
1275 l_time_phase_flag varchar2(2); /* Added for bug 8708651 */
1276
1277 BEGIN
1278
1279 --------------------------------
1280 -- Header table population.
1281 --------------------------------
1282
1283 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_RBS_HDR;
1284 print_time('Populated new records into RBS Header Table.');
1285
1286 PJI_FM_PLAN_MAINT_T_PVT.POPULATE_WBS_HDR;
1287 print_time('Populated new records into WBS Header Table.');
1288
1289 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_PLAN_AMTS_PRIRBS_GLC12;
1290 print_time(' Extracted plan data for primary RBSes.');
1291
1292 PJI_FM_PLAN_MAINT_T_PVT.EXTRACT_PLAN_AMTS_SECRBS_GLC12;
1293 print_time(' Extracted plan data for secondary RBSes.');
1294
1295 --
1296 -- Prorate
1297 -- 1. GL cal and Non time phased entries into PA cal.
1298 -- 2. PA cal and Non time phased entries into GL cal.
1299 -- 3. PA, GL cals and Non time phased entries into Ent cal.
1300 --
1301 /* Added for bug 8708651 */
1302 l_time_phase_flag := PJI_UTILS.GET_SETUP_PARAMETER('TIME_PHASE_FLAG');
1303
1304 if l_time_phase_flag = 'Y' then
1305 null;
1306 else
1307 PJI_FM_PLAN_MAINT_T_PVT.PRORATE_TO_ALL_CALENDARS;
1308 print_time(' Prorated to all calendars. ');
1309 end if;
1310 /* Added for bug 8708651 */
1311
1312 --------------------------------
1313 -- Time rollups.
1314 --------------------------------
1315
1316 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_PA_PRI_ROLLUP;
1317 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_GL_PRI_ROLLUP;
1318 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ENT_ROLLUP;
1319 PJI_FM_PLAN_CAL_RLPS_T.CREATE_FP_ALL_T_PRI_ROLLUP(p_calendar_type => NULL); /* Modified for bug 9067086 */
1320 print_time(' Time rollups done. ');
1321
1322
1323 --------------------------------
1324 -- RBS/WBS/Program rollups.
1325 --------------------------------
1326
1327 PJI_FM_PLAN_MAINT_T_PVT.ROLLUP_FPR_RBS_T_SLICE;
1328 print_time(' RBS rollups done. ');
1329
1330 PJI_FM_PLAN_MAINT_T_PVT.CREATE_WBSRLP;
1331 print_time(' WBS rollups done. ');
1332
1333 PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
1334 print_time('Updated the WBS header table with min max txn dates.');
1335
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 print_time(' SEC_SLICE_CREATE_T exception ' || SQLERRM );
1339 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1340 p_procedure_name => 'SEC_SLICE_CREATE_T');
1341 RAISE;
1342 END;
1343
1344
1345 PROCEDURE CHECK_PRIMARY_ALREADY_EXISTS (
1346 p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1347 , p_is_primary_rbs IN VARCHAR2
1348 , x_return_status OUT NOCOPY VARCHAR2 ) IS
1349 i NUMBER;
1350 l_project_id NUMBER;
1351 excp_plan_already_summarized EXCEPTION;
1352 PRAGMA EXCEPTION_INIT(excp_plan_already_summarized, -1422);
1353 BEGIN
1354
1355 x_return_status := FND_API.G_RET_STS_SUCCESS;
1356
1357 FOR i IN p_fp_version_ids.FIRST..p_fp_version_ids.LAST LOOP
1358
1359 print_time('plan version id.. ' || p_fp_version_ids(i) );
1360
1361 BEGIN
1362
1363 SELECT /*+ index_ffs(wbs_hdr PJI_PJP_WBS_HEADER_N1) */
1364 DISTINCT PROJECT_ID
1365 INTO l_project_id
1366 FROM pji_pjp_wbs_header wbs_hdr
1367 WHERE plan_version_id = p_fp_version_ids(i);
1368
1369 IF (p_is_primary_rbs = 'T') THEN
1370 RAISE excp_plan_already_summarized;
1371 END IF;
1372
1373 EXCEPTION
1374 WHEN NO_DATA_FOUND
1375 THEN NULL;
1376 END;
1377
1378 END LOOP;
1379
1380 EXCEPTION
1381 WHEN excp_plan_already_summarized THEN
1382 x_return_status := FND_API.G_RET_STS_ERROR;
1383 print_time(' CHECK_PRIMARY_ALREADY_EXISTS exception ' || SQLERRM );
1384 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1385 p_procedure_name => 'CHECK_PRIMARY_ALREADY_EXISTS');
1386 RAISE;
1387
1388 WHEN OTHERS THEN
1389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1390 print_time(' SEC_SLICE_CREATE_T exception ' || SQLERRM );
1391 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1392 p_procedure_name => 'CHECK_PRIMARY_ALREADY_EXISTS');
1393 RAISE;
1394 END;
1395
1396 --
1397 -- Performance fix: To avoid re-populating pa/pji denorm tables if
1398 -- denorm definition already exists, function checks if denorm definition
1399 -- exists.
1400 --
1401 PROCEDURE DENORM_DEFINITION_EXISTS(
1402 p_tar_wbs_version_id_tbl IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1403 , x_tar_denorm_not_exists_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
1404 ) IS
1405
1406 CURSOR c_wbs_vers_exist IS
1407 SELECT DISTINCT wbs_struct_version_id
1408 FROM pji_fm_extr_plnver3_t ver3
1409 WHERE EXISTS (
1410 SELECT /*+ no_unnest */ 1 -- bug 7607077 asahoo - replaced index hint
1411 FROM pa_xbs_denorm pxd
1412 WHERE 1=1
1413 AND struct_version_id IS NULL
1414 AND sup_id = ver3.wbs_struct_version_id
1415 );
1416
1417 l_tar_wbs_ver_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1418 l_tar_denorm_not_exists_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1419 l_tar_wbs_ver_id1 NUMBER;
1420 l_tar_wbs_ver_id2 NUMBER;
1421
1422 BEGIN
1423
1424 print_time(' begin list of all dest wbs version ids.. ' );
1425 FOR i IN 1..p_tar_wbs_version_id_tbl.COUNT LOOP
1426 print_time(' i = ' || i || ' ' || p_tar_wbs_version_id_tbl(i));
1427 END LOOP;
1428 print_time(' end list of all dest wbs version ids.. ' );
1429
1430
1431 IF (c_wbs_vers_exist%isopen) THEN
1432 CLOSE c_wbs_vers_exist;
1433 END IF;
1434
1435 -- l_tar_wbs_ver_id_tbl.EXTEND(p_tar_wbs_version_id_tbl.COUNT);
1436
1437 OPEN c_wbs_vers_exist;
1438 FETCH c_wbs_vers_exist
1439 BULK COLLECT
1440 INTO l_tar_wbs_ver_id_tbl;
1441 CLOSE c_wbs_vers_exist;
1442
1443 print_time(' begin list of wbs version ids with denorm already populated.. ' );
1444 FOR i IN 1..l_tar_wbs_ver_id_tbl.COUNT LOOP
1445 print_time(' i = ' || i || ' ' || l_tar_wbs_ver_id_tbl(i));
1446 END LOOP;
1447 print_time(' end list of wbs version ids with denorm already populated.. ' );
1448
1449 print_time( ' p_tar_wbs_version_id_tbl.COUNT ' || p_tar_wbs_version_id_tbl.COUNT );
1450 print_time( ' l_tar_wbs_ver_id_tbl.COUNT ' || l_tar_wbs_ver_id_tbl.COUNT );
1451
1452 <<i_loop>>
1453 FOR i IN 1..p_tar_wbs_version_id_tbl.COUNT LOOP
1454 -- print_time( ' p_tar_wbs_version_id_tbl.(i) ' || p_tar_wbs_version_id_tbl(i) );
1455
1456 l_tar_wbs_ver_id1 := p_tar_wbs_version_id_tbl(i);
1457 l_tar_wbs_ver_id2 := -99999999;
1458
1459 <<j_loop>>
1460 FOR j IN 1..l_tar_wbs_ver_id_tbl.COUNT LOOP
1461
1462 l_tar_wbs_ver_id2 := l_tar_wbs_ver_id_tbl(j);
1463 -- print_time( ' p_tar_wbs_version_id_tbl(i) ' || p_tar_wbs_version_id_tbl(i) );
1464 -- print_time( ' l_tar_wbs_ver_id_tbl(j) ' || l_tar_wbs_ver_id_tbl(j) );
1465 -- print_time( ' l_tar_wbs_ver_id1 ' || l_tar_wbs_ver_id1 );
1466 -- print_time( ' l_tar_wbs_ver_id2 ' || l_tar_wbs_ver_id2 );
1467
1468 IF ( l_tar_wbs_ver_id1 = l_tar_wbs_ver_id2 ) THEN
1469 EXIT j_loop;
1470 END IF;
1471 END LOOP;
1472
1473 IF ( l_tar_wbs_ver_id1 <> l_tar_wbs_ver_id2 ) THEN
1474 l_tar_denorm_not_exists_tbl.EXTEND;
1475 l_tar_denorm_not_exists_tbl(l_tar_denorm_not_exists_tbl.COUNT) := l_tar_wbs_ver_id1;
1476 END IF;
1477
1478 END LOOP;
1479
1480 print_time(' begin list of wbs version ids without denorm populated ' );
1481 FOR i IN 1..l_tar_denorm_not_exists_tbl.COUNT LOOP
1482 print_time(' i = ' || i || ' ' || l_tar_denorm_not_exists_tbl(i));
1483 END LOOP;
1484 print_time(' end list of wbs version ids without denorm populated ' );
1485
1486 x_tar_denorm_not_exists_tbl := l_tar_denorm_not_exists_tbl;
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 print_time(' DENORM_DEFINITION_EXISTS exception ' || SQLERRM );
1491 IF (c_wbs_vers_exist%isopen) THEN
1492 CLOSE c_wbs_vers_exist;
1493 END IF;
1494 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => g_package_name ,
1495 p_procedure_name => 'DENORM_DEFINITION_EXISTS');
1496 RAISE;
1497 END;
1498 --
1499 -- Populate wbs denorm table for plan create case.
1500 --
1501 PROCEDURE POPULATE_WBS_DENORM(
1502 p_online IN VARCHAR2 := 'Y'
1503 , p_tar_denorm_not_exists_tbl IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
1504 , x_return_status OUT NOCOPY VARCHAR2
1505 ) IS
1506
1507 CURSOR c_wbs_struct_ver_id_online (
1508 p_wbs_version_id NUMBER
1509 ) IS
1510 SELECT DISTINCT wbs_struct_version_id, project_id
1511 FROM pji_fm_extr_plnver3_t
1512 WHERE wbs_struct_version_id = p_wbs_version_id;
1513
1514 -- CURSOR c_wbs_struct_ver_id_bulk IS
1515 -- SELECT wbs_struct_version_id, project_id
1516 -- FROM pji_fm_extr_plnver4
1517 -- WHERE worker_id = g_worker_id;
1518
1519 BEGIN
1520
1521 x_return_status := 'S';
1522
1523 IF (p_online = 'Y') THEN
1524
1525 ------------------------------------------------------------------
1526 --Below code is commented out for publish performance fixes
1527 -------------------------------------------------------------------
1528 /*
1529 Bug 4075697:
1530 Below code is added to avoid changes to PA/PJI xbs denorm tables
1531 during copy/move/indent/outdent flows during plan update
1532 Structure changes will get processed in process updates flow
1533 IF NVL(Pa_Task_Pub1.G_CALL_PJI_ROLLUP, 'Y') = 'N' THEN
1534 Pa_Task_Pub1.G_CALL_PJI_ROLLUP := NULL;
1535 RETURN;
1536 END IF;
1537 */
1538
1539 IF (p_tar_denorm_not_exists_tbl.COUNT = 0) THEN
1540 print_time(' All denorm definitions alreadey exist, need not be created again.' );
1541 RETURN;
1542 ELSE
1543 print_time(' ----------- Begin denorm population..' );
1544 END IF;
1545
1546 --IF c_wbs_struct_ver_id_online%ISOPEN THEN CLOSE c_wbs_struct_ver_id_online; END IF;
1547
1548 --FOR j IN c_wbs_struct_ver_id_online LOOP
1549 FOR i IN 1..p_tar_denorm_not_exists_tbl.COUNT LOOP
1550
1551 print_time(' i = ' || i || ' wbs_version_id = ' || p_tar_denorm_not_exists_tbl(i));
1552
1553 FOR j IN c_wbs_struct_ver_id_online(
1554 p_wbs_version_id => p_tar_denorm_not_exists_tbl(i)
1555 ) LOOP
1556
1557 print_time('project_id = ' || j.project_id);
1558 print_time('wbs_version_id = ' || j.wbs_struct_version_id);
1559
1560 Pji_Pjp_Sum_Rollup.set_online_context (
1561 p_event_id => NULL,
1562 p_project_id => j.project_id,
1563 p_plan_type_id => NULL,
1564 p_old_baselined_version => NULL,
1565 p_new_baselined_version => NULL,
1566 p_old_original_version => NULL,
1567 p_new_original_version => NULL,
1568 p_old_struct_version => NULL,
1569 p_new_struct_version => j.wbs_struct_version_id );
1570
1571 print_time(' row cnt after online context setting ' || SQL%ROWCOUNT );
1572
1573 Pji_Pjp_Sum_Denorm.populate_xbs_denorm(
1574 p_worker_id => 1,
1575 p_denorm_type => 'WBS',
1576 p_wbs_version_id => j.wbs_struct_version_id,
1577 p_prg_group1 => NULL,
1578 p_prg_group2 => NULL
1579 );
1580
1581 print_time(' row cnt after populating xbs denorm ' || SQL%ROWCOUNT );
1582
1583 Pji_Pjp_Sum_Rollup.update_xbs_denorm;
1584
1585 print_time(' row cnt after updating xbs denorm ' || SQL%ROWCOUNT );
1586
1587 Pji_Pjp_Sum_Denorm.cleanup_xbs_denorm(
1588 p_worker_id => 1
1589 , p_extraction_type => 'ONLINE');
1590
1591 print_time(' row cnt after cleaning up xbs denorm ' || SQL%ROWCOUNT );
1592
1593 END LOOP;
1594
1595 END LOOP;
1596
1597 print_time(' ----------- End denorm population..' );
1598
1599 ELSE
1600 RETURN;
1601 END IF;
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 IF c_wbs_struct_ver_id_online%ISOPEN THEN CLOSE c_wbs_struct_ver_id_online; END IF;
1606 --IF c_wbs_struct_ver_id_bulk%ISOPEN THEN CLOSE c_wbs_struct_ver_id_bulk; END IF;
1607 x_return_status := 'F';
1608 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
1609 p_procedure_name => 'POPULATE_WBS_DENORM');
1610 RAISE;
1611
1612 END;
1613
1614
1615 -----------------------------------------------------------------------
1616 ----- Partial refresh of plan data... -----
1617 -- PR can be either for a given plan type or a given rbs version id.
1618 -----------------------------------------------------------------------
1619 PROCEDURE PULL_PLANS_FOR_PR (
1620 p_rbs_version_id IN NUMBER
1621 , p_plan_type_id IN NUMBER
1622 , p_context IN VARCHAR2 -- Valid values are 'RBS' or 'PLANTYPE'.
1623 , x_return_status OUT NOCOPY VARCHAR2
1624 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
1625 l_count NUMBER;
1626 l_num_rows NUMBER := 0;
1627 l_time_phase_flag varchar2(2); /* Added for bug 8708651 */
1628 BEGIN
1629
1630 print_time(' PULL_PLANS_FOR_PR : begin.. ');
1631
1632
1633 PJI_PJP_FP_CURR_WRAP.INIT_ERR_STACK
1634 ( p_package_name => g_package_name
1635 , x_return_status => x_return_status );
1636
1637 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 );
1638
1639
1640 PJI_FM_PLAN_MAINT_PVT.VALIDATE_SET_PR_PARAMS(
1641 p_rbs_version_id => p_rbs_version_id
1642 , p_plan_type_id => p_plan_type_id
1643 , p_context => p_context
1644 , x_num_rows => l_num_rows
1645 , x_return_status => x_return_status
1646 , x_msg_code => x_msg_code) ;
1647
1648 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1649 print_time(' PULL_PLANS_FOR_PR input parameters did not validate. Exitting.');
1650 RETURN;
1651 ELSIF (l_num_rows = 0) THEN
1652 print_time ( 'GET_ACTUALS_SUMM : no rows in ver3 to process, RETURNING.' );
1653 RETURN;
1654 END IF;
1655
1656
1657 Pji_Fm_Plan_Maint_Pvt.POPULATE_RBS_HDR;
1658 print_time('Populated new records into RBS Header Table.');
1659
1660 -- Retrieve overridden ETC values for latest published WP structures in budget lines table.
1661 -- PJI_FM_PLAN_MAINT_PVT.RETRIEVE_OVERRIDDEN_WP_ETC;
1662 print_time('Retrieved overridden ETC values.');
1663
1664 -- Pull primary slice for workplans and relevant fin plans.
1665 -- Do not pull actuals/overriddend ETC values, they are extracted in "get plan res actuals" flow.
1666 PJI_FM_PLAN_MAINT_PVT.EXTRACT_PLAN_ETC_PRIRBS ;
1667 print_time('Extracted workplan data.');
1668
1669 -- Plan lines for ETCs are populated in get plan res actuals flow.
1670 -- PJI_FM_PLAN_MAINT_PVT.DELETE_PLAN_LINES ( x_return_status => x_return_status );
1671 -- print_time('Delete processed plan lines.');
1672
1673 PJI_FM_PLAN_MAINT_PVT.GET_GLOBAL_EXCHANGE_RATES;
1674 print_time(' Got global exchange rates. ');
1675
1676 PJI_FM_PLAN_MAINT_PVT.EXTRACT_PLAN_AMTS_PRIRBS_GLC12
1677 (p_pull_dangling_flag => 'N');
1678 print_time('Extracted finplan data for pri RBS for secondary currencies as well.');
1679
1680 PJI_FM_PLAN_MAINT_PVT.EXTRACT_PLAN_AMTS_SECRBS_GLC12
1681 (p_pull_dangling_flag => 'N');
1682 print_time('Extracted finplan data for Secondary RBS for secondary currencies as well.');
1683
1684 /* Added for bug 8708651 */
1685 l_time_phase_flag := PJI_UTILS.GET_SETUP_PARAMETER('TIME_PHASE_FLAG');
1686
1687 if l_time_phase_flag = 'Y' then
1688 null;
1689 else
1690 PJI_FM_PLAN_MAINT_PVT.PRORATE_TO_ALL_CALENDARS ;
1691 print_time('Prorated Fin plan data to all calendars.');
1692 end if;
1693 /* Added for bug 8708651 */
1694 --
1695 -- All rollups are done for plans along with actuals from the calling
1696 -- concurrent program in the following steps.
1697 --
1698
1699 print_time('CREATE_PRIMARY_PVT 2 : end.. ');
1700
1701 EXCEPTION
1702 WHEN OTHERS THEN
1703 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1704 ( p_package_name => g_package_name
1705 , p_procedure_name => ' PULL_PLANS_FOR_PR '
1706 , x_return_status => x_return_status ) ;
1707
1708 RAISE;
1709 END;
1710
1711
1712 ---------------------------------------------------------
1713 ----- Full and Incr summarization of actuals ... -----
1714 ---------------------------------------------------------
1715
1716 PROCEDURE GET_ACTUALS_SUMM (
1717 p_extr_type IN VARCHAR2 -- Valid values are 'FULL' and 'INCREMENTAL'.
1718 , x_return_status OUT NOCOPY VARCHAR2
1719 , x_msg_code OUT NOCOPY VARCHAR2 ) IS
1720
1721 l_num_rows NUMBER := 0;
1722
1723 BEGIN
1724
1725 print_time('GET_ACTUALS_SUMM : start.. ');
1726
1727 print_time ( 'GET_ACTUALS_SUMM : extrn type is: ' || NVL(p_extr_type, 'AA'));
1728
1729 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1730 ( p_package_name => g_package_name
1731 , x_return_status => x_return_status ) ;
1732
1733 g_worker_id := PJI_PJP_FP_CURR_WRAP.GET_WORKER_ID;
1734
1735 print_time ( ' p_extr_type = ' || p_extr_type || ' g_worker_id = ' || g_worker_id );
1736
1737 IF (p_extr_type NOT IN ('FULL', 'INCREMENTAL')) THEN
1738 print_time ( ' p_extr_type value ' || p_extr_type || ' is not valid. ');
1739 RETURN;
1740 END IF;
1741
1742
1743 -- Record plan lines into debug tables.
1744 Pji_Fm_Xbs_Accum_Maint.debug_plan_lines;
1745
1746
1747 -- Populate ver3 table.
1748 PJI_FM_PLAN_MAINT_PVT.VALIDATE_SET_PR_PARAMS(
1749 p_rbs_version_id => NULL
1750 , p_plan_type_id => NULL
1751 , p_context => p_extr_type
1752 , x_num_rows => l_num_rows
1753 , x_return_status => x_return_status
1754 , x_msg_code => x_msg_code) ;
1755
1756
1757 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1758 print_time(' PULL_PLANS_FOR_PR input parameters did not validate. Exitting.');
1759 RETURN;
1760 ELSIF (l_num_rows = 0) THEN
1761 print_time ( 'GET_ACTUALS_SUMM : no rows in ver3 to process, RETURNING.' );
1762 RETURN;
1763 END IF;
1764
1765
1766 Pji_Fm_Plan_Maint_Pvt.POPULATE_RBS_HDR;
1767 print_time('Populated new records into RBS Header Table.');
1768
1769
1770 -- Extract actuals from budget lines for FULL summarization.
1771 pji_fm_plan_maint_pvt.EXTRACT_ACTUALS(
1772 p_extrn_type => p_extr_type
1773 );
1774 print_time('Actuals/etc extraction.');
1775
1776 --
1777 -- WBS and RBS headers will be populated in PROCESS_PENDING_PLAN_UPDATES
1778 --
1779
1780 --PJI_FM_PLAN_MAINT_PVT.DELETE_PLAN_LINES ( x_return_status => x_return_status );
1781 --print_time('Delete processed plan lines for this worker id.');
1782
1783 --
1784 -- All rollups are done for plans along with actuals from the calling
1785 -- concurrent program in the following steps.
1786 --
1787
1788 print_time('GET_ACTUALS_SUMM : end.. ');
1789
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1793 ( p_package_name => g_package_name
1794 , p_procedure_name => 'GET_ACTUALS_SUMM'
1795 , x_return_status => x_return_status ) ;
1796
1797 RAISE;
1798 END;
1799
1800
1801 FUNCTION CHECK_VER3_NOT_EMPTY ( p_online IN VARCHAR2 := 'T') -- Valid values T/F
1802 RETURN VARCHAR2 IS
1803 l_count NUMBER;
1804 l_return_status VARCHAR2(1);
1805 BEGIN
1806
1807 IF (p_online = 'T') THEN
1808 SELECT COUNT(1)
1809 INTO l_count
1810 FROM pji_fm_extr_plnver3_t;
1811 ELSE
1812 SELECT COUNT(1)
1813 INTO l_count
1814 FROM pji_fm_extr_plnver4
1815 WHERE worker_id = g_worker_id;
1816 END IF;
1817
1818 print_time ( ' ver3 table has ' || l_count || ' records. ');
1819
1820 IF (l_count > 0) THEN
1821 RETURN 'T';
1822 ELSE
1823 print_time ( ' # rcds in ver3 is 0, returning from this procedure.... ' );
1824 RETURN 'F';
1825 END IF;
1826
1827 EXCEPTION
1828 WHEN OTHERS THEN
1829 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1830 ( p_package_name => g_package_name
1831 , p_procedure_name => 'CHECK_VER3_NOT_EMPTY'
1832 , x_return_status => l_return_status ) ;
1833
1834 RAISE;
1835 END;
1836
1837
1838 --
1839 -- Contains apis to set online context to perform WBS rollups
1840 -- if WBS changes (changes to structure) have been made.
1841 --
1842 PROCEDURE WBSCHANGEPROC_PRERLP_SETUPS IS
1843
1844 l_project_id NUMBER;
1845 l_plan_version_id NUMBER;
1846 l_wbs_version_id NUMBER;
1847 l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
1848
1849 BEGIN
1850
1851 -- Fix for bug : 4027718
1852 BEGIN
1853
1854 SELECT a.process_update_wbs_flag, a.element_version_id
1855 , a.project_id, b.plan_version_id
1856 INTO g_process_update_wbs_flag, l_wbs_version_id
1857 , l_project_id, l_plan_version_id
1858 FROM pa_proj_elem_ver_structure a ,
1859 PJI_FM_EXTR_PLNVER3_T b
1860 WHERE a.element_version_id = b.wbs_struct_version_id
1861 AND a.project_id = b.project_id -- 4902584
1862 AND ROWNUM <= 1;
1863
1864 EXCEPTION
1865 WHEN NO_DATA_FOUND THEN
1866 RAISE ;
1867 END ;
1868
1869 --
1870 -- Process structure changes if dirty flag is set to 'Y'.
1871 --
1872 IF g_process_update_wbs_flag = 'Y' THEN
1873
1874 --
1875 -- Create an event..
1876 --
1877 g_event_rec.event_type := 'WBS_CHANGE';
1878 g_event_rec.event_object := l_wbs_version_id;
1879 g_event_rec.operation_type := 'I';
1880 g_event_rec.status := 'X';
1881 g_event_rec.attribute1 := l_project_id;
1882 g_event_rec.attribute2 := l_wbs_version_id;
1883 g_event_rec.attribute3 := l_plan_version_id;
1884
1885 PJI_FM_XBS_ACCUM_MAINT.CREATE_EVENT(g_event_rec);
1886
1887 Pji_Pjp_Sum_Denorm.populate_xbs_denorm(
1888 p_worker_id => 1,
1889 p_denorm_type => 'WBS',
1890 p_wbs_version_id => l_wbs_version_id,
1891 p_prg_group1 => NULL,
1892 p_prg_group2 => NULL
1893 );
1894 print_time(' PA denorm table data created. ');
1895
1896 Pji_Pjp_Sum_Rollup.set_online_context (
1897 p_event_id => g_event_rec.event_id,
1898 p_project_id => l_project_id,
1899 p_plan_type_id => NULL,
1900 p_old_baselined_version => NULL,
1901 p_new_baselined_version => NULL,
1902 p_old_original_version => NULL,
1903 p_new_original_version => NULL,
1904 p_old_struct_version => l_wbs_version_id,
1905 p_new_struct_version => l_wbs_version_id
1906 );
1907
1908 Pji_Pjp_Sum_Rollup.populate_xbs_denorm_delta;
1909 print_time(' PA denorm delta table data created. ');
1910
1911 END IF;
1912
1913 END;
1914
1915
1916 --
1917 -- Contains cleanup logic after performing WBS rollups if WBS
1918 -- changes (changes to structure) have been made.
1919 --
1920 PROCEDURE WBSCHANGEPROC_POSTRLP_CLEANUP IS
1921 l_return_status VARCHAR2(300);
1922 l_msg_count NUMBER;
1923 l_msg_data VARCHAR2(100);
1924 BEGIN
1925
1926 IF g_process_update_wbs_flag = 'Y' THEN
1927
1928 Pji_Pjp_Sum_Rollup.rollup_acr_wbs;
1929 print_time(' Did AC lines rollup. ');
1930
1931 Pji_Pjp_Sum_Rollup.update_xbs_denorm;
1932 print_time(' Updated PJI xbs denorm. ');
1933
1934 Pji_Pjp_Sum_Denorm.cleanup_xbs_denorm(
1935 p_worker_id => 1
1936 , p_extraction_type => 'ONLINE');
1937 print_time(' Cleaned up xbs delta denorm. ');
1938
1939 Pji_Fm_Plan_Maint_T_Pvt.MERGE_INTO_AC_FACT;
1940 print_time(' Merged into AC fact. ');
1941
1942
1943 /*
1944 Pa_Proj_Task_Struc_Pub.set_update_wbs_flag (
1945 p_project_id => TO_NUMBER(g_event_rec.attribute1),
1946 p_structure_version_id => TO_NUMBER(g_event_rec.attribute2),
1947 p_update_wbs_flag => 'N',
1948 x_return_status => l_return_status,
1949 x_msg_count => l_msg_count,
1950 x_msg_data => l_msg_data );
1951 print_time(' Reset dirty flag to No. ');
1952 */
1953 /* 5138049 as update by MAANSARI
1954 Pa_Proj_Task_Struc_Pub.process_task_weightage (
1955 p_project_id => TO_NUMBER(g_event_rec.attribute1),
1956 p_structure_version_id => TO_NUMBER(g_event_rec.attribute2),
1957 x_return_status => l_return_status,
1958 x_msg_count => l_msg_count,
1959 x_msg_data => l_msg_data );
1960 */
1961 DELETE FROM PA_PJI_PROJ_EVENTS_LOG
1962 WHERE EVENT_ID = g_event_rec.event_id;
1963
1964 END IF;
1965
1966 END;
1967
1968 -- -----------------------------------------------------
1969 -- procedure POPULATE_FIN8
1970 --
1971 -- History
1972 -- 16-JUN-2005 ANAGARAT Created
1973 --
1974 -- This procedure populates PJI_FM_AGGR_FIN8 table
1975 -- for PJI_FM_XBA_ACCUM_UTILS.get_summarized_data () api
1976 -- -----------------------------------------------------
1977
1978 PROCEDURE POPULATE_FIN8 (p_worker_id IN NUMBER
1979 , p_extraction_type IN VARCHAR2
1980 ,x_return_status OUT NOCOPY VARCHAR2
1981 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1982 BEGIN
1983
1984 print_time('POPULATE_FIN8 : start.. ');
1985
1986 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
1987 ( p_package_name => g_package_name
1988 , x_return_status => x_return_status ) ;
1989
1990
1991 IF (p_worker_id IS NOT NULL) THEN
1992
1993 IF ( p_extraction_type='INCREMENTAL' ) THEN
1994
1995 INSERT INTO pji_fm_aggr_fin8
1996 (
1997 WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
1998 RESOURCE_CLASS_ID , PROJECT_ID , PROJECT_ORG_ID ,
1999 PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS , TASK_ID ,
2000 RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
2001 TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
2002 TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
2003 TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
2004 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
2005 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
2006 PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
2007 PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
2008 POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
2009 POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
2010 POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
2011 EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
2012 EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
2013 EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
2014 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
2015 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
2016 G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
2017 G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
2018 G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
2019 G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
2020 G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
2021 ASSIGNMENT_ID, NAMED_ROLE, --Bug#4590810
2022 CBS_ELEMENT_ID
2023 )
2024 SELECT
2025 tmp.WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
2026 RESOURCE_CLASS_ID , tmp.PROJECT_ID , tmp.PROJECT_ORG_ID ,
2027 tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS , TASK_ID ,
2028 RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
2029 TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
2030 TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
2031 TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
2032 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
2033 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
2034 PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
2035 PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
2036 POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
2037 POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
2038 POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
2039 EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
2040 EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
2041 EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
2042 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
2043 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
2044 G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
2045 G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
2046 G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
2047 G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
2048 G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
2049 ASSIGNMENT_ID , NAMED_ROLE, --Bug#4590810
2050 tmp.cbs_element_id
2051 FROM pji_fm_aggr_fin7 tmp
2052 ,PJI_PJP_PROJ_BATCH_MAP map
2053 ,pa_proj_fp_options ppfo
2054 WHERE
2055 tmp.PROJECT_ID=map.PROJECT_ID AND
2056 map.WORKER_ID = p_worker_id AND
2057 map.PJI_PROJECT_STATUS = 'Y' AND
2058 ppfo.PROJECT_ID=tmp.PROJECT_ID AND
2059 SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
2060 ppfo.FIN_PLAN_TYPE_ID = (
2061 SELECT fin_plan_type_id
2062 FROM pa_fin_plan_types_b
2063 WHERE use_for_workplan_flag = 'Y'
2064 ) AND
2065 ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
2066
2067 ELSE
2068
2069 INSERT INTO pji_fm_aggr_fin8
2070 (
2071 TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , PROJECT_ID ,
2072 PROJECT_ORG_ID , PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS ,
2073 TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
2074 TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
2075 TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
2076 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
2077 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
2078 POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
2079 POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
2080 EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
2081 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
2082 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
2083 G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
2084 G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
2085 WORKER_ID , RECORD_TYPE, NAMED_ROLE, --Bug#4590810
2086 CBS_ELEMENT_ID
2087 )
2088 SELECT
2089 TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , tmp.PROJECT_ID ,
2090 tmp.PROJECT_ORG_ID , tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS ,
2091 TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
2092 TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
2093 TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
2094 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
2095 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
2096 POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
2097 POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
2098 EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
2099 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
2100 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
2101 G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
2102 G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
2103 p_worker_id , 'A' , NAMED_ROLE, --Bug#4590810
2104 tmp.cbs_element_id
2105 FROM pji_fp_txn_accum tmp
2106 ,PJI_PJP_PROJ_BATCH_MAP map
2107 ,pa_proj_fp_options ppfo
2108 WHERE
2109 tmp.PROJECT_ID=map.PROJECT_ID AND
2110 map.WORKER_ID = p_worker_id AND
2111 map.PJI_PROJECT_STATUS = 'Y' AND
2112 ppfo.PROJECT_ID=tmp.PROJECT_ID AND
2113 SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
2114 ppfo.FIN_PLAN_TYPE_ID = (
2115 SELECT fin_plan_type_id
2116 FROM pa_fin_plan_types_b
2117 WHERE use_for_workplan_flag = 'Y'
2118 ) AND
2119 ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
2120
2121
2122 END IF;
2123 END IF;
2124
2125 print_time('POPULATE_FIN8 : end.. ');
2126
2127 EXCEPTION
2128 WHEN OTHERS THEN
2129 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
2130 ( p_package_name => g_package_name
2131 , p_procedure_name => 'POPULATE_FIN8'
2132 , x_return_status => x_return_status ) ;
2133
2134 RAISE;
2135 END;
2136
2137 ----------
2138 -- Print time API to measure time taken by each api. Also useful for debugging.
2139 ----------
2140 PROCEDURE PRINT_TIME(
2141 p_tag IN VARCHAR2
2142 ) IS
2143 BEGIN
2144 PJI_PJP_FP_CURR_WRAP.print_time(p_tag);
2145 EXCEPTION
2146 WHEN OTHERS THEN
2147 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
2148 p_procedure_name => 'PRINT_TIME');
2149 RAISE;
2150 END;
2151
2152
2153 END PJI_FM_PLAN_MAINT;