GL Daily FX Rate Upload

Following are 3 key programs that can be used to upload GL Daily Conversion Rates.

1. A) XXCB FX Rates Data Load Program
2. B) XXCB FX Rates Validation Procedure Program
3. C) Program – Daily Rates Import and Calculation

1) XXCB FX Rates Data Load Program

— SQL Loader Program to Upload to Custom Staging Table

2) XXCB FX Rates Validation Procedure Program

— PLSQL custom validation program to add logic to upcoming rate file.

1. Delete unwanted currency rates from staging table
2. Delete some of the currency rates for example GBP to USD as if we load USD to GBP it will take care reverse rate automatically.
3. Populate To_Conversion date.
4. Insert Friday’s rate for next Saturday and Sunday rate.
5. Insert eligible records in “gl_daily_rates_interface” table Check if FROM_CURRENCY and TO_CURRENCY are valid.
6. Check if USER_CONVERSION_TYPE is valid.
7. 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.
8. 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.
9. 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
10. 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

3) Program – Daily Rates Import and Calculation

— Load FX rate from gl_daily_rates_interface table to base tables Gl_DAILY_RATES

Following are some of the FX daily rates file providers

1. a) capital iq
2. b) Reuters
3. c) Bloomberg
4. d) Oanda

Thanks Yogesh