Calculate Asset YTD Query

Calculate Asset YTD Query


select fb.book_type_code,
asset_number,
fat.description Asset_Desc,
fb.date_placed_in_service,
(fb.LIFE_IN_MONTHS/12) LIFE_IN_YEARS,
-- null invoice_number,
-- null invoice_desc,
-- null invoice_cost,
fb.cost,
fb.original_cost,
fds.deprn_amount,
fds.DEPRN_RESERVE ACC_DEPRN,
fds.ytd_deprn,
fb.deprn_method_code,
DECODE(fb.cost,0,0, (fb.COST - fds.DEPRN_RESERVE)) NET_BOOK_VALUE,
fdp.period_name,
-- null vendor_name,
fc.segment2 Major,
fc.segment3 Minor
from fa_additions_b fa,
fa_additions_tl fat,
fa_books fb,
fa_book_controls fbc,
fa_categories fc,
fa_deprn_summary fds,
fa_deprn_periods fdp
where fdp.period_name =:period_name
and fa.asset_id = fb.asset_id
and fa.asset_id = fat.asset_id(+)
and fa.asset_category_id = fc.category_id
and fb.book_type_code = fbc.book_type_code
and fb.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE
and fds.ASSET_ID = fb.ASSET_ID
and fdp.PERIOD_COUNTER = DECODE(fbc.INITIAL_PERIOD_COUNTER,fds.PERIOD_COUNT ER,fds.PERIOD_COUNTER + 1 ,fds.PERIOD_COUNTER)
and fb.date_ineffective is NULL





Asset Opening

select substr(c.asset_number,6,1) asset_number,sum(a.deprn_reserve) nbt_depr_op_bal
from fa_deprn_summary a,fa_deprn_periods b,fa_additions c
where a.period_counter = b.period_counter
and b.calendar_period_close_date < pf_date
AND b.book_type_code = nbt_book_type
and a.asset_id = c.asset_id
and substr(c.asset_number,1,3) = p_orgid
and a.period_counter = (select max(d.period_counter) from fa_deprn_summary d,fa_deprn_periods e
where a.asset_id = d.asset_id
and d.period_counter = e.period_counter
and e.calendar_period_close_date < pf_date)
group by substr(c.asset_number,6,1);

Depreciation for the year

select sum(a.deprn_amount) into nbt_year_total
from fa_deprn_summary a,fa_deprn_periods b,fa_additions c
where a.period_counter = b.period_counter
and b.calendar_period_close_date between pf_date and pt_date
AND b.book_type_code = nbt_book_type
and a.asset_id = c.asset_id
and substr(c.asset_number,1,3) = p_orgid
and substr(c.asset_number,6,1) = c2_rec.asset_number;
exception when no_data_found then nbt_year_total := 0;
end;

Depreciation for the month

select sum(a.deprn_amount) into nbt_month_total
from fa_deprn_summary a,fa_deprn_periods b,fa_additions c
where a.period_counter = b.period_counter
AND to_char(b.calendar_period_close_date,'MONYYYY') = TO_CHAR(pt_date,'MONYYYY')
AND b.book_type_code = nbt_book_type
and a.asset_id = c.asset_id
and substr(c.asset_number,1,3) = p_orgid
and substr(c.asset_number,6,1) = c2_rec.asset_number;
exception when no_data_found then nbt_year_total := 0;
end;