[Home] [Help]
PACKAGE BODY: APPS.PA_FP_CALC_PLAN_PUB
Source
1 PACKAGE BODY PA_FP_CALC_PLAN_PUB AS
2 --/* $Header: PAFPCPUB.pls 120.1.12000000.2 2007/09/11 08:51:13 anuragar ship $ */
3
4 g_module_name VARCHAR2(100) := 'PA_FP_CALC_PLAN_PUB';
5 Invalid_Arg_Exc_WP Exception;
6 G_PKG_NAME VARCHAR2(100) := 'PA_FP_CALC_PLAN_PUB';
7
8 -- Procedure : REFRESH_RATES
9 -- Type : Public Procedure
10 -- Purpose : This is an AMG API to refresh cost/conversion rates in bulk.
11 -- Note : This public AMG API refreshes the cost rates or conversion rates (depending
12 -- : on the IN parameters) of either all the planning resources in the entire
13 -- : budget version or specific planning resources in the budget version.
14 -- : It supports both Workplan and Financial structures, and it performs all
15 -- : validation and security checks before refreshing rates.
16 -- Assumptions :
17
18 -- Parameters Type Required Description and Purpose
19 -- --------------------------- ------ -------- --------------------------------------------------------
20 -- p_project_id NUMBER NO Project Id
21 -- p_pm_project_reference VARCHAR2 NO Project Reference, either project_id or project reference
22 -- p_update_plan_type VARCHAR2 Yes Accepted values are 'WORKPLAN' or 'FINPLAN'.
23 -- p_structure_version_id NUMBER NO Identifier of the workplan structure version. Required if
24 -- update plan type = WORKPLAN.
25 -- p_budget_version_number VARCHAR2 NO Identifier of the budget version. Required if update plan type = FINPLAN.
26 -- p_version_type VARCHAR2 NO Identifier of the budget version. Required if update plan type = FINPLAN.
27 -- p_finplan_type_id NUMBER NO Identifier of the budget version. Either p_finplan_type_id
28 -- or p_finplan_type_name must be provided if update plan type = FINPLAN.
29 -- p_finplan_type_name VARCHAR2 NO Identifier of the budget version. Either p_finplan_type_id
30 -- or p_finplan_type_name must be provided if update plan type = FINPLAN.
31 -- p_resource_class_code_tab Table NO Optional parameter; if passed in, only the planning resource
32 -- assignments in the specified resource classes will be refreshed.
33 -- p_resource_asgn_id_tab Table NO Optional parameter; if passed in, only the specified planning
34 -- resource assignments will be refreshed.
35 -- p_txn_curr_code_tab Table NO Optional parameter. If passed in with resource assignment ids,
36 -- only the specified planning resource assignments and transaction currency
37 -- combination will be refreshed.
38 -- p_refresh_cost_bill_rates_flag VARCHAR2 NO Flag indicates whether Refresh Cost Rates action should be performed.
39 -- Accepted values are 'Y' and 'N'.
40 -- p_refresh_conv_rates_flag VARCHAR2 NO Flag indicates whether Refresh Conversion Rates action should be performed.
41 -- Accepted values are 'Y' and 'N'.
42
43 PROCEDURE REFRESH_RATES
44 (
45 p_api_version_number IN NUMBER := 1.0
46 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
47 , p_commit IN VARCHAR2 := FND_API.G_FALSE
48 , p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
49 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
50 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
51 , p_update_plan_type IN VARCHAR2
52 , p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
53 , p_budget_version_number IN PA_BUDGET_VERSIONS.version_number%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
57 , p_resource_class_code_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
54 , p_version_type IN PA_BUDGET_VERSIONS.version_type%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
55 , p_finplan_type_id IN PA_BUDGET_VERSIONS.fin_plan_type_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
56 , p_finplan_type_name IN PA_FIN_PLAN_TYPES_VL.name%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
58 , p_resource_asgn_id_tab IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
59 , p_txn_curr_code_tab IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE()
60 , p_refresh_cost_bill_rates_flag IN VARCHAR2 := 'N'
61 , p_refresh_conv_rates_flag IN VARCHAR2 := 'N'
62 , p_budget_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
63 , x_return_status OUT VARCHAR2
64 , x_msg_count OUT NUMBER
65 , x_msg_data OUT VARCHAR2
66 )
67 IS
68
69 l_msg_count NUMBER := 0;
70 l_data VARCHAR2(2000);
71 l_msg_data VARCHAR2(2000);
72 l_msg_index_out NUMBER;
73 l_debug_mode VARCHAR2(1);
74 l_user_id NUMBER;
75 l_login_id NUMBER;
76 l_return_status VARCHAR2(1);
77
78 l_project_id NUMBER;
79 l_budget_version_id NUMBER;
80 l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
81 l_finplan_type_id NUMBER;
82 l_finplan_type_name PA_FIN_PLAN_TYPES_VL.name%TYPE;
83 l_budget_version_number NUMBER;
84
85
86 l_budget_entry_method_code VARCHAR2(30);
87 l_resource_list_id NUMBER;
88 l_fin_plan_level_code VARCHAR2(30);
89 l_time_phased_code VARCHAR2(30);
90 l_plan_in_multi_curr_flag VARCHAR2(1);
91 l_budget_amount_code VARCHAR2(30);
92 l_categorization_code VARCHAR2(30);
93 l_project_number VARCHAR2(25);
94 l_budget_type_code VARCHAR2(30) := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
95 l_change_reason_code VARCHAR2(30) := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
96
97 l_txn_curr_passed_flag VARCHAR2(1) := 'N';
98 l_resource_asgn_id NUMBER := null;
99 l_valid VARCHAR2(1) := 'X';
100 l_trxn_curr_code VARCHAR2(30):= null;
101 l_call_with_res_txn_tbl_flag VARCHAR2(1) := 'N';
102
103 l_raid_tmp_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
104 l_resource_asgn_id_tab SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
105 l_txn_curr_code_tab SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
106 l_trxn_curr_code_tmp_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
107
108 l_debug_level2 CONSTANT NUMBER := 2;
109 l_debug_level3 CONSTANT NUMBER := 3;
110 l_debug_level4 CONSTANT NUMBER := 4;
111 l_debug_level5 CONSTANT NUMBER := 5;
112 --Added for bug 6378555 to pass G_PA_MISS_CHAR
113 l_pa_miss_char varchar2(1) := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
114
115 CURSOR c_validate_res_asgn_id
116 ( l_project_id NUMBER , l_budget_version_id NUMBER
117 , l_resource_assignment_id NUMBER
118 ) IS
119 SELECT '1'
120 FROM DUAL
121 WHERE EXISTS
122 (
123 SELECT resource_assignment_id
124 FROM PA_RESOURCE_ASSIGNMENTS
125 WHERE PROJECT_ID = l_project_id
126 AND BUDGET_VERSION_ID = l_budget_version_id
127 AND RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id
128 );
129
130 CURSOR c_get_all_raid_txn_curr(l_project_id NUMBER, l_budget_version_id NUMBER,
131 l_resource_class_code VARCHAR2) IS
132 select
133 pra.resource_assignment_id raid,
134 pbl.txn_currency_code
135 from
136 pa_resource_assignments pra,
137 pa_budget_lines pbl
138 where pra.project_id = l_project_id
139 and pra.budget_version_id = l_budget_version_id
140 AND pra.resource_class_code = l_resource_class_code
141 and pra.resource_assignment_id = pbl.resource_assignment_id
142 AND pra.budget_version_id = pbl.budget_version_id
143 GROUP BY pra.resource_assignment_id, pbl.txn_currency_code;
144
145 CURSOR c_get_txn_curr_code(l_resource_assignment_id NUMBER) IS
146 SELECT distinct txn_currency_code
147 FROM pa_budget_lines
148 WHERE resource_assignment_id = l_resource_assignment_id;
149
150 CURSOR C_VALIDATE_FP_BUDGET_VERSION (l_budget_version_id NUMBER, l_project_id NUMBER )
151 IS
152 SELECT bv.fin_plan_type_id , bv.version_type, bv.version_number
153 FROM pa_budget_versions bv
154 WHERE bv.budget_version_id = l_budget_version_id
155 AND bv.project_id = l_project_id
156 AND bv.ci_id IS NULL;
157
158 CURSOR c_validate_curr_code(l_currency_code VARCHAR2 , l_resource_assignment_id NUMBER, l_budget_version_id NUMBER) IS
159 SELECT 'Y'
160 FROM DUAL
161 WHERE EXISTS
162 (
163 SELECT TXN_CURRENCY_CODE
164 FROM pa_budget_lines
165 WHERE txn_currency_code = l_currency_code
166 AND resource_assignment_id = l_resource_assignment_id
167 AND budget_version_id = l_budget_version_id
168 );
169 l_valid_currency VARCHAR2(1);
170
171 BEGIN
172
173 x_msg_count := 0;
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 l_user_id := fnd_global.user_id;
177 l_login_id := fnd_global.login_id;
181 PA_DEBUG.set_curr_function( p_function => 'REFRESH_RATES',
178 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',l_user_id, l_login_id,275,null,null),'N');
179
180 IF l_debug_mode = 'Y' THEN
182 p_debug_mode => l_debug_mode );
183 END IF;
184
185 IF NOT FND_API.Compatible_API_Call ( 1.0 ,p_api_version_number,'REFRESH_RATES',G_PKG_NAME) THEN
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188 --Print All Input Params
189 IF l_debug_mode = 'Y' THEN
190 Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB :REFRESH_RATES: Printing Input parameters';
191 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
192 l_debug_level3);
193 Pa_Debug.WRITE(g_module_name,'p_pm_product_code'||':'||p_pm_product_code,
194 l_debug_level3);
195 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
196 l_debug_level3);
197 Pa_Debug.WRITE(g_module_name,'p_pm_project_reference'||':'||p_pm_project_reference,
198 l_debug_level3);
199 Pa_Debug.WRITE(g_module_name,'p_update_plan_type'||':'||p_update_plan_type,
200 l_debug_level3);
201 Pa_Debug.WRITE(g_module_name,'p_structure_version_id'||':'||p_structure_version_id,
202 l_debug_level3);
203 Pa_Debug.WRITE(g_module_name,'p_budget_version_number'||':'||p_budget_version_number,
204 l_debug_level3);
205 Pa_Debug.WRITE(g_module_name,'p_version_type '||':'||p_version_type,
206 l_debug_level3);
207 Pa_Debug.WRITE(g_module_name,'p_finplan_type_id'||':'||p_finplan_type_id,
208 l_debug_level3);
209 Pa_Debug.WRITE(g_module_name,'p_finplan_type_name'||':'||p_finplan_type_name,
210 l_debug_level3);
211 Pa_Debug.WRITE(g_module_name,'p_refresh_cost_bill_rates_flag'||':'||p_refresh_cost_bill_rates_flag,
212 l_debug_level3);
213 Pa_Debug.WRITE(g_module_name,'p_refresh_conv_rates_flag'||':'||p_refresh_conv_rates_flag,
214 l_debug_level3);
215
216 IF (p_resource_class_code_tab is not null and nvl(p_resource_class_code_tab.last,0) > 0) THEN
217 Pa_Debug.WRITE(g_module_name,'p_resource_class_code_tab is passed',l_debug_level3);
218 END IF;
219
220 IF (p_resource_asgn_id_tab is not null and nvl(p_resource_asgn_id_tab.last,0) > 0) THEN
221 Pa_Debug.WRITE(g_module_name,'p_resource_asgn_id_tab is passed',l_debug_level3);
222 END IF;
223
224 IF (p_txn_curr_code_tab is not null and nvl(p_txn_curr_code_tab.last,0) > 0) THEN
225 Pa_Debug.WRITE(g_module_name,'p_txn_curr_code_tab is passed',l_debug_level3);
226 END IF;
227
228 END IF;
229
230
231 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
232 FND_MSG_PUB.initialize;
233 END IF;
234
235 IF (p_commit = FND_API.G_TRUE) THEN
236 savepoint REFRESH_RATES_PUBLIC;
237 END IF;
238 --Validate All Input Params
239 IF l_debug_mode = 'Y' THEN
240 Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB :REFRESH_RATES : Validating Input parameters';
241 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
242 l_debug_level3);
243 END IF;
244
245 IF ( ( p_project_id IS NULL OR p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) AND
246 ( p_pm_project_reference IS NULL
247 OR p_pm_project_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR )
248 )
249 THEN
250 IF l_debug_mode = 'Y' THEN
251 Pa_Debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : Both Project_id and Project_reference are null';
252 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
253 END IF;
254 RAISE Invalid_Arg_Exc_WP;
255 END IF;
256
257 IF ( p_update_plan_type NOT IN ('WORKPLAN', 'FINPLAN')) THEN
258 IF l_debug_mode = 'Y' THEN
259 Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : p_update_plan_type is not WORKPLAN or FINPLAN ';
260 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
261 END IF;
262 RAISE Invalid_Arg_Exc_WP;
263 END IF;
264
265 IF ( nvl ( p_refresh_cost_bill_rates_flag,'N' )='N' AND
266 nvl ( p_refresh_conv_rates_flag, 'N' ) = 'N' ) THEN
267 IF l_debug_mode = 'Y' THEN
268 Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : BOTH cost bill rate and conv rate flags are N';
269 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
270 END IF;
271 RAISE Invalid_Arg_Exc_WP;
272 END IF;
273
274 IF ( p_update_plan_type = 'WORKPLAN' AND
275 ( p_structure_version_id is null or p_structure_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) AND
276 ( p_budget_version_id is null or p_budget_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
277 ) THEN
278 IF l_debug_mode = 'Y' THEN
279 Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : p_update_plan_type is WORKPLAN but str version id is not passed';
280 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
281 END IF;
282 pa_interface_utils_pub.map_new_amg_msg
283 ( p_old_message_code => 'PA_PS_STRUC_BV_VER_REQ'
284 ,p_msg_attribute => 'CHANGE'
285 ,p_resize_flag => 'N'
289 ,p_attribute3 => ''
286 ,p_msg_context => 'GENERAL'
287 ,p_attribute1 => ''
288 ,p_attribute2 => ''
290 ,p_attribute4 => ''
291 ,p_attribute5 => '');
292 RAISE FND_API.G_EXC_ERROR;
293 END IF;
294
295 IF ( p_update_plan_type = 'FINPLAN' AND
296 (
297 ( p_budget_version_number is null or p_budget_version_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) AND
298 ( p_finplan_type_id is null or p_finplan_type_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
299 ( p_finplan_type_name is null or p_finplan_type_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
300 ) AND
301 ( p_budget_version_id is null or p_budget_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
302 ) THEN
303 IF l_debug_mode = 'Y' THEN
304 Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : p_update_plan_type is FINPLAN but params not passed';
305 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
306 END IF;
307 RAISE Invalid_Arg_Exc_WP;
308 END IF;
309
310 --
311 IF (p_resource_asgn_id_tab is not null and nvl(p_resource_asgn_id_tab.last,0) > 0) AND
312 (p_txn_curr_code_tab is not null and nvl(p_txn_curr_code_tab.last,0) > 0) AND
313 ( p_resource_asgn_id_tab.count <> p_txn_curr_code_tab.count )
314 THEN
315 IF l_debug_mode = 'Y' THEN
316 Pa_Debug.g_err_stage:= 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES : Txn_curr_code_tbl.count <> p_resource_asgn_id_tab.count';
317 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
318 END IF;
319 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
320 p_msg_name => 'PA_PASS_TXN_CURR_CODE');
321 RAISE Invalid_Arg_Exc_WP;
322 END IF;
323 --Validate for project_id
324 PA_PROJECT_PVT.Convert_pm_projref_to_id
325 ( p_pm_project_reference => p_pm_project_reference
326 , p_pa_project_id => p_project_id
327 , p_out_project_id => l_project_id
328 , p_return_status => l_return_status
329 );
330 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
331 RAISE Invalid_Arg_Exc_WP;
332 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
333 RAISE FND_API.G_EXC_ERROR;
334 END IF;
335 --Assign values to local variables
336 l_version_type := p_version_type;
337 l_finplan_type_id := p_finplan_type_id;
338 l_finplan_type_name := p_finplan_type_name;
339 l_budget_version_number := p_budget_version_number;
340
341 --Invoke check_edit_task_ok
342 IF (p_update_plan_type = 'WORKPLAN') THEN
343
344 IF ( p_budget_version_id is not null AND p_budget_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) THEN
345 l_budget_version_id := p_budget_version_id;
346 ELSE
347 l_budget_version_id := null;
348 END IF;
349
350 IF l_debug_mode = 'Y' THEN
351 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calling CHECK_EDIT_OK';
352 Pa_Debug.WRITE(g_module_name,pa_debug.g_err_stage ,l_debug_level3);
353 Pa_Debug.WRITE(g_module_name,'l_project_id'||l_project_id,l_debug_level3);
354 Pa_Debug.WRITE(g_module_name,'p_structure_version_id'||p_structure_version_id,l_debug_level3);
355 Pa_Debug.WRITE(g_module_name,'l_budget_version_id'||l_budget_version_id,l_debug_level3);
356 END IF;
357
358 PA_TASK_ASSIGNMENT_UTILS.CHECK_EDIT_OK(
359 p_init_msg_list => p_init_msg_list
360 , p_commit => 'F'
361 , p_project_id => l_project_id
362 , p_pa_structure_version_id => p_structure_version_id
363 , px_budget_version_id => l_budget_version_id
364 , x_return_status => l_return_status
365 , x_msg_data => l_msg_data
366 , x_msg_count => l_msg_count);
367
368 IF l_debug_mode = 'Y' THEN
369 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calling CHECK_EDIT_OK returned'||l_return_status;
370 Pa_Debug.WRITE(g_module_name,pa_debug.g_err_stage ,l_debug_level3);
371 Pa_Debug.WRITE(g_module_name,'l_budget_version_id'||l_budget_version_id,l_debug_level3);
372 END IF;
373
374 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
375 IF l_debug_mode = 'Y' THEN
376 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :CHECK_EDIT_TASK_OK returned'||l_return_status ;
377 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
378 END IF;
379 RAISE FND_API.G_EXC_ERROR;
380 END IF;
381
382 ELSIF (p_update_plan_type = 'FINPLAN') THEN
383 IF (p_budget_version_id is not null and p_budget_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
384 OPEN C_VALIDATE_FP_BUDGET_VERSION(p_budget_version_id, l_project_id);
385 FETCH C_VALIDATE_FP_BUDGET_VERSION INTO l_finplan_type_id, l_version_type, l_budget_version_number;
386 IF (C_VALIDATE_FP_BUDGET_VERSION%NOTFOUND) THEN
387 PA_UTILS.ADD_MESSAGE(
388 p_app_short_name => 'PA'
389 ,p_msg_name => 'PA_FP_INVALID_VERSION_ID'
390 ,p_token1 => 'BUDGET_VERSION_ID'
391 ,p_value1 => p_budget_version_id);
392
393 CLOSE C_VALIDATE_FP_BUDGET_VERSION;
394 raise FND_API.G_EXC_ERROR;
395 END IF;
396 CLOSE C_VALIDATE_FP_BUDGET_VERSION;
397 END IF;
398 IF l_debug_mode = 'Y' THEN
399 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calling Validate_Header_Info';
400 Pa_Debug.WRITE(g_module_name,pa_debug.g_err_stage ,l_debug_level3);
404 Pa_Debug.WRITE(g_module_name,'l_finplan_type_id'||l_finplan_type_id,l_debug_level3);
401 Pa_Debug.WRITE(g_module_name,'l_project_id'||l_project_id,l_debug_level3);
402 Pa_Debug.WRITE(g_module_name,'p_pm_project_reference'||p_pm_project_reference,l_debug_level3);
403 Pa_Debug.WRITE(g_module_name,'p_pm_product_code'||p_pm_product_code,l_debug_level3);
405 Pa_Debug.WRITE(g_module_name,'l_finplan_type_name'||l_finplan_type_name,l_debug_level3);
406 Pa_Debug.WRITE(g_module_name,'l_version_type'||l_version_type,l_debug_level3);
407 Pa_Debug.WRITE(g_module_name,'l_budget_type_code'||l_budget_type_code,l_debug_level3);
408 Pa_Debug.WRITE(g_module_name,'l_budget_version_number'||l_budget_version_number,l_debug_level3);
409 Pa_Debug.WRITE(g_module_name,'l_change_reason_code'||l_change_reason_code,l_debug_level3);
410 END IF;
411
412 PA_BUDGET_PVT.Validate_Header_Info
413 (
414 p_api_version_number => 1.0
415 ,p_init_msg_list => 'F'
416 ,p_api_name => 'REFRESH_RATES'
417 ,px_pa_project_id => l_project_id
418 ,p_pm_project_reference => p_pm_project_reference
419 ,p_pm_product_code => p_pm_product_code
420 ,px_fin_plan_type_id => l_finplan_type_id
421 ,px_fin_plan_type_name => l_finplan_type_name
422 ,px_version_type => l_version_type
423 ,px_budget_type_code => l_budget_type_code
424 ,p_budget_version_number => l_budget_version_number
425 ,p_function_name => 'PA_PM_UPDATE_BUDGET'
426 ,p_change_reason_code => l_change_reason_code
427 ,x_budget_entry_method_code => l_budget_entry_method_code
428 ,x_resource_list_id => l_resource_list_id
429 ,x_budget_version_id => l_budget_version_id
430 ,x_fin_plan_level_code => l_fin_plan_level_code
431 ,x_time_phased_code => l_time_phased_code
432 ,x_plan_in_multi_curr_flag => l_plan_in_multi_curr_flag
433 ,x_budget_amount_code => l_budget_amount_code
434 ,x_categorization_code => l_categorization_code
435 ,x_project_number => l_project_number
436 /* Plan Amount Entry flags introduced by bug 6378555 */
437 /*Passing all as G_PA_MISS_CHAR since validations not required*/
438 ,px_raw_cost_flag => l_pa_miss_char
439 ,px_burdened_cost_flag => l_pa_miss_char
440 ,px_revenue_flag => l_pa_miss_char
441 ,px_cost_qty_flag => l_pa_miss_char
442 ,px_revenue_qty_flag => l_pa_miss_char
443 ,px_all_qty_flag => l_pa_miss_char
444 ,px_bill_rate_flag => l_pa_miss_char
445 ,px_cost_rate_flag => l_pa_miss_char
446 ,px_burden_rate_flag => l_pa_miss_char
447 /* Plan Amount Entry flags introduced by bug 6378555 */
448 ,x_msg_count => l_msg_count
449 ,x_msg_data => l_msg_data
450 ,x_return_status => l_return_status
451 );
452 IF l_debug_mode = 'Y' THEN
453 Pa_Debug.WRITE(g_module_name,'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calling Validate_Header_Info'||l_return_status,l_debug_level3);
454 Pa_Debug.WRITE(g_module_name,'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calling Validate_Header_Info'||l_budget_version_id,l_debug_level3);
455 END IF;
456 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
457 IF l_debug_mode = 'Y' THEN
458 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Validate_Header_Info returned'||l_return_status ;
459 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
460 END IF;
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463 END IF;--update_plan_type FINPLAN
464
465 IF ( p_txn_curr_code_tab is not null and nvl(p_txn_curr_code_tab.last,0) > 0 ) THEN
466 l_txn_curr_passed_flag := 'Y';
467 END IF;
468
469 l_resource_asgn_id_tab.delete;
470 l_txn_curr_code_tab.delete;
471
472 --If res_asgn_id_tbl is passed
473 -----if trxn_curr_code_tbl is also passed
474 -----populate local res asgn id tbl and trxn curr tables with corresponding vales
475 -----else
476 -----derive trxn currency codes for each resource asgn id
477 -----populate local res asgn id tbl and trxn curr tables with corresponding vales
478 IF ( p_resource_asgn_id_tab is not null and nvl(p_resource_asgn_id_tab.last,0) > 0 ) THEN
479 l_resource_asgn_id := null;
480
481 FOR i IN p_resource_asgn_id_tab.FIRST..p_resource_asgn_id_tab.LAST LOOP
482 l_resource_asgn_id := p_resource_asgn_id_tab(i);
483
484 --Validate res asgn id with budget version id
485 OPEN c_validate_res_asgn_id ( l_project_id,l_budget_version_id ,l_resource_asgn_id );
486 FETCH c_validate_res_asgn_id INTO l_valid;
487 IF ( c_validate_res_asgn_id%NOTFOUND ) THEN
488 --Raise Error
489 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
490 p_msg_name => 'PA_INVALID_RES_ASGN_ID');
491 CLOSE c_validate_res_asgn_id;
492 RAISE FND_API.G_EXC_ERROR;
493 END IF;
494 CLOSE c_validate_res_asgn_id;
495
496 IF ( l_txn_curr_passed_flag = 'Y') THEN
497 IF ( p_txn_curr_code_tab(i) IS NULL OR
498 p_txn_curr_code_tab(i) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ) THEN
499 /*--Raise Error
500 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
501 p_msg_name => 'PA_PASS_TXN_CURR_CODE');
502 RAISE FND_API.G_EXC_ERROR;*/
503
504 /*
505 In Case Selective Refresh is required, we can comment above and use following
506 After discussion with Venky, it is assumed that if user is passinf txn curr
510 */
507 code tbl he/she will pass it correctly with each txn curr code.
508
509 In case if it is decided to supprot selective refresh, following code can be uncommented
511
512 OPEN c_get_txn_curr_code(l_resource_asgn_id);
513 FETCH c_get_txn_curr_code BULK COLLECT INTO l_trxn_curr_code_tmp_tbl;
514 CLOSE c_get_txn_curr_code;
515 IF (l_trxn_curr_code_tmp_tbl is not null and
516 nvl (l_trxn_curr_code_tmp_tbl.last, 0) > 0) THEN
517 FOR i IN l_trxn_curr_code_tmp_tbl.FIRST..l_trxn_curr_code_tmp_tbl.LAST LOOP
518 l_resource_asgn_id_tab.extend;
519 l_txn_curr_code_tab.extend;
520
521 l_resource_asgn_id_tab(l_resource_asgn_id_tab.count) := l_resource_asgn_id;
522 l_txn_curr_code_tab(l_txn_curr_code_tab.count) := l_trxn_curr_code_tmp_tbl(i);
523 END LOOP;
524 END IF;
525
526 ELSE
527 --Code to validate if txn currency code is correct or not
528 OPEN c_validate_curr_code(p_txn_curr_code_tab(i),l_resource_asgn_id, l_budget_version_id);
529 FETCH c_validate_curr_code INTO l_valid_currency;
530 IF (c_validate_curr_code%NOTFOUND) THEN
531 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
532 , p_msg_name => 'PA_INVALID_CURR_CODE'
533 , p_token1 => 'TxncurrencyCode'
534 , p_value1 => p_txn_curr_code_tab(i)
535 , p_token2 => 'ResourceAssignmentID'
536 , p_value2 => l_resource_asgn_id);
537 CLOSE c_validate_curr_code;
538 RAISE FND_API.G_EXC_ERROR;
539 END IF;
540 CLOSE c_validate_curr_code;
541 l_resource_asgn_id_tab.extend;
542 l_txn_curr_code_tab.extend;
543
544 l_resource_asgn_id_tab(l_resource_asgn_id_tab.count) := l_resource_asgn_id;
545 l_txn_curr_code_tab(l_txn_curr_code_tab.count) := p_txn_curr_code_tab(i);
546 END IF;
547 ELSE
548 OPEN c_get_txn_curr_code(l_resource_asgn_id);
549 FETCH c_get_txn_curr_code BULK COLLECT INTO l_trxn_curr_code_tmp_tbl;
550 CLOSE c_get_txn_curr_code;
551 IF (l_trxn_curr_code_tmp_tbl is not null and
552 nvl (l_trxn_curr_code_tmp_tbl.last, 0) > 0) THEN
553 FOR i IN l_trxn_curr_code_tmp_tbl.FIRST..l_trxn_curr_code_tmp_tbl.LAST LOOP
554 l_resource_asgn_id_tab.extend;
555 l_txn_curr_code_tab.extend;
556
557 l_resource_asgn_id_tab(l_resource_asgn_id_tab.count) := l_resource_asgn_id;
558 l_txn_curr_code_tab(l_txn_curr_code_tab.count) := l_trxn_curr_code_tmp_tbl(i);
559 END LOOP;
560 END IF;
561 END IF;
562 END LOOP;
563 l_call_with_res_txn_tbl_flag := 'Y';
564 --else if
565 --resource_class_codes are passed
566 --1. For each resource class code, project_id, budget_version_id , get all res asgn id and their corres-
567 --ponding txn currency codes
568 --2. Populate local plsql tables l_resource_asgn_id_tab and l_txn_curr_code_tab
569 ELSIF (p_resource_class_code_tab is not null and nvl(p_resource_class_code_tab.last,0) > 0) THEN
570 l_raid_tmp_tbl.delete;
571 l_trxn_curr_code_tmp_tbl.delete;
572
573 FOR i IN p_resource_class_code_tab.FIRST..p_resource_class_code_tab.LAST LOOP
574 OPEN c_get_all_raid_txn_curr(l_project_id , l_budget_version_id , p_resource_class_code_tab(i) );
575 FETCH c_get_all_raid_txn_curr BULK COLLECT INTO l_raid_tmp_tbl, l_trxn_curr_code_tmp_tbl;
576 CLOSE c_get_all_raid_txn_curr;
577
578 IF (l_raid_tmp_tbl is not null and nvl(l_raid_tmp_tbl.count,0) >0) THEN
579 FOR i IN l_raid_tmp_tbl.FIRST..l_raid_tmp_tbl.LAST LOOP
580 l_resource_asgn_id_tab.extend;
581 l_txn_curr_code_tab.extend;
582 l_resource_asgn_id_tab(l_resource_asgn_id_tab.count) := l_raid_tmp_tbl(i);
583 l_txn_curr_code_tab(l_txn_curr_code_tab.count) := l_trxn_curr_code_tmp_tbl(i);
584 END LOOP;
585 END IF;
586 l_raid_tmp_tbl.delete;
587 l_trxn_curr_code_tmp_tbl.delete;
588 END LOOP;
589 l_call_with_res_txn_tbl_flag := 'Y';
590
591 --else nothing is passed, refresh for budget version id
592 ELSE
593 PA_FP_CALC_PLAN_PKG.calculate(
594 p_project_id => l_project_id
595 , p_budget_version_id => l_budget_version_id
596 , p_refresh_rates_flag => p_refresh_cost_bill_rates_flag
597 , p_refresh_conv_rates_flag => p_refresh_conv_rates_flag
598 , p_source_context => 'RESOURCE_ASSIGNMENT'
599 , x_return_status => l_return_status
600 , x_msg_count => l_msg_count
601 , x_msg_data => l_msg_data
602 );
603 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
604 IF l_debug_mode = 'Y' THEN
605 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calculate returned '||l_return_status ;
606 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
607 END IF;
608 RAISE FND_API.G_EXC_ERROR;
609 END IF;
610
611 END IF;
612
613 IF (l_call_with_res_txn_tbl_flag = 'Y' ) THEN
614 IF l_debug_mode = 'Y' THEN
615 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calling Calculate';
616 Pa_Debug.WRITE(g_module_name,pa_debug.g_err_stage ,l_debug_level3);
617 Pa_Debug.WRITE(g_module_name,'l_project_id'||l_project_id,l_debug_level3);
618 Pa_Debug.WRITE(g_module_name,'l_budget_version_id'||l_budget_version_id,l_debug_level3);
622 Pa_Debug.WRITE(g_module_name,'l_resource_asgn_id_tab'||to_char(l_resource_asgn_id_tab(i)),l_debug_level3);
619 Pa_Debug.WRITE(g_module_name,'p_refresh_cost_bill_rates_flag'||p_refresh_cost_bill_rates_flag,l_debug_level3);
620 Pa_Debug.WRITE(g_module_name,'p_refresh_conv_rates_flag'||p_refresh_conv_rates_flag,l_debug_level3);
621 FOR i IN l_resource_asgn_id_tab.FIRST..l_resource_asgn_id_tab.LAST LOOP
623 Pa_Debug.WRITE(g_module_name,'l_txn_curr_code_tab'||to_char( l_txn_curr_code_tab(i)),l_debug_level3);
624 END LOOP;
625 END IF;
626 PA_FP_CALC_PLAN_PKG.calculate(
627 p_project_id => l_project_id
628 , p_budget_version_id => l_budget_version_id
629 , p_refresh_rates_flag => p_refresh_cost_bill_rates_flag
630 , p_refresh_conv_rates_flag => p_refresh_conv_rates_flag
631 , p_source_context => 'RESOURCE_ASSIGNMENT'
632 , p_resource_assignment_tab => l_resource_asgn_id_tab
633 , p_txn_currency_code_tab => l_txn_curr_code_tab
634 , x_return_status => l_return_status
635 , x_msg_count => l_msg_count
636 , x_msg_data => l_msg_data
637 );
638 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
639 IF l_debug_mode = 'Y' THEN
640 pa_debug.g_err_stage := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Calculate returned '||l_return_status ;
641 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,l_debug_level3);
642 END IF;
643 RAISE FND_API.G_EXC_ERROR;
644 END IF;
645 END IF;
646
647
648
649 IF (p_commit = FND_API.G_TRUE) THEN
650 COMMIT;
651 END IF;
652
653
654 EXCEPTION
655
656 WHEN FND_API.G_EXC_ERROR THEN
657
658 x_return_status := Fnd_Api.G_RET_STS_ERROR;
659 l_msg_count := Fnd_Msg_Pub.count_msg;
660
661 IF p_commit = FND_API.G_TRUE THEN
662 ROLLBACK TO REFRESH_RATES_PUBLIC;
663 END IF;
664
665 IF c_validate_res_asgn_id%ISOPEN THEN
666 CLOSE c_validate_res_asgn_id;
667 END IF;
668
669 IF c_get_all_raid_txn_curr%ISOPEN THEN
670 CLOSE c_get_all_raid_txn_curr;
671 END IF;
672
673 IF c_get_txn_curr_code%ISOPEN THEN
674 CLOSE c_get_txn_curr_code;
675 END IF;
676
677 IF C_VALIDATE_FP_BUDGET_VERSION%ISOPEN THEN
678 CLOSE C_VALIDATE_FP_BUDGET_VERSION;
679 END IF;
680
681 IF c_validate_curr_code%ISOPEN THEN
682 CLOSE c_validate_curr_code;
683 END IF;
684
685 IF l_msg_count = 1 AND x_msg_data IS NULL
686 THEN
687 Pa_Interface_Utils_Pub.get_messages
688 ( p_encoded => Fnd_Api.G_TRUE
689 , p_msg_index => 1
690 , p_msg_count => l_msg_count
691 , p_msg_data => l_msg_data
692 , p_data => l_data
693 , p_msg_index_out => l_msg_index_out);
694 x_msg_data := l_data;
695 x_msg_count := l_msg_count;
696 ELSE
697 x_msg_count := l_msg_count;
698 END IF;
699 IF l_debug_mode = 'Y' THEN
700 Pa_Debug.reset_curr_function;
701 END IF;
702
703 WHEN Invalid_Arg_Exc_WP THEN
704
705 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
706 x_msg_count := 1;
707 x_msg_data := 'PA_FP_CALC_PLAN_PUB : REFRESH_RATES :Invalid Arguments are passed';
708
709 IF p_commit = FND_API.G_TRUE THEN
710 ROLLBACK TO REFRESH_RATES_PUBLIC;
711 END IF;
712
713 IF c_validate_res_asgn_id%ISOPEN THEN
714 CLOSE c_validate_res_asgn_id;
715 END IF;
716
717 IF c_get_all_raid_txn_curr%ISOPEN THEN
718 CLOSE c_get_all_raid_txn_curr;
719 END IF;
720
721 IF c_get_txn_curr_code%ISOPEN THEN
722 CLOSE c_get_txn_curr_code;
723 END IF;
724
725 IF C_VALIDATE_FP_BUDGET_VERSION%ISOPEN THEN
726 CLOSE C_VALIDATE_FP_BUDGET_VERSION;
727 END IF;
728
729 IF c_validate_curr_code%ISOPEN THEN
730 CLOSE c_validate_curr_code;
731 END IF;
732
733 Fnd_Msg_Pub.add_exc_msg
734 ( p_pkg_name => 'PA_FP_CALC_PLAN_PUB'
735 , p_procedure_name => 'REFRESH_RATES'
736 , p_error_text => x_msg_data);
737
738 IF l_debug_mode = 'Y' THEN
739 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
740 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
741 l_debug_level5);
742 Pa_Debug.reset_curr_function;
743 END IF;
744 -- RAISE; Bug 4530531
745
746 WHEN OTHERS THEN
747
748 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
749 x_msg_count := 1;
750 x_msg_data := SQLERRM;
751
752 IF p_commit = FND_API.G_TRUE THEN
753 ROLLBACK TO REFRESH_RATES_PUBLIC;
754 END IF;
755
756 IF c_validate_res_asgn_id%ISOPEN THEN
757 CLOSE c_validate_res_asgn_id;
758 END IF;
759
760 IF c_get_all_raid_txn_curr%ISOPEN THEN
761 CLOSE c_get_all_raid_txn_curr;
762 END IF;
763
764 IF c_get_txn_curr_code%ISOPEN THEN
765 CLOSE c_get_txn_curr_code;
766 END IF;
767
768 IF C_VALIDATE_FP_BUDGET_VERSION%ISOPEN THEN
769 CLOSE C_VALIDATE_FP_BUDGET_VERSION;
773 CLOSE c_validate_curr_code;
770 END IF;
771
772 IF c_validate_curr_code%ISOPEN THEN
774 END IF;
775
776 Fnd_Msg_Pub.add_exc_msg
777 ( p_pkg_name => 'PA_FP_CALC_PLAN_PUB'
778 , p_procedure_name => 'REFRESH_RATES'
779 , p_error_text => x_msg_data);
780
781 IF l_debug_mode = 'Y' THEN
782 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
783 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
784 l_debug_level5);
785 Pa_Debug.reset_curr_function;
786 END IF;
787 -- RAISE; Bug 4530531
788
789 END REFRESH_RATES ;
790
791
792 END PA_FP_CALC_PLAN_PUB;