dbo.QER_PDynamicGroupMakeProc_hlp
Stored ProcedureSQL_STORED_PROCEDURESandbox DB
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_FCVBinaryToString source text reference
- references source dbo.QBM_FCVObjectkeyToElement source text reference
- references source dbo.QBM_FGIBitPatternXMarkedForDel source text reference
- references source dbo.QBM_FGIBitPatternXOrigin source text reference
- references source dbo.QBM_FGISessionErrorRethrow source text reference
- references source dbo.QBM_FSQProcedureDef source text reference
- references source dbo.QER_FCVDynGuidToProcName source text reference
- references source dbo.QBM_PDBQueueCalculateDelta source text reference
- references source dbo.QBM_PMNTableInsert source text reference
- references source dbo.QBM_PMNTableOriginUpdate source text reference
- references source dbo.QBM_PSessionErrorAdd source text reference
- references source dbo.QBM_PSQLCreate source text reference
- references source dbo.QER_PDynamicGroupMakeProc_Drop source text reference
- references source dbo.QER_PMNTableAddViewProperties source text reference
References
- dbo.QBM_FCVBinaryToString
- dbo.QBM_FCVObjectkeyToElement
- dbo.QBM_FGIBitPatternXMarkedForDel
- dbo.QBM_FGIBitPatternXOrigin
- dbo.QBM_FGISessionErrorRethrow
- dbo.QBM_FSQProcedureDef
- dbo.QER_FCVDynGuidToProcName
- dbo.QBM_PDBQueueCalculateDelta
- dbo.QBM_PMNTableInsert
- dbo.QBM_PMNTableOriginUpdate
- dbo.QBM_PSessionErrorAdd
- dbo.QBM_PSQLCreate
- dbo.QER_PDynamicGroupMakeProc_Drop
- dbo.QER_PMNTableAddViewProperties
Referenced By
Complete Source
1CREATE PROCEDURE QER_PDynamicGroupMakeProc_hlp(2 @UID_DynamicGroup varchar(38)3)4AS5BEGIN6 DECLARE @DisplayName nvarchar(255)7 DECLARE @ObjectClass varchar(30)8 DECLARE @IsdeactivatedByPreprocessor BIT9 DECLARE @WhereClause nvarchar(max)10 DECLARE @UID_BaseTreeOrg varchar(38)11 DECLARE @preprop nvarchar(16)12 DECLARE @cons nvarchar(16)13 DECLARE @ProcNameRechneAlle nvarchar(64)14 DECLARE @ProcNameRechneDetail nvarchar(64)15 DECLARE @Declarations nvarchar(max)16 DECLARE @SQLcmd nvarchar(max)17 DECLARE @content1 nvarchar(max)18 DECLARE @content2 nvarchar(max)19 DECLARE @contentDN1 nvarchar(max)20 DECLARE @DebugSwitch int = 021 DECLARE @DebugLevel char(1) = 'W'22 DECLARE @StringPatternDynamic varchar(16) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',23 0)),24 0)25 DECLARE @StringPatternDynamicInv varchar(16) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',26 1)),27 0)28 DECLARE @StringPatternDelay varchar(16) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',29 0)),30 0)31 DECLARE @StringPatternDelayInv varchar(16) = dbo.QBM_FCVBinaryToString(CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',32 1)),33 0)34 SET XACT_ABORT OFF35 BEGIN TRY36 IF @DebugSwitch > 037 BEGIN38 EXEC QER_PDynamicGroupMakeProc_Drop @UID_DynamicGroup39 END40 SELECT41 @DisplayName = isnull(dg.DisplayName,42 '<no Display>'),43 @ObjectClass = t.TableName,44 @whereclause = CASE b.IsInvalidForDynamicGroup45 WHEN 1 THEN46 ' 1 = 0 '47 ELSE dg.WhereClause48 END,49 @UID_BaseTreeOrg = dbo.QBM_FCVObjectkeyToElement('ColumnValue1',50 dg.ObjectKeyBaseTree)51 FROM DynamicGroup dg52 JOIN DialogTable t53 WITH(readpast)54 ON dg.UID_DialogTableObjectClass = t.UID_DialogTable55 JOIN BaseTree b56 ON dg.ObjectKeyBaseTree = b.XObjectKey57 WHERE58 UID_DynamicGroup = @UID_DynamicGroup59 IF 1 = @IsdeactivatedByPreprocessor60 BEGIN61 SELECT @preprop = ''62 END63 ELSE64 BEGIN65 SELECT @preprop = N '--'66 END67 IF @ObjectClass IN('Hardware',68 'Person')69 BEGIN70 SELECT @cons = ''71 END72 ELSE73 BEGIN74 SELECT @cons = N '--'75 END76 SELECT77 @Declarations = CONCAT('78 declare @GenProcID varchar(38)79 declare @Sourcedata QBM_YDataForDelta80 , @CountDeltaQantity int 81 , @CountDeltaOrigin int 82 declare @BlackListExists bit = 083 declare @DBQueueCurrent QBM_YDBQueueCurrent84 declare @UID_Org varchar(38) = '''85 ,86 @UID_BaseTreeOrg,87 '''88 declare @UID_DynamicGroup varchar(38) = ''',89 @UID_DynamicGroup,90 '''91 declare @XUser nvarchar(64) = object_name(@@procid)92 declare @Xdate datetime = getutcdate()93 94')95 DECLARE @ContentStandardsA nvarchar(max) = CONCAT('96--3407697 if exists (select top 1 198 from DynamicGroup b99 where b.UID_DynamicGroup = @UID_DynamicGroup 100 and b.IsRecalculationDeactivated = 1101 )102 begin103 goto EndLabel104 end105106 insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID)107 select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID108 from QBMDBQueueCurrent cu with (readpast)109 where cu.SlotNumber = @SlotNumber110111 if @@rowcount = 0112 begin113 goto EndLabel114 end115116 if exists (select top 1 1117 from QERDynamicGroupBlackList b118 where b.UID_DynamicGroup = @UID_DynamicGroup 119 )120 begin121 select @BlackListExists = 1122 end123'124 ,125 '')126 DECLARE @ContentStandardsE nvarchar(max) = CONCAT('127--34076128 if exists (select top 1 1129 from DynamicGroup b130 where b.UID_DynamicGroup = @UID_DynamicGroup 131 and b.IsRecalculationDeactivated = 1132 )133 begin134 goto EndLabel135 end136137 insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID)138 select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID139 from QBMDBQueueCurrent cu with (readpast)140 where cu.SlotNumber = @SlotNumber141 and cu.UID_SubParameter = @UID_DynamicGroup142143 if @@rowcount = 0144 begin145 goto EndLabel146 end147148 if exists (select top 1 1149 from QERDynamicGroupBlackList b150 where b.UID_DynamicGroup = @UID_DynamicGroup 151 )152 begin153 select @BlackListExists = 1154 end155'156 ,157 '')158 SELECT159 @contentDN1 = CONCAT('160161 if exists (select top 1 1162 from BaseTreeExcludesBasetree beb163 where beb.UID_OrgExcluded = @UID_Org164 )165 begin166 -- 33720167 delete /* algorithmisch notwendig */ @Sourcedata 168 from @Sourcedata s join BaseTreeExcludesBasetree beb on s.Element = beb.UID_OrgExcluded169 join '170 ,171 @ObjectClass,172 'InBaseTree pe on pe.UID_Org = beb.UID_Org173 and pe.UID_',174 @ObjectClass,175 ' = s.AssignedElement176 and pe.XOrigin > 0177 where s.IsUpcommingContent = 1178 end179180 if exists (select top 1 1181 from BaseTreeExcludesBasetree beb182 where beb.UID_Org = @UID_Org183 )184 begin185 -- 33720, Nachtrag bidirektional186 delete /* algorithmisch notwendig */ @Sourcedata 187 from @Sourcedata s join BaseTreeExcludesBasetree beb on s.Element = beb.UID_Org188 join '189 ,190 @ObjectClass,191 'InBaseTree pe on pe.UID_Org = beb.UID_OrgExcluded192 and pe.UID_',193 @ObjectClass,194 ' = s.AssignedElement195 and pe.XOrigin > 0196 where s.IsUpcommingContent = 1197 end198 199 exec QBM_PDBQueueCalculateDelta @SourceData,200 @DeltaQuantity = 0,201 @DeltaDelete = 0,202 @DeltaInsert = 1,203 @DeltaOrigin = 1, 204 @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output205 , @UseIsInEffect = 0206 , @SlotNumber = @SlotNumber207 , @DBQueueCurrentExtern = @DBQueueCurrent208209 select @ret = 0210 select top 1 @GenProcID = cu.GenProcID211 from @DBQueueCurrent cu 212 where cu.UID_SubParameter = @UID_DynamicGroup213214 if isnull(@GenProcID, '''') = ''''215 begin216 select @GenProcID = newid() 217 end218219-- 16147 wenn Zieltabelle deaktiviert machen wir gar nichts220 '221 ,222 @preprop,223 ' goto endLabel224225 if @CountDeltaOrigin > 0 226 begin227 update #QBMDeltaOrigin228 set GenProcID = @GenProcID 229 where GenProcID is null230 exec QBM_PMNTableOriginUpdate '''231 ,232 @ObjectClass,233 'InBaseTree'', ''UID_Org'', ''UID_',234 @ObjectClass,235 '''236 end237238 if @CountDeltaQantity > 0239 begin240 update #QBMDeltaInsert241 set GenProcID = @GenProcID 242 where GenProcID is null 243 exec QER_PMNTableAddViewProperties '''244 ,245 @ObjectClass,246 'InBaseTree''247 exec QBM_PMNTableInsert ''',248 @ObjectClass,249 'InBaseTree'', ''UID_Org'', ''UID_',250 @ObjectClass,251 ''', @TargetIsView = 1252 end 253254')255 IF @DebugSwitch > 0256 BEGIN257 print isnull(@contentDN1,258 '< kein @contentDN1 @UID_BaseTreeOrg>')259 END260 SELECT261 @content1 = CONCAT('262-- 16147 wenn Zieltabelle deaktiviert machen wir gar nichts263 ',264 @preprop,265 ' goto endLabel266267insert into @SourceData(268 IsUpcommingContent, XOriginAfter269 , Element, AssignedElement, XOriginBefore270 )271272 select 0, 0273 , @UID_Org, UID_'274 ,275 @ObjectClass,276 ' , Xorigin277 from ',278 @ObjectClass,279 'InBaseTree280 where UID_Org = @UID_Org281282-- was nicht dbo.QBM_F G I BitPatternXOrigin("|Dynamic|", 0) ist, bleibt erhalten283insert into @SourceData(284 IsUpcommingContent, XOriginAfter285 , Element, AssignedElement, XOriginBefore286 )287288 select 1, Xorigin & '289 ,290 @StringPatternDynamicInv,291 '292 , @UID_Org, UID_',293 @ObjectClass,294 ' , 0295 from ',296 @ObjectClass,297 'InBaseTree298 where UID_Org = @UID_Org299 and Xorigin & ',300 @StringPatternDynamicInv,301 ' > 0302303-- Blacklist304if @BlackListExists = 1305 begin306 update QERDynamicGroupBlackList307 set IsAssignedByOthers = sign(len(ISNULL(s.element, '''')))308 , XDateUpdated = @XDate309 , XUserUpdated = @XUser310 from QERDynamicGroupBlackList b left outer join @SourceData s on b.UID_Person = s.AssignedElement311 and s.IsUpcommingContent = 1312 and s.XOriginAfter & '313 ,314 @StringPatternDynamicInv,315 ' > 0316 where b.UID_DynamicGroup = @UID_DynamicGroup317 and b.IsAssignedByOthers <> sign(len(ISNULL(s.element, '''')))318 end319-- / Blacklist320321insert into @SourceData(322 IsUpcommingContent, XOriginBefore, XOriginAfter323 , Element, AssignedElement324 )325 select 1, 0, '326 + @StringPatternDynamic + ' 327 , @UID_Org , UID_',328 @ObjectClass,329 ' 330from ',331 @ObjectClass,332 ' 333 where (334-- Start Usercondition335')336 SELECT337 @content2 = CONCAT('338339-- End UserCondition340)341',342 @cons,343 ' and XMarkedForDeletion & ' + @StringPatternDelay + ' = 0344',345 @preprop,346 ' and ( 0 = 1 )347348-- BlackList349if @BlackListExists = 1350 begin351 update QERDynamicGroupBlackList352 set IsNotMatched = sign(len(isnull(s.AssignedElement, ''''))) ^ 1353 , XDateUpdated = @XDate354 , XUserUpdated = @XUser355 from QERDynamicGroupBlackList b left outer join @SourceData s on b.UID_Person = s.AssignedElement356 and s.IsUpcommingContent = 1357 and s.XOriginAfter = '358 ,359 @StringPatternDynamic,360 ' 361 where b.UID_DynamicGroup = @UID_DynamicGroup362 and b.IsNotMatched <> sign(len(isnull(s.AssignedElement, ''''))) ^ 1363 364 delete /* algorithmisch notwendig */ @Sourcedata 365 from @Sourcedata s join QERDynamicGroupBlackList b on b.UID_Person = s.AssignedElement366 and s.IsUpcommingContent = 1367 and s.XOriginAfter = '368 ,369 @StringPatternDynamic,370 ' 371 where b.UID_DynamicGroup = @UID_DynamicGroup372373 end374-- / Blacklist375')376 SELECT377 @ProcNameRechneAlle = dbo.QER_FCVDynGuidToProcName(@UID_DynamicGroup,378 'All')379 IF @DebugSwitch > 0380 BEGIN381 print '===========================' print isnull(@Declarations,382 '<@Declarations >') print isnull(@content1,383 '<@content1>') print isnull(@whereclause,384 '<@whereclause >') print isnull(@content2,385 '<@content2 >') print isnull(@contentDN1,386 '<@contentDN1>') print '==========================='387 END388 SELECT389 @SQLcmd = dbo.QBM_FSQProcedureDef(@ProcNameRechneAlle,390 '@SlotNumber int',391 @Declarations + @ContentStandardsA + @content1 + @whereclause + @content2 + @contentDN1,392 N 'Dynamic Role (whole role): ' + @Displayname)393 IF @DebugSwitch > 0394 BEGIN395 print isnull(@SQLcmd,396 ' <keine Prozedur>')397 END398 EXEC QBM_PSQLCreate @ProcNameRechneAlle,399 'P',400 @SQLcmd,401 @UnComment = 0402 SELECT403 @content1 = CONCAT('404-- 16147 wenn Zieltabelle deaktiviert machen wir gar nichts405 ',406 @preprop,407 ' goto endLabel408409insert into @SourceData(410 IsUpcommingContent, XOriginAfter411 , Element, AssignedElement, XOriginBefore412 )413414 select 0, 0415 , @UID_Org, UID_'416 ,417 @ObjectClass,418 ' , XOrigin419 from ',420 @ObjectClass,421 'InBaseTree join @DBQueueCurrent xxp on ',422 @ObjectClass,423 'InBaseTree.UID_',424 @ObjectClass,425 ' = xxp.UID_Parameter426 --and xxp.UID_SubParameter = @UID_DynamicGroup427 where ',428 @ObjectClass,429 'InBaseTree.UID_Org = @UID_Org430431insert into @SourceData(432 IsUpcommingContent, XOriginAfter433 , Element, AssignedElement, XOriginBefore434 )435436 select 1, XOrigin & '437 ,438 @StringPatternDynamicInv,439 '440 , @UID_Org, UID_',441 @ObjectClass,442 ' , 0443 from ',444 @ObjectClass,445 'InBaseTree join @DBQueueCurrent xxp on ',446 @ObjectClass,447 'InBaseTree.UID_',448 @ObjectClass,449 ' = xxp.UID_Parameter450 --and xxp.UID_SubParameter = @UID_DynamicGroup451 where ',452 @ObjectClass,453 'InBaseTree.UID_Org = @UID_Org454 and XOrigin & ',455 @StringPatternDynamicInv,456 ' > 0457458-- Blacklist459if @BlackListExists = 1460 begin461 update QERDynamicGroupBlackList462 set IsAssignedByOthers = sign(len(ISNULL(s.element, '''')))463 , XDateUpdated = @XDate464 , XUserUpdated = @XUser465 from QERDynamicGroupBlackList b join @DBQueueCurrent xxp on b.UID_Person = xxp.UID_Parameter466 --and xxp.UID_SubParameter = @UID_DynamicGroup467 left outer join @SourceData s on b.UID_Person = s.AssignedElement468 and s.IsUpcommingContent = 1469 and s.XOriginAfter & '470 ,471 @StringPatternDynamicInv,472 ' > 0473 where b.UID_DynamicGroup = @UID_DynamicGroup474 and b.IsAssignedByOthers <> sign(len(ISNULL(s.element, '''')))475 end476-- / Blacklist477478insert into @SourceData(479 IsUpcommingContent, XOriginBefore, XOriginAfter480 , Element, AssignedElement481 )482 select 1, 0, '483 ,484 @StringPatternDynamic,485 ' 486 , @UID_Org , UID_',487 @ObjectClass,488 ' 489from ',490 @ObjectClass,491 ' join @DBQueueCurrent xxp on ',492 @ObjectClass,493 '.UID_',494 @ObjectClass,495 ' = xxp.UID_Parameter496 --and xxp.UID_SubParameter = @UID_DynamicGroup497 where (498-- Start Usercondition499')500 SELECT501 @content2 = CONCAT('502503-- End UserCondition504)505',506 @cons,507 ' and XMarkedForDeletion & ' + @StringPatternDelay + ' = 0508',509 @preprop,510 ' and ( 0 = 1 )511-- Blacklist512if @BlackListExists = 1513 begin514 update QERDynamicGroupBlackList515 set IsNotMatched = sign(len(isnull(s.AssignedElement, ''''))) ^ 1516 , XDateUpdated = @XDate517 , XUserUpdated = @XUser518 from QERDynamicGroupBlackList b join @DBQueueCurrent xxp on b.UID_Person = xxp.UID_Parameter519 --and xxp.UID_SubParameter = @UID_DynamicGroup520 left outer join @SourceData s on b.UID_Person = s.AssignedElement521 and s.IsUpcommingContent = 1522 and s.XOriginAfter = '523 ,524 @StringPatternDynamic,525 ' 526 where b.UID_DynamicGroup = @UID_DynamicGroup527 and b.IsNotMatched <> sign(len(isnull(s.AssignedElement, ''''))) ^ 1528529 delete /* algorithmisch notwendig */ @Sourcedata 530 from @Sourcedata s join QERDynamicGroupBlackList b on b.UID_Person = s.AssignedElement531 and s.IsUpcommingContent = 1532 and s.XOriginAfter = '533 ,534 @StringPatternDynamic,535 ' 536 where b.UID_DynamicGroup = @UID_DynamicGroup537 538 end539-- / Blacklist540')541 SELECT542 @ProcNameRechneDetail = dbo.QER_FCVDynGuidToProcName(@UID_DynamicGroup,543 'Detail')544 SELECT545 @SQLcmd = dbo.QBM_FSQProcedureDef(@ProcNameRechneDetail,546 '@SlotNumber int',547 @Declarations + @ContentStandardsE + @content1 + @whereclause + @content2 + @contentDN1,548 N 'Dynamic Role: (given Elements): ' + @Displayname)549 IF @DebugSwitch > 0550 BEGIN551 print isnull(@SQLcmd,552 ' <keine Prozedur>')553 END554 EXEC QBM_PSQLCreate @ProcNameRechneDetail,555 'P',556 @SQLcmd,557 @UnComment = 0558 END TRY559 BEGIN CATCH560 EXEC QBM_PSessionErrorAdd DEFAULT561 DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()562 RAISERROR(@Rethrow,563 18,564 1)565 WITH NOWAIT566 END CATCH567 ende:568 RETURN569ENDOpen raw exported source
1 create procedure QER_PDynamicGroupMakeProc_hlp (@UID_DynamicGroup varchar(38)) as begin declare @DisplayName nvarchar(255) declare @ObjectClass2 varchar(30) declare @IsdeactivatedByPreprocessor bit declare @WhereClause nvarchar(max) declare @UID_BaseTreeOrg varchar(38) declare @preprop nvarchar3(16) declare @cons nvarchar(16) declare @ProcNameRechneAlle nvarchar(64) declare @ProcNameRechneDetail nvarchar(64) declare @Declarations nvarchar(max)4 declare @SQLcmd nvarchar(max) declare @content1 nvarchar(max) declare @content2 nvarchar(max) declare @contentDN1 nvarchar(max) declare @DebugSwitch5 int = 0 declare @DebugLevel char(1) = 'W' declare @StringPatternDynamic varchar(16) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin6('|Dynamic|',0)), 0) declare @StringPatternDynamicInv varchar(16) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Dynamic|'7,1)), 0) declare @StringPatternDelay varchar(16) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',0)), 0) declare8 @StringPatternDelayInv varchar(16) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',1)), 0) SET XACT_ABORT9 OFF BEGIN TRY if @DebugSwitch > 0 begin exec QER_PDynamicGroupMakeProc_Drop @UID_DynamicGroup end select @DisplayName = isnull(dg.DisplayName, '<no Display>'10), @ObjectClass = t.TableName, @whereclause = case b.IsInvalidForDynamicGroup when 1 then ' 1 = 0 ' else dg.WhereClause end , @UID_BaseTreeOrg = dbo.QBM_FCVObjectkeyToElement11('ColumnValue1', dg.ObjectKeyBaseTree) from DynamicGroup dg join DialogTable t with (readpast) on dg.UID_DialogTableObjectClass = t.UID_DialogTable join12 BaseTree b on dg.ObjectKeyBaseTree = b.XObjectKey where UID_DynamicGroup = @UID_DynamicGroup if 1 = @IsdeactivatedByPreprocessor begin select @preprop13 = '' end else begin select @preprop = N'--' end if @ObjectClass in ('Hardware', 'Person') begin select @cons = '' end else begin select @cons = N'--' 14end select @Declarations = concat('15 declare @GenProcID varchar(38)16 declare @Sourcedata QBM_YDataForDelta17 , @CountDeltaQantity int 18 , @CountDeltaOrigin int 19 declare @BlackListExists bit = 020 declare @DBQueueCurrent QBM_YDBQueueCurrent21 declare @UID_Org varchar(38) = '''22, @UID_BaseTreeOrg , '''23 declare @UID_DynamicGroup varchar(38) = ''', @UID_DynamicGroup , '''24 declare @XUser nvarchar(64) = object_name(@@procid)25 declare @Xdate datetime = getutcdate()26 27'28) declare @ContentStandardsA nvarchar(max) = concat('29--3407630 if exists (select top 1 131 from DynamicGroup b32 where b.UID_DynamicGroup = @UID_DynamicGroup 33 and b.IsRecalculationDeactivated = 134 )35 begin36 goto EndLabel37 end3839 insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID)40 select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID41 from QBMDBQueueCurrent cu with (readpast)42 where cu.SlotNumber = @SlotNumber4344 if @@rowcount = 045 begin46 goto EndLabel47 end4849 if exists (select top 1 150 from QERDynamicGroupBlackList b51 where b.UID_DynamicGroup = @UID_DynamicGroup 52 )53 begin54 select @BlackListExists = 155 end56'57, '') declare @ContentStandardsE nvarchar(max) = concat('58--3407659 if exists (select top 1 160 from DynamicGroup b61 where b.UID_DynamicGroup = @UID_DynamicGroup 62 and b.IsRecalculationDeactivated = 163 )64 begin65 goto EndLabel66 end6768 insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID)69 select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID70 from QBMDBQueueCurrent cu with (readpast)71 where cu.SlotNumber = @SlotNumber72 and cu.UID_SubParameter = @UID_DynamicGroup7374 if @@rowcount = 075 begin76 goto EndLabel77 end7879 if exists (select top 1 180 from QERDynamicGroupBlackList b81 where b.UID_DynamicGroup = @UID_DynamicGroup 82 )83 begin84 select @BlackListExists = 185 end86'87, '') select @contentDN1 = concat('8889 if exists (select top 1 190 from BaseTreeExcludesBasetree beb91 where beb.UID_OrgExcluded = @UID_Org92 )93 begin94 -- 3372095 delete /* algorithmisch notwendig */ @Sourcedata 96 from @Sourcedata s join BaseTreeExcludesBasetree beb on s.Element = beb.UID_OrgExcluded97 join '98, @ObjectClass , 'InBaseTree pe on pe.UID_Org = beb.UID_Org99 and pe.UID_', @ObjectClass , ' = s.AssignedElement100 and pe.XOrigin > 0101 where s.IsUpcommingContent = 1102 end103104 if exists (select top 1 1105 from BaseTreeExcludesBasetree beb106 where beb.UID_Org = @UID_Org107 )108 begin109 -- 33720, Nachtrag bidirektional110 delete /* algorithmisch notwendig */ @Sourcedata 111 from @Sourcedata s join BaseTreeExcludesBasetree beb on s.Element = beb.UID_Org112 join '113, @ObjectClass , 'InBaseTree pe on pe.UID_Org = beb.UID_OrgExcluded114 and pe.UID_', @ObjectClass , ' = s.AssignedElement115 and pe.XOrigin > 0116 where s.IsUpcommingContent = 1117 end118 119 exec QBM_PDBQueueCalculateDelta @SourceData,120 @DeltaQuantity = 0,121 @DeltaDelete = 0,122 @DeltaInsert = 1,123 @DeltaOrigin = 1, 124 @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output125 , @UseIsInEffect = 0126 , @SlotNumber = @SlotNumber127 , @DBQueueCurrentExtern = @DBQueueCurrent128129 select @ret = 0130 select top 1 @GenProcID = cu.GenProcID131 from @DBQueueCurrent cu 132 where cu.UID_SubParameter = @UID_DynamicGroup133134 if isnull(@GenProcID, '''') = ''''135 begin136 select @GenProcID = newid() 137 end138139-- 16147 wenn Zieltabelle deaktiviert machen wir gar nichts140 '141, @preprop , ' goto endLabel142143 if @CountDeltaOrigin > 0 144 begin145 update #QBMDeltaOrigin146 set GenProcID = @GenProcID 147 where GenProcID is null148 exec QBM_PMNTableOriginUpdate '''149, @ObjectClass , 'InBaseTree'', ''UID_Org'', ''UID_', @ObjectClass , '''150 end151152 if @CountDeltaQantity > 0153 begin154 update #QBMDeltaInsert155 set GenProcID = @GenProcID 156 where GenProcID is null 157 exec QER_PMNTableAddViewProperties '''158, @ObjectClass , 'InBaseTree''159 exec QBM_PMNTableInsert ''', @ObjectClass , 'InBaseTree'', ''UID_Org'', ''UID_', @ObjectClass , ''', @TargetIsView = 1160 end 161162'163) if @DebugSwitch > 0 begin print isnull(@contentDN1, '< kein @contentDN1 @UID_BaseTreeOrg>') end select @content1 = Concat('164-- 16147 wenn Zieltabelle deaktiviert machen wir gar nichts165 '166, @preprop , ' goto endLabel167168insert into @SourceData(169 IsUpcommingContent, XOriginAfter170 , Element, AssignedElement, XOriginBefore171 )172173 select 0, 0174 , @UID_Org, UID_'175, @ObjectClass , ' , Xorigin176 from ', @ObjectClass , 'InBaseTree177 where UID_Org = @UID_Org178179-- was nicht dbo.QBM_F G I BitPatternXOrigin("|Dynamic|", 0) ist, bleibt erhalten180insert into @SourceData(181 IsUpcommingContent, XOriginAfter182 , Element, AssignedElement, XOriginBefore183 )184185 select 1, Xorigin & '186 , @StringPatternDynamicInv , '187 , @UID_Org, UID_', @ObjectClass , ' , 0188 from ', @ObjectClass , 'InBaseTree189 where UID_Org = @UID_Org190 and Xorigin & '191 , @StringPatternDynamicInv , ' > 0192193-- Blacklist194if @BlackListExists = 1195 begin196 update QERDynamicGroupBlackList197 set IsAssignedByOthers = sign(len(ISNULL(s.element, '''')))198 , XDateUpdated = @XDate199 , XUserUpdated = @XUser200 from QERDynamicGroupBlackList b left outer join @SourceData s on b.UID_Person = s.AssignedElement201 and s.IsUpcommingContent = 1202 and s.XOriginAfter & '203 , @StringPatternDynamicInv, ' > 0204 where b.UID_DynamicGroup = @UID_DynamicGroup205 and b.IsAssignedByOthers <> sign(len(ISNULL(s.element, '''')))206 end207-- / Blacklist208209insert into @SourceData(210 IsUpcommingContent, XOriginBefore, XOriginAfter211 , Element, AssignedElement212 )213 select 1, 0, '214 + @StringPatternDynamic + ' 215 , @UID_Org , UID_', @ObjectClass , ' 216from ', @ObjectClass , ' 217 where (218-- Start Usercondition219') select @content2220 = concat('221222-- End UserCondition223)224', @cons, ' and XMarkedForDeletion & ' + @StringPatternDelay + ' = 0225', @preprop , ' and ( 0 = 1 )226227-- BlackList228if @BlackListExists = 1229 begin230 update QERDynamicGroupBlackList231 set IsNotMatched = sign(len(isnull(s.AssignedElement, ''''))) ^ 1232 , XDateUpdated = @XDate233 , XUserUpdated = @XUser234 from QERDynamicGroupBlackList b left outer join @SourceData s on b.UID_Person = s.AssignedElement235 and s.IsUpcommingContent = 1236 and s.XOriginAfter = '237 , @StringPatternDynamic , ' 238 where b.UID_DynamicGroup = @UID_DynamicGroup239 and b.IsNotMatched <> sign(len(isnull(s.AssignedElement, ''''))) ^ 1240 241 delete /* algorithmisch notwendig */ @Sourcedata 242 from @Sourcedata s join QERDynamicGroupBlackList b on b.UID_Person = s.AssignedElement243 and s.IsUpcommingContent = 1244 and s.XOriginAfter = '245 , @StringPatternDynamic , ' 246 where b.UID_DynamicGroup = @UID_DynamicGroup247248 end249-- / Blacklist250') select @ProcNameRechneAlle = dbo.QER_FCVDynGuidToProcName251(@UID_DynamicGroup, 'All') if @DebugSwitch > 0 begin print '===========================' print isnull(@Declarations , '<@Declarations >') print isnull(@content1252, '<@content1>') print isnull(@whereclause , '<@whereclause >') print isnull(@content2 , '<@content2 >') print isnull(@contentDN1, '<@contentDN1>') print253 '===========================' end select @SQLcmd = dbo.QBM_FSQProcedureDef(@ProcNameRechneAlle, '@SlotNumber int', @Declarations + @ContentStandardsA 254+ @content1 + @whereclause + @content2 + @contentDN1 , N'Dynamic Role (whole role): ' + @Displayname ) if @DebugSwitch > 0 begin print isnull(@SQLcmd,255 ' <keine Prozedur>') end exec QBM_PSQLCreate @ProcNameRechneAlle, 'P', @SQLcmd, @UnComment = 0 select @content1 = concat('256-- 16147 wenn Zieltabelle deaktiviert machen wir gar nichts257 '258, @preprop , ' goto endLabel259260insert into @SourceData(261 IsUpcommingContent, XOriginAfter262 , Element, AssignedElement, XOriginBefore263 )264265 select 0, 0266 , @UID_Org, UID_'267, @ObjectClass , ' , XOrigin268 from ', @ObjectClass , 'InBaseTree join @DBQueueCurrent xxp on ', @ObjectClass , 'InBaseTree.UID_', @ObjectClass , ' = xxp.UID_Parameter269 --and xxp.UID_SubParameter = @UID_DynamicGroup270 where '271, @ObjectClass , 'InBaseTree.UID_Org = @UID_Org272273insert into @SourceData(274 IsUpcommingContent, XOriginAfter275 , Element, AssignedElement, XOriginBefore276 )277278 select 1, XOrigin & '279 , @StringPatternDynamicInv , '280 , @UID_Org, UID_', @ObjectClass , ' , 0281 from ', @ObjectClass , 'InBaseTree join @DBQueueCurrent xxp on ', @ObjectClass282 , 'InBaseTree.UID_', @ObjectClass , ' = xxp.UID_Parameter283 --and xxp.UID_SubParameter = @UID_DynamicGroup284 where ', @ObjectClass , 285'InBaseTree.UID_Org = @UID_Org286 and XOrigin & ' , @StringPatternDynamicInv , ' > 0287288-- Blacklist289if @BlackListExists = 1290 begin291 update QERDynamicGroupBlackList292 set IsAssignedByOthers = sign(len(ISNULL(s.element, '''')))293 , XDateUpdated = @XDate294 , XUserUpdated = @XUser295 from QERDynamicGroupBlackList b join @DBQueueCurrent xxp on b.UID_Person = xxp.UID_Parameter296 --and xxp.UID_SubParameter = @UID_DynamicGroup297 left outer join @SourceData s on b.UID_Person = s.AssignedElement298 and s.IsUpcommingContent = 1299 and s.XOriginAfter & '300, @StringPatternDynamicInv, ' > 0301 where b.UID_DynamicGroup = @UID_DynamicGroup302 and b.IsAssignedByOthers <> sign(len(ISNULL(s.element, '''')))303 end304-- / Blacklist305306insert into @SourceData(307 IsUpcommingContent, XOriginBefore, XOriginAfter308 , Element, AssignedElement309 )310 select 1, 0, '311 , @StringPatternDynamic , ' 312 , @UID_Org , UID_', @ObjectClass , ' 313from ', @ObjectClass , ' join @DBQueueCurrent xxp on ', @ObjectClass , '.UID_'314, @ObjectClass , ' = xxp.UID_Parameter315 --and xxp.UID_SubParameter = @UID_DynamicGroup316 where (317-- Start Usercondition318' ) select @content2319 = concat('320321-- End UserCondition322)323', @cons, ' and XMarkedForDeletion & ' + @StringPatternDelay + ' = 0324', @preprop , ' and ( 0 = 1 )325-- Blacklist326if @BlackListExists = 1327 begin328 update QERDynamicGroupBlackList329 set IsNotMatched = sign(len(isnull(s.AssignedElement, ''''))) ^ 1330 , XDateUpdated = @XDate331 , XUserUpdated = @XUser332 from QERDynamicGroupBlackList b join @DBQueueCurrent xxp on b.UID_Person = xxp.UID_Parameter333 --and xxp.UID_SubParameter = @UID_DynamicGroup334 left outer join @SourceData s on b.UID_Person = s.AssignedElement335 and s.IsUpcommingContent = 1336 and s.XOriginAfter = '337 , @StringPatternDynamic , ' 338 where b.UID_DynamicGroup = @UID_DynamicGroup339 and b.IsNotMatched <> sign(len(isnull(s.AssignedElement, ''''))) ^ 1340341 delete /* algorithmisch notwendig */ @Sourcedata 342 from @Sourcedata s join QERDynamicGroupBlackList b on b.UID_Person = s.AssignedElement343 and s.IsUpcommingContent = 1344 and s.XOriginAfter = '345 , @StringPatternDynamic , ' 346 where b.UID_DynamicGroup = @UID_DynamicGroup347 348 end349-- / Blacklist350' ) select @ProcNameRechneDetail = dbo.QER_FCVDynGuidToProcName351(@UID_DynamicGroup, 'Detail') select @SQLcmd = dbo.QBM_FSQProcedureDef(@ProcNameRechneDetail, '@SlotNumber int', @Declarations + @ContentStandardsE + @content1352 + @whereclause + @content2 + @contentDN1 , N'Dynamic Role: (given Elements): ' + @Displayname ) if @DebugSwitch > 0 begin print isnull(@SQLcmd, ' <keine Prozedur>'353) end exec QBM_PSQLCreate @ProcNameRechneDetail, 'P', @SQLcmd, @UnComment = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar354(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return end 355