Results for “flm_mmm_dept_resources_v”

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

Overview

FLM_MMM_DEPT_RESOURCES_V is a database view owned by the APPS schema in Oracle E-Business Suite, defined within the Flow Manufacturing (FLM) product. It presents a consolidated picture of the department-to-resource relationships that exist for a given organization, bridging standard Bills of Material resource definitions with Flow Manufacturing operation assignments. The view is a supporting component of the Mixed Model Map (MMM) infrastructure, which underpins Flow Manufacturing scheduling, line balancing, and constraint analysis. For the many customers whose manufacturing execution depends on the association between resources and departments, this view provides a single, denormalized access point rather than requiring direct navigation across five separate base tables. It exposes department codes, resource codes, operation types, and capacity units side by side, together with an aggregated quantity of resources assigned to a plan. Because it carries department-level context and operation-type categorization, it is typically consumed by reports, diagnostic queries, and integration extracts that need to enumerate which resources are attached to which departments and under which operation categories.

Underlying Base Objects

The view is defined over four BOM synonyms and one Flow Manufacturing synonym, all resolving to base tables in the APPS schema:

• BOM_RESOURCES — supplies resource identity (RESOURCE_ID, RESOURCE_CODE) and is the anchoring resource definition.
• BOM_DEPARTMENTS — supplies department identity (DEPARTMENT_ID, DEPARTMENT_CODE) and organization context.
• BOM_DEPARTMENT_RESOURCES — links departments to resources and provides CAPACITY_UNITS.
• BOM_STANDARD_OPERATIONS — supplies OPERATION_TYPE and the department-to-standard-operation association.
• FLM_MMM_OP_RESOURCES — provides the plan-specific resource assignment quantity (RESOURCE_ASSIGNED), filtered at PLAN_ID = -1.

The view is a UNION ALL of three result sets. The first aggregates assigned resources by organization, department, resource, and operation type where a standard operation linkage exists. The second and third sets emit operation types 2 and 3 respectively with a zero assigned quantity for department-resource combinations that have no corresponding standard operation of that type, ensuring no valid department-resource pair is omitted from the output. Because the view is read-only and derived, it inherits the security and organization-access behavior of BOM_RESOURCES and the FLM plan assignments.

Key Columns

• ORGANIZATION_ID — the inventory organization to which the department and resource belong; the principal partitioning key for any query.
• DEPARTMENT_ID — surrogate key of the department, useful for joins back to BOM_DEPARTMENTS.
• DEPARTMENT_CODE — the user-facing department identifier displayed in reports.
• RESOURCE_ID — surrogate key of the resource; joins to BOM_RESOURCES and related costing or routing tables.
• RESOURCE_CODE — the user-facing resource name.
• OPERATION_TYPE — classifies the operation context (for example, numeric codes distinguishing operation categories, with literal 2 and 3 used for synthetic rows emitted by the UNION legs).
• CAPACITY_UNITS — the department-resource capacity value drawn from BOM_DEPARTMENT_RESOURCES.
• SUM(FMOR.RESOURCE_ASSIGNED) — the total resource quantity assigned across the plan (PLAN_ID = -1) for the grouped combination, zero where no assignment exists.

Common Use Cases and Queries

Typical scenarios include validating that every resource has a department association, reviewing which operation types a resource participates in, and comparing capacity units against assigned quantities to spot over- or under-allocation in the mixed model map. A representative query follows:

• SELECT organization_id, department_code, resource_code, operation_type, capacity_units, SUM(resource_assigned) FROM flm_mmm_dept_resources_v WHERE organization_id = :org_id GROUP BY organization_id, department_code, resource_code, operation_type, capacity_units ORDER BY department_code, resource_code;
• Filtering on the aggregated quantity to isolate resources with active assignments: ... HAVING SUM(resource_assigned) > 0.
• Joining DEPARTMENT_ID to BOM_DEPARTMENTS or RESOURCE_ID to BOM_RESOURCES to enrich the output with descriptive attributes for reporting or interface extracts.

Queries should always constrain on ORGANIZATION_ID, since the view returns data across organizations and is not inherently indexed beyond the base tables it references.