Oracle E Business Suite - Email Output?

Hi ,
we are in the process of implementing Oracle 11i...

Our consultants tell us that we can automate the sending of emails from PO system..but......

If we want ( for example ) to email a Sales Order Acknowledgement to a customer when created or a shipment email when we've shipped, it looks like we'll need a 3rd party app or some development..

Has anyone any pointers/comments?

Stephen
LVL 1
stephengriffinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
There will be some coding necessary.  I'm not an Oracle Apps person so there might be a way built into the app suite.  

From a strict database method:  One of the experts here ( schwertner) has a great example on sending email from an Oracle DB.  Posted below is an example of a stored procedure that will do this (I believe it is the one he usually posts).

You can then create a trigger off the base tables to send the appropriate email.

A word of caution:
This procedure is fire and forget.  As long as it hands it off, it thinks it went.  I can't think of a way to verify that it acutally left the office (i.e./ the email server botched the send).


email stored procedure
-----------------------------------

 CREATE OR REPLACE PROCEDURE ORACLE_SEND_MAIL
/* execute oracle_Send_mail ('MTLS24', 'pcote@toto.com','' ,'' , 'DW', 'Tbls: 90% eapps, 125/1234');  */

(sender IN VARCHAR2,
recipient IN VARCHAR2,
cc_recipient IN VARCHAR2,
bcc_recipient IN VARCHAR2,
subj IN VARCHAR2,
body IN VARCHAR2) AS

crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2(4000);
mail_conn UTL_SMTP.CONNECTION;

BEGIN
/* PUT HERE THE SMTP SERVER OF YOUR COMPANY */
mail_conn := utl_smtp.open_connection('smtp.b2mtls.ca',25);

utl_smtp.helo(mail_conn, 'smtp.b2mtls.ca');
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.rcpt(mail_conn, cc_recipient);
utl_smtp.rcpt(mail_conn, bcc_recipient);

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||

'From: ' || sender || crlf ||
'To: ' || recipient || crlf ||
'Cc: ' || cc_recipient || crlf ||
'Bcc: ' || bcc_recipient || crlf ||
'Subject: ' || subj || crlf;
mesg:= mesg || '' || crlf || body;

utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);

END;
/
DrSQLCommented:
stephengriffin and slightwv,
   Actually, triggers on the 11i tables are not a good idea.  It would be pretty difficult to figure out WHICH ones to put the trigger on, anyway.  Yes, it will require development, but it is a fairly simple application.  I would suggest you build an application that, once every 15 minutes or so, "wakes up", checks to see if there's any work to do and then does the work (send the email).  Most of the utl_smtp stuff you'd need is above.  you'd also need to query the tables and find out what orders had been created or orders had been shipped in the last 15 minutes.  I'd suggest you also create a table that would be a record of all the emails yousend out, so the routine doesn't send out multiple emails.  I don't have the 11i ERD in front of me, so I can't help with the queries, except to offer the suggestion that you restrict it to shipments/orders based on the "last-updated" of that is > sysdate - 1/48 (anything newer than a half-hour).  That way, if the app sleeps too long, it can catch up, but if it's too old to make any difference due to system downtime, it won't clog up the processing.

Good luck!
DrSQL
kamal_therockyCommented:
Hi stephengriffin,

There is way to send mail in Oracle Apps 11i using Oracle Workflow Engine. you can create subscriptions based on a Business event like When a Sales Order is Created or Updated to Ship. So you need to write a workflow or else a PL/SQL Package to send a mail.

So these mails will be sent automatically when the event is raised by the applications.

Regards
kamal
stephengriffinAuthor Commented:
Thanks all for input so far!

If I could help it at all I would prefer to use some form of workflow as Im not a PL/SQL dev.. in fact Im brand new to Oracle...

The procedure above makes perfect sense.. But Im not sure how I would send contents of a Sales Ack or Shipment Notifacation...

Im also looking at XML Publisher for the actual output.....

Can any of you guys/gals give me further info on either how to do this in Workflow or more info on how to develop  an app?

Cheers!
kamal_therockyCommented:
Hi stephengriffin,

If you are planning to use Workflow to send Shipment Notifications. Then Create a Custom Workflow Process and Create a Notification Item in the Workflow with the Template of your Shipment Notification or Acknowledge and keep the dynamic items as Workflow attributes so that you can replace them at runtime.

i think there will be some standard workflow in the Apps which does such type of thing. you can open them and use those workflow as a Prototype to develop your Process flow

Regards
kamal

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.