Results for “owning_department_code”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MRPFV_PLAN_DEPT_RESOURCES is an APPS-owned database view within the Oracle E-Business Suite Master Scheduling/MRP (MRP) product family. It presents planned resource utilization data at the intersection of organizations, departments, and resources, drawing its content from the CRP_PLANNED_RESOURCES table and enriching it with descriptive attributes from supporting setup entities. The view is documented as "Retrofitted," indicating that it was reconstructed or revalidated to conform to the current ETRM metadata standards under 12.1.1 and 12.2.2.
The view delivers a denormalized, read-only projection that joins resource planning records to organization parameters, organization unit names, department codes, and resource codes. This makes it suitable for capacity planning, resource load reporting, integration extracts, and downstream analytics where consumers need human-readable descriptors alongside the surrogate keys (ORGANIZATION_ID, DEPARTMENT_ID, RESOURCE_ID, OWNING_DEPARTMENT_ID) that populate CRP_PLANNED_RESOURCES.
Underlying Base Objects
The view is defined over five referenced objects, all exposed through APPS synonyms:
- CRP_PLANNED_RESOURCES — the primary driving table holding planned resource utilization, efficiency, department, resource, and owning department identifiers.
- MTL_PARAMETERS — supplies the ORGANIZATION_CODE for the inventory organization associated with each planned resource row.
- HR_ALL_ORGANIZATION_UNITS — provides the organization unit NAME (rendered as ORGANIZATION_NAME in the view) for the organization.
- BOM_DEPARTMENTS (USING_DEPT) — resolves the DEPARTMENT_CODE for the department actually using the resource.
- BOM_DEPARTMENTS (OWNING_DEPT) — resolves the DEPARTMENT_CODE for the department that owns the resource.
- BOM_RESOURCES — supplies the RESOURCE_CODE for each planned resource.
The join uses an outer join on the owning department (OWNING_DEPT.DEPARTMENT_ID(+) = PL.OWNING_DEPARTMENT_ID), so rows referencing a null or unresolvable owning department are still returned. An inner join to USING_DEPT is enforced, and the view filters out rows where DEPARTMENT_ID is null. The view is declared WITH READ ONLY, preventing DML through the view.
Key Columns
- ORGANIZATION_CODE — from MTL_PARAMETERS; the inventory organization short code.
- ORGANIZATION_NAME — from HR_ALL_ORGANIZATION_UNITS; the descriptive organization name.
- PLAN_NAME — alias for PL.COMPILE_DESIGNATOR, identifying the plan context.
- DEPARTMENT_CODE — the code of the department using the resource.
- OWNING_DEPARTMENT_CODE — the code of the department owning the resource; the column directly matching the search term and typically the join key of interest for departmental resource ownership reporting.
- RESOURCE_CODE — the resource identifier.
- RESOURCE_UTILIZATION / RESOURCE_EFFICIENCY — planning utilization and efficiency percentages.
- "_DF" — the descriptive flexfield discriminator column.
- ORGANIZATION_ID, DEPARTMENT_ID, RESOURCE_ID, OWNING_DEPARTMENT_ID — surrogate keys for joins.
- Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY.
Common Use Cases and Queries
A frequent scenario is identifying every planned resource owned by a specific department. Because OWNING_DEPARTMENT_CODE is derived through an outer join, consumers should be prepared for nulls where an owning department is not assigned.
- Locate resources by owning department:
SELECT organization_code, organization_name, plan_name,
department_code, owning_department_code,
resource_code, resource_utilization, resource_efficiency
FROM apps.mrpfv_plan_dept_resources
WHERE owning_department_code = :owning_dept
ORDER BY organization_code, department_code, resource_code;
- Report utilization and efficiency grouped by organization and department, filtering rows where the owning department is missing:
SELECT organization_code, department_code, owning_department_code,
COUNT(*) AS resource_count,
AVG(resource_utilization) AS avg_utilization,
AVG(resource_efficiency) AS avg_efficiency
FROM apps.mrpfv_plan_dept_resources
WHERE owning_department_code IS NOT NULL
GROUP BY organization_code, department_code, owning_department_code;
These queries are read-only by design and are safe for ad-hoc reporting, extract programs, and integration staging. Because the view resolves descriptive codes at query time, results always reflect the current BOM_DEPARTMENTS, BOM_RESOURCES, and organization setup.
-
- Retrofitted
APPS.MRPFV_PLAN_DEPT_RESOURCES·↳ BOM_DEPARTMENTS·↳ BOM_RESOURCES·↳ CRP_PLANNED_RESOURCES·Explore MRP module →
-
- Retrofitted
APPS.MRPFV_PLAN_DEPT_RESOURCES·↳ BOM_DEPARTMENTS·↳ BOM_RESOURCES·↳ CRP_PLANNED_RESOURCES·Explore MRP module →
-
View shows work orders forecasted by Preventive Maintenance scheduling.
APPS.EAM_OUTSTANDING_WORK_ORDERS_V·↳ BOM_DEPARTMENTS·↳ EAM_FORECASTED_WORK_ORDERS·↳ EAM_PM_SCHEDULINGS·Explore EAM module →
-
View for EAM equipment resources
APPS.MTL_EAM_EQUIP_RESOURCES_V·↳ BOM_DEPARTMENTS·↳ BOM_DEPARTMENT_RESOURCES·↳ BOM_DEPT_RES_INSTANCES·Explore INV module →
-
View for EAM equipment resources
APPS.MTL_EAM_EQUIP_RESOURCES_V·↳ BOM_DEPARTMENTS·↳ BOM_DEPARTMENT_RESOURCES·↳ BOM_DEPT_RES_INSTANCES·Explore INV module →
-
APPS.GMP_EAM_EQUIP_RESOURCES_V·↳ CR_RSRC_DTL·↳ CR_RSRC_MST·↳ GMP_RESOURCE_EQUIPMENTS·Explore GMP module →
-
Equipment to resource mapping view
APPS.GMP_EAM_EQUIP_RESOURCES_V·↳ CR_RSRC_DTL·↳ CR_RSRC_MST·↳ GMP_RESOURCE_EQUIPMENTS·Explore GMP module →
-
Asset Number Interface View
APPS.MTL_EAM_ASSET_NUM_INTERFACE_V·↳ BOM_DEPARTMENTS·↳ EAM_COMMON_UTILITIES_PVT·↳ MFG_LOOKUPS·Explore INV module →
-
View of resources used for a plan
APPS.CRP_PLANNED_RESOURCES_V·↳ BOM_DEPARTMENTS·↳ BOM_DEPARTMENT_RESOURCES·↳ BOM_RESOURCES·Explore CRP module →
-
Asset Number Interface View
APPS.MTL_EAM_ASSET_NUM_INTERFACE_V·↳ BOM_DEPARTMENTS·↳ EAM_COMMON_UTILITIES_PVT·↳ MFG_LOOKUPS·Explore INV module →
-
View shows work orders forecasted by Preventive Maintenance scheduling.
APPS.EAM_OUTSTANDING_WORK_ORDERS_V·↳ BOM_DEPARTMENTS·↳ EAM_FORECASTED_WORK_ORDERS·↳ EAM_PM_SCHEDULINGS·Explore EAM module →
-
View of resources used for a plan
APPS.CRP_PLANNED_RESOURCES_V·↳ BOM_DEPARTMENTS·↳ BOM_DEPARTMENT_RESOURCES·↳ BOM_RESOURCES·Explore CRP module →
-
The staging table used by the collection program to validate and process data for table MSC_DEPARTMENT_RESOURCES.
-
The staging table used by the collection program to validate and process data for table MSC_DEPARTMENT_RESOURCES.
-
View: EAM_WORK_ORDERS_V 12.1.1
View shows all maintenance work orders alongwith the service requests associated to them
APPS.EAM_WORK_ORDERS_V·↳ BOM_DEPARTMENTS·↳ CSI_ITEM_INSTANCES·↳ CS_INCIDENTS_B_SEC·Explore EAM module →
-
View: WIP_EAM_WORK_ORDERS_V 12.1.1
Maintenance Work Orders
APPS.WIP_EAM_WORK_ORDERS_V·↳ BOM_DEPARTMENTS·↳ CSI_ITEM_INSTANCES·↳ EAM_COMMON_UTILITIES_PVT·Explore WIP module →
-
View: WIP_EAM_WORK_ORDERS_V 12.2.2
Maintenance Work Orders
APPS.WIP_EAM_WORK_ORDERS_V·↳ BOM_DEPARTMENTS·↳ CSI_ITEM_INSTANCES·↳ EAM_COMMON_UTILITIES_PVT·Explore WIP module →
-
View: EAM_WORK_ORDERS_V 12.2.2
View shows all maintenance work orders along with the work requests/service requests associated to them.
APPS.EAM_WORK_ORDERS_V·↳ BOM_DEPARTMENTS·↳ CSI_ITEM_INSTANCES·↳ CS_INCIDENTS_B_SEC·Explore EAM module →
-
Asset Number Interface table
-
View: EAM_CFR_WORK_ORDER_V 12.2.2
This view displays details of all maintenance work orders.This view is used for ERES at the time of work order completion in the XGM.
APPS.EAM_CFR_WORK_ORDER_V·↳ BOM_DEPARTMENTS·↳ CSI_COUNTERS_TL·↳ CSI_ITEM_INSTANCES·Explore EAM module →
-
This view shows all the resources used in a plan
APPS.MSC_PLANNED_RESOURCES_V·↳ MSC_DEPARTMENT_RESOURCES·↳ MSC_GET_NAME·↳ MSC_INTERORG_SHIP_METHODS·Explore MSC module →
-
This view shows all the resources used in a plan
APPS.MSC_PLANNED_RESOURCES_V·↳ MSC_DEPARTMENT_RESOURCES·↳ MSC_GET_NAME·↳ MSC_INTERORG_SHIP_METHODS·Explore MSC module →
-
Asset Number Interface table
-
Requests to create or modify jobs and schedules
-
Requests to create or modify jobs and schedules
-
Field Prompts used for Personal Queries
-
Field Prompts used for Personal Queries