13 Replies Latest reply on Mar 22, 2016 10:17 PM by 3122801

    Email Notification Alert (Alert Requisition Approval)

    3122801

      Hello Friends,

       

      I am working on a requirement where I have created an notification email alert using Oracle's Alert.

       

      Req:

      when a user creates a Requisition, she should be alerted with the following updates

       

      -- Alert when the Requisition is approved.

      -- Alert when a PO is created against that Requisition.

      -- Alert when that PO is approved

      -- finally, Alert when that PO is closed.

       

      the user will be the only person who get this alert (format is just simple text format). I have developed the alert SQL for this but not sure if it's the correct SQL for this requirement. Please correct my SQL or help me with the right approach. The Alert SQL is below

       

      ======================================================================================

      select

      distinct  prh.segment1 requisition_number

          , prh.description

         ,  (select user_name from fnd_user where user_id =  prh.created_by) requisition_created_by   

          , prh.creation_date

          , prh.created_by

          , prh.approved_date

          , prh.authorization_status

          ,(select fnd.user_name from fnd_user fnd where fnd.user_id = prh.LAST_UPDATED_BY) requisition_approved_by

         , poh.segment1 po_number

         , (select user_name from fnd_user where user_id =  poh.created_by) po_created_by

         , poh.creation_Date

         , poh.authorization_status po_authorization_status

         ,('bxxxxx@cityofsalem.net') Email_Address

      INTO &requisition_number, &descr, &requisition_created_by, &requisition_created_Date,  &preparer_name, &approved_date,

      &authorization_status,  &requisition_approved_by, &po_number, &po_created_by, &po_creation_Date, &po_authorization_status,

      &Email_Address  

      from po_distributions_all          pod

         , po_headers_all                poh

         , po_req_distributions_all      prd

         , po_requisition_lines_all      prl

         , po_requisition_headers_all    prh

      where 1=1

        and pod.PO_HEADER_ID            =   poh.PO_HEADER_ID

        and prd.distribution_id         =   pod.REQ_DISTRIBUTION_ID(+)

        and prl.requisition_line_id     =   prd.requisition_line_id

        and prh.requisition_header_id   =   prl.requisition_header_id

        and prh.approved_date IS NOT NULL

        AND prh.org_id = 102

        AND prh.rowid = :rowid

      ;

      =====================================================================================


      Any help will be greatly appreciated.

        • 1. Re: Email Notification Alert (Alert Requisition Approval)
          Jagadekara

          Hi,

          Req:

          when a user creates a Requisition, she should be alerted with the following updates

           

          -- Alert when the Requisition is approved.

          -- Alert when a PO is created against that Requisition.

          -- Alert when that PO is approved

          -- finally, Alert when that PO is closed.

          you may need more than one alert. Because, when requisition is approved one table will populate, and when po is created against that requisition then another table will populate.

          So the event is different, so we may need to create separate alert for each event.

           

          Hope this will help you..

          1 person found this helpful
          • 2. Re: Email Notification Alert (Alert Requisition Approval)
            3122801

            Thanks for your response. I was thinking the same too. So when an PO was created against a requisition what should be the logic, can you help please ?

            • 3. Re: Email Notification Alert (Alert Requisition Approval)
              Jagadekara

              Hi,

               

              create an event alert on po_distributions_all.

               

              write an sql query to get required details. I guess req_distribution_id will populate when we made po from requisition.

               

              Hope this will help you..

              1 person found this helpful
              • 4. Re: Email Notification Alert (Alert Requisition Approval)
                3122801

                Hi,

                 

                Hope you are doing great, So i separated the Requisition part as email alert which works just fine and I created the po_distributions_all part for that particular requisition to trigger when the PO gets created. But the alert did not work when the PO got created, Please advise or correct what I am missing. code below..

                 

                --------------------------------------------------------------------------------------

                select

                distinct  prh.segment1 requisition_number

                    , prh.description

                    , prh.creation_date

                   ,  (select user_name from fnd_user where user_id =  prh.created_by) requisition_created_by   

                    , prh.approved_date

                    , prh.authorization_status

                    ,(select fnd.user_name from fnd_user fnd where fnd.user_id = prh.LAST_UPDATED_BY) requisition_approved_by

                   , poh.segment1 po_number

                   , (select user_name from fnd_user where user_id =  poh.created_by) po_created_by

                   , poh.creation_date  po_created_date

                   , poh.authorization_status po_authorization_status

                   ,('xxxx@mail.com') Email_Address

                INTO &requisition_number, &description, &creation_date, &requisition_created_by, &approved_date,

                &authorization_status,  &requisition_approved_by, &po_number, &po_created_by, &po_created_date,

                &po_authorization_status, &Email_Address  

                from

                     po_distributions_all          pod

                   , po_headers_all                poh

                    , po_req_distributions_all     prd

                   , po_requisition_lines_all      prl

                   , po_requisition_headers_all    prh

                where 1=1

                  and pod.PO_HEADER_ID            =   poh.PO_HEADER_ID 

                  and prd.distribution_id         =   pod.REQ_DISTRIBUTION_ID

                  and prl.requisition_line_id     =   prd.requisition_line_id

                  and prh.requisition_header_id   =   prl.requisition_header_id

                   and prh.segment1                in   ('16730')

                   and pod.rowid                  =  :rowid                 

                ;

                --------------------------------------------------------------------------------------

                • 5. Re: Email Notification Alert (Alert Requisition Approval)
                  Jagadekara

                  Hi,

                   

                  Did you check alert history?

                   

                  Also you have used sub queries in select statement, I am not sure, but some times it will not work. SO for testing just hard code any value in place of sub query and check.

                   

                  Also you hard coded segment1, why?

                  • 6. Re: Email Notification Alert (Alert Requisition Approval)
                    3122801

                    Thanks for your response.

                     

                    Well, I created 2 alerts one for Req. approval and an another alert for PO created based on that req. # 16730, assuming that when I create a PO for that Req. number 16730  this alert will fire.

                     

                    The same sub queries for fnd user was used in the Req. approval alert and it worked as intended. When I check for history, I was able to find history for Req. approval which send me an email but when i create PO and send it through the approval process the above sql did not fire an email. Thanks for your response, do let me know if anything sparks you.

                     

                    Appreciate all the insight so far.

                    • 7. Re: Email Notification Alert (Alert Requisition Approval)
                      Jagadekara

                      Hi,

                       

                      Can you provide all screen shots of your alert?

                      • 8. Re: Email Notification Alert (Alert Requisition Approval)
                        3122801

                        Hey man, appreciate all your response and time.. Please see the attached doc. where the set I have defined for both the Requisition Approval and Po Approval. The req. approval alert works fine but no the PO approval alert.

                         

                        Below is the query..

                         

                        =====================================================

                        select

                        distinct prh.segment1 requisition_number

                            , prh.description

                            , prh.creation_date

                           , (select user_name from fnd_user where user_id =  prh.created_by) requisition_created_by   

                            , prh.approved_date

                            , prh.authorization_status

                            ,(select fnd.user_name from fnd_user fnd where fnd.user_id = prh.LAST_UPDATED_BY) requisition_approved_by

                           , poh.segment1 po_number

                           , (select user_name from fnd_user where user_id =  poh.created_by) po_created_by

                           , poh.creation_date  po_created_date

                           , poh.authorization_status po_authorization_status

                           ,('bpalani@cityofsalem.net') Email_Address

                        INTO &requisition_number, &description, &creation_date, &requisition_created_by, &approved_date,

                        &authorization_status, &requisition_approved_by, &po_number, &po_created_by, &po_created_date,

                        &po_authorization_status, &Email_Address  

                        from

                             po_distributions_all          pod

                           , po_headers_all                poh

                            , po_req_distributions_all     prd

                           , po_requisition_lines_all      prl

                           , po_requisition_headers_all    prh

                        where 1=1

                          and pod.PO_HEADER_ID =   poh.PO_HEADER_ID 

                          and prd.distribution_id         = pod.REQ_DISTRIBUTION_ID

                          and prl.requisition_line_id     = prd.requisition_line_id

                          and prh.requisition_header_id   = prl.requisition_header_id

                           and prh.segment1                in   ('16730') --hard coded just to get alert for this Requisition only

                           and pod.rowid =  :rowid                 

                        ;

                        =====================================================

                        p1.jpgp2.jpgp3.jpg

                         

                        Thanks in advance.

                         

                        • 9. Re: Email Notification Alert (Alert Requisition Approval)
                          Jagadekara

                          Hi,

                           

                          It's looking fine.

                           

                          Just check like this...

                           

                          Run same query in toad with out rowid and check output showing or not?

                           

                          If yes, then in alert query just comment reqnum hard coded line and also remove all select, just use req number. and in to field just use one email. then check.

                           

                          also remove whole query, just use select po_header_id into &po_head from po_distributions_all where 1=1 and REQ_DISTRIBUTION_ID is not null and rowid=:rowid

                          and into field just use one email id.

                           

                          In keep day give 3.

                           

                          Then let me know.

                          1 person found this helpful
                          • 10. Re: Email Notification Alert (Alert Requisition Approval)
                            3122801

                            Hi,

                             

                            Finally I made it work. The alert triggers only when the table name is "PO_DISTRIBUTIONS_ALL".

                             

                            THANK YOU SO MUCH FOR THE SOLUTION !!

                            • 11. Re: Email Notification Alert (Alert Requisition Approval)
                              Jagadekara

                              So,

                               

                              What exactly you did to work?

                              • 12. Re: Email Notification Alert (Alert Requisition Approval)
                                3122801

                                Yes, As you suggested I changed the driving query to  "PO_DISTRIBUTIONS_ALL" table and definitely it helped.

                                 

                                =========================================================

                                select

                                distinct  prh.segment1 requisition_number

                                    , nvl(prh.description, 'No Description') requisition_Description

                                    , prh.creation_date   requisition_creation_date

                                   ,  (select user_name from fnd_user where user_id =  prh.created_by) requisition_created_by   

                                    , prh.approved_date   requisition_approved_date

                                    , prh.authorization_status

                                    ,(select fnd.user_name from fnd_user fnd where fnd.user_id = prh.LAST_UPDATED_BY) requisition_approved_by

                                   , poh.segment1 po_number

                                   , (select user_name from fnd_user where user_id =  pod.created_by) po_created_by

                                   , (select user_name from fnd_user where user_id =  pod.LAST_UPDATED_BY) po_approved_by

                                   , pod.creation_date  po_created_date

                                   , poh.last_update_Date  po_approved_date

                                   , poh.authorization_status po_authorization_status

                                   ,('bpalani@cityofsalem.net') Email_Address

                                   ,pod.PO_HEADER_ID  

                                INTO &requisition_number, &requisition_Description, &requisition_creation_date, &requisition_created_by, &requisition_approved_date,

                                &authorization_status,  &requisition_approved_by, &po_number, &po_created_by, &po_approved_by, &po_created_date, &po_approved_date,

                                &po_authorization_status, &Email_Address, &po_header_id    

                                from

                                     po_distributions_all          pod

                                   , po_headers_all                poh

                                    , po_req_distributions_all     prd

                                   , po_requisition_lines_all      prl

                                   , po_requisition_headers_all    prh

                                where 1=1

                                  and pod.PO_HEADER_ID            =   poh.PO_HEADER_ID 

                                  and prd.distribution_id         =   pod.req_distribution_id

                                  and prl.requisition_line_id     =   prd.requisition_line_id

                                  and prh.requisition_header_id   =   prl.requisition_header_id

                                  and pod.req_distribution_id is not null    

                                  and prh.segment1   = (

                                                        select

                                                           DISTINCT FIRST_VALUE(prh.segment1) OVER (ORDER BY prh.segment1 DESC) req_nbr

                                                          from po_requisition_headers_all prh

                                                          where prh.created_by in ('6782')

                                                       )

                                  and pod.rowid    =  :rowid

                                ;

                                =========================================================


                                -- This alert is created for a specific user who requested it.

                                -- The query above needs more modifications to make it simple, but time being it works.

                                -- There was one more set up (Action Sets => Action Set Details => Define: Members) which I forgot to do, makes me feel that the alert is ALSO working because of that.

                                -- Time being the Alert fires 3 emails for every updates and Inserts... any idea why ??



                                P4.jpg

                                Thanks again,

                                Bala

                                • 13. Re: Email Notification Alert (Alert Requisition Approval)
                                  3122801

                                  - Time being the Alert fires 3 emails for every updates and Inserts... any idea why ??

                                   

                                  For duplicate alerts emails, I found that by clicking "Suppress Duplicates" at "Action Sets" tab worked.