VIEW: APPS.CST_GL_TRANSLATION_VIEW

Source

View Text - Preformatted

SELECT ood.organization_id, gtr.period_name, gp.period_year, gp.period_num, gtr.to_currency_code, ml.lookup_code, fl.lookup_code, rtrim(to_char( 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) ),'.'), round(decode(ml.lookup_code, 1, avg_rate, 2, eop_rate),20) from gl_translation_rates gtr, gl_periods gp, gl_sets_of_books sob, org_organization_definitions ood, mfg_lookups ml, fnd_lookups fl WHERE ood.set_of_books_id = gtr.set_of_books_id and sob.set_of_books_id = ood.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 ood.organization_id, NULL, 0, 0, sob.currency_code, ml.lookup_code, fl.lookup_code, '1', 1 from GL_SETS_OF_BOOKS SOB, ORG_ORGANIZATION_DEFINITIONS OOD, MFG_LOOKUPS ML, FND_LOOKUPS FL where ood.set_of_books_id = sob.set_of_books_id and ml.lookup_type = 'CST_GL_TRANS_RATE_TYPE' and fl.lookup_type = 'YES_NO' UNION SELECT ood.organization_id org_id , NULL period_name , 9999 period_year , 9999 period_num , fc.currency_code code , ml.lookup_code rate_type , fl.lookup_code inverse , RTRIM (TO_CHAR(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) ),'.') ,round( gl_currency_api.get_rate_sql(sob.currency_code, fc.currency_code, SYSDATE),20) FROM gl_sets_of_books sob , org_organization_definitions ood , mfg_lookups ml , fnd_lookups fl , fnd_currencies fc WHERE sob.set_of_books_id = ood.set_of_books_id AND 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 OOD.ORGANIZATION_ID
, GTR.PERIOD_NAME
, GP.PERIOD_YEAR
, GP.PERIOD_NUM
, GTR.TO_CURRENCY_CODE
, ML.LOOKUP_CODE
, FL.LOOKUP_CODE
, RTRIM(TO_CHAR( 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) )
, '.')
, ROUND(DECODE(ML.LOOKUP_CODE
, 1
, AVG_RATE
, 2
, EOP_RATE)
, 20)
FROM GL_TRANSLATION_RATES GTR
, GL_PERIODS GP
, GL_SETS_OF_BOOKS SOB
, ORG_ORGANIZATION_DEFINITIONS OOD
, MFG_LOOKUPS ML
, FND_LOOKUPS FL
WHERE OOD.SET_OF_BOOKS_ID = GTR.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = OOD.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 OOD.ORGANIZATION_ID
, NULL
, 0
, 0
, SOB.CURRENCY_CODE
, ML.LOOKUP_CODE
, FL.LOOKUP_CODE
, '1'
, 1
FROM GL_SETS_OF_BOOKS SOB
, ORG_ORGANIZATION_DEFINITIONS OOD
, MFG_LOOKUPS ML
, FND_LOOKUPS FL
WHERE OOD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND ML.LOOKUP_TYPE = 'CST_GL_TRANS_RATE_TYPE'
AND FL.LOOKUP_TYPE = 'YES_NO' UNION SELECT OOD.ORGANIZATION_ID ORG_ID
, NULL PERIOD_NAME
, 9999 PERIOD_YEAR
, 9999 PERIOD_NUM
, FC.CURRENCY_CODE CODE
, ML.LOOKUP_CODE RATE_TYPE
, FL.LOOKUP_CODE INVERSE
, RTRIM (TO_CHAR(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) )
, '.')
, ROUND( GL_CURRENCY_API.GET_RATE_SQL(SOB.CURRENCY_CODE
, FC.CURRENCY_CODE
, SYSDATE)
, 20)
FROM GL_SETS_OF_BOOKS SOB
, ORG_ORGANIZATION_DEFINITIONS OOD
, MFG_LOOKUPS ML
, FND_LOOKUPS FL
, FND_CURRENCIES FC
WHERE SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND 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