OIC - Rolling Date Window Using Schedule Parameters in Oracle OIC

📌 Use Case

In many OIC scheduled integrations, we need to: 

Process data between StartTime and EndTime

After successful run:

Move EndTime to StartTime

Reset EndTime

Use current timestamp for next execution

This ensures:

No duplicate processing

No missing data

Smooth incremental extraction

Example:

Run 1 → Process: Jan 01 – Jan 02

Run 2 → Process: Jan 02 – Jan 03

Run 3 → Process: Jan 03 – Jan 04

This is called a Rolling Date Window.

🛠 Solution Steps in OIC

Step 1️⃣ – Create Schedule Parameters

1. Go to Schedule → Parameters
2. Create: StartTime , EndTime
3. Set initial default values.

Step 2️⃣ – Capture Current Time (Timezone Adjusted)

Use Assign action:

format-dateTime(string(adjust-dateTime-to-timezone(current-dateTime(), dayTimeDuration('PT8H'))),'[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01].[H001]Z')

This: Gets current time Adjusts to required timezone (HK example PT8H)

Formats to ISO timestamp

Store it in: Var_CurrentHKDateTime

Step 3️⃣ – Use Stitch (Conditional Logic)

Condition:

EndTime != ""

When EndTime exists:

Use existing StartTime & EndTime

Otherwise:

Set EndTime = Current Timestamp

Step 4️⃣ – Rotate the Values (Important Step)

After successful processing:

Use Assign:

StartTime = EndDate

EndTime = ""

This is called:

🔄 Parameter Rotation

🔁 Rolling Window Shift

📦 Incremental Date Rotation Logic

🎯 Benefits

✅ Prevents duplicate data

✅ Enables incremental extraction

✅ Fully dynamic window

✅ No manual parameter update

✅ Works for ERP, HCM, REST, DB polling