Back to OIM Explorer

dbo.QER_PITShopPersonHasObjFill_b

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 11.862 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVElementToObjectKey1 source text reference
references source dbo.QBM_FCVObjectkeyToElement source text reference
references source dbo.QBM_FGIModuleExists source text reference
references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PExecuteSQLMulti source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QER_PITShopPersonHasObjFill_b(2  @PWOs QBM_YParameterlist READONLY,3  @IsCalledFromTrigger BIT = 04)5  WITH6EXECUTE7AS8'dbo' AS9BEGIN10  DECLARE @PWOToCheck QER_YPWOBufferForPHO11  DECLARE @HelperPWOPersonHasObject QER_YPWOHelperObject12  DECLARE @SQLCmd nvarchar(max)13  SET XACT_ABORT OFF14  BEGIN TRY15    IF dbo.QBM_FGIModuleExists('CPL') = 016    BEGIN17      GOTO ende18    END19    IF NOT EXISTS(20      SELECT TOP 1 121      FROM ComplianceRule c22      WITH(nolock)23    WHERE24      c.IsWorkingCopy = 0)25    BEGIN26      GOTO ende27    END28    INSERT INTO @PWOToCheck(UID_PersonWantsOrg,29    ObjectkeyOrdered,30    ObjectKeyAssignment,31    UID_OrgPR,32    UID_PersonOrdered,33    Orderstate,34    IsAssignment,35    IsReusePossible,36    GenProcID)37    SELECT38      po.Parameter1 AS UID_PersonWantsOrg,39      pwo.ObjectkeyOrdered,40      pwo.ObjectKeyAssignment,41      isnull(pwo.UID_ITShopOrgFinal,42      pwo.uid_Org),43      pwo.uid_Personordered,44      pwo.Orderstate,45      sign(len(isnull(r.UID_QERAssign, ''))),46      ba.IsReusePossible,47      po.Parameter248    FROM @PWOs po49    JOIN personwantsorg pwo50      ON po.Parameter1 = pwo.UID_PersonWantsOrg51    JOIN BaseTree b52      ON isnull(pwo.UID_ITShopOrgFinal,53    pwo.uid_Org) = b.uid_org54    JOIN BaseTreeAssign ba55      ON dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',56    pwo.ObjectkeyOrdered) = ba.UID_DialogTableElement57    LEFT58    OUTER59    JOIN QERAssign r60      ON pwo.ObjectkeyOrdered = r.XObjectKey61    UPDATE @PWOToCheck62    SET isToCalculate = 063    FROM @PWOToCheck c64    WHERE65      c.OrderState IN('Aborted',66    'Assigned',67    'Dismissed',68    'Granted',69    'New',70    'Unsubscribed',71    'Waiting') OR c.ObjectkeyOrdered IS NULL OR(c.IsAssignment = 1 AND c.ObjectKeyAssignment IS NULL)72    UPDATE @PWOToCheck73    SET CountNodes = convert(int,74    dbo.QBM_FCVObjectkeyToElement('CountNodes', CASE c.IsAssignment75    WHEN 1 THEN76    c.ObjectKeyAssignment77    ELSE c.ObjectkeyOrdered78    END))79    FROM @PWOToCheck c80    WHERE81      c.isToCalculate = 182    UPDATE @PWOToCheck83    SET isToCalculate = 084    FROM @PWOToCheck c85    WHERE86      c.isToCalculate = 1 AND((c.IsAssignment = 1 AND c.CountNodes <> 2 AND dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',87    c.ObjectKeyAssignment) <> 'QER-T-ESetHasEntitlement') OR(c.IsAssignment = 1 AND c.CountNodes <> 1 AND dbo.QBM_FCVObjectkeyToElement('UID_DialogTable'88    , c.ObjectKeyAssignment) = 'QER-T-ESetHasEntitlement') OR(c.IsAssignment = 0 AND c.CountNodes <> 1))89    IF @@ROWCOUNT > 090    BEGIN91      IF @IsCalledFromTrigger = 192      BEGIN93        SET XACT_ABORT94          ON95        RAISERROR('#LDS#ObjectkeyOrdered / ObjectkeyAssignment has an invalid number of PK definitions.|',96        18,97        1)98          WITH NOWAIT99      END100    END101    IF EXISTS(102      SELECT TOP 1 1103      FROM @PWOToCheck c104      WHERE105        c.CountNodes = 1 AND c.IsAssignment = 0)106    BEGIN107      INSERT INTO @HelperPWOPersonHasObject(uid_person,108      Objectkey,109      uid_OrgRelated,110      isExisting,111      UID_PersonWantsOrg)112      SELECT113        DISTINCT c.UID_PersonOrdered,114        bho.ObjectKey,115        c.UID_OrgPR,116        0,117        c.UID_PersonWantsOrg118      FROM @PWOToCheck c119      JOIN BaseTreehasObject bho120        WITH(nolock)121        ON bho.UID_Org = c.UID_OrgPR122      WHERE123        c.IsAssignment = 0 AND c.IsReusePossible = 0 AND c.CountNodes = 1 AND c.isToCalculate = 1124    END125    IF EXISTS(126      SELECT TOP 1 1127      FROM @PWOToCheck c128      WHERE129        c.CountNodes = 2 OR c.IsAssignment = 1)130    BEGIN131      UPDATE @PWOToCheck132      SET OrderTableName = dbo.QBM_FCVObjectkeyToElement('TableName',133      c.ObjectKeyAssignment),134      OrderPKValue1 = dbo.QBM_FCVObjectkeyToElement('ColumnValue1',135      c.ObjectKeyAssignment),136      OrderPKValue2 = dbo.QBM_FCVObjectkeyToElement('ColumnValue2',137      c.ObjectKeyAssignment)138      FROM @PWOToCheck c139      WHERE140        c.isToCalculate = 1 AND c.CountNodes = 2141      UPDATE @PWOToCheck142      SET isAssignToBaseTree = ao.isAssignToBaseTree,143      isAssignPersonToBaseTree = ao.isAssignPersonToBaseTree,144      isAssignFK = ao.isAssignFK,145      AssignedTableName = ao.AssignedTable146      FROM @PWOToCheck c147      JOIN QER_VITShopOrderAssignment ao148        ON ao.TableName = c.OrderTableName149      WHERE150        c.isToCalculate = 1 AND c.CountNodes = 2151      UPDATE @PWOToCheck152      SET OrderPKName1 = dbo.QBM_FCVObjectkeyToElement('ColumnName1',153      c.ObjectKeyAssignment),154      OrderPKName1Base = dbo.QBM_FCVObjectkeyToElement('ColumnNameBase1',155      c.ObjectKeyAssignment)156      FROM @PWOToCheck c157      WHERE158        c.isToCalculate = 1 AND c.CountNodes = 2159      IF EXISTS(160        SELECT TOP 1 1161        FROM @PWOToCheck c162        WHERE163          c.isAssignToBaseTree = 1)164      BEGIN165        UPDATE @PWOToCheck166        SET AssignedObjectkey = dbo.QBM_FCVElementToObjectKey1(c.AssignedTableName,167        '',168        CASE c.OrderPKName1Base169          WHEN 'UID_Org' THEN170        c.OrderPKValue2171        ELSE c.OrderPKValue1172        END),173        OrderPKValueOrg = CASE c.OrderPKName1Base174        WHEN 'UID_Org' THEN175        c.OrderPKValue1176        ELSE c.OrderPKValue2177        END178        FROM @PWOToCheck c179        WHERE180          c.isToCalculate = 1 AND c.CountNodes = 2 AND c.isAssignToBaseTree = 1181        INSERT INTO @HelperPWOPersonHasObject(uid_person,182        Objectkey,183        uid_OrgRelated,184        isExisting,185        UID_PersonWantsOrg)186        SELECT187          x.uid_person,188          x.AssignedObjectkey,189          x.OrderPKValueOrg,190          0,191          x.UID_PersonWantsOrg192        FROM(193        SELECT194          pio.uid_Person,195          c.AssignedObjectkey,196          c.OrderPKValueOrg,197          c.UID_PersonWantsOrg198        FROM BaseTreecollection co199        JOIN personinBaseTree pio200          ON co.uid_Org = pio.uid_org201        JOIN @PWOToCheck c202          ON co.uid_Parentorg = c.OrderPKValueOrg203        WHERE204          c.isToCalculate = 1 AND c.CountNodes = 2 AND c.isAssignToBaseTree = 1 AND pio.XOrigin > 0205        UNION206        SELECT207          pio.uid_Person,208          c.AssignedObjectkey,209          c.OrderPKValueOrg,210          c.UID_PersonWantsOrg211        FROM BaseTreecollection co212        JOIN helperpersonorg pio213          ON co.uid_Org = pio.uid_org214        JOIN @PWOToCheck c215          ON co.uid_Parentorg = c.OrderPKValueOrg216        WHERE217          c.isToCalculate = 1 AND c.CountNodes = 2 AND c.isAssignToBaseTree = 1) AS x218      END219      IF EXISTS(220        SELECT TOP 1 1221        FROM @PWOToCheck c222        WHERE223          c.isAssignPersonToBaseTree = 1)224      BEGIN225        UPDATE @PWOToCheck226        SET OrderPKValuePerson = CASE c.OrderPKName1227        WHEN 'UID_Person' THEN228        c.OrderPKValue1229        ELSE c.OrderPKValue2230        END,231        OrderPKValueOrg = CASE c.OrderPKName1232        WHEN 'UID_Person' THEN233        c.OrderPKValue2234        ELSE c.OrderPKValue1235        END236        FROM @PWOToCheck c237        WHERE238          c.isToCalculate = 1 AND c.CountNodes = 2 AND c.isAssignPersonToBaseTree = 1239        INSERT INTO @HelperPWOPersonHasObject(uid_person,240        Objectkey,241        uid_OrgRelated,242        isExisting,243        UID_PersonWantsOrg)244        SELECT245          c.OrderPKValuePerson,246          bho.objectkey,247          c.OrderPKValueOrg,248          0,249          c.UID_PersonWantsOrg250        FROM BaseTreehasObject bho251          WITH(nolock)252        JOIN @PWOToCheck c253          ON bho.uid_org = c.OrderPKValueOrg254        WHERE255          c.isToCalculate = 1 AND c.CountNodes = 2 AND c.isAssignPersonToBaseTree = 1256        UNION257        SELECT258          c.OrderPKValuePerson,259          b.XObjectKey,260          c.OrderPKValueOrg,261          0,262          c.UID_PersonWantsOrg263        FROM BaseTree b264        JOIN @PWOToCheck c265          ON b.uid_org = c.OrderPKValueOrg AND c.isToCalculate = 1266        WHERE267          c.isToCalculate = 1 AND c.CountNodes = 2 AND c.isAssignPersonToBaseTree = 1268      END269      IF EXISTS(270        SELECT TOP 1 1271        FROM @PWOToCheck c272        WHERE273          c.IsAssignment = 1 AND dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', c.ObjectKeyAssignment) = 'QER-T-ESetHasEntitlement' AND c.isToCalculate274      = 1)275      BEGIN276        UPDATE @PWOToCheck277        SET OrderTableName = dbo.QBM_FCVObjectkeyToElement('TableName',278        pwo.ObjectKeyElementUsedInAssign),279        AssignedObjectkey = pwo.ObjectKeyElementUsedInAssign,280        OrderPKValue1 = dbo.QBM_FCVObjectkeyToElement('ColumnValue1',281        pwo.ObjectKeyOrgUsedInAssign)282        FROM @PWOToCheck c283        JOIN personwantsorg pwo284          ON c.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg285        WHERE286          c.isToCalculate = 1 AND dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',287        c.ObjectKeyAssignment) = 'QER-T-ESetHasEntitlement' AND c.IsAssignment = 1288        INSERT INTO @HelperPWOPersonHasObject(uid_person,289        Objectkey,290        uid_OrgRelated,291        isExisting,292        UID_PersonWantsOrg)293        SELECT294          x.uid_person,295          x.AssignedObjectkey,296          x.UID_OrgPR,297          0,298          x.UID_PersonWantsOrg299        FROM(300        SELECT301          phe.uid_Person,302          c.AssignedObjectkey,303          c.UID_OrgPR,304          c.UID_PersonWantsOrg305        FROM PersonHasEset phe306        JOIN @PWOToCheck c307          ON phe.uid_ESet = c.OrderPKValue1308        WHERE309          c.isToCalculate = 1 AND c.IsAssignment = 1 AND phe.XOrigin > 0) AS x310      END311    END312    Delta:313    IF EXISTS(314      SELECT TOP 1 1315      FROM @HelperPWOPersonHasObject h316      WHERE317        h.Objectkey LIKE '<Key><T>ESet</T>%')318    BEGIN319      INSERT INTO @HelperPWOPersonHasObject(uid_person,320      Objectkey,321      uid_OrgRelated,322      isExisting,323      IsFromSubIdentityOnly,324      UID_PersonWantsOrg)325      SELECT326        DISTINCT h.UID_Person,327        ehe.Entitlement,328        h.UID_OrgRelated,329        h.IsExisting,330        h.IsFromSubIdentityOnly,331        h.UID_PersonWantsOrg332      FROM @HelperPWOPersonHasObject h333      JOIN ESet e334        ON h.Objectkey = e.XObjectKey335      JOIN ESetHasEntitlement ehe336        ON ehe.UID_ESet = e.UID_ESet337      WHERE338        NOT EXISTS(339      SELECT TOP 1 1340      FROM @HelperPWOPersonHasObject i341      WHERE342        i.UID_Person = h.UID_Person AND i.Objectkey = ehe.Entitlement AND i.UID_PersonWantsOrg = h.UID_PersonWantsOrg)343    END344    INSERT INTO @HelperPWOPersonHasObject(uid_person,345    Objectkey,346    uid_OrgRelated,347    isExisting,348    IsFromSubIdentityOnly,349    UID_PersonWantsOrg)350    SELECT351      DISTINCT m.uid_person,352      ho.Objectkey,353      ho.uid_OrgRelated,354      0,355      1,356      ho.UID_PersonWantsOrg357    FROM @HelperPWOPersonHasObject ho358    JOIN person p359      ON ho.uid_person = p.uid_person360    JOIN person m361      ON p.uid_personmasteridentity = m.uid_Person362    WHERE363      NOT EXISTS(364    SELECT TOP 1 1365    FROM @HelperPWOPersonHasObject e366    WHERE367      e.uid_person = m.UID_Person AND e.Objectkey = ho.Objectkey AND isnull(e.UID_OrgRelated, '') = isnull(ho.uid_OrgRelated,368    '') AND e.UID_PersonWantsOrg = ho.UID_PersonWantsOrg)369    UPDATE @HelperPWOPersonHasObject370    SET IsExisting = 1371    FROM @HelperPWOPersonHasObject h372    WHERE373      EXISTS(374    SELECT TOP 1 1375    FROM personhasobject pho376      WITH(nolock)377    WHERE378      pho.uid_person = h.uid_person AND pho.Objectkey = h.objectkey)379    DELETE HelperPWOPersonHasObject380    FROM HelperPWOPersonHasObject hpo381    JOIN @PWOToCheck c382      ON hpo.UID_PersonWantsOrg = c.UID_PersonWantsOrg383    WHERE384      NOT EXISTS(385    SELECT TOP 1 1386    FROM @HelperPWOPersonHasObject pho387    WHERE388      pho.uid_person = hpo.UID_Person AND isnull(pho.Objectkey, '') = hpo.Objectkey AND pho.UID_PersonWantsOrg = hpo.UID_PersonWantsOrg)389    INSERT INTO HelperPWOPersonHasObject(UID_HelperPWOPersonHasObject,390    uid_Personwantsorg,391    uid_person,392    Objectkey,393    uid_OrgRelated,394    isExisting,395    IsFromSubIdentityOnly)396    SELECT397      newid(),398      w.UID_PersonWantsOrg,399      w.uid_person,400      w.Objectkey,401      w.uid_OrgRelated,402      isExisting,403      IsFromSubIdentityOnly404    FROM @HelperPWOPersonHasObject w405    JOIN @PWOToCheck c406      ON w.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.isToCalculate = 1407    WHERE408      NOT EXISTS(409    SELECT TOP 1 1410    FROM HelperPWOPersonHasObject pho411      WITH(nolock)412    WHERE413      pho.uid_personwantsorg = w.UID_PersonWantsOrg AND pho.uid_person = w.uid_person AND pho.Objectkey = w.Objectkey)414    DECLARE @Helper TABLE(UID_HelperPWOPersonHasObject varchar(38) collate database_default,415    IsExisting BIT DEFAULT 0,416    IsFromSubIdentity BIT DEFAULT 0)417    INSERT INTO @Helper(UID_HelperPWOPersonHasObject,418    IsExisting,419    IsFromSubIdentity)420    SELECT421      pho.UID_HelperPWOPersonHasObject,422      h.IsExisting,423      h.IsFromSubIdentityOnly424    FROM @HelperPWOPersonHasObject h425    JOIN HelperPWOPersonHasObject pho426      ON pho.uid_personwantsorg = h.UID_PersonWantsOrg AND pho.uid_person = h.uid_person AND pho.Objectkey = h.objectkey427    WHERE428      (pho.IsExisting <> h.IsExisting OR pho.IsFromSubIdentityOnly <> h.IsFromSubIdentityOnly)429    IF EXISTS(430      SELECT TOP 1 1431      FROM @Helper)432    BEGIN433      UPDATE HelperPWOPersonHasObject434      SET IsExisting = h.IsExisting,435      IsFromSubIdentityOnly = h.IsFromSubIdentity436      FROM @Helper h437      JOIN HelperPWOPersonHasObject pho438        ON h.UID_HelperPWOPersonHasObject = pho.UID_HelperPWOPersonHasObject439    END440    IF dbo.QBM_FGIModuleExists('SAC') = 1441    BEGIN442      IF EXISTS(443        SELECT TOP 1 1444        FROM sys.procedures p445        WHERE446          p.name = 'SAC_PITShopPersonHasObjectFill')447      BEGIN448        DECLARE @ElementBufferMulti QBM_YCursorBuffer449        INSERT INTO @ElementBufferMulti(UID1,450        ContentFull)451        SELECT452          DISTINCT c.GenProcID,453          CONCAT(' exec SAC_PITShopPersonHasObjectFill ''',454          c.UID_PersonWantsOrg,455          ''', ''',456          c.GenProcID,457          '''')458        FROM HelperPWOPersonHasObject hpo459        JOIN @PWOToCheck c460          ON hpo.UID_PersonWantsOrg = c.UID_PersonWantsOrg AND c.isToCalculate = 1461        WHERE462          hpo.ObjectKey LIKE '<Key><T>SAP%'463        EXEC QBM_PExecuteSQLMulti @ElementBufferMulti,464          @HasDifferentGenprocID = 0,465          @LockTimeout_ms = 500,466          @MaxWaitTimeForLock_s = 7.0467      END468    END469  END TRY470  BEGIN CATCH471    EXEC QBM_PSessionErrorAdd DEFAULT472    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()473    RAISERROR(@Rethrow,474    18,475    1)476      WITH NOWAIT477  END CATCH478  ende: revert479  RETURN480END

Open raw exported source

1   create   procedure QER_PITShopPersonHasObjFill_b (@PWOs QBM_YParameterlist readonly   , @IsCalledFromTrigger bit = 0 ) with execute as 'dbo' 2as begin  declare @PWOToCheck QER_YPWOBufferForPHO declare @HelperPWOPersonHasObject QER_YPWOHelperObject declare @SQLCmd nvarchar(max) SET XACT_ABORT 3OFF BEGIN TRY  if dbo.QBM_FGIModuleExists('CPL') = 0 begin goto ende end if not exists (select top 1 1 from ComplianceRule c with (nolock) where c.IsWorkingCopy4 = 0 ) begin goto ende end insert into @PWOToCheck ( UID_PersonWantsOrg , ObjectkeyOrdered, ObjectKeyAssignment, UID_OrgPR, UID_PersonOrdered, Orderstate5, IsAssignment, IsReusePossible , GenProcID ) select po.Parameter1 as UID_PersonWantsOrg , pwo.ObjectkeyOrdered, pwo.ObjectKeyAssignment, isnull(pwo.UID_ITShopOrgFinal6, pwo.uid_Org), pwo.uid_Personordered, pwo.Orderstate, sign(len(isnull(r.UID_QERAssign, ''))), ba.IsReusePossible , po.Parameter2 from @PWOs po join personwantsorg7 pwo on po.Parameter1 = pwo.UID_PersonWantsOrg join BaseTree b on isnull(pwo.UID_ITShopOrgFinal, pwo.uid_Org) = b.uid_org join BaseTreeAssign ba on dbo.QBM_FCVObjectkeyToElement8('UID_DialogTable', pwo.ObjectkeyOrdered) = ba.UID_DialogTableElement left outer join QERAssign r on pwo.ObjectkeyOrdered = r.XObjectKey          update9 @PWOToCheck set isToCalculate = 0 from @PWOToCheck c    where c.OrderState in ('Aborted', 'Assigned', 'Dismissed', 'Granted', 'New', 'Unsubscribed', 'Waiting'10)  or c.ObjectkeyOrdered is null  or ( c.IsAssignment = 1 and c.ObjectKeyAssignment is null ) update @PWOToCheck set CountNodes = convert(int, dbo.QBM_FCVObjectkeyToElement11('CountNodes', case c.IsAssignment when 1 then c.ObjectKeyAssignment else c.ObjectkeyOrdered end ) ) from @PWOToCheck c where c.isToCalculate = 1 update12 @PWOToCheck set isToCalculate = 0 from @PWOToCheck c where c.isToCalculate = 1 and ( (c.IsAssignment = 1 and c.CountNodes <> 2 and dbo.QBM_FCVObjectkeyToElement13('UID_DialogTable', c.ObjectKeyAssignment) <> 'QER-T-ESetHasEntitlement' ) or (c.IsAssignment = 1 and c.CountNodes <> 1 and dbo.QBM_FCVObjectkeyToElement14('UID_DialogTable', c.ObjectKeyAssignment) = 'QER-T-ESetHasEntitlement' ) or( c.IsAssignment = 0 and c.CountNodes <> 1 ) ) if @@ROWCOUNT > 0 begin if @IsCalledFromTrigger15 = 1 begin set XACT_ABORT ON  RAISERROR ('#LDS#ObjectkeyOrdered / ObjectkeyAssignment has an invalid number of PK definitions.|', 18,1 ) WITH NOWAIT end16 end  if exists (select top 1 1 from @PWOToCheck c where c.CountNodes = 1 and c.IsAssignment = 0 ) begin insert into @HelperPWOPersonHasObject (uid_person17, Objectkey, uid_OrgRelated, isExisting , UID_PersonWantsOrg) select distinct c.UID_PersonOrdered , bho.ObjectKey, c.UID_OrgPR, 0, c.UID_PersonWantsOrg18 from @PWOToCheck c join BaseTreehasObject bho with (nolock) on bho.UID_Org = c.UID_OrgPR where c.IsAssignment = 0 and c.IsReusePossible = 0 and c.CountNodes19 = 1 and c.isToCalculate = 1 end   if exists (select top 1 1 from @PWOToCheck c where c.CountNodes = 2 or c.IsAssignment = 1 ) begin   update @PWOToCheck20 set OrderTableName = dbo.QBM_FCVObjectkeyToElement('TableName', c.ObjectKeyAssignment) , OrderPKValue1 = dbo.QBM_FCVObjectkeyToElement('ColumnValue1',21 c.ObjectKeyAssignment) , OrderPKValue2 = dbo.QBM_FCVObjectkeyToElement('ColumnValue2', c.ObjectKeyAssignment) from @PWOToCheck c where c.isToCalculate22 = 1 and c.CountNodes = 2 update @PWOToCheck set isAssignToBaseTree = ao.isAssignToBaseTree , isAssignPersonToBaseTree = ao.isAssignPersonToBaseTree , 23isAssignFK = ao.isAssignFK , AssignedTableName = ao.AssignedTable from @PWOToCheck c join QER_VITShopOrderAssignment ao on ao.TableName = c.OrderTableName24 where c.isToCalculate = 1 and c.CountNodes = 2  update @PWOToCheck set OrderPKName1 = dbo.QBM_FCVObjectkeyToElement('ColumnName1', c.ObjectKeyAssignment25) , OrderPKName1Base = dbo.QBM_FCVObjectkeyToElement('ColumnNameBase1', c.ObjectKeyAssignment) from @PWOToCheck c where c.isToCalculate = 1 and c.CountNodes26 = 2    if exists (select top 1 1 from @PWOToCheck c where c.isAssignToBaseTree = 1 ) begin update @PWOToCheck set AssignedObjectkey = dbo.QBM_FCVElementToObjectKey127(c.AssignedTableName , '' , case c.OrderPKName1Base when 'UID_Org' then c.OrderPKValue2 else c.OrderPKValue1 end ) , OrderPKValueOrg = case c.OrderPKName1Base28 when 'UID_Org' then c.OrderPKValue1 else c.OrderPKValue2 end from @PWOToCheck c where c.isToCalculate = 1 and c.CountNodes = 2 and c.isAssignToBaseTree29 = 1    insert into @HelperPWOPersonHasObject (uid_person, Objectkey, uid_OrgRelated , isExisting, UID_PersonWantsOrg) select x.uid_person, x.AssignedObjectkey30, x.OrderPKValueOrg, 0, x.UID_PersonWantsOrg from( select pio.uid_Person , c.AssignedObjectkey, c.OrderPKValueOrg, c.UID_PersonWantsOrg from BaseTreecollection31 co join personinBaseTree pio on co.uid_Org = pio.uid_org join @PWOToCheck c on co.uid_Parentorg = c.OrderPKValueOrg where c.isToCalculate = 1 and c.CountNodes32 = 2 and c.isAssignToBaseTree = 1 and pio.XOrigin > 0 union select pio.uid_Person , c.AssignedObjectkey, c.OrderPKValueOrg, c.UID_PersonWantsOrg from BaseTreecollection33 co join helperpersonorg pio on co.uid_Org = pio.uid_org join @PWOToCheck c on co.uid_Parentorg = c.OrderPKValueOrg where c.isToCalculate = 1 and c.CountNodes34 = 2 and c.isAssignToBaseTree = 1 ) as x end        if exists (select top 1 1 from @PWOToCheck c where c.isAssignPersonToBaseTree = 1 ) begin  update @PWOToCheck35 set OrderPKValuePerson = case c.OrderPKName1 when 'UID_Person' then c.OrderPKValue1 else c.OrderPKValue2 end , OrderPKValueOrg = case c.OrderPKName1 when36 'UID_Person' then c.OrderPKValue2 else c.OrderPKValue1 end from @PWOToCheck c where c.isToCalculate = 1 and c.CountNodes = 2 and c.isAssignPersonToBaseTree37 = 1 insert into @HelperPWOPersonHasObject (uid_person, Objectkey, uid_OrgRelated, isExisting, UID_PersonWantsOrg ) select c.OrderPKValuePerson, bho.objectkey38, c.OrderPKValueOrg, 0, c.UID_PersonWantsOrg from BaseTreehasObject bho with (nolock) join @PWOToCheck c on bho.uid_org = c.OrderPKValueOrg where c.isToCalculate39 = 1 and c.CountNodes = 2 and c.isAssignPersonToBaseTree = 1  union select c.OrderPKValuePerson, b.XObjectKey, c.OrderPKValueOrg, 0, c.UID_PersonWantsOrg40 from BaseTree b join @PWOToCheck c on b.uid_org = c.OrderPKValueOrg and c.isToCalculate = 1 where c.isToCalculate = 1 and c.CountNodes = 2 and c.isAssignPersonToBaseTree41 = 1 end        if exists (select top 1 1 from @PWOToCheck c where c.IsAssignment = 1 and dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', c.ObjectKeyAssignment42) = 'QER-T-ESetHasEntitlement' and c.isToCalculate = 1 ) begin update @PWOToCheck set OrderTableName = dbo.QBM_FCVObjectkeyToElement('TableName', pwo.ObjectKeyElementUsedInAssign43)  , AssignedObjectkey = pwo.ObjectKeyElementUsedInAssign  , OrderPKValue1 = dbo.QBM_FCVObjectkeyToElement('ColumnValue1', pwo.ObjectKeyOrgUsedInAssign44)  from @PWOToCheck c join personwantsorg pwo on c.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg where c.isToCalculate = 1 and dbo.QBM_FCVObjectkeyToElement45('UID_DialogTable', c.ObjectKeyAssignment) = 'QER-T-ESetHasEntitlement' and c.IsAssignment = 1  insert into @HelperPWOPersonHasObject (uid_person, Objectkey46, uid_OrgRelated , isExisting, UID_PersonWantsOrg) select x.uid_person, x.AssignedObjectkey, x.UID_OrgPR, 0, x.UID_PersonWantsOrg from( select phe.uid_Person47 , c.AssignedObjectkey, c.UID_OrgPR, c.UID_PersonWantsOrg from PersonHasEset phe join @PWOToCheck c on phe.uid_ESet = c.OrderPKValue1 where c.isToCalculate48 = 1 and c.IsAssignment = 1 and phe.XOrigin > 0 ) as x end     end  Delta: if exists (select top 1 1 from @HelperPWOPersonHasObject h where h.Objectkey49 like '<Key><T>ESet</T>%' ) begin insert into @HelperPWOPersonHasObject(uid_person, Objectkey, uid_OrgRelated, isExisting, IsFromSubIdentityOnly, UID_PersonWantsOrg50) select distinct h.UID_Person, ehe.Entitlement, h.UID_OrgRelated, h.IsExisting, h.IsFromSubIdentityOnly, h.UID_PersonWantsOrg from @HelperPWOPersonHasObject51 h join ESet e on h.Objectkey = e.XObjectKey join ESetHasEntitlement ehe on ehe.UID_ESet = e.UID_ESet where not exists (select top 1 1 from @HelperPWOPersonHasObject52 i where i.UID_Person = h.UID_Person and i.Objectkey = ehe.Entitlement and i.UID_PersonWantsOrg = h.UID_PersonWantsOrg ) end   insert into @HelperPWOPersonHasObject53 (uid_person, Objectkey, uid_OrgRelated, isExisting, IsFromSubIdentityOnly, UID_PersonWantsOrg ) select distinct m.uid_person, ho.Objectkey, ho.uid_OrgRelated54, 0, 1, ho.UID_PersonWantsOrg from @HelperPWOPersonHasObject ho join person p on ho.uid_person = p.uid_person  join person m on p.uid_personmasteridentity55 = m.uid_Person where Not exists (select top 1 1 from @HelperPWOPersonHasObject e where e.uid_person = m.UID_Person and e.Objectkey = ho.Objectkey and 56isnull(e.UID_OrgRelated, '') = isnull(ho.uid_OrgRelated, '') and e.UID_PersonWantsOrg = ho.UID_PersonWantsOrg ) update @HelperPWOPersonHasObject set IsExisting57 = 1 from @HelperPWOPersonHasObject h where exists (select top 1 1 from personhasobject pho with (nolock) where pho.uid_person = h.uid_person and pho.Objectkey58 = h.objectkey )  delete HelperPWOPersonHasObject from HelperPWOPersonHasObject hpo join @PWOToCheck c on hpo.UID_PersonWantsOrg = c.UID_PersonWantsOrg59 where not exists (select top 1 1 from @HelperPWOPersonHasObject pho where pho.uid_person = hpo.UID_Person and isnull(pho.Objectkey, '') = hpo.Objectkey60 and pho.UID_PersonWantsOrg = hpo.UID_PersonWantsOrg )  insert into HelperPWOPersonHasObject (UID_HelperPWOPersonHasObject, uid_Personwantsorg, uid_person61 ,Objectkey, uid_OrgRelated , isExisting, IsFromSubIdentityOnly) select newid(), w.UID_PersonWantsOrg, w.uid_person , w.Objectkey, w.uid_OrgRelated , isExisting62, IsFromSubIdentityOnly from @HelperPWOPersonHasObject w join @PWOToCheck c on w.UID_PersonWantsOrg = c.UID_PersonWantsOrg and c.isToCalculate = 1 where63 not exists (select top 1 1 from HelperPWOPersonHasObject pho with (nolock) where pho.uid_personwantsorg = w.UID_PersonWantsOrg and pho.uid_person = w.uid_person64 and pho.Objectkey = w.Objectkey ) declare @Helper table (UID_HelperPWOPersonHasObject varchar(38) collate database_default , IsExisting bit default 0 65, IsFromSubIdentity bit default 0 ) insert into @Helper (UID_HelperPWOPersonHasObject, IsExisting, IsFromSubIdentity) select pho.UID_HelperPWOPersonHasObject66, h.IsExisting ,h.IsFromSubIdentityOnly from @HelperPWOPersonHasObject h join HelperPWOPersonHasObject pho on pho.uid_personwantsorg = h.UID_PersonWantsOrg67 and pho.uid_person = h.uid_person and pho.Objectkey = h.objectkey where (pho.IsExisting <> h.IsExisting or pho.IsFromSubIdentityOnly <> h.IsFromSubIdentityOnly68 ) if exists (select top 1 1 from @Helper) begin update HelperPWOPersonHasObject set IsExisting = h.IsExisting , IsFromSubIdentityOnly = h.IsFromSubIdentity69 from @Helper h join HelperPWOPersonHasObject pho on h.UID_HelperPWOPersonHasObject = pho.UID_HelperPWOPersonHasObject end if dbo.QBM_FGIModuleExists('SAC'70) = 1 begin if exists (select top 1 1 from sys.procedures p where p.name = 'SAC_PITShopPersonHasObjectFill' ) begin  declare @ElementBufferMulti QBM_YCursorBuffer71 insert into @ElementBufferMulti (UID1, ContentFull) select distinct c.GenProcID, concat(' exec SAC_PITShopPersonHasObjectFill ''' , c.UID_PersonWantsOrg72 , ''', ''' , c.GenProcID , '''') from HelperPWOPersonHasObject hpo join @PWOToCheck c on hpo.UID_PersonWantsOrg = c.UID_PersonWantsOrg and c.isToCalculate73 = 1 where hpo.ObjectKey like '<Key><T>SAP%' exec QBM_PExecuteSQLMulti @ElementBufferMulti , @HasDifferentGenprocID = 0 , @LockTimeout_ms = 500 , @MaxWaitTimeForLock_s74 = 7.0  end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow75, 18, 1) WITH NOWAIT END CATCH ende: revert return end 76