Results for “mrp_ap_line_resources_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MRP_AP_LINE_RESOURCES_V is a reporting and integration view within the Oracle E-Business Suite Master Scheduling/MRP (MRP) module. In the ETRM metadata for release 12.2.2 it is catalogued as a view, meaning it exposes a pre-joined, denormalized result set rather than storing data of its own. The object is characteristic of the "AP" (Applications) family of MRP views, which are designed to present planning data to external consumers — planning workbenches, capacity reports, custom concurrent programs, and third-party scheduling tools — in a form that is convenient to query without requiring the caller to reconstruct the underlying joins.

The view flattens production line (work center) definitions together with exception-set parameters so that a single row describes both a line's physical and scheduling attributes and the exception thresholds that apply to it. Because the metadata records that the view is "not implemented in this database," it should be understood as a definition supplied by Oracle for reference and portability: it may be absent from a given instance, or present only where the relevant MRP snapshot objects have been created. The column list nonetheless documents the intended contract, and the most notable element for the user searching "capacity_units" is the CAPACITY_UNITS column, which is hard-coded to the literal value 1 in the view text.

Underlying Base Objects

The documented ETRM metadata records no referenced base objects, but the embedded view text identifies three MRP snapshot tables that the view selects from:

• MRP_AP_WLINES_SN — the alias WL, supplying work line (department/line) definitions such as line code, rates, start and stop times, description, and disable date.
• MRP_AP_MRP_PARAS_SN — the alias MP, supplying MRP planning parameters keyed by organization.
• MRP_AP_PL_EX_SETS_SN — the alias MPES, supplying exception set parameters including time fence and utilization thresholds.

The joins are driven from the work-line table: WL.ORGANIZATION_ID = MP.ORGANIZATION_ID, with the exception-set table outer-joined on both EXCEPTION_SET_NAME and ORGANIZATION_ID. The outer join is significant — lines without a matching exception set still appear, with NULL exception parameters and a zero default for the row number, preserving the line as a usable capacity record. The _SN suffix indicates that these are snapshot tables populated during the MRP planning run, so the view reflects the planning snapshot rather than live transactional definitions.

Key Columns

• ORGANIZATION_ID — the inventory organization to which the line belongs; the primary partitioning key for planning queries.
• DEPARTMENT_ID / DEPARTMENT_CODE — the line identifier and its code, aliased from LINE_ID and LINE_CODE respectively.
• LINE_FLAG — set to the constant 1, indicating that the record represents a line.
• MAX_RATE / MIN_RATE — maximum and minimum production rates for the line, aliased from MAXIMUM_RATE and MINIMUM_RATE.
• START_TIME / STOP_TIME — the daily availability window of the line.
• AVAILABLE_24_HOURS_FLAG — constant 1, asserting full-day availability.
• CAPACITY_UNITS — constant 1, expressing capacity in units per rate period rather than in any alternate unit of measure.
• USER_TIME_FENCE, OVER_UTILIZED_PERCENT, UNDER_UTILIZED_PERCENT — exception-set thresholds inherited from MRP_AP_PL_EX_SETS_SN.
• RESOURCE_SHORTAGE_TYPE / RESOURCE_EXCESS_TYPE — exception identifiers for over- and under-utilization.
• RN1, RN2, RN3 — row-number artifacts from the snapshot build, useful only for diagnostics.

Common Use Cases and Queries

Typical usage is capacity reporting: listing line rates and availability for an organization, or joining the view to routing and resource data to compare required load against rated capacity. Because exception parameters are outer-joined, the view also supports identifying lines that have no exception set assigned.

The following query lists line capacity attributes for a given organization:

SELECT organization_id,
       department_code,
       max_rate,
       min_rate,
       start_time,
       stop_time,
       capacity_units
FROM   mrp_ap_line_resources_v
WHERE  organization_id = :org_id
ORDER  BY department_code;

To find lines lacking exception-set parameters:

SELECT department_code, user_time_fence, over_utilized_percent
FROM   mrp_ap_line_resources_v
WHERE  organization_id = :org_id
AND    user_time_fence IS NULL;