CRP Available Resources – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Capacity · all Capacity reports
Overview
Detail report showing the date range availability of each resource, as well as daily hours and unit capacity of the resource.
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Organization Code, Plan, Department, Resource
Oracle EBS Tables Used
mtl_parameters, crp_available_resources, bom_departments, bom_resources, xxen_report_org_access
Report Categories
Related Reports
BOM Department Upload, CRP Resource Plan, WIP Resource Transactions, WIP Entities, CAC ICP PII WIP Material Usage Variance, WIP Job Upload, CAC Manufacturing Variance, CAC WIP Account Summary, CST Department Overhead Rate Upload
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | CRP Available Resources 14-May-2018 005901.xlsx |
| Blitz Report™ XML Import | CRP_Available_Resources.xml |
| SQL Source | CRP Available Resources.sql |
| Full Report on Enginatics | www.enginatics.com/reports/crp-available-resources/ |
Executive Summary
The CRP Available Resources report is a fundamental tool for Capacity Requirements Planning (CRP). It provides a detailed view of the available capacity for manufacturing resources (machines and labor) over a specified date range. By showing the daily hours and unit capacity for each resource, this report helps production planners identify potential bottlenecks and validate that the shop floor has the necessary bandwidth to meet the production schedule.
Business Challenge
Manufacturing efficiency depends on balancing demand (production orders) with supply (resource capacity).
- Overbooking: Scheduling more work than a machine can handle leads to missed deadlines and overtime costs.
- Underutilization: Idle machines represent wasted capital and lost revenue potential.
- Visibility: Planners need to know exactly how many hours a specific resource is available on a specific day, accounting for shifts, holidays, and maintenance.
Solution
This report queries the CRP tables to present the calculated availability of resources.
Key Features:
- Daily Granularity: Shows availability day-by-day, allowing for precise scheduling.
- Resource Specificity: Drills down to the individual resource level within a department.
- Capacity Metrics: Displays both “Daily Hours” (time available) and “Unit Capacity” (throughput potential).
Architecture
The report pulls data from CRP_AVAILABLE_RESOURCES, which is populated by the planning engine (ASCP or MRP). It links to BOM_DEPARTMENTS and BOM_RESOURCES for descriptive details.
Key Tables:
CRP_AVAILABLE_RESOURCES: The core table storing the calculated capacity.BOM_DEPARTMENTS: The manufacturing department owning the resource.BOM_RESOURCES: The specific machine or labor code.
Impact
- Realistic Scheduling: Ensures that production plans are feasible based on actual shop floor constraints.
- Bottleneck Identification: Highlights resources with limited availability that may constrain total output.
- Shift Management: Helps managers decide if additional shifts or overtime are needed to meet demand.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH