SELECT fat.description "Asset_Name"
, fb.life_in_months
, fab.asset_number "asset_number"
, fab.asset_id
, to_char(fb.date_placed_in_service,'DD-MON-RRRR') "date_placed_in_service"
, to_char(fr.date_retired,'DD-MON-RRRR') "retirement_date"
, ceil(months_between(fr.date_retired, fb.prorate_date)) "Actual_lifetime"
, fr.proceeds_of_sale "proceeds_of_sale"
, fr.cost_of_removal "cost_of_removal"
, fr.nbv_retired "net_book_value"
, DECODE(SIGN(fr.gain_loss_amount), 1,
fr.gain_loss_amount, 0) gain_amount
, DECODE(SIGN(fr.gain_loss_amount), -1,
-1 * (fr.gain_loss_amount), 0) loss_amount
, fr.retirement_type_code "retirement_type"
,DECODE(fr.retirement_type_code,'RETIREMENT',0,(fb.life_in_months -
ceil(months_between(fr.date_retired, fb.prorate_date))))write_off_time
,DECODE(fr.retirement_type_code,'RETIREMENT',0,DECODE(SIGN(fr.gain_loss_amount), -1,-1 *
(fr.gain_loss_amount), 0)/(fb.life_in_months - ceil(months_between(fr.date_retired, fb.prorate_date)))
)write_off_amount
FROM fa_books fb
, fa_retirements fr
, fa_additions_b fab
, fa_additions_tl fat
, fa_lookups_b flb
WHERE fb.book_type_code = :P_BOOK_NAME
&lp_date_retired
AND fab.asset_id = fb.asset_id
AND fab.asset_id = fat.asset_id
AND fb.book_type_code = fr.book_type_code
AND fb.asset_id = fr.asset_id
AND fr.status = 'PROCESSED'
AND fat.language='US'
AND fb.date_ineffective IS NULL
AND fb.transaction_header_id_out IS NULL
AND flb.lookup_type = 'RETIREMENT'
AND flb.lookup_code(+) = fr.retirement_type_code
AND &lp_free_of_charge = 'Y'
ORDER BY asset_number |
