Outbound Queue Trigger (Site specific)

Outbound Queue Trigger - Customized

This Trigger SQL is an example of site specific customization to accommodate the following features:

1)Only include Inventory transactions related to SAP Integration and as part of the Delayed Movement Rules (e.g. Pending)

2)For Returns processing using the Event Viewer

3)Preventing tag bounce between RFID enabled trailers

customized-outbound-transaction-trigger.sql

use TagNet;

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[XDEMOLIB].[TR_RFDLOCIH_OutboundQueue]'))

DROP TRIGGER [XDEMOLIB].[TR_RFDLOCIH_OutboundQueue]

CREATE TRIGGER [XDEMOLIB].[TR_RFDLOCIH_OutboundQueue] ON [XDEMOLIB].[RFDLOCIH]

INSTEAD OF INSERT AS

BEGIN

DECLARE @RFDHSTDAT CHAR(8);

DECLARE @RFDHSTTME CHAR(6);

DECLARE @RFDHSTUSR CHAR(10);

DECLARE @RFDHSTACT CHAR(10);

DECLARE @RFDHSTFLG CHAR(1);

DECLARE @RFDTAGUID CHAR(30);

DECLARE @RFDBINID CHAR(20);

DECLARE @RFDITEMID CHAR(20);

DECLARE @RFDINFGLN CHAR(13);

DECLARE @RFDTARCTY CHAR(30);

DECLARE @RFDUSRENV CHAR(15);

DECLARE @RFDLOCQTY DECIMAL(7,2);

DECLARE @RFDLVL1ID CHAR(10);

DECLARE @RFDLVL2ID CHAR(10);

DECLARE @RFDLVL3ID CHAR(10);

DECLARE @RFDLVL4ID CHAR(10);

DECLARE @RFDLVL5ID CHAR(10);

DECLARE @RFDLOCLOT CHAR(20);

DECLARE @RFDLTCODE CHAR(1);

DECLARE @RFDHSTREF CHAR(50);

DECLARE @X_UPID DECIMAL(7,2);

DECLARE @X_RRNO DECIMAL(15,0);

SELECT @RFDHSTDAT = i.RFDHSTDAT FROM inserted i;

SELECT @RFDHSTTME = i.RFDHSTTME FROM inserted i;

SELECT @RFDHSTUSR = i.RFDHSTUSR FROM inserted i;

SELECT @RFDHSTACT = i.RFDHSTACT FROM inserted i;

SELECT @RFDHSTFLG = i.RFDHSTFLG FROM inserted i;

SELECT @RFDTAGUID = i.RFDTAGUID FROM inserted i;

SELECT @RFDBINID = i.RFDBINID FROM inserted i;

SELECT @RFDITEMID = i.RFDITEMID FROM inserted i;

SELECT @RFDINFGLN = i.RFDINFGLN FROM inserted i;

SELECT @RFDTARCTY = i.RFDTARCTY FROM inserted i;

SELECT @RFDUSRENV = i.RFDUSRENV FROM inserted i;

SELECT @RFDLOCQTY = i.RFDLOCQTY FROM inserted i;

SELECT @RFDLVL1ID = i.RFDLVL1ID FROM inserted i;

SELECT @RFDLVL2ID = i.RFDLVL2ID FROM inserted i;

SELECT @RFDLVL3ID = i.RFDLVL3ID FROM inserted i;

SELECT @RFDLVL4ID = i.RFDLVL4ID FROM inserted i;

SELECT @RFDLVL5ID = i.RFDLVL5ID FROM inserted i;

SELECT @RFDLOCLOT = i.RFDLOCLOT FROM inserted i;

SELECT @RFDLTCODE = i.RFDLTCODE FROM inserted i;

SELECT @RFDHSTREF = i.RFDHSTREF FROM inserted i;

SELECT @X_UPID = i.X_UPID FROM inserted i;

SELECT @X_RRNO = i.X_RRNO FROM inserted i;

-- Only touch the queue on positive transactions

IF (@RFDLOCQTY > 0)

BEGIN

-- Declare your variables

DECLARE @SourceLevel1 CHAR(10);

DECLARE @SourceLevel2 CHAR(10);

DECLARE @SourceLevel3 CHAR(10);

DECLARE @SourceLevel4 CHAR(10);

DECLARE @SourceLevel5 CHAR(10);

DECLARE @TargetLevel1 CHAR(10);

DECLARE @TargetLevel2 CHAR(10);

DECLARE @TargetLevel3 CHAR(10);

DECLARE @TargetLevel4 CHAR(10);

DECLARE @TargetLevel5 CHAR(10);

DECLARE @TransactionTime DATETIME;

DECLARE @InsertToQueue BIT;

SET @InsertToQueue = 0

-- Get the transaction time as real DATETIME

