Description:
Oracle Assets calculates depreciation using either the recoverable cost or the recoverable net book value as a basis. If the depreciation method uses the asset cost, Oracle Assets calculates the fiscal year depreciation by multiplying the recoverable price by the rate.
This query fetches the details of those assets which are partially and fully depreciated.
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
Summary
This Post described the script Create Project party in oracle projects accounting using Asset depreciation logic query in oracle EBS.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
