Search Results pa_ei_assgn_exists

Overview

PA_TRANS_UTILS is a validation utility package in the Oracle E-Business Suite Projects (PA) module. Its documented purpose is to validate that actual expenditure items (referred to in the source as "EI" — expenditure items captured against expenditures) exist for a given assignment, particularly at the moment assignment dates are modified, updated, inserted, or deleted. The package body header records that the API checks whether expenditures exist for a supplied assignment and whether their transaction dates fall outside the current or proposed assignment start and end dates. When such a condition is detected, the package returns a context-sensitive error message code and a return status to the calling program. The package therefore acts as a guard against corrupting project assignment data when date changes would orphan or mismatch existing actual cost transactions.

Key Procedures and Functions

One documented procedure is exposed by this package: CHECK_TXN_EXISTS. Per the ETRM metadata and the source excerpt, this procedure accepts an assignment identifier, the old and new assignment start and end dates, a calling mode, a project identifier, and a person identifier, and returns an error message code and a return status through OUT parameters. Its logic depends on the calling mode. When invoked in CANCEL or DELETE mode, the routine returns the error code PA_EI_ASSGN_EXISTS. When invoked in UPDATE or INSERT mode, it returns PA_EI_ASSGN_DATE_OUTOFRANGE. A separate PA_EI_ASSGN_INVALID_PARAMS code signals invalid parameters. The procedure uses a cursor (cur_validate_ei_ins) that decodes the calling mode into the appropriate message and only fires when an EXISTS check against expenditure items succeeds. The source header documents a performance fix (Bug 2783152) that introduced the EXISTS clause to reduce query cost from 91273 to 2, confirming the routine is tuned for high-frequency validation calls.

Tables Accessed

The package reads from two documented application tables through APPS synonyms: PA_EXPENDITURE_ITEMS_ALL and PA_EXPENDITURES_ALL. The EXISTS subquery joins these tables on expenditure_id, filters on INCURRED_BY_PERSON_ID to match the supplied person, and constrains by project_id, assignment_id, and system_linkage_function values of 'ST', 'OT', and 'ER'. It then tests expenditure_item_date against the new assignment start and end dates using NOT BETWEEN. DUAL is referenced only to supply the decoded error message code when the EXISTS predicate is satisfied. No INSERT, UPDATE, or DELETE operations against application tables are documented; the package is read-only in its effect and returns status information to its caller.

Usage Notes

PA_TRANS_UTILS is referenced by three other packages, indicating it is used programmatically rather than directly by end users. It is typically invoked from assignment maintenance logic — for example, when a user changes assignment dates in a Projects form, or through concurrent processing or custom PL/SQL that manipulates assignment records. Because the routine accepts an explicit calling mode, callers must pass the correct mode so that the appropriate error code is returned; the default is CANCEL. Custom code integrating with this package should expect x_error_message_code to be populated with one of the three documented codes, and should branch on x_return_status before committing assignment date changes. The documented performance fix means the procedure is safe to call frequently within transaction processing, but callers should still respect the dependencies on person, project, and assignment identifiers, which must be supplied accurately to avoid false validation results.