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
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
Hello,
ReplyDeleteThanks for the query.
How can I get the script for GET_ACCUM_DEP_RET_AMNT_F ?
Thanks.
Did you find out about GET_ACCUM_DEP_RET_AMNT_F? Thanks,
DeleteHi Team - Thank you very much for the detail info. Its really useful.
ReplyDeleteCan you please tell us what is GET_ACCUM_DEP_RET_AMNT_F. where can we get the logic behind that. Please.
Thanks, AD
Did you find out about GET_ACCUM_DEP_RET_AMNT_F? Thanks,
DeleteCan you please confirm if this query is for Oracle Cloud or Oracle EBS?
ReplyDeleteOracle Fusion HCM Online Training
ReplyDeleteOracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
You truly did more than visitors’ expectations. Thank you for rendering these helpful, trusted, edifying and also cool thoughts on the topic.
ReplyDeleteAsset Management Software
Fixed Asset Management Software
Asset Tracking Software
Asset Management System
Asset Management Software Abu Dhabi
When using asset management software, advisors are also tasked to tutor the users regarding its proper use. Further, the use of asset management tools prompted by asset management advisors increases the company's productivity since it provides relevant information about their assets without employing additional personnel to run these for them. nam group
ReplyDelete
ReplyDeleteyes. its really great content for reading.
HRMS Software Dubai