ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A0462A72D561FBCFE053BB6B635826CDY select 'Overhead Rates Not In Department' Error_Type, -- Revision for version 1.4 cct.cost_type Cost_Type, nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, mp.organization_code Org_Code, bd.department_code Department, -- Revision for version 1.4 bd.department_class_code Department_Class_Code, bro.resource_code Missing_Overhead, brr.resource_code Resource_Code, ml_basis.meaning Overhead_Basis_Type, -- Revision for version 1.1 cce.cost_element Resource_Cost_Element from cst_resource_overheads cro, bom_department_resources bdr, bom_departments bd, bom_resources brr, bom_resources bro, -- Revision for version 1.1 cst_cost_elements cce, cst_cost_types cct, mfg_lookups ml_basis, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl where cro.overhead_id = bro.resource_id and bro.cost_element_id = 5 -- overhead and cro.resource_id = brr.resource_id and cro.resource_id = bdr.resource_id and cct.cost_type_id = cro.cost_type_id and cct.organization_id is null and 2=2 -- p_cost_type and 3=3 -- p_include_osp -- Revision for version 1.1 and cce.cost_element_id = brr.cost_element_id and bd.department_id = bdr.department_id and ml_basis.lookup_code = bro.default_basis_type and ml_basis.lookup_type = 'CST_BASIS_SHORT' and not exists (select cdo.overhead_id from cst_department_overheads cdo, cst_cost_types cct where cdo.overhead_id = cro.overhead_id and cdo.department_id = bdr.department_id and cct.cost_type_id = cdo.cost_type_id and 2=2 -- p_cost_type and cct.organization_id is null ) and mp.organization_id = cro.organization_id -- =========================================== -- 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 and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) 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 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_ledger, p_operating_unit, p_org_code -- Revision for version 1.2 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) union all -- Revision for version 1.4 select 'Overheads Not Set Up with Resources' Error_Type, cct.cost_type Cost_Type, nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, mp.organization_code Org_Code, bd.department_code Department, bd.department_class_code Department_Class_Code, bro.resource_code Missing_Overhead, brr.resource_code Resource_Code, ml_basis.meaning Overhead_Basis_Type, cce.cost_element Resource_Cost_Element from cst_department_overheads cdo, bom_department_resources bdr, bom_departments bd, bom_resources brr, bom_resources bro, cst_cost_elements cce, cst_cost_types cct, mfg_lookups ml_basis, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl where cdo.overhead_id = bro.resource_id and cdo.department_id = bdr.department_id and cdo.basis_type in (3, 4) -- resource units, resource value and bro.cost_element_id = 5 -- overhead and bdr.resource_id = brr.resource_id and brr.cost_element_id in (3,4) -- resource, outside processing and cct.cost_type_id = cdo.cost_type_id and cct.organization_id is null and 2=2 -- p_cost_type and 3=3 -- p_include_osp -- Revision for version 1.1 and cce.cost_element_id = brr.cost_element_id and bd.department_id = bdr.department_id and ml_basis.lookup_code = bro.default_basis_type and ml_basis.lookup_type = 'CST_BASIS_SHORT' and not exists (select cro.overhead_id from cst_resource_overheads cro, cst_cost_types cct where cdo.overhead_id = cro.overhead_id and cro.resource_id = bdr.resource_id and cct.cost_type_id = cro.cost_type_id and 2=2 -- p_cost_type and cct.organization_id is null ) and mp.organization_id = cdo.organization_id -- =========================================== -- 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 and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) 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 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_ledger, p_operating_unit, p_org_code -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- order by report type, cost type, ledger, operating unit, organization code, department, missing overhead, and resource code order by 1,2,3,4,5,6,8,9 Added operating unit security US CAC Department Overhead Setup Errors This report displays two types of department overhead setup errors. First, show overheads which are not assigned to departments, but, have been assigned to resources and those resources have been assigned to these departments as well. This error is called "Overhead Rates Not In Department". The second error is when resources which have been assigned to departments but these resources have not been assigned to the overheads which also have been assigned to these departments. This error is "Overheads Not Set Up with Resources". When either error you will not earn these overheads when you roll up or charge these resources. To fix the "Overhead Rates Not In Department" error, go to the Overhead Define Form, click on the Rates button and enter a department overhead rate for that overhead. To fix the "Overheads Not Set Up with Resources" error, again go to the Overhead Define Form, click on the Resources button and enter the missing resource for that overhead. Parameters: =========== Cost Type: enter Pending or Frozen or other cost type name. Exclude Outside Processing: enter Yes to exclude outside processing resources from this report. Enter No to include outside processing resources. Defaults to Yes (mandatory). Organization Code: any inventory organization, defaults to your session's inventory organization (optional). Operating Unit: specific operating unit (optional). Ledger: specific ledger (optional). /* +=============================================================================+ -- | Copyright 2016 - 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_ovhd_dept_errors.sql -- | -- | Description: -- | Report to show department overheads setup errors. -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 07 Nov 2016 Douglas Volz Initial Coding -- | 1.1 08 Nov 2016 Douglas Volz Added Cost Element -- | 1.2 12 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters -- | and change to gl.short_name. -- | 1.3 27 Apr 2020 Douglas Volz Changed to multi-language views for the -- | inventory orgs and operating units. -- | 1.4 09 Sep 2023 Douglas Volz Added new report for missing resource/overhead -- | associations. Removed tabs and restrict to only -- | orgs you have access to, using the org access view. -- +=============================================================================+*/ Enginatics 1=1 2=2 3=3 1 10 2=2 cct.cost_type = :p_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 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 2 20 3=3 brr.cost_element_id <> 4 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 N N Y US Include Outside Processing 3 30 1=1 mp.organization_code = :p_org_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y 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 mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) 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 4 40 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit 5 50 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER') 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 69BCAA45543B6B92DAEC86E1DDAE4CFF Count by Error Type, Org and Overhead MFG 11 COST_TYPE 5 DEPARTMENT 6 DEPARTMENT_CLASS_CODE 1 ERROR_TYPE 2 LEDGER 7 MISSING_OVERHEAD 3 OPERATING_UNIT 4 ORG_CODE 9 OVERHEAD_BASIS_TYPE 8 RESOURCE_CODE 10 RESOURCE_COST_ELEMENT COST_TYPE VALUE 1 COUNT COST_TYPE ROW 2 ERROR_TYPE ROW 1 LEDGER ROW 3 MISSING_OVERHEAD ROW 6 OPERATING_UNIT ROW 4 ORG_CODE ROW 5 S Site 69BCAA45543B6B92DAEC86E1DDAE4CFF