Search Results space_allocated

Overview

The view PN_EMPLOYEE_SPACE_INFO_V belongs to the Property Manager (PN) module of Oracle E-Business Suite and is documented under the title "View: PN_EMPLOYEE_SPACE_INFO_V." Its stated purpose is to present combined employee and space assignment information, joining human resources employee records to property space allocation records. The view therefore acts as a convenient reporting and integration layer that surfaces, for each employee, identity attributes alongside cost center and physical location allocation details. It is particularly relevant to facilities management, real estate chargeback, and space utilization reporting, where an organization needs to correlate people with the physical space they occupy.

Users searching on the term "employee_num" are typically looking for the employee number key exposed by this view. The column EMPLOYEE_NUM, sourced from the employee master, is the human-readable business key that most operational reports and extracts rely on rather than the internal surrogate EMPLOYEE_ID. For EBS 12.1.1 and 12.2.2, this view exists purely as a read-only query construct; it holds no data of its own.

Underlying Base Objects

Per the documented ETRM metadata, the view is defined over two referenced objects: PER_EMPLOYEES_CURRENT_X and PN_SPACE_ALLOCATIONS. The employee side is driven from PER_EMPLOYEES_CURRENT_X (aliased EMP), which supplies the current effective employee record. The space side is driven from PN_SPACE_ALLOCATIONS (aliased SPC). The two are joined on EMPLOYEE_ID using an outer join, expressed as EMP.EMPLOYEE_ID = SPC.EMPLOYEE_ID (+). Because EMP is the driving table, the view returns every employee from the current employee source, with space allocation columns populated only where a matching allocation exists. Where no allocation row is found, the space columns are handled through DECODE logic to avoid nulls propagating into reporting output.

Key Columns

The view exposes nine columns:

• EMPLOYEE_ID — Internal surrogate key of the employee, from the employee source.
• EMPLOYEE_NUM — The employee number, the primary business identifier searched by users.
• FULL_NAME — The employee's complete formatted name.
• FIRST_NAME, MIDDLE_NAME, LAST_NAME — Individual name components for flexible reporting.
• COST_CENTER_CODE — Derived via DECODE: if the space allocation carries a cost center code it is used; otherwise the value is obtained from the function PNP_UTIL_FUNC.GET_CC_CODE(EMPLOYEE_ID).
• SPACE_ALLOCATED — A flag computed as 'Y' when a LOCATION_ID exists on the allocation, otherwise 'N'.
• LOCATION_ID — The identifier of the allocated space location, null when no space is allocated.

The presence of both a flag and the location identifier enables consumers to distinguish allocated from unallocated employees without additional joins.

Common Use Cases and Queries

Typical uses include listing employees without space assignments, reconciling space chargeback by cost center, and extracting employee-to-location mappings for facilities reporting.

To find an employee by number:

• SELECT employee_num, full_name, cost_center_code, space_allocated, location_id FROM pn_employee_space_info_v WHERE employee_num = :emp_num;

To list unallocated employees:

• SELECT employee_num, full_name, cost_center_code FROM pn_employee_space_info_v WHERE space_allocated = 'N' ORDER BY full_name;

To summarize allocations by cost center:

• SELECT cost_center_code, COUNT(*) total, SUM(DECODE(space_allocated,'Y',1,0)) allocated FROM pn_employee_space_info_v GROUP BY cost_center_code;

Because the view references a PL/SQL utility function for the fallback cost center, query performance depends on whether that function is deterministic; for large extracts, filtering by LOCATION_ID or SPACE_ALLOCATED first is advisable.