Search Results acct_relate




Overview
Explore in AI Chat

Overview

APPS.CSC_ACCT_RELATIONSHIP_V is a reporting and integration view in Oracle E-Business Suite (12.1.1 and 12.2.2) that exposes customer account relationship data from the Oracle Receivables and Oracle Trading Community Architecture (TCA) model. It is defined in the APPS schema and is primarily consumed by downstream applications, concurrent programs, and interfaces that need a denormalized, human-readable representation of the relationships that exist between customer accounts. For example, it associates a primary customer account with a related account and resolves coded values — the relationship type and the status — into their descriptive text via AR_LOOKUPS.

The code_status search term maps directly to this view: the STATUS column in HZ_CUST_ACCT_RELATE is joined to AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'CODE_STATUS'. This means the view is one of the standard mechanisms through which the meaning of a customer account relationship's code status is presented in reports and integrations.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, the view is defined over three referenced base objects:

Note that the relationship-type lookup is an outer join ((+)), whereas the status lookup is an inner join, so every row returned must have a valid CODE_STATUS lookup entry.

Key Columns

  • CUST_ACCOUNT_ID / RELATED_CUST_ACCOUNT_ID — the two accounts participating in the relationship.
  • ACCOUNT_NAME / ACCOUNT_NUMBER — descriptive identifiers of the related account.
  • RELATIONSHIP_TYPE and DESCRIPTION (relation_lookup) — the code and its meaning.
  • STATUS and DESCRIPTION (status_lookup, from CODE_STATUS) — the coded status and its description.
  • CUSTOMER_RECIPROCAL_FLAG — indicates whether the relationship is reciprocal.
  • BILL_TO_FLAG / SHIP_TO_FLAG — whether the related account is used as a bill-to or ship-to site.
  • Audit and DFF columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, ATTRIBUTE_CATEGORY, and ATTRIBUTE1–ATTRIBUTE15.

Common Use Cases and Queries

Typical uses include customer hierarchy reporting, relationship validation in Order Management and Receivables, and integration extracts that require resolved status descriptions rather than raw codes.

SELECT cust_account_id,
       related_cust_account_id,
       account_number,
       account_name,
       relationship_type,
       status,
       description
FROM   apps.csc_acct_relationship_v
WHERE  status = 'A';

SELECT r.account_number  related_account,
       r.description     status_description,
       r.bill_to_flag,
       r.ship_to_flag
FROM   apps.csc_acct_relationship_v r
WHERE  r.cust_account_id = :p_cust_account_id
AND    r.customer_reciprocal_flag = 'Y';

Because all joins are to TCA and AR_LOOKUPS only, the view performs well for point lookups by account, but broad scans should be filtered by CUST_ACCOUNT_ID or STATUS to avoid full scans of HZ_CUST_ACCT_RELATE.