dbo.QER_ZAllForPersonInBaseTree

1CREATE PROCEDURE QER_ZAllForPersonInBaseTree(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @IsCUNode BIT7  DECLARE @IsOrgNode BIT8  SELECT @IsCUNode = 09  SELECT @IsOrgNode = 010  DECLARE @uid_personwantsorg varchar(38)11  DECLARE @SQLCmd nvarchar(2000)12  DECLARE @BasisObjectKey varchar(138)13  DECLARE @GenProcID varchar(38)14  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))15  DECLARE @BitpatternDynInv int = dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',16  1)17  DECLARE @UID_PWOStateFinalError varchar(38)18  DECLARE @Parameter nvarchar(1000)19  DECLARE @Ident_OrgRoot nvarchar(64)20  DECLARE @EntryAll QBM_YCursorBuffer21  DECLARE @ElementLast int22  DECLARE @ElementCount int23  DECLARE @ElementIndex int24  DECLARE @XUser nvarchar(64) = object_name(@@procid)25  DECLARE @Xdate datetime = getutcdate()26  DECLARE @UID_TaskFollow varchar(38)27  BEGIN TRY28    INSERT INTO @EntryAll(UID1,29    UID2,30    ObjectKey1,31    Bit1,32    UID3,33    Int1)34    SELECT35      cu.UID_Parameter,36      cu.UID_SubParameter,37      pio.XObjectKey,38      sign(len(isnull(pio.UID_Person, ''))) ^1,39      cu.GenProcID,40      isnull(pio.XOrigin,41      0)42    FROM QBMDBQueueCurrent cu43      WITH(readpast)44    LEFT45    OUTER46    JOIN PersonInBaseTree pio47      ON cu.UID_Parameter = pio.UID_Person AND cu.UID_SubParameter = pio.UID_Org AND pio.XOrigin > 048    WHERE49      cu.SlotNumber = @SlotNumber50    UPDATE @EntryAll51    SET ObjectKey1 = dbo.QBM_FCVElementToObjectKey2('PersonIn' +dbo.QER_FGIOrgRootName(b.uid_orgroot),52    'uid_person',53    d.UID1,54    'UID_' + dbo.QER_FGIOrgRootName(b.uid_orgroot),55    b.uid_org)56    FROM @EntryAll d57    JOIN BaseTree b58      ON d.UID2 = b.UID_Org59    WHERE60      d.Bit1 = 161    IF EXISTS(62      SELECT TOP 1 163      FROM @EntryAll pio64      JOIN BaseTree b65        ON pio.UID2 = b.UID_Org66      JOIN DynamicGroup dg67        ON dg.ObjectKeyBaseTree = b.XObjectKey68      JOIN QERDynamicGroupBlackList bl69        ON bl.UID_DynamicGroup = dg.UID_DynamicGroup AND bl.UID_Person = pio.UID1)70    BEGIN71      UPDATE QERDynamicGroupBlackList72      SET IsAssignedByOthers = CASE73      WHEN pio.Bit1 = 1 THEN74      075      WHEN pio.Int1 & @BitpatternDynInv > 0 THEN76      177      ELSE bl.IsAssignedByOthers78      END,79      XDateUpdated = @Xdate,80      XUserUpdated = @XUser81      FROM @EntryAll pio82      JOIN BaseTree b83        ON pio.UID2 = b.UID_Org84      JOIN DynamicGroup dg85        ON dg.ObjectKeyBaseTree = b.XObjectKey86      JOIN QERDynamicGroupBlackList bl87        ON bl.UID_DynamicGroup = dg.UID_DynamicGroup AND bl.UID_Person = pio.UID188    END89    IF EXISTS(90      SELECT TOP 1 191      FROM @EntryAll a92      WHERE93        a.UID2 = 'QER-AEROLE-ITSHOP-INTERVENTION')94    BEGIN95      SELECT TOP 1 @GenProcID = a.UID396      FROM @EntryAll a97      WHERE98        a.UID2 = 'QER-AEROLE-ITSHOP-INTERVENTION'99      EXEC QBM_PDBQueueInsert_Single 'QBM-K-Commonrecalculate',100      'QER-K-ShoppingRackPWOHelperPWO',101      '',102        @GenprocID103    END104    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw105    INSERT INTO @DBQueueElements_01(Object,106    SubObject,107    GenProcID)108    SELECT109      DISTINCT h.UID_PersonWantsOrg,110      '',111      cu.UID3112    FROM @EntryAll cu113    JOIN QERWorkingStep s114      ON s.UID_AERoleFallBack = cu.UID2115    JOIN PWOHelperPWO h116      ON h.UID_QERWorkingStep = s.UID_QERWorkingStep117    IF @@ROWCOUNT > 0118    BEGIN119      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO-Cfg',120        @DBQueueElements_01121    END122    IF EXISTS(123      SELECT TOP 1 1124      FROM BaseTree b125      JOIN @EntryAll i126        ON b.uid_org = i.UID2127      WHERE128        b.uid_Orgroot = 'QER-V-ITShopOrg' AND b.itShopInfo = 'CU')129    BEGIN130      SELECT @IsCUNode = 1131    END132    IF EXISTS(133      SELECT TOP 1 1134      FROM BaseTree b135      JOIN @EntryAll i136        ON b.uid_org = i.UID2137      WHERE138        (b.uid_orgroot LIKE '____[^V]%'))139    BEGIN140      SELECT @IsOrgNode = 1141    END142    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw143    INSERT INTO @DBQueueElements_02(object,144    subobject,145    genprocid)146    SELECT147      x.uid,148      NULL,149      x.GenProcID150    FROM(151    SELECT152      DISTINCT d.UID1 AS uid,153      d.UID3 AS GenProcID154    FROM @EntryAll d155    JOIN BaseTree b156      ON d.UID2 = b.uid_org157    WHERE158      b.uid_orgroot = 'QER-V-AERole') AS x159    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-PersonCheckDynUser',160      @DBQueueElements_02161    DECLARE @ElementClosing QBM_YCursorBuffer162    INSERT INTO @ElementClosing(UID1,163    ObjectKey1,164    UID2)165    SELECT166      pwo.uid_personwantsorg,167      pwo.XObjectKey,168      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,169      x.UID3,170      @CfgUseGenProcID) AS GenProcID171    FROM @EntryAll x172    JOIN Delegation d173      ON d.ObjectKeyDelegated = x.ObjectKey1 AND x.Bit1 = 1174    JOIN personwantsorg pwo175      ON d.uid_personwantsorg = pwo.uid_personwantsorg176    WHERE177      pwo.OrderState IN('Assigned',178    'Granted',179    'New',180    'OrderProduct',181    'OrderProlongate',182    'OrderUnsubscribe',183    'Waiting')184    SELECT @ElementCount = @@ROWCOUNT185    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1186    SELECT @ElementLast = @@IDENTITY187    WHILE @ElementIndex <= @ElementLast188    BEGIN189      SELECT190        TOP 1 @uid_personwantsorg = bu.UID1,191        @BasisObjectKey = bu.ObjectKey1,192        @GenProcID = bu.UID2193      FROM @ElementClosing bu194      WHERE195        bu.ElementIndex = @ElementIndex196      SELECT @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''''197      SELECT @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg)198      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',199        @whereclause = @SQLcmd,200        @save = 1,201        @MethodName = 'Abort',202        @GenProcID = @GenprocID,203        @ObjectKeysAffected = DEFAULT,204        @param1 = '#LDS#Closing delegation because its source was was deleted.|',205        @Param2 = @UID_PWOStateFinalError,206        @Retries = 3,207        @isToFreezeOnError = 1,208        @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'209      ,210      @BasisObjectKey = @BasisObjectKey,211      @checkForExisting = 1212      SELECT @ElementIndex += 1213    END214    DECLARE @DBQueueElements_03 QBM_YDBQueueRaw215    INSERT INTO @DBQueueElements_03(object,216    subobject,217    genprocid)218    SELECT219      x.uid,220      NULL,221      x.GenProcID222    FROM(223    SELECT224      DISTINCT d.UID1 AS uid,225      d.UID3 AS GenProcID226    FROM @EntryAll d227    LEFT228    OUTER229    JOIN BaseTree b230      ON d.UID2 = b.uid_org231    WHERE232      isnull(b.uid_orgroot, '') <> 'CPL-V-NonCompliance' AND isnull(b.IsNoInheriteToPerson, 0) = 0) AS x233    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson',234      @DBQueueElements_03235    DECLARE @DBQueueElements_04 QBM_YDBQueueRaw236    INSERT INTO @DBQueueElements_04(object,237    subobject,238    genprocid)239    SELECT240      x.uid,241      NULL,242      x.GenProcID243    FROM(244    SELECT245      DISTINCT d.UID1 AS uid,246      d.UID3 AS GenProcID247    FROM @EntryAll d) AS x248    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-PersonHasObject',249      @DBQueueElements_04250    IF @IsCUNode = 1251    BEGIN252      DECLARE @DBQueueElements_05 QBM_YDBQueueRaw253      INSERT INTO @DBQueueElements_05(object,254      subobject,255      genprocid)256      SELECT257        x.uid,258        x.subobject,259        x.GenProcID260      FROM(261      SELECT262        DISTINCT pio.UID1 AS uid,263        o.uid_parentorg AS subobject,264        pio.UID3 AS GenProcID265      FROM @EntryAll pio266      JOIN BaseTree o267        ON pio.UID2 = o.uid_Org AND o.ITShopInfo = 'CU' AND o.uid_orgroot = 'QER-V-ITShopOrg' AND pio.Bit1 = 1) AS x268      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackMemberDel',269        @DBQueueElements_05270    END271    DECLARE @ElementNacharbeit QBM_YCursorBuffer272    DECLARE @ObjectKeyOrg varchar(138)273    INSERT INTO @ElementNacharbeit(Ident1,274    UID3,275    ObjectKey1)276    SELECT277      DISTINCT dbo.QER_FGIOrgRootName(b.UID_OrgRoot),278      p.UID3,279      b.XObjectKey280    FROM @EntryAll p281    JOIN BaseTree b282      ON p.UID2 = b.UID_Org283    SELECT @ElementCount = @@ROWCOUNT284    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1285    SELECT @ElementLast = @@IDENTITY286    WHILE @ElementIndex <= @ElementLast287    BEGIN288      SELECT289        TOP 1 @Ident_OrgRoot = bu.Ident1,290        @GenProcID = bu.UID3,291        @ObjectKeyOrg = bu.ObjectKey1292      FROM @ElementNacharbeit bu293      WHERE294        bu.ElementIndex = @ElementIndex295      DECLARE @RecalcParameter_01 QBM_YCursorBuffer296      INSERT INTO @RecalcParameter_01(UID1,297      ObjectKey1,298      Ident1)299      SELECT300        'QER-PWODecisionRule-OR',301        @ObjectKeyOrg,302        'I'303      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_01,304        @GenprocID305      IF @Ident_OrgRoot = 'AERole'306      BEGIN307        DECLARE @RecalcParameter_02 QBM_YCursorBuffer308        INSERT INTO @RecalcParameter_02(UID1,309        ObjectKey1,310        Ident1)311        SELECT312          'QER-PWODecisionRule-OA',313          @ObjectKeyOrg,314          'I'315        UNION316        SELECT317          'TSB-PWODecisionRule-TO',318          @ObjectKeyOrg,319          'I'320        UNION321        SELECT322          'AOB-PWODecisionRule-BA',323          @ObjectKeyOrg,324          'I'325        UNION326        SELECT327          'AOB-PWODecisionRule-BE',328          @ObjectKeyOrg,329          'I'330        UNION331        SELECT332          'ARS-ITShopRule-KA',333          @ObjectKeyOrg,334          'I'335        UNION336        SELECT337          'ARS-ITShopRule-PA',338          @ObjectKeyOrg,339          'I'340        UNION341        SELECT342          'PAG-PWODecisionRule-PG',343          @ObjectKeyOrg,344          'I'345        UNION346        SELECT347          'QER-PWODecisionRule-DI',348          @ObjectKeyOrg,349          'I'350        UNION351        SELECT352          'QER-PWODecisionRule-DR',353          @ObjectKeyOrg,354          'I'355        UNION356        SELECT357          'QER-PWODecisionRule-ID',358          @ObjectKeyOrg,359          'I'360        UNION361        SELECT362          'QER-PWODecisionRule-IL',363          @ObjectKeyOrg,364          'I'365        UNION366        SELECT367          'QER-PWODecisionRule-IP',368          @ObjectKeyOrg,369          'I'370        UNION371        SELECT372          'QER-PWODecisionRule-PI',373          @ObjectKeyOrg,374          'I'375        UNION376        SELECT377          'QER-PWODecisionRule-PR',378          @ObjectKeyOrg,379          'I'380        UNION381        SELECT382          'QER-PWODecisionRule-RD',383          @ObjectKeyOrg,384          'I'385        UNION386        SELECT387          'QER-PWODecisionRule-RL',388          @ObjectKeyOrg,389          'I'390        UNION391        SELECT392          'QER-PWODecisionRule-RP',393          @ObjectKeyOrg,394          'I'395        UNION396        SELECT397          'QER-PWODecisionRule-OX',398          @ObjectKeyOrg,399          'I'400        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_02,401          @GenprocID402      END403      IF @Ident_OrgRoot = 'Department'404      BEGIN405        DECLARE @RecalcParameter_03 QBM_YCursorBuffer406        INSERT INTO @RecalcParameter_03(UID1,407        ObjectKey1,408        Ident1)409        SELECT410          'QER-PWODecisionRule-DI',411          @ObjectKeyOrg,412          'I'413        UNION414        SELECT415          'QER-PWODecisionRule-DR',416          @ObjectKeyOrg,417          'I'418        UNION419        SELECT420          'QER-PWODecisionRule-ID',421          @ObjectKeyOrg,422          'I'423        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_03,424          @GenprocID425      END426      IF @Ident_OrgRoot = 'ProfitCenter'427      BEGIN428        DECLARE @RecalcParameter_04 QBM_YCursorBuffer429        INSERT INTO @RecalcParameter_04(UID1,430        ObjectKey1,431        Ident1)432        SELECT433          'QER-PWODecisionRule-IP',434          @ObjectKeyOrg,435          'I'436        UNION437        SELECT438          'QER-PWODecisionRule-PI',439          @ObjectKeyOrg,440          'I'441        UNION442        SELECT443          'QER-PWODecisionRule-PR',444          @ObjectKeyOrg,445          'I'446        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_04,447          @GenprocID448      END449      IF @Ident_OrgRoot = 'Locality'450      BEGIN451        DECLARE @RecalcParameter_05 QBM_YCursorBuffer452        INSERT INTO @RecalcParameter_05(UID1,453        ObjectKey1,454        Ident1)455        SELECT456          'QER-PWODecisionRule-IL',457          @ObjectKeyOrg,458          'I'459        EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_05,460          @GenprocID461      END462      SELECT @ElementIndex += 1463    END464    IF @IsOrgNode = 1465    BEGIN466      IF EXISTS(467        SELECT TOP 1 1468        FROM @EntryAll p469        JOIN BaseTree b470          ON p.UID2 = b.UID_Org471        JOIN pwodecisionstep s472          ON b.XObjectKey = s.ObjectKeyOfAssignedOrg)473      BEGIN474        EXEC QBM_PDBQueueInsert_Single 'QER-K-ShoppingRackPWODecisionMaker',475        '',476        '',477          @GenprocID478      END479    END480    DECLARE @DBQueueElements_06 QBM_YDBQueueRaw481    INSERT INTO @DBQueueElements_06(object,482    subobject,483    genprocid)484    SELECT485      x.uid,486      x.subobject,487      x.GenProcID488    FROM(489    SELECT490      DISTINCT pio.UID1 AS uid,491      NULL AS subobject,492      pio.UID3 AS GenProcID493    FROM @EntryAll pio494    WHERE495      EXISTS(496    SELECT TOP 1 1497    FROM BaseTree o498    WHERE499      o.UID_AERoleManager = pio.UID2)) AS x500    SELECT @UID_TaskFollow = 'QER-K-HelperHeadOrg'501    EXEC QBM_PDBQueueInsert_Bulk @UID_TaskFollow,502      @DBQueueElements_06503  END TRY504  BEGIN CATCH505    EXEC QBM_PSessionErrorAdd DEFAULT506    RAISERROR('',507    18,508    1)509      WITH NOWAIT510  END CATCH511END