[Home] [Help]
PACKAGE BODY: APPS.PA_FP_ADJUSTMENT_UTILS
Source
1 PACKAGE BODY PA_FP_ADJUSTMENT_UTILS AS
2 -- $Header: PAFPADJB.pls 120.5 2007/11/26 07:50:38 vgovvala ship $
3
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 L_MODULE varchar2(100) := 'PA_FP_ADJUSTMENT_UTILS';
6 L_FuncProc varchar2(250) := 'DEFAULT';
7 li_message_level NUMBER := 1;
8 li_curr_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9
10
11 -- This procedure will Get Summary Information on a
12 -- given Budget Version Id/Name
13
14 PROCEDURE Get_Summary_Info
15 ( p_project_id IN NUMBER
16 ,p_cost_budget_version_id IN NUMBER
17 ,p_rev_budget_version_id IN NUMBER
18 ,p_WBS_Element_Id IN NUMBER DEFAULT NULL
19 ,p_RBS_Element_Id IN NUMBER DEFAULT NULL
20 ,p_WBS_Structure_Version_Id IN NUMBER DEFAULT NULL
21 ,p_RBS_Version_Id IN NUMBER DEFAULT NULL
22 ,p_WBS_Rollup_Flag IN VARCHAR2
23 ,p_RBS_Rollup_Flag IN VARCHAR2
24 ,p_resource_tbl_flag IN VARCHAR2 DEFAULT 'N'
25 ,p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE()
26 ,p_txn_currency_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
27 ,x_version OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
28 ,x_version_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
29 ,x_project_id OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
30 ,x_structure_version_id OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
31 ,x_rev_version OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
32 ,x_rev_version_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
33 ,x_task_number OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
34 ,x_task_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
35 ,x_resource_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
36 ,x_plan_setup OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
37 ,x_plan_type_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
38 ,x_fin_plan_type_id OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
39 ,x_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
40 ,x_rev_plan_type_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
41 ,x_rev_workplan_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
42 ,x_rev_plan_setup OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
43 ,x_rev_plan_class_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
44 ,x_rev_fin_plan_type_id OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
45 ,x_rev_version_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
46 ,x_workplan_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
47 ,x_plan_class_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
48 ,x_pc_raw_cost OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
49 ,x_pc_burdened_cost OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
50 ,x_pc_revenue OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
51 ,x_pc_currency OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
52 ,x_pfc_raw_cost OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
53 ,x_pfc_burdened_cost OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
54 ,x_pfc_revenue OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
55 ,x_pfc_currency OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
56 ,x_pc_margin OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
57 ,x_pfc_margin OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
58 ,x_margin_percent OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
59 ,x_total_labor_hours OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
60 ,x_total_equip_hours OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
61 ,x_rev_total_labor_hours OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
62 ,x_rev_total_equip_hours OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
63 ,x_resource_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
64 ,x_txn_currency_code_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_15_TBL_TYPE --File.Sql.39 bug 4440895
65 ,x_workplan_costs_enabled_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
66 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
67 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
68 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
69
70 TYPE DYNAMIC_CUR IS REF CURSOR;
71 l_cur DYNAMIC_CUR;
72 l_sql VARCHAR2(32767);
73
74 l_predicate1 VARCHAR2(32767);
75 l_curr_code_predicate VARCHAR(32767);
76
77 -- Check when to account for 'Cost and revenue separately' with diff.
78 -- fin plan option level code..
79
80 l_task_name pa_proj_elements.name%TYPE;
81 l_task_number pa_proj_elements.element_number%TYPE;
82
83 --C1_Plan_Info_Rev C1_Plan_Info%ROWTYPE;
84 l_margin_code VARCHAR2(240);
85 l_rev_margin_code VARCHAR2(240);
86 l_labor_res_class VARCHAR2(30) := 'PEOPLE';
87 l_equip_res_class VARCHAR2(30) := 'EQUIPMENT';
88 l_msg_count NUMBER;
89 l_msg_data VARCHAR2(4000);
90 l_report_using VARCHAR2(30);
91
92 l_resource_assignment_id NUMBER;
93 l_currency_code VARCHAR2(15);
94 l_pfc_raw_cost NUMBER;
95 l_pfc_burdened_cost NUMBER;
96 l_pc_raw_cost NUMBER;
97 l_pc_burdened_cost NUMBER;
98 l_pc_revenue NUMBER;
99 l_pfc_revenue NUMBER;
100 l_labor_hours NUMBER;
101 l_equip_hours NUMBER;
102 l_rev_labor_hours NUMBER;
103 l_rev_equip_hours NUMBER;
104
105 l_pfc_raw_cost_total NUMBER := 0;
106 l_pfc_burdened_cost_total NUMBER := 0;
107 l_pc_raw_cost_total NUMBER := 0;
108 l_pc_burdened_cost_total NUMBER := 0;
109 l_pc_revenue_total NUMBER := 0;
110 l_pfc_revenue_total NUMBER := 0;
111 l_labor_hours_total NUMBER := 0;
112 l_equip_hours_total NUMBER := 0;
113 l_rev_labor_hours_total NUMBER := 0;
114 l_rev_equip_hours_total NUMBER := 0;
115
116 CURSOR C1_Plan_Info(p_budget_version_id IN NUMBER) IS
117 SELECT
118 B.VERSION_NUMBER,
119 B.VERSION_NAME,
120 B.PROJECT_ID,
121 DECODE(b.wp_version_flag,'Y',B.PROJECT_STRUCTURE_VERSION_ID,
122 PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(b.project_id )),
123 B.FIN_PLAN_TYPE_ID,
124 B.VERSION_TYPE,
125 --B.RECORD_VERSION_NUMBER,
126 A1.NAME PLAN_TYPE_NAME,
127 B.WP_VERSION_FLAG,
128 C.FIN_PLAN_PREFERENCE_CODE PLAN_SETUP,
129 A.PLAN_CLASS_CODE,
130 C.MARGIN_DERIVED_FROM_CODE,
131 C.report_labor_hrs_from_code,
132 C.track_workplan_costs_flag
133 FROM
134 PA_FIN_PLAN_TYPES_B A,
135 PA_FIN_PLAN_TYPES_TL A1,
136 PA_BUDGET_VERSIONS B,
137 PA_PROJ_FP_OPTIONS C
138 WHERE
139 A.FIN_PLAN_TYPE_ID = B.FIN_PLAN_TYPE_ID
140 AND A.FIN_PLAN_TYPE_ID = A1.FIN_PLAN_TYPE_ID
141 AND B.PROJECT_ID = C.PROJECT_ID
142 AND B.FIN_PLAN_TYPE_ID = C.FIN_PLAN_TYPE_ID
143 AND B.BUDGET_VERSION_ID = C.FIN_PLAN_VERSION_ID
144 AND C.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION'
145 AND B.BUDGET_VERSION_ID = p_budget_version_id
146 AND A1.Language = userenv('LANG');
147
148 CURSOR C1_Currency_Info(p_project_id IN NUMBER) IS
149 SELECT project_currency_code, projfunc_currency_code
150 FROM pa_projects_all
151 WHERE project_id = p_project_id;
152
153 CURSOR C1_Task_Info(p_wbs_element_id IN NUMBER) IS
154 SELECT pe.name task_name, pe.element_number task_number
155 FROM pa_proj_elements pe
156 WHERE pe.proj_element_id = p_wbs_element_id;
157
158 CURSOR C2_Task_Info(p_resource_assignment_id IN NUMBER) IS
159 SELECT pe.name task_name, pe.element_number task_number
160 FROM pa_proj_elements pe, Pa_resource_assignments ra
161 WHERE ra.task_id = pe.proj_element_id
162 and ra.resource_assignment_id = p_resource_assignment_id;
163
164 CURSOR C1_Resource_Info(p_rbs_element_id IN NUMBER) IS
165 SELECT name.resource_name
166 FROM pa_rbs_elements element, pa_rbs_element_names_tl name
167 WHERE element.rbs_element_name_id = name.rbs_element_name_id
168 AND element.rbs_element_id = p_rbs_element_id
169 AND name.language=userenv('LANG');
170
171 CURSOR C_Get_Object_Type (p_wbs_element_id IN NUMBER, p_wbs_structure_version_id IN NUMBER) IS
172 SELECT object_type
173 FROM pa_proj_element_versions
174 WHERE proj_element_id = p_wbs_element_id
175 AND element_version_id = p_wbs_structure_version_id;
176
177 l_structure_version_id varchar2(2000);
178 l_object_type pa_proj_element_versions.object_type%TYPE;
179 l_project_level_node_flag varchar2(1) := 'N';
180 l_workplan_costs_enabled_flag pa_proj_fp_options.track_workplan_costs_flag%TYPE;
181
182 BEGIN
183 --dbms_output.put_line('in sum test test1');
184
185 L_FuncProc := 'Get_Summary_Info';
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 pa_debug.g_err_stage:='Beginning of ' || L_FuncProc;
189 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
190
191 --Obtain Plan Info
192
193 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
194 PA_DEBUG.write(x_module => L_Module,
195 x_msg => 'p_project_id: ' || p_project_id,
196 x_log_level => 3);
197 PA_DEBUG.write(x_module => L_Module,
198 x_msg => 'p_cost_budget_version_id: ' || p_cost_budget_version_id,
199 x_log_level => 3);
200 PA_DEBUG.write(x_module => L_Module,
201 x_msg => 'p_rev_budget_version_id: ' || p_rev_budget_version_id,
202 x_log_level => 3);
203 PA_DEBUG.write(x_module => L_Module,
204 x_msg => 'p_wbs_element_id: ' || p_wbs_element_id,
205 x_log_level => 3);
206 PA_DEBUG.write(x_module => L_Module,
207 x_msg => 'p_rbs_element_id: ' || p_rbs_element_id,
208 x_log_level => 3);
209 PA_DEBUG.write(x_module => L_Module,
210 x_msg => 'p_wbs_structure_version_id: ' || p_wbs_structure_version_id,
211 x_log_level => 3);
212 PA_DEBUG.write(x_module => L_Module,
213 x_msg => 'p_rbs_version_id: ' || p_rbs_version_id,
214 x_log_level => 3);
215 PA_DEBUG.write(x_module => L_Module,
216 x_msg => 'p_resource_tbl_flag: ' || p_resource_tbl_flag,
217 x_log_level => 3);
218 FOR temp_i IN 1..p_resource_assignment_id_tbl.COUNT LOOP
219 PA_DEBUG.write(x_module => L_Module,
220 x_msg => 'p_resource_assignment_id_tbl(' || temp_i || '): ' || p_resource_assignment_id_tbl(temp_i),
221 x_log_level => 3);
222 END LOOP;
223 FOR temp_i IN 1..p_txn_currency_code_tbl.COUNT LOOP
224 PA_DEBUG.write(x_module => L_Module,
225 x_msg => 'p_txn_currency_code_tbl(' || temp_i || '): ' || p_txn_currency_code_tbl(temp_i),
226 x_log_level => 3);
227 END LOOP;
228 PA_DEBUG.write(x_module => L_Module,
229 x_msg => 'p_wbs_rollup_flag: ' || p_wbs_rollup_flag,
230 x_log_level => 3);
231 PA_DEBUG.write(x_module => L_Module,
232 x_msg => 'p_rbs_rollup_flag: ' || p_rbs_rollup_flag,
233 x_log_level => 3);
234 END IF;
235
236 IF nvl(p_cost_budget_version_id, -1) <> -1 THEN
237 OPEN C1_Plan_Info(p_cost_budget_version_id);
238 FETCH C1_Plan_Info INTO
239 x_version,
240 x_version_name,
241 --x_record_version_number,
242 x_project_id,
243 x_structure_version_id,
244 x_fin_plan_type_id,
245 x_version_type,
246 x_plan_type_name,
247 x_workplan_flag,
248 x_plan_setup,
249 x_plan_class_code,
250 l_margin_code,
251 l_report_using,
252 l_workplan_costs_enabled_flag;
253 CLOSE C1_Plan_Info;
254 END IF;
255
256 x_workplan_costs_enabled_flag := l_workplan_costs_enabled_flag;
257 IF x_workplan_costs_enabled_flag IS NULL THEN
258 x_workplan_costs_enabled_flag := 'Y';
259 END IF;
260
261 --Obtain Revenue Separate Plan Info
262
263 IF nvl(p_rev_budget_version_id, -1) <> -1 THEN
264 OPEN C1_Plan_Info(p_rev_budget_version_id);
265 FETCH C1_Plan_Info INTO
266 x_rev_version,
267 x_rev_version_name,
268 x_project_id,
269 --x_rev_rec_vers_number,
270 l_structure_version_id,
271 x_rev_fin_plan_type_id,
272 x_rev_version_type,
273 x_rev_plan_type_name, --Not displaying on UI ??
274 x_rev_workplan_flag,
275 x_rev_plan_setup,
276 x_rev_plan_class_code,
277 l_rev_margin_code,
278 l_report_using,
279 l_workplan_costs_enabled_flag;
280 CLOSE C1_Plan_Info;
281 END IF;
282
283 PA_DEBUG.write(x_module => L_Module,
284 x_msg => 'Got Plan Info',
285 x_log_level => 3);
286
287 --Obtain Currency Info
288 IF p_project_id is not NULL THEN
289 OPEN C1_Currency_Info(p_project_id);
290 FETCH C1_Currency_Info INTO
291 x_pc_currency,
292 x_pfc_currency;
293 CLOSE C1_Currency_Info;
294 END IF;
295
296 PA_DEBUG.write(x_module => L_Module,
297 x_msg => 'Got Currency Info',
298 x_log_level => 3);
299
300 --Obtain Task Name/Number.
301 IF p_WBS_Element_Id is not NULL THEN
302 OPEN C1_Task_Info(p_wbs_element_id);
303 FETCH C1_Task_Info into x_task_name, x_task_number;
304 CLOSE C1_Task_Info;
305 ELSIF p_resource_assignment_id_tbl IS NOT NULL THEN
306 -- Check if on a condition of Single Adjust/multiple resource assignments when to
307 -- display and when not to display task name/number.
308 IF p_resource_assignment_id_tbl.exists(1) THEN
309 OPEN C2_Task_Info(p_resource_assignment_id_tbl(1));
310 FETCH C2_Task_Info into x_task_name, x_task_number;
311 CLOSE C2_Task_Info;
312 END IF;
313 FOR i IN 1 .. p_resource_assignment_id_tbl.COUNT LOOP
314 OPEN C2_Task_Info(p_resource_assignment_id_tbl(i));
315 FETCH C2_Task_Info into l_task_name, l_task_number;
316 CLOSE C2_Task_Info;
317 IF l_task_name <> x_task_name THEN
318 x_task_name := 'Multiple';
319 x_task_number := 'Multiple';
320 EXIT;
321 END IF;
322 END LOOP;
323 END IF;
324
325 PA_DEBUG.write(x_module => L_Module,
326 x_msg => 'Got Task Name/Number',
327 x_log_level => 3);
328
329 --Obtain Resource Name
330 IF p_RBS_Element_Id IS NOT NULL THEN
331 --dbms_output.put_line('in sum test test6');
332 OPEN C1_Resource_Info(p_rbs_element_id);
333 FETCH C1_Resource_Info into x_resource_name;
334 CLOSE C1_Resource_Info;
335 END IF;
336
337 -- Check if Project Level WBS Node
338 IF (p_wbs_element_id IS NOT NULL) AND (p_wbs_structure_version_id IS NOT NULL) THEN
339 OPEN C_Get_Object_Type(p_wbs_element_id, p_wbs_structure_version_id);
340 FETCH C_Get_Object_Type into l_object_type;
341 CLOSE C_Get_Object_Type;
342
343 IF l_object_type = 'PA_STRUCTURES' THEN
344 l_project_level_node_flag := 'Y';
345 END IF;
346 END IF;
347
348 IF p_resource_assignment_id_tbl IS NOT NULL THEN
349 FOR j in 1..p_resource_assignment_id_tbl.COUNT LOOP
350 IF j > 1 THEN
351 l_predicate1 := l_predicate1 || ',' || p_resource_assignment_id_tbl(j);
352 ELSE
353 l_predicate1 := p_resource_assignment_id_tbl(j);
354 END IF;
355 END LOOP;
356 END IF;
357
358 IF p_txn_currency_code_tbl IS NOT NULL THEN
359 FOR j in 1..p_txn_currency_code_tbl.COUNT LOOP
360 IF j > 1 THEN
361 l_curr_code_predicate := l_curr_code_predicate || ',''' || p_txn_currency_code_tbl(j) || '''';
362 ELSE
363 l_curr_code_predicate := '''' || p_txn_currency_code_tbl(j) || '''';
364 END IF;
365 END LOOP;
366 END IF;
367
368 PA_DEBUG.write(x_module => L_Module,
369 x_msg => 'Got Resource Info',
370 x_log_level => 3);
371
372 IF l_predicate1 is not null and p_txn_currency_code_tbl.COUNT > 0 THEN
373 l_sql :=
374 ' SELECT BL.RESOURCE_ASSIGNMENT_ID, BL.TXN_CURRENCY_CODE, ' ||
375 ' NVL(SUM(BL.RAW_COST),0) PFC_RAW_COST,' ||
376 ' NVL(SUM(BL.BURDENED_COST),0) PFC_BURDENED_COST,' ||
377 ' NVL(SUM(BL.PROJECT_RAW_COST),0) PC_RAW_COST, ' ||
378 ' NVL(SUM(BL.PROJECT_BURDENED_COST),0) PC_BURDENED_COST, ' ;
379
380 l_sql := l_sql ||
381 ' NVL(SUM(PA_FP_ADJUSTMENT_UTILS.revenue(bl.budget_version_id, :1, :2, ' ||
382 ' BL.PROJECT_REVENUE)),0) PC_REVENUE, ' ||
383 ' NVL(SUM(PA_FP_ADJUSTMENT_UTILS.revenue(bl.budget_version_id, :3, :4, ' ||
384 ' BL.REVENUE)),0) PFC_REVENUE, ' ;
385
386 l_sql := l_sql ||
387 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(bl.budget_version_id, :5, null, :6, :7, ' ||
388 ' ra.resource_class_code, bl.quantity, ra.rate_based_flag)),0),2) TOTAL_LABOR_HOURS, ' ||
389 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(bl.budget_version_id, :8, null, :9, :10, ' ||
390 ' ra.resource_class_code, bl.quantity, ra.rate_based_flag)),0),2) TOTAL_EQUIP_HOURS, ' ||
391 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(bl.budget_version_id, null, :11, :12, :13, ' ||
392 ' ra.resource_class_code, bl.quantity, ra.rate_based_flag)),0),2) TOTAL_REV_LABOR_HOURS, ' ||
393 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(bl.budget_version_id, null, :14, :15, :16, ' ||
394 ' ra.resource_class_code, bl.quantity, ra.rate_based_flag)),0),2) TOTAL_REV_EQUIP_HOURS ' ;
395
396 l_sql := l_sql || ' FROM PA_BUDGET_LINES BL, PA_RESOURCE_ASSIGNMENTS RA ' ||
397 ' where bl.resource_assignment_id = ra.resource_assignment_id ' ||
398 ' and bl.resource_assignment_id in ( ' || l_predicate1 || ' ) ' ||
399 ' and bl.txn_currency_code in ( ' || l_curr_code_predicate || ' ) ' ||
400 ' group by BL.RESOURCE_ASSIGNMENT_ID, BL.TXN_CURRENCY_CODE ';
401
402 OPEN l_cur FOR l_sql using p_cost_budget_version_id, p_rev_budget_version_id,
403 p_cost_budget_version_id, p_rev_budget_version_id,
404 p_cost_budget_version_id, l_report_using, l_labor_res_class,
405 p_cost_budget_version_id, l_report_using, l_equip_res_class,
406 p_rev_budget_version_id, l_report_using, l_labor_res_class,
407 p_rev_budget_version_id, l_report_using, l_equip_res_class;
408 LOOP
409
410 FETCH l_cur INTO l_resource_assignment_id, l_currency_code,
411 l_pfc_raw_cost, l_pfc_burdened_cost, l_pc_raw_cost,
412 l_pc_burdened_cost, l_pc_revenue, l_pfc_revenue,
413 l_labor_hours, l_equip_hours,
414 l_rev_labor_hours, l_rev_equip_hours;
415
416 EXIT WHEN l_cur%NOTFOUND;
417
418 FOR j in 1..p_resource_assignment_id_tbl.COUNT LOOP
419 IF l_resource_assignment_id = p_resource_assignment_id_tbl(j) AND
420 l_currency_code = p_txn_currency_code_tbl(j) THEN
421 l_pfc_raw_cost_total := l_pfc_raw_cost_total + l_pfc_raw_cost;
422 l_pfc_burdened_cost_total := l_pfc_burdened_cost_total + l_pfc_burdened_cost;
423 l_pc_raw_cost_total := l_pc_raw_cost_total + l_pc_raw_cost;
424 l_pc_burdened_cost_total := l_pc_burdened_cost_total + l_pc_burdened_cost;
425 l_pc_revenue_total := l_pc_revenue_total + l_pc_revenue;
426 l_pfc_revenue_total := l_pfc_revenue_total + l_pfc_revenue;
427 l_labor_hours_total := l_labor_hours_total + l_labor_hours;
428 l_equip_hours_total := l_equip_hours_total + l_equip_hours;
429 l_rev_labor_hours_total := l_rev_labor_hours_total + l_rev_labor_hours;
430 l_rev_equip_hours_total := l_rev_equip_hours_total + l_rev_equip_hours;
431
432 EXIT;
433 END IF;
434 END LOOP;
435 END LOOP;
436
437 x_pfc_raw_cost := l_pfc_raw_cost_total;
438 x_pfc_burdened_cost := l_pfc_burdened_cost_total;
439 x_pc_raw_cost := l_pc_raw_cost_total;
440 x_pc_burdened_cost := l_pc_burdened_cost_total;
441 x_pc_revenue := l_pc_revenue_total;
442 x_pfc_revenue := l_pfc_revenue_total;
443 x_total_labor_hours := l_labor_hours_total;
444 x_total_equip_hours := l_equip_hours_total;
445 x_rev_total_labor_hours := l_rev_labor_hours_total;
446 x_rev_total_equip_hours := l_rev_equip_hours_total;
447
448 CLOSE l_cur;
449 ELSIF l_predicate1 is not null and p_txn_currency_code_tbl.COUNT = 0 THEN
450
451 l_sql :=
452 ' SELECT NVL(SUM(TOTAL_PLAN_RAW_COST),0) PFC_RAW_COST,' ||
453 ' NVL(SUM(TOTAL_PLAN_BURDENED_COST),0) PFC_BURDENED_COST,' ||
454 ' NVL(SUM(TOTAL_PROJECT_RAW_COST),0) PC_RAW_COST, ' ||
455 ' NVL(SUM(TOTAL_PROJECT_BURDENED_COST),0) PC_BURDENED_COST, ' ;
456
457 l_sql := l_sql ||
458 ' NVL(SUM(PA_FP_ADJUSTMENT_UTILS.revenue(budget_version_id, :1, :2, ' ||
459 ' TOTAL_PROJECT_REVENUE)),0) PC_REVENUE, ' ||
460 ' NVL(SUM(PA_FP_ADJUSTMENT_UTILS.revenue(budget_version_id, :3, :4, ' ||
461 ' TOTAL_PLAN_REVENUE)),0) PFC_REVENUE, ' ;
462
463 l_sql := l_sql ||
464 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(budget_version_id, :5, null, :6, :7, ' ||
465 ' resource_class_code, total_plan_quantity, rate_based_flag)),0),2) TOTAL_LABOR_HOURS, ' ||
466 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(budget_version_id, :8, null, :9, :10, ' ||
467 ' resource_class_code, total_plan_quantity, rate_based_flag)),0),2) TOTAL_EQUIP_HOURS, ' ||
468 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(budget_version_id, null, :11, :12, :13, ' ||
469 ' resource_class_code, total_plan_quantity, rate_based_flag)),0),2) TOTAL_REV_LABOR_HOURS, ' ||
470 ' ROUND(NVL(SUM(PA_FP_ADJUSTMENT_UTILS.class_hours(budget_version_id, null, :14, :15, :16, ' ||
471 ' resource_class_code, total_plan_quantity, rate_based_flag)),0),2) TOTAL_REV_EQUIP_HOURS ' ;
472
473 l_sql := l_sql || ' FROM PA_RESOURCE_ASSIGNMENTS ' ||
474 ' where resource_assignment_id in ( ';
475
476 l_sql := l_sql || l_predicate1 || ' )';
477
478 OPEN l_cur FOR l_sql using p_cost_budget_version_id, p_rev_budget_version_id,
479 p_cost_budget_version_id, p_rev_budget_version_id,
480 p_cost_budget_version_id, l_report_using, l_labor_res_class,
481 p_cost_budget_version_id, l_report_using, l_equip_res_class,
482 p_rev_budget_version_id, l_report_using, l_labor_res_class,
483 p_rev_budget_version_id, l_report_using, l_equip_res_class;
484
485 FETCH l_cur INTO x_pfc_raw_cost, x_pfc_burdened_cost, x_pc_raw_cost,
486 x_pc_burdened_cost, x_pc_revenue, x_pfc_revenue,
487 x_total_labor_hours, x_total_equip_hours,
488 x_rev_total_labor_hours, x_rev_total_equip_hours;
489 CLOSE l_cur;
490 ELSIF p_wbs_element_id is not null AND p_rbs_element_id IS NULL THEN
491
492 l_sql := 'SELECT ' ||
493 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, raw_cost, 0)),0) pfc_raw_cost, ' ||
494 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, brdn_cost, 0)),0) pfc_burdened_cost, ' ||
495 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, raw_cost, 0)),0) pc_raw_cost, ' ||
496 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, brdn_cost, 0)),0) pc_burdened_cost, ' ||
497 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, revenue, 0)),0) pc_revenue, ' ||
498 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, revenue, 0)),0) pfc_revenue, ' ||
499 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :1, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_labor_hours, ' ||
500 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :2, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_equip_hours, ' ||
501 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :3, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_rev_labor_hours, ' ||
502 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :4, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_rev_equip_hours ' ||
503 ' FROM ' ||
504 ' ( SELECT raw_cost, brdn_cost, revenue, labor_hrs, equipment_hours, ' ||
505 ' curr_record_type_id, plan_version_id ' ||
506 ' FROM pji_fp_xbs_accum_f ' ||
507 ' WHERE bitand(curr_record_type_id, 12) > 0 ' ||
508 ' AND calendar_type = ''A'' ' ||
509 ' AND prg_rollup_flag = ''N'' ' ||
510 ' AND project_id = :5 ' ||
511 ' AND plan_version_id IN (:6, :7) ';
512
513 l_sql := l_sql || ' AND project_element_id = :8 ' ||
514 ' AND rbs_element_id = -1 AND rbs_version_id = -1 ';
515
516 IF p_WBS_Rollup_Flag = 'Y' THEN
517 l_sql := l_sql || ' AND wbs_rollup_flag IN (''Y'',''N'') ';
518 ELSE
519 l_sql := l_sql || ' AND wbs_rollup_flag = ''N'' ';
520 END IF;
521
522 l_sql := l_sql || ' )';
523
524 OPEN l_cur FOR l_sql using p_cost_budget_version_id, p_cost_budget_version_id,
525 p_rev_budget_version_id, p_rev_budget_version_id,
526 p_project_id, p_cost_budget_version_id, p_rev_budget_version_id,
527 p_wbs_element_id;
528
529 FETCH l_cur INTO x_pfc_raw_cost, x_pfc_burdened_cost, x_pc_raw_cost,
530 x_pc_burdened_cost, x_pc_revenue, x_pfc_revenue,
531 x_total_labor_hours, x_total_equip_hours,
532 x_rev_total_labor_hours, x_rev_total_equip_hours;
533 CLOSE l_cur;
534 ELSIF p_wbs_element_id is null AND p_rbs_element_id IS NOT NULL THEN
535
536 l_sql := 'SELECT ' ||
537 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, raw_cost, 0)),0) pfc_raw_cost, ' ||
538 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, brdn_cost, 0),0)) pfc_burdened_cost, ' ||
539 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, raw_cost, 0)),0) pc_raw_cost, ' ||
540 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, brdn_cost, 0)),0) pc_burdened_cost, ' ||
541 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, revenue, 0)),0) pc_revenue, ' ||
542 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, revenue, 0)),0) pfc_revenue, ' ||
543 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :1, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_labor_hours, ' ||
544 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :2, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_equip_hours, ' ||
545 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :3, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_rev_labor_hours, ' ||
546 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :4, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_rev_equip_hours ' ||
547 ' FROM ' ||
548 ' ( SELECT raw_cost, brdn_cost, revenue, labor_hrs, equipment_hours, ' ||
549 ' curr_record_type_id, plan_version_id ' ||
550 ' FROM pji_fp_xbs_accum_f ' ||
551 ' WHERE bitand(curr_record_type_id, 12) > 0 ' ||
552 ' AND calendar_type = ''A'' ' ||
553 ' AND prg_rollup_flag = ''N'' ' ||
554 ' AND project_id = :5 ' ||
555 ' AND plan_version_id IN (:6, :7) ';
556
557 l_sql := l_sql ||
558 ' AND rbs_element_id = :8 ' ||
559 ' AND rbs_version_id = :9 ' ||
560 ' AND wbs_element_id = -1 ';
561
562 IF p_RBS_Rollup_Flag = 'Y' THEN
563 l_sql := l_sql || ' AND rbs_aggr_level IN (''R'',''L'') ';
564 ELSE
565 l_sql := l_sql || ' AND rbs_aggr_level = ''L'' ';
566 END IF;
567
568 l_sql := l_sql || ' )';
569
570 OPEN l_cur FOR l_sql using p_cost_budget_version_id, p_cost_budget_version_id,
571 p_rev_budget_version_id, p_rev_budget_version_id,
572 p_project_id, p_cost_budget_version_id, p_rev_budget_version_id,
573 p_rbs_element_id, p_rbs_version_id;
574
575 FETCH l_cur INTO x_pfc_raw_cost, x_pfc_burdened_cost, x_pc_raw_cost,
576 x_pc_burdened_cost, x_pc_revenue, x_pfc_revenue,
577 x_total_labor_hours, x_total_equip_hours,
578 x_rev_total_labor_hours, x_rev_total_equip_hours;
579 CLOSE l_cur;
580 ELSIF p_wbs_element_id is not null AND p_rbs_element_id IS NOT NULL THEN
581
582 l_sql := 'SELECT ' ||
583 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, raw_cost, 0)),0) pfc_raw_cost, ' ||
584 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, brdn_cost, 0)),0) pfc_burdened_cost, ' ||
585 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, raw_cost, 0)),0) pc_raw_cost, ' ||
586 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, brdn_cost, 0)),0) pc_burdened_cost, ' ||
587 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, revenue, 0)),0) pc_revenue, ' ||
588 ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, revenue, 0)),0) pfc_revenue, ' ||
589 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :1, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_labor_hours, ' ||
590 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :2, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_equip_hours, ' ||
591 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :3, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_rev_labor_hours, ' ||
592 ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :4, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_rev_equip_hours ' ||
593 ' FROM ' ||
594 ' ( SELECT raw_cost, brdn_cost, revenue, labor_hrs, equipment_hours, ' ||
595 ' curr_record_type_id, plan_version_id ' ||
596 ' FROM pji_fp_xbs_accum_f ' ||
597 ' WHERE bitand(curr_record_type_id, 12) > 0 ' ||
598 ' AND calendar_type = ''A'' ' ||
599 ' AND prg_rollup_flag = ''N'' ' ||
600 ' AND project_id = :5 ' ||
601 ' AND plan_version_id IN (:6, :7) ';
602
603 l_sql := l_sql ||
604 ' AND rbs_element_id = :8 ' ||
605 ' AND rbs_version_id = :9 ' ||
606 ' AND project_element_id = :10 ';
607
608 IF p_WBS_Rollup_Flag = 'Y' THEN
609 l_sql := l_sql || ' AND wbs_rollup_flag IN (''Y'',''N'') ';
610 ELSE
611 l_sql := l_sql || ' AND wbs_rollup_flag = ''N'' ';
612 END IF;
613
614 IF p_RBS_Rollup_Flag = 'Y' THEN
615 l_sql := l_sql || ' AND rbs_aggr_level IN (''R'',''L'') ';
616 ELSE
617 l_sql := l_sql || ' AND rbs_aggr_level = ''L'' ';
618 END IF;
619
620 l_sql := l_sql || ' )';
621
622 OPEN l_cur FOR l_sql using p_cost_budget_version_id, p_cost_budget_version_id,
623 p_rev_budget_version_id, p_rev_budget_version_id,
624 p_project_id, p_cost_budget_version_id, p_rev_budget_version_id,
625 p_rbs_element_id, p_rbs_version_id, p_wbs_element_id;
626
627 FETCH l_cur INTO x_pfc_raw_cost, x_pfc_burdened_cost, x_pc_raw_cost,
628 x_pc_burdened_cost, x_pc_revenue, x_pfc_revenue,
629 x_total_labor_hours, x_total_equip_hours,
630 x_rev_total_labor_hours, x_rev_total_equip_hours;
631 CLOSE l_cur;
632 END IF;
633
634 PA_DEBUG.write(x_module => L_Module,
635 x_msg => 'Fetched Data',
636 x_log_level => 3);
637
638 --Margin Calculation can be calculated using either Raw Cost or Burdened Cost.
639 --The selection will be based on the setup value of 'Report Cost Using':
640
641 --'B' - Burdened Cost,'R' - Raw Cost
642 --When Raw Cost is used in the calculation: Margin=(Revenue-Raw Cost)
643 --When Burdened Cost is used in the calculation: Margin=(Revenue-Burdened Cost)
644
645
646 IF l_margin_code = 'R' THEN
647 x_pc_margin := NVL(x_pc_revenue - x_pc_raw_cost,0);
648 x_pfc_margin := NVL(x_pfc_revenue - x_pfc_raw_cost,0);
649 ELSIF l_margin_code = 'B' THEN
650 x_pc_margin := NVL(x_pc_revenue - x_pc_burdened_cost,0);
651 x_pfc_margin := NVL(x_pfc_revenue - x_pfc_burdened_cost,0);
652 END IF;
653
654 IF x_pc_revenue <> 0 THEN
655 x_margin_percent := NVL((x_pc_margin/x_pc_revenue)*100,0);
656 END IF;
657
658 pa_debug.g_err_stage:='End of ' || L_FuncProc;
659 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 --dbms_output.put_line('Others Exception in ' || L_FuncProc);
664 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 6) THEN
665 PA_DEBUG.write_log (x_module => L_Module
666 ,x_msg => 'Unexp. Error:' || L_FuncProc || SQLERRM
667 ,x_log_level => 6);
668 END IF;
669 FND_MSG_PUB.add_exc_msg( p_pkg_name => L_Module,
670 p_procedure_name => L_FuncProc);
671 RAISE;
672 END GET_SUMMARY_INFO;
673
674
675 -- Purpose: Private Specific to compute relevant planning transaction id's affected in
676 -- computing summary amounts/adjusting plan via Adjust/Mass Adjust.
677 -- Called by Get_Summary_Info and AMG Adjust Interface API.
678
679 PROCEDURE COMPUTE_HIERARCHY(
680 p_cost_budget_version_id IN NUMBER
681 ,p_rev_budget_version_id IN NUMBER
682 ,p_WBS_Element_Id IN NUMBER DEFAULT NULL
683 ,p_RBS_Element_Id IN NUMBER DEFAULT NULL
684 ,p_WBS_Structure_Version_Id IN NUMBER DEFAULT NULL
685 ,p_RBS_Version_Id IN NUMBER DEFAULT NULL
686 ,p_WBS_Rollup_Flag IN VARCHAR2
687 ,p_RBS_Rollup_Flag IN VARCHAR2
688 ,X_res_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
689 ,X_txn_currency_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_15_tbl_type --File.Sql.39 bug 4440895
690 ,X_rev_res_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
691 ,X_rev_txn_currency_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_15_tbl_type --File.Sql.39 bug 4440895
692 ,X_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
693 ) IS
694 -- WBS only - Rollup
695 CURSOR C_WBS_RES_ID_ROLL(p_budget_version_id IN NUMBER, p_wbs_element_id IN NUMBER,
696 p_wbs_structure_version_id IN NUMBER) IS
697 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
698 from pji_xbs_denorm xbs, pa_resource_assignments ra, pa_budget_lines bl
699 where ra.resource_assignment_id = bl.resource_assignment_id
700 and xbs.sub_emt_id = ra.task_id
701 and ra.budget_version_id = p_budget_version_id
702 and xbs.sup_emt_id = p_wbs_element_id
703 and xbs.struct_version_id = p_wbs_structure_version_id
704 and xbs.struct_type in ('WBS','XBS');
705
706 -- WBS only - Self-node
707 CURSOR C_WBS_RES_ID_SELF(p_budget_version_id IN NUMBER, p_wbs_element_id IN NUMBER,
708 p_wbs_structure_version_id IN NUMBER) IS
709 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
710 from pji_xbs_denorm xbs, pa_resource_assignments ra, pa_budget_lines bl
711 where ra.resource_assignment_id = bl.resource_assignment_id
712 and xbs.sub_emt_id = ra.task_id
713 and ra.budget_version_id = p_budget_version_id
714 and xbs.sup_emt_id = p_wbs_element_id
715 and xbs.struct_version_id = p_wbs_structure_version_id
716 and xbs.struct_type in ('WBS','XBS')
717 and xbs.sub_level = xbs.sup_level;
718
719 -- WBS Only - Project Level Rollup
720 CURSOR C_WBS_RES_ID_PROJ_ROLL (p_budget_version_id IN NUMBER) IS
721 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
722 from pa_resource_assignments ra, pa_budget_lines bl
723 where ra.resource_assignment_id = bl.resource_assignment_id
724 and ra.budget_version_id = p_budget_version_id;
725
726 -- WBS Only - Project Level Self-node
727 CURSOR C_WBS_RES_ID_PROJ_SELF (p_budget_version_id IN NUMBER) IS
728 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
729 from pa_resource_assignments ra, pa_budget_lines bl
730 where ra.resource_assignment_id = bl.resource_assignment_id
731 and ra.budget_version_id = p_budget_version_id
732 and ra.task_id = 0;
733
734 -- RBS only - Rollup
735 CURSOR C_RBS_RES_ID_ROLL(p_budget_version_id IN NUMBER, p_rbs_element_id IN NUMBER,
736 p_rbs_version_id IN NUMBER) IS
737 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
738 from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
739 where ra.resource_assignment_id = bl.resource_assignment_id
740 and fp.fin_plan_version_id = ra.budget_version_id
741 and rbs.struct_version_id = fp.rbs_version_id
742 and rbs.sub_id = ra.rbs_element_id
743 and ra.budget_version_id = p_budget_version_id
744 and rbs.sup_id = p_rbs_element_id
745 and rbs.struct_version_id = p_rbs_version_id;
746
747 -- RBS only - Self-node
748 CURSOR C_RBS_RES_ID_SELF(p_budget_version_id IN NUMBER, p_rbs_element_id IN NUMBER,
749 p_rbs_version_id IN NUMBER) IS
750 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
751 from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
752 where ra.resource_assignment_id = bl.resource_assignment_id
753 and fp.fin_plan_version_id = ra.budget_version_id
754 and rbs.struct_version_id = fp.rbs_version_id
755 and rbs.sub_id = ra.rbs_element_id
756 and ra.budget_version_id = p_budget_version_id
757 and rbs.sup_id = p_rbs_element_id
758 and rbs.struct_version_id = p_rbs_version_id
759 and rbs.sub_level = rbs.sup_level;
760
761 -- Both - WBS Rollup RBS Rollup
762 CURSOR C_BOTH_RES_ID_ROLL_ROLL(p_budget_version_id IN NUMBER, p_wbs_element_id IN NUMBER,
763 p_rbs_element_id IN NUMBER, p_rbs_version_id IN NUMBER,
764 p_wbs_structure_version_id IN NUMBER) IS
765 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
766 from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
767 where ra.resource_assignment_id = bl.resource_assignment_id
768 and rbs.struct_version_id = fp.rbs_version_id
769 and rbs.sub_id = ra.rbs_element_id
770 and xbs.sub_emt_id = ra.task_id
771 and xbs.sup_project_id = fp.project_id
772 and fp.fin_plan_version_id = p_budget_version_id
773 and rbs.sup_id = p_rbs_element_id
774 and rbs.struct_version_id = p_rbs_version_id
775 and xbs.sup_emt_id = p_wbs_element_id
776 and xbs.struct_version_id = p_wbs_structure_version_id
777 and xbs.struct_type in ('WBS','XBS');
778
779 -- Both - WBS Rollup RBS Self-node
780 CURSOR C_BOTH_RES_ID_ROLL_SELF(p_budget_version_id IN NUMBER, p_wbs_element_id IN NUMBER,
781 p_rbs_element_id IN NUMBER, p_rbs_version_id IN NUMBER,
782 p_wbs_structure_version_id IN NUMBER) IS
783 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
784 from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
785 where ra.resource_assignment_id = bl.resource_assignment_id
786 and rbs.struct_version_id = fp.rbs_version_id
787 and rbs.sub_id = ra.rbs_element_id
788 and xbs.sub_emt_id = ra.task_id
789 and xbs.sup_project_id = fp.project_id
790 and fp.fin_plan_version_id = p_budget_version_id
791 and rbs.sup_id = p_rbs_element_id
792 and rbs.struct_version_id = p_rbs_version_id
793 and xbs.sup_emt_id = p_wbs_element_id
794 and xbs.struct_version_id = p_wbs_structure_version_id
795 and xbs.struct_type in ('WBS','XBS')
796 and rbs.sub_level = rbs.sup_level;
797
798 -- Both - WBS Self-node RBS Rollup
799 CURSOR C_BOTH_RES_ID_SELF_ROLL(p_budget_version_id IN NUMBER, p_wbs_element_id IN NUMBER,
800 p_rbs_element_id IN NUMBER, p_rbs_version_id IN NUMBER,
801 p_wbs_structure_version_id IN NUMBER) IS
802 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
803 from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
804 where ra.resource_assignment_id = bl.resource_assignment_id
805 and rbs.struct_version_id = fp.rbs_version_id
806 and rbs.sub_id = ra.rbs_element_id
807 and xbs.sub_emt_id = ra.task_id
808 and xbs.sup_project_id = fp.project_id
809 and fp.fin_plan_version_id = p_budget_version_id
810 and rbs.sup_id = p_rbs_element_id
811 and rbs.struct_version_id = p_rbs_version_id
812 and xbs.sup_emt_id = p_wbs_element_id
813 and xbs.struct_version_id = p_wbs_structure_version_id
814 and xbs.struct_type in ('WBS','XBS')
815 and xbs.sub_level = xbs.sup_level;
816
817 -- Both - WBS Self-node RBS Self-node
818 CURSOR C_BOTH_RES_ID_SELF_SELF(p_budget_version_id IN NUMBER, p_wbs_element_id IN NUMBER,
819 p_rbs_element_id IN NUMBER, p_rbs_version_id IN NUMBER,
820 p_wbs_structure_version_id IN NUMBER) IS
821 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
822 from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
823 where ra.resource_assignment_id = bl.resource_assignment_id
824 and rbs.struct_version_id = fp.rbs_version_id
825 and rbs.sub_id = ra.rbs_element_id
826 and xbs.sub_emt_id = ra.task_id
827 and xbs.sup_project_id = fp.project_id
828 and fp.fin_plan_version_id = p_budget_version_id
829 and rbs.sup_id = p_rbs_element_id
830 and rbs.struct_version_id = p_rbs_version_id
831 and xbs.sup_emt_id = p_wbs_element_id
832 and xbs.struct_version_id = p_wbs_structure_version_id
833 and xbs.struct_type in ('WBS','XBS')
834 and xbs.sub_level = xbs.sup_level
835 and rbs.sub_level = rbs.sup_level;
836
837 -- Both - WBS Project Level Self-node RBS Rollup
838 CURSOR C_BOTH_RES_ID_PROJ_ROLL(p_budget_version_id IN NUMBER, p_rbs_element_id IN NUMBER,
839 p_rbs_version_id IN NUMBER) IS
840 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
841 from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
842 where ra.resource_assignment_id = bl.resource_assignment_id
843 and fp.fin_plan_version_id = ra.budget_version_id
844 and rbs.struct_version_id = fp.rbs_version_id
845 and rbs.sub_id = ra.rbs_element_id
846 and ra.budget_version_id = p_budget_version_id
847 and rbs.sup_id = p_rbs_element_id
848 and rbs.struct_version_id = p_rbs_version_id
849 and ra.task_id = 0;
850
851 -- Both - WBS Project Level Self-node RBS Self-node
852 CURSOR C_BOTH_RES_ID_PROJ_SELF(p_budget_version_id IN NUMBER, p_rbs_element_id IN NUMBER,
853 p_rbs_version_id IN NUMBER) IS
854 Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
855 from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
856 where ra.resource_assignment_id = bl.resource_assignment_id
857 and fp.fin_plan_version_id = ra.budget_version_id
858 and rbs.struct_version_id = fp.rbs_version_id
859 and rbs.sub_id = ra.rbs_element_id
860 and ra.budget_version_id = p_budget_version_id
861 and rbs.sup_id = p_rbs_element_id
862 and rbs.struct_version_id = p_rbs_version_id
863 and rbs.sub_level = rbs.sup_level
864 and ra.task_id = 0;
865
866 CURSOR C_Get_Object_Type (p_wbs_element_id IN NUMBER, p_wbs_structure_version_id IN NUMBER) IS
867 SELECT object_type
868 FROM pa_proj_element_versions
869 WHERE proj_element_id = p_wbs_element_id
870 AND element_version_id = p_wbs_structure_version_id;
871
872 l_object_type pa_proj_element_versions.object_type%TYPE;
873 l_project_level_node_flag varchar2(1) := 'N';
874
875 l_t_id NUMBER;
876 l_t_code VARCHAR2(15);
877 l_cnt NUMBER;
878
879 BEGIN
880
881 L_FuncProc := 'Compute_Hierarchy';
882 x_return_status := FND_API.G_RET_STS_SUCCESS;
883
884 pa_debug.g_err_stage:='Beginning of ' || L_FuncProc;
885 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
886
887 x_res_assignment_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
888 x_txn_currency_code_tbl := SYSTEM.pa_varchar2_15_tbl_type();
889 x_rev_res_assignment_id_tbl := SYSTEM.PA_NUM_TBL_TYPE();
890 x_rev_txn_currency_code_tbl := SYSTEM.pa_varchar2_15_tbl_type();
891
892 -- Check if Project Level WBS Node
893 IF (p_wbs_element_id IS NOT NULL) AND (p_wbs_structure_version_id IS NOT NULL) THEN
894 OPEN C_Get_Object_Type(p_wbs_element_id, p_wbs_structure_version_id);
895 FETCH C_Get_Object_Type into l_object_type;
896 CLOSE C_Get_Object_Type;
897
898 IF l_object_type = 'PA_STRUCTURES' THEN
899 l_project_level_node_flag := 'Y';
900 END IF;
901 END IF;
902
903 IF P_Wbs_Element_Id is not null AND P_RBS_Element_Id is NULL THEN
904
905 IF nvl(p_cost_budget_version_id,-1) <> -1 THEN
906 IF l_project_level_node_flag = 'Y' THEN
907 IF p_WBS_Rollup_Flag = 'N' THEN
908 OPEN C_WBS_RES_ID_PROJ_SELF(p_cost_budget_version_id);
909 l_cnt := 0;
910 LOOP
911 FETCH C_WBS_RES_ID_PROJ_SELF INTO l_t_id, l_t_code;
912 EXIT WHEN C_WBS_RES_ID_PROJ_SELF%NOTFOUND;
913 x_res_assignment_id_tbl.extend(1);
914 x_txn_currency_code_tbl.extend(1);
915 l_cnt := l_cnt + 1;
916 x_res_assignment_id_tbl(l_cnt) := l_t_id;
917 x_txn_currency_code_tbl(l_cnt) := l_t_code;
918 END LOOP;
919 CLOSE C_WBS_RES_ID_PROJ_SELF;
920 ELSE
921 OPEN C_WBS_RES_ID_PROJ_ROLL(p_cost_budget_version_id);
922 l_cnt := 0;
923 LOOP
924 FETCH C_WBS_RES_ID_PROJ_ROLL INTO l_t_id, l_t_code;
925 EXIT WHEN C_WBS_RES_ID_PROJ_ROLL%NOTFOUND;
926 x_res_assignment_id_tbl.extend(1);
927 x_txn_currency_code_tbl.extend(1);
928 l_cnt := l_cnt + 1;
929 x_res_assignment_id_tbl(l_cnt) := l_t_id;
930 x_txn_currency_code_tbl(l_cnt) := l_t_code;
931 END LOOP;
932 CLOSE C_WBS_RES_ID_PROJ_ROLL;
933 END IF;
934 ELSE
935 IF p_WBS_Rollup_Flag = 'N' THEN
936 OPEN C_WBS_RES_ID_SELF(p_cost_budget_version_id, p_wbs_element_id, p_wbs_structure_version_id);
937 l_cnt := 0;
938 LOOP
939 FETCH C_WBS_RES_ID_SELF INTO l_t_id, l_t_code;
940 EXIT WHEN C_WBS_RES_ID_SELF%NOTFOUND;
941 x_res_assignment_id_tbl.extend(1);
942 x_txn_currency_code_tbl.extend(1);
943 l_cnt := l_cnt + 1;
944 x_res_assignment_id_tbl(l_cnt) := l_t_id;
945 x_txn_currency_code_tbl(l_cnt) := l_t_code;
946 END LOOP;
947 CLOSE C_WBS_RES_ID_SELF;
948 ELSE
949 OPEN C_WBS_RES_ID_ROLL(p_cost_budget_version_id, p_wbs_element_id, p_wbs_structure_version_id);
950 l_cnt := 0;
951 LOOP
952 FETCH C_WBS_RES_ID_ROLL INTO l_t_id, l_t_code;
953 EXIT WHEN C_WBS_RES_ID_ROLL%NOTFOUND;
954 x_res_assignment_id_tbl.extend(1);
955 x_txn_currency_code_tbl.extend(1);
956 l_cnt := l_cnt + 1;
957 x_res_assignment_id_tbl(l_cnt) := l_t_id;
958 x_txn_currency_code_tbl(l_cnt) := l_t_code;
959 END LOOP;
960 CLOSE C_WBS_RES_ID_ROLL;
961 END IF;
962 END IF;
963 END IF;
964
965 IF nvl(p_rev_budget_version_id,-1) <> -1 THEN
966 IF l_project_level_node_flag = 'Y' THEN
967 IF p_WBS_Rollup_Flag = 'N' THEN
968 OPEN C_WBS_RES_ID_PROJ_SELF(p_rev_budget_version_id);
969 l_cnt := 0;
970 LOOP
971 FETCH C_WBS_RES_ID_PROJ_SELF INTO l_t_id, l_t_code;
972 EXIT WHEN C_WBS_RES_ID_PROJ_SELF%NOTFOUND;
973 x_rev_res_assignment_id_tbl.extend(1);
974 x_rev_txn_currency_code_tbl.extend(1);
975 l_cnt := l_cnt + 1;
976 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
977 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
978 END LOOP;
979 CLOSE C_WBS_RES_ID_PROJ_SELF;
980 ELSE
981 OPEN C_WBS_RES_ID_PROJ_ROLL(p_rev_budget_version_id);
982 l_cnt := 0;
983 LOOP
984 FETCH C_WBS_RES_ID_PROJ_ROLL INTO l_t_id, l_t_code;
985 EXIT WHEN C_WBS_RES_ID_PROJ_ROLL%NOTFOUND;
986 x_rev_res_assignment_id_tbl.extend(1);
987 x_rev_txn_currency_code_tbl.extend(1);
988 l_cnt := l_cnt + 1;
989 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
990 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
991 END LOOP;
992 CLOSE C_WBS_RES_ID_PROJ_ROLL;
993 END IF;
994 ELSE
995 IF p_WBS_Rollup_Flag = 'N' THEN
996 OPEN C_WBS_RES_ID_SELF(p_rev_budget_version_id, p_wbs_element_id, p_wbs_structure_version_id);
997 l_cnt := 0;
998 LOOP
999 FETCH C_WBS_RES_ID_SELF INTO l_t_id, l_t_code;
1000 EXIT WHEN C_WBS_RES_ID_SELF%NOTFOUND;
1001 x_rev_res_assignment_id_tbl.extend(1);
1002 x_rev_txn_currency_code_tbl.extend(1);
1003 l_cnt := l_cnt + 1;
1004 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1005 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1006 END LOOP;
1007 CLOSE C_WBS_RES_ID_SELF;
1008 ELSE
1009 OPEN C_WBS_RES_ID_ROLL(p_rev_budget_version_id, p_wbs_element_id, p_wbs_structure_version_id);
1010 l_cnt := 0;
1011 LOOP
1012 FETCH C_WBS_RES_ID_ROLL INTO l_t_id, l_t_code;
1013 EXIT WHEN C_WBS_RES_ID_ROLL%NOTFOUND;
1014 x_rev_res_assignment_id_tbl.extend(1);
1015 x_rev_txn_currency_code_tbl.extend(1);
1016 l_cnt := l_cnt + 1;
1017 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1018 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1019 END LOOP;
1020 CLOSE C_WBS_RES_ID_ROLL;
1021 END IF;
1022 END IF;
1023 END IF;
1024
1025 ELSIF P_Rbs_Element_Id is not null AND P_WBS_Element_Id is NULL THEN
1026
1027 IF nvl(p_cost_budget_version_id,-1) <> -1 THEN
1028 IF p_RBS_Rollup_Flag = 'N' THEN
1029 OPEN C_RBS_RES_ID_SELF(p_cost_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1030 l_cnt := 0;
1031 LOOP
1032 FETCH C_RBS_RES_ID_SELF INTO l_t_id, l_t_code;
1033 EXIT WHEN C_RBS_RES_ID_SELF%NOTFOUND;
1034 x_res_assignment_id_tbl.extend(1);
1035 x_txn_currency_code_tbl.extend(1);
1036 l_cnt := l_cnt + 1;
1037 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1038 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1039 END LOOP;
1040 CLOSE C_RBS_RES_ID_SELF;
1041 ELSE
1042 OPEN C_RBS_RES_ID_ROLL(p_cost_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1043 l_cnt := 0;
1044 LOOP
1045 FETCH C_RBS_RES_ID_ROLL INTO l_t_id, l_t_code;
1046 EXIT WHEN C_RBS_RES_ID_ROLL%NOTFOUND;
1047 x_res_assignment_id_tbl.extend(1);
1048 x_txn_currency_code_tbl.extend(1);
1049 l_cnt := l_cnt + 1;
1050 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1051 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1052 END LOOP;
1053 CLOSE C_RBS_RES_ID_ROLL;
1054 END IF;
1055 END IF;
1056
1057 IF nvl(p_rev_budget_version_id,-1) <> -1 THEN
1058 IF p_RBS_Rollup_Flag = 'N' THEN
1059 OPEN C_RBS_RES_ID_SELF(p_rev_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1060 l_cnt := 0;
1061 LOOP
1062 FETCH C_RBS_RES_ID_SELF INTO l_t_id, l_t_code;
1063 EXIT WHEN C_RBS_RES_ID_SELF%NOTFOUND;
1064 x_rev_res_assignment_id_tbl.extend(1);
1065 x_rev_txn_currency_code_tbl.extend(1);
1066 l_cnt := l_cnt + 1;
1067 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1068 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1069 END LOOP;
1070 CLOSE C_RBS_RES_ID_SELF;
1071 ELSE
1072 OPEN C_RBS_RES_ID_ROLL(p_rev_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1073 l_cnt := 0;
1074 LOOP
1075 FETCH C_RBS_RES_ID_ROLL INTO l_t_id, l_t_code;
1076 EXIT WHEN C_RBS_RES_ID_ROLL%NOTFOUND;
1077 x_rev_res_assignment_id_tbl.extend(1);
1078 x_rev_txn_currency_code_tbl.extend(1);
1079 l_cnt := l_cnt + 1;
1080 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1081 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1082 END LOOP;
1083 CLOSE C_RBS_RES_ID_ROLL;
1084 END IF;
1085 END IF;
1086
1087 ELSIF P_Wbs_Element_Id is not null AND P_RBS_Element_Id is NOT NULL THEN
1088
1089 IF nvl(p_cost_budget_version_id,-1) <> -1 THEN
1090 IF l_project_level_node_flag = 'Y' THEN
1091 IF p_WBS_Rollup_Flag = 'N' THEN
1092 IF p_RBS_Rollup_Flag = 'N' THEN
1093 OPEN C_BOTH_RES_ID_PROJ_SELF(p_cost_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1094 l_cnt := 0;
1095 LOOP
1096 FETCH C_BOTH_RES_ID_PROJ_SELF INTO l_t_id, l_t_code;
1097 EXIT WHEN C_BOTH_RES_ID_PROJ_SELF%NOTFOUND;
1098 x_res_assignment_id_tbl.extend(1);
1099 x_txn_currency_code_tbl.extend(1);
1100 l_cnt := l_cnt + 1;
1101 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1102 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1103 END LOOP;
1104 CLOSE C_BOTH_RES_ID_PROJ_SELF;
1105 ELSE
1106 OPEN C_BOTH_RES_ID_PROJ_ROLL(p_cost_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1107 l_cnt := 0;
1108 LOOP
1109 FETCH C_BOTH_RES_ID_PROJ_ROLL INTO l_t_id, l_t_code;
1110 EXIT WHEN C_BOTH_RES_ID_PROJ_ROLL%NOTFOUND;
1111 x_res_assignment_id_tbl.extend(1);
1112 x_txn_currency_code_tbl.extend(1);
1113 l_cnt := l_cnt + 1;
1114 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1115 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1116 END LOOP;
1117 CLOSE C_BOTH_RES_ID_PROJ_ROLL;
1118 END IF;
1119 ELSE
1120 IF p_RBS_Rollup_Flag = 'N' THEN
1121 OPEN C_RBS_RES_ID_SELF(p_cost_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1122 l_cnt := 0;
1123 LOOP
1124 FETCH C_RBS_RES_ID_SELF INTO l_t_id, l_t_code;
1125 EXIT WHEN C_RBS_RES_ID_SELF%NOTFOUND;
1126 x_res_assignment_id_tbl.extend(1);
1127 x_txn_currency_code_tbl.extend(1);
1128 l_cnt := l_cnt + 1;
1129 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1130 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1131 END LOOP;
1132 CLOSE C_RBS_RES_ID_SELF;
1133 ELSE
1134 OPEN C_RBS_RES_ID_ROLL(p_cost_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1135 l_cnt := 0;
1136 LOOP
1137 FETCH C_RBS_RES_ID_ROLL INTO l_t_id, l_t_code;
1138 EXIT WHEN C_RBS_RES_ID_ROLL%NOTFOUND;
1139 x_res_assignment_id_tbl.extend(1);
1140 x_txn_currency_code_tbl.extend(1);
1141 l_cnt := l_cnt + 1;
1142 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1143 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1144 END LOOP;
1145 CLOSE C_RBS_RES_ID_ROLL;
1146 END IF;
1147 END IF;
1148 ELSE
1149 IF p_WBS_Rollup_Flag = 'N' THEN
1150 IF p_RBS_Rollup_Flag = 'N' THEN
1151 OPEN C_BOTH_RES_ID_SELF_SELF(p_cost_budget_version_id, p_wbs_element_id,
1152 p_rbs_element_id, p_rbs_version_id,
1153 p_wbs_structure_version_id);
1154 l_cnt := 0;
1155 LOOP
1156 FETCH C_BOTH_RES_ID_SELF_SELF INTO l_t_id, l_t_code;
1157 EXIT WHEN C_BOTH_RES_ID_SELF_SELF%NOTFOUND;
1158 x_res_assignment_id_tbl.extend(1);
1159 x_txn_currency_code_tbl.extend(1);
1160 l_cnt := l_cnt + 1;
1161 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1162 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1163 END LOOP;
1164 CLOSE C_BOTH_RES_ID_SELF_SELF;
1165 ELSE
1166 OPEN C_BOTH_RES_ID_SELF_ROLL(p_cost_budget_version_id, p_wbs_element_id,
1167 p_rbs_element_id, p_rbs_version_id,
1168 p_wbs_structure_version_id);
1169 l_cnt := 0;
1170 LOOP
1171 FETCH C_BOTH_RES_ID_SELF_ROLL INTO l_t_id, l_t_code;
1172 EXIT WHEN C_BOTH_RES_ID_SELF_ROLL%NOTFOUND;
1173 x_res_assignment_id_tbl.extend(1);
1174 x_txn_currency_code_tbl.extend(1);
1175 l_cnt := l_cnt + 1;
1176 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1177 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1178 END LOOP;
1179 CLOSE C_BOTH_RES_ID_SELF_ROLL;
1180 END IF;
1181 ELSE
1182 IF p_RBS_Rollup_Flag = 'N' THEN
1183 OPEN C_BOTH_RES_ID_ROLL_SELF(p_cost_budget_version_id, p_wbs_element_id,
1184 p_rbs_element_id, p_rbs_version_id,
1185 p_wbs_structure_version_id);
1186 l_cnt := 0;
1187 LOOP
1188 FETCH C_BOTH_RES_ID_ROLL_SELF INTO l_t_id, l_t_code;
1189 EXIT WHEN C_BOTH_RES_ID_ROLL_SELF%NOTFOUND;
1190 x_res_assignment_id_tbl.extend(1);
1191 x_txn_currency_code_tbl.extend(1);
1192 l_cnt := l_cnt + 1;
1193 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1194 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1195 END LOOP;
1196 CLOSE C_BOTH_RES_ID_ROLL_SELF;
1197 ELSE
1198 OPEN C_BOTH_RES_ID_ROLL_ROLL(p_cost_budget_version_id, p_wbs_element_id,
1199 p_rbs_element_id, p_rbs_version_id,
1200 p_wbs_structure_version_id);
1201 l_cnt := 0;
1202 LOOP
1203 FETCH C_BOTH_RES_ID_ROLL_ROLL INTO l_t_id, l_t_code;
1204 EXIT WHEN C_BOTH_RES_ID_ROLL_ROLL%NOTFOUND;
1205 x_res_assignment_id_tbl.extend(1);
1206 x_txn_currency_code_tbl.extend(1);
1207 l_cnt := l_cnt + 1;
1208 x_res_assignment_id_tbl(l_cnt) := l_t_id;
1209 x_txn_currency_code_tbl(l_cnt) := l_t_code;
1210 END LOOP;
1211 CLOSE C_BOTH_RES_ID_ROLL_ROLL;
1212 END IF;
1213 END IF;
1214 END IF;
1215 END IF;
1216
1217 IF nvl(p_rev_budget_version_id,-1) <> -1 THEN
1218 IF l_project_level_node_flag = 'Y' THEN
1219 IF p_WBS_Rollup_Flag = 'N' THEN
1220 IF p_RBS_Rollup_Flag = 'N' THEN
1221 OPEN C_BOTH_RES_ID_PROJ_SELF(p_rev_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1222 l_cnt := 0;
1223 LOOP
1224 FETCH C_BOTH_RES_ID_PROJ_SELF INTO l_t_id, l_t_code;
1225 EXIT WHEN C_BOTH_RES_ID_PROJ_SELF%NOTFOUND;
1226 x_rev_res_assignment_id_tbl.extend(1);
1227 x_rev_txn_currency_code_tbl.extend(1);
1228 l_cnt := l_cnt + 1;
1229 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1230 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1231 END LOOP;
1232 CLOSE C_BOTH_RES_ID_PROJ_SELF;
1233 ELSE
1234 OPEN C_BOTH_RES_ID_PROJ_ROLL(p_rev_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1235 l_cnt := 0;
1236 LOOP
1237 FETCH C_BOTH_RES_ID_PROJ_ROLL INTO l_t_id, l_t_code;
1238 EXIT WHEN C_BOTH_RES_ID_PROJ_ROLL%NOTFOUND;
1239 x_rev_res_assignment_id_tbl.extend(1);
1240 x_rev_txn_currency_code_tbl.extend(1);
1241 l_cnt := l_cnt + 1;
1242 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1243 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1244 END LOOP;
1245 CLOSE C_BOTH_RES_ID_PROJ_ROLL;
1246 END IF;
1247 ELSE
1248 IF p_RBS_Rollup_Flag = 'N' THEN
1249 OPEN C_RBS_RES_ID_SELF(p_rev_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1250 l_cnt := 0;
1251 LOOP
1252 FETCH C_RBS_RES_ID_SELF INTO l_t_id, l_t_code;
1253 EXIT WHEN C_RBS_RES_ID_SELF%NOTFOUND;
1254 x_rev_res_assignment_id_tbl.extend(1);
1255 x_rev_txn_currency_code_tbl.extend(1);
1256 l_cnt := l_cnt + 1;
1257 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1258 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1259 END LOOP;
1260 CLOSE C_RBS_RES_ID_SELF;
1261 ELSE
1262 OPEN C_RBS_RES_ID_ROLL(p_rev_budget_version_id, p_rbs_element_id, p_rbs_version_id);
1263 l_cnt := 0;
1264 LOOP
1265 FETCH C_RBS_RES_ID_ROLL INTO l_t_id, l_t_code;
1266 EXIT WHEN C_RBS_RES_ID_ROLL%NOTFOUND;
1267 x_rev_res_assignment_id_tbl.extend(1);
1268 x_rev_txn_currency_code_tbl.extend(1);
1269 l_cnt := l_cnt + 1;
1270 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1271 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1272 END LOOP;
1273 CLOSE C_RBS_RES_ID_ROLL;
1274 END IF;
1275 END IF;
1276 ELSE
1277 IF p_WBS_Rollup_Flag = 'N' THEN
1278 IF p_RBS_Rollup_Flag = 'N' THEN
1279 OPEN C_BOTH_RES_ID_SELF_SELF(p_rev_budget_version_id, p_wbs_element_id,
1280 p_rbs_element_id, p_rbs_version_id,
1281 p_wbs_structure_version_id);
1282 l_cnt := 0;
1283 LOOP
1284 FETCH C_BOTH_RES_ID_SELF_SELF INTO l_t_id, l_t_code;
1285 EXIT WHEN C_BOTH_RES_ID_SELF_SELF%NOTFOUND;
1286 x_rev_res_assignment_id_tbl.extend(1);
1287 x_rev_txn_currency_code_tbl.extend(1);
1288 l_cnt := l_cnt + 1;
1289 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1290 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1291 END LOOP;
1292 CLOSE C_BOTH_RES_ID_SELF_SELF;
1293 ELSE
1294 OPEN C_BOTH_RES_ID_SELF_ROLL(p_rev_budget_version_id, p_wbs_element_id,
1295 p_rbs_element_id, p_rbs_version_id,
1296 p_wbs_structure_version_id);
1297 l_cnt := 0;
1298 LOOP
1299 FETCH C_BOTH_RES_ID_SELF_ROLL INTO l_t_id, l_t_code;
1300 EXIT WHEN C_BOTH_RES_ID_SELF_ROLL%NOTFOUND;
1301 x_rev_res_assignment_id_tbl.extend(1);
1302 x_rev_txn_currency_code_tbl.extend(1);
1303 l_cnt := l_cnt + 1;
1304 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1305 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1306 END LOOP;
1307 CLOSE C_BOTH_RES_ID_SELF_ROLL;
1308 END IF;
1309 ELSE
1310 IF p_RBS_Rollup_Flag = 'N' THEN
1311 OPEN C_BOTH_RES_ID_ROLL_SELF(p_rev_budget_version_id, p_wbs_element_id,
1312 p_rbs_element_id, p_rbs_version_id,
1313 p_wbs_structure_version_id);
1314 l_cnt := 0;
1315 LOOP
1316 FETCH C_BOTH_RES_ID_ROLL_SELF INTO l_t_id, l_t_code;
1317 EXIT WHEN C_BOTH_RES_ID_ROLL_SELF%NOTFOUND;
1318 x_rev_res_assignment_id_tbl.extend(1);
1319 x_rev_txn_currency_code_tbl.extend(1);
1320 l_cnt := l_cnt + 1;
1321 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1322 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1323 END LOOP;
1324 CLOSE C_BOTH_RES_ID_ROLL_SELF;
1325 ELSE
1326 OPEN C_BOTH_RES_ID_ROLL_ROLL(p_rev_budget_version_id, p_wbs_element_id,
1327 p_rbs_element_id, p_rbs_version_id,
1328 p_wbs_structure_version_id);
1329 l_cnt := 0;
1330 LOOP
1331 FETCH C_BOTH_RES_ID_ROLL_ROLL INTO l_t_id, l_t_code;
1332 EXIT WHEN C_BOTH_RES_ID_ROLL_ROLL%NOTFOUND;
1333 x_rev_res_assignment_id_tbl.extend(1);
1334 x_rev_txn_currency_code_tbl.extend(1);
1335 l_cnt := l_cnt + 1;
1336 x_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1337 x_rev_txn_currency_code_tbl(l_cnt) := l_t_code;
1338 END LOOP;
1339 CLOSE C_BOTH_RES_ID_ROLL_ROLL;
1340 END IF;
1341 END IF;
1342 END IF;
1343 END IF;
1344
1345 END IF;
1346
1347 pa_debug.g_err_stage:='End of ' || L_FuncProc;
1348 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1349
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 6) THEN
1353 PA_DEBUG.write_log (x_module => L_Module
1354 ,x_msg => 'Unexp. Error:' || L_FuncProc || SQLERRM
1355 ,x_log_level => 6);
1356 END IF;
1357 FND_MSG_PUB.add_exc_msg( p_pkg_name => L_Module,
1358 p_procedure_name => L_FuncProc);
1359 RAISE;
1360 END COMPUTE_HIERARCHY;
1361
1362
1363 -- This procedure will Adjust the relevant Planning transactions based on a percentage
1364 -- for the relevant parameters
1365 --
1366 PROCEDURE Adjust_Planning_Transactions
1367 (
1368 p_Project_Id IN NUMBER
1369 ,p_Context IN VARCHAR2
1370 ,p_user_id IN NUMBER DEFAULT FND_GLOBAL.USER_ID
1371 ,p_cost_budget_version_id IN NUMBER
1372 ,p_rev_budget_version_id IN NUMBER DEFAULT NULL
1373 ,p_cost_fin_plan_type_id IN NUMBER
1374 ,p_cost_version_type IN VARCHAR2
1375 ,p_cost_plan_setup IN VARCHAR2
1376 ,p_rev_fin_plan_type_id IN NUMBER DEFAULT NULL
1377 ,p_rev_version_type IN VARCHAR2 DEFAULT NULL
1378 ,p_rev_plan_setup IN VARCHAR2 DEFAULT NULL
1379 ,p_new_version_flag IN VARCHAR2 DEFAULT 'N'
1380 ,p_new_version_name IN VARCHAR2 DEFAULT NULL
1381 ,p_new_version_desc IN VARCHAR2 DEFAULT NULL
1382 ,p_adjustment_type IN VARCHAR2 DEFAULT NULL
1383 ,p_adjustment_percentage IN NUMBER
1384 ,p_WBS_Element_Id IN NUMBER DEFAULT NULL
1385 ,p_RBS_Element_Id IN NUMBER DEFAULT NULL
1386 ,p_WBS_Structure_Version_Id IN NUMBER DEFAULT NULL
1387 ,p_RBS_Version_Id IN NUMBER DEFAULT NULL
1388 ,p_WBS_Rollup_Flag IN VARCHAR2
1389 ,p_RBS_Rollup_Flag IN VARCHAR2
1390 ,p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
1391 ,p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_15_tbl_type
1392 ,x_cost_budget_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1393 ,x_rev_budget_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1394 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1395 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1396 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1397 IS
1398
1399 TYPE DYNAMIC_CUR IS REF CURSOR;
1400 l_cur DYNAMIC_CUR;
1401 l_sql VARCHAR2(32767);
1402 l_predicate1 VARCHAR2(32767);
1403
1404 l_cur2 DYNAMIC_CUR;
1405 l_sql2 VARCHAR2(32767);
1406 l_predicate2 VARCHAR2(32767);
1407
1408 l_cur3 DYNAMIC_CUR;
1409 l_sql3 VARCHAR2(32767);
1410 l_predicate3 VARCHAR2(32767);
1411
1412 l_quantity_adj_pct NUMBER;
1413 l_cost_rate_adj_pct NUMBER;
1414 l_burdened_rate_adj_pct NUMBER;
1415 l_bill_rate_adj_pct NUMBER;
1416 /* IPM changes */
1417 l_raw_cost_adj_pct NUMBER;
1418 l_burden_cost_adj_pct NUMBER;
1419 l_revenue_adj_pct NUMBER;
1420
1421 L_res_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
1422 L_txn_curr_code_tbl SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
1423 L_rev_res_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
1424 L_rev_txn_curr_code_tbl SYSTEM.pa_varchar2_15_tbl_type:=SYSTEM.pa_varchar2_15_tbl_type();
1425
1426 l_t_id NUMBER;
1427 l_t_code VARCHAR2(15);
1428 l_cnt NUMBER;
1429
1430 l_cost_budget_version_id NUMBER;
1431 l_rev_budget_version_id NUMBER;
1432 l_target_budget_version_id NUMBER;
1433 l_target_rev_version_id NUMBER;
1434
1435 l_target_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
1436 l_target_rev_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
1437
1438 api_exception EXCEPTION ;
1439
1440 l_struct_ver_id NUMBER;
1441
1442 l_locked_by_user_flag VARCHAR2(1);
1443 l_locked_by_person_id NUMBER;
1444 l_editable_flag VARCHAR2(1);
1445
1446 r_locked_by_user_flag VARCHAR2(1);
1447 r_locked_by_person_id NUMBER;
1448 r_editable_flag VARCHAR2(1);
1449
1450 l_user_id NUMBER;
1451 BEGIN
1452
1453 l_user_id := FND_GLOBAL.USER_ID;
1454
1455 L_FuncProc := 'Adjust_Planning_Transactions';
1456 x_return_status := FND_API.G_RET_STS_SUCCESS;
1457
1458 pa_debug.g_err_stage:='Beginning of ' || L_FuncProc;
1459 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1460
1461 IF p_adjustment_type = 'QUANTITY' THEN
1462 l_quantity_adj_pct := p_adjustment_percentage;
1463 ELSIF p_adjustment_type = 'COST_QUANTITY' THEN
1464 l_quantity_adj_pct := p_adjustment_percentage;
1465 ELSIF p_adjustment_type = 'REVENUE_QUANTITY' THEN
1466 l_quantity_adj_pct := p_adjustment_percentage;
1467 ELSIF p_adjustment_type = 'COST_RATE' THEN
1468 l_cost_rate_adj_pct := p_adjustment_percentage;
1469 ELSIF p_adjustment_type = 'BILL_RATE' THEN
1470 l_bill_rate_adj_pct := p_adjustment_percentage;
1471 ELSIF p_adjustment_type = 'BURDENED_RATE' THEN
1472 l_burdened_rate_adj_pct := p_adjustment_percentage;
1473 /* IPM changes */
1474 ELSIF p_adjustment_type = 'RAW_COST' THEN
1475 l_raw_cost_adj_pct := p_adjustment_percentage;
1476 ELSIF p_adjustment_type = 'BURDENED_COST' THEN
1477 l_burden_cost_adj_pct := p_adjustment_percentage;
1478 ELSIF p_adjustment_type = 'REVENUE' THEN
1479 l_revenue_adj_pct := p_adjustment_percentage;
1480 ELSE
1481 RETURN;
1482 END IF;
1483
1484 pa_debug.g_err_stage:='p_project_id ' || p_project_id;
1485 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1486 pa_debug.g_err_stage:= L_FuncProc || ' p_context: ' || p_context;
1487 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1488 pa_debug.g_err_stage:= L_FuncProc || ' p_user_id: ' || p_user_id;
1489 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1490 pa_debug.g_err_stage:= L_FuncProc || ' p_cost_budget_version_id: ' || p_cost_budget_version_id;
1491 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1492 pa_debug.g_err_stage:= L_FuncProc || ' p_rev_budget_version_id: ' || p_rev_budget_version_id;
1493 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1494 pa_debug.g_err_stage:= L_FuncProc || ' p_cost_fin_plan_type_id: ' || p_cost_fin_plan_type_id;
1495 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1496 pa_debug.g_err_stage:= L_FuncProc || ' p_cost_version_type: ' || p_cost_version_type;
1497 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1498 pa_debug.g_err_stage:= L_FuncProc || ' p_rev_fin_plan_type_id: ' || p_rev_fin_plan_type_id;
1499 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1500 pa_debug.g_err_stage:= L_FuncProc || ' p_rev_version_type: ' || p_rev_version_type;
1501 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1502 pa_debug.g_err_stage:= L_FuncProc || ' p_new_version_flag: ' || p_new_version_flag;
1503 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1504 pa_debug.g_err_stage:= L_FuncProc || ' p_new_version_name: ' || p_new_version_name;
1505 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1506 pa_debug.g_err_stage:= L_FuncProc || ' p_new_version_desc: ' || p_new_version_desc;
1507 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1508 pa_debug.g_err_stage:= L_FuncProc || ' p_adjustment_type: ' || p_adjustment_type;
1509 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1510 pa_debug.g_err_stage:= L_FuncProc || ' p_adjustment_percentage: ' || p_adjustment_percentage;
1511 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1512 pa_debug.g_err_stage:= L_FuncProc || ' p_WBS_Element_Id: ' || p_WBS_Element_Id;
1513 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1514 pa_debug.g_err_stage:= L_FuncProc || ' p_RBS_Element_Id: ' || p_RBS_Element_Id;
1515 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1516 pa_debug.g_err_stage:= L_FuncProc || ' p_WBS_Structure_Version_Id: ' || p_WBS_Structure_Version_Id;
1517 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1518 pa_debug.g_err_stage:= L_FuncProc || ' p_RBS_Version_Id: ' || p_RBS_Version_Id;
1519 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1520 IF p_resource_assignment_id_tbl.COUNT > 0 THEN
1521 FOR i IN p_resource_assignment_id_tbl.FIRST .. p_resource_assignment_id_tbl.LAST LOOP
1522 pa_debug.g_err_stage:= L_FuncProc || ' p_resource_assignment_id_tbl(' || i || '): ' || p_resource_assignment_id_tbl(i);
1523 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1524 END LOOP;
1525 ELSE
1526 pa_debug.g_err_stage:= L_FuncProc || ' p_resource_assignment_id_tbl is EMPTY';
1527 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1528 END IF;
1529 IF p_txn_currency_code_tbl.COUNT > 0 THEN
1530 FOR i IN p_txn_currency_code_tbl.FIRST .. p_txn_currency_code_tbl.LAST LOOP
1531 pa_debug.g_err_stage:= L_FuncProc || ' p_txn_currency_code_tbl(' || i || '): ' || p_txn_currency_code_tbl(i);
1532 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1533 END LOOP;
1534 ELSE
1535 pa_debug.g_err_stage:= L_FuncProc || ' p_txn_currency_code_tbl is EMPTY';
1536 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1537 END IF;
1538
1539 IF P_CONTEXT='WORKPLAN' THEN
1540 IF 'Y' <> pa_task_assignment_utils.check_edit_task_ok(
1541 P_PROJECT_ID => P_project_id,
1542 P_STRUCTURE_VERSION_ID => p_wbs_structure_version_id,
1543 P_CURR_STRUCT_VERSION_ID => p_wbs_structure_version_id
1544 ) THEN
1545 -- Bug 4533152
1546 --PA_UTILS.ADD_MESSAGE
1547 -- (p_app_short_name => 'PA',
1548 -- p_msg_name => 'PA_UPDATE_PUB_VER_ERR'
1549 -- );
1550 x_return_status := FND_API.G_RET_STS_ERROR;
1551 RETURN;
1552 ELSE
1553 l_locked_by_user_flag := 'Y';
1554 END IF;
1555 ELSIF P_CONTEXT IN ('BUDGET', 'FORECAST') THEN
1556 IF nvl(p_cost_budget_version_id, -1) <> -1 THEN
1557
1558 pa_debug.g_err_stage:= L_FuncProc || ' l_user_id: ' || l_user_id;
1559 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1560
1561 pa_fin_plan_utils.Check_Locked_By_User
1562 (p_user_id => l_user_id,
1563 p_budget_version_id => p_cost_budget_version_id,
1564 x_is_locked_by_userid => l_locked_by_user_flag,
1565 x_locked_by_person_id => l_locked_by_person_id,
1566 x_return_status => x_return_status,
1567 x_msg_count => x_msg_count,
1568 x_msg_data => x_msg_data);
1569
1570 pa_debug.g_err_stage:= L_FuncProc || ' x_return_status: ' || x_return_status;
1571 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1572
1573 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1574 pa_debug.g_err_stage:='Rasing API_EXCEPTION 1';
1575 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1576 RAISE api_exception;
1577 END IF;
1578
1579 pa_fin_plan_utils.CHECK_IF_PLAN_TYPE_EDITABLE
1580 (p_project_id => p_project_id,
1581 p_fin_plan_type_id => p_cost_fin_plan_type_id,
1582 p_version_type => p_cost_version_type,
1583 x_editable_flag => l_editable_flag,
1584 x_return_status => x_return_status,
1585 x_msg_count => x_msg_count,
1586 x_msg_data => x_msg_data);
1587
1588 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1589 pa_debug.g_err_stage:='Rasing API_EXCEPTION 2';
1590 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1591 RAISE api_exception;
1592 END IF;
1593 END IF;
1594
1595 IF l_locked_by_person_id is null then
1596 l_locked_by_user_flag := 'Y'; -- unlocked is equivalent to locked by user
1597 END IF;
1598
1599 IF nvl(P_rev_budget_version_id, -1) = -1 AND
1600 (l_locked_by_user_flag <> 'Y' OR l_editable_flag <> 'Y') THEN
1601
1602 PA_UTILS.ADD_MESSAGE
1603 (p_app_short_name => 'PA',
1604 p_msg_name => 'PA_NO_ACCESS_TO_UPDATE');
1605
1606 pa_debug.reset_err_stack;
1607 x_return_status := FND_API.G_RET_STS_ERROR;
1608 RETURN;
1609 ELSIF nvl(P_rev_budget_version_id, -1) <> -1 THEN
1610 -- No access to update for the cost version in terms of
1611 -- cost rate or burdened rate which applies to cost version only..
1612
1613 IF p_adjustment_type in ('COST_RATE', 'BURDENED_RATE', 'COST_QUANTITY','RAW_COST','BURDENED_COST') AND
1614 (l_locked_by_user_flag <> 'Y' OR l_editable_flag <> 'Y') THEN
1615
1616 PA_UTILS.ADD_MESSAGE
1617 (p_app_short_name => 'PA',
1618 p_msg_name => 'PA_NO_ACCESS_TO_UPDATE');
1619 pa_debug.reset_err_stack;
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 RETURN;
1622 END IF;
1623
1624 IF p_adjustment_type IN ( 'BILL_RATE', 'QUANTITY', 'REVENUE_QUANTITY','REVENUE') THEN
1625
1626 pa_fin_plan_utils.Check_Locked_By_User
1627 (p_user_id => l_user_id,
1628 p_budget_version_id => p_rev_budget_version_id,
1629 x_is_locked_by_userid => r_locked_by_user_flag,
1630 x_locked_by_person_id => r_locked_by_person_id,
1631 x_return_status => x_return_status,
1632 x_msg_count => x_msg_count,
1633 x_msg_data => x_msg_data);
1634
1635 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1636 pa_debug.g_err_stage:='Rasing API_EXCEPTION 3';
1637 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1638 RAISE api_exception;
1639 END IF;
1640
1641 pa_fin_plan_utils.CHECK_IF_PLAN_TYPE_EDITABLE
1642 (p_project_id => p_project_id,
1643 p_fin_plan_type_id => p_rev_fin_plan_type_id,
1644 p_version_type => p_rev_version_type,
1645 x_editable_flag => r_editable_flag,
1646 x_return_status => x_return_status,
1647 x_msg_count => x_msg_count,
1648 x_msg_data => x_msg_data);
1649
1650 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1651 pa_debug.g_err_stage:='Rasing API_EXCEPTION 4';
1652 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1653 RAISE api_exception;
1654 END IF;
1655
1656 IF r_locked_by_person_id is null then
1657 r_locked_by_user_flag := 'Y'; -- unlocked is equivalent to locked by user
1658 END IF;
1659
1660 IF p_adjustment_type in ('BILL_RATE','REVENUE') AND
1661 (r_locked_by_user_flag <> 'Y' OR r_editable_flag <> 'Y') THEN
1662
1663 PA_UTILS.ADD_MESSAGE
1664 (p_app_short_name => 'PA',
1665 p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
1666
1667 pa_debug.reset_err_stack;
1668 x_return_status := FND_API.G_RET_STS_ERROR;
1669 RETURN;
1670 ELSIF p_adjustment_type IN ('QUANTITY', 'REVENUE_QUANTITY') AND
1671 (r_locked_by_user_flag <> 'Y' OR r_editable_flag <> 'Y' OR
1672 l_locked_by_user_flag <> 'Y' OR l_editable_flag <> 'Y') THEN
1673
1674 PA_UTILS.ADD_MESSAGE
1675 (p_app_short_name => 'PA',
1676 p_msg_name => 'PA_UPDATE_PUB_VER_ERR');
1677
1678 pa_debug.reset_err_stack;
1679 x_return_status := FND_API.G_RET_STS_ERROR;
1680 RETURN;
1681 END IF;
1682 END IF; -- IF p_adjustment_type IN ( 'BILL_RATE', 'QUANTITY') THEN
1683 END IF; --IF P_rev_budget_version_id IS NULL AND
1684 --For quantity proceeding if even one of the two ie.., cost or revenue budget version id is not locked.
1685 END IF;
1686
1687 --Need to call updates for revenue version / cost version when appropriate as above.
1688 --Based on the respective rates and versions when both are planned together.
1689
1690 --On check edit failure of financial structure for even one of the versions
1691 --or check edit failure of workplan structure, need to output error message similar
1692 --to that in task assignments.
1693
1694 IF p_resource_assignment_id_tbl.count = 0 THEN
1695 IF (p_wbs_element_id IS NOT NULL AND p_wbs_structure_version_id IS NOT NULL) OR
1696 (p_rbs_element_id IS NOT NULL AND p_rbs_version_id IS NOT NULL) THEN
1697
1698 COMPUTE_HIERARCHY
1699 (P_cost_budget_version_id => P_cost_budget_version_id,
1700 P_rev_budget_version_id => P_rev_budget_version_id,
1701 P_WBS_Element_Id => P_WBS_Element_Id,
1702 P_RBS_Element_Id => P_RBS_Element_Id,
1703 p_WBS_Structure_Version_Id => p_WBS_Structure_Version_Id,
1704 p_RBS_Version_Id => p_RBS_Version_Id,
1705 p_WBS_Rollup_Flag => p_WBS_Rollup_Flag,
1706 p_RBS_Rollup_Flag => p_RBS_Rollup_Flag,
1707 X_res_assignment_id_tbl => L_res_assignment_id_tbl,
1708 X_txn_currency_code_tbl => L_txn_curr_code_tbl,
1709 X_rev_res_assignment_id_tbl => L_rev_res_assignment_id_tbl,
1710 X_rev_txn_currency_code_tbl => L_rev_txn_curr_code_tbl,
1711 X_return_status => x_return_status);
1712
1713 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1714 pa_debug.g_err_stage:='Rasing API_EXCEPTION 5';
1715 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1716 RAISE api_exception;
1717 END IF;
1718 ELSE
1719 pa_debug.g_err_stage:='Rasing API_EXCEPTION 6';
1720 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1721 RAISE api_exception;
1722 END IF;
1723 ELSE
1724 IF p_txn_currency_code_tbl.count = 0 THEN
1725 FOR j in 1..p_resource_assignment_id_tbl.COUNT LOOP
1726 IF j > 1 THEN
1727 l_predicate1 := l_predicate1 || ',' || p_resource_assignment_id_tbl(j);
1728 ELSE
1729 l_predicate1 := p_resource_assignment_id_tbl(j);
1730 END IF;
1731 END LOOP;
1732
1733 l_sql := ' select distinct resource_assignment_id, txn_currency_code ' ||
1734 ' from pa_budget_lines ' ||
1735 ' where resource_ASSIGNMENT_ID in (' || l_predicate1 || ')';
1736
1737 OPEN l_cur FOR l_sql;
1738 l_cnt := 0;
1739 LOOP
1740 FETCH l_cur INTO l_t_id, l_t_code;
1741 EXIT WHEN l_cur%NOTFOUND;
1742 l_res_assignment_id_tbl.extend(1);
1743 l_txn_curr_code_tbl.extend(1);
1744 l_cnt := l_cnt + 1;
1745 l_res_assignment_id_tbl(l_cnt) := l_t_id;
1746 l_txn_curr_code_tbl(l_cnt) := l_t_code;
1747 END LOOP;
1748 CLOSE l_cur;
1749 ELSE
1750 l_res_assignment_id_tbl := p_resource_assignment_id_tbl;
1751 l_txn_curr_code_tbl := p_txn_currency_code_tbl;
1752 l_rev_res_assignment_id_tbl := p_resource_assignment_id_tbl;
1753 l_rev_txn_curr_code_tbl := p_txn_currency_code_tbl;
1754 END IF;
1755 END IF;
1756
1757 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1758 pa_debug.g_err_stage:='Rasing API_EXCEPTION 7';
1759 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1760 RAISE api_exception;
1761 END IF;
1762
1763 IF P_new_version_flag = 'Y' THEN
1764 IF nvl(p_cost_budget_version_id, -1) <> -1 AND
1765 ((p_adjustment_type IN ('COST_RATE', 'BURDENED_RATE', 'QUANTITY', 'COST_QUANTITY','RAW_COST','BURDENED_COST')) OR
1766 (p_cost_plan_setup = 'COST_AND_REV_SAME' AND p_adjustment_type in ('BILL_RATE','REVENUE'))) THEN
1767
1768 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
1769 PA_DEBUG.write(x_module => L_Module,
1770 x_msg => 'Calling Copy_Version for Cost',
1771 x_log_level => 3);
1772 PA_DEBUG.write(x_module => L_Module,
1773 x_msg => 'p_project_id: ' || p_project_id,
1774 x_log_level => 3);
1775 PA_DEBUG.write(x_module => L_Module,
1776 x_msg => 'p_source_version_id: ' || p_cost_budget_version_id,
1777 x_log_level => 3);
1778 PA_DEBUG.write(x_module => L_Module,
1779 x_msg => 'p_copy_mode: ' || PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
1780 x_log_level => 3);
1781 PA_DEBUG.write(x_module => L_Module,
1782 x_msg => 'p_calling_mode: ' || PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FIN_PLAN,
1783 x_log_level => 3);
1784 PA_DEBUG.write(x_module => L_Module,
1785 x_msg => 'px_target_version_id: ' || l_target_budget_version_id,
1786 x_log_level => 3);
1787 END IF;
1788
1789 PA_FIN_PLAN_PUB.Copy_Version
1790 (p_project_id => p_project_id,
1791 p_source_version_id => P_cost_budget_version_id ,
1792 p_copy_mode => PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
1793 p_calling_module => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FIN_PLAN,
1794 p_pji_rollup_required => 'N',
1795 px_target_version_id => l_target_budget_version_id,
1796 x_return_status => x_return_status,
1797 x_msg_count => x_msg_count,
1798 x_msg_data => x_msg_data);
1799
1800 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1801 pa_debug.g_err_stage:='Rasing API_EXCEPTION 8';
1802 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1803 RAISE api_exception;
1804 ELSE
1805 pa_debug.g_err_stage:='Copy_Version Returned Success';
1806 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1807
1808 IF L_res_assignment_id_tbl.count <> 0 THEN
1809 FOR j in 1..l_res_assignment_id_tbl.COUNT LOOP
1810 IF j > 1 THEN
1811 l_predicate3 := l_predicate3 || ',' || l_res_assignment_id_tbl(j);
1812 ELSE
1813 l_predicate3 := l_res_assignment_id_tbl(j);
1814 END IF;
1815 END LOOP;
1816
1817 --Reinitialize L_res_assignment_id_tbl if necessary...
1818
1819 -- SQL Repository Bug 4884427; SQL ID 14901305
1820 -- Replaced l_target_budget_version_id literal with a bind variable.
1821
1822 l_sql3 := ' select distinct pra.resource_assignment_id, bl.txn_currency_code ' ||
1823 ' from pa_resource_assignments pra, pa_budget_lines bl ' ||
1824 ' where pra.budget_version_id = :1 ' || /* to_char(l_target_budget_version_id) || */
1825 ' and pra.resource_assignment_id = bl.resource_assignment_id ' ||
1826 ' and pra.parent_assignment_id in (' || l_predicate3 || ')';
1827
1828 pa_debug.g_err_stage:='l_sql3: ' || l_sql3;
1829 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1830
1831 -- SQL Repository Bug 4884427; SQL ID 14901305
1832 -- Supply the bind variable value with the USING clause.
1833
1834 OPEN l_cur3 FOR l_sql3 USING l_target_budget_version_id;
1835 l_cnt := 0;
1836 LOOP
1837 FETCH l_cur3 into l_t_id, l_t_code;
1838 EXIT WHEN l_cur3%NOTFOUND;
1839 l_cnt := l_cnt + 1;
1840 IF NOT l_res_assignment_id_tbl.exists(l_cnt) THEN
1841 l_res_assignment_id_tbl.extend(1);
1842 END IF;
1843 IF NOT l_txn_curr_code_tbl.exists(l_cnt) THEN
1844 l_txn_curr_code_tbl.extend(1);
1845 END IF;
1846 l_res_assignment_id_tbl(l_cnt) := l_t_id;
1847 l_txn_curr_code_tbl(l_cnt) := l_t_code;
1848 END LOOP;
1849 CLOSE l_cur3;
1850 END IF;
1851 END IF;
1852
1853 IF (P_new_version_name IS NOT NULL)
1854 OR (P_new_version_desc IS NOT NULL) THEN
1855
1856 UPDATE PA_BUDGET_VERSIONS SET version_name = p_new_version_name,
1857 description = p_new_version_desc
1858 WHERE budget_version_id = l_target_budget_version_id;
1859 END IF;
1860 x_cost_budget_version_id := l_target_budget_version_id;
1861 END IF;
1862
1863 IF nvl(p_rev_budget_version_id, -1) <> -1 AND
1864 p_adjustment_type IN ('BILL_RATE', 'QUANTITY', 'REVENUE_QUANTITY','REVENUE') THEN
1865
1866 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
1867 PA_DEBUG.write(x_module => L_Module,
1868 x_msg => 'Calling Copy_Version for Revenue',
1869 x_log_level => 3);
1870 PA_DEBUG.write(x_module => L_Module,
1871 x_msg => 'p_project_id: ' || p_project_id,
1872 x_log_level => 3);
1873 PA_DEBUG.write(x_module => L_Module,
1874 x_msg => 'p_source_version_id: ' || p_rev_budget_version_id,
1875 x_log_level => 3);
1876 PA_DEBUG.write(x_module => L_Module,
1877 x_msg => 'p_copy_mode: ' || PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
1878 x_log_level => 3);
1879 PA_DEBUG.write(x_module => L_Module,
1880 x_msg => 'p_calling_mode: ' || PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FIN_PLAN,
1881 x_log_level => 3);
1882 PA_DEBUG.write(x_module => L_Module,
1883 x_msg => 'px_target_version_id: ' || l_target_rev_version_id,
1884 x_log_level => 3);
1885 END IF;
1886
1887 PA_FIN_PLAN_PUB.Copy_Version
1888 (p_project_id => p_project_id,
1889 p_source_version_id => P_rev_budget_version_id ,
1890 p_copy_mode => PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
1891 p_calling_module => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FIN_PLAN,
1892 p_pji_rollup_required => 'N',
1893 px_target_version_id => l_target_rev_version_id,
1894 x_return_status => x_return_status,
1895 x_msg_count => x_msg_count,
1896 x_msg_data => x_msg_data);
1897
1898 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1899 pa_debug.g_err_stage:='Rasing API_EXCEPTION 9';
1900 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1901 RAISE api_exception;
1902 ELSE
1903 pa_debug.g_err_stage:='copy Version Returned Success';
1904 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1905 IF L_rev_res_assignment_id_tbl.count <> 0 THEN
1906 FOR j in 1..L_rev_res_assignment_id_tbl.COUNT LOOP
1907 IF j > 1 THEN
1908 l_predicate2 := l_predicate2 || ',' || L_rev_res_assignment_id_tbl(j);
1909 ELSE
1910 l_predicate2 := L_rev_res_assignment_id_tbl(j);
1911 END IF;
1912 END LOOP;
1913
1914 -- SQL Repository Bug 4884427; SQL ID 14901323
1915 -- Replaced l_target_rev_version_id literal with a bind variable.
1916
1917 l_sql2 := ' select distinct pra.resource_assignment_id, bl.txn_currency_code ' ||
1918 ' from pa_resource_assignments pra, pa_budget_lines bl ' ||
1919 ' where pra.budget_version_id = :1 ' || /* to_char(l_target_rev_version_id) || */
1920 ' and pra.resource_assignment_id = bl.resource_assignment_id ' ||
1921 ' and pra.parent_assignment_id in (' || l_predicate2 || ')';
1922
1923
1924 pa_debug.g_err_stage:='l_sql2: ' || l_sql2;
1925 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1926
1927 -- SQL Repository Bug 4884427; SQL ID 14901323
1928 -- Supply the bind variable value with the USING clause.
1929
1930 OPEN l_cur2 FOR l_sql2 USING l_target_rev_version_id;
1931 l_cnt := 0;
1932 LOOP
1933 FETCH l_cur2 INTO l_t_id, l_t_code;
1934 EXIT WHEN l_cur2%NOTFOUND;
1935 l_cnt := l_cnt + 1;
1936 IF NOT l_rev_res_assignment_id_tbl.exists(l_cnt) THEN
1937 l_rev_res_assignment_id_tbl.extend(1);
1938 END IF;
1939 IF NOT l_rev_txn_curr_code_tbl.exists(l_cnt) THEN
1940 l_rev_txn_curr_code_tbl.extend(1);
1941 END IF;
1942 l_rev_res_assignment_id_tbl(l_cnt) := l_t_id;
1943 l_rev_txn_curr_code_tbl(l_cnt) := l_t_code;
1944 END LOOP;
1945 CLOSE l_cur2;
1946 END IF;
1947 END IF;
1948
1949 IF (P_new_version_name IS NOT NULL)
1950 OR (P_new_version_desc IS NOT NULL) THEN
1951
1952 UPDATE PA_BUDGET_VERSIONS SET version_name = p_new_version_name,
1953 description = p_new_version_desc
1954 WHERE budget_version_id = l_target_rev_version_id;
1955 END IF;
1956 x_rev_budget_version_id := l_target_rev_version_id;
1957 END IF;
1958 END IF;
1959
1960 -- Start of Debug Statements
1961 pa_debug.g_err_stage:='l_cost_budget_version_id ' || l_cost_budget_version_id;
1962 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1963 pa_debug.g_err_stage:='l_rev_budget_version_id ' || l_rev_budget_version_id;
1964 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1965 pa_debug.g_err_stage:='l_quantity_adj_pct ' || l_quantity_adj_pct;
1966 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1967 pa_debug.g_err_stage:='l_burdened_rate_adj_pct ' || l_burdened_rate_adj_pct;
1968 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1969 pa_debug.g_err_stage:='l_bill_rate_adj_pct ' || l_bill_rate_adj_pct;
1970 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1971
1972 IF l_res_assignment_id_tbl.COUNT > 0 THEN
1973 FOR i IN l_res_assignment_id_tbl.FIRST .. l_res_assignment_id_tbl.LAST LOOP
1974 pa_debug.g_err_stage:= L_FuncProc || ' l_res_assignment_id_tbl(' || i || '): ' || l_res_assignment_id_tbl(i);
1975 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1976 END LOOP;
1977 ELSE
1978 pa_debug.g_err_stage:= L_FuncProc || ' l_res_assignment_id_tbl is EMPTY';
1979 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1980 END IF;
1981
1982 IF l_txn_curr_code_tbl.COUNT > 0 THEN
1983 FOR i IN l_txn_curr_code_tbl.FIRST .. l_txn_curr_code_tbl.LAST LOOP
1984 pa_debug.g_err_stage:= L_FuncProc || ' l_txn_curr_code_tbl(' || i || '): ' || l_txn_curr_code_tbl(i);
1985 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1986 END LOOP;
1987 ELSE
1988 pa_debug.g_err_stage:= L_FuncProc || ' l_txn_curr_code_tbl is EMPTY';
1989 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1990 END IF;
1991
1992 IF l_rev_res_assignment_id_tbl.COUNT > 0 THEN
1993 FOR i IN l_rev_res_assignment_id_tbl.FIRST .. l_rev_res_assignment_id_tbl.LAST LOOP
1994 pa_debug.g_err_stage:= L_FuncProc || ' l_rev_res_assignment_id_tbl(' || i || '): ' || l_rev_res_assignment_id_tbl(i);
1995 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
1996 END LOOP;
1997 ELSE
1998 pa_debug.g_err_stage:= L_FuncProc || ' l_rev_res_assignment_id_tbl is EMPTY';
1999 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2000 END IF;
2001
2002 IF l_rev_txn_curr_code_tbl.COUNT > 0 THEN
2003 FOR i IN l_rev_txn_curr_code_tbl.FIRST .. l_rev_txn_curr_code_tbl.LAST LOOP
2004 pa_debug.g_err_stage:= L_FuncProc || ' l_rev_txn_curr_code_tbl(' || i || '): ' || l_rev_txn_curr_code_tbl(i);
2005 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2006 END LOOP;
2007 ELSE
2008 pa_debug.g_err_stage:= L_FuncProc || ' l_rev_txn_curr_code_tbl is EMPTY';
2009 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2010 END IF;
2011 -- End of Debugging Statements
2012
2013 IF l_res_assignment_id_tbl.COUNT = 0 AND l_rev_res_assignment_id_tbl.COUNT = 0 THEN
2014 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2015 ,p_msg_name => 'PA_PL_ADJUST_NO_DATA');
2016 x_msg_data := 'PA_PL_ADJUST_NO_DATA';
2017 x_return_status := FND_API.G_RET_STS_ERROR;
2018 RAISE FND_API.G_EXC_ERROR;
2019 ELSE
2020 IF nvl(p_cost_budget_version_id, -1) <> -1 AND
2021 ((p_adjustment_type IN ('COST_RATE', 'BURDENED_RATE', 'QUANTITY', 'COST_QUANTITY','RAW_COST','BURDENED_COST')) OR
2022 (p_cost_plan_setup = 'COST_AND_REV_SAME' AND p_adjustment_type in ('BILL_RATE','REVENUE'))) AND
2023 l_res_assignment_id_tbl.COUNT > 0 THEN
2024
2025 IF p_new_version_flag = 'Y' THEN
2026 pa_fp_calc_plan_pkg.calculate (
2027 p_project_id => p_project_id
2028 ,p_budget_version_id => l_target_budget_version_id
2029 ,p_rollup_required_flag => 'N'
2030 ,p_mass_adjust_flag => 'Y'
2031 ,p_quantity_adj_pct => l_quantity_adj_pct
2032 ,p_cost_rate_adj_pct => l_cost_rate_adj_pct
2033 ,p_burdened_rate_adj_pct => l_burdened_rate_adj_pct
2034 /* IPM changes */
2035 ,p_raw_cost_adj_pct => l_raw_cost_adj_pct
2036 ,p_burden_cost_adj_pct => l_burden_cost_adj_pct
2037 ,p_revenue_adj_pct => l_revenue_adj_pct
2038 ,p_bill_rate_adj_pct => l_bill_rate_adj_pct
2039 ,p_source_context => 'RESOURCE_ASSIGNMENT'
2040 ,p_resource_assignment_tab => l_res_assignment_id_tbl
2041 ,p_txn_currency_code_tab => l_txn_curr_code_tbl
2042 ,x_return_status => x_return_status
2043 ,x_msg_count => x_msg_count
2044 ,x_msg_data => x_msg_data);
2045 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2046 pa_debug.g_err_stage:='Rasing API_EXCEPTION 10';
2047 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2048 RAISE api_exception;
2049 END IF;
2050
2051 l_target_budget_version_id_tbl.extend(1);
2052 l_target_budget_version_id_tbl(1) := l_target_budget_version_id;
2053
2054 PJI_FM_XBS_ACCUM_MAINT.Plan_Create (
2055 p_fp_version_ids => l_target_budget_version_id_tbl,
2056 x_return_status => x_return_status,
2057 x_msg_code => x_msg_data);
2058
2059 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2060 pa_debug.g_err_stage:='Rasing API_EXCEPTION 11';
2061 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2062 RAISE api_exception;
2063 END IF;
2064 ELSE
2065 pa_fp_calc_plan_pkg.calculate (
2066 p_project_id => p_project_id
2067 ,p_budget_version_id => p_cost_budget_version_id
2068 ,p_rollup_required_flag => 'Y'
2069 ,p_mass_adjust_flag => 'Y'
2070 ,p_quantity_adj_pct => l_quantity_adj_pct
2071 ,p_cost_rate_adj_pct => l_cost_rate_adj_pct
2072 ,p_burdened_rate_adj_pct => l_burdened_rate_adj_pct
2073 ,p_bill_rate_adj_pct => l_bill_rate_adj_pct
2074 /* IPM changes */
2075 ,p_raw_cost_adj_pct => l_raw_cost_adj_pct
2076 ,p_burden_cost_adj_pct => l_burden_cost_adj_pct
2077 ,p_revenue_adj_pct => l_revenue_adj_pct
2078 ,p_source_context => 'RESOURCE_ASSIGNMENT'
2079 ,p_resource_assignment_tab => l_res_assignment_id_tbl
2080 ,p_txn_currency_code_tab => l_txn_curr_code_tbl
2081 ,x_return_status => x_return_status
2082 ,x_msg_count => x_msg_count
2083 ,x_msg_data => x_msg_data);
2084 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2085 pa_debug.g_err_stage:='Rasing API_EXCEPTION 12';
2086 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2087 RAISE api_exception;
2088 END IF;
2089 END IF;
2090 END IF;
2091
2092 IF nvl(p_rev_budget_version_id, -1) <> -1 AND
2093 nvl(p_rev_budget_version_id, -1) <> nvl(p_cost_budget_version_id, -1) AND
2094 p_adjustment_type IN ('BILL_RATE', 'QUANTITY', 'REVENUE_QUANTITY','REVENUE') AND
2095 l_rev_res_assignment_id_tbl.COUNT > 0 THEN
2096
2097 IF p_new_version_flag = 'Y' THEN
2098 pa_fp_calc_plan_pkg.calculate (
2099 p_project_id => p_project_id
2100 ,p_budget_version_id => l_target_rev_version_id
2101 ,p_rollup_required_flag => 'N'
2102 ,p_mass_adjust_flag => 'Y'
2103 ,p_quantity_adj_pct => l_quantity_adj_pct
2104 ,p_cost_rate_adj_pct => l_cost_rate_adj_pct
2105 ,p_burdened_rate_adj_pct => l_burdened_rate_adj_pct
2106 ,p_bill_rate_adj_pct => l_bill_rate_adj_pct
2107 /* IPM changes */
2108 ,p_raw_cost_adj_pct => l_raw_cost_adj_pct
2109 ,p_burden_cost_adj_pct => l_burden_cost_adj_pct
2110 ,p_revenue_adj_pct => l_revenue_adj_pct
2111 ,p_source_context => 'RESOURCE_ASSIGNMENT'
2112 ,p_resource_assignment_tab => l_rev_res_assignment_id_tbl
2113 ,p_txn_currency_code_tab => l_rev_txn_curr_code_tbl
2114 ,x_return_status => x_return_status
2115 ,x_msg_count => x_msg_count
2116 ,x_msg_data => x_msg_data);
2117 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2118 pa_debug.g_err_stage:='Rasing API_EXCEPTION 13';
2119 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2120 RAISE api_exception;
2121 END IF;
2122
2123 l_target_rev_version_id_tbl.extend(1);
2124 l_target_rev_version_id_tbl(1) := l_target_rev_version_id;
2125
2126 PJI_FM_XBS_ACCUM_MAINT.Plan_Create (
2127 p_fp_version_ids => l_target_rev_version_id_tbl,
2128 x_return_status => x_return_status,
2129 x_msg_code => x_msg_data);
2130
2131 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2132 pa_debug.g_err_stage:='Rasing API_EXCEPTION 14';
2133 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2134 RAISE api_exception;
2135 END IF;
2136 ELSE
2137 pa_fp_calc_plan_pkg.calculate (
2138 p_project_id => p_project_id
2139 ,p_budget_version_id => p_rev_budget_version_id
2140 ,p_rollup_required_flag => 'Y'
2141 ,p_mass_adjust_flag => 'Y'
2142 ,p_quantity_adj_pct => l_quantity_adj_pct
2143 ,p_cost_rate_adj_pct => l_cost_rate_adj_pct
2144 ,p_burdened_rate_adj_pct => l_burdened_rate_adj_pct
2145 ,p_bill_rate_adj_pct => l_bill_rate_adj_pct
2146 /* IPM changes */
2147 ,p_raw_cost_adj_pct => l_raw_cost_adj_pct
2148 ,p_burden_cost_adj_pct => l_burden_cost_adj_pct
2149 ,p_revenue_adj_pct => l_revenue_adj_pct
2150 ,p_source_context => 'RESOURCE_ASSIGNMENT'
2151 ,p_resource_assignment_tab => l_rev_res_assignment_id_tbl
2152 ,p_txn_currency_code_tab => l_rev_txn_curr_code_tbl
2153 ,x_return_status => x_return_status
2154 ,x_msg_count => x_msg_count
2155 ,x_msg_data => x_msg_data);
2156 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2157 pa_debug.g_err_stage:='Rasing API_EXCEPTION 15';
2158 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2159 RAISE api_exception;
2160 END IF;
2161 END IF;
2162 END IF;
2163
2164 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2165 COMMIT;
2166 END IF;
2167 END IF;
2168
2169 pa_debug.g_err_stage:='End of ' || L_FuncProc;
2170 pa_debug.write(L_Module,pa_debug.g_err_stage,3);
2171 EXCEPTION
2172 WHEN FND_API.G_EXC_ERROR THEN
2173 x_return_status := FND_API.G_RET_STS_ERROR;
2174 x_msg_count := Fnd_Msg_Pub.count_msg;
2175 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
2176 PA_DEBUG.write_log (x_module => L_Module
2177 ,x_msg => 'Error: ' || L_FuncProc || ' ' || SQLERRM
2178 ,x_log_level => 3);
2179 END IF;
2180 WHEN API_EXCEPTION THEN
2181 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 3) THEN
2182 PA_DEBUG.write_log (x_module => L_Module
2183 ,x_msg => 'Error:' || L_FuncProc || SQLERRM
2184 ,x_log_level => 3);
2185 END IF;
2186 FND_MSG_PUB.add_exc_msg(p_pkg_name => L_Module,
2187 p_procedure_name => L_FuncProc);
2188 RAISE;
2189 WHEN OTHERS THEN
2190 IF P_DEBUG_MODE = 'Y' AND (li_curr_level <= 6) THEN
2191 PA_DEBUG.write_log (x_module => L_Module
2192 ,x_msg => 'Unexp. Error:' || L_FuncProc || SQLERRM
2193 ,x_log_level => 6);
2194 END IF;
2195 FND_MSG_PUB.add_exc_msg(p_pkg_name => L_Module,
2196 p_procedure_name => L_FuncProc);
2197 RAISE;
2198 END ADJUST_PLANNING_TRANSACTIONS;
2199
2200
2201
2202 FUNCTION CLASS_HOURS(p_current_budget_version_id IN NUMBER, p_input_budget_version_id IN NUMBER,
2203 p_rev_budget_version_id IN NUMBER, p_report_using IN VARCHAR2,
2204 p_mode IN VARCHAR2, p_resource_class_code IN VARCHAR2,
2205 p_total_plan_quantity IN NUMBER, p_rate_based_flag IN VARCHAR2 ) RETURN NUMBER IS
2206 l_num NUMBER;
2207 BEGIN
2208
2209 IF p_report_using = 'COST' and p_current_budget_version_id <> p_input_budget_version_id THEN
2210 l_num := 0;
2211 RETURN l_num;
2212 ELSIF p_report_using = 'REVENUE' and p_rev_budget_version_id IS NOT NULL AND
2213 p_current_budget_version_id <> p_rev_budget_version_id THEN
2214 l_num := 0;
2215 RETURN l_num;
2216 END IF;
2217
2218 IF (p_input_budget_version_id IS NULL OR p_current_budget_version_id <> p_input_budget_version_id) AND
2219 (p_rev_budget_version_id IS NULL OR p_current_budget_version_id <> p_rev_budget_version_id) THEN
2220 l_num := 0;
2221 RETURN l_num;
2222 END IF;
2223
2224 IF p_resource_class_code = 'PEOPLE' and p_mode = 'PEOPLE' and p_rate_based_flag = 'Y' then
2225 l_num := nvl(p_total_plan_quantity, 0);
2226 ELSIF p_resource_class_code = 'EQUIPMENT' and p_mode = 'EQUIPMENT' and p_rate_based_flag = 'Y' then
2227 l_num := nvl(p_total_plan_quantity, 0);
2228 ELSE
2229 l_num := 0;
2230 END IF;
2231
2232 RETURN l_num;
2233 EXCEPTION WHEN OTHERS THEN
2234 RETURN 0;
2235 END;
2236
2237
2238 FUNCTION REVENUE(p_current_budget_version_id IN NUMBER, p_input_budget_version_id IN NUMBER,
2239 p_rev_budget_version_id IN NUMBER, p_REVENUE IN NUMBER) RETURN NUMBER IS
2240 l_num NUMBER;
2241
2242 BEGIN
2243
2244 IF nvl(p_rev_budget_version_id, -1) = -1 and (p_input_budget_version_id = p_current_budget_version_id) THEN
2245
2246 l_num := nvl(p_REVENUE , 0);
2247
2248 ELSIF nvl(p_rev_budget_version_id, -1) <> -1 and
2249 (p_rev_budget_version_id = p_current_budget_version_id) THEN
2250
2251 l_num := nvl(p_REVENUE , 0);
2252
2253 ELSE
2254 l_num := 0;
2255 END IF;
2256
2257 RETURN l_num;
2258 EXCEPTION WHEN OTHERS THEN
2259 RETURN 0;
2260 END;
2261
2262 --Messages --Use PA_NO_ACCESS_TO_UPDATE and
2263 -- PA_ALL_NO_UPDATE_RECORD
2264 END PA_FP_ADJUSTMENT_UTILS ;