DEV Community: Chinnamsetti Satyanarayana Swamy

The Universal Outbound Pattern: Building a Parametric BIP-to-SFTP Integration Delivery Engine in OIC

Chinnamsetti Satyanarayana Swamy — Sun, 26 Jul 2026 18:08:25 +0000

Introduction & Business Requirement:

During global enterprise resource planning (ERP) or human capital management (HCM) cloud implementations, technical teams face an ongoing integration bottleneck: delivering outbound files to dozens of distinct downstream third-party vendors. Every vendor enforces unique transport specifications, data structures, and layout definitions, including pipe-delimited CSVs, XML feeds, JSON payloads, fixed-width banking text files, and custom fixed-position structures.

The traditional design pattern creates an architectural mess: developers build separate point-to-point integrations for every outbound channel. This point-to-point approach strains developer resources, creates a maintenance nightmare when authentication credentials or server endpoints change, and floods the OIC runtime instance with redundant integrations. To solve this, organizations need a single, centralized outbound utility capable of dynamically running reports, adjusting file formatting, applying encryption, and managing file transport securely.

Implementation:

This solution relies on a single App-Driven Orchestration integration named ORA Common Outbound Service Integration (ORA_COMMON_OUTBOU_SERVIC_INTEGR). It serves as a unified, parametric engine triggered via a REST endpoint. It accepts dynamic parameters—including the target report path, file format types, data chunk sizes, and parameter name-value structures—and maps them to backend processing nodes.

REST API Trigger
(Pass Parameters)
        │
        ▼
Invoke BI Publisher SOAP Service
runReport
        │
        ▼
File Content Returned in Base64
        │
        ▼
Check Report File Size
        │
        ▼
Small File?
        │
        ├── Yes
        │      │
        │      ▼
        │  Read Directly into Stage Memory
        │
        └── No
               │
               ▼
        Invoke getDataInChunks
        Paginated Reader Loop
               │
               ▼
Check Encryption Flag
EncryptionFlag = 'Y'?
               │
               ├── Yes
               │      │
               │      ▼
               │  Execute PGP Stage Action
               │
               └── No
                      │
                      ▼
While Target SFTP Fails
Auto-Retry and Backoff Wait Timer
                      │
                      ▼
Successful Transfer
                      │
                      ▼
Send notifyFileName Delivery Receipt

The system operates through three core layers to deliver files reliably.

Dynamic BIP Mapping Canvas: The engine transforms incoming REST parameters into a SOAP payload directed at the BI Publisher ExternalReportWSSService endpoint, ensuring runtime flexibility across any reporting database.
Memory-Guarded Chunking Loop: To process massive datasets without triggering memory exhaustion errors, the integration uses the getDataInChunks method to stream raw bytes in controlled chunks.
Self-Healing SFTP Delivery Scope: File transport is managed within an isolated sub-process scope equipped with an automated retry routine. If a target server drops the connection, the integration increments a counter, waits, and automatically retries the transfer.

Development:

The technical foundation of this integration relies on parsing parameters dynamically, splitting streams by data density, and handling communication drops gracefully.

Parametric Payload Mapping Canvas: The incoming REST request wrapper exposes generic attributes that map directly to the BI Publisher SOAP body. The core variables map as follows:

Memory Optimization and Stream Segmentation: When the engine invokes the report, an internal Switch activity checks the size of the payload. If the file size exceeds your defined memory threshold, the engine routes processing to a parallel branch called getDataInChunks.

This branch uses a While loop to invoke downloadReportDataChunk from the BI Publisher service. It maps the unique reportFileID and tracks the current segment using beginIdx, appending each chunk to a staging file until the entire file is cached safely outside memory limits.

Secure Processing and PGP Encryption: Once the complete file is staged, a dedicated security switch checks the EncryptionFlag variable. If set to 'Y', the engine routes the data reference to a Stage File Encryption action. This node applies the vendor’s public PGP key to encrypt the staged asset, saving the output as writeFullOutEncrypted before clearing the unencrypted source file from storage.

Working of Integration:
This integration is designed as a reusable outbound solution to trigger BI Publisher (BIP) reports and generate output files in either Fixed Position or Fixed Length formats. It is an application-driven integration that accepts inputs such as Integration ID, Integration Name, and up to five BI parameter values. Based on the provided Integration ID, the integration retrieves all required configuration details from lookups, including BI parameter names, report path, encryption flag, and SFTP destination path.

Once the BI Publisher report is executed, the integration evaluates the output and routes the process through appropriate conditions. Depending on the report format, it follows either the Fixed Length or Fixed Position processing path. If the report does not return any data, the integration follows an alternate path and exits after sending a notification indicating that no data was generated.

The report output is then processed using stage file operations, where encryption is applied conditionally based on the encryption flag retrieved from the lookup. Finally, the file is transferred to the designated SFTP server using an FTP Adapter, with built-in retry logic to handle transient failures. The integration also incorporates comprehensive error handling through both default and global fault handlers, and leverages reusable components to ensure consistent and efficient notification management.

Configuration & Deployment Steps:

Configuring the Inbound REST Endpoint:

Create a new App-Driven Orchestration integration and name it ORA Common Outbound Service Integration.
Add a REST Connection adapter as the initial trigger node and configure it to accept a POST request payload.
Define the request JSON schema to expose your parameters: ReportPath, ReportAttributeFormat, EncryptionFlag, and your array parameters (RepParam1 through RepParam4).

Setting Up the Autonomous SFTP Retry Engine: To insulate transfers from target server network drops, configure your file delivery within a dedicated Scope Block named ServiceScope_i2.

Position a While action loop inside the scope block, controlled by the condition RetriesRequires_i2 == "Y".
Inside the main processing path, place an FTP adapter invoke action named writeFileToOICSFTP.
If the file transfer succeeds, add an Assign step that updates SetRetriesToFalse_i2 to exit the loop cleanly.
Open the scope block's Fault Handler view. Add a Switch block to check the retry count. If it is below your limit (e.g., less than 3 attempts), use an Assign activity named IncrementCounter_i2 to bump the attempt number, add a Wait activity for a brief delay, and allow the loop to retry the transfer. If the limit is reached, log the error using SetRetryToFalse_i2 and re-throw the fault to trigger an alert.

Conclusion:

Building a generic outbound engine in OIC replaces complex point-to-point connections with a clean, scalable architecture. Shifting file generation to dynamic parameter mappings allows a single integration interface to service an unlimited number of downstream vendor reporting requirements.

Combining this flexibility with memory-guarded chunking and automated SFTP error retries gives enterprise environments a reliable, high-performance delivery channel. This approach cuts down on deployment overhead, simplifies credential rotation, and provides a stable framework for large-scale data extractions across the Oracle utility ecosystem.

Automating Core HR Data Correction Using BI Publisher and HCM Data Loader

Chinnamsetti Satyanarayana Swamy — Fri, 24 Jul 2026 16:20:53 +0000

Introduction:

In large-scale Oracle HCM Cloud implementations, data drift between Core HR, global payroll, and downstream third-party systems is an ongoing challenge. When mass organizational restructures, position updates, or external Applicant Tracking System (ATS) syncs occur, errors frequently slip through. Common discrepancies include mismatched worker categories, broken manager hierarchies, or assignments missing mandatory evaluation criteria.

When these misalignments happen, the downstream impacts are costly, payroll runs fail due to missing attributes, security profiles break, and compliance reporting becomes inaccurate. Manually identifying these data gaps requires HR teams to run complex audits, manually format Excel spreadsheets, and upload corrective files via HCM Data Loader (HDL). For global enterprises processing thousands of worker changes daily, this manual approach is slow, prone to errors, and highly inefficient.

Solution Architecture:

To solve this problem at scale, we can build a decoupled, automated remediation pipeline. This architecture shifts the burden from manual HR auditing to an automated query engine. The process uses a high-performance BI Publisher Data Model configured with a Global Temporary Table (GTT) infrastructure to run delta-reconciliation logic across core worker tables (PER_ALL_ASSIGNMENTS_M, PER_ASSIGNMENT_SUPERVISORS_F, and PAY_ALL_PAYROLLS_F).

Instead of simply reporting errors on a dashboard, the BI Publisher engine acts as a data generator. Using BIP Text Output Bursting, the data model formats the discovered errors directly into valid, pipe-delimited HDL business object syntax (such as Worker.dat or Assignment.dat). The generated file is then pushed directly into an Oracle WebCenter Content (UCM) bucket or an SFTP directory, where an automated OIC integration or an Essbase scheduled process picks it up and processes it through the HDL interface. This approach creates a closed-loop, self-healing data pipeline.

[Scheduled BIP Audit Engine Runs]
        |
        |
        |
[Populate PL/SQL GTT with Assignment Deltas]
        |
        |
        |
[BIP Bursting Engine Processes Rows Engine-Side]
        |
        |
        |
[Dynamic XML-to-Text Transformation (HDL Structure)]
        |
        |
        |
[Direct File Delivery to UCM / hcm/dataloader/import]
        |
        |
        |
[Automated HDL Ingestion Engine Automatically Resolves Gaps]

Technical Implementation:

To implement this pipeline, we bypass standard layout templates. Instead, we use SQL-based formatting within a PL/SQL data package to output clean, raw HDL data segments.

1. The Reconciliation Global Temporary Table (GTT):
This database-tier table stages corrupted or drifting assignment records during the execution run, preventing long-running database joins from slowing down the system.

