Search Results ben_delete_orphan_rows




Overview
Explore in AI Chat

Overview

APPS.BEN_DELETE_ORPHAN_ROWS is a PL/SQL package within the Oracle E-Business Suite Advanced Benefits (BEN) module. Its principal business function is to purge orphaned rows that accumulate across the BEN batch processing infrastructure during benefits processing cycles. When concurrent benefits processes — enrollment, eligibility, rating, communications, and related batch operations — are interrupted, terminated, or otherwise fail to complete, related staging and informational records can be left without a valid parent transaction or process header. These orphaned rows consume storage, degrade query performance, and can compromise the integrity of subsequent benefits processing runs. This package provides a controlled, concurrent-manager-driven mechanism for identifying and removing such residue. The package is declared with AUTHID CURRENT_USER, meaning its procedures execute with the privileges of the invoker rather than the package owner, and it exposes a global collection type derived from PER_ALL_PEOPLE_F.PERSON_ID for holding person identifiers during processing. It is classified as an OTHER API in the ETRM metadata for 12.2.2, indicating it is an internal administrative utility rather than a public integration interface, and it is referenced by no other packages, confirming its role as a standalone maintenance routine.

Key Procedures and Functions

The package documents two procedures, both designed for invocation from the concurrent manager:

  • PROCESS — The main batch entry point. It accepts the standard concurrent program errbuf and retcode OUT parameters and performs the orphan-row cleanup in a single-threaded execution path.
  • DO_MULTITHREAD — The multithreaded counterpart to PROCESS. In addition to the standard errbuf and retcode OUT parameters, it accepts p_parent_request_id and p_thread_id as IN parameters, enabling the concurrent manager to spawn multiple worker threads under a single parent request. This procedure is the standard mechanism by which Oracle EBS parallelizes long-running batch cleanup so that large volumes of orphaned rows can be processed concurrently rather than serially.

Tables Accessed

The package operates across the full breadth of the BEN batch infrastructure. Batch staging and informational tables include 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, BEN_BATCH_PROC_INFO, BEN_BATCH_RANGES, and BEN_BATCH_RATE_INFO. These tables hold the intermediate results of batch action items, benefit certifications, communications, dependent designations, electable choice enrollments, eligibility determinations, life event records, batch process headers, range definitions, and rate calculations; rows in these tables become orphaned when their parent batch process no longer exists. The package also touches functional data tables including BEN_BENEFIT_ACTIONS, BEN_BNFT_PRVDD_LDGR_F, BEN_CBR_PER_IN_LER, and BEN_CBR_QUALD_BNF, which represent benefit actions, the provided benefit ledger, person-in-life-event cross-references, and qualified beneficiary records. The PER_ALL_PEOPLE_F table supplies the person identifier datatype used in the package's global collection declaration.

Usage Notes

This package is not intended for direct invocation from Oracle Forms or custom application code. It is designed to be registered and executed as a concurrent program through the standard concurrent manager, which supplies the errbuf and retcode OUT parameters and, for the multithreaded path, the parent request and thread identifiers. Administrators invoke it during benefits maintenance windows or as a remediation step after failed or terminated benefits batch runs. Because it performs physical deletion of orphaned data, it should be run with appropriate database backup practices in place and typically outside peak processing hours. When a large volume of orphaned rows is anticipated, the multithreaded DO_MULTITHREAD path is preferable, as it partitions the work across parallel workers. The package's AUTHID CURRENT_USER declaration means the invoking user must hold the necessary privileges on all referenced BEN and PER tables. Under 12.1.1 and 12.2.2, no significant behavioral differences are documented in the source header, which dates to 2004 and remains stable across releases.