Search Results archive_encumbrance

Overview

PSP_ARCHIVE_RETRIEVE is an Oracle EBS Applications (APPS) PL/SQL package owned by the Projects suite. It belongs to the Oracle Labor Distribution / Effort Reporting family of components (PSP module) and provides the archival and retrieval engine for labor cost distribution and encumbrance data. Its principal business purpose is to move historical distribution and encumbrance records out of the active transaction tables into dedicated archive tables, and to retrieve or restore that data when it is needed again for reporting, reconciliation, or audit. The package is declared AUTHID CURRENT_USER, meaning its SQL executes under the privileges of the calling schema rather than the defining schema, which is a standard convention for EBS concurrent-program callable packages. A single package-level variable, g_error_api_path VARCHAR2(1000), was added to support error-message resolution (referenced in the source header as bug 2482603).

Key Procedures and Functions

The documented interface exposes six callable units. Four of these share an identical signature pattern — an errbuf and retcode pair (the standard concurrent-program output parameters) followed by p_payroll_id, p_begin_period, p_end_period, p_business_group_id, and p_set_of_books_id.

• ARCHIVE_DISTRIBUTION — archives labor distribution lines for a payroll, business group, and set of books within a period range bounded by p_begin_period and p_end_period.
• RETRIEVE_DISTRIBUTION — retrieves previously archived distribution lines back for the same parameter set.
• ARCHIVE_ENCUMBRANCE — archives encumbrance lines associated with the payroll and accounting period range.
• RETRIEVE_ENCUMBRANCE — retrieves archived encumbrance lines.
• ARCHIVE_DISTRIBUTION_INT and RETRIEVE_DISTRIBUTION_INT — archive and retrieve the distribution interface (staging) records used before distribution data is finalized into the permanent tables.

The user-supplied search term p_end_period corresponds directly to the end of the accounting period range that every archive and retrieve procedure uses to scope the records it processes.

Tables Accessed

The package reads and writes a coherent set of PSP tables. Period context is obtained from PER_TIME_PERIODS. Active distribution data resides in PSP_DISTRIBUTION_INTERFACE and PSP_DISTRIBUTION_LINES_HISTORY, while archived counterparts are held in PSP_DISTRIBUTION_LINES_ARCH and PSP_DISTRIBUTION_INT_ARCH. Adjustment data follows the same pattern through PSP_ADJUSTMENT_LINES_HISTORY and PSP_ADJUSTMENT_LINES_ARCH. Encumbrance archiving uses PSP_ENC_CONTROLS, PSP_ENC_LINES_HISTORY, and PSP_ENC_LINES_ARCH. Effort reporting metadata — PSP_EFFORT_REPORTS, PSP_EFFORT_REPORT_DETAILS, PSP_EFFORT_REPORT_PERIODS, and PSP_EFFORT_REPORT_TEMPLATES — supplies report structure and period definitions. Finally, PSP_ARCH_RET_REQUESTS tracks the archive/retrieve requests themselves, supporting traceability of each submitted run.

Usage Notes

PSP_ARCHIVE_RETRIEVE is not referenced by any other package, so it is designed to be invoked directly as a concurrent program entry point rather than called from other PL/SQL. The errbuf/retcode parameters confirm the standard concurrent-manager calling convention. Typical invocation occurs through EBS concurrent programs that archive closed payroll periods or retrieve them on demand; the same logic may be called from custom code that needs the identical archival behavior. Because the range is controlled by p_end_period (paired with p_begin_period), administrators should ensure the period range aligns with closed accounting periods and the correct payroll, business group, and set of books before submission. The source header indicates the package was last shipped at version 115.4, and it remains available in both 12.1.1 and 12.2.2 environments under the APPS schema.