Search Results pn_space_allocations




Overview
Explore in AI Chat

Overview

APPS.PN_SPACE_ALLOCATIONS_V is a reporting view in Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2, owned by the APPS schema. It belongs to the Property Manager (PN) module, which supports real estate and space management functionality. The view presents records from the PN_SPACE_ALLOCATIONS base table, enriched with human-readable employee details obtained from PER_ALL_PEOPLE_F. Its principal role is to expose space allocation data—the assignment of physical office or facility space to individual employees, along with associated area measurements and cost center attribution—in a form suitable for reporting, inquiry screens, and integration with downstream systems. Because it resolves the employee_id foreign key into a person's full name and employee number, the view eliminates the need for report developers and integrators to join to HR tables manually, ensuring that the currently effective employee record is used.

Underlying Base Objects

According to the ETRM metadata, the view is defined over two referenced base objects, both accessed through synonyms in the APPS schema:

  • PN_SPACE_ALLOCATIONS (SYNONYM) — the primary transactional table holding space allocation records, identified by space_allocation_id. All descriptive and attribute columns originate here.
  • PER_ALL_PEOPLE_F (SYNONYM) — the HR people table, used to resolve employee_id into the person's full name (full_name) and employee number (employee_number).

The view definition is a UNION of two nearly identical SELECT statements. Both branches join PER_ALL_PEOPLE_F to PN_SPACE_ALLOCATIONS on pp.person_id = sp.employee_id and restrict the person record to the one effective on the current date via trunc(sysdate) between pp.effective_start_date and pp.effective_end_date. The UNION structure typically distinguishes allocations whose employee has a currently effective HR record from those that do not (for example, terminated or future-dated employees), so that all allocation rows remain visible regardless of HR record status.

Key Columns

The view exposes the following significant columns:

  • row_id — the ROWID of the underlying PN_SPACE_ALLOCATIONS row, useful for direct table updates.
  • space_allocation_id — the unique primary key of the allocation record.
  • location_id — identifies the inventory location or facility to which the space belongs.
  • employee_id — the person to whom the space is allocated; joins to PER_ALL_PEOPLE_F.person_id.
  • employee_name / employee_number — the full name and employee number derived from PER_ALL_PEOPLE_F.
  • cost_center_code — the cost center charged for the allocated space, supporting occupancy cost reporting.
  • allocated_area — the physical area allocated, expressed in the unit of measure configured for the location.
  • allocated_area_pct — the percentage of total space represented by this allocation.
  • attribute_category and attribute1–attribute15 — the standard Oracle flexfield descriptive columns for client-specific extensions.
  • last_update_date, last_updated_by, creation_date, created_by, last_update_login — standard WHO audit columns.

Common Use Cases and Queries

Typical scenarios include occupancy and space-utilization reporting, cost center chargeback analysis, and integration extracts feeding facilities management or corporate real estate systems. A representative query listing current space allocations by employee is:

  • SELECT space_allocation_id, employee_name, employee_number, location_id, allocated_area, allocated_area_pct, cost_center_code FROM apps.pn_space_allocations_v WHERE cost_center_code = :p_cost_center;
  • SELECT location_id, SUM(allocated_area), SUM(allocated_area_pct) FROM apps.pn_space_allocations_v GROUP BY location_id;
  • SELECT employee_name, COUNT(*) FROM apps.pn_space_allocations_v GROUP BY employee_name HAVING COUNT(*) > 1;

Because the view resolves the effective HR record at query time, results reflect the employee assignment effective on the run date, making it well suited to point-in-time occupancy reports.