FA Asset Summary (Germany)

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Description: Asset Summary Report (Germany)
Application: Assets

This Blitz Report has been extended to allow it to be run across multiple Ledgers and/or Asset Books.

Source: Asset Summary Report (Germany)
Short Name: FASSUMRPT
DB package: XXEN_FA_FAS_XMLP

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

with fa_summary as
(
select
 x.ledger,
 x.currency,
 x.book_name,
 x.book_class,
 x.asset_number,
 x.asset_description,
 x.date_placed_in_service,
 x.life_in_months,
case when x.life_in_months is not null then to_char(fnd_number.canonical_to_number((lpad(substr(to_char(trunc(x.life_in_months/12,0),'999'),2,3),3,' ') || '.' || substr(to_char(mod(x.life_in_months,12),'00'),2,2))),'990D99') end life_yr_mo,
 x.remaining_life_in_months,
case when x.remaining_life_in_months is not null then to_char(fnd_number.canonical_to_number((lpad(substr(to_char(trunc(x.remaining_life_in_months/12,0),'999'),2,3),3,' ') || '.' || substr(to_char(mod(x.remaining_life_in_months,12),'00'),2,2))),'990D99') end remaining_life_yr_mo,
 x.cost_account,
 x.cost_account_description,
 x.major_category,
 x.minor_category,
 x.company,
 x.company_description,
 x.account,
 x.account_description,
 x.cost_center,
 x.cost_center_description,
 x.asset_type,
 x.transaction_sub_type,
 sum(nvl(x.original_cost,0))original_cost,
 sum(nvl(x.current_cost,0)) current_cost,
 sum(nvl(x.begin_cost,0)) begin_cost,
 sum(nvl(x.reserve_amount,0)) reserve_amount,
 sum(nvl(x.retirements,0)) retirements,
 sum(nvl(x.changes_of_accounts,0)) changes_of_accounts,
 sum(nvl(x.additions,0)) additions,
 sum(nvl(x.additions,0)) cost_adjustments,
 sum(nvl(x.appreciation_amount,0)) appreciation_amount,
 sum(nvl(x.accum_deprn,0)) accum_deprn,
 sum(nvl(x.deprn_expenses,0)) deprn_expenses,
 x.asset_id,
 x.category_id,
 x.asset_rowid,
 x.category_rowid,
 x.asset_dff_context,
 x.category_dff_context
from
 (select distinct
   gsob.name ledger,
   gsob.currency_code currency,
   fbcs.book_type_code book_name,
   fl.meaning book_class,
   fadd.asset_number asset_number,
   fadd.description asset_description,
   trunc(fb.date_placed_in_service) date_placed_in_service,
   fb.life_in_months,
   (select
     greatest(fb.life_in_months - floor(months_between(fdp.calendar_period_close_date,fb.date_placed_in_service)),0)
    from
     fa_deprn_periods fdp
    where
     fdp.book_type_code = fb.book_type_code and
     fdp.period_close_date is null
   ) remaining_life_in_months,
   decode(fah.asset_type, 'CIP', nvl(fcb.cip_cost_acct,fcb.asset_cost_acct) ,fcb.asset_cost_acct) cost_account,
   gl_flexfields_pkg.get_description(gcc.chart_of_accounts_id,'GL_ACCOUNT',decode(fah.asset_type, 'CIP', nvl(fcb.cip_cost_acct,fcb.asset_cost_acct) ,fcb.asset_cost_acct)) cost_account_description,
   fc.segment1 major_category,
   fc.segment2 minor_category,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, null, gcc.code_combination_id, 'GL_BALANCING' , 'Y', 'VALUE') company,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, null, gcc.code_combination_id, 'GL_BALANCING' , 'Y', 'DESCRIPTION') company_description,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, null, gcc.code_combination_id, 'GL_ACCOUNT' , 'Y', 'VALUE') account,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, null, gcc.code_combination_id, 'GL_ACCOUNT' , 'Y', 'DESCRIPTION') account_description,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cc_seg' , 'SQLGL', 'GL#', gcc.chart_of_accounts_id, null, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') cost_center,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cc_seg' , 'SQLGL', 'GL#', gcc.chart_of_accounts_id, null, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION') cost_center_description,
   fah.asset_type asset_type,
   null transaction_sub_type,
   xxen_fa_fas_xmlp.fassumrpt_amount('ASSIGNED_UNITS'     ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) original_cost,
   xxen_fa_fas_xmlp.fassumrpt_amount('CURRENT_AMOUNT'     ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) current_cost,
   xxen_fa_fas_xmlp.fassumrpt_amount('BEGIN_COST'         ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) begin_cost,
   xxen_fa_fas_xmlp.fassumrpt_amount('RESERVE_AMOUNT'     ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) reserve_amount,
   xxen_fa_fas_xmlp.fassumrpt_amount('RETIREMENT_AMOUNT'  ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) retirements,
   xxen_fa_fas_xmlp.fassumrpt_amount('CHANGES_OF_ACCOUNTS',fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) changes_of_accounts,
   xxen_fa_fas_xmlp.fassumrpt_amount('ADDITIONS_AMOUNT'   ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) additions,
   xxen_fa_fas_xmlp.fassumrpt_amount('APPRECIATION_AMOUNT',fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) appreciation_amount,
   xxen_fa_fas_xmlp.fassumrpt_amount('ACCM_DEPRN_AMT'     ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) accum_deprn,
   xxen_fa_fas_xmlp.fassumrpt_amount('DEPRN_EXPENSE'      ,fbcs.book_type_code,fdp1.period_name,fdp2.period_name,fadd.asset_id,fc.category_id,fah.asset_type,gcc.code_combination_id,fah.transaction_header_id_in) deprn_expenses,
   fb.cost latest_book_cost,
   fadd.asset_id,
   fc.category_id,
   fadd.row_id asset_rowid,
   fc.rowid category_rowid,
   fadd.context asset_dff_context,
   fc.attribute_category_code category_dff_context
  from
   fa_asset_history fah,
   gl_code_combinations gcc,
   fa_categories_b fc,
   fa_category_books fcb,
   fa_additions fadd,
   fa_deprn_periods fdp1,
   fa_deprn_periods fdp2,
   fa_book_controls_sec fbcs,
   fa_books fb,
   fa_system_controls fsc,
   gl_sets_of_books gsob,
   fa_lookups fl
  where
       1=1
   and fah.asset_id = fadd.asset_id
   and fbcs.book_type_code = fb.book_type_code
   and fb.asset_id = fadd.asset_id
   and fb.transaction_header_id_in =
    (select
      max(fb2.transaction_header_id_in)
     from
      fa_books fb2
     where
      fb2.book_type_code = fb.book_type_code
      and fb2.asset_id = fb.asset_id
      and fb2.date_effective <= nvl(fdp2.period_close_date,sysdate)
    )
   and nvl(fb.period_counter_fully_retired,fdp1.period_counter + 1) >= fdp1.period_counter
   and fbcs.book_type_code = fcb.book_type_code
   and fah.category_id = fcb.category_id
   and fah.category_id = fc.category_id
   and gcc.code_combination_id = decode(fah.asset_type, 'CIP', nvl(fcb.wip_cost_account_ccid,fcb.asset_cost_account_ccid) ,fcb.asset_cost_account_ccid)
   and fbcs.book_type_code = fdp1.book_type_code
   and fdp1.book_type_code = fdp2.book_type_code
   and fdp1.period_counter <= fdp2.period_counter
   and upper(fdp1.period_name) = :p_period_open
   and upper(fdp2.period_name) = :p_period_close
   and ((fah.date_effective between fdp1.period_open_date and nvl(fdp2.period_close_date,sysdate)) or
        (fah.date_effective < fdp1.period_open_date and fah.date_ineffective > nvl(fdp2.period_close_date,sysdate)) or
        (fah.date_ineffective is null and fah.date_effective <= nvl(fdp2.period_close_date,sysdate)) or
        (fah.date_ineffective between fdp1.period_open_date and nvl(fdp2.period_close_date,sysdate) and fah.date_effective < fdp1.period_open_date)
       )
   and gsob.set_of_books_id = fbcs.set_of_books_id
   and fl.lookup_type = 'BOOK CLASS'
   and fl.lookup_code = fbcs.book_class
  ) x
