Results for “msc_avail_resource_summary_v”

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

Overview

The MSC_AVAIL_RESOURCE_SUMMARY_V view is a reporting and integration object owned by the APPS schema in Oracle E-Business Suite. It belongs to the MSC product family, Advanced Supply Chain Planning, and is registered with a status of VALID in the ETRM repository. The view consolidates resource availability information drawn from the planning engine into a single presentation layer suitable for inquiry screens, concurrent program output, and downstream integrations.

The view exists because the underlying planning tables — particularly MSC_NET_RESOURCE_AVAIL — store resource capacity in a normalized, plan-centric form that is difficult to consume directly for reporting. MSC_AVAIL_RESOURCE_SUMMARY_V flattens that data, joins departmental and resource descriptive attributes, resolves organization codes through a naming package, and applies bucket date alignment so that each row represents a resource's available capacity within a defined planning bucket.

In the context of a search for "volume_capacity", it is important to note that the view does not expose a literal column named VOLUME_CAPACITY. Capacity is instead surfaced through CAPACITY_UNITS, which carries the available resource capacity for the bucket, and through derived expressions in the union's second branch that prorate capacity across a time window. Reporting requirements framed around "volume capacity" therefore map onto CAPACITY_UNITS and the bucket columns in this view, rather than onto a column of that name.

Underlying Base Objects

The documented base objects referenced by the view are FND_PROFILE (package), MSC_DEPARTMENT_RESOURCES (synonym), MSC_GET_NAME (package), MSC_INTERORG_SHIP_METHODS (synonym), MSC_NET_RESOURCE_AVAIL (synonym), and MSC_PLAN_BUCKETS (synonym). Note that these are exposed as synonyms, meaning the physical tables reside in the MSC schema and are accessed through public or APPS-level synonyms.

The view text reveals a UNION ALL of two branches. Both branches select from MSC_PLAN_BUCKETS, MSC_NET_RESOURCE_AVAIL, and MSC_DEPARTMENT_RESOURCES. The join keys are PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, DEPARTMENT_ID, and RESOURCE_ID. Bucket alignment is enforced by matching the Julian representation of BKT_START_DATE against SHIFT_DATE. The first branch filters on PARENT_ID = -1, null simulation set, positive capacity, and a plan other than -1, returning raw CAPACITY_UNITS. The second branch omits those restrictive predicates and instead prorates capacity using (TO_TIME - FROM_TIME)/3600, producing an hourly-normalized figure. MSC_GET_NAME.ORG_CODE resolves the organization identifier to a code, and FND_PROFILE.VALUE supplies the BOM:HOUR_UOM_CODE profile value for unit-of-measure labeling.

Key Columns

• TRANSACTION_ID — Identifier of the availability transaction record.
• ORGANIZATION_ID — Inventory organization owning the resource capacity.
• PLAN_ID — Planning instance to which the row belongs.
• DEPARTMENT_ID / DEPARTMENT_CODE — Department and its descriptive code.
• RESOURCE_ID / RESOURCE_CODE — Resource and its descriptive code.
• SHIFT_DATE — Date on which the resource availability applies.
• BKT_END_DATE — End date of the aligned planning bucket.
• CAPACITY_UNITS — Available capacity quantity; the primary "volume capacity" measure.
• SR_INSTANCE_ID — Source system instance identifier.
• STATUS, APPLIED, UPDATED — Processing state flags for the availability record.
• LINE_FLAG — Indicates line-level department resource behavior.
• Attribute columns ATTRIBUTE_CATEGORY through ATTRIBUTE15 — Descriptive flexfield segments.
• Standard WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Planners and reporting developers query this view to inspect available capacity by department and resource across a plan's bucket horizon. A typical inquiry filters by plan and organization and orders by bucket date:

• Capacity summary by resource: SELECT department_code, resource_code, shift_date, bkt_end_date, capacity_units FROM msc_avail_resource_summary_v WHERE plan_id = :p_plan AND organization_id = :p_org ORDER BY shift_date, resource_code;
• Total available capacity for a department over a window: SELECT department_code, SUM(capacity_units) FROM msc_avail_resource_summary_v WHERE plan_id = :p_plan AND shift_date BETWEEN :p_start AND :p_end GROUP BY department_code;
• Zero- or negative-capacity exceptions: SELECT resource_code, shift_date, capacity_units FROM msc_avail_resource_summary_v WHERE plan_id = :p_plan AND capacity_units <= 0;

Because the view unions raw and prorated capacity rows, consumers must be aware that a single logical resource/day may appear in more than one form. Queries intended to represent true volume capacity should apply DISTINCT or aggregate carefully, and should join back to MSC_PLAN_BUCKETS if bucket boundaries are needed for grouping. The view is read-only and should not be used for DML.