[Home] [Help]
PACKAGE BODY: APPS.PA_FP_MULTI_CURRENCY_PKG
Source
1 PACKAGE BODY PA_FP_MULTI_CURRENCY_PKG AS
2 --$Header: PAFPMCPB.pls 120.3 2006/07/26 10:24:10 prachand noship $
3 /* Perf Bug: 3683132 */
4 g_cache_fp_plan_version_id Number;
5 g_cache_fp_txn_cur_code Varchar2(100);
6 g_cache_fp_context Varchar2(100);
7 g_cache_fp_mode Varchar2(100);
8 g_fp_projfunc_cost_exchng_rt Number;
9 g_fp_projfunc_rev_exchng_rt Number;
10 g_fp_project_cost_exchng_rt Number;
11 g_fp_project_rev_exchng_rt Number;
12 g_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_MULTI_CURRENCY_PKG';
13
14 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
15
16 /* Perf Bug: 3683132 */
17 /*====================================================================+
18 | Bug 4094376: Refreshed global variables g_cache_fp_plan_version_id |
19 | g_cache_fp_txn_cur_code. |
20 +====================================================================*/
21 FUNCTION get_fp_cur_details( p_budget_version_id Number
22 ,p_txn_currency_code Varchar2
23 ,p_context Varchar2 default 'COST'
24 ,p_mode Varchar2 default 'PROJECT' ) RETURN NUMBER IS
25
26 CURSOR cur_details IS
27 SELECT c.projfunc_cost_exchange_rate
28 ,c.projfunc_rev_exchange_rate
29 ,c.project_cost_exchange_rate
30 ,c.project_rev_exchange_rate
31 FROM pa_fp_txn_currencies c
32 ,pa_proj_fp_options fp
33 WHERE fp.fin_plan_version_id = p_budget_version_id
34 AND fp.fin_plan_version_id = c.fin_plan_version_id
35 AND fp.proj_fp_options_id = c.proj_fp_options_id
36 AND c.txn_currency_code = p_txn_currency_code ;
37
38 l_projfunc_cost_exchange_rate Number;
39 l_projfunc_rev_exchange_rate Number;
40 l_project_cost_exchange_rate Number;
41 l_project_rev_exchange_rate Number;
42 l_return_exchg_rate Number;
43 BEGIN
44 IF p_budget_version_id is NOT NULL AND p_txn_currency_code is NOT NULL Then
45 IF (p_budget_version_id = g_cache_fp_plan_version_id
46 AND p_txn_currency_code = g_cache_fp_txn_cur_code ) THEN
47 l_projfunc_cost_exchange_rate := g_fp_projfunc_cost_exchng_rt;
48 l_projfunc_rev_exchange_rate := g_fp_projfunc_rev_exchng_rt;
49 l_project_cost_exchange_rate := g_fp_project_cost_exchng_rt;
50 l_project_rev_exchange_rate := g_fp_project_rev_exchng_rt;
51 ELSE
52 l_projfunc_cost_exchange_rate := NULL;
53 l_projfunc_rev_exchange_rate := NULL;
54 l_project_cost_exchange_rate := NULL;
55 l_project_rev_exchange_rate := NULL;
56 OPEN cur_details;
57 FETCH cur_details INTO
58 l_projfunc_cost_exchange_rate
59 ,l_projfunc_rev_exchange_rate
60 ,l_project_cost_exchange_rate
61 ,l_project_rev_exchange_rate ;
62 CLOSE cur_details;
63
64 /** assign the values to global variables **/
65 g_cache_fp_plan_version_id := p_budget_version_id;
66 g_cache_fp_txn_cur_code := p_txn_currency_code;
67 g_fp_projfunc_cost_exchng_rt := l_projfunc_cost_exchange_rate;
68 g_fp_projfunc_rev_exchng_rt := l_projfunc_rev_exchange_rate;
69 g_fp_project_cost_exchng_rt := l_project_cost_exchange_rate;
70 g_fp_project_rev_exchng_rt := l_project_rev_exchange_rate;
71 END IF;
72 END IF;
73
74 If p_context = 'COST' Then
75 If p_mode = 'PROJECT' Then
76 l_return_exchg_rate := l_project_cost_exchange_rate;
77 Elsif p_mode = 'PROJFUNC' Then
78 l_return_exchg_rate := l_projfunc_cost_exchange_rate;
79 End If;
80 Else
81 If p_mode = 'PROJECT' Then
82 l_return_exchg_rate := l_project_rev_exchange_rate;
83 Elsif p_mode = 'PROJFUNC' Then
84 l_return_exchg_rate := l_projfunc_rev_exchange_rate;
85 End If;
86 End If;
87
88 RETURN l_return_exchg_rate;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 RAISE;
93
94 END get_fp_cur_details;
95
96
97 PROCEDURE conv_mc_bulk ( p_resource_assignment_id_tab IN
98 pa_fp_multi_currency_pkg.number_type_tab
99 ,p_start_date_tab IN
100 pa_fp_multi_currency_pkg.date_type_tab
101 ,p_end_date_tab IN
102 pa_fp_multi_currency_pkg.date_type_tab
103 ,p_txn_currency_code_tab IN
104 pa_fp_multi_currency_pkg.char240_type_tab
105 ,p_txn_raw_cost_tab IN
106 pa_fp_multi_currency_pkg.number_type_tab
107 ,p_txn_burdened_cost_tab IN
108 pa_fp_multi_currency_pkg.number_type_tab
109 ,p_txn_revenue_tab IN
110 pa_fp_multi_currency_pkg.number_type_tab
111 ,p_projfunc_currency_code_tab IN
112 pa_fp_multi_currency_pkg.char240_type_tab
113 ,p_projfunc_cost_rate_type_tab IN
114 pa_fp_multi_currency_pkg.char240_type_tab
115 ,p_projfunc_cost_rate_tab IN OUT NOCOPY
116 pa_fp_multi_currency_pkg.number_type_tab
117 ,p_projfunc_cost_rate_date_tab IN
118 pa_fp_multi_currency_pkg.date_type_tab
119 ,p_projfunc_rev_rate_type_tab IN
120 pa_fp_multi_currency_pkg.char240_type_tab
121 ,p_projfunc_rev_rate_tab IN OUT NOCOPY
122 pa_fp_multi_currency_pkg.number_type_tab
123 ,p_projfunc_rev_rate_date_tab IN
124 pa_fp_multi_currency_pkg.date_type_tab
125 ,x_projfunc_raw_cost_tab OUT NOCOPY
126 pa_fp_multi_currency_pkg.number_type_tab
127 ,x_projfunc_burdened_cost_tab OUT NOCOPY
128 pa_fp_multi_currency_pkg.number_type_tab
129 ,x_projfunc_revenue_tab OUT NOCOPY
130 pa_fp_multi_currency_pkg.number_type_tab
131 ,x_projfunc_rejection_tab OUT NOCOPY
132 pa_fp_multi_currency_pkg.char30_type_tab
133 ,p_proj_currency_code_tab IN
134 pa_fp_multi_currency_pkg.char240_type_tab
135 ,p_proj_cost_rate_type_tab IN
136 pa_fp_multi_currency_pkg.char240_type_tab
137 ,p_proj_cost_rate_tab IN OUT NOCOPY
138 pa_fp_multi_currency_pkg.number_type_tab
139 ,p_proj_cost_rate_date_tab IN
140 pa_fp_multi_currency_pkg.date_type_tab
141 ,p_proj_rev_rate_type_tab IN
142 pa_fp_multi_currency_pkg.char240_type_tab
143 ,p_proj_rev_rate_tab IN OUT NOCOPY
144 pa_fp_multi_currency_pkg.number_type_tab
145 ,p_proj_rev_rate_date_tab IN
146 pa_fp_multi_currency_pkg.date_type_tab
147 ,x_proj_raw_cost_tab OUT NOCOPY
148 pa_fp_multi_currency_pkg.number_type_tab
149 ,x_proj_burdened_cost_tab OUT NOCOPY
150 pa_fp_multi_currency_pkg.number_type_tab
151 ,x_proj_revenue_tab OUT NOCOPY
152 pa_fp_multi_currency_pkg.number_type_tab
153 ,x_proj_rejection_tab OUT NOCOPY
154 pa_fp_multi_currency_pkg.char30_type_tab
155 ,p_user_validate_flag_tab IN
156 pa_fp_multi_currency_pkg.char240_type_tab
157 ,p_calling_module IN
158 VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION' -- Added for bug#5395732
159 ,x_return_status OUT NOCOPY --File.Sql.39 bug 4440895
160 VARCHAR2
161 ,x_msg_count OUT NOCOPY --File.Sql.39 bug 4440895
162 NUMBER
163 ,x_msg_data OUT NOCOPY --File.Sql.39 bug 4440895
164 VARCHAR2) IS
165
166 l_converted_amount NUMBER;
167 l_numerator NUMBER;
168 l_denominator NUMBER;
169 l_rate NUMBER;
170 l_tab_count NUMBER;
171 l_done_flag VARCHAR2(1);
172 l_cached_count NUMBER;
173 l_stage NUMBER;
174 l_debug_mode VARCHAR2(30);
175 l_number NUMBER;
176
177 l_project_name pa_projects_all.name%TYPE;
178 l_task_name pa_proj_elements.name%TYPE;
179 l_resource_name pa_resource_list_members.alias%TYPE;
180 l_resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE;
181
182 l_allow_user_rate_type VARCHAR2(1);
183 l_call_closest_flag varchar2(1) := 'F'; -- Added for Bug#5395732
184
185 CachedRowTab pa_fp_multi_currency_pkg.cached_row_tab;
186
187 /** Bug fix: 4199085 . No need to use this curosr as we are not adding the error msg to stack
188 CURSOR get_line_info (p_resource_assignment_id IN NUMBER) IS
189 SELECT ppa.name project_name
190 ,pt.name task_name
191 ,prl.alias resource_name
192 FROM pa_projects_all ppa
193 ,pa_proj_elements pt
194 ,pa_resource_list_members prl
195 ,pa_resource_assignments pra
196 WHERE pra.resource_assignment_id = p_resource_assignment_id
197 AND ppa.project_id = pra.project_id
198 AND pt.proj_element_id(+) = pra.task_id
199 AND prl.resource_list_member_id(+) = pra.resource_list_member_id;
200 **/
201
202 BEGIN
203
204 x_return_status := FND_API.G_RET_STS_SUCCESS;
205
206 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
207 --l_debug_mode := NVL(l_debug_mode,'Y');
208 IF P_PA_DEBUG_MODE = 'Y' THEN
209 pa_debug.init_err_stack('PA_FP_MULTI_CURRENCY_PKG.conv_mc_bulk');
210 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
211 END IF;
212
213 pa_debug.g_err_stage := 'Entered PA_FP_MULTI_CURRENCY_PKG.conv_mc_bulk';
214 IF P_PA_DEBUG_MODE = 'Y' THEN
215 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
216 END IF;
217
218 l_tab_count := p_txn_currency_code_tab.COUNT;
219 l_cached_count := CachedRowTab.COUNT;
220
221 l_stage := 100;
222 --hr_utility.trace(to_char(l_stage));
223
224 IF l_tab_count = 0 THEN
225 pa_debug.g_err_stage := to_char(l_stage)||': No records selected -- Returning';
226 IF P_PA_DEBUG_MODE = 'Y' THEN
227 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
228 END IF;
229 RETURN;
230 END IF;
231
232 pa_debug.g_err_stage := to_char(l_stage)||': Records selected '||to_char(l_tab_count);
233 IF P_PA_DEBUG_MODE = 'Y' THEN
234 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
235 END IF;
236
237
238 FOR i in p_txn_currency_code_tab.first..p_txn_currency_code_tab.last LOOP
239
240 --hr_utility.trace('p_resource_assignment_id_tab => '||to_char(p_resource_assignment_id_tab(i)));
241 --hr_utility.trace('p_start_date_tab => '||p_start_date_tab(i));
242 --hr_utility.trace('p_end_date_tab => '||p_end_date_tab(i));
243 --hr_utility.trace('p_txn_currency_code_tab => '||p_txn_currency_code_tab(i));
244 --hr_utility.trace('p_txn_raw_cost_tab => '||to_char(p_txn_raw_cost_tab(i)));
245 --hr_utility.trace('p_txn_burdened_cost_tab => '||to_char(p_txn_burdened_cost_tab(i)));
246 --hr_utility.trace('p_txn_revenue_tab => '||to_char(p_txn_revenue_tab(i)));
247 --hr_utility.trace('p_projfunc_currency_code_tab => '||p_projfunc_currency_code_tab(i));
248 --hr_utility.trace('p_projfunc_cost_rate_type_tab => '||p_projfunc_cost_rate_type_tab(i));
249 --hr_utility.trace('p_projfunc_cost_rate_tab => '||p_projfunc_cost_rate_tab(i));
250 --hr_utility.trace('p_projfunc_cost_rate_date_tab => '||p_projfunc_cost_rate_date_tab(i));
251 --hr_utility.trace('p_projfunc_rev_rate_type_tab => '||p_projfunc_rev_rate_type_tab(i));
252 --hr_utility.trace('p_projfunc_rev_rate_tab => '||p_projfunc_rev_rate_tab(i));
253 --hr_utility.trace('p_projfunc_rev_rate_date_tab => '||p_projfunc_rev_rate_date_tab(i));
254 --hr_utility.trace('p_proj_currency_code_tab => '||p_proj_currency_code_tab(i));
255 --hr_utility.trace('p_proj_cost_rate_type_tab => '||p_proj_cost_rate_type_tab(i));
256 --hr_utility.trace('p_proj_cost_rate_tab => '||p_proj_cost_rate_tab(i));
257 --hr_utility.trace('p_proj_cost_rate_date_tab => '||p_proj_cost_rate_date_tab(i));
258 --hr_utility.trace('p_proj_rev_rate_type_tab => '||p_proj_rev_rate_type_tab(i));
259 --hr_utility.trace('p_proj_rev_rate_tab => '||p_proj_rev_rate_tab(i));
260 --hr_utility.trace('p_proj_rev_rate_date_tab => '||p_proj_rev_rate_date_tab(i));
261
262 x_projfunc_raw_cost_tab(i) := NULL;
263 x_projfunc_burdened_cost_tab(i) := NULL;
264 x_projfunc_revenue_tab(i) := NULL;
265 x_projfunc_rejection_tab(i) := NULL;
266 x_proj_raw_cost_tab(i) := NULL;
267 x_proj_burdened_cost_tab(i) := NULL;
268 x_proj_revenue_tab(i) := NULL;
269 x_proj_rejection_tab(i) := NULL;
270
271 /* Bug fix:4199085 This cursor is being executed 50000 times. After verifying the code, the
272 * the values l_project_name,l_task_name,l_resource_name are not being used any more
273 * as we setting the rejection code instead of adding to error msg stack.
274 * so commenting out the opening of this cursor
275 open get_line_info(p_resource_assignment_id_tab(i));
276 l_stage := 110;
277 fetch get_line_info into l_project_name, l_task_name, l_resource_name;
278 close get_line_info;
279 **/
280
281 -- Convert TxnCurrency to ProjectFunctionalCurrency
282 l_stage := 200;
283 --hr_utility.trace(to_char(l_stage));
284 IF p_txn_currency_code_tab(i) = p_projfunc_currency_code_tab(i) THEN
285 l_stage := 300;
286 --hr_utility.trace(to_char(l_stage));
287 p_projfunc_cost_rate_tab(i) := NULL;
288 x_projfunc_raw_cost_tab(i) := p_txn_raw_cost_tab(i);
289 x_projfunc_burdened_cost_tab(i):= p_txn_burdened_cost_tab(i);
290 p_projfunc_rev_rate_tab(i) := NULL;
291 x_projfunc_revenue_tab(i) := p_txn_revenue_tab(i);
292
293 l_number := x_projfunc_raw_cost_tab(i);
294 --hr_utility.trace('x_projfunc_raw_cost_tab(i) = '||to_char(l_number));
295 l_number := x_projfunc_burdened_cost_tab(i);
296 --hr_utility.trace('x_projfunc_burdened_cost_tab(i) = '||to_char(l_number));
297 ELSE
298 l_stage := 400;
299 --hr_utility.trace(to_char(l_stage));
300 -- Convert TxnCost to ProjectFunctional Cost
301 IF NVL(p_txn_raw_cost_tab(i),0) <> 0 OR NVL(p_txn_burdened_cost_tab(i),0) <> 0 THEN
302 l_stage := 500;
303 --hr_utility.trace(to_char(l_stage));
304 pa_debug.g_err_stage := to_char(l_stage);
305 IF P_PA_DEBUG_MODE = 'Y' THEN
306 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
307 END IF;
308
309 IF p_projfunc_cost_rate_type_tab(i) = 'User' THEN
310 l_stage := 600;
311 --hr_utility.trace(to_char(l_stage));
312 l_allow_user_rate_type := pa_multi_currency.is_user_rate_type_allowed
313 ( p_txn_currency_code_tab(i)
314 ,p_projfunc_currency_code_tab(i)
315 ,p_projfunc_cost_rate_date_tab(i));
316 IF l_allow_user_rate_type = 'Y' THEN
317 IF p_projfunc_cost_rate_tab(i) IS NOT NULL THEN
318 l_stage := 700;
319 --hr_utility.trace(to_char(l_stage));
320 x_projfunc_raw_cost_tab(i) := p_txn_raw_cost_tab(i) *
321 NVL(p_projfunc_cost_rate_tab(i),1);
322 x_projfunc_burdened_cost_tab(i) := p_txn_burdened_cost_tab(i) *
323 NVL(p_projfunc_cost_rate_tab(i),1);
324 /* Rounding Enhancements */
325 x_projfunc_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1(x_projfunc_raw_cost_tab(i),p_projfunc_currency_code_tab(i));
326 x_projfunc_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1(x_projfunc_burdened_cost_tab(i),p_projfunc_currency_code_tab(i));
327 ELSE
328 l_stage := 800;
329 --hr_utility.trace(to_char(l_stage));
330 pa_debug.g_err_stage := to_char(l_stage)||': ProjFunc Cost Rate Not Defined';
331 IF P_PA_DEBUG_MODE = 'Y' THEN
332 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
333 END IF;
334 /*
335 pa_utils.add_message
336 ( p_app_short_name => 'PA',
337 p_msg_name => 'PA_FP_PF_COST_RATE_NOT_DEFINED',
338 p_token1 => 'PROJECT' ,
339 p_value1 => l_project_name,
340 p_token2 => 'TASK',
341 p_value2 => l_task_name,
342 p_token3 => 'RESOURCE_NAME',
343 p_value3 => l_resource_name,
344 p_token4 => 'START_DATE',
345 p_value4 => p_start_date_tab(i));
346 fnd_msg_pub.count_and_get (p_count => x_msg_count,
347 p_data => x_msg_data);
348 x_msg_count := fnd_msg_pub.count_msg;
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 */
351 x_projfunc_rejection_tab(i) := 'PA_FP_PF_COST_RATE_NOT_DEFINED';
352 END IF;
353 ELSE
354 l_stage := 810;
355 --hr_utility.trace(to_char(l_stage));
356 pa_debug.g_err_stage := to_char(l_stage)||': Cost Rate type of User not allowed in ProjFunc Currency';
357 IF P_PA_DEBUG_MODE = 'Y' THEN
358 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
359 END IF;
360 /*
361 pa_utils.add_message
362 ( p_app_short_name => 'PA',
363 p_msg_name => 'PA_FP_PFC_USR_RATE_NOT_ALLOWED',
364 p_token1 => 'PROJECT' ,
365 p_value1 => l_project_name,
366 p_token2 => 'TASK',
367 p_value2 => l_task_name,
368 p_token3 => 'RESOURCE_NAME',
369 p_value3 => l_resource_name,
370 p_token4 => 'START_DATE',
371 p_value4 => p_start_date_tab(i),
372 p_token5 => 'TXN_CURRENCY',
373 p_value5 => p_txn_currency_code_tab(i));
374 fnd_msg_pub.count_and_get (p_count => x_msg_count,
375 p_data => x_msg_data);
376 x_msg_count := fnd_msg_pub.count_msg;
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 */
379 x_projfunc_rejection_tab(i) := 'PA_FP_PFC_USR_RATE_NOT_ALLOWED';
380 END IF;
381 ELSE
382 l_stage := 900;
383 --hr_utility.trace(to_char(l_stage));
384 pa_debug.g_err_stage := to_char(l_stage);
385 IF P_PA_DEBUG_MODE = 'Y' THEN
386 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
387 END IF;
388
389 l_done_flag := 'N';
390 IF nvl(CachedRowTab.COUNT,0) <> 0 THEN
391 l_stage := 1000;
392 --hr_utility.trace(to_char(l_stage));
393 FOR j in CachedRowTab.First..CachedRowTab.Last LOOP
394 IF CachedRowTab(j).from_currency = p_txn_currency_code_tab(i) AND
395 CachedRowTab(j).to_currency = p_projfunc_currency_code_tab(i) AND
396 CachedRowTab(j).rate_date = p_projfunc_cost_rate_date_tab(i) AND
397 CachedRowTab(j).rate_type = p_projfunc_cost_rate_type_tab(i) AND
398 CachedRowTab(j).line_type = 'COST' THEN
399 l_stage := 1100;
400 --hr_utility.trace(to_char(l_stage));
401 p_projfunc_cost_rate_tab(i) := CachedRowTab(j).rate;
402 x_projfunc_raw_cost_tab(i) := nvl(p_txn_raw_cost_tab(i),0) *
403 (round(CachedRowTab(j).numerator/
404 CachedRowTab(j).denominator,20));
405 x_projfunc_burdened_cost_tab(i):= nvl(p_txn_burdened_cost_tab(i),0) *
406 (round(CachedRowTab(j).numerator/
407 CachedRowTab(j).denominator,20));
408 /* Rounding Enhancements */
409 x_projfunc_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1
410 (x_projfunc_raw_cost_tab(i),p_projfunc_currency_code_tab(i));
411 x_projfunc_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
412 (x_projfunc_burdened_cost_tab(i),p_projfunc_currency_code_tab(i));
413
414 l_done_flag := 'Y';
415 EXIT;
416 END IF; -- Cost Rate found
417 END LOOP; -- cached cost rates
418 END IF; -- CachedRowTab.COUNT > 0
419 IF l_done_flag = 'N' THEN
420 l_stage := 1200;
421 --hr_utility.trace(to_char(l_stage));
422 pa_debug.g_err_stage := to_char(l_stage);
423 IF P_PA_DEBUG_MODE = 'Y' THEN
424 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
425 END IF;
426
427 IF nvl(p_txn_raw_cost_tab(i),0) <> 0 THEN
428 l_stage := 1300;
429 --hr_utility.trace(to_char(l_stage));
430 pa_debug.g_err_stage := 'pfc cost rate date' || p_projfunc_cost_rate_date_tab(i);
431 IF P_PA_DEBUG_MODE = 'Y' THEN
432 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
433 END IF;
434
435 pa_debug.g_err_stage := 'pfc cost rate type' || p_projfunc_cost_rate_type_tab(i);
436 IF P_PA_DEBUG_MODE = 'Y' THEN
437 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
438 END IF;
439 l_converted_amount := gl_currency_api.convert_amount_sql
440 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
441 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
442 ,X_CONVERSION_DATE => p_projfunc_cost_rate_date_tab(i)
443 ,X_CONVERSION_TYPE => p_projfunc_cost_rate_type_tab(i)
444 ,X_AMOUNT => p_txn_raw_cost_tab(i));
445 IF l_converted_amount = -1 THEN
446 /* Added the If block for Bug#5395732 */
447 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION')) THEN
448 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
449 ( x_from_currency => p_txn_currency_code_tab(i)
450 ,x_to_currency => p_projfunc_currency_code_tab(i)
451 ,x_conversion_date => p_projfunc_cost_rate_date_tab(i)
452 ,x_conversion_type => p_projfunc_cost_rate_type_tab(i)
453 ,x_user_rate => 1
454 ,x_amount => p_txn_raw_cost_tab(i)
455 ,x_max_roll_days => -1) ;
456 l_call_closest_flag := 'T';
457 END IF;
458 IF l_converted_amount = -1 THEN
459
460 l_stage := 1400;
461 --hr_utility.trace(to_char(l_stage));
462 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given ProjFunc currency attributes. Please change the Currency attributes';
463 IF P_PA_DEBUG_MODE = 'Y' THEN
464 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
465 END IF;
466 /*
467 pa_utils.add_message
468 ( p_app_short_name => 'PA',
469 p_msg_name => 'PA_FP_NO_PF_EXCH_RATE_EXISTS',
470 p_token1 => 'PROJECT' ,
471 p_value1 => l_project_name,
472 p_token2 => 'TASK',
473 p_value2 => l_task_name,
474 p_token3 => 'RESOURCE_NAME',
475 p_value3 => l_resource_name,
476 p_token4 => 'RATE_DATE',
477 p_value4 => p_projfunc_cost_rate_date_tab(i),
478 p_token5 => 'TXN_CURRENCY',
479 p_value5 => p_txn_currency_code_tab(i));
480 fnd_msg_pub.count_and_get (p_count => x_msg_count,
481 p_data => x_msg_data);
482 x_msg_count := fnd_msg_pub.count_msg;
483 x_return_status := FND_API.G_RET_STS_ERROR;
484 */
485 x_projfunc_rejection_tab(i) := 'PA_FP_NO_PF_EXCH_RATE_EXISTS';
486 END IF;
487 ELSIF l_converted_amount = -2 THEN
488 l_stage := 1500;
489 --hr_utility.trace(to_char(l_stage));
490 pa_debug.g_err_stage := to_char(l_stage)||'The Currency you have entered is not valid. Please re-enter the Currency';
491 IF P_PA_DEBUG_MODE = 'Y' THEN
492 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
493 END IF;
494 /*
495 pa_utils.add_message
496 ( p_app_short_name => 'PA',
497 p_msg_name => 'PA_FP_CURR_NOT_VALID',
498 p_token1 => 'PROJECT' ,
499 p_value1 => l_project_name,
500 p_token2 => 'TASK',
501 p_value2 => l_task_name,
502 p_token3 => 'RESOURCE_NAME',
503 p_value3 => l_resource_name,
504 p_token4 => 'RATE_DATE',
505 p_value4 => p_projfunc_cost_rate_date_tab(i),
506 p_token5 => 'TXN_CURRENCY',
507 p_value5 => p_txn_currency_code_tab(i));
508 fnd_msg_pub.count_and_get (p_count => x_msg_count,
509 p_data => x_msg_data);
510 x_msg_count := fnd_msg_pub.count_msg;
511 x_return_status := FND_API.G_RET_STS_ERROR;
512 */
513 x_projfunc_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
514 --Commented for Bug#5395732 ELSE
515 END IF; -- Added for Bug#5395732
516
517 l_stage := 1700;
518 pa_debug.g_err_stage := to_char(l_stage);
519 IF P_PA_DEBUG_MODE = 'Y' THEN
520 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
521 END IF;
522 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
523 --hr_utility.trace(to_char(l_stage));
524 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
525 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
526 ( p_txn_currency_code_tab(i)
527 ,p_projfunc_currency_code_tab(i)
528 ,p_projfunc_cost_rate_date_tab(i)
529 ,p_projfunc_cost_rate_type_tab(i)
530 ,-1);
531 ELSE
532 l_numerator := gl_currency_api.get_rate_numerator_sql
533 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
534 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
535 ,X_CONVERSION_DATE => p_projfunc_cost_rate_date_tab(i)
536 ,X_CONVERSION_TYPE => p_projfunc_cost_rate_type_tab(i));
537
538 l_stage := 1800;
539 END IF;
540 --hr_utility.trace(to_char(l_stage));
541 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
542 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
543 ( p_txn_currency_code_tab(i)
544 ,p_projfunc_currency_code_tab(i)
545 ,p_projfunc_cost_rate_date_tab(i)
546 ,p_projfunc_cost_rate_type_tab(i)
547 ,-1);
548 ELSE
549 l_denominator := gl_currency_api.get_rate_denominator_sql
550 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
551 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
552 ,X_CONVERSION_DATE => p_projfunc_cost_rate_date_tab(i)
553 ,X_CONVERSION_TYPE => p_projfunc_cost_rate_type_tab(i));
554 END IF;
555
556 IF l_numerator > 0 AND l_denominator > 0 THEN
557 l_stage := 1900;
558 --hr_utility.trace(to_char(l_stage));
559 p_projfunc_cost_rate_tab(i) := round(l_numerator/l_denominator,20);
560 l_stage := 1950;
561 --hr_utility.trace(to_char(l_stage));
562 x_projfunc_raw_cost_tab(i) := p_txn_raw_cost_tab(i) *
563 p_projfunc_cost_rate_tab(i);
564 /* Rounding Enhancements */
565 x_projfunc_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1
566 (x_projfunc_raw_cost_tab(i),p_projfunc_currency_code_tab(i));
567 END IF;
568
569 IF nvl(p_txn_burdened_cost_tab(i),0) <> 0 THEN
570 l_stage := 2000;
571 --hr_utility.trace(to_char(l_stage));
572 x_projfunc_burdened_cost_tab(i) := (nvl(p_txn_burdened_cost_tab(i),0) * p_projfunc_cost_rate_tab(i));
573 /* Rounding Enhancements */
574 x_projfunc_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
575 (x_projfunc_burdened_cost_tab(i),p_projfunc_currency_code_tab(i));
576 END IF;
577
578 l_stage := 2100;
579 --hr_utility.trace(to_char(l_stage));
580 l_cached_count := nvl(CachedRowTab.count,0) + 1;
581 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
582 CachedRowTab(l_cached_count).to_currency := p_projfunc_currency_code_tab(i);
583 CachedRowTab(l_cached_count).numerator := l_numerator;
584 CachedRowTab(l_cached_count).denominator := l_denominator;
585 CachedRowTab(l_cached_count).rate := p_projfunc_cost_rate_tab(i);
586 CachedRowTab(l_cached_count).rate_date := p_projfunc_cost_rate_date_tab(i);
587 CachedRowTab(l_cached_count).rate_type := p_projfunc_cost_rate_type_tab(i);
588 CachedRowTab(l_cached_count).line_type := 'COST';
589 --Commented for Bug#5395732 END IF; -- l_converted_amount values
590 END IF; -- Added for Bug#5395732
591 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
592
593 ELSE -- Raw Cost IS NULL or 0
594 l_stage := 2200;
595 --hr_utility.trace(to_char(l_stage));
596 pa_debug.g_err_stage := to_char(l_stage);
597 IF P_PA_DEBUG_MODE = 'Y' THEN
598 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
599 END IF;
600
601 pa_debug.g_err_stage := 'pfc cost rate date' || p_projfunc_cost_rate_date_tab(i);
602 IF P_PA_DEBUG_MODE = 'Y' THEN
603 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
604 END IF;
605
606 pa_debug.g_err_stage := 'pfc cost rate type' || p_projfunc_cost_rate_type_tab(i);
607 IF P_PA_DEBUG_MODE = 'Y' THEN
608 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
609 END IF;
610
611 l_converted_amount := gl_currency_api.convert_amount_sql
612 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
613 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
614 ,X_CONVERSION_DATE => p_projfunc_cost_rate_date_tab(i)
615 ,X_CONVERSION_TYPE => p_projfunc_cost_rate_type_tab(i)
616 ,X_AMOUNT => p_txn_burdened_cost_tab(i));
617 IF l_converted_amount = -1 THEN
618 /* Added the If block for Bug#5395732 */
619 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION')) THEN
620 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
621 ( x_from_currency => p_txn_currency_code_tab(i)
622 ,x_to_currency => p_projfunc_currency_code_tab(i)
623 ,x_conversion_date => p_projfunc_cost_rate_date_tab(i)
624 ,x_conversion_type => p_projfunc_cost_rate_type_tab(i)
625 ,x_user_rate => 1
626 ,x_amount => p_txn_burdened_cost_tab(i)
627 ,x_max_roll_days => -1) ;
628 l_call_closest_flag := 'T';
629 END IF;
630 IF l_converted_amount = -1 THEN
631 l_stage := 2300;
632 --hr_utility.trace(to_char(l_stage));
633 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given ProjFunc currency attributes. Please change the Currency attributes';
634 IF P_PA_DEBUG_MODE = 'Y' THEN
635 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
636 END IF;
637 /*
638 pa_utils.add_message
639 ( p_app_short_name => 'PA',
640 p_msg_name => 'PA_FP_NO_PF_EXCH_RATE_EXISTS',
641 p_token1 => 'PROJECT' ,
642 p_value1 => l_project_name,
643 p_token2 => 'TASK',
644 p_value2 => l_task_name,
645 p_token3 => 'RESOURCE_NAME',
646 p_value3 => l_resource_name,
647 p_token4 => 'RATE_DATE',
648 p_value4 => p_projfunc_cost_rate_date_tab(i),
649 p_token5 => 'TXN_CURRENCY',
650 p_value5 => p_txn_currency_code_tab(i));
651 fnd_msg_pub.count_and_get (p_count => x_msg_count,
652 p_data => x_msg_data);
653 x_msg_count := fnd_msg_pub.count_msg;
654 x_return_status := FND_API.G_RET_STS_ERROR;
655 */
656 x_projfunc_rejection_tab(i) := 'PA_FP_NO_PF_EXCH_RATE_EXISTS';
657 END IF;
658
659 ELSIF l_converted_amount = -2 THEN
660 l_stage := 2400;
661 --hr_utility.trace(to_char(l_stage));
662 pa_debug.g_err_stage := to_char(l_stage)||'The Currency you have entered is not valid. Please re-enter the Currency';
663 IF P_PA_DEBUG_MODE = 'Y' THEN
664 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
665 END IF;
666 /*
667 pa_utils.add_message
668 ( p_app_short_name => 'PA',
669 p_msg_name => 'PA_FP_CURR_NOT_VALID',
670 p_token1 => 'PROJECT' ,
671 p_value1 => l_project_name,
672 p_token2 => 'TASK',
673 p_value2 => l_task_name,
674 p_token3 => 'RESOURCE_NAME',
675 p_value3 => l_resource_name,
676 p_token4 => 'RATE_DATE',
677 p_value4 => p_projfunc_cost_rate_date_tab(i),
678 p_token5 => 'TXN_CURRENCY',
679 p_value5 => p_txn_currency_code_tab(i));
680 fnd_msg_pub.count_and_get (p_count => x_msg_count,
681 p_data => x_msg_data);
682 x_msg_count := fnd_msg_pub.count_msg;
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 */
685 x_projfunc_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
686
687 --Commented for Bug#5395732 ELSE
688 END IF; -- Added for Bug#5395732
689
690 l_stage := 2600;
691 --hr_utility.trace(to_char(l_stage));
692 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
693 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
694 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
695 ( p_txn_currency_code_tab(i)
696 ,p_projfunc_currency_code_tab(i)
697 ,p_projfunc_cost_rate_date_tab(i)
698 ,p_projfunc_cost_rate_type_tab(i)
699 ,-1);
700 ELSE
701 l_numerator := gl_currency_api.get_rate_numerator_sql
702 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
703 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
704 ,X_CONVERSION_DATE => p_projfunc_cost_rate_date_tab(i)
705 ,X_CONVERSION_TYPE => p_projfunc_cost_rate_type_tab(i));
706 END IF;
707 l_stage := 2700;
708 --hr_utility.trace(to_char(l_stage));
709 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
710 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
711 ( p_txn_currency_code_tab(i)
712 ,p_projfunc_currency_code_tab(i)
713 ,p_projfunc_cost_rate_date_tab(i)
714 ,p_projfunc_cost_rate_type_tab(i)
715 ,-1);
716 ELSE
717 l_denominator := gl_currency_api.get_rate_denominator_sql
718 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
719 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
720 ,X_CONVERSION_DATE => p_projfunc_cost_rate_date_tab(i)
721 ,X_CONVERSION_TYPE => p_projfunc_cost_rate_type_tab(i));
722 END IF;
723
724 IF l_numerator > 0 AND l_denominator > 0 THEN
725 l_stage := 2800;
726 --hr_utility.trace(to_char(l_stage));
727 p_projfunc_cost_rate_tab(i) := round(l_numerator/l_denominator,20);
728 l_stage := 2850;
729 --hr_utility.trace(to_char(l_stage));
730 x_projfunc_burdened_cost_tab(i) := p_txn_burdened_cost_tab(i) *
731 p_projfunc_cost_rate_tab(i);
732 /* Rounding Enhancements */
733 x_projfunc_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
734 (x_projfunc_burdened_cost_tab(i),p_projfunc_currency_code_tab(i));
735 END IF;
736
737 l_stage := 2900;
738 --hr_utility.trace(to_char(l_stage));
739 l_cached_count := nvl(CachedRowTab.count,0) + 1;
740 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
741 CachedRowTab(l_cached_count).to_currency := p_projfunc_currency_code_tab(i);
742 CachedRowTab(l_cached_count).numerator := l_numerator;
743 CachedRowTab(l_cached_count).denominator := l_denominator;
744 CachedRowTab(l_cached_count).rate := p_projfunc_cost_rate_tab(i);
745 CachedRowTab(l_cached_count).rate_date := p_projfunc_cost_rate_date_tab(i);
746 CachedRowTab(l_cached_count).rate_type := p_projfunc_cost_rate_type_tab(i);
747 CachedRowTab(l_cached_count).line_type := 'COST';
748 --Commented for Bug#5395732 END IF; -- l_converted_amount values
749 END IF; -- Added for Bug#5395732
750 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
751 END IF; -- Raw cost is NULL or 0
752 END IF; -- Rate not found in Cache
753 END IF; -- rate_type <> 'User'
754 END IF; -- txn_raw or Burdened Cost <> 0
755
756
757 l_stage := 3000;
758 --hr_utility.trace(to_char(l_stage));
759 -- Convert TxnRevenue to ProjectFunctionalRevenue
760 IF NVL(p_txn_revenue_tab(i),0) <> 0 THEN
761 l_stage := 3100;
762 --hr_utility.trace(to_char(l_stage));
763 IF p_projfunc_rev_rate_type_tab(i) = 'User' THEN
764 l_stage := 3200;
765 --hr_utility.trace(to_char(l_stage));
766 l_allow_user_rate_type := pa_multi_currency.is_user_rate_type_allowed
767 ( p_txn_currency_code_tab(i)
768 ,p_projfunc_currency_code_tab(i)
769 ,p_projfunc_rev_rate_date_tab(i));
770 IF l_allow_user_rate_type = 'Y' THEN
771 IF p_projfunc_rev_rate_tab(i) IS NOT NULL THEN
772 x_projfunc_revenue_tab(i) := p_txn_revenue_tab(i) *
773 NVL(p_projfunc_rev_rate_tab(i),1);
774 /* Rounding Enhancements */
775 x_projfunc_revenue_tab(i) := pa_currency.round_trans_currency_amt1
776 (x_projfunc_revenue_tab(i),p_projfunc_currency_code_tab(i));
777 ELSE
778 pa_debug.g_err_stage := to_char(l_stage)||': ProjFunc Revenue Rate Not Defined';
779 IF P_PA_DEBUG_MODE = 'Y' THEN
780 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
781 END IF;
782 /*
783 pa_utils.add_message
784 ( p_app_short_name => 'PA',
785 p_msg_name => 'PA_FP_PF_REV_RATE_NOT_DEFINED',
786 p_token1 => 'PROJECT' ,
787 p_value1 => l_project_name,
788 p_token2 => 'TASK',
789 p_value2 => l_task_name,
790 p_token3 => 'RESOURCE_NAME',
791 p_value3 => l_resource_name,
792 p_token4 => 'START_DATE',
793 p_value4 => p_start_date_tab(i));
794 fnd_msg_pub.count_and_get (p_count => x_msg_count,
795 p_data => x_msg_data);
796 x_msg_count := fnd_msg_pub.count_msg;
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 */
799 x_projfunc_rejection_tab(i) := 'PA_FP_PF_REV_RATE_NOT_DEFINED';
800 END IF;
801 ELSE
802 l_stage := 3210;
803 --hr_utility.trace(to_char(l_stage));
804 pa_debug.g_err_stage := to_char(l_stage)||': Revenue Rate type of User not allowed in ProjFunc Currency';
805 IF P_PA_DEBUG_MODE = 'Y' THEN
806 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
807 END IF;
808 /*
809 pa_utils.add_message
810 ( p_app_short_name => 'PA',
811 p_msg_name => 'PA_FP_PFR_USR_RATE_NOT_ALLOWED',
812 p_token1 => 'PROJECT' ,
813 p_value1 => l_project_name,
814 p_token2 => 'TASK',
815 p_value2 => l_task_name,
816 p_token3 => 'RESOURCE_NAME',
817 p_value3 => l_resource_name,
818 p_token4 => 'START_DATE',
819 p_value4 => p_start_date_tab(i),
820 p_token5 => 'TXN_CURRENCY',
821 p_value5 => p_txn_currency_code_tab(i));
822 fnd_msg_pub.count_and_get (p_count => x_msg_count,
823 p_data => x_msg_data);
824 x_msg_count := fnd_msg_pub.count_msg;
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 */
827 x_projfunc_rejection_tab(i) := 'PA_FP_PFR_USR_RATE_NOT_ALLOWED';
828 END IF;
829 ELSE
830 l_stage := 3300;
831 --hr_utility.trace(to_char(l_stage));
832 l_done_flag := 'N';
833 IF nvl(CachedRowTab.COUNT,0) <> 0 THEN
834 l_stage := 3400;
835 --hr_utility.trace(to_char(l_stage));
836 FOR j in CachedRowTab.First..CachedRowTab.Last LOOP
837 IF CachedRowTab(j).from_currency = p_txn_currency_code_tab(i) AND
838 CachedRowTab(j).to_currency = p_projfunc_currency_code_tab(i) AND
839 CachedRowTab(j).rate_date = p_projfunc_rev_rate_date_tab(i) AND
840 CachedRowTab(j).rate_type = p_projfunc_rev_rate_type_tab(i) AND
841 CachedRowTab(j).line_type = 'REVENUE' THEN
842 l_stage := 3500;
843 --hr_utility.trace(to_char(l_stage));
844 p_projfunc_rev_rate_tab(i) := CachedRowTab(j).rate;
845 x_projfunc_revenue_tab(i) := nvl(p_txn_revenue_tab(i),0) *
846 (round(CachedRowTab(j).numerator/
847 CachedRowTab(j).denominator,20));
848 /* Rounding Enhancements */
849 x_projfunc_revenue_tab(i) := pa_currency.round_trans_currency_amt1
850 (x_projfunc_revenue_tab(i),p_projfunc_currency_code_tab(i));
851 l_done_flag := 'Y';
852 EXIT;
853 END IF; -- RevenueRateFound
854 END LOOP; -- CachedRevenueRates
855 END IF; -- CachedRowTab.COUNT > 0
856 IF l_done_flag = 'N' THEN
857 l_stage := 3600;
858 --hr_utility.trace(to_char(l_stage));
859 l_converted_amount := gl_currency_api.convert_amount_sql
860 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
861 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
862 ,X_CONVERSION_DATE => p_projfunc_rev_rate_date_tab(i)
863 ,X_CONVERSION_TYPE => p_projfunc_rev_rate_type_tab(i)
864 ,X_AMOUNT => p_txn_revenue_tab(i));
865
866 IF l_converted_amount = -1 THEN
867 /* Added the If block for Bug#5395732 */
868 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION')) THEN
869 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
870 ( x_from_currency => p_txn_currency_code_tab(i)
871 ,x_to_currency => p_projfunc_currency_code_tab(i)
872 ,x_conversion_date => p_projfunc_rev_rate_date_tab(i)
873 ,x_conversion_type => p_projfunc_rev_rate_type_tab(i)
874 ,x_user_rate => 1
875 ,x_amount => p_txn_revenue_tab(i)
876 ,x_max_roll_days => -1) ;
877 l_call_closest_flag := 'T';
878 END IF;
879 IF l_converted_amount = -1 THEN
880 l_stage := 3700;
881 --hr_utility.trace(to_char(l_stage));
882 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given Projfunc currency attributes. Please change the Currency attributes';
883 IF P_PA_DEBUG_MODE = 'Y' THEN
884 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
885 END IF;
886 /*
887 pa_utils.add_message
888 ( p_app_short_name => 'PA',
889 p_msg_name => 'PA_FP_NO_PF_EXCH_RATE_EXISTS',
890 p_token1 => 'PROJECT' ,
891 p_value1 => l_project_name,
892 p_token2 => 'TASK',
893 p_value2 => l_task_name,
894 p_token3 => 'RESOURCE_NAME',
895 p_value3 => l_resource_name,
896 p_token4 => 'RATE_DATE',
897 p_value4 => p_projfunc_rev_rate_date_tab(i),
898 p_token5 => 'TXN_CURRENCY',
899 p_value5 => p_txn_currency_code_tab(i));
900 fnd_msg_pub.count_and_get (p_count => x_msg_count,
901 p_data => x_msg_data);
902 x_msg_count := fnd_msg_pub.count_msg;
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 */
905 x_projfunc_rejection_tab(i) := 'PA_FP_NO_PF_EXCH_RATE_EXISTS';
906 END IF;
907 ELSIF l_converted_amount = -2 THEN
908 l_stage := 3800;
909 --hr_utility.trace(to_char(l_stage));
910 pa_debug.g_err_stage := to_char(l_stage)||'The Currency you have entered is not valid. Please re-enter the Currency';
911 IF P_PA_DEBUG_MODE = 'Y' THEN
912 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
913 END IF;
914 /*
915 pa_utils.add_message
916 ( p_app_short_name => 'PA',
917 p_msg_name => 'PA_FP_CURR_NOT_VALID',
918 p_token1 => 'PROJECT' ,
919 p_value1 => l_project_name,
920 p_token2 => 'TASK',
921 p_value2 => l_task_name,
922 p_token3 => 'RESOURCE_NAME',
923 p_value3 => l_resource_name,
924 p_token4 => 'RATE_DATE',
925 p_value4 => p_projfunc_rev_rate_date_tab(i),
926 p_token5 => 'TXN_CURRENCY',
927 p_value5 => p_txn_currency_code_tab(i));
928 fnd_msg_pub.count_and_get (p_count => x_msg_count,
929 p_data => x_msg_data);
930 x_msg_count := fnd_msg_pub.count_msg;
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 */
933 x_projfunc_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
934
935 -- Commented for Bug#5395732 ELSE
936 END IF; -- Added for Bug#5395732
937
938 l_stage := 4000;
939 --hr_utility.trace(to_char(l_stage));
940 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
941 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
942 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
943 ( p_txn_currency_code_tab(i)
944 ,p_projfunc_currency_code_tab(i)
945 ,p_projfunc_rev_rate_date_tab(i)
946 ,p_projfunc_rev_rate_type_tab(i)
947 ,-1);
948 ELSE
949
950 l_numerator := gl_currency_api.get_rate_numerator_sql
951 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
952 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
953 ,X_CONVERSION_DATE => p_projfunc_rev_rate_date_tab(i)
954 ,X_CONVERSION_TYPE => p_projfunc_rev_rate_type_tab(i));
955 END IF;
956 l_stage := 4100;
957 --hr_utility.trace(to_char(l_stage));
958 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
959 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
960 ( p_txn_currency_code_tab(i)
961 ,p_projfunc_currency_code_tab(i)
962 ,p_projfunc_rev_rate_date_tab(i)
963 ,p_projfunc_rev_rate_type_tab(i)
964 ,-1);
965 ELSE
966 l_denominator := gl_currency_api.get_rate_denominator_sql
967 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
968 ,X_TO_CURRENCY => p_projfunc_currency_code_tab(i)
969 ,X_CONVERSION_DATE => p_projfunc_rev_rate_date_tab(i)
970 ,X_CONVERSION_TYPE => p_projfunc_rev_rate_type_tab(i));
971 END IF;
972 IF l_numerator > 0 AND l_denominator > 0 THEN
973 l_stage := 4200;
974 --hr_utility.trace(to_char(l_stage));
975 p_projfunc_rev_rate_tab(i) := round(l_numerator/l_denominator,20);
976 l_stage := 4250;
977 --hr_utility.trace(to_char(l_stage));
978 x_projfunc_revenue_tab(i) := p_txn_revenue_tab(i) *
979 p_projfunc_rev_rate_tab(i);
980 /* Rounding Enhancements */
981 x_projfunc_revenue_tab(i) := pa_currency.round_trans_currency_amt1
982 (x_projfunc_revenue_tab(i),p_projfunc_currency_code_tab(i));
983 END IF;
984
985 l_stage := 4300;
986 --hr_utility.trace(to_char(l_stage));
987 l_cached_count := nvl(CachedRowTab.count,0) + 1;
988 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
989 CachedRowTab(l_cached_count).to_currency := p_projfunc_currency_code_tab(i);
990 CachedRowTab(l_cached_count).numerator := l_numerator;
991 CachedRowTab(l_cached_count).denominator := l_denominator;
992 CachedRowTab(l_cached_count).rate := p_projfunc_rev_rate_tab(i);
993 CachedRowTab(l_cached_count).rate_date := p_projfunc_rev_rate_date_tab(i);
994 CachedRowTab(l_cached_count).rate_type := p_projfunc_rev_rate_type_tab(i);
995 CachedRowTab(l_cached_count).line_type := 'REVENUE';
996 -- Commented for Bug#5395732 END IF; -- l_converted_amount values
997 END IF; -- Added for Bug#5395732
998 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
999 END IF; -- RevenueRate not found in Cache
1000 END IF; -- RevenueRateType <> 'User'
1001 END IF; -- TxnRevenue <> 0
1002 END IF; -- TxnCurrencyCode <> ProjFuncCurrencyCode
1003
1004 l_stage := 4400;
1005 --hr_utility.trace(to_char(l_stage));
1006 -- Convert TxnCurrency to ProjectCurrency
1007
1008 IF p_txn_currency_code_tab(i) = p_proj_currency_code_tab(i) THEN
1009 l_stage := 4500;
1010 --hr_utility.trace(to_char(l_stage));
1011 p_proj_cost_rate_tab(i) := NULL;
1012 x_proj_raw_cost_tab(i) := p_txn_raw_cost_tab(i);
1013 x_proj_burdened_cost_tab(i):= p_txn_burdened_cost_tab(i);
1014 p_proj_rev_rate_tab(i) := NULL;
1015 x_proj_revenue_tab(i) := p_txn_revenue_tab(i);
1016 ELSE
1017 l_stage := 4600;
1018 --hr_utility.trace(to_char(l_stage));
1019 -- Convert TxnCost to ProjectCost
1020 IF NVL(p_txn_raw_cost_tab(i),0) <> 0 OR NVL(p_txn_burdened_cost_tab(i),0) <> 0 THEN
1021 l_stage := 4700;
1022 --hr_utility.trace(to_char(l_stage));
1023 IF p_proj_cost_rate_type_tab(i) = 'User' THEN
1024 l_stage := 4800;
1025 --hr_utility.trace(to_char(l_stage));
1026 l_allow_user_rate_type := pa_multi_currency.is_user_rate_type_allowed
1027 ( p_txn_currency_code_tab(i)
1028 ,p_proj_currency_code_tab(i)
1029 ,p_proj_cost_rate_date_tab(i));
1030 IF l_allow_user_rate_type = 'Y' THEN
1031 IF p_proj_cost_rate_tab(i) IS NOT NULL THEN
1032 l_stage := 4900;
1033 --hr_utility.trace(to_char(l_stage));
1034 x_proj_raw_cost_tab(i) := p_txn_raw_cost_tab(i) *
1035 NVL(p_proj_cost_rate_tab(i),1);
1036 x_proj_burdened_cost_tab(i) := p_txn_burdened_cost_tab(i) *
1037 NVL(p_proj_cost_rate_tab(i),1);
1038 /* Rounding Enhancements */
1039 x_proj_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1
1040 (x_proj_raw_cost_tab(i),p_proj_currency_code_tab(i));
1041 x_proj_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
1042 (x_proj_burdened_cost_tab(i),p_proj_currency_code_tab(i));
1043 ELSE
1044 l_stage := 5000;
1045 --hr_utility.trace(to_char(l_stage));
1046 pa_debug.g_err_stage := to_char(l_stage)||': Project Cost Rate Not Defined';
1047 IF P_PA_DEBUG_MODE = 'Y' THEN
1048 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1049 END IF;
1050 /*
1051 pa_utils.add_message
1052 ( p_app_short_name => 'PA',
1053 p_msg_name => 'PA_FP_PJ_COST_RATE_NOT_DEFINED',
1054 p_token1 => 'PROJECT' ,
1055 p_value1 => l_project_name,
1056 p_token2 => 'TASK',
1057 p_value2 => l_task_name,
1058 p_token3 => 'RESOURCE_NAME',
1059 p_value3 => l_resource_name,
1060 p_token4 => 'START_DATE',
1061 p_value4 => p_start_date_tab(i));
1062 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1063 p_data => x_msg_data);
1064 x_msg_count := fnd_msg_pub.count_msg;
1065 x_return_status := FND_API.G_RET_STS_ERROR;
1066 */
1067 x_proj_rejection_tab(i) := 'PA_FP_PJ_COST_RATE_NOT_DEFINED';
1068 END IF;
1069 ELSE
1070 l_stage := 810;
1071 --hr_utility.trace(to_char(l_stage));
1072 pa_debug.g_err_stage := to_char(l_stage)||': Cost Rate type of User not allowed in Project Currency';
1073 IF P_PA_DEBUG_MODE = 'Y' THEN
1074 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1075 END IF;
1076 /*
1077 pa_utils.add_message
1078 ( p_app_short_name => 'PA',
1079 p_msg_name => 'PA_FP_PJC_USR_RATE_NOT_ALLOWED',
1080 p_token1 => 'PROJECT' ,
1081 p_value1 => l_project_name,
1082 p_token2 => 'TASK',
1083 p_value2 => l_task_name,
1084 p_token3 => 'RESOURCE_NAME',
1085 p_value3 => l_resource_name,
1086 p_token4 => 'START_DATE',
1087 p_value4 => p_start_date_tab(i),
1088 p_token5 => 'TXN_CURRENCY',
1089 p_value5 => p_txn_currency_code_tab(i));
1090 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1091 p_data => x_msg_data);
1092 x_msg_count := fnd_msg_pub.count_msg;
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 */
1095 x_proj_rejection_tab(i) := 'PA_FP_PJC_USR_RATE_NOT_ALLOWED';
1096 END IF;
1097 ELSE
1098 l_stage := 5100;
1099 --hr_utility.trace(to_char(l_stage));
1100 l_done_flag := 'N';
1101 IF nvl(CachedRowTab.COUNT,0) <> 0 THEN
1102 l_stage := 5200;
1103 --hr_utility.trace(to_char(l_stage));
1104 FOR j in CachedRowTab.First..CachedRowTab.Last LOOP
1105 IF CachedRowTab(j).from_currency = p_txn_currency_code_tab(i) AND
1106 CachedRowTab(j).to_currency = p_proj_currency_code_tab(i) AND
1107 CachedRowTab(j).rate_date = p_proj_cost_rate_date_tab(i) AND
1108 CachedRowTab(j).rate_type = p_proj_cost_rate_type_tab(i) AND
1109 CachedRowTab(j).line_type = 'COST' THEN
1110 l_stage := 5300;
1111 --hr_utility.trace(to_char(l_stage));
1112 p_proj_cost_rate_tab(i) := CachedRowTab(j).rate;
1113 x_proj_raw_cost_tab(i) := nvl(p_txn_raw_cost_tab(i),0) *
1114 (round(CachedRowTab(j).numerator/
1115 CachedRowTab(j).denominator,20));
1116 x_proj_burdened_cost_tab(i):= nvl(p_txn_burdened_cost_tab(i),0) *
1117 (round(CachedRowTab(j).numerator/
1118 CachedRowTab(j).denominator,20));
1119 /* Rounding Enhancements */
1120 x_proj_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1
1121 (x_proj_raw_cost_tab(i),p_proj_currency_code_tab(i));
1122 x_proj_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
1123 (x_proj_burdened_cost_tab(i),p_proj_currency_code_tab(i));
1124 l_done_flag := 'Y';
1125 EXIT;
1126 END IF; -- Cost Rate found
1127 END LOOP; -- cached cost rates
1128 END IF; -- CachedRowTab.COUNT > 0
1129 IF l_done_flag = 'N' THEN
1130 l_stage := 5400;
1131 --hr_utility.trace(to_char(l_stage));
1132 IF nvl(p_txn_raw_cost_tab(i),0) <> 0 THEN
1133 l_stage := 5500;
1134 --hr_utility.trace(to_char(l_stage));
1135 l_converted_amount := gl_currency_api.convert_amount_sql
1136 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1137 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1138 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1139 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i)
1140 ,X_AMOUNT => p_txn_raw_cost_tab(i));
1141 IF l_converted_amount = -1 THEN
1142 /* Added the If block for Bug#5395732 */
1143 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION')) THEN
1144 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
1145 ( x_from_currency => p_txn_currency_code_tab(i)
1146 ,x_to_currency => p_proj_currency_code_tab(i)
1147 ,x_conversion_date => p_proj_cost_rate_date_tab(i)
1148 ,x_conversion_type => p_proj_cost_rate_type_tab(i)
1149 ,x_user_rate => 1
1150 ,x_amount => p_txn_raw_cost_tab(i)
1151 ,x_max_roll_days => -1) ;
1152 l_call_closest_flag := 'T';
1153 END IF;
1154 IF l_converted_amount = -1 THEN
1155 l_stage := 5600;
1156 --hr_utility.trace(to_char(l_stage));
1157 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given Project currency attributes. Please change the Currency attributes';
1158 IF P_PA_DEBUG_MODE = 'Y' THEN
1159 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1160 END IF;
1161 /*
1162 pa_utils.add_message
1163 ( p_app_short_name => 'PA',
1164 p_msg_name => 'PA_FP_NO_PJ_EXCH_RATE_EXISTS',
1165 p_token1 => 'PROJECT' ,
1166 p_value1 => l_project_name,
1167 p_token2 => 'TASK',
1168 p_value2 => l_task_name,
1169 p_token3 => 'RESOURCE_NAME',
1170 p_value3 => l_resource_name,
1171 p_token4 => 'RATE_DATE',
1172 p_value4 => p_proj_cost_rate_date_tab(i),
1173 p_token5 => 'TXN_CURRENCY',
1174 p_value5 => p_txn_currency_code_tab(i));
1175 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1176 p_data => x_msg_data);
1177 x_msg_count := fnd_msg_pub.count_msg;
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 */
1180 x_proj_rejection_tab(i) := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
1181 END IF;
1182 ELSIF l_converted_amount = -2 THEN
1183 l_stage := 5700;
1184 --hr_utility.trace(to_char(l_stage));
1185 /*
1186 pa_utils.add_message
1187 (p_app_short_name => 'PA',
1188 p_msg_name => 'PA_FP_CURR_NOT_VALID',
1189 p_token1 => 'PROJECT' ,
1190 p_value1 => l_project_name,
1191 p_token2 => 'TASK',
1192 p_value2 => l_task_name,
1193 p_token3 => 'RESOURCE_NAME',
1194 p_value3 => l_resource_name,
1195 p_token4 => 'RATE_DATE',
1196 p_value4 => p_proj_cost_rate_date_tab(i),
1197 p_token5 => 'TXN_CURRENCY',
1198 p_value5 => p_txn_currency_code_tab(i));
1199 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1200 p_data => x_msg_data);
1201 x_msg_count := fnd_msg_pub.count_msg;
1202 x_return_status := FND_API.G_RET_STS_ERROR;
1203 */
1204 x_proj_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
1205 -- Commented for Bug#5395732 ELSE
1206 END IF; -- Added for Bug#5395732
1207
1208
1209 l_stage := 5900;
1210 --hr_utility.trace(to_char(l_stage));
1211 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
1212 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1213 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
1214 ( p_txn_currency_code_tab(i)
1215 ,p_proj_currency_code_tab(i)
1216 ,p_proj_cost_rate_date_tab(i)
1217 ,p_proj_cost_rate_type_tab(i)
1218 ,-1);
1219 ELSE
1220 l_numerator := gl_currency_api.get_rate_numerator_sql
1221 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1222 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1223 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1224 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i));
1225 END IF;
1226 l_stage := 6000;
1227 --hr_utility.trace(to_char(l_stage));
1228 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1229 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
1230 ( p_txn_currency_code_tab(i)
1231 ,p_proj_currency_code_tab(i)
1232 ,p_proj_cost_rate_date_tab(i)
1233 ,p_proj_cost_rate_type_tab(i)
1234 ,-1);
1235 ELSE
1236 l_denominator := gl_currency_api.get_rate_denominator_sql
1237 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1238 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1239 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1240 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i));
1241 END IF;
1242 IF l_numerator > 0 AND l_denominator > 0 THEN
1243 l_stage := 6100;
1244 --hr_utility.trace(to_char(l_stage));
1245 p_proj_cost_rate_tab(i) := round(l_numerator/l_denominator,20);
1246 l_stage := 6150;
1247 --hr_utility.trace(to_char(l_stage));
1248 x_proj_raw_cost_tab(i) := p_txn_raw_cost_tab(i) *
1249 p_proj_cost_rate_tab(i);
1250
1251 /* Rounding Enhancements */
1252 x_proj_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1
1253 (x_proj_raw_cost_tab(i),p_proj_currency_code_tab(i));
1254
1255 END IF;
1256
1257 IF nvl(p_txn_burdened_cost_tab(i),0) <> 0 THEN
1258 l_stage := 6200;
1259 --hr_utility.trace(to_char(l_stage));
1260 x_proj_burdened_cost_tab(i) := (nvl(p_txn_burdened_cost_tab(i),0) *
1261 p_proj_cost_rate_tab(i));
1262 /* Rounding Enhancements */
1263 x_proj_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
1264 (x_proj_burdened_cost_tab(i),p_proj_currency_code_tab(i));
1265 END IF;
1266
1267 l_stage := 6300;
1268 --hr_utility.trace(to_char(l_stage));
1269 l_cached_count := nvl(CachedRowTab.count,0) + 1;
1270 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
1271 CachedRowTab(l_cached_count).to_currency := p_proj_currency_code_tab(i);
1272 CachedRowTab(l_cached_count).numerator := l_numerator;
1273 CachedRowTab(l_cached_count).denominator := l_denominator;
1274 CachedRowTab(l_cached_count).rate := p_proj_cost_rate_tab(i);
1275 CachedRowTab(l_cached_count).rate_date := p_proj_cost_rate_date_tab(i);
1276 CachedRowTab(l_cached_count).rate_type := p_proj_cost_rate_type_tab(i);
1277 CachedRowTab(l_cached_count).line_type := 'COST';
1278 -- Commented for Bug#5395732 END IF; -- l_converted_amount values
1279 END IF; -- Added for Bug#5395732
1280 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
1281 ELSE -- Raw Cost IS NULL or 0
1282 l_stage := 6400;
1283 --hr_utility.trace(to_char(l_stage));
1284 l_converted_amount := gl_currency_api.convert_amount_sql
1285 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1286 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1287 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1288 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i)
1289 ,X_AMOUNT => p_txn_burdened_cost_tab(i));
1290 IF l_converted_amount = -1 THEN
1291 /* Added the If block for Bug#5395732 */
1292 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION')) THEN
1293 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
1294 ( x_from_currency => p_txn_currency_code_tab(i)
1295 ,x_to_currency => p_proj_currency_code_tab(i)
1296 ,x_conversion_date => p_proj_cost_rate_date_tab(i)
1297 ,x_conversion_type => p_proj_cost_rate_type_tab(i)
1298 ,x_user_rate => 1
1299 ,x_amount => p_txn_burdened_cost_tab(i)
1300 ,x_max_roll_days => -1) ;
1301 l_call_closest_flag := 'T';
1302 END IF;
1303 IF l_converted_amount = -1 THEN
1304
1305 l_stage := 6500;
1306 --hr_utility.trace(to_char(l_stage));
1307 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given Project currency attributes. Please change the Currency attributes';
1308 IF P_PA_DEBUG_MODE = 'Y' THEN
1309 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1310 END IF;
1311 /*
1312 pa_utils.add_message
1313 (p_app_short_name => 'PA',
1314 p_msg_name => 'PA_FP_NO_PJ_EXCH_RATE_EXISTS',
1315 p_token1 => 'PROJECT' ,
1316 p_value1 => l_project_name,
1317 p_token2 => 'TASK',
1318 p_value2 => l_task_name,
1319 p_token3 => 'RESOURCE_NAME',
1320 p_value3 => l_resource_name,
1321 p_token4 => 'RATE_DATE',
1322 p_value4 => p_proj_cost_rate_date_tab(i),
1323 p_token5 => 'TXN_CURRENCY',
1324 p_value5 => p_txn_currency_code_tab(i));
1325 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1326 p_data => x_msg_data);
1327 x_msg_count := fnd_msg_pub.count_msg;
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 */
1330 x_proj_rejection_tab(i) := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
1331 END IF;
1332 ELSIF l_converted_amount = -2 THEN
1333 l_stage := 6600;
1334 --hr_utility.trace(to_char(l_stage));
1335 pa_debug.g_err_stage := to_char(l_stage)||'The Currency you have entered is not valid. Please re-enter the Currency';
1336 IF P_PA_DEBUG_MODE = 'Y' THEN
1337 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1338 END IF;
1339 /*
1340 pa_utils.add_message
1341 ( p_app_short_name => 'PA',
1342 p_msg_name => 'PA_FP_CURR_NOT_VALID',
1343 p_token1 => 'PROJECT' ,
1344 p_value1 => l_project_name,
1345 p_token2 => 'TASK',
1346 p_value2 => l_task_name,
1347 p_token3 => 'RESOURCE_NAME',
1348 p_value3 => l_resource_name,
1349 p_token4 => 'RATE_DATE',
1350 p_value4 => p_proj_cost_rate_date_tab(i),
1351 p_token5 => 'TXN_CURRENCY',
1352 p_value5 => p_txn_currency_code_tab(i));
1353 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1354 p_data => x_msg_data);
1355 x_msg_count := fnd_msg_pub.count_msg;
1356 x_return_status := FND_API.G_RET_STS_ERROR;
1357 */
1358 x_proj_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
1359
1360 -- Commented for Bug#5395732 ELSE
1361 END IF; -- Added for Bug#5395732
1362
1363 l_stage := 6800;
1364 --hr_utility.trace(to_char(l_stage));
1365 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
1366 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1367 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
1368 ( p_txn_currency_code_tab(i)
1369 ,p_proj_currency_code_tab(i)
1370 ,p_proj_cost_rate_date_tab(i)
1371 ,p_proj_cost_rate_type_tab(i)
1372 ,-1);
1373 ELSE
1374 l_numerator := gl_currency_api.get_rate_numerator_sql
1375 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1376 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1377 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1378 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i));
1379 END IF;
1380 l_stage := 6900;
1381 --hr_utility.trace(to_char(l_stage));
1382 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1383 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
1384 ( p_txn_currency_code_tab(i)
1385 ,p_proj_currency_code_tab(i)
1386 ,p_proj_cost_rate_date_tab(i)
1387 ,p_proj_cost_rate_type_tab(i)
1388 ,-1);
1389 ELSE
1390
1391 l_denominator := gl_currency_api.get_rate_denominator_sql
1392 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1393 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1394 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1395 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i));
1396 END IF;
1397 IF l_numerator > 0 AND l_denominator > 0 THEN
1398 l_stage := 7000;
1399 --hr_utility.trace(to_char(l_stage));
1400 p_proj_cost_rate_tab(i) := round(l_numerator/l_denominator,20);
1401 l_stage := 7050;
1402 --hr_utility.trace(to_char(l_stage));
1403 x_proj_burdened_cost_tab(i) := p_txn_burdened_cost_tab(i) *
1404 p_proj_cost_rate_tab(i);
1405
1406 /* Rounding Enhancements */
1407 x_proj_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
1408 (x_proj_burdened_cost_tab(i),p_proj_currency_code_tab(i));
1409 END IF;
1410
1411 l_stage := 7100;
1412 --hr_utility.trace(to_char(l_stage));
1413 l_cached_count := nvl(CachedRowTab.count,0) + 1;
1414 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
1415 CachedRowTab(l_cached_count).to_currency := p_proj_currency_code_tab(i);
1416 CachedRowTab(l_cached_count).numerator := l_numerator;
1417 CachedRowTab(l_cached_count).denominator := l_denominator;
1418 CachedRowTab(l_cached_count).rate := p_proj_cost_rate_tab(i);
1419 CachedRowTab(l_cached_count).rate_date := p_proj_cost_rate_date_tab(i);
1420 CachedRowTab(l_cached_count).rate_type := p_proj_cost_rate_type_tab(i);
1421 CachedRowTab(l_cached_count).line_type := 'COST';
1422 -- Commented for Bug#5395732 END IF; -- l_converted_amount values
1423 END IF; -- Added for Bug#5395732
1424 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
1425 END IF; -- Raw cost is NULL or 0
1426 END IF; -- Rate not found in Cache
1427 END IF; -- rate_type <> 'User'
1428 END IF; -- txn_raw or Burdened Cost <> 0
1429
1430
1431 l_stage := 7200;
1432 --hr_utility.trace(to_char(l_stage));
1433 -- Convert TxnRevenue to ProjectRevenue
1434 IF NVL(p_txn_revenue_tab(i),0) <> 0 THEN
1435 l_stage := 7300;
1436 --hr_utility.trace(to_char(l_stage));
1437 IF p_proj_rev_rate_type_tab(i) = 'User' THEN
1438 l_stage := 7400;
1439 --hr_utility.trace(to_char(l_stage));
1440 l_allow_user_rate_type := pa_multi_currency.is_user_rate_type_allowed
1441 ( p_txn_currency_code_tab(i)
1442 ,p_proj_currency_code_tab(i)
1443 ,p_proj_rev_rate_date_tab(i));
1444 IF l_allow_user_rate_type = 'Y' THEN
1445 IF p_proj_rev_rate_tab(i) IS NOT NULL THEN
1446 x_proj_revenue_tab(i) := p_txn_revenue_tab(i) *
1447 NVL(p_proj_rev_rate_tab(i),1);
1448 /* Rounding Enhancements */
1449 x_proj_revenue_tab(i) := pa_currency.round_trans_currency_amt1
1450 (x_proj_revenue_tab(i),p_proj_currency_code_tab(i));
1451 ELSE
1452 pa_debug.g_err_stage := to_char(l_stage)||': Project Revenue Rate Not Defined';
1453 IF P_PA_DEBUG_MODE = 'Y' THEN
1454 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1455 END IF;
1456 /*
1457 pa_utils.add_message
1458 ( p_app_short_name => 'PA',
1459 p_msg_name => 'PA_FP_PJ_REV_RATE_NOT_DEFINED',
1460 p_token1 => 'PROJECT' ,
1461 p_value1 => l_project_name,
1462 p_token2 => 'TASK',
1463 p_value2 => l_task_name,
1464 p_token3 => 'RESOURCE_NAME',
1465 p_value3 => l_resource_name,
1466 p_token4 => 'START_DATE',
1467 p_value4 => p_start_date_tab(i));
1468 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1469 p_data => x_msg_data);
1470 x_msg_count := fnd_msg_pub.count_msg;
1471 x_return_status := FND_API.G_RET_STS_ERROR;
1472 */
1473 x_proj_rejection_tab(i) := 'PA_FP_PJ_REV_RATE_NOT_DEFINED';
1474 END IF;
1475 ELSE
1476 l_stage := 810;
1477 --hr_utility.trace(to_char(l_stage));
1478 pa_debug.g_err_stage := to_char(l_stage)||': Revenue Rate type of User not allowed in Project Currency';
1479 IF P_PA_DEBUG_MODE = 'Y' THEN
1480 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1481 END IF;
1482 /*
1483 pa_utils.add_message
1484 ( p_app_short_name => 'PA',
1485 p_msg_name => 'PA_FP_PJC_USR_RATE_NOT_ALLOWED',
1486 p_token1 => 'PROJECT' ,
1487 p_value1 => l_project_name,
1488 p_token2 => 'TASK',
1489 p_value2 => l_task_name,
1490 p_token3 => 'RESOURCE_NAME',
1491 p_value3 => l_resource_name,
1492 p_token4 => 'START_DATE',
1493 p_value4 => p_start_date_tab(i),
1494 p_token5 => 'TXN_CURRENCY',
1495 p_value5 => p_txn_currency_code_tab(i));
1496 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1497 p_data => x_msg_data);
1498 x_msg_count := fnd_msg_pub.count_msg;
1499 x_return_status := FND_API.G_RET_STS_ERROR;
1500 */
1501 x_proj_rejection_tab(i) := 'PA_FP_PJC_USR_RATE_NOT_ALLOWED';
1502 END IF;
1503 ELSE
1504 l_stage := 7500;
1505 --hr_utility.trace(to_char(l_stage));
1506 l_done_flag := 'N';
1507
1508 IF nvl(CachedRowTab.COUNT,0) <> 0 THEN
1509 l_stage := 7600;
1510 --hr_utility.trace(to_char(l_stage));
1511 FOR j in CachedRowTab.First..CachedRowTab.Last LOOP
1512 IF CachedRowTab(j).from_currency = p_txn_currency_code_tab(i) AND
1513 CachedRowTab(j).to_currency = p_proj_currency_code_tab(i) AND
1514 CachedRowTab(j).rate_date = p_proj_rev_rate_date_tab(i) AND
1515 CachedRowTab(j).rate_type = p_proj_rev_rate_type_tab(i) AND
1516 CachedRowTab(j).line_type = 'REVENUE' THEN
1517 l_stage := 7700;
1518 --hr_utility.trace(to_char(l_stage));
1519 p_proj_rev_rate_tab(i) := CachedRowTab(j).rate;
1520 x_proj_revenue_tab(i) := nvl(p_txn_revenue_tab(i),0) *
1521 (round(CachedRowTab(j).numerator/
1522 CachedRowTab(j).denominator,20));
1523 /* Rounding Enhancements */
1524 x_proj_revenue_tab(i) := pa_currency.round_trans_currency_amt1
1525 (x_proj_revenue_tab(i),p_proj_currency_code_tab(i));
1526 l_done_flag := 'Y';
1527 EXIT;
1528 END IF; -- RevenueRateFound
1529 END LOOP; -- CachedRevenueRates
1530 END IF; -- CachedRowTab.COUNT > 0
1531 IF l_done_flag = 'N' THEN
1532 l_stage := 7700;
1533 --hr_utility.trace(to_char(l_stage));
1534 l_converted_amount := gl_currency_api.convert_amount_sql
1535 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1536 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1537 ,X_CONVERSION_DATE => p_proj_rev_rate_date_tab(i)
1538 ,X_CONVERSION_TYPE => p_proj_rev_rate_type_tab(i)
1539 ,X_AMOUNT => p_txn_revenue_tab(i));
1540
1541 IF l_converted_amount = -1 THEN
1542 /* Added the If block for Bug#5395732 */
1543 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION')) THEN
1544 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
1545 ( x_from_currency => p_txn_currency_code_tab(i)
1546 ,x_to_currency => p_proj_currency_code_tab(i)
1547 ,x_conversion_date => p_proj_rev_rate_date_tab(i)
1548 ,x_conversion_type => p_proj_rev_rate_type_tab(i)
1549 ,x_user_rate => 1
1550 ,x_amount => p_txn_revenue_tab(i)
1551 ,x_max_roll_days => -1) ;
1552 l_call_closest_flag := 'T';
1553 END IF;
1554 IF l_converted_amount = -1 THEN
1555 l_stage := 7800;
1556 --hr_utility.trace(to_char(l_stage));
1557 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given Project currency attributes. Please change the Currency attributes';
1558 IF P_PA_DEBUG_MODE = 'Y' THEN
1559 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1560 END IF;
1561 /*
1562 pa_utils.add_message
1563 ( p_app_short_name => 'PA',
1564 p_msg_name => 'PA_FP_NO_PJ_EXCH_RATE_EXISTS',
1565 p_token1 => 'PROJECT' ,
1566 p_value1 => l_project_name,
1567 p_token2 => 'TASK',
1568 p_value2 => l_task_name,
1569 p_token3 => 'RESOURCE_NAME',
1570 p_value3 => l_resource_name,
1571 p_token4 => 'RATE_DATE',
1572 p_value4 => p_proj_rev_rate_date_tab(i),
1573 p_token5 => 'TXN_CURRENCY',
1574 p_value5 => p_txn_currency_code_tab(i));
1575 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1576 p_data => x_msg_data);
1577 x_msg_count := fnd_msg_pub.count_msg;
1578 x_return_status := FND_API.G_RET_STS_ERROR;
1579 */
1580 x_proj_rejection_tab(i) := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
1581 END IF;
1582 ELSIF l_converted_amount = -2 THEN
1583 l_stage := 7900;
1584 --hr_utility.trace(to_char(l_stage));
1585 pa_debug.g_err_stage := to_char(l_stage)||'The Currency you have entered is not valid. Please re-enter the Currency';
1586 IF P_PA_DEBUG_MODE = 'Y' THEN
1587 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1588 END IF;
1589 /*
1590 pa_utils.add_message
1591 ( p_app_short_name => 'PA',
1592 p_msg_name => 'PA_FP_CURR_NOT_VALID',
1593 p_token1 => 'PROJECT' ,
1594 p_value1 => l_project_name,
1595 p_token2 => 'TASK',
1596 p_value2 => l_task_name,
1597 p_token3 => 'RESOURCE_NAME',
1598 p_value3 => l_resource_name,
1599 p_token4 => 'RATE_DATE',
1600 p_value4 => p_proj_rev_rate_date_tab(i),
1601 p_token5 => 'TXN_CURRENCY',
1602 p_value5 => p_txn_currency_code_tab(i));
1603 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1604 p_data => x_msg_data);
1605 x_msg_count := fnd_msg_pub.count_msg;
1606 x_return_status := FND_API.G_RET_STS_ERROR;
1607 */
1608 x_proj_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
1609 -- Commented for Bug#5395732 ELSE
1610 END IF; -- Added for Bug#5395732
1611
1612 l_stage := 8100;
1613 --hr_utility.trace(to_char(l_stage));
1614 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
1615 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1616 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
1617 ( p_txn_currency_code_tab(i)
1618 ,p_proj_currency_code_tab(i)
1619 ,p_proj_rev_rate_date_tab(i)
1620 ,p_proj_rev_rate_type_tab(i)
1621 ,-1);
1622 ELSE
1623
1624 l_numerator := gl_currency_api.get_rate_numerator_sql
1625 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1626 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1627 ,X_CONVERSION_DATE => p_proj_rev_rate_date_tab(i)
1628 ,X_CONVERSION_TYPE => p_proj_rev_rate_type_tab(i));
1629 END IF;
1630
1631 l_stage := 8200;
1632 --hr_utility.trace(to_char(l_stage));
1633 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1634 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
1635 ( p_txn_currency_code_tab(i)
1636 ,p_proj_currency_code_tab(i)
1637 ,p_proj_rev_rate_date_tab(i)
1638 ,p_proj_rev_rate_type_tab(i)
1639 ,-1);
1640 ELSE
1641 l_denominator := gl_currency_api.get_rate_denominator_sql
1642 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1643 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1644 ,X_CONVERSION_DATE => p_proj_rev_rate_date_tab(i)
1645 ,X_CONVERSION_TYPE => p_proj_rev_rate_type_tab(i));
1646 END IF;
1647 IF l_numerator > 0 AND l_denominator > 0 THEN
1648 l_stage := 8300;
1649 --hr_utility.trace(to_char(l_stage));
1650 p_proj_rev_rate_tab(i) := round(l_numerator/l_denominator,20);
1651 l_stage := 8350;
1652 --hr_utility.trace(to_char(l_stage));
1653 x_proj_revenue_tab(i) := p_txn_revenue_tab(i) *
1654 p_proj_rev_rate_tab(i);
1655 /* Rounding Enhancements */
1656 x_proj_revenue_tab(i) := pa_currency.round_trans_currency_amt1
1657 (x_proj_revenue_tab(i),p_proj_currency_code_tab(i));
1658 END IF;
1659
1660 l_stage := 8400;
1661 --hr_utility.trace(to_char(l_stage));
1662 l_cached_count := nvl(CachedRowTab.count,0) + 1;
1663 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
1664 CachedRowTab(l_cached_count).to_currency := p_proj_currency_code_tab(i);
1665 CachedRowTab(l_cached_count).numerator := l_numerator;
1666 CachedRowTab(l_cached_count).denominator := l_denominator;
1667 CachedRowTab(l_cached_count).rate := p_proj_rev_rate_tab(i);
1668 CachedRowTab(l_cached_count).rate_date := p_proj_rev_rate_date_tab(i);
1669 CachedRowTab(l_cached_count).rate_type := p_proj_rev_rate_type_tab(i);
1670 CachedRowTab(l_cached_count).line_type := 'REVENUE';
1671 -- Commented for Bug#5395732 END IF; -- l_converted_amount values
1672 END IF; -- Added for Bug#5395732
1673 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
1674 END IF; -- RevenueRate not found in Cache
1675 END IF; -- RevenueRateType <> 'User'
1676 END IF; -- TxnRevenue <> 0
1677 END IF; -- TxnCurrencyCode <> ProjCurrencyCode
1678
1679 l_stage := 8500;
1680 --hr_utility.trace(to_char(l_stage));
1681 --hr_utility.trace('x_projfunc_raw_cost_tab(i) := '||to_char(x_projfunc_raw_cost_tab(i)));
1682 --hr_utility.trace('x_projfunc_burdened_cost_tab(i) := '||to_char(x_projfunc_burdened_cost_tab(i)));
1683 --hr_utility.trace('x_projfunc_revenue_tab(i) := '||to_char(x_projfunc_revenue_tab(i)));
1684 --hr_utility.trace('x_proj_raw_cost_tab(i) := '||to_char(x_proj_raw_cost_tab(i)));
1685 --hr_utility.trace('x_proj_burdened_cost_tab(i) := '||to_char(x_proj_burdened_cost_tab(i)));
1686 --hr_utility.trace('x_proj_revenue_tab(i) := '||to_char(x_proj_revenue_tab(i)));
1687 --hr_utility.trace('x_proj_rejection_tab(i) := '||to_char(x_proj_rejection_tab(i)));
1688 --hr_utility.trace('x_projfunc_rejection_tab(i) := '||to_char(x_projfunc_rejection_tab(i)));
1689 END LOOP;
1690 l_stage := 8600;
1691 --hr_utility.trace(to_char(l_stage));
1692 pa_debug.g_err_stage := 'Leaving PA_FP_MULTI_CURRENCY_PKG.conv_mc_bulk';
1693 IF P_PA_DEBUG_MODE = 'Y' THEN
1694 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1695 END IF;
1696
1697 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1698 p_data => x_msg_data);
1699 x_msg_count := FND_MSG_PUB.Count_Msg;
1700 IF x_msg_count = 1 THEN
1701 IF x_msg_data IS NOT NULL THEN
1702 FND_MESSAGE.SET_ENCODED (x_msg_data);
1703 x_msg_data := FND_MESSAGE.GET;
1704 END IF;
1705 END IF;
1706 /* bug 4227840: wrapping the setting of debug error stack call to
1707 * pa_debug under the debug enbaling check
1708 */
1709 IF P_PA_DEBUG_MODE = 'Y' THEN
1710 pa_debug.reset_err_stack;
1711 END IF;
1712
1713 EXCEPTION WHEN OTHERS THEN
1714 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1715 p_data => x_msg_data);
1716 x_msg_count := FND_MSG_PUB.Count_Msg;
1717 IF x_msg_count = 1 THEN
1718 IF x_msg_data IS NOT NULL THEN
1719 FND_MESSAGE.SET_ENCODED (x_msg_data);
1720 x_msg_data := FND_MESSAGE.GET;
1721 END IF;
1722 END IF;
1723
1724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725 fnd_msg_pub.add_exc_msg
1726 ( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
1727 ,p_procedure_name => 'conv_mc_bulk' );
1728 pa_debug.g_err_stage := 'Stage : '||to_char(l_stage)||' '||substr(SQLERRM,1,240);
1729 IF P_PA_DEBUG_MODE = 'Y' THEN
1730 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1731 END IF;
1732 --hr_utility.trace('PA_FP_MULTI_CURRENCY_PKG.conv_mc_bulk -- Stage : ' ||to_char(l_stage)||' '||substr(SQLERRM,1,240));
1733 /* bug 4227840: wrapping the setting of debug error stack call to
1734 * pa_debug under the debug enbaling check
1735 */
1736 IF P_PA_DEBUG_MODE = 'Y' THEN
1737 pa_debug.reset_err_stack;
1738 END IF;
1739 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1740 END conv_mc_bulk;
1741
1742 PROCEDURE convert_txn_currency
1743 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
1744 ,p_entire_version IN VARCHAR2 DEFAULT 'N'
1745 ,p_budget_line_id IN NUMBER DEFAULT NULL
1746 ,p_source_context IN VARCHAR2 DEFAULT 'BUDGET_VERSION'
1747 ,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION'-- Added for Bug#5395732
1748 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1749 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1750 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1751 IS
1752
1753 l_txn_row_id_tab pa_fp_multi_currency_pkg.rowid_type_tab;
1754 l_resource_assignment_id_tab pa_fp_multi_currency_pkg.number_type_tab;
1755 l_start_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1756 l_end_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1757 l_txn_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
1758 l_txn_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1759 l_txn_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1760 l_txn_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1761 l_projfunc_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
1762 l_projfunc_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1763 l_projfunc_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1764 l_projfunc_cost_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1765 l_projfunc_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1766 l_projfunc_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1767 l_projfunc_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1768 l_projfunc_rev_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1769 l_projfunc_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1770 l_projfunc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1771 l_projfunc_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1772 l_projfunc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1773 l_projfunc_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
1774 l_proj_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
1775 l_proj_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1776 l_proj_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1777 l_proj_cost_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1778 l_proj_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1779 l_proj_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1780 l_proj_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1781 l_proj_rev_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1782 l_proj_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1783 l_proj_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1784 l_proj_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1785 l_proj_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1786 l_proj_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
1787 l_user_validate_flag_tab pa_fp_multi_currency_pkg.char240_type_tab;
1788 l_status_flag_tab pa_fp_multi_currency_pkg.char240_type_tab;
1789
1790 /* Perf Bug: 3683132 */
1791 l_fp_cur_projfunc_cost_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1792 l_fp_cur_projfunc_rev_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1793 l_fp_cur_project_cost_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1794 l_fp_cur_project_rev_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1795
1796 /* Bug fix:4259098 */
1797 l_init_quantity_tab pa_fp_multi_currency_pkg.number_type_tab;
1798 l_txn_init_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1799 l_txn_init_burden_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1800 l_txn_init_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1801 l_pfc_init_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1802 l_pfc_init_burden_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1803 l_pfc_init_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1804 l_proj_init_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1805 l_proj_init_burden_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1806 l_proj_init_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1807
1808 l_return_status VARCHAR2(240);
1809 l_msg_count NUMBER;
1810 l_msg_data VARCHAR2(2000);
1811
1812 l_entire_return_status VARCHAR2(240);
1813 l_entire_msg_count NUMBER;
1814 l_entire_msg_data VARCHAR2(2000);
1815
1816 l_rowcount number;
1817 l_stage NUMBER;
1818
1819 l_debug_mode VARCHAR2(30); /* Bug 4227840 */
1820
1821 CURSOR get_fp_options_data IS
1822 select v.project_id
1823 ,v.fin_plan_type_id
1824 ,o.projfunc_cost_rate_type
1825 ,o.projfunc_cost_rate_date_type
1826 ,o.projfunc_cost_rate_date
1827 ,o.projfunc_rev_rate_type
1828 ,o.projfunc_rev_rate_date_type
1829 ,o.projfunc_rev_rate_date
1830 ,o.project_cost_rate_type
1831 ,o.project_cost_rate_date_type
1832 ,o.project_cost_rate_date
1833 ,o.project_rev_rate_type
1834 ,o.project_rev_rate_date_type
1835 ,o.project_rev_rate_date
1836 from pa_proj_fp_options o
1837 ,pa_budget_versions v
1838 where v.budget_version_id = p_budget_version_id
1839 and o.project_id = v.project_id
1840 and nvl(o.fin_plan_type_id,0) = nvl(v.fin_plan_type_id,0)
1841 and o.fin_plan_version_id = v.budget_version_id;
1842
1843 CURSOR get_project_lvl_data IS
1844 select segment1
1845 ,project_currency_code
1846 ,projfunc_currency_code
1847 from pa_projects_all
1848 where project_id = g_project_id;
1849
1850 CURSOR all_budget_lines IS
1851 select a.rowid
1852 ,a.resource_assignment_id
1853 ,a.start_date
1854 ,a.end_date
1855 ,a.txn_currency_code
1856 ,a.txn_raw_cost
1857 ,a.txn_burdened_cost
1858 ,a.txn_revenue
1859 ,nvl(a.projfunc_currency_code,g_projfunc_currency_code)
1860 ,nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type)
1861 ,DECODE(a.projfunc_cost_exchange_rate,null,
1862 DECODE(nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',
1863 --get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJFUNC' ),
1864 -9999,
1865 a.projfunc_cost_exchange_rate),
1866 a.projfunc_cost_exchange_rate)
1867 projfunc_cost_exchange_rate
1868 ,DECODE(nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',NULL,
1869 nvl(a.projfunc_cost_rate_date_type,g_projfunc_cost_rate_date_type))
1870 ,DECODE(nvl(a.projfunc_cost_rate_date_type,
1871 g_projfunc_cost_rate_date_type),
1872 'START_DATE',a.start_date,
1873 'END_DATE' ,a.end_date,
1874 nvl(a.projfunc_cost_rate_date,g_projfunc_cost_rate_date))
1875 projfunc_cost_rate_date
1876 ,nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type)
1877 ,DECODE(a.projfunc_rev_exchange_rate,null,
1878 DECODE(nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',
1879 -9999, ---c.projfunc_rev_exchange_rate,
1880 a.projfunc_rev_exchange_rate),
1881 a.projfunc_rev_exchange_rate)
1882 projfunc_rev_exchange_rate
1883 ,DECODE(nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',NULL,
1884 nvl(a.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type))
1885 ,DECODE(nvl(a.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type),
1886 'START_DATE',a.start_date,
1887 'END_DATE' ,a.end_date,
1888 nvl(a.projfunc_rev_rate_date,g_projfunc_rev_rate_date))
1889 projfunc_rev_rate_date
1890 ,nvl(a.project_currency_code,g_proj_currency_code)
1891 ,nvl(a.project_cost_rate_type,g_proj_cost_rate_type)
1892 ,DECODE(a.project_cost_exchange_rate,null,
1893 DECODE(nvl(a.project_cost_rate_type,g_proj_cost_rate_type),'User',
1894 -9999, --c.project_cost_exchange_rate,
1895 a.project_cost_exchange_rate),
1896 a.project_cost_exchange_rate)
1897 project_cost_exchange_rate
1898 ,DECODE(nvl(a.project_cost_rate_type,g_proj_cost_rate_type),'User',NULL,
1899 nvl(a.project_cost_rate_date_type,g_proj_cost_rate_date_type))
1900 ,DECODE(nvl(a.project_cost_rate_date_type,g_proj_cost_rate_date_type),
1901 'START_DATE',a.start_date,
1902 'END_DATE' ,a.end_date,
1903 nvl(a.project_cost_rate_date,g_proj_cost_rate_date))
1904 project_cost_rate_date
1905 ,nvl(a.project_rev_rate_type,g_proj_rev_rate_type)
1906 ,DECODE(a.project_rev_exchange_rate,null,
1907 DECODE(nvl(a.project_rev_rate_type,g_proj_rev_rate_type),'User',
1908 -9999, --c.project_rev_exchange_rate,
1909 a.project_rev_exchange_rate),
1910 a.project_rev_exchange_rate)
1911 project_rev_exchange_rate
1912 ,DECODE(nvl(a.project_rev_rate_type,g_proj_rev_rate_type),'User',NULL,
1913 nvl(a.project_rev_rate_date_type,g_proj_rev_rate_date_type))
1914 ,DECODE(nvl(a.project_rev_rate_date_type,g_proj_rev_rate_date_type),
1915 'START_DATE',a.start_date,
1916 'END_DATE' ,a.end_date,
1917 nvl(a.project_rev_rate_date,g_proj_rev_rate_date))
1918 project_rev_rate_date
1919 /* Perf Bug: 3683132 */
1920 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJFUNC' ) fp_cur_projfunc_cost_rate
1921 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'REV','PROJFUNC' ) fp_cur_projfunc_rev_rate
1922 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJECT' ) fp_cur_project_cost_rate
1923 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'REV','PROJECT' ) fp_cur_project_rev_rate
1924 /* Bug fix:4259098 */
1925 ,a.init_quantity
1926 ,a.txn_init_raw_cost
1927 ,a.txn_init_burdened_cost
1928 ,a.txn_init_revenue
1929 ,a.init_raw_cost
1930 ,a.init_burdened_cost
1931 ,a.init_revenue
1932 ,a.project_init_raw_cost
1933 ,a.project_init_burdened_cost
1934 ,a.project_init_revenue
1935 from pa_budget_lines a
1936 ,pa_budget_versions bv
1937 --,pa_fp_txn_currencies c
1938 where a.budget_version_id = p_budget_version_id
1939 and bv.budget_version_id = a.budget_version_id
1940 and EXISTS (select null
1941 from pa_resource_assignments b
1942 where b.resource_assignment_id = a.resource_assignment_id
1943 and b.budget_version_id = a.budget_version_id
1944 )
1945 /** Perf Bug: 3683132 a.budget_version_id = c.fin_plan_version_id (+)
1946 and a.txn_currency_code = c.txn_currency_code (+)
1947 and a.resource_assignment_id in (select b.resource_assignment_id
1948 from pa_resource_assignments b
1949 where b.resource_assignment_id =
1950 a.resource_assignment_id
1951 and b.budget_version_id =
1952 p_budget_version_id)
1953 **/
1954 and (((NVL(p_source_context,'BUDGET_VERSION') = 'BUDGET_LINE')
1955 and a.budget_line_id = p_budget_line_id)
1956 OR
1957 (NVL(p_source_context,'BUDGET_VERSION') <> 'BUDGET_LINE')
1958 )
1959 /* Bug fix: 4085192 Select all budget lines only on or after the ETC STart date, if ETC date is populated */
1960 AND ((bv.ETC_START_DATE IS NULL)
1961 OR (bv.ETC_START_DATE IS NOT NULL
1962 AND ((a.start_date > bv.ETC_START_DATE )
1963 OR (bv.ETC_START_DATE between a.start_date and a.end_date)
1964 )
1965 )
1966 )
1967 order by a.resource_assignment_id,
1968 a.start_date,
1969 a.txn_currency_code;
1970
1971 CURSOR rollup_lines IS
1972 select r.rowid
1973 ,r.resource_assignment_id
1974 ,r.start_date
1975 ,r.end_date
1976 ,r.txn_currency_code
1977 ,nvl(r.txn_raw_cost,0)
1978 ,nvl(r.txn_burdened_cost,0)
1979 ,nvl(r.txn_revenue,0)
1980 ,nvl(r.projfunc_currency_code,g_projfunc_currency_code)
1981 ,nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type)
1982 ,DECODE(r.projfunc_cost_exchange_rate,null,
1983 DECODE(nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',
1984 -9999, --c.projfunc_cost_exchange_rate,
1985 r.projfunc_cost_exchange_rate),
1986 r.projfunc_cost_exchange_rate)
1987 projfunc_cost_exchange_rate
1988 ,DECODE(nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',Null,
1989 nvl(r.projfunc_cost_rate_date_type,g_projfunc_cost_rate_date_type))
1990 ,DECODE(nvl(r.projfunc_cost_rate_date_type,
1991 g_projfunc_cost_rate_date_type),
1992 'START_DATE',r.start_date,
1993 'END_DATE' ,r.end_date,
1994 nvl(r.projfunc_cost_rate_date,g_projfunc_cost_rate_date))
1995 projfunc_cost_rate_date
1996 ,nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type)
1997 ,DECODE(r.projfunc_rev_exchange_rate,null,
1998 DECODE(nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',
1999 -9999, ---c.projfunc_rev_exchange_rate,
2000 r.projfunc_rev_exchange_rate),
2001 r.projfunc_rev_exchange_rate)
2002 projfunc_rev_exchange_rate
2003 ,DECODE(nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',NULL,
2004 nvl(r.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type))
2005 ,DECODE(nvl(r.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type),
2006 'START_DATE',r.start_date,
2007 'END_DATE' ,r.end_date,
2008 nvl(r.projfunc_rev_rate_date,g_projfunc_rev_rate_date))
2009 projfunc_rev_rate_date
2010 ,nvl(r.project_currency_code,g_proj_currency_code)
2011 ,nvl(r.project_cost_rate_type,g_proj_cost_rate_type)
2012 ,DECODE(r.project_cost_exchange_rate,null,
2013 DECODE(nvl(r.project_cost_rate_type,g_proj_cost_rate_type),'User',
2014 -9999, --c.project_cost_exchange_rate,
2015 r.project_cost_exchange_rate),
2016 r.project_cost_exchange_rate)
2017 project_cost_exchange_rate
2018 ,DECODE(nvl(r.project_cost_rate_type,g_proj_cost_rate_type),'User',NULL,
2019 nvl(r.project_cost_rate_date_type,g_proj_cost_rate_date_type))
2020 ,DECODE(nvl(r.project_cost_rate_date_type,g_proj_cost_rate_date_type),
2021 'START_DATE',r.start_date,
2022 'END_DATE' ,r.end_date,
2023 nvl(r.project_cost_rate_date,g_proj_cost_rate_date))
2024 project_cost_rate_date
2025 ,nvl(r.project_rev_rate_type,g_proj_rev_rate_type)
2026 ,DECODE(r.project_rev_exchange_rate,null,
2027 DECODE(nvl(r.project_rev_rate_type,g_proj_rev_rate_type),'User',
2028 -9999, --c.project_rev_exchange_rate,
2029 r.project_rev_exchange_rate),
2030 r.project_rev_exchange_rate)
2031 project_rev_exchange_rate
2032 ,DECODE(nvl(r.project_rev_rate_type,g_proj_rev_rate_type),'User',NULL,
2033 nvl(r.project_rev_rate_date_type,g_proj_rev_rate_date_type))
2034 ,DECODE(nvl(r.project_rev_rate_date_type,g_proj_rev_rate_date_type),
2035 'START_DATE',r.start_date,
2036 'END_DATE' ,r.end_date,
2037 nvl(r.project_rev_rate_date,g_proj_rev_rate_date))
2038 project_rev_rate_date
2039 /* Perf Bug: 3683132 */
2040 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'COST','PROJFUNC' ) fp_cur_projfunc_cost_rate
2041 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'REV','PROJFUNC' ) fp_cur_projfunc_rev_rate
2042 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'COST','PROJECT' ) fp_cur_project_cost_rate
2043 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'REV','PROJECT' ) fp_cur_project_rev_rate
2044 /* Bug fix:4259098 */
2045 ,r.init_quantity
2046 ,r.txn_init_raw_cost
2047 ,r.txn_init_burdened_cost
2048 ,r.txn_init_revenue
2049 ,r.init_raw_cost
2050 ,r.init_burdened_cost
2051 ,r.init_revenue
2052 ,r.project_init_raw_cost
2053 ,r.project_init_burdened_cost
2054 ,r.project_init_revenue
2055 from pa_fp_rollup_tmp r
2056 --,pa_fp_txn_currencies c
2057 where nvl(r.delete_flag,'N') = 'N'
2058 /** Perf Bug: 3683132 and p_budget_version_id = c.fin_plan_version_id (+)
2059 and r.txn_currency_code = c.txn_currency_code (+)
2060 **/
2061 order by r.resource_assignment_id,
2062 r.start_date,
2063 r.txn_currency_code;
2064
2065 BEGIN
2066
2067 /** Bug fix: 3849908 initialization of msg stack here removes all the error msgs added during the calculate api and spread process
2068 * so commenting out
2069 *fnd_msg_pub.initialize;
2070 **/
2071
2072 l_entire_return_status := FND_API.G_RET_STS_SUCCESS;
2073 x_return_status := FND_API.G_RET_STS_SUCCESS;
2074
2075 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2076 l_debug_mode := NVL(l_debug_mode, 'Y');
2077
2078 /* bug 4227840: wrapping the setting of debug error stack call to
2079 * pa_debug under the debug enbaling check
2080 */
2081 IF l_debug_mode = 'Y' THEN
2082 pa_debug.set_err_stack('convert_txn_currency');
2083 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2084 END IF;
2085
2086 -- Get default attributes for currency conversion from version level
2087 -- proj_fp_options
2088 l_stage := 100;
2089 IF NOT get_fp_options_data%ISOPEN THEN
2090 OPEN get_fp_options_data;
2091 ELSE
2092 CLOSE get_fp_options_data;
2093 OPEN get_fp_options_data;
2094 END IF;
2095
2096 BEGIN
2097 l_stage := 200;
2098 FETCH get_fp_options_data INTO
2099 g_project_id
2100 ,g_fin_plan_type_id
2101 ,g_projfunc_cost_rate_type
2102 ,g_projfunc_cost_rate_date_type
2103 ,g_projfunc_cost_rate_date
2104 ,g_projfunc_rev_rate_type
2105 ,g_projfunc_rev_rate_date_type
2106 ,g_projfunc_rev_rate_date
2107 ,g_proj_cost_rate_type
2108 ,g_proj_cost_rate_date_type
2109 ,g_proj_cost_rate_date
2110 ,g_proj_rev_rate_type
2111 ,g_proj_rev_rate_date_type
2112 ,g_proj_rev_rate_date;
2113 EXCEPTION WHEN NO_DATA_FOUND THEN
2114 /* bug 4227840: wrapping the setting of debug error stack call to
2115 * pa_debug under the debug enbaling check
2116 */
2117 IF P_PA_DEBUG_MODE = 'Y' THEN
2118 pa_debug.reset_err_stack;
2119 END IF;
2120 RAISE;
2121 END;
2122
2123 pa_debug.g_err_stage := 'pfc cost rate date' || g_projfunc_cost_rate_date;
2124 IF P_PA_DEBUG_MODE = 'Y' THEN
2125 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2126 END IF;
2127
2128 pa_debug.g_err_stage := 'pfc cost rate type' || g_projfunc_cost_rate_type;
2129 IF P_PA_DEBUG_MODE = 'Y' THEN
2130 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2131 END IF;
2132
2133 pa_debug.g_err_stage := 'pfc cost rate date type' || g_projfunc_cost_rate_date_type;
2134 IF P_PA_DEBUG_MODE = 'Y' THEN
2135 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2136 END IF;
2137
2138 pa_debug.g_err_stage := 'pfc rev rate date' || g_projfunc_rev_rate_date;
2139 IF P_PA_DEBUG_MODE = 'Y' THEN
2140 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2141 END IF;
2142
2143 pa_debug.g_err_stage := 'pfc rev rate type' || g_projfunc_rev_rate_type;
2144 IF P_PA_DEBUG_MODE = 'Y' THEN
2145 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2146 END IF;
2147
2148 pa_debug.g_err_stage := 'pfc rev rate date type' || g_projfunc_rev_rate_date_type;
2149 IF P_PA_DEBUG_MODE = 'Y' THEN
2150 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2151 END IF;
2152
2153 CLOSE get_fp_options_data;
2154
2155 l_stage := 200;
2156 -- Get project level Info
2157 IF NOT get_project_lvl_data%ISOPEN THEN
2158 OPEN get_project_lvl_data;
2159 ELSE
2160 CLOSE get_project_lvl_data;
2161 OPEN get_project_lvl_data;
2162 END IF;
2163 l_stage := 300;
2164 BEGIN
2165 l_stage := 400;
2166 FETCH get_project_lvl_data INTO
2167 g_project_number
2168 ,g_proj_currency_code
2169 ,g_projfunc_currency_code;
2170 EXCEPTION WHEN OTHERS THEN
2171 RAISE;
2172 END;
2173
2174 --hr_utility.trace('ProjectCurr=> '||g_proj_currency_code);
2175 --hr_utility.trace('ProjFuncCurr=> '||g_projfunc_currency_code);
2176
2177 IF p_entire_version = 'Y' THEN
2178 l_stage := 500;
2179 IF NOT all_budget_lines%ISOPEN THEN
2180 OPEN all_budget_lines;
2181 ELSE
2182 CLOSE all_budget_lines;
2183 OPEN all_budget_lines;
2184 END IF;
2185 ELSE
2186 l_stage := 600;
2187 IF NOT rollup_lines%ISOPEN THEN
2188 OPEN rollup_lines;
2189 ELSE
2190 CLOSE rollup_lines;
2191 OPEN rollup_lines;
2192 END IF;
2193 END IF;
2194 l_stage := 700;
2195 LOOP
2196 BEGIN
2197
2198 --Reset PL/SQL Tables.
2199
2200 l_txn_row_id_tab.delete;
2201 l_resource_assignment_id_tab.delete;
2202 l_start_date_tab.delete;
2203 l_end_date_tab.delete;
2204 l_txn_currency_code_tab.delete;
2205 l_txn_raw_cost_tab.delete;
2206 l_txn_burdened_cost_tab.delete;
2207 l_txn_revenue_tab.delete;
2208 l_projfunc_currency_code_tab.delete;
2209 l_projfunc_cost_rate_type_tab.delete;
2210 l_projfunc_cost_rate_tab.delete;
2211 l_projfunc_cost_rt_dt_typ_tab.delete;
2212 l_projfunc_cost_rate_date_tab.delete;
2213 l_projfunc_rev_rate_type_tab.delete;
2214 l_projfunc_rev_rate_tab.delete;
2215 l_projfunc_rev_rt_dt_typ_tab.delete;
2216 l_projfunc_rev_rate_date_tab.delete;
2217 l_projfunc_raw_cost_tab.delete;
2218 l_projfunc_burdened_cost_tab.delete;
2219 l_projfunc_revenue_tab.delete;
2220 l_projfunc_rejection_tab.delete;
2221 l_proj_currency_code_tab.delete;
2222 l_proj_cost_rate_type_tab.delete;
2223 l_proj_cost_rate_tab.delete;
2224 l_proj_cost_rt_dt_typ_tab.delete;
2225 l_proj_cost_rate_date_tab.delete;
2226 l_proj_rev_rate_type_tab.delete;
2227 l_proj_rev_rate_tab.delete;
2228 l_proj_rev_rt_dt_typ_tab.delete;
2229 l_proj_rev_rate_date_tab.delete;
2230 l_proj_raw_cost_tab.delete;
2231 l_proj_burdened_cost_tab.delete;
2232 l_proj_revenue_tab.delete;
2233 l_proj_rejection_tab.delete;
2234 l_user_validate_flag_tab.delete;
2235 l_status_flag_tab.delete;
2236 /* Perf Bug: 3683132 */
2237 l_fp_cur_projfunc_cost_rt_tab.delete;
2238 l_fp_cur_projfunc_rev_rt_tab.delete;
2239 l_fp_cur_project_cost_rt_tab.delete;
2240 l_fp_cur_project_rev_rt_tab.delete;
2241 /* Bug fix:4259098 */
2242 l_init_quantity_tab.delete;
2243 l_txn_init_raw_cost_tab.delete;
2244 l_txn_init_burden_cost_tab.delete;
2245 l_txn_init_revenue_tab.delete;
2246 l_pfc_init_raw_cost_tab.delete;
2247 l_pfc_init_burden_cost_tab.delete;
2248 l_pfc_init_revenue_tab.delete;
2249 l_proj_init_raw_cost_tab.delete;
2250 l_proj_init_burden_cost_tab.delete;
2251 l_proj_init_revenue_tab.delete;
2252
2253 IF p_entire_version = 'Y' THEN
2254 l_stage := 800;
2255 FETCH all_budget_lines
2256 BULK COLLECT INTO
2257 l_txn_row_id_tab
2258 ,l_resource_assignment_id_tab
2259 ,l_start_date_tab
2260 ,l_end_date_tab
2261 ,l_txn_currency_code_tab
2262 ,l_txn_raw_cost_tab
2263 ,l_txn_burdened_cost_tab
2264 ,l_txn_revenue_tab
2265 ,l_projfunc_currency_code_tab
2266 ,l_projfunc_cost_rate_type_tab
2267 ,l_projfunc_cost_rate_tab
2268 ,l_projfunc_cost_rt_dt_typ_tab
2269 ,l_projfunc_cost_rate_date_tab
2270 ,l_projfunc_rev_rate_type_tab
2271 ,l_projfunc_rev_rate_tab
2272 ,l_projfunc_rev_rt_dt_typ_tab
2273 ,l_projfunc_rev_rate_date_tab
2274 ,l_proj_currency_code_tab
2275 ,l_proj_cost_rate_type_tab
2276 ,l_proj_cost_rate_tab
2277 ,l_proj_cost_rt_dt_typ_tab
2278 ,l_proj_cost_rate_date_tab
2279 ,l_proj_rev_rate_type_tab
2280 ,l_proj_rev_rate_tab
2281 ,l_proj_rev_rt_dt_typ_tab
2282 ,l_proj_rev_rate_date_tab
2283 /* Perf Bug: 3683132 */
2284 ,l_fp_cur_projfunc_cost_rt_tab
2285 ,l_fp_cur_projfunc_rev_rt_tab
2286 ,l_fp_cur_project_cost_rt_tab
2287 ,l_fp_cur_project_rev_rt_tab /* Bug fix: 4204134 LIMIT 1000; */
2288 ,l_init_quantity_tab
2289 ,l_txn_init_raw_cost_tab
2290 ,l_txn_init_burden_cost_tab
2291 ,l_txn_init_revenue_tab
2292 ,l_pfc_init_raw_cost_tab
2293 ,l_pfc_init_burden_cost_tab
2294 ,l_pfc_init_revenue_tab
2295 ,l_proj_init_raw_cost_tab
2296 ,l_proj_init_burden_cost_tab
2297 ,l_proj_init_revenue_tab;
2298 ELSE
2299 l_stage := 900;
2300 FETCH rollup_lines
2301 BULK COLLECT INTO
2302 l_txn_row_id_tab
2303 ,l_resource_assignment_id_tab
2304 ,l_start_date_tab
2305 ,l_end_date_tab
2306 ,l_txn_currency_code_tab
2307 ,l_txn_raw_cost_tab
2308 ,l_txn_burdened_cost_tab
2309 ,l_txn_revenue_tab
2310 ,l_projfunc_currency_code_tab
2311 ,l_projfunc_cost_rate_type_tab
2312 ,l_projfunc_cost_rate_tab
2313 ,l_projfunc_cost_rt_dt_typ_tab
2314 ,l_projfunc_cost_rate_date_tab
2315 ,l_projfunc_rev_rate_type_tab
2316 ,l_projfunc_rev_rate_tab
2317 ,l_projfunc_rev_rt_dt_typ_tab
2318 ,l_projfunc_rev_rate_date_tab
2319 ,l_proj_currency_code_tab
2320 ,l_proj_cost_rate_type_tab
2321 ,l_proj_cost_rate_tab
2322 ,l_proj_cost_rt_dt_typ_tab
2323 ,l_proj_cost_rate_date_tab
2324 ,l_proj_rev_rate_type_tab
2325 ,l_proj_rev_rate_tab
2326 ,l_proj_rev_rt_dt_typ_tab
2327 ,l_proj_rev_rate_date_tab
2328 /* Perf Bug: 3683132 */
2329 ,l_fp_cur_projfunc_cost_rt_tab
2330 ,l_fp_cur_projfunc_rev_rt_tab
2331 ,l_fp_cur_project_cost_rt_tab
2332 ,l_fp_cur_project_rev_rt_tab /* Bug fix: 4204134 LIMIT 1000; */
2333 ,l_init_quantity_tab
2334 ,l_txn_init_raw_cost_tab
2335 ,l_txn_init_burden_cost_tab
2336 ,l_txn_init_revenue_tab
2337 ,l_pfc_init_raw_cost_tab
2338 ,l_pfc_init_burden_cost_tab
2339 ,l_pfc_init_revenue_tab
2340 ,l_proj_init_raw_cost_tab
2341 ,l_proj_init_burden_cost_tab
2342 ,l_proj_init_revenue_tab;
2343
2344 END IF;
2345
2346 L_ROWCOUNT := l_txn_row_id_tab.count;
2347
2348 EXIT WHEN l_rowcount = 0;
2349
2350 IF l_rowcount > 0 THEN
2351
2352 l_stage := 1000;
2353 /* Perf Bug: 3683132 */
2354 FOR i IN l_resource_assignment_id_tab.FIRST .. l_resource_assignment_id_tab.LAST LOOP
2355 /* Bug fix:4259098 */
2356 -- calculate the ETC costs and pass this costs for pc and pfc conversion
2357 l_txn_raw_cost_tab(i) := NVL(l_txn_raw_cost_tab(i),0) - NVL(l_txn_init_raw_cost_tab(i),0);
2358 If l_txn_raw_cost_tab(i) = 0 Then
2359 l_txn_raw_cost_tab(i) := NULL;
2360 End If;
2361
2362 l_txn_burdened_cost_tab(i) := NVL(l_txn_burdened_cost_tab(i),0) - NVL(l_txn_init_burden_cost_tab(i),0);
2363 If l_txn_burdened_cost_tab(i) = 0 Then
2364 l_txn_burdened_cost_tab(i) := NULL;
2365 End If;
2366
2367 l_txn_revenue_tab(i) := NVL(l_txn_revenue_tab(i),0) - NVL(l_txn_init_revenue_tab(i),0);
2368 If l_txn_revenue_tab(i) = 0 Then
2369 l_txn_revenue_tab(i) := NULL;
2370 End If;
2371 /* end of bug fix:4259098 */
2372
2373 IF l_projfunc_cost_rate_type_tab(i) = 'User' Then
2374 If l_projfunc_cost_rate_tab(i) = -9999 Then
2375 l_projfunc_cost_rate_tab(i) := l_fp_cur_projfunc_cost_rt_tab(i);
2376 End If;
2377 End If;
2378 IF l_projfunc_rev_rate_type_tab(i) = 'User' Then
2379 If l_projfunc_rev_rate_tab(i) = -9999 Then
2380 l_projfunc_rev_rate_tab(i) := l_fp_cur_projfunc_rev_rt_tab(i);
2381 End If;
2382 End If;
2383
2384 IF l_proj_cost_rate_type_tab(i) = 'User' Then
2385 If l_proj_cost_rate_tab(i) = -9999 Then
2386 l_proj_cost_rate_tab(i) := l_fp_cur_project_cost_rt_tab(i);
2387 End If;
2388 End If;
2389 IF l_proj_rev_rate_type_tab(i) = 'User' Then
2390 If l_proj_rev_rate_tab(i) = -9999 Then
2391 l_proj_rev_rate_tab(i) := l_fp_cur_project_rev_rt_tab(i);
2392 End If;
2393 End If;
2394 END LOOP;
2395 /* End of Perf Bug: 3683132 */
2396
2397 --hr_utility.trace('Calling conv_mc_bulk...');
2398 pa_fp_multi_currency_pkg.conv_mc_bulk (
2399 p_resource_assignment_id_tab => l_resource_assignment_id_tab
2400 ,p_start_date_tab => l_start_date_tab
2401 ,p_end_date_tab => l_end_date_tab
2402 ,p_txn_currency_code_tab => l_txn_currency_code_tab
2403 ,p_txn_raw_cost_tab => l_txn_raw_cost_tab
2404 ,p_txn_burdened_cost_tab => l_txn_burdened_cost_tab
2405 ,p_txn_revenue_tab => l_txn_revenue_tab
2406 ,p_projfunc_currency_code_tab => l_projfunc_currency_code_tab
2407 ,p_projfunc_cost_rate_type_tab => l_projfunc_cost_rate_type_tab
2408 ,p_projfunc_cost_rate_tab => l_projfunc_cost_rate_tab
2409 ,p_projfunc_cost_rate_date_tab => l_projfunc_cost_rate_date_tab
2410 ,p_projfunc_rev_rate_type_tab => l_projfunc_rev_rate_type_tab
2411 ,p_projfunc_rev_rate_tab => l_projfunc_rev_rate_tab
2412 ,p_projfunc_rev_rate_date_tab => l_projfunc_rev_rate_date_tab
2413 ,x_projfunc_raw_cost_tab => l_projfunc_raw_cost_tab
2414 ,x_projfunc_burdened_cost_tab => l_projfunc_burdened_cost_tab
2415 ,x_projfunc_revenue_tab => l_projfunc_revenue_tab
2416 ,x_projfunc_rejection_tab => l_projfunc_rejection_tab
2417 ,p_proj_currency_code_tab => l_proj_currency_code_tab
2418 ,p_proj_cost_rate_type_tab => l_proj_cost_rate_type_tab
2419 ,p_proj_cost_rate_tab => l_proj_cost_rate_tab
2420 ,p_proj_cost_rate_date_tab => l_proj_cost_rate_date_tab
2421 ,p_proj_rev_rate_type_tab => l_proj_rev_rate_type_tab
2422 ,p_proj_rev_rate_tab => l_proj_rev_rate_tab
2423 ,p_proj_rev_rate_date_tab => l_proj_rev_rate_date_tab
2424 ,x_proj_raw_cost_tab => l_proj_raw_cost_tab
2425 ,x_proj_burdened_cost_tab => l_proj_burdened_cost_tab
2426 ,x_proj_revenue_tab => l_proj_revenue_tab
2427 ,x_proj_rejection_tab => l_proj_rejection_tab
2428 ,p_user_validate_flag_tab => l_user_validate_flag_tab
2429 ,p_calling_module => p_calling_module -- Added for Bug#5395732
2430 ,x_return_status => l_return_status
2431 ,x_msg_count => l_msg_count
2432 ,x_msg_data => l_msg_data);
2433
2434 l_entire_msg_count := nvl(l_entire_msg_count,0) + nvl(l_msg_count,0);
2435 l_entire_msg_data := l_msg_data;
2436
2437 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2438 l_stage := 1200;
2439 l_entire_return_status := l_return_status;
2440 END IF;
2441
2442
2443 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2444 l_stage := 1300;
2445 IF l_txn_row_id_tab.COUNT > 0 THEN --{
2446 /* Bug fix:4259098: re-calculate the plan cost by adding the actuals to the etc */
2447 FOR i IN l_txn_row_id_tab.FIRST .. l_txn_row_id_tab.LAST LOOP
2448 l_projfunc_raw_cost_tab(i) := NVL(l_projfunc_raw_cost_tab(i),0) + NVL(l_pfc_init_raw_cost_tab(i),0);
2449 If l_projfunc_raw_cost_tab(i) = 0 Then
2450 l_projfunc_raw_cost_tab(i) := null;
2451 End If;
2452
2453 l_projfunc_burdened_cost_tab(i) := NVL(l_projfunc_burdened_cost_tab(i),0) + NVL(l_pfc_init_burden_cost_tab(i),0);
2454 If l_projfunc_burdened_cost_tab(i) = 0 Then
2455 l_projfunc_burdened_cost_tab(i) := NULL;
2456 End If;
2457
2458 l_projfunc_revenue_tab(i) := NVL(l_projfunc_revenue_tab(i),0) + NVL(l_pfc_init_revenue_tab(i),0);
2459 If l_projfunc_revenue_tab(i) = 0 Then
2460 l_projfunc_revenue_tab(i) := NULL;
2461 End If;
2462
2463 l_proj_raw_cost_tab(i) := NVL(l_proj_raw_cost_tab(i),0) + NVL(l_proj_init_raw_cost_tab(i),0);
2464 If l_proj_raw_cost_tab(i) = 0 Then
2465 l_proj_raw_cost_tab(i) := NULL;
2466 End If;
2467
2468 l_proj_burdened_cost_tab(i) := NVL(l_proj_burdened_cost_tab(i),0) + NVL(l_proj_init_burden_cost_tab(i),0);
2469 If l_proj_burdened_cost_tab(i) = 0 Then
2470 l_proj_burdened_cost_tab(i) := NULL;
2471 End If;
2472 l_proj_revenue_tab(i) := NVL(l_proj_revenue_tab(i),0) + NVL(l_proj_init_revenue_tab(i),0);
2473 If l_proj_revenue_tab(i) = 0 Then
2474 l_proj_revenue_tab(i) := NULL;
2475 End If;
2476 END LOOP;
2477 END IF; --}
2478 /* end of bug fix:4259098 */
2479 IF p_entire_version = 'Y' THEN
2480 l_stage := 1400;
2481
2482 L_ROWCOUNT := l_projfunc_currency_code_tab.count;
2483
2484 IF l_rowcount > 0 THEN
2485 --hr_utility.trace('Updating pa_budget_lines...');
2486 FORALL i in 1..l_rowcount
2487 UPDATE pa_budget_lines
2488 SET projfunc_currency_code = l_projfunc_currency_code_tab(i)
2489 ,projfunc_cost_rate_type = l_projfunc_cost_rate_type_tab(i)
2490 ,projfunc_cost_exchange_rate = l_projfunc_cost_rate_tab(i)
2491 ,projfunc_cost_rate_date_type = l_projfunc_cost_rt_dt_typ_tab(i)
2492 ,projfunc_cost_rate_date = DECODE(l_projfunc_cost_rt_dt_typ_tab(i),
2493 'FIXED_DATE',l_projfunc_cost_rate_date_tab(i),
2494 NULL)
2495 ,projfunc_rev_rate_type = l_projfunc_rev_rate_type_tab(i)
2496 ,projfunc_rev_exchange_rate = l_projfunc_rev_rate_tab(i)
2497 ,projfunc_rev_rate_date_type = l_projfunc_rev_rt_dt_typ_tab(i)
2498 ,projfunc_rev_rate_date = DECODE(l_projfunc_rev_rt_dt_typ_tab(i),
2499 'FIXED_DATE',l_projfunc_rev_rate_date_tab(i),
2500 NULL)
2501 ,raw_cost = l_projfunc_raw_cost_tab(i)
2502 ,burdened_cost = l_projfunc_burdened_cost_tab(i)
2503 ,revenue = l_projfunc_revenue_tab(i)
2504 ,pfc_cur_conv_rejection_code = l_projfunc_rejection_tab(i)
2505 ,project_currency_code = l_proj_currency_code_tab(i)
2506 ,project_cost_rate_type = l_proj_cost_rate_type_tab(i)
2507 ,project_cost_exchange_rate = l_proj_cost_rate_tab(i)
2508 ,project_cost_rate_date_type = l_proj_cost_rt_dt_typ_tab(i)
2509 ,project_cost_rate_date = DECODE(l_proj_cost_rt_dt_typ_tab(i),
2510 'FIXED_DATE',l_proj_cost_rate_date_tab(i),
2511 NULL)
2512 ,project_rev_rate_type = l_proj_rev_rate_type_tab(i)
2513 ,project_rev_exchange_rate = l_proj_rev_rate_tab(i)
2514 ,project_rev_rate_date_type = l_proj_rev_rt_dt_typ_tab(i)
2515 ,project_rev_rate_date = DECODE(l_proj_rev_rt_dt_typ_tab(i),
2516 'FIXED_DATE',l_proj_rev_rate_date_tab(i),
2517 NULL)
2518 ,project_raw_cost = l_proj_raw_cost_tab(i)
2519 ,project_burdened_cost = l_proj_burdened_cost_tab(i)
2520 ,project_revenue = l_proj_revenue_tab(i)
2521 ,pc_cur_conv_rejection_code = l_proj_rejection_tab(i)
2522 WHERE rowid = l_txn_row_id_tab(i);
2523 END IF;
2524 ELSE
2525
2526 L_ROWCOUNT := l_projfunc_currency_code_tab.count;
2527
2528 IF l_rowcount > 0 THEN
2529 --hr_utility.trace('Updating pa_fp_rollup_tmp...');
2530 l_stage := 1500;
2531 FORALL i in 1..l_rowcount
2532 UPDATE pa_fp_rollup_tmp
2533 SET projfunc_currency_code = l_projfunc_currency_code_tab(i)
2534 ,projfunc_cost_rate_type = l_projfunc_cost_rate_type_tab(i)
2535 ,projfunc_cost_exchange_rate = l_projfunc_cost_rate_tab(i)
2536 ,projfunc_cost_rate_date_type = l_projfunc_cost_rt_dt_typ_tab(i)
2537 ,projfunc_cost_rate_date = DECODE(l_projfunc_cost_rt_dt_typ_tab(i),
2538 'FIXED_DATE',l_projfunc_cost_rate_date_tab(i),
2539 NULL)
2540 ,projfunc_rev_rate_type = l_projfunc_rev_rate_type_tab(i)
2541 ,projfunc_rev_exchange_rate = l_projfunc_rev_rate_tab(i)
2542 ,projfunc_rev_rate_date_type = l_projfunc_rev_rt_dt_typ_tab(i)
2543 ,projfunc_rev_rate_date = DECODE(l_projfunc_rev_rt_dt_typ_tab(i),
2544 'FIXED_DATE',l_projfunc_rev_rate_date_tab(i),
2545 NULL)
2546 ,projfunc_raw_cost = l_projfunc_raw_cost_tab(i)
2547 ,projfunc_burdened_cost = l_projfunc_burdened_cost_tab(i)
2548 ,projfunc_revenue = l_projfunc_revenue_tab(i)
2549 ,pfc_cur_conv_rejection_code = l_projfunc_rejection_tab(i)
2550 ,project_currency_code = l_proj_currency_code_tab(i)
2551 ,project_cost_rate_type = l_proj_cost_rate_type_tab(i)
2552 ,project_cost_exchange_rate = l_proj_cost_rate_tab(i)
2553 ,project_cost_rate_date_type = l_proj_cost_rt_dt_typ_tab(i)
2554 ,project_cost_rate_date = DECODE(l_proj_cost_rt_dt_typ_tab(i),
2555 'FIXED_DATE',l_proj_cost_rate_date_tab(i),
2556 NULL)
2557 ,project_rev_rate_type = l_proj_rev_rate_type_tab(i)
2558 ,project_rev_exchange_rate = l_proj_rev_rate_tab(i)
2559 ,project_rev_rate_date_type = l_proj_rev_rt_dt_typ_tab(i)
2560 ,project_rev_rate_date = DECODE(l_proj_rev_rt_dt_typ_tab(i),
2561 'FIXED_DATE',l_proj_rev_rate_date_tab(i),
2562 NULL)
2563 ,project_raw_cost = l_proj_raw_cost_tab(i)
2564 ,project_burdened_cost = l_proj_burdened_cost_tab(i)
2565 ,project_revenue = l_proj_revenue_tab(i)
2566 ,pc_cur_conv_rejection_code = l_proj_rejection_tab(i)
2567 WHERE rowid = l_txn_row_id_tab(i);
2568 END IF;
2569 END IF; -- entire_version or not
2570 END IF; -- returned success
2571 END IF; -- rowcount > 0
2572
2573 EXCEPTION WHEN OTHERS THEN
2574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2575 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2576 p_data => x_msg_data);
2577 x_msg_count := FND_MSG_PUB.Count_Msg;
2578 IF x_msg_count = 1 THEN
2579 IF x_msg_data IS NOT NULL THEN
2580 FND_MESSAGE.SET_ENCODED (x_msg_data);
2581 x_msg_data := FND_MESSAGE.GET;
2582 END IF;
2583 END IF;
2584
2585 fnd_msg_pub.add_exc_msg
2586 ( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
2587 ,p_procedure_name => 'convert_txn_currency' );
2588 pa_debug.g_err_stage := 'Stage : '||to_char(l_stage)||' '||substr(SQLERRM,1,240);
2589 IF P_PA_DEBUG_MODE = 'Y' THEN
2590 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2591 END IF;
2592 --hr_utility.trace('PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency -- Stage : ' ||to_char(l_stage)||' '||substr(SQLERRM,1,240));
2593 /* bug 4227840: wrapping the setting of debug error stack call to
2594 * pa_debug under the debug enbaling check
2595 */
2596 IF P_PA_DEBUG_MODE = 'Y' THEN
2597 pa_debug.reset_err_stack;
2598 END IF;
2599 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2600 END;
2601
2602 END LOOP;
2603
2604 IF p_entire_version = 'Y' THEN
2605 CLOSE all_budget_lines;
2606 ELSE
2607 CLOSE rollup_lines;
2608 END IF;
2609
2610 x_return_status := l_entire_return_status;
2611 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2612 p_data => x_msg_data);
2613 x_msg_count := FND_MSG_PUB.Count_Msg;
2614 IF x_msg_count = 1 THEN
2615 IF x_msg_data IS NOT NULL THEN
2616 FND_MESSAGE.SET_ENCODED (x_msg_data);
2617 x_msg_data := FND_MESSAGE.GET;
2618 END IF;
2619 END IF;
2620
2621 /* bug 4227840: wrapping the setting of debug error stack call to
2622 * pa_debug under the debug enbaling check
2623 */
2624 IF P_PA_DEBUG_MODE = 'Y' THEN
2625 pa_debug.reset_err_stack;
2626 END IF;
2627
2628 EXCEPTION WHEN OTHERS THEN
2629 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2630 p_data => x_msg_data);
2631 x_msg_count := FND_MSG_PUB.Count_Msg;
2632 IF x_msg_count = 1 THEN
2633 IF x_msg_data IS NOT NULL THEN
2634 FND_MESSAGE.SET_ENCODED (x_msg_data);
2635 x_msg_data := FND_MESSAGE.GET;
2636 END IF;
2637 END IF;
2638
2639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2640 fnd_msg_pub.add_exc_msg
2641 ( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
2642 ,p_procedure_name => 'convert_txn_currency' );
2643 pa_debug.g_err_stage := 'Stage : '||to_char(l_stage)||' '||substr(SQLERRM,1,240);
2644 IF P_PA_DEBUG_MODE = 'Y' THEN
2645 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2646 END IF;
2647 --hr_utility.trace('PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency -- Stage : ' ||to_char(l_stage)||' '||substr(SQLERRM,1,240));
2648 /* bug 4227840: wrapping the setting of debug error stack call to
2649 * pa_debug under the debug enbaling check
2650 */
2651 IF P_PA_DEBUG_MODE = 'Y' THEN
2652 pa_debug.reset_err_stack;
2653 END IF;
2654 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2655 END convert_txn_currency;
2656
2657 /*=============================================================================
2658 This api is used to Round budget line amounts as per the currency precision/
2659 MAU (Minimum Accountable Unit). Quantity would be rounded to 5 decimal points.
2660 The api would be called from Copy Version Amounts flow with non-zero adj %
2661 The api is also called Change Order Revenue amount partial implementation.
2662
2663 p_calling_context -> COPY_VERSION, CHANGE_ORDER_MERGE
2664 The parameters p_bls_inserted_after_id will be used only
2665 when p_calling_context is CHANGE_ORDER_MERGE
2666 p_bls_inserted_after_id : This value will be used to find out the budget lines that
2667 got inserted in this flow. All the budget lines with
2668 1. budget line id > p_bls_inserted_after_id AND
2669 2. budget_Version_id = p_budget_version_id
2670 will be considered as inserted in this flow.
2671
2672 Tracking bug No: 4035856 Rravipat Initial creation
2673 ==============================================================================*/
2674
2675 PROCEDURE Round_Budget_Line_Amounts(
2676 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
2677 ,p_bls_inserted_after_id IN pa_budget_lines.budget_line_id%TYPE DEFAULT NULL
2678 ,p_calling_context IN VARCHAR2
2679 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2680 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2681 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2682 AS
2683
2684 -- variables used for debugging
2685 l_return_status VARCHAR2(1);
2686 l_msg_count NUMBER := 0;
2687 l_msg_data VARCHAR2(2000);
2688 l_data VARCHAR2(2000);
2689 l_msg_index_out NUMBER;
2690 l_debug_mode VARCHAR2(30);
2691
2692 -- nested tables to hold amount and currency columns
2693 l_txn_row_id_tab pa_fp_multi_currency_pkg.rowid_type_tab;
2694 l_resource_assignment_id_tab pa_fp_multi_currency_pkg.number_type_tab;
2695 l_start_date_tab pa_fp_multi_currency_pkg.date_type_tab;
2696 l_quantity_tab pa_fp_multi_currency_pkg.number_type_tab;
2697
2698 --Code changes for bug 4200168 starts here.
2699 --l_txn_currency_code_tab pa_fp_multi_currency_pkg.char30_type_tab;
2700 -- l_txn_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2701 --l_txn_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2702 --l_txn_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
2703 l_txn_currency_code_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
2704 l_txn_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2705 l_txn_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2706 l_txn_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2707
2708 /*l_projfunc_currency_code_tab pa_fp_multi_currency_pkg.char30_type_tab;
2709 l_projfunc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2710 l_projfunc_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2711 l_projfunc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
2712 l_proj_currency_code_tab pa_fp_multi_currency_pkg.char30_type_tab;
2713 l_proj_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2714 l_proj_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2715 l_proj_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;*/
2716
2717 l_projfunc_currency_code_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
2718 l_proj_currency_code_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
2719
2720 l_projfunc_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2721 l_projfunc_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2722 l_projfunc_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2723
2724 l_proj_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2725 l_proj_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2726 l_proj_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2727
2728 --these tables below are introduced to store values when the txn, projfunc and project currency are not equal
2729 -- for any budget line.
2730
2731 l_projfunc_raw_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2732 l_prjfnc_burdened_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2733 l_projfunc_revenue_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2734 l_proj_raw_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2735 l_proj_burdened_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2736 l_proj_revenue_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2737 l_pfc_tmp_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type(); --store the projfunc currency code.
2738 l_pc_tmp_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type(); -- store the proj currency code.
2739 --Code changes for bug 4200168 ends here.
2740
2741 --Bug 4052403
2742 l_rate_based_flag_tab pa_plsql_datatypes.Char1TabTyp;
2743 l_version_type pa_budget_versions.version_type%TYPE;
2744
2745 -- cursor to fetch budget line amounts
2746 -- Changes in this cursor might have to be done in budget_line_amounts_cur1 also
2747 -- Bug 4052403. Selected rate_based_flag
2748 CURSOR budget_line_amounts_cur IS
2749 SELECT pbl.ROWID, pbl.resource_assignment_id,pbl.start_date, pbl.quantity,
2750 pbl.raw_cost, pbl.burdened_cost, pbl.revenue,pbl.projfunc_currency_code,
2751 pbl.project_raw_cost, pbl.project_burdened_cost, pbl.project_revenue, pbl.project_currency_code,
2752 pbl.txn_raw_cost, pbl.txn_burdened_cost, pbl.txn_revenue, pbl.txn_currency_code, pra.rate_based_flag
2753 FROM pa_budget_lines pbl,
2754 pa_resource_assignments pra
2755 WHERE pbl.budget_version_id = p_budget_version_id
2756 AND pra.resource_assignment_id=pbl.resource_assignment_id
2757 ORDER BY pbl.txn_currency_code;
2758
2759 -- This cursor is same as budget_line_amounts_cur. This will be used in CHANGE_ORDER_MERGE context
2760 -- Changes in this cursor might have to be done in budget_line_amounts_cur also
2761 -- Bug 4052403. Selected rate_based_flag
2762 CURSOR budget_line_amounts_cur1
2763 IS
2764 SELECT pbl.ROWID, pbl.resource_assignment_id,pbl.start_date, pbl.quantity,
2765 pbl.raw_cost, pbl.burdened_cost, pbl.revenue,pbl.projfunc_currency_code,
2766 pbl.project_raw_cost, pbl.project_burdened_cost, pbl.project_revenue, pbl.project_currency_code,
2767 pbl.txn_raw_cost, pbl.txn_burdened_cost, pbl.txn_revenue, pbl.txn_currency_code, pra.rate_based_flag
2768 FROM pa_budget_lines pbl,
2769 pa_resource_assignments pra
2770 WHERE pbl.budget_version_id = p_budget_version_id
2771 AND pra.resource_assignment_id=pbl.resource_assignment_id
2772 AND budget_line_id > p_bls_inserted_after_id
2773 ORDER BY pbl.txn_currency_code;
2774
2775 BEGIN
2776
2777 x_msg_count := 0;
2778 x_return_status := FND_API.G_RET_STS_SUCCESS;
2779
2780 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2781 l_debug_mode := NVL(l_debug_mode, 'Y');
2782
2783 /* bug 4227840: wrapping the setting of debug error stack call to
2784 * pa_debug under the debug enbaling check
2785 */
2786 IF l_debug_mode = 'Y' THEN
2787 -- set curr function
2788 pa_debug.set_curr_function(
2789 p_function =>'PA_FP_MULTI_CURRENCY_PKG.Round_Budget_Line_Amounts'
2790 ,p_debug_mode => l_debug_mode );
2791 END IF;
2792
2793 -- check for business rules violations
2794 IF l_debug_mode = 'Y' THEN
2795 pa_debug.g_err_stage:='Validating input parameters';
2796 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,3);
2797 END IF;
2798
2799 IF (p_budget_version_id IS NULL) OR (p_calling_context IS NULL) OR
2800 (p_calling_context NOT IN ('COPY_VERSION','CHANGE_ORDER_MERGE'))
2801 THEN
2802
2803 IF l_debug_mode = 'Y' THEN
2804 pa_debug.g_err_stage:='p_budget_version_id = '||p_budget_version_id;
2805 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
2806
2807 pa_debug.g_err_stage:='p_calling_context = '||p_calling_context;
2808 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
2809 END IF;
2810
2811 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2812 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2813 p_token1 => 'PROCEDURENAME',
2814 p_value1 => 'PA_FP_MULTI_CURRENCY_PKG.Round_Budget_Line_Amounts',
2815 p_token2 => 'STAGE',
2816 p_value2 => 'p_budget_version_id '||p_budget_version_id ||' p_calling_context '||p_calling_context );
2817
2818 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2819
2820 END IF;
2821
2822 IF p_calling_context='CHANGE_ORDER_MERGE' AND
2823 nvl(p_bls_inserted_after_id,0) <= 0 THEN
2824
2825 IF l_debug_mode = 'Y' THEN
2826
2827 pa_debug.g_err_stage:='p_bls_inserted_after_id = '||p_bls_inserted_after_id;
2828 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
2829
2830 END IF;
2831
2832 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2833 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2834 p_token1 => 'PROCEDURENAME',
2835 p_value1 => 'PA_FP_MULTI_CURRENCY_PKG.Round_Budget_Line_Amounts',
2836 p_token2 => 'STAGE',
2837 p_value2 => 'p_bls_inserted_after_id '||p_bls_inserted_after_id);
2838
2839
2840 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2841
2842 END IF;
2843
2844 --Bug 4052403. Select the version type of the budget version for which the API is called
2845 SELECT version_type
2846 INTO l_version_type
2847 FROM pa_budget_versions
2848 WHERE budget_version_id=p_budget_version_id;
2849
2850 -- open and fetch PC,PFC and txn cur amounts of the budget version
2851 IF p_calling_context='CHANGE_ORDER_MERGE' THEN
2852
2853 OPEN budget_line_amounts_cur1;
2854
2855 ELSE
2856
2857 OPEN budget_line_amounts_cur;
2858
2859 END IF;
2860
2861 LOOP
2862
2863 l_txn_row_id_tab.delete;
2864 l_resource_assignment_id_tab.delete;
2865 l_start_date_tab.delete;
2866 l_quantity_tab.delete;
2867 l_projfunc_raw_cost_tab.delete;
2868 l_projfunc_burdened_cost_tab.delete;
2869 l_projfunc_revenue_tab.delete;
2870 l_projfunc_currency_code_tab.delete;
2871 l_proj_raw_cost_tab.delete;
2872 l_proj_burdened_cost_tab.delete;
2873 l_proj_revenue_tab.delete;
2874 l_proj_currency_code_tab.delete;
2875 l_txn_raw_cost_tab.delete;
2876 l_txn_burdened_cost_tab.delete;
2877 l_txn_revenue_tab.delete;
2878 l_txn_currency_code_tab.delete;
2879 l_rate_based_flag_tab.delete;--Bug 4052403
2880 l_projfunc_raw_cost_tmp_tab.delete; -- Added for bug 4290451.
2881 l_prjfnc_burdened_cost_tmp_tab.delete; -- Added for bug 4290451.
2882 l_projfunc_revenue_tmp_tab.delete; -- Added for bug 4290451.
2883 l_proj_raw_cost_tmp_tab.delete; -- Added for bug 4290451.
2884 l_proj_burdened_cost_tmp_tab.delete; -- Added for bug 4290451.
2885 l_proj_revenue_tmp_tab.delete; -- Added for bug 4290451.
2886 l_pfc_tmp_tab.delete; -- Added for bug 4290451.
2887 l_pc_tmp_tab.delete; -- Added for bug 4290451.
2888
2889
2890 IF p_calling_context='CHANGE_ORDER_MERGE' THEN
2891
2892 FETCH budget_line_amounts_cur1
2893 BULK COLLECT INTO
2894 l_txn_row_id_tab
2895 ,l_resource_assignment_id_tab
2896 ,l_start_date_tab
2897 ,l_quantity_tab
2898 ,l_projfunc_raw_cost_tab
2899 ,l_projfunc_burdened_cost_tab
2900 ,l_projfunc_revenue_tab
2901 ,l_projfunc_currency_code_tab
2902 ,l_proj_raw_cost_tab
2903 ,l_proj_burdened_cost_tab
2904 ,l_proj_revenue_tab
2905 ,l_proj_currency_code_tab
2906 ,l_txn_raw_cost_tab
2907 ,l_txn_burdened_cost_tab
2908 ,l_txn_revenue_tab
2909 ,l_txn_currency_code_tab
2910 ,l_rate_based_flag_tab;
2911 /* Bug fix: 4204134 LIMIT 1000; */
2912
2913 ELSE
2914
2915 FETCH budget_line_amounts_cur
2916 BULK COLLECT INTO
2917 l_txn_row_id_tab
2918 ,l_resource_assignment_id_tab
2919 ,l_start_date_tab
2920 ,l_quantity_tab
2921 ,l_projfunc_raw_cost_tab
2922 ,l_projfunc_burdened_cost_tab
2923 ,l_projfunc_revenue_tab
2924 ,l_projfunc_currency_code_tab
2925 ,l_proj_raw_cost_tab
2926 ,l_proj_burdened_cost_tab
2927 ,l_proj_revenue_tab
2928 ,l_proj_currency_code_tab
2929 ,l_txn_raw_cost_tab
2930 ,l_txn_burdened_cost_tab
2931 ,l_txn_revenue_tab
2932 ,l_txn_currency_code_tab
2933 ,l_rate_based_flag_tab ;
2934 /* Bug fix: 4204134 LIMIT 1000; */
2935
2936 END IF;
2937
2938 -- exit if there are no rows to be processed
2939 EXIT WHEN l_txn_row_id_tab.count = 0;
2940
2941 --Code changes for bug 4200168 starts here.
2942 l_txn_raw_cost_tab :=
2943 Pa_currency.round_currency_amt_nested_blk(l_txn_raw_cost_tab,
2944 l_txn_currency_code_tab);
2945 l_txn_burdened_cost_tab :=
2946 Pa_currency.round_currency_amt_nested_blk(l_txn_burdened_cost_tab,
2947 l_txn_currency_code_tab);
2948 l_txn_revenue_tab :=
2949 Pa_currency.round_currency_amt_nested_blk(l_txn_revenue_tab,
2950 l_txn_currency_code_tab);
2951 --Code changes for bug 4200168 ends here.
2952
2953 --PC and PFC amounts will be derived/rounded by the Multi Currency API when the context is
2954 --COPY_VERSION. MC API will be called from the calling API in this context
2955 IF p_calling_context <> 'COPY_VERSION' THEN
2956
2957 -- If PFC currency is equal to txn currency copy the rounded txn amount
2958 -- as PFC amount else call rounding util api
2959 FOR i IN l_txn_row_id_tab.first .. l_txn_row_id_tab.last
2960 LOOP
2961
2962 --Code addition for bug#4290451.starts here.
2963 l_projfunc_raw_cost_tmp_tab.extend;
2964 l_prjfnc_burdened_cost_tmp_tab.extend;
2965 l_projfunc_revenue_tmp_tab.extend;
2966 l_proj_raw_cost_tmp_tab.extend;
2967 l_proj_burdened_cost_tmp_tab.extend;
2968 l_proj_revenue_tmp_tab.extend;
2969 l_pfc_tmp_tab.extend;
2970 l_pc_tmp_tab.extend;
2971 --Code addition for bug#4290451.ends here.
2972
2973 --Code changes for bug 4200168 starts here.
2974 IF l_txn_currency_code_tab(i) = l_projfunc_currency_code_tab(i) THEN
2975 l_projfunc_raw_cost_tab(i) := l_txn_raw_cost_tab(i);
2976 l_projfunc_burdened_cost_tab(i) := l_txn_burdened_cost_tab(i);
2977 l_projfunc_revenue_tab(i) := l_txn_revenue_tab(i);
2978 --Added for bug 4290451
2979 l_projfunc_raw_cost_tmp_tab(i) := l_txn_raw_cost_tab(i);
2980 l_prjfnc_burdened_cost_tmp_tab(i) := l_txn_burdened_cost_tab(i);
2981 l_projfunc_revenue_tmp_tab(i) :=l_txn_revenue_tab(i);
2982 l_pfc_tmp_tab(i) := l_projfunc_currency_code_tab(i);
2983 ELSE
2984 l_projfunc_raw_cost_tmp_tab(i) := l_projfunc_raw_cost_tab(i) ;
2985 l_prjfnc_burdened_cost_tmp_tab(i) := l_projfunc_burdened_cost_tab(i) ;
2986 l_projfunc_revenue_tmp_tab(i) := l_projfunc_revenue_tab(i) ;
2987 l_pfc_tmp_tab(i) := l_projfunc_currency_code_tab(i);
2988 END IF;
2989
2990 IF l_proj_currency_code_tab(i) = l_projfunc_currency_code_tab(i) THEN
2991 l_proj_raw_cost_tab(i) := l_projfunc_raw_cost_tab(i);
2992 l_proj_burdened_cost_tab(i) := l_projfunc_burdened_cost_tab(i);
2993 l_proj_revenue_tab(i) := l_projfunc_revenue_tab(i);
2994 --Added for bug 4290451
2995 l_proj_raw_cost_tmp_tab(i) := l_projfunc_raw_cost_tab(i);
2996 l_proj_burdened_cost_tmp_tab(i) := l_projfunc_burdened_cost_tab(i);
2997 l_proj_revenue_tmp_tab(i) := l_projfunc_revenue_tab(i);
2998 l_pc_tmp_tab(i) := l_proj_currency_code_tab(i);
2999 ELSIF l_proj_currency_code_tab(i) = l_txn_currency_code_tab(i) THEN
3000 l_proj_raw_cost_tab(i) := l_txn_raw_cost_tab(i);
3001 l_proj_burdened_cost_tab(i) := l_txn_burdened_cost_tab(i);
3002 l_proj_revenue_tab(i) := l_txn_revenue_tab(i);
3003 --Added for bug 4290451
3004 l_proj_raw_cost_tmp_tab(i) := l_txn_raw_cost_tab(i);
3005 l_proj_burdened_cost_tmp_tab(i) := l_txn_burdened_cost_tab(i);
3006 l_proj_revenue_tmp_tab(i) := l_txn_revenue_tab(i);
3007 l_pc_tmp_tab(i) := l_proj_currency_code_tab(i);
3008 ELSE
3009 l_proj_raw_cost_tmp_tab(i) := l_proj_raw_cost_tab(i);
3010 l_proj_burdened_cost_tmp_tab(i) := l_proj_burdened_cost_tab(i);
3011 l_proj_revenue_tmp_tab(i) := l_proj_revenue_tab(i);
3012 l_pc_tmp_tab(i) := l_proj_currency_code_tab(i);
3013 END IF;
3014 END LOOP;
3015
3016 l_projfunc_raw_cost_tmp_tab :=
3017 Pa_currency.round_currency_amt_nested_blk(l_projfunc_raw_cost_tmp_tab,
3018 l_pfc_tmp_tab);
3019 l_prjfnc_burdened_cost_tmp_tab :=
3020 Pa_currency.round_currency_amt_nested_blk(l_prjfnc_burdened_cost_tmp_tab,
3021 l_pfc_tmp_tab);
3022 l_projfunc_revenue_tmp_tab :=
3023 Pa_currency.round_currency_amt_nested_blk(l_projfunc_revenue_tmp_tab,
3024 l_pfc_tmp_tab);
3025
3026 l_proj_raw_cost_tmp_tab :=
3027 Pa_currency.round_currency_amt_nested_blk(l_proj_raw_cost_tmp_tab,
3028 l_pc_tmp_tab);
3029 l_proj_burdened_cost_tmp_tab :=
3030 Pa_currency.round_currency_amt_nested_blk(l_proj_burdened_cost_tmp_tab,
3031 l_pc_tmp_tab);
3032 l_proj_revenue_tmp_tab :=
3033 Pa_currency.round_currency_amt_nested_blk(l_proj_revenue_tmp_tab,
3034 l_pc_tmp_tab);
3035
3036
3037 IF l_projfunc_raw_cost_tmp_tab.FIRST > 0 OR l_proj_raw_cost_tmp_tab.FIRST> 0 THEN
3038
3039 FOR i IN l_txn_row_id_tab.first .. l_txn_row_id_tab.last
3040 LOOP
3041 IF i BETWEEN l_projfunc_raw_cost_tmp_tab.FIRST AND l_projfunc_raw_cost_tmp_tab.LAST THEN
3042 l_projfunc_raw_cost_tab(i) := l_projfunc_raw_cost_tmp_tab(i);
3043 l_projfunc_burdened_cost_tab(i) := l_prjfnc_burdened_cost_tmp_tab(i);
3044 l_projfunc_revenue_tab(i) := l_projfunc_revenue_tmp_tab(i);
3045 END IF;
3046 IF i BETWEEN l_proj_raw_cost_tmp_tab.FIRST AND l_proj_raw_cost_tmp_tab.LAST THEN
3047 l_proj_raw_cost_tab(i) := l_proj_raw_cost_tmp_tab(i);
3048 l_proj_burdened_cost_tab(i) := l_proj_burdened_cost_tmp_tab(i);
3049 l_proj_revenue_tab(i) := l_proj_revenue_tmp_tab(i);
3050 END IF;
3051 END LOOP;
3052 END IF;
3053 --Code changes for bug 4200168 ends here.
3054 END IF; --IF p_calling_context <> 'COPY_VERSION' THEN
3055
3056 -- Update pa_budget_lines with the rounded amounts
3057 --Bug 4052403. In the Updates below, made sure that for non rate-based planning transactions, the quanity is updated to
3058 --raw cost/revenue as the case may be
3059 IF p_calling_context = 'COPY_VERSION' THEN
3060 -- Update txn currency amounts well, PC and PFC amounts would be null at this point of time
3061 -- Stamp cost rate, burden cost rate and bill rate as overrides
3062 FORALL i in l_txn_row_id_tab.first .. l_txn_row_id_tab.last
3063 UPDATE PA_BUDGET_LINES
3064 SET --While deriving the override rates below, the expression used here for calculating quantity is used.
3065 --Hence whenever this derivation is changed, the change has to be reflected below in override rate
3066 --derivation also
3067 quantity = Decode(l_rate_based_flag_tab(i),
3068 'N',Decode(l_version_type,
3069 'REVENUE',l_txn_revenue_tab(i),
3070 l_txn_raw_cost_tab(i)),
3071 round(l_quantity_tab(i),5))
3072 ,txn_raw_cost = l_txn_raw_cost_tab(i)
3073 ,txn_burdened_cost = l_txn_burdened_cost_tab(i)
3074 ,txn_revenue = l_txn_revenue_tab(i)
3075 ,txn_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3076 'N',Decode(l_version_type,
3077 'REVENUE',l_txn_revenue_tab(i),
3078 l_txn_raw_cost_tab(i)),
3079 round(l_quantity_tab(i),5)),
3080 null, null,
3081 0,0,
3082 l_txn_raw_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3083 'N',Decode(l_version_type,
3084 'REVENUE',l_txn_revenue_tab(i),
3085 l_txn_raw_cost_tab(i)),
3086 round(l_quantity_tab(i),5))))
3087 ,burden_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3088 'N',Decode(l_version_type,
3089 'REVENUE',l_txn_revenue_tab(i),
3090 l_txn_raw_cost_tab(i)),
3091 round(l_quantity_tab(i),5)),
3092 null, null,
3093 0,0,
3094 l_txn_burdened_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3095 'N',Decode(l_version_type,
3096 'REVENUE',l_txn_revenue_tab(i),
3097 l_txn_raw_cost_tab(i)),
3098 round(l_quantity_tab(i),5))))
3099 ,txn_bill_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3100 'N',Decode(l_version_type,
3101 'REVENUE',l_txn_revenue_tab(i),
3102 l_txn_raw_cost_tab(i)),
3103 round(l_quantity_tab(i),5)),
3104 null, null,
3105 0,0,
3106 l_txn_revenue_tab(i)/(Decode(l_rate_based_flag_tab(i),
3107 'N',Decode(l_version_type,
3108 'REVENUE',l_txn_revenue_tab(i),
3109 l_txn_raw_cost_tab(i)),
3110 round(l_quantity_tab(i),5))))
3111 WHERE rowid = l_txn_row_id_tab(i);
3112 ELSIF p_calling_context = 'CHANGE_ORDER_MERGE' THEN
3113 -- Update TXN,PFC,PC amounts Stampe cost rate, budrden cost rate and bill rate as overrides
3114 -- If any line is affected by change order merge, then cur conv rate type is stamped as 'USER'
3115 -- All other lines would have rounded amounts any way and the program should not alter them
3116 -- So only exchange rate column is updated for those lines with 'USER' as the rate type
3117 FORALL i in l_txn_row_id_tab.first .. l_txn_row_id_tab.last
3118 UPDATE PA_BUDGET_LINES
3119 SET quantity = Decode(l_rate_based_flag_tab(i),
3120 'N',Decode(l_version_type,
3121 'REVENUE',l_txn_revenue_tab(i),
3122 l_txn_raw_cost_tab(i)),
3123 round(l_quantity_tab(i),5))
3124 ,raw_cost = l_projfunc_raw_cost_tab(i)
3125 ,burdened_cost = l_projfunc_burdened_cost_tab(i)
3126 ,revenue = l_projfunc_revenue_tab(i)
3127 ,project_raw_cost = l_proj_raw_cost_tab(i)
3128 ,project_burdened_cost = l_proj_burdened_cost_tab(i)
3129 ,project_revenue = l_proj_revenue_tab(i)
3130 ,txn_raw_cost = l_txn_raw_cost_tab(i)
3131 ,txn_burdened_cost = l_txn_burdened_cost_tab(i)
3132 ,txn_revenue = l_txn_revenue_tab(i)
3133 ,txn_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3134 'N',Decode(l_version_type,
3135 'REVENUE',l_txn_revenue_tab(i),
3136 l_txn_raw_cost_tab(i)),
3137 round(l_quantity_tab(i),5)),
3138 null, null,
3139 0,0,
3140 l_txn_raw_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3141 'N',Decode(l_version_type,
3142 'REVENUE',l_txn_revenue_tab(i),
3143 l_txn_raw_cost_tab(i)),
3144 round(l_quantity_tab(i),5))))
3145 ,burden_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3146 'N',Decode(l_version_type,
3147 'REVENUE',l_txn_revenue_tab(i),
3148 l_txn_raw_cost_tab(i)),
3149 round(l_quantity_tab(i),5)),
3150 null, null,
3151 0,0,
3152 l_txn_burdened_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3153 'N',Decode(l_version_type,
3154 'REVENUE',l_txn_revenue_tab(i),
3155 l_txn_raw_cost_tab(i)),
3156 round(l_quantity_tab(i),5))))
3157 ,txn_bill_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3158 'N',Decode(l_version_type,
3159 'REVENUE',l_txn_revenue_tab(i),
3160 l_txn_raw_cost_tab(i)),
3161 round(l_quantity_tab(i),5)),
3162 null, null,
3163 0,0,
3164 l_txn_revenue_tab(i)/(Decode(l_rate_based_flag_tab(i),
3165 'N',Decode(l_version_type,
3166 'REVENUE',l_txn_revenue_tab(i),
3167 l_txn_raw_cost_tab(i)),
3168 round(l_quantity_tab(i),5))))
3169 ,projfunc_rev_exchange_rate = Decode(projfunc_rev_rate_type, 'User',
3170 Decode(l_txn_revenue_tab(i), null, null, 0,0, l_projfunc_revenue_tab(i)/l_txn_revenue_tab(i))
3171 ,null)
3172 ,project_cost_exchange_rate = Decode(project_cost_rate_type, 'User',
3173 Decode(l_txn_raw_cost_tab(i), null, null, 0,0, l_proj_raw_cost_tab(i)/l_txn_raw_cost_tab(i))
3174 ,null)
3175 ,project_rev_exchange_rate = Decode(project_rev_rate_type, 'User',
3176 Decode(l_txn_revenue_tab(i), null, null, 0,0, l_proj_revenue_tab(i)/l_txn_revenue_tab(i))
3177 ,null)
3178 WHERE rowid = l_txn_row_id_tab(i);
3179
3180 END IF;
3181
3182 END LOOP; -- Budget line amounts Cur loop
3183 IF p_calling_context='CHANGE_ORDER_MERGE' THEN
3184
3185 CLOSE budget_line_amounts_cur1;
3186
3187 ELSE
3188
3189 CLOSE budget_line_amounts_cur;
3190
3191 END IF;
3192
3193 IF l_debug_mode = 'Y' THEN
3194 pa_debug.g_err_stage:='Exiting Round_Budget_Line_Amounts';
3195 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,3);
3196 END IF;
3197
3198 /* bug 4227840: wrapping the setting of debug error stack call to
3199 * pa_debug under the debug enbaling check
3200 */
3201 IF l_debug_mode = 'Y' THEN
3202 -- reset curr function
3203 pa_debug.reset_curr_function();
3204 END IF;
3205
3206 EXCEPTION
3207
3208 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3209 l_msg_count := FND_MSG_PUB.count_msg;
3210 IF l_msg_count = 1 THEN
3211 PA_INTERFACE_UTILS_PUB.get_messages
3212 (p_encoded => FND_API.G_TRUE
3213 ,p_msg_index => 1
3214 ,p_msg_count => l_msg_count
3215 ,p_msg_data => l_msg_data
3216 ,p_data => l_data
3217 ,p_msg_index_out => l_msg_index_out);
3218
3219 x_msg_data := l_data;
3220 x_msg_count := l_msg_count;
3221 ELSE
3222 x_msg_count := l_msg_count;
3223 END IF;
3224
3225 x_return_status := FND_API.G_RET_STS_ERROR;
3226
3227 IF l_debug_mode = 'Y' THEN
3228 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
3229 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
3230
3231 END IF;
3232 /* bug 4227840: wrapping the setting of debug error stack call to
3233 * pa_debug under the debug enbaling check
3234 */
3235 IF l_debug_mode = 'Y' THEN
3236 -- reset curr function
3237 pa_debug.reset_curr_function();
3238 END IF;
3239 RETURN;
3240 WHEN Others THEN
3241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3242 x_msg_count := 1;
3243 x_msg_data := SQLERRM;
3244
3245 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
3246 ,p_procedure_name => 'Round_Budget_Line_Amounts');
3247
3248 IF l_debug_mode = 'Y' THEN
3249 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3250 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
3251 END IF;
3252
3253 /* bug 4227840: wrapping the setting of debug error stack call to
3254 * pa_debug under the debug enbaling check
3255 */
3256 IF l_debug_mode = 'Y' THEN
3257 -- reset curr function
3258 pa_debug.Reset_Curr_Function();
3259 END IF;
3260 RAISE;
3261 END Round_Budget_Line_Amounts;
3262
3263 -->This API is written as part of rounding changes. This API will be called from PAFPCIMB.implement_ci_into_single_ver
3264 -->API when partial implementation happens.
3265 ---->p_agr_currency_code,p_project_currency_code and p_projfunc_currency_code should be valid and not null
3266 ---->All the p_...tbl input parameters should have same no. of elemeents
3267 ---->p_txn...tbls will be rounded based on p_agr_currency_code, p_project_...tbls will be rounded based on
3268 --p_project_currency_code and p_projfunc_...tbls will be rounded based on p_projfunc_currency_code
3269 ---->px_quantity_tbl will be rounded to have max 5 digits after decimal point
3270 PROCEDURE round_amounts
3271 ( px_quantity_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3272 ,p_agr_currency_code IN OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
3273 ,px_txn_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3274 ,px_txn_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3275 ,px_txn_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3276 ,p_project_currency_code IN OUT NOCOPY pa_budget_lines.project_currency_code%TYPE --File.Sql.39 bug 4440895
3277 ,px_project_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3278 ,px_project_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3279 ,px_project_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3280 ,p_projfunc_currency_code IN OUT NOCOPY pa_budget_lines.projfunc_currency_code%TYPE --File.Sql.39 bug 4440895
3281 ,px_projfunc_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3282 ,px_projfunc_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3283 ,px_projfunc_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3284 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3285 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3286 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3287 IS
3288 --Start of variables used for debugging
3289 l_return_status VARCHAR2(1);
3290 l_msg_count NUMBER := 0;
3291 l_msg_data VARCHAR2(2000);
3292 l_data VARCHAR2(2000);
3293 l_msg_index_out NUMBER;
3294 l_debug_mode VARCHAR2(30);
3295 l_debug_level3 CONSTANT NUMBER :=3;
3296 l_debug_level5 CONSTANT NUMBER :=5;
3297 l_module_name VARCHAR2(200) := 'PAFPMCPB.round_amounts';
3298
3299 --Code changes for bug 4200168 starts here.
3300 l_agr_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
3301 l_project_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
3302 l_projfunc_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
3303 --Code changes for bug 4200168 ends here.
3304
3305 BEGIN
3306 x_msg_count := 0;
3307 x_return_status := FND_API.G_RET_STS_SUCCESS;
3308
3309 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3310 l_debug_mode := NVL(l_debug_mode, 'Y');
3311
3312 /* bug 4227840: wrapping the setting of debug error stack call to
3313 * pa_debug under the debug enbaling check
3314 */
3315 IF l_debug_mode = 'Y' THEN
3316 -- Set curr function
3317 pa_debug.set_curr_function(
3318 p_function =>'pafpmcpb.round_amounts'
3319 ,p_debug_mode => l_debug_mode );
3320 END IF;
3321
3322 IF l_debug_mode = 'Y' THEN
3323 pa_debug.g_err_stage:='Validating input parameters';
3324 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3325 END IF;
3326
3327 --Currency codes should not be valid
3328 IF p_agr_currency_code IS NULL OR
3329 p_project_currency_code IS NULL OR
3330 p_projfunc_currency_code IS NULL THEN
3331
3332 IF l_debug_mode = 'Y' THEN
3333
3334 pa_debug.g_err_stage:='p_agr_currency_code '||p_agr_currency_code;
3335 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3336
3337 pa_debug.g_err_stage:='p_project_currency_code '||p_project_currency_code;
3338 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3339
3340 pa_debug.g_err_stage:='p_projfunc_currency_code '||p_projfunc_currency_code;
3341 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3342
3343 END IF;
3344
3345 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3346 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
3347 p_token1 => 'PROCEDURENAME',
3348 p_value1 => 'PAFPMCPB.ROUND_AMOUNTS',
3349 p_token2 => 'STAGE',
3350 p_value2 => 'Currency Codes are Invalid');
3351
3352 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3353
3354 END IF;
3355
3356 --All the amount tbls should be equal in length
3357 IF px_txn_raw_cost_tbl.COUNT <> px_txn_burdened_cost_tbl.COUNT OR
3358 px_txn_raw_cost_tbl.COUNT <> px_txn_revenue_tbl.COUNT OR
3359 px_txn_raw_cost_tbl.COUNT <> px_project_raw_cost_tbl.COUNT OR
3360 px_txn_raw_cost_tbl.COUNT <> px_project_burdened_cost_tbl.COUNT OR
3361 px_txn_raw_cost_tbl.COUNT <> px_project_revenue_tbl.COUNT OR
3362 px_txn_raw_cost_tbl.COUNT <> px_projfunc_raw_cost_tbl.COUNT OR
3363 px_txn_raw_cost_tbl.COUNT <> px_projfunc_burdened_cost_tbl.COUNT OR
3364 px_txn_raw_cost_tbl.COUNT <> px_projfunc_revenue_tbl.COUNT OR
3365 px_txn_raw_cost_tbl.COUNT <> px_quantity_tbl.COUNT THEN
3366
3367 IF l_debug_mode = 'Y' THEN
3368
3369 pa_debug.g_err_stage:='px_txn_raw_cost_tbl.COUNT '||px_txn_raw_cost_tbl.COUNT;
3370 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3371
3372 pa_debug.g_err_stage:='px_txn_burdened_cost_tbl.COUNT '||px_txn_burdened_cost_tbl.COUNT;
3373 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3374
3375 pa_debug.g_err_stage:='px_txn_revenue_tbl.COUNT '||px_txn_revenue_tbl.COUNT;
3376 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3377
3378 pa_debug.g_err_stage:='px_project_raw_cost_tbl.COUNT '||px_project_raw_cost_tbl.COUNT;
3379 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3380
3381 pa_debug.g_err_stage:='px_project_burdened_cost_tbl.COUNT '||px_project_burdened_cost_tbl.COUNT;
3382 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3383
3384 pa_debug.g_err_stage:='px_project_revenue_tbl.COUNT '||px_project_revenue_tbl.COUNT;
3385 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3386
3387 pa_debug.g_err_stage:='px_projfunc_raw_cost_tbl.COUNT '||px_projfunc_raw_cost_tbl.COUNT;
3388 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3389
3390 pa_debug.g_err_stage:='px_projfunc_burdened_cost_tbl.COUNT '||px_projfunc_burdened_cost_tbl.COUNT;
3391 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3392
3393 pa_debug.g_err_stage:='px_projfunc_revenue_tbl.COUNT '||px_projfunc_revenue_tbl.COUNT;
3394 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3395
3396 END IF;
3397
3398 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3399 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
3400 p_token1 => 'PROCEDURENAME',
3401 p_value1 => 'PAFPMCPB.ROUND_AMOUNTS',
3402 p_token2 => 'STAGE',
3403 p_value2 => 'Amount tbls are inconsistent');
3404
3405 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3406
3407 END IF;
3408
3409 IF px_txn_raw_cost_tbl.COUNT=0 THEN
3410
3411 IF l_debug_mode = 'Y' THEN
3412
3413 pa_debug.g_err_stage:='Input tbls are empty. Returning';
3414 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3415
3416 END IF;
3417 /* bug 4227840: wrapping the setting of debug error stack call to
3418 * pa_debug under the debug enbaling check
3419 */
3420 IF l_debug_mode = 'Y' THEN
3421 pa_debug.reset_curr_function;
3422 END IF;
3423 RETURN;
3424
3425 END IF;
3426
3427 --Round the quantity
3428 IF l_debug_mode = 'Y' THEN
3429 pa_debug.g_err_stage:='Rounding Quantity';
3430 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3431 END IF;
3432
3433 FOR i IN 1..px_quantity_tbl.COUNT LOOP
3434
3435 IF px_quantity_tbl(i)<>0 THEN
3436
3437 px_quantity_tbl(i):=round(px_quantity_tbl(i),5);
3438
3439 END IF;
3440
3441 END LOOP;
3442
3443 --In 3 for loops written below all the txn/pc/pfc amounts will be rounded. 3 For loops are written to take advantage
3444 --of caching logic in Pa_currency.round_trans_currency_amt1
3445
3446 --Round the agr currency amounts.
3447 IF l_debug_mode = 'Y' THEN
3448 pa_debug.g_err_stage:='Rounding agr amounts';
3449 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3450 END IF;
3451
3452 --Code changes for bug 4200168 starts here.
3453
3454 FOR i IN 1..px_txn_raw_cost_tbl.COUNT LOOP
3455 l_agr_currency_code_tbl.extend; --added for bug#4290451
3456
3457 l_agr_currency_code_tbl(i) := p_agr_currency_code;
3458
3459
3460 END LOOP;
3461
3462 px_txn_raw_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_txn_raw_cost_tbl,
3463 l_agr_currency_code_tbl);
3464
3465 px_txn_burdened_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_txn_burdened_cost_tbl,
3466 l_agr_currency_code_tbl);
3467
3468 px_txn_revenue_tbl := Pa_currency.round_currency_amt_nested_blk(px_txn_revenue_tbl,
3469 l_agr_currency_code_tbl);
3470 --Code changes for bug 4200168 ends here.
3471
3472
3473
3474 --Round the project currency amounts.
3475 IF l_debug_mode = 'Y' THEN
3476 pa_debug.g_err_stage:='Rounding project amounts';
3477 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3478 END IF;
3479
3480 IF p_agr_currency_code=p_project_currency_code THEN
3481
3482 px_project_raw_cost_tbl := px_txn_raw_cost_tbl ;
3483 px_project_burdened_cost_tbl := px_txn_burdened_cost_tbl;
3484 px_project_revenue_tbl := px_txn_revenue_tbl ;
3485
3486 ELSE
3487 --Code changes for bug 4200168 starts here.
3488 FOR i IN 1..px_project_raw_cost_tbl.COUNT LOOP
3489 l_project_currency_code_tbl.extend; --added for bug#4290451
3490 l_project_currency_code_tbl(i) := p_project_currency_code;
3491 END LOOP;
3492
3493 px_project_raw_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_project_raw_cost_tbl,
3494 l_project_currency_code_tbl);
3495
3496 px_project_burdened_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_project_burdened_cost_tbl,
3497 l_project_currency_code_tbl);
3498
3499 px_project_revenue_tbl := Pa_currency.round_currency_amt_nested_blk(px_project_revenue_tbl,
3500 l_project_currency_code_tbl);
3501 --Code changes for bug 4200168 starts here.
3502 END IF;
3503
3504 --Round the Project Functional Currency Amounts
3505 IF l_debug_mode = 'Y' THEN
3506 pa_debug.g_err_stage:='Rounding project functional amounts';
3507 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3508 END IF;
3509
3510 IF p_agr_currency_code=p_projfunc_currency_code THEN
3511
3512 px_projfunc_raw_cost_tbl := px_txn_raw_cost_tbl ;
3513 px_projfunc_burdened_cost_tbl := px_txn_burdened_cost_tbl;
3514 px_projfunc_revenue_tbl := px_txn_revenue_tbl ;
3515
3516 ELSIF p_project_currency_code=p_projfunc_currency_code THEN
3517
3518 px_projfunc_raw_cost_tbl := px_project_raw_cost_tbl ;
3519 px_projfunc_burdened_cost_tbl := px_project_burdened_cost_tbl;
3520 px_projfunc_revenue_tbl := px_project_revenue_tbl ;
3521
3522 ELSE
3523 --Code changes for bug 4200168 starts here.
3524 FOR i IN 1..px_projfunc_raw_cost_tbl.COUNT LOOP
3525 l_projfunc_currency_code_tbl.extend; --added for bug#4290451
3526 l_projfunc_currency_code_tbl(i) := p_projfunc_currency_code;
3527 END LOOP;
3528 px_projfunc_raw_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_projfunc_raw_cost_tbl,
3529 l_projfunc_currency_code_tbl);
3530
3531 px_projfunc_burdened_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_projfunc_burdened_cost_tbl,
3532 l_projfunc_currency_code_tbl);
3533
3534 px_projfunc_revenue_tbl := Pa_currency.round_currency_amt_nested_blk(px_projfunc_revenue_tbl,
3535 l_projfunc_currency_code_tbl);
3536 --Code changes for bug 4200168 starts here.
3537 END IF;
3538
3539 IF l_debug_mode = 'Y' THEN
3540 pa_debug.g_err_stage:='Exiting round_amounts';
3541 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3542 END IF;
3543 /* bug 4227840: wrapping the setting of debug error stack call to
3544 * pa_debug under the debug enbaling check
3545 */
3546 IF l_debug_mode = 'Y' THEN
3547 -- reset curr function
3548 pa_debug.reset_curr_function;
3549 END IF;
3550 EXCEPTION
3551
3552 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3553 l_msg_count := FND_MSG_PUB.count_msg;
3554 IF l_msg_count = 1 THEN
3555 PA_INTERFACE_UTILS_PUB.get_messages
3556 (p_encoded => FND_API.G_TRUE
3557 ,p_msg_index => 1
3558 ,p_msg_count => l_msg_count
3559 ,p_msg_data => l_msg_data
3560 ,p_data => l_data
3561 ,p_msg_index_out => l_msg_index_out);
3562
3563 x_msg_data := l_data;
3564 x_msg_count := l_msg_count;
3565 ELSE
3566 x_msg_count := l_msg_count;
3567 END IF;
3568 x_return_status := FND_API.G_RET_STS_ERROR;
3569
3570 IF l_debug_mode = 'Y' THEN
3571 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
3572 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3573
3574 END IF;
3575 /* bug 4227840: wrapping the setting of debug error stack call to
3576 * pa_debug under the debug enbaling check
3577 */
3578 IF l_debug_mode = 'Y' THEN
3579 -- reset curr function
3580 pa_debug.reset_curr_function();
3581 END IF;
3582 RETURN;
3583 WHEN OTHERS THEN
3584
3585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3586 x_msg_count := 1;
3587 x_msg_data := SQLERRM;
3588
3589 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
3590 ,p_procedure_name => 'round_amounts');
3591
3592 IF l_debug_mode = 'Y' THEN
3593 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3594 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3595 END IF;
3596 /* bug 4227840: wrapping the setting of debug error stack call to
3597 * pa_debug under the debug enbaling check
3598 */
3599 IF l_debug_mode = 'Y' THEN
3600 -- reset curr function
3601 pa_debug.Reset_Curr_Function();
3602 END IF;
3603 RAISE;
3604
3605 END round_amounts;
3606
3607 END PA_FP_MULTI_CURRENCY_PKG;