Building a Self-Healing Framework for Oracle EBS Concurrent Programs (Native PL/SQL, No External Tooling)

A hands-on walkthrough of a native PL/SQL framework that detects, corrects, and re-validates critical Oracle E-Business Suite concurrent programs after patches, DR tests, and switchovers — without any external automation tool.

The problem

Every Oracle E-Business Suite environment runs dozens of critical concurrent programs around the clock: workflow background processing, purge routines, interface loaders, report generators. After any sensitive operation — a security patch, a clone, a disaster-recovery (DR) test, a switchover — someone has to confirm that all of those programs came back and are running normally.

Traditionally that check is manual, slow, and depends on an expert who knows exactly which programs matter and how often each one should run. That expert becomes a bottleneck, and manual checks are easy to get wrong under time pressure.

This article describes a native PL/SQL framework that industrializes the check. It runs a full Detection → Correction → Re-validation cycle, on demand, entirely inside Oracle EBS — no external automation tool, no screen-scraping robot, no additional license.

Why native, not an interface robot?

Interface-driven automation (RPA) that drives EBS screens is a common first instinct. On Oracle EBS R12 it has a well-known weakness: screen automations break after patches an unexpected pop-up, an error message, or a shifted field freezes the robot. That is exactly the moment you most need the check to be reliable.

A low-level control should rest on what does not move: the concurrent-processing tables and the FND submission APIs. That is what this framework does.

Architecture at a glance

The framework has three layers.

1. Data layer — configuration and journaling tables (generic prefix XMON):

2. Processing layer — three PL/SQL packages:

3. Exposure layer — the three packages are registered as standard concurrent programs and attached to a request group, so operators launch them from the normal Submit Request window. No SQL knowledge is needed for day-to-day use.

The three phases chain into one on-demand cycle:

The data model

Here is the core registry table, trimmed to the columns that matter for this article.

CREATE TABLE xmon_critical_programs (
    critical_program_id        NUMBER          NOT NULL,
    enabled_flag               VARCHAR2(1)  DEFAULT 'Y' NOT NULL,
    criticality_code           VARCHAR2(30) DEFAULT 'CRITICAL' NOT NULL,
    application_short_name     VARCHAR2(50)    NOT NULL,
    concurrent_program_name    VARCHAR2(100)   NOT NULL,
    user_concurrent_program_name VARCHAR2(240),
    expected_user_name         VARCHAR2(100)   NOT NULL,
    expected_resp_key          VARCHAR2(100)   NOT NULL,
    expected_resp_appl_short   VARCHAR2(50)    NOT NULL,
    max_absence_minutes        NUMBER,
    allow_auto_resubmit_flag   VARCHAR2(1)  DEFAULT 'N' NOT NULL,
    warning_is_blocking_flag   VARCHAR2(1)  DEFAULT 'N' NOT NULL,
    business_description       VARCHAR2(4000),
    CONSTRAINT xmon_critical_programs_pk PRIMARY KEY (critical_program_id),
    CONSTRAINT xmon_critical_programs_u1 UNIQUE (concurrent_program_name),
    CONSTRAINT xmon_critical_programs_ck1 CHECK (enabled_flag IN ('Y','N')),
    CONSTRAINT xmon_critical_programs_ck3 CHECK (allow_auto_resubmit_flag IN ('Y','N'))
);

Two flags carry the whole safety model:

• enabled_flag — is this program monitored? Set on every program you want under surveillance.
• allow_auto_resubmit_flag — may the framework resubmit it automatically? This is a safety lock, defaulting to N. Only idempotent programs with no dangerous side effects should be set to Y. Sensitive programs (accounting posts, external file transfers, purges) stay detected and reported but never auto-resubmitted — a human decides.

max_absence_minutes drives absence detection. A NULL value means no absence check — appropriate for manually launched or irregular programs.

Phase 1 — Detection

The Global Control entry point mirrors the signature of a standard concurrent program (errbuf/retcode first), so it can run both as a concurrent request and as an anonymous block.

PROCEDURE run_global_control(
    errbuf                  OUT NOCOPY VARCHAR2,
    retcode                 OUT NOCOPY VARCHAR2,
    p_date_from             IN VARCHAR2,
    p_date_to               IN VARCHAR2,
    p_include_normal        IN VARCHAR2 DEFAULT 'Y',
    p_auto_correct          IN VARCHAR2 DEFAULT 'N',
    p_simulation            IN VARCHAR2 DEFAULT 'Y',
    p_operation_type        IN VARCHAR2 DEFAULT NULL,
    p_operation_reference   IN VARCHAR2 DEFAULT NULL
);

Dates arrive as strings and are parsed with the EBS canonical converter, which safely falls back to a default window when the input is empty or malformed:

l_date_from := NVL(fnd_date.canonical_to_date(p_date_from), TRUNC(SYSDATE) - 1);
l_date_to   := NVL(fnd_date.canonical_to_date(p_date_to),   SYSDATE);

Detection compares actual concurrent requests against the registry and flags two anomaly families:

• Execution failure — the program ran but completed in error (phase_code = 'C', status_code = 'E').
• Abnormal absence — the program has not run for longer than its max_absence_minutes threshold.

A read-only query that reproduces the heart of the detection logic:

SELECT p.concurrent_program_name,
       MAX(r.actual_start_date)                                   AS last_run,
       ROUND((SYSDATE - MAX(r.actual_start_date)) * 24 * 60)      AS age_minutes,
       p.max_absence_minutes,
       CASE
         WHEN p.max_absence_minutes IS NOT NULL
          AND (SYSDATE - MAX(r.actual_start_date)) * 24 * 60
              > p.max_absence_minutes
         THEN 'ABSENCE'
         ELSE 'OK'
       END                                                        AS absence_verdict
FROM   xmon_critical_programs p
JOIN   fnd_concurrent_programs cp
       ON cp.concurrent_program_name = p.concurrent_program_name
LEFT   JOIN fnd_concurrent_requests r
       ON r.concurrent_program_id = cp.concurrent_program_id
      AND r.actual_start_date > SYSDATE - 30
WHERE  p.enabled_flag = 'Y'
GROUP  BY p.concurrent_program_name, p.max_absence_minutes
ORDER  BY 1;

Each run produces a Control Run ID that links every downstream phase, plus a report listing the anomalies.

Calibrating thresholds

Set max_absence_minutes from observed frequency, not from a guess. A practical rule is two to three times the average interval between runs over the last 30 days:

SELECT p.concurrent_program_name,
       COUNT(r.request_id)                        AS runs_30d,
       ROUND(AVG(gap_minutes))                    AS avg_gap_min,
       ROUND(MAX(gap_minutes))                    AS max_gap_min,
       ROUND(MAX(gap_minutes) * 2)                AS suggested_threshold
FROM   xmon_critical_programs p
JOIN   fnd_concurrent_programs cp
       ON cp.concurrent_program_name = p.concurrent_program_name
LEFT   JOIN (
         SELECT concurrent_program_id, request_id,
                (actual_start_date - LAG(actual_start_date)
                   OVER (PARTITION BY concurrent_program_id
                         ORDER BY actual_start_date)) * 24 * 60 AS gap_minutes
         FROM   fnd_concurrent_requests
         WHERE  actual_start_date > SYSDATE - 30
       ) r ON r.concurrent_program_id = cp.concurrent_program_id
GROUP  BY p.concurrent_program_name
ORDER  BY runs_30d DESC;

Programs with zero runs in 30 days should not be placed under absence monitoring until operations confirms they are still expected to run.

Phase 2 — Correction

Correction reads the anomalies of a given Control Run ID and resubmits eligible programs.

