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 ?

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                 

;

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

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.

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                 

;

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

Thanks in advance.

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 !!

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 ??

Thanks again,

Bala

- 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.