When an Ellucian Data Connect implementation moves Banner PL/SQL processing into JavaScript, Oracle cursor logic that selects the latest non-terminated effective-dated job record before a cutoff date has to preserve the same record-selection rules. In the workflow covered here, JavaScript filter() reproduces the Oracle eligibility conditions, while reduce() selects the qualifying record with the greatest effectiveOn.

The important detail is the cutoff comparison. The original Oracle logic allows dates up to the cutoff but separately excludes a record whose effective date exactly matches it. In JavaScript, those two conditions become a single strict < comparison. The resulting filter().reduce() expression returns the latest qualifying record, or null when no matching record exists.

This article is part of our Ellucian Data Connect tips and implementation patterns series. This entry focuses on preserving effective-dated Banner record-selection behavior when Oracle PL/SQL logic is translated into Data Connect JavaScript.

What the Oracle cursor needs to select

The workflow needs an existing Banner job-detail record that represents the latest applicable state before a new effective date.

Several job-detail records may exist for the same employee. The required record must:

  • not be terminated;
  • have an effective date earlier than the new effective date;
  • be the latest record that satisfies those conditions.

The source PL/SQL behavior can be summarized as:

SELECT *
FROM nbrjobs_detail
WHERE job_status != 'T'
  AND effective_date <= l_new_effective_date
  AND effective_date != l_new_effective_date
ORDER BY effective_date DESC
FETCH FIRST 1 ROW ONLY;

job_status != ‘T’ excludes terminated records.

The two effective-date conditions exclude both future records and a record on the exact new effective date. The remaining rows are ordered from newest to oldest, and FETCH FIRST 1 ROW ONLY returns the latest qualifying record.

The key point is that the workflow does not simply search for the maximum date. It first defines which records are eligible and then selects the latest one.

Why JavaScript uses < instead of <=

The Oracle cursor contains both:

effective_date <= l_new_effective_date
effective_date != l_new_effective_date

Together, these conditions mean that the record must be strictly earlier than l_new_effective_date.

The JavaScript version therefore uses:

d.effectiveOn < l_new_effective_date

This single comparison preserves both Oracle conditions. Records before the cutoff remain eligible, while records on or after the cutoff are excluded.

Using <= in the JavaScript filter would change the source behavior because a record whose effectiveOn equals l_new_effective_date could remain in the candidate set.

Translate the cursor into `filter()` and `reduce()`

The JavaScript implementation separates the selection into two operations:

  1. filter() keeps only eligible records.
  2. reduce() selects the eligible record with the greatest effectiveOn.

The complete implementation is:

const currentJobDetail = employeeJobDetails
    .filter(d =>
        d.jobStatus !== 'terminated' &&
        d.effectiveOn < l_new_effective_date
    )
    .reduce((max, d) =>
        !max || d.effectiveOn > max.effectiveOn ? d : max
    , null);

Filter out ineligible records

The filtering portion is:

.filter(d =>
    d.jobStatus !== 'terminated' &&
    d.effectiveOn < l_new_effective_date
)

The mapping from the Oracle conditions is straightforward:

Oracle logicJavaScript logic
job_status != ‘T’d.jobStatus !== ‘terminated’
effective_date <= l_new_effective_date plus effective_date != l_new_effective_dated.effectiveOn < l_new_effective_date

After filter() completes, only records that satisfy the original cursor conditions remain.

Select the latest qualifying record

The second stage is:

.reduce((max, d) =>
    !max || d.effectiveOn > max.effectiveOn ? d : max
, null);

max represents the best matching record found so far.

The reducer starts with null. The first qualifying record becomes the initial max. Each later record replaces it only when its effectiveOn is greater.

After all filtered records have been processed, currentJobDetail contains the qualifying record with the greatest effectiveOn.

The input array does not need to be ordered first because reduce() compares each qualifying record with the current maximum.

Here, reduce() is the standard JavaScript array method. It is separate from the Ellucian Data Connect reducer step used elsewhere in pipeline processing.

Handle the case where no record matches

Because the reducer is initialized with null, an empty filtered collection leaves currentJobDetail as null.

The downstream logic should therefore check the result before using it:

if (currentJobDetail) {
    // build the termination entry...
}

The surrounding workflow determines what to do when no matching source record exists. This pattern establishes only that the result can be null and should be guarded before its fields are accessed.

When a matching record does exist, the pipeline can use it as source data for later processing. For example, a workflow that creates a new effective-dated job-detail entry may use values from currentJobDetail before building the new request.

That later INSERT pattern is covered separately in How to use a nil GUID to insert Ellucian Banner job-detail records through Ellucian Ethos.

Keep the pattern scoped to this selection logic

This implementation translates a specific Oracle cursor behavior: exclude terminated records, accept only records before the new effective date, and select the qualifying record with the greatest effective date.

It should not be treated as a universal replacement for every Oracle cursor. Other cursors may implement different selection or processing rules and should be translated according to their own behavior.

Key implementation details

  • Preserve the Oracle record-selection behavior, not only its syntax.
  • Exclude records whose jobStatus is terminated.
  • Use effectiveOn < l_new_effective_date to reproduce both Oracle effective-date guards in this implementation.
  • Apply filter() first to remove ineligible records.
  • Use reduce() to retain the qualifying record with the greatest effectiveOn.
  • Initialize the JavaScript reducer with null.
  • Guard currentJobDetail before using its fields.
  • Keep the pattern scoped to Oracle logic with the same selection semantics.

Related Data Connect posts

Need help migrating Banner PL/SQL logic into Data Connect?

ABCloudz helps institutions analyze Banner PL/SQL integration logic, preserve effective-dated business rules, translate Oracle processing into Data Connect JavaScript, and build maintainable integration workflows for Banner, Ethos, and related systems.

Discuss your Oracle logic migration