1 PACKAGE BODY OKI_DBI_CURRENCY_PVT as
2 /* $Header: OKIRICUB.pls 120.0 2005/05/25 17:55:17 appldev noship $ */
3
4 /* function that calculates annualization_factor */
5 FUNCTION get_annualization_factor( p_start_date DATE,
6 p_end_date DATE ) RETURN NUMBER PARALLEL_ENABLE IS
7 l_years NUMBER;
8 l_leap_days NUMBER;
9 l_start_date DATE;
10 l_end_date DATE;
11 l_count NUMBER;
12
13 BEGIN
14 -- Initialize number of years and number of leap days.
15 l_years := 0;
16 l_leap_days := 0;
17
18 IF(p_end_date < p_start_date)
19 THEN
20 RETURN 0;
21 END IF;
22 l_start_date := p_start_date;
23 LOOP
24 l_end_date := add_months(l_start_date,12)-1;
25 l_years := l_years+1;
26
27 IF( (add_months(l_start_date,12) - l_start_date) = 366)
28 THEN
29 IF(p_end_date < l_end_date)
30 THEN
31 IF( p_end_date - (add_months(p_end_date,-12) ) = 366)
32 THEN
33 l_leap_days := l_leap_days + 1;
34 END IF;
35 ELSE
36 l_leap_days := l_leap_days+1;
37 END IF;
38 END IF;
39 l_start_date := add_months(l_start_date,12);
40 EXIT WHEN (l_start_date >= p_end_date );
41 END LOOP;
42 return 1 * (365+ (l_leap_days/l_years)) / (p_end_date+1 - p_start_date);
43 END get_annualization_factor;
44
45
46 /* *********************************************************
47 Function that sets
48 1. Transaction to functional currency conversion rate
49 2. Functional to global conversion rate
50 3. Conversion date
51 4. Set global missing_cur flag to FALSE in case of error
52 5. set rate values to
53 -1 - missing currency
54 -10 - too many rows
55 -11 - other errors
56 Return Value:
57 Conversion_date
58 ********************************************************* */
59 FUNCTION get_conversion_date
60 ( p_chr_id IN NUMBER
61 , p_curr_code IN VARCHAR2
62 , p_func_curr_code IN VARCHAR2
63 , p_creation_date IN DATE
64 ) RETURN DATE PARALLEL_ENABLE IS
65
66 l_trx_func_rate NUMBER ;
67 l_conversion_date DATE ;
68 l_conversion_type VARCHAR2(40) ;
69
70 BEGIN
71 -- Check whether or not the function was already been executed
72 -- by calls to oki_dbi_currency_rate.get_dbi_global_rate or
73 -- oki_dbi_currency_rate.get_dbi_global_rate
74 IF (p_chr_id = NVL(OKI_DBI_CURRENCY_PVT.g_chr_id, 1)) THEN
75 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
76 END IF ;
77
78 OKI_DBI_CURRENCY_PVT.g_chr_id := p_chr_id ;
79 OKI_DBI_CURRENCY_PVT.g_conversion_date := NULL ;
80 OKI_DBI_CURRENCY_PVT.g_trx_func_rate := NULL ;
81 OKI_DBI_CURRENCY_PVT.g_func_global_rate := NULL ;
82 -- Added by Arun for secondary global currency conversion changes
83 OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate := NULL ;
84
85 BEGIN
86 -- Select user entered conversion rate, date, and type
87 SELECT TO_NUMBER(DECODE(okc_currency_api.get_currency_type(
88 p_func_curr_code
89 , to_date(rul.rule_information2
90 ,'YYYY/MM/DD HH24:MI:SS'))
91 , 'EMU',rul.rule_information3
92 , rul.rule_information1)
93 ) trx_func_rate
94 , TO_DATE(rul.rule_information2, 'YYYY/MM/DD HH24:MI:SS') conversion_date
95 , rul.object1_id1 conversion_type
96 INTO l_trx_func_rate
97 , l_conversion_date
98 , l_conversion_type
99 FROM okc_rules_b rul,
100 okc_rule_groups_b rgp
101 WHERE rul.rule_information_category = 'CVN'
102 AND rul.rgp_id = rgp.id
103 AND rgp.dnz_chr_id = p_chr_id ;
104
105 -- NEW SQL to be used from 11.5.10
106
107 /* select
108 h.conversion_rate trx_func_rate
109 ,h.conversion_rate_date conversion_date
110 , h.conversion_type
111 INTO l_trx_func_rate
112 , l_conversion_date
113 , l_conversion_type
114 from okc_k_headers_b h
115 where h.id = p_chr_id
116 AND h.template_yn = 'N'
117 AND h.application_id = 515
118 AND h.scs_code IN ('SERVICE','WARRANTY');
119 */
120 --------------------------
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 -- No rates was found in the rules table for this contract.
124 -- Continue processing to get the default conversion date
125 -- rate
126 NULL ;
127
128 WHEN TOO_MANY_ROWS THEN
129 BIS_COLLECTION_UTILITIES.log(sqlerrm || ' chr_id '||
130 to_char(p_chr_id) ,3) ;
131 OKI_DBI_CURRENCY_PVT.g_conversion_date := NULL ;
132 OKI_DBI_CURRENCY_PVT.g_trx_func_rate := -10 ;
133 OKI_DBI_CURRENCY_PVT.g_func_global_rate := -10 ;
134 -- Added by Arun for secondary global currency conversion changes
135 OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate := -10 ;
136 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
137
138 WHEN OTHERS THEN
139 BIS_COLLECTION_UTILITIES.log(sqlerrm || ' chr_id '||
140 to_char(p_chr_id) ,3) ;
141 OKI_DBI_CURRENCY_PVT.g_conversion_date := NULL ;
142 OKI_DBI_CURRENCY_PVT.g_trx_func_rate := -11 ;
143 OKI_DBI_CURRENCY_PVT.g_func_global_rate := -11 ;
144 -- Added by Arun for secondary global currency conversion changes
145 OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate := -11 ;
146 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
147 END ;
148
149 OKI_DBI_CURRENCY_PVT.g_conversion_date := get_conversion_rate(p_chr_id, p_curr_code,
150 p_func_curr_code,
151 p_creation_date,
152 l_conversion_date,
153 l_conversion_type,
154 l_trx_func_rate);
155
156 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
161
162 END get_conversion_date ;
163
164 /****************************************************
165 Overloaded get_conversion_date function for incremental load
166 Changes as part of DBI 7.0
167 ********************************************************* */
168 FUNCTION get_conversion_date
169 ( p_chr_id IN NUMBER
170 , p_curr_code IN VARCHAR2
171 , p_func_curr_code IN VARCHAR2
172 , p_creation_date IN DATE
173 , p_conversion_date IN DATE
174 , p_conversion_type IN VARCHAR2
175 , p_trx_func_rate in NUMBER
176 ) RETURN DATE PARALLEL_ENABLE IS
177
178 BEGIN
179
180 -- Check whether or not the function was already been executed
181 -- by calls to oki_dbi_currency_rate.get_dbi_global_rate or
182 -- oki_dbi_currency_rate.get_dbi_global_rate
183 IF (p_chr_id = NVL(OKI_DBI_CURRENCY_PVT.g_chr_id, 1)) THEN
184 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
185 END IF ;
186
187 OKI_DBI_CURRENCY_PVT.g_chr_id := p_chr_id ;
188 OKI_DBI_CURRENCY_PVT.g_conversion_date := NULL ;
189 OKI_DBI_CURRENCY_PVT.g_trx_func_rate := NULL ;
190 OKI_DBI_CURRENCY_PVT.g_func_global_rate := NULL ;
191 -- Added by Arun for secondary global currency conversion changes
192 OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate := NULL ;
193
194 OKI_DBI_CURRENCY_PVT.g_conversion_date := get_conversion_rate(p_chr_id, p_curr_code,
195 p_func_curr_code,
196 p_creation_date,
197 p_conversion_date,
198 p_conversion_type,
199 p_trx_func_rate);
200
201 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
206
207 END get_conversion_date ;
208
209 -- -------------------------------
210 -- get_conversion_rate
211 -- -------------------------------
212 /* *******************************************************
213 Function to get conversion rate and initialize gloabl variables.
214 This is executed for initial load by bypassing get_conversion_date
215 ******************************************************* */
216
217 Function get_conversion_rate ( p_chr_id IN NUMBER
218 , p_curr_code IN VARCHAR2
219 , p_func_curr_code IN VARCHAR2
220 , p_creation_date IN DATE
221 , p_conv_date IN DATE
222 , p_conv_type IN VARCHAR2
223 , p_trx_func_rate in NUMBER
224 ) RETURN DATE PARALLEL_ENABLE IS
225
226 l_success_flag BOOLEAN := TRUE ;
227 l_max_roll_days NUMBER ;
228 l_func_global_rate NUMBER ;
229 -- Added by Arun for secondary global currency conversion changes
230 l_func_sglobal_rate NUMBER ;
231 l_conv_date DATE;
232 l_conv_type VARCHAR2(40);
233 l_trx_func_rate NUMBER;
234 l_global_curr VARCHAR(30);
235 -- Added by Arun for secondary global currency conversion changes
236 l_sglobal_curr VARCHAR(30);
237 --Added by Arun Variables used to say whether the functional and global currencies are the same
238 l_isFuncGlobalEqual_flag BOOLEAN := FALSE ;
239 l_isFuncSGlobalEqual_flag BOOLEAN := FALSE ;
240
241 BEGIN
242 l_max_roll_days := 0 ;
243 l_global_curr := bis_common_parameters.get_currency_code;
244 -- Added by Arun for secondary global currency conversion changes
245 l_sglobal_curr := bis_common_parameters.get_secondary_currency_code;
246 -- Check whether or not the function was already been executed
247 -- by calls to oki_dbi_currency_rate.get_dbi_global_rate or
248 -- oki_dbi_currency_rate.get_dbi_global_rate
249 IF ( OKI_DBI_LOAD_CLEB_PVT.g_load_type = 'INITIAL LOAD' and
250 p_chr_id = NVL(OKI_DBI_CURRENCY_PVT.g_chr_id, 1)) THEN
251 RETURN OKI_DBI_CURRENCY_PVT.g_conversion_date ;
252 END IF ;
253
254 OKI_DBI_CURRENCY_PVT.g_chr_id := p_chr_id ;
255 OKI_DBI_CURRENCY_PVT.g_conversion_date := NULL ;
256 OKI_DBI_CURRENCY_PVT.g_trx_func_rate := NULL ;
257 OKI_DBI_CURRENCY_PVT.g_func_global_rate := NULL ;
258 -- Added by Arun for secondary global currency conversion changes
259 OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate := NULL ;
260 l_conv_date := p_conv_date;
261 l_conv_type := p_conv_type;
262 l_trx_func_rate := p_trx_func_rate;
263
264 -- If conversion date is not populated set it to the default
265 -- value, creation_date
266 IF (l_conv_date IS NULL) THEN
267 l_conv_date := p_creation_date ;
268 END IF ;
269
270 -- If conversion type is not populated set it to the default
271 -- value, BIS global conversion type profile
272 IF (l_conv_type IS NULL) THEN
273 l_conv_type := bis_common_parameters.get_treasury_rate_type ;
274 IF (l_conv_type IS NULL) THEN
275 l_conv_type := bis_common_parameters.get_rate_type ;
276 END IF ;
277 END IF ;
278
279
280
281 IF (p_curr_code <> p_func_curr_code) THEN
282 -- Transaction is in a currency other than the functional currency
283 IF (l_trx_func_rate IS NULL) THEN
284 -- Transactional to functional rate does not exist in the contract
285 -- Get the transactional rate using the date and conversion type if they are in
286 -- the contract, otherwise use the default values
287 -- added new FII_CURRENCY pkg fix for trx - > functional euro issues
288 l_trx_func_rate := fii_currency.get_rate(p_curr_code,p_func_curr_code,l_conv_date,l_conv_type);
289
290 IF (l_trx_func_rate IS NULL OR l_trx_func_rate < 0) THEN
291 -- No rate exists in the contract or no rate exists for the given
292 -- conversion date and type
293 --l_trx_func_rate := -1 ;
294 l_success_flag := FALSE ;
295 END IF ;
296 END IF ;
297
298 OKI_DBI_CURRENCY_PVT.g_trx_func_rate := l_trx_func_rate ;
299
300 ELSE
301 -- Transaction is in the same currency as functional currency
302 OKI_DBI_CURRENCY_PVT.g_trx_func_rate := 1 ;
303 l_conv_type := null;
304 IF (p_curr_code = l_global_curr ) then
305 l_func_global_rate:=1;
306 -- l_conv_date := null;
307 -- Added by Arun for bypassing FII API Calls
308 l_isFuncGlobalEqual_flag:= TRUE;
309 END IF;
310 IF (p_curr_code = l_sglobal_curr ) then
311 l_func_sglobal_rate:=1;
312 -- l_conv_date := null;
313 -- Added by Arun for bypassing FII API Calls
314 l_isFuncSGlobalEqual_flag:= TRUE;
315 END IF;
316 END IF ;
317
318 -- Get the functional to global conversion rate
319 --Find the rates only when the functional and global currencies are different
320 --If condition added by Arun
321 IF(NOT l_isFuncGlobalEqual_flag) THEN
322 l_func_global_rate := fii_currency.get_global_rate_primary(
323 p_func_curr_code
324 , l_conv_date) ;
325 END IF;
326
327 IF (l_func_global_rate IS NULL OR l_func_global_rate < 0) THEN
328 -- No rate exists for the given exchange date and rate type
329 --l_func_global_rate := -1 ;
330 l_success_flag := FALSE ;
331 END IF ;
332
333 -- Added by Arun for secondary global currency conversion changes
334 --Get the functional to secondary global conversion rate
335 --Find the rates only when the functional and global currencies are different
336 --If condition added by Arun
337 IF(NOT l_isFuncSGlobalEqual_flag) THEN
338 l_func_sglobal_rate := fii_currency.get_global_rate_secondary(
339 p_func_curr_code
340 , l_conv_date) ;
341 END IF;
342
343 IF (l_func_sglobal_rate IS NULL OR l_func_sglobal_rate < 0) THEN
344 -- No rate exists for the given exchange date and rate type
345 -- l_func_sglobal_rate := -1 ;
346 l_success_flag := FALSE ;
347 END IF ;
348
349
350 OKI_DBI_CURRENCY_PVT.g_func_global_rate := l_func_global_rate ;
351 -- Added by Arun for secondary global currency conversion changes
352 OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate := l_func_sglobal_rate ;
353 OKI_DBI_CURRENCY_PVT.g_conversion_date := l_conv_date ;
354 OKI_DBI_CURRENCY_PVT.g_trx_rate_type := l_conv_type ;
355
356 RETURN l_conv_date;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 RETURN l_conv_date;
361
362 END get_conversion_rate ;
363 -- -------------------------------
364 -- get_global_currency_rate_init
365 -- -------------------------------
366 /* *******************************************************
367 Function to get functional to global currency for INITIAL load
368 ******************************************************* */
369 FUNCTION get_dbi_global_rate_init
370 ( p_chr_id IN NUMBER
371 , p_curr_code IN VARCHAR2
372 , p_func_curr_code IN VARCHAR2
373 , p_creation_date IN DATE
374 , p_conv_date IN DATE
375 , p_conv_type IN VARCHAR2
376 , p_trx_func_rate in NUMBER
377 ) RETURN NUMBER PARALLEL_ENABLE IS
378
379 l_conversion_date DATE ;
380 BEGIN
381 -- Check whether or not the function was already been executed
382 -- by calls to oki_dbi_currency_rate.get_dbi_global_rate or
383 -- oki_dbi_currency_rate.get_dbi_global_rate
384 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id ,1)) THEN
385 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_rate(
386 p_chr_id
387 , p_curr_code
388 , p_func_curr_code
389 , p_creation_date
390 ,p_conv_date
391 , p_conv_type
392 , p_trx_func_rate ) ;
393
394 END IF ;
395 RETURN OKI_DBI_CURRENCY_PVT.g_func_global_rate ;
396 EXCEPTION
397 WHEN OTHERS THEN
398 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
399 fnd_message.set_name( application => 'FND'
400 , name => 'CRM-DEBUG ERROR' ) ;
401 fnd_message.set_token( token => 'ROUTINE'
402 , value => 'OKI_DBI_CURRENCY_PVT.get_dbi_global_rate_init ' ) ;
403 bis_collection_utilities.put_line(fnd_message.get) ;
404 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
405 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
406 END get_dbi_global_rate_init ;
407
408 /* ***********************************************************************
409 Function to get functional to secondary global currency for INITIAL load
410 *******************************************************************/
411 FUNCTION get_dbi_sglobal_rate_init
412 ( p_chr_id IN NUMBER
413 , p_curr_code IN VARCHAR2
414 , p_func_curr_code IN VARCHAR2
415 , p_creation_date IN DATE
416 , p_conv_date IN DATE
417 , p_conv_type IN VARCHAR2
418 , p_trx_func_rate in NUMBER
419 ) RETURN NUMBER PARALLEL_ENABLE IS
420
421 l_conversion_date DATE ;
422
423 BEGIN
424 -- Check whether or not the conversion rate was calculated alredy for the
425 -- contract by comparing the global value of g_chr_id and the p_chr_id
426 -- passed in as parameter
427 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id ,1)) THEN
428 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_rate(
429 p_chr_id
430 , p_curr_code
431 , p_func_curr_code
432 , p_creation_date
433 , p_conv_date
434 , p_conv_type
435 , p_trx_func_rate ) ;
436
437 END IF ;
438 RETURN OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate ;
439 EXCEPTION
440 WHEN OTHERS THEN
441 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
442 fnd_message.set_name( application => 'FND'
443 , name => 'CRM-DEBUG ERROR' ) ;
444 fnd_message.set_token( token => 'ROUTINE'
445 , value => 'OKI_DBI_CURRENCY_PVT.get_dbi_sglobal_rate_init ' ) ;
446 bis_collection_utilities.put_line(fnd_message.get) ;
447 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
448 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
449 END get_dbi_sglobal_rate_init ;
450
451 -- -------------------------------
452 -- get_global_currency_rate
453 -- -------------------------------
454 /* *******************************************************
455 Function to get functional to global currency conversion rate
456 ******************************************************* */
457 FUNCTION get_dbi_global_rate
458 ( p_chr_id IN NUMBER
459 , p_curr_code IN VARCHAR2
460 , p_func_curr_code IN VARCHAR2
461 , p_creation_date IN DATE
462 , p_conversion_date IN DATE
463 , p_conversion_type IN VARCHAR2
464 , p_trx_func_rate in NUMBER
465 ) RETURN NUMBER PARALLEL_ENABLE IS
466
467 l_conversion_date DATE ;
468
469 BEGIN
470 -- Check whether or not the function was already been executed
471 -- by calls to oki_dbi_currency_rate.get_dbi_global_rate or
472 -- oki_dbi_currency_rate.get_dbi_global_rate
473 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id ,1)) THEN
474 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_date(
475 p_chr_id
476 , p_curr_code
477 , p_func_curr_code
478 , p_creation_date
479 , p_conversion_date
480 , p_conversion_type
481 , p_trx_func_rate ) ;
482
483 END IF ;
484 RETURN OKI_DBI_CURRENCY_PVT.g_func_global_rate ;
485 EXCEPTION
486 WHEN OTHERS THEN
487 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
488 fnd_message.set_name( application => 'FND'
489 , name => 'CRM-DEBUG ERROR' ) ;
490 fnd_message.set_token( token => 'ROUTINE'
491 , value => 'OKI_DBI_CURRENCY_PVT.get_dbi_global_rate ' ) ;
492 bis_collection_utilities.put_line(fnd_message.get) ;
493 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
494 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
495 END get_dbi_global_rate ;
496
497 -- -------------------------------
498 -- get_secondary_global_currency_rate
499 -- -------------------------------
500 /* ****************************************************************************
501 Function to get functional to secondary global currency conversion rate
502 during Incremental load
503 **************************************************************************/
504 FUNCTION get_dbi_sglobal_rate
505 ( p_chr_id IN NUMBER
506 , p_curr_code IN VARCHAR2
507 , p_func_curr_code IN VARCHAR2
508 , p_creation_date IN DATE
509 , p_conversion_date IN DATE
510 , p_conversion_type IN VARCHAR2
511 , p_trx_func_rate in NUMBER
512 ) RETURN NUMBER PARALLEL_ENABLE IS
513
514 l_conversion_date DATE ;
515
516 BEGIN
517 -- Check whether or not the conversion rate was calculated alredy for the
518 -- contract by comparing the global value of g_chr_id and the p_chr_id
519 -- passed in as parameter
520 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id ,1)) THEN
521 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_date(
522 p_chr_id
523 , p_curr_code
524 , p_func_curr_code
525 , p_creation_date
526 , p_conversion_date
527 , p_conversion_type
528 , p_trx_func_rate ) ;
529
530 END IF ;
531 RETURN OKI_DBI_CURRENCY_PVT.g_func_sglobal_rate ;
532 EXCEPTION
533 WHEN OTHERS THEN
534 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
535 fnd_message.set_name( application => 'FND'
536 , name => 'CRM-DEBUG ERROR' ) ;
537 fnd_message.set_token( token => 'ROUTINE'
538 , value => 'OKI_DBI_CURRENCY_PVT.get_dbi_sglobal_rate ' ) ;
539 bis_collection_utilities.put_line(fnd_message.get) ;
540 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
541 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
542 END get_dbi_sglobal_rate ;
543
544 /* *******************************************************
545 Function to get transaction to functional currency conversion rate
546 ******************************************************* */
547 FUNCTION get_trx_func_rate
548 ( p_chr_id IN NUMBER
549 , p_curr_code IN VARCHAR2
550 , p_func_curr_code IN VARCHAR2
551 , p_creation_date IN DATE
552 , p_conversion_date IN DATE
553 , p_conversion_type IN VARCHAR2
554 , p_trx_func_rate in NUMBER
555 ) RETURN NUMBER PARALLEL_ENABLE IS
556
557 l_conversion_date DATE ;
558
559 BEGIN
560 -- Check whether or not the function was already been executed
561 -- by calls to oki_dbi_currency_rate.get_dbi_global_rate or
562 -- oki_dbi_currency_rate.get_dbi_global_rate
563 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id,1)) THEN
564 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_date(
565 p_chr_id
566 , p_curr_code
567 , p_func_curr_code
568 , p_creation_date
569 , p_conversion_date
570 , p_conversion_type
571 , p_trx_func_rate ) ;
572
573 END IF ;
574 RETURN OKI_DBI_CURRENCY_PVT.g_trx_func_rate ;
575 EXCEPTION
576 WHEN OTHERS THEN
577 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
578 fnd_message.set_name( application => 'FND'
579 , name => 'CRM-DEBUG ERROR' ) ;
580 fnd_message.set_token( token => 'ROUTINE'
581 , value => 'OKI_DBI_CURRENCY_PVT.get_trx_func_rate ' ) ;
582 bis_collection_utilities.put_line(fnd_message.get) ;
583 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
584 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
585 END get_trx_func_rate ;
586
587 -----------------------
588 /* *******************************************************
589 Function to get transaction to functional conversion rate Initially
590 ******************************************************* */
591 FUNCTION get_trx_func_rate_init
592 ( p_chr_id IN NUMBER
593 , p_curr_code IN VARCHAR2
594 , p_func_curr_code IN VARCHAR2
595 , p_creation_date IN DATE
596 , p_conv_date IN DATE
597 , p_conv_type IN VARCHAR2
598 , p_trx_func_rate in NUMBER
599 ) RETURN NUMBER PARALLEL_ENABLE IS
600
601 l_conversion_date DATE ;
602
603 BEGIN
604 -- Check whether or not the function was already been executed
605 -- by calls to oki_dbi_currency_rate.get_dbi_global_rate or
606 -- oki_dbi_currency_rate.get_dbi_global_rate
607 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id,1)) THEN
608 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_rate(
609 p_chr_id
610 , p_curr_code
611 , p_func_curr_code
612 , p_creation_date
613 , p_conv_date
614 , p_conv_type
615 , p_trx_func_rate ) ;
616
617 END IF ;
618 RETURN OKI_DBI_CURRENCY_PVT.g_trx_func_rate ;
619 EXCEPTION
620 WHEN OTHERS THEN
621 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
622 fnd_message.set_name( application => 'FND'
623 , name => 'CRM-DEBUG ERROR' ) ;
624 fnd_message.set_token( token => 'ROUTINE'
625 , value => 'OKI_DBI_CURRENCY_PVT.get_trx_func_rate_init ' ) ;
626 bis_collection_utilities.put_line(fnd_message.get) ;
627 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
628 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
629 END get_trx_func_rate_init ;
630
631
632 /*******************************************************************************
633 Description: Function to get functional rate type
634 *******************************************************************************/
635 FUNCTION get_trx_rate_type
636 ( p_chr_id IN NUMBER
637 , p_curr_code IN VARCHAR2
638 , p_func_curr_code IN VARCHAR2
639 , p_creation_date IN DATE
640 , p_conversion_date IN DATE
641 , p_conversion_type IN VARCHAR2
642 , p_trx_func_rate in NUMBER
643 ) RETURN VARCHAR2 IS
644
645 l_conversion_date DATE ;
646
647 BEGIN
648 -- If oki_dbi_currency_pvt.get_conversion_date has already been called,
649 -- then oki_dbi_currency_pvt.g_trx_rate_type has already been set.
650 -- There is no need to call it again to set the value.
651 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id, 1)) THEN
652 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_date(
653 p_chr_id
654 , p_curr_code
655 , p_func_curr_code
656 , p_creation_date
657 , p_conversion_date
658 , p_conversion_type
659 , p_trx_func_rate ) ;
660
661 END IF ;
662 RETURN OKI_DBI_CURRENCY_PVT.g_trx_rate_type ;
663 EXCEPTION
664 WHEN OTHERS THEN
665 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
666 fnd_message.set_name( application => 'FND'
667 , name => 'CRM-DEBUG ERROR' ) ;
668 fnd_message.set_token( token => 'ROUTINE'
669 , value => 'OKI_DBI_CURRENCY_PVT.get_trx_rate_type ' ) ;
670 bis_collection_utilities.put_line(fnd_message.get) ;
671 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
672 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
673 END get_trx_rate_type ;
674 -----------------------
675 /*******************************************************************************
676 Description: Function to get functional rate type for Initial load
677 *******************************************************************************/
678 FUNCTION get_trx_rate_type_init
679 ( p_chr_id IN NUMBER
680 , p_curr_code IN VARCHAR2
681 , p_func_curr_code IN VARCHAR2
682 , p_creation_date IN DATE
683 , p_conv_date IN DATE
684 , p_conv_type IN VARCHAR2
685 , p_trx_func_rate in NUMBER
686 ) RETURN VARCHAR2 PARALLEL_ENABLE IS
687
688 l_conversion_date DATE ;
689
690 BEGIN
691 -- If oki_dbi_currency_pvt.get_conversion_rate has already been called,
692 -- then oki_dbi_currency_pvt.g_trx_rate_type has already been set.
693 -- There is no need to call it again to set the value.
694 IF (p_chr_id <> NVL(OKI_DBI_CURRENCY_PVT.g_chr_id, 1)) THEN
695 l_conversion_date := OKI_DBI_CURRENCY_PVT.get_conversion_rate(
696 p_chr_id
697 , p_curr_code
698 , p_func_curr_code
699 , p_creation_date ,p_conv_date
700 , p_conv_type
701 , p_trx_func_rate ) ;
702
703 END IF ;
704 RETURN OKI_DBI_CURRENCY_PVT.g_trx_rate_type ;
705 EXCEPTION
706 WHEN OTHERS THEN
707 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
708 fnd_message.set_name( application => 'FND'
709 , name => 'CRM-DEBUG ERROR' ) ;
710 fnd_message.set_token( token => 'ROUTINE'
711 , value => 'OKI_DBI_CURRENCY_PVT.get_trx_rate_type_init ' ) ;
712 bis_collection_utilities.put_line(fnd_message.get) ;
713 bis_collection_utilities.put_line( ' CHR_ID : ' || to_char(p_chr_id) ) ;
714 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
715 END get_trx_rate_type_init ;
716
717 END OKI_DBI_CURRENCY_PVT;
[Home] [Help]