Results for “pji_dis_orgwt_v”

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

Overview

The PJI_DIS_ORGWT_V view is an Oracle EBS reporting object owned by the APPS schema and belonging to the PJI (Project Intelligence) product family. Its documented purpose is to provide a Discoverer-oriented presentation of organization work type utilization data, consolidating project resource utilization, billing, and capacity information across organization, sub-organization, and time dimensions. In the context of Oracle EBS 12.1.1 and 12.2.2, this view acts as a denormalized semantic layer that shields Discoverer workbooks and downstream BI queries from the complexity of the underlying materialized views and dimension tables. It joins time, organization, and resource fact structures into a single flat row set suitable for pivoting, aggregation, and hierarchical reporting.

Underlying Base Objects

Based on the documented view text, PJI_DIS_ORGWT_V is defined over several source objects rather than simple lookup tables:

• PJI_RM_WT_F_MV — a resource/work-type fact materialized view, aliased both as FACT and RES_FACT to supply actual and capacity measures.
• PJI_TIME_MV — the time dimension materialized view (PERIOD_NAME, QUARTER_NAME, YEAR, calendar dates).
• PJI_ORG_DENORM, PJI_PMV_ORG_DIM_TMP, PJI_PMV_ORGZ_DIM_TMP — organization and sub-organization dimension sources joined to resolve expenditure organization identifiers.
• PJI_ORG_EXTR_INFO — extraction metadata linking organizations to GL and PA calendar IDs.
• HR_ALL_ORGANIZATION_UNITS_TL — joined twice (HOU1, HOU2) to obtain organization and sub-organization display names.
• Inline sub-queries using PJI_UTILS.GET_PARAMETER and FND_PROFILE.VALUE to derive the last extraction date and default calculation methods.

The extract parameter LAST_EXTR_DATE_A is central to the user query “last_extr_date_a”. It is exposed through an inline function set as TO_NUMBER(TO_CHAR(FUNC1.LAST_EXTR_DATE_A, 'J')), which converts the Julian day of the last FM extract date to a numeric column, allowing Discoverer to perform numeric range filters efficiently.

Key Columns

• CAPACITY_HRS, TOTAL_HRS_A, BILL_HRS_A, CONF_HRS_S, PROV_HRS_S, CONF_OVERCOM_HRS_S, PROV_OVERCOM_HRS_S — utilization, capacity, billing, confirmed, and provisional hour measures.
• PERIOD_NAME, QUARTER_NAME, YEAR, PERIOD_START_DATE, PERIOD_END_DATE — time hierarchy attributes.
• ORGANIZATION_NAME, SUB_ORGANIZATION_NAME, ORGANIZATION_LEVEL, SUB_ORGANIZATION_LEVEL — organizational hierarchies from HR_ALL_ORGANIZATION_UNITS_TL.
• WORK_TYPE_ID, EXPENDITURE_ORG_ID, TIME_ID, PERIOD_ID — dimensional keys for linking to conformed dimensions.
• LAST_EXTR_DATE_A — the Julian numeric form of the last extraction date, plus ORG_UTIL_CALC_METHOD and RES_UTIL_CALC_METHOD from profile options.

Common Use Cases and Queries

Typical usage involves Discoverer workbook refreshes and custom SQL for organization work-type utilization reporting:

• Hour utilization by organization and period.
• Detecting stale extracts by filtering on LAST_EXTR_DATE_A.
• Drilling from year to quarter to period within Discoverer.

SELECT ORGANIZATION_NAME, SUB_ORGANIZATION_NAME, PERIOD_NAME,
       TOTAL_HRS_A, BILL_HRS_A, CAPACITY_HRS
  FROM APPS.PJI_DIS_ORGWT_V
 WHERE LAST_EXTR_DATE_A >= 2459000;