select
nvl(&ACCT_FLEX_BAL_SEG,'None') COMP_CODE,
cb.asset_cost_acct ASSET_ACCT,
adt.asset_number||' - '||adt.description ASSET_NUMBER,
to_char(bks.adjusted_rate*100, '99D99') || '%' RATE,
bks.date_placed_in_service PURCH_DATE,
round(sum(decode(bks.period_counter_fully_retired, '', bks.cost, 0)
* dh.units_assigned / ah.units), :PRECISION) COST,
round(sum(decode(d1.deprn_reserve,'', 0, d1.deprn_reserve)
* dh.units_assigned / ah.units), :PRECISION) PRV_DEPRN,
round(sum(decode(d1.deprn_reserve,'', (bks.cost - dc.deprn_reserve),
(bks.cost - d1.deprn_reserve)) * dh.units_assigned / ah.units),
:PRECISION) O_NBV,
round(sum(nvl(dn.ytd_deprn,0)
* dh.units_assigned/ ah.units), :PRECISION) YTD_DEP,
round(sum(decode(bks.period_counter_fully_retired, '',
(bks.cost - dn.deprn_reserve), 0) * dh.units_assigned/ ah.units),
:PRECISION) C_NBV,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE
from
fa_distribution_history dh,
fa_asset_history ah,
fa_additions adt,
gl_code_combinations dhcc,
fa_category_books cb,
fa_books bks,
fa_methods mth,
fa_deprn_summary dn,
fa_deprn_summary d1,
fa_deprn_summary dc
where
dc.book_type_code = :P_BOOK and
dc.deprn_source_code = 'BOOKS'
and
d1.book_type_code(+) = :P_BOOK and
d1.period_COUNTER(+) = :OPEN_PC
and dn.book_type_code = :P_BOOK and
dn.period_COUNTER = ( select dp.period_COUNTER
from fa_deprn_periods dp
where dp.book_type_code = :P_BOOK
and dp.period_counter =
( select max(dpz.period_counter)
from fa_deprn_summary dsz,
fa_deprn_periods dpz
where dpz.book_type_code = :P_BOOK
and dpz.period_counter <=
:PERIOD1_PC
and dsz.book_type_code = :P_BOOK
and dsz.period_counter = dpz.period_counter
and dsz.asset_id = dn.asset_id
))
and
dc.asset_id = dn.asset_id and
d1.asset_id(+) = dn.asset_id
and
bks.book_type_code = :P_BOOK and
bks.asset_id = dn.asset_id and
nvl(bks.date_ineffective,sysdate) >
:PERIOD1_PCD and
bks.date_effective <=
:PERIOD1_PCD and
nvl(bks.period_counter_fully_retired,:PERIOD1_PC) in
( select dpy.period_counter
from fa_deprn_periods dpy
where dpy.book_type_code = :P_BOOK
and dpy.fiscal_year = :PERIOD1_FY ) and
bks.deprn_method_code = mth.method_code
and
mth.deprn_basis_rule = 'NBV' and
mth.life_in_months is null
and adt.asset_type = 'CAPITALIZED' and
adt.asset_id = dn.asset_id and
adt.asset_category_id = cb.category_id
and
cb.book_type_code = :P_BOOK
and
adt.asset_id = dh.asset_id and
dh.book_type_code = :DISTRIBUTION_SOURCE_BOOK and
nvl(dh.date_ineffective,sysdate) >
:PERIOD1_PCD and
dh.date_effective <=
:PERIOD1_PCD
and
dhcc.code_combination_id(+) = dh.code_combination_id and
ah.asset_id = adt.asset_id and
nvl(ah.date_ineffective,sysdate) >
:PERIOD1_PCD and
ah.date_effective <=
:PERIOD1_PCD
group by
nvl(&ACCT_FLEX_BAL_SEG,'None'),
cb.asset_cost_acct,
adt.asset_number||' - '||adt.description,
bks.adjusted_rate,
bks.date_placed_in_service,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
order by 1,2,
adt.asset_number||' - '||adt.description |