Query to Get Asset YTD Depreciation

SELECT fbv.book_type_code

,fab.asset_number,fab.asset_id

,fab.tag_number

,fbv.date_placed_in_service

,fab.description

,fcb.segment1||'.'||fcb.segment2 asset_category

,fcb.segment1||'.'||fcb.segment2 asset_category1

,fbv.cost asset_cost

,fbv.original_cost original_asset_cost

,fbv.life_in_months asset_life

,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id,fbv.book_type_code,fdh.distribution_id,fdp.period_counter,'ACCUM') accum_depreciation

,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id,fbv.book_type_code,fdh.distribution_id,fdp.period_counter,'YTD') ytd_depreciation

,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id,fbv.book_type_code,fdh.distribution_id,fdp.period_counter,'RUN') dep_this_run

,gcc.segment2 department_no

,(SELECT ppx.full_name FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian

,(SELECT ppx.employee_number FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number

,(SELECT po_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number

,(SELECT invoice_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number

,(SELECT vendor_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number

,(SELECT vendor_name FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name

,fab.serial_number

,fl.segment3||'.'||fl.segment4 location_flexfield

,fab.attribute1 tax_major_category

,fab.attribute2 tax_minor_category

,(SELECT retirement_type_code FROM fa_retirements WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type

,(SELECT segment1||'.'||segment2 FROM fa_asset_keywords WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key

,fdp.period_name

,fab.attribute3 acquisition_date

,fab.manufacturer_name

,fbv.retirement_pending_flag

FROM apps.fa_additions_v fab

,apps.fa_books_v fbv

,apps.fa_categories_b fcb

,apps.fa_deprn_periods fdp

,apps.fa_distribution_history fdh

,apps.gl_code_combinations gcc

,apps.fa_locations fl

WHERE 1=1

AND fab.asset_id = fbv.asset_id

AND fcb.category_id = fab.asset_category_id

AND fbv.transaction_header_id_out IS NULL

AND fdp.book_type_code = fbv.book_type_code

AND fdh.asset_id = fbv.asset_id

AND fdh.code_combination_id = gcc.code_combination_id

AND fdh.location_id=fl.location_id

AND fbv.transaction_header_id_out IS NULL

AND fdh.transaction_header_id_out IS NULL