FA 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 1=1 
and 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'