[Home] [Help]
PACKAGE BODY: APPS.GL_DAILY_RATES_PKG
Source
1 PACKAGE BODY GL_DAILY_RATES_PKG as
2 /* $Header: glirtdyb.pls 120.10 2005/05/05 01:21:06 kvora ship $ */
3 --
4 -- Package
5 -- gl_daily_rates_pkg
6 -- Purpose
7 -- To contain validation and insertion routines for gl_daily_rates
8 -- History
9 -- 07-29-97 W Wong Created
10
11 --
12 -- Procedure
13 -- Insert_Row
14 --
15 -- Purpose
16 -- Inserts two rows into gl_daily_rates:
17 -- one for the original conversion rate ( From Currency -> To Currency )
18 -- one for the inverse conversion rate ( To Currency -> From Currency )
19 --
20 -- History
21 -- 07-29-97 W Wong Created
22 --
23 -- Arguments
24 -- All the columns of the table GL_DAILY_RATES and
25 -- X_Average_Balances_Used Average Balances Used
26 -- X_Euro_Currency Currency Code of EURO
27 --
28 -- Example
29 -- gl_daily_rates.Insert_Row(....);
30 --
31 -- Notes
32 --
33 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
34 X_Inverse_Rowid IN OUT NOCOPY VARCHAR2,
35 X_From_Currency VARCHAR2,
36 X_To_Currency VARCHAR2,
37 X_Conversion_Date DATE,
38 X_Conversion_Type VARCHAR2,
39 X_Conversion_Rate NUMBER,
40 X_Inverse_Conversion_Rate NUMBER,
41 X_Status_Code IN OUT NOCOPY VARCHAR2,
42 X_Creation_Date DATE,
43 X_Created_By NUMBER,
44 X_Last_Update_Date DATE,
45 X_Last_Updated_By NUMBER,
46 X_Last_Update_Login NUMBER,
47 X_Context VARCHAR2,
48 X_Attribute1 VARCHAR2,
49 X_Attribute2 VARCHAR2,
50 X_Attribute3 VARCHAR2,
51 X_Attribute4 VARCHAR2,
52 X_Attribute5 VARCHAR2,
53 X_Attribute6 VARCHAR2,
54 X_Attribute7 VARCHAR2,
55 X_Attribute8 VARCHAR2,
56 X_Attribute9 VARCHAR2,
57 X_Attribute10 VARCHAR2,
58 X_Attribute11 VARCHAR2,
59 X_Attribute12 VARCHAR2,
60 X_Attribute13 VARCHAR2,
61 X_Attribute14 VARCHAR2,
62 X_Attribute15 VARCHAR2,
63 X_Average_Balances_Used VARCHAR2,
64 X_Euro_Currency VARCHAR2
65 ) IS
66 CURSOR C IS SELECT rowid FROM GL_DAILY_RATES
67 WHERE from_currency = X_From_Currency
68 AND to_currency = X_To_Currency
69 AND conversion_date = X_Conversion_Date
70 AND conversion_type = X_Conversion_Type;
71
72 CURSOR Inverse_C IS SELECT rowid FROM GL_DAILY_RATES
73 WHERE from_currency = X_To_Currency
74 AND to_currency = X_From_Currency
75 AND conversion_date = X_Conversion_Date
76 AND conversion_type = X_Conversion_Type;
77
78 ekey VARCHAR2(100);
79 BEGIN
80 --
81 -- Set the status code to 'O' if the X_To_Currency and X_Conversion_Type is
82 -- used in ledgers.
83 -- Set status code to 'C' otherwise.
84 --
85 IF ( GL_DAILY_RATES_PKG.Used_In_Ledger( X_From_Currency,
86 X_To_Currency,
87 X_Conversion_Type,
88 X_Euro_Currency,
89 X_Conversion_Date )) THEN
90 X_Status_Code := 'O';
91
92 ELSE
93 X_Status_Code := 'C';
94 END IF;
95
96 -- Insert the row with conversion rate
97 INSERT INTO GL_DAILY_RATES(
98 from_currency,
99 to_currency,
100 conversion_date,
101 conversion_type,
102 conversion_rate,
103 status_code,
104 creation_date,
105 created_by,
106 last_update_date,
107 last_updated_by,
108 last_update_login,
109 context,
110 attribute1,
111 attribute2,
112 attribute3,
113 attribute4,
114 attribute5,
115 attribute6,
116 attribute7,
117 attribute8,
118 attribute9,
119 attribute10,
120 attribute11,
121 attribute12,
122 attribute13,
123 attribute14,
124 attribute15
125 ) VALUES (
126 X_From_Currency,
127 X_To_Currency,
128 X_Conversion_Date,
129 X_Conversion_Type,
130 X_Conversion_Rate,
131 X_Status_Code,
132 X_Creation_Date,
133 X_Created_By,
134 X_Last_Update_Date,
135 X_Last_Updated_By,
136 X_Last_Update_Login,
137 X_Context,
138 X_Attribute1,
139 X_Attribute2,
140 X_Attribute3,
141 X_Attribute4,
142 X_Attribute5,
143 X_Attribute6,
144 X_Attribute7,
145 X_Attribute8,
146 X_Attribute9,
147 X_Attribute10,
148 X_Attribute11,
149 X_Attribute12,
150 X_Attribute13,
151 X_Attribute14,
152 X_Attribute15
153 );
154
155 OPEN C;
156 FETCH C INTO X_Rowid;
157 if ( C%NOTFOUND ) then
158 CLOSE C;
159 RAISE NO_DATA_FOUND;
160 end if;
161 CLOSE C;
162
163 -- Insert the row with new inverse conversion rate
164 INSERT INTO GL_DAILY_RATES(
165 from_currency,
166 to_currency,
167 conversion_date,
168 conversion_type,
169 conversion_rate,
170 status_code,
171 creation_date,
172 created_by,
173 last_update_date,
174 last_updated_by,
175 last_update_login,
176 context,
177 attribute1,
178 attribute2,
179 attribute3,
180 attribute4,
181 attribute5,
182 attribute6,
183 attribute7,
184 attribute8,
185 attribute9,
186 attribute10,
187 attribute11,
188 attribute12,
189 attribute13,
190 attribute14,
191 attribute15
192 ) VALUES (
193 X_To_Currency,
194 X_From_Currency,
195 X_Conversion_Date,
196 X_Conversion_Type,
197 X_Inverse_Conversion_Rate,
198 X_Status_Code,
199 X_Creation_Date,
200 X_Created_By,
201 X_Last_Update_Date,
202 X_Last_Updated_By,
203 X_Last_Update_Login,
204 X_Context,
205 X_Attribute1,
206 X_Attribute2,
207 X_Attribute3,
208 X_Attribute4,
209 X_Attribute5,
210 X_Attribute6,
211 X_Attribute7,
212 X_Attribute8,
213 X_Attribute9,
214 X_Attribute10,
215 X_Attribute11,
216 X_Attribute12,
217 X_Attribute13,
218 X_Attribute14,
219 X_Attribute15
220 );
221
222 OPEN Inverse_C;
223 FETCH Inverse_C INTO X_Inverse_Rowid;
224 if ( Inverse_C%NOTFOUND ) then
225 CLOSE Inverse_C;
226 RAISE NO_DATA_FOUND;
227 end if;
228 CLOSE Inverse_C;
229
230 ekey := X_From_Currency||':'||X_To_Currency||':'||X_Conversion_Type||':'
231 ||to_char(X_Conversion_Date,'RRDDDSSSSS')||':'
232 ||to_char(X_Conversion_date,'RRDDDSSSSS')||':'
233 ||to_char(sysdate, 'RRDDDSSSSS');
234
235 -- Raise the specify conversion event
236 gl_business_events.raise(
237 p_event_name => 'oracle.apps.gl.CurrencyConversionRates.dailyRate.specify',
238 p_event_key => ekey,
239 p_parameter_name1 => 'FROM_CURRENCY',
240 p_parameter_value1 => X_From_Currency,
241 p_parameter_name2 => 'TO_CURRENCY',
242 p_parameter_value2 => X_To_Currency,
243 p_parameter_name3 => 'FROM_CONVERSION_DATE',
244 p_parameter_value3 => to_char(X_Conversion_Date,'YYYY/MM/DD'),
245 p_parameter_name4 => 'FROM_CONVERSION_DATE',
246 p_parameter_value4 => to_char(X_Conversion_Date,'YYYY/MM/DD'),
247 p_parameter_name5 => 'CONVERSION_TYPE',
248 p_parameter_value5 => X_Conversion_Type,
249 p_parameter_name6 => 'CONVERSION_RATE',
250 p_parameter_value6 => to_char(X_Conversion_Rate,
251 '99999999999999999999.99999999999999999999'),
252 p_parameter_name7 => 'INVERSE_CONVERSION_RATE',
253 p_parameter_value7 => to_char(X_Inverse_Conversion_Rate,
254 '99999999999999999999.99999999999999999999')
255 );
256
257 END Insert_Row;
258
259
260 --
261 -- Procedure
262 -- Lock_Row
263 --
264 -- Purpose
265 -- Locks a pair of rows in gl_daily_rates
266 --
267 -- History
268 -- 07-29-97 W Wong Created
269 --
270 -- Arguments
271 -- All the columns of the table GL_DAILY_RATES
272 --
273 -- Example
274 -- gl_daily_rates.Lock_Row(....);
275 --
276 -- Notes
277 --
278 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
279 X_Inverse_Rowid VARCHAR2,
280 X_From_Currency VARCHAR2,
281 X_To_Currency VARCHAR2,
282 X_Conversion_Date DATE,
283 X_Conversion_Type VARCHAR2,
284 X_Conversion_Rate NUMBER,
285 X_Inverse_Conversion_Rate NUMBER,
286 X_Status_Code VARCHAR2,
287 X_Creation_Date DATE,
288 X_Created_By NUMBER,
289 X_Last_Update_Date DATE,
290 X_Last_Updated_By NUMBER,
291 X_Last_Update_Login NUMBER,
292 X_Context VARCHAR2,
293 X_Attribute1 VARCHAR2,
294 X_Attribute2 VARCHAR2,
295 X_Attribute3 VARCHAR2,
296 X_Attribute4 VARCHAR2,
297 X_Attribute5 VARCHAR2,
298 X_Attribute6 VARCHAR2,
299 X_Attribute7 VARCHAR2,
300 X_Attribute8 VARCHAR2,
301 X_Attribute9 VARCHAR2,
302 X_Attribute10 VARCHAR2,
303 X_Attribute11 VARCHAR2,
304 X_Attribute12 VARCHAR2,
305 X_Attribute13 VARCHAR2,
306 X_Attribute14 VARCHAR2,
307 X_Attribute15 VARCHAR2
308 ) IS
309
310 CURSOR C IS
311 SELECT *
312 FROM GL_DAILY_RATES
313 WHERE rowid = X_Rowid
314 FOR UPDATE of from_currency NOWAIT;
315 Recinfo C%ROWTYPE;
316
317 CURSOR Inverse_C IS
318 SELECT *
319 FROM GL_DAILY_RATES
320 WHERE rowid = X_Inverse_Rowid
321 FOR UPDATE of from_currency NOWAIT;
322 Inverse_Recinfo Inverse_C%ROWTYPE;
323
324 BEGIN
325 OPEN C;
326 FETCH C INTO Recinfo;
327 if ( C%NOTFOUND ) then
328 CLOSE C;
329 RAISE NO_DATA_FOUND;
330 end if;
331 CLOSE C;
332
333 if (
334 ((Recinfo.from_currency = X_From_Currency) OR
335 ( (Recinfo.from_currency IS NULL)
336 AND (X_From_Currency IS NULL)))
337 AND ((Recinfo.to_currency = X_To_Currency) OR
338 ( (Recinfo.to_currency IS NULL)
339 AND (X_To_Currency IS NULL)))
340 AND ((Recinfo.conversion_date = X_Conversion_Date) OR
341 ( (Recinfo.conversion_date IS NULL)
342 AND (X_Conversion_Date IS NULL)))
343 AND ((Recinfo.conversion_type = X_Conversion_Type) OR
344 ( (Recinfo.conversion_type IS NULL)
345 AND (X_Conversion_Type IS NULL)))
346 AND ((Recinfo.conversion_rate = X_Conversion_Rate) OR
347 ( (Recinfo.conversion_rate IS NULL)
348 AND (X_Conversion_Rate IS NULL)))
349 AND ((Recinfo.status_code = X_Status_Code) OR
350 ( (Recinfo.status_code IS NULL)
351 AND (X_Status_Code IS NULL)))
352 AND ((Recinfo.context = X_Context) OR
353 ( (Recinfo.context IS NULL)
354 AND (X_Context IS NULL)))
355 AND ((Recinfo.attribute1 = X_Attribute1) OR
356 ( (Recinfo.attribute1 IS NULL)
357 AND (X_Attribute1 IS NULL)))
358 AND ((Recinfo.attribute2 = X_Attribute2) OR
359 ( (Recinfo.attribute2 IS NULL)
360 AND (X_Attribute2 IS NULL)))
361 AND ((Recinfo.attribute3 = X_Attribute3) OR
362 ( (Recinfo.attribute3 IS NULL)
363 AND (X_Attribute3 IS NULL)))
364 AND ((Recinfo.attribute4 = X_Attribute4) OR
365 ( (Recinfo.attribute4 IS NULL)
366 AND (X_Attribute4 IS NULL)))
367 AND ((Recinfo.attribute5 = X_Attribute5) OR
368 ( (Recinfo.attribute5 IS NULL)
369 AND (X_Attribute5 IS NULL)))
370 AND ((Recinfo.attribute6 = X_Attribute6) OR
371 ( (Recinfo.attribute6 IS NULL)
372 AND (X_Attribute6 IS NULL)))
373 AND ((Recinfo.attribute7 = X_Attribute7) OR
374 ( (Recinfo.attribute7 IS NULL)
375 AND (X_Attribute7 IS NULL)))
376 AND ((Recinfo.attribute8 = X_Attribute8) OR
377 ( (Recinfo.attribute8 IS NULL)
378 AND (X_Attribute8 IS NULL)))
379 AND ((Recinfo.attribute9 = X_Attribute9) OR
380 ( (Recinfo.attribute9 IS NULL)
381 AND (X_Attribute9 IS NULL)))
382 AND ((Recinfo.attribute10 = X_Attribute10) OR
383 ( (Recinfo.attribute10 IS NULL)
384 AND (X_Attribute10 IS NULL)))
385 AND ((Recinfo.attribute11 = X_Attribute11) OR
386 ( (Recinfo.attribute11 IS NULL)
387 AND (X_Attribute11 IS NULL)))
388 AND ((Recinfo.attribute12 = X_Attribute12) OR
389 ( (Recinfo.attribute12 IS NULL)
390 AND (X_Attribute12 IS NULL)))
391 AND ((Recinfo.attribute13 = X_Attribute13) OR
392 ( (Recinfo.attribute13 IS NULL)
393 AND (X_Attribute13 IS NULL)))
394 AND ((Recinfo.attribute14 = X_Attribute14) OR
395 ( (Recinfo.attribute14 IS NULL)
396 AND (X_Attribute14 IS NULL)))
397 AND ((Recinfo.attribute15 = X_Attribute15) OR
398 ( (Recinfo.attribute15 IS NULL)
399 AND (X_Attribute15 IS NULL)))
400 ) then
401 return;
402 else
403 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
404 APP_EXCEPTION.RAISE_EXCEPTION;
405 end if;
406
407
408 OPEN Inverse_C;
409 FETCH Inverse_C INTO Inverse_Recinfo;
410 if ( Inverse_C%NOTFOUND ) then
411 CLOSE Inverse_C;
412 RAISE NO_DATA_FOUND;
413 end if;
414 CLOSE Inverse_C;
415
416 if (
417 ((Inverse_Recinfo.from_currency = X_From_Currency) OR
418 ( (Inverse_Recinfo.from_currency IS NULL)
419 AND (X_From_Currency IS NULL)))
420 AND ((Inverse_Recinfo.to_currency = X_To_Currency) OR
421 ( (Inverse_Recinfo.to_currency IS NULL)
422 AND (X_To_Currency IS NULL)))
423 AND ((Inverse_Recinfo.conversion_date = X_Conversion_Date) OR
424 ( (Inverse_Recinfo.conversion_date IS NULL)
425 AND (X_Conversion_Date IS NULL)))
426 AND ((Inverse_Recinfo.conversion_type = X_Conversion_Type) OR
427 ( (Inverse_Recinfo.conversion_type IS NULL)
428 AND (X_Conversion_Type IS NULL)))
429 AND ((Inverse_Recinfo.conversion_rate = X_Inverse_Conversion_Rate) OR
430 ( (Inverse_Recinfo.conversion_rate IS NULL)
431 AND (X_Inverse_Conversion_Rate IS NULL)))
432 AND ((Inverse_Recinfo.status_code = X_Status_Code) OR
433 ( (Inverse_Recinfo.status_code IS NULL)
434 AND (X_Status_Code IS NULL)))
435 AND ((Inverse_Recinfo.context = X_Context) OR
436 ( (Inverse_Recinfo.context IS NULL)
437 AND (X_Context IS NULL)))
438 AND ((Inverse_Recinfo.attribute1 = X_Attribute1) OR
439 ( (Inverse_Recinfo.attribute1 IS NULL)
440 AND (X_Attribute1 IS NULL)))
441 AND ((Inverse_Recinfo.attribute2 = X_Attribute2) OR
442 ( (Inverse_Recinfo.attribute2 IS NULL)
443 AND (X_Attribute2 IS NULL)))
444 AND ((Inverse_Recinfo.attribute3 = X_Attribute3) OR
445 ( (Inverse_Recinfo.attribute3 IS NULL)
446 AND (X_Attribute3 IS NULL)))
447 AND ((Inverse_Recinfo.attribute4 = X_Attribute4) OR
448 ( (Inverse_Recinfo.attribute4 IS NULL)
449 AND (X_Attribute4 IS NULL)))
450 AND ((Inverse_Recinfo.attribute5 = X_Attribute5) OR
451 ( (Inverse_Recinfo.attribute5 IS NULL)
452 AND (X_Attribute5 IS NULL)))
453 AND ((Inverse_Recinfo.attribute6 = X_Attribute6) OR
454 ( (Inverse_Recinfo.attribute6 IS NULL)
455 AND (X_Attribute6 IS NULL)))
456 AND ((Inverse_Recinfo.attribute7 = X_Attribute7) OR
457 ( (Inverse_Recinfo.attribute7 IS NULL)
458 AND (X_Attribute7 IS NULL)))
459 AND ((Inverse_Recinfo.attribute8 = X_Attribute8) OR
460 ( (Inverse_Recinfo.attribute8 IS NULL)
461 AND (X_Attribute8 IS NULL)))
462 AND ((Inverse_Recinfo.attribute9 = X_Attribute9) OR
463 ( (Inverse_Recinfo.attribute9 IS NULL)
464 AND (X_Attribute9 IS NULL)))
465 AND ((Inverse_Recinfo.attribute10 = X_Attribute10) OR
466 ( (Inverse_Recinfo.attribute10 IS NULL)
467 AND (X_Attribute10 IS NULL)))
468 AND ((Inverse_Recinfo.attribute11 = X_Attribute11) OR
469 ( (Inverse_Recinfo.attribute11 IS NULL)
470 AND (X_Attribute11 IS NULL)))
471 AND ((Inverse_Recinfo.attribute12 = X_Attribute12) OR
472 ( (Inverse_Recinfo.attribute12 IS NULL)
473 AND (X_Attribute12 IS NULL)))
474 AND ((Inverse_Recinfo.attribute13 = X_Attribute13) OR
475 ( (Inverse_Recinfo.attribute13 IS NULL)
476 AND (X_Attribute13 IS NULL)))
477 AND ((Inverse_Recinfo.attribute14 = X_Attribute14) OR
478 ( (Inverse_Recinfo.attribute14 IS NULL)
479 AND (X_Attribute14 IS NULL)))
480 AND ((Inverse_Recinfo.attribute15 = X_Attribute15) OR
481 ( (Inverse_Recinfo.attribute15 IS NULL)
482 AND (X_Attribute15 IS NULL)))
483 ) then
484 return;
485 else
486 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
487 APP_EXCEPTION.RAISE_EXCEPTION;
488 end if;
489
490 END Lock_Row;
491
492
493 --
494 -- Procedure
495 --
496 -- Update_Row
497 --
498 -- Purpose
499 -- Updates a pair of rows in gl_daily_rates.
500 --
501 -- History
502 -- 07-29-97 W Wong Created
503 --
504 -- Arguments
505 -- All the columns of the table GL_DAILY_RATES and
506 -- X_Average_Balances_Used Average Balances Used
507 -- X_Euro_Currency Currency Code of EURO
508 --
509 -- Example
510 -- gl_daily_rates.Update_Row(....);
511 --
512 -- Notes
513 --
514 PROCEDURE Update_Row(X_Rowid VARCHAR2,
515 X_Inverse_Rowid VARCHAR2,
516 X_From_Currency VARCHAR2,
517 X_To_Currency VARCHAR2,
518 X_Conversion_Date DATE,
519 X_Conversion_Type VARCHAR2,
520 X_Conversion_Rate NUMBER,
521 X_Inverse_Conversion_Rate NUMBER,
522 X_Status_Code IN OUT NOCOPY VARCHAR2,
523 X_Creation_Date DATE,
524 X_Created_By NUMBER,
525 X_Last_Update_Date DATE,
526 X_Last_Updated_By NUMBER,
527 X_Last_Update_Login NUMBER,
528 X_Context VARCHAR2,
529 X_Attribute1 VARCHAR2,
530 X_Attribute2 VARCHAR2,
531 X_Attribute3 VARCHAR2,
532 X_Attribute4 VARCHAR2,
533 X_Attribute5 VARCHAR2,
534 X_Attribute6 VARCHAR2,
535 X_Attribute7 VARCHAR2,
536 X_Attribute8 VARCHAR2,
537 X_Attribute9 VARCHAR2,
538 X_Attribute10 VARCHAR2,
539 X_Attribute11 VARCHAR2,
540 X_Attribute12 VARCHAR2,
541 X_Attribute13 VARCHAR2,
542 X_Attribute14 VARCHAR2,
543 X_Attribute15 VARCHAR2,
544 X_Average_Balances_Used VARCHAR2,
545 X_Euro_Currency VARCHAR2
546 ) IS
547 ekey VARCHAR2(100);
548 BEGIN
549 -- Set the status code to 'O' if the X_To_Currency and X_Conversion_Type is
550 -- used in ledgers.
551 IF ( X_Status_Code <> 'O' ) THEN
552 IF ( GL_DAILY_RATES_PKG.Used_In_Ledger( X_From_Currency,
553 X_To_Currency,
554 X_Conversion_Type,
555 X_Euro_Currency,
556 X_Conversion_Date )) THEN
557 X_Status_Code := 'O';
558
559 ELSE
560 X_Status_Code := 'C';
561 END IF;
562
563 ELSE
564 X_Status_Code := 'O';
565 END IF;
566
567 -- Update conversion information for the row with conversion rate
568 UPDATE GL_DAILY_RATES
569 SET
570 from_currency = X_From_Currency,
571 to_currency = X_To_Currency,
572 conversion_date = X_Conversion_Date,
573 conversion_type = X_Conversion_Type,
574 conversion_rate = X_Conversion_Rate,
575 status_code = X_Status_Code,
576 last_update_date = X_Last_Update_Date,
577 last_updated_by = X_Last_Updated_By,
578 last_update_login = X_Last_Update_Login,
579 context = X_Context,
580 attribute1 = X_Attribute1,
581 attribute2 = X_Attribute2,
582 attribute3 = X_Attribute3,
583 attribute4 = X_Attribute4,
584 attribute5 = X_Attribute5,
585 attribute6 = X_Attribute6,
586 attribute7 = X_Attribute7,
587 attribute8 = X_Attribute8,
588 attribute9 = X_Attribute9,
589 attribute10 = X_Attribute10,
590 attribute11 = X_Attribute11,
591 attribute12 = X_Attribute12,
592 attribute13 = X_Attribute13,
593 attribute14 = X_Attribute14,
594 attribute15 = X_Attribute15
595 WHERE rowid = X_Rowid;
596
597 if (SQL%NOTFOUND) then
598 RAISE NO_DATA_FOUND;
599 end if;
600
601 -- Update conversion information for the row with inverse conversion rate
602 UPDATE GL_DAILY_RATES
603 SET
604 from_currency = X_To_Currency,
605 to_currency = X_From_Currency,
606 conversion_date = X_Conversion_Date,
607 conversion_type = X_Conversion_Type,
608 conversion_rate = X_Inverse_Conversion_Rate,
609 status_code = X_Status_Code,
610 last_update_date = X_Last_Update_Date,
611 last_updated_by = X_Last_Updated_By,
612 last_update_login = X_Last_Update_Login,
613 context = X_Context,
614 attribute1 = X_Attribute1,
615 attribute2 = X_Attribute2,
616 attribute3 = X_Attribute3,
617 attribute4 = X_Attribute4,
618 attribute5 = X_Attribute5,
619 attribute6 = X_Attribute6,
620 attribute7 = X_Attribute7,
621 attribute8 = X_Attribute8,
622 attribute9 = X_Attribute9,
623 attribute10 = X_Attribute10,
624 attribute11 = X_Attribute11,
625 attribute12 = X_Attribute12,
626 attribute13 = X_Attribute13,
627 attribute14 = X_Attribute14,
628 attribute15 = X_Attribute15
629 WHERE rowid = X_Inverse_Rowid;
630
631 if (SQL%NOTFOUND) then
632 RAISE NO_DATA_FOUND;
633 end if;
634
635
636 ekey := X_From_Currency||':'||X_To_Currency||':'||X_Conversion_Type||':'
637 ||to_char(X_Conversion_Date,'RRDDDSSSSS')||':'
638 ||to_char(X_Conversion_date,'RRDDDSSSSS')||':'
639 ||to_char(sysdate, 'RRDDDSSSSS');
640
641 -- Raise the specify conversion event
642 gl_business_events.raise(
643 p_event_name => 'oracle.apps.gl.CurrencyConversionRates.dailyRate.specify',
644 p_event_key => ekey,
645 p_parameter_name1 => 'FROM_CURRENCY',
646 p_parameter_value1 => X_From_Currency,
647 p_parameter_name2 => 'TO_CURRENCY',
648 p_parameter_value2 => X_To_Currency,
649 p_parameter_name3 => 'FROM_CONVERSION_DATE',
650 p_parameter_value3 => to_char(X_Conversion_Date,'YYYY/MM/DD'),
651 p_parameter_name4 => 'FROM_CONVERSION_DATE',
652 p_parameter_value4 => to_char(X_Conversion_Date,'YYYY/MM/DD'),
653 p_parameter_name5 => 'CONVERSION_TYPE',
654 p_parameter_value5 => X_Conversion_Type,
655 p_parameter_name6 => 'CONVERSION_RATE',
656 p_parameter_value6 => to_char(X_Conversion_Rate,
657 '99999999999999999999.99999999999999999999'),
658 p_parameter_name7 => 'INVERSE_CONVERSION_RATE',
659 p_parameter_value7 => to_char(X_Inverse_Conversion_Rate,
660 '99999999999999999999.99999999999999999999')
661 );
662
663 END Update_Row;
664
665
666 --
667 -- Procedure
668 --
669 -- Delete_Row
670 --
671 -- Purpose
672 -- Deletes a row from gl_daily_rates
673 --
674 -- History
675 -- 07-29-97 W Wong Created
676 --
677 -- Arguments
678 -- X_Rowid Rowid of the row with conversion rate
679 -- X_Inverse_Rowid Rowid of the row with inverse conversion rate
680 -- X_To_Currency To Currency
681 -- X_Conversion_Type Conversion Type
682 -- X_Status_Code Status Code
683 -- X_Average_Balances_Used Average Balance Used Flag
684 -- X_Euro_Currency Currency code of the Euro currency
685 --
686 -- Example
687 -- gl_daily_rates.delete_row('...');
688 --
689 -- Notes
690 --
691 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
692 X_Inverse_Rowid VARCHAR2,
693 X_From_Currency VARCHAR2,
694 X_To_Currency VARCHAR2,
695 X_Conversion_Type VARCHAR2,
696 X_Conversion_Date DATE,
697 X_Status_Code IN OUT NOCOPY VARCHAR2,
698 X_Average_Balances_Used VARCHAR2,
699 X_Euro_Currency VARCHAR2
700 ) IS
701 ekey VARCHAR2(100);
702 BEGIN
703 -- Set the status code to 'D' if the X_To_Currency and X_Conversion_Type is
704 -- used in ledgers.
705 IF ( X_Status_Code <> 'O' ) THEN
706 IF ( GL_DAILY_RATES_PKG.Used_In_Ledger( X_From_Currency,
707 X_To_Currency,
708 X_Conversion_Type,
709 X_Euro_Currency,
710 X_Conversion_Date )) THEN
711 X_Status_Code := 'D';
712
713 ELSE
714 X_Status_Code := 'C';
715 END IF;
716 ELSE
717 X_Status_Code := 'D';
718 END IF;
719
720 -- Delete or update GL_DAILY_RATES table according to the status code
721 IF (X_Status_Code <> 'D') THEN
722 -- Delete the original row and its corresponding row with the
723 -- inverse conversion rate
724 DELETE FROM GL_DAILY_RATES
725 WHERE rowid IN (X_Rowid, X_Inverse_Rowid);
726
727 ELSE
728 -- Update the original row and its corresponding row with the
729 -- inverse conversion rate
730 UPDATE GL_DAILY_RATES
731 SET
732 status_code = 'D'
733 WHERE rowid IN (X_Rowid, X_Inverse_Rowid);
734 END IF;
735
736 ekey := X_From_Currency||':'||X_To_Currency||':'||X_Conversion_Type||':'
737 ||to_char(X_Conversion_Date,'RRDDDSSSSS')||':'
738 ||to_char(X_Conversion_date,'RRDDDSSSSS')||':'
739 ||to_char(sysdate, 'RRDDDSSSSS');
740
741 -- Raise the specify conversion event
742 gl_business_events.raise(
743 p_event_name => 'oracle.apps.gl.CurrencyConversionRates.dailyRate.remove',
744 p_event_key => ekey,
745 p_parameter_name1 => 'FROM_CURRENCY',
746 p_parameter_value1 => X_From_Currency,
747 p_parameter_name2 => 'TO_CURRENCY',
748 p_parameter_value2 => X_To_Currency,
749 p_parameter_name3 => 'FROM_CONVERSION_DATE',
750 p_parameter_value3 => to_char(X_Conversion_Date,'YYYY/MM/DD'),
751 p_parameter_name4 => 'FROM_CONVERSION_DATE',
752 p_parameter_value4 => to_char(X_Conversion_Date,'YYYY/MM/DD'),
753 p_parameter_name5 => 'CONVERSION_TYPE',
754 p_parameter_value5 => X_Conversion_Type
755 );
756
757 EXCEPTION
758 WHEN NO_DATA_FOUND THEN
759 null;
760 -- The following exception will catch the condition when a row
761 -- which was deleted earlier is reinserted and deleted again. Since
762 -- two rows with the same primary keys exist in GL_DAILY_RATES
763 -- we need to delete this row. The Rate Change program will
764 -- outdate the corresponding rows in GL_DAILY_BALANCES since it will
765 -- process the existing duplicate row with the same accounting date and
766 -- the currency code.
767 WHEN DUP_VAL_ON_INDEX THEN
768 DELETE FROM GL_DAILY_RATES
769 WHERE rowid = X_Rowid;
770 DELETE FROM GL_DAILY_RATES
771 WHERE rowid = X_Inverse_Rowid;
772 WHEN OTHERS THEN
773 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
774 fnd_message.set_token('PROCEDURE', 'gl_daily_rates.delete_row');
775 RAISE;
776
777 END Delete_Row;
778
779
780 --
781 -- Procedure
782 -- Check_Unique
783 --
784 -- Purpose
785 -- Checks to make sure that gl_daily_rates is unique.
786 --
787 -- History
788 -- 07-29-97 W Wong Created
789 --
790 -- Arguments
791 -- X_Rowid The row ID
792 -- X_From_Currency From Currency
793 -- X_To_Currency To Currency
794 -- X_Conversion_Date Conversion Date
795 -- X_Conversion_Type Conversion Type
796 --
797 -- Example
798 -- gl_daily_rates.check_unique(...);
799 --
800 -- Notes
801 --
802 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
803 X_From_Currency VARCHAR2,
804 X_To_Currency VARCHAR2,
805 X_Conversion_Date DATE,
806 X_Conversion_Type VARCHAR2) IS
807 dummy NUMBER;
808 BEGIN
809 IF ( X_From_Currency IS NOT NULL
810 AND X_To_Currency IS NOT NULL
811 AND X_Conversion_Date IS NOT NULL
812 AND X_Conversion_Type IS NOT NULL ) THEN
813
814 SELECT 1
815 INTO dummy
816 FROM dual D
817 WHERE NOT EXISTS ( SELECT 1
818 FROM GL_DAILY_RATES R
819 WHERE R.from_currency = X_From_Currency
820 AND R.to_currency = X_To_Currency
821 AND R.conversion_date = X_Conversion_Date
822 AND R.conversion_type = X_Conversion_Type
823 AND ( R.rowid <> X_Rowid OR X_Rowid IS NULL ));
824 END IF;
825
826 EXCEPTION
827 WHEN NO_DATA_FOUND THEN
828 FND_MESSAGE.SET_NAME('SQLGL', 'GL_EXCHG_RATE_ALREADY_EXISTS');
829 APP_EXCEPTION.RAISE_EXCEPTION;
830
831 end Check_Unique;
832
833
834 --
835 -- Function
836 -- Used_In_Ledger
837 --
838 -- Purpose
839 -- Checks if the functional currency and the specified conversion type is
840 -- being used for translation by any ledgers. If it is, check the
841 -- following:
842 --
843 -- a. If functional currency is an EMU currency,
844 -- and the X_Conversion_Date is ON OR AFTER the EMU date,
845 -- and if daily rates which involves the EURO currency has been changed,
846 -- return 'Y'.
847 --
848 -- b. If functional currency is an EMU currency,
849 -- but the X_Conversion_Date is BEFORE the EMU date,
850 -- and if daily rates which involves the functional currency has been
851 -- changed, return 'Y'.
852 --
853 -- c. If functional currency is not an EMU currency,
854 -- and if daily rates which involves the functional currency has been
855 -- which is not an EMU currency, return 'Y'.
856 --
857 -- d. Return 'N' otherwise.
858 --
859 -- History
860 -- 08-06-97 W Wong Created
861 --
862 -- Arguments
863 -- X_To_Currency To Currency
864 -- X_Conversion_Type Conversion Type
865 --
866 -- Example
867 -- gl_daily_rates.used_in_ledger(...)
868 --
869 -- Notes
870 --
871 FUNCTION Used_In_Ledger( X_From_Currency VARCHAR2,
872 X_To_Currency VARCHAR2,
873 X_Conversion_Type VARCHAR2,
874 X_Euro_Currency VARCHAR2,
875 X_Conversion_Date DATE)
876 RETURN BOOLEAN IS
877 is_used VARCHAR2(1) := 'N';
878
879 BEGIN
880 SELECT nvl(max('Y'),'N')
881 INTO is_used
882 FROM dual
883 WHERE EXISTS (
884 SELECT 'found'
885 FROM GL_LEDGERS LGR, GL_LEDGER_RELATIONSHIPS REL
886 WHERE LGR.currency_code IN (X_From_Currency, X_To_Currency)
887 AND REL.source_ledger_id = LGR.ledger_id+0
888 AND REL.target_ledger_id = LGR.ledger_id+0
889 AND REL.application_id = 101
890 AND REL.target_ledger_category_code = 'ALC'
891 AND REL.relationship_type_code = 'BALANCE'
892 AND REL.target_currency_code IN (X_From_Currency, X_To_Currency)
893 AND ( LGR.daily_translation_rate_type = X_Conversion_Type
894 OR nvl(REL.alc_period_average_rate_type,
895 LGR.period_average_rate_type) = X_Conversion_Type
896 OR nvl(REL.alc_period_end_rate_type,
897 LGR.period_end_rate_type) = X_Conversion_Type));
898
899 IF ( is_used = 'Y' ) THEN
900 return( TRUE );
901
902 ELSE
903 return( FALSE );
904 END IF;
905
906 END Used_In_Ledger;
907
908 --
909 -- Procedure
910 -- Insert_DateRange
911 --
912 -- Purpose
913 -- This procedure is created for Ispeed Daily Rates API.
914 -- It inserts rows into gl_daily_rates_interface.
915 --
916 -- History
917 -- 09-06-00 K Chang Created
918 --
919 -- Arguments
920 -- All the columns of the table GL_DAILY_RATES_INTERFACE
921 --
922 -- Example
923 -- gl_daily_rates_pkg.Insert_DateRage(....);
924 --
925 -- Notes
926 --
927 PROCEDURE Insert_DateRange(X_From_Currency VARCHAR2,
928 X_To_Currency VARCHAR2,
929 X_From_Conversion_Date DATE,
930 X_To_Conversion_Date DATE,
931 X_User_Conversion_Type VARCHAR2,
932 X_Conversion_Rate NUMBER,
933 X_Mode_Flag VARCHAR2,
934 X_Inverse_Conversion_Rate NUMBER,
935 X_User_Id NUMBER,
936 X_Launch_Rate_Change VARCHAR2,
937 X_Error_Code VARCHAR2,
938 X_Context VARCHAR2,
939 X_Attribute1 VARCHAR2,
940 X_Attribute2 VARCHAR2,
941 X_Attribute3 VARCHAR2,
942 X_Attribute4 VARCHAR2,
943 X_Attribute5 VARCHAR2,
944 X_Attribute6 VARCHAR2,
945 X_Attribute7 VARCHAR2,
946 X_Attribute8 VARCHAR2,
947 X_Attribute9 VARCHAR2,
948 X_Attribute10 VARCHAR2,
949 X_Attribute11 VARCHAR2,
950 X_Attribute12 VARCHAR2,
951 X_Attribute13 VARCHAR2,
952 X_Attribute14 VARCHAR2,
953 X_Attribute15 VARCHAR2,
954 X_Used_For_AB_Translation VARCHAR2
955 ) IS
956 BEGIN
957 -- Insert the row with conversion rate
958 INSERT INTO GL_DAILY_RATES_INTERFACE(
959 from_currency,
960 to_currency,
961 from_conversion_date,
962 to_conversion_date,
963 user_conversion_type,
964 conversion_rate,
965 mode_flag,
966 inverse_conversion_rate,
967 user_id,
968 launch_rate_change,
969 error_code,
970 context,
971 attribute1,
972 attribute2,
973 attribute3,
974 attribute4,
975 attribute5,
976 attribute6,
977 attribute7,
978 attribute8,
979 attribute9,
980 attribute10,
981 attribute11,
982 attribute12,
983 attribute13,
984 attribute14,
985 attribute15,
986 used_for_ab_translation
987 ) VALUES (
988 X_From_Currency,
989 X_To_Currency,
990 X_From_Conversion_Date,
991 X_To_Conversion_Date,
992 X_User_Conversion_Type,
993 X_Conversion_Rate,
994 X_Mode_Flag,
995 X_Inverse_Conversion_Rate,
996 X_User_Id,
997 X_Launch_Rate_Change,
998 X_Error_Code,
999 X_Context,
1000 X_Attribute1,
1001 X_Attribute2,
1002 X_Attribute3,
1003 X_Attribute4,
1004 X_Attribute5,
1005 X_Attribute6,
1006 X_Attribute7,
1007 X_Attribute8,
1008 X_Attribute9,
1009 X_Attribute10,
1010 X_Attribute11,
1011 X_Attribute12,
1012 X_Attribute13,
1013 X_Attribute14,
1014 X_Attribute15,
1015 X_Used_For_AB_Translation
1016 );
1017
1018 END Insert_DateRange;
1019
1020
1021 --
1022 -- Procedure
1023 -- Validate_DailyRates
1024 --
1025 -- Purpose
1026 -- This procedure is created for Ispeed Daily Rates API.
1027 -- It validate the following:
1028 -- o From_Currency and To_Currency are not the same
1029 -- o From_Currency and To_Currency:
1030 -- a. Currency exists in the FND_CURRENCIES table
1031 -- b. Currency is enabled
1032 -- c. Currency is not out of date
1033 -- d. Currency is not an EMU currency
1034 -- o Range of dates specified does not exceed 366 days
1035 --
1036 -- History
1037 -- 09-06-00 K Chang Created
1038 --
1039 -- Arguments
1040 -- X_From_Currency From Currency
1041 -- X_To_Currency To Currency
1042 -- X_Conversion_Date Conversion Date
1043 -- X_Converson_Type Conversion Type
1044 -- X_From_Conversion_Date From Conversion Date
1045 -- X_To_Conversion_Date To Conversion Date
1046 --
1047 -- Example
1048 -- gl_daily_rates_pkg.Validate_DailyRates(....);
1049 --
1050 -- Notes
1051 --
1052 PROCEDURE Validate_DailyRates(X_From_Currency VARCHAR2,
1053 X_To_Currency VARCHAR2,
1054 X_Conversion_Date DATE,
1055 X_Conversion_Type VARCHAR2,
1056 X_From_Conversion_Date DATE,
1057 X_To_Conversion_Date DATE
1058
1059 ) IS
1060 CURSOR check_from_currency IS
1061 SELECT 'X'
1062 FROM FND_CURRENCIES
1063 WHERE currency_code = X_FROM_CURRENCY
1064 AND currency_flag = 'Y'
1065 AND enabled_flag ='Y'
1066 AND sign(trunc(sysdate) -
1067 nvl(trunc(start_date_active),trunc(sysdate))) <> -1
1068 AND sign(trunc(sysdate)
1069 - nvl(trunc(end_date_active),trunc(sysdate))) <> 1
1070 AND decode(derive_type, 'EMU', sign( trunc(derive_effective) - trunc(X_CONVERSION_DATE)),1) > 0;
1071
1072 CURSOR check_to_currency IS
1073 SELECT 'X'
1074 FROM FND_CURRENCIES
1075 WHERE currency_code = X_TO_CURRENCY
1076 AND currency_flag = 'Y'
1077 AND enabled_flag ='Y'
1078 AND sign(trunc(sysdate) -
1079 nvl(trunc(start_date_active),trunc(sysdate))) <> -1
1080 AND sign(trunc(sysdate)
1081 - nvl(trunc(end_date_active),trunc(sysdate))) <> 1
1082 AND decode(derive_type,'EMU', sign( trunc(derive_effective) - trunc(X_CONVERSION_DATE) ), 1) > 0 ;
1083
1084 dummy VARCHAR2(10);
1085 numDays number := 0;
1086 BEGIN
1087
1088 -- Check if from and to currencies are the same
1089 IF (X_FROM_CURRENCY = X_TO_CURRENCY) THEN
1090 fnd_message.set_name('SQLGL', 'GL_GLXRTDLY_SAMECURR');
1091 app_exception.raise_exception;
1092 END IF;
1093
1094 -- Check from currency
1095 OPEN check_from_currency;
1096 FETCH check_from_currency INTO dummy;
1097 IF check_from_currency%NOTFOUND THEN
1098 CLOSE check_from_currency;
1099 fnd_message.set_name('SQLGL', 'GL_API_DRATE_INVALID_FCURR');
1100 app_exception.raise_exception;
1101 END IF;
1102 CLOSE check_from_currency;
1103
1104 -- Check to currency
1105 OPEN check_to_currency;
1106 FETCH check_to_currency INTO dummy;
1107 IF check_to_currency%NOTFOUND THEN
1108 CLOSE check_to_currency;
1109 fnd_message.set_name('SQLGL', 'GL_API_DRATE_INVALID_TCURR');
1110 app_exception.raise_exception;
1111 END IF;
1112 CLOSE check_to_currency;
1113
1114 -- Check conversion date ranges
1115 IF (X_From_Conversion_Date IS NOT NULL AND
1116 X_To_Conversion_Date IS NOT NULL) THEN
1117
1118 SELECT least(trunc(X_To_Conversion_DATE) -
1119 trunc(X_From_Conversion_Date), 367)
1120 into numDays
1121 FROM dual;
1122
1123 IF (numDays = 367) THEN
1124 fnd_message.set_name('SQLGL', 'GL_GLXRTDLY_LARGERANGE');
1125 app_exception.raise_exception;
1126 END IF;
1127
1128 END IF;
1129
1130 EXCEPTION
1131 WHEN app_exception.application_exception THEN
1132 RAISE;
1133 WHEN OTHERS THEN
1134 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1135 fnd_message.set_token('PROCEDURE', 'gl_daily_rates.validate_dailyrates');
1136 RAISE;
1137
1138 END Validate_DailyRates;
1139
1140
1141 --
1142 -- Function
1143 -- Submit_Conc_Request
1144 --
1145 -- Purpose
1146 -- Launch Conversion Rate Change concurrent program for
1147 -- Ispeed Daily Rates API
1148 --
1149 -- History
1150 -- 09-08-00 K Chang Created
1151 --
1152 -- Arguments
1153 --
1154 --
1155 -- Example
1156 -- gl_daily_rates_pkg.Submit_Conc_Request(....);
1157 --
1158 -- Notes
1159 --
1160 FUNCTION submit_conc_request RETURN NUMBER
1161 IS
1162 result NUMBER :=-1;
1163 BEGIN
1164
1165 --FND_PROFILE.put('USER_ID', '2090' );
1166 --FND_PROFILE.put('RESP_ID', '50553');
1167 --FND_PROFILE.put('RESP_APPL_ID','101');
1168
1169 -- Submit the request to run Rate Change concurrent program
1170 result := FND_REQUEST.submit_request (
1171 'SQLGL','GLTTRC','','',FALSE,
1172 'D','',chr(0),
1173 '','','','','','','',
1174 '','','','','','','','','','',
1175 '','','','','','','','','','',
1176 '','','','','','','','','','',
1177 '','','','','','','','','','',
1178 '','','','','','','','','','',
1179 '','','','','','','','','','',
1180 '','','','','','','','','','',
1181 '','','','','','','','','','',
1182 '','','','','','','','','','');
1183
1184 return(result);
1185
1186 END submit_conc_request;
1187
1188 --
1189 -- Procedure
1190 -- Upload_Row
1191 --
1192 -- Purpose
1193 -- Inserts or Updates two rows into gl_daily_rates for Ispeed Daily
1194 -- Rates API:
1195 -- one for the original conversion rate ( From Currency -> To Currency )
1196 -- one for the inverse conversion rate ( To Currency -> From Currency )
1197 --
1198 -- History
1199 -- 09-21-00 K Chang Created
1200 --
1201 -- Arguments
1202 -- All the columns of the table GL_DAILY_RATES and
1203 -- X_Average_Balances_Used Average Balances Used
1204 -- X_Euro_Currency Currency Code of EURO
1205 --
1206 -- Example
1207 -- gl_daily_rates.Upload_Row(....);
1208 --
1209 -- Notes
1210 --
1211 PROCEDURE Upload_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
1212 X_Inverse_Rowid IN OUT NOCOPY VARCHAR2,
1213 X_From_Currency VARCHAR2,
1214 X_To_Currency VARCHAR2,
1215 X_Conversion_Date DATE,
1216 X_Conversion_Type VARCHAR2,
1217 X_Conversion_Rate NUMBER,
1218 X_Inverse_Conversion_Rate NUMBER,
1219 X_Status_Code IN OUT NOCOPY VARCHAR2,
1220 X_Creation_Date DATE,
1221 X_Created_By NUMBER,
1222 X_Last_Update_Date DATE,
1223 X_Last_Updated_By NUMBER,
1224 X_Last_Update_Login NUMBER,
1225 X_Context VARCHAR2,
1226 X_Attribute1 VARCHAR2,
1227 X_Attribute2 VARCHAR2,
1228 X_Attribute3 VARCHAR2,
1229 X_Attribute4 VARCHAR2,
1230 X_Attribute5 VARCHAR2,
1231 X_Attribute6 VARCHAR2,
1232 X_Attribute7 VARCHAR2,
1233 X_Attribute8 VARCHAR2,
1234 X_Attribute9 VARCHAR2,
1235 X_Attribute10 VARCHAR2,
1236 X_Attribute11 VARCHAR2,
1237 X_Attribute12 VARCHAR2,
1238 X_Attribute13 VARCHAR2,
1239 X_Attribute14 VARCHAR2,
1240 X_Attribute15 VARCHAR2,
1241 X_Average_Balances_Used VARCHAR2,
1242 X_Euro_Currency VARCHAR2
1243 ) IS
1244 lrowid rowid := null;
1245 invrowid rowid := null;
1246 BEGIN
1247
1248 -- Get the row id
1249 select rowid
1250 into lrowid
1251 from gl_daily_rates
1252 where from_currency = X_From_Currency
1253 and to_currency = X_To_Currency
1254 and conversion_date = X_Conversion_Date
1255 and conversion_type = X_Conversion_Type;
1256
1257 -- Get the row id for the inverse conversion rate
1258 select rowid
1259 into invrowid
1260 from gl_daily_rates
1261 where from_currency = X_To_Currency
1262 and to_currency = X_From_Currency
1263 and conversion_date = X_Conversion_Date
1264 and conversion_type = X_Conversion_Type;
1265
1266 -- Update only if the record exists in the database
1267 --IF ( lrowid IS NOT NULL and invrowid IS NOT NULL) THEN
1268 GL_DAILY_RATES_PKG.update_row(
1269 X_Rowid =>lrowid,
1270 X_Inverse_Rowid =>invrowid,
1271 X_From_Currency =>X_From_Currency,
1272 X_To_Currency =>X_To_Currency,
1273 X_Conversion_Date =>X_Conversion_Date,
1274 X_Conversion_Type =>X_Conversion_Type,
1275 X_Conversion_Rate =>X_Conversion_Rate,
1276 X_Inverse_Conversion_Rate =>X_Inverse_Conversion_Rate,
1277 X_Status_Code =>X_Status_Code,
1278 X_Creation_Date =>X_Creation_Date,
1279 X_Created_By =>X_Created_By,
1280 X_Last_Update_Date =>X_Last_Update_Date,
1281 X_Last_Updated_By =>X_Last_Updated_By,
1282 X_Last_Update_Login =>X_Last_Update_Login,
1283 X_Context =>X_Context,
1284 X_Attribute1 =>X_Attribute1,
1285 X_Attribute2 =>X_Attribute2,
1286 X_Attribute3 =>X_Attribute3,
1287 X_Attribute4 =>X_Attribute4,
1288 X_Attribute5 =>X_Attribute5,
1289 X_Attribute6 =>X_Attribute6,
1290 X_Attribute7 =>X_Attribute7,
1291 X_Attribute8 =>X_Attribute8,
1292 X_Attribute9 =>X_Attribute9,
1293 X_Attribute10 =>X_Attribute10,
1294 X_Attribute11 =>X_Attribute11,
1295 X_Attribute12 =>X_Attribute12,
1296 X_Attribute13 =>X_Attribute13,
1297 X_Attribute14 =>X_Attribute14,
1298 X_Attribute15 =>X_Attribute15,
1299 X_Average_Balances_Used =>X_Average_Balances_Used,
1300 X_Euro_Currency =>X_Euro_Currency
1301 );
1302 --END IF;
1303
1304 EXCEPTION WHEN NO_DATA_FOUND THEN
1305 GL_DAILY_RATES_PKG.insert_row(
1306 X_Rowid =>lrowid,
1307 X_Inverse_Rowid =>invrowid,
1308 X_From_Currency =>X_From_Currency,
1309 X_To_Currency =>X_To_Currency,
1310 X_Conversion_Date =>X_Conversion_Date,
1311 X_Conversion_Type =>X_Conversion_Type,
1312 X_Conversion_Rate =>X_Conversion_Rate,
1313 X_Inverse_Conversion_Rate =>X_Inverse_Conversion_Rate,
1314 X_Status_Code =>X_Status_Code,
1315 X_Creation_Date =>X_Creation_Date,
1316 X_Created_By =>X_Created_By,
1317 X_Last_Update_Date =>X_Last_Update_Date,
1318 X_Last_Updated_By =>X_Last_Updated_By,
1319 X_Last_Update_Login =>X_Last_Update_Login,
1320 X_Context =>X_Context,
1321 X_Attribute1 =>X_Attribute1,
1322 X_Attribute2 =>X_Attribute2,
1323 X_Attribute3 =>X_Attribute3,
1324 X_Attribute4 =>X_Attribute4,
1325 X_Attribute5 =>X_Attribute5,
1326 X_Attribute6 =>X_Attribute6,
1327 X_Attribute7 =>X_Attribute7,
1328 X_Attribute8 =>X_Attribute8,
1329 X_Attribute9 =>X_Attribute9,
1330 X_Attribute10 =>X_Attribute10,
1331 X_Attribute11 =>X_Attribute11,
1332 X_Attribute12 =>X_Attribute12,
1333 X_Attribute13 =>X_Attribute13,
1334 X_Attribute14 =>X_Attribute14,
1335 X_Attribute15 =>X_Attribute15,
1336 X_Average_Balances_Used =>X_Average_Balances_Used,
1337 X_Euro_Currency =>X_Euro_Currency
1338 );
1339
1340
1341 END Upload_Row;
1342
1343 END GL_DAILY_RATES_PKG;