dbo.QER_ZITShopCheckValid

1CREATE PROCEDURE QER_ZITShopCheckValid(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @WhereClause nvarchar(1000)7  DECLARE @uid_personwantsorg varchar(38)8  DECLARE @BasisObjectKey varchar(138)9  DECLARE @ObjectkeyOrdered varchar(138)10  DECLARE @ObjectKeyAssignment varchar(138)11  DECLARE @UID_OrgPR varchar(38)12  DECLARE @ObjectKeyToTest varchar(138)13  DECLARE @IsReusePossible BIT14  DECLARE @IsReusePossibleUS BIT15  DECLARE @IsAssignment BIT16  DECLARE @uid_personOrdered varchar(38)17  DECLARE @uid_PWOConflicted varchar(38)18  DECLARE @Existiert int19  DECLARE @Orderstate nvarchar(32)20  DECLARE @GenProcID varchar(38)21  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))22  DECLARE @ExceededValidUntilUnsubscribe BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\ExceededValidUntilUnsubscribe'))23  DECLARE @UID_PWOStateFinalError varchar(38)24  DECLARE @ElementLast int25  DECLARE @ElementCount int26  DECLARE @ElementIndex int27  DECLARE @PersonOrdered QBM_YParameterlist28  BEGIN TRY29    INSERT INTO @PersonOrdered(Parameter1,30    Parameter2)31    SELECT32      DISTINCT cu.UID_Parameter,33      cu.GenProcID34    FROM QBMDBQueueCurrent cu35      WITH(readpast)36    WHERE37      cu.SlotNumber = @SlotNumber38    DECLARE @ElementGranted QBM_YCursorBuffer39    INSERT INTO @ElementGranted(UID1,40    UID2,41    ObjectKey1,42    ObjectKey2,43    ObjectKey3,44    Ident1,45    Ident2,46    Ident3,47    UID3,48    UID4)49    SELECT50      pwo.uid_PersonWantsOrg,51      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,52      cu.Parameter2,53      @CfgUseGenProcID) AS GenProcID,54      pwo.XObjectKey,55      pwo.ObjectkeyOrdered,56      pwo.ObjectKeyAssignment,57      dbo.QBM_FCVBitToString(ba.IsReusePossible),58      dbo.QBM_FCVBitToString(ba.IsReusePossibleUS),59      dbo.QBM_FCVBitToString(sign(len(isnull(r.UID_QERAssign, '')))) AS IsAssignment,60      pwo.uid_PersonOrdered,61      pwo.uid_Org62    FROM personwantsorg pwo63    JOIN @PersonOrdered cu64      ON pwo.UID_PersonOrdered = cu.Parameter165    JOIN BaseTreeAssign ba66      ON dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',67    pwo.objectkeyordered) = ba.UID_DialogTableElement68    LEFT69    OUTER70    JOIN QERAssign r71      ON pwo.ObjectkeyOrdered = r.XObjectKey72    WHERE73      pwo.OrderState = 'GRANTED' AND(ba.IsReusePossible = 1 OR(ba.IsReusePossible = 0 AND pwo.UID_PersonWantsOrg IN(74    SELECT MAX(pw.uid_personwantsorg) AS uid_personwantsorg75    FROM PersonWantsOrg pw76    JOIN @PersonOrdered cu77      ON pwo.UID_PersonOrdered = cu.Parameter178    WHERE79      pw.OrderState = 'GRANTED' AND isnull(pw.validFrom, '1899-12-30') < getUTCDate() AND isnull(pw.validUntil,80    '2200-01-01') > getUTCDate()81    GROUP BY pw.UID_Org, pw.UID_PersonOrdered))) AND isnull(validFrom,82    '1899-12-30') < getUTCDate() AND isnull(validUntil,83    '2200-01-01') > getUTCDate() AND dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 084    SELECT @ElementCount = @@ROWCOUNT85    SELECT @ElementIndex = @@IDENTITY - @ElementCount +186    SELECT @ElementLast = @@IDENTITY87    WHILE @ElementIndex <= @ElementLast88    BEGIN89      SELECT90        TOP 1 @uid_personwantsorg = bu.UID1,91        @GenProcID = bu.UID2,92        @BasisObjectKey = bu.ObjectKey1,93        @ObjectkeyOrdered = bu.ObjectKey2,94        @ObjectKeyAssignment = bu.ObjectKey3,95        @IsReusePossible = dbo.QBM_FCVStringToBit(bu.Ident1),96        @IsReusePossibleUS = dbo.QBM_FCVStringToBit(bu.Ident2),97        @IsAssignment = dbo.QBM_FCVStringToBit(bu.Ident3),98        @uid_PersonOrdered = bu.UID3,99        @uid_OrgPR = bu.UID4100      FROM @ElementGranted bu101      WHERE102        bu.ElementIndex = @ElementIndex103      SELECT @ObjectkeyToTest = ''104      SELECT @Existiert = 0105      SELECT @uid_PwoConflicted = NULL106      IF @IsReusePossible = 0107      BEGIN108        SELECT109          @ObjectkeyToTest = dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg',110          'uid_Person',111          @uid_PersonOrdered,112          'UID_ITShopOrg',113          @uid_OrgPR)114        EXEC @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest115        IF @Existiert = 1116        BEGIN117          SELECT TOP 1 @uid_PwoConflicted = pwo.uid_personwantsorg118          FROM personwantsorg pwo119          JOIN personInBaseTree pio120            ON pwo.uid_org = pio.uid_org AND pio.XObjectKey = @ObjectkeyToTest AND pio.XOrigin > 0121          WHERE122            isnull(pwo.validUntil,123          '2200-01-01') > getUTCDate() AND pwo.UID_PersonWantsOrg <> @uid_personwantsorg124          ORDER BY pwo.XDateinserted125        END126      END127      ELSE128      BEGIN129        IF @IsAssignment = 1130        BEGIN131          SELECT @ObjectkeyToTest = @ObjectKeyAssignment132          EXEC @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest133          IF @Existiert = 1134          BEGIN135            SELECT TOP 1 @uid_PwoConflicted = pwo.uid_personwantsorg136            FROM personwantsorg pwo137            WHERE138              ObjectKeyAssignment = @ObjectKeyAssignment AND OrderState IN('Assigned',139            'OrderProlongate',140            'OrderUnsubscribe') AND isnull(pwo.validUntil,141            '2200-01-01') > getUTCDate() AND pwo.UID_PersonWantsOrg <> @uid_personwantsorg142            ORDER BY pwo.XDateinserted143          END144        END145      END146      SELECT @WhereClause = 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''147      IF @existiert = 1148      BEGIN149        IF @uid_PwoConflicted IS NULL150        BEGIN151          SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)152          EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',153            @whereclause = @WhereClause,154            @save = 1,155            @MethodName = 'Abort',156            @GenProcID = @GenprocID,157            @ObjectKeysAffected = DEFAULT,158            @param1 = '#LDS#Request has been closed because the requested assignment already exists as direct.|',159            @param2 = @UID_PWOStateFinalError,160            @Retries = 3,161            @isToFreezeOnError = 1,162            @BasisObjectKey = @BasisObjectKey,163            @checkForExisting = 1,164            @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'165        END166        ELSE167        BEGIN168          EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',169            @whereclause = @WhereClause,170            @save = 1,171            @MethodName = 'Waiting',172            @GenProcID = @GenprocID,173            @ObjectKeysAffected = DEFAULT,174            @param1 = @uid_PwoConflicted,175            @param2 = '#LDS#Automatic system approval: Other similar requests have been enabled.|',176            @Retries = 3,177            @isToFreezeOnError = 1,178            @BasisObjectKey = @BasisObjectKey,179            @checkForExisting = 1,180            @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'181        END182      END183      ELSE184      BEGIN185        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',186          @whereclause = @WhereClause,187          @save = 1,188          @MethodName = 'Assign',189          @GenProcID = @GenprocID,190          @ObjectKeysAffected = DEFAULT,191          @param1 = '#LDS#Automatic system approval: ValidFrom time was reached.|',192          @Retries = 3,193          @isToFreezeOnError = 1,194          @BasisObjectKey = @BasisObjectKey,195          @checkForExisting = 1,196          @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'197      END198      SELECT @ElementIndex += 1199    END200    DECLARE @ElementZuAlt QBM_YCursorBuffer201    INSERT INTO @ElementZuAlt(UID1,202    UID2,203    ObjectKey1,204    ObjectKey2,205    ObjectKey3,206    UID3,207    UID4,208    Ident1)209    SELECT210      pwo.uid_PersonWantsOrg,211      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,212      cu.Parameter2,213      @CfgUseGenProcID) AS GenProcID,214      pwo.XObjectKey,215      pwo.ObjectkeyOrdered,216      pwo.ObjectKeyAssignment,217      pwo.uid_PersonOrdered,218      pwo.uid_Org,219      pwo.Orderstate220    FROM personwantsorg pwo221    JOIN @PersonOrdered cu222      ON pwo.UID_PersonOrdered = cu.Parameter1223    WHERE224      pwo.validUntil < getUTCDate() AND pwo.OrderState IN('Assigned',225    'Granted',226    'OrderProlongate',227    'OrderUnsubscribe',228    'Waiting') AND dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0229    SELECT @ElementCount = @@ROWCOUNT230    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1231    SELECT @ElementLast = @@IDENTITY232    WHILE @ElementIndex <= @ElementLast233    BEGIN234      SELECT235        TOP 1 @uid_personwantsorg = bu.UID1,236        @GenProcID = bu.UID2,237        @BasisObjectKey = bu.ObjectKey1,238        @ObjectkeyOrdered = bu.ObjectKey2,239        @ObjectKeyAssignment = bu.ObjectKey3,240        @uid_PersonOrdered = bu.UID3,241        @uid_OrgPR = bu.UID4,242        @Orderstate = bu.Ident1243      FROM @ElementZuAlt bu244      WHERE245        bu.ElementIndex = @ElementIndex246      SELECT @IsAssignment = 0247      IF EXISTS(248        SELECT TOP 1 1249        FROM QERAssign r250        WHERE251          r.XObjectKey = @ObjectkeyOrdered)252      BEGIN253        SELECT @IsAssignment = 1254      END255      SELECT256        TOP 1 @IsReusePossible = ba.IsReusePossible,257        @IsReusePossibleUS = ba.IsReusePossibleUS258      FROM personwantsorg pwo259      JOIN BaseTreeAssign ba260        ON dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',261      pwo.objectkeyordered) = ba.UID_DialogTableElement262      WHERE263        pwo.uid_personwantsorg = @uid_personwantsorg264      SELECT @uid_PwoConflicted = NULL265      IF @OrderState IN('Assigned',266      'OrderProlongate',267      'OrderUnsubscribe')268      BEGIN269        IF @IsReusePossible = 0270        BEGIN271          SELECT TOP 1 @uid_PwoConflicted = pwo.uid_personwantsorg272          FROM personwantsorg pwo273          WHERE274            orderstate = 'waiting' AND uid_personOrdered = @uid_personOrdered AND uid_Org = @uid_OrgPR AND isnull(pwo.validUntil,275          '2200-01-01') > getUTCDate() AND pwo.UID_PersonWantsOrg <> @uid_personwantsorg276          ORDER BY pwo.XDateinserted277        END278        ELSE279        BEGIN280          IF @IsAssignment = 1281          BEGIN282            SELECT TOP 1 @uid_PwoConflicted = pwo.uid_personwantsorg283            FROM personwantsorg pwo284            WHERE285              orderstate = 'waiting' AND ObjectKeyAssignment = @ObjectKeyAssignment AND isnull(pwo.validUntil,286            '2200-01-01') > getUTCDate() AND pwo.UID_PersonWantsOrg <> @uid_personwantsorg287            ORDER BY pwo.XDateinserted288          END289        END290      END291      SELECT @WhereClause = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''292      IF @uid_PwoConflicted IS NULL293      BEGIN294        SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)295        IF @ExceededValidUntilUnsubscribe = 0 OR(@ExceededValidUntilUnsubscribe = 1 AND @Orderstate IN('Waiting',296        'Granted'))297        BEGIN298          EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',299            @whereclause = @WhereClause,300            @save = 1,301            @MethodName = 'Abort',302            @GenProcID = @GenprocID,303            @ObjectKeysAffected = DEFAULT,304            @param1 = '#LDS#Automatic system approval: ValidUntil time period exceeded.|',305            @param2 = @UID_PWOStateFinalError,306            @Retries = 3,307            @isToFreezeOnError = 1,308            @BasisObjectKey = @BasisObjectKey,309            @checkForExisting = 1,310            @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'311        END312        IF @ExceededValidUntilUnsubscribe = 1 AND @Orderstate IN('Assigned',313        'OrderProlongate')314        BEGIN315          EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',316            @whereclause = @WhereClause,317            @save = 1,318            @MethodName = 'Unsubscribe',319            @GenProcID = @GenprocID,320            @ObjectKeysAffected = DEFAULT,321            @param1 = 'sa',322            @param2 = '#LDS#Automatic system approval: ValidUntil time period exceeded.|',323            @param3 = 'QER-VALIDUNTIL-EXCEEDED',324            @Retries = 3,325            @isToFreezeOnError = 1,326            @BasisObjectKey = @BasisObjectKey,327            @checkForExisting = 1,328            @WhereClauseAdditional = ' OrderState in (''Assigned'', ''OrderProlongate'')'329        END330      END331      ELSE332      BEGIN333        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',334          @whereclause = @WhereClause,335          @save = 1,336          @MethodName = 'Replace',337          @GenProcID = @GenprocID,338          @ObjectKeysAffected = DEFAULT,339          @param1 = @uid_PwoConflicted,340          @param2 = '#LDS#Automatic system approval: ValidUntil time period exceeded for the replaced order.|',341          @Retries = 3,342          @isToFreezeOnError = 1,343          @BasisObjectKey = @BasisObjectKey,344          @checkForExisting = 1,345          @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'346      END347      SELECT @ElementIndex += 1348    END349    DECLARE @ElementVorbei QBM_YCursorBuffer350    INSERT INTO @ElementVorbei(UID1,351    UID2,352    ObjectKey1)353    SELECT354      pwo.uid_PersonWantsOrg,355      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,356      cu.Parameter2,357      @CfgUseGenProcID) AS GenProcID,358      pwo.XObjectKey359    FROM personwantsorg pwo360    JOIN @PersonOrdered cu361      ON pwo.UID_PersonOrdered = cu.Parameter1362    WHERE363      pwo.OrderState IN('OrderProduct') AND pwo.validUntil < getUTCDate() AND dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0364    SELECT @ElementCount = @@ROWCOUNT365    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1366    SELECT @ElementLast = @@IDENTITY367    WHILE @ElementIndex <= @ElementLast368    BEGIN369      SELECT370        TOP 1 @uid_personwantsorg = bu.UID1,371        @GenProcID = bu.UID2,372        @BasisObjectKey = bu.ObjectKey1373      FROM @ElementVorbei bu374      WHERE375        bu.ElementIndex = @ElementIndex376      SELECT @WhereClause = 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''377      SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)378      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',379        @whereclause = @WhereClause,380        @save = 1,381        @MethodName = 'Abort',382        @GenProcID = @GenprocID,383        @ObjectKeysAffected = DEFAULT,384        @param1 = '#LDS#Automatic system approval: ValidUntil time period exceeded.|',385        @param2 = @UID_PWOStateFinalError,386        @Retries = 3,387        @isToFreezeOnError = 1,388        @BasisObjectKey = @BasisObjectKey,389        @checkForExisting = 1,390        @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'391      SELECT @ElementIndex += 1392    END393  END TRY394  BEGIN CATCH395    EXEC QBM_PSessionErrorAdd DEFAULT396    RAISERROR('',397    18,398    1)399      WITH NOWAIT400  END CATCH401  ende:402  RETURN403END