Results for “ast_cust_acct_overview_v”

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

Overview

AST_CUST_ACCT_OVERVIEW_V is a TeleSales (AST) reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. As its name implies, the view presents a consolidated overview of a customer account, drawing the header-level attributes of the account together with a decoded description of the account status. It is a read-only presentation layer: it does not own data, does not participate in the transactional DML performed against HZ_CUST_ACCOUNTS, and exists to simplify queries issued by TeleSales forms, concurrent programs, and custom reporting or integration code. Because the view already joins the status lookup and exposes a human-readable status description, consumers avoid repeating lookup logic in their own SQL — a pattern that is particularly valuable in the telesales contact center context, where agents need fast, unambiguous account state information.

Underlying Base Objects

The view is defined over two documented referenced objects:

• HZ_CUST_ACCOUNTS (SYNONYM) — the Trading Community Architecture (TCA) customer account entity. This supplies the account identifiers, financial attributes, lifecycle dates, and the DFF attribute columns. The view aliases it as CUST_ACCT.
• AR_LOOKUPS (VIEW) — the Receivables lookup view over FND_LOOKUPS. The view aliases it as STATUS_LOOKUP and filters it with LOOKUP_TYPE = 'CODE_STATUS'.

The two objects are joined on the equality CUST_ACCT.STATUS = STATUS_LOOKUP.LOOKUP_CODE. This is an inner join, so an account whose STATUS value does not resolve to a CODE_STATUS lookup row will not appear in the result set. The view carries no ORG_ID filtering of its own; multi-org security is enforced by the calling application or by the caller's WHERE clause.

Key Columns

• CUST_ACCOUNT_ID — primary key of the customer account; the standard join key to HZ_CUST_ACCT_SITES_ALL and related TCA entities.
• STATUS — the raw lookup code from HZ_CUST_ACCOUNTS.STATUS (for example, 'A' or 'I').
• ACCOUNT_STATUS_DESCRIPTION — the decoded STATUS_LOOKUP.DESCRIPTION, the column most commonly requested by users searching on "status_lookup". Because the view materializes this join, report authors get the description without writing their own lookup join.
• ACCOUNT_NUMBER / ORIG_SYSTEM_REFERENCE — the user-facing account number and the originating system identifier used for cross-system reconciliation.
• PARTY_ID — link to HZ_PARTIES for party-level attributes.
• CUSTOMER_TYPE and CUSTOMER_CLASS_CODE — classification attributes used for segmentation.
• ACCOUNT_NAME, TAX_CODE, SUBCATEGORY_CODE — descriptive and tax configuration attributes.
• CURRENT_BALANCE — the account's current balance, useful in collections and credit workflows.
• ACCOUNT_ESTABLISHED_DATE, ACCOUNT_ACTIVATION_DATE, ACCOUNT_TERMINATION_DATE, SUSPENSION_DATE — lifecycle dates supporting aging and status analysis.
• HIGH_PRIORITY_INDICATOR, HIGH_PRIORITY_REMARKS — telesales prioritization flags.
• WRITE_OFF_ADJUSTMENT_AMOUNT, WRITE_OFF_PAYMENT_AMOUNT, WRITE_OFF_AMOUNT — write-off detail.
• ATTRIBUTE1–ATTRIBUTE20, ATTRIBUTE_CATEGORY — descriptive flexfield columns passed through from HZ_CUST_ACCOUNTS.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, APPLICATION_ID, CREATED_BY_MODULE, SOURCE_CODE — standard WHO and audit columns.

Several positional slots (for example, the columns following CUSTOMER_TYPE and TAX_CODE) are defined as NULL placeholders, retained for column-position stability. Developers should not rely on those positions.

Common Use Cases and Queries

The view is typically used for account status reporting, agent-facing telesales dashboards, and integration extracts where the friendly status description is required.

Listing active accounts for a given operating unit:

• SELECT cust_account_id, account_number, account_name, account_status_description FROM ast_cust_acct_overview_v WHERE status = 'A' AND org_id = :p_org_id;

Resolving the "status_lookup" request directly — obtaining the description behind a code:

• SELECT status, account_status_description FROM ast_cust_acct_overview_v WHERE cust_account_id = :p_account_id;

Accounts with outstanding balances and their lifecycle dates:

• SELECT account_number, account_name, current_balance, account_activation_date, suspension_date FROM ast_cust_acct_overview_v WHERE current_balance <> 0 AND org_id = :p_org_id ORDER BY current_balance DESC;

Join to the party for a full customer picture:

• SELECT v.account_number, p.party_name, v.account_status_description FROM ast_cust_acct_overview_v v, hz_parties p WHERE v.party_id = p.party_id AND v.status = 'A';

Because the STATUS column originates from HZ_CUST_ACCOUNTS and the description from AR_LOOKUPS, any account carrying a status code not defined under LOOKUP_TYPE = 'CODE_STATUS' will be silently excluded; validation of lookup completeness is therefore advisable before relying on this view as an exhaustive account inventory.