Results for “mail_stop”

50+ results




Overview
Explore in AI Chat

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

Overview

OTA_CUSTOMER_CONTACTS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is catalogued under the OTA (Oracle Learning Management) product family and is documented with the description "View to list all Contacts for a Customer." The view consolidates customer contact information from the Oracle Trading Community Architecture (TCA) model into a single, denormalized result set that presents contact identity, title, status, responsibility role, mailing stop, and a formatted address for every contact associated with a customer account.

Within the EBS reporting and integration layer, the view serves as a convenience access point for Learning Management and any dependent module requiring customer contact data without reconstructing the multi-table TCA joins manually. The TITLE_MEANING column specifically surfaces the decoded lookup meaning for a contact's title, sourced from AR_LOOKUPS, which is frequently the attribute users search for when filtering or displaying contacts by professional designation.

Underlying Base Objects

The view is defined over a join of eleven base objects. The primary driver is HZ_CUST_ACCOUNT_ROLES, aliased ACCT_ROLE, restricted to rows where ROLE_TYPE = 'CONTACT'. Contact identity is resolved through HZ_PARTIES (PARTY) joined via HZ_RELATIONSHIPS (REL), while HZ_ORG_CONTACTS (ORG_CONT) links the relationship to organizational contact attributes such as MAIL_STOP.

Title decoding is performed through AR_LOOKUPS (TIT), joined with an outer join on PERSON_PRE_NAME_ADJUNCT = TIT.LOOKUP_CODE and LOOKUP_TYPE = 'CONTACT_TITLE'. Responsibility information is drawn from HZ_ROLE_RESPONSIBILITY (ROL), outer-joined on CUST_ACCOUNT_ROLE_ID with PRIMARY_FLAG = 'Y'. Site and address data are obtained through HZ_CUST_ACCT_SITES_ALL, HZ_PARTY_SITES, and HZ_LOCATIONS, while HZ_CUST_ACCOUNTS supplies the role account. The packaged function OTA_TDB_BUS.GET_FULL_NAME is invoked to assemble a formatted full name from last name, title meaning, and first name. All TCA objects are referenced through APPS synonyms.

Key Columns

  • CUST_ACCOUNT_ID and CUST_ACCOUNT_ROLE_ID — identify the customer account and the specific contact role record.
  • PERSON_FIRST_NAME and PERSON_LAST_NAME — truncated to 40 and 50 bytes respectively via SUBSTRB; the full-name expression returned by OTA_TDB_BUS.GET_FULL_NAME is exposed as an unnamed column.
  • TITLE — the raw pre-name adjunct code from HZ_PARTIES; TITLE_MEANING — the decoded lookup meaning from AR_LOOKUPS for CONTACT_TITLE, the key attribute for title-based searching.
  • STATUS — current status of the customer account role.
  • RESPONSIBILITY_TYPE — the primary responsibility classification from HZ_ROLE_RESPONSIBILITY.
  • MAIL_STOP — organizational mail stop from HZ_ORG_CONTACTS.
  • CUST_ACCT_SITE_ID — the customer account site associated with the contact.
  • ADDRESS — a concatenated address string assembled from address lines, city, state, province, county, postal code, and country, with comma delimiters inserted conditionally. Two trailing NULL columns complete the projection.

Common Use Cases and Queries

The view is typically used to populate contact listings in Learning Management enrollment and communication flows, and to reconcile contacts against customer accounts during data migration or integration.

To retrieve all contacts for a specific customer account:

SELECT cust_account_id, title_meaning, status, address FROM ota_customer_contacts_v WHERE cust_account_id = :p_account_id;

To search contacts by decoded title:

SELECT cust_account_role_id, title_meaning, responsibility_type FROM ota_customer_contacts_v WHERE title_meaning = 'Manager';

To list active contacts with their responsibility type and mail stop:

SELECT cust_account_id, title_meaning, responsibility_type, mail_stop FROM ota_customer_contacts_v WHERE status = 'A' ORDER BY cust_account_id, title_meaning;

Because the definition includes outer joins on title, responsibility, and location, queries should anticipate NULL values in TITLE_MEANING, RESPONSIBILITY_TYPE, MAIL_STOP, and ADDRESS when the corresponding source rows do not exist.