CREATE GLOBAL TEMPORARY TABLE XX_HR_RECON_TMP (
    person_number      VARCHAR2(30),
    assignment_number  VARCHAR2(30),
    action_code        VARCHAR2(30),
    effective_start_dt DATE,
    legal_entity_id    NUMBER,
    system_error_type  VARCHAR2(50),
    hdl_line_generated VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

2. The Core Audit & Extraction PL/SQL Package Logic:
This package runs the reconciliation query, identifies assignments that lack a valid supervisor or have an invalid payroll setup, and builds the raw HDL string directly into the database row.

CREATE OR REPLACE PACKAGE BODY xx_hr_automation_pkg AS

    PROCEDURE run_assignment_audit IS
    BEGIN
        -- Clear staging environment for current session run
        EXECUTE IMMEDIATE 'TRUNCATE TABLE XX_HR_RECON_TMP';

        -- Identify drifted or broken assignment records
        INSERT INTO XX_HR_RECON_TMP (
            person_number,
            assignment_number,
            action_code,
            effective_start_dt,
            system_error_type,
            hdl_line_generated
        )
        SELECT 
            papf.person_number,
            paam.assignment_number,
            'CORR_ASSIGN', -- Data Correction Action Code
            paam.effective_start_date,
            'MISSING_SUPERVISOR',
            -- Dynamically construct valid HDL Worker.dat Metadata rows
            'MERGE|Assignment|' || papf.person_number || '|' || paam.assignment_number || '|' || 
            TO_CHAR(paam.effective_start_date, 'YYYY/MM/DD') || '|CORR_ASSIGN|CORRECTION'
        FROM 
            per_all_assignments_m paam
            JOIN per_all_people_f papf ON paam.person_id = papf.person_id
        WHERE 
            paam.assignment_status_type_id = 1 -- Active Assignments Only
            AND NOT EXISTS (
                SELECT 1 
                FROM per_assignment_supervisors_f pasf 
                WHERE pasf.assignment_id = paam.assignment_id
                AND TRUNCATE(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date
            )
            AND TRUNCATE(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
            AND TRUNCATE(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;

        COMMIT;
    END run_assignment_audit;

END xx_hr_automation_pkg;

3. The BI Publisher Text-Extraction XML Data Model:
The BI Publisher data model calls the database package execution block and pulls the structured lines out as a single XML document tree.

SELECT 
    'METADATA|Assignment|PersonNumber|AssignmentNumber|EffectiveStartDate|ActionCode|ReasonCode' AS hdl_header_line,
    hdl_line_generated AS hdl_data_line
FROM 
    XX_HR_RECON_TMP;

Configuration & Deployment Steps:

1. Configuring BI Publisher for Text Extraction:
Standard BIP layouts generate PDF or XML files. To output a clean .dat file for HDL ingestion, use this configuration pattern.

Navigate to BI Publisher Enterprise Administration.
Create a new report layout using the Text output option rather than RTF.
Configure the EText Template structure using standard delimiter markers.

Map the hdl_header_line to run once at the top of the file, and loop through the hdl_data_line query results to build the rest of the file.

2. Automating the Ingestion Loop via UCM:

To fully automate this self-healing process, route the file directly to the loader utilities.
Open the Bursting Definition tab inside your BI Publisher Data Model.
Set the delivery destination format to WebCenter Content (UCM).
Point the destination parameters to the account path: hcm/dataloader/import.
Set up an enterprise schedule to run this report nightly. When BI Publisher runs, it places a newly formatted Worker.dat file directly into the automated HDL queue, which processes and corrects the data gaps before the next business day begins.

Sample Worker.dat file:

METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|PersonNumber|StartDate|DateOfBirth|ActionCode
MERGE|Worker|EMP|HDL1001|2006-01-01|1001|2006-01-01|1990-05-12|HIRE

METADATA|PersonName|SourceSystemOwner|SourceSystemId|EffectiveStartDate|PersonNumber|NameType|FirstName|LastName
MERGE|PersonName|EMP|PN1001|2006-01-01|1001|GLOBAL|John|Doe

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonNumber|DateStart|LegalEmployerName|WorkerType|ActionCode
MERGE|WorkRelationship|EMP|WR1001|1001|2006-01-01|US Legal Employer|E|HIRE

Summary & Conclusion:

By moving data validation from manual spreadsheets to an automated database architecture, this solution removes the need for manual administrative auditing. Using BI Publisher Data Models to generate corrective HDL data files allows the system to identify and repair core data errors automatically. This pipeline cuts down on payroll run failures, keeps system hierarchies accurate, and provides enterprise environments with a scalable, automated approach to data health.

Dynamic OAB Post-Election Validation using Value Sets and Lifecycle Phase Shifting

Chinnamsetti Satyanarayana Swamy — Thu, 23 Jul 2026 08:10:06 +0000

Introduction & Business Requirement:

In Oracle Advanced Benefits (OAB), self-service architectures provide employees with a seamless enrollment experience. However, a major data integrity challenge occurs when employees select a coverage tier that requires dependents (e.g., "Spouse Only", "Children Only", or "Family") but complete their checkout without designating any actual dependents or beneficiaries.

When this happens, the application records an invalid transactional state: the employee is charged premium deductions for an expanded tier, but has zero active dependent records attached to the benefit result. This issue causes immediate downstream compliance errors, requires manual data cleanup by HR administrators, and impacts third-party outbound lifestyle vendor interfaces.

Solution:

To prevent invalid data entry at checkout, we can deploy a Post-Election Edit Fast Formula. This formula acts as a synchronous validation gatekeeper that evaluates selections immediately when an employee clicks Save or Submit in Employee Self-Service.

Instead of relying on hardcoded text strings (an anti-pattern that breaks during global rollouts, translations, or plan renames), this enterprise solution uses a decoupled architecture featuring.

Dynamic Value-Set Fetching: Uses native value sets (ORA_BEN_PLAN_VS and ORA_BEN_OPT_VS) to resolve the true database definitions of plan and option IDs dynamically.
Lifecycle Phase Interception: Detects the active execution engine phase via BEN_GET_PROCESS_NAME(). If the formula evaluates outside of the standard self-service window, it dynamically transforms the temporal context to handle complex New Hire or Newly Eligible event horizons.

[User Clicks Submit]
          ↓
[Fetch Value-Sets via PL_ID/OPT_ID]
          ↓
[Phase = PARTICIPANT_ENROLLMENT?]
          ↓
         No
          ↓
[Is New Hire / Newly Eligible?]
          ↓
         Yes
          ↓
[Shift Context: Effective Date +62 Days]
          ↓
[Loop: Validate Active Plan/Option Matches Selection]
          ↓
[Loop: Verify Active Dependent Array]
          ↓
     [COUNT = 0?]
          ↓
         Yes
          ↓
[Halt Execution & Throw Custom UI Error Screen]

Technical Components:

Value Sets:

ORA_BEN_PLAN_VS: This value set returns the name of the Plan.

Value Set Format:
Value Set Code      : ORA_BEN_PLAN_VS
Description             : 
Module                      : Benefits
Validation Type     : Table
Value Data Type     : Character
FROM Clause             : ben_pl_f
Value Column Name : name
ID Column Name      : name
WHERE Clause            : 1=1
                                        pl_id =:{PARAMETER.Pl_ID}
                                        and trunc(sysdate) between effective_start_date and effective_end_date

ORA_BEN_OPT_VS: This value set returns the name of the Option.

Value Set Format:
Value Set Code      : ORA_BEN_OPT_VS
Description             : 
Module                      : Benefits
Validation Type     : Table
Value Data Type     : Character
FROM Clause             : ben_opt_f 
Value Column Name : name
ID Column Name      : name
WHERE Clause            : 1=1
                                        opt_id =:{PARAMETER.OPT_ID}
                                        and trunc(sysdate) between effective_start_date and effective_end_date
                                        and name not in ('employee only','decline/waive coverage','employee only esny 10 month','employee only (cw pa)','employee only (under $25,000)','employee only (over $25,000)','employee only (under $25,000) 10 mos','employee only (over $25,000) 10 mos','employee only (over $40,000)','employee only (under $40,000)')

Fast Formulas:

ORA_BEN_DEPENDENT_ELECTION_FF: This Fast Formula is a Post-Election Validation rule that ensures employees designate the required dependents or beneficiaries when electing benefit plans and options that require dependent coverage. The formula validates benefit elections during the post-election process and prevents incomplete or invalid enrollments by returning an error message when dependent information is missing.

The formula type used is Postelection Edit, and it is triggered during the post-election validation phase of Benefits enrollment.

The formula first identifies the current Benefits process phase using BEN_GET_PROCESS_NAME. Validation is skipped when enrollments are loaded through HDL (PARTICIPANT_ENROLLMENT phase) to avoid unnecessary validation errors during data loads.

The formula determines the effective date to be used for validation based on the life event:

For New Hire or Employee Newly Eligible life events, the effective date is calculated as 62 days after the life event occurred date.
For all other life events, the life event occurred date is used as the effective date.

The effective date is applied using CHANGE_CONTEXTS to ensure enrollment and dependent data are evaluated correctly.

The elected plan name and option name are derived using value sets based on the plan ID (PL_ID) and option ID (OPT_ID) available in the context. These values are used consistently to match elected plans with dependent and beneficiary records.

The formula loops through the participant’s elected plans (BEN_PEN_PL_NAME_TN and BEN_PEN_OPT_NAME_TN) to determine whether the employee has elected the specific plan and option currently being validated.

If the selected plan and option are found, the formula identifies the election as requiring dependent coverage by setting the l_family flag to 'Y'.

Only active dependents (valid person ID and coverage start date earlier than coverage end date) are considered valid. The formula counts the number of valid dependents associated with the elected plan and option.

/****************************************************************************** 
FORMULA NAME: ORA_BEN_DEPENDENT_ELECTION_FF
FORMULA TYPE: Postelection Edit
DESCRIPTION : Validates that required dependents or beneficiaries are designated when an employee elects a benefit plan option that requires dependent coverage.

-------------------------------------------------------------------------------
Name        Date          Version   Comments
Oracle      DD-Mon-YYYY   1.0       Initial base version
-------------------------------------------------------------------------------
******************************************************************************/

/*@section - Default Values*/
DEFAULT_DATA_VALUE FOR BEN_PEN_PL_NAME_TN             IS 'My-Default'
DEFAULT_DATA_VALUE FOR BEN_PEN_OPT_NAME_TN            IS 'My-Default'
DEFAULT_DATA_VALUE FOR BEN_EXT_DPNT_PL_NAME           IS 'XYZ'
DEFAULT_DATA_VALUE FOR BEN_EXT_DPNT_OPT_NAME          IS 'ABC'
DEFAULT_DATA_VALUE FOR BEN_EXT_BNF_OPT_NAME           IS 'ABC'
DEFAULT_DATA_VALUE FOR BEN_EXT_DPNT_DPNT_PERSON_ID    IS 1
DEFAULT_DATA_VALUE FOR BEN_EXT_DPNT_DPNT_CVG_STRT_DT  IS '1950/01/01 12:00:00' (date)
DEFAULT_DATA_VALUE FOR BEN_EXT_DPNT_DPNT_CVG_THRU_DT  IS '1950/01/01 12:00:00' (date)
DEFAULT FOR BEN_PIL_LF_EVT_OCRD_DT                     IS '1950/01/01 12:00:00' (date)
DEFAULT FOR BEN_LER_NAME                               IS 'Default Life Event'

/*@section - Return Variables*/
SUCCESSFUL    = 'Y'
ERROR_MESSAGE = ' '

/*@section - Context Variables*/
L_PERSON_ID    = GET_CONTEXT(PERSON_ID, 0)
L_PLAN_ID      = GET_CONTEXT(PL_ID, 0)
L_OPT_ID       = GET_CONTEXT(OPT_ID, 0)
L_FORMULA_NAME = 'ORA_BEN_DEPENDENT_ELECTION_FF'

/*@section - Local Variables*/
i        = 1
j        = 1
l_family = 'N'
l_count  = 1

l_ler_name       = BEN_LER_NAME
l_lf_evt_ocrd_dt = BEN_PIL_LF_EVT_OCRD_DT

/*@section - Plan and Option Identification*/
PLAN_NAME = GET_VALUE_SET(
              'ORA_BEN_PLAN_VS',
              '|=PL_ID=''' || TO_CHAR(L_PLAN_ID) || ''''
            )

OPT_NAME  = GET_VALUE_SET(
              'ORA_BEN_OPT_VS',
              '|=OPT_ID=''' || TO_CHAR(L_OPT_ID) || ''''
            )

/*@section - Process Phase Check*/
L_PHASE = BEN_GET_PROCESS_NAME()

IF (L_PHASE != 'PARTICIPANT_ENROLLMENT')
THEN
(
  /* Determine Effective Date */
  IF (l_ler_name = 'New Hire' OR l_ler_name = 'Employee Newly Eligible')
  THEN
    l_new_effective_date = ADD_DAYS(l_lf_evt_ocrd_dt, 62)
  ELSE
    l_new_effective_date = l_lf_evt_ocrd_dt

  CHANGE_CONTEXTS(EFFECTIVE_DATE = l_new_effective_date)
  (
    /*@section - Enrollment Validation*/
    WHILE BEN_PEN_PL_NAME_TN.EXISTS(i)
    LOOP
    (
      IF (BEN_PEN_PL_NAME_TN[i] = PLAN_NAME
          AND BEN_PEN_OPT_NAME_TN[i] = OPT_NAME)
      THEN
      (
        l_family = 'Y'
        EXIT
      )
      i = i + 1
    )

    /*@section - Dependent Validation*/
    IF l_family = 'Y'
    THEN
    (
      j = 1
      WHILE BEN_EXT_DPNT_PL_NAME.EXISTS(j)
      LOOP
      (
        IF ( BEN_EXT_DPNT_PL_NAME[j] = PLAN_NAME
             AND BEN_EXT_DPNT_OPT_NAME[j] = OPT_NAME
             AND BEN_EXT_DPNT_DPNT_PERSON_ID[j] != 1
             AND BEN_EXT_DPNT_DPNT_CVG_STRT_DT[j] < BEN_EXT_DPNT_DPNT_CVG_THRU_DT[j]
           )
        THEN
        (
          l_count = l_count + 1
          EXIT
        )
        j = j + 1
      )
    )
  )
)

/*@section - Validation Logic*/
IF (l_family = 'Y' AND l_count = 1)
THEN
(
  SUCCESSFUL    = 'N'
  ERROR_MESSAGE = 'You need to designate dependents or beneficiaries for your selected ' 
                  || PLAN_NAME || ' offerings.'
)

/*@section - Return Values*/
RETURN SUCCESSFUL, ERROR_MESSAGE

ORA_BEN_VOL_LIFE_CHILD_FF: This Fast Formula is a Post-Election Validation rule that ensures employees’ elections for ANTHEM Voluntary Life / AD&D - Employee comply with plan rules. The formula validates benefit enrollments during the post-election process and prevents coverage amounts from exceeding 5 times the employee’s annual salary. Additionally, it enforces that Voluntary Life and Voluntary AD&D coverage amounts match.

Employees electing Anthem Voluntary Life / AD&D - Employee coverage may not exceed plan limits based on their annual salary. The purpose of this document is to describe the logic used by the Post-Election Validation Fast Formula to enforce these coverage rules and ensure that employees’ benefit elections comply with plan guidelines.

The validation considers the employee’s enrollment across the following coverage options:

Voluntary Life - Employee
Voluntary AD&D - Employee

Eligibility and coverage limits are determined during the post-election process. If an employee elects coverage that exceeds 5 times their annual salary, or if the Voluntary Life and Voluntary AD&D amounts do not match, the election is marked unsuccessful, and an error message is returned.

Validation Rules:

Coverage amounts for Voluntary Life - Employee cannot exceed 5 times the employee’s annual salary.
Coverage amounts for Voluntary AD&D - Employee cannot exceed 5 times the employee’s annual salary.
If enrolled in Voluntary Life, the employee must enroll in Voluntary AD&D for the same amount.
The validation occurs during the post-election phase of Benefits enrollment.
If any rule is violated, the formula sets SUCCESSFUL = 'N' and returns an error message guiding the employee to correct the election.

/****************************************************************************** 
FORMULA NAME: ORA_BEN_VOL_LIFE_CHILD_FF
FORMULA TYPE: Postelection Edit
DESCRIPTION : When a person makes an election to Anthem Voluntary Life / AD&D - Child(ren), validate the person selected the Anthem Voluntary Life / AD&D - Employee plan.

-------------------------------------------------------------------------------
Name        Date          Version   Comments
Oracle      DD-Mon-YYYY   1.0       Initial base version
-------------------------------------------------------------------------------
******************************************************************************/
DEFAULT_DATA_VALUE FOR BEN_PEN_BNFT_AMT_NN IS 0 
DEFAULT_DATA_VALUE FOR BEN_PEN_PL_NAME_TN is 'My-Default'
DEFAULT_DATA_VALUE for BEN_PEN_OPT_NAME_TN is 'Option Default'
DEFAULT FOR CMP_ASSIGNMENT_SALARY_AMOUNT IS 0 
DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUALIZATION_FACTOR IS 0
DEFAULT FOR BEN_LER_NAME is 'Default Life Event'
DEFAULT FOR BEN_PIL_LF_EVT_OCRD_DT is '1950/01/01 00:00:00'(date)
default for l_slvl_emp_enrld is 'Y'
default for l_slvl_child_enrld is 'Y'

SUCCESSFUL = 'Y'
ERROR_MESSAGE=' '
amount_AD = 0
amount = 0
j=1
l_ler_name = BEN_LER_NAME
l_lf_evt_ocrd_dt = BEN_PIL_LF_EVT_OCRD_DT

l_eff = GET_CONTEXT(EFFECTIVE_DATE, '1951/01/01' (date))

if (l_ler_name = 'New Hire' or l_ler_name ='Employee Newly Eligible') then
(
l_new_effective_date = add_days(l_lf_evt_ocrd_dt,62)
)
else
(l_new_effective_date = l_lf_evt_ocrd_dt)

CHANGE_CONTEXTS(EFFECTIVE_DATE = l_new_effective_date)
(
l_slvl_emp_enrld = ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'Anthem Voluntary Life / AD&D - Employee'
,'NA'
)

l_slvl_child_enrld = ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'Anthem Voluntary Life / AD&D - Child(ren)'
,'NA'
)

while (BEN_PEN_PL_NAME_TN.exists(j)) loop (
if ((l_slvl_child_enrld = 'Y') AND 
    (BEN_PEN_PL_NAME_TN[j]='Anthem Voluntary Life / AD&D - Child(ren)') AND (BEN_PEN_OPT_NAME_TN[j] like '%Voluntary Life - Child%')  

    )
THEN 
(

amount = BEN_PEN_BNFT_AMT_NN[j]
    plan_n = BEN_PEN_PL_NAME_TN[j]
    l_option = BEN_PEN_OPT_NAME_TN[1]
    )
if ((l_slvl_child_enrld = 'Y') AND 
    (BEN_PEN_PL_NAME_TN[j]='Anthem Voluntary Life / AD&D - Child(ren)') AND (BEN_PEN_OPT_NAME_TN[j] like '%Voluntary AD&D%')    
    )
THEN 
(
amount_AD = BEN_PEN_BNFT_AMT_NN[j]
    plan_AD = BEN_PEN_PL_NAME_TN[j]
    l_option_D = BEN_PEN_OPT_NAME_TN[1]
    )

j=j+1
) )

if (amount_AD != amount)
then 
(
SUCCESSFUL='N'
ERROR_MESSAGE= 'If you enrolled in Voluntary Life - Child, you must enroll in Voluntary AD&D FOR THE SAME AMOUNT.'
)

Return SUCCESSFUL,ERROR_MESSAGE

Configuration:

Attaching the Formula to your Benefit Hierarchy:

Navigate to Benefits Administration -> Plan Configuration.
Search for and edit your target Benefit Program.
Move to the Programs or Plans tab layer where your dependent tiers reside.
Locate the Rules configuration sub-region.
Apply the rules as follows:

For your standard medical and dependent offerings: select ORA_BEN_DEPENDENT_ELECTION_FF in the Post-Election Edit input dropdown.
For the specialized child coverage offerings: select ORA_BEN_VOL_LIFE_CHILD_FF in the Post-Election Edit input dropdown on the Anthem Voluntary Life / AD&D - Child(ren) plan record.

Save your changes and initiate a test enrollment cycle via a trial life event to verify your setup.

Conclusion:

Implementing database-driven validation within OAB eliminates manual data auditing by keeping invalid benefit choices out of your database. By combining table-validated value sets with lifecycle phase controls, this architecture remains maintainable, performant, and fully insulated against plan renames or administrative changes.

Furthermore, using cross-plan balancing formulas ensures that dependent options remain aligned with parent choices and match insurance carrier underwriting guidelines exactly. Shifting processing contexts dynamically allows your system to evaluate transactional records reliably, regardless of whether a user enrolls via a regular open enrollment window or a mid-period life event.

Handling Large Payroll Files via Segmented Loops in Oracle Integration Cloud

Chinnamsetti Satyanarayana Swamy — Wed, 22 Jul 2026 10:49:56 +0000

Introduction:

During global enterprise payroll processing cycles, syncing time management, corporate cross-references, and financial allowances between systems introduces complex data handling challenges. Integrating large datasets requires a middleware layer that manages data formatting transformations while operating within cloud memory limits.

A common bottleneck occurs when standard data loops run into string size boundaries while transforming data lines or calling target reporting modules.

This case study demonstrates how to construct a robust, biweekly scheduled processing pipeline within Oracle Integration Cloud (OIC). The solution combines data enrichment using the Snowflake Adapter, array manipulation via Read File in Segments, iterative BI Publisher (BIP) report processing, and PGP-encrypted bulk uploads through HCM Data Loader (HDL).

Technical Integration Architecture:

Rather than straining server boundaries, the data pipeline processes files using a segmented, decoupled, stream-based lifecycle:

1. SFTP Pull ──► Retrieve & Write to Stage Directory
                     │
                     ▼
2. Snowflake Enrich ──► Dynamic Mapping Transformation
                     │
                     ▼
3. Segment Chunking ──► Concatenate Rows into String
                     │
                     ▼
4. Iterative BIP Call ──► Categorize Element / Abs
                     │
                     ▼
5. HDL Load ──► PGP Encrypt, Compress & Monitor ID

Implementation Framework:

1. Inbound Processing & Snowflake Enrichment:
The scheduled orchestration begins by downloading the latest payroll raw file from a secure external SFTP directory server. If no files exist in the path, an inline Switch component catches the empty count (ItemCount = 0) and triggers a clean notifyNoFileFound exit routine, saving system resources.

When a payload is verified, OIC downloads the text file and writes it to a temporary local scratch directory via a Stage File Write action. The integration loop then immediately calls the endpoint getCompanyCodeCostCenterMapping using the Snowflake Adapter connection (MTWY Snowflake Connection). This function passes raw assignment parameters, returning validated corporate cost centers and company segment metadata strings straight into the OIC data object map.

2. Bypassing String Limits via Segment Chunking:
To prevent the application from hitting native string container memory thresholds when handling tens of thousands of employee rows, the workflow leverages an OIC Read File in Segments operation. This tool reads data in controlled, isolated arrays (e.g., chunks of 2,000 records per loop).

Inside the loop, the mapper concatenates variables into a delimited string. It structures individual column variables using a pipe separator (|) and joins row matrices together using standard comma flags (,).

The data transformation generates the following layout:

PERSON_NUM|ASG_NUM|DEPT_CD|COMPANY_CD,PERSON_NUM|ASG_NUM|DEPT_CD|COMPANY_CD

This structured block is mapped directly to consecutive collection fields (BIPParameter 1 through BIPParameter 8) to prepare the data for the next reporting step.

3. Hierarchical Regex Tokenizer & Tri-Directional Slicing:
OIC passes the generated parameter blocks to the Fusion public SOAP utility ExternalReportWSSService to execute a high-performance BI Publisher data model.

The custom SQL engine parses the comma-separated data stream on the fly. It utilizes a hierarchical regex tokenizer (REGEXP_SUBSTR and CONNECT BY level) to break the strings back into individual table rows. It also implements an analytical ranking function (ROW_NUMBER() OVER) to determine active assignment contexts before using a UNION ALL split to organize the data into either Element Entries or Absence Plans automatically:

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                         All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : ORACLE ERP CLOUD
--  Report Name     : ORA PY Kronos Time Usage Scheduled Time Off Data Model 
--  Purpose         : 

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/
WITH input_value AS (
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value1, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value1, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value1, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value1, ',') + 1
  UNION ALL
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value2, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value2, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value2, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value2, ',') + 1
  UNION ALL
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value3, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value3, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value3, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value3, ',') + 1
  UNION ALL
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value4, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value4, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value4, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value4, ',') + 1
  UNION ALL
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value5, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value5, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value5, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value5, ',') + 1
  UNION ALL
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value6, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value6, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value6, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value6, ',') + 1
  UNION ALL
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value7, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value7, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value7, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value7, ',') + 1
  UNION ALL
  SELECT TRIM(REGEXP_SUBSTR(:p_input_value8, '[^,]+', 1, level)) AS param_set
  FROM dual
  WHERE TRIM(REGEXP_SUBSTR(:p_input_value8, '[^,]+', 1, level)) IS NOT NULL
    OR TRIM(REGEXP_SUBSTR(:p_input_value8, '[^,]+', 1, level)) <> ''
  CONNECT BY level <= REGEXP_COUNT(:p_input_value8, ',') + 1
), param_values AS (
  SELECT REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 1, 'n', 1) AS person_number,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 2, 'n', 1) AS position_code,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 3, 'n', 1) AS job_code,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 4, 'n', 1) AS pay_code,
    TO_DATE(REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 5, 'n', 1), 'YYYYMMDD') AS time_recorded_date,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 6, 'n', 1) AS hours,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 7, 'n', 1) AS rate,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 8, 'n', 1) AS shift,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 9, 'n', 1) AS gl_department,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 10, 'n', 1) AS gl_account,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 11, 'n', 1) AS gl_sub_account,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 12, 'n', 1) AS gl_company,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 13, 'n', 1) AS activity,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 14, 'n', 1) AS activity_account_category,
    REGEXP_SUBSTR(param_set, '(.*?)(\||$)', 1, 15, 'n', 1) AS units
  /* ,row_number() over (order by null) as rn */
  FROM input_value
), element_entry_name AS (
    SELECT DISTINCT
        pv.pay_code,
        pett_main.element_name AS element_name,
        CASE
            WHEN pett_main.element_name IS NOT NULL
            THEN ''
            ELSE 'Element Name not found'
        END AS error_reason
    FROM param_values pv
    LEFT OUTER JOIN (
        SELECT pett.element_name,
            pett.description
        FROM pay_element_types_f petf
        INNER JOIN pay_ele_classifications pec
            ON pec.classification_id = petf.secondary_classification_id
        INNER JOIN pay_element_types_tl pett
            ON petf.element_type_id = pett.element_type_id
        WHERE pec.base_classification_name <> 'Standard Earnings Premium'
            AND UPPER(pett.element_name) LIKE '%BC%'
    ) pett_main
  ON pett_main.description = pv.pay_code
), premium_element_entry_name AS (
  SELECT DISTINCT
    pv.pay_code,
    fuci.value AS premium_element_name
  FROM ff_user_tables fut
  INNER JOIN ff_user_rows_f furf
    ON fut.user_table_id = furf.user_table_id
  INNER JOIN ff_user_columns fuc
    ON fut.user_table_id = fuc.user_table_id
  INNER JOIN ff_user_columns_tl fuct
    ON fuc.user_column_id = fuct.user_column_id
  INNER JOIN ff_user_column_instances_f fuci
    ON (
      fuc.user_column_id = fuci.user_column_id AND furf.user_row_id = fuci.user_row_id
    )
  INNER JOIN param_values pv
    ON furf.row_low_range_or_name = pv.pay_code
  WHERE fut.base_user_table_name = 'EARNINGS_TABLE'
    AND fuct.user_column_name = 'PREMIUM_ELEMENT'
), absence_plans AS (
  SELECT DISTINCT
    pv.pay_code,
    fuci.value AS absence_plan_name
  FROM ff_user_tables fut
  INNER JOIN ff_user_rows_f furf
    ON fut.user_table_id = furf.user_table_id
  INNER JOIN ff_user_columns fuc
    ON fut.user_table_id = fuc.user_table_id
  INNER JOIN ff_user_columns_tl fuct
    ON fuc.user_column_id = fuct.user_column_id
  INNER JOIN ff_user_column_instances_f fuci
    ON (
      fuc.user_column_id = fuci.user_column_id AND furf.user_row_id = fuci.user_row_id
    )
  INNER JOIN param_values pv
    ON furf.row_low_range_or_name = pv.pay_code
  WHERE fut.base_user_table_name = 'EARNINGS_TABLE'
    AND fuct.user_column_name = 'ABSENCE_PLAN'
), person AS (
  SELECT DISTINCT
    papf.person_id,
    pv.person_number,
    CASE WHEN papf.person_id IS NOT NULL THEN '' ELSE 'Person not found in Oracle' END AS error_reason
  /* ,pv.rn */
  FROM param_values pv
  LEFT OUTER JOIN per_all_people_f papf
    ON (
      papf.person_number = pv.person_number
      AND TRUNC(SYSDATE, 'DD') BETWEEN papf.effective_start_date AND papf.effective_end_date
    )
), latest_assignment AS (
  SELECT pe.person_number,
    paam.assignment_number,
    ROW_NUMBER() OVER (PARTITION BY paam.person_id ORDER BY paam.effective_start_date DESC) AS rn_assignment
  FROM per_all_assignments_m paam
  INNER JOIN hr_organization_units_f_tl houft
    ON houft.organization_id = paam.business_unit_id
  INNER JOIN person pe
    ON paam.person_id = pe.person_id
  INNER JOIN param_values pv
    ON pe.person_number = pv.person_number
  LEFT OUTER JOIN hr_all_positions_f hapf
    ON paam.position_id = hapf.position_id
  LEFT OUTER JOIN per_jobs_f pjf
    ON paam.job_id = pjf.job_id
  WHERE TRUNC(SYSDATE, 'DD') BETWEEN houft.effective_start_date AND houft.effective_end_date
    AND paam.assignment_type = 'E'
    AND houft.language = USERENV('LANG')
    AND UPPER(houft.name) = UPPER('Oracle BU')
    AND (
      (
        (
                    pv.position_code IS NOT NULL OR pv.position_code <> ''
                )
        AND 
                (
                    hapf.position_code = pv.position_code
                )
      )
      OR (
        (
          pv.position_code IS NULL OR pv.position_code = ''
        )
        AND 
                (
                    pv.job_code IS NOT NULL OR pv.job_code <> ''
                )
        AND 
                (
                    pjf.job_code = pv.job_code
                )
            )
      OR (
        (
          pv.position_code IS NULL OR pv.position_code = ''
        )
        AND 
                (
          pv.job_code IS NULL OR pv.job_code = ''
        )
                AND 
                (
                    TRUNC(pv.time_recorded_date) BETWEEN paam.effective_start_date AND paam.effective_end_date AND paam.primary_flag = 'Y' AND paam.assignment_status_type = 'ACTIVE'
                )   
            )
    )
    )
SELECT *
FROM (
  SELECT pv.person_number, /* rn */
    pv.position_code,
    pv.job_code,
    pv.pay_code,
    TO_CHAR(pv.time_recorded_date, 'YYYY/MM/DD') AS time_recorded_date,
    pv.hours,
    pv.rate,
    pv.shift,
    pv.gl_department,
    pv.gl_account,
    pv.gl_sub_account,
    pv.gl_company,
    pv.activity,
    pv.activity_account_category,
    pv.units,
    la.assignment_number,
    een.element_name,
        TO_CHAR(
            1000 + ROW_NUMBER() OVER (PARTITION BY pv.person_number, pv.pay_code ORDER BY pv.person_number, pv.pay_code, pv.time_recorded_date) - 1
        ) AS create_entry_sequence,
    'Element' AS hdl_type,
    CASE
      WHEN pe.error_reason IS NOT NULL AND een.error_reason IS NOT NULL
      THEN pe.error_reason || ',' || een.error_reason
      WHEN pe.error_reason IS NOT NULL
      THEN pe.error_reason
      WHEN een.error_reason IS NOT NULL
      THEN een.error_reason
      WHEN la.assignment_number IS NULL
      THEN 'Assignment Number not found'
      ELSE ''
    END AS error_reason,
      CASE WHEN pv.gl_company IS NOT NULL AND pv.gl_department IS NOT NULL THEN pv.gl_company || '.' || pv.gl_department ELSE '' END AS CONCAT
  FROM param_values pv
  INNER JOIN person pe
    ON pe.person_number = pv.person_number
  INNER JOIN element_entry_name een
    ON pv.pay_code = een.pay_code
  LEFT OUTER JOIN latest_assignment la
    ON (
      pv.person_number = la.person_number AND la.rn_assignment = 1
    )
  UNION ALL
  SELECT pv.person_number, /* rn */
    pv.position_code,
    pv.job_code,
    pv.pay_code,
    TO_CHAR(pv.time_recorded_date, 'YYYY/MM/DD') AS time_recorded_date,
    pv.hours,
    pv.rate,
    pv.shift,
    pv.gl_department,
    pv.gl_account,
    pv.gl_sub_account,
    pv.gl_company,
    pv.activity,
    pv.activity_account_category,
    pv.units,
    la.assignment_number,
    peen.premium_element_name,
        TO_CHAR(
            1000 + ROW_NUMBER() OVER (PARTITION BY pv.person_number, pv.pay_code ORDER BY pv.person_number, pv.pay_code, pv.time_recorded_date) - 1
        ) AS create_entry_sequence,
    'Element' AS hdl_type,
    CASE WHEN pe.error_reason IS NOT NULL THEN pe.error_reason ELSE '' END AS error_reason,
      CASE WHEN pv.gl_company IS NOT NULL AND pv.gl_department IS NOT NULL THEN pv.gl_company || '.' || pv.gl_department ELSE '' END AS CONCAT
  FROM param_values pv
  INNER JOIN person pe
    ON pe.person_number = pv.person_number
  INNER JOIN premium_element_entry_name peen
    ON pv.pay_code = peen.pay_code
  INNER JOIN latest_assignment la
    ON (
      pv.person_number = la.person_number AND la.rn_assignment = 1
    )
  UNION ALL
  SELECT pv.person_number, /* rn */
    pv.position_code,
    pv.job_code,
    pv.pay_code,
    TO_CHAR(pv.time_recorded_date, 'YYYY/MM/DD') AS time_recorded_date,
    pv.hours,
    pv.rate,
    pv.shift,
    pv.gl_department,
    pv.gl_account,
    pv.gl_sub_account,
    pv.gl_company,
    pv.activity,
    pv.activity_account_category,
    pv.units,
    la.assignment_number,
    ap.absence_plan_name,
        '' AS create_entry_sequence,
    'Absence' AS hdl_type,
    CASE WHEN pe.error_reason IS NOT NULL THEN pe.error_reason ELSE '' END AS error_reason,
      CASE WHEN pv.gl_company IS NOT NULL AND pv.gl_department IS NOT NULL THEN pv.gl_company || '.' || pv.gl_department ELSE '' END AS CONCAT
  FROM param_values pv
  INNER JOIN person pe
    ON pe.person_number = pv.person_number
  INNER JOIN absence_plans ap
    ON pv.pay_code = ap.pay_code
  INNER JOIN latest_assignment la
    ON (
      pv.person_number = la.person_number AND la.rn_assignment = 1
    )
)
ORDER BY TO_NUMBER(person_number)

An inline validation rule (reportBytes != DefaultContent) catches any blank data blocks early. This ensures that only valid, populated responses move forward to the file writing stage.

4: Formatting Native HDL Schema Files:
OIC decodes the Base64 binary text from the report response and reads the entries line-by-line. Based on the calculated hdl_type tag column value, the integration splits rows into two separate local stage files, structuring them to match the exact schema layouts expected by the HCM Data Loader engine:

File 1: ElementEntryWithCosting.dat:

This file handles payroll allocations and maps Snowflake dimensions directly to the entry costing segments (Segment1 through Segment3):

METADATA|ElementEntryWithCosting|SourceSystemOwner|SourceSystemId|ElementName|EffectiveStartDate|EffectiveEndDate|AssignmentNumber|EntryType|InputValueName1|ScreenEntryValue1|InputValueName2|ScreenEntryValue2|CreateEntrySequence|Segment1|Segment2|Segment3
MERGE|ElementEntryWithCosting|HRC_SQLLOADER|HRC_SQLLOADER_E112698_102|BC101 - Reg Pay|2025/08/02|4712/12/31|E112698|E|Hours|0.25|Rate|0|1000|6000|1001|6000

File 2: PersonAccrualDetail.dat:

METADATA|PersonAccrualDetail|PersonNumber|WorkTermsNumber|PlanName|AccrualType|Value|ProcdDate|AdjustmentReason
MERGE|PersonAccrualDetail|110684|ET110684|Extended Leave|ADJOTH|4|2025/07/26|CP

Once written, both files are compressed into a single zip archive. To maintain absolute data privacy compliance for sensitive employee financial data, the integration runs a secure command wrapper that encrypts the zip payload using a corporate PGP Public Key before passing the file to the HCM Cloud Adapter bulk load service.

4. Process Monitoring and Diagnostic Logging Flow:

The integration tracks execution health by capturing the unique Process ID returned by the background loading engine. A structured tracking matrix logs every phase of the lifecycle:

[OIC_TRACKING]  [BIWEEKLY_RUN] Started payroll pipeline sequence.
[SFTP_STAGE]   Successfully uncompressed source text stream.
[SNOWFLAKE]    Enriched 4,500 record streams with matching company codes.
[SEGMENT_LOOP] Processing File Chunk 1 (Rows 1-2000). Total length within limits.
[BIP_INVOKE]   Iterative Data Model call complete. Generated XML response elements.
[HDL_ENGINE]   PGP Encryption complete. Submitted zip payload. Process Tracking ID: 890241.
[HDL_STATUS]   Process ID 890241 returned: SUCCESS. Sending confirmation alerts.
[SFTP_ARCHIVE] Cleaned source folder pathing parameters. Pipeline shut down.

If an processing exception occurs, global fault-handling catch blocks intercept the failed payload instance, generate a detailed administrative alert, and safely archive the source file to prevent duplicate processing cycles.

5. Summary:

This integration has been developed to support the creation of Element Entries and Absence Entries as part of the payroll process. It operates on a biweekly schedule, automatically retrieving the latest encoded file from the SFTP server. Once the file is received, it is decoded and written to the OIC temporary directory using the Stage File action. The data is then processed and transformed, incorporating department and company code mappings sourced through the Snowflake adapter to ensure consistency and accuracy.

Following the transformation, the data is structured by separating columns with a pipe (“|”) and combining rows using commas. This formatting is performed within the “Read File in Segments” operation, enabling efficient processing and transmission to a BI Publisher report. Due to input size limitations, the data is divided into manageable segments, and the report is triggered iteratively until all records are processed. Based on the report output, records are categorized as either Element Entries or Absence Entries, and the corresponding DAT files are generated. These files are then compressed and encrypted using a PGP key before being submitted to Oracle HCM Cloud through the HCM Data Loader.

The outcome of the data load process is monitored using the generated Process ID, allowing appropriate success or failure notifications to be issued. Once processing is complete, the source file is archived from the SFTP directory. The integration also includes configurable flags to manage notifications and encryption requirements. Comprehensive error handling is implemented through both default and global fault handlers, and reusable integrations are leveraged to ensure consistent and efficient notification delivery and SFTP file movement.

Conclusion:

Combining BI Publisher, Oracle Integration Cloud, and HCM Data Loader into a single automated pipeline creates a highly scalable data integration framework. By extracting datasets in bulk using high-performance database models and processing data parsing entirely within local OIC memory maps, you protect environment stability, ensure clean data updates, and avoid system failures during business-critical integration windows.

Handling Dependent Certification Reuse in Oracle Advanced Benefits Using Custom Fast Formulas

Chinnamsetti Satyanarayana Swamy — Tue, 21 Jul 2026 09:02:27 +0000

Introduction:

In Oracle Advanced Benefits (OAB), ensuring compliance through document verification is a standard operational requirement. During open enrollment or specific life events, organizations frequently mandate that employees upload supporting documentation, such as a birth or marriage certificate, to validate dependent eligibility.

However, standard out-of-the-box system behavior often creates a frustrating user experience, every time a fresh enrollment lifecycle event triggers, the system creates a new Action Item demanding a fresh document upload. This happens even if the employee already provided a verified, approved certificate during a previous enrollment cycle. Also if the user submits the document from document of Records prior enrolling, it also frustrates to again submit into the system.

To eliminate this redundant administrative burden, technical consultants can deploy an upgrade safe automation framework. By combining a Dependent Certification Required Fast Formula with table-validated Value Sets, you can programmatically inspect historical document records. If a verified document already exists for the dependent, the system automatically clears the action item, enabling seamless document reuse.

Solution:

Rather than forcing benefits specialists to manually clear thousands of repetitive action items, the formula acts as an active gatekeeper during the life event evaluation process.

 [Life Event / Enrollment Executes]
                  │
                  ▼
 ┌──────────────────────────────────────────────┐
 │   OAB Engine Invokes Certification Rule FF   │
 └──────────────────────────────────────────────┘
                  │
                  ▼
 ┌──────────────────────────────────────────────┐
 │  Formula Calls Table-Validated Value Set     │
 └──────────────────────────────────────────────┘
                  │
                  ├──► Queries HR_DOCUMENT_RECORDS for Dependent
                  │
                  ▼
 ┌──────────────────────────────────────────────┐
 │    Document Found & Status == 'APPROVED'?     │
 └──────────────────────────────────────────────┘
          │                            │
      [YES]                        [NO]
          │                            │
          ▼                            ▼
 ┌──────────────────┐        ┌──────────────────┐
 │ Return REQUIRED  │        │ Return REQUIRED  │
 │     = 'N'        │        │     = 'Y'        │
 └──────────────────┘        └──────────────────┘
          │                            │
          ▼                            ▼
 (Action Item Cleared)       (Action Item Created)

Technical Components:

This requirement can be fulfilled by creating two value sets along with Fast Formulas for the corresponding certifications.

Value Sets:

ORA_BEN_DEP_CNT_CHK_VS: This value set is responsible to return the number of eligible dependents for an employee.

select 
    to_char(to_number(counter))
from 
    (select level counter from dual connect by   level <= 50)
where 
    1=1
    and counter =
    (
    select 
        count(distinct bed.dpnt_person_id)
    from 
        per_contact_relships_f pcrf
        inner join ben_elig_dpnt bed on pcrf.contact_person_id = bed.dpnt_person_id
        inner join per_all_people_f papf on papf.person_id = pcrf.person_id
        left outer join ben_per_le_habits_cov_f bplhcf on pcrf.contact_person_id = bplhcf.person_id and :{PARAMETER.P_EFFECTIVE_DATE} between bplhcf.effective_start_date and bplhcf.effective_end_date
    where 
        1=1
        and pcrf.contact_type = :{PARAMETER.P_RELATION_TYPE}
        and pcrf.person_id = :{PARAMETER.P_PERSON_ID}
        and :{PARAMETER.P_EFFECTIVE_DATE} between pcrf.effective_start_date and pcrf.effective_end_date
        and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
    )

ORA_BEN_DOC_REC_CHK_VS: This value set is responsible to return the number of certain documents available in the Documents of Records.

select 
    to_char(to_number(counter))
from 
    (select level counter from dual connect by   level <= 50)
where 
    1=1
    and counter =
    (
    select 
        count(distinct documents_of_record_id)
    from 
        hr_documents_of_record hdor
        inner join hr_document_types_tl hdtt on hdor.document_type_id = hdtt.document_type_id
        inner join per_all_people_f papf on papf.person_id = hdor.person_id
    where 
        1=1
        and hdor.person_id = :{PARAMETER.P_PERSON_ID}
        and upper(hdtt.document_type) = :{PARAMETER.P_DOCUMENT_TYPE}
        and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
    )

Fast Formulas:

Adoption Certificate Formula: This formula will clears the action item to submit the document if it is already submitted either from previous enrollment or from documents of records.

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                         All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : HCM Benefits
--  Formula  Name   : ORA_BEN_DEP_ADOP_CERT_REQ_FF
--  Formula Type    : Dependent Certification Required
--  Purpose         : This formula is used to determine if an individual needs to provide Adoption certification for their dependents when enrolling in benefits
                                            Programs

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/

/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0

/*@section - Input variables */

INPUTS are CON_PERSON_ID(number)


/*@section - Local variable declarations and initializations*/
l_dpnt_id                           =       CON_PERSON_ID
l_person_id             =   get_context(PERSON_ID,0)
l_effective_date        =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn             =       'N'
l_adopted_child_cnt     =   0
l_document_cnt              =   0
l_relation_type             =   'A'
l_document_type             =   'ADOPTION CERTIFICATE'

/* 
log = ess_log_write('--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date)) 
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))

/*@section - Return Variable initialization*/
L_OUTPUT                    =       'Y'

/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Adoption certificate')
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
    L_OUTPUT = 'N'
    /*
    log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
    l_adopted_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type||''''))
    if isnull(l_adopted_child_cnt) = 'N' then
    (
        l_adopted_child_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_adopted_child_cnt = '+to_char(l_adopted_child_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_adopted_child_cnt = '+to_char(l_adopted_child_cnt))

    l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
    if isnull(l_document_cnt) = 'N' then
    (
        l_document_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    if (l_document_cnt >= l_adopted_child_cnt and l_adopted_child_cnt != 0) then
    (
        L_OUTPUT = 'N'
        /*
        log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
        */
        l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
    )
)
/*
log = ess_log_write('ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_ADOP_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_ADOP_CERT_REQ_FF Formula Ends--------------') 
/*@section - Return the calculated value*/
return L_OUTPUT

Similarly we have formulas for other certificates mentioned below.

Birth Certificate Formula:

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : HCM Benefits
--  Formula  Name   : ORA_BEN_DEP_BIR_CERT_REQ_FF
--  Formula Type    : Dependent Certification Required
--  Purpose         : This formula is used to determine if an individual needs to provide birth certificate for their dependents when enrolling in benefits programs

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/

/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0

/*@section - Input variables */

INPUTS are CON_PERSON_ID(number)


/*@section - Local variable declarations and initializations*/
l_dpnt_id                   =       CON_PERSON_ID
l_person_id       =   get_context(PERSON_ID,0)
l_effective_date  =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn     =       'N'
l_total_cnt             =   0
l_child_cnt             =   0
l_step_child_cnt    =   0
l_document_cnt      =   0
l_relation_type =   'C'
l_document_type     =   'BIRTH CERTIFICATE'

/* 
log = ess_log_write('--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date)) 
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))

/*@section - Return Variable initialization*/
L_OUTPUT                    =       'Y'

/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Birth certificate')
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
    L_OUTPUT = 'N'
    /*
    log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
    l_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type||''''))
    if isnull(l_child_cnt) = 'N' then
    (
        l_child_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_child_cnt = '+to_char(l_child_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_child_cnt = '+to_char(l_child_cnt))

    l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
    if isnull(l_document_cnt) = 'N' then
    (
        l_document_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    if (l_document_cnt >= l_child_cnt and l_child_cnt != 0) then
    (
        L_OUTPUT = 'N'
        /*
        log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
        */
        l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
    )
)
/*
log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_BIR_CERT_REQ_FF Formula Ends--------------') 
/*@section - Return the calculated value*/
return L_OUTPUT

Legal Custody Certificate Formula:

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : HCM Benefits
--  Formula  Name   : ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF
--  Formula Type    : Dependent Certification Required
--  Purpose         : This formula is used to determine if an individual needs to provide legal custody certification for their dependents when enrolling in 
                                            benefits programs

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/

/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0

/*@section - Input variables */

INPUTS are CON_PERSON_ID(number)


/*@section - Local variable declarations and initializations*/
l_dpnt_id                           =       CON_PERSON_ID
l_person_id             =   get_context(PERSON_ID,0)
l_effective_date        =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn             =       'N'
l_foster_child_cnt      =   0
l_document_cnt              =   0
l_relation_type             =   'O'
l_document_type             =   'LEGAL CUSTODY CERTIFICATE'

/* 
log = ess_log_write('--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date)) 
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))

/*@section - Return Variable initialization*/
L_OUTPUT                    =       'Y'

/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Legal custody certificate')
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
    L_OUTPUT = 'N'
    /*
    log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
    l_foster_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type||''''))
    if isnull(l_foster_child_cnt) = 'N' then
    (
        l_foster_child_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_foster_child_cnt = '+to_char(l_foster_child_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_foster_child_cnt = '+to_char(l_foster_child_cnt))

    l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
    if isnull(l_document_cnt) = 'N' then
    (
        l_document_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    if (l_document_cnt >= l_foster_child_cnt and l_foster_child_cnt != 0) then
    (
        L_OUTPUT = 'N'
        /*
        log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
        */
        l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
    )
)
/*
log = ess_log_write('ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_LEG_CUST_CERT_REQ_FF Formula Ends--------------') 
/*@section - Return the calculated value*/
return L_OUTPUT

Marriage Certificate Formula:

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : HCM Benefits
--  Formula  Name   : ORA_BEN_DEP_MAR_CERT_REQ_FF
--  Formula Type    : Dependent Certification Required
--  Purpose         : This formula is used to determine if an individual needs to provide Marriage certification for their dependents when enrolling in benefits 
                                            programs

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/

/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0

/*@section - Input variables */

INPUTS are CON_PERSON_ID(number)


/*@section - Local variable declarations and initializations*/
l_dpnt_id                   =       CON_PERSON_ID
l_person_id       =   get_context(PERSON_ID,0)
l_effective_date  =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn     =       'N'
l_total_cnt             =   0
l_spouse_cnt            =   0
l_step_child_cnt    =   0
l_document_cnt      =   0
l_relation_type1    =   'S'
l_relation_type2    =   'T'
l_document_type     =   'MARRIAGE CERTIFICATE'

/* 
log = ess_log_write('--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date)) 
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))

/*@section - Return Variable initialization*/
L_OUTPUT                    =       'Y'

/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Marriage certificate')
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
    L_OUTPUT = 'N'
    /*
    log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(
    l_spouse_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type1||''''))
    if isnull(l_spouse_cnt) = 'N' then
    (
        l_spouse_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_spouse_cnt = '+to_char(l_spouse_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_spouse_cnt = '+to_char(l_spouse_cnt))

    l_step_child_cnt = to_number(get_value_set('ORA_BEN_DEP_CNT_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_RELATION_TYPE='''||l_relation_type2||''''))
    if isnull(l_step_child_cnt) = 'N' then
    (
        l_step_child_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_step_child_cnt = '+to_char(l_child_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_step_child_cnt = '+to_char(l_step_child_cnt))
    l_total_cnt = l_spouse_cnt + l_step_child_cnt
    /*
    log = ess_log_write('ORA_BEN_DEP_BIR_CERT_REQ_FF l_total_cnt_cnt = '+to_char(l_total_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_BIR_CERT_REQ_FF l_total_cnt_cnt = '+to_char(l_total_cnt))

    l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
    if isnull(l_document_cnt) = 'N' then
    (
        l_document_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    if (l_document_cnt >= l_total_cnt and l_total_cnt != 0) then
    (
        L_OUTPUT = 'N'
        /*
        log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
        */
        l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
    )
)
/*
log = ess_log_write('ORA_BEN_DEP_MAR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_MAR_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_MAR_CERT_REQ_FF Formula Ends--------------') 
/*@section - Return the calculated value*/
return L_OUTPUT

