10 Replies Latest reply on Jun 8, 2016 4:23 PM by John_K

    Scheduling a Concurrent Request thru front end scheduler

    1048687

      Dear team,

       

      We have a requirement to fetch the data from a table with in a certain interval of time say 12 hours. So we have created a db procedure with input parameter p_interval.

      This pl/sql procedure is called from a concurrent program. we defined a parameter to the concurrent program. When we executed the procedure from concuurent program its working fine,

      If we are passing concurrent program parameter value as 12 , then procedure is fetching data for the 12 hours from the table.

      If we are passing concurrent program parameter value as 3, then procedure is fetching data for the 3hours from the table.

       

      Now we need to schedule the concurrent program from frontend periodically. As of now twice a day ie 12 hours.

      but the scheduling period may vary. Say 4 times a day ie. every  6 hours.

       

      We need to pass the scehduling period(say 12 hours)  set to the scheduler the parameter of concurrent program so that it invoke procedure to fetch last 12 hours data,

       

      Can you pls suggest how to pass the period set in scehduler to  the parameter of the concurrent program.

       

      Thanks in advance

        • 1. Re: Scheduling a Concurrent Request thru front end scheduler
          Bashar.

          Hi,

           

          The parameters cannot be changed after the request is submitted.

           

          You can setup your scheduling periods in a custom database table and pass the custom period name in the request parameter (or omit it and work with only one setup).

          Then submit the request and schedule it to run every hour (for example) and do the processing based on the time it runs by comparing it with the custom schedule.

           

          Regards,

          Bashar

          • 2. Re: Scheduling a Concurrent Request thru front end scheduler
            1048687

            Hi bashar,

             

            Thanks for the response.

             

            We need to pass the scheduler period as value to the parameter of  concurrent program . Is there any other way other than using the custom table.

            • 3. Re: Scheduling a Concurrent Request thru front end scheduler
              Bashar.

              You can use a custom lookup and query the view FND_LOOKUP_VALUES_VL for details on the period intervals.

               

              Regards,

              Bashar

              • 4. Re: Scheduling a Concurrent Request thru front end scheduler
                John_K

                You can use any structure to hold the value.

                • Profile value
                • Application lookup
                • Value set
                • User defined table

                etc etc...

                 

                It depends on how complicated you want to make your process. I'd consider using a site-level profile value, then you can be sure you're only going to return a single value, whereas in an ALU you could get someone creating rows without end-dating the previous ones etc, which you would have to deal with in your code.

                 

                Remember though this will replace the requirement for a parameter to your concurrent program. You can't put in the code to select the value as the parameter, because doing so will set it the first time and use that for each resubmission (i.e. the code to derive the parameter isn't run on every execution of the job). So you would be modifying the code of your plsql procedure to use the value stored in the db somewhere.

                • 5. Re: Scheduling a Concurrent Request thru front end scheduler
                  1048687

                  Dear team,

                   

                  Thank you for the reply.

                  As suggested we have used the site level profile value as input to the concurrent program. We want to use this profile value  as period value to the scheduler. Can you please suggest.

                  • 6. Re: Re: Scheduling a Concurrent Request thru front end scheduler
                    John_K

                    You wouldn't pass this as a parameter to the concurrent program because you would have to reschedule it every time the value changed. Instead you would modify your code to use it. So say your current code was this:


                    Procedure blah(errbuff Out Varchar2, Retcode Out Varchar2, pDelay Number)
                    Is
                     lnSomething Number;
                    Begin
                     Select val
                       Into lnSomething
                       From my_table
                     Where time_col>Sysdate-pDelay;
                    End blah;
                    


                    You would change it to:


                    Procedure blah(errbuff Out Varchar2, Retcode Out Varchar2)
                    Is
                     lnSomething Number;
                    Begin
                     Select val
                       Into lnSomething
                       From my_table
                     Where time_col>Sysdate-To_Number((Select Fnd_Profile.Value('XX_PROFILE_NAME') From Dual));
                    End blah;
                    


                    • 7. Re: Scheduling a Concurrent Request thru front end scheduler
                      1048687

                      Hi,

                       

                      Thank you for the response.

                       

                      Our requirement is to reschedule  the concurrent program from Concurrent manager with the site level profile value. Site level profile value has nothing to do with the concurrent program or the pl/sql code. We need to schedules the concurrent program periodically based on the profile value. Say if profile value is 30. then we have to schedule 30  minutes.

                      If the profile value is updated to 40 then we have to schedule for every 40 minutes.

                       

                      Thanks in advance

                      • 8. Re: Scheduling a Concurrent Request thru front end scheduler
                        John_K

                        You cannot dynamically change the schedule of a concurrent program short of having a process which physically goes and updates the schedule in the underlying tables.

                         

                        If you pass a parameter to a program on a resubmission schedule then you have to resubmit the program to pick up any changes to that parameter. If you have a default of SQL statement, profile values etc then it gets set only on the initial execution. The workaround to this is to modify the concurrent program code (as I demonstrated above very roughly) and then submit the concurrent request more often. The modification of the code should ensure that the program only runs when expected.

                        • 9. Re: Scheduling a Concurrent Request thru front end scheduler
                          1048687

                          Dear team,

                           

                          Can anyone suggest whether this  we can pass profile value as period to the scheduling of the concurrent program.

                          • 10. Re: Scheduling a Concurrent Request thru front end scheduler
                            John_K

                            See my previous response.