SOP email notification? 1

[clicker666]

I can't imagine I'm the only person who ever needed this. I've tried to create a business alert that would notify a user when an order is saved. Unfortunately the best I could come up with was one that every minute sent all the open orders. Does anyone have the definition for this business alert lying around?

TIA

 

[stef315]

I did something like this in POP. I put a trigger on the PO Approval table to send the buyer an email when their PO is approved.

So, you'd probably need to right a trigger that fires when the status changes.

 

[clicker666]

That sounds pretty cool. I'll have to figure out how to get the SOP trigger into my SQL server... time to crack open some SQL books.

 

[stef315]

Here's an example I used:

CREATE TRIGGER [PO_Approved] ON [dbo].[POA40003]
FOR INSERT, UPDATE, DELETE
AS

SET NoCount ON
DECLARE @buyerid nvarchar(30), @query nvarchar(100)

IF update(POA_PO_Approval_Status)
begin
SELECT @query= POA_PO_Approval_Status FROM Inserted
if @query = '2'
begin
select @buyerid=(rtrim(POP10100.buyerid) + '@hotmail.com') from POP10100 LEFT OUTER JOIN Inserted ON POP10100.PONUMBER = Inserted.PONUMBER
SELECT @query=('Your ' + rtrim(Inserted.PONUMBER) +' has now been approved.') FROM Inserted
EXEC master..xp_sendmail @buyerid, @query
end
end

 

[clicker666]

I'm going to poke around the SOP tables and see if I can find a unique identifier when a record is added to search for.

Hopefully between your code and other trigger tutorials I can rig something up that will send the alert.

 

[clicker666]

So close... but one teensie problem.

CREATE TRIGGER NEW_SOP
ON SOP10100
FOR INSERT

AS

SET NOCOUNT ON
DECLARE

@r AS int,
@SOPNUMBE varchar(21),
@SUBJECTTEXT as varchar(50),
@ATTACHMENTNAME as varchar(50),

SET @SOPNUMBE=(SELECT SOPNUMBE FROM Inserted)
SET @MESSAGETEXT= 'SOP Number: ' + @SOPNUMBE + 'was just created.'
SET @SUBJECTTEXT = 'Order Notification: SOP ' + @SOPNUMBE
SET @ATTACHMENTNAME = rtrim('sop' + @SOPNUMBE) + '.csv'

EXEC @r = master..xp_sendmail @recipients='myexhangeemailaddress',
@message=@MESSAGETEXT,
@query='SELECT TOP 1 * FROM SOP10100 ORDER BY DEX_ROW_ID DESC',
@subject=@SUBJECTTEXT,
@attachments=@ATTACHMENTNAME,
@attach_results='TRUE',
@width=8000,
@separator=',',
@dbuse = 'test'

The only problem is the trigger occurs at the beginning of the record creation, not the end. The attachment then comes in blank. Any ideas on what I am missing here? I'm sure it's something simple, but it is eluding me at the moment.

 

[clicker666]

I created an UPDATE trigger instead. That fired the trigger AFTER the order was completed.

Here’s the code:

CREATE TRIGGER NEW_SOP
ON SOP10100
FOR UPDATE

AS

SET NOCOUNT ON
DECLARE

@r AS int,
@SOPNUMBE varchar(21),
@SUBJECTTEXT as varchar(50),
@MESSAGETEXT as varchar(100),
@ATTACHMENTNAME as varchar(50)

SET @SOPNUMBE=(SELECT SOPNUMBE FROM Inserted)
SET @MESSAGETEXT= 'SOP Number: ' + rtrim(@SOPNUMBE) + ' was just created.'
SET @SUBJECTTEXT = 'Order Notification: SOP ' + rtrim(@SOPNUMBE) + '.'
SET @ATTACHMENTNAME = rtrim('SOP' + @SOPNUMBE) + '.CSV'

EXEC @r = master..xp_sendmail @recipients='myexchangemailaddress',
@message=@MESSAGETEXT,
@query='SELECT TOP 1 * FROM SOP10100 ORDER BY DEX_ROW_ID DESC',
@subject=@SUBJECTTEXT,
@attachments=@ATTACHMENTNAME,
@attach_results='TRUE',
@width=8000,
@separator=',',
@dbuse = 'test'

It’s not terribly fancy at this point. I decided to keep it simple at first, although I could add things like record details by querying SOP10200 for records matching SOPNUMBE whatever. I just couldn’t figure out how to string the records into an email body, using nothing but SQL. So it sends an email to me, and has a CSV containing the record details. The important part is the notification of an SOP number, at least I think so.

Do you see any negatives to this? Are there any other actions that could be performed that would cause this email to launch in error?

 

[Borvik]

Well, I'm not sure about any negatives - but I do have an example for you about the SOP10200 details inclusion:

What I've done is create a temporary table with the fields that I will need, plus an identifier field:

create table temp_OrderItems (
               my_id int IDENTITY (1, 1),
               ITEMNMBR char(30),
               QUANTITY numeric(19,5),
               ITEMDESC char(51),
               UNITPRCE numeric(19,5),
               XTNDPRCE numeric(19,5))

