This example stored procedure sends an email notification when serial numbers are received on a purchase order.

CREATE PROCEDURE Panatrack_PostTrxReceivingNotify
  @TrxOid UNIQUEIDENTIFIER
AS

DECLARE @message AS VARCHAR(MAX)
DECLARE @Start_SourceLineNumber BIGINT
DECLARE @End_SourceLineNumber BIGINT
DECLARE @CC_Email NVARCHAR(50)

SELECT @Start_SourceLineNumber = MIN(SourceLineNumber),@End_SourceLineNumber = MAX(SourceLineNumber)
FROM PanatrackerGP6_TrxReceivingUnit with(nolock)
WHERE TrxReceivingOid = @TrxOid

--PRINT @Start_SourceLineNumber
--PRINT @End_SourceLineNumber

-- PO Receipt Header
SELECT @message = N'Purchase receipt: ' + R.TransactionCode + ' for Vendor: ' + VENDNAME + CHAR(13)+CHAR(10)
FROM PM00200 V with(nolock)
INNER JOIN PanatrackerGP6_TrxReceiving R with(nolock) ON V.VENDORID = R.VendorCode
WHERE R.Oid = @TrxOid

-- Loop over each receipt line item
WHILE @Start_SourceLineNumber <= @End_SourceLineNumber
    BEGIN
       SELECT @message = @message + 'Item Number: ' + ItemCode + ' - Qty: ' + CAST(ReceivedQuantity AS NVARCHAR(10)) + '      ' + ItemDescription + CHAR(13)+CHAR(10)
       FROM PanatrackerGP6_TrxReceivingUnit with(nolock)
       WHERE TrxReceivingOid = @TrxOid AND SourceLineNumber = @Start_SourceLineNumber

       SET @Start_SourceLineNumber = @Start_SourceLineNumber + 16384
       END

--PRINT @message

IF EXISTS (SELECT * FROM PanatrackerGP6_TrxReceivingUnit with(nolock) WHERE TrxReceivingOid = @TrxOid AND TrackingOption = 2)
BEGIN
SET @CC_Email = 'serials@mycompany.com'
END

IF @message IS NOT NULL
    BEGIN
      SELECT @message

      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'EMAIL1',
            @recipients = 'user1@mycompany.com; user2@mycompany.com',
            @copy_recipients = @CC_Email,
            @subject = 'PO Receipt',
            @body = @message;
    END
GO

GRANT EXEC ON Panatrack_PostTrxReceivingNotify TO DYNGRP
GO