How Can An Organization Be Assigned To A Pre-Existing Asset Book? - Oracle EBS R12

http://fixedasstets.blogspot.com/2016/08/how-can-organization-be-assigned-to-pre.html

How Can An Organization Be Assigned To A Pre-Existing Asset Book?

1. Using an Assets responsibility, navigate to Setup > Security > Organization > Description.

2. Query the Organization (F11will put form in query mode, execute query via Ctrl + F11

3. Place the cursor on Asset Organization under Organization Classification. (Create using List of Values if necessary) 

4. Click on Others button 

5. Query the book and make a minor alteration to the book description and save. Note: You must use F11 first to get into query mode and then % in the book name. Then ctrl F11 should bring back the books. Ctrl F11 on its own without entering query mode first will not return a books rows.

Setup of FA Security Profile Does Not Work - Oracle EBS R12

http://fixedasstets.blogspot.com/2016/08/setup-of-fa-security-profile-does-not.html

When running depreciation, all books are displayed. 

However, only the asset book that is assigned to the particular organization should be displayed.

Navigation:  Responsibility FA / Depreciation / Run Depreciation

Solution:

1.  Query the organization                                                  
   Nav-> Setup / Security / Organization / Description      
               
2.  Classify the organization as an asset organization    
                  
3.  Click the button Others               
                                  
4.  Query a book  (F11 to place form in query mode then Ctrl+F11 to execute query)                                                      
5.  Make some changes to the book description in order to get the book saved for this organization.        
                                    
6.  Check whether the organization ID has been assigned to the book.           
   Nav. -> Help / Diagnostics / Examine or via the following SQL script

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)

APP-OFA-48232 Another transaction is already being performed on this asset when trying to perform a transaction. - Oracle EBS R12

http://fixedasstets.blogspot.com/2016/08/app-ofa-48232-another-transaction-is.html

Error:

APP-OFA-48232 Another transaction is already being performed on this asset when trying to perform a transaction.

Solution:

The issue is caused by existing lock sessions on FA objects.

Due to the locks, other locks for update cannot be obtained.

Please execute the following statement to verify if there are locked sessions in the FA tables:

select a. session_id, b.object_name
from V$locked_object a, dba_objects b
where a.OBJECT_ID=b.OBJECT_ID and b.object_name like 'FA%' and b.object_type='TABLE';

If yes, ask your DBA to release the lock and then try again.

Once the locks are released, the user can perform the transactions