Search Results mrpbv_plan_dept_resources




Overview
Explore in AI Chat

Overview

The APPS.MRPBV_PLAN_DEPT_RESOURCES view is a read-only reporting object in the Oracle E-Business Suite Master Scheduling/MRP (MRP) module. Its name follows the "MRPBV_" convention commonly applied to backward-compatibility (retrofitted) views within the MRP schema, and the ETRM metadata explicitly annotates its description as "Retrofitted." This designation indicates the view was re-created or aliased to preserve compatibility with earlier releases or with interfaces that referenced the legacy object by name. The view exposes planned resource utilization and efficiency data at the intersection of organization, department, and resource, joining capacity-planning attributes from CRP_PLANNED_RESOURCES with the descriptive master data held in BOM_DEPARTMENTS, BOM_RESOURCES, HR_ALL_ORGANIZATION_UNITS, and MTL_PARAMETERS.

Because the view is defined WITH READ ONLY, it supports query access only and cannot be used as an update surface. It functions primarily as a denormalized reporting layer, presenting codes and names from multiple base tables in a single result set so that capacity and resource-planning consumers need not reconstruct the joins themselves.

Underlying Base Objects

The view text joins five base objects, all referenced through APPS synonyms in the 12.2.2 documentation:

  • CRP_PLANNED_RESOURCES (aliased PL) — the driving table, holding planned resource rows keyed by organization, department, and resource, together with utilization and efficiency percentages. It supplies ORGANIZATION_ID, DEPARTMENT_ID, RESOURCE_ID, OWNING_DEPARTMENT_ID, UTILIZATION, and EFFICIENCY.
  • BOM_DEPARTMENTS (aliased USING_DEPT) — provides DEPARTMENT_CODE, joined on DEPARTMENT_ID where PL.DEPARTMENT_ID is not null.
  • BOM_RESOURCES (aliased RES) — supplies RESOURCE_CODE, joined on RESOURCE_ID.
  • HR_ALL_ORGANIZATION_UNITS (aliased AL) — supplies ORGANIZATION_NAME, joined on ORGANIZATION_ID.
  • MTL_PARAMETERS (aliased PA) — provides ORGANIZATION_CODE, joined on ORGANIZATION_ID.

The join therefore requires an organization to exist in both MTL_PARAMETERS and HR_ALL_ORGANIZATION_UNITS, and every returned planned-resource row to resolve to a valid department and resource. Rows with a null DEPARTMENT_ID are excluded by the explicit predicate. The embedded "_SEC:PL.ORGANIZATION_ID" clause reflects the organization-level security (MO: Operating Unit / organization security) applied by the view.

Key Columns

  • ORGANIZATION_CODE / ORGANIZATION_NAME — the inventory organization code and its HR organization name, used for reporting context.
  • PLAN_NAME — the column label applied to the BOM_RESOURCES or department identifier list in the select projection (the underlying select aliases PL.COMPILE_DESIGNATOR, DEPARTMENT_CODE, and RESOURCE_CODE); in this view it surfaces the designator/resource grouping label.
  • DEPARTMENT_CODE / RESOURCE_CODE — human-readable identifiers for the department and the resource.
  • RESOURCE_UTILIZATION / RESOURCE_EFFICIENCY — the utilization and efficiency factors from CRP_PLANNED_RESOURCES (PL.UTILIZATION, PL.EFFICIENCY), central to capacity calculations.
  • ORGANIZATION_ID, DEPARTMENT_ID, RESOURCE_ID, OWNING_DEPARTMENT_ID — surrogate keys enabling joins back to base tables; OWNING_DEPARTMENT_ID distinguishes the department that owns the resource from the department where it is planned.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard audit columns inherited from CRP_PLANNED_RESOURCES.

Common Use Cases and Queries

Typical uses include capacity reporting, resource-efficiency audits, and integrations that need department-resource utilization in a single pass. Because the view already resolves codes and names, it is convenient for ad-hoc analysis and extracts.

  • List all planned resources for an organization:
    SELECT organization_code, department_code, resource_code, resource_utilization, resource_efficiency FROM apps.mrpbv_plan_dept_resources WHERE organization_code = :org_code;
  • Identify resources with low utilization for review:
    SELECT department_code, resource_code, resource_utilization FROM apps.mrpbv_plan_dept_resources WHERE resource_utilization < 50 ORDER BY resource_utilization;
  • Audit recently changed planning rows:
    SELECT resource_code, last_update_date, last_updated_by FROM apps.mrpbv_plan_dept_resources WHERE last_update_date > SYSDATE - 30;
  • Join to base BOM resources for extended attributes using RESOURCE_ID and ORGANIZATION_ID.

All queries must respect organization security, which the view enforces through the _SEC predicate on PL.ORGANIZATION_ID.