[Home] [Help]
PACKAGE BODY: APPS.PA_FP_VIEW_PLANS_TXN_PUB
Source
1 PACKAGE BODY pa_fp_view_plans_txn_pub as
2 /* $Header: PAFPVPNB.pls 120.1 2005/08/19 16:31:28 mwasowic noship $
3 Start of Comments
4 Package name : pa_fp_view_plans_txn_pub
5 Purpose : API's for Financial Planning: View Plans Non-Hgrid Page
6 History :
7 NOTE :
8 End of Comments
9 */
10
11 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
12 g_plsql_max_array_size NUMBER := 200;
13
14 function Get_Multicurrency_Flag return VARCHAR2 is
15 BEGIN
16 return pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG;
17 END Get_Multicurrency_Flag;
18
19 function Get_Plan_Type_Id return NUMBER is
20 BEGIN
21 return pa_fp_view_plans_txn_pub.G_PLAN_TYPE_ID;
22 END Get_Plan_Type_Id;
23
24 function Get_Cost_Version_Id return NUMBER is
25 BEGIN
26 return pa_fp_view_plans_txn_pub.G_COST_VERSION_ID;
27 END Get_Cost_Version_Id;
28
29 function Get_Rev_Version_Id return NUMBER is
30 BEGIN
31 return pa_fp_view_plans_txn_pub.G_REV_VERSION_ID;
32 END Get_Rev_Version_Id;
33
34 function Get_Single_Version_Id return NUMBER is
35 BEGIN
36 return pa_fp_view_plans_txn_pub.G_SINGLE_VERSION_ID;
37 END Get_Single_Version_Id;
38
39 function Get_Cost_Resource_List_Id return NUMBER is
40 BEGIN
41 return pa_fp_view_plans_txn_pub.G_COST_RESOURCE_LIST_ID;
42 END Get_Cost_Resource_List_Id;
43
44 function Get_Revenue_Resource_List_Id return NUMBER is
45 BEGIN
46 return pa_fp_view_plans_txn_pub.G_REVENUE_RESOURCE_LIST_ID;
47 END Get_Revenue_Resource_List_Id;
48
49 function Get_Report_Labor_Hrs_From_Code return VARCHAR2 is
50 BEGIN
51 return pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE;
52 END Get_Report_Labor_Hrs_From_Code;
53
54 function Get_Derive_Margin_From_Code return VARCHAR2 is
55 BEGIN
56 return pa_fp_view_plans_txn_pub.G_DERIVE_MARGIN_FROM_CODE;
57 END Get_Derive_Margin_From_Code;
58
59 function Get_Display_From return VARCHAR2 is
60 BEGIN
61 return pa_fp_view_plans_txn_pub.G_DISPLAY_FROM;
62 END;
63
64 function Get_Cost_Version_Grouping return VARCHAR2 is
65 BEGIN
66 return pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING;
67 END;
68
69 function Get_Rev_Version_Grouping return VARCHAR2 is
70 BEGIN
71 return pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING;
72 END;
73
74 function Get_Cost_RV_Num return NUMBER is
75 BEGIN
76 return pa_fp_view_plans_txn_pub.G_COST_RECORD_VERSION_NUM;
77 END;
78
79 function Get_Rev_RV_Num return NUMBER is
80 BEGIN
81 return pa_fp_view_plans_txn_pub.G_REV_RECORD_VERSION_NUM;
82 END;
83
84 --
85 -- BUG FIX 2615852: need to recognize if all txn currencies entered for a ra
86 --
87
88 function all_txn_currencies_entered
89 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE)
90 return VARCHAR2
91 is
92 l_return_value VARCHAR2(1);
93 l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
94 /* bug 3106741
95 pa_fp_txn_curr table doesn't have any index on plan_version_id.
96 But index is available on proj_fp_options_id.
97 So, pa_proj_fp_options_id is used to fetch options_id
98 */
99 cursor unentered_csr is
100 select txn_currency_code
101 from pa_fp_txn_currencies txncurr,
102 pa_proj_fp_options pfo
103 where pfo.fin_plan_version_id = l_budget_version_id and
104 txncurr.proj_fp_options_id = pfo.proj_fp_options_id and
105 not (txn_currency_code in
106 (select distinct txn_currency_code
107 from pa_budget_lines bl
108 where bl.resource_assignment_id = p_resource_assignment_id));
109 unentered_rec unentered_csr%ROWTYPE;
110
111 begin
112 l_return_value := 'N';
113 select budget_version_id
114 into l_budget_version_id
115 from pa_resource_assignments
116 where resource_assignment_id = p_resource_assignment_id;
117 open unentered_csr;
118 fetch unentered_csr into unentered_rec;
119 -- if the csr is empty, then there are no txn currencies for which a budget
120 -- line has not been created. thus, all txn currencies have been entered
121 if unentered_csr%NOTFOUND then
122 l_return_value := 'Y';
123 end if;
124 close unentered_csr;
125 return l_return_value;
126 EXCEPTION
127 WHEN NO_DATA_FOUND then
128 return l_return_value;
129 WHEN OTHERS then
130 return l_return_value;
131 end all_txn_currencies_entered;
132
133
134 /* ------------------------------------------------------------- */
135
136 function get_task_name
137 (p_task_id IN pa_tasks.task_id%TYPE) return VARCHAR2 is
138 l_task_name pa_tasks.task_name%TYPE;
139 BEGIN
140 select task_name
141 into l_task_name
142 from pa_tasks
143 where task_id = p_task_id;
144 return l_task_name;
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 l_task_name := 'none';
148 return(l_task_name);
149 WHEN OTHERS THEN
150 l_task_name := 'error';
151 return(l_task_name);
152 END;
153 /* ------------------------------------------------------------- */
154
155 function get_task_number
156 (p_task_id IN pa_tasks.task_id%TYPE) return VARCHAR2 is
157 l_task_number pa_tasks.task_number%TYPE;
158 BEGIN
159 select task_number
160 into l_task_number
161 from pa_tasks
162 where task_id = p_task_id;
163 return l_task_number;
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 l_task_number := 'none';
167 return(l_task_number);
168 WHEN OTHERS THEN
169 l_task_number := 'error';
170 return(l_task_number);
171 END;
172 /* ------------------------------------------------------------- */
173 function get_resource_name
174 (p_resource_id IN pa_resources.resource_id%TYPE) return VARCHAR2 is
175 l_resource_name pa_resources.name%TYPE;
176 BEGIN
177 select name
178 into l_resource_name
179 from pa_resources
180 where resource_id = p_resource_id;
181 return l_resource_name;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 l_resource_name := 'none';
185 return(l_resource_name);
186 WHEN OTHERS THEN
187 l_resource_name := 'error';
188 return(l_resource_name);
189 END;
190 /* ------------------------------------------------------------- */
191
192 /* ---------------------- CHANGE HISTORY ----------------------- */
193 -- 10/08/02: x_planned_resources_flag:
194 -- check pa_resource_lists.uncategorized_flag
195 -- 10/10/02: for x_display_from, query PLAN_TYPE planning options
196 -- 10/28/02: make sure resource list global variables are populated
197 -- for resource query to work
198 -- 10/31/02: queries for compl version should make sure ci_id is null
199 -- 11/08/02: x_project_currency = project or projfunc currency, depending if
200 -- plan type = AR
201 -- populate G_DISPLAY_CURRENCY_TYPE
202 -- 07/30/03: changed logic for populating x_planned_resources_flag and x_grouping_type
203 -- BUG 2813661
204 procedure nonhgrid_view_initialize
205 (p_project_id IN pa_budget_versions.project_id%TYPE,
206 p_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
207 p_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
208 p_user_id IN NUMBER,
209 -- x_budget_status_code OUT pa_budget_versions.budget_status_code%TYPE,
210 x_cost_budget_status_code OUT NOCOPY pa_budget_versions.budget_status_code%TYPE, --File.Sql.39 bug 4440895
211 x_rev_budget_status_code OUT NOCOPY pa_budget_versions.budget_status_code%TYPE, --File.Sql.39 bug 4440895
212 x_cost_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
213 x_rev_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
214 x_cost_rl_id OUT NOCOPY pa_budget_versions.resource_list_id%TYPE, --File.Sql.39 bug 4440895
215 x_rev_rl_id OUT NOCOPY pa_budget_versions.resource_list_id%TYPE, --File.Sql.39 bug 4440895
216 -- x_cost_locked_id OUT pa_budget_versions.locked_by_person_id%TYPE,
217 -- x_rev_locked_id OUT pa_budget_versions.locked_by_person_id%TYPE,
218 x_display_from OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
219 x_planned_resources_flag OUT NOCOPY VARCHAR2, -- valid values: 'Y', 'N' --File.Sql.39 bug 4440895
220 x_grouping_type OUT NOCOPY VARCHAR2, -- valid values: 'GROUPED', 'NONGROUPED', 'MIXED' --File.Sql.39 bug 4440895
221 x_planning_level OUT NOCOPY VARCHAR2, -- valid values: 'P', 'T', 'L', 'M' --File.Sql.39 bug 4440895
222 x_multicurrency_flag OUT NOCOPY VARCHAR2, -- valid values: 'Y', 'N' --File.Sql.39 bug 4440895
223 x_plan_type_name OUT NOCOPY pa_fin_plan_types_tl.name%TYPE, --File.Sql.39 bug 4440895
224 x_project_currency OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
225 x_labor_hrs_from_code OUT NOCOPY pa_proj_fp_options.report_labor_hrs_from_code%TYPE, --File.Sql.39 bug 4440895
226 x_cost_rv_number OUT NOCOPY pa_budget_versions.record_version_number%TYPE, --File.Sql.39 bug 4440895
227 x_rev_rv_number OUT NOCOPY pa_budget_versions.record_version_number%TYPE, --File.Sql.39 bug 4440895
228 x_cost_locked_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
229 x_rev_locked_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
230 x_ar_ac_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
231 x_plan_type_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
232 x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
233 x_plan_class_code OUT NOCOPY VARCHAR2, -- FP L: Plan Class Security --File.Sql.39 bug 4440895
234 x_display_res_flag OUT NOCOPY VARCHAR2, -- bug 3081511 --File.Sql.39 bug 4440895
235 x_display_resgp_flag OUT NOCOPY VARCHAR2, -- bug 3081511 --File.Sql.39 bug 4440895
236 x_auto_baselined_flag OUT NOCOPY VARCHAR2, -- bug 3146974 --File.Sql.39 bug 4440895
237 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
238 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
239 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
240 )
241 is
242
243 l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
244 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
245 l_working_or_baselined VARCHAR2(30);
246 l_cost_or_revenue VARCHAR2(30);
247 l_ar_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
248 l_ac_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
249 l_c_budget_status_code pa_budget_versions.budget_status_code%TYPE;
250 l_r_budget_status_code pa_budget_versions.budget_status_code%TYPE;
251 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
252 l_report_labor_hrs_from_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE;
253 l_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
254 l_margin_derived_code pa_proj_fp_options.margin_derived_from_code%TYPE;
255 l_grouping_type VARCHAR2(30);
256 l_compl_grouping_type VARCHAR2(30);
257 l_cost_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
258 l_rev_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
259 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
260 l_compl_resource_list_id pa_budget_versions.resource_list_id%TYPE;
261 l_rv_number pa_budget_versions.record_version_number%TYPE;
262 l_compl_rv_number pa_budget_versions.record_version_number%TYPE;
263 l_uncategorized_flag pa_resource_lists.uncategorized_flag%TYPE;
264 l_compl_uncategorized_flag pa_resource_lists.uncategorized_flag%TYPE;
265 l_debug_mode VARCHAR2(30);
266 l_is_cost_locked_by_user VARCHAR2(1);
267 l_is_rev_locked_by_user VARCHAR2(1);
268 l_cost_locked_by_person_id NUMBER;
269 l_rev_locked_by_person_id NUMBER;
270 l_resource_level VARCHAR2(1); -- bug 2813661
271 l_cost_resource_level VARCHAR2(1); -- bug 2813661
272 l_revenue_resource_level VARCHAR2(1); -- bug 2813661
273
274 -- local error handling variables
275 l_return_status VARCHAR2(1);
276 l_msg_count NUMBER;
277 l_msg_data VARCHAR2(2000);
278 l_version_id pa_budget_versions.budget_version_id%TYPE;
279 l_module_name VARCHAR2(100);
280 l_msg_index_out NUMBER;
281
282 BEGIN
283 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_view_initialize', '100: entered procedure', 2);
284 x_msg_count := 0;
285 l_module_name:='pa_fp_view_plans_txn_pub';
286 x_return_status := FND_API.G_RET_STS_SUCCESS;
287 pa_debug.set_err_stack('pa_fp_view_plans_txn_pub.nonhgrid_view_initialize');
288 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
289 l_debug_mode := NVL(l_debug_mode, 'Y');
290 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
291 IF ( p_cost_version_id IS NULL
292 AND p_rev_version_id IS NULL ) THEN
293
294 IF l_debug_mode = 'Y' THEN
295 pa_debug.g_err_stage := 'Both cost and rev version ids are null' ;
296 pa_debug.write('nonhgrid_view_initialize: ' || l_module_name,pa_debug.g_err_stage,1);
297 END IF;
298 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
299
300 END IF;
301 IF l_debug_mode = 'Y' THEN
302 pa_debug.g_err_stage := 'Inside nonhgrid_view_initialize...';
303 pa_debug.write('nonhgrid_view_initialize: ' || l_module_name,pa_debug.g_err_stage,3);
304 END IF;
305
306 -- bug 3146974 GET AUTO BASELINED FLAG
307 x_auto_baselined_flag :=
308 Pa_Fp_Control_Items_Utils.IsFpAutoBaselineEnabled(p_project_id); -- OUTPUT: x_auto_baselined_flag
309
310 /*Populate l_version_id with any one of the version ids passed so that the common global
311 variables can be initialised
312 */
313 IF (p_cost_version_id IS NOT NULL) THEN
314 l_version_id :=p_cost_version_id;
315 ELSIF (p_rev_version_id IS NOT NULL) THEN
316 l_version_id :=p_rev_version_id;
317 END IF;
318
319
320 pa_fp_view_plans_txn_pub.G_SINGLE_VERSION_ID := l_version_id;
321
322 SELECT nvl(bv.approved_cost_plan_type_flag, 'N'),
323 nvl(bv.approved_rev_plan_type_flag, 'N')
324 INTO l_ac_flag,
325 l_ar_flag
326 FROM pa_budget_versions bv
327 WHERE bv.budget_version_id = l_version_id;
328
329 -- >>>>> BUG FIX 2602849: need to check only AR flag <<<<<
330 -- >>>> BUG FIX 2650878: project or projfunc, depending on AR flag <<<<
331 if l_ar_flag = 'Y' then
332 -- APPROVED REVENUE: go with Project Functional Currency
333 x_ar_ac_flag := 'Y';
334 -- get PROJECT CURRENCY
335 select projfunc_currency_code
336 into x_project_currency
337 from pa_projects_all
338 where project_id = p_project_id;
339 pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE := 'PROJFUNC';
340 else
341 -- NOT APPROVED REVENUE: go with Project Currency
342 x_ar_ac_flag := 'N';
343 -- get PROJECT CURRENCY
344 select project_currency_code
345 into x_project_currency
346 from pa_projects_all
347 where project_id = p_project_id;
348 pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE := 'PROJECT';
349 end if; -- approved revenue flag
350
351 select fin_plan_type_id,
352 NVL(plan_in_multi_curr_flag, 'N'),
353 proj_fp_options_id
354 into l_fin_plan_type_id,
355 l_multi_curr_flag,
356 l_proj_fp_options_id
357 from pa_proj_fp_options
358 where project_id = p_project_id and
359 fin_plan_version_id = l_version_id and
360 fin_plan_option_level_code = 'PLAN_VERSION';
361 x_fin_plan_type_id := l_fin_plan_type_id;
362
363 -- 05/30/03 FP L: Plan Class Security
364 x_plan_class_code := pa_fin_plan_type_global.plantype_to_planclass
365 (p_project_id, l_fin_plan_type_id);
366
367 select proj_fp_options_id,
368 fin_plan_preference_code
369 into x_plan_type_fp_options_id,
370 l_fp_preference_code
371 from pa_proj_fp_options
372 where project_id = p_project_id and
373 fin_plan_type_id = l_fin_plan_type_id and
374 fin_plan_option_level_code = 'PLAN_TYPE';
375 pa_fp_view_plans_txn_pub.G_PLAN_TYPE_ID := l_fin_plan_type_id;
376 pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG := l_multi_curr_flag;
377
378 -- get PLAN TYPE NAME
379 select name
380 into x_plan_type_name
381 from pa_fin_plan_types_tl
382 where fin_plan_type_id = l_fin_plan_type_id and
383 language = USERENV('LANG');
384
385 -- retrieve report_labor_hrs, margin_derived codes from PLAN TYPE entry
386 select report_labor_hrs_from_code,
387 margin_derived_from_code
388 into l_report_labor_hrs_from_code,
389 l_margin_derived_code
390 from pa_proj_fp_options
391 where project_id = p_project_id and
392 fin_plan_type_id = l_fin_plan_type_id and
393 fin_plan_option_level_code = 'PLAN_TYPE';
394 pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE := l_report_labor_hrs_from_code;
395 pa_fp_view_plans_txn_pub.G_DERIVE_MARGIN_FROM_CODE := l_margin_derived_code;
396
397
398 /*Fix for bug 2699644 starts*/
399 /*Get the values for the revenue version id and populate the global variables with those
400 values
401 */
402 IF(p_cost_version_id IS NOT NULL) THEN
403
404 SELECT DECODE(rl.group_resource_type_id,
405 0, 'NONGROUPED',
406 'GROUPED'),
407 rl.resource_list_id,
408 bv.record_version_number,
409 nvl(rl.uncategorized_flag, 'N'),
410 DECODE(bv.budget_status_code,
411 'B', 'B',
412 'W')
413 INTO l_grouping_type,
414 l_resource_list_id,
415 l_rv_number,
416 l_uncategorized_flag,
417 l_c_budget_status_code
418 FROM pa_budget_versions bv,
419 pa_resource_lists_all_bg rl
420 WHERE bv.budget_version_id = p_cost_version_id and
421 bv.resource_list_id = rl.resource_list_id;
422
423
424 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := p_cost_version_id;
425 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
426 IF l_fp_preference_code = 'COST_AND_REV_SAME' THEN
427 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'ANY';
428 ELSE
429 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'COST';
430 END IF;
431
432 x_cost_rv_number := l_rv_number;
433 x_cost_rl_id := l_resource_list_id;
434
435 ELSE
436
437 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := -1;
438 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := '';
439 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := null;
440 l_grouping_type:=null;
441 x_cost_rv_number := -1;
442 x_cost_rl_id := -1;
443 END IF;
444
445 IF(p_rev_version_id IS NOT NULL) THEN
446
447 SELECT DECODE(rl.group_resource_type_id,
448 0, 'NONGROUPED',
449 'GROUPED'),
450 rl.resource_list_id,
451 bv.record_version_number,
452 nvl(rl.uncategorized_flag, 'N'),
453 DECODE(bv.budget_status_code,
454 'B', 'B',
455 'W')
456 INTO l_compl_grouping_type,
457 l_compl_resource_list_id,
458 l_compl_rv_number,
459 l_compl_uncategorized_flag,
460 l_r_budget_status_code
461 FROM pa_budget_versions bv,
462 pa_resource_lists_all_bg rl
463 WHERE bv.budget_version_id = p_rev_version_id and
464 bv.resource_list_id = rl.resource_list_id;
465
466
467 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := p_rev_version_id;
468 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_compl_grouping_type;
469 IF (pa_fp_view_plans_txn_pub.G_DISPLAY_FROM = 'COST') THEN
470
471 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'BOTH';
472
473 ELSE
474 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'REVENUE';
475 END IF;
476
477 x_rev_rv_number := l_compl_rv_number;
478 x_rev_rl_id := l_compl_resource_list_id;
479
480 ELSE
481
482 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := -1;
483 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := '';
484 x_rev_rv_number := -1;
485 x_rev_rl_id := -1;
486 l_compl_grouping_type:=null;
487
488 END IF;
489
490 -- BUG FIX 3050448: replace x_budget_status_code with
491 -- x_cost_budget_status_code and x_rev_budget_status_code
492 /*
493 IF(l_c_budget_status_code='W' OR
494 l_r_budget_status_code='W') THEN
495 x_budget_status_code:='W';
496 ELSE
497 x_budget_status_code:='B';
498 END IF;
499 */
500 x_cost_budget_status_code := l_c_budget_status_code;
501 x_rev_budget_status_code := l_r_budget_status_code;
502 -- END BUG FIX 3050448
503
504 IF l_grouping_type = 'GROUPED' THEN
505 IF l_compl_grouping_type = 'GROUPED' THEN
506 x_grouping_type := 'GROUPED';
507 ELSE
508 x_grouping_type := 'MIXED';
509 END IF;
510 ELSE
511 IF l_compl_grouping_type = 'GROUPED' THEN
512 x_grouping_type := 'MIXED';
513 ELSE
514 x_grouping_type := 'NONGROUPED';
515 END IF;
516 END IF;
517
518 IF (pa_fp_view_plans_txn_pub.G_DISPLAY_FROM = 'BOTH') THEN
519
520 -- planning level code for cost version: P, T, L, or M
521 SELECT cost_fin_plan_level_code
522 INTO l_cost_planning_level
523 FROM pa_proj_fp_options
524 WHERE proj_fp_options_id = l_proj_fp_options_id;
525
526 -- planning level code for revenue (compl) version
527 SELECT revenue_fin_plan_level_code
528 INTO l_rev_planning_level
529 FROM pa_proj_fp_options
530 WHERE fin_plan_version_id = p_rev_version_id;
531
532 -- PLANNING LEVEL = 'P' if one of the planning levels is P
533 IF (l_cost_planning_level = 'P') or (l_rev_planning_level = 'P') THEN
534 x_planning_level := 'P';
535 ELSE
536 x_planning_level := l_cost_planning_level;
537 END IF;
538
539 ELSIF (pa_fp_view_plans_txn_pub.G_DISPLAY_FROM = 'ANY') THEN
540
541 SELECT all_fin_plan_level_code
542 INTO l_cost_planning_level
543 FROM pa_proj_fp_options
544 WHERE fin_plan_version_id = p_cost_version_id;
545
546 x_planning_level := l_cost_planning_level;
547
548 ELSIF (pa_fp_view_plans_txn_pub.G_DISPLAY_FROM = 'REVENUE') THEN
549
550 SELECT revenue_fin_plan_level_code
551 INTO l_rev_planning_level
552 FROM pa_proj_fp_options
553 WHERE fin_plan_version_id = p_rev_version_id;
554
555 x_planning_level := l_rev_planning_level;
556
557 ELSIF (pa_fp_view_plans_txn_pub.G_DISPLAY_FROM = 'COST') THEN
558
559 SELECT cost_fin_plan_level_code
560 INTO l_cost_planning_level
561 FROM pa_proj_fp_options
562 WHERE fin_plan_version_id = p_cost_version_id;
563
564 x_planning_level := l_cost_planning_level;
565
566 END IF;
567
568 x_display_from := pa_fp_view_plans_txn_pub.G_DISPLAY_FROM;
569 x_multicurrency_flag := pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG;
570 x_cost_version_id := pa_fp_view_plans_txn_pub.G_COST_VERSION_ID;
571 x_rev_version_id := pa_fp_view_plans_txn_pub.G_REV_VERSION_ID;
572 x_labor_hrs_from_code := pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE;
573
574 pa_fp_view_plans_txn_pub.G_COST_RESOURCE_LIST_ID := x_cost_rl_id;
575 pa_fp_view_plans_txn_pub.G_REVENUE_RESOURCE_LIST_ID := x_rev_rl_id;
576
577
578 if (pa_fp_view_plans_txn_pub.G_DISPLAY_FROM = 'BOTH' and
579 l_uncategorized_flag = 'Y' and l_compl_uncategorized_flag = 'Y') or
580 (pa_fp_view_plans_txn_pub.G_DISPLAY_FROM <> 'BOTH' and
581 l_uncategorized_flag = 'Y') then
582 x_planned_resources_flag := 'N';
583 else
584 x_planned_resources_flag := 'Y';
585 end if;
586
587 -- determine locked status of budget version(s)
588 -- BUG 2813661: use pa_fp_view_plans_util.get_plan_version_res_level to set
589 -- x_grouping_type and x_planned_resources_flag
590 if x_display_from = 'ANY' then
591 pa_fin_plan_utils.Check_Locked_By_User
592 (p_user_id => p_user_id,
593 p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
594 x_is_locked_by_userid => l_is_cost_locked_by_user,
595 x_locked_by_person_id => l_cost_locked_by_person_id,
596 x_return_status => l_return_status,
597 x_msg_count => l_msg_count,
598 x_msg_data => l_msg_data);
599 if l_is_cost_locked_by_user = 'N' then
600 if l_cost_locked_by_person_id is null then
601 x_cost_locked_name := 'NONE';
602 x_rev_locked_name := 'NONE';
603 else
604 x_cost_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
605 x_rev_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
606 end if;
607 else
608 x_cost_locked_name := 'SELF';
609 x_rev_locked_name := 'SELF';
610 end if; -- is_cost_locked_by_user
611
612 pa_fp_view_plans_util.get_plan_version_res_level
613 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
614 p_entered_amts_only_flag => 'N',
615 x_resource_level => l_resource_level,
616 x_return_status => l_return_status,
617 x_msg_count => l_msg_count,
618 x_msg_data => l_msg_data);
619 if l_return_status = FND_API.G_RET_STS_SUCCESS then
620 if l_resource_level = 'R' then
621 x_display_res_flag := 'Y';
622 x_display_resgp_flag := 'N';
623 elsif l_resource_level = 'G' then
624 x_display_res_flag := 'N';
625 x_display_resgp_flag := 'Y';
626 elsif l_resource_level = 'M' then
627 x_display_res_flag := 'Y';
628 x_display_resgp_flag := 'Y';
629 else
630 x_display_res_flag := 'N';
631 x_display_resgp_flag := 'N';
632 end if;
633 else
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 x_msg_count := FND_MSG_PUB.Count_Msg;
636 if x_msg_count = 1 then
637 PA_INTERFACE_UTILS_PUB.get_messages
638 (p_encoded => FND_API.G_TRUE,
639 p_msg_index => 1,
640 p_data => x_msg_data,
641 p_msg_index_out => l_msg_index_out);
642 end if;
643 return;
644 end if;
645
646 elsif x_display_from = 'COST' then
647 pa_fin_plan_utils.Check_Locked_By_User
648 (p_user_id => p_user_id,
649 p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
650 x_is_locked_by_userid => l_is_cost_locked_by_user,
651 x_locked_by_person_id => l_cost_locked_by_person_id,
652 x_return_status => l_return_status,
653 x_msg_count => l_msg_count,
654 x_msg_data => l_msg_data);
655 if l_is_cost_locked_by_user = 'N' then
656 if l_cost_locked_by_person_id is null then
657 x_cost_locked_name := 'NONE';
658 else
659 x_cost_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
660 end if;
661 else
662 x_cost_locked_name := 'SELF';
663 end if; -- is_cost_locked_by_user
664
665 pa_fp_view_plans_util.get_plan_version_res_level
666 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
667 p_entered_amts_only_flag => 'Y',
668 x_resource_level => l_resource_level,
669 x_return_status => l_return_status,
670 x_msg_count => l_msg_count,
671 x_msg_data => l_msg_data);
672 if l_return_status = FND_API.G_RET_STS_SUCCESS then
673 if l_resource_level = 'R' then
674 x_display_res_flag := 'Y';
675 x_display_resgp_flag := 'N';
676 elsif l_resource_level = 'G' then
677 x_display_res_flag := 'N';
678 x_display_resgp_flag := 'Y';
679 elsif l_resource_level = 'M' then
680 x_display_res_flag := 'Y';
681 x_display_resgp_flag := 'Y';
682 else
683 x_display_res_flag := 'N';
684 x_display_resgp_flag := 'N';
685 end if;
686 else
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 x_msg_count := FND_MSG_PUB.Count_Msg;
689 if x_msg_count = 1 then
690 PA_INTERFACE_UTILS_PUB.get_messages
691 (p_encoded => FND_API.G_TRUE,
692 p_msg_index => 1,
693 p_data => x_msg_data,
694 p_msg_index_out => l_msg_index_out);
695 end if;
696 return;
697 end if;
698
699 elsif x_display_from = 'REVENUE' then
700 pa_fin_plan_utils.Check_Locked_By_User
701 (p_user_id => p_user_id,
702 p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
703 x_is_locked_by_userid => l_is_rev_locked_by_user,
704 x_locked_by_person_id => l_rev_locked_by_person_id,
705 x_return_status => l_return_status,
706 x_msg_count => l_msg_count,
707 x_msg_data => l_msg_data);
708 if l_is_rev_locked_by_user = 'N' then
709 if l_rev_locked_by_person_id is null then
710 x_rev_locked_name := 'NONE';
711 else
712 x_rev_locked_name := pa_fin_plan_utils.get_person_name(l_rev_locked_by_person_id);
713 end if;
714 else
715 x_rev_locked_name := 'SELF';
716 end if; -- is_rev_locked_by_user
717
718 pa_fp_view_plans_util.get_plan_version_res_level
719 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
720 p_entered_amts_only_flag => 'Y',
721 x_resource_level => l_resource_level,
722 x_return_status => l_return_status,
723 x_msg_count => l_msg_count,
724 x_msg_data => l_msg_data);
725 if l_return_status = FND_API.G_RET_STS_SUCCESS then
726 if l_resource_level = 'R' then
727 x_display_res_flag := 'Y';
728 x_display_resgp_flag := 'N';
729 elsif l_resource_level = 'G' then
730 x_display_res_flag := 'N';
731 x_display_resgp_flag := 'Y';
732 elsif l_resource_level = 'M' then
733 x_display_res_flag := 'Y';
734 x_display_resgp_flag := 'Y';
735 else
736 x_display_res_flag := 'N';
737 x_display_resgp_flag := 'N';
738 end if;
739 else
740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
741 x_msg_count := FND_MSG_PUB.Count_Msg;
742 if x_msg_count = 1 then
743 PA_INTERFACE_UTILS_PUB.get_messages
744 (p_encoded => FND_API.G_TRUE,
745 p_msg_index => 1,
746 p_data => x_msg_data,
747 p_msg_index_out => l_msg_index_out);
748 end if;
749 return;
750 end if;
751
752 elsif x_display_from = 'BOTH' then
753
754 -- FOR COST VERSION
755 pa_fin_plan_utils.Check_Locked_By_User
756 (p_user_id => p_user_id,
757 p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
758 x_is_locked_by_userid => l_is_cost_locked_by_user,
759 x_locked_by_person_id => l_cost_locked_by_person_id,
760 x_return_status => l_return_status,
761 x_msg_count => l_msg_count,
762 x_msg_data => l_msg_data);
763 if l_is_cost_locked_by_user = 'N' then
764 if l_cost_locked_by_person_id is null then
765 x_cost_locked_name := 'NONE';
766 else
767 x_cost_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
768 end if;
769 else
770 x_cost_locked_name := 'SELF';
771 end if; -- is_cost_locked_by_user
772
773 pa_fp_view_plans_util.get_plan_version_res_level
774 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
775 p_entered_amts_only_flag => 'Y',
776 x_resource_level => l_cost_resource_level,
777 x_return_status => l_return_status,
778 x_msg_count => l_msg_count,
779 x_msg_data => l_msg_data);
780 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 x_msg_count := FND_MSG_PUB.Count_Msg;
783 if x_msg_count = 1 then
784 PA_INTERFACE_UTILS_PUB.get_messages
785 (p_encoded => FND_API.G_TRUE,
786 p_msg_index => 1,
787 p_data => x_msg_data,
788 p_msg_index_out => l_msg_index_out);
789 end if;
790 return;
791 end if;
792
793 -- FOR REVENUE VERSION
794 pa_fin_plan_utils.Check_Locked_By_User
795 (p_user_id => p_user_id,
796 p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
797 x_is_locked_by_userid => l_is_rev_locked_by_user,
798 x_locked_by_person_id => l_rev_locked_by_person_id,
799 x_return_status => l_return_status,
800 x_msg_count => l_msg_count,
801 x_msg_data => l_msg_data);
802 if l_is_rev_locked_by_user = 'N' then
803 if l_rev_locked_by_person_id is null then
804 x_rev_locked_name := 'NONE';
805 else
806 x_rev_locked_name := pa_fin_plan_utils.get_person_name(l_rev_locked_by_person_id);
807 end if;
808 else
809 x_rev_locked_name := 'SELF';
810 end if; -- is_cost_locked_by_user
811
812 pa_fp_view_plans_util.get_plan_version_res_level
813 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
814 p_entered_amts_only_flag => 'Y',
815 x_resource_level => l_revenue_resource_level,
816 x_return_status => l_return_status,
817 x_msg_count => l_msg_count,
818 x_msg_data => l_msg_data);
819 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
821 x_msg_count := FND_MSG_PUB.Count_Msg;
822 if x_msg_count = 1 then
823 PA_INTERFACE_UTILS_PUB.get_messages
824 (p_encoded => FND_API.G_TRUE,
825 p_msg_index => 1,
826 p_data => x_msg_data,
827 p_msg_index_out => l_msg_index_out);
828 end if;
829 return;
830 end if;
831 if l_cost_resource_level = 'R' and l_revenue_resource_level = 'R' then
832 x_display_res_flag := 'Y';
833 x_display_resgp_flag := 'N';
834 elsif l_cost_resource_level = 'G' and l_revenue_resource_level = 'G' then
835 x_display_res_flag := 'N';
836 x_display_resgp_flag := 'Y';
837 else
838 x_display_res_flag := 'Y';
839 x_display_resgp_flag := 'Y';
840 end if;
841
842 end if;
843
844 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_view_initialize', '1000: exited procedure', 2);
845 END nonhgrid_view_initialize;
846 /* ------------------------------------------------------------- */
847
848 /* ---------------------- CHANGE HISTORY ----------------------- */
849 -- 10/08/02: x_planned_resources_flag:
850 -- check pa_resource_lists.uncategorized_flag
851 -- 10/28/02: make sure resource list global variables are populated
852 -- for resource query to work
853 -- 11/08/02: x_project_currency = project or projfunc currency, depending if
854 -- plan type = AR
855 -- populate G_DISPLAY_CURRENCY_TYPE
856 -- 12/30/02: x_project_currency can be AGREEMENT CURRENCY if ci_id is not null
857 -- 07/30/03: changed logic for populating x_planned_resources_flag and x_grouping_type
858 -- BUG 2813661
859 procedure nonhgrid_edit_initialize
860 (p_project_id IN pa_budget_versions.project_id%TYPE,
861 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
862 -- p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE,
863 -- p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE,
864 -- p_working_or_baselined IN VARCHAR2,
865 -- p_cost_or_revenue IN VARCHAR2,
866 x_budget_status_code OUT NOCOPY pa_budget_versions.budget_status_code%TYPE, --File.Sql.39 bug 4440895
867 x_current_working_flag OUT NOCOPY pa_budget_versions.current_working_flag%TYPE, --File.Sql.39 bug 4440895
868 x_cost_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
869 x_rev_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
870 x_cost_rl_id OUT NOCOPY pa_budget_versions.resource_list_id%TYPE, --File.Sql.39 bug 4440895
871 x_rev_rl_id OUT NOCOPY pa_budget_versions.resource_list_id%TYPE, --File.Sql.39 bug 4440895
872 x_display_from OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
873 x_planned_resources_flag OUT NOCOPY VARCHAR2, -- valid values: 'Y', 'N' --File.Sql.39 bug 4440895
874 x_grouping_type OUT NOCOPY VARCHAR2, -- valid values: 'GROUPED', 'NONGROUPED', 'MIXED' --File.Sql.39 bug 4440895
875 x_planning_level OUT NOCOPY VARCHAR2, -- valid values: 'P', 'T', 'L', 'M' --File.Sql.39 bug 4440895
876 x_multicurrency_flag OUT NOCOPY VARCHAR2, -- valid values: 'Y', 'N' --File.Sql.39 bug 4440895
877 x_plan_type_name OUT NOCOPY pa_fin_plan_types_tl.name%TYPE, --File.Sql.39 bug 4440895
878 x_project_currency OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
879 x_record_version_number OUT NOCOPY pa_budget_versions.record_version_number%TYPE, --File.Sql.39 bug 4440895
880 x_plan_type_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
881 x_plan_version_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
882 x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
883 x_ar_ac_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
884 x_auto_baselined_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
885 x_plan_class_code OUT NOCOPY VARCHAR2, -- FP L: Plan Class Security --File.Sql.39 bug 4440895
886 x_display_res_flag OUT NOCOPY VARCHAR2, -- bug 3081511 --File.Sql.39 bug 4440895
887 x_display_resgp_flag OUT NOCOPY VARCHAR2, -- bug 3081511 --File.Sql.39 bug 4440895
888 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
889 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
890 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
891 )
892 is
893
894 l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
895 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
896 l_working_or_baselined VARCHAR2(30);
897 l_cost_or_revenue VARCHAR2(30);
898 l_ar_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
899 l_ac_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
900 l_ci_id pa_budget_versions.ci_id%TYPE;
901 l_agreement_id pa_budget_versions.agreement_id%TYPE;
902 l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
903
904 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
905 l_report_labor_hrs_from_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE;
906 l_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
907 l_margin_derived_code pa_proj_fp_options.margin_derived_from_code%TYPE;
908 l_grouping_type VARCHAR2(30);
909 l_compl_grouping_type VARCHAR2(30);
910 l_cost_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
911 l_rev_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
912 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
913 l_compl_resource_list_id pa_budget_versions.resource_list_id%TYPE;
914 l_version_type pa_budget_versions.version_type%TYPE;
915 l_uncategorized_flag pa_resource_lists.uncategorized_flag%TYPE;
916 l_resource_level VARCHAR2(1); -- bug 2813661
917
918 -- local debugging variables
919 l_return_status VARCHAR2(1);
920 l_msg_count NUMBER(10);
921 l_msg_data VARCHAR2(2000);
922 l_msg_index_out NUMBER(10);
923
924 BEGIN
925 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_edit_initialize', '100: entered procedure', 2);
926 x_msg_count := 0;
927 x_return_status := FND_API.G_RET_STS_SUCCESS;
928
929 -- get AUTO BASELINED FLAG
930 x_auto_baselined_flag :=
931 Pa_Fp_Control_Items_Utils.IsFpAutoBaselineEnabled(p_project_id);
932
933 select DECODE(rl.group_resource_type_id,
934 0, 'NONGROUPED',
935 'GROUPED'),
936 bv.resource_list_id,
937 nvl(bv.budget_status_code, 'W'),
938 DECODE(bv.budget_status_code,
939 'B', 'B',
940 'W'),
941 DECODE(bv.version_type,
942 'COST', 'C',
943 'REVENUE', 'R',
944 'N'),
945 nvl(bv.current_working_flag, 'N'),
946 bv.record_version_number,
947 nvl(bv.approved_cost_plan_type_flag, 'N'),
948 nvl(bv.approved_rev_plan_type_flag, 'N'),
949 nvl(rl.uncategorized_flag, 'N')
950 into l_grouping_type,
951 l_resource_list_id,
952 x_budget_status_code,
953 l_working_or_baselined,
954 l_cost_or_revenue,
955 x_current_working_flag,
956 x_record_version_number,
957 l_ac_flag,
958 l_ar_flag,
959 l_uncategorized_flag
960 from pa_budget_versions bv,
961 pa_resource_lists_all_bg rl
962 where bv.budget_version_id = p_budget_version_id and
963 bv.resource_list_id = rl.resource_list_id;
964 pa_fp_view_plans_txn_pub.G_SINGLE_VERSION_ID := p_budget_version_id;
965 -- >>>>> BUG FIX 2602849: need to check only AR flag <<<<<
966 -- >>>> BUG FIX 2650878: project or projfunc, depending on AR flag <<<<
967 if l_ar_flag = 'Y' then
968 -- APPROVED REVENUE: go with Project Functional Currency
969 x_ar_ac_flag := 'Y';
970 -- get PROJECT CURRENCY
971 select projfunc_currency_code
972 into x_project_currency
973 from pa_projects_all
974 where project_id = p_project_id;
975 -- >>>> BUG FIX 2730016: check pa_agreements_all as well
976 select ci_id,
977 agreement_id
978 into l_ci_id,
979 l_agreement_id
980 from pa_budget_versions
981 where budget_version_id = p_budget_version_id;
982 if l_ci_id is not null and l_agreement_id is not null then
983 select nvl (agreement_currency_code, 'ANY')
984 into l_agreement_currency_code
985 from pa_agreements_all
986 where agreement_id = l_agreement_id;
987 if l_agreement_currency_code <> 'ANY' then
988 x_project_currency := l_agreement_currency_code;
989 end if;
990 end if; -- ci_id is not null
991 pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE := 'PROJFUNC';
992 else
993 -- NOT APPROVED REVENUE: go with Project Currency
994 x_ar_ac_flag := 'N';
995 -- get PROJECT CURRENCY
996 select project_currency_code
997 into x_project_currency
998 from pa_projects_all
999 where project_id = p_project_id;
1000 pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE := 'PROJECT';
1001 end if; -- approved revenue flag
1002
1003 select fin_plan_type_id,
1004 NVL(plan_in_multi_curr_flag, 'N'),
1005 proj_fp_options_id
1006 into l_fin_plan_type_id,
1007 l_multi_curr_flag,
1008 l_proj_fp_options_id
1009 from pa_proj_fp_options
1010 where project_id = p_project_id and
1011 fin_plan_version_id = p_budget_version_id and
1012 fin_plan_option_level_code = 'PLAN_VERSION';
1013
1014 -- 05/30/03 FP L: Plan Class Security
1015 x_plan_class_code := pa_fin_plan_type_global.plantype_to_planclass
1016 (p_project_id, l_fin_plan_type_id);
1017
1018 -- 8/26/02: retrieve fp_options_id for plan_version and plan_type level, and plan_type_id
1019 x_fin_plan_type_id := l_fin_plan_type_id;
1020 x_plan_version_fp_options_id := l_proj_fp_options_id;
1021 select proj_fp_options_id,
1022 fin_plan_preference_code
1023 into x_plan_type_fp_options_id,
1024 l_fp_preference_code
1025 from pa_proj_fp_options
1026 where project_id = p_project_id and
1027 fin_plan_type_id = l_fin_plan_type_id and
1028 fin_plan_option_level_code = 'PLAN_TYPE';
1029
1030 pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE := l_report_labor_hrs_from_code;
1031 pa_fp_view_plans_txn_pub.G_PLAN_TYPE_ID := l_fin_plan_type_id;
1032 pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG := l_multi_curr_flag;
1033 -- retrieve report_labor_hrs, margin_derived codes from PLAN TYPE entry
1034 select report_labor_hrs_from_code,
1035 margin_derived_from_code
1036 into l_report_labor_hrs_from_code,
1037 l_margin_derived_code
1038 from pa_proj_fp_options
1039 where project_id = p_project_id and
1040 fin_plan_type_id = l_fin_plan_type_id and
1041 fin_plan_option_level_code = 'PLAN_TYPE';
1042 pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE := l_report_labor_hrs_from_code;
1043 pa_fp_view_plans_txn_pub.G_DERIVE_MARGIN_FROM_CODE := l_margin_derived_code;
1044 -- get PLAN TYPE NAME
1045
1046 select name
1047 into x_plan_type_name
1048 from pa_fin_plan_types_tl
1049 where fin_plan_type_id = l_fin_plan_type_id and
1050 language = USERENV('LANG');
1051
1052
1053 if l_fp_preference_code = 'COST_AND_REV_SAME' then
1054 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_edit_initialize', '200: pref = COST_AND_REV_SAME', 1);
1055 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := p_budget_version_id;
1056 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := p_budget_version_id;
1057 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
1058 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
1059 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'ANY';
1060
1061 x_grouping_type := l_grouping_type;
1062 -- set planning level code for page: P, T, L, or M
1063 select all_fin_plan_level_code
1064 into l_cost_planning_level
1065 from pa_proj_fp_options
1066 where proj_fp_options_id = l_proj_fp_options_id;
1067 x_planning_level := l_cost_planning_level;
1068 x_cost_rl_id := l_resource_list_id;
1069 x_rev_rl_id := l_resource_list_id;
1070
1071 elsif l_fp_preference_code = 'COST_ONLY' then
1072 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_edit_initialize', '300: pref = COST_ONLY', 1);
1073 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := p_budget_version_id;
1074 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := -1;
1075 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
1076 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := '';
1077 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'COST';
1078 x_grouping_type := l_grouping_type;
1079 -- set planning level code for page: P, T, L, or M
1080 select cost_fin_plan_level_code
1081 into l_cost_planning_level
1082 from pa_proj_fp_options
1083 where proj_fp_options_id = l_proj_fp_options_id;
1084 x_planning_level := l_cost_planning_level;
1085 x_cost_rl_id := l_resource_list_id;
1086 x_rev_rl_id := -1;
1087
1088 elsif l_fp_preference_code = 'REVENUE_ONLY' then
1089 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_edit_initialize', '400: pref = REVENUE_ONLY', 1);
1090 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := -1;
1091 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := p_budget_version_id;
1092 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := '';
1093 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
1094 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'REVENUE';
1095 x_grouping_type := l_grouping_type;
1096 -- set planning level code for page: P, T, L, or M
1097 select revenue_fin_plan_level_code
1098 into l_rev_planning_level
1099 from pa_proj_fp_options
1100 where proj_fp_options_id = l_proj_fp_options_id;
1101 x_planning_level := l_rev_planning_level;
1102 x_cost_rl_id := -1;
1103 x_rev_rl_id := l_resource_list_id;
1104
1105 elsif l_fp_preference_code = 'COST_AND_REV_SEP' then
1106 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_edit_initialize', '500: pref = COST_AND_REV_SEP', 1);
1107 -- this is a cost/revenue version that's part of a cost-revenue pairing
1108 -- we need to find out which one it is
1109 select version_type
1110 into l_version_type
1111 from pa_budget_versions
1112 where budget_version_id = p_budget_version_id;
1113 if l_version_type = 'COST' then
1114 -- COST VERSION: treat as if COST_ONLY
1115 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := p_budget_version_id;
1116 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := -1;
1117 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
1118 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := '';
1119 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'COST';
1120 x_grouping_type := l_grouping_type;
1121 -- set planning level code for page: P, T, L, or M
1122 select cost_fin_plan_level_code
1123 into l_cost_planning_level
1124 from pa_proj_fp_options
1125 where proj_fp_options_id = l_proj_fp_options_id;
1126 x_planning_level := l_cost_planning_level;
1127 x_cost_rl_id := l_resource_list_id;
1128 x_rev_rl_id := -1;
1129 else
1130 -- REVENUE VERSION: treat as if REVENUE_ONLY
1131 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := -1;
1132 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := p_budget_version_id;
1133 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := '';
1134 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
1135 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'REVENUE';
1136 x_grouping_type := l_grouping_type;
1137 -- set planning level code for page: P, T, L, or M
1138 select revenue_fin_plan_level_code
1139 into l_rev_planning_level
1140 from pa_proj_fp_options
1141 where proj_fp_options_id = l_proj_fp_options_id;
1142 x_planning_level := l_rev_planning_level;
1143 x_cost_rl_id := -1;
1144 x_rev_rl_id := l_resource_list_id;
1145 end if; -- l_version_type
1146
1147 else
1148 --pa_debug.write('pa_fp_view_plans_txn_pub.hgrid_edit_initialize', '600: invalid value for FIN_PLAN_PREFERENCE_CODE', 1);
1149 return;
1150 end if; -- l_fp_preference_code
1151
1152 x_display_from := pa_fp_view_plans_txn_pub.G_DISPLAY_FROM;
1153 x_multicurrency_flag := pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG;
1154 x_cost_version_id := pa_fp_view_plans_txn_pub.G_COST_VERSION_ID;
1155 x_rev_version_id := pa_fp_view_plans_txn_pub.G_REV_VERSION_ID;
1156 pa_fp_view_plans_txn_pub.G_COST_RESOURCE_LIST_ID := x_cost_rl_id;
1157 pa_fp_view_plans_txn_pub.G_REVENUE_RESOURCE_LIST_ID := x_rev_rl_id;
1158 if (l_uncategorized_flag = 'Y') then
1159 x_planned_resources_flag := 'N';
1160 else
1161 x_planned_resources_flag := 'Y';
1162 end if;
1163
1164 -- *** BUG 2813661: use pa_fp_view_plans_util.get_plan_version_res_level to set
1165 -- x_grouping_type and x_planned_resources_flag
1166 pa_fp_view_plans_util.get_plan_version_res_level
1167 (p_budget_version_id => p_budget_version_id,
1168 p_entered_amts_only_flag => 'Y',
1169 x_resource_level => l_resource_level,
1170 x_return_status => l_return_status,
1171 x_msg_count => l_msg_count,
1172 x_msg_data => l_msg_data);
1173 if l_return_status = FND_API.G_RET_STS_SUCCESS then
1174 if l_resource_level = 'R' then
1175 x_display_res_flag := 'Y';
1176 x_display_resgp_flag := 'N';
1177 elsif l_resource_level = 'G' then
1178 x_display_res_flag := 'N';
1179 x_display_resgp_flag := 'Y';
1180 elsif l_resource_level = 'M' then
1181 x_display_res_flag := 'Y';
1182 x_display_resgp_flag := 'Y';
1183 else
1184 x_display_res_flag := 'N';
1185 x_display_resgp_flag := 'N';
1186 end if;
1187 else
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 /*
1190 PA_UTILS.Add_Message(p_app_short_name => 'PA',
1191 p_msg_name => l_msg_data);
1192 */
1193 x_msg_count := FND_MSG_PUB.Count_Msg;
1194 if x_msg_count = 1 then
1195 PA_INTERFACE_UTILS_PUB.get_messages
1196 (p_encoded => FND_API.G_TRUE,
1197 p_msg_index => 1,
1198 p_data => x_msg_data,
1199 p_msg_index_out => l_msg_index_out);
1200 end if;
1201 return;
1202 end if;
1203
1204 IF P_PA_DEBUG_MODE = 'Y' THEN
1205 pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_edit_initialize', '1000: exited procedure', 2);
1206 END IF;
1207 END nonhgrid_edit_initialize;
1208 /* ------------------------------------------------------------- */
1209
1210 -- HISTORY
1211 -- 10/22/2002: dlai commented out using ra_cursors; it's just a performance optimization
1212 -- that may cause errors like bug 2637079
1213 -- 11/08/02: the cursors are modified such that if they query from pa_resource_assignments,
1214 -- we use the value of G_DISPLAY_CURRENCY_TYPE to determine whether to query from
1215 -- the project columns or projfunc columns
1216 -- 12/24/02: Bug 2710844-The view plan should show resource assignments for which budget lines exists
1217 PROCEDURE view_plans_txn_populate_tmp
1218 (p_page_mode IN VARCHAR2, /* V - View mode ; E - Edit Mode */
1219 p_project_id IN pa_budget_versions.project_id%TYPE,
1220 p_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
1221 p_revenue_version_id IN pa_budget_versions.budget_version_id%TYPE,
1222 p_both_version_id IN pa_budget_versions.budget_version_id%TYPE,
1223 p_project_currency IN pa_projects_all.project_currency_code%TYPE,
1224 p_get_display_from IN VARCHAR2, -- 'COST', 'REVENUE', 'BOTH', 'ANY'
1225 p_filter_task_id IN pa_resource_assignments.task_id%TYPE,
1226 p_filter_resource_id IN pa_resource_list_members.resource_id%TYPE,
1227 p_filter_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
1228 p_filter_txncurrency IN pa_budget_lines.txn_currency_code%TYPE,
1229 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1230 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1231 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1232
1233 -------------------- CURSORS FOR MULTICURRENCY PLAN VERSIONS --------------------
1234 -- 9/25/02: Inserted UNION with query from PA_RESOURCE_ASSIGNMENTS; we want to
1235 -- return all entered rows (budget lines) as well as resource assignments for
1236 -- which budget lines have not yet been created
1237 --10/08/02: modified cursor query: where rlm.resource_type_code <> 'UNCATEGORIZED'
1238
1239 c_view_mode CONSTANT VARCHAR2(1) := 'V';
1240 c_edit_mode CONSTANT VARCHAR2(1) := 'E';
1241
1242 cursor cost_csr is
1243 select ra_cost.project_id,
1244 ra_cost.task_id,
1245 ra_cost.resource_list_member_id,
1246 bl_cost.resource_assignment_id,
1247 -1 as compl_resource_assignment_id,
1248 pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type, -- used in wrapper view to decide how to handle
1249 -- parent_member_id = null cases
1250 bl_cost.txn_currency_code,
1251 decode((NVL(ra_cost.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS UNIT_OF_MEASURE, -- ra_cost.unit_of_measure, bug 3463685
1252 SUM(nvl(bl_cost.quantity,0)) as quantity,
1253 SUM(nvl(bl_cost.txn_burdened_cost,0)) as burdened_cost,
1254 SUM(nvl(bl_cost.txn_raw_cost,0)) as raw_cost,
1255 0 as revenue,
1256 0 as margin,
1257 0 as margin_percent
1258 from pa_resource_assignments ra_cost,
1259 pa_budget_lines bl_cost,
1260 pa_resource_list_members rlm,
1261 pa_resources pr -- added for bug 3463685
1262 where ra_cost.budget_version_id = p_cost_version_id and
1263 ra_cost.resource_assignment_type = 'USER_ENTERED' and
1264 ra_cost.resource_assignment_id = bl_cost.resource_assignment_id and
1265 ra_cost.resource_list_member_id = rlm.resource_list_member_id and
1266 rlm.resource_id = pr.resource_id and -- added for bug 3463685
1267 (p_filter_task_id = -1 or ra_cost.task_id = p_filter_task_id) and
1268 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1269 (p_filter_resource_id = -1 or
1270 rlm.parent_member_id = p_filter_resource_id or
1271 (rlm.parent_member_id is null and
1272 rlm.resource_id = (select resource_id
1273 from pa_resource_list_members
1274 where resource_list_member_id = p_filter_resource_id))) and
1275 /*
1276 (p_filter_resource_id = -1 or
1277 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1278 rlm.parent_member_id = p_filter_resource_id) or
1279 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1280 rlm.parent_member_id is null and
1281 rlm.resource_id = p_filter_resource_id)) and
1282 */
1283 (p_filter_rlm_id = -1 or
1284 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1285 rlm.parent_member_id is null and
1286 rlm.resource_id = p_filter_rlm_id and
1287 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1288 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1289 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1290 rlm.parent_member_id is not null and
1291 rlm.resource_id = p_filter_rlm_id and
1292 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1293 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1294 (p_filter_txncurrency = 'ALL' or bl_cost.txn_currency_code = p_filter_txncurrency)
1295 --(bl_cost.txn_raw_cost is not null or bl_cost.txn_burdened_cost is not null)
1296 group by ra_cost.project_id,
1297 ra_cost.task_id,
1298 ra_cost.resource_list_member_id,
1299 bl_cost.resource_assignment_id,
1300 bl_cost.txn_currency_code,
1301 decode((NVL(ra_cost.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) -- ra_cost.unit_of_measure bug 3463685
1302 UNION
1303 select ra.project_id,
1304 ra.task_id,
1305 ra.resource_list_member_id,
1306 ra.resource_assignment_id,
1307 -1 as compl_resource_assignment_id,
1308 pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
1309 ftc.txn_currency_code as txn_currency_code,
1310 DECODE((NVL(ra.track_as_labor_flag,'N')), 'Y' , 'HOURS' , pr.unit_of_measure) as UNIT_OF_MEASURE, -- ra.unit_of_measure, bug 3463685
1311 ra.total_plan_quantity as quantity,
1312 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1313 'PROJECT', ra.total_project_burdened_cost,
1314 ra.total_plan_burdened_cost) as burdened_cost,
1315 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1316 'PROJECT', ra.total_project_raw_cost,
1317 ra.total_plan_raw_cost) as raw_cost,
1318 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1319 'PROJECT', ra.total_project_revenue,
1320 ra.total_plan_revenue) as revenue, -- null
1321 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1322 'PROJECT', ra.total_project_revenue - ra.total_project_raw_cost,
1323 ra.total_plan_revenue - ra.total_plan_raw_cost) as margin, -- null
1324 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1325 'PROJECT',
1326 DECODE(ra.total_project_revenue,
1327 0, 0,
1328 (ra.total_project_revenue - ra.total_project_raw_cost)/
1329 ra.total_project_revenue),
1330 DECODE(ra.total_plan_revenue,
1331 0, 0,
1332 (ra.total_plan_revenue - ra.total_plan_raw_cost)/
1333 ra.total_plan_revenue)) as margin_percent -- null
1334 from pa_resource_assignments ra,
1335 pa_resource_list_members rlm,
1336 pa_fp_txn_currencies ftc,
1337 pa_resources pr -- added for bug 3463685
1338 where ra.budget_version_id = p_cost_version_id and
1339 ra.resource_assignment_type = 'USER_ENTERED' and
1340 ra.resource_list_member_id = rlm.resource_list_member_id and
1341 rlm.resource_id = pr.resource_id and -- added for bug 3463685
1342 ra.budget_version_id = ftc.fin_plan_version_id and
1343 (p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
1344 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1345 (p_filter_resource_id = -1 or
1346 rlm.parent_member_id = p_filter_resource_id or
1347 (rlm.parent_member_id is null and
1348 rlm.resource_id = (select resource_id
1349 from pa_resource_list_members
1350 where resource_list_member_id = p_filter_resource_id))) and
1351 /*
1352 (p_filter_resource_id = -1 or
1353 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1354 rlm.parent_member_id = p_filter_resource_id) or
1355 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1356 rlm.parent_member_id is null and
1357 rlm.resource_id = p_filter_resource_id)) and
1358 */
1359 (p_filter_rlm_id = -1 or
1360 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1361 rlm.parent_member_id is null and
1362 rlm.resource_id = p_filter_rlm_id and
1363 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1364 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1365 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1366 rlm.parent_member_id is not null and
1367 rlm.resource_id = p_filter_rlm_id and
1368 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1369 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1370 ftc.default_cost_curr_flag = 'Y' and
1371 (p_filter_txncurrency = 'ALL' or
1372 ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
1373 not exists(select bl.resource_assignment_id from pa_budget_lines bl
1374 where ra.resource_assignment_id = bl.resource_assignment_id) and
1375 p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
1376
1377 cursor revenue_csr is
1378 select ra_revenue.project_id,
1379 ra_revenue.task_id,
1380 ra_revenue.resource_list_member_id,
1381 bl_revenue.resource_assignment_id,
1382 pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping as grouping_type,
1383 bl_revenue.txn_currency_code,
1384 DECODE((NVL(ra_revenue.track_as_labor_flag,'N')), 'Y' , 'HOURS' , pr.unit_of_measure) as UNIT_OF_MEASURE, -- ra_revenue.unit_of_measure, bug 3463685
1385 SUM(nvl(bl_revenue.quantity,0)) as quantity,
1386 -- 0 as burdened_cost,
1387 -- 0 as raw_cost,
1388 SUM(nvl(bl_revenue.txn_revenue,0)) as revenue
1389 -- 0 as margin,
1390 -- 0 as margin_percent
1391 from pa_resource_assignments ra_revenue,
1392 pa_budget_lines bl_revenue,
1393 pa_resource_list_members rlm,
1394 pa_resources pr -- Added for bug 3463685
1395 where ra_revenue.budget_version_id = p_revenue_version_id and
1396 ra_revenue.resource_assignment_type = 'USER_ENTERED' and
1397 ra_revenue.resource_assignment_id = bl_revenue.resource_assignment_id and
1398 ra_revenue.resource_list_member_id = rlm.resource_list_member_id and
1399 pr.resource_id = rlm.resource_id and -- added for bug 3463685
1400 (p_filter_task_id = -1 or ra_revenue.task_id = p_filter_task_id) and
1401 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1402 (p_filter_resource_id = -1 or
1403 rlm.parent_member_id = p_filter_resource_id or
1404 (rlm.parent_member_id is null and
1405 rlm.resource_id = (select resource_id
1406 from pa_resource_list_members
1407 where resource_list_member_id = p_filter_resource_id))) and
1408 /*
1409 (p_filter_resource_id = -1 or
1410 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and
1411 rlm.parent_member_id = p_filter_resource_id) or
1412 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and
1413 rlm.parent_member_id is null and
1414 rlm.resource_id = p_filter_resource_id)) and
1415 */
1416 (p_filter_rlm_id = -1 or
1417 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and /* Bug 2843566 - changed cost function to rev function */
1418 rlm.parent_member_id is null and
1419 rlm.resource_id = p_filter_rlm_id and
1420 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1421 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1422 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and /* Bug 2843566 - changed cost function to rev
1423 function */
1424 rlm.parent_member_id is not null and
1425 rlm.resource_id = p_filter_rlm_id and
1426 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1427 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1428 (p_filter_txncurrency = 'ALL' or bl_revenue.txn_currency_code = p_filter_txncurrency)
1429 --bl_revenue.txn_revenue is not null
1430 group by ra_revenue.project_id,
1431 ra_revenue.task_id,
1432 ra_revenue.resource_list_member_id,
1433 bl_revenue.resource_assignment_id,
1434 bl_revenue.txn_currency_code,
1435 DECODE((NVL(ra_revenue.track_as_labor_flag,'N')), 'Y' , 'HOURS' , pr.unit_of_measure)-- pr.unit_of_measure --ra_revenue.unit_of_measure bug 3463685
1436 UNION
1437 select ra.project_id,
1438 ra.task_id,
1439 ra.resource_list_member_id,
1440 ra.resource_assignment_id,
1441 pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping as grouping_type,
1442 ftc.txn_currency_code as txn_currency_code,
1443 DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) as unit_of_measure, -- ra.unit_of_measure, bug 3463685
1444 ra.total_plan_quantity as quantity,
1445 -- 0 as burdened_cost,
1446 -- 0 as raw_cost,
1447 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1448 'PROJECT', ra.total_project_revenue,
1449 ra.total_plan_revenue) as revenue -- null
1450 -- 0 as margin,
1451 -- 0 as margin_percent
1452 from pa_resource_assignments ra,
1453 pa_resource_list_members rlm,
1454 pa_fp_txn_currencies ftc,
1455 pa_resources pr -- added for bug 3463685
1456 where ra.budget_version_id = p_revenue_version_id and
1457 ra.resource_assignment_type = 'USER_ENTERED' and
1458 ra.resource_list_member_id = rlm.resource_list_member_id and
1459 pr.resource_id = rlm.resource_id and -- bug 3463685
1460 (p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
1461 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1462 (p_filter_resource_id = -1 or
1463 rlm.parent_member_id = p_filter_resource_id or
1464 (rlm.parent_member_id is null and
1465 rlm.resource_id = (select resource_id
1466 from pa_resource_list_members
1467 where resource_list_member_id = p_filter_resource_id))) and
1468 /*
1469 (p_filter_resource_id = -1 or
1470 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1471 rlm.parent_member_id = p_filter_resource_id) or
1472 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1473 rlm.parent_member_id is null and
1474 rlm.resource_id = p_filter_resource_id)) and
1475 */
1476 (p_filter_rlm_id = -1 or
1477 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and /* Bug 2843566 - changed cost function to rev
1478 function */
1479 rlm.parent_member_id is null and
1480 rlm.resource_id = p_filter_rlm_id and
1481 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1482 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1483 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and /* Bug 2843566 - changed cost function to rev
1484 function */
1485 rlm.parent_member_id is not null and
1486 rlm.resource_id = p_filter_rlm_id and
1487 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1488 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1489 ra.budget_version_id = ftc.fin_plan_version_id and
1490 ftc.default_rev_curr_flag = 'Y' and
1491 (p_filter_txncurrency = 'ALL' or
1492 ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
1493 not exists(select bl.resource_assignment_id from pa_budget_lines bl
1494 where ra.resource_assignment_id = bl.resource_assignment_id) and
1495 p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
1496
1497 cursor all_csr is
1498 select ra.project_id,
1499 ra.task_id,
1500 ra.resource_list_member_id,
1501 bl.resource_assignment_id,
1502 pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
1503 bl.txn_currency_code,
1504 DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) as unit_of_measure, -- ra.unit_of_measure, bug 3463685
1505 SUM(nvl(bl.quantity,0)) as quantity,
1506 SUM(nvl(bl.txn_burdened_cost,0)) as burdened_cost,
1507 SUM(nvl(bl.txn_raw_cost,0)) as raw_cost,
1508 SUM(nvl(bl.txn_revenue,0)) as revenue,
1509 DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
1510 'R', SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_raw_cost,0)),
1511 SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_burdened_cost,0))) as margin,
1512 DECODE(SUM(nvl(bl.txn_revenue,0)),
1513 0, 0,
1514 null, 0,
1515 DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
1516 'R', (SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_raw_cost,0)))/
1517 SUM(nvl(bl.txn_revenue,0)),
1518 (SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_burdened_cost,0)))/
1519 SUM(nvl(bl.txn_revenue,0)))) as margin_percent
1520 from pa_resource_assignments ra,
1521 pa_budget_lines bl,
1522 pa_resource_list_members rlm,
1523 pa_resources pr -- Added for bug 3463685
1524 where ra.budget_version_id = p_both_version_id and
1525 ra.resource_assignment_type = 'USER_ENTERED' and
1526 ra.resource_assignment_id = bl.resource_assignment_id and
1527 ra.resource_list_member_id = rlm.resource_list_member_id and
1528 pr.resource_id = rlm.resource_id and -- added for bug 3463685
1529 (p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
1530 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1531 (p_filter_resource_id = -1 or
1532 rlm.parent_member_id = p_filter_resource_id or
1533 (rlm.parent_member_id is null and
1534 rlm.resource_id = (select resource_id
1535 from pa_resource_list_members
1536 where resource_list_member_id = p_filter_resource_id))) and
1537 /*
1538 (p_filter_resource_id = -1 or
1539 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1540 rlm.parent_member_id = p_filter_resource_id) or
1541 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1542 rlm.parent_member_id is null and
1543 rlm.resource_id = p_filter_resource_id)) and
1544 */
1545 (p_filter_rlm_id = -1 or
1546 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1547 rlm.parent_member_id is null and
1548 rlm.resource_id = p_filter_rlm_id and
1549 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1550 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1551 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1552 rlm.parent_member_id is not null and
1553 rlm.resource_id = p_filter_rlm_id and
1554 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1555 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1556 (p_filter_txncurrency = 'ALL' or bl.txn_currency_code = p_filter_txncurrency)
1557 group by ra.project_id,
1558 ra.task_id,
1559 ra.resource_list_member_id,
1560 bl.resource_assignment_id,
1561 bl.txn_currency_code,
1562 DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) --ra.unit_of_measure bug 3463685
1563 UNION
1564 select ra.project_id,
1565 ra.task_id,
1566 ra.resource_list_member_id,
1567 ra.resource_assignment_id,
1568 pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
1569 ftc.txn_currency_code as txn_currency_code,
1570 DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS unit_of_measure, -- ra.unit_of_measure, bug 3463685
1571 ra.total_plan_quantity as quantity,
1572 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1573 'PROJECT', ra.total_project_burdened_cost,
1574 ra.total_plan_burdened_cost) as burdened_cost, -- null
1575 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1576 'PROJECT', ra.total_project_raw_cost,
1577 ra.total_plan_raw_cost) as raw_cost, -- null
1578 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1579 'PROJECT', ra.total_project_revenue,
1580 ra.total_plan_revenue) as revenue, -- null
1581 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1582 'PROJECT', ra.total_project_revenue - ra.total_project_raw_cost,
1583 ra.total_plan_revenue - total_plan_raw_cost) as margin, -- null
1584 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1585 'PROJECT',
1586 DECODE(ra.total_project_revenue,
1587 0, 0,
1588 (ra.total_project_revenue - ra.total_project_raw_cost)/
1589 ra.total_project_revenue),
1590 DECODE(ra.total_plan_revenue,
1591 0, 0,
1592 (ra.total_plan_revenue - ra.total_plan_raw_cost)/
1593 ra.total_plan_revenue)) as margin_percent -- null
1594 from pa_resource_assignments ra,
1595 pa_resource_list_members rlm,
1596 pa_fp_txn_currencies ftc,
1597 pa_resources pr -- Added for bug 3463685
1598 where ra.budget_version_id = p_both_version_id and
1599 ra.resource_assignment_type = 'USER_ENTERED' and
1600 ra.resource_list_member_id = rlm.resource_list_member_id and
1601 (p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
1602 pr.resource_id = rlm.resource_id and -- bug 3463685
1603 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1604 (p_filter_resource_id = -1 or
1605 rlm.parent_member_id = p_filter_resource_id or
1606 (rlm.parent_member_id is null and
1607 rlm.resource_id = (select resource_id
1608 from pa_resource_list_members
1609 where resource_list_member_id = p_filter_resource_id))) and
1610 /*
1611 (p_filter_resource_id = -1 or
1612 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1613 rlm.parent_member_id = p_filter_resource_id) or
1614 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1615 rlm.parent_member_id is null and
1616 rlm.resource_id = p_filter_resource_id)) and
1617 */
1618 (p_filter_rlm_id = -1 or
1619 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1620 rlm.parent_member_id is null and
1621 rlm.resource_id = p_filter_rlm_id and
1622 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1623 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1624 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1625 rlm.parent_member_id is not null and
1626 rlm.resource_id = p_filter_rlm_id and
1627 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1628 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1629 ra.budget_version_id = ftc.fin_plan_version_id and
1630 ftc.default_all_curr_flag = 'Y' and
1631 (p_filter_txncurrency = 'ALL' or
1632 ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
1633 not exists(select bl.resource_assignment_id from pa_budget_lines bl
1634 where ra.resource_assignment_id = bl.resource_assignment_id) and
1635 p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
1636
1637 -------------------- CURSORS FOR SINGLE CURRENCY PLAN VERSIONS --------------------
1638 cursor cost_ra_csr is
1639 select ra.project_id,
1640 ra.task_id,
1641 ra.resource_list_member_id,
1642 ra.resource_assignment_id,
1643 -1 as compl_resource_assignment_id,
1644 pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
1645 p_project_currency as txn_currency_code,
1646 DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS unit_of_measure, -- ra.unit_of_measure, bug 3463685
1647 ra.total_plan_quantity as quantity,
1648 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1649 'PROJECT', ra.total_project_burdened_cost,
1650 ra.total_plan_burdened_cost) as burdened_cost,
1651 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1652 'PROJECT', ra.total_project_raw_cost,
1653 ra.total_plan_raw_cost) as raw_cost,
1654 0 as revenue,
1655 0 as margin,
1656 0 as margin_percent
1657 from pa_resource_assignments ra,
1658 pa_resource_list_members rlm,
1659 pa_resources pr -- added for bug 3463685
1660 where ra.budget_version_id = p_cost_version_id and
1661 ra.resource_assignment_type = 'USER_ENTERED' and
1662 ra.resource_list_member_id = rlm.resource_list_member_id and
1663 pr.resource_id = rlm.resource_id and -- added for bug 3463685
1664 (p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
1665 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1666 (p_filter_resource_id = -1 or
1667 rlm.parent_member_id = p_filter_resource_id or
1668 (rlm.parent_member_id is null and
1669 rlm.resource_id = (select resource_id
1670 from pa_resource_list_members
1671 where resource_list_member_id = p_filter_resource_id))) and
1672 /*
1673 (p_filter_resource_id = -1 or
1674 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1675 rlm.parent_member_id = p_filter_resource_id) or
1676 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1677 rlm.parent_member_id is null and
1678 rlm.resource_id = p_filter_resource_id)) and
1679 */
1680 (p_filter_rlm_id = -1 or
1681 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1682 rlm.parent_member_id is null and
1683 rlm.resource_id = p_filter_rlm_id and
1684 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1685 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1686 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1687 rlm.parent_member_id is not null and
1688 rlm.resource_id = p_filter_rlm_id and
1689 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1690 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1691 exists (select 1 from pa_budget_lines bl where bl.budget_version_id = ra.budget_version_id and
1692 bl.resource_assignment_id = ra.resource_assignment_id
1693 union
1694 select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
1695
1696 cursor revenue_ra_csr is
1697 select ra.project_id,
1698 ra.task_id,
1699 ra.resource_list_member_id,
1700 ra.resource_assignment_id,
1701 pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping as grouping_type,
1702 p_project_currency as txn_currency_code,
1703 decode((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS UNIT_OF_MEASURE, -- ra.unit_of_measure, bug 3463685
1704 total_plan_quantity as quantity,
1705 -- 0 as burdened_cost,
1706 -- 0 as raw_cost,
1707 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1708 'PROJECT', ra.total_project_revenue,
1709 ra.total_plan_revenue) as revenue
1710 -- 0 as margin,
1711 -- 0 as margin_percent
1712 from pa_resource_assignments ra,
1713 pa_resource_list_members rlm,
1714 pa_resources pr -- Added for bug 3463685
1715 where ra.budget_version_id = p_revenue_version_id and
1716 ra.resource_assignment_type = 'USER_ENTERED' and
1717 ra.resource_list_member_id = rlm.resource_list_member_id and
1718 pr.resource_id = rlm.resource_id and -- bug 3463685
1719 (p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
1720 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1721 (p_filter_resource_id = -1 or
1722 rlm.parent_member_id = p_filter_resource_id or
1723 (rlm.parent_member_id is null and
1724 rlm.resource_id = (select resource_id
1725 from pa_resource_list_members
1726 where resource_list_member_id = p_filter_resource_id))) and
1727 /*
1728 (p_filter_resource_id = -1 or
1729 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and
1730 rlm.parent_member_id = p_filter_resource_id) or
1731 (pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and
1732 rlm.parent_member_id is null and
1733 rlm.resource_id = p_filter_resource_id)) and
1734 */
1735 (p_filter_rlm_id = -1 or
1736 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1737 rlm.parent_member_id is null and
1738 rlm.resource_id = p_filter_rlm_id and
1739 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1740 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1741 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1742 rlm.parent_member_id is not null and
1743 rlm.resource_id = p_filter_rlm_id and
1744 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1745 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1746 exists (select 1 from pa_budget_lines bl where bl.budget_version_id = ra.budget_version_id and
1747 bl.resource_assignment_id = ra.resource_assignment_id
1748 union
1749 select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
1750
1751 cursor all_ra_csr is
1752 select ra.project_id,
1753 ra.task_id,
1754 ra.resource_list_member_id,
1755 ra.resource_assignment_id,
1756 pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
1757 p_project_currency as txn_currency_code,
1758 decode((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS UNIT_OF_MEASURE, -- ra.unit_of_measure, bug 3463685
1759 ra.total_plan_quantity as quantity,
1760 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1761 'PROJECT', ra.total_project_burdened_cost,
1762 ra.total_plan_burdened_cost) as burdened_cost,
1763 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1764 'PROJECT', ra.total_project_raw_cost,
1765 ra.total_plan_raw_cost) as raw_cost,
1766 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1767 'PROJECT', ra.total_project_revenue,
1768 ra.total_plan_revenue) as revenue,
1769 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1770 'PROJECT',
1771 DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
1772 'R', ra.total_project_revenue - ra.total_project_raw_cost,
1773 ra.total_project_revenue - ra.total_project_burdened_cost),
1774 DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
1775 'R', ra.total_plan_revenue - ra.total_plan_raw_cost,
1776 ra.total_plan_revenue - ra.total_plan_burdened_cost)) as margin,
1777 DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
1778 'PROJECT',
1779 DECODE(ra.total_project_revenue,
1780 null, null,
1781 0, 0,
1782 DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
1783 'R', (ra.total_project_revenue - ra.total_project_raw_cost)/
1784 ra.total_project_revenue,
1785 (ra.total_project_revenue - ra.total_project_burdened_cost)/
1786 ra.total_project_revenue)),
1787 DECODE(ra.total_project_revenue,
1788 null, null,
1789 0, 0,
1790 DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
1791 'R', (ra.total_plan_revenue - ra.total_plan_raw_cost)/
1792 ra.total_plan_revenue,
1793 (ra.total_plan_revenue - ra.total_plan_burdened_cost)/
1794 ra.total_plan_revenue))) as margin_percent
1795 from pa_resource_assignments ra,
1796 pa_resource_list_members rlm,
1797 pa_resources pr -- Added for bug 3463685
1798 where ra.budget_version_id = p_both_version_id and
1799 ra.resource_assignment_type = 'USER_ENTERED' and
1800 ra.resource_list_member_id = rlm.resource_list_member_id and
1801 pr.resource_id = rlm.resource_id and -- added for bug 3463685
1802 (p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
1803 -- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
1804 (p_filter_resource_id = -1 or
1805 rlm.parent_member_id = p_filter_resource_id or
1806 (rlm.parent_member_id is null and
1807 rlm.resource_id = (select resource_id
1808 from pa_resource_list_members
1809 where resource_list_member_id = p_filter_resource_id))) and
1810 /*
1811 (p_filter_resource_id = -1 or
1812 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1813 rlm.parent_member_id = p_filter_resource_id) or
1814 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1815 rlm.parent_member_id is null and
1816 rlm.resource_id = p_filter_resource_id)) and
1817 */
1818 (p_filter_rlm_id = -1 or
1819 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
1820 rlm.parent_member_id is null and
1821 rlm.resource_id = p_filter_rlm_id and
1822 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1823 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
1824 (pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
1825 rlm.parent_member_id is not null and
1826 rlm.resource_id = p_filter_rlm_id and
1827 (rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
1828 rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
1829 exists (select 1 from pa_budget_lines bl where bl.budget_version_id = ra.budget_version_id and
1830 bl.resource_assignment_id = ra.resource_assignment_id
1831 union
1832 select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
1833
1834
1835 -- PL/SQL tables
1836
1837 l_c_project_id_tab pa_fp_view_plans_txn_pub.vptxn_project_id_tab;
1838 l_c_task_id_tab pa_fp_view_plans_txn_pub.vptxn_task_id_tab;
1839 l_c_res_list_member_id_tab pa_fp_view_plans_txn_pub.vptxn_res_list_member_id_tab;
1840 l_c_res_assignment_id_tab pa_fp_view_plans_txn_pub.vptxn_res_assignment_id_tab;
1841 l_cr_res_assignment_id_tab pa_fp_view_plans_txn_pub.vptxn_res_assignment_id_tab; -- for compl res_asignment_id
1842 l_c_grouping_tab pa_fp_view_plans_txn_pub.vptxn_grouping_type_tab;
1843 l_c_txn_currency_code_tab pa_fp_view_plans_txn_pub.vptxn_txn_currency_code_tab;
1844 l_c_unit_of_measure_tab pa_fp_view_plans_txn_pub.vptxn_unit_of_measure_tab;
1845 l_c_quantity_tab pa_fp_view_plans_txn_pub.vptxn_quantity_tab;
1846 l_c_revenue_tab pa_fp_view_plans_txn_pub.vptxn_txn_revenue_tab;
1847 l_c_burdened_cost_tab pa_fp_view_plans_txn_pub.vptxn_txn_burdened_cost_tab;
1848 l_c_raw_cost_tab pa_fp_view_plans_txn_pub.vptxn_txn_raw_cost_tab;
1849 l_c_margin_tab pa_fp_view_plans_txn_pub.vptxn_txn_raw_cost_tab;
1850 l_c_margin_pct_tab pa_fp_view_plans_txn_pub.vptxn_txn_raw_cost_tab;
1851
1852 l_r_project_id_tab pa_fp_view_plans_txn_pub.vptxn_project_id_tab;
1853 l_r_task_id_tab pa_fp_view_plans_txn_pub.vptxn_task_id_tab;
1854 l_r_res_list_member_id_tab pa_fp_view_plans_txn_pub.vptxn_res_list_member_id_tab;
1855 l_r_res_assignment_id_tab pa_fp_view_plans_txn_pub.vptxn_res_assignment_id_tab;
1856 l_r_grouping_tab pa_fp_view_plans_txn_pub.vptxn_grouping_type_tab;
1857 l_r_txn_currency_code_tab pa_fp_view_plans_txn_pub.vptxn_txn_currency_code_tab;
1858 l_r_unit_of_measure_tab pa_fp_view_plans_txn_pub.vptxn_unit_of_measure_tab;
1859 l_r_quantity_tab pa_fp_view_plans_txn_pub.vptxn_quantity_tab;
1860 l_r_revenue_tab pa_fp_view_plans_txn_pub.vptxn_txn_revenue_tab;
1861
1862 l_cost_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
1863 l_rev_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
1864 l_both_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
1865 l_rec_counter NUMBER;
1866 l_compl_found BOOLEAN;
1867
1868 BEGIN
1869 --hr_utility.trace_on(null, 'dlai');
1870 FND_MSG_PUB.initialize;
1871 IF P_PA_DEBUG_MODE = 'Y' THEN
1872 pa_debug.init_err_stack('nonhgrid_view_initialize: ' || 'PA_FIN_PLAN_PUB.Submit_Current_Working');
1873 END IF;
1874 x_return_status := FND_API.G_RET_STS_SUCCESS;
1875 x_msg_count := 0;
1876 SAVEPOINT PA_FIN_PLAN_VIEWPLANS_TXN;
1877 -- flush out any existing data in PA_FP_TXN_LINES_TMP
1878 delete from PA_FP_TXN_LINES_TMP where project_id is not null;
1879
1880 -- SINGLE BUDGET VERSION: both cost and revenue
1881 if p_get_display_from = 'ANY' then
1882 --hr_utility.trace('p_get_display_from = ANY');
1883 -- test to see if version is planned in multicurrency
1884 select nvl(plan_in_multi_curr_flag, 'N')
1885 into l_rev_multi_curr_flag
1886 from pa_proj_fp_options
1887 where project_id = p_project_id and
1888 fin_plan_version_id = p_both_version_id and
1889 fin_plan_option_level_code = 'PLAN_VERSION';
1890 if l_rev_multi_curr_flag = 'Y' then
1891 open all_csr;
1892 fetch all_csr bulk collect into
1893 l_c_project_id_tab,
1894 l_c_task_id_tab,
1895 l_c_res_list_member_id_tab,
1896 l_c_res_assignment_id_tab,
1897 l_c_grouping_tab,
1898 l_c_txn_currency_code_tab,
1899 l_c_unit_of_measure_tab,
1900 l_c_quantity_tab,
1901 l_c_burdened_cost_tab,
1902 l_c_raw_cost_tab,
1903 l_c_revenue_tab,
1904 l_c_margin_tab,
1905 l_c_margin_pct_tab;
1906 close all_csr;
1907 else
1908 open all_ra_csr;
1909 fetch all_ra_csr bulk collect into
1910 l_c_project_id_tab,
1911 l_c_task_id_tab,
1912 l_c_res_list_member_id_tab,
1913 l_c_res_assignment_id_tab,
1914 l_c_grouping_tab,
1915 l_c_txn_currency_code_tab,
1916 l_c_unit_of_measure_tab,
1917 l_c_quantity_tab,
1918 l_c_burdened_cost_tab,
1919 l_c_raw_cost_tab,
1920 l_c_revenue_tab,
1921 l_c_margin_tab,
1922 l_c_margin_pct_tab;
1923 close all_ra_csr;
1924 end if; -- multi_curr_flag for ANY
1925 forall c in nvl(l_c_project_id_tab.first,0)..nvl(l_c_project_id_tab.last,-1)
1926 insert into PA_FP_TXN_LINES_TMP
1927 (project_id,
1928 task_id,
1929 resource_list_member_id,
1930 cost_resource_assignment_id,
1931 rev_resource_assignment_id,
1932 all_resource_assignment_id,
1933 grouping_type,
1934 txn_currency_code,
1935 unit_of_measure,
1936 quantity,
1937 revenue,
1938 burdened_cost,
1939 raw_cost,
1940 margin,
1941 margin_pct) values
1942 (l_c_project_id_tab(c),
1943 l_c_task_id_tab(c),
1944 l_c_res_list_member_id_tab(c),
1945 -1, -- cost_resource_assignment_id
1946 -1, -- rev_resource_assignment_id
1947 l_c_res_assignment_id_tab(c), -- all_resource_assignment_id
1948 l_c_grouping_tab(c),
1949 l_c_txn_currency_code_tab(c),
1950 l_c_unit_of_measure_tab(c),
1951 l_c_quantity_tab(c), -- always display the quantity from the version
1952 l_c_revenue_tab(c),
1953 l_c_burdened_cost_tab(c),
1954 l_c_raw_cost_tab(c),
1955 l_c_margin_tab(c),
1956 l_c_margin_pct_tab(c));
1957
1958 -- SINGLE BUDGET VERSION: cost only
1959 elsif p_get_display_from = 'COST' then
1960 --hr_utility.trace('p_get_display_from = COST');
1961 -- test to see if version is planned in multicurrency
1962 select nvl(plan_in_multi_curr_flag, 'N')
1963 into l_cost_multi_curr_flag
1964 from pa_proj_fp_options
1965 where project_id = p_project_id and
1966 fin_plan_version_id = p_cost_version_id and
1967 fin_plan_option_level_code = 'PLAN_VERSION';
1968 if l_cost_multi_curr_flag = 'Y' then
1969 open cost_csr;
1970 fetch cost_csr bulk collect into
1971 l_c_project_id_tab,
1972 l_c_task_id_tab,
1973 l_c_res_list_member_id_tab,
1974 l_c_res_assignment_id_tab,
1975 l_cr_res_assignment_id_tab,
1976 l_c_grouping_tab,
1977 l_c_txn_currency_code_tab,
1978 l_c_unit_of_measure_tab,
1979 l_c_quantity_tab,
1980 l_c_burdened_cost_tab,
1981 l_c_raw_cost_tab,
1982 l_c_revenue_tab,
1983 l_c_margin_tab,
1984 l_c_margin_pct_tab;
1985 close cost_csr;
1986 else
1987 open cost_ra_csr;
1988 fetch cost_ra_csr bulk collect into
1989 l_c_project_id_tab,
1990 l_c_task_id_tab,
1991 l_c_res_list_member_id_tab,
1992 l_c_res_assignment_id_tab,
1993 l_cr_res_assignment_id_tab,
1994 l_c_grouping_tab,
1995 l_c_txn_currency_code_tab,
1996 l_c_unit_of_measure_tab,
1997 l_c_quantity_tab,
1998 l_c_burdened_cost_tab,
1999 l_c_raw_cost_tab,
2000 l_c_revenue_tab,
2001 l_c_margin_tab,
2002 l_c_margin_pct_tab;
2003 close cost_ra_csr;
2004 end if; -- multicurrency test for COST
2005 forall c in nvl(l_c_project_id_tab.first,0)..nvl(l_c_project_id_tab.last,-1)
2006 insert into PA_FP_TXN_LINES_TMP
2007 (project_id,
2008 task_id,
2009 resource_list_member_id,
2010 cost_resource_assignment_id,
2011 rev_resource_assignment_id,
2012 all_resource_assignment_id,
2013 grouping_type,
2014 txn_currency_code,
2015 unit_of_measure,
2016 quantity,
2017 revenue,
2018 burdened_cost,
2019 raw_cost,
2020 margin,
2021 margin_pct) values
2022 (l_c_project_id_tab(c),
2023 l_c_task_id_tab(c),
2024 l_c_res_list_member_id_tab(c),
2025 l_c_res_assignment_id_tab(c), -- cost_resource_assignment_id
2026 l_cr_res_assignment_id_tab(c), -- revenue_resource_assignment_id = -1
2027 -1, -- all_resource_assignment_id
2028 l_c_grouping_tab(c),
2029 l_c_txn_currency_code_tab(c),
2030 l_c_unit_of_measure_tab(c),
2031 l_c_quantity_tab(c), -- always display the quantity from the version
2032 null, -- null for revenue
2033 l_c_burdened_cost_tab(c),
2034 l_c_raw_cost_tab(c),
2035 null, -- null for margin
2036 null); -- null for margin_pct
2037
2038 -- SINGLE BUDGET VERSION: revenue only
2039 elsif p_get_display_from = 'REVENUE' then
2040 --hr_utility.trace('p_get_display_from = REVENUE');
2041 -- test to see if version is planned in multicurrency
2042 select nvl(plan_in_multi_curr_flag, 'N')
2043 into l_rev_multi_curr_flag
2044 from pa_proj_fp_options
2045 where project_id = p_project_id and
2046 fin_plan_version_id = p_revenue_version_id and
2047 fin_plan_option_level_code = 'PLAN_VERSION';
2048 if l_rev_multi_curr_flag = 'Y' then
2049 open revenue_csr;
2050 fetch revenue_csr bulk collect into
2051 l_r_project_id_tab,
2052 l_r_task_id_tab,
2053 l_r_res_list_member_id_tab,
2054 l_r_res_assignment_id_tab,
2055 l_r_grouping_tab,
2056 l_r_txn_currency_code_tab,
2057 l_r_unit_of_measure_tab,
2058 l_r_quantity_tab,
2059 l_r_revenue_tab;
2060 close revenue_csr;
2061 else
2062 open revenue_ra_csr;
2063 fetch revenue_ra_csr bulk collect into
2064 l_r_project_id_tab,
2065 l_r_task_id_tab,
2066 l_r_res_list_member_id_tab,
2067 l_r_res_assignment_id_tab,
2068 l_r_grouping_tab,
2069 l_r_txn_currency_code_tab,
2070 l_r_unit_of_measure_tab,
2071 l_r_quantity_tab,
2072 l_r_revenue_tab;
2073 close revenue_ra_csr;
2074 end if; -- multicurrency test for REVENUE
2075 forall r in nvl(l_r_project_id_tab.first,0)..nvl(l_r_project_id_tab.last,-1)
2076 insert into PA_FP_TXN_LINES_TMP
2077 (project_id,
2078 task_id,
2079 resource_list_member_id,
2080 cost_resource_assignment_id,
2081 rev_resource_assignment_id,
2082 all_resource_assignment_id,
2083 grouping_type,
2084 txn_currency_code,
2085 unit_of_measure,
2086 quantity,
2087 revenue,
2088 burdened_cost,
2089 raw_cost,
2090 margin,
2091 margin_pct) values
2092 (l_r_project_id_tab(r),
2093 l_r_task_id_tab(r),
2094 l_r_res_list_member_id_tab(r),
2095 -1, -- cost_resource_assignment_id
2096 l_r_res_assignment_id_tab(r), -- rev_resource_assignment_id
2097 -1, -- all_resource_assignment_id
2098 l_r_grouping_tab(r),
2099 l_r_txn_currency_code_tab(r),
2100 l_r_unit_of_measure_tab(r),
2101 l_r_quantity_tab(r), -- always display the quantity from the version
2102 l_r_revenue_tab(r),
2103 null, -- null for burdened_cost
2104 null, -- null for raw cost
2105 null, -- null for margin
2106 null); -- null for margin_pct
2107 -- TWO BUDGET VERSIONS
2108 else
2109 --hr_utility.trace('p_get_display_from = BOTH');
2110 -- test to see if COST version is planned in multicurrency
2111 select nvl(plan_in_multi_curr_flag, 'N')
2112 into l_cost_multi_curr_flag
2113 from pa_proj_fp_options
2114 where project_id = p_project_id and
2115 fin_plan_version_id = p_cost_version_id and
2116 fin_plan_option_level_code = 'PLAN_VERSION';
2117 --hr_utility.trace('l_cost_multi_curr_flag= ' || l_cost_multi_curr_flag);
2118 if l_cost_multi_curr_flag = 'Y' then
2119 open cost_csr;
2120 fetch cost_csr bulk collect into
2121 l_c_project_id_tab,
2122 l_c_task_id_tab,
2123 l_c_res_list_member_id_tab,
2124 l_c_res_assignment_id_tab,
2125 l_cr_res_assignment_id_tab,
2126 l_c_grouping_tab,
2127 l_c_txn_currency_code_tab,
2128 l_c_unit_of_measure_tab,
2129 l_c_quantity_tab,
2130 l_c_burdened_cost_tab,
2131 l_c_raw_cost_tab,
2132 l_c_revenue_tab,
2133 l_c_margin_tab,
2134 l_c_margin_pct_tab;
2135 close cost_csr;
2136 --hr_utility.trace('opened/closed cost_csr');
2137 else
2138 open cost_ra_csr;
2139 fetch cost_ra_csr bulk collect into
2140 l_c_project_id_tab,
2141 l_c_task_id_tab,
2142 l_c_res_list_member_id_tab,
2143 l_c_res_assignment_id_tab,
2144 l_cr_res_assignment_id_tab,
2145 l_c_grouping_tab,
2146 l_c_txn_currency_code_tab,
2147 l_c_unit_of_measure_tab,
2148 l_c_quantity_tab,
2149 l_c_burdened_cost_tab,
2150 l_c_raw_cost_tab,
2151 l_c_revenue_tab,
2152 l_c_margin_tab,
2153 l_c_margin_pct_tab;
2154 close cost_ra_csr;
2155 --hr_utility.trace('opened/closed cost_ra_csr');
2156 end if; -- multicurrency test for COST
2157 -- test to see if REVENUE version is planned in multicurrency
2158 select nvl(plan_in_multi_curr_flag, 'N')
2159 into l_rev_multi_curr_flag
2160 from pa_proj_fp_options
2161 where project_id = p_project_id and
2162 fin_plan_version_id = p_revenue_version_id and
2163 fin_plan_option_level_code = 'PLAN_VERSION';
2164 --hr_utility.trace('l_rev_multi_curr_flag= ' || l_rev_multi_curr_flag);
2165 if l_rev_multi_curr_flag = 'Y' then
2166 open revenue_csr;
2167 fetch revenue_csr bulk collect into
2168 l_r_project_id_tab,
2169 l_r_task_id_tab,
2170 l_r_res_list_member_id_tab,
2171 l_r_res_assignment_id_tab,
2172 l_r_grouping_tab,
2173 l_r_txn_currency_code_tab,
2174 l_r_unit_of_measure_tab,
2175 l_r_quantity_tab,
2176 l_r_revenue_tab;
2177 close revenue_csr;
2178 --hr_utility.trace('opened/closed revenue_csr');
2179 else
2180 open revenue_ra_csr;
2181 fetch revenue_ra_csr bulk collect into
2182 l_r_project_id_tab,
2183 l_r_task_id_tab,
2184 l_r_res_list_member_id_tab,
2185 l_r_res_assignment_id_tab,
2186 l_r_grouping_tab,
2187 l_r_txn_currency_code_tab,
2188 l_r_unit_of_measure_tab,
2189 l_r_quantity_tab,
2190 l_r_revenue_tab;
2191 close revenue_ra_csr;
2192 --hr_utility.trace('opened/closed revenue_ra_csr');
2193 end if; -- multicurrency test for REVENUE
2194 -- walk through cost table, and look for a match for each row
2195 for i in nvl(l_c_project_id_tab.first,0)..nvl(l_c_project_id_tab.last,-1) loop
2196 l_compl_found := false;
2197 --hr_utility.trace('outer i= ' || to_char(i));
2198 -- look for a row in revenue tables to complement current cost row
2199 --hr_utility.trace('j size= ' || to_char(l_r_project_id_tab.last));
2200 for j in nvl(l_r_project_id_tab.first,0)..nvl(l_r_project_id_tab.last,-1) loop
2201 ----hr_utility.trace('j = ' || to_char(j));
2202 if (l_r_project_id_tab(j) = l_c_project_id_tab(i)) and
2203 (l_r_task_id_tab(j) = l_c_task_id_tab(i)) and
2204 (l_r_res_list_member_id_tab(j) = l_c_res_list_member_id_tab(i)) and
2205 (l_r_txn_currency_code_tab(j) = l_c_txn_currency_code_tab(i)) and
2206 not l_compl_found then
2207 -- match has been found
2208 --hr_utility.trace('found match');
2209 --hr_utility.trace('jvalue= ' || to_char(j));
2210 l_compl_found := true;
2211 l_c_revenue_tab(i) := l_r_revenue_tab(j);
2212 l_cr_res_assignment_id_tab(i) := l_r_res_assignment_id_tab(j);
2213 -- use Get_Derive_Margin_From_Code to calculate margin
2214 if pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code = 'R' then
2215 l_c_margin_tab(i) := l_c_revenue_tab(i) - l_c_raw_cost_tab(i);
2216 else
2217 l_c_margin_tab(i) := l_c_revenue_tab(i) - l_c_burdened_cost_tab(i);
2218 end if;
2219 -- divide by zero special case
2220 if l_r_revenue_tab(j) = 0 then
2221 l_c_margin_pct_tab(i) := 0;
2222 else
2223 l_c_margin_pct_tab(i) := l_c_margin_tab(i) / l_c_revenue_tab(i);
2224 end if;
2225 -- stamp QUANTITY and UNIT OF MEASURE based on the correct source
2226 if pa_fp_view_plans_txn_pub.Get_Report_Labor_Hrs_From_Code = 'REVENUE' then
2227 l_c_quantity_tab(i) := l_r_quantity_tab(j);
2228 l_c_unit_of_measure_tab(i) := l_r_unit_of_measure_tab(j);
2229 end if;
2230 /*
2231 -- finally, delete the revenue row from the revenue table
2232 l_r_project_id_tab.delete(j);
2233 l_r_task_id_tab.delete(j);
2234 l_r_res_list_member_id_tab.delete(j);
2235 l_r_res_assignment_id_tab.delete(j);
2236 l_r_txn_currency_code_tab.delete(j);
2237 l_r_unit_of_measure_tab.delete(j);
2238 l_r_quantity_tab.delete(j);
2239 l_r_revenue_tab.delete(j);
2240 */
2241 end if; -- if complement found
2242
2243 -- the following is part of the regular loop processing, because we need to start
2244 -- deleting IMMEDIATELY after the match has been found.
2245 if l_compl_found then
2246 -- WHEN WE DELETE ROW, WE NEED TO SHIFT ALL THE OTHER ROWS UP
2247 if j=l_r_project_id_tab.last then
2248 -- We've reached the last row; delete it
2249 l_r_project_id_tab.delete(j);
2250 l_r_task_id_tab.delete(j);
2251 l_r_res_list_member_id_tab.delete(j);
2252 l_r_res_assignment_id_tab.delete(j);
2253 l_r_txn_currency_code_tab.delete(j);
2254 l_r_unit_of_measure_tab.delete(j);
2255 l_r_quantity_tab.delete(j);
2256 l_r_revenue_tab.delete(j);
2257 --hr_utility.trace('deleted the last row');
2258 else
2259 -- shift up all rows after deleted row
2260 l_r_project_id_tab(j) := l_r_project_id_tab(j+1);
2261 l_r_task_id_tab(j) := l_r_task_id_tab(j+1);
2262 l_r_res_list_member_id_tab(j) := l_r_res_list_member_id_tab(j+1);
2263 l_r_res_assignment_id_tab(j) := l_r_res_assignment_id_tab(j+1);
2264 l_r_txn_currency_code_tab(j) := l_r_txn_currency_code_tab(j+1);
2265 l_r_unit_of_measure_tab(j) := l_r_unit_of_measure_tab(j+1);
2266 l_r_quantity_tab(j) := l_r_quantity_tab(j+1);
2267 l_r_revenue_tab(j) := l_r_revenue_tab(j+1);
2268 --hr_utility.trace('shifting to row ' || to_char(j));
2269 end if;
2270 end if;
2271
2272 end loop; -- inner search loop
2273 -- IF COMPLEMENT NOT FOUND, null out the revenue, margin values
2274 if l_cr_res_assignment_id_tab(i) = -1 then
2275 l_c_revenue_tab(i) := null;
2276 l_c_margin_tab(i) := null;
2277 l_c_margin_pct_tab(i) := null;
2278 end if; -- complement not found
2279 end loop; -- outer loop: cost table
2280 --hr_utility.trace('finished first pass of match-making');
2281 -- now walk through revenue table, and add the unmatched rows to the cost table
2282 l_rec_counter := nvl(l_c_project_id_tab.last,0); --Bug 2730209
2283 for k in nvl(l_r_project_id_tab.first,0)..nvl(l_r_project_id_tab.last,-1) loop
2284 l_rec_counter := l_rec_counter + 1;
2285 l_c_project_id_tab(l_rec_counter) := l_r_project_id_tab(k);
2286 l_c_task_id_tab(l_rec_counter) := l_r_task_id_tab(k);
2287 l_c_res_list_member_id_tab(l_rec_counter) := l_r_res_list_member_id_tab(k);
2288 l_c_res_assignment_id_tab(l_rec_counter) := -1; -- cost res_ass_id
2289 l_cr_res_assignment_id_tab(l_rec_counter) := l_r_res_assignment_id_tab(k); -- revenue res_ass_id
2290 l_c_grouping_tab(l_rec_counter) := l_r_grouping_tab(k);
2291 l_c_txn_currency_code_tab(l_rec_counter) := l_r_txn_currency_code_tab(k);
2292 l_c_unit_of_measure_tab(l_rec_counter) := l_r_unit_of_measure_tab(k);
2293 l_c_quantity_tab(l_rec_counter) := l_r_quantity_tab(k);
2294 l_c_burdened_cost_tab(l_rec_counter) := null;
2295 l_c_raw_cost_tab(l_rec_counter) := null;
2296 l_c_revenue_tab(l_rec_counter) := l_r_revenue_tab(k);
2297 l_c_margin_tab(l_rec_counter) := null;
2298 l_c_margin_pct_tab(l_rec_counter) := null;
2299 end loop; -- revenue table loop
2300 --hr_utility.trace('finished 2nd pass');
2301 -- FINALLY populate the global temporary table
2302 forall c in nvl(l_c_project_id_tab.first,0)..nvl(l_c_project_id_tab.last,-1)
2303 insert into PA_FP_TXN_LINES_TMP
2304 (project_id,
2305 task_id,
2306 resource_list_member_id,
2307 cost_resource_assignment_id,
2308 rev_resource_assignment_id,
2309 all_resource_assignment_id,
2310 grouping_type,
2311 txn_currency_code,
2312 unit_of_measure,
2313 quantity,
2314 revenue,
2315 burdened_cost,
2316 raw_cost,
2317 margin,
2318 margin_pct) values
2319 (l_c_project_id_tab(c),
2320 l_c_task_id_tab(c),
2321 l_c_res_list_member_id_tab(c),
2322 l_c_res_assignment_id_tab(c), -- cost_res_assignment_id
2323 l_cr_res_assignment_id_tab(c), -- rev_res_assignment_id
2324 -1, -- all_res_assignment_id
2325 l_c_grouping_tab(c),
2326 l_c_txn_currency_code_tab(c),
2327 l_c_unit_of_measure_tab(c),
2328 l_c_quantity_tab(c),
2329 l_c_revenue_tab(c), -- null values already present where needed
2330 l_c_burdened_cost_tab(c), -- null values already present where needed
2331 l_c_raw_cost_tab(c), -- null values already present where needed
2332 l_c_margin_tab(c), -- null values already present where needed
2333 l_c_margin_pct_tab(c)); -- null values already present where needed
2334 end if; -- check bvId's
2335 commit;
2336
2337
2338 pa_debug.reset_err_stack;
2339
2340 exception
2341 when others then
2342 rollback to PA_FIN_PLAN_VIEWPLANS_TXN;
2343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2344 x_msg_count := 1;
2345 x_msg_data := SQLERRM;
2346 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fp_view_plans_txn_pub',
2347 p_procedure_name => 'view_plans_txn_populate_tmp');
2348 pa_debug.reset_err_stack;
2349 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2350 END view_plans_txn_populate_tmp;
2351
2352 ---------------------------------------------------------------
2353 ----------- BEGIN OF CHANGE ORDER / CONTROL ITEM -------------
2354 ---------------------------------------------------------------
2355 -- CHANGE HISTORY:
2356 -- 10/28/02: make sure resource list global variables are populated
2357 -- for resource query to work
2358 -- 11/08/02: x_project_currency = project or projfunc currency, depending if
2359 -- plan type = AR
2360 -- populate G_DISPLAY_CURRENCY_TYPE
2361 -- 05/30/03: x_project_currency can be AGREEMENT CURRENCY if ci_id is not null
2362 -- 07/30/03: changed logic for populating x_planned_resources_flag and x_grouping_type
2363 -- BUG 2813661
2364 procedure nonhgrid_view_initialize_ci
2365 (p_project_id IN pa_budget_versions.project_id%TYPE,
2366 p_ci_id IN pa_budget_versions.ci_id%TYPE,
2367 p_user_id IN NUMBER,
2368 x_budget_status_code OUT NOCOPY pa_budget_versions.budget_status_code%TYPE, --File.Sql.39 bug 4440895
2369 x_cost_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
2370 x_rev_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
2371 x_cost_rl_id OUT NOCOPY pa_budget_versions.resource_list_id%TYPE, --File.Sql.39 bug 4440895
2372 x_rev_rl_id OUT NOCOPY pa_budget_versions.resource_list_id%TYPE, --File.Sql.39 bug 4440895
2373 x_display_from OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2374 x_planned_resources_flag OUT NOCOPY VARCHAR2, -- valid values: 'Y', 'N' --File.Sql.39 bug 4440895
2375 x_grouping_type OUT NOCOPY VARCHAR2, -- valid values: 'GROUPED', 'NONGROUPED', 'MIXED' --File.Sql.39 bug 4440895
2376 x_planning_level OUT NOCOPY VARCHAR2, -- valid values: 'P', 'T', 'L', 'M' --File.Sql.39 bug 4440895
2377 x_multicurrency_flag OUT NOCOPY VARCHAR2, -- valid values: 'Y', 'N' --File.Sql.39 bug 4440895
2378 x_plan_type_name OUT NOCOPY pa_fin_plan_types_tl.name%TYPE, --File.Sql.39 bug 4440895
2379 x_project_currency OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
2380 x_labor_hrs_from_code OUT NOCOPY pa_proj_fp_options.report_labor_hrs_from_code%TYPE, --File.Sql.39 bug 4440895
2381 x_cost_rv_number OUT NOCOPY pa_budget_versions.record_version_number%TYPE, --File.Sql.39 bug 4440895
2382 x_rev_rv_number OUT NOCOPY pa_budget_versions.record_version_number%TYPE, --File.Sql.39 bug 4440895
2383 x_cost_locked_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2384 x_rev_locked_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2385 x_ar_ac_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2386 x_plan_type_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE, --File.Sql.39 bug 4440895
2387 x_fin_plan_type_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
2388 x_auto_baselined_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2389 x_display_res_flag OUT NOCOPY VARCHAR2, -- bug 3081511 --File.Sql.39 bug 4440895
2390 x_display_resgp_flag OUT NOCOPY VARCHAR2, -- bug 3081511 --File.Sql.39 bug 4440895
2391 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2392 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2393 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2394 ) is
2395
2396 l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
2397 l_fin_plan_type_id2 pa_proj_fp_options.fin_plan_type_id%TYPE;
2398 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2399 l_proj_fp_options_id2 pa_proj_fp_options.proj_fp_options_id%TYPE;
2400 l_working_or_baselined VARCHAR2(30);
2401 l_cost_or_revenue VARCHAR2(30);
2402 l_ar_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
2403 l_ac_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
2404
2405 cursor ci_csr is
2406 select bv.budget_version_id,
2407 po.proj_fp_options_id,
2408 NVL(po.plan_in_multi_curr_flag, 'N') as plan_in_multi_curr_flag
2409 from pa_budget_versions bv,
2410 pa_proj_fp_options po
2411 where bv.project_id = p_project_id and
2412 bv.ci_id = p_ci_id and
2413 bv.budget_version_id = po.fin_plan_version_id and
2414 po.fin_plan_option_level_code='PLAN_VERSION';
2415 ci_rec ci_csr%ROWTYPE;
2416
2417
2418 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
2419 l_report_labor_hrs_from_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE;
2420 l_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
2421 l_margin_derived_code pa_proj_fp_options.margin_derived_from_code%TYPE;
2422 l_grouping_type VARCHAR2(30);
2423 l_compl_grouping_type VARCHAR2(30);
2424 l_cost_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2425 l_rev_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2426 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
2427 l_compl_resource_list_id pa_budget_versions.resource_list_id%TYPE;
2428 l_rv_number pa_budget_versions.record_version_number%TYPE;
2429 l_compl_rv_number pa_budget_versions.record_version_number%TYPE;
2430 l_uncategorized_flag pa_resource_lists.uncategorized_flag%TYPE;
2431 l_compl_uncategorized_flag pa_resource_lists.uncategorized_flag%TYPE;
2432 l_agreement_id pa_agreements_all.agreement_id%TYPE; -- bug 2984679
2433 l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE; -- bug 2984679
2434
2435 l_is_cost_locked_by_user VARCHAR2(1);
2436 l_is_rev_locked_by_user VARCHAR2(1);
2437 l_cost_locked_by_person_id NUMBER;
2438 l_rev_locked_by_person_id NUMBER;
2439 l_resource_level VARCHAR2(1); -- bug 2813661
2440 l_cost_resource_level VARCHAR2(1); -- bug 2813661
2441 l_revenue_resource_level VARCHAR2(1); -- bug 2813661
2442
2443 l_ci_row_index NUMBER := 0;
2444 l_ci_budget_version_id pa_budget_versions.budget_version_id%TYPE;
2445
2446 -- local error handling variables
2447 l_return_status VARCHAR2(1);
2448 l_msg_count NUMBER;
2449 l_msg_data VARCHAR2(2000);
2450 l_msg_index_out NUMBER;
2451
2452 BEGIN
2453 --pa_debug.write('pa_fp_view_plans_txn_pub.nonhgrid_view_initialize', '100: entered procedure', 2);
2454 --hr_utility.trace_on(null, 'dlai');
2455 x_msg_count := 0;
2456 x_return_status := FND_API.G_RET_STS_SUCCESS;
2457
2458 -- GET AUTO BASELINED FLAG
2459 x_auto_baselined_flag :=
2460 Pa_Fp_Control_Items_Utils.IsFpAutoBaselineEnabled(p_project_id);
2461
2462 -- get PROJECT CURRENCY
2463 select project_currency_code
2464 into x_project_currency
2465 from pa_projects_all
2466 where project_id = p_project_id;
2467 x_plan_type_name := 'CI STUFF';
2468
2469 open ci_csr;
2470 loop
2471 fetch ci_csr into ci_rec;
2472 exit when ci_csr%NOTFOUND;
2473 l_ci_row_index := l_ci_row_index + 1;
2474
2475 --- >>>> PROCESSING FOR FIRST ROW <<<< ---
2476 if l_ci_row_index = 1 then
2477 l_ci_budget_version_id := ci_rec.budget_version_id;
2478 pa_fp_view_plans_txn_pub.G_SINGLE_VERSION_ID := l_ci_budget_version_id;
2479 select fin_plan_type_id,
2480 proj_fp_options_id
2481 into l_fin_plan_type_id,
2482 l_proj_fp_options_id
2483 from pa_proj_fp_options
2484 where project_id = p_project_id and
2485 fin_plan_version_id = ci_rec.budget_version_id and
2486 fin_plan_option_level_code = 'PLAN_VERSION';
2487
2488 select DECODE(rl.group_resource_type_id,
2489 0, 'NONGROUPED',
2490 'GROUPED'),
2491 nvl(bv.resource_list_id,0),
2492 nvl(bv.budget_status_code, 'W'),
2493 DECODE(bv.budget_status_code,
2494 'B', 'B',
2495 'W'),
2496 DECODE(bv.version_type,
2497 'COST', 'C',
2498 'REVENUE', 'R',
2499 'N'),
2500 bv.record_version_number,
2501 nvl(bv.approved_cost_plan_type_flag, 'N'),
2502 nvl(bv.approved_rev_plan_type_flag, 'N'),
2503 nvl(rl.uncategorized_flag, 'N'),
2504 bv.agreement_id
2505 into l_grouping_type,
2506 l_resource_list_id,
2507 x_budget_status_code,
2508 l_working_or_baselined,
2509 l_cost_or_revenue,
2510 l_rv_number,
2511 l_ac_flag,
2512 l_ar_flag,
2513 l_uncategorized_flag,
2514 l_agreement_id
2515 from pa_budget_versions bv,
2516 pa_resource_lists_all_bg rl
2517 where bv.budget_version_id = ci_rec.budget_version_id and
2518 bv.resource_list_id = rl.resource_list_id;
2519
2520 -- >>>> BUG FIX 2650878: project or projfunc, depending on AR flag <<<<
2521 if l_ar_flag = 'Y' then
2522 -- bug fix 2984679: For APPROVED REVENUE, check for agreement currency
2523 -- before using Project Functional Currency
2524 x_ar_ac_flag := 'Y';
2525
2526 if p_ci_id is not null and l_agreement_id is not null then
2527 select nvl (agreement_currency_code, 'ANY')
2528 into l_agreement_currency_code
2529 from pa_agreements_all
2530 where agreement_id = l_agreement_id;
2531 if l_agreement_currency_code <> 'ANY' then
2532 x_project_currency := l_agreement_currency_code;
2533 end if;
2534
2535 else
2536 -- get PROJECT CURRENCY
2537 select projfunc_currency_code
2538 into x_project_currency
2539 from pa_projects_all
2540 where project_id = p_project_id;
2541 pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE := 'PROJFUNC';
2542
2543 end if; -- ci_id is not null
2544
2545 else
2546 -- NOT APPROVED REVENUE: go with Project Currency
2547 x_ar_ac_flag := 'N';
2548 -- get PROJECT CURRENCY
2549 select project_currency_code
2550 into x_project_currency
2551 from pa_projects_all
2552 where project_id = p_project_id;
2553 pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE := 'PROJECT';
2554 end if; -- approved revenue flag
2555
2556 if l_uncategorized_flag = 'Y' then
2557 x_planned_resources_flag := 'N';
2558 else
2559 x_planned_resources_flag := 'Y';
2560 end if;
2561
2562 select proj_fp_options_id,
2563 fin_plan_preference_code
2564 into x_plan_type_fp_options_id,
2565 l_fp_preference_code
2566 from pa_proj_fp_options
2567 where project_id = p_project_id and
2568 fin_plan_type_id = l_fin_plan_type_id and
2569 fin_plan_option_level_code = 'PLAN_TYPE';
2570
2571 -- retrieve report_labor_hrs, margin_derived codes from PLAN TYPE entry
2572 select report_labor_hrs_from_code,
2573 margin_derived_from_code
2574 into l_report_labor_hrs_from_code,
2575 l_margin_derived_code
2576 from pa_proj_fp_options
2577 where project_id = p_project_id and
2578 fin_plan_type_id = l_fin_plan_type_id and
2579 fin_plan_option_level_code = 'PLAN_TYPE';
2580 pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE := l_report_labor_hrs_from_code;
2581 pa_fp_view_plans_txn_pub.G_PLAN_TYPE_ID := l_fin_plan_type_id;
2582 pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG := ci_rec.plan_in_multi_curr_flag;
2583
2584 pa_fp_view_plans_txn_pub.G_DERIVE_MARGIN_FROM_CODE := l_margin_derived_code;
2585
2586 if l_fp_preference_code = 'COST_AND_REV_SAME' then
2587 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := l_ci_budget_version_id;
2588 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := l_ci_budget_version_id;
2589 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
2590 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
2591 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'ANY';
2592 x_grouping_type := l_grouping_type;
2593 -- set planning level code for page: P, T, L, or M
2594 select all_fin_plan_level_code
2595 into l_cost_planning_level
2596 from pa_proj_fp_options
2597 where proj_fp_options_id = l_proj_fp_options_id;
2598 x_planning_level := l_cost_planning_level;
2599 x_cost_rv_number := l_rv_number;
2600 x_rev_rv_number := l_rv_number;
2601 x_cost_rl_id := l_resource_list_id;
2602 x_rev_rl_id := l_resource_list_id;
2603
2604 elsif l_fp_preference_code = 'COST_ONLY' then
2605 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := l_ci_budget_version_id;
2606 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := -1;
2607 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
2608 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
2609 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'COST';
2610 x_grouping_type := l_grouping_type;
2611 -- set planning level code for page: P, T, L, or M
2612 select cost_fin_plan_level_code
2613 into l_cost_planning_level
2614 from pa_proj_fp_options
2615 where proj_fp_options_id = l_proj_fp_options_id;
2616 x_planning_level := l_cost_planning_level;
2617 x_cost_rv_number := l_rv_number;
2618 x_rev_rv_number := -1;
2619 x_cost_rl_id := l_resource_list_id;
2620 x_rev_rl_id := -1;
2621
2622 elsif l_fp_preference_code = 'REVENUE_ONLY' then
2623 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := -1;
2624 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := l_ci_budget_version_id;
2625 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
2626 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
2627 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'REVENUE';
2628 x_grouping_type := l_grouping_type;
2629 -- set planning level code for page: P, T, L, or M
2630 select revenue_fin_plan_level_code
2631 into l_rev_planning_level
2632 from pa_proj_fp_options
2633 where proj_fp_options_id = l_proj_fp_options_id;
2634 x_planning_level := l_rev_planning_level;
2635 x_cost_rv_number := -1;
2636 x_rev_rv_number := l_rv_number;
2637 x_cost_rl_id := -1;
2638 x_rev_rl_id := l_resource_list_id;
2639 end if;
2640
2641 --- >>>> PROCESSING FOR SECOND ROW <<<< ---
2642 else
2643 -- what we do w/second row depends on the PLAN PREFERENCE CODE
2644 -- NOTE: if COST_AND_REV_SAME, then we will NOT get a second row
2645
2646 -- If the second record is using a different plan type, then we'll
2647 -- get a second REPORT_LABOR_HRS_FROM_CODE and
2648 -- MARGIN_DERIVED_FROM_CODE. In this case, the one
2649 -- attached to the COST plan prevails.
2650
2651 select fin_plan_type_id,
2652 proj_fp_options_id
2653 into l_fin_plan_type_id2,
2654 l_proj_fp_options_id2
2655 from pa_proj_fp_options
2656 where project_id = p_project_id and
2657 fin_plan_version_id = ci_rec.budget_version_id and
2658 fin_plan_option_level_code = 'PLAN_VERSION';
2659 select report_labor_hrs_from_code,
2660 margin_derived_from_code
2661 into l_report_labor_hrs_from_code,
2662 l_margin_derived_code
2663 from pa_proj_fp_options
2664 where project_id = p_project_id and
2665 fin_plan_type_id = l_fin_plan_type_id2 and
2666 fin_plan_option_level_code = 'PLAN_TYPE';
2667
2668 if l_fp_preference_code = 'COST_ONLY' then
2669 -- this second row must be the complementary REVENUE version
2670 select DECODE(rl.group_resource_type_id,
2671 0, 'NONGROUPED',
2672 'GROUPED'),
2673 rl.resource_list_id,
2674 bv.record_version_number,
2675 nvl(rl.uncategorized_flag, 'N')
2676 into l_compl_grouping_type,
2677 l_compl_resource_list_id,
2678 l_compl_rv_number,
2679 l_compl_uncategorized_flag
2680 from pa_budget_versions bv,
2681 pa_resource_lists_all_bg rl
2682 where bv.budget_version_id = ci_rec.budget_version_id and
2683 bv.resource_list_id = rl.resource_list_id;
2684 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := l_ci_budget_version_id;
2685 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := ci_rec.budget_version_id;
2686 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
2687 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_compl_grouping_type;
2688 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'BOTH';
2689 if l_grouping_type = 'GROUPED' then
2690 if l_compl_grouping_type = 'GROUPED' then
2691 x_grouping_type := 'GROUPED';
2692 else
2693 x_grouping_type := 'MIXED';
2694 end if;
2695 else
2696 if l_compl_grouping_type = 'GROUPED' then
2697 x_grouping_type := 'MIXED';
2698 else
2699 x_grouping_type := 'NONGROUPED';
2700 end if;
2701 end if;
2702 x_cost_rv_number := l_rv_number;
2703 x_rev_rv_number := l_compl_rv_number;
2704 x_cost_rl_id := l_resource_list_id;
2705 x_rev_rl_id := l_compl_resource_list_id;
2706 -- planning level code for cost version: P, T, L, or M
2707 select cost_fin_plan_level_code
2708 into l_cost_planning_level
2709 from pa_proj_fp_options
2710 where proj_fp_options_id = l_proj_fp_options_id;
2711 -- planning level code for revenue (compl) version
2712 select revenue_fin_plan_level_code
2713 into l_rev_planning_level
2714 from pa_proj_fp_options
2715 where proj_fp_options_id = ci_rec.proj_fp_options_id;
2716 -- PLANNING LEVEL = 'P' if one of the planning levels is P
2717 if (l_cost_planning_level = 'P') or (l_rev_planning_level = 'P') then
2718 x_planning_level := 'P';
2719 else
2720 x_planning_level := l_cost_planning_level;
2721 end if;
2722 if pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG = 'N' or ci_rec.plan_in_multi_curr_flag = 'N' then
2723 pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG := 'N';
2724 end if;
2725
2726 elsif l_fp_preference_code = 'REVENUE_ONLY' then
2727 -- this second row must be the complementary COST version
2728 select DECODE(rl.group_resource_type_id,
2729 0, 'NONGROUPED',
2730 'GROUPED'),
2731 rl.resource_list_id,
2732 bv.record_version_number,
2733 nvl(rl.uncategorized_flag, 'N')
2734 into l_compl_grouping_type,
2735 l_compl_resource_list_id,
2736 l_compl_rv_number,
2737 l_compl_uncategorized_flag
2738 from pa_budget_versions bv,
2739 pa_resource_lists_all_bg rl
2740 where bv.budget_version_id = ci_rec.budget_version_id and
2741 bv.resource_list_id = rl.resource_list_id;
2742 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := ci_rec.budget_version_id;
2743 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := l_ci_budget_version_id;
2744 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_compl_grouping_type;
2745 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
2746 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'BOTH';
2747 if l_grouping_type = 'GROUPED' then
2748 if l_compl_grouping_type = 'GROUPED' then
2749 x_grouping_type := 'GROUPED';
2750 else
2751 x_grouping_type := 'MIXED';
2752 end if;
2753 else
2754 if l_compl_grouping_type = 'GROUPED' then
2755 x_grouping_type := 'MIXED';
2756 else
2757 x_grouping_type := 'NONGROUPED';
2758 end if;
2759 end if;
2760 x_cost_rv_number := l_compl_rv_number;
2761 x_rev_rv_number := l_rv_number;
2762 x_cost_rl_id := l_resource_list_id;
2763 x_rev_rl_id := l_compl_resource_list_id;
2764 -- planning level code for cost (compl) version: P, T, L, or M
2765 select cost_fin_plan_level_code
2766 into l_cost_planning_level
2767 from pa_proj_fp_options
2768 where proj_fp_options_id = ci_rec.proj_fp_options_id;
2769 -- planning level code for revenue version
2770 select revenue_fin_plan_level_code
2771 into l_rev_planning_level
2772 from pa_proj_fp_options
2773 where proj_fp_options_id = l_proj_fp_options_id;
2774 -- PLANNING LEVEL = 'P' if one of the planning levels is P
2775 if (l_cost_planning_level = 'P') or (l_rev_planning_level = 'P') then
2776 x_planning_level := 'P';
2777 else
2778 x_planning_level := l_rev_planning_level;
2779 end if;
2780 if pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG = 'N' or ci_rec.plan_in_multi_curr_flag = 'N' then
2781 pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG := 'N';
2782 end if;
2783 -- ** if the second row is COST version, then its pref codes take precedence **
2784 if l_margin_derived_code is not null then
2785 pa_fp_view_plans_txn_pub.G_DERIVE_MARGIN_FROM_CODE := l_margin_derived_code;
2786 end if;
2787 if l_fin_plan_type_id <> l_fin_plan_type_id2 then
2788 pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE := 'COST';
2789 end if;
2790
2791
2792 elsif l_fp_preference_code = 'COST_AND_REV_SEP' then
2793 if l_cost_or_revenue = 'R' then
2794 -- this second row must be the complementary COST version
2795 select DECODE(rl.group_resource_type_id,
2796 0, 'NONGROUPED',
2797 'GROUPED'),
2798 rl.resource_list_id,
2799 bv.record_version_number,
2800 nvl(rl.uncategorized_flag, 'N')
2801 into l_compl_grouping_type,
2802 l_compl_resource_list_id,
2803 l_compl_rv_number,
2804 l_compl_uncategorized_flag
2805 from pa_budget_versions bv,
2806 pa_resource_lists_all_bg rl
2807 where bv.budget_version_id = ci_rec.budget_version_id and
2808 bv.resource_list_id = rl.resource_list_id;
2809 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := ci_rec.budget_version_id;
2810 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := l_ci_budget_version_id;
2811 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_compl_grouping_type;
2812 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_grouping_type;
2813 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'BOTH';
2814 if l_grouping_type = 'GROUPED' then
2815 if l_compl_grouping_type = 'GROUPED' then
2816 x_grouping_type := 'GROUPED';
2817 else
2818 x_grouping_type := 'MIXED';
2819 end if;
2820 else
2821 if l_compl_grouping_type = 'GROUPED' then
2822 x_grouping_type := 'MIXED';
2823 else
2824 x_grouping_type := 'NONGROUPED';
2825 end if;
2826 end if;
2827 x_cost_rv_number := l_compl_rv_number;
2828 x_rev_rv_number := l_rv_number;
2829 x_cost_rl_id := l_resource_list_id;
2830 x_rev_rl_id := l_compl_resource_list_id;
2831 -- planning level code for cost (compl) version: P, T, L, or M
2832 select cost_fin_plan_level_code
2833 into l_cost_planning_level
2834 from pa_proj_fp_options
2835 where proj_fp_options_id = ci_rec.proj_fp_options_id;
2836 -- planning level code for revenue version
2837 select revenue_fin_plan_level_code
2838 into l_rev_planning_level
2839 from pa_proj_fp_options
2840 where proj_fp_options_id = l_proj_fp_options_id;
2841 -- PLANNING LEVEL = 'P' if one of the planning levels is P
2842 if (l_cost_planning_level = 'P') or (l_rev_planning_level = 'P') then
2843 x_planning_level := 'P';
2844 else
2845 x_planning_level := l_rev_planning_level;
2846 end if;
2847 if pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG = 'N' or ci_rec.plan_in_multi_curr_flag = 'N' then
2848 pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG := 'N';
2849 end if;
2850 -- ** if the second row is COST version, then its pref codes take precedence **
2851 if l_margin_derived_code is not null then
2852 pa_fp_view_plans_txn_pub.G_DERIVE_MARGIN_FROM_CODE := l_margin_derived_code;
2853 end if;
2854 if l_fin_plan_type_id <> l_fin_plan_type_id2 then
2855 pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE := 'COST';
2856 end if;
2857
2858
2859 else
2860 -- this second row must be the complementary REVENUE version
2861 select DECODE(rl.group_resource_type_id,
2862 0, 'NONGROUPED',
2863 'GROUPED'),
2864 rl.resource_list_id,
2865 bv.record_version_number,
2866 nvl(rl.uncategorized_flag, 'N')
2867 into l_compl_grouping_type,
2868 l_compl_resource_list_id,
2869 l_compl_rv_number,
2870 l_compl_uncategorized_flag
2871 from pa_budget_versions bv,
2872 pa_resource_lists_all_bg rl
2873 where bv.budget_version_id = ci_rec.budget_version_id and
2874 bv.resource_list_id = rl.resource_list_id;
2875 pa_fp_view_plans_txn_pub.G_COST_VERSION_ID := l_ci_budget_version_id;
2876 pa_fp_view_plans_txn_pub.G_REV_VERSION_ID := ci_rec.budget_version_id;
2877 pa_fp_view_plans_txn_pub.G_COST_VERSION_GROUPING := l_grouping_type;
2878 pa_fp_view_plans_txn_pub.G_REV_VERSION_GROUPING := l_compl_grouping_type;
2879 pa_fp_view_plans_txn_pub.G_DISPLAY_FROM := 'BOTH';
2880 if l_grouping_type = 'GROUPED' then
2881 if l_compl_grouping_type = 'GROUPED' then
2882 x_grouping_type := 'GROUPED';
2883 else
2884 x_grouping_type := 'MIXED';
2885 end if;
2886 else
2887 if l_compl_grouping_type = 'GROUPED' then
2888 x_grouping_type := 'MIXED';
2889 else
2890 x_grouping_type := 'NONGROUPED';
2891 end if;
2892 end if;
2893 x_cost_rv_number := l_rv_number;
2894 x_rev_rv_number := l_compl_rv_number;
2895 x_cost_rl_id := l_resource_list_id;
2896 x_rev_rl_id := l_compl_resource_list_id;
2897 -- planning level code for cost version: P, T, L, or M
2898 select cost_fin_plan_level_code
2899 into l_cost_planning_level
2900 from pa_proj_fp_options
2901 where proj_fp_options_id = l_proj_fp_options_id;
2902 -- planning level code for revenue (compl) version
2903 select revenue_fin_plan_level_code
2904 into l_rev_planning_level
2905 from pa_proj_fp_options
2906 where proj_fp_options_id = ci_rec.proj_fp_options_id;
2907 -- PLANNING LEVEL = 'P' if one of the planning levels is P
2908 if (l_cost_planning_level = 'P') or (l_rev_planning_level = 'P') then
2909 x_planning_level := 'P';
2910 else
2911 x_planning_level := l_cost_planning_level;
2912 end if;
2913 if pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG = 'N' or ci_rec.plan_in_multi_curr_flag = 'N' then
2914 pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG := 'N';
2915 end if;
2916 end if;
2917
2918
2919 end if;
2920 end if;
2921 end loop;
2922 close ci_csr;
2923
2924
2925 x_fin_plan_type_id := l_fin_plan_type_id;
2926 x_display_from := pa_fp_view_plans_txn_pub.G_DISPLAY_FROM;
2927 x_multicurrency_flag := pa_fp_view_plans_txn_pub.G_MULTI_CURR_FLAG;
2928 x_cost_version_id := pa_fp_view_plans_txn_pub.G_COST_VERSION_ID;
2929 x_rev_version_id := pa_fp_view_plans_txn_pub.G_REV_VERSION_ID;
2930
2931 x_labor_hrs_from_code := pa_fp_view_plans_txn_pub.G_REPORT_LABOR_HRS_FROM_CODE;
2932 if ((pa_fp_view_plans_txn_pub.G_DISPLAY_FROM = 'BOTH') and
2933 ((l_uncategorized_flag = 'Y') and (l_compl_uncategorized_flag = 'Y'))) or
2934 ((pa_fp_view_plans_txn_pub.G_DISPLAY_FROM <> 'BOTH') and
2935 (l_uncategorized_flag = 'Y')) then
2936 x_planned_resources_flag := 'N';
2937 else
2938 x_planned_resources_flag := 'Y';
2939 end if;
2940
2941 -- determine locked status of budget version(s)
2942 if x_display_from = 'ANY' then
2943 pa_fin_plan_utils.Check_Locked_By_User
2944 (p_user_id => p_user_id,
2945 p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
2946 x_is_locked_by_userid => l_is_cost_locked_by_user,
2947 x_locked_by_person_id => l_cost_locked_by_person_id,
2948 x_return_status => l_return_status,
2949 x_msg_count => l_msg_count,
2950 x_msg_data => l_msg_data);
2951 if l_is_cost_locked_by_user = 'N' then
2952 if l_cost_locked_by_person_id is null then
2953 x_cost_locked_name := 'NONE';
2954 x_rev_locked_name := 'NONE';
2955 else
2956 x_cost_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
2957 x_rev_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
2958 end if;
2959 else
2960 x_cost_locked_name := 'SELF';
2961 x_rev_locked_name := 'SELF';
2962 end if; -- is_cost_locked_by_user
2963
2964 /***** BUG 2813661: use pa_fp_view_plans_util.get_plan_version_res_level to set
2965 x_grouping_type and x_planned_resources_flag *****/
2966 pa_fp_view_plans_util.get_plan_version_res_level
2967 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
2968 p_entered_amts_only_flag => 'Y',
2969 x_resource_level => l_resource_level,
2970 x_return_status => l_return_status,
2971 x_msg_count => l_msg_count,
2972 x_msg_data => l_msg_data);
2973 if l_return_status = FND_API.G_RET_STS_SUCCESS then
2974 if l_resource_level = 'R' then
2975 x_display_res_flag := 'Y';
2976 x_display_resgp_flag := 'N';
2977 elsif l_resource_level = 'G' then
2978 x_display_res_flag := 'N';
2979 x_display_resgp_flag := 'Y';
2980 elsif l_resource_level = 'M' then
2981 x_display_res_flag := 'Y';
2982 x_display_resgp_flag := 'Y';
2983 else
2984 x_display_res_flag := 'N';
2985 x_display_resgp_flag := 'N';
2986 end if;
2987 else
2988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2989 x_msg_count := FND_MSG_PUB.Count_Msg;
2990 if x_msg_count = 1 then
2991 PA_INTERFACE_UTILS_PUB.get_messages
2992 (p_encoded => FND_API.G_TRUE,
2993 p_msg_index => 1,
2994 p_data => x_msg_data,
2995 p_msg_index_out => l_msg_index_out);
2996 end if;
2997 return;
2998 end if;
2999
3000 elsif x_display_from = 'COST' then
3001 pa_fin_plan_utils.Check_Locked_By_User
3002 (p_user_id => p_user_id,
3003 p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
3004 x_is_locked_by_userid => l_is_cost_locked_by_user,
3005 x_locked_by_person_id => l_cost_locked_by_person_id,
3006 x_return_status => l_return_status,
3007 x_msg_count => l_msg_count,
3008 x_msg_data => l_msg_data);
3009 if l_is_cost_locked_by_user = 'N' then
3010 if l_cost_locked_by_person_id is null then
3011 x_cost_locked_name := 'NONE';
3012 else
3013 x_cost_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
3014 end if;
3015 else
3016 x_cost_locked_name := 'SELF';
3017 end if; -- is_cost_locked_by_user
3018
3019 pa_fp_view_plans_util.get_plan_version_res_level
3020 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
3021 p_entered_amts_only_flag => 'Y',
3022 x_resource_level => l_resource_level,
3023 x_return_status => l_return_status,
3024 x_msg_count => l_msg_count,
3025 x_msg_data => l_msg_data);
3026 if l_return_status = FND_API.G_RET_STS_SUCCESS then
3027 if l_resource_level = 'R' then
3028 x_display_res_flag := 'Y';
3029 x_display_resgp_flag := 'N';
3030 elsif l_resource_level = 'G' then
3031 x_display_res_flag := 'N';
3032 x_display_resgp_flag := 'Y';
3033 elsif l_resource_level = 'M' then
3034 x_display_res_flag := 'Y';
3035 x_display_resgp_flag := 'Y';
3036 else
3037 x_display_res_flag := 'N';
3038 x_display_resgp_flag := 'N';
3039 end if;
3040 else
3041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3042 x_msg_count := FND_MSG_PUB.Count_Msg;
3043 if x_msg_count = 1 then
3044 PA_INTERFACE_UTILS_PUB.get_messages
3045 (p_encoded => FND_API.G_TRUE,
3046 p_msg_index => 1,
3047 p_data => x_msg_data,
3048 p_msg_index_out => l_msg_index_out);
3049 end if;
3050 return;
3051 end if;
3052
3053 elsif x_display_from = 'REVENUE' then
3054 pa_fin_plan_utils.Check_Locked_By_User
3055 (p_user_id => p_user_id,
3056 p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
3057 x_is_locked_by_userid => l_is_rev_locked_by_user,
3058 x_locked_by_person_id => l_rev_locked_by_person_id,
3059 x_return_status => l_return_status,
3060 x_msg_count => l_msg_count,
3061 x_msg_data => l_msg_data);
3062 if l_is_rev_locked_by_user = 'N' then
3063 if l_rev_locked_by_person_id is null then
3064 x_rev_locked_name := 'NONE';
3065 else
3066 x_rev_locked_name := pa_fin_plan_utils.get_person_name(l_rev_locked_by_person_id);
3067 end if;
3068 else
3069 x_rev_locked_name := 'SELF';
3070 end if; -- is_rev_locked_by_user
3071
3072 pa_fp_view_plans_util.get_plan_version_res_level
3073 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
3074 p_entered_amts_only_flag => 'Y',
3075 x_resource_level => l_resource_level,
3076 x_return_status => l_return_status,
3077 x_msg_count => l_msg_count,
3078 x_msg_data => l_msg_data);
3079 if l_return_status = FND_API.G_RET_STS_SUCCESS then
3080 if l_resource_level = 'R' then
3081 x_display_res_flag := 'Y';
3082 x_display_resgp_flag := 'N';
3083 elsif l_resource_level = 'G' then
3084 x_display_res_flag := 'N';
3085 x_display_resgp_flag := 'Y';
3086 elsif l_resource_level = 'M' then
3087 x_display_res_flag := 'Y';
3088 x_display_resgp_flag := 'Y';
3089 else
3090 x_display_res_flag := 'N';
3091 x_display_resgp_flag := 'N';
3092 end if;
3093 else
3094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3095 x_msg_count := FND_MSG_PUB.Count_Msg;
3096 if x_msg_count = 1 then
3097 PA_INTERFACE_UTILS_PUB.get_messages
3098 (p_encoded => FND_API.G_TRUE,
3099 p_msg_index => 1,
3100 p_data => x_msg_data,
3101 p_msg_index_out => l_msg_index_out);
3102 end if;
3103 return;
3104 end if;
3105
3106 elsif x_display_from = 'BOTH' then
3107 -- FOR COST VERSION
3108 pa_fin_plan_utils.Check_Locked_By_User
3109 (p_user_id => p_user_id,
3110 p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
3111 x_is_locked_by_userid => l_is_cost_locked_by_user,
3112 x_locked_by_person_id => l_cost_locked_by_person_id,
3113 x_return_status => l_return_status,
3114 x_msg_count => l_msg_count,
3115 x_msg_data => l_msg_data);
3116 if l_is_cost_locked_by_user = 'N' then
3117 if l_cost_locked_by_person_id is null then
3118 x_cost_locked_name := 'NONE';
3119 else
3120 x_cost_locked_name := pa_fin_plan_utils.get_person_name(l_cost_locked_by_person_id);
3121 end if;
3122 else
3123 x_cost_locked_name := 'SELF';
3124 end if; -- is_cost_locked_by_user
3125
3126 pa_fp_view_plans_util.get_plan_version_res_level
3127 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_COST_VERSION_ID,
3128 p_entered_amts_only_flag => 'Y',
3129 x_resource_level => l_cost_resource_level,
3130 x_return_status => l_return_status,
3131 x_msg_count => l_msg_count,
3132 x_msg_data => l_msg_data);
3133 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3135 x_msg_count := FND_MSG_PUB.Count_Msg;
3136 if x_msg_count = 1 then
3137 PA_INTERFACE_UTILS_PUB.get_messages
3138 (p_encoded => FND_API.G_TRUE,
3139 p_msg_index => 1,
3140 p_data => x_msg_data,
3141 p_msg_index_out => l_msg_index_out);
3142 end if;
3143 return;
3144 end if;
3145
3146 -- FOR REVENUE VERSION
3147 pa_fin_plan_utils.Check_Locked_By_User
3148 (p_user_id => p_user_id,
3149 p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
3150 x_is_locked_by_userid => l_is_rev_locked_by_user,
3151 x_locked_by_person_id => l_rev_locked_by_person_id,
3152 x_return_status => l_return_status,
3153 x_msg_count => l_msg_count,
3154 x_msg_data => l_msg_data);
3155 if l_is_rev_locked_by_user = 'N' then
3156 if l_rev_locked_by_person_id is null then
3157 x_rev_locked_name := 'NONE';
3158 else
3159 x_rev_locked_name := pa_fin_plan_utils.get_person_name(l_rev_locked_by_person_id);
3160 end if;
3161 else
3162 x_rev_locked_name := 'SELF';
3163 end if; -- is_cost_locked_by_user
3164 pa_fp_view_plans_util.get_plan_version_res_level
3165 (p_budget_version_id => pa_fp_view_plans_txn_pub.G_REV_VERSION_ID,
3166 p_entered_amts_only_flag => 'Y',
3167 x_resource_level => l_revenue_resource_level,
3168 x_return_status => l_return_status,
3169 x_msg_count => l_msg_count,
3170 x_msg_data => l_msg_data);
3171 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3173 x_msg_count := FND_MSG_PUB.Count_Msg;
3174 if x_msg_count = 1 then
3175 PA_INTERFACE_UTILS_PUB.get_messages
3176 (p_encoded => FND_API.G_TRUE,
3177 p_msg_index => 1,
3178 p_data => x_msg_data,
3179 p_msg_index_out => l_msg_index_out);
3180 end if;
3181 return;
3182 end if;
3183 if l_cost_resource_level = 'R' and l_revenue_resource_level = 'R' then
3184 x_display_res_flag := 'Y';
3185 x_display_resgp_flag := 'N';
3186 elsif l_cost_resource_level = 'G' and l_revenue_resource_level = 'G' then
3187 x_display_res_flag := 'N';
3188 x_display_resgp_flag := 'Y';
3189 else
3190 x_display_res_flag := 'Y';
3191 x_display_resgp_flag := 'Y';
3192 end if;
3193 end if;
3194 pa_fp_view_plans_txn_pub.G_COST_RESOURCE_LIST_ID := x_cost_rl_id;
3195 pa_fp_view_plans_txn_pub.G_REVENUE_RESOURCE_LIST_ID := x_rev_rl_id;
3196
3197 END nonhgrid_view_initialize_ci;
3198
3199
3200 /*==========================================================================================
3201 This is the main API which will do the necessary actions for using the Client Extensions
3202 for calculating the raw_cost, burdened_cost and revenue for a complete version.
3203 =========================================================================================*/
3204 PROCEDURE CALCULATE_AMOUNTS_FOR_VERSION
3205 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
3206 ,p_calling_context IN VARCHAR2
3207 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3208 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3209 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3210 AS
3211
3212 l_msg_count NUMBER := 0;
3213 l_data VARCHAR2(2000);
3214 l_msg_data VARCHAR2(2000);
3215 l_msg_index_out NUMBER;
3216 l_debug_mode VARCHAR2(1);
3217 l_module_name VARCHAR2(50) := 'pa.plsql.PA_FP_VIEW_PLANS_TXN_PUB';
3218
3219 l_debug_level2 CONSTANT NUMBER := 2;
3220 l_debug_level3 CONSTANT NUMBER := 3;
3221 l_debug_level4 CONSTANT NUMBER := 4;
3222 l_debug_level5 CONSTANT NUMBER := 5;
3223
3224 l_project_id pa_projects_all.project_id%TYPE ;
3225 l_task_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3226 l_res_list_member_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3227 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE ;
3228 l_resource_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3229 l_txn_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type ;
3230 l_product_code_tbl SYSTEM.pa_varchar2_30_tbl_type ;
3231 l_start_date_tbl SYSTEM.pa_date_tbl_type ;
3232 l_end_date_tbl SYSTEM.pa_date_tbl_type ;
3233 l_period_name_tbl SYSTEM.pa_varchar2_30_tbl_type ;
3234 l_quantity_tbl SYSTEM.pa_num_tbl_type ;
3235 l_txn_raw_cost_tbl SYSTEM.pa_num_tbl_type ;
3236 l_txn_burdened_cost_tbl SYSTEM.pa_num_tbl_type ;
3237 l_txn_revenue_tbl SYSTEM.pa_num_tbl_type ;
3238
3239 l_budget_line_id_tbl SYSTEM.pa_num_tbl_type ;
3240 l_ra_id_tbl SYSTEM.pa_num_tbl_type ;
3241 l_task_id pa_tasks.task_id%TYPE;
3242 l_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
3243 l_resource_id pa_resource_list_members.resource_id%TYPE;
3244 l_prev_res_assignment_id pa_resource_assignments.resource_assignment_id%TYPE := -99;
3245
3246 CURSOR cur_sel_bud_line IS
3247 SELECT budget_line_id,
3248 resource_assignment_id,
3249 start_date,
3250 end_date,
3251 period_name,
3252 txn_currency_code,
3253 pm_product_code,
3254 quantity,
3255 txn_raw_cost,
3256 txn_burdened_cost,
3257 txn_revenue
3258 FROM pa_budget_lines
3259 WHERE budget_version_id = p_budget_version_id
3260 ORDER BY resource_assignment_id;
3261
3262 BEGIN
3263
3264 x_msg_count := 0;
3265 x_return_status := FND_API.G_RET_STS_SUCCESS;
3266 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3267
3268 pa_debug.set_curr_function( p_function => 'CALCULATE_AMOUNTS_FOR_VERSION',
3269 p_debug_mode => l_debug_mode );
3270
3271 IF l_debug_mode = 'Y' THEN
3272 pa_debug.g_err_stage := 'In PA_FP_VIEW_PLANS_TXN.CALCULATE_AMTS_FOR_VERSION ';
3273 pa_debug.write('CALCULATE_AMTS_FOR_VERSION: ' || l_module_name,pa_debug.g_err_stage,l_debug_level3);
3274 END IF;
3275
3276 -- Check for business rules violations
3277
3278 IF l_debug_mode = 'Y' THEN
3279 pa_debug.g_err_stage:= 'Validating input parameters';
3280 pa_debug.write(l_module_name,pa_debug.g_err_stage,
3281 l_debug_level3);
3282 END IF;
3283
3284 IF (p_budget_version_id IS NULL)
3285 THEN
3286 IF l_debug_mode = 'Y' THEN
3287 pa_debug.g_err_stage:= 'Invalid parameter(budget version id)';
3288 pa_debug.write(l_module_name,pa_debug.g_err_stage,
3289 l_debug_level5);
3290 END IF;
3291 PA_UTILS.ADD_MESSAGE
3292 (p_app_short_name => 'PA',
3293 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3294 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3295
3296 END IF;
3297
3298 /* Bulk fetch the cursor records into the PL/SQL tables. */
3299 OPEN cur_sel_bud_line;
3300 LOOP
3301
3302 IF P_PA_DEBUG_MODE = 'Y' THEN
3303 pa_debug.g_err_stage := 'Fetching records from Budget Lines';
3304 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3305 END IF;
3306
3307 FETCH cur_sel_bud_line BULK COLLECT INTO
3308 l_budget_line_id_tbl,
3309 l_ra_id_tbl,
3310 l_start_date_tbl,
3311 l_end_date_tbl,
3312 l_period_name_tbl,
3313 l_txn_currency_code_tbl,
3314 l_product_code_tbl,
3315 l_quantity_tbl,
3316 l_txn_raw_cost_tbl,
3317 l_txn_burdened_cost_tbl,
3318 l_txn_revenue_tbl
3319 LIMIT g_plsql_max_array_size;
3320
3321
3322 /* Delete the PL/SQL tables which are being populated manually. */
3323 l_task_id_tbl.delete;
3324 l_res_list_member_id_tbl.delete;
3325 l_resource_id_tbl.delete;
3326
3327 IF nvl(l_budget_line_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
3328
3329 l_task_id_tbl.extend(l_budget_line_id_tbl.last);
3330 l_res_list_member_id_tbl.extend(l_budget_line_id_tbl.last);
3331 l_resource_id_tbl.extend(l_budget_line_id_tbl.last);
3332
3333 FOR i in l_budget_line_id_tbl.first..l_budget_line_id_tbl.last
3334 LOOP
3335
3336 /* Fetch the relevant details for the Res Assignment that are
3337 required to call the CAll_Client_Extensions API. The select
3338 has to be done only once for a RA ID and so caching the RA ID.
3339
3340 Since l_prev_res_assignment_id has been initialised to -99,
3341 the below condition will be satisfied even for the first time we
3342 enter into this loop. */
3343
3344 IF l_prev_res_assignment_id <> l_ra_id_tbl(i) THEN
3345
3346 /* Fetch the details of the Resource Assignment if not fetched
3347 already. */
3348 SELECT pra.task_id,
3349 pra.resource_list_member_id,
3350 pra.project_id,
3351 prlm.resource_id,
3352 prlm.resource_list_id
3353 INTO l_task_id,
3354 l_rlm_id,
3355 l_project_id,
3356 l_resource_id,
3357 l_resource_list_id
3358 FROM pa_resource_assignments pra,
3359 pa_resource_list_members prlm
3360 WHERE pra.resource_assignment_id = l_ra_id_tbl(i)
3361 AND prlm.resource_list_member_id = pra.resource_list_member_id;
3362
3363 l_prev_res_assignment_id := l_ra_id_tbl(i);
3364
3365 ELSE
3366
3367 /* Do not fetch the details of the RA once again. The local variables
3368 already have the values.*/
3369 NULL;
3370
3371 END IF;
3372
3373 /* Populate the record in the PL/SQL table with the local variables populated. */
3374
3375 l_task_id_tbl(i) := l_task_id;
3376 l_res_list_member_id_tbl(i) := l_rlm_id;
3377 l_resource_id_tbl(i) := l_resource_id;
3378
3379 END LOOP;
3380
3381 /* By now all the required PL/SQL tables to call the API Call_Client_Extension are
3382 populated. Call the overloaded API which accepts the PL/SQL tables as parameters. */
3383
3384 IF P_PA_DEBUG_MODE = 'Y' THEN
3385 pa_debug.g_err_stage := 'Calling PA_FP_EDIT_LINE_PKG.CALL_CLIENT_EXTENSIONS';
3386 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3387 END IF;
3388
3389 PA_FP_EDIT_LINE_PKG.CALL_CLIENT_EXTENSIONS
3390 ( p_project_id => l_project_id
3391 ,p_budget_version_id => p_budget_version_id
3392 ,p_task_id_tbl => l_task_id_tbl
3393 ,p_res_list_member_id_tbl => l_res_list_member_id_tbl
3394 ,p_resource_list_id => l_resource_list_id
3395 ,p_resource_id_tbl => l_resource_id_tbl
3396 ,p_txn_currency_code_tbl => l_txn_currency_code_tbl
3397 ,p_product_code_tbl => l_product_code_tbl
3398 ,p_start_date_tbl => l_start_date_tbl
3399 ,p_end_date_tbl => l_end_date_tbl
3400 ,p_period_name_tbl => l_period_name_tbl
3401 ,p_quantity_tbl => l_quantity_tbl
3402 ,px_raw_cost_tbl => l_txn_raw_cost_tbl
3403 ,px_burdened_cost_tbl => l_txn_burdened_cost_tbl
3404 ,px_revenue_tbl => l_txn_revenue_tbl
3405 ,x_return_status => x_return_status
3406 ,x_msg_count => x_msg_count
3407 ,x_msg_data => x_msg_data );
3408
3409 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3410 IF P_PA_DEBUG_MODE = 'Y' THEN
3411 pa_debug.g_err_stage := 'Call to PA_FP_EDIT_LINE_PKG.CALL_CLIENT_EXTENSIONS errored... ';
3412 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
3413 END IF;
3414 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3415 END IF;
3416
3417 /* The client extensions would have populated the Raw Cost, Burdened Cost, Revenue
3418 and Quantity accordingly. Bulk update the amounts on the Budget Lines table. */
3419
3420 IF P_PA_DEBUG_MODE = 'Y' THEN
3421 pa_debug.g_err_stage := 'Updating the Budget Line amounts';
3422 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3423 END IF;
3424
3425 FORALL i in l_budget_line_id_tbl.first..l_budget_line_id_tbl.last
3426 UPDATE pa_budget_lines
3427 SET txn_raw_cost = l_txn_raw_cost_tbl(i)
3428 ,txn_burdened_cost = l_txn_burdened_cost_tbl(i)
3429 ,txn_revenue = l_txn_revenue_tbl(i)
3430 ,quantity = l_quantity_tbl(i)
3431 ,last_update_date = SYSDATE
3432 ,last_updated_by = FND_GLOBAL.user_id
3433 ,last_update_login = FND_GLOBAL.login_id
3434 WHERE budget_line_id = l_budget_line_id_tbl(i);
3435
3436 IF P_PA_DEBUG_MODE = 'Y' THEN
3437 pa_debug.g_err_stage := 'Updated - '||sql%rowcount||' records';
3438 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3439 END IF;
3440
3441 END IF; /* end of only if something is fetched */
3442
3443 EXIT WHEN nvl(l_budget_line_id_tbl.last,0) < g_plsql_max_array_size;
3444
3445 END LOOP; -- End of main loop
3446 CLOSE cur_sel_bud_line; -- Close the main cursor.
3447
3448
3449 IF P_PA_DEBUG_MODE = 'Y' THEN
3450 pa_debug.g_err_stage := 'Calling PA_FP_EDIT_LINE_PKG.PROCESS_BDGTLINES_FOR_VERSION';
3451 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3452 END IF;
3453
3454 PA_FP_EDIT_LINE_PKG.PROCESS_BDGTLINES_FOR_VERSION
3455 ( p_budget_version_id => p_budget_version_id
3456 ,p_calling_context => p_calling_context
3457 ,x_return_status => x_return_status
3458 ,x_msg_count => x_msg_count
3459 ,x_msg_data => x_msg_data);
3460
3461 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3462 IF P_PA_DEBUG_MODE = 'Y' THEN
3463 pa_debug.g_err_stage := 'Call to PA_FP_EDIT_LINE_PKG.PROCESS_BDGTLINES_FOR_VERSION errored... ';
3464 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
3465 END IF;
3466 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3467 END IF;
3468
3469 IF l_debug_mode = 'Y' THEN
3470 pa_debug.g_err_stage:= 'Exiting CALCULATE_AMOUNTS_FOR_VERSION';
3471 pa_debug.write(l_module_name,pa_debug.g_err_stage,
3472 l_debug_level3);
3473 END IF;
3474 pa_debug.reset_curr_function;
3475
3476 EXCEPTION
3477
3478 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3479
3480 x_return_status := FND_API.G_RET_STS_ERROR;
3481 /* Rollback in case of any exceptions in the process. */
3482 ROLLBACK;
3483 l_msg_count := FND_MSG_PUB.count_msg;
3484
3485 IF cur_sel_bud_line%ISOPEN THEN
3486 CLOSE cur_sel_bud_line;
3487 END IF;
3488
3489 IF l_msg_count = 1 and x_msg_data IS NULL THEN
3490 PA_INTERFACE_UTILS_PUB.get_messages
3491 (p_encoded => FND_API.G_TRUE
3492 ,p_msg_index => 1
3493 ,p_msg_count => l_msg_count
3494 ,p_msg_data => l_msg_data
3495 ,p_data => l_data
3496 ,p_msg_index_out => l_msg_index_out);
3497 x_msg_data := l_data;
3498 x_msg_count := l_msg_count;
3499 ELSE
3500 x_msg_count := l_msg_count;
3501 END IF;
3502 pa_debug.reset_curr_function;
3503 RETURN;
3504
3505 WHEN others THEN
3506
3507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3508 /* Rollback in case of any exceptions in the process. */
3509 ROLLBACK;
3510 x_msg_count := 1;
3511 x_msg_data := SQLERRM;
3512
3513 IF cur_sel_bud_line%ISOPEN THEN
3514 CLOSE cur_sel_bud_line;
3515 END IF;
3516
3517 FND_MSG_PUB.add_exc_msg
3518 ( p_pkg_name => 'pa_fp_view_plans_txn_pub'
3519 ,p_procedure_name => 'CALCULATE_AMOUNTS_FOR_VERSION'
3520 ,p_error_text => x_msg_data);
3521
3522 IF l_debug_mode = 'Y' THEN
3523 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3524 pa_debug.write(l_module_name,pa_debug.g_err_stage,
3525 l_debug_level5);
3526 END IF;
3527 pa_debug.reset_curr_function;
3528 RAISE;
3529 END CALCULATE_AMOUNTS_FOR_VERSION;
3530
3531 end pa_fp_view_plans_txn_pub;