Search Results jai_cmn_customer_sites_v




Overview
Explore in AI Chat

Overview

APPS.JAI_CMN_CUSTOMER_SITES_V is a validity-checked (Status: VALID) reporting and integration view owned by the APPS schema and shipped as part of the JA — Asia/Pacific Localizations product family. Its purpose is to expose a simplified, filtered projection of Oracle Receivables / Trading Community Architecture (TCA) customer site data for consumption by the JA localization modules, most notably the country-specific address and tax-reporting components that require validated customer address assignments.

The view is significant because it does not simply flatten the standard TCA customer model. It applies an EXISTS filter against the JA localization table JAI_CMN_CUS_ADDRESSES, meaning only customer site uses that have a corresponding JA-registered customer address row are returned. This makes the view the canonical source for JA-localized customer site information rather than a generic TCA view. The four exposed columns (PARTY_SITE_ID, PARTY_SITE_CODE, ORG_ID, PARTY_ID) map directly onto identifiers used by localization extraction, reporting, and interface programs.

Underlying Base Objects

The view is defined over six documented base objects, all referenced through APPS synonyms:

The join path follows the standard TCA chain HZ_PARTIES → HZ_CUST_ACCOUNTS → HZ_CUST_ACCT_SITES_ALL → HZ_CUST_SITE_USES_ALL, with HZ_PARTY_SITES bridging the party-to-site relationship, and the JA table acting as a mandatory filter. The inline comment /* BUG#3360432 */ in the view text indicates a historical fix applied to the PARTY_SITE_ID join condition between HZ_PARTY_SITES and HZ_CUST_ACCT_SITES_ALL.

Key Columns

  • PARTY_SITE_ID — sourced from HZ_CUST_SITE_USES_ALL.SITE_USE_ID. Despite the name, this is the site use identifier, not the HZ_PARTY_SITES primary key.
  • PARTY_SITE_CODE — sourced from HZ_CUST_SITE_USES_ALL.LOCATION. This is the customer-facing location code commonly searched as "party_site_code" by users tracing JA localization records.
  • ORG_ID — sourced from HZ_CUST_ACCT_SITES_ALL.ORG_ID; the operating unit identifier, essential for multi-org (MOAC) filtering.
  • PARTY_ID — sourced from HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID. The alias is deliberate: downstream JA consumers treat the customer account identifier as the party reference key.

Note the naming asymmetry: PARTY_SITE_ID and PARTY_ID are customer-account identifiers, while PARTY_SITE_CODE is the location flexfield-style code. Consumers must not assume that these columns reconcile to HZ_PARTY_SITES or HZ_PARTIES keys without the intermediate join.

Common Use Cases and Queries

Typical usage includes JA-localized invoice and address validation reports, localization data extraction for statutory reporting, and cross-referencing a party_site_code back to its customer account and operating unit.

Basic lookup by site code:

SELECT party_site_id, party_site_code, org_id, party_id
  FROM apps.jai_cmn_customer_sites_v
 WHERE party_site_code = :p_site_code;

Operating-unit-scoped extraction:

SELECT party_site_id, party_site_code, party_id
  FROM apps.jai_cmn_customer_sites_v
 WHERE org_id = :p_org_id
 ORDER BY party_site_code;

Joining back to the customer account for reporting:

SELECT v.party_site_code, hca.account_number, v.org_id
  FROM apps.jai_cmn_customer_sites_v v,
       apps.hz_cust_accounts hca
 WHERE hca.cust_account_id = v.party_id;

Because the view enforces the EXISTS predicate against JAI_CMN_CUS_ADDRESSES, it returns only JA-registered address combinations. Queries expecting the full TCA site population should use standard HZ views instead. Additionally, since the view has no WHERE clause of its own beyond the exists filter, MOAC security is not automatically applied — callers must filter on ORG_ID explicitly, and appropriate grants on the APPS synonym are required for non-APPS access.