Search Results dept_code




Overview
Explore in AI Chat

Overview

APPS.CRP_AVAILABLE_HOURS is a reporting view in the Oracle E-Business Suite Applications schema that consolidates resource capacity availability information for the Capacity Requirements Planning (CRP) module. Its principal role is to expose, in a single unified result set, the available working hours recorded against departments and resources across different capacity calendars. Two distinct availability models are maintained internally within the CRP schema — standard (non-24-hour) capacity and continuous 24-hour capacity — and this view presents both through a UNION ALL so that downstream reports, concurrent programs, and integration queries do not need to know which underlying source holds a given row. Because it is a view rather than a table, no data is stored by CRP_AVAILABLE_HOURS itself; it is a read-only projection that resolves at query time against its constituent objects.

Underlying Base Objects

The ETRM metadata for release 12.2.2 documents the view as owned by APPS and defined directly over two referenced base objects, both of which are themselves views:

  • CRP_OTHER_AVAILABLE_HOURS (VIEW) — provides availability rows for capacity calendars that are not modeled on a continuous 24-hour basis.
  • CRP_24HR_AVAILABLE_HOURS (VIEW) — provides availability rows for resources operating under a 24-hour calendar.

The definition is a straight column-aligned UNION ALL over these two objects, selecting the same ten columns from each. UNION ALL is used rather than UNION, so duplicate rows are preserved and no sort/distinct overhead is imposed. A given department-resource-date combination is therefore expected to originate from exactly one of the two branches, depending on how the resource calendar was set up. The view text is identical in form across the 12.1.1 and 12.2.2 releases described in the documentation excerpt.

Key Columns

The view projects ten columns, providing both identifying keys and the schedule facts:

  • DEPARTMENT_ID, DEPT_CODE, DEPT_DESCRIPTION — the department key, its short code, and its descriptive name. DEPT_DESCRIPTION is the column most commonly used to label results in reports, which aligns with the "dept_description" search term.
  • RESOURCE_ID, RES_CODE, RES_DESCRIPTION — the resource key, resource code, and descriptive name of the resource whose capacity is being measured.
  • ORGANIZATION_ID, ORGANIZATION_CODE — the inventory/warehouse organization in which the department and resource reside, providing the multi-organization context.
  • AVAILABLE_DATE — the calendar date for which availability is stated.
  • AVAILABLE_HOURS — the number of hours the resource is available on that date.

Common Use Cases and Queries

The view is typically used for capacity reporting, resource-load analysis, and as a feed into custom dashboards or extracts. A representative query listing availability by department description and date is:

  • SELECT dept_description, res_code, available_date, available_hours FROM apps.crp_available_hours WHERE organization_id = :org_id AND available_date BETWEEN :start_date AND :end_date ORDER BY dept_description, res_code, available_date;
  • Aggregating total planned capacity per department: SELECT dept_description, SUM(available_hours) FROM apps.crp_available_hours WHERE available_date BETWEEN :start_date AND :end_date GROUP BY dept_description;
  • Filtering to a single resource: SELECT * FROM apps.crp_available_hours WHERE resource_id = :resource_id;

Because the view unions two underlying views, queries should constrain on ORGANIZATION_ID and AVAILABLE_DATE where possible to limit the rows contributed by each branch. Note that since the base objects are views over further CRP tables, performance depends on the definitions beneath them rather than on this view itself.