[Home] [Help]
PACKAGE BODY: APPS.PA_MULTI_CURRENCY_BILLING
Source
1 PACKAGE BODY pa_multi_currency_billing AS
2 --$Header: PAXMULTB.pls 120.6 2008/05/16 05:13:08 nkapling 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 l_Curr_Exists VARCHAR2(1); --Added for Bug 6645222
573
574 BEGIN
575
576 l_tab_count := p_from_currency_tab.COUNT;
577 l_AttrTab_count := CurrAttrTab.COUNT;
578 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* added for bug 6322049 */
579
580 IF l_tab_count = 0 then
581
582 RETURN;
583
584 END IF;
585
586 FOR i in p_from_currency_tab.first..p_from_currency_tab.last LOOP
587
588 x_status_tab(i) := 'N';
589 l_done_flag := 'N';
590
591 IF (p_from_currency_tab(i) = p_to_currency_tab(i)) THEN
592
593 p_conversion_date_tab(i) := null;
594 p_conversion_type_tab(i) := null;
595 p_rate_tab(i) := null;
596 p_converted_amount_tab(i):= p_amount_tab(i);
597
598 ELSE
599
600 IF p_cache_flag = 'Y' then
601
602 --PA_MCB_REVENUE_PKG.log_message('MCB... Inside Convert amount bulk ');
603
604
605 IF nvl(CurrAttrTab.count,0) <> 0 THEN
606
607 --PA_MCB_REVENUE_PKG.log_message('UTL... in if');
608
609 For j in CurrAttrTab.First..CurrAttrTab.Last LOOP
610
611 IF l_debug_mode = 'Y' THEN /* added IF for bug 6322049 */
612 PA_MCB_REVENUE_PKG.log_message('UTL' || CurrAttrTab(j).from_currency);
613 PA_MCB_REVENUE_PKG.log_message('UTL' || CurrAttrTab(j).from_currency);
614 END IF;
615
616 If CurrAttrTab(j).from_currency = p_from_currency_tab(i) and
617 CurrAttrTab(j).to_currency = p_to_currency_tab(i) and
618 CurrAttrTab(j).conv_date = p_conversion_date_tab(i) and /* Condition added for bug 5907315 */
619 CurrAttrTab(j).conv_between = p_conversion_between then
620
621 --PA_MCB_REVENUE_PKG.log_message('UTL in if 2' || to_char(CurrAttrTab(j).rate));
622 p_rate_tab(i) := CurrAttrTab(j).rate;
623 p_numerator_tab(i) := CurrAttrTab(j).numerator ;
624 p_denominator_tab(i) := CurrAttrTab(j).denominator ;
625 /*
626 p_converted_amount_tab(i) := round_trans_currency_amt(
627 p_amount_tab(i) * p_rate_tab(i), p_to_currency_tab(i)) ;
628 */
629 p_converted_amount_tab(i) := round_trans_currency_amt(
630 p_amount_tab(i) * p_numerator_tab(i)/p_denominator_tab(i), p_to_currency_tab(i)) ;
631 l_done_flag := 'Y';
632
633 exit;
634
635 end if;
636
637 End loop;
638
639 end if ;
640
641 END IF;
642
643 IF l_done_flag = 'N' then
644
645 IF ( p_conversion_type_tab(i) = 'User') THEN
646
647 IF ( p_user_validate_flag_tab(i) = 'Y') THEN
648
649 l_allow_user_rate_type := pa_multi_currency.is_user_rate_type_allowed (
650 p_from_currency_tab(i),
651 p_to_currency_tab(i) ,
652 NVL(p_conversion_date_tab(i),sysdate)) ;
653
654 IF ( l_allow_user_rate_type = 'Y') then
655
656 p_converted_amount_tab(i) := round_trans_currency_amt
657 (p_amount_tab(i) * NVL(p_rate_tab(i),1),
658 p_to_currency_tab(i)) ;
659 p_denominator_tab(i) := 1 ;
660 p_numerator_tab(i) := NVL(p_rate_tab(i),1) ;
661
662 IF p_cache_flag = 'Y' then
663
664 l_AttrTab_count := l_AttrTab_Count + 1;
665 CurrAttrTab(l_AttrTab_Count).conv_between := p_conversion_between;
666 CurrAttrTab(l_AttrTab_Count).from_currency := p_from_currency_tab(i);
667 CurrAttrTab(l_AttrTab_Count).to_currency := p_to_currency_tab(i);
668 CurrAttrTab(l_AttrTab_Count).rate := nvl(p_rate_tab(i),1);
669 CurrAttrTab(l_AttrTab_Count).numerator := p_numerator_tab(i);
670 CurrAttrTab(l_AttrTab_Count).conv_date := p_conversion_date_tab(i); /* Code added for bug 5907315 */
671 CurrAttrTab(l_AttrTab_Count).denominator := 1 ;
672
673 END IF;
674
675 ELSE
676
677 x_status_tab(i) := 'PA_USR_RATE_NOT_ALLOWED_' || p_conversion_between;
678
679
680 END IF;
681
682 ELSE
683
684 p_converted_amount_tab(i) := round_trans_currency_amt
685 (p_amount_tab(i) * p_rate_tab(i), p_to_currency_tab(i)) ;
686 p_denominator_tab(i) := 1 ;
687 p_numerator_tab(i) := p_rate_tab(i) ;
688
689 IF p_cache_flag = 'Y' then
690
691 l_AttrTab_count := l_AttrTab_Count + 1;
692 CurrAttrTab(l_AttrTab_Count).conv_between := p_conversion_between;
693 CurrAttrTab(l_AttrTab_Count).from_currency := p_from_currency_tab(i);
694 CurrAttrTab(l_AttrTab_Count).to_currency := p_to_currency_tab(i);
695 CurrAttrTab(l_AttrTab_Count).rate := p_rate_tab(i);
696 CurrAttrTab(l_AttrTab_Count).numerator := p_numerator_tab(i);
697 CurrAttrTab(l_AttrTab_Count).conv_date := p_conversion_date_tab(i); /* Code added for bug 5907315 */
698 CurrAttrTab(l_AttrTab_Count).denominator := 1 ;
699
700 END IF;
701
702 END IF;
703
704 ELSE
705
706 p_conversion_date_tab(i) := NVL(p_conversion_date_tab(i), sysdate);
707 l_converted_amount := GL_CURRENCY_API.convert_amount_sql
708 ( p_from_currency_tab(i) ,
709 p_to_currency_tab(i) ,
710 p_conversion_date_tab(i) ,
711 p_conversion_type_tab(i) ,
712 p_amount_tab(i) ) ;
713
714 l_Curr_Exists := GL_CURRENCY_API.rate_exists
715 ( p_from_currency_tab(i) ,
716 p_to_currency_tab(i) ,
717 p_conversion_date_tab(i) ,
718 p_conversion_type_tab(i) ); --Added for Bug 6645222
719
720 IF ( l_converted_amount = -1 And l_Curr_Exists = 'N') THEN --Condition changed for Bug 6645222
721
722 x_status_tab(i) := 'PA_NO_EXCH_RATE_EXISTS_'|| p_conversion_between;
723
724 ELSIF ( l_converted_amount = -2 And l_Curr_Exists = 'N') THEN --Condition changed for Bug 6645222
725
726 x_status_tab(i) := 'PA_CURR_NOT_VALID_' || p_conversion_between;
727
728 ELSE
729
730 p_converted_amount_tab(i) := l_converted_amount ;
731
732
733 l_numerator := GL_CURRENCY_API.get_rate_numerator_sql(
734 p_from_currency_tab(i),
735 p_to_currency_tab(i),
736 p_conversion_date_tab(i),
737 p_conversion_type_tab(i) );
738
739 p_numerator_tab(i) := l_numerator ;
740
741 l_denominator := GL_CURRENCY_API.get_rate_denominator_sql(
742 p_from_currency_tab(i),
743 p_to_currency_tab(i),
744 p_conversion_date_tab(i),
745 p_conversion_type_tab(i) );
746
747 p_denominator_tab(i) := l_denominator ;
748
749 -- Get conversion rate by using the x_numerator and x_denominator
750
751 IF (( p_numerator_tab(i) > 0 ) AND ( p_denominator_tab(i) > 0 )) THEN
752
753 p_rate_tab(i) := round(p_numerator_tab(i) / p_denominator_tab(i),20);
754
755 IF p_cache_flag = 'Y' then
756
757 l_AttrTab_count := l_AttrTab_Count + 1;
758 CurrAttrTab(l_AttrTab_Count).conv_between := p_conversion_between;
759 CurrAttrTab(l_AttrTab_Count).from_currency := p_from_currency_tab(i);
760 CurrAttrTab(l_AttrTab_Count).to_currency := p_to_currency_tab(i);
761 CurrAttrTab(l_AttrTab_Count).rate := p_rate_tab(i);
762 CurrAttrTab(l_AttrTab_Count).numerator := p_numerator_tab(i);
763 CurrAttrTab(l_AttrTab_Count).conv_date := p_conversion_date_tab(i); /* Code added for bug 5907315 */
764 CurrAttrTab(l_AttrTab_Count).denominator := p_denominator_tab(i) ;
765
766 END IF;
767
768 ELSE
769
770 IF (( p_numerator_tab(i) = -2 ) OR
771 (p_denominator_tab(i) = -2 )) THEN
772
773 x_status_tab(i) := 'PA_CURR_NOT_VALID_' || p_conversion_between;
774
775 ELSE
776
777 x_status_tab(i) := 'PA_NO_EXCH_RATE_EXISTS_' || p_conversion_between;
778
779 END IF;
780
781 END IF;
782
783 END IF ;
784
785 END IF;
786
787 END IF ;
788
789 END IF ;
790
791 END LOOP;
792
793 EXCEPTION
794
795 WHEN others THEN
796 FND_MSG_PUB.add_Exc_msg(
797 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
798 p_procedure_name => 'convert_amount_bulk');
799
800 RAISE ;
801
802 END convert_amount_bulk;
803
804 /*----------------------------------------------------------------------------------------+
805 | Procedure : init_cache |
806 | Purpose : This procedure sets the rounding precision attributes of a project for |
807 | project, project functional, invoice revenue processing currency |
808 | Parameters : |
809 | ================================================================================== |
810 | Name Mode Description |
811 | ================================================================================== |
812 | p_project_id IN Project ID |
813 | ================================================================================== |
814 +----------------------------------------------------------------------------------------*/
815 PROCEDURE init_cache (p_project_id IN NUMBER) IS
816
817
818 l_prectab_count NUMBER;
819
820 l_CurrCodeTab PA_PLSQL_DATATYPES.Char30TabTyp;
821 l_MauTab PA_PLSQL_DATATYPES.NumTabTyp;
822 l_SpTab PA_PLSQL_DATATYPES.NumTabTyp;
823 l_EpTab PA_PLSQL_DATATYPES.NumTabTyp;
824
825 lv_found_flag VARCHAR2(1);
826
827 cursor prec_info is
828 SELECT FC.Currency_Code currency_code,
829 FC.Minimum_Accountable_Unit mau,
830 FC.Precision sp,
831 FC.Extended_Precision ep
832 FROM FND_CURRENCIES FC
833 WHERE EXISTS
834 (SELECT null FROM
835 pa_projects_all pr
836 where pr.project_id = p_project_id AND
837 fc.currency_code in (pr.project_currency_code,
838 pr.projfunc_currency_code)
839 UNION
840 SELECT null from
841 PA_SUMMARY_PROJECT_FUNDINGS spf
842 WHERE project_id = p_project_id
843 AND spf.funding_currency_code = fc.currency_code
844 AND spf.total_baselined_amount <> 0 )
845 ORDER BY currency_code;
846
847 BEGIN
848
849 CurrAttrTab.delete;
850 l_prectab_count := CurrPrecTab.count;
851
852 OPEN prec_info;
853
854 LOOP
855 FETCH prec_info BULK COLLECT INTO l_CurrCodeTab,
856 l_MauTab, l_SpTab, l_EpTab;
857 IF l_CurrCodeTab.COUNT =0 THEN
858
859 EXIT;
860
861 END IF;
862
863 for i in l_CurrCodeTab.first..l_CurrCodeTab.Last loop
864
865 lv_found_flag := 'N';
866
867 IF CurrPrecTab.Count <> 0 then
868
869 for j in CurrPrecTab.first..CurrPrecTab.Last loop
870
871 if l_CurrCodeTab(i) = CurrPrecTab(j).curr_code then
872
873 lv_found_flag := 'Y';
874
875 exit;
876
877 end if;
878
879 END LOOP;
880
881 end if;
882
883 if lv_found_flag = 'N' then
884
885 l_prectab_count := l_prectab_count + 1;
886
887 CurrPrecTab(l_prectab_count).curr_code := l_CurrCodeTab(i);
888 CurrPrecTab(l_prectab_count).mau := l_MauTab(i);
889 CurrPrecTab(l_prectab_count).sp := l_SpTab(i);
890 CurrPrecTab(l_prectab_count).ep := l_EpTab(i);
891
892 end if;
893
894 END LOOP;
895
896 l_CurrCodeTab.delete;
897 l_MauTab.delete;
898 l_SpTab.delete;
899 l_EpTab.delete;
900
901 END LOOP;
902
903 END init_cache;
904
905 /*----------------------------------------------------------------------------------------+
906 | Procedure : get_trans_currency_info |
907 | Purpose : This procedure gets the rounding precision attributes of a currency |
908 | Parameters : |
909 | ================================================================================== |
910 | Name Mode Description |
911 | ================================================================================== |
912 | p_curr_code IN Currency Code |
913 | ================================================================================== |
914 +----------------------------------------------------------------------------------------*/
915 PROCEDURE Get_Trans_Currency_Info (p_curr_code IN varchar2, x_mau out NOCOPY number,
916 x_sp out NOCOPY number, x_ep out NOCOPY number) IS
917 BEGIN
918
919 SELECT FC.Minimum_Accountable_Unit,
920 FC.Precision,
921 FC.Extended_Precision
922 INTO x_mau,
923 x_sp,
924 x_ep
925 FROM FND_CURRENCIES FC
926 WHERE FC.Currency_Code = p_curr_code;
927
928 END Get_Trans_Currency_Info;
929
930
931 /*----------------------------------------------------------------------------------------+
932 | Function : round_trans_currency_amt |
933 | Purpose : The round_trans_currency_amt returns the round off amount based on the |
934 | currency code |
935 | Parameters : |
936 | ================================================================================== |
937 | Name Mode Description |
938 | ================================================================================== |
939 | p_amount IN The amount to be rounded |
940 | p_curr_code IN Currency Code |
941 | ================================================================================== |
942 | Returns : Number
943 +----------------------------------------------------------------------------------------*/
944 FUNCTION round_trans_currency_amt ( p_amount IN NUMBER,
945 p_Curr_Code IN VARCHAR2 ) RETURN NUMBER
946 IS
947
948 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
949 l_sp fnd_currencies.precision%TYPE;
950 l_ep fnd_currencies.extended_precision%TYPE;
951
952 l_prectab_count NUMBER;
953 l_found_flag VARCHAR2(1):= 'F';
954
955 BEGIN
956
957 l_found_flag := 'F';
958
959 l_prectab_count := CurrPrecTab.count;
960
961 IF CurrPrecTab.Count <> 0 then
962
963 FOR i in CurrPrecTab.first..CurrPrecTab.last loop
964
965 if CurrPrecTab(i).curr_code = p_Curr_Code then
966
967 l_found_flag := 'T';
968 l_mau := CurrPrecTab(i).mau;
969 l_sp := CurrPrecTab(i).sp ;
970 l_ep := CurrPrecTab(i).ep ;
971
972 exit;
973
974 end if;
975
976 end loop;
977
978 end if;
979
980 if l_found_flag = 'F' THEN
981
982 l_prectab_count := l_prectab_count + 1;
983
984 Get_Trans_Currency_Info(
985 p_curr_code => p_curr_code,
986 x_mau => l_mau,
987 x_sp => l_sp,
988 x_ep => l_ep);
989
990 CurrPrecTab(l_prectab_count).curr_code := p_curr_code;
991 CurrPrecTab(l_prectab_count).mau := l_mau;
992 CurrPrecTab(l_prectab_count).sp := l_sp;
993 CurrPrecTab(l_prectab_count).ep := l_ep;
994
995 end if;
996
997 IF l_mau IS NOT NULL THEN
998
999 IF l_mau < 0.00001 THEN
1000 RETURN( round(p_Amount, 5));
1001 ELSE
1002 RETURN( round(p_Amount/l_mau) * l_mau );
1003 END IF;
1004
1005 ELSIF l_sp IS NOT NULL THEN
1006
1007 IF l_sp > 5 THEN
1008 RETURN( round(p_Amount, 5));
1009 ELSE
1010 RETURN( round(p_Amount, l_sp));
1011 END IF;
1012
1013 ELSE
1014
1015 RETURN( round(p_Amount, 5));
1016
1017 END IF;
1018
1019 END round_trans_currency_amt;
1020
1021
1022 Function check_mcb_trans_exist (p_project_id IN NUMBER) return varchar2 is
1023
1024 l_baseline_amount number;
1025
1026 lv_return_flag varchar2(1);
1027
1028
1029 BEGIN
1030
1031 lv_return_flag := 'N';
1032 -- check to see if any non zero baselined funding records exists
1033
1034 SELECT sum(nvl(allocated_amount,0))
1035 INTO l_baseline_amount
1036 FROM PA_PROJECT_FUNDINGS P
1037 WHERE P.PROJECT_ID = p_project_id
1038 AND budget_type_code = 'BASELINE';
1039
1040 if l_baseline_amount <> 0 then
1041
1042 lv_return_flag := 'Y';
1043 return (lv_return_flag);
1044
1045 end if;
1046
1047 -- check to see if any draft funding records exists
1048
1049 begin
1050
1051 /* Modified the code for bug 3686650
1052 SELECT 'Y' into lv_return_flag
1053 FROM PA_PROJECT_FUNDINGS P
1054 WHERE P.PROJECT_ID = p_project_id
1055 AND budget_type_code = 'DRAFT';
1056 */
1057 SELECT 'Y' into lv_return_flag FROM dual
1058 WHERE exists
1059 (SELECT project_id
1060 FROM pa_project_fundings
1061 WHERE project_id = p_project_id
1062 AND budget_type_code = 'DRAFT');
1063
1064 return (lv_return_flag);
1065
1066 exception
1067
1068 when others then
1069
1070 lv_return_flag := 'N';
1071
1072 end;
1073
1074 -- check to see if any Event records exists
1075
1076 begin
1077
1078 SELECT 'Y' into lv_return_flag from dual
1079 where exists
1080 (select project_id
1081 from pa_events
1082 where project_id = p_project_id);
1083
1084 return (lv_return_flag);
1085
1086 exception
1087
1088 when others then
1089
1090 lv_return_flag := 'N';
1091
1092 end;
1093
1094 -- check to see if any EI records exists
1095
1096 begin
1097
1098 /*Commented for bug 3088683
1099 SELECT 'Y' into lv_return_flag from dual
1100 where exists
1101 (select T.project_id
1102 from pa_expenditure_items_all E, pa_tasks T
1103 where T.project_id = p_project_id
1104 and E.task_id = T.task_id);
1105 End of comment for bug 3088683 */
1106
1107 /*Added for bug 3088683 */
1108
1109 SELECT 'Y' into lv_return_flag from dual
1110 where exists
1111 (select E.project_id
1112 from pa_expenditure_items_all E
1113 where E.project_id = p_project_id);
1114
1115 /*End of change for bug 3088683 */
1116
1117 return (lv_return_flag);
1118
1119 exception
1120
1121 when others then
1122
1123 lv_return_flag := 'N';
1124
1125 end;
1126
1127 return (lv_return_flag);
1128
1129 EXCEPTION
1130
1131 when others then
1132
1133 return 'Y';
1134
1135 END check_mcb_trans_exist;
1136
1137 PROCEDURE get_project_types_dflt(
1138 p_project_type IN VARCHAR2,
1139 x_baseline_flag OUT NOCOPY VARCHAR2,
1140 x_nl_rt_sch_id OUT NOCOPY NUMBER,
1141 x_nl_rt_sch_name OUT NOCOPY VARCHAR2,
1142 x_rate_sch_currency_code OUT NOCOPY VARCHAR2 ) IS
1143
1144
1145 cursor cur_proj_types IS
1146 SELECT ppt.baseline_funding_flag
1147 ,ppt.NON_LAB_STD_BILL_RT_SCH_ID
1148 ,brs.std_bill_rate_schedule
1149 ,brs.rate_sch_currency_code
1150 FROM pa_project_types ppt, pa_std_bill_rate_schedules brs
1151 WHERE ppt.project_type = p_project_type
1152 AND ppt.non_lab_std_bill_rt_sch_id = brs.bill_rate_sch_id(+);
1153
1154 BEGIN
1155
1156
1157
1158 OPEN cur_proj_types;
1159 FETCH cur_proj_types INTO x_baseline_flag, x_nl_rt_sch_id,
1160 x_nl_rt_sch_name, x_rate_sch_currency_code;
1161 CLOSE cur_proj_types;
1162
1163 /* Added the below for NOCOPY mandate */
1164 EXCEPTION WHEN OTHERS THEN
1165 x_baseline_flag := NULL;
1166 x_nl_rt_sch_id := NULL;
1167 x_nl_rt_sch_name := NULL;
1168 x_rate_sch_currency_code := NULL;
1169 raise;
1170 END get_project_types_dflt;
1171
1172
1173 FUNCTION check_cross_ou_fund_exist RETURN VARCHAR2 IS
1174
1175 lv_return_flag varchar2(1);
1176
1177 BEGIN
1178
1179 SELECT 'Y' INTO lv_return_flag FROM DUAL
1180 WHERE EXISTS ( SELECT spf.project_id
1181 FROM pa_summary_project_fundings spf, pa_agreements a,
1182 pa_projects_all P
1183 WHERE spf.agreement_id = a.agreement_id
1184 AND spf.project_id = p.project_id
1185 AND a.org_id <> p.org_id);
1186
1187
1188 RETURN (lv_return_flag);
1189
1190 EXCEPTION
1191
1192 WHEN OTHERS THEN
1193
1194 lv_return_flag := 'N';
1195 RETURN (lv_return_flag);
1196
1197 END check_cross_ou_fund_exist;
1198
1199
1200 FUNCTION check_cross_ou_billrate_exist RETURN VARCHAR2 IS
1201
1202 lv_return_flag varchar2(1);
1203
1204 BEGIN
1205
1206 SELECT 'Y' INTO lv_return_flag FROM DUAL
1207 WHERE EXISTS (SELECT p.project_type_id
1208 FROM pa_std_bill_rate_schedules_all br, pa_project_types p
1209 WHERE ( p.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1210 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1211 OR ( p.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1212 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1213 OR ( p.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1214 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99)));
1215
1216 RETURN (lv_return_flag);
1217
1218 EXCEPTION
1219
1220 WHEN OTHERS THEN
1221
1222 BEGIN
1223
1224 SELECT 'Y' INTO lv_return_flag FROM DUAL
1225 WHERE EXISTS (SELECT p.project_id
1226 FROM pa_std_bill_rate_schedules_all br, pa_projects p
1227 WHERE ( p.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1228 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1229 OR ( p.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1230 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1231 OR ( p.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1232 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99)));
1233
1234 RETURN (lv_return_flag);
1235
1236 EXCEPTION
1237
1238 WHEN OTHERS THEN
1239
1240 BEGIN
1241
1242 /* Fix for Performance bug 4939354
1243
1244 SELECT 'Y' INTO lv_return_flag FROM DUAL
1245 WHERE EXISTS (SELECT p.project_id
1246 FROM pa_std_bill_rate_schedules_all br, pa_projects p,
1247 pa_tasks t
1248 WHERE p.project_id = t.project_id
1249 AND ( ( t.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1250 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1251 OR ( t.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1252 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1253 OR ( t.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1254 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))));
1255 */
1256 SELECT 'Y' INTO lv_return_flag FROM DUAL
1257 WHERE EXISTS (SELECT p.project_id
1258 FROM pa_projects p, pa_tasks t
1259 WHERE p.project_id = t.project_id
1260 AND EXISTS
1261 (select null
1262 FROM pa_std_bill_rate_schedules_all br
1263 WHERE ( (t.job_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1264 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1265 OR ( t.emp_bill_rate_schedule_id = br.BILL_RATE_SCH_ID
1266 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1267 OR ( t.non_lab_std_bill_rt_sch_id = br.BILL_RATE_SCH_ID
1268 AND NVL(p.org_id,-99) <> NVL(br.org_id,-99))
1269 )));
1270
1271 RETURN (lv_return_flag);
1272
1273 EXCEPTION
1274
1275 WHEN OTHERS THEN
1276
1277 lv_return_flag := 'N';
1278 RETURN (lv_return_flag);
1279 END;
1280 END;
1281
1282 END check_cross_ou_billrate_exist;
1283
1284 Function is_baseline_funding_enabled (p_project_id IN NUMBER) return varchar2 is
1285
1286 lv_baseline_flag VARCHAR2(1);
1287
1288 begin
1289
1290 select baseline_funding_flag into lv_baseline_flag
1291 from pa_projects_all
1292 where project_id = p_project_id;
1293
1294 return lv_baseline_flag;
1295
1296 exception
1297
1298 when others then
1299
1300 return 'N';
1301
1302 end is_baseline_funding_enabled;
1303
1304 FUNCTION proj_cust_curr( p_project_id VARCHAR2,
1305 p_curr_code VARCHAR2 ) return VARCHAR2
1306 is
1307
1308 CURSOR cur_proj_cust IS
1309 SELECT 'x'
1310 FROM pa_project_customers
1311 WHERE INV_CURRENCY_CODE = p_curr_code
1312 AND project_id = p_project_id;
1313
1314 v_dummy_char VARCHAR2(1);
1315 begin
1316
1317 open cur_proj_cust;
1318 fetch cur_proj_cust into v_dummy_char;
1319
1320 IF cur_proj_cust%FOUND THEN
1321 CLOSE cur_proj_cust;
1322 RETURN 'N';
1323 ELSE
1324 CLOSE cur_proj_cust;
1325 RETURN 'Y';
1326 END IF;
1327 end proj_cust_curr;
1328
1329 ----------------------------------------------------------------------------------
1330 -- Purpose: This function will return value 'Y' if Project Functional Currency
1331 -- is not the same as that of invoice currency.
1332 --
1333 -- Inputs: Project_ID and Project_Functional_Currency_Code
1334 ----------------------------------------------------------------------------------
1335 FUNCTION MCB_Flag_Required(
1336 P_Project_ID IN PA_PROJECTS_ALL.Project_ID%TYPE,
1337 P_PFC_Currency_Code IN PA_PROJECTS_ALL.ProjFunc_Currency_Code%TYPE
1338 )
1339 RETURN VARCHAR2
1340 IS
1341
1342 l_Flag VARCHAR2(1);
1343 BEGIN
1344 -- Check whether the given Project has any Project Customers with
1345 -- different currency code, other than the Given Currency Code
1346 BEGIN
1347 SELECT 'Y'
1348 INTO l_Flag
1349 FROM Dual
1350 WHERE Exists ( SELECt 1 FROM PA_Project_Customers
1351 WHERE Project_ID = p_Project_ID
1352 AND Inv_Currency_Code <> p_pfc_Currency_Code);
1353 EXCEPTION WHEN NO_DATA_FOUND THEN
1354 l_Flag := 'N';
1355 END;
1356
1357 -- Check whether the given Project has any Invoice transactions having
1358 -- different currency code, other than the Given Currency Code
1359 IF l_Flag = 'N'
1360 THEN
1361 BEGIN
1362 SELECT 'Y'
1363 INTO l_Flag
1364 FROM Dual
1365 WHERE Exists ( SELECT 1 FROM PA_Draft_Invoices_All
1366 WHERE Project_ID = p_Project_ID
1367 AND Inv_Currency_Code <> p_pfc_Currency_Code);
1368 EXCEPTION WHEN NO_DATA_FOUND THEN
1369 l_Flag := 'N';
1370 END;
1371 END IF;
1372
1373 RETURN l_Flag ; -- Return 'Y' if yes else 'N' if No, after checking above conditions
1374
1375 END MCB_Flag_Required;
1376
1377
1378 FUNCTION get_currency( P_org_id IN pa_implementations_all.org_id%TYPE)
1379 RETURN VARCHAR2
1380 IS
1381
1382 BEGIN
1383
1384 return( G_Curr_Tab(P_org_id));
1385
1386 EXCEPTION WHEN others THEN
1387 raise;
1388 END get_currency;
1389
1390
1391 FUNCTION Check_update_ou_mcb_flag RETURN VARCHAR2 IS
1392
1393 lv_return_flag varchar2(1);
1394 /* Added local variable l_currency_code for bug 2872748 */
1395 l_currency_code fnd_currencies.currency_code%TYPE;
1396 BEGIN
1397
1398 /* Code addition for bug 2872748 starts */
1399 begin
1400 select pa_currency.get_currency_code
1401 into l_currency_code
1402 from dual ;
1403 end;
1404 /* Code addition for bug 2872748 ends */
1405
1406 SELECT 'N' INTO lv_return_flag FROM DUAL
1407 WHERE EXISTS ( SELECT a.agreement_id
1408 FROM pa_agreements a
1409 WHERE a.agreement_currency_code <> l_currency_code);
1410 /* for bug 2872748 replaced pa_currency.get_currency_code with l_currency_code in above sql */
1411
1412 RETURN (lv_return_flag);
1413
1414 EXCEPTION
1415
1416 WHEN OTHERS THEN
1417
1418 BEGIN
1419
1420 SELECT 'N' INTO lv_return_flag FROM DUAL
1421 WHERE EXISTS ( SELECT b.bill_rate_sch_id
1422 /* Commented for bug 2867740
1423 FROM pa_bill_rates b
1424 WHERE b.RATE_CURRENCY_CODE <> pa_currency.get_currency_code); */
1425 /* Bug fix for bug 2867740 Starts Here */
1426 FROM pa_std_bill_rate_schedules b
1427 WHERE b.RATE_SCH_CURRENCY_CODE <> pa_currency.get_currency_code);
1428 /* Bug fix for bug 2867740 Ends Here */
1429 RETURN (lv_return_flag);
1430 EXCEPTION
1431
1432 WHEN OTHERS THEN
1433
1434 lv_return_flag := 'Y';
1435 RETURN (lv_return_flag);
1436 END;
1437
1438
1439 END Check_update_ou_mcb_flag;
1440
1441 Function check_mcb_setup_exists (p_project_id IN NUMBER) return varchar2 is
1442
1443 lv_return_flag varchar2(1);
1444 lv_projfunc_currency varchar2(15);
1445
1446 BEGIN
1447 lv_return_flag := 'N';
1448
1449 SELECT projfunc_currency_code into lv_projfunc_currency
1450 FROM pa_projects
1451 WHERE project_id = p_project_id;
1452
1453 BEGIN
1454 SELECT 'Y' into lv_return_flag from dual
1455 where exists (select null
1456 from pa_billing_assignments asg
1457 /* where nvl(asg.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002*/
1458 where asg.project_id = p_project_id
1459 and asg.project_id IS NOT NULL
1460 and asg.rate_override_currency_code <> lv_projfunc_currency);
1461 RETURN (lv_return_flag);
1462
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 lv_return_flag := 'N';
1466 END;
1467
1468 BEGIN
1469 SELECT 'Y' into lv_return_flag from dual
1470 where exists (select null
1471 FROM pa_job_bill_rate_overrides jbr
1472 /* where nvl(jbr.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002 */
1473 where jbr.project_id = p_project_id
1474 and jbr.project_id IS NOT NULL
1475 and jbr.rate_currency_code <> lv_projfunc_currency);
1476
1477 RETURN (lv_return_flag);
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 lv_return_flag := 'N';
1482 END;
1483
1484 BEGIN
1485 SELECT 'Y' into lv_return_flag from dual
1486 where exists (select null
1487 FROM pa_nl_bill_rate_overrides nlr
1488 /* where nvl(nlr.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002 */
1489 where nlr.project_id = p_project_id
1490 and nlr.project_id IS NOT NULL
1491 and nlr.rate_currency_code <> lv_projfunc_currency);
1492
1493 RETURN (lv_return_flag);
1494
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 lv_return_flag := 'N';
1498 END;
1499
1500
1501 BEGIN
1502 SELECT 'Y' into lv_return_flag from dual
1503 where exists (select null
1504 FROM pa_emp_bill_rate_overrides emp
1505 /* where nvl(emp.project_id,-99) = p_project_id Bug 2702200 Modified on 20/12/2002 */
1506 where emp.project_id = p_project_id
1507 and emp.project_id IS NOT NULL
1508 and emp.rate_currency_code <> lv_projfunc_currency);
1509
1510 RETURN (lv_return_flag);
1511
1512 EXCEPTION
1513 WHEN OTHERS THEN
1514 lv_return_flag := 'N';
1515 END;
1516
1517
1518 BEGIN
1519 SELECT 'Y' into lv_return_flag from dual
1520 where exists (select null
1521 FROM PA_STD_BILL_RATE_SCHEDULES_all br, pa_projects p
1522 where p.project_id = p_project_id
1523 and br.BILL_RATE_SCH_ID in
1524 (nvl(p.job_bill_rate_schedule_id,-99),
1525 nvl(p.emp_bill_rate_schedule_id,-99),
1526 nvl(p.non_lab_std_bill_rt_sch_id,-99))
1527 and br.RATE_SCH_CURRENCY_CODE <> lv_projfunc_currency);
1528
1529 RETURN (lv_return_flag);
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 lv_return_flag := 'N';
1534 END;
1535 --- task level overrides
1536
1537
1538 BEGIN
1539 SELECT 'Y' into lv_return_flag from dual
1540 where exists (select null
1541 from pa_billing_assignments asg, pa_tasks t
1542 where t.project_id = p_project_id
1543 /* and nvl(asg.top_task_id,-99) = t.task_id Bug 2702200 Modified on 20/12/2002 */
1544 and asg.top_task_id = t.task_id
1545 and asg.project_id = t.project_id -- added for bug 3517177
1546 and asg.top_task_id IS NOT NULL
1547 and asg.rate_override_currency_code <>lv_projfunc_currency);
1548
1549 RETURN (lv_return_flag);
1550
1551 EXCEPTION
1552 WHEN OTHERS THEN
1553 lv_return_flag := 'N';
1554 END;
1555
1556 BEGIN
1557 SELECT 'Y' into lv_return_flag from dual
1558 where exists (select null
1559 FROM pa_job_bill_rate_overrides jbr, pa_tasks t
1560 where t.project_id = p_project_id
1561 /* and nvl(jbr.task_id,-99) = t.task_id Bug 2702200 Modified on 20/12/2002 */
1562 and jbr.task_id = t.task_id
1563 and jbr.task_id IS NOT NULL
1564 and jbr.rate_currency_code <> lv_projfunc_currency);
1565
1566 RETURN (lv_return_flag);
1567
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 lv_return_flag := 'N';
1571 END;
1572
1573 BEGIN
1574 SELECT 'Y' into lv_return_flag from dual
1575 where exists (select null
1576 FROM pa_nl_bill_rate_overrides nlr, pa_tasks t
1577 where t.project_id = p_project_id
1578 /* and nvl(nlr.task_id,-99) = t.task_id Modifed on 20/12/2002 */
1579 and nlr.task_id = t.task_id
1580 and nlr.task_id IS NOT NULL
1581 and nlr.rate_currency_code <> lv_projfunc_currency);
1582
1583 RETURN (lv_return_flag);
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 lv_return_flag := 'N';
1588 END;
1589
1590
1591 BEGIN
1592 SELECT 'Y' into lv_return_flag from dual
1593 where exists (select null
1594 FROM pa_emp_bill_rate_overrides emp, pa_tasks t
1595 where t.project_id = p_project_id
1596 /* and nvl(emp.task_id,-99) = t.task_id Modifed on 20/12/2002*/
1597 and emp.task_id = t.task_id
1598 and emp.task_id IS NOT NULL
1599 and emp.rate_currency_code <> lv_projfunc_currency);
1600
1601 RETURN (lv_return_flag);
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 lv_return_flag := 'N';
1606 END;
1607
1608
1609 BEGIN
1610 SELECT 'Y' into lv_return_flag from dual
1611 where exists (select null
1612 FROM PA_STD_BILL_RATE_SCHEDULES_all br, pa_tasks t
1613 where t.project_id = p_project_id
1614 and br.BILL_RATE_SCH_ID in
1615 (nvl(t.job_bill_rate_schedule_id,-99),
1616 nvl(t.emp_bill_rate_schedule_id,-99),
1617 nvl(t.non_lab_std_bill_rt_sch_id, -99))
1618 and br.RATE_SCH_CURRENCY_CODE <> lv_projfunc_currency);
1619
1620 RETURN (lv_return_flag);
1621
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 lv_return_flag := 'N';
1625 END;
1626
1627 return (lv_return_flag);
1628
1629 EXCEPTION
1630
1631 WHEN OTHERS THEN
1632 lv_return_flag := 'N';
1633 return (lv_return_flag);
1634
1635 END check_mcb_setup_exists;
1636
1637 /* Added the given below procedure for Enhancement bug 2520222
1638 It is being called from customer window of project form.
1639 This procedure will check if the assigned customer is having valid funding lines and
1640 user is trying to change existing contribution from non zero to zero then it will give error.*/
1641
1642 Procedure Check_Cust_Funding_Exists(
1643 p_proj_customer_id IN NUMBER,
1644 p_project_id IN NUMBER,
1645 p_cust_contribution IN NUMBER,
1646 x_return_status OUT NOCOPY VARCHAR2,
1647 x_msg_data OUT NOCOPY VARCHAR2,
1648 x_msg_count OUT NOCOPY NUMBER
1649 )
1650 IS
1651 CURSOR C_fund IS
1652 SELECT 'x'
1653 FROM pa_agreements a,
1654 pa_summary_project_fundings f
1655 WHERE a.customer_id = p_proj_customer_id
1656 AND a.agreement_id = f.agreement_id
1657 AND f.project_id = p_project_id
1658 AND ( f.total_unbaselined_amount <>0
1659 OR f.total_baselined_amount <> 0);
1660
1661 x_funding_exists VARCHAR2(1):= NULL;
1662 BEGIN
1663
1664 x_return_status := 'S'; -- FND_API.G_RET_STS_SUCCESS;
1665
1666 IF ( p_cust_contribution = 0 ) THEN
1667
1668 OPEN C_fund;
1669 FETCH C_fund
1670 INTO x_funding_exists;
1671 CLOSE C_fund;
1672
1673 IF (x_funding_exists IS NOT NULL) THEN
1674 x_return_status := 'E'; -- FND_API.G_RET_STS_ERROR;
1675 x_msg_data := 'PA_BILL_CUST_CONTR_ZERO';
1676 x_msg_count := 1;
1677 ELSE
1678 x_return_status := 'S'; -- FND_API.G_RET_STS_SUCCESS;
1679 END IF;
1680
1681 END IF;
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 x_return_status := 'U'; -- FND_API.G_RET_STS_UNEXP_ERROR;
1685 x_msg_data := SUBSTR(SQLERRM,1,50);
1686 x_msg_count := 1;
1687
1688 END Check_Cust_Funding_Exists;
1689
1690 /* till here */
1691
1692 END pa_multi_currency_billing;