Search Results pn_space_allocations_v




Overview
Explore in AI Chat

Overview

PN_SPACE_ALLOCATIONS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, delivered as part of the PN – Property Manager product family. The view presents space allocation records in which a physical location, identified by LOCATION_ID, is assigned to a specific employee together with an allocated area and an allocated area percentage. Its principal purpose is to enrich the transactional data held in PN_SPACE_ALLOCATIONS with descriptive human-resources context drawn from PER_ALL_PEOPLE_F, specifically the employee's full name and employee number.

From a reporting and integration standpoint, the view abstracts the employee lookup so that Property Manager users and downstream reports do not need to join to the HR person tables manually or handle date-effective filtering themselves. The view is defined with a UNION, which combines a date-effective join against the current person record with a second branch that preserves space allocations whose employee cannot be matched to an active person record. This design ensures that allocations are not silently dropped from reporting simply because the referenced employee record is not effective as of the current date — for example, where the employee has left the organization or the person record dates do not span SYSDATE.

Underlying Base Objects

The ETRM metadata documents two referenced base objects, both resolved through APPS synonyms:

  • PN_SPACE_ALLOCATIONS — the primary transactional table, aliased SP in the view definition. It supplies the space allocation identifier, location, cost center code, allocated area, allocated area percentage, and the descriptive flexfield attributes (ATTRIBUTE_CATEGORY through ATTRIBUTE15).
  • PER_ALL_PEOPLE_F — the date-effective HR person table, aliased PP. It supplies PERSON_ID, FULL_NAME, EMPLOYEE_NUMBER, and the effective date range.

The join predicate links PP.PERSON_ID to SP.EMPLOYEE_ID, restricted by TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE so that only the currently effective person row is joined. Each returned row also carries the base table ROWID as ROW_ID, allowing the view to be used in certain update or locking contexts via the ROWID pseudocolumn.

Key Columns

  • ROW_ID — the ROWID of the underlying PN_SPACE_ALLOCATIONS row.
  • SPACE_ALLOCATION_ID — primary key of the space allocation.
  • LOCATION_ID — the property location to which the allocation applies.
  • EMPLOYEE_ID — person identifier linking to PER_ALL_PEOPLE_F.
  • EMPLOYEE_NAME — FULL_NAME of the currently effective person; this is the column most commonly searched by users for "employee_name".
  • EMPLOYEE_NUMBER — the person's employee number.
  • COST_CENTER_CODE — cost center charged for the allocated space.
  • ALLOCATED_AREA / ALLOCATED_AREA_PCT — the physical area assigned and its percentage share.
  • ATTRIBUTE_CATEGORY through ATTRIBUTE15 — descriptive flexfield columns inherited from the base table.
  • Standard WHO audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include space utilization reports by employee or department, cost center chargeback extracts, and reconciliation of allocated areas against occupancy data. Because EMPLOYEE_NAME is exposed directly, searches such as "employee_name" resolve to this column without requiring a manual join to PER_ALL_PEOPLE_F.

  • List allocations for a named employee:
SELECT space_allocation_id, employee_name, employee_number,
       location_id, allocated_area, allocated_area_pct
FROM   apps.pn_space_allocations_v
WHERE  UPPER(employee_name) LIKE UPPER(:p_employee_name);
  • Summarize allocated area by cost center:
SELECT cost_center_code,
       COUNT(*)         allocation_count,
       SUM(allocated_area) total_area
FROM   apps.pn_space_allocations_v
GROUP  BY cost_center_code
ORDER  BY total_area DESC;
  • Identify allocations whose employee could not be matched to a currently effective person record (where EMPLOYEE_NAME returns NULL).

All queries should be issued with APPS or a synonym-enabled schema, and callers should account for the UNION semantics when deduplicating results.