Then insert all the records you will need (where @sopnumbe is the variable containing your SOPNUMBE):

INSERT temp_OrderItems(ITEMNMBR,QUANTITY,ITEMDESC,UNITPRCE,XTNDPRCE) SELECT ITEMNMBR, QUANTITY, ITEMDESC, UNITPRCE, XTNDPRCE FROM SOP10200 WHERE SOPNUMBE=@sopnumbe

I then created a temporary table so that I could carry the changed variables in a while loop back out of the while loop and inserted a blank record:

create table temp_Details (
               BODY varchar(500))
INSERT INTO temp_Details(BODY) VALUES(' ')

Now I'm using HTML tables during the generation, but you can modify that.

/* Start the table */
SET @tmpBody = '<table align="center"><tr><th>Item #</th><th>Qty</th><th>Description</th><th>Price</th><th>Ext. Price</th></tr>' + CHAR(13) + CHAR(10)

/* From now on only grab 1 record when using SELECT statements */
SET rowcount 1

/* While there is still something in our temp Item table continue */
WHILE exists(SELECT * FROM temp_OrderItems)
BEGIN

  /* Get our values
  SELECT @id=my_id, @itemnum=rtrim(ITEMNMBR), @qty=CAST(QUANTITY AS numeric(19,0)), @desc=rtrim(ITEMDESC), @price=CAST(UNITPRCE AS numeric(19,2)), @xprice=CAST(XTNDPRCE AS numeric(19,2)) FROM temp_OrderItems

  /* Build them into our table adding on each time */
  SET @tmpBody = @tmpBody + '<tr align="center">' +
            '<td>' + @itemnum + '</td>' +
            '<td>' + @qty + '</td>' +
            '<td align="left">' + @desc + '</td>' +
            '<td>$' + @price + '</td>' +
            '<td align="right">$' + @xprice + '</td>' +
            '</tr>' + CHAR(13) + CHAR(10)

  /* Delete the record from the temporary table */
  DELETE FROM temp_OrderItems WHERE my_id=@id

  /* Update our other temporary table to carry our changes in here out with us */
  UPDATE temp_Details SET BODY=@tmpBody
END

/* Grab table of items and do what we need with it */
SELECT @tmpBody=BODY FROM temp_Details

Hope that helps with the details part of it.

 

[clicker666]

Very nice!

I haven't tried it yet, but am assuming that would fall in between my SET statements and EXEC? Second, would I use the SELECT statement at the very end of your example to create my messagetext?

