Results for “csc_cust_acct_sites_v”

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

Overview

The CSC_CUST_ACCT_SITES_V view is a Customer Care (CSC) module database object owned by the APPS schema and defined over the Oracle E-Business Suite Trading Community Architecture (TCA) customer model. It presents a consolidated, reporting-friendly projection of customer account sites, joining account site records to their assigned party sites, physical location addresses, and the corresponding country territory definition. Because it flattens the multi-table TCA structure into a single row per customer account site, the view is well suited to concurrent-program extracts, Discoverer or BI Publisher reports, and integration interfaces that must resolve a customer site to its mailing address and territory without reproducing the underlying join logic.

The view is significant in the context of the query term service_territory_id. Unlike the base HZ_CUST_ACCT_SITES table, which stores only SERVICE_TERRITORY_ID, this view additionally exposes TERRITORY_ID and the joined TERRITORY_SHORT_NAME sourced from the territory lookup, allowing consumers to obtain both the identifier and a human-readable territory label in one pass.

Underlying Base Objects

The view is defined over the following documented objects:

• HZ_CUST_ACCT_SITES (synonym to the TCA customer account site entity) — the driving table, supplying site identifiers, status, business-purpose flags, addresses, DFF attributes, and the audit columns.
• HZ_PARTY_SITES (synonym) — joined on PARTY_SITE_ID, supplying PARTY_SITE_NAME.
• HZ_LOCATIONS (synonym) — joined on LOCATION_ID, supplying the address components and country code.
• FND_TERRITORIES_VL (view) — joined on LOCATIONS.COUNTRY = TERRITORY.TERRITORY_CODE, supplying the territory short name (rendered as the COUNTRY_NAME column).
• HR_OPERATING_UNITS (view) — documented as a referenced base object, consistent with the ORG_ID column exposing the operating unit context under multi-org security.

The join is an inner join across account sites, party sites, locations, and territories; a customer account site whose location has no matching territory row will not appear in the result set.

Key Columns

• CUST_ACCT_SITE_ID / CUST_ACCOUNT_ID / PARTY_SITE_ID — primary identifiers linking the site to its account and party site.
• ORG_ID — operating unit that owns the record; governs multi-org access.
• STATUS — active/inactive indicator for the site.
• BILL_TO_FLAG, SHIP_TO_FLAG, MARKET_FLAG — business-purpose flags indicating the role of the site.
• SERVICE_TERRITORY_ID — the service territory assigned to the account site, used for territory-based routing and coverage.
• TERRITORY_ID — the territory identifier carried on the account site record.
• TERRITORY_SHORT_NAME — the territory/country short name resolved from FND_TERRITORIES_VL.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY, COUNTRY_NAME — the resolved physical address and country description.
• PARTY_SITE_NAME — descriptive name of the party site.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — descriptive flexfield context and segments.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and LANGUAGE.

Common Use Cases and Queries

Typical uses include listing customer sites for a given account with their addresses, identifying bill-to and ship-to sites, extracting site data for data-warehouse loading, and resolving service_territory_id to a readable territory name.

Retrieve all sites for a customer account:

SELECT cust_acct_site_id, cust_account_id, party_site_name,
       address1, city, postal_code, country_name,
       service_territory_id, territory_id, territory_short_name
FROM   apps.csc_cust_acct_sites_v
WHERE  cust_account_id = :p_cust_account_id;

Filter to active ship-to sites and their service territories within an operating unit:

SELECT cust_acct_site_id, party_site_name, city, country_name,
       service_territory_id, territory_short_name
FROM   apps.csc_cust_acct_sites_v
WHERE  org_id = :p_org_id
AND    status = 'A'
AND    ship_to_flag = 'Y';

Report sites grouped by service territory:

SELECT service_territory_id, COUNT(*) site_count
FROM   apps.csc_cust_acct_sites_v
GROUP  BY service_territory_id;