group by
 x.ledger,
 x.currency,
 x.book_name,
 x.book_class,
 x.asset_number,
 x.asset_description,
 x.date_placed_in_service,
 x.life_in_months,
 x.remaining_life_in_months,
 x.cost_account,
 x.cost_account_description,
 x.major_category,
 x.minor_category,
 x.company,
 x.company_description,
 x.account,
 x.account_description,
 x.cost_center,
 x.cost_center_description,
 x.asset_type,
 x.transaction_sub_type,
 x.original_cost,
 x.asset_id,
 x.category_id,
 x.asset_rowid,
 x.category_rowid,
 x.asset_dff_context,
 x.category_dff_context
)
--
-- main query starts here
--
select
 fs.ledger,
 fs.currency,
 fs.book_class,
 fs.book_name,
 fs.asset_number,
 fs.asset_description,
 fs.date_placed_in_service,
 fs.life_in_months,
 fs.life_yr_mo,
 fs.remaining_life_in_months,
 fs.remaining_life_yr_mo,
 fs.major_category,
 fs.minor_category,
 fs.company,
 fs.company_description,
 fs.account,
 fs.account_description,
 fs.cost_center,
 fs.cost_center_description,
 fs.asset_type,
 --
 case
 when fs.asset_type = 'CIP'
 then fs.current_cost
 when fs.asset_type != 'CIP' and fs.changes_of_accounts <= 0 and fs.current_cost > 0
 then fs.current_cost
 else 0
 end original_asset_cost,
 fs.additions,
 fs.retirements,
 fs.changes_of_accounts,
 fs.appreciation_amount,
 case
 when fs.asset_type = 'CAPITALIZED'
 then fs.accum_deprn
 else null
 end accumulated_depreciation,
 case
 when fs.asset_type = 'CAPITALIZED'
 then fs.deprn_expenses
 else null
 end depreciation_expense,
 fs.current_cost + fs.additions + fs.changes_of_accounts - fs.retirements - fs.accum_deprn nbv_ending_period,
 fs.current_cost - fs.reserve_amount nbv_beginning_period,
 --
 &dff_segments
 --
 fs.ledger || ' (' || fs.currency || ')' ledger_label,
 fs.book_name || ' (' || fs.book_class || ')' book_label,
 fs.company || ' - ' || fs.company_description company_label,
 fs.account || ' - ' || fs.account_description account_label,
 fs.cost_center || ' - ' || fs.cost_center_description cost_centre_label
