Concurrent Processing - Using a Dynamic SQL Statement for the Date Parameter in Scheduled Concurrent Requests (Doc ID 971928.1)

Goal

Provide steps to use a DATE parameter(s) in any concurrent program with a value based on Value Set or Dynamic SQL Query calculated at runtime.

The below example is based on a DATE parameter, however other variations are possible depending on every customer's business needs.

Before starting its worth mentioning how Oracle Applications is dealing with DATE parameter(s), when it is being used in concurrent programs/requests. 

Specifically when a concurrent program is scheduled to run on regular basis and the date is being incremented.

By default when you have a DATE parameter(s) with a fixed value defined in the concurrent program definition screen (or provided at runtime when scheduling the concurrent program for the first time), you have the option to enable the checkbox to increment the value of all date parameters used in the concurrent program in the subsequent runs of the request.

When the program is scheduled, it saves the first value of the DATE parameter(s) in FND_CONCUURRENT_REQUESTS table, and for the next subsequent run, it will retrieve this value and add to it the amount of time that this request scheduled to run on and use it as a value for the next run and this is the default incrementor used by Oracle Applications. This has been explained by development team in the following bug:
<Bug 3197639> - INCREMENT_DATE NOT WORKING PROPERLY WHEN START AT TIME IS CHANGE

If we applied the same concept and used default incrementor used by Oracle Applications but this time we will replace the source of the DATE parameter to be SQL statement of Value Set then what will happen is as following:

First time Oracle Applications will run the query to get the value which will be correct and expected behavior and save this value in FND_CONCURRENT_REQUESTS table at the time of request submission, then after first run completes successfully Oracle Applications will get the first value calculated and saved in the table above and increments it by the period that this request is scheduled to run on and will not re-run the query defined for the DATE parameter which is not the required needs.

Accordingly Oracle Applications offers a solution for this case, which is:

In the concurrent program define screen there is a field named "Incrementor" which will do the trick here.

This field is being used if provided to define a custom incrementor that will override the default Oracle Applications incrementor and will be called in each run for the request.

So if we define our own code through a custom database procedure that will do the calculation for DATA parameter(s) each time the request is re-scheduled and get the parameters updated regularly in FND_CONCURRENT_REQUESTS table for each run this will resolve the issue.

For your reference about this issue, it has been addressed in details in the following bug with development team:
<Bug 8240778> - SCHEDULED CONCURRENT REQUESTS DO NOT RESPECT QUERY VALUE SET FOR DATE PARAMETER

Please review the below simple example that will explain how this can be achieved/implemented.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.