Oracle Apps Knowledge Sharing

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