Daily Exchange Currency Rates Interface

Description:

URL will be passed as a 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 will 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 a parameter in the package. The URL will return an 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 be 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 system +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 system +1

and To_conversion_date is system +3

7. If the program has an 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 the business team to send the details of load for each day.

 

 

Summary:

This Post explained what the steps are should follow to create daily exchange currency rats interface in oracle ERP

Queries?

Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.