Search Results defrag_contig_assign




Overview
Explore in AI Chat

Overview

APPS.PN_SPACE_ASSIGN_CUST_PKG is a PL/SQL package body in the Oracle E-Business Suite Property Manager (PN) module that manages customer space assignments within leased and owned properties. Space assignment is the process by which a specific customer is allocated a defined area of a property — typically a suite, floor, or portion of a building — for a specified period and under specified lease terms. This package encapsulates the core business logic for creating, maintaining, and validating those customer space assignment records, insulating the surrounding application code from direct manipulation of the underlying interface and base tables.

The package is classified as an "OTHER" API in the ETRM repository, meaning it is primarily an internal implementation package rather than a formally published public API. Its procedures are invoked by the Property Manager forms and by other PN packages that need to validate, split, or reposition customer space assignments. It also coordinates with the corresponding employee space assignment package (PN_SPACE_ASSIGN_EMP_PKG), reflecting the tandem nature of customer and employee assignment logic in Property Manager.

Key Procedures and Functions

  • INSERT_ROW — Inserts a new customer space assignment record into the base table, applying the package's standard defaults and derivation logic.
  • LOCK_ROW — Obtains a row-level lock on an existing assignment record to support concurrent, multi-user update scenarios and optimistic locking checks.
  • UPDATE_ROW — Modifies an existing customer space assignment record, including its area, percentage, or date attributes.
  • DELETE_ROW — Removes a customer space assignment record, performing any prerequisite validation before deletion.
  • CHK_DUP_CUST_ASSIGN — Validates whether a proposed assignment would duplicate an existing customer assignment for the same location or period, raising an application error when a conflict is detected.
  • GET_DUP_CUST_ASSIGN_COUNT — Returns the count of conflicting duplicate assignments, used to drive user-facing messages or decision logic.
  • CHECK_ASSIGN_ARCL_LINE — Verifies whether a space assignment is referenced by recovery/archive detail lines, guarding against deletion or modification of assignments already carried into recovery processing.
  • ASSIGNMENT_SPLIT — Splits an existing customer assignment into two or more portions, supporting partial reassignment or reconfiguration of occupied space.
  • AREA_PCT_AND_AREA — Derives or recalculates the area and percentage-of-property values associated with an assignment.
  • ASSIGNMENT_COUNT — Returns the number of space assignments associated with a given customer or location context.
  • LOCATION_COUNT — Returns the number of locations tied to a given assignment or customer.
  • DEFRAG_CONTIG_ASSIGN — Defragments or consolidates contiguous customer assignments, reconciling adjacent space records into a coherent set.
  • DELETE_OTHER_ASSIGNMENTS_EMP — Deletes competing employee assignments, used when a customer assignment supersedes employee occupancy.
  • DELETE_OTHER_ASSIGNMENTS_CUST — Deletes competing customer assignments, ensuring a location is not double-booked.

Tables Accessed

  • PN_SPACE_ASSIGN_CUST_ALL — The base table storing customer space assignment records. INSERT_ROW, UPDATE_ROW, and DELETE_ROW operate on this table, while LOCK_ROW locks rows within it.
  • PN_SPACE_ASSIGN_CUST — The MLS-enabled view/synonym over the customer assignment base table, used for reading assignment data in the session language.
  • PN_LOCATIONS_ALL / PN_LOCATIONS — The location master tables that define assignable space. The package references these to validate locations and support counts.
  • PN_SPACE_ASSIGN_EMP_ALL / PN_SPACE_ASSIGN_EMP — Employee assignment tables, accessed by the DELETE_OTHER_ASSIGNMENTS_EMP logic to clear conflicting employee assignments.
  • PN_REC_ARCL_DTLLN_ALL — Recovery/archive detail lines, checked by CHECK_ASSIGN_ARCL_LINE to prevent changes to assignments already captured in recovery.
  • PN_SPACE_ASSIGN_CUST_S — The sequence used to generate primary keys for new customer assignment records.
  • DUAL — Used for single-row utility queries and validation lookups.
  • PLITBLM — The standard EBS PL/SQL table of numbers used for list-based processing within the package.

Usage Notes

PN_SPACE_ASSIGN_CUST_PKG is invoked primarily from the Property Manager space assignment forms and from concurrent/background processes that manipulate customer occupancy. The dependency list confirms that the package calls FND_GLOBAL for session context, FND_MESSAGE for user-facing error messaging, PNP_DEBUG_PKG for debug tracing, PNT_LOCATIONS_PKG for location validation, PN_MO_CACHE_UTILS for cached lookups, PN_RECOVERY_EXTRACT_PKG for recovery extraction, and PN_VAR_RENT_PKG for variable rent calculations triggered by area changes.

Although not a published API, the package is referenced by four other database objects, so customizations should treat its interfaces as stable. Modifications should be tested in both 12.1.1 and 12.2.2, since the package is documented as valid and available in both releases. The DELETE_OTHER_ASSIGNMENTS procedures indicate the package enforces exclusive occupancy rules, so callers should invoke it before manually inserting competing assignments.