SET @TransactionTime = CONVERT

(

DATETIME,

SUBSTRING(@RFDHSTDAT, 1, 4) + '-' +

SUBSTRING(@RFDHSTDAT, 5, 2) + '-' +

SUBSTRING(@RFDHSTDAT, 7, 2) + ' ' +

SUBSTRING(@RFDHSTTME, 1, 2) + ':' +

SUBSTRING(@RFDHSTTME, 3, 2) + ':' +

SUBSTRING(@RFDHSTTME, 5, 2)

)

-- Find the last (source) location of the tag

SELECT

@SourceLevel1 = RTRIM(ref.Level1),

@SourceLevel2 = RTRIM(ref.Level2),

@SourceLevel3 = RTRIM(ref.Level3),

@SourceLevel4 = RTRIM(ref.Level4),

@SourceLevel5 = RTRIM(ref.Level5)

FROM EF.OutboundLocationReference ref WHERE TagID = @RFDTAGUID;

SET @TargetLevel1 = RTRIM(@RFDLVL1ID);

SET @TargetLevel2 = RTRIM(@RFDLVL2ID);

SET @TargetLevel3 = RTRIM(@RFDLVL3ID);

SET @TargetLevel4 = RTRIM(@RFDLVL4ID);

SET @TargetLevel5 = RTRIM(@RFDLVL5ID);

-- This indicates a 301 movement

IF (@SourceLevel2 = '0956' AND @TargetLevel2 != '0956' AND @TargetLevel3 LIKE 'TRAILER%')

BEGIN

SET @InsertToQueue = 1;

END

-- This indicates a 291 Issue movement

IF (@SourceLevel3 = 'PENDING' AND @TargetLevel3 = 'ISSUED')

BEGIN

SET @InsertToQueue = 1;

END

-- This indicates a 292 Return movement

IF (@SourceLevel3 = 'ISSUED' AND @TargetLevel3 LIKE 'TRAILER%')

BEGIN

SET @InsertToQueue = 1;

END

-- This indicates a return to the PMK

IF (@TargetLevel3 = 'PMK_WHSE' OR @TargetLevel3 = 'RTN_ISSUE')

BEGIN

SET @InsertToQueue = 1;

END

-- If this is bounce transaction, flag the transaction so it can be

-- excluded from the business side views

IF (@SourceLevel3 = 'PENDING' AND @TargetLevel3 LIKE 'TRAILER%')

BEGIN

SET @RFDHSTFLG = 'X';

END

-- Add to the Outbound Queue if the condition has been met

IF (@InsertToQueue = 1)

BEGIN

INSERT INTO EF.OutboundTransactionQueue VALUES

(

@X_RRNO,

RTRIM(@RFDTAGUID),

RTRIM(@RFDITEMID),

@RFDLOCQTY,

RTRIM(@RFDHSTACT),

@SourceLevel1,

@SourceLevel2,

@SourceLevel3,

@SourceLevel4,

@SourceLevel5,

@TargetLevel1,

@TargetLevel2,

@TargetLevel3,

@TargetLevel4,

@TargetLevel5,

@TransactionTime,

RTRIM(@RFDBINID),

RTRIM(@RFDHSTUSR),

RTRIM(@RFDLOCLOT),

@RFDLTCODE,

RTRIM(@RFDHSTREF),

NULL, -- Reason Coe

NULL, -- Rule Name

NULL, -- Processed Time

0, -- Status (NotProcessed)

NULL -- Result Message

);

END

UPDATE EF.OutboundLocationReference

SET

Level1 = @TargetLevel1,

Level2 = @TargetLevel2,

Level3 = @TargetLevel3,

Level4 = @TargetLevel4,

Level5 = @TargetLevel5,

UpdateTime = @TransactionTime

WHERE TagID = @RFDTAGUID;

IF @@ROWCOUNT = 0

INSERT INTO EF.OutboundLocationReference VALUES

(

@RFDTAGUID,

@TargetLevel1,

@TargetLevel2,

@TargetLevel3,

@TargetLevel4,

@TargetLevel5,

@TransactionTime

);

END

INSERT INTO XDEMOLIB.RFDLOCIH

VALUES

(

@RFDHSTDAT,

@RFDHSTTME,

@RFDHSTUSR,

@RFDHSTACT,

@RFDHSTFLG,

@RFDTAGUID,

@RFDBINID,

@RFDITEMID,

@RFDINFGLN,

@RFDTARCTY,

@RFDUSRENV,

@RFDLOCQTY,

@RFDLVL1ID,

@RFDLVL2ID,

@RFDLVL3ID,

@RFDLVL4ID,

@RFDLVL5ID,

@RFDLOCLOT,

@RFDLTCODE,

@RFDHSTREF,

@X_UPID,

@X_RRNO

)

END