FA Asset Retirements

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Imported from BI Publisher
Description: Asset Retirements Report
Application: Assets
Source: Asset Retirements Report (XML)
Short Name: FAS440_XML
DB package: FA_FAS440_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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_desciption,
  x.date_placed_in_service,
  x.date_retired,
  x.period,
  x.transaction_type,
  x.cost_retired,
  x.net_book_value_retired,
  x.proceeds_of_sale,
  x.removal_cost,
  x.reval_reserve_retired,
  (  -x.net_book_value_retired
   + x.proceeds_of_sale
   - x.removal_cost
   + x.reval_reserve_retired
  ) gain_loss,
  x.transaction_number,
  x.company_name || ': ' || x.book || ' (' || x.currency || ')' comp_book_curr_label
from
  (select   /*+ ordered */
     fsc.company_name,
     gsob.name ledger,
     fb.book_type_code book,
     gsob.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,
     fa.description asset_desciption,
     fr.date_retired,
     (select fdp.period_name from fa_deprn_periods fdp where fdp.book_type_code=fb.book_type_code and fth.date_effective >= fdp.calendar_period_open_date and fth.date_effective < fdp.calendar_period_close_date + 1 and rownum=1) period,
     fth.transaction_type_code,
     (select flv.meaning from fa_lookups_vl flv where flv.lookup_type='FAXOLTRX' and fth.transaction_type_code=flv.lookup_code) transaction_type,
     fth.asset_id,
     fb.date_placed_in_service,
     sum(decode(fadj.adjustment_type,'COST',1, 'CIP COST',1,0)        * decode(fadj.debit_credit_flag,'DR',-1,'CR', 1,0) * fadj.adjustment_amount) cost_retired,
     sum(decode(fadj.adjustment_type,'NBV RETIRED',-1,0)              * decode(fadj.debit_credit_flag,'DR',-1,'CR', 1,0) * fadj.adjustment_amount) net_book_value_retired,
     sum(decode(fadj.adjustment_type,'PROCEEDS CLR',1,'PROCEEDS',1,0) * decode(fadj.debit_credit_flag,'DR', 1,'CR',-1,0) * fadj.adjustment_amount) proceeds_of_sale,
     sum(decode(fadj.adjustment_type,'REMOVALCOST',-1,0)              * decode(fadj.debit_credit_flag,'DR',-1,'CR', 1,0) * fadj.adjustment_amount) removal_cost,
     sum(decode(fadj.adjustment_type,'REVAL RSV RET',1,0)             * decode(fadj.debit_credit_flag,'DR',-1,'CR', 1,0) * fadj.adjustment_amount) reval_reserve_retired,
     fth.transaction_header_id transaction_number
   from
     fa_system_controls      fsc,
     gl_sets_of_books        gsob,
     fa_transaction_headers  fth,
     fa_additions            fa,
     &lp_fa_books            fb,
     &lp_fa_retirements      fr,
     &lp_fa_adjustments      fadj,
     fa_distribution_history fdh,
     gl_code_combinations    gcc,
     fa_asset_history        fah,
     fa_category_books       fcb,
     fa_lookups              fl
   where
     gsob.set_of_books_id = :p_ca_set_of_books_id and
     fth.date_effective >= :period1_pod and
     fth.date_effective <= :period2_pcd and
     fth.book_type_code =  :p_book and
     fth.transaction_key = 'R' and
     fr.book_type_code = :p_book and
     fr.asset_id = fb.asset_id and
     decode(fth.transaction_type_code,'REINSTATEMENT', fr.transaction_header_id_out,fr.transaction_header_id_in) = fth.transaction_header_id and
     fa.asset_id = fth.asset_id and
     fadj.asset_id = fr.asset_id and
     fadj.book_type_code = :p_book and
     fadj.adjustment_type not in
       (select
          'PROCEEDS'
        from
          &lp_fa_adjustments fadj1
        where
          fadj1.book_type_code  = fadj.book_type_code and
          fadj1.asset_id        = fadj.asset_id and
          fadj1.transaction_header_id = fadj.transaction_header_id and
          fadj1.adjustment_type = 'PROCEEDS CLR'
       ) and
     fadj.transaction_header_id = fth.transaction_header_id and
     fah.asset_id = fa.asset_id and
     fah.date_effective <= fth.date_effective and
     nvl(fah.date_ineffective, fth.date_effective+1) > fth.date_effective and
     fl.lookup_code = fah.asset_type and
     fl.lookup_type = 'ASSET TYPE' and
     fb.transaction_header_id_out = fth.transaction_header_id and
     fb.book_type_code = :p_book and
     fb.asset_id = fa.asset_id and
     fcb.category_id = fah.category_id and
     fcb.book_type_code = :p_book and
     fdh.distribution_id = fadj.distribution_id and
     fth.asset_id = fdh.asset_id and
     gcc.code_combination_id  = fdh.code_combination_id
   group by
     fsc.company_name,
     gsob.name,
     fb.book_type_code,
     gsob.currency_code,
     fl.meaning,
     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'),
     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'),
     fth.transaction_type_code,
     fth.asset_id,
     fcb.asset_cost_acct,
     fcb.cip_cost_acct,
     fa.asset_number,
     fa.description,
     fb.date_placed_in_service,
     fr.date_retired,
     fth.date_effective,
     fth.transaction_header_id,
     fah.asset_type,
     fr.gain_loss_amount
   union
   select  /*+ ordered */
     fsc.company_name,
     gsob.name ledger,
     fb.book_type_code book,
     gsob.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,
     fa.description asset_desciption,
     fr.date_retired,
     (select fdp.period_name from fa_deprn_periods fdp where fdp.book_type_code=fb.book_type_code and fth.date_effective >= fdp.calendar_period_open_date and fth.date_effective < fdp.calendar_period_close_date + 1 and rownum=1) period,
     fth.transaction_type_code,
     (select flv.meaning from fa_lookups_vl flv where flv.lookup_type='FAXOLTRX' and fth.transaction_type_code=flv.lookup_code) transaction_type,
     fth.asset_id,
     fb.date_placed_in_service,
     0  cost_retired,
     0  net_book_value_retired,
     nvl(fr.proceeds_of_sale,0) proceeds_of_sale,
     nvl(fr.cost_of_removal,0) removal_cost,
     0 reval_reserve_retired,
     fth.transaction_header_id transaction_number
   from
     fa_system_controls      fsc,
     gl_sets_of_books        gsob,
     fa_transaction_headers  fth,
     fa_additions            fa,
     &lp_fa_books            fb,
     &lp_fa_retirements      fr,
     (select
        fdh.*
      from
        fa_transaction_headers  fth1,
        fa_distribution_history fdh,
        fa_book_controls        fbc,
        fa_transaction_headers  fth2
      where
        fth1.book_type_code          = :p_book and
        fth1.transaction_type_code  in ( 'FULL RETIREMENT', 'PARTIAL RETIREMENT') and
        fth1.date_effective    between :period1_pod and :period2_pcd and
        fth1.asset_id                = fdh.asset_id and
        fbc.book_type_code           = fth1.book_type_code and
        fbc.distribution_source_book = fdh.book_type_code and
        fth1.date_effective         <= nvl(fdh.date_ineffective,fth1.date_effective) and
        fth1.asset_id                = fth2.asset_id and
        fth2.book_type_code          = :p_book and
        fth2.transaction_type_code   = 'REINSTATEMENT' and
        fth2.date_effective    between :period1_pod and :period2_pcd and
        fth2.date_effective         >=  fdh.date_effective
     )                       fdh,
     gl_code_combinations    gcc,
     fa_asset_history        fah,
     fa_category_books       fcb,
     fa_lookups              fl
   where
     gsob.set_of_books_id = :p_ca_set_of_books_id and
     fth.date_effective >= :period1_pod and
     fth.date_effective <= :period2_pcd and
     fth.book_type_code =  :p_book and
     fth.transaction_key = 'R' and
     fr.book_type_code = :p_book and
     fr.asset_id = fb.asset_id and
     fr.transaction_header_id_out = fth.transaction_header_id and
     fa.asset_id = fth.asset_id and
     fah.asset_id = fa.asset_id and
     fah.date_effective <= fth.date_effective and
     nvl(fah.date_ineffective, fth.date_effective+1) > fth.date_effective and
     fl.lookup_code = fah.asset_type and
     fl.lookup_type = 'ASSET TYPE' and
     fb.transaction_header_id_out = fth.transaction_header_id and
     fb.book_type_code = :p_book and
     fb.asset_id = fa.asset_id and
     fcb.category_id = fah.category_id and
     fcb.book_type_code = :p_book and
     fth.asset_id = fdh.asset_id and
     gcc.code_combination_id = fdh.code_combination_id and
     fth.transaction_type_code = 'REINSTATEMENT' and
     fr.cost_retired = 0 and
     fr.cost_of_removal = 0 and
     fr.proceeds_of_sale = 0
   group by
     fsc.company_name,
     gsob.name,
     fb.book_type_code,
     gsob.currency_code,
     fl.meaning,
     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'),
     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'),
     fth.transaction_type_code,
     fth.asset_id,
     fcb.asset_cost_acct,
     fcb.cip_cost_acct,
     fa.asset_number,
     fa.description,
     fb.date_placed_in_service,
     fr.date_retired,
     fth.date_effective,
     fth.transaction_header_id,
     fah.asset_type,
     fr.gain_loss_amount,
     fr.status,
     fr.proceeds_of_sale,
     fr.cost_of_removal
  ) x
order by
  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.date_retired
Parameter Name SQL text Validation
Book 
 
 LOV Oracle
Set of Books Currency 
 
 LOV Oracle
From Period 
 
 LOV Oracle
To Period 
 
 LOV Oracle
Download
   
Related Blitz Reports
FA Asset Book Details FA Asset Impairments FA Asset Book Details 11i FA Additions By Source FA Asset Additions - draft FA Cost Adjustments - draft FA Asset Additions FA Form 4797 - Gain From Disposition of 1245 Property - draft FA Cost Adjustments
Blitz Report™

Blitz Report™ provides multiple benefits: