This content has been marked as final. Show 4 replies
Hi again Nikki, I see you have two Oracle.com user accounts!
This appears to relate to several issues you are having in understanding how global currencies work with the BI Applications. For the benefit of others, related threads are:-
Informatica Logic for Balance_Global1_Amt column in W_GL_BALANCE_F table
Informatica Logic for Balance_Global1_Amt column in W_GL_BALANCE_F table.
Insert records into W_EXCH_RATE_G table
Re: Insert records into W_EXCH_RATE_G table.
Modify SIL_GLBalanceFact to populate Balance_Global1_Amt column
Modify SIL_GLBalanceFact to populate Balance_Global1_Amt column.
With regards to your latest question, I recognise that the SQL you have populated below has been taken from the MPLT_CURCY_CONVERSION_RATES1 mapplet in the SIL_GLBalanceFact mapping.
To understand the logic, you need to understand the SQL used by Informatica, as well as Informatica lookup notation. Going through your example line by line:-
-- So if the GLOBAL1_CURR_CODE is null, then return NULL else...
IIF(GLOBAL1_CURR_CODE = DOC_CURR_CODE, 1.0,
-- if the GLOBAL1_CURR_CODE is equal to the DOC_CURR_CODE then return 1.0 else...
IIF(DOC_CURR_CODE = 'STAT', 1.0,
-- if the DOC_CURR_CODE is equal to 'STAT' (for statistical journals) then return 1.0 else...
IIF(GLOBAL1_CURR_CODE = LOC_CURR_CODE, DOC_TO_LOC_EXCH_RATE_VAR,
-- if the GLOBAL1_CURR_CODE is equal to the LOC_CURR_CODE then return the column DOC_TO_LOC_EXCH_RATE_VAR else...
:LKP.LKP_W_EXCH_RATE(DOC_CURR_CODE, GLOBAL1_CURR_CODE, EXCH_DT,GLOBAL1_RATE_TYPE, DATASOURCE_NUM_ID)))))
-- perform a lookup using the LKP_W_EXCH_RATE transformation (you will see this lookup on it's own in the mapplet; the colon notation above is another way to reference a lookup transformation, instead of using connectors). The five columns in the brackets are used as inputs for the lookup, if a matching record is found in the lookup table then the EXCH_RATE column is returned (check out the LKP_W_EXCH_RATE transformation for the port marked as Output).
Please mark if helpful / correct,