Search Results using_dept

Overview

The APPS.MRFV_PLAN_DEPT_RESOURCES view is a reporting and integration object within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments. It belongs to the Oracle Advanced Supply Chain Planning / Capacity Requirements Planning (CRP) functional area and exposes planned resource utilization data at the department level. The view presents a denormalized, human-readable projection of capacity planning records held in CRP_PLANNED_RESOURCES, enriching them with descriptive attributes such as organization code, organization name, department codes, resource codes, and planning efficiency and utilization factors. It is defined WITH READ ONLY, confirming its intended role as a query-only construct for reporting, discoverability, and integration rather than as an updateable interface.

Underlying Base Objects

The view is defined over five base objects, all referenced through public synonyms in the APPS schema:

• CRP_PLANNED_RESOURCES — the driving table (alias PL) holding planned resource utilization and efficiency records for a plan and organization.
• MTL_PARAMETERS — the organization parameters table (alias PA), supplying ORGANIZATION_CODE for the requested inventory organization.
• HR_ALL_ORGANIZATION_UNITS — the organization unit definition (alias AL), supplying the organization NAME.
• BOM_DEPARTMENTS — joined twice, as USING_DEPT (the department that uses the resource, matched on DEPARTMENT_ID) and as OWNING_DEPT (the department that owns the resource, an outer join on OWNING_DEPARTMENT_ID), supplying DEPARTMENT_CODE values.
• BOM_RESOURCES — the resource master (alias RES), supplying RESOURCE_CODE.

Joins are performed on ORGANIZATION_ID, DEPARTMENT_ID, and RESOURCE_ID. Rows are restricted to records where PL.DEPARTMENT_ID IS NOT NULL and a security predicate is applied on PL.ORGANIZATION_ID.

Key Columns

• ORGANIZATION_CODE — short code of the inventory organization from MTL_PARAMETERS.
• NAME — descriptive organization unit name from HR_ALL_ORGANIZATION_UNITS.
• COMPILE_DESIGNATOR — identifies the plan to which the planned resource belongs.
• USING_DEPT_DEPARTMENT_CODE — department code of the department that uses the resource.
• OWNING_DEPT_DEPARTMENT_CODE — department code of the department owning the resource (may be null given the outer join).
• RESOURCE_CODE — the resource identifier.
• UTILIZATION and EFFICIENCY — planning factors applied to the resource.
• ORGANIZATION_ID, DEPARTMENT_ID, RESOURCE_ID, OWNING_DEPARTMENT_ID — surrogate keys enabling downstream joins.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard audit columns.
• '_DF:CRP:CRP_PLANNED_RESOURCES:pl' — a descriptive flexfield reference token used by the framework.

Common Use Cases and Queries

The most frequent use case is reporting planned capacity by department and resource for a given plan, particularly where the user needs the department that "uses" a resource — the origin of the "using_dept" search term. A representative query follows:

• SELECT organization_code, name, compile_designator, using_dept_department_code, owning_dept_department_code, resource_code, utilization, efficiency FROM apps.mrpfv_plan_dept_resources WHERE organization_id = :p_org_id AND compile_designator = :p_plan;

Additional scenarios include integration extracts feeding external capacity dashboards, reconciliation of department-level resource loading, and outer-join-aware reporting that must tolerate resources without an owning department. Because the view is read only and carries an organization security predicate, it is safe for concurrent query access and does not enforce additional business logic beyond the joins shown.