SQL script to get the Fix Asset for Tax Book

[kinovisques]

Hello FA Gurus

I have the following SQL to get the assets for the Corporate Book, however it does not work for the tax books.

Do you have any SQL that you can share with me to get the tax book asset information or if you can suggest me how to improve the following SQL I will really appreciate.

Thanks
[:)]


SELECT
a.asset_number "Asset Number",
a.asset_id,
a.asset_category_id,
d.deprn_method_code,
d.life_in_months,
a.attribute_category_code,a.context,
D.PERIOD_COUNTER_FULLY_RETIRED "Asset Retired",
D.period_counter_fully_reserved "Assets Fully Reserved",
DECODE(a.asset_type,'CAPITALIZED','Capitalized',a.asset_type) Asset_Type,
DECODE(d.depreciate_flag,'YES','Yes','NO','No',depreciate_flag) deprn_yn,
d.date_placed_in_service DPIS ,
a.attribute_category_code asset_category ,
--a.description DESCRIPTION ,
c.concatenated_segments expense_account ,
c.code_combination_id,
d.cost - z.accumulated_deprn "NBV",
d.cost current_cost ,
d.original_cost ,
z.ytd_deprn YTD_DEPR ,
z.accumulated_deprn DEPRN_RESERVE ,
a.current_units Units ,
e.location_id,
e.segment1 COUNTRY , --LOCATION FLEXFIELD
e.segment2 STATE ,
e.segment3 CITY ,
e.segment4 FUTURE,
a.manufacturer_name manufacturer,
a.model_number model,
a.serial_number ,
a.tag_number ,
-- not reqd nvl(a.attribute6,'0000000000') ,
DECODE(a.INVENTORIAL,'YES','Yes','NO','No',a.INVENTORIAL) PHYSICAL_INVENTORY,
f.segment1 Asset_Key ,
NULL ASSET_KEY_FUTURE,
DECODE(a.PROPERTY_TYPE_CODE,'PERSONAL','Personal',a.PROPERTY_TYPE_CODE) PROPERTY_TYPE ,
A.PROPERTY_1245_1250_CODE PROPERTY_CLASS
FROM
apps.fa_additions_b a,
apps.fa_distribution_history b,
apps.gl_code_combinations_kfv c,
apps.fa_books d,
apps.fa_locations e,
apps.fa_asset_keywords f,
(SELECT a.asset_id,
a.ytd_deprn,
a.deprn_reserve accumulated_deprn
FROM apps.fa_deprn_summary a,
(SELECT asset_id,
MAX(period_counter) period_counter
FROM apps.fa_deprn_summary
WHERE book_type_code = 'MBB CORPORATE'
GROUP BY asset_id ) b
WHERE a.asset_id = b.asset_id
AND a.period_counter = b.period_counter
AND a.book_type_code = 'MBB CORPORATE') z
WHERE a.asset_id = b.asset_id
AND b.book_type_code = 'MBB CORPORATE'
AND b.code_combination_id = c.code_combination_id
AND b.transaction_header_id_out IS NULL
AND a.asset_id = d.asset_id
AND d.book_type_code = 'MBB CORPORATE'
AND d.transaction_header_id_out IS NULL
AND b.location_id = e.location_id
AND a.asset_id = z.asset_id
AND f.code_combination_id(+) = a.asset_key_ccid
AND D.PERIOD_COUNTER_FULLY_RETIRED IS NULL
--AND D.period_counter_fully_reserved is Null
Order by a.asset_id