[Home] [Help]
PACKAGE BODY: APPS.PA_PLANNING_ELEMENT_UTILS
Source
1 PACKAGE BODY pa_planning_element_utils AS
2 /* $Header: PAFPPEUB.pls 120.10.12010000.3 2008/12/26 14:05:38 bifernan ship $
3 Start of Comments
4 Package name : pa_planning_element_utils
5 Purpose : API's for Edit Plan / Task Details page
6 History :
7 NOTE :
8 End of Comments
9 */
10
11 /* CHANGE HISTORY
12 * 05/18/2004 dlai In the select statements to populate x_current_version_id and x_original_version_id,
13 * joined str.element_version_id to bv.project_structure_version_id instead of
14 * str.pev_structure_id to bv.project_structure_version_id (bug 3622609)
15 */
16 /* This procedure should be used for the Workplan Task Details page ONLY!
17 */
18
19 --These variables are internally used by the API get_fin_struct_id. They should not be used by other
20 --APIs in this package. These are created for bug 3546208
21 l_edit_plan_project_id pa_projects_all.project_id%TYPE;
22 l_edit_plan_struct_id pa_budget_versions.project_structure_version_id%TYPE;
23 l_edit_plan_bv_id pa_budget_versions.budget_version_id%TYPE;
24
25 PROCEDURE get_workplan_bvids
26 (p_project_id IN pa_budget_versions.project_id%TYPE,
27 p_element_version_id IN pa_proj_element_versions.element_version_id%TYPE,
28 x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
29 x_baselined_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
30 x_published_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
31 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
33 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
34 l_wp_structure_version_id pa_budget_versions.project_structure_version_id%TYPE;
35 BEGIN
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37 begin
38 -- RETRIEVE THE WORKPLAN STRUCTURE VERSION ID which is used for
39 -- join with pa_budget_versions
40 select evs.element_version_id -- changed by shyugen
41 into l_wp_structure_version_id
42 from pa_proj_element_versions ev,
43 pa_proj_elem_ver_structure evs
44 where ev.project_id = p_project_id and
45 ev.element_version_id = p_element_version_id and
46 ev.project_id = evs.project_id and -- Added for perf fix - 3961665
47 ev.parent_structure_version_id = evs.element_version_id;
48
49 exception
50 when NO_DATA_FOUND then
51 x_return_status := FND_API.G_RET_STS_ERROR;
52 x_msg_count := 1;
53 x_msg_data := SQLERRM;
54 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_planning_element_utils',
55 p_procedure_name => 'get_workplan_bvids');
56 return;
57 end;
58
59 begin
60 -- RETRIEVE CURRENT VERSION ID
61 select budget_version_id
62 into x_current_version_id
63 from pa_budget_versions
64 where project_id = p_project_id and
65 wp_version_flag = 'Y' and
66 project_structure_version_id = l_wp_structure_version_id;
67 exception
68 when NO_DATA_FOUND then
69 x_current_version_id := -1;
70 end;
71 -- RETRIEVE CURRENT BASELINED VERSION ID
72 begin
73 select bv.budget_version_id
74 into x_baselined_version_id
75 from pa_proj_elem_ver_structure str,
76 pa_proj_element_versions ppev1,
77 pa_proj_element_versions ppev2,
78 pa_budget_versions bv
79 where ppev1.element_version_id = p_element_version_id
80 and ppev1.project_id = ppev2.project_id
81 and ppev1.proj_element_id = ppev2.proj_element_id -- all the other task versions
82 and ppev2.parent_structure_version_id = str.element_version_id --the structure version of each task version
83 and ppev2.project_id = str.project_id
84 and str.current_flag = 'Y' --the baselined structure version
85 and str.element_version_id = bv.project_structure_version_id
86 and bv.wp_version_flag = 'Y';
87 exception
88 when NO_DATA_FOUND then
89 x_baselined_version_id := -1;
90 end;
91 -- RETRIEVE LATEST PUBLISHED VERSION ID
92 begin
93 select bv.budget_version_id
94 into x_published_version_id
95 from pa_proj_elem_ver_structure str,
96 pa_proj_element_versions ppev1,
97 pa_proj_element_versions ppev2,
98 pa_budget_versions bv
99 where ppev1.element_version_id = p_element_version_id
100 and ppev1.project_id = ppev2.project_id
101 and ppev1.proj_element_id = ppev2.proj_element_id -- all the other task versions
102 and ppev2.parent_structure_version_id = str.element_version_id --the structure version of each task version
103 and ppev2.project_id = str.project_id
104 and latest_eff_published_flag = 'Y' --the structure version which is latest published
105 and str.element_version_id = bv.project_structure_version_id
106 and bv.wp_version_flag = 'Y';
107 exception
108 when NO_DATA_FOUND then
109 x_published_version_id := -1;
110 end;
111 EXCEPTION
112 when others then
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 x_msg_count := 1;
115 x_msg_data := SQLERRM;
116 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_planning_element_utils',
117 p_procedure_name => 'get_workplan_bvids');
118 END get_workplan_bvids;
119
120 /* CHANGE HISTORY
121 * 03/04/2004 dlai modified behavior of get_finplan_bvids. New summary:
122 * If p_budget_version_id is a BUDGET version:
123 * x_current_version_id = current baselined version of same plan type
124 * x_original_version_id = original baselined version of same plan type
125 * x_prior_fcst_version_id = current baselined version of PRIMARY FORECAST plan type
126 * If p_budget_version is a FORECAST version:
127 * x_current_version_id = current baselined version of APPROVED BUDGET plan type (-1 if not existing)
128 * x_original_version_id = -1 (don't need original baselined version of AB plan type)
129 * x_prior_fcst_version_id = current baselined version of same plan type
130 * 03/04/2005 dlai added additional input parameter: p_view_plan_flag. If this value is 'Y',
131 * then even if the plan version is FORECAST, we will still return the current and
132 * original budget version id's
133 * 07/14/2005 dlai if p_budget_version is a FORECAST version, x_original_version_id will
134 * be passed if it exists (regardless of the value of p_view_plan_flag)
135 */
136 PROCEDURE get_finplan_bvids
137 (p_project_id IN pa_budget_versions.project_id%TYPE,
138 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
139 p_view_plan_flag IN VARCHAR2 default 'N',
140 x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
141 x_original_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
142 x_prior_fcst_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
143 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
144 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
145 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
146 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
147 l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
148 BEGIN
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150 select pt.plan_class_code,
151 po.fin_plan_preference_code
152 into l_plan_class_code,
153 l_fin_plan_pref_code
154 from pa_budget_versions bv,
155 pa_fin_plan_types_b pt,
156 pa_proj_fp_options po
157 where bv.budget_version_id = p_budget_version_id and
158 bv.fin_plan_type_id = pt.fin_plan_type_id and
159 bv.budget_version_id = po.fin_plan_version_id;
160 if l_plan_class_code = 'BUDGET' then
161 -- CURRENT PLAN VERSION IS BUDGET PLAN CLASS
162 begin
163 -- RETRIEVE CURRENT BASELINED VERSION (IF IT EXISTS)
164 select bv.budget_version_id
165 into x_current_version_id
166 from pa_proj_fp_options po,
167 pa_budget_versions bv,
168 pa_proj_fp_options po2
169 where po.project_id = p_project_id and
170 po.fin_plan_version_id = p_budget_version_id and
171 po.fin_plan_option_level_code = 'PLAN_VERSION' and
172 po.project_id = po2.project_id and
173 po.fin_plan_type_id = po2.fin_plan_type_id and
174 po.fin_plan_preference_code = po2.fin_plan_preference_code and
175 po2.fin_plan_option_level_code = 'PLAN_VERSION' and
176 po2.fin_plan_version_id = bv.budget_version_id and
177 bv.current_flag = 'Y';
178 exception
179 when NO_DATA_FOUND then
180 x_current_version_id := -1;
181 end;
182 -- RETRIEVE ORIGINAL BASELINED VERSION (IF IT EXISTS)
183 begin
184 select bv.budget_version_id
185 into x_original_version_id
186 from pa_proj_fp_options po,
187 pa_budget_versions bv,
188 pa_proj_fp_options po2
189 where po.project_id = p_project_id and
190 po.fin_plan_version_id = p_budget_version_id and
191 po.fin_plan_option_level_code = 'PLAN_VERSION' and
192 po.project_id = po2.project_id and
193 po.fin_plan_type_id = po2.fin_plan_type_id and
194 po.fin_plan_preference_code = po2.fin_plan_preference_code and
195 po2.fin_plan_option_level_code = 'PLAN_VERSION' and
196 po2.fin_plan_version_id = bv.budget_version_id and
197 bv.current_original_flag = 'Y'; -- bug fix 3630207
198 exception
199 when NO_DATA_FOUND then
200 x_original_version_id := -1;
201 end;
202 -- RETRIEVE PRIMARY FORECAST BASELINED VERSION (IF IT EXISTS)
203 if l_fin_plan_pref_code = 'COST_ONLY' then
204 -- looking for PRIMARY COST FORECAST plan type
205 begin
206 select bv.budget_version_id
207 into x_prior_fcst_version_id
208 from pa_proj_fp_options po,
209 pa_budget_versions bv
210 where po.project_id = p_project_id and
211 po.fin_plan_option_level_code = 'PLAN_VERSION' and
212 bv.primary_cost_forecast_flag = 'Y' and
213 po.fin_plan_version_id = bv.budget_version_id and
214 bv.current_flag = 'Y';
215 exception
216 when NO_DATA_FOUND then
217 x_prior_fcst_version_id := -1;
218 end;
219 else
220 -- looking for PRIMARY REVENUE FORECAST plan type
221 begin
222 select bv.budget_version_id
223 into x_prior_fcst_version_id
224 from pa_proj_fp_options po,
225 pa_budget_versions bv
226 where po.project_id = p_project_id and
227 po.fin_plan_option_level_code = 'PLAN_VERSION' and
228 bv.primary_rev_forecast_flag = 'Y' and
229 po.fin_plan_version_id = bv.budget_version_id and
230 bv.current_flag = 'Y';
231 exception
232 when NO_DATA_FOUND then
233 x_prior_fcst_version_id := -1;
234 end;
235 end if; -- l_fin_plan_pref_code
236
237 else
238 -- CURRENT PLAN VERSION IS FORECAST PLAN CLASS
239 begin
240 select bv.budget_version_id
241 into x_prior_fcst_version_id
242 from pa_proj_fp_options po,
243 pa_budget_versions bv,
244 pa_proj_fp_options po2
245 where po.project_id = p_project_id and
246 po.fin_plan_version_id = p_budget_version_id and
247 po.fin_plan_option_level_code = 'PLAN_VERSION' and
248 po.project_id = po2.project_id and
249 po.fin_plan_type_id = po2.fin_plan_type_id and
250 po.fin_plan_preference_code = po2.fin_plan_preference_code and
251 po2.fin_plan_option_level_code = 'PLAN_VERSION' and
252 po2.fin_plan_version_id = bv.budget_version_id and
253 bv.current_flag = 'Y';
254 exception
255 when NO_DATA_FOUND then
256 x_prior_fcst_version_id := -1;
257 end;
258 -- RETRIEVE APPROVED BUDGET INFO (IF IT EXISTS)
259 -- 4477233: for Forecast version, always return original baselined info
260 -- retrieve original baselined info
261 if l_fin_plan_pref_code = 'COST_ONLY' then
262 -- looking for APPROVED COST BUDGET plan type
263 begin
264 select bv.budget_version_id
265 into x_original_version_id
266 from pa_proj_fp_options po,
267 pa_budget_versions bv
268 where po.project_id = p_project_id and
269 po.fin_plan_option_level_code = 'PLAN_VERSION' and
270 bv.approved_cost_plan_type_flag = 'Y' and
271 po.fin_plan_version_id = bv.budget_version_id and
272 bv.current_original_flag = 'Y';
273 exception
274 when NO_DATA_FOUND then
275 x_original_version_id := -1; -- Bug 7668837
276 end;
277 elsif l_fin_plan_pref_code = 'REVENUE_ONLY' then
278 -- looking for APPROVED REVENUE BUDGET plan type
279 begin
280 select bv.budget_version_id
281 into x_original_version_id
282 from pa_proj_fp_options po,
283 pa_budget_versions bv
284 where po.project_id = p_project_id and
285 po.fin_plan_option_level_code = 'PLAN_VERSION' and
286 bv.approved_rev_plan_type_flag = 'Y' and
287 po.fin_plan_version_id = bv.budget_version_id and
288 bv.current_original_flag = 'Y';
289 exception
290 when NO_DATA_FOUND then
291 x_original_version_id := -1; -- Bug 7668837
292 end;
293 else
294 -- looking for APPROVED COST AND REVENUE BUDGET plan type
295 begin
296 select bv.budget_version_id
297 into x_original_version_id
298 from pa_proj_fp_options po,
299 pa_budget_versions bv
300 where po.project_id = p_project_id and
301 po.fin_plan_option_level_code = 'PLAN_VERSION' and
302 bv.approved_cost_plan_type_flag = 'Y' and
303 bv.approved_rev_plan_type_flag = 'Y' and
304 po.fin_plan_version_id = bv.budget_version_id and
305 bv.current_original_flag = 'Y';
306 exception
307 when NO_DATA_FOUND then
308 x_original_version_id := -1; -- Bug 7668837
309 end;
310 end if; -- l_fin_plan_pref_code
311
312
313 if l_fin_plan_pref_code = 'COST_ONLY' then
314 -- looking for APPROVED COST BUDGET plan type
315 begin
316 select bv.budget_version_id
317 into x_current_version_id
318 from pa_proj_fp_options po,
319 pa_budget_versions bv
320 where po.project_id = p_project_id and
321 po.fin_plan_option_level_code = 'PLAN_VERSION' and
322 bv.approved_cost_plan_type_flag = 'Y' and
323 po.fin_plan_version_id = bv.budget_version_id and
324 bv.current_flag = 'Y';
325 exception
326 when NO_DATA_FOUND then
327 x_current_version_id := -1;
328 end;
329 else
330 -- looking for APPROVED REVENUE BUDGET plan type
331 begin
332 select bv.budget_version_id
333 into x_current_version_id
334 from pa_proj_fp_options po,
335 pa_budget_versions bv
336 where po.project_id = p_project_id and
337 po.fin_plan_option_level_code = 'PLAN_VERSION' and
338 bv.approved_rev_plan_type_flag = 'Y' and
339 po.fin_plan_version_id = bv.budget_version_id and
340 bv.current_flag = 'Y';
341 exception
342 when NO_DATA_FOUND then
343 x_current_version_id := -1;
344 end;
345 end if; -- l_fin_plan_pref_code
346
347 end if; -- l_plan_class_code
348 EXCEPTION
349 when others then
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 x_msg_count := 1;
352 x_msg_data := SQLERRM;
353 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_planning_element_utils',
354 p_procedure_name => 'get_finplan_bvids');
355 END get_finplan_bvids;
356
357 FUNCTION get_task_name_and_number
358 (p_project_or_task IN VARCHAR2, -- 'PROJECT' or 'TASK'
359 p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE) return VARCHAR2 is
360 l_return_value VARCHAR2(2000);
361 BEGIN
362 if p_project_or_task = 'PROJECT' then
363 -- GET PROJECT NAME AND NUMBER
364 select pa.name || ' (' || pa.segment1 || ')'
365 into l_return_value
366 from pa_resource_assignments ra,
367 pa_projects_all pa
368 where ra.resource_assignment_id = p_resource_assignment_id and
369 ra.project_id = pa.project_id;
370 else
371 -- GET TASK NAME AND NUMBER
372 select pe.name || ' (' || pe.element_number || ')'
373 into l_return_value
374 from pa_resource_assignments ra,
375 pa_proj_elements pe
376 where ra.resource_assignment_id = p_resource_assignment_id and
377 ra.task_id = pe.proj_element_id;
378 end if;
379 return l_return_value;
380 EXCEPTION
381 when NO_DATA_FOUND then
382 return null;
383 when OTHERS then
384 return null;
385 END get_task_name_and_number;
386 /* Bug 3840851: Fin strucutre function call was used before l_project_id was
387 * retrieved!. Since the selects which use this function in the below api will
388 * always return one record, removed the local variable for structure version id
389 * and included direct function call for retrieving fin struct ver id in the
390 * selects. */
391 -- Bug 4057673. Added a parameter p_fin_plan_level_code. It will be either 'P','L' or 'M'
392 --depending on the planning level of the budget version
393 FUNCTION get_project_task_level
394 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
395 p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE) return VARCHAR2 is
396
397 l_project_id pa_resource_assignments.project_id%TYPE;
398 l_element_version_id pa_resource_assignments.wbs_element_version_id%TYPE;
399 l_rlm_id pa_resource_assignments.resource_list_member_id%TYPE;
400 l_uncat_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
401 l_summary_task_flag VARCHAR2(1);
402 l_return_value VARCHAR2(80);
403
404 CURSOR cur_obj_rel
405 IS
406 select object_id_to1 from pa_object_relationships
407 where object_id_to1 = l_element_version_id and
408 object_id_from1 = PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(l_project_id)
409 and object_type_from = 'PA_STRUCTURES'
410 and relationship_type = 'S';
411 /*
412 CURSOR cur_obj_rel
413 IS
414 SELECT 'x'
415 FROM pa_object_relationships
416 WHERE object_id_to1 = l_element_version_id and
417 rownum < 2 and
418 object_type_from = 'PA_TASKS';
419 */
420 v_dummy_id pa_resource_assignments.wbs_element_version_id%TYPE;
421 --Bug 4057673.This variable will be set to Y for tasks that are both top and lowest
422 l_top_and_lowest_task_flag VARCHAR2(1);
423
424 BEGIN
425 l_return_value := 'INVALID VALUE';
426 l_uncat_rlm_id := pa_planning_element_utils.get_project_uncat_rlmid;
427 l_top_and_lowest_task_flag := 'N';--Bug 4057673
428 -- we have an element_version_id; need to determine what level task it is
429 BEGIN
430 select pelm.element_version_id,
431 ra.resource_list_member_id,
432 ra.project_id
433 into l_element_version_id,
434 l_rlm_id,
435 l_project_id
436 from pa_resource_assignments ra,
437 pa_proj_element_versions pelm
438 where ra.resource_assignment_id = p_resource_assignment_id
439 AND pelm.proj_element_id(+)=ra.task_id
440 AND pelm.parent_structure_Version_id(+)=PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(ra.project_id);
441 EXCEPTION
442 when NO_DATA_FOUND then
443 return l_return_value;
444 END;
445 /* l_element_version_id could be 0 or null if not a task-level row:
446 * bug 3455681
447 */
448 if (l_element_version_id is null or l_element_version_id = 0) then
449 -- no element_version_id; must be PROJECT row or resource row
450 l_return_value := 'PROJECT';
451 /*
452 if l_rlm_id = l_uncat_rlm_id then
453 l_return_value := 'PROJECT';
454 else
455 l_return_value := 'RESOURCE';
456 end if;
457 */
458 else
459 open cur_obj_rel;
460 fetch cur_obj_rel into v_dummy_id;
461 if cur_obj_rel%FOUND then
462 -- the task is a TOP task or a LOWEST_TASK
463 l_summary_task_flag :=
464 PA_PROJ_ELEMENTS_UTILS.is_summary_task_or_structure(l_element_version_id);
465 if l_summary_task_flag = 'N' then
466 -- task is a Lowest Task
467 l_return_value := 'LOWEST_TASK';
468 l_top_and_lowest_task_flag := 'Y';--Bug 4057673
469 else
470 -- task is a Top Task
471 l_return_value := 'TOP_TASK';
472 end if;
473 else
474 l_summary_task_flag :=
475 PA_PROJ_ELEMENTS_UTILS.is_summary_task_or_structure(l_element_version_id);
476 if l_summary_task_flag = 'N' then
477 -- task is a Lowest Task
478 l_return_value := 'LOWEST_TASK';
479 else
480 l_return_value := 'MIDDLE_TASK';
481 end if;
482 end if; -- cursor%FOUND
483 close cur_obj_rel;
484 end if; -- TASK
485 --Bug 4057673. If the task is both top and lowest then depending on planning level return either TOP or LOWEST for
486 --task level.
487 IF l_top_and_lowest_task_flag = 'Y' THEN
488
489 IF p_fin_plan_level_code = 'T' THEN
490
491 l_return_value := 'TOP_TASK';
492
493 ELSIF p_fin_plan_level_code = 'L' THEN
494
495 l_return_value := 'LOWEST_TASK';
496
497 END IF;
498
499 END IF;
500 return l_return_value;
501 END get_project_task_level;
502
503 FUNCTION get_res_class_name
504 (p_res_class_code IN pa_resource_classes_b.resource_class_code%TYPE) return VARCHAR2 is
505 l_return_value VARCHAR2(240) := 'invalid value';
506 BEGIN
507 select name
508 into l_return_value
509 from pa_resource_classes_vl
510 where resource_class_code = p_res_class_code;
511 return l_return_value;
512 EXCEPTION
513 when NO_DATA_FOUND then
514 return null;
515 when OTHERS then
516 return null;
517 END get_res_class_name;
518
519 FUNCTION get_res_type_name
520 (p_res_type_code IN pa_res_types_b.res_type_code%TYPE) return VARCHAR2 is
521 l_return_value VARCHAR2(240) := 'invalid value';
522 BEGIN
523 select name
524 into l_return_value
525 from pa_res_types_vl
526 where res_type_code = p_res_type_code;
527 return l_return_value;
528 EXCEPTION
529 when NO_DATA_FOUND then
530 return null;
531 when OTHERS then
532 return null;
533 END get_res_type_name;
534
535 FUNCTION get_project_role_name
536 (p_project_role_id IN pa_project_role_types_b.project_role_id%TYPE) return VARCHAR2 IS
537 l_return_value VARCHAR2(80) := 'invalid value';
538 BEGIN
539 select meaning
540 into l_return_value
541 -- Bug Fix 4452472
542 -- replaced the _vl with _tl
543 -- from pa_project_role_types_vl
544 from pa_project_role_types_tl
545 where project_role_id = p_project_role_id and
546 language = userenv('LANG');
547 return l_return_value;
548 EXCEPTION
549 when NO_DATA_FOUND then
550 return null;
551 when OTHERS then
552 return null;
553 END get_project_role_name;
554
555 FUNCTION get_supplier_name
556 (p_supplier_id IN po_vendors.vendor_id%TYPE) return VARCHAR2 is
557 l_return_value VARCHAR2(80) := 'invalid value';
558 BEGIN
559 select vendor_name
560 into l_return_value
561 from po_vendors
562 where vendor_id = p_supplier_id;
563 return l_return_value;
564 EXCEPTION
565 when NO_DATA_FOUND then
566 return null;
567 when OTHERS then
568 return null;
569 END get_supplier_name;
570
571 FUNCTION get_schedule_role_name
572 (p_proj_assignment_id IN pa_project_assignments.assignment_id%TYPE) return VARCHAR2 is
573 l_return_value VARCHAR2(80) := 'invalid value';
574 BEGIN
575 select assignment_name
576 into l_return_value
577 from pa_project_assignments
578 where assignment_id = p_proj_assignment_id;
579 return l_return_value;
580 EXCEPTION
581 when NO_DATA_FOUND then
582 return null;
583 when OTHERS then
584 return null;
585 END get_schedule_role_name;
586
587 FUNCTION get_spread_curve_name
588 (p_spread_curve_id IN pa_spread_curves_b.spread_curve_id%TYPE) return VARCHAR2 is
589 l_return_value VARCHAR2(240);
590 BEGIN
591 select name
592 into l_return_value
593 -- Bug Fix 4452472
594 -- replaced the _vl with _tl
595 -- from pa_spread_curves_vl
596 from pa_spread_curves_tl
597 where spread_curve_id = p_spread_curve_id and
598 language = userenv('LANG');
599 return l_return_value;
600 EXCEPTION
601 when NO_DATA_FOUND then
602 return null;
603 when OTHERS then
604 return null;
605 END get_spread_curve_name;
606
607 FUNCTION get_mfc_cost_type_name
608 (p_mfc_cost_type_id IN pa_resource_assignments.mfc_cost_type_id%TYPE) return VARCHAR2 is
609 l_return_value VARCHAR2(80);
610 BEGIN
611 select cost_type
612 into l_return_value
613 from cst_cost_types_v
614 where cost_type_id = p_mfc_cost_type_id;
615 return l_return_value;
616 EXCEPTION
617 when NO_DATA_FOUND then
618 return null;
619 when OTHERS then
620 return null;
621 END get_mfc_cost_type_name;
622
623 FUNCTION get_project_uncat_rlmid return NUMBER is
624 l_uncat_resource_list_id pa_resource_lists.resource_list_id%TYPE;
625 l_uncat_rlm_id pa_resource_assignments.resource_list_member_id%TYPE;
626 l_track_as_labor_flag pa_resources.track_as_labor_flag%TYPE;
627 l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
628 l_return_status VARCHAR2(1);
629 l_msg_count NUMBER;
630 l_msg_data VARCHAR2(2000);
631 BEGIN
632 pa_fin_plan_utils.Get_Uncat_Resource_List_Info
633 (x_resource_list_id => l_uncat_resource_list_id
634 ,x_resource_list_member_id => l_uncat_rlm_id
635 ,x_track_as_labor_flag => l_track_as_labor_flag
636 ,x_unit_of_measure => l_unit_of_measure
637 ,x_return_status => l_return_status
638 ,x_msg_count => l_msg_count
639 ,x_msg_data => l_msg_data);
640 if l_return_status = FND_API.G_RET_STS_SUCCESS then
641 return l_uncat_rlm_id;
642 else
643 return null;
644 end if;
645 EXCEPTION
646 when OTHERS then
647 return null;
648 END get_project_uncat_rlmid;
649
650
651 /* PROCEDURE get_common_budget_version_info
652 * This procedure is used to populate attributes for a particular row in the
653 * PlanningElementsCommonVO.
654 * INPUT: p_budget_version_id - budget version id
655 * p_resource_assignment_id - particular planning element row
656 * p_project_currency_code - project currency code
657 * p_projfunc_currency_code - project functional currency code
658 * p_txn_currency_code - applicable if the planning element is planned in
659 * multi-currency
660 * OUTPUT: row-level attributes for the budget version's planning element for a
661 * particular currency
662 * REVISION HISTORY:
663 * 07/08/2004 - dlai - added the following input parameters so ETC rates can
664 * be displayed for baselined and latest published versions (Task
665 * Assignments): x_etc_avg_rev_rate, x_etc_avg_raw_cost_rate,
666 * x_etc_avg_burd_cost_rate
667 * 07/15/2004 - dlai - added the following output parameters for bug 3622609:
668 * x_schedule_start_date, x_schedule_end_date
669 * 11/16/2004 - dlai - when no budget lines exist for a particular
670 * txn_currency_code (which is not proj or projfunc currency), be
671 * sure to return null for txn amounts, and not rely on the amounts in
672 * pa_resource_assignments
673 * 02/08/2005 - dlai - when looking for match, the records should also have
674 * the same unit_of_measure in addition to task-res-txncurrency
675 * 05/10/2005 - dlai - margin parameters are returned as null if
676 * p_budget_version_id refers to COST_ONLY or REVENUE_ONLY version
677 */
678 PROCEDURE get_common_budget_version_info
679 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
680 p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
681 p_project_currency_code IN pa_projects_all.project_currency_code%TYPE,
682 p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE,
683 p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
684 p_line_start_date IN pa_budget_lines.start_date%TYPE := to_date(NULL),
685 p_line_end_date IN pa_budget_lines.end_date%TYPE := to_date(NULL),
686 x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
687 x_planning_start_date OUT NOCOPY pa_resource_assignments.planning_start_date%TYPE, --File.Sql.39 bug 4440895
688 x_planning_end_date OUT NOCOPY pa_resource_assignments.planning_end_date%TYPE, --File.Sql.39 bug 4440895
689 x_schedule_start_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
690 x_schedule_end_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
691 x_quantity OUT NOCOPY pa_resource_assignments.total_plan_quantity%TYPE, --File.Sql.39 bug 4440895
692 x_revenue_txn_cur OUT NOCOPY pa_budget_lines.txn_revenue%TYPE, --File.Sql.39 bug 4440895
693 x_revenue_proj_cur OUT NOCOPY pa_resource_assignments.total_project_revenue%TYPE, --File.Sql.39 bug 4440895
694 x_revenue_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_revenue%TYPE, --File.Sql.39 bug 4440895
695 x_raw_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_raw_cost%TYPE, --File.Sql.39 bug 4440895
696 x_raw_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_raw_cost%TYPE, --File.Sql.39 bug 4440895
697 x_raw_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_raw_cost%TYPE, --File.Sql.39 bug 4440895
698 x_burd_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_burdened_cost%TYPE, --File.Sql.39 bug 4440895
699 x_burd_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_burdened_cost%TYPE, --File.Sql.39 bug 4440895
700 x_burd_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_burdened_cost%TYPE, --File.Sql.39 bug 4440895
701 -- x_burd_multiplier OUT pa_budget_lines.txn_burden_multiplier%TYPE, -- FPM2 data model changes
702 x_init_rev_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
703 x_avg_rev_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
704 x_init_raw_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
705 x_avg_raw_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
706 x_init_burd_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
707 x_avg_burd_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
708 x_margin_txn_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
709 x_margin_proj_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
710 x_margin_proj_func_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
711 x_margin_pct OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
712 x_etc_avg_rev_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
713 x_etc_avg_raw_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
714 x_etc_avg_burd_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
715 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
716 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
717 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
718
719 l_project_id pa_resource_assignments.project_id%TYPE;
720 l_task_id pa_resource_assignments.task_id%TYPE;
721 l_resource_list_member_id pa_resource_assignments.resource_list_member_id%TYPE;
722 l_resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE;
723 l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
724
725 l_budget_lines_exist_flag VARCHAR2(1); -- whether budget lines exist for planning element
726
727 l_start_date pa_budget_lines.start_date%TYPE;
728 l_end_date pa_budget_lines.end_date%TYPE;
729 l_period_name pa_budget_lines.period_name%TYPE;
730 l_quantity pa_budget_lines.quantity%TYPE;
731 l_txn_raw_cost pa_budget_lines.raw_cost%TYPE;
732 l_txn_burdened_cost pa_budget_lines.burdened_cost%TYPE;
733 l_txn_revenue pa_budget_lines.revenue%TYPE;
734 l_init_quantity pa_budget_lines.init_quantity%TYPE;
735 l_txn_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
736 l_txn_init_burdened_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
737 l_txn_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
738 l_init_raw_cost_rate NUMBER;
739 l_init_burd_cost_rate NUMBER;
740 l_init_revenue_rate NUMBER;
741 l_etc_init_raw_cost_rate NUMBER;
742 l_etc_init_burd_cost_rate NUMBER;
743 l_etc_init_revenue_rate NUMBER;
744
745 cursor raid_csr is
746 select ra.resource_assignment_id
747 from pa_resource_assignments ra
748 where ra.project_id = l_project_id and
749 ra.budget_version_id = p_budget_version_id and
750 ra.task_id = l_task_id and
751 ra.resource_list_member_id = l_resource_list_member_id and
752 ra.unit_of_measure = l_unit_of_measure;
753 raid_rec raid_csr%ROWTYPE;
754
755 cursor budget_lines_csr is
756 select 'Y'
757 from pa_budget_lines
758 where resource_assignment_id = l_resource_assignment_id and
759 txn_currency_code = p_txn_currency_code;
760 budget_lines_rec budget_lines_csr%ROWTYPE;
761
762 -- Bug Fix 3732157
763 -- Moving the code from the body to here as a cursor.
764
765 -- Bug 6459226: Replacing the queries from pa_resource_asgn_curr
766 -- with pa_budget_lines once again because this cursor is called
767 -- in case budget lines exist and on Edit Budget Line page, we
768 -- have to show the periodic split up of amounts.
769
770
771 --Bug 6836806 Modifying the cursor to display periodic level information under current budget columns
772 --for budget lines when viewed at period level data.
773 -- Reverting the changes coz the budget line infomatin should be picked from pa_budget_lines in order view
774 -- data periodically when planning start/end dates are passed.
775 CURSOR get_budget_line_amts_for_dates IS
776 select ra.planning_start_date,
777 ra.planning_end_date,
778 ra.schedule_start_date,
779 ra.schedule_end_date,
780 SUM(bl.quantity),
781 SUM(bl.txn_revenue),
782 SUM(bl.project_revenue), --ra.total_project_revenue,
783 SUM(bl.revenue), --ra.total_plan_revenue,
784 SUM(bl.txn_raw_cost),
785 SUM(bl.project_raw_cost), --ra.total_project_raw_cost,
786 SUM(bl.raw_cost), --ra.total_plan_raw_cost,
787 SUM(bl.txn_burdened_cost),
788 SUM(bl.project_burdened_cost), --ra.total_project_burdened_cost,
789 SUM(bl.burdened_cost), --ra.total_plan_burdened_cost,
790 null, -- x_init_rev_rate (TO BE CALCULATED)
791 DECODE(SUM(bl.quantity),
792 0, 0,
793 null, null,
794 SUM(bl.txn_revenue)/SUM(bl.quantity)),
795 null, -- x_init_raw_cost_rate (TO BE CALCULATED)
796 DECODE(SUM(bl.quantity),
797 0, 0,
798 null, null,
799 SUM(bl.txn_raw_cost)/SUM(bl.quantity)),
800 null, -- x_init_burd_cost_rate (TO BE CALCULATED)
801 DECODE(SUM(bl.quantity),
802 0, 0,
803 null, null,
804 SUM(bl.txn_burdened_cost)/SUM(bl.quantity)),
805 DECODE(po.fin_plan_preference_code,
806 'COST_ONLY', to_number(null),
807 'REVENUE_ONLY', to_number(null),
808 DECODE(po.margin_derived_from_code,
809 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
810 SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))),
811 DECODE(po.fin_plan_preference_code,
812 'COST_ONLY', to_number(null),
813 'REVENUE_ONLY', to_number(null),
814 DECODE(po.margin_derived_from_code,
815 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
816 SUM(bl.project_revenue) - SUM(bl.project_raw_cost))),
817 DECODE(po.fin_plan_preference_code,
818 'COST_ONLY', to_number(null),
819 'REVENUE_ONLY', to_number(null),
820 DECODE(po.margin_derived_from_code,
821 'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
822 SUM(bl.revenue) - SUM(bl.raw_cost))),
823 DECODE(po.fin_plan_preference_code,
824 'COST_ONLY', to_number(null),
825 'REVENUE_ONLY', to_number(null),
826 DECODE(SUM(bl.project_revenue),
827 0, 0,
828 null, to_number(null),
829 DECODE(po.margin_derived_from_code,
830 'B', 100*(SUM(bl.project_revenue) - SUM(bl.project_burdened_cost))/SUM(bl.project_revenue),
831 100*(SUM(bl.project_revenue) - SUM(bl.project_raw_cost)))/SUM(bl.project_revenue))),
832 DECODE(SUM(bl.quantity) - SUM(nvl(bl.init_quantity,0)),
833 0, 0,
834 null, 0,
835 (SUM(bl.txn_revenue) - SUM(nvl(bl.txn_init_revenue,0)))/(SUM(bl.quantity) - SUM(nvl(bl.init_quantity,0)))),
836 DECODE(SUM(bl.quantity) - SUM(nvl(init_quantity,0)),
837 0, 0,
838 null, 0,
839 (SUM(bl.txn_raw_cost) - SUM(nvl(bl.txn_init_raw_cost,0)))/(SUM(bl.quantity) - SUM(nvl(init_quantity,0)))),
840 DECODE(SUM(bl.quantity) - SUM(nvl(init_quantity,0)),
841 0, 0,
842 null, 0,
843 (SUM(bl.txn_burdened_cost) - SUM(nvl(bl.txn_init_burdened_cost,0)))/(SUM(bl.quantity) - SUM(nvl(init_quantity,0))))
844 from pa_resource_assignments ra,
845 pa_budget_lines bl,
846 pa_budget_versions bv,
847 pa_proj_fp_options po
848 where ra.resource_assignment_id = l_resource_assignment_id and
849 ra.resource_assignment_id = bl.resource_assignment_id and
850 bl.txn_currency_code = p_txn_currency_code and
851 ra.budget_version_id = bv.budget_version_id and
852 bv.budget_version_id = po.fin_plan_version_id and
853 po.fin_plan_option_level_code = 'PLAN_VERSION' and
854 bl.start_date BETWEEN p_line_start_date and p_line_end_date
855 group by bl.resource_assignment_id,
856 bl.txn_currency_code,
857 ra.planning_start_date,
858 ra.planning_end_date,
859 po.margin_derived_from_code,
860 ra.schedule_start_date,
861 ra.schedule_end_date,
862 po.fin_plan_preference_code;
863
864 -- SELECT ra.planning_start_date,
865 -- ra.planning_end_date,
866 -- ra.schedule_start_date,
867 -- ra.schedule_end_date,
868 -- rac.total_display_quantity,
869 -- rac.total_txn_revenue,
870 -- rac.total_project_revenue, --ra.total_project_revenue,
871 -- rac.total_projfunc_revenue, --ra.total_plan_revenue,
872 -- rac.total_txn_raw_cost,
873 -- rac.total_project_raw_cost, --ra.total_project_raw_cost,
874 -- rac.total_projfunc_raw_cost, --ra.total_plan_raw_cost,
875 -- rac.total_txn_burdened_cost,
876 -- rac.total_project_burdened_cost, --ra.total_project_burdened_cost,
877 -- rac.total_projfunc_burdened_cost, --ra.total_plan_burdened_cost,
878 -- /*
879 -- SUM(bl.quantity),
880 -- SUM(bl.txn_revenue),
881 -- SUM(bl.project_revenue), --ra.total_project_revenue,
882 -- SUM(bl.revenue), --ra.total_plan_revenue,
883 -- SUM(bl.txn_raw_cost),
884 -- SUM(bl.project_raw_cost), --ra.total_project_raw_cost,
885 -- SUM(bl.raw_cost), --ra.total_plan_raw_cost,
886 -- SUM(bl.txn_burdened_cost),
887 -- SUM(bl.project_burdened_cost), --ra.total_project_burdened_cost,
888 -- SUM(bl.burdened_cost), --ra.total_plan_burdened_cost,
889 -- */
890 -- NULL, -- x_init_rev_rate (TO BE CALCULATED)
891 -- /*
892 -- DECODE(SUM(bl.quantity),
893 -- 0, 0,
894 -- NULL, NULL,
895 -- SUM(bl.txn_revenue)/SUM(bl.quantity)),
896 -- */
897 -- DECODE(ra.rate_based_flag, 'Y', rac.txn_average_bill_rate, TO_NUMBER(NULL)),
898 -- NULL, -- x_init_raw_cost_rate (TO BE CALCULATED)
899 -- /*
900 -- DECODE(SUM(bl.quantity),
901 -- 0, 0,
902 -- NULL, NULL,
903 -- SUM(bl.txn_raw_cost)/SUM(bl.quantity)),
904 -- */
905 -- DECODE(ra.rate_based_flag, 'Y', rac.txn_average_raw_cost_rate, TO_NUMBER(NULL)),
906 -- NULL, -- x_init_burd_cost_rate (TO BE CALCULATED)
907 -- /*
908 -- DECODE(SUM(bl.quantity),
909 -- 0, 0,
910 -- NULL, NULL,
911 -- SUM(bl.txn_burdened_cost)/SUM(bl.quantity)),
912 -- */
913 -- DECODE(ra.rate_based_flag, 'Y', rac.txn_average_burden_cost_rate, TO_NUMBER(NULL)),
914 -- DECODE(po.fin_plan_preference_code,
915 -- 'COST_ONLY', TO_NUMBER(NULL),
916 -- 'REVENUE_ONLY', TO_NUMBER(NULL),
917 -- DECODE(po.margin_derived_from_code,
918 -- -- 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
919 -- -- SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))),
920 -- 'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
921 -- rac.total_txn_revenue - rac.total_txn_raw_cost)),
922 -- DECODE(po.fin_plan_preference_code,
923 -- 'COST_ONLY', TO_NUMBER(NULL),
924 -- 'REVENUE_ONLY', TO_NUMBER(NULL),
925 -- DECODE(po.margin_derived_from_code,
926 -- -- 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
927 -- -- SUM(bl.project_revenue) - SUM(bl.project_raw_cost))),
928 -- 'B', rac.total_project_revenue - rac.total_project_burdened_cost,
929 -- rac.total_project_revenue - rac.total_project_raw_cost)),
930 -- DECODE(po.fin_plan_preference_code,
931 -- 'COST_ONLY', TO_NUMBER(NULL),
932 -- 'REVENUE_ONLY', TO_NUMBER(NULL),
933 -- DECODE(po.margin_derived_from_code,
934 -- -- 'B', SUM(bl.reve nue) - SUM(bl.burdened_cost),
935 -- -- SUM(bl.revenue) - SUM(bl.raw_cost))),
936 -- 'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
937 -- rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)),
938 -- DECODE(po.fin_plan_preference_code,
939 -- 'COST_ONLY', TO_NUMBER(NULL),
940 -- 'REVENUE_ONLY', TO_NUMBER(NULL),
941 -- -- DECODE(SUM(bl.project_revenue),
942 -- DECODE(rac.total_project_revenue,
943 -- 0, 0,
944 -- NULL, TO_NUMBER(NULL),
945 -- DECODE(po.margin_derived_from_code,
946 -- -- 'B', 100*(SUM(bl.project_revenue) - SUM(bl.project_burdened_cost))/SUM(bl.project_revenue),
947 -- -- 100*(SUM(bl.project_revenue) - SUM(bl.project_raw_cost)))/SUM(bl.project_revenue))),
948 -- 'B', 100*(rac.total_project_revenue - rac.total_project_burdened_cost)/rac.total_project_revenue,
949 -- 100*(rac.total_project_revenue - rac.total_project_raw_cost)/rac.total_project_revenue))),
950 -- /*
951 -- DECODE(SUM(bl.quantity) - SUM(NVL(bl.init_quantity,0)),
952 -- 0, 0,
953 -- NULL, 0,
954 -- (SUM(bl.txn_revenue) - SUM(NVL(bl.txn_init_revenue,0)))/(SUM(bl.quantity) - SUM(NVL(bl.init_quantity,0)))),
955 -- */
956 -- DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_bill_rate, TO_NUMBER(NULL)),
957 -- /*
958 -- DECODE(SUM(bl.quantity) - SUM(NVL(init_quantity,0)),
959 -- 0, 0,
960 -- NULL, 0,
961 -- (SUM(bl.txn_raw_cost) - SUM(NVL(bl.txn_init_raw_cost,0)))/(SUM(bl.quantity) - SUM(NVL(init_quantity,0)))),
962 -- */
963 -- DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_raw_cost_rate, TO_NUMBER(NULL)),
964 -- /*
965 -- DECODE(SUM(bl.quantity) - SUM(NVL(init_quantity,0)),
966 -- 0, 0,
967 -- NULL, 0,
968 -- (SUM(bl.txn_burdened_cost) - SUM(NVL(bl.txn_init_burdened_cost,0)))/(SUM(bl.quantity) - SUM(NVL(init_quantity,0))))
969 -- */
970 -- DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_burden_cost_rate, TO_NUMBER(NULL))
971 -- FROM pa_resource_assignments ra,
972 -- -- pa_budget_lines bl,
973 -- pa_resource_asgn_curr rac,
974 -- pa_budget_versions bv,
975 -- pa_proj_fp_options po
976 -- WHERE ra.resource_assignment_id = l_resource_assignment_id AND
977 -- -- ra.resource_assignment_id = bl.resource_assignment_id AND
978 -- -- bl.txn_currency_code = p_txn_currency_code AND
979 -- ra.resource_assignment_id = rac.resource_assignment_id AND
980 -- rac.txn_currency_code = p_txn_currency_code AND
981 -- ra.budget_version_id = bv.budget_version_id AND
982 -- bv.budget_version_id = po.fin_plan_version_id AND
983 -- po.fin_plan_option_level_code = 'PLAN_VERSION';
984 -- -- bl.start_date BETWEEN p_line_start_date AND p_line_end_date
985 -- /*
986 -- GROUP BY bl.resource_assignment_id,
987 -- bl.txn_currency_code,
988 -- ra.planning_start_date,
989 -- ra.planning_end_date,
990 -- po.margin_derived_from_code,
991 -- ra.schedule_start_date,
992 -- ra.schedule_end_date,
993 -- po.fin_plan_preference_code;
994 -- */
995 -- End bug 6836806.
996
997 -- ERROR HANDLING VARIABLES
998 l_return_status VARCHAR2(1);
999 l_msg_count NUMBER :=0;
1000 l_data VARCHAR2(2000);
1001 l_msg_data VARCHAR2(2000);
1002 l_error_msg_code VARCHAR2(30);
1003 l_msg_index_out NUMBER;
1004 BEGIN
1005 x_return_status := FND_API.G_RET_STS_SUCCESS;
1006 if (p_budget_version_id is null) or (p_resource_assignment_id is null) then
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1009 p_msg_name => 'PA_FP_COMMONVO_ERROR');
1010 else
1011 x_budget_version_id := p_budget_version_id;
1012 -- NOTE: p_resource_assignment_id refers to the resource assignment for the
1013 -- CURRENT DISPLAYED budget version. We need to figure out the resource
1014 -- assignment for the resource that correlates to p_budget_version_id
1015
1016 -- get task_id and resource_list_member_id that we will need to match
1017 select ra.project_id,
1018 ra.task_id,
1019 ra.resource_list_member_id,
1020 ra.unit_of_measure
1021 into l_project_id,
1022 l_task_id,
1023 l_resource_list_member_id,
1024 l_unit_of_measure
1025 from pa_resource_assignments ra
1026 where ra.resource_assignment_id = p_resource_assignment_id;
1027 open raid_csr;
1028 fetch raid_csr into raid_rec;
1029 if raid_csr%NOTFOUND then
1030 -- no match found: return null for all attribute values
1031 x_planning_start_date := null;
1032 x_planning_end_date := null;
1033 x_schedule_start_date := null;
1034 x_schedule_end_date := null;
1035 x_quantity := null;
1036 x_revenue_txn_cur := null;
1037 x_revenue_proj_cur := null;
1038 x_revenue_proj_func_cur := null;
1039 x_raw_cost_txn_cur := null;
1040 x_raw_cost_proj_cur := null;
1041 x_raw_cost_proj_func_cur := null;
1042 x_burd_cost_txn_cur := null;
1043 x_burd_cost_proj_cur := null;
1044 x_burd_cost_proj_func_cur := null;
1045 x_init_rev_rate := null;
1046 x_avg_rev_rate := null;
1047 x_init_raw_cost_rate := null;
1048 x_avg_raw_cost_rate := null;
1049 x_init_burd_cost_rate := null;
1050 x_avg_burd_cost_rate := null;
1051 x_margin_txn_cur := null;
1052 x_margin_proj_cur := null;
1053 x_margin_proj_func_cur := null;
1054 x_margin_pct := null;
1055 x_etc_avg_rev_rate := null;
1056 x_etc_avg_raw_cost_rate := null;
1057 x_etc_avg_burd_cost_rate := null;
1058
1059 else
1060 l_resource_assignment_id := raid_rec.resource_assignment_id;
1061 -- figure out whether or not budget lines exist for the planning element
1062 open budget_lines_csr;
1063 fetch budget_lines_csr into budget_lines_rec;
1064 if budget_lines_csr%NOTFOUND then
1065 l_budget_lines_exist_flag := 'N';
1066 else
1067 l_budget_lines_exist_flag := 'Y';
1068 end if;
1069 close budget_lines_csr;
1070
1071 if l_budget_lines_exist_flag = 'N' then
1072 -- query pa_resource_assignments only
1073 x_init_rev_rate := null;
1074 x_avg_rev_rate := null;
1075 x_init_raw_cost_rate := null;
1076 x_avg_raw_cost_rate := null;
1077 x_init_burd_cost_rate := null;
1078 x_avg_burd_cost_rate := null;
1079 -- bug 3979904: query pa_resource_assignments only if p_txn_currency_code
1080 -- is the same as project currency or project functional currency
1081 -- bug 4091886: if no budget lines exist for a particular task-res-currency
1082 -- combination, then even PC and PFC values should be nulled-out
1083 begin
1084 select ra.planning_start_date, -- x_planning_start_date
1085 ra.planning_end_date, -- x_planning_end_date
1086 ra.schedule_start_date, -- x_schedule_start_date
1087 ra.schedule_end_date, -- x_schedule_end_date
1088 to_number(null), -- x_quantity
1089 /*
1090 DECODE(p_txn_currency_code,
1091 p_project_currency_code, ra.total_plan_quantity,
1092 p_projfunc_currency_code, ra.total_plan_quantity,
1093 to_number(null)), -- x_quantity
1094 */
1095 to_number(null), -- x_revenue_txn_cur
1096 /*
1097 DECODE(p_txn_currency_code,
1098 p_project_currency_code, ra.total_project_revenue,
1099 p_projfunc_currency_code, ra.total_plan_revenue,
1100 to_number(null)), -- x_revenue_txn_cur
1101 */
1102 to_number(null), /*ra.total_project_revenue,*/ -- x_revenue_proj_cur
1103 to_number(null), /*ra.total_plan_revenue,*/ -- x_revenue_proj_func_cur
1104 to_number(null), -- x_raw_cost_txn_cur
1105 /*
1106 DECODE(p_txn_currency_code,
1107 p_project_currency_code, ra.total_project_raw_cost,
1108 p_projfunc_currency_code, ra.total_plan_raw_cost,
1109 to_number(null)), -- x_raw_cost_txn_cur
1110 */
1111 to_number(null), /*ra.total_project_raw_cost,*/ -- x_raw_cost_proj_cur
1112 to_number(null), /*ra.total_plan_raw_cost,*/ -- x_raw_cost_proj_func_cur
1113 to_number(null), -- x_burd_cost_txn_cur
1114 /*
1115 DECODE(p_txn_currency_code,
1116 p_project_currency_code, ra.total_project_burdened_cost,
1117 p_projfunc_currency_code, ra.total_plan_burdened_cost,
1118 to_number(null)), -- x_burd_cost_txn_cur
1119 */
1120 to_number(null), /*ra.total_project_burdened_cost,*/ -- x_burd_cost_proj_cur
1121 to_number(null), /*ra.total_plan_burdened_cost,*/ -- x_burd_cost_proj_func_cur
1122 to_number(null), -- x_margin_txn_cur
1123 /*
1124 DECODE(p_txn_currency_code,
1125 p_project_currency_code, DECODE(po.margin_derived_from_code,
1126 'B', ra.total_project_revenue - ra.total_project_burdened_cost,
1127 ra.total_project_revenue - ra.total_project_raw_cost),
1128 p_projfunc_currency_code, DECODE(po.margin_derived_from_code,
1129 'B', ra.total_plan_revenue - ra.total_plan_burdened_cost,
1130 ra.total_plan_revenue - ra.total_plan_raw_cost),
1131 to_number(null)), -- x_margin_txn_cur
1132 */
1133 to_number(null),
1134 /* DECODE(po.margin_derived_from_code,
1135 'B', ra.total_project_revenue - ra.total_project_burdened_cost,
1136 ra.total_project_revenue - ra.total_project_raw_cost), -- x_margin_proj_cur
1137 */
1138 to_number(null),
1139 /* DECODE(po.margin_derived_from_code,
1140 'B', ra.total_plan_revenue - ra.total_plan_burdened_cost,
1141 ra.total_plan_revenue - ra.total_plan_raw_cost), -- x_margin_proj_func_cur
1142 */
1143 DECODE(po.fin_plan_preference_code,
1144 'COST_ONLY', to_number(null),
1145 'REVENUE_ONLY', to_number(null),
1146 DECODE(nvl(ra.total_project_revenue,0),
1147 0, 0,
1148 DECODE(po.margin_derived_from_code,
1149 'B', 100*(ra.total_project_revenue - ra.total_project_burdened_cost)/ra.total_project_revenue,
1150 100*(ra.total_project_revenue - ra.total_project_raw_cost)/ra.total_project_revenue))) -- x_margin_pct
1151 into x_planning_start_date,
1152 x_planning_end_date,
1153 x_schedule_start_date,
1154 x_schedule_end_date,
1155 x_quantity,
1156 x_revenue_txn_cur,
1157 x_revenue_proj_cur,
1158 x_revenue_proj_func_cur,
1159 x_raw_cost_txn_cur,
1160 x_raw_cost_proj_cur,
1161 x_raw_cost_proj_func_cur,
1162 x_burd_cost_txn_cur,
1163 x_burd_cost_proj_cur,
1164 x_burd_cost_proj_func_cur,
1165 x_margin_txn_cur,
1166 x_margin_proj_cur,
1167 x_margin_proj_func_cur,
1168 x_margin_pct
1169 from pa_resource_assignments ra,
1170 pa_budget_versions bv,
1171 pa_proj_fp_options po
1172 where ra.resource_assignment_id = l_resource_assignment_id and
1173 ra.budget_version_id = bv.budget_version_id and
1174 bv.budget_version_id = po.fin_plan_version_id and
1175 po.fin_plan_option_level_code = 'PLAN_VERSION';
1176 exception
1177 when NO_DATA_FOUND then
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 x_msg_count := 1;
1180 x_msg_data := SQLERRM;
1181 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
1182 p_procedure_name => 'get_common_budget_version_info');
1183 end;
1184 else
1185 -- budget lines exist, so query pa_resource_assignment and pa_budget_lines
1186 -- Bug Fix 3732157.
1187 -- Budget lines cursor is not considering the dates while checking the
1188 -- lines existence. While fetching the data within a certain date range
1189 -- is failing as the lines doesnt exist in that range.
1190 -- Making this as a cursor to get rid of the no data found exception
1191 -- and if cursor not found then we initialize all the out vars to null.
1192
1193 IF p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL THEN
1194
1195 OPEN get_budget_line_amts_for_dates;
1196 FETCH get_budget_line_amts_for_dates INTO
1197 x_planning_start_date,
1198 x_planning_end_date,
1199 x_schedule_start_date,
1200 x_schedule_end_date,
1201 x_quantity,
1202 x_revenue_txn_cur,
1203 x_revenue_proj_cur,
1204 x_revenue_proj_func_cur,
1205 x_raw_cost_txn_cur,
1206 x_raw_cost_proj_cur,
1207 x_raw_cost_proj_func_cur,
1208 x_burd_cost_txn_cur,
1209 x_burd_cost_proj_cur,
1210 x_burd_cost_proj_func_cur,
1211 x_init_rev_rate,
1212 x_avg_rev_rate,
1213 x_init_raw_cost_rate,
1214 x_avg_raw_cost_rate,
1215 x_init_burd_cost_rate,
1216 x_avg_burd_cost_rate,
1217 x_margin_txn_cur,
1218 x_margin_proj_cur,
1219 x_margin_proj_func_cur,
1220 x_margin_pct,
1221 x_etc_avg_rev_rate,
1222 x_etc_avg_raw_cost_rate,
1223 x_etc_avg_burd_cost_rate;
1224
1225 IF get_budget_line_amts_for_dates%NOTFOUND THEN
1226
1227 x_planning_start_date := null;
1228 x_planning_end_date := null;
1229 x_schedule_start_date := null;
1230 x_schedule_end_date := null;
1231 x_quantity := null;
1232 x_revenue_txn_cur := null;
1233 x_revenue_proj_cur := null;
1234 x_revenue_proj_func_cur := null;
1235 x_raw_cost_txn_cur := null;
1236 x_raw_cost_proj_cur := null;
1237 x_raw_cost_proj_func_cur := null;
1238 x_burd_cost_txn_cur := null;
1239 x_burd_cost_proj_cur := null;
1240 x_burd_cost_proj_func_cur := null;
1241 x_init_rev_rate := null;
1242 x_avg_rev_rate := null;
1243 x_init_raw_cost_rate := null;
1244 x_avg_raw_cost_rate := null;
1245 x_init_burd_cost_rate := null;
1246 x_avg_burd_cost_rate := null;
1247 x_margin_txn_cur := null;
1248 x_margin_proj_cur := null;
1249 x_margin_proj_func_cur := null;
1250 x_margin_pct := null;
1251 x_etc_avg_rev_rate := null;
1252 x_etc_avg_raw_cost_rate := null;
1253 x_etc_avg_burd_cost_rate := null;
1254
1255 END IF;
1256
1257 CLOSE get_budget_line_amts_for_dates;
1258
1259 ELSE -- p_line_start_date IS NULL AND/OR p_line_end_date IS NULL THEN
1260
1261 SELECT ra.planning_start_date,
1262 ra.planning_end_date,
1263 ra.schedule_start_date,
1264 ra.schedule_end_date,
1265 rac.total_display_quantity,
1266 rac.total_txn_revenue,
1267 rac.total_project_revenue, --ra.total_project_revenue,
1268 rac.total_projfunc_revenue, --ra.total_plan_revenue,
1269 rac.total_txn_raw_cost,
1270 rac.total_project_raw_cost, --ra.total_project_raw_cost,
1271 rac.total_projfunc_raw_cost, --ra.total_plan_raw_cost,
1272 rac.total_txn_burdened_cost,
1273 rac.total_project_burdened_cost, --ra.total_project_burdened_cost,
1274 rac.total_projfunc_burdened_cost, --ra.total_plan_burdened_cost,
1275 /*
1276 SUM(bl.quantity),
1277 SUM(bl.txn_revenue),
1278 SUM(bl.project_revenue), --ra.total_project_revenue,
1279 SUM(bl.revenue), --ra.total_plan_revenue,
1280 SUM(bl.txn_raw_cost),
1281 SUM(bl.project_raw_cost), --ra.total_project_raw_cost,
1282 SUM(bl.raw_cost), --ra.total_plan_raw_cost,
1283 SUM(bl.txn_burdened_cost),
1284 SUM(bl.project_burdened_cost), --ra.total_project_burdened_cost,
1285 SUM(bl.burdened_cost), --ra.total_plan_burdened_cost,
1286 */
1287 NULL, -- x_init_rev_rate (TO BE CALCULATED)
1288 --AVG(nvl(bl.txn_bill_rate_override,bl.txn_standard_bill_rate)),
1289 /*
1290 DECODE(SUM(bl.quantity),
1291 0, 0,
1292 NULL, NULL,
1293 SUM(bl.txn_revenue)/SUM(bl.quantity)),
1294 */
1295 DECODE(ra.rate_based_flag, 'Y', rac.txn_average_bill_rate, TO_NUMBER(NULL)),
1296 NULL, -- x_init_raw_cost_rate (TO BE CALCULATED)
1297 --AVG(nvl(bl.txn_cost_rate_override,bl.txn_standard_cost_rate)),
1298 /*
1299 DECODE(SUM(bl.quantity),
1300 0, 0,
1301 NULL, NULL,
1302 SUM(bl.txn_raw_cost)/SUM(bl.quantity)),
1303 */
1304 DECODE(ra.rate_based_flag, 'Y', rac.txn_average_raw_cost_rate, TO_NUMBER(NULL)),
1305 NULL, -- x_init_burd_cost_rate (TO BE CALCULATED)
1306 --AVG(nvl(bl.burden_cost_rate_override,bl.burden_cost_rate)),
1307 /*
1308 DECODE(SUM(bl.quantity),
1309 0, 0,
1310 NULL, NULL,
1311 SUM(bl.txn_burdened_cost)/SUM(bl.quantity)),
1312 */
1313 DECODE(ra.rate_based_flag, 'Y', rac.txn_average_burden_cost_rate, TO_NUMBER(NULL)),
1314 DECODE(po.fin_plan_preference_code,
1315 'COST_ONLY', TO_NUMBER(NULL),
1316 'REVENUE_ONLY', TO_NUMBER(NULL),
1317 DECODE(po.margin_derived_from_code,
1318 -- 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
1319 -- SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))),
1320 'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
1321 rac.total_txn_revenue - rac.total_txn_raw_cost)),
1322 DECODE(po.fin_plan_preference_code,
1323 'COST_ONLY', TO_NUMBER(NULL),
1324 'REVENUE_ONLY', TO_NUMBER(NULL),
1325 DECODE(po.margin_derived_from_code,
1326 -- 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
1327 -- SUM(bl.project_revenue) - SUM(bl.project_raw_cost))),
1328 'B', rac.total_project_revenue - rac.total_project_burdened_cost,
1329 rac.total_project_revenue - rac.total_project_raw_cost)),
1330 DECODE(po.fin_plan_preference_code,
1331 'COST_ONLY', TO_NUMBER(NULL),
1332 'REVENUE_ONLY', TO_NUMBER(NULL),
1333 DECODE(po.margin_derived_from_code,
1334 -- 'B', SUM(bl.reve nue) - SUM(bl.burdened_cost),
1335 -- SUM(bl.revenue) - SUM(bl.raw_cost))),
1336 'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
1337 rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)),
1338 DECODE(po.fin_plan_preference_code,
1339 'COST_ONLY', TO_NUMBER(NULL),
1340 'REVENUE_ONLY', TO_NUMBER(NULL),
1341 -- DECODE(SUM(bl.project_revenue),
1342 DECODE(rac.total_project_revenue,
1343 0, 0,
1344 NULL, TO_NUMBER(NULL),
1345 DECODE(po.margin_derived_from_code,
1346 -- 'B', 100*(SUM(bl.project_revenue) - SUM(bl.project_burdened_cost))/SUM(bl.project_revenue),
1347 -- 100*(SUM(bl.project_revenue) - SUM(bl.project_raw_cost)))/SUM(bl.project_revenue))),
1348 'B', 100*(rac.total_project_revenue - rac.total_project_burdened_cost)/rac.total_project_revenue,
1349 100*(rac.total_project_revenue - rac.total_project_raw_cost)/rac.total_project_revenue))),
1350 /*
1351 DECODE(SUM(bl.quantity) - SUM(NVL(bl.init_quantity,0)),
1352 0, 0,
1353 NULL, 0,
1354 (SUM(bl.txn_revenue) - SUM(NVL(bl.txn_init_revenue,0)))/(SUM(bl.quantity) - SUM(NVL(bl.init_quantity,0)))),
1355 */
1356 DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_bill_rate, TO_NUMBER(NULL)),
1357 /*
1358 DECODE(SUM(bl.quantity) - SUM(NVL(init_quantity,0)),
1359 0, 0,
1360 NULL, 0,
1361 (SUM(bl.txn_raw_cost) - SUM(NVL(bl.txn_init_raw_cost,0)))/(SUM(bl.quantity) - SUM(NVL(init_quantity,0)))),
1362 */
1363 DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_raw_cost_rate, TO_NUMBER(NULL)),
1364 /*
1365 DECODE(SUM(bl.quantity) - SUM(NVL(init_quantity,0)),
1366 0, 0,
1367 NULL, 0,
1368 (SUM(bl.txn_burdened_cost) - SUM(NVL(bl.txn_init_burdened_cost,0)))/(SUM(bl.quantity) - SUM(NVL(init_quantity,0))))
1369 */
1370 DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_burden_cost_rate, TO_NUMBER(NULL))
1371 INTO x_planning_start_date,
1372 x_planning_end_date,
1373 x_schedule_start_date,
1374 x_schedule_end_date,
1375 x_quantity,
1376 x_revenue_txn_cur,
1377 x_revenue_proj_cur,
1378 x_revenue_proj_func_cur,
1379 x_raw_cost_txn_cur,
1380 x_raw_cost_proj_cur,
1381 x_raw_cost_proj_func_cur,
1382 x_burd_cost_txn_cur,
1383 x_burd_cost_proj_cur,
1384 x_burd_cost_proj_func_cur,
1385 x_init_rev_rate,
1386 x_avg_rev_rate,
1387 x_init_raw_cost_rate,
1388 x_avg_raw_cost_rate,
1389 x_init_burd_cost_rate,
1390 x_avg_burd_cost_rate,
1391 x_margin_txn_cur,
1392 x_margin_proj_cur,
1393 x_margin_proj_func_cur,
1394 x_margin_pct,
1395 x_etc_avg_rev_rate,
1396 x_etc_avg_raw_cost_rate,
1397 x_etc_avg_burd_cost_rate
1398 FROM pa_resource_assignments ra,
1399 -- pa_budget_lines bl,
1400 pa_resource_asgn_curr rac,
1401 pa_budget_versions bv,
1402 pa_proj_fp_options po
1403 WHERE ra.resource_assignment_id = l_resource_assignment_id AND
1404 -- ra.resource_assignment_id = bl.resource_assignment_id AND
1405 -- bl.txn_currency_code = p_txn_currency_code AND
1406 rac.resource_assignment_id = ra.resource_assignment_id AND
1407 rac.txn_currency_code = p_txn_currency_code AND
1408 ra.budget_version_id = bv.budget_version_id AND
1409 bv.budget_version_id = po.fin_plan_version_id AND
1410 po.fin_plan_option_level_code = 'PLAN_VERSION';
1411 /*
1412 GROUP BY bl.resource_assignment_id,
1413 bl.txn_currency_code,
1414 ra.planning_start_date,
1415 ra.planning_end_date,
1416 ra.schedule_start_date,
1417 ra.schedule_end_date,
1418 --ra.total_project_revenue,
1419 --ra.total_plan_revenue,
1420 --ra.total_project_raw_cost,
1421 --ra.total_plan_raw_cost,
1422 --ra.total_project_burdened_cost,
1423 --ra.total_plan_burdened_cost,
1424 po.margin_derived_from_code,
1425 po.fin_plan_preference_code;
1426 */
1427
1428
1429 END IF; -- p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL
1430 -- CALCULATE THE AVG RATES
1431 pa_planning_element_utils.get_initial_budget_line_info
1432 (p_resource_assignment_id => p_resource_assignment_id,
1433 p_txn_currency_code => p_txn_currency_code,
1434 p_line_start_date => p_line_start_date,
1435 p_line_end_date => p_line_end_date,
1436 x_start_date => l_start_date,
1437 x_end_date => l_end_date,
1438 x_period_name => l_period_name,
1439 x_quantity => l_quantity,
1440 x_txn_raw_cost => l_txn_raw_cost,
1441 x_txn_burdened_cost => l_txn_burdened_cost,
1442 x_txn_revenue => l_txn_revenue,
1443 x_init_quantity => l_init_quantity,
1444 x_txn_init_raw_cost => l_txn_init_raw_cost,
1445 x_txn_init_burdened_cost => l_txn_init_burdened_cost,
1446 x_txn_init_revenue => l_txn_init_revenue,
1447 x_init_raw_cost_rate => l_init_raw_cost_rate,
1448 x_init_burd_cost_rate => l_init_burd_cost_rate,
1449 x_init_revenue_rate => l_init_revenue_rate,
1450 x_etc_init_raw_cost_rate => l_etc_init_raw_cost_rate,
1451 x_etc_init_burd_cost_rate => l_etc_init_burd_cost_rate,
1452 x_etc_init_revenue_rate => l_etc_init_revenue_rate,
1453 x_return_status => l_return_status,
1454 x_msg_count => l_msg_count,
1455 x_msg_data => l_msg_data);
1456
1457 x_init_rev_rate := l_init_revenue_rate;
1458 x_init_raw_cost_rate := l_init_raw_cost_rate;
1459 x_init_burd_cost_rate := l_init_burd_cost_rate;
1460
1461 end if;
1462 end if;
1463 end if; -- if l_resource_assignment_id found
1464 close raid_csr;
1465
1466 -- Check message stack for error messages
1467 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1468 l_msg_count := FND_MSG_PUB.count_msg;
1469 if l_msg_count = 1 THEN
1470 PA_INTERFACE_UTILS_PUB.get_messages
1471 (p_encoded => FND_API.G_TRUE
1472 ,p_msg_index => 1
1473 ,p_msg_count => l_msg_count
1474 ,p_msg_data => l_msg_data
1475 ,p_data => l_data
1476 ,p_msg_index_out => l_msg_index_out);
1477 x_msg_data := l_data;
1478 x_msg_count := l_msg_count;
1479 else
1480 x_msg_count := l_msg_count;
1481 end if;
1482 end if;
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 FND_MSG_PUB.ADD_EXC_MSG (p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
1486 p_procedure_name => 'get_common_budget_version_info');
1487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1488 x_msg_data := SQLERRM;
1489 END get_common_budget_version_info;
1490
1491
1492 /*
1493 * REVISION HISTORY:
1494 * 07/08/2004 - dlai - added the following input parameters so ETC rates can
1495 * be displayed for baselined and latest published versions (Task
1496 * Assignments): x_etc_avg_rev_rate, x_etc_avg_raw_cost_rate,
1497 * x_etc_avg_burd_cost_rate
1498 * 07/15/2004 - dlai - added the following output parameters for bug 3622609:
1499 * x_schedule_start_date, x_schedule_end_date
1500 * 12/10/2004 - dlai - when no budget lines exist for a particular
1501 * txn_currency_code (which is not proj or projfunc currency), be
1502 * sure to return null for txn amounts, and not rely on the amounts in
1503 * pa_resource_assignments
1504 * 12/19/2004 removed all references to pa_resource_assignments.init columns
1505 * 02/08/2005 - dlai - when looking for match, the records should also have
1506 * the same unit_of_measure in addition to task-res-txncurrency
1507 * 05/10/2005 - dlai - margin parameters are returned as null if
1508 * p_budget_version_id refers to COST_ONLY or REVENUE_ONLY version
1509 * 06/02/2005 - dlai - queries for etc amounts should use nvl(..,0) so that they
1510 * are calculate correctly in case the act or fcst amounts are null
1511 */
1512 PROCEDURE get_common_bv_info_fcst
1513 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
1514 p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
1515 p_project_currency_code IN pa_projects_all.project_currency_code%TYPE,
1516 p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE,
1517 p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
1518 p_line_start_date IN pa_budget_lines.start_date%TYPE := to_date(NULL),
1519 p_line_end_date IN pa_budget_lines.end_date%TYPE := to_date(NULL),
1520 x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
1521 x_planning_start_date OUT NOCOPY pa_resource_assignments.planning_start_date%TYPE, --File.Sql.39 bug 4440895
1522 x_planning_end_date OUT NOCOPY pa_resource_assignments.planning_end_date%TYPE, --File.Sql.39 bug 4440895
1523 x_schedule_start_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
1524 x_schedule_end_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
1525 x_act_quantity OUT NOCOPY pa_resource_assignments.total_plan_quantity%TYPE, --File.Sql.39 bug 4440895
1526 x_etc_quantity OUT NOCOPY pa_resource_assignments.total_plan_quantity%TYPE, --File.Sql.39 bug 4440895
1527 x_fcst_quantity OUT NOCOPY pa_resource_assignments.total_plan_quantity%TYPE, --File.Sql.39 bug 4440895
1528 x_act_revenue_txn_cur OUT NOCOPY pa_budget_lines.txn_revenue%TYPE, --File.Sql.39 bug 4440895
1529 x_act_revenue_proj_cur OUT NOCOPY pa_resource_assignments.total_project_revenue%TYPE, --File.Sql.39 bug 4440895
1530 x_act_revenue_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_revenue%TYPE, --File.Sql.39 bug 4440895
1531 x_etc_revenue_txn_cur OUT NOCOPY pa_budget_lines.txn_revenue%TYPE, --File.Sql.39 bug 4440895
1532 x_etc_revenue_proj_cur OUT NOCOPY pa_resource_assignments.total_project_revenue%TYPE, --File.Sql.39 bug 4440895
1533 x_etc_revenue_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_revenue%TYPE, --File.Sql.39 bug 4440895
1534 x_fcst_revenue_txn_cur OUT NOCOPY pa_budget_lines.txn_revenue%TYPE, --File.Sql.39 bug 4440895
1535 x_fcst_revenue_proj_cur OUT NOCOPY pa_resource_assignments.total_project_revenue%TYPE, --File.Sql.39 bug 4440895
1536 x_fcst_revenue_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_revenue%TYPE, --File.Sql.39 bug 4440895
1537 x_act_raw_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_raw_cost%TYPE, --File.Sql.39 bug 4440895
1538 x_act_raw_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_raw_cost%TYPE, --File.Sql.39 bug 4440895
1539 x_act_raw_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_raw_cost%TYPE, --File.Sql.39 bug 4440895
1540 x_etc_raw_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_raw_cost%TYPE, --File.Sql.39 bug 4440895
1541 x_etc_raw_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_raw_cost%TYPE, --File.Sql.39 bug 4440895
1542 x_etc_raw_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_raw_cost%TYPE, --File.Sql.39 bug 4440895
1543 x_fcst_raw_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_raw_cost%TYPE, --File.Sql.39 bug 4440895
1544 x_fcst_raw_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_raw_cost%TYPE, --File.Sql.39 bug 4440895
1545 x_fcst_raw_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_raw_cost%TYPE, --File.Sql.39 bug 4440895
1546 x_act_burd_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1547 x_act_burd_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1548 x_act_burd_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1549 x_etc_burd_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1550 x_etc_burd_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1551 x_etc_burd_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1552 x_fcst_burd_cost_txn_cur OUT NOCOPY pa_budget_lines.txn_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1553 x_fcst_burd_cost_proj_cur OUT NOCOPY pa_resource_assignments.total_project_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1554 x_fcst_burd_cost_proj_func_cur OUT NOCOPY pa_resource_assignments.total_plan_burdened_cost%TYPE, --File.Sql.39 bug 4440895
1555 x_act_rev_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
1556 x_etc_init_rev_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
1557 x_etc_avg_rev_rate OUT NOCOPY pa_budget_lines.txn_standard_bill_rate%TYPE, --File.Sql.39 bug 4440895
1558 x_act_raw_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
1559 x_etc_init_raw_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
1560 x_etc_avg_raw_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
1561 x_act_burd_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
1562 x_etc_init_burd_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
1563 x_etc_avg_burd_cost_rate OUT NOCOPY pa_budget_lines.txn_standard_cost_rate%TYPE, --File.Sql.39 bug 4440895
1564 x_act_margin_txn_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1565 x_act_margin_proj_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1566 x_act_margin_proj_func_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1567 x_etc_margin_txn_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1568 x_etc_margin_proj_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1569 x_etc_margin_proj_func_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1570 x_fcst_margin_txn_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1571 x_fcst_margin_proj_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1572 x_fcst_margin_proj_func_cur OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1573 x_act_margin_pct OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1574 x_etc_margin_pct OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1575 x_fcst_margin_pct OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1576 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1577 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1578 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
1579
1580 l_project_id pa_resource_assignments.project_id%TYPE;
1581 l_task_id pa_resource_assignments.task_id%TYPE;
1582 l_resource_list_member_id pa_resource_assignments.resource_list_member_id%TYPE;
1583 l_resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE;
1584 l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
1585 l_margin_derived_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
1586
1587 l_budget_lines_exist_flag VARCHAR2(1); -- whether budget lines exist for planning element
1588
1589 l_start_date pa_budget_lines.start_date%TYPE;
1590 l_end_date pa_budget_lines.end_date%TYPE;
1591 l_period_name pa_budget_lines.period_name%TYPE;
1592 l_quantity pa_budget_lines.quantity%TYPE;
1593 l_txn_raw_cost pa_budget_lines.raw_cost%TYPE;
1594 l_txn_burdened_cost pa_budget_lines.burdened_cost%TYPE;
1595 l_txn_revenue pa_budget_lines.revenue%TYPE;
1596 l_init_quantity pa_budget_lines.init_quantity%TYPE;
1597 l_txn_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
1598 l_txn_init_burdened_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
1599 l_txn_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
1600 l_init_raw_cost_rate NUMBER;
1601 l_init_burd_cost_rate NUMBER;
1602 l_init_revenue_rate NUMBER;
1603 l_etc_init_raw_cost_rate NUMBER;
1604 l_etc_init_burd_cost_rate NUMBER;
1605 l_etc_init_revenue_rate NUMBER;
1606
1607 cursor raid_csr is
1608 select ra.resource_assignment_id
1609 from pa_resource_assignments ra
1610 where ra.project_id = l_project_id and
1611 ra.budget_version_id = p_budget_version_id and
1612 ra.task_id = l_task_id and
1613 ra.resource_list_member_id = l_resource_list_member_id and
1614 ra.unit_of_measure = l_unit_of_measure;
1615 raid_rec raid_csr%ROWTYPE;
1616
1617 cursor budget_lines_csr is
1618 select 'Y'
1619 from pa_budget_lines
1620 where resource_assignment_id = l_resource_assignment_id and
1621 txn_currency_code = p_txn_currency_code;
1622 budget_lines_rec budget_lines_csr%ROWTYPE;
1623
1624 CURSOR get_fcst_line_amts_for_dates IS
1625 SELECT ra.planning_start_date, -- x_planning_start_date
1626 ra.planning_end_date, -- x_planning_end_date
1627 ra.schedule_start_date, -- x_schedule_start_date
1628 ra.schedule_end_date, -- x_schedule_end_date
1629 rac.total_init_quantity, -- x_act_quantity
1630 DECODE(ra.rate_based_flag, 'Y', NVL(rac.total_display_quantity,0) - NVL(rac.total_init_quantity,0), 0), -- x_etc_quantity, Bug 5726773
1631 rac.total_display_quantity, -- x_fcst_quantity
1632 rac.total_txn_init_revenue, -- x_act_revenue_txn_cur
1633 rac.total_project_init_revenue, -- x_act_revenue_proj_cur
1634 rac.total_projfunc_init_revenue, -- x_act_revenue_proj_func_cur
1635 NVL(rac.total_txn_revenue,0) - NVL(rac.total_txn_init_revenue,0), -- x_etc_revenue_txn_cur
1636 NVL(rac.total_project_revenue,0) - NVL(rac.total_project_init_revenue,0), -- x_etc_revenue_proj_cur
1637 NVL(rac.total_projfunc_revenue,0) - NVL(rac.total_projfunc_init_revenue,0), -- x_etc_revenue_proj_func_cur
1638 rac.total_txn_revenue, -- x_fcst_revenue_txn_cur
1639 rac.total_project_revenue, -- x_fcst_revenue_proj_cur
1640 rac.total_projfunc_revenue, -- x_fcst_revenue_proj_func_cur
1641 rac.total_txn_init_raw_cost, -- x_act_raw_cost_txn_cur
1642 rac.total_project_init_raw_cost, -- x_act_raw_cost_proj_cur
1643 rac.total_projfunc_init_raw_cost, -- x_act_raw_cost_proj_func_cur
1644 NVL(rac.total_txn_raw_cost,0) - NVL(rac.total_txn_init_raw_cost,0), -- x_etc_raw_cost_txn_cur
1645 NVL(rac.total_project_raw_cost,0) - NVL(rac.total_project_init_raw_cost,0),-- x_etc_raw_cost_proj_cur
1646 NVL(rac.total_projfunc_raw_cost,0) - NVL(rac.total_projfunc_init_raw_cost,0),-- x_etc_raw_cost_proj_func_cur
1647 rac.total_txn_raw_cost, -- x_fcst_raw_cost_txn_cur
1648 rac.total_project_raw_cost, -- x_fcst_raw_cost_proj_cur
1649 rac.total_projfunc_raw_cost, -- x_fcst_raw_cost_proj_func_cur
1650 rac.total_txn_init_burdened_cost, -- x_act_burd_cost_txn_cur
1651 rac.total_project_init_bd_cost, -- x_act_burd_cost_proj_cur
1652 rac.total_projfunc_init_bd_cost, -- x_act_burd_cost_proj_func_cur
1653 NVL(rac.total_txn_burdened_cost,0) - NVL(rac.total_txn_init_burdened_cost,0), -- x_etc_burd_cost_txn_cur
1654 NVL(rac.total_project_burdened_cost,0) - NVL(rac.total_project_init_bd_cost,0), -- x_etc_burd_cost_proj_cur
1655 NVL(rac.total_projfunc_burdened_cost,0) - NVL(rac.total_projfunc_init_bd_cost,0), -- x_etc_burd_cost_proj_func_cur
1656 rac.total_txn_burdened_cost, -- x_fcst_burd_cost_txn_cur
1657 rac.total_project_burdened_cost, -- x_fcst_burd_cost_proj_cur
1658 rac.total_projfunc_burdened_cost, -- x_fcst_burd_cost_proj_func_cur
1659 /*
1660 SUM(bl.init_quantity), -- x_act_quantity
1661 SUM(NVL(bl.quantity,0)) - SUM(NVL(bl.init_quantity,0)), -- x_etc_quantity
1662 SUM(bl.quantity), -- x_fcst_quantity
1663 SUM(txn_init_revenue), -- x_act_revenue_txn_cur
1664 SUM(bl.project_init_revenue), -- x_act_revenue_proj_cur
1665 SUM(bl.init_revenue), -- x_act_revenue_proj_func_cur
1666 SUM(NVL(bl.txn_revenue,0)) - SUM(NVL(bl.txn_init_revenue,0)), -- x_etc_revenue_txn_cur
1667 SUM(NVL(bl.project_revenue,0)) - SUM(NVL(bl.project_init_revenue,0)), -- x_etc_revenue_proj_cur
1668 SUM(NVL(bl.revenue,0)) - SUM(NVL(bl.init_revenue,0)), -- x_etc_revenue_proj_func_cur
1669 SUM(bl.txn_revenue), -- x_fcst_revenue_txn_cur
1670 SUM(bl.project_revenue), -- x_fcst_revenue_proj_cur
1671 SUM(bl.revenue), -- x_fcst_revenue_proj_func_cur
1672 SUM(bl.txn_init_raw_cost), -- x_act_raw_cost_txn_cur
1673 SUM(bl.project_init_raw_cost), -- x_act_raw_cost_proj_cur
1674 SUM(bl.init_raw_cost), -- x_act_raw_cost_proj_func_cur
1675 SUM(NVL(bl.txn_raw_cost,0)) - SUM(NVL(bl.txn_init_raw_cost,0)), -- x_etc_raw_cost_txn_cur
1676 SUM(NVL(bl.project_raw_cost,0)) - SUM(NVL(bl.project_init_raw_cost,0)), -- x_etc_raw_cost_proj_cur
1677 SUM(NVL(bl.raw_cost,0)) - SUM(NVL(bl.init_raw_cost,0)), -- x_etc_raw_cost_proj_func_cur
1678 SUM(bl.txn_raw_cost), -- x_fcst_raw_cost_txn_cur
1679 SUM(bl.project_raw_cost), -- x_fcst_raw_cost_proj_cur
1680 SUM(bl.raw_cost), -- x_fcst_raw_cost_proj_func_cur
1681 SUM(bl.txn_init_burdened_cost), -- x_act_burd_cost_txn_cur
1682 SUM(bl.project_init_burdened_cost), -- x_act_burd_cost_proj_cur
1683 SUM(bl.init_burdened_cost), -- x_act_burd_cost_proj_func_cur
1684 SUM(NVL(bl.txn_burdened_cost,0)) - SUM(NVL(bl.txn_init_burdened_cost,0)), -- x_etc_burd_cost_txn_cur
1685 SUM(NVL(bl.project_burdened_cost,0)) - SUM(NVL(bl.project_init_burdened_cost,0)), -- x_etc_burd_cost_proj_cur
1686 SUM(NVL(bl.burdened_cost,0)) - SUM(NVL(bl.init_burdened_cost,0)), -- x_etc_burd_cost_proj_func_cur
1687 SUM(bl.txn_burdened_cost), -- x_fcst_burd_cost_txn_cur
1688 SUM(bl.project_burdened_cost), -- x_fcst_burd_cost_proj_cur
1689 SUM(bl.burdened_cost), -- x_fcst_burd_cost_proj_func_cur
1690 */
1691 NULL, -- x_act_rev_rate (TO BE CALCULATED)
1692 NULL, -- x_etc_init_rev_rate (TO BE CALCULATED)
1693 NULL, -- x_etc_avg_rev_rate (TO BE CALCULATED)
1694 NULL, -- x_act_raw_cost_rate (TO BE CALCULATED)
1695 NULL, -- x_etc_init_raw_cost_rate (TO BE CALCULATED)
1696 NULL, -- x_etc_avg_raw_cost_rate (TO BE CALCULATED)
1697 NULL, -- x_act_burd_cost_rate (TO BE CALCULATED)
1698 NULL, -- x_etc_init_burd_cost_rate (TO BE CALCULATED)
1699 NULL, -- x_etc_avg_burd_cost_rate (TO BE CALCULATED)
1700 DECODE(po.fin_plan_preference_code,
1701 'COST_ONLY', TO_NUMBER(NULL),
1702 'REVENUE_ONLY', TO_NUMBER(NULL),
1703 DECODE(po.margin_derived_from_code,
1704 -- 'B', SUM(bl.txn_init_revenue) - SUM(bl.txn_init_burdened_cost),
1705 -- SUM(bl.txn_init_revenue) - SUM(bl.txn_init_raw_cost))), -- x_act_margin_txn_cur
1706 'B', rac.total_txn_init_revenue - rac.total_txn_init_burdened_cost,
1707 rac.total_txn_init_revenue - rac.total_txn_init_raw_cost)), -- x_act_margin_txn_cur
1708 DECODE(po.fin_plan_preference_code,
1709 'COST_ONLY', TO_NUMBER(NULL),
1710 'REVENUE_ONLY', TO_NUMBER(NULL),
1711 DECODE(po.margin_derived_from_code,
1712 -- 'B', SUM(bl.project_init_revenue) - SUM(bl.project_init_burdened_cost),
1713 -- SUM(bl.project_init_revenue) - SUM(bl.project_init_raw_cost))), -- x_act_margin_proj_cur
1714 'B', rac.total_project_init_revenue - rac.total_project_init_bd_cost,
1715 rac.total_project_init_revenue - rac.total_project_init_raw_cost)), -- x_act_margin_proj_cur
1716 DECODE(po.fin_plan_preference_code,
1717 'COST_ONLY', TO_NUMBER(NULL),
1718 'REVENUE_ONLY', TO_NUMBER(NULL),
1719 DECODE(po.margin_derived_from_code,
1720 -- 'B', SUM(bl.init_revenue) - SUM(bl.init_burdened_cost),
1721 -- SUM(bl.init_revenue) - SUM(bl.init_raw_cost))), -- x_act_margin_proj_func_cur
1722 'B', rac.total_projfunc_init_revenue - rac.total_projfunc_init_bd_cost,
1723 rac.total_projfunc_init_revenue - rac.total_projfunc_init_raw_cost)), -- x_act_margin_proj_func_cur
1724 NULL, -- x_etc_margin_txn_cur (TO BE POPULATED)
1725 NULL, -- x_etc_margin_proj_cur (TO BE POPULATED)
1726 NULL, -- x_etc_margin_proj_func_cur (TO BE POPULATED)
1727 DECODE(po.fin_plan_preference_code,
1728 'COST_ONLY', TO_NUMBER(NULL),
1729 'REVENUE_ONLY', TO_NUMBER(NULL),
1730 DECODE(po.margin_derived_from_code,
1731 -- 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
1732 -- SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))), -- x_fcst_margin_txn_cur
1733 'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
1734 rac.total_txn_revenue - rac.total_txn_raw_cost)), -- x_fcst_margin_txn_cur
1735 DECODE(po.fin_plan_preference_code,
1736 'COST_ONLY', TO_NUMBER(NULL),
1737 'REVENUE_ONLY', TO_NUMBER(NULL),
1738 DECODE(po.margin_derived_from_code,
1739 -- 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
1740 -- SUM(bl.project_revenue) - SUM(bl.project_raw_cost))), -- x_fcst_margin_proj_cur
1741 'B', rac.total_project_revenue - rac.total_project_burdened_cost,
1742 rac.total_project_revenue - rac.total_project_raw_cost)), -- x_fcst_margin_proj_cur
1743 DECODE(po.fin_plan_preference_code,
1744 'COST_ONLY', TO_NUMBER(NULL),
1745 'REVENUE_ONLY', TO_NUMBER(NULL),
1746 DECODE(po.margin_derived_from_code,
1747 -- 'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
1748 -- SUM(bl.revenue) - SUM(bl.raw_cost))), -- x_fcst_margin_proj_func_cur
1749 'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
1750 rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)), -- x_fcst_margin_proj_func_cur
1751 NULL, -- x_act_margin_pct (TO BE POPULATED)
1752 NULL, -- x_etc_margin_pct (TO BE POPULATED)
1753 NULL, -- x_fcst_margin_pct (TO BE POPULATED)
1754 po.margin_derived_from_code
1755 FROM pa_resource_assignments ra,
1756 -- pa_budget_lines bl,
1757 pa_resource_asgn_curr rac,
1758 pa_budget_versions bv,
1759 pa_proj_fp_options po
1760 WHERE ra.resource_assignment_id = l_resource_assignment_id AND
1761 -- ra.resource_assignment_id = bl.resource_assignment_id AND
1762 -- bl.txn_currency_code = p_txn_currency_code AND
1763 ra.resource_assignment_id = rac.resource_assignment_id AND
1764 rac.txn_currency_code = p_txn_currency_code AND
1765 ra.budget_version_id = bv.budget_version_id AND
1766 bv.budget_version_id = po.fin_plan_version_id AND
1767 po.fin_plan_option_level_code = 'PLAN_VERSION';
1768 --AND bl.start_date BETWEEN p_line_start_date AND p_line_end_date
1769 /*
1770 GROUP BY ra.transaction_source_code,
1771 -- ra.init_plan_quantity,
1772 ra.total_plan_quantity,
1773 bl.resource_assignment_id,
1774 bl.txn_currency_code,
1775 ra.planning_start_date,
1776 ra.planning_end_date,
1777 ra.schedule_start_date,
1778 ra.schedule_end_date,
1779 --ra.total_project_revenue,
1780 --ra.total_plan_revenue,
1781 --ra.total_project_raw_cost,
1782 --ra.total_plan_raw_cost,
1783 --ra.total_project_burdened_cost,
1784 --ra.total_plan_burdened_cost,
1785 po.margin_derived_from_code,
1786 po.fin_plan_preference_code;
1787 */
1788
1789
1790 -- ERROR HANDLING VARIABLES
1791 l_return_status VARCHAR2(1);
1792 l_msg_count NUMBER :=0;
1793 l_data VARCHAR2(2000);
1794 l_msg_data VARCHAR2(2000);
1795 l_error_msg_code VARCHAR2(30);
1796 l_msg_index_out NUMBER;
1797
1798 BEGIN
1799 x_return_status := FND_API.G_RET_STS_SUCCESS;
1800 if (p_budget_version_id is null) or (p_resource_assignment_id is null) then
1801 x_return_status := FND_API.G_RET_STS_ERROR;
1802 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1803 p_msg_name => 'PA_FP_COMMONVO_ERROR');
1804 else
1805 x_budget_version_id := p_budget_version_id;
1806 -- NOTE: p_resource_assignment_id refers to the resource assignment for the
1807 -- CURRENT DISPLAYED budget version. We need to figure out the resource
1808 -- assignment for the resource that correlates to p_budget_version_id
1809
1810 -- get task_id and resource_list_member_id that we will need to match
1811 select ra.project_id,
1812 ra.task_id,
1813 ra.resource_list_member_id,
1814 ra.unit_of_measure
1815 into l_project_id,
1816 l_task_id,
1817 l_resource_list_member_id,
1818 l_unit_of_measure
1819 from pa_resource_assignments ra
1820 where ra.resource_assignment_id = p_resource_assignment_id;
1821 open raid_csr;
1822 fetch raid_csr into raid_rec;
1823 if raid_csr%NOTFOUND then
1824 -- no match found: return null for all attribute values
1825 x_budget_version_id := null;
1826 x_planning_start_date := null;
1827 x_planning_end_date := null;
1828 x_schedule_start_date := null;
1829 x_schedule_end_date := null;
1830 x_act_quantity := null;
1831 x_etc_quantity := null;
1832 x_fcst_quantity := null;
1833 x_act_revenue_txn_cur := null;
1834 x_act_revenue_proj_cur := null;
1835 x_act_revenue_proj_func_cur := null;
1836 x_etc_revenue_txn_cur := null;
1837 x_etc_revenue_proj_cur := null;
1838 x_etc_revenue_proj_func_cur := null;
1839 x_fcst_revenue_txn_cur := null;
1840 x_fcst_revenue_proj_cur := null;
1841 x_fcst_revenue_proj_func_cur := null;
1842 x_act_raw_cost_txn_cur := null;
1843 x_act_raw_cost_proj_cur := null;
1844 x_act_raw_cost_proj_func_cur := null;
1845 x_etc_raw_cost_txn_cur := null;
1846 x_etc_raw_cost_proj_cur := null;
1847 x_etc_raw_cost_proj_func_cur := null;
1848 x_fcst_raw_cost_txn_cur := null;
1849 x_fcst_raw_cost_proj_cur := null;
1850 x_fcst_raw_cost_proj_func_cur := null;
1851 x_act_burd_cost_txn_cur := null;
1852 x_act_burd_cost_proj_cur := null;
1853 x_act_burd_cost_proj_func_cur := null;
1854 x_etc_burd_cost_txn_cur := null;
1855 x_etc_burd_cost_proj_cur := null;
1856 x_etc_burd_cost_proj_func_cur := null;
1857 x_fcst_burd_cost_txn_cur := null;
1858 x_fcst_burd_cost_proj_cur := null;
1859 x_fcst_burd_cost_proj_func_cur := null;
1860 x_act_rev_rate := null;
1861 x_etc_init_rev_rate := null;
1862 x_etc_avg_rev_rate := null;
1863 x_act_raw_cost_rate := null;
1864 x_etc_init_raw_cost_rate := null;
1865 x_etc_avg_raw_cost_rate := null;
1866 x_act_burd_cost_rate := null;
1867 x_etc_init_burd_cost_rate := null;
1868 x_etc_avg_burd_cost_rate := null;
1869 x_act_margin_txn_cur := null;
1870 x_act_margin_proj_cur := null;
1871 x_act_margin_proj_func_cur := null;
1872 x_etc_margin_txn_cur := null;
1873 x_etc_margin_proj_cur := null;
1874 x_etc_margin_proj_func_cur := null;
1875 x_fcst_margin_txn_cur := null;
1876 x_fcst_margin_proj_cur := null;
1877 x_fcst_margin_proj_func_cur := null;
1878 x_act_margin_pct := null;
1879 x_etc_margin_pct := null;
1880 x_fcst_margin_pct := null;
1881
1882 else
1883 l_resource_assignment_id := raid_rec.resource_assignment_id;
1884 -- figure out whether or not budget lines exist for the planning element
1885 open budget_lines_csr;
1886 fetch budget_lines_csr into budget_lines_rec;
1887 if budget_lines_csr%NOTFOUND then
1888 l_budget_lines_exist_flag := 'N';
1889 else
1890 l_budget_lines_exist_flag := 'Y';
1891 end if;
1892 close budget_lines_csr;
1893
1894 if l_budget_lines_exist_flag = 'N' then
1895 -- query pa_resource_assignments only
1896 -- bug 3979904: query pa_resource_assignments only if p_txn_currency_code
1897 -- is the same as project currency or project functional currency
1898 begin
1899 select ra.planning_start_date, -- x_planning_start_date
1900 ra.planning_end_date, -- x_planning_end_date
1901 ra.schedule_start_date, -- x_schedule_start_date
1902 ra.schedule_end_date, -- x_schedule_end_date
1903 to_number(null), -- x_act_quantity
1904 to_number(null), -- x_etc_quantity
1905 to_number(null), -- x_fcst_quantity
1906 to_number(null), -- x_act_revenue_txn_cur
1907 to_number(null), -- x_act_revenue_proj_cur
1908 to_number(null), -- x_act_revenue_proj_func_cur
1909 to_number(null), -- x_etc_revenue_txn_cur
1910 to_number(null), -- x_etc_revenue_proj_cur
1911 to_number(null), -- x_etc_revenue_proj_func_cur
1912 to_number(null), -- x_fcst_revenue_txn_cur
1913 to_number(null), -- x_fcst_revenue_proj_cur
1914 to_number(null), -- x_fcst_revenue_proj_func_cur
1915 to_number(null), -- x_act_raw_cost_txn_cur
1916 to_number(null), -- x_act_raw_cost_proj_cur
1917 to_number(null), -- x_act_raw_cost_proj_func_cur
1918 to_number(null), -- x_etc_raw_cost_txn_cur
1919 to_number(null), -- x_etc_raw_cost_proj_cur
1920 to_number(null), -- x_etc_raw_cost_proj_func_cur
1921 to_number(null), -- x_fcst_raw_cost_txn_cur
1922 to_number(null), -- x_fcst_raw_cost_proj_cur
1923 to_number(null), -- x_fcst_raw_cost_proj_func_cur
1924 to_number(null), -- x_act_burd_cost_txn_cur
1925 to_number(null), -- x_act_burd_cost_proj_cur
1926 to_number(null), -- x_act_burd_cost_proj_func_cur
1927 to_number(null), -- x_etc_burd_cost_txn_cur
1928 to_number(null), -- x_etc_burd_cost_proj_cur
1929 to_number(null), -- x_etc_burd_cost_proj_func_cur
1930 to_number(null), -- x_fcst_burd_cost_txn_cur
1931 to_number(null), -- x_fcst_burd_cost_proj_cur
1932 to_number(null), -- x_fcst_burd_cost_proj_func_cur
1933 to_number(null), -- x_act_rev_rate
1934 to_number(null), -- x_etc_init_rev_rate
1935 to_number(null), -- x_etc_avg_rev_rate,
1936 to_number(null), -- x_act_raw_cost_rate
1937 to_number(null), -- x_etc_init_raw_cost_rate
1938 to_number(null), -- x_etc_avg_raw_cost_rate
1939 to_number(null), -- x_act_burd_cost_rate
1940 to_number(null), -- x_etc_init_burd_cost_rate
1941 to_number(null), -- x_etc_avg_burd_cost_rate
1942 to_number(null), -- x_act_margin_txn_cur
1943 to_number(null), -- x_act_margin_proj_cur
1944 to_number(null), -- x_act_margin_proj_func_cur
1945 to_number(null), -- x_etc_margin_txn_cur
1946 to_number(null), -- x_etc_margin_proj_cur
1947 to_number(null), -- x_etc_margin_proj_func_cur
1948 to_number(null), -- x_fcst_margin_txn_cur
1949 to_number(null), -- x_fcst_margin_proj_cur
1950 to_number(null), -- x_fcst_margin_proj_func_cur
1951 to_number(null), -- x_act_margin_pct
1952 to_number(null), -- x_etc_margin_pct
1953 to_number(null), -- x_fcst_margin_pct
1954 po.margin_derived_from_code
1955 into x_planning_start_date,
1956 x_planning_end_date,
1957 x_schedule_start_date,
1958 x_schedule_end_date,
1959 x_act_quantity,
1960 x_etc_quantity,
1961 x_fcst_quantity,
1962 x_act_revenue_txn_cur,
1963 x_act_revenue_proj_cur,
1964 x_act_revenue_proj_func_cur,
1965 x_etc_revenue_txn_cur,
1966 x_etc_revenue_proj_cur,
1967 x_etc_revenue_proj_func_cur,
1968 x_fcst_revenue_txn_cur,
1969 x_fcst_revenue_proj_cur,
1970 x_fcst_revenue_proj_func_cur,
1971 x_act_raw_cost_txn_cur,
1972 x_act_raw_cost_proj_cur,
1973 x_act_raw_cost_proj_func_cur,
1974 x_etc_raw_cost_txn_cur,
1975 x_etc_raw_cost_proj_cur,
1976 x_etc_raw_cost_proj_func_cur,
1977 x_fcst_raw_cost_txn_cur,
1978 x_fcst_raw_cost_proj_cur,
1979 x_fcst_raw_cost_proj_func_cur,
1980 x_act_burd_cost_txn_cur,
1981 x_act_burd_cost_proj_cur,
1982 x_act_burd_cost_proj_func_cur,
1983 x_etc_burd_cost_txn_cur,
1984 x_etc_burd_cost_proj_cur,
1985 x_etc_burd_cost_proj_func_cur,
1986 x_fcst_burd_cost_txn_cur,
1987 x_fcst_burd_cost_proj_cur,
1988 x_fcst_burd_cost_proj_func_cur,
1989 x_act_rev_rate,
1990 x_etc_init_rev_rate,
1991 x_etc_avg_rev_rate,
1992 x_act_raw_cost_rate,
1993 x_etc_init_raw_cost_rate,
1994 x_etc_avg_raw_cost_rate,
1995 x_act_burd_cost_rate,
1996 x_etc_init_burd_cost_rate,
1997 x_etc_avg_burd_cost_rate,
1998 x_act_margin_txn_cur,
1999 x_act_margin_proj_cur,
2000 x_act_margin_proj_func_cur,
2001 x_etc_margin_txn_cur,
2002 x_etc_margin_proj_cur,
2003 x_etc_margin_proj_func_cur,
2004 x_fcst_margin_txn_cur,
2005 x_fcst_margin_proj_cur,
2006 x_fcst_margin_proj_func_cur,
2007 x_act_margin_pct,
2008 x_etc_margin_pct,
2009 x_fcst_margin_pct,
2010 l_margin_derived_from_code
2011 from pa_resource_assignments ra,
2012 pa_budget_versions bv,
2013 pa_proj_fp_options po
2014 where ra.resource_assignment_id = l_resource_assignment_id and
2015 ra.budget_version_id = bv.budget_version_id and
2016 bv.budget_version_id = po.fin_plan_version_id and
2017 po.fin_plan_option_level_code = 'PLAN_VERSION';
2018 exception
2019 when NO_DATA_FOUND then
2020 x_return_status := FND_API.G_RET_STS_ERROR;
2021 x_msg_count := 1;
2022 x_msg_data := SQLERRM;
2023 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2024 p_procedure_name => 'get_common_budget_version_info');
2025 end;
2026 -- Calculate the remaining OUT parameters
2027 if l_margin_derived_from_code = 'B' then
2028 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2029 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2030 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2031 else
2032 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2033 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2034 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2035 end if; -- l_margin_derived_from_code
2036 if x_act_revenue_proj_cur is not null then
2037 if x_act_revenue_proj_cur = 0 then
2038 x_act_margin_pct := 0;
2039 else
2040 x_act_margin_pct := 100 * x_act_margin_proj_cur / x_act_revenue_proj_cur;
2041 end if; -- x_act_revenue_proj_cur = 0
2042 end if;
2043 if x_etc_revenue_proj_cur is not null then
2044 if x_etc_revenue_proj_cur = 0 then
2045 x_etc_margin_pct := 0;
2046 else
2047 x_etc_margin_pct := 100 * x_etc_margin_proj_cur / x_etc_revenue_proj_cur;
2048 end if; -- x_etc_revenue_proj_cur = 0
2049 end if;
2050 if x_fcst_revenue_proj_cur is not null then
2051 if x_fcst_revenue_proj_cur = 0 then
2052 x_fcst_margin_pct := 0;
2053 else
2054 x_fcst_margin_pct := 100 * x_fcst_margin_proj_cur / x_fcst_revenue_proj_cur;
2055 end if; -- x_fcst_revenue_proj_cur = 0
2056 end if;
2057
2058 else
2059 -- budget lines exist, so query pa_resource_assignment and pa_budget_lines
2060 IF p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL THEN
2061
2062 OPEN get_fcst_line_amts_for_dates;
2063 FETCH get_fcst_line_amts_for_dates INTO
2064 x_planning_start_date,
2065 x_planning_end_date,
2066 x_schedule_start_date,
2067 x_schedule_end_date,
2068 x_act_quantity,
2069 x_etc_quantity,
2070 x_fcst_quantity,
2071 x_act_revenue_txn_cur,
2072 x_act_revenue_proj_cur,
2073 x_act_revenue_proj_func_cur,
2074 x_etc_revenue_txn_cur,
2075 x_etc_revenue_proj_cur,
2076 x_etc_revenue_proj_func_cur,
2077 x_fcst_revenue_txn_cur,
2078 x_fcst_revenue_proj_cur,
2079 x_fcst_revenue_proj_func_cur,
2080 x_act_raw_cost_txn_cur,
2081 x_act_raw_cost_proj_cur,
2082 x_act_raw_cost_proj_func_cur,
2083 x_etc_raw_cost_txn_cur,
2084 x_etc_raw_cost_proj_cur,
2085 x_etc_raw_cost_proj_func_cur,
2086 x_fcst_raw_cost_txn_cur,
2087 x_fcst_raw_cost_proj_cur,
2088 x_fcst_raw_cost_proj_func_cur,
2089 x_act_burd_cost_txn_cur,
2090 x_act_burd_cost_proj_cur,
2091 x_act_burd_cost_proj_func_cur,
2092 x_etc_burd_cost_txn_cur,
2093 x_etc_burd_cost_proj_cur,
2094 x_etc_burd_cost_proj_func_cur,
2095 x_fcst_burd_cost_txn_cur,
2096 x_fcst_burd_cost_proj_cur,
2097 x_fcst_burd_cost_proj_func_cur,
2098 x_act_rev_rate,
2099 x_etc_init_rev_rate,
2100 x_etc_avg_rev_rate,
2101 x_act_raw_cost_rate,
2102 x_etc_init_raw_cost_rate,
2103 x_etc_avg_raw_cost_rate,
2104 x_act_burd_cost_rate,
2105 x_etc_init_burd_cost_rate,
2106 x_etc_avg_burd_cost_rate,
2107 x_act_margin_txn_cur,
2108 x_act_margin_proj_cur,
2109 x_act_margin_proj_func_cur,
2110 x_etc_margin_txn_cur,
2111 x_etc_margin_proj_cur,
2112 x_etc_margin_proj_func_cur,
2113 x_fcst_margin_txn_cur,
2114 x_fcst_margin_proj_cur,
2115 x_fcst_margin_proj_func_cur,
2116 x_act_margin_pct,
2117 x_etc_margin_pct,
2118 x_fcst_margin_pct,
2119 l_margin_derived_from_code;
2120
2121 IF get_fcst_line_amts_for_dates%NOTFOUND THEN
2122 x_planning_start_date := null;
2123 x_planning_end_date := null;
2124 x_schedule_start_date := null;
2125 x_schedule_end_date := null;
2126 x_act_quantity := null;
2127 x_etc_quantity := null;
2128 x_fcst_quantity := null;
2129 x_act_revenue_txn_cur := null;
2130 x_act_revenue_proj_cur := null;
2131 x_act_revenue_proj_func_cur := null;
2132 x_etc_revenue_txn_cur := null;
2133 x_etc_revenue_proj_cur := null;
2134 x_etc_revenue_proj_func_cur := null;
2135 x_fcst_revenue_txn_cur := null;
2136 x_fcst_revenue_proj_cur := null;
2137 x_fcst_revenue_proj_func_cur := null;
2138 x_act_raw_cost_txn_cur := null;
2139 x_act_raw_cost_proj_cur := null;
2140 x_act_raw_cost_proj_func_cur := null;
2141 x_etc_raw_cost_txn_cur := null;
2142 x_etc_raw_cost_proj_cur := null;
2143 x_etc_raw_cost_proj_func_cur := null;
2144 x_fcst_raw_cost_txn_cur := null;
2145 x_fcst_raw_cost_proj_cur := null;
2146 x_fcst_raw_cost_proj_func_cur := null;
2147 x_act_burd_cost_txn_cur := null;
2148 x_act_burd_cost_proj_cur := null;
2149 x_act_burd_cost_proj_func_cur := null;
2150 x_etc_burd_cost_txn_cur := null;
2151 x_etc_burd_cost_proj_cur := null;
2152 x_etc_burd_cost_proj_func_cur := null;
2153 x_fcst_burd_cost_txn_cur := null;
2154 x_fcst_burd_cost_proj_cur := null;
2155 x_fcst_burd_cost_proj_func_cur := null;
2156 x_act_rev_rate := null;
2157 x_etc_init_rev_rate := null;
2158 x_etc_avg_rev_rate := null;
2159 x_act_raw_cost_rate := null;
2160 x_etc_init_raw_cost_rate := null;
2161 x_etc_avg_raw_cost_rate := null;
2162 x_act_burd_cost_rate := null;
2163 x_etc_init_burd_cost_rate := null;
2164 x_etc_avg_burd_cost_rate := null;
2165 x_act_margin_txn_cur := null;
2166 x_act_margin_proj_cur := null;
2167 x_act_margin_proj_func_cur := null;
2168 x_etc_margin_txn_cur := null;
2169 x_etc_margin_proj_cur := null;
2170 x_etc_margin_proj_func_cur := null;
2171 x_fcst_margin_txn_cur := null;
2172 x_fcst_margin_proj_cur := null;
2173 x_fcst_margin_proj_func_cur := null;
2174 x_act_margin_pct := null;
2175 x_etc_margin_pct := null;
2176 x_fcst_margin_pct := null;
2177 l_margin_derived_from_code := null;
2178 END IF;
2179
2180 CLOSE get_fcst_line_amts_for_dates;
2181
2182 ELSE --p_line_start_date IS NULL AND/OR p_line_end_date IS NULL THEN
2183 SELECT ra.planning_start_date, -- x_planning_start_date
2184 ra.planning_end_date, -- x_planning_end_date
2185 ra.schedule_start_date, -- x_schedule_start_date
2186 ra.schedule_end_date, -- x_schedule_end_date
2187 rac.total_init_quantity, -- x_act_quantity
2188 DECODE(ra.rate_based_flag, 'Y', NVL(rac.total_display_quantity,0) - NVL(rac.total_init_quantity,0), 0), -- x_etc_quantity, Bug 5726773
2189 rac.total_display_quantity, -- x_fcst_quantity
2190 rac.total_txn_init_revenue, -- x_act_revenue_txn_cur
2191 rac.total_project_init_revenue, -- x_act_revenue_proj_cur
2192 rac.total_projfunc_init_revenue, -- x_act_revenue_proj_func_cur
2193 NVL(rac.total_txn_revenue,0) - NVL(rac.total_txn_init_revenue,0), -- x_etc_revenue_txn_cur
2194 NVL(rac.total_project_revenue,0) - NVL(rac.total_project_init_revenue,0), -- x_etc_revenue_proj_cur
2195 NVL(rac.total_projfunc_revenue,0) - NVL(rac.total_projfunc_init_revenue,0), -- x_etc_revenue_proj_func_cur
2196 rac.total_txn_revenue, -- x_fcst_revenue_txn_cur
2197 rac.total_project_revenue, -- x_fcst_revenue_proj_cur
2198 rac.total_projfunc_revenue, -- x_fcst_revenue_proj_func_cur
2199 rac.total_txn_init_raw_cost, -- x_act_raw_cost_txn_cur
2200 rac.total_project_init_raw_cost, -- x_act_raw_cost_proj_cur
2201 rac.total_projfunc_init_raw_cost, -- x_act_raw_cost_proj_func_cur
2202 NVL(rac.total_txn_raw_cost,0) - NVL(rac.total_txn_init_raw_cost,0), -- x_etc_raw_cost_txn_cur
2203 NVL(rac.total_project_raw_cost,0) - NVL(rac.total_project_init_raw_cost,0),-- x_etc_raw_cost_proj_cur
2204 NVL(rac.total_projfunc_raw_cost,0) - NVL(rac.total_projfunc_init_raw_cost,0),-- x_etc_raw_cost_proj_func_cur
2205 rac.total_txn_raw_cost, -- x_fcst_raw_cost_txn_cur
2206 rac.total_project_raw_cost, -- x_fcst_raw_cost_proj_cur
2207 rac.total_projfunc_raw_cost, -- x_fcst_raw_cost_proj_func_cur
2208 rac.total_txn_init_burdened_cost, -- x_act_burd_cost_txn_cur
2209 rac.total_project_init_bd_cost, -- x_act_burd_cost_proj_cur
2210 rac.total_projfunc_init_bd_cost, -- x_act_burd_cost_proj_func_cur
2211 NVL(rac.total_txn_burdened_cost,0) - NVL(rac.total_txn_init_burdened_cost,0), -- x_etc_burd_cost_txn_cur
2212 NVL(rac.total_project_burdened_cost,0) - NVL(rac.total_project_init_bd_cost,0), -- x_etc_burd_cost_proj_cur
2213 NVL(rac.total_projfunc_burdened_cost,0) - NVL(rac.total_projfunc_init_bd_cost,0), -- x_etc_burd_cost_proj_func_cur
2214 rac.total_txn_burdened_cost, -- x_fcst_burd_cost_txn_cur
2215 rac.total_project_burdened_cost, -- x_fcst_burd_cost_proj_cur
2216 rac.total_projfunc_burdened_cost, -- x_fcst_burd_cost_proj_func_cur
2217 /*
2218 SUM(bl.init_quantity), -- x_act_quantity
2219 SUM(NVL(bl.quantity,0)) - SUM(NVL(bl.init_quantity,0)), -- x_etc_quantity
2220 SUM(bl.quantity), -- x_fcst_quantity
2221 SUM(txn_init_revenue), -- x_act_revenue_txn_cur
2222 SUM(bl.project_init_revenue), -- x_act_revenue_proj_cur
2223 SUM(bl.init_revenue), -- x_act_revenue_proj_func_cur
2224 SUM(NVL(bl.txn_revenue,0)) - SUM(NVL(bl.txn_init_revenue,0)), -- x_etc_revenue_txn_cur
2225 SUM(NVL(bl.project_revenue,0)) - SUM(NVL(bl.project_init_revenue,0)), -- x_etc_revenue_proj_cur
2226 SUM(NVL(bl.revenue,0)) - SUM(NVL(bl.init_revenue,0)), -- x_etc_revenue_proj_func_cur
2227 SUM(bl.txn_revenue), -- x_fcst_revenue_txn_cur
2228 SUM(bl.project_revenue), -- x_fcst_revenue_proj_cur
2229 SUM(bl.revenue), -- x_fcst_revenue_proj_func_cur
2230 SUM(bl.txn_init_raw_cost), -- x_act_raw_cost_txn_cur
2231 SUM(bl.project_init_raw_cost), -- x_act_raw_cost_proj_cur
2232 SUM(bl.init_raw_cost), -- x_act_raw_cost_proj_func_cur
2233 SUM(NVL(bl.txn_raw_cost,0)) - SUM(NVL(bl.txn_init_raw_cost,0)), -- x_etc_raw_cost_txn_cur
2234 SUM(NVL(bl.project_raw_cost,0)) - SUM(NVL(bl.project_init_raw_cost,0)), -- x_etc_raw_cost_proj_cur
2235 SUM(NVL(bl.raw_cost,0)) - SUM(NVL(bl.init_raw_cost,0)), -- x_etc_raw_cost_proj_func_cur
2236 SUM(bl.txn_raw_cost), -- x_fcst_raw_cost_txn_cur
2237 SUM(bl.project_raw_cost), -- x_fcst_raw_cost_proj_cur
2238 SUM(bl.raw_cost), -- x_fcst_raw_cost_proj_func_cur
2239 SUM(bl.txn_init_burdened_cost), -- x_act_burd_cost_txn_cur
2240 SUM(bl.project_init_burdened_cost), -- x_act_burd_cost_proj_cur
2241 SUM(bl.init_burdened_cost), -- x_act_burd_cost_proj_func_cur
2242 SUM(NVL(bl.txn_burdened_cost,0)) - SUM(NVL(bl.txn_init_burdened_cost,0)), -- x_etc_burd_cost_txn_cur
2243 SUM(NVL(bl.project_burdened_cost,0)) - SUM(NVL(bl.project_init_burdened_cost,0)), -- x_etc_burd_cost_proj_cur
2244 SUM(NVL(bl.burdened_cost,0)) - SUM(NVL(bl.init_burdened_cost,0)), -- x_etc_burd_cost_proj_func_cur
2245 SUM(bl.txn_burdened_cost), -- x_fcst_burd_cost_txn_cur
2246 SUM(bl.project_burdened_cost), -- x_fcst_burd_cost_proj_cur
2247 SUM(bl.burdened_cost), -- x_fcst_burd_cost_proj_func_cur
2248 */
2249 NULL, -- x_act_rev_rate (TO BE CALCULATED)
2250 NULL, -- x_etc_init_rev_rate (TO BE CALCULATED)
2251 NULL, -- x_etc_avg_rev_rate (TO BE CALCULATED)
2252 NULL, -- x_act_raw_cost_rate (TO BE CALCULATED)
2253 NULL, -- x_etc_init_raw_cost_rate (TO BE CALCULATED)
2254 NULL, -- x_etc_avg_raw_cost_rate (TO BE CALCULATED)
2255 NULL, -- x_act_burd_cost_rate (TO BE CALCULATED)
2256 NULL, -- x_etc_init_burd_cost_rate (TO BE CALCULATED)
2257 NULL, -- x_etc_avg_burd_cost_rate (TO BE CALCULATED)
2258 DECODE(po.fin_plan_preference_code,
2259 'COST_ONLY', TO_NUMBER(NULL),
2260 'REVENUE_ONLY', TO_NUMBER(NULL),
2261 DECODE(po.margin_derived_from_code,
2262 -- 'B', SUM(bl.txn_init_revenue) - SUM(bl.txn_init_burdened_cost),
2263 -- SUM(bl.txn_init_revenue) - SUM(bl.txn_init_raw_cost))), -- x_act_margin_txn_cur
2264 'B', rac.total_txn_init_revenue - rac.total_txn_init_burdened_cost,
2265 rac.total_txn_init_revenue - rac.total_txn_init_raw_cost)), -- x_act_margin_txn_cur
2266 DECODE(po.fin_plan_preference_code,
2267 'COST_ONLY', TO_NUMBER(NULL),
2268 'REVENUE_ONLY', TO_NUMBER(NULL),
2269 DECODE(po.margin_derived_from_code,
2270 -- 'B', SUM(bl.project_init_revenue) - SUM(bl.project_init_burdened_cost),
2271 -- SUM(bl.project_init_revenue) - SUM(bl.project_init_raw_cost))), -- x_act_margin_proj_cur
2272 'B', rac.total_project_init_revenue - rac.total_project_init_bd_cost,
2273 rac.total_project_init_revenue - rac.total_project_init_raw_cost)), -- x_act_margin_proj_cur
2274 DECODE(po.fin_plan_preference_code,
2275 'COST_ONLY', TO_NUMBER(NULL),
2276 'REVENUE_ONLY', TO_NUMBER(NULL),
2277 DECODE(po.margin_derived_from_code,
2278 -- 'B', SUM(bl.init_revenue) - SUM(bl.init_burdened_cost),
2279 -- SUM(bl.init_revenue) - SUM(bl.init_raw_cost))), -- x_act_margin_proj_func_cur
2280 'B', rac.total_projfunc_init_revenue - rac.total_projfunc_init_bd_cost,
2281 rac.total_projfunc_init_revenue - rac.total_projfunc_init_raw_cost)), -- x_act_margin_proj_func_cur
2282 NULL, -- x_etc_margin_txn_cur (TO BE POPULATED)
2283 NULL, -- x_etc_margin_proj_cur (TO BE POPULATED)
2284 NULL, -- x_etc_margin_proj_func_cur (TO BE POPULATED)
2285 DECODE(po.fin_plan_preference_code,
2286 'COST_ONLY', TO_NUMBER(NULL),
2287 'REVENUE_ONLY', TO_NUMBER(NULL),
2288 DECODE(po.margin_derived_from_code,
2289 -- 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
2290 -- SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))), -- x_fcst_margin_txn_cur
2291 'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
2292 rac.total_txn_revenue - rac.total_txn_raw_cost)), -- x_fcst_margin_txn_cur
2293 DECODE(po.fin_plan_preference_code,
2294 'COST_ONLY', TO_NUMBER(NULL),
2295 'REVENUE_ONLY', TO_NUMBER(NULL),
2296 DECODE(po.margin_derived_from_code,
2297 -- 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
2298 -- SUM(bl.project_revenue) - SUM(bl.project_raw_cost))), -- x_fcst_margin_proj_cur
2299 'B', rac.total_project_revenue - rac.total_project_burdened_cost,
2300 rac.total_project_revenue - rac.total_project_raw_cost)), -- x_fcst_margin_proj_cur
2301 DECODE(po.fin_plan_preference_code,
2302 'COST_ONLY', TO_NUMBER(NULL),
2303 'REVENUE_ONLY', TO_NUMBER(NULL),
2304 DECODE(po.margin_derived_from_code,
2305 -- 'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
2306 -- SUM(bl.revenue) - SUM(bl.raw_cost))), -- x_fcst_margin_proj_func_cur
2307 'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
2308 rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)), -- x_fcst_margin_proj_func_cur
2309 NULL, -- x_act_margin_pct (TO BE POPULATED)
2310 NULL, -- x_etc_margin_pct (TO BE POPULATED)
2311 NULL, -- x_fcst_margin_pct (TO BE POPULATED)
2312 po.margin_derived_from_code
2313 into x_planning_start_date,
2314 x_planning_end_date,
2315 x_schedule_start_date,
2316 x_schedule_end_date,
2317 x_act_quantity,
2318 x_etc_quantity,
2319 x_fcst_quantity,
2320 x_act_revenue_txn_cur,
2321 x_act_revenue_proj_cur,
2322 x_act_revenue_proj_func_cur,
2323 x_etc_revenue_txn_cur,
2324 x_etc_revenue_proj_cur,
2325 x_etc_revenue_proj_func_cur,
2326 x_fcst_revenue_txn_cur,
2327 x_fcst_revenue_proj_cur,
2328 x_fcst_revenue_proj_func_cur,
2329 x_act_raw_cost_txn_cur,
2330 x_act_raw_cost_proj_cur,
2331 x_act_raw_cost_proj_func_cur,
2332 x_etc_raw_cost_txn_cur,
2333 x_etc_raw_cost_proj_cur,
2334 x_etc_raw_cost_proj_func_cur,
2335 x_fcst_raw_cost_txn_cur,
2336 x_fcst_raw_cost_proj_cur,
2337 x_fcst_raw_cost_proj_func_cur,
2338 x_act_burd_cost_txn_cur,
2339 x_act_burd_cost_proj_cur,
2340 x_act_burd_cost_proj_func_cur,
2341 x_etc_burd_cost_txn_cur,
2342 x_etc_burd_cost_proj_cur,
2343 x_etc_burd_cost_proj_func_cur,
2344 x_fcst_burd_cost_txn_cur,
2345 x_fcst_burd_cost_proj_cur,
2346 x_fcst_burd_cost_proj_func_cur,
2347 x_act_rev_rate,
2348 x_etc_init_rev_rate,
2349 x_etc_avg_rev_rate,
2350 x_act_raw_cost_rate,
2351 x_etc_init_raw_cost_rate,
2352 x_etc_avg_raw_cost_rate,
2353 x_act_burd_cost_rate,
2354 x_etc_init_burd_cost_rate,
2355 x_etc_avg_burd_cost_rate,
2356 x_act_margin_txn_cur,
2357 x_act_margin_proj_cur,
2358 x_act_margin_proj_func_cur,
2359 x_etc_margin_txn_cur,
2360 x_etc_margin_proj_cur,
2361 x_etc_margin_proj_func_cur,
2362 x_fcst_margin_txn_cur,
2363 x_fcst_margin_proj_cur,
2364 x_fcst_margin_proj_func_cur,
2365 x_act_margin_pct,
2366 x_etc_margin_pct,
2367 x_fcst_margin_pct,
2368 l_margin_derived_from_code
2369 FROM pa_resource_assignments ra,
2370 -- pa_budget_lines bl,
2371 pa_resource_asgn_curr rac,
2372 pa_budget_versions bv,
2373 pa_proj_fp_options po
2374 where ra.resource_assignment_id = l_resource_assignment_id and
2375 -- ra.resource_assignment_id = bl.resource_assignment_id and
2376 -- bl.txn_currency_code = p_txn_currency_code and
2377 ra.resource_assignment_id = rac.resource_assignment_id and
2378 rac.txn_currency_code = p_txn_currency_code and
2379 ra.budget_version_id = bv.budget_version_id and
2380 bv.budget_version_id = po.fin_plan_version_id and
2381 po.fin_plan_option_level_code = 'PLAN_VERSION';
2382 /*
2383 group by ra.transaction_source_code,
2384 --ra.init_plan_quantity,
2385 --ra.total_plan_quantity,
2386 bl.resource_assignment_id,
2387 bl.txn_currency_code,
2388 ra.planning_start_date,
2389 ra.planning_end_date,
2390 ra.schedule_start_date,
2391 ra.schedule_end_date,
2392 --ra.total_project_revenue,
2393 --ra.total_plan_revenue,
2394 --ra.total_project_raw_cost,
2395 --ra.total_plan_raw_cost,
2396 --ra.total_project_burdened_cost,
2397 --ra.total_plan_burdened_cost,
2398 po.margin_derived_from_code,
2399 po.fin_plan_preference_code;
2400 */
2401 END IF; --p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL
2402
2403 -- CALCULATE THE RATE/MARGIN/MARGINPCT VALUES
2404
2405 pa_planning_element_utils.get_initial_budget_line_info
2406 (p_resource_assignment_id => p_resource_assignment_id,
2407 p_txn_currency_code => p_txn_currency_code,
2408 p_line_start_date => p_line_start_date,
2409 p_line_end_date => p_line_end_date,
2410 x_start_date => l_start_date,
2411 x_end_date => l_end_date,
2412 x_period_name => l_period_name,
2413 x_quantity => l_quantity,
2414 x_txn_raw_cost => l_txn_raw_cost,
2415 x_txn_burdened_cost => l_txn_burdened_cost,
2416 x_txn_revenue => l_txn_revenue,
2417 x_init_quantity => l_init_quantity,
2418 x_txn_init_raw_cost => l_txn_init_raw_cost,
2419 x_txn_init_burdened_cost => l_txn_init_burdened_cost,
2420 x_txn_init_revenue => l_txn_init_revenue,
2421 x_init_raw_cost_rate => l_init_raw_cost_rate,
2422 x_init_burd_cost_rate => l_init_burd_cost_rate,
2423 x_init_revenue_rate => l_init_revenue_rate,
2424 x_etc_init_raw_cost_rate => l_etc_init_raw_cost_rate,
2425 x_etc_init_burd_cost_rate => l_etc_init_burd_cost_rate,
2426 x_etc_init_revenue_rate => l_etc_init_revenue_rate,
2427 x_return_status => l_return_status,
2428 x_msg_count => l_msg_count,
2429 x_msg_data => l_msg_data);
2430
2431 if x_act_quantity is not null then
2432 if x_act_quantity = 0 then
2433 x_act_rev_rate := 0;
2434 x_act_raw_cost_rate := 0;
2435 x_act_burd_cost_rate := 0;
2436 else
2437 x_act_rev_rate := x_act_revenue_txn_cur / x_act_quantity;
2438 x_act_raw_cost_rate := x_act_raw_cost_txn_cur / x_act_quantity;
2439 x_act_burd_cost_rate := x_act_burd_cost_txn_cur / x_act_quantity;
2440 end if; -- x_act_quantity = 0
2441 end if; -- x_act_quantity is not null
2442
2443 /* when calculating etc rates, use etc quantity, not fcst quantity */
2444 if x_etc_quantity is not null then
2445 if x_etc_quantity = 0 then
2446 x_etc_avg_rev_rate := 0;
2447 x_etc_avg_raw_cost_rate := 0;
2448 x_etc_avg_burd_cost_rate := 0;
2449 else
2450 x_etc_avg_rev_rate := x_etc_revenue_txn_cur / x_etc_quantity;
2451 x_etc_avg_raw_cost_rate := x_etc_raw_cost_txn_cur / x_etc_quantity;
2452 x_etc_avg_burd_cost_rate := x_etc_burd_cost_txn_cur / x_etc_quantity;
2453 end if; -- x_fcst_quantity = 0;
2454 else
2455 x_etc_avg_rev_rate := 0;
2456 x_etc_avg_raw_cost_rate := 0;
2457 x_etc_avg_burd_cost_rate := 0;
2458 end if; -- x_fcst_quantity is not null
2459
2460 x_etc_init_rev_rate := l_etc_init_revenue_rate;
2461 x_etc_init_raw_cost_rate := l_etc_init_raw_cost_rate;
2462 x_etc_init_burd_cost_rate := l_etc_init_burd_cost_rate;
2463
2464 if l_margin_derived_from_code = 'B' then
2465 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2466 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2467 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2468 else
2469 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2470 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2471 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2472 end if; -- l_margin_derived_from_code
2473 if x_act_revenue_proj_cur is not null then
2474 if x_act_revenue_proj_cur = 0 then
2475 x_act_margin_pct := 0;
2476 else
2477 x_act_margin_pct := 100 * x_act_margin_proj_cur / x_act_revenue_proj_cur;
2478 end if; -- x_act_revenue_proj_cur = 0
2479 end if;
2480 if x_etc_revenue_proj_cur is not null then
2481 if x_etc_revenue_proj_cur = 0 then
2482 x_etc_margin_pct := 0;
2483 else
2484 x_etc_margin_pct := 100 * x_etc_margin_proj_cur / x_etc_revenue_proj_cur;
2485 end if; -- x_etc_revenue_proj_cur = 0
2486 end if;
2487 if x_fcst_revenue_proj_cur is not null then
2488 if x_fcst_revenue_proj_cur = 0 then
2489 x_fcst_margin_pct := 0;
2490 else
2491 x_fcst_margin_pct := 100 * x_fcst_margin_proj_cur / x_fcst_revenue_proj_cur;
2492 end if; -- x_fcst_revenue_proj_cur = 0
2493 end if;
2494
2495 end if;
2496 end if;
2497 end if; -- if l_resource_assignment_id found
2498 close raid_csr;
2499 -- Check message stack for error messages
2500 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2501 l_msg_count := FND_MSG_PUB.count_msg;
2502 if l_msg_count = 1 THEN
2503 PA_INTERFACE_UTILS_PUB.get_messages
2504 (p_encoded => FND_API.G_TRUE
2505 ,p_msg_index => 1
2506 ,p_msg_count => l_msg_count
2507 ,p_msg_data => l_msg_data
2508 ,p_data => l_data
2509 ,p_msg_index_out => l_msg_index_out);
2510 x_msg_data := l_data;
2511 x_msg_count := l_msg_count;
2512 else
2513 x_msg_count := l_msg_count;
2514 end if;
2515 end if;
2516 EXCEPTION
2517 WHEN OTHERS THEN
2518 FND_MSG_PUB.ADD_EXC_MSG (p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2519 p_procedure_name => 'get_common_bv_info_fcst');
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 x_msg_data := SQLERRM;
2522 END get_common_bv_info_fcst;
2523
2524
2525 procedure get_initial_budget_line_info
2526 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2527 p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
2528 p_line_start_date IN pa_budget_lines.start_date%TYPE := to_date(NULL),
2529 p_line_end_date IN pa_budget_lines.end_date%TYPE := to_date(NULL),
2530 x_start_date OUT NOCOPY pa_budget_lines.start_date%TYPE, --File.Sql.39 bug 4440895
2531 x_end_date OUT NOCOPY pa_budget_lines.end_date%TYPE, --File.Sql.39 bug 4440895
2532 x_period_name OUT NOCOPY pa_budget_lines.period_name%TYPE, --File.Sql.39 bug 4440895
2533 x_quantity OUT NOCOPY pa_budget_lines.quantity%TYPE, --File.Sql.39 bug 4440895
2534 x_txn_raw_cost OUT NOCOPY pa_budget_lines.raw_cost%TYPE, --File.Sql.39 bug 4440895
2535 x_txn_burdened_cost OUT NOCOPY pa_budget_lines.burdened_cost%TYPE, --File.Sql.39 bug 4440895
2536 x_txn_revenue OUT NOCOPY pa_budget_lines.revenue%TYPE, --File.Sql.39 bug 4440895
2537 x_init_quantity OUT NOCOPY pa_budget_lines.init_quantity%TYPE, --File.Sql.39 bug 4440895
2538 x_txn_init_raw_cost OUT NOCOPY pa_budget_lines.txn_init_raw_cost%TYPE, --File.Sql.39 bug 4440895
2539 x_txn_init_burdened_cost OUT NOCOPY pa_budget_lines.txn_init_burdened_cost%TYPE, --File.Sql.39 bug 4440895
2540 x_txn_init_revenue OUT NOCOPY pa_budget_lines.txn_init_revenue%TYPE, --File.Sql.39 bug 4440895
2541 x_init_raw_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2542 x_init_burd_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2543 x_init_revenue_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2544 x_etc_init_raw_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2545 x_etc_init_burd_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2546 x_etc_init_revenue_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2547 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2548 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2549 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
2550 BEGIN
2551 x_msg_count := 0;
2552 x_return_status := FND_API.G_RET_STS_SUCCESS;
2553 IF p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL THEN
2554 select bl.start_date,
2555 bl.end_date,
2556 bl.period_name,
2557 -- bl.quantity,
2558 bl.display_quantity, --IPM
2559 bl.txn_raw_cost,
2560 bl.txn_burdened_cost,
2561 bl.txn_revenue,
2562 bl.init_quantity,
2563 bl.txn_init_raw_cost,
2564 bl.txn_init_burdened_cost,
2565 bl.txn_init_revenue
2566 into x_start_date,
2567 x_end_date,
2568 x_period_name,
2569 x_quantity,
2570 x_txn_raw_cost,
2571 x_txn_burdened_cost,
2572 x_txn_revenue,
2573 x_init_quantity,
2574 x_txn_init_raw_cost,
2575 x_txn_init_burdened_cost,
2576 x_txn_init_revenue
2577 from pa_budget_lines bl
2578 where bl.resource_assignment_id = p_resource_assignment_id and
2579 bl.txn_currency_code = p_txn_currency_code and
2580 bl.start_date BETWEEN p_line_start_date and p_line_end_date and
2581 start_date = (select min(start_date)
2582 from pa_budget_lines
2583 where resource_assignment_id = p_resource_assignment_id and
2584 txn_currency_code = p_txn_currency_code);
2585 ELSE -- p_line_start_date IS NULL AND/OR p_line_end_date IS NULL
2586 select bl.start_date,
2587 bl.end_date,
2588 bl.period_name,
2589 -- bl.quantity,
2590 bl.display_quantity, -- IPM
2591 bl.txn_raw_cost,
2592 bl.txn_burdened_cost,
2593 bl.txn_revenue,
2594 bl.init_quantity,
2595 bl.txn_init_raw_cost,
2596 bl.txn_init_burdened_cost,
2597 bl.txn_init_revenue
2598 into x_start_date,
2599 x_end_date,
2600 x_period_name,
2601 x_quantity,
2602 x_txn_raw_cost,
2603 x_txn_burdened_cost,
2604 x_txn_revenue,
2605 x_init_quantity,
2606 x_txn_init_raw_cost,
2607 x_txn_init_burdened_cost,
2608 x_txn_init_revenue
2609 from pa_budget_lines bl
2610 where bl.resource_assignment_id = p_resource_assignment_id and
2611 bl.txn_currency_code = p_txn_currency_code and
2612 start_date = (select min(start_date)
2613 from pa_budget_lines
2614 where resource_assignment_id = p_resource_assignment_id and
2615 txn_currency_code = p_txn_currency_code);
2616 END IF; -- p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL
2617 -- CALCULATE THE RATES
2618 if x_quantity is not null then
2619 if x_quantity = 0 then
2620 x_init_raw_cost_rate := 0;
2621 x_init_burd_cost_rate := 0;
2622 x_init_revenue_rate := 0;
2623 x_etc_init_raw_cost_rate := 0;
2624 x_etc_init_burd_cost_rate := 0;
2625 x_etc_init_revenue_rate := 0;
2626 else
2627 x_init_raw_cost_rate := x_txn_raw_cost / x_quantity;
2628 x_init_burd_cost_rate := x_txn_burdened_cost / x_quantity;
2629 x_init_revenue_rate := x_txn_revenue / x_quantity;
2630 x_etc_init_raw_cost_rate := (x_txn_raw_cost - x_txn_init_raw_cost) / x_quantity;
2631 x_etc_init_burd_cost_rate := (x_txn_burdened_cost - x_txn_init_burdened_cost) / x_quantity;
2632 x_etc_init_revenue_rate := (x_txn_revenue - x_txn_init_revenue) / x_quantity;
2633 end if; -- x_txn_quantity = 0
2634 end if; -- x_txn_quantity is not null
2635 EXCEPTION
2636 WHEN NO_DATA_FOUND THEN
2637 /*
2638 x_return_status := FND_API.G_RET_STS_ERROR;
2639 x_msg_count := 1;
2640 x_msg_data := SQLERRM;
2641 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2642 p_procedure_name => 'get_initial_budget_line_info');
2643 */
2644 x_start_date := null;
2645 x_end_date := null;
2646 x_period_name := null;
2647 x_quantity := null;
2648 x_txn_raw_cost := null;
2649 x_txn_burdened_cost := null;
2650 x_txn_revenue := null;
2651 x_init_quantity := null;
2652 x_txn_init_raw_cost := null;
2653 x_txn_init_burdened_cost := null;
2654 x_txn_init_revenue := null;
2655 x_init_raw_cost_rate := null;
2656 x_init_burd_cost_rate := null;
2657 x_init_revenue_rate := null;
2658 x_etc_init_raw_cost_rate := null;
2659 x_etc_init_burd_cost_rate := null;
2660 x_etc_init_revenue_rate := null;
2661 return;
2662 WHEN OTHERS THEN
2663 FND_MSG_PUB.ADD_EXC_MSG (p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2664 p_procedure_name => 'get_common_budget_version_info_fcst');
2665 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2666 x_msg_data := SQLERRM;
2667 END get_initial_budget_line_info;
2668
2669
2670 /* REVISION HISTORY:
2671 * 07/16/2004 dlai - instead of looping through each element_version_id and
2672 * calling add_planning_transaction each time, we can now just call
2673 * add_planning_transaction once with the p_one_to_one_mapping param
2674 * 01/09/2004 sagarwal - Removed Commented out code from add_new_resource_assignments
2675 * and re-wrote this API. Old Code for this API can be reffered in
2676 * version 115.29 of PAFPPEUB.pls
2677 */
2678 PROCEDURE add_new_resource_assignments
2679 (p_context IN VARCHAR2,
2680 p_project_id IN pa_budget_versions.project_id%TYPE,
2681 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
2682 p_task_elem_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2683 p_resource_list_member_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2684 p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2685 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE(),
2686 p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2687 p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2688 p_revenue_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2689 p_cost_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2690 p_bill_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2691 p_burdened_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2692 p_unplanned_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_1_TBL_TYPE(),
2693 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2694 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2695 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
2696
2697 -- begin PL/SQL tables to pass to add_planning_transaction API
2698 l_task_elem_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2699 l_resource_list_member_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2700 l_quantity_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2701 l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2702 l_raw_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2703 l_burdened_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2704 l_revenue_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2705 l_cost_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2706 l_bill_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2707 l_burdened_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2708 l_unplanned_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2709 -- end PL/SQL tables to pass to add_planning_transaction API
2710
2711 --Start of variables used for debugging
2712 l_msg_count NUMBER :=0;
2713 l_data VARCHAR2(2000);
2714 l_msg_data VARCHAR2(2000);
2715 l_error_msg_code VARCHAR2(30);
2716 l_msg_index_out NUMBER;
2717 l_return_status VARCHAR2(2000);
2718 l_debug_mode VARCHAR2(30);
2719 --End of variables used for debugging
2720
2721 -- Start of Variable used for comparisons and calling ADD PLAN TXN API
2722 l_structure_version_id PA_PROJ_ELEMENT_VERSIONS.PARENT_STRUCTURE_VERSION_ID%TYPE;
2723 l_bl_already_exists VARCHAR2(1) := 'N';
2724 l_rec_already_exists VARCHAR2(1) := 'N';
2725 l_index NUMBER := 1;
2726
2727 BEGIN
2728
2729 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2730 l_debug_mode := NVL(l_debug_mode, 'Y');
2731 x_msg_count := 0;
2732 x_return_status := FND_API.G_RET_STS_SUCCESS;
2733 PA_DEBUG.Set_Curr_Function( p_function => 'PAFPPEUB.add_new_resource_assignments',
2734 p_debug_mode => l_debug_mode );
2735
2736 IF l_debug_mode = 'Y' THEN
2737 pa_debug.g_err_stage:='Adding minimum Validations Here - COUNT of tables to be Same';
2738 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2739 END IF;
2740 IF (p_task_elem_version_id_tbl.COUNT <> p_resource_list_member_id_tbl.COUNT OR
2741 p_resource_list_member_id_tbl.COUNT <> p_currency_code_tbl.COUNT) THEN
2742
2743 IF l_debug_mode = 'Y' THEN
2744 pa_debug.g_err_stage:='p_task_elem_version_id_tbl.COUNT : '||p_task_elem_version_id_tbl.COUNT ;
2745 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2746
2747 pa_debug.g_err_stage:='p_resource_list_member_id_tbl.COUNT : '||p_resource_list_member_id_tbl.COUNT ;
2748 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2749
2750 pa_debug.g_err_stage:='p_currency_code_tbl.COUNT : '||p_currency_code_tbl.COUNT ;
2751 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2752 END IF;
2753 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2754 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2755 p_token1 => 'PROCEDURENAME',
2756 p_value1 => 'PAFPPTPB.add_new_resource_assignments',
2757 p_token2 => 'STAGE',
2758 p_value2 => 'I/P Table Counts are not Equal');
2759 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2760 END IF;
2761
2762 IF (p_task_elem_version_id_tbl.COUNT = 0) THEN
2763 IF l_debug_mode = 'Y' THEN
2764 pa_debug.g_err_stage:='elem_version_id table is empty - RETURNING ... ';
2765 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2766 END IF;
2767 pa_debug.reset_curr_function;
2768 RETURN;
2769 END IF;
2770
2771 IF l_debug_mode = 'Y' THEN
2772 pa_debug.g_err_stage:='Fetching PARENT_STRUCTURE_VERSION_ID';
2773 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2774 END IF;
2775 l_structure_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(p_project_id);
2776
2777 /*
2778 Scheme Used Below To derive Data to be passed to Add Planning TXN API.
2779 Let the Folowing I/P Data is passed to this API.
2780 1) t1 r1 c1
2781 2) t1 r1 c1
2782 3) t2 r2 c2
2783 4) t2 r2 c3
2784 5) t3 r3 c3
2785
2786 System State is such that Budget Lines Already Exists for
2787 1) t2 r2 c3
2788 2) t3 r3 c3
2789
2790 In this case add_planning_txn API should be called with the following data
2791 1)t1 r1 c1 and
2792 3)t2 r2 c2
2793 Basically records 2)4) and 5) have to be skipped.
2794
2795 For Dev reference -
2796 By I/P Set of tables - parameters passed to add_new_resource_assignments
2797 are referred.
2798 By O/P Set of tables - parameters passed to add_planning_transaction API
2799 are referred.
2800
2801 For Each Element Passed in the I/P Set of tables
2802 Check If Budget line exists for task/rlm/currency combination
2803 If Budget line already exists then jump to next element of I/P tables
2804 and skip the current I/P Record.
2805 ElsIf Budget line does not exist then
2806 Check if the if a record already exists in the O/P Set of Tables
2807 for task/rlm/currency I/P combination.
2808 If record does not exists then populate the O/P set of tables
2809 Else if a record already exists then skip the record for
2810 for task/rlm/currency I/P combination.
2811 */
2812
2813 IF p_task_elem_version_id_tbl.COUNT > 0 THEN
2814 FOR i IN p_task_elem_version_id_tbl.FIRST .. p_task_elem_version_id_tbl.LAST LOOP
2815 IF l_debug_mode = 'Y' THEN
2816 pa_debug.g_err_stage:='Loop 1 : p_task_elem_version_id_tbl('||i||') - '||p_task_elem_version_id_tbl(i);
2817 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2818 END IF;
2819
2820 -- Resetting flags Used
2821 l_bl_already_exists := 'N';
2822 l_rec_already_exists := 'N';
2823
2824 IF l_debug_mode = 'Y' THEN
2825 pa_debug.g_err_stage:='Checking if budget lines exist or not';
2826 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2827 END IF;
2828
2829 -- For a Project level record p_task_elem_version_id_tbl is passed as 0
2830 IF p_task_elem_version_id_tbl(i) <> 0 THEN -- For Task level record
2831 BEGIN
2832 SELECT 'Y'
2833 INTO l_bl_already_exists
2834 FROM DUAL
2835 WHERE EXISTS ( SELECT 1
2836 FROM PA_BUDGET_LINES PBL,PA_RESOURCE_ASSIGNMENTS PRA,PA_PROJ_ELEMENT_VERSIONS PEV
2837 WHERE PRA.PROJECT_ID = p_project_id
2838 AND PRA.BUDGET_VERSION_ID = p_budget_version_id
2839 AND PRA.RESOURCE_LIST_MEMBER_ID = p_resource_list_member_id_tbl(i)
2840 AND PEV.PROJ_ELEMENT_ID = PRA.TASK_ID
2841 AND PEV.PARENT_STRUCTURE_VERSION_ID = l_structure_version_id
2842 AND PEV.ELEMENT_VERSION_ID = p_task_elem_version_id_tbl(i)
2843 AND PBL.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
2844 AND PBL.TXN_CURRENCY_CODE = p_currency_code_tbl(i) );
2845
2846 IF l_debug_mode = 'Y' THEN
2847 pa_debug.g_err_stage:='Budget Lines EXIST - l_bl_already_exists :'||l_bl_already_exists;
2848 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2849 END IF;
2850
2851 EXCEPTION
2852 WHEN NO_DATA_FOUND THEN
2853 l_bl_already_exists := 'N';
2854 IF l_debug_mode = 'Y' THEN
2855 pa_debug.g_err_stage:='Budget Lines DONT EXIST - l_bl_already_exists :'||l_bl_already_exists;
2856 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2857 END IF;
2858 END;
2859
2860 ELSE -- For Project level record
2861
2862 BEGIN
2863 -- SQL Repository Bug 4884718; SQL ID 14903213
2864 -- Fixed Merge Join Cartesian violation by commenting out
2865 -- PA_PROJ_ELEMENT_VERSIONS from the FROM clause of the
2866 -- query below. It seems to be a copy/past artifact, as it
2867 -- is not references anywhere in the WHERE clause.
2868
2869 SELECT 'Y'
2870 INTO l_bl_already_exists
2871 FROM DUAL
2872 WHERE EXISTS ( SELECT 1
2873 FROM PA_BUDGET_LINES PBL,PA_RESOURCE_ASSIGNMENTS PRA
2874 --,PA_PROJ_ELEMENT_VERSIONS PEV /* Bug 4884718; SQL ID 14903213 */
2875 WHERE PRA.PROJECT_ID = p_project_id
2876 AND PRA.BUDGET_VERSION_ID = p_budget_version_id
2877 AND PRA.RESOURCE_LIST_MEMBER_ID = p_resource_list_member_id_tbl(i)
2878 AND PRA.TASK_ID = 0
2879 AND PBL.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
2880 AND PBL.TXN_CURRENCY_CODE = p_currency_code_tbl(i) );
2881
2882 IF l_debug_mode = 'Y' THEN
2883 pa_debug.g_err_stage:='Budget Lines EXIST - l_bl_already_exists :'||l_bl_already_exists;
2884 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2885 END IF;
2886
2887 EXCEPTION
2888 WHEN NO_DATA_FOUND THEN
2889 l_bl_already_exists := 'N';
2890 IF l_debug_mode = 'Y' THEN
2891 pa_debug.g_err_stage:='Budget Lines DONT EXIST - l_bl_already_exists :'||l_bl_already_exists;
2892 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2893 END IF;
2894 END;
2895 END IF;
2896
2897 -- Note record will be skipped if the BL already exists
2898 -- If BL/RA does not exists for I/P params Loop through Output Tables
2899 -- By Output tables, Tables to be passed to Add API are referred.
2900 IF l_bl_already_exists = 'N' THEN
2901
2902 IF l_debug_mode = 'Y' THEN
2903 pa_debug.g_err_stage:='BL Does Not Exist';
2904 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2905 END IF;
2906
2907 -- If Output Table is not Empty
2908 IF l_task_elem_version_id_tbl.COUNT > 0 THEN
2909 -- Loop Though Output Table
2910 FOR k IN l_task_elem_version_id_tbl.FIRST .. l_task_elem_version_id_tbl.LAST LOOP
2911 IF l_debug_mode = 'Y' THEN
2912 pa_debug.g_err_stage:='Loop 3 : l_task_elem_version_id_tbl('||k||') - '||l_task_elem_version_id_tbl(k);
2913 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2914 END IF;
2915 -- Compare if I/P Params have alreayd been added to output table or not.
2916 IF ((p_task_elem_version_id_tbl(i) = l_task_elem_version_id_tbl(k)) AND
2917 (p_resource_list_member_id_tbl(i) = l_resource_list_member_id_tbl(k)) AND
2918 (p_currency_code_tbl(i) = l_currency_code_tbl(k))) THEN
2919 -- If Already Added Set l_rec_already_exists to Y
2920 IF l_debug_mode = 'Y' THEN
2921 pa_debug.g_err_stage:='Output Rec Exists';
2922 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2923 END IF;
2924 l_rec_already_exists := 'Y';
2925 EXIT;
2926 ELSE
2927 --Set l_rec_already_exists to N
2928 l_rec_already_exists := 'N';
2929 END IF;
2930 END LOOP;
2931 ELSE
2932 -- If Output Table is Empty Set l_rec_already_exists to N
2933 l_rec_already_exists := 'N';
2934 END IF;
2935
2936 -- If Output Table does not have the I/P Rec add I/P Rec to Output Tables
2937 IF l_rec_already_exists = 'N' THEN
2938 IF l_debug_mode = 'Y' THEN
2939 pa_debug.g_err_stage:='Add to Output Rec';
2940 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2941 END IF;
2942
2943 l_task_elem_version_id_tbl.extend(1);
2944 l_resource_list_member_id_tbl.extend(1);
2945 l_quantity_tbl.extend(1);
2946 l_currency_code_tbl.extend(1);
2947 l_raw_cost_tbl.extend(1);
2948 l_burdened_cost_tbl.extend(1);
2949 l_revenue_tbl.extend(1);
2950 l_cost_rate_tbl.extend(1);
2951 l_bill_rate_tbl.extend(1);
2952 l_burdened_rate_tbl.extend(1);
2953 l_unplanned_flag_tbl.extend(1);
2954
2955 l_task_elem_version_id_tbl(l_index) := p_task_elem_version_id_tbl(i);
2956 l_resource_list_member_id_tbl(l_index) := p_resource_list_member_id_tbl(i);
2957 l_currency_code_tbl(l_index) := p_currency_code_tbl(i);
2958 IF p_quantity_tbl.EXISTS(i) THEN
2959 l_quantity_tbl(l_index) := p_quantity_tbl(i);
2960 END IF;
2961 IF p_raw_cost_tbl.EXISTS(i) THEN
2962 l_raw_cost_tbl(l_index) := p_raw_cost_tbl(i);
2963 END IF;
2964 IF p_burdened_cost_tbl.EXISTS(i) THEN
2965 l_burdened_cost_tbl(l_index) := p_burdened_cost_tbl(i);
2966 END IF;
2967 IF p_revenue_tbl.EXISTS(i) THEN
2968 l_revenue_tbl(l_index) := p_revenue_tbl(i);
2969 END IF;
2970 IF p_cost_rate_tbl.EXISTS(i) THEN
2971 l_cost_rate_tbl(l_index) := p_cost_rate_tbl(i);
2972 END IF;
2973 IF p_bill_rate_tbl.EXISTS(i) THEN
2974 l_bill_rate_tbl(l_index) := p_bill_rate_tbl(i);
2975 END IF;
2976 IF p_burdened_rate_tbl.EXISTS(i) THEN
2977 l_burdened_rate_tbl(l_index) := p_burdened_rate_tbl(i);
2978 END IF;
2979 IF p_unplanned_flag_tbl.EXISTS(i) THEN
2980 l_unplanned_flag_tbl(l_index) := p_unplanned_flag_tbl(i);
2981 END IF;
2982 l_index := l_index +1;
2983 END IF;
2984
2985 ELSE -- i.e. l_bl_already_exists = 'Y'
2986 IF l_debug_mode = 'Y' THEN
2987 pa_debug.g_err_stage:='BL Exists - Skipping Rec p_task_elem_version_id_tbl : '||p_task_elem_version_id_tbl(i);
2988 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2989
2990 pa_debug.g_err_stage:='BL Exists - Skipping Rec p_resource_list_member_id_tbl : '||p_resource_list_member_id_tbl(i);
2991 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2992
2993 pa_debug.g_err_stage:='BL Exists - Skipping Rec p_currency_code_tbl : '||p_currency_code_tbl(i);
2994 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2995 END IF;
2996 END IF;
2997
2998 END LOOP;
2999
3000 ELSE
3001 -- If Empty Tables are passed to Wrapper API. Simply Return
3002 IF l_debug_mode = 'Y' THEN
3003 pa_debug.g_err_stage:='elem_version_id table is empty - RETURNING ... '||p_context;
3004 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
3005 END IF;
3006 pa_debug.reset_curr_function;
3007 RETURN;
3008 END IF;
3009
3010 IF l_debug_mode = 'Y' THEN
3011 pa_debug.g_err_stage:='Calling Add Planning TXN API';
3012 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
3013 END IF;
3014
3015 pa_fp_planning_transaction_pub.add_planning_transactions
3016 (p_context => p_context,
3017 p_one_to_one_mapping_flag => 'Y',
3018 p_skip_duplicates_flag => 'Y',
3019 p_project_id => p_project_id,
3020 p_budget_version_id => p_budget_version_id,
3021 p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
3022 p_resource_list_member_id_tbl => l_resource_list_member_id_tbl,
3023 p_quantity_tbl => l_quantity_tbl,
3024 p_currency_code_tbl => l_currency_code_tbl,
3025 p_raw_cost_tbl => l_raw_cost_tbl,
3026 p_burdened_cost_tbl => l_burdened_cost_tbl,
3027 p_revenue_tbl => l_revenue_tbl,
3028 p_cost_rate_tbl => l_cost_rate_tbl,
3029 p_bill_rate_tbl => l_bill_rate_tbl,
3030 p_burdened_rate_tbl => l_burdened_rate_tbl,
3031 p_unplanned_flag_tbl => l_unplanned_flag_tbl,
3032 x_return_status => l_return_status,
3033 x_msg_count => l_msg_count,
3034 x_msg_data => l_msg_data);
3035
3036 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3037 IF l_debug_mode = 'Y' THEN
3038 pa_debug.g_err_stage:='ADD PLAN TXN Returned Error';
3039 pa_debug.write('PA_PLANNING_ELEMENT_UTILS.add_new_resource_assignments',pa_debug.g_err_stage,3);
3040 END IF;
3041
3042 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3043 END IF;
3044
3045 pa_debug.reset_curr_function;
3046
3047 EXCEPTION
3048 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3049 l_msg_count := FND_MSG_PUB.count_msg;
3050 IF l_msg_count = 1 THEN
3051 PA_INTERFACE_UTILS_PUB.get_messages
3052 (p_encoded => FND_API.G_TRUE
3053 ,p_msg_index => 1
3054 ,p_msg_count => l_msg_count
3055 ,p_msg_data => l_msg_data
3056 ,p_data => l_data
3057 ,p_msg_index_out => l_msg_index_out);
3058 x_msg_data := l_data;
3059 x_msg_count := l_msg_count;
3060 ELSE
3061 x_msg_count := l_msg_count;
3062 END IF;
3063 x_return_status := FND_API.G_RET_STS_ERROR;
3064 pa_debug.reset_curr_function;
3065
3066 WHEN OTHERS THEN
3067
3068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3069 x_msg_count := 1;
3070 x_msg_data := SQLERRM;
3071 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS'
3072 ,p_procedure_name => 'add_new_resource_assignments');
3073
3074 IF l_debug_mode = 'Y' THEN
3075 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3076 pa_debug.write('add_new_resource_assignments',pa_debug.g_err_stage,5);
3077 END IF;
3078 pa_debug.reset_curr_function;
3079 RAISE;
3080
3081 END add_new_resource_assignments;
3082
3083
3084 /* This procedure is used to retrieve:
3085 FND_API.G_MISS_NUM (x_num)
3086 FND_API.G_MISS_CHAR (x_char)
3087 FND_API.G_MISS_DATE (x_date)
3088 so it can be passed to the Java-side for further use
3089 */
3090 PROCEDURE get_fnd_miss_constants
3091 (x_num OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3092 x_char OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3093 x_date OUT NOCOPY DATE) IS --File.Sql.39 bug 4440895
3094 BEGIN
3095 x_num:=FND_API.G_MISS_NUM;
3096 x_char:=FND_API.G_MISS_CHAR;
3097 x_date:=FND_API.G_MISS_DATE;
3098 END get_fnd_miss_constants;
3099
3100 /* REVISION HISTORY
3101 * Created: 07/20/2004 by DLAI for bug 3747582
3102 */
3103 FUNCTION get_bv_name_from_id
3104 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) return VARCHAR2 is
3105 l_return_value pa_budget_versions.version_name%TYPE;
3106 BEGIN
3107 select version_name
3108 into l_return_value
3109 from pa_budget_versions
3110 where budget_version_id = p_budget_version_id;
3111 return l_return_value;
3112 EXCEPTION
3113 when NO_DATA_FOUND then
3114 return null;
3115 when others then
3116 return null;
3117 END get_bv_name_from_id;
3118
3119 --Created for bug 3546208. This function will return the financial structure version id for the project
3120 --id passed.
3121 FUNCTION get_fin_struct_id(p_project_id pa_projects_all.project_id%TYPE,
3122 p_budget_version_id pa_budget_versions.budget_Version_id%TYPE)
3123 RETURN NUMBER
3124 IS
3125 BEGIN
3126 IF (l_edit_plan_project_id IS NULL OR
3127 l_edit_plan_struct_id IS NULL OR
3128 l_edit_plan_bv_id IS NULL) OR
3129 (l_edit_plan_project_id <> NVL(p_project_id,-99) OR
3130 l_edit_plan_bv_id <> NVL(p_budget_version_id,-99)) THEN
3131
3132 SELECT DECODE(wp_version_flag,
3133 'Y',project_structure_version_id,
3134 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(project_id))
3135 INTO l_edit_plan_struct_id
3136 FROM pa_budget_versions
3137 WHERE budget_Version_id=p_budget_version_id;
3138
3139 l_edit_plan_project_id:= p_project_id;
3140 l_edit_plan_bv_id := p_budget_version_id;
3141
3142 END IF;
3143 RETURN l_edit_plan_struct_id;
3144 END get_fin_struct_id;
3145
3146
3147 -- This function returns the wbs element name, either from the wbs_element_version_id
3148 -- or from the proj_element_id. If using proj_element_id, then p_use_element_version_id_flag
3149 -- must be set to 'N'
3150 FUNCTION get_wbs_element_name_from_id
3151 (p_project_id IN pa_projects_all.project_id%TYPE,
3152 p_wbs_element_version_id IN pa_resource_assignments.wbs_element_version_id%TYPE,
3153 p_wbs_project_element_id IN pa_proj_element_versions.proj_element_id%TYPE,
3154 p_use_element_version_flag IN VARCHAR2)
3155 return VARCHAR2
3156 IS
3157 l_return_value pa_proj_elements.name%TYPE;
3158 BEGIN
3159 select name
3160 into l_return_value
3161 from pa_projects_all
3162 where project_id = p_project_id;
3163 -- if wbs_element_version_id is 0 or -1, then it is a project-level row
3164 if p_wbs_element_version_id = 0 or p_wbs_element_version_id = -1 then
3165 return l_return_value;
3166 else
3167 if p_use_element_version_flag = 'N' then
3168 -- using proj_element_id
3169 select pe.name
3170 into l_return_value
3171 from pa_proj_elements pe
3172 where pe.proj_element_id = p_wbs_project_element_id;
3173 return l_return_value;
3174 else
3175 -- using wbs_element_version_id
3176 select pe.name
3177 into l_return_value
3178 from pa_proj_element_versions pev,
3179 pa_proj_elements pe
3180 where pev.element_version_id = p_wbs_element_version_id and
3181 pev.proj_element_id = pe.proj_element_id;
3182 return l_return_value;
3183 end if; -- use wbs_element_version_id
3184 end if;
3185 EXCEPTION
3186 when NO_DATA_FOUND then
3187 return null;
3188 when others then
3189 return null;
3190 END get_wbs_element_name_from_id;
3191
3192
3193 FUNCTION get_proj_element_id
3194 (p_wbs_element_version_id IN pa_proj_element_versions.element_version_id%TYPE)
3195 return NUMBER
3196 IS
3197 l_return_value pa_proj_element_versions.proj_element_id%TYPE;
3198 BEGIN
3199 select proj_element_id
3200 into l_return_value
3201 from pa_proj_element_versions
3202 where element_version_id = p_wbs_element_version_id;
3203 return l_return_value;
3204 EXCEPTION
3205 when NO_DATA_FOUND then
3206 return null;
3207 when others then
3208 return null;
3209 END get_proj_element_id;
3210
3211 FUNCTION get_rbs_element_name_from_id
3212 (p_rbs_element_version_id IN pa_rbs_elements.rbs_element_id%TYPE)
3213 return VARCHAR2
3214 IS
3215 l_return_value pa_rbs_element_names_vl.resource_name%TYPE;
3216 BEGIN
3217 select names.resource_name
3218 into l_return_value
3219 from pa_rbs_elements ele,
3220 pa_rbs_element_names_vl names
3221 where ele.rbs_element_id = p_rbs_element_version_id and
3222 ele.rbs_element_name_id = names.rbs_element_name_id;
3223 return l_return_value;
3224 EXCEPTION
3225 when NO_DATA_FOUND then
3226 return null;
3227 when others then
3228 return null;
3229 END get_rbs_element_name_from_id;
3230
3231
3232 FUNCTION get_task_percent_complete
3233 (p_project_id IN pa_projects_all.project_id%TYPE,
3234 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3235 p_proj_element_id IN pa_proj_element_versions.proj_element_id%TYPE,
3236 p_calling_context IN VARCHAR2) return NUMBER
3237 is
3238 l_return_value NUMBER;
3239 l_structure_type VARCHAR2(30) := 'FINANCIAL'; -- could also be 'WORKPLAN'
3240 l_object_type VARCHAR2(30) := 'PA_TASKS';
3241 l_structure_status_flag VARCHAR2(1) := null;
3242 l_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE;
3243 l_structure_status VARCHAR2(30) := null;
3244 l_base_percent_complete NUMBER := null;
3245 l_return_status VARCHAR2(1);
3246 l_msg_count NUMBER;
3247 l_msg_data VARCHAR2(2000);
3248 BEGIN
3249 --hr_utility.trace_on(null, 'dlai');
3250 --hr_utility.trace('ENTERING GET PERCENT COMPLETE API');
3251 l_return_value := null;
3252 l_structure_version_id :=
3253 pa_planning_element_utils.get_fin_struct_id(p_project_id,p_budget_version_id);
3254 l_structure_status_flag :=
3255 PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(
3256 p_project_id,
3257 l_structure_version_id);
3258 if l_structure_status_flag = 'Y' then
3259 l_structure_status := 'PUBLISHED';
3260 else
3261 l_structure_status := 'WORKING';
3262 end if;
3263 /*
3264 hr_utility.trace('p_project_id is ' || to_char(p_project_id));
3265 hr_utility.trace('p_proj_element_id is ' || to_char(p_proj_element_id));
3266 hr_utility.trace('p_structure_type is ' || l_structure_type);
3267 hr_utility.trace('p_object_type is ' || l_object_type);
3268 hr_utility.trace('p_as_of_date is ' || to_char(trunc(SYSDATE)));
3269 hr_utility.trace('p_structure_version_id is ' || to_char(l_structure_version_id));
3270 hr_utility.trace('p_structure_status is ' || l_structure_status);
3271 hr_utility.trace('p_calling_context is ' || p_calling_context);
3272 hr_utility.trace('x_base_percent_complete is ' || to_char(l_base_percent_complete));
3273 hr_utility.trace('x_return_status is ' || l_return_status);
3274 hr_utility.trace('x_msg_count is ' || to_char(l_msg_count));
3275 hr_utility.trace('x_msg_data is ' || l_msg_data);
3276 */
3277 PA_PROGRESS_UTILS.REDEFAULT_BASE_PC
3278 (p_project_id => p_project_id,
3279 p_proj_element_id => p_proj_element_id,
3280 p_structure_type => l_structure_type,
3281 p_object_type => l_object_type,
3282 p_as_of_date => trunc(SYSDATE),
3283 p_structure_version_id => l_structure_version_id,
3284 p_structure_status => l_structure_status,
3285 p_calling_context => p_calling_context,
3286 x_base_percent_complete => l_base_percent_complete,
3287 x_return_status => l_return_status,
3288 x_msg_count => l_msg_count,
3289 x_msg_data => l_msg_data);
3290 if l_return_status = 'S' then
3291 l_return_value := l_base_percent_complete;
3292 end if;
3293 return l_return_value;
3294 EXCEPTION
3295 when NO_DATA_FOUND then
3296 return null;
3297 when others then
3298 return null;
3299 END get_task_percent_complete;
3300
3301 /* Bug 5524803: Added the below API to return the prior forecast version id
3302 to be used by PJI team.
3303 This procedure returns a different value of 'x_prior_fcst_version_id' compared
3304 to the get_finplan_bvids. This procedure has been specifically created for
3305 PJI team.
3306
3307 If p_budget_version_id is a BUDGET version:
3308 x_prior_fcst_version_id = Version previous to the current baselined version
3309 of PRIMARY FORECAST plan type
3310 If p_budget_version is a FORECAST version:
3311 x_prior_fcst_version_id = Version previous to the current baselined version
3312 of same plan type
3313 */
3314 FUNCTION get_prior_forecast_version_id
3315 (p_plan_version_id IN pa_budget_versions.budget_version_id%TYPE,
3316 p_project_id IN pa_projects_all.project_id%TYPE
3317 ) return NUMBER
3318 is
3319 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
3320 l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3321 l_curr_fcst_ver_id pa_budget_versions.budget_version_id%TYPE := NULL;
3322 l_curr_fcst_ver_num pa_budget_versions.version_number%TYPE := NULL;
3323 l_fp_type_id pa_budget_versions.fin_plan_type_id%TYPE := NULL;
3324 l_version_type pa_budget_versions.version_type%TYPE := NULL;
3325 x_prior_fcst_version_id pa_budget_versions.budget_version_id%TYPE := NULL;
3326
3327
3328 BEGIN
3329 select pt.plan_class_code,
3330 decode(bv.version_type,'COST','COST_ONLY','REVENUE','REVENUE_ONLY',
3331 'ALL','COST_AND_REV_SAME')
3332 into l_plan_class_code,
3333 l_fin_plan_pref_code
3334 from pa_budget_versions bv,
3335 pa_fin_plan_types_b pt
3336 where bv.budget_version_id = p_plan_version_id and
3337 bv.fin_plan_type_id = pt.fin_plan_type_id;
3338
3339 if l_plan_class_code = 'BUDGET' then
3340 -- CURRENT PLAN VERSION IS BUDGET PLAN CLASS
3341 -- RETRIEVE PRIMARY FORECAST BASELINED VERSION (IF IT EXISTS)
3342 if l_fin_plan_pref_code = 'COST_ONLY' then
3343 -- looking for PRIMARY COST FORECAST plan type
3344 begin
3345 select bv.budget_version_id,
3346 bv.version_number
3347 into l_curr_fcst_ver_id,
3348 l_curr_fcst_ver_num
3349 from pa_budget_versions bv
3350 where bv.project_id = p_project_id and
3351 bv.primary_cost_forecast_flag = 'Y' and
3352 bv.current_flag = 'Y';
3353
3354 select bv1.budget_version_id
3355 into x_prior_fcst_version_id
3356 from pa_budget_versions bv1
3357 where bv1.project_id = p_project_id
3358 and bv1.primary_cost_forecast_flag = 'Y'
3359 and bv1.budget_status_code = 'B'
3360 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3361 commented and added below for bug 6870324 */
3362 and bv1.version_number = (select max(bv2.version_number)
3363 from pa_budget_versions bv2
3364 where bv2.project_id = p_project_id
3365 and bv2.primary_cost_forecast_flag = 'Y'
3366 and bv2.budget_status_code = 'B'
3367 and bv2.version_number < l_curr_fcst_ver_num
3368 );
3369
3370
3371 exception
3372 when NO_DATA_FOUND then
3373 x_prior_fcst_version_id := -1;
3374 end;
3375 elsif l_fin_plan_pref_code = 'REVENUE_ONLY' then
3376 -- looking for PRIMARY REVENUE FORECAST plan type
3377 begin
3378 select bv.budget_version_id,
3379 bv.version_number
3380 into l_curr_fcst_ver_id,
3381 l_curr_fcst_ver_num
3382 from pa_budget_versions bv
3383 where bv.project_id = p_project_id and
3384 bv.primary_rev_forecast_flag = 'Y' and
3385 bv.current_flag = 'Y';
3386
3387 select bv1.budget_version_id
3388 into x_prior_fcst_version_id
3389 from pa_budget_versions bv1
3390 where bv1.project_id = p_project_id
3391 and bv1.primary_rev_forecast_flag = 'Y'
3392 and bv1.budget_status_code = 'B'
3393 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3394 commented and added below for bug 6870324 */
3395 and bv1.version_number = (select max(bv2.version_number)
3396 from pa_budget_versions bv2
3397 where bv2.project_id = p_project_id
3398 and bv2.primary_rev_forecast_flag = 'Y'
3399 and bv2.budget_status_code = 'B'
3400 and bv2.version_number < l_curr_fcst_ver_num
3401 );
3402
3403 exception
3404 when NO_DATA_FOUND then
3405 x_prior_fcst_version_id := -1;
3406 end;
3407 elsif l_fin_plan_pref_code = 'COST_AND_REV_SAME' then
3408 -- looking for PRIMARY 'ALL' FORECAST plan type
3409 begin
3410 select bv.budget_version_id,
3411 bv.version_number
3412 into l_curr_fcst_ver_id,
3413 l_curr_fcst_ver_num
3414 from pa_budget_versions bv
3415 where bv.project_id = p_project_id and
3416 bv.primary_rev_forecast_flag = 'Y' and
3417 bv.primary_cost_forecast_flag = 'Y' and
3418 bv.current_flag = 'Y';
3419 select bv1.budget_version_id
3420 into x_prior_fcst_version_id
3421 from pa_budget_versions bv1
3422 where bv1.project_id = p_project_id
3423 and bv1.primary_rev_forecast_flag = 'Y'
3424 and bv1.primary_cost_forecast_flag = 'Y'
3425 and bv1.budget_status_code = 'B'
3426 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3427 commented and added below for bug 6870324 */
3428 and bv1.version_number = (select max(bv2.version_number)
3429 from pa_budget_versions bv2
3430 where bv2.project_id = p_project_id
3431 and bv2.primary_rev_forecast_flag = 'Y'
3432 and bv2.primary_cost_forecast_flag = 'Y'
3433 and bv2.budget_status_code = 'B'
3434 and bv2.version_number < l_curr_fcst_ver_num
3435 );
3436
3437 exception
3438 when NO_DATA_FOUND then
3439
3440 begin
3441 select bv.budget_version_id,
3442 bv.version_number
3443 into l_curr_fcst_ver_id,
3444 l_curr_fcst_ver_num
3445 from pa_budget_versions bv
3446 where bv.project_id = p_project_id and
3447 bv.primary_cost_forecast_flag = 'Y' and
3448 bv.current_flag = 'Y';
3449
3450 select bv1.budget_version_id
3451 into x_prior_fcst_version_id
3452 from pa_budget_versions bv1
3453 where bv1.project_id = p_project_id
3454 and bv1.primary_cost_forecast_flag = 'Y'
3455 and bv1.budget_status_code = 'B'
3456 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3457 commented and added below for bug 6870324 */
3458 and bv1.version_number = (select max(bv2.version_number)
3459 from pa_budget_versions bv2
3460 where bv2.project_id = p_project_id
3461 and bv2.primary_cost_forecast_flag = 'Y'
3462 and bv2.budget_status_code = 'B'
3463 and bv2.version_number < l_curr_fcst_ver_num
3464 );
3465
3466 exception
3467 when NO_DATA_FOUND then
3468
3469 begin
3470 select bv.budget_version_id,
3471 bv.version_number
3472 into l_curr_fcst_ver_id,
3473 l_curr_fcst_ver_num
3474 from pa_budget_versions bv
3475 where bv.project_id = p_project_id and
3476 bv.primary_rev_forecast_flag = 'Y' and
3477 bv.current_flag = 'Y';
3478
3479 select bv1.budget_version_id
3480 into x_prior_fcst_version_id
3481 from pa_budget_versions bv1
3482 where bv1.project_id = p_project_id
3483 and bv1.primary_rev_forecast_flag = 'Y'
3484 and bv1.budget_status_code = 'B'
3485 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3486 commented and added below for bug 6870324 */
3487 and bv1.version_number = (select max(bv2.version_number)
3488 from pa_budget_versions bv2
3489 where bv2.project_id = p_project_id
3490 and bv2.primary_rev_forecast_flag = 'Y'
3491 and bv2.budget_status_code = 'B'
3492 and bv2.version_number < l_curr_fcst_ver_num
3493 );
3494
3495 exception
3496 when NO_DATA_FOUND then
3497 x_prior_fcst_version_id := -1;
3498 end;
3499 end;
3500 end;
3501
3502 end if; -- l_fin_plan_pref_code
3503
3504 else
3505 -- CURRENT PLAN VERSION IS FORECAST PLAN CLASS
3506 begin
3507 select bv2.budget_version_id,
3508 bv2.version_number,
3509 bv2.fin_plan_type_id,
3510 bv2.version_type
3511 into l_curr_fcst_ver_id,
3512 l_curr_fcst_ver_num,
3513 l_fp_type_id,
3514 l_version_type
3515 from pa_budget_versions bv1,
3516 pa_budget_versions bv2
3517 where bv1.project_id = p_project_id and
3518 bv1.budget_version_id = p_plan_version_id and
3519 bv1.project_id = bv2.project_id and
3520 bv1.fin_plan_type_id = bv2.fin_plan_type_id and
3521 bv1.version_type = bv2.version_type and
3522 bv2.current_flag = 'Y';
3523
3524 select budget_version_id
3525 into x_prior_fcst_version_id
3526 from pa_budget_versions
3527 where project_id = p_project_id and
3528 fin_plan_type_id = l_fp_type_id and
3529 version_type = l_version_type and
3530 budget_status_code = 'B' and
3531 /* version_number = l_curr_fcst_ver_num - 1;
3532 commented and added below for bug 6870324 */
3533 version_number = (select max(bv1.version_number)
3534 from pa_budget_versions bv1
3535 where bv1.project_id = p_project_id
3536 and bv1.fin_plan_type_id = l_fp_type_id
3537 and bv1.version_type = l_version_type
3538 and bv1.budget_status_code = 'B'
3539 and bv1.version_number < l_curr_fcst_ver_num
3540 );
3541
3542
3543 exception
3544 when NO_DATA_FOUND then
3545 x_prior_fcst_version_id := -1;
3546 end;
3547
3548 end if; -- l_plan_class_code
3549 RETURN x_prior_fcst_version_id;
3550 EXCEPTION
3551 when no_data_found then
3552 RETURN NULL;
3553 END get_prior_forecast_version_id;
3554
3555 END pa_planning_element_utils;