ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A3BFC166CC5C7F77E053BB6B6358CAD3Y select nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, mp.organization_code Org_Code, cct.cost_type Cost_Type, bd.department_code Department, (select ca.activity from cst_activities ca where ca.activity_id = br.default_activity_id) Activity, br.resource_code Resource_Code, cec.cost_element Cost_Element, br.description Description, gl.currency_code Currency_Code, nvl(crc.resource_rate,0) Resource_Rate, ml1.meaning Resource_Type from bom_departments bd, bom_resources br, bom_department_resources bdr, cst_resource_costs crc, cst_cost_types cct, -- Revision for version 1.2 cst_cost_types cct2, -- Avg Rates Cost Type cst_cost_elements cec, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, mfg_lookups ml1 where gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and 1=1 -- p_org_code, p_operating_unit, p_ledger, p_cost_type and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id = br.organization_id and bd.organization_id = br.organization_id and bdr.department_id = bd.department_id and bdr.resource_id = br.resource_id and crc.resource_id = br.resource_id and crc.organization_id = mp.organization_id and crc.cost_type_id = cct.cost_type_id -- Revision for version 1.2 and cct2.cost_type_id = nvl(mp.avg_rates_cost_type_id, mp.primary_cost_method) and cct.cost_type = nvl(:p_cost_type, cct2.cost_type) -- p_cost_type -- End revision for version 1.2 and br.cost_element_id = cec.cost_element_id and br.allow_costs_flag = 1 -- Yes -- =========================================== -- Joins for the lookup codes -- =========================================== and ml1.lookup_type = 'BOM_RESOURCE_TYPE' and ml1.lookup_code = br.resource_type -- =========================================== -- Organization joins to the HR org model -- =========================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id order by gl.short_name, -- Ledger haou.name, -- Operating Unit mp.organization_code, -- Org Code cct.cost_type, -- Cost Type bd.department_code -- Department Added operating unit security 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 -- +=============================================================================+*/ Enginatics 1=1 :p_cost_type 1 10 1=1 cct.cost_type = nvl(:p_cost_type, cct2.cost_type) LOV CST Cost Type (No ICP PII) A486BD05719B2A82E053BB6B6358FC8E Y 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 Y 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 ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_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 haou2.name = :p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y 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')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name US Operating Unit 4 40 1=1 gl.name = :p_ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y 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$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) 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,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger