Results for “as_cust_relationships_v”

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

Overview

AS_CUST_RELATIONSHIPS_V is a reporting view in the Oracle E-Business Suite Sales Foundation (AS) product family. It presents customer-to-customer relationship information maintained in the Oracle Trading Community Architecture (TCA) model, exposing both sides of each relationship — the subject party and the object party — together with the relationship type and the descriptive attributes of the relationship record itself. Its principal value is that it flattens the underlying TCA relationship structure into a single, human-readable row per relationship, resolving numeric party identifiers into party names and party numbers, and resolving relationship type codes into their translated meanings. This makes the view suitable for operational reporting, data extracts, and integration interfaces that need to publish or reconcile customer hierarchies without joining multiple TCA tables directly.

Because the view is not implemented as a physical database object in every environment (the ETRM documentation records it as "Not implemented in this database" for the source system), it should be treated as a logical reporting definition rather than a guaranteed object in any given schema. Where it is deployed, it is typically consumed by custom reports, BI Publisher data models, and inbound/outbound interfaces in Oracle EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view text is defined over four base objects:

• HZ_PARTY_RELATIONSHIPS REL — the driving table holding one row per relationship, including the subject party, object party, relationship type, directionality, and effective dates.
• AR_LOOKUPS LOOK1 — joined with an outer join (LOOK1.LOOKUP_CODE(+)) on PARTY_RELATIONSHIP_TYPE, restricted to LOOKUP_TYPE = 'RELATIONSHIP_TYPE', to supply the decoded relationship type meaning.
• HZ_PARTIES CUST1 — aliased as the subject party, joined on REL.SUBJECT_ID = CUST1.PARTY_ID.
• HZ_PARTIES CUST2 — aliased as the object party, joined on REL.OBJECT_ID = CUST2.PARTY_ID.

The ETRM metadata records no referenced base objects beyond the view text itself, so the joins above are the authoritative definition of the view's lineage.

Key Columns

• ROW_ID, PARTY_RELATIONSHIP_ID — the unique key of the underlying relationship record.
• SUBJECT_PARTY_ID / OBJECT_PARTY_ID — the party identifiers on each side of the relationship. This pair is the relevant association when a user searches for "subject_party_name."
• SUBJECT_PARTY_NAME — the party name of the subject side (from HZ_PARTIES CUST1); the column users most often seek under the label subject_party_name.
• PARTY_NUMBER / OBJECT_PARTY_NUMBER — the party numbers for the subject and object parties respectively.
• OBJECT_PARTY_NAME — the party name on the object side of the relationship.
• RELATIONSHIP_TYPE_CODE / RELATIONSHIP_TYPE — the raw lookup code (PARTY_RELATIONSHIP_TYPE) and its decoded meaning.
• START_DATE / END_DATE — the effective period of the relationship.
• DIRECTIONAL_FLAG, COMMENTS — directionality indicator and free-text notes.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — the standard descriptive flexfield columns.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus the concurrent program columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical uses include customer hierarchy and "related accounts" reporting, data migration and reconciliation extracts between EBS and external CRM or MDM systems, and supporting 360-degree customer views in custom OAF pages or BI Publisher reports. Because the view already resolves party names, downstream reports avoid repetitive TCA joins.

Retrieving the subject party name for a given customer:

• SELECT subject_party_name, object_party_name, relationship_type, start_date, end_date FROM as_cust_relationships_v WHERE object_party_id = :party_id;

Listing all relationships where a party appears on either side:

• SELECT subject_party_name, relationship_type, object_party_name FROM as_cust_relationships_v WHERE subject_party_id = :party_id OR object_party_id = :party_id;

Filtering to currently active relationships only:

• SELECT subject_party_name, object_party_name, relationship_type FROM as_cust_relationships_v WHERE TRUNC(SYSDATE) BETWEEN NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE);

As with all EBS views, callers should respect operating unit and security context requirements and confirm the view's presence in the target instance, since the ETRM metadata indicates it may not be implemented in every database.