Begin Transaction


-------- We need to get the information from the linked validation table first for the new person

DECLARE @rootdata CHAR(128); ---- Get the name
Set @rootdata = (select rootdata from mt.vallinkdata where root = '4247' and  data1 = '$New_ID$');

DECLARE @data1 CHAR(20); ----- save the employee id
Set @data1 = (select data1 from mt.vallinkdata where root = '4247' and data1 = '$New_ID$');

DECLARE @data2 CHAR(200); ---- Get the Email Address
Set @data2 = (select data2 from mt.vallinkdata where root = '4247' and data1 = '$New_ID$');

-------- Inventory Change

update mt.inventory set i4247 = @rootdata, i4202 = @data1, i4241 = @data2 where i4202 = '$Old_ID$';


-------- Location Change - adding a new record ASSUMES THE ASSET HAS NOT BEEN CHECKED IN

Insert into mt.location (L2809, L2810, L2824, L2804, L2828, L2801, L2835, Mtag)
select CONVERT(date, GETDATE()), (SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS '24 Hour Time'), CONVERT(date, GETDATE()), (SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS '24 Hour Time'), @rootdata, @data1, @data2, mtag
from mt.location where L2801 = '$Old_ID$' and L2825 is null and L2802 is null and l2815 = 1;

													 
------ The Location change below creates a new record if has been checked in.....
													 
insert into mt.location (L2809, L2810, L2824, L2804, L2828, L2801, L2835, Mtag, L2825, L2816, L2833, L2802)
select CONVERT(date, GETDATE()), (SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS '24 Hour Time'), CONVERT(date, GETDATE()), (SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS '24 Hour Time'), @rootdata, @data1, @data2, mtag, CONVERT(date, GETDATE()), (SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS '24 Hour Time'), @rootdata, @data1 
from mt.location where L2801 = '$Old_ID$' and L2825 is NOT null and L2802 is NOT null and l2815 = 1;


select i4201, i4248, i4203, I4247, i4202, i4241 from mt.inventory where i4202 = '$New_ID$';

commit Transaction