FA Query

select SUM( NVL(ds.deprn_reserve,0) * ds.UNITS_ASSIGNED/ad.CURRENT_UNITS) as deprn_reserve
From (select a.asset_id,
a.tag_number,
a.current_units+nvl(b.units,0) as current_units,
a.asset_category_id
from apps.fa_additions a,
(select asset_id,sum(abs(transaction_units)) as units
from fa.fa_distribution_history
where asset_id in (select asset_id
from fa.fa_retirements
where to_char(date_retired,'mon-yy') = 'dec-03')
and (retirement_id is not null and date_ineffective is not null)
group by asset_id
) b
where a.asset_id = b.asset_id(+)
) ad,
apps.fa_books bk,
apps.fa_categories_b cat,
fa.FA_BOOK_CONTROLS fbc,
fa.fa_locations loc,
hr.per_all_people_f hr,
(Select ds2.asset_id, fdh.UNITS_ASSIGNED, cc.SEGMENT2,cc.SEGMENT3,
fdh.LOCATION_ID,fdh.ASSIGNED_TO,
ds2.deprn_reserve as deprn_reserve,
decode(dp2.fiscal_year,TO_NUMBER(TO_CHAR(to_date(upper('JAN-07'),'MON-RR'),'YYYY')),
ds2.ytd_deprn,Null) as ytd_deprn,
decode(dp2.period_name,upper('JAN-07'),ds2.deprn_amount,Null) as deprn_amount
from fa.fa_deprn_periods dp2,
fa.fa_deprn_summary ds2,
fa.FA_BOOK_CONTROLS fbc,
fa.FA_DISTRIBUTION_HISTORY fdh,
gl.GL_CODE_COMBINATIONS cc
where fbc.BOOK_CLASS = 'CORPORATE'
and dp2.book_type_code = fbc.book_type_code
and ds2.book_type_code = fbc.book_type_code
and fdh.book_type_code = fbc.book_type_code
and dp2.period_counter = ds2.period_counter
and ds2.asset_id = fdh.asset_id
and cc.CODE_COMBINATION_ID=fdh.CODE_COMBINATION_ID
and nvl(fdh.DATE_INEFFECTIVE,sysdate+1) > sysdate
and ds2.period_counter = ( Select Max(dss.period_counter)
from fa.fa_deprn_summary dss
where dss.book_type_code = ds2.book_type_code
and dss.asset_id = ds2.asset_id )
and dp2.book_type_code = fbc.BOOK_TYPE_CODE ) ds
where ad.ASSET_ID = bk.ASSET_ID
and fbc.BOOK_CLASS = 'CORPORATE'
and bk.book_type_code = fbc.book_type_code
and nvl(bk.DATE_INEFFECTIVE, sysdate+1)>sysdate
and ds.asset_id(+) = ad.asset_id
and ad.asset_category_id = cat.category_id
and ds.LOCATION_ID = loc.location_id
and loc.enabled_flag = 'Y'
and ds.assigned_to = hr.person_id(+)
and nvl(hr.EFFECTIVE_END_DATE,sysdate+1)>sysdate