Back to OIM Explorer

dbo.CPL_PSubRuleFillPerson_i

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.908 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVStringToList source text reference
  • references source dbo.QBM_FCVStringToListSQLMorphem source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL302 lines
1CREATE PROCEDURE CPL_PSubRuleFillPerson_i(2  @UID_ComplianceRule varchar(38),3  @Persons QBM_YSingleGuid READONLY,4  @GenProcID varchar(38)5)6AS7BEGIN8  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')9  DECLARE @SQLcmd nvarchar(max)10  DECLARE @whereclause nvarchar(max)11  DECLARE @IsCrossPersonCheck BIT12  DECLARE @IsInactive BIT13  DECLARE @IsWorkingCopy BIT14  DECLARE @IsSinglePerson BIT = 015  DECLARE @TakeAllPersons BIT = 016  DECLARE @IsPersonStoreInverted BIT = 017  DECLARE @DebugSwitch int = 018  DECLARE @AnzahlEinzelPersonen int19  SET XACT_ABORT OFF20  BEGIN TRY21    SET nocount22      ON23    SELECT @AnzahlEinzelPersonen = COUNT(*)24    FROM @Persons25    IF @AnzahlEinzelPersonen > 026    BEGIN27      SELECT @IsSinglePerson = 128    END29    SELECT30      TOP 1 @whereclause = c.WhereClausePerson,31      @IsInactive = c.IsInactive,32      @IsWorkingCopy = c.IsWorkingCopy,33      @IsPersonStoreInverted = c.IsPersonStoreInverted,34      @IsCrossPersonCheck = c.IsCrossPersonCheck35    FROM ComplianceRule c36    WHERE37      UID_ComplianceRule = @UID_ComplianceRule38    IF @IsInactive = 1 OR @IsWorkingCopy = 139    BEGIN40      EXEC QBM_PSessionContextSet 'GenProcID',41        @GenProcID42      DELETE ComplianceSubRulePerson43      WHERE44        UID_ComplianceRule = @UID_ComplianceRule45      GOTO endLabel46    END47    IF isnull(@whereclause,48    N '') = ''49    BEGIN50      SELECT @whereclause = '1=1'51    END52    drop TABLE53    IF EXISTS #PersonToHandle54    CREATE TABLE #PersonToHandle(55      UID_Person varchar(38) collate database_default NOT NULL primary key clustered56    ) drop TABLE57    IF EXISTS #ValidMembersOfRule58    CREATE TABLE #ValidMembersOfRule(59      UID_Person varchar(38) collate database_default NOT NULL primary key clustered60    ) drop TABLE61    IF EXISTS #HelperForMasterSub62    CREATE TABLE #HelperForMasterSub(63      UID_Person varchar(38) collate database_default NOT NULL primary key clustered,64      UID_PersonMasterIdentity varchar(38) collate database_default NULL,65      index #HelperForMasterSub1(UID_PersonMasterIdentity)66    )67    IF @IsSinglePerson = 168    BEGIN69      INSERT INTO #PersonToHandle(UID_Person70    )71    SELECT72      DISTINCT me.UID_PersonAlsoMe73    FROM QER_VPersonsAreMe me74    JOIN @Persons pe75      ON me.UID_PersonOrigin = pe.UID_SingleGuid76  END77  IF @WhereClause LIKE '%1%=%1%' AND @IsSinglePerson = 078  BEGIN79    IF 3 =(80      SELECT count(*)81    FROM dbo.QBM_FCVStringToListSQLMorphem(@WhereClause,82    0,83    0)84    AS85    i86    JOIN(87    VALUES(1, '1'),(2, '='),(3, '1')) AS s(Sortorder, Morphem)88      ON s.Sortorder = i.SortOrder AND s.Morphem = i.Morphem)89    BEGIN90      SELECT @TakeAllPersons = 191    END92  END93  IF @DebugSwitch > 094  BEGIN95    print '@TakeAllPersons : ' + str(@TakeAllPersons) print '@WhereClause    : ' + @whereclause96  END97  IF @TakeAllPersons = 198  BEGIN99    INSERT INTO #ValidMembersOfRule(UID_Person)100    SELECT p.UID_Person101    FROM Person p102  END103  ELSE104  BEGIN105    IF @IsCrossPersonCheck = 1106    BEGIN107      SELECT108        @SQLcmd = CONCAT('	select UID_Person, UID_PersonMasterIdentity109				from Person110				where ( ',111        @whereclause,112        '113					) 114				',115      CASE @IsSinglePerson116        WHEN 1 THEN117      ' and UID_Person in (select x.UID_Person from #PersonToHandle x) '118      ELSE ''119      END)120      INSERT INTO #HelperForMasterSub(UID_Person,121      UID_PersonMasterIdentity)122      EXEC sp_executesql @SQLCmd123      INSERT INTO #ValidMembersOfRule(UID_Person)124      SELECT m.UID_Person125      FROM #HelperForMasterSub m126      UNION127      SELECT p.UID_PersonMasterIdentity128      FROM #HelperForMasterSub p129      WHERE130        p.UID_PersonMasterIdentity > ' '131      UNION132      SELECT p.UID_Person133      FROM dbo.Person p134      JOIN(135      SELECT m.UID_Person136      FROM #HelperForMasterSub m137      UNION all138      SELECT p.UID_PersonMasterIdentity139      FROM #HelperForMasterSub p140      WHERE141        p.UID_PersonMasterIdentity > ' ') e142        ON p.UID_PersonMasterIdentity = e.UID_Person143    END144    ELSE145    BEGIN146      SELECT147        @SQLcmd = CONCAT('	select UID_Person148				from Person149				where ( ',150        @whereclause,151        '152					) 153				',154      CASE @IsSinglePerson155        WHEN 1 THEN156      ' and UID_Person in (select x.UID_Person from #PersonToHandle x) '157      ELSE ''158      END)159      INSERT INTO #ValidMembersOfRule(UID_Person)160      EXEC sp_executesql @SQLCmd161    END162  END163  EXEC QBM_PSessionContextSet 'GenProcID',164    @GenProcID165  IF @IsPersonStoreInverted = 0166  BEGIN167    IF @IsSinglePerson = 0168    BEGIN169      IF @DebugSwitch > 0170      BEGIN171        print 'Verarbeitung inverted = 0, @IsSinglePerson = 0'172      END173      INSERT INTO ComplianceSubRulePerson(UID_ComplianceRule,174      UID_Person)175      SELECT176        @UID_ComplianceRule,177        m.UID_Person178      FROM #ValidMembersOfRule m179      WHERE180        NOT EXISTS(181      SELECT TOP 1 1182      FROM ComplianceSubRulePerson e183      WHERE184        e.UID_ComplianceRule = @UID_ComplianceRule AND e.UID_Person = m.UID_Person)185      DELETE ComplianceSubRulePerson186      FROM ComplianceSubRulePerson csp187      WHERE188        csp.UID_ComplianceRule = @UID_ComplianceRule AND NOT EXISTS(189      SELECT TOP 1 1190      FROM #ValidMembersOfRule m191      WHERE192        m.UID_Person = csp.UID_Person)193    END194    ELSE195    BEGIN196      IF @DebugSwitch > 0197      BEGIN198        print 'Verarbeitung inverted = 0, @IsSinglePerson = 1'199      END200      INSERT INTO ComplianceSubRulePerson(UID_ComplianceRule,201      UID_Person)202      SELECT203        @UID_ComplianceRule,204        m.UID_Person205      FROM #ValidMembersOfRule m206      WHERE207        NOT EXISTS(208      SELECT TOP 1 1209      FROM ComplianceSubRulePerson e210      WHERE211        e.UID_ComplianceRule = @UID_ComplianceRule AND e.UID_Person = m.UID_Person)212      DELETE ComplianceSubRulePerson213      FROM ComplianceSubRulePerson csp214      JOIN #PersonToHandle ph215        ON csp.UID_Person = ph.UID_Person AND csp.UID_ComplianceRule = @UID_ComplianceRule216      WHERE217        NOT EXISTS(218      SELECT TOP 1 1219      FROM #ValidMembersOfRule m220      WHERE221        m.UID_Person = csp.UID_Person)222    END223  END224  ELSE225  BEGIN226    IF @IsSinglePerson = 0227    BEGIN228      IF @DebugSwitch > 0229      BEGIN230        print 'Verarbeitung inverted = 1, @IsSinglePerson = 0'231      END232      INSERT INTO ComplianceSubRulePerson(UID_ComplianceRule,233      UID_Person)234      SELECT235        @UID_ComplianceRule,236        p.UID_person237      FROM Person p238      LEFT239      OUTER240      JOIN #ValidMembersOfRule vm241        ON p.UID_Person = vm.UID_Person242      WHERE243        vm.UID_Person IS NULL AND NOT EXISTS(244      SELECT TOP 1 1245      FROM ComplianceSubRulePerson e246      WHERE247        e.UID_ComplianceRule = @UID_ComplianceRule AND e.UID_Person = p.UID_Person)248      DELETE ComplianceSubRulePerson249      FROM ComplianceSubRulePerson csp250      WHERE251        csp.UID_ComplianceRule = @UID_ComplianceRule AND EXISTS(252      SELECT TOP 1 1253      FROM #ValidMembersOfRule m254      WHERE255        m.UID_Person = csp.UID_Person)256    END257    ELSE258    BEGIN259      IF @DebugSwitch > 0260      BEGIN261        print 'Verarbeitung inverted = 1, @IsSinglePerson = 1'262      END263      DELETE ComplianceSubRulePerson264      FROM ComplianceSubRulePerson csp265      JOIN #PersonToHandle ph266        ON csp.UID_Person = ph.UID_Person267      JOIN #ValidMembersOfRule vm268        ON ph.UID_Person = vm.UID_Person269      WHERE270        csp.UID_ComplianceRule = @UID_ComplianceRule271      INSERT INTO ComplianceSubRulePerson(UID_ComplianceRule,272      UID_Person)273      SELECT274        @UID_ComplianceRule,275        ph.UID_Person276      FROM #PersonToHandle ph277      LEFT278      OUTER279      JOIN #ValidMembersOfRule vm280        ON ph.UID_Person = vm.UID_Person281      WHERE282        vm.UID_Person IS NULL AND NOT EXISTS(283      SELECT TOP 1 1284      FROM ComplianceSubRulePerson e285      WHERE286        e.UID_ComplianceRule = @UID_ComplianceRule AND e.UID_Person = ph.UID_Person)287    END288  END289END TRY290BEGIN CATCH291  EXEC QBM_PSessionErrorAdd DEFAULT292  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()293  RAISERROR(@Rethrow,294  18,295  1)296    WITH NOWAIT297END CATCH298endLabel:299EXEC QBM_PSessionContextSet 'GenProcID',300  @GenProcID_R301RETURN302END
Open raw exported source
SQL ยท Raw53 lines
1          create   procedure CPL_PSubRuleFillPerson_i ( @UID_ComplianceRule varchar(38) , @Persons QBM_YSingleGuid readonly   , @GenProcID varchar2(38) ) AS begin declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @SQLcmd nvarchar(max) declare @whereclause nvarchar(max) declare3 @IsCrossPersonCheck bit declare @IsInactive bit declare @IsWorkingCopy bit declare @IsSinglePerson bit = 0 declare @TakeAllPersons bit = 0 declare @IsPersonStoreInverted4 bit = 0 declare @DebugSwitch int = 0 declare @AnzahlEinzelPersonen int SET XACT_ABORT OFF BEGIN TRY set nocount on select @AnzahlEinzelPersonen = COUNT5(*) from @Persons if @AnzahlEinzelPersonen > 0 begin select @IsSinglePerson = 1 end select top 1 @whereclause = c.WhereClausePerson , @IsInactive = c.IsInactive6 , @IsWorkingCopy = c.IsWorkingCopy , @IsPersonStoreInverted = c.IsPersonStoreInverted , @IsCrossPersonCheck = c.IsCrossPersonCheck from ComplianceRule7 c where UID_ComplianceRule = @UID_ComplianceRule if    @IsInactive = 1 or @IsWorkingCopy = 1 begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID8 delete ComplianceSubRulePerson where UID_ComplianceRule = @UID_ComplianceRule goto endLabel end if isnull(@whereclause, N'') = '' begin select @whereclause9 = '1=1' end drop table if exists #PersonToHandle create table #PersonToHandle (UID_Person varchar(38) collate database_default not null primary key clustered10)   drop table if exists #ValidMembersOfRule create table #ValidMembersOfRule (UID_Person varchar(38) collate database_default not null primary key clustered11)  drop table if exists #HelperForMasterSub create table #HelperForMasterSub (UID_Person varchar(38) collate database_default not null primary key clustered12 , UID_PersonMasterIdentity varchar(38) collate database_default null , index #HelperForMasterSub1 (UID_PersonMasterIdentity) )  if @IsSinglePerson = 113 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_FCVStringToListSQLMorphem15(@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.Morphem16 ) begin select @TakeAllPersons = 1 end end if @DebugSwitch > 0 begin print '@TakeAllPersons : ' + str(@TakeAllPersons) print '@WhereClause    : ' + @whereclause17 end if @TakeAllPersons = 1 begin insert into #ValidMembersOfRule(UID_Person) select p.UID_Person from Person p end else  begin  if @IsCrossPersonCheck18 = 1 begin select @SQLcmd = CONCAT( '	select UID_Person, UID_PersonMasterIdentity19				from Person20				where ( ' , @whereclause , '21					) 22				', case23 @IsSinglePerson when 1 then ' and UID_Person in (select x.UID_Person from #PersonToHandle x) ' else '' end ) insert into #HelperForMasterSub(UID_Person24, UID_PersonMasterIdentity) exec sp_executesql @SQLCmd  insert into #ValidMembersOfRule(UID_Person)  select m.UID_Person from #HelperForMasterSub m  union25 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_PersonMasterIdentity27 > ' ' ) e on p.UID_PersonMasterIdentity = e.UID_Person end else  begin select @SQLcmd = CONCAT( '	select UID_Person28				from Person29				where ( ' , 30@whereclause , '31					) 32				', case @IsSinglePerson when 1 then ' and UID_Person in (select x.UID_Person from #PersonToHandle x) ' else '' end ) insert33 into #ValidMembersOfRule(UID_Person) exec sp_executesql @SQLCmd end  end    exec QBM_PSessionContextSet 'GenProcID', @GenProcID if @IsPersonStoreInverted34 = 0 begin  if @IsSinglePerson = 0 begin  if @DebugSwitch > 0 begin print 'Verarbeitung inverted = 0, @IsSinglePerson = 0' end insert into ComplianceSubRulePerson35(UID_ComplianceRule, UID_Person) select @UID_ComplianceRule, m.UID_Person from #ValidMembersOfRule m where not exists (select top 1 1 from ComplianceSubRulePerson36 e where e.UID_ComplianceRule = @UID_ComplianceRule and e.UID_Person = m.UID_Person ) delete ComplianceSubRulePerson from ComplianceSubRulePerson csp where37 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_Person39) select @UID_ComplianceRule, m.UID_Person from #ValidMembersOfRule m where not exists (select top 1 1 from ComplianceSubRulePerson e where e.UID_ComplianceRule40 = @UID_ComplianceRule and e.UID_Person = m.UID_Person ) delete ComplianceSubRulePerson from ComplianceSubRulePerson csp join #PersonToHandle ph on csp.UID_Person41 = 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_Person42 ) 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 on44 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_ComplianceRule45 and e.UID_Person = p.UID_Person ) delete ComplianceSubRulePerson from ComplianceSubRulePerson csp where csp.UID_ComplianceRule = @UID_ComplianceRule and46 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 #ValidMembersOfRule48 vm on ph.UID_Person = vm.UID_Person where csp.UID_ComplianceRule = @UID_ComplianceRule  insert into ComplianceSubRulePerson(UID_ComplianceRule, UID_Person49) select @UID_ComplianceRule, ph.UID_Person from #PersonToHandle ph left outer join #ValidMembersOfRule vm on ph.UID_Person = vm.UID_Person where vm.UID_Person50 is null and not exists (select top 1 1 from ComplianceSubRulePerson e where e.UID_ComplianceRule = @UID_ComplianceRule and e.UID_Person = ph.UID_Person51 ) end end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow52, 18, 1) WITH NOWAIT END CATCH endLabel:     exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 53