VIEW: APPS.CST_PAC_GL_TRANSLATION_VIEW

Source

View Text - Preformatted

SELECT gtr.set_of_books_id, gtr.period_name period, gp.period_year, gp.period_num, gtr.to_currency_code currency_code, ml.lookup_code rate_type, fl.lookup_code inverse, TRUNC((round(decode(ml.lookup_code, 1, decode(fl.lookup_code, 'N', avg_rate, 'Y', 1 / avg_rate), 2, decode(fl.lookup_code, 'N', eop_rate, 'Y', 1 / eop_rate)), 15))) display_rate, round(decode(ml.lookup_code, 1, avg_rate, 2, eop_rate),20) actual_rate from gl_translation_rates gtr, gl_periods gp, gl_sets_of_books sob, mfg_lookups ml, fnd_lookups fl WHERE gtr.set_of_books_id = sob.set_of_books_id and gp.period_set_name = sob.period_set_name and gtr.actual_flag = 'A' and ml.lookup_type = 'CST_GL_TRANS_RATE_TYPE' and fl.lookup_type = 'YES_NO' and gtr.period_name = gp.period_name union select sob.set_of_books_id, NULL period, 0 period_year, 0 period_num, sob.currency_code currency_code, ml.lookup_code rate_type, fl.lookup_code inverse, 1 display_rate, 1 actual_rate from GL_SETS_OF_BOOKS SOB, MFG_LOOKUPS ML, FND_LOOKUPS FL where ml.lookup_type = 'CST_GL_TRANS_RATE_TYPE' and fl.lookup_type = 'YES_NO' UNION SELECT sob.set_of_books_id , NULL period_name , 9999 period_year , 9999 period_num , fc.currency_code currency_code , ml.lookup_code rate_type , fl.lookup_code inverse , TRUNC((ROUND(DECODE(fl.lookup_code, 'N', gl_currency_api.get_rate_sql(sob.currency_code, fc.currency_code, SYSDATE), 'Y', 1 / gl_currency_api.get_rate_sql(sob.currency_code, fc.currency_code, SYSDATE)), 15))) display_rate ,round( gl_currency_api.get_rate_sql(sob.currency_code, fc.currency_code, SYSDATE),20) inverse_rate FROM gl_sets_of_books sob , mfg_lookups ml , fnd_lookups fl , fnd_currencies fc WHERE ml.lookup_type = 'CST_GL_TRANS_RATE_TYPE' AND fl.lookup_type = 'YES_NO' AND fc.derive_type IS NOT NULL AND EXISTS (SELECT NULL FROM fnd_currencies WHERE currency_code = sob.currency_code AND derive_type IS NOT NULL) AND fc.currency_code <> sob.currency_code

View Text - HTML Formatted

SELECT GTR.SET_OF_BOOKS_ID
, GTR.PERIOD_NAME PERIOD
, GP.PERIOD_YEAR
, GP.PERIOD_NUM
, GTR.TO_CURRENCY_CODE CURRENCY_CODE
, ML.LOOKUP_CODE RATE_TYPE
, FL.LOOKUP_CODE INVERSE
, TRUNC((ROUND(DECODE(ML.LOOKUP_CODE
, 1
, DECODE(FL.LOOKUP_CODE
, 'N'
, AVG_RATE
, 'Y'
, 1 / AVG_RATE)
, 2
, DECODE(FL.LOOKUP_CODE
, 'N'
, EOP_RATE
, 'Y'
, 1 / EOP_RATE))
, 15))) DISPLAY_RATE
, ROUND(DECODE(ML.LOOKUP_CODE
, 1
, AVG_RATE
, 2
, EOP_RATE)
, 20) ACTUAL_RATE
FROM GL_TRANSLATION_RATES GTR
, GL_PERIODS GP
, GL_SETS_OF_BOOKS SOB
, MFG_LOOKUPS ML
, FND_LOOKUPS FL
WHERE GTR.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GP.PERIOD_SET_NAME = SOB.PERIOD_SET_NAME
AND GTR.ACTUAL_FLAG = 'A'
AND ML.LOOKUP_TYPE = 'CST_GL_TRANS_RATE_TYPE'
AND FL.LOOKUP_TYPE = 'YES_NO'
AND GTR.PERIOD_NAME = GP.PERIOD_NAME UNION SELECT SOB.SET_OF_BOOKS_ID
, NULL PERIOD
, 0 PERIOD_YEAR
, 0 PERIOD_NUM
, SOB.CURRENCY_CODE CURRENCY_CODE
, ML.LOOKUP_CODE RATE_TYPE
, FL.LOOKUP_CODE INVERSE
, 1 DISPLAY_RATE
, 1 ACTUAL_RATE
FROM GL_SETS_OF_BOOKS SOB
, MFG_LOOKUPS ML
, FND_LOOKUPS FL
WHERE ML.LOOKUP_TYPE = 'CST_GL_TRANS_RATE_TYPE'
AND FL.LOOKUP_TYPE = 'YES_NO' UNION SELECT SOB.SET_OF_BOOKS_ID
, NULL PERIOD_NAME
, 9999 PERIOD_YEAR
, 9999 PERIOD_NUM
, FC.CURRENCY_CODE CURRENCY_CODE
, ML.LOOKUP_CODE RATE_TYPE
, FL.LOOKUP_CODE INVERSE
, TRUNC((ROUND(DECODE(FL.LOOKUP_CODE
, 'N'
, GL_CURRENCY_API.GET_RATE_SQL(SOB.CURRENCY_CODE
, FC.CURRENCY_CODE
, SYSDATE)
, 'Y'
, 1 / GL_CURRENCY_API.GET_RATE_SQL(SOB.CURRENCY_CODE
, FC.CURRENCY_CODE
, SYSDATE))
, 15))) DISPLAY_RATE
, ROUND( GL_CURRENCY_API.GET_RATE_SQL(SOB.CURRENCY_CODE
, FC.CURRENCY_CODE
, SYSDATE)
, 20) INVERSE_RATE
FROM GL_SETS_OF_BOOKS SOB
, MFG_LOOKUPS ML
, FND_LOOKUPS FL
, FND_CURRENCIES FC
WHERE ML.LOOKUP_TYPE = 'CST_GL_TRANS_RATE_TYPE'
AND FL.LOOKUP_TYPE = 'YES_NO'
AND FC.DERIVE_TYPE IS NOT NULL
AND EXISTS (SELECT NULL
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = SOB.CURRENCY_CODE
AND DERIVE_TYPE IS NOT NULL)
AND FC.CURRENCY_CODE <> SOB.CURRENCY_CODE