FA Gain and Loss Query - Oracle EBS R12

http://fixedasstets.blogspot.com/2016/08/gain-and-loss-query.html


PLSQL Query to get Cost Retired, Proceeds of Sale, Gain & Loss and NBV of an Asset


select fc.ASSET_NUMBER,
       fb.COST,
       fr.COST_RETIRED,
       fb.ADJUSTED_COST,
     
       abs(NVL(sum(fr.PROCEEDS_OF_SALE), 0) -
           nvl(sum(fr.GAIN_LOSS_AMOUNT), 0) - nvl(sum(fr.COST_RETIRED), 0)) Acc_Dep
     
      ,
       NVL(sum(fr.PROCEEDS_OF_SALE), 0) - nvl(sum(fr.GAIN_LOSS_AMOUNT), 0) NBV,
       fr.PROCEEDS_OF_SALE,
     
       fr.GAIN_LOSS_AMOUNT
  from fa_retirements_v fr,
       fa_books         fb,
       fa_additions     fc,
     
       fa_categories_b   pp,
       fa_category_books cb,
       fa_locations      fl
     
      ,
       fa_asset_keywords       ak,
       fa_distribution_history dh,
       GL_CODE_COMBINATIONS    C

 where fr.RETIREMENT_ID = fb.RETIREMENT_ID
   
   and fc.ASSET_ID = fr.ASSET_ID
   
   and pp.CATEGORY_ID = fc.ASSET_CATEGORY_ID
   
   and pp.CATEGORY_ID = cb.CATEGORY_ID
   
   and ak.CODE_COMBINATION_ID = fc.ASSET_KEY_CCID
   
   and dh.ASSET_ID = fr.ASSET_ID
   
   and fl.LOCATION_ID = dh.LOCATION_ID
   
   and c.CODE_COMBINATION_ID = dh.CODE_COMBINATION_ID
   
   and dh.DATE_INEFFECTIVE is null

 group by fc.ASSET_NUMBER,
          fb.COST,
          fr.COST_RETIRED,
          fb.ADJUSTED_COST,
          fr.PROCEEDS_OF_SALE,
          fr.GAIN_LOSS_AMOUNT

#############################################################################

For My Case



select jj.ASSET_NUMBER,
     
       fb.DESCRIPTION,
       fb.TAG_NUMBER new_asset_no,
     
       pp.segment1 || '-' || pp.segment2 || '-' || pp.segment3 CATEGORY_CONCAT,
     
       fl.SEGMENT1 || '-' || fl.SEGMENT2 || '-' || fl.SEGMENT3 LOCATION_CONCAT,
     
       ak.segment1 AssetKey,
     
       fb.CURRENT_UNITS,
     
       fb.PROPERTY_TYPE_CODE,
     
       zz.date_placed_in_service,
     
       jj.DATE_RETIRED,
     
       zz.life_in_months,
     
       MONTHS_BETWEEN(TRUNC(jj.DATE_RETIRED, 'MONTH'),
                      TRUNC(zz.DATE_PLACED_IN_SERVICE, 'MONTH')) LIFE_USED,
     
       zz.COST,
     
       jj.COST_RETIRED,
     
       (
     
        select sum(dd.deprn_adjustment_amount)
     
          from fa_deprn_detail dd
     
         where dd.book_type_code = &p_book_type
           
           and dd.asset_id = fb.ASSET_ID
           
           and
           
               (
             
                (&P_DATE_RETIRED1 is null and &P_DATE_RETIRED2 is null)
             
                or
             
                (dd.PERIOD_COUNTER between
             
                (
               
                  SELECT max(P.PERIOD_COUNTER)
               
                    FROM FA_DEPRN_PERIODS P
               
                   WHERE
               
                   BOOK_TYPE_CODE = &P_BOOK_TYPE
               
                AND CALENDAR_PERIOD_OPEN_DATE <= &P_DATE_RETIRED1
               
                AND &P_DATE_RETIRED1 is not null
               
                  )
             
                and
             
                (
               
                  SELECT min(P.PERIOD_COUNTER)
               
                    FROM FA_DEPRN_PERIODS P
               
                   WHERE
               
                   BOOK_TYPE_CODE = &P_BOOK_TYPE
               
                AND CALENDAR_PERIOD_CLOSE_DATE >= &P_DATE_RETIRED2
               
                AND &P_DATE_RETIRED2 is not null
               
                  )
             
                )
             
               )
     
        )
     
       DEPRN_ADJUSTMENT_AMOUNT,
     
       (
     
        abs(NVL(sum(jj.PROCEEDS_OF_SALE), 0) -
            nvl(sum(jj.GAIN_LOSS_AMOUNT), 0) - nvl(sum(jj.COST_RETIRED), 0))
     
       ) RTRD_ACCDEPRN,
     
       NVL(sum(jj.PROCEEDS_OF_SALE), 0) - nvl(sum(jj.GAIN_LOSS_AMOUNT), 0) RTRD_NBV,
     
       jj.PROCEEDS_OF_SALE,
     
       --   jj.GAIN_LOSS_AMOUNT ,
     
       -1 * jj.GAIN_LOSS_AMOUNT GAIN_LOSS_AMOUNT, --for changing sign added on 07NOV2016
     
       jj.RETIREMENT_TYPE_CODE MODE_OF_DISPOSAL,
     
       jj.SOLD_TO,
     
       C.SEGMENT4 SEGMENT4_1,
     
       jj.ATTRIBUTE4 ATTRIBUTE4_1,
     
       jj.ATTRIBUTE3 ATTRIBUTE3_1,
     
       jj.RET_ATTRIBUTE1 COMMENT1,
     
       ------Remaining Life in Months--------
     
       ff.life_in_months -
     
       GREATEST
     
       (NVL(ff.life_in_months, 0)
     
        - MONTHS_BETWEEN(TRUNC(jj.DATE_RETIRED, 'MONTH'),
                         TRUNC(ff.DATE_PLACED_IN_SERVICE, 'MONTH')),
     
        0
     
        ) Life_Used2

