Search Results get_pl_id

Overview

APPS.BEN_PUMP_GET is a PL/SQL resolution package within the Oracle E-Business Suite Advanced Benefits (BEN) module. Its declared purpose, as stated in the package header, is to serve the Data Pump engine by resolving surrogate ID values that must be passed to the Benefits API modules. The package is defined with AUTHID CURRENT_USER and carries the header identifier bedpget.pkh, with the last recorded revision dated 2006.

At the core of Benefits data loading and migration, external or staged records carry developer keys and user-defined names rather than internal primary keys. The Data Pump engine requires the numeric primary key identifiers that the underlying API layers expect. BEN_PUMP_GET bridges this gap, accepting business-group context, effective dates, and user key values, then returning the corresponding internal ID from the relevant BEN entity table. Although the package was authored for the Data Pump, the header notes that most of its functions are general-purpose and may be called by any program performing a similar ID-resolution task.

Key Procedures and Functions

The documented API surface of BEN_PUMP_GET comprises eleven functions, each resolving a specific Benefits entity identifier:

• GET_ACTY_BASE_RT_ID1 through GET_ACTY_BASE_RT_ID4 — return an activity base rate ID. The four variants correspond to the separate rate name and rate number columns maintained on the activity base rate entity.
• GET_PGM_ID — returns a program ID. This is the function associated with the search term get_pgm_id, and it resolves a Benefits program identifier from the program definition.
• GET_PL_ID — returns a plan ID for an active or effective plan definition.
• GET_ENDED_PL_ID — returns a plan ID for a plan whose effective dates have ended, allowing the Data Pump to resolve historical or terminated plan references.
• GET_OPT_ID — returns an option ID within a plan.
• GET_ENDED_OPT_ID — returns an option ID for an option that is no longer in effect.
• GET_PEN_PERSON_ID — returns a person ID for a participant record.
• GET_CON_PERSON_ID — returns a person ID for a contact or related person record.

Each function is pure with respect to database state, as indicated by the PRAGMA RESTRICT_REFERENCES (..., WNDS) declarations, meaning no function writes to the database.

Tables Accessed

The package resolves identifiers by querying the following tables through APPS synonyms:

• BEN_PGM_F — the Benefits program definition table, supporting GET_PGM_ID.
• BEN_PL_F — the plan definition table, supporting GET_PL_ID and GET_ENDED_PL_ID.
• BEN_OPT_F — the plan option definition table, supporting GET_OPT_ID and GET_ENDED_OPT_ID.
• BEN_ACTY_BASE_RT_F — the activity base rate table, supporting the four GET_ACTY_BASE_RT_ID variants.
• PER_ALL_PEOPLE_F — the core HR person table, supporting GET_PEN_PERSON_ID and GET_CON_PERSON_ID.

All access is read-only. The _F suffix on the Benefits tables indicates effective-dated date-tracked entities, which is why the function signatures accept an effective date parameter.

Usage Notes

BEN_PUMP_GET is invoked primarily by the Benefits Data Pump engine during batch loads and migrations, where staged data contains names and numbers rather than internal IDs. It is referenced by three other packages within the E-Business Suite, reflecting its role as a shared resolution utility. Custom code performing Benefits integrations may call these functions directly when the same ID-translation requirement arises, though the header cautions that reliability is lower where the user key value is supplied as a parameter. The p_data_pump_always_call argument present in the documented signatures allows the Data Pump to force resolution even when caching would normally short-circuit the call.