Sample 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

Have more questions? Submit a request

Comments