Results for “per_expanded_analyses_view2”

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

Overview

PER_EXPANDED_ANALYSES_VIEW2 is a VALID database view owned by the APPS schema within the PER (Human Resources) product module of Oracle E-Business Suite 12.1.1 and 12.2.2. The ETRM metadata documents its description concisely as "Used to support user interface," indicating that the object exists primarily to serve the Oracle HRMS forms-based UI rather than as a general-purpose reporting layer. The view denormalizes person analysis records by joining personnel data, analysis criteria, and person type information into a single flattened row set, allowing forms and concurrent programs to retrieve complete analysis context without performing multiple round trips.

Because the view begins with a /*+ RULE */ hint, the optimizer deliberately applies the rule-based approach rather than the cost-based optimizer. This is characteristic of many older EBS seeded views that pre-date reliable statistics collection on the underlying HR tables. The view is one element of a family of related constructions (View, View2) that Oracle seeded to support the Person Analyses functionality within the People window of the HRMS responsibility, and it should be treated as an Oracle-maintained object that is not intended for customer modification.

Underlying Base Objects

The ETRM 12.2.2 metadata records the following referenced base objects: HR_GENERAL (package), HR_PERSON_NAME (package), HR_PERSON_TYPE_USAGE_INFO (package), HR_SECURITY (package), PER_ANALYSIS_CRITERIA (synonym), PER_PEOPLE (view), PER_PERSON_ANALYSES (synonym), PER_PERSON_TYPES (synonym), and PER_PERSON_TYPES_TL (synonym).

The SQL body reveals the join structure. PER_PEOPLE (N) is joined to PER_PERSON_ANALYSES (A) on PERSON_ID, and A is joined to PER_ANALYSIS_CRITERIA (C) on ANALYSIS_CRITERIA_ID. Person type information is obtained from PER_PERSON_TYPES (P) and its translation table PER_PERSON_TYPES_TL (PTL), joined on PERSON_TYPE_ID and constrained by PTL.LANGUAGE = USERENV('LANG') to return the description in the session's language. The package references — HR_PERSON_TYPE_USAGE_INFO, HR_PERSON_NAME, HR_GENERAL, and HR_SECURITY — supply the user-friendly person type name via the GET_USER_PERSON_TYPE function call and enforce row-level security. Because PER_PEOPLE and PER_PERSON_ANALYSES are exposed through synonyms in APPS, the physical base tables reside in the PER schema.

Key Columns

• PERSON_ID, FULL_NAME, EMPLOYEE_NUMBER, APPLICANT_NUMBER — core person identification attributes sourced from PER_PEOPLE.
• PERSON_TYPE_ID, USER_PERSON_TYPE — the internal person type identifier and the descriptive name returned by HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE, resolved as of the record's effective end date.
• PERSON_ANALYSIS_ID, ANALYSIS_CRITERIA_ID, BUSINESS_GROUP_ID, COMMENTS, DATE_FROM, DATE_TO — the analysis record itself, including its validity window and free-text comments.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — the descriptive flexfield segments attached to the person analysis record.
• SEGMENT1–SEGMENT30, ID_FLEX_NUM, ENABLED_FLAG, SUMMARY_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE — the analysis criteria key flexfield and its structural definition.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns carried from PER_PERSON_ANALYSES.
• NPW_NUMBER — the national person number, where configured.

Common Use Cases and Queries

The principal use case is retrieval of a person's analyses for display in the HRMS People form. Integrators and report developers who use this view generally do so to reproduce the same flattened structure the form consumes. A representative query retrieving all analyses for an employee is:

SELECT person_id, full_name, employee_number,
       user_person_type, analysis_criteria_id,
       segment1, comments, date_from, date_to
FROM   apps.per_expanded_analyses_view2
WHERE  person_id = :p_person_id
ORDER  BY date_from DESC;

A second common pattern filters on the analysis criteria flexfield to group employees by a specific analysis classification:

SELECT analysis_criteria_id, segment1,
       COUNT(DISTINCT person_id) person_count
FROM   apps.per_expanded_analyses_view2
WHERE  TRUNC(SYSDATE) BETWEEN date_from AND date_to
GROUP  BY analysis_criteria_id, segment1;

In the context of a "People Insights 2.0 – Power BI" initiative, this view is a candidate source for extracting person analysis attributes into a reporting layer. However, several caveats apply. The /*+ RULE */ hint can produce inefficient plans for set-based extraction, so materializing the result into a staging table is preferable to direct query. The view depends on HR_SECURITY, so results are subject to the security profile of the connecting user; integration accounts must be granted an unrestricted HR security profile. Finally, because the object is documented as UI-supporting, Oracle does not guarantee its shape across patches, and any downstream Power BI model should be regenerated after EBS patching to detect column changes.