[Home] [Help]
PACKAGE BODY: APPS.PA_PLANNING_ELEMENT_UTILS
Source
1 PACKAGE BODY pa_planning_element_utils AS
2 /* $Header: PAFPPEUB.pls 120.16.12020000.5 2013/05/04 11:18:03 bpottipa 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 --13960670:Reverted the changes done to CURSOR get_fcst_line_amts_for_dates in 120.8 version
1625 CURSOR get_fcst_line_amts_for_dates IS
1626 SELECT ra.planning_start_date, -- x_planning_start_date
1627 ra.planning_end_date, -- x_planning_end_date
1628 ra.schedule_start_date, -- x_schedule_start_date
1629 ra.schedule_end_date, -- x_schedule_end_date
1630 /* rac.total_init_quantity, -- x_act_quantity
1631 DECODE(ra.rate_based_flag, 'Y', NVL(rac.total_display_quantity,0) - NVL(rac.total_init_quantity,0), 0), -- x_etc_quantity, Bug 5726773
1632 rac.total_display_quantity, -- x_fcst_quantity
1633 rac.total_txn_init_revenue, -- x_act_revenue_txn_cur
1634 rac.total_project_init_revenue, -- x_act_revenue_proj_cur
1635 rac.total_projfunc_init_revenue, -- x_act_revenue_proj_func_cur
1636 NVL(rac.total_txn_revenue,0) - NVL(rac.total_txn_init_revenue,0), -- x_etc_revenue_txn_cur
1637 NVL(rac.total_project_revenue,0) - NVL(rac.total_project_init_revenue,0), -- x_etc_revenue_proj_cur
1638 NVL(rac.total_projfunc_revenue,0) - NVL(rac.total_projfunc_init_revenue,0), -- x_etc_revenue_proj_func_cur
1639 rac.total_txn_revenue, -- x_fcst_revenue_txn_cur
1640 rac.total_project_revenue, -- x_fcst_revenue_proj_cur
1641 rac.total_projfunc_revenue, -- x_fcst_revenue_proj_func_cur
1642 rac.total_txn_init_raw_cost, -- x_act_raw_cost_txn_cur
1643 rac.total_project_init_raw_cost, -- x_act_raw_cost_proj_cur
1644 rac.total_projfunc_init_raw_cost, -- x_act_raw_cost_proj_func_cur
1645 NVL(rac.total_txn_raw_cost,0) - NVL(rac.total_txn_init_raw_cost,0), -- x_etc_raw_cost_txn_cur
1646 NVL(rac.total_project_raw_cost,0) - NVL(rac.total_project_init_raw_cost,0),-- x_etc_raw_cost_proj_cur
1647 NVL(rac.total_projfunc_raw_cost,0) - NVL(rac.total_projfunc_init_raw_cost,0),-- x_etc_raw_cost_proj_func_cur
1648 rac.total_txn_raw_cost, -- x_fcst_raw_cost_txn_cur
1649 rac.total_project_raw_cost, -- x_fcst_raw_cost_proj_cur
1650 rac.total_projfunc_raw_cost, -- x_fcst_raw_cost_proj_func_cur
1651 rac.total_txn_init_burdened_cost, -- x_act_burd_cost_txn_cur
1652 rac.total_project_init_bd_cost, -- x_act_burd_cost_proj_cur
1653 rac.total_projfunc_init_bd_cost, -- x_act_burd_cost_proj_func_cur
1654 NVL(rac.total_txn_burdened_cost,0) - NVL(rac.total_txn_init_burdened_cost,0), -- x_etc_burd_cost_txn_cur
1655 NVL(rac.total_project_burdened_cost,0) - NVL(rac.total_project_init_bd_cost,0), -- x_etc_burd_cost_proj_cur
1656 NVL(rac.total_projfunc_burdened_cost,0) - NVL(rac.total_projfunc_init_bd_cost,0), -- x_etc_burd_cost_proj_func_cur
1657 rac.total_txn_burdened_cost, -- x_fcst_burd_cost_txn_cur
1658 rac.total_project_burdened_cost, -- x_fcst_burd_cost_proj_cur
1659 rac.total_projfunc_burdened_cost, -- x_fcst_burd_cost_proj_func_cur
1660 */
1661 SUM(bl.init_quantity), -- x_act_quantity
1662 SUM(NVL(bl.quantity,0)) - SUM(NVL(bl.init_quantity,0)), -- x_etc_quantity
1663 SUM(bl.quantity), -- x_fcst_quantity
1664 SUM(txn_init_revenue), -- x_act_revenue_txn_cur
1665 SUM(bl.project_init_revenue), -- x_act_revenue_proj_cur
1666 SUM(bl.init_revenue), -- x_act_revenue_proj_func_cur
1667 SUM(NVL(bl.txn_revenue,0)) - SUM(NVL(bl.txn_init_revenue,0)), -- x_etc_revenue_txn_cur
1668 SUM(NVL(bl.project_revenue,0)) - SUM(NVL(bl.project_init_revenue,0)), -- x_etc_revenue_proj_cur
1669 SUM(NVL(bl.revenue,0)) - SUM(NVL(bl.init_revenue,0)), -- x_etc_revenue_proj_func_cur
1670 SUM(bl.txn_revenue), -- x_fcst_revenue_txn_cur
1671 SUM(bl.project_revenue), -- x_fcst_revenue_proj_cur
1672 SUM(bl.revenue), -- x_fcst_revenue_proj_func_cur
1673 SUM(bl.txn_init_raw_cost), -- x_act_raw_cost_txn_cur
1674 SUM(bl.project_init_raw_cost), -- x_act_raw_cost_proj_cur
1675 SUM(bl.init_raw_cost), -- x_act_raw_cost_proj_func_cur
1676 SUM(NVL(bl.txn_raw_cost,0)) - SUM(NVL(bl.txn_init_raw_cost,0)), -- x_etc_raw_cost_txn_cur
1677 SUM(NVL(bl.project_raw_cost,0)) - SUM(NVL(bl.project_init_raw_cost,0)), -- x_etc_raw_cost_proj_cur
1678 SUM(NVL(bl.raw_cost,0)) - SUM(NVL(bl.init_raw_cost,0)), -- x_etc_raw_cost_proj_func_cur
1679 SUM(bl.txn_raw_cost), -- x_fcst_raw_cost_txn_cur
1680 SUM(bl.project_raw_cost), -- x_fcst_raw_cost_proj_cur
1681 SUM(bl.raw_cost), -- x_fcst_raw_cost_proj_func_cur
1682 SUM(bl.txn_init_burdened_cost), -- x_act_burd_cost_txn_cur
1683 SUM(bl.project_init_burdened_cost), -- x_act_burd_cost_proj_cur
1684 SUM(bl.init_burdened_cost), -- x_act_burd_cost_proj_func_cur
1685 SUM(NVL(bl.txn_burdened_cost,0)) - SUM(NVL(bl.txn_init_burdened_cost,0)), -- x_etc_burd_cost_txn_cur
1686 SUM(NVL(bl.project_burdened_cost,0)) - SUM(NVL(bl.project_init_burdened_cost,0)), -- x_etc_burd_cost_proj_cur
1687 SUM(NVL(bl.burdened_cost,0)) - SUM(NVL(bl.init_burdened_cost,0)), -- x_etc_burd_cost_proj_func_cur
1688 SUM(bl.txn_burdened_cost), -- x_fcst_burd_cost_txn_cur
1689 SUM(bl.project_burdened_cost), -- x_fcst_burd_cost_proj_cur
1690 SUM(bl.burdened_cost), -- x_fcst_burd_cost_proj_func_cur
1691
1692 NULL, -- x_act_rev_rate (TO BE CALCULATED)
1693 NULL, -- x_etc_init_rev_rate (TO BE CALCULATED)
1694 NULL, -- x_etc_avg_rev_rate (TO BE CALCULATED)
1695 NULL, -- x_act_raw_cost_rate (TO BE CALCULATED)
1696 NULL, -- x_etc_init_raw_cost_rate (TO BE CALCULATED)
1697 NULL, -- x_etc_avg_raw_cost_rate (TO BE CALCULATED)
1698 NULL, -- x_act_burd_cost_rate (TO BE CALCULATED)
1699 NULL, -- x_etc_init_burd_cost_rate (TO BE CALCULATED)
1700 NULL, -- x_etc_avg_burd_cost_rate (TO BE CALCULATED)
1701 DECODE(po.fin_plan_preference_code,
1702 'COST_ONLY', TO_NUMBER(NULL),
1703 'REVENUE_ONLY', TO_NUMBER(NULL),
1704 DECODE(po.margin_derived_from_code,
1705 'B', SUM(bl.txn_init_revenue) - SUM(bl.txn_init_burdened_cost),
1706 SUM(bl.txn_init_revenue) - SUM(bl.txn_init_raw_cost))), -- x_act_margin_txn_cur
1707 -- 'B', rac.total_txn_init_revenue - rac.total_txn_init_burdened_cost,
1708 -- rac.total_txn_init_revenue - rac.total_txn_init_raw_cost)), -- x_act_margin_txn_cur
1709 DECODE(po.fin_plan_preference_code,
1710 'COST_ONLY', TO_NUMBER(NULL),
1711 'REVENUE_ONLY', TO_NUMBER(NULL),
1712 DECODE(po.margin_derived_from_code,
1713 'B', SUM(bl.project_init_revenue) - SUM(bl.project_init_burdened_cost),
1714 SUM(bl.project_init_revenue) - SUM(bl.project_init_raw_cost))), -- x_act_margin_proj_cur
1715 -- 'B', rac.total_project_init_revenue - rac.total_project_init_bd_cost,
1716 -- rac.total_project_init_revenue - rac.total_project_init_raw_cost)), -- x_act_margin_proj_cur
1717 DECODE(po.fin_plan_preference_code,
1718 'COST_ONLY', TO_NUMBER(NULL),
1719 'REVENUE_ONLY', TO_NUMBER(NULL),
1720 DECODE(po.margin_derived_from_code,
1721 'B', SUM(bl.init_revenue) - SUM(bl.init_burdened_cost),
1722 SUM(bl.init_revenue) - SUM(bl.init_raw_cost))), -- x_act_margin_proj_func_cur
1723 -- 'B', rac.total_projfunc_init_revenue - rac.total_projfunc_init_bd_cost,
1724 -- rac.total_projfunc_init_revenue - rac.total_projfunc_init_raw_cost)), -- x_act_margin_proj_func_cur
1725 NULL, -- x_etc_margin_txn_cur (TO BE POPULATED)
1726 NULL, -- x_etc_margin_proj_cur (TO BE POPULATED)
1727 NULL, -- x_etc_margin_proj_func_cur (TO BE POPULATED)
1728 DECODE(po.fin_plan_preference_code,
1729 'COST_ONLY', TO_NUMBER(NULL),
1730 'REVENUE_ONLY', TO_NUMBER(NULL),
1731 DECODE(po.margin_derived_from_code,
1732 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
1733 SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))), -- x_fcst_margin_txn_cur
1734 -- 'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
1735 -- rac.total_txn_revenue - rac.total_txn_raw_cost)), -- x_fcst_margin_txn_cur
1736 DECODE(po.fin_plan_preference_code,
1737 'COST_ONLY', TO_NUMBER(NULL),
1738 'REVENUE_ONLY', TO_NUMBER(NULL),
1739 DECODE(po.margin_derived_from_code,
1740 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
1741 SUM(bl.project_revenue) - SUM(bl.project_raw_cost))), -- x_fcst_margin_proj_cur
1742 -- 'B', rac.total_project_revenue - rac.total_project_burdened_cost,
1743 -- rac.total_project_revenue - rac.total_project_raw_cost)), -- x_fcst_margin_proj_cur
1744 DECODE(po.fin_plan_preference_code,
1745 'COST_ONLY', TO_NUMBER(NULL),
1746 'REVENUE_ONLY', TO_NUMBER(NULL),
1747 DECODE(po.margin_derived_from_code,
1748 'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
1749 SUM(bl.revenue) - SUM(bl.raw_cost))), -- x_fcst_margin_proj_func_cur
1750 -- 'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
1751 -- rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)), -- x_fcst_margin_proj_func_cur
1752 NULL, -- x_act_margin_pct (TO BE POPULATED)
1753 NULL, -- x_etc_margin_pct (TO BE POPULATED)
1754 NULL, -- x_fcst_margin_pct (TO BE POPULATED)
1755 po.margin_derived_from_code
1756 FROM pa_resource_assignments ra,
1757 pa_budget_lines bl,
1758 -- pa_resource_asgn_curr rac,
1759 pa_budget_versions bv,
1760 pa_proj_fp_options po
1761 WHERE ra.resource_assignment_id = l_resource_assignment_id AND
1762 ra.resource_assignment_id = bl.resource_assignment_id AND
1763 bl.txn_currency_code = p_txn_currency_code AND
1764 -- ra.resource_assignment_id = rac.resource_assignment_id AND
1765 -- rac.txn_currency_code = p_txn_currency_code AND
1766 ra.budget_version_id = bv.budget_version_id AND
1767 bv.budget_version_id = po.fin_plan_version_id AND
1768 po.fin_plan_option_level_code = 'PLAN_VERSION' AND
1769 bl.start_date BETWEEN p_line_start_date AND p_line_end_date
1770
1771 GROUP BY ra.transaction_source_code,
1772 -- ra.init_plan_quantity,
1773 ra.total_plan_quantity,
1774 bl.resource_assignment_id,
1775 bl.txn_currency_code,
1776 ra.planning_start_date,
1777 ra.planning_end_date,
1778 ra.schedule_start_date,
1779 ra.schedule_end_date,
1780 --ra.total_project_revenue,
1781 --ra.total_plan_revenue,
1782 --ra.total_project_raw_cost,
1783 --ra.total_plan_raw_cost,
1784 --ra.total_project_burdened_cost,
1785 --ra.total_plan_burdened_cost,
1786 po.margin_derived_from_code,
1787 po.fin_plan_preference_code;
1788
1789
1790
1791 -- ERROR HANDLING VARIABLES
1792 l_return_status VARCHAR2(1);
1793 l_msg_count NUMBER :=0;
1794 l_data VARCHAR2(2000);
1795 l_msg_data VARCHAR2(2000);
1796 l_error_msg_code VARCHAR2(30);
1797 l_msg_index_out NUMBER;
1798
1799 BEGIN
1800 x_return_status := FND_API.G_RET_STS_SUCCESS;
1801 if (p_budget_version_id is null) or (p_resource_assignment_id is null) then
1802 x_return_status := FND_API.G_RET_STS_ERROR;
1803 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1804 p_msg_name => 'PA_FP_COMMONVO_ERROR');
1805 else
1806 x_budget_version_id := p_budget_version_id;
1807 -- NOTE: p_resource_assignment_id refers to the resource assignment for the
1808 -- CURRENT DISPLAYED budget version. We need to figure out the resource
1809 -- assignment for the resource that correlates to p_budget_version_id
1810
1811 -- get task_id and resource_list_member_id that we will need to match
1812 select ra.project_id,
1813 ra.task_id,
1814 ra.resource_list_member_id,
1815 ra.unit_of_measure
1816 into l_project_id,
1817 l_task_id,
1818 l_resource_list_member_id,
1819 l_unit_of_measure
1820 from pa_resource_assignments ra
1821 where ra.resource_assignment_id = p_resource_assignment_id;
1822 open raid_csr;
1823 fetch raid_csr into raid_rec;
1824 if raid_csr%NOTFOUND then
1825 -- no match found: return null for all attribute values
1826 x_budget_version_id := null;
1827 x_planning_start_date := null;
1828 x_planning_end_date := null;
1829 x_schedule_start_date := null;
1830 x_schedule_end_date := null;
1831 x_act_quantity := null;
1832 x_etc_quantity := null;
1833 x_fcst_quantity := null;
1834 x_act_revenue_txn_cur := null;
1835 x_act_revenue_proj_cur := null;
1836 x_act_revenue_proj_func_cur := null;
1837 x_etc_revenue_txn_cur := null;
1838 x_etc_revenue_proj_cur := null;
1839 x_etc_revenue_proj_func_cur := null;
1840 x_fcst_revenue_txn_cur := null;
1841 x_fcst_revenue_proj_cur := null;
1842 x_fcst_revenue_proj_func_cur := null;
1843 x_act_raw_cost_txn_cur := null;
1844 x_act_raw_cost_proj_cur := null;
1845 x_act_raw_cost_proj_func_cur := null;
1846 x_etc_raw_cost_txn_cur := null;
1847 x_etc_raw_cost_proj_cur := null;
1848 x_etc_raw_cost_proj_func_cur := null;
1849 x_fcst_raw_cost_txn_cur := null;
1850 x_fcst_raw_cost_proj_cur := null;
1851 x_fcst_raw_cost_proj_func_cur := null;
1852 x_act_burd_cost_txn_cur := null;
1853 x_act_burd_cost_proj_cur := null;
1854 x_act_burd_cost_proj_func_cur := null;
1855 x_etc_burd_cost_txn_cur := null;
1856 x_etc_burd_cost_proj_cur := null;
1857 x_etc_burd_cost_proj_func_cur := null;
1858 x_fcst_burd_cost_txn_cur := null;
1859 x_fcst_burd_cost_proj_cur := null;
1860 x_fcst_burd_cost_proj_func_cur := null;
1861 x_act_rev_rate := null;
1862 x_etc_init_rev_rate := null;
1863 x_etc_avg_rev_rate := null;
1864 x_act_raw_cost_rate := null;
1865 x_etc_init_raw_cost_rate := null;
1866 x_etc_avg_raw_cost_rate := null;
1867 x_act_burd_cost_rate := null;
1868 x_etc_init_burd_cost_rate := null;
1869 x_etc_avg_burd_cost_rate := null;
1870 x_act_margin_txn_cur := null;
1871 x_act_margin_proj_cur := null;
1872 x_act_margin_proj_func_cur := null;
1873 x_etc_margin_txn_cur := null;
1874 x_etc_margin_proj_cur := null;
1875 x_etc_margin_proj_func_cur := null;
1876 x_fcst_margin_txn_cur := null;
1877 x_fcst_margin_proj_cur := null;
1878 x_fcst_margin_proj_func_cur := null;
1879 x_act_margin_pct := null;
1880 x_etc_margin_pct := null;
1881 x_fcst_margin_pct := null;
1882
1883 else
1884 l_resource_assignment_id := raid_rec.resource_assignment_id;
1885 -- figure out whether or not budget lines exist for the planning element
1886 open budget_lines_csr;
1887 fetch budget_lines_csr into budget_lines_rec;
1888 if budget_lines_csr%NOTFOUND then
1889 l_budget_lines_exist_flag := 'N';
1890 else
1891 l_budget_lines_exist_flag := 'Y';
1892 end if;
1893 close budget_lines_csr;
1894
1895 if l_budget_lines_exist_flag = 'N' then
1896 -- query pa_resource_assignments only
1897 -- bug 3979904: query pa_resource_assignments only if p_txn_currency_code
1898 -- is the same as project currency or project functional currency
1899 begin
1900 select ra.planning_start_date, -- x_planning_start_date
1901 ra.planning_end_date, -- x_planning_end_date
1902 ra.schedule_start_date, -- x_schedule_start_date
1903 ra.schedule_end_date, -- x_schedule_end_date
1904 to_number(null), -- x_act_quantity
1905 to_number(null), -- x_etc_quantity
1906 to_number(null), -- x_fcst_quantity
1907 to_number(null), -- x_act_revenue_txn_cur
1908 to_number(null), -- x_act_revenue_proj_cur
1909 to_number(null), -- x_act_revenue_proj_func_cur
1910 to_number(null), -- x_etc_revenue_txn_cur
1911 to_number(null), -- x_etc_revenue_proj_cur
1912 to_number(null), -- x_etc_revenue_proj_func_cur
1913 to_number(null), -- x_fcst_revenue_txn_cur
1914 to_number(null), -- x_fcst_revenue_proj_cur
1915 to_number(null), -- x_fcst_revenue_proj_func_cur
1916 to_number(null), -- x_act_raw_cost_txn_cur
1917 to_number(null), -- x_act_raw_cost_proj_cur
1918 to_number(null), -- x_act_raw_cost_proj_func_cur
1919 to_number(null), -- x_etc_raw_cost_txn_cur
1920 to_number(null), -- x_etc_raw_cost_proj_cur
1921 to_number(null), -- x_etc_raw_cost_proj_func_cur
1922 to_number(null), -- x_fcst_raw_cost_txn_cur
1923 to_number(null), -- x_fcst_raw_cost_proj_cur
1924 to_number(null), -- x_fcst_raw_cost_proj_func_cur
1925 to_number(null), -- x_act_burd_cost_txn_cur
1926 to_number(null), -- x_act_burd_cost_proj_cur
1927 to_number(null), -- x_act_burd_cost_proj_func_cur
1928 to_number(null), -- x_etc_burd_cost_txn_cur
1929 to_number(null), -- x_etc_burd_cost_proj_cur
1930 to_number(null), -- x_etc_burd_cost_proj_func_cur
1931 to_number(null), -- x_fcst_burd_cost_txn_cur
1932 to_number(null), -- x_fcst_burd_cost_proj_cur
1933 to_number(null), -- x_fcst_burd_cost_proj_func_cur
1934 to_number(null), -- x_act_rev_rate
1935 to_number(null), -- x_etc_init_rev_rate
1936 to_number(null), -- x_etc_avg_rev_rate,
1937 to_number(null), -- x_act_raw_cost_rate
1938 to_number(null), -- x_etc_init_raw_cost_rate
1939 to_number(null), -- x_etc_avg_raw_cost_rate
1940 to_number(null), -- x_act_burd_cost_rate
1941 to_number(null), -- x_etc_init_burd_cost_rate
1942 to_number(null), -- x_etc_avg_burd_cost_rate
1943 to_number(null), -- x_act_margin_txn_cur
1944 to_number(null), -- x_act_margin_proj_cur
1945 to_number(null), -- x_act_margin_proj_func_cur
1946 to_number(null), -- x_etc_margin_txn_cur
1947 to_number(null), -- x_etc_margin_proj_cur
1948 to_number(null), -- x_etc_margin_proj_func_cur
1949 to_number(null), -- x_fcst_margin_txn_cur
1950 to_number(null), -- x_fcst_margin_proj_cur
1951 to_number(null), -- x_fcst_margin_proj_func_cur
1952 to_number(null), -- x_act_margin_pct
1953 to_number(null), -- x_etc_margin_pct
1954 to_number(null), -- x_fcst_margin_pct
1955 po.margin_derived_from_code
1956 into x_planning_start_date,
1957 x_planning_end_date,
1958 x_schedule_start_date,
1959 x_schedule_end_date,
1960 x_act_quantity,
1961 x_etc_quantity,
1962 x_fcst_quantity,
1963 x_act_revenue_txn_cur,
1964 x_act_revenue_proj_cur,
1965 x_act_revenue_proj_func_cur,
1966 x_etc_revenue_txn_cur,
1967 x_etc_revenue_proj_cur,
1968 x_etc_revenue_proj_func_cur,
1969 x_fcst_revenue_txn_cur,
1970 x_fcst_revenue_proj_cur,
1971 x_fcst_revenue_proj_func_cur,
1972 x_act_raw_cost_txn_cur,
1973 x_act_raw_cost_proj_cur,
1974 x_act_raw_cost_proj_func_cur,
1975 x_etc_raw_cost_txn_cur,
1976 x_etc_raw_cost_proj_cur,
1977 x_etc_raw_cost_proj_func_cur,
1978 x_fcst_raw_cost_txn_cur,
1979 x_fcst_raw_cost_proj_cur,
1980 x_fcst_raw_cost_proj_func_cur,
1981 x_act_burd_cost_txn_cur,
1982 x_act_burd_cost_proj_cur,
1983 x_act_burd_cost_proj_func_cur,
1984 x_etc_burd_cost_txn_cur,
1985 x_etc_burd_cost_proj_cur,
1986 x_etc_burd_cost_proj_func_cur,
1987 x_fcst_burd_cost_txn_cur,
1988 x_fcst_burd_cost_proj_cur,
1989 x_fcst_burd_cost_proj_func_cur,
1990 x_act_rev_rate,
1991 x_etc_init_rev_rate,
1992 x_etc_avg_rev_rate,
1993 x_act_raw_cost_rate,
1994 x_etc_init_raw_cost_rate,
1995 x_etc_avg_raw_cost_rate,
1996 x_act_burd_cost_rate,
1997 x_etc_init_burd_cost_rate,
1998 x_etc_avg_burd_cost_rate,
1999 x_act_margin_txn_cur,
2000 x_act_margin_proj_cur,
2001 x_act_margin_proj_func_cur,
2002 x_etc_margin_txn_cur,
2003 x_etc_margin_proj_cur,
2004 x_etc_margin_proj_func_cur,
2005 x_fcst_margin_txn_cur,
2006 x_fcst_margin_proj_cur,
2007 x_fcst_margin_proj_func_cur,
2008 x_act_margin_pct,
2009 x_etc_margin_pct,
2010 x_fcst_margin_pct,
2011 l_margin_derived_from_code
2012 from pa_resource_assignments ra,
2013 pa_budget_versions bv,
2014 pa_proj_fp_options po
2015 where ra.resource_assignment_id = l_resource_assignment_id and
2016 ra.budget_version_id = bv.budget_version_id and
2017 bv.budget_version_id = po.fin_plan_version_id and
2018 po.fin_plan_option_level_code = 'PLAN_VERSION';
2019 exception
2020 when NO_DATA_FOUND then
2021 x_return_status := FND_API.G_RET_STS_ERROR;
2022 x_msg_count := 1;
2023 x_msg_data := SQLERRM;
2024 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2025 p_procedure_name => 'get_common_budget_version_info');
2026 end;
2027 -- Calculate the remaining OUT parameters
2028 if l_margin_derived_from_code = 'B' then
2029 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2030 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2031 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2032 else
2033 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2034 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2035 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2036 end if; -- l_margin_derived_from_code
2037 if x_act_revenue_proj_cur is not null then
2038 if x_act_revenue_proj_cur = 0 then
2039 x_act_margin_pct := 0;
2040 else
2041 x_act_margin_pct := 100 * x_act_margin_proj_cur / x_act_revenue_proj_cur;
2042 end if; -- x_act_revenue_proj_cur = 0
2043 end if;
2044 if x_etc_revenue_proj_cur is not null then
2045 if x_etc_revenue_proj_cur = 0 then
2046 x_etc_margin_pct := 0;
2047 else
2048 x_etc_margin_pct := 100 * x_etc_margin_proj_cur / x_etc_revenue_proj_cur;
2049 end if; -- x_etc_revenue_proj_cur = 0
2050 end if;
2051 if x_fcst_revenue_proj_cur is not null then
2052 if x_fcst_revenue_proj_cur = 0 then
2053 x_fcst_margin_pct := 0;
2054 else
2055 x_fcst_margin_pct := 100 * x_fcst_margin_proj_cur / x_fcst_revenue_proj_cur;
2056 end if; -- x_fcst_revenue_proj_cur = 0
2057 end if;
2058
2059 else
2060 -- budget lines exist, so query pa_resource_assignment and pa_budget_lines
2061 IF p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL THEN
2062
2063 OPEN get_fcst_line_amts_for_dates;
2064 FETCH get_fcst_line_amts_for_dates INTO
2065 x_planning_start_date,
2066 x_planning_end_date,
2067 x_schedule_start_date,
2068 x_schedule_end_date,
2069 x_act_quantity,
2070 x_etc_quantity,
2071 x_fcst_quantity,
2072 x_act_revenue_txn_cur,
2073 x_act_revenue_proj_cur,
2074 x_act_revenue_proj_func_cur,
2075 x_etc_revenue_txn_cur,
2076 x_etc_revenue_proj_cur,
2077 x_etc_revenue_proj_func_cur,
2078 x_fcst_revenue_txn_cur,
2079 x_fcst_revenue_proj_cur,
2080 x_fcst_revenue_proj_func_cur,
2081 x_act_raw_cost_txn_cur,
2082 x_act_raw_cost_proj_cur,
2083 x_act_raw_cost_proj_func_cur,
2084 x_etc_raw_cost_txn_cur,
2085 x_etc_raw_cost_proj_cur,
2086 x_etc_raw_cost_proj_func_cur,
2087 x_fcst_raw_cost_txn_cur,
2088 x_fcst_raw_cost_proj_cur,
2089 x_fcst_raw_cost_proj_func_cur,
2090 x_act_burd_cost_txn_cur,
2091 x_act_burd_cost_proj_cur,
2092 x_act_burd_cost_proj_func_cur,
2093 x_etc_burd_cost_txn_cur,
2094 x_etc_burd_cost_proj_cur,
2095 x_etc_burd_cost_proj_func_cur,
2096 x_fcst_burd_cost_txn_cur,
2097 x_fcst_burd_cost_proj_cur,
2098 x_fcst_burd_cost_proj_func_cur,
2099 x_act_rev_rate,
2100 x_etc_init_rev_rate,
2101 x_etc_avg_rev_rate,
2102 x_act_raw_cost_rate,
2103 x_etc_init_raw_cost_rate,
2104 x_etc_avg_raw_cost_rate,
2105 x_act_burd_cost_rate,
2106 x_etc_init_burd_cost_rate,
2107 x_etc_avg_burd_cost_rate,
2108 x_act_margin_txn_cur,
2109 x_act_margin_proj_cur,
2110 x_act_margin_proj_func_cur,
2111 x_etc_margin_txn_cur,
2112 x_etc_margin_proj_cur,
2113 x_etc_margin_proj_func_cur,
2114 x_fcst_margin_txn_cur,
2115 x_fcst_margin_proj_cur,
2116 x_fcst_margin_proj_func_cur,
2117 x_act_margin_pct,
2118 x_etc_margin_pct,
2119 x_fcst_margin_pct,
2120 l_margin_derived_from_code;
2121
2122 IF get_fcst_line_amts_for_dates%NOTFOUND THEN
2123 x_planning_start_date := null;
2124 x_planning_end_date := null;
2125 x_schedule_start_date := null;
2126 x_schedule_end_date := null;
2127 x_act_quantity := null;
2128 x_etc_quantity := null;
2129 x_fcst_quantity := null;
2130 x_act_revenue_txn_cur := null;
2131 x_act_revenue_proj_cur := null;
2132 x_act_revenue_proj_func_cur := null;
2133 x_etc_revenue_txn_cur := null;
2134 x_etc_revenue_proj_cur := null;
2135 x_etc_revenue_proj_func_cur := null;
2136 x_fcst_revenue_txn_cur := null;
2137 x_fcst_revenue_proj_cur := null;
2138 x_fcst_revenue_proj_func_cur := null;
2139 x_act_raw_cost_txn_cur := null;
2140 x_act_raw_cost_proj_cur := null;
2141 x_act_raw_cost_proj_func_cur := null;
2142 x_etc_raw_cost_txn_cur := null;
2143 x_etc_raw_cost_proj_cur := null;
2144 x_etc_raw_cost_proj_func_cur := null;
2145 x_fcst_raw_cost_txn_cur := null;
2146 x_fcst_raw_cost_proj_cur := null;
2147 x_fcst_raw_cost_proj_func_cur := null;
2148 x_act_burd_cost_txn_cur := null;
2149 x_act_burd_cost_proj_cur := null;
2150 x_act_burd_cost_proj_func_cur := null;
2151 x_etc_burd_cost_txn_cur := null;
2152 x_etc_burd_cost_proj_cur := null;
2153 x_etc_burd_cost_proj_func_cur := null;
2154 x_fcst_burd_cost_txn_cur := null;
2155 x_fcst_burd_cost_proj_cur := null;
2156 x_fcst_burd_cost_proj_func_cur := null;
2157 x_act_rev_rate := null;
2158 x_etc_init_rev_rate := null;
2159 x_etc_avg_rev_rate := null;
2160 x_act_raw_cost_rate := null;
2161 x_etc_init_raw_cost_rate := null;
2162 x_etc_avg_raw_cost_rate := null;
2163 x_act_burd_cost_rate := null;
2164 x_etc_init_burd_cost_rate := null;
2165 x_etc_avg_burd_cost_rate := null;
2166 x_act_margin_txn_cur := null;
2167 x_act_margin_proj_cur := null;
2168 x_act_margin_proj_func_cur := null;
2169 x_etc_margin_txn_cur := null;
2170 x_etc_margin_proj_cur := null;
2171 x_etc_margin_proj_func_cur := null;
2172 x_fcst_margin_txn_cur := null;
2173 x_fcst_margin_proj_cur := null;
2174 x_fcst_margin_proj_func_cur := null;
2175 x_act_margin_pct := null;
2176 x_etc_margin_pct := null;
2177 x_fcst_margin_pct := null;
2178 l_margin_derived_from_code := null;
2179 END IF;
2180
2181 CLOSE get_fcst_line_amts_for_dates;
2182
2183 ELSE --p_line_start_date IS NULL AND/OR p_line_end_date IS NULL THEN
2184 SELECT ra.planning_start_date, -- x_planning_start_date
2185 ra.planning_end_date, -- x_planning_end_date
2186 ra.schedule_start_date, -- x_schedule_start_date
2187 ra.schedule_end_date, -- x_schedule_end_date
2188 rac.total_init_quantity, -- x_act_quantity
2189 DECODE(ra.rate_based_flag, 'Y', NVL(rac.total_display_quantity,0) - NVL(rac.total_init_quantity,0), 0), -- x_etc_quantity, Bug 5726773
2190 rac.total_display_quantity, -- x_fcst_quantity
2191 rac.total_txn_init_revenue, -- x_act_revenue_txn_cur
2192 rac.total_project_init_revenue, -- x_act_revenue_proj_cur
2193 rac.total_projfunc_init_revenue, -- x_act_revenue_proj_func_cur
2194 NVL(rac.total_txn_revenue,0) - NVL(rac.total_txn_init_revenue,0), -- x_etc_revenue_txn_cur
2195 NVL(rac.total_project_revenue,0) - NVL(rac.total_project_init_revenue,0), -- x_etc_revenue_proj_cur
2196 NVL(rac.total_projfunc_revenue,0) - NVL(rac.total_projfunc_init_revenue,0), -- x_etc_revenue_proj_func_cur
2197 rac.total_txn_revenue, -- x_fcst_revenue_txn_cur
2198 rac.total_project_revenue, -- x_fcst_revenue_proj_cur
2199 rac.total_projfunc_revenue, -- x_fcst_revenue_proj_func_cur
2200 rac.total_txn_init_raw_cost, -- x_act_raw_cost_txn_cur
2201 rac.total_project_init_raw_cost, -- x_act_raw_cost_proj_cur
2202 rac.total_projfunc_init_raw_cost, -- x_act_raw_cost_proj_func_cur
2203 NVL(rac.total_txn_raw_cost,0) - NVL(rac.total_txn_init_raw_cost,0), -- x_etc_raw_cost_txn_cur
2204 NVL(rac.total_project_raw_cost,0) - NVL(rac.total_project_init_raw_cost,0),-- x_etc_raw_cost_proj_cur
2205 NVL(rac.total_projfunc_raw_cost,0) - NVL(rac.total_projfunc_init_raw_cost,0),-- x_etc_raw_cost_proj_func_cur
2206 rac.total_txn_raw_cost, -- x_fcst_raw_cost_txn_cur
2207 rac.total_project_raw_cost, -- x_fcst_raw_cost_proj_cur
2208 rac.total_projfunc_raw_cost, -- x_fcst_raw_cost_proj_func_cur
2209 rac.total_txn_init_burdened_cost, -- x_act_burd_cost_txn_cur
2210 rac.total_project_init_bd_cost, -- x_act_burd_cost_proj_cur
2211 rac.total_projfunc_init_bd_cost, -- x_act_burd_cost_proj_func_cur
2212 NVL(rac.total_txn_burdened_cost,0) - NVL(rac.total_txn_init_burdened_cost,0), -- x_etc_burd_cost_txn_cur
2213 NVL(rac.total_project_burdened_cost,0) - NVL(rac.total_project_init_bd_cost,0), -- x_etc_burd_cost_proj_cur
2214 NVL(rac.total_projfunc_burdened_cost,0) - NVL(rac.total_projfunc_init_bd_cost,0), -- x_etc_burd_cost_proj_func_cur
2215 rac.total_txn_burdened_cost, -- x_fcst_burd_cost_txn_cur
2216 rac.total_project_burdened_cost, -- x_fcst_burd_cost_proj_cur
2217 rac.total_projfunc_burdened_cost, -- x_fcst_burd_cost_proj_func_cur
2218 /*
2219 SUM(bl.init_quantity), -- x_act_quantity
2220 SUM(NVL(bl.quantity,0)) - SUM(NVL(bl.init_quantity,0)), -- x_etc_quantity
2221 SUM(bl.quantity), -- x_fcst_quantity
2222 SUM(txn_init_revenue), -- x_act_revenue_txn_cur
2223 SUM(bl.project_init_revenue), -- x_act_revenue_proj_cur
2224 SUM(bl.init_revenue), -- x_act_revenue_proj_func_cur
2225 SUM(NVL(bl.txn_revenue,0)) - SUM(NVL(bl.txn_init_revenue,0)), -- x_etc_revenue_txn_cur
2226 SUM(NVL(bl.project_revenue,0)) - SUM(NVL(bl.project_init_revenue,0)), -- x_etc_revenue_proj_cur
2227 SUM(NVL(bl.revenue,0)) - SUM(NVL(bl.init_revenue,0)), -- x_etc_revenue_proj_func_cur
2228 SUM(bl.txn_revenue), -- x_fcst_revenue_txn_cur
2229 SUM(bl.project_revenue), -- x_fcst_revenue_proj_cur
2230 SUM(bl.revenue), -- x_fcst_revenue_proj_func_cur
2231 SUM(bl.txn_init_raw_cost), -- x_act_raw_cost_txn_cur
2232 SUM(bl.project_init_raw_cost), -- x_act_raw_cost_proj_cur
2233 SUM(bl.init_raw_cost), -- x_act_raw_cost_proj_func_cur
2234 SUM(NVL(bl.txn_raw_cost,0)) - SUM(NVL(bl.txn_init_raw_cost,0)), -- x_etc_raw_cost_txn_cur
2235 SUM(NVL(bl.project_raw_cost,0)) - SUM(NVL(bl.project_init_raw_cost,0)), -- x_etc_raw_cost_proj_cur
2236 SUM(NVL(bl.raw_cost,0)) - SUM(NVL(bl.init_raw_cost,0)), -- x_etc_raw_cost_proj_func_cur
2237 SUM(bl.txn_raw_cost), -- x_fcst_raw_cost_txn_cur
2238 SUM(bl.project_raw_cost), -- x_fcst_raw_cost_proj_cur
2239 SUM(bl.raw_cost), -- x_fcst_raw_cost_proj_func_cur
2240 SUM(bl.txn_init_burdened_cost), -- x_act_burd_cost_txn_cur
2241 SUM(bl.project_init_burdened_cost), -- x_act_burd_cost_proj_cur
2242 SUM(bl.init_burdened_cost), -- x_act_burd_cost_proj_func_cur
2243 SUM(NVL(bl.txn_burdened_cost,0)) - SUM(NVL(bl.txn_init_burdened_cost,0)), -- x_etc_burd_cost_txn_cur
2244 SUM(NVL(bl.project_burdened_cost,0)) - SUM(NVL(bl.project_init_burdened_cost,0)), -- x_etc_burd_cost_proj_cur
2245 SUM(NVL(bl.burdened_cost,0)) - SUM(NVL(bl.init_burdened_cost,0)), -- x_etc_burd_cost_proj_func_cur
2246 SUM(bl.txn_burdened_cost), -- x_fcst_burd_cost_txn_cur
2247 SUM(bl.project_burdened_cost), -- x_fcst_burd_cost_proj_cur
2248 SUM(bl.burdened_cost), -- x_fcst_burd_cost_proj_func_cur
2249 */
2250 NULL, -- x_act_rev_rate (TO BE CALCULATED)
2251 NULL, -- x_etc_init_rev_rate (TO BE CALCULATED)
2252 NULL, -- x_etc_avg_rev_rate (TO BE CALCULATED)
2253 NULL, -- x_act_raw_cost_rate (TO BE CALCULATED)
2254 NULL, -- x_etc_init_raw_cost_rate (TO BE CALCULATED)
2255 NULL, -- x_etc_avg_raw_cost_rate (TO BE CALCULATED)
2256 NULL, -- x_act_burd_cost_rate (TO BE CALCULATED)
2257 NULL, -- x_etc_init_burd_cost_rate (TO BE CALCULATED)
2258 NULL, -- x_etc_avg_burd_cost_rate (TO BE CALCULATED)
2259 DECODE(po.fin_plan_preference_code,
2260 'COST_ONLY', TO_NUMBER(NULL),
2261 'REVENUE_ONLY', TO_NUMBER(NULL),
2262 DECODE(po.margin_derived_from_code,
2263 -- 'B', SUM(bl.txn_init_revenue) - SUM(bl.txn_init_burdened_cost),
2264 -- SUM(bl.txn_init_revenue) - SUM(bl.txn_init_raw_cost))), -- x_act_margin_txn_cur
2265 'B', rac.total_txn_init_revenue - rac.total_txn_init_burdened_cost,
2266 rac.total_txn_init_revenue - rac.total_txn_init_raw_cost)), -- x_act_margin_txn_cur
2267 DECODE(po.fin_plan_preference_code,
2268 'COST_ONLY', TO_NUMBER(NULL),
2269 'REVENUE_ONLY', TO_NUMBER(NULL),
2270 DECODE(po.margin_derived_from_code,
2271 -- 'B', SUM(bl.project_init_revenue) - SUM(bl.project_init_burdened_cost),
2272 -- SUM(bl.project_init_revenue) - SUM(bl.project_init_raw_cost))), -- x_act_margin_proj_cur
2273 'B', rac.total_project_init_revenue - rac.total_project_init_bd_cost,
2274 rac.total_project_init_revenue - rac.total_project_init_raw_cost)), -- x_act_margin_proj_cur
2275 DECODE(po.fin_plan_preference_code,
2276 'COST_ONLY', TO_NUMBER(NULL),
2277 'REVENUE_ONLY', TO_NUMBER(NULL),
2278 DECODE(po.margin_derived_from_code,
2279 -- 'B', SUM(bl.init_revenue) - SUM(bl.init_burdened_cost),
2280 -- SUM(bl.init_revenue) - SUM(bl.init_raw_cost))), -- x_act_margin_proj_func_cur
2281 'B', rac.total_projfunc_init_revenue - rac.total_projfunc_init_bd_cost,
2282 rac.total_projfunc_init_revenue - rac.total_projfunc_init_raw_cost)), -- x_act_margin_proj_func_cur
2283 NULL, -- x_etc_margin_txn_cur (TO BE POPULATED)
2284 NULL, -- x_etc_margin_proj_cur (TO BE POPULATED)
2285 NULL, -- x_etc_margin_proj_func_cur (TO BE POPULATED)
2286 DECODE(po.fin_plan_preference_code,
2287 'COST_ONLY', TO_NUMBER(NULL),
2288 'REVENUE_ONLY', TO_NUMBER(NULL),
2289 DECODE(po.margin_derived_from_code,
2290 -- 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
2291 -- SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))), -- x_fcst_margin_txn_cur
2292 'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
2293 rac.total_txn_revenue - rac.total_txn_raw_cost)), -- x_fcst_margin_txn_cur
2294 DECODE(po.fin_plan_preference_code,
2295 'COST_ONLY', TO_NUMBER(NULL),
2296 'REVENUE_ONLY', TO_NUMBER(NULL),
2297 DECODE(po.margin_derived_from_code,
2298 -- 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
2299 -- SUM(bl.project_revenue) - SUM(bl.project_raw_cost))), -- x_fcst_margin_proj_cur
2300 'B', rac.total_project_revenue - rac.total_project_burdened_cost,
2301 rac.total_project_revenue - rac.total_project_raw_cost)), -- x_fcst_margin_proj_cur
2302 DECODE(po.fin_plan_preference_code,
2303 'COST_ONLY', TO_NUMBER(NULL),
2304 'REVENUE_ONLY', TO_NUMBER(NULL),
2305 DECODE(po.margin_derived_from_code,
2306 -- 'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
2307 -- SUM(bl.revenue) - SUM(bl.raw_cost))), -- x_fcst_margin_proj_func_cur
2308 'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
2309 rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)), -- x_fcst_margin_proj_func_cur
2310 NULL, -- x_act_margin_pct (TO BE POPULATED)
2311 NULL, -- x_etc_margin_pct (TO BE POPULATED)
2312 NULL, -- x_fcst_margin_pct (TO BE POPULATED)
2313 po.margin_derived_from_code
2314 into x_planning_start_date,
2315 x_planning_end_date,
2316 x_schedule_start_date,
2317 x_schedule_end_date,
2318 x_act_quantity,
2319 x_etc_quantity,
2320 x_fcst_quantity,
2321 x_act_revenue_txn_cur,
2322 x_act_revenue_proj_cur,
2323 x_act_revenue_proj_func_cur,
2324 x_etc_revenue_txn_cur,
2325 x_etc_revenue_proj_cur,
2326 x_etc_revenue_proj_func_cur,
2327 x_fcst_revenue_txn_cur,
2328 x_fcst_revenue_proj_cur,
2329 x_fcst_revenue_proj_func_cur,
2330 x_act_raw_cost_txn_cur,
2331 x_act_raw_cost_proj_cur,
2332 x_act_raw_cost_proj_func_cur,
2333 x_etc_raw_cost_txn_cur,
2334 x_etc_raw_cost_proj_cur,
2335 x_etc_raw_cost_proj_func_cur,
2336 x_fcst_raw_cost_txn_cur,
2337 x_fcst_raw_cost_proj_cur,
2338 x_fcst_raw_cost_proj_func_cur,
2339 x_act_burd_cost_txn_cur,
2340 x_act_burd_cost_proj_cur,
2341 x_act_burd_cost_proj_func_cur,
2342 x_etc_burd_cost_txn_cur,
2343 x_etc_burd_cost_proj_cur,
2344 x_etc_burd_cost_proj_func_cur,
2345 x_fcst_burd_cost_txn_cur,
2346 x_fcst_burd_cost_proj_cur,
2347 x_fcst_burd_cost_proj_func_cur,
2348 x_act_rev_rate,
2349 x_etc_init_rev_rate,
2350 x_etc_avg_rev_rate,
2351 x_act_raw_cost_rate,
2352 x_etc_init_raw_cost_rate,
2353 x_etc_avg_raw_cost_rate,
2354 x_act_burd_cost_rate,
2355 x_etc_init_burd_cost_rate,
2356 x_etc_avg_burd_cost_rate,
2357 x_act_margin_txn_cur,
2358 x_act_margin_proj_cur,
2359 x_act_margin_proj_func_cur,
2360 x_etc_margin_txn_cur,
2361 x_etc_margin_proj_cur,
2362 x_etc_margin_proj_func_cur,
2363 x_fcst_margin_txn_cur,
2364 x_fcst_margin_proj_cur,
2365 x_fcst_margin_proj_func_cur,
2366 x_act_margin_pct,
2367 x_etc_margin_pct,
2368 x_fcst_margin_pct,
2369 l_margin_derived_from_code
2370 FROM pa_resource_assignments ra,
2371 -- pa_budget_lines bl,
2372 pa_resource_asgn_curr rac,
2373 pa_budget_versions bv,
2374 pa_proj_fp_options po
2375 where ra.resource_assignment_id = l_resource_assignment_id and
2376 -- ra.resource_assignment_id = bl.resource_assignment_id and
2377 -- bl.txn_currency_code = p_txn_currency_code and
2378 ra.resource_assignment_id = rac.resource_assignment_id and
2379 rac.txn_currency_code = p_txn_currency_code and
2380 ra.budget_version_id = bv.budget_version_id and
2381 bv.budget_version_id = po.fin_plan_version_id and
2382 po.fin_plan_option_level_code = 'PLAN_VERSION';
2383 /*
2384 group by ra.transaction_source_code,
2385 --ra.init_plan_quantity,
2386 --ra.total_plan_quantity,
2387 bl.resource_assignment_id,
2388 bl.txn_currency_code,
2389 ra.planning_start_date,
2390 ra.planning_end_date,
2391 ra.schedule_start_date,
2392 ra.schedule_end_date,
2393 --ra.total_project_revenue,
2394 --ra.total_plan_revenue,
2395 --ra.total_project_raw_cost,
2396 --ra.total_plan_raw_cost,
2397 --ra.total_project_burdened_cost,
2398 --ra.total_plan_burdened_cost,
2399 po.margin_derived_from_code,
2400 po.fin_plan_preference_code;
2401 */
2402 END IF; --p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL
2403
2404 -- CALCULATE THE RATE/MARGIN/MARGINPCT VALUES
2405
2406 pa_planning_element_utils.get_initial_budget_line_info
2407 (p_resource_assignment_id => p_resource_assignment_id,
2408 p_txn_currency_code => p_txn_currency_code,
2409 p_line_start_date => p_line_start_date,
2410 p_line_end_date => p_line_end_date,
2411 x_start_date => l_start_date,
2412 x_end_date => l_end_date,
2413 x_period_name => l_period_name,
2414 x_quantity => l_quantity,
2415 x_txn_raw_cost => l_txn_raw_cost,
2416 x_txn_burdened_cost => l_txn_burdened_cost,
2417 x_txn_revenue => l_txn_revenue,
2418 x_init_quantity => l_init_quantity,
2419 x_txn_init_raw_cost => l_txn_init_raw_cost,
2420 x_txn_init_burdened_cost => l_txn_init_burdened_cost,
2421 x_txn_init_revenue => l_txn_init_revenue,
2422 x_init_raw_cost_rate => l_init_raw_cost_rate,
2423 x_init_burd_cost_rate => l_init_burd_cost_rate,
2424 x_init_revenue_rate => l_init_revenue_rate,
2425 x_etc_init_raw_cost_rate => l_etc_init_raw_cost_rate,
2426 x_etc_init_burd_cost_rate => l_etc_init_burd_cost_rate,
2427 x_etc_init_revenue_rate => l_etc_init_revenue_rate,
2428 x_return_status => l_return_status,
2429 x_msg_count => l_msg_count,
2430 x_msg_data => l_msg_data);
2431
2432 if x_act_quantity is not null then
2433 if x_act_quantity = 0 then
2434 x_act_rev_rate := 0;
2435 x_act_raw_cost_rate := 0;
2436 x_act_burd_cost_rate := 0;
2437 else
2438 x_act_rev_rate := x_act_revenue_txn_cur / x_act_quantity;
2439 x_act_raw_cost_rate := x_act_raw_cost_txn_cur / x_act_quantity;
2440 x_act_burd_cost_rate := x_act_burd_cost_txn_cur / x_act_quantity;
2441 end if; -- x_act_quantity = 0
2442 end if; -- x_act_quantity is not null
2443
2444 /* when calculating etc rates, use etc quantity, not fcst quantity */
2445 if x_etc_quantity is not null then
2446 if x_etc_quantity = 0 then
2447 x_etc_avg_rev_rate := 0;
2448 x_etc_avg_raw_cost_rate := 0;
2449 x_etc_avg_burd_cost_rate := 0;
2450 else
2451 x_etc_avg_rev_rate := x_etc_revenue_txn_cur / x_etc_quantity;
2452 x_etc_avg_raw_cost_rate := x_etc_raw_cost_txn_cur / x_etc_quantity;
2453 x_etc_avg_burd_cost_rate := x_etc_burd_cost_txn_cur / x_etc_quantity;
2454 end if; -- x_fcst_quantity = 0;
2455 else
2456 x_etc_avg_rev_rate := 0;
2457 x_etc_avg_raw_cost_rate := 0;
2458 x_etc_avg_burd_cost_rate := 0;
2459 end if; -- x_fcst_quantity is not null
2460
2461 x_etc_init_rev_rate := l_etc_init_revenue_rate;
2462 x_etc_init_raw_cost_rate := l_etc_init_raw_cost_rate;
2463 x_etc_init_burd_cost_rate := l_etc_init_burd_cost_rate;
2464
2465 if l_margin_derived_from_code = 'B' then
2466 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2467 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2468 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_burd_cost_txn_cur;
2469 else
2470 x_etc_margin_txn_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2471 x_etc_margin_proj_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2472 x_etc_margin_proj_func_cur := x_etc_revenue_txn_cur - x_etc_raw_cost_txn_cur;
2473 end if; -- l_margin_derived_from_code
2474 if x_act_revenue_proj_cur is not null then
2475 if x_act_revenue_proj_cur = 0 then
2476 x_act_margin_pct := 0;
2477 else
2478 x_act_margin_pct := 100 * x_act_margin_proj_cur / x_act_revenue_proj_cur;
2479 end if; -- x_act_revenue_proj_cur = 0
2480 end if;
2481 if x_etc_revenue_proj_cur is not null then
2482 if x_etc_revenue_proj_cur = 0 then
2483 x_etc_margin_pct := 0;
2484 else
2485 x_etc_margin_pct := 100 * x_etc_margin_proj_cur / x_etc_revenue_proj_cur;
2486 end if; -- x_etc_revenue_proj_cur = 0
2487 end if;
2488 if x_fcst_revenue_proj_cur is not null then
2489 if x_fcst_revenue_proj_cur = 0 then
2490 x_fcst_margin_pct := 0;
2491 else
2492 x_fcst_margin_pct := 100 * x_fcst_margin_proj_cur / x_fcst_revenue_proj_cur;
2493 end if; -- x_fcst_revenue_proj_cur = 0
2494 end if;
2495
2496 end if;
2497 end if;
2498 end if; -- if l_resource_assignment_id found
2499 close raid_csr;
2500 -- Check message stack for error messages
2501 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2502 l_msg_count := FND_MSG_PUB.count_msg;
2503 if l_msg_count = 1 THEN
2504 PA_INTERFACE_UTILS_PUB.get_messages
2505 (p_encoded => FND_API.G_TRUE
2506 ,p_msg_index => 1
2507 ,p_msg_count => l_msg_count
2508 ,p_msg_data => l_msg_data
2509 ,p_data => l_data
2510 ,p_msg_index_out => l_msg_index_out);
2511 x_msg_data := l_data;
2512 x_msg_count := l_msg_count;
2513 else
2514 x_msg_count := l_msg_count;
2515 end if;
2516 end if;
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 FND_MSG_PUB.ADD_EXC_MSG (p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2520 p_procedure_name => 'get_common_bv_info_fcst');
2521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2522 x_msg_data := SQLERRM;
2523 END get_common_bv_info_fcst;
2524
2525
2526 procedure get_initial_budget_line_info
2527 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2528 p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
2529 p_line_start_date IN pa_budget_lines.start_date%TYPE := to_date(NULL),
2530 p_line_end_date IN pa_budget_lines.end_date%TYPE := to_date(NULL),
2531 x_start_date OUT NOCOPY pa_budget_lines.start_date%TYPE, --File.Sql.39 bug 4440895
2532 x_end_date OUT NOCOPY pa_budget_lines.end_date%TYPE, --File.Sql.39 bug 4440895
2533 x_period_name OUT NOCOPY pa_budget_lines.period_name%TYPE, --File.Sql.39 bug 4440895
2534 x_quantity OUT NOCOPY pa_budget_lines.quantity%TYPE, --File.Sql.39 bug 4440895
2535 x_txn_raw_cost OUT NOCOPY pa_budget_lines.raw_cost%TYPE, --File.Sql.39 bug 4440895
2536 x_txn_burdened_cost OUT NOCOPY pa_budget_lines.burdened_cost%TYPE, --File.Sql.39 bug 4440895
2537 x_txn_revenue OUT NOCOPY pa_budget_lines.revenue%TYPE, --File.Sql.39 bug 4440895
2538 x_init_quantity OUT NOCOPY pa_budget_lines.init_quantity%TYPE, --File.Sql.39 bug 4440895
2539 x_txn_init_raw_cost OUT NOCOPY pa_budget_lines.txn_init_raw_cost%TYPE, --File.Sql.39 bug 4440895
2540 x_txn_init_burdened_cost OUT NOCOPY pa_budget_lines.txn_init_burdened_cost%TYPE, --File.Sql.39 bug 4440895
2541 x_txn_init_revenue OUT NOCOPY pa_budget_lines.txn_init_revenue%TYPE, --File.Sql.39 bug 4440895
2542 x_init_raw_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2543 x_init_burd_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2544 x_init_revenue_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2545 x_etc_init_raw_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2546 x_etc_init_burd_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2547 x_etc_init_revenue_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2548 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2549 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2550 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
2551 BEGIN
2552 x_msg_count := 0;
2553 x_return_status := FND_API.G_RET_STS_SUCCESS;
2554 IF p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL THEN
2555 select bl.start_date,
2556 bl.end_date,
2557 bl.period_name,
2558 -- bl.quantity,
2559 bl.display_quantity, --IPM
2560 bl.txn_raw_cost,
2561 bl.txn_burdened_cost,
2562 bl.txn_revenue,
2563 bl.init_quantity,
2564 bl.txn_init_raw_cost,
2565 bl.txn_init_burdened_cost,
2566 bl.txn_init_revenue
2567 into x_start_date,
2568 x_end_date,
2569 x_period_name,
2570 x_quantity,
2571 x_txn_raw_cost,
2572 x_txn_burdened_cost,
2573 x_txn_revenue,
2574 x_init_quantity,
2575 x_txn_init_raw_cost,
2576 x_txn_init_burdened_cost,
2577 x_txn_init_revenue
2578 from pa_budget_lines bl
2579 where bl.resource_assignment_id = p_resource_assignment_id and
2580 bl.txn_currency_code = p_txn_currency_code and
2581 bl.start_date BETWEEN p_line_start_date and p_line_end_date and
2582 start_date = (select min(start_date)
2583 from pa_budget_lines
2584 where resource_assignment_id = p_resource_assignment_id and
2585 txn_currency_code = p_txn_currency_code);
2586 ELSE -- p_line_start_date IS NULL AND/OR p_line_end_date IS NULL
2587 select bl.start_date,
2588 bl.end_date,
2589 bl.period_name,
2590 -- bl.quantity,
2591 bl.display_quantity, -- IPM
2592 bl.txn_raw_cost,
2593 bl.txn_burdened_cost,
2594 bl.txn_revenue,
2595 bl.init_quantity,
2596 bl.txn_init_raw_cost,
2597 bl.txn_init_burdened_cost,
2598 bl.txn_init_revenue
2599 into x_start_date,
2600 x_end_date,
2601 x_period_name,
2602 x_quantity,
2603 x_txn_raw_cost,
2604 x_txn_burdened_cost,
2605 x_txn_revenue,
2606 x_init_quantity,
2607 x_txn_init_raw_cost,
2608 x_txn_init_burdened_cost,
2609 x_txn_init_revenue
2610 from pa_budget_lines bl
2611 where bl.resource_assignment_id = p_resource_assignment_id and
2612 bl.txn_currency_code = p_txn_currency_code and
2613 start_date = (select min(start_date)
2614 from pa_budget_lines
2615 where resource_assignment_id = p_resource_assignment_id and
2616 txn_currency_code = p_txn_currency_code);
2617 END IF; -- p_line_start_date IS NOT NULL AND p_line_end_date IS NOT NULL
2618 -- CALCULATE THE RATES
2619 if x_quantity is not null then
2620 if x_quantity = 0 then
2621 x_init_raw_cost_rate := 0;
2622 x_init_burd_cost_rate := 0;
2623 x_init_revenue_rate := 0;
2624 x_etc_init_raw_cost_rate := 0;
2625 x_etc_init_burd_cost_rate := 0;
2626 x_etc_init_revenue_rate := 0;
2627 else
2628 x_init_raw_cost_rate := x_txn_raw_cost / x_quantity;
2629 x_init_burd_cost_rate := x_txn_burdened_cost / x_quantity;
2630 x_init_revenue_rate := x_txn_revenue / x_quantity;
2631 x_etc_init_raw_cost_rate := (x_txn_raw_cost - x_txn_init_raw_cost) / x_quantity;
2632 x_etc_init_burd_cost_rate := (x_txn_burdened_cost - x_txn_init_burdened_cost) / x_quantity;
2633 x_etc_init_revenue_rate := (x_txn_revenue - x_txn_init_revenue) / x_quantity;
2634 end if; -- x_txn_quantity = 0
2635 end if; -- x_txn_quantity is not null
2636 EXCEPTION
2637 WHEN NO_DATA_FOUND THEN
2638 /*
2639 x_return_status := FND_API.G_RET_STS_ERROR;
2640 x_msg_count := 1;
2641 x_msg_data := SQLERRM;
2642 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2643 p_procedure_name => 'get_initial_budget_line_info');
2644 */
2645 x_start_date := null;
2646 x_end_date := null;
2647 x_period_name := null;
2648 x_quantity := null;
2649 x_txn_raw_cost := null;
2650 x_txn_burdened_cost := null;
2651 x_txn_revenue := null;
2652 x_init_quantity := null;
2653 x_txn_init_raw_cost := null;
2654 x_txn_init_burdened_cost := null;
2655 x_txn_init_revenue := null;
2656 x_init_raw_cost_rate := null;
2657 x_init_burd_cost_rate := null;
2658 x_init_revenue_rate := null;
2659 x_etc_init_raw_cost_rate := null;
2660 x_etc_init_burd_cost_rate := null;
2661 x_etc_init_revenue_rate := null;
2662 return;
2663 WHEN OTHERS THEN
2664 FND_MSG_PUB.ADD_EXC_MSG (p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS',
2665 p_procedure_name => 'get_common_budget_version_info_fcst');
2666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2667 x_msg_data := SQLERRM;
2668 END get_initial_budget_line_info;
2669
2670
2671 /* REVISION HISTORY:
2672 * 07/16/2004 dlai - instead of looping through each element_version_id and
2673 * calling add_planning_transaction each time, we can now just call
2674 * add_planning_transaction once with the p_one_to_one_mapping param
2675 * 01/09/2004 sagarwal - Removed Commented out code from add_new_resource_assignments
2676 * and re-wrote this API. Old Code for this API can be reffered in
2677 * version 115.29 of PAFPPEUB.pls
2678 */
2679 PROCEDURE add_new_resource_assignments
2680 (p_context IN VARCHAR2,
2681 p_project_id IN pa_budget_versions.project_id%TYPE,
2682 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
2683 p_task_elem_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2684 p_resource_list_member_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2685 p_cbs_element_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),--bug#16200605
2686 p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2687 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE(),
2688 p_raw_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2689 p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2690 p_revenue_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2691 p_cost_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2692 p_bill_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2693 p_burdened_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2694 p_unplanned_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_1_TBL_TYPE(),
2695 p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE(), --added for Enc
2696 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2697 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2698 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
2699
2700 -- begin PL/SQL tables to pass to add_planning_transaction API
2701 l_task_elem_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2702 l_resource_list_member_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2703 l_cbs_element_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.PA_NUM_TBL_TYPE();--bug#16481402
2704 l_quantity_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2705 l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2706 l_raw_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2707 l_burdened_cost_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2708 l_revenue_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2709 l_cost_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2710 l_bill_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2711 l_burdened_rate_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2712 l_unplanned_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
2713 l_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(); --added for Enc
2714 -- end PL/SQL tables to pass to add_planning_transaction API
2715
2716 --Start of variables used for debugging
2717 l_msg_count NUMBER :=0;
2718 l_data VARCHAR2(2000);
2719 l_msg_data VARCHAR2(2000);
2720 l_error_msg_code VARCHAR2(30);
2721 l_msg_index_out NUMBER;
2722 l_return_status VARCHAR2(2000);
2723 l_debug_mode VARCHAR2(30);
2724 --End of variables used for debugging
2725
2726 -- Start of Variable used for comparisons and calling ADD PLAN TXN API
2727 l_structure_version_id PA_PROJ_ELEMENT_VERSIONS.PARENT_STRUCTURE_VERSION_ID%TYPE;
2728 l_bl_already_exists VARCHAR2(1) := 'N';
2729 l_rec_already_exists VARCHAR2(1) := 'N';
2730 l_index NUMBER := 1;
2731
2732 l_Cbs_Enabled VARCHAR2(1) := 'N'; -- bug#16481402
2733 BEGIN
2734
2735 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2736 l_debug_mode := NVL(l_debug_mode, 'Y');
2737 x_msg_count := 0;
2738 x_return_status := FND_API.G_RET_STS_SUCCESS;
2739 PA_DEBUG.Set_Curr_Function( p_function => 'PAFPPEUB.add_new_resource_assignments',
2740 p_debug_mode => l_debug_mode );
2741
2742 /*CBS enhancement to validate Planning Options of the budget bug#16600305*/
2743 PA_ALTERNATE_TASK_PVT.Is_Planning_Options_Valid(
2744 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
2745 X_RETURN_STATUS => X_RETURN_STATUS,
2746 X_MSG_COUNT => X_MSG_COUNT,
2747 X_MSG_DATA => X_MSG_DATA);
2748 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2749 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2750 p_msg_name => x_msg_data);
2751 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2752 END IF;
2753
2754 IF l_debug_mode = 'Y' THEN
2755 pa_debug.g_err_stage:='Adding minimum Validations Here - COUNT of tables to be Same';
2756 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2757 END IF;
2758 IF (p_task_elem_version_id_tbl.COUNT <> p_resource_list_member_id_tbl.COUNT OR
2759 p_resource_list_member_id_tbl.COUNT <> p_currency_code_tbl.COUNT) THEN
2760
2761 IF l_debug_mode = 'Y' THEN
2762 pa_debug.g_err_stage:='p_task_elem_version_id_tbl.COUNT : '||p_task_elem_version_id_tbl.COUNT ;
2763 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2764
2765 pa_debug.g_err_stage:='p_resource_list_member_id_tbl.COUNT : '||p_resource_list_member_id_tbl.COUNT ;
2766 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2767
2768 pa_debug.g_err_stage:='p_currency_code_tbl.COUNT : '||p_currency_code_tbl.COUNT ;
2769 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2770 END IF;
2771 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2772 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2773 p_token1 => 'PROCEDURENAME',
2774 p_value1 => 'PAFPPTPB.add_new_resource_assignments',
2775 p_token2 => 'STAGE',
2776 p_value2 => 'I/P Table Counts are not Equal');
2777 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2778 END IF;
2779
2780 IF (p_task_elem_version_id_tbl.COUNT = 0) THEN
2781 IF l_debug_mode = 'Y' THEN
2782 pa_debug.g_err_stage:='elem_version_id table is empty - RETURNING ... ';
2783 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2784 END IF;
2785 pa_debug.reset_curr_function;
2786 RETURN;
2787 END IF;
2788
2789 IF l_debug_mode = 'Y' THEN
2790 pa_debug.g_err_stage:='Fetching PARENT_STRUCTURE_VERSION_ID';
2791 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2792 END IF;
2793 l_structure_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(p_project_id);
2794
2795 l_Cbs_Enabled := PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(p_Project_Id => p_project_id); -- bug#16481402
2796
2797 /*
2798 Scheme Used Below To derive Data to be passed to Add Planning TXN API.
2799 Let the Folowing I/P Data is passed to this API.
2800 1) t1 r1 c1
2801 2) t1 r1 c1
2802 3) t2 r2 c2
2803 4) t2 r2 c3
2804 5) t3 r3 c3
2805
2806 System State is such that Budget Lines Already Exists for
2807 1) t2 r2 c3
2808 2) t3 r3 c3
2809
2810 In this case add_planning_txn API should be called with the following data
2811 1)t1 r1 c1 and
2812 3)t2 r2 c2
2813 Basically records 2)4) and 5) have to be skipped.
2814
2815 For Dev reference -
2816 By I/P Set of tables - parameters passed to add_new_resource_assignments
2817 are referred.
2818 By O/P Set of tables - parameters passed to add_planning_transaction API
2819 are referred.
2820
2821 For Each Element Passed in the I/P Set of tables
2822 Check If Budget line exists for task/rlm/currency combination
2823 If Budget line already exists then jump to next element of I/P tables
2824 and skip the current I/P Record.
2825 ElsIf Budget line does not exist then
2826 Check if the if a record already exists in the O/P Set of Tables
2827 for task/rlm/currency I/P combination.
2828 If record does not exists then populate the O/P set of tables
2829 Else if a record already exists then skip the record for
2830 for task/rlm/currency I/P combination.
2831 */
2832
2833 IF p_task_elem_version_id_tbl.COUNT > 0 THEN
2834 FOR i IN p_task_elem_version_id_tbl.FIRST .. p_task_elem_version_id_tbl.LAST LOOP
2835 IF l_debug_mode = 'Y' THEN
2836 pa_debug.g_err_stage:='Loop 1 : p_task_elem_version_id_tbl('||i||') - '||p_task_elem_version_id_tbl(i);
2837 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2838 END IF;
2839
2840 -- Resetting flags Used
2841 l_bl_already_exists := 'N';
2842 l_rec_already_exists := 'N';
2843
2844 IF l_debug_mode = 'Y' THEN
2845 pa_debug.g_err_stage:='Checking if budget lines exist or not';
2846 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2847 END IF;
2848
2849 -- For a Project level record p_task_elem_version_id_tbl is passed as 0
2850 IF p_task_elem_version_id_tbl(i) <> 0 THEN -- For Task level record
2851 BEGIN
2852 SELECT 'Y'
2853 INTO l_bl_already_exists
2854 FROM DUAL
2855 WHERE EXISTS ( SELECT 1
2856 FROM PA_BUDGET_LINES PBL,PA_RESOURCE_ASSIGNMENTS PRA,PA_PROJ_ELEMENT_VERSIONS PEV
2857 WHERE PRA.PROJECT_ID = p_project_id
2858 AND PRA.BUDGET_VERSION_ID = p_budget_version_id
2859 AND PRA.RESOURCE_LIST_MEMBER_ID = p_resource_list_member_id_tbl(i)
2860 AND PEV.PROJ_ELEMENT_ID = PRA.TASK_ID
2861 AND PEV.PARENT_STRUCTURE_VERSION_ID = l_structure_version_id
2862 AND PEV.ELEMENT_VERSION_ID = p_task_elem_version_id_tbl(i)
2863 AND PBL.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
2864 AND PBL.TXN_CURRENCY_CODE = p_currency_code_tbl(i)
2865 AND nvl(PRA.CBS_ELEMENT_ID,-1) = nvl(p_cbs_element_id_tbl(i),-1) --bug#16481402
2866 );
2867
2868 IF l_debug_mode = 'Y' THEN
2869 pa_debug.g_err_stage:='Budget Lines EXIST - l_bl_already_exists :'||l_bl_already_exists;
2870 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2871 END IF;
2872
2873 EXCEPTION
2874 WHEN NO_DATA_FOUND THEN
2875 l_bl_already_exists := 'N';
2876 IF l_debug_mode = 'Y' THEN
2877 pa_debug.g_err_stage:='Budget Lines DONT EXIST - l_bl_already_exists :'||l_bl_already_exists;
2878 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2879 END IF;
2880 END;
2881
2882 ELSE -- For Project level record
2883
2884 BEGIN
2885 -- SQL Repository Bug 4884718; SQL ID 14903213
2886 -- Fixed Merge Join Cartesian violation by commenting out
2887 -- PA_PROJ_ELEMENT_VERSIONS from the FROM clause of the
2888 -- query below. It seems to be a copy/past artifact, as it
2889 -- is not references anywhere in the WHERE clause.
2890
2891 SELECT 'Y'
2892 INTO l_bl_already_exists
2893 FROM DUAL
2894 WHERE EXISTS ( SELECT 1
2895 FROM PA_BUDGET_LINES PBL,PA_RESOURCE_ASSIGNMENTS PRA
2896 --,PA_PROJ_ELEMENT_VERSIONS PEV /* Bug 4884718; SQL ID 14903213 */
2897 WHERE PRA.PROJECT_ID = p_project_id
2898 AND PRA.BUDGET_VERSION_ID = p_budget_version_id
2899 AND PRA.RESOURCE_LIST_MEMBER_ID = p_resource_list_member_id_tbl(i)
2900 AND PRA.TASK_ID = 0
2901 AND PBL.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
2902 AND PBL.TXN_CURRENCY_CODE = p_currency_code_tbl(i) );
2903
2904 IF l_debug_mode = 'Y' THEN
2905 pa_debug.g_err_stage:='Budget Lines EXIST - l_bl_already_exists :'||l_bl_already_exists;
2906 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2907 END IF;
2908
2909 EXCEPTION
2910 WHEN NO_DATA_FOUND THEN
2911 l_bl_already_exists := 'N';
2912 IF l_debug_mode = 'Y' THEN
2913 pa_debug.g_err_stage:='Budget Lines DONT EXIST - l_bl_already_exists :'||l_bl_already_exists;
2914 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2915 END IF;
2916 END;
2917 END IF;
2918
2919 -- Note record will be skipped if the BL already exists
2920 -- If BL/RA does not exists for I/P params Loop through Output Tables
2921 -- By Output tables, Tables to be passed to Add API are referred.
2922 IF l_bl_already_exists = 'N' THEN
2923
2924 IF l_debug_mode = 'Y' THEN
2925 pa_debug.g_err_stage:='BL Does Not Exist';
2926 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2927 END IF;
2928
2929 -- If Output Table is not Empty
2930 IF l_task_elem_version_id_tbl.COUNT > 0 THEN
2931 -- Loop Though Output Table
2932 FOR k IN l_task_elem_version_id_tbl.FIRST .. l_task_elem_version_id_tbl.LAST LOOP
2933 IF l_debug_mode = 'Y' THEN
2934 pa_debug.g_err_stage:='Loop 3 : l_task_elem_version_id_tbl('||k||') - '||l_task_elem_version_id_tbl(k);
2935 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2936 END IF;
2937 -- Compare if I/P Params have alreayd been added to output table or not.
2938 IF ((p_task_elem_version_id_tbl(i) = l_task_elem_version_id_tbl(k)) AND
2939 (p_resource_list_member_id_tbl(i) = l_resource_list_member_id_tbl(k)) AND
2940 (p_currency_code_tbl(i) = l_currency_code_tbl(k))) THEN
2941 -- If Already Added Set l_rec_already_exists to Y
2942 IF l_Cbs_Enabled = 'Y' THEN --bug#16481402
2943 IF (p_cbs_element_id_tbl(i) = l_cbs_element_id_tbl(k)) THEN
2944 IF l_debug_mode = 'Y' THEN
2945 pa_debug.g_err_stage:='Output Rec Exists';
2946 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2947 END IF;
2948 l_rec_already_exists := 'Y';
2949 ELSE
2950 l_rec_already_exists := 'N';
2951 END IF;
2952 ELSE
2953 IF l_debug_mode = 'Y' THEN
2954 pa_debug.g_err_stage:='Output Rec Exists';
2955 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2956 END IF;
2957 l_rec_already_exists := 'Y';
2958
2959 EXIT;
2960 END IF;
2961 ELSE
2962 --Set l_rec_already_exists to N
2963 l_rec_already_exists := 'N';
2964 END IF;
2965 END LOOP;
2966 ELSE
2967 -- If Output Table is Empty Set l_rec_already_exists to N
2968 l_rec_already_exists := 'N';
2969 END IF;
2970
2971 -- If Output Table does not have the I/P Rec add I/P Rec to Output Tables
2972 IF l_rec_already_exists = 'N' THEN
2973 IF l_debug_mode = 'Y' THEN
2974 pa_debug.g_err_stage:='Add to Output Rec';
2975 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
2976 END IF;
2977
2978 l_task_elem_version_id_tbl.extend(1);
2979 l_resource_list_member_id_tbl.extend(1);
2980 l_cbs_element_id_tbl.extend(1); --bug#16481402
2981 l_quantity_tbl.extend(1);
2982 l_currency_code_tbl.extend(1);
2983 l_raw_cost_tbl.extend(1);
2984 l_burdened_cost_tbl.extend(1);
2985 l_revenue_tbl.extend(1);
2986 l_cost_rate_tbl.extend(1);
2987 l_bill_rate_tbl.extend(1);
2988 l_expenditure_type_tbl.extend(1);
2989
2990 l_burdened_rate_tbl.extend(1);
2991 l_unplanned_flag_tbl.extend(1);
2992
2993 l_task_elem_version_id_tbl(l_index) := p_task_elem_version_id_tbl(i);
2994 l_resource_list_member_id_tbl(l_index) := p_resource_list_member_id_tbl(i);
2995 l_cbs_element_id_tbl(l_index) := p_cbs_element_id_tbl(i); --bug#16481402
2996 l_currency_code_tbl(l_index) := p_currency_code_tbl(i);
2997 IF p_quantity_tbl.EXISTS(i) THEN
2998 l_quantity_tbl(l_index) := p_quantity_tbl(i);
2999 END IF;
3000 IF p_raw_cost_tbl.EXISTS(i) THEN
3001 l_raw_cost_tbl(l_index) := p_raw_cost_tbl(i);
3002 END IF;
3003 IF p_burdened_cost_tbl.EXISTS(i) THEN
3004 l_burdened_cost_tbl(l_index) := p_burdened_cost_tbl(i);
3005 END IF;
3006 IF p_revenue_tbl.EXISTS(i) THEN
3007 l_revenue_tbl(l_index) := p_revenue_tbl(i);
3008 END IF;
3009 IF p_cost_rate_tbl.EXISTS(i) THEN
3010 l_cost_rate_tbl(l_index) := p_cost_rate_tbl(i);
3011 END IF;
3012 IF p_bill_rate_tbl.EXISTS(i) THEN
3013 l_bill_rate_tbl(l_index) := p_bill_rate_tbl(i);
3014 END IF;
3015 IF p_burdened_rate_tbl.EXISTS(i) THEN
3016 l_burdened_rate_tbl(l_index) := p_burdened_rate_tbl(i);
3017 END IF;
3018 IF p_unplanned_flag_tbl.EXISTS(i) THEN
3019 l_unplanned_flag_tbl(l_index) := p_unplanned_flag_tbl(i);
3020 END IF;
3021 IF p_expenditure_type_tbl.EXISTS(i) THEN
3022 l_expenditure_type_tbl(l_index) := p_expenditure_type_tbl(i); --added for Enc
3023 END IF;
3024 l_index := l_index +1;
3025 END IF;
3026
3027 ELSE -- i.e. l_bl_already_exists = 'Y'
3028 IF l_debug_mode = 'Y' THEN
3029 pa_debug.g_err_stage:='BL Exists - Skipping Rec p_task_elem_version_id_tbl : '||p_task_elem_version_id_tbl(i);
3030 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
3031
3032 pa_debug.g_err_stage:='BL Exists - Skipping Rec p_resource_list_member_id_tbl : '||p_resource_list_member_id_tbl(i);
3033 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
3034
3035 pa_debug.g_err_stage:='BL Exists - Skipping Rec p_currency_code_tbl : '||p_currency_code_tbl(i);
3036 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
3037 END IF;
3038 END IF;
3039
3040 END LOOP;
3041
3042 ELSE
3043 -- If Empty Tables are passed to Wrapper API. Simply Return
3044 IF l_debug_mode = 'Y' THEN
3045 pa_debug.g_err_stage:='elem_version_id table is empty - RETURNING ... '||p_context;
3046 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
3047 END IF;
3048 pa_debug.reset_curr_function;
3049 RETURN;
3050 END IF;
3051
3052 IF l_debug_mode = 'Y' THEN
3053 pa_debug.g_err_stage:='Calling Add Planning TXN API';
3054 pa_debug.write('PAFPPEUB.add_new_resource_assignments',pa_debug.g_err_stage,3);
3055 END IF;
3056
3057 pa_fp_planning_transaction_pub.add_planning_transactions
3058 (p_context => p_context,
3059 p_one_to_one_mapping_flag => 'Y',
3060 p_skip_duplicates_flag => 'Y',
3061 p_project_id => p_project_id,
3062 p_budget_version_id => p_budget_version_id,
3063 p_task_elem_version_id_tbl => l_task_elem_version_id_tbl,
3064 p_resource_list_member_id_tbl => l_resource_list_member_id_tbl,
3065 p_cbs_element_id_tbl => l_cbs_element_id_tbl,--bug#16481402
3066 p_quantity_tbl => l_quantity_tbl,
3067 p_currency_code_tbl => l_currency_code_tbl,
3068 p_raw_cost_tbl => l_raw_cost_tbl,
3069 p_burdened_cost_tbl => l_burdened_cost_tbl,
3070 p_revenue_tbl => l_revenue_tbl,
3071 p_cost_rate_tbl => l_cost_rate_tbl,
3072 p_bill_rate_tbl => l_bill_rate_tbl,
3073 p_burdened_rate_tbl => l_burdened_rate_tbl,
3074 p_unplanned_flag_tbl => l_unplanned_flag_tbl,
3075 p_expenditure_type_tbl => l_expenditure_type_tbl, --for Enc
3076 x_return_status => l_return_status,
3077 x_msg_count => l_msg_count,
3078 x_msg_data => l_msg_data);
3079
3080 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3081 IF l_debug_mode = 'Y' THEN
3082 pa_debug.g_err_stage:='ADD PLAN TXN Returned Error';
3083 pa_debug.write('PA_PLANNING_ELEMENT_UTILS.add_new_resource_assignments',pa_debug.g_err_stage,3);
3084 END IF;
3085
3086 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3087 END IF;
3088
3089 pa_debug.reset_curr_function;
3090
3091 EXCEPTION
3092 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3093 l_msg_count := FND_MSG_PUB.count_msg;
3094 IF l_msg_count = 1 THEN
3095 PA_INTERFACE_UTILS_PUB.get_messages
3096 (p_encoded => FND_API.G_TRUE
3097 ,p_msg_index => 1
3098 ,p_msg_count => l_msg_count
3099 ,p_msg_data => l_msg_data
3100 ,p_data => l_data
3101 ,p_msg_index_out => l_msg_index_out);
3102 x_msg_data := l_data;
3103 x_msg_count := l_msg_count;
3104 ELSE
3105 x_msg_count := l_msg_count;
3106 END IF;
3107 x_return_status := FND_API.G_RET_STS_ERROR;
3108 pa_debug.reset_curr_function;
3109
3110 WHEN OTHERS THEN
3111
3112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3113 x_msg_count := 1;
3114 x_msg_data := SQLERRM;
3115 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PLANNING_ELEMENT_UTILS'
3116 ,p_procedure_name => 'add_new_resource_assignments');
3117
3118 IF l_debug_mode = 'Y' THEN
3119 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3120 pa_debug.write('add_new_resource_assignments',pa_debug.g_err_stage,5);
3121 END IF;
3122 pa_debug.reset_curr_function;
3123 RAISE;
3124
3125 END add_new_resource_assignments;
3126
3127
3128 /* This procedure is used to retrieve:
3129 FND_API.G_MISS_NUM (x_num)
3130 FND_API.G_MISS_CHAR (x_char)
3131 FND_API.G_MISS_DATE (x_date)
3132 so it can be passed to the Java-side for further use
3133 */
3134 PROCEDURE get_fnd_miss_constants
3135 (x_num OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3136 x_char OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3137 x_date OUT NOCOPY DATE) IS --File.Sql.39 bug 4440895
3138 BEGIN
3139 x_num:=FND_API.G_MISS_NUM;
3140 x_char:=FND_API.G_MISS_CHAR;
3141 x_date:=FND_API.G_MISS_DATE;
3142 END get_fnd_miss_constants;
3143
3144 /* REVISION HISTORY
3145 * Created: 07/20/2004 by DLAI for bug 3747582
3146 */
3147 FUNCTION get_bv_name_from_id
3148 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) return VARCHAR2 is
3149 l_return_value pa_budget_versions.version_name%TYPE;
3150 BEGIN
3151 select version_name
3152 into l_return_value
3153 from pa_budget_versions
3154 where budget_version_id = p_budget_version_id;
3155 return l_return_value;
3156 EXCEPTION
3157 when NO_DATA_FOUND then
3158 return null;
3159 when others then
3160 return null;
3161 END get_bv_name_from_id;
3162
3163 --Created for bug 3546208. This function will return the financial structure version id for the project
3164 --id passed.
3165 FUNCTION get_fin_struct_id(p_project_id pa_projects_all.project_id%TYPE,
3166 p_budget_version_id pa_budget_versions.budget_Version_id%TYPE)
3167 RETURN NUMBER
3168 IS
3169 BEGIN
3170 IF (l_edit_plan_project_id IS NULL OR
3171 l_edit_plan_struct_id IS NULL OR
3172 l_edit_plan_bv_id IS NULL) OR
3173 (l_edit_plan_project_id <> NVL(p_project_id,-99) OR
3174 l_edit_plan_bv_id <> NVL(p_budget_version_id,-99)) THEN
3175
3176 SELECT DECODE(wp_version_flag,
3177 'Y',project_structure_version_id,
3178 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(project_id))
3179 INTO l_edit_plan_struct_id
3180 FROM pa_budget_versions
3181 WHERE budget_Version_id=p_budget_version_id;
3182
3183 l_edit_plan_project_id:= p_project_id;
3184 l_edit_plan_bv_id := p_budget_version_id;
3185
3186 END IF;
3187 RETURN l_edit_plan_struct_id;
3188 END get_fin_struct_id;
3189
3190
3191 -- This function returns the wbs element name, either from the wbs_element_version_id
3192 -- or from the proj_element_id. If using proj_element_id, then p_use_element_version_id_flag
3193 -- must be set to 'N'
3194 FUNCTION get_wbs_element_name_from_id
3195 (p_project_id IN pa_projects_all.project_id%TYPE,
3196 p_wbs_element_version_id IN pa_resource_assignments.wbs_element_version_id%TYPE,
3197 p_wbs_project_element_id IN pa_proj_element_versions.proj_element_id%TYPE,
3198 p_use_element_version_flag IN VARCHAR2)
3199 return VARCHAR2
3200 IS
3201 l_return_value pa_proj_elements.name%TYPE;
3202 BEGIN
3203 select name
3204 into l_return_value
3205 from pa_projects_all
3206 where project_id = p_project_id;
3207 -- if wbs_element_version_id is 0 or -1, then it is a project-level row
3208 if p_wbs_element_version_id = 0 or p_wbs_element_version_id = -1 then
3209 return l_return_value;
3210 else
3211 if p_use_element_version_flag = 'N' then
3212 -- using proj_element_id
3213 select pe.name
3214 into l_return_value
3215 from pa_proj_elements pe
3216 where pe.proj_element_id = p_wbs_project_element_id;
3217 return l_return_value;
3218 else
3219 -- using wbs_element_version_id
3220 select pe.name
3221 into l_return_value
3222 from pa_proj_element_versions pev,
3223 pa_proj_elements pe
3224 where pev.element_version_id = p_wbs_element_version_id and
3225 pev.proj_element_id = pe.proj_element_id;
3226 return l_return_value;
3227 end if; -- use wbs_element_version_id
3228 end if;
3229 EXCEPTION
3230 when NO_DATA_FOUND then
3231 return null;
3232 when others then
3233 return null;
3234 END get_wbs_element_name_from_id;
3235
3236
3237 FUNCTION get_proj_element_id
3238 (p_wbs_element_version_id IN pa_proj_element_versions.element_version_id%TYPE)
3239 return NUMBER
3240 IS
3241 l_return_value pa_proj_element_versions.proj_element_id%TYPE;
3242 BEGIN
3243 select proj_element_id
3244 into l_return_value
3245 from pa_proj_element_versions
3246 where element_version_id = p_wbs_element_version_id;
3247 return l_return_value;
3248 EXCEPTION
3249 when NO_DATA_FOUND then
3250 return null;
3251 when others then
3252 return null;
3253 END get_proj_element_id;
3254
3255 FUNCTION get_rbs_element_name_from_id
3256 (p_rbs_element_version_id IN pa_rbs_elements.rbs_element_id%TYPE)
3257 return VARCHAR2
3258 IS
3259 l_return_value pa_rbs_element_names_vl.resource_name%TYPE;
3260 BEGIN
3261 select names.resource_name
3262 into l_return_value
3263 from pa_rbs_elements ele,
3264 pa_rbs_element_names_vl names
3265 where ele.rbs_element_id = p_rbs_element_version_id and
3266 ele.rbs_element_name_id = names.rbs_element_name_id;
3267 return l_return_value;
3268 EXCEPTION
3269 when NO_DATA_FOUND then
3270 return null;
3271 when others then
3272 return null;
3273 END get_rbs_element_name_from_id;
3274
3275
3276 FUNCTION get_task_percent_complete
3277 (p_project_id IN pa_projects_all.project_id%TYPE,
3278 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3279 p_proj_element_id IN pa_proj_element_versions.proj_element_id%TYPE,
3280 p_calling_context IN VARCHAR2) return NUMBER
3281 is
3282 l_return_value NUMBER;
3283 l_structure_type VARCHAR2(30) := 'FINANCIAL'; -- could also be 'WORKPLAN'
3284 l_object_type VARCHAR2(30) := 'PA_TASKS';
3285 l_structure_status_flag VARCHAR2(1) := null;
3286 l_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE;
3287 l_structure_status VARCHAR2(30) := null;
3288 l_base_percent_complete NUMBER := null;
3289 l_return_status VARCHAR2(1);
3290 l_msg_count NUMBER;
3291 l_msg_data VARCHAR2(2000);
3292 BEGIN
3293 --hr_utility.trace_on(null, 'dlai');
3294 --hr_utility.trace('ENTERING GET PERCENT COMPLETE API');
3295 l_return_value := null;
3296 l_structure_version_id :=
3297 pa_planning_element_utils.get_fin_struct_id(p_project_id,p_budget_version_id);
3298 l_structure_status_flag :=
3299 PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(
3300 p_project_id,
3301 l_structure_version_id);
3302 if l_structure_status_flag = 'Y' then
3303 l_structure_status := 'PUBLISHED';
3304 else
3305 l_structure_status := 'WORKING';
3306 end if;
3307 /*
3308 hr_utility.trace('p_project_id is ' || to_char(p_project_id));
3309 hr_utility.trace('p_proj_element_id is ' || to_char(p_proj_element_id));
3310 hr_utility.trace('p_structure_type is ' || l_structure_type);
3311 hr_utility.trace('p_object_type is ' || l_object_type);
3312 hr_utility.trace('p_as_of_date is ' || to_char(trunc(SYSDATE)));
3313 hr_utility.trace('p_structure_version_id is ' || to_char(l_structure_version_id));
3314 hr_utility.trace('p_structure_status is ' || l_structure_status);
3315 hr_utility.trace('p_calling_context is ' || p_calling_context);
3316 hr_utility.trace('x_base_percent_complete is ' || to_char(l_base_percent_complete));
3317 hr_utility.trace('x_return_status is ' || l_return_status);
3318 hr_utility.trace('x_msg_count is ' || to_char(l_msg_count));
3319 hr_utility.trace('x_msg_data is ' || l_msg_data);
3320 */
3321 PA_PROGRESS_UTILS.REDEFAULT_BASE_PC
3322 (p_project_id => p_project_id,
3323 p_proj_element_id => p_proj_element_id,
3324 p_structure_type => l_structure_type,
3325 p_object_type => l_object_type,
3326 p_as_of_date => trunc(SYSDATE),
3327 p_structure_version_id => l_structure_version_id,
3328 p_structure_status => l_structure_status,
3329 p_calling_context => p_calling_context,
3330 x_base_percent_complete => l_base_percent_complete,
3331 x_return_status => l_return_status,
3332 x_msg_count => l_msg_count,
3333 x_msg_data => l_msg_data);
3334 if l_return_status = 'S' then
3335 l_return_value := l_base_percent_complete;
3336 end if;
3337 return l_return_value;
3338 EXCEPTION
3339 when NO_DATA_FOUND then
3340 return null;
3341 when others then
3342 return null;
3343 END get_task_percent_complete;
3344
3345 /* Bug 5524803: Added the below API to return the prior forecast version id
3346 to be used by PJI team.
3347 This procedure returns a different value of 'x_prior_fcst_version_id' compared
3348 to the get_finplan_bvids. This procedure has been specifically created for
3349 PJI team.
3350
3351 If p_budget_version_id is a BUDGET version:
3352 x_prior_fcst_version_id = Version previous to the current baselined version
3353 of PRIMARY FORECAST plan type
3354 If p_budget_version is a FORECAST version:
3355 x_prior_fcst_version_id = Version previous to the current baselined version
3356 of same plan type
3357 */
3358 FUNCTION get_prior_forecast_version_id
3359 (p_plan_version_id IN pa_budget_versions.budget_version_id%TYPE,
3360 p_project_id IN pa_projects_all.project_id%TYPE
3361 ) return NUMBER
3362 is
3363 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
3364 l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3365 l_curr_fcst_ver_id pa_budget_versions.budget_version_id%TYPE := NULL;
3366 l_curr_fcst_ver_num pa_budget_versions.version_number%TYPE := NULL;
3367 l_fp_type_id pa_budget_versions.fin_plan_type_id%TYPE := NULL;
3368 l_version_type pa_budget_versions.version_type%TYPE := NULL;
3369 x_prior_fcst_version_id pa_budget_versions.budget_version_id%TYPE := NULL;
3370
3371
3372 BEGIN
3373 select pt.plan_class_code,
3374 decode(bv.version_type,'COST','COST_ONLY','REVENUE','REVENUE_ONLY',
3375 'ALL','COST_AND_REV_SAME')
3376 into l_plan_class_code,
3377 l_fin_plan_pref_code
3378 from pa_budget_versions bv,
3379 pa_fin_plan_types_b pt
3380 where bv.budget_version_id = p_plan_version_id and
3381 bv.fin_plan_type_id = pt.fin_plan_type_id;
3382
3383 if l_plan_class_code = 'BUDGET' then
3384 -- CURRENT PLAN VERSION IS BUDGET PLAN CLASS
3385 -- RETRIEVE PRIMARY FORECAST BASELINED VERSION (IF IT EXISTS)
3386 if l_fin_plan_pref_code = 'COST_ONLY' then
3387 -- looking for PRIMARY COST FORECAST plan type
3388 begin
3389 select bv.budget_version_id,
3390 bv.version_number
3391 into l_curr_fcst_ver_id,
3392 l_curr_fcst_ver_num
3393 from pa_budget_versions bv
3394 where bv.project_id = p_project_id and
3395 bv.primary_cost_forecast_flag = 'Y' and
3396 bv.current_flag = 'Y';
3397
3398 select bv1.budget_version_id
3399 into x_prior_fcst_version_id
3400 from pa_budget_versions bv1
3401 where bv1.project_id = p_project_id
3402 and bv1.primary_cost_forecast_flag = 'Y'
3403 and bv1.budget_status_code = 'B'
3404 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3405 commented and added below for bug 6870324 */
3406 and bv1.version_number = (select max(bv2.version_number)
3407 from pa_budget_versions bv2
3408 where bv2.project_id = p_project_id
3409 and bv2.primary_cost_forecast_flag = 'Y'
3410 and bv2.budget_status_code = 'B'
3411 and bv2.version_number < l_curr_fcst_ver_num
3412 );
3413
3414
3415 exception
3416 when NO_DATA_FOUND then
3417 x_prior_fcst_version_id := -1;
3418 end;
3419 elsif l_fin_plan_pref_code = 'REVENUE_ONLY' then
3420 -- looking for PRIMARY REVENUE FORECAST plan type
3421 begin
3422 select bv.budget_version_id,
3423 bv.version_number
3424 into l_curr_fcst_ver_id,
3425 l_curr_fcst_ver_num
3426 from pa_budget_versions bv
3427 where bv.project_id = p_project_id and
3428 bv.primary_rev_forecast_flag = 'Y' and
3429 bv.current_flag = 'Y';
3430
3431 select bv1.budget_version_id
3432 into x_prior_fcst_version_id
3433 from pa_budget_versions bv1
3434 where bv1.project_id = p_project_id
3435 and bv1.primary_rev_forecast_flag = 'Y'
3436 and bv1.budget_status_code = 'B'
3437 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3438 commented and added below for bug 6870324 */
3439 and bv1.version_number = (select max(bv2.version_number)
3440 from pa_budget_versions bv2
3441 where bv2.project_id = p_project_id
3442 and bv2.primary_rev_forecast_flag = 'Y'
3443 and bv2.budget_status_code = 'B'
3444 and bv2.version_number < l_curr_fcst_ver_num
3445 );
3446
3447 exception
3448 when NO_DATA_FOUND then
3449 x_prior_fcst_version_id := -1;
3450 end;
3451 elsif l_fin_plan_pref_code = 'COST_AND_REV_SAME' then
3452 -- looking for PRIMARY 'ALL' FORECAST plan type
3453 begin
3454 select bv.budget_version_id,
3455 bv.version_number
3456 into l_curr_fcst_ver_id,
3457 l_curr_fcst_ver_num
3458 from pa_budget_versions bv
3459 where bv.project_id = p_project_id and
3460 bv.primary_rev_forecast_flag = 'Y' and
3461 bv.primary_cost_forecast_flag = 'Y' and
3462 bv.current_flag = 'Y';
3463 select bv1.budget_version_id
3464 into x_prior_fcst_version_id
3465 from pa_budget_versions bv1
3466 where bv1.project_id = p_project_id
3467 and bv1.primary_rev_forecast_flag = 'Y'
3468 and bv1.primary_cost_forecast_flag = 'Y'
3469 and bv1.budget_status_code = 'B'
3470 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3471 commented and added below for bug 6870324 */
3472 and bv1.version_number = (select max(bv2.version_number)
3473 from pa_budget_versions bv2
3474 where bv2.project_id = p_project_id
3475 and bv2.primary_rev_forecast_flag = 'Y'
3476 and bv2.primary_cost_forecast_flag = 'Y'
3477 and bv2.budget_status_code = 'B'
3478 and bv2.version_number < l_curr_fcst_ver_num
3479 );
3480
3481 exception
3482 when NO_DATA_FOUND then
3483
3484 begin
3485 select bv.budget_version_id,
3486 bv.version_number
3487 into l_curr_fcst_ver_id,
3488 l_curr_fcst_ver_num
3489 from pa_budget_versions bv
3490 where bv.project_id = p_project_id and
3491 bv.primary_cost_forecast_flag = 'Y' and
3492 bv.current_flag = 'Y';
3493
3494 select bv1.budget_version_id
3495 into x_prior_fcst_version_id
3496 from pa_budget_versions bv1
3497 where bv1.project_id = p_project_id
3498 and bv1.primary_cost_forecast_flag = 'Y'
3499 and bv1.budget_status_code = 'B'
3500 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3501 commented and added below for bug 6870324 */
3502 and bv1.version_number = (select max(bv2.version_number)
3503 from pa_budget_versions bv2
3504 where bv2.project_id = p_project_id
3505 and bv2.primary_cost_forecast_flag = 'Y'
3506 and bv2.budget_status_code = 'B'
3507 and bv2.version_number < l_curr_fcst_ver_num
3508 );
3509
3510 exception
3511 when NO_DATA_FOUND then
3512
3513 begin
3514 select bv.budget_version_id,
3515 bv.version_number
3516 into l_curr_fcst_ver_id,
3517 l_curr_fcst_ver_num
3518 from pa_budget_versions bv
3519 where bv.project_id = p_project_id and
3520 bv.primary_rev_forecast_flag = 'Y' and
3521 bv.current_flag = 'Y';
3522
3523 select bv1.budget_version_id
3524 into x_prior_fcst_version_id
3525 from pa_budget_versions bv1
3526 where bv1.project_id = p_project_id
3527 and bv1.primary_rev_forecast_flag = 'Y'
3528 and bv1.budget_status_code = 'B'
3529 /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3530 commented and added below for bug 6870324 */
3531 and bv1.version_number = (select max(bv2.version_number)
3532 from pa_budget_versions bv2
3533 where bv2.project_id = p_project_id
3534 and bv2.primary_rev_forecast_flag = 'Y'
3535 and bv2.budget_status_code = 'B'
3536 and bv2.version_number < l_curr_fcst_ver_num
3537 );
3538
3539 exception
3540 when NO_DATA_FOUND then
3541 x_prior_fcst_version_id := -1;
3542 end;
3543 end;
3544 end;
3545
3546 end if; -- l_fin_plan_pref_code
3547
3548 else
3549 -- CURRENT PLAN VERSION IS FORECAST PLAN CLASS
3550 begin
3551 select bv2.budget_version_id,
3552 bv2.version_number,
3553 bv2.fin_plan_type_id,
3554 bv2.version_type
3555 into l_curr_fcst_ver_id,
3556 l_curr_fcst_ver_num,
3557 l_fp_type_id,
3558 l_version_type
3559 from pa_budget_versions bv1,
3560 pa_budget_versions bv2
3561 where bv1.project_id = p_project_id and
3562 bv1.budget_version_id = p_plan_version_id and
3563 bv1.project_id = bv2.project_id and
3564 bv1.fin_plan_type_id = bv2.fin_plan_type_id and
3565 bv1.version_type = bv2.version_type and
3566 bv2.current_flag = 'Y';
3567
3568 select budget_version_id
3569 into x_prior_fcst_version_id
3570 from pa_budget_versions
3571 where project_id = p_project_id and
3572 fin_plan_type_id = l_fp_type_id and
3573 version_type = l_version_type and
3574 budget_status_code = 'B' and
3575 /* version_number = l_curr_fcst_ver_num - 1;
3576 commented and added below for bug 6870324 */
3577 version_number = (select max(bv1.version_number)
3578 from pa_budget_versions bv1
3579 where bv1.project_id = p_project_id
3580 and bv1.fin_plan_type_id = l_fp_type_id
3581 and bv1.version_type = l_version_type
3582 and bv1.budget_status_code = 'B'
3583 and bv1.version_number < l_curr_fcst_ver_num
3584 );
3585
3586
3587 exception
3588 when NO_DATA_FOUND then
3589 x_prior_fcst_version_id := -1;
3590 end;
3591
3592 end if; -- l_plan_class_code
3593 RETURN x_prior_fcst_version_id;
3594 EXCEPTION
3595 when no_data_found then
3596 RETURN NULL;
3597 END get_prior_forecast_version_id;
3598
3599 END pa_planning_element_utils;