View: CST_GL_TRANSLATION_VIEW

Product:	BOM - Bills of Material
Description:	Currency exchange rate for reports : SINGLE-ORG view
Implementation/DBA Data:	APPS.CST_GL_TRANSLATION_VIEW

View Text

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

Columns