i'm trying to customize the web portal in a way tha allows different groups of users to place requests on behalf of defined users. The SQL is getting a bit advanced and i'm trying to figure out how to do what I want. In the code below i'm using case-when to define the groups of users with request-permissions, but the THEN-clauses are causing me trouble. Are there a better way to do this than my attempt?
-- Employees for whom a request can be placed
uid_person in (
select uid_person from QER_VEditEmployee where uid_personhead like
CASE
WHEN --ProductOwner
(EXISTS (select 1 from personinaerole where uid_person = '%useruid%' and UID_AERole in (select uid_aerole from aerole where uid_parentaerole like 'QER-AEROLE-ITSHOPADMIN-OWNER')))
THEN --Show everyone who can order the products owned by %useruid%
'?'
WHEN --ShelfOwner
(EXISTS (select 1 from ITShopOrg where uid_personhead = '%useruid%'))
THEN --Show everyone with access to the selves owned by %useruid%
'?'
WHEN --ServiceDesk (Member of permission group OrderOnBehalfExceptX)
(EXISTS(
select 1 from personinaerole pa
join aerole a on pa.uid_aerole = a.uid_aerole
join DialogGroupCollection dgc on a.uid_dialoggroup = dgc.uid_dialoggroup
where dgc.uid_dialogGroupParent = 'CCC-B151339FAAD674499565EAC010DD1AAB'
and pa.uid_person = '%useruid%'
))
THEN -- Show everyone except X (Person.CCC_XIsInActive = '1')
'?'
WHEN --ServiceDesk X (Member of permission group OrderOnBehalfX)
(EXISTS(
select 1 from personinaerole pa
join aerole a on pa.uid_aerole = a.uid_aerole
join DialogGroupCollection dgc on a.uid_dialoggroup = dgc.uid_dialoggroup
where dgc.uid_dialogGroupParent = 'CCC-04881080D4C98C48BB734763FCD4D84E'
and pa.uid_person = '%useruid%'
))
THEN -- Show X (Person.CCC_XIsInActive = '0')
'?'
WHEN --ServiceDesk and ServiceDesk X (Member of permission groups OrderOnBehalfExceptX + OrderOnBehalfX)
(EXISTS(
select 1 from personinaerole pa
join aerole a on pa.uid_aerole = a.uid_aerole
join DialogGroupCollection dgc on a.uid_dialoggroup = dgc.uid_dialoggroup
where dgc.uid_dialogGroupParent in ('CCC-04881080D4C98C48BB734763FCD4D84E','CCC-B151339FAAD674499565EAC010DD1AAB')
and pa.uid_person = '%useruid%'
))
THEN -- Show everyone
'%'
ELSE --Regular manager, show everyone with QER_VEditEmployee.PersonHead = %useruid%
'%useruid%'
END
)