Asset Seelcting Query

SELECT b.book_type_code "Asset Book Type"
, l.segment1 loc1
, l.segment2 loc2
, l.attribute1 "site "
, a.asset_number "Asset Number"
, a.asset_type "Asset Type"
, a.tag_number "Tag Number"
, b.COST "Cost"
, ds.deprn_reserve
, (b.COST - ds.deprn_reserve) net_book_value
, get_account( da.ASSET_COST_ACCOUNT_CCID ) "Asset Cost Account" ,
get_account( da.ASSET_CLEARING_ACCOUNT_CCID ) "Asset Clearing Account" ,
get_account( da.DEPRN_EXPENSE_ACCOUNT_CCID ) "Depreciation Expense Account" ,
get_account( da.DEPRN_RESERVE_ACCOUNT_CCID ) "Depreciation Reserve Account",
get_account( da.CIP_COST_ACCOUNT_CCID ) "CIP Cost Account",
get_account( da.CIP_CLEARING_ACCOUNT_CCID ) "CIP Clearing Account",
get_account( da.NBV_RETIRED_GAIN_CCID ) "Net Book Value Retired Gain",
get_account( da.NBV_RETIRED_LOSS_CCID ) "Net Book Value Retired Loss",
get_account( da.PROCEEDS_SALE_GAIN_CCID ) "Proceeds Sale Gain",
get_account( da.PROCEEDS_SALE_LOSS_CCID ) "Proceeds Sale Loss",
get_account( da.COST_REMOVAL_GAIN_CCID ) "Cost Removal Gain",
get_account( da.COST_REMOVAL_LOSS_CCID ) "Cost Removal Loss",
get_account( da.PROCEEDS_SALE_CLEARING_CCID ) "Proceed Sale Clearing",
get_account( da.COST_REMOVAL_CLEARING_CCID ) "Cost Removal Clearing",
get_account( da.REVAL_RSV_GAIN_ACCOUNT_CCID ) "Revaluation Reserve Ret Gain",
get_account( da.REVAL_RSV_LOSS_ACCOUNT_CCID ) "Revaluation Reserve Ret Loss",
get_account( da.DEFERRED_EXP_ACCOUNT_CCID ) "Deferred Depreciation Expense",
get_account( da.DEFERRED_RSV_ACCOUNT_CCID ) "Deferred Depreciation Reserve",
get_account( da.DEPRN_ADJ_ACCOUNT_CCID ) "Depreciation Adjustment",
get_account( da.REVAL_AMORT_ACCOUNT_CCID ) "Revaluation Amortization",
get_account( da.REVAL_RSV_ACCOUNT_CCID ) "Revaluation Reserve",
get_account( da.BONUS_EXP_ACCOUNT_CCID ) "Bonus Depreciation Expense",
get_account( da.BONUS_RSV_ACCOUNT_CCID ) "Bonus Depreciation Reserve"
FROM fa_additions_b a
, fa_books b
, fa_book_controls c
, fa_distribution_history d
, fa_locations l
, fa_deprn_summary ds
, fa_distribution_accounts da
WHERE a.asset_id = b.asset_id
AND b.book_type_code = c.book_type_code
AND c.set_of_books_id = 2
AND c.book_class = 'CORPORATE'
AND in_use_flag = 'YES'
AND b.date_ineffective IS NULL
AND d.asset_id = a.asset_id
AND l.location_id = d.location_id
AND ds.book_type_code = b.book_type_code
AND ds.asset_id = b.asset_id
AND d.distribution_id = da.distribution_id
AND d.book_type_code = b.book_type_code
AND d.book_type_code = da.book_type_code
AND d.date_effective = (SELECT MAX (date_effective)
FROM fa_distribution_history
WHERE asset_id = d.asset_id
AND book_type_code = d.book_type_code)
AND ds.period_counter = (SELECT MAX (ds1.period_counter)
FROM fa_deprn_summary ds1
WHERE ds1.asset_id = ds.asset_id
AND ds1.book_type_code = ds.book_type_code)
ORDER BY asset_number

