SELECT ASSET_CATEGORY_ID
,MAJOR_CATEGORY
,OPENING_DAY_ASSET_COST
,OPEN_ACC_ASSET_COST_FY
,DECODE(SIGN(ASSET_COST_REDUCE_FY),-1,0,ASSET_COST_REDUCE_FY) ADD_TO_ASSET
,NVL(ASSET_COST_RETIRE_FY,DECODE(SIGN(ASSET_COST_REDUCE_FY),+1,0,ASSET_COST_REDUCE_FY)) REDUCE_TO_ASSET
,DEPRN_ADJU_COST_FY
,CURRENT_DEP_FY
,NVL(ASSET_ACC_DEP_RETIRE,0) ASSET_ACC_DEP_RETIRE
,((OPENING_DAY_ASSET_COST)+ (DECODE(SIGN(ASSET_COST_REDUCE_FY),-1,0,ASSET_COST_REDUCE_FY))-
(NVL(ASSET_COST_RETIRE_FY,DECODE(SIGN(ASSET_COST_REDUCE_FY),+1,0,ASSET_COST_REDUCE_FY)))-(OPEN_ACC_ASSET_COST_FY)-
(CURRENT_DEP_FY)-(NVL(ASSET_ACC_DEP_RETIRE,0))) CLOSING_WDV
FROM (SELECT FA.ASSET_CATEGORY_ID
,FFV.FLEX_VALUE MAJOR_CATEGORY
, SUM(FB.ORIGINAL_COST) OPENING_DAY_ASSET_COST
,(SELECT SUM(FDS.DEPRN_AMOUNT)
FROM FA_DEPRN_SUMMARY FDS
WHERE 1=1
AND FDS.ASSET_ID = FB.ASSET_ID
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
–AND FB.BOOK_TYPE_CODE = ‘AXIS CORP BOOK’
AND FDS.DEPRN_SOURCE_CODE =’DEPRN’
AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER) OPEN_ACC_ASSET_COST_FY
,(SELECT SUM(FR.COST_RETIRED)
FROM FA_RETIREMENTS FR
WHERE 1=1
AND FR.ASSET_ID = FB.ASSET_ID
AND FR.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE) ASSET_COST_RETIRE_FY
,((SELECT COST FROM FA_BOOKS WHERE ASSET_ID = FB.ASSET_ID AND BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE AND TRANSACTION_HEADER_ID_OUT IS NULL)-
(SELECT COST FROM FA_BOOKS B WHERE B.ASSET_ID= FB.ASSET_ID AND B.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND b.transaction_header_id_out = (SELECT MAX(a.TRANSACTION_HEADER_ID_OUT) from fa_books a where A.asset_id=B.ASSET_ID))) ASSET_COST_REDUCE_FY
,(SELECT SUM(FDS.ADJUSTED_COST)
FROM FA_DEPRN_SUMMARY FDS
WHERE 1=1
AND FDS.ASSET_ID = FB.ASSET_ID
–AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
–AND FB.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.DEPRN_SOURCE_CODE =’DEPRN’
AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER) DEPRN_ADJU_COST_FY
,(SELECT SUM(YTD_DEPRN)
FROM FA_DEPRN_SUMMARY FDS
WHERE 1=1
AND FDS.ASSET_ID = FB.ASSET_ID
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.DEPRN_SOURCE_CODE =’DEPRN’
AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER) CURRENT_DEP_FY
, (SELECT GET_ACCUM_DEP_RET_AMNT_F(FB.ASSET_ID,FB.BOOK_TYPE_CODE)FROM DUAL) ASSET_ACC_DEP_RETIRE
FROM FND_FLEX_VALUES FFV
,FND_FLEX_VALUE_SETS FFVS
,FA_CATEGORIES_B FCB
,FA_BOOKS FB
,FA_DEPRN_PERIODS FDP
,FA_ADDITIONS FA
WHERE 1=1
AND FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND UPPER(FFV.FLEX_VALUE) = FCB.SEGMENT1
AND FCB.CATEGORY_ID = FA.ASSET_CATEGORY_ID
AND FA.ASSET_ID = FB.ASSET_ID
AND FB.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND FFVS.FLEX_VALUE_SET_NAME = ‘ASSET_MAJOR_CATEGORY’
and fb.adjustment_required_status = ‘ADD’
AND FDP.PERIOD_NAME = :P_DEPRICIATION_PERIOD –‘MAR17-18’
AND FB.BOOK_TYPE_CODE = :P_ASSET_BOOK
GROUP BY FFV.FLEX_VALUE,
FDP.PERIOD_COUNTER,
FB.BOOK_TYPE_CODE,
FB.ASSET_ID,
FB.RETIREMENT_ID,
FB.ANNUAL_DEPRN_ROUNDING_FLAG,
FA.ASSET_CATEGORY_ID) Q
This query fetches the details of those assets which are partially and fully depreciated.
Recommended Posts