Search Results create_staffed_assignment

Overview

PA_STAFFED_ASSIGNMENT_PVT is a private PL/SQL package in the Oracle E-Business Suite Projects (PA) module. It provides the underlying implementation logic for creating, updating, and deleting staffed project assignments — that is, assignments that link a specific named resource to a project or project task with defined dates, effort, and role information. As a "PVT" (private) package, it is not intended as a public integration interface; the public counterpart is PA_ASSIGNMENTS_PUB, which exposes the formal application programming interface. The private package encapsulates the validation, defaulting, denormalization maintenance, and multi-table insert/update/delete operations that a public assignment API ultimately delegates to.

The package is declared with AUTHID CURRENT_USER, meaning its SQL executes with the privileges of the calling user rather than the package owner. This design supports the standard EBS pattern of exposing private logic through public wrappers while respecting the caller's security context, including row-level security applied to project and resource data.

Key Procedures and Functions

The ETRM metadata documents three procedures, all centered on the lifecycle of a staffed assignment.

• CREATE_STAFFED_ASSIGNMENT — Creates a new staffed assignment. It accepts an assignment record structure defined by PA_ASSIGNMENTS_PUB.Assignment_Rec_Type and a creation mode. It also accepts optional inputs controlling unfilled assignment status, resource source, and location attributes (city, region, country code), plus flags for task summarization, budget version, commit behavior, and validate-only execution. On success it returns the new assignment identifier, the assignment row identifier, the resolved resource identifier, and the API return status. The presence of validate-only and commit flags indicates the procedure follows the standard EBS API control conventions, allowing callers to perform a dry-run validation before committing.
• UPDATE_STAFFED_ASSIGNMENT — Modifies an existing staffed assignment using the same assignment record type, with optional location overrides and the same commit and validate-only controls. It returns the API return status.
• DELETE_STAFFED_ASSIGNMENT — Removes a staffed assignment, identified by its assignment row identifier, with an optional assignment identifier parameter.

No parameters beyond those documented should be assumed; the signatures above reflect only what the ETRM excerpt confirms.

Tables Accessed

The package reads and writes a broad set of Projects tables, consistent with the complexity of assignment creation.

• PA_PROJECT_ASSIGNMENTS — The primary assignment table; the target of inserts, updates, and deletes.
• PA_RESOURCE_ASSIGNMENTS — Stores resource-level assignment detail and budget version linkage.
• PA_RESOURCES / PA_RESOURCES_DENORM / PA_RESOURCE_LIST_MEMBERS — Resource identity and denormalized resource attributes; the denormalized table is typically refreshed after changes.
• PA_RESOURCE_TXN_ATTRIBUTES — Transaction attribute values associated with the assignment.
• PA_LOCATIONS — Supplies and validates location city, region, and country code values.
• PA_PROJECT_PARTIES — Project party relationships maintained alongside assignments.
• PA_PROJECTS_ALL, PA_PROJECT_STATUSES, PA_BUDGET_VERSIONS — Project, status, and budget version validation and derivation.
• PA_PROJ_ELEM_VER_STRUCTURE, PA_PROJ_WORKPLAN_ATTR — Task and workplan structure used when summarizing tasks or linking assignments to workplan attributes.
• PA_RES_FORMATS_B — Resource format definitions controlling how resource information is stored and displayed.
• PER_ALL_ASSIGNMENTS_F — HR assignment data, used to resolve the person/resource associated with the staffed assignment.

Usage Notes

PA_STAFFED_ASSIGNMENT_PVT is normally invoked indirectly. Oracle Projects forms, concurrent programs, and public APIs such as PA_ASSIGNMENTS_PUB call into it rather than invoking it directly. The ETRM metadata records that it is referenced by two other packages, confirming it functions as a shared internal service rather than an entry point.

Custom code should prefer the public PA_ASSIGNMENTS_PUB interface. Direct calls to this private package are unsupported and risk breakage on patching, since Oracle may change private signatures between releases without notice. Where direct invocation is unavoidable, callers must honor the G_MISS_CHAR, G_MISS_NUM, and G_FALSE sentinel conventions, set validate-only appropriately during testing, and explicitly manage commit scope. The G_AUTO_APPROVE package variable defaults to 'N', indicating assignments are not automatically approved unless the calling context enables it. This package is documented here for the 12.2.2 ETRM release; behavior in 12.1.1 is consistent for the same assignment operations.