[Home] [Help]
PACKAGE BODY: APPS.PA_MULTI_CURRENCY_BILLING
Source
1 PACKAGE BODY pa_multi_currency_billing AS
2 --$Header: PAXMULTB.pls 120.5 2007/12/28 12:01:11 hkansal ship $
3
4 /*----------------------------------------------------------------------------------------+
5 | Procedure : get_imp_defaults |
6 | Purpose : To get implementation level defaults related to multi_currency_billing|
7 | setup |
8 | Parameters : |
9 | ================================================================================== |
10 | Name Mode Description |
11 | ================================================================================== |
12 | x_multi_currency_billing_flag OUT Indicates multi_currency_billing_flag |
13 | is allowed for this OU |
14 | x_share_bill_rates_across_ou OUT Indicates sharing Bill rates schedules |
15 | across OU is allowed for this OU |
16 | x_allow_funding_across_ou OUT Indicates funding across OU is allowed for |
17 | this OU |
18 | x_default_exchange_rate_type OUT Default value for rate type |
19 | x_functional_currency OUT Functional currency of OU |
20 | x_return_status OUT Return status of this procedure |
21 | x_msg_count OUT Error message count |
22 | x_msg_data OUT Error message |
23 | ================================================================================== |
24 +----------------------------------------------------------------------------------------*/
25
26 PROCEDURE get_imp_defaults (
27 x_multi_currency_billing_flag OUT NOCOPY VARCHAR2,
28 x_share_bill_rates_across_OU OUT NOCOPY VARCHAR2,
29 x_allow_funding_across_OU OUT NOCOPY VARCHAR2,
30 x_default_exchange_rate_type OUT NOCOPY VARCHAR2,
31 x_functional_currency OUT NOCOPY VARCHAR2,
32 x_competence_match_wt OUT NOCOPY NUMBER,
33 x_availability_match_wt OUT NOCOPY NUMBER,
34 x_job_level_match_wt OUT NOCOPY NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2) IS
38
39
40 BEGIN
41
42 SELECT multi_currency_billing_flag,
43 share_across_ou_br_sch_flag,
44 allow_funding_across_ou_flag,
45 default_rate_type,
46 pa_currency.get_currency_code,
47 competence_match_wt,
48 availability_match_wt,
49 job_level_match_wt
50 INTO x_multi_currency_billing_flag,
51 x_share_bill_rates_across_OU,
52 x_allow_funding_across_OU,
53 x_default_exchange_rate_type,
54 x_functional_currency,
55 x_competence_match_wt,
56 x_availability_match_wt,
57 x_job_level_match_wt
58 FROM pa_implementations;
59
60 EXCEPTION
61
62 WHEN others THEN
63 x_msg_count := 1;
64 x_msg_data := SUBSTR(SQLERRM, 1, 240);
65 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
66 /* Aded the below for NOCOPY Mandate */
67 x_multi_currency_billing_flag := NULL;
68 x_share_bill_rates_across_OU := NULL;
69 x_allow_funding_across_OU := NULL;
70 x_default_exchange_rate_type := NULL;
71 x_functional_currency := NULL;
72 x_competence_match_wt := NULL;
73 x_availability_match_wt := NULL;
74 x_job_level_match_wt := NULL;
75 FND_MSG_PUB.add_Exc_msg(
76 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
77 p_procedure_name => 'get_imp_defaults');
78
79 RAISE ;
80
81 END get_imp_defaults;
82
83 /*----------------------------------------------------------------------------------------+
84 | Procedure : get_project_defaults |
85 | Purpose : To get project level defaults related to multi_currency_billing setup |
86 | Parameters : |
87 | ================================================================================== |
88 | Name Mode Description |
89 | ================================================================================== |
90 | p_project_id IN Project ID |
91 | x_multi_currency_billing_flag OUT Indicates multi_currency_billing_flag |
92 | is allowed for the project |
93 | x_baseline_funding_flag OUT Indicates baselining is allowed from |
94 | funding inquiry form (without any existing)|
95 | budgets for the project |
96 | x_revproc_currency_code OUT Revenue processing currency code of the |
97 | project |
98 | x_invproc_currency_type OUT Invoice processing currency type of the |
99 | project |
100 | x_invproc_currency_code OUT Invoice processing currency code of the |
101 | project |
102 | x_project_currency_code OUT Project currency code of the project |
103 | x_project_bil_rate_date_code OUT Exchange rate date type for determining the|
104 | date to use for conversion between bill |
105 | transaction currency/funding currency to |
106 | project currency for customer billing |
107 | x_project_bil_rate_type OUT Exchange rate type to use for conversion |
108 | between bill transaction currency/funding |
109 | currency to project currency for customer |
110 | billing |
111 | x_project_bil_rate_date OUT Exchange rate date to use for conversion |
112 | between bill transaction currency/funding |
113 | currency to project currency |
114 | x_project_bil_exchange_rate OUT Exchange rate to use for conversion |
115 | between bill transaction currency/funding |
116 | currency to project currency if |
117 | bil_rate_type is user |
118 | x_projfunc_currency_code OUT Project functional currency code of the |
119 | project |
120 | x_projfunc_bil_rate_date_code OUT Exchange rate date type for determining the|
121 | date to use for conversion between bill |
122 | transaction currency/funding currency to |
123 | project functional currency |
124 | x_projfunc_bil_rate_type OUT Exchange rate type to use for conversion |
125 | between bill transaction currency/funding |
126 | currency to project functional currency |
127 | x_projfunc_bil_rate_date OUT Exchange rate date to use for conversion |
128 | between bill transaction currency/funding |
129 | currency to project functional currency r |
130 | x_projfunc_bil_exchange_rate OUT Exchange rate to use for conversion |
131 | between bill transaction currency/funding |
132 | currency to project functional currency if |
133 | bil_rate_type is user |
134 | x_funding_rate_date_code OUT Exchange rate date type for determining the|
135 | date to use for conversion between bill |
136 | transaction currency to funding currency |
137 | x_funding_rate_type OUT Exchange rate type to use for conversion |
138 | between bill transaction currency to |
139 | funding currency |
140 | x_funding_rate_date OUT Exchange rate date to use for conversion |
141 | between bill transaction currency to |
142 | funding currency |
143 | x_funding_exchange_rate OUT Exchange rate to use for conversion |
144 | between bill transaction currency to |
145 | funding currency if bil_rate_type is user |
146 | x_return_status OUT Return status of this procedure |
147 | x_msg_count OUT Error message count |
148 | x_msg_data OUT Error message |
149 | ================================================================================== |
150 +----------------------------------------------------------------------------------------*/
151 PROCEDURE get_project_defaults (
152 p_project_id IN NUMBER,
153 x_multi_currency_billing_flag OUT NOCOPY VARCHAR2,
154 x_baseline_funding_flag OUT NOCOPY VARCHAR2,
155 x_revproc_currency_code OUT NOCOPY VARCHAR2,
156 x_invproc_currency_type OUT NOCOPY VARCHAR2,
157 x_invproc_currency_code OUT NOCOPY VARCHAR2,
158 x_project_currency_code OUT NOCOPY VARCHAR2,
159 x_project_bil_rate_date_code OUT NOCOPY VARCHAR2,
160 x_project_bil_rate_type OUT NOCOPY VARCHAR2,
161 x_project_bil_rate_date OUT NOCOPY DATE,
162 x_project_bil_exchange_rate OUT NOCOPY NUMBER,
163 x_projfunc_currency_code OUT NOCOPY VARCHAR2,
164 x_projfunc_bil_rate_date_code OUT NOCOPY VARCHAR2,
165 x_projfunc_bil_rate_type OUT NOCOPY VARCHAR2,
166 x_projfunc_bil_rate_date OUT NOCOPY DATE,
167 x_projfunc_bil_exchange_rate OUT NOCOPY NUMBER,
168 x_funding_rate_date_code OUT NOCOPY VARCHAR2,
169 x_funding_rate_type OUT NOCOPY VARCHAR2,
170 x_funding_rate_date OUT NOCOPY DATE,
171 x_funding_exchange_rate OUT NOCOPY NUMBER,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2) IS
175
176
177 BEGIN
178 SELECT invproc_currency_type, revproc_currency_code,
179 project_currency_code, project_bil_rate_date_code,
180 project_bil_rate_type, project_bil_rate_date,
181 project_bil_exchange_rate,
182 projfunc_currency_code, projfunc_bil_rate_date_code,
183 projfunc_bil_rate_type, projfunc_bil_rate_date,
184 projfunc_bil_exchange_rate,
185 funding_rate_date_code, funding_rate_type,
186 funding_rate_date, funding_exchange_rate,
187 baseline_funding_flag, multi_currency_billing_flag
188 INTO x_invproc_currency_type, x_revproc_currency_code,
189 x_project_currency_code, x_project_bil_rate_date_code,
190 x_project_bil_rate_type, x_project_bil_rate_date,
191 x_project_bil_exchange_rate,
192 x_projfunc_currency_code, x_projfunc_bil_rate_date_code,
193 x_projfunc_bil_rate_type, x_projfunc_bil_rate_date,
194 x_projfunc_bil_exchange_rate,
195 x_funding_rate_date_code, x_funding_rate_type,
196 x_funding_rate_date, x_funding_exchange_rate,
197 x_baseline_funding_flag, x_multi_currency_billing_flag
198 FROM pa_projects_all
199 WHERE project_id = p_project_id;
200
201
202 IF x_invproc_currency_type = 'PROJECT_CURRENCY' THEN
203
204 x_invproc_currency_code := x_project_currency_code;
205
206 ELSIF x_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
207
208 x_invproc_currency_code := x_projfunc_currency_code;
209
210 ELSIF x_invproc_currency_type = 'FUNDING_CURRENCY' THEN
211
212 BEGIN
213
214 SELECT funding_currency_code
215 INTO x_invproc_currency_code
216 FROM pa_summary_project_fundings
217 WHERE project_id = p_project_id
218 AND rownum = 1
219 AND NVL(total_baselined_amount,0) > 0; /* Added for bug 2834362 */
220 /*
221 GROUP BY funding_currency_code
222 HAVING sum(nvl(total_baselined_amount,0)) > 0; Commented for bug 2834362*/
223
224 EXCEPTION
225
226 WHEN NO_DATA_FOUND THEN
227
228 x_invproc_currency_code := null;
229
230 /*
231 x_msg_count := 1;
232 x_msg_data := 'PA_NO_FUNDING_EXISTS';
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 FND_MSG_PUB.add_Exc_msg(
235 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
236 p_procedure_name => 'get_project_defaults');
237
238 RAISE ;
239 */
240
241 END;
242
243
244 END IF;
245
246
247 EXCEPTION
248
249 WHEN others THEN
250
251 x_msg_count := 1;
252 x_msg_data := SUBSTR(SQLERRM, 1, 240);
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 /* Added the belog for NOCOPY mandate */
255 x_multi_currency_billing_flag := NULL;
256 x_baseline_funding_flag := NULL;
257 x_revproc_currency_code := NULL;
258 x_invproc_currency_type := NULL;
259 x_invproc_currency_code := NULL;
260 x_project_currency_code := NULL;
261 x_project_bil_rate_date_code := NULL;
262 x_project_bil_rate_type := NULL;
263 x_project_bil_rate_date := NULL;
264 x_project_bil_exchange_rate := NULL;
265 x_projfunc_currency_code := NULL;
266 x_projfunc_bil_rate_date_code := NULL;
267 x_projfunc_bil_rate_type := NULL;
268 x_projfunc_bil_rate_date := NULL;
269 x_projfunc_bil_exchange_rate := NULL;
270 x_funding_rate_date_code := NULL;
271 x_funding_rate_type := NULL;
272 x_funding_rate_date := NULL;
273 x_funding_exchange_rate := NULL;
274
275 FND_MSG_PUB.add_Exc_msg(
276 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
277 p_procedure_name => 'get_project_defaults');
278
279 RAISE ;
280
281 END get_project_defaults;
282
283 /*----------------------------------------------------------------------------------------+
284 | Function : is_project_mcb_enabled |
285 | Purpose : To return if multi currency billing is enabled for a project |
286 | Parameters : |
287 | ================================================================================== |
288 | Name Mode Description |
289 | ================================================================================== |
290 | p_project_id IN Project ID |
291 | ================================================================================== |
292 | Returns : VARCHAR2 |
293 +----------------------------------------------------------------------------------------*/
294
295 FUNCTION is_project_mcb_enabled ( p_project_id IN NUMBER)
296 RETURN VARCHAR2 IS
297
298 l_multi_currency_billing_flag VARCHAR2(15);
299
300 BEGIN
301
302 SELECT multi_currency_billing_flag
303 INTO l_multi_currency_billing_flag
304 FROM pa_projects_all
305 WHERE project_id = p_project_id;
306
307 RETURN l_multi_currency_billing_flag;
308
309 EXCEPTION
310
311 WHEN others THEN
312 FND_MSG_PUB.add_Exc_msg(
313 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
314 p_procedure_name => 'is_project_mcb_enabled');
315
316 RAISE ;
317
318 END is_project_mcb_enabled;
319
320
321 /*----------------------------------------------------------------------------------------+
322 | Function : is_ou_mcb_enabled |
323 | Purpose : To return if multi currency billing is enabled for the OU |
324 | Parameters : |
325 | ================================================================================== |
326 | Name Mode Description |
327 | ================================================================================== |
328 | p_org_id IN Operatin Unit Identifier |
329 | ================================================================================== |
330 | Returns : VARCHAR2 |
331 +----------------------------------------------------------------------------------------*/
332
333 FUNCTION is_OU_mcb_enabled ( p_org_id IN NUMBER)
334 RETURN VARCHAR2 IS
335
336 l_multi_currency_billing_flag VARCHAR2(15);
337
338 BEGIN
339
340 SELECT multi_currency_billing_flag
341 INTO l_multi_currency_billing_flag
342 FROM pa_implementations_all
343 WHERE org_id = p_org_id; /*Bug 5368089*/
344
345 RETURN l_multi_currency_billing_flag;
346
347 EXCEPTION
348
349 WHEN others THEN
350 FND_MSG_PUB.add_Exc_msg(
351 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
352 p_procedure_name => 'is_OU_mcb_enabled');
353
354 RAISE ;
355
356 END is_OU_mcb_enabled;
357
358
359 /*----------------------------------------------------------------------------------------+
360 | Function : is_sharing_bill_rates_allowed |
361 | Purpose : To return if sharing bill rate schedules across OU is allowed |
362 | Parameters : |
363 | ================================================================================== |
364 | Name Mode Description |
365 | ================================================================================== |
366 | p_org_id IN Operatin Unit Identifier |
367 | ================================================================================== |
368 | Returns : VARCHAR2 |
369 +----------------------------------------------------------------------------------------*/
370 FUNCTION is_sharing_bill_rates_allowed ( p_org_id IN NUMBER)
371 RETURN VARCHAR2 IS
372
373 l_share_across_ou_br_sch_flag VARCHAR2(15);
374
375 BEGIN
376
377 SELECT share_across_ou_br_sch_flag
378 INTO l_share_across_ou_br_sch_flag
379 FROM pa_implementations_all
380 WHERE org_id = p_org_id; /*Bug 5368089*/
381
382 RETURN l_share_across_ou_br_sch_flag;
383
384 EXCEPTION
385
386 WHEN others THEN
387 FND_MSG_PUB.add_Exc_msg(
388 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
389 p_procedure_name => 'is_sharing_bill_rates_allowed');
390
391 RAISE ;
392
393 END is_sharing_bill_rates_allowed;
394
395
396 /*----------------------------------------------------------------------------------------+
397 | Function : is_funding_across_ou_allowed |
398 | Purpose : To return if funding across OU is allowed |
399 | Parameters : |
400 | ================================================================================== |
401 | Name Mode Description |
402 | ================================================================================== |
403 | p_org_id IN Operatin Unit Identifier |
404 | ================================================================================== |
405 | Returns : VARCHAR2 |
406 +----------------------------------------------------------------------------------------*/
407
408 FUNCTION is_funding_across_ou_allowed
409 RETURN VARCHAR2 IS
410
411 l_allow_funding_across_ou_flag VARCHAR2(15);
412
413 BEGIN
414
415 SELECT allow_funding_across_ou_flag
416 INTO l_allow_funding_across_ou_flag
417 FROM pa_implementations;
418
419 RETURN l_allow_funding_across_ou_flag;
420
421 EXCEPTION
422
423 WHEN others THEN
424 FND_MSG_PUB.add_Exc_msg(
425 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
426 p_procedure_name => 'is_funding_across_ou_allowed');
427
428 RAISE ;
429
430 END is_funding_across_ou_allowed;
431
432 /*----------------------------------------------------------------------------------------+
433 | Function : get_invoice_processing_cur |
434 | Purpose : To return invoice processing currency code of a project |
435 | Parameters : |
436 | ================================================================================== |
437 | Name Mode Description |
438 | ================================================================================== |
439 | p_project_id IN Project ID |
440 | ================================================================================== |
441 | Returns : VARCHAR2 |
442 +----------------------------------------------------------------------------------------*/
443 FUNCTION get_invoice_processing_cur ( p_project_id IN NUMBER)
444 RETURN VARCHAR2 IS
445
446 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
447 l_invproc_currency_code pa_projects_all.project_currency_code%TYPE;
448 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
449 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
450
451 BEGIN
452
453 SELECT invproc_currency_type, project_currency_code,
454 projfunc_currency_code
455 INTO l_invproc_currency_type, l_project_currency_code,
456 l_projfunc_currency_code
457 FROM pa_projects_all
458 WHERE project_id = p_project_id;
459
460
461 IF l_invproc_currency_type = 'PROJECT_CURRENCY' THEN
462
463 l_invproc_currency_code := l_project_currency_code;
464
465 ELSIF l_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
466
467 l_invproc_currency_code := l_projfunc_currency_code;
468
469 ELSIF l_invproc_currency_type = 'FUNDING_CURRENCY' THEN
470
471 BEGIN
472
473 SELECT funding_currency_code
474 INTO l_invproc_currency_code
475 FROM pa_summary_project_fundings
476 WHERE project_id = p_project_id
477 AND rownum = 1;
478
479 EXCEPTION
480
481 WHEN NO_DATA_FOUND THEN
482 FND_MSG_PUB.add_Exc_msg(
483 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
484 p_procedure_name => 'get_invoice_processing_cur');
485
486 RAISE ;
487
488 END;
489
490 END IF;
491
492 RETURN l_invproc_currency_code;
493
494 EXCEPTION
495
496 WHEN others THEN
497 FND_MSG_PUB.add_Exc_msg(
498 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
499 p_procedure_name => 'get_invoice_processing_cur');
500
501 RAISE ;
502
503 END get_invoice_processing_cur;
504
505 /*----------------------------------------------------------------------------------------+
506 | Procedure : convert_amount_bulk |
507 | Purpose : Converts amount from one currency to another based on the currency |
508 | attributes. Also returns the exchange rate/error messages. This will |
509 | handle bulk conversion amounts. |
510 | Error message will be returned in x_status_tab. Input p_conversion_between will be|
511 | appended to the end of the error_messages, The possible expected |
512 | error messages are 1)PA_USR_RATE_NOT_ALLOWED |
513 | 2)PA_NO_EXCH_RATE_EXISTS |
514 | 3)PA_CURR_NOT_VALID |
515 | Parameters : |
516 | ================================================================================== |
517 | Name Mode Description |
518 | ================================================================================== |
519 | p_from_currency_tab IN Source Currency code(s) of the amount(s) to|
520 | be converted |
521 | p_to_currency_tab IN Destination Currency code(s) of the |
522 | amount(s) to be converted |
523 | p_conversion_date_tab IN OUT Conversion Date(s) to use for conversion |
524 | p_conversion_type_tab IN OUT Conversion type(s) to use for conversion |
525 | p_amount_tab IN Amount to be converted |
526 | p_user_validate_flag_tab IN For coversion type 'user', this flag |
527 | indicates if validation is to be done to |
528 | check if 'user' rate type is allowed for |
529 | the currency conversion |
530 | 'Y' : yes; 'N : No |
531 | p_converted_amount_tab IN OUT Converted amount |
532 | p_denominator_tab IN OUT Denominator value |
533 | p_numerator_tab IN OUT Numerator value |
534 | p_rate_tab IN OUT Rate used for coversion |
535 | p_conversion_between IN Error string denoting which conversion has |
536 | failed. Ex : PC_PF indicates project to |
537 | project functional has failed |
538 | p_cache_flag IN if attributes are to cached |
539 | |
540 | Brief desc : During EI conversion, it is assumed that the conversion attributes |
541 | will be same. So the attribures are cached into a plsql table and used for |
542 | further computations which would help in performance |
543 | x_status_tab OUT Return status of each conversion |
544 | ================================================================================== |
545 +----------------------------------------------------------------------------------------*/
546 PROCEDURE convert_amount_bulk (
547 p_from_currency_tab IN PA_PLSQL_DATATYPES.Char30TabTyp,
548 p_to_currency_tab IN PA_PLSQL_DATATYPES.Char30TabTyp,
549 p_conversion_date_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp ,
550 p_conversion_type_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
551 p_amount_tab IN PA_PLSQL_DATATYPES.NumTabTyp,
552 p_user_validate_flag_tab IN PA_PLSQL_DATATYPES.Char30TabTyp ,
553 p_converted_amount_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
554 p_denominator_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
555 p_numerator_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
556 p_rate_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp,
557 p_conversion_between IN VARCHAR2,
558 p_cache_flag IN VARCHAR2,
559 x_status_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp) IS
560
561 l_allow_user_rate_type VARCHAR2(1) ;
562 l_converted_amount NUMBER ;
563 l_numerator NUMBER ;
564 l_denominator NUMBER ;
565 l_rate NUMBER ;
566
567 l_tab_count NUMBER;
568 l_done_flag VARCHAR2(1);
569
570 l_AttrTab_count NUMBER;
571 l_debug_mode VARCHAR2(1); /* added for bug 6322049 */
572
573 BEGIN
574
575 l_tab_count := p_from_currency_tab.COUNT;
576 l_AttrTab_count := CurrAttrTab.COUNT;
577 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* added for bug 6322049 */
578
579 IF l_tab_count = 0 then
580
581 RETURN;
582
583 END IF;
584
585 FOR i in p_from_currency_tab.first..p_from_currency_tab.last LOOP
586
587 x_status_tab(i) := 'N';
588 l_done_flag := 'N';
589
590 IF (p_from_currency_tab(i) = p_to_currency_tab(i)) THEN
591
592 p_conversion_date_tab(i) := null;
593 p_conversion_type_tab(i) := null;
594 p_rate_tab(i) := null;
595 p_converted_amount_tab(i):= p_amount_tab(i);
596
597 ELSE
598
599 IF p_cache_flag = 'Y' then
600
601 --PA_MCB_REVENUE_PKG.log_message('MCB... Inside Convert amount bulk ');
602
603
604 IF nvl(CurrAttrTab.count,0) <> 0 THEN
605
606 --PA_MCB_REVENUE_PKG.log_message('UTL... in if');
607
608 For j in CurrAttrTab.First..CurrAttrTab.Last LOOP
609
610 IF l_debug_mode = 'Y' THEN /* added IF for bug 6322049 */
611 PA_MCB_REVENUE_PKG.log_message('UTL' || CurrAttrTab(j).from_currency);
612 PA_MCB_REVENUE_PKG.log_message('UTL' || CurrAttrTab(j).from_currency);
613 END IF;
614
615 If CurrAttrTab(j).from_currency = p_from_currency_tab(i) and
616 CurrAttrTab(j).to_currency = p_to_currency_tab(i) and
617 CurrAttrTab(j).conv_date = p_conversion_date_tab(i) and /* Condition added for bug 5907315 */
618 CurrAttrTab(j).conv_between = p_conversion_between then
619
620 --PA_MCB_REVENUE_PKG.log_message('UTL in if 2' || to_char(CurrAttrTab(j).rate));
621 p_rate_tab(i) := CurrAttrTab(j).rate;
622 p_numerator_tab(i) := CurrAttrTab(j).numerator ;
623 p_denominator_tab(i) := CurrAttrTab(j).denominator ;
624 /*
625 p_converted_amount_tab(i) := round_trans_currency_amt(
626 p_amount_tab(i) * p_rate_tab(i), p_to_currency_tab(i)) ;
627 */
628 p_converted_amount_tab(i) := round_trans_currency_amt(
629 p_amount_tab(i) * p_numerator_tab(i)/p_denominator_tab(i), p_to_currency_tab(i)) ;
630 l_done_flag := 'Y';
631
632 exit;
633
634 end if;
635
636 End loop;
637
638 end if ;
639
640 END IF;
641
642 IF l_done_flag = 'N' then
643
644 IF ( p_conversion_type_tab(i) = 'User') THEN
645
646 IF ( p_user_validate_flag_tab(i) = 'Y') THEN
647
648 l_allow_user_rate_type := pa_multi_currency.is_user_rate_type_allowed (
649 p_from_currency_tab(i),
650 p_to_currency_tab(i) ,
651 NVL(p_conversion_date_tab(i),sysdate)) ;
652
653 IF ( l_allow_user_rate_type = 'Y') then
654
655 p_converted_amount_tab(i) := round_trans_currency_amt
656 (p_amount_tab(i) * NVL(p_rate_tab(i),1),
657 p_to_currency_tab(i)) ;
658 p_denominator_tab(i) := 1 ;
659 p_numerator_tab(i) := NVL(p_rate_tab(i),1) ;
660
661 IF p_cache_flag = 'Y' then
662
663 l_AttrTab_count := l_AttrTab_Count + 1;
664 CurrAttrTab(l_AttrTab_Count).conv_between := p_conversion_between;
665 CurrAttrTab(l_AttrTab_Count).from_currency := p_from_currency_tab(i);
666 CurrAttrTab(l_AttrTab_Count).to_currency := p_to_currency_tab(i);
667 CurrAttrTab(l_AttrTab_Count).rate := nvl(p_rate_tab(i),1);
668 CurrAttrTab(l_AttrTab_Count).numerator := p_numerator_tab(i);
669 CurrAttrTab(l_AttrTab_Count).conv_date := p_conversion_date_tab(i); /* Code added for bug 5907315 */
670 CurrAttrTab(l_AttrTab_Count).denominator := 1 ;
671
672 END IF;
673
674 ELSE
675
676 x_status_tab(i) := 'PA_USR_RATE_NOT_ALLOWED_' || p_conversion_between;
677
678
679 END IF;
680
681 ELSE
682
683 p_converted_amount_tab(i) := round_trans_currency_amt
684 (p_amount_tab(i) * p_rate_tab(i), p_to_currency_tab(i)) ;
685 p_denominator_tab(i) := 1 ;
686 p_numerator_tab(i) := p_rate_tab(i) ;
687
688 IF p_cache_flag = 'Y' then
689
690 l_AttrTab_count := l_AttrTab_Count + 1;
691 CurrAttrTab(l_AttrTab_Count).conv_between := p_conversion_between;
692 CurrAttrTab(l_AttrTab_Count).from_currency := p_from_currency_tab(i);
693 CurrAttrTab(l_AttrTab_Count).to_currency := p_to_currency_tab(i);
694 CurrAttrTab(l_AttrTab_Count).rate := p_rate_tab(i);
695 CurrAttrTab(l_AttrTab_Count).numerator := p_numerator_tab(i);
696 CurrAttrTab(l_AttrTab_Count).conv_date := p_conversion_date_tab(i); /* Code added for bug 5907315 */
697 CurrAttrTab(l_AttrTab_Count).denominator := 1 ;
698
699 END IF;
700
701 END IF;
702
703 ELSE
704
705 p_conversion_date_tab(i) := NVL(p_conversion_date_tab(i), sysdate);
706 l_converted_amount := GL_CURRENCY_API.convert_amount_sql
707 ( p_from_currency_tab(i) ,
708 p_to_currency_tab(i) ,
709 p_conversion_date_tab(i) ,
710 p_conversion_type_tab(i) ,
711 p_amount_tab(i) ) ;
712
713 IF ( l_converted_amount = -1 ) THEN
714
715 x_status_tab(i) := 'PA_NO_EXCH_RATE_EXISTS_'|| p_conversion_between;
716
717 ELSIF ( l_converted_amount = -2 ) THEN
718
719 x_status_tab(i) := 'PA_CURR_NOT_VALID_' || p_conversion_between;
720
721 ELSE
722
723 p_converted_amount_tab(i) := l_converted_amount ;
724
725
726 l_numerator := GL_CURRENCY_API.get_rate_numerator_sql(
727 p_from_currency_tab(i),
728 p_to_currency_tab(i),
729 p_conversion_date_tab(i),
730 p_conversion_type_tab(i) );
731
732 p_numerator_tab(i) := l_numerator ;
733
734 l_denominator := GL_CURRENCY_API.get_rate_denominator_sql(
735 p_from_currency_tab(i),
736 p_to_currency_tab(i),
737 p_conversion_date_tab(i),
738 p_conversion_type_tab(i) );
739
740 p_denominator_tab(i) := l_denominator ;
741
742 -- Get conversion rate by using the x_numerator and x_denominator
743
744 IF (( p_numerator_tab(i) > 0 ) AND ( p_denominator_tab(i) > 0 )) THEN
745
746 p_rate_tab(i) := round(p_numerator_tab(i) / p_denominator_tab(i),20);
747
748 IF p_cache_flag = 'Y' then
749
750 l_AttrTab_count := l_AttrTab_Count + 1;
751 CurrAttrTab(l_AttrTab_Count).conv_between := p_conversion_between;
752 CurrAttrTab(l_AttrTab_Count).from_currency := p_from_currency_tab(i);
753 CurrAttrTab(l_AttrTab_Count).to_currency := p_to_currency_tab(i);
754 CurrAttrTab(l_AttrTab_Count).rate := p_rate_tab(i);
755 CurrAttrTab(l_AttrTab_Count).numerator := p_numerator_tab(i);
756 CurrAttrTab(l_AttrTab_Count).conv_date := p_conversion_date_tab(i); /* Code added for bug 5907315 */
757 CurrAttrTab(l_AttrTab_Count).denominator := p_denominator_tab(i) ;
758
759 END IF;
760
761 ELSE
762
763 IF (( p_numerator_tab(i) = -2 ) OR
764 (p_denominator_tab(i) = -2 )) THEN
765
766 x_status_tab(i) := 'PA_CURR_NOT_VALID_' || p_conversion_between;
767
768 ELSE
769
770 x_status_tab(i) := 'PA_NO_EXCH_RATE_EXISTS_' || p_conversion_between;
771
772 END IF;
773
774 END IF;
775
776 END IF ;
777
778 END IF;
779
780 END IF ;
781
782 END IF ;
783
784 END LOOP;
785
786 EXCEPTION
787
788 WHEN others THEN
789 FND_MSG_PUB.add_Exc_msg(
790 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
791 p_procedure_name => 'convert_amount_bulk');
792
793 RAISE ;
794
795 END convert_amount_bulk;
796
797 /*----------------------------------------------------------------------------------------+
798 | Procedure : init_cache |
799 | Purpose : This procedure sets the rounding precision attributes of a project for |
800 | project, project functional, invoice revenue processing currency |
801 | Parameters : |
802 | ================================================================================== |
803 | Name Mode Description |
804 | ================================================================================== |
805 | p_project_id IN Project ID |
806 | ================================================================================== |
807 +----------------------------------------------------------------------------------------*/
808 PROCEDURE init_cache (p_project_id IN NUMBER) IS
809
810
811 l_prectab_count NUMBER;
812
813 l_CurrCodeTab PA_PLSQL_DATATYPES.Char30TabTyp;
814 l_MauTab PA_PLSQL_DATATYPES.NumTabTyp;
815 l_SpTab PA_PLSQL_DATATYPES.NumTabTyp;
816 l_EpTab PA_PLSQL_DATATYPES.NumTabTyp;
817
818 lv_found_flag VARCHAR2(1);
819
820 cursor prec_info is
821 SELECT FC.Currency_Code currency_code,
822 FC.Minimum_Accountable_Unit mau,
823 FC.Precision sp,
824 FC.Extended_Precision ep
825 FROM FND_CURRENCIES FC
826 WHERE EXISTS
827 (SELECT null FROM
828 pa_projects_all pr
829 where pr.project_id = p_project_id AND
830 fc.currency_code in (pr.project_currency_code,
831 pr.projfunc_currency_code)
832 UNION
833 SELECT null from
834 PA_SUMMARY_PROJECT_FUNDINGS spf
835 WHERE project_id = p_project_id
836 AND spf.funding_currency_code = fc.currency_code
837 AND spf.total_baselined_amount <> 0 )
838 ORDER BY currency_code;
839
840 BEGIN
841
842 CurrAttrTab.delete;
843 l_prectab_count := CurrPrecTab.count;
844
845 OPEN prec_info;
846
847 LOOP
848 FETCH prec_info BULK COLLECT INTO l_CurrCodeTab,
849 l_MauTab, l_SpTab, l_EpTab;
850 IF l_CurrCodeTab.COUNT =0 THEN
851
852 EXIT;
853
854 END IF;
855
856 for i in l_CurrCodeTab.first..l_CurrCodeTab.Last loop
857
858 lv_found_flag := 'N';
859
860 IF CurrPrecTab.Count <> 0 then
861
862 for j in CurrPrecTab.first..CurrPrecTab.Last loop
863
864 if l_CurrCodeTab(i) = CurrPrecTab(j).curr_code then
865
866 lv_found_flag := 'Y';
867
868 exit;
869
870 end if;
871
872 END LOOP;
873
874 end if;
875
876 if lv_found_flag = 'N' then
877
878 l_prectab_count := l_prectab_count + 1;
879
880 CurrPrecTab(l_prectab_count).curr_code := l_CurrCodeTab(i);
881 CurrPrecTab(l_prectab_count).mau := l_MauTab(i);
882 CurrPrecTab(l_prectab_count).sp := l_SpTab(i);
883 CurrPrecTab(l_prectab_count).ep := l_EpTab(i);
884
885 end if;
886
887 END LOOP;
888
889 l_CurrCodeTab.delete;
890 l_MauTab.delete;
891 l_SpTab.delete;
892 l_EpTab.delete;
893
894 END LOOP;
895
896 END init_cache;
897
898 /*----------------------------------------------------------------------------------------+
899 | Procedure : get_trans_currency_info |
900 | Purpose : This procedure gets the rounding precision attributes of a currency |
901 | Parameters : |
902 | ================================================================================== |
903 | Name Mode Description |
904 | ================================================================================== |
905 | p_curr_code IN Currency Code |
906 | ================================================================================== |
907 +----------------------------------------------------------------------------------------*/
908 PROCEDURE Get_Trans_Currency_Info (p_curr_code IN varchar2, x_mau out NOCOPY number,
909 x_sp out NOCOPY number, x_ep out NOCOPY number) IS
910 BEGIN
911
912 SELECT FC.Minimum_Accountable_Unit,
913 FC.Precision,
914 FC.Extended_Precision
915 INTO x_mau,
916 x_sp,
917 x_ep
918 FROM FND_CURRENCIES FC
919 WHERE FC.Currency_Code = p_curr_code;
920
921 END Get_Trans_Currency_Info;
922
923
924 /*----------------------------------------------------------------------------------------+
925 | Function : round_trans_currency_amt |
926 | Purpose : The round_trans_currency_amt returns the round off amount based on the |
927 | currency code |
928 | Parameters : |
929 | ================================================================================== |
930 | Name Mode Description |
931 | ================================================================================== |
932 | p_amount IN The amount to be rounded |
933 | p_curr_code IN Currency Code |
934 | ================================================================================== |
935 | Returns : Number
936 +----------------------------------------------------------------------------------------*/
937 FUNCTION round_trans_currency_amt ( p_amount IN NUMBER,
938 p_Curr_Code IN VARCHAR2 ) RETURN NUMBER
939 IS
940
941 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
942 l_sp fnd_currencies.precision%TYPE;
943 l_ep fnd_currencies.extended_precision%TYPE;
944
945 l_prectab_count NUMBER;
946 l_found_flag VARCHAR2(1):= 'F';
947
948 BEGIN
949
950 l_found_flag := 'F';
951
952 l_prectab_count := CurrPrecTab.count;
953
954 IF CurrPrecTab.Count <> 0 then
955
956 FOR i in CurrPrecTab.first..CurrPrecTab.last loop
957
958 if CurrPrecTab(i).curr_code = p_Curr_Code then
959
960 l_found_flag := 'T';
961 l_mau := CurrPrecTab(i).mau;
962 l_sp := CurrPrecTab(i).sp ;
963 l_ep := CurrPrecTab(i).ep ;
964
965 exit;
966
967 end if;
968
969 end loop;
970
971 end if;
972
973 if l_found_flag = 'F' THEN
974
975 l_prectab_count := l_prectab_count + 1;
976
977 Get_Trans_Currency_Info(
978 p_curr_code => p_curr_code,
979 x_mau => l_mau,
980 x_sp => l_sp,
981 x_ep => l_ep);
982
983 CurrPrecTab(l_prectab_count).curr_code := p_curr_code;
984 CurrPrecTab(l_prectab_count).mau := l_mau;
985 CurrPrecTab(l_prectab_count).sp := l_sp;
986 CurrPrecTab(l_prectab_count).ep := l_ep;
987
988 end if;
989
990 IF l_mau IS NOT NULL THEN
991
992 IF l_mau < 0.00001 THEN
993 RETURN( round(p_Amount, 5));
994 ELSE
995 RETURN( round(p_Amount/l_mau) * l_mau );
996 END IF;
997
998 ELSIF l_sp IS NOT NULL THEN
999
1000 IF l_sp > 5 THEN
1001 RETURN( round(p_Amount, 5));
1002 ELSE
1003 RETURN( round(p_Amount, l_sp));
1004 END IF;
1005
1006 ELSE
1007
1008 RETURN( round(p_Amount, 5));
1009
1010 END IF;
1011
1012 END round_trans_currency_amt;
1013
1014
1015 Function check_mcb_trans_exist (p_project_id IN NUMBER) return varchar2 is
1016
1017 l_baseline_amount number;
1018
1019 lv_return_flag varchar2(1);
1020
1021
1022 BEGIN
1023
1024 lv_return_flag := 'N';
1025 -- check to see if any non zero baselined funding records exists
1026
1027 SELECT sum(nvl(allocated_amount,0))
1028 INTO l_baseline_amount
1029 FROM PA_PROJECT_FUNDINGS P
1030 WHERE P.PROJECT_ID = p_project_id
1031 AND budget_type_code = 'BASELINE';
1032
1033 if l_baseline_amount <> 0 then
1034
1035 lv_return_flag := 'Y';
1036 return (lv_return_flag);
1037
1038 end if;
1039
1040 -- check to see if any draft funding records exists
1041
1042 begin
1043
1044 /* Modified the code for bug 3686650
1045 SELECT 'Y' into lv_return_flag
1046 FROM PA_PROJECT_FUNDINGS P
1047 WHERE P.PROJECT_ID = p_project_id
1048 AND budget_type_code = 'DRAFT';
1049 */
1050 SELECT 'Y' into lv_return_flag FROM dual
1051 WHERE exists
1052 (SELECT project_id
1053 FROM pa_project_fundings
1054 WHERE project_id = p_project_id
1055 AND budget_type_code = 'DRAFT');
1056
1057 return (lv_return_flag);
1058
1059 exception
1060
1061 when others then
1062
1063 lv_return_flag := 'N';
1064
1065 end;
1066
1067 -- check to see if any Event records exists
1068
1069 begin
1070
1071 SELECT 'Y' into lv_return_flag from dual
1072 where exists
1073 (select project_id
1074 from pa_events
1075 where project_id = p_project_id);
1076
1077 return (lv_return_flag);
1078
1079 exception
1080
1081 when others then
1082
1083 lv_return_flag := 'N';
1084
1085 end;
1086
1087 -- check to see if any EI records exists
1088
1089 begin
1090
1091 /*Commented for bug 3088683
1092 SELECT 'Y' into lv_return_flag from dual
1093 where exists
1094 (select T.project_id
1095 from pa_expenditure_items_all E, pa_tasks T
1096 where T.project_id = p_project_id
1097 and E.task_id = T.task_id);
1098 End of comment for bug 3088683 */
1099
1100 /*Added for bug 3088683 */
1101
1102 SELECT 'Y' into lv_return_flag from dual
1103 where exists
1104 (select E.project_id
1105 from pa_expenditure_items_all E
1106 where E.project_id = p_project_id);
1107
1108 /*End of change for bug 3088683 */
1109
1110 return (lv_return_flag);
1111
1112 exception
1113
1114 when others then
1115
1116 lv_return_flag := 'N';
1117
1118 end;
1119
1120 return (lv_return_flag);
1121
1122 EXCEPTION
1123
1124 when others then
1125
1126 return 'Y';
1127
1128 END check_mcb_trans_exist;
1129
1130 PROCEDURE get_project_types_dflt(
1131 p_project_type IN VARCHAR2,
1132 x_baseline_flag OUT NOCOPY VARCHAR2,
1133 x_nl_rt_sch_id OUT NOCOPY NUMBER,
1134 x_nl_rt_sch_name OUT NOCOPY VARCHAR2,
1135 x_rate_sch_currency_code OUT NOCOPY VARCHAR2 ) IS
1136
1137
1138 cursor cur_proj_types IS
1139 SELECT ppt.baseline_funding_flag
1140 ,ppt.NON_LAB_STD_BILL_RT_SCH_ID
1141 ,brs.std_bill_rate_schedule
1142 ,brs.rate_sch_currency_code
1143 FROM pa_project_types ppt, pa_std_bill_rate_schedules brs
1144 WHERE ppt.project_type = p_project_type
1145 AND ppt.non_lab_std_bill_rt_sch_id = brs.bill_rate_sch_id(+);
1146
1147 BEGIN
1148
1149
1150
1151 OPEN cur_proj_types;
1152 FETCH cur_proj_types INTO x_baseline_flag, x_nl_rt_sch_id,
1153 x_nl_rt_sch_name, x_rate_sch_currency_code;
1154 CLOSE cur_proj_types;
1155
1156 /* Added the below for NOCOPY mandate */
1157 EXCEPTION WHEN OTHERS THEN
1158 x_baseline_flag := NULL;
1159 x_nl_rt_sch_id := NULL;
1160 x_nl_rt_sch_name := NULL;
1161 x_rate_sch_currency_code := NULL;
1162 raise;
1163 END get_project_types_dflt;
1164
1165
1166 FUNCTION check_cross_ou_fund_exist RETURN VARCHAR2 IS
1167
1168 lv_return_flag varchar2(1);
1169
1170 BEGIN
1171
1172 SELECT 'Y' INTO lv_return_flag FROM DUAL
1173 WHERE EXISTS ( SELECT spf.project_id
1174 FROM pa_summary_project_fundings spf, pa_agreements a,
1175 pa_projects_all P
1176 WHERE spf.agreement_id = a.agreement_id
1177 AND spf.project_id = p.project_id
1178 AND a.org_id <> p.org_id);
1179
1180
1181 RETURN (lv_return_flag);
1182
1183 EXCEPTION
1184
1185 WHEN OTHERS THEN
1186
1187 lv_return_flag := 'N';
1188 RETURN (lv_return_flag);
1189
1190 END check_cross_ou_fund_exist;
1191
1192
1193 FUNCTION check_cross_ou_billrate_exist RETURN VARCHAR2 IS
1194
1195 lv_return_flag varchar2(1);
1196
1197 BEGIN
1198
1199 SELECT 'Y' INTO lv_return_flag FROM DUAL
1200 WHERE EXISTS (SELECT p.project_type_id
1201 FROM pa_std_bill_rate_schedules_all br, pa_project_types p
1202 WHERE ( p.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1203 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1204 OR ( p.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1205 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1206 OR ( p.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1207 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99)));
1208
1209 RETURN (lv_return_flag);
1210
1211 EXCEPTION
1212
1213 WHEN OTHERS THEN
1214
1215 BEGIN
1216
1217 SELECT 'Y' INTO lv_return_flag FROM DUAL
1218 WHERE EXISTS (SELECT p.project_id
1219 FROM pa_std_bill_rate_schedules_all br, pa_projects p
1220 WHERE ( p.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1221 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1222 OR ( p.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1223 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1224 OR ( p.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1225 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99)));
1226
1227 RETURN (lv_return_flag);
1228
1229 EXCEPTION
1230
1231 WHEN OTHERS THEN
1232
1233 BEGIN
1234
1235 /* Fix for Performance bug 4939354
1236
1237 SELECT 'Y' INTO lv_return_flag FROM DUAL
1238 WHERE EXISTS (SELECT p.project_id
1239 FROM pa_std_bill_rate_schedules_all br, pa_projects p,
1240 pa_tasks t
1241 WHERE p.project_id = t.project_id
1242 AND ( ( t.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1243 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1244 OR ( t.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1245 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1246 OR ( t.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1247 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))));
1248 */
1249 SELECT 'Y' INTO lv_return_flag FROM DUAL
1250 WHERE EXISTS (SELECT p.project_id
1251 FROM pa_projects p, pa_tasks t
1252 WHERE p.project_id = t.project_id
1253 AND EXISTS
1254 (select null
1255 FROM pa_std_bill_rate_schedules_all br
1256 WHERE ( (t.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1257 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1258 OR ( t.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1259 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1260 OR ( t.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1261 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1262 )));
1263
1264 RETURN (lv_return_flag);
1265
1266 EXCEPTION
1267
1268 WHEN OTHERS THEN
1269
1270 lv_return_flag := 'N';
1271 RETURN (lv_return_flag);
1272 END;
1273 END;
1274
1275 END check_cross_ou_billrate_exist;
1276
1277 Function is_baseline_funding_enabled (p_project_id IN NUMBER) return varchar2 is
1278
1279 lv_baseline_flag VARCHAR2(1);
1280
1281 begin
1282
1283 select baseline_funding_flag into lv_baseline_flag
1284 from pa_projects_all
1285 where project_id = p_project_id;
1286
1287 return lv_baseline_flag;
1288
1289 exception
1290
1291 when others then
1292
1293 return 'N';
1294
1295 end is_baseline_funding_enabled;
1296
1297 FUNCTION proj_cust_curr( p_project_id VARCHAR2,
1298 p_curr_code VARCHAR2 ) return VARCHAR2
1299 is
1300
1301 CURSOR cur_proj_cust IS
1302 SELECT 'x'
1303 FROM pa_project_customers
1304 WHERE INV_CURRENCY_CODE = p_curr_code
1305 AND project_id = p_project_id;
1306
1307 v_dummy_char VARCHAR2(1);
1308 begin
1309
1310 open cur_proj_cust;
1311 fetch cur_proj_cust into v_dummy_char;
1312
1313 IF cur_proj_cust%FOUND THEN
1314 CLOSE cur_proj_cust;
1315 RETURN 'N';
1316 ELSE
1317 CLOSE cur_proj_cust;
1318 RETURN 'Y';
1319 END IF;
1320 end proj_cust_curr;
1321
1322 ----------------------------------------------------------------------------------
1323 -- Purpose: This function will return value 'Y' if Project Functional Currency
1324 -- is not the same as that of invoice currency.
1325 --
1326 -- Inputs: Project_ID and Project_Functional_Currency_Code
1327 ----------------------------------------------------------------------------------
1328 FUNCTION MCB_Flag_Required(
1329 P_Project_ID IN PA_PROJECTS_ALL.Project_ID%TYPE,
1330 P_PFC_Currency_Code IN PA_PROJECTS_ALL.ProjFunc_Currency_Code%TYPE
1331 )
1332 RETURN VARCHAR2
1333 IS
1334
1335 l_Flag VARCHAR2(1);
1336 BEGIN
1337 -- Check whether the given Project has any Project Customers with
1338 -- different currency code, other than the Given Currency Code
1339 BEGIN
1340 SELECT 'Y'
1341 INTO l_Flag
1342 FROM Dual
1343 WHERE Exists ( SELECt 1 FROM PA_Project_Customers
1344 WHERE Project_ID = p_Project_ID
1345 AND Inv_Currency_Code <> p_pfc_Currency_Code);
1346 EXCEPTION WHEN NO_DATA_FOUND THEN
1347 l_Flag := 'N';
1348 END;
1349
1350 -- Check whether the given Project has any Invoice transactions having
1351 -- different currency code, other than the Given Currency Code
1352 IF l_Flag = 'N'
1353 THEN
1354 BEGIN
1355 SELECT 'Y'
1356 INTO l_Flag
1357 FROM Dual
1358 WHERE Exists ( SELECT 1 FROM PA_Draft_Invoices_All
1359 WHERE Project_ID = p_Project_ID
1360 AND Inv_Currency_Code <> p_pfc_Currency_Code);
1361 EXCEPTION WHEN NO_DATA_FOUND THEN
1362 l_Flag := 'N';
1363 END;
1364 END IF;
1365
1366 RETURN l_Flag ; -- Return 'Y' if yes else 'N' if No, after checking above conditions
1367
1368 END MCB_Flag_Required;
1369
1370
1371 FUNCTION get_currency( P_org_id IN pa_implementations_all.org_id%TYPE)
1372 RETURN VARCHAR2
1373 IS
1374
1375 BEGIN
1376
1377 return( G_Curr_Tab(P_org_id));
1378
1379 EXCEPTION WHEN others THEN
1380 raise;
1381 END get_currency;
1382
1383
1384 FUNCTION Check_update_ou_mcb_flag RETURN VARCHAR2 IS
1385
1386 lv_return_flag varchar2(1);
1387 /* Added local variable l_currency_code for bug 2872748 */
1388 l_currency_code fnd_currencies.currency_code%TYPE;
1389 BEGIN
1390
1391 /* Code addition for bug 2872748 starts */
1392 begin
1393 select pa_currency.get_currency_code
1394 into l_currency_code
1395 from dual ;
1396 end;
1397 /* Code addition for bug 2872748 ends */
1398
1399 SELECT 'N' INTO lv_return_flag FROM DUAL
1400 WHERE EXISTS ( SELECT a.agreement_id
1401 FROM pa_agreements a
1402 WHERE a.agreement_currency_code <> l_currency_code);
1403 /* for bug 2872748 replaced pa_currency.get_currency_code with l_currency_code in above sql */
1404
1405 RETURN (lv_return_flag);
1406
1407 EXCEPTION
1408
1409 WHEN OTHERS THEN
1410
1411 BEGIN
1412
1413 SELECT 'N' INTO lv_return_flag FROM DUAL
1414 WHERE EXISTS ( SELECT b.bill_rate_sch_id
1415 /* Commented for bug 2867740
1416 FROM pa_bill_rates b
1417 WHERE b.RATE_CURRENCY_CODE <> pa_currency.get_currency_code); */
1418 /* Bug fix for bug 2867740 Starts Here */
1419 FROM pa_std_bill_rate_schedules b
1420 WHERE b.RATE_SCH_CURRENCY_CODE <> pa_currency.get_currency_code);
1421 /* Bug fix for bug 2867740 Ends Here */
1422 RETURN (lv_return_flag);
1423 EXCEPTION
1424
1425 WHEN OTHERS THEN
1426
1427 lv_return_flag := 'Y';
1428 RETURN (lv_return_flag);
1429 END;
1430
1431
1432 END Check_update_ou_mcb_flag;
1433
1434 Function check_mcb_setup_exists (p_project_id IN NUMBER) return varchar2 is
1435
1436 lv_return_flag varchar2(1);
1437 lv_projfunc_currency varchar2(15);
1438
1439 BEGIN
1440 lv_return_flag := 'N';
1441
1442 SELECT projfunc_currency_code into lv_projfunc_currency
1443 FROM pa_projects
1444 WHERE project_id = p_project_id;
1445
1446 BEGIN
1447 SELECT 'Y' into lv_return_flag from dual
1448 where exists (select null
1449 from pa_billing_assignments asg
1450 /* where nvl(asg.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002*/
1451 where asg.project_id = p_project_id
1452 and asg.project_id IS NOT NULL
1453 and asg.rate_override_currency_code <> lv_projfunc_currency);
1454 RETURN (lv_return_flag);
1455
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 lv_return_flag := 'N';
1459 END;
1460
1461 BEGIN
1462 SELECT 'Y' into lv_return_flag from dual
1463 where exists (select null
1464 FROM pa_job_bill_rate_overrides jbr
1465 /* where nvl(jbr.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002 */
1466 where jbr.project_id = p_project_id
1467 and jbr.project_id IS NOT NULL
1468 and jbr.rate_currency_code <> lv_projfunc_currency);
1469
1470 RETURN (lv_return_flag);
1471
1472 EXCEPTION
1473 WHEN OTHERS THEN
1474 lv_return_flag := 'N';
1475 END;
1476
1477 BEGIN
1478 SELECT 'Y' into lv_return_flag from dual
1479 where exists (select null
1480 FROM pa_nl_bill_rate_overrides nlr
1481 /* where nvl(nlr.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002 */
1482 where nlr.project_id = p_project_id
1483 and nlr.project_id IS NOT NULL
1484 and nlr.rate_currency_code <> lv_projfunc_currency);
1485
1486 RETURN (lv_return_flag);
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 lv_return_flag := 'N';
1491 END;
1492
1493
1494 BEGIN
1495 SELECT 'Y' into lv_return_flag from dual
1496 where exists (select null
1497 FROM pa_emp_bill_rate_overrides emp
1498 /* where nvl(emp.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002 */
1499 where emp.project_id = p_project_id
1500 and emp.project_id IS NOT NULL
1501 and emp.rate_currency_code <> lv_projfunc_currency);
1502
1503 RETURN (lv_return_flag);
1504
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 lv_return_flag := 'N';
1508 END;
1509
1510
1511 BEGIN
1512 SELECT 'Y' into lv_return_flag from dual
1513 where exists (select null
1514 FROM PA_STD_BILL_RATE_SCHEDULES_all br, pa_projects p
1515 where p.project_id = p_project_id
1516 and br.BILL_RATE_SCH_ID in
1517 (nvl(p.job_bill_rate_schedule_id,-99),
1518 nvl(p.emp_bill_rate_schedule_id,-99),
1519 nvl(p.non_lab_std_bill_rt_sch_id,-99))
1520 and br.RATE_SCH_CURRENCY_CODE <> lv_projfunc_currency);
1521
1522 RETURN (lv_return_flag);
1523
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 lv_return_flag := 'N';
1527 END;
1528 --- task level overrides
1529
1530
1531 BEGIN
1532 SELECT 'Y' into lv_return_flag from dual
1533 where exists (select null
1534 from pa_billing_assignments asg, pa_tasks t
1535 where t.project_id = p_project_id
1536 /* and nvl(asg.top_task_id,-99) = t.task_id Bug 2702200 Modified on 20/12/2002 */
1537 and asg.top_task_id = t.task_id
1538 and asg.project_id = t.project_id -- added for bug 3517177
1539 and asg.top_task_id IS NOT NULL
1540 and asg.rate_override_currency_code <>lv_projfunc_currency);
1541
1542 RETURN (lv_return_flag);
1543
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 lv_return_flag := 'N';
1547 END;
1548
1549 BEGIN
1550 SELECT 'Y' into lv_return_flag from dual
1551 where exists (select null
1552 FROM pa_job_bill_rate_overrides jbr, pa_tasks t
1553 where t.project_id = p_project_id
1554 /* and nvl(jbr.task_id,-99) = t.task_id Bug 2702200 Modified on 20/12/2002 */
1555 and jbr.task_id = t.task_id
1556 and jbr.task_id IS NOT NULL
1557 and jbr.rate_currency_code <> lv_projfunc_currency);
1558
1559 RETURN (lv_return_flag);
1560
1561 EXCEPTION
1562 WHEN OTHERS THEN
1563 lv_return_flag := 'N';
1564 END;
1565
1566 BEGIN
1567 SELECT 'Y' into lv_return_flag from dual
1568 where exists (select null
1569 FROM pa_nl_bill_rate_overrides nlr, pa_tasks t
1570 where t.project_id = p_project_id
1571 /* and nvl(nlr.task_id,-99) = t.task_id Modifed on 20/12/2002 */
1572 and nlr.task_id = t.task_id
1573 and nlr.task_id IS NOT NULL
1574 and nlr.rate_currency_code <> lv_projfunc_currency);
1575
1576 RETURN (lv_return_flag);
1577
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 lv_return_flag := 'N';
1581 END;
1582
1583
1584 BEGIN
1585 SELECT 'Y' into lv_return_flag from dual
1586 where exists (select null
1587 FROM pa_emp_bill_rate_overrides emp, pa_tasks t
1588 where t.project_id = p_project_id
1589 /* and nvl(emp.task_id,-99) = t.task_id Modifed on 20/12/2002*/
1590 and emp.task_id = t.task_id
1591 and emp.task_id IS NOT NULL
1592 and emp.rate_currency_code <> lv_projfunc_currency);
1593
1594 RETURN (lv_return_flag);
1595
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 lv_return_flag := 'N';
1599 END;
1600
1601
1602 BEGIN
1603 SELECT 'Y' into lv_return_flag from dual
1604 where exists (select null
1605 FROM PA_STD_BILL_RATE_SCHEDULES_all br, pa_tasks t
1606 where t.project_id = p_project_id
1607 and br.BILL_RATE_SCH_ID in
1608 (nvl(t.job_bill_rate_schedule_id,-99),
1609 nvl(t.emp_bill_rate_schedule_id,-99),
1610 nvl(t.non_lab_std_bill_rt_sch_id, -99))
1611 and br.RATE_SCH_CURRENCY_CODE <> lv_projfunc_currency);
1612
1613 RETURN (lv_return_flag);
1614
1615 EXCEPTION
1616 WHEN OTHERS THEN
1617 lv_return_flag := 'N';
1618 END;
1619
1620 return (lv_return_flag);
1621
1622 EXCEPTION
1623
1624 WHEN OTHERS THEN
1625 lv_return_flag := 'N';
1626 return (lv_return_flag);
1627
1628 END check_mcb_setup_exists;
1629
1630 /* Added the given below procedure for Enhancement bug 2520222
1631 It is being called from customer window of project form.
1632 This procedure will check if the assigned customer is having valid funding lines and
1633 user is trying to change existing contribution from non zero to zero then it will give error.*/
1634
1635 Procedure Check_Cust_Funding_Exists(
1636 p_proj_customer_id IN NUMBER,
1637 p_project_id IN NUMBER,
1638 p_cust_contribution IN NUMBER,
1639 x_return_status OUT NOCOPY VARCHAR2,
1640 x_msg_data OUT NOCOPY VARCHAR2,
1641 x_msg_count OUT NOCOPY NUMBER
1642 )
1643 IS
1644 CURSOR C_fund IS
1645 SELECT 'x'
1646 FROM pa_agreements a,
1647 pa_summary_project_fundings f
1648 WHERE a.customer_id = p_proj_customer_id
1649 AND a.agreement_id = f.agreement_id
1650 AND f.project_id = p_project_id
1651 AND ( f.total_unbaselined_amount <>0
1652 OR f.total_baselined_amount <> 0);
1653
1654 x_funding_exists VARCHAR2(1):= NULL;
1655 BEGIN
1656
1657 x_return_status := 'S'; -- FND_API.G_RET_STS_SUCCESS;
1658
1659 IF ( p_cust_contribution = 0 ) THEN
1660
1661 OPEN C_fund;
1662 FETCH C_fund
1663 INTO x_funding_exists;
1664 CLOSE C_fund;
1665
1666 IF (x_funding_exists IS NOT NULL) THEN
1667 x_return_status := 'E'; -- FND_API.G_RET_STS_ERROR;
1668 x_msg_data := 'PA_BILL_CUST_CONTR_ZERO';
1669 x_msg_count := 1;
1670 ELSE
1671 x_return_status := 'S'; -- FND_API.G_RET_STS_SUCCESS;
1672 END IF;
1673
1674 END IF;
1675 EXCEPTION
1676 WHEN OTHERS THEN
1677 x_return_status := 'U'; -- FND_API.G_RET_STS_UNEXP_ERROR;
1678 x_msg_data := SUBSTR(SQLERRM,1,50);
1679 x_msg_count := 1;
1680
1681 END Check_Cust_Funding_Exists;
1682
1683 /* till here */
1684
1685 END pa_multi_currency_billing;