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.
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.
Thanks a lot Gaurav Kumar
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi Gaurav,
ReplyDeleteIf I am having 2 date parameters in the concurrent program,
Ex. Start Date and End date.
If I schedule a program which should run on every month end with parameters
Start_date : first of that month
End_date : last date of that month.
How does the Incrementer procedure recognize which parameter to be passed?
Thanks.
Hi Ashok,
DeleteI never tried it but you may try as below.
Assume you have two parameters Argument1 - Start Date and Argument2 - End Date.
Now write your increment procedure as.
CREATE OR REPLACE PROCEDURE XX_CUSTOM_INCREMENT AS
l_arg1 VARCHAR2(20);
l_arg2 VARCHAR2(20);
BEGIN
SELECT TO_CHAR(TRUNC(SYSDATE,'MM'),'DD-MON-YYYY HH24:MI:SS')
INTO l_arg1
FROM DUAL;
FND_RESUB_PRIVATE.SET_PARAMETER(1,l_arg1);
SELECT TO_CHAR(LAST_DAY(TRUNC(sysdate))+0.99999,'DD-MON-YYYY HH24:MI:SS')
INTO l_arg2
FROM DUAL;
FND_RESUB_PRIVATE.SET_PARAMETER(2,l_arg2);
END;
/