Back to OIM Explorer

dbo.QER_PITShopOrderMove_bulk

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. HOCallMethod -> PersonWantsOrg.Abort at line 140; HOCallMethod -> PersonWantsOrg.Abort at line 140; HOCallMethod -> PersonWantsOrg.Abort at line 140; HOCallMethod -> PersonWantsOrg.MakeDecision at line 140

Source: sandbox-db sys.sql_modules

Source size: 24.596 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.

Relations

  • HOCallMethod -> PersonWantsOrg.Abort at line 140
  • HOCallMethod -> PersonWantsOrg.Abort at line 140
  • HOCallMethod -> PersonWantsOrg.Abort at line 140
  • HOCallMethod -> PersonWantsOrg.MakeDecision at line 140
  • HOCallMethod -> PersonWantsOrg.Abort at line 144
  • HOCallMethod -> PersonWantsOrg.Abort at line 144
  • HOCallMethod -> PersonWantsOrg.MakeDecision at line 144
  • HOCallMethod -> PersonWantsOrg.Abort at line 147
  • HOCallMethod -> PersonWantsOrg.MakeDecision at line 147
  • HOCallMethod -> PersonWantsOrg.MakeDecision at line 155
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOCallMethod*

Typed Edges

  • calls object method PersonWantsOrg.Abort HOCallMethod -> PersonWantsOrg.Abort at line 140
  • calls object method PersonWantsOrg.MakeDecision HOCallMethod -> PersonWantsOrg.MakeDecision at line 140
  • references source dbo.QER_FTITShopNewProductNode source text reference
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVElementToObjectKey2 source text reference
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QER_FCVObjectkeyToBaseTree source text reference
  • references source dbo.QER_FGIGenProcIDForPWO source text reference
  • references source dbo.QER_FGIITShopNewPersonOrdered source text reference
  • references source dbo.QBM_PExecuteSQLMulti source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOCallMethod source text reference
  • references source dbo.QBM_PJobCreate_HOCallMethod_L source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PGIQERWorkingMethod source text reference
  • references source dbo.QER_PITShopHelperFill source text reference
  • references source dbo.QER_PITShopHelperFill_bulk source text reference
  • references source dbo.QER_PITShopOrderMove source text reference

References

Referenced By

Complete Source

