Loading

Friday, August 1, 2014

R12 Oracle Fixed Assets Technical Queries (PART1) - FA_ADDITIONS, FA_BOOKS, FA_DISTRIBUTION_HISTORY etc














In my recent project, I have asked to write an outbound interface from Oracle Apps Fixed Assets R12 module to external legacy systems. In the process of understanding the fixed assets tables, I have prepared few queries to fetch the different asset data from FA.

In this post, I have given all those queries. I hope it helps. At least it gives the idea for beginners like me.  

Asset details
SELECT fa.asset_id,
       fa.asset_number,
       fac.segment1 major_category,
       fac.description category_description,
       fak.concatenated_segments asset_key,
       fa.asset_key_ccid,
       fa.current_units,
       fa.asset_type,
       fa.tag_number,
       fa.description,
       fa.in_use_flag,
       fa.owned_leased,
       fa.new_used,
       fa.inventorial
  FROM fa_additions          fa,
       fa_categories         fac,
       fa_asset_keywords_kfv fak
 WHERE fa.asset_number = '123456'
   AND fa.asset_category_id = fac.category_id
   AND fa.asset_key_ccid = fak.code_combination_id;



Asset source lines PAYABLE
SELECT fa.asset_number,
       fai.asset_id,
       fai.asset_invoice_id,
       fai.fixed_assets_cost,
       fai.invoice_transaction_id_in,
       fai.invoice_number,
       fai.ap_distribution_line_number,
       fai.source_line_id,
       fai.invoice_distribution_id,
       fai.invoice_line_number,
       fai.invoice_id,
       aia.invoice_id,
       aila.line_number,
       aida.invoice_distribution_id
  FROM fa_additions                 fa,
       fa_asset_invoices            fai,
       ap_invoices_all              aia,
       ap_invoice_lines_all         aila,
       ap_invoice_distributions_all aida
 WHERE fai.invoice_id = aia.invoice_id
   AND aia.invoice_id = aila.invoice_id
   AND aia.invoice_id = aida.invoice_id
   AND aila.line_number = aida.invoice_line_number
   AND fai.invoice_line_number = aila.line_number
   AND fai.invoice_distribution_id = aida.invoice_distribution_id
   AND fai.asset_id = fa.asset_id
   AND fa.asset_number = '12345';



-- FA Assignment details

SELECT fdh.distribution_id,
       fdh.book_type_code,
       fdh.units_assigned,
       fdh.date_effective,
       fdh.date_ineffective,
       fdh.transaction_units,
       fdh.retirement_id,
       fdh.assigned_to,
       ppf.full_name,
       ppt.user_person_type,
       ppf.sex,
       ppf.employee_number,
       ppf.person_type_id,
       gcc.concatenated_segments expense_account,
       fl.segment1 location,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = asset_cost_account_ccid) asset_cost_account,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = asset_clearing_account_ccid)clearing_account,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = deprn_expense_account_ccid) depn_exp_account,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = deprn_reserve_account_ccid) depn_res_account,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = nbv_retired_gain_ccid) nbv_retired_gain,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = nbv_retired_loss_ccid) nbv_retired_loss,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = proceeds_sale_gain_ccid) proceeds_sale_gain,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = proceeds_sale_loss_ccid) proceeds_sale_loss,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = cost_removal_gain_ccid) proceeds_sale_gain,
       (SELECT concatenated_segments
          FROM gl_code_combinations_kfv
         WHERE code_combination_id = cost_removal_loss_ccid) proceeds_sale_loss,
       fda.*
  FROM per_person_types         ppt,
       per_people_f             ppf,
       fa_locations             fl,
       gl_code_combinations_kfv gcc,
       fa_distribution_accounts fda,
       fa_distribution_history  fdh,
       fa_additions             fa
 WHERE 1 = 1
   AND ppf.person_type_id = ppt.person_type_id
   AND ppf.person_id = fdh.assigned_to
   AND fl.location_id = fdh.location_id
   AND gcc.code_combination_id = fdh.code_combination_id
   AND fda.distribution_id = fdh.distribution_id
   AND fa.asset_id = fdh.asset_id
   AND fa.asset_number = '354009';




1 Responses to “R12 Oracle Fixed Assets Technical Queries (PART1) - FA_ADDITIONS, FA_BOOKS, FA_DISTRIBUTION_HISTORY etc”

Anonymous said...
August 1, 2014 at 8:17 AM

Hello.
In FA Assignment details, fdh.DATE_INEFFECTIVE IS NULL ?


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.