dbo.QER_ZITShopCheckDecision

1CREATE PROCEDURE QER_ZITShopCheckDecision(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @SQLcmd nvarchar(1000)7  DECLARE @uid_personwantsorg varchar(38)8  DECLARE @GenProcID varchar(38)9  DECLARE @BasisObjectKey varchar(138)10  DECLARE @AbortMethod nvarchar(64)11  DECLARE @UID_PWOStateFinalError varchar(38) = NULL12  DECLARE @EscalateIfNoApprover BIT13  DECLARE @UID_PWOHelperPWO varchar(38)14  DECLARE @UID_PersonHead varchar(38)15  DECLARE @MinutesReminder int16  DECLARE @MailExists int17  DECLARE @ObjectkeyForMail varchar(138)18  DECLARE @ObjectkeySForMail QBM_YParameterList19  DECLARE @WhereclauseForMail nvarchar(1000)20  DECLARE @CurrentDecisionLevel int21  DECLARE @SubLevelNumberString varchar(16)22  DECLARE @UID_Task varchar(38)23  DECLARE @DBQueueElements_spezial QBM_YDBQueueRaw24  DECLARE @Dummystring nvarchar(100)25  DECLARE @MyName nvarchar(64) = object_name(@@procid)26  DECLARE @DecisionLevel int27  DECLARE @XUser nvarchar(64) = object_name(@@procid)28  DECLARE @Debugswitch int = 029  DECLARE @MaxDate2200 datetime = '2200-01-01'30  DECLARE @Mindate1899 datetime = '1899-12-30'31  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')32  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')33  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))34  DECLARE @ElementLast int35  DECLARE @ElementCount int36  DECLARE @ElementIndex int37  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent38  DECLARE @DaysToAbort QBM_YDBQueueCurrent39  DECLARE @Helper QER_YPWOHelperReminder40  BEGIN TRY41    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,42    UID_Parameter,43    UID_SubParameter,44    GenProcID)45    SELECT46      UID_DialogDBQueue,47      UID_Parameter,48      UID_SubParameter,49      GenProcID50    FROM QBMDBQueueCurrent cu51      WITH(readpast)52    WHERE53      cu.SlotNumber = @SlotNumber54    IF @@rowcount = 055    BEGIN56      GOTO EndLabel57    END58    DECLARE @DBQueueElements_36443 QBM_YDBQueueRaw59    DECLARE @Task36443 varchar(38) = 'QER-K-ITSHOPOrder-RESET'60    INSERT INTO @DBQueueElements_36443(Object,61    GenProcID)62    SELECT63      x.UID_PersonWantsOrg,64      x.GenProcID65    FROM(66    SELECT67      pwo.UID_PersonWantsOrg,68      pwo.OrderState,69      pwo.DecisionLevel,70      h.SubLevelNumber,71      sum(CASE h.Decision72      WHEN 'P' THEN73      174    ELSE 075    END) AS CountPositive,76    COUNT(*) AS CountDecider,77    max(ws.CountApprover) AS CountApprover,78    po.DecisionPossible,79    max(dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, cu.GenProcID, @CfgUseGenProcID)) AS GenProcID80    FROM @DBQueueCurrent cu81    JOIN PersonWantsOrg pwo82      WITH(readpast)83      ON cu.UID_Parameter = pwo.UID_PersonWantsOrg84    JOIN PWOHelperPWO h85      WITH(readpast)86      ON pwo.UID_PersonWantsOrg = h.UID_PersonWantsOrg AND pwo.DecisionLevel = h.LevelNumber87    JOIN QERWorkingStep ws88      ON ws.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod AND ws.LevelNumber = pwo.DecisionLevel AND ws.SubLevelNumber = h.SubLevelNumber89    JOIN PWODecisionRule ru90      ON ws.UID_PWODecisionRule = ru.UID_PWODecisionRule AND ru.UID_Task IS NULL CROSS apply dbo.QER_FTPWODecisionPossible(pwo.UID_PersonWantsOrg,91    pwo.DecisionLevel, h.SubLevelNumber) po92    WHERE93      pwo.OrderState IN('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe')94    GROUP BY pwo.UID_PersonWantsOrg,95    pwo.OrderState,96    pwo.DecisionLevel,97    h.SubLevelNumber,98    po.DecisionPossible) AS x99    WHERE100      x.CountApprover = -1 AND x.DecisionPossible = 0 AND x.CountPositive = x.CountDecider AND x.CountPositive > 0101    GROUP BY x.UID_PersonWantsOrg,102    x.GenProcID103    IF @@ROWCOUNT > 0104    BEGIN105      EXEC QBM_PDBQueueInsert_Bulk @Task36443,106        @DBQueueElements_36443107      DELETE @DBQueueCurrent108      FROM @DBQueueCurrent cu109      JOIN @DBQueueElements_36443 el110        ON cu.UID_Parameter = el.Object111    END112    DECLARE @PWOOhneEntscheider QBM_YCursorBuffer113    INSERT INTO @PWOOhneEntscheider(UID1,114    UID2,115    ObjectKey1,116    Ident1,117    Bit1,118    Int1)119    SELECT120      pwo.uid_personwantsorg,121      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,122      p.GenProcID,123      @CfgUseGenProcID) AS GenProcID,124      pwo.XObjectKey,125    CASE pwo.Orderstate126      WHEN 'OrderProduct' THEN127    'Abort'128    ELSE 'AbortOrder'129    END,130    ws.EscalateIfNoApprover,131    pwo.DecisionLevel132    FROM @DBQueueCurrent p133    JOIN personwantsorg pwo134      WITH(readpast)135      ON p.uid_parameter = pwo.uid_personwantsorg136    JOIN QERWorkingStep ws137      ON pwo.UID_QERWorkingMethod = ws.UID_QERWorkingMethod AND pwo.DecisionLevel = ws.LevelNumber138    WHERE139      NOT EXISTS(140    SELECT TOP 1 1141    FROM PWOHelperPWO ph142      WITH(readpast)143    WHERE144      pwo.uid_personwantsorg = ph.uid_personwantsorg AND ws.LevelNumber = ph.LevelNumber AND ws.SubLevelNumber = ph.SubLevelNumber AND(ws.UID_PWODecisionRule145    = ph.UID_PWODecisionRule OR ph.UID_PWODecisionRule = 'QER-PWODecisionRule-QP') AND ph.RulerLevel IN(0,146    1)) AND pwo.OrderState IN('OrderProduct',147    'OrderProlongate',148    'OrderUnsubscribe')149    UNION150    SELECT151      pwo.uid_personwantsorg,152      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,153      p.GenProcID,154      @CfgUseGenProcID) AS GenProcID,155      pwo.XObjectKey,156    CASE pwo.Orderstate157      WHEN 'OrderProduct' THEN158    'Abort'159    ELSE 'AbortOrder'160    END,161    0,162    pwo.DecisionLevel163    FROM @DBQueueCurrent p164    JOIN personwantsorg pwo165      WITH(readpast)166      ON p.uid_parameter = pwo.uid_personwantsorg167    WHERE168      NOT EXISTS(169    SELECT TOP 1 1170    FROM QERWorkingStep ws171    WHERE172      pwo.UID_QERWorkingMethod = ws.UID_QERWorkingMethod) AND pwo.UID_QERWorkingMethod > ' ' AND pwo.OrderState = 'OrderProduct'173    SELECT @ElementCount = @@ROWCOUNT174    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1175    SELECT @ElementLast = @@IDENTITY176    WHILE @ElementIndex <= @ElementLast177    BEGIN178      SELECT179        TOP 1 @uid_personwantsorg = bu.UID1,180        @GenProcID = bu.UID2,181        @BasisObjectKey = bu.ObjectKey1,182        @AbortMethod = bu.Ident1,183        @EscalateIfNoApprover = bu.Bit1,184        @DecisionLevel = bu.Int1185      FROM @PWOOhneEntscheider bu186      WHERE187        bu.ElementIndex = @ElementIndex188      IF @EscalateIfNoApprover = 0189      BEGIN190        SELECT @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''191        SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)192        EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',193          @SQLcmd,194          @save = 1,195          @Methodname = @AbortMethod,196          @GenProcID = @GenProcID,197          @ObjectKeysAffected = DEFAULT,198          @Param1 = '#LDS#Automatic system approval: No approver available (in this shop).|',199          @Param2 = @UID_PWOStateFinalError,200          @Retries = 3,201          @checkForExisting = 1,202          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')',203          @BasisObjectKey = @BasisObjectKey204      END205      ELSE206      BEGIN207        SELECT208          @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)209        EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',210          @SQLcmd,211          @save = 1,212          @Methodname = 'ESCALATE',213          @GenProcID = @GenProcID,214          @ObjectKeysAffected = DEFAULT,215          @Param1 = 'sa',216          @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to no approver available.|',217          @Retries = 3,218          @checkForExisting = 1,219          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')',220          @BasisObjectKey = @BasisObjectKey,221          @isToFreezeOnError = 1222      END223      DELETE @DBQueueCurrent224      FROM @DBQueueCurrent cu225      WHERE226        cu.UID_Parameter = @uid_personwantsorg227      IF @Debugswitch > 0228      BEGIN229        print 'Zurücksetzen 1'230      END231      SELECT @ElementIndex += 1232    END233    DECLARE @PWOZuWenigEntscheider QBM_YCursorBuffer234    INSERT INTO @PWOZuWenigEntscheider(UID1,235    UID2,236    ObjectKey1,237    Ident1,238    Bit1,239    Int1)240    SELECT241      DISTINCT pwo.uid_personwantsorg,242      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,243      p.GenProcID,244      @CfgUseGenProcID) AS GenProcID,245      pwo.XObjectKey,246    CASE pwo.Orderstate247      WHEN 'OrderProduct' THEN248    'Abort'249    ELSE 'AbortOrder'250    END,251    ws.EscalateIfNoApprover,252    pwo.DecisionLevel253    FROM @DBQueueCurrent p254    JOIN personwantsorg pwo255      WITH(readpast)256      ON p.uid_parameter = pwo.uid_personwantsorg257    JOIN QERWorkingStep ws258      ON pwo.UID_QERWorkingMethod = ws.UID_QERWorkingMethod AND pwo.decisionlevel = ws.levelnumber CROSS apply dbo.QER_FTPWODecisionPossible(pwo.uid_personwantsorg259    ,260    ws.LevelNumber,261    ws.SubLevelNumber) dp262    WHERE263      dp.DecisionPossible = -1 AND pwo.OrderState IN('OrderProduct',264    'OrderProlongate',265    'OrderUnsubscribe')266    SELECT @ElementCount = @@ROWCOUNT267    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1268    SELECT @ElementLast = @@IDENTITY269    WHILE @ElementIndex <= @ElementLast270    BEGIN271      SELECT272        TOP 1 @uid_personwantsorg = bu.UID1,273        @GenProcID = bu.UID2,274        @BasisObjectKey = bu.ObjectKey1,275        @AbortMethod = bu.Ident1,276        @EscalateIfNoApprover = bu.Bit1,277        @DecisionLevel = bu.Int1278      FROM @PWOZuWenigEntscheider bu279      WHERE280        bu.ElementIndex = @ElementIndex281      IF @EscalateIfNoApprover = 0282      BEGIN283        SELECT @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''284        SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)285        EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',286          @SQLcmd,287          @save = 1,288          @Methodname = @AbortMethod,289          @GenProcID = @GenProcID,290          @ObjectKeysAffected = DEFAULT,291          @Param1 = '#LDS#Automatic system approval: Not enough approvers available.|',292          @Param2 = @UID_PWOStateFinalError,293          @Retries = 3,294          @checkForExisting = 1,295          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')',296          @BasisObjectKey = @BasisObjectKey297      END298      ELSE299      BEGIN300        SELECT301          @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)302        EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',303          @SQLcmd,304          @save = 1,305          @Methodname = 'ESCALATE',306          @GenProcID = @GenProcID,307          @ObjectKeysAffected = DEFAULT,308          @Param1 = 'sa',309          @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to not enough approvers available.|',310          @Retries = 3,311          @checkForExisting = 1,312          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')',313          @BasisObjectKey = @BasisObjectKey,314          @isToFreezeOnError = 1315      END316      DELETE @DBQueueCurrent317      FROM @DBQueueCurrent cu318      WHERE319        cu.UID_Parameter = @uid_personwantsorg320      IF @Debugswitch > 0321      BEGIN322        print 'Zurücksetzen 2'323      END324      SELECT @ElementIndex += 1325    END326    INSERT INTO @DaysToAbort(UID_DialogDBQueue,327    UID_Parameter,328    UID_SubParameter,329    GenProcID)330    SELECT331      UID_DialogDBQueue,332      UID_Parameter,333      UID_SubParameter,334      GenProcID335    FROM @DBQueueCurrent336    DECLARE @PWOPositiv QBM_YCursorBuffer337    INSERT INTO @PWOPositiv(UID1)338    SELECT339      DISTINCT dr.UID_Task340    FROM @DBQueueCurrent p341    JOIN personwantsorg pwo342      WITH(readpast)343      ON p.uid_parameter = pwo.uid_personwantsorg344    JOIN PWOHelperPWO ph345      WITH(readpast)346      ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber347    JOIN pwodecisionrule dr348      ON ph.UID_PWODecisionRule = dr.UID_PWODecisionRule349    WHERE350      dr.UID_Task > ' ' AND OrderState IN('OrderProduct',351    'OrderProlongate',352    'OrderUnsubscribe')353    SELECT @ElementCount = @@ROWCOUNT354    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1355    SELECT @ElementLast = @@IDENTITY356    WHILE @ElementIndex <= @ElementLast357    BEGIN358      SELECT TOP 1 @UID_Task = bu.UID1359      FROM @PWOPositiv bu360      WHERE361        bu.ElementIndex = @ElementIndex362      DELETE @DBQueueElements_spezial363      INSERT INTO @DBQueueElements_spezial(object,364      subobject,365      genprocid)366      SELECT367        x.uid,368        NULL,369        x.GenProcID370      FROM(371      SELECT372        pwo.uid_personwantsorg AS uid,373        dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) AS GenProcID374      FROM @DBQueueCurrent p375      JOIN personwantsorg pwo376        WITH(readpast)377        ON p.uid_parameter = pwo.uid_personwantsorg378      JOIN PWOHelperPWO ph379        WITH(readpast)380        ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber381      JOIN pwodecisionrule dr382        ON ph.UID_PWODecisionRule = dr.UID_PWODecisionRule AND OrderState IN('OrderProduct', 'OrderProlongate',383      'OrderUnsubscribe') AND dr.UID_Task = @UID_Task) AS x384      DELETE @DBQueueCurrent385      FROM @DBQueueCurrent cu386      WHERE387        cu.UID_Parameter IN(388      SELECT object389      FROM @DBQueueElements_spezial)390      DELETE @DBQueueElements_spezial391      FROM @DBQueueElements_spezial e392      JOIN PersonWantsOrg pwo393        WITH(readpast)394        ON e.Object = pwo.UID_PersonWantsOrg395      WHERE396        @UID_Task = 'QER-K-ShoppingRackMakeDecisionEX' AND(pwo.DecisionLevel > 0 OR(pwo.DecisionLevel = 0 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\DecisionOnInsert'397      ) > ' '))398      EXEC QBM_PDBQueueInsert_Bulk @UID_Task,399        @DBQueueElements_spezial400      IF @Debugswitch > 0401      BEGIN402        print 'Zurücksetzen 3'403      END404      SELECT @ElementIndex += 1405    END406    DECLARE @PWOZeit QBM_YCursorBuffer407    INSERT INTO @PWOZeit(UID1,408    UID2,409    ObjectKey1,410    Ident1)411    SELECT412      DISTINCT p.uid_parameter,413      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,414      p.GenProcID,415      @CfgUseGenProcID) AS GenProcID,416      pwo.XObjectKey,417    CASE pwo.Orderstate418      WHEN 'OrderProduct' THEN419    'Abort'420    ELSE 'AbortOrder'421    END422    FROM personwantsorg pwo423      WITH(readpast)424    JOIN @DaysToAbort p425      ON pwo.uid_personwantsorg = p.uid_parameter426    JOIN PWOHelperPWO h427      WITH(readpast)428      ON pwo.uid_personwantsorg = h.uid_personwantsorg429    JOIN QERWorkingMethod wm430      ON pwo.UID_QERWorkingMethod = wm.UID_QERWorkingMethod431    LEFT432    OUTER433    JOIN(434    SELECT435      h1.UID_PersonWantsOrg,436      MAX(h1.XDateInserted) AS OrderDate437    FROM @DaysToAbort p1438    JOIN PWODecisionHistory h1439      ON p1.UID_Parameter = h1.UID_PersonWantsOrg440    WHERE441      h1.DecisionType = 'Prolongate' AND h1.OrderState = 'OrderProlongate'442    GROUP BY h1.UID_PersonWantsOrg) AS hu443      ON pwo.uid_PersonwantsOrg = hu.uid_PersonwantsOrg444    LEFT445    OUTER446    JOIN(447    SELECT448      h1.UID_PersonWantsOrg,449      MAX(h1.XDateInserted) AS OrderDate450    FROM @DaysToAbort p1451    JOIN PWODecisionHistory h1452      ON p1.UID_Parameter = h1.UID_PersonWantsOrg453    WHERE454      h1.DecisionType = 'Unsubscribe' AND h1.OrderState = 'OrderUnsubscribe'455    GROUP BY h1.UID_PersonWantsOrg) AS un456      ON pwo.uid_PersonwantsOrg = un.uid_PersonwantsOrg457    WHERE458      dateadd(dd,459    wm.DaysToAbort,460    CASE pwo.Orderstate461      WHEN 'OrderProduct' THEN462    pwo.OrderDate463      WHEN 'OrderProlongate' THEN464    isnull(hu.OrderDate, @Mindate1899)465      WHEN 'OrderUnsubscribe' THEN466    isnull(un.OrderDate, @Mindate1899)467    ELSE pwo.Orderdate468    END) < getutcdate() AND isnull(wm.DaysToAbort,469    0) > 0 AND pwo.OrderState IN('OrderProduct',470    'OrderProlongate',471    'OrderUnsubscribe') AND NOT EXISTS(472    SELECT TOP 1 1473    FROM JobQueue q474    JOIN QBMElementAffectedByJob ebj475      ON q.UID_Job = ebj.UID_Job476    WHERE477      q.ComponentAssembly = 'HandleObjectComponent' AND q.TaskName = 'CallMethod' AND ebj.ObjectKeyAffected = pwo.XObjectKey AND q.Ready2EXE LIKE478    '[^DH][^I] % ')479    SELECT @ElementCount = @@ROWCOUNT480    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1481    SELECT @ElementLast = @@IDENTITY482    WHILE @ElementIndex <= @ElementLast483    BEGIN484      SELECT485        TOP 1 @uid_personwantsorg = bu.UID1,486        @GenProcID = bu.UID2,487        @BasisObjectKey = bu.ObjectKey1,488        @AbortMethod = bu.Ident1489      FROM @PWOZeit bu490      WHERE491        bu.ElementIndex = @ElementIndex492      SELECT @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''493      SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)494      EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',495        @SQLcmd,496        @save = 1,497        @Methodname = @AbortMethod,498        @GenProcID = @GenProcID,499        @ObjectKeysAffected = DEFAULT,500        @Param1 = '#LDS#Automatic system approval: Number of days has been exceeded, as specified in decision method.|',501        @Param2 = @UID_PWOStateFinalError,502        @Retries = 3,503        @checkForExisting = 1,504        @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')',505        @BasisObjectKey = @BasisObjectKey506      DELETE @DBQueueCurrent507      FROM @DBQueueCurrent cu508      WHERE509        cu.UID_Parameter = @uid_personwantsorg510      IF @Debugswitch > 0511      BEGIN512        print 'Zurücksetzen 4'513      END514      SELECT @ElementIndex += 1515    END516    SELECT @GenProcID = NULL517    SELECT TOP 1 @GenProcID = GenProcID518    FROM @DBQueueCurrent p519    IF @GenProcID IS NULL520    BEGIN521      IF @Debugswitch > 0522      BEGIN523        print 'ausstieg 4'524      END525      GOTO EndLabel526    END527    EXEC QBM_PSessionContextSet 'GenProcID',528      @GenProcID529    EXEC QBM_PSessionContextSet 'XUser',530      @XUser531    INSERT INTO @Helper(UID_HelperKey,532    NextReminder,533    NextAutomaticDecision,534    UID_ElementKey,535    UID_PersonHead,536    UID_QERWorkingStep,537    Decision,538    LevelNumber,539    UID_PWODecisionRule,540    XObjectKey,541    RulerLevel)542    SELECT543      h.UID_PWOHelperPWO,544      h.NextReminder,545      h.NextAutomaticDecision,546      h.UID_PersonWantsOrg,547      h.UID_PersonHead,548      h.UID_QERWorkingStep,549      h.Decision,550      h.LevelNumber,551      h.UID_PWODecisionRule,552      h.XObjectKey,553      h.RulerLevel554    FROM @DBQueueCurrent cu555    JOIN PWOHelperPWO h556      WITH(readpast)557      ON cu.UID_Parameter = h.UID_PersonWantsOrg558    UPDATE @Helper559    SET NextAutomaticDecision = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(),560    ws.MinutesAutomaticDecision,561    h.UID_PersonHead),562    IsToUpdateNextAutomaticDec = 1563    FROM @Helper h564    JOIN @DBQueueCurrent p565      ON h.UID_ElementKey = p.UID_Parameter566    JOIN QERWorkingStep ws567      ON h.uid_QERWorkingStep = ws.UID_QERWorkingStep568    JOIN PersonWantsOrg pwo569      WITH(readpast)570      ON h.UID_ElementKey = pwo.UID_PersonWantsOrg571    JOIN PWODecisionRule r572      ON ws.UID_PWODecisionRule = r.UID_PWODecisionRule573    WHERE574      ws.MinutesAutomaticDecision > 0 AND h.NextAutomaticDecision = @MaxDate2200 AND isnull(h.Decision,575    '') = '' AND r.UID_Task IS NULL AND h.levelnumber = pwo.DecisionLevel576    SELECT TOP 1 @GenProcID = GenProcID577    FROM @DBQueueCurrent p578    EXEC QBM_PSessionContextSet 'GenProcID',579      @GenProcID580    EXEC QBM_PSessionContextSet 'XUser',581      @XUser582    UPDATE @Helper583    SET NextAutomaticDecision = @MaxDate2200,584    IsToUpdateNextAutomaticDec = 1585    FROM @Helper h586    JOIN @DBQueueCurrent p587      ON h.UID_ElementKey = p.UID_Parameter588    JOIN QERWorkingStep ws589      ON h.uid_QERWorkingStep = ws.UID_QERWorkingStep590    JOIN PersonWantsOrg pwo591      WITH(readpast)592      ON h.UID_ElementKey = pwo.UID_PersonWantsOrg593    JOIN PWODecisionRule r594      ON ws.UID_PWODecisionRule = r.UID_PWODecisionRule595    WHERE596      h.NextAutomaticDecision < @MaxDate2200 AND(ws.MinutesAutomaticDecision = 0 OR isnull(h.Decision,597    '') > '' OR h.levelnumber <> pwo.DecisionLevel OR r.UID_Task > ' ')598    DECLARE @PWOAutomatic QBM_YCursorBuffer599    INSERT INTO @PWOAutomatic(UID1,600    UID2,601    ObjectKey1,602    Ident1,603    Int1,604    Ident2)605    SELECT606      DISTINCT p.UID_Parameter,607      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,608      p.GenProcID,609      @CfgUseGenProcID) AS GenProcID,610      pwo.XObjectKey,611      ws.AutomaticDecision,612      pwo.DecisionLevel,613      min(dbo.QBM_FCVIntToString(hp.SubLevelNumber))614    FROM @DBQueueCurrent p615    JOIN(616    SELECT617      h.UID_ElementKey AS uid_personwantsorg,618      h.uid_QERWorkingStep619    FROM @Helper h620    JOIN @DBQueueCurrent p621      ON h.UID_ElementKey = p.uid_parameter622    JOIN PersonWantsOrg pwo623      WITH(readpast)624      ON h.UID_ElementKey = pwo.UID_PersonWantsOrg AND h.levelnumber = pwo.DecisionLevel625    JOIN QERWorkingStep ws626      ON ws.UID_QERWorkingStep = h.UID_QERWorkingStep627    WHERE628      h.NextAutomaticDecision < @MaxDate2200 AND h.RulerLevel <> 2629    GROUP BY h.UID_ElementKey,630    h.uid_QERWorkingStep631    HAVING max(NextAutomaticDecision) < getutcdate()) AS x632      ON p.uid_parameter = x.uid_personwantsorg633    JOIN PersonWantsOrg pwo634      ON p.UID_Parameter = pwo.UID_PersonWantsOrg AND pwo.OrderState IN('OrderProduct',635    'OrderProlongate',636    'OrderUnsubscribe')637    JOIN QERWorkingStep ws638      ON ws.uid_QERWorkingStep = x.uid_QERWorkingStep639    JOIN PWOHelperPWO hp640      ON hp.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg AND hp.UID_QERWorkingStep = ws.UID_QERWorkingStep641    WHERE642      dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0 AND dbo.QER_FGIPWODecisionPossible(pwo.UID_PersonWantsOrg,643    hp.LevelNumber,644    hp.SubLevelNumber) = 1645    GROUP BY p.UID_Parameter,646    dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,647    p.GenProcID,648    @CfgUseGenProcID),649    pwo.XObjectKey,650    ws.AutomaticDecision,651    pwo.DecisionLevel652    SELECT @ElementCount = @@ROWCOUNT653    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1654    SELECT @ElementLast = @@IDENTITY655    WHILE @ElementIndex <= @ElementLast656    BEGIN657      SELECT658        TOP 1 @uid_personwantsorg = bu.UID1,659        @GenProcID = bu.UID2,660        @BasisObjectKey = bu.ObjectKey1,661        @AbortMethod = bu.Ident1,662        @DecisionLevel = bu.Int1,663        @SubLevelNumberString = bu.Ident2664      FROM @PWOAutomatic bu665      WHERE666        bu.ElementIndex = @ElementIndex667      SELECT668        @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)669      SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)670      IF @AbortMethod IN('TRUE',671      'FALSE')672      BEGIN673        EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',674          @SQLcmd,675          @save = 1,676          @Methodname = 'MakeDecision',677          @ObjectKeysAffected = DEFAULT,678          @GenProcID = @GenProcID,679          @Param1 = 'sa',680          @Param2 = @AbortMethod,681          @Param3 = '#LDS#Automatic system approval: Decision due to timeout.|',682          @Param5 = @SubLevelNumberString,683          @Retries = 3,684          @checkForExisting = 1,685          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')',686          @BasisObjectKey = @BasisObjectKey,687          @isToFreezeOnError = 1688      END689      IF @AbortMethod IN('ESCALATE') AND NOT EXISTS(690        SELECT TOP 1 1691        FROM @PWOAutomatic bu692        WHERE693          bu.ElementIndex < @ElementIndex AND bu.UID1 = @uid_personwantsorg)694      BEGIN695        EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',696          @SQLcmd,697          @save = 1,698          @Methodname = 'ESCALATE',699          @GenProcID = @GenProcID,700          @ObjectKeysAffected = DEFAULT,701          @Param1 = 'sa',702          @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to timeout.|',703          @Retries = 3,704          @checkForExisting = 1,705          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')',706          @BasisObjectKey = @BasisObjectKey,707          @isToFreezeOnError = 1708      END709      IF @AbortMethod IN('Abort') AND NOT EXISTS(710        SELECT TOP 1 1711        FROM @PWOAutomatic bu712        WHERE713          bu.ElementIndex < @ElementIndex AND bu.UID1 = @uid_personwantsorg)714      BEGIN715        IF NOT EXISTS(716          SELECT TOP 1 1717          FROM personwantsorg718          WITH(readpast)719        WHERE720          uid_personwantsorg = @uid_personwantsorg AND orderstate = 'OrderProduct')721        BEGIN722          SELECT @AbortMethod = 'AbortOrder'723        END724        EXEC QBM_PJobCreate_HOCallMethod 'personwantsorg',725          @SQLcmd,726          @save = 1,727          @Methodname = @AbortMethod,728          @GenProcID = @GenProcID,729          @ObjectKeysAffected = DEFAULT,730          @Param1 = '#LDS#Automatic system approval: "Abort" due to timeout.|',731          @Param2 = @UID_PWOStateFinalError,732          @Retries = 3,733          @checkForExisting = 1,734          @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'735        ,736        @BasisObjectKey = @BasisObjectKey,737        @isToFreezeOnError = 1738      END739      DELETE @DBQueueCurrent740      FROM @DBQueueCurrent cu741      WHERE742        cu.UID_Parameter = @uid_personwantsorg743      IF @Debugswitch > 0744      BEGIN745        print 'Zurücksetzen 5'746      END747      SELECT @ElementIndex += 1748    END749    SELECT @GenProcID = NULL750    SELECT TOP 1 @GenProcID = GenProcID751    FROM @DBQueueCurrent p752    IF @GenProcID IS NULL753    BEGIN754      IF @Debugswitch > 0755      BEGIN756        print 'ausstieg 5'757      END758      GOTO EndLabel759    END760    EXEC QBM_PSessionContextSet 'GenProcID',761      @GenProcID762    EXEC QBM_PSessionContextSet 'XUser',763      @XUser764    UPDATE @Helper765    SET NextReminder = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(),766    ws.MinutesReminder,767    h.UID_PersonHead),768    IsToUpdateNextReminder = 1769    FROM @Helper h770    JOIN @DBQueueCurrent p771      ON h.UID_ElementKey = p.UID_Parameter772    JOIN QERWorkingStep ws773      ON h.uid_QERWorkingStep = ws.uid_QERWorkingStep774    JOIN PersonWantsOrg pwo775      WITH(readpast)776      ON h.UID_ElementKey = pwo.UID_PersonWantsOrg777    JOIN PWODecisionRule r778      ON h.UID_PWODecisionRule = r.UID_PWODecisionRule779    WHERE780      ws.MinutesReminder > 0 AND isnull(h.NextReminder,781    @MaxDate2200) = @MaxDate2200 AND isnull(h.Decision,782    '') IN('',783    'Q') AND r.UID_Task IS NULL AND h.levelnumber = pwo.DecisionLevel784    UPDATE @Helper785    SET NextReminder = @MaxDate2200,786    IsToUpdateNextReminder = 1787    FROM @Helper h788    JOIN @DBQueueCurrent p789      ON h.UID_ElementKey = p.UID_Parameter790    JOIN QERWorkingStep ws791      ON h.uid_QERWorkingStep = ws.UID_QERWorkingStep792    JOIN PersonWantsOrg pwo793      WITH(readpast)794      ON h.UID_ElementKey = pwo.UID_PersonWantsOrg795    JOIN pwodecisionrule r796      ON h.UID_PWODecisionRule = r.UID_PWODecisionRule797    WHERE798      h.NextReminder < @MaxDate2200 AND(ws.MinutesReminder = 0 OR isnull(h.Decision, '') NOT IN('', 'Q') OR h.levelnumber <> pwo.DecisionLevel OR799    r.UID_Task > ' ')800    UPDATE @Helper801    SET NextReminder = @MaxDate2200,802    IsToUpdateNextReminder = 1803    FROM @Helper h804    JOIN @DBQueueCurrent p805      ON h.UID_ElementKey = p.UID_Parameter806    JOIN PersonWantsOrg pwo807      WITH(readpast)808      ON h.UID_ElementKey = pwo.UID_PersonWantsOrg809    WHERE810      h.NextReminder < @MaxDate2200 AND isnull(h.Decision,811    '') = '' AND h.levelnumber = pwo.DecisionLevel AND EXISTS(812    SELECT TOP 1 1813    FROM @Helper hq814    WHERE815      hq.UID_ElementKey = h.UID_ElementKey AND hq.Decision = 'Q' AND hq.LevelNumber = h.LevelNumber)816    DECLARE @PWOSenden QBM_YCursorBuffer817    INSERT INTO @PWOSenden(UID1,818    UID2,819    UID3,820    Int1,821    Bit1,822    ObjectKey1,823    Int2,824    UID4)825    SELECT826      h.UID_HelperKey AS UID_PWOHelperPWO,827      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,828      p.GenProcID,829      @CfgUseGenProcID) AS GenProcID,830      pe.UID_Person,831      ws.MinutesReminder,832      sign(len(isnull(ws.UID_DialogRichMailReminder, ''))),833      h.XObjectKey,834      pwo.DecisionLevel,835      pwo.UID_PersonWantsOrg836    FROM @Helper h837    JOIN @DBQueueCurrent p838      ON h.UID_ElementKey = p.UID_Parameter839    JOIN PersonWantsOrg pwo840      WITH(readpast)841      ON h.UID_ElementKey = pwo.UID_PersonWantsOrg AND h.levelnumber = pwo.DecisionLevel842    JOIN Person pe843      WITH(readpast)844      ON pe.UID_Person = h.UID_PersonHead845    JOIN QERWorkingStep ws846      ON h.uid_QERWorkingStep = ws.uid_QERWorkingStep847    WHERE848      h.NextReminder < getutcdate() AND dbo.QBM_FGIJobCreatedExists(h.XObjectKey) = 0 AND h.RulerLevel < 2849    SELECT @ElementCount = @@ROWCOUNT850    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1851    SELECT @ElementLast = @@IDENTITY852    WHILE @ElementIndex <= @ElementLast853    BEGIN854      SELECT855        TOP 1 @UID_PWOHelperPWO = bu.UID1,856        @GenProcID = bu.UID2,857        @UID_PersonHead = bu.UID3,858        @MinutesReminder = bu.Int1,859        @MailExists = bu.Bit1,860        @ObjectkeyForMail = bu.ObjectKey1,861        @CurrentDecisionLevel = bu.Int2,862        @uid_Personwantsorg = bu.UID4863      FROM @PWOSenden bu864      WHERE865        bu.ElementIndex = @ElementIndex866      EXEC QBM_PSessionContextSet 'GenProcID',867        @GenProcID868      EXEC QBM_PSessionContextSet 'XUser',869        @XUser870      UPDATE @Helper871      SET NextReminder = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(),872      @MinutesReminder,873      @UID_PersonHead),874      IsToUpdateNextReminder = 1875      WHERE876        UID_HelperKey = @UID_PWOHelperPWO877      IF @MailExists > 0878      BEGIN879        DELETE @ObjectkeySForMail880        INSERT INTO @ObjectkeySForMail(Parameter1)881        SELECT @ObjectkeyForMail882        SELECT883          @WhereclauseForMail = ' exists(select top 1 1 884												from personwantsorg pwo 885												where pwo.uid_personwantsorg  = ''' + @uid_Personwantsorg886          + ''' 887												and pwo.decisionlevel = ' + str(@CurrentDecisionLevel) + ' 888												)'889        EXEC QBM_PJobCreate_HOFireEvent_L 'PWOHelperPWO',890          @ObjectkeySForMail,891        'Remind',892          @GenProcID,893          @priority = 10,894          @BasisObjectKey = @ObjectkeyForMail,895          @CheckForExisting = 1,896          @whereclauseAdditional = @WhereclauseForMail,897          @AdditionalObjectKeysAffected = DEFAULT898      END899      SELECT @ElementIndex += 1900    END901    UPDATE PWOHelperPWO902    SET NextReminder = CASE h.IsToUpdateNextReminder903    WHEN 1 THEN904    h.NextReminder905    ELSE t.NextReminder906    END,907    NextAutomaticDecision = CASE h.IsToUpdateNextAutomaticDec908    WHEN 1 THEN909    h.NextAutomaticDecision910    ELSE t.NextAutomaticDecision911    END912    FROM PWOHelperPWO t913    JOIN @Helper h914      ON t.UID_PWOHelperPWO = h.UID_HelperKey915    WHERE916      h.IsToUpdateNextReminder = 1 OR h.IsToUpdateNextAutomaticDec = 1917  END TRY918  BEGIN CATCH919    EXEC QBM_PSessionErrorAdd DEFAULT920    RAISERROR('',921    18,922    1)923      WITH NOWAIT924  END CATCH925  EndLabel:926  EXEC QBM_PSessionContextSet 'GenProcID',927    @GenProcID_R928  EXEC QBM_PSessionContextSet 'XUser',929    @XUser_R930  RETURN931END