Results for “crp_available_resources”

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

Overview

The CRP_AVAILABLE_RESOURCES table resides in the CRP (Capacity) schema and stores resource availability information used by Oracle EBS Capacity Planning and Resource Scheduling. It defines which resources are available for a given department, organization, and compilation designator, and records the hours, units, and rates that capacity planning and rough-cut capacity planning (RCCP) calculations consume. In Oracle EBS 12.1.1 and 12.2.2, this table is a foundational input for CRP resource loading and availability computations.

From a modeling perspective, the heuristic Data Vault classification for this object is satellite. The ETRM metadata indicates it is a standalone table with a single foreign key reference to BOM_DEPARTMENTS, suggesting it stores descriptive availability attributes attached to a departmental resource context rather than acting as a hub or link. It may also be modeled as a hub if the resource/organization combination is treated as a durable business key.

Key Information Stored

Each row represents an availability record for a specific resource within a department and organization. The most significant columns include:

• DEPARTMENT_ID — foreign key to BOM_DEPARTMENTS, identifying the owning department.
• RESOURCE_ID — the resource whose availability is described.
• ORGANIZATION_ID — the inventory/planning organization context.
• COMPILE_DESIGNATOR — the MPS/MRP compilation identifier under which records were generated.
• LINE_ID — line identifier for the resource load.
• RESOURCE_HOURS and RESOURCE_UNITS — available capacity in hours and units.
• MAX_RATE — the maximum production rate for the resource.
• ORIGINAL_CAPACITY — the baseline capacity before adjustments.
• RESOURCE_START_DATE and RESOURCE_END_DATE — the effective availability window.
• STATUS and APPLIED — processing state and whether the availability has been applied.
• Standard EBS audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID.

The metadata does not document a single-column surrogate primary key. The composite of ORGANIZATION_ID, DEPARTMENT_ID, RESOURCE_ID, and COMPILE_DESIGNATOR is the logical business-key candidate for uniquely identifying an availability row.

Common Use Cases and Queries

CRP availability data supports capacity planners who need to know how many hours or units a resource can deliver in a period. Typical queries join the table to departments and resources, filter by organization and compilation, and aggregate available hours. A representative pattern is:

• Reporting available capacity per resource by filtering on ORGANIZATION_ID and COMPILE_DESIGNATOR, summing RESOURCE_HOURS.
• Joining to BOM_DEPARTMENTS on DEPARTMENT_ID to enrich availability with department attributes.
• Identifying capacity shortfalls by comparing RESOURCE_HOURS against loaded demand from CRP planning runs.
• Auditing recent changes using LAST_UPDATE_DATE and LAST_UPDATED_BY.

Because the table is compilation-scoped, transactional queries should always constrain on COMPILE_DESIGNATOR to avoid mixing results from multiple planning runs.

Related Objects

The documented foreign key establishes a direct link to the department master:

• BOM_DEPARTMENTS — joined via DEPARTMENT_ID, providing department identity and attributes.

Additional related objects, based on standard CRP schema relationships, include resource and organization masters such as BOM_RESOURCES (via RESOURCE_ID), ORG_ORGANIZATION_DEFINITIONS (via ORGANIZATION_ID), and departmental capacity views used by the Capacity Planning concurrent programs. These references support end-to-end capacity reporting and availability analysis.