E-mail Notification 1

[bradth]

What I would like to do is create an e-mail notification that would alert the salesperson that their sales order has shipped. I am currently using GP 7.5 and Outlook 2000. Basically, whenever a shipment for a sales order is made,
I would like to have an e-mail sent to the salesperson who entered it which contains the SO#, Customer, Items Shipped and Tracking numbers. Any idea how to go about doing this? I know there is a way to do it with SQL and creating a job, but I'm not sure how that works. Any examples would be much appreciated. Thanks

Brad

 

[Borvik]

Take a look at this thread - e-mailing in SQL is covered there.

http://www.tek-tips.com/viewthread.cfm?qid=953382

 

[jazgeek]

I created a view for this in sql and then I have a scheduled job to run at 5:30 every night that emails to all salesmen all sales orders shipped that day.

here's the view I use

CREATE VIEW SHIPCOMB
AS
SELECT SOPNUMBE AS INVOICE#, ORIGNUMB AS ORDER#, DOCDATE AS [DATE], ReqShipDate AS ESD, SALSTERR AS TERR, SLPRSNID AS SALESMAN
FROM dbo.SOP10100
WHERE (SOPTYPE = 3) AND (DOCDATE = CONVERT(SMALLDATETIME, CONVERT(CHAR(10), GETDATE(), 101)))

UNION

SELECT SOPNUMBE AS INVOICE#, ORIGNUMB AS ORDER#, DOCDATE AS DATE, ReqShipDate AS ESD, SALSTERR AS TERR, SLPRSNID AS SALESMAN
FROM dbo.SOP30200
WHERE (SOPTYPE = 3) AND (DOCDATE = CONVERT(SMALLDATETIME, CONVERT(CHAR(10), GETDATE(), 101))) AND (VOIDSTTS = 0)

You need to use a union of open and history in case the invoice is posted before the job runs.

I would recommend against putting a trigger on the sop table, it's pretty touchy.



-----------
and they wonder why they call it Great Pains!

jaz

 

[bradth]

That example looks like it'll work well. Just one question for the both of you, Have any of you used the Business Alerts in GP. I was also wondering if there was a way to do it with that. I have a alert made, but I would like it to display the SO number rather than just a user typed in message. Any ideas??? Thanks for the help.

Brad

 

[Borvik]

Nope - sorry.

 

[Dukester0122]

You could add a body on the email that Business Alerts send and pick the fields from the table in this case if you use SOP10100 or SOP30200 then pick SOPNUMBE.

 

[bradth]

Dukester0122,

How do you go about adding the body on the e-mail that will pick fields from one of the SOP tables? Because I want to write a SQL Query that will do this, but all it seems to want to do is send a message. Is there a way to get a query to send the results as the message, or for the message to display the results of a stored procedure? Thanks for the help.

Brad

 

[jymm]

here is how I do some selected alerts Brad - you should be able to find about xp_sendmail in the BOL since you can get pretty fancy



Declare @cmd varchar (600),
@ThisSubject varChar(100)

Set @cmd = 'Use database; select field1, field2, field3 From table order by field4'

Select @ThisSubject = '*** email about bad stuff happening '

Exec master.dbo.xp_sendmail
@recipients = 'jymm@beer.com',
@subject = @Thissubject,
@query = @cmd, @width=500

 

[bradth]

Thanks jymm, only a couple of problems. Here is my code:

DECLARE @CMD VARCHAR (500),
	@THESUBJECT VARCHAR (100)

SET @CMD = 'dbo.WAVE; SELECT BACHNUMB, MKDTOPST, GLPOSTDT FROM SY00500 ORDER BY BACHNUMB'

SELECT @THESUBJECT = '***SOPs POSTED***'

EXEC MASTER..xp_sendmail
@RECIPIENTS = 'Brad',
@SUBJECT = @THESUBJECT,
@QUERY = @CMD, @width = 500

I end up getting two errors from this:
1) Invalid object name 'SY00500'
2) Could not find stored procedure 'dbo.WAVE'

Any idea why I would be getting these errors? Thanks in advance for all the help.

Brad

 

[jymm]

gotta have the USE command --- USE <databasename> in the command string. Since when the exec xp_sendmail hits it is going against master if I remember right...

so try SET @CMD = 'use <put-database-name-here>;dbo.WAVE; SELECT BACHNUMB, MKDTOPST, GLPOSTDT FROM SY00500 ORDER BY BACHNUMB'

 

[bradth]

Thanks a lot jymm, that worked perfect. Now I just have to write a query for the select statement so the message will give all of the information that I need. All I need now is to place this into a job and away I go. For all your hard work, you get a star.