-------------------------------------------------------

MC
SELECT b.book_type_code
, l.segment1 loc1
, l.segment2 loc2
, l.attribute1 site
, a.asset_number
, a.asset_type
, a.tag_number
, b.COST
, ds.deprn_reserve
, (b.COST - ds.deprn_reserve) net_book_value
, get_account( da.ASSET_COST_ACCOUNT_CCID ) "Asset Cost Account" ,
get_account( da.ASSET_CLEARING_ACCOUNT_CCID ) "Asset Clearing Account" ,
get_account( da.DEPRN_EXPENSE_ACCOUNT_CCID ) "Depreciation Expense Account" ,
get_account( da.DEPRN_RESERVE_ACCOUNT_CCID ) "Depreciation Reserve Account",
get_account( da.CIP_COST_ACCOUNT_CCID ) "CIP Cost Account",
get_account( da.CIP_CLEARING_ACCOUNT_CCID ) "CIP Clearing Account",
get_account( da.NBV_RETIRED_GAIN_CCID ) "Net Book Value Retired Gain",
get_account( da.NBV_RETIRED_LOSS_CCID ) "Net Book Value Retired Loss",
get_account( da.PROCEEDS_SALE_GAIN_CCID ) "Proceeds Sale Gain",
get_account( da.PROCEEDS_SALE_LOSS_CCID ) "Proceeds Sale Loss",
get_account( da.COST_REMOVAL_GAIN_CCID ) "Cost Removal Gain",
get_account( da.COST_REMOVAL_LOSS_CCID ) "Cost Removal Loss",
get_account( da.PROCEEDS_SALE_CLEARING_CCID ) "Proceed Sale Clearing",
get_account( da.COST_REMOVAL_CLEARING_CCID ) "Cost Removal Clearing",
get_account( da.REVAL_RSV_GAIN_ACCOUNT_CCID ) "Revaluation Reserve Ret Gain",
get_account( da.REVAL_RSV_LOSS_ACCOUNT_CCID ) "Revaluation Reserve Ret Loss",
get_account( da.DEFERRED_EXP_ACCOUNT_CCID ) "Deferred Depreciation Expense",
get_account( da.DEFERRED_RSV_ACCOUNT_CCID ) "Deferred Depreciation Reserve",
get_account( da.DEPRN_ADJ_ACCOUNT_CCID ) "Depreciation Adjustment",
get_account( da.REVAL_AMORT_ACCOUNT_CCID ) "Revaluation Amortization",
get_account( da.REVAL_RSV_ACCOUNT_CCID ) "Revaluation Reserve",
get_account( da.BONUS_EXP_ACCOUNT_CCID ) "Bonus Depreciation Expense",
get_account( da.BONUS_RSV_ACCOUNT_CCID ) "Bonus Depreciation Reserve"
FROM fa_additions_b a
, fa_mc_books b
, fa_mc_book_controls c
, fa_distribution_history d
, fa_locations l
, fa_mc_deprn_summary ds
, fa_distribution_accounts da
WHERE a.asset_id = b.asset_id
AND b.book_type_code = c.book_type_code
AND c.set_of_books_id = 4
--and c.book_class = 'CORPORATE'
AND in_use_flag = 'YES'
AND b.date_ineffective IS NULL
AND d.asset_id = a.asset_id
AND l.location_id = d.location_id
AND ds.book_type_code = b.book_type_code
AND ds.asset_id = b.asset_id
AND d.distribution_id = da.distribution_id
AND d.book_type_code = b.book_type_code
AND d.book_type_code = da.book_type_code
AND d.date_effective = (SELECT MAX (date_effective)
FROM fa_distribution_history
WHERE asset_id = d.asset_id
AND book_type_code = d.book_type_code)
AND ds.period_counter = (SELECT MAX (ds1.period_counter)
FROM fa_mc_deprn_summary ds1
WHERE ds1.asset_id = ds.asset_id
AND ds1.book_type_code = ds.book_type_code)
ORDER BY asset_number