CAC Resources by Department Setup

➜ Back to Library
Description
Categories: Enginatics
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  ...  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 ([email protected])
-- |
-- | 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
-- +=============================================================================+*/   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	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
and	1=1                         -- p_org_code, p_operating_unit, p_ledger, p_cost_type
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 
cct.cost_type = nvl(:p_cost_type, cct2.cost_type)
 LOV
Organization Code 
mp.organization_code = :p_org_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
 
Related Blitz Reports
CAC Item Cost & Routing CAC Resources Associated with Overheads Setup CAC WIP Account Detail CAC Resource Costs CAC Department Overhead Setup Errors CAC ICP PII WIP Material Usage Variance CAC WIP Resource Efficiency CAC WIP Resource Efficiency with Method and Lot Variances CAC Manufacturing Variance Summary
Blitz Report™

Blitz Report™ provides multiple benefits: