Results for “as_reporting_managers”

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

Overview

AS_REPORTING_MANAGERS is a denormalized table in the Oracle E-Business Suite Sales Foundation (AS) product family, residing in the OSM schema. It is derived from AS_SALESFORCE and captures the reporting hierarchy that links sales representatives to their managers within a given sales group. Because the table flattens organizational relationships into a single physical structure, it eliminates the recursive joins typically required when navigating AS_SALESFORCE directly, making it well suited for reporting, territory analysis, and manager-level roll-up queries.

The table is owned by OSM and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Its documented physical schema contains 25 columns. A heuristic Data Vault classification mined from the foreign-key structure suggests treating AS_REPORTING_MANAGERS as a standalone object. In practice, the pairing of PERSON_ID and MANAGER_PERSON_ID gives the table the character of a relationship or link structure, joining two party records (the salesperson and the manager) rather than describing attributes of a single entity. Modelers adopting Data Vault conventions may therefore treat it as a link candidate, while the descriptive columns behave as dependent attributes.

Key Information Stored

The table's identifying columns and principal business attributes are:

• PERSON_ID — the identifier of the sales representative (the subordinate) in the reporting relationship. Part of the primary key.
• MANAGER_PERSON_ID — the identifier of the manager to whom the representative reports. The second component of the primary key.
• SALES_GROUP_ID — the sales group context in which the reporting relationship is valid; it participates in both unique indexes, indicating that a representative may report to different managers across sales groups.
• REPORTS_TO_FLAG — a flag qualifying the nature of the reporting relationship.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns tracking who created and last modified each row, and when.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield (DFF) columns, allowing customers to extend the table with site-specific attributes without modifying its base structure.

The surrogate primary key is AS_REP_MANAGERS_PK, defined on the composite of (PERSON_ID, MANAGER_PERSON_ID). Two unique indexes serve as business-key candidates: AS_REP_MANAGERS_U1 on (PERSON_ID, MANAGER_PERSON_ID, SALES_GROUP_ID) and AS_REP_MANAGERS_U2 on (PERSON_ID, SALES_GROUP_ID, MANAGER_PERSON_ID). Their presence confirms that the combination of representative, manager, and sales group is intended to be unique, while the primary key alone does not include the group.

Common Use Cases and Queries

Typical reporting scenarios include listing all direct reports for a manager, identifying a representative's manager within a specific sales group, and building manager roll-up summaries of sales activity. A basic pattern joins the table to party or resource views on PERSON_ID and MANAGER_PERSON_ID:

• Direct reports: SELECT PERSON_ID FROM AS_REPORTING_MANAGERS WHERE MANAGER_PERSON_ID = :manager_id;
• Manager lookup by group: SELECT MANAGER_PERSON_ID FROM AS_REPORTING_MANAGERS WHERE PERSON_ID = :rep_id AND SALES_GROUP_ID = :group_id;
• Roll-up reporting: join AS_REPORTING_MANAGERS to sales opportunity or quota tables to aggregate pipeline totals by MANAGER_PERSON_ID.

Because the DFF columns carry a category plus fifteen attributes, queries that filter on customer-defined criteria should include ATTRIBUTE_CATEGORY to scope the flexfield context correctly.

Related Objects

The principal related objects, inferred from the documented relationships and the table's derivation lineage, include:

• AS_SALESFORCE — the source table from which AS_REPORTING_MANAGERS is denormalized; join on PERSON_ID and MANAGER_PERSON_ID.
• AS_SALES_GROUPS (and the associated SALES_GROUP_ID reference) — supplies the group context used in both unique indexes.
• JTF_RS_RESOURCE_EXTNS / JTF_RS_RESOURCE_EXTNS_VL — resource extensions that resolve PERSON_ID and MANAGER_PERSON_ID to named sales resources.
• PER_ALL_PEOPLE_F — the HR party table used to obtain employee names, titles, and status for both participants in the relationship.
• AS_SALES_CREDITS — sales credit lines that can be aggregated through the reporting hierarchy.
• AS_OPPORTUNITIES / AS_LEADS — pipeline objects frequently reported at the manager level using the hierarchy in this table.

As a standalone object with no documented outbound foreign keys to the core HR tables, AS_REPORTING_MANAGERS is best understood as a reporting aid: it centralizes the representative-to-manager mapping so that applications and custom reports can navigate the sales hierarchy without querying AS_SALESFORCE recursively.