[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
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
57 , p_resource_class_code_tab IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
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;
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
181 PA_DEBUG.set_curr_function( p_function => 'REFRESH_RATES',
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'
286 ,p_msg_context => 'GENERAL'
287 ,p_attribute1 => ''
288 ,p_attribute2 => ''
289 ,p_attribute3 => ''
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);
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);
404 Pa_Debug.WRITE(g_module_name,'l_finplan_type_id'||l_finplan_type_id,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
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
510 */
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);
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
622 Pa_Debug.WRITE(g_module_name,'l_resource_asgn_id_tab'||to_char(l_resource_asgn_id_tab(i)),l_debug_level3);
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;
770 END IF;
771
772 IF c_validate_curr_code%ISOPEN THEN
773 CLOSE c_validate_curr_code;
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;