Sample post submit stored procedure to create email when serial numbers received

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

Was this helpful?