Search Results assembly_usage

Overview

CRP_RESOURCE_HOURS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the CRP (Capacity) product family. It exposes the required resource hours needed to build an assembly, consolidating department, resource, and lookup information into a single denormalized structure that is convenient for capacity planning queries. The view is defined as a UNION ALL of two branches, allowing it to return both department-level and line-level resource loading records within the same result set. A literal NULL is projected in the department code position of the second branch where no department applies, and vice versa, which preserves column alignment across the union.

In EBS 12.1.1 and 12.2.2, the view is a read-only access path to the underlying CRP_RESOURCE_HOURS storage. Because it resolves department codes, resource codes, and lookup meanings in a single pass, it is frequently used by capacity reports, custom dashboards, and interface programs that need human-readable resource loading data without performing their own joins.

Underlying Base Objects

The documented base objects for this view are BOM_DEPARTMENTS, BOM_RESOURCES, CRP_RESOURCE_HOURS, MFG_LOOKUPS, and WIP_LINES. Each plays a specific role:

• CRP_RESOURCE_HOURS is the primary driving table, supplying the transaction, assembly, department, resource, usage, hours, units, basis, and origination columns.
• BOM_DEPARTMENTS is joined on ORGANIZATION_ID and DEPARTMENT_ID to resolve DEPARTMENT_CODE. Records with a NULL DEPARTMENT_ID are excluded from the outer-join branch.
• BOM_RESOURCES is joined on ORGANIZATION_ID and RESOURCE_ID to resolve RESOURCE_CODE.
• MFG_LOOKUPS is filtered to LOOKUP_TYPE = 'CRP_ROLLUP_ORIGINATION' and joined on ORIGINATION_TYPE to resolve the lookup MEANING displayed on capacity output.
• WIP_LINES supplies LINE_CODE in the second UNION ALL branch, which handles records associated with discrete job lines rather than departments.

The view therefore combines stored BOM/CRP loading data with descriptive reference data, producing a façade that abstracts several tables away from the reporting consumer.

Key Columns

The view carries the primary key and auditing columns of CRP_RESOURCE_HOURS (ROWID, TRANSACTION_ID, ORGANIZATION_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) alongside business columns. Notable business columns include:

• ASSEMBLY_ITEM_ID and BILL_OF_RESOURCES — the assembly and its bill of resources context.
• DEPARTMENT_ID / DEPARTMENT_CODE and RESOURCE_ID / RESOURCE_CODE — the resolved department and resource identifiers and codes.
• ASSEMBLY_USAGE — the usage quantity per assembly, which is the search term that surfaces this object for capacity quantity calculations.
• RESOURCE_DEPARTMENT_HOURS and RESOURCE_UNITS — the hours and units required per department.
• BASIS — the basis flag; a computed expression returns ASSEMBLY_USAGE × RESOURCE_DEPARTMENT_HOURS when BASIS = 1, RESOURCE_DEPARTMENT_HOURS when BASIS = 2, and NULL otherwise, yielding the effective required hours.
• OPERATION_SEQ_NUM and OPERATION_SEQUENCE_ID — routing operation context.
• ORIGINATION_TYPE and MEANING — the rollup origination value and its decoded lookup meaning.
• LINE_ID and LINE_CODE — discrete job line context in the second union branch.

Common Use Cases and Queries

Typical scenarios include quantifying resource load for a specific assembly, exporting resource loading by department for a capacity review, and validating bill of resources usage for a given organization. Because the effective hours are derived only for BASIS values 1 and 2, queries should account for a NULL result on other basis codes.

• Resource load for an assembly:

  SELECT organization_id, assembly_item_id, department_code, resource_code,
         assembly_usage, resource_department_hours
  FROM   apps.crp_resource_hours_v
  WHERE  assembly_item_id = :item_id;

• Load filtered by resource:

  SELECT department_code, SUM(resource_department_hours) hours
  FROM   apps.crp_resource_hours_v
  WHERE  resource_code = :resource_code
  GROUP  BY department_code;

• Origination breakdown:

  SELECT meaning, COUNT(*) occurrences
  FROM   apps.crp_resource_hours_v
  GROUP  BY meaning;

In all cases the view should be accessed with the APPS schema or a synonym, and queries should restrict by ORGANIZATION_ID to avoid cross-organization data leakage.