[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_PLAN_EXTR
Source
1 PACKAGE BODY PJI_FM_PLAN_EXTR AS
2 /* $Header: PJISF07B.pls 120.5.12000000.2 2007/07/25 11:00:21 bifernan ship $ */
3
4 g_cost_budget_type_code varchar2(30);
5 g_cost_forecast_type_code varchar2(30);
6 g_revenue_budget_type_code varchar2(30);
7 g_revenue_forecast_type_code varchar2(30);
8 g_cost_budget_curr_rule varchar2(1);
9 g_cost_forecast_curr_rule varchar2(1);
10 g_revenue_budget_curr_rule varchar2(1);
11 g_revenue_forecast_curr_rule varchar2(1);
12 g_cost_fp_type_code varchar2(30);
13 g_rev_fp_type_code varchar2(30);
14 g_cost_forecast_fp_type_code varchar2(30);
15 g_rev_forecast_fp_type_code varchar2(30);
16
17 g_project_id number(15);
18 g_project_org_id number(15);
19 g_project_organization_id number(15);
20 g_projfunc_currency_code varchar2(15);
21 g_projfunc_currency_mau number;
22 g_labor_mau number := 0.01;
23
24 g_pa_period_flag varchar2(1);
25 g_gl_period_flag varchar2(1);
26
27 g_ent_start_period_id number := null;
28 g_ent_start_period_name varchar2(100) := null;
29 g_entw_start_period_id number := null;
30 g_entw_start_period_name varchar2(100) := null;
31 g_ent_start_date date := null;
32 g_entw_start_date date := null;
33 g_ent_end_date date := null;
34 g_entw_end_date date := null;
35 g_global_start_date date := null;
36
37 g_global_start_J number := null;
38 g_ent_start_J number := null;
39 g_entw_start_J number := null;
40 g_ent_end_J number := null;
41 g_entw_end_J number := null;
42
43 -- -----------------------------------------------------
44 -- procedure GET_SYSTEM_SETTINGS
45 -- -----------------------------------------------------
46
47 procedure get_system_settings is
48
49 begin
50
51 -- Cache setup settings in global variables.
52
53 pji_utils.write2log('Entering PJI_FM_PLAN_EXTR.GET_SYSTEM_SETTINGS',TRUE,3);
54
55 g_cost_budget_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'COST_BUDGET_TYPE_CODE');
56 g_cost_forecast_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'COST_FORECAST_TYPE_CODE');
57 g_revenue_budget_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'REVENUE_BUDGET_TYPE_CODE');
58 g_revenue_forecast_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'REVENUE_FORECAST_TYPE_CODE');
59 g_cost_budget_curr_rule := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'COST_BUDGET_CURR_RULE');
60 g_cost_forecast_curr_rule := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'COST_FORECAST_CURR_RULE');
61 g_revenue_budget_curr_rule := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'REVENUE_BUDGET_CURR_RULE');
62 g_revenue_forecast_curr_rule := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'REVENUE_FORECAST_CURR_RULE');
63 g_pa_period_flag := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'PA_PERIOD_FLAG');
64 g_gl_period_flag := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process,'GL_PERIOD_FLAG');
65
66 g_cost_fp_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process, 'COST_FP_TYPE_ID');
67 g_rev_fp_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process, 'REVENUE_FP_TYPE_ID');
68 g_cost_forecast_fp_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process, 'COST_FORECAST_FP_TYPE_ID');
69 g_rev_forecast_fp_type_code := pji_process_util.get_process_parameter(PJI_RM_SUM_MAIN.g_process, 'REVENUE_FORECAST_FP_TYPE_ID');
70
71 pji_utils.write2log('Completed PJI_FM_PLAN_EXTR.GET_SYSTEM_SETTINGS',TRUE,3);
72
73 end get_system_settings;
74
75 -- -----------------------------------------------------
76 -- procedure INIT_GLOBAL_PARAMETERS
77 -- -----------------------------------------------------
78
79 procedure init_global_parameters is
80
81 begin
82
83 -- This procedure is called from PJI_RM_SUM_MAIN.INIT_PROCESS.
84 -- Since users may make changes in setup form at any given
85 -- point in time we need to make sure that we do not
86 -- pickup these changes in the middle of summarization run.
87 -- We get plan settings once from PJI setup table and
88 -- store them as process variables.
89
90 pji_utils.write2log('Entering PJI_FM_PLAN_EXTR.INIT_GLOBAL_PARAMETERS',TRUE,3);
91
92 pji_process_util.set_process_parameter(
93 PJI_RM_SUM_MAIN.g_process,
94 'COST_BUDGET_TYPE_CODE',
95 pji_utils.get_setup_parameter('COST_BUDGET_TYPE_CODE')
96 );
97
98 pji_process_util.set_process_parameter(
99 PJI_RM_SUM_MAIN.g_process,
100 'COST_FORECAST_TYPE_CODE',
101 pji_utils.get_setup_parameter('COST_FORECAST_TYPE_CODE')
102 );
103
104 pji_process_util.set_process_parameter(
105 PJI_RM_SUM_MAIN.g_process,
106 'REVENUE_BUDGET_TYPE_CODE',
107 pji_utils.get_setup_parameter('REVENUE_BUDGET_TYPE_CODE')
108 );
109
110 pji_process_util.set_process_parameter(
111 PJI_RM_SUM_MAIN.g_process,
112 'REVENUE_FORECAST_TYPE_CODE',
113 pji_utils.get_setup_parameter('REVENUE_FORECAST_TYPE_CODE')
114 );
115
116 pji_process_util.set_process_parameter(
117 PJI_RM_SUM_MAIN.g_process,
118 'COST_BUDGET_CURR_RULE',
119 pji_utils.get_setup_parameter('COST_BUDGET_CONV_RULE')
120 );
121
122 pji_process_util.set_process_parameter(
123 PJI_RM_SUM_MAIN.g_process,
124 'COST_FORECAST_CURR_RULE',
125 pji_utils.get_setup_parameter('COST_FORECAST_CONV_RULE')
126 );
127
128 pji_process_util.set_process_parameter(
129 PJI_RM_SUM_MAIN.g_process,
130 'REVENUE_BUDGET_CURR_RULE',
131 pji_utils.get_setup_parameter('REVENUE_BUDGET_CONV_RULE')
132 );
133
134 pji_process_util.set_process_parameter(
135 PJI_RM_SUM_MAIN.g_process,
136 'REVENUE_FORECAST_CURR_RULE',
137 pji_utils.get_setup_parameter('REVENUE_FORECAST_CONV_RULE')
138 );
139
140 pji_process_util.set_process_parameter(
141 PJI_RM_SUM_MAIN.g_process,
142 'PA_PERIOD_FLAG',
143 pji_utils.get_setup_parameter('PA_PERIOD_FLAG')
144 );
145
146 pji_process_util.set_process_parameter(
147 PJI_RM_SUM_MAIN.g_process,
148 'GL_PERIOD_FLAG',
149 pji_utils.get_setup_parameter('GL_PERIOD_FLAG')
150 );
151
152 pji_process_util.set_process_parameter(
153 PJI_RM_SUM_MAIN.g_process,
154 'COST_FP_TYPE_ID',
155 pji_utils.get_setup_parameter('COST_FP_TYPE_ID')
156 );
157
158 pji_process_util.set_process_parameter(
159 PJI_RM_SUM_MAIN.g_process,
160 'REVENUE_FP_TYPE_ID',
161 pji_utils.get_setup_parameter('REVENUE_FP_TYPE_ID')
162 );
163
164 pji_process_util.set_process_parameter(
165 PJI_RM_SUM_MAIN.g_process,
166 'COST_FORECAST_FP_TYPE_ID',
167 pji_utils.get_setup_parameter('COST_FORECAST_FP_TYPE_ID')
168 );
169
170 pji_process_util.set_process_parameter(
171 PJI_RM_SUM_MAIN.g_process,
172 'REVENUE_FORECAST_FP_TYPE_ID',
173 pji_utils.get_setup_parameter('REVENUE_FORECAST_FP_TYPE_ID')
174 );
175
176
177 pji_utils.write2log('Completed PJI_FM_PLAN_EXTR.INIT_GLOBAL_PARAMETERS',TRUE,3);
178
179 end init_global_parameters;
180
181
182 -- -----------------------------------------------------
183 -- procedure CLEANUP
184 -- -----------------------------------------------------
185
186 procedure cleanup(p_worker_id number) is
187
188 begin
189
190 pji_utils.write2log('Entering PJI_FM_PLAN_EXTR.CLEANUP',TRUE, 3);
191
192
193 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_EXTR_PLNVER1', 'NORMAL',null);
194 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_EXTR_PLAN', 'NORMAL',null);
195 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_EXTR_PLNVER2', 'NORMAL',null);
196 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_AGGR_PLN', 'NORMAL',null);
197
198
199 pji_utils.write2log('Completed PJI_FM_PLAN_EXTR.CLEANUP',TRUE, 3);
200
201 end cleanup;
202
203 -- -----------------------------------------------------
204 -- procedure CLEANUP_LOG
205 -- -----------------------------------------------------
206
207 procedure cleanup_log is
208
209 begin
210
211 pji_utils.write2log('Entering PJI_FM_PLAN_EXTR.CLEANUP_LOG',TRUE, 3);
212
213 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( pji_utils.get_pji_schema_name , 'PJI_FM_EXTR_PLN_LOG', 'NORMAL',null);
214
215 pji_utils.write2log('Completed PJI_FM_PLAN_EXTR.CLEANUP_LOG',TRUE, 3);
216
217 end cleanup_log;
218
219
220 -- -----------------------------------------------------
221 -- procedure UPDATE_PLAN_ORG_INFO
222 -- -----------------------------------------------------
223
224 procedure update_plan_org_info(p_worker_id number) is
225
226 l_process varchar2(30);
227
228 begin
229
230 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
231
232 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
233 (l_process,'PJI_FM_PLAN_EXTR.UPDATE_PLAN_ORG_INFO(p_worker_id);')) then
234 return;
235 end if;
236
237 get_system_settings;
238
239 Update PJI_ORG_EXTR_INFO orginfo
240 set
241 ( orginfo.PA_FIRST_PERIOD_ID
242 , orginfo.PA_FIRST_PERIOD_NAME
243 , orginfo.PA_FIRST_START_DATE
244 , orginfo.PA_FIRST_END_DATE
245 , orginfo.PROJFUNC_CURRENCY_MAU
246 ) =
247 ( select
248 cal_pa.cal_period_id
249 , cal_pa.name
250 , to_number(to_char(cal_pa.start_date,'J'))
251 , to_number(to_char(cal_pa.end_date,'J'))
252 , nvl( curr.minimum_accountable_unit
253 , power( 10, (-1 * curr.precision)))
254 from fii_time_cal_period cal_pa
255 , fnd_currencies curr
256 where cal_pa.calendar_id = orginfo.pa_calendar_id
257 and DECODE(sign(g_global_start_J - orginfo.pa_calendar_min_date)
258 , 1, g_global_start_date
259 , to_char(to_date(orginfo.pa_calendar_min_date,'J'),'DD-MON-YYYY')
260 ) between cal_pa.start_date and cal_pa.end_date
261 and orginfo.pf_currency_code = curr.currency_code
262 )
263 ;
264
265
266 Update PJI_ORG_EXTR_INFO orginfo
267 set
268 ( orginfo.GL_FIRST_PERIOD_ID
269 , orginfo.GL_FIRST_PERIOD_NAME
270 , orginfo.GL_FIRST_START_DATE
271 , orginfo.GL_FIRST_END_DATE
272 ) =
273 ( select
274 cal_gl.cal_period_id
275 , cal_gl.name
276 , to_number(to_char(cal_gl.start_date,'J'))
277 , to_number(to_char(cal_gl.end_date,'J'))
278 from fii_time_cal_period cal_gl
279 where cal_gl.calendar_id = orginfo.gl_calendar_id
280 and DECODE(sign(g_global_start_J - orginfo.gl_calendar_min_date)
281 , 1, g_global_start_date
282 , to_char(to_date(orginfo.gl_calendar_min_date,'J'),'DD-MON-YYYY')
283 ) between cal_gl.start_date and cal_gl.end_date
284 )
285 ;
286
287 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
288 (l_process, 'PJI_FM_PLAN_EXTR.UPDATE_PLAN_ORG_INFO(p_worker_id);');
289
290 commit;
291
292 end update_plan_org_info;
293
294
295
296 -- -----------------------------------------------------
297 -- procedure EXTRACT_PLAN_VERSIONS
298 -- -----------------------------------------------------
299
300 procedure extract_plan_versions(p_worker_id number) is
301
302 l_process varchar2(30);
303
304 begin
305
306 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
307
308 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
309 (l_process,'PJI_FM_PLAN_EXTR.EXTRACT_PLAN_VERSIONS(p_worker_id);')) then
310 return;
311 end if;
312
313 -- Get Financial Plan versions
314
315 Insert into PJI_FM_EXTR_PLNVER1
316 ( WORKER_ID
317 , PROJECT_ID
318 , PROJECT_ORGANIZATION_ID
319 , PROJECT_ORG_ID
320 , VERSION_ID
321 , PLAN_TYPE_CODE
322 , TIME_PHASED_TYPE_CODE
323 , CURRENT_FLAG
324 , CURRENT_ORIGINAL_FLAG
325 , DANGLING_FLAG
326 , PROJECT_TYPE_CLASS
327 )
328 Select /*+ ORDERED
329 full(bv) use_hash(bv) parallel(bv)
330 full(fpo) use_hash(fpo) swap_join_inputs(fpo)
331 */
332 map.WORKER_id worker_id
333 , map.project_id project_id
334 , map.project_organization_id project_organization_id
335 , map.project_org_id project_org_id
336 , bv.budget_version_id version_id
337 , to_char(fpo.fin_plan_type_id) plan_type_code
338 ,DECODE(bv.version_type
339 , 'ALL', fpo.all_time_phased_code
340 , 'COST', fpo.cost_time_phased_code
341 , 'REVENUE', fpo.revenue_time_phased_code
342 ) time_phased_type_code
343 , bv.current_flag current_flag
344 , bv.current_original_flag current_original_flag
345 , null dangling_flag
346 , map.project_type_class project_type_class
347 From
348 PJI_PJI_PROJ_BATCH_MAP map
349 , pa_budget_versions bv
350 , pa_proj_fp_options fpo
351 Where 1=1
352 and map.worker_id = p_worker_id
353 and map.project_id = bv.project_id
354 and (
355 ( bv.current_flag = 'Y'
356 and to_char(fpo.fin_plan_type_id) in
357 (
358 g_cost_fp_type_code,
359 g_cost_forecast_fp_type_code,
360 g_rev_fp_type_code,
361 g_rev_forecast_fp_type_code
362 )
363 )
364 or
365 (
366 bv.current_original_flag = 'Y'
367 and to_char(fpo.fin_plan_type_id) in
368 (
369 g_cost_fp_type_code,
370 g_rev_fp_type_code
371 )
372 )
373 )
374 and bv.budget_version_id <> map.cost_budget_c_version
375 and bv.budget_version_id <> map.cost_budget_co_version
376 and bv.budget_version_id <> map.revenue_budget_c_version
377 and bv.budget_version_id <> map.revenue_budget_co_version
378 and bv.budget_version_id <> map.cost_forecast_c_version
379 and bv.budget_version_id <> map.revenue_forecast_c_version
380 and bv.version_type is not null
381 and bv.fin_plan_type_id is not null
382 and fpo.project_id = bv.project_id
383 and bv.fin_plan_type_id = fpo.fin_plan_type_id
384 and bv.budget_version_id = fpo.fin_plan_version_id
385 and fpo.fin_plan_option_level_code = 'PLAN_VERSION'
386 ;
387
388 -- Get budget versions for those projects without Financial Plans
389
390 Insert /*+ APPEND */ into PJI_FM_EXTR_PLNVER1
391 ( WORKER_ID
392 , PROJECT_ID
393 , PROJECT_ORGANIZATION_ID
394 , PROJECT_ORG_ID
395 , VERSION_ID
396 , PLAN_TYPE_CODE
397 , TIME_PHASED_TYPE_CODE
398 , CURRENT_FLAG
399 , CURRENT_ORIGINAL_FLAG
400 , DANGLING_FLAG
401 , PROJECT_TYPE_CLASS
402 )
403 Select /*+ ORDERED
404 full(bv) use_hash(bv) parallel(bv)
405 full(bem) use_hash(bem) swap_join_inputs(bem)
406 full(pln) use_hash(pln) swap_join_inputs(pln)
407 */
408 map.worker_id worker_id
409 , map.project_id project_id
410 , map.project_organization_id project_organization_id
411 , map.project_org_id project_org_id
412 , bv.budget_version_id version_id
413 , bv.budget_type_code plan_type_code
414 , decode(bem.time_phased_type_code
415 , 'R', 'N'
416 , bem.time_phased_type_code) time_phased_type_code
417 , bv.current_flag current_flag
418 , bv.current_original_flag current_original_flag
419 , null dangling_flag
420 , map.project_type_class project_type_class
421 From
422 PJI_PJI_PROJ_BATCH_MAP map
423 , pa_budget_versions bv
424 , pa_budget_entry_methods bem
425 , (select distinct
426 PROJECT_ID
427 from PJI_FM_EXTR_PLNVER1
428 where WORKER_ID = p_worker_id) pln
429 Where 1=1
430 and map.worker_id = p_worker_id
431 and map.project_id = bv.project_id
432 and bem.budget_entry_method_code = bv.budget_entry_method_code
433 and (
434 ( bv.current_flag = 'Y'
435 and bv.budget_type_code in
436 (
437 g_cost_budget_type_code,
438 g_cost_forecast_type_code,
439 g_revenue_budget_type_code,
440 g_revenue_forecast_type_code
441 )
442 )
443 or
444 (
445 bv.current_original_flag = 'Y'
446 and bv.budget_type_code in
447 (
448 g_cost_budget_type_code,
449 g_revenue_budget_type_code
450 )
451 )
452 )
453 and bv.budget_version_id <> map.cost_budget_c_version
454 and bv.budget_version_id <> map.cost_budget_co_version
455 and bv.budget_version_id <> map.revenue_budget_c_version
456 and bv.budget_version_id <> map.revenue_budget_co_version
457 and bv.budget_version_id <> map.cost_forecast_c_version
458 and bv.budget_version_id <> map.revenue_forecast_c_version
459 and bv.version_type is null
460 and bv.fin_plan_type_id is null
461 and map.project_id = pln.project_id (+)
462 and pln.project_id is null;
463
464 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
465 (l_process, 'PJI_FM_PLAN_EXTR.EXTRACT_PLAN_VERSIONS(p_worker_id);');
466
467 commit;
468
469 end extract_plan_versions;
470
471
472
473 -- -----------------------------------------------------
474 -- procedure EXTRACT_BATCH_PLAN
475 -- -----------------------------------------------------
476
477 procedure extract_batch_plan(p_worker_id number) is
478
479 l_process varchar2(30);
480 l_degree number;
481
482 begin
483
484 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
485
486 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
487 (l_process,'PJI_FM_PLAN_EXTR.EXTRACT_BATCH_PLAN(p_worker_id);')) then
488 return;
489 end if;
490
491 l_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
492
493 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
494 'CURRENT_BATCH') = 1) then
495 FND_STATS.GATHER_TABLE_STATS(
496 ownname => pji_utils.get_pji_schema_name
497 , tabname => 'PJI_FM_EXTR_PLNVER1'
498 , percent => 10
499 , degree => l_degree
500 );
501 end if;
502
503
504 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- for OF slice
505 ( LINE_TYPE
506 , CALENDAR_TYPE_CODE
507 , WORKER_ID
508 , PROJECT_ID
509 , PROJECT_ORG_ID
510 , PF_CURRENCY_CODE
511 , VERSION_ID
512 , PLAN_TYPE_CODE
513 , CURRENCY_TYPE
514 , PERIOD_ID
515 , PERIOD_NAME
516 , START_DATE
517 , END_DATE
518 , REVENUE
519 , RAW_COST
520 , BURDENED_COST
521 , LABOR_HRS
522 , TIME_DANGLING_FLAG
523 , RATE_DANGLING_FLAG
524 , RATE2_DANGLING_FLAG
525 )
526 select /*+ ORDERED
527 full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
528 full(vers) use_hash(vers) parallel(vers)
529 full(fii_time) use_hash(fii_time) swap_join_inputs(fii_time)
530 */
531 decode(vers.time_phased_type_code
532 , 'P', 'OF'
533 , 'G', 'OF'
534 , 'F1') line_type
535 , decode(vers.time_phased_type_code
536 , 'P', 'PA'
537 , 'G', 'GL'
538 , 'ENT') calendar_type_code
539 , p_worker_id worker_id
540 , vers.project_id project_id
541 , vers.project_org_id project_org_id
542 , orginfo.pf_currency_code pf_currency_code
543 , vers.version_id version_id
544 , vers.plan_type_code plan_type_code
545 , 'F' currency_type
546 , decode(vers.time_phased_type_code
547 , 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
548 , -1, orginfo.pa_first_period_id
549 , fii_time.period_id)
550 , 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
551 , -1, orginfo.gl_first_period_id
552 , fii_time.period_id)
553 , decode(sign(bl.end_date - g_ent_end_date)
554 , -1, g_ent_start_period_id
555 , -1)
556 ) period_id
557 , decode(vers.time_phased_type_code
558 , 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
559 , -1, orginfo.pa_first_period_name
560 , fii_time.period_name)
561 , 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
562 , -1, orginfo.gl_first_period_name
563 , fii_time.period_name)
564 , decode(sign(bl.end_date - g_ent_start_date)
565 , -1, g_ent_start_period_name
566 , PJI_RM_SUM_MAIN.g_null)
567 ) period_name
568 , decode(vers.time_phased_type_code
569 , 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
570 , -1, to_date(orginfo.pa_first_start_date,'J')
571 , fii_time.start_date)
572 , 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
573 , -1, to_date(orginfo.gl_first_start_date,'J')
574 , fii_time.start_date)
575 , decode(sign(bl.end_date - g_ent_start_date)
576 , -1, g_ent_start_date
577 , bl.start_date)
578 ) start_date
579 , decode(vers.time_phased_type_code
580 , 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
581 , -1, to_date(orginfo.pa_first_end_date,'J')
582 , fii_time.end_date)
583 , 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
584 , -1, to_date(orginfo.gl_first_end_date,'J')
585 , fii_time.end_date)
586 , decode(sign(bl.end_date - g_ent_end_date)
587 , -1, g_ent_end_date
588 , bl.end_date)
589 ) end_date
590 , decode(vers.plan_type_code
591 , g_revenue_budget_type_code , nvl(bl.revenue,to_number(null))
592 , g_revenue_forecast_type_code , nvl(bl.revenue,to_number(null))
593 , g_rev_fp_type_code , nvl(bl.revenue,to_number(null))
594 , g_rev_forecast_fp_type_code , nvl(bl.revenue,to_number(null))
595 , to_number(null)
596 ) revenue
597 , decode(vers.plan_type_code
598 , g_cost_budget_type_code , nvl(bl.raw_cost,to_number(null))
599 , g_cost_forecast_type_code , nvl(bl.raw_cost,to_number(null))
600 , g_cost_fp_type_code , nvl(bl.raw_cost,to_number(null))
601 , g_cost_forecast_fp_type_code , nvl(bl.raw_cost,to_number(null))
602 , to_number(null)
603 ) raw_cost
604 , decode(vers.plan_type_code
605 , g_cost_budget_type_code , nvl(bl.burdened_cost,to_number(null))
606 , g_cost_forecast_type_code , nvl(bl.burdened_cost,to_number(null))
607 , g_cost_fp_type_code , nvl(bl.burdened_cost,to_number(null))
608 , g_cost_forecast_fp_type_code , nvl(bl.burdened_cost,to_number(null))
609 , to_number(null)
610 ) burdened_cost
611 , decode(nvl(ra.track_as_labor_flag,'Y')
612 , 'Y', nvl(bl.quantity,to_number(null))
613 , to_number(null)
614 ) labor_hrs
615 , decode(sign(to_date(orginfo.pa_calendar_max_date,'J') - fii_time.end_date)
616 , -1, 'P'
617 , null)||
618 decode(sign(to_date(orginfo.gl_calendar_max_date,'J') - fii_time.end_date)
619 , -1, 'G'
620 , null)||
621 decode(sign(to_date(orginfo.en_calendar_max_date,'J') - bl.end_date)
622 , -1, 'E'
623 , null) time_dangling_flag
624 , null rate_dangling_flag
625 , null rate2_dangling_flag
626 from
627 PJI_ORG_EXTR_INFO orginfo
628 , PJI_FM_EXTR_PLNVER1 vers
629 , pa_resource_assignments ra
630 , pa_budget_lines bl
631 , ( select -1 calendar_id
632 , -1 period_id
633 , PJI_RM_SUM_MAIN.g_null period_name
634 , null start_date
635 , null end_date
636 from dual
637 union all
638 select calendar_id calendar_id
639 , cal_period_id period_id
640 , name period_name
641 , start_date start_date
642 , end_date end_date
643 from fii_time_cal_period
644 ) fii_time
645 where 1=1
646 and orginfo.projfunc_currency_mau is not null
647 and vers.worker_id = p_worker_id
648 and vers.project_org_id = orginfo.org_id
649 and ra.project_id = vers.project_id
650 and ra.budget_version_id = vers.version_id
651 and ra.resource_assignment_id = bl.resource_assignment_id
652 and decode(vers.time_phased_type_code
653 , 'P', orginfo.pa_calendar_id
654 , 'G', orginfo.gl_calendar_id
655 , -1 ) = fii_time.calendar_id
656 and decode(vers.time_phased_type_code
657 , 'P', decode(sign(bl.end_date - to_date(orginfo.pa_first_end_date,'J'))
658 , -1, orginfo.pa_first_period_name
659 , bl.period_name)
660 , 'G', decode(sign(bl.end_date - to_date(orginfo.gl_first_end_date,'J'))
661 , -1, orginfo.gl_first_period_name
662 , bl.period_name)
663 , PJI_RM_SUM_MAIN.g_null
664 ) = fii_time.period_name
665 and decode(vers.time_phased_type_code
666 , 'P', orginfo.pa_first_period_id
667 , 'G', orginfo.gl_first_period_id
668 , -1
669 ) <= fii_time.period_id
670 ;
671
672 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
673 (l_process, 'PJI_FM_PLAN_EXTR.EXTRACT_BATCH_PLAN(p_worker_id);');
674
675 commit;
676
677 end extract_batch_plan;
678
679
680
681 -- -----------------------------------------------------
682 -- procedure SPREAD_ENT_PLANS
683 -- -----------------------------------------------------
684
685 procedure spread_ent_plans(p_worker_id number) is
686
687 l_process varchar2(30);
688
689 begin
690
691 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
692
693 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
694 (l_process,'PJI_FM_PLAN_EXTR.SPREAD_ENT_PLANS(p_worker_id);')) then
695 return;
696 end if;
697
698 -- spread the ENT amounts
699 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- OF slice for ENT
700 ( LINE_TYPE
701 , CALENDAR_TYPE_CODE
702 , WORKER_ID
703 , PROJECT_ID
704 , PROJECT_ORG_ID
705 , PF_CURRENCY_CODE
706 , VERSION_ID
707 , PLAN_TYPE_CODE
708 , CURRENCY_TYPE
709 , PERIOD_ID
710 , PERIOD_NAME
711 , START_DATE
712 , END_DATE
713 , REVENUE
714 , RAW_COST
715 , BURDENED_COST
716 , LABOR_HRS
717 , TIME_DANGLING_FLAG
718 , RATE_DANGLING_FLAG
719 , RATE2_DANGLING_FLAG
720 )
721 select /*+ ORDERED
722 full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
723 full(tmp) use_hash(tmp) parallel(tmp)
724 full(ent) use_hash(ent) swap_join_inputs(ent)
725 */
726 'OF' line_type
727 , tmp.calendar_type_code calendar_type_code
728 , tmp.worker_id
729 , tmp.project_id
730 , tmp.project_org_id
731 , tmp.pf_currency_code
732 , tmp.version_id
733 , tmp.plan_type_code plan_type_code
734 , tmp.currency_type currency_type
735 , ent.ent_period_id period_id
736 , ent.name period_name
737 , ent.start_date start_date
738 , ent.end_date end_date
739 , round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
740 (tmp.end_date >= ent.end_date)
741 then (ent.end_date - ent.start_date + 1) *
742 tmp.revenue / (tmp.end_date - tmp.start_date+1)
743 when (ent.start_date <= tmp.start_date) and
744 (ent.end_date <= tmp.end_date )
745 then (ent.end_date - tmp.start_date + 1) *
746 tmp.revenue / (tmp.end_date - tmp.start_date+1)
747 when (ent.start_date >= tmp.start_date) and
748 (ent.end_date >= tmp.end_date)
749 then (tmp.end_date - ent.start_date + 1) *
750 tmp.revenue / (tmp.end_date - tmp.start_date+1)
751 when (ent.start_date <= tmp.start_date) and
752 (ent.end_date >= tmp.end_date)
753 then tmp.revenue
754 else to_number(null)
755 end,to_number(null)))/orginfo.projfunc_currency_mau
756 )*orginfo.projfunc_currency_mau revenue
757 , round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
758 (tmp.end_date >= ent.end_date)
759 then (ent.end_date - ent.start_date + 1) *
760 tmp.raw_cost /(tmp.end_date - tmp.start_date+1)
761 when (ent.start_date <= tmp.start_date) and
762 (ent.end_date <= tmp.end_date )
763 then (ent.end_date - tmp.start_date + 1) *
764 tmp.raw_cost /(tmp.end_date - tmp.start_date+1)
765 when (ent.start_date >= tmp.start_date) and
766 (ent.end_date >= tmp.end_date)
767 then (tmp.end_date - ent.start_date + 1) *
768 tmp.raw_cost /(tmp.end_date - tmp.start_date+1)
769 when (ent.start_date <= tmp.start_date) and
770 (ent.end_date >= tmp.end_date)
771 then tmp.raw_cost
772 else to_number(null)
773 end,to_number(null)))/orginfo.projfunc_currency_mau
774 )*orginfo.projfunc_currency_mau raw_cost
775 , round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
776 (tmp.end_date >= ent.end_date)
777 then (ent.end_date - ent.start_date + 1) *
778 tmp.burdened_cost/(tmp.end_date-tmp.start_date+1)
779 when (ent.start_date <= tmp.start_date) and
780 (ent.end_date <= tmp.end_date )
781 then (ent.end_date - tmp.start_date + 1) *
782 tmp.burdened_cost/(tmp.end_date-tmp.start_date+1)
783 when (ent.start_date >= tmp.start_date) and
784 (ent.end_date >= tmp.end_date)
785 then (tmp.end_date - ent.start_date + 1) *
786 tmp.burdened_cost/(tmp.end_date-tmp.start_date+1)
787 when (ent.start_date <= tmp.start_date) and
788 (ent.end_date >= tmp.end_date)
789 then tmp.burdened_cost
790 else to_number(null)
791 end,to_number(null)))/orginfo.projfunc_currency_mau
792 )*orginfo.projfunc_currency_mau burdened_cost
793 , round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
794 (tmp.end_date >= ent.end_date)
795 then (ent.end_date - ent.start_date + 1) *
796 tmp.labor_hrs/(tmp.end_date - tmp.start_date+1)
797 when (ent.start_date <= tmp.start_date) and
798 (ent.end_date <= tmp.end_date )
799 then (ent.end_date - tmp.start_date + 1) *
800 tmp.labor_hrs/(tmp.end_date - tmp.start_date+1)
801 when (ent.start_date >= tmp.start_date) and
802 (ent.end_date >= tmp.end_date)
803 then (tmp.end_date - ent.start_date + 1) *
804 tmp.labor_hrs/(tmp.end_date - tmp.start_date+1)
805 when (ent.start_date <= tmp.start_date) and
806 (ent.end_date >= tmp.end_date)
807 then tmp.labor_hrs
808 else to_number(null)
809 end,to_number(null)))/g_labor_mau
810 )*g_labor_mau labor_hrs
811 , decode(sign(to_date(orginfo.en_calendar_max_date,'J') - ent.end_date)
812 , -1, 'E'
813 , null
814 ) time_dangling_flag
815 , tmp.rate_dangling_flag rate_dangling_flag
816 , tmp.rate2_dangling_flag rate2_dangling_flag
817 from
818 PJI_ORG_EXTR_INFO orginfo
819 , PJI_FM_EXTR_PLAN tmp
820 , fii_time_ent_period ent
821 where tmp.worker_id = p_worker_id
822 and tmp.end_date >= ent.start_date
823 and tmp.start_date <= ent.end_date
824 and tmp.line_type = 'F1'
825 and tmp.calendar_type_code = 'ENT'
826 and tmp.project_org_id = orginfo.org_id
827 and tmp.time_dangling_flag is null
828 group by
829 tmp.worker_id
830 , tmp.project_id
831 , tmp.project_org_id
832 , tmp.pf_currency_code
833 , tmp.version_id
834 , tmp.plan_type_code
835 , tmp.calendar_type_code
836 , tmp.currency_type
837 , ent.ent_period_id
838 , ent.name
839 , ent.start_date
840 , ent.end_date
841 , tmp.start_date
842 , tmp.end_date
843 , tmp.time_dangling_flag
844 , tmp.rate_dangling_flag
845 , tmp.rate2_dangling_flag
846 , orginfo.projfunc_currency_mau
847 , orginfo.en_calendar_max_date
848 ;
849
850 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
851 (l_process, 'PJI_FM_PLAN_EXTR.SPREAD_ENT_PLANS(p_worker_id);');
852
853 commit;
854
855 end spread_ent_plans;
856
857
858
859 -- -----------------------------------------------------
860 -- procedure PLAN_CURR_CONV_TABLE
861 -- -----------------------------------------------------
862
863 procedure plan_curr_conv_table(p_worker_id number) is
864
865 l_process varchar2(30);
866 l_mau number;
867 l_mau2 number;
868
869 begin
870
871 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
872
873 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
874 (l_process, 'PJI_FM_PLAN_EXTR.PLAN_CURR_CONV_TABLE(p_worker_id);')) then
875 return;
876 end if;
877
878 l_mau := PJI_UTILS.GET_MAU_PRIMARY;
879 l_mau2 := PJI_UTILS.GET_MAU_SECONDARY;
880
881 insert /*+ append */ into PJI_FM_AGGR_DLY_RATES -- for curr conv
882 (
883 WORKER_ID,
884 PF_CURRENCY_CODE,
885 TIME_ID,
886 RATE,
887 MAU,
888 RATE2,
889 MAU2
890 )
891 select
892 -1 worker_id,
893 tmp.pf_currency_code pf_currency_code,
894 to_char(tmp.curr_date,'J') time_id,
895 PJI_UTILS.GET_GLOBAL_RATE_PRIMARY(tmp.pf_currency_code,
896 tmp.curr_date) rate,
897 l_mau mau,
898 PJI_UTILS.GET_GLOBAL_RATE_SECONDARY(tmp.pf_currency_code,
899 tmp.curr_date) rate,
900 l_mau2 mau2
901 from
902 (
903 select
904 distinct
905 tmp.pf_currency_code pf_currency_code,
906 decode(invert.rule,
907 'F', tmp.start_date,
908 'L', tmp.end_date) curr_date
909 from
910 PJI_FM_EXTR_PLAN tmp,
911 (
912 select 'F' rule from dual union all
913 select 'L' rule from dual
914 ) invert
915 where
916 worker_id = p_worker_id and
917 time_dangling_flag is null
918 ) tmp;
919
920 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
921 (l_process, 'PJI_FM_PLAN_EXTR.PLAN_CURR_CONV_TABLE(p_worker_id);');
922
923 commit;
924
925 end plan_curr_conv_table;
926
927
928
929 -- -----------------------------------------------------
930 -- procedure CONVERT_TO_GLOBAL_CURRENCY
931 -- -----------------------------------------------------
932
933 procedure convert_to_global_currency(p_worker_id number) is
934
935 l_process varchar2(30);
936
937 begin
938
939 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
940
941 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
942 (l_process,'PJI_FM_PLAN_EXTR.CONVERT_TO_GLOBAL_CURRENCY(p_worker_id);')) then
943 return;
944 end if;
945
946 -- convert to GLOBAL CURRENCY
947 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GLOBAL CURRENCY
948 ( LINE_TYPE
949 , CALENDAR_TYPE_CODE
950 , WORKER_ID
951 , PROJECT_ID
952 , PROJECT_ORG_ID
953 , PF_CURRENCY_CODE
954 , VERSION_ID
955 , PLAN_TYPE_CODE
956 , CURRENCY_TYPE
957 , PERIOD_ID
958 , PERIOD_NAME
959 , START_DATE
960 , END_DATE
961 , REVENUE
962 , RAW_COST
963 , BURDENED_COST
964 , LABOR_HRS
965 , TIME_DANGLING_FLAG
966 , RATE_DANGLING_FLAG
967 , RATE2_DANGLING_FLAG
968 )
969 SELECT /*+ ORDERED
970 full(rates) use_hash(rates) swap_join_inputs(rates)
971 full(tmp) use_hash(tmp) parallel(tmp)
972 */
973 'OG' LINE_TYPE
974 , tmp.calendar_type_code
975 , tmp.worker_id
976 , tmp.project_id
977 , tmp.project_org_id
978 , tmp.pf_currency_code
979 , tmp.version_id
980 , tmp.plan_type_code
981 , 'G' currency_type
982 , tmp.period_id
983 , tmp.period_name
984 , tmp.start_date
985 , tmp.end_date
986 , round(rates.rate*tmp.revenue/rates.mau)*rates.mau revenue
987 , round(rates.rate*tmp.raw_cost/rates.mau)*rates.mau raw_cost
988 , round(rates.rate*tmp.burdened_cost/rates.mau)*rates.mau burdened_cost
989 , tmp.labor_hrs labor_hrs
990 , tmp.time_dangling_flag
991 , case when rates.rate > 0
992 then null
993 when rates.rate = -3
994 then 'U' -- EUR conversion rate for 01-JAN-1999 is missing
995 else
996 decode(tmp.plan_type_code
997 , g_cost_budget_type_code, decode(g_cost_budget_curr_rule
998 , 'F', 'F'
999 , 'E')
1000 , g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
1001 , 'F', 'F'
1002 , 'E')
1003 , g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
1004 , 'F', 'F'
1005 , 'E')
1006 , g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
1007 , 'F', 'F'
1008 , 'E')
1009 , g_cost_fp_type_code, decode(g_cost_budget_curr_rule
1010 , 'F', 'F'
1011 , 'E')
1012 , g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
1013 , 'F', 'F'
1014 , 'E')
1015 , g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
1016 , 'F', 'F'
1017 , 'E')
1018 , g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
1019 , 'F', 'F'
1020 , 'E')
1021 ) end rate_dangling_flag
1022 , null rate2_dangling_flag
1023 FROM
1024 PJI_FM_AGGR_DLY_RATES rates
1025 , PJI_FM_EXTR_PLAN tmp
1026 where tmp.WORKER_ID = p_worker_id
1027 and tmp.LINE_TYPE = 'OF'
1028 and rates.worker_id = -1
1029 and tmp.pf_currency_code = rates.pf_currency_code
1030 and decode(tmp.plan_type_code
1031 , g_cost_budget_type_code, decode(g_cost_budget_curr_rule
1032 , 'F', tmp.start_date
1033 , tmp.end_date)
1034 , g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
1035 , 'F', tmp.start_date
1036 , tmp.end_date)
1037 , g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
1038 , 'F', tmp.start_date
1039 , tmp.end_date)
1040 , g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
1041 , 'F', tmp.start_date
1042 , tmp.end_date)
1043 , g_cost_fp_type_code, decode(g_cost_budget_curr_rule
1044 , 'F', tmp.start_date
1045 , tmp.end_date)
1046 , g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
1047 , 'F', tmp.start_date
1048 , tmp.end_date)
1049 , g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
1050 , 'F', tmp.start_date
1051 , tmp.end_date)
1052 , g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
1053 , 'F', tmp.start_date
1054 , tmp.end_date)
1055 ) = to_date(rates.time_id,'J')
1056 and tmp.time_dangling_flag is null
1057 and rates.time_id > 0
1058 ;
1059
1060 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1061 (l_process, 'PJI_FM_PLAN_EXTR.CONVERT_TO_GLOBAL_CURRENCY(p_worker_id);');
1062
1063 commit;
1064
1065 end convert_to_global_currency;
1066
1067
1068
1069 -- -----------------------------------------------------
1070 -- procedure CONVERT_TO_GLOBAL2_CURRENCY
1071 -- -----------------------------------------------------
1072
1073 procedure convert_to_global2_currency(p_worker_id number) is
1074
1075 l_process varchar2(30);
1076
1077 begin
1078
1079 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1080
1081 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1082 (l_process,'PJI_FM_PLAN_EXTR.CONVERT_TO_GLOBAL2_CURRENCY(p_worker_id);')) then
1083 return;
1084 end if;
1085
1086 -- convert to GLOBAL CURRENCY
1087 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GLOBAL CURRENCY
1088 ( LINE_TYPE
1089 , CALENDAR_TYPE_CODE
1090 , WORKER_ID
1091 , PROJECT_ID
1092 , PROJECT_ORG_ID
1093 , PF_CURRENCY_CODE
1094 , VERSION_ID
1095 , PLAN_TYPE_CODE
1096 , CURRENCY_TYPE
1097 , PERIOD_ID
1098 , PERIOD_NAME
1099 , START_DATE
1100 , END_DATE
1101 , REVENUE
1102 , RAW_COST
1103 , BURDENED_COST
1104 , LABOR_HRS
1105 , TIME_DANGLING_FLAG
1106 , RATE_DANGLING_FLAG
1107 , RATE2_DANGLING_FLAG
1108 )
1109 SELECT /*+ ORDERED
1110 full(rates) use_hash(rates) swap_join_inputs(rates)
1111 full(tmp) use_hash(tmp) parallel(tmp)
1112 */
1113 'OG' LINE_TYPE
1114 , tmp.calendar_type_code
1115 , tmp.worker_id
1116 , tmp.project_id
1117 , tmp.project_org_id
1118 , tmp.pf_currency_code
1119 , tmp.version_id
1120 , tmp.plan_type_code
1121 , '2' currency_type
1122 , tmp.period_id
1123 , tmp.period_name
1124 , tmp.start_date
1125 , tmp.end_date
1126 , round(rates.rate2*tmp.revenue/rates.mau2)*rates.mau2 revenue
1127 , round(rates.rate2*tmp.raw_cost/rates.mau2)*rates.mau2 raw_cost
1128 , round(rates.rate2*tmp.burdened_cost/rates.mau2)*rates.mau2 burdened_cost
1129 , tmp.labor_hrs labor_hrs
1130 , tmp.time_dangling_flag
1131 , null rate_dangling_flag
1132 , case when rates.rate2 > 0
1133 then null
1134 when rates.rate2 = -3
1135 then 'U' -- EUR conversion rate for 01-JAN-1999 is missing
1136 else
1137 decode(tmp.plan_type_code
1138 , g_cost_budget_type_code, decode(g_cost_budget_curr_rule
1139 , 'F', 'F'
1140 , 'E')
1141 , g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
1142 , 'F', 'F'
1143 , 'E')
1144 , g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
1145 , 'F', 'F'
1146 , 'E')
1147 , g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
1148 , 'F', 'F'
1149 , 'E')
1150 , g_cost_fp_type_code, decode(g_cost_budget_curr_rule
1151 , 'F', 'F'
1152 , 'E')
1153 , g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
1154 , 'F', 'F'
1155 , 'E')
1156 , g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
1157 , 'F', 'F'
1158 , 'E')
1159 , g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
1160 , 'F', 'F'
1161 , 'E')
1162 ) end rate2_dangling_flag
1163 FROM
1164 PJI_FM_AGGR_DLY_RATES rates
1165 , PJI_FM_EXTR_PLAN tmp
1166 where tmp.WORKER_ID = p_worker_id
1167 and tmp.LINE_TYPE = 'OF'
1168 and rates.worker_id = -1
1169 and tmp.pf_currency_code = rates.pf_currency_code
1170 and decode(tmp.plan_type_code
1171 , g_cost_budget_type_code, decode(g_cost_budget_curr_rule
1172 , 'F', tmp.start_date
1173 , tmp.end_date)
1174 , g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
1175 , 'F', tmp.start_date
1176 , tmp.end_date)
1177 , g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
1178 , 'F', tmp.start_date
1179 , tmp.end_date)
1180 , g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
1181 , 'F', tmp.start_date
1182 , tmp.end_date)
1183 , g_cost_fp_type_code, decode(g_cost_budget_curr_rule
1184 , 'F', tmp.start_date
1185 , tmp.end_date)
1186 , g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
1187 , 'F', tmp.start_date
1188 , tmp.end_date)
1189 , g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
1190 , 'F', tmp.start_date
1191 , tmp.end_date)
1192 , g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
1193 , 'F', tmp.start_date
1194 , tmp.end_date)
1195 ) = to_date(rates.time_id,'J')
1196 and tmp.time_dangling_flag is null
1197 and rates.time_id > 0
1198 ;
1199
1200 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1201 (l_process, 'PJI_FM_PLAN_EXTR.CONVERT_TO_GLOBAL2_CURRENCY(p_worker_id);');
1202
1203 commit;
1204
1205 end convert_to_global2_currency;
1206
1207
1208
1209 -- -----------------------------------------------------
1210 -- procedure CONVERT_TO_PA_PERIODS
1211 -- -----------------------------------------------------
1212
1213 procedure convert_to_pa_periods(p_worker_id number) is
1214
1215 l_process varchar2(30);
1216
1217 begin
1218
1219 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1220
1221 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1222 (l_process,'PJI_FM_PLAN_EXTR.CONVERT_TO_PA_PERIODS(p_worker_id);')) then
1223 return;
1224 end if;
1225
1226
1227
1228 if g_pa_period_flag = 'Y' then
1229
1230 -- convert to PA periods
1231 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to PA periods
1232 ( LINE_TYPE
1233 , CALENDAR_TYPE_CODE
1234 , WORKER_ID
1235 , PROJECT_ID
1236 , PROJECT_ORG_ID
1237 , PF_CURRENCY_CODE
1238 , VERSION_ID
1239 , PLAN_TYPE_CODE
1240 , CURRENCY_TYPE
1241 , PERIOD_ID
1242 , PERIOD_NAME
1243 , START_DATE
1244 , END_DATE
1245 , REVENUE
1246 , RAW_COST
1247 , BURDENED_COST
1248 , LABOR_HRS
1249 , TIME_DANGLING_FLAG
1250 , RATE_DANGLING_FLAG
1251 , RATE2_DANGLING_FLAG
1252 )
1253 select /*+ ORDERED
1254 full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
1255 full(tmp) use_hash(tmp) parallel(tmp)
1256 full(cal_pa) use_hash(cal_pa) swap_join_inputs(cal_pa)
1257 pq_distribute(cal_pa, none, broadcast)
1258 pq_distribute(tmp, broadcast, none)
1259 */
1260 decode(tmp.LINE_TYPE
1261 , 'OF', 'CF'
1262 , 'OG', 'CG'
1263 ) LINE_TYPE
1264 , 'PA' calendar_type_code
1265 , tmp.worker_id
1266 , tmp.project_id
1267 , tmp.project_org_id project_org_id
1268 , tmp.pf_currency_code pf_currency_code
1269 , tmp.version_id
1270 , tmp.plan_type_code
1271 , tmp.currency_type currency_type
1272 , cal_pa.cal_period_id period_id
1273 , cal_pa.name period_name
1274 , cal_pa.start_date start_date
1275 , cal_pa.end_date end_date
1276 , round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
1277 (tmp.end_date >= cal_pa.end_date)
1278 then (cal_pa.end_date - cal_pa.start_date + 1) *
1279 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1280 when (cal_pa.start_date <= tmp.start_date) and
1281 (cal_pa.end_date <= tmp.end_date )
1282 then (cal_pa.end_date - tmp.start_date + 1) *
1283 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1284 when (cal_pa.start_date >= tmp.start_date) and
1285 (cal_pa.end_date >= tmp.end_date)
1286 then (tmp.end_date - cal_pa.start_date + 1) *
1287 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1288 when (cal_pa.start_date <= tmp.start_date) and
1289 (cal_pa.end_date >= tmp.end_date)
1290 then tmp.revenue
1291 else to_number(null)
1292 end,to_number(null)))/orginfo.projfunc_currency_mau
1293 )*orginfo.projfunc_currency_mau revenue
1294 , round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
1295 (tmp.end_date >= cal_pa.end_date)
1296 then (cal_pa.end_date - cal_pa.start_date + 1) *
1297 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1298 when (cal_pa.start_date <= tmp.start_date) and
1299 (cal_pa.end_date <= tmp.end_date )
1300 then (cal_pa.end_date - tmp.start_date + 1) *
1301 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1302 when (cal_pa.start_date >= tmp.start_date) and
1303 (cal_pa.end_date >= tmp.end_date)
1304 then (tmp.end_date - cal_pa.start_date + 1) *
1305 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1306 when (cal_pa.start_date <= tmp.start_date) and
1307 (cal_pa.end_date >= tmp.end_date)
1308 then tmp.raw_cost
1309 else to_number(null)
1310 end,to_number(null)))/orginfo.projfunc_currency_mau
1311 )*orginfo.projfunc_currency_mau raw_cost
1312 , round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
1313 (tmp.end_date >= cal_pa.end_date)
1314 then (cal_pa.end_date - cal_pa.start_date + 1) *
1315 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1316 when (cal_pa.start_date <= tmp.start_date) and
1317 (cal_pa.end_date <= tmp.end_date )
1318 then (cal_pa.end_date - tmp.start_date + 1) *
1319 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1320 when (cal_pa.start_date >= tmp.start_date) and
1321 (cal_pa.end_date >= tmp.end_date)
1322 then (tmp.end_date - cal_pa.start_date + 1) *
1323 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1324 when (cal_pa.start_date <= tmp.start_date) and
1325 (cal_pa.end_date >= tmp.end_date)
1326 then tmp.burdened_cost
1327 else to_number(null)
1328 end,to_number(null)))/orginfo.projfunc_currency_mau
1329 )*orginfo.projfunc_currency_mau burdened_cost
1330 , round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
1331 (tmp.end_date >= cal_pa.end_date)
1332 then (cal_pa.end_date - cal_pa.start_date + 1) *
1333 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1334 when (cal_pa.start_date <= tmp.start_date) and
1335 (cal_pa.end_date <= tmp.end_date )
1336 then (cal_pa.end_date - tmp.start_date + 1) *
1337 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1338 when (cal_pa.start_date >= tmp.start_date) and
1339 (cal_pa.end_date >= tmp.end_date)
1340 then (tmp.end_date - cal_pa.start_date + 1) *
1341 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1342 when (cal_pa.start_date <= tmp.start_date) and
1343 (cal_pa.end_date >= tmp.end_date)
1344 then tmp.labor_hrs
1345 else to_number(null)
1346 end,to_number(null)))/g_labor_mau
1347 )*g_labor_mau labor_hrs
1348 , decode(sign(to_date(orginfo.pa_calendar_max_date,'J') - cal_pa.end_date)
1349 , -1, 'P'
1350 , tmp.time_dangling_flag
1351 ) time_dangling_flag
1352 , tmp.rate_dangling_flag rate_dangling_flag
1353 , tmp.rate2_dangling_flag rate2_dangling_flag
1354 from
1355 PJI_ORG_EXTR_INFO orginfo
1356 , PJI_FM_EXTR_PLAN tmp
1357 , fii_time_cal_period cal_pa
1358 where tmp.worker_id = p_worker_id
1359 and tmp.end_date >= cal_pa.start_date
1360 and tmp.start_date <= cal_pa.end_date
1361 and tmp.calendar_type_code <> 'PA'
1362 and orginfo.pa_calendar_id = cal_pa.calendar_id
1363 and orginfo.org_id = tmp.project_org_id
1364 and tmp.LINE_TYPE in ( 'OF' , 'OG' )
1365 and tmp.time_dangling_flag is null
1366 and tmp.rate_dangling_flag is null
1367 and tmp.rate2_dangling_flag is null
1368 ;
1369
1370 end if;
1371
1372 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1373 (l_process, 'PJI_FM_PLAN_EXTR.CONVERT_TO_PA_PERIODS(p_worker_id);');
1374
1375 commit;
1376
1377 end convert_to_pa_periods;
1378
1379
1380
1381 -- -----------------------------------------------------
1382 -- procedure CONVERT_TO_GL_PERIODS
1383 -- -----------------------------------------------------
1384
1385 procedure convert_to_gl_periods(p_worker_id number) is
1386
1387 l_process varchar2(30);
1388
1389 begin
1390
1391 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1392
1393 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1394 (l_process,'PJI_FM_PLAN_EXTR.CONVERT_TO_GL_PERIODS(p_worker_id);')) then
1395 return;
1396 end if;
1397
1398
1399
1400 if g_gl_period_flag = 'Y' then
1401
1402 -- convert to GL periods
1403 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GL periods
1404 ( LINE_TYPE
1405 , CALENDAR_TYPE_CODE
1406 , WORKER_ID
1407 , PROJECT_ID
1408 , PROJECT_ORG_ID
1409 , PF_CURRENCY_CODE
1410 , VERSION_ID
1411 , PLAN_TYPE_CODE
1412 , CURRENCY_TYPE
1413 , PERIOD_ID
1414 , PERIOD_NAME
1415 , START_DATE
1416 , END_DATE
1417 , REVENUE
1418 , RAW_COST
1419 , BURDENED_COST
1420 , LABOR_HRS
1421 , TIME_DANGLING_FLAG
1422 , RATE_DANGLING_FLAG
1423 , RATE2_DANGLING_FLAG
1424 )
1425 select /*+ ORDERED
1426 full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
1427 full(tmp) use_hash(tmp) parallel(tmp)
1428 full(cal_gl) use_hash(cal_gl) swap_join_inputs(cal_gl)
1429 pq_distribute(cal_gl, none, broadcast)
1430 pq_distribute(tmp, broadcast, none)
1431 */
1432 decode(tmp.LINE_TYPE
1433 , 'OF', 'CF'
1434 , 'OG', 'CG'
1435 ) LINE_TYPE
1436 , 'GL' calendar_type_code
1437 , tmp.worker_id
1438 , tmp.project_id
1439 , tmp.project_org_id project_org_id
1440 , tmp.pf_currency_code pf_currency_code
1441 , tmp.version_id
1442 , tmp.plan_type_code
1443 , tmp.currency_type currency_type
1444 , cal_gl.cal_period_id period_id
1445 , cal_gl.name period_name
1446 , cal_gl.start_date start_date
1447 , cal_gl.end_date end_date
1448 , round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
1449 (tmp.end_date >= cal_gl.end_date)
1450 then (cal_gl.end_date - cal_gl.start_date + 1) *
1451 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1452 when (cal_gl.start_date <= tmp.start_date) and
1453 (cal_gl.end_date <= tmp.end_date )
1454 then (cal_gl.end_date - tmp.start_date + 1) *
1455 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1456 when (cal_gl.start_date >= tmp.start_date) and
1457 (cal_gl.end_date >= tmp.end_date)
1458 then (tmp.end_date - cal_gl.start_date + 1) *
1459 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1460 when (cal_gl.start_date <= tmp.start_date) and
1461 (cal_gl.end_date >= tmp.end_date)
1462 then tmp.revenue
1463 else to_number(null)
1464 end,to_number(null)))/orginfo.projfunc_currency_mau
1465 )*orginfo.projfunc_currency_mau revenue
1466 , round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
1467 (tmp.end_date >= cal_gl.end_date)
1468 then (cal_gl.end_date - cal_gl.start_date + 1) *
1469 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1470 when (cal_gl.start_date <= tmp.start_date) and
1471 (cal_gl.end_date <= tmp.end_date )
1472 then (cal_gl.end_date - tmp.start_date + 1) *
1473 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1474 when (cal_gl.start_date >= tmp.start_date) and
1475 (cal_gl.end_date >= tmp.end_date)
1476 then (tmp.end_date - cal_gl.start_date + 1) *
1477 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1478 when (cal_gl.start_date <= tmp.start_date) and
1479 (cal_gl.end_date >= tmp.end_date)
1480 then tmp.raw_cost
1481 else to_number(null)
1482 end,to_number(null)))/orginfo.projfunc_currency_mau
1483 )*orginfo.projfunc_currency_mau raw_cost
1484 , round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
1485 (tmp.end_date >= cal_gl.end_date)
1486 then (cal_gl.end_date - cal_gl.start_date + 1) *
1487 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1488 when (cal_gl.start_date <= tmp.start_date) and
1489 (cal_gl.end_date <= tmp.end_date )
1490 then (cal_gl.end_date - tmp.start_date + 1) *
1491 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1492 when (cal_gl.start_date >= tmp.start_date) and
1493 (cal_gl.end_date >= tmp.end_date)
1494 then (tmp.end_date - cal_gl.start_date + 1) *
1495 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1496 when (cal_gl.start_date <= tmp.start_date) and
1497 (cal_gl.end_date >= tmp.end_date)
1498 then tmp.burdened_cost
1499 else to_number(null)
1500 end,to_number(null)))/orginfo.projfunc_currency_mau
1501 )*orginfo.projfunc_currency_mau burdened_cost
1502 , round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
1503 (tmp.end_date >= cal_gl.end_date)
1504 then (cal_gl.end_date - cal_gl.start_date + 1) *
1505 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1506 when (cal_gl.start_date <= tmp.start_date) and
1507 (cal_gl.end_date <= tmp.end_date )
1508 then (cal_gl.end_date - tmp.start_date + 1) *
1509 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1510 when (cal_gl.start_date >= tmp.start_date) and
1511 (cal_gl.end_date >= tmp.end_date)
1512 then (tmp.end_date - cal_gl.start_date + 1) *
1513 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1514 when (cal_gl.start_date <= tmp.start_date) and
1515 (cal_gl.end_date >= tmp.end_date)
1516 then tmp.labor_hrs
1517 else to_number(null)
1518 end,to_number(null)))/g_labor_mau
1519 )*g_labor_mau labor_hrs
1520 , decode(sign(to_date(orginfo.gl_calendar_max_date,'J') - cal_gl.end_date)
1521 , -1, 'G'
1522 , tmp.time_dangling_flag
1523 ) time_dangling_flag
1524 , tmp.rate_dangling_flag rate_dangling_flag
1525 , tmp.rate2_dangling_flag rate2_dangling_flag
1526 from
1527 PJI_ORG_EXTR_INFO orginfo
1528 , PJI_FM_EXTR_PLAN tmp
1529 , fii_time_cal_period cal_gl
1530 where tmp.worker_id = p_worker_id
1531 and tmp.end_date >= cal_gl.start_date
1532 and tmp.start_date <= cal_gl.end_date
1533 and tmp.calendar_type_code <> 'GL'
1534 and orginfo.gl_calendar_id = cal_gl.calendar_id
1535 and orginfo.org_id = tmp.project_org_id
1536 and tmp.LINE_TYPE in ( 'OF' , 'OG' )
1537 and tmp.time_dangling_flag is null
1538 and tmp.rate_dangling_flag is null
1539 and tmp.rate2_dangling_flag is null
1540 ;
1541
1542 end if;
1543
1544 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1545 (l_process, 'PJI_FM_PLAN_EXTR.CONVERT_TO_GL_PERIODS(p_worker_id);');
1546
1547 commit;
1548
1549 end convert_to_gl_periods;
1550
1551
1552
1553 -- -----------------------------------------------------
1554 -- procedure CONVERT_TO_ENT_PERIODS
1555 -- -----------------------------------------------------
1556
1557 procedure convert_to_ent_periods(p_worker_id number) is
1558
1559 l_process varchar2(30);
1560
1561 begin
1562
1563 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1564
1565 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1566 (l_process,'PJI_FM_PLAN_EXTR.CONVERT_TO_ENT_PERIODS(p_worker_id);')) then
1567 return;
1568 end if;
1569
1570
1571 -- convert to ENT periods
1572 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to ENT periods
1573 ( LINE_TYPE
1574 , CALENDAR_TYPE_CODE
1575 , WORKER_ID
1576 , PROJECT_ID
1577 , PROJECT_ORG_ID
1578 , PF_CURRENCY_CODE
1579 , VERSION_ID
1580 , PLAN_TYPE_CODE
1581 , CURRENCY_TYPE
1582 , PERIOD_ID
1583 , PERIOD_NAME
1584 , START_DATE
1585 , END_DATE
1586 , REVENUE
1587 , RAW_COST
1588 , BURDENED_COST
1589 , LABOR_HRS
1590 , TIME_DANGLING_FLAG
1591 , RATE_DANGLING_FLAG
1592 , RATE2_DANGLING_FLAG
1593 )
1594 select /*+ ORDERED
1595 full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
1596 full(tmp) use_hash(tmp) parallel(tmp)
1597 cache(ent)
1598 pq_distribute(tmp, broadcast, none)
1599 */
1600 decode(tmp.LINE_TYPE
1601 , 'OF', 'CF'
1602 , 'OG', 'CG'
1603 ) LINE_TYPE
1604 , 'ENT' calendar_type_code
1605 , tmp.worker_id
1606 , tmp.project_id
1607 , tmp.project_org_id
1608 , tmp.pf_currency_code
1609 , tmp.version_id
1610 , tmp.plan_type_code
1611 , tmp.currency_type currency_type
1612 , ent.ent_period_id period_id
1613 , ent.name period_name
1614 , ent.start_date start_date
1615 , ent.end_date end_date
1616 , round((nvl(case when (tmp.start_date <= ent.start_date) and
1617 (tmp.end_date >= ent.end_date)
1618 then (ent.end_date - ent.start_date + 1) *
1619 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1620 when (ent.start_date <= tmp.start_date) and
1621 (ent.end_date <= tmp.end_date )
1622 then (ent.end_date - tmp.start_date + 1) *
1623 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1624 when (ent.start_date >= tmp.start_date) and
1625 (ent.end_date >= tmp.end_date)
1626 then (tmp.end_date - ent.start_date + 1) *
1627 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1628 when (ent.start_date <= tmp.start_date) and
1629 (ent.end_date >= tmp.end_date)
1630 then tmp.revenue
1631 else to_number(null)
1632 end,to_number(null)))/orginfo.projfunc_currency_mau
1633 )*orginfo.projfunc_currency_mau revenue
1634 , round((nvl(case when (tmp.start_date <= ent.start_date) and
1635 (tmp.end_date >= ent.end_date)
1636 then (ent.end_date - ent.start_date + 1) *
1637 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1638 when (ent.start_date <= tmp.start_date) and
1639 (ent.end_date <= tmp.end_date )
1640 then (ent.end_date - tmp.start_date + 1) *
1641 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1642 when (ent.start_date >= tmp.start_date) and
1643 (ent.end_date >= tmp.end_date)
1644 then (tmp.end_date - ent.start_date + 1) *
1645 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1646 when (ent.start_date <= tmp.start_date) and
1647 (ent.end_date >= tmp.end_date)
1648 then tmp.raw_cost
1649 else to_number(null)
1650 end,to_number(null)))/orginfo.projfunc_currency_mau
1651 )*orginfo.projfunc_currency_mau raw_cost
1652 , round((nvl(case when (tmp.start_date <= ent.start_date) and
1653 (tmp.end_date >= ent.end_date)
1654 then (ent.end_date - ent.start_date + 1) *
1655 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1656 when (ent.start_date <= tmp.start_date) and
1657 (ent.end_date <= tmp.end_date )
1658 then (ent.end_date - tmp.start_date + 1) *
1659 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1660 when (ent.start_date >= tmp.start_date) and
1661 (ent.end_date >= tmp.end_date)
1662 then (tmp.end_date - ent.start_date + 1) *
1663 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1664 when (ent.start_date <= tmp.start_date) and
1665 (ent.end_date >= tmp.end_date)
1666 then tmp.burdened_cost
1667 else to_number(null)
1668 end,to_number(null)))/orginfo.projfunc_currency_mau
1669 )*orginfo.projfunc_currency_mau burdened_cost
1670 , round((nvl(case when (tmp.start_date <= ent.start_date) and
1671 (tmp.end_date >= ent.end_date)
1672 then (ent.end_date - ent.start_date + 1) *
1673 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1674 when (ent.start_date <= tmp.start_date) and
1675 (ent.end_date <= tmp.end_date )
1676 then (ent.end_date - tmp.start_date + 1) *
1677 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1678 when (ent.start_date >= tmp.start_date) and
1679 (ent.end_date >= tmp.end_date)
1680 then (tmp.end_date - ent.start_date + 1) *
1681 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1682 when (ent.start_date <= tmp.start_date) and
1683 (ent.end_date >= tmp.end_date)
1684 then tmp.labor_hrs
1685 else to_number(null)
1686 end,to_number(null)))/g_labor_mau
1687 )*g_labor_mau labor_hrs
1688 , decode(sign(to_date(orginfo.en_calendar_max_date,'J') - ent.end_date)
1689 , -1, 'G'
1690 , tmp.time_dangling_flag
1691 ) time_dangling_flag
1692 , tmp.rate_dangling_flag rate_dangling_flag
1693 , tmp.rate2_dangling_flag rate2_dangling_flag
1694 from
1695 PJI_ORG_EXTR_INFO orginfo
1696 , PJI_FM_EXTR_PLAN tmp
1697 , fii_time_ent_period ent
1698 where tmp.worker_id = p_worker_id
1699 and tmp.end_date >= ent.start_date
1700 and tmp.start_date <= ent.end_date
1701 and tmp.calendar_type_code <> 'ENT'
1702 and tmp.LINE_TYPE in ( 'OF' , 'OG' )
1703 and tmp.project_org_id = orginfo.org_id
1704 and tmp.time_dangling_flag is null
1705 and tmp.rate_dangling_flag is null
1706 and tmp.rate2_dangling_flag is null
1707 ;
1708
1709 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1710 (l_process, 'PJI_FM_PLAN_EXTR.CONVERT_TO_ENT_PERIODS(p_worker_id);');
1711
1712 commit;
1713
1714 end convert_to_ent_periods;
1715
1716
1717
1718 -- -----------------------------------------------------
1719 -- procedure CONVERT_TO_ENTW_PERIODS
1720 -- -----------------------------------------------------
1721
1722 procedure convert_to_entw_periods(p_worker_id number) is
1723
1724 l_process varchar2(30);
1725
1726 begin
1727
1728 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1729
1730 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1731 (l_process,'PJI_FM_PLAN_EXTR.CONVERT_TO_ENTW_PERIODS(p_worker_id);')) then
1732 return;
1733 end if;
1734
1735
1736 -- convert to ENTW periods
1737 Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to ENTW periods
1738 ( LINE_TYPE
1739 , CALENDAR_TYPE_CODE
1740 , WORKER_ID
1741 , PROJECT_ID
1742 , PROJECT_ORG_ID
1743 , PF_CURRENCY_CODE
1744 , VERSION_ID
1745 , PLAN_TYPE_CODE
1746 , CURRENCY_TYPE
1747 , PERIOD_ID
1748 , PERIOD_NAME
1749 , START_DATE
1750 , END_DATE
1751 , REVENUE
1752 , RAW_COST
1753 , BURDENED_COST
1754 , LABOR_HRS
1755 , TIME_DANGLING_FLAG
1756 , RATE_DANGLING_FLAG
1757 , RATE2_DANGLING_FLAG
1758 )
1759 select /*+ ORDERED
1760 full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
1761 full(tmp) use_hash(tmp) parallel(tmp)
1762 cache(entw)
1763 pq_distribute(tmp, broadcast, none)
1764 */
1765 decode(tmp.LINE_TYPE
1766 , 'OF', 'CF'
1767 , 'OG', 'CG'
1768 ) LINE_TYPE
1769 , 'ENTW' calendar_type_code
1770 , tmp.worker_id
1771 , tmp.project_id
1772 , tmp.project_org_id
1773 , tmp.pf_currency_code
1774 , tmp.version_id
1775 , tmp.plan_type_code
1776 , tmp.currency_type currency_type
1777 , entw.week_id period_id
1778 , PJI_RM_SUM_MAIN.g_null period_name
1779 , entw.start_date start_date
1780 , entw.end_date end_date
1781 , round((nvl(case when (tmp.start_date <= entw.start_date) and
1782 (tmp.end_date >= entw.end_date)
1783 then (entw.end_date - entw.start_date + 1) *
1784 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1785 when (entw.start_date <= tmp.start_date) and
1786 (entw.end_date <= tmp.end_date )
1787 then (entw.end_date - tmp.start_date + 1) *
1788 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1789 when (entw.start_date >= tmp.start_date) and
1790 (entw.end_date >= tmp.end_date)
1791 then (tmp.end_date - entw.start_date + 1) *
1792 tmp.revenue / (tmp.end_date - tmp.start_date + 1)
1793 when (entw.start_date <= tmp.start_date) and
1794 (entw.end_date >= tmp.end_date)
1795 then tmp.revenue
1796 else to_number(null)
1797 end,to_number(null)))/orginfo.projfunc_currency_mau
1798 )*orginfo.projfunc_currency_mau revenue
1799 , round((nvl(case when (tmp.start_date <= entw.start_date) and
1800 (tmp.end_date >= entw.end_date)
1801 then (entw.end_date - entw.start_date + 1) *
1802 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1803 when (entw.start_date <= tmp.start_date) and
1804 (entw.end_date <= tmp.end_date )
1805 then (entw.end_date - tmp.start_date + 1) *
1806 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1807 when (entw.start_date >= tmp.start_date) and
1808 (entw.end_date >= tmp.end_date)
1809 then (tmp.end_date - entw.start_date + 1) *
1810 tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
1811 when (entw.start_date <= tmp.start_date) and
1812 (entw.end_date >= tmp.end_date)
1813 then tmp.raw_cost
1814 else to_number(null)
1815 end,to_number(null)))/orginfo.projfunc_currency_mau
1816 )*orginfo.projfunc_currency_mau raw_cost
1817 , round((nvl(case when (tmp.start_date <= entw.start_date) and
1818 (tmp.end_date >= entw.end_date)
1819 then (entw.end_date - entw.start_date + 1) *
1820 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1821 when (entw.start_date <= tmp.start_date) and
1822 (entw.end_date <= tmp.end_date )
1823 then (entw.end_date - tmp.start_date + 1) *
1824 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1825 when (entw.start_date >= tmp.start_date) and
1826 (entw.end_date >= tmp.end_date)
1827 then (tmp.end_date - entw.start_date + 1) *
1828 tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
1829 when (entw.start_date <= tmp.start_date) and
1830 (entw.end_date >= tmp.end_date)
1831 then tmp.burdened_cost
1832 else to_number(null)
1833 end,to_number(null)))/orginfo.projfunc_currency_mau
1834 )*orginfo.projfunc_currency_mau burdened_cost
1835 , round((nvl(case when (tmp.start_date <= entw.start_date) and
1836 (tmp.end_date >= entw.end_date)
1837 then (entw.end_date - entw.start_date + 1) *
1838 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1839 when (entw.start_date <= tmp.start_date) and
1840 (entw.end_date <= tmp.end_date )
1841 then (entw.end_date - tmp.start_date + 1) *
1842 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1843 when (entw.start_date >= tmp.start_date) and
1844 (entw.end_date >= tmp.end_date)
1845 then (tmp.end_date - entw.start_date + 1) *
1846 tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
1847 when (entw.start_date <= tmp.start_date) and
1848 (entw.end_date >= tmp.end_date)
1849 then tmp.labor_hrs
1850 else to_number(null)
1851 end,to_number(null)))/g_labor_mau
1852 )*g_labor_mau labor_hrs
1853 , decode(sign(to_date(orginfo.en_calendar_max_date,'J') - entw.end_date)
1854 , -1, 'G'
1855 , tmp.time_dangling_flag
1856 ) time_dangling_flag
1857 , tmp.rate_dangling_flag rate_dangling_flag
1858 , tmp.rate2_dangling_flag rate2_dangling_flag
1859 from
1860 PJI_ORG_EXTR_INFO orginfo
1861 , PJI_FM_EXTR_PLAN tmp
1862 , fii_time_week entw
1863 where tmp.worker_id = p_worker_id
1864 and tmp.end_date >= entw.start_date
1865 and tmp.start_date <= entw.end_date
1866 and tmp.calendar_type_code <> 'ENTW'
1867 and tmp.LINE_TYPE in ( 'OF' , 'OG' )
1868 and tmp.project_org_id = orginfo.org_id
1869 and tmp.time_dangling_flag is null
1870 and tmp.rate_dangling_flag is null
1871 and tmp.rate2_dangling_flag is null
1872 ;
1873
1874 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1875 (l_process, 'PJI_FM_PLAN_EXTR.CONVERT_TO_ENTW_PERIODS(p_worker_id);');
1876
1877 commit;
1878
1879 end convert_to_entw_periods;
1880
1881
1882
1883 -- -----------------------------------------------------
1884 -- procedure DANGLING_PLAN_VERSIONS
1885 -- -----------------------------------------------------
1886
1887 procedure dangling_plan_versions(p_worker_id number) is
1888
1889 l_process varchar2(30);
1890
1891 begin
1892
1893 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1894
1895 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1896 (l_process,'PJI_FM_PLAN_EXTR.DANGLING_PLAN_VERSIONS(p_worker_id);')) then
1897 return;
1898 end if;
1899
1900
1901 Insert /*+ APPEND */ into PJI_FM_EXTR_PLN_LOG
1902 (PROJECT_ID
1903 ,PROJECT_ORG_ID
1904 ,PLAN_TYPE_CODE
1905 ,BUDGET_VERSION_ID
1906 ,RECORD_TYPE_CODE
1907 ,FROM_DATE
1908 ,TO_DATE
1909 ,CALENDAR_ID
1910 )
1911 select /*+ ORDERED
1912 full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
1913 full(tmp) use_hash(tmp) parallel(tmp)
1914 */
1915 tmp.PROJECT_ID
1916 , tmp.PROJECT_ORG_ID
1917 , tmp.PLAN_TYPE_CODE
1918 , tmp.VERSION_ID
1919 , to_char(decode(tmp.RATE_DANGLING_FLAG,
1920 'U', 1, 0) + -- EUR rate for 01-JAN-1999 missing
1921 decode(tmp.RATE2_DANGLING_FLAG,
1922 'U', 2, 0) + -- EUR rate for 01-JAN-1999 missing
1923 decode(tmp.RATE_DANGLING_FLAG,
1924 null, 0, 4) + -- Global 1 rate missing
1925 decode(tmp.RATE2_DANGLING_FLAG,
1926 null, 0, 8) + -- Global 2 rate missing
1927 decode(tmp.TIME_DANGLING_FLAG,
1928 null, 0, 16) -- Calendar setup missing
1929 ) RECORD_TYPE_CODE
1930 , tmp.START_DATE
1931 , tmp.END_DATE
1932 , decode(tmp.CALENDAR_TYPE_CODE
1933 , 'PA', orginfo.PA_CALENDAR_ID
1934 , 'GL', orginfo.GL_CALENDAR_ID
1935 , null) CALENDAR_ID
1936 from PJI_ORG_EXTR_INFO orginfo
1937 , PJI_FM_EXTR_PLAN tmp
1938 where tmp.WORKER_ID = p_worker_id
1939 and ( tmp.TIME_DANGLING_FLAG is not null
1940 or tmp.RATE_DANGLING_FLAG is not null
1941 or tmp.RATE2_DANGLING_FLAG is not null)
1942 and tmp.project_org_id = orginfo.org_id
1943 ;
1944
1945 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1946 (l_process, 'PJI_FM_PLAN_EXTR.DANGLING_PLAN_VERSIONS(p_worker_id);');
1947
1948 commit;
1949
1950 end dangling_plan_versions;
1951
1952
1953
1954 -- -----------------------------------------------------
1955 -- procedure SUMMARIZE_EXTRACT
1956 -- -----------------------------------------------------
1957
1958 procedure summarize_extract(p_worker_id number) is
1959
1960 l_process varchar2(30);
1961
1962 l_txn_currency_flag varchar2(1);
1963 l_g2_currency_flag varchar2(1);
1964
1965 l_g1_currency_code varchar2(30);
1966 l_g2_currency_code varchar2(30);
1967
1968
1969 begin
1970
1971 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1972
1973 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1974 (l_process,'PJI_FM_PLAN_EXTR.SUMMARIZE_EXTRACT(p_worker_id);')) then
1975 return;
1976 end if;
1977
1978 select
1979 TXN_CURR_FLAG,
1980 GLOBAL_CURR2_FLAG
1981 into
1982 l_txn_currency_flag,
1983 l_g2_currency_flag
1984 from
1985 PJI_SYSTEM_SETTINGS;
1986
1987 l_g1_currency_code := PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY;
1988 l_g2_currency_code := PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
1989
1990 insert /*+ append parallel(pln_i) */ into PJI_FM_AGGR_PLN pln_i
1991 (
1992 WORKER_ID,
1993 PROJECT_ID,
1994 PROJECT_ORG_ID,
1995 PROJECT_ORGANIZATION_ID,
1996 PROJECT_TYPE_CLASS,
1997 CALENDAR_TYPE_CODE,
1998 CURR_RECORD_TYPE_ID,
1999 CURRENCY_CODE,
2000 TIME_PHASED_TYPE_CODE,
2001 TIME_ID,
2002 PERIOD_NAME,
2003 START_DATE,
2004 END_DATE,
2005 CURR_BGT_REVENUE,
2006 CURR_BGT_RAW_COST,
2007 CURR_BGT_BRDN_COST,
2008 CURR_BGT_LABOR_HRS,
2009 CURR_ORIG_BGT_REVENUE,
2010 CURR_ORIG_BGT_RAW_COST,
2011 CURR_ORIG_BGT_BRDN_COST,
2012 CURR_ORIG_BGT_LABOR_HRS,
2013 CURR_FORECAST_REVENUE,
2014 CURR_FORECAST_RAW_COST,
2015 CURR_FORECAST_BRDN_COST,
2016 CURR_FORECAST_LABOR_HRS
2017 )
2018 select
2019 tmp1.WORKER_ID,
2020 tmp1.PROJECT_ID,
2021 tmp1.PROJECT_ORG_ID,
2022 tmp1.PROJECT_ORGANIZATION_ID,
2023 tmp1.PROJECT_TYPE_CLASS,
2024 tmp1.CALENDAR_TYPE_CODE,
2025 sum(tmp1.CURR_RECORD_TYPE_ID) CURR_RECORD_TYPE_ID,
2026 nvl(tmp1.CURRENCY_CODE, 'PJI$NULL') CURRENCY_CODE,
2027 tmp1.TIME_PHASED_TYPE_CODE,
2028 tmp1.PERIOD_ID,
2029 tmp1.PERIOD_NAME,
2030 tmp1.START_DATE,
2031 tmp1.END_DATE,
2032 max(tmp1.CURR_BGT_REVENUE) CURR_BGT_REVENUE,
2033 max(tmp1.CURR_BGT_RAW_COST) CURR_BGT_RAW_COST,
2034 max(tmp1.CURR_BGT_BRDN_COST) CURR_BGT_BRDN_COST,
2035 max(tmp1.CURR_BGT_LABOR_HRS) CURR_BGT_LABOR_HRS,
2036 max(tmp1.CURR_ORIG_BGT_REVENUE) CURR_ORIG_BGT_REVENUE,
2037 max(tmp1.CURR_ORIG_BGT_RAW_COST) CURR_ORIG_BGT_RAW_COST,
2038 max(tmp1.CURR_ORIG_BGT_BRDN_COST) CURR_ORIG_BGT_BRDN_COST,
2039 max(tmp1.CURR_ORIG_BGT_LABOR_HRS) CURR_ORIG_BGT_LABOR_HRS,
2040 max(tmp1.CURR_FORECAST_REVENUE) CURR_FORECAST_REVENUE,
2041 max(tmp1.CURR_FORECAST_RAW_COST) CURR_FORECAST_RAW_COST,
2042 max(tmp1.CURR_FORECAST_BRDN_COST) CURR_FORECAST_BRDN_COST,
2043 max(tmp1.CURR_FORECAST_LABOR_HRS) CURR_FORECAST_LABOR_HRS
2044 from
2045 (
2046 select /*+ ordered
2047 full(vers) use_hash(vers) swap_join_inputs(vers)
2048 full(tmp) use_hash(tmp) parallel(tmp) */
2049 tmp.worker_id,
2050 tmp.project_id,
2051 tmp.project_org_id,
2052 vers.project_organization_id,
2053 vers.project_type_class,
2054 tmp.calendar_type_code,
2055 decode(tmp.currency_type,
2056 'G', 1,
2057 '2', 2,
2058 'F', 4) curr_record_type_id,
2059 decode(tmp.currency_type,
2060 'G', l_g1_currency_code,
2061 '2', l_g2_currency_code,
2062 'F', tmp.pf_currency_code) currency_code,
2063 vers.time_phased_type_code,
2064 tmp.period_id,
2065 tmp.period_name,
2066 tmp.start_date,
2067 tmp.end_date,
2068 sum(decode(tmp.plan_type_code,
2069 g_revenue_budget_type_code,
2070 decode(vers.current_flag,
2071 'Y', tmp.revenue,
2072 to_number(null)),
2073 g_rev_fp_type_code,
2074 decode(vers.current_flag,
2075 'Y', tmp.revenue,
2076 to_number(null)),
2077 to_number(null))) curr_bgt_revenue,
2078 sum(decode(tmp.plan_type_code,
2079 g_cost_budget_type_code,
2080 decode(vers.current_flag,
2081 'Y', tmp.raw_cost,
2082 to_number(null)),
2083 g_cost_fp_type_code,
2084 decode(vers.current_flag,
2085 'Y', tmp.raw_cost,
2086 to_number(null)),
2087 to_number(null))) curr_bgt_raw_cost,
2088 sum(decode(tmp.plan_type_code,
2089 g_cost_budget_type_code,
2090 decode(vers.current_flag,
2091 'Y', tmp.burdened_cost,
2092 to_number(null)),
2093 g_cost_fp_type_code,
2094 decode(vers.current_flag,
2095 'Y', tmp.burdened_cost,
2096 to_number(null)),
2097 to_number(null))) curr_bgt_brdn_cost,
2098 sum(decode(tmp.plan_type_code,
2099 g_cost_budget_type_code,
2100 decode(vers.current_flag,
2101 'Y', tmp.labor_hrs,
2102 to_number(null)),
2103 g_cost_fp_type_code,
2104 decode(vers.current_flag,
2105 'Y', tmp.labor_hrs,
2106 to_number(null)),
2107 to_number(null))) curr_bgt_labor_hrs,
2108 sum(decode(tmp.plan_type_code,
2109 g_revenue_budget_type_code,
2110 decode(vers.current_original_flag,
2111 'Y', tmp.revenue,
2112 to_number(null)),
2113 g_rev_fp_type_code,
2114 decode(vers.current_original_flag,
2115 'Y', tmp.revenue,
2116 to_number(null)),
2117 to_number(null))) curr_orig_bgt_revenue,
2118 sum(decode(tmp.plan_type_code,
2119 g_cost_budget_type_code,
2120 decode(vers.current_original_flag,
2121 'Y', tmp.raw_cost,
2122 to_number(null)),
2123 g_cost_fp_type_code,
2124 decode(vers.current_original_flag,
2125 'Y', tmp.raw_cost,
2126 to_number(null)),
2127 to_number(null))) curr_orig_bgt_raw_cost,
2128 sum(decode(tmp.plan_type_code,
2129 g_cost_budget_type_code,
2130 decode(vers.current_original_flag,
2131 'Y', tmp.burdened_cost,
2132 to_number(null)),
2133 g_cost_fp_type_code,
2134 decode(vers.current_original_flag,
2135 'Y', tmp.burdened_cost,
2136 to_number(null)),
2137 to_number(null))) curr_orig_bgt_brdn_cost,
2138 sum(decode(tmp.plan_type_code,
2139 g_cost_budget_type_code,
2140 decode(vers.current_original_flag,
2141 'Y', tmp.labor_hrs,
2142 to_number(null)),
2143 g_cost_fp_type_code,
2144 decode(vers.current_original_flag,
2145 'Y', tmp.labor_hrs,
2146 to_number(null)),
2147 to_number(null))) curr_orig_bgt_labor_hrs,
2148 sum(decode(tmp.plan_type_code,
2149 g_revenue_forecast_type_code,
2150 decode(vers.current_flag,
2151 'Y', tmp.revenue,
2152 to_number(null)),
2153 g_rev_forecast_fp_type_code,
2154 decode(vers.current_flag,
2155 'Y', tmp.revenue,
2156 to_number(null)),
2157 to_number(null))) curr_forecast_revenue,
2158 sum(decode(tmp.plan_type_code,
2159 g_cost_forecast_type_code,
2160 decode(vers.current_flag,
2161 'Y', tmp.raw_cost,
2162 to_number(null)),
2163 g_cost_forecast_fp_type_code,
2164 decode(vers.current_flag,
2165 'Y', tmp.raw_cost,
2166 to_number(null)),
2167 to_number(null))) curr_forecast_raw_cost,
2168 sum(decode(tmp.plan_type_code,
2169 g_cost_forecast_type_code,
2170 decode(vers.current_flag,
2171 'Y', tmp.burdened_cost,
2172 to_number(null)),
2173 g_cost_forecast_fp_type_code,
2174 decode(vers.current_flag,
2175 'Y', tmp.burdened_cost,
2176 to_number(null)),
2177 to_number(null))) curr_forecast_brdn_cost,
2178 sum(decode(tmp.plan_type_code,
2179 g_cost_forecast_type_code,
2180 decode(vers.current_flag,
2181 'Y', tmp.labor_hrs,
2182 to_number(null)),
2183 g_cost_forecast_fp_type_code,
2184 decode(vers.current_flag,
2185 'Y', tmp.labor_hrs,
2186 to_number(null)),
2187 to_number(null))) curr_forecast_labor_hrs
2188 from
2189 PJI_FM_EXTR_PLNVER1 vers,
2190 PJI_FM_EXTR_PLAN tmp,
2191 PJI_FM_EXTR_PLN_LOG log
2192 where
2193 tmp.WORKER_ID = p_worker_id and
2194 tmp.LINE_TYPE <> 'F1' and
2195 vers.WORKER_ID = p_worker_id and
2196 tmp.project_id = vers.project_id and
2197 tmp.version_id = vers.version_id and
2198 decode(nvl(g_gl_period_flag, 'N'),
2199 'Y', 'ZZ', 'GL') <> tmp.calendar_type_code and
2200 decode(nvl(g_pa_period_flag, 'N'),
2201 'Y', 'ZZ', 'PA') <> tmp.calendar_type_code and
2202 tmp.version_id = log.budget_version_id (+) and
2203 log.budget_version_id is null
2204 group by
2205 tmp.worker_id,
2206 tmp.project_id,
2207 tmp.project_org_id,
2208 vers.project_organization_id,
2209 vers.project_type_class,
2210 tmp.calendar_type_code,
2211 decode(tmp.currency_type,
2212 'G', 1,
2213 '2', 2,
2214 'F', 4),
2215 decode(tmp.currency_type,
2216 'G', l_g1_currency_code,
2217 '2', l_g2_currency_code,
2218 'F', tmp.pf_currency_code),
2219 vers.time_phased_type_code,
2220 tmp.period_id,
2221 tmp.period_name,
2222 tmp.start_date,
2223 tmp.end_date
2224 ) tmp1
2225 group by
2226 tmp1.WORKER_ID,
2227 tmp1.PROJECT_ID,
2228 tmp1.PROJECT_ORG_ID,
2229 tmp1.PROJECT_ORGANIZATION_ID,
2230 tmp1.PROJECT_TYPE_CLASS,
2231 tmp1.CALENDAR_TYPE_CODE,
2232 nvl(tmp1.CURRENCY_CODE, 'PJI$NULL'),
2233 tmp1.TIME_PHASED_TYPE_CODE,
2234 tmp1.PERIOD_ID,
2235 tmp1.PERIOD_NAME,
2236 tmp1.START_DATE,
2237 tmp1.END_DATE;
2238
2239 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2240 (l_process, 'PJI_FM_PLAN_EXTR.SUMMARIZE_EXTRACT(p_worker_id);');
2241
2242 commit;
2243
2244 end summarize_extract;
2245
2246
2247
2248 -- -----------------------------------------------------
2249 -- procedure EXTRACT_UPDATED_VERSIONS
2250 -- -----------------------------------------------------
2251
2252 procedure extract_updated_versions(p_worker_id number) is
2253
2254 l_process varchar2(30);
2255
2256 begin
2257
2258 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2259
2260 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2261 (l_process,'PJI_FM_PLAN_EXTR.EXTRACT_UPDATED_VERSIONS(p_worker_id);')) then
2262 return;
2263 end if;
2264
2265 Insert /*+ APPEND */ into PJI_FM_EXTR_PLNVER2
2266 (
2267 WORKER_ID,
2268 BATCH_MAP_ROWID,
2269 PROJECT_ID,
2270 COST_BUDGET_C_VERSION,
2271 COST_BUDGET_CO_VERSION,
2272 REVENUE_BUDGET_C_VERSION,
2273 REVENUE_BUDGET_CO_VERSION,
2274 COST_FORECAST_C_VERSION,
2275 REVENUE_FORECAST_C_VERSION,
2276 BATCH_ID
2277 )
2278 select p_worker_id
2279 , tmp.batch_map_rowid
2280 , tmp.project_id
2281 , tmp.COST_BUDGET_C_VERSION
2282 , tmp.COST_BUDGET_CO_VERSION
2283 , tmp.REVENUE_BUDGET_C_VERSION
2284 , tmp.REVENUE_BUDGET_CO_VERSION
2285 , tmp.COST_FORECAST_C_VERSION
2286 , tmp.REVENUE_FORECAST_C_VERSION
2287 , ceil(ROWNUM / PJI_RM_SUM_MAIN.g_commit_threshold)
2288 from
2289 (
2290 SELECT tmp.BATCH_MAP_ROWID BATCH_MAP_ROWID
2291 , tmp.PROJECT_ID PROJECT_ID
2292 , max(tmp.COST_BUDGET_C_VERSION) COST_BUDGET_C_VERSION
2293 , max(tmp.COST_BUDGET_CO_VERSION) COST_BUDGET_CO_VERSION
2294 , max(tmp.REVENUE_BUDGET_C_VERSION) REVENUE_BUDGET_C_VERSION
2295 , max(tmp.REVENUE_BUDGET_CO_VERSION) REVENUE_BUDGET_CO_VERSION
2296 , max(tmp.COST_FORECAST_C_VERSION) COST_FORECAST_C_VERSION
2297 , max(tmp.REVENUE_FORECAST_C_VERSION) REVENUE_FORECAST_C_VERSION
2298 FROM
2299 (
2300 select /*+ ORDERED
2301 full(bvs) use_hash(bvs) parallel(bvs) swap_join_inputs(bvs)
2302 full(tmp) use_hash(tmp) parallel(tmp)
2303 */
2304 map.rowid batch_map_rowid
2305 , tmp.project_id PROJECT_ID
2306 , case
2307 when (tmp.PLAN_TYPE_CODE = g_cost_budget_type_code or
2308 tmp.PLAN_TYPE_CODE = g_cost_fp_type_code)
2309 and bvs.CURRENT_FLAG = 'Y'
2310 then bvs.VERSION_ID
2311 else 0
2312 end COST_BUDGET_C_VERSION
2313 , case
2314 when (tmp.PLAN_TYPE_CODE = g_cost_budget_type_code or
2315 tmp.PLAN_TYPE_CODE = g_cost_fp_type_code)
2316 and bvs.CURRENT_ORIGINAL_FLAG = 'Y'
2317 then bvs.VERSION_ID
2318 else 0
2319 end COST_BUDGET_CO_VERSION
2320 , case
2321 when (tmp.PLAN_TYPE_CODE = g_revenue_budget_type_code or
2322 tmp.PLAN_TYPE_CODE = g_rev_fp_type_code)
2323 and bvs.CURRENT_FLAG = 'Y'
2324 then bvs.VERSION_ID
2325 else 0
2326 end REVENUE_BUDGET_C_VERSION
2327 , case
2328 when (tmp.PLAN_TYPE_CODE = g_revenue_budget_type_code or
2329 tmp.PLAN_TYPE_CODE = g_rev_fp_type_code)
2330 and bvs.CURRENT_ORIGINAL_FLAG = 'Y'
2331 then bvs.VERSION_ID
2332 else 0
2333 end REVENUE_BUDGET_CO_VERSION
2334 , case
2335 when (tmp.PLAN_TYPE_CODE = g_cost_forecast_type_code or
2336 tmp.PLAN_TYPE_CODE = g_cost_forecast_fp_type_code)
2337 and bvs.CURRENT_FLAG = 'Y'
2338 then bvs.VERSION_ID
2339 else 0
2340 end COST_FORECAST_C_VERSION
2341 , case
2342 when (tmp.PLAN_TYPE_CODE =g_rev_forecast_fp_type_code or
2343 tmp.PLAN_TYPE_CODE = g_revenue_forecast_type_code)
2344 and bvs.CURRENT_FLAG = 'Y'
2345 then bvs.VERSION_ID
2346 else 0
2347 end REVENUE_FORECAST_C_VERSION
2348 from
2349 PJI_PJI_PROJ_BATCH_MAP map,
2350 PJI_FM_EXTR_PLNVER1 bvs,
2351 PJI_FM_EXTR_PLAN tmp,
2352 PJI_FM_EXTR_PLN_LOG log
2353 where
2354 map.worker_id = p_worker_id
2355 and map.project_id = bvs.project_id
2356 and bvs.worker_id = p_worker_id
2357 and tmp.project_id = bvs.project_id
2358 and tmp.version_id = bvs.version_id
2359 and tmp.worker_id = bvs.worker_id
2360 and tmp.calendar_type_code = 'ENTW'
2361 and tmp.LINE_TYPE = 'CG'
2362 and tmp.currency_type = 'G'
2363 and tmp.version_id = log.budget_version_id (+)
2364 and log.budget_version_id is null
2365 ) tmp
2366 GROUP BY
2367 tmp.PROJECT_ID
2368 , tmp.BATCH_MAP_ROWID
2369 ) tmp
2370 ;
2371
2372 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2373 (l_process, 'PJI_FM_PLAN_EXTR.EXTRACT_UPDATED_VERSIONS(p_worker_id);');
2374
2375 commit;
2376
2377 end extract_updated_versions;
2378
2379
2380 -- -----------------------------------------------------
2381 -- procedure UPDATE_BATCH_VERSIONS_PRE
2382 -- -----------------------------------------------------
2383 procedure update_batch_versions_pre(p_worker_id number) is
2384
2385 l_process varchar2(30);
2386
2387 begin
2388
2389 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2390
2391 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2392 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_PRE(p_worker_id);')) then
2393 return;
2394 end if;
2395
2396 insert /*+ append */ into PJI_HELPER_BATCH_MAP
2397 (
2398 BATCH_ID,
2399 WORKER_ID,
2400 STATUS
2401 )
2402 select
2403 distinct
2404 BATCH_ID,
2405 null,
2406 null
2407 from
2408 PJI_FM_EXTR_PLNVER2
2409 where
2410 WORKER_ID = p_worker_id;
2411
2412 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2413 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_PRE(p_worker_id);');
2414
2415 commit;
2416
2417 end update_batch_versions_pre;
2418
2419
2420 -- -----------------------------------------------------
2421 -- procedure UPDATE_BATCH_VERSIONS
2422 -- -----------------------------------------------------
2423 procedure update_batch_versions(p_worker_id number) is
2424
2425 l_process varchar2(30);
2426 l_leftover_batches number;
2427 l_helper_batch_id number;
2428 l_row_count number;
2429 l_parallel_processes number;
2430
2431 begin
2432
2433 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2434
2435 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2436 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);')) then
2437 return;
2438 end if;
2439
2440 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2441 (PJI_RM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
2442
2443 select count(*)
2444 into l_leftover_batches
2445 from PJI_HELPER_BATCH_MAP
2446 where WORKER_ID = p_worker_id and
2447 STATUS = 'P';
2448
2449 l_helper_batch_id := 0;
2450
2451 while (l_helper_batch_id >= 0) loop
2452
2453 if (l_leftover_batches > 0) then
2454
2455 l_leftover_batches := l_leftover_batches - 1;
2456
2457 select BATCH_ID
2458 into l_helper_batch_id
2459 from PJI_HELPER_BATCH_MAP
2460 where WORKER_ID = p_worker_id and
2461 STATUS = 'P' and
2462 ROWNUM = 1;
2463
2464 else
2465
2466 update PJI_HELPER_BATCH_MAP
2467 set WORKER_ID = p_worker_id,
2468 STATUS = 'P'
2469 where WORKER_ID is null and
2470 ROWNUM = 1
2471 returning BATCH_ID
2472 into l_helper_batch_id;
2473
2474 end if;
2475
2476 if (sql%rowcount <> 0) then
2477
2478 commit;
2479
2480 update pji_pji_proj_batch_map map
2481 set (map.COST_BUDGET_N_VERSION,
2482 map.COST_BUDGET_NO_VERSION,
2483 map.REVENUE_BUDGET_N_VERSION,
2484 map.REVENUE_BUDGET_NO_VERSION,
2485 map.COST_FORECAST_N_VERSION,
2486 map.REVENUE_FORECAST_N_VERSION) =
2487 (select
2488 decode(sign(vrs.COST_BUDGET_C_VERSION)
2489 , 0, decode(map.COST_BUDGET_C_VERSION,
2490 -1, -2, map.COST_BUDGET_C_VERSION)
2491 , vrs.COST_BUDGET_C_VERSION
2492 ) COST_BUDGET_C_VERSION
2493 , decode(sign(vrs.COST_BUDGET_CO_VERSION)
2494 , 0, decode(map.COST_BUDGET_CO_VERSION,
2495 -1, -2, map.COST_BUDGET_CO_VERSION)
2496 , vrs.COST_BUDGET_CO_VERSION
2497 ) COST_BUDGET_CO_VERSION
2498 , decode(sign(vrs.REVENUE_BUDGET_C_VERSION)
2499 , 0, decode(map.REVENUE_BUDGET_C_VERSION,
2500 -1, -2, map.REVENUE_BUDGET_C_VERSION)
2501 , vrs.REVENUE_BUDGET_C_VERSION
2502 ) REVENUE_BUDGET_C_VERSION
2503 , decode(sign(vrs.REVENUE_BUDGET_CO_VERSION)
2504 , 0, decode(map.REVENUE_BUDGET_CO_VERSION,
2505 -1, -2, map.REVENUE_BUDGET_CO_VERSION)
2506 , vrs.REVENUE_BUDGET_CO_VERSION
2507 ) REVENUE_BUDGET_CO_VERSION
2508 , decode(sign(vrs.COST_FORECAST_C_VERSION)
2509 , 0, decode(map.COST_FORECAST_C_VERSION,
2510 -1, -2, map.COST_FORECAST_C_VERSION)
2511 , vrs.COST_FORECAST_C_VERSION
2512 ) COST_FORECAST_C_VERSION
2513 , decode(sign(vrs.REVENUE_FORECAST_C_VERSION)
2514 , 0, decode(map.REVENUE_FORECAST_C_VERSION,
2515 -1, -2, map.REVENUE_FORECAST_C_VERSION)
2516 , vrs.REVENUE_FORECAST_C_VERSION
2517 ) REVENUE_FORECAST_C_VERSION
2518 from PJI_FM_EXTR_PLNVER2 vrs
2519 where vrs.batch_map_rowid = map.rowid
2520 )
2521 where map.project_id in (select project_id
2522 from PJI_FM_EXTR_PLNVER2
2523 where WORKER_ID = 1 and
2524 BATCH_ID = l_helper_batch_id)
2525 and map.worker_id = 1;
2526
2527 update PJI_HELPER_BATCH_MAP
2528 set STATUS = 'C'
2529 where WORKER_ID = p_worker_id and
2530 BATCH_ID = l_helper_batch_id;
2531
2532 commit;
2533
2534 else
2535
2536 select count(*)
2537 into l_row_count
2538 from PJI_HELPER_BATCH_MAP
2539 where nvl(STATUS, 'X') <> 'C';
2540
2541 if (l_row_count = 0) then
2542
2543 for x in 2 .. l_parallel_processes loop
2544
2545 update PJI_SYSTEM_PRC_STATUS
2546 set STEP_STATUS = 'C'
2547 where PROCESS_NAME like PJI_RM_SUM_MAIN.g_process|| to_char(x) and
2548 STEP_NAME =
2549 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);' and
2550 START_DATE is null;
2551
2552 commit;
2553
2554 end loop;
2555
2556 l_helper_batch_id := -1;
2557
2558 else
2559
2560 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
2561
2562 end if;
2563
2564 end if;
2565
2566 if (l_helper_batch_id >= 0) then
2567
2568 for x in 2 .. l_parallel_processes loop
2569 if (not PJI_RM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
2570 l_helper_batch_id := -2;
2571 end if;
2572 end loop;
2573
2574 end if;
2575
2576 end loop;
2577
2578 if (l_helper_batch_id <> -2) then
2579
2580 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2581 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);');
2582
2583 end if;
2584
2585 commit;
2586
2587 end update_batch_versions;
2588
2589
2590 -- -----------------------------------------------------
2591 -- procedure UPDATE_BATCH_VERSIONS_POST
2592 -- -----------------------------------------------------
2593 procedure update_batch_versions_post(p_worker_id number) is
2594
2595 l_process varchar2(30);
2596
2597 begin
2598
2599 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2600
2601 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2602 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_POST(p_worker_id);')) then
2603 return;
2604 end if;
2605
2606 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
2607 'PJI_HELPER_BATCH_MAP',
2608 'NORMAL',
2609 null);
2610
2611 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2612 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_POST(p_worker_id);');
2613
2614 commit;
2615
2616 end update_batch_versions_post;
2617
2618
2619 -- -----------------------------------------------------
2620 -- procedure UPDATE_BATCH_STATUSES
2621 -- -----------------------------------------------------
2622 procedure UPDATE_BATCH_STATUSES (p_worker_id in number) is
2623
2624 l_process varchar2(30);
2625
2626 begin
2627
2628 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
2629
2630 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2631 (
2632 l_process,
2633 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_STATUSES(p_worker_id);'
2634 )) then
2635 return;
2636 end if;
2637
2638 -- update project extraction status
2639
2640 update /*+ index(status, PJI_PJI_PROJ_EXTR_STATUS_U1) */
2641 PJI_PJI_PROJ_EXTR_STATUS status
2642 set (CLOSED_DATE,
2643 PROJECT_ORGANIZATION_ID,
2644 COST_BUDGET_C_VERSION,
2645 COST_BUDGET_CO_VERSION,
2646 REVENUE_BUDGET_C_VERSION,
2647 REVENUE_BUDGET_CO_VERSION,
2648 COST_FORECAST_C_VERSION,
2649 REVENUE_FORECAST_C_VERSION) =
2650 (select /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) */
2651 map.NEW_CLOSED_DATE,
2652 map.NEW_PROJECT_ORGANIZATION_ID,
2653 nvl(map.COST_BUDGET_N_VERSION,status.COST_BUDGET_C_VERSION),
2654 nvl(map.COST_BUDGET_NO_VERSION,status.COST_BUDGET_CO_VERSION),
2655 nvl(map.REVENUE_BUDGET_N_VERSION,status.REVENUE_BUDGET_C_VERSION),
2656 nvl(map.REVENUE_BUDGET_NO_VERSION,status.REVENUE_BUDGET_CO_VERSION),
2657 nvl(map.COST_FORECAST_N_VERSION,status.COST_FORECAST_C_VERSION),
2658 nvl(map.REVENUE_FORECAST_N_VERSION,status.REVENUE_FORECAST_C_VERSION)
2659 from PJI_PJI_PROJ_BATCH_MAP map
2660 where map.WORKER_ID = p_worker_id and
2661 map.PROJECT_ID = status.PROJECT_ID)
2662 where PROJECT_ID in (select /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) */
2663 PROJECT_ID
2664 from PJI_PJI_PROJ_BATCH_MAP
2665 where WORKER_ID = p_worker_id);
2666
2667 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2668 (
2669 l_process,
2670 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_STATUSES(p_worker_id);'
2671 );
2672
2673 commit;
2674
2675 end UPDATE_BATCH_STATUSES;
2676
2677
2678 begin -- this protion is executed whenever the package is initialized
2679 g_global_start_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),PJI_RM_SUM_MAIN.g_date_mask);
2680
2681
2682 begin
2683 select ent_period_id,name,start_date,end_date
2684 into g_ent_start_period_id,g_ent_start_period_name,g_ent_start_date,g_ent_end_date
2685 from fii_time_ent_period
2686 where g_global_start_date between start_date AND end_date
2687 ;
2688
2689 select week_id,PJI_RM_SUM_MAIN.g_null,start_date,end_date
2690 into g_entw_start_period_id,g_entw_start_period_name,g_entw_start_date,g_entw_end_date
2691 from fii_time_week
2692 where g_global_start_date between start_date AND end_date
2693 ;
2694 exception
2695 when no_data_found then
2696 null;
2697 end;
2698
2699 g_global_start_J := to_char(g_global_start_date,'J');
2700 g_ent_start_J := to_char(g_ent_start_date,'J');
2701 g_ent_end_J := to_char(g_ent_end_date,'J');
2702 g_entw_start_J := to_char(g_entw_start_date,'J');
2703 g_entw_end_J := to_char(g_entw_end_date,'J');
2704
2705
2706 end PJI_FM_PLAN_EXTR;