Results for “as_customer_relationships_v”

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

Overview

AS_CUSTOMER_RELATIONSHIPS_V is a reporting view in the Oracle E-Business Suite Sales Foundation (AS) module that exposes customer-to-customer relationship data. It presents the reciprocal and directional links defined between customer records — for example, parent/subsidiary, bill-to/ship-to associations, or other user-defined relationship types — alongside the descriptive names and numbers of both the primary and the related customer. Its principal role is to provide a denormalized, human-readable surface over the underlying relationship table so that reports, concurrent programs, and integration extracts can resolve related customers without performing the multiple joins themselves.

The view is documented in ETRM 12.2.2 with no owner recorded and no referenced base objects explicitly listed, although the view text itself clearly identifies its sources. The metadata notes that it is “Not implemented in this database” in the reference environment, meaning the definition is supplied for documentation and dependency analysis rather than as a guarantee of presence in every instance. Notably, the view exposes the CUSTOMER_RECIPROCAL_FLAG column, which is the attribute most commonly sought by users researching reverse relationship behavior on customer records.

Underlying Base Objects

The view is defined over three objects:

• RA_CUSTOMER_RELATIONSHIPS — the driving table aliased REL, holding one row per relationship.
• RA_CUSTOMERS CUST1 — the primary customer, joined on REL.CUSTOMER_ID = CUST1.CUSTOMER_ID.
• RA_CUSTOMERS CUST2 — the related customer, joined on REL.RELATED_CUSTOMER_ID = CUST2.CUSTOMER_ID.
• AR_LOOKUPS LOOK1 — an outer-joined lookup on RELATIONSHIP_TYPE = LOOKUP_CODE with LOOKUP_TYPE = 'RELATIONSHIP_TYPE', supplying the translated meaning of the relationship type code.

Because the lookup join is outer (+), relationships whose type code has no lookup entry are still returned, with a null meaning. Both customer joins are inner joins, so a relationship row is only surfaced when both the primary and related customer exist in RA_CUSTOMERS.

Key Columns

• ROW_ID — the row identifier from RA_CUSTOMER_RELATIONSHIPS.
• CUSTOMER_ID / CUSTOMER_NUMBER / CUSTOMER_NAME — identity of the primary customer (aliased NAME and CUSTOMER_NUMBER in the column list).
• RELATED_CUSTOMER_ID / RELATED_CUSTOMER_NUMBER / RELATED_NAME — identity of the related customer.
• RELATIONSHIP_TYPE_CODE / RELATIONSHIP_TYPE — the stored type code and its lookup meaning.
• CUSTOMER_RECIPROCAL_FLAG — indicates whether the relationship is reciprocal, i.e., whether an equivalent reverse relationship is maintained automatically for the related customer.
• STATUS_CODE / STATUS and COMMENTS — lifecycle state and free-text notes on the relationship.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• Concurrent columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the DFF/extension columns carried from the base table.

Common Use Cases and Queries

Typical uses include customer hierarchy reporting, reciprocal-relationship verification, and data extracts feeding master data management or CRM synchronization. A common query lists all relationships for a given customer together with the related party:

• SELECT customer_number, name, relationship_type, related_customer_number, related_name, customer_reciprocal_flag FROM as_customer_relationships_v WHERE customer_id = :p_customer_id;
• SELECT * FROM as_customer_relationships_v WHERE customer_reciprocal_flag = 'Y'; to isolate reciprocal links.
• Join the view to RA_CUSTOMERS or RA_SITE_USES when building consolidated customer-hierarchy extracts, or filter by RELATIONSHIP_TYPE to restrict output to a single relationship semantic.

Because the view carries no documented owner and is not implemented in every reference database, consumers should confirm its presence and privileges in the target instance before relying on it in production reports or interfaces.