Search Results overcommited_hours

Overview

APPS.PA_REP_RES_OVC_WK_V is a reporting view in Oracle EBS Projects (PA) that identifies resource overcommitment conditions at the weekly forecast level. It exposes resources whose overcommitted quantity exceeds a configurable percentage of their available capacity, as defined by the PA_OVERCOMMITMENT_PERCENTAGE profile option. The view is part of the resource utilization and workforce planning reporting layer, providing pre-joined data suitable for overcommitment analysis, resource management dashboards, and forecasting extracts.

The name "OVC_WK" reflects the view's purpose: overcommitment (OVC) evaluated on a weekly (WK) resource sheet grain. It is read-only and intended for reporting and integration rather than transactional processing. Because the percentage threshold originates from a profile option and is resolved through the subquery aliased PROFILE, the view dynamically reflects the deployment-level configuration at query runtime.

Underlying Base Objects

The view is defined over the following documented objects:

• PA_RESOURCES_DENORM (synonym) — the denormalized resource source (alias PRD), supplying resource identity, manager, organization, job, and effective dates. Filters require resource_effective_end_date >= sysdate and schedulable_flag = 'Y'.
• PA_REP_SEQ_NUMBER (synonym) — the sequence generator (alias TEMP) used to derive individual daily dates from the resource effective start date, joined where seq_number > 0.
• PA_FORECAST_ITEMS (synonym) — the forecast fact source (alias PFI), filtered to forecast_item_type = 'U', delete_flag = 'N', overcommitment_flag = 'Y', and availability_flag = 'N'.
• PER_JOBS (synonym) — job definitions joined on prd.job_id = pj.job_id.
• FND_PROFILE (package) — resolves the PA_OVERCOMMITMENT_PERCENTAGE profile value.
• FND_NUMBER (package) — converts the profile string to a numeric canonical value via CANONICAL_TO_NUMBER.
• DUAL (synonym) — used only by the inline PROFILE subquery.

Key Columns

• RES_ID — the resource_id from PA_RESOURCES_DENORM.
• RESOURCE_NAME, MANAGER_ID, MANAGER_NAME — resource and manager identifiers.
• RESOURCE_ORGANIZATION_ID — owning organization of the resource.
• GLOBAL_EXP_PERIOD_END_DATE — the forecast period end date from PA_FORECAST_ITEMS.
• CAPACITY_QUANTITY — available capacity for the period.
• OVERCOMMITMENT_QUANTITY — committed effort beyond capacity.
• CAPACITY_QUANTITY + OVERCOMMITMENT_QUANTITY — total quantity (unnamed aggregate expression).
• PJ.NAME — job name from PER_JOBS.
• RESOURCE_JOB_LEVEL, RESOURCE_PERSON_TYPE — job level and person type attributes.
• VALUE_PERCENT — the resolved profile percentage divided by 100. Despite the user's search term, this column is internal to the inline PROFILE subquery; the WHERE clause compares overcommitment_quantity >= capacity_quantity * profile.value_percent. It is not projected as a top-level column.

Common Use Cases and Queries

The view is typically used to list overcommitted resources and their weekly forecast positions, supporting staffing alerts, capacity reviews, and integration extracts.

SELECT res_id,
       resource_name,
       manager_name,
       global_exp_period_end_date,
       capacity_quantity,
       overcommitment_quantity
FROM   apps.pa_rep_res_ovc_wk_v
WHERE  global_exp_period_end_date BETWEEN :p_start_date AND :p_end_date
ORDER  BY resource_name, global_exp_period_end_date;

Aggregate use for organization-level rollups and job-level analysis:

SELECT resource_organization_id,
       name AS job_name,
       SUM(overcommitment_quantity) total_overcommitment
FROM   apps.pa_rep_res_ovc_wk_v
GROUP  BY resource_organization_id, name
ORDER  BY total_overcommitment DESC;

Because the overcommitment threshold is profile-driven, tuning PA_OVERCOMMITMENT_PERCENTAGE changes the population returned rather than the columns. The view should be queried with date bounds on GLOBAL_EXP_PERIOD_END_DATE for performance, and callers should note that VALUE_PERCENT is not selectable at the outer level.