Search Results pa_hr_resource




Overview
Explore in AI Chat

Overview

APPS.PA_HR_RESOURCE is a PL/SQL package body within the Oracle E-Business Suite Projects (PA) module that enforces referential integrity between Oracle HR persons and jobs and the resource objects used by Oracle Projects. Its primary business role is to safeguard the personnel and resource data model by detecting whether an HR person or an HR job is still referenced by operational Projects entities before any deletion or modification is permitted. When a reference exists, the package raises a controlled application error rather than allowing orphaned records, thereby protecting the integrity of resource lists, resource transaction attributes, resource maps, and the resource breakdown structure (RBS).

Key Procedures and Functions

The package exposes two documented public procedures:

  • CHECK_PERSON_REFERENCE — Verifies whether a given HR person is still referenced by Projects resource data. It evaluates several reference paths: membership of the person in the RBS and RBS elements, and the existence of resource transaction attribute rows keyed by person. Where no reference is found, the procedure proceeds to remove obsolete rows from the denormalized resources table, the resource transaction attributes table, and the resources table. An error message identifier is returned to the caller when a blocking reference is detected.
  • CHECK_JOB_REFERENCE — Performs the parallel validation for HR jobs. It determines whether an HR job is referenced by Projects resource records and signals the caller if the job cannot be safely removed or altered because active references remain.

Both procedures follow the Oracle EBS extension convention of raising a named exception and returning an error message and reference-existence flag through OUT parameters, allowing calling forms and programs to present a meaningful message to the end user.

Tables Accessed

The package operates against the following documented tables, all accessed through APPS synonyms:

  • PA_RESOURCE_TXN_ATTRIBUTES — Stores resource transaction attribute rows, including the person-to-resource association. The package checks for existing rows here and deletes stale entries when no reference remains.
  • PA_RESOURCES — The master resource definition table; cleared of obsolete resource rows where no reference exists.
  • PA_RESOURCES_DENORM — The denormalized resource table maintained for query performance; purged in step with the master resource cleanup.
  • PA_RESOURCE_LIST_MEMBERS — Holds the membership of resource lists. The package queries this table to confirm whether a resource is still a member of any list before permitting deletion.
  • PA_RESOURCE_MAPS — Maps resource instances between organization contexts; participating in the resource reference validation flow.
  • PA_RBS_ELEMENTS — Defines the elements of the resource breakdown structure. The person reference check first consults this table, since an HR person used in the RBS must not be deleted.
  • DUAL — Used for single-row procedural checks and exception handling scaffolding.

Usage Notes

PA_HR_RESOURCE is classified as an OTHER API in the ETRM registry, indicating it is primarily an internal validation utility rather than a business-facing public API. It is referenced by two other packages, which typically invoke it as part of HR-to-Projects synchronization and resource maintenance processing. In practice the procedures are called before deletion or end-dating of HR persons and jobs, most commonly from Oracle Projects setup and resource maintenance forms when a resource is removed, and from concurrent programs that reconcile HR personnel against the Projects resource model. Custom code extending resource maintenance should invoke these procedures rather than deleting from PA_RESOURCES, PA_RESOURCES_DENORM, or PA_RESOURCE_TXN_ATTRIBUTES directly, because the procedures encapsulate the correct order of reference checks, error messaging, and cascading cleanup. Because the procedures return status through OUT parameters and named exceptions, callers must include proper exception handlers to translate PA_HR_PER_RES_TXN_ATTR and PA_HR_PER_RBS_REF conditions into user-facing messages.