[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_XBS_ACCUM_UTILS
Source
1 PACKAGE BODY PJI_FM_XBS_ACCUM_UTILS AS
2 /* $Header: PJIPUT1B.pls 120.39.12000000.2 2007/03/08 23:57:36 djoseph ship $ */
3
4 g_package_name VARCHAR2(100) := 'PJI_FM_XBS_ACCUM_UTILS';
5 g_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N') ;
6
7 PROCEDURE PRINT_TIME(p_tag IN VARCHAR2);
8
9
10 -----------------------------------------------------------------
11 -- This API supports 3 kinds of data retrieval
12 -- 1. Entered level data at task level by resource list member
13 -- 2. Entered level data at project level by resource list member
14 -- 3. Rollup data by task (no resource list member context)
15 -----------------------------------------------------------------
16 /*Changed for workplan progress. p_end_date IN DATE ,
17 p_calendar_type IN VARCHAR2
18 and added p_extraction_type IN VARCHAR2 := NULL,
19 p_calling_context IN VARCHAR2 := NULL,
20 */
21
22 /* added procedure for bug#3993830 */
23 PROCEDURE debug_accum
24 IS
25 BEGIN
26 INSERT INTO pji_fm_xbs_accum_tmp1_debug
27 (
28 PROJECT_ID ,
29 STRUCT_VERSION_ID ,
30 PROJECT_ELEMENT_ID ,
31 CALENDAR_TYPE ,
32 PERIOD_NAME ,
33 PLAN_VERSION_ID ,
34 RES_LIST_MEMBER_ID ,
35 QUANTITY ,
36 TXN_CURRENCY_CODE ,
37 TXN_RAW_COST ,
38 TXN_BRDN_COST ,
39 TXN_REVENUE ,
40 TXN_LABOR_RAW_COST ,
41 TXN_LABOR_BRDN_COST ,
42 TXN_EQUIP_RAW_COST ,
43 TXN_EQUIP_BRDN_COST ,
44 TXN_BASE_RAW_COST ,
45 TXN_BASE_BRDN_COST ,
46 TXN_BASE_LABOR_RAW_COST ,
47 TXN_BASE_LABOR_BRDN_COST ,
48 TXN_BASE_EQUIP_RAW_COST ,
49 TXN_BASE_EQUIP_BRDN_COST ,
50 PRJ_RAW_COST ,
51 PRJ_BRDN_COST ,
52 PRJ_REVENUE ,
53 PRJ_LABOR_RAW_COST ,
54 PRJ_LABOR_BRDN_COST ,
55 PRJ_EQUIP_RAW_COST ,
56 PRJ_EQUIP_BRDN_COST ,
57 PRJ_BASE_RAW_COST ,
58 PRJ_BASE_BRDN_COST ,
59 PRJ_BASE_LABOR_RAW_COST ,
60 PRJ_BASE_LABOR_BRDN_COST ,
61 PRJ_BASE_EQUIP_RAW_COST ,
62 PRJ_BASE_EQUIP_BRDN_COST ,
63 POU_RAW_COST ,
64 POU_BRDN_COST ,
65 POU_REVENUE ,
66 POU_LABOR_RAW_COST ,
67 POU_LABOR_BRDN_COST ,
68 POU_EQUIP_RAW_COST ,
69 POU_EQUIP_BRDN_COST ,
70 POU_BASE_RAW_COST ,
71 POU_BASE_BRDN_COST ,
72 POU_BASE_LABOR_RAW_COST ,
73 POU_BASE_LABOR_BRDN_COST ,
74 POU_BASE_EQUIP_RAW_COST ,
75 POU_BASE_EQUIP_BRDN_COST ,
76 LABOR_HOURS ,
77 EQUIPMENT_HOURS ,
78 BASE_LABOR_HOURS ,
79 BASE_EQUIP_HOURS ,
80 SOURCE_ID ,
81 ACT_LABOR_HRS ,
82 ACT_EQUIP_HRS ,
83 ACT_TXN_LABOR_BRDN_COST ,
84 ACT_TXN_EQUIP_BRDN_COST ,
85 ACT_TXN_BRDN_COST ,
86 ACT_PRJ_LABOR_BRDN_COST ,
87 ACT_PRJ_EQUIP_BRDN_COST ,
88 ACT_PRJ_BRDN_COST ,
89 ACT_PFC_LABOR_BRDN_COST ,
90 ACT_PFC_EQUIP_BRDN_COST ,
91 ACT_PFC_BRDN_COST ,
92 ETC_LABOR_HRS ,
93 ETC_EQUIP_HRS ,
94 ETC_TXNLABOR_BRDN_COST ,
95 ETC_TXN_EQUIP_BRDN_COST ,
96 ETC_TXN_BRDN_COST ,
97 ETC_PRJ_LABOR_BRDN_COST ,
98 ETC_PRJ_EQUIP_BRDN_COST ,
99 ETC_PRJ_BRDN_COST ,
100 ETC_POU_LABOR_BRDN_COST ,
101 ETC_POU_EQUIP_BRDN_COST ,
102 ETC_POU_BRDN_COST ,
103 ACT_TXN_RAW_COST ,
104 ACT_PRJ_RAW_COST ,
105 ACT_POU_RAW_COST ,
106 ETC_TXN_RAW_COST ,
107 ETC_PRJ_RAW_COST ,
108 ETC_POU_RAW_COST ,
109 ACT_TXN_LABOR_RAW_COST ,
110 ACT_TXN_EQUIP_RAW_COST ,
111 ACT_PRJ_LABOR_RAW_COST ,
112 ACT_PRJ_EQUIP_RAW_COST ,
113 ACT_POU_LABOR_RAW_COST ,
114 ACT_POU_EQUIP_RAW_COST ,
115 ETC_TXN_LABOR_RAW_COST ,
116 ETC_TXN_EQUIP_RAW_COST ,
117 ETC_PRJ_LABOR_RAW_COST ,
118 ETC_PRJ_EQUIP_RAW_COST ,
119 ETC_POU_LABOR_RAW_COST ,
120 ETC_POU_EQUIP_RAW_COST ,
121 ACT_POU_LABOR_BRDN_COST ,
122 ACT_POU_EQUIP_BRDN_COST ,
123 ACT_POU_BRDN_COST ,
124 ETC_TXN_LABOR_BRDN_COST ,
125 TXN_LPB_RAW_COST ,
126 TXN_LPB_BRDN_COST ,
127 TXN_LPB_LABOR_RAW_COST ,
128 TXN_LPB_LABOR_BRDN_COST ,
129 TXN_LPB_EQUIP_RAW_COST ,
130 TXN_LPB_EQUIP_BRDN_COST ,
131 PRJ_LPB_RAW_COST ,
132 PRJ_LPB_BRDN_COST ,
133 PRJ_LPB_LABOR_RAW_COST ,
134 PRJ_LPB_LABOR_BRDN_COST ,
135 PRJ_LPB_EQUIP_RAW_COST ,
136 PRJ_LPB_EQUIP_BRDN_COST ,
137 POU_LPB_RAW_COST ,
138 POU_LPB_BRDN_COST ,
139 POU_LPB_LABOR_RAW_COST ,
140 POU_LPB_LABOR_BRDN_COST ,
141 POU_LPB_EQUIP_RAW_COST ,
142 POU_LPB_EQUIP_BRDN_COST ,
143 LPB_LABOR_HOURS ,
144 LPB_EQUIP_HOURS ,
145 PERIOD_FLAG ,
146 CREATION_DATE
147 )
148 SELECT
149 PROJECT_ID ,
150 STRUCT_VERSION_ID ,
151 PROJECT_ELEMENT_ID ,
152 CALENDAR_TYPE ,
153 PERIOD_NAME ,
154 PLAN_VERSION_ID ,
155 RES_LIST_MEMBER_ID ,
156 QUANTITY ,
157 TXN_CURRENCY_CODE ,
158 TXN_RAW_COST ,
159 TXN_BRDN_COST ,
160 TXN_REVENUE ,
161 TXN_LABOR_RAW_COST ,
162 TXN_LABOR_BRDN_COST ,
163 TXN_EQUIP_RAW_COST ,
164 TXN_EQUIP_BRDN_COST ,
165 TXN_BASE_RAW_COST ,
166 TXN_BASE_BRDN_COST ,
167 TXN_BASE_LABOR_RAW_COST ,
168 TXN_BASE_LABOR_BRDN_COST ,
169 TXN_BASE_EQUIP_RAW_COST ,
170 TXN_BASE_EQUIP_BRDN_COST ,
171 PRJ_RAW_COST ,
172 PRJ_BRDN_COST ,
173 PRJ_REVENUE ,
174 PRJ_LABOR_RAW_COST ,
175 PRJ_LABOR_BRDN_COST ,
176 PRJ_EQUIP_RAW_COST ,
177 PRJ_EQUIP_BRDN_COST ,
178 PRJ_BASE_RAW_COST ,
179 PRJ_BASE_BRDN_COST ,
180 PRJ_BASE_LABOR_RAW_COST ,
181 PRJ_BASE_LABOR_BRDN_COST ,
182 PRJ_BASE_EQUIP_RAW_COST ,
183 PRJ_BASE_EQUIP_BRDN_COST ,
184 POU_RAW_COST ,
185 POU_BRDN_COST ,
186 POU_REVENUE ,
187 POU_LABOR_RAW_COST ,
188 POU_LABOR_BRDN_COST ,
189 POU_EQUIP_RAW_COST ,
190 POU_EQUIP_BRDN_COST ,
191 POU_BASE_RAW_COST ,
192 POU_BASE_BRDN_COST ,
193 POU_BASE_LABOR_RAW_COST ,
194 POU_BASE_LABOR_BRDN_COST ,
195 POU_BASE_EQUIP_RAW_COST ,
196 POU_BASE_EQUIP_BRDN_COST ,
197 LABOR_HOURS ,
198 EQUIPMENT_HOURS ,
199 BASE_LABOR_HOURS ,
200 BASE_EQUIP_HOURS ,
201 SOURCE_ID ,
202 ACT_LABOR_HRS ,
203 ACT_EQUIP_HRS ,
204 ACT_TXN_LABOR_BRDN_COST ,
205 ACT_TXN_EQUIP_BRDN_COST ,
206 ACT_TXN_BRDN_COST ,
207 ACT_PRJ_LABOR_BRDN_COST ,
208 ACT_PRJ_EQUIP_BRDN_COST ,
209 ACT_PRJ_BRDN_COST ,
210 ACT_PFC_LABOR_BRDN_COST ,
211 ACT_PFC_EQUIP_BRDN_COST ,
212 ACT_PFC_BRDN_COST ,
213 ETC_LABOR_HRS ,
214 ETC_EQUIP_HRS ,
215 ETC_TXNLABOR_BRDN_COST ,
216 ETC_TXN_EQUIP_BRDN_COST ,
217 ETC_TXN_BRDN_COST ,
218 ETC_PRJ_LABOR_BRDN_COST ,
219 ETC_PRJ_EQUIP_BRDN_COST ,
220 ETC_PRJ_BRDN_COST ,
221 ETC_POU_LABOR_BRDN_COST ,
222 ETC_POU_EQUIP_BRDN_COST ,
223 ETC_POU_BRDN_COST ,
224 ACT_TXN_RAW_COST ,
225 ACT_PRJ_RAW_COST ,
226 ACT_POU_RAW_COST ,
227 ETC_TXN_RAW_COST ,
228 ETC_PRJ_RAW_COST ,
229 ETC_POU_RAW_COST ,
230 ACT_TXN_LABOR_RAW_COST ,
231 ACT_TXN_EQUIP_RAW_COST ,
232 ACT_PRJ_LABOR_RAW_COST ,
233 ACT_PRJ_EQUIP_RAW_COST ,
234 ACT_POU_LABOR_RAW_COST ,
235 ACT_POU_EQUIP_RAW_COST ,
236 ETC_TXN_LABOR_RAW_COST ,
237 ETC_TXN_EQUIP_RAW_COST ,
238 ETC_PRJ_LABOR_RAW_COST ,
239 ETC_PRJ_EQUIP_RAW_COST ,
240 ETC_POU_LABOR_RAW_COST ,
241 ETC_POU_EQUIP_RAW_COST ,
242 ACT_POU_LABOR_BRDN_COST ,
243 ACT_POU_EQUIP_BRDN_COST ,
244 ACT_POU_BRDN_COST ,
245 ETC_TXN_LABOR_BRDN_COST ,
246 TXN_LPB_RAW_COST ,
247 TXN_LPB_BRDN_COST ,
248 TXN_LPB_LABOR_RAW_COST ,
249 TXN_LPB_LABOR_BRDN_COST ,
250 TXN_LPB_EQUIP_RAW_COST ,
251 TXN_LPB_EQUIP_BRDN_COST ,
252 PRJ_LPB_RAW_COST ,
253 PRJ_LPB_BRDN_COST ,
254 PRJ_LPB_LABOR_RAW_COST ,
255 PRJ_LPB_LABOR_BRDN_COST ,
256 PRJ_LPB_EQUIP_RAW_COST ,
257 PRJ_LPB_EQUIP_BRDN_COST ,
258 POU_LPB_RAW_COST ,
259 POU_LPB_BRDN_COST ,
260 POU_LPB_LABOR_RAW_COST ,
261 POU_LPB_LABOR_BRDN_COST ,
262 POU_LPB_EQUIP_RAW_COST ,
263 POU_LPB_EQUIP_BRDN_COST ,
264 LPB_LABOR_HOURS ,
265 LPB_EQUIP_HOURS ,
266 PERIOD_FLAG ,
267 SYSDATE
268 FROM
269 pji_fm_xbs_accum_tmp1 ;
270
271 END;
272 ----------------------------------------------------------------------------
273 -- Created DEGUPTA
274 -- To delete fin8 table data from pa_progress_pub.get_summarized_actuals API
275 -- Removing the delete from this package API get_summarized_actuals
276 -- Bug No. 5349102
277 ----------------------------------------------------------------------------
278 PROCEDURE DELETE_FIN8(
279 p_project_id IN NUMBER,
280 p_calendar_type IN VARCHAR2 DEFAULT NULL,
281 p_end_date IN DATE DEFAULT NULL,
282 p_err_flag IN NUMBER DEFAULT 0,
283 p_err_msg IN VARCHAR2 DEFAULT NULL
284 ) IS
285
286 l_period_type_id NUMBER;
287 l_calendar_type VARCHAR2(1);
288 l_org_id NUMBER;
289 l_end_period_id NUMBER;
290 BEGIN
291
292 pa_debug.log_message('DELETE_FIN8: p_project_id'||p_project_id||'p_err_flag'||p_err_flag||'p_err_msg '||p_err_msg , 3);
293 IF p_err_flag =1 THEN
294 update pji_pjp_proj_batch_map set act_err_msg=p_err_msg
295 where project_id=p_project_id;
296
297 else
298 print_time ( ' Deleting pji_fm_aggr_fin8 0001 p_calendar_type ' || p_calendar_type ) ;
299 IF (p_calendar_type = 'N') THEN
300 print_time ( ' Deleting pji_fm_aggr_fin8 0001.1 ' ) ;
301 l_calendar_type := 'A';
302 l_period_type_id := 2048;
303 ELSIF (p_calendar_type = 'P') THEN
304 print_time ( ' Deleting pji_fm_aggr_fin8 0001.2 ' ) ;
305 l_calendar_type := 'P';
306 l_period_type_id := 32;
307 ELSE
308 print_time ( ' Deleting pji_fm_aggr_fin8 0001.3 ' ) ;
309 l_calendar_type := 'G';
310 l_period_type_id := 32;
311 END IF;
312 print_time ( ' get_summarized_data 0002 ' ) ;
313
314 SELECT ORG_ID
315 INTO l_org_id
316 FROM pa_projects_all
317 WHERE project_id = p_project_id;
318
319 IF l_calendar_type ='A' then
320 l_end_period_id :=-1;
321 ELSE
322 BEGIN
323 SELECT cal.CAL_PERIOD_ID
324 INTO l_end_period_id
325 FROM pji_time_cal_period_v cal,
326 pji_org_extr_info info
327 WHERE TRUNC(p_end_date) BETWEEN
328 TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE) AND
329 info.ORG_ID = l_org_id AND
330 DECODE(l_calendar_type, 'P', info.PA_CALENDAR_ID,
331 info.GL_CALENDAR_ID) = cal.CALENDAR_ID;
332 EXCEPTION WHEN NO_DATA_FOUND THEN
333 Pa_Debug.log_message('Project Id:' || p_project_id
334 || ' Org Id:' || l_org_id
335 || ' End Date:' || p_end_date);
336 print_time ('Project Id:' || p_project_id
337 || ' Org Id:' || l_org_id
338 || ' End Date:' || p_end_date);
339 END;
340 END IF;
341
342 IF l_calendar_type = 'A' THEN
343 delete from pji_fm_aggr_fin8 fin where
344 fin.PROJECT_ID = p_project_id;
345 ELSE
346 delete from pji_fm_aggr_fin8 fin where
347 fin.PROJECT_ID = p_project_id
348 AND fin.RECVR_PERIOD_ID <= l_end_period_id;
349 END IF;
350 END IF;
351
352 END;
353
354
355
356 PROCEDURE get_summarized_data (
357 p_project_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
358 p_resource_list_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
359 p_struct_ver_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
360 p_start_date IN DATE := NULL,
361 p_end_date IN SYSTEM.pa_date_tbl_type := system.pa_date_tbl_type(),
362 p_start_period_name IN VARCHAR2 := NULL,
363 p_end_period_name IN VARCHAR2 := NULL,
364 p_calendar_type IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM. PA_VARCHAR2_1_TBL_TYPE(),
365 p_extraction_type IN VARCHAR2 := NULL,
366 p_calling_context IN VARCHAR2 := NULL,
367 p_record_type IN VARCHAR2,
368 p_currency_type IN NUMBER,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_code OUT NOCOPY VARCHAR2) IS
371
372 /* Commented for workplan progress
373 l_end_period_id NUMBER;
374 l_period_type_id NUMBER;
375 l_org_id NUMBER;
376 End of workplan progress */
377 l_end_period_id SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); /* Added for workplan progress */
378 l_currency_mask NUMBER;
379 l_period_type_id SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();/*Added for workplan progress */
380 l_periodic_flag VARCHAR2(1);
381 l_planres_level_flag VARCHAR2(1);
382 l_task_level_flag VARCHAR2(1);
383 l_task_rollup_flag VARCHAR2(1);
384 l_proj_level_flag VARCHAR2(1);
385 l_summarized_flag VARCHAR2(1);
386 l_msg_count NUMBER;
387 l_calendar_type SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
388 l_org_id SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();/*Added for workplan progress */
389 l_map_resource_list EXCEPTION;
390 l_get_summarized_data varchar2(1) :='Y';
391 l_summ_hasrun varchar2(1) :='N';
392
393
394 BEGIN
395
396 PJI_PJP_FP_CURR_WRAP.INIT_ERR_STACK
397 ( p_package_name => g_package_name
398 , x_return_status => x_return_status );
399
400
401 -- Cleanup tmp1 table for 3750147.
402 DELETE FROM pji_fm_xbs_accum_tmp1;
403
404 --DELETE FROM pa_res_list_map_tmp1;
405 DELETE FROM pa_res_list_map_tmp2; -- Bug#4726170
406
407
408 /* Commented for workplan progress
409 print_time ( ' get_summarized_data 0001 p_calendar_type ' || p_calendar_type ) ;
410
411 --------------------------------------------------
412 --Identifying the period type id and calendar type
413 --------------------------------------------------
414 --TODO: Need to add calendar type joins throughout
415 IF (p_calendar_type = 'N') THEN print_time ( ' get_summarized_data 0001.1 ' ) ;
416 l_calendar_type := 'A';
417 l_period_type_id := 2048;
418 ELSIF (p_calendar_type = 'P') THEN print_time ( ' get_summarized_data 0001.2 ' ) ;
419 l_calendar_type := 'P';
420 l_period_type_id := 32;
421 ELSE print_time ( ' get_summarized_data 0001.3 ' ) ;
422 l_calendar_type := 'G';
423 l_period_type_id := 32;
424 END IF; print_time ( ' get_summarized_data 0002 ' ) ;
425
426 End of workplan progress */
427
428 ----------------------
429 --Decoding record type
430 ----------------------
431 l_periodic_flag := SUBSTR( p_record_type, 1, 1);
432 l_planres_level_flag := SUBSTR( p_record_type, 2, 1);
433 l_task_level_flag := SUBSTR( p_record_type, 3, 1);
434 l_task_rollup_flag := NVL(SUBSTR( p_record_type, 4, 1), 'Y');
435
436 -- If l_proj_level_flag is Y, then return task id as 0, else return project element id.
437 IF (l_task_rollup_flag = 'N' AND l_task_level_flag = 'N') THEN -- BandF
438 l_proj_level_flag := 'Y';
439 ELSE -- Used by progress: NY, YN. YY never used.
440 l_proj_level_flag := 'N';
441 END IF;
442
443 /* Commented and moved down for workplan progress
444 IF l_planres_level_flag = 'Y' THEN
445 print_time ( ' get_summarized_data 0003 ' ) ; */
446
447 /* Changed the logic. Existing loop is split into 2 for loops.
448 Because to find the values of l_calendar_type, l_end_period_id, l_org_id
449 which are used in the rollup */
450
451 FOR i IN 1..p_project_ids.COUNT LOOP
452 print_time ( ' get_summarized_data 0004 ' ) ;
453
454 /* Added here for workplan progress */
455
456 l_calendar_type.extend;
457 l_end_period_id.extend;
458 l_period_type_id.extend;
459 l_org_id.extend;
460
461 print_time ( ' get_summarized_data 0001 p_calendar_type ' || p_calendar_type(i) ) ;
462 IF (p_calendar_type(i) = 'N') THEN
463 print_time ( ' get_summarized_data 0001.1 ' ) ;
464 l_calendar_type(i) := 'A';
465 l_period_type_id(i) := 2048;
466 ELSIF (p_calendar_type(i) = 'P') THEN
467 print_time ( ' get_summarized_data 0001.2 ' ) ;
468 l_calendar_type(i) := 'P';
469 l_period_type_id(i) := 32;
470 ELSE
471 print_time ( ' get_summarized_data 0001.3 ' ) ;
472 l_calendar_type(i) := 'G';
473 l_period_type_id(i) := 32;
474 END IF;
475 print_time ( ' get_summarized_data 0002 ' ) ;
476 /* End for workplan progress */
477
478
479 --Get org for the project
480 SELECT ORG_ID
481 INTO l_org_id(i)
482 FROM pa_projects_all
483 WHERE project_id = p_project_ids(i);
484 print_time ( ' get_summarized_data 0004.1 ' || l_org_id(i) || ' l_calendar_type ' || l_calendar_type(i) ) ;
485
486 -------------------------------
487 --Identifying the end period id
488 -------------------------------
489 /* Commented for workplan progress
490 SELECT cal.CAL_PERIOD_ID
491 INTO l_end_period_id
492 FROM pji_time_cal_period_v cal,
493 pji_org_extr_info info
494 WHERE TRUNC(p_end_date) BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE) AND
495 info.ORG_ID = l_org_id AND
496 DECODE(l_calendar_type, 'P',
497 info.PA_CALENDAR_ID,
498 info.GL_CALENDAR_ID) = cal.CALENDAR_ID; */
499
500 IF l_calendar_type(i) ='A' then
501 l_end_period_id(i) :=-1;
502 ELSE
503 BEGIN
504 SELECT cal.CAL_PERIOD_ID
505 INTO l_end_period_id(i)
506 FROM pji_time_cal_period_v cal,
507 pji_org_extr_info info
508 WHERE TRUNC(p_end_date(i)) BETWEEN
509 TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE) AND
510 info.ORG_ID = l_org_id(i) AND
511 DECODE(l_calendar_type(i), 'P', info.PA_CALENDAR_ID,
512 info.GL_CALENDAR_ID) = cal.CALENDAR_ID;
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515 Pa_Debug.log_message('Project Id:' || p_project_ids(i)
516 || ' Org Id:' || l_org_id(i)
517 || ' End Date:' || p_end_date(i));
518 print_time ('Project Id:' || p_project_ids(i)
519 || ' Org Id:' || l_org_id(i)
520 || ' End Date:' || p_end_date(i));
521 END;
522 END IF;
523
524 print_time ( ' get_summarized_data 0004.2 ' ) ;
525
526 END LOOP;
527
528 ---------------------------------------------
529 --ENTERED LEVEL DATA RETREIVAL
530 --If planning resource level data is required
531 --map to resource list and get data from
532 --transaction accum
533 ---------------------------------------------
534
535 FOR i IN 1..p_project_ids.COUNT LOOP
536 /*bug#4415960 added the summarization check in Publish mode */
537 /* commenting this as this is already handled,keeping the code for future requirement
538 l_summ_hasrun:='N';
539 l_get_summarized_data:='Y';
540 IF p_calling_context='P' THEN
541 begin
542 select 'Y'
543 into l_summ_hasrun
544 from dual
545 where exists (select 1 from pji_fp_xbs_accum_f
546 where project_id=p_project_ids(i)
547 and plan_version_id=-1
548 and rownum=1);
549 exception when no_data_found then
550 l_summ_hasrun:='N';
551 end;
552
553 if l_summ_hasrun ='N' and p_calling_context='P' then
554 l_get_summarized_data :='N';
555 end if;
556 END IF;
557 */
558 IF l_planres_level_flag = 'Y' THEN
559
560 --DELETE FROM pa_res_list_map_tmp1;
561 DELETE FROM pa_res_list_map_tmp2; -- Bug#4726170
562
563 print_time ( ' get_summarized_data 0004 ' ) ;
564
565 INSERT INTO pa_res_list_map_tmp1 (
566 PERSON_ID,
567 JOB_ID,
568 ORGANIZATION_ID,
569 VENDOR_ID,
570 EXPENDITURE_TYPE,
571 EVENT_TYPE,
572 NON_LABOR_RESOURCE,
573 EXPENDITURE_CATEGORY,
574 REVENUE_CATEGORY,
575 EVENT_TYPE_CLASSIFICATION,
576 SYSTEM_LINKAGE_FUNCTION,
577 PROJECT_ROLE_ID,
578 RESOURCE_CLASS_ID,
579 RESOURCE_CLASS_CODE,
580 BOM_LABOR_RESOURCE_ID,
581 BOM_EQUIP_RESOURCE_ID,
582 INVENTORY_ITEM_ID,
583 ITEM_CATEGORY_ID,
584 PERSON_TYPE_CODE,
585 BOM_RESOURCE_ID,
586 NAMED_ROLE,
587 TXN_SOURCE_ID,
588 FC_RES_TYPE_CODE ) --bug#3804500
589 SELECT DISTINCT /* Added for bug 3729366*/
590 decode(head.PERSON_ID, -1, null, head.PERSON_ID),
591 decode(head.JOB_ID, -1, null, head.JOB_ID),
592 decode(head.EXPENDITURE_ORGANIZATION_ID, -1, null, head.EXPENDITURE_ORGANIZATION_ID),
593 decode(head.VENDOR_ID,-1, null, head.VENDOR_ID),
594 decode(head.EXPENDITURE_TYPE, 'PJI$NULL', null,head.EXPENDITURE_TYPE),
595 decode(head.EVENT_TYPE, 'PJI$NULL', null, head.EVENT_TYPE),
596 nlr.NON_LABOR_RESOURCE,
597 decode(head.EXPENDITURE_CATEGORY, 'PJI$NULL', null, head.EXPENDITURE_CATEGORY),
598 decode(head.REVENUE_CATEGORY,'PJI$NULL', null,head.REVENUE_CATEGORY),
599 decode(head.EVENT_TYPE_CLASSIFICATION,'PJI$NULL', null, head.EVENT_TYPE_CLASSIFICATION),
600 decode(head.SYSTEM_LINKAGE_FUNCTION,'PJI$NULL', null,head.SYSTEM_LINKAGE_FUNCTION),
601 decode(head.PROJECT_ROLE_ID,-1,null, head.PROJECT_ROLE_ID), /*For bug 4590810 */
602 head.RESOURCE_CLASS_ID,
603 cls.RESOURCE_CLASS_CODE,
604 decode(head.BOM_LABOR_RESOURCE_ID, -1, null, head.BOM_LABOR_RESOURCE_ID),
605 decode(head.BOM_EQUIPMENT_RESOURCE_ID, -1, null, head.BOM_EQUIPMENT_RESOURCE_ID),
606 decode(head.INVENTORY_ITEM_ID, -1, null, head.INVENTORY_ITEM_ID),
607 decode(head.ITEM_CATEGORY_ID, -1, null, head.ITEM_CATEGORY_ID),
608 decode(head.PERSON_TYPE,'PJI$NULL', null,head.PERSON_TYPE),
609 decode(head.BOM_LABOR_RESOURCE_ID, -1, decode(head.BOM_EQUIPMENT_RESOURCE_ID, -1, null, head.BOM_EQUIPMENT_RESOURCE_ID), head.BOM_LABOR_RESOURCE_ID),
610 decode(accum.NAMED_ROLE,'PJI$NULL',null,accum.NAMED_ROLE), /*For Bug 5564306 and bug 4034467 */
611 head.TXN_ACCUM_HEADER_ID,
612 decode(head.EXPENDITURE_TYPE,'PJI$NULL',
613 decode(head.EVENT_TYPE,'PJI$NULL',
614 decode(head.EXPENDITURE_CATEGORY,'PJI$NULL',
615 decode(head.REVENUE_CATEGORY,'PJI$NULL',null,'REVENUE_CATEGORY'),'EXPENDITURE_CATEGORY'),'EVENT_TYPE'),'EXPENDITURE_TYPE')
616 FROM
617 (
618 SELECT /*+ NO_MERGE */ DISTINCT txn_accum_header_id, project_id,named_role -- Bug#5377911
619 FROM (
620 SELECT
621 txn_accum_header_id,
622 project_id,
623 named_role /*For bug 4590810 */
624 FROM
625 pji_fp_txn_accum
626 WHERE project_id = p_project_ids(i)
627 AND recvr_period_type='GL' --Bug#5356978
628 UNION ALL
629 SELECT
630 txn_accum_header_id,
631 project_id,
632 named_role /*For bug 4590810 */
633 FROM
634 pji_fm_aggr_fin7
635 WHERE project_id = p_project_ids(i)
636 AND recvr_period_type='GL' --Bug#5356978
637 )
638 ) accum,
639 pji_fp_txn_accum_header head,
640 pa_non_labor_resources nlr,
641 pa_resource_classes_b cls
642 WHERE
643 head.TXN_ACCUM_HEADER_ID = accum.TXN_ACCUM_HEADER_ID AND
644 accum.PROJECT_ID = p_project_ids(i) AND
645 nlr.NON_LABOR_RESOURCE_ID (+) = head.NON_LABOR_RESOURCE_ID AND
646 cls.RESOURCE_CLASS_ID = head.RESOURCE_CLASS_ID ;
647
648 print_time ( ' get_summarized_data 0004.3 ' ) ;
649
650
651
652 /* Added for bug 3729366 - Start */
653
654 INSERT INTO pa_res_list_map_tmp2
655 (TXN_SOURCE_ID,VENDOR_ID,PERSON_ID)
656 SELECT /* + index(hr, per_assignments_f_n12) index(prd, pji_time_cal_period_u1) */
657 distinct T.TXN_SOURCE_ID,HR.VENDOR_ID,HR.PERSON_ID
658 FROM pa_res_list_map_tmp1 t,
659 per_all_assignments_f hr, --Bug#5356978
660 pji_time_cal_period_v prd,
661 ( SELECT txn_accum_header_id,max(recvr_period_id) recvr_period_id -- Bug#5262851
662 FROM
663 (
664 SELECT
665 txn_accum_header_id,recvr_period_id
666 FROM pji_fp_txn_accum
667 WHERE recvr_period_type = 'GL'
668 and project_id = p_project_ids(i)
669 UNION ALL
670 SELECT /*+ index(pji_fm_aggr_fin7 pji_fm_aggr_fin7_n2) */
671 txn_accum_header_id,recvr_period_id
672 FROM pji_fm_aggr_fin7
673 WHERE recvr_period_type = 'GL'
674 and project_id = p_project_ids(i)
675 )
676 GROUP BY txn_accum_header_id -- Bug#5262851
677 )
678 det
679 WHERE
680 det.TXN_ACCUM_HEADER_ID = t.TXN_SOURCE_ID AND
681 t.PERSON_TYPE_CODE = 'CWK' AND
682 prd.CAL_PERIOD_ID = det.RECVR_PERIOD_ID AND
683 (prd.START_DATE BETWEEN hr.EFFECTIVE_START_DATE AND hr.EFFECTIVE_END_DATE) AND
684 hr.PERSON_ID = t.PERSON_ID AND
685 hr.PRIMARY_FLAG = 'Y' AND
686 hr.ASSIGNMENT_TYPE = 'C' AND
687 hr.VENDOR_ID is not null;
688
689
690
691 print_time ( ' get_summarized_data 0004.4 ' ) ;
692
693 UPDATE pa_res_list_map_tmp1 tmp
694 SET VENDOR_ID =
695 (
696 SELECT t1.VENDOR_ID
697 FROM pa_res_list_map_tmp2 t1
698 WHERE t1.TXN_SOURCE_ID=tmp.TXN_SOURCE_ID
699 AND t1.PERSON_ID=tmp.PERSON_ID
700 AND tmp.PERSON_ID IS NOT NULL
701 AND EXISTS
702 (
703 SELECT NULL
704 FROM pa_res_list_map_tmp2 t1
705 WHERE t1.TXN_SOURCE_ID=tmp.TXN_SOURCE_ID
706 AND t1.PERSON_ID=tmp.PERSON_ID
707 )
708 )
709 WHERE VENDOR_ID IS NULL;
710
711 /* Added for bug 3729366 - End */
712 print_time ( ' get_summarized_data 0004.41 ' ) ;
713
714 pa_resource_mapping.g_called_process :='ACTUALS';
715
716 pa_resource_mapping.map_resource_list (
717 p_resource_list_id => p_resource_list_ids (i),
718 p_project_id => p_project_ids(i),
719 x_return_status => x_return_status,
720 x_msg_count => l_msg_count,
721 x_msg_data => x_msg_code );
722
723 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN /* Bug No. 4461060 */
724 RAISE l_map_resource_list;
725 END IF;
726
727
728 pa_resource_mapping.g_called_process :='PLAN';
729
730 print_time ( ' get_summarized_data 0004.42 ' ) ;
731
732 IF nvl(p_calling_context,'F') in ('P','W') THEN
733 IF p_extraction_type <>'INCREMENTAL' THEN
734
735 INSERT INTO pji_fm_xbs_accum_tmp1
736 (
737 SOURCE_ID, RES_LIST_MEMBER_ID, PROJECT_ID, STRUCT_VERSION_ID,
738 PROJECT_ELEMENT_ID, CALENDAR_TYPE, PERIOD_NAME, PLAN_VERSION_ID,
739 TXN_CURRENCY_CODE, TXN_RAW_COST, TXN_BRDN_COST, TXN_REVENUE,
740 TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST,
741 QUANTITY, PRJ_RAW_COST, PRJ_BRDN_COST, PRJ_REVENUE,
742 PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST, PRJ_EQUIP_BRDN_COST,
743 POU_RAW_COST, POU_BRDN_COST, POU_REVENUE, POU_LABOR_RAW_COST,
744 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
745 EQUIPMENT_HOURS, PERIOD_FLAG, BASE_LABOR_HOURS, POU_LPB_RAW_COST,
746 POU_LPB_BRDN_COST, ACT_TXN_RAW_COST, ACT_TXN_BRDN_COST, ACT_TXN_LABOR_RAW_COST,
747 ACT_TXN_LABOR_BRDN_COST,ACT_TXN_EQUIP_RAW_COST, ACT_TXN_EQUIP_BRDN_COST,ACT_PRJ_RAW_COST,
748 ACT_PRJ_BRDN_COST, ACT_PRJ_LABOR_RAW_COST, ACT_PRJ_LABOR_BRDN_COST,ACT_PRJ_EQUIP_RAW_COST,
749 ACT_PRJ_EQUIP_BRDN_COST,ACT_POU_RAW_COST, ACT_POU_BRDN_COST, ACT_POU_LABOR_RAW_COST,
750 ACT_POU_LABOR_BRDN_COST,ACT_POU_EQUIP_RAW_COST, ACT_POU_EQUIP_BRDN_COST,ACT_LABOR_HRS,
751 ACT_EQUIP_HRS, MIN_START_DATE, MAX_END_DATE
752 )
753 SELECT /*+ NO_MERGE */ --Bug#5356978
754 SOURCE_ID, RES_LIST_MEMBER_ID, PROJECT_ID, STRUCT_VERSION_ID,
755 PROJECT_ELEMENT_ID, CALENDAR_TYPE, PERIOD_NAME, PLAN_VERSION_ID,
756 TXN_CURRENCY_CODE, TXN_RAW_COST, TXN_BRDN_COST, TXN_REVENUE,
757 TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST,
758 QUANTITY, PRJ_RAW_COST, PRJ_BRDN_COST, PRJ_REVENUE,
759 PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST, PRJ_EQUIP_BRDN_COST,
760 POU_RAW_COST, POU_BRDN_COST, POU_REVENUE, POU_LABOR_RAW_COST,
761 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
762 EQUIPMENT_HOURS, PERIOD_FLAG, BASE_LABOR_HOURS, POU_LPB_RAW_COST,
763 POU_LPB_BRDN_COST, TXN_RAW_COST, TXN_BRDN_COST, TXN_LABOR_RAW_COST,
764 TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST, PRJ_RAW_COST,
765 PRJ_BRDN_COST, PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST,
766 PRJ_EQUIP_BRDN_COST, POU_RAW_COST, POU_BRDN_COST, POU_LABOR_RAW_COST,
767 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
768 EQUIPMENT_HOURS, MIN_START_DATE, MAX_END_DATE
769 FROM
770 (
771
772 /* Below select statment is added for workplan progress (periodic data) from PJI_FP_TXN_ACCUM
773 To identify those records check for period_flag ='Y'. This is for FULL and PARTIAL
774 Retrieve Task / Project Level Data for PA/ GL Period
775 */
776 SELECT
777 /*tmp4.TXN_SOURCE_ID, Commented for workplan progress */
778 min(null) SOURCE_ID,
779 tmp4.RESOURCE_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
780 accum.PROJECT_ID PROJECT_ID,
781 p_struct_ver_ids(i) STRUCT_VERSION_ID,
782 DECODE(l_proj_level_flag,'Y',0,accum.TASK_ID) PROJECT_ELEMENT_ID,
783 l_calendar_type(i) CALENDAR_TYPE,
784 time.NAME PERIOD_NAME,
785 -1 PLAN_VERSION_ID,
786 accum.TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
787 sum(accum.TXN_RAW_COST) TXN_RAW_COST,
788 sum(accum.TXN_BRDN_COST) TXN_BRDN_COST,
789 sum(accum.TXN_REVENUE) TXN_REVENUE,
790 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.TXN_RAW_COST,0)) TXN_LABOR_RAW_COST,
791 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.TXN_BRDN_COST,0)) TXN_LABOR_BRDN_COST,
792 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.TXN_RAW_COST,0)) TXN_EQUIP_RAW_COST,
793 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.TXN_BRDN_COST,0)) TXN_EQUIP_BRDN_COST,
794 sum(accum.QUANTITY) QUANTITY,
795 sum(accum.PRJ_RAW_COST) PRJ_RAW_COST,
796 sum(accum.PRJ_BRDN_COST) PRJ_BRDN_COST,
797 sum(accum.PRJ_REVENUE) PRJ_REVENUE,
798 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.PRJ_RAW_COST,0)) PRJ_LABOR_RAW_COST,
799 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.PRJ_BRDN_COST,0)) PRJ_LABOR_BRDN_COST,
800 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.PRJ_RAW_COST,0)) PRJ_EQUIP_RAW_COST,
801 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.PRJ_BRDN_COST,0)) PRJ_EQUIP_BRDN_COST,
802 sum(accum.POU_RAW_COST) POU_RAW_COST,
803 sum(accum.POU_BRDN_COST) POU_BRDN_COST,
804 sum(accum.POU_REVENUE) POU_REVENUE,
805 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.POU_RAW_COST,0)) POU_LABOR_RAW_COST,
806 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.POU_BRDN_COST,0)) POU_LABOR_BRDN_COST,
807 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.POU_RAW_COST,0)) POU_EQUIP_RAW_COST,
808 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.POU_BRDN_COST,0)) POU_EQUIP_BRDN_COST,
809 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.QUANTITY,0)) LABOR_HOURS,
810 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.QUANTITY,0)) EQUIPMENT_HOURS,
811 MIN('Y') PERIOD_FLAG,
812 null BASE_LABOR_HOURS,
813 null POU_LPB_RAW_COST,
814 null POU_LPB_BRDN_COST,
815 MIN(time.START_DATE) MIN_START_DATE,
816 MAX(time.END_DATE) MAX_END_DATE
817 FROM
818 pa_res_list_map_tmp4 tmp4,
819 pji_fm_aggr_fin8 accum,
820 pji_time_cal_period_v time,
821 pji_org_extr_info info
822 WHERE
823 tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
824 accum.PROJECT_ID = p_project_ids(i) AND
825 accum.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
826 /*Added 'G' in below decode for workplan progress */
827 accum.RECVR_PERIOD_TYPE = decode(l_calendar_type(i) , 'P', 'PA', 'G','GL') AND
828 time.CALENDAR_ID = decode(l_calendar_type(i) , 'P', info.PA_CALENDAR_ID,
829 'G',info.GL_CALENDAR_ID) AND
830 info.ORG_ID = l_org_id(i) AND
831 time.CAL_PERIOD_ID <= l_end_period_id(i) AND
832 p_calling_context in ('P', 'W') /* added for workplan progress*/ AND
833 p_extraction_type in ('FULL','PARTIAL')
834 GROUP BY
835 /*tmp4.TXN_SOURCE_ID, Commented for workplan progress */
836 tmp4.RESOURCE_LIST_MEMBER_ID,
837 accum.PROJECT_ID,
838 p_struct_ver_ids(i),
839 DECODE(l_proj_level_flag, 'Y', 0, accum.TASK_ID) ,
840 l_calendar_type(i),
841 time.NAME,
842 -1,
843 accum.TXN_CURRENCY_CODE
844
845 -- Added the following Two union all caluses to populate
846 -- data for nontime phase: Bug : 4224314
847 UNION ALL
848 /* Below select statment is added for workplan progress (periodic data) from PJI_FP_TXN_ACCUM
849 To identify those records check for period_flag ='Y'. This is for FULL and PARTIAL
850 Retrieve Task / Project Level Data for Non Time Phased Period
851 */
852 SELECT
853 min(null) SOURCE_ID,
854 tmp4.RESOURCE_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
855 accum.PROJECT_ID PROJECT_ID,
856 p_struct_ver_ids(i) STRUCT_VERSION_ID,
857 DECODE(l_proj_level_flag,'Y',0,accum.TASK_ID) PROJECT_ELEMENT_ID,
858 l_calendar_type(i) CALENDAR_TYPE,
859 NULL PERIOD_NAME,
860 -1 PLAN_VERSION_ID,
861 accum.TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
862 sum(accum.TXN_RAW_COST) TXN_RAW_COST,
863 sum(accum.TXN_BRDN_COST) TXN_BRDN_COST,
864 sum(accum.TXN_REVENUE) TXN_REVENUE,
865 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.TXN_RAW_COST,0)) TXN_LABOR_RAW_COST,
866 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.TXN_BRDN_COST,0)) TXN_LABOR_BRDN_COST,
867 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.TXN_RAW_COST,0)) TXN_EQUIP_RAW_COST,
868 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.TXN_BRDN_COST,0)) TXN_EQUIP_BRDN_COST,
869 sum(accum.QUANTITY) QUANTITY,
870 sum(accum.PRJ_RAW_COST) PRJ_RAW_COST,
871 sum(accum.PRJ_BRDN_COST) PRJ_BRDN_COST,
872 sum(accum.PRJ_REVENUE) PRJ_REVENUE,
873 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.PRJ_RAW_COST,0)) PRJ_LABOR_RAW_COST,
874 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.PRJ_BRDN_COST,0)) PRJ_LABOR_BRDN_COST,
875 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.PRJ_RAW_COST,0)) PRJ_EQUIP_RAW_COST,
876 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.PRJ_BRDN_COST,0)) PRJ_EQUIP_BRDN_COST,
877 sum(accum.POU_RAW_COST) POU_RAW_COST,
878 sum(accum.POU_BRDN_COST) POU_BRDN_COST,
879 sum(accum.POU_REVENUE) POU_REVENUE,
880 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.POU_RAW_COST,0)) POU_LABOR_RAW_COST,
881 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.POU_BRDN_COST,0)) POU_LABOR_BRDN_COST,
882 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.POU_RAW_COST,0)) POU_EQUIP_RAW_COST,
883 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.POU_BRDN_COST,0)) POU_EQUIP_BRDN_COST,
884 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',accum.QUANTITY,0)) LABOR_HOURS,
885 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',accum.QUANTITY,0)) EQUIPMENT_HOURS,
886 MIN('Y') PERIOD_FLAG,
887 null BASE_LABOR_HOURS,
888 null POU_LPB_RAW_COST,
889 null POU_LPB_BRDN_COST,
890 MIN(time.START_DATE) MIN_START_DATE,
891 MAX(time.END_DATE) MAX_END_DATE
892 FROM
893 pa_res_list_map_tmp4 tmp4,
894 pji_fm_aggr_fin8 accum ,
895 pji_time_cal_period_v time --, pji_org_extr_info info
896 WHERE
897 tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
898 accum.PROJECT_ID = p_project_ids(i) AND
899 accum.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
900 accum.RECVR_PERIOD_TYPE = 'GL' AND
901 l_calendar_type(i) = 'A' AND
902 p_calling_context in ('P', 'W') AND
903 p_extraction_type in ('FULL','PARTIAL')
904 GROUP BY
905 tmp4.RESOURCE_LIST_MEMBER_ID,
906 accum.PROJECT_ID,
907 p_struct_ver_ids(i),
908 DECODE(l_proj_level_flag, 'Y', 0, accum.TASK_ID) ,
909 l_calendar_type(i),
910 -1 ,
911 accum.TXN_CURRENCY_CODE
912 );
913
914 else /* p_extraction_type ='INCREMENTAL*/
915
916 INSERT INTO pji_fm_xbs_accum_tmp1
917 (
918 SOURCE_ID, RES_LIST_MEMBER_ID, PROJECT_ID, STRUCT_VERSION_ID,
919 PROJECT_ELEMENT_ID, CALENDAR_TYPE, PERIOD_NAME, PLAN_VERSION_ID,
920 TXN_CURRENCY_CODE, TXN_RAW_COST, TXN_BRDN_COST, TXN_REVENUE,
921 TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST,
922 QUANTITY, PRJ_RAW_COST, PRJ_BRDN_COST, PRJ_REVENUE,
923 PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST, PRJ_EQUIP_BRDN_COST,
924 POU_RAW_COST, POU_BRDN_COST, POU_REVENUE, POU_LABOR_RAW_COST,
925 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
926 EQUIPMENT_HOURS, PERIOD_FLAG, BASE_LABOR_HOURS, POU_LPB_RAW_COST,
927 POU_LPB_BRDN_COST, ACT_TXN_RAW_COST, ACT_TXN_BRDN_COST, ACT_TXN_LABOR_RAW_COST,
928 ACT_TXN_LABOR_BRDN_COST,ACT_TXN_EQUIP_RAW_COST, ACT_TXN_EQUIP_BRDN_COST,ACT_PRJ_RAW_COST,
929 ACT_PRJ_BRDN_COST, ACT_PRJ_LABOR_RAW_COST, ACT_PRJ_LABOR_BRDN_COST,ACT_PRJ_EQUIP_RAW_COST,
930 ACT_PRJ_EQUIP_BRDN_COST,ACT_POU_RAW_COST, ACT_POU_BRDN_COST, ACT_POU_LABOR_RAW_COST,
931 ACT_POU_LABOR_BRDN_COST,ACT_POU_EQUIP_RAW_COST, ACT_POU_EQUIP_BRDN_COST,ACT_LABOR_HRS,
932 ACT_EQUIP_HRS, MIN_START_DATE, MAX_END_DATE
933 )
934 SELECT /*+ NO_MERGE */ --Bug#5356978
935 SOURCE_ID, RES_LIST_MEMBER_ID, PROJECT_ID, STRUCT_VERSION_ID,
936 PROJECT_ELEMENT_ID, CALENDAR_TYPE, PERIOD_NAME, PLAN_VERSION_ID,
937 TXN_CURRENCY_CODE, TXN_RAW_COST, TXN_BRDN_COST, TXN_REVENUE,
938 TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST,
939 QUANTITY, PRJ_RAW_COST, PRJ_BRDN_COST, PRJ_REVENUE,
940 PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST, PRJ_EQUIP_BRDN_COST,
941 POU_RAW_COST, POU_BRDN_COST, POU_REVENUE, POU_LABOR_RAW_COST,
942 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
943 EQUIPMENT_HOURS, PERIOD_FLAG, BASE_LABOR_HOURS, POU_LPB_RAW_COST,
944 POU_LPB_BRDN_COST, TXN_RAW_COST, TXN_BRDN_COST, TXN_LABOR_RAW_COST,
945 TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST, PRJ_RAW_COST,
946 PRJ_BRDN_COST, PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST,
947 PRJ_EQUIP_BRDN_COST, POU_RAW_COST, POU_BRDN_COST, POU_LABOR_RAW_COST,
948 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
949 EQUIPMENT_HOURS, MIN_START_DATE, MAX_END_DATE
950 FROM
951 (
952 /* Below select statment is added for workplan progress (periodic data) from PJI_FM_AGGR_FIN7
953 To identify those records check for period_flag ='Y'. This is for INCREMENTAL
954 Retrieve Task / Project Level Data for PA/ GL Period
955 */
956 SELECT
957 /* tmp4.TXN_SOURCE_ID, Commented for workplan progress */
958 min(null) SOURCE_ID,
959 tmp4.RESOURCE_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
960 fin.PROJECT_ID PROJECT_ID,
961 p_struct_ver_ids(i) STRUCT_VERSION_ID,
962 DECODE(l_proj_level_flag,'Y',0,fin.TASK_ID) PROJECT_ELEMENT_ID,
963 l_calendar_type(i) CALENDAR_TYPE,
964 time.NAME PERIOD_NAME,
965 -1 PLAN_VERSION_ID,
966 fin.TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
967 sum(fin.TXN_RAW_COST) TXN_RAW_COST,
968 sum(fin.TXN_BRDN_COST) TXN_BRDN_COST,
969 sum(fin.TXN_REVENUE) TXN_REVENUE,
970 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.TXN_RAW_COST,0)) TXN_LABOR_RAW_COST,
971 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.TXN_BRDN_COST,0)) TXN_LABOR_BRDN_COST,
972 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.TXN_RAW_COST,0)) TXN_EQUIP_RAW_COST,
973 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.TXN_BRDN_COST,0)) TXN_EQUIP_BRDN_COST,
974 sum(fin.QUANTITY) QUANTITY,
975 sum(fin.PRJ_RAW_COST) PRJ_RAW_COST,
976 sum(fin.PRJ_BRDN_COST) PRJ_BRDN_COST,
977 sum(fin.PRJ_REVENUE) PRJ_REVENUE,
978 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.PRJ_RAW_COST,0)) PRJ_LABOR_RAW_COST,
979 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.PRJ_BRDN_COST,0)) PRJ_LABOR_BRDN_COST,
980 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.PRJ_RAW_COST,0)) PRJ_EQUIP_RAW_COST,
981 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.PRJ_BRDN_COST,0)) PRJ_EQUIP_BRDN_COST,
982 sum(fin.POU_RAW_COST) POU_RAW_COST,
983 sum(fin.POU_BRDN_COST) POU_BRDN_COST,
984 sum(fin.POU_REVENUE) POU_REVENUE,
985 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.POU_RAW_COST,0)) POU_LABOR_RAW_COST,
986 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.POU_BRDN_COST,0)) POU_LABOR_BRDN_COST,
987 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.POU_RAW_COST,0)) POU_EQUIP_RAW_COST,
988 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.POU_BRDN_COST,0)) POU_EQUIP_BRDN_COST,
989 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.QUANTITY,0)) LABOR_HOURS,
990 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.QUANTITY,0)) EQUIPMENT_HOURS,
991 MIN('Y') PERIOD_FLAG,
992 null BASE_LABOR_HOURS,
993 null POU_LPB_RAW_COST,
994 null POU_LPB_BRDN_COST,
995 MIN(time.START_DATE) MIN_START_DATE,
996 MAX(time.END_DATE) MAX_END_DATE
997 FROM
998 pa_res_list_map_tmp4 tmp4,
999 pji_fm_aggr_fin8 fin,
1000 pji_time_cal_period_v time,
1001 pji_org_extr_info info
1002 WHERE
1003 tmp4.TXN_SOURCE_ID = fin.TXN_ACCUM_HEADER_ID AND
1004 fin.PROJECT_ID = p_project_ids(i) AND
1005 fin.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
1006 /*Added 'G' in below decode for workplan progress */
1007 fin.RECVR_PERIOD_TYPE = decode(l_calendar_type(i) , 'P', 'PA', 'G','GL') AND
1008 time.CALENDAR_ID = decode(l_calendar_type(i) , 'P', info.PA_CALENDAR_ID,
1009 'G',info.GL_CALENDAR_ID) AND
1010 info.ORG_ID = l_org_id(i) AND
1011 time.CAL_PERIOD_ID <= l_end_period_id(i) AND
1012 p_calling_context in ('P', 'W') /* added for workplan progress*/ AND
1013 p_extraction_type = 'INCREMENTAL'
1014 GROUP BY
1015 /*tmp4.TXN_SOURCE_ID, Commented for workplan progress */
1016 tmp4.RESOURCE_LIST_MEMBER_ID,
1017 fin.PROJECT_ID,
1018 p_struct_ver_ids(i),
1019 DECODE(l_proj_level_flag, 'Y', 0, fin.TASK_ID),
1020 l_calendar_type(i),
1021 time.NAME,
1022 -1,
1023 fin.TXN_CURRENCY_CODE
1024
1025 -- Added the following Two union all caluses to populate
1026 -- data for nontime phase: Bug : 4224314
1027 UNION ALL
1028 /* Below select statment is added for workplan progress (periodic data) from PJI_FP_TXN_ACCUM
1029 To identify those records check for period_flag ='Y'. This is for FULL and PARTIAL
1030 Retrieve Task / Project Level Data for Non Time Phased Period
1031 */
1032 SELECT
1033 min(null) SOURCE_ID,
1034 tmp4.RESOURCE_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
1035 fin.PROJECT_ID PROJECT_ID,
1036 p_struct_ver_ids(i) STRUCT_VERSION_ID,
1037 DECODE(l_proj_level_flag,'Y',0,fin.TASK_ID) PROJECT_ELEMENT_ID,
1038 l_calendar_type(i) CALENDAR_TYPE,
1039 NULL PERIOD_NAME,
1040 -1 PLAN_VERSION_ID,
1041 fin.TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
1042 sum(fin.TXN_RAW_COST) TXN_RAW_COST,
1043 sum(fin.TXN_BRDN_COST) TXN_BRDN_COST,
1044 sum(fin.TXN_REVENUE) TXN_REVENUE,
1045 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.TXN_RAW_COST,0)) TXN_LABOR_RAW_COST,
1046 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.TXN_BRDN_COST,0)) TXN_LABOR_BRDN_COST,
1047 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.TXN_RAW_COST,0)) TXN_EQUIP_RAW_COST,
1048 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.TXN_BRDN_COST,0)) TXN_EQUIP_BRDN_COST,
1049 sum(fin.QUANTITY) QUANTITY,
1050 sum(fin.PRJ_RAW_COST) PRJ_RAW_COST,
1051 sum(fin.PRJ_BRDN_COST) PRJ_BRDN_COST,
1052 sum(fin.PRJ_REVENUE) PRJ_REVENUE,
1053 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.PRJ_RAW_COST,0)) PRJ_LABOR_RAW_COST,
1054 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.PRJ_BRDN_COST,0)) PRJ_LABOR_BRDN_COST,
1055 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.PRJ_RAW_COST,0)) PRJ_EQUIP_RAW_COST,
1056 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.PRJ_BRDN_COST,0)) PRJ_EQUIP_BRDN_COST,
1057 sum(fin.POU_RAW_COST) POU_RAW_COST,
1058 sum(fin.POU_BRDN_COST) POU_BRDN_COST,
1059 sum(fin.POU_REVENUE) POU_REVENUE,
1060 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.POU_RAW_COST,0)) POU_LABOR_RAW_COST,
1061 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.POU_BRDN_COST,0)) POU_LABOR_BRDN_COST,
1062 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.POU_RAW_COST,0)) POU_EQUIP_RAW_COST,
1063 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.POU_BRDN_COST,0)) POU_EQUIP_BRDN_COST,
1064 sum(decode(tmp4.RESOURCE_CLASS_CODE,'PEOPLE',fin.QUANTITY,0)) LABOR_HOURS,
1065 sum(decode(tmp4.RESOURCE_CLASS_CODE,'EQUIPMENT',fin.QUANTITY,0)) EQUIPMENT_HOURS,
1066 MIN('Y') PERIOD_FLAG,
1067 null BASE_LABOR_HOURS,
1068 null POU_LPB_RAW_COST,
1069 null POU_LPB_BRDN_COST,
1070 MIN(time.START_DATE) MIN_START_DATE,
1071 MAX(time.END_DATE) MAX_END_DATE
1072 FROM
1073 pa_res_list_map_tmp4 tmp4,
1074 pji_fm_aggr_fin8 fin ,
1075 pji_time_cal_period_v time --, pji_org_extr_info info
1076 WHERE
1077 tmp4.TXN_SOURCE_ID = fin.TXN_ACCUM_HEADER_ID AND
1078 fin.PROJECT_ID = p_project_ids(i) AND
1079 fin.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
1080 fin.RECVR_PERIOD_TYPE = 'GL' and
1081 l_calendar_type(i) = 'A' AND
1082 p_calling_context in ('P', 'W') AND
1083 p_extraction_type in ('INCREMENTAL')
1084 GROUP BY
1085 tmp4.RESOURCE_LIST_MEMBER_ID,
1086 fin.PROJECT_ID,
1087 p_struct_ver_ids(i),
1088 DECODE(l_proj_level_flag, 'Y', 0, fin.TASK_ID),
1089 l_calendar_type(i),
1090 -1 ,
1091 fin.TXN_CURRENCY_CODE
1092 );
1093 END IF;/* IF p_extraction_type <>'INCREMENTAL' THEN*/
1094 ELSE /* nvl(p_calling_context,'F') NOT IN ('P','W') */
1095
1096 INSERT INTO pji_fm_xbs_accum_tmp1
1097 (
1098 SOURCE_ID, RES_LIST_MEMBER_ID, PROJECT_ID, STRUCT_VERSION_ID,
1099 PROJECT_ELEMENT_ID, CALENDAR_TYPE, PERIOD_NAME, PLAN_VERSION_ID,
1100 TXN_CURRENCY_CODE, TXN_RAW_COST, TXN_BRDN_COST, TXN_REVENUE,
1101 TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST,
1102 QUANTITY, PRJ_RAW_COST, PRJ_BRDN_COST, PRJ_REVENUE,
1103 PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST, PRJ_EQUIP_BRDN_COST,
1104 POU_RAW_COST, POU_BRDN_COST, POU_REVENUE, POU_LABOR_RAW_COST,
1105 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
1106 EQUIPMENT_HOURS, PERIOD_FLAG, BASE_LABOR_HOURS, POU_LPB_RAW_COST,
1107 POU_LPB_BRDN_COST, ACT_TXN_RAW_COST, ACT_TXN_BRDN_COST, ACT_TXN_LABOR_RAW_COST,
1108 ACT_TXN_LABOR_BRDN_COST,ACT_TXN_EQUIP_RAW_COST, ACT_TXN_EQUIP_BRDN_COST,ACT_PRJ_RAW_COST,
1109 ACT_PRJ_BRDN_COST, ACT_PRJ_LABOR_RAW_COST, ACT_PRJ_LABOR_BRDN_COST,ACT_PRJ_EQUIP_RAW_COST,
1110 ACT_PRJ_EQUIP_BRDN_COST,ACT_POU_RAW_COST, ACT_POU_BRDN_COST, ACT_POU_LABOR_RAW_COST,
1111 ACT_POU_LABOR_BRDN_COST,ACT_POU_EQUIP_RAW_COST, ACT_POU_EQUIP_BRDN_COST,ACT_LABOR_HRS,
1112 ACT_EQUIP_HRS, MIN_START_DATE, MAX_END_DATE
1113 )
1114 SELECT
1115 SOURCE_ID, RES_LIST_MEMBER_ID, PROJECT_ID, STRUCT_VERSION_ID,
1116 PROJECT_ELEMENT_ID, CALENDAR_TYPE, PERIOD_NAME, PLAN_VERSION_ID,
1117 TXN_CURRENCY_CODE, TXN_RAW_COST, TXN_BRDN_COST, TXN_REVENUE,
1118 TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST,
1119 QUANTITY, PRJ_RAW_COST, PRJ_BRDN_COST, PRJ_REVENUE,
1120 PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST, PRJ_EQUIP_BRDN_COST,
1121 POU_RAW_COST, POU_BRDN_COST, POU_REVENUE, POU_LABOR_RAW_COST,
1122 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
1123 EQUIPMENT_HOURS, PERIOD_FLAG, BASE_LABOR_HOURS, POU_LPB_RAW_COST,
1124 POU_LPB_BRDN_COST, TXN_RAW_COST, TXN_BRDN_COST, TXN_LABOR_RAW_COST,
1125 TXN_LABOR_BRDN_COST, TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST, PRJ_RAW_COST,
1126 PRJ_BRDN_COST, PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST,
1127 PRJ_EQUIP_BRDN_COST, POU_RAW_COST, POU_BRDN_COST, POU_LABOR_RAW_COST,
1128 POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST, LABOR_HOURS,
1129 EQUIPMENT_HOURS, MIN_START_DATE, MAX_END_DATE
1130 FROM
1131 (
1132 SELECT
1133 MIN(SOURCE_ID) SOURCE_ID,
1134 RES_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
1135 PROJECT_ID PROJECT_ID,
1136 STRUCT_VERSION_ID STRUCT_VERSION_ID,
1137 DECODE(l_proj_level_flag,'Y',0,PROJECT_ELEMENT_ID) PROJECT_ELEMENT_ID ,
1138 CALENDAR_TYPE CALENDAR_TYPE,
1139 PERIOD_NAME PERIOD_NAME,
1140 PLAN_VERSION_ID PLAN_VERSION_ID,
1141 TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
1142 SUM(TXN_RAW_COST) TXN_RAW_COST,
1143 SUM(TXN_BRDN_COST) TXN_BRDN_COST,
1144 SUM(TXN_REVENUE) TXN_REVENUE,
1145 SUM(TXN_LABOR_RAW_COST) TXN_LABOR_RAW_COST,
1146 SUM(TXN_LABOR_BRDN_COST) TXN_LABOR_BRDN_COST,
1147 SUM(TXN_EQUIP_RAW_COST) TXN_EQUIP_RAW_COST,
1148 SUM(TXN_EQUIP_BRDN_COST) TXN_EQUIP_BRDN_COST,
1149 SUM(QUANTITY) QUANTITY,
1150 SUM(PRJ_RAW_COST) PRJ_RAW_COST,
1151 SUM(PRJ_BRDN_COST) PRJ_BRDN_COST,
1152 SUM(PRJ_REVENUE) PRJ_REVENUE,
1153 SUM(PRJ_LABOR_RAW_COST) PRJ_LABOR_RAW_COST,
1154 SUM(PRJ_LABOR_BRDN_COST) PRJ_LABOR_BRDN_COST,
1155 SUM(PRJ_EQUIP_RAW_COST) PRJ_EQUIP_RAW_COST,
1156 SUM(PRJ_EQUIP_BRDN_COST) PRJ_EQUIP_BRDN_COST,
1157 SUM(POU_RAW_COST) POU_RAW_COST,
1158 SUM(POU_BRDN_COST) POU_BRDN_COST,
1159 SUM(POU_REVENUE) POU_REVENUE,
1160 SUM(POU_LABOR_RAW_COST) POU_LABOR_RAW_COST,
1161 SUM(POU_LABOR_BRDN_COST) POU_LABOR_BRDN_COST,
1162 SUM(POU_EQUIP_RAW_COST) POU_EQUIP_RAW_COST,
1163 SUM(POU_EQUIP_BRDN_COST) POU_EQUIP_BRDN_COST,
1164 SUM(LABOR_HOURS) LABOR_HOURS,
1165 SUM(EQUIP_HOURS) EQUIPMENT_HOURS,
1166 MIN(PERIOD_FLAG) PERIOD_FLAG,
1167 SUM(INCR_QUANTITY) BASE_LABOR_HOURS,
1168 SUM(INCR_POU_RAW_COST) POU_LPB_RAW_COST,
1169 SUM(INCR_POU_BRDN_COST) POU_LPB_BRDN_COST,
1170 MIN(START_DATE) MIN_START_DATE,
1171 MAX(END_DATE) MAX_END_DATE
1172 FROM
1173 (
1174 SELECT --Retreives actuals data by resource list as ITD amounts for TIME PHASED Calendar
1175 --from pji_fp_txn_Accum
1176 /*tmp4.TXN_SOURCE_ID, Commented for workplan progress */
1177 (null) SOURCE_ID,
1178 tmp4.RESOURCE_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
1179 accum.PROJECT_ID PROJECT_ID,
1180 p_struct_ver_ids(i) STRUCT_VERSION_ID,
1181 accum.TASK_ID PROJECT_ELEMENT_ID,
1182 l_calendar_type(i) CALENDAR_TYPE,
1183 decode(l_periodic_flag,'Y',time.NAME,null) PERIOD_NAME, /* Added for workplan progress */
1184 -1 PLAN_VERSION_ID,
1185 accum.TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
1186 accum.TXN_RAW_COST TXN_RAW_COST,
1187 accum.TXN_BRDN_COST TXN_BRDN_COST,
1188 accum.TXN_REVENUE TXN_REVENUE,
1189 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.TXN_RAW_COST, 0) TXN_LABOR_RAW_COST,
1190 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.TXN_BRDN_COST, 0) TXN_LABOR_BRDN_COST,
1191 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.TXN_RAW_COST, 0) TXN_EQUIP_RAW_COST,
1192 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.TXN_BRDN_COST, 0) TXN_EQUIP_BRDN_COST,
1193 accum.QUANTITY QUANTITY,
1194 accum.PRJ_RAW_COST PRJ_RAW_COST,
1195 accum.PRJ_BRDN_COST PRJ_BRDN_COST,
1196 accum.PRJ_REVENUE PRJ_REVENUE,
1197 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.PRJ_RAW_COST, 0) PRJ_LABOR_RAW_COST,
1198 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.PRJ_BRDN_COST, 0) PRJ_LABOR_BRDN_COST,
1199 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.PRJ_RAW_COST, 0) PRJ_EQUIP_RAW_COST,
1200 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.PRJ_BRDN_COST, 0) PRJ_EQUIP_BRDN_COST,
1201 accum.POU_RAW_COST POU_RAW_COST,
1202 accum.POU_BRDN_COST POU_BRDN_COST,
1203 accum.POU_REVENUE POU_REVENUE,
1204 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.POU_RAW_COST, 0) POU_LABOR_RAW_COST,
1205 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.POU_BRDN_COST, 0) POU_LABOR_BRDN_COST,
1206 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.POU_RAW_COST, 0) POU_EQUIP_RAW_COST,
1207 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.POU_BRDN_COST, 0) POU_EQUIP_BRDN_COST,
1208 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.QUANTITY, 0) LABOR_HOURS,
1209 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.QUANTITY, 0) EQUIP_HOURS,
1210 null PERIOD_FLAG,
1211 null INCR_QUANTITY,
1212 null INCR_POU_RAW_COST,
1213 null INCR_POU_BRDN_COST,
1214 time.START_DATE,
1215 time.END_DATE
1216 FROM
1217 pa_res_list_map_tmp4 tmp4,
1218 pji_fp_txn_accum accum,
1219 pji_time_cal_period_v time,
1220 pji_org_extr_info info
1221 WHERE
1222 tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
1223 accum.PROJECT_ID = p_project_ids(i) AND
1224 accum.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
1225 /*Added 'G' in below decode for workplan progress */
1226 accum.RECVR_PERIOD_TYPE = decode(l_calendar_type(i), 'P', 'PA', 'G','GL') AND
1227 time.CALENDAR_ID = decode(l_calendar_type(i) , 'P', info.PA_CALENDAR_ID,
1228 'G',info.GL_CALENDAR_ID) AND
1229 info.ORG_ID = l_org_id(i) AND
1230 time.CAL_PERIOD_ID <= l_end_period_id(i) AND
1231 nvl(p_calling_context,'F') not in ('P', 'W')
1232 )
1233 GROUP BY
1234 RES_LIST_MEMBER_ID,
1235 PROJECT_ID,
1236 STRUCT_VERSION_ID,
1237 DECODE(l_proj_level_flag, 'Y', 0, PROJECT_ELEMENT_ID) ,
1238 CALENDAR_TYPE,
1239 PERIOD_NAME,
1240 PLAN_VERSION_ID,
1241 TXN_CURRENCY_CODE
1242
1243 UNION ALL
1244
1245 SELECT
1246 MIN(SOURCE_ID) SOURCE_ID,
1247 RES_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
1248 PROJECT_ID PROJECT_ID,
1249 STRUCT_VERSION_ID STRUCT_VERSION_ID,
1250 DECODE(l_proj_level_flag,'Y',0,PROJECT_ELEMENT_ID) PROJECT_ELEMENT_ID,
1251 CALENDAR_TYPE CALENDAR_TYPE,
1252 PERIOD_NAME PERIOD_NAME,
1253 PLAN_VERSION_ID PLAN_VERSION_ID,
1254 TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
1255 SUM(TXN_RAW_COST) TXN_RAW_COST,
1256 SUM(TXN_BRDN_COST) TXN_BRDN_COST,
1257 SUM(TXN_REVENUE) TXN_REVENUE,
1258 SUM(TXN_LABOR_RAW_COST) TXN_LABOR_RAW_COST,
1259 SUM(TXN_LABOR_BRDN_COST) TXN_LABOR_BRDN_COST,
1260 SUM(TXN_EQUIP_RAW_COST) TXN_EQUIP_RAW_COST,
1261 SUM(TXN_EQUIP_BRDN_COST) TXN_EQUIP_BRDN_COST,
1262 SUM(QUANTITY) QUANTITY,
1263 SUM(PRJ_RAW_COST) PRJ_RAW_COST,
1264 SUM(PRJ_BRDN_COST) PRJ_BRDN_COST,
1265 SUM(PRJ_REVENUE) PRJ_REVENUE,
1266 SUM(PRJ_LABOR_RAW_COST) PRJ_LABOR_RAW_COST,
1267 SUM(PRJ_LABOR_BRDN_COST) PRJ_LABOR_BRDN_COST,
1268 SUM(PRJ_EQUIP_RAW_COST) PRJ_EQUIP_RAW_COST,
1269 SUM(PRJ_EQUIP_BRDN_COST) PRJ_EQUIP_BRDN_COST,
1270 SUM(POU_RAW_COST) POU_RAW_COST,
1271 SUM(POU_BRDN_COST) POU_BRDN_COST,
1272 SUM(POU_REVENUE) POU_REVENUE,
1273 SUM(POU_LABOR_RAW_COST) POU_LABOR_RAW_COST,
1274 SUM(POU_LABOR_BRDN_COST) POU_LABOR_BRDN_COST,
1275 SUM(POU_EQUIP_RAW_COST) POU_EQUIP_RAW_COST,
1276 SUM(POU_EQUIP_BRDN_COST) POU_EQUIP_BRDN_COST,
1277 SUM(LABOR_HOURS) LABOR_HOURS,
1278 SUM(EQUIP_HOURS) EQUIPMENT_HOURS,
1279 MIN(PERIOD_FLAG) PERIOD_FLAG,
1280 SUM(INCR_QUANTITY) BASE_LABOR_HOURS,
1281 SUM(INCR_POU_RAW_COST) POU_LPB_RAW_COST,
1282 SUM(INCR_POU_BRDN_COST) POU_LPB_BRDN_COST,
1283 MIN(START_DATE) MIN_START_DATE,
1284 MAX(END_DATE) MAX_END_DATE
1285 FROM
1286 (
1287 SELECT --Retreives actuals data by resource list as ITD amounts for NON-TIME PHASED Calendar
1288 --from pji_fp_txn_Accum
1289 /*tmp4.TXN_SOURCE_ID, Commented for workplan progress */
1290 (null) SOURCE_ID,
1291 tmp4.RESOURCE_LIST_MEMBER_ID RES_LIST_MEMBER_ID,
1292 accum.PROJECT_ID PROJECT_ID,
1293 p_struct_ver_ids(i) STRUCT_VERSION_ID,
1294 accum.TASK_ID PROJECT_ELEMENT_ID,
1295 l_calendar_type(i) CALENDAR_TYPE,
1296 NULL PERIOD_NAME, /* Added for workplan progress */
1297 -1 PLAN_VERSION_ID,
1298 accum.TXN_CURRENCY_CODE TXN_CURRENCY_CODE,
1299 accum.TXN_RAW_COST TXN_RAW_COST,
1300 accum.TXN_BRDN_COST TXN_BRDN_COST,
1301 accum.TXN_REVENUE TXN_REVENUE,
1302 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.TXN_RAW_COST, 0) TXN_LABOR_RAW_COST,
1303 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.TXN_BRDN_COST, 0) TXN_LABOR_BRDN_COST,
1304 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.TXN_RAW_COST, 0) TXN_EQUIP_RAW_COST,
1305 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.TXN_BRDN_COST, 0) TXN_EQUIP_BRDN_COST,
1306 accum.QUANTITY QUANTITY,
1307 accum.PRJ_RAW_COST PRJ_RAW_COST,
1308 accum.PRJ_BRDN_COST PRJ_BRDN_COST,
1309 accum.PRJ_REVENUE PRJ_REVENUE,
1310 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.PRJ_RAW_COST, 0) PRJ_LABOR_RAW_COST,
1311 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.PRJ_BRDN_COST, 0) PRJ_LABOR_BRDN_COST,
1312 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.PRJ_RAW_COST, 0) PRJ_EQUIP_RAW_COST,
1313 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.PRJ_BRDN_COST, 0) PRJ_EQUIP_BRDN_COST,
1314 accum.POU_RAW_COST POU_RAW_COST,
1315 accum.POU_BRDN_COST POU_BRDN_COST,
1316 accum.POU_REVENUE POU_REVENUE,
1317 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.POU_RAW_COST, 0) POU_LABOR_RAW_COST,
1318 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.POU_BRDN_COST, 0) POU_LABOR_BRDN_COST,
1319 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.POU_RAW_COST, 0) POU_EQUIP_RAW_COST,
1320 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.POU_BRDN_COST, 0) POU_EQUIP_BRDN_COST,
1321 decode(tmp4.RESOURCE_CLASS_CODE, 'PEOPLE', accum.QUANTITY, 0) LABOR_HOURS,
1322 decode(tmp4.RESOURCE_CLASS_CODE, 'EQUIPMENT', accum.QUANTITY, 0) EQUIP_HOURS,
1323 null PERIOD_FLAG,
1324 null INCR_QUANTITY,
1325 null INCR_POU_RAW_COST,
1326 null INCR_POU_BRDN_COST,
1327 time.START_DATE,
1328 time.END_DATE
1329 FROM
1330 pa_res_list_map_tmp4 tmp4,
1331 pji_fp_txn_accum accum,
1332 pji_time_cal_period_v time
1333 WHERE
1334 tmp4.TXN_SOURCE_ID = accum.TXN_ACCUM_HEADER_ID AND
1335 accum.PROJECT_ID = p_project_ids(i) AND
1336 l_calendar_type(i) = 'A' AND
1337 accum.RECVR_PERIOD_ID = time.CAL_PERIOD_ID AND
1338 accum.RECVR_PERIOD_TYPE = 'GL' AND
1339 nvl(p_calling_context,'F') not in ('P', 'W')
1340 )
1341 GROUP BY
1342 RES_LIST_MEMBER_ID,
1343 PROJECT_ID,
1344 STRUCT_VERSION_ID,
1345 DECODE(l_proj_level_flag, 'Y', 0, PROJECT_ELEMENT_ID) ,
1346 CALENDAR_TYPE,
1347 PERIOD_NAME,
1348 PLAN_VERSION_ID,
1349 TXN_CURRENCY_CODE
1350 );
1351 END IF;/*p_calling_context*/
1352
1353 print_time ( ' get_summarized_data 0004.4 ' ) ;
1354
1355 delete from pa_res_list_map_tmp4;
1356
1357 /* Added for populating periodic actuals till as_of_data parameter */
1358 /* Bug 5349102 :shifted the code to delete_fin8
1359 IF p_calling_context in ('P', 'W') THEN
1360
1361 IF l_calendar_type(i) = 'A' THEN
1362
1363 delete from pji_fm_aggr_fin8 fin where
1364 fin.PROJECT_ID = p_project_ids(i);
1365 ELSE
1366
1367 delete from pji_fm_aggr_fin8 fin where
1368 fin.PROJECT_ID = p_project_ids(i)
1369 AND fin.RECVR_PERIOD_ID <= l_end_period_id(i);
1370 END IF;
1371
1372 END IF; */
1373 /* Added for populating periodic actuals till as_of_data parameter */
1374
1375 END IF;/* End of l_plan_ver_flag='Y' and l_get_summarized_Data='Y' */
1376 END LOOP;
1377
1378 delete
1379 from PJI_FM_XBS_ACCUM_TMP1
1380 where nvl(TXN_RAW_COST, 0) = 0 and
1381 nvl(TXN_BRDN_COST, 0) = 0 and
1382 nvl(TXN_LABOR_RAW_COST, 0) = 0 and
1383 nvl(TXN_LABOR_BRDN_COST, 0) = 0 and
1384 nvl(TXN_EQUIP_RAW_COST, 0) = 0 and
1385 nvl(TXN_EQUIP_BRDN_COST, 0) = 0 and
1386 nvl(TXN_BASE_RAW_COST, 0) = 0 and
1387 nvl(TXN_BASE_BRDN_COST, 0) = 0 and
1388 nvl(TXN_BASE_LABOR_RAW_COST, 0) = 0 and
1389 nvl(TXN_BASE_LABOR_BRDN_COST, 0) = 0 and
1390 nvl(TXN_BASE_EQUIP_RAW_COST, 0) = 0 and
1391 nvl(TXN_BASE_EQUIP_BRDN_COST, 0) = 0 and
1392 nvl(PRJ_RAW_COST, 0) = 0 and
1393 nvl(PRJ_BRDN_COST, 0) = 0 and
1394 nvl(PRJ_LABOR_RAW_COST, 0) = 0 and
1395 nvl(PRJ_LABOR_BRDN_COST, 0) = 0 and
1396 nvl(PRJ_EQUIP_RAW_COST, 0) = 0 and
1397 nvl(PRJ_EQUIP_BRDN_COST, 0) = 0 and
1398 nvl(PRJ_BASE_RAW_COST, 0) = 0 and
1399 nvl(PRJ_BASE_BRDN_COST, 0) = 0 and
1400 nvl(PRJ_BASE_LABOR_RAW_COST, 0) = 0 and
1401 nvl(PRJ_BASE_LABOR_BRDN_COST, 0) = 0 and
1402 nvl(PRJ_BASE_EQUIP_RAW_COST, 0) = 0 and
1403 nvl(PRJ_BASE_EQUIP_BRDN_COST, 0) = 0 and
1404 nvl(POU_RAW_COST, 0) = 0 and
1405 nvl(POU_BRDN_COST, 0) = 0 and
1406 nvl(POU_LABOR_RAW_COST, 0) = 0 and
1407 nvl(POU_LABOR_BRDN_COST, 0) = 0 and
1408 nvl(POU_EQUIP_RAW_COST, 0) = 0 and
1409 nvl(POU_EQUIP_BRDN_COST, 0) = 0 and
1410 nvl(POU_BASE_RAW_COST, 0) = 0 and
1411 nvl(POU_BASE_BRDN_COST, 0) = 0 and
1412 nvl(POU_BASE_LABOR_RAW_COST, 0) = 0 and
1413 nvl(POU_BASE_LABOR_BRDN_COST, 0) = 0 and
1414 nvl(POU_BASE_EQUIP_RAW_COST, 0) = 0 and
1415 nvl(POU_BASE_EQUIP_BRDN_COST, 0) = 0 and
1416 nvl(LABOR_HOURS, 0) = 0 and
1417 nvl(EQUIPMENT_HOURS, 0) = 0 and
1418 nvl(BASE_LABOR_HOURS, 0) = 0 and
1419 nvl(BASE_EQUIP_HOURS, 0) = 0 and
1420 nvl(SOURCE_ID, 0) = 0 and
1421 nvl(ACT_LABOR_HRS, 0) = 0 and
1422 nvl(ACT_EQUIP_HRS, 0) = 0 and
1423 nvl(ACT_TXN_LABOR_BRDN_COST, 0) = 0 and
1424 nvl(ACT_TXN_EQUIP_BRDN_COST, 0) = 0 and
1425 nvl(ACT_TXN_BRDN_COST, 0) = 0 and
1426 nvl(ACT_PRJ_LABOR_BRDN_COST, 0) = 0 and
1427 nvl(ACT_PRJ_EQUIP_BRDN_COST, 0) = 0 and
1428 nvl(ACT_PRJ_BRDN_COST, 0) = 0 and
1429 nvl(ACT_PFC_LABOR_BRDN_COST, 0) = 0 and
1430 nvl(ACT_PFC_EQUIP_BRDN_COST, 0) = 0 and
1431 nvl(ACT_PFC_BRDN_COST, 0) = 0 and
1432 nvl(ETC_LABOR_HRS, 0) = 0 and
1433 nvl(ETC_EQUIP_HRS, 0) = 0 and
1434 nvl(ETC_TXNLABOR_BRDN_COST, 0) = 0 and
1435 nvl(ETC_TXN_EQUIP_BRDN_COST, 0) = 0 and
1436 nvl(ETC_TXN_BRDN_COST, 0) = 0 and
1437 nvl(ETC_PRJ_LABOR_BRDN_COST, 0) = 0 and
1438 nvl(ETC_PRJ_EQUIP_BRDN_COST, 0) = 0 and
1439 nvl(ETC_PRJ_BRDN_COST, 0) = 0 and
1440 nvl(ETC_POU_LABOR_BRDN_COST, 0) = 0 and
1441 nvl(ETC_POU_EQUIP_BRDN_COST, 0) = 0 and
1442 nvl(ETC_POU_BRDN_COST, 0) = 0 and
1443 nvl(ACT_TXN_RAW_COST, 0) = 0 and
1444 nvl(ACT_PRJ_RAW_COST, 0) = 0 and
1445 nvl(ACT_POU_RAW_COST, 0) = 0 and
1446 nvl(ETC_TXN_RAW_COST, 0) = 0 and
1447 nvl(ETC_PRJ_RAW_COST, 0) = 0 and
1448 nvl(ETC_POU_RAW_COST, 0) = 0 and
1449 nvl(ACT_TXN_LABOR_RAW_COST, 0) = 0 and
1450 nvl(ACT_TXN_EQUIP_RAW_COST, 0) = 0 and
1451 nvl(ACT_PRJ_LABOR_RAW_COST, 0) = 0 and
1452 nvl(ACT_PRJ_EQUIP_RAW_COST, 0) = 0 and
1453 nvl(ACT_POU_LABOR_RAW_COST, 0) = 0 and
1454 nvl(ACT_POU_EQUIP_RAW_COST, 0) = 0 and
1455 nvl(ETC_TXN_LABOR_RAW_COST, 0) = 0 and
1456 nvl(ETC_TXN_EQUIP_RAW_COST, 0) = 0 and
1457 nvl(ETC_PRJ_LABOR_RAW_COST, 0) = 0 and
1458 nvl(ETC_PRJ_EQUIP_RAW_COST, 0) = 0 and
1459 nvl(ETC_POU_LABOR_RAW_COST, 0) = 0 and
1460 nvl(ETC_POU_EQUIP_RAW_COST, 0) = 0 and
1461 nvl(ACT_POU_LABOR_BRDN_COST, 0) = 0 and
1462 nvl(ACT_POU_EQUIP_BRDN_COST, 0) = 0 and
1463 nvl(ACT_POU_BRDN_COST, 0) = 0 and
1464 nvl(ETC_TXN_LABOR_BRDN_COST, 0) = 0 and
1465 nvl(TXN_LPB_RAW_COST, 0) = 0 and
1466 nvl(TXN_LPB_BRDN_COST, 0) = 0 and
1467 nvl(TXN_LPB_LABOR_RAW_COST, 0) = 0 and
1468 nvl(TXN_LPB_LABOR_BRDN_COST, 0) = 0 and
1469 nvl(TXN_LPB_EQUIP_RAW_COST, 0) = 0 and
1470 nvl(TXN_LPB_EQUIP_BRDN_COST, 0) = 0 and
1471 nvl(PRJ_LPB_RAW_COST, 0) = 0 and
1472 nvl(PRJ_LPB_BRDN_COST, 0) = 0 and
1473 nvl(PRJ_LPB_LABOR_RAW_COST, 0) = 0 and
1474 nvl(PRJ_LPB_LABOR_BRDN_COST, 0) = 0 and
1475 nvl(PRJ_LPB_EQUIP_RAW_COST, 0) = 0 and
1476 nvl(PRJ_LPB_EQUIP_BRDN_COST, 0) = 0 and
1477 nvl(POU_LPB_RAW_COST, 0) = 0 and
1478 nvl(POU_LPB_BRDN_COST, 0) = 0 and
1479 nvl(POU_LPB_LABOR_RAW_COST, 0) = 0 and
1480 nvl(POU_LPB_LABOR_BRDN_COST, 0) = 0 and
1481 nvl(POU_LPB_EQUIP_RAW_COST, 0) = 0 and
1482 nvl(POU_LPB_EQUIP_BRDN_COST, 0) = 0 and
1483 nvl(LPB_LABOR_HOURS, 0) = 0 and
1484 nvl(LPB_EQUIP_HOURS, 0) = 0 and
1485 RES_LIST_MEMBER_ID > 0 and
1486 p_calling_context = 'W';
1487
1488 IF NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N') = 'Y' THEN
1489 debug_accum ; /* bug#3993830 */
1490 END IF;
1491
1492 print_time ( ' get_summarized_data 0010 ' ) ;
1493
1494 EXCEPTION
1495 WHEN l_map_resource_list THEN /* Bug No. 4461060 */
1496 print_time('Error in pkg ' || g_package_name || 'Procedure GET_SUMMARIZED_DATA' || ' is: ' || 'Error is in pa_resource_mapping.map_resource_list' );
1497 x_return_status :='E' ;
1498
1499 WHEN OTHERS THEN
1500 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
1501 ( p_package_name => g_package_name
1502 , p_procedure_name => 'GET_SUMMARIZED_DATA'
1503 , x_return_status => x_return_status ) ;
1504
1505 RAISE;
1506 END;
1507
1508
1509
1510 /*********************************************************
1511 This procedure populates data in PJI_FM_XBS_ACCUM_TMP1
1512 for workplans.
1513 API supports both work plans and progress actuals
1514 The parameters that the API accepts can have one of the
1515 following combinations
1516 - p_struct_ver_id, p_base_struct_ver_id
1517 - p_plan_version_id
1518 **********************************************************/
1519
1520 PROCEDURE populate_updatewbs_data (
1521 p_project_id IN NUMBER,
1522 p_struct_ver_id IN NUMBER := NULL,
1523 p_base_struct_ver_id IN NUMBER := NULL,
1524 p_plan_version_id IN NUMBER := NULL,
1525 p_as_of_date IN DATE := NULL,
1526 p_delete_flag IN VARCHAR2 := 'Y',
1527 p_project_element_id IN NUMBER := NULL,
1528 p_level IN NUMBER := 1,
1529 p_structure_flag IN VARCHAR2 := 'N',
1530 x_return_status OUT NOCOPY VARCHAR2,
1531 x_msg_code OUT NOCOPY VARCHAR2 ) IS
1532
1533 l_plan_ver_id NUMBER;
1534 l_base_plan_ver_id NUMBER := 0;
1535 l_wking_struct_ver_id NUMBER;
1536 l_prd_start_date DATE;
1537 l_calendar_id NUMBER;
1538 l_org_id NUMBER;
1539 l_calendar_type VARCHAR2(1);
1540 -- changes made for populate_workplan_data fix for bug : 4158221
1541 l_cal_type VARCHAR2(1) := 'A' ;
1542 l_prd_type_id NUMBER := 2048 ;
1543 l_end_period_id NUMBER := -1 ; -- Added Defalut value of -1 if calander_type = 'A'
1544
1545 l_lpb_plan_ver_id Number; /*Added for workplan progress */
1546 l_lpb_struct_ver_id Number; /* Added for workplan progress*/
1547 l_return_status VARCHAR2(1);
1548 p_workplan_flag VARCHAR2(1) :='Y';
1549 p_program_rollup_flag VARCHAR2(1) :='N';
1550
1551 BEGIN
1552
1553 PJI_PJP_FP_CURR_WRAP.INIT_ERR_STACK
1554 ( p_package_name => g_package_name
1555 , x_return_status => x_return_status );
1556
1557 print_time (' p_project_id ' || p_project_id || ' p_struct_ver_id ' || p_struct_ver_id );
1558 print_time (' p_base_struct_ver_id ' || p_base_struct_ver_id || ' p_plan_version_id ' || p_plan_version_id );
1559 print_time ( ' p_as_of_date ' || p_as_of_date );
1560 print_time (' p_delete_flag ' || p_delete_flag || ' p_workplan_flag ' || p_workplan_flag );
1561
1562 -- fnd_stats.set_table_stats('PJI','PJI_PLAN_EXTR_TMP',10,10,10);
1563 pji_pjp_fp_curr_wrap.set_table_stats('PJI','PJI_PLAN_EXTR_TMP',10,10,10);
1564
1565 l_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1566 /* Added for workplan progress */
1567 -- Get the Latest Published Version for the Project
1568
1569 BEGIN
1570 SELECT element_version_id
1571 INTO l_lpb_struct_ver_id
1572 FROM pa_proj_elem_ver_structure ppevs,
1573 pa_proj_structure_types ppst
1574 WHERE ppevs.project_id = p_project_id
1575 AND latest_eff_published_flag = 'Y'
1576 AND ppst.proj_element_id = ppevs.proj_element_id
1577 AND ppst.structure_type_id = 1;
1578 EXCEPTION
1579 WHEN NO_DATA_FOUND THEN
1580 l_lpb_struct_ver_id := null;
1581 END;
1582
1583
1584 IF l_lpb_struct_ver_id <> -1 then -- To find out the latest published plan version Id
1585
1586 BEGIN
1587 SELECT budget_version_id
1588 INTO l_lpb_plan_ver_id
1589 FROM PA_BUDGET_VERSIONS
1590 WHERE project_structure_Version_id = l_lpb_struct_ver_id
1591 AND wp_version_flag ='Y'
1592 AND project_id = p_project_id;
1593
1594 EXCEPTION
1595 WHEN NO_DATA_FOUND THEN
1596 l_lpb_plan_ver_id := null;
1597 END;
1598 ELSE
1599 l_lpb_struct_ver_id :=null;
1600 l_lpb_plan_ver_id := null;
1601 END IF;
1602
1603
1604 /* End of workplan progress change */
1605
1606
1607
1608 IF (p_plan_version_id IS NOT NULL) THEN
1609
1610 l_plan_ver_id := p_plan_version_id;
1611
1612 BEGIN
1613
1614 print_time ( ' populate_updatewbs_data 0003.2 ' ) ;
1615
1616 SELECT WBS_VERSION_ID
1617 INTO l_wking_struct_ver_id
1618 FROM pji_pjp_wbs_header
1619 WHERE plan_version_id = p_plan_version_id;
1620
1621 EXCEPTION
1622 WHEN NO_DATA_FOUND THEN
1623 print_time( 'PJI_FM_XBS_ACCUM_UTILS.POPULATE_WORKPLAN_DATA. Structure info does not exist for this plan version in WBS header table ' || NVL(p_plan_version_id, -99));
1624 END;
1625
1626 ELSE
1627
1628 print_time ( ' populate_updatewbs_data 0003.3 ' ) ;
1629
1630 l_wking_struct_ver_id := p_struct_ver_id ;
1631
1632 --Get the plan version for work plan
1633
1634 BEGIN
1635 print_time ( ' populate_updatewbs_data 0003.4 ' ) ;
1636
1637 SELECT head.PLAN_VERSION_ID
1638 INTO l_plan_ver_id
1639 FROM pji_pjp_wbs_header head,
1640 pa_budget_versions bv
1641 WHERE 1=1
1642 AND head.plan_version_id = bv.budget_version_id
1643 AND NVL(bv.wp_version_flag, 'N') = head.wp_flag
1644 AND NVL(bv.wp_version_flag, 'N') = p_workplan_flag
1645 AND head.WBS_VERSION_ID = p_struct_ver_id
1646 AND head.PROJECT_ID = p_project_id
1647 AND DECODE(p_workplan_flag
1648 , 'N'
1649 , DECODE(bv.budget_status_code||bv.current_working_flag
1650 , 'WY'
1651 ,'X'
1652 , 'Y')
1653 , 'X') = 'X'
1654 AND head.PLAN_VERSION_ID > 0;
1655
1656 EXCEPTION
1657 WHEN no_data_found THEN
1658 print_time( 'PJI_FM_XBS_ACCUM_UTILS.POPULATE_WORKPLAN_DATA. Plan info does not exists for this project id ' || p_project_id || ' and this structure version id ' || p_struct_ver_id || ' in WBS header table.');
1659
1660 END;
1661
1662 print_time ( ' populate_updatewbs_data 0003.5 ' ) ;
1663
1664
1665 --Get the baselined plan version
1666
1667 BEGIN
1668
1669 print_time ( ' populate_updatewbs_data 0003.6 ' ) ;
1670
1671 SELECT head.PLAN_VERSION_ID
1672 INTO l_base_plan_ver_id
1673 FROM pji_pjp_wbs_header head,
1674 pa_budget_versions bv
1675 WHERE 1=1
1676 AND head.plan_version_id = bv.budget_version_id
1677 AND NVL(bv.wp_version_flag, 'N') = head.wp_flag
1678 AND NVL(bv.wp_version_flag, 'N') = p_workplan_flag
1679 AND head.WBS_VERSION_ID = p_base_struct_ver_id
1680 AND head.PROJECT_ID = p_project_id
1681 AND DECODE(p_workplan_flag
1682 , 'N'
1683 , DECODE(bv.budget_status_code||bv.current_flag
1684 , 'BY','X'
1685 , 'Y')
1686 , 'X')
1687 = 'X'
1688 AND head.PLAN_VERSION_ID > 0;
1689
1690 EXCEPTION
1691 WHEN no_data_found THEN
1692 print_time( 'PJI_FM_XBS_ACCUM_UTILS.POPULATE_WORKPLAN_DATA no current baselined plan version');
1693 END;
1694
1695 END IF;
1696
1697
1698 print_time ( ' populate_updatewbs_data 0003.7 p_project_id' || p_project_id ) ;
1699 print_time ( ' l_base_plan_ver_id ' || l_base_plan_ver_id || ' plan_ver_id ' || l_plan_ver_id ) ;
1700
1701 -- Changes made for populate_work_plan_data chagnes : Fix for bug : 4158221
1702 If p_as_of_date IS NOT NULL then -- Fix for bug : 4196808
1703 BEGIN
1704 SELECT calendar_type, DECODE(calendar_type, 'A', 2048, 32) PERIOD_TYPE_ID
1705 INTO l_cal_type, l_prd_type_id
1706 FROM
1707 (
1708 SELECT
1709 DECODE(NVL(NVL(cost_time_phased_code, revenue_time_phased_code), all_time_phased_code), 'G', 'G', 'P', 'P', 'A') calendar_type
1710 FROM pa_proj_fp_options
1711 WHERE fin_plan_option_level_code = 'PLAN_VERSION'
1712 AND fin_plan_version_id = l_plan_ver_id
1713 ) ;
1714 EXCEPTION
1715 when no_data_found then
1716 print_time( 'PJI_FM_XBS_ACCUM_UTILS.POPULATE_WORKPLAN_DATA : No calendar_type');
1717 when others then
1718 print_time( 'PJI_FM_XBS_ACCUM_UTILS.POPULATE_WORKPLAN_DATA : No Calander_Type');
1719 END ;
1720
1721 BEGIN
1722 SELECT ORG_ID
1723 INTO l_org_id
1724 FROM pa_projects_all
1725 WHERE project_id = p_project_id ;
1726
1727 IF L_CAL_TYPE IN ('P', 'G') THEN -- retrieve CAL_PERIOD_ID only if calander_type is 'P' or 'G'
1728 SELECT cal.CAL_PERIOD_ID
1729 INTO l_end_period_id
1730 FROM pji_time_cal_period_v cal, pji_org_extr_info info
1731 WHERE TRUNC(p_as_of_date)
1732 BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE)
1733 AND info.ORG_ID = l_org_id
1734 AND DECODE(l_cal_type, 'P', info.PA_CALENDAR_ID, info.GL_CALENDAR_ID) = cal.CALENDAR_ID;
1735 END IF ;
1736 EXCEPTION
1737 when no_data_found then
1738 print_time( 'PJI_FM_XBS_ACCUM_UTILS.POPULATE_WORKPLAN_DATA : No Calander Period Id');
1739 when others then
1740 print_time( 'PJI_FM_XBS_ACCUM_UTILS.POPULATE_WORKPLAN_DATA : No Calander Period Id');
1741 END ;
1742 END IF ; -- end if for If p_as_of_date IS NOT NULL then
1743
1744
1745
1746 DELETE FROM PJI_PLAN_EXTR_TMP;
1747 print_time ( ' # rows deleted from tmp = ' || SQL%ROWCOUNT ) ;
1748
1749
1750 --Ensures that data is cleaned up for the Project / Program and the linked sub projects
1751 IF (p_delete_flag = 'Y') THEN
1752
1753 print_time ( ' populate_updatewbs_data 0002 ' ) ;
1754 DELETE FROM pji_fm_xbs_accum_tmp1;
1755 print_time ( ' # rows deleted from tmp1 = ' || SQL%ROWCOUNT ) ;
1756
1757 END IF;
1758
1759 print_time ( ' populate_updatewbs_data 0003 ' ) ;
1760
1761 -- The Temp table is populated with the PROJECT_ID and the PROJECT_ELEMENT_ID
1762
1763 IF p_structure_flag ='N' THEN
1764
1765 insert into PJI_PLAN_EXTR_TMP(project_id,plan_ver_id)
1766 select sup_project_id,sub_emt_id
1767 from pji_xbs_Denorm
1768 where sup_project_id=p_project_id
1769 and struct_version_id = p_struct_ver_id
1770 and sup_emt_id=p_project_element_id
1771 and sup_level <> sub_level
1772 and abs(sup_level - sub_level) <=p_level ;
1773 ELSE
1774 insert into PJI_PLAN_EXTR_TMP(project_id,plan_ver_id)
1775 select sub.sup_project_id,sub.sub_emt_id
1776 from pji_xbs_Denorm sup,pji_xbs_Denorm sub
1777 where sup.sup_project_id=p_project_id
1778 and sup.sup_project_id =sub.sup_project_id
1779 and sup.sup_id = p_struct_ver_id
1780 and sup.sub_id = sub.sup_id
1781 and sub.struct_type<> 'XBS'
1782 and sup.struct_type<> 'WBS'
1783 and abs(sub.sup_level - sub.sub_level) <=p_level -1;
1784 END IF;
1785
1786 --
1787 -- Get task level data from reporting lines
1788 -- Data is rolled up by WBS hierarchy
1789 -- Data inserted is the Totals
1790 --
1791
1792 INSERT INTO pji_fm_xbs_accum_tmp1 (
1793 PROJECT_ID, STRUCT_VERSION_ID, PROJECT_ELEMENT_ID, CALENDAR_TYPE,
1794 PERIOD_NAME, PLAN_VERSION_ID, QUANTITY, TXN_RAW_COST,
1795 TXN_BRDN_COST, TXN_REVENUE, TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST,
1796 TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST, TXN_BASE_RAW_COST, TXN_BASE_BRDN_COST,
1797 TXN_BASE_LABOR_RAW_COST, TXN_BASE_LABOR_BRDN_COST,TXN_BASE_EQUIP_RAW_COST, TXN_BASE_EQUIP_BRDN_COST,
1798 TXN_LPB_RAW_COST, TXN_LPB_BRDN_COST, TXN_LPB_LABOR_RAW_COST, TXN_LPB_LABOR_BRDN_COST,
1799 TXN_LPB_EQUIP_RAW_COST, TXN_LPB_EQUIP_BRDN_COST, PRJ_RAW_COST, PRJ_BRDN_COST,
1800 PRJ_REVENUE, PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST,
1801 PRJ_EQUIP_BRDN_COST, PRJ_BASE_RAW_COST, PRJ_BASE_BRDN_COST, PRJ_BASE_LABOR_RAW_COST,
1802 PRJ_BASE_LABOR_BRDN_COST,PRJ_BASE_EQUIP_RAW_COST, PRJ_BASE_EQUIP_BRDN_COST,PRJ_LPB_RAW_COST,
1803 PRJ_LPB_BRDN_COST, PRJ_LPB_LABOR_RAW_COST, PRJ_LPB_LABOR_BRDN_COST, PRJ_LPB_EQUIP_RAW_COST,
1804 PRJ_LPB_EQUIP_BRDN_COST, POU_RAW_COST, POU_BRDN_COST, POU_REVENUE,
1805 POU_LABOR_RAW_COST, POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST,
1806 POU_BASE_RAW_COST, POU_BASE_BRDN_COST, POU_BASE_LABOR_RAW_COST, POU_BASE_LABOR_BRDN_COST,
1807 POU_BASE_EQUIP_RAW_COST, POU_BASE_EQUIP_BRDN_COST,POU_LPB_RAW_COST, POU_LPB_BRDN_COST,
1808 POU_LPB_LABOR_RAW_COST, POU_LPB_LABOR_BRDN_COST, POU_LPB_EQUIP_RAW_COST, POU_LPB_EQUIP_BRDN_COST,
1809 LABOR_HOURS, EQUIPMENT_HOURS, BASE_LABOR_HOURS, BASE_EQUIP_HOURS,
1810 LPB_LABOR_HOURS, LPB_EQUIP_HOURS, ACT_LABOR_HRS, ACT_EQUIP_HRS,
1811 ACT_TXN_LABOR_BRDN_COST, ACT_TXN_EQUIP_BRDN_COST, ACT_TXN_RAW_COST, ACT_TXN_BRDN_COST,
1812 ACT_PRJ_LABOR_BRDN_COST, ACT_PRJ_EQUIP_BRDN_COST, ACT_PRJ_RAW_COST, ACT_PRJ_BRDN_COST,
1813 ACT_POU_LABOR_BRDN_COST, ACT_POU_EQUIP_BRDN_COST, ACT_POU_RAW_COST, ACT_POU_BRDN_COST,
1814 ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_TXN_LABOR_BRDN_COST, ETC_TXN_EQUIP_BRDN_COST,
1815 ETC_TXN_RAW_COST, ETC_TXN_BRDN_COST, ETC_PRJ_LABOR_BRDN_COST, ETC_PRJ_EQUIP_BRDN_COST,
1816 ETC_PRJ_RAW_COST, ETC_PRJ_BRDN_COST, ETC_POU_LABOR_BRDN_COST, ETC_POU_EQUIP_BRDN_COST,
1817 ETC_POU_RAW_COST, ETC_POU_BRDN_COST, ACT_TXN_LABOR_RAW_COST , ACT_PRJ_LABOR_RAW_COST,
1818 ACT_POU_LABOR_RAW_COST, ACT_TXN_EQUIP_RAW_COST, ACT_PRJ_EQUIP_RAW_COST, ACT_POU_EQUIP_RAW_COST,
1819 ETC_TXN_LABOR_RAW_COST, ETC_PRJ_LABOR_RAW_COST, ETC_POU_LABOR_RAW_COST, ETC_TXN_EQUIP_RAW_COST,
1820 ETC_PRJ_EQUIP_RAW_COST, ETC_POU_EQUIP_RAW_COST, P_RAW_COST,P_BRDN_COST, P_REVENUE,
1821 P_LBR_RAW_COST, P_LBR_BRDN_COST, P_EQP_RAW_COST, P_EQP_BRDN_COST,
1822 P_BASE_RAW_COST, P_BASE_BRDN_COST, P_BASE_LBR_RAW_COST, P_BASE_LBR_BRDN_COST,
1823 P_BASE_EQP_RAW_COST, P_BASE_EQP_BRDN_COST, P_LPB_RAW_COST, P_LPB_BRDN_COST,
1824 P_LPB_LBR_RAW_COST, P_LPB_LBR_BRDN_COST, P_LPB_EQP_RAW_COST, P_LPB_EQP_BRDN_COST,
1825 P_LBR_HOURS, P_EQP_HOURS, P_BASE_LBR_HOURS, P_BASE_EQP_HOURS,
1826 P_LPB_LBR_HOURS, P_LPB_EQP_HOURS, P_ACT_LBR_HOURS, P_ACT_EQP_HOURS,
1827 P_ACT_LBR_BRDN_COST, P_ACT_EQP_BRDN_COST, P_ACT_RAW_COST, P_ACT_BRDN_COST,
1828 P_ACT_LBR_RAW_COST, P_ACT_EQP_RAW_COST, P_ETC_EQP_HOURS, P_ETC_LBR_HOURS,
1829 P_ETC_RAW_COST, P_ETC_BRDN_COST, P_ETC_LBR_BRDN_COST, P_ETC_EQP_BRDN_COST,
1830 P_ETC_LBR_RAW_COST,P_ETC_EQP_RAW_COST
1831 )
1832 SELECT /*+ LEADING(head) USE_NL(fact.fact) */
1833 fact.PROJECT_ID,
1834 l_wking_struct_ver_id STRUCT_VERSION_ID,
1835 fact.PROJECT_ELEMENT_ID PROJECT_ELEMENT_ID,
1836 'A',
1837 null PERIOD_NAME,
1838 l_plan_ver_id PLAN_VERSION_ID,
1839 0 QUANTITY,
1840 sum(case when fact.plan_version_id = l_plan_ver_id then fact.raw_cost*TXN_MASK else 0 end) TXN_RAW_COST,
1841 sum(case when fact.plan_version_id = l_plan_ver_id then fact.brdn_cost*TXN_MASK else 0 end) TXN_BRDN_COST,
1842 sum(case when fact.plan_version_id = l_plan_ver_id then fact.revenue*TXN_MASK else 0 end) TXN_REVENUE,
1843 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_raw_cost*TXN_MASK else 0 end) TXN_LABOR_RAW_COST,
1844 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_brdn_cost*TXN_MASK else 0 end) TXN_LABOR_BRDN_COST,
1845 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_raw_cost*TXN_MASK else 0 end) TXN_EQUIP_RAW_COST,
1846 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_brdn_cost*TXN_MASK else 0 end) TXN_EQUIP_BRDN_COST,
1847
1848 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.raw_cost*TXN_MASK else 0 end) TXN_BASE_RAW_COST,
1849 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.brdn_cost*TXN_MASK else 0 end) TXN_BASE_BRDN_COST,
1850 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_raw_cost*TXN_MASK else 0 end) TXN_BASE_LABOR_RAW_COST,
1851 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_brdn_cost*TXN_MASK else 0 end) TXN_BASE_LABOR_BRDN_COST,
1852 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_raw_cost*TXN_MASK else 0 end) TXN_BASE_EQUIP_RAW_COST,
1853 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_brdn_cost*TXN_MASK else 0 end) TXN_BASE_EQUIP_BRDN_COST,
1854
1855 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.raw_cost*TXN_MASK else 0 end) TXN_LPB_RAW_COST,
1856 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.brdn_cost*TXN_MASK else 0 end) TXN_LPB_BRDN_COST,
1857 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_raw_cost*TXN_MASK else 0 end) TXN_LPB_LABOR_RAW_COST,
1858 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_brdn_cost*TXN_MASK else 0 end) TXN_LPB_LABOR_BRDN_COST,
1859 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_raw_cost*TXN_MASK else 0 end) TXN_LPB_EQUIP_RAW_COST,
1860 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_brdn_cost*TXN_MASK else 0 end) TXN_LPB_EQUIP_BRDN_COST,
1861
1862 sum(case when fact.plan_version_id = l_plan_ver_id then fact.raw_cost*PRJ_MASK else 0 end) PRJ_RAW_COST,
1863 sum(case when fact.plan_version_id = l_plan_ver_id then fact.brdn_cost*PRJ_MASK else 0 end) PRJ_BRDN_COST,
1864 sum(case when fact.plan_version_id = l_plan_ver_id then fact.revenue*PRJ_MASK else 0 end) PRJ_REVENUE,
1865 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_raw_cost*PRJ_MASK else 0 end) PRJ_LABOR_RAW_COST,
1866 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK else 0 end) PRJ_LABOR_BRDN_COST,
1867 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK else 0 end) PRJ_EQUIP_RAW_COST,
1868 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK else 0 end) PRJ_EQUIP_BRDN_COST,
1869
1870 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.raw_cost*PRJ_MASK else 0 end) PRJ_BASE_RAW_COST,
1871 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.brdn_cost*PRJ_MASK else 0 end) PRJ_BASE_BRDN_COST,
1872 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_raw_cost*PRJ_MASK else 0 end) PRJ_BASE_LABOR_RAW_COST,
1873 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK else 0 end) PRJ_BASE_LABOR_BRDN_COST,
1874 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK else 0 end) PRJ_BASE_EQUIP_RAW_COST,
1875 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK else 0 end) PRJ_BASE_EQUIP_BRDN_COST,
1876
1877 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.raw_cost*PRJ_MASK else 0 end) PRJ_LPB_RAW_COST,
1878 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.brdn_cost*PRJ_MASK else 0 end) PRJ_LPB_BRDN_COST,
1879 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_raw_cost*PRJ_MASK else 0 end) PRJ_LPB_LABOR_RAW_COST,
1880 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK else 0 end) PRJ_LPB_LABOR_BRDN_COST,
1881 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK else 0 end) PRJ_LBP_EQUIP_RAW_COST,
1882 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK else 0 end) PRJ_LBP_EQUIP_BRDN_COST,
1883
1884 sum(case when fact.plan_version_id = l_plan_ver_id then fact.raw_cost*POU_MASK else 0 end) POU_RAW_COST,
1885 sum(case when fact.plan_version_id = l_plan_ver_id then fact.brdn_cost*POU_MASK else 0 end) POU_BRDN_COST,
1886 sum(case when fact.plan_version_id = l_plan_ver_id then fact.revenue*POU_MASK else 0 end) POU_REVENUE,
1887 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_raw_cost*POU_MASK else 0 end) POU_LABOR_RAW_COST,
1888 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_brdn_cost*POU_MASK else 0 end) POU_LABOR_BRDN_COST,
1889 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_raw_cost*POU_MASK else 0 end) POU_EQUIP_RAW_COST,
1890 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_brdn_cost*POU_MASK else 0 end) POU_EQUIP_BRDN_COST,
1891
1892 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.raw_cost*POU_MASK else 0 end) POU_BASE_RAW_COST,
1893 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.brdn_cost*POU_MASK else 0 end) POU_BASE_BRDN_COST,
1894 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_raw_cost*POU_MASK else 0 end) POU_BASE_LABOR_RAW_COST,
1895 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_brdn_cost*POU_MASK else 0 end) POU_BASE_LABOR_BRDN_COST,
1896 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_raw_cost*POU_MASK else 0 end) POU_BASE_EQUIP_RAW_COST,
1897 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_brdn_cost*POU_MASK else 0 end) POU_BASE_EQUIP_BRDN_COST,
1898
1899 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.raw_cost*POU_MASK else 0 end) POU_LPB_RAW_COST,
1900 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.brdn_cost*POU_MASK else 0 end) POU_LPB_BRDN_COST,
1901 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_raw_cost*POU_MASK else 0 end) POU_LPB_LABOR_RAW_COST,
1902 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_brdn_cost*POU_MASK else 0 end) POU_LPB_LABOR_BRDN_COST,
1903 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_raw_cost*POU_MASK else 0 end) POU_LPB_EQUIP_RAW_COST,
1904 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_brdn_cost*POU_MASK else 0 end) POU_LPB_EQUIP_BRDN_COST,
1905
1906 sum(case when fact.plan_version_id = l_plan_ver_id then fact.LABOR_HRS else 0 end) LABOR_HOURS,
1907 sum(case when fact.plan_version_id = l_plan_ver_id then fact.EQUIPMENT_HOURS else 0 end) EQUIPMENT_HOURS,
1908 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.LABOR_HRS else 0 end) BASE_LABOR_HOURS,
1909 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.EQUIPMENT_HOURS else 0 end) BASE_EQUIP_HOURS,
1910 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.LABOR_HRS else 0 end) LPB_LABOR_HOURS,
1911 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.EQUIPMENT_HOURS else 0 end) LPB_EQUIP_HOURS,
1912
1913 sum( case when fact.time_id <= l_end_period_id
1914 then
1915 decode(fact.plan_version_id, l_plan_ver_id, fact.ACT_LABOR_HRS, 0)
1916 else
1917 NULL
1918 end
1919 ) ACT_LABOR_HRS,
1920 sum( case when fact.time_id <= l_end_period_id
1921 then
1922 decode(fact.plan_version_id, l_plan_ver_id, fact.ACT_EQUIP_HRS, 0)
1923 else
1924 NULL
1925 end
1926 ) ACT_EQUIP_HRS,
1927 sum( case when fact.time_id <= l_end_period_id
1928 then
1929 decode(fact.plan_version_id, l_plan_ver_id, TXN_MASK* fact.act_labor_brdn_cost, 0 )
1930 else
1931 NULL
1932 end
1933 ) ACT_TXN_LABOR_BRDN_COST,
1934 sum( case when fact.time_id <= l_end_period_id
1935 then
1936 decode(fact.plan_version_id, l_plan_ver_id, TXN_MASK* fact.act_equip_brdn_cost, 0 )
1937 else
1938 NULL
1939 end
1940 ) ACT_TXN_EQUIP_BRDN_COST,
1941 sum( case when fact.time_id <= l_end_period_id
1942 then
1943 decode(fact.plan_version_id, l_plan_ver_id, TXN_MASK* fact.act_raw_cost, 0 )
1944 else
1945 NULL
1946 end
1947 ) ACT_TXN_RAW_COST,
1948 sum( case when fact.time_id <= l_end_period_id
1949 then
1950 decode(fact.plan_version_id, l_plan_ver_id, TXN_MASK* fact.act_brdn_cost, 0 )
1951 else
1952 NULL
1953 end
1954 ) ACT_TXN_BRDN_COST,
1955 sum( case when fact.time_id <= l_end_period_id
1956 then
1957 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK* fact.act_labor_brdn_cost, 0)
1958 else
1959 NULL
1960 end
1961 ) ACT_PRJ_LABOR_BRDN_COST,
1962 sum( case when fact.time_id <= l_end_period_id
1963 then
1964 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK* fact.act_equip_brdn_cost, 0)
1965 else
1966 NULL
1967 end
1968 ) ACT_PRJ_EQUIP_BRDN_COST,
1969 sum( case when fact.time_id <= l_end_period_id
1970 then
1971 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK* fact.act_raw_cost, 0 )
1972 else
1973 NULL
1974 end
1975 ) ACT_PRJ_RAW_COST,
1976 sum( case when fact.time_id <= l_end_period_id
1977 then
1978 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK* fact.act_brdn_cost, 0 )
1979 else
1980 NULL
1981 end
1982 ) ACT_PRJ_BRDN_COST,
1983 sum( case when fact.time_id <= l_end_period_id
1984 then
1985 decode(fact.plan_version_id, l_plan_ver_id, POU_MASK* fact.act_labor_brdn_cost, 0 )
1986 else
1987 NULL
1988 end
1989 ) ACT_POU_LABOR_BRDN_COST,
1990 sum( case when fact.time_id <= l_end_period_id
1991 then
1992 decode(fact.plan_version_id, l_plan_ver_id, POU_MASK* fact.act_equip_brdn_cost, 0 )
1993 else
1994 NULL
1995 end
1996 ) ACT_POU_EQUIP_BRDN_COST,
1997 sum( case when fact.time_id <= l_end_period_id
1998 then
1999 decode(fact.plan_version_id, l_plan_ver_id, POU_MASK* fact.act_raw_cost, 0 )
2000 else
2001 NULL
2002 end
2003 ) ACT_POU_RAW_COST,
2004 sum( case when fact.time_id <= l_end_period_id
2005 then
2006 decode(fact.plan_version_id, l_plan_ver_id, POU_MASK* fact.act_brdn_cost, 0 )
2007 else
2008 NULL
2009 end
2010 ) ACT_POU_BRDN_COST,
2011
2012 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_LABOR_HRS else 0 end) ETC_LABOR_HRS,
2013 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_EQUIP_HRS else 0 end) ETC_EQUIP_HRS,
2014 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_brdn_cost*TXN_MASK else 0 end) ETC_TXN_LABOR_BRDN_COST,
2015 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_brdn_cost*TXN_MASK else 0 end) ETC_TXN_EQUIP_BRDN_COST,
2016 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_raw_cost*TXN_MASK else 0 end) ETC_TXN_RAW_COST,
2017 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_brdn_cost*TXN_MASK else 0 end) ETC_TXN_BRDN_COST,
2018 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_brdn_cost*PRJ_MASK else 0 end) ETC_PRJ_LABOR_BRDN_COST,
2019 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_brdn_cost*PRJ_MASK else 0 end) ETC_PRJ_EQUIP_BRDN_COST,
2020 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_raw_cost*PRJ_MASK else 0 end) ETC_PRJ_RAW_COST,
2021 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_brdn_cost*PRJ_MASK else 0 end) ETC_PRJ_BRDN_COST,
2022 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_brdn_cost*POU_MASK else 0 end) ETC_POU_LABOR_BRDN_COST,
2023 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_brdn_cost*POU_MASK else 0 end) ETC_POU_EQUIP_BRDN_COST,
2024 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_raw_cost*POU_MASK else 0 end) ETC_POU_RAW_COST,
2025 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_brdn_cost*POU_MASK else 0 end) ETC_POU_BRDN_COST,
2026 sum( case when fact.time_id <= l_end_period_id
2027 then
2028 decode(fact.plan_version_id, l_plan_ver_id, TXN_MASK*fact.act_labor_raw_cost,0)
2029 else
2030 NULL
2031 end
2032 ) ACT_TXN_LABOR_RAW_COST,
2033 sum( case when fact.time_id <= l_end_period_id
2034 then
2035 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK* fact.act_labor_raw_cost,0)
2036 else
2037 NULL
2038 end
2039 ) ACT_PRJ_LABOR_RAW_COST,
2040 sum( case when fact.time_id <= l_end_period_id
2041 then
2042 decode(fact.plan_version_id, l_plan_ver_id, POU_MASK* fact.act_labor_raw_cost,0)
2043 else
2044 NULL
2045 end
2046 ) ACT_POU_LABOR_RAW_COST,
2047 sum( case when fact.time_id <= l_end_period_id
2048 then
2049 decode(fact.plan_version_id, l_plan_ver_id, TXN_MASK*fact.act_equip_raw_cost,0)
2050 else
2051 NULL
2052 end
2053 ) ACT_TXN_EQUIP_RAW_COST,
2054 sum( case when fact.time_id <= l_end_period_id
2055 then
2056 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK* fact.act_equip_raw_cost,0)
2057 else
2058 NULL
2059 end
2060 ) ACT_PRJ_EQUIP_RAW_COST,
2061 sum( case when fact.time_id <= l_end_period_id
2062 then
2063 decode(fact.plan_version_id, l_plan_ver_id, POU_MASK* fact.act_equip_raw_cost,0)
2064 else
2065 NULL
2066 end
2067 ) ACT_POU_EQUIP_RAW_COST,
2068
2069 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_raw_cost*TXN_MASK else 0 end) ETC_TXN_LABOR_RAW_COST,
2070 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_raw_cost*PRJ_MASK else 0 end) ETC_PRJ_LABOR_RAW_COST,
2071 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_raw_cost*POU_MASK else 0 end) ETC_POU_LABOR_RAW_COST,
2072 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_raw_cost*TXN_MASK else 0 end) ETC_TXN_EQUIP_RAW_COST,
2073 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_raw_cost*PRJ_MASK else 0 end) ETC_PRJ_EQUIP_RAW_COST,
2074 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_raw_cost*POU_MASK else 0 end) ETC_POU_EQUIP_RAW_COST,
2075 /* Retrival of Project Level Data Starts*/
2076 sum(case when fact.plan_version_id = l_plan_ver_id then fact.raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_RAW_COST,
2077 sum(case when fact.plan_version_id = l_plan_ver_id then fact.brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BRDN_COST,
2078 sum(case when fact.plan_version_id = l_plan_ver_id then fact.revenue*PRJ_MASK*ROLLUP_MASK else 0 end) P_REVENUE,
2079 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LBR_RAW_COST,
2080 sum(case when fact.plan_version_id = l_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LBR_BRDN_COST,
2081 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_EQP_RAW_COST,
2082 sum(case when fact.plan_version_id = l_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_EQP_BRDN_COST,
2083 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_RAW_COST,
2084 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_BRDN_COST,
2085 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_LBR_RAW_COST,
2086 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_LBR_BRDN_COST,
2087 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_EQP_RAW_COST,
2088 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_EQP_BRDN_COST,
2089 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_RAW_COST,
2090 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_BRDN_COST,
2091 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_LBR_RAW_COST,
2092 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_LBR_BRDN_COST,
2093 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_EQP_RAW_COST,
2094 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_EQP_BRDN_COST,
2095 sum(case when fact.plan_version_id = l_plan_ver_id then fact.LABOR_HRS*ROLLUP_MASK else 0 end) P_LBR_HOURS,
2096 sum(case when fact.plan_version_id = l_plan_ver_id then fact.EQUIPMENT_HOURS*ROLLUP_MASK else 0 end) P_EQP_HOURS,
2097 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.LABOR_HRS*ROLLUP_MASK else 0 end) P_BASE_LBR_HOURS,
2098 sum(case when fact.plan_version_id = l_base_plan_ver_id then fact.EQUIPMENT_HOURS*ROLLUP_MASK else 0 end) P_BASE_EQP_HOURS,
2099 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.LABOR_HRS*ROLLUP_MASK else 0 end) P_LPB_LBR_HOURS,
2100 sum(case when fact.plan_version_id = l_lpb_plan_ver_id then fact.EQUIPMENT_HOURS*ROLLUP_MASK else 0 end) P_LPB_EQP_HOURS,
2101
2102
2103 sum( case when fact.time_id <= l_end_period_id
2104 then
2105 decode(fact.plan_version_id, l_plan_ver_id, ROLLUP_MASK*fact.ACT_LABOR_HRS, 0)
2106 else
2107 NULL
2108 end
2109 ) P_ACT_LBR_HOURS,
2110 sum( case when fact.time_id <= l_end_period_id
2111 then
2112 decode(fact.plan_version_id, l_plan_ver_id, ROLLUP_MASK*fact.ACT_EQUIP_HRS, 0)
2113 else
2114 NULL
2115 end
2116 ) P_ACT_EQP_HOURS,
2117 sum( case when fact.time_id <= l_end_period_id
2118 then
2119 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK*ROLLUP_MASK* fact.act_labor_brdn_cost, 0)
2120 else
2121 NULL
2122 end
2123 ) P_ACT_LBR_BRDN_COST,
2124 sum( case when fact.time_id <= l_end_period_id
2125 then
2126 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK*ROLLUP_MASK* fact.act_equip_brdn_cost, 0)
2127 else
2128 NULL
2129 end
2130 ) P_ACT_EQP_BRDN_COST,
2131 sum( case when fact.time_id <= l_end_period_id
2132 then
2133 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK*ROLLUP_MASK* fact.act_raw_cost, 0 )
2134 else
2135 NULL
2136 end
2137 ) P_ACT_RAW_COST,
2138 sum( case when fact.time_id <= l_end_period_id
2139 then
2140 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK*ROLLUP_MASK* fact.act_brdn_cost, 0 )
2141 else
2142 NULL
2143 end
2144 ) P_ACT_BRDN_COST,
2145 sum( case when fact.time_id <= l_end_period_id
2146 then
2147 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK*ROLLUP_MASK* fact.act_labor_raw_cost,0)
2148 else
2149 NULL
2150 end
2151 ) P_ACT_LBR_RAW_COST,
2152 sum( case when fact.time_id <= l_end_period_id
2153 then
2154 decode(fact.plan_version_id, l_plan_ver_id, PRJ_MASK*ROLLUP_MASK* fact.act_equip_raw_cost,0)
2155 else
2156 NULL
2157 end
2158 ) P_ACT_EQP_RAW_COST,
2159
2160 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_EQUIP_HRS*ROLLUP_MASK else 0 end) P_ETC_EQP_HOURS,
2161 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_LABOR_HRS*ROLLUP_MASK else 0 end) P_ETC_LBR_HOURS,
2162 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_RAW_COST,
2163 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_BRDN_COST,
2164 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_LBR_BRDN_COST,
2165 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_EQP_BRDN_COST,
2166 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_LBR_RAW_COST,
2167 sum(case when fact.plan_version_id = l_plan_ver_id then fact.ETC_equip_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_EQP_RAW_COST
2168 /* Retrival of Project Level Data Ends*/
2169 FROM
2170 (
2171 SELECT
2172 PROJECT_ID ,
2173 PROJECT_ORG_ID ,
2174 PROJECT_ORGANIZATION_ID ,
2175 PROJECT_ELEMENT_ID ,
2176 TIME_ID ,
2177 PERIOD_TYPE_ID ,
2178 CALENDAR_TYPE ,
2179 RBS_AGGR_LEVEL ,
2180 WBS_ROLLUP_FLAG ,
2181 PRG_ROLLUP_FLAG ,
2182 decode ( cc_src.curr_type, 'TXN', 16, 'PRJ', 8, 'POU', 4) CURR_RECORD_TYPE_ID ,
2183 CURRENCY_CODE ,
2184 RBS_ELEMENT_ID ,
2185 RBS_VERSION_ID ,
2186 PLAN_VERSION_ID ,
2187 -- PLAN_TYPE_ID ,
2188 RAW_COST ,
2189 BRDN_COST ,
2190 REVENUE ,
2191 BILL_RAW_COST ,
2192 BILL_BRDN_COST ,
2193 BILL_LABOR_RAW_COST ,
2194 BILL_LABOR_BRDN_COST ,
2195 decode ( cc_src.curr_type, 'PRJ', BILL_LABOR_HRS, 0) BILL_LABOR_HRS ,
2196 EQUIPMENT_RAW_COST ,
2197 EQUIPMENT_BRDN_COST ,
2198 CAPITALIZABLE_RAW_COST ,
2199 CAPITALIZABLE_BRDN_COST ,
2200 LABOR_RAW_COST ,
2201 LABOR_BRDN_COST ,
2202 decode ( cc_src.curr_type, 'PRJ', LABOR_HRS, 0) LABOR_HRS ,
2203 LABOR_REVENUE ,
2204 decode ( cc_src.curr_type, 'PRJ', EQUIPMENT_HOURS, 0) EQUIPMENT_HOURS ,
2205 decode ( cc_src.curr_type, 'PRJ', BILLABLE_EQUIPMENT_HOURS, 0) BILLABLE_EQUIPMENT_HOURS ,
2206 SUP_INV_COMMITTED_COST ,
2207 PO_COMMITTED_COST ,
2208 PR_COMMITTED_COST ,
2209 OTH_COMMITTED_COST ,
2210 CUSTOM1 ,
2211 CUSTOM2 ,
2212 CUSTOM3 ,
2213 CUSTOM4 ,
2214 CUSTOM5 ,
2215 CUSTOM6 ,
2216 CUSTOM7 ,
2217 CUSTOM8 ,
2218 CUSTOM9 ,
2219 CUSTOM10 ,
2220 CUSTOM11 ,
2221 CUSTOM12 ,
2222 CUSTOM13 ,
2223 CUSTOM14 ,
2224 CUSTOM15 ,
2225 decode ( cc_src.curr_type, 'PRJ', ACT_LABOR_HRS, 0) ACT_LABOR_HRS ,
2226 decode ( cc_src.curr_type, 'PRJ', ACT_EQUIP_HRS, 0) ACT_EQUIP_HRS ,
2227 ACT_LABOR_BRDN_COST ,
2228 ACT_EQUIP_BRDN_COST ,
2229 ACT_BRDN_COST ,
2230 decode ( cc_src.curr_type, 'PRJ', ETC_LABOR_HRS, 0) ETC_LABOR_HRS ,
2231 decode ( cc_src.curr_type, 'PRJ', ETC_EQUIP_HRS, 0) ETC_EQUIP_HRS ,
2232 ETC_LABOR_BRDN_COST ,
2233 ETC_EQUIP_BRDN_COST ,
2234 ETC_BRDN_COST ,
2235 ACT_RAW_COST ,
2236 ACT_REVENUE ,
2237 ETC_RAW_COST ,
2238 ACT_LABOR_RAW_COST ,
2239 ACT_EQUIP_RAW_COST ,
2240 ETC_LABOR_RAW_COST ,
2241 ETC_EQUIP_RAW_COST ,
2242 decode(fact.prg_rollup_flag,'N',1,0) ROLLUP_MASK,
2243 decode ( cc_src.curr_type, 'TXN',1,0) TXN_MASK,
2244 decode ( cc_src.curr_type, 'PRJ',1,0) PRJ_MASK,
2245 decode ( cc_src.curr_type, 'POU',1,0) POU_MASK
2246 from
2247 pji_fp_xbs_accum_f fact,
2248 (
2249 SELECT 'TXN' curr_type FROM DUAL
2250 UNION ALL
2251 SELECT 'PRJ' curr_type FROM DUAL
2252 UNION ALL
2253 SELECT 'POU' curr_type FROM DUAL
2254 ) cc_src
2255 where 1=1
2256 and ( decode ( cc_src.curr_type, 'TXN', DECODE(BITAND(fact.curr_record_type_id, 16), 16, 'a'), 'b') = 'a'
2257 or decode ( cc_src.curr_type, 'PRJ', DECODE(BITAND(fact.curr_record_type_id, 8), 8, 'a'), 'b') = 'a'
2258 or decode ( cc_src.curr_type, 'POU', DECODE(BITAND(fact.curr_record_type_id, 4), 4, 'a'), 'b') = 'a' )
2259 ) fact,
2260 pji_plan_extr_tmp head
2261 WHERE 1=1
2262 and fact.PROJECT_ID = head.PROJECT_ID
2263 and fact.PLAN_VERSION_ID in (l_plan_ver_id,l_lpb_plan_ver_id,l_base_plan_ver_id)
2264 and fact.PROJECT_ELEMENT_ID = head.plan_ver_id -- plan_version_id contains the project_element_id
2265 and fact.CALENDAR_TYPE = l_cal_type
2266 and fact.PERIOD_TYPE_ID = l_prd_type_id
2267 and BITAND(fact.CURR_RECORD_TYPE_ID,28) <= 28
2268 and BITAND(fact.CURR_RECORD_TYPE_ID,28) >= 4
2269 and fact.RBS_AGGR_LEVEL = 'T'
2270 and fact.prg_rollup_flag ='N'
2271 GROUP BY
2272 fact.PROJECT_ID,
2273 fact.PROJECT_ELEMENT_ID,
2274 fact.CALENDAR_TYPE;
2275
2276 IF NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N') = 'Y' THEN
2277 debug_accum ; /* bug#3993830 */
2278 END IF;
2279 DELETE FROM PJI_PLAN_EXTR_TMP; --- Bug 5653800
2280 x_return_status := l_return_status;
2281
2282 EXCEPTION
2283 WHEN OTHERS THEN
2284
2285 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
2286 ( p_package_name => g_package_name
2287 , p_procedure_name => 'POPULATE_UPDATEWBS_DATA'
2288 , x_return_status => x_return_status ) ;
2289
2290 RAISE;
2291 END;
2292
2293
2294
2295 /*********************************************************
2296 This procedure populates data in PJI_FM_XBS_ACCUM_TMP1
2297 for workplans.
2298 API supports both work plans and progress actuals
2299 The parameters that the API accepts can have one of the
2300 following combinations
2301 - p_struct_ver_id, p_base_struct_ver_id
2302 - p_plan_version_id
2303 **********************************************************/
2304 PROCEDURE populate_workplan_data (
2305 p_populate_in_tbl IN populate_in_tbl_type := populate_in_default_tbl,
2306 p_project_id IN NUMBER := NULL,
2307 p_struct_ver_id IN NUMBER := NULL,
2308 p_base_struct_ver_id IN NUMBER := NULL,
2309 p_plan_version_id IN NUMBER := NULL,
2310 p_progress_actuals_flag IN VARCHAR2 := 'N',
2311 p_as_of_date IN DATE := NULL,
2312 p_delete_flag IN VARCHAR2 := 'Y',
2313 p_workplan_flag IN VARCHAR2 := 'Y',
2314 p_project_element_id IN NUMBER := NULL,
2315 p_calling_context IN VARCHAR2 := NULL,
2316 p_program_rollup_flag IN VARCHAR2 := 'N',
2317 x_return_status OUT NOCOPY VARCHAR2,
2318 x_msg_code OUT NOCOPY VARCHAR2 ) IS
2319
2320 l_project_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type ();
2321 l_wk_struct_ver_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
2322 l_lpb_struct_ver_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
2323 l_base_struct_ver_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
2324 l_cal_type_tbl SYSTEM.pa_varchar2_15_tbl_type := SYSTEM.pa_varchar2_15_tbl_type();
2325 l_period_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
2326 l_end_period_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
2327
2328 l_org_id NUMBER;
2329 l_return_status VARCHAR2(1);
2330
2331 BEGIN
2332
2333 PJI_PJP_FP_CURR_WRAP.INIT_ERR_STACK
2334 ( p_package_name => g_package_name
2335 , x_return_status => x_return_status );
2336
2337 pa_debug.log_message('populate_workplan_data:p_project_id_tbl'||p_populate_in_tbl.COUNT, 3);
2338
2339 pji_pjp_fp_curr_wrap.set_table_stats('PJI','PJI_PLAN_EXTR_TMP',10,10,10);
2340
2341 l_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2342
2343 PRINT_TIME ( ' populate_workplan_data 0003.1 ' ) ;
2344
2345 IF p_project_id is not null then
2346
2347 INSERT INTO pji_plan_extr_tmp
2348 ( PROJECT_ID , PLAN_VER_ID , STRUCT_VER_ID , BASE_STRUCT_VER_ID , AS_OF_DATE , PROJ_ELEM_ID )
2349 VALUES ( p_project_id,p_plan_version_id,p_struct_ver_id , p_base_struct_ver_id,p_as_of_date,p_project_element_id );
2350
2351 print_time (' p_project_id ' || p_project_id || ' p_struct_ver_id ' || p_struct_ver_id );
2352 print_time (' p_base_struct_ver_id ' || p_base_struct_ver_id || ' p_plan_version_id ' || p_plan_version_id );
2353 print_time (' p_progress_actuals_flag ' || p_progress_actuals_flag || ' p_as_of_date ' || p_as_of_date );
2354 print_time (' p_delete_flag ' || p_delete_flag || ' p_workplan_flag ' || p_workplan_flag );
2355
2356 ELSE
2357 IF p_populate_in_tbl.COUNT >0 THEN
2358 FOR i IN p_populate_in_tbl.FIRST .. p_populate_in_tbl.LAST
2359 LOOP
2360 INSERT INTO pji_plan_extr_tmp
2361 ( PROJECT_ID , PLAN_VER_ID , STRUCT_VER_ID , BASE_STRUCT_VER_ID , AS_OF_DATE , PROJ_ELEM_ID )
2362 VALUES ( p_populate_in_tbl(i).PROJECT_ID,p_populate_in_tbl(i).plan_version_id, p_populate_in_tbl(i).struct_ver_id,p_populate_in_tbl(i).base_struct_ver_id,
2363 p_populate_in_tbl(i).as_of_date,p_populate_in_tbl(i).project_element_id );
2364
2365 PRINT_TIME ( ' p_project_id ' || p_populate_in_tbl(i).PROJECT_ID || ' p_struct_ver_id ' || p_populate_in_tbl(i).struct_ver_id );
2366 PRINT_TIME ( ' p_base_struct_ver_id ' || p_populate_in_tbl(i).base_struct_ver_id || ' p_plan_version_id ' || p_populate_in_tbl(i).plan_version_id );
2367 PRINT_TIME ( ' p_project_element_id ' || p_populate_in_tbl(i).project_element_id || ' p_as_of_date ' || p_populate_in_tbl(i).as_of_date );
2368 PRINT_TIME ( ' p_delete_flag '|| p_delete_flag || ' p_calling_context ' || p_calling_context || ' p_workplan_flag ' || p_workplan_flag ||' p_program_rollup_flag ' || p_program_rollup_flag );
2369
2370 END LOOP;
2371 ELSE
2372 PRINT_TIME ( ' InValid parameters Passed to populate_workplan_data' );
2373 END IF;
2374 end if;
2375
2376 IF g_debug_mode='Y' THEN
2377 PRINT_TIME ( ' populate_workplan_data 0003.2 ' ) ;
2378 end if;
2379
2380 /* Added for workplan progress */
2381 -- Get the Latest Published Version for the Project
2382
2383 IF p_workplan_flag ='Y' THEN --bug#5554311
2384
2385 UPDATE pji_plan_extr_tmp TMP
2386 SET (LPB_STRUCT_VER_ID,LPB_PLAN_VER_ID)=
2387 (
2388 SELECT element_version_id,bv.budget_version_id
2389 FROM pa_proj_elem_ver_structure ppevs,
2390 pa_proj_structure_types ppst,
2391 pa_budget_versions bv
2392 WHERE 1=1
2393 and latest_eff_published_flag = 'Y'
2394 and ppst.proj_element_id = ppevs.proj_element_id
2395 and ppst.structure_type_id = 1
2396 and element_version_id=bv.project_structure_Version_id
2397 and bv.wp_version_flag ='Y'
2398 and bv.project_id=ppevs.project_id
2399 and tmp.project_id=ppevs.project_id
2400 );
2401
2402
2403
2404
2405 PRINT_TIME ( ' populate_workplan_data 0003.3 ' ) ;
2406
2407 UPDATE pji_plan_extr_tmp TMP
2408 SET WK_STRUCT_VER_ID=
2409 (
2410 SELECT WBS_VERSION_ID
2411 FROM pji_pjp_wbs_header wbs
2412 WHERE wbs.project_id=tmp.project_id AND
2413 plan_version_id = tmp.plan_ver_id
2414 );
2415
2416 PRINT_TIME ( ' populate_workplan_data 0003.4 ' ) ;
2417
2418 UPDATE pji_plan_extr_tmp TMP
2419 SET WK_PLAN_VER_ID= (
2420 SELECT head.PLAN_VERSION_ID
2421 FROM pji_pjp_wbs_header head,
2422 pa_budget_versions bv
2423 WHERE 1=1
2424 AND head.plan_version_id = bv.budget_version_id
2425 AND NVL(bv.wp_version_flag, 'N') = head.wp_flag
2426 AND NVL(bv.wp_version_flag, 'N') = p_workplan_flag
2427 AND head.WBS_VERSION_ID = tmp.struct_ver_id
2428 AND head.PROJECT_ID = tmp.project_id
2429 AND DECODE(p_workplan_flag
2430 , 'N'
2431 , DECODE(bv.budget_status_code||bv.current_working_flag
2432 , 'WY'
2433 ,'X'
2434 , 'Y')
2435 , 'X') = 'X'
2436 AND head.PLAN_VERSION_ID > 0);
2437
2438 PRINT_TIME ( ' populate_workplan_data 0003.4.1 ' ) ;
2439
2440 UPDATE pji_plan_extr_tmp TMP
2441 SET WK_STRUCT_VER_ID = STRUCT_VER_ID
2442 where WK_STRUCT_VER_ID is null;
2443
2444 PRINT_TIME ( ' populate_workplan_data 0003.5 ' ) ;
2445
2446 UPDATE pji_plan_extr_tmp TMP
2447 SET BASE_PLAN_VER_ID= (
2448 SELECT head.PLAN_VERSION_ID
2449 FROM pji_pjp_wbs_header head,
2450 pa_budget_versions bv
2451 WHERE 1=1
2452 AND head.plan_version_id = bv.budget_version_id
2453 AND NVL(bv.wp_version_flag, 'N') = head.wp_flag
2454 AND NVL(bv.wp_version_flag , 'N') = p_workplan_flag
2455 AND head.WBS_VERSION_ID = TMP.BASE_STRUCT_VER_ID
2456 AND head.PROJECT_ID = tmp.PROJECT_ID
2457 AND DECODE(p_workplan_flag
2458 , 'N'
2459 , DECODE(bv.budget_status_code||bv.current_flag
2460 , 'BY','X'
2461 , 'Y')
2462 , 'X')
2463 = 'X'
2464 AND head.PLAN_VERSION_ID > 0);
2465
2466 ELSE --- when p_workplan_flag ='N' bug#5554311
2467
2468 PRINT_TIME ( ' populate_workplan_data 0003.5.1 ' ) ;
2469
2470 UPDATE pji_plan_extr_tmp TMP
2471 SET (LPB_STRUCT_VER_ID,LPB_PLAN_VER_ID,BASE_STRUCT_VER_ID,BASE_PLAN_VER_ID,
2472 WK_STRUCT_VER_ID,WK_PLAN_VER_ID)=
2473 (SELECT p_struct_ver_id,p_plan_version_id,p_struct_ver_id,p_plan_version_id,p_struct_ver_id,p_plan_version_id from dual );
2474
2475 END IF;
2476
2477 PRINT_TIME ( ' populate_workplan_data 0003.6 ' ) ;
2478
2479
2480
2481 UPDATE pji_plan_extr_tmp TMP
2482 SET (CAL_TYPE,ORG_ID)=
2483 (
2484 SELECT DECODE(NVL(NVL(fp.cost_time_phased_code, fp.revenue_time_phased_code ), fp.all_time_phased_code), 'G', 'G', 'P', 'P', 'A') calendar_type,
2485 pa.ORG_ID
2486 FROM
2487 pa_proj_fp_options fp,
2488 pa_projects_all pa
2489 WHERE 1=1
2490 and pa.project_id=fp.project_id
2491 and pa.project_id=tmp.project_id
2492 and fp.fin_plan_option_level_code = 'PLAN_VERSION'
2493 and fp.fin_plan_version_id =tmp.WK_PLAN_VER_ID
2494 )
2495 WHERE tmp.AS_OF_DATE is not null;
2496
2497 PRINT_TIME ( ' populate_workplan_data 0003.7.1 ' ) ;
2498
2499 UPDATE pji_plan_extr_tmp TMP
2500 SET (END_PERIOD_ID,PERIOD_ID)=
2501 (
2502 SELECT cal.CAL_PERIOD_ID ,DECODE(tmp.cal_type, 'A', 2048, 32) PERIOD_TYPE_ID
2503 FROM pji_time_cal_period_v cal,
2504 pji_org_extr_info info
2505 WHERE TRUNC(tmp.AS_OF_DATE)
2506 BETWEEN TRUNC(cal.START_DATE) AND TRUNC(cal.END_DATE)
2507 AND info.ORG_ID = tmp.ORG_ID
2508 AND DECODE(tmp.cal_type, 'P', info.PA_CALENDAR_ID , info.GL_CALENDAR_ID) = cal.CALENDAR_ID
2509 )
2510 WHERE tmp.AS_OF_DATE is not null;
2511
2512 PRINT_TIME ( ' populate_workplan_data 0003.8 ' ) ;
2513
2514 -- The Temp table is populated with the Program and the Linked Projects and their corresponding plan versions
2515
2516
2517 SELECT
2518 project_id,WK_STRUCT_VER_ID,LPB_STRUCT_VER_ID,BASE_STRUCT_VER_ID,
2519 CAL_TYPE,PERIOD_ID,END_PERIOD_ID
2520 BULK COLLECT INTO
2521 l_project_id_tbl,l_wk_struct_ver_id_tbl,l_lpb_struct_ver_id_tbl,l_base_struct_ver_id_tbl,
2522 l_cal_type_tbl,l_period_id_tbl, l_end_period_id_tbl
2523 FROM PJI_PLAN_EXTR_TMP;
2524
2525 PRINT_TIME ( ' populate_workplan_data 0003.9 ' ) ;
2526
2527 IF p_program_rollup_flag='Y' and p_calling_context='SUMMARIZE' and p_workplan_flag ='Y' THEN
2528 /* Populates Data for the given Project and all the projects above and a level below */
2529 PRINT_TIME ( ' populate_workplan_data 0003.9.1 ' ) ;
2530
2531 FOR i IN 1 .. l_project_id_tbl.count
2532 LOOP
2533 INSERT into PJI_PLAN_EXTR_TMP
2534 (project_id,wk_plan_ver_id,lpb_plan_ver_id,base_plan_ver_id,struct_ver_id,cal_type,period_id,end_period_id) --Bug#5660324
2535 SELECT
2536 head.PROJECT_ID,
2537 MAX(DECODE(SUBSTR(den.RECORD_TYPE,1,1), 'W', head.plan_version_id, NULL)) wk_plan_ver_id,
2538 MAX(DECODE(SUBSTR( den.RECORD_TYPE,1,1), 'P', head.plan_version_id, NULL)) lpb_plan_ver_id ,
2539 MAX(DECODE(SUBSTR(den.RECORD_TYPE,1,1), 'B', head.plan_version_id, NULL)) base_plan_ver_id,
2540 MAX(DECODE(SUBSTR( den.RECORD_TYPE,1,1), 'W', den.wbs_version_id, NULL)) struct_ver_id,
2541 MAX(l_cal_type_tbl(i)),
2542 MAX(l_period_id_tbl(i)),
2543 MAX(l_end_period_id_tbl(i))
2544 FROM (
2545 SELECT
2546 DECODE(SUBSTR(record_type,2,1),'R',sub_id,'S',sup_id) wbs_version_id,record_type,
2547 DECODE(NVL(sub_rollup_id,sup_emt_id),sup_emt_id,0,1) relationship
2548 FROM
2549 (
2550 SELECT
2551 sub_id,sup_id,sub_rollup_id,sup_emt_id,'WR' record_type
2552 FROM
2553 pji_xbs_Denorm wrk
2554 WHERE
2555 wrk.STRUCT_TYPE = 'PRG' AND
2556 wrk.SUP_ID = l_wk_struct_ver_id_tbl(i) AND
2557 ( wrk.RELATIONSHIP_TYPE <>'LF' OR wrk.RELATIONSHIP_TYPE IS NULL) AND
2558 wrk.struct_version_id is null
2559 UNION ALL
2560 SELECT
2561 sub_id,sup_id,sub_rollup_id,sup_emt_id,'PR' record_type
2562 FROM
2563 pji_xbs_Denorm pub
2564 WHERE
2565 pub.STRUCT_TYPE = 'PRG' AND
2566 pub.SUP_ID = l_lpb_struct_ver_id_tbl(i) AND
2567 ( pub.RELATIONSHIP_TYPE <>'LF' OR pub.RELATIONSHIP_TYPE IS NULL) AND
2568 pub.struct_version_id is null
2569 UNION ALL
2570 SELECT
2571 sub_id,sup_id,sub_rollup_id,sup_emt_id,'BR' record_type
2572 FROM
2573 pji_xbs_Denorm base
2574 WHERE
2575 base.STRUCT_TYPE = 'PRG' AND
2576 base.SUP_ID = l_base_struct_ver_id_tbl(i) AND
2577 ( base.RELATIONSHIP_TYPE <>'LF' OR base.RELATIONSHIP_TYPE IS NULL) AND
2578 base.struct_version_id is null
2579 UNION ALL
2580 SELECT sub_id,sup_id,sub_rollup_id,sup_emt_id,'WS' record_type
2581 FROM
2582 pji_xbs_Denorm wrk
2583 WHERE
2584 wrk.STRUCT_TYPE = 'PRG' AND
2585 wrk.SUB_ID = l_wk_struct_ver_id_tbl(i) AND
2586 ( wrk.RELATIONSHIP_TYPE <>'LF' OR wrk.RELATIONSHIP_TYPE IS NULL) AND
2587 wrk.struct_version_id is null
2588 UNION ALL
2589 SELECT sub_id,sup_id,sub_rollup_id,sup_emt_id,'PS' record_type
2590 FROM
2591 pji_xbs_Denorm pub
2592 WHERE
2593 pub.STRUCT_TYPE = 'PRG' AND
2594 pub.SUB_ID = l_lpb_struct_ver_id_tbl(i) AND
2595 ( pub.RELATIONSHIP_TYPE <>'LF' OR pub.RELATIONSHIP_TYPE IS NULL) AND
2596 pub.struct_version_id is null
2597 UNION ALL
2598 SELECT sub_id,sup_id,sub_rollup_id,sup_emt_id,'BS' record_type
2599 FROM
2600 pji_xbs_Denorm base
2601 WHERE
2602 base.STRUCT_TYPE = 'PRG' AND
2603 base.SUB_ID = l_base_struct_ver_id_tbl(i) AND
2604 ( base.RELATIONSHIP_TYPE <>'LF' OR base.RELATIONSHIP_TYPE IS NULL) AND
2605 base.struct_version_id is null
2606 )
2607 )
2608 den,
2609 pa_proj_element_versions ver,
2610 pji_pjp_wbs_header head
2611 WHERE
2612 den.WBS_VERSION_ID = ver.element_version_id AND
2613 den.record_type is not null AND
2614 DECODE(SUBSTR(den.RECORD_TYPE,2,1),'S',1,'R',den.RELATIONSHIP) =1 AND
2615 ver.project_id = head.project_id AND
2616 den.WBS_VERSION_ID = head.wbs_version_id AND
2617 head.WP_FLAG = 'Y'
2618 GROUP BY head.project_id;
2619 END LOOP;
2620
2621
2622
2623 ELSIF p_program_rollup_flag='Y' and p_calling_context='ROLLUP' and p_workplan_flag ='Y' THEN
2624 /* Populates Data for the Project and all the projects below the given project */
2625
2626 PRINT_TIME ( ' populate_workplan_data 0003.9.2 ' ) ;
2627
2628 FOR i IN 1 .. l_project_id_tbl.count
2629 LOOP
2630
2631 INSERT into PJI_PLAN_EXTR_TMP
2632 (project_id,wk_plan_ver_id,lpb_plan_ver_id,base_plan_ver_id,struct_ver_id,cal_type,period_id,end_period_id) --Bug#5660324
2633 SELECT
2634 head.PROJECT_ID,
2635 MAX(DECODE(den.RECORD_TYPE, 'W', head.plan_version_id, NULL)) wk_plan_ver_id,
2636 MAX(DECODE(den.RECORD_TYPE, 'P', head.plan_version_id, NULL)) lpb_plan_ver_id ,
2637 MAX(DECODE(den.RECORD_TYPE, 'B', head.plan_version_id, NULL)) base_plan_ver_id,
2638 MAX(DECODE(den.RECORD_TYPE, 'W', den.wbs_version_id, NULL)) struct_ver_id,
2639 MAX(l_cal_type_tbl(i)),
2640 MAX(l_period_id_tbl(i)),
2641 MAX(l_end_period_id_tbl(i))
2642 FROM
2643 (
2644 SELECT
2645 wrk.SUB_ID wbs_version_id,'W' record_type
2646 FROM
2647 pji_xbs_Denorm wrk
2648 WHERE
2649 wrk.STRUCT_TYPE = 'PRG' AND
2650 wrk.SUP_ID = l_wk_struct_ver_id_tbl(i) AND
2651 ( wrk.RELATIONSHIP_TYPE <>'LF' OR wrk.RELATIONSHIP_TYPE IS NULL) AND
2652 wrk.struct_version_id is null
2653 UNION ALL
2654 SELECT
2655 pub.SUB_ID wbs_version_id,'P' record_type
2656 FROM
2657 pji_xbs_Denorm pub
2658 WHERE
2659 pub.STRUCT_TYPE = 'PRG' AND
2660 pub.SUP_ID = l_lpb_struct_ver_id_tbl(i) AND
2661 ( pub.RELATIONSHIP_TYPE <>'LF' OR pub.RELATIONSHIP_TYPE IS NULL) AND
2662 pub.struct_version_id is null
2663 UNION ALL
2664 SELECT
2665 base.SUB_ID wbs_version_id,'B' record_type
2666 FROM
2667 pji_xbs_Denorm base
2668 WHERE
2669 base.STRUCT_TYPE = 'PRG' AND
2670 base.SUP_ID = l_base_struct_ver_id_tbl(i) AND
2671 ( base.RELATIONSHIP_TYPE <>'LF' OR base.RELATIONSHIP_TYPE IS NULL) AND
2672 base.struct_version_id is null
2673 )
2674 den,
2675 pa_proj_element_versions ver,
2676 pji_pjp_wbs_header head
2677 WHERE
2678 den.wbs_version_id = ver.element_version_id AND
2679 ver.project_id = head.project_id AND
2680 den.wbs_version_id = head.wbs_version_id AND
2681 head.WP_FLAG = 'Y'
2682 GROUP BY head.project_id;
2683
2684 END LOOP;
2685
2686 END IF;
2687
2688 /* Start of changes for bug 5751250 */
2689
2690 IF p_calling_context = 'MSP' THEN
2691
2692 UPDATE pji_plan_extr_tmp
2693 SET WK_PLAN_VER_ID = -1, BASE_PLAN_VER_ID = -1, LPB_PLAN_VER_ID = -1;
2694
2695 END IF;
2696
2697 /* End of changes for bug 5751250 */
2698
2699 --Ensures that data is cleaned up for the Project / Program and the linked sub projects
2700 IF (p_delete_flag = 'Y') THEN
2701
2702 PRINT_TIME ( ' populate_workplan_data 0003.10 ' ) ;
2703
2704 DELETE FROM pji_fm_xbs_accum_tmp1
2705 WHERE rowid IN
2706 (
2707 SELECT tmp.rowid
2708 FROM pji_plan_extr_tmp head, pji_fm_xbs_accum_tmp1 tmp
2709 WHERE head.project_id=tmp.project_id
2710 );
2711
2712 END IF;
2713
2714 PRINT_TIME ( ' populate_workplan_data 0003.10.1 ' ) ;
2715
2716 DELETE FROM PJI_PLAN_EXTR_TMP tmp1
2717 WHERE EXISTS
2718 ( SELECT * FROM PJI_PLAN_EXTR_TMP tmp2
2719 WHERE tmp1.PROJECT_ID=tmp2.PROJECT_ID
2720 AND tmp1.ROWID > tmp2.ROWID );
2721
2722 IF g_debug_mode='Y' THEN
2723 PRINT_TIME ( ' populate_workplan_data 0003.11 ' ) ;
2724 END IF;
2725
2726
2727
2728 --
2729 -- Get task level data from reporting lines
2730 -- Data is rolled up by WBS hierarchy
2731 -- Data inserted is the Totals
2732 --
2733
2734 INSERT INTO pji_fm_xbs_accum_tmp1 (
2735 PROJECT_ID, STRUCT_VERSION_ID, PROJECT_ELEMENT_ID, CALENDAR_TYPE,
2736 PERIOD_NAME, PLAN_VERSION_ID, QUANTITY, TXN_RAW_COST,
2737 TXN_BRDN_COST, TXN_REVENUE, TXN_LABOR_RAW_COST, TXN_LABOR_BRDN_COST,
2738 TXN_EQUIP_RAW_COST, TXN_EQUIP_BRDN_COST, TXN_BASE_RAW_COST, TXN_BASE_BRDN_COST,
2739 TXN_BASE_LABOR_RAW_COST, TXN_BASE_LABOR_BRDN_COST,TXN_BASE_EQUIP_RAW_COST, TXN_BASE_EQUIP_BRDN_COST,
2740 TXN_LPB_RAW_COST, TXN_LPB_BRDN_COST, TXN_LPB_LABOR_RAW_COST, TXN_LPB_LABOR_BRDN_COST,
2741 TXN_LPB_EQUIP_RAW_COST, TXN_LPB_EQUIP_BRDN_COST, PRJ_RAW_COST, PRJ_BRDN_COST,
2742 PRJ_REVENUE, PRJ_LABOR_RAW_COST, PRJ_LABOR_BRDN_COST, PRJ_EQUIP_RAW_COST,
2743 PRJ_EQUIP_BRDN_COST, PRJ_BASE_RAW_COST, PRJ_BASE_BRDN_COST, PRJ_BASE_LABOR_RAW_COST,
2744 PRJ_BASE_LABOR_BRDN_COST,PRJ_BASE_EQUIP_RAW_COST, PRJ_BASE_EQUIP_BRDN_COST,PRJ_LPB_RAW_COST,
2745 PRJ_LPB_BRDN_COST, PRJ_LPB_LABOR_RAW_COST, PRJ_LPB_LABOR_BRDN_COST, PRJ_LPB_EQUIP_RAW_COST,
2746 PRJ_LPB_EQUIP_BRDN_COST, POU_RAW_COST, POU_BRDN_COST, POU_REVENUE,
2747 POU_LABOR_RAW_COST, POU_LABOR_BRDN_COST, POU_EQUIP_RAW_COST, POU_EQUIP_BRDN_COST,
2748 POU_BASE_RAW_COST, POU_BASE_BRDN_COST, POU_BASE_LABOR_RAW_COST, POU_BASE_LABOR_BRDN_COST,
2749 POU_BASE_EQUIP_RAW_COST, POU_BASE_EQUIP_BRDN_COST,POU_LPB_RAW_COST, POU_LPB_BRDN_COST,
2750 POU_LPB_LABOR_RAW_COST, POU_LPB_LABOR_BRDN_COST, POU_LPB_EQUIP_RAW_COST, POU_LPB_EQUIP_BRDN_COST,
2751 LABOR_HOURS, EQUIPMENT_HOURS, BASE_LABOR_HOURS, BASE_EQUIP_HOURS,
2752 LPB_LABOR_HOURS, LPB_EQUIP_HOURS, ACT_LABOR_HRS, ACT_EQUIP_HRS,
2753 ACT_TXN_LABOR_BRDN_COST, ACT_TXN_EQUIP_BRDN_COST, ACT_TXN_RAW_COST, ACT_TXN_BRDN_COST,
2754 ACT_PRJ_LABOR_BRDN_COST, ACT_PRJ_EQUIP_BRDN_COST, ACT_PRJ_RAW_COST, ACT_PRJ_BRDN_COST,
2755 ACT_POU_LABOR_BRDN_COST, ACT_POU_EQUIP_BRDN_COST, ACT_POU_RAW_COST, ACT_POU_BRDN_COST,
2756 ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_TXN_LABOR_BRDN_COST, ETC_TXN_EQUIP_BRDN_COST,
2757 ETC_TXN_RAW_COST, ETC_TXN_BRDN_COST, ETC_PRJ_LABOR_BRDN_COST, ETC_PRJ_EQUIP_BRDN_COST,
2758 ETC_PRJ_RAW_COST, ETC_PRJ_BRDN_COST, ETC_POU_LABOR_BRDN_COST, ETC_POU_EQUIP_BRDN_COST,
2759 ETC_POU_RAW_COST, ETC_POU_BRDN_COST, ACT_TXN_LABOR_RAW_COST , ACT_PRJ_LABOR_RAW_COST,
2760 ACT_POU_LABOR_RAW_COST, ACT_TXN_EQUIP_RAW_COST, ACT_PRJ_EQUIP_RAW_COST, ACT_POU_EQUIP_RAW_COST,
2761 ETC_TXN_LABOR_RAW_COST, ETC_PRJ_LABOR_RAW_COST, ETC_POU_LABOR_RAW_COST, ETC_TXN_EQUIP_RAW_COST,
2762 ETC_PRJ_EQUIP_RAW_COST, ETC_POU_EQUIP_RAW_COST, P_RAW_COST,P_BRDN_COST, P_REVENUE,
2763 P_LBR_RAW_COST, P_LBR_BRDN_COST, P_EQP_RAW_COST, P_EQP_BRDN_COST,
2764 P_BASE_RAW_COST, P_BASE_BRDN_COST, P_BASE_LBR_RAW_COST, P_BASE_LBR_BRDN_COST,
2765 P_BASE_EQP_RAW_COST, P_BASE_EQP_BRDN_COST, P_LPB_RAW_COST, P_LPB_BRDN_COST,
2766 P_LPB_LBR_RAW_COST, P_LPB_LBR_BRDN_COST, P_LPB_EQP_RAW_COST, P_LPB_EQP_BRDN_COST,
2767 P_LBR_HOURS, P_EQP_HOURS, P_BASE_LBR_HOURS, P_BASE_EQP_HOURS,
2768 P_LPB_LBR_HOURS, P_LPB_EQP_HOURS, P_ACT_LBR_HOURS, P_ACT_EQP_HOURS,
2769 P_ACT_LBR_BRDN_COST, P_ACT_EQP_BRDN_COST, P_ACT_RAW_COST, P_ACT_BRDN_COST,
2770 P_ACT_LBR_RAW_COST, P_ACT_EQP_RAW_COST, P_ETC_EQP_HOURS, P_ETC_LBR_HOURS,
2771 P_ETC_RAW_COST, P_ETC_BRDN_COST, P_ETC_LBR_BRDN_COST, P_ETC_EQP_BRDN_COST,
2772 P_ETC_LBR_RAW_COST,P_ETC_EQP_RAW_COST
2773 )
2774 SELECT /*+ LEADING(head) USE_NL(fact.fact) */
2775 fact.PROJECT_ID,
2776 struct_ver_id STRUCT_VERSION_ID,
2777 fact.PROJECT_ELEMENT_ID PROJECT_ELEMENT_ID,
2778 'A',
2779 null PERIOD_NAME,
2780 WK_PLAN_VER_ID PLAN_VERSION_ID,
2781 0 QUANTITY,
2782 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.raw_cost*TXN_MASK else 0 end) TXN_RAW_COST,
2783 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.brdn_cost*TXN_MASK else 0 end) TXN_BRDN_COST,
2784 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.revenue*TXN_MASK else 0 end) TXN_REVENUE,
2785 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_raw_cost*TXN_MASK else 0 end) TXN_LABOR_RAW_COST,
2786 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_brdn_cost*TXN_MASK else 0 end) TXN_LABOR_BRDN_COST,
2787 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_raw_cost*TXN_MASK else 0 end) TXN_EQUIP_RAW_COST,
2788 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_brdn_cost*TXN_MASK else 0 end) TXN_EQUIP_BRDN_COST,
2789 sum(case when fact.plan_version_id = base_plan_ver_id then fact.raw_cost*TXN_MASK else 0 end) TXN_BASE_RAW_COST,
2790 sum(case when fact.plan_version_id = base_plan_ver_id then fact.brdn_cost*TXN_MASK else 0 end) TXN_BASE_BRDN_COST,
2791 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_raw_cost*TXN_MASK else 0 end) TXN_BASE_LABOR_RAW_COST,
2792 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_brdn_cost*TXN_MASK else 0 end) TXN_BASE_LABOR_BRDN_COST,
2793 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_raw_cost*TXN_MASK else 0 end) TXN_BASE_EQUIP_RAW_COST,
2794 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_brdn_cost*TXN_MASK else 0 end) TXN_BASE_EQUIP_BRDN_COST,
2795 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.raw_cost*TXN_MASK else 0 end) TXN_LPB_RAW_COST,
2796 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.brdn_cost*TXN_MASK else 0 end) TXN_LPB_BRDN_COST,
2797 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_raw_cost*TXN_MASK else 0 end) TXN_LPB_LABOR_RAW_COST,
2798 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_brdn_cost*TXN_MASK else 0 end) TXN_LPB_LABOR_BRDN_COST,
2799 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_raw_cost*TXN_MASK else 0 end) TXN_LPB_EQUIP_RAW_COST,
2800 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_brdn_cost*TXN_MASK else 0 end) TXN_LPB_EQUIP_BRDN_COST,
2801 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.raw_cost*PRJ_MASK else 0 end) PRJ_RAW_COST,
2802 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.brdn_cost*PRJ_MASK else 0 end) PRJ_BRDN_COST,
2803 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.revenue*PRJ_MASK else 0 end) PRJ_REVENUE,
2804 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_raw_cost*PRJ_MASK else 0 end) PRJ_LABOR_RAW_COST,
2805 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_brdn_cost*PRJ_MASK else 0 end) PRJ_LABOR_BRDN_COST,
2806 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_raw_cost*PRJ_MASK else 0 end) PRJ_EQUIP_RAW_COST,
2807 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_brdn_cost*PRJ_MASK else 0 end) PRJ_EQUIP_BRDN_COST,
2808 sum(case when fact.plan_version_id = base_plan_ver_id then fact.raw_cost*PRJ_MASK else 0 end) PRJ_BASE_RAW_COST,
2809 sum(case when fact.plan_version_id = base_plan_ver_id then fact.brdn_cost*PRJ_MASK else 0 end) PRJ_BASE_BRDN_COST,
2810 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_raw_cost*PRJ_MASK else 0 end) PRJ_BASE_LABOR_RAW_COST,
2811 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK else 0 end) PRJ_BASE_LABOR_BRDN_COST,
2812 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK else 0 end) PRJ_BASE_EQUIP_RAW_COST,
2813 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK else 0 end) PRJ_BASE_EQUIP_BRDN_COST,
2814
2815 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.raw_cost*PRJ_MASK else 0 end) PRJ_LPB_RAW_COST,
2816 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.brdn_cost*PRJ_MASK else 0 end) PRJ_LPB_BRDN_COST,
2817 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_raw_cost*PRJ_MASK else 0 end) PRJ_LPB_LABOR_RAW_COST,
2818 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK else 0 end) PRJ_LPB_LABOR_BRDN_COST,
2819 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK else 0 end) PRJ_LBP_EQUIP_RAW_COST,
2820 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK else 0 end) PRJ_LBP_EQUIP_BRDN_COST,
2821
2822 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.raw_cost*POU_MASK else 0 end) POU_RAW_COST,
2823 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.brdn_cost*POU_MASK else 0 end) POU_BRDN_COST,
2824 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.revenue*POU_MASK else 0 end) POU_REVENUE,
2825 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_raw_cost*POU_MASK else 0 end) POU_LABOR_RAW_COST,
2826 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_brdn_cost*POU_MASK else 0 end) POU_LABOR_BRDN_COST,
2827 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_raw_cost*POU_MASK else 0 end) POU_EQUIP_RAW_COST,
2828 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_brdn_cost*POU_MASK else 0 end) POU_EQUIP_BRDN_COST,
2829
2830 sum(case when fact.plan_version_id = base_plan_ver_id then fact.raw_cost*POU_MASK else 0 end) POU_BASE_RAW_COST,
2831 sum(case when fact.plan_version_id = base_plan_ver_id then fact.brdn_cost*POU_MASK else 0 end) POU_BASE_BRDN_COST,
2832 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_raw_cost*POU_MASK else 0 end) POU_BASE_LABOR_RAW_COST,
2833 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_brdn_cost*POU_MASK else 0 end) POU_BASE_LABOR_BRDN_COST,
2834 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_raw_cost*POU_MASK else 0 end) POU_BASE_EQUIP_RAW_COST,
2835 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_brdn_cost*POU_MASK else 0 end) POU_BASE_EQUIP_BRDN_COST,
2836
2837 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.raw_cost*POU_MASK else 0 end) POU_LPB_RAW_COST,
2838 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.brdn_cost*POU_MASK else 0 end) POU_LPB_BRDN_COST,
2839 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_raw_cost*POU_MASK else 0 end) POU_LPB_LABOR_RAW_COST,
2840 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_brdn_cost*POU_MASK else 0 end) POU_LPB_LABOR_BRDN_COST,
2841 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_raw_cost*POU_MASK else 0 end) POU_LPB_EQUIP_RAW_COST,
2842 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_brdn_cost*POU_MASK else 0 end) POU_LPB_EQUIP_BRDN_COST,
2843
2844 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.LABOR_HRS else 0 end) LABOR_HOURS,
2845 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.EQUIPMENT_HOURS else 0 end) EQUIPMENT_HOURS,
2846 sum(case when fact.plan_version_id = base_plan_ver_id then fact.LABOR_HRS else 0 end) BASE_LABOR_HOURS,
2847 sum(case when fact.plan_version_id = base_plan_ver_id then fact.EQUIPMENT_HOURS else 0 end) BASE_EQUIP_HOURS,
2848 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.LABOR_HRS else 0 end) LPB_LABOR_HOURS,
2849 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.EQUIPMENT_HOURS else 0 end) LPB_EQUIP_HOURS,
2850
2851 sum( case when fact.time_id <= END_PERIOD_ID
2852 then
2853 decode( fact.plan_version_id, WK_PLAN_VER_ID, fact.ACT_LABOR_HRS, 0)
2854 else
2855 NULL
2856 end
2857 ) ACT_LABOR_HRS,
2858 sum( case when fact.time_id <= END_PERIOD_ID
2859 then
2860 decode(fact.plan_version_id, WK_PLAN_VER_ID, fact.ACT_EQUIP_HRS, 0)
2861 else
2862 NULL
2863 end
2864 ) ACT_EQUIP_HRS,
2865 sum( case when fact.time_id <= END_PERIOD_ID
2866 then
2867 decode( fact.plan_version_id, WK_PLAN_VER_ID, TXN_MASK* fact.act_labor_brdn_cost, 0 )
2868 else
2869 NULL
2870 end
2871 ) ACT_TXN_LABOR_BRDN_COST,
2872 sum( case when fact.time_id <= END_PERIOD_ID
2873 then
2874 decode(fact.plan_version_id, WK_PLAN_VER_ID, TXN_MASK* fact.act_equip_brdn_cost, 0 )
2875 else
2876 NULL
2877 end
2878 ) ACT_TXN_EQUIP_BRDN_COST,
2879 sum( case when fact.time_id <= END_PERIOD_ID
2880 then
2881 decode(fact.plan_version_id, WK_PLAN_VER_ID, TXN_MASK* fact.act_raw_cost, 0 )
2882 else
2883 NULL
2884 end
2885 ) ACT_TXN_RAW_COST,
2886 sum( case when fact.time_id <= END_PERIOD_ID
2887 then
2888 decode(fact.plan_version_id, WK_PLAN_VER_ID, TXN_MASK* fact.act_brdn_cost, 0 )
2889 else
2890 NULL
2891 end
2892 ) ACT_TXN_BRDN_COST,
2893 sum( case when fact.time_id <= END_PERIOD_ID
2894 then
2895 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK* fact.act_labor_brdn_cost, 0)
2896 else
2897 NULL
2898 end
2899 ) ACT_PRJ_LABOR_BRDN_COST,
2900 sum( case when fact.time_id <= END_PERIOD_ID
2901 then
2902 decode( fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK* fact.act_equip_brdn_cost, 0)
2903 else
2904 NULL
2905 end
2906 ) ACT_PRJ_EQUIP_BRDN_COST,
2907 sum( case when fact.time_id <= END_PERIOD_ID
2908 then
2909 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK* fact.act_raw_cost, 0 )
2910 else
2911 NULL
2912 end
2913 ) ACT_PRJ_RAW_COST,
2914 sum( case when fact.time_id <= END_PERIOD_ID
2915 then
2916 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK* fact.act_brdn_cost, 0 )
2917 else
2918 NULL
2919 end
2920 ) ACT_PRJ_BRDN_COST,
2921 sum( case when fact.time_id <= END_PERIOD_ID
2922 then
2923 decode(fact.plan_version_id, WK_PLAN_VER_ID, POU_MASK* fact.act_labor_brdn_cost, 0 )
2924 else
2925 NULL
2926 end
2927 ) ACT_POU_LABOR_BRDN_COST,
2928 sum( case when fact.time_id <= END_PERIOD_ID
2929 then
2930 decode(fact.plan_version_id, WK_PLAN_VER_ID, POU_MASK* fact.act_equip_brdn_cost, 0 )
2931 else
2932 NULL
2933 end
2934 ) ACT_POU_EQUIP_BRDN_COST,
2935 sum( case when fact.time_id <= END_PERIOD_ID
2936 then
2937 decode(fact.plan_version_id, WK_PLAN_VER_ID, POU_MASK* fact.act_raw_cost, 0 )
2938 else
2939 NULL
2940 end
2941 ) ACT_POU_RAW_COST,
2942 sum( case when fact.time_id <= END_PERIOD_ID
2943 then
2944 decode(fact.plan_version_id, WK_PLAN_VER_ID, POU_MASK* fact.act_brdn_cost, 0 )
2945 else
2946 NULL
2947 end
2948 ) ACT_POU_BRDN_COST,
2949
2950 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_LABOR_HRS else 0 end) ETC_LABOR_HRS,
2951 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_EQUIP_HRS else 0 end) ETC_EQUIP_HRS,
2952 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_brdn_cost*TXN_MASK else 0 end) ETC_TXN_LABOR_BRDN_COST,
2953 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_brdn_cost*TXN_MASK else 0 end) ETC_TXN_EQUIP_BRDN_COST,
2954 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_raw_cost*TXN_MASK else 0 end) ETC_TXN_RAW_COST,
2955 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_brdn_cost*TXN_MASK else 0 end) ETC_TXN_BRDN_COST,
2956 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_brdn_cost*PRJ_MASK else 0 end) ETC_PRJ_LABOR_BRDN_COST,
2957 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_brdn_cost*PRJ_MASK else 0 end) ETC_PRJ_EQUIP_BRDN_COST,
2958 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_raw_cost*PRJ_MASK else 0 end) ETC_PRJ_RAW_COST,
2959 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_brdn_cost*PRJ_MASK else 0 end) ETC_PRJ_BRDN_COST,
2960 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_brdn_cost*POU_MASK else 0 end) ETC_POU_LABOR_BRDN_COST,
2961 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_brdn_cost*POU_MASK else 0 end) ETC_POU_EQUIP_BRDN_COST,
2962 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_raw_cost*POU_MASK else 0 end) ETC_POU_RAW_COST,
2963 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_brdn_cost*POU_MASK else 0 end) ETC_POU_BRDN_COST,
2964 sum( case when fact.time_id <= END_PERIOD_ID
2965 then
2966 decode(fact.plan_version_id , WK_PLAN_VER_ID, TXN_MASK*fact.act_labor_raw_cost,0)
2967 else
2968 NULL
2969 end
2970 ) ACT_TXN_LABOR_RAW_COST,
2971 sum( case when fact.time_id <= END_PERIOD_ID
2972 then
2973 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK* fact.act_labor_raw_cost,0)
2974 else
2975 NULL
2976 end
2977 ) ACT_PRJ_LABOR_RAW_COST,
2978 sum( case when fact.time_id <= END_PERIOD_ID
2979 then
2980 decode( fact.plan_version_id, WK_PLAN_VER_ID, POU_MASK* fact.act_labor_raw_cost,0)
2981 else
2982 NULL
2983 end
2984 ) ACT_POU_LABOR_RAW_COST,
2985 sum( case when fact.time_id <= END_PERIOD_ID
2986 then
2987 decode(fact.plan_version_id, WK_PLAN_VER_ID, TXN_MASK*fact.act_equip_raw_cost,0)
2988 else
2989 NULL
2990 end
2991 ) ACT_TXN_EQUIP_RAW_COST,
2992 sum( case when fact.time_id <= END_PERIOD_ID
2993 then
2994 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK* fact.act_equip_raw_cost,0)
2995 else
2996 NULL
2997 end
2998 ) ACT_PRJ_EQUIP_RAW_COST,
2999 sum( case when fact.time_id <= END_PERIOD_ID
3000 then
3001 decode( fact.plan_version_id, WK_PLAN_VER_ID, POU_MASK* fact.act_equip_raw_cost,0)
3002 else
3003 NULL
3004 end
3005 ) ACT_POU_EQUIP_RAW_COST,
3006
3007 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_raw_cost*TXN_MASK else 0 end) ETC_TXN_LABOR_RAW_COST,
3008 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_raw_cost*PRJ_MASK else 0 end) ETC_PRJ_LABOR_RAW_COST,
3009 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_raw_cost*POU_MASK else 0 end) ETC_POU_LABOR_RAW_COST,
3010 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_raw_cost*TXN_MASK else 0 end) ETC_TXN_EQUIP_RAW_COST,
3011 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_raw_cost*PRJ_MASK else 0 end) ETC_PRJ_EQUIP_RAW_COST,
3012 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_raw_cost*POU_MASK else 0 end) ETC_POU_EQUIP_RAW_COST,
3013 /* Retrival of Project Level Data Starts*/
3014 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_RAW_COST,
3015 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BRDN_COST,
3016 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.revenue*PRJ_MASK*ROLLUP_MASK else 0 end) P_REVENUE,
3017 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LBR_RAW_COST,
3018 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LBR_BRDN_COST,
3019 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_EQP_RAW_COST,
3020 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.equipment_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_EQP_BRDN_COST,
3021 sum(case when fact.plan_version_id = base_plan_ver_id then fact.raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_RAW_COST,
3022 sum(case when fact.plan_version_id = base_plan_ver_id then fact.brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_BRDN_COST,
3023 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_LBR_RAW_COST,
3024 sum(case when fact.plan_version_id = base_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_LBR_BRDN_COST,
3025 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_EQP_RAW_COST,
3026 sum(case when fact.plan_version_id = base_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_BASE_EQP_BRDN_COST,
3027 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_RAW_COST,
3028 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_BRDN_COST,
3029 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_LBR_RAW_COST,
3030 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_LBR_BRDN_COST,
3031 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_EQP_RAW_COST,
3032 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.equipment_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_LPB_EQP_BRDN_COST,
3033 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.LABOR_HRS*ROLLUP_MASK else 0 end) P_LBR_HOURS,
3034 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.EQUIPMENT_HOURS*ROLLUP_MASK else 0 end) P_EQP_HOURS,
3035 sum(case when fact.plan_version_id = base_plan_ver_id then fact.LABOR_HRS*ROLLUP_MASK else 0 end) P_BASE_LBR_HOURS,
3036 sum(case when fact.plan_version_id = base_plan_ver_id then fact.EQUIPMENT_HOURS*ROLLUP_MASK else 0 end) P_BASE_EQP_HOURS,
3037 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.LABOR_HRS*ROLLUP_MASK else 0 end) P_LPB_LBR_HOURS,
3038 sum(case when fact.plan_version_id = lpb_plan_ver_id then fact.EQUIPMENT_HOURS*ROLLUP_MASK else 0 end) P_LPB_EQP_HOURS,
3039
3040
3041 sum( case when fact.time_id <= END_PERIOD_ID
3042 then
3043 decode(fact.plan_version_id, WK_PLAN_VER_ID, ROLLUP_MASK*fact.ACT_LABOR_HRS, 0)
3044 else
3045 NULL
3046 end
3047 ) P_ACT_LBR_HOURS,
3048 sum( case when fact.time_id <= END_PERIOD_ID
3049 then
3050 decode(fact.plan_version_id, WK_PLAN_VER_ID, ROLLUP_MASK*fact.ACT_EQUIP_HRS, 0)
3051 else
3052 NULL
3053 end
3054 ) P_ACT_EQP_HOURS,
3055 sum( case when fact.time_id <= END_PERIOD_ID
3056 then
3057 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK*ROLLUP_MASK* fact.act_labor_brdn_cost, 0)
3058 else
3059 NULL
3060 end
3061 ) P_ACT_LBR_BRDN_COST,
3062 sum( case when fact.time_id <= END_PERIOD_ID
3063 then
3064 decode( fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK*ROLLUP_MASK* fact.act_equip_brdn_cost, 0)
3065 else
3066 NULL
3067 end
3068 ) P_ACT_EQP_BRDN_COST,
3069 sum( case when fact.time_id <= END_PERIOD_ID
3070 then
3071 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK*ROLLUP_MASK* fact.act_raw_cost , 0 )
3072 else
3073 NULL
3074 end
3075 ) P_ACT_RAW_COST,
3076 sum( case when fact.time_id <= END_PERIOD_ID
3077 then
3078 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK*ROLLUP_MASK* fact.act_brdn_cost, 0 )
3079 else
3080 NULL
3081 end
3082 ) P_ACT_BRDN_COST,
3083 sum( case when fact.time_id <= END_PERIOD_ID
3084 then
3085 decode( fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK*ROLLUP_MASK* fact.act_labor_raw_cost,0)
3086 else
3087 NULL
3088 end
3089 ) P_ACT_LBR_RAW_COST,
3090 sum( case when fact.time_id <= END_PERIOD_ID
3091 then
3092 decode(fact.plan_version_id, WK_PLAN_VER_ID, PRJ_MASK*ROLLUP_MASK* fact.act_equip_raw_cost ,0)
3093 else
3094 NULL
3095 end
3096 ) P_ACT_EQP_RAW_COST,
3097
3098 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_EQUIP_HRS*ROLLUP_MASK else 0 end) P_ETC_EQP_HOURS,
3099 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_LABOR_HRS*ROLLUP_MASK else 0 end) P_ETC_LBR_HOURS,
3100 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_RAW_COST,
3101 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_BRDN_COST,
3102 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_LBR_BRDN_COST,
3103 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_brdn_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_EQP_BRDN_COST,
3104 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_labor_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_LBR_RAW_COST,
3105 sum(case when fact.plan_version_id = WK_PLAN_VER_ID then fact.ETC_equip_raw_cost*PRJ_MASK*ROLLUP_MASK else 0 end) P_ETC_EQP_RAW_COST
3106 /* Retrival of Project Level Data Ends*/
3107 FROM
3108 (
3109 SELECT
3110 PROJECT_ID ,
3111 PROJECT_ORG_ID ,
3112 PROJECT_ORGANIZATION_ID ,
3113 PROJECT_ELEMENT_ID ,
3114 TIME_ID ,
3115 PERIOD_TYPE_ID ,
3116 CALENDAR_TYPE ,
3117 RBS_AGGR_LEVEL ,
3118 WBS_ROLLUP_FLAG ,
3119 PRG_ROLLUP_FLAG ,
3120 decode ( cc_src.curr_type, 'TXN', 16, 'PRJ', 8, 'POU', 4) CURR_RECORD_TYPE_ID ,
3121 CURRENCY_CODE ,
3122 RBS_ELEMENT_ID ,
3123 RBS_VERSION_ID ,
3124 PLAN_VERSION_ID ,
3125 -- PLAN_TYPE_ID ,
3126 RAW_COST ,
3127 BRDN_COST ,
3128 REVENUE ,
3129 BILL_RAW_COST ,
3130 BILL_BRDN_COST ,
3131 BILL_LABOR_RAW_COST ,
3132 BILL_LABOR_BRDN_COST ,
3133 decode ( cc_src.curr_type, 'PRJ', BILL_LABOR_HRS, 0) BILL_LABOR_HRS ,
3134 EQUIPMENT_RAW_COST ,
3135 EQUIPMENT_BRDN_COST ,
3136 CAPITALIZABLE_RAW_COST ,
3137 CAPITALIZABLE_BRDN_COST ,
3138 LABOR_RAW_COST ,
3139 LABOR_BRDN_COST ,
3140 decode ( cc_src.curr_type, 'PRJ', LABOR_HRS, 0) LABOR_HRS ,
3141 LABOR_REVENUE ,
3142 decode ( cc_src.curr_type, 'PRJ', EQUIPMENT_HOURS, 0) EQUIPMENT_HOURS ,
3143 decode ( cc_src.curr_type, 'PRJ', BILLABLE_EQUIPMENT_HOURS, 0) BILLABLE_EQUIPMENT_HOURS ,
3144 SUP_INV_COMMITTED_COST ,
3145 PO_COMMITTED_COST ,
3146 PR_COMMITTED_COST ,
3147 OTH_COMMITTED_COST ,
3148 CUSTOM1 ,
3149 CUSTOM2 ,
3150 CUSTOM3 ,
3151 CUSTOM4 ,
3152 CUSTOM5 ,
3153 CUSTOM6 ,
3154 CUSTOM7 ,
3155 CUSTOM8 ,
3156 CUSTOM9 ,
3157 CUSTOM10 ,
3158 CUSTOM11 ,
3159 CUSTOM12 ,
3160 CUSTOM13 ,
3161 CUSTOM14 ,
3162 CUSTOM15 ,
3163 decode ( cc_src.curr_type, 'PRJ', ACT_LABOR_HRS, 0) ACT_LABOR_HRS ,
3164 decode ( cc_src.curr_type, 'PRJ', ACT_EQUIP_HRS, 0) ACT_EQUIP_HRS ,
3165 ACT_LABOR_BRDN_COST ,
3166 ACT_EQUIP_BRDN_COST ,
3167 ACT_BRDN_COST ,
3168 decode ( cc_src.curr_type, 'PRJ', ETC_LABOR_HRS, 0) ETC_LABOR_HRS ,
3169 decode ( cc_src.curr_type, 'PRJ', ETC_EQUIP_HRS, 0) ETC_EQUIP_HRS ,
3170 ETC_LABOR_BRDN_COST ,
3171 ETC_EQUIP_BRDN_COST ,
3172 ETC_BRDN_COST ,
3173 ACT_RAW_COST ,
3174 ACT_REVENUE ,
3175 ETC_RAW_COST ,
3176 ACT_LABOR_RAW_COST ,
3177 ACT_EQUIP_RAW_COST ,
3178 ETC_LABOR_RAW_COST ,
3179 ETC_EQUIP_RAW_COST ,
3180 decode(fact.prg_rollup_flag,'N',1,0) ROLLUP_MASK,
3181 decode ( cc_src.curr_type, 'TXN',1,0) TXN_MASK,
3182 decode ( cc_src.curr_type, 'PRJ',1,0) PRJ_MASK,
3183 decode ( cc_src.curr_type, 'POU',1,0) POU_MASK
3184 FROM
3185 pji_fp_xbs_accum_f fact,
3186 (
3187 SELECT 'TXN' curr_type FROM DUAL
3188 UNION ALL
3189 SELECT 'PRJ' curr_type FROM DUAL
3190 UNION ALL
3191 SELECT 'POU' curr_type FROM DUAL
3192 ) cc_src
3193 WHERE 1=1
3194 and ( decode ( cc_src.curr_type, 'TXN', DECODE(BITAND(fact.curr_record_type_id, 16), 16, 'a'), 'b') = 'a'
3195 or decode ( cc_src.curr_type, 'PRJ', DECODE(BITAND(fact.curr_record_type_id , 8), 8, 'a'), 'b') = 'a'
3196 or decode ( cc_src.curr_type, 'POU', DECODE(BITAND(fact.curr_record_type_id, 4), 4, 'a'), 'b') = 'a' )
3197 ) fact,
3198 pji_plan_extr_tmp head
3199 WHERE 1=1
3200 and fact.PROJECT_ID = head.PROJECT_ID
3201 and fact.PLAN_VERSION_ID in (head.WK_PLAN_VER_ID , head.BASE_PLAN_VER_ID,
3202 head.LPB_PLAN_VER_ID )
3203 and fact.PROJECT_ELEMENT_ID = nvl(head.PROJ_ELEM_ID, fact.PROJECT_ELEMENT_ID )
3204 and fact.CALENDAR_TYPE = CAL_TYPE
3205 and fact.PERIOD_TYPE_ID = PERIOD_ID
3206 and BITAND(fact.CURR_RECORD_TYPE_ID,28) <= 28
3207 and BITAND(fact.CURR_RECORD_TYPE_ID,28) >= 4
3208 and fact.RBS_AGGR_LEVEL = 'T'
3209 and fact.prg_rollup_flag in (p_program_rollup_flag,'N')
3210 GROUP BY
3211 fact.PROJECT_ID,
3212 fact.PROJECT_ELEMENT_ID,
3213 fact.CALENDAR_TYPE,
3214 head.WK_PLAN_VER_ID,
3215 head.STRUCT_VER_ID;
3216
3217 PRINT_TIME ( ' populate_workplan_data 0003.12 '||SQL%ROWCOUNT ) ;
3218
3219 IF g_debug_mode='Y' THEN
3220 debug_accum ; /* bug#3993830 */
3221 END IF;
3222
3223
3224
3225
3226 DELETE FROM PJI_PLAN_EXTR_TMP;
3227
3228 x_return_status := l_return_status;
3229
3230 PRINT_TIME ( ' populate_workplan_data 0003.13 ' ) ;
3231
3232 EXCEPTION
3233 WHEN OTHERS THEN
3234
3235 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
3236 ( p_package_name => g_package_name
3237 , p_procedure_name => 'POPULATE_WORKPLAN_DATA'
3238 , x_return_status => x_return_status ) ;
3239
3240 RAISE;
3241 END;
3242
3243
3244 PROCEDURE FPM_UPGRADE_INITIALIZE IS
3245
3246 c_upgr_proc_name VARCHAR2(30) := 'PJI_FPM_UPGRADE';
3247 l_worker_id NUMBER;
3248 l_process VARCHAR2(30);
3249 l_extr_start_date DATE;
3250 l_pa_period_flag VARCHAR2(10);
3251 l_gl_period_flag VARCHAR2(10);
3252 l_return_status VARCHAR2(100);
3253
3254 BEGIN
3255
3256 PJI_PJP_FP_CURR_WRAP.INIT_ERR_STACK
3257 ( p_package_name => g_package_name
3258 , x_return_status => l_return_status );
3259
3260 l_worker_id := 1;
3261 l_process := PJI_PJP_SUM_MAIN.g_process || l_worker_id;
3262 l_extr_start_date := PJI_UTILS.GET_EXTRACTION_START_DATE; PRINT_TIME ( ' FPM_UPGRADE_INITIALIZE 001 ' ) ;
3263
3264 Pji_utils.set_parameter(c_upgr_proc_name, 'P'); -- table pji_system_parameters
3265 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE', 'FULL'); -- table pji_system_parameters
3266 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process, 'PROCESS_RUNNING', 'Y'); -- table pji_system_parameters
3267 PJI_PJP_EXTRACTION_UTILS.SET_WORKER_ID(l_worker_id); -- Private global pkg var: PJI_PJP_EXTRACTION_UTILS.g_worker_id.
3268
3269 PRINT_TIME ( ' FPM_UPGRADE_INITIALIZE 002 ' ) ;
3270
3271
3272 insert into PJI_SYSTEM_CONFIG_HIST
3273 (
3274 REQUEST_ID,
3275 USER_NAME,
3276 PROCESS_NAME,
3277 RUN_TYPE,
3278 PARAMETERS,
3279 CONFIG_PROJ_PERF_FLAG,
3280 CONFIG_COST_FLAG,
3281 CONFIG_PROFIT_FLAG,
3282 CONFIG_UTIL_FLAG,
3283 START_DATE,
3284 END_DATE,
3285 COMPLETION_TEXT
3286 )
3287 select
3288 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
3289 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
3290 l_process PROCESS_NAME,
3291 'FPM_UPGRADE' RUN_TYPE,
3292 null PARAMETERS,
3293 null CONFIG_PROJ_PERF_FLAG,
3294 null CONFIG_COST_FLAG,
3295 null CONFIG_PROFIT_FLAG,
3296 null CONFIG_UTIL_FLAG,
3297 sysdate START_DATE,
3298 null END_DATE,
3299 null COMPLETION_TEXT
3300 from
3301 dual;
3302
3303 insert into PJI_PJP_PROJ_BATCH_MAP
3304 (
3305 WORKER_ID,
3306 PROJECT_ID,
3307 PJI_PROJECT_STATUS,
3308 EXTRACTION_TYPE,
3309 EXTRACTION_STATUS,
3310 PROJECT_TYPE,
3311 PROJECT_ORG_ID,
3312 PROJECT_ORGANIZATION_ID,
3313 PROJECT_TYPE_CLASS,
3314 PRJ_CURRENCY_CODE,
3315 PROJECT_ACTIVE_FLAG
3316 )
3317 select
3318 l_worker_id,
3319 prj.PROJECT_ID,
3320 null,
3321 null,
3322 'F',
3323 prj.PROJECT_TYPE,
3324 prj.ORG_ID,
3325 prj.CARRYING_OUT_ORGANIZATION_ID,
3326 decode(pt.PROJECT_TYPE_CLASS_CODE,
3327 'CAPITAL', 'C',
3328 'CONTRACT', 'B',
3329 'INDIRECT', 'I'),
3330 prj.PROJECT_CURRENCY_CODE,
3331 null
3332 from
3333 PA_PROJECTS_ALL prj,
3334 PA_PROJECT_TYPES_ALL pt
3335 where
3336 -- We cannot depend on extraction start date as it will not be
3337 -- set at the time of upgrade.
3338 nvl(prj.CLOSED_DATE, nvl(l_extr_start_date, to_date(1, 'J')))
3339 >= nvl(l_extr_start_date, to_date(1, 'J')) and
3340 prj.ORG_ID = pt.ORG_ID and
3341 prj.PROJECT_TYPE = pt.PROJECT_TYPE and
3342 prj.PROJECT_ID in (select ver.PROJECT_ID
3343 from PA_BUDGET_VERSIONS ver
3344 where ver.BUDGET_TYPE_CODE is null);
3345
3346 PRINT_TIME ( ' FPM_UPGRADE_INITIALIZE 004 ' ) ;
3347
3348 SELECT PJI_UTILS.GET_SETUP_PARAMETER('PA_PERIOD_FLAG') , PJI_UTILS.GET_SETUP_PARAMETER('GL_PERIOD_FLAG')
3349 INTO l_pa_period_flag, l_gl_period_flag
3350 FROM DUAL;
3351
3352 if (l_pa_period_flag = 'N') then
3353 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3354 'PA_CALENDAR_FLAG',
3355 'N');
3356 else
3357 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3358 'PA_CALENDAR_FLAG',
3359 'Y');
3360 end if;
3361
3362 if (l_gl_period_flag = 'N') then
3363 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3364 'GL_CALENDAR_FLAG',
3365 'N');
3366 else
3367 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
3368 'GL_CALENDAR_FLAG',
3369 'Y');
3370 end if; PRINT_TIME ( ' FPM_UPGRADE_INITIALIZE 004 ' ) ;
3371
3372 --
3373 -- 4682341
3374 -- Parameter 'EXTRACT_ETC_FULLLOAD' has been added to check whether
3375 -- etc (due to act or overridden) is not extracted twice after upgrade
3376 -- and initial load.
3377 -- Assumption: After fpm upgrade, an initial load is run for *All* projects
3378 -- before new actuals are entered in the system.
3379 -- Use: If the value of this param is 'Y', then etc from get plan res actuals will
3380 -- be extracted during FULL sumz.
3381
3382 DELETE FROM pji_system_parameters
3383 WHERE name = 'EXTRACT_ETC_FULLLOAD';
3384
3385 INSERT INTO pji_system_parameters ( name, value )
3386 VALUES ( 'EXTRACT_ETC_FULLLOAD' , 'N' );
3387
3388 commit;
3389
3390 EXCEPTION
3391 WHEN OTHERS THEN
3392
3393 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
3394 ( p_package_name => g_package_name
3395 , p_procedure_name => 'FPM_UPGRADE_INITIALIZE'
3396 , x_return_status => l_return_status ) ;
3397
3398 RAISE;
3399 END;
3400
3401
3402 PROCEDURE FPM_UPGRADE_END IS
3403
3404 l_worker_id NUMBER;
3405 l_process VARCHAR2(30);
3406 l_extr_start_date DATE;
3407 l_return_status VARCHAR2(100);
3408 l_sqlerrm VARCHAR2(240);
3409
3410 BEGIN
3411
3412 PJI_PJP_FP_CURR_WRAP.INIT_ERR_STACK
3413 ( p_package_name => g_package_name
3414 , x_return_status => l_return_status );
3415
3416 l_worker_id := 1;
3417 l_process := PJI_PJP_SUM_MAIN.g_process || l_worker_id;
3418 l_extr_start_date := PJI_UTILS.GET_EXTRACTION_START_DATE;
3419
3420 PJI_PJP_SUM_ROLLUP.CLEANUP(l_worker_id);
3421
3422 Pji_utils.set_parameter('PJI_FPM_UPGRADE', 'C');
3423
3424 PJI_PROCESS_UTIL.WRAPUP_PROCESS(l_process);
3425
3426 update PJI_SYSTEM_CONFIG_HIST
3427 set END_DATE = sysdate,
3428 COMPLETION_TEXT = 'Normal completion'
3429 where PROCESS_NAME = l_process and
3430 RUN_TYPE = 'FPM_UPGRADE' and
3431 END_DATE is null;
3432
3433 PJI_UTILS.SET_PARAMETER('PJP_FPM_UPGRADE_DATE',
3434 to_char(sysdate, PJI_PJP_SUM_MAIN.g_date_mask));
3435
3436 DELETE FROM PJI_PJP_PROJ_BATCH_MAP WHERE WORKER_ID = l_worker_id;
3437
3438 commit;
3439
3440 EXCEPTION
3441 WHEN OTHERS THEN
3442
3443 rollback;
3444
3445 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
3446 ( p_package_name => g_package_name
3447 , p_procedure_name => 'FPM_UPGRADE_END'
3448 , x_return_status => l_return_status ) ;
3449
3450 l_sqlerrm := substr(sqlerrm, 1, 240);
3451
3452 update PJI_SYSTEM_CONFIG_HIST
3453 set END_DATE = sysdate,
3454 COMPLETION_TEXT = l_sqlerrm
3455 where RUN_TYPE = 'FPM_UPGRADE' and
3456 END_DATE is null;
3457
3458 commit;
3459
3460 RAISE;
3461 END;
3462
3463
3464 PROCEDURE REMAP_RBS_TXN_ACCUM_HDRS (
3465 x_return_status OUT NOCOPY VARCHAR2
3466 ,x_msg_data OUT NOCOPY VARCHAR2
3467 ,x_msg_count OUT NOCOPY NUMBER ) IS
3468
3469 CURSOR c_current_rbs_versions IS
3470 SELECT prv.RBS_VERSION_ID
3471 FROM pa_rbs_versions_b prv
3472 WHERE 1=1
3473 AND prv.CURRENT_REPORTING_FLAG = 'Y'
3474 AND prv.STATUS_CODE = 'FROZEN';
3475 --AND prv.RBS_VERSION_ID not in ( 10000, 10142, 10224, 10821);
3476
3477 CURSOR c_plan_versions (l_rbs_version_id NUMBER) IS -- This can be combined with the previous cursor.
3478 SELECT bv.budget_version_id, bv.project_id
3479 FROM pa_budget_versions bv
3480 , pa_proj_fp_options fpo
3481 WHERE 1=1
3482 AND bv.budget_version_id = fpo.fin_plan_version_id
3483 AND bv.fin_plan_type_id = fpo.fin_plan_type_id
3484 AND fpo.project_id = bv.project_id
3485 AND bv.version_type is not NULL
3486 AND bv.fin_plan_type_id is not NULL
3487 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION'
3488 AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
3489 AND fpo.RBS_VERSION_ID = l_rbs_version_id;
3490
3491 l_res_list_member_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3492 l_txn_source_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3493 l_rbs_element_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3494 l_txn_accum_header_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3495
3496 BEGIN
3497
3498 x_msg_count := 0;
3499 x_return_status := FND_API.G_RET_STS_SUCCESS;
3500 pa_debug.reset_curr_function;
3501
3502 UPDATE pa_resource_assignments
3503 SET txn_accum_header_id = NULL;
3504
3505 FOR l_curr_rbs_vers IN c_current_rbs_versions LOOP
3506
3507
3508 FOR l_plan_versions in c_plan_versions(l_curr_rbs_vers.RBS_VERSION_ID) LOOP
3509
3510 BEGIN
3511 pa_rlmi_rbs_map_pub.Map_Rlmi_Rbs (
3512 p_budget_version_id => l_plan_versions.budget_version_id
3513 ,p_project_id => l_plan_versions.project_id
3514 ,p_rbs_version_id => l_curr_rbs_vers.RBS_VERSION_ID
3515 ,p_calling_process => 'RBS_REFRESH'
3516 ,p_calling_context => 'SELF_SERVICE'
3517 ,p_process_code => 'RBS_MAP'
3518 ,p_calling_mode => 'BUDGET_VERSION'
3519 ,x_txn_source_id_tab => l_txn_source_id_tbl
3520 ,x_res_list_member_id_tab => l_res_list_member_id_tbl
3521 ,x_rbs_element_id_tab => l_rbs_element_id_tbl
3522 ,x_txn_accum_header_id_tab => l_txn_accum_header_id_tbl
3523 ,x_return_status => x_return_status
3524 ,x_msg_count => x_msg_count
3525 ,x_msg_data => x_msg_data);
3526
3527
3528 FORALL i IN l_txn_source_id_tbl.FIRST..l_txn_source_id_tbl.LAST
3529 UPDATE pa_resource_assignments
3530 SET TXN_ACCUM_HEADER_ID = l_txn_accum_header_id_tbl(i),
3531 RBS_ELEMENT_ID = l_rbs_element_id_tbl(i)
3532 WHERE
3533 RESOURCE_ASSIGNMENT_ID = l_txn_source_id_tbl(i);
3534
3535 EXCEPTION
3536 WHEN OTHERS THEN
3537 null;
3538 END;
3539
3540 COMMIT;
3541
3542 END LOOP;
3543 END LOOP;
3544
3545 EXCEPTION
3546
3547 WHEN OTHERS THEN
3548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3549 x_msg_count := 1;
3550 x_msg_data := SQLERRM;
3551 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
3552 p_procedure_name => 'REMAP_RBS_TXN_ACCUM_HDRS');
3553 RAISE;
3554 END;
3555
3556
3557
3558 ----------
3559 -- Print time API to measure time taken by each api. Also useful for debugging.
3560 ----------
3561 PROCEDURE PRINT_TIME(p_tag IN VARCHAR2) IS
3562 BEGIN
3563 PJI_PJP_FP_CURR_WRAP.print_time(p_tag);
3564 EXCEPTION
3565 WHEN OTHERS THEN
3566 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
3567 p_procedure_name => 'PRINT_TIME');
3568 RAISE;
3569 END;
3570
3571 PROCEDURE get_msp_actuals_data(
3572 p_project_id IN NUMBER,
3573 p_calendar_type IN VARCHAR2,
3574 p_resource_list_id IN NUMBER DEFAULT NULL,
3575 p_task_res_flag IN VARCHAR2,
3576 p_end_date IN DATE,
3577 x_return_status OUT NOCOPY VARCHAR2,
3578 x_msg_code OUT NOCOPY VARCHAR2) IS
3579
3580 l_struct_element_id NUMBER;
3581 l_project_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3582 l_resource_list_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3583 l_struct_ver_id_tab SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3584 l_calendar_type_tab SYSTEM.pa_varchar2_1_tbl_type :=
3585 SYSTEM.pa_varchar2_1_tbl_type();
3586 l_end_date_tab SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
3587
3588 l_populate_in_tbl populate_in_tbl_type ;
3589 l_populate_in_rec populate_in_rec_type;
3590
3591 BEGIN
3592
3593 SELECT wbs_version_id
3594 INTO l_struct_element_id
3595 FROM pji_pjp_wbs_header
3596 WHERE project_id = p_project_id AND plan_version_id=-1;
3597
3598 l_populate_in_rec.project_id := p_project_id;
3599 l_populate_in_rec.struct_ver_id := l_struct_element_id;
3600 l_populate_in_rec.base_struct_ver_id := NULL;
3601 l_populate_in_rec.plan_version_id := NULL;
3602 l_populate_in_rec.as_of_date := p_end_date;
3603 l_populate_in_rec.project_element_id := NULL;
3604 l_populate_in_tbl(1) := l_populate_in_rec;
3605
3606
3607 l_project_id_tab.extend;
3608 l_resource_list_id_tab.extend;
3609 l_struct_ver_id_tab.extend;
3610 l_calendar_type_tab.extend;
3611 l_end_date_tab.extend;
3612
3613
3614 l_project_id_tab(1) := p_project_id;
3615 l_resource_list_id_tab(1) := p_resource_list_id;
3616 l_struct_ver_id_tab(1) := l_struct_element_id;
3617 l_calendar_type_tab(1) := p_calendar_type;
3618 l_end_date_tab(1) := p_end_date;
3619
3620 IF(p_task_res_flag ='R') THEN
3621 get_summarized_data(
3622 p_project_ids => l_project_id_tab,
3623 p_resource_list_ids => l_resource_list_id_tab,
3624 p_struct_ver_ids => l_struct_ver_id_tab,
3625 p_end_date => l_end_date_tab,
3626 p_calendar_type => l_calendar_type_tab,
3627 p_extraction_type => 'FULL',
3628 p_record_type => 'NYYY',
3629 p_currency_type => 4,
3630 x_return_status => x_return_status,
3631 x_msg_code => x_msg_code);
3632 ELSE IF(p_task_res_flag='T') THEN
3633 populate_workplan_data (
3634 p_populate_in_tbl => l_populate_in_tbl,
3635 p_calling_context => 'MSP', -- added for bug 5751250
3636 x_return_status => x_return_status,
3637 x_msg_code => x_msg_code
3638 );
3639 end if;
3640 END IF;
3641 EXCEPTION
3642 WHEN OTHERS THEN
3643 PJI_PJP_FP_CURR_WRAP.EXCP_HANDLER
3644 (p_package_name => g_package_name
3645 , p_procedure_name => 'GET_MSP_ACTUALS_DATA'
3646 , x_return_status => x_return_status ) ;
3647 END;
3648
3649
3650
3651 END PJI_FM_XBS_ACCUM_UTILS;