** Proof of External Coverage Formula:**

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : HCM Benefits
--  Formula  Name   : ORA_BEN_DEP_PEC_CERT_REQ_FF
--  Formula Type    : Dependent Certification Required
--  Purpose         : This formula is used to determine if an individual needs to provide Proof of External Coverage certification when enrolling in benefits
                                            programs

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/

/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0

/*@section - Input variables */

INPUTS are CON_PERSON_ID(number)


/*@section - Local variable declarations and initializations*/
l_dpnt_id                   =       CON_PERSON_ID
l_person_id       =   get_context(PERSON_ID,0)
l_effective_date  =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn     =       'N'
l_document_cnt      =   0
l_document_type     =   'PROOF OF EXTERNAL COVERAGE'

/* 
log = ess_log_write('--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date)) 
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))

/*@section - Return Variable initialization*/
L_OUTPUT                    =       'Y'

/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Proof of external coverage')
/*
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
    L_OUTPUT = 'N'
    /*
    log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(   
    l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
    if isnull(l_document_cnt) = 'N' then
    (
        l_document_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    if (l_document_cnt > 0) then
    (
        L_OUTPUT = 'N'
        /*
        log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
        */
        l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
    )
)
/*
log = ess_log_write('ORA_BEN_DEP_PEC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PEC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PEC_CERT_REQ_FF Formula Ends--------------') 
/*@section - Return the calculated value*/
return L_OUTPUT

