APP-OFA-47685: Error: Unable to get information from FA_CALENDAR_PERIODS table - Oracle EBS R12

http://fixedasstets.blogspot.com/2016/10/app-ofa-47685-error-unable-to-get.html


Unable to get information from FA_CALENDAR_PERIODS table

+---------------------------------------------------------------------------+
Assets: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

FADEPR module: Depreciation Run
+---------------------------------------------------------------------------+

Current system time is 18-OCT-2016 17:06:35

+---------------------------------------------------------------------------+

Depreciation Program Exception Report
APP-OFA-48152: Asset number 108438
APP-FND-01564: ORACLE error 1403 in fazgtff

Cause: fazgtff failed due to ORA-01403: no data found
                                             .

The SQL statement being executed at the time of the error was:  and was executed from the file .
APP-OFA-47685: Error: Unable to get information from FA_CALENDAR_PERIODS table

Cause:        You have a database problem.

Action:        Contact your system administrator.

APP-OFA-47715: Error: function fazcff returned failure (called from faxcfyf)
APP-OFA-47715: Error: function faxcfyf returned failure (called from faxcde)
APP-OFA-47715: Error: function faxcde returned failure (called from fadmlp)
APP-OFA-47715: Error: function fadmlp returned failure (called from faddep)
Asset ID 108438 (108438) -> ** Failed **
APP-OFA-48152: Asset number 108755
APP-OFA-48152: Asset number 108756
APP-FND-01564: ORACLE error 1403 in fazgtff

Cause: fazgtff failed due to ORA-01403: no data found

--------------------------------------------------------------------------------------------------------------------------

In my Case Asset Calendar Periods were defined for only 3 Months for the Year. I Defined Calendar for Complete Year and Resolves the Issue.

------------------------------'OR' ----------------------------------as below

Depreciation Projection Report (FAPROJ) errors with:

APP-FND-1564: ORACLE error 1403 in fazgtff
APP-OFA-47685: Error:Unable to get information from FA_CALENDAR_PERIODS

fajprj failed due to ORA-1403: no data found


CAUSE

General Ledger calendar period names may be different than the Oracle Asset calendar period names.

For example:
GL may have Jan-99 but FA may have JAN-99

With these 2 diagnostic queries we can see that the period names are different:

select set_of_books_id, period_name, closing_status
from gl_period_statuses
where set_of_books_id in
(select set_of_books_id
from fa_book_controls
where book_type_code = '&your_deprn_book');

select period_name, period_counter, deprn_run
from fa_deprn_periods
where book_type_code = '&your_deprn_book'
order by period_counter, period_name ;

SOLUTION

Change the calendar period names to be the same. The report should complete
successfully.

To change unused period names:

Navigate to: Setup\Asset System\Calendars

1. Query the calendar in question.
2. Scroll to the last period.
3. From the Main menu select: Edit/Delete Record
4. Continue to delete as far back as necessary (or to where transactions have been entered for the period)
5. Re-add the periods deleted with the correct name.

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

Asset number and Asset ID skips - Oracle EBS R12

https://fixedasstets.blogspot.com/2016/06/asset-number-and-asset-id-skips.html

Error:

We have created two new books in FA, then create new assets. The Asset number & ID skips. Please find the attached document "Asset Number Skipping"

Solution:

Command

alter sequence FA.FA_ADDITIONS_S NOCACHE

--------------------------------------------------------------------------------------------------------------------------

you have two possible solutions to prevent sequences from aging out of the sequence cache.

The first and most obvious option would be not to cache the sequence values at all. But the result of this could be a moderate to severe degradation of the application's performance. This performance degradation may not affect individual manual asset additions so much as when posting Mass Additions. The following command will change the CACHE_SIZE to 0:

alter sequence fa_additions_s nocache;

Another solution is to prevent sequences from aging out of the library cache by pinning them using dbms_shared_pool.keep(). Pinning the sequence will prevent the sequence values from being aged out of the cache. Pinning the sequence is achieved by invoking the rdbms package dbms_shared_pool.keep() as follows:

dbms_shared_pool.keep('FA_ADDITIONS_S','Q')

for more details follow the note below
Automatic Asset Numbering Skips Numbers ( Doc ID 1036833.6 )

Manual Mass Addition For Current Period Errors with APP-48678 - Oracle EBS R12

http://fixedasstets.blogspot.com/2016/03/manual-mass-addition-for-current-period.html

Manual Mass Addition For Current Period Errors with APP-48678 (Doc ID 204105.1)

R11i to R12 Assets Error Listings for: APP-OFA-47039, APP-OFA-47169, APP-OFA-47661, APP-OFA-47670, APP-OFA-47693, APP-OFA-47715, APP-OFA-48066, APP-OFA-48208, APP-OFA-48353, APP-OFA-48392, APP-OFA-48393, APP-OFA-48397,APP-OFA-48398 and other APP-OFA-XXXX (Doc ID 1460268.1)

SYMPTOMS

Manually adding an asset to the current period errors with:

APP-OFA-48678 DATE MUST BE IN A FUTURE ASSET PERIOD

CAUSE

This is intended functionality.

SOLUTION

The Mass Additions workbench allows manual additions for FUTURE transactions. Use the Asset Workbench for adding assets/adjustments in the current period.