Saturday, July 11, 2015

Query to get running and completed concurrent jobs with start date and end date

  SELECT f.request_id,
         pt.user_concurrent_program_name user_conc_program_name,
         f.actual_start_date start_on,
         f.actual_completion_date end_on,
            FLOOR (
                 (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
               / 3600)
         || ' hours '
         || FLOOR (
                 (  (  (f.actual_completion_date - f.actual_start_date)
                     * 24
                     * 60
                     * 60)
                  -   FLOOR (
                           (  (f.actual_completion_date - f.actual_start_date)
                            * 24
                            * 60
                            * 60)
                         / 3600)
                    * 3600)
               / 60)
         || ' minutes '
         || ROUND (
               (  (  (f.actual_completion_date - f.actual_start_date)
                   * 24
                   * 60
                   * 60)
                -   FLOOR (
                         (  (f.actual_completion_date - f.actual_start_date)
                          * 24
                          * 60
                          * 60)
                       / 3600)
                  * 3600
                - (  FLOOR (
                          (  (  (f.actual_completion_date - f.actual_start_date)
                              * 24
                              * 60
                              * 60)
                           -   FLOOR (
                                    (  (  f.actual_completion_date
                                        - f.actual_start_date)
                                     * 24
                                     * 60
                                     * 60)
                                  / 3600)
                             * 3600)
                        / 60)
                   * 60)))
         || ' secs '
            time_difference,
         p.concurrent_program_name concurrent_program_name,
         DECODE (f.phase_code,
                 'r', 'running',
                 'c', 'complete',
                 f.phase_code)
            phase,
         f.status_code
    FROM apps.fnd_concurrent_programs p,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
   WHERE     f.concurrent_program_id = p.concurrent_program_id
         AND f.program_application_id = p.application_id
         AND f.concurrent_program_id = pt.concurrent_program_id
         AND f.program_application_id = pt.application_id
         AND pt.language = USERENV ('lang')
         AND f.actual_start_date IS NOT NULL

ORDER BY f.actual_start_date DESC;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect