Back to OIM Explorer

dbo.ATT_PAttestationHelperFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. HOFireEvent -> AttestationHelper.DecisionRequired at line 118; HOFireEvent -> AttestationHelper.DecisionRequired at line 143; Single DBQueue insert -> ATT-K-AttestationCheckValid / ATT_ZAttestationCheckValid at line 118; References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 21.157 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • HOFireEvent -> AttestationHelper.DecisionRequired at line 118
  • HOFireEvent -> AttestationHelper.DecisionRequired at line 143
  • Single DBQueue insert -> ATT-K-AttestationCheckValid / ATT_ZAttestationCheckValid at line 118
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOFireEvent*
  • References QBM_PDBQueueInsert_Single

Typed Edges

  • fires event AttestationHelper.DecisionRequired HOFireEvent -> AttestationHelper.DecisionRequired at line 118
  • queues DBQueue task ATT_ZAttestationCheckValid at line 118 Single DBQueue insert -> ATT-K-AttestationCheckValid / ATT_ZAttestationCheckValid at line 118
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVStringToInt source text reference
  • references source dbo.QBM_FCVStringTrimLDSPrefix source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QER_FCVRuleGUIDToFunctionName source text reference
  • references source dbo.QER_FGIPWORulerOrigin source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOFireEvent source text reference
  • references source dbo.QBM_PJobCreate_HOFireEvent_B source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task ATT-K-AttestationCheckValid -> ATT_ZAttestationCheckValid QBM_PDBQueueInsert_Single 'ATT-K-AttestationCheckValid', @UID_AttestationCase, null, @GenProcID end if @isToDelete = 1 begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID delete AttestationHelper where UID_AttestationCase = @UID_Atte…

References

Referenced By

Complete Source