from
 fa_summary fs
order by
 fs.ledger,
 fs.book_class,
 fs.book_name,
 fs.major_category,
 fs.minor_category,
 fs.account,
 fs.asset_type,
 fs.cost_center,
 fs.asset_number
Parameter Name SQL text Validation
Ledger 
gsob.name=:p_ledger
 LOV
Book Class 
fbcs.book_class in (select fl.lookup_code from fa_lookups fl where fl.lookup_type = 'BOOK CLASS' and xxen_util.contains(:p_book_class,fl.meaning)='Y')
 LOV
Book 
fbcs.book_type_code=:p_book_name
 LOV
From Period 
 
 LOV
To Period 
 
 LOV
From Category 
fck.concatenated_segments>=:p_from_category
 LOV
To Category 
fck.concatenated_segments<=:p_to_category
 LOV
From Balancing Segment 
select
 'gcc.' || fifsgv.application_column_name || ' >= ''' || :p_from_bal_seg || ''''
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_vl fifsgv
where
fsav.application_id    = 101 and
fsav.id_flex_code      = 'GL#' and
fsav.segment_attribute_type = 'GL_BALANCING' and
fsav.id_flex_num       = :p_coa_id and
fsav.attribute_value   = 'Y' and
fifsgv.application_id=fsav.application_id and
fifsgv.id_flex_code=fsav.id_flex_code and
fifsgv.id_flex_num=fsav.id_flex_num and
fifsgv.application_column_name = fsav.application_column_name
 LOV
To Balancing Segment 
select
 'gcc.' || fifsgv.application_column_name || ' <= ''' || :p_to_bal_seg || ''''
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_vl fifsgv
where
fsav.application_id    = 101 and
fsav.id_flex_code      = 'GL#' and
fsav.segment_attribute_type = 'GL_BALANCING' and
fsav.id_flex_num       = :p_coa_id and
fsav.attribute_value   = 'Y' and
fifsgv.application_id=fsav.application_id and
fifsgv.id_flex_code=fsav.id_flex_code and
fifsgv.id_flex_num=fsav.id_flex_num and
fifsgv.application_column_name = fsav.application_column_name
 LOV
From Account Segment 
select
 'gcc.' || fifsgv.application_column_name || ' >= ''' || :p_from_account || ''''
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_vl fifsgv
where
fsav.application_id    = 101 and
fsav.id_flex_code      = 'GL#' and
fsav.segment_attribute_type = 'GL_ACCOUNT' and
fsav.id_flex_num       = :p_coa_id and
fsav.attribute_value   = 'Y' and
fifsgv.application_id=fsav.application_id and
fifsgv.id_flex_code=fsav.id_flex_code and
fifsgv.id_flex_num=fsav.id_flex_num and
fifsgv.application_column_name = fsav.application_column_name
 LOV
To Account Segment 
select
 'gcc.' || fifsgv.application_column_name || ' <= ''' || :p_to_account || ''''
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_vl fifsgv
where
fsav.application_id    = 101 and
fsav.id_flex_code      = 'GL#' and
fsav.segment_attribute_type = 'GL_ACCOUNT' and
fsav.id_flex_num       = :p_coa_id and
fsav.attribute_value   = 'Y' and
fifsgv.application_id=fsav.application_id and
fifsgv.id_flex_code=fsav.id_flex_code and
fifsgv.id_flex_num=fsav.id_flex_num and
fifsgv.application_column_name = fsav.application_column_name
 LOV
Asset Type 
fah.asset_type=:p_asset_type
 LOV Oracle
Descriptive Flexfield Attributes 
select
y.text
from
(
select distinct
x.title,
x.global_flag,
x.form_left_prompt,
case x.global_flag
when 1
then x.attr_text ||' "'||substrb(x.form_left_prompt,1,xxen_report.max_column_length)||'",'
else 'decode('||x.context_column_name ||','||
     listagg(''''||x.descriptive_flex_context_code||''','||x.attr_text,',')
     within group (order by x.global_flag,x.form_left_prompt)
     over (partition by x.application_id,x.application_table_name,x.title,x.global_flag,x.form_left_prompt) ||
     ',null) "'||substrb(x.form_left_prompt,1,xxen_report.max_column_length)||'",'
