Results for “ams_org_acct_roles_v”

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

Overview

AMS_ORG_ACCT_ROLES_V is a read-only view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. Its stated purpose in the ETRM documentation is to expose the "Roles that parties perform in customer accounts." The view is defined as a restricted projection over the Oracle Receivables / Trading Community Architecture (TCA) customer account role tables, filtered to return only active organizations and active customer accounts. Because it draws from the same TCA foundation used across Order Management, Receivables, and Marketing, the view functions as a lightweight, marketing-oriented access point for account role data without requiring the consuming process to join or filter the base tables directly.

The view is catalogued as VALID in both Oracle EBS 12.1.1 and 12.2.2. The user search term "current_role_state_effective" corresponds to one of its exposed columns, CURRENT_ROLE_STATE_EFFECTIVE, which is derived from HZ_CUST_ACCOUNT_ROLES.

Underlying Base Objects

The documented ETRM metadata lists three referenced base objects, all accessed through APPS synonyms:

• HZ_CUST_ACCOUNT_ROLES (aliased HCAR) — the primary driver table, holding one row per party-role assignment on a customer account.
• HZ_CUST_ACCOUNTS (aliased HCA) — joined on CUST_ACCOUNT_ID and filtered to STATUS = 'A'.
• HZ_PARTIES (aliased HP) — joined on PARTY_ID and filtered to STATUS = 'A' and PARTY_TYPE = 'ORGANIZATION'.

The joins are inner equi-joins, so any role whose account or party is not active, or whose party is not an organization, is excluded from the result set. This makes the view narrower than the underlying HZ_CUST_ACCOUNT_ROLES table and explains its marketing focus on organizational customer accounts.

Key Columns

The view exposes eleven columns, projected directly from HZ_CUST_ACCOUNT_ROLES:

• CUST_ACCOUNT_ROLE_ID — primary key of the role assignment in the base table.
• PARTY_ID — the party (organization) performing the role.
• CUST_ACCOUNT_ID — the customer account to which the role applies.
• BEGIN_DATE / END_DATE — the effective date range of the role assignment.
• CUST_ACCT_SITE_ID — the specific account site associated with the role, where applicable.
• ROLE_TYPE — the classification of the role (for example, bill-to, ship-to, or other configured role meanings).
• PRIMARY_FLAG — indicates whether the role is the primary one for the account or site.
• CURRENT_ROLE_STATE — the current lifecycle state of the role record.
• CURRENT_ROLE_STATE_EFFECTIVE — the effective state value used to interpret whether the role is presently active, resolution of this column is keyed to the CURRENT_ROLE_STATE and the date window. This is the column referenced by the search term.
• SOURCE_CODE — identifies the origin of the role assignment record.

Common Use Cases and Queries

Typical uses include marketing segmentation, account hierarchy reporting, and integration feeds that need the currently effective role for an organization account. Because the view already enforces active account and active organization filters, queries can be simpler than against the base tables.

Listing all roles for an account:

SELECT cust_account_role_id, party_id, role_type, primary_flag, current_role_state_effective FROM ams_org_acct_roles_v WHERE cust_account_id = :p_account_id;

Filtering for currently effective roles within a date window:

SELECT party_id, cust_account_id, role_type FROM ams_org_acct_roles_v WHERE current_role_state_effective = 'A' AND TRUNC(SYSDATE) BETWEEN begin_date AND NVL(end_date, TRUNC(SYSDATE)+1);

Identifying primary roles per account:

SELECT cust_account_id, party_id, role_type FROM ams_org_acct_roles_v WHERE primary_flag = 'Y';

Because the view is a projection with no aggregation, it can be safely joined to other TCA-based views on CUST_ACCOUNT_ID or PARTY_ID. Note that its organization-only filter means it is unsuitable for reporting on person-type parties; those must be sourced from HZ_CUST_ACCOUNT_ROLES directly.