Search Results jai_cmn_cust_address_v




Overview
Explore in AI Chat

Overview

JAI_CMN_CUST_ADDRESS_V is a database view owned by the APPS schema within the Oracle E-Business Suite Asia/Pacific Localizations (JA) product family. Its status is VALID in both 12.1.1 and 12.2.2. The view presents a unified address directory that reconciles two distinct sources of address data: customer addresses held in the Oracle Receivables / Trading Community Architecture (HZ) model, and internal organization (inventory) addresses held in Oracle HR and the JA localization tables. Its primary role is to provide downstream JA localization features — most notably tax reporting, legal document generation, and localization-specific address formatting — with a single queryable interface that can return either a customer address or an organization address depending on the row, distinguished by the ATTRIBUTE1 discriminator column.

Underlying Base Objects

The view is implemented as a UNION ALL of two SELECT blocks. The first block joins HZ_LOCATIONS (synonym for the TCA location master), HZ_PARTY_SITES (party-site association), HZ_CUST_ACCT_SITES_ALL (customer account site assignments, including the operating unit / ORG_ID), and JAI_CMN_CUS_ADDRESSES (the JA localization linkage between a customer and an address identifier). This branch produces rows with ATTRIBUTE1 set to 'CUSTOMER'. The second block joins HR_LOCATIONS (the Oracle HR locations view) against JAI_CMN_INVENTORY_ORGS, the JA localization table that maps inventory organizations to locations, and produces rows with ATTRIBUTE1 set to 'ORGANIZATION'. Documented referenced objects also include the HR_GENERAL package, which supplies organizational context such as business group and operating unit derivation. An earlier UNION branch that concatenated address line segments was commented out (EBIZ-00190/00191); the current form exposes separate ADDRESS_LINE_1/2/3 columns.

Key Columns

  • ID — For customer rows this is HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID; for organization rows it is HR_LOCATIONS.LOCATION_ID. Consumers must disambiguate using ATTRIBUTE1.
  • ADDRESS1, ADDRESS2, ADDRESS3 — Address line components sourced from HZ_LOCATIONS for customers and HR_LOCATIONS for organizations. These were separated out under EBIZ-00191 rather than concatenated.
  • CUSTOMER_ID — Populated only for customer rows (JAI_CMN_CUS_ADDRESSES.CUSTOMER_ID); NULL for organization rows.
  • ATTRIBUTE1 — The row-type discriminator: 'CUSTOMER' or 'ORGANIZATION'.
  • ORGANIZATION_ID — Populated only for organization rows via JAI_CMN_INVENTORY_ORGS.ORGANIZATION_ID; set to NULL for customers.
  • OPERATING_UNIT — Populated for customer rows from HZ_CUST_ACCT_SITES_ALL.ORG_ID, which is the operating unit owning the customer account site; NULL for organization rows. This is the column most relevant to searches on "operating_unit", since it exposes the ORG_ID in a friendly form.

Common Use Cases and Queries

Typical usage includes localization tax setup, invoice and legal-document address resolution, and cross-validation reports that must display both customer and internal organization addresses in one result set. A frequent pattern filters by operating unit to isolate addresses belonging to a single business unit.

SELECT id,
       address1,
       address2,
       address3,
       customer_id,
       attribute1,
       organization_id,
       operating_unit
FROM   apps.jai_cmn_cust_address_v
WHERE  attribute1 = 'CUSTOMER'
AND    operating_unit = :p_org_id;

To obtain all organization-side addresses regardless of operating unit, query with ATTRIBUTE1 = 'ORGANIZATION' and join ORGANIZATION_ID to HR_OPERATING_UNITS or org definitions. Because the UNION ALL can return a location twice (once as a customer site and once as an organization), reports should always constrain on ATTRIBUTE1 as well as the relevant ID column to avoid duplication.