Outbound Queue Trigger

To enable the Outbound Transaction Queue functionality the below trigger must be implemented as shown below. This SQL file can be found in the [Drive]\TagNet Extension Framework\EF Web\custom-sql folder. Simply double-click the generic-outbound-transaction-trigger.sql file on the DB server (or where SSMS is installed) and execute this script. Once complete any Inventory transactions within core TagNet will populate the EF OutboundQueue table.

Note that this trigger can be modified to only queue certain inventory levels, item types, or other tag linked attributes. Click here for example.

generic-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 TR_RFDLOCIH_OutboundQueue ON XDEMOLIB.RFDLOCIH

INSTEAD OF INSERT ASh

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 @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;

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)

)

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);

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

);

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