OIC - Automating SFTP File Listing in OIC Based on Dynamic Date Filters and Adhoc Runs for past date files

Use Case:

In Oracle Integration Cloud (OIC), we need to list files from an source SFTP server based on a specific filename pattern and a date in the format YYYYMMDD. This date should be passed as a parameter to support both:

1. Scheduled Runs – Using the current date dynamically.
2. Ad-hoc or Past Date Processing – Allowing users to specify any past date manually.

This flexibility ensures that integrations can process files efficiently, whether through automated schedules or on-demand executions.

Date validation condition:

 (((FileProcessingDate = 'YYYYMMDD') or (FileProcessingDate = '')) or (FileProcessingDate = format-dateTime(concat(substring(FileProcessingDate, 1, 4), '-', substring(FileProcessingDate, 5, 2), '-', substring(FileProcessingDate, 7, 2)), '[Y0001][M01][D01]')))

File pattern population logic:

When processingdate != 'YYYYMMDD' and  processingdate != ''

replace(lookupValue("Common_CCS_Interface_SFTP_Lookup", "IntegrationId", Var_InterfaceId, "SourceFileName", "CTFCLPMC*"), "YYYYMMDD", FileProcessingDate)

Otherwise:

replace(lookupValue("Common_CCS_Interface_SFTP_Lookup", "IntegrationId", Var_InterfaceId, "SourceFileName", "CTFCLPMC*"), "YYYYMMDD", format-dateTime(current-dateTime(), "[Y0001][M01][D01]"))

Solution Steps:

1. Create a Scheduled Orchestration integration pattern to enable both scheduled and manual triggers.
2. Define a schedule parameter fileprocesingDate (YYYYMMDD format) to accept the date dynamically.
3. Validation: add a throw new fault and put the skip condition, if invalid date, it will throw error. Skip conditions:
1. Procrssing date is YYYYMMDD 
2. Processing date is empty
3. File processing is in YYYYMMDD format. 
4. Configure the sftp adapter and choose list files operations and put the following branching to populate the right file name pattern.
1. When processingdate is not YYYYMMDD and not empty, take file name part and current date from the schedule parameter for adhoc run.
2. Otherwise, take the name and current date for automatic processing.
5. Take the list of files and process one by one using a for each loop
6. If no files found, send a notication or log an appropriate message.

Testing & Deployment

• Test with multiple scenarios:
  • Scheduled execution with the current date.
  • Manual execution with a specific past date.
  • Scenarios where no matching files exist.
• Deploy the integration and configure scheduling as per business needs.

Detailed screenshots: