Add KPI and Inbox entries to the user's Start Center

add-kpi-inbox-to-user.sql

create procedure uspAddKpiToUser(
	@usrcod nvarchar(30),
	@kpicod nvarchar(30)
)
as
begin
	declare @dupcnt int;
	select @dupcnt=count(1) from r5homeusers where hmu_user=@usrcod and hmu_homcode=@kpicod and hmu_homtype='+';
	if @dupcnt=0 and exists(select 1 from r5home where hom_code=@kpicod and hom_type='+') and exists(select 1 from r5users where usr_code=@usrcod)
	begin
		insert into r5homeusers (hmu_homcode, hmu_homtype, hmu_user, hmu_seq, hmu_autofresh, hmu_updatecount, hmu_tab)
		select @kpicod, '+', hmu_user, max(hmu_seq)+10, '+', 0, NULL from r5homeusers where hmu_user=@usrcod group by hmu_user;
	end
	else
	begin
		raiserror('Error', 18, -1);
	end
end;
go


create procedure uspAddInboxToUser(
	@usrcod nvarchar(30),
	@inboxcod nvarchar(30)
)
as
begin
	declare @dupcnt int;
	select @dupcnt=count(1) from r5homeusers where hmu_user=@usrcod and hmu_homcode=@inboxcod and hmu_homtype='-' and hmu_tab='OPER';
	if @dupcnt=0 and exists(select 1 from r5home where hom_code=@inboxcod and hom_type='-') and exists(select 1 from r5users where usr_code=@usrcod)
	begin
		insert into r5homeusers (hmu_homcode, hmu_homtype, hmu_user, hmu_seq, hmu_autofresh, hmu_updatecount, hmu_tab)
		select @inboxcod, '-', hmu_user, max(hmu_seq)+10, '+', 0, 'OPER' from r5homeusers where hmu_user=@usrcod group by hmu_user;
	end
	else
	begin
		raiserror('Error', 18, -1);
	end
end;
go