Results for “as_sg_active_mgr_v”

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

Overview

AS_SG_ACTIVE_MGR_V is an Oracle E-Business Suite Sales Foundation (AS) view owned by the APPS schema. It is a reporting and integration construct that exposes the set of sales groups associated with an active manager, that is, sales groups whose currently effective role relations identify a designated manager. The view draws exclusively from the Resource Manager (JTF_RS) family of tables, which underpin the sales group and salesforce hierarchy model in Oracle EBS 12.1.1 and 12.2.2.

The view resolves a common reporting requirement: identifying, for any given point in time, which sales groups have a manager assigned and active. Because manager assignments in JTF_RS_ROLES_B and JTF_RS_ROLE_RELATIONS are date-effective, the view applies SYSDATE-based filtering so that only currently valid manager relationships are returned. This makes it suitable for embedded use in concurrent programs, BI Publisher reports, OBIEE extracts, and custom SQL interfaces. The object is documented in ETRM with a VALID status, indicating that its definition compiles successfully against the underlying synonyms.

Underlying Base Objects

The view is defined over seven documented base objects, all referenced through APPS synonyms:

• JTF_RS_GROUPS_B — the base table holding sales group definitions, including group ID, accounting code, descriptive flexfield attributes, and active date ranges.
• JTF_RS_GROUPS_TL — the translation table supplying GROUP_NAME and GROUP_DESC, joined on GROUP_ID and filtered by LANG = USERENV('LANG').
• JTF_RS_GROUP_MEMBERS — group membership records, used in the inline manager subquery to identify the manager as a member of the group.
• JTF_RS_GROUP_USAGES — restricts the result set to groups with a usage of 'SALES'.
• JTF_RS_GRP_RELATIONS — supplies the parent group relationship via an outer join, with RELATION_TYPE = 'PARENT_GROUP'.
• JTF_RS_ROLES_B — role definitions; the inline query filters MANAGER_FLAG = 'Y'.
• JTF_RS_ROLE_RELATIONS — date-effective role-to-resource assignments driving the active-date logic.

The inline subquery (aliased MEM) intersects JTF_RS_ROLE_RELATIONS with JTF_RS_GROUP_MEMBERS and JTF_RS_ROLES_B, selecting members whose assigned role is flagged as a manager role and whose relationship date range encompasses SYSDATE (or has a null end date). The parent group relationship is an outer join, so groups without a parent are still returned.

Key Columns

• GROUP_ID (SALES_GROUP_ID) — primary identifier of the sales group.
• GROUP_NAME / NAME — translated group name from JTF_RS_GROUPS_TL.
• GROUP_DESC / DESCRIPTION — translated group description.
• PARENT_SALES_GROUP_ID — the related parent group from JTF_RS_GRP_RELATIONS; the column directly relevant to the "parent_group" search.
• MANAGER_PERSON_ID — PERSON_ID of the active manager.
• MANAGER_SALESFORCE_ID — RESOURCE_ID of the active manager.
• START_DATE_ACTIVE / END_DATE_ACTIVE — date-effective window for both the group and the manager relation (the latter from the member subquery).
• ACCOUNTING_CODE and ATTRIBUTE_CATEGORY through ATTRIBUTE15 — descriptive flexfield and accounting attributes inherited from JTF_RS_GROUPS_B.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical scenarios include hierarchy-driven reporting, territory and quota rollups, and integration extracts that must resolve a group's manager and parent group in a single query.

Listing groups with a parent group and an active manager:

SELECT sales_group_id, name, description, parent_sales_group_id, manager_person_id, manager_salesforce_id FROM apps.as_sg_active_mgr_v WHERE parent_sales_group_id IS NOT NULL;

Obtaining the manager for a specific group:

SELECT manager_person_id FROM apps.as_sg_active_mgr_v WHERE sales_group_id = :p_group_id;

Extracting the full group-to-parent mapping for a hierarchy load:

SELECT sales_group_id, parent_sales_group_id, name FROM apps.as_sg_active_mgr_v ORDER BY parent_sales_group_id, sales_group_id;

Because the view resolves manager activity against SYSDATE at execution time, results vary by run date; reports requiring historical accuracy should query the base tables directly.