Results for “pn_addresses_all”

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

Overview

The PN.PN_ADDRESSES_ALL table is a core master data object within the Oracle E-Business Suite Property Manager (PN) module. As described in the ETRM metadata, it stores "information on locations and service providers." In practice, this table serves as the centralized address repository for the Property Manager application, holding structured address records that are subsequently referenced by company sites, locations, and other property-related entities.

From a Data Vault modeling perspective, the ETRM schema-mining heuristic classifies PN_ADDRESSES_ALL as hub-leaning. This suggests the table behaves as a business-key hub around which dependent entities cluster: the surrogate ADDRESS_ID identifies each unique address, while the two documented foreign keys — PN_COMPANY_SITES_ALL and PN_LOCATIONS_ALL — both point back to it, confirming its role as an anchor referenced by multiple downstream transactional and operational objects. Rather than a link (which records relationships between hubs) or a satellite (which stores descriptive attributes), the structure indicates an entity hub with attached descriptive columns.

Key Information Stored

The table contains 51 documented columns. The most operationally significant are:

• ADDRESS_ID — the surrogate primary key (PN_ADDRESSES_PK) and also the business-key candidate, as reflected in the unique index PN_ADDRESSES_U1. This is the value propagated to all referencing tables.
• ADDRESS_LINE1 through ADDRESS_LINE4 — the free-text address lines forming the street-level portion of the record.
• CITY, STATE, PROVINCE, COUNTY — geographic hierarchy attributes supporting regional reporting and tax/territory logic.
• ZIP_CODE and COUNTRY — postal code and country identifiers used in address validation and territory assignment.
• TERRITORY_ID — links the address to territory definitions, relevant for sales and service coverage reporting.
• ADDRESS_STYLE — indicates the formatting convention applied to the address layout.
• ORG_ID — the multi-org operating unit discriminator, enabling the table to function correctly under Oracle's multi-organization architecture.
• ATTRIBUTE_CATEGORY with ATTRIBUTE1–ATTRIBUTE15, and the parallel ADDR_ATTRIBUTE_CATEGORY with ADDR_ATTRIBUTE1–ADDR_ATTRIBUTE15 — two sets of descriptive flexfield (DFF) columns allowing customer-specific extensions without schema modification.
• Standard who-columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN provide audit and concurrency control.

Common Use Cases and Queries

Typical reporting and integration scenarios involve resolving an address_id held on a company site or location into its full address text. A representative query joining the address to its primary consumer is:

• SELECT a.address_id, a.address_line1, a.city, a.state, a.zip_code, a.country
FROM pn_addresses_all a, pn_company_sites_all c
WHERE a.address_id = c.address_id AND a.org_id = :org_id;
• Service-provider and location rosters: join PN_LOCATIONS_ALL to PN_ADDRESSES_ALL to report each maintained location with its formatted address.
• Territory coverage analysis: group addresses by TERRITORY_ID to assess geographic distribution of sites and providers.
• DFF extraction: query the ATTRIBUTE* or ADDR_ATTRIBUTE* columns where populated to surface customer-defined address metadata.
• Data-quality audits: identify records with missing ADDRESS_LINE1, CITY, or ZIP_CODE as part of address cleansing initiatives.

Related Objects

The following objects depend on or directly reference PN_ADDRESSES_ALL per the documented foreign-key relationships:

• PN_COMPANY_SITES_ALL — references via ADDRESS_ID; represents company site records that consume address data.
• PN_LOCATIONS_ALL — references via ADDRESS_ID; holds location definitions linked to addresses for service-provider and operational purposes.
• PN_ADDRESSES_PK and PN_ADDRESSES_U1 — the primary key constraint and unique index on ADDRESS_ID that enforce entity integrity and confirm the business-key candidate.

Any additional dependent tables within the Property Manager module that carry an ADDRESS_ID foreign key should be treated as downstream consumers of this hub, though only the two relationships above are formally documented in the ETRM metadata.