Search Results capacity_quantity




Overview
Explore in AI Chat

Overview

PA_REP_RES_OVC_WK_V is an APPS-owned database view in the Oracle E-Business Suite Projects (PA) module. It reports resource capacity and overcommitment on a global week basis, deriving its data from pre-aggregated forecast item records rather than from transactional assignment tables. The view is intended for reporting and integration scenarios that must identify resources whose committed work exceeds their available capacity within a specific global expenditure week.

The name decomposes into its functional intent: PA (Projects), REP (reporting), RES (resource), OVC (overcommitment), WK (weekly), and V (view). The view exposes a row for each schedulable resource per qualifying week, together with the capacity, overcommitment, and confirmed quantities recorded against the corresponding forecast item. It applies an explicit profile-driven threshold, so only resources exceeding a configured percentage of overcommitment are returned. This makes the view a targeted exception report rather than a complete capacity listing.

Because the view filters on the current date through RESOURCE_EFFECTIVE_END_DATE >= SYSDATE, results change as resource effective-dating evolves. Consumers should treat output as point-in-time and avoid caching results across date boundaries.

Underlying Base Objects

The view is defined over four synonym-wrapped base objects and two FND packages. The primary fact source is PA_FORECAST_ITEMS, aliased PFI, which supplies GLOBAL_EXP_PERIOD_END_DATE, CAPACITY_QUANTITY, OVERCOMMITMENT_QUANTITY, and the flag columns used in filtering. PA_RESOURCES_DENORM (PRD) is the denormalized resource dimension, contributing resource identity, manager, organization, job, person type, and effective dates. PER_JOBS (PJ) resolves JOB_NAME through JOB_ID. PA_REP_SEQ_NUMBER (TEMP) provides a sequence generator used to expand each resource across the days of a global week by adding SEQ_NUMBER to the resource effective start date.

Two FND packages are invoked. FND_PROFILE.VALUE reads the PA_OVERCOMMITMENT_PERCENTAGE system profile option, and FND_NUMBER.CANONICAL_TO_NUMBER converts the resulting character value to a numeric percentage. The expression NVL(FND_NUMBER.CANONICAL_TO_NUMBER(FND_PROFILE.VALUE('PA_OVERCOMMITMENT_PERCENTAGE')), 0)/100 is evaluated inside an inline view aliased PROFILE. DUAL provides the single row against which that calculation is projected.

Joins are inner joins throughout. Forecast items are constrained to FORECAST_ITEM_TYPE = 'U', DELETE_FLAG = 'N', OVERCOMMITMENT_FLAG = 'Y', and AVAILABILITY_FLAG = 'N'. Resources must have SCHEDULABLE_FLAG = 'Y' and an effective end date on or after the current date. The final predicate, OVERCOMMITMENT_QUANTITY >= CAPACITY_QUANTITY * PROFILE.VALUE_PERCENT, is what qualifies a row as an overcommitment exception.

Key Columns

  • RESOURCE_ID / RESOURCE_NAME — Identifier and display name of the resource derived from PA_RESOURCES_DENORM.
  • MANAGER_ID / MANAGER_NAME — Owning manager of the resource, useful for escalation and grouping.
  • ORGANIZATION_ID — Resource organization used to partition reporting by operating unit or organization.
  • GLOBAL_WEEK_END_DATE — The global expenditure period end date that anchors the reported week.
  • MONTH / YEAR — Numeric extractions from the global week end date, enabling period grouping.
  • WEEK_DAY — Date produced by adding the sequence number to the resource effective start date.
  • CAPACITY_HOURS — Capacity quantity recorded on the forecast item.
  • OVERCOMMITED_HOURS — Overcommitment quantity recorded on the forecast item; the column most directly answering the term "overcommitment_quantity".
  • CONFIRMED_HOURS — The computed sum CAPACITY_QUANTITY + OVERCOMMITMENT_QUANTITY, representing total confirmed load.
  • JOB_NAME / RESOURCE_JOB_LEVEL / PERSON_TYPE — Descriptive attributes from PER_JOBS and the resource denormalization.

Common Use Cases and Queries

