Navigation: TagNet Extension Framework (EF) > Outbound Transactions >

Outbound Queue Trigger

Still need help? Create a Support Ticket with Stratum Support

Send comments on this topic.

← Previous Next →

 

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 C:\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]

GO

 

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


Copyright © 2024 Stratum Global, Inc.