Proof of Good Health Formula:

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : HCM Benefits
--  Formula  Name   : ORA_BEN_DEP_PGH_CERT_REQ_FF
--  Formula Type    : Dependent Certification Required
--  Purpose         : This formula is used to determine if an individual needs to provide Proof of Good Health certification when enrolling in benefits

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/

/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0

/*@section - Input variables */

INPUTS are CON_PERSON_ID(number)


/*@section - Local variable declarations and initializations*/
l_dpnt_id                   =       CON_PERSON_ID
l_person_id       =   get_context(PERSON_ID,0)
l_effective_date  =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn     =       'N'
l_document_cnt      =   0
l_document_type     =   'PROOF OF GOOD HEALTH'

/* 
log = ess_log_write('--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date)) 
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))

/*@section - Return Variable initialization*/
L_OUTPUT                    =       'Y'

/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Proof of good health')
/*
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
    L_OUTPUT = 'N'
    /*
    log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(   
    l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
    if isnull(l_document_cnt) = 'N' then
    (
        l_document_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    if (l_document_cnt > 0) then
    (
        L_OUTPUT = 'N'
        /*
        log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
        */
        l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
    )
)
/*
log = ess_log_write('ORA_BEN_DEP_PGH_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_PGH_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_PGH_CERT_REQ_FF Formula Ends--------------') 
/*@section - Return the calculated value*/
return L_OUTPUT

Proof of Other Coverage Formula:

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : HCM Benefits
--  Formula  Name   : ORA_BEN_DEP_POC_CERT_REQ_FF
--  Formula Type    : Dependent Certification Required
--  Purpose         : This formula is used to determine if an individual needs to provide Proof of Other Coverage certification when enrolling in benefits or other programs

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    dd-mm-yyyy    Satyanarayana Swamy Chinnamsetti   1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/

/*@section - Default values for input variables (DBIs)*/
default for CON_PERSON_ID is 0

/*@section - Input variables */

INPUTS are CON_PERSON_ID(number)


/*@section - Local variable declarations and initializations*/
l_dpnt_id                   =       CON_PERSON_ID
l_person_id       =   get_context(PERSON_ID,0)
l_effective_date  =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_chk_dpnt_ctfn     =       'N'
l_document_cnt      =   0
l_document_type     =   'PROOF OF OTHER COVERAGE'

/* 
log = ess_log_write('--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date)) 
*/
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Begins--------------')
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_dpnt_id = '+to_char(l_dpnt_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_person_id = '+to_char(l_person_id))
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_effective_date = '+to_char(l_effective_date))

/*@section - Return Variable initialization*/
L_OUTPUT                    =       'Y'

/*@section - Business Logic and Interface*/
l_chk_dpnt_ctfn = BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD' ,'PROVIDED' ,'NA_CHECK_ONCE' ,'NA' ,to_char(l_dpnt_id) ,'Proof of other coverage')
/*
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_chk_dpnt_ctfn = '+l_chk_dpnt_ctfn)
if (l_chk_dpnt_ctfn = 'Y') then
(
    L_OUTPUT = 'N'
    /*
    log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-1 = '+L_OUTPUT)
)
else
(   
    l_document_cnt = to_number(get_value_set('ORA_BEN_DOC_REC_CHK_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_DOCUMENT_TYPE='''||l_document_type||''''))
    if isnull(l_document_cnt) = 'N' then
    (
        l_document_cnt = 0
    )
    /*
    log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    */
    l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_document_cnt = '+to_char(l_document_cnt))
    if (l_document_cnt > 0) then
    (
        L_OUTPUT = 'N'
        /*
        log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
        */
        l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_output step-2 = '+L_OUTPUT)
    )
)
/*
log = ess_log_write('ORA_BEN_DEP_POC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
log = ess_log_write('--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Ends--------------')
*/
l_id = add_rlog (-1, -1, 'ORA_BEN_DEP_POC_CERT_REQ_FF l_output final step = '+L_OUTPUT)
l_id = add_rlog (-1, -1, '--------------ORA_BEN_DEP_POC_CERT_REQ_FF Formula Ends--------------') 
/*@section - Return the calculated value*/
return L_OUTPUT

Configuration:

Navigate to Benefits Administration -> Plan Configuration -> Select Plan -> Designation Requirements -> select Plan Type -> click on Life event -> Choose Life event -> Edit the certification type and select the Determination Formula.

Conclusion:

By shifting document verification requirements away from rigid out-of-the-box triggers and utilizing targeted table-validated calculations, organizations can significantly improve the employee enrollment experience. This architecture ensures that historical compliance validations remain intact, data silos between Core HR and Advanced Benefits are removed, and system resources are preserved during high-volume batch runs.

Handling Loss of Dependent Eligibility Using Default Enrollment Formulas

Chinnamsetti Satyanarayana Swamy — Mon, 20 Jul 2026 09:08:50 +0000

Introduction

In Oracle Advanced Benefits (OAB), handling changes in dependent eligibility such as a child "aging out" on their 26th birthday is a frequent operational challenge. If left to standard system configurations, processing a Loss of Eligibility (LOE) life event can terminate coverage entirely or drop the employee into incorrect enrollment tiers. This creates significant downstream reconciliation issues for both HR and Payroll.

To maintain compliance and reduce manual work, organizations can implement an upgrade safe automation framework using a Default Enrollment Fast Formula. This technical guide explains how to design an enterprise-grade cascade system across multiple plan types (including Medical, HSA, Voluntary Life, Accidental Insurance and Critical Illness) based on the number and relationship type of remaining eligible dependents.

Business Requirement:

To handle these transitions correctly, the system must process two distinct dependent termination timelines and coordinate complex tier reductions across several coverage plans.

End-of-Month (EOM) Horizon: Coverage for Medical, Dental, and Vision plans terminates at the end of the calendar month in which the dependent turns 26.
Exact-Day Horizon: Coverage for HSA, Voluntary Life, Critical Illness, and Accidental Insurance terminates exactly on the dependent's 26th birthday.

Dependent types are eligible as Dependents:

Child
Step Child
Adopted child
Foster child
Domestic partner child

Medical /Dental / Vision:

When processing Life Events, the option should be automatically changed or defaulted to next lower option for plans (Medical plan/ Dental plan / Vision Plan Types) based on eligibility.
If employee is enrolled in “Employee + Family” then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 2 or more eligible dependents, the enrollment would remain in “Employee + Family” option itself
If employee is enrolled in “Employee + Family” and has 2 dependents enrolled (one is spouse, one is child), then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee + Spouse” option if remaining eligible dependent has relationship type “spouse” OR “Employee + Child(ren)” option If remaining eligible dependent has relationship type “Child”/”Step child”/”Adopted Child”/”Foster child”/” Domestic partner child”
If employee is enrolled in “Employee + Spouse” and has spouse enrolled, then spouse loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
If employee is enrolled in “Employee + Child(ren)” and has 1 child enrolled, then child loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
If employee is enrolled in “Employee + Child(ren)” and has more than 2 dependents then one of the dependent loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee + Child(ren)” option
If employee is enrolled in “Employee + Child(ren)” and 1 dependent then the dependent loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in in “Employee Only” option
If employee is enrolled in “Employee Only” and has more than 2 dependents but has not enrolled any dependent, then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee Only” option

Health Savings Account:
The Health Saving Account (HSA) plan does not enroll dependents. The options which are related to Plan would get enabled based on the number of dependents available for employee.
NOTE: HSA plan needs to be validated only when CDHP is elected or else this might be always Waive. During Waive. No need to perform anything.
When processing Life Events, the option should be automatically changed or defaulted to next lower option for the Health Savings Account plan based on eligibility.

If employee is enrolled in “Health Savings Account Contribution - Family” then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 1 or more eligible dependents, the enrollment would remain in “Health Savings Account Contribution - Family” option itself
If employee is enrolled in “Health Savings Account Contribution - Family” and has 1 dependent, the the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Health Savings Account Contribution - Individual” option
If employee is enrolled in “Health Savings Account Contribution - Individual” and has 1 or more dependents but has not enrolled any dependent, then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Health Savings Account Contribution - Individual” option

Voluntary Life Dependent Children Insurance:

The Voluntary Life Dependent Children Insurance do enroll dependents. The options which are related to plan would get enabled based on the number/type of dependents available for employee.

When processing Life Events, the option should be automatically changed or defaulted to next lower option for Voluntary Spouse Life Insurance and Voluntary Spouse AD&D Insurance Plans based on eligibility.

If employee is enrolled in any coverage option of Voluntary Child Life Insurance or Voluntary Child AD&D Insurance, then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 1 or more eligible dependents, the enrollment would remain in their original option itself
If employee is enrolled in “Voluntary Child Life Insurance” or “Voluntary Child AD&D Insurance then their child loses eligibility, on processing the Life events (Loss of Eligibility), the enrollment should automatically enroll in “Waive” option itself

Critical Illness:

When processing Life Events, the option should be automatically changed or defaulted to next lower option for 3 Critical Illness plans based on eligibility.
If employee is enrolled in any coverage option of Critical Illness Insurance, then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 1 or more eligible dependents, the enrollment would remain in their original option itself
If employee is enrolled in Critical Illness Insurance then their child loses eligibility, on processing the Life events (Loss of Eligibility), the enrollment should automatically enroll in “Waive” option itself Note: There are 3 critical illness plans - Critical Illness, Critical Illness - Employee, Critical Illness – Spouse.

Accidental Insurance:
When processing Life Events, the option should be automatically changed or defaulted to next lower option for Accidental Insurance Plans based on eligibility.

If employee is enrolled in “Employee + Family” then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 2 or more eligible dependents, the enrollment would remain in “Employee + Family” option itself
If employee is enrolled in “Employee + Family” and has 2 dependents enrolled (one is spouse, one is child), then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee + Spouse” option if remaining eligible dependent has relationship type “spouse” OR “Employee + Child(ren)” option If remaining eligible dependent has relationship type “Child”/”Step child”/”Adopted Child”/”Foster child”/” Domestic partner child”
If employee is enrolled in “Employee + Spouse” and has spouse enrolled, then spouse loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
If employee is enrolled in “Employee + Child(ren)” and has 1 child enrolled, then child loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
If employee is enrolled in “Employee + Child(ren)” and has more than 2 dependents then one of the dependents loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee + Child(ren)” option
If employee is enrolled in “Employee + Child(ren)” and 1 dependent then the dependent loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in in “Employee Only” option
If employee is enrolled in “Employee Only” and has more than 2 dependents but has not enrolled any dependent, then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee Only” option

Development Information:

To Handle this requirement, I've created 5 Fast Formulas for different plans and 2 value Sets to check the dependent's eligibility and are as follows.

Value Sets:

ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS: This value set is responsible to fetch the count of Eligible dependents available for an employee.

SQL Query:
select
    to_char(bed.dpnt_person_id)||'|'||pcrf.contact_type||'|'||bplhcf.registered_disabled_flag
from
    per_contact_relships_f pcrf,
  ben_elig_dpnt bed,
  per_all_people_f papf,
  ben_per_le_habits_cov_f bplhcf
