1 PACKAGE BODY ap_web_amount_util AS
2 /* $Header: apwamtub.pls 120.11 2006/08/22 06:18:27 sbalaji noship $ */
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
284 begin
285
286 -- Bug 4020295
287 -- Get the preferred currency code from profile for this user if set
288 --
289
290 -- l_pref_curr_code := get_preferred_currency_code( p_user_id => p_userid );
291 l_pref_curr_code := p_conv_currency_code;
292
293 if ( l_pref_curr_code is not null ) then
294
295 --
296 -- Get the other information from the employee's HR information
297 --
298
299 begin
300
301 select asp.default_exchange_rate_type
302 into l_exchange_rate_type
303 from per_employees_x pex,
304 ap_system_parameters_all asp,
305 fnd_user fu
306 where
307 pex.set_of_books_id = asp.set_of_books_id and
308 fu.employee_id = pex.employee_id and
309 fu.user_id = p_userid and
310 rownum = 1;
311 exception
312 when others then
313 p_success_flag := 'N';
314 return;
315 end;
316
317 --
318 -- Get the conversion rate for this preferred currency code
319 --
320
321 l_success_flag := get_exchange_rate(
322 p_from_curr_code => p_currency_code,
323 p_to_curr_code => l_pref_curr_code,
324 p_date => p_date,
325 p_exchange_rate_type => l_exchange_rate_type,
326 p_exchange_rate => l_exchange_rate);
327
328 if (l_success_flag = 'N') then
329
330 p_success_flag := 'N';
331 return;
332 else
333 p_conv_rate := l_exchange_rate;
334 p_conv_currency_code := l_pref_curr_code;
335 p_success_flag := 'Y';
336 end if;
337
338 else
339
340
341 -- arp_standard.enable_file_debug('/sqlcom/out/aroa55n','osteinme_oie');
342
343 -- arp_standard.debug('get_meaningful_value()+');
344
345 l_success_flag := get_oie_responsibility(p_userid, l_oie_resp_id);
346
347 -- arp_standard.debug('resp_id = ' || to_char(l_oie_resp_id));
348 -- arp_standard.debug('success = ' || l_success_flag);
349
350 if (l_success_flag = 'N') then
351 p_success_flag := 'N';
352 return;
353 end if;
354
355 l_success_flag := get_oie_org_id(p_userid, l_oie_resp_id, l_org_id);
356
357 -- arp_standard.debug('org_id = ' || to_char(l_org_id));
358 -- arp_standard.debug('success = ' || l_success_flag);
359
360
361 if (l_success_flag = 'N') then
362 p_success_flag := 'N';
363 return;
364 end if;
365
366 l_success_flag := get_ap_setup_data(l_org_id, l_func_curr_code, l_sob_id, l_exchange_rate_type);
367
368 -- arp_standard.debug('sob_id = ' || to_char(l_sob_id));
369 -- arp_standard.debug('func curr = ' || l_func_curr_code);
370 -- arp_standard.debug('def exch type = ' || l_exchange_rate_type);
371
372 -- arp_standard.debug('success = ' || l_success_flag);
373
374
375 if (l_success_flag = 'N') then
376 p_success_flag := 'N';
377 return;
378 end if;
379
380
381 -- no point in doing conversion if reimbursement amount is already
382 -- in desired currency.
383
384 if (l_func_curr_code = p_currency_code) then
385 p_success_flag := 'N';
386 return;
387 end if;
388
389
390 l_success_flag := get_exchange_rate(l_sob_id,
391 l_func_curr_code,
392 p_currency_code,
393 p_date,
394 l_exchange_rate_type,
395 l_exchange_rate);
396
397
398 -- arp_standard.debug('rate = ' || to_char(l_exchange_rate));
399 -- arp_standard.debug('success = ' || l_success_flag);
400
401 if (l_success_flag = 'N') then
402 p_success_flag := 'N';
403 return;
404 else
405 p_conv_rate := l_exchange_rate;
406 p_conv_currency_code := l_func_curr_code;
407 p_success_flag := 'Y';
408 end if;
409
410 end if; -- end of if ( l_pref_curr_code is not null )
411
412 end get_meaningful_rate;
413
414
415 /* -------------------------------------------------------------------
416 -- Public procedure to get a meaningful converted amount for a given
417 -- user, date, and currency code.
418 -- ---------------------------------------------------------------- */
419
420 procedure get_meaningful_amount(
421 p_userid IN NUMBER,
422 p_amount IN NUMBER,
423 p_date IN DATE,
424 p_currency_code IN VARCHAR2,
425 p_success_flag OUT NOCOPY VARCHAR2, -- Y/N
426 p_conv_amount OUT NOCOPY NUMBER,
427 p_conv_currency_code IN OUT NOCOPY VARCHAR2) IS
428
429 l_conv_rate NUMBER;
430 l_conv_date DATE;
431 l_success_flag VARCHAR2(1);
432
433 begin
434
435 get_meaningful_rate(
436 p_userid,
437 p_date,
438 p_currency_code,
439 l_success_flag,
440 l_conv_date,
441 l_conv_rate,
442 p_conv_currency_code);
443
444 if (l_success_flag = 'Y') then
445 p_conv_amount := round_amount(p_amount * l_conv_rate, p_conv_currency_code);
446 p_success_flag := 'Y';
447 else
448 p_success_flag := 'N';
449 p_conv_amount := NULL;
450 end if;
451
452 end;
453
454
455 /* -------------------------------------------------------------------
456 -- Public function to get a string returning a translated string of
457 -- format "Estimated Reimbursement Amount in <curr>: <amount>
458 -- where <amount> is a converted amount in currency <curr>.
459 -- ---------------------------------------------------------------- */
460
461 function get_meaningful_amount_msg(
462 p_userid IN NUMBER,
463 p_amount IN NUMBER,
464 p_date IN DATE,
465 p_currency_code IN VARCHAR2,
466 p_out_currency_code IN VARCHAR2) RETURN VARCHAR2 IS
467
468 l_msg VARCHAR2(240);
469 l_success_flag VARCHAR2(1);
470 l_conv_amount NUMBER;
471 l_conv_amount_formatted VARCHAR2(30);
472 l_conv_curr_code VARCHAR2(30);
473
474 begin
475 l_conv_curr_code := p_out_currency_code;
476
477 begin
478 get_meaningful_amount(
479 p_userid,
480 p_amount,
481 p_date,
482 p_currency_code,
483 l_success_flag,
484 l_conv_amount,
485 l_conv_curr_code);
486
487 if l_success_flag = 'N' then
488 return null;
489 else
490 l_conv_amount_formatted := to_char(l_conv_amount,
491 fnd_currency.get_format_mask(l_conv_curr_code, 20));
492
493 fnd_message.set_name('SQLAP','OIE_MEANINGFUL_AMOUNT_PROMPT');
494 fnd_message.set_token('CURRENCY', l_conv_curr_code);
495 fnd_message.set_token('AMOUNT', l_conv_amount_formatted);
496 l_msg := fnd_message.get;
497 return l_msg;
498 end if;
499
500 exception
501 when others then
502 return NULL;
503 end;
504
505 end get_meaningful_amount_msg;
506
507 /* -------------------------------------------------------------------
508 -- Public function to get a string returning a translated string of
509 -- format "Estimated Reimbursement Amount in <curr>: <amount>
510 -- where <amount> is a converted amount in currency <curr>.
511 -- ---------------------------------------------------------------- */
512
513 function get_meaningful_amount_msg_emp(
514 p_employee_id IN NUMBER,
515 p_amount IN NUMBER,
516 p_date IN DATE,
517 p_currency_code IN VARCHAR2,
518 p_out_currency_code IN VARCHAR2) RETURN VARCHAR2 IS
519 l_user_id NUMBER;
520 begin
521
522 -- Bug 5436992 - Do not show any message if the currencies are the same
523 if ( (p_out_currency_code IS NOT NULL) AND ( p_out_currency_code = p_currency_code) )
524 THEN
525 return null;
526 END IF;
527
528 l_user_id := get_user_id(p_employee_id);
529
530 if l_user_id is not null then
531 return get_meaningful_amount_msg(
532 l_user_id,
533 p_amount,
534 p_date,
535 p_currency_code,
536 p_out_currency_code);
537 else
538 return null;
539 end if;
540
541 end get_meaningful_amount_msg_emp;
542
543
544 procedure get_meaningful_amount_emp(
545 p_employee_id IN NUMBER,
546 p_amount IN NUMBER,
547 p_date IN DATE,
548 p_currency_code IN VARCHAR2,
549 p_success_flag OUT NOCOPY VARCHAR2,
550 p_conv_amount OUT NOCOPY NUMBER,
551 p_conv_currency_code OUT NOCOPY VARCHAR2) IS
552 l_user_id NUMBER;
553 begin
554
555 l_user_id := get_user_id(p_employee_id);
556
557 if l_user_id is not null then
558 get_meaningful_amount(
559 l_user_id,
560 p_amount,
561 p_date,
562 p_currency_code,
563 p_success_flag,
564 p_conv_amount,
565 p_conv_currency_code);
566 else
567 p_success_flag := 'N';
568 end if;
569
570 end get_meaningful_amount_emp;
571
572 end ap_web_amount_util;
[Home] [Help]