PROCEDURE run_targeted_correction(
    errbuf            OUT NOCOPY VARCHAR2,
    retcode           OUT NOCOPY VARCHAR2,
    p_control_run_id  IN VARCHAR2,
    p_simulation      IN VARCHAR2 DEFAULT 'Y'
);

A program is resubmitted only if all three conditions hold:

1. enabled_flag = 'Y'
2. allow_auto_resubmit_flag = 'Y'
3. its expected arguments are known (or it takes none)

If any gate fails, the program is reported but never touched a human decides:

The resubmission reuses the original identity via fnd_global.apps_initialize and fnd_request.submit_request. Note the pattern: resolve IDs into variables first — you cannot inline a SELECT as an argument in a PL/SQL call.

DECLARE
  l_user_id      NUMBER;
  l_resp_id      NUMBER;
  l_resp_appl_id NUMBER;
  l_request_id   NUMBER;
BEGIN
  SELECT user_id INTO l_user_id
  FROM   fnd_user
  WHERE  user_name = 'BATCH_USER';               -- expected_user_name

  SELECT responsibility_id, application_id
  INTO   l_resp_id, l_resp_appl_id
  FROM   fnd_responsibility
  WHERE  responsibility_key = 'SYSTEM_ADMINISTRATOR'   -- expected_resp_key
  AND    ROWNUM = 1;

  fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);

  l_request_id := fnd_request.submit_request(
    application => 'XXAPP',                      -- application_short_name
    program     => 'XMON_SAMPLE_PROGRAM',        -- concurrent_program_name
    description => NULL,
    start_time  => NULL,
    sub_request => FALSE);

  IF l_request_id = 0 THEN
    DBMS_OUTPUT.PUT_LINE('SUBMIT FAILED: ' || fnd_message.get);
  ELSE
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Request ID = ' || l_request_id);
  END IF;
END;
/

Every action is written to XMON_CORRECTION_AUDIT with a simulation_flag. Running with p_simulation = 'Y' records what would happen without submitting anything — always do this before the real run.

Safety lock in practice. allow_auto_resubmit_flag defaults to N. Only programs you have explicitly reviewed as safe are set to Y. This keeps automatic remediation narrow and auditable.

Phase 3 — Re-validation

After correction, the Final Check re-runs an analysis over the same window, tied to the parent Control Run ID, and confirms the corrected programs are back to normal.

PROCEDURE run_final_check(
    errbuf                  OUT NOCOPY VARCHAR2,
    retcode                 OUT NOCOPY VARCHAR2,
    p_parent_control_run_id IN VARCHAR2,
    p_date_from             IN VARCHAR2,
    p_date_to               IN VARCHAR2,
    p_include_normal        IN VARCHAR2 DEFAULT 'Y'
);

When the final check reports the previously failing program as NORMAL, the cycle is closed: the framework has detected, corrected, and verified on its own.

Running the full cycle from SQL

You can drive the whole chain from an anonymous block for testing. In production the same three programs are launched from the Submit Request screen.

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_errbuf  VARCHAR2(4000);
  l_retcode VARCHAR2(100);
BEGIN
  xmon_monitor_pkg.run_global_control(
     errbuf                => l_errbuf,
     retcode               => l_retcode,
     p_date_from           => TO_CHAR(SYSDATE - 1, 'YYYY/MM/DD HH24:MI:SS'),
     p_date_to             => TO_CHAR(SYSDATE,     'YYYY/MM/DD HH24:MI:SS'),
     p_include_normal      => 'N',
     p_auto_correct        => 'N',
     p_simulation          => 'Y',
     p_operation_type      => 'POST_PATCH',
     p_operation_reference => 'CYCLE_DEMO');
  DBMS_OUTPUT.PUT_LINE('Global control: retcode=' || l_retcode);
END;
/

Read only the evaluated lines (filtering out the raw-collection rows):

SELECT critical_program_id, concurrent_program_name,
       phase_code, status_code, severity_code, correction_required_flag
