[Home] [Help]
PACKAGE BODY: APPS.GL_DAILY_RATES_PKG
Source
1 PACKAGE BODY GL_DAILY_RATES_PKG as
2 /* $Header: glirtdyb.pls 120.10.12020000.2 2012/08/28 21:01:06 phmullap 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 status varchar(1):=null;
809 BEGIN
810 IF ( X_From_Currency IS NOT NULL
811 AND X_To_Currency IS NOT NULL
812 AND X_Conversion_Date IS NOT NULL
813 AND X_Conversion_Type IS NOT NULL ) THEN
814
815 SELECT 1
816 INTO dummy
817 FROM dual D
818 WHERE NOT EXISTS ( SELECT 1
819 FROM GL_DAILY_RATES R
820 WHERE R.from_currency = X_From_Currency
821 AND R.to_currency = X_To_Currency
822 AND R.conversion_date = X_Conversion_Date
823 AND R.conversion_type = X_Conversion_Type
824 AND ( R.rowid <> X_Rowid OR X_Rowid IS NULL ));
825 END IF;
826
827 EXCEPTION
828 WHEN NO_DATA_FOUND THEN
829 SELECT R.status_code into status
830 FROM GL_DAILY_RATES R
831 WHERE R.from_currency = X_From_Currency
832 AND R.to_currency = X_To_Currency
833 AND R.conversion_date = X_Conversion_Date
834 AND R.conversion_type = X_Conversion_Type
835 AND ( R.rowid <> X_Rowid OR X_Rowid IS NULL );
836 If (status<>'D')
837 then
838 FND_MESSAGE.SET_NAME('SQLGL', 'GL_EXCHG_RATE_ALREADY_EXISTS');
839 APP_EXCEPTION.RAISE_EXCEPTION;
840 else
841 fnd_message.set_name('SQLGL','GL_EXCHG_RATE_DELETED');
842 app_exception.raise_exception;
843 end if;
844
845 end Check_Unique;
846
847
848 --
849 -- Function
850 -- Used_In_Ledger
851 --
852 -- Purpose
853 -- Checks if the functional currency and the specified conversion type is
854 -- being used for translation by any ledgers. If it is, check the
855 -- following:
856 --
857 -- a. If functional currency is an EMU currency,
858 -- and the X_Conversion_Date is ON OR AFTER the EMU date,
859 -- and if daily rates which involves the EURO currency has been changed,
860 -- return 'Y'.
861 --
862 -- b. If functional currency is an EMU currency,
863 -- but the X_Conversion_Date is BEFORE the EMU date,
864 -- and if daily rates which involves the functional currency has been
865 -- changed, return 'Y'.
866 --
867 -- c. If functional currency is not an EMU currency,
868 -- and if daily rates which involves the functional currency has been
869 -- which is not an EMU currency, return 'Y'.
870 --
871 -- d. Return 'N' otherwise.
872 --
873 -- History
874 -- 08-06-97 W Wong Created
875 --
876 -- Arguments
877 -- X_To_Currency To Currency
878 -- X_Conversion_Type Conversion Type
879 --
880 -- Example
881 -- gl_daily_rates.used_in_ledger(...)
882 --
883 -- Notes
884 --
885 FUNCTION Used_In_Ledger( X_From_Currency VARCHAR2,
886 X_To_Currency VARCHAR2,
887 X_Conversion_Type VARCHAR2,
888 X_Euro_Currency VARCHAR2,
889 X_Conversion_Date DATE)
890 RETURN BOOLEAN IS
891 is_used VARCHAR2(1) := 'N';
892
893 BEGIN
894 SELECT nvl(max('Y'),'N')
895 INTO is_used
896 FROM dual
897 WHERE EXISTS (
898 SELECT 'found'
899 FROM GL_LEDGERS LGR, GL_LEDGER_RELATIONSHIPS REL
900 WHERE LGR.currency_code IN (X_From_Currency, X_To_Currency)
901 AND REL.source_ledger_id = LGR.ledger_id+0
902 AND REL.target_ledger_id = LGR.ledger_id+0
903 AND REL.application_id = 101
904 AND REL.target_ledger_category_code = 'ALC'
905 AND REL.relationship_type_code = 'BALANCE'
906 AND REL.target_currency_code IN (X_From_Currency, X_To_Currency)
907 AND ( LGR.daily_translation_rate_type = X_Conversion_Type
908 OR nvl(REL.alc_period_average_rate_type,
909 LGR.period_average_rate_type) = X_Conversion_Type
910 OR nvl(REL.alc_period_end_rate_type,
911 LGR.period_end_rate_type) = X_Conversion_Type));
912
913 IF ( is_used = 'Y' ) THEN
914 return( TRUE );
915
916 ELSE
917 return( FALSE );
918 END IF;
919
920 END Used_In_Ledger;
921
922 --
923 -- Procedure
924 -- Insert_DateRange
925 --
926 -- Purpose
927 -- This procedure is created for Ispeed Daily Rates API.
928 -- It inserts rows into gl_daily_rates_interface.
929 --
930 -- History
931 -- 09-06-00 K Chang Created
932 --
933 -- Arguments
934 -- All the columns of the table GL_DAILY_RATES_INTERFACE
935 --
936 -- Example
937 -- gl_daily_rates_pkg.Insert_DateRage(....);
938 --
939 -- Notes
940 --
941 PROCEDURE Insert_DateRange(X_From_Currency VARCHAR2,
942 X_To_Currency VARCHAR2,
943 X_From_Conversion_Date DATE,
944 X_To_Conversion_Date DATE,
945 X_User_Conversion_Type VARCHAR2,
946 X_Conversion_Rate NUMBER,
947 X_Mode_Flag VARCHAR2,
948 X_Inverse_Conversion_Rate NUMBER,
949 X_User_Id NUMBER,
950 X_Launch_Rate_Change VARCHAR2,
951 X_Error_Code VARCHAR2,
952 X_Context VARCHAR2,
953 X_Attribute1 VARCHAR2,
954 X_Attribute2 VARCHAR2,
955 X_Attribute3 VARCHAR2,
956 X_Attribute4 VARCHAR2,
957 X_Attribute5 VARCHAR2,
958 X_Attribute6 VARCHAR2,
959 X_Attribute7 VARCHAR2,
960 X_Attribute8 VARCHAR2,
961 X_Attribute9 VARCHAR2,
962 X_Attribute10 VARCHAR2,
963 X_Attribute11 VARCHAR2,
964 X_Attribute12 VARCHAR2,
965 X_Attribute13 VARCHAR2,
966 X_Attribute14 VARCHAR2,
967 X_Attribute15 VARCHAR2,
968 X_Used_For_AB_Translation VARCHAR2
969 ) IS
970 BEGIN
971 -- Insert the row with conversion rate
972 INSERT INTO GL_DAILY_RATES_INTERFACE(
973 from_currency,
974 to_currency,
975 from_conversion_date,
976 to_conversion_date,
977 user_conversion_type,
978 conversion_rate,
979 mode_flag,
980 inverse_conversion_rate,
981 user_id,
982 launch_rate_change,
983 error_code,
984 context,
985 attribute1,
986 attribute2,
987 attribute3,
988 attribute4,
989 attribute5,
990 attribute6,
991 attribute7,
992 attribute8,
993 attribute9,
994 attribute10,
995 attribute11,
996 attribute12,
997 attribute13,
998 attribute14,
999 attribute15,
1000 used_for_ab_translation
1001 ) VALUES (
1002 X_From_Currency,
1003 X_To_Currency,
1004 X_From_Conversion_Date,
1005 X_To_Conversion_Date,
1006 X_User_Conversion_Type,
1007 X_Conversion_Rate,
1008 X_Mode_Flag,
1009 X_Inverse_Conversion_Rate,
1010 X_User_Id,
1011 X_Launch_Rate_Change,
1012 X_Error_Code,
1013 X_Context,
1014 X_Attribute1,
1015 X_Attribute2,
1016 X_Attribute3,
1017 X_Attribute4,
1018 X_Attribute5,
1019 X_Attribute6,
1020 X_Attribute7,
1021 X_Attribute8,
1022 X_Attribute9,
1023 X_Attribute10,
1024 X_Attribute11,
1025 X_Attribute12,
1026 X_Attribute13,
1027 X_Attribute14,
1028 X_Attribute15,
1029 X_Used_For_AB_Translation
1030 );
1031
1032 END Insert_DateRange;
1033
1034
1035 --
1036 -- Procedure
1037 -- Validate_DailyRates
1038 --
1039 -- Purpose
1040 -- This procedure is created for Ispeed Daily Rates API.
1041 -- It validate the following:
1042 -- o From_Currency and To_Currency are not the same
1043 -- o From_Currency and To_Currency:
1044 -- a. Currency exists in the FND_CURRENCIES table
1045 -- b. Currency is enabled
1046 -- c. Currency is not out of date
1047 -- d. Currency is not an EMU currency
1048 -- o Range of dates specified does not exceed 366 days
1049 --
1050 -- History
1051 -- 09-06-00 K Chang Created
1052 --
1053 -- Arguments
1054 -- X_From_Currency From Currency
1055 -- X_To_Currency To Currency
1056 -- X_Conversion_Date Conversion Date
1057 -- X_Converson_Type Conversion Type
1058 -- X_From_Conversion_Date From Conversion Date
1059 -- X_To_Conversion_Date To Conversion Date
1060 --
1061 -- Example
1062 -- gl_daily_rates_pkg.Validate_DailyRates(....);
1063 --
1064 -- Notes
1065 --
1066 PROCEDURE Validate_DailyRates(X_From_Currency VARCHAR2,
1067 X_To_Currency VARCHAR2,
1068 X_Conversion_Date DATE,
1069 X_Conversion_Type VARCHAR2,
1070 X_From_Conversion_Date DATE,
1071 X_To_Conversion_Date DATE
1072
1073 ) IS
1074 CURSOR check_from_currency IS
1075 SELECT 'X'
1076 FROM FND_CURRENCIES
1077 WHERE currency_code = X_FROM_CURRENCY
1078 AND currency_flag = 'Y'
1079 AND enabled_flag ='Y'
1080 AND sign(trunc(sysdate) -
1081 nvl(trunc(start_date_active),trunc(sysdate))) <> -1
1082 AND sign(trunc(sysdate)
1083 - nvl(trunc(end_date_active),trunc(sysdate))) <> 1
1084 AND decode(derive_type, 'EMU', sign( trunc(derive_effective) - trunc(X_CONVERSION_DATE)),1) > 0;
1085
1086 CURSOR check_to_currency IS
1087 SELECT 'X'
1088 FROM FND_CURRENCIES
1089 WHERE currency_code = X_TO_CURRENCY
1090 AND currency_flag = 'Y'
1091 AND enabled_flag ='Y'
1092 AND sign(trunc(sysdate) -
1093 nvl(trunc(start_date_active),trunc(sysdate))) <> -1
1094 AND sign(trunc(sysdate)
1095 - nvl(trunc(end_date_active),trunc(sysdate))) <> 1
1096 AND decode(derive_type,'EMU', sign( trunc(derive_effective) - trunc(X_CONVERSION_DATE) ), 1) > 0 ;
1097
1098 dummy VARCHAR2(10);
1099 numDays number := 0;
1100 BEGIN
1101
1102 -- Check if from and to currencies are the same
1103 IF (X_FROM_CURRENCY = X_TO_CURRENCY) THEN
1104 fnd_message.set_name('SQLGL', 'GL_GLXRTDLY_SAMECURR');
1105 app_exception.raise_exception;
1106 END IF;
1107
1108 -- Check from currency
1109 OPEN check_from_currency;
1110 FETCH check_from_currency INTO dummy;
1111 IF check_from_currency%NOTFOUND THEN
1112 CLOSE check_from_currency;
1113 fnd_message.set_name('SQLGL', 'GL_API_DRATE_INVALID_FCURR');
1114 app_exception.raise_exception;
1115 END IF;
1116 CLOSE check_from_currency;
1117
1118 -- Check to currency
1119 OPEN check_to_currency;
1120 FETCH check_to_currency INTO dummy;
1121 IF check_to_currency%NOTFOUND THEN
1122 CLOSE check_to_currency;
1123 fnd_message.set_name('SQLGL', 'GL_API_DRATE_INVALID_TCURR');
1124 app_exception.raise_exception;
1125 END IF;
1126 CLOSE check_to_currency;
1127
1128 -- Check conversion date ranges
1129 IF (X_From_Conversion_Date IS NOT NULL AND
1130 X_To_Conversion_Date IS NOT NULL) THEN
1131
1132 SELECT least(trunc(X_To_Conversion_DATE) -
1133 trunc(X_From_Conversion_Date), 367)
1134 into numDays
1135 FROM dual;
1136
1137 IF (numDays = 367) THEN
1138 fnd_message.set_name('SQLGL', 'GL_GLXRTDLY_LARGERANGE');
1139 app_exception.raise_exception;
1140 END IF;
1141
1142 END IF;
1143
1144 EXCEPTION
1145 WHEN app_exception.application_exception THEN
1146 RAISE;
1147 WHEN OTHERS THEN
1148 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1149 fnd_message.set_token('PROCEDURE', 'gl_daily_rates.validate_dailyrates');
1150 RAISE;
1151
1152 END Validate_DailyRates;
1153
1154
1155 --
1156 -- Function
1157 -- Submit_Conc_Request
1158 --
1159 -- Purpose
1160 -- Launch Conversion Rate Change concurrent program for
1161 -- Ispeed Daily Rates API
1162 --
1163 -- History
1164 -- 09-08-00 K Chang Created
1165 --
1166 -- Arguments
1167 --
1168 --
1169 -- Example
1170 -- gl_daily_rates_pkg.Submit_Conc_Request(....);
1171 --
1172 -- Notes
1173 --
1174 FUNCTION submit_conc_request RETURN NUMBER
1175 IS
1176 result NUMBER :=-1;
1177 BEGIN
1178
1179 --FND_PROFILE.put('USER_ID', '2090' );
1180 --FND_PROFILE.put('RESP_ID', '50553');
1181 --FND_PROFILE.put('RESP_APPL_ID','101');
1182
1183 -- Submit the request to run Rate Change concurrent program
1184 result := FND_REQUEST.submit_request (
1185 'SQLGL','GLTTRC','','',FALSE,
1186 'D','',chr(0),
1187 '','','','','','','',
1188 '','','','','','','','','','',
1189 '','','','','','','','','','',
1190 '','','','','','','','','','',
1191 '','','','','','','','','','',
1192 '','','','','','','','','','',
1193 '','','','','','','','','','',
1194 '','','','','','','','','','',
1195 '','','','','','','','','','',
1196 '','','','','','','','','','');
1197
1198 return(result);
1199
1200 END submit_conc_request;
1201
1202 --
1203 -- Procedure
1204 -- Upload_Row
1205 --
1206 -- Purpose
1207 -- Inserts or Updates two rows into gl_daily_rates for Ispeed Daily
1208 -- Rates API:
1209 -- one for the original conversion rate ( From Currency -> To Currency )
1210 -- one for the inverse conversion rate ( To Currency -> From Currency )
1211 --
1212 -- History
1213 -- 09-21-00 K Chang Created
1214 --
1215 -- Arguments
1216 -- All the columns of the table GL_DAILY_RATES and
1217 -- X_Average_Balances_Used Average Balances Used
1218 -- X_Euro_Currency Currency Code of EURO
1219 --
1220 -- Example
1221 -- gl_daily_rates.Upload_Row(....);
1222 --
1223 -- Notes
1224 --
1225 PROCEDURE Upload_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
1226 X_Inverse_Rowid IN OUT NOCOPY VARCHAR2,
1227 X_From_Currency VARCHAR2,
1228 X_To_Currency VARCHAR2,
1229 X_Conversion_Date DATE,
1230 X_Conversion_Type VARCHAR2,
1231 X_Conversion_Rate NUMBER,
1232 X_Inverse_Conversion_Rate NUMBER,
1233 X_Status_Code IN OUT NOCOPY VARCHAR2,
1234 X_Creation_Date DATE,
1235 X_Created_By NUMBER,
1236 X_Last_Update_Date DATE,
1237 X_Last_Updated_By NUMBER,
1238 X_Last_Update_Login NUMBER,
1239 X_Context VARCHAR2,
1240 X_Attribute1 VARCHAR2,
1241 X_Attribute2 VARCHAR2,
1242 X_Attribute3 VARCHAR2,
1243 X_Attribute4 VARCHAR2,
1244 X_Attribute5 VARCHAR2,
1245 X_Attribute6 VARCHAR2,
1246 X_Attribute7 VARCHAR2,
1247 X_Attribute8 VARCHAR2,
1248 X_Attribute9 VARCHAR2,
1249 X_Attribute10 VARCHAR2,
1250 X_Attribute11 VARCHAR2,
1251 X_Attribute12 VARCHAR2,
1252 X_Attribute13 VARCHAR2,
1253 X_Attribute14 VARCHAR2,
1254 X_Attribute15 VARCHAR2,
1255 X_Average_Balances_Used VARCHAR2,
1256 X_Euro_Currency VARCHAR2
1257 ) IS
1258 lrowid rowid := null;
1259 invrowid rowid := null;
1260 BEGIN
1261
1262 -- Get the row id
1263 select rowid
1264 into lrowid
1265 from gl_daily_rates
1266 where from_currency = X_From_Currency
1267 and to_currency = X_To_Currency
1268 and conversion_date = X_Conversion_Date
1269 and conversion_type = X_Conversion_Type;
1270
1271 -- Get the row id for the inverse conversion rate
1272 select rowid
1273 into invrowid
1274 from gl_daily_rates
1275 where from_currency = X_To_Currency
1276 and to_currency = X_From_Currency
1277 and conversion_date = X_Conversion_Date
1278 and conversion_type = X_Conversion_Type;
1279
1280 -- Update only if the record exists in the database
1281 --IF ( lrowid IS NOT NULL and invrowid IS NOT NULL) THEN
1282 GL_DAILY_RATES_PKG.update_row(
1283 X_Rowid =>lrowid,
1284 X_Inverse_Rowid =>invrowid,
1285 X_From_Currency =>X_From_Currency,
1286 X_To_Currency =>X_To_Currency,
1287 X_Conversion_Date =>X_Conversion_Date,
1288 X_Conversion_Type =>X_Conversion_Type,
1289 X_Conversion_Rate =>X_Conversion_Rate,
1290 X_Inverse_Conversion_Rate =>X_Inverse_Conversion_Rate,
1291 X_Status_Code =>X_Status_Code,
1292 X_Creation_Date =>X_Creation_Date,
1293 X_Created_By =>X_Created_By,
1294 X_Last_Update_Date =>X_Last_Update_Date,
1295 X_Last_Updated_By =>X_Last_Updated_By,
1296 X_Last_Update_Login =>X_Last_Update_Login,
1297 X_Context =>X_Context,
1298 X_Attribute1 =>X_Attribute1,
1299 X_Attribute2 =>X_Attribute2,
1300 X_Attribute3 =>X_Attribute3,
1301 X_Attribute4 =>X_Attribute4,
1302 X_Attribute5 =>X_Attribute5,
1303 X_Attribute6 =>X_Attribute6,
1304 X_Attribute7 =>X_Attribute7,
1305 X_Attribute8 =>X_Attribute8,
1306 X_Attribute9 =>X_Attribute9,
1307 X_Attribute10 =>X_Attribute10,
1308 X_Attribute11 =>X_Attribute11,
1309 X_Attribute12 =>X_Attribute12,
1310 X_Attribute13 =>X_Attribute13,
1311 X_Attribute14 =>X_Attribute14,
1312 X_Attribute15 =>X_Attribute15,
1313 X_Average_Balances_Used =>X_Average_Balances_Used,
1314 X_Euro_Currency =>X_Euro_Currency
1315 );
1316 --END IF;
1317
1318 EXCEPTION WHEN NO_DATA_FOUND THEN
1319 GL_DAILY_RATES_PKG.insert_row(
1320 X_Rowid =>lrowid,
1321 X_Inverse_Rowid =>invrowid,
1322 X_From_Currency =>X_From_Currency,
1323 X_To_Currency =>X_To_Currency,
1324 X_Conversion_Date =>X_Conversion_Date,
1325 X_Conversion_Type =>X_Conversion_Type,
1326 X_Conversion_Rate =>X_Conversion_Rate,
1327 X_Inverse_Conversion_Rate =>X_Inverse_Conversion_Rate,
1328 X_Status_Code =>X_Status_Code,
1329 X_Creation_Date =>X_Creation_Date,
1330 X_Created_By =>X_Created_By,
1331 X_Last_Update_Date =>X_Last_Update_Date,
1332 X_Last_Updated_By =>X_Last_Updated_By,
1333 X_Last_Update_Login =>X_Last_Update_Login,
1334 X_Context =>X_Context,
1335 X_Attribute1 =>X_Attribute1,
1336 X_Attribute2 =>X_Attribute2,
1337 X_Attribute3 =>X_Attribute3,
1338 X_Attribute4 =>X_Attribute4,
1339 X_Attribute5 =>X_Attribute5,
1340 X_Attribute6 =>X_Attribute6,
1341 X_Attribute7 =>X_Attribute7,
1342 X_Attribute8 =>X_Attribute8,
1343 X_Attribute9 =>X_Attribute9,
1344 X_Attribute10 =>X_Attribute10,
1345 X_Attribute11 =>X_Attribute11,
1346 X_Attribute12 =>X_Attribute12,
1347 X_Attribute13 =>X_Attribute13,
1348 X_Attribute14 =>X_Attribute14,
1349 X_Attribute15 =>X_Attribute15,
1350 X_Average_Balances_Used =>X_Average_Balances_Used,
1351 X_Euro_Currency =>X_Euro_Currency
1352 );
1353
1354
1355 END Upload_Row;
1356
1357 END GL_DAILY_RATES_PKG;