-
1. Re: Email Notification Alert (Alert Requisition Approval)
Jagadekara Mar 1, 2016 5:46 AM (in response to 3122801)1 person found this helpfulHi,
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..
-
2. Re: Email Notification Alert (Alert Requisition Approval)
3122801 Mar 1, 2016 4:00 PM (in response to Jagadekara)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 Mar 2, 2016 10:30 AM (in response to 3122801)1 person found this helpfulHi,
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..
-
4. Re: Email Notification Alert (Alert Requisition Approval)
3122801 Mar 8, 2016 9:40 PM (in response to Jagadekara)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 Mar 9, 2016 7:39 AM (in response to 3122801)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 Mar 9, 2016 11:39 PM (in response to Jagadekara)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 Mar 10, 2016 6:14 AM (in response to 3122801)Hi,
Can you provide all screen shots of your alert?
-
8. Re: Email Notification Alert (Alert Requisition Approval)
3122801 Mar 10, 2016 5:03 PM (in response to Jagadekara)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.
-
9. Re: Email Notification Alert (Alert Requisition Approval)
Jagadekara Mar 11, 2016 7:51 AM (in response to 3122801)1 person found this helpfulHi,
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.
-
10. Re: Email Notification Alert (Alert Requisition Approval)
3122801 Mar 21, 2016 10:33 PM (in response to Jagadekara)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 Mar 22, 2016 6:33 AM (in response to 3122801)So,
What exactly you did to work?
-
12. Re: Email Notification Alert (Alert Requisition Approval)
3122801 Mar 22, 2016 2:58 PM (in response to Jagadekara)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
-
13. Re: Email Notification Alert (Alert Requisition Approval)
3122801 Mar 22, 2016 10:17 PM (in response to 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.