/*,

zz.ADJUSTED_COST,*/

  from fa_retirements_v jj,
     
       fa_books zz,
     
       fa_additions fb,
     
       fa_categories_b pp,
     
       fa_category_books cb,
     
       fa_distribution_history dh,
     
       fa_locations fl,
     
       GL_CODE_COMBINATIONS C,
     
       fa_asset_keywords ak,
     
       fa_books_v ff /*,

(select lookup_code from fnd_lookups where lookup_type ='YES_NO'

and lookup_code= 'N')*/

 where 1 = 1
   
   and fb.ASSET_ID = zz.ASSET_ID
   
   and jj.ASSET_ID = zz.ASSET_ID
   
   and ff.asset_id = zz.ASSET_ID
   
   and jj.RETIREMENT_ID = zz.RETIREMENT_ID
   
   and zz.book_type_code = cb.BOOK_TYPE_CODE
   
   and zz.BOOK_TYPE_CODE = dh.BOOK_TYPE_CODE
   
   and jj.BOOK_TYPE_CODE = zz.BOOK_TYPE_CODE
   
   and fb.ASSET_ID = jj.ASSET_ID
   
   and zz.RETIREMENT_ID = jj.RETIREMENT_ID
   
   and dh.ASSET_ID = zz.ASSET_ID
   
   and ak.CODE_COMBINATION_ID = fb.ASSET_KEY_CCID
   
   and pp.CATEGORY_ID = fb.ASSET_CATEGORY_ID
   
   and pp.CATEGORY_id = cb.CATEGORY_ID
   
   and cb.CATEGORY_ID = fb.ASSET_CATEGORY_ID
   
   and c.CODE_COMBINATION_ID = dh.CODE_COMBINATION_ID
   
   and fl.LOCATION_ID = dh.LOCATION_ID
   
   AND DH.DATE_INEFFECTIVE IS NULL
   
   and jj.BOOK_TYPE_CODE = &P_BOOK_TYPE
   
   and pp.segment1 like nvl(&p_major_cat, pp.segment1)
   
   and pp.segment2 like nvl(&p_minor_cat, pp.segment2)
   
   and pp.segment3 like nvl(&p_type, pp.segment3)
   
   and ak.segment1 like nvl(&p_asset_key, ak.segment1)
   
   and nvl(fl.segment1, 'NA') like nvl(&p_city, nvl(fl.segment1, 'NA'))
   
   and nvl(fl.segment2, 'NA') like nvl(&p_area, nvl(fl.segment2, 'NA'))
   
   and nvl(fl.segment3, 'NA') like nvl(&p_nature, nvl(fl.segment3, 'NA'))
   
   and decode(decode(&p_property_type_code,
                     'ALL',
                     &p_property_type_code,
                     fb.PROPERTY_TYPE_CODE),
              'ALL',
              'A',
              'CAPITALIZE',
              'CAPITALIZE',
              'NONE') = decode(&p_property_type_code,
                               'ALL',
                               'A',
                               'CAPITALIZE',
                               'CAPITALIZE',
                               'NONE') -- added on January 04, 2011
   
   AND fb.ASSET_NUMBER = NVL(&P_ASSET_NUMBER, fb.ASSET_NUMBER)
   
   AND jj.STATUS = 'PROCESSED'
   
   AND jj.DATE_RETIRED BETWEEN NVL(&P_DATE_RETIRED1, jj.DATE_RETIRED) AND
       NVL(&P_DATE_RETIRED2, jj.DATE_RETIRED)
   
   AND TO_DATE(&P_PERIOD, 'MON-RR') >=
       TRUNC(NVL(jj.DATE_RETIRED, TO_DATE(&P_PERIOD, 'MON-RR')), 'MONTH')

--and Lookup_Code= &P_YES_NO

 group by fb.ASSET_ID,
          dh.distribution_id,
          jj.ASSET_NUMBER,
          zz.COST,
          jj.COST_RETIRED,
          jj.PROCEEDS_OF_SALE,
          jj.GAIN_LOSS_AMOUNT,
          zz.ADJUSTED_COST,
          fb.DESCRIPTION,
          fb.TAG_NUMBER,
       
          pp.segment1 || '-' || pp.segment2 || '-' || pp.segment3
       
         ,
          fl.SEGMENT1 || '-' || fl.SEGMENT2 || '-' || fl.SEGMENT3
       
         ,
          ak.segment1
       
         ,
          fb.CURRENT_UNITS
       
         ,
          fb.PROPERTY_TYPE_CODE
       
         ,
          zz.date_placed_in_service
       
         ,
          jj.DATE_RETIRED
       
         ,
          zz.life_in_months
       
         ,
          jj.RETIREMENT_TYPE_CODE
       
         ,
          jj.SOLD_TO
       
         ,
          C.SEGMENT4
       
         ,
          jj.ATTRIBUTE4
       
         ,
          jj.ATTRIBUTE3
       
         ,
          jj.RET_ATTRIBUTE1,
          ff.life_in_months,
          MONTHS_BETWEEN(TRUNC(jj.DATE_RETIRED, 'MONTH'),
                         TRUNC(ff.DATE_PLACED_IN_SERVICE, 'MONTH'))

 ORDER BY TO_NUMBER(JJ.ASSET_NUMBER)