CAC Resources by Department Setup
Description
Categories: Enginatics
Repository: Github
Repository: Github
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 ... more
/* +=============================================================================+
-- | Copyright 2016 - 2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use ... more
Run
CAC Resources by Department Setup 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, 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 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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Resource/Overhead Cost Type |
|
LOV | |
Organization Code |
|
LOV | |
Operating Unit |
|
LOV | |
Ledger |
|
LOV |