Search Results pn_space_assign_emp_pk

Overview

PN_SPACE_ASSIGN_EMP_ALL is a Property Manager (PN) transaction table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores employee space assignment information. It records which employees (or persons) occupy specific locations, the project and task charged for that occupancy, the period of the assignment, and the resulting allocation and utilization metrics. In facilities and real estate management, this table is the operational bridge between physical floor space inventory and the human occupants assigned to it, and it supports cost recovery, occupancy analysis, and space utilization reporting.

The "_ALL" suffix indicates the presence of the ORG_ID column, meaning the table participates in Oracle's multi-org (operating unit) data partitioning model. The table is owned by the PN schema and consists of 36 documented columns in the 12.2.2 physical schema. Based on the foreign key structure mined from the metadata, the heuristic Data Vault classification for this table is a link: it resolves and connects business entities—person, location, project, and task—into an associative relationship. This classification is a modeling suggestion rather than an enforced database construct.

Key Information Stored

The primary key of the table is the surrogate column EMP_SPACE_ASSIGN_ID, enforced through the constraint PN_SPACE_ASSIGN_EMP_PK. A separate unique index, PN_SPACE_ASSIGN_EMP_U1, is also defined on EMP_SPACE_ASSIGN_ID, making it the documented business-key candidate and the canonical identifier for a single space assignment record.

The most significant columns include:

• EMP_SPACE_ASSIGN_ID — Surrogate primary key uniquely identifying each employee space assignment.
• PERSON_ID — The employee or person assigned to the space.
• LOCATION_ID — The physical location or space allocated to the person.
• PROJECT_ID — Foreign key to PA_PROJECTS_ALL, identifying the project associated with the assignment for cost-charging purposes.
• TASK_ID — Foreign key to PA_TASKS, identifying the specific project task charged.
• EMP_ASSIGN_START_DATE and EMP_ASSIGN_END_DATE — The effective date range of the assignment, enabling point-in-time occupancy queries.
• COST_CENTER_CODE — The cost center bearing the occupancy cost.
• ALLOCATED_AREA and ALLOCATED_AREA_PCT — The assigned floor area and the percentage of the space allocated to the person.
• UOM_CODE — Unit of measure for the allocated area.
• UTILIZED_AREA — The area actually utilized, supporting efficiency analysis.
• EMP_SPACE_COMMENTS — Free-text notes on the assignment.
• ORG_ID — Operating unit identifier supporting multi-org security and partitioning.
• SOURCE — Origin indicator for the record, used during interface and conversion processing.

The table also carries the standard EBS audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and fifteen descriptive flexfield columns (ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15).

Common Use Cases and Queries

Typical reporting scenarios include occupancy rosters by location, space chargebacks to projects and tasks, and utilization trend analysis. A common query joins the assignment to its project to produce chargeback detail:

• SELECT s.emp_space_assign_id, s.person_id, s.location_id, p.segment1 project_number, s.allocated_area, s.allocated_area_pct, s.emp_assign_start_date, s.emp_assign_end_date FROM pn_space_assign_emp_all s, pa_projects_all p WHERE s.project_id = p.project_id AND s.org_id = :org_id;
• Point-in-time occupancy: filter with EMP_ASSIGN_START_DATE <= :as_of AND (EMP_ASSIGN_END_DATE IS NULL OR EMP_ASSIGN_END_DATE >= :as_of).
• Aggregate utilization: SELECT location_id, SUM(allocated_area) total_allocated, SUM(utilized_area) total_utilized FROM pn_space_assign_emp_all GROUP BY location_id;
• Task-level cost analysis: join to PA_TASKS on TASK_ID to attribute occupancy cost to a specific project task.

Because the table is multi-org enabled, every custom query should be restricted by ORG_ID to respect operating unit security.

Related Objects

The table participates in the following documented relationships:

• PA_PROJECTS_ALL — Referenced via PN_SPACE_ASSIGN_EMP_ALL.PROJECT_ID; supplies project context for occupancy charging.
• PA_TASKS — Referenced via PN_SPACE_ASSIGN_EMP_ALL.TASK_ID; supplies task-level detail within the project.
• PN_EMP_SPACE_ASSIGN_ITF — Interface table that references EMP_SPACE_ASSIGN_ID back to this table, used for bulk import and conversion of assignment data.
• PN_SPACE_ASSIGN_EMP_PK — The primary key constraint defined on EMP_SPACE_ASSIGN_ID.
• PN_SPACE_ASSIGN_EMP_U1 — The unique index covering EMP_SPACE_ASSIGN_ID, serving as the documented business-key candidate.

Additional PN location and person-related objects are commonly joined through LOCATION_ID and PERSON_ID for reporting, though these joins are not documented as foreign keys in the ETRM metadata above.