Brad

 

[jymm]

hey Brad - the problem with alot of stuff in the Select statement is that it will wrap in the email - gets ugly.

I have gone to writing this type of thing as a scheduled crystal report off of a view (that is just me of course) that is based on @@rowcount or similar. But with that I can combine the info that is not stored in Great Pains and distribute the report to the user that would benefit most from one page of the report than another (so the CFO gets one page, the Marketing person gets hers...). Then I just sit back and eat bon bons and nap all day - yeah right...

Since you already have Crystal (by having gp) it might help out... If you need help on the decollation or the auto run - let me know.

 

[bradth]

Ok, I have run into a problem now. Here is my code:

DECLARE @CMD VARCHAR (500),
	@THESUBJECT VARCHAR (100)

SET @CMD = 'USE WAVE; SELECT A.SOPNUMBE, A.BACHNUMB, A.CUSTNAME, A.SHIPMTHD, A.ACTLSHIP, B.ITEMNMBR, C.Tracking_Number, MKDTOPST
		FROM SOP10100 A INNER JOIN SOP10200 B ON A.SOPNUMBE = B.SOPNUMBE
		LEFT JOIN SOP10107 C ON A.SOPNUMBE = C.SOPNUMBE
		LEFT JOIN SY00500 D ON A.BACHNUMB = D.BACHNUMB
		WHERE MKDTOPST = 1 '

SELECT @THESUBJECT = '***SOPs POSTED***'

EXEC MASTER..xp_sendmail
@RECIPIENTS = 'Brad',
@SUBJECT = @THESUBJECT,
@QUERY = @CMD, @width = 500

I now get the error message: SQL Mail session is not started.

It happened after I pasted in the big SQL statement. Do you have any idea why I'd be getting this error message? As well any info on the auto run would be greatly appreciated. Thanks much again.

Brad

 

[jymm]

if you are the sql admin - sql mail can be started from within Enterprize manager (under 'support services' - right click on SQL mail and hit properties).

did you get the message when there was a short select statement? If so, increase the declare for @cmd.

you can schedule this sql through Enterprize mgr too, or you can have it in a .sql file and run that -- depends on which route you are going. SQL reporting I am going to point you to things you probably already have - Crystal I am going to have you make a small investment like I did 3 years ago.

 

[jymm]

one more thing - test the sql in query analyzer just to be sure that it works since sql mail will not interpret the stuff - it just submits it

 

[Dukester0122]

When you're creating the Alert Notification choose Message and Report then system would ask you to create the Report Columns.

 

[bradth]

Ya, I should've done more research into this before I posted. I realized that outlook wasn't set up on the one server so that is why I was getting the message. Ya I've worked with crystal a fair bit and kinda know most of the functionality of it. I would first just like to get a basic e-mail message sent with all the info I need for the time being. I appreciate the help much and hope you'll stick around for the next couple of days in case I run across any other problems. Thanks.

Brad

 

[bradth]

Dukester0122, I tried that and I posted above that I can't get any columns from the sales tables, therefore I can't get any of the information that I need from business alerts. That is why I am going through SQL rather than through GP. Thanks for the info though.

Brad

 

[jymm]

ya should not need outlook on the server - just the email account (on exchange - like mine is called sqlservice) and the mail settings in windoze.

to send the Crystal stuff -- you want the easy way (aka - you got $250?) or the Crystal/hard way?

//on soap box
Easy way is to buy Visual Cut from

http://www.milletsoftware.com/

-- essentially the program will allow you to burst and email via smtp - I put the reports into a batch file (some with SQL commands) and have them run from the server. For some reports I export the file to a PDF, some I export to odbc, some I fax to customers, some I break to specific department heads or store on the server... you get the idea. probably paid for itself about 100 times over in a VERY short time period. And the support from Ido is truly how support should be (good documentation and listens about enhancement ideas). ---
//off soap box

the hard way - Crystal/BA (much like when you do an asp web page for your intranet) actually gives you samples of how to do it on their disks... problem is that they do not tell you where they are (and I can not lay my hands on the disk right now). Surf into the cd and look at the examples. It will take you a while to do this - I got frustrated and spent the $250 about 3 years ago (then it was only $200).

let me know next step - j

 

[bradth]

I'm gonna try and create a job on sql server that will call a stored procedure that will update the user of all of the SO posted for the day. I've been slowly working and learning a lot all day, so I'll see what tomorrow brings. I'll keep you updated though and let you know how it turns out. Thanks for all the help.

Brad