ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F1B83869E053B46B63588994 with q_assets as ( select ad.asset_number, ad2.description, ad.tag_number, ad.serial_number, fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_seg', 'OFA', 'CAT#', :p_cat_flex_id, NULL, cat.category_id, 'ALL', 'Y', 'VALUE') asset_category, cat.description category_desc, ad.manufacturer_name, ad.model_number, lookups_pt.meaning property_type, lookups_12.meaning property_class, lookups_nu.meaning new_used, lookups_iu.meaning in_use_flag, lookups_ol.meaning owned_leased, lookups_at.meaning asset_type, p_ad.asset_number parent_asset_number, p_ad2.description parent_asset_desc, ad.current_units total_units, ad.asset_id, bk.book_type_code from fa_asset_history ah, fa_books bk, fa_categories cat, fa_lookups lookups_at, fa_lookups lookups_nu, fa_lookups lookups_ol, fa_lookups lookups_iu, fa_lookups lookups_pt, fa_lookups lookups_12, fa_additions_tl ad2, fa_additions_b ad, fa_additions_tl p_ad2, fa_additions_b p_ad where 1=1 and ad.asset_id = ad2.asset_id and ad2.language = userenv('LANG') and ah.asset_id = ad.asset_id and ah.date_effective <= sysdate and nvl(ah.date_ineffective, sysdate+1) > sysdate and ah.category_id = cat.category_id and bk.asset_id = ad.asset_id and bk.book_type_code = :p_book and bk.date_ineffective is null and -- p_ad.asset_id (+) = ad.parent_asset_id and p_ad2.asset_id (+) = p_ad.asset_id and p_ad2.language (+) = userenv('LANG') and -- lookups_at.lookup_code = ad.asset_type and lookups_at.lookup_type = 'ASSET TYPE' and lookups_nu.lookup_code = ad.new_used and lookups_nu.lookup_type = 'NEWUSE' and lookups_ol.lookup_code = ad.owned_leased and lookups_ol.lookup_type = 'OWNLEASE' and lookups_iu.lookup_code = ad.in_use_flag and lookups_iu.lookup_type = 'YESNO' and lookups_pt.lookup_code(+) = ad.property_type_code and lookups_pt.lookup_type(+) = 'PROPERTY TYPE' and lookups_12.lookup_code(+) = ad.property_1245_1250_code and lookups_12.lookup_type(+) = '1245/1250 PROPERTY' ), q_books as ( select books.asset_id, books.book_type_code book, bc.book_class, trunc(books.date_placed_in_service) date_placed_in_service, books.prorate_convention_code prorate_convention, trunc(books.prorate_date) prorate_date, (cat.number_per_fiscal_year - cap.period_num + 1) months_deprn_in_first_yr, lu_df.meaning depreciate, lu_dwa.meaning depreciate_when_placed_in_serv, trunc(books.deprn_start_date) deprn_start_date, books.deprn_method_code deprn_method, to_char(trunc(books.life_in_months/12) + (mod(books.life_in_months,12)/100), '90D00') life_in_yr_mo, (books.basic_rate * 100) basic_rate_pct, (books.adjusted_rate * 100) adjusted_rate_pct, books.production_capacity capacity, decode(dp_ds.fiscal_year, bc.current_fiscal_year, ds.ytd_production, null) ytd_production, ds.ltd_production ltd_production, books.unit_of_measure unit_of_measure, lu_ly.meaning depreciate_in_last_year, pd2.period_name period_reserved, pd1.period_name period_retired, (itc.itc_amount_rate * 100) itc_rate_pct, books.itc_amount itc_amount, books.itc_basis itc_basis, (itc.basis_reduction_rate * 100) basis_reduction_rate_pct, (itc.basis_reduction_rate * books.original_cost) basis_reduction, nvl(ds.reval_reserve,0) revaluation_reserve, books.ceiling_name ceiling_name, ceil.ceiling_type ceiling_type, books.bonus_rule bonus_rule, books.salvage_value salvage_value, books.rate_adjustment_factor rate_adjustment_factor, books.original_cost original_cost, books.cost cost, books.adjusted_recoverable_cost recoverable_cost, books.adjusted_cost depreciable_basis, (books.cost - ds.deprn_reserve) net_book_value, ds.deprn_reserve deprn_reserve, decode(dp_ds.fiscal_year, bc.current_fiscal_year, ds.ytd_deprn, 0) ytd_deprn from fa_books books, fa_book_controls bc, fa_deprn_periods pd1, fa_deprn_periods pd2, fa_ceiling_types ceil, fa_deprn_summary ds, fa_deprn_periods dp_ds, fa_methods meth, fa_itc_rates itc, fa_convention_types cot, fa_calendar_periods cap, fa_calendar_types cat, fa_lookups lu_df, fa_lookups lu_ly, fa_lookups lu_dwa where :p_incl_book = 'Y' and books.book_type_code = bc.book_type_code and books.date_ineffective is null and bc.book_class in ('CORPORATE', 'TAX') and pd1.book_type_code(+) = books.book_type_code and pd2.book_type_code(+) = books.book_type_code and pd1.period_counter(+) = nvl(books.period_counter_fully_retired,0) and pd2.period_counter(+) = nvl(books.period_counter_fully_reserved,0) and ceil.ceiling_name(+) = books.ceiling_name and ds.asset_id = books.asset_id and ds.book_type_code = books.book_type_code and ds.period_counter = (select max(ds1.period_counter) from fa_deprn_summary ds1 where ds1.asset_id=ds.asset_id and ds1.book_type_code= ds.book_type_code) and dp_ds.period_counter = decode(bc.initial_period_counter, ds.period_counter, ds.period_counter + 1, ds.period_counter) and dp_ds.book_type_code = books.book_type_code and meth.method_code = books.deprn_method_code and nvl(meth.life_in_months, -1) = nvl(books.life_in_months, -1) and itc.itc_amount_id(+) = books.itc_amount_id and cot.prorate_convention_code = books.prorate_convention_code and cap.calendar_type = bc.prorate_calendar and books.prorate_date between cap.start_date and cap.end_date and cap.calendar_type = cat.calendar_type and lu_df.lookup_code = books.depreciate_flag and lu_df.lookup_type = 'YESNO' and lu_ly.lookup_code = meth.depreciate_lastyear_flag and lu_ly.lookup_type = 'YESNO' and lu_dwa.lookup_code = cot.depr_when_acquired_flag and lu_dwa.lookup_type = 'YESNO' ), d_distributions as ( select dh.asset_id asset_id, bc.book_type_code, dh.book_type_code book, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, null, cc.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', :p_loc_flex_id, null, loc.location_id, 'ALL', 'Y', 'VALUE') location, emp.employee_number employee_number, emp.full_name employee_name, dh.units_assigned assigned_units from fa_locations loc, gl_code_combinations cc, per_all_people_f emp, fa_distribution_history dh, fa_book_controls bc where dh.code_combination_id = cc.code_combination_id and dh.assigned_to is not null and dh.assigned_to = emp.person_id and dh.date_effective between emp.effective_start_date and emp.effective_end_date and dh.location_id = loc.location_id and dh.date_ineffective is null and dh.book_type_code = bc.distribution_source_book union all select dh.asset_id asset_id, bc.book_type_code, dh.book_type_code book, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, null, cc.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', :p_loc_flex_id, null, loc.location_id, 'ALL', 'Y', 'VALUE')location, null, null, dh.units_assigned distribution_units from fa_locations loc, gl_code_combinations cc, fa_distribution_history dh, fa_book_controls bc where dh.code_combination_id = cc.code_combination_id and dh.assigned_to is null and dh.location_id = loc.location_id and dh.date_ineffective is null and dh.book_type_code = bc.distribution_source_book union all select dh.asset_id asset_id, bc.book_type_code, dh.book_type_code book, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, null, cc.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', :p_loc_flex_id, null, loc.location_id, 'ALL', 'Y', 'VALUE') location, null, null, dh.units_assigned distribution_units from fa_locations loc, gl_code_combinations cc, fa_distribution_history dh, fa_book_controls bc where dh.code_combination_id = cc.code_combination_id and dh.assigned_to is not null and dh.location_id = loc.location_id and dh.date_ineffective is null and dh.book_type_code = bc.distribution_source_book and not exists (select employee_id from fa_employees where employee_id = dh.assigned_to) ), q_invoices as ( select ai.asset_id asset_id, ai.invoice_number, ai.invoice_line_number||' - '||ai.ap_distribution_line_number invoice_line_number, ai.description invoice_descripton, po.segment1 vendor_number, po.vendor_name, ai.po_number from fa_asset_invoices ai, po_vendors po where ai.po_vendor_id = po.vendor_id(+) and ai.date_ineffective is null ) -- -- Main Query Starts Here -- select x.* from ( -- -- Q1 Asset Books -- select /*+ push_pred(books) */ assets.asset_number, assets.description, assets.tag_number, assets.serial_number, assets.asset_category, assets.category_desc, assets.manufacturer_name, assets.model_number, assets.property_type, assets.property_class, assets.new_used, assets.in_use_flag, assets.owned_leased, assets.asset_type, assets.parent_asset_number, assets.parent_asset_desc, assets.total_units, -- 'Book' record_type, -- Books books.book, books.book_class, books.date_placed_in_service, books.prorate_convention, books.prorate_date, books.months_deprn_in_first_yr, books.depreciate, books.depreciate_when_placed_in_serv, books.deprn_start_date, books.deprn_method, books.life_in_yr_mo, books.basic_rate_pct, books.adjusted_rate_pct, books.capacity, books.ytd_production, books.ltd_production, books.unit_of_measure, books.depreciate_in_last_year, books.period_reserved, books.period_retired, books.itc_rate_pct, books.itc_amount, books.itc_basis, books.basis_reduction_rate_pct, books.basis_reduction, books.revaluation_reserve, books.ceiling_name, books.ceiling_type, books.bonus_rule, books.salvage_value, books.rate_adjustment_factor, books.original_cost, books.cost, books.recoverable_cost, books.depreciable_basis, books.net_book_value, books.deprn_reserve, books.ytd_deprn, -- Distributions null gl_account, null location, null employee_number, null employee_name, to_number(null) assigned_units, -- Invoices null invoice_number, null invoice_line_number, null invoice_descripton, null vendor_number, null vendor_name, null po_number from q_assets assets, q_books books where (:p_incl_book = 'Y' or (:p_incl_book = 'N' and :p_incl_dist = 'N' and :p_incl_inv = 'N')) and assets.asset_id = books.asset_id (+) union all -- -- Q2 Asset Distributions -- select /*+ push_pred(dist) */ assets.asset_number, assets.description, assets.tag_number, assets.serial_number, assets.asset_category, assets.category_desc, assets.manufacturer_name, assets.model_number, assets.property_type, assets.property_class, assets.new_used, assets.in_use_flag, assets.owned_leased, assets.asset_type, assets.parent_asset_number, assets.parent_asset_desc, assets.total_units, -- 'Distribution' record_type, -- Books dist.book, null book_class, to_date(null) date_placed_in_service, null prorate_convention, to_date(null) prorate_date, to_number(null) months_deprn_in_first_yr, null depreciate, null depreciate_when_placed_in_serv, to_date(null) deprn_start_date, null deprn_method, null life_in_yr_mo, to_number(null) basic_rate_pct, to_number(null) adjusted_rate_pct, to_number(null) capacity, to_number(null) ytd_production, to_number(null) ltd_production, null unit_of_measure, null depreciate_in_last_year, null period_reserved, null period_retired, to_number(null) itc_rate_pct, to_number(null) itc_amount, to_number(null) itc_basis, to_number(null) basis_reduction_rate_pct, to_number(null) basis_reduction, to_number(null) revaluation_reserve, null ceiling_name, null ceiling_type, null bonus_rule, to_number(null) salvage_value, to_number(null) rate_adjustment_factor, to_number(null) original_cost, to_number(null) cost, to_number(null) recoverable_cost, to_number(null) depreciable_basis, to_number(null) net_book_value, to_number(null) deprn_reserve, to_number(null) ytd_deprn, -- Distributions dist.gl_account, dist.location, dist.employee_number, dist.employee_name, dist.assigned_units, -- Invoices null invoice_number, null invoice_line_number, null invoice_descripton, null vendor_number, null vendor_name, null po_number from q_assets assets, d_distributions dist where :p_incl_dist = 'Y' and assets.asset_id = dist.asset_id and assets.book_type_code = dist.book_type_code union all -- -- Q3 Asset Invoices -- select /*+ push_pred(inv) */ assets.asset_number, assets.description, assets.tag_number, assets.serial_number, assets.asset_category, assets.category_desc, assets.manufacturer_name, assets.model_number, assets.property_type, assets.property_class, assets.new_used, assets.in_use_flag, assets.owned_leased, assets.asset_type, assets.parent_asset_number, assets.parent_asset_desc, assets.total_units, -- 'Invoice' record_type, -- Books null book, null book_class, to_date(null) date_placed_in_service, null prorate_convention, to_date(null) prorate_date, to_number(null) months_deprn_in_first_yr, null depreciate, null depreciate_when_placed_in_serv, to_date(null) deprn_start_date, null deprn_method, null life_in_yr_mo, to_number(null) basic_rate_pct, to_number(null) adjusted_rate_pct, to_number(null) capacity, to_number(null) ytd_production, to_number(null) ltd_production, null unit_of_measure, null depreciate_in_last_year, null period_reserved, null period_retired, to_number(null) itc_rate_pct, to_number(null) itc_amount, to_number(null) itc_basis, to_number(null) basis_reduction_rate_pct, to_number(null) basis_reduction, to_number(null) revaluation_reserve, null ceiling_name, null ceiling_type, null bonus_rule, to_number(null) salvage_value, to_number(null) rate_adjustment_factor, to_number(null) original_cost, to_number(null) cost, to_number(null) recoverable_cost, to_number(null) depreciable_basis, to_number(null) net_book_value, to_number(null) deprn_reserve, to_number(null) ytd_deprn, -- Distributions null gl_account, null location, null employee_number, null employee_name, to_number(null) assigned_units, -- Invoice inv.invoice_number, inv.invoice_line_number, inv.invoice_descripton, inv.vendor_number, inv.vendor_name, inv.po_number from q_assets assets, q_invoices inv where :p_incl_inv = 'Y' and assets.asset_id = inv.asset_id ) x order by x.asset_number, decode(x.record_type,'Book',1,'Distribution',2,3), x.book_class, x.book, x.gl_account, x.location, x.employee_name, x.invoice_number, x.invoice_line_number Corrected ORA-00972: identifier is too long on 11g databases OFA FAS600_XML #,##0.00;[Red]-#,##0.00 AR FA تقرير مسجل الأصل (بتنسيق XML) - غير مدعم: محجوز للاستخدام الآجل Application: الأصول Source: تقرير مسجل الأصل (بتنسيق XML) - غير مدعم: محجوز للاستخدام الآجل Short Name: FAS600_XML DB package: FA_FAS600_XMLP_PKG D FA Anlagenregister- Nicht unterstützt: Reserviert für zukünftige Verwendung Application: Anlagen Source: Anlagenregister (XML) - Nicht unterstützt: Reserviert für zukünftige Verwendung Short Name: FAS600_XML DB package: FA_FAS600_XMLP_PKG F FA Etat du registre des immobilisations- Non pris en charge : réservé pour une utilisation future Application: Assets Source: Etat du registre des immobilisations (XML) - Non pris en charge : réservé pour une utilisation future Short Name: FAS600_XML DB package: FA_FAS600_XMLP_PKG US FA Asset Register Application: Assets Source: Asset Register Report (XML) Short Name: FAS600_XML DB package: FA_FAS600_XMLP_PKG ZHS FA 资产登记报表- 不支持：已保留供将来使用 Application: 资产 Source: 资产登记报表 (XML) - 不支持：已保留供将来使用 Short Name: FAS600_XML DB package: FA_FAS600_XMLP_PKG Enginatics R12 only 1=1 :accounting_flex_structure :acct_bal_aprompt :acct_cc_aprompt :book_class :cat_maj_aprompt :currency_code :current_fiscal_year :distribution_source_book :p_book :p_cat_flex_id :p_conc_request_id :p_end_asset :p_incl_book :p_incl_dist :p_incl_inv :p_loc_flex_id :p_min_precision :p_start_asset :rp_company_name :rp_maj_lprompt :rp_report_name 1 -20 :p_loc_flex_id Number select location_flex_structure from fa_system_controls US Location Flex ID 2 -10 :p_cat_flex_id Number select category_flex_structure from fa_system_controls US Category Flex ID 3 10 :p_book LOV Oracle FA_CORP_BOOK Y select book_type_code id, book_type_code value, book_type_name description from fa_book_controls_sec where book_class='CORPORATE' order by book_type_code Y AR الدفتر D AfA-Buch F Livre US Book ZHS 帐簿 4 20 1=1 ad.asset_number >= :p_start_asset LOV Oracle FA_CORP_ASSET_RANGE Y select asset_number id, asset_number value, null description from fa_additions fad where exists (select asset_id from fa_books fab where book_type_code=:$flex$.fa_corp_book and fab.asset_id=fad.asset_id and fab.date_ineffective is null) order by value,description AR من رقم الأصل D Von Anlagennummer F Du numéro d'immobilisation US From Asset Number ZHS 起始资产编号 5 30 1=1 ad.asset_number <= :p_end_asset LOV Oracle FA_CORP_ASSET_RANGE_TO Y select asset_number id, asset_number value, null description from fa_additions fad where exists (select asset_id from fa_books fab where book_type_code=:$flex$.fa_corp_book and fab.asset_id=fad.asset_id and fab.date_ineffective is null and fad.asset_number>=:$flex$.fa_corp_asset_range) order by value,description AR إلى رقم الأصل D Bis Anlagennummer F Au numéro d'immobilisation US To Asset Number ZHS 终止资产编号 6 40 :p_incl_book LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Show Deprn Book Details 7 50 :p_incl_dist LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Show Distribution Details 8 60 :p_incl_inv LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Show Invoice Details :$flex$.fa_corp_asset_range From Asset Number To Asset Number :$flex$.fa_corp_book Book From Asset Number :$flex$.fa_corp_book Book To Asset Number Asset Deprn Book Details SYSADMIN 31 ADJUSTED_RATE_PCT 5 ASSET_CATEGORY 1 ASSET_NUMBER 14 ASSET_TYPE 30 BASIC_RATE_PCT 43 BASIS_REDUCTION 42 BASIS_REDUCTION_RATE_PCT 47 BONUS_RULE 19 BOOK 20 BOOK_CLASS 32 CAPACITY 6 CATEGORY_DESC 45 CEILING_NAME 46 CEILING_TYPE 51 COST 21 DATE_PLACED_IN_SERVICE 53 DEPRECIABLE_BASIS 25 DEPRECIATE 36 DEPRECIATE_IN_LAST_YEAR 26 DEPRECIATE_WHEN_PACED_IN_SERVICE 28 DEPRN_METHOD 55 DEPRN_RESERVE 27 DEPRN_START_DATE 2 DESCRIPTION 12 IN_USE_FLAG 40 ITC_AMOUNT 41 ITC_BASIS 39 ITC_RATE_PCT 29 LIFE_IN_YR_MO 34 LTD_PRODUCTION 7 MANUFACTURER_NAME 8 MODEL_NUMBER 24 MONTHS_DEPRN_IN_FIRST_YR 54 NET_BOOK_VALUE 11 NEW_USED 50 ORIGINAL_COST 13 OWNED_LEASED 16 PARENT_ASSET_DESC 15 PARENT_ASSET_NUMBER 37 PERIOD_RESERVED 38 PERIOD_RETIRED 10 PROPERTY_CLASS 9 PROPERTY_TYPE 22 PRORATE_CONVENTION 23 PRORATE_DATE 49 RATE_ADJUSTMENT_FACTOR 18 RECORD_TYPE 52 RECOVERABLE_COST 44 REVALUATION_RESERVE 48 SALVAGE_VALUE 4 SERIAL_NUMBER 3 TAG_NUMBER 17 TOTAL_UNITS 35 UNIT_OF_MEASURE 56 YTD_DEPRN 33 YTD_PRODUCTION Show Distribution Details N Show Invoice Details N S Site Asset Distribution Details SYSADMIN 5 ASSET_CATEGORY 1 ASSET_NUMBER 14 ASSET_TYPE 24 ASSIGNED_UNITS 19 BOOK 6 CATEGORY_DESC 2 DESCRIPTION 23 EMPLOYEE_NAME 22 EMPLOYEE_NUMBER 20 GL_ACCOUNT 12 IN_USE_FLAG 21 LOCATION 7 MANUFACTURER_NAME 8 MODEL_NUMBER 11 NEW_USED 13 OWNED_LEASED 16 PARENT_ASSET_DESC 15 PARENT_ASSET_NUMBER 10 PROPERTY_CLASS 9 PROPERTY_TYPE 18 RECORD_TYPE 4 SERIAL_NUMBER 3 TAG_NUMBER 17 TOTAL_UNITS Show Deprn Book Details N Show Invoice Details N S Site Asset Invoice Details SYSADMIN 5 ASSET_CATEGORY 1 ASSET_NUMBER 14 ASSET_TYPE 6 CATEGORY_DESC 2 DESCRIPTION 21 INVOICE_DESCRIPTON 20 INVOICE_LINE_NUMBER 19 INVOICE_NUMBER 12 IN_USE_FLAG 7 MANUFACTURER_NAME 8 MODEL_NUMBER 11 NEW_USED 13 OWNED_LEASED 16 PARENT_ASSET_DESC 15 PARENT_ASSET_NUMBER 24 PO_NUMBER 10 PROPERTY_CLASS 9 PROPERTY_TYPE 18 RECORD_TYPE 4 SERIAL_NUMBER 3 TAG_NUMBER 17 TOTAL_UNITS 23 VENDOR_NAME 22 VENDOR_NUMBER Show Deprn Book Details N Show Distribution Details N S Site