I would like to know how we could insert additional exchange rate record into W_EXCH_RATE_G table in the datawarehouse.
I verified that the records populated in that table ate from PS_RT_RATE_TBL from peoplesoft.
But I just have a flat file and I will have to populate the additional record.
Can you let me know if I have to use and update query to do the same or should I modify the Informatica mapping?
so you have a manual record you want to enter from a flat file?
if so, you can just edit the MAPPLET to include the FLAT FILE as a source...and just UNION the PSoft and the values from the flat file. That is probably the easiest way. You can also have a separate mapping/task if you want that loads the same W_EXCH_RATE_GS staging table.
If you do not have a data source then the BI Apps will not be able to load the data from anywhere...
As suggested, you could use a flatfile which you populate with the manual exchange rates, or enter them directly into PeopleSoft. An alternative would be to populate the exchange rates into a database table and source them from there, perhaps building an APEX front end for this. Either way, you will need to physically populate a data source with the exchange rates.
If the exchange rates can be inferred/generated (e.g. missing rates within a date range should be extrapolated from existing data points) then you can calculate these during the ETL.
Thanks for the Reply Andy. So you mean to say I will have to edit the mapping in such a way that I add a flatfile source in the mapping and join in such a way that the exchange rate is taken from the source file to convert the local amounts coming from the instances and populate in the global column in w_gl_balance_f table?