Introduction:
This Post illustrates the steps required to extract the FA details based on the asset book in Oracle EBS R12
Script to Query to extract FA details based on the asset book
SELECT
fp.period_name,
adt.asset_number,
adt.tag_number,
bks.book_type_code,
ltrim(rtrim(cat.segment1)) ||’-‘|| ltrim(rtrim(cat.segment2)) ||’-‘|| ltrim(rtrim(cat.segment3)) category,
bks.date_placed_in_service,
bks.original_cost,
adt.description,
adt.context subject_to_property_tax,
adt.attribute1 property_tax_code,
dn.deprn_reserve,
nvl(bks.original_cost,0) – nvl(dn.deprn_reserve,0) net_book_value,
dn.ytd_deprn,
(select dhcc.segment1||’.’||dhcc.segment2||’.’||dhcc.segment3||’.’||dhcc.segment4||’.’||dhcc.segment5||’.’||dhcc.segment6||’.’||dhcc.segment7||’.’||dhcc.segment8||’.’||dhcc.segment9 from gl_code_combinations dhcc,fa_distribution_history dh where dhcc.code_combination_id(+) = dh.code_combination_id and dh.book_type_code = :BOOK_NAME and adt.asset_id = dh.asset_id ) EXPENSE_ACCOUNT,
(select fl.segment1||’.’||fl.segment2||’.’||fl.segment3||’.’||fl.segment4||’.’||fl.segment5||’.’||fl.segment6||’.’||fl.segment7 from fa_locations fl,fa_distribution_history dh where fl.location_id=dh.location_id and adt.asset_id = dh.asset_id) “Asset Location”,
bks.life_in_months,
bks.prorate_convention_code,
bks.prorate_date,
bks.deprn_method_code,
bks.depreciate_flag,
dn.deprn_amount AS “MTD DEPRECIATION”
FROM
fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_books bks,
fa_deprn_summary dn,
fa_deprn_periods fp
WHERE
fp.book_type_code =:BOOK_NAME
and dn.book_type_code =:BOOK_NAME
and dn.period_counter =
( select dp.period_counter from fa_deprn_periods dp where dp.book_type_code =:BOOK_NAME
and dp.period_counter =
( select max(dpz.period_counter) from fa_deprn_summary dsz, fa_deprn_periods dpz
where dpz.book_type_code =:BOOK_NAME
and dpz.period_counter <=fp.period_counter
and dsz.book_type_code =:BOOK_NAME
and dsz.period_counter = dpz.period_counter
and dsz.asset_id = dn.asset_id ))
and bks.book_type_code =:BOOK_NAME
and bks.asset_id = dn.asset_id
and nvl(bks.period_counter_fully_retired, fp.period_counter) in
( select dpy.period_counter
from fa_deprn_periods dpy
where dpy.book_type_code =:BOOK_NAME
and dpy.fiscal_year = fp.fiscal_year)
and adt.asset_id = dn.asset_id
and adt.ASSET_CATEGORY_ID = cat.category_id
and fp.period_name =:PERIOD_NAME
and ah.asset_id = adt.asset_id
and bks.transaction_header_id_out is null
GROUP BY
fp.period_name,
adt.asset_number,
adt.tag_number,
ltrim(rtrim(cat.segment1))||’-‘||ltrim(rtrim(cat.segment2))||’-‘||ltrim(rtrim(cat.segment3)),
adt.description,
bks.date_placed_in_service,
bks.original_cost,
adt.context,
adt.attribute1,
dn.ytd_deprn,
dn.deprn_reserve,
bks.life_in_months,
bks.life_in_months,
bks.prorate_convention_code,
bks.depreciate_flag,
bks.deprn_method_code,
bks.prorate_date,
dn.deprn_amount,
bks.book_type_code,
adt.asset_id;
Summary
This Post described the script for extract FA details based on the asset book in Oracle EBS R12.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
