[Home] [Help]
PACKAGE BODY: APPS.PA_INTEGRATION_UTILS
Source
1 PACKAGE BODY PA_INTEGRATION_UTILS AS
2 /*$Header: PAINTUTB.pls 120.1 2010/06/24 14:12:57 vgovvala noship $*/
3
4
5
6 FUNCTION get_budget_version_id (parent_structure_version_id_in IN number) RETURN number IS
7
8 l_budget_version_id NUMBER := -1;
9
10 BEGIN
11
12 SELECT DISTINCT(budget_version_id) INTO l_budget_version_id FROM pa_budget_versions
13
14 WHERE project_structure_version_id = parent_structure_version_id_in
15
16 AND fin_plan_type_id = 10;
17
18 RETURN l_budget_version_id;
19
20 EXCEPTION WHEN NO_DATA_FOUND THEN /* Return an empty record. */
21 RETURN -1;
22
23 END get_budget_version_id;
24
25
26
27
28 FUNCTION get_bill_rates_all_nextseq RETURN number IS
29
30 bill_rates_all_seq_nextval NUMBER := -1;
31
32 BEGIN
33
34 SELECT pa_bill_rates_all_seq.nextval INTO bill_rates_all_seq_nextval FROM dual;
35
36 RETURN bill_rates_all_seq_nextval;
37
38 EXCEPTION WHEN NO_DATA_FOUND THEN /* Return an empty record. */
39 RETURN -1;
40
41 END get_bill_rates_all_nextseq;
42
43
44
45 PROCEDURE populate_resource_actuals_data (project_id_in IN NUMBER, resource_list_id_in IN NUMBER) IS
46
47 l_return_status VARCHAR2(100);
48 l_msg_code VARCHAR2(100);
49 l_end_date DATE;
50 l_add_days NUMBER;
51
52 BEGIN
53
54 DELETE FROM pji_fm_xbs_accum_int;
55
56 SELECT Trunc(Max(END_DATE))-1 INTO l_end_date FROM pa_periods_all WHERE status <> 'C';
57 SELECT trunc(trunc(Max(END_DATE) - Sysdate - 1)) into l_add_days FROM pa_periods_all WHERE status <> 'C';
58
59 PJI_FM_XBS_ACCUM_UTILS.get_msp_actuals_data
60 (p_project_id => project_id_in,
61 p_calendar_type => 'A',
65 x_return_status => l_return_status,
62 p_resource_list_id => resource_list_id_in,
63 p_task_res_flag => 'R',
64 p_end_date => sysdate + l_add_days,
66 x_msg_code => l_msg_code) ;
67
68 INSERT INTO pji_fm_xbs_accum_int
69 (PROJECT_ID,
70 STRUCT_VERSION_ID,
71 PROJECT_ELEMENT_ID,
72 CALENDAR_TYPE,
73 PERIOD_NAME,
74 PLAN_VERSION_ID,
75 RES_LIST_MEMBER_ID,
76 QUANTITY,
77 TXN_CURRENCY_CODE,
78 TXN_RAW_COST,
79 TXN_BRDN_COST,
80 TXN_REVENUE,
81 TXN_LABOR_RAW_COST,
82 TXN_LABOR_BRDN_COST,
83 TXN_EQUIP_RAW_COST,
84 TXN_EQUIP_BRDN_COST,
85 TXN_BASE_RAW_COST,
86 TXN_BASE_BRDN_COST,
87 TXN_BASE_LABOR_RAW_COST,
88 TXN_BASE_LABOR_BRDN_COST,
89 TXN_BASE_EQUIP_RAW_COST,
90 TXN_BASE_EQUIP_BRDN_COST,
91 PRJ_RAW_COST,
92 PRJ_BRDN_COST,
93 PRJ_REVENUE,
94 PRJ_LABOR_RAW_COST,
95 PRJ_LABOR_BRDN_COST,
96 PRJ_EQUIP_RAW_COST,
97 PRJ_EQUIP_BRDN_COST,
98 PRJ_BASE_RAW_COST,
99 PRJ_BASE_BRDN_COST,
100 PRJ_BASE_LABOR_RAW_COST,
101 PRJ_BASE_LABOR_BRDN_COST,
102 PRJ_BASE_EQUIP_RAW_COST,
103 PRJ_BASE_EQUIP_BRDN_COST,
104 POU_RAW_COST,
105 POU_BRDN_COST,
106 POU_REVENUE,
107 POU_LABOR_RAW_COST,
108 POU_LABOR_BRDN_COST,
109 POU_EQUIP_RAW_COST,
110 POU_EQUIP_BRDN_COST,
111 POU_BASE_RAW_COST,
112 POU_BASE_BRDN_COST,
113 POU_BASE_LABOR_RAW_COST,
114 POU_BASE_LABOR_BRDN_COST,
115 POU_BASE_EQUIP_RAW_COST,
116 POU_BASE_EQUIP_BRDN_COST,
117 LABOR_HOURS,
118 EQUIPMENT_HOURS,
119 BASE_LABOR_HOURS,
120 BASE_EQUIP_HOURS,
121 SOURCE_ID,
122 ACT_LABOR_HRS,
123 ACT_EQUIP_HRS,
124 ACT_TXN_LABOR_BRDN_COST,
125 ACT_TXN_EQUIP_BRDN_COST,
126 ACT_TXN_BRDN_COST,
127 ACT_PRJ_LABOR_BRDN_COST,
128 ACT_PRJ_EQUIP_BRDN_COST,
129 ACT_PRJ_BRDN_COST,
130 ACT_PFC_LABOR_BRDN_COST,
131 ACT_PFC_EQUIP_BRDN_COST,
132 ACT_PFC_BRDN_COST,
133 ETC_LABOR_HRS,
134 ETC_EQUIP_HRS,
135 ETC_TXNLABOR_BRDN_COST,
136 ETC_TXN_EQUIP_BRDN_COST,
137 ETC_TXN_BRDN_COST,
138 ETC_PRJ_LABOR_BRDN_COST,
139 ETC_PRJ_EQUIP_BRDN_COST,
140 ETC_PRJ_BRDN_COST,
141 ETC_POU_LABOR_BRDN_COST,
142 ETC_POU_EQUIP_BRDN_COST,
143 ETC_POU_BRDN_COST,
144 ACT_TXN_RAW_COST,
145 ACT_PRJ_RAW_COST,
146 ACT_POU_RAW_COST,
147 ETC_TXN_RAW_COST,
148 ETC_PRJ_RAW_COST,
149 ETC_POU_RAW_COST,
150 ACT_TXN_LABOR_RAW_COST,
151 ACT_TXN_EQUIP_RAW_COST,
152 ACT_PRJ_LABOR_RAW_COST,
153 ACT_PRJ_EQUIP_RAW_COST,
154 ACT_POU_LABOR_RAW_COST,
155 ACT_POU_EQUIP_RAW_COST,
156 ETC_TXN_LABOR_RAW_COST,
157 ETC_TXN_EQUIP_RAW_COST,
158 ETC_PRJ_LABOR_RAW_COST,
159 ETC_PRJ_EQUIP_RAW_COST,
160 ETC_POU_LABOR_RAW_COST,
161 ETC_POU_EQUIP_RAW_COST,
162 ACT_POU_LABOR_BRDN_COST,
163 ACT_POU_EQUIP_BRDN_COST,
164 ACT_POU_BRDN_COST,
165 ETC_TXN_LABOR_BRDN_COST,
166 TXN_LPB_RAW_COST,
167 TXN_LPB_BRDN_COST,
168 TXN_LPB_LABOR_RAW_COST,
169 TXN_LPB_LABOR_BRDN_COST,
170 TXN_LPB_EQUIP_RAW_COST,
171 TXN_LPB_EQUIP_BRDN_COST,
172 PRJ_LPB_RAW_COST,
173 PRJ_LPB_BRDN_COST,
174 PRJ_LPB_LABOR_RAW_COST,
175 PRJ_LPB_LABOR_BRDN_COST,
176 PRJ_LPB_EQUIP_RAW_COST,
177 PRJ_LPB_EQUIP_BRDN_COST,
178 POU_LPB_RAW_COST,
179 POU_LPB_BRDN_COST,
180 POU_LPB_LABOR_RAW_COST,
181 POU_LPB_LABOR_BRDN_COST,
182 POU_LPB_EQUIP_RAW_COST,
183 POU_LPB_EQUIP_BRDN_COST,
184 LPB_LABOR_HOURS,
185 LPB_EQUIP_HOURS,
186 PERIOD_FLAG,
187 MIN_START_DATE,
188 MAX_END_DATE,
189 P_RAW_COST,
190 P_BRDN_COST,
191 P_REVENUE,
192 P_LBR_RAW_COST,
193 P_LBR_BRDN_COST,
194 P_EQP_RAW_COST,
195 P_EQP_BRDN_COST,
196 P_BASE_RAW_COST,
197 P_BASE_BRDN_COST,
198 P_BASE_LBR_RAW_COST,
199 P_BASE_LBR_BRDN_COST,
200 P_BASE_EQP_RAW_COST,
201 P_BASE_EQP_BRDN_COST,
202 P_LPB_RAW_COST,
203 P_LPB_BRDN_COST,
204 P_LPB_LBR_RAW_COST,
205 P_LPB_LBR_BRDN_COST,
206 P_LPB_EQP_RAW_COST,
207 P_LPB_EQP_BRDN_COST,
208 P_LBR_HOURS,
209 P_EQP_HOURS,
210 P_BASE_LBR_HOURS,
211 P_BASE_EQP_HOURS,
212 P_LPB_LBR_HOURS,
213 P_LPB_EQP_HOURS,
214 P_ACT_LBR_HOURS,
215 P_ACT_EQP_HOURS,
216 P_ACT_LBR_BRDN_COST,
217 P_ACT_EQP_BRDN_COST,
218 P_ACT_RAW_COST,
219 P_ACT_BRDN_COST,
220 P_ACT_LBR_RAW_COST,
221 P_ACT_EQP_RAW_COST,
222 P_ETC_EQP_HOURS,
223 P_ETC_LBR_HOURS,
224 P_ETC_RAW_COST,
225 P_ETC_BRDN_COST,
226 P_ETC_LBR_BRDN_COST,
227 P_ETC_EQP_BRDN_COST,
228 P_ETC_LBR_RAW_COST,
229 P_ETC_EQP_RAW_COST)
230 SELECT PROJECT_ID,
231 STRUCT_VERSION_ID,
232 PROJECT_ELEMENT_ID,
233 CALENDAR_TYPE,
234 PERIOD_NAME,
235 PLAN_VERSION_ID,
236 RES_LIST_MEMBER_ID,
237 QUANTITY,
238 TXN_CURRENCY_CODE,
239 TXN_RAW_COST,
240 TXN_BRDN_COST,
241 TXN_REVENUE,
242 TXN_LABOR_RAW_COST,
243 TXN_LABOR_BRDN_COST,
244 TXN_EQUIP_RAW_COST,
245 TXN_EQUIP_BRDN_COST,
246 TXN_BASE_RAW_COST,
247 TXN_BASE_BRDN_COST,
248 TXN_BASE_LABOR_RAW_COST,
249 TXN_BASE_LABOR_BRDN_COST,
250 TXN_BASE_EQUIP_RAW_COST,
251 TXN_BASE_EQUIP_BRDN_COST,
252 PRJ_RAW_COST,
253 PRJ_BRDN_COST,
254 PRJ_REVENUE,
255 PRJ_LABOR_RAW_COST,
256 PRJ_LABOR_BRDN_COST,
257 PRJ_EQUIP_RAW_COST,
258 PRJ_EQUIP_BRDN_COST,
262 PRJ_BASE_LABOR_BRDN_COST,
259 PRJ_BASE_RAW_COST,
260 PRJ_BASE_BRDN_COST,
261 PRJ_BASE_LABOR_RAW_COST,
263 PRJ_BASE_EQUIP_RAW_COST,
264 PRJ_BASE_EQUIP_BRDN_COST,
265 POU_RAW_COST,
266 POU_BRDN_COST,
267 POU_REVENUE,
268 POU_LABOR_RAW_COST,
269 POU_LABOR_BRDN_COST,
270 POU_EQUIP_RAW_COST,
271 POU_EQUIP_BRDN_COST,
272 POU_BASE_RAW_COST,
273 POU_BASE_BRDN_COST,
274 POU_BASE_LABOR_RAW_COST,
275 POU_BASE_LABOR_BRDN_COST,
276 POU_BASE_EQUIP_RAW_COST,
277 POU_BASE_EQUIP_BRDN_COST,
278 LABOR_HOURS,
279 EQUIPMENT_HOURS,
280 BASE_LABOR_HOURS,
281 BASE_EQUIP_HOURS,
282 SOURCE_ID,
283 ACT_LABOR_HRS,
284 ACT_EQUIP_HRS,
285 ACT_TXN_LABOR_BRDN_COST,
286 ACT_TXN_EQUIP_BRDN_COST,
287 ACT_TXN_BRDN_COST,
288 ACT_PRJ_LABOR_BRDN_COST,
289 ACT_PRJ_EQUIP_BRDN_COST,
290 ACT_PRJ_BRDN_COST,
291 ACT_PFC_LABOR_BRDN_COST,
292 ACT_PFC_EQUIP_BRDN_COST,
293 ACT_PFC_BRDN_COST,
294 ETC_LABOR_HRS,
295 ETC_EQUIP_HRS,
296 ETC_TXNLABOR_BRDN_COST,
297 ETC_TXN_EQUIP_BRDN_COST,
298 ETC_TXN_BRDN_COST,
299 ETC_PRJ_LABOR_BRDN_COST,
300 ETC_PRJ_EQUIP_BRDN_COST,
301 ETC_PRJ_BRDN_COST,
302 ETC_POU_LABOR_BRDN_COST,
303 ETC_POU_EQUIP_BRDN_COST,
304 ETC_POU_BRDN_COST,
305 ACT_TXN_RAW_COST,
306 ACT_PRJ_RAW_COST,
307 ACT_POU_RAW_COST,
308 ETC_TXN_RAW_COST,
309 ETC_PRJ_RAW_COST,
310 ETC_POU_RAW_COST,
311 ACT_TXN_LABOR_RAW_COST,
312 ACT_TXN_EQUIP_RAW_COST,
313 ACT_PRJ_LABOR_RAW_COST,
314 ACT_PRJ_EQUIP_RAW_COST,
315 ACT_POU_LABOR_RAW_COST,
316 ACT_POU_EQUIP_RAW_COST,
317 ETC_TXN_LABOR_RAW_COST,
318 ETC_TXN_EQUIP_RAW_COST,
319 ETC_PRJ_LABOR_RAW_COST,
320 ETC_PRJ_EQUIP_RAW_COST,
321 ETC_POU_LABOR_RAW_COST,
322 ETC_POU_EQUIP_RAW_COST,
323 ACT_POU_LABOR_BRDN_COST,
324 ACT_POU_EQUIP_BRDN_COST,
325 ACT_POU_BRDN_COST,
326 ETC_TXN_LABOR_BRDN_COST,
327 TXN_LPB_RAW_COST,
328 TXN_LPB_BRDN_COST,
329 TXN_LPB_LABOR_RAW_COST,
330 TXN_LPB_LABOR_BRDN_COST,
331 TXN_LPB_EQUIP_RAW_COST,
332 TXN_LPB_EQUIP_BRDN_COST,
333 PRJ_LPB_RAW_COST,
334 PRJ_LPB_BRDN_COST,
335 PRJ_LPB_LABOR_RAW_COST,
336 PRJ_LPB_LABOR_BRDN_COST,
337 PRJ_LPB_EQUIP_RAW_COST,
338 PRJ_LPB_EQUIP_BRDN_COST,
339 POU_LPB_RAW_COST,
340 POU_LPB_BRDN_COST,
341 POU_LPB_LABOR_RAW_COST,
342 POU_LPB_LABOR_BRDN_COST,
343 POU_LPB_EQUIP_RAW_COST,
344 POU_LPB_EQUIP_BRDN_COST,
345 LPB_LABOR_HOURS,
346 LPB_EQUIP_HOURS,
347 PERIOD_FLAG,
348 MIN_START_DATE,
349 MAX_END_DATE,
350 P_RAW_COST,
351 P_BRDN_COST,
352 P_REVENUE,
353 P_LBR_RAW_COST,
354 P_LBR_BRDN_COST,
355 P_EQP_RAW_COST,
356 P_EQP_BRDN_COST,
357 P_BASE_RAW_COST,
358 P_BASE_BRDN_COST,
359 P_BASE_LBR_RAW_COST,
360 P_BASE_LBR_BRDN_COST,
361 P_BASE_EQP_RAW_COST,
362 P_BASE_EQP_BRDN_COST,
363 P_LPB_RAW_COST,
364 P_LPB_BRDN_COST,
365 P_LPB_LBR_RAW_COST,
366 P_LPB_LBR_BRDN_COST,
367 P_LPB_EQP_RAW_COST,
368 P_LPB_EQP_BRDN_COST,
369 P_LBR_HOURS,
370 P_EQP_HOURS,
371 P_BASE_LBR_HOURS,
372 P_BASE_EQP_HOURS,
373 P_LPB_LBR_HOURS,
374 P_LPB_EQP_HOURS,
375 P_ACT_LBR_HOURS,
376 P_ACT_EQP_HOURS,
377 P_ACT_LBR_BRDN_COST,
378 P_ACT_EQP_BRDN_COST,
379 P_ACT_RAW_COST,
380 P_ACT_BRDN_COST,
381 P_ACT_LBR_RAW_COST,
382 P_ACT_EQP_RAW_COST,
383 P_ETC_EQP_HOURS,
384 P_ETC_LBR_HOURS,
385 P_ETC_RAW_COST,
386 P_ETC_BRDN_COST,
387 P_ETC_LBR_BRDN_COST,
388 P_ETC_EQP_BRDN_COST,
389 P_ETC_LBR_RAW_COST,
390 P_ETC_EQP_RAW_COST
391 FROM pji_fm_xbs_accum_tmp1;
392
393 WRITE_LOG('RESOURCE ACTUAL FETCH ', 'PA_INTEGRATION_UTILS return status : '||l_return_status||'--'||l_msg_code||'Project id : '||project_id_in||'--'||'Res list id : '||resource_list_id_in||' end date : '||l_end_date||' l_add_days : '||l_add_days);
394
395 COMMIT;
396
397 END populate_resource_actuals_data;
398
399
400
401
402 PROCEDURE WRITE_LOG (
403 p_module in varchar2,
404 p_msg in varchar2
405 ) IS pragma autonomous_transaction;
406
407 BEGIN
408
409 insert into PJI_SYSTEM_DEBUG_MSG
410 (
411 MESSAGE_ID,
412 MESSAGE_LEVEL,
413 MESSAGE_CONTEXT,
414 MESSAGE_TEXT,
415 MESSAGE_TYPE,
416 MODULE,
417 CREATED_BY,
418 CREATION_DATE
419 )
420 values
421 (
422 PJI_SYSTEM_DEBUG_MSG_S.NEXTVAL,
423 1,
424 'AIA',
425 substr(p_msg,1,3999),
426 'OUT',
427 substr(p_module,1,239),
428 -1,
429 sysdate
430 );
431 commit;
432 --end if;
433 END write_log;
434
435
436 FUNCTION GET_BUDGET_LEVEL ( p_project_id IN NUMBER,
437 p_budget_type IN VARCHAR2 DEFAULT NULL ,
438 p_fin_plan_type_id IN NUMBER DEFAULT NULL,
439 p_mode IN VARCHAR2 DEFAULT 'BUDGET')
440 RETURN VARCHAR2
441 IS
442
443 l_TypeCode VARCHAR2(20) := 'NONE';
444 l_ver_not_found VARCHAR2(1) := 'N';
445 l_level_code VARCHAR2(10);
446 l_resource_list_id NUMBER;
447
448 BEGIN
449 l_level_code := 'T';
450
451 IF (p_fin_plan_type_id IS NOT NULL) THEN
452
453 BEGIN
454 select PA_FIN_PLAN_UTILS.Get_Fin_Plan_Level_Code(a.budget_version_id),
455 a.resource_list_id
456 into l_level_code,l_resource_list_id
457 from pa_budget_versions a , pa_proj_fp_options b
458 where a.project_id = p_project_id
459 and a.budget_status_code = 'W'
460 and a.current_working_flag = 'Y'
461 and a.fin_plan_type_id = b.fin_plan_type_id
462 and a.budget_version_id = b.fin_plan_version_id
463 and b.fin_plan_option_level_code = 'PLAN_VERSION'
464 and a.project_id = b.project_id
465 and b.fin_plan_preference_code like '%COST%'
466 and a.fin_plan_type_id = p_fin_plan_type_id
467 and a.fin_plan_type_id <> 10 ;
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 l_TypeCode := 'NONE';
471 l_ver_not_found := 'Y';
472 WHEN OTHERS THEN
473 --write_log('BUDGET','Inside GET_BUDGET_LEVEL raise 6001');
474 RAISE;
475 END ;
476
477 IF ( l_ver_not_found = 'Y' ) THEN
478 BEGIN
479 SELECT PA_FIN_PLAN_UTILS.GET_OPTION_PLANNING_LEVEL(proj_fp_options_id,fin_plan_preference_code),
480 DECODE(all_resource_list_id,NULL, cost_resource_list_id,all_resource_list_id)
481 INTO l_level_code,l_resource_list_id
482 FROM pa_proj_fp_options
483 WHERE fin_plan_type_id = p_fin_plan_type_id
484 and project_id = p_project_id
485 and fin_plan_option_level_code = 'PLAN_TYPE';
486 EXCEPTION
487 WHEN NO_DATA_FOUND THEN
488 l_TypeCode := 'NONE';
489 WHEN OTHERS THEN
490 --write_log('BUDGET','Inside GET_BUDGET_LEVEL raise 6002');
491 RAISE;
492 END;
493 END IF;
494
495 END IF;
496
497 IF ( p_fin_plan_type_id is null and p_budget_type is not null ) THEN
498 BEGIN
499 select b.entry_level_code
500 INTO l_level_code
501 FROM PA_BUDGET_VERSIONS a , pa_budget_entry_methods b
502 where a.fin_plan_type_id is null
503 and a.project_id = p_project_id
504 and a.budget_status_code = 'W'
505 --and a.current_working_flag = 'Y'
506 and a.budget_type_code = p_budget_type
507 and b.budget_entry_method_code = a.budget_entry_method_code ;
508 EXCEPTION
509 WHEN NO_DATA_FOUND THEN
510 l_TypeCode := 'NONE';
511 WHEN OTHERS THEN
512 --write_log('BUDGET','Inside GET_BUDGET_LEVEL raise 6004');
513 RAISE;
514 END ;
515
516 END IF;
517
518 IF l_level_code = 'T' THEN
519 l_TypeCode := 'TOP_TASK';
520 ELSIF l_level_code = 'L' THEN
521 l_TypeCode := 'LOWEST_TASK';
522 ELSIF l_level_code = 'P' THEN
523 l_TypeCode := 'PROJECT';
524 ELSE
525 l_TypeCode := 'NONE';
526 END IF;
527
528 -- Bug 8822052
529 IF p_mode <> 'BUDGET' THEN
530 IF (p_fin_plan_type_id IS NOT NULL) THEN
531 IF nvl(l_resource_list_id,1000) = 1000 THEN
532 l_TypeCode := 'NONE';
533 END IF;
534 ELSIF ( p_fin_plan_type_id is null and p_budget_type is not null ) THEN
535 l_TypeCode := 'NONE';
536 END IF;
537 END IF;
538
539 RETURN l_TypeCode;
540
541 END GET_BUDGET_LEVEL;
542
543 END PA_INTEGRATION_UTILS;