where
    1=1
    and pcrf.contact_person_id = bed.dpnt_person_id
  and papf.person_id = pcrf.person_id
  and pcrf.contact_type in ('S','C','A','O','T','R')
  and pcrf.person_id = :{PARAMETER.P_PERSON_ID}
  and pcrf.contact_person_id = bplhcf.person_id(+)
  and :{PARAMETER.P_EFFECTIVE_DATE} between bed.elig_strt_dt and bed.elig_thru_dt
  and :{PARAMETER.P_EFFECTIVE_DATE} between pcrf.effective_start_date and pcrf.effective_end_date
  and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
  and :{PARAMETER.P_EFFECTIVE_DATE} between bplhcf.effective_start_date(+) and bplhcf.effective_end_date(+)
  and bed.dpnt_person_id > :{PARAMETER.P_LIMITER}
order by 
  bed.dpnt_person_id
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Value Set Format:
Value Set Code      : ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS
Description             : 
Module                      : Benefits
Validation Type     : Table
Value Data Type     : Character
FROM Clause             : per_contact_relships_f pcrf, ben_elig_dpnt bed, per_all_people_f papf, ben_per_le_habits_cov_f bplhcf
Value Column Name : to_char(bed.dpnt_person_id)||'|'||pcrf.contact_type||'|'||bplhcf.registered_disabled_flag
ID Column Name      : to_char(bed.dpnt_person_id)||'|'||pcrf.contact_type||'|'||bplhcf.registered_disabled_flag
WHERE Clause            : 1=1
                                        and pcrf.contact_person_id = bed.dpnt_person_id
                                        and papf.person_id = pcrf.person_id
                                        and pcrf.contact_type in ('S','C','A','O','T','R')
                                        and pcrf.person_id = :{PARAMETER.P_PERSON_ID}
                                        and pcrf.contact_person_id = bplhcf.person_id(+)
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between bed.elig_strt_dt and bed.elig_thru_dt
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between pcrf.effective_start_date and pcrf.effective_end_date
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between bplhcf.effective_start_date(+) and bplhcf.effective_end_date(+)
                                        and bed.dpnt_person_id > :{PARAMETER.P_LIMITER}
ORDER BY Clause     : bed.dpnt_person_id

ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS: This value set is responsible to fetch the count of enrolled dependents available for an employee.

SQL Query:
select
    to_char(becp.dpnt_person_id)
from
    ben_prtt_enrt_rslt bper,
    ben_pl_f bpf,
    ben_opt_f bof,
    ben_ler_f blf,
    ben_per_in_ler bpil,
    ben_pl_typ_f bptf,
    ben_elig_cvrd_dpnt becp
where
    1=1
    and bper.pl_id =    bpf.pl_id(+)
    and bper.opt_id = bof.opt_id(+)
    and bper.pl_typ_id =    bptf.pl_typ_id
    and bper.ler_id =   blf.ler_id
    and bper.ler_id =   blf.ler_id
    and bper.ler_id =   blf.ler_id
    and blf.ler_id = bpil.ler_id
    and bper.per_in_ler_id =    bpil.per_in_ler_id
    and bper.prtt_enrt_rslt_id = becp.prtt_enrt_rslt_id
    and :{PARAMETER.P_EFFECTIVE_DATE} between bpf.effective_start_date(+) and bpf.effective_end_date(+)
    and :{PARAMETER.P_EFFECTIVE_DATE} between bof.effective_start_date(+) and bof.effective_end_date(+)
    and :{PARAMETER.P_EFFECTIVE_DATE} between bptf.effective_start_date and bptf.effective_end_date
    and :{PARAMETER.P_EFFECTIVE_DATE} between blf.effective_start_date and blf.effective_end_date   
    and :{PARAMETER.P_EFFECTIVE_DATE} between becp.cvg_strt_dt and becp.cvg_thru_dt
    and bpil.per_in_ler_stat_cd  in ('STRTD','PROCD')
    and bper.person_id = :{PARAMETER.P_PERSON_ID}
    and bper.pl_typ_id = :{PARAMETER.P_PLAN_TYPE_ID}
    and bper.pl_id = :{PARAMETER.P_PLAN_ID}
    and becp.dpnt_person_id > :{PARAMETER.P_LIMITER}    
order by 
    becp.dpnt_person_id
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Value Set Format:
Value Set Code      : ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS
Description             : 
Module                      : Benefits
Validation Type     : Table
Value Data Type     : Character
FROM Clause             : ben_prtt_enrt_rslt bper, ben_pl_f bpf, ben_opt_f bof, ben_ler_f blf, ben_per_in_ler bpil, ben_pl_typ_f bptf, ben_elig_cvrd_dpnt becp
Value Column Name : to_char(becp.dpnt_person_id)
ID Column Name      : to_char(becp.dpnt_person_id)
WHERE Clause            : 1=1
                                        and bper.pl_id =    bpf.pl_id(+)
                                        and bper.opt_id = bof.opt_id(+)
                                        and bper.pl_typ_id =    bptf.pl_typ_id
                                        and bper.ler_id =   blf.ler_id
                                        and bper.ler_id =   blf.ler_id
                                        and bper.ler_id =   blf.ler_id
                                        and blf.ler_id = bpil.ler_id
                                        and bper.per_in_ler_id =    bpil.per_in_ler_id
                                        and bper.prtt_enrt_rslt_id = becp.prtt_enrt_rslt_id
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between bpf.effective_start_date(+) and bpf.effective_end_date(+)
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between bof.effective_start_date(+) and bof.effective_end_date(+)
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between bptf.effective_start_date and bptf.effective_end_date
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between blf.effective_start_date and blf.effective_end_date   
                                        and :{PARAMETER.P_EFFECTIVE_DATE} between becp.cvg_strt_dt and becp.cvg_thru_dt
                                        and bpil.per_in_ler_stat_cd  in ('STRTD','PROCD')
                                        and bper.person_id = :{PARAMETER.P_PERSON_ID}
                                        and bper.pl_typ_id = :{PARAMETER.P_PLAN_TYPE_ID}
                                        and bper.pl_id = :{PARAMETER.P_PLAN_ID}
                                        and becp.dpnt_person_id > :{PARAMETER.P_LIMITER}
ORDER BY Clause     : becp.dpnt_person_id

Fast Formulas:

ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Medical, Dental and Vision Plans.

/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:  
====================================================================================
Change History:
Name            Date            Version     Comments
Oracle      2025-06-17      1.0         Created.
------------------------------------------------------------------------------------
***********************************************************************************/

/*@section - Default for DBIs*/
default for BEN_OPT_NAME                        is 'XX'
default for BEN_PLN_NAME                        is 'XX'
default for BEN_PTP_NAME                        is 'XX'
default for PER_PER_DATE_OF_BIRTH               is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME     is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME    is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME    is 'XX'

/* @Section - input variables */
/* No Inputs*/

/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL           =   'N'
l_cur_option_name               =   BEN_OPT_NAME
l_cur_plan_name         =   BEN_PLN_NAME
l_plan_type             =   BEN_PTP_NAME
l_plan_enrolled         =   'N'
l_person_id             =   get_context(PERSON_ID,0)
l_plan_type_id          =   get_context(PL_TYP_ID,0)
l_plan_id                               =   get_context(PL_ID,0)
l_effective_date        =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date       =   add_days(l_effective_date, -1)
l_enrolled_option       =   'N'
l_spouse_flag                   =   'N'
CARRY_FORWARD_ELIG_DPNT =   'CFWP'


