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