How to use dynamic SQL Statement for the Date Parameter in Scheduled Concurrent Requests

Cause: If we schedule a concurrent requests which has a date parameter and value of this date parameter is coming through the dynamic SQL statement or value set then we can't see the correct value to the request parameter as per the dynamic SQL statement or value set. It will take the same value each time which we have passed first time.

Example: Date parameter details of your Request.
     Seq             : 1
     Parameter    : Test Date Time
     Description  : Test Date Time
     Enabled      : Make sure it is checked

   Under Validation Section :

     Value Set    : FND_STANDARD_DATETIME
     Default Type : SQL Statement
     Default Value: SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') FROM DUAL

  Under Display Section :

     Display Size : 11
     Prompt       : Test Date Time
     Token        : Test_Date_Time

Submit the request only once and take a note of request id's.

When you will check these request id in FND_CONCURRENT_REQUESTS table then the value of ARGUMENT1 will be the same each time. But it should be different for every request id.

Solution :
Below are the steps which we have to follow to use dynamic SQL statement or a Value set  for the date parameter in scheduled concurrent requests.

1. Create the following customer procedure under APPS schema that will be used to update the date parameter (ARGUMENT1) in FND_CONCURRENT_REQUESTS table for each run with same query used in the parameters screen for the concurrent program:

CREATE OR REPLACE PROCEDURE XX_CUSTOM_INCREMENT AS
PARAM    VARCHAR2(20);
BEGIN
  SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  INTO   PARAM
  FROM   DUAL;

  FND_RESUB_PRIVATE.SET_PARAMETER(1,PARAM);
END;

2. Navigate to "Concurrent: Program > Define" screen and Query for program.
3. Enter in the "Incrementor" field the procedure name created in step 1: XX_CUSTOM_INCREMENT.
4. Save Changes
5. Cancel previous scheduled request and schedule a new one.

Script to get Scheduled Concurrent Requests

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;