dbo.QER_TUPerson

1CREATE trigger QER_TUPerson2  ON Person FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @Parameter nvarchar(1000)7  DECLARE @Elements QBM_YParameterList8  DECLARE @UID_Person varchar(38)9  DECLARE @JournalMessage nvarchar(1000)10  DECLARE @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',11  0)12  DECLARE @QBM_BitPatternXMarkedForDel_OutStanding int = dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',13  0)14  DECLARE @PWOTaskToUse varchar(38) = dbo.QER_FGIPWOTaskToUse()15  DECLARE @AbortWhereAddOn nvarchar(1000) = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'16  DECLARE @PWOToAbort QBM_YParameterList17  DECLARE @DebugLevel varchar(1) = 'I'18  BEGIN TRY19    IF EXISTS(20      SELECT TOP 1 121      FROM inserted)22    GOTO start23    IF EXISTS(24      SELECT TOP 1 125      FROM deleted)26    GOTO start27    RETURN start:28    DECLARE @GenProcID varchar(38)29    SELECT @GenProcID = dbo.QBM_FGISessionContext('')30    IF31    UPDATE(UID_PersonMasterIdentity)32    BEGIN33      IF @PWOTaskToUse > ' '34      BEGIN35        EXEC QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate',36          @PWOTaskToUse,37        '',38          @GenProcID39      END40      IF EXISTS(41        SELECT TOP 1 142        FROM inserted i43        WHERE44          i.UID_PersonMasterIdentity > ' ')45      BEGIN46        INSERT INTO @PWOToAbort(Parameter1)47        SELECT x.XObjectKey48        FROM(49        SELECT pwo.XObjectKey50        FROM inserted p51        JOIN QER_VPersonsAreMe me52          ON p.UID_Person = me.UID_PersonOrigin53        JOIN QERUniversalSubstitute su54          ON su.UID_PersonSender = me.UID_PersonOrigin AND su.UID_PersonReceiver = me.UID_PersonAlsoMe55        JOIN PersonWantsOrg pwo56          ON su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg57        UNION58        SELECT pwo.XObjectKey59        FROM inserted p60        JOIN QER_VPersonsAreMe me61          ON p.UID_Person = me.UID_PersonOrigin62        JOIN QERUniversalSubstitute su63          ON su.UID_PersonReceiver = me.UID_PersonOrigin AND su.UID_PersonSender = me.UID_PersonAlsoMe64        JOIN PersonWantsOrg pwo65          ON su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg66        UNION67        SELECT pwo.XObjectKey68        FROM inserted p69        JOIN QER_VPersonsAreMe me70          ON p.UID_PersonMasterIdentity = me.UID_PersonOrigin71        JOIN QERUniversalSubstitute su72          ON su.UID_PersonSender = me.UID_PersonOrigin AND su.UID_PersonReceiver = me.UID_PersonAlsoMe73        JOIN PersonWantsOrg pwo74          ON su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg75        UNION76        SELECT pwo.XObjectKey77        FROM inserted p78        JOIN QER_VPersonsAreMe me79          ON p.UID_PersonMasterIdentity = me.UID_PersonOrigin80        JOIN QERUniversalSubstitute su81          ON su.UID_PersonReceiver = me.UID_PersonOrigin AND su.UID_PersonSender = me.UID_PersonAlsoMe82        JOIN PersonWantsOrg pwo83          ON su.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg) AS x84        IF @@ROWCOUNT > 085        BEGIN86          EXEC QBM_PJobCreate_HOCallMethod_L 'PersonWantsOrg',87            @pwoToAbort,88          1,89          'Abort',90            @GenProcID,91            @AdditionalObjectKeysAffected = DEFAULT,92            @param1 = '#LDS#Substitution no longer allowed because of definition of subidentities.|',93            @Retries = 3,94            @checkForExisting = 1,95            @WhereClauseAdditional = @AbortWhereAddOn96        END97      END98      DECLARE @DBQueueElements_updateMasterIdentity QBM_YDBQueueRaw99      INSERT INTO @DBQueueElements_updateMasterIdentity(object,100      subobject,101      genprocid)102      SELECT103        x.uid,104        NULL,105        @GenProcID106      FROM(107      SELECT UID_PersonMasterIdentity AS uid108      FROM inserted109      WHERE110        UID_PersonMasterIdentity > ' '111      UNION112      SELECT UID_PersonMasterIdentity AS uid113      FROM deleted114      WHERE115        UID_PersonMasterIdentity > ' '116      UNION117      SELECT p.UID_Person AS uid118      FROM deleted d119      JOIN Person p120        ON d.UID_Person = p.UID_Person121      WHERE122        isnull(d.UID_PersonMasterIdentity, '') <> isnull(p.UID_PersonMasterIdentity, '')) AS x123      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadPerson',124        @DBQueueElements_updateMasterIdentity125    END126    IF127    UPDATE(uid_personHead)128    BEGIN129      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw130      INSERT INTO @DBQueueElements_01(object,131      subobject,132      genprocid)133      SELECT134        x.uid,135        NULL,136        @GenProcID137      FROM(138      SELECT uid_personHead AS uid139      FROM inserted140      WHERE141        uid_personHead > ' '142      UNION143      SELECT uid_personHead AS uid144      FROM deleted145      WHERE146        uid_personHead > ' '147      UNION148      SELECT us.UID_PersonReceiver AS uid149      FROM inserted i150      JOIN QERUniversalSubstitute us151        ON i.UID_PersonHead = us.UID_PersonSender152      WHERE153        i.UID_PersonHead > ' ' AND us.IsCurrentlyActive = 1 AND us.UseForHeadPerson = 1154      UNION155      SELECT us.UID_PersonReceiver AS uid156      FROM deleted d157      JOIN QERUniversalSubstitute us158        ON d.UID_PersonHead = us.UID_PersonSender159      WHERE160        d.UID_PersonHead > ' ' AND us.IsCurrentlyActive = 1 AND us.UseForHeadPerson = 1) AS x161      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadPerson',162        @DBQueueElements_01163    END164    IF165    UPDATE(UID_PersonMasterIdentity)166    BEGIN167      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw168      INSERT INTO @DBQueueElements_02(object,169      subobject,170      genprocid)171      SELECT172        x.uid,173        NULL,174        @GenProcID175      FROM(176      SELECT UID_PersonMasterIdentity AS uid177      FROM inserted178      WHERE179        UID_PersonMasterIdentity > ' '180      UNION181      SELECT UID_PersonMasterIdentity AS uid182      FROM deleted183      WHERE184        UID_PersonMasterIdentity > ' ') AS x185      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-PersonHasObject',186        @DBQueueElements_02187    END188    IF189    UPDATE(IsInActive) OR190    UPDATE(IsExternal) OR191    UPDATE(UID_PersonMasterIdentity)192    BEGIN193      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw194      INSERT INTO @DBQueueElements_03(object,195      subobject,196      genprocid)197      SELECT198        x.uid,199        y.UID_DynamicGroup,200        @GenProcID201      FROM(202      SELECT p.uid_person AS uid203      FROM deleted d204      JOIN Person p205        ON d.UID_Person = p.UID_Person206      WHERE207        (isnull(d.IsInActive, 0) <> isnull(p.IsInActive, 0) OR isnull(d.IsExternal, 0) <> isnull(p.IsExternal,208      0) OR isnull(d.UID_PersonMasterIdentity, '') <> isnull(p.UID_PersonMasterIdentity, ''))) AS x,209      (210      SELECT g.UID_DynamicGroup211      FROM DynamicGroup g212      WHERE213        dbo.QBM_FCVGUIDToModuleOwner(g.UID_DynamicGroup) <> 'CCC' AND g.UID_DialogTableObjectClass = 'QER-T-Person') AS y214      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-DynamicGroupSingleBasic',215        @DBQueueElements_03216    END217    IF dbo.QBM_FGIColumnUpdatedOthers('Person',218    'XTouched',219    columns_updated()) = 1 AND(dbo.QBM_FGIColumnUpdatedOthers('Person', 'XMarkedForDeletion', columns_updated()) = 1 OR EXISTS(220    SELECT TOP 1 1221    FROM deleted i222    JOIN person p223      ON i.uid_person = p.uid_person224    WHERE225      sign(i.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay) <> sign(p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay)))226    BEGIN227      DECLARE @maxAnzahl int228      SELECT @maxAnzahl = 100229      IF 100 <(230        SELECT count(*)231      FROM inserted) +(232      SELECT count(*)233      FROM deleted)234      BEGIN235        SELECT TOP 1 @maxAnzahl =((Count(*)) /10 + 1)236        FROM person237      END238      ELSE239      BEGIN240        SELECT @maxAnzahl = 1073741824241      END242    END243    IF244    UPDATE(IsInActive) OR245    UPDATE(IsTemporaryDeactivated) OR246    UPDATE(XMarkedForDeletion) OR247    UPDATE(IsSecurityIncident) OR248    UPDATE(IsNoInherite)249    BEGIN250      DECLARE @DBQueueElements_05 QBM_YDBQueueRaw251      INSERT INTO @DBQueueElements_05(object,252      subobject,253      genprocid)254      SELECT255        x.uid,256        NULL,257        @GenProcID258      FROM(259      SELECT p.uid_person AS uid260      FROM person p261      JOIN deleted d262        ON p.uid_person = d.uid_person263      WHERE264        (isnull(p.IsInActive, 0) <> isnull(d.IsInActive, 0) OR isnull(p.IsTemporaryDeactivated, 0) <> isnull(d.IsTemporaryDeactivated,265      0) OR isnull(p.IsSecurityIncident, 0) <> isnull(d.IsSecurityIncident, 0) OR isnull(p.IsNoInherite, 0) <> isnull(d.IsNoInherite,266      0) OR sign(p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay) <> sign(d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay))) AS267      x268      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson',269        @DBQueueElements_05270    END271    IF272    UPDATE(XMarkedForDeletion)273    BEGIN274      DECLARE @DBQueueElements_06 QBM_YDBQueueRaw275      INSERT INTO @DBQueueElements_06(object,276      subobject,277      genprocid)278      SELECT279        x.uid,280        NULL,281        @GenProcID282      FROM(283      SELECT p.uid_person AS uid284      FROM person p285      JOIN deleted d286        ON p.uid_person = d.uid_person287      WHERE288        p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding = 0 AND d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding289      > 0290      UNION291      SELECT us.UID_PersonReceiver AS uid292      FROM Person p293      JOIN deleted d294        ON p.UID_Person = d.UID_Person295      JOIN QERUniversalSubstitute us296        ON p.UID_Person = us.UID_PersonSender297      WHERE298        p.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding = 0 AND d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding299      > 0 AND us.IsCurrentlyActive = 1 AND(us.UseForHeadPerson = 1 OR us.UseForHeadOrg = 1)) AS x300      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson',301        @DBQueueElements_06302      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadPerson',303        @DBQueueElements_06304      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperPersonOrg',305        @DBQueueElements_06306      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadOrg',307        @DBQueueElements_06308    END309    IF310    UPDATE(uid_department) OR311    UPDATE(uid_ProfitCenter) OR312    UPDATE(uid_locality)313    BEGIN314      DECLARE @DBQueueElements_07 QBM_YDBQueueRaw315      INSERT INTO @DBQueueElements_07(object,316      subobject,317      genprocid)318      SELECT319        x.uid,320        NULL,321        @GenProcID322      FROM(323      SELECT p.uid_person AS uid324      FROM person p325      JOIN deleted d326        ON p.uid_person = d.uid_person327      WHERE328        (isnull(p.uid_department, '') <> isnull(d.uid_department, '') OR isnull(p.uid_ProfitCenter, '') <> isnull(d.uid_ProfitCenter,329      '') OR isnull(p.uid_locality, '') <> isnull(d.uid_locality, ''))) AS x330      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-HelperPersonOrg',331        @DBQueueElements_07332    END333    IF334    UPDATE(Passcode)335    BEGIN336      DELETE @Elements337      INSERT INTO @Elements(Parameter1,338      ContentFull)339      SELECT340        d.UID_Person,341        isnull(p.InternalName,342        '<no InternalName>')343      FROM deleted d344      JOIN Person p345        ON d.UID_Person = p.UID_Person346      WHERE347        d.Passcode IS NULL AND p.Passcode > ' '348      WHILE EXISTS(349      SELECT TOP 1 1350      FROM @Elements)351      BEGIN352        SELECT353          TOP 1 @UID_Person = e.Parameter1,354          @JournalMessage = CONCAT('#LDS#Passcode issued for identity: {0}, UID: {1}.|',355          e.ContentFull,356          '|',357          e.Parameter1,358          '|')359        FROM @Elements e360        EXEC QBM_PJournal @JournalMessage,361          @@ProcID,362        'I',363          @DebugLevel364        DELETE @elements365        WHERE366          Parameter1 = @UID_Person367      END368    END369    pwotest:370    IF371    UPDATE(uid_department)372    BEGIN373      DECLARE @RecalcParameter_01 QBM_YCursorBuffer374      INSERT INTO @RecalcParameter_01(UID1,375      ObjectKey1,376      Ident1)377      SELECT378        'QER-PWODecisionRule-DM',379        i.XobjectKey,380        'I'381      FROM inserted i382      UNION all383      SELECT384        'QER-PWODecisionRule-ID',385        i.XobjectKey,386        'I'387      FROM inserted i388      UNION all389      SELECT390        'QER-PWODecisionRule-RD',391        i.XobjectKey,392        'I'393      FROM inserted i394      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_01,395        @GenprocID396    END397    IF398    UPDATE(uid_ProfitCenter)399    BEGIN400      DECLARE @RecalcParameter_02 QBM_YCursorBuffer401      INSERT INTO @RecalcParameter_02(UID1,402      ObjectKey1,403      Ident1)404      SELECT405        'QER-PWODecisionRule-IP',406        i.XobjectKey,407        'I'408      FROM inserted i409      UNION all410      SELECT411        'QER-PWODecisionRule-PM',412        i.XobjectKey,413        'I'414      FROM inserted i415      UNION all416      SELECT417        'QER-PWODecisionRule-RP',418        i.XobjectKey,419        'I'420      FROM inserted i421      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_02,422        @GenprocID423    END424    IF425    UPDATE(uid_Locality)426    BEGIN427      DECLARE @RecalcParameter_03 QBM_YCursorBuffer428      INSERT INTO @RecalcParameter_03(UID1,429      ObjectKey1,430      Ident1)431      SELECT432        'QER-PWODecisionRule-IL',433        i.XobjectKey,434        'I'435      FROM inserted i436      UNION all437      SELECT438        'QER-PWODecisionRule-RL',439        i.XobjectKey,440        'I'441      FROM inserted i442      EXEC QER_PITShopHelperFill_Recalc @RecalcParameter_03,443        @GenprocID444    END445    IF446    UPDATE(IsInActive)447    BEGIN448      IF dbo.QBM_FGIConfigparmValue('QER\ITSHop\AutoCloseInactivePerson') > ' '449      BEGIN450        INSERT INTO @Elements(Parameter1)451        SELECT pio.XObjectKey452        FROM Person p453        JOIN deleted d454          ON p.UID_Person = d.UID_Person AND p.IsInActive = 1 AND d.IsInActive = 0455        JOIN PersonInBaseTree pio456          ON pio.UID_Person = p.UID_Person457        JOIN BaseTree b458          ON pio.UID_Org = b.UID_Org459        LEFT460        OUTER461        JOIN DynamicGroup dg462          ON b.XObjectKey = dg.ObjectKeyBaseTree463        WHERE464          b.ITShopInfo = 'CU' AND b.UID_OrgRoot = 'QER-V-ITShopOrg' AND dg.UID_DynamicGroup IS NULL465        EXEC QBM_PJobCreate_HODelete_L 'PersonInITShopOrg',466          @elements,467          @GenProcID,468        DEFAULT469      END470      DECLARE @DBQueueElements_08 QBM_YDBQueueRaw471      INSERT INTO @DBQueueElements_08(object,472      subobject,473      genprocid)474      SELECT475        x.uid,476        NULL,477        @GenProcID478      FROM(479      SELECT480        DISTINCT ph.UID_PersonWantsOrg AS uid481      FROM deleted d482      JOIN Person p483        ON d.UID_Person = p.UID_Person484      JOIN PWOHelperPWO ph485        ON ph.UID_PersonHead = p.UID_Person486      WHERE487        d.IsInActive = 0 AND p.IsInActive = 1) AS x488      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO',489        @DBQueueElements_08490    END491  END TRY492  BEGIN CATCH493    EXEC QBM_PSessionErrorAdd DEFAULT494    RAISERROR('',495    18,496    1)497      WITH NOWAIT498  END CATCH499  ende:500  RETURN501END