Monday, 22 December 2014

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.

7 comments:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete
  2. When we insert into GL_DAILY_RATES_INTERFACE on Sat or Sun, then the rates do not get moved to GL_DAILY_RATES. They sit in interface table. On Monday when Monday's rates are loaded in interface table, then the rates for Sat, Sun & Mon goes into the base table. need to find out why.

    ReplyDelete
  3. Hi,
    Do you have a sample script of the code along with the insert table details for GL_Daily_Rates Interface.
    I have a below URL which I have to pass
    https://openexchangerates.org/api/latest.json?app_id=1234
    Thanks,
    Rajesh.

    ReplyDelete
  4. Nice blog about punchout XML, it's being great to read this.
    CXML Punchout

    ReplyDelete

  5. Good to read article about open catalog interface Punchout.
    OCI Punchout




    ReplyDelete
  6. Thanks for sharing article about Benefits of OCI Punchout, Open Catalog Interface Punchout
    Benefits of OCI Punchout

    ReplyDelete
  7. eToro is the #1 forex broker for new and established traders.

    ReplyDelete