2 Replies Latest reply: May 7, 2014 11:49 PM by AmitGargOCE RSS

    how to schedule select statement in crontab

    894943

      Hi,

       

      can any one provide the script how to schedule select statement in crontab.

       

      select a.request_id "Req Id"

            ,a.phase_code,a.status_code

            ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins

            ,(select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

                from APPLSYS.fnd_Concurrent_requests a2,

                     APPLSYS.fnd_concurrent_programs c2

               where c2.concurrent_program_id = c.concurrent_program_id

                 and a2.concurrent_program_id = c2.concurrent_program_id

                 and a2.program_application_id = c2.application_id

                 and a2.phase_code || '' = 'C') avg_mins

            ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"

      from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

          ,applsys.fnd_concurrent_queues q

          ,APPLSYS.fnd_concurrent_programs c

          ,APPLSYS.fnd_concurrent_programs_tl ctl

      where a.controlling_manager = b.concurrent_process_id

        and a.concurrent_program_id = c.concurrent_program_id

        and a.program_application_id = c.application_id

        and a.phase_code in ('I','P','R','T')

        and b.queue_application_id = q.application_id

        and b.concurrent_queue_id = q.concurrent_queue_id

        and ctl.concurrent_program_id = c.concurrent_program_id

        and ctl.language = 'US'

        and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 10

        and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >

         ( select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440

             from APPLSYS.fnd_Concurrent_requests a2,

                  APPLSYS.fnd_concurrent_programs c2

            where c2.concurrent_program_id = c.concurrent_program_id

              and a2.concurrent_program_id = c2.concurrent_program_id

              and a2.program_application_id = c2.application_id

              and a2.phase_code || '' = 'C'

         )

       

      regards,

      prathap