1 PACKAGE BODY ap_web_amount_util AS
2 /* $Header: apwamtub.pls 120.13 2011/08/22 11:23:54 rveliche ship $ */
3
4 /* -------------------------------------------------------------------
5 -- Function to get user_id for an employee.
6 -- Parameters:
7 -- IN p_employee_id
8 -- Return Value:
9 -- user_id (from fnd_user) or NULL if no user exists for
10 -- that employee_id
11 -- Note: this will return the first user_id that has an AP SSWA
12 -- responsibility, since that is the most likely useful user_id.
13 -- In other words, this function is NOT a generic employee_id->user_id
14 -- converter!
15 -- ---------------------------------------------------------------- */
16
17 function get_user_id(p_employee_id IN NUMBER) RETURN NUMBER IS
18 l_user_id NUMBER;
19 begin
20 begin
21 select fu.user_id
22 into l_user_id
23 from fnd_user fu
24 where fu.employee_id = p_employee_id
25 and sysdate >= fu.start_date
26 and sysdate <= nvl(fu.end_date, sysdate)
27 and rownum = 1;
28
29 exception
30 when others then
31 l_user_id := NULL;
32 end;
33
34 return l_user_id;
35
36 end get_user_id;
37
38
39 /* -------------------------------------------------------------------
40 -- Function to round an amount based on a currency code
41 -- Parameters:
42 -- IN p_amount amount to be rounded
43 -- IN p_curr_code currency code of amount
44 -- Return Value:
45 -- rounded amount
46 -- ---------------------------------------------------------------- */
47
48 function round_amount(
49 p_amount IN NUMBER,
50 p_curr_code IN VARCHAR2) RETURN NUMBER IS
51 l_value number;
52 begin
53
54 -- arp_standard.debug('rounding amount ' || p_amount || ' for curr ' || p_curr_code);
55
56 select round(p_amount, fc.precision)
57 into l_value
58 from fnd_currencies_vl fc
59 where currency_code = p_curr_code;
60
61 return l_value;
62 end;
63
64
65 /* -------------------------------------------------------------------
66 -- Function to derive the OIE responsibility for a given user.
67 -- Note: We have to make certain assumptions:
68 -- 1. a self-service responsibility for product AP is an OIE
69 -- responsibility. This isn't necessarily true, but it's
70 -- quite unlikely that an internal approver has iInvoicing
71 -- access (another type of AP Web Responsibility). In the
72 -- future this would have to be revisited as more and more
73 -- products migrate to web UI.
74 -- 2. a user could have multiple OIE responsibilities (e.g.,
75 -- "OIE with Projects" and "OIE without Projects". We take
76 -- the first one. Since we're using the responsibility to
77 -- derive the ORG and thus the functional currency code, it
78 -- should in most cases make no difference.
79 -- ---------------------------------------------------------------- */
80
81 function get_oie_responsibility(p_user_id IN NUMBER,
82 p_responsibility_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
83
84 l_menu_id NUMBER;
85 l_responsibility_id NUMBER;
86 begin
87
88 begin
89 select menu_id, responsibility_id
90 into l_menu_id, l_responsibility_id
91 from (
92 select fr.menu_id, fr.responsibility_id
93 from fnd_responsibility fr,
94 fnd_user_resp_groups furg
95 where fr.application_id = 200
96 and fr.version = 'W'
97 and furg.user_id = p_user_id
98 and furg.responsibility_id = fr.responsibility_id
99 order by furg.start_date desc
100 )
101 where rownum=1;
102
103 p_responsibility_id := l_responsibility_id;
104 return 'Y';
105
106 exception
107 when others then
108 p_responsibility_id := NULL;
109 return 'N';
110 end;
111
112 end;
113
114
115 /* -------------------------------------------------------------------
116 -- Function to get the ORG ID for a given user and responsibility.
117 -- This is based on a profile
118 -- ---------------------------------------------------------------- */
119
120
121 function get_oie_org_id(p_userid IN NUMBER,
122 p_oie_resp_id IN NUMBER,
123 p_org_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
124
125 l_defined BOOLEAN;
126 l_value VARCHAR2(240);
127 begin
128
129 begin
130
131 fnd_profile.get_specific(NAME_Z => 'ORG_ID',
132 USER_ID_Z => p_userid,
133 RESPONSIBILITY_ID_Z => p_oie_resp_id,
134 APPLICATION_ID_Z => 200, -- SQL*AP product id
135 VAL_Z => l_value,
136 DEFINED_Z => l_defined);
137
138 exception
139 when others then
140 l_defined := FALSE;
141 end;
142
143 if (l_defined) then
144 p_org_id := to_number(l_value);
145 return 'Y';
146 else
147 p_org_id := NULL;
148 return 'N';
149 end if;
150
151 end;
152
153
154 /* -------------------------------------------------------------------
155 -- Function to get the func currency, the set of books id, and the
156 -- default exchange rate type for a given org from ap_system_parameters
157 -- ---------------------------------------------------------------- */
158
159 function get_ap_setup_data(p_org_id IN NUMBER,
160 p_func_curr_code OUT NOCOPY VARCHAR2,
161 p_sob_id OUT NOCOPY NUMBER,
162 p_default_exchange_rate_type OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
163 l_curr_code VARCHAR2(30);
164 l_sob_id NUMBER;
165 l_default_exchange_rate_type VARCHAR2(30);
166 begin
167
168 begin
169 select base_currency_code, asp.set_of_books_id, asp.default_exchange_rate_type
170 into l_curr_code, l_sob_id, l_default_exchange_rate_type
171 from ap_system_parameters_all asp
172 where asp.org_id = p_org_id;
173
174 p_func_curr_code := l_curr_code;
175 p_sob_id := l_sob_id;
176 p_default_exchange_rate_type := l_default_exchange_rate_type;
177 return 'Y';
178
179 exception
180 when others then
181 p_func_curr_code := NULL;
182 p_sob_id := NULL;
183 p_default_exchange_rate_type := NULL;
184 return 'N';
185 end;
186
187 end;
188
189
190 /* -------------------------------------------------------------------
191 -- Bug 4020295
192 -- Function to get the exchange rate for a given from Currency code,
193 -- exchange rate type, pair of currencies and date.
194 -- This is essentially just a cover on top of a GL API. It looks
195 -- back into the past for up to 60 days if the exchange rate isn't
196 -- defined for the requested date.
197 -- ---------------------------------------------------------------- */
198
199 function get_exchange_rate(p_from_curr_code IN VARCHAR2,
200 p_to_curr_code IN VARCHAR2,
201 p_date IN DATE,
202 p_exchange_rate_type IN VARCHAR2,
203 p_exchange_rate OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
204 l_rate NUMBER;
205 begin
206
207 l_rate := gl_currency_api.get_closest_rate_sql(
208 x_from_currency => p_from_curr_code,
209 x_to_currency => p_to_curr_code,
210 x_conversion_date => p_date,
211 x_conversion_type => p_exchange_rate_type,
212 x_max_roll_days => 60);
213
214 if (l_rate < 0) then
215 p_exchange_rate := NULL;
216 return 'N';
217 else
218 p_exchange_rate := l_rate;
219 return 'Y';
220 end if;
221
222 end;
223
224
225 /* -------------------------------------------------------------------
226 -- Function to get the exchange rate for a given set of books,
227 -- exchange rate type, pair of currencies and date.
228 -- This is essentially just a cover on top of a GL API. It looks
229 -- back into the past for up to 60 days if the exchange rate isn't
230 -- defined for the requested date.
231 -- ---------------------------------------------------------------- */
232
233 function get_exchange_rate(p_sob_id IN NUMBER,
234 p_func_curr_code IN VARCHAR2,
235 p_currency_code IN VARCHAR2,
236 p_date IN DATE,
237 p_exchange_rate_type IN VARCHAR2,
238 p_exchange_rate OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
239 l_rate NUMBER;
240 begin
241
242 l_rate := gl_currency_api.get_closest_rate_sql(
243 x_set_of_books_id => p_sob_id,
244 x_from_currency => p_currency_code,
245 x_conversion_date => p_date,
246 x_conversion_type => p_exchange_rate_type,
247 x_max_roll_days => 60);
248
249 if (l_rate < 0) then
250 p_exchange_rate := NULL;
251 return 'N';
252 else
253 p_exchange_rate := l_rate;
254 return 'Y';
255 end if;
256
257 end;
258
259 /* -------------------------------------------------------------------
260 -- Public procedure to get a meaningful exchange rate for a given
261 -- user, date, and currency code.
262 -- ---------------------------------------------------------------- */
263
264 procedure get_meaningful_rate(
265 p_userid IN NUMBER,
266 p_date IN DATE,
267 p_currency_code IN VARCHAR2,
268 p_success_flag OUT NOCOPY VARCHAR2,
269 p_conv_date OUT NOCOPY DATE,
270 p_conv_rate OUT NOCOPY NUMBER,
271 p_conv_currency_code IN OUT NOCOPY VARCHAR2) IS
272
273 l_success_flag VARCHAR2(1);
274
275
276 l_oie_resp_id NUMBER;
277 l_org_id NUMBER;
278 l_func_curr_code VARCHAR2(30);
279 l_sob_id NUMBER;
280 l_exchange_rate_type VARCHAR2(30);
281 l_exchange_rate NUMBER;
282 l_pref_curr_code VARCHAR2(30);
283 l_employee_id NUMBER;
284
285 begin
286
287 -- Bug 4020295
288 -- Get the preferred currency code from profile for this user if set
289 --
290
291 -- l_pref_curr_code := get_preferred_currency_code( p_user_id => p_userid );
292 l_pref_curr_code := p_conv_currency_code;
293
294 if ( l_pref_curr_code is not null ) then
295
296 --
297 -- Get the other information from the employee's HR information
298 --
299
300 begin
301
302 select asp.default_exchange_rate_type
303 into l_exchange_rate_type
304 from per_employees_x pex,
305 ap_system_parameters_all asp,
306 fnd_user fu
307 where
308 pex.set_of_books_id = asp.set_of_books_id and
309 fu.employee_id = pex.employee_id and
310 fu.user_id = p_userid and
311 rownum = 1;
312 exception
313 when others then
314 p_success_flag := 'N';
315 return;
316 end;
317
318 --
319 -- Get the conversion rate for this preferred currency code
320 --
321
322 l_success_flag := get_exchange_rate(
323 p_from_curr_code => p_currency_code,
324 p_to_curr_code => l_pref_curr_code,
325 p_date => p_date,
326 p_exchange_rate_type => l_exchange_rate_type,
327 p_exchange_rate => l_exchange_rate);
328
329 if (l_success_flag = 'N') then
330
331 p_success_flag := 'N';
332 return;
333 else
334 p_conv_rate := l_exchange_rate;
335 p_conv_currency_code := l_pref_curr_code;
336 p_success_flag := 'Y';
337 end if;
338
339 else
340
341 /*
342 -- arp_standard.enable_file_debug('/sqlcom/out/aroa55n','osteinme_oie');
343
344 -- arp_standard.debug('get_meaningful_value()+');
345
346 l_success_flag := get_oie_responsibility(p_userid, l_oie_resp_id);
347
348 -- arp_standard.debug('resp_id = ' || to_char(l_oie_resp_id));
349 -- arp_standard.debug('success = ' || l_success_flag);
350
351 if (l_success_flag = 'N') then
352 p_success_flag := 'N';
353 return;
354 end if;
355
356 l_success_flag := get_oie_org_id(p_userid, l_oie_resp_id, l_org_id);
357
358 -- arp_standard.debug('org_id = ' || to_char(l_org_id));
359 -- arp_standard.debug('success = ' || l_success_flag);
360 */
361 BEGIN
362 IF NOT (AP_WEB_DB_HR_INT_PKG.GetEmpIdForUser(p_userid, l_employee_id)) THEN
363 p_success_flag := 'N';
364 return;
365 END IF;
366 SELECT set_of_books_id
367 INTO l_sob_id
368 FROM
369 (SELECT set_of_books_id
370 FROM per_employees_x emp
371 WHERE employee_id = l_employee_id
372 AND NOT AP_WEB_DB_HR_INT_PKG.isPersonCwk(emp.employee_id)='Y'
373
374 UNION ALL
375
376 SELECT set_of_books_id
377 FROM per_cont_workers_current_x emp
378 WHERE person_id = l_employee_id
379 );
380 IF (l_sob_id IS NOT NULL) THEN
381 SELECT currency_code INTO l_func_curr_code FROM gl_sets_of_books WHERE set_of_books_id=l_sob_id;
382 END IF;
383 IF (l_sob_id IS NOT NULL AND l_func_curr_code IS NOT NULL) THEN
384 SELECT default_exchange_rate_type
385 INTO l_exchange_rate_type
386 FROM ap_system_parameters_all
387 WHERE set_of_books_id = l_sob_id
388 AND rownum= 1;
389 END IF;
390 EXCEPTION
391 WHEN OTHERS THEN
392 l_success_flag := 'N';
393 END;
394
395 /* if (l_success_flag = 'N') then
396 p_success_flag := 'N';
397 return;
398 end if;*/
399
400 IF (l_sob_id IS NULL OR l_func_curr_code IS NULL OR l_exchange_rate_type IS NULL) THEN
401 IF NOT (AP_WEB_DB_HR_INT_PKG.GetEmpOrgId(l_employee_id, l_org_id)) THEN
402 p_success_flag := 'N';
403 return;
404 END IF;
405
406 l_success_flag := get_ap_setup_data(l_org_id, l_func_curr_code, l_sob_id, l_exchange_rate_type);
407 END IF;
408
409 -- arp_standard.debug('sob_id = ' || to_char(l_sob_id));
410 -- arp_standard.debug('func curr = ' || l_func_curr_code);
411 -- arp_standard.debug('def exch type = ' || l_exchange_rate_type);
412
413 -- arp_standard.debug('success = ' || l_success_flag);
414
415
416 if (l_success_flag = 'N') then
417 p_success_flag := 'N';
418 return;
419 end if;
420
421
422 -- no point in doing conversion if reimbursement amount is already
423 -- in desired currency.
424
425 if (l_func_curr_code = p_currency_code) then
426 p_success_flag := 'N';
427 return;
428 end if;
429
430
431 l_success_flag := get_exchange_rate(l_sob_id,
432 l_func_curr_code,
433 p_currency_code,
434 p_date,
435 l_exchange_rate_type,
436 l_exchange_rate);
437
438
439 -- arp_standard.debug('rate = ' || to_char(l_exchange_rate));
440 -- arp_standard.debug('success = ' || l_success_flag);
441
442 if (l_success_flag = 'N') then
443 p_success_flag := 'N';
444 return;
445 else
446 p_conv_rate := l_exchange_rate;
447 p_conv_currency_code := l_func_curr_code;
448 p_success_flag := 'Y';
449 end if;
450
451 end if; -- end of if ( l_pref_curr_code is not null )
452
453 end get_meaningful_rate;
454
455
456 /* -------------------------------------------------------------------
457 -- Public procedure to get a meaningful converted amount for a given
458 -- user, date, and currency code.
459 -- ---------------------------------------------------------------- */
460
461 procedure get_meaningful_amount(
462 p_userid IN NUMBER,
463 p_amount IN NUMBER,
464 p_date IN DATE,
465 p_currency_code IN VARCHAR2,
466 p_success_flag OUT NOCOPY VARCHAR2, -- Y/N
467 p_conv_amount OUT NOCOPY NUMBER,
468 p_conv_currency_code IN OUT NOCOPY VARCHAR2) IS
469
470 l_conv_rate NUMBER;
471 l_conv_date DATE;
472 l_success_flag VARCHAR2(1);
473
474 begin
475
476 get_meaningful_rate(
477 p_userid,
478 p_date,
479 p_currency_code,
480 l_success_flag,
481 l_conv_date,
482 l_conv_rate,
483 p_conv_currency_code);
484
485 if (l_success_flag = 'Y') then
486 p_conv_amount := round_amount(p_amount * l_conv_rate, p_conv_currency_code);
487 p_success_flag := 'Y';
488 else
489 p_success_flag := 'N';
490 p_conv_amount := NULL;
491 end if;
492
493 end;
494
495
496 /* -------------------------------------------------------------------
497 -- Public function to get a string returning a translated string of
498 -- format "Estimated Reimbursement Amount in <curr>: <amount>
499 -- where <amount> is a converted amount in currency <curr>.
500 -- ---------------------------------------------------------------- */
501
502 function get_meaningful_amount_msg(
503 p_userid IN NUMBER,
504 p_amount IN NUMBER,
505 p_date IN DATE,
506 p_currency_code IN VARCHAR2,
507 p_out_currency_code IN VARCHAR2) RETURN VARCHAR2 IS
508
509 l_msg VARCHAR2(240);
510 l_success_flag VARCHAR2(1);
511 l_conv_amount NUMBER;
512 l_conv_amount_formatted VARCHAR2(30);
513 l_conv_curr_code VARCHAR2(30);
514
515 begin
516 l_conv_curr_code := p_out_currency_code;
517
518 begin
519 get_meaningful_amount(
520 p_userid,
521 p_amount,
522 p_date,
523 p_currency_code,
524 l_success_flag,
525 l_conv_amount,
526 l_conv_curr_code);
527
528 if l_success_flag = 'N' then
529 return null;
530 else
531 l_conv_amount_formatted := to_char(l_conv_amount,
532 fnd_currency.get_format_mask(l_conv_curr_code, 20));
533
534 fnd_message.set_name('SQLAP','OIE_MEANINGFUL_AMOUNT_PROMPT');
535 fnd_message.set_token('CURRENCY', l_conv_curr_code);
536 fnd_message.set_token('AMOUNT', l_conv_amount_formatted);
537 l_msg := fnd_message.get;
538 return l_msg;
539 end if;
540
541 exception
542 when others then
543 return NULL;
544 end;
545
546 end get_meaningful_amount_msg;
547
548 /* -------------------------------------------------------------------
549 -- Public function to get a string returning a translated string of
550 -- format "Estimated Reimbursement Amount in <curr>: <amount>
551 -- where <amount> is a converted amount in currency <curr>.
552 -- Function altered to fetch the functional currency
553 -- ---------------------------------------------------------------- */
554
555 function get_meaningful_amount_msg_emp(
556 p_employee_id IN NUMBER,
557 p_amount IN NUMBER,
558 p_date IN DATE,
559 p_currency_code IN VARCHAR2,
560 p_out_currency_code IN VARCHAR2) RETURN VARCHAR2 IS
561 l_user_id NUMBER;
562 l_org_id VARCHAR2(200);
563 l_defined BOOLEAN;
564 l_func_curr_code VARCHAR2(30);
565 l_sob_id NUMBER;
566 l_exchange_rate_type VARCHAR2(30);
567 l_success_flag varchar2(1);
568
569 begin
570
571 -- Bug 5436992 - Do not show any message if the currencies are the same
572 if ( (p_out_currency_code IS NOT NULL) AND ( p_out_currency_code = p_currency_code) )
573 THEN
574 return null;
575 END IF;
576
577 l_user_id := get_user_id(p_employee_id);
578 l_func_curr_code := p_out_currency_code;
579 /* The following code is for future use where current resp is also checked
580 if(l_user_id is not null and p_out_currency_code is null) then
581 fnd_profile.get_specific(NAME_Z => 'ORG_ID',
582 USER_ID_Z => null,
583 RESPONSIBILITY_ID_Z => fnd_global.resp_id(),
584 APPLICATION_ID_Z => null, -- SQL*AP product id
585 VAL_Z => l_org_id,
586 DEFINED_Z => l_defined);
587 if (l_org_id is not null) then
588 l_success_flag := get_ap_setup_data(to_number(l_org_id), l_func_curr_code, l_sob_id, l_exchange_rate_type);
589 end if;
590 end if;*/
591
592 if l_user_id is not null then
593 return get_meaningful_amount_msg(
594 l_user_id,
595 p_amount,
596 p_date,
597 p_currency_code,
598 p_out_currency_code);
599 else
600 return null;
601 end if;
602
603 end get_meaningful_amount_msg_emp;
604
605
606 procedure get_meaningful_amount_emp(
607 p_employee_id IN NUMBER,
608 p_amount IN NUMBER,
609 p_date IN DATE,
610 p_currency_code IN VARCHAR2,
611 p_success_flag OUT NOCOPY VARCHAR2,
612 p_conv_amount OUT NOCOPY NUMBER,
613 p_conv_currency_code OUT NOCOPY VARCHAR2) IS
614 l_user_id NUMBER;
615 begin
616
617 l_user_id := get_user_id(p_employee_id);
618
619 if l_user_id is not null then
620 get_meaningful_amount(
621 l_user_id,
622 p_amount,
623 p_date,
624 p_currency_code,
625 p_success_flag,
626 p_conv_amount,
627 p_conv_currency_code);
628 else
629 p_success_flag := 'N';
630 end if;
631
632 end get_meaningful_amount_emp;
633
634 end ap_web_amount_util;
[Home] [Help]