Daily Exchange Currency Rates Interface

Solution : URL will be passed as parameter where we can identify the currency rate conversion amount. FROM_CURRENCY , TO_CURRENCY and the date for when it has to be run will be passed as parameter By default system date will be taken. The URL Data wil be stored in the staging table and then based on the validations it will be moved to GL_DAILY_RATES_INTERFACE_TABLE. Then the standard import program will be run to load to base tables

Step 1 : Pass the url as parameter in the package. The url will return a XML File content.

Step 2 : Download the XML Content and insert to table XML_DATA

      

     CREATE TABLE XML_DATA

(

  RESULT  SYS.XMLTYPE,

  ID      NUMBER,

  URL     VARCHAR2(1000 BYTE)

);

Step 3 : Create another table to store the values of xml

      create table temp_xml(file_content XMLType)

Step 4 : Data will inserted to staging table created for gl_conv_stg

       CREATE TABLE GL_CONV_STG

(

  CURRENCIES            VARCHAR2(15 BYTE),

  CONVERSION_RATE       NUMBER,

  FROM_CONVERSION_DATE  DATE,

  FROM_CURRENCY         VARCHAR2(15 BYTE),

  TO_CURRENCY           VARCHAR2(15 BYTE),

  TO_CONVERSION_DATE    DATE,

  MODE_FLAG             VARCHAR2(1 BYTE),

  CORPORATE_TYPE_FLAG   VARCHAR2(1 BYTE),

  SPOT_TYPE_FLAG        VARCHAR2(1 BYTE),

  ERROR_FLAG            VARCHAR2(1 BYTE),

  ERROR_MSG             VARCHAR2(1000 BYTE),

  USER_CONVERSION_TYPE  VARCHAR2(30 BYTE)

);

Step 5 : From the staging table data will be validated and inserted to interface table GL_DAILY_RATES_INTERFACE

Step 6 : Below are the validations done before inserting to interface table

1.         Check if FROM_CURRENCY and TO_CURRENCY are valid.

2.         Check if USER_CONVERSION_TYPE is valid.

3.         If Conversion Rate is null then data from the base table GL_DAILY_RATES will be considered to insert into GL_DAILY_RATES_INTERFACE table.

4.         If Conversion Rate is not null then data from the staging table GL_CONV_STG will be considered to insert into GL_DAILY_RATES_INTERFACE table.

5.         If the Day of the run is between Monday - Thursday, then below are the changes in the GL_DAILY_RATES_INTERFACE table.

    From_conversion_date and To_conversion_date is sysdate +1

6.         If the Day of the run is Friday, then below are the changes in the GL_DAILY_RATES_INTERFACE table.

    From_conversion_date  is sysdate +1

           and To_conversion_date is sysdate +3

7. If the program has error, then details of the run with the error message should go the requestor in the email process.

Step 7 : Calling the standard program Standard program Daily Rates Import and Calculation.

Step 8 : Email will be sent to business team to send the details of load for each day.