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.
  • December 22, 2014 | 25 views