Search Results delete_space_allocations

Overview

APPS.PN_PURGE_INTERFACE_PKG is a PL/SQL package body within the Oracle E-Business Suite Property Manager (PN) module. Its principal business function is the controlled purge of interface (staging) data that has been loaded into the Property Manager open interface tables. Interface tables such as PN_LOCATIONS_ITF and PN_EMP_SPACE_ASSIGN_ITF accumulate rows during batch loads of property locations and employee space assignments. Once those records have been processed and moved into the base Property Manager tables, the corresponding interface rows become obsolete and must be removed to prevent table growth and to preserve the integrity of subsequent batch runs.

This package provides that cleanup mechanism. It is designed to be executed as a concurrent program, and it accepts a batch name to scope the deletion to a specific interface load, along with a function flag that determines which class of interface data is purged. The presence of FND_FILE and FND_MESSAGE calls at the top of the main procedure confirms that the package writes a log file and an output file and that it reports progress through the standard concurrent manager messaging framework.

Key Procedures and Functions

• PURGE_CAD — The main entry point and the procedure that exposes the p_batch_name parameter referenced in user searches. It accepts an error buffer, a return code, a function flag, and a batch name. It initializes the concurrent program log and output files, optionally emits debug messages that echo the batch name and function flag, and then dispatches to the appropriate deletion routine based on the value of the function flag.
• DELETE_LOCATIONS — Removes interface rows associated with the supplied batch name from the property locations interface. It first performs a count against the interface table to confirm that qualifying rows exist for the batch before proceeding with deletion. Comments in the source indicate defensive handling was introduced to address a specific defect, reflecting the null-safety checks around the batch name.
• DELETE_SPACE_ALLOCATIONS — Removes interface rows associated with the supplied batch name from the employee space assignment interface. It is invoked independently or in conjunction with DELETE_LOCATIONS depending on the function flag supplied to PURGE_CAD.

The function flag governs dispatch as follows: a value of L triggers location deletion only, S triggers space allocation deletion only, and A triggers both deletions in sequence, separated by a debug separator line. Each deletion is wrapped in an exception handler that re-raises through APP_EXCEPTION.RAISE_EXCEPTION, ensuring failures surface to the concurrent manager rather than being silently swallowed.

Tables Accessed

• PN_LOCATIONS_ITF — The property locations interface table. It is queried for a row count and then purged by batch name via DELETE_LOCATIONS.
• PN_EMP_SPACE_ASSIGN_ITF — The employee space assignment interface table. It is purged by batch name via DELETE_SPACE_ALLOCATIONS.

Both tables are referenced through APPS synonyms. No base Property Manager tables are modified by this package; its scope is limited to interface staging data.

Usage Notes

The package is intended to be run as an Oracle EBS concurrent program, typically submitted from the Property Manager responsibility after a location or space assignment interface load has completed successfully. The concurrent program parameter set provides the function flag and the batch name that scope the purge. Because the package relies on FND_FILE for output and FND_MESSAGE for logging, it should not be invoked outside the concurrent manager environment without those dependencies being available.

The p_batch_name parameter is optional in the sense that the underlying deletion logic includes null checks; however, omitting a batch name broadens the scope of deletion and should be done only when intentionally clearing all interface rows for the selected function. The package is not referenced by any other documented package, so it stands as a terminal cleanup utility rather than a reusable API. Custom code should call it only through the concurrent program interface, preserving the expected error buffer and return code handling.