log = ess_log_write('--------------ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)

/*@section - Business Logic and Interface*/

change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
    l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
    while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
    (
        if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and (BEN_EXT_RSLT_PTP_NAME[l_index] = 'Medical' or BEN_EXT_RSLT_PTP_NAME[l_index] = 'Dental' or BEN_EXT_RSLT_PTP_NAME[l_index] = 'Vision'))then
        (
            l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
            l_plan_enrolled   = BEN_EXT_RSLT_PL_NAME[l_index]
            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
            if l_plan_enrolled = l_cur_plan_name then
            (
                i=1
                limiter = 0
                l_enrolled_data = ' '
                l_enrolled_dependents_count = 0
                l_enrolled_flag = 'N'
                while isnull(l_enrolled_data) = 'Y' loop
                (
                    l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
                    log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
                    if isnull(l_enrolled_data) = 'Y' then
                    (
                        l_enrolled_flag = 'Y'
                        l_enrolled_dpnt_per_id[i] = l_enrolled_data
                        l_enrolled_dependents_count = l_enrolled_dependents_count + 1
                        limiter = to_number(l_enrolled_data)
                        log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
                        i = i + 1                       
                    )
                )
                j = 1
                limiter = 0
                l_eligible_data = ' '
                l_eligible_flag = 'N'
                while isnull(l_eligible_data) = 'Y' loop
                (
                    l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
                    log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
                    if isnull(l_eligible_data) = 'Y' then
                    (   
                        l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
                        l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
                        l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
                        change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
                        (
                            l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
                            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
                            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))

                            if (((l_dpnt_age < 26 or (l_dpnt_age >= 26 and l_dpnt_age < 27 and (to_number(to_char(l_effective_date,'MM')) = to_number(to_char(PER_PER_DATE_OF_BIRTH,'MM'))) and last_day(l_effective_date)!= l_effective_date )) and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
                            (
                                l_eligible_flag = 'Y'
                                l_eligible_dpnt_per_id[j] = l_dpnt_person_id
                                log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                                j = j + 1
                            )
                        )
                        if (l_dpnt_contact_type = 'S' and l_plan_enrolled != 'EE+Child') then
                        (
                            l_spouse_flag = 'Y'
                        )
                        limiter = to_number(l_dpnt_person_id)                    
                    )
                )
                i = 1                
                l_loss_of_eligibility = 'N'
                l_dpnt_loss_count = 0
                if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
                (
                    while( l_enrolled_dpnt_per_id.exists(i)) loop
                    (
                        j = 1
                        while(l_eligible_dpnt_per_id.exists(j)) loop
                        (
                            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                            if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
                            (
                                exit                            
                            )
                            j = j + 1
                            if j > l_eligible_dpnt_per_id.count then
                            (
                                l_loss_of_eligibility = 'Y'
                                l_dpnt_loss_count = l_dpnt_loss_count + 1
                            )
                        )
                        i = i + 1
                    )
                )
                else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
                (
                    l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
                )
                log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
                log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
                log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))

                l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
                if l_cur_dependent_count < 0 then
                (
                    l_cur_dependent_count = 0
                )
                log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_cur_dependent_count ='+to_char(l_cur_dependent_count))

                if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
                (
                    log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name) 
                    if isnull(l_cur_dependent_count) = 'Y' then
                    (
                        log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
                        if  l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                        (
                            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL1'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )                    
                        else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
                        (
                            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )                   
                        else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                        (
                            log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )                    
                        else if l_cur_option_name = 'EE Only' and l_cur_dependent_count = 0 and l_enrolled_flag = 'Y' then
                        (
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'EE Only' and l_enrolled_flag = 'N' then
                        (
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'Waive Coverage' then
                        (
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                    )
                )
                else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' then
                (
                    log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
                    if (l_cur_option_name = 'EE Only' and (l_cur_dependent_count = 0 or l_enrolled_flag = 'N')) then
                    (
                        log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                    (
                        log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
                    (
                        log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                    (
                        log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                )
            )
        )
    l_index = l_index + 1
    )
)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF Formula Ends--------------')

/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT

ORA_BEN_HSA_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for HSA Plan.

/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_BEN_HSA_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:  
====================================================================================
Change History:
Name            Date            Version     Comments
Oracle      2025-06-17      1.0         Created.
------------------------------------------------------------------------------------
***********************************************************************************/

/*@section - Default for DBIs*/     
default for BEN_OPT_NAME                          is 'XX' 
default for BEN_PLN_NAME                            is 'XX'
default for BEN_PTP_NAME                            is 'XX'
default for PER_PER_DATE_OF_BIRTH                   is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME         is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME        is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME        is 'XX'

/* @Section - input variables */
/* No Inputs*/

/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL           =   'N'
l_cur_option_name               =   BEN_OPT_NAME
l_cur_plan_name         =   BEN_PLN_NAME
l_plan_type             =   BEN_PTP_NAME
l_plan_enrolled         =   'N'
l_person_id             =   get_context(PERSON_ID,0)
l_effective_date        =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date       =   add_days(l_effective_date, -1)
l_enrolled_option       =   'N'
l_spouse_flag                   =   'N'
CARRY_FORWARD_ELIG_DPNT =   'CFWP'

log = ess_log_write('--------------ORA_BEN_HSA_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id)) 
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)

/*@section - Business Logic and Interface*/

change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
    a_index = BEN_EXT_RSLT_PL_NAME.first(-1)
    l_plan_counter = 0
    while(BEN_EXT_RSLT_PL_NAME.exists(a_index))loop
    (
        if (BEN_EXT_RSLT_PL_NAME[a_index] = 'UHC Consumer Driven Health Plan (CDHP)') then
        (
            l_plan_counter = 1
            exit
        )
        a_index = a_index + 1
    )
    if (l_plan_counter = 1) then
    (
        l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
        while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
        (
            l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
            l_plan_enrolled   = BEN_EXT_RSLT_PL_NAME[l_index]
            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
            if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'FSA / HSA' and l_plan_enrolled = 'HSA')then
            (
                j = 1
                limiter = 0
                l_eligible_data = ' '
                l_eligible_flag = 'N'
                while isnull(l_eligible_data) = 'Y' loop
                (
                    l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_LIMITER='''||to_char(limiter)||'''')
                    log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
                    if isnull(l_eligible_data) = 'Y' then
                    (   
                        l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
                        l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
                        l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
                        change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
                        (
                            l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
                            if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
                            (
                                l_eligible_flag = 'Y'
                                l_eligible_dpnt_per_id[j] = l_dpnt_person_id
                                log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                                j = j + 1
                            )
                        )
                        if (l_dpnt_contact_type = 'S') then
                        (
                            l_spouse_flag = 'Y'
                        )
                        limiter = to_number(l_dpnt_person_id)                    
                  )
                )
                i = 1
                l_elig_dependent_count = 0
                if l_eligible_flag = 'Y' then
                (
                    while( l_eligible_dpnt_per_id.exists(i)) loop
                    (
                        l_elig_dependent_count = l_eligible_dpnt_per_id.count
                        i = i + 1
                    )
                )
                log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_elig_dependent_count ='+to_char(l_elig_dependent_count))

                if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
                (
                    log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name) 
                    if isnull(l_elig_dependent_count) = 'Y' then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
                        if l_cur_option_name = 'Family Only Coverage' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y' then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL1'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )                    
                        else if l_cur_option_name = 'EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N' then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y' then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'Self-Only Coverage' and l_elig_dependent_count = 0 then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'Per Pay Period Amount - EE+Family' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y' then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'Per Pay Period Amount - EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N' then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'Per Pay Period Amount - EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y' then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL7'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'Per Pay Period Amount - Self Only' and l_elig_dependent_count = 0 then
                        (
                            log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL8'+AUTO_DFLT_VAL )
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                        else if l_cur_option_name = 'Waive Coverage' then
                        (
                            AUTO_DFLT_VAL = 'Y'
                            exit
                        )
                    )
                )
                else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' and l_enrolled_option not like 'Per Pay Period Amount%' then
                (
                    log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
                    if (l_cur_option_name =  'Self-Only Coverage' and l_elig_dependent_count = 0) then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL10'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if (l_cur_option_name =  'EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y') then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL11'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if (l_cur_option_name =  'EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N') then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL12'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if (l_cur_option_name =  'Family Only Coverage' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y') then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL12'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                )
                else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' and l_enrolled_option like 'Per Pay Period Amount%' then
                (
                    if (l_cur_option_name =  'Per Pay Period Amount - Self Only' and l_elig_dependent_count = 0) then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL13'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if (l_cur_option_name =  'Per Pay Period Amount - EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y') then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL14'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if (l_cur_option_name =  'Per Pay Period Amount - EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N') then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL15'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if (l_cur_option_name =  'Per Pay Period Amount - EE+Family' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y') then
                    (
                        log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL15'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                )       
            )
            l_index = l_index + 1
        )
    )
)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_BEN_HSA_PLN_DFLT_ENRT_FF Formula Ends--------------')

/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT

ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Voluntary Life Plan.

/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION: 
====================================================================================
Change History:
Name            Date            Version     Comments
Oracle      2025-06-17      1.0         Created.
------------------------------------------------------------------------------------
***********************************************************************************/

/*@section - Default for DBIs*/  
default for BEN_OPT_NAME                        is 'XX'   
default for BEN_PLN_NAME                        is 'XX'
default for BEN_PTP_NAME                        is 'XX'
default for PER_PER_DATE_OF_BIRTH               is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME     is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME    is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME    is 'XX'

/* @Section - input variables */
/* No Inputs*/

/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL           =   'N'
l_cur_option_name               =   BEN_OPT_NAME
l_cur_plan_name         =   BEN_PLN_NAME
l_plan_type             =   BEN_PTP_NAME
l_plan_enrolled         =   'N'
l_person_id             =   get_context(PERSON_ID,0)
l_plan_type_id          =   get_context(PL_TYP_ID,0)
l_plan_id               =   get_context(PL_ID,0)
l_effective_date        =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date       =   add_days(l_effective_date, -1)
l_enrolled_option       =   'N'
l_spouse_flag                   =   'N'
CARRY_FORWARD_ELIG_DPNT =   'CFWP'

log = ess_log_write('--------------ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)

/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
  l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
  while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
  (
        l_plan_enrolled = BEN_EXT_RSLT_PL_NAME[l_index]
        l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
        log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
        log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
        if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'Optional Life' and l_plan_enrolled = 'Voluntary Life - Dependent Children')then
        (
            i=1
            limiter = 0
            l_enrolled_data = ' '
            l_enrolled_dependents_count = 0
            l_enrolled_flag = 'N'
            while isnull(l_enrolled_data) = 'Y' loop
            (
                l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
              log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
                if isnull(l_enrolled_data) = 'Y' then
                (
                    l_enrolled_flag = 'Y'
                    l_enrolled_dpnt_per_id[i] = l_enrolled_data
                    l_enrolled_dependents_count = l_enrolled_dependents_count + 1
                    limiter = to_number(l_enrolled_data)
                    log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
                    i = i + 1                       
                )                    
            )
            j = 1
            limiter = 0
            l_eligible_data = ' '
            l_eligible_flag = 'N'
            while isnull(l_eligible_data) = 'Y' loop
            (
                l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
                log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
                if isnull(l_eligible_data) = 'Y' then
                (   
                    l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
                    l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
                    l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
                    change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
                    (
                        l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
                        log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
                        log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
                        if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
                        (
                            l_eligible_flag = 'Y'
                            l_eligible_dpnt_per_id[j] = l_dpnt_person_id
                            log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                            j = j + 1
                        )
                    )   
                    if l_dpnt_contact_type = 'S' then
                    (
                        l_spouse_flag           = 'Y'
                        l_spouse_dep_id     = l_dpnt_person_id
                    )
                    limiter = to_number(l_dpnt_person_id)                    
              )
            )
            i = 1                
            l_loss_of_eligibility = 'N'
            l_dpnt_loss_count = 0
            if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
            (
                while( l_enrolled_dpnt_per_id.exists(i)) loop
                (
                    j = 1
                    while(l_eligible_dpnt_per_id.exists(j)) loop
                    (
                        if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
                        (
                            if l_enrolled_dpnt_per_id[i] = l_spouse_dep_id then
                            (
                                l_dpnt_loss_count = l_dpnt_loss_count + 1
                            )
                            else
                            (
                                exit
                            )
                        )
                        j = j + 1
                        if j > l_eligible_dpnt_per_id.count then
                        (
                            l_loss_of_eligibility = 'Y'
                            l_dpnt_loss_count = l_dpnt_loss_count + 1
                        )
                    )
                    i = i + 1
                )
            )
            else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
            (
                l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
            )
            log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
            log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
            log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))
                          */
            l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
            if l_cur_dependent_count < 0 then
            (
                l_cur_dependent_count = 0
            )

            if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
            (
                log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name)
                if isnull(l_cur_dependent_count) = 'Y' then
                (
                    log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
                    if (l_cur_option_name = 'Elect' and l_cur_dependent_count >= 1) then
                    (
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if l_cur_option_name = 'Waive Coverage' then
                    (
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                )
            )
            else if l_plan_enrolled = l_cur_plan_name then
            (
                log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
                if (l_cur_option_name = 'Elect' and l_cur_dependent_count >= 1) then
                (
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
                else if (l_cur_option_name = 'Waive Coverage' and l_cur_dependent_count = 0) then
                (
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
            )       
        )
      l_index = l_index + 1
  )
)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF Formula Ends--------------')

/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT

ORA_CRT_ILL_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Critical Illness Plan.

/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_CRT_ILL_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:  
====================================================================================
Change History:
Name            Date            Version     Comments
Oracle      2025-06-17      1.0         Created.
------------------------------------------------------------------------------------
***********************************************************************************/

/*@section - Default for DBIs*/
default for BEN_OPT_NAME                        is 'XX'
default for BEN_PLN_NAME                        is 'XX'
default for BEN_PTP_NAME                        is 'XX'
default for PER_PER_DATE_OF_BIRTH               is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME     is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME    is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME    is 'XX'

/* @Section - input variables */
/* No Inputs*/

/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL           =   'N'
l_cur_option_name               =   BEN_OPT_NAME
l_cur_plan_name         =   BEN_PLN_NAME
l_plan_type             =   BEN_PTP_NAME
l_plan_enrolled         =   'N'
l_person_id             =   get_context(PERSON_ID,0)
l_plan_type_id          =   get_context(PL_TYP_ID,0)
l_plan_id               =   get_context(PL_ID,0)
l_effective_date        =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date       =   add_days(l_effective_date, -1)
l_enrolled_option       =   'N'
l_spouse_flag                   =   'N'
CARRY_FORWARD_ELIG_DPNT =   'CFWP'

log = ess_log_write('--------------ORA_CRT_ILL_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)

/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
    l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
    while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
    (
        l_plan_enrolled = BEN_EXT_RSLT_PL_NAME[l_index]
        l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
        log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
        log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
        if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'Misc Plans' and l_plan_enrolled = 'Critical Illness')then
        (
            i=1
            limiter = 0
            l_enrolled_data = ' '
            l_enrolled_dependents_count = 0
            l_enrolled_flag = 'N'
            while isnull(l_enrolled_data) = 'Y' loop
            (
                l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
                log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
                if isnull(l_enrolled_data) = 'Y' then
                (
                    l_enrolled_flag = 'Y'
                    l_enrolled_dpnt_per_id[i] = l_enrolled_data
                    l_enrolled_dependents_count = l_enrolled_dependents_count + 1
                    limiter = to_number(l_enrolled_data)
                    log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
                    i = i + 1                       
                )
            )
            j = 1
            limiter = 0
            l_eligible_data = ' '
            l_eligible_flag = 'N'
            while isnull(l_eligible_data) = 'Y' loop
            (
                l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
                log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
                if isnull(l_eligible_data) = 'Y' then
                (   
                    l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
                    l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
                    l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
                    change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
                    (
                        l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
                        log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
                        log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
                        if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
                        (
                            l_eligible_flag = 'Y'
                            l_eligible_dpnt_per_id[j] = l_dpnt_person_id
                            log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                            j = j + 1
                        )
                    )   
                    if (l_dpnt_contact_type = 'S' and l_plan_enrolled != 'EE+Child') then
                    (
                        l_spouse_flag = 'Y'
                    )
                    limiter = to_number(l_dpnt_person_id)                    
                )
            )
            i = 1                
            l_loss_of_eligibility = 'N'
            l_dpnt_loss_count = 0
            if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
            (
                while( l_enrolled_dpnt_per_id.exists(i)) loop
                (
                    j = 1
                    while(l_eligible_dpnt_per_id.exists(j)) loop
                    (
                        log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                        if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
                        (
                            exit                            
                        )
                        j = j + 1
                        if j > l_eligible_dpnt_per_id.count then
                        (
                            l_loss_of_eligibility = 'Y'
                            l_dpnt_loss_count = l_dpnt_loss_count + 1
                        )
                    )
                    i = i + 1
                )
            )
            else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
            (
                l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
            )
            log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
            log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
            log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))

            l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
            if l_cur_dependent_count < 0 then
            (
                l_cur_dependent_count = 0
            )
            log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_cur_dependent_count ='+to_char(l_cur_dependent_count))

            if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
            (
                log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name) 
                if isnull(l_cur_dependent_count) = 'Y' then
                (
                    log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
                    if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
                    (
                        log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )                   
                    else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                    (
                        log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )                    
                    else if l_cur_option_name = 'EE Only' and l_cur_dependent_count = 0 and l_enrolled_flag = 'Y' then
                    (
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if l_cur_option_name = 'EE Only' and l_enrolled_flag = 'N' then
                    (
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if l_cur_option_name = 'Waive Coverage' then
                    (
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                )
            )
            else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' then
            (
                log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
                if (l_cur_option_name = 'EE Only' and (l_cur_dependent_count = 0 or l_enrolled_flag = 'N')) then
                (
                    log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
                else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                (
                    log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
                else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
                (
                    log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
            )
        )
    l_index = l_index + 1
    )
)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_CRT_ILL_PLN_DFLT_ENRT_FF Formula Ends--------------')

/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT

ORA_ACC_INS_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Accidental Insurance Plan.

/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_ACC_INS_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:  
====================================================================================
Change History:
Name            Date            Version     Comments
Oracle      2025-06-17      1.0         Created.
------------------------------------------------------------------------------------
***********************************************************************************/

/*@section - Default for DBIs*/
default for BEN_OPT_NAME                        is 'XX'
default for BEN_PLN_NAME                        is 'XX'
default for BEN_PTP_NAME                        is 'XX'
default for PER_PER_DATE_OF_BIRTH               is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME     is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME    is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME    is 'XX'

/* @Section - input variables */
/* No Inputs*/

/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL           =   'N'
l_cur_option_name               =   BEN_OPT_NAME
l_cur_plan_name         =   BEN_PLN_NAME
l_plan_type             =   BEN_PTP_NAME
l_plan_enrolled         =   'N'
l_person_id             =   get_context(PERSON_ID,0)
l_plan_type_id          =   get_context(PL_TYP_ID,0)
l_plan_id               =   get_context(PL_ID,0)
l_effective_date        =   get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date       =   add_days(l_effective_date, -1)
l_enrolled_option       =   'N'
l_spouse_flag                   =   'N'
CARRY_FORWARD_ELIG_DPNT =   'CFWP'

log = ess_log_write('--------------ORA_ACC_INS_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)

/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
    l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
    while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
    (
        l_plan_enrolled   = BEN_EXT_RSLT_PL_NAME[l_index]
        l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
        if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'Misc Plans' and l_plan_enrolled = 'Accident Insurance')then
        (
            i=1
            limiter = 0
            l_enrolled_data = ' '
            l_enrolled_dependents_count = 0
            l_enrolled_flag = 'N'
            while isnull(l_enrolled_data) = 'Y' loop
            (
                l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
                log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
                if isnull(l_enrolled_data) = 'Y' then
                (
                    l_enrolled_flag = 'Y'
                    l_enrolled_dpnt_per_id[i] = l_enrolled_data
                    l_enrolled_dependents_count = l_enrolled_dependents_count + 1
                    limiter = to_number(l_enrolled_data)
                    log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
                    i = i + 1                       
                )
            )
            j = 1
            limiter = 0
            l_eligible_data = ' '
            l_eligible_flag = 'N'
            while isnull(l_eligible_data) = 'Y' loop
            (
                l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
                log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
                if isnull(l_eligible_data) = 'Y' then
                (   
                    l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
                    l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
                    l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
                    change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
                    (
                        l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
                        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
                        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
                        if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
                        (
                            l_eligible_flag = 'Y'
                            l_eligible_dpnt_per_id[j] = l_dpnt_person_id
                            log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                            j = j + 1
                        )
                    )                    
                    if (l_dpnt_contact_type = 'S' and l_plan_enrolled != 'EE+Child') then
                    (
                        l_spouse_flag = 'Y'
                    )
                    limiter = to_number(l_dpnt_person_id)                    
                )
            )
            i = 1                
            l_loss_of_eligibility = 'N'
            l_dpnt_loss_count = 0
            if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
            (
                while( l_enrolled_dpnt_per_id.exists(i)) loop
                (
                    j = 1
                    while(l_eligible_dpnt_per_id.exists(j)) loop
                    (
                        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
                        if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
                        (
                            exit                            
                        )
                        j = j + 1
                        if j > l_eligible_dpnt_per_id.count then
                        (
                            l_loss_of_eligibility = 'Y'
                            l_dpnt_loss_count = l_dpnt_loss_count + 1
                        )
                    )
                    i = i + 1
                )
            )
            else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
            (
                l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
            )
            log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
            log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
            log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))

            l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
            if l_cur_dependent_count < 0 then
            (
                l_cur_dependent_count = 0
            )
            log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_cur_dependent_count ='+to_char(l_cur_dependent_count))

            if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
            (
                log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name) 
                if isnull(l_cur_dependent_count) = 'Y' then
                (
                    log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
                    if  l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                    (
                        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL1'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )                    
                    else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
                    (
                        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )                   
                    else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                    (
                        log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )                    
                    else if l_cur_option_name = 'EE Only' and l_cur_dependent_count = 0 and l_enrolled_flag = 'Y' then
                    (
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                    else if l_cur_option_name = 'EE Only' and l_enrolled_flag = 'N' then
                    (
                        AUTO_DFLT_VAL = 'Y' 
                        exit
                    )
                    else if l_cur_option_name = 'Waive Coverage' then
                    (
                        AUTO_DFLT_VAL = 'Y'
                        exit
                    )
                )
            )
            else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' then
            (
                log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
                if (l_cur_option_name = 'EE Only' and (l_cur_dependent_count = 0 or l_enrolled_flag = 'N')) then
                (
                    log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
                else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                (
                    log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
                else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
                (
                    log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
                else if l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
                (
                    log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
                    AUTO_DFLT_VAL = 'Y'
                    exit
                )
            )
        )
    l_index = l_index + 1
    )
)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_ACC_INS_PLN_DFLT_ENRT_FF Formula Ends--------------')

/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT

Configuration:

Navigation for Plan Setup:
Benefits Administration > Plan Configuration > Programs > Search > Select Program Name > Enrollment > Select Plan Type > Life Events > Select Life Event (Loss of Eligibility)

Configure the Loss of Eligibility life event at the Program level. Ensure that the session effective date is set to 1/1/1951. Navigate to the Enrollment train stop, select the Program Standard line underneath the program hierarchy, and then Add Life Event: Loss of Eligibility.
Configure the following fields for the loss of eligibility life event on the program level and save it.

Next, configure the Enrollment Period for Plan – this is the list of all plans in which the coverage for the dependent needs to end on the day they turn 26, rather than ending coverage at the end of the month. Underneath the section you just added the loss of eligibility life event on the program, please add the following configuration.

Next, navigate to the Benefit Plan: Benefits Administration > Plan Configuration > Plans > Effective As-Of Date [1/1/1951] > Search > Select Plan Name > Enrollment Train Stop > Complete configuration in the screenshot below for the Waive Coverage option.

Similarly, do the same configuration for rest of the options in the plan and repeat the same steps for different plans.

Scheduling the Reevaluate Designee Eligibility process:

Login as the account that owns all scheduled process – this should not be a personal account.
Navigator > Tools > Scheduled Processes > Schedule New Process > Type “Reevaluate Designee Eligibility” > select continue
Parameters tab: Ensure the Loss of Eligibility Tracking Life Event field is “Remain in started status”.
Optional: Set Program Name so that it’s only checking benefits eligible employees.
Select Advanced at the top
Select the Schedule tab
Run: Using a schedule
Frequency: Daily, set your start & end times
Click Submit.

Testing:

Change dependents DOB to make them 26 as of the date you want to test.
Run Reevaluate Designee Eligibility Process with the following parameters:
Effective Date: Dependents 26th Birthday
Validate: Save
Person Name
Loss of Eligibility Tracking Life Event: Remain in Started Status

Click Search until all 3 processes have completed successfully. Review Log for Evaluate Life Event Participation Process.

After the process is completed, you can review the changes by navigating to Benefits Administration-> Enrollment -> search for an Employee -> select effective date.
Change your effective date to the day before the life event to see enrollments prior to loss of eligibility. Change your date to the first of the month following the life event to review enrollments after life event processed. Validate that enrollments are correct as of the first of the next month.

Bypassing Layout Compiler Errors in Fixed-Width and Delimiter-Based eText Banking Templates

Chinnamsetti Satyanarayana Swamy — Fri, 17 Jul 2026 12:35:38 +0000

Introduction:

In Oracle Fusion HCM and ERP implementations, outbound third-party integrations frequently demand flat-file text formats rather than standard visual layouts like PDF or Excel. The most prominent example is generating Electronic Funds Transfer (EFT) payment files for global financial institutions.

Banks enforce rigid formatting structures—such as specific record lengths, padding characters, and delimiter rules—to process direct deposits safely. Within the Oracle ecosystem, the most efficient way to generate these files is by building BI Publisher eText Templates. This technical guide explains how to design, construct, and validate an upgrade-safe, delimiter-based eText structure directly within Microsoft Word.

eText Layout:

Unlike standard Rich Text Format (RTF) templates that focus on visual design elements, an eText template acts as a data translator. It tells the BI Publisher layout engine exactly how to map raw XML data structures into flat ASCII text configurations.

An enterprise eText template consists of two major operational tables.

The Setup Table (Command Block): Defines global parameters like the output character set, dynamic delimiters (e.g., commas or tabs), and record end characters (e.g., Carriage Return/Line Feed).
The Record Layout Table (Data Block): Defines the exact sequence, maximum length, and alignment padding rules for individual database attributes mapped from the underlying XML payload.

Template Implementation:

Command Setup Block:
To get started, open Microsoft Word and create a single two-column table. This block instructs the layout compiler on how to format the macro structure of the final text document.

Data Record Block:
Next, define how your employee bank records will print. Create a separate five-column table in Word. This table maps directly to your data model group element (for example, /DATA_DS/G_1).

The table requires a specific header structure to pass the engine's translation process.

Key Functions for Advanced Banking Logic:

When dealing with production payment configurations, raw data fields often require transformation rules before hitting the text interface. Here are three critical built-in eText functions to maximize formatting control.

TRUNCATE Logic: Banks will reject lines if an employee's name exceeds field parameters. Setting a maximum length in column two automatically caps string extractions safely.

Number Formatting (9999.99): Financial networks frequently mandate that decimal points be completely removed from numeric values (e.g., $1,250.45 must print as 000000125045). Define an explicit format mask of 99999D99 with a custom decimal setting to handle currency conversions smoothly.

Conditional Displays: Use DISPLAY_CONDITION lines inside command blocks to skip rendering payment rows if bank routing fields are missing, preventing runtime file corruption.

Testing and Validation:

Once your Word document layout is complete, save the file as a Rich Text Format (.rtf) document. Upload it to your target BI Publisher report interface and apply the eText template type attribute.

While performing testing, please do the below additional checks to avoid errors.

Check for Hidden Truncations: Open the generated .txt file in an editor like Notepad++ and turn on hidden characters. Ensure that total record lengths match the bank's schema requirements exactly.
Validate the XML Tag Casing: BI Publisher is strictly case-sensitive. If your data model SQL prints person_number in lowercase, but your eText table declares PERSON_NUMBER, the compiler will output a blank string without throwing an explicit syntax error.

Conclusion:

Mastering custom eText templates allows technical teams to build dependable, secure, and upgrade-safe bank integrations by embedding logic directly within BI Publisher, eliminating the need for external, complex parsing scripts. This approach ensures compliant, precise, and lightweight file formatting while maintaining 100% standard functionality that survives system updates.

A Step-by-Step Guide to Conditional SFTP Bursting in Oracle Cloud BI Publisher

Chinnamsetti Satyanarayana Swamy — Fri, 17 Jul 2026 10:24:44 +0000

Introduction:

Oracle Fusion BI Publisher (BIP) is the primary workhorse for enterprise data extraction and outbound document delivery. While the standard bursting engine handles basic distribution well such as emailing a payslip to a specific worker real world integration requirements are often much more demanding.

A frequent challenge arises when an organization needs a single payroll or financial report to execute, split its data rows dynamically by business unit or country, and securely deliver those separate files into completely different third-party SFTP servers or specific, nested directory paths. Hardcoding static endpoints within delivery channels fails to scale.

This technical guide provides an end-to-end walkthrough on how to construct an advanced SQL data model and a dynamic Bursting Control XML script to route high-volume system files across separate secure servers and target sub-folders automatically.

Business Requirement:

Consider a multinational enterprise utilizing Oracle HCM Cloud across different entities(for eg: US, UK, and India). At the end of a processing cycle, a single third-party benefits interface file must be generated.

However, regulatory compliance and infrastructure policies dictate that:

The US data file must be delivered to an external server named US_BENEFITS_SFTP in the folder /us/production/.
The UK data file must be delivered to a separate server named UK_BENEFITS_SFTP in the folder /uk/compliance/.
The system must perform these calculations and routing actions seamlessly during a single scheduled enterprise process run, without spawning multiple reports or requiring manual file splitting by IT administrators.

Solution:

1. Dynamic Bursting SQL Query:
The secret to dynamic routing lies within the DELIVERY_KEY and the specific parameter mappings inside your data model's bursting query. Instead of typing fixed server names, we use conditional SQL CASE statements or DECODE logic to establish distinct connection strings on a row-by-row basis.

Navigate to your Data Model, click on the Bursting tab, and implement an advanced query structured like shown below:

select 
    legal_entity_id as key,
    'BenefitsTemplate' as template,
    'en-US' as template_locale,
    'TEXT' as output_format,
    'SFTP' as delivery_channel,
  case when le_country = 'US' then 'US_BENEFITS_SFTP' when le_country = 'UK' then 'UK_BENEFITS_SFTP' else 'GLOBAL_BENEFITS_SFTP' end as parameter1,
  case when le_country = 'US' then '/us/production/' when le_country = 'UK' then '/uk/compliance/' else '/global/dropzone/' end as parameter2,
  'BEN_INTERFACE_' || le_country || '_' || to_char(sysdate, 'YYYYMMDD_HH24MISS') || '.txt' as parameter3,
  'sftp_oracle_user' as parameter4
from
(
    select 
        haou.legal_entity_id,
        ft.country as le_country
  from 
        per_all_assignments_m paam
    inner join hr_all_organization_units haou on paam.legal_entity_id = haou.organization_id
    inner join fnd_territories ft on haou.location_id = ft.territory_code
  where 
        trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
  group by 
        haou.legal_entity_id, ft.country
)

2. Bursting Control XML:
Once the data model query exposes these dynamic parameters, you must map them explicitly to the BI Publisher engine's delivery definitions. Under the Bursting Control XML block, implement the following clean mapping logic.

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://oracle.com" type="bursting">
   <xapi:request select="/DATA_DS/G_1">
      <xapi:delivery>
         <xapi:ftp server="${PARAMETER1}" remote-directory="${PARAMETER2}" remote-file="${PARAMETER3}" username="${PARAMETER4}">
            <!-- Password token -->
         </xapi:ftp>
      </xapi:delivery>
      <xapi:document output-type="${OUTPUT_FORMAT}" template-type="rtf" repository="generic">
         <xapi:template select="${TEMPLATE}" locale="${TEMPLATE_LOCALE}"/>
      </xapi:document>
   </xapi:request>
</xapi:requestset>

Best Practices & Environment Verifications:

To avoid processing exceptions during delivery, ensure your environment adheres to these three critical deployment rules.

Admin Console Pre-Configuration: The server values returned by PARAMETER1 (e.g., US_BENEFITS_SFTP) must exactly match the name of the SFTP server registered under the Administration -> Delivery -> FTP page in BI Publisher. If a typo exists, the engine throws a generic Delivery failed exception.

Directory Permissions Validation: Ensure that the user account declared in PARAMETER4 has absolute read, write, and execute permissions on the paths generated by PARAMETER2. If the directory path is missing on the remote host, BIP will not auto-create it unless specified by server parameters; it will fail the handshake instead.

Handling Memory Overheads: When processing massive employee structures, avoid passing data variables directly inside the bursting filename query string (PARAMETER3) if they can cause duplicates. Duplicate keys force file locking sequences on the target SFTP, lowering processing performance.

Conclusion:

By separating delivery settings from fixed properties and adding decision-making logic into the SQL data layer, technical consultants can greatly reduce the effort needed to maintain reports. This approach changes a fixed file extraction script into a smart, flexible distribution process that can handle complex global delivery requirements in a single scheduled run.

Visual Builder Studio: Migration Steps and Best Practices

Chinnamsetti Satyanarayana Swamy — Thu, 16 Jul 2026 10:42:25 +0000

In the previous blog (Previous blog link is provided at the end of this blog.), we learned the basics of Visual Builder Studio, including how to create a project and manage repositories. Now that you have a good understanding of these concepts, it's time to look at some practical tips that can help you work more effectively.

In this blog, we'll go through some best practices, useful migration tips, and common things to watch out for when working with Visual Builder Studio.

Migration:

Create a new Branch:

In the left navigator, click Git.
Click the Refs view and then click Branches.
From the Repositories drop-down list, select the repository.
Click + Create Branch.
In the New Branch dialog box, in Name, enter the branch name. From the Base drop-down list, select the main branch as the base branch.
Click Create.

Configure Package:

Deploy Package:

Pipeline:

Things to Consider

Use the same VB Project and extension for all changes in HCM.
Enable Branch Protection on Main branch and optionally other branches.
Ensure that when working in multiple environments, the same Project is used.
Only merge changes to the main branch when they are ready for Production.
Use Share to provide the URL to a deployed version of your changes to reviewers.
No two developers should work on the same page at the same time.
Make sure that the source and target instances are of the same release, with the same standard and one-off patches applied to both environments.
Only use Dev POD for all the VBS changes and migrate your changes from
1. Dev pod to higher environments. 2.Developers should work in separate feature branches to avoid conflict.

Customization of Processes Using Page Properties in Oracle Visual Builder Studio (VB Studio):

Functional administrators use page properties to extend Redwood pages.

Page properties enable extensibility for:

Reordering tabs in Journeys.
Showing or hiding panels in Connections.
Configuring guided journeys.
Expressions can be written in Express mode to conditionally render elements (e.g., displaying person pronouns for specific roles only).

Accessing Page Properties:

Available page properties for a page are accessible via the Page Properties pane in the Designer.
If the pane shows "We didn't find any extendable page properties on this page," it means no page properties have been delivered for that page by HCM yet.

Additional Notes:

Many page properties have already been delivered across various Redwood pages.
These properties provide diverse options to tailor the application.

Common Uses of Page Properties Include:

Adding custom content to a page or region.
Configuring guided journeys.
Enabling nudges.
Controlling page elements such as sorting, tabs, and field display in some cases.

Recommended Project Structure:

Use one project for all HCM changes.
Use one Git repository.
Have one workspace (multiple workspaces are allowed).
Use multiple development/feature branches per developer/page.
Avoid multiple projects to prevent overwriting.
Use local branches for development and commit them in individual development/feature branches (example: cwk_branch, emp_branch)
Use release branches(main) for deployment.

VBS Branching Strategy:

Why do you need a branching strategy

Allows control over what is deployed to Production.
Allows users to create personalizations in isolation from others or in conjunction depending on the requirement.
Allows different sets of personalizations to be developed and deployed at different.

How do you decide what your branching strategy should be
What are your timelines?
• How is you team structured ?
• How streamlined do you need the development environment to be?
• How much complexity can you tolerate ?
NOTE: Managing branches can only be done in Advanced Mode.

Create a Blank Branch (Clean Code Reference)

Acts as a clean template with no custom code.
Serves as a reference point for any new feature branches created.
Ensures developers always branch from a consistent baseline.

Setup Branch Protection for Main branch(Production Release)

This is your release branch.
All code gets merged here through pull/merge requests
Include approval workflows to control what gets into higher environments and production. NOTE: Make the main branch private and setup some approval workflows before merging the code to main branch

Local Development Branch:

The local branch gets autocreated whenever we open the workspace
We do all our changes in the local development branch
Once all the changes are completed, we publish the changes to the working environment and also push the code to feature branch.

Feature Branch (Branch per Page or Developer)

Create a separate Feature branch for each UI page being modified or per Developer based on the feature being developed.
Promotes isolated development and avoids conflicts.

Development Flow:

Developers make changes in local branches.
Once completed and tested, changes are committed to the feature branches.
From feature branches, merge into the main branch after testing and review.

Approval Work Flow Diagram:

(Link to previous Blog: https://dev.to/satyanarayana_swamy/get-started-with-oracle-visual-builder-studio-for-fusion-apps-extensions-4fdd)

Get Started with Oracle Visual Builder Studio for Fusion Apps Extensions

Chinnamsetti Satyanarayana Swamy — Wed, 15 Jul 2026 12:56:02 +0000

Introduction:

With the rapid evolution of the Oracle Redwood User Experience (UX), classic page personalization tools—such as Page Composer, Sandboxes, and Design Studio—are transitioning to a new standard. To modify, extend, or personalize modern Redwood pages in Oracle Fusion Cloud, implementation teams must transition to Oracle Visual Builder Studio (VBS).

VBS gives consultants the exact low-code and visual development framework used by Oracle's internal product engineers to build the core cloud application rows. This blog provides a step-by-step introduction to the fundamentals of VBS, outlining the baseline architecture and guiding you through your very first UI extension layout.

VB Studio (VBS) is included as part of the Oracle SaaS license and built into Oracle Fusion SaaS environments such as HCM or ERP.
VB Studio is the recommended option for extending Fusion user interface.
All new modules in Fusion Apps (the Redwood modules) are built using Visual Builder, and VBS is the tool used for both Oracle’s own development and customer customizations.
Fusion VB Studio comes enabled in both the dev/test instances.

Oracle Cloud – Overview:

Visual Builder is available out of the box for your Fusion environments.
Visual Builder can be used to personalize Redwood pages and can be used to migrate technical objects.
VBS is GIT online.
VBS can be connected to a "Source".

Oracle Cloud – Security Consideration:

There are delivered roles that are required to provide access to VBS

Developer role: ORA_FND_APPLICATION_DEVELOPER_JOB
Admin role: ORA_FND_APPLICATION_ADMINISTRATOR_JOB

You can define who is on the team for a project once you create the project (see more info below).

VBS Life Cycle:

Functional Activities:
Design:

Confirm Redwood changes
Confirm source & target instances
Identity key workflows and conditional rules

Build:

Extend Redwood pages (Required, Hidden, Read Only, Value, Conditional rules, etc)
Publish code to feature branch/source instance (ie: cwk_employee)

Test:

Login to target to validate the changes
Verify redwood page updates (fields, rules and visibility)
Test features and functionality

Technical Activities:
Design:

Configure VBS (Project, workspace, report)
Plan version control strategy and branching model

Build:

Any advanced features (java script/json updates)
Approve changes & deploy to main branch

Test:

Perform regression testing on impacted components
Verify deployment success and error logs

Other Activities:

Build upstream (ie: UAT, PROD) pipeline
Monitor pipeline health and resolve build failures quickly
Manage post-refresh deployment activities

VBS Components:

Project
Workspace
Git
Builds
Environments

Initial Setup – Create Project:

Create a Project as an empty project.
Add the team when creating the project (can be adjusted later).
This must happen at the beginning of the project.

Workspace:

Workspaces are where most of a user’s work occurs.
Workspaces are unique to a user identity.
Workspace lets you operate on a local copy of the Extension source, which is a clone of the copy that's in the central VBS repository.
Changes made are stored in the git repository and ultimately get synced back to the project repository.
Also associates an environment for deploying packaged code.

Initial Setup – Create Repository

Navigate to GIT and select 'Create Repository'.
Create a New Branch per pillars (Recruiting, Core-HR, Benefits).

Enable Branch Protection on the Main branch
Never make direct updates to the Main branch (see recommended branch structure)

Branches

When you open a workspace and make personalization's, the changes are initially stored in a branch, separate from other code.
A branch isolates the changes you're working on, allowing you to develop without affecting the Master code.
Once changes are finalized, they can be merged into the main(production) branch, which holds the Master set of code changes.

Environments:

Use Environments to define the target Oracle Cloud Applications where you want to deploy code changes.
Environments Page

Create and delete environments.
Add/remove instances tied to an environment.
View the health status, account names, and service ID’s for each environment
Review the deployments for extensions.
Use the extension lifecycle button to manage deployed extensions across environments.

Now that we have covered what Visual Builder Studio is, how to set up your project, and how to manage repositories, you are ready for the next step. In my next blog, I will share key best practices, migration tips, and critical factors to watch out for.

Solving Complex Overtime Thresholds: Incorporating Holiday and Leave Hours into OTL Calculations

Chinnamsetti Satyanarayana Swamy — Tue, 14 Jul 2026 09:36:57 +0000

Introduction:

Managing compliance for multi-tiered compensation policies is a major operational challenge for global enterprises. The problem intensifies when managing complex overtime (OT) and compensatory time rules across distinct employee populations—such as Non-Exempt workers, Public Safety Officers, and Exempt teams.

A common pitfall in Oracle Cloud Time and Labor (OTL) implementations occurs when Absence Management transactions (like Holiday, Annual, or Sick Leave) must factor directly into weekly overtime thresholds. Out-of-the-box rule templates often struggle to process multi-tier splits or capture absence hours within active timecard evaluation passes.

This technical guide presents an end-to-end framework. It showcases three production-grade, two-pass Time Calculation Fast Formulas that dynamically include absence hours, reduce statutory limits, and split premium allocations across custom worker groups.

Business Scenario & Policy Matrix:

Core Architectural Challenge:

Standard Fast Formulas evaluate timecards line by line. When an absence record contains no payroll mapping data, standard evaluation loops completely ignore it.

To overcome this constraint, we implement a Two-Pass Scan Strategy:

Pass 1 (Pre-Scan Optimization Loop): Isolates all holiday or module absence hours prior to calculating premiums. It splits these hours across tracking buckets for Week 1 and Week 2, and dynamically shifts down the overtime thresholds (v_net_threshold = pMaxHrs - v_holiday_hrs).
Pass 2 (Sequential Allocation Loop): Iterates through actual worked hours. It contrasts cumulative work totals against the newly shifted thresholds, allowing absence periods to seamlessly push worked hours into premium bands.

Step-by-Step Formula Implementation:

Formula 1: Non-Exempt Workers (Double-Pay Component Splitting)
Demonstrates dynamic threshold manipulation and simultaneously populates parallel output arrays (out_measure_ary_over and out_measure_ary_over_p) to drive multiple payroll elements on a single split entry.

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : Oracle HCM Cloud
--  Formula  Name   : ORA_OTL_NON_EXEMPT_OVERTIME_FF
--  Formula Type    : Time Calculation rule
--  Purpose         : OT Calculation for Non Exempt Employees

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    01-07-2026    Satyanarayana Swamy Chinnamsetti      1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables and DBI Items*/
default for HWM_CTXARY_RECORD_POSITIONS is empty_text_number
default for measure                     is empty_number_number
default for PayrollTimeType             is empty_text_number

/*@section - Input variables */
inputs are HWM_CTXARY_RECORD_POSITIONS,
           measure,
           PayrollTimeType

/*@section - Local variable declarations and initializations*/
ffs_id                      = get_context(HWM_FFS_ID, 0)
rule_id                     = get_context(HWM_RULE_ID, 0)  
ffName                      = 'ORA_OTL_NON_EXEMPT_OVERTIME_FF'
hExecType               = Get_Hdr_Text(rule_id, 'RULE_EXEC_TYPE', 'CREATE')
hCreateYn               = 'N'
pMaxHrs                 = get_rvalue_number(rule_id, 'DEFINED_LIMIT', 40)
wMaAry              = HWM_CTXARY_RECORD_POSITIONS.count
nidx                = 0
wkTotalHrsTc        = 0
v_holiday_hrs_w1    = 0
v_holiday_hrs_w2    = 0
scan_idx                = 0
day_scan_c              = 0
day_process_c       = 0
rLog                        = add_rlog(ffs_id, rule_id, '>>> Enter - ' || ffName)

/*@section - Return Variable initialization*/
out_measure_ary_under   = empty_number_number
out_measure_ary_over    = empty_number_number
out_measure_ary_over_p  = empty_number_number

/*@section - Business Logic and Interface*/
if (upper(hExecType) = 'CREATE') then 
(
    hCreateYn = 'Y'
)
v_net_threshold     = pMaxHrs

while (scan_idx < wMaAry) loop
(
  scan_idx = scan_idx + 1  
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'END_DAY') then
    (
        day_scan_c = day_scan_c + 1
    )
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'DETAIL') then
    (
        if (NOT PayrollTimeType.exists(scan_idx) and measure.exists(scan_idx)) then
        (
            if (day_scan_c < 7) then
            (
                v_holiday_hrs_w1 = v_holiday_hrs_w1 + measure[scan_idx]
            )
            else
            (
                v_holiday_hrs_w2 = v_holiday_hrs_w2 + measure[scan_idx]
            )
        )
    )
)
v_net_threshold = pMaxHrs - v_holiday_hrs_w1

rLog = add_rlog(ffs_id, rule_id, 'W1 Holiday Hrs: ' || to_char(v_holiday_hrs_w1) || ' | W1 Net Threshold: ' || to_char(v_net_threshold))

while (nidx < wMaAry) loop
(     
  nidx = nidx + 1     
  tcRecPosition = HWM_CTXARY_RECORD_POSITIONS[nidx]  
  if (tcRecPosition = 'END_DAY') then
  (
        day_process_c = day_process_c + 1  
        if (day_process_c = 7) then
        (
            wkTotalHrsTc    = 0 
            v_net_threshold = pMaxHrs - v_holiday_hrs_w2

            rLog = add_rlog(ffs_id, rule_id, '--- Switch to Week 2 --- W2 Holiday Hrs: ' || to_char(v_holiday_hrs_w2) || ' | W2 Net Threshold: ' || to_char(v_net_threshold))
    )
  )  
  if (tcRecPosition = 'DETAIL') then
  (
        if (measure.exists(nidx)) then
        (
            tcMeasure = measure[nidx]
            if (PayrollTimeType.exists(nidx)) then
            (
                if (wkTotalHrsTc >= v_net_threshold) then
                (
                    out_measure_ary_under[nidx]  = 0            
                    out_measure_ary_over[nidx]   = tcMeasure
                    out_measure_ary_over_p[nidx] = tcMeasure
                )
                else if ((wkTotalHrsTc + tcMeasure) > v_net_threshold) then
                (
                    v_reg_allowed                            = v_net_threshold - wkTotalHrsTc
                    v_ot_split                                   = tcMeasure - v_reg_allowed
                    out_measure_ary_under[nidx]  = v_reg_allowed
                    out_measure_ary_over[nidx]   = v_ot_split
                    out_measure_ary_over_p[nidx] = v_ot_split
                )
                else
                (
                    out_measure_ary_under[nidx]  = tcMeasure
                )
                wkTotalHrsTc = wkTotalHrsTc + tcMeasure
            )
        )
  )
)  
rLog = add_rlog(ffs_id, rule_id, '<< Exit - ' || ffName)

/*@section - Return the calculated value*/
return out_measure_ary_under, out_measure_ary_over, out_measure_ary_over_p

Formula 2: Public Safety Personnel (In-Line Factor Scaling)
Directly scales the calculated remainder array by a 1.5x factor in the script's calculation loop. It handles the ELSE branch cleanly to preserve and return unmapped holiday records to the payroll engine.

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : Oracle HCM Cloud
--  Formula  Name   : ORA_OTL_PUBLIC_SAFETY_OVERTIME_FF
--  Formula Type    : Time Calculation rule
--  Purpose         : Compensatory Time Earned Calculation with Holiday Threshold Reductions

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    02-07-2026    Satyanarayana Swamy Chinnamsetti      1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables and DBI Items*/
default for HWM_CTXARY_RECORD_POSITIONS is empty_text_number  
default for measure                     is empty_number_number 
default for PayrollTimeType             is empty_text_number 

/*@section - Input variables */
inputs are HWM_CTXARY_RECORD_POSITIONS,
                     measure, 
                     PayrollTimeType

/*@section - Local variable declarations and initializations*/
ffName            = 'ORA_OTL_PUBLIC_SAFETY_OVERTIME_FF'
ffs_id            = GET_CONTEXT(HWM_FFS_ID, 0)
rule_id           = GET_CONTEXT(HWM_RULE_ID, 0)
pMaxHrs           = get_rvalue_number(rule_id, 'DEFINED_LIMIT', 40) 
wMaAry            = HWM_CTXARY_RECORD_POSITIONS.count
scan_idx          = 0
nidx              = 0
day_scan_c        = 0
day_process_c     = 0
v_holiday_hrs_w1  = 0
v_holiday_hrs_w2  = 0
wkTotalHrsTc      = 0   
rLog              = add_rlog(ffs_id, rule_id, '>>> Enter - ' || ffName)

/*@section - Return Variable initialization*/
measure_under = empty_number_number 
measure_over  = empty_number_number

/*@section - Business Logic and Interface*/
while (scan_idx < wMaAry) loop
(
  scan_idx = scan_idx + 1  
  if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'END_DAY') then
  (
    day_scan_c = day_scan_c + 1
  )
  if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'DETAIL') then
  (
    if (not PayrollTimeType.exists(scan_idx) and measure.exists(scan_idx)) then
    (
      if (day_scan_c < 7) then
      (
        v_holiday_hrs_w1 = v_holiday_hrs_w1 + measure[scan_idx]
      )
      else
      (
        v_holiday_hrs_w2 = v_holiday_hrs_w2 + measure[scan_idx]
      )
    )
  )
)
v_net_threshold = pMaxHrs - v_holiday_hrs_w1

rLog = add_rlog(ffs_id, rule_id, 'W1 Holiday Hrs: ' || to_char(v_holiday_hrs_w1) || ' | W1 Net Threshold: ' || to_char(v_net_threshold))

while (nidx < wMaAry) loop
(     
  nidx                  = nidx + 1     
  tcRecPosition = HWM_CTXARY_RECORD_POSITIONS[nidx]  
  if (tcRecPosition = 'END_DAY') then
  (
    day_process_c = day_process_c + 1  
    if (day_process_c = 7) then
    (
      wkTotalHrsTc    = 0 
      v_net_threshold = pMaxHrs - v_holiday_hrs_w2
      rLog = add_rlog(ffs_id, rule_id, '--- Switch to Week 2 --- W2 Holiday Hrs: ' || to_char(v_holiday_hrs_w2) || ' | W2 Net Threshold: ' || to_char(v_net_threshold))
    )
  )
  if (tcRecPosition = 'DETAIL') then
  (
    if (measure.exists(nidx)) then
    (
      tcMeasure = measure[nidx]
      if (PayrollTimeType.exists(nidx)) then
      (
        if (wkTotalHrsTc >= v_net_threshold) then
        (
          measure_under[nidx] = 0            
          measure_over[nidx]  = tcMeasure * 1.5
        )
        else if ((wkTotalHrsTc + tcMeasure) > v_net_threshold) then
        (
          v_reg_allowed             = v_net_threshold - wkTotalHrsTc
          v_comp_split              = tcMeasure - v_reg_allowed
          measure_under[nidx]   = v_reg_allowed
          measure_over[nidx]    = v_comp_split * 1.5
        )
        else
        (
          measure_under[nidx] = tcMeasure
        )
        wkTotalHrsTc = wkTotalHrsTc + tcMeasure
      )
      else
      (
        measure_under[nidx] = tcMeasure
      )
    )
  )
)  
rLog = add_rlog(ffs_id, rule_id, '<< Exit - ' || ffName)

/*@section - Return the calculated value*/ 
return measure_under, measure_over

Formula 3: Exempt Teams (Multi-Tier Comp Time Cascade)
Solves the challenge of progressive, multi-tier allocation cascading. This block processes chunk remnants sequentially across distinct limits: Standard regular time up to 37.5 hours, Tier 1 premium time up to 40.0 hours (1.0x), and Tier 2 premium time past 40.0 hours (1.5x).

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : Oracle HCM Cloud
--  Formula  Name   : ORA_OTL_EXEMPT_OVERTIME_FF
--  Formula Type    : Time Calculation rule
--  Purpose         : 

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    03-07-2026    Satyanarayana Swamy Chinnamsetti      1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables and DBI Items*/
default for HWM_CTXARY_RECORD_POSITIONS is empty_text_number  
default for measure                     is empty_number_number 
default for PayrollTimeType             is empty_text_number 

/*@section - Input variables */
inputs are HWM_CTXARY_RECORD_POSITIONS,
                     measure, 
                     PayrollTimeType 

/*@section - Local variable declarations and initializations*/
ffName            = 'ORA_OTL_EXEMPT_OVERTIME_FF'
ffs_id            = GET_CONTEXT(HWM_FFS_ID, 0) 
rule_id           = GET_CONTEXT(HWM_RULE_ID, 0) 
pMaxHrs           = get_rvalue_number(rule_id, 'DEFINED_LIMIT', 37.5) 
pTier1Gap         = 2.5
wMaAry            = HWM_CTXARY_RECORD_POSITIONS.count
scan_idx          = 0
nidx              = 0
day_scan_c        = 0
day_process_c     = 0
v_holiday_hrs_w1  = 0
v_holiday_hrs_w2  = 0
wkTotalHrsTc      = 0   
rLog              = add_rlog(ffs_id, rule_id, '>>> Enter - ' || ffName)

/*@section - Return Variable initialization*/
measure_under = empty_number_number
measure_over = empty_number_number

/*@section - Business Logic and Interface*/
while (scan_idx < wMaAry) loop
(
    scan_idx = scan_idx + 1  
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'END_DAY') then
    (
        day_scan_c = day_scan_c + 1
    )
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'DETAIL') then
    (
        if (not PayrollTimeType.exists(scan_idx) and measure.exists(scan_idx)) then
        (
            if (day_scan_c < 7) then
            (
                v_holiday_hrs_w1 = v_holiday_hrs_w1 + measure[scan_idx]
            )
            else
            (
                v_holiday_hrs_w2 = v_holiday_hrs_w2 + measure[scan_idx]
            )
        )
    )
)
v_net_threshold1 = pMaxHrs - v_holiday_hrs_w1
v_net_threshold2 = v_net_threshold1 + pTier1Gap

