Results for “as_reporting_managers_v”

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

Overview

AS_REPORTING_MANAGERS_V is a reporting view belonging to the AS (Sales Foundation) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the reporting hierarchy definition stored in the AS_REPORTING_MANAGERS base table, resolving the manager's personnel identifiers into human-readable name attributes by joining to the Oracle HR people table. The view presents the mapping between an employee and the manager to whom that employee reports, together with the descriptive flexfield (DFF) context and attribute columns carried on the underlying entity.

In practice the view functions as a denormalised read layer for reporting and integration purposes. Rather than requiring an external report or interface to join the reporting-manager table to PER_ALL_PEOPLE_F and apply the effective-date predicate itself, AS_REPORTING_MANAGERS_V performs that join internally, so consumers receive only currently effective manager records with the manager's first and last name already resolved. This makes it a convenient source for hierarchical reporting queries, territory and sales-credit assignments, and any process that must traverse the "who reports to whom" relationship within the Sales Foundation data model.

Underlying Base Objects

The ETRM metadata records no formally documented base objects for this view (referenced base objects: none documented), and notes that the view is not implemented in the reference database. The view text, however, defines it unambiguously over two tables:

• AS_REPORTING_MANAGERS (alias RM) — the Sales Foundation table that stores each person-to-manager relationship, along with audit columns, the REPORTS_TO_FLAG indicator, and the standard descriptive flexfield attribute columns.
• PER_ALL_PEOPLE_F (alias P) — the Oracle HR people table (an effective-dated, non-frozen view over PER_ALL_PEOPLE_F/PER_ALL_PEOPLE_F_TL). It is joined so that MANAGER_PERSON_ID = P.PERSON_ID, with the additional predicate TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE ensuring that only the manager's currently effective person record is returned.

Because the view carries no formal owner recorded in the metadata, it is treated as a public reporting object in the Applications schema. The effective-date filter is applied to the HR side of the join only; the AS_REPORTING_MANAGERS side contributes its rows directly.

Key Columns

The view exposes the following columns, corresponding to the metadata column list:

• ROW_ID / ROWID — row identifier projected from AS_REPORTING_MANAGERS; useful for uniqueness checks.
• PERSON_ID — the employee whose reporting relationship is being described.
• MANAGER_PERSON_ID — the manager (the HR person_id) to whom PERSON_ID reports. This is the column most commonly searched for, and it drives the join to PER_ALL_PEOPLE_F.
• MANAGER_FIRST_NAME / MANAGER_LAST_NAME — the manager's descriptive name, sourced from PER_ALL_PEOPLE_F via the P.FIRST_NAME and P.LAST_NAME columns. Note that the view text aliases these as FIRST_NAME and LAST_NAME; the documented column names use the MANAGER_ prefix.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Oracle audit columns from AS_REPORTING_MANAGERS.
• REPORTS_TO_FLAG — status flag indicating the nature of the reporting relationship.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield context and segment values carried on the reporting-manager record, available for reporting on customer-defined attributes.

Common Use Cases and Queries

Typical scenarios include building organisational or sales-hierarchy reports, identifying all employees reporting to a given manager, and validating manager assignments during data conversion or integration work.

List all current reporting relationships with manager names:

• SELECT person_id, manager_person_id, manager_first_name, manager_last_name, reports_to_flag, last_update_date FROM as_reporting_managers_v;

Find everyone reporting to a specific manager:

• SELECT person_id, manager_person_id, manager_first_name, manager_last_name FROM as_reporting_managers_v WHERE manager_person_id = :manager_person_id;

Resolve a manager's name from a person_id:

• SELECT DISTINCT manager_person_id, manager_first_name, manager_last_name FROM as_reporting_managers_v WHERE manager_person_id = :person_id;

Because the join to PER_ALL_PEOPLE_F is effective-dated on the manager side, results reflect only relationships whose manager has a current, active person record; employees whose manager record is end-dated will not appear.