[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.4.12020000.2 2013/03/28 12:24:37 tvala ship $
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','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
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','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
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','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
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
1142 IF P_PA_DEBUG_MODE = 'Y' THEN
1143 pa_debug.g_err_stage := '5500.1 x_from_currency '|| p_txn_currency_code_tab(i) || 'x_to_currency ' || p_proj_currency_code_tab(i);
1144 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1145 pa_debug.g_err_stage := 'x_conversion_date ' || p_proj_cost_rate_date_tab(i) || 'x_conversion_type ' || p_proj_cost_rate_type_tab(i);
1146 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1147 pa_debug.g_err_stage := 'X_AMOUNT ' || p_txn_raw_cost_tab(i) || 'l_converted_amount ' || l_converted_amount;
1148 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1149 END IF;
1150 IF l_converted_amount = -1 THEN
1151 /* Added the If block for Bug#5395732 */
1152 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
1153 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
1154 ( x_from_currency => p_txn_currency_code_tab(i)
1155 ,x_to_currency => p_proj_currency_code_tab(i)
1156 ,x_conversion_date => p_proj_cost_rate_date_tab(i)
1157 ,x_conversion_type => p_proj_cost_rate_type_tab(i)
1158 ,x_user_rate => 1
1159 ,x_amount => p_txn_raw_cost_tab(i)
1160 ,x_max_roll_days => -1) ;
1161 l_call_closest_flag := 'T';
1162 IF P_PA_DEBUG_MODE = 'Y' THEN
1163 pa_debug.g_err_stage := '5500.2 x_from_currency '|| p_txn_currency_code_tab(i) || 'x_to_currency ' || p_proj_currency_code_tab(i);
1164 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1165 pa_debug.g_err_stage := 'x_conversion_date ' || p_proj_cost_rate_date_tab(i) || 'x_conversion_type ' || p_proj_cost_rate_type_tab(i);
1166 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1167 pa_debug.g_err_stage := 'X_AMOUNT ' || p_txn_raw_cost_tab(i) || 'l_converted_amount ' || l_converted_amount;
1168 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1169 END IF;
1170 END IF;
1171 IF l_converted_amount = -1 THEN
1172 l_stage := 5600;
1173 --hr_utility.trace(to_char(l_stage));
1174 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given Project currency attributes. Please change the Currency attributes';
1175 IF P_PA_DEBUG_MODE = 'Y' THEN
1176 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1177 END IF;
1178 /*
1179 pa_utils.add_message
1180 ( p_app_short_name => 'PA',
1181 p_msg_name => 'PA_FP_NO_PJ_EXCH_RATE_EXISTS',
1182 p_token1 => 'PROJECT' ,
1183 p_value1 => l_project_name,
1184 p_token2 => 'TASK',
1185 p_value2 => l_task_name,
1186 p_token3 => 'RESOURCE_NAME',
1187 p_value3 => l_resource_name,
1188 p_token4 => 'RATE_DATE',
1189 p_value4 => p_proj_cost_rate_date_tab(i),
1190 p_token5 => 'TXN_CURRENCY',
1191 p_value5 => p_txn_currency_code_tab(i));
1192 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1193 p_data => x_msg_data);
1194 x_msg_count := fnd_msg_pub.count_msg;
1195 x_return_status := FND_API.G_RET_STS_ERROR;
1196 */
1197 x_proj_rejection_tab(i) := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
1198 END IF;
1199 ELSIF l_converted_amount = -2 THEN
1200 l_stage := 5700;
1201 --hr_utility.trace(to_char(l_stage));
1202 /*
1203 pa_utils.add_message
1204 (p_app_short_name => 'PA',
1205 p_msg_name => 'PA_FP_CURR_NOT_VALID',
1206 p_token1 => 'PROJECT' ,
1207 p_value1 => l_project_name,
1208 p_token2 => 'TASK',
1209 p_value2 => l_task_name,
1210 p_token3 => 'RESOURCE_NAME',
1211 p_value3 => l_resource_name,
1212 p_token4 => 'RATE_DATE',
1213 p_value4 => p_proj_cost_rate_date_tab(i),
1214 p_token5 => 'TXN_CURRENCY',
1215 p_value5 => p_txn_currency_code_tab(i));
1216 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1217 p_data => x_msg_data);
1218 x_msg_count := fnd_msg_pub.count_msg;
1219 x_return_status := FND_API.G_RET_STS_ERROR;
1220 */
1221 x_proj_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
1222 -- Commented for Bug#5395732 ELSE
1223 END IF; -- Added for Bug#5395732
1224
1225
1226 l_stage := 5900;
1227 --hr_utility.trace(to_char(l_stage));
1228 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
1229 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1230 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
1231 ( p_txn_currency_code_tab(i)
1232 ,p_proj_currency_code_tab(i)
1233 ,p_proj_cost_rate_date_tab(i)
1234 ,p_proj_cost_rate_type_tab(i)
1235 ,-1);
1236 ELSE
1237 l_numerator := gl_currency_api.get_rate_numerator_sql
1238 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1239 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1240 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1241 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i));
1242 END IF;
1243 l_stage := 6000;
1244 --hr_utility.trace(to_char(l_stage));
1245 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1246 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
1247 ( p_txn_currency_code_tab(i)
1248 ,p_proj_currency_code_tab(i)
1249 ,p_proj_cost_rate_date_tab(i)
1250 ,p_proj_cost_rate_type_tab(i)
1251 ,-1);
1252 ELSE
1253 l_denominator := gl_currency_api.get_rate_denominator_sql
1254 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1255 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1256 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1257 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i));
1258 END IF;
1259 IF l_numerator > 0 AND l_denominator > 0 THEN
1260 l_stage := 6100;
1261 --hr_utility.trace(to_char(l_stage));
1262 p_proj_cost_rate_tab(i) := round(l_numerator/l_denominator,20);
1263 l_stage := 6150;
1264 --hr_utility.trace(to_char(l_stage));
1265 x_proj_raw_cost_tab(i) := p_txn_raw_cost_tab(i) *
1266 p_proj_cost_rate_tab(i);
1267
1268 /* Rounding Enhancements */
1269 x_proj_raw_cost_tab(i) := pa_currency.round_trans_currency_amt1
1270 (x_proj_raw_cost_tab(i),p_proj_currency_code_tab(i));
1271
1272 END IF;
1273
1274 IF nvl(p_txn_burdened_cost_tab(i),0) <> 0 THEN
1275 l_stage := 6200;
1276 --hr_utility.trace(to_char(l_stage));
1277 x_proj_burdened_cost_tab(i) := (nvl(p_txn_burdened_cost_tab(i),0) *
1278 p_proj_cost_rate_tab(i));
1279 /* Rounding Enhancements */
1280 x_proj_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
1281 (x_proj_burdened_cost_tab(i),p_proj_currency_code_tab(i));
1282 END IF;
1283
1284 l_stage := 6300;
1285 --hr_utility.trace(to_char(l_stage));
1286 l_cached_count := nvl(CachedRowTab.count,0) + 1;
1287 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
1288 CachedRowTab(l_cached_count).to_currency := p_proj_currency_code_tab(i);
1289 CachedRowTab(l_cached_count).numerator := l_numerator;
1290 CachedRowTab(l_cached_count).denominator := l_denominator;
1291 CachedRowTab(l_cached_count).rate := p_proj_cost_rate_tab(i);
1292 CachedRowTab(l_cached_count).rate_date := p_proj_cost_rate_date_tab(i);
1293 CachedRowTab(l_cached_count).rate_type := p_proj_cost_rate_type_tab(i);
1294 CachedRowTab(l_cached_count).line_type := 'COST';
1295 -- Commented for Bug#5395732 END IF; -- l_converted_amount values
1296 END IF; -- Added for Bug#5395732
1297 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
1298 ELSE -- Raw Cost IS NULL or 0
1299 l_stage := 6400;
1300 --hr_utility.trace(to_char(l_stage));
1301 l_converted_amount := gl_currency_api.convert_amount_sql
1302 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1303 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1304 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1305 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i)
1306 ,X_AMOUNT => p_txn_burdened_cost_tab(i));
1307 IF l_converted_amount = -1 THEN
1308 /* Added the If block for Bug#5395732 */
1309 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
1310 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
1311 ( x_from_currency => p_txn_currency_code_tab(i)
1312 ,x_to_currency => p_proj_currency_code_tab(i)
1313 ,x_conversion_date => p_proj_cost_rate_date_tab(i)
1314 ,x_conversion_type => p_proj_cost_rate_type_tab(i)
1315 ,x_user_rate => 1
1316 ,x_amount => p_txn_burdened_cost_tab(i)
1317 ,x_max_roll_days => -1) ;
1318 l_call_closest_flag := 'T';
1319 END IF;
1320 IF l_converted_amount = -1 THEN
1321
1322 l_stage := 6500;
1323 --hr_utility.trace(to_char(l_stage));
1324 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given Project currency attributes. Please change the Currency attributes';
1325 IF P_PA_DEBUG_MODE = 'Y' THEN
1326 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1327 END IF;
1328 /*
1329 pa_utils.add_message
1330 (p_app_short_name => 'PA',
1331 p_msg_name => 'PA_FP_NO_PJ_EXCH_RATE_EXISTS',
1332 p_token1 => 'PROJECT' ,
1333 p_value1 => l_project_name,
1334 p_token2 => 'TASK',
1335 p_value2 => l_task_name,
1336 p_token3 => 'RESOURCE_NAME',
1337 p_value3 => l_resource_name,
1338 p_token4 => 'RATE_DATE',
1339 p_value4 => p_proj_cost_rate_date_tab(i),
1340 p_token5 => 'TXN_CURRENCY',
1341 p_value5 => p_txn_currency_code_tab(i));
1342 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1343 p_data => x_msg_data);
1344 x_msg_count := fnd_msg_pub.count_msg;
1345 x_return_status := FND_API.G_RET_STS_ERROR;
1346 */
1347 x_proj_rejection_tab(i) := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
1348 END IF;
1349 ELSIF l_converted_amount = -2 THEN
1350 l_stage := 6600;
1351 --hr_utility.trace(to_char(l_stage));
1352 pa_debug.g_err_stage := to_char(l_stage)||'The Currency you have entered is not valid. Please re-enter the Currency';
1353 IF P_PA_DEBUG_MODE = 'Y' THEN
1354 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1355 END IF;
1356 /*
1357 pa_utils.add_message
1358 ( p_app_short_name => 'PA',
1359 p_msg_name => 'PA_FP_CURR_NOT_VALID',
1360 p_token1 => 'PROJECT' ,
1361 p_value1 => l_project_name,
1362 p_token2 => 'TASK',
1363 p_value2 => l_task_name,
1364 p_token3 => 'RESOURCE_NAME',
1365 p_value3 => l_resource_name,
1366 p_token4 => 'RATE_DATE',
1367 p_value4 => p_proj_cost_rate_date_tab(i),
1368 p_token5 => 'TXN_CURRENCY',
1369 p_value5 => p_txn_currency_code_tab(i));
1370 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1371 p_data => x_msg_data);
1372 x_msg_count := fnd_msg_pub.count_msg;
1373 x_return_status := FND_API.G_RET_STS_ERROR;
1374 */
1375 x_proj_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
1376
1377 -- Commented for Bug#5395732 ELSE
1378 END IF; -- Added for Bug#5395732
1379
1380 l_stage := 6800;
1381 --hr_utility.trace(to_char(l_stage));
1382 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
1383 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1384 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
1385 ( p_txn_currency_code_tab(i)
1386 ,p_proj_currency_code_tab(i)
1387 ,p_proj_cost_rate_date_tab(i)
1388 ,p_proj_cost_rate_type_tab(i)
1389 ,-1);
1390 ELSE
1391 l_numerator := gl_currency_api.get_rate_numerator_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 l_stage := 6900;
1398 --hr_utility.trace(to_char(l_stage));
1399 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1400 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
1401 ( p_txn_currency_code_tab(i)
1402 ,p_proj_currency_code_tab(i)
1403 ,p_proj_cost_rate_date_tab(i)
1404 ,p_proj_cost_rate_type_tab(i)
1405 ,-1);
1406 ELSE
1407
1408 l_denominator := gl_currency_api.get_rate_denominator_sql
1409 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1410 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1411 ,X_CONVERSION_DATE => p_proj_cost_rate_date_tab(i)
1412 ,X_CONVERSION_TYPE => p_proj_cost_rate_type_tab(i));
1413 END IF;
1414 IF l_numerator > 0 AND l_denominator > 0 THEN
1415 l_stage := 7000;
1416 --hr_utility.trace(to_char(l_stage));
1417 p_proj_cost_rate_tab(i) := round(l_numerator/l_denominator,20);
1418 l_stage := 7050;
1419 --hr_utility.trace(to_char(l_stage));
1420 x_proj_burdened_cost_tab(i) := p_txn_burdened_cost_tab(i) *
1421 p_proj_cost_rate_tab(i);
1422
1423 /* Rounding Enhancements */
1424 x_proj_burdened_cost_tab(i) := pa_currency.round_trans_currency_amt1
1425 (x_proj_burdened_cost_tab(i),p_proj_currency_code_tab(i));
1426 END IF;
1427
1428 l_stage := 7100;
1429 --hr_utility.trace(to_char(l_stage));
1430 l_cached_count := nvl(CachedRowTab.count,0) + 1;
1431 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
1432 CachedRowTab(l_cached_count).to_currency := p_proj_currency_code_tab(i);
1433 CachedRowTab(l_cached_count).numerator := l_numerator;
1434 CachedRowTab(l_cached_count).denominator := l_denominator;
1435 CachedRowTab(l_cached_count).rate := p_proj_cost_rate_tab(i);
1436 CachedRowTab(l_cached_count).rate_date := p_proj_cost_rate_date_tab(i);
1437 CachedRowTab(l_cached_count).rate_type := p_proj_cost_rate_type_tab(i);
1438 CachedRowTab(l_cached_count).line_type := 'COST';
1439 -- Commented for Bug#5395732 END IF; -- l_converted_amount values
1440 END IF; -- Added for Bug#5395732
1441 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
1442 END IF; -- Raw cost is NULL or 0
1443 END IF; -- Rate not found in Cache
1444 END IF; -- rate_type <> 'User'
1445 END IF; -- txn_raw or Burdened Cost <> 0
1446
1447
1448 l_stage := 7200;
1449 --hr_utility.trace(to_char(l_stage));
1450 -- Convert TxnRevenue to ProjectRevenue
1451 IF NVL(p_txn_revenue_tab(i),0) <> 0 THEN
1452 l_stage := 7300;
1453 --hr_utility.trace(to_char(l_stage));
1454 IF p_proj_rev_rate_type_tab(i) = 'User' THEN
1455 l_stage := 7400;
1456 --hr_utility.trace(to_char(l_stage));
1457 l_allow_user_rate_type := pa_multi_currency.is_user_rate_type_allowed
1458 ( p_txn_currency_code_tab(i)
1459 ,p_proj_currency_code_tab(i)
1460 ,p_proj_rev_rate_date_tab(i));
1461 IF l_allow_user_rate_type = 'Y' THEN
1462 IF p_proj_rev_rate_tab(i) IS NOT NULL THEN
1463 x_proj_revenue_tab(i) := p_txn_revenue_tab(i) *
1464 NVL(p_proj_rev_rate_tab(i),1);
1465 /* Rounding Enhancements */
1466 x_proj_revenue_tab(i) := pa_currency.round_trans_currency_amt1
1467 (x_proj_revenue_tab(i),p_proj_currency_code_tab(i));
1468 ELSE
1469 pa_debug.g_err_stage := to_char(l_stage)||': Project Revenue Rate Not Defined';
1470 IF P_PA_DEBUG_MODE = 'Y' THEN
1471 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1472 END IF;
1473 /*
1474 pa_utils.add_message
1475 ( p_app_short_name => 'PA',
1476 p_msg_name => 'PA_FP_PJ_REV_RATE_NOT_DEFINED',
1477 p_token1 => 'PROJECT' ,
1478 p_value1 => l_project_name,
1479 p_token2 => 'TASK',
1480 p_value2 => l_task_name,
1481 p_token3 => 'RESOURCE_NAME',
1482 p_value3 => l_resource_name,
1483 p_token4 => 'START_DATE',
1484 p_value4 => p_start_date_tab(i));
1485 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1486 p_data => x_msg_data);
1487 x_msg_count := fnd_msg_pub.count_msg;
1488 x_return_status := FND_API.G_RET_STS_ERROR;
1489 */
1490 x_proj_rejection_tab(i) := 'PA_FP_PJ_REV_RATE_NOT_DEFINED';
1491 END IF;
1492 ELSE
1493 l_stage := 810;
1494 --hr_utility.trace(to_char(l_stage));
1495 pa_debug.g_err_stage := to_char(l_stage)||': Revenue Rate type of User not allowed in Project Currency';
1496 IF P_PA_DEBUG_MODE = 'Y' THEN
1497 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1498 END IF;
1499 /*
1500 pa_utils.add_message
1501 ( p_app_short_name => 'PA',
1502 p_msg_name => 'PA_FP_PJC_USR_RATE_NOT_ALLOWED',
1503 p_token1 => 'PROJECT' ,
1504 p_value1 => l_project_name,
1505 p_token2 => 'TASK',
1506 p_value2 => l_task_name,
1507 p_token3 => 'RESOURCE_NAME',
1508 p_value3 => l_resource_name,
1509 p_token4 => 'START_DATE',
1510 p_value4 => p_start_date_tab(i),
1511 p_token5 => 'TXN_CURRENCY',
1512 p_value5 => p_txn_currency_code_tab(i));
1513 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1514 p_data => x_msg_data);
1515 x_msg_count := fnd_msg_pub.count_msg;
1516 x_return_status := FND_API.G_RET_STS_ERROR;
1517 */
1518 x_proj_rejection_tab(i) := 'PA_FP_PJC_USR_RATE_NOT_ALLOWED';
1519 END IF;
1520 ELSE
1521 l_stage := 7500;
1522 --hr_utility.trace(to_char(l_stage));
1523 l_done_flag := 'N';
1524
1525 IF nvl(CachedRowTab.COUNT,0) <> 0 THEN
1526 l_stage := 7600;
1527 --hr_utility.trace(to_char(l_stage));
1528 FOR j in CachedRowTab.First..CachedRowTab.Last LOOP
1529 IF CachedRowTab(j).from_currency = p_txn_currency_code_tab(i) AND
1530 CachedRowTab(j).to_currency = p_proj_currency_code_tab(i) AND
1531 CachedRowTab(j).rate_date = p_proj_rev_rate_date_tab(i) AND
1532 CachedRowTab(j).rate_type = p_proj_rev_rate_type_tab(i) AND
1533 CachedRowTab(j).line_type = 'REVENUE' THEN
1534 l_stage := 7700;
1535 --hr_utility.trace(to_char(l_stage));
1536 p_proj_rev_rate_tab(i) := CachedRowTab(j).rate;
1537 x_proj_revenue_tab(i) := nvl(p_txn_revenue_tab(i),0) *
1538 (round(CachedRowTab(j).numerator/
1539 CachedRowTab(j).denominator,20));
1540 /* Rounding Enhancements */
1541 x_proj_revenue_tab(i) := pa_currency.round_trans_currency_amt1
1542 (x_proj_revenue_tab(i),p_proj_currency_code_tab(i));
1543 l_done_flag := 'Y';
1544 EXIT;
1545 END IF; -- RevenueRateFound
1546 END LOOP; -- CachedRevenueRates
1547 END IF; -- CachedRowTab.COUNT > 0
1548 IF l_done_flag = 'N' THEN
1549 l_stage := 7700;
1550 --hr_utility.trace(to_char(l_stage));
1551 l_converted_amount := gl_currency_api.convert_amount_sql
1552 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1553 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1554 ,X_CONVERSION_DATE => p_proj_rev_rate_date_tab(i)
1555 ,X_CONVERSION_TYPE => p_proj_rev_rate_type_tab(i)
1556 ,X_AMOUNT => p_txn_revenue_tab(i));
1557
1558 IF P_PA_DEBUG_MODE = 'Y' THEN
1559 pa_debug.g_err_stage := '7700.1 x_from_currency '|| p_txn_currency_code_tab(i) || 'x_to_currency ' || p_proj_currency_code_tab(i);
1560 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1561 pa_debug.g_err_stage := 'x_conversion_date ' || p_proj_rev_rate_date_tab(i) || 'x_conversion_type ' || p_proj_rev_rate_type_tab(i);
1562 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1563 pa_debug.g_err_stage := 'X_AMOUNT ' || p_txn_revenue_tab(i) || 'l_converted_amount ' || l_converted_amount;
1564 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1565 END IF;
1566 IF l_converted_amount = -1 THEN
1567 /* Added the If block for Bug#5395732 */
1568 IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
1569 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
1570 ( x_from_currency => p_txn_currency_code_tab(i)
1571 ,x_to_currency => p_proj_currency_code_tab(i)
1572 ,x_conversion_date => p_proj_rev_rate_date_tab(i)
1573 ,x_conversion_type => p_proj_rev_rate_type_tab(i)
1574 ,x_user_rate => 1
1575 ,x_amount => p_txn_revenue_tab(i)
1576 ,x_max_roll_days => -1) ;
1577 IF P_PA_DEBUG_MODE = 'Y' THEN
1578 pa_debug.g_err_stage := '7700.2 x_from_currency '|| p_txn_currency_code_tab(i) || 'x_to_currency ' || p_proj_currency_code_tab(i);
1579 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1580 pa_debug.g_err_stage := 'x_conversion_date ' || p_proj_rev_rate_date_tab(i) || 'x_conversion_type ' || p_proj_rev_rate_type_tab(i);
1581 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1582 pa_debug.g_err_stage := 'X_AMOUNT ' || p_txn_revenue_tab(i) || 'l_converted_amount ' || l_converted_amount;
1583 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1584 END IF;
1585 l_call_closest_flag := 'T';
1586 END IF;
1587 IF l_converted_amount = -1 THEN
1588 l_stage := 7800;
1589 --hr_utility.trace(to_char(l_stage));
1590 pa_debug.g_err_stage := to_char(l_stage)||': No Exchange Rate exists for the given Project currency attributes. Please change the Currency attributes';
1591 IF P_PA_DEBUG_MODE = 'Y' THEN
1592 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1593 END IF;
1594 /*
1595 pa_utils.add_message
1596 ( p_app_short_name => 'PA',
1597 p_msg_name => 'PA_FP_NO_PJ_EXCH_RATE_EXISTS',
1598 p_token1 => 'PROJECT' ,
1599 p_value1 => l_project_name,
1600 p_token2 => 'TASK',
1601 p_value2 => l_task_name,
1602 p_token3 => 'RESOURCE_NAME',
1603 p_value3 => l_resource_name,
1604 p_token4 => 'RATE_DATE',
1605 p_value4 => p_proj_rev_rate_date_tab(i),
1606 p_token5 => 'TXN_CURRENCY',
1607 p_value5 => p_txn_currency_code_tab(i));
1608 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1609 p_data => x_msg_data);
1610 x_msg_count := fnd_msg_pub.count_msg;
1611 x_return_status := FND_API.G_RET_STS_ERROR;
1612 */
1613 x_proj_rejection_tab(i) := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
1614 END IF;
1615 ELSIF l_converted_amount = -2 THEN
1616 l_stage := 7900;
1617 --hr_utility.trace(to_char(l_stage));
1618 pa_debug.g_err_stage := to_char(l_stage)||'The Currency you have entered is not valid. Please re-enter the Currency';
1619 IF P_PA_DEBUG_MODE = 'Y' THEN
1620 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1621 END IF;
1622 /*
1623 pa_utils.add_message
1624 ( p_app_short_name => 'PA',
1625 p_msg_name => 'PA_FP_CURR_NOT_VALID',
1626 p_token1 => 'PROJECT' ,
1627 p_value1 => l_project_name,
1628 p_token2 => 'TASK',
1629 p_value2 => l_task_name,
1630 p_token3 => 'RESOURCE_NAME',
1631 p_value3 => l_resource_name,
1632 p_token4 => 'RATE_DATE',
1633 p_value4 => p_proj_rev_rate_date_tab(i),
1634 p_token5 => 'TXN_CURRENCY',
1635 p_value5 => p_txn_currency_code_tab(i));
1636 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1637 p_data => x_msg_data);
1638 x_msg_count := fnd_msg_pub.count_msg;
1639 x_return_status := FND_API.G_RET_STS_ERROR;
1640 */
1641 x_proj_rejection_tab(i) := 'PA_FP_CURR_NOT_VALID';
1642 -- Commented for Bug#5395732 ELSE
1643 END IF; -- Added for Bug#5395732
1644
1645 l_stage := 8100;
1646 --hr_utility.trace(to_char(l_stage));
1647 IF l_converted_amount <> -1 AND l_converted_amount <> -2 THEN -- Added for Bug#5395732
1648 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1649 l_numerator := GL_CURRENCY_API.get_closest_rate_numerator_sql
1650 ( p_txn_currency_code_tab(i)
1651 ,p_proj_currency_code_tab(i)
1652 ,p_proj_rev_rate_date_tab(i)
1653 ,p_proj_rev_rate_type_tab(i)
1654 ,-1);
1655 ELSE
1656
1657 l_numerator := gl_currency_api.get_rate_numerator_sql
1658 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1659 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1660 ,X_CONVERSION_DATE => p_proj_rev_rate_date_tab(i)
1661 ,X_CONVERSION_TYPE => p_proj_rev_rate_type_tab(i));
1662 END IF;
1663
1664 l_stage := 8200;
1665 --hr_utility.trace(to_char(l_stage));
1666 IF l_call_closest_flag = 'T' THEN -- Added for Bug#5395732
1667 l_denominator := GL_CURRENCY_API.get_closest_rate_denom_sql
1668 ( p_txn_currency_code_tab(i)
1669 ,p_proj_currency_code_tab(i)
1670 ,p_proj_rev_rate_date_tab(i)
1671 ,p_proj_rev_rate_type_tab(i)
1672 ,-1);
1673 ELSE
1674 l_denominator := gl_currency_api.get_rate_denominator_sql
1675 ( X_FROM_CURRENCY => p_txn_currency_code_tab(i)
1676 ,X_TO_CURRENCY => p_proj_currency_code_tab(i)
1677 ,X_CONVERSION_DATE => p_proj_rev_rate_date_tab(i)
1678 ,X_CONVERSION_TYPE => p_proj_rev_rate_type_tab(i));
1679 END IF;
1680 IF l_numerator > 0 AND l_denominator > 0 THEN
1681 l_stage := 8300;
1682 --hr_utility.trace(to_char(l_stage));
1683 p_proj_rev_rate_tab(i) := round(l_numerator/l_denominator,20);
1684 l_stage := 8350;
1685 --hr_utility.trace(to_char(l_stage));
1686 x_proj_revenue_tab(i) := p_txn_revenue_tab(i) *
1687 p_proj_rev_rate_tab(i);
1688 /* Rounding Enhancements */
1689 x_proj_revenue_tab(i) := pa_currency.round_trans_currency_amt1
1690 (x_proj_revenue_tab(i),p_proj_currency_code_tab(i));
1691 END IF;
1692
1693 l_stage := 8400;
1694 --hr_utility.trace(to_char(l_stage));
1695 l_cached_count := nvl(CachedRowTab.count,0) + 1;
1696 CachedRowTab(l_cached_count).from_currency := p_txn_currency_code_tab(i);
1697 CachedRowTab(l_cached_count).to_currency := p_proj_currency_code_tab(i);
1698 CachedRowTab(l_cached_count).numerator := l_numerator;
1699 CachedRowTab(l_cached_count).denominator := l_denominator;
1700 CachedRowTab(l_cached_count).rate := p_proj_rev_rate_tab(i);
1701 CachedRowTab(l_cached_count).rate_date := p_proj_rev_rate_date_tab(i);
1702 CachedRowTab(l_cached_count).rate_type := p_proj_rev_rate_type_tab(i);
1703 CachedRowTab(l_cached_count).line_type := 'REVENUE';
1704 -- Commented for Bug#5395732 END IF; -- l_converted_amount values
1705 END IF; -- Added for Bug#5395732
1706 l_call_closest_flag := 'N'; -- Added for Bug#5395732 Resetting.
1707 END IF; -- RevenueRate not found in Cache
1708 END IF; -- RevenueRateType <> 'User'
1709 END IF; -- TxnRevenue <> 0
1710 END IF; -- TxnCurrencyCode <> ProjCurrencyCode
1711
1712 l_stage := 8500;
1713 --hr_utility.trace(to_char(l_stage));
1714 --hr_utility.trace('x_projfunc_raw_cost_tab(i) := '||to_char(x_projfunc_raw_cost_tab(i)));
1715 --hr_utility.trace('x_projfunc_burdened_cost_tab(i) := '||to_char(x_projfunc_burdened_cost_tab(i)));
1716 --hr_utility.trace('x_projfunc_revenue_tab(i) := '||to_char(x_projfunc_revenue_tab(i)));
1717 --hr_utility.trace('x_proj_raw_cost_tab(i) := '||to_char(x_proj_raw_cost_tab(i)));
1718 --hr_utility.trace('x_proj_burdened_cost_tab(i) := '||to_char(x_proj_burdened_cost_tab(i)));
1719 --hr_utility.trace('x_proj_revenue_tab(i) := '||to_char(x_proj_revenue_tab(i)));
1720 --hr_utility.trace('x_proj_rejection_tab(i) := '||to_char(x_proj_rejection_tab(i)));
1721 --hr_utility.trace('x_projfunc_rejection_tab(i) := '||to_char(x_projfunc_rejection_tab(i)));
1722 END LOOP;
1723 l_stage := 8600;
1724 --hr_utility.trace(to_char(l_stage));
1725 pa_debug.g_err_stage := 'Leaving PA_FP_MULTI_CURRENCY_PKG.conv_mc_bulk';
1726 IF P_PA_DEBUG_MODE = 'Y' THEN
1727 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1728 END IF;
1729
1730 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1731 p_data => x_msg_data);
1732 x_msg_count := FND_MSG_PUB.Count_Msg;
1733 IF x_msg_count = 1 THEN
1734 IF x_msg_data IS NOT NULL THEN
1735 FND_MESSAGE.SET_ENCODED (x_msg_data);
1736 x_msg_data := FND_MESSAGE.GET;
1737 END IF;
1738 END IF;
1739 /* bug 4227840: wrapping the setting of debug error stack call to
1740 * pa_debug under the debug enbaling check
1741 */
1742 IF P_PA_DEBUG_MODE = 'Y' THEN
1743 pa_debug.reset_err_stack;
1744 END IF;
1745
1746 EXCEPTION WHEN OTHERS THEN
1747 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1748 p_data => x_msg_data);
1749 x_msg_count := FND_MSG_PUB.Count_Msg;
1750 IF x_msg_count = 1 THEN
1751 IF x_msg_data IS NOT NULL THEN
1752 FND_MESSAGE.SET_ENCODED (x_msg_data);
1753 x_msg_data := FND_MESSAGE.GET;
1754 END IF;
1755 END IF;
1756
1757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1758 fnd_msg_pub.add_exc_msg
1759 ( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
1760 ,p_procedure_name => 'conv_mc_bulk' );
1761 pa_debug.g_err_stage := 'Stage : '||to_char(l_stage)||' '||substr(SQLERRM,1,240);
1762 IF P_PA_DEBUG_MODE = 'Y' THEN
1763 pa_debug.write('conv_mc_bulk: ' || g_module_name,pa_debug.g_err_stage,3);
1764 END IF;
1765 --hr_utility.trace('PA_FP_MULTI_CURRENCY_PKG.conv_mc_bulk -- Stage : ' ||to_char(l_stage)||' '||substr(SQLERRM,1,240));
1766 /* bug 4227840: wrapping the setting of debug error stack call to
1767 * pa_debug under the debug enbaling check
1768 */
1769 IF P_PA_DEBUG_MODE = 'Y' THEN
1770 pa_debug.reset_err_stack;
1771 END IF;
1772 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1773 END conv_mc_bulk;
1774
1775 PROCEDURE convert_txn_currency
1776 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
1777 ,p_entire_version IN VARCHAR2 DEFAULT 'N'
1778 ,p_budget_line_id IN NUMBER DEFAULT NULL
1779 ,p_source_context IN VARCHAR2 DEFAULT 'BUDGET_VERSION'
1780 ,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION'-- Added for Bug#5395732
1781 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1782 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1783 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1784 IS
1785
1786 l_txn_row_id_tab pa_fp_multi_currency_pkg.rowid_type_tab;
1787 l_resource_assignment_id_tab pa_fp_multi_currency_pkg.number_type_tab;
1788 l_start_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1789 l_end_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1790 l_txn_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
1791 l_txn_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1792 l_txn_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1793 l_txn_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1794 l_projfunc_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
1795 l_projfunc_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1796 l_projfunc_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1797 l_projfunc_cost_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1798 l_projfunc_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1799 l_projfunc_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1800 l_projfunc_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1801 l_projfunc_rev_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1802 l_projfunc_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1803 l_projfunc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1804 l_projfunc_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1805 l_projfunc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1806 l_projfunc_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
1807 l_proj_currency_code_tab pa_fp_multi_currency_pkg.char240_type_tab;
1808 l_proj_cost_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1809 l_proj_cost_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1810 l_proj_cost_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1811 l_proj_cost_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1812 l_proj_rev_rate_type_tab pa_fp_multi_currency_pkg.char240_type_tab;
1813 l_proj_rev_rate_tab pa_fp_multi_currency_pkg.number_type_tab;
1814 l_proj_rev_rt_dt_typ_tab pa_fp_multi_currency_pkg.char240_type_tab;
1815 l_proj_rev_rate_date_tab pa_fp_multi_currency_pkg.date_type_tab;
1816 l_proj_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1817 l_proj_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1818 l_proj_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1819 l_proj_rejection_tab pa_fp_multi_currency_pkg.char30_type_tab;
1820 l_user_validate_flag_tab pa_fp_multi_currency_pkg.char240_type_tab;
1821 l_status_flag_tab pa_fp_multi_currency_pkg.char240_type_tab;
1822
1823 /* Perf Bug: 3683132 */
1824 l_fp_cur_projfunc_cost_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1825 l_fp_cur_projfunc_rev_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1826 l_fp_cur_project_cost_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1827 l_fp_cur_project_rev_rt_tab pa_fp_multi_currency_pkg.number_type_tab;
1828
1829 /* Bug fix:4259098 */
1830 l_init_quantity_tab pa_fp_multi_currency_pkg.number_type_tab;
1831 l_txn_init_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1832 l_txn_init_burden_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1833 l_txn_init_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1834 l_pfc_init_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1835 l_pfc_init_burden_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1836 l_pfc_init_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1837 l_proj_init_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1838 l_proj_init_burden_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
1839 l_proj_init_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
1840
1841 l_return_status VARCHAR2(240);
1842 l_msg_count NUMBER;
1843 l_msg_data VARCHAR2(2000);
1844
1845 l_entire_return_status VARCHAR2(240);
1846 l_entire_msg_count NUMBER;
1847 l_entire_msg_data VARCHAR2(2000);
1848
1849 l_rowcount number;
1850 l_stage NUMBER;
1851
1852 l_debug_mode VARCHAR2(30); /* Bug 4227840 */
1853
1854 CURSOR get_fp_options_data IS
1855 select v.project_id
1856 ,v.fin_plan_type_id
1857 ,o.projfunc_cost_rate_type
1858 ,o.projfunc_cost_rate_date_type
1859 ,o.projfunc_cost_rate_date
1860 ,o.projfunc_rev_rate_type
1861 ,o.projfunc_rev_rate_date_type
1862 ,o.projfunc_rev_rate_date
1863 ,o.project_cost_rate_type
1864 ,o.project_cost_rate_date_type
1865 ,o.project_cost_rate_date
1866 ,o.project_rev_rate_type
1867 ,o.project_rev_rate_date_type
1868 ,o.project_rev_rate_date
1869 from pa_proj_fp_options o
1870 ,pa_budget_versions v
1871 where v.budget_version_id = p_budget_version_id
1872 and o.project_id = v.project_id
1873 and nvl(o.fin_plan_type_id,0) = nvl(v.fin_plan_type_id,0)
1874 and o.fin_plan_version_id = v.budget_version_id;
1875
1876 CURSOR get_project_lvl_data IS
1877 select segment1
1878 ,project_currency_code
1879 ,projfunc_currency_code
1880 from pa_projects_all
1881 where project_id = g_project_id;
1882
1883 CURSOR all_budget_lines IS
1884 select a.rowid
1885 ,a.resource_assignment_id
1886 ,a.start_date
1887 ,a.end_date
1888 ,a.txn_currency_code
1889 ,a.txn_raw_cost
1890 ,a.txn_burdened_cost
1891 ,a.txn_revenue
1892 ,nvl(a.projfunc_currency_code,g_projfunc_currency_code)
1893 ,nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type)
1894 ,DECODE(a.projfunc_cost_exchange_rate,null,
1895 DECODE(nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',
1896 --get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJFUNC' ),
1897 -9999,
1898 a.projfunc_cost_exchange_rate),
1899 a.projfunc_cost_exchange_rate)
1900 projfunc_cost_exchange_rate
1901 ,DECODE(nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',NULL,
1902 nvl(a.projfunc_cost_rate_date_type,g_projfunc_cost_rate_date_type))
1903 ,DECODE(nvl(a.projfunc_cost_rate_date_type,
1904 g_projfunc_cost_rate_date_type),
1905 'START_DATE',a.start_date,
1906 'END_DATE' ,a.end_date,
1907 nvl(a.projfunc_cost_rate_date,g_projfunc_cost_rate_date))
1908 projfunc_cost_rate_date
1909 ,nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type)
1910 ,DECODE(a.projfunc_rev_exchange_rate,null,
1911 DECODE(nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',
1912 -9999, ---c.projfunc_rev_exchange_rate,
1913 a.projfunc_rev_exchange_rate),
1914 a.projfunc_rev_exchange_rate)
1915 projfunc_rev_exchange_rate
1916 ,DECODE(nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',NULL,
1917 nvl(a.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type))
1918 ,DECODE(nvl(a.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type),
1919 'START_DATE',a.start_date,
1920 'END_DATE' ,a.end_date,
1921 nvl(a.projfunc_rev_rate_date,g_projfunc_rev_rate_date))
1922 projfunc_rev_rate_date
1923 ,nvl(a.project_currency_code,g_proj_currency_code)
1924 ,nvl(a.project_cost_rate_type,g_proj_cost_rate_type)
1925 ,DECODE(a.project_cost_exchange_rate,null,
1926 DECODE(nvl(a.project_cost_rate_type,g_proj_cost_rate_type),'User',
1927 -9999, --c.project_cost_exchange_rate,
1928 a.project_cost_exchange_rate),
1929 a.project_cost_exchange_rate)
1930 project_cost_exchange_rate
1931 ,DECODE(nvl(a.project_cost_rate_type,g_proj_cost_rate_type),'User',NULL,
1932 nvl(a.project_cost_rate_date_type,g_proj_cost_rate_date_type))
1933 ,DECODE(nvl(a.project_cost_rate_date_type,g_proj_cost_rate_date_type),
1934 'START_DATE',a.start_date,
1935 'END_DATE' ,a.end_date,
1936 nvl(a.project_cost_rate_date,g_proj_cost_rate_date))
1937 project_cost_rate_date
1938 ,nvl(a.project_rev_rate_type,g_proj_rev_rate_type)
1939 ,DECODE(a.project_rev_exchange_rate,null,
1940 DECODE(nvl(a.project_rev_rate_type,g_proj_rev_rate_type),'User',
1941 -9999, --c.project_rev_exchange_rate,
1942 a.project_rev_exchange_rate),
1943 a.project_rev_exchange_rate)
1944 project_rev_exchange_rate
1945 ,DECODE(nvl(a.project_rev_rate_type,g_proj_rev_rate_type),'User',NULL,
1946 nvl(a.project_rev_rate_date_type,g_proj_rev_rate_date_type))
1947 ,DECODE(nvl(a.project_rev_rate_date_type,g_proj_rev_rate_date_type),
1948 'START_DATE',a.start_date,
1949 'END_DATE' ,a.end_date,
1950 nvl(a.project_rev_rate_date,g_proj_rev_rate_date))
1951 project_rev_rate_date
1952 /* Perf Bug: 3683132 */
1953 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJFUNC' ) fp_cur_projfunc_cost_rate
1954 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'REV','PROJFUNC' ) fp_cur_projfunc_rev_rate
1955 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJECT' ) fp_cur_project_cost_rate
1956 ,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'REV','PROJECT' ) fp_cur_project_rev_rate
1957 /* Bug fix:4259098 */
1958 ,a.init_quantity
1959 ,a.txn_init_raw_cost
1960 ,a.txn_init_burdened_cost
1961 ,a.txn_init_revenue
1962 ,a.init_raw_cost
1963 ,a.init_burdened_cost
1964 ,a.init_revenue
1965 ,a.project_init_raw_cost
1966 ,a.project_init_burdened_cost
1967 ,a.project_init_revenue
1968 from pa_budget_lines a
1969 ,pa_budget_versions bv
1970 --,pa_fp_txn_currencies c
1971 where a.budget_version_id = p_budget_version_id
1972 and bv.budget_version_id = a.budget_version_id
1973 and EXISTS (select null
1974 from pa_resource_assignments b
1975 where b.resource_assignment_id = a.resource_assignment_id
1976 and b.budget_version_id = a.budget_version_id
1977 )
1978 /** Perf Bug: 3683132 a.budget_version_id = c.fin_plan_version_id (+)
1979 and a.txn_currency_code = c.txn_currency_code (+)
1980 and a.resource_assignment_id in (select b.resource_assignment_id
1981 from pa_resource_assignments b
1982 where b.resource_assignment_id =
1983 a.resource_assignment_id
1984 and b.budget_version_id =
1985 p_budget_version_id)
1986 **/
1987 and (((NVL(p_source_context,'BUDGET_VERSION') = 'BUDGET_LINE')
1988 and a.budget_line_id = p_budget_line_id)
1989 OR
1990 (NVL(p_source_context,'BUDGET_VERSION') <> 'BUDGET_LINE')
1991 )
1992 /* Bug fix: 4085192 Select all budget lines only on or after the ETC STart date, if ETC date is populated */
1993 AND ((bv.ETC_START_DATE IS NULL)
1994 OR (bv.ETC_START_DATE IS NOT NULL
1995 AND ((a.start_date > bv.ETC_START_DATE )
1996 OR (bv.ETC_START_DATE between a.start_date and a.end_date)
1997 )
1998 )
1999 )
2000 order by a.resource_assignment_id,
2001 a.start_date,
2002 a.txn_currency_code;
2003
2004 CURSOR rollup_lines IS
2005 select r.rowid
2006 ,r.resource_assignment_id
2007 ,r.start_date
2008 ,r.end_date
2009 ,r.txn_currency_code
2010 ,nvl(r.txn_raw_cost,0)
2011 ,nvl(r.txn_burdened_cost,0)
2012 ,nvl(r.txn_revenue,0)
2013 ,nvl(r.projfunc_currency_code,g_projfunc_currency_code)
2014 ,nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type)
2015 ,DECODE(r.projfunc_cost_exchange_rate,null,
2016 DECODE(nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',
2017 -9999, --c.projfunc_cost_exchange_rate,
2018 r.projfunc_cost_exchange_rate),
2019 r.projfunc_cost_exchange_rate)
2020 projfunc_cost_exchange_rate
2021 ,DECODE(nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',Null,
2022 nvl(r.projfunc_cost_rate_date_type,g_projfunc_cost_rate_date_type))
2023 ,DECODE(nvl(r.projfunc_cost_rate_date_type,
2024 g_projfunc_cost_rate_date_type),
2025 'START_DATE',r.start_date,
2026 'END_DATE' ,r.end_date,
2027 nvl(r.projfunc_cost_rate_date,g_projfunc_cost_rate_date))
2028 projfunc_cost_rate_date
2029 ,nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type)
2030 ,DECODE(r.projfunc_rev_exchange_rate,null,
2031 DECODE(nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',
2032 -9999, ---c.projfunc_rev_exchange_rate,
2033 r.projfunc_rev_exchange_rate),
2034 r.projfunc_rev_exchange_rate)
2035 projfunc_rev_exchange_rate
2036 ,DECODE(nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',NULL,
2037 nvl(r.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type))
2038 ,DECODE(nvl(r.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type),
2039 'START_DATE',r.start_date,
2040 'END_DATE' ,r.end_date,
2041 nvl(r.projfunc_rev_rate_date,g_projfunc_rev_rate_date))
2042 projfunc_rev_rate_date
2043 ,nvl(r.project_currency_code,g_proj_currency_code)
2044 ,nvl(r.project_cost_rate_type,g_proj_cost_rate_type)
2045 ,DECODE(r.project_cost_exchange_rate,null,
2046 DECODE(nvl(r.project_cost_rate_type,g_proj_cost_rate_type),'User',
2047 -9999, --c.project_cost_exchange_rate,
2048 r.project_cost_exchange_rate),
2049 r.project_cost_exchange_rate)
2050 project_cost_exchange_rate
2051 ,DECODE(nvl(r.project_cost_rate_type,g_proj_cost_rate_type),'User',NULL,
2052 nvl(r.project_cost_rate_date_type,g_proj_cost_rate_date_type))
2053 ,DECODE(nvl(r.project_cost_rate_date_type,g_proj_cost_rate_date_type),
2054 'START_DATE',r.start_date,
2055 'END_DATE' ,r.end_date,
2056 nvl(r.project_cost_rate_date,g_proj_cost_rate_date))
2057 project_cost_rate_date
2058 ,nvl(r.project_rev_rate_type,g_proj_rev_rate_type)
2059 ,DECODE(r.project_rev_exchange_rate,null,
2060 DECODE(nvl(r.project_rev_rate_type,g_proj_rev_rate_type),'User',
2061 -9999, --c.project_rev_exchange_rate,
2062 r.project_rev_exchange_rate),
2063 r.project_rev_exchange_rate)
2064 project_rev_exchange_rate
2065 ,DECODE(nvl(r.project_rev_rate_type,g_proj_rev_rate_type),'User',NULL,
2066 nvl(r.project_rev_rate_date_type,g_proj_rev_rate_date_type))
2067 ,DECODE(nvl(r.project_rev_rate_date_type,g_proj_rev_rate_date_type),
2068 'START_DATE',r.start_date,
2069 'END_DATE' ,r.end_date,
2070 nvl(r.project_rev_rate_date,g_proj_rev_rate_date))
2071 project_rev_rate_date
2072 /* Perf Bug: 3683132 */
2073 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'COST','PROJFUNC' ) fp_cur_projfunc_cost_rate
2074 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'REV','PROJFUNC' ) fp_cur_projfunc_rev_rate
2075 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'COST','PROJECT' ) fp_cur_project_cost_rate
2076 ,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'REV','PROJECT' ) fp_cur_project_rev_rate
2077 /* Bug fix:4259098 */
2078 ,r.init_quantity
2079 ,r.txn_init_raw_cost
2080 ,r.txn_init_burdened_cost
2081 ,r.txn_init_revenue
2082 ,r.init_raw_cost
2083 ,r.init_burdened_cost
2084 ,r.init_revenue
2085 ,r.project_init_raw_cost
2086 ,r.project_init_burdened_cost
2087 ,r.project_init_revenue
2088 from pa_fp_rollup_tmp r
2089 --,pa_fp_txn_currencies c
2090 where nvl(r.delete_flag,'N') = 'N'
2091 /** Perf Bug: 3683132 and p_budget_version_id = c.fin_plan_version_id (+)
2092 and r.txn_currency_code = c.txn_currency_code (+)
2093 **/
2094 order by r.resource_assignment_id,
2095 r.start_date,
2096 r.txn_currency_code;
2097
2098 BEGIN
2099
2100 /** Bug fix: 3849908 initialization of msg stack here removes all the error msgs added during the calculate api and spread process
2101 * so commenting out
2102 *fnd_msg_pub.initialize;
2103 **/
2104
2105 l_entire_return_status := FND_API.G_RET_STS_SUCCESS;
2106 x_return_status := FND_API.G_RET_STS_SUCCESS;
2107
2108 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2109 l_debug_mode := NVL(l_debug_mode, 'Y');
2110
2111 /* bug 4227840: wrapping the setting of debug error stack call to
2112 * pa_debug under the debug enbaling check
2113 */
2114 IF l_debug_mode = 'Y' THEN
2115 pa_debug.set_err_stack('convert_txn_currency');
2116 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2117 END IF;
2118
2119 -- Get default attributes for currency conversion from version level
2120 -- proj_fp_options
2121 l_stage := 100;
2122 IF NOT get_fp_options_data%ISOPEN THEN
2123 OPEN get_fp_options_data;
2124 ELSE
2125 CLOSE get_fp_options_data;
2126 OPEN get_fp_options_data;
2127 END IF;
2128
2129 BEGIN
2130 l_stage := 200;
2131 FETCH get_fp_options_data INTO
2132 g_project_id
2133 ,g_fin_plan_type_id
2134 ,g_projfunc_cost_rate_type
2135 ,g_projfunc_cost_rate_date_type
2136 ,g_projfunc_cost_rate_date
2137 ,g_projfunc_rev_rate_type
2138 ,g_projfunc_rev_rate_date_type
2139 ,g_projfunc_rev_rate_date
2140 ,g_proj_cost_rate_type
2141 ,g_proj_cost_rate_date_type
2142 ,g_proj_cost_rate_date
2143 ,g_proj_rev_rate_type
2144 ,g_proj_rev_rate_date_type
2145 ,g_proj_rev_rate_date;
2146 EXCEPTION WHEN NO_DATA_FOUND THEN
2147 /* bug 4227840: wrapping the setting of debug error stack call to
2148 * pa_debug under the debug enbaling check
2149 */
2150 IF P_PA_DEBUG_MODE = 'Y' THEN
2151 pa_debug.reset_err_stack;
2152 END IF;
2153 RAISE;
2154 END;
2155
2156 pa_debug.g_err_stage := 'pfc cost rate date' || g_projfunc_cost_rate_date;
2157 IF P_PA_DEBUG_MODE = 'Y' THEN
2158 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2159 END IF;
2160
2161 pa_debug.g_err_stage := 'pfc cost rate type' || g_projfunc_cost_rate_type;
2162 IF P_PA_DEBUG_MODE = 'Y' THEN
2163 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2164 END IF;
2165
2166 pa_debug.g_err_stage := 'pfc cost rate date type' || g_projfunc_cost_rate_date_type;
2167 IF P_PA_DEBUG_MODE = 'Y' THEN
2168 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2169 END IF;
2170
2171 pa_debug.g_err_stage := 'pfc rev rate date' || g_projfunc_rev_rate_date;
2172 IF P_PA_DEBUG_MODE = 'Y' THEN
2173 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2174 END IF;
2175
2176 pa_debug.g_err_stage := 'pfc rev rate type' || g_projfunc_rev_rate_type;
2177 IF P_PA_DEBUG_MODE = 'Y' THEN
2178 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2179 END IF;
2180
2181 pa_debug.g_err_stage := 'pfc rev rate date type' || g_projfunc_rev_rate_date_type;
2182 IF P_PA_DEBUG_MODE = 'Y' THEN
2183 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2184 END IF;
2185
2186 CLOSE get_fp_options_data;
2187
2188 l_stage := 200;
2189 -- Get project level Info
2190 IF NOT get_project_lvl_data%ISOPEN THEN
2191 OPEN get_project_lvl_data;
2192 ELSE
2193 CLOSE get_project_lvl_data;
2194 OPEN get_project_lvl_data;
2195 END IF;
2196 l_stage := 300;
2197 BEGIN
2198 l_stage := 400;
2199 FETCH get_project_lvl_data INTO
2200 g_project_number
2201 ,g_proj_currency_code
2202 ,g_projfunc_currency_code;
2203 EXCEPTION WHEN OTHERS THEN
2204 RAISE;
2205 END;
2206
2207 --hr_utility.trace('ProjectCurr=> '||g_proj_currency_code);
2208 --hr_utility.trace('ProjFuncCurr=> '||g_projfunc_currency_code);
2209
2210 IF p_entire_version = 'Y' THEN
2211 l_stage := 500;
2212 IF NOT all_budget_lines%ISOPEN THEN
2213 OPEN all_budget_lines;
2214 ELSE
2215 CLOSE all_budget_lines;
2216 OPEN all_budget_lines;
2217 END IF;
2218 ELSE
2219 l_stage := 600;
2220 IF NOT rollup_lines%ISOPEN THEN
2221 OPEN rollup_lines;
2222 ELSE
2223 CLOSE rollup_lines;
2224 OPEN rollup_lines;
2225 END IF;
2226 END IF;
2227 l_stage := 700;
2228 LOOP
2229 BEGIN
2230
2231 --Reset PL/SQL Tables.
2232
2233 l_txn_row_id_tab.delete;
2234 l_resource_assignment_id_tab.delete;
2235 l_start_date_tab.delete;
2236 l_end_date_tab.delete;
2237 l_txn_currency_code_tab.delete;
2238 l_txn_raw_cost_tab.delete;
2239 l_txn_burdened_cost_tab.delete;
2240 l_txn_revenue_tab.delete;
2241 l_projfunc_currency_code_tab.delete;
2242 l_projfunc_cost_rate_type_tab.delete;
2243 l_projfunc_cost_rate_tab.delete;
2244 l_projfunc_cost_rt_dt_typ_tab.delete;
2245 l_projfunc_cost_rate_date_tab.delete;
2246 l_projfunc_rev_rate_type_tab.delete;
2247 l_projfunc_rev_rate_tab.delete;
2248 l_projfunc_rev_rt_dt_typ_tab.delete;
2249 l_projfunc_rev_rate_date_tab.delete;
2250 l_projfunc_raw_cost_tab.delete;
2251 l_projfunc_burdened_cost_tab.delete;
2252 l_projfunc_revenue_tab.delete;
2253 l_projfunc_rejection_tab.delete;
2254 l_proj_currency_code_tab.delete;
2255 l_proj_cost_rate_type_tab.delete;
2256 l_proj_cost_rate_tab.delete;
2257 l_proj_cost_rt_dt_typ_tab.delete;
2258 l_proj_cost_rate_date_tab.delete;
2259 l_proj_rev_rate_type_tab.delete;
2260 l_proj_rev_rate_tab.delete;
2261 l_proj_rev_rt_dt_typ_tab.delete;
2262 l_proj_rev_rate_date_tab.delete;
2263 l_proj_raw_cost_tab.delete;
2264 l_proj_burdened_cost_tab.delete;
2265 l_proj_revenue_tab.delete;
2266 l_proj_rejection_tab.delete;
2267 l_user_validate_flag_tab.delete;
2268 l_status_flag_tab.delete;
2269 /* Perf Bug: 3683132 */
2270 l_fp_cur_projfunc_cost_rt_tab.delete;
2271 l_fp_cur_projfunc_rev_rt_tab.delete;
2272 l_fp_cur_project_cost_rt_tab.delete;
2273 l_fp_cur_project_rev_rt_tab.delete;
2274 /* Bug fix:4259098 */
2275 l_init_quantity_tab.delete;
2276 l_txn_init_raw_cost_tab.delete;
2277 l_txn_init_burden_cost_tab.delete;
2278 l_txn_init_revenue_tab.delete;
2279 l_pfc_init_raw_cost_tab.delete;
2280 l_pfc_init_burden_cost_tab.delete;
2281 l_pfc_init_revenue_tab.delete;
2282 l_proj_init_raw_cost_tab.delete;
2283 l_proj_init_burden_cost_tab.delete;
2284 l_proj_init_revenue_tab.delete;
2285
2286 IF p_entire_version = 'Y' THEN
2287 l_stage := 800;
2288 FETCH all_budget_lines
2289 BULK COLLECT INTO
2290 l_txn_row_id_tab
2291 ,l_resource_assignment_id_tab
2292 ,l_start_date_tab
2293 ,l_end_date_tab
2294 ,l_txn_currency_code_tab
2295 ,l_txn_raw_cost_tab
2296 ,l_txn_burdened_cost_tab
2297 ,l_txn_revenue_tab
2298 ,l_projfunc_currency_code_tab
2299 ,l_projfunc_cost_rate_type_tab
2300 ,l_projfunc_cost_rate_tab
2301 ,l_projfunc_cost_rt_dt_typ_tab
2302 ,l_projfunc_cost_rate_date_tab
2303 ,l_projfunc_rev_rate_type_tab
2304 ,l_projfunc_rev_rate_tab
2305 ,l_projfunc_rev_rt_dt_typ_tab
2306 ,l_projfunc_rev_rate_date_tab
2307 ,l_proj_currency_code_tab
2308 ,l_proj_cost_rate_type_tab
2309 ,l_proj_cost_rate_tab
2310 ,l_proj_cost_rt_dt_typ_tab
2311 ,l_proj_cost_rate_date_tab
2312 ,l_proj_rev_rate_type_tab
2313 ,l_proj_rev_rate_tab
2314 ,l_proj_rev_rt_dt_typ_tab
2315 ,l_proj_rev_rate_date_tab
2316 /* Perf Bug: 3683132 */
2317 ,l_fp_cur_projfunc_cost_rt_tab
2318 ,l_fp_cur_projfunc_rev_rt_tab
2319 ,l_fp_cur_project_cost_rt_tab
2320 ,l_fp_cur_project_rev_rt_tab /* Bug fix: 4204134 LIMIT 1000; */
2321 ,l_init_quantity_tab
2322 ,l_txn_init_raw_cost_tab
2323 ,l_txn_init_burden_cost_tab
2324 ,l_txn_init_revenue_tab
2325 ,l_pfc_init_raw_cost_tab
2326 ,l_pfc_init_burden_cost_tab
2327 ,l_pfc_init_revenue_tab
2328 ,l_proj_init_raw_cost_tab
2329 ,l_proj_init_burden_cost_tab
2330 ,l_proj_init_revenue_tab;
2331 ELSE
2332 l_stage := 900;
2333 FETCH rollup_lines
2334 BULK COLLECT INTO
2335 l_txn_row_id_tab
2336 ,l_resource_assignment_id_tab
2337 ,l_start_date_tab
2338 ,l_end_date_tab
2339 ,l_txn_currency_code_tab
2340 ,l_txn_raw_cost_tab
2341 ,l_txn_burdened_cost_tab
2342 ,l_txn_revenue_tab
2343 ,l_projfunc_currency_code_tab
2344 ,l_projfunc_cost_rate_type_tab
2345 ,l_projfunc_cost_rate_tab
2346 ,l_projfunc_cost_rt_dt_typ_tab
2347 ,l_projfunc_cost_rate_date_tab
2348 ,l_projfunc_rev_rate_type_tab
2349 ,l_projfunc_rev_rate_tab
2350 ,l_projfunc_rev_rt_dt_typ_tab
2351 ,l_projfunc_rev_rate_date_tab
2352 ,l_proj_currency_code_tab
2353 ,l_proj_cost_rate_type_tab
2354 ,l_proj_cost_rate_tab
2355 ,l_proj_cost_rt_dt_typ_tab
2356 ,l_proj_cost_rate_date_tab
2357 ,l_proj_rev_rate_type_tab
2358 ,l_proj_rev_rate_tab
2359 ,l_proj_rev_rt_dt_typ_tab
2360 ,l_proj_rev_rate_date_tab
2361 /* Perf Bug: 3683132 */
2362 ,l_fp_cur_projfunc_cost_rt_tab
2363 ,l_fp_cur_projfunc_rev_rt_tab
2364 ,l_fp_cur_project_cost_rt_tab
2365 ,l_fp_cur_project_rev_rt_tab /* Bug fix: 4204134 LIMIT 1000; */
2366 ,l_init_quantity_tab
2367 ,l_txn_init_raw_cost_tab
2368 ,l_txn_init_burden_cost_tab
2369 ,l_txn_init_revenue_tab
2370 ,l_pfc_init_raw_cost_tab
2371 ,l_pfc_init_burden_cost_tab
2372 ,l_pfc_init_revenue_tab
2373 ,l_proj_init_raw_cost_tab
2374 ,l_proj_init_burden_cost_tab
2375 ,l_proj_init_revenue_tab;
2376
2377 END IF;
2378
2379 L_ROWCOUNT := l_txn_row_id_tab.count;
2380
2381 EXIT WHEN l_rowcount = 0;
2382
2383 IF l_rowcount > 0 THEN
2384
2385 l_stage := 1000;
2386 /* Perf Bug: 3683132 */
2387 FOR i IN l_resource_assignment_id_tab.FIRST .. l_resource_assignment_id_tab.LAST LOOP
2388 /* Bug fix:4259098 */
2389 -- calculate the ETC costs and pass this costs for pc and pfc conversion
2390 l_txn_raw_cost_tab(i) := NVL(l_txn_raw_cost_tab(i),0) - NVL(l_txn_init_raw_cost_tab(i),0);
2391 If l_txn_raw_cost_tab(i) = 0 Then
2392 l_txn_raw_cost_tab(i) := NULL;
2393 End If;
2394
2395 l_txn_burdened_cost_tab(i) := NVL(l_txn_burdened_cost_tab(i),0) - NVL(l_txn_init_burden_cost_tab(i),0);
2396 If l_txn_burdened_cost_tab(i) = 0 Then
2397 l_txn_burdened_cost_tab(i) := NULL;
2398 End If;
2399
2400 l_txn_revenue_tab(i) := NVL(l_txn_revenue_tab(i),0) - NVL(l_txn_init_revenue_tab(i),0);
2401 If l_txn_revenue_tab(i) = 0 Then
2402 l_txn_revenue_tab(i) := NULL;
2403 End If;
2404 /* end of bug fix:4259098 */
2405
2406 IF l_projfunc_cost_rate_type_tab(i) = 'User' Then
2407 If l_projfunc_cost_rate_tab(i) = -9999 Then
2408 l_projfunc_cost_rate_tab(i) := l_fp_cur_projfunc_cost_rt_tab(i);
2409 End If;
2410 End If;
2411 IF l_projfunc_rev_rate_type_tab(i) = 'User' Then
2412 If l_projfunc_rev_rate_tab(i) = -9999 Then
2413 l_projfunc_rev_rate_tab(i) := l_fp_cur_projfunc_rev_rt_tab(i);
2414 End If;
2415 End If;
2416
2417 IF l_proj_cost_rate_type_tab(i) = 'User' Then
2418 If l_proj_cost_rate_tab(i) = -9999 Then
2419 l_proj_cost_rate_tab(i) := l_fp_cur_project_cost_rt_tab(i);
2420 End If;
2421 End If;
2422 IF l_proj_rev_rate_type_tab(i) = 'User' Then
2423 If l_proj_rev_rate_tab(i) = -9999 Then
2424 l_proj_rev_rate_tab(i) := l_fp_cur_project_rev_rt_tab(i);
2425 End If;
2426 End If;
2427 END LOOP;
2428 /* End of Perf Bug: 3683132 */
2429
2430 --hr_utility.trace('Calling conv_mc_bulk...');
2431 pa_fp_multi_currency_pkg.conv_mc_bulk (
2432 p_resource_assignment_id_tab => l_resource_assignment_id_tab
2433 ,p_start_date_tab => l_start_date_tab
2434 ,p_end_date_tab => l_end_date_tab
2435 ,p_txn_currency_code_tab => l_txn_currency_code_tab
2436 ,p_txn_raw_cost_tab => l_txn_raw_cost_tab
2437 ,p_txn_burdened_cost_tab => l_txn_burdened_cost_tab
2438 ,p_txn_revenue_tab => l_txn_revenue_tab
2439 ,p_projfunc_currency_code_tab => l_projfunc_currency_code_tab
2440 ,p_projfunc_cost_rate_type_tab => l_projfunc_cost_rate_type_tab
2441 ,p_projfunc_cost_rate_tab => l_projfunc_cost_rate_tab
2442 ,p_projfunc_cost_rate_date_tab => l_projfunc_cost_rate_date_tab
2443 ,p_projfunc_rev_rate_type_tab => l_projfunc_rev_rate_type_tab
2444 ,p_projfunc_rev_rate_tab => l_projfunc_rev_rate_tab
2445 ,p_projfunc_rev_rate_date_tab => l_projfunc_rev_rate_date_tab
2446 ,x_projfunc_raw_cost_tab => l_projfunc_raw_cost_tab
2447 ,x_projfunc_burdened_cost_tab => l_projfunc_burdened_cost_tab
2448 ,x_projfunc_revenue_tab => l_projfunc_revenue_tab
2449 ,x_projfunc_rejection_tab => l_projfunc_rejection_tab
2450 ,p_proj_currency_code_tab => l_proj_currency_code_tab
2451 ,p_proj_cost_rate_type_tab => l_proj_cost_rate_type_tab
2452 ,p_proj_cost_rate_tab => l_proj_cost_rate_tab
2453 ,p_proj_cost_rate_date_tab => l_proj_cost_rate_date_tab
2454 ,p_proj_rev_rate_type_tab => l_proj_rev_rate_type_tab
2455 ,p_proj_rev_rate_tab => l_proj_rev_rate_tab
2456 ,p_proj_rev_rate_date_tab => l_proj_rev_rate_date_tab
2457 ,x_proj_raw_cost_tab => l_proj_raw_cost_tab
2458 ,x_proj_burdened_cost_tab => l_proj_burdened_cost_tab
2459 ,x_proj_revenue_tab => l_proj_revenue_tab
2460 ,x_proj_rejection_tab => l_proj_rejection_tab
2461 ,p_user_validate_flag_tab => l_user_validate_flag_tab
2462 ,p_calling_module => p_calling_module -- Added for Bug#5395732
2463 ,x_return_status => l_return_status
2464 ,x_msg_count => l_msg_count
2465 ,x_msg_data => l_msg_data);
2466
2467 l_entire_msg_count := nvl(l_entire_msg_count,0) + nvl(l_msg_count,0);
2468 l_entire_msg_data := l_msg_data;
2469
2470 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2471 l_stage := 1200;
2472 l_entire_return_status := l_return_status;
2473 END IF;
2474
2475
2476 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2477 l_stage := 1300;
2478 IF l_txn_row_id_tab.COUNT > 0 THEN --{
2479 /* Bug fix:4259098: re-calculate the plan cost by adding the actuals to the etc */
2480 FOR i IN l_txn_row_id_tab.FIRST .. l_txn_row_id_tab.LAST LOOP
2481 l_projfunc_raw_cost_tab(i) := NVL(l_projfunc_raw_cost_tab(i),0) + NVL(l_pfc_init_raw_cost_tab(i),0);
2482 If l_projfunc_raw_cost_tab(i) = 0 Then
2483 l_projfunc_raw_cost_tab(i) := null;
2484 End If;
2485
2486 l_projfunc_burdened_cost_tab(i) := NVL(l_projfunc_burdened_cost_tab(i),0) + NVL(l_pfc_init_burden_cost_tab(i),0);
2487 If l_projfunc_burdened_cost_tab(i) = 0 Then
2488 l_projfunc_burdened_cost_tab(i) := NULL;
2489 End If;
2490
2491 l_projfunc_revenue_tab(i) := NVL(l_projfunc_revenue_tab(i),0) + NVL(l_pfc_init_revenue_tab(i),0);
2492 If l_projfunc_revenue_tab(i) = 0 Then
2493 l_projfunc_revenue_tab(i) := NULL;
2494 End If;
2495
2496 l_proj_raw_cost_tab(i) := NVL(l_proj_raw_cost_tab(i),0) + NVL(l_proj_init_raw_cost_tab(i),0);
2497 If l_proj_raw_cost_tab(i) = 0 Then
2498 l_proj_raw_cost_tab(i) := NULL;
2499 End If;
2500
2501 l_proj_burdened_cost_tab(i) := NVL(l_proj_burdened_cost_tab(i),0) + NVL(l_proj_init_burden_cost_tab(i),0);
2502 If l_proj_burdened_cost_tab(i) = 0 Then
2503 l_proj_burdened_cost_tab(i) := NULL;
2504 End If;
2505 l_proj_revenue_tab(i) := NVL(l_proj_revenue_tab(i),0) + NVL(l_proj_init_revenue_tab(i),0);
2506 If l_proj_revenue_tab(i) = 0 Then
2507 l_proj_revenue_tab(i) := NULL;
2508 End If;
2509 END LOOP;
2510 END IF; --}
2511 /* end of bug fix:4259098 */
2512 IF p_entire_version = 'Y' THEN
2513 l_stage := 1400;
2514
2515 L_ROWCOUNT := l_projfunc_currency_code_tab.count;
2516
2517 IF l_rowcount > 0 THEN
2518 --hr_utility.trace('Updating pa_budget_lines...');
2519 FORALL i in 1..l_rowcount
2520 UPDATE pa_budget_lines
2521 SET projfunc_currency_code = l_projfunc_currency_code_tab(i)
2522 ,projfunc_cost_rate_type = l_projfunc_cost_rate_type_tab(i)
2523 ,projfunc_cost_exchange_rate = l_projfunc_cost_rate_tab(i)
2524 ,projfunc_cost_rate_date_type = l_projfunc_cost_rt_dt_typ_tab(i)
2525 ,projfunc_cost_rate_date = DECODE(l_projfunc_cost_rt_dt_typ_tab(i),
2526 'FIXED_DATE',l_projfunc_cost_rate_date_tab(i),
2527 NULL)
2528 ,projfunc_rev_rate_type = l_projfunc_rev_rate_type_tab(i)
2529 ,projfunc_rev_exchange_rate = l_projfunc_rev_rate_tab(i)
2530 ,projfunc_rev_rate_date_type = l_projfunc_rev_rt_dt_typ_tab(i)
2531 ,projfunc_rev_rate_date = DECODE(l_projfunc_rev_rt_dt_typ_tab(i),
2532 'FIXED_DATE',l_projfunc_rev_rate_date_tab(i),
2533 NULL)
2534 ,raw_cost = l_projfunc_raw_cost_tab(i)
2535 ,burdened_cost = l_projfunc_burdened_cost_tab(i)
2536 ,revenue = l_projfunc_revenue_tab(i)
2537 ,pfc_cur_conv_rejection_code = l_projfunc_rejection_tab(i)
2538 ,project_currency_code = l_proj_currency_code_tab(i)
2539 ,project_cost_rate_type = l_proj_cost_rate_type_tab(i)
2540 ,project_cost_exchange_rate = l_proj_cost_rate_tab(i)
2541 ,project_cost_rate_date_type = l_proj_cost_rt_dt_typ_tab(i)
2542 ,project_cost_rate_date = DECODE(l_proj_cost_rt_dt_typ_tab(i),
2543 'FIXED_DATE',l_proj_cost_rate_date_tab(i),
2544 NULL)
2545 ,project_rev_rate_type = l_proj_rev_rate_type_tab(i)
2546 ,project_rev_exchange_rate = l_proj_rev_rate_tab(i)
2547 ,project_rev_rate_date_type = l_proj_rev_rt_dt_typ_tab(i)
2548 ,project_rev_rate_date = DECODE(l_proj_rev_rt_dt_typ_tab(i),
2549 'FIXED_DATE',l_proj_rev_rate_date_tab(i),
2550 NULL)
2551 ,project_raw_cost = l_proj_raw_cost_tab(i)
2552 ,project_burdened_cost = l_proj_burdened_cost_tab(i)
2553 ,project_revenue = l_proj_revenue_tab(i)
2554 ,pc_cur_conv_rejection_code = l_proj_rejection_tab(i)
2555 WHERE rowid = l_txn_row_id_tab(i);
2556 END IF;
2557 ELSE
2558
2559 L_ROWCOUNT := l_projfunc_currency_code_tab.count;
2560
2561 IF l_rowcount > 0 THEN
2562 --hr_utility.trace('Updating pa_fp_rollup_tmp...');
2563 l_stage := 1500;
2564 FORALL i in 1..l_rowcount
2565 UPDATE pa_fp_rollup_tmp
2566 SET projfunc_currency_code = l_projfunc_currency_code_tab(i)
2567 ,projfunc_cost_rate_type = l_projfunc_cost_rate_type_tab(i)
2568 ,projfunc_cost_exchange_rate = l_projfunc_cost_rate_tab(i)
2569 ,projfunc_cost_rate_date_type = l_projfunc_cost_rt_dt_typ_tab(i)
2570 ,projfunc_cost_rate_date = DECODE(l_projfunc_cost_rt_dt_typ_tab(i),
2571 'FIXED_DATE',l_projfunc_cost_rate_date_tab(i),
2572 NULL)
2573 ,projfunc_rev_rate_type = l_projfunc_rev_rate_type_tab(i)
2574 ,projfunc_rev_exchange_rate = l_projfunc_rev_rate_tab(i)
2575 ,projfunc_rev_rate_date_type = l_projfunc_rev_rt_dt_typ_tab(i)
2576 ,projfunc_rev_rate_date = DECODE(l_projfunc_rev_rt_dt_typ_tab(i),
2577 'FIXED_DATE',l_projfunc_rev_rate_date_tab(i),
2578 NULL)
2579 ,projfunc_raw_cost = l_projfunc_raw_cost_tab(i)
2580 ,projfunc_burdened_cost = l_projfunc_burdened_cost_tab(i)
2581 ,projfunc_revenue = l_projfunc_revenue_tab(i)
2582 ,pfc_cur_conv_rejection_code = l_projfunc_rejection_tab(i)
2583 ,project_currency_code = l_proj_currency_code_tab(i)
2584 ,project_cost_rate_type = l_proj_cost_rate_type_tab(i)
2585 ,project_cost_exchange_rate = l_proj_cost_rate_tab(i)
2586 ,project_cost_rate_date_type = l_proj_cost_rt_dt_typ_tab(i)
2587 ,project_cost_rate_date = DECODE(l_proj_cost_rt_dt_typ_tab(i),
2588 'FIXED_DATE',l_proj_cost_rate_date_tab(i),
2589 NULL)
2590 ,project_rev_rate_type = l_proj_rev_rate_type_tab(i)
2591 ,project_rev_exchange_rate = l_proj_rev_rate_tab(i)
2592 ,project_rev_rate_date_type = l_proj_rev_rt_dt_typ_tab(i)
2593 ,project_rev_rate_date = DECODE(l_proj_rev_rt_dt_typ_tab(i),
2594 'FIXED_DATE',l_proj_rev_rate_date_tab(i),
2595 NULL)
2596 ,project_raw_cost = l_proj_raw_cost_tab(i)
2597 ,project_burdened_cost = l_proj_burdened_cost_tab(i)
2598 ,project_revenue = l_proj_revenue_tab(i)
2599 ,pc_cur_conv_rejection_code = l_proj_rejection_tab(i)
2600 WHERE rowid = l_txn_row_id_tab(i);
2601 END IF;
2602 END IF; -- entire_version or not
2603 END IF; -- returned success
2604 END IF; -- rowcount > 0
2605
2606 EXCEPTION WHEN OTHERS THEN
2607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2609 p_data => x_msg_data);
2610 x_msg_count := FND_MSG_PUB.Count_Msg;
2611 IF x_msg_count = 1 THEN
2612 IF x_msg_data IS NOT NULL THEN
2613 FND_MESSAGE.SET_ENCODED (x_msg_data);
2614 x_msg_data := FND_MESSAGE.GET;
2615 END IF;
2616 END IF;
2617
2618 fnd_msg_pub.add_exc_msg
2619 ( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
2620 ,p_procedure_name => 'convert_txn_currency' );
2621 pa_debug.g_err_stage := 'Stage : '||to_char(l_stage)||' '||substr(SQLERRM,1,240);
2622 IF P_PA_DEBUG_MODE = 'Y' THEN
2623 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2624 END IF;
2625 --hr_utility.trace('PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency -- Stage : ' ||to_char(l_stage)||' '||substr(SQLERRM,1,240));
2626 /* bug 4227840: wrapping the setting of debug error stack call to
2627 * pa_debug under the debug enbaling check
2628 */
2629 IF P_PA_DEBUG_MODE = 'Y' THEN
2630 pa_debug.reset_err_stack;
2631 END IF;
2632 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2633 END;
2634
2635 END LOOP;
2636
2637 IF p_entire_version = 'Y' THEN
2638 CLOSE all_budget_lines;
2639 ELSE
2640 CLOSE rollup_lines;
2641 END IF;
2642
2643 x_return_status := l_entire_return_status;
2644 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2645 p_data => x_msg_data);
2646 x_msg_count := FND_MSG_PUB.Count_Msg;
2647 IF x_msg_count = 1 THEN
2648 IF x_msg_data IS NOT NULL THEN
2649 FND_MESSAGE.SET_ENCODED (x_msg_data);
2650 x_msg_data := FND_MESSAGE.GET;
2651 END IF;
2652 END IF;
2653
2654 /* bug 4227840: wrapping the setting of debug error stack call to
2655 * pa_debug under the debug enbaling check
2656 */
2657 IF P_PA_DEBUG_MODE = 'Y' THEN
2658 pa_debug.reset_err_stack;
2659 END IF;
2660
2661 EXCEPTION WHEN OTHERS THEN
2662 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2663 p_data => x_msg_data);
2664 x_msg_count := FND_MSG_PUB.Count_Msg;
2665 IF x_msg_count = 1 THEN
2666 IF x_msg_data IS NOT NULL THEN
2667 FND_MESSAGE.SET_ENCODED (x_msg_data);
2668 x_msg_data := FND_MESSAGE.GET;
2669 END IF;
2670 END IF;
2671
2672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2673 fnd_msg_pub.add_exc_msg
2674 ( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
2675 ,p_procedure_name => 'convert_txn_currency' );
2676 pa_debug.g_err_stage := 'Stage : '||to_char(l_stage)||' '||substr(SQLERRM,1,240);
2677 IF P_PA_DEBUG_MODE = 'Y' THEN
2678 pa_debug.write('convert_txn_currency: ' || g_module_name,pa_debug.g_err_stage,3);
2679 END IF;
2680 --hr_utility.trace('PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency -- Stage : ' ||to_char(l_stage)||' '||substr(SQLERRM,1,240));
2681 /* bug 4227840: wrapping the setting of debug error stack call to
2682 * pa_debug under the debug enbaling check
2683 */
2684 IF P_PA_DEBUG_MODE = 'Y' THEN
2685 pa_debug.reset_err_stack;
2686 END IF;
2687 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2688 END convert_txn_currency;
2689
2690 /*=============================================================================
2691 This api is used to Round budget line amounts as per the currency precision/
2692 MAU (Minimum Accountable Unit). Quantity would be rounded to 5 decimal points.
2693 The api would be called from Copy Version Amounts flow with non-zero adj %
2694 The api is also called Change Order Revenue amount partial implementation.
2695
2696 p_calling_context -> COPY_VERSION, CHANGE_ORDER_MERGE
2697 The parameters p_bls_inserted_after_id will be used only
2698 when p_calling_context is CHANGE_ORDER_MERGE
2699 p_bls_inserted_after_id : This value will be used to find out the budget lines that
2700 got inserted in this flow. All the budget lines with
2701 1. budget line id > p_bls_inserted_after_id AND
2702 2. budget_Version_id = p_budget_version_id
2703 will be considered as inserted in this flow.
2704
2705 Tracking bug No: 4035856 Rravipat Initial creation
2706 ==============================================================================*/
2707
2708 PROCEDURE Round_Budget_Line_Amounts(
2709 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
2710 ,p_bls_inserted_after_id IN pa_budget_lines.budget_line_id%TYPE DEFAULT NULL
2711 ,p_calling_context IN VARCHAR2
2712 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2713 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2714 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2715 AS
2716
2717 -- variables used for debugging
2718 l_return_status VARCHAR2(1);
2719 l_msg_count NUMBER := 0;
2720 l_msg_data VARCHAR2(2000);
2721 l_data VARCHAR2(2000);
2722 l_msg_index_out NUMBER;
2723 l_debug_mode VARCHAR2(30);
2724
2725 -- nested tables to hold amount and currency columns
2726 l_txn_row_id_tab pa_fp_multi_currency_pkg.rowid_type_tab;
2727 l_resource_assignment_id_tab pa_fp_multi_currency_pkg.number_type_tab;
2728 l_start_date_tab pa_fp_multi_currency_pkg.date_type_tab;
2729 l_quantity_tab pa_fp_multi_currency_pkg.number_type_tab;
2730
2731 --Code changes for bug 4200168 starts here.
2732 --l_txn_currency_code_tab pa_fp_multi_currency_pkg.char30_type_tab;
2733 -- l_txn_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2734 --l_txn_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2735 --l_txn_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
2736 l_txn_currency_code_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
2737 l_txn_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2738 l_txn_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2739 l_txn_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2740
2741 /*l_projfunc_currency_code_tab pa_fp_multi_currency_pkg.char30_type_tab;
2742 l_projfunc_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2743 l_projfunc_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2744 l_projfunc_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;
2745 l_proj_currency_code_tab pa_fp_multi_currency_pkg.char30_type_tab;
2746 l_proj_raw_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2747 l_proj_burdened_cost_tab pa_fp_multi_currency_pkg.number_type_tab;
2748 l_proj_revenue_tab pa_fp_multi_currency_pkg.number_type_tab;*/
2749
2750 l_projfunc_currency_code_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
2751 l_proj_currency_code_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
2752
2753 l_projfunc_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2754 l_projfunc_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2755 l_projfunc_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2756
2757 l_proj_raw_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2758 l_proj_burdened_cost_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2759 l_proj_revenue_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2760
2761 --these tables below are introduced to store values when the txn, projfunc and project currency are not equal
2762 -- for any budget line.
2763
2764 l_projfunc_raw_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2765 l_prjfnc_burdened_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2766 l_projfunc_revenue_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2767 l_proj_raw_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2768 l_proj_burdened_cost_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2769 l_proj_revenue_tmp_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2770 l_pfc_tmp_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type(); --store the projfunc currency code.
2771 l_pc_tmp_tab SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type(); -- store the proj currency code.
2772 --Code changes for bug 4200168 ends here.
2773
2774 --Bug 4052403
2775 l_rate_based_flag_tab pa_plsql_datatypes.Char1TabTyp;
2776 l_version_type pa_budget_versions.version_type%TYPE;
2777
2778 -- cursor to fetch budget line amounts
2779 -- Changes in this cursor might have to be done in budget_line_amounts_cur1 also
2780 -- Bug 4052403. Selected rate_based_flag
2781 CURSOR budget_line_amounts_cur IS
2782 SELECT pbl.ROWID, pbl.resource_assignment_id,pbl.start_date, pbl.quantity,
2783 pbl.raw_cost, pbl.burdened_cost, pbl.revenue,pbl.projfunc_currency_code,
2784 pbl.project_raw_cost, pbl.project_burdened_cost, pbl.project_revenue, pbl.project_currency_code,
2785 pbl.txn_raw_cost, pbl.txn_burdened_cost, pbl.txn_revenue, pbl.txn_currency_code, pra.rate_based_flag
2786 FROM pa_budget_lines pbl,
2787 pa_resource_assignments pra
2788 WHERE pbl.budget_version_id = p_budget_version_id
2789 AND pra.resource_assignment_id=pbl.resource_assignment_id
2790 ORDER BY pbl.txn_currency_code;
2791
2792 -- This cursor is same as budget_line_amounts_cur. This will be used in CHANGE_ORDER_MERGE context
2793 -- Changes in this cursor might have to be done in budget_line_amounts_cur also
2794 -- Bug 4052403. Selected rate_based_flag
2795 CURSOR budget_line_amounts_cur1
2796 IS
2797 SELECT pbl.ROWID, pbl.resource_assignment_id,pbl.start_date, pbl.quantity,
2798 pbl.raw_cost, pbl.burdened_cost, pbl.revenue,pbl.projfunc_currency_code,
2799 pbl.project_raw_cost, pbl.project_burdened_cost, pbl.project_revenue, pbl.project_currency_code,
2800 pbl.txn_raw_cost, pbl.txn_burdened_cost, pbl.txn_revenue, pbl.txn_currency_code, pra.rate_based_flag
2801 FROM pa_budget_lines pbl,
2802 pa_resource_assignments pra
2803 WHERE pbl.budget_version_id = p_budget_version_id
2804 AND pra.resource_assignment_id=pbl.resource_assignment_id
2805 AND budget_line_id > p_bls_inserted_after_id
2806 ORDER BY pbl.txn_currency_code;
2807
2808 BEGIN
2809
2810 x_msg_count := 0;
2811 x_return_status := FND_API.G_RET_STS_SUCCESS;
2812
2813 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2814 l_debug_mode := NVL(l_debug_mode, 'Y');
2815
2816 /* bug 4227840: wrapping the setting of debug error stack call to
2817 * pa_debug under the debug enbaling check
2818 */
2819 IF l_debug_mode = 'Y' THEN
2820 -- set curr function
2821 pa_debug.set_curr_function(
2822 p_function =>'PA_FP_MULTI_CURRENCY_PKG.Round_Budget_Line_Amounts'
2823 ,p_debug_mode => l_debug_mode );
2824 END IF;
2825
2826 -- check for business rules violations
2827 IF l_debug_mode = 'Y' THEN
2828 pa_debug.g_err_stage:='Validating input parameters';
2829 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,3);
2830 END IF;
2831
2832 IF (p_budget_version_id IS NULL) OR (p_calling_context IS NULL) OR
2833 (p_calling_context NOT IN ('COPY_VERSION','CHANGE_ORDER_MERGE'))
2834 THEN
2835
2836 IF l_debug_mode = 'Y' THEN
2837 pa_debug.g_err_stage:='p_budget_version_id = '||p_budget_version_id;
2838 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
2839
2840 pa_debug.g_err_stage:='p_calling_context = '||p_calling_context;
2841 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
2842 END IF;
2843
2844 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2845 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2846 p_token1 => 'PROCEDURENAME',
2847 p_value1 => 'PA_FP_MULTI_CURRENCY_PKG.Round_Budget_Line_Amounts',
2848 p_token2 => 'STAGE',
2849 p_value2 => 'p_budget_version_id '||p_budget_version_id ||' p_calling_context '||p_calling_context );
2850
2851 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2852
2853 END IF;
2854
2855 IF p_calling_context='CHANGE_ORDER_MERGE' AND
2856 nvl(p_bls_inserted_after_id,0) <= 0 THEN
2857
2858 IF l_debug_mode = 'Y' THEN
2859
2860 pa_debug.g_err_stage:='p_bls_inserted_after_id = '||p_bls_inserted_after_id;
2861 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
2862
2863 END IF;
2864
2865 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2866 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2867 p_token1 => 'PROCEDURENAME',
2868 p_value1 => 'PA_FP_MULTI_CURRENCY_PKG.Round_Budget_Line_Amounts',
2869 p_token2 => 'STAGE',
2870 p_value2 => 'p_bls_inserted_after_id '||p_bls_inserted_after_id);
2871
2872
2873 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2874
2875 END IF;
2876
2877 --Bug 4052403. Select the version type of the budget version for which the API is called
2878 SELECT version_type
2879 INTO l_version_type
2880 FROM pa_budget_versions
2881 WHERE budget_version_id=p_budget_version_id;
2882
2883 -- open and fetch PC,PFC and txn cur amounts of the budget version
2884 IF p_calling_context='CHANGE_ORDER_MERGE' THEN
2885
2886 OPEN budget_line_amounts_cur1;
2887
2888 ELSE
2889
2890 OPEN budget_line_amounts_cur;
2891
2892 END IF;
2893
2894 LOOP
2895
2896 l_txn_row_id_tab.delete;
2897 l_resource_assignment_id_tab.delete;
2898 l_start_date_tab.delete;
2899 l_quantity_tab.delete;
2900 l_projfunc_raw_cost_tab.delete;
2901 l_projfunc_burdened_cost_tab.delete;
2902 l_projfunc_revenue_tab.delete;
2903 l_projfunc_currency_code_tab.delete;
2904 l_proj_raw_cost_tab.delete;
2905 l_proj_burdened_cost_tab.delete;
2906 l_proj_revenue_tab.delete;
2907 l_proj_currency_code_tab.delete;
2908 l_txn_raw_cost_tab.delete;
2909 l_txn_burdened_cost_tab.delete;
2910 l_txn_revenue_tab.delete;
2911 l_txn_currency_code_tab.delete;
2912 l_rate_based_flag_tab.delete;--Bug 4052403
2913 l_projfunc_raw_cost_tmp_tab.delete; -- Added for bug 4290451.
2914 l_prjfnc_burdened_cost_tmp_tab.delete; -- Added for bug 4290451.
2915 l_projfunc_revenue_tmp_tab.delete; -- Added for bug 4290451.
2916 l_proj_raw_cost_tmp_tab.delete; -- Added for bug 4290451.
2917 l_proj_burdened_cost_tmp_tab.delete; -- Added for bug 4290451.
2918 l_proj_revenue_tmp_tab.delete; -- Added for bug 4290451.
2919 l_pfc_tmp_tab.delete; -- Added for bug 4290451.
2920 l_pc_tmp_tab.delete; -- Added for bug 4290451.
2921
2922
2923 IF p_calling_context='CHANGE_ORDER_MERGE' THEN
2924
2925 FETCH budget_line_amounts_cur1
2926 BULK COLLECT INTO
2927 l_txn_row_id_tab
2928 ,l_resource_assignment_id_tab
2929 ,l_start_date_tab
2930 ,l_quantity_tab
2931 ,l_projfunc_raw_cost_tab
2932 ,l_projfunc_burdened_cost_tab
2933 ,l_projfunc_revenue_tab
2934 ,l_projfunc_currency_code_tab
2935 ,l_proj_raw_cost_tab
2936 ,l_proj_burdened_cost_tab
2937 ,l_proj_revenue_tab
2938 ,l_proj_currency_code_tab
2939 ,l_txn_raw_cost_tab
2940 ,l_txn_burdened_cost_tab
2941 ,l_txn_revenue_tab
2942 ,l_txn_currency_code_tab
2943 ,l_rate_based_flag_tab;
2944 /* Bug fix: 4204134 LIMIT 1000; */
2945
2946 ELSE
2947
2948 FETCH budget_line_amounts_cur
2949 BULK COLLECT INTO
2950 l_txn_row_id_tab
2951 ,l_resource_assignment_id_tab
2952 ,l_start_date_tab
2953 ,l_quantity_tab
2954 ,l_projfunc_raw_cost_tab
2955 ,l_projfunc_burdened_cost_tab
2956 ,l_projfunc_revenue_tab
2957 ,l_projfunc_currency_code_tab
2958 ,l_proj_raw_cost_tab
2959 ,l_proj_burdened_cost_tab
2960 ,l_proj_revenue_tab
2961 ,l_proj_currency_code_tab
2962 ,l_txn_raw_cost_tab
2963 ,l_txn_burdened_cost_tab
2964 ,l_txn_revenue_tab
2965 ,l_txn_currency_code_tab
2966 ,l_rate_based_flag_tab ;
2967 /* Bug fix: 4204134 LIMIT 1000; */
2968
2969 END IF;
2970
2971 -- exit if there are no rows to be processed
2972 EXIT WHEN l_txn_row_id_tab.count = 0;
2973
2974 --Code changes for bug 4200168 starts here.
2975 l_txn_raw_cost_tab :=
2976 Pa_currency.round_currency_amt_nested_blk(l_txn_raw_cost_tab,
2977 l_txn_currency_code_tab);
2978 l_txn_burdened_cost_tab :=
2979 Pa_currency.round_currency_amt_nested_blk(l_txn_burdened_cost_tab,
2980 l_txn_currency_code_tab);
2981 l_txn_revenue_tab :=
2982 Pa_currency.round_currency_amt_nested_blk(l_txn_revenue_tab,
2983 l_txn_currency_code_tab);
2984 --Code changes for bug 4200168 ends here.
2985
2986 --PC and PFC amounts will be derived/rounded by the Multi Currency API when the context is
2987 --COPY_VERSION. MC API will be called from the calling API in this context
2988 IF p_calling_context <> 'COPY_VERSION' THEN
2989
2990 -- If PFC currency is equal to txn currency copy the rounded txn amount
2991 -- as PFC amount else call rounding util api
2992 FOR i IN l_txn_row_id_tab.first .. l_txn_row_id_tab.last
2993 LOOP
2994
2995 --Code addition for bug#4290451.starts here.
2996 l_projfunc_raw_cost_tmp_tab.extend;
2997 l_prjfnc_burdened_cost_tmp_tab.extend;
2998 l_projfunc_revenue_tmp_tab.extend;
2999 l_proj_raw_cost_tmp_tab.extend;
3000 l_proj_burdened_cost_tmp_tab.extend;
3001 l_proj_revenue_tmp_tab.extend;
3002 l_pfc_tmp_tab.extend;
3003 l_pc_tmp_tab.extend;
3004 --Code addition for bug#4290451.ends here.
3005
3006 --Code changes for bug 4200168 starts here.
3007 IF l_txn_currency_code_tab(i) = l_projfunc_currency_code_tab(i) THEN
3008 l_projfunc_raw_cost_tab(i) := l_txn_raw_cost_tab(i);
3009 l_projfunc_burdened_cost_tab(i) := l_txn_burdened_cost_tab(i);
3010 l_projfunc_revenue_tab(i) := l_txn_revenue_tab(i);
3011 --Added for bug 4290451
3012 l_projfunc_raw_cost_tmp_tab(i) := l_txn_raw_cost_tab(i);
3013 l_prjfnc_burdened_cost_tmp_tab(i) := l_txn_burdened_cost_tab(i);
3014 l_projfunc_revenue_tmp_tab(i) :=l_txn_revenue_tab(i);
3015 l_pfc_tmp_tab(i) := l_projfunc_currency_code_tab(i);
3016 ELSE
3017 l_projfunc_raw_cost_tmp_tab(i) := l_projfunc_raw_cost_tab(i) ;
3018 l_prjfnc_burdened_cost_tmp_tab(i) := l_projfunc_burdened_cost_tab(i) ;
3019 l_projfunc_revenue_tmp_tab(i) := l_projfunc_revenue_tab(i) ;
3020 l_pfc_tmp_tab(i) := l_projfunc_currency_code_tab(i);
3021 END IF;
3022
3023 IF l_proj_currency_code_tab(i) = l_projfunc_currency_code_tab(i) THEN
3024 l_proj_raw_cost_tab(i) := l_projfunc_raw_cost_tab(i);
3025 l_proj_burdened_cost_tab(i) := l_projfunc_burdened_cost_tab(i);
3026 l_proj_revenue_tab(i) := l_projfunc_revenue_tab(i);
3027 --Added for bug 4290451
3028 l_proj_raw_cost_tmp_tab(i) := l_projfunc_raw_cost_tab(i);
3029 l_proj_burdened_cost_tmp_tab(i) := l_projfunc_burdened_cost_tab(i);
3030 l_proj_revenue_tmp_tab(i) := l_projfunc_revenue_tab(i);
3031 l_pc_tmp_tab(i) := l_proj_currency_code_tab(i);
3032 ELSIF l_proj_currency_code_tab(i) = l_txn_currency_code_tab(i) THEN
3033 l_proj_raw_cost_tab(i) := l_txn_raw_cost_tab(i);
3034 l_proj_burdened_cost_tab(i) := l_txn_burdened_cost_tab(i);
3035 l_proj_revenue_tab(i) := l_txn_revenue_tab(i);
3036 --Added for bug 4290451
3037 l_proj_raw_cost_tmp_tab(i) := l_txn_raw_cost_tab(i);
3038 l_proj_burdened_cost_tmp_tab(i) := l_txn_burdened_cost_tab(i);
3039 l_proj_revenue_tmp_tab(i) := l_txn_revenue_tab(i);
3040 l_pc_tmp_tab(i) := l_proj_currency_code_tab(i);
3041 ELSE
3042 l_proj_raw_cost_tmp_tab(i) := l_proj_raw_cost_tab(i);
3043 l_proj_burdened_cost_tmp_tab(i) := l_proj_burdened_cost_tab(i);
3044 l_proj_revenue_tmp_tab(i) := l_proj_revenue_tab(i);
3045 l_pc_tmp_tab(i) := l_proj_currency_code_tab(i);
3046 END IF;
3047 END LOOP;
3048
3049 l_projfunc_raw_cost_tmp_tab :=
3050 Pa_currency.round_currency_amt_nested_blk(l_projfunc_raw_cost_tmp_tab,
3051 l_pfc_tmp_tab);
3052 l_prjfnc_burdened_cost_tmp_tab :=
3053 Pa_currency.round_currency_amt_nested_blk(l_prjfnc_burdened_cost_tmp_tab,
3054 l_pfc_tmp_tab);
3055 l_projfunc_revenue_tmp_tab :=
3056 Pa_currency.round_currency_amt_nested_blk(l_projfunc_revenue_tmp_tab,
3057 l_pfc_tmp_tab);
3058
3059 l_proj_raw_cost_tmp_tab :=
3060 Pa_currency.round_currency_amt_nested_blk(l_proj_raw_cost_tmp_tab,
3061 l_pc_tmp_tab);
3062 l_proj_burdened_cost_tmp_tab :=
3063 Pa_currency.round_currency_amt_nested_blk(l_proj_burdened_cost_tmp_tab,
3064 l_pc_tmp_tab);
3065 l_proj_revenue_tmp_tab :=
3066 Pa_currency.round_currency_amt_nested_blk(l_proj_revenue_tmp_tab,
3067 l_pc_tmp_tab);
3068
3069
3070 IF l_projfunc_raw_cost_tmp_tab.FIRST > 0 OR l_proj_raw_cost_tmp_tab.FIRST> 0 THEN
3071
3072 FOR i IN l_txn_row_id_tab.first .. l_txn_row_id_tab.last
3073 LOOP
3074 IF i BETWEEN l_projfunc_raw_cost_tmp_tab.FIRST AND l_projfunc_raw_cost_tmp_tab.LAST THEN
3075 l_projfunc_raw_cost_tab(i) := l_projfunc_raw_cost_tmp_tab(i);
3076 l_projfunc_burdened_cost_tab(i) := l_prjfnc_burdened_cost_tmp_tab(i);
3077 l_projfunc_revenue_tab(i) := l_projfunc_revenue_tmp_tab(i);
3078 END IF;
3079 IF i BETWEEN l_proj_raw_cost_tmp_tab.FIRST AND l_proj_raw_cost_tmp_tab.LAST THEN
3080 l_proj_raw_cost_tab(i) := l_proj_raw_cost_tmp_tab(i);
3081 l_proj_burdened_cost_tab(i) := l_proj_burdened_cost_tmp_tab(i);
3082 l_proj_revenue_tab(i) := l_proj_revenue_tmp_tab(i);
3083 END IF;
3084 END LOOP;
3085 END IF;
3086 --Code changes for bug 4200168 ends here.
3087 END IF; --IF p_calling_context <> 'COPY_VERSION' THEN
3088
3089 -- Update pa_budget_lines with the rounded amounts
3090 --Bug 4052403. In the Updates below, made sure that for non rate-based planning transactions, the quanity is updated to
3091 --raw cost/revenue as the case may be
3092 IF p_calling_context = 'COPY_VERSION' THEN
3093 -- Update txn currency amounts well, PC and PFC amounts would be null at this point of time
3094 -- Stamp cost rate, burden cost rate and bill rate as overrides
3095 FORALL i in l_txn_row_id_tab.first .. l_txn_row_id_tab.last
3096 UPDATE PA_BUDGET_LINES
3097 SET --While deriving the override rates below, the expression used here for calculating quantity is used.
3098 --Hence whenever this derivation is changed, the change has to be reflected below in override rate
3099 --derivation also
3100 quantity = Decode(l_rate_based_flag_tab(i),
3101 'N',Decode(l_version_type,
3102 'REVENUE',l_txn_revenue_tab(i),
3103 l_txn_raw_cost_tab(i)),
3104 round(l_quantity_tab(i),5))
3105 ,txn_raw_cost = l_txn_raw_cost_tab(i)
3106 ,txn_burdened_cost = l_txn_burdened_cost_tab(i)
3107 ,txn_revenue = l_txn_revenue_tab(i)
3108 ,txn_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3109 'N',Decode(l_version_type,
3110 'REVENUE',l_txn_revenue_tab(i),
3111 l_txn_raw_cost_tab(i)),
3112 round(l_quantity_tab(i),5)),
3113 null, null,
3114 0,0,
3115 l_txn_raw_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3116 'N',Decode(l_version_type,
3117 'REVENUE',l_txn_revenue_tab(i),
3118 l_txn_raw_cost_tab(i)),
3119 round(l_quantity_tab(i),5))))
3120 ,burden_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3121 'N',Decode(l_version_type,
3122 'REVENUE',l_txn_revenue_tab(i),
3123 l_txn_raw_cost_tab(i)),
3124 round(l_quantity_tab(i),5)),
3125 null, null,
3126 0,0,
3127 l_txn_burdened_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3128 'N',Decode(l_version_type,
3129 'REVENUE',l_txn_revenue_tab(i),
3130 l_txn_raw_cost_tab(i)),
3131 round(l_quantity_tab(i),5))))
3132 ,txn_bill_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3133 'N',Decode(l_version_type,
3134 'REVENUE',l_txn_revenue_tab(i),
3135 l_txn_raw_cost_tab(i)),
3136 round(l_quantity_tab(i),5)),
3137 null, null,
3138 0,0,
3139 l_txn_revenue_tab(i)/(Decode(l_rate_based_flag_tab(i),
3140 'N',Decode(l_version_type,
3141 'REVENUE',l_txn_revenue_tab(i),
3142 l_txn_raw_cost_tab(i)),
3143 round(l_quantity_tab(i),5))))
3144 WHERE rowid = l_txn_row_id_tab(i);
3145 ELSIF p_calling_context = 'CHANGE_ORDER_MERGE' THEN
3146 -- Update TXN,PFC,PC amounts Stampe cost rate, budrden cost rate and bill rate as overrides
3147 -- If any line is affected by change order merge, then cur conv rate type is stamped as 'USER'
3148 -- All other lines would have rounded amounts any way and the program should not alter them
3149 -- So only exchange rate column is updated for those lines with 'USER' as the rate type
3150 FORALL i in l_txn_row_id_tab.first .. l_txn_row_id_tab.last
3151 UPDATE PA_BUDGET_LINES
3152 SET quantity = 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 ,raw_cost = l_projfunc_raw_cost_tab(i)
3158 ,burdened_cost = l_projfunc_burdened_cost_tab(i)
3159 ,revenue = l_projfunc_revenue_tab(i)
3160 ,project_raw_cost = l_proj_raw_cost_tab(i)
3161 ,project_burdened_cost = l_proj_burdened_cost_tab(i)
3162 ,project_revenue = l_proj_revenue_tab(i)
3163 ,txn_raw_cost = l_txn_raw_cost_tab(i)
3164 ,txn_burdened_cost = l_txn_burdened_cost_tab(i)
3165 ,txn_revenue = l_txn_revenue_tab(i)
3166 ,txn_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3167 'N',Decode(l_version_type,
3168 'REVENUE',l_txn_revenue_tab(i),
3169 l_txn_raw_cost_tab(i)),
3170 round(l_quantity_tab(i),5)),
3171 null, null,
3172 0,0,
3173 l_txn_raw_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3174 'N',Decode(l_version_type,
3175 'REVENUE',l_txn_revenue_tab(i),
3176 l_txn_raw_cost_tab(i)),
3177 round(l_quantity_tab(i),5))))
3178 ,burden_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3179 'N',Decode(l_version_type,
3180 'REVENUE',l_txn_revenue_tab(i),
3181 l_txn_raw_cost_tab(i)),
3182 round(l_quantity_tab(i),5)),
3183 null, null,
3184 0,0,
3185 l_txn_burdened_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
3186 'N',Decode(l_version_type,
3187 'REVENUE',l_txn_revenue_tab(i),
3188 l_txn_raw_cost_tab(i)),
3189 round(l_quantity_tab(i),5))))
3190 ,txn_bill_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
3191 'N',Decode(l_version_type,
3192 'REVENUE',l_txn_revenue_tab(i),
3193 l_txn_raw_cost_tab(i)),
3194 round(l_quantity_tab(i),5)),
3195 null, null,
3196 0,0,
3197 l_txn_revenue_tab(i)/(Decode(l_rate_based_flag_tab(i),
3198 'N',Decode(l_version_type,
3199 'REVENUE',l_txn_revenue_tab(i),
3200 l_txn_raw_cost_tab(i)),
3201 round(l_quantity_tab(i),5))))
3202 ,projfunc_rev_exchange_rate = Decode(projfunc_rev_rate_type, 'User',
3203 Decode(l_txn_revenue_tab(i), null, null, 0,0, l_projfunc_revenue_tab(i)/l_txn_revenue_tab(i))
3204 ,null)
3205 ,project_cost_exchange_rate = Decode(project_cost_rate_type, 'User',
3206 Decode(l_txn_raw_cost_tab(i), null, null, 0,0, l_proj_raw_cost_tab(i)/l_txn_raw_cost_tab(i))
3207 ,null)
3208 ,project_rev_exchange_rate = Decode(project_rev_rate_type, 'User',
3209 Decode(l_txn_revenue_tab(i), null, null, 0,0, l_proj_revenue_tab(i)/l_txn_revenue_tab(i))
3210 ,null)
3211 WHERE rowid = l_txn_row_id_tab(i);
3212
3213 END IF;
3214
3215 END LOOP; -- Budget line amounts Cur loop
3216 IF p_calling_context='CHANGE_ORDER_MERGE' THEN
3217
3218 CLOSE budget_line_amounts_cur1;
3219
3220 ELSE
3221
3222 CLOSE budget_line_amounts_cur;
3223
3224 END IF;
3225
3226 IF l_debug_mode = 'Y' THEN
3227 pa_debug.g_err_stage:='Exiting Round_Budget_Line_Amounts';
3228 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,3);
3229 END IF;
3230
3231 /* bug 4227840: wrapping the setting of debug error stack call to
3232 * pa_debug under the debug enbaling check
3233 */
3234 IF l_debug_mode = 'Y' THEN
3235 -- reset curr function
3236 pa_debug.reset_curr_function();
3237 END IF;
3238
3239 EXCEPTION
3240
3241 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3242 l_msg_count := FND_MSG_PUB.count_msg;
3243 IF l_msg_count = 1 THEN
3244 PA_INTERFACE_UTILS_PUB.get_messages
3245 (p_encoded => FND_API.G_TRUE
3246 ,p_msg_index => 1
3247 ,p_msg_count => l_msg_count
3248 ,p_msg_data => l_msg_data
3249 ,p_data => l_data
3250 ,p_msg_index_out => l_msg_index_out);
3251
3252 x_msg_data := l_data;
3253 x_msg_count := l_msg_count;
3254 ELSE
3255 x_msg_count := l_msg_count;
3256 END IF;
3257
3258 x_return_status := FND_API.G_RET_STS_ERROR;
3259
3260 IF l_debug_mode = 'Y' THEN
3261 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
3262 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
3263
3264 END IF;
3265 /* bug 4227840: wrapping the setting of debug error stack call to
3266 * pa_debug under the debug enbaling check
3267 */
3268 IF l_debug_mode = 'Y' THEN
3269 -- reset curr function
3270 pa_debug.reset_curr_function();
3271 END IF;
3272 RETURN;
3273 WHEN Others THEN
3274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3275 x_msg_count := 1;
3276 x_msg_data := SQLERRM;
3277
3278 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
3279 ,p_procedure_name => 'Round_Budget_Line_Amounts');
3280
3281 IF l_debug_mode = 'Y' THEN
3282 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3283 pa_debug.write('Round_Budget_Line_Amounts: ' || g_module_name,pa_debug.g_err_stage,5);
3284 END IF;
3285
3286 /* bug 4227840: wrapping the setting of debug error stack call to
3287 * pa_debug under the debug enbaling check
3288 */
3289 IF l_debug_mode = 'Y' THEN
3290 -- reset curr function
3291 pa_debug.Reset_Curr_Function();
3292 END IF;
3293 RAISE;
3294 END Round_Budget_Line_Amounts;
3295
3296 -->This API is written as part of rounding changes. This API will be called from PAFPCIMB.implement_ci_into_single_ver
3297 -->API when partial implementation happens.
3298 ---->p_agr_currency_code,p_project_currency_code and p_projfunc_currency_code should be valid and not null
3299 ---->All the p_...tbl input parameters should have same no. of elemeents
3300 ---->p_txn...tbls will be rounded based on p_agr_currency_code, p_project_...tbls will be rounded based on
3301 --p_project_currency_code and p_projfunc_...tbls will be rounded based on p_projfunc_currency_code
3302 ---->px_quantity_tbl will be rounded to have max 5 digits after decimal point
3303 PROCEDURE round_amounts
3304 ( px_quantity_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3305 ,p_agr_currency_code IN OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
3306 ,px_txn_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3307 ,px_txn_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3308 ,px_txn_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3309 ,p_project_currency_code IN OUT NOCOPY pa_budget_lines.project_currency_code%TYPE --File.Sql.39 bug 4440895
3310 ,px_project_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3311 ,px_project_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3312 ,px_project_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3313 ,p_projfunc_currency_code IN OUT NOCOPY pa_budget_lines.projfunc_currency_code%TYPE --File.Sql.39 bug 4440895
3314 ,px_projfunc_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3315 ,px_projfunc_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3316 ,px_projfunc_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
3317 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3318 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3319 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3320 IS
3321 --Start of variables used for debugging
3322 l_return_status VARCHAR2(1);
3323 l_msg_count NUMBER := 0;
3324 l_msg_data VARCHAR2(2000);
3325 l_data VARCHAR2(2000);
3326 l_msg_index_out NUMBER;
3327 l_debug_mode VARCHAR2(30);
3328 l_debug_level3 CONSTANT NUMBER :=3;
3329 l_debug_level5 CONSTANT NUMBER :=5;
3330 l_module_name VARCHAR2(200) := 'PAFPMCPB.round_amounts';
3331
3332 --Code changes for bug 4200168 starts here.
3333 l_agr_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
3334 l_project_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
3335 l_projfunc_currency_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
3336 --Code changes for bug 4200168 ends here.
3337
3338 BEGIN
3339 x_msg_count := 0;
3340 x_return_status := FND_API.G_RET_STS_SUCCESS;
3341
3342 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3343 l_debug_mode := NVL(l_debug_mode, 'Y');
3344
3345 /* bug 4227840: wrapping the setting of debug error stack call to
3346 * pa_debug under the debug enbaling check
3347 */
3348 IF l_debug_mode = 'Y' THEN
3349 -- Set curr function
3350 pa_debug.set_curr_function(
3351 p_function =>'pafpmcpb.round_amounts'
3352 ,p_debug_mode => l_debug_mode );
3353 END IF;
3354
3355 IF l_debug_mode = 'Y' THEN
3356 pa_debug.g_err_stage:='Validating input parameters';
3357 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3358 END IF;
3359
3360 --Currency codes should not be valid
3361 IF p_agr_currency_code IS NULL OR
3362 p_project_currency_code IS NULL OR
3363 p_projfunc_currency_code IS NULL THEN
3364
3365 IF l_debug_mode = 'Y' THEN
3366
3367 pa_debug.g_err_stage:='p_agr_currency_code '||p_agr_currency_code;
3368 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3369
3370 pa_debug.g_err_stage:='p_project_currency_code '||p_project_currency_code;
3371 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3372
3373 pa_debug.g_err_stage:='p_projfunc_currency_code '||p_projfunc_currency_code;
3374 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3375
3376 END IF;
3377
3378 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3379 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
3380 p_token1 => 'PROCEDURENAME',
3381 p_value1 => 'PAFPMCPB.ROUND_AMOUNTS',
3382 p_token2 => 'STAGE',
3383 p_value2 => 'Currency Codes are Invalid');
3384
3385 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3386
3387 END IF;
3388
3389 --All the amount tbls should be equal in length
3390 IF px_txn_raw_cost_tbl.COUNT <> px_txn_burdened_cost_tbl.COUNT OR
3391 px_txn_raw_cost_tbl.COUNT <> px_txn_revenue_tbl.COUNT OR
3392 px_txn_raw_cost_tbl.COUNT <> px_project_raw_cost_tbl.COUNT OR
3393 px_txn_raw_cost_tbl.COUNT <> px_project_burdened_cost_tbl.COUNT OR
3394 px_txn_raw_cost_tbl.COUNT <> px_project_revenue_tbl.COUNT OR
3395 px_txn_raw_cost_tbl.COUNT <> px_projfunc_raw_cost_tbl.COUNT OR
3396 px_txn_raw_cost_tbl.COUNT <> px_projfunc_burdened_cost_tbl.COUNT OR
3397 px_txn_raw_cost_tbl.COUNT <> px_projfunc_revenue_tbl.COUNT OR
3398 px_txn_raw_cost_tbl.COUNT <> px_quantity_tbl.COUNT THEN
3399
3400 IF l_debug_mode = 'Y' THEN
3401
3402 pa_debug.g_err_stage:='px_txn_raw_cost_tbl.COUNT '||px_txn_raw_cost_tbl.COUNT;
3403 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3404
3405 pa_debug.g_err_stage:='px_txn_burdened_cost_tbl.COUNT '||px_txn_burdened_cost_tbl.COUNT;
3406 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3407
3408 pa_debug.g_err_stage:='px_txn_revenue_tbl.COUNT '||px_txn_revenue_tbl.COUNT;
3409 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3410
3411 pa_debug.g_err_stage:='px_project_raw_cost_tbl.COUNT '||px_project_raw_cost_tbl.COUNT;
3412 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3413
3414 pa_debug.g_err_stage:='px_project_burdened_cost_tbl.COUNT '||px_project_burdened_cost_tbl.COUNT;
3415 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3416
3417 pa_debug.g_err_stage:='px_project_revenue_tbl.COUNT '||px_project_revenue_tbl.COUNT;
3418 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3419
3420 pa_debug.g_err_stage:='px_projfunc_raw_cost_tbl.COUNT '||px_projfunc_raw_cost_tbl.COUNT;
3421 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3422
3423 pa_debug.g_err_stage:='px_projfunc_burdened_cost_tbl.COUNT '||px_projfunc_burdened_cost_tbl.COUNT;
3424 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3425
3426 pa_debug.g_err_stage:='px_projfunc_revenue_tbl.COUNT '||px_projfunc_revenue_tbl.COUNT;
3427 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3428
3429 END IF;
3430
3431 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3432 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
3433 p_token1 => 'PROCEDURENAME',
3434 p_value1 => 'PAFPMCPB.ROUND_AMOUNTS',
3435 p_token2 => 'STAGE',
3436 p_value2 => 'Amount tbls are inconsistent');
3437
3438 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3439
3440 END IF;
3441
3442 IF px_txn_raw_cost_tbl.COUNT=0 THEN
3443
3444 IF l_debug_mode = 'Y' THEN
3445
3446 pa_debug.g_err_stage:='Input tbls are empty. Returning';
3447 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3448
3449 END IF;
3450 /* bug 4227840: wrapping the setting of debug error stack call to
3451 * pa_debug under the debug enbaling check
3452 */
3453 IF l_debug_mode = 'Y' THEN
3454 pa_debug.reset_curr_function;
3455 END IF;
3456 RETURN;
3457
3458 END IF;
3459
3460 --Round the quantity
3461 IF l_debug_mode = 'Y' THEN
3462 pa_debug.g_err_stage:='Rounding Quantity';
3463 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3464 END IF;
3465
3466 FOR i IN 1..px_quantity_tbl.COUNT LOOP
3467
3468 IF px_quantity_tbl(i)<>0 THEN
3469
3470 px_quantity_tbl(i):=round(px_quantity_tbl(i),5);
3471
3472 END IF;
3473
3474 END LOOP;
3475
3476 --In 3 for loops written below all the txn/pc/pfc amounts will be rounded. 3 For loops are written to take advantage
3477 --of caching logic in Pa_currency.round_trans_currency_amt1
3478
3479 --Round the agr currency amounts.
3480 IF l_debug_mode = 'Y' THEN
3481 pa_debug.g_err_stage:='Rounding agr amounts';
3482 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3483 END IF;
3484
3485 --Code changes for bug 4200168 starts here.
3486
3487 FOR i IN 1..px_txn_raw_cost_tbl.COUNT LOOP
3488 l_agr_currency_code_tbl.extend; --added for bug#4290451
3489
3490 l_agr_currency_code_tbl(i) := p_agr_currency_code;
3491
3492
3493 END LOOP;
3494
3495 px_txn_raw_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_txn_raw_cost_tbl,
3496 l_agr_currency_code_tbl);
3497
3498 px_txn_burdened_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_txn_burdened_cost_tbl,
3499 l_agr_currency_code_tbl);
3500
3501 px_txn_revenue_tbl := Pa_currency.round_currency_amt_nested_blk(px_txn_revenue_tbl,
3502 l_agr_currency_code_tbl);
3503 --Code changes for bug 4200168 ends here.
3504
3505
3506
3507 --Round the project currency amounts.
3508 IF l_debug_mode = 'Y' THEN
3509 pa_debug.g_err_stage:='Rounding project amounts';
3510 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3511 END IF;
3512
3513 IF p_agr_currency_code=p_project_currency_code THEN
3514
3515 px_project_raw_cost_tbl := px_txn_raw_cost_tbl ;
3516 px_project_burdened_cost_tbl := px_txn_burdened_cost_tbl;
3517 px_project_revenue_tbl := px_txn_revenue_tbl ;
3518
3519 ELSE
3520 --Code changes for bug 4200168 starts here.
3521 FOR i IN 1..px_project_raw_cost_tbl.COUNT LOOP
3522 l_project_currency_code_tbl.extend; --added for bug#4290451
3523 l_project_currency_code_tbl(i) := p_project_currency_code;
3524 END LOOP;
3525
3526 px_project_raw_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_project_raw_cost_tbl,
3527 l_project_currency_code_tbl);
3528
3529 px_project_burdened_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_project_burdened_cost_tbl,
3530 l_project_currency_code_tbl);
3531
3532 px_project_revenue_tbl := Pa_currency.round_currency_amt_nested_blk(px_project_revenue_tbl,
3533 l_project_currency_code_tbl);
3534 --Code changes for bug 4200168 starts here.
3535 END IF;
3536
3537 --Round the Project Functional Currency Amounts
3538 IF l_debug_mode = 'Y' THEN
3539 pa_debug.g_err_stage:='Rounding project functional amounts';
3540 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3541 END IF;
3542
3543 IF p_agr_currency_code=p_projfunc_currency_code THEN
3544
3545 px_projfunc_raw_cost_tbl := px_txn_raw_cost_tbl ;
3546 px_projfunc_burdened_cost_tbl := px_txn_burdened_cost_tbl;
3547 px_projfunc_revenue_tbl := px_txn_revenue_tbl ;
3548
3549 ELSIF p_project_currency_code=p_projfunc_currency_code THEN
3550
3551 px_projfunc_raw_cost_tbl := px_project_raw_cost_tbl ;
3552 px_projfunc_burdened_cost_tbl := px_project_burdened_cost_tbl;
3553 px_projfunc_revenue_tbl := px_project_revenue_tbl ;
3554
3555 ELSE
3556 --Code changes for bug 4200168 starts here.
3557 FOR i IN 1..px_projfunc_raw_cost_tbl.COUNT LOOP
3558 l_projfunc_currency_code_tbl.extend; --added for bug#4290451
3559 l_projfunc_currency_code_tbl(i) := p_projfunc_currency_code;
3560 END LOOP;
3561 px_projfunc_raw_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_projfunc_raw_cost_tbl,
3562 l_projfunc_currency_code_tbl);
3563
3564 px_projfunc_burdened_cost_tbl := Pa_currency.round_currency_amt_nested_blk(px_projfunc_burdened_cost_tbl,
3565 l_projfunc_currency_code_tbl);
3566
3567 px_projfunc_revenue_tbl := Pa_currency.round_currency_amt_nested_blk(px_projfunc_revenue_tbl,
3568 l_projfunc_currency_code_tbl);
3569 --Code changes for bug 4200168 starts here.
3570 END IF;
3571
3572 IF l_debug_mode = 'Y' THEN
3573 pa_debug.g_err_stage:='Exiting round_amounts';
3574 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
3575 END IF;
3576 /* bug 4227840: wrapping the setting of debug error stack call to
3577 * pa_debug under the debug enbaling check
3578 */
3579 IF l_debug_mode = 'Y' THEN
3580 -- reset curr function
3581 pa_debug.reset_curr_function;
3582 END IF;
3583 EXCEPTION
3584
3585 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3586 l_msg_count := FND_MSG_PUB.count_msg;
3587 IF l_msg_count = 1 THEN
3588 PA_INTERFACE_UTILS_PUB.get_messages
3589 (p_encoded => FND_API.G_TRUE
3590 ,p_msg_index => 1
3591 ,p_msg_count => l_msg_count
3592 ,p_msg_data => l_msg_data
3593 ,p_data => l_data
3594 ,p_msg_index_out => l_msg_index_out);
3595
3596 x_msg_data := l_data;
3597 x_msg_count := l_msg_count;
3598 ELSE
3599 x_msg_count := l_msg_count;
3600 END IF;
3601 x_return_status := FND_API.G_RET_STS_ERROR;
3602
3603 IF l_debug_mode = 'Y' THEN
3604 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
3605 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3606
3607 END IF;
3608 /* bug 4227840: wrapping the setting of debug error stack call to
3609 * pa_debug under the debug enbaling check
3610 */
3611 IF l_debug_mode = 'Y' THEN
3612 -- reset curr function
3613 pa_debug.reset_curr_function();
3614 END IF;
3615 RETURN;
3616 WHEN OTHERS THEN
3617
3618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3619 x_msg_count := 1;
3620 x_msg_data := SQLERRM;
3621
3622 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_MULTI_CURRENCY_PKG'
3623 ,p_procedure_name => 'round_amounts');
3624
3625 IF l_debug_mode = 'Y' THEN
3626 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3627 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
3628 END IF;
3629 /* bug 4227840: wrapping the setting of debug error stack call to
3630 * pa_debug under the debug enbaling check
3631 */
3632 IF l_debug_mode = 'Y' THEN
3633 -- reset curr function
3634 pa_debug.Reset_Curr_Function();
3635 END IF;
3636 RAISE;
3637
3638 END round_amounts;
3639
3640 END PA_FP_MULTI_CURRENCY_PKG;