[Home] [Help]
PACKAGE BODY: APPS.PA_PPR_ROLLUP_ACTUAL_PVT
Source
1 package body PA_ppr_rollup_actual_PVT as
2 /* $Header: PAPPR03B.pls 120.0.12020000.4 2013/03/27 18:31:12 krkondur noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PPR_ROLLUP_ACTUAL_PVT';
5
6
7 procedure ppr_log (
8 p_msg IN VARCHAR2
9 ) as
10
11 begin
12 -- insert into sri_log values ( fnd_log_messages_s.nextval, p_msg);
13 --DBMS_OUTPUT.PUT_LINE(p_msg);
14 --commit;
15 null;
16 end ppr_log;
17
18
19
20 procedure actual_rollup (
21 p_commit IN VARCHAR2 default 'N'
22 ,p_calling_mode IN VARCHAR2 default 'FULL'
23 ,p_fact_slice IN VARCHAR2 default 'WBS'
24 ,p_debug_mode IN VARCHAR2 default 'N'
25 ,p_project_id In number default -1
26 ,p_budget_version_id in number default -1
27 ,p_rbs_version_id in number default -1
28 ,p_cbs_version_id in number default -1
29 ,p_proj_element_id in number default -1
30 ,p_rbs_element_id in number default -1
31 ,p_cbs_element_id in number default -1
32 ,x_return_status OUT NOCOPY VARCHAR2
33 ,x_msg_count OUT NOCOPY NUMBER
34 ,x_msg_data OUT NOCOPY VARCHAR2
35 ) as
36
37
38 l_return_status VARCHAR2(1);
39 l_msg_count NUMBER ;
40 l_msg_data VARCHAR2(250);
41
42 c number;
43
44 l_project_currency varchar2(10);
45 l_functional_currency varchar2(10);
46
47 l_version_type varchar2(15);
48
49 l_object_id number;
50
51 l_rbs_cbs_version_id_2 number;
52
53
54 l_struct_ver_id number;
55 l_project_id number;
56 l_rbs_cbs_version_id number ;
57 l_budget_version_id number;
58
59 l_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
60 l_task_level_arr PA_PLSQL_DATATYPES.NumTabTyp;
61 l_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
62 l_leaf_node_arr PA_PLSQL_DATATYPES.NumTabTyp;
63
64
65 l_prj_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
66 l_prj_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
67 l_prj_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
68 l_prj_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
69 l_prj_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
70 l_prj_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
71 l_prj_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
72 l_prj_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
73 l_prj_amount9_arr PA_PLSQL_DATATYPES.NumTabTyp;
74 l_prj_amount10_arr PA_PLSQL_DATATYPES.NumTabTyp;
75 l_prj_amount11_arr PA_PLSQL_DATATYPES.NumTabTyp;
76 l_prj_amount12_arr PA_PLSQL_DATATYPES.NumTabTyp;
77 l_prj_amount13_arr PA_PLSQL_DATATYPES.NumTabTyp;
78 l_prj_amount14_arr PA_PLSQL_DATATYPES.NumTabTyp;
79 l_prj_amount15_arr PA_PLSQL_DATATYPES.NumTabTyp;
80 l_prj_amount16_arr PA_PLSQL_DATATYPES.NumTabTyp;
81 l_prj_amount17_arr PA_PLSQL_DATATYPES.NumTabTyp;
82 l_prj_amount18_arr PA_PLSQL_DATATYPES.NumTabTyp;
83 l_prj_amount19_arr PA_PLSQL_DATATYPES.NumTabTyp;
84
85
86
87 l_func_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
88 l_func_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
89 l_func_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
90 l_func_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
91 l_func_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
92 l_func_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
93 l_func_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
94 l_func_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
95 l_func_amount9_arr PA_PLSQL_DATATYPES.NumTabTyp;
96 l_func_amount10_arr PA_PLSQL_DATATYPES.NumTabTyp;
97 l_func_amount11_arr PA_PLSQL_DATATYPES.NumTabTyp;
98 l_func_amount12_arr PA_PLSQL_DATATYPES.NumTabTyp;
99 l_func_amount13_arr PA_PLSQL_DATATYPES.NumTabTyp;
100 l_func_amount14_arr PA_PLSQL_DATATYPES.NumTabTyp;
101 l_func_amount15_arr PA_PLSQL_DATATYPES.NumTabTyp;
102 l_func_amount16_arr PA_PLSQL_DATATYPES.NumTabTyp;
103 l_func_amount17_arr PA_PLSQL_DATATYPES.NumTabTyp;
104 l_func_amount18_arr PA_PLSQL_DATATYPES.NumTabTyp;
105 l_func_amount19_arr PA_PLSQL_DATATYPES.NumTabTyp;
106
107
108
109 l_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
110 l_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
111 l_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
112
113 l_qty4_arr PA_PLSQL_DATATYPES.NumTabTyp;
114 l_qty5_arr PA_PLSQL_DATATYPES.NumTabTyp;
115 l_qty6_arr PA_PLSQL_DATATYPES.NumTabTyp;
116
117
118 l_out_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
119 l_out_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
120 l_out_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
121
122 l_out_qty4_arr PA_PLSQL_DATATYPES.NumTabTyp;
123 l_out_qty5_arr PA_PLSQL_DATATYPES.NumTabTyp;
124 l_out_qty6_arr PA_PLSQL_DATATYPES.NumTabTyp;
125
126
127 l_out_labor_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
128 l_out_labor_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
129 l_out_labor_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
130
131 l_out_equip_qty1_arr PA_PLSQL_DATATYPES.NumTabTyp;
132 l_out_equip_qty2_arr PA_PLSQL_DATATYPES.NumTabTyp;
133 l_out_equip_qty3_arr PA_PLSQL_DATATYPES.NumTabTyp;
134
135 l_out_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
136 l_upd_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
137
138 l_ins_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
139 l_upd_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
140
141 l_out_task_index_arr PA_PLSQL_DATATYPES.NumTabTyp;
142
143 l_out_amount1_arr PA_PLSQL_DATATYPES.NumTabTyp;
144 l_out_amount2_arr PA_PLSQL_DATATYPES.NumTabTyp;
145 l_out_amount3_arr PA_PLSQL_DATATYPES.NumTabTyp;
146 l_out_amount4_arr PA_PLSQL_DATATYPES.NumTabTyp;
147 l_out_amount5_arr PA_PLSQL_DATATYPES.NumTabTyp;
148 l_out_amount6_arr PA_PLSQL_DATATYPES.NumTabTyp;
149 l_out_amount7_arr PA_PLSQL_DATATYPES.NumTabTyp;
150 l_out_amount8_arr PA_PLSQL_DATATYPES.NumTabTyp;
151 l_out_amount9_arr PA_PLSQL_DATATYPES.NumTabTyp;
152 l_out_amount10_arr PA_PLSQL_DATATYPES.NumTabTyp;
153 l_out_amount11_arr PA_PLSQL_DATATYPES.NumTabTyp;
154 l_out_amount12_arr PA_PLSQL_DATATYPES.NumTabTyp;
158 l_out_amount16_arr PA_PLSQL_DATATYPES.NumTabTyp;
155 l_out_amount13_arr PA_PLSQL_DATATYPES.NumTabTyp;
156 l_out_amount14_arr PA_PLSQL_DATATYPES.NumTabTyp;
157 l_out_amount15_arr PA_PLSQL_DATATYPES.NumTabTyp;
159 l_out_amount17_arr PA_PLSQL_DATATYPES.NumTabTyp;
160 l_out_amount18_arr PA_PLSQL_DATATYPES.NumTabTyp;
161 l_out_amount19_arr PA_PLSQL_DATATYPES.NumTabTyp;
162
163
164 l_res_class_arr PA_PLSQL_DATATYPES.Char30TabTyp;
165
166 -- Open Item : Can we sum the resource assignments at task level here itself ? Will the sum scale ?
167
168 cursor RBS_INC_FULL_CUR is
169 select
170 -- tmp7.TXN_ACCUM_HEADER_ID,
171 -- tmp7.PROJECT_ID,
172 tmp7.element_id ,
173 ------------------
174 sum(tmp7.PRJ_RAW_COST),
175 sum(tmp7.PRJ_BILL_RAW_COST),
176 sum(tmp7.PRJ_BRDN_COST),
177 sum(tmp7.PRJ_BILL_BRDN_COST),
178 sum(tmp7.PRJ_REVENUE),
179 --------------
180 sum( tmp7.LABOR_PRJ_RAW_COST) ,
181 sum( tmp7.LABOR_PRJ_BILL_RAW_COST),
182 sum( tmp7.LABOR_PRJ_BRDN_COST),
183 sum( tmp7.LABOR_PRJ_BILL_BRDN_COST),
184 sum( tmp7.LABOR_PRJ_REVENUE),
185 --------------------
186 sum(tmp7.EQUIP_PRJ_RAW_COST ),
187 sum(tmp7.EQUIP_PRJ_BRDN_COST),
188 ------------------
189 sum(tmp7.PRJ_SUP_INV_COMMITTED_COST),
190 sum(tmp7.PRJ_PO_COMMITTED_COST),
191 sum(tmp7.PRJ_PR_COMMITTED_COST),
192 sum(tmp7.PRJ_OTH_COMMITTED_COST),
193 ------------------- BELOW POU---------------------------
194 sum(tmp7.POU_RAW_COST),
195 sum(tmp7.POU_BILL_RAW_COST),
196 sum(tmp7.POU_BRDN_COST),
197 sum(tmp7.POU_BILL_BRDN_COST),
198 sum(tmp7.POU_REVENUE),
199 ----------------------
200 sum( tmp7.LABOR_POU_RAW_COST ) ,
201 sum( tmp7.LABOR_POU_BILL_RAW_COST),
202 sum( tmp7.LABOR_POU_BRDN_COST),
203 sum( tmp7.LABOR_POU_BILL_BRDN_COST),
204 sum( tmp7.LABOR_POU_REVENUE),
205 --------------------
206 sum( tmp7.EQUIP_POU_RAW_COST ),
207 sum( tmp7.EQUIP_POU_BRDN_COST),
208 ---------------------
209 sum(tmp7.POU_SUP_INV_COMMITTED_COST),
210 sum(tmp7.POU_PO_COMMITTED_COST),
211 sum(tmp7.POU_PR_COMMITTED_COST),
212 sum(tmp7.POU_OTH_COMMITTED_COST),
213 ----------------------
214 sum( tmp7.LABOR_QUANTITY),
215 sum( tmp7.LABOR_BILL_QUANTITY),
216 ----------------------
217 sum( tmp7.EQUIP_QUANTITY),
218 sum( tmp7.EQUIP_BILL_QUANTITY),
219 ----------------------
220 sum(tmp7.QUANTITY),
221 sum(tmp7.BILL_QUANTITY),
222 ----------------------
223 b.connect_path ,
224 b.leaf_node ,
225 b.wbs_rbs_level,
226 0,0,0,
227 0,0,0
228 from
229 (
230 SELECT
231 map1.element_id , ---tmp7.TASK_ID,
232 nvl(tmp7.PRJ_RAW_COST,0) PRJ_RAW_COST ,
233 nvl(tmp7.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
234 nvl(tmp7.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
235 nvl(tmp7.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
236 nvl(tmp7.PRJ_REVENUE,0) PRJ_REVENUE,
237 --------------
238 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
239 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
240 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
241 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
242 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
243 --------------------
244 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
245 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
246 ------------------
247 nvl(tmp7.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
248 nvl(tmp7.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
249 nvl(tmp7.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
250 nvl(tmp7.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
251 ------------------- BELOW POU---------------------------
252 nvl(tmp7.POU_RAW_COST,0) POU_RAW_COST,
253 nvl(tmp7.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
254 nvl(tmp7.POU_BRDN_COST,0) POU_BRDN_COST,
255 nvl(tmp7.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
256 nvl(tmp7.POU_REVENUE,0) POU_REVENUE,
257 ----------------------
258 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
259 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
260 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
261 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
262 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_REVENUE,0),0) LABOR_POU_REVENUE,
263 --------------------
264 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
265 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
266 ---------------------
267 nvl(tmp7.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
268 nvl(tmp7.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
269 nvl(tmp7.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
273 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
270 nvl(tmp7.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
271 ----------------------
272 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.QUANTITY,0),0) LABOR_QUANTITY,
274 ----------------------
275 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.QUANTITY,0),0) EQUIP_QUANTITY,
276 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
277 ----------------------
278 nvl(tmp7.QUANTITY,0) QUANTITY,
279 nvl(tmp7.BILL_QUANTITY,0) BILL_QUANTITY
280 FROM
281 PJI_FM_AGGR_FIN7 tmp7,
282 PA_RBS_TXN_ACCUM_MAP map1
283 WHERE project_id = p_project_id
284 AND p_calling_mode IN ( 'INCREMENTAL' , 'FULL' )
285 AND recvr_period_type = 'GL'
286 AND map1.txn_accum_header_id = tmp7.txn_accum_header_id
287 AND map1.struct_version_id = l_rbs_cbs_version_id
288 UNION ALL
289 SELECT
290 map1.element_id, --accum.TASK_ID,
291 nvl(accum.PRJ_RAW_COST,0) PRJ_RAW_COST ,
292 nvl(accum.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
293 nvl(accum.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
294 nvl(accum.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
295 nvl(accum.PRJ_REVENUE,0) PRJ_REVENUE,
296 --------------
297 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
298 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
299 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
300 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
301 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
302 --------------------
303 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
304 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
305 ------------------
306 0 PRJ_SUP_INV_COMMITTED_COST,
307 0 PRJ_PO_COMMITTED_COST,
308 0 PRJ_PR_COMMITTED_COST,
309 0 PRJ_OTH_COMMITTED_COST,
310 ------------------- BELOW POU---------------------------
311 nvl(accum.POU_RAW_COST,0) POU_RAW_COST,
312 nvl(accum.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
313 nvl(accum.POU_BRDN_COST,0) POU_BRDN_COST,
314 nvl(accum.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
315 nvl(accum.POU_REVENUE,0) POU_REVENUE,
316 ----------------------
317 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
318 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
319 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
320 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
321 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_REVENUE,0),0) LABOR_POU_REVENUE,
322 --------------------
323 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
324 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
325 ---------------------
326 0 POU_SUP_INV_COMMITTED_COST,
327 0 POU_PO_COMMITTED_COST,
328 0 POU_PR_COMMITTED_COST,
329 0 POU_OTH_COMMITTED_COST,
330 ----------------------
331 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.QUANTITY,0),0) LABOR_QUANTITY,
332 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
333 ----------------------
334 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.QUANTITY,0),0) EQUIP_QUANTITY,
335 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
336 ----------------------
337 nvl(accum.QUANTITY,0) QUANTITY,
338 nvl(accum.BILL_QUANTITY,0) BILL_QUANTITY
339 FROM
340 PJI_FP_TXN_ACCUM accum,
341 PA_RBS_TXN_ACCUM_MAP map1
342 WHERE project_id = p_project_id
343 AND p_calling_mode = 'FULL'
344 AND recvr_period_type = 'GL'
345 AND map1.txn_accum_header_id = accum.txn_accum_header_id
346 AND map1.struct_version_id = l_rbs_cbs_version_id
347 UNION ALL
348 SELECT
349 map1.element_id, ---- accum1.TASK_ID,
350 0 PRJ_RAW_COST ,
351 0 PRJ_BILL_RAW_COST ,
352 0 PRJ_BRDN_COST,
353 0 PRJ_BILL_BRDN_COST,
354 0 PRJ_REVENUE,
355 --------------
356 0 LABOR_PRJ_RAW_COST ,
357 0 LABOR_PRJ_BILL_RAW_COST,
358 0 LABOR_PRJ_BRDN_COST,
359 0 LABOR_PRJ_BILL_BRDN_COST,
360 0 LABOR_PRJ_REVENUE,
361 --------------------
362 0 EQUIP_PRJ_RAW_COST,
363 0 EQUIP_PRJ_BRDN_COST,
364 ------------------
365 nvl(accum1.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
366 nvl(accum1.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
367 nvl(accum1.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
368 nvl(accum1.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
369 ------------------- BELOW POU---------------------------
370 0 POU_RAW_COST,
371 0 POU_BILL_RAW_COST,
372 0 POU_BRDN_COST,
373 0 POU_BILL_BRDN_COST,
374 0 POU_REVENUE,
378 0 LABOR_POU_BRDN_COST,
375 ----------------------
376 0 LABOR_POU_RAW_COST ,
377 0 LABOR_POU_BILL_RAW_COST,
379 0 LABOR_POU_BILL_BRDN_COST,
380 0 LABOR_POU_REVENUE,
381 --------------------
382 0 EQUIP_POU_RAW_COST,
383 0 EQUIP_POU_BRDN_COST,
384 ---------------------
385 nvl(accum1.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
386 nvl(accum1.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
387 nvl(accum1.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
388 nvl(accum1.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
389 ----------------------
390 0 LABOR_QUANTITY,
391 0 LABOR_BILL_QUANTITY,
392 ----------------------
393 0 EQUIP_QUANTITY,
394 0 EQUIP_BILL_QUANTITY,
395 ----------------------
396 0 QUANTITY,
397 0 BILL_QUANTITY
398 FROM
399 PJI_FP_TXN_ACCUM1 accum1,
400 PA_RBS_TXN_ACCUM_MAP map1
401 WHERE project_id = p_project_id
402 AND p_calling_mode = 'FULL'
403 AND recvr_period_type = 'GL'
404 AND map1.txn_accum_header_id = accum1.txn_accum_header_id
405 AND map1.struct_version_id = l_rbs_cbs_version_id
406 ) tmp7 ,
407 pa_ppr_obj_tmp b
408 where
409 tmp7.element_id = b.object_id
410 group by -- tmp7.TXN_ACCUM_HEADER_ID,
411 -- tmp7.PROJECT_ID,
412 tmp7.element_id,
413 b.connect_path ,
414 b.leaf_node ,
415 b.wbs_rbs_level;
416
417
418
419 cursor WBS_INC_FULL_CUR is
420 select
421 -- tmp7.TXN_ACCUM_HEADER_ID,
422 -- tmp7.PROJECT_ID,
423 tmp7.TASK_ID ,
424 ------------------
425 sum(tmp7.PRJ_RAW_COST),
426 sum(tmp7.PRJ_BILL_RAW_COST),
427 sum(tmp7.PRJ_BRDN_COST),
428 sum(tmp7.PRJ_BILL_BRDN_COST),
429 sum(tmp7.PRJ_REVENUE),
430 --------------
431 sum( tmp7.LABOR_PRJ_RAW_COST) ,
432 sum( tmp7.LABOR_PRJ_BILL_RAW_COST),
433 sum( tmp7.LABOR_PRJ_BRDN_COST),
434 sum( tmp7.LABOR_PRJ_BILL_BRDN_COST),
435 sum( tmp7.LABOR_PRJ_REVENUE),
436 --------------------
437 sum(tmp7.EQUIP_PRJ_RAW_COST ),
438 sum(tmp7.EQUIP_PRJ_BRDN_COST),
439 ------------------
440 sum(tmp7.PRJ_SUP_INV_COMMITTED_COST),
441 sum(tmp7.PRJ_PO_COMMITTED_COST),
442 sum(tmp7.PRJ_PR_COMMITTED_COST),
443 sum(tmp7.PRJ_OTH_COMMITTED_COST),
444 ------------------- BELOW POU---------------------------
445 sum(tmp7.POU_RAW_COST),
446 sum(tmp7.POU_BILL_RAW_COST),
447 sum(tmp7.POU_BRDN_COST),
448 sum(tmp7.POU_BILL_BRDN_COST),
449 sum(tmp7.POU_REVENUE),
450 ----------------------
451 sum( tmp7.LABOR_POU_RAW_COST ) ,
452 sum( tmp7.LABOR_POU_BILL_RAW_COST),
453 sum( tmp7.LABOR_POU_BRDN_COST),
454 sum( tmp7.LABOR_POU_BILL_BRDN_COST),
455 sum( tmp7.LABOR_POU_REVENUE),
456 --------------------
457 sum( tmp7.EQUIP_POU_RAW_COST ),
458 sum( tmp7.EQUIP_POU_BRDN_COST),
459 ---------------------
460 sum(tmp7.POU_SUP_INV_COMMITTED_COST),
461 sum(tmp7.POU_PO_COMMITTED_COST),
462 sum(tmp7.POU_PR_COMMITTED_COST),
463 sum(tmp7.POU_OTH_COMMITTED_COST),
464 ----------------------
465 sum( tmp7.LABOR_QUANTITY),
466 sum( tmp7.LABOR_BILL_QUANTITY),
467 ----------------------
468 sum( tmp7.EQUIP_QUANTITY),
469 sum( tmp7.EQUIP_BILL_QUANTITY),
470 ----------------------
471 sum(tmp7.QUANTITY),
472 sum(tmp7.BILL_QUANTITY),
473 ----------------------
474 b.connect_path ,
475 b.leaf_node ,
476 b.wbs_rbs_level,
477 0,0,0,
478 0,0,0
479 from
480 (
481 SELECT
482 tmp7.TASK_ID,
483 nvl(tmp7.PRJ_RAW_COST,0) PRJ_RAW_COST ,
484 nvl(tmp7.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
485 nvl(tmp7.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
486 nvl(tmp7.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
487 nvl(tmp7.PRJ_REVENUE,0) PRJ_REVENUE,
488 --------------
489 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
490 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
491 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
492 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
493 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
494 --------------------
495 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
496 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
497 ------------------
498 nvl(tmp7.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
499 nvl(tmp7.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
500 nvl(tmp7.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
501 nvl(tmp7.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
502 ------------------- BELOW POU---------------------------
503 nvl(tmp7.POU_RAW_COST,0) POU_RAW_COST,
507 nvl(tmp7.POU_REVENUE,0) POU_REVENUE,
504 nvl(tmp7.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
505 nvl(tmp7.POU_BRDN_COST,0) POU_BRDN_COST,
506 nvl(tmp7.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
508 ----------------------
509 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
510 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
511 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
512 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
513 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_REVENUE,0),0) LABOR_POU_REVENUE,
514 --------------------
515 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
516 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
517 ---------------------
518 nvl(tmp7.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
519 nvl(tmp7.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
520 nvl(tmp7.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
521 nvl(tmp7.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
522 ----------------------
523 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.QUANTITY,0),0) LABOR_QUANTITY,
524 decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
525 ----------------------
526 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.QUANTITY,0),0) EQUIP_QUANTITY,
527 decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
528 ----------------------
529 nvl(tmp7.QUANTITY,0) QUANTITY,
530 nvl(tmp7.BILL_QUANTITY,0) BILL_QUANTITY
531 FROM
532 PJI_FM_AGGR_FIN7 tmp7
533 WHERE project_id = p_project_id
534 AND p_calling_mode = 'INCREMENTAL'
535 AND recvr_period_type = 'GL'
536 UNION ALL
537 SELECT
538 accum.TASK_ID,
539 nvl(accum.PRJ_RAW_COST,0) PRJ_RAW_COST ,
540 nvl(accum.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
541 nvl(accum.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
542 nvl(accum.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
543 nvl(accum.PRJ_REVENUE,0) PRJ_REVENUE,
544 --------------
545 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
546 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
547 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
548 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
549 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
550 --------------------
551 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
552 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
553 ------------------
554 0 PRJ_SUP_INV_COMMITTED_COST,
555 0 PRJ_PO_COMMITTED_COST,
556 0 PRJ_PR_COMMITTED_COST,
557 0 PRJ_OTH_COMMITTED_COST,
558 ------------------- BELOW POU---------------------------
559 nvl(accum.POU_RAW_COST,0) POU_RAW_COST,
560 nvl(accum.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
561 nvl(accum.POU_BRDN_COST,0) POU_BRDN_COST,
562 nvl(accum.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
563 nvl(accum.POU_REVENUE,0) POU_REVENUE,
564 ----------------------
565 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
566 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
567 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
568 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
569 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_REVENUE,0),0) LABOR_POU_REVENUE,
570 --------------------
571 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
572 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
573 ---------------------
574 0 POU_SUP_INV_COMMITTED_COST,
575 0 POU_PO_COMMITTED_COST,
576 0 POU_PR_COMMITTED_COST,
577 0 POU_OTH_COMMITTED_COST,
578 ----------------------
579 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.QUANTITY,0),0) LABOR_QUANTITY,
580 decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
581 ----------------------
582 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.QUANTITY,0),0) EQUIP_QUANTITY,
583 decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
584 ----------------------
585 nvl(accum.QUANTITY,0) QUANTITY,
586 nvl(accum.BILL_QUANTITY,0) BILL_QUANTITY
587 FROM
588 PJI_FP_TXN_ACCUM accum
589 WHERE project_id = p_project_id
590 AND p_calling_mode = 'FULL'
591 AND recvr_period_type = 'GL'
592 UNION ALL
593 SELECT
594 accum1.TASK_ID,
595 0 PRJ_RAW_COST ,
596 0 PRJ_BILL_RAW_COST ,
597 0 PRJ_BRDN_COST,
598 0 PRJ_BILL_BRDN_COST,
599 0 PRJ_REVENUE,
600 --------------
601 0 LABOR_PRJ_RAW_COST ,
602 0 LABOR_PRJ_BILL_RAW_COST,
606 --------------------
603 0 LABOR_PRJ_BRDN_COST,
604 0 LABOR_PRJ_BILL_BRDN_COST,
605 0 LABOR_PRJ_REVENUE,
607 0 EQUIP_PRJ_RAW_COST,
608 0 EQUIP_PRJ_BRDN_COST,
609 ------------------
610 nvl(accum1.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
611 nvl(accum1.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
612 nvl(accum1.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
613 nvl(accum1.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
614 ------------------- BELOW POU---------------------------
615 0 POU_RAW_COST,
616 0 POU_BILL_RAW_COST,
617 0 POU_BRDN_COST,
618 0 POU_BILL_BRDN_COST,
619 0 POU_REVENUE,
620 ----------------------
621 0 LABOR_POU_RAW_COST ,
622 0 LABOR_POU_BILL_RAW_COST,
623 0 LABOR_POU_BRDN_COST,
624 0 LABOR_POU_BILL_BRDN_COST,
625 0 LABOR_POU_REVENUE,
626 --------------------
627 0 EQUIP_POU_RAW_COST,
628 0 EQUIP_POU_BRDN_COST,
629 ---------------------
630 nvl(accum1.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
631 nvl(accum1.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
632 nvl(accum1.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
633 nvl(accum1.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
634 ----------------------
635 0 LABOR_QUANTITY,
636 0 LABOR_BILL_QUANTITY,
637 ----------------------
638 0 EQUIP_QUANTITY,
639 0 EQUIP_BILL_QUANTITY,
640 ----------------------
641 0 QUANTITY,
642 0 BILL_QUANTITY
643 FROM
644 PJI_FP_TXN_ACCUM1 accum1
645 WHERE project_id = p_project_id
646 AND p_calling_mode = 'FULL'
647 AND recvr_period_type = 'GL'
648 ) tmp7 ,
649 pa_ppr_obj_tmp b
650 where
651 tmp7.task_id = b.object_id
652 group by -- tmp7.TXN_ACCUM_HEADER_ID,
653 -- tmp7.PROJECT_ID,
654 tmp7.TASK_ID,
655 b.connect_path ,
656 b.leaf_node ,
657 b.wbs_rbs_level;
658
659
660 cursor c0 is
661 SELECT object_id
662 FROM pa_ppr_obj_tmp2 r
663 START WITH r.parent_object_id = l_object_id CONNECT BY
664 PRIOR r.object_id = r.parent_object_id ;
665
666 cursor c1 is
667 SELECT object_id , LEVEL ,
668 SYS_CONNECT_BY_PATH(object_id, '/') connect_path,
669 connect_by_isleaf leaf_node
670 FROM pa_ppr_obj_tmp r
671 START WITH r.parent_object_id is null CONNECT BY
672 PRIOR r.object_id = r.parent_object_id ;
673
674
675
676
677
678 begin
679
680 ppr_log('START ... ');
681
682 ppr_log(' p_commit '|| p_commit );
683 ppr_log(' p_calling_mode '|| p_calling_mode );
684 ppr_log(' p_fact_slice '|| p_fact_slice );
685 ppr_log(' p_debug_mode '|| p_debug_mode );
686 ppr_log(' p_project_id '|| p_project_id );
687 ppr_log(' p_budget_version_id '|| p_budget_version_id );
688 ppr_log(' p_rbs_version_id '|| p_rbs_version_id );
689 ppr_log(' p_cbs_version_id '|| p_cbs_version_id );
690 ppr_log(' p_proj_element_id '|| p_proj_element_id );
691 ppr_log(' p_rbs_element_id '|| p_rbs_element_id );
692 ppr_log(' p_cbs_element_id '|| p_cbs_element_id );
693
694
695
696 l_project_id := p_project_id;
697 l_budget_version_id := p_budget_version_id;
698
699 if ( p_fact_slice in ( 'RBS', 'WBS_TO_RBS', 'CBS_TO_RBS' ,'RBS_TO_WBS') ) then
700
701 l_rbs_cbs_version_id := p_rbs_version_id ;
702
703
704 elsif ( p_fact_slice in ( 'CBS','WBS_TO_CBS','CBS_TO_WBS' ) ) then
705
706 l_rbs_cbs_version_id := p_cbs_version_id ;
707
708
709 else
710 l_rbs_cbs_version_id := -1;
711 end if;
712
713
714 if ( p_fact_slice in ( 'WBS_TO_RBS', 'WBS_TO_CBS' ) ) then
715
716 l_object_id := p_proj_element_id ;
717
718 elsif ( p_fact_slice in ( 'CBS_TO_RBS', 'CBS_TO_WBS' ) ) then
719
720 l_object_id := p_cbs_element_id ;
721
722 elsif ( p_fact_slice in ( 'RBS_TO_WBS' ) ) then
723
724 l_object_id := p_rbs_element_id ;
725
726 end if;
727
728 -- l_project_id := 4546;
729 -- l_struct_ver_id := 94014;
730
731 -- l_budget_version_id := 168752;
732
733 -- Project level data
734 select project_currency_code , projfunc_currency_code
735 into l_project_currency, l_functional_currency
736 from pa_projects_All
737 where project_id = l_project_id;
738
739 --l_functional_currency := 'GBP';
740
741 if ( l_budget_version_id <> -1 ) then
742
743 select /*+ ordered */ distinct decode(bud.fin_plan_type_id,10,'WORKPLAN','FINANCIAL') , s.element_version_id
744 into l_version_type , l_struct_ver_id
745 from pa_budget_versions bud,
746 pa_proj_elem_ver_structure s,
747 pa_proj_structure_types ppst,
748 pa_structure_types pst
749 where
750 bud.budget_version_id = l_budget_version_id
751 and s.proj_element_id = ppst.proj_element_id (+)
752 and ppst.structure_type_id = pst.structure_type_id (+)
756 end if;
753 and pst.structure_type_class_code (+) = 'WORKPLAN'
754 and bud.project_structure_version_id = s.element_version_id (+);
755
757
758 -- l_functional_currency := 'GBP';
759
760 --- Scenodary slice task / rbs / cbs list
761
762 delete from pa_ppr_obj_tmp2;
763
764 if ( p_fact_slice in ( 'WBS_TO_RBS' , 'WBS_TO_CBS' ) ) then
765
766 if ( l_version_type = 'WORKPLAN' ) then
767
768 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
769 select /*+ ordered */
770 a.proj_element_id , nvl(c.proj_element_id,0)
771 from PA_PROJ_ELEMENT_VERSIONS a , pa_tasks c , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
772 where a.project_id = l_project_id
773 and a.object_type = 'PA_TASK' -- SRI_BUG add this new condition
774 and a.parent_structure_version_id = l_struct_ver_id
775 and a.proj_element_id = c.task_id
776 and a.element_version_id = b.object_id_to1 (+)
777 and b.object_type_From (+) = 'PA_TASKS'
778 and b.relationship_type (+) = 'S'
779 and b.object_id_from1 = c.element_version_id (+) ;
780
781 elsif ( l_version_type = 'FINANCIAL' ) then
782
783 -- For financial tasks and fully shared.
784 -- For budgets pa_tasks can be used directly
785
786 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
787 select c.task_id , nvl(c.parent_task_id,0)
788 from pa_tasks c
789 where c.project_id = l_project_id ;
790
791 end if;
792
793 elsif p_fact_slice in ( 'RBS_TO_WBS','CBS_TO_RBS', 'CBS_TO_WBS' ) then
794
795 if ( p_fact_slice like 'RBS%' ) then
796
797 l_rbs_cbs_version_id_2 := p_rbs_version_id ;
798
799 else
800
801 l_rbs_cbs_version_id_2 := p_cbs_version_id ;
802
803 end if;
804
805
806 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
807 select rbs_element_id , nvl(parent_element_id,0)
808 from pa_rbs_elements
809 where rbs_version_id = l_rbs_cbs_version_id_2 ;
810
811 end if;
812
813 if ( p_fact_slice in ( 'RBS_TO_WBS','CBS_TO_RBS', 'CBS_TO_WBS' , 'WBS_TO_RBS' , 'WBS_TO_CBS' ) ) then
814
815
816 /* l_task_arr.delete;
817
818 SELECT object_id
819 BULK COLLECT INTO l_task_arr
820 FROM pa_ppr_obj_tmp2;
821
822 ppr_log('SECONDARY SOURCE l_task_arr '||l_task_arr.count);
823 */
824 l_task_arr.delete;
825
826 OPEN c0;
827
828 FETCH c0
829 BULK COLLECT INTO l_task_arr;
830
831 CLOSE c0;
832
833 ppr_log('SECONDARY l_task_arr '||l_task_arr.count);
834
835 delete from pa_ppr_obj_tmp2;
836
837 FORALL i IN 1..l_out_task_arr.count
838 INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_out_task_arr(i) ) ;
839
840 l_task_arr.delete;
841
842 INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_object_id ) ;
843
844
845 end if;
846
847 -- TASK Hierarchy details
848
849 delete from pa_ppr_obj_tmp;
850
851 if ( p_fact_slice in ( 'WBS' ) ) then
852
853 ppr_log('into WBS ');
854 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
855 select c.task_id , nvl(c.parent_task_id,0)
856 from pa_tasks c
857 where c.project_id = l_project_id ;
858
859
860 elsif p_fact_slice in ( 'RBS' , 'CBS' ) then
861
862 ppr_log('into RBS ');
863
864 insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
865 select rbs_element_id , nvl(parent_element_id,0)
866 from pa_rbs_elements
867 where rbs_version_id = l_rbs_cbs_version_id ;
868
869 end if;
870
871 insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( 0 , null );
872
873
874 OPEN c1;
875
876 FETCH c1
877 BULK COLLECT INTO l_task_arr, l_task_level_arr,l_connect_path_arr , l_leaf_node_arr ;
878
879 CLOSE c1;
880
881 FORALL k IN 1..l_task_arr.COUNT
882 UPDATE pa_ppr_obj_tmp
883 SET connect_path = l_connect_path_arr(k),
884 leaf_node = l_leaf_node_arr(k),
885 wbs_rbs_level = l_task_level_arr(k)
886 WHERE object_id = l_task_arr(k);
887
888
889 ppr_log(' TASK CONNECT BY l_task_arr.COUNT ... '||l_task_arr.COUNT);
890
891 l_task_arr.delete;
892 l_task_level_arr.delete;
893 l_connect_path_arr.delete;
894 l_leaf_node_arr.delete;
895
896 ppr_log('AFTER TASK ... ');
897
898 l_prj_amount1_arr.delete; l_prj_amount2_arr.delete; l_prj_amount3_arr.delete;
899 l_prj_amount4_arr.delete; l_prj_amount5_arr.delete;
900 l_prj_amount6_arr.delete; l_prj_amount7_arr.delete; l_prj_amount8_arr.delete;
901 l_prj_amount9_arr.delete; l_prj_amount10_arr.delete;
902 -------
903 l_prj_amount11_arr.delete; l_prj_amount12_arr.delete;
904 l_prj_amount13_arr.delete; l_prj_amount14_arr.delete; l_prj_amount15_arr.delete;
905 l_prj_amount16_arr.delete;
906 -------
907 l_func_amount1_arr.delete; l_func_amount2_arr.delete; l_func_amount3_arr.delete;
908 l_func_amount4_arr.delete; l_func_amount5_arr.delete;
912 l_func_amount11_arr.delete; l_func_amount12_arr.delete;
909 l_func_amount6_arr.delete; l_func_amount7_arr.delete; l_func_amount8_arr.delete;
910 l_func_amount9_arr.delete; l_func_amount10_arr.delete;
911 -------
913 l_func_amount13_arr.delete; l_func_amount14_arr.delete; l_prj_amount15_arr.delete;
914 l_func_amount16_arr.delete;
915 l_func_amount17_arr.delete; l_func_amount18_arr.delete; l_prj_amount19_arr.delete;
916 -------
917 l_qty1_arr.delete; l_qty2_arr.delete;
918 l_qty3_arr.delete; l_qty4_arr.delete;
919 l_qty5_arr.delete; l_qty6_arr.delete;
920
921
922 if ( p_fact_slice = 'WBS') then
923
924 open WBS_INC_FULL_CUR;
925
926 FETCH WBS_INC_FULL_CUR
927 BULK COLLECT INTO
928 l_task_arr,
929 -------
930 l_prj_amount1_arr ,l_prj_amount2_arr ,l_prj_amount3_arr ,l_prj_amount4_arr ,l_prj_amount5_arr ,
931 l_prj_amount6_arr ,l_prj_amount7_arr ,l_prj_amount8_arr ,l_prj_amount9_arr ,l_prj_amount10_arr ,
932 -------
933 l_prj_amount11_arr ,l_prj_amount12_arr ,
934 l_prj_amount13_arr ,l_prj_amount14_arr ,l_prj_amount15_arr ,l_prj_amount16_arr,
935 -------
936 l_func_amount1_arr ,l_func_amount2_arr ,l_func_amount3_arr ,l_func_amount4_arr ,l_func_amount5_arr ,
937 l_func_amount6_arr ,l_func_amount7_arr ,l_func_amount8_arr ,l_func_amount9_arr ,l_func_amount10_arr ,
938 -------
939 l_func_amount11_arr ,l_func_amount12_arr ,
940 l_func_amount13_arr ,l_func_amount14_arr ,l_prj_amount15_arr ,l_func_amount16_arr,
941 -------
942 l_qty1_arr,l_qty2_arr,
943 l_qty3_arr,l_qty4_arr,
944 l_qty5_arr,l_qty6_arr,
945 -------
946 l_connect_path_arr ,
947 l_leaf_node_arr ,
948 l_task_level_arr,
949 l_prj_amount17_arr ,l_prj_amount18_arr ,l_prj_amount19_arr,
950 l_func_amount17_arr ,l_func_amount18_arr ,l_prj_amount19_arr;
951
952
953 CLOSE WBS_INC_FULL_CUR;
954
955 elsif ( p_fact_slice = 'RBS') then
956
957 open RBS_INC_FULL_CUR;
958
959 FETCH RBS_INC_FULL_CUR
960 BULK COLLECT INTO
961 l_task_arr,
962 -------
963 l_prj_amount1_arr ,l_prj_amount2_arr ,l_prj_amount3_arr ,l_prj_amount4_arr ,l_prj_amount5_arr ,
964 l_prj_amount6_arr ,l_prj_amount7_arr ,l_prj_amount8_arr ,l_prj_amount9_arr ,l_prj_amount10_arr ,
965 -------
966 l_prj_amount11_arr ,l_prj_amount12_arr ,
967 l_prj_amount13_arr ,l_prj_amount14_arr ,l_prj_amount15_arr ,l_prj_amount16_arr,
968 -------
969 l_func_amount1_arr ,l_func_amount2_arr ,l_func_amount3_arr ,l_func_amount4_arr ,l_func_amount5_arr ,
970 l_func_amount6_arr ,l_func_amount7_arr ,l_func_amount8_arr ,l_func_amount9_arr ,l_func_amount10_arr ,
971 -------
972 l_func_amount11_arr ,l_func_amount12_arr ,
973 l_func_amount13_arr ,l_func_amount14_arr ,l_prj_amount15_arr ,l_func_amount16_arr,
974 -------
975 l_qty1_arr,l_qty2_arr,
976 l_qty3_arr,l_qty4_arr,
977 l_qty5_arr,l_qty6_arr,
978 -------
979 l_connect_path_arr ,
980 l_leaf_node_arr ,
981 l_task_level_arr,
982 l_prj_amount17_arr ,l_prj_amount18_arr ,l_prj_amount19_arr,
983 l_func_amount17_arr ,l_func_amount18_arr ,l_prj_amount19_arr;
984
985
986 CLOSE RBS_INC_FULL_CUR;
987
988 end if;
989
990
991
992 ppr_log(' l_task_arr.COUNT ... '||l_task_arr.COUNT);
993
994
995 rollup_amounts (
996 p_object_id_tab => l_task_arr
997 ,p_wbs_level_tab => l_task_level_arr
998 ,p_connect_path_tab => l_connect_path_arr
999 ,p_leaf_node_tab => l_leaf_node_arr
1000 ,p_resource_class_tab => l_res_class_arr
1001 ,p_amount1_tab => l_prj_amount1_arr
1002 ,p_amount2_tab => l_prj_amount2_arr
1003 ,p_amount3_tab => l_prj_amount3_arr
1004 ,p_amount4_tab => l_prj_amount4_arr
1005 ,p_amount5_tab => l_prj_amount5_arr
1006 ,p_amount6_tab => l_prj_amount6_arr
1007 ,p_amount7_tab => l_prj_amount7_arr
1008 ,p_amount8_tab => l_prj_amount8_arr
1009 ,p_amount9_tab => l_prj_amount9_arr
1010 ,p_amount10_tab => l_prj_amount10_arr
1011 ,p_amount11_tab => l_prj_amount11_arr
1012 ,p_amount12_tab => l_prj_amount12_arr
1013 ,p_amount13_tab => l_prj_amount13_arr
1014 ,p_amount14_tab => l_prj_amount14_arr
1015 ,p_amount15_tab => l_prj_amount15_arr
1016 ,p_amount16_tab => l_prj_amount16_arr
1017 ,p_amount17_tab => l_prj_amount17_arr
1018 ,p_amount18_tab => l_prj_amount18_arr
1019 ,p_amount19_tab => l_prj_amount19_arr
1020 ,p_qty1_tab => l_qty1_arr
1021 ,p_qty2_tab => l_qty2_arr
1022 ,p_qty3_tab => l_qty3_arr
1023 ,p_qty4_tab => l_qty4_arr
1024 ,p_qty5_tab => l_qty5_arr
1025 ,p_qty6_tab => l_qty6_arr
1026 ,p_out_object_index_tab => l_out_task_index_arr
1027 ,p_out_object_id_tab => l_out_task_arr
1028 ,p_out_amount1_tab => l_out_amount1_arr
1029 ,p_out_amount2_tab => l_out_amount2_arr
1030 ,p_out_amount3_tab => l_out_amount3_arr
1031 ,p_out_amount4_tab => l_out_amount4_arr
1032 ,p_out_amount5_tab => l_out_amount5_arr
1033 ,p_out_amount6_tab => l_out_amount6_arr
1034 ,p_out_amount7_tab => l_out_amount7_arr
1035 ,p_out_amount8_tab => l_out_amount8_arr
1036 ,p_out_amount9_tab => l_out_amount9_arr
1037 ,p_out_amount10_tab => l_out_amount10_arr
1038 ,p_out_amount11_tab => l_out_amount11_arr
1039 ,p_out_amount12_tab => l_out_amount12_arr
1043 ,p_out_amount16_tab => l_out_amount16_arr
1040 ,p_out_amount13_tab => l_out_amount13_arr
1041 ,p_out_amount14_tab => l_out_amount14_arr
1042 ,p_out_amount15_tab => l_out_amount15_arr
1044 ,p_out_amount17_tab => l_out_amount17_arr
1045 ,p_out_amount18_tab => l_out_amount18_arr
1046 ,p_out_amount19_tab => l_out_amount19_arr
1047 ,p_out_qty1_tab => l_out_qty1_arr
1048 ,p_out_qty2_tab => l_out_qty2_arr
1049 ,p_out_qty3_tab => l_out_qty3_arr
1050 ,p_out_qty4_tab => l_out_qty4_arr
1051 ,p_out_qty5_tab => l_out_qty5_arr
1052 ,p_out_qty6_tab => l_out_qty6_arr
1053 ,x_return_status => l_return_status
1054 ,x_msg_count => l_msg_count
1055 ,x_msg_data => l_msg_data );
1056
1057
1058
1059
1060 ppr_log(' after api l_out_task_arr.count ... '||l_out_task_arr.count);
1061
1062
1063
1064
1065 merge_into_fact (
1066 p_calling_mode => p_calling_mode
1067 --,p_max_msg_count IN NUMBER default NULL
1068 ,p_fact_slice => p_fact_slice
1069 -- ,p_debug_mode IN VARCHAR2 default 'N'
1070 ,p_project_id => l_project_id
1071 ,p_budget_version_id => l_budget_version_id
1072 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
1073 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
1074 ,p_object_id => l_object_id
1075 ,p_currency_code => l_project_currency
1076 ,p_fact_task_arr => l_out_task_arr
1077 ,p_fact_task_index_arr => l_out_task_index_arr
1078 ,p_fact_amount1_arr => l_out_amount1_arr
1079 ,p_fact_amount2_arr => l_out_amount2_arr
1080 ,p_fact_amount3_arr => l_out_amount3_arr
1081 ,p_fact_amount4_arr => l_out_amount4_arr
1082 ,p_fact_amount5_arr => l_out_amount5_arr
1083 ,p_fact_amount6_arr => l_out_amount6_arr
1084 ,p_fact_amount7_arr => l_out_amount7_arr
1085 ,p_fact_amount8_arr => l_out_amount8_arr
1086 ,p_fact_amount9_arr => l_out_amount9_arr
1087 ,p_fact_amount10_arr => l_out_amount10_arr
1088 ,p_fact_amount11_arr => l_out_amount11_arr
1089 ,p_fact_amount12_arr => l_out_amount12_arr
1090 ,p_fact_amount13_arr => l_out_amount13_arr
1091 ,p_fact_amount14_arr => l_out_amount14_arr
1092 ,p_fact_amount15_arr => l_out_amount15_arr
1093 ,p_fact_amount16_arr => l_out_amount16_arr
1094 ,p_fact_amount17_arr => l_out_amount17_arr
1095 ,p_fact_amount18_arr => l_out_amount18_arr
1096 ,p_fact_amount19_arr => l_out_amount19_arr
1097 ,p_fact_qty1_arr => l_out_qty1_arr
1098 ,p_fact_qty2_arr => l_out_qty2_arr
1099 ,p_fact_qty3_arr => l_out_qty3_arr
1100 ,p_fact_qty4_arr => l_out_qty4_arr
1101 ,p_fact_qty5_arr => l_out_qty5_arr
1102 ,p_fact_qty6_arr => l_out_qty6_arr
1103 ,x_return_status => l_return_status
1104 ,x_msg_count => l_msg_count
1105 ,x_msg_data => l_msg_data
1106 );
1107
1108
1109 -- RAW_COST, BRDN_COST, REVENUE
1110 -- l_out_amount1_arr(i),l_out_amount2_arr(i),l_out_amount3_arr(i)
1111 -- LABOR_HRS, LABOR_REVENUE, EQUIPMENT_HOURS,
1112 -- l_out_labor_qty1_arr(i), 0, l_out_equip_qty1_arr(i),
1113 -- , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_BRDN_COST, ACT_RAW_COST
1114 --- ,l_out_labor_qty1_arr(i),l_out_equip_qty2_arr(i),l_out_amount5_arr(i),l_out_amount4_arr(i)
1115 ---
1116 ---, ACT_REVENUE, ETC_LABOR_HRS, ETC_EQUIP_HRS,
1117 --- ,l_out_amount6_arr(i),l_out_labor_qty3_arr(i),l_out_equip_qty3_arr(i)
1118 ----
1119 ----ETC_BRDN_COST, ETC_RAW_COST,
1120 ---l_out_amount8_arr(i), l_out_amount7_arr(i)
1121
1122
1123 -- END Project Currency Process
1124
1125
1126 If ( l_project_currency <> l_functional_currency ) then /* Project_currency <> functional_currency */
1127
1128 ppr_log(' SECOND RUN......... ');
1129
1130 rollup_amounts (
1131 p_object_id_tab => l_task_arr
1132 ,p_wbs_level_tab => l_task_level_arr
1133 ,p_connect_path_tab => l_connect_path_arr
1134 ,p_leaf_node_tab => l_leaf_node_arr
1135 ,p_resource_class_tab => l_res_class_arr
1136 ,p_amount1_tab => l_func_amount1_arr
1137 ,p_amount2_tab => l_func_amount2_arr
1138 ,p_amount3_tab => l_func_amount3_arr
1139 ,p_amount4_tab => l_func_amount4_arr
1140 ,p_amount5_tab => l_func_amount5_arr
1141 ,p_amount6_tab => l_func_amount6_arr
1142 ,p_amount7_tab => l_func_amount7_arr
1143 ,p_amount8_tab => l_func_amount8_arr
1144 ,p_amount9_tab => l_func_amount9_arr
1145 ,p_amount10_tab => l_func_amount10_arr
1146 ,p_amount11_tab => l_func_amount11_arr
1147 ,p_amount12_tab => l_func_amount12_arr
1148 ,p_amount13_tab => l_func_amount13_arr
1149 ,p_amount14_tab => l_func_amount14_arr
1150 ,p_amount15_tab => l_func_amount15_arr
1151 ,p_amount16_tab => l_func_amount16_arr
1152 ,p_amount17_tab => l_func_amount17_arr
1153 ,p_amount18_tab => l_func_amount18_arr
1154 ,p_amount19_tab => l_func_amount19_arr
1155 ,p_qty1_tab => l_qty1_arr
1156 ,p_qty2_tab => l_qty2_arr
1157 ,p_qty3_tab => l_qty3_arr
1158 ,p_qty4_tab => l_qty4_arr
1159 ,p_qty5_tab => l_qty5_arr
1160 ,p_qty6_tab => l_qty6_arr
1161 ,p_out_object_index_tab => l_out_task_index_arr
1162 ,p_out_object_id_tab => l_out_task_arr
1163 ,p_out_amount1_tab => l_out_amount1_arr
1164 ,p_out_amount2_tab => l_out_amount2_arr
1168 ,p_out_amount6_tab => l_out_amount6_arr
1165 ,p_out_amount3_tab => l_out_amount3_arr
1166 ,p_out_amount4_tab => l_out_amount4_arr
1167 ,p_out_amount5_tab => l_out_amount5_arr
1169 ,p_out_amount7_tab => l_out_amount7_arr
1170 ,p_out_amount8_tab => l_out_amount8_arr
1171 ,p_out_amount9_tab => l_out_amount9_arr
1172 ,p_out_amount10_tab => l_out_amount10_arr
1173 ,p_out_amount11_tab => l_out_amount11_arr
1174 ,p_out_amount12_tab => l_out_amount12_arr
1175 ,p_out_amount13_tab => l_out_amount13_arr
1176 ,p_out_amount14_tab => l_out_amount14_arr
1177 ,p_out_amount15_tab => l_out_amount15_arr
1178 ,p_out_amount16_tab => l_out_amount16_arr
1179 ,p_out_amount17_tab => l_out_amount17_arr
1180 ,p_out_amount18_tab => l_out_amount18_arr
1181 ,p_out_amount19_tab => l_out_amount19_arr
1182 ,p_out_qty1_tab => l_out_qty1_arr
1183 ,p_out_qty2_tab => l_out_qty2_arr
1184 ,p_out_qty3_tab => l_out_qty3_arr
1185 ,p_out_qty4_tab => l_out_qty4_arr
1186 ,p_out_qty5_tab => l_out_qty5_arr
1187 ,p_out_qty6_tab => l_out_qty6_arr
1188 ,x_return_status => l_return_status
1189 ,x_msg_count => l_msg_count
1190 ,x_msg_data => l_msg_data );
1191
1192
1193
1194
1195 merge_into_fact (
1196 p_calling_mode => p_calling_mode
1197 --,p_max_msg_count IN NUMBER default NULL
1198 ,p_fact_slice => p_fact_slice
1199 -- ,p_debug_mode IN VARCHAR2 default 'N'
1200 ,p_project_id => l_project_id
1201 ,p_budget_version_id => l_budget_version_id
1202 ,p_rbs_cbs_version_id_2 => l_rbs_cbs_version_id_2
1203 ,p_rbs_cbs_version_id => l_rbs_cbs_version_id
1204 ,p_object_id => l_object_id
1205 ,p_currency_code => l_functional_currency
1206 ,p_fact_task_arr => l_out_task_arr
1207 ,p_fact_task_index_arr => l_out_task_index_arr
1208 ,p_fact_amount1_arr => l_out_amount1_arr
1209 ,p_fact_amount2_arr => l_out_amount2_arr
1210 ,p_fact_amount3_arr => l_out_amount3_arr
1211 ,p_fact_amount4_arr => l_out_amount4_arr
1212 ,p_fact_amount5_arr => l_out_amount5_arr
1213 ,p_fact_amount6_arr => l_out_amount6_arr
1214 ,p_fact_amount7_arr => l_out_amount7_arr
1215 ,p_fact_amount8_arr => l_out_amount8_arr
1216 ,p_fact_amount9_arr => l_out_amount9_arr
1217 ,p_fact_amount10_arr => l_out_amount10_arr
1218 ,p_fact_amount11_arr => l_out_amount11_arr
1219 ,p_fact_amount12_arr => l_out_amount12_arr
1220 ,p_fact_amount13_arr => l_out_amount13_arr
1221 ,p_fact_amount14_arr => l_out_amount14_arr
1222 ,p_fact_amount15_arr => l_out_amount15_arr
1223 ,p_fact_amount16_arr => l_out_amount16_arr
1224 ,p_fact_amount17_arr => l_out_amount17_arr
1225 ,p_fact_amount18_arr => l_out_amount18_arr
1226 ,p_fact_amount19_arr => l_out_amount19_arr
1227 ,p_fact_qty1_arr => l_out_qty1_arr
1228 ,p_fact_qty2_arr => l_out_qty2_arr
1229 ,p_fact_qty3_arr => l_out_qty3_arr
1230 ,p_fact_qty4_arr => l_out_qty4_arr
1231 ,p_fact_qty5_arr => l_out_qty5_arr
1232 ,p_fact_qty6_arr => l_out_qty6_arr
1233 ,x_return_status => l_return_status
1234 ,x_msg_count => l_msg_count
1235 ,x_msg_data => l_msg_data
1236 );
1237
1238
1239 end if; /* Project_currency <> functional_currency */
1240
1241
1242 commit;
1243
1244 end actual_rollup ;
1245
1246
1247 procedure rollup_amounts (
1248 p_api_version IN NUMBER default 1.0
1249 ,p_commit IN VARCHAR2 default 'N'
1250 ,p_calling_mode IN VARCHAR2 default 'FULL'
1251 ,p_debug_mode IN VARCHAR2 default 'N'
1252 ,p_max_msg_count IN NUMBER default NULL
1253 ,p_resource_class_tab IN PA_PLSQL_DATATYPES.Char30TabTyp
1254 ,p_object_id_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1255 ,p_wbs_level_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1256 ,p_connect_path_tab IN PA_PLSQL_DATATYPES.Char240TabTyp
1257 ,p_leaf_node_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1258 ,p_amount1_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1259 ,p_amount2_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1260 ,p_amount3_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1261 ,p_amount4_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1262 ,p_amount5_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1263 ,p_amount6_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1264 ,p_amount7_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1265 ,p_amount8_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1266 ,p_amount9_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1267 ,p_amount10_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1268 ,p_amount11_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1269 ,p_amount12_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1270 ,p_amount13_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1271 ,p_amount14_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1272 ,p_amount15_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1273 ,p_amount16_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1274 ,p_amount17_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1275 ,p_amount18_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1276 ,p_amount19_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1277 ,p_qty1_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1278 ,p_qty2_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1279 ,p_qty3_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1280 ,p_qty4_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1281 ,p_qty5_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1285 ,p_out_amount1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1282 ,p_qty6_tab IN PA_PLSQL_DATATYPES.NumTabTyp
1283 ,p_out_object_index_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1284 ,p_out_object_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1286 ,p_out_amount2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1287 ,p_out_amount3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1288 ,p_out_amount4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1289 ,p_out_amount5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1290 ,p_out_amount6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1291 ,p_out_amount7_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1292 ,p_out_amount8_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1293 ,p_out_amount9_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1294 ,p_out_amount10_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1295 ,p_out_amount11_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1296 ,p_out_amount12_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1297 ,p_out_amount13_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1298 ,p_out_amount14_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1299 ,p_out_amount15_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1300 ,p_out_amount16_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1301 ,p_out_amount17_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1302 ,p_out_amount18_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1303 ,p_out_amount19_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1304 ,p_out_qty1_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1305 ,p_out_qty2_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1306 ,p_out_qty3_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1307 ,p_out_qty4_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1308 ,p_out_qty5_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1309 ,p_out_qty6_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1310 ,x_return_status OUT NOCOPY VARCHAR2
1311 ,x_msg_count OUT NOCOPY NUMBER
1312 ,x_msg_data OUT NOCOPY VARCHAR2
1313 ) as
1314
1315 t_str varchar2(20);
1316
1317 t_id number;
1318 l_level number;
1319 p_path varchar2(240);
1320
1321
1322 l_parent_object_index PA_PLSQL_DATATYPES.NumTabTyp;
1323 l_parent_counter number ;
1324
1325 l_rollup_amount1 PA_PLSQL_DATATYPES.NumTabTyp;
1326 l_rollup_amount2 PA_PLSQL_DATATYPES.NumTabTyp;
1327 l_rollup_amount3 PA_PLSQL_DATATYPES.NumTabTyp;
1328
1329 l_rollup_amount4 PA_PLSQL_DATATYPES.NumTabTyp;
1330 l_rollup_amount5 PA_PLSQL_DATATYPES.NumTabTyp;
1331 l_rollup_amount6 PA_PLSQL_DATATYPES.NumTabTyp;
1332 l_rollup_amount7 PA_PLSQL_DATATYPES.NumTabTyp;
1333 l_rollup_amount8 PA_PLSQL_DATATYPES.NumTabTyp;
1334
1335 l_rollup_amount9 PA_PLSQL_DATATYPES.NumTabTyp;
1336 l_rollup_amount10 PA_PLSQL_DATATYPES.NumTabTyp;
1337 l_rollup_amount11 PA_PLSQL_DATATYPES.NumTabTyp;
1338 l_rollup_amount12 PA_PLSQL_DATATYPES.NumTabTyp;
1339 l_rollup_amount13 PA_PLSQL_DATATYPES.NumTabTyp;
1340
1341 l_rollup_amount14 PA_PLSQL_DATATYPES.NumTabTyp;
1342 l_rollup_amount15 PA_PLSQL_DATATYPES.NumTabTyp;
1343 l_rollup_amount16 PA_PLSQL_DATATYPES.NumTabTyp;
1344 l_rollup_amount17 PA_PLSQL_DATATYPES.NumTabTyp;
1345 l_rollup_amount18 PA_PLSQL_DATATYPES.NumTabTyp;
1346 l_rollup_amount19 PA_PLSQL_DATATYPES.NumTabTyp;
1347
1348
1349 l_rollup_qty1 PA_PLSQL_DATATYPES.NumTabTyp;
1350 l_rollup_qty2 PA_PLSQL_DATATYPES.NumTabTyp;
1351 l_rollup_qty3 PA_PLSQL_DATATYPES.NumTabTyp;
1352
1353 l_rollup_qty4 PA_PLSQL_DATATYPES.NumTabTyp;
1354 l_rollup_qty5 PA_PLSQL_DATATYPES.NumTabTyp;
1355 l_rollup_qty6 PA_PLSQL_DATATYPES.NumTabTyp;
1356
1357
1358
1359
1360
1361 begin
1362
1363 null;
1364
1365 l_parent_counter := 0;
1366
1367 ppr_log(' INSIDE ROLLUP task_id count ... '||p_object_id_tab.count);
1368
1369
1370 FOR i IN 1..p_object_id_tab.count LOOP
1371
1372 l_level := p_wbs_level_tab(i);
1373 p_path := p_connect_path_tab(i);
1374
1375 ppr_log('level '||l_level||' task_id ... '||p_object_id_tab(i)||' path '||p_path||' .. amount 13.. '||p_amount13_tab(i)||' .... amt 1 : '||p_amount1_tab(i));
1376
1377 FOR i_level IN 1..l_level LOOP
1378
1379 if ( instr(p_path , '/', 1, i_level) = 0 ) then
1380 t_str := null;
1381 else
1382 if ( instr(p_path , '/', 1, i_level+1) = 0 ) then
1383 t_str := substr(p_path , instr(p_path , '/', 1, i_level)+1, length(p_path ) ) ;
1384 else
1385 t_str :=substr(p_path , instr(p_path , '/', 1, i_level)+1,
1386 instr(p_path , '/', 1, i_level+1) - instr(p_path , '/', 1, i_level)- 1 ) ;
1387 end if;
1388 end if;
1389
1390 t_id := to_number(t_str);
1391
1392
1393 ppr_log(' t_id '||t_id) ;
1394
1395 if (l_rollup_amount1.exists(t_id)) then
1396 ppr_log(' EXIST ... t_id '||t_id) ;
1397 l_rollup_amount1(t_id) := l_rollup_amount1(t_id) + p_amount1_tab(i);
1398 l_rollup_amount2(t_id) := l_rollup_amount2(t_id) + p_amount2_tab(i);
1399 l_rollup_amount3(t_id) := l_rollup_amount3(t_id) + p_amount3_tab(i);
1400
1401 l_rollup_amount4(t_id) := l_rollup_amount4(t_id) + p_amount4_tab(i);
1402 l_rollup_amount5(t_id) := l_rollup_amount5(t_id) + p_amount5_tab(i);
1403 l_rollup_amount6(t_id) := l_rollup_amount6(t_id) + p_amount6_tab(i);
1404
1405 l_rollup_amount7(t_id) := l_rollup_amount7(t_id) + p_amount7_tab(i);
1406 l_rollup_amount8(t_id) := l_rollup_amount8(t_id) + p_amount8_tab(i);
1407
1408 l_rollup_amount9(t_id) := l_rollup_amount9(t_id) + p_amount9_tab(i);
1412 l_rollup_amount12(t_id) := l_rollup_amount12(t_id) + p_amount12_tab(i);
1409 l_rollup_amount10(t_id) := l_rollup_amount10(t_id) + p_amount10_tab(i);
1410
1411 l_rollup_amount11(t_id) := l_rollup_amount11(t_id) + p_amount11_tab(i);
1413 l_rollup_amount13(t_id) := l_rollup_amount13(t_id) + p_amount13_tab(i);
1414 l_rollup_amount14(t_id) := l_rollup_amount14(t_id) + p_amount14_tab(i);
1415 l_rollup_amount15(t_id) := l_rollup_amount15(t_id) + p_amount15_tab(i);
1416 l_rollup_amount16(t_id) := l_rollup_amount16(t_id) + p_amount16_tab(i);
1417 l_rollup_amount17(t_id) := l_rollup_amount17(t_id) + p_amount17_tab(i);
1418 l_rollup_amount18(t_id) := l_rollup_amount18(t_id) + p_amount18_tab(i);
1419 l_rollup_amount19(t_id) := l_rollup_amount19(t_id) + p_amount19_tab(i);
1420
1421
1422 l_rollup_qty1(t_id) := l_rollup_qty1(t_id) + p_qty1_tab(i);
1423 l_rollup_qty2(t_id) := l_rollup_qty2(t_id) + p_qty2_tab(i);
1424 l_rollup_qty3(t_id) := l_rollup_qty3(t_id) + p_qty3_tab(i);
1425
1426 l_rollup_qty4(t_id) := l_rollup_qty4(t_id) + p_qty4_tab(i);
1427 l_rollup_qty5(t_id) := l_rollup_qty5(t_id) + p_qty5_tab(i);
1428 l_rollup_qty6(t_id) := l_rollup_qty6(t_id) + p_qty6_tab(i);
1429
1430 else
1431 ppr_log(' Not EXIST ... t_id '||t_id) ;
1432 l_parent_counter := l_parent_counter + 1;
1433 l_parent_object_index(l_parent_counter) := t_id;
1434
1435 l_rollup_amount1(t_id) := p_amount1_tab(i);
1436 l_rollup_amount2(t_id) := p_amount2_tab(i);
1437 l_rollup_amount3(t_id) := p_amount3_tab(i);
1438
1439 l_rollup_amount4(t_id) := p_amount4_tab(i);
1440 l_rollup_amount5(t_id) := p_amount5_tab(i);
1441 l_rollup_amount6(t_id) := p_amount6_tab(i);
1442
1443 l_rollup_amount7(t_id) := p_amount7_tab(i);
1444 l_rollup_amount8(t_id) := p_amount8_tab(i);
1445
1446 l_rollup_amount9(t_id) := p_amount9_tab(i);
1447 l_rollup_amount10(t_id) := p_amount10_tab(i);
1448
1449 l_rollup_amount11(t_id) := p_amount11_tab(i);
1450 l_rollup_amount12(t_id) := p_amount12_tab(i);
1451 l_rollup_amount13(t_id) := p_amount13_tab(i);
1452 l_rollup_amount14(t_id) := p_amount14_tab(i);
1453 l_rollup_amount15(t_id) := p_amount15_tab(i);
1454 l_rollup_amount16(t_id) := p_amount16_tab(i);
1455 l_rollup_amount17(t_id) := p_amount17_tab(i);
1456 l_rollup_amount18(t_id) := p_amount18_tab(i);
1457 l_rollup_amount19(t_id) := p_amount19_tab(i);
1458
1459 l_rollup_qty1(t_id) := p_qty1_tab(i);
1460 l_rollup_qty2(t_id) := p_qty2_tab(i);
1461 l_rollup_qty3(t_id) := p_qty3_tab(i);
1462
1463 l_rollup_qty4(t_id) := p_qty4_tab(i);
1464 l_rollup_qty5(t_id) := p_qty5_tab(i);
1465 l_rollup_qty6(t_id) := p_qty6_tab(i);
1466
1467
1468
1469 end if;
1470 -- if ( t_id = 0 ) then
1471 ppr_log('......... roll task_id ... '||t_id||' total '||l_rollup_amount1(t_id));
1472 -- end if;
1473
1474
1475
1476 END LOOP; -- l_level loop
1477
1478 END LOOP; -- p_object_id_tab loop
1479
1480 For k IN 1..l_parent_counter LOOP
1481
1482 t_id := l_parent_object_index(k);
1483
1484 p_out_object_id_tab(k) := t_id;
1485 p_out_amount1_tab(k) := l_rollup_amount1(t_id);
1486 p_out_amount2_tab(k) := l_rollup_amount2(t_id);
1487 p_out_amount3_tab(k) := l_rollup_amount3(t_id);
1488
1489 p_out_amount4_tab(k) := l_rollup_amount4(t_id);
1490 p_out_amount5_tab(k) := l_rollup_amount5(t_id);
1491 p_out_amount6_tab(k) := l_rollup_amount6(t_id);
1492
1493 p_out_amount7_tab(k) := l_rollup_amount7(t_id);
1494 p_out_amount8_tab(k) := l_rollup_amount8(t_id);
1495
1496 p_out_amount9_tab(k) := l_rollup_amount9(t_id);
1497 p_out_amount10_tab(k) := l_rollup_amount10(t_id);
1498
1499 p_out_amount11_tab(k) := l_rollup_amount11(t_id);
1500 p_out_amount12_tab(k) := l_rollup_amount12(t_id);
1501 p_out_amount13_tab(k) := l_rollup_amount13(t_id);
1502 p_out_amount14_tab(k) := l_rollup_amount14(t_id);
1503 p_out_amount15_tab(k) := l_rollup_amount15(t_id);
1504 p_out_amount16_tab(k) := l_rollup_amount16(t_id);
1505 p_out_amount17_tab(k) := l_rollup_amount17(t_id);
1506 p_out_amount18_tab(k) := l_rollup_amount18(t_id);
1507 p_out_amount19_tab(k) := l_rollup_amount19(t_id);
1508
1509 p_out_qty1_tab(k) := l_rollup_qty1(t_id);
1510 p_out_qty2_tab(k) := l_rollup_qty2(t_id);
1511 p_out_qty3_tab(k) := l_rollup_qty3(t_id);
1512 p_out_qty4_tab(k) := l_rollup_qty4(t_id);
1513 p_out_qty5_tab(k) := l_rollup_qty5(t_id);
1514 p_out_qty6_tab(k) := l_rollup_qty6(t_id);
1515
1516 p_out_object_index_tab(t_id) := k;
1517
1518 ppr_log('FINAL task '||t_id||' total '||l_rollup_amount1(t_id));
1519
1520
1521 END LOOP;
1522
1523
1524 Exception
1525 when others then
1526 raise;
1527
1528 end rollup_amounts ;
1529
1530 procedure merge_into_fact (
1531 p_calling_mode IN VARCHAR2 default 'FULL'
1532 ,p_max_msg_count IN NUMBER default NULL
1533 ,p_fact_slice IN VARCHAR2 default 'WBS'
1534 ,p_debug_mode IN VARCHAR2 default 'N'
1535 ,p_project_id In number default -1
1536 ,p_budget_version_id in number default -1
1537 ,p_rbs_cbs_version_id_2 in number default -1
1538 ,p_rbs_cbs_version_id in number default -1
1539 ,p_object_id in number default -1
1540 ,p_currency_code in VARCHAR2 default -1
1541 ,p_fact_task_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1542 ,p_fact_task_index_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1543 ,p_fact_amount1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1547 ,p_fact_amount5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1544 ,p_fact_amount2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1545 ,p_fact_amount3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1546 ,p_fact_amount4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1548 ,p_fact_amount6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1549 ,p_fact_amount7_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1550 ,p_fact_amount8_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1551 ,p_fact_amount9_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1552 ,p_fact_amount10_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1553 ,p_fact_amount11_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1554 ,p_fact_amount12_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1555 ,p_fact_amount13_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1556 ,p_fact_amount14_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1557 ,p_fact_amount15_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1558 ,p_fact_amount16_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1559 ,p_fact_amount17_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1560 ,p_fact_amount18_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1561 ,p_fact_amount19_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1562 ,p_fact_qty1_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1563 ,p_fact_qty2_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1564 ,p_fact_qty3_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1565 ,p_fact_qty4_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1566 ,p_fact_qty5_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1567 ,p_fact_qty6_arr IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
1568 ,x_return_status OUT NOCOPY VARCHAR2
1569 ,x_msg_count OUT NOCOPY NUMBER
1570 ,x_msg_data OUT NOCOPY VARCHAR2
1571 ) as
1572
1573
1574 l_upd_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
1575 l_upd_task_arr_2 PA_PLSQL_DATATYPES.NumTabTyp;
1576 l_upd_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
1577
1578 l_ins_task_flag_arr PA_PLSQL_DATATYPES.Char1TabTyp;
1579
1580 l_out_task_index_arr PA_PLSQL_DATATYPES.NumTabTyp;
1581
1582 begin
1583
1584
1585 l_upd_task_flag_arr.delete;
1586 l_upd_task_arr.delete;
1587 l_upd_task_arr_2.delete;
1588
1589
1590 if ( p_calling_mode = 'FULL' and ( p_fact_slice = 'WBS' ) ) then
1591
1592 ppr_log(' p_calling_mode = CREATE and ( p_fact_slice = WBS )');
1593
1594 FORALL i IN 1..p_fact_task_arr.count
1595 UPDATE pa_pjt_wbs_fact fact /* bulk bind */
1596 SET (
1597 ----
1598 -----
1599 RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
1600 ,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
1601 ,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
1602 ,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
1603 ------
1604 ------
1605 ,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
1606 ------
1607 ------
1608 ,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
1609 ----
1610 ----
1611 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST
1612 , ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
1613 -------
1614 -------
1615 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST,ETC_EQUIP_BRDN_COST, ETC_BRDN_COST
1616 , ETC_RAW_COST, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
1617 ------
1618 -------
1619 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5
1620 , CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10
1621 , CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
1622 (
1623 SELECT
1624 ----
1625 p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i), p_fact_amount4_arr(i),p_fact_amount5_arr(i)
1626 ,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
1627 ,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
1628 ,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
1629 ----
1630 ----
1631 ,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
1632 ----
1633 ----
1634 ,0,0
1635 ----
1636 ----
1637 ,0,0,0,0,0
1638 ,0,0,0,0
1639 ----
1640 ----
1641 ,0,0,0,0,0
1642 ,0,0,0
1643 ----
1644 ----
1645 ,0,0,0,0,0
1646 ,0,0,0,0,0
1647 ,0,0,0,0,0
1648 FROM DUAL
1649 )
1650 WHERE PROJECT_ID = p_project_id
1651 AND PROJECT_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS'
1652 AND WBS_ROLLUP_FLAG = 'Y'
1653 AND PRG_ROLLUP_FLAG = 'N'
1654 AND PLAN_VERSION_ID = p_budget_version_id
1655 AND CURRENCY_CODE = p_currency_code
1656 RETURNING PROJECT_ELEMENT_ID
1657 BULK COLLECT INTO l_upd_task_arr;
1658
1659 elsif ( p_calling_mode = 'INCREMENTAL' and ( p_fact_slice = 'WBS' ) ) then
1660
1661 ppr_log(' p_calling_mode = INCREMENTAL and ( p_fact_slice = WBS )');
1662
1663 FORALL i IN 1..p_fact_task_arr.count
1664 UPDATE pa_pjt_wbs_fact fact /* bulk bind */
1665 SET (
1666 ----
1667 -----
1668 RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
1669 ,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
1670 ,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
1671 ,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
1672 ------
1673 ------
1677 ,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
1674 ,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
1675 ------
1676 ------
1678 ----
1679 ----
1680 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST
1681 , ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
1682 -------
1683 -------
1684 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST,ETC_EQUIP_BRDN_COST, ETC_BRDN_COST
1685 , ETC_RAW_COST, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
1686 ------
1687 -------
1688 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5
1689 , CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10
1690 , CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
1691 (
1692 SELECT
1693
1694 RAW_COST+p_fact_amount1_arr(i),bill_RAW_COST+p_fact_amount2_arr(i), BRDN_COST+p_fact_amount3_arr(i),
1695 bill_BRDN_COST +p_fact_amount4_arr(i), REVENUE + p_fact_amount5_arr(i),
1696 LABOR_RAW_COST+p_fact_amount6_arr(i),bill_LABOR_RAW_COST+p_fact_amount7_arr(i),
1697 LABOR_BRDN_COST+p_fact_amount8_arr(i),bill_LABOR_BRDN_COST+p_fact_amount9_arr(i),
1698 LABOR_REVENUE+p_fact_amount10_arr(i),EQUIPMENT_RAW_COST+p_fact_amount11_arr(i),
1699 EQUIPMENT_BRDN_COST+p_fact_amount12_arr(i),
1700 ------
1701 ------
1702 p_fact_amount13_arr(i), -- SUP_INV_COMMITTED_COST
1703 p_fact_amount14_arr(i), --- PO_COMMITTED_COST
1704 p_fact_amount15_arr(i), --- PR_COMMITTED_COST
1705 p_fact_amount16_arr(i) --- OTH_COMMITTED_COST
1706 ----
1707 -----
1708 ,LABOR_HRS+p_fact_qty1_arr(i), BILL_LABOR_HRS+p_fact_qty2_arr(i), EQUIPMENT_HOURS+p_fact_qty3_arr(i),
1709 BILLABLE_EQUIPMENT_HOURS+p_fact_qty4_arr(i)
1710 ----
1711 ----
1712 ,0,0
1713 ----
1714 ----
1715 ,0,0,0,0,0
1716 ,0,0,0,0
1717 ----
1718 ----
1719 ,0,0,0,0,0
1720 ,0,0,0
1721 ----
1722 ----
1723 ,0,0,0,0,0
1724 ,0,0,0,0,0
1725 ,0,0,0,0,0
1726 FROM DUAL
1727 )
1728 WHERE PROJECT_ID = p_project_id
1729 AND PROJECT_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS'
1730 AND WBS_ROLLUP_FLAG = 'Y'
1731 AND PRG_ROLLUP_FLAG = 'N'
1732 AND PLAN_VERSION_ID = p_budget_version_id
1733 AND CURRENCY_CODE = p_currency_code
1734 RETURNING PROJECT_ELEMENT_ID
1735 BULK COLLECT INTO l_upd_task_arr;
1736
1737 end if;
1738
1739
1740
1741 if ( p_calling_mode = 'FULL' and ( p_fact_slice = 'RBS' ) ) then
1742
1743 ppr_log(' p_calling_mode = CREATE and ( p_fact_slice = RBS )');
1744
1745 FORALL i IN 1..p_fact_task_arr.count
1746 UPDATE pa_pjt_rbs_cbs_fact fact /* bulk bind */
1747 SET (
1748 ----
1749 -----
1750 RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
1751 ,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
1752 ,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
1753 ,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
1754 ------
1755 ------
1756 ,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
1757 ------
1758 ------
1759 ,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
1760 ----
1761 ----
1762 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST
1763 , ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
1764 -------
1765 -------
1766 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST,ETC_EQUIP_BRDN_COST, ETC_BRDN_COST
1767 , ETC_RAW_COST, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
1768 ------
1769 -------
1770 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5
1771 , CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10
1772 , CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
1773 (
1774 SELECT
1775 ----
1776 p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i), p_fact_amount4_arr(i),p_fact_amount5_arr(i)
1777 ,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
1778 ,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
1779 ,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
1780 ----
1781 ----
1782 ,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
1783 ----
1784 ----
1785 ,0,0
1786 ----
1787 ----
1788 ,0,0,0,0,0
1789 ,0,0,0,0
1790 ----
1791 ----
1792 ,0,0,0,0,0
1793 ,0,0,0
1794 ----
1795 ----
1796 ,0,0,0,0,0
1797 ,0,0,0,0,0
1798 ,0,0,0,0,0
1799 FROM DUAL
1800 )
1801 WHERE PROJECT_ID = p_project_id
1802 AND rbs_cbs_element_id = p_fact_task_arr(i) and p_fact_slice = 'RBS'
1803 AND RBS_AGGR_LEVEL = 'Y'
1804 AND PRG_ROLLUP_FLAG = 'N'
1805 AND PLAN_VERSION_ID = -1
1806 AND CURRENCY_CODE = p_currency_code
1807 AND rbs_cbs_version_id = p_rbs_cbs_version_id
1808 RETURNING rbs_cbs_element_id
1809 BULK COLLECT INTO l_upd_task_arr;
1810
1811 elsif ( p_calling_mode = 'INCREMENTAL' and ( p_fact_slice = 'RBS' ) ) then
1812
1813 ppr_log(' p_calling_mode = INCREMENTAL and ( p_fact_slice = RBS )');
1814
1815 FORALL i IN 1..p_fact_task_arr.count
1816 UPDATE pa_pjt_rbs_cbs_fact fact /* bulk bind */
1817 SET (
1818 ----
1819 -----
1823 ,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
1820 RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
1821 ,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
1822 ,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
1824 ------
1825 ------
1826 ,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
1827 ------
1828 ------
1829 ,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
1830 ----
1831 ----
1832 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST
1833 , ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
1834 -------
1835 -------
1836 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST,ETC_EQUIP_BRDN_COST, ETC_BRDN_COST
1837 , ETC_RAW_COST, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
1838 ------
1839 -------
1840 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5
1841 , CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10
1842 , CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
1843 (
1844 SELECT
1845
1846 RAW_COST+p_fact_amount1_arr(i),bill_RAW_COST+p_fact_amount2_arr(i), BRDN_COST+p_fact_amount3_arr(i),
1847 bill_BRDN_COST +p_fact_amount4_arr(i), REVENUE + p_fact_amount5_arr(i),
1848 LABOR_RAW_COST+p_fact_amount6_arr(i),bill_LABOR_RAW_COST+p_fact_amount7_arr(i),
1849 LABOR_BRDN_COST+p_fact_amount8_arr(i),bill_LABOR_BRDN_COST+p_fact_amount9_arr(i),
1850 LABOR_REVENUE+p_fact_amount10_arr(i),EQUIPMENT_RAW_COST+p_fact_amount11_arr(i),
1851 EQUIPMENT_BRDN_COST+p_fact_amount12_arr(i),
1852 ------
1853 ------
1854 p_fact_amount13_arr(i), -- SUP_INV_COMMITTED_COST
1855 p_fact_amount14_arr(i), --- PO_COMMITTED_COST
1856 p_fact_amount15_arr(i), --- PR_COMMITTED_COST
1857 p_fact_amount16_arr(i) --- OTH_COMMITTED_COST
1858 ----
1859 -----
1860 ,LABOR_HRS+p_fact_qty1_arr(i), BILL_LABOR_HRS+p_fact_qty2_arr(i), EQUIPMENT_HOURS+p_fact_qty3_arr(i),
1861 BILLABLE_EQUIPMENT_HOURS+p_fact_qty4_arr(i)
1862 ----
1863 ----
1864 ,0,0
1865 ----
1866 ----
1867 ,0,0,0,0,0
1868 ,0,0,0,0
1869 ----
1870 ----
1871 ,0,0,0,0,0
1872 ,0,0,0
1873 ----
1874 ----
1875 ,0,0,0,0,0
1876 ,0,0,0,0,0
1877 ,0,0,0,0,0
1878 FROM DUAL
1879 )
1880 WHERE PROJECT_ID = p_project_id
1881 AND rbs_cbs_element_id = p_fact_task_arr(i) and p_fact_slice = 'RBS'
1882 AND RBS_AGGR_LEVEL = 'Y'
1883 AND PRG_ROLLUP_FLAG = 'N'
1884 AND PLAN_VERSION_ID = -1
1885 AND CURRENCY_CODE = p_currency_code
1886 AND rbs_cbs_version_id = p_rbs_cbs_version_id
1887 RETURNING rbs_cbs_element_id
1888 BULK COLLECT INTO l_upd_task_arr;
1889
1890 end if;
1891
1892
1893 ppr_log(' SRIDHAR .... l_upd_task_arr '||l_upd_task_arr.count);
1894
1895
1896
1897 l_ins_task_flag_arr.delete;
1898
1899 FOR k IN 1..l_upd_task_arr.count LOOP
1900
1901 ppr_log(' SRIDHAR .... l_upd_task_arr(k) , k '||l_upd_task_arr(k)||' '||k);
1902 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);
1903
1904 l_upd_task_flag_arr( p_fact_task_index_arr(l_upd_task_arr(k) ) ) := 'Y';
1905
1906 ppr_log(' AFTER SRIDHAR .... l_upd_task_arr(k) , k '||l_upd_task_arr(k)||' '||k);
1907
1908 end loop;
1909
1910
1911
1912 FOR g IN 1..p_fact_task_arr.count LOOP
1913
1914 if (l_upd_task_flag_arr.exists(g)) then
1915 l_ins_task_flag_arr(g) := 'N' ;
1916 else
1917 l_ins_task_flag_arr(g) := 'Y' ;
1918 end if;
1919
1920 end loop;
1921 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 );
1922 ppr_log(' SRIDHAR .... l_ins_task_flag_arr '||l_ins_task_flag_arr.count);
1923
1924
1925 ppr_log(' after api l_upd_task_arr.count ... '||l_upd_task_arr.count);
1926
1927 If ( p_fact_slice = 'WBS' ) then
1928
1929 ppr_log(' p_fact_slice '||p_fact_slice);
1930
1931
1932 FORALL i IN 1..p_fact_task_arr.count
1933 INSERT INTO pa_pjt_wbs_fact (
1934 ----
1935 PROJECT_ID , PROJECT_ELEMENT_ID
1936 , WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
1937 , PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
1938 ---
1939 ---
1940 ,RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
1941 ,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
1942 ,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
1943 ,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
1944 ------
1945 ------
1946 ,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
1947 ------
1948 ------
1949 ,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
1950 ----
1951 ----
1952 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST
1953 , ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
1954 -------
1955 -------
1956 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST,ETC_EQUIP_BRDN_COST, ETC_BRDN_COST
1957 , ETC_RAW_COST, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
1958 ------
1959 -------
1960 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5
1961 , CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10
1962 , CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
1963 )
1964 SELECT
1965 p_project_id ,p_fact_task_arr(i)
1966 ,'Y','N',p_currency_code --,0,0
1967 ,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
1968 ----
1969 ----
1970 ,p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i), p_fact_amount4_arr(i),p_fact_amount5_arr(i)
1971 ,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
1972 ,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
1973 ,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
1974 ----
1975 ----
1976 ,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
1977 ----
1978 ----
1979 ,0,0
1980 ----
1981 ----
1982 ,0,0,0,0,0
1983 ,0,0,0,0
1984 ----
1985 ----
1986 ,0,0,0,0,0
1987 ,0,0,0
1988 ----
1989 ----
1990 ,0,0,0,0,0
1991 ,0,0,0,0,0
1992 ,0,0,0,0,0
1993 from dual
1994 where l_ins_task_flag_arr(i) = 'Y';
1995
1996
1997
1998 end if;
1999
2000
2001 If ( p_fact_slice = 'RBS' ) then
2002
2003 ppr_log(' p_fact_slice '||p_fact_slice);
2004
2005
2006 FORALL i IN 1..p_fact_task_arr.count
2007 INSERT INTO pa_pjt_rbs_cbs_fact (
2008 ----
2009 PROJECT_ID , rbs_cbs_element_id , rbs_cbs_version_id , rollup_type
2010 , RBS_AGGR_LEVEL, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
2011 , PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
2012 ---
2013 ---
2014 ,RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
2015 ,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
2016 ,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
2017 ,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
2018 ------
2019 ------
2020 ,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
2021 ------
2022 ------
2023 ,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
2024 ----
2025 ----
2026 , ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST
2027 , ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
2028 -------
2029 -------
2030 , ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST,ETC_EQUIP_BRDN_COST, ETC_BRDN_COST
2031 , ETC_RAW_COST, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
2032 ------
2033 -------
2034 , CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5
2035 , CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10
2036 , CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
2037 )
2038 SELECT
2039 p_project_id ,p_fact_task_arr(i),p_rbs_cbs_version_id , 'RBS'
2040 ,'Y','N',p_currency_code --,0,0
2041 ,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
2042 ----
2043 ----
2044 ,p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i), p_fact_amount4_arr(i),p_fact_amount5_arr(i)
2045 ,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
2046 ,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
2047 ,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
2048 ----
2049 ----
2050 ,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
2051 ----
2052 ----
2053 ,0,0
2054 ----
2055 ----
2056 ,0,0,0,0,0
2057 ,0,0,0,0
2058 ----
2059 ----
2060 ,0,0,0,0,0
2061 ,0,0,0
2062 ----
2063 ----
2064 ,0,0,0,0,0
2065 ,0,0,0,0,0
2066 ,0,0,0,0,0
2067 from dual
2068 where l_ins_task_flag_arr(i) = 'Y';
2069
2070 end if;
2071
2072
2073
2074 end merge_into_fact;
2075
2076 End PA_ppr_rollup_actual_PVT;