Results for “dept_res”

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

Overview

APPS.MSC_RESOURCE_ITEMS_TREE_V is a reporting and integration view in the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema. It exposes the relationship between planned items and the department resources that produce or consume them, joining supply-side resource requirements against both resource assignments and routing operations. The view name reflects its purpose: it presents a flattened, query-friendly tree of resource-to-item associations across a planning instance.

Because the view is defined with a UNION ALL, it returns two complementary populations in a single result set. The first branch derives rows from MSC_RESOURCE_REQUIREMENTS and MSC_SUPPLIES, capturing resources tied to discrete supplies with NVL(req.parent_id,2) = 2. The second branch derives rows from routings and operation resource sequences, capturing resources associated with routing operations at the assembly level. Both branches project a consistent column list, so downstream consumers need not distinguish the source path unless they inspect the item/resource columns.

The user search term "dept_res" aligns directly with the view's dominant source object, MSC_DEPARTMENT_RESOURCES, aliased dept_res in the view text. Most descriptive resource attributes — group name, department class, code, description, and line and batchable flags — originate from this alias.

Underlying Base Objects

The documented base objects referenced by this view are:

• MSC_DEPARTMENT_RESOURCES — department and resource attributes (alias dept_res)
• MSC_RESOURCE_REQUIREMENTS — resource demand tied to supplies (alias req)
• MSC_SUPPLIES — supply records linking items to requirements (alias sup)
• MSC_SYSTEM_ITEMS — item master attributes within the planning instance (alias item)
• MSC_ROUTINGS — routing headers (alias rtg)
• MSC_ROUTING_OPERATIONS — routing operation detail (alias rtg_op)
• MSC_OPERATION_RESOURCES — operation resource definitions (alias op_res)
• MSC_OPERATION_RESOURCE_SEQS — operation resource sequence detail (alias op_res_s)

All are accessed through public synonyms owned by APPS. Joins in the first branch are keyed on sr_instance_id, plan_id, organization_id, and, for supply-to-requirement linkage, transaction_id = supply_id. The second branch joins routings to items on assembly item and organization, and department resources to routing operations on the shared instance and plan identifiers.

Key Columns

• plan_id, sr_instance_id, organization_id — planning context keys identifying the plan, source instance, and organization.
• resource_group_name, department_class, department_id, department_code, department_description — department and resource grouping attributes sourced from dept_res.
• resource_id, resource_code, resource_description — the specific department resource; in the routing branch resource_id is drawn from op_res.
• line_flag, batchable_flag — indicators describing resource characteristics such as line-based or batch processing.
• inventory_item_id / assembly_item_id, item_name, description, buyer_id, planner_code, abc_class — item identity and planning attributes from MSC_SYSTEM_ITEMS.

Common Use Cases and Queries

Typical uses include resource capacity reporting, item-resource impact analysis, and feeding downstream integrations that need a single list of item/resource pairings without navigating multiple planning tables.

List resources for a given plan and organization:

SELECT department_code, resource_code, item_name, resource_group_name
FROM   apps.msc_resource_items_tree_v
WHERE  plan_id = :plan_id
AND    organization_id = :org_id;

Find all items requiring a particular department resource:

SELECT item_name, planner_code, abc_class
FROM   apps.msc_resource_items_tree_v
WHERE  department_id = :dept_id
AND    line_flag = 'Y';

Because it is a UNION ALL view, duplicate item/resource pairs may appear where a resource is both a supply requirement and a routing operation resource. Queries intended for distinct reporting should therefore apply DISTINCT or aggregate as appropriate.