ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 92DADAD6B555A363E053BB6B6358D65BY select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, br.resource_code, br.description resource_description, cce.cost_element, crc.cost_type, gl.currency_code, crc.resource_rate, xxen_util.meaning(br.resource_type,'BOM_RESOURCE_TYPE',700) resource_type, xxen_util.meaning(br.autocharge_type,'BOM_AUTOCHARGE_TYPE',700) charge_type, xxen_util.yes(decode(br.allow_costs_flag,1,'Y')) allow_costs, br.unit_of_measure uom_code, xxen_util.yes(decode(br.standard_rate_flag,1,'Y')) standard_rate, msiv.concatenated_segments osp_item_number, msiv.description osp_item_description, (select ca.activity from cst_activities ca where br.default_activity_id=ca.activity_id) activity, &segment_columns gcc2.concatenated_segments absorption_account, gcc1.concatenated_segments variance_account, br.disable_date, br.creation_date, xxen_util.user_name(br.created_by) created_by, br.last_update_date, xxen_util.user_name(br.last_updated_by) last_updated_by from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, bom_resources br, ( select crc.resource_id, cct.cost_type, crc.resource_rate from cst_resource_costs crc, cst_cost_types cct where 1=1 and crc.cost_type_id=cct.cost_type_id ) crc, cst_cost_elements cce, mtl_system_items_vl msiv, gl_code_combinations_kfv gcc1, gl_code_combinations_kfv gcc2 where 2=2 and mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and sysdate<nvl(haou.date_to,sysdate+1) and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=br.organization_id and br.resource_id=crc.resource_id(+) and (br.allow_costs_flag=1 and crc.resource_id is not null or br.allow_costs_flag=2 and 3=3) and br.cost_element_id=cce.cost_element_id(+) and br.organization_id=msiv.organization_id(+) and br.purchase_item_id=msiv.inventory_item_id(+) and br.rate_variance_account=gcc1.code_combination_id(+) and br.absorption_account=gcc2.code_combination_id(+) order by ledger, operating_unit, org_code, br.resource_code Rebuilt to the Blitz Report SQL standards: resources without a resource type or charge type are no longer missing, non-costed resources show their cost element, accounts and currency, flags show Yes or blank, Created By and Last Updated By show the user name with the person. Setup report without organization security, so that every user sees the setup of all organizations. #,##0.00000;[Red](#,##0.00000) US CAC Resource Costs Report to show the resource and outside processing costs by organization and cost type. Parameters: Cost Type: enter the cost type you wish to report, defaults to your Costing Method (mandatory). Include Non-Costed Resources: choose No to exclude non-costed resources, choose Yes to include them. You normally use non-costed resources for scheduling purposes (mandatory). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2006-2023 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is -- | acknowledged. No warranties, express or otherwise is included in this permission. -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: xxx_resource_costs_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 18 Nov 2010 Douglas Volz Initial Coding -- | 1.1 15 Dec 2010 Douglas Volz Final Coding -- | 1.2 28 Dec 2010 Douglas Volz Removed condition to omit Orgs 108, 109, 110 -- | and 331 as these may be used at any time -- | 1.3 18 Nov 2015 Douglas Volz Removed organization restrictions -- | 1.4 15 Nov 2016 Douglas Volz Added DFF for Resource_Type, changed -- | cancatenated segments to separate columns. -- | 1.5 18 Nov 2016 Douglas Volz Added Activity to this report -- | 1.6 16 Feb 2017 Douglas Volz Added Creation_Date and Who Created -- | 1.7 17 Feb 2017 Douglas Volz Add Resource_Description -- | 1.8 18 Sep 2019 Douglas Volz Added last update by and last update date -- | 1.9 27 Jan 2020 Douglas Volz Changed to multi-language views for the item -- | master, inventory orgs and operating units. -- | 1.10 21 Jul 2021 Douglas Volz Add Basis Type, UOM Code and changed Allow -- | Costs and Std Rate to lookup codes. -- | 1.11 11 Oct 2023 Douglas Volz Modified for invalid created by, invalid -- | last updated by and invalid code_combination_id -- | values, to ensure resources are reported. -- | Removed tabs and added inventory org access controls. -- | Reversed revision 1.4, added back concatentated -- | account number values. -- | 1.12 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &segment_columns 1=1 2=2 3=3 1 -40 &segment_columns with x as ( select fifsv.application_column_name, fifsv.form_left_prompt from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num ) select 'gcc2.'||lower(x.application_column_name)||' "Absorptn '||substr(x.form_left_prompt,1,21)||'",' text from x union all select 'gcc1.'||lower(x.application_column_name)||' "Var '||substr(x.form_left_prompt,1,21)||'",' text from x LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV'))) and rownum=1 Y US Chart of Accounts 2 10 1=1 cct.cost_type=:p_cost_type LOV CST Cost Type (No ICP PII) A486BD05719B2A82E053BB6B6358FC8E select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') order by cct.cost_type select cct.cost_type value, cct.description from cst_cost_types cct, mtl_parameters mp where nvl(cct.disable_date,sysdate + 1) > sysdate and cct.organization_id is null and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id) and (mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or (fnd_profile.value('MFG_ORGANIZATION_ID') is null and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id)) ) and rownum < 2 group by cct.cost_type, cct.description order by cct.cost_type Y US Cost Type 3 20 3=3 :p_include_non_costed_res='Y' LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y US Include Non-Costed Resources 4 30 2=2 mp.organization_code=:p_org_code LOV INV Organization Code (all) 8E2FF36EDEA279D2E0530100007F1FF2 select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 5 40 2=2 haouv_ou.name=:p_operating_unit LOV HR Operating Unit (all) 8E2FF36EDE8879D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) order by hou.name US Operating Unit 6 50 2=2 gl.name=:p_ledger LOV GL Ledger (all) A95C471001B20DD1E0530100007FBF83 select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and gl.object_type_code='L' and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'NONE',1,'PRIMARY',2,'SECONDARY',3,'ALC',4), gl.name US Ledger