Search Results update_auto_space_assign

Overview

The APPS.PN_TENANCIES_PKG package body is a core PL/SQL component of the Oracle E-Business Suite Property Manager (PN) module, which supports lease and property management for tenants and locations. Its principal business function is to encapsulate the create, read, update, and delete (CRUD) logic and validation rules for tenancy records stored in the PN_TENANCIES_ALL table. A tenancy links a tenant (customer or employee) to a specific space or location within a leased property, and this package enforces the constraints that govern those associations, including date validity, uniqueness of primary locations, and overlap prevention between concurrent tenancies. In ETRM 12.1.1 and 12.2.2, the package is classified as an OTHER API rather than a formal public API, meaning it is primarily intended for internal use by Property Manager forms, concurrent programs, and dependent packages rather than as a documented integration interface. It also provides supporting utilities for automatic space assignment and area allocation, which are central to how the module tracks leasable and occupied space.

Key Procedures and Functions

The package exposes sixteen documented procedures and functions, which can be grouped by responsibility.

DML operations: INSERT_ROW, LOCK_ROW, UPDATE_ROW, and DELETE_ROW provide the standard row-level maintenance operations against the tenancy table, with LOCK_ROW supporting concurrency control.

Validation routines: CHECK_UNIQUE_PRIMARY_LOCATION ensures that only one primary location is associated per tenancy context; CHECK_FOR_OVELAP_OF_TENANCY (note the legacy spelling) detects date-range collisions between tenancy records; and CHECK_TENANCY_DATES validates start and end date consistency.

Automatic space assignment: CREATE_AUTO_SPACE_ASSIGN, UPDATE_AUTO_SPACE_ASSIGN, and DELETE_AUTO_SPACE_ASSIGN manage the lifecycle of automatically allocated space assignments, while UPDATE_DUP_SPACE_ASSIGN handles duplicate assignment corrections.

Area and location utilities: AUTO_ALLOCATED_AREA, AUTO_ALLOCATED_AREA_PCT, AVAILAIBLE_SPACE, GET_LOC_INFO, and GET_ALLOCATED_AREA compute allocated and available space figures and retrieve location details for downstream processing and UI display.

Tables Accessed

The package reads and writes the principal tenancy tables PN_TENANCIES_ALL, PN_TENANCIES_HISTORY, and its shadow table PN_TENANCIES_HISTORY_S, together with PN_TENANCIES_S, the sequence/identifier support object referenced in the user search. Space assignment logic touches PN_SPACE_ASSIGN_CUST, PN_SPACE_ASSIGN_CUST_ALL, and PN_SPACE_ASSIGN_EMP_ALL, while location and lease context is drawn from PN_LOCATIONS, PN_LOCATIONS_ALL, and PN_LEASES_ALL. Recovery and allocation detail resides in PN_REC_ARCL_DTLLN_ALL, PN_REC_ARCL_DTL_ALL, PN_REC_EXPCL_DTLLN_ALL, and PN_REC_EXPCL_DTL_ALL. DUAL is used for single-row PL/SQL evaluations.

Usage Notes

Because PN_TENANCIES_PKG is classified as OTHER rather than a public API, it is typically invoked indirectly. Property Manager forms call it to maintain tenancy records and to trigger automatic space assignment. Dependent packages — the metadata records references from PN_SPACE_ASSIGN_CUST_PKG, PN_RECOVERY_EXTRACT_PKG, PN_VAR_RENT_PKG, and PN_MO_CACHE_UTILS — rely on its validation and allocation routines. Utilities such as PNP_DEBUG_PKG, PNP_UTIL_FUNC, and FND_MESSAGE indicate integrated error messaging and debugging, and APP_EXCEPTION and FND_PROFILE support exception handling and profile-driven behavior. Custom code should invoke it cautiously and ideally validate against Oracle's documented public APIs, since the package is not referenced by any other database object and its interface may change between releases.