[Home] [Help]
PACKAGE BODY: APPS.PA_PPR_ROLLUP_PVT
Source
1 package body PA_ppr_rollup_PVT as
2 /* $Header: PAPPR02B.pls 120.0.12020000.13 2013/05/23 23:39:57 sachandr noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PPR_ROLLUP_PVT';
5
6 G_RBS_AGGR_LEVEL varchar2(1);
7 G_WBS_ROLLUP_FLAG varchar2(1);
8 G_PRG_ROLLUP_FLAG varchar2(1);
9
10 G_DEBUG_MODE varchar2(1);
11
12
13
14 procedure rbs_denorm
15 (
16 p_rbs_version_id In NUMBER
17 ) as
18
19 l_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
20 l_task_level_arr PA_PLSQL_DATATYPES.NumTabTyp;
21 l_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
22 l_line_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
23 l_leaf_node_arr PA_PLSQL_DATATYPES.NumTabTyp;
24
25 cursor c1 is
26 SELECT object_id , LEVEL,
27 SYS_CONNECT_BY_PATH(object_id, '/') connect_path,
28 connect_by_isleaf leaf_node
29 FROM pa_ppr_obj_tmp r
30 START WITH r.parent_object_id is null CONNECT BY
31 PRIOR r.object_id = r.parent_object_id ;
32
33
34 begin
35
36 delete from pa_ppr_obj_tmp;
37 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
38 select rbs_element_id , nvl(parent_element_id,p_rbs_version_id)
39 from pa_rbs_elements
40 where rbs_version_id = p_rbs_version_id;
41
42 insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( p_rbs_version_id , null );
43
44 OPEN c1;
45
46 FETCH c1
47 BULK COLLECT INTO l_task_arr, l_task_level_arr,l_connect_path_arr , l_leaf_node_arr ;
48
49 CLOSE c1;
50
51 FORALL k IN 1..l_task_arr.COUNT
52 UPDATE pa_rbs_elements
53 SET connect_path = l_connect_path_arr(k),
54 leaf_node = l_leaf_node_arr(k)
55 WHERE rbs_element_id = l_task_arr(k);
59
56
57 end ;
58
60
61 procedure plan_update
62 (
63 p_plan_version_id IN NUMBER ,
64 p_project_id IN NUMBER ,
65 p_rbs_version_id In NUMBER ,
66 p_cbs_version_id IN NUMBER ,
67 x_msg_code OUT NOCOPY VARCHAR2,
68 x_return_status OUT NOCOPY VARCHAR2 ) as
69
70 l_msg_count number;
71
72 l_scoped_out varchar2(1) := 'Y' ; -- SCOPE_00
73 l_cbs_version_id NUMBER;
74 begin
75
76 select cbs_version_id into l_cbs_version_id from pa_projects_all where project_id = p_project_id;
77
78 if l_scoped_out <> 'Y' then -- SCOPE_00
79
80 PA_ppr_rollup_PVT.plan_rollup(
81 p_commit => 'N'
82 ,p_calling_mode => 'INCREMENTAL'
83 ,p_fact_slice => 'WBS'
84 ,p_debug_mode => 'N'
85 ,p_project_id => p_project_id
86 ,p_budget_version_id => p_plan_version_id
87 ,p_rbs_version_id => -1
88 ,p_cbs_version_id => -1
89 ,p_proj_element_id => -1
90 ,p_rbs_element_id => -1
91 ,p_cbs_element_id => -1
92 ,x_return_status => x_return_status
93 ,x_msg_count => l_msg_count
94 ,x_msg_data => x_msg_code
95 ) ;
96
97 PA_ppr_rollup_PVT.plan_rollup(
98 p_commit => 'N'
99 ,p_calling_mode => 'INCREMENTAL'
100 ,p_fact_slice => 'RBS'
101 ,p_debug_mode => 'N'
102 ,p_project_id => p_project_id
103 ,p_budget_version_id => p_plan_version_id
104 ,p_rbs_version_id => p_rbs_version_id
105 ,p_cbs_version_id => -1
106 ,p_proj_element_id => -1
107 ,p_rbs_element_id => -1
108 ,p_cbs_element_id => -1
109 ,x_return_status => x_return_status
110 ,x_msg_count => l_msg_count
111 ,x_msg_data => x_msg_code
112 ) ;
113
114 end if; -- SCOPE_00
115
116 PA_ppr_rollup_PVT.plan_rollup(
117 p_commit => 'N'
118 ,p_calling_mode => 'INCREMENTAL'
119 ,p_fact_slice => 'CBS'
120 ,p_debug_mode => 'N'
121 ,p_project_id => p_project_id
122 ,p_budget_version_id => p_plan_version_id
123 ,p_rbs_version_id => -1
124 ,p_cbs_version_id => l_cbs_version_id
125 ,p_proj_element_id => -1
126 ,p_rbs_element_id => -1
127 ,p_cbs_element_id => -1
128 ,x_return_status => x_return_status
129 ,x_msg_count => l_msg_count
130 ,x_msg_data => x_msg_code
131 ) ;
132
133
134 end ;
135
136
137 procedure plan_create
138 (
139 p_plan_version_id IN NUMBER ,
140 p_project_id IN NUMBER ,
141 p_rbs_version_id In NUMBER ,
142 p_cbs_version_id IN NUMBER ,
143 x_msg_code OUT NOCOPY VARCHAR2,
144 x_return_status OUT NOCOPY VARCHAR2 ) as
145
146 l_msg_count number;
147
148 l_scoped_out varchar2(1) := 'Y' ; -- SCOPE_00
149 l_cbs_version_id NUMBER;
150
151
152 begin
153
154 select cbs_version_id into l_cbs_version_id from pa_projects_all where project_id = p_project_id;
155
156
157 if l_scoped_out <> 'Y' then -- SCOPE_00
158
159 PA_ppr_rollup_PVT.plan_rollup(
160 p_commit => 'N'
161 ,p_calling_mode => 'CREATE'
162 ,p_fact_slice => 'WBS'
163 ,p_debug_mode => 'N'
164 ,p_project_id => p_project_id
165 ,p_budget_version_id => p_plan_version_id
166 ,p_rbs_version_id => -1
167 ,p_cbs_version_id => -1
168 ,p_proj_element_id => -1
169 ,p_rbs_element_id => -1
170 ,p_cbs_element_id => -1
171 ,x_return_status => x_return_status
172 ,x_msg_count => l_msg_count
173 ,x_msg_data => x_msg_code
174 ) ;
175
176 PA_ppr_rollup_PVT.plan_rollup(
177 p_commit => 'N'
178 ,p_calling_mode => 'CREATE'
179 ,p_fact_slice => 'RBS'
180 ,p_debug_mode => 'N'
181 ,p_project_id => p_project_id
182 ,p_budget_version_id => p_plan_version_id
183 ,p_rbs_version_id => p_rbs_version_id
184 ,p_cbs_version_id => -1
185 ,p_proj_element_id => -1
186 ,p_rbs_element_id => -1
187 ,p_cbs_element_id => -1
188 ,x_return_status => x_return_status
189 ,x_msg_count => l_msg_count
190 ,x_msg_data => x_msg_code
191 ) ;
192
193
194 end if; -- SCOPE_00
195
196 PA_ppr_rollup_PVT.plan_rollup(
197 p_commit => 'N'
198 ,p_calling_mode => 'CREATE'
199 ,p_fact_slice => 'CBS'
200 ,p_debug_mode => 'N'
201 ,p_project_id => p_project_id
202 ,p_budget_version_id => p_plan_version_id
203 ,p_rbs_version_id => p_rbs_version_id
204 ,p_cbs_version_id => l_cbs_version_id
205 ,p_proj_element_id => -1
206 ,p_rbs_element_id => -1
207 ,p_cbs_element_id => -1
208 ,x_return_status => x_return_status
209 ,x_msg_count => l_msg_count
210 ,x_msg_data => x_msg_code
211 ) ;
212
213
214 end ;
215
216 procedure ppr_log (
217 p_msg IN VARCHAR2,
218 p_level IN number default 0
219 ) as
220 --pragma autonomous_transaction;
221 begin
222
226 -- end if;
223 -- insert into sri_log values ( fnd_log_messages_s.nextval, p_msg);
224 -- commit;
225
227
228
229 IF G_debug_mode = 'Y' THEN
230 pa_debug.write('PA_ppr_rollup_PVT: ' , p_msg ,1);
231 END IF;
232
233 null;
234 end ppr_log;
235
236
237
238 procedure plan_rollup (
239 p_commit IN VARCHAR2 default 'N'
240 ,p_calling_mode IN VARCHAR2 default 'CREATE'
241 ,p_fact_slice IN VARCHAR2 default 'WBS'
242 ,p_debug_mode IN VARCHAR2 default 'N'
243 ,p_project_id In number default -1
244 ,p_budget_version_id in number default -1
245 ,p_rbs_version_id in number default -1
246 ,p_cbs_version_id in number default -1
247 ,p_proj_element_id in number default -1
248 ,p_rbs_element_id in number default -1
249 ,p_cbs_element_id in number default -1
250 ,x_return_status OUT NOCOPY VARCHAR2
251 ,x_msg_count OUT NOCOPY NUMBER
252 ,x_msg_data OUT NOCOPY VARCHAR2
253 ,p_wbs_rollup_flag IN VARCHAR2 default 'Y' -- 16767868
254 ) as
255
256
257 l_return_status VARCHAR2(1);
258 l_msg_count NUMBER ;
259 l_msg_data VARCHAR2(250);
260
261 c number;
262
263 l_project_currency varchar2(10);
264 l_functional_currency varchar2(10);
265
266 l_version_type varchar2(15);
267
268 l_object_id number;
269
270 l_3_level_object_id number;
271 l_3_level_version_id number;
272
273 l_rbs_cbs_version_id_2 number;
274
275
276 l_struct_ver_id number;
277 l_struct_elem_id number;
278 l_project_id number;
279 l_rbs_cbs_version_id number ;
280 l_budget_version_id number;
281
282 l_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
283 l_task_level_arr PA_PLSQL_DATATYPES.NumTabTyp;
284 l_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
285 l_line_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
286 l_leaf_node_arr PA_PLSQL_DATATYPES.NumTabTyp;
287 l_line_task_level_arr PA_PLSQL_DATATYPES.NumTabTyp;
288
289
290 l_prj_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
291 l_prj_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
292 l_prj_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
293 l_prj_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
294 l_prj_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
295 l_prj_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
296 l_prj_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
297 l_prj_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
298 l_prj_amount9_arr PA_PLSQL_DATATYPES.NumTabTyp;
299 l_prj_amount10_arr PA_PLSQL_DATATYPES.NumTabTyp;
300
301
302 l_func_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
303 l_func_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
304 l_func_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
305 l_func_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
306 l_func_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
307 l_func_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
308 l_func_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
309 l_func_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
310 l_func_amount9_arr PA_PLSQL_DATATYPES.NumTabTyp;
311 l_func_amount10_arr PA_PLSQL_DATATYPES.NumTabTyp;
312
313 l_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
314 l_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
315 l_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
316
317
318 l_out_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
319 l_out_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
320 l_out_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
321
322 l_out_labor_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
323 l_out_labor_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
324 l_out_labor_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
325
326 l_out_equip_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
327 l_out_equip_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
328 l_out_equip_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
329
330 l_out_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
331 l_upd_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
332
333 l_ins_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
334 l_upd_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
335
336 l_out_task_index_arr PA_PLSQL_DATATYPES.NumTabTyp;
337
338 l_out_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
339 l_out_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
340 l_out_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
341 l_out_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
342 l_out_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
343 l_out_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
344 l_out_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
345 l_out_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
346
347
348 l_out_bill_labor_Qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
349 l_out_bill_equip_Qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
350 l_out_bill_labor_Qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
351 l_out_bill_equip_Qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
352 l_out_bill_labor_Qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
353 l_out_bill_equip_Qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
354 l_out_bill_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
355 l_out_bill_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
356 l_out_bill_labor_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
357 l_out_bill_labor_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
358 l_out_equip_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
359 l_out_equip_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
360 l_out_capit_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
361 l_out_capit_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
362 l_out_labor_amt1_arr PA_PLSQL_DATATYPES.NumTabTyp;
363 l_out_labor_amt2_arr PA_PLSQL_DATATYPES.NumTabTyp;
364
368 l_out_labor_amt6_arr PA_PLSQL_DATATYPES.NumTabTyp;
365 l_out_labor_amt3_arr PA_PLSQL_DATATYPES.NumTabTyp;
366 l_out_labor_amt4_arr PA_PLSQL_DATATYPES.NumTabTyp;
367 l_out_labor_amt5_arr PA_PLSQL_DATATYPES.NumTabTyp;
369 l_out_labor_amt7_arr PA_PLSQL_DATATYPES.NumTabTyp;
370 l_out_labor_amt8_arr PA_PLSQL_DATATYPES.NumTabTyp;
371
372
373 l_out_equip_amt3_arr PA_PLSQL_DATATYPES.NumTabTyp;
374 l_out_equip_amt4_arr PA_PLSQL_DATATYPES.NumTabTyp;
375 l_out_equip_amt5_arr PA_PLSQL_DATATYPES.NumTabTyp;
376 l_out_equip_amt6_arr PA_PLSQL_DATATYPES.NumTabTyp;
377 l_out_equip_amt7_arr PA_PLSQL_DATATYPES.NumTabTyp;
378 l_out_equip_amt8_arr PA_PLSQL_DATATYPES.NumTabTyp;
379
380
381
382
383 l_res_class_arr PA_PLSQL_DATATYPES.Char30TabTyp;
384 l_billable_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
385
386 -- Open Item : Can we sum the resource assignments at task level here itself ? Will the sum scale ?
387
388 cursor c2_backup is
389 SELECT /*+ ordered */
390 ra.rate_based_flag billable_flag ,
391 ra.task_id wbs_element_id ,
392 ra.resource_class_code resource_class ,
393 sum(nvl(bl.project_raw_cost,0)) ,
394 sum(nvl(bl.project_burdened_COST,0)) ,
395 sum(nvl(bl.project_revenue,0)) ,
396 sum(nvl(bl.project_init_raw_cost,0)) ,
397 sum(nvl(bl.project_init_burdened_cost,0)) ,
398 sum(nvl(bl.project_init_revenue,0)) ,
399 sum( decode( l_version_type,'FINANCIAL', nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0), 0) ) ,
400 sum(decode( l_version_type,'FINANCIAL', nvl(bl.project_burdened_COST,0) - nvl(bl.project_init_burdened_cost,0), 0)) ,
401 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
402 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
403 sum(nvl(bl.revenue,0)) func_revenue ,
404 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
405 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
406 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
407 sum(decode( l_version_type,'FINANCIAL', nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0), 0)) func_etc_raw_cost ,
408 sum(decode( l_version_type,'FINANCIAL', nvl(bl.burdened_COST,0) - nvl(bl.init_burdened_cost,0), 0)) func_etc_brdn_cost ,
409 sum(nvl(bl.quantity,0) ) quantity,
410 sum(nvl(bl.init_quantity,0)) actual_quantity ,
411 sum(decode( l_version_type,'FINANCIAL', nvl(bl.quantity,0) - nvl(bl.init_quantity,0), 0)) etc_quantity ,
412 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
413 nvl(b.leaf_node,1) ,
414 nvl(b.wbs_rbs_level,1)
415 FROM PA_RESOURCE_ASSIGNMENTS ra ,
416 PA_BUDGET_LINES bl ,
417 pa_ppr_obj_tmp b
418 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
419 and ra.budget_version_id = l_budget_version_id
420 and ra.task_id = b.object_id
421 group by
422 ra.rate_based_flag ,
423 ra.task_id,
424 ra.resource_class_code,
425 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
426 nvl(b.leaf_node,1) ,
427 nvl(b.wbs_rbs_level,1) ;
428
429
430 cursor c2 is --satya
431
432 SELECT
433 billable_flag ,
434 wbs_element_id ,
435 resource_class ,
436 sum(project_raw_cost) ,
437 sum(project_burdened_COST) ,
438 sum(project_revenue) ,
439 sum(project_init_raw_cost) ,
440 sum(project_init_burdened_cost) ,
441 sum(project_init_revenue),
442 sum(etc_prj_raw_cost) ,
443 sum(etc_prj_burdened_cost) ,
444 sum(func_raw_cost),
445 sum(func_BRDN_COST) ,
446 sum(func_revenue ),
447 sum(func_actual_raw_cost) ,
448 sum(func_actual_brdn_cost) ,
449 sum(func_actual_revenue) ,
450 sum(func_etc_raw_cost) ,
451 sum(func_etc_brdn_cost) ,
452 sum(quantity),
453 sum(actual_quantity) ,
454 sum(etc_quantity) ,
455 connect_path,
456 leaf_node,
457 task_level_arr
458 from ( SELECT /*+ ordered */
459 ra.rate_based_flag billable_flag ,
460 ra.task_id wbs_element_id ,
461 ra.resource_class_code resource_class ,
462 sum(nvl(bl.project_raw_cost,0)) project_raw_cost ,
463 sum(nvl(bl.project_burdened_COST,0)) project_burdened_COST ,
464 sum(nvl(bl.project_revenue,0)) project_revenue ,
465 sum(nvl(bl.project_init_raw_cost,0)) project_init_raw_cost ,
466 sum(nvl(bl.project_init_burdened_cost,0)) project_init_burdened_cost ,
467 sum(nvl(bl.project_init_revenue,0)) project_init_revenue ,
468 0 etc_prj_raw_cost ,
469 0 etc_prj_burdened_cost ,
470 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
471 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
472 sum(nvl(bl.revenue,0)) func_revenue ,
473 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
474 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
475 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
476 0 func_etc_raw_cost ,
477 0 func_etc_brdn_cost ,
478 sum(nvl(bl.quantity,0) ) quantity,
479 sum(nvl(bl.init_quantity,0)) actual_quantity ,
480 0 etc_quantity ,
481 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) connect_path,
482 nvl(b.leaf_node,1) leaf_node,
483 nvl(b.wbs_rbs_level,1) task_level_arr
484 FROM PA_RESOURCE_ASSIGNMENTS ra ,
485 PA_BUDGET_LINES bl ,
486 pa_ppr_obj_tmp b
487 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
488 and ra.budget_version_id = l_budget_version_id
489 and ra.task_id = b.object_id
490 group by
491 ra.rate_based_flag,
492 ra.task_id,
493 ra.resource_class_code,
494 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
495 nvl(b.leaf_node,1) ,
499 bl2.rate_based_flag billable_flag ,
496 nvl(b.wbs_rbs_level,1)
497 UNION ALL
498 SELECT /*+ ordered */
500 bl2.project_element_id wbs_element_id ,
501 bl2.resource_class_code resource_class ,
502 0 project_raw_cost ,
503 0 project_burdened_COST ,
504 0 project_revenue ,
505 0 project_init_raw_cost ,
506 0 project_init_burdened_cost ,
507 0 project_init_revenue,
508 bl2.etc_prj_raw_cost ,
509 bl2.etc_prj_burdened_cost ,
510 0 func_raw_cost,
511 0 func_BRDN_COST ,
512 0 func_revenue ,
513 0 func_actual_raw_cost ,
514 0 func_actual_brdn_cost ,
515 0 func_actual_revenue ,
516 bl2.ETC_PFC_RAW_COST func_etc_raw_cost ,
517 bl2.etc_pfc_burdened_cost func_etc_brdn_cost ,
518 0 quantity,
519 0 actual_quantity ,
520 bl2.etc_quantity etc_quantity ,
521 nvl(b.connect_path , '/'||DECODE(bl2.project_element_id, 0, l_struct_elem_id, bl2.project_element_id) ) connect_path,
522 nvl(b.leaf_node,1) leaf_node,
523 nvl(b.wbs_rbs_level,1) task_level_arr
524 FROM
525 PJI_FM_EXTR_PLAN_LINES bl2,
526 pa_ppr_obj_tmp b
527 WHERE
528 bl2.plan_version_id = l_budget_version_id
529 and bl2.project_element_id = b.object_id ) t1
530 group by
531 billable_flag ,
532 wbs_element_id ,
533 resource_class ,
534 connect_path,
535 leaf_node,
536 task_level_arr ;
537
538
539
540 cursor c2_wbs_plan_update is
541 SELECT
542 t.billable_flag
543 ,t.task_id
544 ,t.resource_class_code
545 ,sum(nvl(t.prj_raw_cost,0))
546 ,sum(nvl(t.prj_BRDN_COST,0))
547 ,sum(nvl(t.prj_revenue,0))
548 ,sum(nvl(t.act_prj_raw_cost,0))
549 ,sum(nvl(t.act_prj_brdn_cost,0))
550 ,sum(nvl(t.act_prj_revenue,0))
551 ,sum(nvl(t.etc_prj_raw_cost,0))
552 ,sum(nvl(t.etc_prj_brdn_cost,0))
553 ,sum(nvl(t.pfc_raw_cost,0))
554 ,sum(nvl(t.pfc_BRDN_COST,0))
555 ,sum(nvl(t.pfc_revenue,0))
556 ,sum(nvl(t.act_pfc_raw_cost,0))
557 ,sum(nvl(t.act_pfc_brdn_cost,0))
558 ,sum(nvl(t.act_pfc_revenue,0))
559 ,sum(nvl(t.etc_pfc_raw_cost,0))
560 ,sum(nvl(t.etc_pfc_brdn_cost,0))
561 ,sum(nvl(t.quantity,0))
562 ,sum(nvl(t.act_quantity,0))
563 ,sum(nvl(t.etc_quantity,0))
564 , nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id) ) connect_path
565 , nvl(b.leaf_node,1) leaf_node
566 , nvl(b.wbs_rbs_level,1) wbs_rbs_level
567 FROM
568 (
569 SELECT
570 bl.rate_based_flag billable_flag
571 , bl.project_element_id task_id
572 , bl.resource_class_code resource_class_code
573 , bl.prj_raw_cost prj_raw_cost
574 , bl.prj_burdened_cost prj_BRDN_COST
575 , bl.prj_revenue prj_revenue
576 , bl.act_prj_raw_cost act_prj_raw_cost
577 , bl.act_prj_burdened_cost act_prj_brdn_cost
578 , bl.act_prj_revenue act_prj_revenue
579 , bl.etc_prj_raw_cost etc_prj_raw_cost
580 , bl.etc_prj_burdened_cost etc_prj_brdn_cost
581 , bl.pfc_raw_cost pfc_raw_cost
582 , bl.pfc_burdened_cost pfc_BRDN_COST
583 , bl.pfc_revenue pfc_revenue
584 , bl.act_pfc_raw_cost act_pfc_raw_cost
585 , bl.act_pfc_burdened_cost act_pfc_brdn_cost
586 , bl.act_pfc_revenue act_pfc_revenue
587 , bl.ETC_PFC_RAW_COST etc_pfc_raw_cost
588 , bl.etc_pfc_burdened_cost etc_pfc_brdn_cost
589 , bl.quantity quantity
590 , bl.act_quantity act_quantity
591 , bl.etc_quantity etc_quantity
592 FROM
593 PJI_FM_EXTR_PLAN_LINES bl
594 WHERE 1=1
595 AND bl.project_id = l_project_id
596 AND bl.plan_version_id = l_budget_version_id
597 ) t , pa_ppr_obj_tmp b
598 WHERE t.task_id = b.object_id (+)
599 group by
600 t.billable_flag
601 ,t. task_id
602 ,t.resource_class_code
603 , nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id, t.task_id) )
604 , nvl(b.leaf_node,1)
605 , nvl(b.wbs_rbs_level,1) ;
606
607
608 cursor c2_rbs_cbs_plan_update is
609 SELECT /*+ ordered */
610 t.billable_flag
611 ,t.task_id
612 ,t.resource_class_code
613 ,sum(nvl(t.prj_raw_cost,0))
614 ,sum(nvl(t.prj_BRDN_COST,0))
615 ,sum(nvl(t.prj_revenue,0))
616 ,sum(nvl(t.act_prj_raw_cost,0))
617 ,sum(nvl(t.act_prj_brdn_cost,0))
618 ,sum(nvl(t.act_prj_revenue,0))
619 ,sum(nvl(t.etc_prj_raw_cost,0))
620 ,sum(nvl(t.etc_prj_brdn_cost,0))
621 ,sum(nvl(t.pfc_raw_cost,0))
622 ,sum(nvl(t.pfc_BRDN_COST,0))
623 ,sum(nvl(t.pfc_revenue,0))
624 ,sum(nvl(t.act_pfc_raw_cost,0))
625 ,sum(nvl(t.act_pfc_brdn_cost,0))
626 ,sum(nvl(t.act_pfc_revenue,0))
627 ,sum(nvl(t.etc_pfc_raw_cost,0))
628 ,sum(nvl(t.etc_pfc_brdn_cost,0))
629 ,sum(nvl(t.quantity,0))
630 ,sum(nvl(t.act_quantity,0))
631 ,sum(nvl(t.etc_quantity,0))
632 , nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id) ) connect_path
633 , nvl(b.leaf_node,1) leaf_node
634 , nvl(b.wbs_rbs_level,1) wbs_rbs_level
635 , '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id)
636 FROM
637 (
638 SELECT
639 bl.rate_based_flag billable_flag
640 , decode(p_fact_slice,'RBS',bl.rbs_element_id,'CBS',bl.cbs_element_id,null) task_id
641 , bl.resource_class_code resource_class_code
642 , bl.prj_raw_cost prj_raw_cost
643 , bl.prj_burdened_cost prj_BRDN_COST
647 , bl.act_prj_burdened_cost act_prj_brdn_cost
644 , bl.prj_revenue prj_revenue
645 , bl.act_prj_raw_cost act_prj_raw_cost
646
648 , bl.act_prj_revenue act_prj_revenue
649 , bl.etc_prj_raw_cost etc_prj_raw_cost
650 , bl.etc_prj_burdened_cost etc_prj_brdn_cost
651 , bl.pfc_raw_cost pfc_raw_cost
652 , bl.pfc_burdened_cost pfc_BRDN_COST
653 , bl.pfc_revenue pfc_revenue
654 , bl.act_pfc_raw_cost act_pfc_raw_cost
655 , bl.act_pfc_burdened_cost act_pfc_brdn_cost
656 , bl.act_pfc_revenue act_pfc_revenue
657 , bl.ETC_PFC_RAW_COST etc_pfc_raw_cost
658 , bl.etc_pfc_burdened_cost etc_pfc_brdn_cost
659 , bl.quantity quantity
660 , bl.act_quantity act_quantity
661 , bl.etc_quantity etc_quantity
662 , bl.rbs_element_id
663 , bl.cbs_element_id
664 FROM
665 PJI_FM_EXTR_PLAN_LINES bl
666 WHERE 1=1
667 AND bl.project_id = l_project_id
668 AND bl.plan_version_id = l_budget_version_id
669 ) t , pa_ppr_obj_tmp b
670 WHERE t.task_id = b.object_id (+)
671 and (
672 ( p_fact_slice = 'RBS' AND t.rbs_element_id = b.object_id )
673 OR
674 ( p_fact_slice = 'CBS' AND t.cbs_element_id = b.object_id )
675 )
676 group by
677 t.billable_flag
678 ,t.task_id
679 ,t.resource_class_code
680 , nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id, t.task_id) )
681 , nvl(b.leaf_node,1)
682 , nvl(b.wbs_rbs_level,1);
683
684
685 cursor c2_rbs_cbs is
686 SELECT /*+ ordered */
687 ra.rate_based_flag billable_flag ,
688 decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null) rbs_element_id ,
689 ra.resource_class_code resource_class ,
690 sum(nvl(bl.project_raw_cost,0)) ,
691 sum(nvl(bl.project_burdened_COST,0)) ,
692 sum(nvl(bl.project_revenue,0)) ,
693 sum(nvl(bl.project_init_raw_cost,0)) ,
694 sum(nvl(bl.project_init_burdened_cost,0)) ,
695 sum(nvl(bl.project_init_revenue,0)) ,
696 sum( decode( l_version_type,'FINANCIAL', nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0), 0) ) ,
697 sum(decode( l_version_type,'FINANCIAL', nvl(bl.project_burdened_COST,0) - nvl(bl.project_init_burdened_cost,0), 0)) ,
698 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
699 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
700 sum(nvl(bl.revenue,0)) func_revenue ,
701 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
702 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
703 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
704 sum(decode( l_version_type,'FINANCIAL', nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0), 0)) func_etc_raw_cost ,
705 sum(decode( l_version_type,'FINANCIAL', nvl(bl.burdened_COST,0) - nvl(bl.init_burdened_cost,0), 0)) func_etc_brdn_cost ,
706 sum(nvl(bl.quantity,0) ) quantity,
707 sum(nvl(bl.init_quantity,0)) actual_quantity ,
708 sum(decode( l_version_type,'FINANCIAL', nvl(bl.quantity,0) - nvl(bl.init_quantity,0), 0)) etc_quantity ,
709 nvl(b.connect_path , '/'||decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null) ) ,
710 nvl(b.leaf_node,1) ,
711 nvl(b.wbs_rbs_level,1),
712 '/'||decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null)
713 FROM PA_RESOURCE_ASSIGNMENTS ra ,
714 PA_BUDGET_LINES bl ,
715 pa_ppr_obj_tmp b
716 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
717 and ra.budget_version_id = l_budget_version_id
718 and (
719 ( p_fact_slice = 'RBS' AND ra.rbs_element_id = b.object_id )
720 OR
721 ( p_fact_slice = 'CBS' AND ra.cbs_element_id = b.object_id )
722 )
723 group by
724 ra.rate_based_flag,
725 decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null),
726 ra.resource_class_code,
727 nvl(b.connect_path , '/'||decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null) ) ,
728 nvl(b.leaf_node,1) ,
729 nvl(b.wbs_rbs_level,1) ;
730
731
732
733 cursor c2_wbs_to_rbs_or_cbs is
734 SELECT /*+ ordered */
735 ra.rate_based_flag billable_flag ,
736 decode(p_fact_slice,'CBS_TO_WBS_TO_RBS',ra.rbs_element_id, 'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) rbs_element_id ,
737 ra.resource_class_code resource_class ,
738 sum(nvl(bl.project_raw_cost,0)) ,
739 sum(nvl(bl.project_burdened_COST,0)) ,
740 sum(nvl(bl.project_revenue,0)) ,
741 sum(nvl(bl.project_init_raw_cost,0)) ,
742 sum(nvl(bl.project_init_burdened_cost,0)) ,
743 sum(nvl(bl.project_init_revenue,0)) ,
744 sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
745 sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
746 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
747 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
748 sum(nvl(bl.revenue,0)) func_revenue ,
749 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
750 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
751 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
752 sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
753 sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
754 sum(nvl(bl.quantity,0) ) quantity,
755 sum(nvl(bl.init_quantity,0)) actual_quantity ,
756 sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
757 nvl(b.connect_path , '/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ) ,
758 nvl(b.leaf_node,1) ,
759 nvl(b.wbs_rbs_level,1),
760 '/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null)
761 FROM PA_RESOURCE_ASSIGNMENTS ra ,
765 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
762 PA_BUDGET_LINES bl ,
763 pa_ppr_obj_tmp b,
764 pa_ppr_obj_tmp2 c
766 and ra.budget_version_id = l_budget_version_id
767 and ra.task_id = c.object_id
768 and (
769 ( p_fact_slice = 'CBS_TO_WBS_TO_RBS' AND ra.rbs_element_id = b.object_id and ra.cbs_element_id = l_3_level_object_id ) OR
770 ( p_fact_slice = 'WBS_TO_RBS' AND ra.rbs_element_id = b.object_id ) OR
771 ( p_fact_slice = 'WBS_TO_CBS' AND ra.cbs_element_id = b.object_id )
772 )
773 group by
774 ra.rate_based_flag ,
775 decode(p_fact_slice,'CBS_TO_WBS_TO_RBS',ra.rbs_element_id, 'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ,
776 ra.resource_class_code ,
777 nvl(b.connect_path , '/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ) ,
778 nvl(b.leaf_node,1) ,
779 nvl(b.wbs_rbs_level,1),
780 '/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ;
781
782
783 cursor c2_rbs_to_wbs is
784 SELECT /*+ ordered */
785 ra.rate_based_flag billable_flag ,
786 ra.task_id task_id ,
787 ra.resource_class_code resource_class ,
788 sum(nvl(bl.project_raw_cost,0)) ,
789 sum(nvl(bl.project_burdened_COST,0)) ,
790 sum(nvl(bl.project_revenue,0)) ,
791 sum(nvl(bl.project_init_raw_cost,0)) ,
792 sum(nvl(bl.project_init_burdened_cost,0)) ,
793 sum(nvl(bl.project_init_revenue,0)) ,
794 sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
795 sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
796 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
797 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
798 sum(nvl(bl.revenue,0)) func_revenue ,
799 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
800 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
801 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
802 sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
803 sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
804 sum(nvl(bl.quantity,0) ) quantity,
805 sum(nvl(bl.init_quantity,0)) actual_quantity ,
806 sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
807 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
808 nvl(b.leaf_node,1) ,
809 nvl(b.wbs_rbs_level,1)
810 FROM PA_RESOURCE_ASSIGNMENTS ra ,
811 PA_BUDGET_LINES bl ,
812 pa_ppr_obj_tmp b,
813 pa_ppr_obj_tmp2 c
814 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
815 and ra.budget_version_id = l_budget_version_id
816 and ra.rbs_element_id = c.object_id
817 and ra.task_id = b.object_id
818 group by
819 ra.rate_based_flag ,
820 ra.task_id ,
821 ra.resource_class_code,
822 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
823 nvl(b.leaf_node,1) ,
824 nvl(b.wbs_rbs_level,1) ;
825
826
827
828 cursor c2_cbs_to_wbs_or_rbs is
829 SELECT /*+ ordered */
830 ra.rate_based_flag billable_flag ,
831 decode(p_fact_slice,'WBS_TO_CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_WBS',ra.task_id,null) element_id ,
832 ra.resource_class_code resource_class ,
833 sum(nvl(bl.project_raw_cost,0)) ,
834 sum(nvl(bl.project_burdened_COST,0)) ,
835 sum(nvl(bl.project_revenue,0)) ,
836 sum(nvl(bl.project_init_raw_cost,0)) ,
837 sum(nvl(bl.project_init_burdened_cost,0)) ,
838 sum(nvl(bl.project_init_revenue,0)) ,
839 sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
840 sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
841 sum(nvl(bl.raw_cost,0) ) func_raw_cost,
842 sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
843 sum(nvl(bl.revenue,0)) func_revenue ,
844 sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
845 sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
846 sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
847 sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
848 sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
849 sum(nvl(bl.quantity,0) ) quantity,
850 sum(nvl(bl.init_quantity,0)) actual_quantity ,
851 sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
852 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
853 nvl(b.leaf_node,1) ,
854 nvl(b.wbs_rbs_level,1)
855 FROM PA_RESOURCE_ASSIGNMENTS ra ,
856 PA_BUDGET_LINES bl ,
857 pa_ppr_obj_tmp b,
858 pa_ppr_obj_tmp2 c
859 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
860 and ra.budget_version_id = l_budget_version_id
861 and ra.cbs_element_id = c.object_id
862 and (
863 ( p_fact_slice = 'CBS_TO_RBS' AND ra.rbs_element_id = b.object_id ) OR
864 ( p_fact_slice = 'CBS_TO_WBS' AND ra.task_id = b.object_id ) OR
865 ( p_fact_slice = 'WBS_TO_CBS_TO_RBS' AND ra.rbs_element_id = b.object_id AND ra.task_id = l_3_level_object_id )
866 )
867 group by
868 ra.rate_based_flag ,
869 decode(p_fact_slice,'WBS_TO_CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_WBS',ra.task_id,null),
870 ra.resource_class_code,
871 nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
872 nvl(b.leaf_node,1) ,
873 nvl(b.wbs_rbs_level,1) ;
874
875
876 cursor c2_wbs_reverse_etc is
877 SELECT
878 t.billable_flag
879 ,t.task_id
880 ,t.resource_class_code
881 ,sum(nvl(t.prj_raw_cost,0))
882 ,sum(nvl(t.prj_BRDN_COST,0))
883 ,sum(nvl(t.prj_revenue,0))
884 ,sum(nvl(t.act_prj_raw_cost,0))
888 ,sum(nvl(t.etc_prj_brdn_cost,0))
885 ,sum(nvl(t.act_prj_brdn_cost,0))
886 ,sum(nvl(t.act_prj_revenue,0))
887 ,sum(nvl(t.etc_prj_raw_cost,0))
889 ,sum(nvl(t.pfc_raw_cost,0))
890 ,sum(nvl(t.pfc_BRDN_COST,0))
891 ,sum(nvl(t.pfc_revenue,0))
892 ,sum(nvl(t.act_pfc_raw_cost,0))
893 ,sum(nvl(t.act_pfc_brdn_cost,0))
894 ,sum(nvl(t.act_pfc_revenue,0))
895 ,sum(nvl(t.etc_pfc_raw_cost,0))
896 ,sum(nvl(t.etc_pfc_brdn_cost,0))
897 ,sum(nvl(t.quantity,0))
898 ,sum(nvl(t.act_quantity,0))
899 ,sum(nvl(t.etc_quantity,0))
900 , nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id) ) connect_path
901 , nvl(b.leaf_node,1) leaf_node
902 , nvl(b.wbs_rbs_level,1) wbs_rbs_level
903 FROM
904 (
905 SELECT
906 bl.rate_based_flag billable_flag
907 , bl.project_element_id task_id
908 , bl.resource_class_code resource_class_code
909 , bl.prj_raw_cost prj_raw_cost
910 , bl.prj_burdened_cost prj_BRDN_COST
911 , bl.prj_revenue prj_revenue
912 , TO_NUMBER(NULL) act_prj_raw_cost
913 , TO_NUMBER(NULL) act_prj_brdn_cost
914 , TO_NUMBER(NULL) act_prj_revenue
915 , NVL(bl.prj_raw_cost, 0)+NVL(bl.etc_prj_raw_cost, 0) etc_prj_raw_cost
916 , NVL(bl.prj_burdened_cost, 0)+NVL(bl.etc_prj_burdened_cost, 0) etc_prj_brdn_cost
917 , bl.pfc_raw_cost pfc_raw_cost
918 , bl.pfc_burdened_cost pfc_BRDN_COST
919 , bl.pfc_revenue pfc_revenue
920 , TO_NUMBER(NULL) act_pfc_raw_cost
921 , TO_NUMBER(NULL) act_pfc_brdn_cost
922 , TO_NUMBER(NULL) act_pfc_revenue
923 , NVL(bl.pfc_raw_cost, 0)+NVL(bl.etc_pfc_raw_cost, 0) etc_pfc_raw_cost
924 , NVL(bl.pfc_burdened_cost, 0)+NVL(bl.etc_pfc_burdened_cost, 0) etc_pfc_brdn_cost
925 , bl.quantity quantity
926 , TO_NUMBER(NULL) act_quantity
927 , NVL(bl.quantity, 0)+NVL(bl.etc_quantity, 0) etc_quantity
928 FROM
929 PJI_FM_EXTR_PLAN_LINES bl
930 WHERE 1=1
931 AND bl.project_id = l_project_id
932 AND bl.plan_version_id = l_budget_version_id
933 UNION ALL
934 SELECT
935 ra.rate_based_flag billable_flag
936 , ra.task_id task_id
937 , ra.resource_class_code resource_class_code
938 , TO_NUMBER(NULL) prj_raw_cost
939 , TO_NUMBER(NULL) prj_BRDN_COST
940 , TO_NUMBER(NULL) prj_revenue
941 , bl.project_init_raw_cost act_prj_raw_cost -- new
942 , bl.project_init_burdened_cost act_prj_brdn_cost -- new
943 , bl.project_init_revenue act_prj_revenue -- new
944 , TO_NUMBER(NULL) etc_prj_raw_cost -- new
945 , TO_NUMBER(NULL) etc_prj_brdn_cost -- new
946 , TO_NUMBER(NULL) pfc_raw_cost
947 , TO_NUMBER(NULL) pfc_BRDN_COST
948 , TO_NUMBER(NULL) pfc_revenue
949 , bl.init_raw_cost act_pfc_raw_cost -- new
950 , bl.init_burdened_cost act_pfc_brdn_cost -- new
951 , bl.init_revenue act_pfc_revenue -- new
952 , TO_NUMBER(NULL) etc_pfc_raw_cost -- new
953 , TO_NUMBER(NULL) etc_pfc_brdn_cost -- new
954 , TO_NUMBER(NULL) quantity
955 , bl.init_quantity act_quantity -- new
956 , TO_NUMBER(NULL) etc_quantity -- new
957 FROM PA_RESOURCE_ASSIGNMENTS ra ,
958 PA_BUDGET_LINES bl
959 WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
960 and ra.budget_version_id = l_budget_version_id
961 ) t , pa_ppr_obj_tmp b
962 WHERE t.task_id = b.object_id (+)
963 group by
964 t.billable_flag
965 ,t. task_id
966 ,t.resource_class_code
967 , nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id, t.task_id) )
968 , nvl(b.leaf_node,1)
969 , nvl(b.wbs_rbs_level,1) ;
970
971
972
973
974 cursor c0 is
975 SELECT object_id
976 FROM pa_ppr_obj_tmp2 r
977 START WITH r.parent_object_id = l_object_id CONNECT BY
978 PRIOR r.object_id = r.parent_object_id ;
979
980 cursor c1 is
981 SELECT object_id , LEVEL,
982 SYS_CONNECT_BY_PATH(object_id, '/') connect_path,
983 connect_by_isleaf leaf_node
984 FROM pa_ppr_obj_tmp r
985 START WITH r.parent_object_id is null CONNECT BY
986 PRIOR r.object_id = r.parent_object_id ;
987
988
989
990
991
992
993 begin
994
995 pa_debug.init_err_stack('PA_ppr_rollup_PVT.plan_rollup');
996
997 fnd_profile.get('PA_DEBUG_MODE',G_debug_mode);
998 G_debug_mode := NVL(g_debug_mode, 'N');
999
1000 pa_debug.set_process('PLSQL','LOG',G_debug_mode);
1001
1002
1003
1004
1005 ppr_log('START ... ',0);
1006
1007 ppr_log(' p_commit '|| p_commit ,0);
1008 ppr_log(' p_calling_mode '|| p_calling_mode ,0);
1009 ppr_log(' p_fact_slice '|| p_fact_slice,0 );
1010 ppr_log(' p_debug_mode '|| p_debug_mode ,0);
1011 ppr_log(' p_project_id '|| p_project_id ,0);
1012 ppr_log(' p_budget_version_id '|| p_budget_version_id,0 );
1013 ppr_log(' p_rbs_version_id '|| p_rbs_version_id ,0);
1014 ppr_log(' p_cbs_version_id '|| p_cbs_version_id,0 );
1015 ppr_log(' p_proj_element_id '|| p_proj_element_id ,0);
1016 ppr_log(' p_rbs_element_id '|| p_rbs_element_id ,0);
1017 ppr_log(' p_cbs_element_id '|| p_cbs_element_id ,0 );
1018
1019
1020
1021 G_RBS_AGGR_LEVEL :='T';
1025
1022 G_WBS_ROLLUP_FLAG := p_wbs_rollup_flag ; --16767868
1023 G_PRG_ROLLUP_FLAG := 'N';
1024
1026
1027 l_project_id := p_project_id;
1028 l_budget_version_id := p_budget_version_id;
1029
1030 l_3_level_version_id := - 1;
1031 l_3_level_object_id := - 1;
1032
1033 if ( p_fact_slice in ( 'WBS_TO_CBS_TO_RBS' ) ) then
1034
1035 l_3_level_object_id := p_proj_element_id ;
1036
1037 elsif ( p_fact_slice in ( 'CBS_TO_WBS_TO_RBS') ) then
1038
1039 l_3_level_object_id := p_cbs_element_id ;
1040 l_3_level_version_id := p_cbs_version_id;
1041
1042
1043 end if ;
1044
1045
1046 if ( p_fact_slice in ( 'RBS', 'WBS_TO_RBS', 'CBS_TO_RBS' ,'RBS_TO_WBS', 'WBS_TO_CBS_TO_RBS', 'CBS_TO_WBS_TO_RBS' ) ) then
1047
1048 l_rbs_cbs_version_id := p_rbs_version_id ;
1049
1050
1051 elsif ( p_fact_slice in ( 'CBS','WBS_TO_CBS','CBS_TO_WBS' ) ) then
1052
1053 l_rbs_cbs_version_id := p_cbs_version_id ;
1054
1055
1056 else
1057 l_rbs_cbs_version_id := -1;
1058 end if;
1059
1060
1061 if ( p_fact_slice in ( 'WBS_TO_RBS', 'WBS_TO_CBS', 'CBS_TO_WBS_TO_RBS' ) ) then
1062
1063 l_object_id := p_proj_element_id ;
1064
1065 elsif ( p_fact_slice in ( 'CBS_TO_RBS', 'CBS_TO_WBS' , 'WBS_TO_CBS_TO_RBS' ) ) then
1066
1067 l_object_id := p_cbs_element_id ;
1068
1069 elsif ( p_fact_slice in ( 'RBS_TO_WBS' ) ) then
1070
1071 l_object_id := p_rbs_element_id ;
1072
1073 end if;
1074
1075
1076 ppr_log(' Before Projects table ' ,0);
1077
1078 ppr_log('l_3_level_object_id '||l_3_level_object_id);
1079 ppr_log('l_3_level_version_id '||l_3_level_version_id);
1080 ppr_log('l_rbs_cbs_version_id '||l_rbs_cbs_version_id);
1081 ppr_log('l_object_id '||l_object_id);
1082 -- l_project_id := 4546;
1083 -- l_struct_ver_id := 94014;
1084
1085 -- l_budget_version_id := 168752;
1086
1087 -- Project level data
1088 select project_currency_code , projfunc_currency_code
1089 into l_project_currency, l_functional_currency
1090 from pa_projects_All
1091 where project_id = l_project_id;
1092
1093 --l_functional_currency := 'GBP';
1094
1095
1096 ppr_log(' AFter Currency l_project_currency '||l_project_currency|| 'l_functional_currency '||l_functional_currency ,0);
1097
1098 select /*+ ordered */ distinct decode(bud.fin_plan_type_id,10,'WORKPLAN','FINANCIAL') ,
1099 s.element_version_id, s.proj_element_id
1100 into l_version_type , l_struct_ver_id , l_struct_elem_id
1101 from pa_budget_versions bud,
1102 pa_proj_elem_ver_structure s,
1103 pa_proj_structure_types ppst,
1104 pa_structure_types pst
1105 where
1106 bud.budget_version_id = l_budget_version_id
1107 and s.proj_element_id = ppst.proj_element_id (+)
1108 and ppst.structure_type_id = pst.structure_type_id (+)
1109 and pst.structure_type_class_code (+) = 'WORKPLAN'
1110 and bud.project_structure_version_id = s.element_version_id (+);
1111
1112
1113 ppr_log(' AFter WORKPLAN l_version_type '||l_version_type || ' l_struct_ver_id '||l_struct_ver_id || ' l_struct_elem_id '||l_struct_elem_id,0);
1114
1115 if ( l_version_type = 'FINANCIAL' ) then
1116
1117 select /*+ ordered */
1118 distinct 0, s.proj_element_id
1119 into l_struct_ver_id , l_struct_elem_id
1120 from
1121 pa_proj_elem_ver_structure s,
1122 pa_proj_structure_types ppst,
1123 pa_structure_types pst
1124 where
1125 s.project_id = l_project_id
1126 and s.proj_element_id = ppst.proj_element_id
1127 and ppst.structure_type_id = pst.structure_type_id
1128 and pst.structure_type_class_code = 'FINANCIAL';
1129
1130 end if;
1131
1132 ppr_log(' AFter FINANCIAL l_struct_ver_id , l_struct_elem_id '||l_struct_ver_id||' '||l_struct_elem_id ,0);
1133
1134 -- l_functional_currency := 'GBP';
1135
1136 --- Scenodary slice task / rbs / cbs list
1137
1138 delete from pa_ppr_obj_tmp2;
1139
1140 if ( p_fact_slice in ( 'WBS_TO_RBS' , 'WBS_TO_CBS' , 'CBS_TO_WBS_TO_RBS' ) ) then
1141
1142 if ( l_version_type = 'WORKPLAN' ) then
1143
1144 ppr_log('inserting into pa_ppr_obj_tmp2 workplan '|| p_fact_slice);
1145 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
1146 select /*+ ordered */
1147 a.proj_element_id , nvl(c.proj_element_id,l_struct_elem_id)
1148 from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
1149 where a.project_id = l_project_id
1150 and a.parent_structure_version_id = l_struct_ver_id
1151 and a.element_version_id = b.object_id_to1 (+)
1152 and b.object_type_From (+) = 'PA_TASKS'
1153 and b.relationship_type (+) = 'S'
1154 and b.object_id_from1 = c.element_version_id (+)
1155 AND ( p_wbs_rollup_flag = 'Y' or
1156 ( p_wbs_rollup_flag = 'N' and a.PROJ_element_id = p_proj_element_id ) ); --16767868 ;
1157
1158 elsif ( l_version_type = 'FINANCIAL' ) then
1159
1160 ppr_log('inserting into tmp2 financial '|| p_fact_slice);
1161 -- For financial tasks and fully shared.
1162 -- For budgets pa_tasks can be used directly
1163
1164 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
1165 select /*+ ordered */
1169 and ( p_wbs_rollup_flag = 'Y' or ( p_wbs_rollup_flag = 'N' and p.task_id = p_proj_element_id ) ); --16767868 ;
1166 p.task_id , nvl(p.parent_task_id, l_struct_elem_id )
1167 from pa_tasks p
1168 where p.project_id = l_project_id
1170
1171 end if;
1172
1173 elsif p_fact_slice in ( 'RBS_TO_WBS','CBS_TO_RBS', 'CBS_TO_WBS','WBS_TO_CBS_TO_RBS' ) then
1174
1175 if ( p_fact_slice like 'RBS%' ) then
1176
1177 l_rbs_cbs_version_id_2 := p_rbs_version_id ;
1178
1179 else
1180
1181 l_rbs_cbs_version_id_2 := p_cbs_version_id ;
1182
1183 end if;
1184
1185 ppr_log('p_fact_slice '||p_fact_slice|| ' l_rbs_cbs_version_id_2 '||l_rbs_cbs_version_id_2 );
1186 -- l_struct_elem_id := 0;
1187
1188 ppr_log( 'Inserting into pa_ppr_obj_tmp2');
1189 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
1190 select rbs_element_id , nvl(parent_element_id,l_rbs_cbs_version_id_2)
1191 from pa_rbs_elements
1192 where rbs_version_id = l_rbs_cbs_version_id_2;
1193
1194 ppr_log( 'Inserted into pa_ppr_obj_tmp2 count '||SQL%rowcount);
1195 end if;
1196
1197 if ( p_fact_slice in ( 'RBS_TO_WBS','CBS_TO_RBS', 'CBS_TO_WBS' , 'WBS_TO_RBS' , 'WBS_TO_CBS', 'WBS_TO_CBS_TO_RBS', 'CBS_TO_WBS_TO_RBS' ) ) then
1198
1199
1200 /* l_task_arr.delete;
1201
1202 SELECT object_id
1203 BULK COLLECT INTO l_task_arr
1204 FROM pa_ppr_obj_tmp2;
1205
1206 ppr_log('SECONDARY SOURCE l_task_arr '||l_task_arr.count);
1207 */
1208 l_task_arr.delete;
1209
1210 ppr_Log(' before opening c0');
1211 OPEN c0;
1212
1213 FETCH c0
1214 BULK COLLECT INTO l_task_arr;
1215
1216 CLOSE c0;
1217
1218 ppr_log('SECONDARY c0 cursor output l_task_arr.count '||l_task_arr.count,0);
1219
1220 delete from pa_ppr_obj_tmp2 ;
1221
1222 FORALL i IN 1..l_task_arr.count
1223 INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_task_arr(i) ) ;
1224
1225 if ( g_log_level >0 ) then
1226 for k1 in 1..l_task_arr.count loop
1227
1228 ppr_log(' SECONDARY TASK ... '||l_task_arr(k1),1);
1229
1230 end loop;
1231 end if;
1232
1233
1234 l_task_arr.delete;
1235
1236 ppr_log('before inserting l_object_id '||l_object_id);
1237 INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_object_id ) ;
1238
1239
1240 end if;
1241
1242 -- TASK Hierarchy details
1243
1244 delete from pa_ppr_obj_tmp;
1245
1246 if ( p_fact_slice in ( 'WBS', 'RBS_TO_WBS','CBS_TO_WBS' ) ) then
1247
1248 if ( l_version_type = 'WORKPLAN' ) then
1249
1250
1251 /*
1252
1253 , DECODE ( NVL(bv.wp_version_flag, 'N')
1254 , 'Y', bv.project_structure_version_id
1255 , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id) -- -9999 --
1256 ) wbs_struct_version_id
1257
1258 */
1259
1260
1261 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
1262 select /*+ ordered */
1263 a.proj_element_id , nvl(c.proj_element_id,l_struct_elem_id)
1264 from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
1265 where a.project_id = l_project_id
1266 and a.object_type = 'PA_TASKS'
1267 and a.parent_structure_version_id = l_struct_ver_id
1268 and a.element_version_id = b.object_id_to1 (+)
1269 and b.object_type_From (+) = 'PA_TASKS'
1270 and b.relationship_type (+) = 'S'
1271 and b.object_id_from1 = c.element_version_id (+) ;
1272
1273 insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( l_struct_elem_id , null );
1274
1275
1276 elsif ( l_version_type = 'FINANCIAL' ) then
1277
1278 -- For financial tasks and fully shared.
1279 -- For budgets pa_tasks can be used directly
1280
1281 ppr_log('TASKS INSERTED ',0);
1282
1283 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
1284 select /*+ ordered */
1285 p.task_id , nvl(p.parent_task_id, l_struct_elem_id )
1286 from pa_tasks p
1287 where p.project_id = l_project_id ;
1288
1289 insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( l_struct_elem_id , null );
1290
1291 end if;
1292
1293 elsif p_fact_slice in ( 'RBS' ,'CBS','WBS_TO_RBS','CBS_TO_RBS','WBS_TO_CBS', 'WBS_TO_CBS_TO_RBS', 'CBS_TO_WBS_TO_RBS' ) then
1294
1295 ppr_log('into RBS ',0);
1296
1297 l_struct_elem_id := 0;
1298 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
1299 select rbs_element_id , nvl(parent_element_id,l_rbs_cbs_version_id)
1300 from pa_rbs_elements
1301 where rbs_version_id = l_rbs_cbs_version_id;
1302
1303 ppr_log('first Insert into pa_ppr_obj_tmp '||SQL%rowcount,0);
1304 insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( l_rbs_cbs_version_id , null );
1305
1306 ppr_log('second Inserted into pa_ppr_obj_tmp '||SQL%rowcount,0);
1307 end if;
1308
1309
1310
1311
1312 OPEN c1;
1313
1314 FETCH c1
1315 BULK COLLECT INTO l_task_arr, l_task_level_arr,l_connect_path_arr , l_leaf_node_arr ;
1316
1317 CLOSE c1;
1318
1319 FORALL k IN 1..l_task_arr.COUNT
1323 wbs_rbs_level = l_task_level_arr(k)
1320 UPDATE pa_ppr_obj_tmp
1321 SET connect_path = l_connect_path_arr(k),
1322 leaf_node = l_leaf_node_arr(k),
1324 WHERE object_id = l_task_arr(k);
1325
1326
1327 ppr_log(' PATH TASKS l_task_arr.COUNT ... '||l_task_arr.COUNT,0);
1328
1329 if ( g_log_level >0 ) then
1330 for k1 in 1..l_task_arr.count loop
1331
1332 ppr_log('TASK PATH ... '||l_connect_path_arr(k1),1);
1333
1334 end loop;
1335 end if;
1336
1337
1338 l_task_arr.delete;
1339 l_task_level_arr.delete;
1340 l_connect_path_arr.delete;
1341 l_line_connect_path_arr.delete;
1342 l_leaf_node_arr.delete;
1343
1344 ppr_log('AFTER TASK ... ',0);
1345
1346 if ( p_fact_slice = 'WBS' and p_calling_mode = 'INCREMENTAL' ) then
1347
1348 OPEN c2_wbs_plan_update;
1349
1350 FETCH c2_wbs_plan_update
1351 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1352 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1353 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1354 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1355 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1356 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1357 l_connect_path_arr ,
1358 l_leaf_node_arr ,
1359 l_task_level_arr;
1360
1361 CLOSE c2_wbs_plan_update;
1362
1363
1364 elsif ( p_fact_slice = 'WBS' and p_calling_mode = 'CREATE' ) then
1365
1366 OPEN c2;
1367
1368 FETCH c2
1369 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1370 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1371 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1372 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1373 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1374 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1375 l_connect_path_arr ,
1376 l_leaf_node_arr ,
1377 l_task_level_arr;
1378
1379 CLOSE c2;
1380
1381 elsif ( ( p_fact_slice = 'RBS' or p_fact_slice = 'CBS' ) and p_calling_mode = 'CREATE' ) then
1382
1383
1384 ppr_log('into RBS C2 cursor ',0);
1385
1386 OPEN c2_rbs_cbs;
1387
1388 FETCH c2_rbs_cbs
1389 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1390 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1391 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1392 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1393 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1394 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1395 l_connect_path_arr ,
1396 l_leaf_node_arr ,
1397 l_task_level_arr, l_line_connect_path_arr;
1398
1399 CLOSE c2_rbs_cbs;
1400
1401
1402 elsif ( ( p_fact_slice = 'RBS' or p_fact_slice = 'CBS' ) and p_calling_mode = 'INCREMENTAL' ) then
1403
1404
1405 ppr_log('into RBS C2 cursor plan update ',0);
1406
1407 OPEN c2_rbs_cbs_plan_update;
1408
1409 FETCH c2_rbs_cbs_plan_update
1410 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1411 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1412 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1413 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1414 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1415 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1416 l_connect_path_arr ,
1417 l_leaf_node_arr ,
1418 l_task_level_arr, l_line_connect_path_arr;
1419
1420 CLOSE c2_rbs_cbs_plan_update;
1421
1422
1423
1424 elsif ( p_fact_slice = 'WBS_TO_RBS' or p_fact_slice in ( 'WBS_TO_CBS', 'CBS_TO_WBS_TO_RBS' ) ) then
1425
1426
1427 ppr_log(' c2_wbs_to_rbs_or_cbs ... ',0);
1428
1429 OPEN c2_wbs_to_rbs_or_cbs;
1430
1431 FETCH c2_wbs_to_rbs_or_cbs
1432 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1433 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1434 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1435 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1436 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1437 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1438 l_connect_path_arr ,
1439 l_leaf_node_arr ,
1440 l_task_level_arr, l_line_connect_path_arr;
1441
1442 CLOSE c2_wbs_to_rbs_or_cbs;
1443
1444 elsif ( p_fact_slice = 'RBS_TO_WBS') then
1445
1446
1447 ppr_log(' rbs_to_wbs ... ',0);
1448
1449 OPEN c2_rbs_to_wbs;
1450
1451 FETCH c2_rbs_to_wbs
1452 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1453 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1454 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1455 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1456 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1457 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1458 l_connect_path_arr ,
1459 l_leaf_node_arr ,
1460 l_task_level_arr;
1461
1462 CLOSE c2_rbs_to_wbs;
1463
1467
1464 elsif ( p_fact_slice IN ('CBS_TO_RBS','CBS_TO_WBS', 'WBS_TO_CBS_TO_RBS' ) ) then
1465
1466
1468
1469 ppr_log(' rbs_to_wbs ... ');
1470
1471 OPEN c2_cbs_to_wbs_or_rbs;
1472
1473 FETCH c2_cbs_to_wbs_or_rbs
1474 BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
1475 l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
1476 l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
1477 l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
1478 l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
1479 l_qty1_arr, l_qty2_arr, l_qty3_arr,
1480 l_connect_path_arr ,
1481 l_leaf_node_arr ,
1482 l_task_level_arr;
1483
1484 CLOSE c2_cbs_to_wbs_or_rbs;
1485
1486 end if;
1487
1488
1489
1490 ppr_log(' BEFORE ROLLUP_AMOUNTS l_task_arr.COUNT ... '||l_task_arr.COUNT,0);
1491
1492
1493 l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
1494 l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
1495
1496 l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
1497
1498 l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
1499 l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
1500 l_out_amount7_arr.delete;l_out_amount8_arr.delete;
1501
1502
1503 l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
1504 l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
1505 l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
1506 l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
1507 l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
1508 l_out_labor_amt2_arr.delete;
1509
1510 l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
1511 l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
1512
1513
1514 l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
1515 l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
1516 l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
1517
1518
1519 rollup_amounts (
1520 p_billable_flag_tab => l_billable_flag_arr
1521 ,p_object_id_tab => l_task_arr
1522 ,p_wbs_level_tab => l_task_level_arr
1523 ,p_connect_path_tab => l_connect_path_arr
1524 ,p_leaf_node_tab => l_leaf_node_arr
1525 ,p_resource_class_tab => l_res_class_arr
1526 ,p_amount1_tab => l_prj_amount1_arr
1527 ,p_amount2_tab => l_prj_amount2_arr
1528 ,p_amount3_tab => l_prj_amount3_arr
1529 ,p_amount4_tab => l_prj_amount4_arr
1530 ,p_amount5_tab => l_prj_amount5_arr
1531 ,p_amount6_tab => l_prj_amount6_arr
1532 ,p_amount7_tab => l_prj_amount7_arr
1533 ,p_amount8_tab => l_prj_amount8_arr
1534 ,p_qty1_tab => l_qty1_arr
1535 ,p_qty2_tab => l_qty2_arr
1536 ,p_qty3_tab => l_qty3_arr
1537 ,p_out_object_index_tab => l_out_task_index_arr
1538 ,p_out_object_id_tab => l_out_task_arr
1539 ,p_out_amount1_tab => l_out_amount1_arr
1540 ,p_out_amount2_tab => l_out_amount2_arr
1541 ,p_out_amount3_tab => l_out_amount3_arr
1542 ,p_out_amount4_tab => l_out_amount4_arr
1543 ,p_out_amount5_tab => l_out_amount5_arr
1544 ,p_out_amount6_tab => l_out_amount6_arr
1545 ,p_out_amount7_tab => l_out_amount7_arr
1546 ,p_out_amount8_tab => l_out_amount8_arr
1547 ,p_out_qty1_tab => l_out_qty1_arr
1548 ,p_out_qty2_tab => l_out_qty2_arr
1549 ,p_out_qty3_tab => l_out_qty3_arr
1550 ,p_out_labor_Qty1_tab => l_out_labor_qty1_arr
1551 ,p_out_equip_Qty1_tab => l_out_equip_qty1_arr
1552 ,p_out_labor_Qty2_tab => l_out_labor_qty2_arr
1553 ,p_out_equip_Qty2_tab => l_out_equip_qty2_arr
1554 ,p_out_labor_Qty3_tab => l_out_labor_qty3_arr
1555 ,p_out_equip_Qty3_tab => l_out_equip_qty3_arr
1556 ,p_out_bill_labor_Qty1_tab => l_out_bill_labor_Qty1_arr
1557 ,p_out_bill_equip_Qty1_tab => l_out_bill_equip_Qty1_arr
1558 ,p_out_bill_labor_Qty2_tab => l_out_bill_labor_Qty2_arr
1559 ,p_out_bill_equip_Qty2_tab => l_out_bill_equip_Qty2_arr
1560 ,p_out_bill_labor_Qty3_tab => l_out_bill_labor_Qty3_arr
1561 ,p_out_bill_equip_Qty3_tab => l_out_bill_equip_Qty3_arr
1562 ,p_out_bill_amt1_tab => l_out_bill_amt1_arr
1563 ,p_out_bill_amt2_tab => l_out_bill_amt2_arr
1564 ,p_out_bill_labor_amt1_tab => l_out_bill_labor_amt1_arr
1565 ,p_out_bill_labor_amt2_tab => l_out_bill_labor_amt2_arr
1566 ,p_out_equip_amt1_tab => l_out_equip_amt1_arr
1567 ,p_out_equip_amt2_tab => l_out_equip_amt2_arr
1568 ,p_out_equip_amt3_tab => l_out_equip_amt3_arr
1569 ,p_out_equip_amt4_tab => l_out_equip_amt4_arr
1570 ,p_out_equip_amt5_tab => l_out_equip_amt5_arr
1571 ,p_out_equip_amt6_tab => l_out_equip_amt6_arr
1572 ,p_out_equip_amt7_tab => l_out_equip_amt7_arr
1573 ,p_out_equip_amt8_tab => l_out_equip_amt8_arr
1574 ,p_out_capit_amt1_tab => l_out_capit_amt1_arr
1575 ,p_out_capit_amt2_tab => l_out_capit_amt2_arr
1576 ,p_out_labor_amt1_tab => l_out_labor_amt1_arr
1577 ,p_out_labor_amt2_tab => l_out_labor_amt2_arr
1578 ,p_out_labor_amt3_tab => l_out_labor_amt3_arr
1579 ,p_out_labor_amt4_tab => l_out_labor_amt4_arr
1580 ,p_out_labor_amt5_tab => l_out_labor_amt5_arr
1581 ,p_out_labor_amt6_tab => l_out_labor_amt6_arr
1582 ,p_out_labor_amt7_tab => l_out_labor_amt7_arr
1586 ,x_msg_data => l_msg_data );
1583 ,p_out_labor_amt8_tab => l_out_labor_amt8_arr
1584 ,x_return_status => l_return_status
1585 ,x_msg_count => l_msg_count
1587
1588
1589
1590
1591 ppr_log(' after api l_out_task_arr.count ... '||l_out_task_arr.count,0);
1592
1593
1594 ppr_log(' AFTER ROLLUP COUNT '||l_out_equip_amt1_arr.count,0);
1595
1596
1597
1598 G_RBS_AGGR_LEVEL :='T';
1599
1600 G_WBS_ROLLUP_FLAG := p_wbs_rollup_flag ; --16767868
1601
1602 G_PRG_ROLLUP_FLAG := 'N';
1603
1604 merge_into_fact (
1605 p_calling_mode => p_calling_mode
1606 --,p_max_msg_count IN NUMBER default NULL
1607 ,p_fact_slice => p_fact_slice
1608 -- ,p_debug_mode IN VARCHAR2 default 'N'
1609 ,p_project_id => l_project_id
1610 ,p_budget_version_id => l_budget_version_id
1611 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
1612 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
1613 ,p_object_id => l_object_id
1614 ,p_3_level_object_id => l_3_level_object_id
1615 ,p_3_level_version_id => l_3_level_version_id
1616 ,p_currency_code => l_project_currency
1617 ,p_fact_task_arr => l_out_task_arr
1618 ,p_fact_task_index_arr => l_out_task_index_arr
1619 ,p_fact_amount1_arr => l_out_amount1_arr
1620 ,p_fact_amount2_arr => l_out_amount2_arr
1621 ,p_fact_amount3_arr => l_out_amount3_arr
1622 ,p_fact_amount4_arr => l_out_amount4_arr
1623 ,p_fact_amount5_arr => l_out_amount5_arr
1624 ,p_fact_amount6_arr => l_out_amount6_arr
1625 ,p_fact_amount7_arr => l_out_amount7_arr
1626 ,p_fact_amount8_arr => l_out_amount8_arr
1627 ,p_fact_qty1_arr => l_out_qty1_arr
1628 ,p_fact_qty2_arr => l_out_qty2_arr
1629 ,p_fact_qty3_arr => l_out_qty3_arr
1630 ,p_fact_labor_Qty1_arr => l_out_labor_qty1_arr
1631 ,p_fact_equip_Qty1_arr => l_out_equip_qty1_arr
1632 ,p_fact_labor_Qty2_arr => l_out_labor_qty2_arr
1633 ,p_fact_equip_Qty2_arr => l_out_equip_qty2_arr
1634 ,p_fact_labor_Qty3_arr => l_out_labor_qty3_arr
1635 ,p_fact_equip_Qty3_arr => l_out_equip_qty3_arr
1636 ,p_fact_bill_labor_Qty1_arr => l_out_bill_labor_Qty1_arr
1637 ,p_fact_bill_equip_Qty1_arr => l_out_bill_equip_Qty1_arr
1638 ,p_fact_bill_labor_Qty2_arr => l_out_bill_labor_Qty2_arr
1639 ,p_fact_bill_equip_Qty2_arr => l_out_bill_equip_Qty2_arr
1640 ,p_fact_bill_labor_Qty3_arr => l_out_bill_labor_Qty3_arr
1641 ,p_fact_bill_equip_Qty3_arr => l_out_bill_equip_Qty3_arr
1642 ,p_fact_bill_amt1_arr => l_out_bill_amt1_arr
1643 ,p_fact_bill_amt2_arr => l_out_bill_amt2_arr
1644 ,p_fact_bill_labor_amt1_arr => l_out_bill_labor_amt1_arr
1645 ,p_fact_bill_labor_amt2_arr => l_out_bill_labor_amt2_arr
1646 ,p_fact_equip_amt1_arr => l_out_equip_amt1_arr
1647 ,p_fact_equip_amt2_arr => l_out_equip_amt2_arr
1648 ,p_fact_equip_amt3_arr => l_out_equip_amt3_arr
1649 ,p_fact_equip_amt4_arr => l_out_equip_amt4_arr
1650 ,p_fact_equip_amt5_arr => l_out_equip_amt5_arr
1651 ,p_fact_equip_amt6_arr => l_out_equip_amt6_arr
1652 ,p_fact_equip_amt7_arr => l_out_equip_amt7_arr
1653 ,p_fact_equip_amt8_arr => l_out_equip_amt8_arr
1654 ,p_fact_capit_amt1_arr => l_out_capit_amt1_arr
1655 ,p_fact_capit_amt2_arr => l_out_capit_amt2_arr
1656 ,p_fact_labor_amt1_arr => l_out_labor_amt1_arr
1657 ,p_fact_labor_amt2_arr => l_out_labor_amt2_arr
1658 ,p_fact_labor_amt3_arr => l_out_labor_amt3_arr
1659 ,p_fact_labor_amt4_arr => l_out_labor_amt4_arr
1660 ,p_fact_labor_amt5_arr => l_out_labor_amt5_arr
1661 ,p_fact_labor_amt6_arr => l_out_labor_amt6_arr
1662 ,p_fact_labor_amt7_arr => l_out_labor_amt7_arr
1663 ,p_fact_labor_amt8_arr => l_out_labor_amt8_arr
1664 ,x_return_status => l_return_status
1665 ,x_msg_count => l_msg_count
1666 ,x_msg_data => l_msg_data
1667 );
1668
1669
1670 -- for line level totals of CBS slice
1671
1672 if ( p_fact_slice in ( 'CBS' , 'WBS_TO_CBS' ) ) then
1673
1674
1675 l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
1676 l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
1677
1678 l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
1679
1680 l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
1681 l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
1682 l_out_amount7_arr.delete;l_out_amount8_arr.delete;
1683
1684
1685 l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
1686 l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
1687 l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
1688 l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
1689 l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
1690 l_out_labor_amt2_arr.delete;
1691
1692 l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
1693 l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
1694
1695
1696 l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
1697 l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
1698 l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
1699
1700 l_line_task_level_arr.delete; -- satya
1701 ppr_log(' l_task_level_arr '||l_task_level_arr.count);
1702 IF l_task_level_arr.count > 0 then
1703 FOR i in l_task_level_arr.first..l_task_level_arr.last loop
1704
1708 END IF;
1705 l_line_task_level_arr(i) := 1;
1706
1707 end loop;
1709
1710 ppr_log(' Line level BEFORE ROLLUP_AMOUNTS l_task_arr.COUNT ... '||l_task_arr.COUNT,0);
1711 rollup_amounts (
1712 p_billable_flag_tab => l_billable_flag_arr
1713 ,p_object_id_tab => l_task_arr
1714 --,p_wbs_level_tab => l_task_level_arr
1715 ,p_wbs_level_tab => l_line_task_level_arr
1716 ,p_connect_path_tab => l_line_connect_path_arr
1717 ,p_leaf_node_tab => l_leaf_node_arr
1718 ,p_resource_class_tab => l_res_class_arr
1719 ,p_amount1_tab => l_prj_amount1_arr
1720 ,p_amount2_tab => l_prj_amount2_arr
1721 ,p_amount3_tab => l_prj_amount3_arr
1722 ,p_amount4_tab => l_prj_amount4_arr
1723 ,p_amount5_tab => l_prj_amount5_arr
1724 ,p_amount6_tab => l_prj_amount6_arr
1725 ,p_amount7_tab => l_prj_amount7_arr
1726 ,p_amount8_tab => l_prj_amount8_arr
1727 ,p_qty1_tab => l_qty1_arr
1728 ,p_qty2_tab => l_qty2_arr
1729 ,p_qty3_tab => l_qty3_arr
1730 ,p_out_object_index_tab => l_out_task_index_arr
1731 ,p_out_object_id_tab => l_out_task_arr
1732 ,p_out_amount1_tab => l_out_amount1_arr
1733 ,p_out_amount2_tab => l_out_amount2_arr
1734 ,p_out_amount3_tab => l_out_amount3_arr
1735 ,p_out_amount4_tab => l_out_amount4_arr
1736 ,p_out_amount5_tab => l_out_amount5_arr
1737 ,p_out_amount6_tab => l_out_amount6_arr
1738 ,p_out_amount7_tab => l_out_amount7_arr
1739 ,p_out_amount8_tab => l_out_amount8_arr
1740 ,p_out_qty1_tab => l_out_qty1_arr
1741 ,p_out_qty2_tab => l_out_qty2_arr
1742 ,p_out_qty3_tab => l_out_qty3_arr
1743 ,p_out_labor_Qty1_tab => l_out_labor_qty1_arr
1744 ,p_out_equip_Qty1_tab => l_out_equip_qty1_arr
1745 ,p_out_labor_Qty2_tab => l_out_labor_qty2_arr
1746 ,p_out_equip_Qty2_tab => l_out_equip_qty2_arr
1747 ,p_out_labor_Qty3_tab => l_out_labor_qty3_arr
1748 ,p_out_equip_Qty3_tab => l_out_equip_qty3_arr
1749 ,p_out_bill_labor_Qty1_tab => l_out_bill_labor_Qty1_arr
1750 ,p_out_bill_equip_Qty1_tab => l_out_bill_equip_Qty1_arr
1751 ,p_out_bill_labor_Qty2_tab => l_out_bill_labor_Qty2_arr
1752 ,p_out_bill_equip_Qty2_tab => l_out_bill_equip_Qty2_arr
1753 ,p_out_bill_labor_Qty3_tab => l_out_bill_labor_Qty3_arr
1754 ,p_out_bill_equip_Qty3_tab => l_out_bill_equip_Qty3_arr
1755 ,p_out_bill_amt1_tab => l_out_bill_amt1_arr
1756 ,p_out_bill_amt2_tab => l_out_bill_amt2_arr
1757 ,p_out_bill_labor_amt1_tab => l_out_bill_labor_amt1_arr
1758 ,p_out_bill_labor_amt2_tab => l_out_bill_labor_amt2_arr
1759 ,p_out_equip_amt1_tab => l_out_equip_amt1_arr
1760 ,p_out_equip_amt2_tab => l_out_equip_amt2_arr
1761 ,p_out_equip_amt3_tab => l_out_equip_amt3_arr
1762 ,p_out_equip_amt4_tab => l_out_equip_amt4_arr
1763 ,p_out_equip_amt5_tab => l_out_equip_amt5_arr
1764 ,p_out_equip_amt6_tab => l_out_equip_amt6_arr
1765 ,p_out_equip_amt7_tab => l_out_equip_amt7_arr
1766 ,p_out_equip_amt8_tab => l_out_equip_amt8_arr
1767 ,p_out_capit_amt1_tab => l_out_capit_amt1_arr
1768 ,p_out_capit_amt2_tab => l_out_capit_amt2_arr
1769 ,p_out_labor_amt1_tab => l_out_labor_amt1_arr
1770 ,p_out_labor_amt2_tab => l_out_labor_amt2_arr
1771 ,p_out_labor_amt3_tab => l_out_labor_amt3_arr
1772 ,p_out_labor_amt4_tab => l_out_labor_amt4_arr
1773 ,p_out_labor_amt5_tab => l_out_labor_amt5_arr
1774 ,p_out_labor_amt6_tab => l_out_labor_amt6_arr
1775 ,p_out_labor_amt7_tab => l_out_labor_amt7_arr
1776 ,p_out_labor_amt8_tab => l_out_labor_amt8_arr
1777 ,x_return_status => l_return_status
1778 ,x_msg_count => l_msg_count
1779 ,x_msg_data => l_msg_data );
1780
1781
1782
1783
1784 ppr_log(' Line level after api l_out_task_arr.count ... '||l_out_task_arr.count,0);
1785
1786
1787 ppr_log(' Line level AFTER ROLLUP COUNT '||l_out_equip_amt1_arr.count,0);
1788
1789
1790
1791 G_RBS_AGGR_LEVEL :='L';
1792 G_WBS_ROLLUP_FLAG := p_wbs_rollup_flag ; --16767868
1793 G_PRG_ROLLUP_FLAG := 'N';
1794
1795 merge_into_fact (
1796 p_calling_mode => p_calling_mode
1797 --,p_max_msg_count IN NUMBER default NULL
1798 ,p_fact_slice => p_fact_slice
1799 -- ,p_debug_mode IN VARCHAR2 default 'N'
1800 ,p_project_id => l_project_id
1801 ,p_budget_version_id => l_budget_version_id
1802 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
1803 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
1804 ,p_object_id => l_object_id
1805 ,p_3_level_object_id => l_3_level_object_id
1806 ,p_3_level_version_id => l_3_level_version_id
1807 ,p_currency_code => l_project_currency
1808 ,p_fact_task_arr => l_out_task_arr
1809 ,p_fact_task_index_arr => l_out_task_index_arr
1810 ,p_fact_amount1_arr => l_out_amount1_arr
1811 ,p_fact_amount2_arr => l_out_amount2_arr
1812 ,p_fact_amount3_arr => l_out_amount3_arr
1813 ,p_fact_amount4_arr => l_out_amount4_arr
1814 ,p_fact_amount5_arr => l_out_amount5_arr
1815 ,p_fact_amount6_arr => l_out_amount6_arr
1816 ,p_fact_amount7_arr => l_out_amount7_arr
1817 ,p_fact_amount8_arr => l_out_amount8_arr
1818 ,p_fact_qty1_arr => l_out_qty1_arr
1819 ,p_fact_qty2_arr => l_out_qty2_arr
1820 ,p_fact_qty3_arr => l_out_qty3_arr
1821 ,p_fact_labor_Qty1_arr => l_out_labor_qty1_arr
1822 ,p_fact_equip_Qty1_arr => l_out_equip_qty1_arr
1823 ,p_fact_labor_Qty2_arr => l_out_labor_qty2_arr
1824 ,p_fact_equip_Qty2_arr => l_out_equip_qty2_arr
1828 ,p_fact_bill_equip_Qty1_arr => l_out_bill_equip_Qty1_arr
1825 ,p_fact_labor_Qty3_arr => l_out_labor_qty3_arr
1826 ,p_fact_equip_Qty3_arr => l_out_equip_qty3_arr
1827 ,p_fact_bill_labor_Qty1_arr => l_out_bill_labor_Qty1_arr
1829 ,p_fact_bill_labor_Qty2_arr => l_out_bill_labor_Qty2_arr
1830 ,p_fact_bill_equip_Qty2_arr => l_out_bill_equip_Qty2_arr
1831 ,p_fact_bill_labor_Qty3_arr => l_out_bill_labor_Qty3_arr
1832 ,p_fact_bill_equip_Qty3_arr => l_out_bill_equip_Qty3_arr
1833 ,p_fact_bill_amt1_arr => l_out_bill_amt1_arr
1834 ,p_fact_bill_amt2_arr => l_out_bill_amt2_arr
1835 ,p_fact_bill_labor_amt1_arr => l_out_bill_labor_amt1_arr
1836 ,p_fact_bill_labor_amt2_arr => l_out_bill_labor_amt2_arr
1837 ,p_fact_equip_amt1_arr => l_out_equip_amt1_arr
1838 ,p_fact_equip_amt2_arr => l_out_equip_amt2_arr
1839 ,p_fact_equip_amt3_arr => l_out_equip_amt3_arr
1840 ,p_fact_equip_amt4_arr => l_out_equip_amt4_arr
1841 ,p_fact_equip_amt5_arr => l_out_equip_amt5_arr
1842 ,p_fact_equip_amt6_arr => l_out_equip_amt6_arr
1843 ,p_fact_equip_amt7_arr => l_out_equip_amt7_arr
1844 ,p_fact_equip_amt8_arr => l_out_equip_amt8_arr
1845 ,p_fact_capit_amt1_arr => l_out_capit_amt1_arr
1846 ,p_fact_capit_amt2_arr => l_out_capit_amt2_arr
1847 ,p_fact_labor_amt1_arr => l_out_labor_amt1_arr
1848 ,p_fact_labor_amt2_arr => l_out_labor_amt2_arr
1849 ,p_fact_labor_amt3_arr => l_out_labor_amt3_arr
1850 ,p_fact_labor_amt4_arr => l_out_labor_amt4_arr
1851 ,p_fact_labor_amt5_arr => l_out_labor_amt5_arr
1852 ,p_fact_labor_amt6_arr => l_out_labor_amt6_arr
1853 ,p_fact_labor_amt7_arr => l_out_labor_amt7_arr
1854 ,p_fact_labor_amt8_arr => l_out_labor_amt8_arr
1855 ,x_return_status => l_return_status
1856 ,x_msg_count => l_msg_count
1857 ,x_msg_data => l_msg_data
1858 );
1859
1860
1861
1862 end if;
1863 -- RAW_COST, BRDN_COST, REVENUE
1864 -- l_out_amount1_arr(i),l_out_amount2_arr(i),l_out_amount3_arr(i)
1865 -- LABOR_HRS, LABOR_REVENUE, EQUIPMENT_HOURS,
1866 -- l_out_labor_qty1_arr(i), 0, l_out_equip_qty1_arr(i),
1867 -- , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_BRDN_COST, ACT_RAW_COST
1868 --- ,l_out_labor_qty1_arr(i),l_out_equip_qty2_arr(i),l_out_amount5_arr(i),l_out_amount4_arr(i)
1869 ---
1870 ---, ACT_REVENUE, ETC_LABOR_HRS, ETC_EQUIP_HRS,
1871 --- ,l_out_amount6_arr(i),l_out_labor_qty3_arr(i),l_out_equip_qty3_arr(i)
1872 ----
1873 ----ETC_BRDN_COST, ETC_RAW_COST,
1874 ---l_out_amount8_arr(i), l_out_amount7_arr(i)
1875
1876
1877 -- END Project Currency Process
1878
1879
1880 If ( l_project_currency <> l_functional_currency ) then /* Project_currency <> functional_currency */
1881
1882 ppr_log(' SECOND RUN......... ',0);
1883
1884
1885 l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
1886 l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
1887
1888 l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
1889
1890 l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
1891 l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
1892 l_out_amount7_arr.delete;l_out_amount8_arr.delete;
1893
1894
1895 l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
1896 l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
1897 l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
1898 l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
1899 l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
1900 l_out_labor_amt2_arr.delete;
1901
1902 l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
1903 l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
1904
1905
1906 l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
1907 l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
1908 l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
1909
1910 rollup_amounts (
1911 p_billable_flag_tab => l_billable_flag_arr
1912 ,p_object_id_tab => l_task_arr
1913 ,p_wbs_level_tab => l_task_level_arr
1914 ,p_connect_path_tab => l_connect_path_arr
1915 ,p_leaf_node_tab => l_leaf_node_arr
1916 ,p_resource_class_tab => l_res_class_arr
1917 ,p_amount1_tab => l_func_amount1_arr
1918 ,p_amount2_tab => l_func_amount2_arr
1919 ,p_amount3_tab => l_func_amount3_arr
1920 ,p_amount4_tab => l_func_amount4_arr
1921 ,p_amount5_tab => l_func_amount5_arr
1922 ,p_amount6_tab => l_func_amount6_arr
1923 ,p_amount7_tab => l_func_amount7_arr
1924 ,p_amount8_tab => l_func_amount8_arr
1925 ,p_qty1_tab => l_qty1_arr
1926 ,p_qty2_tab => l_qty2_arr
1927 ,p_qty3_tab => l_qty3_arr
1928 ,p_out_object_index_tab => l_out_task_index_arr
1929 ,p_out_object_id_tab => l_out_task_arr
1930 ,p_out_amount1_tab => l_out_amount1_arr
1931 ,p_out_amount2_tab => l_out_amount2_arr
1932 ,p_out_amount3_tab => l_out_amount3_arr
1933 ,p_out_amount4_tab => l_out_amount4_arr
1934 ,p_out_amount5_tab => l_out_amount5_arr
1935 ,p_out_amount6_tab => l_out_amount6_arr
1936 ,p_out_amount7_tab => l_out_amount7_arr
1937 ,p_out_amount8_tab => l_out_amount8_arr
1938 ,p_out_qty1_tab => l_out_qty1_arr
1939 ,p_out_qty2_tab => l_out_qty2_arr
1940 ,p_out_qty3_tab => l_out_qty3_arr
1941 ,p_out_labor_Qty1_tab => l_out_labor_qty1_arr
1945 ,p_out_labor_Qty3_tab => l_out_labor_qty3_arr
1942 ,p_out_equip_Qty1_tab => l_out_equip_qty1_arr
1943 ,p_out_labor_Qty2_tab => l_out_labor_qty2_arr
1944 ,p_out_equip_Qty2_tab => l_out_equip_qty2_arr
1946 ,p_out_equip_Qty3_tab => l_out_equip_qty3_arr
1947 ,p_out_bill_labor_Qty1_tab => l_out_bill_labor_Qty1_arr
1948 ,p_out_bill_equip_Qty1_tab => l_out_bill_equip_Qty1_arr
1949 ,p_out_bill_labor_Qty2_tab => l_out_bill_labor_Qty2_arr
1950 ,p_out_bill_equip_Qty2_tab => l_out_bill_equip_Qty2_arr
1951 ,p_out_bill_labor_Qty3_tab => l_out_bill_labor_Qty3_arr
1952 ,p_out_bill_equip_Qty3_tab => l_out_bill_equip_Qty3_arr
1953 ,p_out_bill_amt1_tab => l_out_bill_amt1_arr
1954 ,p_out_bill_amt2_tab => l_out_bill_amt2_arr
1955 ,p_out_bill_labor_amt1_tab => l_out_bill_labor_amt1_arr
1956 ,p_out_bill_labor_amt2_tab => l_out_bill_labor_amt2_arr
1957 ,p_out_equip_amt1_tab => l_out_equip_amt1_arr
1958 ,p_out_equip_amt2_tab => l_out_equip_amt2_arr
1959 ,p_out_equip_amt3_tab => l_out_equip_amt3_arr
1960 ,p_out_equip_amt4_tab => l_out_equip_amt4_arr
1961 ,p_out_equip_amt5_tab => l_out_equip_amt5_arr
1962 ,p_out_equip_amt6_tab => l_out_equip_amt6_arr
1963 ,p_out_equip_amt7_tab => l_out_equip_amt7_arr
1964 ,p_out_equip_amt8_tab => l_out_equip_amt8_arr
1965 ,p_out_capit_amt1_tab => l_out_capit_amt1_arr
1966 ,p_out_capit_amt2_tab => l_out_capit_amt2_arr
1967 ,p_out_labor_amt1_tab => l_out_labor_amt1_arr
1968 ,p_out_labor_amt2_tab => l_out_labor_amt2_arr
1969 ,p_out_labor_amt3_tab => l_out_labor_amt3_arr
1970 ,p_out_labor_amt4_tab => l_out_labor_amt4_arr
1971 ,p_out_labor_amt5_tab => l_out_labor_amt5_arr
1972 ,p_out_labor_amt6_tab => l_out_labor_amt6_arr
1973 ,p_out_labor_amt7_tab => l_out_labor_amt7_arr
1974 ,p_out_labor_amt8_tab => l_out_labor_amt8_arr
1975 ,x_return_status => l_return_status
1976 ,x_msg_count => l_msg_count
1977 ,x_msg_data => l_msg_data );
1978
1979
1980
1981 ppr_log(' AFTER SECOND RUN......... ',0);
1982
1983
1984 G_RBS_AGGR_LEVEL :='T';
1985 G_WBS_ROLLUP_FLAG := p_wbs_rollup_flag ; --16767868
1986 G_PRG_ROLLUP_FLAG := 'N';
1987
1988 merge_into_fact (
1989 p_calling_mode => p_calling_mode
1990 --,p_max_msg_count IN NUMBER default NULL
1991 ,p_fact_slice => p_fact_slice
1992 -- ,p_debug_mode IN VARCHAR2 default 'N'
1993 ,p_project_id => l_project_id
1994 ,p_budget_version_id => l_budget_version_id
1995 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
1996 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
1997 ,p_object_id => l_object_id
1998 ,p_3_level_object_id => l_3_level_object_id
1999 ,p_3_level_version_id => l_3_level_version_id
2000 ,p_currency_code => l_project_currency
2001 ,p_fact_task_arr => l_out_task_arr
2002 ,p_fact_task_index_arr => l_out_task_index_arr
2003 ,p_fact_amount1_arr => l_out_amount1_arr
2004 ,p_fact_amount2_arr => l_out_amount2_arr
2005 ,p_fact_amount3_arr => l_out_amount3_arr
2006 ,p_fact_amount4_arr => l_out_amount4_arr
2007 ,p_fact_amount5_arr => l_out_amount5_arr
2008 ,p_fact_amount6_arr => l_out_amount6_arr
2009 ,p_fact_amount7_arr => l_out_amount7_arr
2010 ,p_fact_amount8_arr => l_out_amount8_arr
2011 ,p_fact_qty1_arr => l_out_qty1_arr
2012 ,p_fact_qty2_arr => l_out_qty2_arr
2013 ,p_fact_qty3_arr => l_out_qty3_arr
2014 ,p_fact_labor_Qty1_arr => l_out_labor_qty1_arr
2015 ,p_fact_equip_Qty1_arr => l_out_equip_qty1_arr
2016 ,p_fact_labor_Qty2_arr => l_out_labor_qty2_arr
2017 ,p_fact_equip_Qty2_arr => l_out_equip_qty2_arr
2018 ,p_fact_labor_Qty3_arr => l_out_labor_qty3_arr
2019 ,p_fact_equip_Qty3_arr => l_out_equip_qty3_arr
2020 ,p_fact_bill_labor_Qty1_arr => l_out_bill_labor_Qty1_arr
2021 ,p_fact_bill_equip_Qty1_arr => l_out_bill_equip_Qty1_arr
2022 ,p_fact_bill_labor_Qty2_arr => l_out_bill_labor_Qty2_arr
2023 ,p_fact_bill_equip_Qty2_arr => l_out_bill_equip_Qty2_arr
2024 ,p_fact_bill_labor_Qty3_arr => l_out_bill_labor_Qty3_arr
2025 ,p_fact_bill_equip_Qty3_arr => l_out_bill_equip_Qty3_arr
2026 ,p_fact_bill_amt1_arr => l_out_bill_amt1_arr
2027 ,p_fact_bill_amt2_arr => l_out_bill_amt2_arr
2028 ,p_fact_bill_labor_amt1_arr => l_out_bill_labor_amt1_arr
2029 ,p_fact_bill_labor_amt2_arr => l_out_bill_labor_amt2_arr
2030 ,p_fact_equip_amt1_arr => l_out_equip_amt1_arr
2031 ,p_fact_equip_amt2_arr => l_out_equip_amt2_arr
2032 ,p_fact_equip_amt3_arr => l_out_equip_amt3_arr
2033 ,p_fact_equip_amt4_arr => l_out_equip_amt4_arr
2034 ,p_fact_equip_amt5_arr => l_out_equip_amt5_arr
2035 ,p_fact_equip_amt6_arr => l_out_equip_amt6_arr
2036 ,p_fact_equip_amt7_arr => l_out_equip_amt7_arr
2037 ,p_fact_equip_amt8_arr => l_out_equip_amt8_arr
2038 ,p_fact_capit_amt1_arr => l_out_capit_amt1_arr
2039 ,p_fact_capit_amt2_arr => l_out_capit_amt2_arr
2040 ,p_fact_labor_amt1_arr => l_out_labor_amt1_arr
2041 ,p_fact_labor_amt2_arr => l_out_labor_amt2_arr
2042 ,p_fact_labor_amt3_arr => l_out_labor_amt3_arr
2043 ,p_fact_labor_amt4_arr => l_out_labor_amt4_arr
2044 ,p_fact_labor_amt5_arr => l_out_labor_amt5_arr
2045 ,p_fact_labor_amt6_arr => l_out_labor_amt6_arr
2046 ,p_fact_labor_amt7_arr => l_out_labor_amt7_arr
2047 ,p_fact_labor_amt8_arr => l_out_labor_amt8_arr
2048 ,x_return_status => l_return_status
2049 ,x_msg_count => l_msg_count
2050 ,x_msg_data => l_msg_data
2051 );
2052
2053
2054
2055 -- line level totals
2056
2057 if ( p_fact_slice in ('CBS', 'WBS_TO_CBS')) then
2058
2059
2063 l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
2060 l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
2061 l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
2062
2064
2065 l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
2066 l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
2067 l_out_amount7_arr.delete;l_out_amount8_arr.delete;
2068
2069
2070 l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
2071 l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
2072 l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
2073 l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
2074 l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
2075 l_out_labor_amt2_arr.delete;
2076
2077 l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
2078 l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
2079
2080
2081 l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
2082 l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
2083 l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
2084
2085 l_line_task_level_arr.delete; -- satya
2086 IF l_task_level_arr.count > 0 then
2087 FOR i in l_task_level_arr.first..l_task_level_arr.last loop
2088
2089 l_line_task_level_arr(i) := 1;
2090
2091 end loop;
2092 END IF;
2093
2094
2095 rollup_amounts (
2096 p_billable_flag_tab => l_billable_flag_arr
2097 ,p_object_id_tab => l_task_arr
2098 --,p_wbs_level_tab => l_task_level_arr
2099 ,p_wbs_level_tab => l_line_task_level_arr
2100 ,p_connect_path_tab => l_line_connect_path_arr
2101 ,p_leaf_node_tab => l_leaf_node_arr
2102 ,p_resource_class_tab => l_res_class_arr
2103 ,p_amount1_tab => l_func_amount1_arr
2104 ,p_amount2_tab => l_func_amount2_arr
2105 ,p_amount3_tab => l_func_amount3_arr
2106 ,p_amount4_tab => l_func_amount4_arr
2107 ,p_amount5_tab => l_func_amount5_arr
2108 ,p_amount6_tab => l_func_amount6_arr
2109 ,p_amount7_tab => l_func_amount7_arr
2110 ,p_amount8_tab => l_func_amount8_arr
2111 ,p_qty1_tab => l_qty1_arr
2112 ,p_qty2_tab => l_qty2_arr
2113 ,p_qty3_tab => l_qty3_arr
2114 ,p_out_object_index_tab => l_out_task_index_arr
2115 ,p_out_object_id_tab => l_out_task_arr
2116 ,p_out_amount1_tab => l_out_amount1_arr
2117 ,p_out_amount2_tab => l_out_amount2_arr
2118 ,p_out_amount3_tab => l_out_amount3_arr
2119 ,p_out_amount4_tab => l_out_amount4_arr
2120 ,p_out_amount5_tab => l_out_amount5_arr
2121 ,p_out_amount6_tab => l_out_amount6_arr
2122 ,p_out_amount7_tab => l_out_amount7_arr
2123 ,p_out_amount8_tab => l_out_amount8_arr
2124 ,p_out_qty1_tab => l_out_qty1_arr
2125 ,p_out_qty2_tab => l_out_qty2_arr
2126 ,p_out_qty3_tab => l_out_qty3_arr
2127 ,p_out_labor_Qty1_tab => l_out_labor_qty1_arr
2128 ,p_out_equip_Qty1_tab => l_out_equip_qty1_arr
2129 ,p_out_labor_Qty2_tab => l_out_labor_qty2_arr
2130 ,p_out_equip_Qty2_tab => l_out_equip_qty2_arr
2131 ,p_out_labor_Qty3_tab => l_out_labor_qty3_arr
2132 ,p_out_equip_Qty3_tab => l_out_equip_qty3_arr
2133 ,p_out_bill_labor_Qty1_tab => l_out_bill_labor_Qty1_arr
2134 ,p_out_bill_equip_Qty1_tab => l_out_bill_equip_Qty1_arr
2135 ,p_out_bill_labor_Qty2_tab => l_out_bill_labor_Qty2_arr
2136 ,p_out_bill_equip_Qty2_tab => l_out_bill_equip_Qty2_arr
2137 ,p_out_bill_labor_Qty3_tab => l_out_bill_labor_Qty3_arr
2138 ,p_out_bill_equip_Qty3_tab => l_out_bill_equip_Qty3_arr
2139 ,p_out_bill_amt1_tab => l_out_bill_amt1_arr
2140 ,p_out_bill_amt2_tab => l_out_bill_amt2_arr
2141 ,p_out_bill_labor_amt1_tab => l_out_bill_labor_amt1_arr
2142 ,p_out_bill_labor_amt2_tab => l_out_bill_labor_amt2_arr
2143 ,p_out_equip_amt1_tab => l_out_equip_amt1_arr
2144 ,p_out_equip_amt2_tab => l_out_equip_amt2_arr
2145 ,p_out_equip_amt3_tab => l_out_equip_amt3_arr
2146 ,p_out_equip_amt4_tab => l_out_equip_amt4_arr
2147 ,p_out_equip_amt5_tab => l_out_equip_amt5_arr
2148 ,p_out_equip_amt6_tab => l_out_equip_amt6_arr
2149 ,p_out_equip_amt7_tab => l_out_equip_amt7_arr
2150 ,p_out_equip_amt8_tab => l_out_equip_amt8_arr
2151 ,p_out_capit_amt1_tab => l_out_capit_amt1_arr
2152 ,p_out_capit_amt2_tab => l_out_capit_amt2_arr
2153 ,p_out_labor_amt1_tab => l_out_labor_amt1_arr
2154 ,p_out_labor_amt2_tab => l_out_labor_amt2_arr
2155 ,p_out_labor_amt3_tab => l_out_labor_amt3_arr
2156 ,p_out_labor_amt4_tab => l_out_labor_amt4_arr
2157 ,p_out_labor_amt5_tab => l_out_labor_amt5_arr
2158 ,p_out_labor_amt6_tab => l_out_labor_amt6_arr
2159 ,p_out_labor_amt7_tab => l_out_labor_amt7_arr
2160 ,p_out_labor_amt8_tab => l_out_labor_amt8_arr
2161 ,x_return_status => l_return_status
2162 ,x_msg_count => l_msg_count
2163 ,x_msg_data => l_msg_data );
2164
2165
2166
2167 ppr_log(' AFTER SECOND RUN......... ',0);
2168
2169
2170 G_RBS_AGGR_LEVEL :='L';
2171 G_WBS_ROLLUP_FLAG := p_wbs_rollup_flag ; --16767868
2172 G_PRG_ROLLUP_FLAG := 'N';
2173
2174 merge_into_fact (
2175 p_calling_mode => p_calling_mode
2176 --,p_max_msg_count IN NUMBER default NULL
2177 ,p_fact_slice => p_fact_slice
2178 -- ,p_debug_mode IN VARCHAR2 default 'N'
2182 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
2179 ,p_project_id => l_project_id
2180 ,p_budget_version_id => l_budget_version_id
2181 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
2183 ,p_object_id => l_object_id
2184 ,p_3_level_object_id => l_3_level_object_id
2185 ,p_3_level_version_id => l_3_level_version_id
2186 ,p_currency_code => l_project_currency
2187 ,p_fact_task_arr => l_out_task_arr
2188 ,p_fact_task_index_arr => l_out_task_index_arr
2189 ,p_fact_amount1_arr => l_out_amount1_arr
2190 ,p_fact_amount2_arr => l_out_amount2_arr
2191 ,p_fact_amount3_arr => l_out_amount3_arr
2192 ,p_fact_amount4_arr => l_out_amount4_arr
2193 ,p_fact_amount5_arr => l_out_amount5_arr
2194 ,p_fact_amount6_arr => l_out_amount6_arr
2195 ,p_fact_amount7_arr => l_out_amount7_arr
2196 ,p_fact_amount8_arr => l_out_amount8_arr
2197 ,p_fact_qty1_arr => l_out_qty1_arr
2198 ,p_fact_qty2_arr => l_out_qty2_arr
2199 ,p_fact_qty3_arr => l_out_qty3_arr
2200 ,p_fact_labor_Qty1_arr => l_out_labor_qty1_arr
2201 ,p_fact_equip_Qty1_arr => l_out_equip_qty1_arr
2202 ,p_fact_labor_Qty2_arr => l_out_labor_qty2_arr
2203 ,p_fact_equip_Qty2_arr => l_out_equip_qty2_arr
2204 ,p_fact_labor_Qty3_arr => l_out_labor_qty3_arr
2205 ,p_fact_equip_Qty3_arr => l_out_equip_qty3_arr
2206 ,p_fact_bill_labor_Qty1_arr => l_out_bill_labor_Qty1_arr
2207 ,p_fact_bill_equip_Qty1_arr => l_out_bill_equip_Qty1_arr
2208 ,p_fact_bill_labor_Qty2_arr => l_out_bill_labor_Qty2_arr
2209 ,p_fact_bill_equip_Qty2_arr => l_out_bill_equip_Qty2_arr
2210 ,p_fact_bill_labor_Qty3_arr => l_out_bill_labor_Qty3_arr
2211 ,p_fact_bill_equip_Qty3_arr => l_out_bill_equip_Qty3_arr
2212 ,p_fact_bill_amt1_arr => l_out_bill_amt1_arr
2213 ,p_fact_bill_amt2_arr => l_out_bill_amt2_arr
2214 ,p_fact_bill_labor_amt1_arr => l_out_bill_labor_amt1_arr
2215 ,p_fact_bill_labor_amt2_arr => l_out_bill_labor_amt2_arr
2216 ,p_fact_equip_amt1_arr => l_out_equip_amt1_arr
2217 ,p_fact_equip_amt2_arr => l_out_equip_amt2_arr
2218 ,p_fact_equip_amt3_arr => l_out_equip_amt3_arr
2219 ,p_fact_equip_amt4_arr => l_out_equip_amt4_arr
2220 ,p_fact_equip_amt5_arr => l_out_equip_amt5_arr
2221 ,p_fact_equip_amt6_arr => l_out_equip_amt6_arr
2222 ,p_fact_equip_amt7_arr => l_out_equip_amt7_arr
2223 ,p_fact_equip_amt8_arr => l_out_equip_amt8_arr
2224 ,p_fact_capit_amt1_arr => l_out_capit_amt1_arr
2225 ,p_fact_capit_amt2_arr => l_out_capit_amt2_arr
2226 ,p_fact_labor_amt1_arr => l_out_labor_amt1_arr
2227 ,p_fact_labor_amt2_arr => l_out_labor_amt2_arr
2228 ,p_fact_labor_amt3_arr => l_out_labor_amt3_arr
2229 ,p_fact_labor_amt4_arr => l_out_labor_amt4_arr
2230 ,p_fact_labor_amt5_arr => l_out_labor_amt5_arr
2231 ,p_fact_labor_amt6_arr => l_out_labor_amt6_arr
2232 ,p_fact_labor_amt7_arr => l_out_labor_amt7_arr
2233 ,p_fact_labor_amt8_arr => l_out_labor_amt8_arr
2234 ,x_return_status => l_return_status
2235 ,x_msg_count => l_msg_count
2236 ,x_msg_data => l_msg_data
2237 );
2238
2239
2240
2241 end if ;
2242
2243
2244
2245
2246 end if; /* Project_currency <> functional_currency */
2247
2248
2249 -- commit;
2250
2251 end plan_rollup ;
2252
2253
2254 procedure rollup_amounts (
2255 p_api_version IN NUMBER default 1.0
2256 ,p_commit IN VARCHAR2 default 'N'
2257 ,p_calling_mode IN VARCHAR2 default 'CREATE'
2258 ,p_debug_mode IN VARCHAR2 default 'N'
2259 ,p_max_msg_count IN NUMBER default NULL
2260 ,p_billable_flag_tab IN PA_PLSQL_DATATYPES.Char1TabTyp
2261 ,p_resource_class_tab IN PA_PLSQL_DATATYPES.Char30TabTyp
2262 ,p_object_id_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2263 ,p_wbs_level_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2264 ,p_connect_path_tab IN PA_PLSQL_DATATYPES.Char240TabTyp
2265 ,p_leaf_node_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2266 ,p_amount1_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2267 ,p_amount2_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2268 ,p_amount3_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2269 ,p_amount4_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2270 ,p_amount5_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2271 ,p_amount6_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2272 ,p_amount7_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2273 ,p_amount8_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2274 ,p_qty1_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2275 ,p_qty2_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2276 ,p_qty3_tab IN PA_PLSQL_DATATYPES.NumTabTyp
2277 ,p_out_object_index_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2278 ,p_out_object_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2279 ,p_out_amount1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2280 ,p_out_amount2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2281 ,p_out_amount3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2282 ,p_out_amount4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2283 ,p_out_amount5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2284 ,p_out_amount6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2285 ,p_out_amount7_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2286 ,p_out_amount8_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2287 ,p_out_qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2288 ,p_out_qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2289 ,p_out_qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2290 ,p_out_labor_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2294 ,p_out_labor_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2291 ,p_out_equip_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2292 ,p_out_labor_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2293 ,p_out_equip_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2295 ,p_out_equip_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2296 ,p_out_bill_labor_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2297 ,p_out_bill_equip_Qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2298 ,p_out_bill_labor_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2299 ,p_out_bill_equip_Qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2300 ,p_out_bill_labor_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2301 ,p_out_bill_equip_Qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2302 ,p_out_bill_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2303 ,p_out_bill_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2304 ,p_out_bill_labor_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2305 ,p_out_bill_labor_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2306 ,p_out_equip_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2307 ,p_out_equip_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2308 ,p_out_equip_amt3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2309 ,p_out_equip_amt4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2310 ,p_out_equip_amt5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2311 ,p_out_equip_amt6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2312 ,p_out_equip_amt7_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2313 ,p_out_equip_amt8_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2314 ,p_out_capit_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2315 ,p_out_capit_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2316 ,p_out_labor_amt1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2317 ,p_out_labor_amt2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2318 ,p_out_labor_amt3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2319 ,p_out_labor_amt4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2320 ,p_out_labor_amt5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2321 ,p_out_labor_amt6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2322 ,p_out_labor_amt7_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2323 ,p_out_labor_amt8_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2324 ,x_return_status OUT NOCOPY VARCHAR2
2325 ,x_msg_count OUT NOCOPY NUMBER
2326 ,x_msg_data OUT NOCOPY VARCHAR2
2327 ) as
2328
2329 t_str varchar2(20);
2330
2331 t_id number;
2332 l_level number;
2333 p_path varchar2(240);
2334
2335
2336 l_parent_object_index PA_PLSQL_DATATYPES.NumTabTyp;
2337 l_parent_counter number ;
2338
2339 l_rollup_amount1 PA_PLSQL_DATATYPES.NumTabTyp;
2340 l_rollup_amount2 PA_PLSQL_DATATYPES.NumTabTyp;
2341 l_rollup_amount3 PA_PLSQL_DATATYPES.NumTabTyp;
2342
2343 l_rollup_amount4 PA_PLSQL_DATATYPES.NumTabTyp;
2344 l_rollup_amount5 PA_PLSQL_DATATYPES.NumTabTyp;
2345 l_rollup_amount6 PA_PLSQL_DATATYPES.NumTabTyp;
2346 l_rollup_amount7 PA_PLSQL_DATATYPES.NumTabTyp;
2347 l_rollup_amount8 PA_PLSQL_DATATYPES.NumTabTyp;
2348
2349 l_rollup_qty1 PA_PLSQL_DATATYPES.NumTabTyp;
2350 l_rollup_qty2 PA_PLSQL_DATATYPES.NumTabTyp;
2351 l_rollup_qty3 PA_PLSQL_DATATYPES.NumTabTyp;
2352
2353 l_rollup_labor_qty1 PA_PLSQL_DATATYPES.NumTabTyp;
2354 l_rollup_labor_qty2 PA_PLSQL_DATATYPES.NumTabTyp;
2355 l_rollup_labor_qty3 PA_PLSQL_DATATYPES.NumTabTyp;
2356
2357 l_rollup_equip_qty1 PA_PLSQL_DATATYPES.NumTabTyp;
2358 l_rollup_equip_qty2 PA_PLSQL_DATATYPES.NumTabTyp;
2359 l_rollup_equip_qty3 PA_PLSQL_DATATYPES.NumTabTyp;
2360
2361 l_rollup_bill_labor_Qty1 PA_PLSQL_DATATYPES.NumTabTyp;
2362 l_rollup_bill_equip_Qty1 PA_PLSQL_DATATYPES.NumTabTyp;
2363 l_rollup_bill_labor_Qty2 PA_PLSQL_DATATYPES.NumTabTyp;
2364 l_rollup_bill_equip_Qty2 PA_PLSQL_DATATYPES.NumTabTyp;
2365 l_rollup_bill_labor_Qty3 PA_PLSQL_DATATYPES.NumTabTyp;
2366 l_rollup_bill_equip_Qty3 PA_PLSQL_DATATYPES.NumTabTyp;
2367 l_rollup_bill_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
2368 l_rollup_bill_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
2369 l_rollup_bill_labor_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
2370 l_rollup_bill_labor_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
2371 l_rollup_equip_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
2372 l_rollup_equip_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
2373 l_rollup_equip_amt3 PA_PLSQL_DATATYPES.NumTabTyp;
2374 l_rollup_equip_amt4 PA_PLSQL_DATATYPES.NumTabTyp;
2375 l_rollup_equip_amt5 PA_PLSQL_DATATYPES.NumTabTyp;
2376 l_rollup_equip_amt6 PA_PLSQL_DATATYPES.NumTabTyp;
2377 l_rollup_equip_amt7 PA_PLSQL_DATATYPES.NumTabTyp;
2378 l_rollup_equip_amt8 PA_PLSQL_DATATYPES.NumTabTyp;
2379 l_rollup_capit_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
2380 l_rollup_capit_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
2381 l_rollup_labor_amt1 PA_PLSQL_DATATYPES.NumTabTyp;
2382 l_rollup_labor_amt2 PA_PLSQL_DATATYPES.NumTabTyp;
2383 l_rollup_labor_amt3 PA_PLSQL_DATATYPES.NumTabTyp;
2384 l_rollup_labor_amt4 PA_PLSQL_DATATYPES.NumTabTyp;
2385 l_rollup_labor_amt5 PA_PLSQL_DATATYPES.NumTabTyp;
2386 l_rollup_labor_amt6 PA_PLSQL_DATATYPES.NumTabTyp;
2387 l_rollup_labor_amt7 PA_PLSQL_DATATYPES.NumTabTyp;
2388 l_rollup_labor_amt8 PA_PLSQL_DATATYPES.NumTabTyp;
2389
2390
2391 begin
2392
2393 null;
2394
2395 l_parent_counter := 0;
2396
2397 ppr_log(' ROLLUP_AMOUNT : task_id count ... '||p_object_id_tab.count,0);
2398
2399
2403 ppr_log(' p_wbs_level_tab '||p_wbs_level_tab.count);
2400 FOR i IN 1..p_object_id_tab.count LOOP
2401
2402 ppr_log(' Inside p_object_id_tab loop ') ;
2404 ppr_log(' p_connect_path_tab '||p_connect_path_tab.count);
2405 l_level := p_wbs_level_tab(i);
2406 p_path := p_connect_path_tab(i);
2407
2408 ppr_log(' ROLLUP_AMOUNT : source task_id ... '||p_object_id_tab(i)||' path '||p_path||' level '||l_level||' .. amount1 .. '||p_amount1_tab(i),1);
2409 ppr_log(' ROLLUP_ETC : source task_id ... '||p_object_id_tab(i)||' path '||p_path||' level '||l_level||' .. etc .. '||p_qty3_tab(i),1);
2410
2411 FOR i_level IN 1..l_level LOOP
2412
2413 if ( instr(p_path , '/', 1, i_level) = 0 ) then
2414 t_str := null;
2415 else
2416 if ( instr(p_path , '/', 1, i_level+1) = 0 ) then
2417 t_str := substr(p_path , instr(p_path , '/', 1, i_level)+1, length(p_path ) ) ;
2418 else
2419 t_str :=substr(p_path , instr(p_path , '/', 1, i_level)+1,
2420 instr(p_path , '/', 1, i_level+1) - instr(p_path , '/', 1, i_level)- 1 ) ;
2421 end if;
2422 end if;
2423
2424 t_id := to_number(t_str);
2425
2426 ppr_log(' ROLLUP_AMOUNT : rollup_task task_id ... '||t_id ,1);
2427 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);
2428
2429 if (l_rollup_amount1.exists(t_id)) then
2430
2431 l_rollup_amount1(t_id) := l_rollup_amount1(t_id) + p_amount1_tab(i);
2432 l_rollup_amount2(t_id) := l_rollup_amount2(t_id) + p_amount2_tab(i);
2433 l_rollup_amount3(t_id) := l_rollup_amount3(t_id) + p_amount3_tab(i);
2434
2435 l_rollup_amount4(t_id) := l_rollup_amount4(t_id) + p_amount4_tab(i);
2436 l_rollup_amount5(t_id) := l_rollup_amount5(t_id) + p_amount5_tab(i);
2437 l_rollup_amount6(t_id) := l_rollup_amount6(t_id) + p_amount6_tab(i);
2438
2439 l_rollup_amount7(t_id) := l_rollup_amount7(t_id) + p_amount7_tab(i);
2440 l_rollup_amount8(t_id) := l_rollup_amount8(t_id) + p_amount8_tab(i);
2441
2442 l_rollup_qty1(t_id) := l_rollup_qty1(t_id) + p_qty1_tab(i);
2443 l_rollup_qty2(t_id) := l_rollup_qty2(t_id) + p_qty2_tab(i);
2444 l_rollup_qty3(t_id) := l_rollup_qty3(t_id) + p_qty3_tab(i);
2445
2446 ppr_log(' ROLLUP_AMOUNT : test0 ',1);
2447
2448 if ( p_resource_class_tab(i) = 'PEOPLE') then
2449
2450 l_rollup_labor_qty1(t_id) := l_rollup_labor_qty1(t_id) + p_qty1_tab(i);
2451 l_rollup_labor_qty2(t_id) := l_rollup_labor_qty2(t_id) + p_qty2_tab(i);
2452 l_rollup_labor_qty3(t_id) := l_rollup_labor_qty3(t_id) + p_qty3_tab(i);
2453
2454 ppr_log(' ROLLUP_AMOUNT : test1 ',1);
2455
2456 l_rollup_labor_amt1(t_id) := l_rollup_labor_amt1(t_id) + p_amount1_tab(i);
2457 l_rollup_labor_amt2(t_id) := l_rollup_labor_amt2(t_id) + p_amount2_tab(i);
2458 l_rollup_labor_amt3(t_id) := l_rollup_labor_amt3(t_id) + p_amount3_tab(i);
2459 l_rollup_labor_amt4(t_id) := l_rollup_labor_amt4(t_id) + p_amount4_tab(i);
2460 l_rollup_labor_amt5(t_id) := l_rollup_labor_amt5(t_id) + p_amount5_tab(i);
2461 l_rollup_labor_amt6(t_id) := l_rollup_labor_amt6(t_id) + p_amount6_tab(i);
2462 l_rollup_labor_amt7(t_id) := l_rollup_labor_amt7(t_id) + p_amount7_tab(i);
2463 l_rollup_labor_amt8(t_id) := l_rollup_labor_amt8(t_id) + p_amount8_tab(i);
2464
2465 ppr_log(' ROLLUP_AMOUNT : test2 ',1);
2466
2467 if ( p_billable_flag_tab(i) = 'Y' ) then
2468
2469 l_rollup_bill_labor_Qty1(t_id) := l_rollup_bill_labor_Qty1(t_id) + p_qty1_tab(i);
2470 l_rollup_bill_labor_Qty2(t_id) := l_rollup_bill_labor_Qty2(t_id) + p_qty2_tab(i);
2471 l_rollup_bill_labor_Qty3(t_id) := l_rollup_bill_labor_Qty3(t_id) + p_qty3_tab(i);
2472
2473 ppr_log(' ROLLUP_AMOUNT : test3 ',1);
2474
2475 l_rollup_bill_labor_amt1(t_id) := l_rollup_bill_labor_amt1(t_id) + p_amount1_tab(i);
2476 l_rollup_bill_labor_amt2(t_id) := l_rollup_bill_labor_amt2(t_id) + p_amount2_tab(i);
2477
2478 end if;
2479
2480 ppr_log(' ROLLUP_AMOUNT : test4 ',1);
2481 end if;
2482
2483 if ( p_resource_class_tab(i) = 'EQUIPMENT') then
2484 l_rollup_equip_qty1(t_id) := l_rollup_equip_qty1(t_id) + p_qty1_tab(i);
2485 l_rollup_equip_qty2(t_id) := l_rollup_equip_qty2(t_id) + p_qty2_tab(i);
2486 l_rollup_equip_qty3(t_id) := l_rollup_equip_qty3(t_id) + p_qty3_tab(i);
2487
2488 l_rollup_equip_amt1(t_id) := l_rollup_equip_amt1(t_id) + p_amount1_tab(i);
2489 l_rollup_equip_amt2(t_id) := l_rollup_equip_amt2(t_id) + p_amount2_tab(i);
2490 l_rollup_equip_amt3(t_id) := l_rollup_equip_amt3(t_id) + p_amount3_tab(i);
2491 l_rollup_equip_amt4(t_id) := l_rollup_equip_amt4(t_id) + p_amount4_tab(i);
2492 l_rollup_equip_amt5(t_id) := l_rollup_equip_amt5(t_id) + p_amount5_tab(i);
2493 l_rollup_equip_amt6(t_id) := l_rollup_equip_amt6(t_id) + p_amount6_tab(i);
2494 l_rollup_equip_amt7(t_id) := l_rollup_equip_amt7(t_id) + p_amount7_tab(i);
2495 l_rollup_equip_amt8(t_id) := l_rollup_equip_amt8(t_id) + p_amount8_tab(i);
2496
2497 if ( p_billable_flag_tab(i) = 'Y' ) then
2498
2499 l_rollup_bill_equip_Qty1(t_id) := l_rollup_bill_equip_Qty1(t_id) + p_qty1_tab(i);
2500 l_rollup_bill_equip_Qty2(t_id) := l_rollup_bill_equip_Qty2(t_id) + p_qty2_tab(i);
2501 l_rollup_bill_equip_Qty3(t_id) := l_rollup_bill_equip_Qty3(t_id) + p_qty3_tab(i);
2502
2503 end if;
2504
2505 end if;
2506
2507
2508 if ( p_billable_flag_tab(i) = 'Y' ) then
2509
2510
2511 l_rollup_bill_amt1(t_id) := l_rollup_bill_amt1(t_id) + p_amount1_tab(i);
2512 l_rollup_bill_amt2(t_id) := l_rollup_bill_amt2(t_id) + p_amount2_tab(i);
2513
2514 l_rollup_capit_amt1(t_id) := l_rollup_capit_amt1(t_id) + p_amount1_tab(i);
2518 end if;
2515 l_rollup_capit_amt2(t_id) := l_rollup_capit_amt2(t_id) + p_amount2_tab(i);
2516
2517
2519
2520 else
2521
2522 ppr_log(' ROLLUP_AMOUNT : l_parent_counter ... '||l_parent_counter ,1);
2523
2524 l_parent_counter := l_parent_counter + 1;
2525 ppr_log(' ROLLUP_AMOUNT : l_parent_counter1 ... '||l_parent_counter|| ' t_id '||t_id ,1);
2526 l_parent_object_index(l_parent_counter) := t_id;
2527
2528 l_rollup_amount1(t_id) := p_amount1_tab(i);
2529 l_rollup_amount2(t_id) := p_amount2_tab(i);
2530 l_rollup_amount3(t_id) := p_amount3_tab(i);
2531
2532 l_rollup_amount4(t_id) := p_amount4_tab(i);
2533 l_rollup_amount5(t_id) := p_amount5_tab(i);
2534 l_rollup_amount6(t_id) := p_amount6_tab(i);
2535
2536 l_rollup_amount7(t_id) := p_amount7_tab(i);
2537 l_rollup_amount8(t_id) := p_amount8_tab(i);
2538
2539 l_rollup_qty1(t_id) := p_qty1_tab(i);
2540 l_rollup_qty2(t_id) := p_qty2_tab(i);
2541 l_rollup_qty3(t_id) := p_qty3_tab(i);
2542
2543 ppr_log(' ROLLUP_AMOUNT : l_parent_counter2 ... ' ,1);
2544 if ( p_resource_class_tab(i) = 'PEOPLE') then
2545 l_rollup_labor_qty1(t_id) := p_qty1_tab(i);
2546 l_rollup_labor_qty2(t_id) := p_qty2_tab(i);
2547 l_rollup_labor_qty3(t_id) := p_qty3_tab(i);
2548
2549 l_rollup_labor_amt1(t_id) := p_amount1_tab(i);
2550 l_rollup_labor_amt2(t_id) := p_amount2_tab(i);
2551 l_rollup_labor_amt3(t_id) := p_amount3_tab(i);
2552 l_rollup_labor_amt4(t_id) := p_amount4_tab(i);
2553 l_rollup_labor_amt5(t_id) := p_amount5_tab(i);
2554 l_rollup_labor_amt6(t_id) := p_amount6_tab(i);
2555 l_rollup_labor_amt7(t_id) := p_amount7_tab(i);
2556 l_rollup_labor_amt8(t_id) := p_amount8_tab(i);
2557
2558
2559 if ( p_billable_flag_tab(i) = 'Y' ) then
2560
2561 l_rollup_bill_labor_Qty1(t_id) := p_qty1_tab(i);
2562 l_rollup_bill_labor_Qty2(t_id) := p_qty2_tab(i);
2563 l_rollup_bill_labor_Qty3(t_id) := p_qty3_tab(i);
2564 l_rollup_bill_labor_amt1(t_id) := p_amount1_tab(i);
2565 l_rollup_bill_labor_amt2(t_id) := p_amount2_tab(i);
2566
2567 else
2568
2569 l_rollup_bill_labor_Qty1(t_id) := 0;
2570 l_rollup_bill_labor_Qty2(t_id) := 0;
2571 l_rollup_bill_labor_Qty3(t_id) := 0;
2572 l_rollup_bill_labor_amt1(t_id) := 0;
2573 l_rollup_bill_labor_amt2(t_id) := 0;
2574
2575 end if;
2576
2577 ppr_log(' ROLLUP_AMOUNT : l_parent_counter4 ... ' ,1);
2578 else
2579 l_rollup_labor_qty1(t_id) := 0;
2580 l_rollup_labor_qty2(t_id) := 0;
2581 l_rollup_labor_qty3(t_id) := 0;
2582
2583 l_rollup_bill_labor_Qty1(t_id) := 0;
2584 l_rollup_bill_labor_Qty2(t_id) := 0;
2585 l_rollup_bill_labor_Qty3(t_id) := 0;
2586 l_rollup_bill_labor_amt1(t_id) := 0;
2587 l_rollup_bill_labor_amt2(t_id) := 0;
2588
2589 l_rollup_labor_amt1(t_id) := 0;
2590 l_rollup_labor_amt2(t_id) := 0;
2591 l_rollup_labor_amt3(t_id) := 0;
2592 l_rollup_labor_amt4(t_id) := 0;
2593 l_rollup_labor_amt5(t_id) := 0;
2594 l_rollup_labor_amt6(t_id) := 0;
2595 l_rollup_labor_amt7(t_id) := 0;
2596 l_rollup_labor_amt8(t_id) := 0;
2597
2598
2599
2600 end if;
2601
2602 if ( p_resource_class_tab(i) = 'EQUIPMENT') then
2603 l_rollup_equip_qty1(t_id) := p_qty1_tab(i);
2604 l_rollup_equip_qty2(t_id) := p_qty2_tab(i);
2605 l_rollup_equip_qty3(t_id) := p_qty3_tab(i);
2606
2607 l_rollup_equip_amt1(t_id) := p_amount1_tab(i);
2608 l_rollup_equip_amt2(t_id) := p_amount2_tab(i);
2609 l_rollup_equip_amt3(t_id) := p_amount3_tab(i);
2610 l_rollup_equip_amt4(t_id) := p_amount4_tab(i);
2611 l_rollup_equip_amt5(t_id) := p_amount5_tab(i);
2612 l_rollup_equip_amt6(t_id) := p_amount6_tab(i);
2613 l_rollup_equip_amt7(t_id) := p_amount7_tab(i);
2614 l_rollup_equip_amt8(t_id) := p_amount8_tab(i);
2615
2616 if ( p_billable_flag_tab(i) = 'Y' ) then
2617
2618 l_rollup_bill_equip_Qty1(t_id) := p_qty1_tab(i);
2619 l_rollup_bill_equip_Qty2(t_id) := p_qty2_tab(i);
2620 l_rollup_bill_equip_Qty3(t_id) := p_qty3_tab(i);
2621
2622 else
2623 l_rollup_bill_equip_Qty1(t_id) := 0;
2624 l_rollup_bill_equip_Qty2(t_id) := 0;
2625 l_rollup_bill_equip_Qty3(t_id) := 0;
2626
2627 end if;
2628
2629 else
2630 l_rollup_equip_qty1(t_id) := 0;
2631 l_rollup_equip_qty2(t_id) := 0;
2632 l_rollup_equip_qty3(t_id) := 0;
2633
2634 l_rollup_bill_equip_Qty1(t_id) := 0;
2635 l_rollup_bill_equip_Qty2(t_id) := 0;
2636 l_rollup_bill_equip_Qty3(t_id) := 0;
2637
2638 l_rollup_equip_amt1(t_id) := 0;
2639 l_rollup_equip_amt2(t_id) := 0;
2640 l_rollup_equip_amt3(t_id) := 0;
2641 l_rollup_equip_amt4(t_id) := 0;
2642 l_rollup_equip_amt5(t_id) := 0;
2643 l_rollup_equip_amt6(t_id) := 0;
2644 l_rollup_equip_amt7(t_id) := 0;
2645 l_rollup_equip_amt8(t_id) := 0;
2646
2647
2648 end if;
2649
2650 if ( p_billable_flag_tab(i) = 'Y' ) then
2651
2652
2653 l_rollup_bill_amt1(t_id) := p_amount1_tab(i);
2654 l_rollup_bill_amt2(t_id) := p_amount2_tab(i);
2655
2656 l_rollup_capit_amt1(t_id) := p_amount1_tab(i);
2657 l_rollup_capit_amt2(t_id) := p_amount2_tab(i);
2658 else
2659
2660
2664 l_rollup_capit_amt1(t_id) := 0;
2661 l_rollup_bill_amt1(t_id) := 0;
2662 l_rollup_bill_amt2(t_id) := 0;
2663
2665 l_rollup_capit_amt2(t_id) := 0;
2666
2667 end if;
2668
2669 end if;
2670
2671 ppr_log('ROLLUP AMOUNT : LOOP END roll task_id ... '||t_id||' l_rollup_bill_amt1(t_id) '|| l_rollup_bill_amt1(t_id),1);
2672
2673
2674 END LOOP; -- l_level loop
2675
2676 END LOOP; -- p_object_id_tab loop
2677
2678
2679 ppr_log('ROLLUP AMOUNT : OUT VARIABLE l_parent_counter '||l_parent_counter,1);
2680
2681
2682 For k IN 1..l_parent_counter LOOP
2683
2684 t_id := l_parent_object_index(k);
2685
2686 p_out_object_id_tab(k) := t_id;
2687 p_out_amount1_tab(k) := l_rollup_amount1(t_id);
2688 p_out_amount2_tab(k) := l_rollup_amount2(t_id);
2689 p_out_amount3_tab(k) := l_rollup_amount3(t_id);
2690
2691 p_out_amount4_tab(k) := l_rollup_amount4(t_id);
2692 p_out_amount5_tab(k) := l_rollup_amount5(t_id);
2693 p_out_amount6_tab(k) := l_rollup_amount6(t_id);
2694
2695 p_out_amount7_tab(k) := l_rollup_amount7(t_id);
2696 p_out_amount8_tab(k) := l_rollup_amount8(t_id);
2697
2698 p_out_qty1_tab(k) := l_rollup_qty1(t_id);
2699 p_out_qty2_tab(k) := l_rollup_qty2(t_id);
2700 p_out_qty3_tab(k) := l_rollup_qty3(t_id);
2701
2702 p_out_labor_qty1_tab(k) := l_rollup_labor_qty1(t_id);
2703 p_out_labor_qty2_tab(k) := l_rollup_labor_qty2(t_id);
2704 p_out_labor_qty3_tab(k) := l_rollup_labor_qty3(t_id);
2705
2706 p_out_equip_qty1_tab(k) := l_rollup_equip_qty1(t_id);
2707 p_out_equip_qty2_tab(k) := l_rollup_equip_qty2(t_id);
2708 p_out_equip_qty3_tab(k) := l_rollup_equip_qty3(t_id);
2709
2710 p_out_bill_labor_Qty1_tab(k) := l_rollup_bill_labor_Qty1(t_id);
2711 p_out_bill_equip_Qty1_tab(k) := l_rollup_bill_equip_Qty1(t_id);
2712 p_out_bill_labor_Qty2_tab(k) := l_rollup_bill_labor_Qty2(t_id);
2713 p_out_bill_equip_Qty2_tab(k) := l_rollup_bill_equip_Qty2(t_id);
2714 p_out_bill_labor_Qty3_tab(k) := l_rollup_bill_labor_Qty3(t_id);
2715 p_out_bill_equip_Qty3_tab(k) := l_rollup_bill_equip_Qty3(t_id);
2716 p_out_bill_amt1_tab(k) := l_rollup_bill_amt1(t_id);
2717 p_out_bill_amt2_tab(k) := l_rollup_bill_amt2(t_id);
2718 p_out_bill_labor_amt1_tab(k) := l_rollup_bill_labor_amt1(t_id);
2719 p_out_bill_labor_amt2_tab(k) := l_rollup_bill_labor_amt2(t_id);
2720 p_out_equip_amt1_tab(k) := l_rollup_equip_amt1(t_id);
2721 p_out_equip_amt2_tab(k) := l_rollup_equip_amt2(t_id);
2722 p_out_equip_amt3_tab(k) := l_rollup_equip_amt3(t_id);
2723 p_out_equip_amt4_tab(k) := l_rollup_equip_amt4(t_id);
2724 p_out_equip_amt5_tab(k) := l_rollup_equip_amt5(t_id);
2725 p_out_equip_amt6_tab(k) := l_rollup_equip_amt6(t_id);
2726 p_out_equip_amt7_tab(k) := l_rollup_equip_amt7(t_id);
2727 p_out_equip_amt8_tab(k) := l_rollup_equip_amt8(t_id);
2728 p_out_capit_amt1_tab(k) := l_rollup_capit_amt1(t_id);
2729 p_out_capit_amt2_tab(k) := l_rollup_capit_amt2(t_id);
2730 p_out_labor_amt1_tab(k) := l_rollup_labor_amt1(t_id);
2731 p_out_labor_amt2_tab(k) := l_rollup_labor_amt2(t_id);
2732 p_out_labor_amt3_tab(k) := l_rollup_labor_amt3(t_id);
2733 p_out_labor_amt4_tab(k) := l_rollup_labor_amt4(t_id);
2734 p_out_labor_amt5_tab(k) := l_rollup_labor_amt5(t_id);
2735 p_out_labor_amt6_tab(k) := l_rollup_labor_amt6(t_id);
2736 p_out_labor_amt7_tab(k) := l_rollup_labor_amt7(t_id);
2737 p_out_labor_amt8_tab(k) := l_rollup_labor_amt8(t_id);
2738
2739
2740
2741 p_out_object_index_tab(t_id) := k;
2742
2743 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);
2744
2745
2746 END LOOP;
2747
2748
2749 ppr_log('ROLLUP COUNT '||p_out_equip_amt1_tab.count,1);
2750
2751 Exception
2752 when others then
2753 raise;
2754
2755 end rollup_amounts ;
2756
2757 procedure merge_into_fact (
2758 p_calling_mode IN VARCHAR2 default 'CREATE'
2759 ,p_max_msg_count IN NUMBER default NULL
2760 ,p_fact_slice IN VARCHAR2 default 'WBS'
2761 ,p_debug_mode IN VARCHAR2 default 'N'
2762 ,p_project_id In number default -1
2763 ,p_budget_version_id in number default -1
2764 ,p_rbs_cbs_version_id_2 in number default -1
2765 ,p_rbs_cbs_version_id in number default -1
2766 ,p_object_id in number default -1
2767 ,p_3_level_object_id in number default -1
2768 ,p_3_level_version_id in number default -1
2769 ,p_currency_code in VARCHAR2 default -1
2770 ,p_fact_task_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2771 ,p_fact_task_index_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2772 ,p_fact_amount1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2773 ,p_fact_amount2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2774 ,p_fact_amount3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2775 ,p_fact_amount4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2776 ,p_fact_amount5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2777 ,p_fact_amount6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2778 ,p_fact_amount7_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2779 ,p_fact_amount8_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2780 ,p_fact_qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2781 ,p_fact_qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2782 ,p_fact_qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2786 ,p_fact_equip_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2783 ,p_fact_labor_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2784 ,p_fact_equip_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2785 ,p_fact_labor_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2787 ,p_fact_labor_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2788 ,p_fact_equip_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2789 ,p_fact_bill_labor_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2790 ,p_fact_bill_equip_Qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2791 ,p_fact_bill_labor_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2792 ,p_fact_bill_equip_Qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2793 ,p_fact_bill_labor_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2794 ,p_fact_bill_equip_Qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2795 ,p_fact_bill_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2796 ,p_fact_bill_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2797 ,p_fact_bill_labor_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2798 ,p_fact_bill_labor_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2799 ,p_fact_equip_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2800 ,p_fact_equip_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2801 ,p_fact_equip_amt3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2802 ,p_fact_equip_amt4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2803 ,p_fact_equip_amt5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2804 ,p_fact_equip_amt6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2805 ,p_fact_equip_amt7_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2806 ,p_fact_equip_amt8_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2807 ,p_fact_capit_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2808 ,p_fact_capit_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2809 ,p_fact_labor_amt1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2810 ,p_fact_labor_amt2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2811 ,p_fact_labor_amt3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2812 ,p_fact_labor_amt4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2813 ,p_fact_labor_amt5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2814 ,p_fact_labor_amt6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2815 ,p_fact_labor_amt7_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2816 ,p_fact_labor_amt8_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
2817 ,x_return_status OUT NOCOPY VARCHAR2
2818 ,x_msg_count OUT NOCOPY NUMBER
2819 ,x_msg_data OUT NOCOPY VARCHAR2
2820 ) as
2821
2822
2823 l_upd_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
2824 l_upd_task_arr_2 PA_PLSQL_DATATYPES.NumTabTyp;
2825 l_upd_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
2826
2827 l_ins_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
2828
2829 l_out_task_index_arr PA_PLSQL_DATATYPES.NumTabTyp;
2830
2831 begin
2832
2833
2834 l_upd_task_flag_arr.delete;
2835 l_upd_task_arr.delete;
2836 l_upd_task_arr_2.delete;
2837
2838 ppr_log('INSIDE MERGE p_calling_mode '||p_calling_mode||' p_fact_slice '||p_fact_slice ,0);
2839
2840
2841 ppr_log(' TEST COUNT '||p_fact_equip_amt1_arr.count,0);
2842
2843 /*
2844 FOR i IN 1..p_fact_task_arr.count loop
2845
2846 ppr_log('LOOP i '||i);
2847
2848 ppr_log(p_fact_amount1_arr(i));ppr_log(p_fact_amount2_arr(i));
2849 ppr_log(p_fact_amount3_arr(i));ppr_log(p_fact_bill_amt1_arr(i));
2850 ppr_log(p_fact_bill_amt2_arr(i)); ppr_log(p_fact_bill_labor_amt1_arr(i));
2851 ppr_log(p_fact_bill_labor_amt2_arr(i)); ppr_log(p_fact_bill_labor_Qty1_arr(i));
2852 ppr_log(' print 1 ');
2853 ppr_log(p_fact_equip_amt1_arr(i));
2854 ppr_log(p_fact_equip_amt2_arr(i));
2855 -----
2856 ppr_log(p_fact_capit_amt1_arr(i));ppr_log(p_fact_capit_amt2_arr(i));
2857 ppr_log(p_fact_labor_amt1_arr(i));ppr_log(p_fact_labor_amt2_arr(i));
2858 ppr_log(p_fact_labor_qty1_arr(i)); ppr_log(p_fact_labor_amt3_arr(i));
2859 ppr_log(p_fact_equip_qty1_arr(i));ppr_log(p_fact_bill_equip_Qty1_arr(i));
2860 ----
2861 ppr_log(' print 2 ');
2862 ppr_log(p_fact_labor_qty2_arr(i));ppr_log(p_fact_equip_qty2_arr(i));ppr_log(p_fact_labor_amt5_arr(i));
2863 ppr_log(p_fact_equip_amt5_arr(i)) ;ppr_log(p_fact_amount5_arr(i));ppr_log(p_fact_amount4_arr(i));
2864 -------
2865 ppr_log(p_fact_amount6_arr(i));ppr_log(p_fact_labor_amt4_arr(i));ppr_log(p_fact_equip_amt4_arr(i));
2866 ppr_log(p_fact_labor_qty3_arr(i));ppr_log(p_fact_equip_qty3_arr(i));ppr_log(p_fact_labor_amt8_arr(i));
2867 ----
2868 ppr_log(' print 3 ');
2869 ppr_log(p_fact_equip_amt8_arr(i));ppr_log(p_fact_amount8_arr(i));ppr_log(p_fact_amount7_arr(i));
2870 ppr_log(p_fact_labor_amt7_arr(i));ppr_log(p_fact_equip_amt7_arr(i)) ;
2871
2872 end loop;
2873 */
2874
2875 if ( p_calling_mode = 'CREATE' and ( p_fact_slice = 'CBS_TO_RBS' or p_fact_slice = 'CBS_TO_WBS' or p_fact_slice = 'WBS_TO_CBS_TO_RBS') ) then
2876
2877 ppr_log(' p_calling_mode = CREATE and ( p_fact_slice = CBS_TO_RBS and CBS_TO_WBS )');
2878
2879 FORALL i IN 1..p_fact_task_arr.count
2880 UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
2881 SET (
2882 ----
2883 RAW_COST, BRDN_COST, REVENUE
2884 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
2885 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
2886 --------
2887 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
2888 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
2889 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
2890 ----
2894 -------
2891 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
2892 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
2893 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
2895 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
2896 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
2897 ----
2898 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
2899 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
2900 -----
2901 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
2902 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
2903 (
2904 SELECT
2905 ----
2906 p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
2907 ,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)
2908 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
2909 -----
2910 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
2911 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
2912 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
2913 ----
2914 ,0,0,0,0
2915 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
2916 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
2917 -------
2918 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
2919 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
2920 ----
2921 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
2922 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
2923 -------
2924 -------
2925 ,0,0,0,0,0,0,0,0,0,0,0
2926 ,0,0,0,0
2927 FROM DUAL
2928 )
2929 WHERE PROJECT_ID = p_project_id
2930 AND ( ( PROJECT_ELEMENT_ID = p_3_level_object_id AND
2931 RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS_TO_RBS' )
2932 OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'CBS_TO_RBS' )
2933 OR ( PROJECT_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS' ) )
2934 AND CBS_VERSION_ID = decode(p_fact_slice,'CBS_TO_WBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id_2,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id_2,-1)
2935 AND CBS_ELEMENT_ID = p_object_id
2936 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
2937 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
2938 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
2939 AND PLAN_VERSION_ID = p_budget_version_id
2940 AND CURRENCY_CODE = p_currency_code
2941 RETURNING RBS_ELEMENT_ID, PROJECT_ELEMENT_ID
2942 BULK COLLECT INTO l_upd_task_arr, l_upd_task_arr_2;
2943
2944 IF p_fact_slice = 'CBS_TO_WBS' and l_upd_task_arr_2.count > 0 then
2945
2946 l_upd_task_arr.delete;
2947
2948 for i IN 1..l_upd_task_arr_2.count Loop
2949
2950 l_upd_task_arr(i) := l_upd_task_arr_2(i);
2951
2952 end loop;
2953
2954 end if;
2955
2956
2957
2958 elsif ( p_calling_mode = 'INCREMENTAL' and ( p_fact_slice = 'CBS_TO_RBS' or p_fact_slice = 'CBS_TO_WBS' or p_fact_slice = 'WBS_TO_CBS_TO_RBS' ) ) then
2959
2960
2961 FORALL i IN 1..p_fact_task_arr.count
2962 UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
2963 SET (
2964 ----
2965 RAW_COST, BRDN_COST, REVENUE
2966 , bill_RAW_COST, bill_BRDN_COST
2967 , bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
2968 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
2969 --------
2970 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
2971 , LABOR_RAW_COST, LABOR_BRDN_COST
2972 , LABOR_HRS, LABOR_REVENUE
2973 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
2974 ----
2975 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
2976 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
2977 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
2978 -------
2979 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
2980 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
2981 ----
2982 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
2983 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
2984 -----
2985 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
2986 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
2987 (
2988 SELECT
2989 ----
2990 p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
2991 ,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
2992 ,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
2993 ,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
2994 -----
2995 ,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
2996 ,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
2997 ,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
2998 , p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
2999 ----
3000 ,0,0,0,0
3001 ,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
3002 ,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
3003 -------
3007 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3004 ,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
3005 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3006 ----
3008 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3009 -------
3010 -------
3011 ,0,0,0,0,0,0,0,0,0,0,0
3012 ,0,0,0,0
3013 FROM DUAL
3014 )
3015 WHERE PROJECT_ID = p_project_id
3016 AND ( ( PROJECT_ELEMENT_ID = p_3_level_object_id AND
3017 RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS_TO_RBS' )
3018 OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'CBS_TO_RBS' )
3019 OR ( PROJECT_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS' ) )
3020 AND CBS_VERSION_ID = decode(p_fact_slice,'CBS_TO_WBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id_2,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id_2,-1)
3021 AND CBS_ELEMENT_ID = p_object_id
3022 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3023 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3024 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3025 AND PLAN_VERSION_ID = p_budget_version_id
3026 AND CURRENCY_CODE = p_currency_code
3027 RETURNING RBS_ELEMENT_ID, PROJECT_ELEMENT_ID
3028 BULK COLLECT INTO l_upd_task_arr, l_upd_task_arr_2;
3029
3030 IF p_fact_slice = 'CBS_TO_WBS' and l_upd_task_arr_2.count > 0 then
3031
3032 l_upd_task_arr.delete;
3033
3034 for i IN 1..l_upd_task_arr_2.count Loop
3035
3036 l_upd_task_arr(i) := l_upd_task_arr_2(i);
3037
3038 end loop;
3039
3040 end if;
3041
3042
3043 elsif ( p_calling_mode = 'CREATE' and ( p_fact_slice = 'RBS_TO_WBS') ) then
3044
3045 ppr_log(' p_calling_mode = CREATE and ( p_fact_slice = RBS_TO_WBS )');
3046
3047 FORALL i IN 1..p_fact_task_arr.count
3048 UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
3049 SET (
3050 ----
3051 RAW_COST, BRDN_COST, REVENUE
3052 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3053 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3054 --------
3055 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3056 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3057 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3058 ----
3059 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3060 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3061 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3062 -------
3063 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3064 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3065 ----
3066 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3067 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3068 -----
3069 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3070 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3071 (
3072 SELECT
3073 ----
3074 p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3075 ,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)
3076 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3077 -----
3078 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3079 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3080 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3081 ----
3082 ,0,0,0,0
3083 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3084 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3085 -------
3086 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3087 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3088 ----
3089 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3090 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3091 -------
3092 -------
3093 ,0,0,0,0,0,0,0,0,0,0,0
3094 ,0,0,0,0
3095 FROM DUAL
3096 )
3097 WHERE PROJECT_ID = p_project_id
3098 AND RBS_ELEMENT_ID = p_object_id
3099 AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
3100 AND RBS_VERSION_ID = p_rbs_cbs_version_id
3101 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3102 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3103 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3104 -- AND RBS_AGGR_LEVEL = 'T'
3105 -- AND WBS_ROLLUP_FLAG = 'Y'
3106 -- AND PRG_ROLLUP_FLAG = 'N'
3107 AND PLAN_VERSION_ID = p_budget_version_id
3108 AND CURRENCY_CODE = p_currency_code
3109 RETURNING PROJECT_ELEMENT_ID
3110 BULK COLLECT INTO l_upd_task_arr;
3111
3112 elsif ( p_calling_mode = 'INCREMENTAL' and p_fact_slice = 'RBS_TO_WBS' ) then
3113
3114 ppr_log(' p_calling_mode = INCREMENTAL and ( p_fact_slice = RBS_TO_WBS )');
3115
3116 FORALL i IN 1..p_fact_task_arr.count
3117 UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
3118 SET (
3119 ----
3120 RAW_COST, BRDN_COST, REVENUE
3121 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3122 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3123 --------
3124 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3125 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3126 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3127 ----
3128 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3129 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3130 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3131 -------
3132 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3133 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3134 ----
3135 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3136 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3137 -----
3138 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3139 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3140 (
3141 SELECT
3142 ----
3143 p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
3144 ,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
3145 ,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
3146 ,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
3147 -----
3148 ,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
3149 ,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
3150 ,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
3151 , p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
3152 ----
3153 ,0,0,0,0
3154 ,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
3155 ,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
3156 -------
3157 ,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
3158 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3159 ----
3160 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3161 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3162 -------
3163 -------
3164 ,0,0,0,0,0,0,0,0,0,0,0
3165 ,0,0,0,0
3166 FROM DUAL
3167 )
3168 WHERE PROJECT_ID = p_project_id
3169 AND RBS_ELEMENT_ID = p_object_id
3170 AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
3171 AND RBS_VERSION_ID = p_rbs_cbs_version_id
3172 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3173 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3174 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3175 -- AND RBS_AGGR_LEVEL = 'T'
3176 -- AND WBS_ROLLUP_FLAG = 'Y'
3177 -- AND PRG_ROLLUP_FLAG = 'N'
3178 AND PLAN_VERSION_ID = p_budget_version_id
3179 AND CURRENCY_CODE = p_currency_code
3180 RETURNING PROJECT_ELEMENT_ID
3181 BULK COLLECT INTO l_upd_task_arr;
3182
3183 elsif ( p_calling_mode = 'CREATE' and ( p_fact_slice = 'WBS_TO_RBS' or p_fact_slice = 'WBS_TO_CBS' or p_fact_slice = 'CBS_TO_WBS_TO_RBS' ) ) then
3184
3185 ppr_log(' p_calling_mode = CREATE and ( p_fact_slice = WBS_TO_RBS or p_fact_slice = WBS_TO_CBS )');
3186
3187 FORALL i IN 1..p_fact_task_arr.count
3188 UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
3189 SET (
3190 ----
3191 RAW_COST, BRDN_COST, REVENUE
3192 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3193 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3194 --------
3195 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3196 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3197 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3198 ----
3199 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3200 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3201 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3202 -------
3203 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3204 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3205 ----
3206 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3207 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3208 -----
3209 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3210 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3211 (
3212 SELECT
3213 ----
3214 p_fact_amount1_arr(i) ,p_fact_amount2_arr(i) ,p_fact_amount3_arr(i)
3215 ,p_fact_bill_amt1_arr(i) ,p_fact_bill_amt2_arr(i)
3216 ,p_fact_bill_labor_amt1_arr(i) ,p_fact_bill_labor_amt2_arr(i)
3217 ,p_fact_bill_labor_Qty1_arr(i) ,p_fact_equip_amt1_arr(i) ,p_fact_equip_amt2_arr(i)
3218 -----
3219 ,p_fact_capit_amt1_arr(i) ,p_fact_capit_amt2_arr(i)
3220 ,p_fact_labor_amt1_arr(i) ,p_fact_labor_amt2_arr(i)
3221 ,p_fact_labor_qty1_arr(i) , p_fact_labor_amt3_arr(i)
3222 , p_fact_equip_qty1_arr(i) ,p_fact_bill_equip_Qty1_arr(i)
3223 ----
3224 ,0,0,0,0
3225 ,p_fact_labor_qty2_arr(i) ,p_fact_equip_qty2_arr(i) ,p_fact_labor_amt5_arr(i)
3226 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i) ,p_fact_amount4_arr(i)
3227 -------
3228 ,p_fact_amount6_arr(i) ,p_fact_labor_amt4_arr(i) ,p_fact_equip_amt4_arr(i)
3229 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3230 ----
3231 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3232 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3233 -------
3234 -------
3235 ,0,0,0,0,0,0,0,0,0,0,0
3236 ,0,0,0,0
3237 FROM DUAL
3238 )
3239 WHERE PROJECT_ID = p_project_id
3240 AND ( ( cbs_element_id = p_3_level_object_id AND RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS_TO_RBS' )
3241 OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_RBS' )
3242 OR ( CBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_CBS' ) )
3243 AND ( ( RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_RBS' )
3244 OR ( CBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS' ) )
3245 AND PROJECT_ELEMENT_ID = p_object_id
3246 -- AND RBS_AGGR_LEVEL = 'T'
3247 -- AND WBS_ROLLUP_FLAG = 'Y'
3248 -- AND PRG_ROLLUP_FLAG = 'N'
3249 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3250 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3251 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3252 AND PLAN_VERSION_ID = p_budget_version_id
3253 AND CURRENCY_CODE = p_currency_code
3254 RETURNING RBS_ELEMENT_ID,CBS_ELEMENT_ID
3255 BULK COLLECT INTO l_upd_task_arr,l_upd_task_arr_2;
3256
3257 ppr_log(' BEFORE SWAP ... ');
3258 IF p_fact_slice = 'WBS_TO_CBS' and l_upd_task_arr_2.count > 0 then
3259
3260 l_upd_task_arr.delete;
3261
3262 for i IN 1..l_upd_task_arr_2.count Loop
3263
3264 l_upd_task_arr(i) := l_upd_task_arr_2(i);
3265
3266 end loop;
3267 end if;
3268
3269 elsif ( p_calling_mode = 'INCREMENTAL' and ( p_fact_slice = 'WBS_TO_RBS' or p_fact_slice = 'WBS_TO_CBS' or p_fact_slice = 'CBS_TO_WBS_TO_RBS' ) ) then
3270
3271 ppr_log(' p_calling_mode = INCREMENTAL and ( p_fact_slice = WBS_TO_RBS or p_fact_slice = WBS_TO_CBS )');
3272
3273 FORALL i IN 1..p_fact_task_arr.count
3274 UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
3275 SET (
3276 ----
3277 RAW_COST, BRDN_COST, REVENUE
3278 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3279 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3280 --------
3281 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3282 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3283 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3284 ----
3285 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3286 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3287 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3288 -------
3289 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3290 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3291 ----
3292 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3293 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3294 -----
3295 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3296 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3297 (
3298 SELECT
3299 ----
3300 p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
3301 ,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
3302 ,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
3303 ,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
3304 -----
3308 , p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
3305 ,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
3306 ,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
3307 ,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
3309 ----
3310 ,0,0,0,0
3311 ,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
3312 ,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
3313 -------
3314 ,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
3315 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3316 ----
3317 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3318 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3319 -------
3320 -------
3321 ,0,0,0,0,0,0,0,0,0,0,0
3322 ,0,0,0,0
3323 FROM DUAL
3324 )
3325 WHERE PROJECT_ID = p_project_id
3326 AND ( ( cbs_element_id = p_3_level_object_id and RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS_TO_RBS' )
3327 OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_RBS' )
3328 OR ( CBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_CBS' ) )
3329 AND ( ( RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_RBS' )
3330 OR ( CBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS' ) )
3331 AND PROJECT_ELEMENT_ID = p_object_id
3332 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3333 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3334 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3335 -- AND RBS_AGGR_LEVEL = 'T'
3336 -- AND WBS_ROLLUP_FLAG = 'Y'
3337 -- AND PRG_ROLLUP_FLAG = 'N'
3338 AND PLAN_VERSION_ID = p_budget_version_id
3339 AND CURRENCY_CODE = p_currency_code
3340 RETURNING RBS_ELEMENT_ID,CBS_ELEMENT_ID
3341 BULK COLLECT INTO l_upd_task_arr,l_upd_task_arr_2;
3342
3343 ppr_log(' BEFORE SWAP ... ');
3344 IF p_fact_slice = 'WBS_TO_CBS' and l_upd_task_arr_2.count > 0 then
3345
3346 l_upd_task_arr.delete;
3347
3348 for i IN 1..l_upd_task_arr_2.count Loop
3349
3350 l_upd_task_arr(i) := l_upd_task_arr_2(i);
3351
3352 end loop;
3353
3354 end if;
3355
3356
3357 elsif ( p_calling_mode = 'CREATE' and p_fact_slice = 'WBS' ) then
3358
3359 ppr_log(' p_calling_mode = CREATE and p_fact_slice = WBS');
3360
3361 FORALL i IN 1..p_fact_task_arr.count
3362 UPDATE pa_pjt_wbs_fact fact /* bulk bind */
3363 SET (
3364 ----
3365 RAW_COST, BRDN_COST, REVENUE
3366 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3367 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3368 --------
3369 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3370 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3371 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3372 ----
3373 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3374 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3375 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3376 -------
3377 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3378 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3379 ----
3380 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3381 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3382 -----
3383 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3384 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3385 (
3386 SELECT
3387 ----
3388 p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3389 ,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)
3390 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3391 -----
3392 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3393 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3394 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3395 ----
3396 ,0,0,0,0
3397 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3398 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3399 -------
3400 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3401 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3402 ----
3403 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3404 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3405 -------
3406 -------
3407 ,0,0,0,0,0,0,0,0,0,0,0
3408 ,0,0,0,0
3409 FROM DUAL
3410 )
3411 WHERE PROJECT_ID = p_project_id
3412 AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
3413 -- AND RBS_AGGR_LEVEL = 'T'
3414 -- AND WBS_ROLLUP_FLAG = 'Y'
3415 -- AND PRG_ROLLUP_FLAG = 'N'
3416 -- AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3417 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3418 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3419 AND PLAN_VERSION_ID = p_budget_version_id
3420 AND CURRENCY_CODE = p_currency_code
3421 RETURNING PROJECT_ELEMENT_ID
3422 BULK COLLECT INTO l_upd_task_arr;
3423
3424 elsif ( p_calling_mode = 'INCREMENTAL' and p_fact_slice = 'WBS' ) then
3425
3426 FORALL i IN 1..p_fact_task_arr.count
3427 UPDATE pa_pjt_wbs_fact fact /* bulk bind */
3428 SET (
3429 ----
3430 RAW_COST, BRDN_COST, REVENUE
3431 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3432 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3433 --------
3434 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3435 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3436 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3437 ----
3438 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3439 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3440 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3441 -------
3442 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3443 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3444 ----
3445 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3446 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3447 -----
3448 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3449 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3450 (
3451 SELECT
3452 ----
3453 p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
3454 ,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
3455 ,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
3456 ,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
3457 -----
3458 ,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
3459 ,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
3460 ,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
3461 , p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
3462 ----
3463 ,0,0,0,0
3464 ,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
3465 ,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
3466 -------
3467 ,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
3468 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3469 ----
3470 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3471 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3472 -------
3473 -------
3474 ,0,0,0,0,0,0,0,0,0,0,0
3475 ,0,0,0,0
3476 FROM DUAL
3477 )
3478 WHERE PROJECT_ID = p_project_id
3479 AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
3480 -- AND RBS_AGGR_LEVEL = 'T'
3481 -- AND WBS_ROLLUP_FLAG = 'Y'
3482 -- AND PRG_ROLLUP_FLAG = 'N'
3483 -- AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3484 AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3485 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3486 AND PLAN_VERSION_ID = p_budget_version_id
3487 AND CURRENCY_CODE = p_currency_code
3488 RETURNING PROJECT_ELEMENT_ID
3489 BULK COLLECT INTO l_upd_task_arr;
3490
3491
3492 elsif ( p_calling_mode = 'CREATE' and ( p_fact_slice = 'RBS' or p_fact_slice = 'CBS') ) then
3493
3494
3495 FORALL i IN 1..p_fact_task_arr.count
3496 UPDATE pa_pjt_rbs_cbs_fact fact /* bulk bind */
3497 SET (
3498 ----
3499 RAW_COST, BRDN_COST, REVENUE
3500 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3501 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3502 --------
3503 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3504 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3505 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3506 ----
3507 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3508 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3509 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3510 -------
3511 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3512 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3513 ----
3514 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3515 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3516 -----
3517 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3518 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3519 (
3520 SELECT
3521 ----
3522 p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3523 ,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)
3524 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3525 -----
3526 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3527 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3528 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3529 ----
3530 ,0,0,0,0
3531 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3532 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3533 -------
3534 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3535 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3536 ----
3537 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3538 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3539 -------
3540 -------
3541 ,0,0,0,0,0,0,0,0,0,0,0
3542 ,0,0,0,0
3543 FROM DUAL
3544 )
3545 WHERE PROJECT_ID = p_project_id
3546 AND RBS_CBS_ELEMENT_ID = p_fact_task_arr(i)
3547 AND RBS_CBS_VERSION_ID = p_rbs_cbs_version_id
3548 AND ROLLUP_TYPE = p_fact_slice
3549 -- AND RBS_AGGR_LEVEL = 'T'
3550 -- AND WBS_ROLLUP_FLAG = 'Y'
3551 -- AND PRG_ROLLUP_FLAG = 'N'
3552 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3553 -- AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3554 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3555 AND PLAN_VERSION_ID = p_budget_version_id
3556 AND CURRENCY_CODE = p_currency_code
3557 RETURNING RBS_CBS_ELEMENT_ID
3558 BULK COLLECT INTO l_upd_task_arr;
3559
3560 elsif ( p_calling_mode = 'INCREMENTAL' and ( p_fact_slice = 'RBS' or p_fact_slice = 'CBS') ) then
3561
3562 FORALL i IN 1..p_fact_task_arr.count
3563 UPDATE pa_pjt_rbs_cbs_fact fact /* bulk bind */
3564 SET (
3565 ----
3566 RAW_COST, BRDN_COST, REVENUE
3567 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3568 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3569 --------
3570 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3571 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3572 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3573 ----
3574 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3575 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3576 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3577 -------
3578 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3579 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3580 ----
3581 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3582 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3583 -----
3584 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3585 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
3589 p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
3586 (
3587 SELECT
3588 ----
3590 ,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
3591 ,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
3592 ,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
3593 -----
3594 ,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
3595 ,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
3596 ,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
3597 , p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
3598 ----
3599 ,0,0,0,0
3600 ,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
3601 ,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
3602 -------
3603 ,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
3604 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3605 ----
3606 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3607 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3608 -------
3609 -------
3610 ,0,0,0,0,0,0,0,0,0,0,0
3611 ,0,0,0,0
3612 FROM DUAL
3613 )
3614 WHERE PROJECT_ID = p_project_id
3615 AND RBS_CBS_ELEMENT_ID = p_fact_task_arr(i)
3616 AND RBS_CBS_VERSION_ID = p_rbs_cbs_version_id
3617 AND ROLLUP_TYPE = p_fact_slice
3618 -- AND RBS_AGGR_LEVEL = 'T'
3619 -- AND WBS_ROLLUP_FLAG = 'Y'
3620 -- AND PRG_ROLLUP_FLAG = 'N'
3621 AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
3622 -- AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
3623 AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
3624 AND PLAN_VERSION_ID = p_budget_version_id
3625 AND CURRENCY_CODE = p_currency_code
3626 RETURNING RBS_CBS_ELEMENT_ID
3627 BULK COLLECT INTO l_upd_task_arr;
3628
3629
3630 end if;
3631
3632 ppr_log(' SRIDHAR .... l_upd_task_arr '||l_upd_task_arr.count,0);
3633
3634
3635 l_ins_task_flag_arr.delete;
3636
3637
3638
3639 FOR k IN 1..l_upd_task_arr.count LOOP
3640
3641 ppr_log(' SRIDHAR .... l_upd_task_arr(k) , k '||l_upd_task_arr(k)||' '||k,1);
3642 ppr_log(' SRIDHAR .... p_fact_task_index_arr(l_upd_task_arr(k) ) , k '||p_fact_task_index_arr(l_upd_task_arr(k) )||' '||k,1);
3643
3644 l_upd_task_flag_arr( p_fact_task_index_arr(l_upd_task_arr(k) ) ) := 'Y';
3645
3646 ppr_log(' AFTER SRIDHAR .... l_upd_task_arr(k) , k '||l_upd_task_arr(k)||' '||k,1);
3647
3648 end loop;
3649
3650
3651 FOR g IN 1..p_fact_task_arr.count LOOP
3652
3653 if (l_upd_task_flag_arr.exists(g)) then
3654 l_ins_task_flag_arr(g) := 'N' ;
3655 else
3656 l_ins_task_flag_arr(g) := 'Y' ;
3657 end if;
3658
3659 end loop;
3660 ppr_log('UPDATE_INSERT p_fact_slice '||p_fact_slice||'.... l_upd_task_arr '||l_upd_task_arr.count||' l_ins_task_flag_arr '||l_ins_task_flag_arr.count||' p_fact_task_arr.count '||p_fact_task_arr.count );
3661 ppr_log(' SRIDHAR .... l_ins_task_flag_arr '||l_ins_task_flag_arr.count);
3662
3663
3664 ppr_log(' after api l_upd_task_arr.count ... '||l_upd_task_arr.count);
3665
3666 If ( p_fact_slice = 'CBS_TO_WBS' or p_fact_slice = 'CBS_TO_RBS' or p_fact_slice = 'WBS_TO_CBS_TO_RBS' ) then
3667
3668 ppr_log(' p_fact_slice '||p_fact_slice);
3669
3670
3671 FORALL i IN 1..p_fact_task_arr.count
3672 INSERT INTO pa_pjt_wbs_rbs_cbs_fact (
3673 ----
3674 PROJECT_ID , PROJECT_ELEMENT_ID
3675 , RBS_AGGR_LEVEL , WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
3676 , PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
3677 ----
3678 ,RAW_COST, BRDN_COST, REVENUE
3679 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3680 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3681 --------
3682 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3683 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3684 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3685 ----
3686 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3687 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3688 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3689 -------
3690 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3691 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3692 ----
3693 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3694 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3695 -----
3696 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3697 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
3698 ----
3699 , RBS_ELEMENT_ID, RBS_VERSION_ID
3700 , CBS_ELEMENT_ID, CBS_VERSION_ID
3701 )
3702 SELECT
3703 p_project_id ,DECODE( p_fact_slice,'CBS_TO_WBS',p_fact_task_arr(i),'WBS_TO_CBS_TO_RBS',p_3_level_object_id ,-1)
3704 -- ,'T','Y','N',p_currency_code --,0,0
3705 , G_RBS_AGGR_LEVEL , G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
3706 ,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
3707 ----
3708 , p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3709 ,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)
3710 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3711 -----
3712 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3713 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3714 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3715 ----
3716 ,0,0,0,0
3717 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3718 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3719 -------
3720 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3721 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3722 ----
3723 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3724 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3725 -------
3726 -------
3727 ,0,0,0,0,0,0,0,0,0,0,0
3728 ,0,0,0,0
3729 ,DECODE( p_fact_slice,'WBS_TO_CBS_TO_RBS',p_fact_task_arr(i),'CBS_TO_RBS',p_fact_task_arr(i),-1),decode(p_fact_slice,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id,-1)
3730 ,p_object_id, decode(p_fact_slice,'CBS_TO_WBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id_2,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id_2,-1)
3731 from dual
3732 where l_ins_task_flag_arr(i) = 'Y';
3733
3734
3735 elsIf ( p_fact_slice = 'RBS_TO_WBS' ) then
3736
3737 ppr_log(' p_fact_slice =RBS_TO_WBS p_rbs_cbs_version_id = '||p_rbs_cbs_version_id);
3738
3739 ppr_log(' SRIDHAR .... p_fact_task_arr '||p_fact_task_arr.count);
3740
3741 FORALL i IN 1..p_fact_task_arr.count
3742 INSERT INTO pa_pjt_wbs_rbs_cbs_fact (
3743 ----
3744 PROJECT_ID , PROJECT_ELEMENT_ID
3745 , RBS_AGGR_LEVEL , WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
3746 , PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
3747 ----
3748 ,RAW_COST, BRDN_COST, REVENUE
3749 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3750 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3751 --------
3752 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3753 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3754 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3755 ----
3756 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3757 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3758 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3759 -------
3760 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3761 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3762 ----
3763 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3764 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3765 -----
3766 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3767 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
3768 ----
3769 , RBS_ELEMENT_ID, RBS_VERSION_ID
3770 , CBS_ELEMENT_ID, CBS_VERSION_ID
3771 )
3772 SELECT
3773 p_project_id ,p_fact_task_arr(i)
3774 --,'T','Y','N',p_currency_code --,0,0
3775 , G_RBS_AGGR_LEVEL , G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
3776 ,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
3777 ----
3778 ----
3779 , p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3780 ,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)
3781 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3782 -----
3783 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3784 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3785 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3786 ----
3787 ,0,0,0,0
3788 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3789 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3790 -------
3791 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3792 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3793 ----
3794 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3795 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3796 -------
3797 -------
3798 ,0,0,0,0,0,0,0,0,0,0,0
3799 ,0,0,0,0
3800 ,p_object_id, p_rbs_cbs_version_id
3801 ,-1,-1
3802 from dual
3803 where l_ins_task_flag_arr(i) = 'Y';
3804
3805
3806
3807 elsIf ( p_fact_slice = 'WBS_TO_RBS' or p_fact_slice = 'WBS_TO_CBS' or p_fact_slice = 'CBS_TO_WBS_TO_RBS' ) then
3808
3809 ppr_log(' p_fact_slice =WBS_TO_RBS or WBS_TO_CBS ');
3810
3811
3812 FORALL i IN 1..p_fact_task_arr.count
3813 INSERT INTO pa_pjt_wbs_rbs_cbs_fact (
3814 ----
3815 PROJECT_ID , PROJECT_ELEMENT_ID
3816 , RBS_AGGR_LEVEL , WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
3817 , PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
3818 ----
3819 ,RAW_COST, BRDN_COST, REVENUE
3820 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3821 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3822 --------
3823 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3824 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3825 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3826 ----
3827 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3828 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3829 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3830 -------
3831 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3832 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3833 ----
3834 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3835 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3836 -----
3837 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3838 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
3839 ----
3840 , RBS_ELEMENT_ID, RBS_VERSION_ID
3841 , CBS_ELEMENT_ID, CBS_VERSION_ID
3842 )
3843 SELECT
3844 p_project_id ,p_object_id
3845 -- ,'T','Y','N',p_currency_code --,0,0
3846 , G_RBS_AGGR_LEVEL , G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
3847 ,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
3848 ----
3849 ----
3850 , p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3851 ,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)
3852 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3853 -----
3854 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3855 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3856 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3857 ----
3858 ,0,0,0,0
3859 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3860 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3861 -------
3862 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3863 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3864 ----
3868 -------
3865 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3866 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3867 -------
3869 ,0,0,0,0,0,0,0,0,0,0,0
3870 ,0,0,0,0
3871 ,DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_fact_task_arr(i),'WBS_TO_RBS',p_fact_task_arr(i),-1), DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_rbs_cbs_version_id,'WBS_TO_RBS',p_rbs_cbs_version_id,-1)
3872 ,DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_3_level_object_id , 'WBS_TO_CBS',p_fact_task_arr(i),-1), DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_3_level_version_id , 'WBS_TO_CBS',p_rbs_cbs_version_id,-1)
3873 from dual
3874 where l_ins_task_flag_arr(i) = 'Y';
3875
3876
3877 elsif ( p_fact_slice = 'WBS' ) then
3878
3879
3880 ppr_log(' p_fact_slice = WBS');
3881
3882 FORALL i IN 1..p_fact_task_arr.count
3883 INSERT INTO pa_pjt_wbs_fact (
3884 ----
3885 PROJECT_ID , PROJECT_ELEMENT_ID
3886 ,WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
3887 , PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
3888 ----
3889 ,RAW_COST, BRDN_COST, REVENUE
3890 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3891 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3892 --------
3893 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3894 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3895 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3896 ----
3897 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3898 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3899 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3900 -------
3901 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3902 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3903 ----
3904 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3905 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3906 -----
3907 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3908 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
3909 ----
3910 )
3911 SELECT
3912 p_project_id ,p_fact_task_arr(i)
3913 -- ,'Y','N',p_currency_code --,0,0
3914 , G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
3915 ,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
3916 ----
3917 ----
3918 , p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3919 ,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)
3920 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3921 -----
3922 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3923 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3924 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3925 ----
3926 ,0,0,0,0
3927 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3928 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3929 -------
3930 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3931 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3932 ----
3933 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3934 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3935 -------
3936 -------
3937 ,0,0,0,0,0,0,0,0,0,0,0
3938 ,0,0,0,0
3939 from dual
3940 where l_ins_task_flag_arr(i) = 'Y';
3941
3942 elsif ( p_fact_slice = 'RBS' or p_fact_slice = 'CBS' ) then
3943
3944 ppr_log(' p_fact_slice = RBS or CBS ');
3945
3946 FORALL i IN 1..p_fact_task_arr.count
3947 INSERT INTO pa_pjt_rbs_cbs_fact (
3948 ----
3949 PROJECT_ID , ROLLUP_TYPE
3950 , RBS_AGGR_LEVEL , PRG_ROLLUP_FLAG,CURRENCY_CODE,RBS_CBS_ELEMENT_ID, RBS_CBS_VERSION_ID
3951 , PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
3952 ----
3953 ,RAW_COST, BRDN_COST, REVENUE
3954 , bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
3955 , bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
3956 --------
3957 , CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
3958 , LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
3959 , EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
3960 ----
3961 , SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
3962 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
3963 , ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
3964 -------
3965 , ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
3966 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
3967 ----
3968 , ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
3969 , ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
3970 -----
3971 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
3972 , CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
3973 ----
3974 )
3975 SELECT
3976 p_project_id , p_fact_slice
3977 -- ,'T','N',p_currency_code,p_fact_task_arr(i),p_rbs_cbs_version_id
3978 , G_RBS_AGGR_LEVEL , G_PRG_ROLLUP_FLAG,p_currency_code ,p_fact_task_arr(i),p_rbs_cbs_version_id
3979 ,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
3980 ----
3981 ----
3982 , p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
3983 ,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)
3984 ,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
3985 -----
3986 ,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
3987 ,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
3988 , p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
3989 ----
3990 ,0,0,0,0
3991 ,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
3992 ,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
3993 -------
3994 ,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
3995 ,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
3996 ----
3997 ,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
3998 , p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
3999 -------
4000 -------
4001 ,0,0,0,0,0,0,0,0,0,0,0
4002 ,0,0,0,0
4003 from dual
4004 where l_ins_task_flag_arr(i) = 'Y';
4005
4006 end if;
4007
4008
4009 end merge_into_fact;
4010
4011
4012 End PA_ppr_rollup_PVT;