View: FII_BIS_GLEPS_V

View Text

SELECT S.SET_OF_BOOKS_ID
, S.PERIOD_YEAR
, S.QUARTER_NUM
, NVL(DECODE(SUM(DECODE(S.FIN_ITEM_ID
, 'SOBASIC'
, S.QUARTER_TO_DATE
, 0))
, 0
, 0
, SUM(DECODE(S.FIN_ITEM_ID
, 'SOBASIC'
, 0
, 'SODILUT'
, 0
, 'PREFDIV'
, S.QUARTER_TO_DATE
, -1*S.QUARTER_TO_DATE)) / SUM(DECODE(S.FIN_ITEM_ID
, 'SOBASIC'
, S.QUARTER_TO_DATE
, 0)))
, 0) BASIC
, NVL(DECODE(SUM(DECODE(S.FIN_ITEM_ID
, 'SODILUT'
, S.QUARTER_TO_DATE
, 0))
, 0
, 0
, SUM(DECODE(S.FIN_ITEM_ID
, 'SOBASIC'
, 0
, 'SODILUT'
, 0
, 'PREFDIV'
, S.QUARTER_TO_DATE
, -1*S.QUARTER_TO_DATE)) / SUM(DECODE(S.FIN_ITEM_ID
, 'SODILUT'
, S.QUARTER_TO_DATE
, 0)))
, 0) DILUTED
, SUM(DECODE(S.FIN_ITEM_ID
, 'SOBASIC'
, S.QUARTER_TO_DATE
, 0)) BASIC_SHARES
, SUM(DECODE(S.FIN_ITEM_ID
, 'SODILUT'
, S.QUARTER_TO_DATE
, 0)) DILUTED_SHARES
FROM (SELECT SOB.SET_OF_BOOKS_ID
, FIN_ITEM_ID
, NVL(MAX(PS.PERIOD_YEAR * NUMBER_PER_FISCAL_YEAR + PS.PERIOD_NUM)
, 0) RELATIVE_PERIOD
FROM (SELECT SET_OF_BOOKS_ID
, FIN_ITEM_ID
, MAX(SD2.RELATIVE_PERIOD_POS) REL_POS
FROM GL_OASIS_SUMMARY_DATA SD2
WHERE SD2.DRILLDOWN_SEGNUM1 = -1
AND SD2.DRILLDOWN_SEGNUM2 = -1
AND SD2.ACTUAL_FLAG = 'A'
AND SD2.DRILLDOWN_SEGVAL1 = '-1'
AND SD2.DRILLDOWN_SEGVAL2 = '-1' GROUP BY SET_OF_BOOKS_ID
, FIN_ITEM_ID) REL
, GL_PERIOD_STATUSES PS
, GL_SETS_OF_BOOKS SOB
, GL_PERIOD_TYPES PT
, GL_LOOKUPS L
WHERE PS.APPLICATION_ID = 101
AND PS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND PT.PERIOD_TYPE = SOB.ACCOUNTED_PERIOD_TYPE
AND PS.PERIOD_YEAR * NUMBER_PER_FISCAL_YEAR + PS.PERIOD_NUM <= REL.REL_POS
AND PS.QUARTER_NUM = DECODE(L.LOOKUP_CODE
, 'Q1'
, 1
, 'Q2'
, 2
, 'Q3'
, 3
, 'Q4'
, 4)
AND L.LOOKUP_TYPE = 'OASIS_QUARTERS'
AND PS.SET_OF_BOOKS_ID = REL.SET_OF_BOOKS_ID GROUP BY SOB.SET_OF_BOOKS_ID
, FIN_ITEM_ID) GER
, GL_OASIS_SUMMARY_DATA S
WHERE S.SET_OF_BOOKS_ID = GER.SET_OF_BOOKS_ID
AND ( ( S.FIN_ITEM_ID = 'REVENUE'
AND S.FIN_ITEM_ID = GER.FIN_ITEM_ID
AND S.DRILLDOWN_SEGNUM1 = -1
AND S.DRILLDOWN_SEGNUM2 = -1
AND S.ACTUAL_FLAG = 'A'
AND S.DRILLDOWN_SEGVAL1 = '-1'
AND S.DRILLDOWN_SEGVAL2 = '-1'
AND S.RELATIVE_PERIOD_POS = GER.RELATIVE_PERIOD) OR ( S.FIN_ITEM_ID = 'EXPENSE'
AND S.FIN_ITEM_ID = GER.FIN_ITEM_ID
AND S.DRILLDOWN_SEGNUM1 = -1
AND S.DRILLDOWN_SEGNUM2 = -1
AND S.ACTUAL_FLAG = 'A'
AND S.DRILLDOWN_SEGVAL1 = '-1'
AND S.DRILLDOWN_SEGVAL2 = '-1'
AND S.RELATIVE_PERIOD_POS = GER.RELATIVE_PERIOD) OR ( S.FIN_ITEM_ID = 'PREFDIV'
AND S.FIN_ITEM_ID = GER.FIN_ITEM_ID
AND S.DRILLDOWN_SEGNUM1 = -1
AND S.DRILLDOWN_SEGNUM2 = -1
AND S.ACTUAL_FLAG = 'A'
AND S.DRILLDOWN_SEGVAL1 = '-1'
AND S.DRILLDOWN_SEGVAL2 = '-1'
AND S.RELATIVE_PERIOD_POS = GER.RELATIVE_PERIOD) OR ( S.FIN_ITEM_ID = 'SOBASIC'
AND S.FIN_ITEM_ID = GER.FIN_ITEM_ID
AND S.DRILLDOWN_SEGNUM1 = -1
AND S.DRILLDOWN_SEGNUM2 = -1
AND S.ACTUAL_FLAG = 'A'
AND S.DRILLDOWN_SEGVAL1 = '-1'
AND S.DRILLDOWN_SEGVAL2 = '-1'
AND S.RELATIVE_PERIOD_POS = GER.RELATIVE_PERIOD) OR ( S.FIN_ITEM_ID = 'SODILUT'
AND S.FIN_ITEM_ID = GER.FIN_ITEM_ID
AND S.DRILLDOWN_SEGNUM1 = -1
AND S.DRILLDOWN_SEGNUM2 = -1
AND S.ACTUAL_FLAG = 'A'
AND S.DRILLDOWN_SEGVAL1 = '-1'
AND S.DRILLDOWN_SEGVAL2 = '-1'
AND S.RELATIVE_PERIOD_POS = GER.RELATIVE_PERIOD) ) GROUP BY S.SET_OF_BOOKS_ID
, S.PERIOD_YEAR
, S.QUARTER_NUM ORDER BY QUARTER_NUM

Columns