Flex: Create 4 ranges for the created KPI

create-4-ranges-for-KPI.sql

--Flex for R5HOME PostInsert

declare @dupcnt int;
declare @homcod nvarchar(30), @hommax numeric(24,6), @homfunc nvarchar(30), @homkpityp nvarchar(30), @typ nvarchar(30);

select @homcod=hom_code, @hommax=HOM_MAX, @homfunc=hom_ewsfunction, @homkpityp=hom_kpitype, @typ=hom_type from r5home where hom_sqlidentity=:rowid;

--Note: Right now, we're only doing this for the requisition screen
if @homfunc<>'SSREQU' or @homkpityp not in ('D', 'SB') or @typ<>'+' or @hommax<4
return;

select 
@dupcnt=count(1)
from
r5kpiscores
where
kps_homcode=@homcod;

if @dupcnt=0
begin

insert into r5kpiscores (
KPS_HOMCODE
,KPS_HOMTYPE
,KPS_LOWVALUE
,KPS_HIGHVALUE
,KPS_SCORE
,KPS_DESC
,KPS_ICON
,KPS_UPDATECOUNT
,KPS_COLOR
) values (
@homcod
,@typ
,0
,cast(@hommax/4 as int)
,4
,'Lo'
,NULL
,0
,'2DB329'
);

insert into r5kpiscores (
KPS_HOMCODE
,KPS_HOMTYPE
,KPS_LOWVALUE
,KPS_HIGHVALUE
,KPS_SCORE
,KPS_DESC
,KPS_ICON
,KPS_UPDATECOUNT
,KPS_COLOR
) values (
@homcod
,@typ
,cast(@hommax/4 as int)+1
,cast(@hommax/2 as int)
,3
,'Med-Lo'
,NULL
,0
,'9ED927'
);

insert into r5kpiscores (
KPS_HOMCODE
,KPS_HOMTYPE
,KPS_LOWVALUE
,KPS_HIGHVALUE
,KPS_SCORE
,KPS_DESC
,KPS_ICON
,KPS_UPDATECOUNT
,KPS_COLOR
) values (
@homcod
,@typ
,cast(@hommax/2 as int)+1
,cast(@hommax*.75 as int)
,2
,'Med-Hi'
,NULL
,0
,'FFD500'
);

insert into r5kpiscores (
KPS_HOMCODE
,KPS_HOMTYPE
,KPS_LOWVALUE
,KPS_HIGHVALUE
,KPS_SCORE
,KPS_DESC
,KPS_ICON
,KPS_UPDATECOUNT
,KPS_COLOR
) values (
@homcod
,@typ
,cast(@hommax*.75 as int)+1
,cast(@hommax as int)
,1
,'Hi'
,NULL
,0
,'FFAA00'
);

insert into r5kpiscores (
KPS_HOMCODE
,KPS_HOMTYPE
,KPS_LOWVALUE
,KPS_HIGHVALUE
,KPS_SCORE
,KPS_DESC
,KPS_ICON
,KPS_UPDATECOUNT
,KPS_COLOR
) values (
@homcod
,@typ
,cast(@hommax as int)+1
,999999
,0
,'Exceeded'
,NULL
,0
,'D5000E'
);

end;

HOM_TYPE is '+' for KPIs and '-' for Inboxes