Search Results pn_emp_space_assign_itf




Overview
Explore in AI Chat

Overview

The PN_EMP_SPACE_ASSIGN_ITF table is a Property Manager (PN) interface (staging) table in Oracle E-Business Suite, delivered in the PN schema and valid in both 12.1.1 and 12.2.2. It serves as the inbound migration and data-loading surface for employee-to-space allocations—records that assign a specific employee to a specific property location, cost center, or project for a defined date range. Rather than inserting directly into the production assignment entity, external sources, legacy conversions, and batch loaders write rows into this interface table, which is subsequently validated and transferred into the base application tables by a concurrent program. This decoupled design isolates bulk loading from transactional logic and provides an error-capture mechanism for rejected rows.

The ETRM metadata classifies this object heuristically as standalone under the Data Vault model, indicating it does not participate in the core hub/link/satellite integration graph. From a modeling perspective this is consistent with its role as a transient staging artifact rather than a persistent business entity.

Key Information Stored

The table comprises 43 documented columns. Its central identifier is EMP_SPACE_ASSIGN_ID, a foreign key to PN_SPACE_ASSIGN_EMP_ALL that links each interface row to the corresponding production assignment record; EMPLOYEE_ID similarly references PSB_EMPLOYEES and identifies the assigned worker. Business attributes include LOCATION_ID, COST_CENTER_CODE, ALLOCATED_AREA, UTILIZED_AREA, PROJECT_ID, and TASK_ID, which together describe what space is assigned, where, its measured allocation, and any project or task charging context. EMP_ASSIGN_START_DATE and EMP_ASSIGN_END_DATE define the assignment validity window, while CHANGE_DATE and CHANGE_MODE describe incremental changes to an existing record.

Load-control columns include BATCH_NAME, ENTRY_TYPE, SOURCE, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE—these track which batch, source system, and concurrent request produced each row. Processing status is captured by TRANSFERRED_TO_PN, TRANSFERRED_TO_CAD, and ERROR_MESSAGE, which indicate successful transfer to downstream systems or record the reason for rejection. Fifteen ATTRIBUTE columns plus ATTRIBUTE_CATEGORY provide descriptive flexibility, and the standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide audit tracking. The metadata does not document a single-column surrogate primary key or unique index; EMP_SPACE_ASSIGN_ID functions as the primary business join key.

Common Use Cases and Queries

Typical uses include validating interface loads before they are processed, isolating failed rows for correction, and reconciling source counts against successfully transferred assignments. Identifiers commonly filtered are BATCH_NAME, TRANSFERRED_TO_PN, TRANSFERRED_TO_CAD, and ERROR_MESSAGE.

  • Pending rows for a batch: SELECT * FROM PN.PN_EMP_SPACE_ASSIGN_ITF WHERE BATCH_NAME = :batch AND TRANSFERRED_TO_PN = 'N';
  • Failed rows: SELECT EMP_SPACE_ASSIGN_ID, EMPLOYEE_ID, ERROR_MESSAGE FROM PN.PN_EMP_SPACE_ASSIGN_ITF WHERE ERROR_MESSAGE IS NOT NULL;
  • Reconciliation by request: aggregate counts grouped by REQUEST_ID and PROGRAM_ID to confirm a concurrent program processed all queued rows.
  • Assignment detail report: join to PSB_EMPLOYEES on EMPLOYEE_ID and to PN_SPACE_ASSIGN_EMP_ALL on EMP_SPACE_ASSIGN_ID to render employee name, location, allocated area, and assignment dates.

Related Objects

  • PN_SPACE_ASSIGN_EMP_ALL — base assignment entity, joined on EMP_SPACE_ASSIGN_ID.
  • PSB_EMPLOYEES — employee master, joined on EMPLOYEE_ID.
  • PN_LOCATIONS_ALL — location reference for LOCATION_ID (downstream validation).
  • PN_EMPLOYEES / PN_EMP_ASSIGNMENTS — related Property Manager assignment tables populated by the transfer program.
  • FND_CONCURRENT_REQUESTS / FND_CONCURRENT_PROGRAMS — joined on REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID for load-audit reporting.
  • The employee-space assignment import concurrent program, which reads this table, writes to PN_SPACE_ASSIGN_EMP_ALL, and sets TRANSFERRED_TO_PN, TRANSFERRED_TO_CAD, and ERROR_MESSAGE.