[Home] [Help]
PACKAGE BODY: APPS.PA_PPR_ROLLUP_PERIOD_PVT
Source
1 package body PA_ppr_rollup_period_PVT as
2 /* $Header: PAPPR05B.pls 120.0.12020000.6 2013/05/08 23:37:34 sachandr noship $ * */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROGRESS_PVT';
5 G_DEBUG_MODE varchar2(1);
6
7
8 procedure ppr_log (
9 p_msg IN VARCHAR2,
10 p_level IN number default 0
11 ) as
12
13 begin
14
15 -- if ( p_level <= g_log_level ) then
16
17 -- insert into sri_log values ( fnd_log_messages_s.nextval, p_msg);
18 --DBMS_OUTPUT.PUT_LINE( to_char(sysdate,'DD-MON HH24:MI:SS')||' :'||p_msg);
19 -- commit;
20 null;
21 -- end if;
22 IF G_debug_mode = 'Y' THEN
23 pa_debug.write('PA_ppr_rollup_period_PVT: ' , p_msg ,1);
24 END IF;
25
26
27
28 end ppr_log;
29
30
31 procedure create_periodic_data
32 (
36 ,p_debug_mode IN VARCHAR2 default 'N'
33 p_commit IN VARCHAR2 default 'N'
34 ,p_calling_mode IN VARCHAR2 default 'CREATE'
35 ,p_fact_slice IN VARCHAR2 default 'WBS'
37 ,p_project_id In number default -1
38 ,plan3_cost_ver_id In number default 0
39 ,plan3_rev_ver_id In number default 0
40 ,plan1_cost_ver_id In number default 0
41 ,plan1_rev_ver_id In number default 0
42 ,plan2_cost_ver_id In number default 0
43 ,plan2_rev_ver_id In number default 0
44 ,ori_cst_budget_ver_id In number default 0
45 ,ori_rev_budget_ver_id In number default 0
46 ,curr_cst_budget_ver_id In number default 0
47 ,curr_rev_budget_ver_id In number default 0
48 ,p_rbs_version_id in number default 0
49 ,p_cbs_version_id in number default 0
50 ,p_proj_element_id in number default 0
51 ,p_rbs_element_id in number default 0
52 ,p_cbs_element_id in number default 0
53 ,pr_cst_fcst_ver_id in number default 0
54 ,pr_rev_fcst_ver_id in number default 0
55 ,act_ver_id in number default 0
56 ) as
57
58 x_status varchar2(1);
59 x_msg_count number;
60 x_msg_data varchar2(250);
61
62 begin
63
64 -- SRI_BUG START
65
66 delete from pa_pjt_fact_period;
67
68 ppr_log(' create_periodic_data called ');
69
70 -- SRI_BUG END
71
72 if ( plan3_cost_ver_id > 0 ) then --satya
73
74 PA_ppr_rollup_period_PVT.plan_rollup(
75 p_commit => p_commit
76 ,p_calling_mode => p_calling_mode
77 ,p_fact_slice => p_fact_slice
78 ,p_debug_mode => p_debug_mode
79 ,p_project_id => p_project_id
80 ,p_budget_version_id => plan3_cost_ver_id
81 ,p_rbs_version_id => p_rbs_version_id
82 ,p_cbs_version_id => p_cbs_version_id
83 ,p_proj_element_id => p_proj_element_id
84 ,p_rbs_element_id => p_rbs_element_id
85 ,p_cbs_element_id => p_cbs_element_id
86 ,x_return_status => x_status
87 ,x_msg_count => x_msg_count
88 ,x_msg_data => x_msg_data
89 ) ;
90
91 end if;
92
93 if ( plan3_rev_ver_id > 0 ) then --satya
94
95 PA_ppr_rollup_period_PVT.plan_rollup(
96 p_commit => p_commit
97 ,p_calling_mode => p_calling_mode
98 ,p_fact_slice => p_fact_slice
99 ,p_debug_mode => p_debug_mode
100 ,p_project_id => p_project_id
101 ,p_budget_version_id => plan3_rev_ver_id
102 ,p_rbs_version_id => p_rbs_version_id
103 ,p_cbs_version_id => p_cbs_version_id
104 ,p_proj_element_id => p_proj_element_id
105 ,p_rbs_element_id => p_rbs_element_id
106 ,p_cbs_element_id => p_cbs_element_id
107 ,x_return_status => x_status
108 ,x_msg_count => x_msg_count
109 ,x_msg_data => x_msg_data
110 ) ;
111
112 end if;
113
114 if ( plan2_cost_ver_id > 0 ) then
115
116 PA_ppr_rollup_period_PVT.plan_rollup(
117 p_commit => p_commit
118 ,p_calling_mode => p_calling_mode
119 ,p_fact_slice => p_fact_slice
120 ,p_debug_mode => p_debug_mode
121 ,p_project_id => p_project_id
122 ,p_budget_version_id => plan2_cost_ver_id
123 ,p_rbs_version_id => p_rbs_version_id
124 ,p_cbs_version_id => p_cbs_version_id
125 ,p_proj_element_id => p_proj_element_id
126 ,p_rbs_element_id => p_rbs_element_id
127 ,p_cbs_element_id => p_cbs_element_id
128 ,x_return_status => x_status
129 ,x_msg_count => x_msg_count
130 ,x_msg_data => x_msg_data
131 ) ;
132
133 end if;
134
135
136 if ( plan2_rev_ver_id > 0 ) then
137
138 PA_ppr_rollup_period_PVT.plan_rollup(
139 p_commit => p_commit
140 ,p_calling_mode => p_calling_mode
141 ,p_fact_slice => p_fact_slice
142 ,p_debug_mode => p_debug_mode
143 ,p_project_id => p_project_id
144 ,p_budget_version_id => plan2_rev_ver_id
145 ,p_rbs_version_id => p_rbs_version_id
146 ,p_cbs_version_id => p_cbs_version_id
147 ,p_proj_element_id => p_proj_element_id
148 ,p_rbs_element_id => p_rbs_element_id
149 ,p_cbs_element_id => p_cbs_element_id
150 ,x_return_status => x_status
151 ,x_msg_count => x_msg_count
152 ,x_msg_data => x_msg_data
153 ) ;
154
155 end if;
156
157
158 if ( plan1_cost_ver_id > 0 ) then
159
160
161 PA_ppr_rollup_period_PVT.plan_rollup(
162 p_commit => p_commit
163 ,p_calling_mode => p_calling_mode
164 ,p_fact_slice => p_fact_slice
165 ,p_debug_mode => p_debug_mode
166 ,p_project_id => p_project_id
167 ,p_budget_version_id => plan1_cost_ver_id
168 ,p_rbs_version_id => p_rbs_version_id
169 ,p_cbs_version_id => p_cbs_version_id
170 ,p_proj_element_id => p_proj_element_id
171 ,p_rbs_element_id => p_rbs_element_id
172 ,p_cbs_element_id => p_cbs_element_id
173 ,x_return_status => x_status
174 ,x_msg_count => x_msg_count
175 ,x_msg_data => x_msg_data
176 ) ;
177
178 end if;
179
180
181 if ( plan1_rev_ver_id > 0 ) then
182
183 PA_ppr_rollup_period_PVT.plan_rollup(
184 p_commit => p_commit
185 ,p_calling_mode => p_calling_mode
186 ,p_fact_slice => p_fact_slice
187 ,p_debug_mode => p_debug_mode
188 ,p_project_id => p_project_id
189 ,p_budget_version_id => plan1_rev_ver_id
190 ,p_rbs_version_id => p_rbs_version_id
191 ,p_cbs_version_id => p_cbs_version_id
192 ,p_proj_element_id => p_proj_element_id
193 ,p_rbs_element_id => p_rbs_element_id
194 ,p_cbs_element_id => p_cbs_element_id
195 ,x_return_status => x_status
196 ,x_msg_count => x_msg_count
197 ,x_msg_data => x_msg_data
198 ) ;
199
200 end if;
201
202 if ( curr_cst_budget_ver_id > 0 ) then
203
204 PA_ppr_rollup_period_PVT.plan_rollup(
205 p_commit => p_commit
206 ,p_calling_mode => p_calling_mode
207 ,p_fact_slice => p_fact_slice
208 ,p_debug_mode => p_debug_mode
209 ,p_project_id => p_project_id
210 ,p_budget_version_id => curr_cst_budget_ver_id
211 ,p_rbs_version_id => p_rbs_version_id
212 ,p_cbs_version_id => p_cbs_version_id
213 ,p_proj_element_id => p_proj_element_id
214 ,p_rbs_element_id => p_rbs_element_id
215 ,p_cbs_element_id => p_cbs_element_id
216 ,x_return_status => x_status
217 ,x_msg_count => x_msg_count
218 ,x_msg_data => x_msg_data
219 ) ;
220
221 end if;
222
223
224 if ( curr_rev_budget_ver_id > 0 ) then
225
226 PA_ppr_rollup_period_PVT.plan_rollup(
227 p_commit => p_commit
228 ,p_calling_mode => p_calling_mode
229 ,p_fact_slice => p_fact_slice
230 ,p_debug_mode => p_debug_mode
231 ,p_project_id => p_project_id
232 ,p_budget_version_id => curr_rev_budget_ver_id
233 ,p_rbs_version_id => p_rbs_version_id
234 ,p_cbs_version_id => p_cbs_version_id
235 ,p_proj_element_id => p_proj_element_id
236 ,p_rbs_element_id => p_rbs_element_id
237 ,p_cbs_element_id => p_cbs_element_id
238 ,x_return_status => x_status
239 ,x_msg_count => x_msg_count
240 ,x_msg_data => x_msg_data
241 ) ;
242
243 end if;
244
245 if ( ori_cst_budget_ver_id > 0 ) then
246
247 PA_ppr_rollup_period_PVT.plan_rollup(
248 p_commit => p_commit
249 ,p_calling_mode => p_calling_mode
250 ,p_fact_slice => p_fact_slice
251 ,p_debug_mode => p_debug_mode
252 ,p_project_id => p_project_id
253 ,p_budget_version_id => ori_cst_budget_ver_id
254 ,p_rbs_version_id => p_rbs_version_id
255 ,p_cbs_version_id => p_cbs_version_id
256 ,p_proj_element_id => p_proj_element_id
257 ,p_rbs_element_id => p_rbs_element_id
258 ,p_cbs_element_id => p_cbs_element_id
259 ,x_return_status => x_status
260 ,x_msg_count => x_msg_count
261 ,x_msg_data => x_msg_data
262 ) ;
263
264 end if;
265
266 if ( ori_rev_budget_ver_id > 0 ) then
267
268 PA_ppr_rollup_period_PVT.plan_rollup(
269 p_commit => p_commit
270 ,p_calling_mode => p_calling_mode
271 ,p_fact_slice => p_fact_slice
272 ,p_debug_mode => p_debug_mode
273 ,p_project_id => p_project_id
274 ,p_budget_version_id => ori_rev_budget_ver_id
275 ,p_rbs_version_id => p_rbs_version_id
276 ,p_cbs_version_id => p_cbs_version_id
277 ,p_proj_element_id => p_proj_element_id
278 ,p_rbs_element_id => p_rbs_element_id
279 ,p_cbs_element_id => p_cbs_element_id
280 ,x_return_status => x_status
281 ,x_msg_count => x_msg_count
282 ,x_msg_data => x_msg_data
283 ) ;
284
285 end if;
286
287
288
289 if ( pr_cst_fcst_ver_id > 0 ) then
290
291 PA_ppr_rollup_period_PVT.plan_rollup(
292 p_commit => p_commit
293 ,p_calling_mode => p_calling_mode
294 ,p_fact_slice => p_fact_slice
295 ,p_debug_mode => p_debug_mode
296 ,p_project_id => p_project_id
297 ,p_budget_version_id => pr_cst_fcst_ver_id
298 ,p_rbs_version_id => p_rbs_version_id
299 ,p_cbs_version_id => p_cbs_version_id
300 ,p_proj_element_id => p_proj_element_id
301 ,p_rbs_element_id => p_rbs_element_id
302 ,p_cbs_element_id => p_cbs_element_id
303 ,x_return_status => x_status
304 ,x_msg_count => x_msg_count
305 ,x_msg_data => x_msg_data
306 ) ;
307
308 end if;
309
310 if ( pr_rev_fcst_ver_id > 0 ) then
311
312 PA_ppr_rollup_period_PVT.plan_rollup(
313 p_commit => p_commit
314 ,p_calling_mode => p_calling_mode
315 ,p_fact_slice => p_fact_slice
316 ,p_debug_mode => p_debug_mode
317 ,p_project_id => p_project_id
318 ,p_budget_version_id => pr_rev_fcst_ver_id
319 ,p_rbs_version_id => p_rbs_version_id
320 ,p_cbs_version_id => p_cbs_version_id
321 ,p_proj_element_id => p_proj_element_id
322 ,p_rbs_element_id => p_rbs_element_id
323 ,p_cbs_element_id => p_cbs_element_id
324 ,x_return_status => x_status
325 ,x_msg_count => x_msg_count
326 ,x_msg_data => x_msg_data
327 ) ;
328
329 end if;
330
331 if ( act_ver_id > 0 ) then
332
333 PA_ppr_rollup_period_PVT.plan_rollup(
334 p_commit => p_commit
335 ,p_calling_mode => p_calling_mode
336 ,p_fact_slice => p_fact_slice
337 ,p_debug_mode => p_debug_mode
338 ,p_project_id => p_project_id
339 ,p_budget_version_id => act_ver_id
340 ,p_rbs_version_id => p_rbs_version_id
341 ,p_cbs_version_id => p_cbs_version_id
342 ,p_proj_element_id => p_proj_element_id
343 ,p_rbs_element_id => p_rbs_element_id
344 ,p_cbs_element_id => p_cbs_element_id
345 ,x_return_status => x_status
346 ,x_msg_count => x_msg_count
347 ,x_msg_data => x_msg_data
348 ) ;
349
350 end if;
351
352
353
354 end ;
355
356
357
358 procedure plan_rollup (
359 p_commit IN VARCHAR2 default 'N'
360 ,p_calling_mode IN VARCHAR2 default 'CREATE'
361 ,p_fact_slice IN VARCHAR2 default 'WBS'
362 ,p_debug_mode IN VARCHAR2 default 'N'
363 ,p_project_id In number default -1
364 ,p_budget_version_id in number default -1
365 ,p_rbs_version_id in number default -1
366 ,p_cbs_version_id in number default -1
367 ,p_proj_element_id in number default -1
368 ,p_rbs_element_id in number default -1
369 ,p_cbs_element_id in number default -1
370 ,x_return_status OUT NOCOPY VARCHAR2
371 ,x_msg_count OUT NOCOPY NUMBER
372 ,x_msg_data OUT NOCOPY VARCHAR2
373 ) as
374
375
376 l_return_status VARCHAR2(1);
377 l_msg_count NUMBER ;
378 l_msg_data VARCHAR2(250);
379
380 c number;
381
382 l_project_currency varchar2(10);
383 l_functional_currency varchar2(10);
384
385 l_version_type varchar2(15);
386
387
388
389 l_rbs_cbs_version_id_2 number;
390
391
392 l_struct_ver_id number;
393 l_struct_elem_id number;
394 l_project_id number;
398 l_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
395 l_rbs_cbs_version_id number ;
396 l_budget_version_id number;
397
399 l_task_level_arr PA_PLSQL_DATATYPES.NumTabTyp;
400 l_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
401 l_leaf_node_arr PA_PLSQL_DATATYPES.NumTabTyp;
402
403
404 l_prj_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
405 l_prj_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
406 l_prj_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
407 l_prj_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
408 l_prj_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
409 l_prj_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
410 l_prj_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
411 l_prj_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
412 l_prj_amount9_arr PA_PLSQL_DATATYPES.NumTabTyp;
413 l_prj_amount10_arr PA_PLSQL_DATATYPES.NumTabTyp;
414
415
416 l_func_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
417 l_func_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
418 l_func_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
419 l_func_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
420 l_func_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
421 l_func_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
422 l_func_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
423 l_func_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
424 l_func_amount9_arr PA_PLSQL_DATATYPES.NumTabTyp;
425 l_func_amount10_arr PA_PLSQL_DATATYPES.NumTabTyp;
426
427 l_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
428 l_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
429 l_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
430
431
432 l_out_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
433 l_out_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
434 l_out_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
435
436 l_out_labor_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
437 l_out_labor_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
438 l_out_labor_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
439
440 l_out_equip_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
441 l_out_equip_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
442 l_out_equip_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
443
444 l_out_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
445 l_upd_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
446
447 l_ins_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
448 l_upd_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
449
450 l_out_task_index_arr PA_PLSQL_DATATYPES.NumTabTyp;
451
452 l_out_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
453 l_out_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
454 l_out_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
455 l_out_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
456 l_out_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
457 l_out_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
458 l_out_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
459 l_out_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
460
461
462 l_out_bill_labor_Qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
463 l_out_bill_equip_Qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
464 l_out_bill_labor_Qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
465 l_out_bill_equip_Qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
466 l_out_bill_labor_Qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
467 l_out_bill_equip_Qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
468 l_out_bill_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
469 l_out_bill_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
470 l_out_bill_labor_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
471 l_out_bill_labor_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
472 l_out_equip_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
473 l_out_equip_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
474 l_out_capit_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
475 l_out_capit_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
476 l_out_labor_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
477 l_out_labor_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
478
479 l_out_labor_amt3_arr PA_PLSQL_DATATYPES.NumTabTyp;
480 l_out_labor_amt4_arr PA_PLSQL_DATATYPES.NumTabTyp;
481 l_out_labor_amt5_arr PA_PLSQL_DATATYPES.NumTabTyp;
482 l_out_labor_amt6_arr PA_PLSQL_DATATYPES.NumTabTyp;
483 l_out_labor_amt7_arr PA_PLSQL_DATATYPES.NumTabTyp;
484 l_out_labor_amt8_arr PA_PLSQL_DATATYPES.NumTabTyp;
485
486
487 l_out_equip_amt3_arr PA_PLSQL_DATATYPES.NumTabTyp;
488 l_out_equip_amt4_arr PA_PLSQL_DATATYPES.NumTabTyp;
489 l_out_equip_amt5_arr PA_PLSQL_DATATYPES.NumTabTyp;
490 l_out_equip_amt6_arr PA_PLSQL_DATATYPES.NumTabTyp;
491 l_out_equip_amt7_arr PA_PLSQL_DATATYPES.NumTabTyp;
492 l_out_equip_amt8_arr PA_PLSQL_DATATYPES.NumTabTyp;
493
494
495
496
497 l_res_class_arr PA_PLSQL_DATATYPES.Char30TabTyp;
498 l_billable_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
499
500 -- Open Item : Can we sum the resource assignments at task level here itself ? Will the sum scale ?
501
502 cursor c2 is
503 SELECT /*+ ordered */
504 ra.rate_based_flag billable_flag ,
505 to_number(to_char(bl.start_date,'YYYYMMDD')) wbs_element_id ,
506 -- ra.task_id wbs_element_id ,
507 ra.resource_class_code resource_class ,
508 sum(nvl(bl.project_raw_cost,0)) ,
509 sum(nvl(bl.project_burdened_COST,0)) ,
510 sum(nvl(bl.project_revenue,0)) ,
511 sum(nvl(bl.project_init_raw_cost,0)) ,
512 sum(nvl(bl.project_init_burdened_cost,0)) ,
513 sum(nvl(bl.project_init_revenue,0)) ,
514 sum( decode( l_version_type,'FINANCIAL', nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0), 0) ) ,
515 sum(decode( l_version_type,'FINANCIAL', nvl(bl.project_burdened_COST,0) - nvl(bl.project_init_burdened_cost,0), 0)) ,
516 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
517 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
518 sum(nvl(bl.revenue,0)) func_revenue ,
519 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
520 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
521 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
522 sum(decode( l_version_type,'FINANCIAL', nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0), 0)) func_etc_raw_cost ,
523 sum(decode( l_version_type,'FINANCIAL', nvl(bl.burdened_COST,0) - nvl(bl.init_burdened_cost,0), 0)) func_etc_brdn_cost ,
524 sum(nvl(bl.quantity,0) ) quantity,
525 sum(nvl(bl.init_quantity,0)) actual_quantity ,
526 sum(decode( l_version_type,'FINANCIAL', nvl(bl.quantity,0) - nvl(bl.init_quantity,0), 0)) etc_quantity ,
527 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ,
528 1 ,
529 1 wbs_level
530 FROM PA_RESOURCE_ASSIGNMENTS ra ,
531 PA_BUDGET_LINES bl ,
532 pa_ppr_obj_tmp b
533 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
534 and ra.budget_version_id = l_budget_version_id
535 and ra.task_id = b.object_id
536 group by
537 ra.rate_based_flag ,
538 to_number(to_char(bl.start_date,'YYYYMMDD')),
539 ra.resource_class_code,
540 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ;
541
542
543
544
545
546
547
548 cursor c2_rbs_cbs is
549 SELECT /*+ ordered */
550 ra.rate_based_flag billable_flag ,
551 to_number(to_char(bl.start_date,'YYYYMMDD')) rbs_element_id ,
552 ra.resource_class_code resource_class ,
553 sum(nvl(bl.project_raw_cost,0)) ,
554 sum(nvl(bl.project_burdened_COST,0)) ,
555 sum(nvl(bl.project_revenue,0)) ,
556 sum(nvl(bl.project_init_raw_cost,0)) ,
557 sum(nvl(bl.project_init_burdened_cost,0)) ,
558 sum(nvl(bl.project_init_revenue,0)) ,
559 sum( decode( l_version_type,'FINANCIAL', nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0), 0) ) ,
560 sum(decode( l_version_type,'FINANCIAL', nvl(bl.project_burdened_COST,0) - nvl(bl.project_init_burdened_cost,0), 0)) ,
561 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
562 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
563 sum(nvl(bl.revenue,0)) func_revenue ,
564 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
565 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
566 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
567 sum(decode( l_version_type,'FINANCIAL', nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0), 0)) func_etc_raw_cost ,
568 sum(decode( l_version_type,'FINANCIAL', nvl(bl.burdened_COST,0) - nvl(bl.init_burdened_cost,0), 0)) func_etc_brdn_cost ,
569 sum(nvl(bl.quantity,0) ) quantity,
570 sum(nvl(bl.init_quantity,0)) actual_quantity ,
571 sum(decode( l_version_type,'FINANCIAL', nvl(bl.quantity,0) - nvl(bl.init_quantity,0), 0)) etc_quantity ,
572 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ,
573 1 ,
574 1 wbs_level
575 FROM PA_RESOURCE_ASSIGNMENTS ra ,
576 PA_BUDGET_LINES bl ,
577 pa_ppr_obj_tmp b
578 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
579 and ra.budget_version_id = l_budget_version_id
580 and (
581 ( p_fact_slice = 'RBS' AND ra.rbs_element_id = b.object_id )
582 OR
583 ( p_fact_slice = 'CBS' AND ra.cbs_element_id = b.object_id )
584 )
585 group by
586 ra.rate_based_flag,
587 to_number(to_char(bl.start_date,'YYYYMMDD')) ,
588 ra.resource_class_code,
589 '/'||to_number(to_char(bl.start_date,'YYYYMMDD'));
590
591
592
593 cursor c2_wbs_to_rbs_or_cbs is
594 SELECT /*+ ordered */
595 ra.rate_based_flag billable_flag ,
596 to_number(to_char(bl.start_date,'YYYYMMDD')) rbs_element_id ,
597 ra.resource_class_code resource_class ,
598 sum(nvl(bl.project_raw_cost,0)) ,
599 sum(nvl(bl.project_burdened_COST,0)) ,
600 sum(nvl(bl.project_revenue,0)) ,
601 sum(nvl(bl.project_init_raw_cost,0)) ,
602 sum(nvl(bl.project_init_burdened_cost,0)) ,
603 sum(nvl(bl.project_init_revenue,0)) ,
604 sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
605 sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
606 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
607 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
608 sum(nvl(bl.revenue,0)) func_revenue ,
609 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
610 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
611 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
612 sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
613 sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
614 sum(nvl(bl.quantity,0) ) quantity,
615 sum(nvl(bl.init_quantity,0)) actual_quantity ,
616 sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
617 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ,
618 1 ,
619 1 wbs_level
620 FROM PA_RESOURCE_ASSIGNMENTS ra ,
621 PA_BUDGET_LINES bl ,
622 pa_ppr_obj_tmp c,
623 pa_ppr_obj_tmp2 b
624 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
625 and ra.budget_version_id = l_budget_version_id
626 and ra.task_id = c.object_id
627 and (
628 ( p_fact_slice = 'WBS_TO_RBS' AND ra.rbs_element_id = b.object_id ) OR
629 ( p_fact_slice = 'WBS_TO_CBS' AND ra.cbs_element_id = b.object_id )
630 )
631 group by
632 ra.rate_based_flag ,
633 to_number(to_char(bl.start_date,'YYYYMMDD')),
634 ra.resource_class_code,
635 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ;
636
637
638 cursor c2_rbs_to_wbs is
639 SELECT /*+ ordered */
640 ra.rate_based_flag billable_flag ,
641 to_number(to_char(bl.start_date,'YYYYMMDD')) task_id ,
642 ra.resource_class_code resource_class ,
643 sum(nvl(bl.project_raw_cost,0)) ,
644 sum(nvl(bl.project_burdened_COST,0)) ,
645 sum(nvl(bl.project_revenue,0)) ,
646 sum(nvl(bl.project_init_raw_cost,0)) ,
647 sum(nvl(bl.project_init_burdened_cost,0)) ,
648 sum(nvl(bl.project_init_revenue,0)) ,
649 sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
650 sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
651 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
652 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
653 sum(nvl(bl.revenue,0)) func_revenue ,
654 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
655 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
656 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
657 sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
658 sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
659 sum(nvl(bl.quantity,0) ) quantity,
660 sum(nvl(bl.init_quantity,0)) actual_quantity ,
661 sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
662 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ,
663 1 ,
664 1 wbs_level
665 FROM PA_RESOURCE_ASSIGNMENTS ra ,
666 PA_BUDGET_LINES bl ,
667 pa_ppr_obj_tmp c,
668 pa_ppr_obj_tmp2 b
669 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
670 and ra.budget_version_id = l_budget_version_id
671 and ra.rbs_element_id = c.object_id
672 and ra.task_id = b.object_id
673 group by
674 ra.rate_based_flag ,
675 to_number(to_char(bl.start_date,'YYYYMMDD')),
676 ra.resource_class_code,
677 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ;
678
679
680
681 cursor c2_cbs_to_wbs_or_rbs is
682 SELECT /*+ ordered */
683 ra.rate_based_flag billable_flag ,
684 to_number(to_char(bl.start_date,'YYYYMMDD')) element_id ,
685 ra.resource_class_code resource_class ,
686 sum(nvl(bl.project_raw_cost,0)) ,
687 sum(nvl(bl.project_burdened_COST,0)) ,
688 sum(nvl(bl.project_revenue,0)) ,
689 sum(nvl(bl.project_init_raw_cost,0)) ,
690 sum(nvl(bl.project_init_burdened_cost,0)) ,
691 sum(nvl(bl.project_init_revenue,0)) ,
692 sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
693 sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
694 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
695 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
696 sum(nvl(bl.revenue,0)) func_revenue ,
697 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
698 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
699 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
700 sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
701 sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
702 sum(nvl(bl.quantity,0) ) quantity,
703 sum(nvl(bl.init_quantity,0)) actual_quantity ,
704 sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
705 '/'||to_number(to_char(bl.start_date,'YYYYMMDD')) ,
706 1 ,
707 1 wbs_level
708 FROM PA_RESOURCE_ASSIGNMENTS ra ,
709 PA_BUDGET_LINES bl ,
710 pa_ppr_obj_tmp c,
711 pa_ppr_obj_tmp2 b
712 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
713 and ra.budget_version_id = l_budget_version_id
714 and ra.cbs_element_id = c.object_id
715 and (
716 ( p_fact_slice = 'CBS_TO_RBS' AND ra.rbs_element_id = b.object_id ) OR
717 ( p_fact_slice = 'CBS_TO_WBS' AND ra.task_id = b.object_id )
718 )
719 group by
720 ra.rate_based_flag ,
721 to_number(to_char(bl.start_date,'YYYYMMDD')),
722 ra.resource_class_code,
723 '/'||to_number(to_char(bl.start_date,'YYYYMMDD'));
724
725
726
727 l_object_id number;
728 l_object_id_2 number;
729
730 cursor c0 is
731 SELECT object_id
732 FROM pa_ppr_obj_tmp r
733 START WITH r.parent_object_id = l_object_id CONNECT BY
734 PRIOR r.object_id = r.parent_object_id;
735
736 cursor c1 is
737 SELECT object_id
738 FROM pa_ppr_obj_tmp2 r
739 START WITH r.parent_object_id = l_object_id_2 CONNECT BY
740 PRIOR r.object_id = r.parent_object_id ;
741
742
743 l_cnt number ;
744
745 l_count1 number ;
746
747 begin
748
749 fnd_profile.get('PA_DEBUG_MODE',G_debug_mode);
750 G_debug_mode := NVL(g_debug_mode, 'N');
751
752 ppr_log('START ... ',0);
753
754 ppr_log(' p_commit '|| p_commit ,0);
755 ppr_log(' p_calling_mode '|| p_calling_mode ,0);
756 ppr_log(' p_fact_slice '|| p_fact_slice,0 );
757 ppr_log(' p_debug_mode '|| p_debug_mode ,0);
758 ppr_log(' p_project_id '|| p_project_id ,0);
759 ppr_log(' p_budget_version_id '|| p_budget_version_id,0 );
760 ppr_log(' p_rbs_version_id '|| p_rbs_version_id ,0);
761 ppr_log(' p_cbs_version_id '|| p_cbs_version_id,0 );
762 ppr_log(' p_proj_element_id '|| p_proj_element_id ,0);
763 ppr_log(' p_rbs_element_id '|| p_rbs_element_id ,0);
764 ppr_log(' p_cbs_element_id '|| p_cbs_element_id ,0 );
765
766
767 -- SRI_BUG START
768
769 select count(*) into l_count1
770 from pa_pjt_fact_period
771 where plan_version_id = p_budget_version_id
772 and rownum = 1 ;
773
774
775
776 if ( l_count1 > 0 ) then
777
778
779 ppr_log(' DATA EXISTS and returning ' ,0 );
780
781 return ;
782
783 end if;
784
785 -- delete from pa_pjt_fact_period; -- SRI_BUG
786 ppr_log(' NO DATA EXISTS ' ,0 );
787
788 -- SRI_BUG END
789
790
791
792
793
794
795 l_project_id := p_project_id;
796 l_budget_version_id := p_budget_version_id;
797
798 if ( p_fact_slice in ( 'RBS', 'RBS_TO_WBS') ) then
799
800 l_rbs_cbs_version_id := p_rbs_version_id ;
801
802
803 elsif ( p_fact_slice in ( 'CBS','CBS_TO_RBS','CBS_TO_WBS' ) ) then
804
805 l_rbs_cbs_version_id := p_cbs_version_id ;
806
807
808 else
809 l_rbs_cbs_version_id := -1;
810 end if;
811
812
813 if ( p_fact_slice in ( 'WBS_TO_RBS', 'WBS_TO_CBS','WBS' ) ) then
814
815 l_object_id := p_proj_element_id ;
816
817 if ( p_fact_slice = 'WBS_TO_RBS' ) then
818
819 if ( p_rbs_element_id = -1 or p_rbs_element_id = 0 ) then -- SRI_BUG add = 0 condition
820 l_object_id_2 := p_rbs_version_id;
821 else
822 l_object_id_2 := p_rbs_element_id;
823 end if;
824
825 end if;
826
827 if ( p_fact_slice = 'WBS_TO_CBS' ) then
828
829 if ( p_cbs_element_id = -1 or p_cbs_element_id = 0 ) then -- SRI_BUG add = 0 condition
830 l_object_id_2 := p_cbs_version_id;
831 else
832 l_object_id_2 := p_cbs_element_id;
833 end if;
834
835 end if;
836
837
838 elsif ( p_fact_slice in ( 'CBS_TO_RBS', 'CBS_TO_WBS', 'CBS' ) ) then
839
840 l_object_id := p_cbs_element_id ;
841
842 if ( p_fact_slice in ( 'WBS_TO_RBS','CBS_TO_RBS' ) ) then
843
844 if ( p_rbs_element_id = -1 or p_rbs_element_id = 0 ) then -- SRI_BUG
845 l_object_id_2 := p_rbs_version_id;
846 else
847 l_object_id_2 := p_rbs_element_id;
848 end if;
849
850 end if;
851
852
853 elsif ( p_fact_slice in ( 'RBS_TO_WBS' ,'RBS' ) ) then
854
855 l_object_id := p_rbs_element_id ;
856
857 end if;
858
859
860 ppr_log(' Before Projects table ' ,0);
861
862 -- l_project_id := 4546;
863 -- l_struct_ver_id := 94014;
864
865 -- l_budget_version_id := 168752;
866
867 -- Project level data
868 select project_currency_code , projfunc_currency_code
869 into l_project_currency, l_functional_currency
870 from pa_projects_All
871 where project_id = l_project_id;
872
873 --l_functional_currency := 'GBP';
874
875
876 ppr_log(' AFter Currency ' ,0);
877
878 select /*+ ordered */ distinct decode(bud.fin_plan_type_id,10,'WORKPLAN','FINANCIAL') ,
879 s.element_version_id, s.proj_element_id
880 into l_version_type , l_struct_ver_id , l_struct_elem_id
881 from pa_budget_versions bud,
882 pa_proj_elem_ver_structure s,
883 pa_proj_structure_types ppst,
884 pa_structure_types pst
885 where
886 bud.budget_version_id = l_budget_version_id
887 and s.proj_element_id = ppst.proj_element_id (+)
888 and ppst.structure_type_id = pst.structure_type_id (+)
889 and pst.structure_type_class_code (+) = 'WORKPLAN'
890 and bud.project_structure_version_id = s.element_version_id (+);
891
892
893 ppr_log(' AFter WORKPLAN l_version_type '||l_version_type ,0);
894
895 if ( l_version_type = 'FINANCIAL' ) then
896
897 select /*+ ordered */
898 distinct 0, s.proj_element_id
899 into l_struct_ver_id , l_struct_elem_id
900 from
901 pa_proj_elem_ver_structure s,
902 pa_proj_structure_types ppst,
903 pa_structure_types pst
904 where
905 s.project_id = l_project_id
906 and s.proj_element_id = ppst.proj_element_id
907 and ppst.structure_type_id = pst.structure_type_id
908 and pst.structure_type_class_code = 'FINANCIAL';
909
910 end if;
911
912 ppr_log(' AFter FINANCIAL l_struct_ver_id , l_struct_elem_id '||l_struct_ver_id||' '||l_struct_elem_id ,0);
913
914 -- l_functional_currency := 'GBP';
915
916 --- Scenodary slice task / rbs / cbs list
917
918
919
920 if ( p_fact_slice in ('CBS_TO_WBS' , 'RBS_TO_WBS')) then
921
922 if ( p_proj_element_id = -1 or p_proj_element_id = 0 ) then -- SRI_BUG
923 l_object_id_2 := l_struct_elem_id;
924 else
925 l_object_id_2 := p_proj_element_id;
926 end if;
927
928 end if;
929
930
931
932 delete from pa_ppr_obj_tmp2;
933 delete from pa_ppr_obj_tmp;
934
935 if ( p_fact_slice in ( 'WBS_TO_RBS' , 'WBS_TO_CBS' , 'WBS' ) ) then
936
937 if ( l_version_type = 'WORKPLAN' ) then
938
939 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
940 select /*+ ordered */
941 a.proj_element_id , nvl(c.proj_element_id,l_struct_elem_id)
942 from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
943 where a.project_id = l_project_id
944 and a.object_type = 'PA_TASKS' -- SRI_BUG2
945 and a.parent_structure_version_id = l_struct_ver_id
946 and a.element_version_id = b.object_id_to1 (+)
947 and b.object_type_From (+) = 'PA_TASKS'
948 and b.relationship_type (+) = 'S'
949 and b.object_id_from1 = c.element_version_id (+) ;
950
951 elsif ( l_version_type = 'FINANCIAL' ) then
952
953 -- For financial tasks and fully shared.
954 -- For budgets pa_tasks can be used directly
955
956 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
957 select /*+ ordered */
958 p.task_id , nvl(p.parent_task_id, l_struct_elem_id )
959 from pa_tasks p
960 where p.project_id = l_project_id ;
961
962 end if;
963
964
965 if ( l_object_id = 0 or l_object_id = -1 ) then -- SRI_BUG
966 l_object_id := l_struct_elem_id;
967 end if;
968
969 if p_fact_slice in ( 'WBS_TO_RBS' , 'WBS_TO_CBS' ) then
970
971
972 if ( p_fact_slice = 'WBS_TO_RBS' ) then
973
974 l_rbs_cbs_version_id_2 := p_rbs_version_id ;
975
976 else
977
978 l_rbs_cbs_version_id_2 := p_cbs_version_id ;
979
980 end if;
981
982
983 if ( l_object_id_2 = -1 or l_object_id_2 = 0 ) then -- SRI_BUG
984 l_object_id_2 := l_rbs_cbs_version_id_2;
985 end if;
986
987 ppr_log(' WBS_TO l_rbs_cbs_version_id_2 ... '||l_rbs_cbs_version_id_2,0);
988 ppr_log(' WBS_TO l_object_id_2 ... '||l_object_id_2,0);
989
990
991 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
992 select rbs_element_id , nvl(parent_element_id,l_rbs_cbs_version_id)
993 from pa_rbs_elements
994 where rbs_version_id = l_rbs_cbs_version_id_2;
995
996 end if;
997
998
999 elsif p_fact_slice in ( 'RBS_TO_WBS','CBS_TO_RBS', 'CBS_TO_WBS','CBS','RBS' ) then
1000
1001 -- l_struct_elem_id := 0;
1002
1003 if ( l_object_id = -1 or l_object_id = 0 ) then -- SRI_BUG
1004 l_object_id := l_rbs_cbs_version_id;
1005 end if;
1006
1007 ppr_log(' l_rbs_cbs_version_id '||l_rbs_cbs_version_id ,0);
1008 ppr_log(' l_object_id '||l_object_id ,0);
1009
1010 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
1011 select rbs_element_id , nvl(parent_element_id,l_rbs_cbs_version_id_2)
1012 from pa_rbs_elements
1013 where rbs_version_id = l_rbs_cbs_version_id ;
1014
1015
1016 if p_fact_slice in ( 'RBS_TO_WBS', 'CBS_TO_WBS' ) then
1017
1018
1019 if ( l_object_id_2 = -1 or l_object_id_2 = 0 ) then -- SRI_BUG
1020 l_object_id_2 := l_struct_elem_id;
1021 end if;
1022
1023 if ( l_version_type = 'WORKPLAN' ) then
1024
1025 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
1026 select /*+ ordered */
1027 a.proj_element_id , nvl(c.proj_element_id,l_struct_elem_id)
1028 from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
1029 where a.project_id = l_project_id
1030 and a.object_type = 'PA_TASKS' -- SRI_BUG2
1031 and a.parent_structure_version_id = l_struct_ver_id
1032 and a.element_version_id = b.object_id_to1 (+)
1033 and b.object_type_From (+) = 'PA_TASKS'
1034 and b.relationship_type (+) = 'S'
1035 and b.object_id_from1 = c.element_version_id (+) ;
1036
1037 elsif ( l_version_type = 'FINANCIAL' ) then
1038
1039 -- For financial tasks and fully shared.
1040 -- For budgets pa_tasks can be used directly
1041
1042 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
1043 select /*+ ordered */
1044 p.task_id , nvl(p.parent_task_id, l_struct_elem_id )
1045 from pa_tasks p
1046 where p.project_id = l_project_id ;
1047
1048 end if;
1049
1050 end if;
1051
1052
1053 if p_fact_slice in ( 'CBS_TO_RBS' ) then
1054
1055 l_rbs_cbs_version_id_2 := p_rbs_version_id ;
1056
1057 if ( l_object_id_2 = -1 or l_object_id_2 = 0 ) then -- SRI_BUG
1058 l_object_id_2 := l_rbs_cbs_version_id_2;
1059 end if;
1060
1061 ppr_log(' l_rbs_cbs_version_id 2 '||l_rbs_cbs_version_id_2 ,0);
1062 ppr_log(' l_object_id 2 '||l_object_id_2 ,0);
1063
1064 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
1065 select rbs_element_id , nvl(parent_element_id,l_rbs_cbs_version_id_2)
1066 from pa_rbs_elements
1067 where rbs_version_id = l_rbs_cbs_version_id_2 ;
1068
1069
1070 end if;
1071
1072 end if;
1073
1074
1075
1076 l_task_arr.delete;
1077
1078 OPEN c0;
1079
1080 FETCH c0
1081 BULK COLLECT INTO l_task_arr;
1082
1083 CLOSE c0;
1084
1085 ppr_log('FIRST l_task_arr '||l_task_arr.count,0);
1086
1087 delete from pa_ppr_obj_tmp ;
1088
1089 FORALL i IN 1..l_task_arr.count
1090 INSERT INTO pa_ppr_obj_tmp ( object_id ) values ( l_task_arr(i) ) ;
1091
1092 if ( g_log_level >0 ) then
1093 for k1 in 1..l_task_arr.count loop
1094
1095 ppr_log(' FIRST OBJECT ... '||l_task_arr(k1),1);
1096
1097 end loop;
1098 end if;
1099
1100 INSERT INTO pa_ppr_obj_tmp ( object_id ) values ( l_object_id ) ;
1101
1102
1103 l_task_arr.delete;
1104
1105 OPEN c1;
1106
1107 FETCH c1
1108 BULK COLLECT INTO l_task_arr;
1109
1110 CLOSE c1;
1111
1112 ppr_log('SECONDARY l_task_arr '||l_task_arr.count,0);
1113
1114 delete from pa_ppr_obj_tmp2 ;
1115
1116 FORALL i IN 1..l_task_arr.count
1117 INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_task_arr(i) ) ;
1118
1119 if ( g_log_level >0 ) then
1120 for k1 in 1..l_task_arr.count loop
1121
1122 ppr_log(' SECOND OBJECT ... '||l_task_arr(k1),1);
1123
1124 end loop;
1125 end if;
1126
1127 INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_object_id_2 ) ;
1128
1129
1130
1131 l_task_arr.delete;
1132 l_task_level_arr.delete;
1133 l_connect_path_arr.delete;
1134 l_leaf_node_arr.delete;
1135
1136 ppr_log('AFTER TASK ... ',0);
1137
1138 if ( p_fact_slice = 'WBS' and p_calling_mode = 'CREATE' ) then
1139
1140 OPEN c2;
1141
1142 FETCH c2
1143 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1144 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1145 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1146 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1147 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1148 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1149 l_connect_path_arr ,
1150 l_leaf_node_arr ,
1151 l_task_level_arr;
1152
1153 CLOSE c2;
1154
1155 elsif ( ( p_fact_slice = 'RBS' or p_fact_slice = 'CBS' ) and p_calling_mode = 'CREATE' ) then
1156
1157
1158 ppr_log('into RBS C2 cursor ',0);
1159
1160 OPEN c2_rbs_cbs;
1161
1162 FETCH c2_rbs_cbs
1163 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1164 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1165 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1166 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1167 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1168 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1169 l_connect_path_arr ,
1170 l_leaf_node_arr ,
1171 l_task_level_arr;
1172
1173 CLOSE c2_rbs_cbs;
1174
1175
1176 elsif ( p_fact_slice = 'WBS_TO_RBS' or p_fact_slice = 'WBS_TO_CBS' ) then
1177
1178
1179 ppr_log(' c2_wbs_to_rbs_or_cbs ... ',0);
1180
1181 OPEN c2_wbs_to_rbs_or_cbs;
1182
1183 FETCH c2_wbs_to_rbs_or_cbs
1184 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1185 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1186 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1187 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1188 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1189 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1190 l_connect_path_arr ,
1191 l_leaf_node_arr ,
1192 l_task_level_arr;
1193
1194 CLOSE c2_wbs_to_rbs_or_cbs;
1195
1196 elsif ( p_fact_slice = 'RBS_TO_WBS') then
1197
1198
1199 ppr_log(' rbs_to_wbs ... ',0);
1200
1201 OPEN c2_rbs_to_wbs;
1202
1203 FETCH c2_rbs_to_wbs
1204 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1205 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1206 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1207 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1208 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1209 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1210 l_connect_path_arr ,
1211 l_leaf_node_arr ,
1212 l_task_level_arr;
1213
1214 CLOSE c2_rbs_to_wbs;
1215
1216 elsif ( p_fact_slice IN ('CBS_TO_RBS','CBS_TO_WBS') ) then
1217
1218
1219 ppr_log(' rbs_to_wbs ... ');
1220
1221 OPEN c2_cbs_to_wbs_or_rbs;
1222
1223 FETCH c2_cbs_to_wbs_or_rbs
1224 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1225 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1226 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1227 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1228 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1229 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1230 l_connect_path_arr ,
1231 l_leaf_node_arr ,
1232 l_task_level_arr;
1233
1234 CLOSE c2_cbs_to_wbs_or_rbs;
1235
1236 end if;
1237
1238
1239
1240 ppr_log(' BEFORE ROLLUP_AMOUNTS l_task_arr.COUNT ... '||l_task_arr.COUNT,0);
1241
1242
1243 l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
1244 l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
1245
1246 l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
1247
1248 l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
1249 l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
1250 l_out_amount7_arr.delete;l_out_amount8_arr.delete;
1251
1252
1253 l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
1254 l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
1255 l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
1256 l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
1257 l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
1261 l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
1258 l_out_labor_amt2_arr.delete;
1259
1260 l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
1262
1263
1264 l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
1265 l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
1266 l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
1267
1268
1269 rollup_amounts (
1270 p_billable_flag_tab => l_billable_flag_arr
1271 ,p_object_id_tab => l_task_arr
1272 ,p_wbs_level_tab => l_task_level_arr
1273 ,p_connect_path_tab => l_connect_path_arr
1274 ,p_leaf_node_tab => l_leaf_node_arr
1275 ,p_resource_class_tab => l_res_class_arr
1276 ,p_amount1_tab => l_prj_amount1_arr
1277 ,p_amount2_tab => l_prj_amount2_arr
1278 ,p_amount3_tab => l_prj_amount3_arr
1279 ,p_amount4_tab => l_prj_amount4_arr
1280 ,p_amount5_tab => l_prj_amount5_arr
1281 ,p_amount6_tab => l_prj_amount6_arr
1282 ,p_amount7_tab => l_prj_amount7_arr
1283 ,p_amount8_tab => l_prj_amount8_arr
1284 ,p_qty1_tab => l_qty1_arr
1285 ,p_qty2_tab => l_qty2_arr
1286 ,p_qty3_tab => l_qty3_arr
1287 ,p_out_object_index_tab => l_out_task_index_arr
1288 ,p_out_object_id_tab => l_out_task_arr
1289 ,p_out_amount1_tab => l_out_amount1_arr
1290 ,p_out_amount2_tab => l_out_amount2_arr
1291 ,p_out_amount3_tab => l_out_amount3_arr
1292 ,p_out_amount4_tab => l_out_amount4_arr
1293 ,p_out_amount5_tab => l_out_amount5_arr
1294 ,p_out_amount6_tab => l_out_amount6_arr
1295 ,p_out_amount7_tab => l_out_amount7_arr
1296 ,p_out_amount8_tab => l_out_amount8_arr
1297 ,p_out_qty1_tab => l_out_qty1_arr
1298 ,p_out_qty2_tab => l_out_qty2_arr
1299 ,p_out_qty3_tab => l_out_qty3_arr
1300 ,p_out_labor_Qty1_tab => l_out_labor_qty1_arr
1301 ,p_out_equip_Qty1_tab => l_out_equip_qty1_arr
1302 ,p_out_labor_Qty2_tab => l_out_labor_qty2_arr
1303 ,p_out_equip_Qty2_tab => l_out_equip_qty2_arr
1304 ,p_out_labor_Qty3_tab => l_out_labor_qty3_arr
1305 ,p_out_equip_Qty3_tab => l_out_equip_qty3_arr
1306 ,p_out_bill_labor_Qty1_tab => l_out_bill_labor_Qty1_arr
1307 ,p_out_bill_equip_Qty1_tab => l_out_bill_equip_Qty1_arr
1308 ,p_out_bill_labor_Qty2_tab => l_out_bill_labor_Qty2_arr
1309 ,p_out_bill_equip_Qty2_tab => l_out_bill_equip_Qty2_arr
1310 ,p_out_bill_labor_Qty3_tab => l_out_bill_labor_Qty3_arr
1311 ,p_out_bill_equip_Qty3_tab => l_out_bill_equip_Qty3_arr
1312 ,p_out_bill_amt1_tab => l_out_bill_amt1_arr
1313 ,p_out_bill_amt2_tab => l_out_bill_amt2_arr
1314 ,p_out_bill_labor_amt1_tab => l_out_bill_labor_amt1_arr
1315 ,p_out_bill_labor_amt2_tab => l_out_bill_labor_amt2_arr
1316 ,p_out_equip_amt1_tab => l_out_equip_amt1_arr
1317 ,p_out_equip_amt2_tab => l_out_equip_amt2_arr
1318 ,p_out_equip_amt3_tab => l_out_equip_amt3_arr
1319 ,p_out_equip_amt4_tab => l_out_equip_amt4_arr
1320 ,p_out_equip_amt5_tab => l_out_equip_amt5_arr
1321 ,p_out_equip_amt6_tab => l_out_equip_amt6_arr
1322 ,p_out_equip_amt7_tab => l_out_equip_amt7_arr
1323 ,p_out_equip_amt8_tab => l_out_equip_amt8_arr
1324 ,p_out_capit_amt1_tab => l_out_capit_amt1_arr
1325 ,p_out_capit_amt2_tab => l_out_capit_amt2_arr
1326 ,p_out_labor_amt1_tab => l_out_labor_amt1_arr
1327 ,p_out_labor_amt2_tab => l_out_labor_amt2_arr
1328 ,p_out_labor_amt3_tab => l_out_labor_amt3_arr
1329 ,p_out_labor_amt4_tab => l_out_labor_amt4_arr
1330 ,p_out_labor_amt5_tab => l_out_labor_amt5_arr
1331 ,p_out_labor_amt6_tab => l_out_labor_amt6_arr
1332 ,p_out_labor_amt7_tab => l_out_labor_amt7_arr
1333 ,p_out_labor_amt8_tab => l_out_labor_amt8_arr
1334 ,x_return_status => l_return_status
1335 ,x_msg_count => l_msg_count
1336 ,x_msg_data => l_msg_data );
1337
1338
1339
1340
1341 ppr_log(' after api l_out_task_arr.count ... '||l_out_task_arr.count,0);
1342
1343
1344 ppr_log(' AFTER ROLLUP COUNT '||l_out_equip_amt1_arr.count,0);
1345
1346 merge_into_fact (
1347 p_calling_mode => p_calling_mode
1348 --,p_max_msg_count IN NUMBER default NULL
1349 ,p_fact_slice => p_fact_slice
1350 -- ,p_debug_mode IN VARCHAR2 default 'N'
1351 ,p_project_id => l_project_id
1352 ,p_budget_version_id => l_budget_version_id
1353 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
1354 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
1355 ,p_object_id => l_object_id
1356 ,p_currency_code => l_project_currency
1357 ,p_fact_task_arr => l_out_task_arr
1358 ,p_fact_task_index_arr => l_out_task_index_arr
1359 ,p_fact_amount1_arr => l_out_amount1_arr
1360 ,p_fact_amount2_arr => l_out_amount2_arr
1361 ,p_fact_amount3_arr => l_out_amount3_arr
1362 ,p_fact_amount4_arr => l_out_amount4_arr
1363 ,p_fact_amount5_arr => l_out_amount5_arr
1364 ,p_fact_amount6_arr => l_out_amount6_arr
1365 ,p_fact_amount7_arr => l_out_amount7_arr
1366 ,p_fact_amount8_arr => l_out_amount8_arr
1367 ,p_fact_qty1_arr => l_out_qty1_arr
1368 ,p_fact_qty2_arr => l_out_qty2_arr
1369 ,p_fact_qty3_arr => l_out_qty3_arr
1370 ,p_fact_labor_Qty1_arr => l_out_labor_qty1_arr
1371 ,p_fact_equip_Qty1_arr => l_out_equip_qty1_arr
1372 ,p_fact_labor_Qty2_arr => l_out_labor_qty2_arr
1373 ,p_fact_equip_Qty2_arr => l_out_equip_qty2_arr
1374 ,p_fact_labor_Qty3_arr => l_out_labor_qty3_arr
1375 ,p_fact_equip_Qty3_arr => l_out_equip_qty3_arr
1376 ,p_fact_bill_labor_Qty1_arr => l_out_bill_labor_Qty1_arr
1377 ,p_fact_bill_equip_Qty1_arr => l_out_bill_equip_Qty1_arr
1378 ,p_fact_bill_labor_Qty2_arr => l_out_bill_labor_Qty2_arr
1379 ,p_fact_bill_equip_Qty2_arr => l_out_bill_equip_Qty2_arr
1380 ,p_fact_bill_labor_Qty3_arr => l_out_bill_labor_Qty3_arr
1381 ,p_fact_bill_equip_Qty3_arr => l_out_bill_equip_Qty3_arr
1382 ,p_fact_bill_amt1_arr => l_out_bill_amt1_arr
1383 ,p_fact_bill_amt2_arr => l_out_bill_amt2_arr
1384 ,p_fact_bill_labor_amt1_arr => l_out_bill_labor_amt1_arr
1385 ,p_fact_bill_labor_amt2_arr => l_out_bill_labor_amt2_arr
1386 ,p_fact_equip_amt1_arr => l_out_equip_amt1_arr
1387 ,p_fact_equip_amt2_arr => l_out_equip_amt2_arr
1388 ,p_fact_equip_amt3_arr => l_out_equip_amt3_arr
1389 ,p_fact_equip_amt4_arr => l_out_equip_amt4_arr
1390 ,p_fact_equip_amt5_arr => l_out_equip_amt5_arr
1391 ,p_fact_equip_amt6_arr => l_out_equip_amt6_arr
1392 ,p_fact_equip_amt7_arr => l_out_equip_amt7_arr
1393 ,p_fact_equip_amt8_arr => l_out_equip_amt8_arr
1394 ,p_fact_capit_amt1_arr => l_out_capit_amt1_arr
1395 ,p_fact_capit_amt2_arr => l_out_capit_amt2_arr
1396 ,p_fact_labor_amt1_arr => l_out_labor_amt1_arr
1397 ,p_fact_labor_amt2_arr => l_out_labor_amt2_arr
1398 ,p_fact_labor_amt3_arr => l_out_labor_amt3_arr
1399 ,p_fact_labor_amt4_arr => l_out_labor_amt4_arr
1400 ,p_fact_labor_amt5_arr => l_out_labor_amt5_arr
1401 ,p_fact_labor_amt6_arr => l_out_labor_amt6_arr
1402 ,p_fact_labor_amt7_arr => l_out_labor_amt7_arr
1403 ,p_fact_labor_amt8_arr => l_out_labor_amt8_arr
1404 ,x_return_status => l_return_status
1405 ,x_msg_count => l_msg_count
1406 ,x_msg_data => l_msg_data
1407 );
1408
1409
1410 -- RAW_COST, BRDN_COST, REVENUE
1411 -- l_out_amount1_arr(i),l_out_amount2_arr(i),l_out_amount3_arr(i)
1412 -- LABOR_HRS, LABOR_REVENUE, EQUIPMENT_HOURS,
1413 -- l_out_labor_qty1_arr(i), 0, l_out_equip_qty1_arr(i),
1414 -- , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_BRDN_COST, ACT_RAW_COST
1415 --- ,l_out_labor_qty1_arr(i),l_out_equip_qty2_arr(i),l_out_amount5_arr(i),l_out_amount4_arr(i)
1416 ---
1417 ---, ACT_REVENUE, ETC_LABOR_HRS, ETC_EQUIP_HRS,
1418 --- ,l_out_amount6_arr(i),l_out_labor_qty3_arr(i),l_out_equip_qty3_arr(i)
1419 ----
1420 ----ETC_BRDN_COST, ETC_RAW_COST,
1421 ---l_out_amount8_arr(i), l_out_amount7_arr(i)
1422
1423
1424 -- END Project Currency Process
1425
1426
1427 If ( l_project_currency <> l_functional_currency ) then /* Project_currency <> functional_currency */
1428
1429 ppr_log(' SECOND RUN......... ',0);
1430
1431
1432 l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
1433 l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
1434
1435 l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
1436
1437 l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
1438 l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
1439 l_out_amount7_arr.delete;l_out_amount8_arr.delete;
1440
1441
1442 l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
1443 l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
1444 l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
1445 l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
1446 l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
1447 l_out_labor_amt2_arr.delete;
1448
1449 l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
1450 l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
1451
1452
1453 l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
1454 l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
1455 l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
1456
1457 rollup_amounts (
1458 p_billable_flag_tab => l_billable_flag_arr
1459 ,p_object_id_tab => l_task_arr
1460 ,p_wbs_level_tab => l_task_level_arr
1461 ,p_connect_path_tab => l_connect_path_arr
1462 ,p_leaf_node_tab => l_leaf_node_arr
1463 ,p_resource_class_tab => l_res_class_arr
1464 ,p_amount1_tab => l_func_amount1_arr
1465 ,p_amount2_tab => l_func_amount2_arr
1466 ,p_amount3_tab => l_func_amount3_arr
1467 ,p_amount4_tab => l_func_amount4_arr
1468 ,p_amount5_tab => l_func_amount5_arr
1469 ,p_amount6_tab => l_func_amount6_arr
1470 ,p_amount7_tab => l_func_amount7_arr
1471 ,p_amount8_tab => l_func_amount8_arr
1472 ,p_qty1_tab => l_qty1_arr
1473 ,p_qty2_tab => l_qty2_arr
1474 ,p_qty3_tab => l_qty3_arr
1475 ,p_out_object_index_tab => l_out_task_index_arr
1476 ,p_out_object_id_tab => l_out_task_arr
1477 ,p_out_amount1_tab => l_out_amount1_arr
1478 ,p_out_amount2_tab => l_out_amount2_arr
1479 ,p_out_amount3_tab => l_out_amount3_arr
1480 ,p_out_amount4_tab => l_out_amount4_arr
1481 ,p_out_amount5_tab => l_out_amount5_arr
1482 ,p_out_amount6_tab => l_out_amount6_arr
1483 ,p_out_amount7_tab => l_out_amount7_arr
1484 ,p_out_amount8_tab => l_out_amount8_arr
1485 ,p_out_qty1_tab => l_out_qty1_arr
1486 ,p_out_qty2_tab => l_out_qty2_arr
1487 ,p_out_qty3_tab => l_out_qty3_arr
1488 ,p_out_labor_Qty1_tab => l_out_labor_qty1_arr
1489 ,p_out_equip_Qty1_tab => l_out_equip_qty1_arr
1490 ,p_out_labor_Qty2_tab => l_out_labor_qty2_arr
1491 ,p_out_equip_Qty2_tab => l_out_equip_qty2_arr
1492 ,p_out_labor_Qty3_tab => l_out_labor_qty3_arr
1493 ,p_out_equip_Qty3_tab => l_out_equip_qty3_arr
1494 ,p_out_bill_labor_Qty1_tab => l_out_bill_labor_Qty1_arr
1495 ,p_out_bill_equip_Qty1_tab => l_out_bill_equip_Qty1_arr
1496 ,p_out_bill_labor_Qty2_tab => l_out_bill_labor_Qty2_arr
1497 ,p_out_bill_equip_Qty2_tab => l_out_bill_equip_Qty2_arr
1498 ,p_out_bill_labor_Qty3_tab => l_out_bill_labor_Qty3_arr
1499 ,p_out_bill_equip_Qty3_tab => l_out_bill_equip_Qty3_arr
1500 ,p_out_bill_amt1_tab => l_out_bill_amt1_arr
1501 ,p_out_bill_amt2_tab => l_out_bill_amt2_arr
1502 ,p_out_bill_labor_amt1_tab => l_out_bill_labor_amt1_arr
1503 ,p_out_bill_labor_amt2_tab => l_out_bill_labor_amt2_arr
1504 ,p_out_equip_amt1_tab => l_out_equip_amt1_arr
1505 ,p_out_equip_amt2_tab => l_out_equip_amt2_arr
1506 ,p_out_equip_amt3_tab => l_out_equip_amt3_arr
1507 ,p_out_equip_amt4_tab => l_out_equip_amt4_arr
1508 ,p_out_equip_amt5_tab => l_out_equip_amt5_arr
1509 ,p_out_equip_amt6_tab => l_out_equip_amt6_arr
1510 ,p_out_equip_amt7_tab => l_out_equip_amt7_arr
1511 ,p_out_equip_amt8_tab => l_out_equip_amt8_arr
1512 ,p_out_capit_amt1_tab => l_out_capit_amt1_arr
1513 ,p_out_capit_amt2_tab => l_out_capit_amt2_arr
1514 ,p_out_labor_amt1_tab => l_out_labor_amt1_arr
1515 ,p_out_labor_amt2_tab => l_out_labor_amt2_arr
1516 ,p_out_labor_amt3_tab => l_out_labor_amt3_arr
1517 ,p_out_labor_amt4_tab => l_out_labor_amt4_arr
1518 ,p_out_labor_amt5_tab => l_out_labor_amt5_arr
1519 ,p_out_labor_amt6_tab => l_out_labor_amt6_arr
1520 ,p_out_labor_amt7_tab => l_out_labor_amt7_arr
1521 ,p_out_labor_amt8_tab => l_out_labor_amt8_arr
1522 ,x_return_status => l_return_status
1523 ,x_msg_count => l_msg_count
1524 ,x_msg_data => l_msg_data );
1525
1526
1527
1528 ppr_log(' AFTER SECOND RUN......... ',0);
1529
1530
1531 merge_into_fact (
1532 p_calling_mode => p_calling_mode
1533 --,p_max_msg_count IN NUMBER default NULL
1534 ,p_fact_slice => p_fact_slice
1535 -- ,p_debug_mode IN VARCHAR2 default 'N'
1536 ,p_project_id => l_project_id
1537 ,p_budget_version_id => l_budget_version_id
1538 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
1539 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
1540 ,p_object_id => l_object_id
1541 ,p_currency_code => l_project_currency
1542 ,p_fact_task_arr => l_out_task_arr
1543 ,p_fact_task_index_arr => l_out_task_index_arr
1544 ,p_fact_amount1_arr => l_out_amount1_arr
1545 ,p_fact_amount2_arr => l_out_amount2_arr
1546 ,p_fact_amount3_arr => l_out_amount3_arr
1547 ,p_fact_amount4_arr => l_out_amount4_arr
1548 ,p_fact_amount5_arr => l_out_amount5_arr
1549 ,p_fact_amount6_arr => l_out_amount6_arr
1550 ,p_fact_amount7_arr => l_out_amount7_arr
1551 ,p_fact_amount8_arr => l_out_amount8_arr
1552 ,p_fact_qty1_arr => l_out_qty1_arr
1553 ,p_fact_qty2_arr => l_out_qty2_arr
1554 ,p_fact_qty3_arr => l_out_qty3_arr
1555 ,p_fact_labor_Qty1_arr => l_out_labor_qty1_arr
1556 ,p_fact_equip_Qty1_arr => l_out_equip_qty1_arr
1557 ,p_fact_labor_Qty2_arr => l_out_labor_qty2_arr
1558 ,p_fact_equip_Qty2_arr => l_out_equip_qty2_arr
1559 ,p_fact_labor_Qty3_arr => l_out_labor_qty3_arr
1560 ,p_fact_equip_Qty3_arr => l_out_equip_qty3_arr
1561 ,p_fact_bill_labor_Qty1_arr => l_out_bill_labor_Qty1_arr
1562 ,p_fact_bill_equip_Qty1_arr => l_out_bill_equip_Qty1_arr
1566 ,p_fact_bill_equip_Qty3_arr => l_out_bill_equip_Qty3_arr
1563 ,p_fact_bill_labor_Qty2_arr => l_out_bill_labor_Qty2_arr
1564 ,p_fact_bill_equip_Qty2_arr => l_out_bill_equip_Qty2_arr
1565 ,p_fact_bill_labor_Qty3_arr => l_out_bill_labor_Qty3_arr
1567 ,p_fact_bill_amt1_arr => l_out_bill_amt1_arr
1568 ,p_fact_bill_amt2_arr => l_out_bill_amt2_arr
1569 ,p_fact_bill_labor_amt1_arr => l_out_bill_labor_amt1_arr
1570 ,p_fact_bill_labor_amt2_arr => l_out_bill_labor_amt2_arr
1571 ,p_fact_equip_amt1_arr => l_out_equip_amt1_arr
1572 ,p_fact_equip_amt2_arr => l_out_equip_amt2_arr
1573 ,p_fact_equip_amt3_arr => l_out_equip_amt3_arr
1574 ,p_fact_equip_amt4_arr => l_out_equip_amt4_arr
1575 ,p_fact_equip_amt5_arr => l_out_equip_amt5_arr
1576 ,p_fact_equip_amt6_arr => l_out_equip_amt6_arr
1577 ,p_fact_equip_amt7_arr => l_out_equip_amt7_arr
1578 ,p_fact_equip_amt8_arr => l_out_equip_amt8_arr
1579 ,p_fact_capit_amt1_arr => l_out_capit_amt1_arr
1580 ,p_fact_capit_amt2_arr => l_out_capit_amt2_arr
1581 ,p_fact_labor_amt1_arr => l_out_labor_amt1_arr
1582 ,p_fact_labor_amt2_arr => l_out_labor_amt2_arr
1583 ,p_fact_labor_amt3_arr => l_out_labor_amt3_arr
1584 ,p_fact_labor_amt4_arr => l_out_labor_amt4_arr
1585 ,p_fact_labor_amt5_arr => l_out_labor_amt5_arr
1586 ,p_fact_labor_amt6_arr => l_out_labor_amt6_arr
1587 ,p_fact_labor_amt7_arr => l_out_labor_amt7_arr
1588 ,p_fact_labor_amt8_arr => l_out_labor_amt8_arr
1589 ,x_return_status => l_return_status
1590 ,x_msg_count => l_msg_count
1591 ,x_msg_data => l_msg_data
1592 );
1593
1594
1595
1596
1597 end if; /* Project_currency <> functional_currency */
1598
1599
1600 commit;
1601
1602 end plan_rollup ;
1603
1604
1605 procedure rollup_amounts (
1606 p_api_version IN NUMBER default 1.0
1607 ,p_commit IN VARCHAR2 default 'N'
1608 ,p_calling_mode IN VARCHAR2 default 'CREATE'
1609 ,p_debug_mode IN VARCHAR2 default 'N'
1610 ,p_max_msg_count IN NUMBER default NULL
1611 ,p_billable_flag_tab IN PA_PLSQL_DATATYPES.Char1TabTyp
1612 ,p_resource_class_tab IN PA_PLSQL_DATATYPES.Char30TabTyp
1613 ,p_object_id_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1614 ,p_wbs_level_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1615 ,p_connect_path_tab IN PA_PLSQL_DATATYPES.Char240TabTyp
1616 ,p_leaf_node_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1617 ,p_amount1_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1618 ,p_amount2_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1619 ,p_amount3_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1620 ,p_amount4_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1621 ,p_amount5_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1622 ,p_amount6_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1623 ,p_amount7_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1624 ,p_amount8_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1625 ,p_qty1_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1626 ,p_qty2_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1627 ,p_qty3_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1628 ,p_out_object_index_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1629 ,p_out_object_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1630 ,p_out_amount1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1631 ,p_out_amount2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1632 ,p_out_amount3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1633 ,p_out_amount4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1634 ,p_out_amount5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1635 ,p_out_amount6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1636 ,p_out_amount7_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1637 ,p_out_amount8_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1638 ,p_out_qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1639 ,p_out_qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1640 ,p_out_qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1641 ,p_out_labor_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1642 ,p_out_equip_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1643 ,p_out_labor_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1644 ,p_out_equip_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1645 ,p_out_labor_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1646 ,p_out_equip_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1647 ,p_out_bill_labor_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1648 ,p_out_bill_equip_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1649 ,p_out_bill_labor_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1650 ,p_out_bill_equip_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1651 ,p_out_bill_labor_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1652 ,p_out_bill_equip_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1653 ,p_out_bill_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1654 ,p_out_bill_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1655 ,p_out_bill_labor_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1656 ,p_out_bill_labor_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1657 ,p_out_equip_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1658 ,p_out_equip_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1659 ,p_out_equip_amt3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1660 ,p_out_equip_amt4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1661 ,p_out_equip_amt5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1662 ,p_out_equip_amt6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1663 ,p_out_equip_amt7_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1667 ,p_out_labor_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1664 ,p_out_equip_amt8_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1665 ,p_out_capit_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1666 ,p_out_capit_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1668 ,p_out_labor_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1669 ,p_out_labor_amt3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1670 ,p_out_labor_amt4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1671 ,p_out_labor_amt5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1672 ,p_out_labor_amt6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1673 ,p_out_labor_amt7_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1674 ,p_out_labor_amt8_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1675 ,x_return_status OUT NOCOPY VARCHAR2
1676 ,x_msg_count OUT NOCOPY NUMBER
1677 ,x_msg_data OUT NOCOPY VARCHAR2
1678 ) as
1679
1680 t_str varchar2(20);
1681
1682 t_id number;
1683 l_level number;
1684 p_path varchar2(240);
1685
1686
1687 l_parent_object_index PA_PLSQL_DATATYPES.NumTabTyp;
1688 l_parent_counter number ;
1689
1690 l_rollup_amount1 PA_PLSQL_DATATYPES.NumTabTyp;
1691 l_rollup_amount2 PA_PLSQL_DATATYPES.NumTabTyp;
1692 l_rollup_amount3 PA_PLSQL_DATATYPES.NumTabTyp;
1693
1694 l_rollup_amount4 PA_PLSQL_DATATYPES.NumTabTyp;
1695 l_rollup_amount5 PA_PLSQL_DATATYPES.NumTabTyp;
1696 l_rollup_amount6 PA_PLSQL_DATATYPES.NumTabTyp;
1697 l_rollup_amount7 PA_PLSQL_DATATYPES.NumTabTyp;
1698 l_rollup_amount8 PA_PLSQL_DATATYPES.NumTabTyp;
1699
1700 l_rollup_qty1 PA_PLSQL_DATATYPES.NumTabTyp;
1701 l_rollup_qty2 PA_PLSQL_DATATYPES.NumTabTyp;
1702 l_rollup_qty3 PA_PLSQL_DATATYPES.NumTabTyp;
1703
1704 l_rollup_labor_qty1 PA_PLSQL_DATATYPES.NumTabTyp;
1705 l_rollup_labor_qty2 PA_PLSQL_DATATYPES.NumTabTyp;
1706 l_rollup_labor_qty3 PA_PLSQL_DATATYPES.NumTabTyp;
1707
1708 l_rollup_equip_qty1 PA_PLSQL_DATATYPES.NumTabTyp;
1709 l_rollup_equip_qty2 PA_PLSQL_DATATYPES.NumTabTyp;
1710 l_rollup_equip_qty3 PA_PLSQL_DATATYPES.NumTabTyp;
1711
1712 l_rollup_bill_labor_Qty1 PA_PLSQL_DATATYPES.NumTabTyp;
1713 l_rollup_bill_equip_Qty1 PA_PLSQL_DATATYPES.NumTabTyp;
1714 l_rollup_bill_labor_Qty2 PA_PLSQL_DATATYPES.NumTabTyp;
1715 l_rollup_bill_equip_Qty2 PA_PLSQL_DATATYPES.NumTabTyp;
1716 l_rollup_bill_labor_Qty3 PA_PLSQL_DATATYPES.NumTabTyp;
1717 l_rollup_bill_equip_Qty3 PA_PLSQL_DATATYPES.NumTabTyp;
1718 l_rollup_bill_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
1719 l_rollup_bill_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
1720 l_rollup_bill_labor_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
1721 l_rollup_bill_labor_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
1722 l_rollup_equip_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
1723 l_rollup_equip_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
1724 l_rollup_equip_amt3 PA_PLSQL_DATATYPES.NumTabTyp;
1725 l_rollup_equip_amt4 PA_PLSQL_DATATYPES.NumTabTyp;
1726 l_rollup_equip_amt5 PA_PLSQL_DATATYPES.NumTabTyp;
1727 l_rollup_equip_amt6 PA_PLSQL_DATATYPES.NumTabTyp;
1728 l_rollup_equip_amt7 PA_PLSQL_DATATYPES.NumTabTyp;
1729 l_rollup_equip_amt8 PA_PLSQL_DATATYPES.NumTabTyp;
1730 l_rollup_capit_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
1731 l_rollup_capit_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
1732 l_rollup_labor_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
1733 l_rollup_labor_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
1734 l_rollup_labor_amt3 PA_PLSQL_DATATYPES.NumTabTyp;
1735 l_rollup_labor_amt4 PA_PLSQL_DATATYPES.NumTabTyp;
1736 l_rollup_labor_amt5 PA_PLSQL_DATATYPES.NumTabTyp;
1737 l_rollup_labor_amt6 PA_PLSQL_DATATYPES.NumTabTyp;
1738 l_rollup_labor_amt7 PA_PLSQL_DATATYPES.NumTabTyp;
1739 l_rollup_labor_amt8 PA_PLSQL_DATATYPES.NumTabTyp;
1740
1741
1742 begin
1743
1744 null;
1745
1746 l_parent_counter := 0;
1747
1748 ppr_log(' ROLLUP_AMOUNT : task_id count ... '||p_object_id_tab.count,0);
1749
1750
1751 FOR i IN 1..p_object_id_tab.count LOOP
1752
1753 l_level := p_wbs_level_tab(i);
1754 p_path := p_connect_path_tab(i);
1755
1756 ppr_log(' ROLLUP_AMOUNT : source task_id ... '||p_object_id_tab(i)||' path '||p_path||' level '||l_level||' .. amount1 .. '||p_amount1_tab(i),1);
1757
1758 FOR i_level IN 1..l_level LOOP
1759
1760 if ( instr(p_path , '/', 1, i_level) = 0 ) then
1761 t_str := null;
1762 else
1763 if ( instr(p_path , '/', 1, i_level+1) = 0 ) then
1764 t_str := substr(p_path , instr(p_path , '/', 1, i_level)+1, length(p_path ) ) ;
1765 else
1766 t_str :=substr(p_path , instr(p_path , '/', 1, i_level)+1,
1767 instr(p_path , '/', 1, i_level+1) - instr(p_path , '/', 1, i_level)- 1 ) ;
1768 end if;
1769 end if;
1770
1771 t_id := to_number(t_str);
1772
1773 ppr_log(' ROLLUP_AMOUNT : rollup_task task_id ... '||t_id ,1);
1774 ppr_log(' ROLLUP_AMOUNT : p_resource_class_tab(i) ... '||p_resource_class_tab(i)||' p_billable_flag_tab(i) '||p_billable_flag_tab(i) ,1);
1775
1776 if (l_rollup_amount1.exists(t_id)) then
1777
1778 l_rollup_amount1(t_id) := l_rollup_amount1(t_id) + p_amount1_tab(i);
1779 l_rollup_amount2(t_id) := l_rollup_amount2(t_id) + p_amount2_tab(i);
1780 l_rollup_amount3(t_id) := l_rollup_amount3(t_id) + p_amount3_tab(i);
1781
1782 l_rollup_amount4(t_id) := l_rollup_amount4(t_id) + p_amount4_tab(i);
1783 l_rollup_amount5(t_id) := l_rollup_amount5(t_id) + p_amount5_tab(i);
1787 l_rollup_amount8(t_id) := l_rollup_amount8(t_id) + p_amount8_tab(i);
1784 l_rollup_amount6(t_id) := l_rollup_amount6(t_id) + p_amount6_tab(i);
1785
1786 l_rollup_amount7(t_id) := l_rollup_amount7(t_id) + p_amount7_tab(i);
1788
1789 l_rollup_qty1(t_id) := l_rollup_qty1(t_id) + p_qty1_tab(i);
1790 l_rollup_qty2(t_id) := l_rollup_qty2(t_id) + p_qty2_tab(i);
1791 l_rollup_qty3(t_id) := l_rollup_qty3(t_id) + p_qty3_tab(i);
1792
1793 ppr_log(' ROLLUP_AMOUNT : test0 ',1);
1794
1795 if ( p_resource_class_tab(i) = 'PEOPLE') then
1796
1797 l_rollup_labor_qty1(t_id) := l_rollup_labor_qty1(t_id) + p_qty1_tab(i);
1798 l_rollup_labor_qty2(t_id) := l_rollup_labor_qty2(t_id) + p_qty2_tab(i);
1799 l_rollup_labor_qty3(t_id) := l_rollup_labor_qty3(t_id) + p_qty3_tab(i);
1800
1801 ppr_log(' ROLLUP_AMOUNT : test1 ',1);
1802
1803 l_rollup_labor_amt1(t_id) := l_rollup_labor_amt1(t_id) + p_amount1_tab(i);
1804 l_rollup_labor_amt2(t_id) := l_rollup_labor_amt2(t_id) + p_amount2_tab(i);
1805 l_rollup_labor_amt3(t_id) := l_rollup_labor_amt3(t_id) + p_amount3_tab(i);
1806 l_rollup_labor_amt4(t_id) := l_rollup_labor_amt4(t_id) + p_amount4_tab(i);
1807 l_rollup_labor_amt5(t_id) := l_rollup_labor_amt5(t_id) + p_amount5_tab(i);
1808 l_rollup_labor_amt6(t_id) := l_rollup_labor_amt6(t_id) + p_amount6_tab(i);
1809 l_rollup_labor_amt7(t_id) := l_rollup_labor_amt7(t_id) + p_amount7_tab(i);
1810 l_rollup_labor_amt8(t_id) := l_rollup_labor_amt8(t_id) + p_amount8_tab(i);
1811
1812 ppr_log(' ROLLUP_AMOUNT : test2 ',1);
1813
1814 if ( p_billable_flag_tab(i) = 'Y' ) then
1815
1816 l_rollup_bill_labor_Qty1(t_id) := l_rollup_bill_labor_Qty1(t_id) + p_qty1_tab(i);
1817 l_rollup_bill_labor_Qty2(t_id) := l_rollup_bill_labor_Qty2(t_id) + p_qty2_tab(i);
1818 l_rollup_bill_labor_Qty3(t_id) := l_rollup_bill_labor_Qty3(t_id) + p_qty3_tab(i);
1819
1820 ppr_log(' ROLLUP_AMOUNT : test3 ',1);
1821
1822 l_rollup_bill_labor_amt1(t_id) := l_rollup_bill_labor_amt1(t_id) + p_amount1_tab(i);
1823 l_rollup_bill_labor_amt2(t_id) := l_rollup_bill_labor_amt2(t_id) + p_amount2_tab(i);
1824
1825 end if;
1826
1827 ppr_log(' ROLLUP_AMOUNT : test4 ',1);
1828 end if;
1829
1830 if ( p_resource_class_tab(i) = 'EQUIPMENT') then
1831 l_rollup_equip_qty1(t_id) := l_rollup_equip_qty1(t_id) + p_qty1_tab(i);
1832 l_rollup_equip_qty2(t_id) := l_rollup_equip_qty2(t_id) + p_qty2_tab(i);
1833 l_rollup_equip_qty3(t_id) := l_rollup_equip_qty3(t_id) + p_qty3_tab(i);
1834
1835 l_rollup_equip_amt1(t_id) := l_rollup_equip_amt1(t_id) + p_amount1_tab(i);
1836 l_rollup_equip_amt2(t_id) := l_rollup_equip_amt2(t_id) + p_amount2_tab(i);
1837 l_rollup_equip_amt3(t_id) := l_rollup_equip_amt3(t_id) + p_amount3_tab(i);
1838 l_rollup_equip_amt4(t_id) := l_rollup_equip_amt4(t_id) + p_amount4_tab(i);
1839 l_rollup_equip_amt5(t_id) := l_rollup_equip_amt5(t_id) + p_amount5_tab(i);
1840 l_rollup_equip_amt6(t_id) := l_rollup_equip_amt6(t_id) + p_amount6_tab(i);
1841 l_rollup_equip_amt7(t_id) := l_rollup_equip_amt7(t_id) + p_amount7_tab(i);
1842 l_rollup_equip_amt8(t_id) := l_rollup_equip_amt8(t_id) + p_amount8_tab(i);
1843
1844 if ( p_billable_flag_tab(i) = 'Y' ) then
1845
1846 l_rollup_bill_equip_Qty1(t_id) := l_rollup_bill_equip_Qty1(t_id) + p_qty1_tab(i);
1847 l_rollup_bill_equip_Qty2(t_id) := l_rollup_bill_equip_Qty2(t_id) + p_qty2_tab(i);
1848 l_rollup_bill_equip_Qty3(t_id) := l_rollup_bill_equip_Qty3(t_id) + p_qty3_tab(i);
1849
1850 end if;
1851
1852 end if;
1853
1854
1855 if ( p_billable_flag_tab(i) = 'Y' ) then
1856
1857
1858 l_rollup_bill_amt1(t_id) := l_rollup_bill_amt1(t_id) + p_amount1_tab(i);
1859 l_rollup_bill_amt2(t_id) := l_rollup_bill_amt2(t_id) + p_amount2_tab(i);
1860
1861 l_rollup_capit_amt1(t_id) := l_rollup_capit_amt1(t_id) + p_amount1_tab(i);
1862 l_rollup_capit_amt2(t_id) := l_rollup_capit_amt2(t_id) + p_amount2_tab(i);
1863
1864
1865 end if;
1866
1867 else
1868
1869 ppr_log(' ROLLUP_AMOUNT : l_parent_counter ... '||l_parent_counter ,1);
1870
1871 l_parent_counter := l_parent_counter + 1;
1872 l_parent_object_index(l_parent_counter) := t_id;
1873
1874 l_rollup_amount1(t_id) := p_amount1_tab(i);
1875 l_rollup_amount2(t_id) := p_amount2_tab(i);
1876 l_rollup_amount3(t_id) := p_amount3_tab(i);
1877
1878 l_rollup_amount4(t_id) := p_amount4_tab(i);
1879 l_rollup_amount5(t_id) := p_amount5_tab(i);
1880 l_rollup_amount6(t_id) := p_amount6_tab(i);
1881
1882 l_rollup_amount7(t_id) := p_amount7_tab(i);
1883 l_rollup_amount8(t_id) := p_amount8_tab(i);
1884
1885 l_rollup_qty1(t_id) := p_qty1_tab(i);
1886 l_rollup_qty2(t_id) := p_qty2_tab(i);
1887 l_rollup_qty3(t_id) := p_qty3_tab(i);
1888
1889 if ( p_resource_class_tab(i) = 'PEOPLE') then
1890 l_rollup_labor_qty1(t_id) := p_qty1_tab(i);
1891 l_rollup_labor_qty2(t_id) := p_qty2_tab(i);
1892 l_rollup_labor_qty3(t_id) := p_qty3_tab(i);
1893
1894 l_rollup_labor_amt1(t_id) := p_amount1_tab(i);
1895 l_rollup_labor_amt2(t_id) := p_amount2_tab(i);
1896 l_rollup_labor_amt3(t_id) := p_amount3_tab(i);
1897 l_rollup_labor_amt4(t_id) := p_amount4_tab(i);
1898 l_rollup_labor_amt5(t_id) := p_amount5_tab(i);
1899 l_rollup_labor_amt6(t_id) := p_amount6_tab(i);
1900 l_rollup_labor_amt7(t_id) := p_amount7_tab(i);
1901 l_rollup_labor_amt8(t_id) := p_amount8_tab(i);
1902
1903
1904 if ( p_billable_flag_tab(i) = 'Y' ) then
1905
1906 l_rollup_bill_labor_Qty1(t_id) := p_qty1_tab(i);
1910 l_rollup_bill_labor_amt2(t_id) := p_amount2_tab(i);
1907 l_rollup_bill_labor_Qty2(t_id) := p_qty2_tab(i);
1908 l_rollup_bill_labor_Qty3(t_id) := p_qty3_tab(i);
1909 l_rollup_bill_labor_amt1(t_id) := p_amount1_tab(i);
1911
1912 else
1913
1914 l_rollup_bill_labor_Qty1(t_id) := 0;
1915 l_rollup_bill_labor_Qty2(t_id) := 0;
1916 l_rollup_bill_labor_Qty3(t_id) := 0;
1917 l_rollup_bill_labor_amt1(t_id) := 0;
1918 l_rollup_bill_labor_amt2(t_id) := 0;
1919
1920 end if;
1921
1922 else
1923 l_rollup_labor_qty1(t_id) := 0;
1924 l_rollup_labor_qty2(t_id) := 0;
1925 l_rollup_labor_qty3(t_id) := 0;
1926
1927 l_rollup_bill_labor_Qty1(t_id) := 0;
1928 l_rollup_bill_labor_Qty2(t_id) := 0;
1929 l_rollup_bill_labor_Qty3(t_id) := 0;
1930 l_rollup_bill_labor_amt1(t_id) := 0;
1931 l_rollup_bill_labor_amt2(t_id) := 0;
1932
1933 l_rollup_labor_amt1(t_id) := 0;
1934 l_rollup_labor_amt2(t_id) := 0;
1935 l_rollup_labor_amt3(t_id) := 0;
1936 l_rollup_labor_amt4(t_id) := 0;
1937 l_rollup_labor_amt5(t_id) := 0;
1938 l_rollup_labor_amt6(t_id) := 0;
1939 l_rollup_labor_amt7(t_id) := 0;
1940 l_rollup_labor_amt8(t_id) := 0;
1941
1942
1943
1944 end if;
1945
1946 if ( p_resource_class_tab(i) = 'EQUIPMENT') then
1947 l_rollup_equip_qty1(t_id) := p_qty1_tab(i);
1948 l_rollup_equip_qty2(t_id) := p_qty2_tab(i);
1949 l_rollup_equip_qty3(t_id) := p_qty3_tab(i);
1950
1951 l_rollup_equip_amt1(t_id) := p_amount1_tab(i);
1952 l_rollup_equip_amt2(t_id) := p_amount2_tab(i);
1953 l_rollup_equip_amt3(t_id) := p_amount3_tab(i);
1954 l_rollup_equip_amt4(t_id) := p_amount4_tab(i);
1955 l_rollup_equip_amt5(t_id) := p_amount5_tab(i);
1956 l_rollup_equip_amt6(t_id) := p_amount6_tab(i);
1957 l_rollup_equip_amt7(t_id) := p_amount7_tab(i);
1958 l_rollup_equip_amt8(t_id) := p_amount8_tab(i);
1959
1960 if ( p_billable_flag_tab(i) = 'Y' ) then
1961
1962 l_rollup_bill_equip_Qty1(t_id) := p_qty1_tab(i);
1963 l_rollup_bill_equip_Qty2(t_id) := p_qty2_tab(i);
1964 l_rollup_bill_equip_Qty3(t_id) := p_qty3_tab(i);
1965
1966 else
1967 l_rollup_bill_equip_Qty1(t_id) := 0;
1968 l_rollup_bill_equip_Qty2(t_id) := 0;
1969 l_rollup_bill_equip_Qty3(t_id) := 0;
1970
1971 end if;
1972
1973 else
1974 l_rollup_equip_qty1(t_id) := 0;
1975 l_rollup_equip_qty2(t_id) := 0;
1976 l_rollup_equip_qty3(t_id) := 0;
1977
1978 l_rollup_bill_equip_Qty1(t_id) := 0;
1979 l_rollup_bill_equip_Qty2(t_id) := 0;
1980 l_rollup_bill_equip_Qty3(t_id) := 0;
1981
1982 l_rollup_equip_amt1(t_id) := 0;
1983 l_rollup_equip_amt2(t_id) := 0;
1984 l_rollup_equip_amt3(t_id) := 0;
1985 l_rollup_equip_amt4(t_id) := 0;
1986 l_rollup_equip_amt5(t_id) := 0;
1987 l_rollup_equip_amt6(t_id) := 0;
1988 l_rollup_equip_amt7(t_id) := 0;
1989 l_rollup_equip_amt8(t_id) := 0;
1990
1991
1992 end if;
1993
1994 if ( p_billable_flag_tab(i) = 'Y' ) then
1995
1996
1997 l_rollup_bill_amt1(t_id) := p_amount1_tab(i);
1998 l_rollup_bill_amt2(t_id) := p_amount2_tab(i);
1999
2000 l_rollup_capit_amt1(t_id) := p_amount1_tab(i);
2001 l_rollup_capit_amt2(t_id) := p_amount2_tab(i);
2002 else
2003
2004
2005 l_rollup_bill_amt1(t_id) := 0;
2006 l_rollup_bill_amt2(t_id) := 0;
2007
2008 l_rollup_capit_amt1(t_id) := 0;
2009 l_rollup_capit_amt2(t_id) := 0;
2010
2011 end if;
2012
2013 end if;
2014
2015 ppr_log('ROLLUP AMOUNT : LOOP END roll task_id ... '||t_id||' l_rollup_bill_amt1(t_id) '|| l_rollup_bill_amt1(t_id),1);
2016
2017
2018 END LOOP; -- l_level loop
2019
2020 END LOOP; -- p_object_id_tab loop
2021
2022
2023 ppr_log('ROLLUP AMOUNT : OUT VARIABLE l_parent_counter '||l_parent_counter,1);
2024
2025
2026 For k IN 1..l_parent_counter LOOP
2027
2028 t_id := l_parent_object_index(k);
2029
2030 p_out_object_id_tab(k) := t_id;
2031 p_out_amount1_tab(k) := l_rollup_amount1(t_id);
2032 p_out_amount2_tab(k) := l_rollup_amount2(t_id);
2033 p_out_amount3_tab(k) := l_rollup_amount3(t_id);
2034
2035 p_out_amount4_tab(k) := l_rollup_amount4(t_id);
2036 p_out_amount5_tab(k) := l_rollup_amount5(t_id);
2037 p_out_amount6_tab(k) := l_rollup_amount6(t_id);
2038
2039 p_out_amount7_tab(k) := l_rollup_amount7(t_id);
2040 p_out_amount8_tab(k) := l_rollup_amount8(t_id);
2041
2042 p_out_qty1_tab(k) := l_rollup_qty1(t_id);
2043 p_out_qty2_tab(k) := l_rollup_qty2(t_id);
2044 p_out_qty3_tab(k) := l_rollup_qty3(t_id);
2045
2046 p_out_labor_qty1_tab(k) := l_rollup_labor_qty1(t_id);
2047 p_out_labor_qty2_tab(k) := l_rollup_labor_qty2(t_id);
2048 p_out_labor_qty3_tab(k) := l_rollup_labor_qty3(t_id);
2049
2050 p_out_equip_qty1_tab(k) := l_rollup_equip_qty1(t_id);
2051 p_out_equip_qty2_tab(k) := l_rollup_equip_qty2(t_id);
2052 p_out_equip_qty3_tab(k) := l_rollup_equip_qty3(t_id);
2053
2054 p_out_bill_labor_Qty1_tab(k) := l_rollup_bill_labor_Qty1(t_id);
2055 p_out_bill_equip_Qty1_tab(k) := l_rollup_bill_equip_Qty1(t_id);
2056 p_out_bill_labor_Qty2_tab(k) := l_rollup_bill_labor_Qty2(t_id);
2057 p_out_bill_equip_Qty2_tab(k) := l_rollup_bill_equip_Qty2(t_id);
2058 p_out_bill_labor_Qty3_tab(k) := l_rollup_bill_labor_Qty3(t_id);
2059 p_out_bill_equip_Qty3_tab(k) := l_rollup_bill_equip_Qty3(t_id);
2060 p_out_bill_amt1_tab(k) := l_rollup_bill_amt1(t_id);
2061 p_out_bill_amt2_tab(k) := l_rollup_bill_amt2(t_id);
2062 p_out_bill_labor_amt1_tab(k) := l_rollup_bill_labor_amt1(t_id);
2063 p_out_bill_labor_amt2_tab(k) := l_rollup_bill_labor_amt2(t_id);
2064 p_out_equip_amt1_tab(k) := l_rollup_equip_amt1(t_id);
2065 p_out_equip_amt2_tab(k) := l_rollup_equip_amt2(t_id);
2066 p_out_equip_amt3_tab(k) := l_rollup_equip_amt3(t_id);
2067 p_out_equip_amt4_tab(k) := l_rollup_equip_amt4(t_id);
2068 p_out_equip_amt5_tab(k) := l_rollup_equip_amt5(t_id);
2069 p_out_equip_amt6_tab(k) := l_rollup_equip_amt6(t_id);
2070 p_out_equip_amt7_tab(k) := l_rollup_equip_amt7(t_id);
2071 p_out_equip_amt8_tab(k) := l_rollup_equip_amt8(t_id);
2072 p_out_capit_amt1_tab(k) := l_rollup_capit_amt1(t_id);
2073 p_out_capit_amt2_tab(k) := l_rollup_capit_amt2(t_id);
2074 p_out_labor_amt1_tab(k) := l_rollup_labor_amt1(t_id);
2075 p_out_labor_amt2_tab(k) := l_rollup_labor_amt2(t_id);
2076 p_out_labor_amt3_tab(k) := l_rollup_labor_amt3(t_id);
2077 p_out_labor_amt4_tab(k) := l_rollup_labor_amt4(t_id);
2078 p_out_labor_amt5_tab(k) := l_rollup_labor_amt5(t_id);
2079 p_out_labor_amt6_tab(k) := l_rollup_labor_amt6(t_id);
2080 p_out_labor_amt7_tab(k) := l_rollup_labor_amt7(t_id);
2081 p_out_labor_amt8_tab(k) := l_rollup_labor_amt8(t_id);
2082
2083
2084
2085 p_out_object_index_tab(t_id) := k;
2086
2087 ppr_log('K '||k||'FINAL task '||t_id||' total '||l_rollup_amount1(t_id)||' l_rollup_equip_amt1(t_id) '||l_rollup_equip_amt1(t_id) ,1);
2088
2089
2090 END LOOP;
2091
2092
2093 ppr_log('ROLLUP COUNT '||p_out_equip_amt1_tab.count,1);
2094
2095 Exception
2096 when others then
2097 raise;
2098
2099 end rollup_amounts ;
2100
2101 procedure merge_into_fact (
2102 p_calling_mode IN VARCHAR2 default 'CREATE'
2103 ,p_max_msg_count IN NUMBER default NULL
2104 ,p_fact_slice IN VARCHAR2 default 'WBS'
2105 ,p_debug_mode IN VARCHAR2 default 'N'
2106 ,p_project_id In number default -1
2107 ,p_budget_version_id in number default -1
2108 ,p_rbs_cbs_version_id_2 in number default -1
2109 ,p_rbs_cbs_version_id in number default -1
2110 ,p_object_id in number default -1
2111 ,p_currency_code in VARCHAR2 default -1
2112 ,p_fact_task_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2113 ,p_fact_task_index_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2114 ,p_fact_amount1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2115 ,p_fact_amount2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2116 ,p_fact_amount3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2117 ,p_fact_amount4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2118 ,p_fact_amount5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2119 ,p_fact_amount6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2120 ,p_fact_amount7_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2121 ,p_fact_amount8_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2122 ,p_fact_qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2123 ,p_fact_qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2124 ,p_fact_qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2125 ,p_fact_labor_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2126 ,p_fact_equip_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2127 ,p_fact_labor_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2128 ,p_fact_equip_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2129 ,p_fact_labor_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2130 ,p_fact_equip_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2131 ,p_fact_bill_labor_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2132 ,p_fact_bill_equip_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2133 ,p_fact_bill_labor_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2134 ,p_fact_bill_equip_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2135 ,p_fact_bill_labor_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2136 ,p_fact_bill_equip_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2137 ,p_fact_bill_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2138 ,p_fact_bill_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2139 ,p_fact_bill_labor_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2140 ,p_fact_bill_labor_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2141 ,p_fact_equip_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2142 ,p_fact_equip_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2143 ,p_fact_equip_amt3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2144 ,p_fact_equip_amt4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2145 ,p_fact_equip_amt5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2146 ,p_fact_equip_amt6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2147 ,p_fact_equip_amt7_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2148 ,p_fact_equip_amt8_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2149 ,p_fact_capit_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2150 ,p_fact_capit_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2151 ,p_fact_labor_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2152 ,p_fact_labor_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2153 ,p_fact_labor_amt3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2154 ,p_fact_labor_amt4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2155 ,p_fact_labor_amt5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2156 ,p_fact_labor_amt6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2157 ,p_fact_labor_amt7_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2158 ,p_fact_labor_amt8_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2159 ,x_return_status OUT NOCOPY VARCHAR2
2160 ,x_msg_count OUT NOCOPY NUMBER
2161 ,x_msg_data OUT NOCOPY VARCHAR2
2162 ) as
2163
2164
2165 l_upd_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
2166 l_upd_task_arr_2 PA_PLSQL_DATATYPES.NumTabTyp;
2167 l_upd_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
2168
2169 l_ins_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
2170
2171 l_out_task_index_arr PA_PLSQL_DATATYPES.NumTabTyp;
2172
2173 begin
2174
2175
2176 l_upd_task_flag_arr.delete;
2177 l_upd_task_arr.delete;
2178 l_upd_task_arr_2.delete;
2179
2180 ppr_log('INSIDE MERGE p_calling_mode '||p_calling_mode||' p_fact_slice '||p_fact_slice ,0);
2181
2182
2183 ppr_log(' TEST COUNT '||p_fact_equip_amt1_arr.count,0);
2184
2185 FORALL i IN 1..p_fact_task_arr.count
2186 INSERT INTO pa_pjt_fact_period (
2187 ----
2188 PROJECT_ID , PERIOD_ID
2189 , PLAN_VERSION_ID , CURRENCY_CODE
2190 ----
2191 ,RAW_COST, BRDN_COST, REVENUE
2192 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
2193 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
2194 --------
2195 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
2196 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
2197 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
2198 ----
2199 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
2200 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
2201 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
2202 -------
2203 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
2204 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
2205 ----
2206 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
2207 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
2208 -----
2209 , START_DATE
2210 ----
2211 )
2212 SELECT
2213 p_project_id ,p_fact_task_arr(i)
2214 ,p_budget_version_id , p_currency_code
2215 ----
2216 ----
2217 , p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
2218 ,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
2219 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
2220 -----
2221 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
2222 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
2223 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
2224 ----
2225 ,0,0,0,0
2226 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
2227 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
2228 -------
2229 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
2230 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
2231 ----
2232 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
2233 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
2234 -------
2235 -------
2236 ,to_date( to_char(p_fact_task_arr(i)),'YYYYMMDD')
2237 from dual ;
2238
2239
2240 end merge_into_fact;
2241
2242 End PA_ppr_rollup_Period_PVT;