CAC Department Overhead Setup Errors
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report to 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.
/* +=============================================================================+
-- | Copyright 2016 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. ... more
/* +=============================================================================+
-- | Copyright 2016 - 2020 Douglas Volz Consulting, Inc. |
-- | All rights reserved. ... more
Run
CAC Department Overhead Setup Errors and other Oracle EBS reports with Blitz Report™ on our demo environment
select nvl(gl.short_name, gl.name) Ledger, haou.name Operating_Unit, mp.organization_code Org_Code, bd.department_code Department, 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, cct.cost_type Cost_Type 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 -- 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 cdo.basis_type in (3, 4) 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 1=1 -- p_ledger, p_operating_unit, p_org_code -- Revision for version 1.3 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) order by nvl(gl.short_name, gl.name), -- Ledger haou.name, -- Operating_Unit mp.organization_code, -- Org_Code bd.department_code -- Department |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Cost Type |
|
LOV | |
Organization Code |
|
LOV | |
Operating Unit |
|
LOV | |
Ledger |
|
LOV |