SQL740 lines
1CREATE PROCEDURE ATT_PAttestationHelperFill(2  @UID_AttestationCase varchar(38),3  @GenProcID varchar(38),4  @isNewDecisionLevel BIT = 0,5  @CurrentLevelOnly int = 06)7  WITH8EXECUTE9AS10'dbo' AS11BEGIN12  DECLARE @SubLevelNumber int13  DECLARE @LevelNumber int14  DECLARE @LevelNumber_alt int15  DECLARE @ObjectkeyForMail varchar(138)16  DECLARE @AttestationHelper_Old QER_YPWOHelper17  DECLARE @AttestationHelper_New QER_YPWOHelper18  DECLARE @PersonAndLevel QBM_YParameterList19  DECLARE @FunctionName varchar(30)20  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')21  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')22  DECLARE @WhereclauseForMail nvarchar(1000)23  DECLARE @SQLCmd nvarchar(max)24  DECLARE @isToInsert BIT25  DECLARE @isToDelete BIT26  DECLARE @makeEmpty BIT27  DECLARE @CurrentDecisionLevel int28  DECLARE @UID_PwoDecisionRule varchar(38)29  DECLARE @UID_QERWorkingStep varchar(38)30  DECLARE @UID_AttestationHistory varchar(38)31  DECLARE @countApproverNeeded int32  DECLARE @countApproverAvailable int33  DECLARE @ElementLast int34  DECLARE @ElementBuffer_Rules QBM_YCursorBuffer35  DECLARE @ElementBuffer_Notification QBM_YCursorBuffer36  DECLARE @ElementCount int37  DECLARE @ElementIndex int38  DECLARE @XUser nvarchar(64) = object_name(@@procid)39  DECLARE @Xdate datetime = getutcdate()40  SET XACT_ABORT OFF41  BEGIN TRY42    SELECT TOP 1 @CurrentDecisionLevel = decisionlevel43    FROM attestationCase44    WHERE45      UID_AttestationCase = @UID_AttestationCase46    SELECT @makeEmpty = 047    IF EXISTS(48      SELECT TOP 1 149      FROM AttestationCase ac50      WHERE51        ac.UID_AttestationCase = @UID_AttestationCase AND ac.isclosed = 1)52    BEGIN53      SELECT @makeEmpty = 154    END55    IF EXISTS(56      SELECT TOP 1 157      FROM AttestationCase ac58      JOIN AttestationPolicy ap59        ON ac.UID_AttestationPolicy = ap.UID_AttestationPolicy60      WHERE61        ac.UID_AttestationCase = @UID_AttestationCase AND ap.isinactive = 1)62    BEGIN63      SELECT @makeEmpty = 164    END65    IF EXISTS(66      SELECT TOP 1 167      FROM AttestationHelper68      WHERE69        UID_AttestationCase = @UID_AttestationCase AND Decision IS NULL)70    BEGIN71      UPDATE AttestationHelper72      SET Decision = ''73      WHERE74        UID_AttestationCase = @UID_AttestationCase AND Decision IS NULL75    END76    IF EXISTS(77      SELECT TOP 1 178      FROM AttestationHelper79      WHERE80        UID_AttestationCase = @UID_AttestationCase AND Decision = '-')81    BEGIN82      DELETE AttestationHelper83      WHERE84        UID_AttestationCase = @UID_AttestationCase AND Decision = '-'85    END86    INSERT INTO @AttestationHelper_Old(UID_PWODecisionRule,87    uid_personHead,88    levelnumber,89    SubLevelNumber,90    uid_QERWorkingStep,91    decision,92    UID_PersonAdditional,93    UID_PersonInsteadOf,94    IsFromDelegation,95    RulerLevel,96    UID_PWORulerOrigin,97    NextAutomaticDecision)98    SELECT99      isnull(UID_PWODecisionRule,100      ''),101      uid_personHead,102      isnull(levelnumber,103      0),104      isnull(SubLevelNumber,105      0),106      ah.uid_QERWorkingStep,107      isnull(ah.decision,108      ''),109      UID_PersonAdditional,110      UID_PersonInsteadOf,111      IsFromDelegation,112      RulerLevel,113      UID_PWORulerOrigin,114      NextAutomaticDecision115    FROM AttestationHelper ah116    WHERE117      ah.UID_AttestationCase = @UID_AttestationCase AND(@CurrentLevelOnly = 0 OR ah.LevelNumber = @CurrentDecisionLevel)118    IF @makeEmpty = 1119    BEGIN120      UPDATE @AttestationHelper_Old121      SET isToDelete = 1122      GOTO publizieren123    END124    UPDATE @AttestationHelper_Old125    SET isToDelete = 1126    FROM @AttestationHelper_Old zk127    JOIN(128    SELECT *129    FROM @AttestationHelper_Old) AS d130      ON zk.UID_PWODecisionRule = d.UID_PWODecisionRule AND zk.uid_QERWorkingStep = d.uid_QERWorkingStep AND zk.levelnumber = d.levelnumber AND zk.Sublevelnumber131    = d.Sublevelnumber132    WHERE133      zk.UID_PersonInsteadOf = d.UID_PersonHead AND d.UID_PersonInsteadOf > ' '134    DELETE @ElementBuffer_Rules135    INSERT INTO @ElementBuffer_Rules(UID1,136    UID2,137    Ident1,138    Int1,139    Int2,140    Int3)141    SELECT142      DISTINCT ws.UID_PWODecisionRule,143      ws.UID_QERWorkingStep,144      dbo.QER_FCVRuleGUIDToFunctionName(ws.UID_PWODecisionRule),145      ws.LevelNumber,146      ws.SubLevelNumber,147      ws.CountApprover148    FROM AttestationCase ac149    JOIN AttestationPolicy ap150      ON ac.uid_attestationPolicy = ap.uid_attestationPolicy151    JOIN QERWorkingStep ws152      ON ac.UID_QERWorkingMethod = ws.UID_QERWorkingMethod153    WHERE154      ac.UID_AttestationCase = @UID_AttestationCase AND(@CurrentLevelOnly = 0 OR(ac.DecisionLevel = @CurrentDecisionLevel AND ws.LevelNumber =155    @CurrentDecisionLevel))156    SELECT @ElementCount = @@ROWCOUNT157    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1158    SELECT @ElementLast = @@IDENTITY159    WHILE @ElementIndex <= @ElementLast160    BEGIN161      SELECT162        TOP 1 @UID_PWODecisionRule = bu.UID1,163        @UID_QERWorkingStep = bu.UID2,164        @FunctionName = bu.Ident1,165        @LevelNumber = bu.Int1,166        @SublevelNumber = bu.Int2,167        @countApproverNeeded = bu.Int3168      FROM @ElementBuffer_Rules bu169      WHERE170        bu.ElementIndex = @ElementIndex171      IF EXISTS(172        SELECT TOP 1 1173        FROM sys.objects o174        WHERE175          o.name = @FunctionName AND o.type IN('IF', 'TF'))176      BEGIN177        SELECT178          @SQLcmd = CONCAT('179					select s.uid_person,  max(s.UID_PWORulerOrigin), s.RulerLevel180						from ( select a1.uid_person, ''0'' as RulerLevel, a1.UID_PWORulerOrigin181								from dbo.'182          ,183          @FunctionName,184          ' (''' + @UID_AttestationCase,185          ''', ''',186          @uid_QERWorkingStep,187          ''') as a1188								union all189								select  pio.UID_Person, ''1'' as Rulerlevel, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin190									from QERWorkingStep s join PersonInBaseTree pio on s.UID_AERoleFallBack = pio.UID_Org191																					and pio.XOrigin > 0192									where s.UID_QERWorkingStep = '''193          ,194          @UID_QERWorkingStep,195          '''196								union all 197								select  pio.UID_Person, ''2'' as Rulerlevel, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin198									from PersonInBaseTree pio 199									where pio.UID_Org = ''ATT-AEROLE-ATTESTATION-INTERVENTION''200									and pio.XOrigin > 0201							) as s202						group by s.uid_person, s.RulerLevel203								'204          )205        DELETE @PersonAndLevel206        INSERT INTO @PersonAndLevel(Parameter1,207        Parameter2,208        Parameter3)209        EXEC sp_executesql @SQLcmd210        INSERT INTO @AttestationHelper_New(UID_PWODecisionRule,211        uid_personHead,212        uid_QERWorkingStep,213        levelnumber,214        Sublevelnumber,215        istoInsert,216        RulerLevel,217        UID_PWORulerOrigin,218        UID_PersonSubstituteSender)219        SELECT220          @uid_Pwodecisionrule,221          s.uid_person,222          @UID_QERWorkingStep,223          @levelnumber,224          @Sublevelnumber,225          0,226          dbo.QBM_FCVStringToInt(s.RulerLevel,227          0),228          max(s.UID_PWORulerOrigin) AS UID_PWORulerOrigin,229          max(isnull(s.UID_PersonSubstituteSender, ''))230        FROM(231        SELECT232          p.Parameter1 AS UID_Person,233          p.Parameter2 AS UID_PWORulerOrigin,234          p.Parameter3 AS RulerLevel,235          NULL AS UID_PersonSubstituteSender236        FROM @PersonAndLevel p237        UNION all238        SELECT239          us.UID_PersonReceiver,240          us.UID_PersonWantsOrg AS UID_PWORulerOrigin,241          p.Parameter3 AS RulerLevel,242          us.UID_PersonSender AS UID_PersonSubstituteSender243        FROM @PersonAndLevel p244        JOIN QERUniversalSubstitute us245          ON p.Parameter1 = us.UID_PersonSender246        WHERE247          us.IsCurrentlyActive = 1 AND us.UseForAttestation = 1 AND NOT EXISTS(248        SELECT TOP 1 1249        FROM @PersonAndLevel pal250        WHERE251          pal.Parameter1 = us.UID_PersonReceiver AND pal.Parameter3 = p.Parameter3)) AS s252        GROUP BY s.UID_Person,253        s.RulerLevel254        UPDATE @AttestationHelper_New255        SET UID_PWORulerOrigin = su.UID_PersonWantsOrg256        FROM @AttestationHelper_New n257        JOIN QERUniversalSubstitute su258          ON n.UID_PersonHead = su.UID_PersonReceiver AND(su.UseForHeadPerson = 1 OR su.UseForHeadOrg = 1) AND su.IsCurrentlyActive = 1259        WHERE260          n.UID_PWORulerOrigin IS NULL AND EXISTS(261        SELECT TOP 1 1262        FROM @AttestationHelper_New se263        WHERE264          se.UID_PersonHead = su.UID_PersonSender AND se.levelnumber = n.levelnumber AND se.Sublevelnumber = n.sublevelnumber)265        IF dbo.QBM_FGIConfigparmValue('QER\Attestation\PersonToAttestNoDecide') = '1'266        BEGIN267          DELETE @AttestationHelper_New268          FROM @AttestationHelper_New ah269          JOIN QERWorkingStep s270            ON ah.uid_QERWorkingStep = s.UID_QERWorkingStep AND s.IgnoreNoDecideForPerson = 0271          JOIN(272          SELECT he.UID_PersonAlsoMe AS UID_Person273          FROM(274          SELECT275            ac.ObjectKey1, ac.ObjectKey2, ac.ObjectKey3, ac.ObjectKeyBase276          FROM AttestationCase ac277          WHERE278            ac.UID_AttestationCase = @UID_AttestationCase) r unpivot(wert FOR spalte IN(ObjectKey1, ObjectKey2,279          ObjectKey3, ObjectKeyBase)) AS un280          JOIN Person p281            ON un.wert = p.XObjectKey282          JOIN QER_VPersonsAreMe he283            ON p.UID_Person = he.UID_PersonOrigin) AS weg284            ON weg.UID_Person IN(ah.uid_personHead,285          ah.UID_PersonSubstituteSender) OR(isnull(ah.UID_PersonAdditional, '') = weg.UID_Person AND ah.RulerLevel < 2) OR(isnull(ah.UID_PersonInsteadOf286          , '') = weg.UID_Person AND ah.RulerLevel < 2)287        END288        SELECT TOP 1 @countApproverAvailable = COUNT(*)289        FROM @AttestationHelper_New n290        WHERE291          n.RulerLevel = 0 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber292        IF @countApproverAvailable >= CASE293        WHEN @countApproverNeeded < 0 THEN294        @countApproverAvailable295        WHEN @countApproverNeeded = 0 THEN296        1297        ELSE @countApproverNeeded298        END AND @countApproverAvailable > 0299        BEGIN300          DELETE @AttestationHelper_New301          FROM @AttestationHelper_New n302          WHERE303            n.RulerLevel = 1 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber304        END305        DELETE @AttestationHelper_New306        FROM @AttestationHelper_New n307        WHERE308          n.RulerLevel = 0 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber AND309        EXISTS(310        SELECT TOP 1 1311        FROM @AttestationHelper_New n1312        WHERE313          n1.RulerLevel = 1 AND n1.uid_QERWorkingStep = @UID_QERWorkingStep AND n1.levelnumber = @LevelNumber AND n1.Sublevelnumber = @SubLevelNumber AND314        n1.uid_personHead = n.uid_personHead)315        IF @UID_PwoDecisionRule IN(316          SELECT r.UID_PWODecisionRule317          FROM PWODecisionRule r318          WHERE319            r.UID_Task > ' ')320        BEGIN321          DELETE @AttestationHelper_New322          FROM @AttestationHelper_New n323          WHERE324            n.RulerLevel > 0 AND n.uid_QERWorkingStep = @UID_QERWorkingStep AND n.levelnumber = @LevelNumber AND n.Sublevelnumber = @SubLevelNumber325        END326      END327      SELECT @ElementIndex += 1328    END329    UPDATE @AttestationHelper_Old330    SET isToDelete = 1331    WHERE332      uid_personhead IS NULL AND isnull(UID_PWODecisionRule,333    '') NOT IN(334    SELECT UID_PWODecisionRule335    FROM pwodecisionrule336    WHERE337      UID_Task > ' ')338    DELETE @AttestationHelper_New339    WHERE340      uid_personhead IS NULL AND UID_PWODecisionRule NOT IN(341    SELECT UID_PWODecisionRule342    FROM pwodecisionrule343    WHERE344      UID_Task > ' ')345    UPDATE @AttestationHelper_new346    SET UID_PersonAdditional = o.UID_PersonAdditional,347    UID_PersonInsteadOf = o.UID_PersonInsteadOf348    FROM @AttestationHelper_new n349    JOIN @AttestationHelper_Old o350      ON n.UID_PWODecisionRule = o.UID_PWODecisionRule AND n.uid_personHead = o.uid_personHead AND n.uid_QERWorkingStep = o.uid_QERWorkingStep AND n.levelnumber351    = o.levelnumber AND n.Sublevelnumber = o.Sublevelnumber352    WHERE353      o.UID_PersonAdditional > ' ' OR o.UID_PersonInsteadOf > ' '354    INSERT INTO @AttestationHelper_New(UID_PWODecisionRule,355    uid_personHead,356    uid_QERWorkingStep,357    levelnumber,358    Sublevelnumber,359    istoInsert,360    UID_PersonAdditional,361    UID_PersonInsteadOf,362    IsFromDelegation,363    RulerLevel)364    SELECT365      DISTINCT de.UID_PWODecisionRule,366      de.UID_PersonAdditional,367      de.uid_QERWorkingStep,368      de.levelnumber,369      1,370      0,371      NULL,372      NULL,373      1,374      0375    FROM @AttestationHelper_New de376    WHERE377      de.UID_PersonAdditional > ' ' AND NOT EXISTS(378    SELECT TOP 1 1379    FROM @AttestationHelper_New n380    WHERE381      n.UID_PWODecisionRule = de.UID_PWODecisionRule AND n.uid_personHead = de.UID_PersonAdditional AND n.uid_QERWorkingStep = de.uid_QERWorkingStep AND382    n.levelnumber = de.levelnumber AND n.Sublevelnumber = 1) AND EXISTS(383    SELECT TOP 1 1384    FROM QERWorkingStep ds385    WHERE386      ds.UID_QERWorkingStep = de.uid_QERWorkingStep AND ds.IsAdditionalAllowed = 1)387    IF @@rowcount > 0388    BEGIN389      INSERT INTO @AttestationHelper_New(UID_PWODecisionRule,390      uid_personHead,391      UID_QERWorkingStep,392      levelnumber,393      Sublevelnumber,394      istoInsert,395      UID_PersonAdditional,396      UID_PersonInsteadOf,397      IsFromDelegation,398      RulerLevel,399      UID_PWORulerOrigin)400      SELECT401        DISTINCT de.UID_PWODecisionRule,402        esc.UID_Person,403        de.UID_QERWorkingStep,404        de.levelnumber,405        1,406        0,407        NULL,408        NULL,409        1,410        2,411        esc.UID_PWORulerOrigin412      FROM @AttestationHelper_New de CROSS413      JOIN(414      SELECT415        pio.UID_Person,416        dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) AS UID_PWORulerOrigin417      FROM PersonInBaseTree pio418      WHERE419        pio.UID_Org = 'ATT-AEROLE-ATTESTATION-INTERVENTION' AND pio.XOrigin > 0) AS esc420      WHERE421        de.UID_PersonAdditional > ' ' AND NOT EXISTS(422      SELECT TOP 1 1423      FROM @AttestationHelper_New n424      WHERE425        n.UID_PWODecisionRule = de.UID_PWODecisionRule AND n.uid_personHead = esc.UID_Person AND n.UID_QERWorkingStep = de.UID_QERWorkingStep AND426      n.levelnumber = de.levelnumber AND n.Sublevelnumber = 1)427    END428    INSERT INTO @AttestationHelper_New(UID_PWODecisionRule,429    uid_personHead,430    uid_QERWorkingStep,431    levelnumber,432    Sublevelnumber,433    istoInsert,434    UID_PersonAdditional,435    UID_PersonInsteadOf,436    IsFromDelegation,437    RulerLevel)438    SELECT439      DISTINCT de.UID_PWODecisionRule,440      de.UID_PersonInsteadOf,441      de.uid_QERWorkingStep,442      de.levelnumber,443      0,444      0,445      NULL,446      NULL,447      1,448      0449    FROM @AttestationHelper_New de450    WHERE451      de.UID_PersonInsteadOf > ' ' AND NOT EXISTS(452    SELECT TOP 1 1453    FROM @AttestationHelper_New n454    WHERE455      n.UID_PWODecisionRule = de.UID_PWODecisionRule AND n.uid_personHead = de.UID_PersonInsteadOf AND n.uid_QERWorkingStep = de.uid_QERWorkingStep AND456    n.levelnumber = de.levelnumber AND n.Sublevelnumber = 0) AND EXISTS(457    SELECT TOP 1 1458    FROM QERWorkingStep ds459    WHERE460      ds.UID_QERWorkingStep = de.uid_QERWorkingStep AND ds.IsInsteadOfAllowed = 1)461    UPDATE @AttestationHelper_Old462    SET isToDelete = 1463    FROM @AttestationHelper_Old o464    WHERE465      (NOT EXISTS(466    SELECT TOP 1 1467    FROM @AttestationHelper_New n468    WHERE469      isnull(n.UID_PWODecisionRule, '') = isnull(o.UID_PWODecisionRule, '') AND isnull(n.uid_personHead,470    '') = isnull(o.uid_personHead, '') AND n.levelnumber = o.levelnumber AND n.Sublevelnumber = o.Sublevelnumber AND isnull(n.uid_QERWorkingStep,471    '') = isnull(o.uid_QERWorkingStep, '') AND isnull(n.UID_PWORulerOrigin, '') = isnull(o.UID_PWORulerOrigin,472    '') AND ISNULL(n.rulerlevel, 0) = ISNULL(o.rulerlevel, 0)) OR(isnull(o.uid_personHead, '') > ' ' AND NOT EXISTS(473    SELECT TOP 1 1474    FROM Person p475    WHERE476      p.UID_Person = isnull(o.uid_personHead, '') AND p.IsInActive = 0))) AND o.decision IN('',477    '-')478    IF EXISTS(479      SELECT TOP 1 1480      FROM AttestationCase481      WHERE482        UID_AttestationCase = @UID_AttestationCase AND IsReserved =1)483    BEGIN484      IF EXISTS(485        SELECT TOP 1 1486        FROM AttestationCase pwo,487      @AttestationHelper_Old o488        WHERE489          pwo.UID_AttestationCase = @UID_AttestationCase AND o.uid_personHead = pwo.UID_PersonHead AND o.isToDelete = 1)490      BEGIN491        UPDATE AttestationCase492        SET IsReserved = 0,493        XDateUpdated = @Xdate,494        XUserUpdated = @XUser495        WHERE496          UID_AttestationCase = @UID_AttestationCase497        UPDATE @AttestationHelper_Old498        SET isToDelete = 1499        WHERE500          UID_PWODecisionRule = 'ATT-PWODecisionRule-QP' OR decision = 'Q'501        SELECT @UID_AttestationHistory = NEWID()502        EXEC QBM_PSessionContextSet 'GenProcID',503          @GenProcID504        INSERT INTO AttestationHistory(DecisionType,505        IsDecisionBySystem,506        UID_AttestationHistory,507        UID_PersonHead,508        UID_AttestationCase,509        DisplayPersonHead,510        ReasonHead,511        DateHead,512        XDateInserted,513        XDateUpdated,514        XUserInserted,515        XUserUpdated,516        DecisionLevel,517        XTouched,518        XObjectKey)519        SELECT520          'RecallQuery',521          1,522          @UID_AttestationHistory,523          NULL,524          @UID_AttestationCase,525          dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval.|'),526          dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Query to Person finished due to changed deciders.|'),527          GetUTCDate(),528          GetUTCDate(),529          GetUTCDate(),530          'sa',531          'sa',532          0,533          '',534          dbo.QBM_FCVElementToObjectKey1('AttestationHistory',535          'UID_AttestationHistory',536          @UID_AttestationHistory)537      END538    END539    UPDATE @AttestationHelper_Old540    SET isToDelete = 1541    FROM @AttestationHelper_Old o542    WHERE543      NOT EXISTS(544    SELECT TOP 1 1545    FROM AttestationCase ac546    WHERE547      ac.UID_AttestationCase = @UID_AttestationCase AND isclosed = 0)548    UPDATE @AttestationHelper_New549    SET isToInsert = 1550    FROM @AttestationHelper_New n551    WHERE552      NOT EXISTS(553    SELECT TOP 1 1554    FROM @AttestationHelper_Old o555    WHERE556      o.UID_PWODecisionRule = n.UID_PWODecisionRule AND isnull(o.uid_personHead, '') = isnull(n.uid_personHead,557    '') AND o.levelnumber = n.levelnumber AND o.Sublevelnumber = n.Sublevelnumber AND isnull(o.UID_QERWorkingStep,558    '') = isnull(n.UID_QERWorkingStep, '') AND isnull(o.UID_PWORulerOrigin, '') = isnull(n.UID_PWORulerOrigin,559    '') AND ISNULL(o.rulerlevel, 0) = ISNULL(n.rulerlevel, 0) AND o.isToDelete = 0) publizieren:560    SELECT @isToInsert = 0561    SELECT @isToDelete = 0562    IF EXISTS(563      SELECT TOP 1 1564      FROM @AttestationHelper_New565      WHERE566        isToInsert = 1)567    BEGIN568      SELECT @isToInsert = 1569    END570    IF EXISTS(571      SELECT TOP 1 1572      FROM @AttestationHelper_Old573      WHERE574        isTodelete = 1)575    BEGIN576      SELECT @isToDelete = 1577    END578    IF(@isToInsert = 1 OR @isToDelete = 1) AND @makeEmpty = 0579    BEGIN580      EXEC QBM_PDBQueueInsert_Single 'ATT-K-AttestationCheckValid',581        @UID_AttestationCase,582      NULL,583        @GenProcID584    END585    IF @isToDelete = 1586    BEGIN587      EXEC QBM_PSessionContextSet 'GenProcID',588        @GenProcID589      DELETE AttestationHelper590      WHERE591        UID_AttestationCase = @UID_AttestationCase AND EXISTS(592      SELECT TOP 1 1593      FROM @AttestationHelper_Old o594      WHERE595        isnull(o.UID_PWODecisionRule, '') = isnull(AttestationHelper.UID_PWODecisionRule, '') AND isnull(o.uid_personHead,596      '') = isnull(AttestationHelper.uid_personHead, '') AND o.levelnumber = AttestationHelper.levelnumber AND o.Sublevelnumber = AttestationHelper.Sublevelnumber AND597      o.Rulerlevel = AttestationHelper.Rulerlevel AND isnull(o.UID_PWORulerOrigin, '') = isnull(AttestationHelper.UID_PWORulerOrigin,598      '') AND o.isToDelete = 1)599    END600    IF @isToInsert = 1601    BEGIN602      UPDATE @AttestationHelper_New603      SET UID_PrimaryKey = newid()604      WHERE605        istoinsert = 1606      UPDATE @AttestationHelper_new607      SET NextAutomaticDecision = isnull(g.MaxDate,608      '2200-01-01')609      FROM @AttestationHelper_new b610      LEFT611      OUTER612      JOIN(613      SELECT614        a.LevelNumber,615        a.Sublevelnumber,616        a.RulerLevel,617        MAX(a.NextAutomaticDecision) AS MaxDate618      FROM @AttestationHelper_Old a619      GROUP BY a.LevelNumber,620      a.Sublevelnumber,621      a.RulerLevel) AS g622        ON b.LevelNumber = g.LevelNumber AND b.Sublevelnumber = g.Sublevelnumber AND b.RulerLevel = g.RulerLevel623      WHERE624        b.IstoInsert = 1625      EXEC QBM_PSessionContextSet 'GenProcID',626        @GenProcID627      INSERT INTO AttestationHelper(UID_AttestationHelper,628      UID_AttestationCase,629      UID_PWODecisionRule,630      uid_personHead,631      levelnumber,632      SubLevelNumber,633      Decision,634      UID_QERWorkingStep,635      NextReminder,636      NextAutomaticDecision,637      XObjectKey,638      UID_PersonAdditional,639      UID_PersonInsteadOf,640      IsFromDelegation,641      RulerLevel,642      UID_PWORulerOrigin)643      SELECT644        UID_PrimaryKey,645        @UID_AttestationCase,646        UID_PWODecisionRule,647        uid_personHead,648        levelnumber,649        SubLevelNumber,650      CASE651        WHEN n.UID_PersonInsteadOf > ' ' THEN652      'D'653      ELSE ''654      END AS Decision,655      UID_QERWorkingStep,656      '2200-01-01',657      n.NextAutomaticDecision,658      dbo.QBM_FCVElementToObjectKey1('AttestationHelper',659      'UID_AttestationHelper',660      UID_PrimaryKey),661      UID_PersonAdditional,662      UID_PersonInsteadOf,663      IsFromDelegation,664      n.RulerLevel,665      UID_PWORulerOrigin666      FROM @AttestationHelper_New n667      WHERE668        istoinsert = 1669    END670    IF(@isToInsert = 1 OR @isToDelete = 1 OR @isNewDecisionLevel = 1) AND @makeEmpty = 0671    BEGIN672      DELETE @ElementBuffer_Notification673      INSERT INTO @ElementBuffer_Notification(ObjectKey1)674      SELECT h.XObjectKey675      FROM @AttestationHelper_New n676      JOIN AttestationHelper h677        ON n.UID_PrimaryKey = h.uid_AttestationHelper AND h.decision = ''678      JOIN Attestationcase pwo679        ON h.uid_Attestationcase = pwo.uid_Attestationcase AND h.levelnumber = pwo.decisionlevel680      JOIN QERWorkingStep ws681        ON h.UID_QERWorkingStep = ws.UID_QERWorkingStep AND ws.UID_DialogRichMailInsert > ' '682      JOIN pwodecisionrule r683        ON ws.UID_PWODecisionRule = r.UID_PWODecisionRule AND r.UID_Task IS NULL684      JOIN person p685        ON h.uid_personhead = p.uid_person AND p.defaultemailaddress > ' '686      WHERE687        n.istoinsert = 1 AND h.RulerLevel < 2688      UNION689      SELECT h.XObjectKey690      FROM AttestationHelper h691      JOIN Attestationcase pwo692        ON h.uid_Attestationcase = @UID_AttestationCase AND pwo.uid_Attestationcase = @UID_AttestationCase AND h.levelnumber = pwo.decisionlevel AND h.decision693      = ''694      JOIN QERWorkingStep ws695        ON h.UID_QERWorkingStep = ws.UID_QERWorkingStep AND ws.UID_DialogRichMailInsert > ' '696      JOIN pwodecisionrule r697        ON ws.UID_PWODecisionRule = r.UID_PWODecisionRule AND r.UID_Task IS NULL698      JOIN person p699        ON h.uid_personhead = p.uid_person AND p.defaultemailaddress > ' '700      WHERE701        @isNewDecisionLevel = 1 AND h.RulerLevel < 2702      SELECT @ElementCount = @@ROWCOUNT703      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1704      SELECT @ElementLast = @@IDENTITY705      WHILE @ElementIndex <= @ElementLast706      BEGIN707        SELECT TOP 1 @ObjectkeyForMail = bu.ObjectKey1708        FROM @ElementBuffer_Notification bu709        WHERE710          bu.ElementIndex = @ElementIndex711        SELECT712          @WhereclauseForMail = N 'XObjectKey = ''' + @ObjectkeyForMail + N '''' + N ' and exists (select top 1 1 from Attestationcase where uid_Attestationcase  = '''713          + @uid_Attestationcase + ''' and decisionlevel = ' + str(@CurrentDecisionLevel) + ' )'714        EXEC QBM_PJobCreate_HOFireEvent_B 'AttestationHelper',715          @WhereclauseForMail,716        'DecisionRequired',717          @GenProcID,718          @AdditionalObjectKeysAffected = DEFAULT,719          @checkForExisting = 1,720          @priority = 10,721          @BasisObjectKey = @ObjectkeyForMail722        SELECT @ElementIndex += 1723      END724    END725  END TRY726  BEGIN CATCH727    EXEC QBM_PSessionErrorAdd DEFAULT728    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()729    RAISERROR(@Rethrow,730    18,731    1)732      WITH NOWAIT733  END CATCH734  ende:735  EXEC QBM_PSessionContextSet 'GenProcID',736    @GenProcID_R737  EXEC QBM_PSessionContextSet 'XUser',738    @XUser_R revert739  RETURN740END
Open raw exported source
SQL · Raw148 lines
1    create   procedure ATT_PAttestationHelperFill (@UID_AttestationCase varchar(38) , @GenProcID varchar(38) , @isNewDecisionLevel bit = 0  , @CurrentLevelOnly2 int = 0 ) with execute as 'dbo' as begin declare @SubLevelNumber int declare @LevelNumber int declare @LevelNumber_alt int  declare @ObjectkeyForMail 3varchar(138)  declare @AttestationHelper_Old QER_YPWOHelper declare @AttestationHelper_New QER_YPWOHelper declare @PersonAndLevel QBM_YParameterList   4 declare @FunctionName varchar(30)  declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext5('XUser') declare @WhereclauseForMail nvarchar(1000) declare @SQLCmd nvarchar(max) declare @isToInsert bit declare @isToDelete bit declare @makeEmpty bit6 declare @CurrentDecisionLevel int declare @UID_PwoDecisionRule varchar(38) declare @UID_QERWorkingStep varchar(38) declare @UID_AttestationHistory varchar7(38) declare @countApproverNeeded int declare @countApproverAvailable int declare @ElementLast int declare @ElementBuffer_Rules QBM_YCursorBuffer declare8 @ElementBuffer_Notification QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @XUser nvarchar(64) = object_name(@@procid) 9declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY select top 1 @CurrentDecisionLevel = decisionlevel from attestationCase where UID_AttestationCase10 = @UID_AttestationCase select @makeEmpty = 0  if exists (select top 1 1 from AttestationCase ac where ac.UID_AttestationCase = @UID_AttestationCase and11 ac.isclosed = 1 ) begin  select @makeEmpty = 1 end if exists (select top 1 1 from AttestationCase ac join AttestationPolicy ap on ac.UID_AttestationPolicy12 = ap.UID_AttestationPolicy where ac.UID_AttestationCase = @UID_AttestationCase and ap.isinactive = 1 ) begin  select @makeEmpty = 1 end  if exists (select13 top 1 1 from AttestationHelper where UID_AttestationCase = @UID_AttestationCase and Decision is null ) begin update AttestationHelper set Decision = ''14 where UID_AttestationCase = @UID_AttestationCase and Decision is null end   if exists (select top 1 1 from AttestationHelper where UID_AttestationCase15 = @UID_AttestationCase and Decision = '-' ) begin delete AttestationHelper where UID_AttestationCase = @UID_AttestationCase and Decision = '-' end  insert16 into @AttestationHelper_Old (UID_PWODecisionRule, uid_personHead, levelnumber, SubLevelNumber, uid_QERWorkingStep, decision , UID_PersonAdditional, UID_PersonInsteadOf17, IsFromDelegation, RulerLevel , UID_PWORulerOrigin , NextAutomaticDecision ) select isnull(UID_PWODecisionRule, ''), uid_personHead, isnull(levelnumber18,0), isnull(SubLevelNumber,0), ah.uid_QERWorkingStep, isnull(ah.decision,'') , UID_PersonAdditional, UID_PersonInsteadOf, IsFromDelegation, RulerLevel 19, UID_PWORulerOrigin , NextAutomaticDecision from AttestationHelper ah where ah.UID_AttestationCase = @UID_AttestationCase  and (@CurrentLevelOnly = 0 20or ah.LevelNumber = @CurrentDecisionLevel ) if @makeEmpty = 1 begin update @AttestationHelper_Old set isToDelete = 1 goto publizieren end  Update @AttestationHelper_Old21 set isToDelete = 1 from @AttestationHelper_Old zk join (select * from @AttestationHelper_Old ) as d on zk.UID_PWODecisionRule = d.UID_PWODecisionRule 22and zk.uid_QERWorkingStep = d.uid_QERWorkingStep and zk.levelnumber = d.levelnumber and zk.Sublevelnumber = d.Sublevelnumber where zk.UID_PersonInsteadOf23 = d.UID_PersonHead  and d.UID_PersonInsteadOf > ' '  delete  @ElementBuffer_Rules  insert into @ElementBuffer_Rules ( UID1  , UID2  , Ident1  , Int1  24, Int2  , Int3  ) select distinct ws.UID_PWODecisionRule, ws.UID_QERWorkingStep , dbo.QER_FCVRuleGUIDToFunctionName(ws.UID_PWODecisionRule) , ws.LevelNumber25 , ws.SubLevelNumber , ws.CountApprover from AttestationCase ac join AttestationPolicy ap on ac.uid_attestationPolicy = ap.uid_attestationPolicy join QERWorkingStep26 ws on ac.UID_QERWorkingMethod = ws.UID_QERWorkingMethod where ac.UID_AttestationCase = @UID_AttestationCase and (@CurrentLevelOnly = 0 or ( ac.DecisionLevel27 = @CurrentDecisionLevel and ws.LevelNumber = @CurrentDecisionLevel ) ) select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount28 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_PWODecisionRule = bu.UID1  , @UID_QERWorkingStep = bu.UID229  , @FunctionName = bu.Ident1  , @LevelNumber = bu.Int1  , @SublevelNumber = bu.Int2 , @countApproverNeeded = bu.Int3 from @ElementBuffer_Rules bu where30 bu.ElementIndex = @ElementIndex   if exists (select top 1 1 from sys.objects o where o.name = @FunctionName and o.type in ('IF'  , 'TF'  ) ) begin select31 @SQLcmd = concat('32					select s.uid_person,  max(s.UID_PWORulerOrigin), s.RulerLevel33						from ( select a1.uid_person, ''0'' as RulerLevel, a1.UID_PWORulerOrigin34								from dbo.'35 , @FunctionName , ' (''' + @UID_AttestationCase , ''', ''' , @uid_QERWorkingStep , ''') as a136								union all37								select  pio.UID_Person, ''1'' as Rulerlevel, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin38									from QERWorkingStep s join PersonInBaseTree pio on s.UID_AERoleFallBack = pio.UID_Org39																					and pio.XOrigin > 040									where s.UID_QERWorkingStep = '''41 , @UID_QERWorkingStep , '''42								union all 43								select  pio.UID_Person, ''2'' as Rulerlevel, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin44									from PersonInBaseTree pio 45									where pio.UID_Org = ''ATT-AEROLE-ATTESTATION-INTERVENTION''46									and pio.XOrigin > 047							) as s48						group by s.uid_person, s.RulerLevel49								'50 ) delete  @PersonAndLevel  insert into @PersonAndLevel (Parameter1, Parameter2, Parameter3) exec sp_executesql @SQLcmd insert into @AttestationHelper_New51 (UID_PWODecisionRule, uid_personHead, uid_QERWorkingStep , levelnumber, Sublevelnumber, istoInsert, RulerLevel , UID_PWORulerOrigin, UID_PersonSubstituteSender52 ) select @uid_Pwodecisionrule , s.uid_person, @UID_QERWorkingStep , @levelnumber, @Sublevelnumber, 0, dbo.QBM_FCVStringToInt( s.RulerLevel, 0) , max(s.UID_PWORulerOrigin53) as UID_PWORulerOrigin, max(isnull(s.UID_PersonSubstituteSender, '')) from ( select p.Parameter1 as UID_Person, p.Parameter2 as UID_PWORulerOrigin, p.Parameter354 as RulerLevel, null as UID_PersonSubstituteSender from @PersonAndLevel p union all select us.UID_PersonReceiver, us.UID_PersonWantsOrg as UID_PWORulerOrigin55, p.Parameter3 as RulerLevel , us.UID_PersonSender as UID_PersonSubstituteSender from @PersonAndLevel p join QERUniversalSubstitute us on p.Parameter1 56= us.UID_PersonSender where us.IsCurrentlyActive = 1 and us.UseForAttestation = 1 and not exists (select top 1 1 from @PersonAndLevel pal where pal.Parameter157 = us.UID_PersonReceiver and pal.Parameter3 = p.Parameter3) ) as s group by s.UID_Person, s.RulerLevel   update @AttestationHelper_New set UID_PWORulerOrigin58 = su.UID_PersonWantsOrg from @AttestationHelper_New n join QERUniversalSubstitute su on n.UID_PersonHead = su.UID_PersonReceiver  and (su.UseForHeadPerson59 = 1 or su.UseForHeadOrg = 1) and su.IsCurrentlyActive = 1 where n.UID_PWORulerOrigin is null and exists (select top 1 1 from @AttestationHelper_New se60 where se.UID_PersonHead = su.UID_PersonSender and se.levelnumber = n.levelnumber and se.Sublevelnumber = n.sublevelnumber )  if dbo.QBM_FGIConfigparmValue61('QER\Attestation\PersonToAttestNoDecide') = '1' begin delete  @AttestationHelper_New from @AttestationHelper_New ah join QERWorkingStep s on ah.uid_QERWorkingStep62 = s.UID_QERWorkingStep  and s.IgnoreNoDecideForPerson = 0 join (select he.UID_PersonAlsoMe as UID_Person  from ( select ac.ObjectKey1, ac.ObjectKey2, 63ac.ObjectKey3, ac.ObjectKeyBase from AttestationCase ac where ac.UID_AttestationCase = @UID_AttestationCase ) r unpivot ( wert for spalte in (ObjectKey164, ObjectKey2, ObjectKey3, ObjectKeyBase) ) as un join Person p on un.wert = p.XObjectKey join QER_VPersonsAreMe he on p.UID_Person = he.UID_PersonOrigin65  ) as weg on weg.UID_Person in (ah.uid_personHead , ah.UID_PersonSubstituteSender) or (isnull(ah.UID_PersonAdditional , '') = weg.UID_Person and ah.RulerLevel66 < 2 ) or (isnull(ah.UID_PersonInsteadOf , '') = weg.UID_Person and ah.RulerLevel < 2 ) end  select top 1 @countApproverAvailable = COUNT(*) from @AttestationHelper_New67 n where n.RulerLevel = 0 and n.uid_QERWorkingStep = @UID_QERWorkingStep and n.levelnumber = @LevelNumber and n.Sublevelnumber = @SubLevelNumber  if @countApproverAvailable68 >= case when @countApproverNeeded < 0 then @countApproverAvailable when @countApproverNeeded = 0 then 1 else @countApproverNeeded end and @countApproverAvailable69 > 0 begin delete  @AttestationHelper_New from @AttestationHelper_New n where n.RulerLevel = 1 and n.uid_QERWorkingStep = @UID_QERWorkingStep and n.levelnumber70 = @LevelNumber and n.Sublevelnumber = @SubLevelNumber end    delete  @AttestationHelper_New from @AttestationHelper_New n where n.RulerLevel = 0 and n.uid_QERWorkingStep71 = @UID_QERWorkingStep and n.levelnumber = @LevelNumber and n.Sublevelnumber = @SubLevelNumber and exists (select top 1 1 from @AttestationHelper_New n172 where n1.RulerLevel = 1 and n1.uid_QERWorkingStep = @UID_QERWorkingStep and n1.levelnumber = @LevelNumber and n1.Sublevelnumber = @SubLevelNumber and 73n1.uid_personHead = n.uid_personHead )   if @UID_PwoDecisionRule in (select r.UID_PWODecisionRule from PWODecisionRule r where r.UID_Task > ' ' ) begin74 delete  @AttestationHelper_New from @AttestationHelper_New n where n.RulerLevel > 0 and n.uid_QERWorkingStep = @UID_QERWorkingStep and n.levelnumber =75 @LevelNumber and n.Sublevelnumber = @SubLevelNumber end  end select @ElementIndex += 1 end     update @AttestationHelper_Old set isToDelete = 1 where 76uid_personhead is null  and isnull(UID_PWODecisionRule, '') not in (select UID_PWODecisionRule from pwodecisionrule where UID_Task > ' '  )  delete  @AttestationHelper_New77 where uid_personhead is null  and UID_PWODecisionRule not in (select UID_PWODecisionRule from pwodecisionrule where UID_Task > ' '  )   update @AttestationHelper_new78 set UID_PersonAdditional = o.UID_PersonAdditional , UID_PersonInsteadOf = o.UID_PersonInsteadOf from @AttestationHelper_new n join @AttestationHelper_Old79 o on n.UID_PWODecisionRule = o.UID_PWODecisionRule and n.uid_personHead = o.uid_personHead and n.uid_QERWorkingStep = o.uid_QERWorkingStep and n.levelnumber80 = o.levelnumber and n.Sublevelnumber = o.Sublevelnumber where o.UID_PersonAdditional > ' ' or o.UID_PersonInsteadOf > ' '  insert into @AttestationHelper_New81 (UID_PWODecisionRule, uid_personHead, uid_QERWorkingStep, levelnumber, Sublevelnumber, istoInsert , UID_PersonAdditional, UID_PersonInsteadOf, IsFromDelegation82, RulerLevel ) select distinct de.UID_PWODecisionRule, de.UID_PersonAdditional, de.uid_QERWorkingStep, de.levelnumber, 1  , 0, null,null, 1, 0  from @AttestationHelper_New83 de where de.UID_PersonAdditional > ' '  and not exists (select top 1 1 from @AttestationHelper_New n where n.UID_PWODecisionRule = de.UID_PWODecisionRule84 and n.uid_personHead = de.UID_PersonAdditional and n.uid_QERWorkingStep = de.uid_QERWorkingStep and n.levelnumber = de.levelnumber and n.Sublevelnumber85 = 1 )  and exists (select top 1 1 from QERWorkingStep ds where ds.UID_QERWorkingStep = de.uid_QERWorkingStep and ds.IsAdditionalAllowed = 1 )  if @@rowcount86 > 0 begin insert into @AttestationHelper_New (UID_PWODecisionRule, uid_personHead, UID_QERWorkingStep, levelnumber, Sublevelnumber, istoInsert , UID_PersonAdditional87, UID_PersonInsteadOf, IsFromDelegation, RulerLevel, UID_PWORulerOrigin ) select distinct de.UID_PWODecisionRule, esc.UID_Person, de.UID_QERWorkingStep88, de.levelnumber, 1  , 0, null,null, 1, 2 , esc.UID_PWORulerOrigin from @AttestationHelper_New de cross join ( select pio.UID_Person, dbo.QER_FGIPWORulerOrigin89(pio.XObjectKey) as UID_PWORulerOrigin from PersonInBaseTree pio where pio.UID_Org = 'ATT-AEROLE-ATTESTATION-INTERVENTION' and pio.XOrigin > 0 ) as esc90 where de.UID_PersonAdditional > ' '  and not exists (select top 1 1 from @AttestationHelper_New n where n.UID_PWODecisionRule = de.UID_PWODecisionRule91 and n.uid_personHead = esc.UID_Person and n.UID_QERWorkingStep = de.UID_QERWorkingStep and n.levelnumber = de.levelnumber and n.Sublevelnumber = 1 ) end92   insert into @AttestationHelper_New (UID_PWODecisionRule, uid_personHead, uid_QERWorkingStep, levelnumber, Sublevelnumber, istoInsert , UID_PersonAdditional93, UID_PersonInsteadOf, IsFromDelegation, RulerLevel ) select distinct de.UID_PWODecisionRule, de.UID_PersonInsteadOf, de.uid_QERWorkingStep, de.levelnumber94, 0  , 0, null,null, 1, 0  from @AttestationHelper_New de where de.UID_PersonInsteadOf > ' '  and not exists (select top 1 1 from @AttestationHelper_New95 n where n.UID_PWODecisionRule = de.UID_PWODecisionRule and n.uid_personHead = de.UID_PersonInsteadOf and n.uid_QERWorkingStep = de.uid_QERWorkingStep 96and n.levelnumber = de.levelnumber and n.Sublevelnumber = 0 )  and exists (select top 1 1 from QERWorkingStep ds where ds.UID_QERWorkingStep = de.uid_QERWorkingStep97 and ds.IsInsteadOfAllowed = 1 )   update @AttestationHelper_Old set isToDelete = 1 from @AttestationHelper_Old o where (Not exists (select top 1 1 from98 @AttestationHelper_New n where isnull(n.UID_PWODecisionRule, '') = isnull(o.UID_PWODecisionRule, '') and isnull(n.uid_personHead, '') = isnull(o.uid_personHead99, '') and n.levelnumber = o.levelnumber and n.Sublevelnumber = o.Sublevelnumber and isnull(n.uid_QERWorkingStep, '') = isnull(o.uid_QERWorkingStep, '')100 and isnull(n.UID_PWORulerOrigin, '') = isnull(o.UID_PWORulerOrigin, '') and ISNULL(n.rulerlevel, 0) = ISNULL(o.rulerlevel, 0) ) or (  isnull(o.uid_personHead101, '') > ' ' and not exists ( select top 1 1 from Person p where p.UID_Person = isnull(o.uid_personHead, '') and p.IsInActive = 0 ) ) ) and o.decision in102( '', '-')   if exists (select top 1 1 from AttestationCase where UID_AttestationCase = @UID_AttestationCase and IsReserved =1 ) begin if exists (select103 top 1 1 from AttestationCase pwo , @AttestationHelper_Old o where pwo.UID_AttestationCase = @UID_AttestationCase and o.uid_personHead = pwo.UID_PersonHead104  and o.isToDelete = 1 ) begin update AttestationCase set IsReserved = 0  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_AttestationCase = @UID_AttestationCase105 update @AttestationHelper_Old set isToDelete = 1 where UID_PWODecisionRule = 'ATT-PWODecisionRule-QP' or decision = 'Q'  select @UID_AttestationHistory106 = NEWID() exec QBM_PSessionContextSet 'GenProcID', @GenProcID insert into AttestationHistory (DecisionType, IsDecisionBySystem, UID_AttestationHistory107, UID_PersonHead, UID_AttestationCase, DisplayPersonHead, ReasonHead, DateHead, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, DecisionLevel108, XTouched, XObjectKey) select 'RecallQuery', 1, @UID_AttestationHistory, null, @UID_AttestationCase, dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval.|'109) , dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Query to Person finished due to changed deciders.|'), GetUTCDate(), GetUTCDate(), GetUTCDate(), 'sa', 'sa', 0,110 '', dbo.QBM_FCVElementToObjectKey1('AttestationHistory', 'UID_AttestationHistory', @UID_AttestationHistory)  end end   update @AttestationHelper_Old set111 isToDelete = 1 from @AttestationHelper_Old o where Not exists (select top 1 1 from AttestationCase ac where ac.UID_AttestationCase = @UID_AttestationCase112 and isclosed = 0 )  update @AttestationHelper_New set isToInsert = 1 from @AttestationHelper_New n where Not exists (select top 1 1 from @AttestationHelper_Old113 o where o.UID_PWODecisionRule = n.UID_PWODecisionRule and isnull(o.uid_personHead, '') = isnull(n.uid_personHead, '') and o.levelnumber = n.levelnumber114 and o.Sublevelnumber = n.Sublevelnumber and isnull(o.UID_QERWorkingStep, '') = isnull(n.UID_QERWorkingStep, '') and isnull(o.UID_PWORulerOrigin, '') =115 isnull(n.UID_PWORulerOrigin, '') and ISNULL(o.rulerlevel, 0) = ISNULL(n.rulerlevel, 0)  and o.isToDelete = 0  ) publizieren: select @isToInsert = 0 select116 @isToDelete = 0 if exists (select top 1 1 from @AttestationHelper_New where isToInsert = 1 ) begin select @isToInsert = 1 end if exists (select top 1 1171 from @AttestationHelper_Old where isTodelete = 1 ) begin select @isToDelete = 1 end if ( @isToInsert = 1 or @isToDelete = 1 ) and @makeEmpty = 0 begin118 exec QBM_PDBQueueInsert_Single 'ATT-K-AttestationCheckValid', @UID_AttestationCase, null, @GenProcID end  if @isToDelete = 1 begin exec QBM_PSessionContextSet119 'GenProcID', @GenProcID delete  AttestationHelper where UID_AttestationCase = @UID_AttestationCase and exists (select top 1 1 from @AttestationHelper_Old120 o where isnull(o.UID_PWODecisionRule, '') = isnull(AttestationHelper.UID_PWODecisionRule, '') and isnull(o.uid_personHead, '') = isnull(AttestationHelper.uid_personHead121, '') and o.levelnumber = AttestationHelper.levelnumber and o.Sublevelnumber = AttestationHelper.Sublevelnumber and o.Rulerlevel = AttestationHelper.Rulerlevel122 and isnull(o.UID_PWORulerOrigin, '') = isnull(AttestationHelper.UID_PWORulerOrigin, '') and o.isToDelete = 1 ) end  if @isToInsert = 1 begin update @AttestationHelper_New123 set UID_PrimaryKey = newid() where istoinsert = 1  update @AttestationHelper_new set NextAutomaticDecision = isnull(g.MaxDate, '2200-01-01') from @AttestationHelper_new124 b left outer join ( select  a.LevelNumber, a.Sublevelnumber, a.RulerLevel, MAX(a.NextAutomaticDecision) as MaxDate from @AttestationHelper_Old a group125 by  a.LevelNumber, a.Sublevelnumber, a.RulerLevel ) as g on  b.LevelNumber = g.LevelNumber and b.Sublevelnumber = g.Sublevelnumber and b.RulerLevel = 126g.RulerLevel where b.IstoInsert = 1 exec QBM_PSessionContextSet 'GenProcID', @GenProcID insert into AttestationHelper (UID_AttestationHelper, UID_AttestationCase127, UID_PWODecisionRule, uid_personHead, levelnumber, SubLevelNumber, Decision, UID_QERWorkingStep , NextReminder, NextAutomaticDecision, XObjectKey , UID_PersonAdditional128, UID_PersonInsteadOf, IsFromDelegation, RulerLevel , UID_PWORulerOrigin ) select UID_PrimaryKey, @UID_AttestationCase, UID_PWODecisionRule, uid_personHead129 , levelnumber, SubLevelNumber , case when n.UID_PersonInsteadOf > ' ' then 'D' else '' end as Decision , UID_QERWorkingStep , '2200-01-01', n.NextAutomaticDecision130, dbo.QBM_FCVElementToObjectKey1('AttestationHelper', 'UID_AttestationHelper', UID_PrimaryKey) , UID_PersonAdditional, UID_PersonInsteadOf, IsFromDelegation131, n.RulerLevel , UID_PWORulerOrigin from @AttestationHelper_New n where istoinsert = 1 end  if (@isToInsert = 1 or @isToDelete = 1 or @isNewDecisionLevel132 = 1) and @makeEmpty = 0 begin   delete  @ElementBuffer_Notification  insert into @ElementBuffer_Notification (ObjectKey1) select h.XObjectKey from @AttestationHelper_New133 n join AttestationHelper h on n.UID_PrimaryKey = h.uid_AttestationHelper and h.decision = '' join Attestationcase pwo on h.uid_Attestationcase = pwo.uid_Attestationcase134 and h.levelnumber = pwo.decisionlevel join QERWorkingStep ws on h.UID_QERWorkingStep = ws.UID_QERWorkingStep and ws.UID_DialogRichMailInsert > ' ' join135 pwodecisionrule r on ws.UID_PWODecisionRule = r.UID_PWODecisionRule and r.UID_Task is null join person p on h.uid_personhead = p.uid_person and p.defaultemailaddress136 > ' ' where n.istoinsert = 1  and h.RulerLevel < 2 union  select h.XObjectKey from AttestationHelper h join Attestationcase pwo on h.uid_Attestationcase137 = @UID_AttestationCase and pwo.uid_Attestationcase = @UID_AttestationCase and h.levelnumber = pwo.decisionlevel and h.decision = '' join QERWorkingStep138 ws on h.UID_QERWorkingStep = ws.UID_QERWorkingStep and ws.UID_DialogRichMailInsert > ' ' join pwodecisionrule r on ws.UID_PWODecisionRule = r.UID_PWODecisionRule139 and r.UID_Task is null join person p on h.uid_personhead = p.uid_person and p.defaultemailaddress > ' ' where @isNewDecisionLevel = 1  and h.RulerLevel140 < 2 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast141 begin select top 1 @ObjectkeyForMail = bu.ObjectKey1 from @ElementBuffer_Notification bu where bu.ElementIndex = @ElementIndex  select @WhereclauseForMail142 = N'XObjectKey = ''' + @ObjectkeyForMail + N''''  + N' and exists (select top 1 1 from Attestationcase where uid_Attestationcase  = ''' + @uid_Attestationcase143 + ''' and decisionlevel = ' + str(@CurrentDecisionLevel) + ' )'  exec QBM_PJobCreate_HOFireEvent_B 'AttestationHelper', @WhereclauseForMail, 'DecisionRequired'144, @GenProcID , @AdditionalObjectKeysAffected = DEFAULT , @checkForExisting = 1 , @priority = 10 , @BasisObjectKey = @ObjectkeyForMail select @ElementIndex145 += 1 end   end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow146, 18, 1) WITH NOWAIT END CATCH  ende: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R revert return147 end 148