Results for “benefit_action”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

APPS.BEN_DELETE_ORPHAN_ROWS is an administrative maintenance package body within the Oracle E-Business Suite Advanced Benefits (BEN) module. Its documented purpose is to identify and remove orphaned or residual data rows—records that no longer have a valid parent in the benefits processing hierarchy—so that batch processing tables remain consistent and reusable. In release 12.1.1 and 12.2.2, batch action, communication, election, eligibility, and rate processing can leave transient staging rows behind if a process terminates abnormally, if a participant record changes mid-run, or if a manual cleanup is required before a re-run. The package provides a concurrent-program driven mechanism that splits the deletion workload across multiple parallel workers and tracks completion of those workers before the master process terminates.

The header identifier (bedeorph.pkb 120.0.12010000.3) confirms the object is a 12.1.x-era community build that carries forward unchanged into 12.2.x file systems, so behavior is consistent across both releases.

Key Procedures and Functions

• PROCESS — The primary entry point, typically bound to a concurrent program. It establishes the scope of the cleanup, determines the number of worker processes required, and coordinates the overall execution cycle.
• DO_MULTITHREAD — Drives the parallel execution model. It spawns the slave concurrent requests, populates the internal G_REQUEST_TBL collection with their request identifiers and maintains G_NUM_PROCESSES, which the completion-check logic iterates over.

The package body also contains internal helper routines visible in the excerpt. CHECK_ALL_SLAVES_FINISHED loops through the slave request IDs held in G_REQUEST_TBL and issues FND_CONCURRENT_REQUESTS queries (phase_code <> 'C'), sleeping five seconds via DBMS_LOCK between passes until every worker reports complete. DELETE_PER_CON performs the actual row-by-row deletion work. No public parameter lists are documented in the ETRM metadata, so the signatures should be read from the source before custom invocation.

Tables Accessed

The package operates on the batch-control and benefit-action family of tables. Its documented table list includes BEN_BATCH_PROC_INFO, BEN_BATCH_RANGES, BEN_BATCH_ACTN_ITEM_INFO, BEN_BATCH_BNFT_CERT_INFO, BEN_BATCH_COMMU_INFO, BEN_BATCH_DPNT_INFO, BEN_BATCH_ELCTBL_CHC_INFO, BEN_BATCH_ELIG_INFO, BEN_BATCH_LER_INFO, and BEN_BATCH_RATE_INFO. These tables hold staged lifecycle-event and benefit-action detail generated during batch open enrollment and qualifying-event processing, making them the primary deletion targets. BEN_BENEFIT_ACTIONS represents the parent lifecycle action entity used to validate which staged rows are still legitimate. PER_ALL_PEOPLE_F supplies participant identity and effective-dating context. BEN_BATCH_ORPHAN_ROWS logic also references BEN_BNFT_PRVDD_LDGR_F, BEN_CBR_PER_IN_LER, and BEN_CBR_QUALD_BNF for activity and qualifying benefit cross-checks.

Usage Notes

This package is intended for controlled, administrative use rather than end-user interaction. It is not referenced by any other documented package, so it sits at the outer edge of the dependency graph and is normally launched directly as a concurrent request by a benefits administrator or through a diagnostic script. Because the deletion targets span multiple batch tables, the package must not be run while related batch processes are active; the multithreaded slave model means concurrent managers must have worker capacity available, otherwise CHECK_ALL_SLAVES_FINISHED will block indefinitely in its sleep-and-retry loop. In 12.2.x online patching environments, cleanup is frequently performed after an aborted open enrollment run to remove stale BEN_BATCH_* rows before restarting the program. Custom code should invoke the package only after a backup of the affected batch tables, and always within the documented request-identifier framework so that orphan detection remains scoped to a single prior run.