Search Results check_ref_unique

Overview

APPS.PA_XC_PROJECT_PUB is a public PL/SQL package in Oracle E-Business Suite that supports the bulk import of project and task structures into Oracle Projects. The package is declared with AUTHID DEFINER, meaning it executes with the privileges of its owning schema (APPS), which allows calling applications to perform project and task maintenance operations without requiring direct object grants. The package version constant G_API_VERSION_NUMBER (1.0) is provided for version validation by calling code, and a ROW_ALREADY_LOCKED exception (initialized against Oracle error -54) signals concurrency conflicts when a record under modification is already locked by another session.

The central business purpose of the package is to accept inbound task and project data — typically staged data originating from external systems or interface tables — and to create or update the corresponding PROJECTS and TASKS records while maintaining the hierarchical WBS structure. Because task identification in source systems is frequently expressed as a task reference rather than the internal Oracle TASKS.TASK_ID surrogate key, the package exposes a resolution helper, FETCH_TASK_ID, to translate a caller-supplied task reference into the internal task identifier used by the database.

Key Procedures and Functions

• IMPORT_PROJECT — The primary entry point for importing project-level data, creating or updating the project header and initiating the associated task structure load.
• IMPORT_TASK — Imports individual task records, populating task attributes such as task name, number, WBS level, start and end dates, and descriptive fields along with the defined attribute columns.
• FETCH_TASK_ID — Resolves a task reference (or equivalent caller-supplied identifier) to the internal TASK_ID stored in PA_TASKS. This is the procedure most relevant to the "fetch_task_id" search, and it is the mechanism by which external references are mapped to Oracle Projects keys prior to task-level DML.
• GENERATE_NEW_TASK_REFERENCE — Produces a new, unique task reference for tasks that do not already carry one, supporting the interface's requirement that every imported task be externally identifiable.
• CHECK_REF_UNIQUE — Validates that a supplied task reference is unique within its scope, preventing duplicate task references from being created during import.
• IS_NUMBER — A utility validation function that determines whether a given value is numeric, used to guard date and identifier conversions during the import process.

The package also defines the task_in_rec_type record structure, which carries the full set of inbound task fields (task_id, task_reference, task_name, dates, WBS level, parent reference, scheduling dates, and attribute columns).

Tables Accessed

• PA_TASKS — The principal target table; stores the task records created or updated by IMPORT_TASK and queried by FETCH_TASK_ID.
• PA_PROJECTS / PA_PROJECTS_ALL — Project header records maintained by IMPORT_PROJECT.
• PA_PROJ_ELEMENTS, PA_PROJ_ELEMENT_VERSIONS, PA_PROJ_ELEM_VER_SCHEDULE, PA_PROJ_ELEM_VER_STRUCTURE — Maintain the project element and WBS structure hierarchy and scheduling information for imported tasks.
• PA_PROJ_STRUCTURE_TYPES, PA_STRUCTURE_TYPES — Provide structure type definitions used to classify the task hierarchy.
• PA_OBJECT_RELATIONSHIPS — Records relationships between project objects established during import.
• PA_TEMP_IMPORT_TASKS — Temporary staging area used during the import process.
• FND_USER — Resolves the login user name supplied on the task record.
• DUAL — Used for single-row evaluations such as the IS_NUMBER check.

Usage Notes

PA_XC_PROJECT_PUB is classified as a public (PUB) API, indicating it is intended for supported invocation from external interface code rather than internal-only use. It is referenced by one other package within Oracle Projects. Callers typically invoke IMPORT_PROJECT to load project headers and IMPORT_TASK to load the associated task hierarchy, using FETCH_TASK_ID and GENERATE_NEW_TASK_REFERENCE to align caller-supplied references with internal keys, and CHECK_REF_UNIQUE to enforce reference integrity before insert. The package is commonly driven from concurrent programs, interface processing logic, or custom PL/SQL that populates the inbound task record structure. Because it executes AUTHID DEFINER, invoking sessions do not require direct privileges on the underlying Oracle Projects tables, but they must be granted EXECUTE on the package and should handle the ROW_ALREADY_LOCKED exception when concurrent processing targets the same project records.