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.
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.
ReplyDeleteHi,
ReplyDeleteDo 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.
ReplyDeleteGood to read article about open catalog interface Punchout.
OCI Punchout
Thanks for sharing article about Benefits of OCI Punchout, Open Catalog Interface Punchout
ReplyDeleteBenefits of OCI Punchout
This is a good post. This post gives truly quality information. usd to aud I’m definitely going to look into it. Really very useful tips are provided here. Thank you so much. Keep up the good works.
ReplyDeleteExcellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion HCM Training