Results for “ast_org_contact_roles_v”

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

Overview

AST_ORG_CONTACT_ROLES_V is an APPS-owned database view used by the Oracle E-Business Suite TeleSales (AST) module. It exposes contact role assignments that link a contact to an organization/customer record, decorating the underlying transactional rows with decoded lookup meanings. The view presents one row per organization-contact-role association found in the HZ_ORG_CONTACT_ROLES entity, filtered to active and inactive registry statuses so that both current and historical role assignments remain visible to the application. Because it is a view rather than a table, it carries no storage of its own and inherits the security and consistency characteristics of its base objects. In Oracle EBS 12.1.1 and 12.2.2, TeleSales and related customer-facing modules rely on such views to render contact role information on organization and contact forms, in folder-based inquiry screens, and in concurrent reporting. The object was retained and validated in 12.2.2, confirming its role in the current data model.

Underlying Base Objects

The documented base objects for this view are HZ_ORG_CONTACT_ROLES (exposed as a SYNONYM) and AR_LOOKUPS (a VIEW). HZ_ORG_CONTACT_ROLES is the transactional Registry table that stores each role assigned to an organization contact. AR_LOOKUPS supplies the lookup codes and meanings used for decoding. The view joins ORG_CONT_ROLES to AR_LOOKUPS twice using outer joins: the first alias (LKUP1) decodes ROLE_TYPE against LOOKUP_TYPE 'CONTACT_ROLE_TYPE' to yield ROLE_TYPE_MEANING, and the second alias (LKUP2) decodes STATUS against LOOKUP_TYPE 'REGISTRY_STATUS'. The WHERE clause restricts STATUS to 'A' (active) and 'I' (inactive), excluding other registry states. Additional standard WHO and program columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) and the ATTRIBUTE1 through ATTRIBUTE15 DFF columns are passed through unchanged.

Key Columns

• ROW_ID – The ROWID of the underlying HZ_ORG_CONTACT_ROLES row, useful for row-level addressing.
• ORG_CONTACT_ROLE_ID – Primary key of the role assignment row.
• ORG_CONTACT_ID – Foreign key to the organization contact that owns the role.
• ROLE_TYPE – Lookup code identifying the role assigned to the contact.
• ROLE_TYPE_MEANING – Decoded meaning of ROLE_TYPE from AR_LOOKUPS where LOOKUP_TYPE is 'CONTACT_ROLE_TYPE'.
• ROLE_LEVEL, PRIMARY_FLAG, PRIMARY_CONTACT_PER_ROLE_TYPE – Indicators controlling role precedence and which contact is primary within a role type.
• STATUS and its decoded meaning – REGISTRY_STATUS lookup value; the view includes only 'A' and 'I' rows.
• ORIG_SYSTEM_REFERENCE, OBJECT_VERSION_NUMBER, APPLICATION_ID, CREATED_BY_MODULE – Integration and concurrency columns supporting external system references and the multi-module registry model.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 – Descriptive flexfield columns for customer-defined attributes.

Common Use Cases and Queries

Typical scenarios include reporting all roles held by contacts of a given organization, identifying primary contacts for each role type, and auditing active versus inactive role assignments. A representative query is:

SELECT org_contact_id, role_type, role_type_meaning, status, primary_flag FROM ast_org_contact_roles_v WHERE org_contact_id = :p_org_contact_id AND status = 'A' ORDER BY role_type;

A second common pattern filters by role type to locate all contacts designated with a particular role:

SELECT org_contact_id, role_type_meaning, primary_flag FROM ast_org_contact_roles_v WHERE role_type = :p_role_type AND status = 'A';

Because the view joins to AR_LOOKUPS with outer joins, role assignments whose lookup codes are undefined still appear with a null meaning, which is useful for data-quality reviews. In 12.1.1 and 12.2.2 the view is also referenced by TeleSales contact and organization inquiry screens, so its output must remain consistent with HZ_ORG_CONTACT_ROLES. Direct DML against the view is not supported; changes must be made to the base Registry tables through the appropriate APIs.