Search Results oke_sec_overlap_role_assigned

Overview

OKE_K_ACCESS_PVT is a private PL/SQL package in the Oracle E-Business Suite (EBS) Applications schema (APPS) that supports the Oracle Contracts Core (OKE) module. Its principal business function is to validate and manage role-based access and assignment records associated with contract parties, ensuring that personnel assignments to a project or object do not conflict with existing role grants. The package operates against the Oracle Projects foundation table PA_PROJECT_PARTIES, which stores the association between project parties, resources, roles, and effective date ranges. By centralizing these validation checks, OKE_K_ACCESS_PVT helps enforce security and integrity rules governing who may act on a contract and under what role, over what active period.

The package is classified as a Private API (PVT), meaning it is intended for internal consumption by other OKE packages and standard product code rather than being a public integration point for customer extensions. The header comment ($Header: OKEVKASB.pls 115.10 2002/11/20) indicates the file is comparatively old, yet the package remains relevant across both EBS 12.1.1 and 12.2.2 because contract role access logic continues to rely on it.

Key Procedures and Functions

The ETRM metadata documents three procedures for this package. In practice the source also contains additional private helpers, including the ASSIGNMENT_EXISTS logic that a "assignment_exists" search surfaces, but the formally documented entry points are:

• CREATE_CONTRACT_ACCESS — Establishes contract access records, recording that a given party or person is authorized to access a contract under a specified role.
• UPDATE_CONTRACT_ACCESS — Modifies existing contract access records, for example to change the effective dates or role associated with an existing assignment. It typically applies the same overlap and conflict rules enforced at creation.
• LOCK_ROW — Acquires a lock on the relevant row to serialize concurrent updates, preventing race conditions when multiple users modify contract access data simultaneously.

Although listed under the documented procedures, the package body also exposes a private procedure ASSIGNMENT_EXISTS, which queries PA_PROJECT_PARTIES to detect whether a person already holds a role with an overlapping active date range. If a conflict is found, it raises either OKE_SEC_MULTI_ROLE_ASSIGNED (a different role already assigned) or OKE_SEC_OVERLAP_ROLE_ASSIGNED (the same role in an overlapping period), and returns FND_API.G_RET_STS_ERROR via the standard API return status convention.

Tables Accessed

The sole documented table referenced through APPS synonyms is PA_PROJECT_PARTIES. This Oracle Projects table stores project party records, including PROJECT_PARTY_ID, OBJECT_TYPE, OBJECT_ID, PROJECT_ROLE_ID, RESOURCE_TYPE_ID, RESOURCE_SOURCE_ID (the person), and START_DATE_ACTIVE/END_DATE_ACTIVE. The package reads this table to determine whether a prospective assignment duplicates or overlaps an existing one, using a cursor that applies GREATEST/LEAST logic to test date-range overlap and NVL handling for open-ended assignments. All access is effectively read-oriented for validation, with the row-locking procedure supporting controlled writes performed by the calling access-management routines.

Usage Notes

OKE_K_ACCESS_PVT is invoked indirectly by standard OKE contract role and security forms, by related OKE packages, and by any custom code that must validate contract party role assignments before committing them. The metadata notes that the package is referenced by one other package, confirming its role as an internal dependency rather than a standalone entry point. It follows Oracle's standard API error-handling pattern, populating FND_MSG_PUB and returning FND_API.G_RET_STS_SUCCESS, G_RET_STS_ERROR, or G_RET_STS_UNEXP_ERROR. Because it is private and undocumented for external use, developers should not call it directly; instead they should invoke the supported public OKE/K access APIs that internally rely on this package. When troubleshooting "assignment_exists" behavior, examine whether an existing PA_PROJECT_PARTIES row shares the person, object, and an overlapping date range, as this is precisely the condition the package detects and rejects.