Search Results location_suite_name




Overview
Explore in AI Chat

Overview

APPS.PN_SPACE_ASSIGN_EMP_V is a reporting view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that consolidates employee-to-space assignments maintained by the Property Manager (PN) module. Property Manager is the EBS application used to track real estate, offices, floors, and the allocation of those spaces to employees, projects, and cost centers. The view presents a denormalized, read-only projection that joins the space assignment transaction table to the location master and to the HR person record, meaning consumers do not need to construct those joins themselves.

Its principal role is reporting and integration. Because the assignment records carry structured descriptive flexfield columns (attribute1 through attribute15, plus attribute_category), the view is well suited to operational reporting on space utilization, occupancy cost allocation, and headcount-versus-seat analysis. The UNION ALL structure in the view definition indicates it emits more than one row per logical assignment for certain location categories, which is important when writing aggregate queries.

Underlying Base Objects

The ETRM repository documents three referenced base objects for this view: PER_ALL_PEOPLE_F, PN_LOCATIONS, and PN_SPACE_ASSIGN_EMP. Each is referenced through an APPS synonym.

  • PN_SPACE_ASSIGN_EMP — the driving transaction table. It stores one row per employee space assignment, including the assignment start and end dates, the person, the location, project and task references, cost center, and allocated area metrics.
  • PN_LOCATIONS — the space/location master. The view joins on location_id (outer join) to supply the location code, suite name, location type, and unit of measure, and to expose the location's own active start and end dates. The join filters out common areas through NVL(pl.common_area_flag,'N') = 'N', so only assignable space is returned.
  • PER_ALL_PEOPLE_F — the HR person effective-dated table. The outer join restricts to the row effective on the query date using SYSDATE BETWEEN effective_start_date(+) AND effective_end_date(+), and supplies last name, first name, full name, and employee number.

The view derives org_id from PN_LOCATIONS (pl.org_id), which makes it straightforward to apply multi-org security predicates in custom reports.

Key Columns

The employee assignment identifiers include emp_space_assign_id (primary key of the underlying assignment row) and row_id (ROWID passed through from PN_SPACE_ASSIGN_EMP). Space attributes include location_id and its aliased counterpart location_location_id, location_code, location_suite_name, and location_type_lookup_code. Personnel attributes include person_id, full_name, last_name, first_name, and employee_number.

Time and allocation measures include emp_assign_start_date, emp_assign_end_date, allocated_area, allocated_area_pct, utilized_area, and uom_code. Organizational context is provided by project_id, task_id, cost_center_code, and org_id. Audit columns (creation_date, created_by, last_update_date, last_updated_by) and the descriptive flexfield block (attribute_category, attribute1 through attribute15) are also exposed.

Common Use Cases and Queries

Typical uses are current occupancy reports, seat-utilization against allocations, cost center chargeback, and feeds into a data warehouse. A basic listing of active assignments for a location:

  • SELECT emp_space_assign_id, full_name, employee_number, location_code, allocated_area, allocated_area_pct FROM apps.pn_space_assign_emp_v WHERE location_id = :p_location_id AND SYSDATE BETWEEN emp_assign_start_date AND emp_assign_end_date;
  • Aggregate allocated area by cost center: SELECT cost_center_code, SUM(allocated_area) FROM apps.pn_space_assign_emp_v GROUP BY cost_center_code;
  • Locations assigned to no employee (outer-join residue): rows where person_id IS NULL.

Note the UNION ALL can duplicate results for a single assignment when a location matches more than one branch; apply DISTINCT on emp_space_assign_id where a one-row-per-assignment result set is required.