Results for “ast_cust_acct_sites_v”

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

Overview

AST_CUST_ACCT_SITES_V is a TeleSales (AST) view owned by the APPS schema in Oracle E-Business Suite. It is a denormalized reporting construct that consolidates customer account site information from the Receivables and Trading Community (HZ) model, joining account sites to their physical locations, party sites, territories, and status lookup meanings in a single queryable object. Its purpose is to shield TeleSales and downstream reporting logic from the multi-table joins required to reconstruct a complete customer site record, presenting an address-ready result set that combines site identifiers, party relationships, flags, and descriptive geography in one row.

Because the view restricts its output to sites with a status of Active or Inactive, it is intended for operational reporting and integration scenarios where only live or intentionally retired sites are relevant. It is widely used in TeleSales inquiry screens, customer data extracts, and custom concurrent programs that need site-level detail without traversing HZ_CUST_ACCT_SITES and HZ_PARTY_SITES directly.

Underlying Base Objects

The view is defined over five documented base objects:

• HZ_CUST_ACCT_SITES (synonym) — the driving table supplying customer account site identifiers, status, and the BILL_TO_FLAG, MARKET_FLAG, and SHIP_TO_FLAG usage indicators, along with DFF attributes and audit columns.
• HZ_PARTY_SITES (synonym) — joined on PARTY_SITE_ID to establish the party-site association.
• HZ_LOCATIONS (synonym) — joined via PARTY_SITES.LOCATION_ID to supply the address elements (ADDRESS1–4, CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY, COUNTRY).
• FND_TERRITORIES_TL (synonym) — an outer join on COUNTRY = TERRITORY_CODE, filtered by USERENV('LANG'), providing localized territory short name.
• AR_LOOKUPS (view) — joined on LOOKUP_TYPE = 'CODE_STATUS' to translate STATUS into a descriptive STATUS_MEAN.

The outer joins on territory and the inner joins on AR_LOOKUPS, party sites, and locations mean the view returns only sites that resolve to a valid location and status code. The status filter (STATUS IN ('A','I')) enforces the Active/Inactive restriction at the view level.

Key Columns

• CUST_ACCT_SITE_ID / CUST_ACCOUNT_ID / PARTY_SITE_ID — primary site key, parent account, and party-site linkage.
• ORG_ID — operating unit identifier, essential for multi-org secured queries.
• STATUS / STATUS_MEAN — the raw status code and its decoded meaning from AR_LOOKUPS.
• BILL_TO_FLAG, MARKET_FLAG, SHIP_TO_FLAG — indicate the business purposes for which the site is enabled. The MARKET_FLAG specifically denotes a marketing-designated site, relevant to TeleSales prospecting and campaign targeting.
• SERVICE_TERRITORY_ID / TERRITORY_ID / TERRITORY_SHORT_NAME — territory assignments and the localized territory abbreviation.
• Address columns (ADDRESS1–4, CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY, COUNTRY, COUNTRY_NAME) — full postal address.
• Audit and DFF columns — LAST_UPDATE_DATE, CREATED_BY, and ATTRIBUTE1–20 for descriptive flexfield data.

Common Use Cases and Queries

Typical uses include identifying marketable sites, extracting active site addresses for a given account or operating unit, and listing bill-to or ship-to sites.

To retrieve market-flagged active sites for an operating unit:

SELECT cust_acct_site_id, cust_account_id, address1, city, postal_code, market_flag FROM apps.ast_cust_acct_sites_v WHERE market_flag = 'Y' AND status = 'A' AND org_id = :p_org_id;

To list all active sites with decoded status and territory:

SELECT cust_acct_site_id, status_mean, territory_short_name, bill_to_flag, ship_to_flag FROM apps.ast_cust_acct_sites_v WHERE status = 'A' ORDER BY cust_acct_site_id;

Because ORG_ID is exposed, queries should apply Multi-Org security or explicit org filters to avoid cross-operating-unit leakage.