2 Replies Latest reply on Apr 26, 2017 6:03 PM by Mr_VL

    Periodic Alert SQL has different output in Alert and in SQL Developer under APPS user

    Mr_VL

      I want to create Periodic Alert (Detail) to send e-mail about every ERROR'ed request.

      When I run the following SQL in SQL Developer using APPS user I am getting 3 request_ids.

       

       

      But when I try to check my Alert I am getting 7 e-mails for request_id's that are different then 3 request_id's I am getting from SQL Developer query.

      Why I am getting different requests_id's from SQL query ran under APPS user in SQL Developer and ran from Alert?

      Alert was defined using "Application Object Library" Application.

       

       

      --- SQL Developer SQL

      select

        fcr.request_id

      from

        fnd_concurrent_requests fcr,

        fnd_concurrent_programs fcp,

        fnd_concurrent_programs_tl fcpt,

        fnd_user fu,

        fnd_responsibility fr

      where

        fcr.status_code in ('D', 'E', 'S', 'T', 'X') and

        fcr.phase_code = 'C' and

        fcr.last_update_date > sysdate - 30 and

        fu.user_id = fcr.requested_by and

        fcr.concurrent_program_id = fcp.concurrent_program_id and

        fcr.concurrent_program_id = fcpt.concurrent_program_id and

        fcr.responsibility_id = fr.responsibility_id

      order by

        fcr.last_update_date,

        fcr.request_id

       

       

      --- Periodic Alert SQL

      select

        fcr.request_id

      into

        &request_id

      from

        fnd_concurrent_requests fcr,

        fnd_concurrent_programs fcp,

        fnd_concurrent_programs_tl fcpt,

        fnd_user fu,

        fnd_responsibility fr

      where

        fcr.status_code in ('D', 'E', 'S', 'T', 'X') and

        fcr.phase_code = 'C' and

        fcr.last_update_date > sysdate - 30 and

        fu.user_id = fcr.requested_by and

        fcr.concurrent_program_id = fcp.concurrent_program_id and

        fcr.concurrent_program_id = fcpt.concurrent_program_id and

        fcr.responsibility_id = fr.responsibility_id

      order by

        fcr.last_update_date,

        fcr.request_id