Friday, August 1, 2014

R12 Oracle Fixed Assets Technical Queries (PART2) - Depriciation and Retirement Details













This article is the continuation of the part 1 available in the below link, 


-- FA Retirement Details
SELECT fa.asset_number,
       fa.tag_number,
       fa.description,
       fr.retirement_id,
       fr.retirement_type_code,
       flb.description retirement_type_description,
       fr.book_type_code,
       fr.date_retired,
       fr.date_effective,
       fr.cost_retired,
       fr.status,
       fr.retirement_prorate_convention,
       fr.cost_of_removal,
       fr.nbv_retired,
       fr.proceeds_of_sale
  FROM fa_additions   fa,
       fa_retirements fr,
       fa_lookups_tl  flb
 WHERE fr.asset_id = fa.asset_id
   AND fa.asset_number = '123456'
   AND flb.lookup_type = 'RETIREMENT'
   AND flb.lookup_code = fr.retirement_type_code;


-- Asset depriciation calculation details
   
SELECT fa.asset_number,
       fb.book_type_code,
       fbc.book_type_name,
       fbc.set_of_books_id,
       gll.NAME ledger_name,
       fbc.deprn_calendar,
       fcat.description dern_calandar_desc,
       fbc.book_class,
       fb.date_placed_in_service,
       fb.date_effective,
       fb.deprn_start_date,
       fb.deprn_method_code,
       fm.NAME depriciation_method_name,
       fb.life_in_months,
       fb.rate_adjustment_factor,
       fb.original_cost,
       fb.salvage_value,
       fb.salvage_type,
       fb.prorate_convention_code,
       fct.description prorate_convention_desc,
       fb.prorate_date,
       fb.capitalize_flag,
       fb.retirement_pending_flag,
       fb.depreciate_flag,
       fb.period_counter_fully_retired
  FROM fa_additions        fa,
       fa_books            fb,
       fa_book_controls    fbc,
       gl_ledgers          gll,
       fa_convention_types fct,
       fa_methods          fm,
       fa_calendar_types   fcat
 WHERE fb.asset_id = fa.asset_id
   AND fa.asset_number = '123456'
   AND fb.date_ineffective IS NULL
   AND fbc.book_type_code = fb.book_type_code
   AND gll.ledger_id = fbc.set_of_books_id
   AND fcat.calendar_type = fbc.deprn_calendar
   AND fct.prorate_convention_code = fb.prorate_convention_code
   AND fb.deprn_method_code = fm.method_code;

-- Asset depriciation summary for a particular period
   SELECT fa.asset_number,
       fa.asset_id,
       fds.period_counter,
       fdp.period_name,
       fdp.fiscal_year,
       fdp.period_num,
       fds.book_type_code,
       fds.deprn_run_date,
       fds.deprn_amount,
       fds.ytd_deprn,
       fds.deprn_reserve,
       fds.deprn_source_code,
       fds.adjusted_cost
  FROM fa_additions        fa,
       fa_deprn_periods    fdp,
       fa.fa_deprn_summary fds
 WHERE fa.asset_id = fds.asset_id
   AND fdp.period_counter = fds.period_counter
   AND fdp.book_type_code = fds.book_type_code
   AND fa.asset_id = 12345
   AND fdp.period_name = '04-14';


-- Asset depriciation details for a particular period
SELECT fa.asset_number,
       fa.asset_id,
       fdd.period_counter,
       fdp.period_name,
       fdp.fiscal_year,
       fdp.period_num,
       fdd.book_type_code,
       fdd.distribution_id,
       fdd.deprn_run_date,
       fdd.deprn_amount,
       fdd.ytd_deprn,
       fdd.deprn_reserve,
       fdd.cost,
       fdd.deprn_adjustment_amount,
       fdd.event_id,
       fdd.deprn_run_id
  FROM fa_additions       fa,
       fa_deprn_periods   fdp,
       fa.fa_deprn_detail fdd
 WHERE fa.asset_id = fdd.asset_id
   AND fdp.period_counter = fdd.period_counter
   AND fdp.book_type_code = fdd.book_type_code
   AND fa.asset_id = 432178
   AND fdp.period_name = '04-14';


0 Responses to “R12 Oracle Fixed Assets Technical Queries (PART2) - Depriciation and Retirement Details”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.