Search Results subro_assignment_id

Overview

APPS.PJI_DIS_SUP_ROHRCHY_V is a Discoverer view owned by the APPS schema within the PJI – Project Intelligence product family. Its documented purpose is to expose resource hierarchy data together with subordinate rollup data, presenting supervisor, subordinate, and subordinate-of-subordinate relationships in a single flattened result set. The view is designed primarily for Oracle Business Intelligence Discoverer (and by extension other ad hoc reporting layers) so that users can analyze multi-level resource reporting structures without authoring their own recursive SQL against the underlying hierarchy engine.

The view resolves the personnel identifiers and reporting relationships into human-readable rows by joining the hierarchy source to PER_ALL_PEOPLE_F three times — once each for the supervisor, the subordinate, and the subordinate's subordinate. It is therefore most relevant in contexts such as project resource management, time and labor analysis, and any Project Intelligence report that must roll subordinate effort or assignment detail up through a supervisory chain. In Oracle EBS 12.1.1 and 12.2.2 the object is documented as VALID and is referenced as a Discoverer folder rather than as a transactional entity, meaning it is read-only and intended for query consumption.

Underlying Base Objects

The documented view text defines the object over four base objects:

• HRI_CS_SUPHRO_V — aliased MGR. This is the source of the hierarchy relationship rows, filtered by PRIMARY_HIERARCHY_FLAG_CODE = 'Y' so that only the primary hierarchy is reported. It supplies the supervisor, subordinate, and subordinate-of-subordinate person and assignment identifiers, along with the level and effective date columns.
• PER_ALL_PEOPLE_F — aliased three times as SUP, SUB, and SUBRO. Each alias supplies the FULL_NAME for its respective person. The joins require the hierarchy row's EFFECTIVE_START_DATE to fall between the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE of each of the three person records, ensuring date-effective name resolution.

The ETRM metadata lists no additional referenced base objects. The driving relationship is that MGR.SUP_PERSON_ID, MGR.SUB_PERSON_ID, and MGR.SUBRO_PERSON_ID must each match the corresponding PERSON_ID in the three PER_ALL_PEOPLE_F aliases; if any of the three person lookups fails the date-effective predicate, the row is excluded from the result set.

Key Columns

The view exposes level, identifier, name, and date columns:

• Level columns — SUP_ABSOLUTE_LEVEL, SUB_ABSOLUTE_LEVEL, SUB_RELATIVE_LEVEL, SUBRO_ABSOLUTE_LEVEL, SUBRO_SUP_RELATIVE_LEVEL, and SUBRO_SUB_RELATIVE_LEVEL. These describe the depth of each party in the hierarchy and the relative distance between them, enabling reports to distinguish direct from indirect reporting lines.
• Person identifiers — SUP_PERSON_ID, SUB_PERSON_ID, and SUBRO_PERSON_ID, keyed to PER_ALL_PEOPLE_F.PERSON_ID.
• Assignment identifiers — SUP_ASSIGNMENT_ID, SUB_ASSIGNMENT_ID, and SUBRO_ASSIGNMENT_ID. These are the assignment-level keys relevant to payroll, costing, and resource assignment reporting; the term subro_assignment_id corresponds to SUBRO_ASSIGNMENT_ID, the assignment of the subordinate's subordinate.
• Name columns — SUP_NAME, SUB_NAME, and SUBRO_NAME, sourced from FULL_NAME in the three person aliases.
• Date columns — EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, inherited from the hierarchy row and used to constrain the date-effective person joins.

Common Use Cases and Queries

Typical uses include org-chart style reporting, resource rollup for project performance analysis, and identifying indirect managerial accountability above a given resource. A representative query returning the subordinate assignment detail for a supervisor follows:

• SELECT sup_name, sub_name, subro_name, sub_assignment_id, subro_assignment_id FROM apps.pji_dis_sup_rohrchy_v WHERE sup_person_id = :p_sup_person_id ORDER BY subro_absolute_level;
• Filtering by effective date range to report the hierarchy as of a period: WHERE :p_as_of_date BETWEEN effective_start_date AND effective_end_date.
• Locating a subordinate's position in a chain using subro_assignment_id when reconciling assignment-level project or costing data.

Because the view is Discoverer-oriented and read-only, it should be used for reporting and analysis only; transactional updates must target the underlying base tables, not this view.