Following are 3 key programs that can be used to upload GL Daily Conversion Rates.
- A) XXCB FX Rates Data Load Program
- B) XXCB FX Rates Validation Procedure Program
- 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.
- Delete unwanted currency rates from staging table
- 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.
- Populate To_Conversion date.
- Insert Friday’s rate for next Saturday and Sunday rate.
- Insert eligible records in “gl_daily_rates_interface” table Check if FROM_CURRENCY and TO_CURRENCY are valid.
- Check if USER_CONVERSION_TYPE is valid.
- 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.
- 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.
- 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
- 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
- a) capital iq
- b) Reuters
- c) Bloomberg
- d) Oanda
Thanks Yogesh