Sunday, March 20, 2016

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


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect