Results for “account_status_description”

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

Overview

AST_ACCT_RELATIONSHIP_V is a TeleSales (AST) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to retrieve related accounts for a customer account. In entity-relationship terms, a customer account stored in HZ_CUST_ACCOUNTS can be linked to one or more other customer accounts through the relationship table HZ_CUST_ACCT_RELATE; this view flattens that relationship into a single denormalized result set that joins the related account's descriptive attributes and lookup-code descriptions back onto each relationship row.

Because the underlying relationship data resides in the shared Trading Community Architecture (TCA) model rather than in an AST-specific table, the view serves as the standard read-only access point for TeleSales and other modules that need to present "related account" information without querying and joining the TCA tables directly. It preserves the full set of descriptive flexfield columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15), audit columns, OBJECT_VERSION_NUMBER, and the multi-tenant keys APPLICATION_ID and ORG_ID, making it suitable for both reporting and integration use.

Underlying Base Objects

The documented ETRM metadata lists three referenced base objects:

• AR_LOOKUPS (VIEW) — referenced twice, under the aliases STATUS_LOOKUP and RELATION_LOOKUP, to resolve the lookup codes STATUS and RELATIONSHIP_TYPE into their display descriptions.
• HZ_CUST_ACCOUNTS (SYNONYM) — supplies the related account's name and account number (ACCOUNT_NAME, ACCOUNT_NUMBER).
• HZ_CUST_ACCT_RELATE (SYNONYM) — the driving table, providing the relationship, reciprocal flag, status, audit, flexfield, and multi-tenant columns.

The view joins HZ_CUST_ACCT_RELATE to HZ_CUST_ACCOUNTS on RELATED_CUST_ACCOUNT_ID = CUST_ACCOUNT_ID, so the ACCOUNT_NAME and ACCOUNT_NUMBER returned describe the related (child) account, not the parent account identified by CUST_ACCOUNT_ID. STATUS_LOOKUP is joined on STATUS with LOOKUP_TYPE = 'CODE_STATUS'; RELATION_LOOKUP is joined on RELATIONSHIP_TYPE with LOOKUP_TYPE = 'RELATIONSHIP_TYPE' using an outer join (+), so a relationship row survives even when no matching relationship-type lookup exists.

Key Columns

• CUST_ACCOUNT_ID — the parent customer account that owns the relationship.
• RELATED_CUST_ACCOUNT_ID — the related (child) customer account.
• RELATED_ACCOUNT_NAME / RELATED_ACCOUNT_NUMBER — the name and number of the related account from HZ_CUST_ACCOUNTS.
• RELATIONSHIP_TYPE / RELATIONSHIP_DESCRIPTION — the relationship code and its AR_LOOKUPS description.
• CUSTOMER_RECIPROCAL_FLAG — indicates whether the relationship is reciprocal, i.e. whether the reverse relationship also exists between the two accounts. This is the column most frequently referenced in queries and is the term the user searched for.
• STATUS / RELATED_ACCOUNT_STATUS / ACCOUNT_STATUS_DESCRIPTION — the relationship status code and its CODE_STATUS description.
• ROW_ID — the ROWID of the underlying HZ_CUST_ACCT_RELATE row.
• Audit and flexfield columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, CREATED_BY_MODULE, ATTRIBUTE_CATEGORY and ATTRIBUTE1–15.
• APPLICATION_ID, ORG_ID — multi-tenant / operating-unit context columns.

Common Use Cases and Queries

Typical uses include displaying related-account hierarchies in TeleSales account screens, driving reports that compare reciprocal and non-reciprocal relationships, and integrating TCA relationship data into external systems.

List all related accounts for a given customer account:

• SELECT related_cust_account_id, related_account_name, related_account_number, relationship_type, relationship_description, customer_reciprocal_flag, related_account_status FROM apps.ast_acct_relationship_v WHERE cust_account_id = :p_cust_account_id;

Isolate reciprocal relationships only:

• SELECT cust_account_id, related_cust_account_id, relationship_type FROM apps.ast_acct_relationship_v WHERE customer_reciprocal_flag = 'Y';

Join the view to HZ_CUST_ACCOUNTS to obtain the parent account name alongside each related account:

• SELECT p.account_name parent_account, r.related_account_name, r.relationship_description FROM apps.hz_cust_accounts p, apps.ast_acct_relationship_v r WHERE p.cust_account_id = r.cust_account_id AND r.customer_reciprocal_flag = 'Y';

Because the view is a simple join over TCA tables, it inherits the security and performance characteristics of those tables; queries should generally be constrained by CUST_ACCOUNT_ID or ORG_ID to avoid full scans.