SQL907 lines
1CREATE PROCEDURE QER_PITShopOrderMove_bulk(2  @pwos QBM_YParameterList READONLY3)4AS5BEGIN6  DECLARE @PwoToMove TABLE(UID_PersonWantsOrg varchar(38) collate database_default NOT NULL,7  GenProcID varchar(38) collate database_default NULL,8  XObjectKey varchar(138) collate database_default NOT NULL,9  UID_BOTarget varchar(38) collate database_default NULL,10  IsRemovePersonFromShop BIT DEFAULT 0,11  ObjectKeyOrdered varchar(138) collate database_default NOT NULL,12  UID_OrgPR_Old varchar(38) collate database_default NULL,13  UID_OrgBO_Old varchar(38) collate database_default NULL,14  UID_OrgSH_Old varchar(38) collate database_default NULL,15  UID_PersonOrdered_Old varchar(38) collate database_default NULL,16  UID_PersonOrdered_New varchar(38) collate database_default NULL,17  UID_PersonInserted varchar(38) collate database_default NULL,18  Orderstate varchar(16) collate database_default NULL,19  UID_ITShopOrgFinal varchar(38) collate database_default NULL,20  IsCopyOnShopChange BIT DEFAULT 0,21  ObjectKeyAssignment varchar(138) collate database_default NULL,22  IsKeepAssignment BIT DEFAULT 0,23  TargetTable varchar(38) collate database_default NULL,24  IsReusePossible BIT DEFAULT 0,25  IsReusePossibleUS BIT DEFAULT 0,26  IsAssignmentOrder BIT DEFAULT 0,27  UID_OrgPR_New varchar(38) collate database_default NULL,28  UID_OrgBO_New varchar(38) collate database_default NULL,29  UID_OrgSH_New varchar(38) collate database_default NULL,30  Ident_OrgPR_New nvarchar(256) collate database_default NULL,31  Ident_OrgBO_New nvarchar(256) collate database_default NULL,32  Ident_OrgSH_New nvarchar(256) collate database_default NULL,33  UID_PwodecisionMethodNew varchar(38) collate database_default NULL,34  UID_QERWorkingMethod varchar(38) collate database_default NULL,35  IsChangeValid BIT DEFAULT 1,36  IsOrderToMove BIT DEFAULT 1,37  Statements nvarchar(max) collate database_default NULL,38  ExecPersoninPRDel BIT DEFAULT 0,39  ExecPersonInPRIns BIT DEFAULT 0,40  ExecCallMethodAbort int DEFAULT 0,41  ExecCallMethod_mdSB BIT DEFAULT 0,42  ExecPWOUpdate int DEFAULT 0,43  execPersonInsertedNull BIT DEFAULT 0,44  execPersonOrderedNull BIT DEFAULT 0,45  primary key(UID_PersonWantsOrg),46  index PwoToMove1(GenProcID),47  index PwoToMove2(UID_OrgPR_new),48  index PwoToMove3(OrderState, IsOrderToMove))49  DECLARE @HistorySammler TABLE(UID_PersonWantsOrg varchar(38) collate database_default NOT NULL,50  DecisionType nvarchar(16) collate database_default NULL,51  GenProcID varchar(38) collate database_default NULL,52  XUser nvarchar(64) collate database_default NULL,53  Description nvarchar(256) collate database_default NULL,54  Orderstate varchar(16) collate database_default NULL,55  UID_ITShopOrgFinal varchar(38) collate database_default NULL)56  DECLARE @InsertHistorySammler int = 157  DECLARE @XUser nvarchar(64) = object_name(@@procid)58  DECLARE @jetzt datetime = getutcdate()59  DECLARE @DescriptionForHistory nvarchar(256) = '#LDS#Move IT Shop product to new shelf.|'60  DECLARE @AbortWhereAddOn nvarchar(1000) = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'61  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')62  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')63  DECLARE @UID_PwodecisionMethodNew varchar(38),64  @UID_QERWorkingMethod varchar(38),65  @Orderstate varchar(16)66  DECLARE @ElementCount int67  DECLARE @ElementIndex int68  DECLARE @ElementLast int69  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))70  DECLARE @QBM_BitPatternXOrigin_Direct int = dbo.QBM_FGIBitPatternXOrigin('|Direct|',71  0)72  DECLARE @QBM_BitPatternXOrigin_Direct_inv int = dbo.QBM_FGIBitPatternXOrigin('|Direct|',73  1)74  DECLARE @GenProcIDImZyklus varchar(38)75  DECLARE @pwoToAbort QBM_YParameterList76  DECLARE @UID_PersonWantsOrg varchar(38)77  DECLARE @SQLCmd nvarchar(max)78  DECLARE @DebugSwitch int = 079  DECLARE @PWOsToFill QBM_YParameterlist80  DECLARE @AdditionalObjectKeysAffected QBM_YParameterList81  SET XACT_ABORT OFF82  BEGIN TRY83    EXEC QBM_PSessionContextSet 'XUser',84      @XUser85    INSERT INTO @PwoToMove(UID_PersonWantsOrg,86    XObjectKey,87    UID_BOTarget,88    GenProcID,89    IsRemovePersonFromShop,90    ObjectKeyOrdered,91    UID_OrgPR_Old,92    UID_OrgBO_Old,93    UID_OrgSH_Old,94    UID_PersonOrdered_Old,95    UID_PersonOrdered_New,96    UID_PersonInserted,97    Orderstate,98    UID_ITShopOrgFinal,99    IsCopyOnShopChange,100    ObjectKeyAssignment,101    IsKeepAssignment,102    TargetTable,103    IsReusePossible,104    IsReusePossibleUS,105    IsAssignmentOrder,106    uid_OrgSH_New,107    UID_OrgBO_New,108    UID_OrgPR_New,109    Ident_OrgBO_New,110    Ident_OrgSH_New,111    Ident_OrgPR_New,112    UID_PwodecisionMethodNew)113    SELECT114      po.Parameter1 AS UID_PersonWantsOrg,115      pwo.XObjectKey,116      po.Parameter2 AS UID_BOTarget,117      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,118      po.Parameter3,119      @CfgUseGenProcID) AS GenProcID,120      po.HasContentFull AS IsRemovePersonFromShop,121      pwo.ObjectKeyOrdered AS ObjectKeyOrdered,122      isnull(pwo.UID_Org,123      '') AS UID_OrgPR_Old,124      isnull(pwo.UID_OrgParent,125      '') AS UID_OrgBO_Old,126      isnull(pwo.UID_OrgParentOfParent,127      '') AS UID_OrgSH_Old,128      pwo.UID_PersonOrdered AS UID_PersonOrdered_Old,129      pwo.UID_PersonOrdered AS UID_PersonOrdered_New,130      isnull(pwo.UID_PersonInserted,131      '') AS UID_PersonInserted,132      pwo.OrderState AS Orderstate,133      isnull(pwo.UID_ITShopOrgFinal,134      '') AS UID_ITShopOrgFinal,135      ISNULL(ac.IsCopyOnShopChange,136      0) AS IsCopyOnShopChange,137      pwo.ObjectKeyAssignment AS ObjectKeyAssignment,138    CASE139      WHEN ISNULL(qa.IsKeepAssignment,140    0) = 1 AND(pwo.ObjectKeyAssignment LIKE CONCAT('%<P>', pwo.UID_PersonOrdered, '</P>%') OR pwo.ObjectKeyAssignment LIKE CONCAT('%<P>',141    pwo.UID_PersonInserted, '</P>%')) THEN142    0143    ELSE ISNULL(qa.IsKeepAssignment,144    0)145    END AS IsKeepAssignment,146    tmn.TableName AS TargetTable,147    ba.IsReusePossible AS IsReusePossible,148    ba.IsReusePossibleUS AS IsReusePossibleUS,149    CASE ba.UID_BaseTreeAssign150      WHEN 'QER-AsgnBT-QERAssign' THEN151    1152    ELSE 0153    END AS IsAssignmentOrder,154    nd.UID_OrgSH,155    nd.UID_OrgBO,156    nd.UID_OrgPR,157    nd.Ident_OrgBO,158    nd.Ident_OrgSH,159    nd.Ident_OrgPR,160    nd.UID_PWODecisionMethod161    FROM @pwos po162    JOIN PersonWantsOrg pwo163      ON po.Parameter1 = pwo.UID_PersonWantsOrg164    JOIN DialogTable tb165      WITH(readpast)166      ON pwo.ObjectKeyOrdered LIKE CONCAT('<Key><T>',167    tb.TableName,168    '</T>%')169    JOIN BaseTreeAssign ba170      ON ba.UID_DialogTableElement = tb.UID_DialogTable171    JOIN DialogTable tmn172      WITH(readpast)173      ON ba.UID_DialogTableMN = tmn.UID_DialogTable174    OUTER apply dbo.QER_FTITShopNewProductNode(po.Parameter2,175    pwo.ObjectKeyOrdered,176    pwo.OrderState,177    isnull(pwo.UID_Org, ''),178    ba.IsReusePossible,179    pwo.UID_PersonOrdered,180    pwo.UID_PersonOrdered) nd181    LEFT182    OUTER183    JOIN BaseTree pr184      ON pwo.UID_Org = pr.UID_Org185    LEFT186    OUTER187    JOIN AccProduct ac188      ON pr.UID_AccProduct = ac.UID_AccProduct189    LEFT190    OUTER191    JOIN QERAssign qa192      ON qa.XObjectKey = pwo.ObjectKeyOrdered193    IF @DebugSwitch > 0194    BEGIN195      print 'nach der 1. Befüllung'196      SELECT *197      FROM @PwoToMove198    END199    UPDATE @PwoToMove200    SET IsChangeValid = 0,201    IsOrderToMove = 0202    FROM @PwoToMove m203    WHERE204      m.IsRemovePersonFromShop = 1 AND m.IsAssignmentOrder = 0 AND NOT EXISTS(205    SELECT TOP 1 1206    FROM Person p207    WHERE208      p.UID_Person = m.UID_PersonOrdered_Old)209    IF dbo.QBM_FGIConfigparmValue('QER\ITShop\ReplaceAssignmentRequestOnLeaveCU') > ' '210    BEGIN211      UPDATE @PwoToMove212      SET UID_PersonOrdered_New = dbo.QER_FGIITShopNewPersonOrdered(mo.UID_PersonWantsOrg,213      mo.UID_BOTarget,214      mo.ObjectkeyOrdered)215      FROM @PwoToMove mo216      WHERE217        dbo.QER_FGIITShopNewPersonOrdered(mo.UID_PersonWantsOrg,218      mo.UID_BOTarget,219      mo.ObjectkeyOrdered) > ' ' AND mo.IsRemovePersonFromShop = 1 AND mo.UID_OrgPR_New IS NULL AND mo.IsAssignmentOrder = 1 AND mo.IsKeepAssignment220      = 1 AND mo.IsOrderToMove = 1 AND mo.OrderState IN('Assigned',221      'OrderProlongate',222      'OrderUnsubscribe')223      IF @@rowcount > 0224      BEGIN225        UPDATE @PwoToMove226        SET uid_OrgSH_New = nd.UID_OrgSH,227        UID_OrgBO_New = nd.UID_OrgBO,228        UID_OrgPR_New = nd.UID_OrgPR,229        Ident_OrgBO_New = nd.Ident_OrgBO,230        Ident_OrgSH_New = nd.Ident_OrgSH,231        Ident_OrgPR_New = nd.Ident_OrgPR,232        UID_PwodecisionMethodNew = nd.UID_PWODecisionMethod233        FROM @PwoToMove mo CROSS apply dbo.QER_FTITShopNewProductNode(mo.UID_BOTarget,234        mo.ObjectkeyOrdered,235        mo.Orderstate,236        mo.UID_OrgPR_Old,237        mo.IsReusePossible,238        mo.UID_PersonOrdered_Old,239        mo.UID_PersonOrdered_New) nd240        WHERE241          mo.UID_PersonOrdered_New <> mo.UID_PersonOrdered_Old242        INSERT INTO @HistorySammler(UID_PersonWantsOrg,243        DecisionType,244        GenProcID,245        XUser,246        Description,247        OrderState,248        UID_ITShopOrgFinal)249        SELECT250          mo.uid_personwantsorg,251          'ChangeBoard',252          mo.GenProcID,253          @XUser,254          'New UID_PersonOrdered set',255          mo.OrderState,256          mo.UID_OrgPR_New257        FROM @PwoToMove mo258        WHERE259          mo.UID_PersonOrdered_New <> mo.UID_PersonOrdered_Old AND mo.OrderState IN('Assigned',260        'OrderProlongate',261        'OrderUnsubscribe')262        SELECT @InsertHistorySammler = 0263      END264    END265    UPDATE @PwoToMove266    SET IsChangeValid = 0267    WHERE268      UID_OrgPR_New IS NULL269    UPDATE @PwoToMove270    SET IsChangeValid = 0271    FROM @PwoToMove mo272    WHERE273      mo.IsReusePossible = 0 AND mo.IsChangeValid = 1 AND EXISTS(274    SELECT TOP 1 1275    FROM PersonInBaseTree pio276    WHERE277      pio.UID_Person = mo.UID_PersonOrdered_Old AND pio.UID_Org = mo.uid_OrgPR_New AND pio.XOrigin > 0) AND mo.OrderState IN('Assigned',278    'OrderProlongate',279    'OrderUnsubscribe')280    UPDATE @PwoToMove281    SET IsOrderToMove = 0282    FROM @PwoToMove mo283    WHERE284      mo.IsChangeValid = 0 OR(mo.IsChangeValid = 1 AND mo.IsCopyOnShopChange = 0) OR mo.OrderState IN('Aborted',285    'Dismissed',286    'Unsubscribed')287    UPDATE @PwoToMove288    SET execPersonInsertedNull = 1289    FROM @PwoToMove mo290    LEFT291    OUTER292    JOIN person pin293      ON mo.UID_PersonInserted = pin.UID_Person294    WHERE295      mo.IsOrderToMove = 0 AND pin.UID_Person IS NULL296    UPDATE @PwoToMove297    SET execPersonOrderedNull = 1298    FROM @PwoToMove mo299    LEFT300    OUTER301    JOIN person po302      ON mo.UID_PersonOrdered_Old = po.UID_Person303    WHERE304      mo.IsOrderToMove = 0 AND mo.IsKeepAssignment = 1 AND po.UID_Person IS NULL305    IF @InsertHistorySammler >0306    BEGIN307      INSERT INTO @HistorySammler(UID_PersonWantsOrg,308      DecisionType,309      GenProcID,310      XUser,311      Description,312      OrderState,313      UID_ITShopOrgFinal)314      SELECT315        mo.UID_PersonWantsOrg,316        'ChangeBoard',317        mo.GenProcID,318        @XUser,319        @DescriptionForHistory,320        mo.OrderState,321        mo.UID_OrgPR_New322      FROM @PwoToMove mo323      WHERE324        mo.OrderState IN('Aborted',325      'Dismissed',326      'Unsubscribed') AND IsOrderToMove = 1327    END328    UPDATE @PwoToMove329    SET ExecPersoninPRDel = 1330    FROM @PwoToMove mo331    WHERE332      mo.Orderstate IN('Assigned',333    'Granted',334    'OrderProlongate',335    'OrderUnsubscribe',336    'Waiting') AND mo.IsOrderToMove = 0337    UPDATE @PwoToMove338    SET Statements = CONCAT(mo.Statements,339    '340									update ',341    t.TableName,342    '		set XOrigin = XOrigin | ',343    str(@QBM_BitPatternXOrigin_Direct),344    ' ',345    CASE dbo.QBM_FGIColumnExistsInSchema(t.TableName, 'XDateUpdated')346      WHEN 1 THEN347    ', XDateUpdated = getutcdate(), XUserUpdated = ''QER_PITShopOrderMove_bulk'' '348    ELSE ''349    END,350    '351									where XObjectKey = ''',352    mo.ObjectKeyAssignment,353    '''	354							')355    FROM @PwoToMove mo356    JOIN DialogTable t357      WITH(readpast)358      ON mo.ObjectKeyAssignment LIKE CONCAT('<Key><T>',359    t.TableName,360    '</T>%')361    WHERE362      mo.Orderstate IN('Assigned',363    'Granted',364    'OrderProlongate',365    'OrderUnsubscribe',366    'Waiting') AND mo.IsOrderToMove = 0 AND mo.IsAssignmentOrder = 1 AND mo.IsRemovePersonFromShop = 1 AND mo.IsKeepAssignment = 1 AND EXISTS(367    SELECT TOP 1 1368    FROM OrgRootAssign oa369    JOIN BaseTree b370      ON oa.UID_OrgRoot = b.UID_OrgRoot AND dbo.QER_FCVObjectkeyToBaseTree(mo.ObjectKeyAssignment) = b.UID_Org371    JOIN BaseTreeAssign ba372      ON oa.UID_BaseTreeAssign = ba.UID_BaseTreeAssign373    JOIN DialogTable t374      WITH(readpast)375      ON mo.ObjectKeyAssignment LIKE CONCAT('<Key><T>', t.TableName, '</T>%') AND ba.UID_DialogTableMN = t.UID_DialogTableBase376    WHERE377      oa.IsDirectAssignmentAllowed = 1)378    UPDATE @PwoToMove379    SET ExecCallMethodAbort = 1380    FROM @PwoToMove mo381    WHERE382      mo.Orderstate IN('Assigned',383    'Granted',384    'OrderProlongate',385    'OrderUnsubscribe',386    'Waiting') AND mo.IsOrderToMove = 0 AND mo.execPersonOrderedNull = 0387    UPDATE @PwoToMove388    SET ExecCallMethodAbort = CASE389    WHEN EXISTS(390    SELECT TOP 1 1391    FROM OrgRootAssign oa392    JOIN BaseTree b393      ON oa.UID_OrgRoot = b.UID_OrgRoot AND dbo.QER_FCVObjectkeyToBaseTree(mo.ObjectKeyAssignment) = b.UID_Org394    JOIN BaseTreeAssign ba395      ON oa.UID_BaseTreeAssign = ba.UID_BaseTreeAssign396    JOIN DialogTable t397      WITH(readpast)398      ON mo.ObjectKeyAssignment LIKE CONCAT('<Key><T>', t.TableName, '</T>%') AND ba.UID_DialogTableMN = t.UID_DialogTableBase399    WHERE400      oa.IsDirectAssignmentAllowed = 1) THEN401    2402    ELSE 3403    END404    FROM @PwoToMove mo405    WHERE406      mo.Orderstate IN('Assigned',407    'Granted',408    'OrderProlongate',409    'OrderUnsubscribe',410    'Waiting') AND mo.IsOrderToMove = 0 AND mo.IsAssignmentOrder = 1 AND mo.IsRemovePersonFromShop = 1 AND mo.IsKeepAssignment = 1411    UPDATE @PwoToMove412    SET ExecPWOUpdate = 1413    FROM @PwoToMove mo414    WHERE415      mo.Orderstate IN('Assigned',416    'Granted',417    'OrderProlongate',418    'OrderUnsubscribe',419    'Waiting') AND mo.IsOrderToMove = 1420    IF @InsertHistorySammler > 0421    BEGIN422      INSERT INTO @HistorySammler(UID_PersonWantsOrg,423      DecisionType,424      GenProcID,425      XUser,426      Description,427      OrderState,428      UID_ITShopOrgFinal)429      SELECT430        mo.uid_personwantsorg,431        'ChangeBoard',432        mo.GenProcID,433        @XUser,434        @DescriptionForHistory,435        mo.OrderState,436        mo.UID_OrgPR_New437      FROM @PwoToMove mo438      WHERE439        mo.Orderstate IN('Assigned',440      'Granted',441      'OrderProlongate',442      'OrderUnsubscribe',443      'Waiting') AND mo.IsOrderToMove = 1444    END445    UPDATE @PwoToMove446    SET ExecPersoninPRDel = 1,447    ExecPersonInPRIns = 1448    FROM @PwoToMove mo449    WHERE450      mo.OrderState IN('Assigned',451    'OrderProlongate',452    'OrderUnsubscribe') AND mo.IsOrderToMove = 1 AND mo.IsReusePossible = 0453    UPDATE @PwoToMove454    SET ExecCallMethodAbort = 1,455    ExecPersoninPRDel = 1456    FROM @PwoToMove mo457    WHERE458      mo.Orderstate = 'OrderProduct' AND mo.IsOrderToMove = 0459    IF @InsertHistorySammler > 0460    BEGIN461      INSERT INTO @HistorySammler(UID_PersonWantsOrg,462      DecisionType,463      GenProcID,464      XUser,465      Description,466      OrderState,467      UID_ITShopOrgFinal)468      SELECT469        mo.uid_personwantsorg,470        'ChangeBoard',471        mo.GenProcID,472        @XUser,473        @DescriptionForHistory,474        mo.OrderState,475        mo.UID_OrgPR_New476      FROM @PwoToMove mo477      WHERE478        mo.Orderstate IN('OrderProduct',479      'Reset') AND mo.IsOrderToMove = 1480    END481    UPDATE @PwoToMove482    SET ExecPWOUpdate = 2483    FROM @PwoToMove mo484    WHERE485      mo.Orderstate IN('OrderProduct',486    'OrderProlongate',487    'OrderUnsubscribe') AND mo.IsOrderToMove = 1488    DECLARE @ElementOrderState QBM_YCursorbuffer489    INSERT INTO @ElementOrderState(UID1,490    Ident1)491    SELECT492      DISTINCT mo.UID_PwodecisionMethodNew,493      mo.Orderstate494    FROM @PwoToMove mo495    WHERE496      mo.OrderState IN('Granted',497    'OrderProduct',498    'OrderProlongate',499    'Waiting') AND mo.IsOrderToMove = 1 AND mo.UID_PwodecisionMethodNew > ' '500    SELECT @ElementCount = @@ROWCOUNT501    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1502    SELECT @ElementLast = @@IDENTITY503    WHILE @ElementIndex <= @ElementLast504    BEGIN505      SELECT506        TOP 1 @UID_PwodecisionMethodNew = bu.UID1,507        @Orderstate = bu.Ident1508      FROM @ElementOrderState bu509      WHERE510        bu.ElementIndex = @ElementIndex511      EXEC QER_PGIQERWorkingMethod @UID_PwodecisionMethodNew,512        @Orderstate,513        @UID_QERWorkingMethod OUTPUT514      UPDATE @PwoToMove515      SET UID_QERWorkingMethod = @UID_QERWorkingMethod516      FROM @PwoToMove mo517      WHERE518        mo.UID_PwodecisionMethodNew = @UID_PwodecisionMethodNew AND mo.Orderstate = @Orderstate519      SELECT @ElementIndex += 1520    END521    UPDATE @PwoToMove522    SET ExecCallMethod_mdSB = 1523    FROM @PwoToMove mo524    JOIN QERWorkingStep s525      ON mo.UID_QERWorkingMethod = s.UID_QERWorkingMethod AND s.LevelNumber = 0 AND s.UID_PWODecisionRule = 'QER-PWODecisionRule-SB'526    WHERE527      mo.Orderstate = 'OrderProduct' AND mo.IsOrderToMove = 1528    IF @DebugSwitch > 0529    BEGIN530      print 'nach der vollständigen Analyse'531      SELECT *532      FROM @PwoToMove533    END534    INSERT INTO pwodecisionHistory(UID_PWODecisionHistory,535    UID_PersonWantsOrg,536    DisplayPersonHead,537    ReasonHead,538    DateHead,539    XDateInserted,540    XDateUpdated,541    XUserInserted,542    XUserUpdated,543    DecisionType,544    IsDecisionBySystem,545    XObjectKey,546    OrderState,547    UID_ITShopOrgFinal)548    SELECT549      UID_PWODecisionHistory,550      UID_PersonWantsOrg,551      DisplayPersonHead,552      ReasonHead,553      @jetzt,554      @jetzt,555      @jetzt,556      @XUser,557      @XUser,558      DecisionType,559      1,560      dbo.QBM_FCVElementToObjectKey1('pwodecisionHistory',561      'UID_PWODecisionHistory',562      x.UID_PWODecisionHistory),563      OrderState,564      UID_ITShopOrgFinal565    FROM(566    SELECT567      Newid() AS UID_PWODecisionHistory,568      h.UID_PersonWantsOrg,569      h.Description AS DisplayPersonHead,570      h.Description AS ReasonHead,571      h.DecisionType,572      h.OrderState,573      h.UID_ITShopOrgFinal574    FROM @HistorySammler h) AS x575    DECLARE @ElementSatetement QBM_YCursorBuffer576    INSERT INTO @ElementSatetement(ContentFull,577    UID1)578    SELECT579      DISTINCT mo.Statements,580      mo.GenProcID581    FROM @PwoToMove mo582    WHERE583      mo.Statements > ' '584    EXEC QBM_PExecuteSQLMulti @ElementSatetement,585    1,586      @@PROCID587    DECLARE @GenProcIDs QBM_YCursorBuffer588    INSERT INTO @GenProcIDs(UID1)589    SELECT590      DISTINCT mo.GenProcID591    FROM @PwoToMove mo592    SELECT @ElementCount = @@ROWCOUNT593    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1594    SELECT @ElementLast = @@IDENTITY595    WHILE @ElementIndex <= @ElementLast596    BEGIN597      SELECT TOP 1 @GenProcIDImZyklus = bu.UID1598      FROM @GenProcIDs bu599      WHERE600        bu.ElementIndex = @ElementIndex601      EXEC QBM_PSessionContextSet 'GenProcID',602        @GenProcIDImZyklus603      IF EXISTS(604        SELECT TOP 1 1605        FROM @PwoToMove mo606        WHERE607          mo.ExecPersoninPRDel = 1 AND mo.GenProcID = @GenProcIDImZyklus)608      BEGIN609        UPDATE PersonInBaseTree610        SET XOrigin = XOrigin & @QBM_BitPatternXOrigin_Direct_inv,611        XDateUpdated = @jetzt,612        XUserUpdated = @XUser613        FROM PersonInBaseTree pio614        JOIN @PwoToMove mo615          ON pio.UID_Person = mo.UID_PersonOrdered_Old AND pio.UID_Org = mo.uid_OrgPR_Old616        WHERE617          mo.ExecPersoninPRDel = 1 AND mo.GenProcID = @GenProcIDImZyklus618        DELETE PersonInBaseTree619        FROM PersonInBaseTree pio620        JOIN @PwoToMove mo621          ON pio.UID_Person = mo.UID_PersonOrdered_Old AND pio.UID_Org = mo.uid_OrgPR_Old622        WHERE623          mo.ExecPersoninPRDel = 1 AND mo.GenProcID = @GenProcIDImZyklus624      END625      IF EXISTS(626        SELECT TOP 1 1627        FROM @PwoToMove mo628        WHERE629          mo.ExecPersonInPRIns = 1 AND mo.GenProcID = @GenProcIDImZyklus)630      BEGIN631        INSERT INTO PersonInBaseTree(UID_Org,632        UID_Person,633        XDateInserted,634        XDateUpdated,635        XObjectKey,636        XUserInserted,637        XUserUpdated,638        XOrigin)639        SELECT640          DISTINCT mo.UID_OrgPR_New,641          mo.UID_PersonOrdered_New,642          @jetzt,643          @jetzt,644          dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg',645          'UID_ITShopOrg',646          mo.UID_OrgPR_New,647          'UID_Person',648          mo.UID_PersonOrdered_New),649          @XUser,650          @XUser,651          @QBM_BitPatternXOrigin_Direct652        FROM @PwoToMove mo653        WHERE654          mo.ExecPersonInPRIns = 1 AND mo.GenProcID = @GenProcIDImZyklus AND NOT EXISTS(655        SELECT TOP 1 1656        FROM PersonInBaseTree pio657        WHERE658          pio.UID_Org = mo.UID_OrgPR_New AND pio.UID_Person = mo.UID_PersonOrdered_New)659      END660      IF EXISTS(661        SELECT TOP 1 1662        FROM @PwoToMove mo663        WHERE664          mo.ExecPWOUpdate = 1 AND mo.GenProcID = @GenProcIDImZyklus)665      BEGIN666        UPDATE personwantsorg667        SET uid_org = mo.uid_orgPR_new,668        uid_orgParent = mo.UID_OrgBO_New,669        uid_OrgParentOfParent = mo.UID_OrgSH_New,670        UID_ITShopOrgFinal = mo.UID_OrgPR_New,671        DisplayOrgParent = mo.Ident_OrgBO_New,672        DisplayOrgParentOfParent = mo.Ident_OrgSH_New,673        DateHead = @jetzt,674        ReasonHead = 'ChangeBoard',675        XDateUpdated = @jetzt,676        XUserUpdated = @XUser,677        UID_QERWorkingMethod = mo.UID_QERWorkingMethod,678        UID_PersonOrdered = mo.UID_PersonOrdered_New679        FROM @PwoToMove mo680        JOIN PersonWantsOrg pwo681          ON mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg682        WHERE683          mo.ExecPWOUpdate = 1 AND mo.GenProcID = @GenProcIDImZyklus684      END685      IF EXISTS(686        SELECT TOP 1 1687        FROM @PwoToMove mo688        WHERE689          mo.ExecPWOUpdate = 2 AND mo.GenProcID = @GenProcIDImZyklus)690      BEGIN691        UPDATE personwantsorg692        SET decisionlevel = 0,693        UID_PWOState = NULL,694        UID_PersonHead = NULL,695        DisplayPersonHead = NULL,696        DateHead = @jetzt,697        ReasonHead = 'ChangeBoard',698        XDateUpdated = @jetzt,699        XUserUpdated = @XUser,700        UID_Org = mo.UID_OrgPR_New,701        UID_OrgParent = mo.UID_OrgBO_New,702        UID_OrgParentOfParent = mo.UID_OrgSH_New,703        UID_ITShopOrgFinal = mo.UID_OrgPR_New,704        DisplayOrgParent = mo.Ident_OrgBO_New,705        DisplayOrgParentOfParent = mo.Ident_OrgSH_New,706        UID_QERWorkingMethod = mo.UID_QERWorkingMethod,707        UID_PersonOrdered = mo.UID_PersonOrdered_New708        FROM @PwoToMove mo709        JOIN PersonWantsOrg pwo710          ON mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg711        WHERE712          mo.ExecPWOUpdate = 2 AND mo.GenProcID = @GenProcIDImZyklus713        DELETE PWOHelperPWO714        FROM @PwoToMove mo715        JOIN PWOHelperPWO h716          ON mo.UID_PersonWantsOrg = h.UID_PersonWantsOrg717        WHERE718          mo.ExecPWOUpdate = 2 AND mo.GenProcID = @GenProcIDImZyklus719      END720      IF EXISTS(721        SELECT TOP 1 1722        FROM @PwoToMove mo723        WHERE724          mo.execPersonInsertedNull = 1 AND mo.GenProcID = @GenProcIDImZyklus)725      BEGIN726        UPDATE PersonWantsOrg727        SET UID_PersonInserted = NULL,728        XDateUpdated = @jetzt,729        XUserUpdated = @XUser730        FROM @PwoToMove mo731        JOIN PersonWantsOrg pwo732          ON mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg733        WHERE734          mo.execPersonInsertedNull = 1735      END736      IF EXISTS(737        SELECT TOP 1 1738        FROM @PwoToMove mo739        WHERE740          mo.execPersonOrderedNull = 1 AND mo.GenProcID = @GenProcIDImZyklus)741      BEGIN742        UPDATE PersonWantsOrg743        SET UID_PersonOrdered = NULL,744        XDateUpdated = @jetzt,745        XUserUpdated = @XUser746        FROM @PwoToMove mo747        JOIN PersonWantsOrg pwo748          ON mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg749        WHERE750          mo.execPersonOrderedNull = 1751      END752      IF EXISTS(753        SELECT TOP 1 1754        FROM @PwoToMove mo755        WHERE756          mo.ExecCallMethodAbort = 1 AND mo.GenProcID = @GenProcIDImZyklus)757      BEGIN758        INSERT INTO @AdditionalObjectKeysAffected(Parameter1)759        SELECT b.XObjectKey760        FROM @PwoToMove mo761        JOIN PersonWantsOrg pwo762          ON mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg763        JOIN Basetree b764          ON b.UID_Org = pwo.UID_Org765        WHERE766          mo.ExecCallMethodAbort = 1 AND mo.GenProcID = @GenProcIDImZyklus767        DELETE @pwoToAbort768        INSERT INTO @pwoToAbort(Parameter1)769        SELECT mo.XObjectKey770        FROM @PwoToMove mo771        WHERE772          mo.ExecCallMethodAbort = 1 AND mo.GenProcID = @GenProcIDImZyklus773        IF @@ROWCOUNT > 0774        BEGIN775          EXEC QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg',776            @pwoToAbort,777          1,778          'Abort',779            @GenProcIDImZyklus,780            @AdditionalObjectKeysAffected = @AdditionalObjectKeysAffected,781            @param1 = '#LDS#Product no longer available.|',782            @Retries = 3,783            @checkForExisting = 1,784            @WhereClauseAdditional = @AbortWhereAddOn785        END786      END787      IF EXISTS(788        SELECT TOP 1 1789        FROM @PwoToMove mo790        WHERE791          mo.ExecCallMethodAbort = 2 AND mo.GenProcID = @GenProcIDImZyklus)792      BEGIN793        DELETE @pwoToAbort794        INSERT INTO @pwoToAbort(Parameter1)795        SELECT mo.XObjectKey796        FROM @PwoToMove mo797        WHERE798          mo.ExecCallMethodAbort = 2 AND mo.GenProcID = @GenProcIDImZyklus799        IF @@ROWCOUNT > 0800        BEGIN801          EXEC QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg',802            @pwoToAbort,803          1,804          'Abort',805            @GenProcIDImZyklus,806            @AdditionalObjectKeysAffected = DEFAULT,807            @param1 = '#LDS#Assignment order will be switched to a direct assignment.|',808            @Retries = 3,809            @checkForExisting = 1,810            @WhereClauseAdditional = @AbortWhereAddOn811        END812      END813      IF EXISTS(814        SELECT TOP 1 1815        FROM @PwoToMove mo816        WHERE817          mo.ExecCallMethodAbort = 3 AND mo.GenProcID = @GenProcIDImZyklus)818      BEGIN819        DELETE @pwoToAbort820        INSERT INTO @pwoToAbort(Parameter1)821        SELECT mo.XObjectKey822        FROM @PwoToMove mo823        WHERE824          mo.ExecCallMethodAbort = 3 AND mo.GenProcID = @GenProcIDImZyklus825        IF @@ROWCOUNT > 0826        BEGIN827          EXEC QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg',828            @pwoToAbort,829          1,830          'Abort',831            @GenProcIDImZyklus,832            @AdditionalObjectKeysAffected = DEFAULT,833            @param1 = '#LDS#Assignment order will be aborted because a direct assignment is not allowed.|',834            @Retries = 3,835            @checkForExisting = 1,836            @WhereClauseAdditional = @AbortWhereAddOn837        END838      END839      SELECT @ElementIndex += 1840    END841    INSERT INTO @PWOsToFill(Parameter1,842    Parameter2,843    Parameter3,844    HasContentFull)845    SELECT846      mo.UID_PersonWantsOrg,847      mo.GenProcID,848      '0',849      0850    FROM @PwoToMove mo851    WHERE852      mo.ExecPWOUpdate = 2853    EXEC QER_PITShopHelperFill_bulk @PWOsToFill854    DECLARE @GenProcIDsAbort QBM_YCursorBuffer855    INSERT INTO @GenProcIDsAbort(UID1)856    SELECT857      DISTINCT mo.GenProcID858    FROM @PwoToMove mo859    WHERE860      mo.ExecCallMethod_mdSB = 1861    SELECT @ElementCount = @@ROWCOUNT862    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1863    SELECT @ElementLast = @@IDENTITY864    WHILE @ElementIndex <= @ElementLast865    BEGIN866      SELECT TOP 1 @GenProcIDImZyklus = bu.UID1867      FROM @GenProcIDsAbort bu868      WHERE869        bu.ElementIndex = @ElementIndex870      DELETE @pwoToAbort871      INSERT INTO @pwoToAbort(Parameter1)872      SELECT mo.XObjectKey873      FROM @PwoToMove mo874      WHERE875        mo.ExecCallMethod_mdSB = 1 AND mo.GenProcID = @GenProcIDImZyklus876      IF @@ROWCOUNT > 0877      BEGIN878        EXEC QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg',879          @pwoToAbort,880        1,881        'MakeDecision',882          @GenProcIDImZyklus,883          @AdditionalObjectKeysAffected = DEFAULT,884          @param1 = 'sa',885          @param2 = 'True',886          @param3 = 'SB',887          @Retries = 3,888          @checkForExisting = 1,889          @WhereClauseAdditional = @AbortWhereAddOn890      END891      SELECT @ElementIndex += 1892    END893  END TRY894  BEGIN CATCH895    EXEC QBM_PSessionErrorAdd DEFAULT896    RAISERROR('',897    18,898    1)899      WITH NOWAIT900  END CATCH901  ende:902  EXEC QBM_PSessionContextSet 'GenProcID',903    @GenProcID_R904  EXEC QBM_PSessionContextSet 'XUser',905    @XUser_R906  RETURN907END
Open raw exported source
SQL · Raw159 lines
1 create   procedure QER_PITShopOrderMove_bulk (@pwos QBM_YParameterList readonly     ) as begin declare @PwoToMove table(UID_PersonWantsOrg varchar2(38) collate database_default Not Null , GenProcID varchar(38) collate database_default Null , XObjectKey varchar(138) collate database_default Not Null3 , UID_BOTarget varchar(38) collate database_default Null  , IsRemovePersonFromShop bit default 0  , ObjectKeyOrdered varchar(138) collate database_default4 not Null , UID_OrgPR_Old varchar(38) collate database_default Null , UID_OrgBO_Old varchar(38) collate database_default Null  , UID_OrgSH_Old varchar(385) collate database_default Null  , UID_PersonOrdered_Old varchar(38) collate database_default Null , UID_PersonOrdered_New varchar(38) collate database_default6 Null , UID_PersonInserted varchar(38) collate database_default Null , Orderstate varchar(16) collate database_default Null  , UID_ITShopOrgFinal varchar7(38) collate database_default Null  , IsCopyOnShopChange bit default 0 , ObjectKeyAssignment varchar(138) collate database_default Null , IsKeepAssignment8 bit default 0 , TargetTable varchar(38) collate database_default Null , IsReusePossible bit default 0 , IsReusePossibleUS bit default 0 , IsAssignmentOrder9 bit default 0  , UID_OrgPR_New varchar(38) collate database_default Null , UID_OrgBO_New varchar(38) collate database_default Null , UID_OrgSH_New varchar10(38) collate database_default Null , Ident_OrgPR_New nvarchar(256) collate database_default Null , Ident_OrgBO_New nvarchar(256) collate database_default11 Null , Ident_OrgSH_New nvarchar(256) collate database_default Null , UID_PwodecisionMethodNew varchar(38) collate database_default Null , UID_QERWorkingMethod12 varchar(38) collate database_default Null  , IsChangeValid bit default 1 , IsOrderToMove bit default 1  , Statements nvarchar(max) collate database_default13 Null  , ExecPersoninPRDel bit default 0 , ExecPersonInPRIns bit default 0 , ExecCallMethodAbort int default 0    , ExecCallMethod_mdSB bit default 0  14, ExecPWOUpdate int default 0   , execPersonInsertedNull bit default 0 , execPersonOrderedNull bit default 0 , primary key (UID_PersonWantsOrg) , index15 PwoToMove1 (GenProcID) , index PwoToMove2 (UID_OrgPR_new) , index PwoToMove3 (OrderState, IsOrderToMove) ) declare @HistorySammler table (UID_PersonWantsOrg16 varchar(38) collate database_default not Null , DecisionType nvarchar(16) collate database_default Null , GenProcID varchar(38) collate database_default17 Null , XUser nvarchar(64) collate database_default Null , Description nvarchar(256) collate database_default Null  , Orderstate varchar(16) collate database_default18 Null , UID_ITShopOrgFinal varchar(38) collate database_default Null   ) declare @InsertHistorySammler int = 1 declare @XUser nvarchar(64) = object_name19(@@procid) declare @jetzt datetime = getutcdate() declare @DescriptionForHistory nvarchar(256)= '#LDS#Move IT Shop product to new shelf.|' declare @AbortWhereAddOn20 nvarchar(1000) = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')' declare21 @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')  declare @UID_PwodecisionMethodNew22 varchar(38) , @UID_QERWorkingMethod varchar(38) , @Orderstate varchar(16) declare @ElementCount int declare @ElementIndex int declare @ElementLast int23 declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))  declare @QBM_BitPatternXOrigin_Direct24 int = dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0) declare @QBM_BitPatternXOrigin_Direct_inv int = dbo.QBM_FGIBitPatternXOrigin('|Direct|', 1)  declare25 @GenProcIDImZyklus varchar(38) declare @pwoToAbort QBM_YParameterList declare @UID_PersonWantsOrg varchar(38) declare @SQLCmd nvarchar(max) declare @DebugSwitch26 int = 0 declare @PWOsToFill QBM_YParameterlist      declare @AdditionalObjectKeysAffected QBM_YParameterList SET XACT_ABORT OFF BEGIN TRY exec QBM_PSessionContextSet27 'XUser', @XUser insert into @PwoToMove ( UID_PersonWantsOrg , XObjectKey , UID_BOTarget , GenProcID  , IsRemovePersonFromShop  , ObjectKeyOrdered , UID_OrgPR_Old28 , UID_OrgBO_Old , UID_OrgSH_Old , UID_PersonOrdered_Old , UID_PersonOrdered_New , UID_PersonInserted , Orderstate , UID_ITShopOrgFinal  , IsCopyOnShopChange29 , ObjectKeyAssignment , IsKeepAssignment , TargetTable , IsReusePossible , IsReusePossibleUS , IsAssignmentOrder  ,uid_OrgSH_New , UID_OrgBO_New , UID_OrgPR_New30 , Ident_OrgBO_New , Ident_OrgSH_New , Ident_OrgPR_New , UID_PwodecisionMethodNew ) select po.Parameter1 as UID_PersonWantsOrg , pwo.XObjectKey , po.Parameter231 as UID_BOTarget , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, po.Parameter3, @CfgUseGenProcID) as GenProcID   , po.HasContentFull as IsRemovePersonFromShop32 , pwo.ObjectKeyOrdered as ObjectKeyOrdered , isnull(pwo.UID_Org, '') as UID_OrgPR_Old , isnull(pwo.UID_OrgParent, '') as UID_OrgBO_Old , isnull(pwo.UID_OrgParentOfParent33, '') as UID_OrgSH_Old , pwo.UID_PersonOrdered as UID_PersonOrdered_Old , pwo.UID_PersonOrdered as UID_PersonOrdered_New , isnull(pwo.UID_PersonInserted34, '') as UID_PersonInserted , pwo.OrderState as Orderstate , isnull(pwo.UID_ITShopOrgFinal, '') as UID_ITShopOrgFinal  , ISNULL(ac.IsCopyOnShopChange ,35 0) as IsCopyOnShopChange , pwo.ObjectKeyAssignment as ObjectKeyAssignment  , case when ISNULL(qa.IsKeepAssignment, 0) = 1 and ( pwo.ObjectKeyAssignment36 like concat('%<P>', pwo.UID_PersonOrdered , '</P>%') or pwo.ObjectKeyAssignment like concat('%<P>', pwo.UID_PersonInserted , '</P>%') ) then 0 else ISNULL37(qa.IsKeepAssignment, 0) end as IsKeepAssignment , tmn.TableName as TargetTable , ba.IsReusePossible as IsReusePossible , ba.IsReusePossibleUS as IsReusePossibleUS38 , case ba.UID_BaseTreeAssign when 'QER-AsgnBT-QERAssign' then 1 else 0 end as IsAssignmentOrder  , nd.UID_OrgSH , nd.UID_OrgBO , nd.UID_OrgPR , nd.Ident_OrgBO39 , nd.Ident_OrgSH , nd.Ident_OrgPR , nd.UID_PWODecisionMethod from @pwos po join PersonWantsOrg pwo on po.Parameter1 = pwo.UID_PersonWantsOrg join DialogTable40 tb with (readpast) on pwo.ObjectKeyOrdered like concat('<Key><T>' , tb.TableName , '</T>%') join BaseTreeAssign ba on ba.UID_DialogTableElement = tb.UID_DialogTable41 join DialogTable tmn with (readpast) on ba.UID_DialogTableMN = tmn.UID_DialogTable outer apply dbo.QER_FTITShopNewProductNode (po.Parameter2  , pwo.ObjectKeyOrdered42  , pwo.OrderState  , isnull(pwo.UID_Org, '')  , ba.IsReusePossible  , pwo.UID_PersonOrdered  , pwo.UID_PersonOrdered  ) nd left outer join BaseTree pr43 on pwo.UID_Org = pr.UID_Org left outer join AccProduct ac on pr.UID_AccProduct = ac.UID_AccProduct left outer join QERAssign qa on qa.XObjectKey = pwo.ObjectKeyOrdered44       if @DebugSwitch > 0 begin print 'nach der 1. Befüllung' select * from @PwoToMove end update @PwoToMove set IsChangeValid = 0 , IsOrderToMove = 045 from @PwoToMove m where m.IsRemovePersonFromShop = 1 and m.IsAssignmentOrder = 0 and not exists (select top 1 1 from Person p where p.UID_Person = m.UID_PersonOrdered_Old46 ) if dbo.QBM_FGIConfigparmValue('QER\ITShop\ReplaceAssignmentRequestOnLeaveCU') > ' ' begin update @PwoToMove set UID_PersonOrdered_New = dbo.QER_FGIITShopNewPersonOrdered47(mo.UID_PersonWantsOrg , mo.UID_BOTarget , mo.ObjectkeyOrdered ) from @PwoToMove mo where dbo.QER_FGIITShopNewPersonOrdered(mo.UID_PersonWantsOrg , mo.UID_BOTarget48 , mo.ObjectkeyOrdered ) > ' ' and mo.IsRemovePersonFromShop = 1 and mo.UID_OrgPR_New is null and mo.IsAssignmentOrder = 1 and mo.IsKeepAssignment = 1 49and mo.IsOrderToMove = 1      and mo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) if @@rowcount > 0 begin  update @PwoToMove set 50uid_OrgSH_New = nd.UID_OrgSH , UID_OrgBO_New = nd.UID_OrgBO , UID_OrgPR_New = nd.UID_OrgPR , Ident_OrgBO_New = nd.Ident_OrgBO , Ident_OrgSH_New = nd.Ident_OrgSH51 , Ident_OrgPR_New = nd.Ident_OrgPR , UID_PwodecisionMethodNew = nd.UID_PWODecisionMethod from @PwoToMove mo cross apply dbo.QER_FTITShopNewProductNode52 (mo.UID_BOTarget , mo.ObjectkeyOrdered , mo.Orderstate , mo.UID_OrgPR_Old , mo.IsReusePossible , mo.UID_PersonOrdered_Old , mo.UID_PersonOrdered_New )53 nd where mo.UID_PersonOrdered_New <> mo.UID_PersonOrdered_Old insert into @HistorySammler(UID_PersonWantsOrg, DecisionType, GenProcID, XUser, Description54, OrderState, UID_ITShopOrgFinal) select mo.uid_personwantsorg, 'ChangeBoard', mo.GenProcID, @XUser, 'New UID_PersonOrdered set', mo.OrderState, mo.UID_OrgPR_New55 from @PwoToMove mo where mo.UID_PersonOrdered_New <> mo.UID_PersonOrdered_Old  and mo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe'56 ) select @InsertHistorySammler = 0  end  end  update @PwoToMove set IsChangeValid = 0 where UID_OrgPR_New is null update @PwoToMove set IsChangeValid 57= 0 from @PwoToMove mo where mo.IsReusePossible = 0 and mo.IsChangeValid = 1 and exists (select top 1 1 from PersonInBaseTree pio where pio.UID_Person 58= mo.UID_PersonOrdered_Old and pio.UID_Org = mo.uid_OrgPR_New and pio.XOrigin > 0 )      and mo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe'59 )  update @PwoToMove set IsOrderToMove = 0 from @PwoToMove mo where mo.IsChangeValid = 0 or (mo.IsChangeValid = 1 and mo.IsCopyOnShopChange = 0  )    60  or mo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' )  update @PwoToMove set execPersonInsertedNull = 1 from @PwoToMove mo left outer join person61 pin on mo.UID_PersonInserted = pin.UID_Person where mo.IsOrderToMove = 0 and pin.UID_Person is null  update @PwoToMove set execPersonOrderedNull = 1 from62 @PwoToMove mo left outer join person po on mo.UID_PersonOrdered_Old = po.UID_Person where mo.IsOrderToMove = 0 and mo.IsKeepAssignment = 1 and po.UID_Person63 is null   if @InsertHistorySammler >0 begin insert into @HistorySammler(UID_PersonWantsOrg, DecisionType, GenProcID, XUser, Description, OrderState, UID_ITShopOrgFinal64) select mo.UID_PersonWantsOrg, 'ChangeBoard', mo.GenProcID, @XUser, @DescriptionForHistory, mo.OrderState, mo.UID_OrgPR_New from @PwoToMove mo      where65 mo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' )  and IsOrderToMove = 1 end     update @PwoToMove set ExecPersoninPRDel = 1 from @PwoToMove 66mo          where mo.Orderstate in ('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and mo.IsOrderToMove = 0    update @PwoToMove67 set Statements = concat(mo.Statements, '68									update ' , t.TableName , '		set XOrigin = XOrigin | ', str(@QBM_BitPatternXOrigin_Direct) , ' ' , case69 dbo.QBM_FGIColumnExistsInSchema(t.TableName, 'XDateUpdated') when 1 then ', XDateUpdated = getutcdate(), XUserUpdated = ''QER_PITShopOrderMove_bulk'' '70 else '' end , '71									where XObjectKey = ''' , mo.ObjectKeyAssignment , '''	72							' ) from @PwoToMove mo join DialogTable t with (readpast) on 73mo.ObjectKeyAssignment like concat('<Key><T>' , t.TableName , '</T>%')          where mo.Orderstate in ('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe'74, 'Waiting') and mo.IsOrderToMove = 0 and mo.IsAssignmentOrder = 1 and mo.IsRemovePersonFromShop = 1 and mo.IsKeepAssignment = 1  and exists (select top75 1 1 from OrgRootAssign oa join BaseTree b on oa.UID_OrgRoot = b.UID_OrgRoot and dbo.QER_FCVObjectkeyToBaseTree(mo.ObjectKeyAssignment) = b.UID_Org join76 BaseTreeAssign ba on oa.UID_BaseTreeAssign = ba.UID_BaseTreeAssign join DialogTable t with (readpast) on mo.ObjectKeyAssignment like concat('<Key><T>'77 , t.TableName , '</T>%')  and ba.UID_DialogTableMN = t.UID_DialogTableBase where oa.IsDirectAssignmentAllowed = 1 )    update @PwoToMove set ExecCallMethodAbort78 = 1 from @PwoToMove mo          where mo.Orderstate in ('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and mo.IsOrderToMove 79= 0  and mo.execPersonOrderedNull = 0  update @PwoToMove set ExecCallMethodAbort = case when exists (select top 1 1 from OrgRootAssign oa join BaseTree80 b on oa.UID_OrgRoot = b.UID_OrgRoot and dbo.QER_FCVObjectkeyToBaseTree(mo.ObjectKeyAssignment) = b.UID_Org join BaseTreeAssign ba on oa.UID_BaseTreeAssign81 = ba.UID_BaseTreeAssign join DialogTable t with (readpast) on mo.ObjectKeyAssignment like concat('<Key><T>' , t.TableName , '</T>%')  and ba.UID_DialogTableMN82 = t.UID_DialogTableBase where oa.IsDirectAssignmentAllowed = 1 ) then 2  else 3  end from @PwoToMove mo          where mo.Orderstate in ('Assigned', 'Granted'83, 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and mo.IsOrderToMove = 0 and mo.IsAssignmentOrder = 1 and mo.IsRemovePersonFromShop = 1 and mo.IsKeepAssignment84 = 1   update @PwoToMove set ExecPWOUpdate = 1 from @PwoToMove mo          where mo.Orderstate in ('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe'85, 'Waiting') and mo.IsOrderToMove = 1 if @InsertHistorySammler > 0 begin insert into @HistorySammler(UID_PersonWantsOrg, DecisionType, GenProcID, XUser86, Description, OrderState, UID_ITShopOrgFinal) select mo.uid_personwantsorg, 'ChangeBoard', mo.GenProcID, @XUser, @DescriptionForHistory, mo.OrderState87, mo.UID_OrgPR_New from @PwoToMove mo          where mo.Orderstate in ('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and mo.IsOrderToMove88 = 1 end   update @PwoToMove set ExecPersoninPRDel = 1 , ExecPersonInPRIns = 1 from @PwoToMove mo      where mo.OrderState in ('Assigned', 'OrderProlongate'89, 'OrderUnsubscribe' ) and mo.IsOrderToMove = 1 and mo.IsReusePossible = 0           update @PwoToMove set ExecCallMethodAbort = 1 , ExecPersoninPRDel 90= 1 from @PwoToMove mo where mo.Orderstate = 'OrderProduct' and mo.IsOrderToMove = 0    if @InsertHistorySammler > 0 begin insert into @HistorySammler(UID_PersonWantsOrg91, DecisionType, GenProcID, XUser, Description, OrderState, UID_ITShopOrgFinal) select mo.uid_personwantsorg, 'ChangeBoard', mo.GenProcID, @XUser, @DescriptionForHistory92, mo.OrderState, mo.UID_OrgPR_New from @PwoToMove mo where mo.Orderstate in( 'OrderProduct', 'Reset') and mo.IsOrderToMove = 1 end update @PwoToMove set93 ExecPWOUpdate = 2 from @PwoToMove mo where mo.Orderstate in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe')  and mo.IsOrderToMove = 1        declare94 @ElementOrderState QBM_YCursorbuffer insert into @ElementOrderState (UID1, Ident1)  select distinct mo.UID_PwodecisionMethodNew, mo.Orderstate from @PwoToMove95 mo  where  mo.OrderState in ('Granted', 'OrderProduct', 'OrderProlongate', 'Waiting') and mo.IsOrderToMove = 1 and mo.UID_PwodecisionMethodNew > ' ' select96 @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin97 select top 1 @UID_PwodecisionMethodNew = bu.UID1 , @Orderstate = bu.Ident1 from @ElementOrderState bu where bu.ElementIndex = @ElementIndex exec QER_PGIQERWorkingMethod98 @UID_PwodecisionMethodNew , @Orderstate, @UID_QERWorkingMethod output update @PwoToMove set UID_QERWorkingMethod = @UID_QERWorkingMethod from @PwoToMove99 mo where mo.UID_PwodecisionMethodNew = @UID_PwodecisionMethodNew and mo.Orderstate = @Orderstate select @ElementIndex += 1 end    update @PwoToMove set100 ExecCallMethod_mdSB = 1 from @PwoToMove mo join QERWorkingStep s on mo.UID_QERWorkingMethod = s.UID_QERWorkingMethod and s.LevelNumber = 0 and s.UID_PWODecisionRule101 = 'QER-PWODecisionRule-SB' where mo.Orderstate = 'OrderProduct' and mo.IsOrderToMove = 1 if @DebugSwitch > 0 begin print 'nach der vollständigen Analyse'102 select * from @PwoToMove end  insert into pwodecisionHistory (UID_PWODecisionHistory, UID_PersonWantsOrg, DisplayPersonHead, ReasonHead , DateHead, XDateInserted103, XDateUpdated, XUserInserted, XUserUpdated , DecisionType, IsDecisionBySystem, XObjectKey  , OrderState, UID_ITShopOrgFinal) select UID_PWODecisionHistory104, UID_PersonWantsOrg, DisplayPersonHead, ReasonHead , @jetzt, @jetzt, @jetzt, @XUser, @XUser , DecisionType, 1, dbo.QBM_FCVElementToObjectKey1('pwodecisionHistory'105, 'UID_PWODecisionHistory', x.UID_PWODecisionHistory) , OrderState, UID_ITShopOrgFinal from ( select Newid() as UID_PWODecisionHistory, h.UID_PersonWantsOrg106, h.Description as DisplayPersonHead, h.Description as ReasonHead , h.DecisionType, h.OrderState, h.UID_ITShopOrgFinal from @HistorySammler h ) as x  Declare107 @ElementSatetement QBM_YCursorBuffer insert into @ElementSatetement (ContentFull, UID1) select distinct mo.Statements, mo.GenProcID from @PwoToMove mo108 where mo.Statements > ' ' exec QBM_PExecuteSQLMulti @ElementSatetement, 1, @@PROCID  declare @GenProcIDs QBM_YCursorBuffer insert into @GenProcIDs (UID1109) select distinct mo.GenProcID from @PwoToMove mo select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast110 = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @GenProcIDImZyklus = bu.UID1 from @GenProcIDs bu where bu.ElementIndex = @ElementIndex111 exec QBM_PSessionContextSet 'GenProcID', @GenProcIDImZyklus if exists (select top 1 1 from @PwoToMove mo where mo.ExecPersoninPRDel = 1 and mo.GenProcID112 = @GenProcIDImZyklus ) begin update PersonInBaseTree set XOrigin = XOrigin & @QBM_BitPatternXOrigin_Direct_inv   , XDateUpdated = @jetzt , XUserUpdated113 = @XUser from PersonInBaseTree pio join @PwoToMove mo on pio.UID_Person = mo.UID_PersonOrdered_Old and pio.UID_Org = mo.uid_OrgPR_Old where mo.ExecPersoninPRDel114 = 1 and mo.GenProcID = @GenProcIDImZyklus delete PersonInBaseTree from PersonInBaseTree pio join @PwoToMove mo on pio.UID_Person = mo.UID_PersonOrdered_Old115 and pio.UID_Org = mo.uid_OrgPR_Old where mo.ExecPersoninPRDel = 1 and mo.GenProcID = @GenProcIDImZyklus end  if exists (select top 1 1 from @PwoToMove116 mo where mo.ExecPersonInPRIns = 1 and mo.GenProcID = @GenProcIDImZyklus ) begin insert into PersonInBaseTree(UID_Org, UID_Person, XDateInserted, XDateUpdated117, XObjectKey , XUserInserted, XUserUpdated , XOrigin ) select distinct mo.UID_OrgPR_New, mo.UID_PersonOrdered_New, @jetzt , @jetzt , dbo.QBM_FCVElementToObjectKey2118('PersonInITShopOrg', 'UID_ITShopOrg', mo.UID_OrgPR_New, 'UID_Person', mo.UID_PersonOrdered_New) , @XUser, @XUser , @QBM_BitPatternXOrigin_Direct from 119@PwoToMove mo where mo.ExecPersonInPRIns = 1  and mo.GenProcID = @GenProcIDImZyklus and not exists (select top 1 1 from PersonInBaseTree pio where pio.UID_Org120 = mo.UID_OrgPR_New and pio.UID_Person = mo.UID_PersonOrdered_New ) end  if exists (select top 1 1 from @PwoToMove mo where mo.ExecPWOUpdate = 1 and mo.GenProcID121 = @GenProcIDImZyklus ) begin update personwantsorg set uid_org = mo.uid_orgPR_new, uid_orgParent = mo.UID_OrgBO_New, uid_OrgParentOfParent = mo.UID_OrgSH_New122 , UID_ITShopOrgFinal = mo.UID_OrgPR_New , DisplayOrgParent = mo.Ident_OrgBO_New , DisplayOrgParentOfParent = mo.Ident_OrgSH_New , DateHead = @jetzt , 123ReasonHead = 'ChangeBoard' , XDateUpdated = @jetzt , XUserUpdated = @XUser , UID_QERWorkingMethod = mo.UID_QERWorkingMethod , UID_PersonOrdered = mo.UID_PersonOrdered_New124  from @PwoToMove mo join PersonWantsOrg pwo on mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg where mo.ExecPWOUpdate = 1 and mo.GenProcID = @GenProcIDImZyklus125 end  if exists (select top 1 1 from @PwoToMove mo where mo.ExecPWOUpdate = 2 and mo.GenProcID = @GenProcIDImZyklus ) begin update personwantsorg set decisionlevel126 = 0 , UID_PWOState = null , UID_PersonHead = null , DisplayPersonHead = null , DateHead = @jetzt , ReasonHead = 'ChangeBoard' , XDateUpdated = @jetzt 127, XUserUpdated = @XUser , UID_Org = mo.UID_OrgPR_New , UID_OrgParent = mo.UID_OrgBO_New , UID_OrgParentOfParent = mo.UID_OrgSH_New , UID_ITShopOrgFinal128 = mo.UID_OrgPR_New , DisplayOrgParent = mo.Ident_OrgBO_New , DisplayOrgParentOfParent = mo.Ident_OrgSH_New , UID_QERWorkingMethod = mo.UID_QERWorkingMethod129 , UID_PersonOrdered = mo.UID_PersonOrdered_New from @PwoToMove mo join PersonWantsOrg pwo on mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg where mo.ExecPWOUpdate130 = 2 and mo.GenProcID = @GenProcIDImZyklus delete PWOHelperPWO from @PwoToMove mo join PWOHelperPWO h on mo.UID_PersonWantsOrg = h.UID_PersonWantsOrg where131 mo.ExecPWOUpdate = 2 and mo.GenProcID = @GenProcIDImZyklus end   if exists (select top 1 1 from @PwoToMove mo where mo.execPersonInsertedNull = 1 and 132mo.GenProcID = @GenProcIDImZyklus ) begin update PersonWantsOrg set UID_PersonInserted = null  , XDateUpdated = @jetzt , XUserUpdated = @XUser from @PwoToMove133 mo join PersonWantsOrg pwo on mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg where mo.execPersonInsertedNull = 1 end  if exists (select top 1 1 from @PwoToMove134 mo where mo.execPersonOrderedNull = 1 and mo.GenProcID = @GenProcIDImZyklus ) begin update PersonWantsOrg set UID_PersonOrdered = null  , XDateUpdated135 = @jetzt , XUserUpdated = @XUser from @PwoToMove mo join PersonWantsOrg pwo on mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg where mo.execPersonOrderedNull136 = 1 end   if exists (select top 1 1 from @PwoToMove mo where mo.ExecCallMethodAbort = 1 and mo.GenProcID = @GenProcIDImZyklus ) begin          insert 137into @AdditionalObjectKeysAffected (Parameter1) select b.XObjectKey  from @PwoToMove mo join PersonWantsOrg pwo on mo.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg138 join Basetree b on b.UID_Org = pwo.UID_Org where mo.ExecCallMethodAbort = 1 and mo.GenProcID = @GenProcIDImZyklus  delete  @pwoToAbort  insert into @pwoToAbort139 (Parameter1) select mo.XObjectKey from @PwoToMove mo where mo.ExecCallMethodAbort = 1 and mo.GenProcID = @GenProcIDImZyklus if @@ROWCOUNT > 0 begin exec140 QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg', @pwoToAbort,1, 'Abort' , @GenProcIDImZyklus , @AdditionalObjectKeysAffected = @AdditionalObjectKeysAffected141 , @param1 = '#LDS#Product no longer available.|' , @Retries = 3 , @checkForExisting = 1 , @WhereClauseAdditional = @AbortWhereAddOn end end   if exists142 (select top 1 1 from @PwoToMove mo where mo.ExecCallMethodAbort = 2 and mo.GenProcID = @GenProcIDImZyklus ) begin delete  @pwoToAbort  insert into @pwoToAbort143 (Parameter1) select mo.XObjectKey from @PwoToMove mo where mo.ExecCallMethodAbort = 2 and mo.GenProcID = @GenProcIDImZyklus if @@ROWCOUNT > 0 begin exec144 QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg', @pwoToAbort,1, 'Abort' , @GenProcIDImZyklus , @AdditionalObjectKeysAffected = DEFAULT , @param1 = '#LDS#Assignment order will be switched to a direct assignment.|'145 , @Retries = 3 , @checkForExisting = 1 , @WhereClauseAdditional = @AbortWhereAddOn end end   if exists (select top 1 1 from @PwoToMove mo where mo.ExecCallMethodAbort146 = 3 and mo.GenProcID = @GenProcIDImZyklus ) begin delete  @pwoToAbort  insert into @pwoToAbort (Parameter1) select mo.XObjectKey from @PwoToMove mo where147 mo.ExecCallMethodAbort = 3 and mo.GenProcID = @GenProcIDImZyklus if @@ROWCOUNT > 0 begin exec QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg', @pwoToAbort148,1, 'Abort' , @GenProcIDImZyklus , @AdditionalObjectKeysAffected = DEFAULT , @param1 = '#LDS#Assignment order will be aborted because a direct assignment is not allowed.|'149 , @Retries = 3 , @checkForExisting = 1 , @WhereClauseAdditional = @AbortWhereAddOn end end  select @ElementIndex += 1 end    insert into @PWOsToFill(Parameter1150, Parameter2, Parameter3, HasContentFull) select mo.UID_PersonWantsOrg, mo.GenProcID, '0', 0 from @PwoToMove mo where mo.ExecPWOUpdate = 2 exec QER_PITShopHelperFill_bulk151 @PWOsToFill  declare @GenProcIDsAbort QBM_YCursorBuffer insert into @GenProcIDsAbort (UID1) select distinct mo.GenProcID from @PwoToMove mo where mo.ExecCallMethod_mdSB152 = 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast153 begin select top 1 @GenProcIDImZyklus = bu.UID1 from @GenProcIDsAbort bu where bu.ElementIndex = @ElementIndex delete  @pwoToAbort  insert into @pwoToAbort154 (Parameter1) select mo.XObjectKey from @PwoToMove mo where mo.ExecCallMethod_mdSB = 1 and mo.GenProcID = @GenProcIDImZyklus if @@ROWCOUNT > 0 begin exec155 QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg', @pwoToAbort,1, 'MakeDecision' , @GenProcIDImZyklus , @AdditionalObjectKeysAffected = DEFAULT , @param1156 = 'sa' , @param2 = 'True' , @param3 = 'SB' , @Retries = 3 , @checkForExisting = 1 , @WhereClauseAdditional = @AbortWhereAddOn end select @ElementIndex157 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende: exec QBM_PSessionContextSet 'GenProcID'158, @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 159