ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 01672CE61F7F5E9BE06362FB0905AA05 SELECT DISTINCT fab.asset_id, fab.asset_number, FAT.DESCRIPTION, fab.current_units, SUM (fdh.units_assigned) units_assigned_count, COUNT (fdh.distribution_id) distribution_count, ROUND ( (fb.life_in_months / 12), 1) lifeyear, TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY') date_placed_in_service, fb.deprn_method_code, fb.cost, fb.original_cost, (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3) CATEGORY_COMBINATION, NVL ( (SELECT SUM (deprn_reserve) FROM fa_deprn_detail FDD, fa_distribution_history fdhi WHERE fdd.asset_id = fab.asset_id AND PERIOD_COUNTER = (SELECT MAX (PERIOD_COUNTER) FROM fa_deprn_detail fddi WHERE ASSET_ID = FDD.ASSET_ID AND fddi.distribution_id = fdd.distribution_id AND fddi.distribution_id = fdhi.distribution_id) AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID), 0) depreciation, ( fb.cost - NVL ( (SELECT SUM (deprn_reserve) FROM fa_deprn_detail FDD, fa_distribution_history fdhi WHERE fdd.asset_id = fab.asset_id AND PERIOD_COUNTER = (SELECT MAX (PERIOD_COUNTER) FROM fa_deprn_detail fddi WHERE ASSET_ID = FDD.ASSET_ID AND fddi.distribution_id = fdd.distribution_id AND fddi.distribution_id = fdhi.distribution_id) AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID), 0)) nbv, FACB.ASSET_COST_ACCT ORIGINALCOSTGL, facb.deprn_reserve_acct accumulateddeprngl, fb.book_type_code, TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY') CREATION_DATE, TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY') purchased_date, fab.creation_date, fcb.category_id, (SELECT DISTINCT LISTAGG (fidv.INVOICE_NUMBER, ';') WITHIN GROUP (ORDER BY fidv.asset_id) OVER (PARTITION BY fidv.asset_id) FROM FA_INVOICE_DETAILS_V fidv WHERE fidv.asset_id = fab.asset_id) INVOICE_NUMBER, (SELECT DISTINCT pol.category_id || ';' || mc.concatenated_segments || ';' || poh.segment1 po_number FROM po_headers_all poh, po_lines_all pol, po_distributions_all pda, gl_code_combinations gcc, mtl_categories_kfv mc, FA_INVOICE_DETAILS_V fidv WHERE poh.po_header_id = pol.po_header_id AND pol.po_line_id = pda.po_line_id AND pol.po_header_id = pda.po_header_id AND gcc.code_combination_id = pda.code_combination_id AND pol.category_id = mc.category_id AND fidv.asset_id = fab.asset_id AND fidv.po_number = poh.segment1 AND ROWNUM = 1) po_details FROM fa_additions_b fab, fa_additions_tl fat, fa_books fb, fa_categories_b fcb, fa_distribution_history fdh, fa_locations fl, fa_category_books facb, fa_asset_keywords fak, fa_add_warranties fad, fa_warranties fw, gl_code_combinations_kfv gcc WHERE 1 = 1 AND fab.asset_id = fat.asset_id AND fab.asset_category_id = fcb.category_id AND fab.asset_id = fb.asset_id AND fab.asset_id = fdh.asset_id AND fdh.location_id = fl.location_id AND fak.code_combination_id(+) = fab.asset_key_ccid AND fad.asset_id(+) = fab.asset_id AND fb.date_ineffective IS NULL AND facb.category_id = fcb.category_id AND fb.book_type_code = 'Your ASSET BOOK' AND facb.book_type_code = fb.book_type_code AND fat.LANGUAGE = 'US' AND FW.WARRANTY_ID(+) = FAD.WARRANTY_ID AND NOT EXISTS (SELECT 1 FROM FA_RETIREMENTS FR WHERE RETIREMENT_ID = FDH.RETIREMENT_ID) AND ( (fdh.retirement_id IS NOT NULL) OR FDH.DATE_INEFFECTIVE IS NULL) AND NOT EXISTS (SELECT 1 FROM (SELECT TRANSACTION_TYPE_CODE FROM (SELECT TRANSACTION_TYPE_CODE FROM FA_TRANSACTION_HISTORY_TRX_V WHERE ASSET_ID = FAB.ASSET_ID ORDER BY TRANSACTION_HEADER_ID DESC) WHERE ROWNUM = 1) WHERE TRANSACTION_TYPE_CODE = 'FULL RETIREMENT') AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID GROUP BY fab.asset_id, fab.asset_number, FAT.DESCRIPTION, fab.current_units, ROUND ( (fb.life_in_months / 12), 1), TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY'), fb.deprn_method_code, fb.original_cost, (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3), FACB.ASSET_COST_ACCT, facb.deprn_reserve_acct, fb.book_type_code, TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY'), TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY'), fab.creation_date, fb.cost, fcb.category_id Y US JH Asset details Asset Details Report for recon 1=1