Results for “related_customer_number”

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

Overview

AR_CUSTOMER_RELATIONSHIPS_V is an APPS-owned view in the Oracle Receivables (AR) module that exposes customer-to-customer relationship data maintained in the Oracle Trading Community Architecture (TCA) and HZ schema. It is documented as a Release 11.5-era object, though the view remains valid and queryable in Oracle EBS 12.1.1 and 12.2.2 environments. The view presents the relationship records stored in HZ_CUST_ACCT_RELATE, enriched with the related customer's party name, account number, and the lookup meaning associated with each relationship type.

The view is primarily used for reporting and integration. Because it resolves foreign keys into human-readable values—specifically the relationship type meaning—it is useful for functional users and downstream interfaces that must present or consume customer relationship hierarchies without joining TCA tables directly. In Receivables contexts, relationships govern linked accounts, shared bill-to and ship-to arrangements, and reciprocal relationships between customers.

Underlying Base Objects

The documented view text joins four objects:

• HZ_CUST_ACCT_RELATE (SYNONYM) — the primary base table. Each row represents one customer account relationship. The view aliases this table as REL and drives the result set from it.
• HZ_CUST_ACCOUNTS (SYNONYM) — provides the account number of the related customer (CUST.ACCOUNT_NUMBER), aliased as CUSTOMER_NUMBER.
• HZ_PARTIES (SYNONYM) — provides the party name of the related customer (PARTY.PARTY_NAME), aliased as CUSTOMER_NAME.
• AR_LOOKUPS (VIEW) — a Receivables lookup view. It is joined on LOOKUP_CODE = RELATIONSHIP_TYPE with the restriction LOOKUP_TYPE = 'RELATIONSHIP_TYPE', supplying the L.MEANING column that the view surfaces as relationship type meaning.

The join conditions link REL.RELATED_CUST_ACCOUNT_ID to CUST.CUST_ACCOUNT_ID, CUST.PARTY_ID to PARTY.PARTY_ID, and the lookup code to the AR_LOOKUPS lookup. All joins are inner joins, so rows with missing parties, accounts, or lookup codes are excluded.

Key Columns

Prominent columns include:

• CUSTOMER_ID and RELATED_CUSTOMER_ID — the customer account identifiers on each side of the relationship.
• RELATED_CUSTOMER_NAME and RELATED_CUSTOMER_NUMBER — the party name and account number of the related customer.
• RELATIONSHIP_TYPE_CODE and RELATIONSHIP_TYPE — the lookup code for the relationship, typically the reciprocal partner type.
• RELATIONSHIP_TYPE_MEANING — the translated meaning of the relationship type, derived from AR_LOOKUPS.MEANING. This is the column most commonly sought by users searching for the relationship type meaning.
• STATUS, COMMENTS, CUSTOMER_RECIPROCAL_FLAG, BILL_TO_FLAG, and SHIP_TO_FLAG — status, free-text comments, and flags controlling the relationship's behaviour for billing and shipping.
• Standard concurrency and audit columns plus fifteen generic ATTRIBUTE columns and ATTRIBUTE_CATEGORY.

Common Use Cases and Queries

Typical scenarios include reviewing all customers related to a given account, analysing reciprocal relationships, auditing bill-to and ship-to sharing, and feeding downstream applications with decoded relationship types.

• List relationships, meanings, and related customers for a specific account.
• Identify reciprocal or bill-to-sharing relationships.
• Report on relationship types and their distribution.

Sample query:

SELECT relationship_type_code,
       relationship_type_meaning,
       related_customer_number,
       related_customer_name,
       status,
       bill_to_flag,
       ship_to_flag
FROM   apps.ar_customer_relationships_v
WHERE  customer_id = :p_customer_id;

Because the view performs the lookup decode, no additional join to AR_LOOKUPS is required to obtain the relationship type meaning.