Saturday, July 11, 2015

Query to find all Scheduled concurrent Programs

  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))
         || ' hours '
         || FLOOR (
                 (  (  (f.actual_completion_date - f.actual_start_date)
                     * 24
                     * 60
                     * 60)
                  -   FLOOR (
                         (  (f.actual_completion_date - f.actual_start_date)
                          * 24))
                    * 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))
                  * 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_Short_name,
         DECODE (f.phase_code,
                 'R', 'Running',
                 'C', 'Complete',
                 f.phase_code)
            phase,
         DECODE (f.status_code,
                 'G', 'Warning',
                 'C', 'Normal',
                 'X', 'Terminated',
                 'E', 'Error',
                 f.status_code)
            Status
    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.request_id = 14990295
         AND pt.user_concurrent_program_name = 'MA Certificate Analysis Report'
         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