7 Replies Latest reply on May 12, 2016 6:31 AM by Zubear

    Concurrent Program Mail Alert

    Ramaraju

      Hi all,

      How do I get a mail regarding concurrent program status?

      For example, 'gather schema stats' concurrent program status is completed/error/warning

       

      Thanks,

      Ramaraju

        • 1. Re: Concurrent Program Mail Alert
          Jason Brincat
          1 person found this helpful
          • 3. Re: Concurrent Program Mail Alert
            Kj - Kiran Jadhav

            Hi Ramaraju,

             

            You can create a monitoring shell script to send the alerts and schedule it in crontab or OEM.

             

            You can use below Query to check the status in your shell script:

             

            SELECT a.request_id "Req Id", SUBSTR (u.user_name, 1, 25) "Submitted by", DECODE (a.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running', 'NA') "Phasecode"

                  ,DECODE (a.status_code

                          ,'A', 'Waiting'

                          ,'B', 'Resuming'

                          ,'C', 'Normal'

                          ,'D', 'Cancelled'

                          ,'E', 'Error'

                          ,'F', 'Scheduled'

                          ,'G', 'Warning'

                          ,'H', 'On Hold'

                          ,'I', 'Normal'

                          ,'M', 'No Manager'

                          ,'Q', 'Standby'

                          ,'R', 'Normal'

                          ,'S', 'Suspended'

                          ,'T', 'Terminating'

                          ,'U', 'Disabled'

                          ,'W', 'Paused'

                          ,'X', 'Terminated'

                          ,'Z', 'Waiting'

                          ) "Status"

                  ,a.argument_text " Arguments ", TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date", TO_CHAR (actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "Completion Date"

                  ,ctl.user_concurrent_program_name "Conc Program Name", a.completion_text "Error/Warning"

              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

                  ,applsys.fnd_user u

            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.status_code IN ('D', 'E', 'G', 'H', 'M', 'S', 'T', 'U', 'X', 'W')                                                                                                       -- for the status ERROR

               AND a.phase_code = 'C'                                                                                                                                                        -- Phase code COMPLETED

               AND actual_start_date >= SYSDATE - 7                                                                                                                                       -- Threshold days to check

               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 a.requested_by = u.user_id

               AND ctl.user_concurrent_program_name = 'Gather Schema Statistics';

             

             

            Regards,

            Kiran

            1 person found this helpful
            • 4. Re: Concurrent Program Mail Alert
              Kj - Kiran Jadhav

              If you want shell script then check out at:

               

              https://h2hdba.blogspot.in/2016/05/script-to-monitor-gather-schema.html

               

              Regards,

              Kiran

              1 person found this helpful
              • 5. Re: Concurrent Program Mail Alert
                Ramaraju

                Hi,

                I didn't find any output.

                 

                Thanks,

                Ramaraju

                • 6. Re: Concurrent Program Mail Alert
                  Kj - Kiran Jadhav

                  Query which I had posted earlier will give output if Gather Schema statistics request completed with Error in last 7 days. You need to comment or modify the below highlighted parameters as per your requirement.

                   

                  SELECT a.request_id "Req Id", SUBSTR (u.user_name, 1, 25) "Submitted by", DECODE (a.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running', 'NA') "Phasecode"

                        ,DECODE (a.status_code

                                ,'A', 'Waiting'

                                ,'B', 'Resuming'

                                ,'C', 'Normal'

                                ,'D', 'Cancelled'

                                ,'E', 'Error'

                                ,'F', 'Scheduled'

                                ,'G', 'Warning'

                                ,'H', 'On Hold'

                                ,'I', 'Normal'

                                ,'M', 'No Manager'

                                ,'Q', 'Standby'

                                ,'R', 'Normal'

                                ,'S', 'Suspended'

                                ,'T', 'Terminating'

                                ,'U', 'Disabled'

                                ,'W', 'Paused'

                                ,'X', 'Terminated'

                                ,'Z', 'Waiting'

                                ) "Status"

                        ,a.argument_text " Arguments ", TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date", TO_CHAR (actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "Completion Date"

                        ,ctl.user_concurrent_program_name "Conc Program Name", a.completion_text "Error/Warning"

                    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

                        ,applsys.fnd_user u

                  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.status_code IN ('D', 'E', 'G', 'H', 'M', 'S', 'T', 'U', 'X', 'W')                                                                                                       -- for the status ERROR (Comment out if need history for all status)

                     AND a.phase_code = 'C'                                                                                                                                                        -- Phase code COMPLETED

                    AND actual_start_date >= SYSDATE - 7                                                                                                                                       -- Threshold days to check

                     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 a.requested_by = u.user_id

                     AND ctl.user_concurrent_program_name = 'Gather Schema Statistics';

                   

                  Regards,

                  Kiran