Results for “company_site_code”

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

Overview

PN_COMPANY_SITES_ALL is the Oracle EBS Property Manager (PN) table that stores site information for a service provider, more commonly referred to as a supplier or vendor site. Within Oracle EBS 12.1.1 and 12.2.2, this table acts as the operational anchor for a company's (vendor's) physical or logical site locations, enabling Property Manager functionality such as lease roles, contact assignments, and address resolution tied to a specific trading partner site. It resides in the PN schema, holds 29 documented columns, and is classified as VALID in the ETRM 12.2.2 physical schema. The table is multi-org enabled, as evidenced by the ORG_ID column, and carries the standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN). Under the heuristic Data Vault classification supplied in the metadata, PN_COMPANY_SITES_ALL is identified as hub-leaning, meaning it most naturally models as a hub entity in a Data Vault construct — a stable, uniquely keyed business concept (the company site) around which links to company, address, and contact satellites are built.

Key Information Stored

The surrogate primary key is COMPANY_SITE_ID, enforced by the primary key constraint PN_COMPANY_SITES_PK and mirrored by the unique index PN_COMPANY_SITES_U1. This makes COMPANY_SITE_ID the documented business-key candidate for this table. Among the most significant columns:

• COMPANY_ID — foreign key to PN_COMPANIES_ALL, identifying the owning service provider company.
• ADDRESS_ID — foreign key to PN_ADDRESSES_ALL, resolving the physical address of the site.
• COMPANY_SITE_CODE — the site code used for identification within the company.
• NAME — the descriptive site name.
• ENABLED_FLAG — indicates whether the site is active and usable.
• LEASE_ROLE_TYPE — classifies the role the company plays at this site (for example, landlord or tenant) in Property Manager lease processing.
• ORG_ID — the operating unit attribute enabling multi-org security and partitioning.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns for customer-specific extension data.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the WHO audit columns for row-level tracking.

Common Use Cases and Queries

Typical uses include supplier site reporting, validating active sites per company or operating unit, and joining sites to contacts for correspondence. A representative query lists enabled sites joined to their company and address:

• SELECT pcs.company_site_id, pcs.name, pcs.company_site_code, pcs.enabled_flag, pc.company_name FROM pn_company_sites_all pcs JOIN pn_companies_all pc ON pc.company_id = pcs.company_id WHERE pcs.org_id = :p_org_id AND pcs.enabled_flag = 'Y';
• Joining PN_SITES to PN_ADDRESSES_ALL on pcs.address_id = pa.address_id to report site location details.
• Aggregating contact counts per site via PN_CONTACTS_ALL on company_site_id.
• Filtering by LEASE_ROLE_TYPE to isolate landlord or tenant sites for lease-related reporting.

Related Objects

The most significant related objects, based on the documented foreign key relationships, are:

• PN_COMPANIES_ALL — parent company; join on PN_COMPANY_SITES_ALL.COMPANY_ID = PN_COMPANIES_ALL.COMPANY_ID.
• PN_ADDRESSES_ALL — address reference; join on ADDRESS_ID.
• PN_CONTACTS_ALL — child contacts; join on COMPANY_SITE_ID.
• PN_CONTACT_ASSIGNMENTS_ALL — contact assignments referencing COMPANY_SITE_ID.
• PN_CONTACT_ASSIGN_HISTORY — historical contact assignments referencing COMPANY_SITE_ID.
• MSC_COMPANY_SITES, MSC_COMPANY_SITE_ID_LID, MSC_ITEM_EXCEPTIONS, MSC_EXCEPTION_THRESHOLD_VALUES, MSC_X_EXCEPTION_DETAILS — Supply Chain (Planning/MSC) objects that consume the company site identifier.