Oracle Apps Knowledge Sharing

-- 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';