rLog = add_rlog(ffs_id, rule_id, 'W1 Regular Limit: ' || to_char(v_net_threshold1) || ' | W1 Tier 1 Limit: ' || to_char(v_net_threshold2))

while (nidx < wMaAry) LOOP
(     
    nidx                    = nidx + 1     
    tcRecPosition = HWM_CTXARY_RECORD_POSITIONS[nidx]  
    if (tcRecPosition = 'END_DAY') then
    (
        day_process_c = day_process_c + 1  
        if (day_process_c = 7) then
        (
            wkTotalHrsTc     = 0 
            v_net_threshold1 = pMaxHrs - v_holiday_hrs_w2
            v_net_threshold2 = v_net_threshold1 + pTier1Gap
            rLog = add_rlog(ffs_id, rule_id, '--- Switch to Week 2 --- W2 Regular Limit: ' || to_char(v_net_threshold1) || ' | W2 Tier 1 Limit: ' || to_char(v_net_threshold2))
        )
    )
    if (tcRecPosition = 'DETAIL') then
    (
        if (measure.exists(nidx)) then
        (
            tcMeasure = measure[nidx]
            if (PayrollTimeType.exists(nidx)) then
            (
                v_rem         = tcMeasure
                v_reg_chunk   = 0
                v_tier1_chunk = 0
                v_tier2_chunk = 0
                if (wkTotalHrsTc < v_net_threshold1) then
                (
                    v_avail1 = v_net_threshold1 - wkTotalHrsTc
                    if (v_rem <= v_avail1) then
                    (
                        v_reg_chunk  = v_rem
                        wkTotalHrsTc = wkTotalHrsTc + v_rem
                        v_rem        = 0
                    )
                    else
                    (
                        v_reg_chunk  = v_avail1
                        wkTotalHrsTc = wkTotalHrsTc + v_avail1
                        v_rem        = v_rem - v_avail1
                    )
                )
                if (v_rem > 0 and wkTotalHrsTc >= v_net_threshold1 and wkTotalHrsTc < v_net_threshold2) then
                (
                    v_avail2 = v_net_threshold2 - wkTotalHrsTc
                    if (v_rem <= v_avail2) then
                    (
                        v_tier1_chunk = v_rem
                        wkTotalHrsTc  = wkTotalHrsTc + v_rem
                        v_rem         = 0
                    )
                    else
                    (
                        v_tier1_chunk = v_avail2
                        wkTotalHrsTc  = wkTotalHrsTc + v_avail2
                        v_rem         = v_rem - v_avail2
                    )
                )
                if (v_rem > 0 and wkTotalHrsTc >= v_net_threshold2) then
                (
                    v_tier2_chunk = v_rem
                    wkTotalHrsTc  = wkTotalHrsTc + v_rem
                    v_rem         = 0
                )
                measure_under[nidx] = v_reg_chunk
                if ((v_tier1_chunk + v_tier2_chunk) > 0) then
                (
                    measure_over[nidx] = (v_tier1_chunk * 1.0) + (v_tier2_chunk * 1.5)
                )
            )
            else
            (
                measure_under[nidx] = tcMeasure
            )
        )
    )
)  
rLog = add_rlog(ffs_id, rule_id, '<< Exit - ' || ffName)

