Results for “oke_cust_contacts_v”

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

Overview

OKE_CUST_CONTACTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, published under the OKE – Project Contracts product family. It presents a consolidated, denormalized list of customer contacts associated with Oracle Receivables customer accounts, drawing together party, account, relationship, and contact-role data residing in the Oracle Trading Community Architecture (TCA) tables under the HZ schema. The view is implemented as a single SQL statement (no procedural logic) and is marked VALID in the ETRM 12.2.2 registry, which also confirms availability in the 12.1.1 and 12.2.2 code lines for OKE Project Contracts and its integration points with Receivables and TCA.

Its primary role is to expose contact parties attached to a customer account through the CONTACT role type, together with an effective status and a computed point-in-time activity indicator. Because it joins across HZ_CUST_ACCOUNTS_ALL, HZ_CUST_ACCOUNT_ROLES, HZ_PARTY_RELATIONSHIPS, HZ_ORG_CONTACTS, and HZ_PARTIES, it is typically consumed by reports, concurrent programs, and outbound interfaces that need a flattened contact list without re-implementing the TCA join logic.

Underlying Base Objects

The ETRM metadata documents the following base objects, all exposed to APPS as synonyms: HZ_CUST_ACCOUNTS_ALL, HZ_CUST_ACCOUNT_ROLES, HZ_ORG_CONTACTS, HZ_PARTIES, and HZ_RELATIONSHIPS (referenced in the view text as HZ_PARTY_RELATIONSHIPS). The join path is:

• HZ_CUST_ACCOUNT_ROLES (CAR) is restricted to ROLE_TYPE = 'CONTACT' and is the driving table, supplying the account-role identifier, account, site, role state, and begin/end dates.
• HZ_PARTY_RELATIONSHIPS (PR) links to CAR on PARTY_ID and is filtered to CONTENT_SOURCE_TYPE = 'USER_ENTERED'; HZ_ORG_CONTACTS (OC) supplies the job title via PARTY_RELATIONSHIP_ID.
• HZ_PARTIES (P) is joined through PR.SUBJECT_ID to yield the contact name; a second instance of HZ_PARTIES (P2) is joined to PR.PARTY_ID to yield the e-mail address.
• HZ_CUST_ACCOUNTS_ALL (CA) supplies the operating unit (ORG_ID) for the account.

Key Columns

• PARTY_ID / PARTY_ID2 – contact party and related party identifiers exposed by the view definition.
• NAME – concatenation of PERSON_LAST_NAME and PERSON_FIRST_NAME (truncated to 50 and 40 bytes respectively) formatted as "Last, First".
• DESCRIPTION – job title from HZ_ORG_CONTACTS, truncated to 40 characters.
• ORG_ID – operating unit of the customer account.
• CUST_ACCT_ID / CUST_ACCT_SITE_ID – customer account and account site identifiers.
• ROLE_TYPE – always 'CONTACT' given the view filter.
• CURRENT_ROLE_STATE_EFFECTIVE – the effective role state flag on the account role record.
• START_DATE_ACTIVE / END_DATE_ACTIVE – begin and end dates for the contact role.
• STATUS – a computed indicator: 'I' when the role is not yet in effect or has ended relative to SYSDATE, otherwise 'A' for active.
• EMAIL_ADDRESS – e-mail address of the contact party.

Common Use Cases and Queries

Typical uses include account contact rosters for project contract documents, verification of active versus inactive contacts, and extraction feeds into notifications or integrations. A representative query filtering on the effective role state and computed status is:

SELECT cust_acct_id, name, description, email_address, start_date_active, end_date_active, status, current_role_state_effective FROM apps.oke_cust_contacts_v WHERE org_id = :p_org_id AND status = 'A' AND current_role_state_effective = :p_role_state ORDER BY name;

For point-in-time analysis, replace the STATUS predicate with BEGIN_DATE/END_DATE comparisons against a bind date. Because the view joins multiple TCA tables, queries should be constrained by CUST_ACCT_ID or ORG_ID to avoid full scans.