end text
from
(
select
fdfv.application_id,
fdfv.application_table_name,
fdfv.title,
decode(fdfv.application_table_name,'FA_ADDITIONS_B','fs.asset_dff_context','fs.category_dff_context') context_column_name,
decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2) global_flag,
fdfcuv.form_left_prompt,
fdfcuv.descriptive_flex_context_code,
fdfcuv.application_column_name,
fdfcuv.column_seq_num,
case when ffvs.validation_type in ('D','I','X','Y','F')
then
'(select
   case when instr(x.val,'': '',1,1) > 0
   then substr(x.val,1,instr(x.val,'': '',1,1)-1)
   else x.val
   end
  from
  (select
   xxen_util.display_flexfield_value
   (p_application_id => ' || fdfv.application_id || '
   ,p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || '''
   ,p_context_code => ''' || fdfcuv.descriptive_flex_context_code || '''
   ,p_column_name => ''' || fdfcuv.application_column_name || '''
   ,p_rowid => ' || decode(fdfv.application_table_name,'FA_ADDITIONS_B','fs.asset_rowid','fs.category_rowid') || '
   ) val
   from
   dual
  ) x
 )'
else
'xxen_util.display_flexfield_value
(p_application_id => ' || fdfv.application_id || '
,p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || '''
,p_context_code => ''' || fdfcuv.descriptive_flex_context_code || '''
,p_column_name => ''' || fdfcuv.application_column_name || '''
,p_rowid => ' || decode(fdfv.application_table_name,'FA_ADDITIONS_B','fs.asset_rowid','fs.category_rowid') || '
)'
end attr_text
from
fnd_descriptive_flexs_vl    fdfv,
fnd_descr_flex_contexts     fdfc,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets         ffvs
where
fdfc.application_id=fdfv.application_id and
fdfc.descriptive_flexfield_name=fdfv.descriptive_flexfield_name and
fdfcuv.application_id=fdfc.application_id and
fdfcuv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and
fdfcuv.descriptive_flex_context_code=fdfc.descriptive_flex_context_code and
ffvs.flex_value_set_id(+)= fdfcuv.flex_value_set_id and
fdfv.application_id=140 and
fdfv.application_table_name in ('FA_ADDITIONS_B','FA_CATEGORIES_B') and
fdfcuv.application_column_name like 'ATTRIBUTE%' and
fdfv.title = decode(:p_dff_title,'All',fdfv.title,:p_dff_title) and
fdfc.enabled_flag='Y' and
fdfcuv.enabled_flag='Y' and
fdfcuv.display_flag='Y'
) x
order by
x.title,
x.global_flag,
x.form_left_prompt
) y
 LOV
Download
   
Blitz Report™

Blitz Report™ provides multiple benefits: