Search Results pa_ei_assgn_invalid_params




Overview
Explore in AI Chat

Overview

PA_TRANS_UTILS is a utility package in the Oracle Projects (PA) module of Oracle E-Business Suite, owned by the APPS schema and classified as an OTHER API. Its principal business function is to validate whether actual transactions — specifically expenditure items and raw expenditures in the Expenditure Inquiry (EI) domain — exist for a given assignment, and whether the transaction dates fall within the assignment's effective start and end dates. The package is invoked when assignment dates are modified, updated, inserted, or deleted, ensuring that transactional data does not become orphaned outside a valid assignment date range. The header of the package source documents its origin (PATRUTLS.pls, version 115.3) and confirms that it is intended for internal validations rather than end-user-facing features. The package operates under AUTHID CURRENT_USER, meaning that privileges are evaluated against the invoking schema rather than the definer, which is consistent with many Oracle Projects utility APIs that are called from within other PL/SQL routines in the same session context.

Key Procedures and Functions

The ETRM metadata documents a single public procedure in PA_TRANS_UTILS:

  • CHECK_TXN_EXISTS — Validates that expenditure items (EI) exist for the specified assignment when assignment dates are being changed. When a conflict is detected, the procedure returns an error message code and a return status to the caller. The documented calling modes and their outcomes are: CANCEL or DELETE mode returns PA_EI_ASSGN_EXISTS with status 'E'; UPDATE or INSERT mode returns PA_EI_ASSGN_DATE_OUTOFRANGE with status 'E'; and an invalid parameter combination returns PA_EI_ASSGN_INVALID_PARAMS with status 'E'. On success, the return status is 'S' and the error message code is null. The procedure accepts parameters for the assignment identifier, the old and new start and end dates, a calling mode (defaulting to 'CANCEL'), the project identifier, and the person identifier, and returns the error message code and return status through OUT parameters.

Tables Accessed

The package reads from the following tables, referenced through APPS synonyms:

  • PA_EXPENDITURES_ALL — The primary source of raw expenditure records used to determine whether any actual transactions exist for the assignment in question.
  • PA_EXPENDITURE_ITEMS_ALL — Holds the expenditure item detail associated with raw expenditures; queried to confirm the existence of items tied to the assignment and to evaluate their transaction dates against the old and new assignment date ranges.
  • DUAL — Used for single-row PL/SQL expression evaluations typical in validation logic.

No documented writes are performed against these tables by this package; the operations are primarily read-only validations intended to gate date-change operations performed elsewhere in the Expenditure Inquiry or assignment maintenance flows.

Usage Notes

PA_TRANS_UTILS is a low-level utility API invoked from other PL/SQL packages rather than as a standalone concurrent program. ETRM metadata records that it is referenced by three other packages, indicating it is called as a validation step within larger assignment and expenditure-processing flows — typically when the assignment maintenance logic needs to prevent the creation of transactions outside a valid date window. The procedure should be called with the appropriate calling mode (CANCEL, DELETE, UPDATE, or INSERT) so that the returned error message matches the business scenario. Callers must supply the old and new date ranges along with the project and person identifiers so that CHECK_TXN_EXISTS can accurately determine whether existing expenditures conflict with the proposed changes. Custom code integrating with this package should inspect both x_return_status and x_error_message_code, since the procedure returns 'S' only when no conflict is detected and a null error message accompanies success. Because the package is classified as OTHER rather than as a published public API, it is recommended that customizations invoke it only through the standard Oracle Projects forms and concurrent programs that already depend on it, or that they replicate its validation logic rather than calling it directly.