Search Results write_route_and_hierarchy




Overview
Explore in AI Chat

Overview

The APPS.BEN_PLAN_DESIGN_WIZARD_API package body is the server-side engine behind the Oracle EBS Advanced Benefits Plan Design Wizard, a guided setup tool for configuring benefit plans, programs, eligibility profiles, and related reference data. Its central purpose is to support copy-and-reuse operations within the wizard by reading the results of prior entity-copy transactions and writing the resulting routes and hierarchy relationships into the underlying runtime tables.

The package version captured here is bepdwapi.pkb 120.3, last revised 2006/04/20 under label noship, and it remains relevant across the EBS 12.1.1 and 12.2.2 application releases. Consistent with Oracle's PL/SQL API naming conventions, the package exposes a small, controlled set of public procedures rather than a wide functional surface, delegating most of the wizard's interactive behavior to forms and other benefits APIs.

A distinctive characteristic of this package is its metadata-driven query construction. The get_query procedure builds dynamic SQL strings by mapping a supplied table alias (for example 'ABR', 'ACP', 'APR', 'BNB', 'BNG', or 'BPP') to a set of related table aliases and a selectively indexed INFORMATION<n> column on BEN_COPY_ENTITY_RESULTS. Rather than hard-coding every relationship, the package derives the correct filter predicate at runtime based on the entity being copied.

Key Procedures and Functions

The ETRM documentation for this object registers six procedures and functions. Each is described below by name and purpose; parameter lists are intentionally not reproduced.

  • WRITE_ROUTE_AND_HIERARCHY — The procedure most directly relevant to the write_route_and_hierarchy search. It persists route definitions and hierarchy (parent-child) relationships for benefit objects identified during the Plan Design Wizard copy flow. It is responsible for translating the in-memory design produced by the wizard into rows that reflect the entity's navigational and structural position within the benefits model.
  • UPDATE_RESULT_ROWS — Updates rows in BEN_COPY_ENTITY_RESULTS to reflect processing status, identifiers, or DML outcomes after a copy operation has been evaluated or executed.
  • DELETE_ENTITY — Removes an entity and its associated copy results, supporting the wizard's delete and rework paths.
  • PDW_SUBMIT_COPY_REQUEST — Creates and submits the copy request handled by the Plan Design Wizard concurrent process, seeding the transaction into PQH_COPY_ENTITY_TXNS.
  • REUSE_DELETED_HIERARCHY — Restores or re-links hierarchy structures that were previously removed, allowing the wizard to reuse existing tree definitions instead of rebuilding them.
  • get_query (internal) — Constructs the dynamic SQL used to enumerate copyable entities by alias and INFORMATION column.

Tables Accessed

  • BEN_COPY_ENTITY_RESULTS — The primary work table. It stores per-entity copy results, table aliases, primary keys, DML operations, and the aliased INFORMATION<n> columns that drive dynamic query generation.
  • PQH_COPY_ENTITY_TXNS — Holds the copy transaction header created by PDW_SUBMIT_COPY_REQUEST, keyed by COPY_ENTITY_TXN_ID, which subsequent queries filter on.
  • PQH_TABLE_ROUTE — Provides the routings that map entity tables to their hierarchies, consumed by WRITE_ROUTE_AND_HIERARCHY.

Usage Notes

This package is typically invoked indirectly. The Plan Design Wizard, and certain Advanced Benefits setup paths, call PDW_SUBMIT_COPY_REQUEST to launch the copy concurrent process, which in turn drives the remaining procedures. Two other packages reference it, indicating that other benefits setup flows depend on its hierarchy-writing capability. Custom code should call only the documented procedures, supply a valid COPY_ENTITY_TXN_ID, and avoid direct DML against BEN_COPY_ENTITY_RESULTS, since UPDATE_RESULT_ROWS and DELETE_ENTITY maintain the integrity of that table.