Search Results exp_end_date

Overview

PA_REP_UTIL_OWTVW_GE_V is a reporting view owned by the APPS schema in Oracle E-Business Suite Projects (PA). It supplies aggregated Total Hours and Total Weighted Hours for combinations of expenditure organization and work type across a "Global Week" period. The view is consumed by Disco Workbooks, which are the analytical reporting workbooks used for resource utilization reporting in the Projects suite. Because the object is a view rather than a table, it performs no data storage of its own; it projects and flattens pre-aggregated balances into a shape suitable for period-based utilization analysis.

The view name encodes its purpose: "REP" indicates a reporting object, "UTIL" indicates utilization, and "OWTVW" indicates organization/work type. It supplies values per expenditure organization, per work type, per reporting period, per balance type, which allows workbook consumers to trend utilization across calendar or fiscal reporting horizons.

Underlying Base Objects

The view is defined over five documented base objects:

PA_SUMM_BALANCES — the primary fact source, holding summarized period balances. Filters restrict rows to PERIOD_TYPE and OBJECT_TYPE_CODE obtained from the PA_REP_UTIL_GLOB package, AMOUNT_TYPE_ID values of 31 (organization total hours) and 32 (organization total weighted hours), VERSION_ID = -1, and a dummy period set name.
PA_OBJECTS — supplies the organizational and object dimension keys (EXPENDITURE_ORGANIZATION_ID, ORG_UTIL_CATEGORY_ID, WORK_TYPE_ID, BALANCE_TYPE_CODE). Joins are made on OBJECT_TYPE_CODE and OBJECT_ID.
PA_UTIL_CATEGORIES_V — a view providing the organization utilization category name and its reporting order for sorted presentation.
PA_WORK_TYPES_TL — the translatable work type table, supplying WORK_TYPE_NAME and filtered by USERENV('LANG') so that labels appear in the session language.
PA_REP_UTIL_GLOB — a PL/SQL package supplying the constant values used in the WHERE clause, including the period type, object type, balance type for actuals and forecast, and the dummy period set name.

Key Columns

EXPENDITURE_ORGANIZATION_ID — organization key for the utilization grouping.
ORG_UTIL_CATEGORY_ID / ORG_UTIL_CATEGORY_NAME / ORG_REPORTING_ORDER — utilization category identifier, its descriptive label, and the sequence used to order categories in reports.
WORK_TYPE_ID / WORK_TYPE_NAME — work type identifier and translated name.
EXP_END_DATE — the global expenditure period end date, derived from SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE.
PERIOD_MONTH — the quarter-or-month number within the period year, taken from SUMMBAL.QUARTER_OR_MONTH_NUMBER. This is the column most commonly searched for as "period_month" and is the anchor for period-by-period trending.
PERIOD_YEAR — the fiscal or calendar year of the summarized balance.
BALANCE_TYPE_CODE — distinguishes actuals from forecast balances.
TOTAL_HOURS — total hours, computed as the maximum of PERIOD_BALANCE where AMOUNT_TYPE_ID = 31, defaulting to zero.
TOTAL_WEIGHTED_HOURS — total weighted hours, computed as the maximum of PERIOD_BALANCE where AMOUNT_TYPE_ID = 32, defaulting to zero.

Common Use Cases and Queries

Typical scenarios include organization-by-work-type utilization reporting, weighted-hour capacity analysis, and feeding Disco Workbook extracts for a given period year. The conditional aggregation pattern (MAX with CASE) collapses the two AMOUNT_TYPE_ID rows into a single output row per organization/work type/period combination, so consumers can compare raw hours against weighted hours directly.

A simple query returning utilization by month for a given year:

SELECT expenditure_organization_id, work_type_name, period_year, period_month, total_hours, total_weighted_hours FROM pa_rep_util_owtvw_ge_v WHERE period_year = :p_year ORDER BY expenditure_organization_id, period_month;

A query isolating a specific month across organizations:

SELECT org_util_category_name, work_type_name, total_hours, total_weighted_hours FROM pa_rep_util_owtvw_ge_v WHERE period_month = :p_month AND period_year = :p_year ORDER BY org_reporting_order, work_type_name;

Because the view depends on session context — USERENV('LANG') for work type names and USERENV('CLIENT_INFO') for expenditure organization security — queries should be executed in a properly initialized EBS session, or the caller may not see the expected rows. Reporting should generally be driven from the period_month and period_year columns rather than EXP_END_DATE, since period_month aligns directly with the summarized balance structure used by the Disco Workbooks.

View: PA_REP_UTIL_OWTVW_GE_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_OWTVW_GE_V, object_name:PA_REP_UTIL_OWTVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_OWTVW_GE_V is a view to provide Total Hours and Total Weighted Hours values for organization/WorkType combinations across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_OWTVW_GE_V ,
View: PA_REP_UTIL_OWTVW_GE_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_OWTVW_GE_V, object_name:PA_REP_UTIL_OWTVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_OWTVW_GE_V is a view to provide Total Hours and Total Weighted Hours values for organization/WorkType combinations across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_OWTVW_GE_V ,
View: PA_REP_UTIL_ORGVW_GE_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_ORGVW_GE_V, object_name:PA_REP_UTIL_ORGVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_ORGVW_GE_V is a view to provide Capacity and Total Hours values for organizations across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_ORGVW_GE_V ,
View: PA_REP_UTIL_ORGVW_GE_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_ORGVW_GE_V, object_name:PA_REP_UTIL_ORGVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_ORGVW_GE_V is a view to provide Capacity and Total Hours values for organizations across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_ORGVW_GE_V ,
View: PA_REP_UTIL_RESVW_GE_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_RESVW_GE_V, object_name:PA_REP_UTIL_RESVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_RESVW_GE_V is a view to provide Capacity and Total Hours values for resources across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_RESVW_GE_V ,
View: PA_REP_UTIL_RESVW_GE_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_RESVW_GE_V, object_name:PA_REP_UTIL_RESVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_RESVW_GE_V is a view to provide Capacity and Total Hours values for resources across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_RESVW_GE_V ,
View: PA_REP_UTIL_RWTVW_GE_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_RWTVW_GE_V, object_name:PA_REP_UTIL_RWTVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_RWTVW_GE_V is a view to provide Total Hours and Total Weighted Hours values for resources across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_RWTVW_GE_V ,
View: PA_REP_UTIL_RWTVW_GE_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:PA.PA_REP_UTIL_RWTVW_GE_V, object_name:PA_REP_UTIL_RWTVW_GE_V, status:VALID, product: PA - Projects , description: PA_REP_UTIL_RWTVW_GE_V is a view to provide Total Hours and Total Weighted Hours values for resources across Global Week. This information is used by Disco Workbooks. , implementation_dba_data: APPS.PA_REP_UTIL_RWTVW_GE_V ,