Results for “last_update_login_name”

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

Overview

The AMS_ORG_CONTACT_ROLES_V view in the APPS schema exposes the roles played by organization contacts within the Oracle E-Business Suite Marketing (AMS) module. In 12.1.1 and 12.2.2, it serves as a reporting and integration layer over the Trading Community Architecture (TCA) model, presenting meaningful role information for organization contacts without requiring callers to replicate the multi-table join logic against the underlying HZ tables. Because it is a read-only view, it is intended for queries, extracts, and light integration rather than for DML.

When users search on the term "role_level", this view is a relevant hit because it exposes ROLE_LEVEL alongside ROLE_TYPE and PRIMARY_FLAG. The view is primarily consumed by Marketing reporting, contact role lookups, list generation, and data extracts that need to know which contacts of an organization hold which roles and at what level.

Underlying Base Objects

The view is defined over four TCA synonym base objects: HZ_ORG_CONTACTS, HZ_ORG_CONTACT_ROLES, HZ_PARTIES, and HZ_RELATIONSHIPS. Its driving table is HZ_ORG_CONTACT_ROLES (aliased HOCR), which supplies the role row, including ROLE_LEVEL, ROLE_TYPE, and the primary flags. That role row joins to HZ_ORG_CONTACTS (HOC) on ORG_CONTACT_ID, linking the role to a specific organization contact. The contact is then resolved through HZ_RELATIONSHIPS (HPR), where HPR.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID. Only the forward direction (DIRECTIONAL_FLAG = 'F') is used, and the relationship must be current: HPR.END_DATE IS NULL OR SYSDATE <= HPR.END_DATE. The related party is confirmed through HZ_PARTIES (HP) on HP.PARTY_ID = HPR.OBJECT_ID, restricted to active organizations (HP.STATUS = 'A' and HP.PARTY_TYPE = 'ORGANIZATION'). The view also requires HPR.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED', so only user-entered relationships qualify.

Key Columns

The most significant columns include:

• ORG_CONTACT_ROLE_ID – Primary key of the underlying role record.
• ORG_CONTACT_ID – Identifies the organization contact to which the role applies.
• ROLE_LEVEL – Indicates the level at which the role is held, allowing roles to be distinguished by hierarchy or scope.
• ROLE_TYPE – The classification of the role (for example, a functional or responsibility type).
• PRIMARY_FLAG and PRIMARY_CONTACT_PER_ROLE_TYPE – Denote whether the contact is the primary holder of that role or role type.
• ORIG_SYSTEM_REFERENCE – The source-system key for the role record.
• Audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).
• Concurrent program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, WH_UPDATE_DATE).
• Derived name columns CREATED_BY_NAME, LAST_UPDATED_BY_NAME, and LAST_UPDATE_LOGIN_NAME, which alias the ID columns.

Common Use Cases and Queries

Typical uses include producing contact role listings, filtering by role level or role type, identifying primary contacts per role, and feeding Marketing extracts. A sample query filtering by role level:

SELECT org_contact_role_id, org_contact_id, role_level, role_type, primary_flag
FROM   apps.ams_org_contact_roles_v
WHERE  role_level = :p_role_level
ORDER  BY org_contact_id;

A second pattern identifies primary contacts per role type:

SELECT org_contact_id, role_type, role_level
FROM   apps.ams_org_contact_roles_v
WHERE  primary_contact_per_role_type = 'Y';

Because joins are pre-resolved, the view is well suited to reporting where the calling code has no direct TCA access.