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




3 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 ?


Anonymous said...
May 4, 2018 at 8:04 AM

Here's a usefull one for supporting FA: It's a script to remove FUTURE ADDs, just query the FA_MASS_ADDITIONS table and determine the Mass Addition ID of the line that has a FUTURE ADD as its Transaction Type, and the use the ID to run this update script to remove the FUTURE ADD and its associated date, and then the Asset will behave as normal:

Update FA.FA_Mass_Additions FX
SET FX.Transaction_Type_Code = '',
FX.Transaction_Date = ''
Where FX.Mass_Addition_ID = 2178609 (example of ID)


Anonymous said...
June 29, 2022 at 7:49 AM

Please any one can help..Asset_key_ccid is null in fa_additions_b table.. what could be the reason


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.