ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A3BFC166CC567F77E053BB6B6358CAD3Y select nvl2(cro.overhead_id,xxen_util.meaning('SET UP','CHECK RANGE STATUS',200),xxen_util.meaning('MISSING','NL_IZA_REJECT_REASON',3)) status, x.ledger, x.operating_unit, x.org_code, x.cost_type, x.resource_code, x.resource_uom, x.cost_element, x.currency_code, x.resource_rate, x.resource_type, x.charge_type, x.resource_basis, x.allow_costs, x.department, nvl(br_ovh.resource_code,xxen_util.meaning('MISSING','NL_IZA_REJECT_REASON',3)) overhead_code, br_ovh.unit_of_measure overhead_uom from ( select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, cct.cost_type_id, cct.cost_type, br.resource_id, br.resource_code, br.unit_of_measure resource_uom, cce.cost_element, gl.currency_code, (select crc.resource_rate from cst_resource_costs crc where br.resource_id=crc.resource_id and cct.cost_type_id=crc.cost_type_id) 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.meaning(br.default_basis_type,'CST_BASIS_SHORT',700) resource_basis, xxen_util.yes(decode(br.allow_costs_flag,1,'Y')) allow_costs, bd.department_code department from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, cst_cost_types cct, bom_resources br, cst_cost_elements cce, bom_department_resources bdr, bom_departments bd where 1=1 and 2=2 and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and (cct.cost_type=:p_cost_type or :p_cost_type is null and cct.cost_type_id=nvl(mp.avg_rates_cost_type_id,mp.primary_cost_method)) and mp.organization_id=br.organization_id and br.cost_element_id=cce.cost_element_id and br.resource_id=bdr.resource_id and bdr.department_id=bd.department_id ) x, cst_resource_overheads cro, bom_resources br_ovh where x.resource_id=cro.resource_id(+) and x.cost_type_id=cro.cost_type_id(+) and cro.overhead_id=br_ovh.resource_id(+) order by status, x.ledger, x.operating_unit, x.org_code, x.cost_type, x.resource_code, x.cost_element, x.allow_costs, x.department, overhead_code Rebuilt to the Blitz Report SQL standards: resources without a basis, resource type or charge type are no longer missing, Allow Costs shows Yes or blank. Setup report without organization security, so that every user sees the setup of all organizations. US CAC Resources Associated with Overheads Setup Report to show which resources are associated with which overheads and which resources are associated with which departments. And find any resources which do not have any overhead associations. If there are no overhead associations the first report column will say "Missing". If the resource/overhead association exists, the first column of the report will say "Set Up". Note: if a resource does not have a cost, the Resource Rate column has a blank or empty value. Parameters: =========== Resource/Overhead Cost Type: enter the cost type you wish to report for your resources and overheads. If left blank, depending on your Costing Method, it defaults to your AvgRates or Frozen Cost Type (optional). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). 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 2016 - 2024 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_res_ovhd_setup.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 11 Nov 2016 Douglas Volz Initial Coding -- | 1.1 16 Jun 2017 Douglas Volz Add check for resources which are not -- | associated with overheads and added the -- | resource cost element. -- | 1.2 17 Jul 2018 Douglas Volz Made Cost Type parameter optional -- | 1.3 16 Jan 2020 Douglas Volz Added org code and operating unit parameters. -- | 1.4 20 Apr 2020 Douglas Volz Make Cost Type default work for all cost methods -- | 1.5 09 Jul 2022 Douglas Volz Changes for multi-language lookup values. -- | 1.6 22 Jan 2024 Douglas Volz Add with statement for cst_resource_costs, overheads -- | were not reported if the resource cost was missing. -- | Added resource "Allow Costs" column. Removed -- | tabs and added inventory access controls. -- | 1.7 31 Jan 2024 Douglas Volz Add Currency Code, Resource Type and Charge Type columns. -- | 1.8 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics 1=1 2=2 :p_cost_type 1 10 2=2 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 US Resource/Overhead Cost Type 2 20 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 3 30 1=1 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 4 40 1=1 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 4742FEED4458541D5247AC8495BD494C Count by Org by Resource Count by Status, Ledger, Operating Unit, Org Code and Resource MFG 14 ALLOW_COSTS 12 CHARGE_TYPE 8 COST_ELEMENT 5 COST_TYPE 9 CURRENCY_CODE 15 DEPARTMENT 2 LEDGER 3 OPERATING_UNIT 4 ORG_CODE 16 OVERHEAD_CODE 17 OVERHEAD_UOM 13 RESOURCE_BASIS 6 RESOURCE_CODE 10 RESOURCE_RATE 11 RESOURCE_TYPE 7 RESOURCE_UOM 1 STATUS COST_TYPE ROW 5 LEDGER ROW 2 OPERATING_UNIT ROW 3 ORG_CODE ROW 4 RESOURCE_CODE VALUE 1 COUNT STATUS ROW 1 S Site 4742FEED4458541D5247AC8495BD494C