IMOVE Queue

This queue manages the synchronization of asset department ownership (assigned) to the TagNet inventory module to keep the two systems aligned. The 'assigned' department is imported along with other asset data into TagNet's Asset Master from a 3rd party Metrology system (such as BMRAM). There is a trigger on the Asset UDA table (RFDASSEX) that monitors any changes to the 'DEPT' UDA and inserts a request into the IMOVE queue table (EF.InventoryMovementQueue). The queue request starts as 'waiting' status (see below) and is processed by the ImoveQueue job that attempts to update the Asset Tag's 'inventory' department Level 3. If successful an [OK] is issued and transaction history (RFDLOCIH) is updated, if unsuccessful due to the change violating the Inventory location matrix an [ERROR] is issued with result message. Regardless whether the update is successful or not, an email alert is sent out based on the TagNet Systems settings.

This inquiry is a diagnostic tool to monitor that the TagNet Level Relationships have been setup correctly to allow an IMOVE rule to take place (as a result of Asset Department change as above). In a dynamically changing location management environment whereas the location matrix is in flux, this could cause contention with IMOVE rules not current with the location matrix. This is more apparent when a level is introduced such as 'Dept' that introduces a logical ownership into the location matrix. They end result is that an existing inventoried tag could have been in a valid location, but when being evaluated for an IMOVE rule, the target location in the IMOVE may no longer exist because the departments have been realigned. An example of the resulting email alert showing error can be seen here.

	Request Time
	The Timestamp when IMOVE request was made

	Request Status
	The request status (Waiting or Processed)

	TagID
	The TAGID that was moved

	Process Time
	The Timestamp when IMOVE event was processed

	Result Status
	The status of the IMOVE request

	Result Message
	The resulting message from the IMOVE request to Server

Action Links

These hyperlinks perform the following functions:

Details

Enables drill-down to the request details

Example Email Alert from IMOVE Request (v2/IMOVE or IMOVE queue).

Trigger on LOCEX when change on UDA value of 'DEPT'

Type

IMOVE Queue setup Objects

Update Trigger and IMOVE Queue.SQL

Date: April 15th, 2019

Author: Darryl Christensen

Dependencies: Extension Framework 1.0.6.0

Description: This trigger will capture any changes to the DEPT Asset UDA and

insert a "sync" request to the InventoryMovementQueue table. Note this table

is added with the Extension Framework 1.0.6.0 version. Once in this table, a

job must be setup to trigger a sync request.

USE TagNet

INSERT INTO XDEMOLIB.RFDLOCIT

(

RFDHSTAPP,

RFDHSTMOD,

RFDHSTCOD,

RFDHSTDSC,

RFDHSTCDT,

RFDHSTCTM,

RFDHSTCUS,

RFDHSTUDT,

RFDHSTUTM,

RFDHSTUUS,

X_UPID,

X_RRNO

)

VALUES

(

'TagNet',

'DEPTCHG',

'DEPT_CHG',

'Asset UDA Department Changed',

'20190419',

'120000',

'RFIDADMIN',

'',

)

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

DROP TRIGGER [XDEMOLIB].[SYNC_ASSET_LOC_WITH_DEPT]

CREATE TRIGGER SYNC_ASSET_LOC_WITH_DEPT ON [XDEMOLIB].[RFDASSEX]

AFTER INSERT, UPDATE

DECLARE @AssetId CHAR(30);

DECLARE @AssetUdaElement CHAR(30);

DECLARE @AssetUdaValue VARCHAR(256);

SELECT @AssetId = i.RFDASXAST FROM inserted i;

SELECT @AssetUdaElement = i.RFDASXELM FROM inserted i;

SELECT @AssetUdaValue = UPPER(i.RFDASXVAL) FROM inserted i;

/* Only make changes if the UDA being updated is the Asset 'DEPT' attribute */

IF @AssetUdaElement = 'DEPT'

BEGIN

DECLARE @AssetTagID CHAR(30);

DECLARE @AssetLevel1 CHAR(10);

DECLARE @AssetLevel2 CHAR(10);

DECLARE @AssetLevel3 CHAR(10);

DECLARE @AssetLevel4 CHAR(10);

DECLARE @AssetLevel5 CHAR(10);

DECLARE @IlvlrRrn DECIMAL(15,0);

/* Get the current asset location */

SELECT @AssetTagID = a.RFDASTGID FROM XDEMOLIB.RFDASSM a WHERE RFDASSTID = @AssetId;

SELECT

@AssetLevel1 = l.RFDLVL1ID,

@AssetLevel2 = l.RFDLVL2ID,

@AssetLevel3 = l.RFDLVL3ID,

@AssetLevel4 = l.RFDLVL4ID,

@AssetLevel5 = l.RFDLVL5ID

FROM XDEMOLIB.RFDLOCIN l WHERE RFDTAGUID = @AssetTagID;

INSERT INTO EF.InventoryMovementQueue

(

RequestTime,

RequestStatus,

RequestDescription,

TagID,TargetLevel1,

TargetLevel2,

TargetLevel3,

TargetLevel4,

TargetLevel5,

SourceLevel1,

SourceLevel2,

SourceLevel3,

SourceLevel4,

SourceLevel5,

ProcessTime

)

VALUES

(

GETDATE(),

'Waiting',

'Asset DEPT has been changed to ' + @AssetUdaValue + '. The Asset will be put in a queue to have the inventory location synced with the new DEPT.',

RTRIM(@AssetTagID),

RTRIM(@AssetLevel1),

RTRIM(@AssetLevel2),

RTRIM(@AssetUdaValue),

RTRIM(@AssetLevel4),

RTRIM(@AssetLevel5),

RTRIM(@AssetLevel1),

RTRIM(@AssetLevel2),

RTRIM(@AssetLevel3),

RTRIM(@AssetLevel4),

RTRIM(@AssetLevel5),

GETDATE()

)

END

Download Link

Click this link to down load ZIP of objects to implement