Results for “res_fact”

3 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

APPS.PJI_DIS_ORGWT_V is a reporting view in the Oracle EBS Projects Intelligence (PJI) schema, part of the Enterprise Project and Portfolio Management (EPM) reporting layer. The view presents organization-level and resource-level utilization, capacity, and effort metrics, distributing fact rows across time periods, organizations, and work types. It is designed to serve as a source for utilization dashboards, resource management analytics, and time-phased reporting rather than as a transactional object. The name suffix "_V" confirms it is a read-only view, and the "DIS" prefix indicates it belongs to the distribution reporting family of PJI objects. As the view text references materialized views (PJI_RM_WT_F_MV, PJI_TIME_MV) and profile-driven functions, it is refreshed implicitly whenever its underlying materialized views are refreshed, making it suitable for performance-sensitive reporting but not for real-time transactional queries.

Underlying Base Objects

Although ETRM metadata documents no referenced base objects, the view text reveals its physical definition. The central fact sources are two aliases of PJI_RM_WT_F_MV: fact (utilization facts) and res_fact (resource capacity facts). Supporting dimensions include PA_WORK_TYPES_B, PJI_PMV_ORG_DIM_TMP, PJI_PMV_ORGZ_DIM_TMP, PJI_ORG_DENORM, HR_ALL_ORGANIZATION_UNITS_TL (self-joined as hou1 for organization and hou2 for sub-organization names), PJI_TIME_MV, and PJI_ORG_EXTR_INFO. Three inline scalar subqueries supply attributes: PJI_UTILS.GET_PARAMETER for the last extraction date, and FND_PROFILE.VALUE for the PA_ORG_UTIL_DEF_CALC_METHOD and PA_RES_UTIL_DEF_CALC_METHOD profile options controlling organization and resource utilization calculation methods. The joins enforce that expenditure organization identifiers reconcile across the fact, denormalized organization, extra-info, and temporary dimension tables, while calendar resolution uses a DECODE on PERIOD_TYPE to select the GL or PA calendar.

Key Columns

Common Use Cases and Queries

Typical usage includes organization utilization analysis, resource capacity versus consumption comparisons, and period-based effort rollups for management reporting. A representative query joining the "res_fact" alias for capacity against actual effort by period would resemble:

  • SELECT period_name, organization_name, capability_hrs, total_hrs_a, bill_hrs_a FROM apps.pji_dis_orgwt_v WHERE year = :year AND calendar_type = 'PA';
  • SELECT organization_name, SUM(capacity_hrs), SUM(conf_hrs_s) FROM apps.pji_dis_orgwt_v WHERE period_start_date BETWEEN :from_date AND :to_date GROUP BY organization_name;
  • SELECT work_type_id, period_name, res_util_calc_method, org_util_calc_method FROM apps.pji_dis_orgwt_v WHERE expenditure_org_id = :org_id;

Because the view depends on materialized fact tables and inline profile lookups, queries are best limited to reporting workloads and filtered by period, organization, or work type to avoid excessive aggregation across the full fact set.