SELECT DISTINCT
fb.asset_number,
fab.book_type_code,
(SELECT
CASE
WHEN decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))<=0
then
0
ELSE
decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))
END
FROM
apps.fa_books faab,
apps.fa_deprn_periods fdpp,
apps.fa_additions_bfbb
WHERE
faab.asset_id=fbb.asset_id
AND faab.date_ineffecative is null
AND fdpp.period_counter=(
select
MAX(dpp.period_counter)
from
apps.fa_deprn_periods dpp
where
dpp.book_type_code=fdpp.book_type_code
)
AND faab.asset_id=fab.asset_id
and fb.asset_id=fbb.asset_id
and faab.book_type_code=fab.book_type_code
and fdp.period_counter=fdpp.period_counter
and fdp.book_type_code=fdpp.book_type_code
) rem_life_in_mon,
trunc((SELECT
CASE
WHEN decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))<=0
then
0
ELSE
decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))
END
FROM
apps.fa_books faab,
apps.fa_deprn_periods fdpp,
apps.fa_additions_bfbb
WHERE
faab.asset_id=fbb.asset_id
AND faab.date_ineffecative is null
AND fdpp.period_counter=(
select
MAX(dpp.period_counter)
from
apps.fa_deprn_periods dpp
where
dpp.book_type_code=fdpp.book_type_code
)
AND faab.asset_id=fab.asset_id
and fb.asset_id=fbb.asset_id
and faab.book_type_code=fab.book_type_code
and fdp.period_counter=fdpp.period_counter
and fdp.book_type_code=fdpp.book_type_code
)/12) years,
(SELECT
CASE
WHEN decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))<=0
then
0
ELSE
decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))
END
FROM
apps.fa_books faab,
apps.fa_deprn_periods fdpp,
apps.fa_additions_bfbb
WHERE
faab.asset_id=fbb.asset_id
AND faab.date_ineffecative is null
AND fdpp.period_counter=(
select
MAX(dpp.period_counter)
from
apps.fa_deprn_periods dpp
where
dpp.book_type_code=fdpp.book_type_code
)
AND faab.asset_id=fab.asset_id
and fb.asset_id=fbb.asset_id
and faab.book_type_code=fab.book_type_code
and fdp.period_counter=fdpp.period_counter
and fdp.book_type_code=fdpp.book_type_code
)-trunc((SELECT
CASE
WHEN decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))<=0
then
0
ELSE
decode(faab.conversion_date,
NULL,
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.prorate_date)),
faab.life_in_months-floor(months_between(fdpp.calender_period_close_date,faab.deprn_start_date)))
END
FROM
apps.fa_books faab,
apps.fa_deprn_periods fdpp,
apps.fa_additions_bfbb
WHERE
faab.asset_id=fbb.asset_id
AND faab.date_ineffecative is null
AND fdpp.period_counter=(
select
MAX(dpp.period_counter)
from
apps.fa_deprn_periods dpp
where
dpp.book_type_code=fdpp.book_type_code
)
AND faab.asset_id=fab.asset_id
and fb.asset_id=fbb.asset_id
and faab.book_type_code=fab.book_type_code
and fdp.period_counter=fdpp.period_counter
and fdp.book_type_code=fdpp.book_type_code
)/12)*12 months,
(select
a.transaction_type_code
from
apps.fa_transaction_history_trx_v a
where
a.asset_id=fab.asset_id
and a.book_type_code=fab.book_type_code
and a.category_id=fb.asset_category_id
and a.asset_id=fb.asset_id
anda.book_type_code=fdp.book_type_code
and a.transaction_type_code=’FULL RETIREMENT’
) full_retirement
FROM
apps.fa_booksfab,
apps.fa_deprn_periods fdp,
apps.fa_additions_bfb
WHERE
fab.asset_id=fb.asset_id
AND fdp.period_counter=(
select
MAX(dp.period_counter)
from
apps.fa_deprn_periods dp
where
dp.book_type_code=fdp.book_type_code
and dp.book_type_code=fab.book_type_code
)
ORDER BY TO_NUMBER(fb.asset_number)
Recent Posts
