Search Results not_exists

Overview

The APPS.BEN_SEED_ACTION_ITEM_TYPES package body is a seeding utility within the Oracle EBS Advanced Benefits (BEN) module. Its stated purpose, per the embedded header comment, is to "seed action item types on a business group basis." Action item types are the configurable categories of follow-up activities that Advanced Benefits generates for participants, administrators, or third parties during the lifecycle of a benefits enrollment or life event. Because these types are defined per business group, a mechanism is required to populate each business group with the standard Oracle-delivered set of action item types without forcing administrators to define them manually. This package provides that mechanism.

The package carries a header version of 120.0.12010000.2, dated 2008/08/05, and its revision history shows incremental evolution: initial creation in June 1998, conversion to seed-table sourcing in July 1999, multilingual (MLS) insertion added in May 2000, and removal of the description column from the NOT EXISTS clause in February 2002 to fix bug 2217566. The package body declares a single global constant, g_package, used for location tracing via hr_utility.set_location.

Key Procedures and Functions

The package exposes one documented procedure: SEED_ACTION_ITEM_TYPES. It accepts a business group identifier and performs the seeding operation for that business group. Internally, the procedure constructs a local package-name string for diagnostic tracing, enters a logging location at step 10, and then executes two insert statements.

The first insert populates the base (non-translated) action item type table by selecting rows from the corresponding seed table and supplying a fresh primary key from the sequence, the target business group ID, and an object version number of 1. The second insert populates the multilingual table, propagating the same generated key along with the language and translation type columns. This two-step pattern is the standard Oracle EBS MLS seeding idiom: the base row is inserted first, and the translation row is inserted immediately afterward referencing the same primary key.

Tables Accessed

• BEN_STARTUP_ACTN_TYP — read only; the seed (delivered) source of action item type definitions. The procedure selects type_cd, name, and description from this table.
• BEN_STARTUP_ACTN_TYP_TL — the multilingual counterpart of the seed table, referenced for translation seeding.
• BEN_ACTN_TYP — written; the business-group-level action item types table. Rows are inserted only where no matching row already exists.
• BEN_ACTN_TYP_S — the sequence object supplying actn_typ_id values via BEN_ACTN_TYP_S.NEXTVAL.
• BEN_ACTN_TYP_TL — written; the multilingual table for business-group-level action item types.

The NOT EXISTS subquery is central to the package's idempotency and is the element associated with the user's search term. It correlates on type_cd, name, and business_group_id, preventing duplicate inserts when the procedure is run more than once against the same business group. Notably, the description predicate is commented out in the source (lines 59 and 22–24 of the history), reflecting the bug 2217566 fix: comparing descriptions caused the existence test to fail when users edited descriptions, producing erroneous duplicates.

Usage Notes

This is an internal seeding package rather than a public API; ETRM classifies it as OTHER, and it is referenced by zero other packages, indicating it is invoked at the boundary of setup or business group creation rather than from within the benefits processing engine. Typical invocation points include business group initialization or configuration routines during implementation and the seeded-data refresh steps performed after patching. It may also be called from custom setup scripts or concurrent programs that provision new business groups in a multi-organization deployment.

Because the procedure is idempotent, it is safe to re-run; existing action item types are skipped, and only missing seed rows are added. Subsequent changes to delivered action item types in BEN_STARTUP_ACTN_TYP will not overwrite user-modified rows, since the existence check short-circuits the insert. Administrators should therefore treat the package as a one-way seed loader rather than a synchronization tool.