Typical uses include capacity exception reporting, resource utilization dashboards, and integrations that feed planning or alerting systems when a resource becomes overcommitted. Because the view already applies the profile threshold, consumers generally filter further by organization, manager, or date range rather than re-implementing the overcommitment logic.

Listing overcommitted resources for a given month:

SELECT resource_name, manager_name, organization_id,
       global_week_end_date, capacity_hours,
       overcommited_hours, confirmed_hours
FROM   apps.pa_rep_res_ovc_wk_v
WHERE  year = 2024 AND month = 6
ORDER BY overcommited_hours DESC;

Summarizing overcommitment by manager:

SELECT manager_name,
       COUNT(DISTINCT resource_id) resources,
       SUM(overcommited_hours)     total_overcommited
FROM   apps.pa_rep_res_ovc_wk_v
GROUP BY manager_name
HAVING SUM(overcommited_hours) > 0;

Joining to PER_JOBS for additional job detail is unnecessary because JOB_NAME is already resolved. Integrations should respect the view's read-only nature, its SYSDATE dependence, and the fact that the threshold is controlled by the PA_OVERCOMMITMENT_PERCENTAGE profile option at the site or responsibility level.

  • View: PA_REP_RES_OVC_WK_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_REP_RES_OVC_WK_V,  object_name:PA_REP_RES_OVC_WK_V,  status:VALID,  product: PA - Projects ,  description: PA_REP_RES_OVC_WK_V is a view that shows resource capacity for a gloabl week ,  implementation_dba_data: APPS.PA_REP_RES_OVC_WK_V , 

  • View: PA_RES_AVL_HOURS_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_RES_AVL_HOURS_V,  object_name:PA_RES_AVL_HOURS_V,  status:VALID,  product: PA - Projects ,  description: PA_RES_AVL_HOURS_V is a view that shows available resource by weeks ,  implementation_dba_data: APPS.PA_RES_AVL_HOURS_V , 

  • View: PA_REP_RES_AVL_WK_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_REP_RES_AVL_WK_V,  object_name:PA_REP_RES_AVL_WK_V,  status:VALID,  product: PA - Projects ,  description: PA_REP_RES_AVL_WK_V is a view that shows resource capacity for a gloabl week ,  implementation_dba_data: APPS.PA_REP_RES_AVL_WK_V , 

  • View: PA_RES_AVL_HOURS_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_RES_AVL_HOURS_V,  object_name:PA_RES_AVL_HOURS_V,  status:VALID,  product: PA - Projects ,  description: PA_RES_AVL_HOURS_V is a view that shows available resource by weeks ,  implementation_dba_data: APPS.PA_RES_AVL_HOURS_V , 

  • View: PA_REP_RES_AVL_WK_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_REP_RES_AVL_WK_V,  object_name:PA_REP_RES_AVL_WK_V,  status:VALID,  product: PA - Projects ,  description: PA_REP_RES_AVL_WK_V is a view that shows resource capacity for a gloabl week ,  implementation_dba_data: APPS.PA_REP_RES_AVL_WK_V , 

  • View: PA_REP_RES_OVC_WK_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_REP_RES_OVC_WK_V,  object_name:PA_REP_RES_OVC_WK_V,  status:VALID,  product: PA - Projects ,  description: PA_REP_RES_OVC_WK_V is a view that shows resource capacity for a gloabl week ,  implementation_dba_data: APPS.PA_REP_RES_OVC_WK_V , 

  • View: PA_RES_AVL_HOURS_STAFF_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_RES_AVL_HOURS_STAFF_V,  object_name:PA_RES_AVL_HOURS_STAFF_V,  status:VALID,  product: PA - Projects ,  description: PA_RES_AVL_HOURS_STAFF_V is a view that shows available resource by weeks ,  implementation_dba_data: APPS.PA_RES_AVL_HOURS_STAFF_V , 

  • View: PA_RES_AVL_HOURS_STAFF_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:PA.PA_RES_AVL_HOURS_STAFF_V,  object_name:PA_RES_AVL_HOURS_STAFF_V,  status:VALID,  product: PA - Projects ,  description: PA_RES_AVL_HOURS_STAFF_V is a view that shows available resource by weeks ,  implementation_dba_data: APPS.PA_RES_AVL_HOURS_STAFF_V ,