dbo.CPL_PSubRuleFillPerson_i

1          create   procedure CPL_PSubRuleFillPerson_i ( @UID_ComplianceRule varchar(38) , @Persons QBM_YSingleGuid readonly   , @GenProcID varchar
2(38) ) AS begin declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @SQLcmd nvarchar(max) declare @whereclause nvarchar(max) declare
3 @IsCrossPersonCheck bit declare @IsInactive bit declare @IsWorkingCopy bit declare @IsSinglePerson bit = 0 declare @TakeAllPersons bit = 0 declare @IsPersonStoreInverted
4 bit = 0 declare @DebugSwitch int = 0 declare @AnzahlEinzelPersonen int SET XACT_ABORT OFF BEGIN TRY set nocount on select @AnzahlEinzelPersonen = COUNT
5(*) from @Persons if @AnzahlEinzelPersonen > 0 begin select @IsSinglePerson = 1 end select top 1 @whereclause = c.WhereClausePerson , @IsInactive = c.IsInactive
6 , @IsWorkingCopy = c.IsWorkingCopy , @IsPersonStoreInverted = c.IsPersonStoreInverted , @IsCrossPersonCheck = c.IsCrossPersonCheck from ComplianceRule
7 c where UID_ComplianceRule = @UID_ComplianceRule if    @IsInactive = 1 or @IsWorkingCopy = 1 begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID
8 delete ComplianceSubRulePerson where UID_ComplianceRule = @UID_ComplianceRule goto endLabel end if isnull(@whereclause, N'') = '' begin select @whereclause
9 = '1=1' end drop table if exists #PersonToHandle create table #PersonToHandle (UID_Person varchar(38) collate database_default not null primary key clustered
10)   drop table if exists #ValidMembersOfRule create table #ValidMembersOfRule (UID_Person varchar(38) collate database_default not null primary key clustered
11)  drop table if exists #HelperForMasterSub create table #HelperForMasterSub (UID_Person varchar(38) collate database_default not null primary key clustered
12 , UID_PersonMasterIdentity varchar(38) collate database_default null , index #HelperForMasterSub1 (UID_PersonMasterIdentity) )  if @IsSinglePerson = 1
13 begin insert into #PersonToHandle(UID_Person) select distinct me.UID_PersonAlsoMe from QER_VPersonsAreMe me join @Persons pe on me.UID_PersonOrigin = 
14pe.UID_SingleGuid      end    if @WhereClause like '%1%=%1%'  and @IsSinglePerson = 0 begin  if 3 = (select count(*) from dbo.QBM_FCVStringToListSQLMorphem
15(@WhereClause, 0, 0) as i join ( values (1, '1') , (2, '=') , (3, '1') ) as s (Sortorder, Morphem) on s.Sortorder = i.SortOrder and s.Morphem = i.Morphem
16 ) begin select @TakeAllPersons = 1 end end if @DebugSwitch > 0 begin print '@TakeAllPersons : ' + str(@TakeAllPersons) print '@WhereClause    : ' + @whereclause
17 end if @TakeAllPersons = 1 begin insert into #ValidMembersOfRule(UID_Person) select p.UID_Person from Person p end else  begin  if @IsCrossPersonCheck
18 = 1 begin select @SQLcmd = CONCAT( '	select UID_Person, UID_PersonMasterIdentity
19				from Person
20				where ( ' , @whereclause , '
21					) 
22				', case
23 @IsSinglePerson when 1 then ' and UID_Person in (select x.UID_Person from #PersonToHandle x) ' else '' end ) insert into #HelperForMasterSub(UID_Person
24, UID_PersonMasterIdentity) exec sp_executesql @SQLCmd  insert into #ValidMembersOfRule(UID_Person)  select m.UID_Person from #HelperForMasterSub m  union
25 select p.UID_PersonMasterIdentity from #HelperForMasterSub p where p.UID_PersonMasterIdentity > ' '  union select p.UID_Person from dbo.Person p join(
26   select m.UID_Person from #HelperForMasterSub m  union all select p.UID_PersonMasterIdentity from #HelperForMasterSub p where p.UID_PersonMasterIdentity
27 > ' ' ) e on p.UID_PersonMasterIdentity = e.UID_Person end else  begin select @SQLcmd = CONCAT( '	select UID_Person
28				from Person
29				where ( ' , 
30@whereclause , '
31					) 
32				', case @IsSinglePerson when 1 then ' and UID_Person in (select x.UID_Person from #PersonToHandle x) ' else '' end ) insert
33 into #ValidMembersOfRule(UID_Person) exec sp_executesql @SQLCmd end  end    exec QBM_PSessionContextSet 'GenProcID', @GenProcID if @IsPersonStoreInverted
34 = 0 begin  if @IsSinglePerson = 0 begin  if @DebugSwitch > 0 begin print 'Verarbeitung inverted = 0, @IsSinglePerson = 0' end insert into ComplianceSubRulePerson
35(UID_ComplianceRule, UID_Person) select @UID_ComplianceRule, m.UID_Person from #ValidMembersOfRule m where not exists (select top 1 1 from ComplianceSubRulePerson
36 e where e.UID_ComplianceRule = @UID_ComplianceRule and e.UID_Person = m.UID_Person ) delete ComplianceSubRulePerson from ComplianceSubRulePerson csp where
37 csp.UID_ComplianceRule = @UID_ComplianceRule and not exists (select top 1 1 from #ValidMembersOfRule m where m.UID_Person = csp.UID_Person ) end else 
38begin    if @DebugSwitch > 0 begin print 'Verarbeitung inverted = 0, @IsSinglePerson = 1' end insert into ComplianceSubRulePerson(UID_ComplianceRule, UID_Person
39) select @UID_ComplianceRule, m.UID_Person from #ValidMembersOfRule m where not exists (select top 1 1 from ComplianceSubRulePerson e where e.UID_ComplianceRule
40 = @UID_ComplianceRule and e.UID_Person = m.UID_Person ) delete ComplianceSubRulePerson from ComplianceSubRulePerson csp join #PersonToHandle ph on csp.UID_Person
41 = ph.UID_Person and csp.UID_ComplianceRule = @UID_ComplianceRule where not exists (select top 1 1 from #ValidMembersOfRule m where m.UID_Person = csp.UID_Person
42 ) end end else begin  if @IsSinglePerson = 0 begin   if @DebugSwitch > 0 begin print 'Verarbeitung inverted = 1, @IsSinglePerson = 0' end insert into 
43ComplianceSubRulePerson(UID_ComplianceRule, UID_Person) select @UID_ComplianceRule, p.UID_person from Person p left outer join #ValidMembersOfRule vm on
44 p.UID_Person = vm.UID_Person where vm.UID_Person is null and not exists (select top 1 1 from ComplianceSubRulePerson e where e.UID_ComplianceRule = @UID_ComplianceRule
45 and e.UID_Person = p.UID_Person ) delete ComplianceSubRulePerson from ComplianceSubRulePerson csp where csp.UID_ComplianceRule = @UID_ComplianceRule and
46 exists (select top 1 1 from #ValidMembersOfRule m where m.UID_Person = csp.UID_Person ) end else begin     if @DebugSwitch > 0 begin print 'Verarbeitung inverted = 1, @IsSinglePerson = 1'
47 end  delete ComplianceSubRulePerson from ComplianceSubRulePerson csp join #PersonToHandle ph on csp.UID_Person = ph.UID_Person join #ValidMembersOfRule
48 vm on ph.UID_Person = vm.UID_Person where csp.UID_ComplianceRule = @UID_ComplianceRule  insert into ComplianceSubRulePerson(UID_ComplianceRule, UID_Person
49) select @UID_ComplianceRule, ph.UID_Person from #PersonToHandle ph left outer join #ValidMembersOfRule vm on ph.UID_Person = vm.UID_Person where vm.UID_Person
50 is null and not exists (select top 1 1 from ComplianceSubRulePerson e where e.UID_ComplianceRule = @UID_ComplianceRule and e.UID_Person = ph.UID_Person
51 ) end end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow
52, 18, 1) WITH NOWAIT END CATCH endLabel:     exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 
53