Results for “msc_pdr_resource_details_v”

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

Overview

MSC_PDR_RESOURCE_DETAILS_V is an Advanced Supply Chain Planning (MSC) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes resource-level detail used by the Planner Workbench and the Planning Detail Report (PDR) resource-detail reporting framework. The view consolidates department and operation resource information into a single, filter-aware result set whose rows are restricted at runtime by the user's current planning parameters.

Its defining characteristic is that it is not a static reporting view. Every query is filtered against MSC_PDR_PARAMETERS for the FND_GLOBAL.USER_ID, meaning the same SELECT statement returns different rows depending on the plan, organization, resource group, department, and resource selections stored for the logged-in user. The view therefore serves as a runtime bridge between the parameter-collection UI and the underlying planning tables.

Underlying Base Objects

The view is documented as referencing the following objects: FND_GLOBAL (package), MSC_DEPARTMENT_RESOURCES (synonym), MSC_GET_NAME (package), MSC_OPERATION_RESOURCES (synonym), MSC_OPERATION_RESOURCE_SEQS (synonym), and MSC_PDR_PARAMETERS (synonym). The first branch of the UNION ALL selects from MSC_DEPARTMENT_RESOURCES aliased as PP, while a second branch draws from the operation-resource tables to produce the combined result set.

MSC_PDR_PARAMETERS is central to the view's behaviour. The WHERE clause joins it four times — once for PLAN_ID and then once each for organization/instance, resource group name, department, and resource — using the NVL-based pattern that either matches the stored parameter or defers to the underlying PP value when no parameter is set. FND_GLOBAL.USER_ID supplies the current session identity for each of these correlated subqueries. MSC_GET_NAME is called repeatedly as a PL/SQL helper to resolve organization codes, lookup meanings, department codes, and resource over-utilization costs.

Key Columns

• DEPARTMENT_ID — Surfaces as the outer column list reference and is the target of the parameter predicate NVL(DEPT_LINE_ID, NVL(PP.DEPARTMENT_ID, -1)).
• RESOURCE_ID — The resource key; DECODE(PP.RESOURCE_ID, -1, NULL, ...) suppresses department-code resolution for the synthetic "no resource" row.
• DEPARTMENT_CODE / RESOURCE_CODE / RESOURCE_GROUP_NAME — Human-readable identifiers resolved through MSC_GET_NAME.
• RESOURCE_TYPE — Decoded via MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE', ...).
• MIN_RATE, MAX_RATE, RESOURCE_COST, UTILIZATION, EFFICIENCY — Planning rate and capacity attributes.
• PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID — Planning context keys carried through to the caller.
• DEPARTMENT_CLASS, UNIT_OF_MEASURE, RESOURCE_DESCRIPTION — Descriptive attributes.
• Several positions are populated with TO_CHAR(NULL), TO_NUMBER(NULL), TRUNC(SYSDATE), or NULL as placeholders to satisfy the column contract expected by the consuming report or interface.

Common Use Cases and Queries

The primary use case is PDR resource-detail reporting within Advanced Supply Chain Planning, where the planner selects an organization and resource scope in the parameter form and the view returns only authorized rows. A typical query is:

SELECT department_id, resource_id, department_code, resource_code, resource_cost FROM apps.msc_pdr_resource_details_v WHERE plan_id = :plan_id;

Because filtering is user-driven, ad hoc reporting must first insert or verify a row in MSC_PDR_PARAMETERS for the session user. Investigating the dept_line_id predicate is a frequent diagnostic task: the view matches NVL(DEPARTMENT_ID, -1) against NVL(DEPT_LINE_ID, ...) in MSC_PDR_PARAMETERS, so a missing or stale DEPT_LINE_ID causes the view to return no departmental rows or to fall through to the default branch.