ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A3BFC166CC5C7F77E053BB6B6358CAD3Y select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, cct.cost_type, bd.department_code department, (select ca.activity from cst_activities ca where br.default_activity_id=ca.activity_id) activity, br.resource_code, cce.cost_element, br.description, gl.currency_code, nvl(crc.resource_rate,0) resource_rate, xxen_util.meaning(br.resource_type,'BOM_RESOURCE_TYPE',700) resource_type from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, cst_cost_types cct_default, bom_resources br, cst_cost_elements cce, bom_department_resources bdr, bom_departments bd, cst_resource_costs crc, cst_cost_types cct where 1=1 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 nvl(mp.avg_rates_cost_type_id,mp.primary_cost_method)=cct_default.cost_type_id and mp.organization_id=br.organization_id and br.allow_costs_flag=1 and br.cost_element_id=cce.cost_element_id and br.resource_id=bdr.resource_id and bdr.department_id=bd.department_id and br.organization_id=bd.organization_id and br.resource_id=crc.resource_id and mp.organization_id=crc.organization_id and crc.cost_type_id=cct.cost_type_id and cct.cost_type=nvl(:p_cost_type,cct_default.cost_type) order by ledger, operating_unit, org_code, cost_type, department Rebuilt to the Blitz Report SQL standards. Operating Unit shows the operating unit instead of the organization name. Setup report without organization security, so that every user sees the setup of all organizations. US CAC Resources by Department Setup Report to show which resources are assigned to which departments. With the respective resource rates as well. /* +=============================================================================+ -- | Copyright 2016 - 2022 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_resources_by_dept.sql -- | -- | Parameters: -- | p_org_code -- Specific inventory organization you wish to report (optional) -- | p_operating_unit -- Operating Unit you wish to report, leave blank for all -- | operating units (optional) -- | p_ledger -- general ledger you wish to report, leave blank for all -- | ledgers (optional) -- | p_cost_type -- Enter Pending or Frozen or AvgRates for the Cost Type. -- | Optional, defaults to your AvgRates or Frozen Cost Type, -- | depending on your Costing Method. -- | Description: -- | Report to show which resources are assigned to which departments. -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 21 Jan 2017 Douglas Volz Initial Coding -- | 1.1 20 Jan 2020 Douglas Volz Add operating unit and org code parameters. -- | 1.2 20 Apr 2020 Douglas Volz Make Cost Type default work for all cost methods -- | 1.3 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics 1=1 :p_cost_type 1 10 1=1 cct.cost_type=nvl(:p_cost_type,cct_default.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 US Resource/Overhead Cost Type 2 20 1=1 mp.organization_code=:p_org_code LOV custom select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_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 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