SELECT substr(fad.description,1,11) Asset_Description,
substr(fad.asset_number,1,8) Asset_Number,
sum(nvl(fdd3.deprn_reserve,0)) Tax_Dp_reserve,
sum(nvl(fdd3.cost,0) - nvl(fdd3.deprn_reserve,0)) Initial_NBV,
fad.asset_id Asset_id,
fah.category_id Asset_Category_Id
--JE_JEFIASDR_XMLP_PKG.cf_asset_close_nbvformula(:CF_ASS_INITIAL_NBV_ADJUSTED, :CF_ASS_TAX_ADDITIONS, :CS_ASS_TAX_RETIREMENT, --:a1_1, :CF_ASS_DEF_DP) CF_ASS_CLOSE_NBV,
--&CS_ASS_TAX_ADDITIONS CF_ASS_TAX_ADDITIONS,
-- JE_JEFIASDR_XMLP_PKG.cf_ass_def_dpformula(:CS_ASS_TAX_DEPRN, :a1_1, :CS_ass_this_fisadj) CF_ASS_DEF_DP,
-- JE_JEFIASDR_XMLP_PKG.cf_ass_initial_nbv_adjustedfor(substr ( fad.asset_number , 1 , 8 ), :CAT_ID, :ENABLED_FLAG, sum ( nvl --( fdd3.cost , 0 ) - nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) CF_ASS_INITIAL_NBV_ADJUSTED,
-- JE_JEFIASDR_XMLP_PKG.cf_ass_tax_dp_reserve_adjusted(sum ( nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) --CF_ASS_TAX_DP_RESERVE_ADJUSTED,
--&CS_ASS_CORP_ADDITIONS CF_ASS_CORP_ADDITIONS
FROM fa_asset_history fah,
fa_additions fad,
fa_deprn_detail fdd3,
fa_books bks
WHERE fah.asset_id = fad.asset_id
and fdd3.asset_id = fad.asset_id
and fad.asset_id = bks.asset_id
and fdd3.book_type_code = bks.book_type_code
and bks.book_type_code = :P_TAX_BOOK
and (fdd3.period_counter = :C_FROM_COUNTER - 1
OR (fdd3.period_counter between :C_FROM_COUNTER and :C_TO_COUNTER and fdd3.deprn_source_code= 'B')
OR (bks.depreciate_flag = 'NO' and fdd3.period_counter < :C_FROM_COUNTER))
and fah.transaction_header_id_in = ( SELECT max(ah2.transaction_header_id_in)
FROM fa_asset_history ah2
WHERE ah2.asset_id = fah.asset_id
and ah2.category_id = fah.category_id
and to_date(ah2.date_effective,'dd-mon-yy') <=:C_TIME_TO_CLOSE_DATE
and nvl(ah2.date_ineffective,:C_TIME_TO_CLOSE_DATE) >= :C_TIME_TO_CLOSE_DATE )
and to_date(bks.date_effective,'dd-mon-yy') <= :C_TO_CLOSE_DATE
and nvl(bks.date_ineffective,:C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
and nvl(bks.period_counter_fully_retired,:C_FROM_COUNTER) >= :C_FROM_COUNTER
&C_ASSET_WHERE
and bks.transaction_header_id_in =
(SELECT min(bks2.transaction_header_id_in)
FROM fa_books bks2
WHERE bks2.book_type_code = :P_TAX_BOOK
and bks2.asset_id = fad.asset_id
and to_date(bks2.date_effective,'dd-mon-yy') <= :C_TO_CLOSE_DATE
and nvl(bks2.date_ineffective,:C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
and nvl(bks2.period_counter_fully_retired,:C_FROM_COUNTER) >= :C_FROM_COUNTER)
GROUP BY fad.description,
fad.asset_number,
bks.cost,
fad.asset_id,
fah.category_id
UNION ALL
SELECT substr(fad.description,1,11) Asset_Description,
substr(fad.asset_number,1,8) Asset_Number,
0 Tax_Dp_reserve,
0 Initial_NBV,
fad.asset_id Asset_id,
fah.category_id Asset_Category_Id
-- JE_JEFIASDR_XMLP_PKG.cf_asset_close_nbvformula(:CF_ASS_INITIAL_NBV_ADJUSTED, :CF_ASS_TAX_ADDITIONS, :CS_ASS_TAX_RETIREMENT, --:a1_1, :CF_ASS_DEF_DP) CF_ASS_CLOSE_NBV,
-- &CS_ASS_TAX_ADDITIONS CF_ASS_TAX_ADDITIONS,
-- JE_JEFIASDR_XMLP_PKG.cf_ass_def_dpformula(:CS_ASS_TAX_DEPRN, :a1_1, :CS_ass_this_fisadj) CF_ASS_DEF_DP,
-- JE_JEFIASDR_XMLP_PKG.cf_ass_initial_nbv_adjustedfor(substr ( fad.asset_number , 1 , 8 ), :CAT_ID, :ENABLED_FLAG,
-- sum ( nvl ( fdd3.cost , 0 ) - nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) --CF_ASS_INITIAL_NBV_ADJUSTED,
-- JE_JEFIASDR_XMLP_PKG.cf_ass_tax_dp_reserve_adjusted(sum ( nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) --CF_ASS_TAX_DP_RESERVE_ADJUSTED,
-- &CS_ASS_CORP_ADDITIONS CF_ASS_CORP_ADDITIONS
FROM fa_asset_history fah,
fa_additions fad,
fa_books bks
WHERE fah.asset_id = fad.asset_id
and fad.asset_id = bks.asset_id
and bks.book_type_code = :P_TAX_BOOK
and not exists (select 'x'
from fa_deprn_detail fdd3
where fdd3.asset_id = fad.asset_id
and fdd3.book_type_code = :P_tax_book
and ( (fdd3.period_counter = :C_FROM_COUNTER - 1)
OR (fdd3.period_counter between :C_FROM_COUNTER and :C_TO_COUNTER and fdd3.deprn_source_code= 'B')
OR (bks.depreciate_flag = 'NO' and fdd3.period_counter < :C_FROM_COUNTER) )
)
and exists (select 'x'
from fa_deprn_detail fddcorp
where fddcorp.asset_id = fad.asset_id
and fddcorp.book_type_code = :C_distribution_source_book
and fddcorp.period_counter >= :C_corp_from_counter )
and fah.transaction_header_id_in = (SELECT max(ah2.transaction_header_id_in)
FROM fa_asset_history ah2
WHERE ah2.asset_id = fah.asset_id
and ah2.category_id = fah.category_id
and to_date(ah2.date_effective,'dd-mon-yy') <= :C_TIME_TO_CLOSE_DATE
and nvl(ah2.date_ineffective, :C_TIME_TO_CLOSE_DATE) >= :C_TIME_TO_CLOSE_DATE)
and to_date(bks.date_effective,'dd-mon-yy') <= :C_TO_CLOSE_DATE
and nvl(bks.date_ineffective, :C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
and nvl(bks.period_counter_fully_retired, :C_FROM_COUNTER) >= :C_FROM_COUNTER
&C_ASSET_WHERE
and bks.transaction_header_id_in = (SELECT min(bks2.transaction_header_id_in)
FROM fa_books bks2
WHERE bks2.book_type_code = :P_TAX_BOOK
and bks2.asset_id = fad.asset_id
and to_date(bks2.date_effective,'dd-mon-yy') <= :C_TO_CLOSE_DATE
and nvl(bks2.date_ineffective, :C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
and nvl(bks2.period_counter_fully_retired, :C_FROM_COUNTER) >= :C_FROM_COUNTER)
and fah.category_id=:Cat_id
GROUP BY fad.description,
fad.asset_number,
bks.cost,
fad.asset_id,
fah.category_id
ORDER BY asset_number |