select
x.company_name company_name,
x.ledger ledger,
x.book book,
x.currency currency,
x.period period,
x.fiscal_year fiscal_year,
x.bal_segment balancing_segment,
x.ast_account asset_account,
x.rsv_account reserve_account,
x.asset_number asset_number,
x.asset_desc asset_description,
x.asset_key,
x.asset_key||' - '||x.asset_desc asset_key_description,
x.start_date date_placed_in_service,
x.method depreciation_method,
x.d_life "Life Yr.Mo",
x.cost cost,
x.deprn_amount depreciation_amount,
x.ytd_deprn ytd_depreciation,
x.deprn_reserve depreciation_reserve,
x.transaction_type transaction_type,
x.company_name || ': ' || x.book || ' - ' || x.period || ' (' || x.currency || ')' comp_book_prd_curr_label,
'FY: ' || x.fiscal_year fiscal_year_label
from
(
select
fsc.company_name,
gl.name ledger,
:p_book book,
gl.currency_code currency,
:p_period1 period,
ffy.fiscal_year,
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') bal_segment,
frlg.asset_cost_acct ast_account,
frlg.deprn_reserve_acct rsv_account,
fa.asset_number,
fa.description asset_desc,
fakk.concatenated_segments asset_key,
frlg.date_placed_in_service start_date,
frlg.method_code method,
frlg.life life,
frlg.rate adj_rate,
frlg.bonus_rate bonus_rate,
frlg.capacity prod,
round(sum(frlg.cost),fc.precision) cost,
round(sum(frlg.deprn_amount),fc.precision) deprn_amount,
round(sum(frlg.ytd_deprn),fc.precision) ytd_deprn,
round(sum(frlg.deprn_reserve),fc.precision) deprn_reserve,
frlg.transaction_type t_type,
case frlg.transaction_type
when 'P' then 'Partial Unit Retirement'
when 'F' then 'Full Retirement'
when 'T' then 'Transfer Out'
when 'N' then 'Non-depreciating Asset'
when 'R' then 'Reclassification'
when 'B' then 'Bonus Depreciation Amount'
else frlg.transaction_type
end transaction_type,
fa_fas480_xmlp_pkg.d_lifeformula(frlg.life, frlg.rate, frlg.bonus_rate, frlg.capacity) d_life
from
fa_system_controls fsc,
gl_ledgers gl,
fnd_currencies fc,
fa_book_controls fbc,
fa_reserve_ledger_gt frlg,
fa_additions fa,
fa_asset_keywords_kfv fakk,
gl_code_combinations gcc,
fa_fiscal_year ffy
where
gl.ledger_id = :p_ca_set_of_books_id and
fc.currency_code = gl.currency_code and
fbc.book_type_code = :p_book and
fa.asset_id = frlg.asset_id and
fa.asset_key_ccid = fakk.code_combination_id (+) and
gcc.code_combination_id = frlg.dh_ccid and
ffy.fiscal_year_name = fbc.fiscal_year_name and
frlg.date_placed_in_service between ffy.start_date and ffy.end_date and
1=1
group by
fsc.company_name,
gl.name,
:p_book,
gl.currency_code,
fc.precision,
:p_period1,
ffy.fiscal_year,
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'),
frlg.asset_cost_acct,
frlg.deprn_reserve_acct,
fa.asset_number,
fa.description,
fakk.concatenated_segments,
frlg.date_placed_in_service,
frlg.method_code,
frlg.life,
frlg.rate,
frlg.bonus_rate,
frlg.capacity,
frlg.transaction_type
) x
order by
x.company_name,
x.ledger,
x.book,
x.currency,
x.period,
x.bal_segment,
x.fiscal_year,
x.ast_account,
x.rsv_account,
x.asset_number,
x.asset_desc,
x.method,
x.d_life,
x.start_date |