/*@section - Return the calculated value*/ 
return measure_under, measure_over

Key Functional Mechanics Explanations

1. Dynamic Threshold Shifting
Hardcoding 37.5 or 40-hour limits inside a calculation rule forces the engine to ignore overlapping historical balances. By executing an isolated pre-scan loop across data indexes (HWM_CTXARY_RECORD_POSITIONS), the logic shifts thresholds dynamically before distributing hours into worked categories. This approach includes absences in threshold metrics without altering the original absence records.

2. Remainder Cascading Logic
The cascading architecture implemented in Formula 3 uses an isolated container tracker (v_rem) to break down individual time entries. When an entry spans multiple limit zones, the logic carves out and counts chunks against available room profiles sequentially. This guarantees down-to-the-minute allocation accuracy for complex multi-tier plans.

3. Simultaneous Component Routing
To minimize configuration overhead, Formula 1 uses a multi-array mapping strategy. Instead of running separate rules to generate basic premium additions, the calculation engine returns out_measure_ary_over and out_measure_ary_over_p concurrently. This approach automatically routes double-pay balances to separate payroll components during the evaluation run.

Conclusion

Integrating timecard calculations across OTL, Absence Management, and Global Payroll requires a deliberate, memory-conscious approach to rule design. Using a two-pass scan architecture allows your team to enforce multi-tiered compliance rules, account for cross-module absence variances, and maintain stable processing performance across large user groups.

Loading Global User-Defined Tables (UDT) Without an LDG using HCM Data Loader

Chinnamsetti Satyanarayana Swamy — Fri, 03 Jul 2026 15:28:30 +0000

Before we get into loading a User-Defined Table (UDT) without a Legislative Data Group (LDG) using HCM Data Loader, let's first understand what a UDT is and why it's an important feature in Oracle HCM.

What is a User-Defined Table (UDT)?
A User-Defined Table (UDT) in Oracle Fusion HCM is a flexible way to store business-specific data that can be accessed whenever it's needed. You can think of it as a spreadsheet built into Oracle HCM, where you define your own lookup values instead of hardcoding them into Fast Formulas or integrations.

Rather than embedding values directly in your logic, you store them in a UDT and retrieve them whenever required. This makes your solutions easier to maintain and update over time.

A User-Defined Table is made up of three main components:

Columns: These represent the different types of information you want to store.
Rows: These act as the lookup keys or criteria used to identify a specific record.
Column Instances: These are the actual values stored where a row and a column intersect.

Why Use User-Defined Tables?
If you've ever had to update hardcoded values in a Fast Formula, you already know how time-consuming and error-prone it can be. User-Defined Tables solve this problem by keeping your business data separate from your application logic.

Some of the key benefits include:

Simpler maintenance: Business values can be updated without modifying the Fast Formula itself.
Dynamic lookups: Functions such as GET_TABLE_VALUE allow you to retrieve values from the table based on the lookup key you provide.
Greater flexibility: UDTs can store text, numbers, ranges, and other business-specific values, making them suitable for a wide variety of use cases.

By using UDTs, you can build solutions that are easier to manage, more scalable, and adaptable to changing business requirements.

Why use HCM Data Loader to create the User-Defined Table?
Creating a simple User-Defined Table (UDT) with just a few rows and a single column is easy through the Oracle Fusion user interface. The process is straightforward, for a local table, select the appropriate Legislative Data Group (LDG) from the dropdown list. If you are creating a global table, simply leave the LDG field empty.

While this approach works well for small datasets, it becomes impractical in real-world business scenarios. Organizations often maintain hundreds or even thousands of records for items such as payroll mappings, regional allowances, or other reference data. Entering such large datasets manually through the application is both time-consuming and prone to errors.

This is where HCM Data Loader (HDL) becomes the preferred solution. HDL enables you to package large volumes of reference data into a single data file and import everything in one operation, making bulk data loading both efficient and reliable. However, moving from the user interface to HDL introduces an important challenge, particularly when working with global tables where the Legislative Data Group (LDG) field is intentionally left blank.

To begin, we will create a standard User-Defined Table (UDT) with a Legislative Data Group (LDG) using HCM Data Loader (HDL). For this, we will use the UserDefinedTable.dat business object.

Below are the Metadata and Merge lines for a sample UDT.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName
MERGE|UserDefinedTable|ORA_US_RELOCATION_ALLOWANCE_UDT|M|T|ORA_US_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableCode|LegislativeDataGroupName|RowName|RowLowRangeOrName
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group|GRADE_1|GRADE_1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group|GRADE_2|GRADE_2
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|UserTableCode|RowLowRangeOrName
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|IN Legislative Data Group|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|GRADE_1
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|IN Legislative Data Group|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|GRADE_2

Once the data loader completes successfully, we can navigate to the User-Defined Tables section to verify that the table structure, column parameters, and row entries were correctly generated within our Legislative Data Group.

Now, let's try to create a global UDT by using the exact same METADATA and MERGE line structures, but with one change, we will remove the Legislative Data Group references entirely from the data file.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName
MERGE|UserDefinedTable|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|M|T|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableCode|RowName|RowLowRangeOrName
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_1|GRADE_1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_2|GRADE_2
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|UserColumnCode|UserTableCode|RowLowRangeOrName
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_1
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_2

While you are trying to load the dat file, you will encounter the below issue.

To resolve this parent-child relationship issue, the deployment process must be divided into two sequential load stages.

First Load: Create a UserDefinedTable.dat file by including the UserDefinedTable, UserDefinedTableColumn, and UserDefinedTableRow METADATA and MERGE records together in a single HDL file. This load establishes the table structure without creating any UserDefinedTableColumnInstance records.

Second Load: After the first load completes successfully, create a separate UserDefinedTable.dat file containing only the UserDefinedTableColumnInstance METADATA and MERGE records.

First Load:
Below are the METADATA and MERGE lines.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTable|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|M|T|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT||HRC_SQLLOADER|RELOCATION_ALLOWANCE_TABLE
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName|UserTableId(SourceSystemId)|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT||RELOCATION_ALLOWANCE_TABLE|HRC_SQLLOADER|RELOCATION_ALLOWANCE_COLUMN
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableId(SourceSystemId)|RowName|RowLowRangeOrName|LegislativeDataGroupName|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|RELOCATION_ALLOWANCE_TABLE|GRADE_1|GRADE_1||HRC_SQLLOADER|RELOCATION_ALLOWANCE_ROW1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|RELOCATION_ALLOWANCE_TABLE|GRADE_2|GRADE_2||HRC_SQLLOADER|RELOCATION_ALLOWANCE_ROW2

We have added multiple METADATA fields to overcome the parent-child relationship issue.

Second Load:
For the second load pass to work, your data file must reference the exact records generated during the first step. Because we lack an LDG to use as a user-key qualifier, we must rely on database surrogate IDs. This means before building your column instance file, you need to run a quick BI Publisher SQL query to extract the newly generated UserColumnId and UserRowId values. Adding these unique system identifiers into your second file establishes the exact relationship required by the data loader engine.

Below is the SQL Query to get the UserColumnId and UserRowId.

select 
    t.base_user_table_name,
    r.user_row_id,
    r.row_low_range_or_name,
    c.user_column_id,
    c.base_user_column_name
from 
    ff_user_tables t,
    ff_user_rows_f r ,
    ff_user_columns c
where 
    t.base_user_table_name = 'ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT'
    and t.user_table_id = r.user_table_id
    and t.user_table_id = c.user_table_id

Below are the METADATA and MERGE lines for second load.

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|SourceSystemOwner|SourceSystemId|UserColumnId|UserRowId
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|HRC_SQLLOADER|RELOCATION_ALLOWANCE_VAL1|300000118166578|300000118166576
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|HRC_SQLLOADER|RELOCATION_ALLOWANCE_VAL2|300000118166578|300000118166577

With both load passes complete, let’s navigate back to the application to verify that our global table structure and data instances have been created properly.

Splitting your load into a two-step process and leveraging BI Publisher to extract those specific surrogate keys is the most reliable workaround to handle global UDT layouts. I hope this saves you time on your next Global UDT Creation.