Results for “ast_ls_cont_addr_v”

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

Overview

AST_LS_CONT_ADDR_V is a reporting view within the Oracle E-Business Suite TeleSales (AST) module. It consolidates party, contact, address, and telephone information into a single denormalized result set, allowing TeleSales applications, concurrent programs, and integration interfaces to retrieve contact and address details for parties involved in a relationship without joining the underlying Trading Community Architecture (TCA) tables directly. The view is particularly significant because it surfaces both organizational parties and their related contacts, together with primary phone contact points, in one queryable structure. ETRM documentation lists this view under category "Implementation/DBA Data: Not implemented in this database," indicating the view definition is seeded by the application but not always deployed or reserved in every environment. The view text itself is fully documented, which provides precise visibility into the filtering and join logic applied.

Underlying Base Objects

The view is defined over six TCA base tables. The principal driver is HZ_PARTY_RELATIONSHIPS (aliased REL), which supplies the relationship between a subject party and an object party, constrained by REL.STATUS = 'A' to return only active relationships. HZ_PARTIES appears three times with distinct aliases: P (the subject party, joined on REL.SUBJECT_ID), O (the object/organization party, joined on REL.OBJECT_ID), and R (the related party linked via REL.PARTY_ID). Address data is drawn from the R alias of HZ_PARTIES, which is why ADDRESS1 through COUNTRY are exposed from that instance. HZ_ORG_CONTACTS (CNT) joins to HZ_PARTY_RELATIONSHIPS on PARTY_RELATIONSHIP_ID, supplying contact-specific attributes such as contact number, job title, and department. HZ_CONTACT_POINTS (CPT) is outer-joined to HZ_PARTIES on OWNER_TABLE_ID = REL.PARTY_ID, restricted to primary, active phone contact points. The outer-join syntax (+) used throughout confirms this view predates the ANSI join standard and belongs to the classic Oracle join style used throughout EBS 12.1.1 and 12.2.2.

Key Columns

• PARTY_NAME / PARTY_NUMBER / PARTY_ID — Object party identification from HZ_PARTIES alias O, representing the organization or party on the object side of the relationship.
• PERSON_ID / PERSON_NAME / PERSON_FIRST_NAME / PERSON_LAST_NAME / FULL_NAME — Contact person attributes from alias P, with FULL_NAME concatenating first and last names (the provided view text concatenates first and last; note this differs from the standard LAST||', '||FIRST convention).
• EMAIL_ADDRESS, ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — Location and address detail from the related party alias R.
• CONTACT_NUMBER, CONTACT_KEY, ORG_CONTACT_ID, JOB_TITLE, DEPARTMENT_CODE, DEPARTMENT — Organizational contact attributes from HZ_ORG_CONTACTS.
• CONTACT_POINT_ID, CONTACT_POINT_TYPE, TELEPHONE_TYPE, PHONE_COUNTRY_CODE, PHONE_AREA_CODE, PHONE_NUMBER, PHONE_EXTENSION, FULL_PHONE_NUMBER — Primary phone contact point data from HZ_CONTACT_POINTS, with FULL_PHONE_NUMBER concatenating the components in a formatted string.
• PARTY_RELATIONSHIP_TYPE, PARTY_RELATIONSHIP_ID — The relationship classification and its identifier, useful for filtering by relationship role.
• COMPETITOR_FLAG, CUSTOMER_KEY, STATUS, SIC_CODE, FISCAL_YEAREND_MONTH, CURRENT_FY_REVENUE, TAX_REFERENCE — Organization-level attributes sourced from the O party alias for sales and account classification.

Common Use Cases and Queries

A typical use case is retrieving all contacts and their primary phone numbers for a specific customer account, for example a query filtering on PARTY_NUMBER or PARTY_ID to support TeleSales call-list generation or outbound campaign targeting. Address verification and location-based searches—such as locating a party at a specific street address—are also supported because ADDRESS1 through POSTAL_CODE are exposed. Integration interfaces that must push contact and phone data to external dialers or CRM systems frequently select from this view rather than reconstructing the multi-table TCA join.

Sample query:

SELECT party_name, full_name, full_phone_number, address1, city, state, postal_code
FROM ast_ls_cont_addr_v
WHERE status = 'A'
AND party_relationship_type = 'CONTACT_OF';

Because the ETRM metadata documents no explicit owner and no separately listed referenced base objects beyond those embedded in the view text, implementers should validate the view's deployment status in each environment. Where the view is not implemented, the documented SELECT text can be used to recreate equivalent functionality directly against the TCA tables.