[Home] [Help]
View: CST_PAC_GL_TRANSLATION_VIEW
View Text
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
Columns
Name |
SET_OF_BOOKS_ID |
PERIOD |
PERIOD_YEAR |
PERIOD_NUM |
CURRENCY_CODE |
RATE_TYPE |
INVERSE |
DISPLAY_RATE |
ACTUAL_RATE |