Special Order Parts Business Alert Example

We are having a great time here at Synergy 2016!

Here is the example from the Workflow session we did yesterday.

This is a trigger based business alert that is designed to email the Technician and a dispatch mail group whenever a part is received that is associated with a Service Call.

Here is the stored procedure that constructs and sends the email:

CREATE PROCEDURE dbo.cssp_SpecialOrderPartsReceipt (@ITEMNMBR VARCHAR(31),@ITEMDESC VARCHAR(101), @Service_Call_ID VARCHAR(17))
AS
/*

This is an example for educational purposes. You should test all code for its intended use before placing it in production. No warranty is implied. It you find any issues with the code, please post in comments and I will endeavor to correct it.
20161025 djedziniak initial version
*/
BEGIN
SET NOCOUNT ON
DECLARE @Technician VARCHAR(11),@CUSTNMBR VARCHAR(15),@ADRSCODE VARCHAR(15),@TECHEMAIL VARCHAR(41),@emails VARCHAR(max),@THISbody VARCHAR(max)

SELECT @Technician = Technician,@CUSTNMBR = CUSTNMBR,@ADRSCODE = ADRSCODE
FROM dbo.SV00300
WHERE Service_Call_ID = @Service_Call_ID

SELECT @TECHEMAIL = rtrim(SV_Pager_PIN)
FROM dbo.SV00115
WHERE Technician = @Technician

SELECT @emails = @TECHEMAIL + ';dispatch@key2act.com'
,@THISbody = 'Item: ' + rtrim(@ITEMNMBR) + ' ' + rtrim(@ITEMDESC) +
' Customer: ' + rtrim(@CUSTNMBR) + ' Address: ' + rtrim(@ADRSCODE)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Business Alert'
,@recipients = @emails
,@body = @THISbody
,@subject = 'Special Order Part Received';
END

Here is the trigger that calls the proc.

CREATE TRIGGER dbo.cstr_POP10310_INSERT ON dbo.POP10310 AFTER INSERT
AS
/*
This is an example for educational purposes. You should test all code for its intended use before placing it in production. No warranty is implied. It you find any issues with the code, please post in comments and I will endeavor to correct it.
20161025 djedziniak initial version
*/
BEGIN
SET NOCOUNT ON;
DECLARE @JOBNUMBR VARCHAR(17),@ITEMNMBR VARCHAR(15), @ITEMDESC VARCHAR(101), @QTY NUMERIC(19,5)

DECLARE C_INSERTED CURSOR FOR
SELECT JOBNUMBR,ITEMNMBR,ITEMDESC
FROM inserted
WHERE RTRIM(JOBNUMBR)!=''
OPEN C_INSERTED
FETCH NEXT FROM C_INSERTED INTO @JOBNUMBR,@ITEMNMBR,@ITEMDESC
WHILE @@FETCH_STATUS=0
BEGIN
EXEC dbo.cssp_SpecialOrderPartsReceipt @ITEMNMBR,@ITEMDESC,@JOBNUMBR
FETCH NEXT FROM C_INSERTED INTO @JOBNUMBR,@ITEMNMBR,@ITEMDESC
END
CLOSE C_INSERTED
DEALLOCATE C_INSERTED
END

You will have to configure DB mail on your SQL Server in order for the send mail part to work.  However, you could replace that call with some other action, like writing a log record or calling an eConnect proc.

Enjoy!

script to auto roll calls forward in signature

DECLARE @ROLLDATE DATETIME,
@STATUS INT
SELECT @ROLLDATE=CONVERT(VARCHAR,GETDATE(),102)

EXEC SMS_Auto_Call_Roll_Forward @ROLLDATE,'sa',@STATUS OUT
go