Oracle Applications: FA - Query to Get Fixed Asset Register Values

SELECT dh.asset_id asset_id,

dh.code_combination_id dh_ccid,

APPS_GL_CODE_COMB_UTIL_API.Get_Segment2(dh.code_combination_id) segment2,

cb.deprn_reserve_acct rsv_account,

books.date_placed_in_service start_date,

books.deprn_method_code method,

books.life_in_months life,

books.adjusted_rate rate,

books.production_capacity capacity,

dd_bonus.cost cost,

DECODE (dd_bonus.period_counter, :upc_, dd_bonus.deprn_amount - dd_bonus.bonus_deprn_amount, 0) deprn_amount,

DECODE (SIGN (:tpc_ - dd_bonus.period_counter), 1, 0, dd_bonus.ytd_deprn - dd_bonus.bonus_ytd_deprn) ytd_deprn,

dd_bonus.deprn_reserve - dd_bonus.bonus_deprn_reserve deprn_reserve,

DECODE (th.transaction_type_code, NULL, dh.units_assigned / ah.units * 100) percent,

DECODE (th.transaction_type_code,

NULL, DECODE (th_rt.transaction_type_code, 'FULL RETIREMENT', 'F', DECODE (books.depreciate_flag, 'NO', 'N')),

'TRANSFER', 'T',

'TRANSFER OUT', 'P',

'RECLASS', 'R')

t_type,

dd_bonus.period_counter,

NVL (th.date_effective, :ucd_),

FROM fa_deprn_detail dd_bonus,

fa_asset_history ah,

fa_transaction_headers th,

fa_transaction_headers th_rt,

fa_books books,

fa_distribution_history dh,

fa_category_books cb

WHERE cb.book_type_code = :book_

AND cb.category_id = ah.category_id

AND ah.asset_id = dh.asset_id

AND ah.date_effective < NVL (th.date_effective, :ucd_)

AND NVL (ah.date_ineffective, SYSDATE) >= NVL (th.date_effective, :ucd_)

AND -- AH.ASSET_TYPE = 'CAPITALIZED'

--AND

dd_bonus.book_type_code = :book_

AND dd_bonus.distribution_id = dh.distribution_id

AND dd_bonus.period_counter =

(SELECT MAX (dd_sub.period_counter)

FROM fa_deprn_detail dd_sub

WHERE dd_sub.book_type_code = :book_

AND dd_sub.asset_id = dh.asset_id

AND dd_sub.distribution_id = dh.distribution_id

AND dd_sub.period_counter <= :upc_)

AND th_rt.book_type_code = :book_

AND th_rt.transaction_header_id = books.transaction_header_id_in

AND books.book_type_code = :book_

AND books.asset_id = dh.asset_id

AND NVL (books.period_counter_fully_retired, :upc_) >= :tpc_

AND books.date_effective <= NVL (th.date_effective, :ucd_)

AND NVL (books.date_ineffective, SYSDATE + 1) > NVL (th.date_effective, :ucd_)

AND th.book_type_code(+) = :book_

AND th.transaction_header_id(+) = dh.transaction_header_id_out

AND th.date_effective(+) BETWEEN :tod_ AND :ucd_

AND dh.book_type_code = :book_

AND dh.date_effective <= :ucd_

AND NVL (dh.date_ineffective, SYSDATE) > :tod_

UNION ALL

SELECT dh.asset_id asset_id,

dh.code_combination_id dh_ccid,

APPS_GL_CODE_COMB_UTIL_API.Get_Segment2(dh.code_combination_id) segment2,

cb.bonus_deprn_reserve_acct rsv_account,

books.date_placed_in_service start_date,

books.deprn_method_code method,

books.life_in_months life,

books.adjusted_rate rate,

books.production_capacity capacity,

0 cost,

DECODE (dd.period_counter, :upc_, dd.bonus_deprn_amount, 0) deprn_amount,

DECODE (SIGN (:tpc_ - dd.period_counter), 1, 0, dd.bonus_ytd_deprn) ytd_deprn,

dd.bonus_deprn_reserve deprn_reserve,

0 percent,

'B' t_type,

dd.period_counter,