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

NEW ASSETS ADDITION QUERY

This query fetches the detailed information related to those assets which are added newly for a particular period.



 SELECT  FAL.segment1 SOL_ID
            ,FFV.description BRANCH_NAME
            ,FAA.tag_number  ASSET_ID
            ,FAA.description ASSET_DESCRIPTION
            ,FCB.SEGMENT1 ASSET_CLASS      --major_category
            ,FCB.segment2  ASSET_SUB_GROUP -- minor_category
            ,(FB.life_in_months)/12  DEP_YEAR
            ,FAA.creation_date ASSET_CREATION_DATE
            ,FB.date_placed_in_service INSERVICE_DATE
            ,FB.original_cost  COST
            ,APS.vendor_name MANUFACTURER_NAME
            ,'ADDITION' REMARKS
            ,FAA.asset_number DOCUMENT_NUM
            ,(SELECT FMA.create_batch_date
               FROM fa_mass_additions FMA
               WHERE 1=1
                 AND TRIM(FMA.asset_number) = TRIM(FAA.asset_number)
                 AND FMA.book_type_code     = FB.book_type_code) DOCUMENT_DATE
            ,FU.user_name AUTHORIZED_BY
            ,NULL COST_CENTER
            ,FAA.serial_number SL_NO
            ,SUBSTR(AIA.ATTRIBUTE5,1,INSTR(AIA.attribute5,'/')-1) PO
            ,(SELECT PPF1.full_name
                FROM per_all_people_f PPF1
               WHERE PPF1.person_id = FDH.assigned_to
                 AND TRUNC(sysdate) BETWEEN TRUNC(PPF1.effective_start_date) AND TRUNC(PPF1.effective_end_date)) CUSTODAIN
            ,GLCC.segment3 DEPRICIATION_ACCOUNT
            ,GLCC.segment4  DEP_LOC_SEGMENT
            ,GLCC.segment5  DEP_COST_CENTER
            ,FAK.segment1 CSR
FROM         fa_locations FAL
            ,fnd_flex_values_vl FFV
            ,fnd_flex_value_sets FFVS
            ,fa_distribution_history FDH
            ,fa_additions FAA
            ,fa_categories_b FCB
            ,fa_category_books FCBK
            ,fa_books FB
            ,fa_adjustments FADJ
            ,fa_asset_invoices FAI
            ,fa_asset_keywords FAK
            ,ap_suppliers APS
            ,ap_invoices_all AIA
            ,gl_code_combinations GLCC
            ,fnd_user FU
            ,per_all_people_f PPF
      WHERE 1=1
      AND FAL.segment1             = FFV.flex_value
      AND FFV.flex_value_set_id    = FFVS.flex_value_set_id
      AND FFVS.flex_value_set_name ='Axis_LOC_Value set'
      AND FAL.location_id         = FDH.location_id
      AND FDH.asset_id            = FAA.asset_id
      AND FCB.category_id         = FAA.asset_category_id
      AND FAA.asset_id            = FB.asset_id
      AND FB.book_type_code       = FADJ.book_type_code
      AND FDH.book_type_code      = FB.book_type_code
      AND FB.asset_id             = FADJ.asset_id
      AND FADJ.source_line_id     = FAI.source_line_id
      AND FCBK.category_id        = FCB.category_id
      AND APS.vendor_id           = FAI.po_vendor_id
      AND FAI.asset_id            = FAA.asset_id
      AND FAA.asset_key_ccid      = FAK.code_combination_id
      AND FAI.invoice_id          = AIA.invoice_id
      AND FDH.code_combination_id = GLCC.code_combination_id
      AND FAA.created_by   = FU.user_id
      AND FU.employee_id   = PPF.person_id
     -- AND FDH.assigned_to   = PPF.person_id
      AND FADJ.source_type_code   = 'ADDITION'
      AND FAA.parent_asset_id IS NULL
      AND fdh.transaction_header_id_out is null
      AND FCBK.book_type_code     = :p_asset_book 
      AND FAA.creation_date between TO_DATE(:p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(:p_date_to,'YYYY/MM/DD HH24:MI:SS')
GROUP BY FAL.segment1
        ,FFV.description
        ,FAA.tag_number
        ,FAA.description
        ,FCB.SEGMENT1  --major_category
        ,FCB.segment2   -- minor_category
        ,(FB.life_in_months)
        ,FAA.creation_date
        ,FB.date_placed_in_service
        ,FB.original_cost 
        ,APS.vendor_name
        ,FAA.asset_number
        ,FU.user_name
        ,FAA.serial_number
        ,aia.attribute5   
        ,GLCC.segment3
        ,GLCC.segment4
        ,GLCC.segment5
        ,FAK.segment1
        , FB.book_type_code
        ,FDH.assigned_to