View: FII_BIS_FANBV_V

View Text

SELECT NUM_YEAR
, FISCAL_YEAR
, SYSDATE REPORT_DATE
, ORG_ID
, SET_OF_BOOKS_ID
, BOOK_TYPE_CODE
, ATTRIBUTE_CATEGORY_CODE
, (COST-PY_COST) ACQ_RET_ACTIVITY
, PY_COST
, COST
, DEPRECIATION
, NET_BOOK_VALUE
FROM ( SELECT DP.FISCAL_YEAR NUM_YEAR
, TO_CHAR(DP.FISCAL_YEAR) FISCAL_YEAR
, BC.ORG_ID ORG_ID
, BC.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, BC.BOOK_TYPE_CODE BOOK_TYPE_CODE
, AD.ATTRIBUTE_CATEGORY_CODE ATTRIBUTE_CATEGORY_CODE
, SUM((SELECT NVL(SUM(BK1.COST)
, 0) ACQ_COST
FROM FA_DEPRN_PERIODS DP1
, FA_CALENDAR_TYPES CT1
, FA_ADDITIONS AD1
, FA_CATEGORY_BOOKS CB1
, FA_BOOK_CONTROLS BC1
, FA_BOOKS BK1
WHERE BC1.BOOK_CLASS = 'CORPORATE'
AND BC1.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND AD1.ATTRIBUTE_CATEGORY_CODE = AD.ATTRIBUTE_CATEGORY_CODE
AND BC1.DATE_INEFFECTIVE IS NULL
AND BK1.COST <> 0
AND BC1.BOOK_TYPE_CODE = CB1.BOOK_TYPE_CODE
AND CB1.CATEGORY_ID = AD1.ASSET_CATEGORY_ID
AND CB1.CATEGORY_ID = CB.CATEGORY_ID
AND CT1.CALENDAR_TYPE = BC1.DEPRN_CALENDAR
AND CT1.CALENDAR_TYPE = CT.CALENDAR_TYPE
AND DP1.BOOK_TYPE_CODE = BC1.BOOK_TYPE_CODE
AND DP1.FISCAL_YEAR = DP.FISCAL_YEAR - 1
AND (DP1.PERIOD_NUM = CT1.NUMBER_PER_FISCAL_YEAR OR DP1.PERIOD_COUNTER = BC1.LAST_PERIOD_COUNTER)
AND BK1.ASSET_ID = AD1.ASSET_ID
AND BK1.ASSET_ID = BK.ASSET_ID
AND BK1.BOOK_TYPE_CODE = BC1.BOOK_TYPE_CODE
AND NVL(DP1.PERIOD_CLOSE_DATE
, SYSDATE) BETWEEN BK1.DATE_EFFECTIVE
AND NVL(BK1.DATE_INEFFECTIVE
, SYSDATE))) /1000 PY_COST
, SUM(BK.COST)/1000 COST
, SUM(DS.DEPRN_RESERVE)/1000 DEPRECIATION
, SUM(BK.COST - NVL(DS.DEPRN_RESERVE
, 0))/1000 NET_BOOK_VALUE
FROM FA_DEPRN_SUMMARY DS
, FA_DEPRN_PERIODS DP
, FA_CALENDAR_TYPES CT
, FA_ADDITIONS AD
, FA_CATEGORY_BOOKS CB
, FA_BOOKS BK
, FA_BOOK_CONTROLS BC
WHERE BC.BOOK_CLASS = 'CORPORATE'
AND BC.DATE_INEFFECTIVE IS NULL
AND BC.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
AND DS.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
AND BK.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AD.ASSET_CATEGORY_ID
AND CT.CALENDAR_TYPE = BC.DEPRN_CALENDAR
AND BK.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND BK.ASSET_ID = AD.ASSET_ID
AND BK.COST <> 0
AND NVL(DP.PERIOD_CLOSE_DATE
, SYSDATE) BETWEEN BK.DATE_EFFECTIVE
AND NVL(BK.DATE_INEFFECTIVE
, SYSDATE)
AND DP.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND DP.FISCAL_YEAR BETWEEN BC.CURRENT_FISCAL_YEAR-4
AND BC.CURRENT_FISCAL_YEAR
AND (DP.PERIOD_NUM = CT.NUMBER_PER_FISCAL_YEAR OR DP.PERIOD_COUNTER = BC.LAST_PERIOD_COUNTER + 1)
AND DS.ASSET_ID = AD.ASSET_ID
AND DS.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND DS.PERIOD_COUNTER = DECODE(DP.FISCAL_YEAR
, BC.CURRENT_FISCAL_YEAR
, BC.LAST_PERIOD_COUNTER
, DP.PERIOD_COUNTER) GROUP BY DP.FISCAL_YEAR
, BC.ORG_ID
, BC.SET_OF_BOOKS_ID
, BC.BOOK_TYPE_CODE
, AD.ATTRIBUTE_CATEGORY_CODE)

Columns