Oracle GL Daily Conversion Rates Deep Drive

We will discuss the process of Oracle GL Daily Conversion Rates with few example and technical code.

Client Industry - Applicable to clients implementing Oracle General Ledger

Business Case - Almost all clients who implement Oracle General Ledger make use of the currency exchange rate interface, since it provides a convenient and automated way for multicurrency processing within Oracle Apps. Many clients make use of this interface to report financials in a common currency as well as to perform inter-company transactions between companies that have 2 different functional currencies.

Solution – A simple custom interface program can be written which will import rates automatically from a vendor and will also load these rates into the core GL daily rates table using the delivered daily rates interface tables. Find below a high-level process flowchart which can be used as a starting point to design this interface:

1. Request file is sent to Vendor (Reuters, Bloomberg, Oanda).
2. The encrypted file “XXGLDAILYRATES_XXXX.txt” from the vendor is obtained where XXXX is “mmdd”.
3. If the file is available, continue step 4 thro 11. Else, go to step 12.
4. Invoke the Shell script to decrypt the file and load the data from the file into the daily rates interface table .
5. Call the concurrent program “Program - Daily Rates Import and Calculation”
6. Load the daily rates from interface table into Oracle base tables till the end of next fiscal month.
7. Calculate the Period average rates.
8. Load the period rates into the interface table (gl_daily_rates_interface) and Call the concurrent program “Program - Daily Rates Import and Calculation” to load the period rates from interface table into Oracle base tables.
9. Calculate the Period average and end rates for NON - US currencies.
10. Load the period average and end rates into Oracle base tables (gl_translation_rates)
11. Archive the decrypted data file as “XXGLDAILYRATES_XXXX.txt” in ARCHIVE/inbound folder where XXXX is “mmdd”.
12. Page the Finance on-call support.

Example with code:

Base tables for GL Daily Rates are—Gl_DAILY_RATES

Interface table for GL Daily Rates are—Gl_DAILY_RATES_INTERFACE

Moving the Data from Flat File to Base Table using SQL * LOADER:

Options (Skip =0)

Load data

infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'

Insert into table GL_daily_rates_stg

fields terminated by ','

optionally enclosed by '"'

Trailing nullcols

(From_currency,

 To_currency, 

 From_conversion_date, 

 To_conversion_date,

 User_conversion_type, 

 conversion_rate, 

 Mode_flag)

Moving the data from Staging tables to Base Tables using Standard Interface Programs:

Create a Staging table based on the requirement

CREATE TABLE XXGL_DRATES_STG (

FROM_CURRENCY VARCHAR2(15),

TO_CURRENCY VARCHAR2(15),

FROM_CONVERSION_DATE DATE,

TO_CONVERSION_DATE DATE,

USER_CONVERSION_TYPE VARCHAR2(30),

CONVERSION_RATE NUMBER,

MODE_FLAG CHAR(1));

Inserting Data into Staging Table:

Insert into XXGL_DRATES_STG Values (

'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');

Create a Package with validations to move the data into Interface Tables

CREATE OR REPLACE PACKAGE xxgl_drates_pkg
IS
   PROCEDURE daily_rates_prc (retcode OUT NUMBER, errbuff OUT VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY xxgl_drates_pkg
IS
   PROCEDURE daily_rates_prc (retcode OUT NUMBER, errbuff OUT VARCHAR2)
   IS
      CURSOR cur_drates
      IS
         SELECT from_currency, to_currency, from_conversion_date,
                to_conversion_date, user_conversion_type, conversion_rate,
                mode_flag
           FROM xxgl_drates_stg;

      lv_from_currency          VARCHAR2 (15);
      lv_to_currency            VARCHAR2 (15);
      lv_user_conversion_type   VARCHAR2 (30);
      lv_conversion_rate        NUMBER;
      lv_err_flag               VARCHAR2 (1)  := 'A';
   BEGIN
      FOR i IN cur_drates
      LOOP
         BEGIN
            SELECT currency_code
              INTO lv_from_currency
              FROM fnd_currencies
             WHERE currency_code = i.from_currency;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_from_currency := NULL;
               lv_err_flag := 'E';
               fnd_file.put_line
                  (fnd_file.LOG,
                   'The Currency Code is not defined
/not enabled if not enabled enable it.'
                  );
         END;

         fnd_file.put_line (fnd_file.LOG,
                               'The Currency Code inserting IS--'
                            || lv_from_currency
                           );

         BEGIN
            SELECT currency_code
              INTO lv_to_currency
              FROM fnd_currencies
             WHERE enabled_flag = 'Y' AND currency_code = i.to_currency;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_from_currency := NULL;
               lv_err_flag := 'E';
               fnd_file.put_line
                  (fnd_file.LOG,
                   'The Currency Code is not defined
/not enabled if not enabled enable it.'
                  );
         END;

         fnd_file.put_line (fnd_file.LOG,
                               'The Currency Code inserting IS--'
                            || lv_to_currency
                           );

         BEGIN
            SELECT user_conversion_type
              INTO lv_user_conversion_type
              FROM gl_daily_conversion_types
             WHERE user_conversion_type = i.user_conversion_type;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_user_conversion_type := NULL;
               lv_err_flag := 'E';
               fnd_file.put_line (fnd_file.LOG,
                                  'The USER_CONVERSION_TYPE is not defined.'
                                 );
         END;

         fnd_file.put_line (fnd_file.LOG,
                               'The USER_CONVERSION_TYPE inserting IS--'
                            || lv_user_conversion_type
                           );

         BEGIN
            SELECT user_conversion_type
              INTO lv_user_conversion_type
              FROM gl_daily_conversion_types
             WHERE user_conversion_type = i.user_conversion_type;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lv_user_conversion_type := NULL;
               lv_err_flag := 'E';
               fnd_file.put_line (fnd_file.LOG,
                                  'The USER_CONVERSION_TYPE is not defined.'
                                 );
         END;

         fnd_file.put_line (fnd_file.LOG,
                               'The USER_CONVERSION_TYPE inserting IS--'
                            || lv_user_conversion_type
                           );

         IF lv_err_flag = 'A'
         THEN
            INSERT INTO gl_daily_rates_interface
                        (from_currency, to_currency,
                         from_conversion_date, to_conversion_date,
                         user_conversion_type, conversion_rate,
                         mode_flag
                        )
                 VALUES (lv_from_currency, lv_to_currency,
                         i.from_conversion_date, i.to_conversion_date,
                         lv_user_conversion_type, i.conversion_rate,
                         i.mode_flag
                        );
         END IF;
      END LOOP;

      COMMIT;
   END;
END xxgl_drates_pkg;

Create an Executable – XXGL_DRATES_PKG_EXEC

Execution File

Create a Concurrent program – XXGL_DRATES_PKG_EXEC IFace Conc prg

Add the Conc program to the Request group

In custom module, Run the Conc Program thro’ SRS Window.

In GL MODULE Run the Standard Concurrent Program –Program - Daily Rates Import and Calculation