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:

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.