(I'm going to play with those ideas and see what happens, and appreciate the code!)

 

[Borvik]

No problem - glad to help.

Yes, you are correct in assuming that it would fall in between your SET statements and EXEC.

And yes, you would need the SELECT statement at the end just to be able to pull the information you build during the while loop, because of the scope of the variables within the loop doesn't allow the changes to leave. So just append what you grab from the last SELECT statement to the rest of the email body.

Although of note, xp_sendmail does not send html email - so if you send the html tags the tags will be visible in the e-mail.

If you want to send HTML e-mails take a look at the following site:

http://www.sqldev.net/xp/xpsmtp.htm

Cheers!

 

[jazgeek]

I'm very pleased with what you've been doing here. I've been looking for something like this for a while.

how would the statement look if I was trying to run off the SOP process holds table?

eg. a process hold is removed, an email is fired off



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

jaz

 

[Borvik]

Well, you would need a trigger on SOP10104 on DELETE to capture that.

CREATE TRIGGER tr_EmailHoldsDeleted
ON SOP10104
FOR DELETE
AS

/* Declare your variable */
DECLARE @sopnumbe nvarchar(50)

/* Get your SOP Number */
SELECT @sopnumber=SOPNUMBE FROM deleted

After you have your SOP Number you can follow the other examples to grab the details of the order and/or to email your message.

 

[clicker666]

That was my next project. I'm thinking some IF statements similar to Stef's at the top of the page. I'm having some issues with the code here butting heads with some other customizations for process locks. Duplicate record issues and the like.

I haven't delved too deeply, but I'm thinking there might be some other temp tables, might be a good idea to rename some of my temp tables and other variables to prevent any oddities.

 

[Borvik]

Well, at least I think the trigger would be on SOP10104 FOR DELETE. I'm not sure how the process holds work, but I know that is the table - and a trigger similar to above would catch on a delete from that table. So...

 

[jazgeek]

no, unfortunately a delete won't work. the hold release is a change of the field DELETE in the SOP10104 table from 0 to 1

Update would work, but how would you be selective on it?



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

jaz

 

[Borvik]

Well, then you would have to make in on UPDATE instead and check the inserted value to make sure it is what you need.

CREATE TRIGGER tr_EmailHoldsDeleted
ON SOP10104
FOR UPDATE
AS

/* Declare your variable */
DECLARE @sopnumbe nvarchar(50)
DECLARE @value tinyint /* Or whatever type the field is

/* Get your SOP Number, and Deleted Value */
SELECT @sopnumber=SOPNUMBE, @value=DELETE1 FROM Inserted

IF @value=1
  BEGIN
    /* REST OF CODE HERE */
  END

 

[jazgeek]

ok, I'm a bit out of my element now, what have I done wrong?

this creates a

A save operation on table 'SOP_Process_Holds_WORK_HIST' failed accessign SQL data.

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

create trigger PURCH_REL
on SOP10104
for update

as
declare @r as int
declare @JOB varchar(21)
declare @VALUE tinyint
declare @SOPNUMBE varchar(21)
declare @SUBJECTTEXT as varchar(50)
declare @MESSAGETEXT as varchar(100)
declare @ATTACHMENTNAME as varchar(50)

SET @SOPNUMBE=(SELECT SOPNUMBE FROM Inserted)
SET @MESSAGETEXT= 'SOP Number: ' + rtrim(@SOPNUMBE) + ' was just removed.'
SET @SUBJECTTEXT = 'Order Notification: SOP ' + rtrim(@SOPNUMBE) + '.'
SET @ATTACHMENTNAME = rtrim('SOP' + @SOPNUMBE) + '.CSV'

select @JOB = SOPNUMBE,@VALUE = DELETE1 from Inserted

if @VALUE = 1
Begin

exec @r = master..xp_sendmail
@receipient = 'myemail',
@message = @MESSAGETEXT,
@query = 'SELECT TOP 1 * FROM SOP10104 ORDER BY DEX_ROW_ID',
@DBUSE = 'mydatabase'

END


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

jaz

 

[Borvik]

Your problem might be in this line:

declare @MESSAGETEXT as varchar(100)
declare @ATTACHMENTNAME as varchar(50)

/* Problem HERE */
SET @SOPNUMBE=(SELECT SOPNUMBE FROM Inserted)
SET @MESSAGETEXT= 'SOP Number: ' + rtrim(@SOPNUMBE) + ' was just removed.'
SET @SUBJECTTEXT = 'Order Notification: SOP ' + rtrim(@SOPNUMBE) + '.'
SET @ATTACHMENTNAME = rtrim('SOP' + @SOPNUMBE) + '.CSV'

/* you already have the SOPNUMBE you don't need to grab it here */
select @JOB = SOPNUMBE,@VALUE = DELETE1 from Inserted

if @VALUE = 1


To assign a variable using a select statement it should read:

SELECT @SOPNUMBE=SOPNUMBE FROM Inserted

So try this to replace the section of code above:

declare @MESSAGETEXT as varchar(100)
declare @ATTACHMENTNAME as varchar(50)

SELECT @SOPNUMBE=SOPNUMBE FROM Inserted
SET @MESSAGETEXT= 'SOP Number: ' + rtrim(@SOPNUMBE) + ' was just removed.'
SET @SUBJECTTEXT = 'Order Notification: SOP ' + rtrim(@SOPNUMBE) + '.'
SET @ATTACHMENTNAME = rtrim('SOP' + @SOPNUMBE) + '.CSV'

select @VALUE = DELETE1 from Inserted

if @VALUE = 1

If that still doesn't work - I'll take a closer look at it when I have a database in front of me to work with.

 

[jazgeek]

sorry, it's still giving the same sql error when a hold is removed from within GreatPlains

"A save operation on table 'SOP_Process_Holds_WORK_HIST' failed accessign SQL data."


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

jaz

 

[Borvik]

I just created and tried (using GP) this trigger

CREATE TRIGGER PURCH_REL
ON SOP10104 
FOR UPDATE
AS

DECLARE @r as int
DECLARE @JOB varchar(21)
DECLARE @VALUE tinyint
DECLARE @SOPNUMBE varchar(21)
DECLARE @SUBJECTTEXT as varchar(50)
DECLARE @MESSAGETEXT as varchar(100)
DECLARE @ATTACHMENTNAME as varchar(50)

SELECT @SOPNUMBE=SOPNUMBE FROM Inserted
SET @MESSAGETEXT= 'SOP Number: ' + rtrim(@SOPNUMBE) + ' was just removed.'
SET @SUBJECTTEXT = 'Order Notification: SOP ' + rtrim(@SOPNUMBE) + '.'
SET @ATTACHMENTNAME = rtrim('SOP' + @SOPNUMBE) + '.CSV'

SELECT @VALUE = DELETE1 from Inserted

IF @VALUE = 1
BEGIN
  EXEC @r = master.dbo.xp_smtp_sendmail
       @FROM = N'mymailer@mydomain.com',
       @TO = N'recipient@mydomain.com',
       @subject = @SUBJECTTEXT,
       @message = @MESSAGETEXT,
       @type = N'text/html',
       @server = N'myservername',
       @dumpmsg = N'C:\MyEmailLog1.log'
END

Greate Plains never threw an error at me, and I received the e-mail. You may be getting the error from xp_sendmail. Try using xp_smtp_sendmail - you'll need to download and install it though. You can find information on that here:

http://www.sqldev.net/xp/xpsmtp.htm