Query to Get Asset Details

SELECT

fa_additions.asset_id,

disc_gl_set_of_books.set_of_books_id,

disc_gl_set_of_books.description AS set_of_books,

disc_gl_set_of_books.set_of_book_currency,

fa_additions.asset_number, fa_additions.tag_number,

fa_additions.description, fa_additions.manufacturer_name,

fa_additions.serial_number, fa_additions.model_number,

fa_categories_b.segment1, fa_categories_b.segment2,

fa_categories_b.segment3, fa_books.book_type_code,

ROUND ( fa_books.COST * fa_distribution_history.units_assigned / fa_additions.current_units,0) COST,

fa_books.date_effective, fa_books.date_ineffective,

fa_books.date_placed_in_service, fa_books.deprn_start_date,

fa_books.original_cost, fa_additions.current_units,

fa_distribution_history.units_assigned, fa_books.life_in_months,

fa_employees.employee_number, fa_employees.NAME employee_name,

fa_locations.segment1 "STATE", fa_locations.segment2 "CITY",

fa_locations.segment3 "SITE", fa_locations.segment4 "LOCATOR",

fa_category_books.asset_cost_acct "ASSET_ACCOUNT_ID",

segment2.description AS "ASSET_ACCOUNT",

fa_book_controls.book_class, disc_ccid_dsc_mv.account_type,

disc_ccid_dsc_mv.code_combination_id, disc_ccid_dsc_mv.gl_seg1,

disc_ccid_dsc_mv.gl_seg2, disc_ccid_dsc_mv.gl_seg3,

disc_ccid_dsc_mv.gl_seg4, disc_ccid_dsc_mv.gl_seg5,

disc_ccid_dsc_mv.gl_seg6, disc_ccid_dsc_mv.gl_seg7,

disc_ccid_dsc_mv.gl_seg8, disc_ccid_dsc_mv.gl_seg9,

disc_ccid_dsc_mv.gl_seg10, disc_ccid_dsc_mv.gl_seg11,

disc_ccid_dsc_mv.gl_name_seg1, disc_ccid_dsc_mv.gl_name_seg2,

disc_ccid_dsc_mv.gl_name_seg3, disc_ccid_dsc_mv.gl_name_seg4,

disc_ccid_dsc_mv.gl_name_seg5, disc_ccid_dsc_mv.gl_name_seg6,

disc_ccid_dsc_mv.gl_name_seg7, disc_ccid_dsc_mv.gl_name_seg8,

disc_ccid_dsc_mv.gl_name_seg9, disc_ccid_dsc_mv.gl_name_seg10,

disc_ccid_dsc_mv.gl_name_seg11,

fa_books.date_placed_in_service "DATE_IN",

--TO_DATE(FA_DISTRIBUTION_HISTORY.DATE_INEFFECTIVE,'DD-MON-RRRR') "DATE_INEFFECTIVE",

--FA_BOOKS.DATE_INEFFECTIVE

NULL "DATE_IN_MONTH", fa_deprn_summary.deprn_amount,

fa_deprn_summary.ytd_deprn, fa_deprn_summary.deprn_reserve,

fa_deprn_summary.deprn_source_code,

fa_deprn_summary.addition_cost_to_clear adjusted_cost,

fa_deprn_periods.period_name

-- disc_fa_invoice_details.vendor_name,

-- disc_fa_invoice_details.invoice_number,

-- disc_fa_invoice_details.invoice_date,

-- disc_fa_invoice_details.fixed_assets_cost AS fa_cost_by_invoice

FROM

apps.fa_additions fa_additions,

fa.fa_book_controls fa_book_controls,

fa.fa_books fa_books,

fa.fa_categories_b fa_categories_b,

fa.fa_category_books fa_category_books,

fa.fa_distribution_history fa_distribution_history,

gl.gl_code_combinations gl_code_combinations,

apps.fa_employees fa_employees,

fa.fa_locations fa_locations,

apps.disc_ccid_dsc_mv disc_ccid_dsc_mv,

apps.disc_segment2 segment2,

apps.disc_gl_set_of_books disc_gl_set_of_books,

-- disc_gl_month in_month,

fa.fa_deprn_detail fa_deprn_summary,

fa.fa_deprn_periods fa_deprn_periods

-- disc_fa_invoice_details disc_fa_invoice_details

WHERE fa_books.book_type_code = fa_book_controls.book_type_code

AND fa_books.asset_id = fa_additions.asset_id

AND fa_books.date_ineffective IS NULL

AND fa_distribution_history.book_type_code = fa_books.book_type_code

AND fa_distribution_history.asset_id = fa_additions.asset_id

AND fa_distribution_history.date_ineffective IS NULL

AND fa_categories_b.category_id = fa_additions.asset_category_id

AND fa_category_books.book_type_code = fa_distribution_history.book_type_code

AND fa_category_books.category_id = fa_additions.asset_category_id

AND gl_code_combinations.code_combination_id = fa_distribution_history.code_combination_id

AND fa_employees.employee_id(+) = fa_distribution_history.assigned_to

AND fa_locations.location_id = fa_distribution_history.location_id

AND gl_code_combinations.code_combination_id = disc_ccid_dsc_mv.code_combination_id

AND segment2.SEGMENT = fa_category_books.asset_cost_acct

AND disc_gl_set_of_books.set_of_books_id = fa_book_controls.set_of_books_id

-- AND fa_books.date_placed_in_service BETWEEN in_month.start_date AND in_month.end_date

AND fa_deprn_summary.asset_id = fa_distribution_history.asset_id

AND ( fa_deprn_periods.period_counter = fa_deprn_summary.period_counter AND fa_deprn_periods.book_type_code = fa_deprn_summary.book_type_code )

AND fa_deprn_summary.distribution_id = fa_distribution_history.distribution_id

-- AND fa_books.asset_id = disc_fa_invoice_details.asset_id(+)

-- AND fa_deprn_summary.deprn_source_code = 'D'

--and fa_additions.asset_id = '10000767';

/