1 PACKAGE BODY pa_fp_txn_currencies_pub AS
2 /* $Header: PAFPTXCB.pls 120.1 2005/08/19 16:30:38 mwasowic noship $*/
3
4 Invalid_Arg_Exc EXCEPTION;
5 l_module_name VARCHAR2(100):= 'pa.plsql.pa_fp_txn_currencies_pub';
6
7 /*===========================================================================
8 This api copies pa_fp_txn_currencies from one proj option to another.
9 If p_target_fp_preference_code isn't passed then it's vale is fetched from
10 pa_proj_fp_options table.
11
12 3/30/2004 Raja FP M Phase II Dev changes
13 Post FP M txn currencies can be added irrespective of MC flag status
14 Changed the code such that if source option is available all the currencies
15 are copied from source to target option
16
17 ============================================================================*/
18
19 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
20
21 PROCEDURE Copy_Fp_Txn_Currencies (
22 p_source_fp_option_id IN NUMBER
23 ,p_target_fp_option_id IN NUMBER
24 ,p_target_fp_preference_code IN VARCHAR2
25 ,p_plan_in_multi_curr_flag IN VARCHAR2 --Bug:- 2706430
26 ,p_approved_rev_plan_type_flag IN VARCHAR2 --For Bug 2998696
27 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
28 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
29 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
30 AS
31
32 /* Start of Variables to be used for debugging purpose */
33
34 l_msg_count NUMBER :=0;
35 l_data VARCHAR2(2000);
36 l_msg_data VARCHAR2(2000);
37 l_error_msg_code VARCHAR2(30);
38 l_msg_index_out NUMBER;
39 l_return_status VARCHAR2(2000);
40 l_debug_mode VARCHAR2(30);
41
42
43 /* End of Variables to be used for debugging purpose */
44
45 l_source_project_id pa_projects_all.project_id%TYPE;
46 l_source_fp_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
47
48 l_srce_cost_default_curr_code pa_fp_txn_currencies.txn_currency_code%TYPE;
49 l_srce_rev_default_curr_code pa_fp_txn_currencies.txn_currency_code%TYPE;
50 l_srce_all_default_curr_code pa_fp_txn_currencies.txn_currency_code%TYPE;
51 l_only_projfunc_curr BOOLEAN; -- Added for #2632410.
52
53 CURSOR target_fp_options_cur IS
54 SELECT project_id
55 ,fin_plan_type_id
56 ,fin_plan_version_id
57 ,NVL(p_target_fp_preference_code,fin_plan_preference_code) fin_plan_preference_code
58 ,nvl(p_approved_rev_plan_type_flag,nvl(approved_rev_plan_type_flag,'N')) approved_rev_plan_type_flag--For Bug 2998696
59 -- ,plan_in_multi_curr_flag Bug:- 2706430
60 /* commented out as we should be using the passed value always */
61 FROM pa_proj_fp_options
62 WHERE proj_fp_options_id = p_target_fp_option_id;
63
64 target_fp_options_rec target_fp_options_cur%ROWTYPE;
65
66 CURSOR proj_pf_currencies_cur(c_project_id pa_projects.project_id%TYPE) IS
67 SELECT project_currency_code
68 ,projfunc_currency_code
69 FROM pa_projects_all
70 WHERE project_id = c_project_id;
71
72 proj_pf_currencies_rec proj_pf_currencies_cur%ROWTYPE;
73
74 CURSOR default_all_curr_code IS
75 SELECT txn_currency_code
76 FROM pa_fp_txn_currencies
77 WHERE proj_fp_options_id = p_target_fp_option_id
78 AND default_all_curr_flag = 'Y';
79
80 CURSOR default_cost_curr_code IS
81 SELECT txn_currency_code
82 FROM pa_fp_txn_currencies
83 WHERE proj_fp_options_id = p_target_fp_option_id
84 AND default_cost_curr_flag = 'Y';
85
86 CURSOR default_rev_curr_code IS
87 SELECT txn_currency_code
88 FROM pa_fp_txn_currencies
89 WHERE proj_fp_options_id = p_target_fp_option_id
90 AND default_rev_curr_flag = 'Y';
91
92 BEGIN
93
94 -- Set the error stack.
95 pa_debug.set_err_stack('PA_FP_TXN_CURRENCIES_PUB.Copy_Fp_Txn_Currencies');
96
97 -- Get the Debug mode into local variable and set it to 'Y' if its NULL
98 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
99 l_debug_mode := NVL(l_debug_mode, 'Y');
100
101 -- Initialize the return status to success
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103 x_msg_count := 0;
104
105 IF P_PA_DEBUG_MODE = 'Y' THEN
106 pa_debug.set_process('Copy_Fp_Txn_Currencies: ' || 'PLSQL','LOG',l_debug_mode);
107 END IF;
108
109 -- Check for business rules violations
110
111 IF P_PA_DEBUG_MODE = 'Y' THEN
112 pa_debug.g_err_stage:='Validating input parameters';
113 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
114 END IF;
115
116 -- Check if source and target fp option ids are null
117
118 IF (p_target_fp_option_id IS NULL) OR
119 (p_plan_in_multi_curr_flag IS NULL)
120 THEN
121
122 IF P_PA_DEBUG_MODE = 'Y' THEN
123 pa_debug.g_err_stage:='Target_fp_option_id = '||p_target_fp_option_id;
124 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,5);
125 pa_debug.g_err_stage:='Target_fp_option_id = '||p_plan_in_multi_curr_flag;
126 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,5);
127 END IF;
128
129 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
130 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
131
132 RAISE Invalid_Arg_Exc;
133
134 END IF;
135
136 IF P_PA_DEBUG_MODE = 'Y' THEN
137 pa_debug.g_err_stage:='Parameter validation complete';
138 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
139 END IF;
140
141 -- Fetch project id,plan type id,plan version id and preference code of target
142 -- from pa_proj_fp_options
143
144 IF P_PA_DEBUG_MODE = 'Y' THEN
145 pa_debug.g_err_stage:='Opening target_fp_options_cur';
146 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
147 END IF;
148
149 OPEN target_fp_options_cur;
150 FETCH target_fp_options_cur INTO target_fp_options_rec;
151 CLOSE target_fp_options_cur;
152
153 IF P_PA_DEBUG_MODE = 'Y' THEN
154 pa_debug.g_err_stage:='Project_id ='||target_fp_options_rec.project_id;
155 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
156 pa_debug.g_err_stage:='Fin_plan_type_id ='||target_fp_options_rec.fin_plan_type_id;
157 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
158 pa_debug.g_err_stage:='Fin_plan_preference_code ='||target_fp_options_rec.fin_plan_preference_code;
159 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
160 pa_debug.g_err_stage:='Fin_plan_version_id ='||target_fp_options_rec.fin_plan_version_id;
161 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
162 END IF;
163
164 --Fetch project and project functional currencies for target project
165
166 IF P_PA_DEBUG_MODE = 'Y' THEN
167 pa_debug.g_err_stage:='Opening proj_pf_currencies_cur';
168 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
169 END IF;
170
171 OPEN proj_pf_currencies_cur(target_fp_options_rec.project_id);
172 FETCH proj_pf_currencies_cur INTO proj_pf_currencies_rec;
173 CLOSE proj_pf_currencies_cur;
174
175 IF P_PA_DEBUG_MODE = 'Y' THEN
176 pa_debug.g_err_stage:='project_currency_code='||proj_pf_currencies_rec.project_currency_code;
177 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
178 pa_debug.g_err_stage:='projfunc_currency_code='||proj_pf_currencies_rec.projfunc_currency_code;
179 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
180 END IF;
181
182 IF p_source_fp_option_id IS NULL THEN
183
184 l_source_fp_option_id := PA_PROJ_FP_OPTIONS_PUB.GET_PARENT_FP_OPTION_ID(p_target_fp_option_id);
185
186 ELSE
187
188 l_source_fp_option_id := p_source_fp_option_id;
189
190 END IF;
191
192 --Delete the existing fp txn currencies of the target fp option
193
194 DELETE FROM pa_fp_txn_currencies
195 WHERE proj_fp_options_id = p_target_fp_option_id;
196
197 -- p_plan_in_multi_curr_flag = 'N' condition has been added (for bug :- 2706430) to insert
198 -- PC and PFC as txn currencies incase multi currency isn't enabled
199
200 -- 3/30/2004 Raja FP M Phase II Dev changes
201 -- Post FP M txn currencies can be added irrespective of MC flag status
202 -- Changed the code such that if source option is available all the currencies
203 -- are copied from source to target option
204
205 IF l_source_fp_option_id IS NULL
206 -- Raja 3/30/2004 FP M Phase II Dev Changes OR p_plan_in_multi_curr_flag = 'N'
207 THEN
208
209 --Calling Insert_Default_Currencies api
210
211 IF P_PA_DEBUG_MODE = 'Y' THEN
212 pa_debug.g_err_stage:='Calling Insert_Default_Currencies api';
213 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
214 END IF;
215
216 Insert_Default_Currencies(
217 p_project_id => target_fp_options_rec.project_id
218 ,p_fin_plan_type_id => target_fp_options_rec.fin_plan_type_id
219 ,p_fin_plan_preference_code => target_fp_options_rec.fin_plan_preference_code
220 ,p_fin_plan_version_id => target_fp_options_rec.fin_plan_version_id
221 ,p_project_currency_code => proj_pf_currencies_rec.project_currency_code
222 ,p_projfunc_currency_code => proj_pf_currencies_rec.projfunc_currency_code
223 ,p_approved_rev_plan_type_flag => target_fp_options_rec.approved_rev_plan_type_flag
224 ,p_target_fp_option_id => p_target_fp_option_id );
225
226 ELSE -- Raja 3/30/2004 FP M phase II Dev changes IF p_plan_in_multi_curr_flag = 'Y' THEN
227
228 --Fetch project id of source fp option
229
230 IF P_PA_DEBUG_MODE = 'Y' THEN
231 pa_debug.g_err_stage:='Fetching source project id';
232 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
233 END IF;
234
235 SELECT project_id
236 INTO l_source_project_id
237 FROM pa_proj_fp_options
238 WHERE proj_fp_options_id = l_source_fp_option_id;
239
240 /* #2632410: Modified the below logic to insert only Project Functional Records
241 when the l_only_projfunc_curr_flg returned by Insert_Only_Projfunc_Curr is TRUE. */
242
243 /* Getting the l_only_projfunc_curr_flg to determine if only the Project
244 Functional currency has to be inserted. */
245
246 IF P_PA_DEBUG_MODE = 'Y' THEN
247 pa_debug.g_err_stage:='Calling Insert_Only_Projfunc_Curr';
248 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
249 END IF;
250
251 l_only_projfunc_curr := Insert_Only_Projfunc_Curr( p_proj_fp_options_id => p_target_fp_option_id
252 ,p_approved_rev_plan_type_flag => p_approved_rev_plan_type_flag );
253
254 IF l_only_projfunc_curr = TRUE THEN -- Call Insert Default currencies to insert only proj func record.
255
256 IF P_PA_DEBUG_MODE = 'Y' THEN
257 pa_debug.g_err_stage:='Calling Insert_Default_Currencies to insert projfunc record.';
258 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
259 END IF;
260
261 Insert_Default_Currencies(
262 p_project_id => target_fp_options_rec.project_id
263 ,p_fin_plan_type_id => target_fp_options_rec.fin_plan_type_id
264 ,p_fin_plan_preference_code => target_fp_options_rec.fin_plan_preference_code
265 ,p_fin_plan_version_id => target_fp_options_rec.fin_plan_version_id
266 ,p_project_currency_code => proj_pf_currencies_rec.project_currency_code
267 ,p_projfunc_currency_code => proj_pf_currencies_rec.projfunc_currency_code
268 ,p_approved_rev_plan_type_flag => target_fp_options_rec.approved_rev_plan_type_flag
269 ,p_target_fp_option_id => p_target_fp_option_id );
270
271 ELSE -- Do the processing as it was previously
272
273 IF P_PA_DEBUG_MODE = 'Y' THEN
274 pa_debug.g_err_stage:='Inserting records into pa_fp_txn_currencies for the target ';
275 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
276 END IF;
277
278 INSERT INTO PA_FP_TXN_CURRENCIES (
279 fp_txn_currency_id
280 ,proj_fp_options_id
281 ,project_id
282 ,fin_plan_type_id
283 ,fin_plan_version_id
284 ,txn_currency_code
285 ,default_rev_curr_flag
286 ,default_cost_curr_flag
287 ,default_all_curr_flag
288 ,project_currency_flag
289 ,projfunc_currency_flag
290 ,last_update_date
291 ,last_updated_by
292 ,creation_date
293 ,created_by
294 ,last_update_login
295 ,project_cost_exchange_rate --fix for bug 2613901
296 ,project_rev_exchange_rate
297 ,projfunc_cost_exchange_Rate
298 ,projfunc_rev_exchange_Rate)
299 SELECT pa_fp_txn_currencies_s.NEXTVAL
300 ,p_target_fp_option_id
301 ,target_fp_options_rec.project_id --project_id of target fp option
302 ,target_fp_options_rec.fin_plan_type_id --plan_type of target fp option
303 ,target_fp_options_rec.fin_plan_version_id --plan version of target fp option
304 ,txn_currency_code
305 ,default_rev_curr_flag
306 ,default_cost_curr_flag
307 ,default_all_curr_flag
308 ,project_currency_flag
309 ,projfunc_currency_flag
310 ,SYSDATE
311 ,fnd_global.user_id
312 ,SYSDATE
313 ,fnd_global.user_id
314 ,fnd_global.login_id
315 ,project_cost_exchange_rate --fix for bug 2613901
316 ,project_rev_exchange_rate
317 ,projfunc_cost_exchange_Rate
318 ,projfunc_rev_exchange_Rate
319 FROM pa_fp_txn_currencies
320 WHERE proj_fp_options_id = l_source_fp_option_id;
321
322 IF SQL%rowcount = 0 THEN
323
324 --Insert default currencies if no records exist for source fp options id
325
326 IF P_PA_DEBUG_MODE = 'Y' THEN
327 pa_debug.g_err_stage:='Calling Insert_Default_Currencies api ';
328 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
329 END IF;
330
331 Insert_Default_Currencies(
332 p_project_id => target_fp_options_rec.project_id
333 ,p_fin_plan_type_id => target_fp_options_rec.fin_plan_type_id
334 ,p_fin_plan_preference_code => target_fp_options_rec.fin_plan_preference_code
335 ,p_fin_plan_version_id => target_fp_options_rec.fin_plan_version_id
336 ,p_project_currency_code => proj_pf_currencies_rec.project_currency_code
337 ,p_projfunc_currency_code => proj_pf_currencies_rec.projfunc_currency_code
338 ,p_approved_rev_plan_type_flag => target_fp_options_rec.approved_rev_plan_type_flag
339 ,p_target_fp_option_id => p_target_fp_option_id );
340
341 ELSE
342
343 --If project ids are different then pass the curr codes as NULL
344
345 IF l_source_project_id <> target_fp_options_rec.project_id THEN
346
347 l_srce_all_default_curr_code:= NULL;
348
349 l_srce_cost_default_curr_code:= NULL;
350
351 l_srce_rev_default_curr_code:= NULL;
352
353 ELSE -- if same fetch default curr codes
354
355 OPEN default_all_curr_code;
356
357 FETCH default_all_curr_code INTO l_srce_all_default_curr_code;
358
359 CLOSE default_all_curr_code;
360
361 OPEN default_cost_curr_code;
362
363 FETCH default_cost_curr_code INTO l_srce_cost_default_curr_code;
364
365 CLOSE default_cost_curr_code;
366
367 OPEN default_rev_curr_code;
368
369 FETCH default_rev_curr_code INTO l_srce_rev_default_curr_code;
370
371 CLOSE default_rev_curr_code;
372
373 END IF;
374
375 --Call Set_Default_Currencies private procedure
376
377 IF P_PA_DEBUG_MODE = 'Y' THEN
378 pa_debug.g_err_stage:='Calling Set_Default_Currencies';
379 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
380 END IF;
381
382 PA_FP_TXN_CURRENCIES_PUB.Set_Default_Currencies(
383 p_target_fp_option_id => p_target_fp_option_id
384 ,p_target_preference_code => target_fp_options_rec.fin_plan_preference_code
385 ,p_approved_rev_plan_type_flag => target_fp_options_rec.approved_rev_plan_type_flag
386 ,p_srce_all_default_curr_code => l_srce_all_default_curr_code
387 ,p_srce_rev_default_curr_code => l_srce_rev_default_curr_code
388 ,p_srce_cost_default_curr_code => l_srce_cost_default_curr_code
389 ,p_project_currency_code => proj_pf_currencies_rec.project_currency_code
390 ,p_projfunc_currency_code => proj_pf_currencies_rec.projfunc_currency_code );
391
392 END IF; --sql%rowcount <> 0
393
394 END IF; --l_only_projfunc_curr_flg = 'Y'
395
396 END IF; -- l_source_fp_option_id null/not null
397
398 IF P_PA_DEBUG_MODE = 'Y' THEN
399 pa_debug.g_err_stage:='Exiting Copy_Fp_Txn_Currencies';
400 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
401 END IF;
402
403 --Reset the error stack
404
405 pa_debug.reset_err_stack;
406
407 EXCEPTION
408
409 WHEN Invalid_Arg_Exc THEN
410
411 l_msg_count := FND_MSG_PUB.count_msg;
412
413 IF l_msg_count = 1 THEN
414
415 PA_INTERFACE_UTILS_PUB.get_messages
416 (p_encoded => FND_API.G_TRUE
417 ,p_msg_index => 1
418 ,p_msg_count => l_msg_count
419 ,p_msg_data => l_msg_data
420 ,p_data => l_data
421 ,p_msg_index_out => l_msg_index_out);
422
423 x_msg_data := l_data;
424
425 x_msg_count := l_msg_count;
426 ELSE
427
428 x_msg_count := l_msg_count;
429
430 END IF;
431
432 x_return_status := FND_API.G_RET_STS_ERROR;
433
434 IF P_PA_DEBUG_MODE = 'Y' THEN
435 pa_debug.g_err_stage:='Invalid Arguments Passed';
436 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,5);
437 END IF;
438 pa_debug.reset_err_stack;
439
440 RAISE;
441
442 WHEN Others THEN
443
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 x_msg_count := 1;
446 x_msg_data := SQLERRM;
447
448 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_TXN_CURRENCIES_PUB'
449 ,p_procedure_name => 'COPY_FP_TXN_CURRENCIES');
450
451 IF P_PA_DEBUG_MODE = 'Y' THEN
452 pa_debug.g_err_stage:='Unexpeted Error';
453 pa_debug.write('Copy_Fp_Txn_Currencies: ' || l_module_name,pa_debug.g_err_stage,5);
454 END IF;
455 pa_debug.reset_err_stack;
456 RAISE ;
457
458 END Copy_Fp_Txn_Currencies;
459
460 /*===========================================================================
461 This api is called from copy_fp_txn_currencies to insert default currencies
462 for target fp option if source option is null and parent option is not present
463 =============================================================================*/
464
465 PROCEDURE Insert_Default_Currencies(
466 p_project_id IN NUMBER
467 ,p_fin_plan_type_id IN NUMBER
468 ,p_fin_plan_preference_code IN VARCHAR2
469 ,p_fin_plan_version_id IN NUMBER
470 ,p_project_currency_code IN VARCHAR2
471 ,p_projfunc_currency_code IN VARCHAR2
472 ,p_approved_rev_plan_type_flag IN VARCHAR2
473 ,p_target_fp_option_id IN NUMBER )
474 AS
475 l_only_proj_func_curr BOOLEAN; -- Added for #2632410
476 BEGIN
477
478 /* #2632410: Modified the below logic to insert the Project Currency Record
479 when the l_only_proj_func_curr returned by Insert_Only_Projfunc_Curr is
480 FALSE. */
481
482 IF P_PA_DEBUG_MODE = 'Y' THEN
483 pa_debug.g_err_stage:='Calling Insert_Only_Projfunc_Curr - 1';
484 pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
485 END IF;
486
487 l_only_proj_func_curr := Insert_Only_Projfunc_Curr( p_proj_fp_options_id => p_target_fp_option_id
488 ,p_approved_rev_plan_type_flag => p_approved_rev_plan_type_flag );--For bug 2998696
489
490 IF l_only_proj_func_curr = FALSE THEN --Do not insert any proj currency rec if flag is TRUE
491
492 IF P_PA_DEBUG_MODE = 'Y' THEN
493 pa_debug.g_err_stage:='Inserting project currency as default currency ';
494 pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
495 END IF;
496
497 INSERT INTO PA_FP_TXN_CURRENCIES (
498 fp_txn_currency_id
499 ,proj_fp_options_id
500 ,project_id
501 ,fin_plan_type_id
502 ,fin_plan_version_id
503 ,txn_currency_code
504 ,default_rev_curr_flag
505 ,default_cost_curr_flag
506 ,default_all_curr_flag
507 ,project_currency_flag
508 ,projfunc_currency_flag
509 ,last_update_date
510 ,last_updated_by
511 ,creation_date
512 ,created_by
513 ,last_update_login
514 ,project_cost_exchange_rate --fix for bug 2613901
515 ,project_rev_exchange_rate
516 ,projfunc_cost_exchange_Rate
517 ,projfunc_rev_exchange_Rate
518 )
519 SELECT pa_fp_txn_currencies_s.NEXTVAL
520 ,p_target_fp_option_id
521 ,p_project_id --project_id of target fp option
522 ,p_fin_plan_type_id --plan_type of target fp option
523 ,p_fin_plan_version_id --plan version of target fp option
524 ,p_project_currency_code
525 ,'N' --default_rev_curr_flag
526 ,'N' --default_cost_curr_flag
527 ,'N' --default_all_curr_flag
528 ,'Y'--project_currency_flag
529 ,DECODE(p_projfunc_currency_code,p_project_currency_code,'Y','N') --projfunc_currency_flag
530 ,SYSDATE
531 ,fnd_global.user_id
532 ,SYSDATE
533 ,fnd_global.user_id
534 ,fnd_global.login_id
535 ,NULL --fix for bug 2613901
536 ,NULL
537 ,NULL
538 ,NULL
539 FROM DUAL;
540
541 END IF; --l_only_proj_func_curr = FALSE
542
543 /* #2632410: The Project Functional Currency record has to be inserted
544 even when l_only_proj_func_curr is TRUE */
545
546 IF (p_projfunc_currency_code <> p_project_currency_code OR
547 l_only_proj_func_curr = TRUE) THEN
548
549 IF P_PA_DEBUG_MODE = 'Y' THEN
550 pa_debug.g_err_stage:='Inserting projfunc currency ';
551 pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
552 END IF;
553
554 INSERT INTO PA_FP_TXN_CURRENCIES (
555 fp_txn_currency_id
556 ,proj_fp_options_id
557 ,project_id
558 ,fin_plan_type_id
559 ,fin_plan_version_id
560 ,txn_currency_code
561 ,default_rev_curr_flag
562 ,default_cost_curr_flag
563 ,default_all_curr_flag
564 ,project_currency_flag
565 ,projfunc_currency_flag
566 ,last_update_date
567 ,last_updated_by
568 ,creation_date
569 ,created_by
570 ,last_update_login
571 ,project_cost_exchange_rate --fix for bug 2613901
572 ,project_rev_exchange_rate
573 ,projfunc_cost_exchange_Rate
574 ,projfunc_rev_exchange_Rate)
575 SELECT pa_fp_txn_currencies_s.NEXTVAL
576 ,p_target_fp_option_id
577 ,p_project_id --project_id of target fp option
578 ,p_fin_plan_type_id --plan_type of target fp option
579 ,p_fin_plan_version_id --plan version of target fp option
580 ,p_projfunc_currency_code
581 ,'N' --default_rev_curr_flag
582 ,'N' --default_cost_curr_flag
583 ,'N' --default_all_curr_flag
584 ,DECODE(p_projfunc_currency_code,p_project_currency_code,'Y','N') --project_currency_flag
585 ,'Y' --projfunc_currency_flag
586 ,SYSDATE
587 ,fnd_global.user_id
588 ,SYSDATE
589 ,fnd_global.user_id
590 ,fnd_global.login_id
591 ,NULL --fix for bug 2613901
592 ,NULL
593 ,NULL
594 ,NULL
595 FROM DUAL;
596
597 END IF;
598
599 --To set the default currencies call Set_Default_Currencies
600
601 IF P_PA_DEBUG_MODE = 'Y' THEN
602 pa_debug.g_err_stage:='Calling Set_Default_Currencies';
603 pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
604 END IF;
605
606 PA_FP_TXN_CURRENCIES_PUB.Set_Default_Currencies(
607 p_target_fp_option_id => p_target_fp_option_id
608 ,p_target_preference_code => p_fin_plan_preference_code
609 ,p_approved_rev_plan_type_flag => p_approved_rev_plan_type_flag
610 ,p_srce_all_default_curr_code => NULL
611 ,p_srce_rev_default_curr_code => NULL
612 ,p_srce_cost_default_curr_code => NULL
613 ,p_project_currency_code => p_project_currency_code
614 ,p_projfunc_currency_code => p_projfunc_currency_code );
615
616 IF P_PA_DEBUG_MODE = 'Y' THEN
617 pa_debug.g_err_stage:='Exiting Insert_Default_Currencies ';
618 pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
619 END IF;
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 IF P_PA_DEBUG_MODE = 'Y' THEN
624 pa_debug.g_err_stage:='EXCEPTION Insert_Default_Currencies ' || SQLERRM;
625 pa_debug.write('Insert_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
626 END IF;
627 RAISE;
628 END Insert_Default_Currencies;
629 /*===========================================================================
630 This api is called from copy_fp_txn_currencies and Insert_Default_Currencies
631 this api sets the default currency flags appropriately
632 =============================================================================*/
633 PROCEDURE Set_Default_Currencies(
634 p_target_fp_option_id IN NUMBER
635 ,p_target_preference_code IN VARCHAR2
636 ,p_approved_rev_plan_type_flag IN VARCHAR2
637 ,p_srce_all_default_curr_code IN VARCHAR2
638 ,p_srce_rev_default_curr_code IN VARCHAR2
639 ,p_srce_cost_default_curr_code IN VARCHAR2
640 ,p_project_currency_code IN VARCHAR2
641 ,p_projfunc_currency_code IN VARCHAR2 )
642 AS
643 l_srce_cost_default_curr_code pa_fp_txn_currencies.txn_currency_code%TYPE;
644 l_srce_rev_default_curr_code pa_fp_txn_currencies.txn_currency_code%TYPE;
645 l_srce_all_default_curr_code pa_fp_txn_currencies.txn_currency_code%TYPE;
646 BEGIN
647
648 IF P_PA_DEBUG_MODE = 'Y' THEN
649 pa_debug.g_err_stage:='Target_preference_code ='||p_target_preference_code;
650 pa_debug.write('Set_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
651 END IF;
652
653
654 IF p_target_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY THEN
655
656 IF p_srce_all_default_curr_code IS NOT NULL THEN
657
658 l_srce_cost_default_curr_code := p_srce_all_default_curr_code;
659
660 END IF;
661
662 IF p_srce_cost_default_curr_code IS NOT NULL THEN
663
664 l_srce_cost_default_curr_code := p_srce_cost_default_curr_code;
665
666 END IF;
667
668 --If l_srce_cost_default_curr_code is still NULL then set project_currency as default.
669
670 IF l_srce_cost_default_curr_code IS NULL THEN
671
672 l_srce_cost_default_curr_code := p_project_currency_code;
673
674 END IF;
675
676 UPDATE pa_fp_txn_currencies
677 SET default_cost_curr_flag = DECODE(txn_currency_code,l_srce_cost_default_curr_code,'Y','N')
678 ,default_rev_curr_flag = 'N'
679 ,default_all_curr_flag = 'N'
680 WHERE proj_fp_options_id = p_target_fp_option_id ;
681
682 ELSIF p_target_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY THEN
683
684 IF p_srce_all_default_curr_code IS NOT NULL THEN
685
686 l_srce_rev_default_curr_code := p_srce_all_default_curr_code;
687
688 END IF;
689
690 IF p_srce_rev_default_curr_code IS NOT NULL THEN
691
692 l_srce_rev_default_curr_code := p_srce_rev_default_curr_code;
693
694 END IF;
695
696 --If l_srce_rev_default_curr_code is still NULL then set project_currency/projfunc currency
697 --as default depending on approved_rev_plan_type_flag
698
699 -- IF l_srce_rev_default_curr_code IS NULL THEN Commented out the outer if for bug 2593182 and
700 --shifted it to the if condition below
701
702 IF (nvl(p_approved_rev_plan_type_flag,'N') <> 'Y') THEN
703 IF (l_srce_rev_default_curr_code IS NULL) THEN
704
705 l_srce_rev_default_curr_code := p_project_currency_code;
706
707 END IF;
708
709 ELSE
710 l_srce_rev_default_curr_code := p_projfunc_currency_code;
711
712 END IF;
713
714 -- END IF;
715
716
717 UPDATE pa_fp_txn_currencies
718 SET default_cost_curr_flag = 'N'
719 ,default_rev_curr_flag = DECODE(txn_currency_code,l_srce_rev_default_curr_code,'Y','N')
720 ,default_all_curr_flag = 'N'
721 WHERE proj_fp_options_id = p_target_fp_option_id ;
722
723 ELSIF p_target_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME THEN
724
725 IF p_srce_cost_default_curr_code IS NOT NULL THEN
726
727 l_srce_all_default_curr_code := p_srce_cost_default_curr_code;
728
729 ELSIF p_srce_rev_default_curr_code IS NOT NULL THEN
730
731 l_srce_all_default_curr_code := p_srce_rev_default_curr_code;
732
733 END IF;
734
735 IF p_srce_all_default_curr_code IS NOT NULL THEN
736
737 l_srce_all_default_curr_code := p_srce_all_default_curr_code;
738
739 END IF;
740
741 /* IF l_srce_all_default_curr_code IS NULL THEN
742
743 l_srce_all_default_curr_code := p_project_currency_code;
744
745 END IF;
746 Commented out the If condition for bug 2593182 and included the if condition below
747 */
748
749 IF p_approved_rev_plan_type_flag ='Y' THEN
750
751 l_srce_all_default_curr_code := p_projfunc_currency_code;
752
753 ELSIF l_srce_all_default_curr_code IS NULL THEN /*included the if for bug 2593182*/
754
755 l_srce_all_default_curr_code := p_project_currency_code;
756
757 END IF;
758
759 UPDATE pa_fp_txn_currencies
760 SET default_cost_curr_flag = 'N'
761 ,default_rev_curr_flag = 'N'
762 ,default_all_curr_flag = DECODE(txn_currency_code,l_srce_all_default_curr_code,'Y','N')
763 WHERE proj_fp_options_id = p_target_fp_option_id ;
764
765 ELSIF p_target_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP THEN
766
767 IF p_srce_all_default_curr_code IS NOT NULL THEN
768
769 l_srce_cost_default_curr_code := p_srce_all_default_curr_code;
770
771 l_srce_rev_default_curr_code := p_srce_all_default_curr_code;
772
773 END IF;
774
775 IF p_srce_rev_default_curr_code IS NOT NULL THEN
776
777 l_srce_rev_default_curr_code := p_srce_rev_default_curr_code;
778
779 END IF;
780
781 IF p_srce_cost_default_curr_code IS NOT NULL THEN
782
783 l_srce_cost_default_curr_code := p_srce_cost_default_curr_code;
784
785 END IF;
786 -- If cost_currency is null then set project currency as cost currency
787
788 IF l_srce_cost_default_curr_code IS NULL THEN
789
790 l_srce_cost_default_curr_code := p_project_currency_code;
791
792 END IF;
793
794
795 --If rev_currency is null then projfunc currency/project currency as
796 --rev currency using approved rev plan type flag
797
798 -- IF l_srce_rev_default_curr_code IS NULL THEN Commenting out for bug 2593182
799
800 IF p_approved_rev_plan_type_flag ='Y' THEN
801
802 l_srce_rev_default_curr_code := p_projfunc_currency_code;
803
804 ELSIF l_srce_rev_default_curr_code IS NULL THEN /*included the if for bug 2593182*/
805
806 l_srce_rev_default_curr_code := p_project_currency_code;
807
808 END IF;
809
810 -- END IF;
811
812 IF P_PA_DEBUG_MODE = 'Y' THEN
813 pa_debug.g_err_stage:='About to update ';
814 pa_debug.write('Set_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
815 END IF;
816
817 UPDATE pa_fp_txn_currencies
818 SET default_cost_curr_flag = DECODE(txn_currency_code,l_srce_cost_default_curr_code,'Y','N')
819 ,default_rev_curr_flag = DECODE(txn_currency_code,l_srce_rev_default_curr_code,'Y','N')
820 ,default_all_curr_flag = 'N'
821 WHERE proj_fp_options_id = p_target_fp_option_id ;
822
823 END IF; --preference code
824
825 IF P_PA_DEBUG_MODE = 'Y' THEN
826 pa_debug.g_err_stage:='Exiting Set_Default_Currencies ';
827 pa_debug.write('Set_Default_Currencies: ' || l_module_name,pa_debug.g_err_stage,3);
828 END IF;
829
830 END Set_Default_Currencies;
831
832 /*==============================================================================
833 This api is used to enter the agreement currency for the control item versions
834 in the pa_fp_txn_currencies table.
835 ===============================================================================*/
836
837 PROCEDURE enter_agreement_curr_for_ci
838 ( p_project_id IN pa_budget_versions.project_id%TYPE
839 ,p_fin_plan_version_id IN pa_budget_versions.budget_Version_id%TYPE
840 ,p_ci_id IN pa_budget_Versions.ci_id%TYPE
841 ,p_project_currency_code IN pa_projects.project_currency_code%TYPE
842 ,p_projfunc_currency_code IN pa_projects.projfunc_currency_code%TYPE
843 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
844 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
845 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
846 AS
847 l_msg_count NUMBER := 0;
848 l_data VARCHAR2(2000);
849 l_msg_data VARCHAR2(2000);
850 l_error_msg_code VARCHAR2(30);
851 l_msg_index_out NUMBER;
852 l_return_status VARCHAR2(2000);
853 l_debug_mode VARCHAR2(30);
854
855 l_agreement_num pa_agreements_all.agreement_num%TYPE;
856 l_agreement_amount pa_agreements_all.amount%TYPE;
857 l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
858
859 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
860 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
861 l_dummy_currency_code pa_projects_all.projfunc_currency_code%TYPE;
862
863
864 CURSOR version_details_cur IS
865 SELECT proj_fp_options_id,
866 fin_plan_type_id
867 FROM pa_proj_fp_options
868 WHERE fin_plan_version_id = p_fin_plan_version_id
869 AND project_id = p_project_id;
870
871 version_details_rec version_details_cur%ROWTYPE;
872
873 BEGIN
874
875 x_msg_count := 0;
876 x_return_status := FND_API.G_RET_STS_SUCCESS;
877 pa_debug.set_err_stack('pa_fp_txn_currencies_pub.enter_agreement_curr_for_ci');
878 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
879 l_debug_mode := NVL(l_debug_mode, 'Y');
880 IF P_PA_DEBUG_MODE = 'Y' THEN
881 pa_debug.set_process('enter_agreement_curr_for_ci: ' || 'PLSQL','LOG',l_debug_mode);
882 END IF;
883
884 -- Check for business rules violations
885
886 IF P_PA_DEBUG_MODE = 'Y' THEN
887 pa_debug.g_err_stage:= 'Validating input parameters';
888 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
889 END IF;
890
891 --Check if plan version id is null
892
893 IF (p_project_id IS NULL) OR
894 (p_fin_plan_version_id IS NULL) OR
895 (p_ci_id IS NULL)
896 THEN
897 IF P_PA_DEBUG_MODE = 'Y' THEN
898 pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
899 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
900 pa_debug.g_err_stage:= 'p_fin_plan_version_id = '|| p_fin_plan_version_id;
901 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
902 pa_debug.g_err_stage:= 'p_ci_id = '|| p_ci_id;
903 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
904 END IF;
905
906 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
907 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
908
909 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
910 END IF;
911
912 -- Using the project_id and ci_id fetch the agreement_currency
913
914 IF P_PA_DEBUG_MODE = 'Y' THEN
915 pa_debug.g_err_stage:='Fetching the agreement details';
916 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
917 END IF;
918
919 Pa_Fp_Control_Items_Utils.get_fp_ci_agreement_dtls(
920 p_project_id => p_project_id
921 ,p_ci_id => p_ci_id
922 ,x_agreement_num => l_agreement_num
923 ,x_agreement_amount => l_agreement_amount
924 ,x_agreement_currency_code => l_agreement_currency_code
925 ,x_msg_data => l_msg_data
926 ,x_msg_count => l_msg_count
927 ,x_return_status => l_return_status );
928
929 IF (l_agreement_currency_code IS NULL) OR
930 (l_return_status <> FND_API.G_RET_STS_SUCCESS)
931 THEN
932 IF P_PA_DEBUG_MODE = 'Y' THEN
933 pa_debug.g_err_stage:='Agreement_currency_code is null';
934 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
935 END IF;
936 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
937 END IF;
938
939 -- Get the required details of the fin plan version from the fp options table
940
941 IF P_PA_DEBUG_MODE = 'Y' THEN
942 pa_debug.g_err_stage:='Fetching the version details';
943 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
944 END IF;
945
946 OPEN version_details_cur;
947 FETCH version_details_cur INTO version_details_rec;
948 IF version_details_cur%NOTFOUND THEN
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950 END IF;
951 CLOSE version_details_cur;
952
953 IF p_project_currency_code IS NULL OR p_projfunc_currency_code IS NULL THEN
954 pa_budget_utils.Get_Project_Currency_Info
955 (
956 p_project_id => p_project_id
957 , x_projfunc_currency_code => l_projfunc_currency_code
958 , x_project_currency_code => l_project_currency_code
959 , x_txn_currency_code => l_dummy_currency_code
960 , x_msg_count => x_msg_count
961 , x_msg_data => x_msg_data
962 , x_return_status => x_return_status
963 );
964
965 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
966
967 IF P_PA_DEBUG_MODE = 'Y' THEN
968 pa_debug.g_err_stage:= 'Could not obtain currency info for the project';
969 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,
970 pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
971 END IF;
972 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
973 END IF;
974
975 END IF;
976
977 IF p_project_currency_code IS NOT NULL THEN
978 l_project_currency_code := p_project_currency_code;
979 END IF;
980
981 IF p_projfunc_currency_code IS NOT NULL THEN
982 l_projfunc_currency_code := p_projfunc_currency_code;
983 END IF;
984
985
986 -- Delete the entry already there (if any) for the version
987 DELETE FROM PA_FP_TXN_CURRENCIES
988 WHERE proj_fp_options_id = version_details_rec.proj_fp_options_id;
989 -- Insert into the pa_fp_txn_currencies table
990
991 INSERT INTO PA_FP_TXN_CURRENCIES (
992 fp_txn_currency_id
993 ,proj_fp_options_id
994 ,project_id
995 ,fin_plan_type_id
996 ,fin_plan_version_id
997 ,txn_currency_code
998 ,default_rev_curr_flag
999 ,default_cost_curr_flag
1000 ,default_all_curr_flag
1001 ,project_currency_flag
1002 ,projfunc_currency_flag
1003 ,last_update_date
1004 ,last_updated_by
1005 ,creation_date
1006 ,created_by
1007 ,last_update_login
1008 ,project_cost_exchange_rate
1009 ,project_rev_exchange_rate
1010 ,projfunc_cost_exchange_Rate
1011 ,projfunc_rev_exchange_Rate)
1012 SELECT pa_fp_txn_currencies_s.NEXTVAL
1013 ,version_details_rec.proj_fp_options_id
1014 ,p_project_id
1015 ,version_details_rec.fin_plan_type_id
1016 ,p_fin_plan_version_id
1017 ,l_agreement_currency_code -- txn_currency_code
1018 ,'Y' -- default_rev_curr_flag
1019 ,'Y' -- default_cost_curr_flag
1020 ,'Y' -- default_all_curr_flag
1021 ,DECODE(l_agreement_currency_code,l_project_currency_code,'Y','N') -- project_currency_flag
1022 ,DECODE(l_agreement_currency_code,l_projfunc_currency_code,'Y','N') -- projfunc_currency_flag
1023 ,SYSDATE
1024 ,fnd_global.user_id
1025 ,SYSDATE
1026 ,fnd_global.user_id
1027 ,fnd_global.login_id
1028 ,NULL -- project_cost_exchange_rate
1029 ,NULL -- project_rev_exchange_rate
1030 ,NULL -- projfunc_cost_exchange_Rate
1031 ,NULL -- projfunc_rev_exchange_Rate
1032 FROM DUAL;
1033
1034 IF P_PA_DEBUG_MODE = 'Y' THEN
1035 pa_debug.g_err_stage:= 'Exiting enter_agreement_curr_for_ci';
1036 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1037 END IF;
1038 pa_debug.reset_err_stack;
1039
1040 EXCEPTION
1041
1042 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1043
1044 x_return_status := FND_API.G_RET_STS_ERROR;
1045 l_msg_count := FND_MSG_PUB.count_msg;
1046 IF l_msg_count = 1 THEN
1047 PA_INTERFACE_UTILS_PUB.get_messages
1048 (p_encoded => FND_API.G_TRUE
1049 ,p_msg_index => 1
1050 ,p_msg_count => l_msg_count
1051 ,p_msg_data => l_msg_data
1052 ,p_data => l_data
1053 ,p_msg_index_out => l_msg_index_out);
1054 x_msg_data := l_data;
1055 x_msg_count := l_msg_count;
1056 ELSE
1057 x_msg_count := l_msg_count;
1058 END IF;
1059
1060 IF P_PA_DEBUG_MODE = 'Y' THEN
1061 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
1062 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1063 END IF;
1064 pa_debug.reset_err_stack;
1065 RAISE;
1066
1067 WHEN others THEN
1068
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070 x_msg_count := 1;
1071 x_msg_data := SQLERRM;
1072 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fp_txn_currencies_pub'
1073 ,p_procedure_name => 'enter_agreement_curr_for_ci');
1074 IF P_PA_DEBUG_MODE = 'Y' THEN
1075 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
1076 pa_debug.write('enter_agreement_curr_for_ci: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1077 END IF;
1078 pa_debug.reset_err_stack;
1079 RAISE;
1080 END enter_agreement_curr_for_ci;
1081
1082 /*===============================================================================================
1083 Bug #2632410: The following function has been added to return the flag which indicates if only
1084 the Project Functional currency attributes have to be inserted into pa_fp_txn_currencies table.
1085 Only Project Functional Currency has to be inserted in the following situations:
1086 - The Approved Revenue Flag for the Proj FP Option ID is 'Y'
1087 - The Plan level is either 'PLAN_TYPE' or 'PLAN_VERSION'
1088 - The Preference Code is either 'COST_AND_REV_SAME' or 'REVENUE_ONLY'
1089 This function will be called from Copy_Fp_Txn_Currencies and also Insert_Default_Currencies to
1090 get the l_insert_only_projfunc_curr flag.
1091
1092 Bug 3668370 Raja FP M changes Even for AR versions there can be multiple txn currencies
1093 So, changed the api to always return false so that all the currencies from
1094 parent record are added
1095 ===============================================================================================*/
1096 FUNCTION Insert_Only_Projfunc_Curr( p_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE
1097 ,p_approved_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE)--for bug 2998696
1098 RETURN BOOLEAN
1099 IS
1100
1101 l_planning_level pa_proj_fp_options.fin_plan_option_level_code%TYPE;
1102 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
1103 l_approved_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE;
1104 l_insert_only_proj_func_curr BOOLEAN;
1105
1106 BEGIN
1107
1108 IF P_PA_DEBUG_MODE = 'Y' THEN
1109 pa_debug.g_err_stage:='In Insert_Only_Projfunc_Curr';
1110 pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1111 END IF;
1112
1113 l_insert_only_proj_func_curr := FALSE;
1114
1115 /* Bug 3668370 Raja FP M changes Even for AR versions there can be multiple txn currencies
1116 -- Getting the Proj FP Option details for the Proj FP Option ID.
1117
1118 IF P_PA_DEBUG_MODE = 'Y' THEN
1119 pa_debug.g_err_stage:='Getting the FP Option details';
1120 pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1121 END IF;
1122
1123 SELECT fin_plan_option_level_code
1124 ,fin_plan_preference_code
1125 ,nvl(p_approved_rev_plan_type_flag,nvl(approved_rev_plan_type_flag,'N'))--Bug 2998696
1126 INTO l_planning_level
1127 ,l_fp_preference_code
1128 ,l_approved_rev_plan_type_flag
1129 FROM pa_proj_fp_options
1130 WHERE proj_fp_options_id = p_proj_fp_options_id;
1131
1132 IF P_PA_DEBUG_MODE = 'Y' THEN
1133 pa_debug.g_err_stage:='Approved Revenue Flag is Y';
1134 pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1135 END IF;
1136
1137 IF l_approved_rev_plan_type_flag = 'Y' AND
1138 (l_planning_level IN (PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,
1139 PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION)) AND
1140 (l_fp_preference_code IN (PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME,
1141 PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY)) THEN
1142
1143 -- Approved Rev Plan Type Flag is Y
1144 -- Planning Level is Plan Type/Plan Version,
1145 -- Fin Plan Preference code is Cost_And_Rev_Same/Revenue_Only.
1146 -- For all the above conditions, set the l_insert_only_proj_func_curr as TRUE
1147
1148 IF P_PA_DEBUG_MODE = 'Y' THEN
1149 pa_debug.g_err_stage:='Setting the l_insert_only_proj_func_curr as TRUE';
1150 pa_debug.write('Insert_Only_Projfunc_Curr: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1151 END IF;
1152
1153 l_insert_only_proj_func_curr := TRUE;
1154
1155 END IF;
1156 */
1157 RETURN l_insert_only_proj_func_curr;
1158
1159 END Insert_Only_Projfunc_Curr;
1160
1161 END pa_fp_txn_currencies_pub;
[Home] [Help]