1 Reply Latest reply: Oct 9, 2012 6:17 AM by Andy Coates RSS

    Modify SIL_GLBalanceFact to populate Balance_Global1_Amt column.

      Hello Everyone,

      Any help on the following scenario Will be much appreciated!

      -->We have data coming in from 2 Peoplesoft instances(one with USD currency values and other with CAD currency values)
      -->Currently we are extracting these values and loading them as local amounts in the Balance_loc_Amt column in W_GL_BALANCE_F table in the datawarehouse without any conversions.
      -->Now we have a flat file which has the Currency conversion rate to SEK(Swedish Currency)

      1st task:
      -->Populate the SEK currency rate to W_EXCH_RATE_G table manually.

      2nd task:
      -->Modify the Informatica mapping such that the records coming from USD and CAD are converted to SEK using the flatfile as source and W_EXCH_RATE_G table and get populated in Balance_Global1_Amt column in W_GL_BALANCE_F table.

      Thanks for the help in advance!

        • 1. Re: Modify SIL_GLBalanceFact to populate Balance_Global1_Amt column.
          Andy Coates
          Hi Nikki,

          The BI Applications have currency conversion features built in; I would explore them in the first instance.

          Up to three global currencies are supported; these are setup using DAC parameters:-



          The ETL populates these for each datasource into the Warehouse table W_GLOBAL_CURR_G (using mapping SIL_GlobalCurrencyGeneral_Update).

          The rate type parameters tell the ETL where to source exchange rates from in your source system. If you want to source rates from a different data source (e.g. a flatfile) then you will have to customise mappings to load W_EXCH_RATE_G accordingly (see the mappings SIL_ExchangeRateGeneral and the corresponding SDE mapping(s) for your data source e.g. PeopleSoft).

          If you look at the SIL mapping for the W_GL_BALANCE_F table (SIL_GLBalanceFact) you will see a Mapplet called MPLT_CURCY_CONVERSION_RATES1 which gets the three global exchange rates for a given DATASOURCE_NUM_ID, TENANT_ID, EXCH_DT and DOC_CURR_CODE. This mapplet references W_GLOBAL_CURR_G to get the rate types. A latter Expression transformation called Exp_W_GL_BALANCE_F_Update_Flag then multiplies the retrieved rates by the local amounts to get the global amounts.

          So, the custom mapping that loads the exchange rate table should load rates (between local and global currencies) with all the necessary dates for conversion, the correct datasource_num_id and tenant_id. The rate type should then be defined in the DAC parameter(s).

          Please see the following section of the configuration guide:-


          Please mark if helpful / answered,