Recently I came across a scenario where I had to schedule a concurrent program which contained couple of parameters.
One of the parameter was a date parameters and one was a character parameter which was defaulted to a string value based on SYSDATE when submitted from SRS. This was done using program definition defaults.
The program was scheduled with increment date parameters on and we observed that the non date parameter was the same for all schedules. This is because Oracle only increments date parameters and doesn't honor the defaulting logic setup at the concurrent program definitions.
This is a classic problem and everybody knows the classic solution :
Oracle provides a much less intrusive and a straight forward approach to achieve this common requirement : Concurrent Request Incrementor
An example incrementor procedure is below. Note that we completely take over the incrementing logic and it becomes our responsibility to increment the data parameters too.
Now this procedure can be given as the Incrementor function in the program definition window as shown below.
One of the parameter was a date parameters and one was a character parameter which was defaulted to a string value based on SYSDATE when submitted from SRS. This was done using program definition defaults.
The program was scheduled with increment date parameters on and we observed that the non date parameter was the same for all schedules. This is because Oracle only increments date parameters and doesn't honor the defaulting logic setup at the concurrent program definitions.
This is a classic problem and everybody knows the classic solution :
Write a wrapper that implements the defaulting logic and submit the program in question. This wrapper program can be then scheduled.
Oracle provides a much less intrusive and a straight forward approach to achieve this common requirement : Concurrent Request Incrementor
An example incrementor procedure is below. Note that we completely take over the incrementing logic and it becomes our responsibility to increment the data parameters too.
PROCEDURE
parameter_incrementor
IS
l_para1 VARCHAR2 (1000);
l_defaulted_param VARCHAR2 (1000);
l_date_param VARCHAR2 (1000);
l_param4 VARCHAR2 (1000);
BEGIN
-- get parameter1 value
l_para1 := FND_RESUB_PRIVATE.GET_PARAMETER (1);
-- get paramter4 value
l_param4 := FND_RESUB_PRIVATE.GET_PARAMETER (4);
-- contruct the string parameter that needs to be used
when resubmitting
-- adn aslo increment the date parameter
SELECT 'XXXXX_'
|| l_para1
|| 'XXXXXXXXX_'
|| TO_CHAR (SYSDATE, 'MMDDYYYY_HH24MISS'),
TO_CHAR (TO_DATE (l_param4, 'YYYY/MM/DD HH24:MI:SS') + 1,
'YYYY/MM/DD HH24:MI:SS')
INTO l_defaulted_param, l_date_param
FROM DUAL;
-- setting parameter values
FND_RESUB_PRIVATE.SET_PARAMETER (2, l_defaulted_param);
FND_RESUB_PRIVATE.SET_PARAMETER (3, l_defaulted_param);
FND_RESUB_PRIVATE.SET_PARAMETER (4, l_date_param);
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END parameter_incrementor;
Now this procedure can be given as the Incrementor function in the program definition window as shown below.