FROM   xmon_control_results
WHERE  control_run_id = (SELECT MAX(control_run_id) FROM xmon_control_runs
                         WHERE operation_reference = 'CYCLE_DEMO')
AND    critical_program_id IS NOT NULL
ORDER  BY critical_program_id;

Registering the programs and making them visible

Registration uses the fnd_program API. The parameters must match what the packages expect, in order.

BEGIN
  fnd_program.register(
    program                => 'Global Control of Concurrent Programs',
    application            => 'XXAPP',
    enabled                => 'Y',
    short_name             => 'XMON_GLOBAL_CONTROL',
    executable_short_name  => 'XMON_GLOBAL_CONTROL_EXE',
    executable_application => 'XXAPP');
  COMMIT;
END;
/

To make a program launchable from a responsibility's Submit Request screen, add it to the request group that the responsibility already uses do not replace the group, or you lose its existing programs:

BEGIN
  fnd_program.add_to_group(
    program_short_name  => 'XMON_GLOBAL_CONTROL',
    program_application => 'XXAPP',
    request_group       => 'System Administrator Reports',
    group_application   => 'FND');
  COMMIT;
END;
/

Verify the attachment:

SELECT cp.concurrent_program_name
FROM   fnd_responsibility r
JOIN   fnd_request_group_units rgu ON rgu.request_group_id = r.request_group_id
JOIN   fnd_concurrent_programs cp  ON cp.concurrent_program_id = rgu.request_unit_id
WHERE  r.responsibility_key = 'SYSTEM_ADMINISTRATOR'
AND    rgu.request_unit_type = 'P'
AND    cp.concurrent_program_name LIKE 'XMON%';

Tip. A responsibility caches its request group at login. After adding programs, log out and back in, or the new programs will not appear in the LOV.

Lessons learned (the practical traps)

These are the issues that actually cost time during implementation. They generalize to most EBS customizations.

• Verify the application short name empirically on each instance. Assumptions are often wrong; a wrong value silently breaks FND lookups. Query fnd_application rather than trusting a convention.
• SQLERRM cannot go directly into a SQL VALUES clause. Capture it into a variable first, then insert.
• Some FND columns are version-dependent. RIGHT_JUSTIFY_ZERO_FILL_FLAG and the active-date columns are absent on some instances — detect columns dynamically instead of hard-coding them.
• Reserved words cannot be PL/SQL identifiers. For example, COLUMNS will fail in a FND_PROGRAM.REGISTER call.
• Date parameter defaults can truncate. A default returning YYYY/MM/DD HH24:MI:SS (19 chars) against a short display size raises ORA-01406 when the parameter window opens. Keep defaults in the format the value set expects, or leave them empty and let the package apply its fallback window.
• On R12.2 (online patching), create value sets through the Application Developer screens rather than inserting directly into FND_FLEX_VALUE_SETS, to avoid edition-column errors (ORA-01400).
• Never re-run creation scripts once successful. Use a dedicated repair script for targeted recompilation only.
• Static analysis pays off. A pre-install review caught blocking bugs — missing parameters, missing value sets, an off-by-one in argument parsing, an uninitialized collection, and a missing request group — before any object was created.

Why this approach holds up

A native, API-based control has three properties that an interface robot does not:

1. Resilience. It depends on tables and submission APIs, not on screens — so patches, pop-ups, and dynamic fields do not break it.
2. Speed and clarity. It reads state directly in the database; the logic stays readable and maintainable by any Oracle EBS team.
3. No added surface. No extra license, no new expertise to hire, nothing more to re-test at every upgrade.

It does not replace enterprise automation platforms — it complements them where low-level, native, post-operation control is the right tool: as close as possible to the Oracle concurrent-processing engine.

Written by **WASSIM BEN SAIDA. If this was useful, follow for more hands-on Oracle EBS and PL/SQL write-ups. Feedback and questions welcome in the comments.