Results for “bom_department_resources_v”

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

Overview

BOM_DEPARTMENT_RESOURCES_V is a view in the APPS schema of Oracle E-Business Suite, owned by the Bills of Material (BOM) product. Its documented description is "Resources associated with departments." The view presents the effective definition of each department–resource assignment, combining attributes that originate from three related entities: the department, the resource itself, and the department-resource association stored in the base table BOM_DEPARTMENT_RESOURCES. It joins these to the manufacturing ATP rules table so that the ATP rule name and CTP (Capable-to-Promise) configuration of the resource are available in a single result set.

The view is primarily consumed by Oracle's own forms, concurrent programs, and integrations that present or validate resource definitions, scheduling parameters, and capacity information at the department level. Because it resolves the "share capacity" relationship — where one department uses capacity from another — it exposes a derived capacity unit and a derived CTP flag that reflect the effective source department rather than only the local association row. This makes it useful for reporting and for any consumer needing the effective, cross-referenced resource setup rather than raw table content.

Underlying Base Objects

The documented referenced base objects are BOM_DEPARTMENTS, BOM_DEPARTMENT_RESOURCES, BOM_RESOURCES, and MTL_ATP_RULES, all accessed via synonyms from the APPS schema. The view aliases BOM_DEPARTMENT_RESOURCES twice: BDR for the primary association and BDR1 for a self-join used to resolve the shared/from-department resource record.

The join conditions are: BDR.RESOURCE_ID equals BR.RESOURCE_ID; BDR.SHARE_FROM_DEPT_ID equals BD.DEPARTMENT_ID (outer join); BDR.SHARE_FROM_DEPT_ID equals BDR1.DEPARTMENT_ID and BDR.RESOURCE_ID equals BDR1.RESOURCE_ID (both outer joins); and BDR.ATP_RULE_ID equals ATP.RULE_ID (outer join). All lookups to BOM_DEPARTMENTS, BOM_DEPARTMENT_RESOURCES (as BDR1), and MTL_ATP_RULES are outer joins, so a department-resource row is returned even when it shares no external capacity, references no ATP rule, or has no ATP rule defined.

Key Columns

• DEPARTMENT_ID / RESOURCE_ID — keys identifying the department and resource for the assignment.
• RESOURCE_CODE / RESOURCE_DESCRIPTION — code and description from BOM_RESOURCES.
• SHARE_CAPACITY_FLAG / SHARE_FROM_DEPT_ID / SHARE_FROM_DEPARTMENT — indicate whether capacity is borrowed and from which department.
• DERIVED CAPACITY_UNITS — DECODE(SHARE_FROM_DEPT_ID, NULL, BDR.CAPACITY_UNITS, BDR1.CAPACITY_UNITS); returns the source department's capacity when capacity is shared.
• DERIVED CTP_FLAG — DECODE(SHARE_FROM_DEPT_ID, NULL, BDR.CTP_FLAG, BDR1.CTP_FLAG); the effective Capable-to-Promise indicator, taken from the shared source department when applicable. This is the column the searching user is likely interested in.
• ATP_RULE_ID / RULE_NAME — the associated ATP rule and its name from MTL_ATP_RULES.
• AVAILABLE_24_HOURS_FLAG, SHARE_CAPACITY_FLAG, UTILIZATION, EFFICIENCY — scheduling and capacity utilization attributes.
• DEPARTMENT_CODE / DEPARTMENT_DESCRIPTION / DISABLE_DATE — department identification and status.
• ORG_ID (ORGANIZATION_ID) — organization scoping, taken from BOM_RESOURCES.
• ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield columns carried from the association table.

Common Use Cases and Queries

A frequent requirement is reporting which department-resource assignments are CTP-enabled, using the effective derived value rather than the raw column.

SELECT department_id, resource_id, resource_code, ctp_flag, atp_rule_id, rule_name FROM apps.bom_department_resources_v WHERE ctp_flag = 'Y' AND organization_id = :org_id;

Capacity sharing analysis lists the effective capacity for resources that borrow capacity:

SELECT resource_code, department_code, share_capacity_flag, share_from_department, capacity_units FROM apps.bom_department_resources_v WHERE share_capacity_flag = 'Y';

A resource listing by department, including scheduling parameters, serves validation and integration lookups:

SELECT department_code, resource_code, utilization, efficiency, available_24_hours_flag FROM apps.bom_department_resources_v WHERE department_id = :dept_id;

Because all auxiliary joins are outer joins, filters on department, ATP rule, or CTP values should account for possible NULLs. Reporting queries should scope by ORGANIZATION_ID to respect multi-organization data separation.