FA Cost Adjustments

with fa_trx as
(
select
  x.company_name,
  x.ledger,
  x.book,
  x.currency,
  x.balancing_segment,
  x.asset_type,
  x.asset_account,
  x.cost_center,
  x.asset_number,
  x.asset_description,
  x.asset_key,
  x.category category,
  x.period,
  sum(decode(x.unit_sum,x.units,x.old_cost1+x.old_cost-x.old_cost_rsum,x.old_cost1)) old_cost,
  sum(decode(x.unit_sum,x.units,x.new_cost1+x.new_cost-x.new_cost_rsum,x.new_cost1)) new_cost,
  sum(decode(x.unit_sum,x.units,x.new_cost1+x.new_cost-x.new_cost_rsum,x.new_cost1) - decode(x.unit_sum,x.units,x.old_cost1+x.old_cost-x.old_cost_rsum,x.old_cost1)) net_change,
  x.transaction_number,
  x.invoice_transaction_id
from
  ( select
      fsc.company_name,
      gl.name ledger,
      fdp.book_type_code book,
      gl.currency_code currency,
      fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'GL_BALANCING','Y','VALUE') balancing_segment,
      fl.meaning asset_type,
      decode(fah.asset_type, 'CIP', fcb.cip_cost_acct, fcb.asset_cost_acct) asset_account,
      fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'FA_COST_CTR','Y','VALUE') cost_center,
      fa.asset_number asset_number,
      fa.description  asset_description,
      fakk.concatenated_segments asset_key,
      fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_seg', 'OFA', 'CAT#', fsc.category_flex_structure, NULL, fc.category_id, 'ALL', 'Y', 'VALUE') category,
      fdp.period_name period,
      round((fb_old.cost * nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision) old_cost1,
      (round((fb_old.cost * nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision) + round(((fb_new.cost - fb_old.cost)* nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision)) new_cost1,
      sum(round((fb_old.cost * nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision)) over(partition by fth.transaction_header_id,fdh.asset_id order by fdh.distribution_id) old_cost_rsum,
      sum((round((fb_old.cost * nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision) + round(((fb_new.cost - fb_old.cost)* nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision))) over(partition by fth.transaction_header_id,fdh.asset_id order by fdh.distribution_id) new_cost_rsum,
      sum(nvl(fdh.units_assigned,fah.units)) over (partition by fth.transaction_header_id,fdh.asset_id order by fdh.distribution_id) unit_sum,
      fah.units units,
      fb_old.cost old_cost,
      fb_new.cost new_cost,
      fth.transaction_header_id transaction_number,
      fth.invoice_transaction_id
    from
      fa_system_controls      fsc,
      gl_ledgers              gl,
      fnd_currencies          fcu,
      fa_asset_history        fah,
      fa_additions            fa,
      fa_asset_keywords_kfv   fakk,
      fa_categories           fc,
      fa_category_books       fcb,
      &lp_fa_books            fb_old,
      &lp_fa_books            fb_new,
      fa_lookups              fl,
      &lp_fa_deprn_periods    fdp,
      fa_distribution_history fdh,
      gl_code_combinations    gcc,
      fa_transaction_headers  fth
    where
      gl.ledger_id = :p_ca_set_of_books_id and
      fcu.currency_code = gl.currency_code and
      fdp.book_type_code = :p_book and
      fdp.period_counter >= :period1_pc and
      fdp.period_counter <= nvl(:period2_pc, fdp.period_counter) and
      fth.book_type_code = fdp.book_type_code and
      fth.date_effective between fdp.period_open_date and nvl(fdp.period_close_date, sysdate) and
      fth.transaction_type_code in ('ADJUSTMENT','CIP ADJUSTMENT') and
      fb_old.transaction_header_id_out = fth.transaction_header_id and
      fb_old.book_type_code = fth.book_type_code and
      fb_new.transaction_header_id_in = fth.transaction_header_id and
      fb_new.book_type_code = fth.book_type_code and
      fa.asset_id = fth.asset_id and
      fa.asset_key_ccid = fakk.code_combination_id (+) and
      fl.lookup_type = 'ASSET TYPE' and
      fcb.category_id = fah.category_id and
      fcb.book_type_code = fth.book_type_code and
      fc.category_id = fcb.category_id and
      fah.asset_id = fa.asset_id and
      fah.asset_type = fl.lookup_code and
      fth.transaction_header_id >= fah.transaction_header_id_in and
      fth.transaction_header_id < nvl(fah.transaction_header_id_out, fth.transaction_header_id + 1) and
      fth.asset_id = fdh.asset_id and
      :p_distribution_source_book = fdh.book_type_code and
      fth.transaction_header_id >= fdh.transaction_header_id_in and
      fth.transaction_header_id < nvl(fdh.transaction_header_id_out, fth.transaction_header_id + 1) and
      fdh.code_combination_id = gcc.code_combination_id and
      round((fb_old.cost * nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision) != round((fb_new.cost * nvl(fdh.units_assigned,fah.units)/fah.units), fcu.precision)
  ) x
group by
  x.company_name,
  x.ledger,
  x.book,
  x.currency,
  x.balancing_segment,
  x.asset_type,
  x.cost_center,
  x.asset_account,
  x.asset_number,
  x.asset_description,
  x.asset_key,
  x.category,
  x.period,
  x.transaction_number,
  x.invoice_transaction_id
)
--
--
--
select
  ft.company_name,
  ft.ledger,
  ft.book,
  ft.currency,
  ft.balancing_segment,
  ft.asset_type,
  ft.asset_account,
  ft.cost_center,
  ft.asset_number,
  ft.asset_description,
  ft.asset_key,
  ft.asset_key||' - '||ft.asset_description asset_key_description,
  ft.category category,
  ft.period,
  case when 1 = row_number() over (partition by ft.transaction_number order by ft.transaction_number)
  then ft.old_cost
  else null
  end old_cost,
  case when 1 = row_number() over (partition by ft.transaction_number order by ft.transaction_number)
  then ft.new_cost
  else null
  end new_cost,
  case when 1 = row_number() over (partition by ft.transaction_number order by ft.transaction_number)
  then ft.net_change
  else null
  end net_change,
  ft.transaction_number,
  --
  asu.vendor_name vendor,
  asu.segment1 vendor_num,
  fai.po_number,
  fai.invoice_number,
  fai.fixed_assets_cost invoice_cost,
  fai.description invoice_description,
  --
  ft.company_name || ': ' || ft.book || ' (' || ft.currency || ')' comp_book_curr_label
from
  fa_trx ft,
  fa_asset_invoices fai,
  ap_suppliers asu
where
  nvl2(:p_show_invoice_details,ft.invoice_transaction_id,null) = fai.invoice_transaction_id_in (+) and
  fai.po_vendor_id = asu.vendor_id (+)
order by
  ft.company_name,
  ft.ledger,
  ft.book,
  ft.currency,
  ft.balancing_segment,
  ft.asset_type,
  ft.asset_account,
  ft.cost_center,
  ft.asset_number,
  ft.transaction_number