Friday 21 September 2018

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

9 comments:

  1. Hello,
    Thanks for the query.
    How can I get the script for GET_ACCUM_DEP_RET_AMNT_F ?
    Thanks.

    ReplyDelete
    Replies
    1. Did you find out about GET_ACCUM_DEP_RET_AMNT_F? Thanks,

      Delete
  2. Hi Team - Thank you very much for the detail info. Its really useful.
    Can you please tell us what is GET_ACCUM_DEP_RET_AMNT_F. where can we get the logic behind that. Please.

    Thanks, AD

    ReplyDelete
    Replies
    1. Did you find out about GET_ACCUM_DEP_RET_AMNT_F? Thanks,

      Delete
  3. Can you please confirm if this query is for Oracle Cloud or Oracle EBS?

    ReplyDelete
  4. You truly did more than visitors’ expectations. Thank you for rendering these helpful, trusted, edifying and also cool thoughts on the topic.
    Asset Management Software
    Fixed Asset Management Software
    Asset Tracking Software
    Asset Management System
    Asset Management Software Abu Dhabi

    ReplyDelete
  5. 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