dbo.QER_PITShopHelperFill_bulk

1CREATE PROCEDURE QER_PITShopHelperFill_bulk(2  @PWOs QBM_YParameterlist READONLY3)4  WITH5EXECUTE6AS7'dbo' AS8BEGIN9  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')10  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')11  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))12  DECLARE @ElementLast int13  DECLARE @ElementBufferMulti_1 QBM_YCursorBuffer14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  DECLARE @ElementCountPWO int17  DECLARE @ElementIndexPWO int18  DECLARE @DBQueueElements_CheckDecision QBM_YDBQueueRaw19  DECLARE @DBQueueElements_MakeDecisionOC QBM_YDBQueueRaw20  DECLARE @DBQueueElements_MakeDecisionOH QBM_YDBQueueRaw21  DECLARE @DBQueueElements_MakeDecisionEX QBM_YDBQueueRaw22  DECLARE @PWOToCheck QER_YPWOBufferForHelper23  DECLARE @PWOHelperPWO_Old QER_YPWOHelper24  DECLARE @PWOHelperPWO_New QER_YPWOHelper25  DECLARE @hilfstab QER_YPWOHelperCPL26  DECLARE @CountItemsException int27  DECLARE @UID_PWO varchar(38)28  DECLARE @SubLevelNumber int29  DECLARE @LevelNumber int30  DECLARE @LevelNumber_alt int31  DECLARE @uid_complianceRule varchar(38)32  DECLARE @uid_complianceRule_max varchar(38)33  DECLARE @UID_PersonNonCompliant varchar(38)34  DECLARE @FunctionName varchar(30)35  DECLARE @UID_PWODecisionRule varchar(38)36  DECLARE @UID_QERWorkingStep varchar(38)37  DECLARE @countApproverNeeded int38  DECLARE @SQLCmd nvarchar(max)39  DECLARE @countApproverAvailable int40  DECLARE @GenProcIDToUse varchar(38)41  DECLARE @DecisionLevelToUse int42  DECLARE @SonderBehandlungException BIT = 043  DECLARE @DebugSwitch int = 044  DECLARE @cfgPersonOrderedNoDecide BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide'))45  DECLARE @cfgPersonInsertedNoDecide BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide'))46  DECLARE @OrderState nvarchar(16)47  DECLARE @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',48  0)49  DECLARE @XUser nvarchar(64) = object_name(@@procid)50  DECLARE @Xdate datetime = getutcdate()51  DECLARE @PWOAndLevel TABLE(UID_PersonWantsOrg varchar(38) collate database_default,52  LevelNumber int DEFAULT 0,53  index PWOAndLevel1(UID_PersonWantsOrg))54  DECLARE @Calltype varchar(1)55  SET XACT_ABORT OFF56  BEGIN TRY57    drop TABLE58    IF EXISTS #PWOHelperPWO_New59    CREATE TABLE #PWOHelperPWO_New(60      UID_PWODecisionRule varchar(38) collate database_default,61      uid_personHead varchar(38) collate database_default,62      uid_QERWorkingStep varchar(38) collate database_default,63      levelnumber int DEFAULT 0,64      Sublevelnumber int DEFAULT 0,65      istoInsert BIT DEFAULT 0,66      UID_PWORulerOrigin varchar(38) collate database_default,67      RulerLevel int DEFAULT 0,68      UID_PersonWantsOrg varchar(38) collate database_default,69      UID_PersonSubstituteSender varchar(38) collate database_default70    )71    INSERT INTO @PWOToCheck(UID_PersonWantsOrg,72    GenProcid,73    CurrentLevelOnly,74    IsNewDecisionLevel,75    makeEmpty,76    Decisionlevel,77    UID_QERWorkingMethod,78    Orderstate,79    SonderlockeException,80    UID_ITShopOrgFinal,81    validFrom,82    ValidUntil,83    isOrderforWorkDesk,84    UID_PersonOrdered,85    UID_OrgParent,86    UID_PersonInserted,87    isReserved,88    UID_PersonHead89  )90  SELECT91    DISTINCT p.Parameter1,92    dbo.QER_FGIGenProcIDForPWO(isnull(pwo.GenProcID,93    p.Parameter2),94    p.Parameter2,95    @CfgUseGenProcID96    )97    AS98    GenProcid,99    dbo.QBM_FCVStringToInt(p.Parameter3,100    0) AS CurrentLevelOnly,101    p.HasContentFull AS IsNewDecisionLevel,102CASE103  WHEN pwo.UID_PersonWantsOrg IS NULL THEN1041105  WHEN pwo.OrderState NOT IN('OrderProduct',106'OrderProlongate',107'OrderUnsubscribe') THEN1081109ELSE 0110END AS makeEmpty,111pwo.decisionlevel,112pwo.UID_QERWorkingMethod,113pwo.OrderState,114sign(len(isnull(sOC.UID_QERWorkingStep, ''))) AS SonderlockeException,115pwo.UID_ITShopOrgFinal,116pwo.ValidFrom,117pwo.ValidUntil,118isOrderforWorkDesk,119UID_PersonOrdered,120UID_OrgParent,121pwo.UID_PersonInserted,122pwo.IsReserved,123UID_PersonHead124FROM @PWOs p125LEFT126OUTER127JOIN PersonWantsOrg pwo128  ON p.Parameter1 = pwo.UID_PersonWantsOrg129LEFT130OUTER131JOIN QERWorkingStep sOC132  ON pwo.UID_QERWorkingMethod = sOC.UID_QERWorkingMethod AND sOC.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',133'CPL-PWODecisionRule-OH') AND(dbo.QBM_FCVStringToInt(p.Parameter3, 0) = 0 OR(sOC.LevelNumber = pwo.DecisionLevel))134SELECT @ElementCountPWO = @@ROWCOUNT135INSERT INTO @PWOAndLevel(UID_PersonWantsOrg,136LevelNumber)137SELECT138  h.UID_PersonWantsOrg,139  h.LevelNumber140FROM @PWOToCheck pwo141JOIN PWOHelperPWO h142  ON h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg143GROUP BY h.UID_PersonWantsOrg,144h.LevelNumber,145pwo.DecisionLevel146HAVING((max(pwo.CurrentLevelOnly) = 1 AND pwo.DecisionLevel = h.LevelNumber) OR(max(pwo.CurrentLevelOnly) = 0 AND(MAX(isnull(h.Decision,147'')) = '' OR pwo.DecisionLevel = h.LevelNumber)))148UNION149SELECT150  pwo.UID_PersonWantsOrg,151  s.LevelNumber152FROM @PWOToCheck pwo153JOIN QERWorkingStep s154  ON pwo.UID_QERWorkingMethod = s.UID_QERWorkingMethod155WHERE156  NOT EXISTS(157SELECT TOP 1 1158FROM PWOHelperPWO h159WHERE160  h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg AND h.LevelNumber = s.LevelNumber AND h.SubLevelNumber = s.SubLevelNumber)161IF @DebugSwitch > 0162BEGIN163  print 'initialbefüllung @PWOToCheck'164  SELECT *165  FROM @PWOToCheck166END167INSERT INTO @PWOHelperPWO_Old(UID_PWODecisionRule,168uid_personHead,169levelnumber,170SubLevelNumber,171UID_QERWorkingStep,172decision,173uid_complianceRule,174uid_PersonNonCompliant,175UID_PWORulerOrigin,176UID_PersonAdditional,177UID_PersonInsteadOf,178IsFromDelegation,179RulerLevel,180IsToDelete,181UID_PWOHelperPWO,182isToUpdateDecision,183UID_PersonWantsOrg,184NextAutomaticDecision)185SELECT186  isnull(UID_PWODecisionRule,187  ''),188  pwoh.UID_PersonHead,189  levelnumber,190  SubLevelNumber,191  pwoh.uid_QERWorkingStep,192  isnull(pwoh.decision,193  ''),194  uid_complianceRule,195  uid_PersonNonCompliant,196  UID_PWORulerOrigin,197  UID_PersonAdditional,198  UID_PersonInsteadOf,199  IsFromDelegation,200  RulerLevel,201CASE202  WHEN c.makeEmpty = 1 THEN2031204  WHEN pwoh.Decision = '-' THEN2051206ELSE 0207END AS IsToDelete,208pwoh.UID_PWOHelperPWO,209CASE210  WHEN pwoh.Decision IS NULL THEN2111212ELSE 0213END AS isToUpdateDecision,214c.UID_PersonWantsOrg,215pwoh.NextAutomaticDecision216FROM PWOHelperPWO pwoh217  WITH(readpast)218JOIN @PWOToCheck c219  ON pwoh.UID_PersonWantsOrg = c.UID_PersonWantsOrg220WHERE221  EXISTS(222SELECT TOP 1 1223FROM @PWOAndLevel pl224WHERE225  pl.UID_PersonWantsOrg = pwoh.UID_PersonWantsOrg AND pl.LevelNumber = pwoh.LevelNumber) OR c.makeEmpty = 1226UPDATE @PWOToCheck227SET CheckSumHelper = x.CheckSumHelper228FROM @PWOToCheck p229JOIN(230SELECT231  c.UID_PersonWantsOrg,232  checksum_agg(checksum(CONCAT(h.UID_PWOHelperPWO, h.Decision))) AS CheckSumHelper233FROM @PWOToCheck c234LEFT235OUTER236JOIN PWOHelperPWO h237  WITH(readpast)238  ON c.UID_PersonWantsOrg = h.UID_PersonWantsOrg239GROUP BY c.UID_PersonWantsOrg) AS x240  ON p.UID_PersonWantsOrg = x.UID_PersonWantsOrg241IF @DebugSwitch > 0242BEGIN243  print 'initialbefüllung @PWOHelperPWO_Old'244  SELECT *245  FROM @PWOHelperPWO_Old246END247IF EXISTS(248  SELECT TOP 1 1249  FROM @PWOToCheck c250  WHERE251    c.SonderlockeException = 1)252BEGIN253  SELECT @SonderBehandlungException = 1254END255IF @SonderBehandlungException = 1256BEGIN257  INSERT INTO @hilfstab(uid_complianceRule,258  uid_personNonCompliant,259  SubLevelNumber,260  RuleSeverity,261  isHighestSeverity,262  Levelnumber,263  UID_PersonWantsOrg)264  SELECT265    pic.uid_complianceRule,266    pic.uid_person,267    -1,268    isnull(c.RuleSeverity,269    0.0),270    0,271    sOC.Levelnumber,272    pwo.UID_PersonWantsOrg273  FROM HelperPWOCompliance pic274  JOIN @PWOToCheck pwo275    ON pic.uid_personwantsorg = pwo.uid_personwantsorg AND pwo.SonderlockeException = 1276  JOIN ComplianceRule c277    ON pic.uid_complianceRule = c.uid_complianceRule AND c.IsInActive = 0 AND c.IsWorkingCopy = 0278  JOIN QERWorkingStep sOC279    ON sOC.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod AND sOC.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',280  'CPL-PWODecisionRule-OH')281  JOIN QERWorkingStep sCC282    ON sCC.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod AND sCC.UID_PWODecisionRule IN('CPL-PWODecisionRule-CR') AND sCC.levelnumber + sCC.NegativeSteps283  = sOC.Levelnumber284  GROUP BY pwo.UID_PersonWantsOrg,285  pic.uid_complianceRule,286  pic.uid_Person,287  isnull(c.RuleSeverity,288  0.0),289  sOC.Levelnumber290  ORDER BY pwo.UID_PersonWantsOrg,291  pic.uid_complianceRule,292  pic.uid_Person,293  isnull(c.RuleSeverity,294  0.0),295  sOC.Levelnumber296  UPDATE @PWOToCheck297  SET CountItemsException = x.anzahl298  FROM @PWOToCheck c299  JOIN(300  SELECT301    h.UID_PersonWantsOrg,302    count(*) AS anzahl303  FROM @hilfstab h304  GROUP BY h.UID_PersonWantsOrg) AS x305    ON x.UID_PersonWantsOrg = c.UID_PersonWantsOrg306  WHERE307    c.SonderlockeException = 1308  INSERT INTO PWODecisionHistory(UID_PWODecisionHistory,309  XObjectKey,310  UID_PersonRelated,311  UID_PersonWantsOrg,312  DisplayPersonHead,313  ReasonHead,314  DateHead,315  XDateInserted,316  XDateUpdated,317  XUserInserted,318  XUserUpdated,319  DecisionLevel,320  ValidUntil,321  ValidFrom,322  DecisionType,323  IsDecisionBySystem,324  UID_PWODecisionRule,325  OrderState,326  UID_ITShopOrgFinal,327  IsToHideInHistory,328  UID_ComplianceRule)329  SELECT330    c.UID_HelperPWOCompliance,331    dbo.QBM_FCVElementToObjectKey1('PWODecisionHistory',332    'UID_PWODecisionHistory',333    c.UID_HelperPWOCompliance),334    c.UID_Person,335    pwo.UID_PersonWantsOrg,336    'compliance violation detected',337    'compliance violation detected',338    GETUTCDATE(),339    GETUTCDATE(),340    GETUTCDATE(),341    'sa',342    'sa',343    pwo.DecisionLevel,344    pwo.ValidUntil,345    pwo.ValidFrom,346    'Compliance',347    1,348    NULL,349    pwo.OrderState,350    pwo.UID_ITShopOrgFinal,351    1,352    c.UID_ComplianceRule353  FROM HelperPWOCompliance c354  JOIN @PWOToCheck pwo355    ON c.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg356  WHERE357    NOT EXISTS(358  SELECT TOP 1 1359  FROM PWODecisionHistory h360  WHERE361    h.UID_PWODecisionHistory = c.UID_HelperPWOCompliance) AND NOT EXISTS(362  SELECT TOP 1 1363  FROM PWODecisionHistory h364  WHERE365    h.UID_ComplianceRule = c.UID_ComplianceRule AND h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg)366  SELECT @ElementIndexPWO = 1367  WHILE @ElementIndexPWO <= @ElementCountPWO368  BEGIN369    SELECT370      TOP 1 @CountItemsException = c.CountItemsException,371      @UID_PWO = c.UID_PersonWantsOrg372    FROM @PWOToCheck c373    WHERE374      c.ElementIndex = @ElementIndexPWO375    IF @CountItemsException > 0376    BEGIN377      SELECT @SubLevelNumber = 0378      SELECT @Levelnumber_alt = -100379      DELETE @ElementBufferMulti_1380      INSERT INTO @ElementBufferMulti_1(Int1,381      UID1,382      UID2)383      SELECT384        h.Levelnumber,385        h.UID_ComplianceRule,386        h.UID_PersonNonCompliant387      FROM @hilfstab h388      WHERE389        h.UID_PersonWantsOrg = @UID_PWO390      ORDER BY h.Levelnumber,391      h.UID_ComplianceRule,392      h.UID_PersonNonCompliant393      SELECT @ElementCount = @@ROWCOUNT394      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1395      SELECT @ElementLast = @@IDENTITY396      WHILE @ElementIndex <= @ElementLast397      BEGIN398        SELECT399          TOP 1 @Levelnumber = bu.Int1,400          @uid_complianceRule = bu.UID1,401          @UID_PersonNonCompliant = bu.UID2402        FROM @ElementBufferMulti_1 bu403        WHERE404          bu.ElementIndex = @ElementIndex405        IF @levelnumber <> @Levelnumber_alt406        BEGIN407          SELECT @levelnumber_alt = @levelnumber408          SELECT @SubLevelNumber = 0409          SELECT TOP 1 @uid_complianceRule_max = h.UID_ComplianceRule410          FROM @hilfstab h411          WHERE412            h.levelnumber = @levelnumber AND h.UID_PersonWantsOrg = @UID_PWO413          ORDER BY isnull(h.RuleSeverity,414          0.0) DESC,415          uid_compliancerule ASC416          UPDATE @hilfstab417          SET isHighestSeverity = 1418          FROM @hilfstab h419          WHERE420            h.UID_ComplianceRule = @uid_complianceRule_max AND h.Levelnumber = @Levelnumber AND h.UID_PersonWantsOrg = @UID_PWO421        END422        ELSE423        BEGIN424          SELECT @SubLevelNumber = @SubLevelNumber + 1425        END426        UPDATE @Hilfstab427        SET SubLevelNumber = @SubLevelNumber428        FROM @hilfstab h429        WHERE430          h.UID_ComplianceRule = @uid_complianceRule AND UID_PersonNonCompliant = @UID_PersonNonCompliant AND h.Levelnumber = @levelnumber AND h.UID_PersonWantsOrg431        = @UID_PWO432        SELECT @ElementIndex += 1433      END434    END435    SELECT @ElementIndexPWO += 1436  END437END438UPDATE @PWOHelperPWO_Old439SET isToDelete = 1440FROM @PWOHelperPWO_Old zk441JOIN(442SELECT *443FROM @PWOHelperPWO_Old) AS d444  ON zk.UID_PWODecisionRule = d.UID_PWODecisionRule AND zk.uid_QERWorkingStep = d.uid_QERWorkingStep AND zk.levelnumber = d.levelnumber AND zk.Sublevelnumber445= d.Sublevelnumber AND zk.UID_PersonWantsOrg = d.UID_PersonWantsOrg446WHERE447  zk.UID_PersonInsteadOf = d.UID_PersonHead AND d.UID_PersonInsteadOf > ' '448DECLARE @ElementAufruf QBM_YCursorbuffer449INSERT INTO @ElementAufruf(UID1,450UID2,451Ident1,452Int1,453Int2,454Int3,455UID3,456Ident2)457SELECT458  DISTINCT s.UID_PWODecisionRule,459  s.UID_QERWorkingStep,460  dbo.QER_FCVRuleGUIDToFunctionName(s.UID_PWODecisionRule),461  s.LevelNumber,462  s.SubLevelNumber,463  s.CountApprover,464  pwo.UID_PersonWantsOrg,465  pwo.Orderstate466FROM @PWOToCheck pwo467JOIN QERWorkingStep s468  ON s.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod469WHERE470  (pwo.isOrderforWorkDesk = 1 OR EXISTS(471SELECT TOP 1 1472FROM BaseTree bo473JOIN BaseTree sh474  ON bo.uid_ParentOrg = sh.uid_Org AND bo.itshopInfo = 'BO' AND sh.itshopInfo = 'SH'475JOIN BaseTree cu476  ON cu.uid_ParentOrg = sh.uid_Org AND cu.ITShopInfo = 'CU'477JOIN PersonInBaseTree pio478  ON pio.uid_Org = cu.uid_Org AND pwo.uid_PersonOrdered = pio.UID_Person AND pio.XOrigin > 0479WHERE480  bo.uid_Org = pwo.UID_OrgParent)) AND(EXISTS(481SELECT TOP 1 1482FROM @PWOAndLevel pl483WHERE484  pl.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg AND pl.LevelNumber = s.LevelNumber)) AND pwo.makeEmpty = 0485ORDER BY s.UID_PWODecisionRule486SELECT @ElementCount = @@ROWCOUNT487SELECT @ElementIndex = @@IDENTITY - @ElementCount +1488SELECT @ElementLast = @@IDENTITY489WHILE @ElementIndex <= @ElementLast490BEGIN491  SELECT492    TOP 1 @UID_PWODecisionRule = bu.UID1,493    @UID_QERWorkingStep = bu.UID2,494    @FunctionName = bu.Ident1,495    @LevelNumber = bu.Int1,496    @SublevelNumber = bu.Int2,497    @countApproverNeeded = bu.Int3,498    @UID_PWO = bu.UID3,499    @OrderState = bu.Ident2500  FROM @ElementAufruf bu501  WHERE502    bu.ElementIndex = @ElementIndex503  SELECT TOP 1 @Calltype = CASE o.type504  WHEN 'IF' THEN505  'F'506  WHEN 'TF' THEN507  'F'508  WHEN 'P' THEN509  'P'510  ELSE ''511  END512  FROM sys.objects o513  WHERE514    o.name = @FunctionName515  IF @Calltype = ''516  BEGIN517    GOTO zyklusende518  END519  IF @Calltype = 'P'520  BEGIN521    truncate TABLE #PWOHelperPWO_New522  END523  SELECT @SQLcmd = ''524  IF @Calltype = 'P'525  BEGIN526    SELECT527      @SQLCmd = CONCAT('drop table if exists #Ruler_main528									create table #Ruler_main(UID_Person varchar(38) collate database_default529														, UID_PWORulerOrigin varchar(38) collate database_default530														)531								exec '532      ,533      @functionname,534      ' ''',535      @UID_PWO,536      ''', ''',537      @UID_QERWorkingStep,538      '''539									')540  END541  SELECT542    @SQLcmd = CONCAT(@SQLcmd,543    '544					declare @PersonAndLevel QBM_YParameterList545546					insert into @PersonAndLevel (Parameter1, Parameter2, Parameter3)547					select s.uid_person,  max(s.UID_PWORulerOrigin), s.RulerLevel548					  from (549								'550    ,551  CASE @Calltype552    WHEN 'F' THEN553  CONCAT('select a1.uid_person, a1.UID_PWORulerOrigin, ''0'' as RulerLevel554								from dbo.', @functionname,555  ' (''', @UID_PWO, ''', ''', @UID_QERWorkingStep, ''') a1 ')556  ELSE 'select uid_person, UID_PWORulerOrigin, ''0'' as RulerLevel557										from #Ruler_main '558  END,559  '560								union all561								select  pio.UID_Person, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin, ''1'' as Rulerlevel562									from QERWorkingStep s join PersonInBaseTree pio on s.UID_AERoleFallBack = pio.UID_Org563																					and pio.XOrigin > 0564									where s.UID_QERWorkingStep = '''565  ,566  @UID_QERWorkingStep,567  '''568								union all 569								select  pio.UID_Person, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin, ''2'' as Rulerlevel570									from PersonInBaseTree pio571									where pio.UID_Org = ''QER-AEROLE-ITSHOP-INTERVENTION''572									and pio.XOrigin > 0573							) as s574						group by s.uid_person, s.RulerLevel575				'576  ,577  CASE @Calltype578    WHEN 'F' THEN579  ''580    WHEN 'P' THEN581  '582				insert into #PWOHelperPWO_New (UID_PWODecisionRule , uid_personHead, uid_QERWorkingStep, levelnumber, Sublevelnumber, istoInsert, UID_PWORulerOrigin, RulerLevel, UID_PersonWantsOrg, UID_PersonSubstituteSender)'583  ELSE ''584  END,585  '586				select	''',587  @uid_Pwodecisionrule,588  ''' , s.uid_person, ''',589  @UID_QERWorkingStep,590  ''' , ',591  str(@levelnumber),592  ', ',593  str(@Sublevelnumber),594  ', 0, max(s.UID_PWORulerOrigin), dbo.QBM_FCVStringToInt( s.RulerLevel, 0), ''',595  @UID_PWO,596  '''597								-- 31893 ist eine Person sowieso entscheidungsberechtigt und noch einmal über Substitute reingekommen, soll der Eintrag von "sowieso" erhalten bleiben598								, min(isnull(s.UID_PersonSubstituteSender, ''''))599					from (600							select p.Parameter1 as UID_Person, p.Parameter2 as UID_PWORulerOrigin, p.Parameter3 as RulerLevel, null as UID_PersonSubstituteSender601								from @PersonAndLevel p602603							union all604605							select  us.UID_PersonReceiver, us.UID_PersonWantsOrg as UID_PWORulerOrigin, p.Parameter3 as RulerLevel /* string */, us.UID_PersonSender as UID_PersonSubstituteSender606								from @PersonAndLevel p join QERUniversalSubstitute us on p.Parameter1 = us.UID_PersonSender607								where us.IsCurrentlyActive = 1608								and us.UseForITShop = 1609								and not exists (select top 1 1 from @PersonAndLevel pal where pal.Parameter1 = us.UID_PersonReceiver and pal.Parameter3 = p.Parameter3)610							) as s611					group by s.UID_Person, s.RulerLevel612613								'614  )615  IF @Calltype = 'F'616  BEGIN617    INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,618    uid_personHead,619    uid_QERWorkingStep,620    levelnumber,621    Sublevelnumber,622    istoInsert,623    UID_PWORulerOrigin,624    RulerLevel,625    UID_PersonWantsOrg,626    UID_PersonSubstituteSender)627    EXEC sp_executesql @SQLcmd628  END629  IF @Calltype = 'P'630  BEGIN631    EXEC sp_executesql @SQLcmd632    INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,633    uid_personHead,634    uid_QERWorkingStep,635    levelnumber,636    Sublevelnumber,637    istoInsert,638    UID_PWORulerOrigin,639    RulerLevel,640    UID_PersonWantsOrg,641    UID_PersonSubstituteSender)642    SELECT643      UID_PWODecisionRule,644      uid_personHead,645      uid_QERWorkingStep,646      levelnumber,647      Sublevelnumber,648      istoInsert,649      UID_PWORulerOrigin,650      RulerLevel,651      UID_PersonWantsOrg,652      UID_PersonSubstituteSender653    FROM #PWOHelperPWO_New654  END655  UPDATE @PWOHelperPWO_New656  SET UID_PWORulerOrigin = su.UID_PersonWantsOrg657  FROM @PWOHelperPWO_New n658  JOIN QERUniversalSubstitute su659    ON n.UID_PersonHead = su.UID_PersonReceiver AND(su.UseForHeadPerson = 1 OR su.UseForHeadOrg = 1) AND su.IsCurrentlyActive = 1660  WHERE661    n.UID_PWORulerOrigin IS NULL AND EXISTS(662  SELECT TOP 1 1663  FROM @PWOHelperPWO_New se664  WHERE665    se.UID_PersonHead = su.UID_PersonSender AND se.levelnumber = n.levelnumber AND se.Sublevelnumber = n.sublevelnumber)666  IF @cfgPersonOrderedNoDecide = 1667  BEGIN668    DELETE @PWOHelperPWO_New669    FROM @PWOToCheck pwo670    JOIN QER_VPersonsAreMe me671      ON pwo.UID_PersonOrdered = me.UID_PersonOrigin672    JOIN @PWOHelperPWO_New n673      ON me.UID_PersonAlsoMe IN(n.UID_PersonHead,674    n.UID_PersonSubstituteSender) AND n.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg675    JOIN QERWorkingStep s676      ON n.UID_QERWorkingStep = s.UID_QERWorkingStep AND s.IgnoreNoDecideForPerson = 0677    WHERE678      pwo.uid_personwantsorg = @UID_PWO AND((isnull(pwo.UID_PersonInserted, pwo.UID_PersonOrdered) <> pwo.UID_PersonOrdered AND n.UID_PWODecisionRule679    <> 'QER-PWODecisionRule-BR') OR(isnull(pwo.UID_PersonInserted, pwo.UID_PersonOrdered) = pwo.UID_PersonOrdered AND n.UID_PWODecisionRule NOT IN680    ('QER-PWODecisionRule-BS', 'QER-PWODecisionRule-BR')))681  END682  IF @cfgPersonInsertedNoDecide = 1683  BEGIN684    IF @OrderState = 'OrderProduct'685    BEGIN686      DELETE @PWOHelperPWO_New687      FROM @PWOToCheck pwo688      JOIN QER_VPersonsAreMe me689        ON pwo.UID_PersonInserted = me.UID_PersonOrigin690      JOIN @PWOHelperPWO_New n691        ON me.UID_PersonAlsoMe IN(n.UID_PersonHead,692      n.UID_PersonSubstituteSender) AND n.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg693      JOIN QERWorkingStep s694        ON n.UID_QERWorkingStep = s.UID_QERWorkingStep AND s.IgnoreNoDecideForPerson = 0695      WHERE696        pwo.uid_personwantsorg = @UID_PWO AND((isnull(pwo.UID_PersonInserted, pwo.UID_PersonOrdered) <> pwo.UID_PersonOrdered AND n.UID_PWODecisionRule697      <> 'QER-PWODecisionRule-BS') OR(isnull(pwo.UID_PersonInserted, pwo.UID_PersonOrdered) = pwo.UID_PersonOrdered AND n.UID_PWODecisionRule NOT IN698      ('QER-PWODecisionRule-BS', 'QER-PWODecisionRule-BR')))699    END700    IF @OrderState IN('OrderProlongate',701    'OrderUnsubscribe')702    BEGIN703      DELETE @PWOHelperPWO_New704      FROM @PWOToCheck pwo705      JOIN(706      SELECT707        h.UID_PersonWantsOrg,708        substring(max(CONCAT(dbo.QBM_FCVDatetimeToString(h.XDateInserted), h.UID_PersonHead)), 24, 38) AS UID_PersonHead,709        substring(max(CONCAT(dbo.QBM_FCVDatetimeToString(h.XDateInserted), h.OrderState)), 24, 38) AS OrderState710      FROM PWODecisionHistory h711      WHERE712        h.UID_PersonWantsOrg = @UID_PWO AND(h.OrderState = 'OrderProlongate' AND h.DecisionType = 'Prolongate' OR h.OrderState = 'OrderUnsubscribe' AND713      h.DecisionType = 'Unsubscribe') AND h.UID_PersonHead > ' '714      GROUP BY h.UID_PersonWantsOrg) h1715        ON h1.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg AND h1.OrderState = pwo.OrderState716      JOIN QER_VPersonsAreMe me717        ON h1.UID_PersonHead = me.UID_PersonOrigin718      JOIN @PWOHelperPWO_New n719        ON me.UID_PersonAlsoMe = n.uid_personHead AND n.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg720      WHERE721        pwo.UID_PersonWantsOrg = @UID_PWO AND((isnull(pwo.UID_PersonInserted, pwo.UID_PersonOrdered) <> pwo.UID_PersonOrdered AND n.UID_PWODecisionRule722      <> 'QER-PWODecisionRule-BS') OR(isnull(pwo.UID_PersonInserted, pwo.UID_PersonOrdered) = pwo.UID_PersonOrdered AND n.UID_PWODecisionRule NOT IN723      ('QER-PWODecisionRule-BS', 'QER-PWODecisionRule-BR')))724    END725  END726  SELECT TOP 1 @countApproverAvailable = COUNT(*)727  FROM @PWOHelperPWO_New n728  WHERE729    n.RulerLevel = 0 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber AND730  n.UID_PersonWantsOrg = @UID_PWO731  IF @countApproverAvailable >= CASE732  WHEN @countApproverNeeded < 0 THEN733  @countApproverAvailable734  WHEN @countApproverNeeded = 0 THEN735  1736  ELSE @countApproverNeeded737  END AND @countApproverAvailable > 0738  BEGIN739    DELETE @PWOHelperPWO_New740    FROM @PWOHelperPWO_New n741    WHERE742      n.RulerLevel = 1 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber AND743    n.UID_PersonWantsOrg = @UID_PWO744  END745  DELETE @PWOHelperPWO_New746  FROM @PWOHelperPWO_New n747  WHERE748    n.RulerLevel = 0 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber AND749  n.UID_PersonWantsOrg = @UID_PWO AND EXISTS(750  SELECT TOP 1 1751  FROM @PWOHelperPWO_New n1752  WHERE753    n1.RulerLevel = 1 AND n1.uid_QERWorkingStep = @UID_QERWorkingStep AND n1.levelnumber = @LevelNumber AND n1.Sublevelnumber = @SubLevelNumber AND754  n1.uid_personHead = n.uid_personHead AND n1.UID_PersonWantsOrg = @UID_PWO)755  IF @UID_PwoDecisionRule IN(756    SELECT r.UID_PWODecisionRule757    FROM PWODecisionRule r758    WHERE759      r.UID_Task > ' '760    UNION all761    SELECT 'CPL-PWODecisionRule-OC'762    UNION all763    SELECT 'CPL-PWODecisionRule-OH')764  BEGIN765    DELETE @PWOHelperPWO_New766    FROM @PWOHelperPWO_New n767    WHERE768      n.RulerLevel > 0 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber AND769    n.UID_PersonWantsOrg = @UID_PWO770  END771  zyklusende:772  SELECT @ElementIndex += 1773END774IF @SonderBehandlungException = 1775BEGIN776  INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,777  uid_QERWorkingStep,778  levelnumber,779  Sublevelnumber,780  UID_ComplianceRule,781  uid_personHead,782  uid_PersonNoncompliant,783  UID_PWORulerOrigin,784  UID_PersonWantsOrg,785  UID_PersonSubstituteSender)786  SELECT787    s.UID_PWODecisionRule,788    s.uid_QERWorkingStep,789    s.levelnumber,790    ht.SubLevelNumber,791    c.UID_ComplianceRule,792    p.uid_person,793    pic.uid_Person,794    dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) AS UID_PWORulerOrigin,795    pwo.UID_PersonWantsOrg,796    NULL AS UID_PersonSubstituteSender797  FROM @PWOToCheck pwo798  JOIN QERWorkingStep s799    ON s.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod AND s.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',800  'CPL-PWODecisionRule-OH')801  JOIN HelperPWOCompliance pic802    ON pwo.uid_personwantsorg = pic.uid_personwantsorg803  JOIN ComplianceRule c804    ON pic.uid_complianceRule = c.uid_complianceRule AND c.IsInActive = 0 AND c.IsWorkingCopy = 0805  JOIN @Hilfstab ht806    ON ht.uid_complianceRule = c.uid_complianceRule AND ht.levelnumber = s.levelnumber AND ht.uid_PersonNonCompliant = pic.uid_Person AND ht.UID_PersonWantsOrg807  = pwo.UID_PersonWantsOrg808  LEFT809  OUTER810  JOIN personinBaseTree pio811    ON pio.uid_org = c.uid_orgRuler AND pio.XOrigin > 0812  LEFT813  OUTER814  JOIN Person p815    ON p.UID_Person = pio.UID_Person AND p.IsInActive = 0816  WHERE817    pwo.MakeEmpty = 0818  INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,819  uid_QERWorkingStep,820  levelnumber,821  Sublevelnumber,822  UID_ComplianceRule,823  uid_personHead,824  uid_PersonNoncompliant,825  UID_PWORulerOrigin,826  UID_PersonWantsOrg,827  UID_PersonSubstituteSender)828  SELECT829    DISTINCT n.UID_PWODecisionRule,830    n.UID_QERWorkingStep,831    n.LevelNumber,832    n.Sublevelnumber,833    n.UID_ComplianceRule,834    us.UID_PersonReceiver,835    n.UID_PersonNonCompliant,836    NULL AS UID_PWORulerOrigin,837    n.UID_PersonWantsOrg,838    us.UID_PersonSender AS UID_PersonSubstituteSender839  FROM @PWOHelperPWO_New n840  JOIN QERUniversalSubstitute us841    ON n.UID_PersonHead = us.UID_PersonSender842  WHERE843    n.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',844  'CPL-PWODecisionRule-OH') AND us.IsCurrentlyActive = 1 AND us.UseForITShopCompliance = 1 AND NOT EXISTS(845  SELECT TOP 1 1846  FROM @PWOHelperPWO_New e847  WHERE848    e.UID_PersonWantsOrg = n.UID_PersonWantsOrg AND e.UID_PWODecisionRule = n.UID_PWODecisionRule AND e.UID_QERWorkingStep = n.UID_QERWorkingStep AND849  e.LevelNumber = n.LevelNumber AND e.Sublevelnumber = n.Sublevelnumber AND e.UID_PersonNonCompliant = n.UID_PersonNonCompliant AND e.UID_PersonHead850  = us.UID_PersonReceiver)851  IF dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecideCompliance') = '1'852  BEGIN853    DELETE @PWOHelperPWO_New854    FROM @PWOToCheck pwo855    JOIN QER_VPersonsAreMe me856      ON pwo.UID_PersonInserted = me.UID_PersonOrigin857    JOIN @PWOHelperPWO_New n858      ON me.UID_PersonAlsoMe IN(n.UID_PersonHead,859    n.UID_PersonSubstituteSender) AND n.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg860    JOIN QERWorkingStep s861      ON n.UID_QERWorkingStep = s.UID_QERWorkingStep AND s.IgnoreNoDecideForPerson = 0862    WHERE863      n.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',864    'CPL-PWODecisionRule-OH')865  END866  IF dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecideCompliance') = '1'867  BEGIN868    DELETE @PWOHelperPWO_New869    FROM @PWOToCheck pwo870    JOIN QER_VPersonsAreMe me871      ON pwo.UID_PersonOrdered = me.UID_PersonOrigin872    JOIN @PWOHelperPWO_New n873      ON me.UID_PersonAlsoMe IN(n.UID_PersonHead,874    n.UID_PersonSubstituteSender) AND n.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg875    JOIN QERWorkingStep s876      ON n.UID_QERWorkingStep = s.UID_QERWorkingStep AND s.IgnoreNoDecideForPerson = 0877    WHERE878      n.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',879    'CPL-PWODecisionRule-OH')880  END881  IF dbo.QBM_FGIConfigparmValue('QER\ComplianceCheck\DisableSelfExceptionGranting') = '1'882  BEGIN883    DELETE @PWOHelperPWO_New884    FROM @PWOHelperPWO_New n885    JOIN QER_VPersonsAreMe nc886      ON n.UID_PersonNonCompliant = nc.UID_PersonOrigin887    JOIN QER_VPersonsAreMe nh888      ON n.uid_personHead = nh.UID_PersonOrigin889    WHERE890      nc.UID_PersonAlsoMe = nh.UID_PersonAlsoMe AND UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',891    'CPL-PWODecisionRule-OH')892  END893END894UPDATE @PWOHelperPWO_Old895SET isToDelete = 1896WHERE897  uid_personhead IS NULL AND isnull(UID_PWODecisionRule,898'') NOT IN(899SELECT UID_PWODecisionRule900FROM pwodecisionrule r901WHERE902  r.UID_Task > ' ' OR r.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC', 'CPL-PWODecisionRule-OH'))903IF @SonderBehandlungException = 1904BEGIN905  UPDATE @PWOHelperPWO_Old906  SET isToDelete = 1907  FROM @PWOHelperPWO_Old o908  WHERE909    o.uid_personHead IS NULL AND o.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',910  'CPL-PWODecisionRule-OH') AND EXISTS(911  SELECT TOP 1 1912  FROM @PWOHelperPWO_New n913  WHERE914    n.UID_PWODecisionRule = o.UID_PWODecisionRule AND isnull(n.UID_QERWorkingStep, '') = isnull(o.UID_QERWorkingStep,915  '') AND n.levelnumber = o.levelnumber AND n.uid_personhead > ' ' AND o.UID_PersonWantsOrg = n.UID_PersonWantsOrg)916  UPDATE @PWOHelperPWO_Old917  SET isToDelete = 1918  FROM @PWOHelperPWO_Old o919  WHERE920    o.uid_personHead IS NULL AND o.UID_ComplianceRule IS NULL AND o.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',921  'CPL-PWODecisionRule-OH') AND EXISTS(922  SELECT TOP 1 1923  FROM @PWOHelperPWO_New n924  WHERE925    n.UID_PWODecisionRule = o.UID_PWODecisionRule AND isnull(n.UID_QERWorkingStep, '') = isnull(o.UID_QERWorkingStep,926  '') AND n.levelnumber = o.levelnumber AND n.uid_compliancerule > ' ' AND o.UID_PersonWantsOrg = n.UID_PersonWantsOrg)927END928DELETE @PWOHelperPWO_New929WHERE930  uid_personhead IS NULL AND UID_PWODecisionRule NOT IN(931SELECT UID_PWODecisionRule932FROM pwodecisionrule r933WHERE934  UID_Task > ' ' OR r.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC', 'CPL-PWODecisionRule-OH'))935IF @SonderBehandlungException = 1936BEGIN937  DELETE @PWOHelperPWO_New938  FROM @PWOHelperPWO_New n939  WHERE940    uid_personhead IS NULL AND UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',941  'CPL-PWODecisionRule-OH') AND EXISTS(942  SELECT TOP 1 1943  FROM @PWOHelperPWO_New o944  WHERE945    o.UID_PWODecisionRule = n.UID_PWODecisionRule AND isnull(o.UID_QERWorkingStep, '') = isnull(n.UID_QERWorkingStep,946  '') AND o.levelnumber = n.levelnumber AND o.uid_personhead > ' ' AND o.UID_PersonWantsOrg = n.UID_PersonWantsOrg)947END948UPDATE @PWOHelperPWO_new949SET UID_PersonAdditional = o.UID_PersonAdditional,950UID_PersonInsteadOf = o.UID_PersonInsteadOf951FROM @PWOHelperPWO_new n952JOIN @PWOHelperPWO_Old o953  ON n.UID_PWODecisionRule = o.UID_PWODecisionRule AND n.uid_personHead = o.uid_personHead AND n.UID_QERWorkingStep = o.UID_QERWorkingStep AND n.levelnumber954= o.levelnumber AND n.Sublevelnumber = o.Sublevelnumber AND o.UID_PersonWantsOrg = n.UID_PersonWantsOrg955WHERE956  o.UID_PersonAdditional > ' ' OR o.UID_PersonInsteadOf > ' '957INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,958uid_personHead,959UID_QERWorkingStep,960levelnumber,961Sublevelnumber,962istoInsert,963UID_PersonAdditional,964UID_PersonInsteadOf,965IsFromDelegation,966RulerLevel,967UID_PersonWantsOrg)968SELECT969  DISTINCT de.UID_PWODecisionRule,970  de.UID_PersonAdditional,971  de.UID_QERWorkingStep,972  de.levelnumber,973  1,974  0,975  NULL,976  NULL,977  1,978  0,979  de.UID_PersonWantsOrg980FROM @PWOHelperPWO_New de981WHERE982  de.UID_PersonAdditional > ' ' AND NOT EXISTS(983SELECT TOP 1 1984FROM @PWOHelperPWO_New n985WHERE986  n.UID_PWODecisionRule = de.UID_PWODecisionRule AND n.uid_personHead = de.UID_PersonAdditional AND n.UID_QERWorkingStep = de.UID_QERWorkingStep AND987n.levelnumber = de.levelnumber AND n.Sublevelnumber = 1 AND de.UID_PersonWantsOrg = n.UID_PersonWantsOrg) AND EXISTS(988SELECT TOP 1 1989FROM QERWorkingStep ds990WHERE991  ds.UID_QERWorkingStep = de.UID_QERWorkingStep AND ds.IsAdditionalAllowed = 1)992IF @@rowcount > 0993BEGIN994  INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,995  uid_personHead,996  UID_QERWorkingStep,997  levelnumber,998  Sublevelnumber,999  istoInsert,1000  UID_PersonAdditional,1001  UID_PersonInsteadOf,1002  IsFromDelegation,1003  RulerLevel,1004  UID_PWORulerOrigin,1005  UID_PersonWantsOrg)1006  SELECT1007    DISTINCT de.UID_PWODecisionRule,1008    esc.UID_Person,1009    de.UID_QERWorkingStep,1010    de.levelnumber,1011    1,1012    0,1013    NULL,1014    NULL,1015    1,1016    2,1017    esc.UID_PWORulerOrigin,1018    de.UID_PersonWantsOrg1019  FROM @PWOHelperPWO_New de CROSS1020  JOIN(1021  SELECT1022    pio.UID_Person,1023    dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) AS UID_PWORulerOrigin1024  FROM PersonInBaseTree pio1025  WHERE1026    pio.UID_Org = 'QER-AEROLE-ITSHOP-INTERVENTION' AND pio.XOrigin > 0) AS esc1027  WHERE1028    de.UID_PersonAdditional > ' ' AND NOT EXISTS(1029  SELECT TOP 1 11030  FROM @PWOHelperPWO_New n1031  WHERE1032    n.UID_PWODecisionRule = de.UID_PWODecisionRule AND n.uid_personHead = esc.UID_Person AND n.UID_QERWorkingStep = de.UID_QERWorkingStep AND1033  n.levelnumber = de.levelnumber AND n.Sublevelnumber = 1 AND de.UID_PersonWantsOrg = n.UID_PersonWantsOrg)1034END1035INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,1036uid_personHead,1037UID_QERWorkingStep,1038levelnumber,1039Sublevelnumber,1040istoInsert,1041UID_PersonAdditional,1042UID_PersonInsteadOf,1043IsFromDelegation,1044RulerLevel,1045UID_PersonWantsOrg)1046SELECT1047  DISTINCT de.UID_PWODecisionRule,1048  de.UID_PersonInsteadOf,1049  de.UID_QERWorkingStep,1050  de.levelnumber,1051  0,1052  0,1053  NULL,1054  NULL,1055  1,1056  0,1057  de.UID_PersonWantsOrg1058FROM @PWOHelperPWO_New de1059WHERE1060  de.UID_PersonInsteadOf > ' ' AND NOT EXISTS(1061SELECT TOP 1 11062FROM @PWOHelperPWO_New n1063WHERE1064  n.UID_PWODecisionRule = de.UID_PWODecisionRule AND n.uid_personHead = de.UID_PersonInsteadOf AND n.UID_QERWorkingStep = de.UID_QERWorkingStep AND1065n.levelnumber = de.levelnumber AND n.Sublevelnumber = 0 AND de.UID_PersonWantsOrg = n.UID_PersonWantsOrg) AND EXISTS(1066SELECT TOP 1 11067FROM QERWorkingStep ds1068WHERE1069  ds.UID_QERWorkingStep = de.UID_QERWorkingStep AND ds.IsInsteadOfAllowed = 1)1070UPDATE @PWOHelperPWO_Old1071SET isToDelete = 11072FROM @PWOHelperPWO_Old o1073JOIN @PWOToCheck c1074  ON o.UID_PersonWantsOrg = c.UID_PersonWantsOrg1075WHERE1076  (NOT EXISTS(1077SELECT TOP 1 11078FROM @PWOHelperPWO_New n1079WHERE1080  isnull(n.UID_PWODecisionRule, '') = isnull(o.UID_PWODecisionRule, '') AND isnull(n.uid_personHead,1081'') = isnull(o.uid_personHead, '') AND n.levelnumber = o.levelnumber AND n.Sublevelnumber = o.Sublevelnumber AND isnull(n.UID_QERWorkingStep,1082'') = isnull(o.UID_QERWorkingStep, '') AND isnull(n.UID_ComplianceRule, '') = isnull(o.UID_ComplianceRule,1083'') AND isnull(n.UID_PersonNonCompliant, '') = isnull(o.UID_PersonNonCompliant, '') AND isnull(n.UID_PWORulerOrigin,1084'') = isnull(o.UID_PWORulerOrigin, '') AND ISNULL(n.rulerlevel, 0) = ISNULL(o.rulerlevel, 0) AND o.UID_PersonWantsOrg = n.UID_PersonWantsOrg) OR1085(isnull(o.uid_personHead, '') > ' ' AND NOT EXISTS(1086SELECT TOP 1 11087FROM Person p1088WHERE1089  p.UID_Person = isnull(o.uid_personHead, '') AND p.IsInActive = 0))) AND((c.SonderlockeException = 0 AND o.decision IN('',1090'-')) OR(c.SonderlockeException = 1 AND o.decision IN('', '-', 'R', '+')))1091IF EXISTS(1092  SELECT TOP 1 11093  FROM @PWOToCheck c1094  WHERE1095    c.isReserved =1)1096BEGIN1097  IF EXISTS(1098    SELECT TOP 1 11099    FROM @PWOToCheck pwo1100    JOIN @PWOHelperPWO_Old o1101      ON pwo.UID_PersonWantsOrg = o.UID_PersonWantsOrg1102    WHERE1103      o.uid_personHead = pwo.UID_PersonHead AND o.isToDelete = 1 AND pwo.isReserved = 1)1104  BEGIN1105    UPDATE PersonWantsOrg1106    SET IsReserved = 0,1107    XDateUpdated = @Xdate,1108    XUserUpdated = @XUser1109    FROM PersonWantsOrg pwo1110    JOIN @PWOHelperPWO_Old o1111      ON pwo.UID_PersonWantsOrg = o.UID_PersonWantsOrg1112    WHERE1113      pwo.IsReserved = 1 AND o.IsToDelete = 1 AND o.UID_PersonHead = pwo.UID_PersonHead1114    UPDATE @PWOHelperPWO_Old1115    SET isToDelete = 11116    FROM @PWOHelperPWO_Old o1117    JOIN @PWOToCheck c1118      ON o.UID_PersonWantsOrg = c.UID_PersonWantsOrg1119    WHERE1120      (o.UID_PWODecisionRule = 'QER-PWODecisionRule-QP' OR o.Decision = 'Q') AND c.isReserved = 1 AND EXISTS(1121    SELECT TOP 1 11122    FROM @PWOHelperPWO_Old oo1123    WHERE1124      oo.UID_PersonWantsOrg = o.UID_PersonWantsOrg AND oo.IsToDelete = 1 AND oo.UID_PersonHead = c.UID_PersonHead)1125    INSERT INTO PWODecisionHistory(DecisionType,1126    IsDecisionBySystem,1127    UID_PWODecisionHistory,1128    UID_PWOState,1129    UID_PersonHead,1130    UID_PersonWantsOrg,1131    DisplayPersonHead,1132    ReasonHead,1133    DateHead,1134    XDateInserted,1135    XDateUpdated,1136    XUserInserted,1137    XUserUpdated,1138    DecisionLevel,1139    XTouched,1140    ValidUntil,1141    ValidFrom,1142    XObjectKey,1143    UID_ITShopOrgFinal)1144    SELECT1145      DISTINCT 'RecallQuery',1146      1,1147      o.UID_PWOHelperPWO AS UID_PWODecisionHistory,1148      NULL,1149      NULL,1150      o.UID_PersonWantsOrg,1151      dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval.|'),1152      dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Query to Person finished due to changed deciders.|'),1153      GetUTCDate(),1154      GetUTCDate(),1155      GetUTCDate(),1156      'sa',1157      'sa',1158      0,1159      NULL,1160      NULL,1161      NULL,1162      dbo.QBM_FCVElementToObjectKey1('PWODecisionHistory',1163      'UID_PWODecisionHistory',1164      o.UID_PWOHelperPWO),1165      pwo.UID_ITShopOrgFinal1166    FROM @PWOToCheck pwo1167    JOIN @PWOHelperPWO_Old o1168      ON pwo.UID_PersonWantsOrg = o.UID_PersonWantsOrg1169    WHERE1170      pwo.IsReserved = 1 AND o.IsToDelete = 1 AND o.UID_PersonHead = pwo.UID_PersonHead1171  END1172END1173UPDATE @PWOHelperPWO_Old1174SET isToDelete = 11175FROM @PWOHelperPWO_Old o1176WHERE1177  IsToDelete = 0 AND NOT EXISTS(1178SELECT TOP 1 11179FROM @PWOToCheck pwo1180WHERE1181  pwo.uid_personwantsorg = o.UID_PersonWantsOrg AND OrderState IN('OrderProduct', 'OrderProlongate',1182'OrderUnsubscribe'))1183UPDATE @PWOHelperPWO_New1184SET isToInsert = 11185FROM @PWOHelperPWO_New n1186WHERE1187  NOT EXISTS(1188SELECT TOP 1 11189FROM @PWOHelperPWO_Old o1190WHERE1191  isnull(o.UID_PWODecisionRule, '') = isnull(n.UID_PWODecisionRule, '') AND isnull(o.uid_personHead,1192'') = isnull(n.uid_personHead, '') AND o.levelnumber = n.levelnumber AND o.Sublevelnumber = n.Sublevelnumber AND isnull(o.UID_QERWorkingStep,1193'') = isnull(n.UID_QERWorkingStep, '') AND isnull(o.UID_ComplianceRule, '') = isnull(n.UID_ComplianceRule,1194'') AND isnull(o.UID_PersonNonCompliant, '') = isnull(n.UID_PersonNonCompliant, '') AND isnull(o.UID_PWORulerOrigin,1195'') = isnull(n.UID_PWORulerOrigin, '') AND ISNULL(o.rulerlevel, 0) = ISNULL(n.rulerlevel, 0) AND(o.isToDelete = 0 OR o.UID_PWODecisionRule IN1196('CPL-PWODecisionRule-OC', 'CPL-PWODecisionRule-OH') AND o.UID_ComplianceRule > ' ') AND n.UID_PersonWantsOrg = o.UID_PersonWantsOrg)1197IF @SonderBehandlungException = 11198BEGIN1199  UPDATE @PWOHelperPWO_New1200  SET istoInsert = 11201  FROM @PWOHelperPWO_New o1202  WHERE1203    uid_compliancerule IS NULL AND UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',1204  'CPL-PWODecisionRule-OH') AND NOT EXISTS(1205  SELECT TOP 1 11206  FROM @PWOHelperPWO_New n1207  WHERE1208    n.UID_PWODecisionRule = o.UID_PWODecisionRule AND isnull(n.UID_QERWorkingStep, '') = isnull(o.UID_QERWorkingStep,1209  '') AND n.levelnumber = o.levelnumber AND n.UID_PersonWantsOrg = o.UID_PersonWantsOrg)1210  UPDATE @PWOHelperPWO_New1211  SET istoInsert = 01212  FROM @PWOHelperPWO_New o1213  WHERE1214    uid_compliancerule IS NULL AND UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',1215  'CPL-PWODecisionRule-OH') AND EXISTS(1216  SELECT TOP 1 11217  FROM @PWOHelperPWO_New n1218  WHERE1219    n.UID_PWODecisionRule = o.UID_PWODecisionRule AND isnull(n.UID_QERWorkingStep, '') = isnull(o.UID_QERWorkingStep,1220  '') AND n.levelnumber = o.levelnumber AND n.uid_compliancerule > ' ' AND n.UID_PersonWantsOrg = o.UID_PersonWantsOrg)1221END1222INSERT INTO @PWOHelperPWO_New(UID_PWODecisionRule,1223uid_personHead,1224UID_QERWorkingStep,1225levelnumber,1226Sublevelnumber,1227istoInsert,1228UID_PersonWantsOrg)1229SELECT1230  'QER-PWODecisionRule-SB',1231  NULL,1232  NULL,1233  0,1234  0,1235  1,1236  c.UID_PersonWantsOrg1237FROM @PWOToCheck c1238WHERE1239  c.UID_QERWorkingMethod IS NULL AND c.Orderstate IN('OrderProlongate',1240'OrderUnsubscribe') AND NOT EXISTS(1241SELECT TOP 1 11242FROM @PWOHelperPWO_New n1243WHERE1244  n.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND n.IstoInsert = 1) AND NOT EXISTS(1245SELECT TOP 1 11246FROM @PWOHelperPWO_Old o1247WHERE1248  o.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND o.IsToDelete = 0) publizieren:1249UPDATE @PWOToCheck1250SET IsModefiedByOthers = 11251FROM @PWOToCheck p1252JOIN(1253SELECT1254  c.UID_PersonWantsOrg,1255  checksum_agg(checksum(CONCAT(h.UID_PWOHelperPWO, h.Decision))) AS CheckSumHelper1256FROM @PWOToCheck c1257LEFT1258OUTER1259JOIN PWOHelperPWO h1260  WITH(nolock)1261  ON c.UID_PersonWantsOrg = h.UID_PersonWantsOrg1262GROUP BY c.UID_PersonWantsOrg) AS x1263  ON p.UID_PersonWantsOrg = x.UID_PersonWantsOrg1264WHERE1265  p.CheckSumHelper <> x.CheckSumHelper1266IF @@ROWCOUNT > 01267BEGIN1268  DECLARE @DBQueueElements_ModifiedHelper QBM_YDBQueueRaw1269  INSERT INTO @DBQueueElements_ModifiedHelper(Object,1270  SubObject,1271  GenProcID)1272  SELECT1273    p.UID_PersonWantsOrg,1274    NULL,1275    p.GenProcID1276  FROM @PWOToCheck p1277  EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO',1278    @DBQueueElements_ModifiedHelper1279  DELETE @PWOHelperPWO_Old1280  FROM @PWOHelperPWO_Old o1281  JOIN @PWOToCheck c1282    ON o.UID_PersonWantsOrg = c.UID_PersonWantsOrg1283  WHERE1284    c.IsModefiedByOthers = 11285  DELETE @PWOHelperPWO_New1286  FROM @PWOHelperPWO_New n1287  JOIN @PWOToCheck c1288    ON n.UID_PersonWantsOrg = c.UID_PersonWantsOrg1289  WHERE1290    c.IsModefiedByOthers = 11291  DELETE @PWOToCheck1292  FROM @PWOToCheck c1293  WHERE1294    c.IsModefiedByOthers = 11295END1296DELETE @DBQueueElements_CheckDecision1297INSERT INTO @DBQueueElements_CheckDecision(Object,1298SubObject,1299GenProcID)1300SELECT1301  DISTINCT c.UID_PersonWantsOrg,1302  '',1303  c.GenProcid1304FROM @PWOToCheck c1305LEFT1306OUTER1307JOIN @PWOHelperPWO_New n1308  ON c.UID_PersonWantsOrg = n.UID_PersonWantsOrg1309LEFT1310OUTER1311JOIN @PWOHelperPWO_Old o1312  ON c.UID_PersonWantsOrg = o.UID_PersonWantsOrg1313WHERE1314  (c.makeEmpty = 0 AND(isnull(o.IsToDelete, 0) = 1 OR isnull(n.IstoInsert, 0) = 1)) OR n.UID_PersonWantsOrg IS NULL1315EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckDecision',1316  @DBQueueElements_CheckDecision1317IF @DebugSwitch > 01318BEGIN1319  print 'Vor publizieren @@PWOHelperPWO_Old'1320  SELECT *1321  FROM @PWOHelperPWO_Old1322END1323DECLARE @GenProcIDs QBM_YCursorBuffer1324INSERT INTO @GenProcIDs(UID1)1325SELECT1326  DISTINCT c.GenProcID1327FROM @PWOHelperPWO_Old o1328JOIN @PWOToCheck c1329  ON o.UID_PersonWantsOrg = c.UID_PersonWantsOrg1330WHERE1331  o.IsToDelete = 11332SELECT @ElementCount = @@ROWCOUNT1333SELECT @ElementIndex = @@IDENTITY - @ElementCount +11334SELECT @ElementLast = @@IDENTITY1335WHILE @ElementIndex <= @ElementLast1336BEGIN1337  SELECT TOP 1 @GenProcIDToUse = bu.UID11338  FROM @GenProcIDs bu1339  WHERE1340    bu.ElementIndex = @ElementIndex1341  EXEC QBM_PSessionContextSet 'GenProcID',1342    @GenProcIDToUse1343  DELETE PWOHelperPWO1344  FROM PWOHelperPWO h1345  JOIN @PWOToCheck c1346    ON h.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.GenProcid = @GenProcIDToUse1347  JOIN @PWOHelperPWO_Old o1348    ON h.UID_PWOHelperPWO = o.UID_PWOHelperPWO1349  WHERE1350    o.IsToDelete = 11351  SELECT @ElementIndex += 11352END1353IF EXISTS(1354  SELECT TOP 1 11355  FROM @PWOHelperPWO_Old o1356  WHERE1357    o.isToUpdateDecision = 1)1358BEGIN1359  UPDATE PWOHelperPWO1360  SET Decision = o.Decision1361  FROM PWOHelperPWO h1362  JOIN @PWOHelperPWO_Old o1363    ON h.UID_PWOHelperPWO = o.UID_PWOHelperPWO1364  WHERE1365    o.isToUpdateDecision = 11366END1367UPDATE @PWOHelperPWO_New1368SET UID_PrimaryKey = newid()1369WHERE1370  istoinsert = 11371IF @DebugSwitch > 01372BEGIN1373  print 'Vor publizieren @PWOHelperPWO_New'1374  SELECT *1375  FROM @PWOHelperPWO_New1376END1377DECLARE @GenProcIDs_2 QBM_YCursorBuffer1378INSERT INTO @GenProcIDs_2(UID1)1379SELECT1380  DISTINCT c.GenProcID1381FROM @PWOHelperPWO_New n1382JOIN @PWOToCheck c1383  ON n.UID_PersonWantsOrg = c.UID_PersonWantsOrg1384WHERE1385  n.IstoInsert = 11386SELECT @ElementCount = @@ROWCOUNT1387SELECT @ElementIndex = @@IDENTITY - @ElementCount +11388SELECT @ElementLast = @@IDENTITY1389IF @ElementCount > 01390BEGIN1391  UPDATE @PWOHelperPWO_New1392  SET NextAutomaticDecision = isnull(g.MaxDate,1393  '2200-01-01')1394  FROM @PWOHelperPWO_New b1395  LEFT1396  OUTER1397  JOIN(1398  SELECT1399    a.UID_PersonwantsOrg,1400    a.LevelNumber,1401    a.Sublevelnumber,1402    a.RulerLevel,1403    MAX(a.NextAutomaticDecision) AS MaxDate1404  FROM @PWOHelperPWO_Old a1405  GROUP BY a.UID_PersonwantsOrg,1406  a.LevelNumber,1407  a.Sublevelnumber,1408  a.RulerLevel) AS g1409    ON b.UID_PersonwantsOrg = g.UID_PersonwantsOrg AND b.LevelNumber = g.LevelNumber AND b.Sublevelnumber = g.Sublevelnumber AND b.RulerLevel = g.RulerLevel1410  WHERE1411    b.IstoInsert = 11412END1413WHILE @ElementIndex <= @ElementLast1414BEGIN1415  SELECT TOP 1 @GenProcIDToUse = bu.UID11416  FROM @GenProcIDs_2 bu1417  WHERE1418    bu.ElementIndex = @ElementIndex1419  EXEC QBM_PSessionContextSet 'GenProcID',1420    @GenProcIDToUse1421  INSERT INTO PWOHelperPWO(UID_PWOHelperPWO,1422  uid_Personwantsorg,1423  UID_PWODecisionRule,1424  uid_personHead,1425  levelnumber,1426  SubLevelNumber,1427  Decision,1428  UID_QERWorkingStep,1429  UID_ComplianceRule,1430  UID_PersonNonCompliant,1431  UID_PWORulerOrigin,1432  NextReminder,1433  NextAutomaticDecision,1434  XObjectKey,1435  UID_PersonAdditional,1436  UID_PersonInsteadOf,1437  IsFromDelegation,1438  RulerLevel)1439  SELECT1440    n.UID_PrimaryKey,1441    n.UID_PersonWantsOrg,1442    n.UID_PWODecisionRule,1443    n.UID_PersonHead,1444    n.LevelNumber,1445    n.Sublevelnumber,1446  CASE1447    WHEN n.UID_PersonInsteadOf > ' ' THEN1448  'D'1449  ELSE ''1450  END AS Decision,1451  n.UID_QERWorkingStep,1452  n.UID_ComplianceRule,1453  n.UID_PersonNonCompliant,1454  n.UID_PWORulerOrigin,1455  '2200-01-01',1456  n.NextAutomaticDecision,1457  dbo.QBM_FCVElementToObjectKey1('PWOHelperPWO',1458  'UID_PWOHelperPWO',1459  n.UID_PrimaryKey),1460  UID_PersonAdditional,1461  UID_PersonInsteadOf,1462  IsFromDelegation,1463  n.RulerLevel1464  FROM @PWOHelperPWO_New n1465  JOIN @PWOToCheck c1466    ON n.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.GenProcid = @GenProcIDToUse1467  WHERE1468    n.IstoInsert = 1 AND NOT EXISTS(1469  SELECT TOP 1 11470  FROM PWOHelperPWO p1471  WHERE1472    p.UID_PersonWantsOrg = n.UID_PersonWantsOrg AND isnull(p.UID_PersonHead, '') = isnull(n.UID_PersonHead,1473  '') AND p.UID_PWODecisionRule = n.UID_PWODecisionRule AND p.LevelNumber = n.LevelNumber AND p.Sublevelnumber = n.Sublevelnumber AND p.RulerLevel1474  = n.RulerLevel AND p.UID_QERWorkingStep = n.UID_QERWorkingStep)1475  SELECT @ElementIndex += 11476END1477IF @SonderBehandlungException = 11478BEGIN1479  DECLARE @GenProcIDs_3 QBM_YCursorBuffer1480  INSERT INTO @GenProcIDs_3(UID1)1481  SELECT1482    DISTINCT c.GenProcID1483  FROM @PWOToCheck c1484  WHERE1485    c.SonderlockeException = 11486  SELECT @ElementCount = @@ROWCOUNT1487  SELECT @ElementIndex = @@IDENTITY - @ElementCount +11488  SELECT @ElementLast = @@IDENTITY1489  WHILE @ElementIndex <= @ElementLast1490  BEGIN1491    SELECT TOP 1 @GenProcIDToUse = bu.UID11492    FROM @GenProcIDs_3 bu1493    WHERE1494      bu.ElementIndex = @ElementIndex1495    EXEC QBM_PSessionContextSet 'GenProcID',1496      @GenProcIDToUse1497    UPDATE PWOHelperPWO1498    SET decision = '+'1499    FROM PWOHelperPWO h1500    JOIN @PWOToCheck c1501      ON h.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.GenProcid = @GenProcIDToUse AND c.OrderState IN('OrderProduct',1502    'OrderProlongate',1503    'OrderUnsubscribe') AND h.UID_PWODecisionRule IN('CPL-PWODecisionRule-OH') AND h.LevelNumber = c.decisionlevel1504    JOIN @Hilfstab ht1505      ON ht.SubLevelNumber = h.SubLevelNumber AND ht.isHighestSeverity = 0 AND ht.UID_PersonWantsOrg = c.UID_PersonWantsOrg1506    WHERE1507      h.Decision IN('',1508    'R')1509    UPDATE PWOHelperPWO1510    SET decision = '+'1511    FROM PWOHelperPWO h1512    JOIN @PWOToCheck c1513      ON h.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.GenProcid = @GenProcIDToUse AND c.OrderState IN('OrderProduct',1514    'OrderProlongate',1515    'OrderUnsubscribe') AND h.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC',1516    'CPL-PWODecisionRule-OH') AND h.LevelNumber = c.decisionlevel1517    JOIN @Hilfstab ht1518      ON ht.SubLevelNumber = h.SubLevelNumber AND ht.UID_PersonWantsOrg = c.UID_PersonWantsOrg1519    JOIN ComplianceRule cr1520      ON ht.uid_complianceRule = cr.UID_ComplianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 01521    JOIN PersonInBaseTree pin1522      ON cr.UID_NonCompliance = pin.UID_Org AND pin.XOrigin > 0 AND pin.UID_Person = ht.UID_PersonNonCompliant AND pin.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay1523    = 0 AND(pin.isExceptionGranted = 1 AND isnull(cr.isToGrantEver, 0) = 0)1524    WHERE1525      h.Decision IN('',1526    'R')1527    INSERT INTO @DBQueueElements_MakeDecisionOC(object,1528    subobject,1529    genprocid)1530    SELECT1531      x.uid,1532      NULL,1533      @GenProcIDToUse1534    FROM(1535    SELECT1536      DISTINCT h.UID_PersonWantsOrg AS uid1537    FROM PWOHelperPWO h1538    JOIN @PWOToCheck c1539      ON h.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.GenProcid = @GenProcIDToUse1540    WHERE1541      h.UID_PWODecisionRule IN('CPL-PWODecisionRule-OC') AND h.Decision IN('', 'R', '+') AND c.OrderState IN('OrderProduct',1542    'OrderProlongate', 'OrderUnsubscribe') AND c.decisionlevel = h.levelnumber) AS x1543    INSERT INTO @DBQueueElements_MakeDecisionOH(object,1544    subobject,1545    genprocid)1546    SELECT1547      x.uid,1548      NULL,1549      @GenProcIDToUse1550    FROM(1551    SELECT1552      DISTINCT h.UID_PersonWantsOrg AS uid1553    FROM PWOHelperPWO h1554    JOIN @PWOToCheck c1555      ON h.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.GenProcid = @GenProcIDToUse1556    WHERE1557      h.UID_PWODecisionRule IN('CPL-PWODecisionRule-OH') AND h.Decision IN('', 'R', '+') AND c.OrderState IN('OrderProduct',1558    'OrderProlongate', 'OrderUnsubscribe') AND c.decisionlevel = h.levelnumber) AS x1559    SELECT @ElementIndex += 11560  END1561  IF EXISTS(1562    SELECT TOP 1 11563    FROM @DBQueueElements_MakeDecisionOC)1564  BEGIN1565    EXEC QBM_PDBQueueInsert_Bulk 'CPL-K-ShoppingRackMakeDecisionOC',1566      @DBQueueElements_MakeDecisionOC1567  END1568  IF EXISTS(1569    SELECT TOP 1 11570    FROM @DBQueueElements_MakeDecisionOH)1571  BEGIN1572    EXEC QBM_PDBQueueInsert_Bulk 'CPL-K-ShoppingRackMakeDecisionOH',1573      @DBQueueElements_MakeDecisionOH1574  END1575END1576DECLARE @ElementsToFire QBM_YCursorBuffer1577INSERT INTO @ElementsToFire(UID1,1578Int1,1579UID2)1580SELECT1581  DISTINCT c.GenProcID,1582  c.decisionlevel,1583  c.UID_PersonWantsOrg1584FROM @PWOToCheck c1585LEFT1586OUTER1587JOIN @PWOHelperPWO_New n1588  ON c.UID_PersonWantsOrg = n.UID_PersonWantsOrg1589LEFT1590OUTER1591JOIN @PWOHelperPWO_Old o1592  ON c.UID_PersonWantsOrg = o.UID_PersonWantsOrg1593WHERE1594  c.makeEmpty = 0 AND(n.IstoInsert = 1 OR o.IsToDelete = 1 OR c.IsNewDecisionLevel = 1)1595SELECT @ElementCount = @@ROWCOUNT1596SELECT @ElementIndex = @@IDENTITY - @ElementCount +11597SELECT @ElementLast = @@IDENTITY1598WHILE @ElementIndex <= @ElementLast1599BEGIN1600  SELECT1601    TOP 1 @GenProcIDToUse = bu.UID1,1602    @DecisionLevelToUse = bu.Int1,1603    @UID_PWO = bu.UID21604  FROM @ElementsToFire bu1605  WHERE1606    bu.ElementIndex = @ElementIndex1607  EXEC QER_P04F128DC257D2B3C236MAIL_ @GenProcIDToUse,1608    @PWOHelperPWO_New,1609    @PWOToCheck,1610    @DecisionLevelToUse,1611    @UID_PWO1612  SELECT @ElementIndex += 11613END1614IF @SonderBehandlungException = 11615BEGIN1616  UPDATE PWOHelperPWO1617  SET decision = 'R'1618  FROM PWOHelperPWO h1619  JOIN @PWOToCheck c1620    ON h.UID_PersonWantsOrg = c.UID_PersonWantsOrg1621  JOIN(1622  SELECT1623    UID_PWODecisionRule,1624    LevelNumber1625  FROM @PWOHelperPWO_New1626  WHERE1627    isToInsert = 1 AND UID_PWODecisionRule IN('CPL-PWODecisionRule-OC', 'CPL-PWODecisionRule-OH')1628  UNION1629  SELECT1630    UID_PWODecisionRule,1631    Levelnumber1632  FROM @PWOHelperPWO_Old1633  WHERE1634    isTodelete = 1 AND UID_PWODecisionRule IN('CPL-PWODecisionRule-OC', 'CPL-PWODecisionRule-OH')) AS x1635    ON h.UID_PWODecisionRule = x.UID_PWODecisionRule AND h.LevelNumber = x.LevelNumber1636  WHERE1637    h.Decision = ''1638END1639INSERT INTO @DBQueueElements_MakeDecisionEX(Object,1640SubObject,1641GenProcID)1642SELECT1643  DISTINCT c.UID_PersonWantsOrg,1644  '',1645  c.GenProcid1646FROM @PWOToCheck c1647JOIN PWOHelperPWO h1648  WITH(readpast)1649  ON c.UID_PersonWantsOrg = h.UID_PersonWantsOrg AND c.DecisionLevel = h.LevelNumber AND c.IsNewDecisionLevel = 11650JOIN PWODecisionRule r1651  ON h.UID_PWODecisionRule = r.UID_PWODecisionRule AND r.UID_Task = 'QER-K-ShoppingRackMakeDecisionEX'1652WHERE1653  (c.OrderState = 'OrderProduct' AND(c.DecisionLevel > 0 OR EXISTS(1654SELECT TOP 1 11655FROM @PWOHelperPWO_New n1656WHERE1657  n.UID_PrimaryKey = h.UID_PWOHelperPWO AND n.istoInsert = 1 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\DecisionOnInsert') > ' '))) OR(c.OrderState1658IN('OrderProlongate', 'OrderUnsubscribe'))1659EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackMakeDecisionEX',1660  @DBQueueElements_MakeDecisionEX1661END TRY1662BEGIN CATCH1663  EXEC QBM_PSessionErrorAdd DEFAULT1664  RAISERROR('',1665  18,1666  1)1667    WITH NOWAIT1668END CATCH1669ende:1670SET ANSI_WARNINGS1671  ON1672EXEC QBM_PSessionContextSet 'GenProcID',1673  @GenProcID_R1674EXEC QBM_PSessionContextSet 'XUser',1675  @XUser_R revert1676RETURN1677END