ASSET DEPRECIATION LOGIC QUERY

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