
CREATE  PROCEDURE   `um_update_unused_tcodes`(in tname varchar(50),in sys char(3),in cli char(3))
BEGIN 
-- update unused tcodes
set @old =subdate(curdate(),interval 1 year); 
CREATE TEMPORARY TABLE urt( last_used_date date ,  tcode_id int);
set @sql =CONCAT('
insert into urt
(SELECT
  max(date) as date,
  um_tcode.id as tcode_id
FROM ',tname,' as a 
inner join um_user on a.user = um_user.user and um_user.system =''',sys,''' and um_user.client =''',cli,''' 
inner join um_tcode on a.tcode = um_tcode.tcode and um_tcode.system =''',sys,''' and um_tcode.client =''',cli,''' 
inner join um_role_tcode on um_role_tcode.tcode_id =  um_tcode.id
inner join um_user_role on  um_user_role.user_id = um_user.id and um_user_role.system =''',sys,''' and um_user_role.client =''',cli,''' 
where um_role_tcode.role_id = um_user_role.role_id
AND um_user_role.um_delete = 0 AND um_role_tcode.um_delete = 0 and um_tcode.um_delete = 0
group by  um_tcode.id);');

PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

 set @sql2 = CONCAT('update um_dash_unused_tcodes_',sys,'_',cli,'  as b
	        inner join (select last_used_date as a,tcode_id from urt a where a.last_used_date > ''',@old,''') as temp on b.tcode_id=temp.tcode_id
	         set b.last_used_date =temp.a'); 


 PREPARE stmt2 FROM @sql2;
   EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;

  -- select * from urt where last_used_date > @old;

 DROP TEMPORARY TABLE urt;
END