dbo.QBM_PDialogProcessShrink_fill

1CREATE PROCEDURE QBM_PDialogProcessShrink_fill(2  @RepairSubstituteOnly BIT = 03)4AS5BEGIN6  DECLARE @info nvarchar(64)7  DECLARE @datum datetime8  SELECT @datum = GetUTCDate()9  SET XACT_ABORT OFF10  BEGIN TRY11    IF EXISTS(12      SELECT TOP 1 113      FROM DialogProcessSubstitute x14      WHERE15        NOT EXISTS(16      SELECT TOP 1 117      FROM DialogProcess p18      WHERE19        p.GenProcID = x.GenProcIDOrigin))20    BEGIN21      SELECT @info = N 'DialogProcessSubstitute'22      INSERT INTO dialogprocess(GenProcID,23      BasisObjectType,24      ObjectKey,25      ProcessState,26      XDateInserted,27      XDateUpdated,28      XUserInserted,29      XUserUpdated,30      CustomComment,31      XTouched,32      DisplayName)33      SELECT34        GenProcID,35        BasisObjectType,36        ObjectKey,37        ProcessState,38        XDateInserted,39        XDateUpdated,40        XUserInserted,41        XUserUpdated,42        CustomComment,43        XTouched,44        DisplayName45      FROM(46      SELECT47        x.GenProcIDOrigin AS GenProcID,48        N 'unknown Object' BasisObjectType,49        NULL AS ObjectKey,50        'E' AS ProcessState,51        @datum AS XDateInserted,52        @datum AS XDateUpdated,53        'GenProcIDRepair' AS XUserInserted,54        'GenProcIDRepair' AS XUserUpdated,55        N 'Inserted from ' + @info AS CustomComment,56        '' AS XTouched,57        N 'Inserted from ' + @info AS DisplayName58      FROM DialogProcessSubstitute x59      LEFT60      OUTER61      JOIN Dialogprocess p62        ON x.GenProcIDOrigin = p.GenProcID63      WHERE64        p.GenProcID IS NULL65      GROUP BY x.GenProcIDOrigin) AS z66      WHERE67        NOT EXISTS(68      SELECT TOP 1 169      FROM DialogProcess y70      WHERE71        y.GenProcID = z.GenProcID)72    END73    IF EXISTS(74      SELECT TOP 1 175      FROM DialogProcessSubstitute x76      WHERE77        NOT EXISTS(78      SELECT TOP 1 179      FROM DialogProcess p80      WHERE81        p.GenProcID = x.GenProcIDNew))82    BEGIN83      SELECT @info = N 'DialogProcessSubstitute'84      INSERT INTO dialogprocess(GenProcID,85      BasisObjectType,86      ObjectKey,87      ProcessState,88      XDateInserted,89      XDateUpdated,90      XUserInserted,91      XUserUpdated,92      CustomComment,93      XTouched,94      DisplayName)95      SELECT96        GenProcID,97        BasisObjectType,98        ObjectKey,99        ProcessState,100        XDateInserted,101        XDateUpdated,102        XUserInserted,103        XUserUpdated,104        CustomComment,105        XTouched,106        DisplayName107      FROM(108      SELECT109        x.GenProcIDNew AS GenProcID,110        N 'unknown Object' BasisObjectType,111        NULL AS ObjectKey,112        'E' AS ProcessState,113        @datum AS XDateInserted,114        @datum AS XDateUpdated,115        'GenProcIDRepair' AS XUserInserted,116        'GenProcIDRepair' AS XUserUpdated,117        N 'Inserted from ' + @info AS CustomComment,118        '' AS XTouched,119        N 'Inserted from ' + @info AS DisplayName120      FROM DialogProcessSubstitute x121      LEFT122      OUTER123      JOIN Dialogprocess p124        ON x.GenProcIDNew = p.GenProcID125      WHERE126        p.GenProcID IS NULL127      GROUP BY x.GenProcIDnew) AS z128      WHERE129        NOT EXISTS(130      SELECT TOP 1 1131      FROM dialogprocess y132      WHERE133        y.GenProcID = z.GenProcID)134    END135    IF @RepairSubstituteOnly = 1136    BEGIN137      GOTO endLabel138    END139    IF EXISTS(140      SELECT TOP 1 1141      FROM DialogWatchOperation x142      WHERE143        NOT EXISTS(144      SELECT TOP 1 1145      FROM DialogProcess p146      WHERE147        p.GenProcID = x.GenProcID))148    BEGIN149      SELECT @info = N 'DialogWatchOperation'150      INSERT INTO dialogprocess(GenProcID,151      BasisObjectType,152      ObjectKey,153      ProcessState,154      XDateInserted,155      XDateUpdated,156      XUserInserted,157      XUserUpdated,158      CustomComment,159      XTouched,160      DisplayName)161      SELECT162        GenProcID,163      CASE164        WHEN z.BasisObjectType = '<unknown Object>' AND z.ObjectKey > ' ' THEN165      dbo.QBM_FCVObjectkeyToElement('TableName',166      z.ObjectKey)167      ELSE z.BasisObjectType168      END AS BasisObjectType,169      ObjectKey,170      ProcessState,171      XDateInserted,172      XDateUpdated,173      XUserInserted,174      XUserUpdated,175      CustomComment,176      XTouched,177      DisplayName178      FROM(179      SELECT180        x.GenProcID,181        '<unknown Object>' AS BasisObjectType,182        max(x.ObjectKeyOfRow) AS ObjectKey,183        'E' AS ProcessState,184        min(x.OperationDate) AS XDateInserted,185        min(x.OperationDate) AS XDateUpdated,186        max(x.OperationUser) AS XUserInserted,187        max(x.OperationUser) AS XUserUpdated,188        N 'Inserted from ' + @info AS CustomComment,189        '' AS XTouched,190        max(x.DisplayValue) AS DisplayName191      FROM DialogWatchOperation x192      LEFT193      OUTER194      JOIN Dialogprocess p195        ON x.GenProcID = p.GenProcID196      WHERE197        p.GenProcID IS NULL198      GROUP BY x.GenProcID) AS z199      WHERE200        NOT EXISTS(201      SELECT TOP 1 1202      FROM dialogprocess y203      WHERE204        y.GenProcID = z.GenProcID)205    END206    IF EXISTS(207      SELECT TOP 1 1208      FROM Dialogprocesschain x209      WHERE210        NOT EXISTS(211      SELECT TOP 1 1212      FROM DialogProcess p213      WHERE214        p.GenProcID = x.GenProcID))215    BEGIN216      SELECT @info = N 'Dialogprocesschain'217      INSERT INTO dialogprocess(GenProcID,218      BasisObjectType,219      ObjectKey,220      ProcessState,221      XDateInserted,222      XDateUpdated,223      XUserInserted,224      XUserUpdated,225      CustomComment,226      XTouched,227      DisplayName)228      SELECT229        GenProcID,230        BasisObjectType,231        ObjectKey,232        ProcessState,233        XDateInserted,234        XDateUpdated,235        XUserInserted,236        XUserUpdated,237        CustomComment,238        XTouched,239        DisplayName240      FROM(241      SELECT242        x.GenProcID,243        max(x.BasisObjectType) AS BasisObjectType,244        max(x.ObjectKey) AS ObjectKey,245        'E' AS ProcessState,246        max(x.XDateInserted) AS XDateInserted,247        max(x.XDateUpdated) AS XDateUpdated,248        max(x.XUserInserted) AS XUserInserted,249        max(x.XUserUpdated) AS XUserUpdated,250        max(x.JobChainName) AS CustomComment,251        '' AS XTouched,252        max(x.DisplayName) AS DisplayName253      FROM Dialogprocesschain x254      LEFT255      OUTER256      JOIN Dialogprocess p257        ON x.GenProcID = p.GenProcID258      WHERE259        p.GenProcID IS NULL260      GROUP BY x.GenProcID) AS z261      WHERE262        NOT EXISTS(263      SELECT TOP 1 1264      FROM dialogprocess y265      WHERE266        y.GenProcID = z.GenProcID)267    END268    IF EXISTS(269      SELECT TOP 1 1270      FROM Jobhistory x271    LEFT272    OUTER273      JOIN Dialogprocess p274        ON x.GenProcID = p.GenProcID275      WHERE276        p.GenProcID IS NULL)277    BEGIN278      SELECT @info = N 'JobHistory'279      INSERT INTO dialogprocess(GenProcID,280      BasisObjectType,281      ObjectKey,282      ProcessState,283      XDateInserted,284      XDateUpdated,285      XUserInserted,286      XUserUpdated,287      CustomComment,288      XTouched,289      DisplayName)290      SELECT291        GenProcID,292        BasisObjectType,293        ObjectKey,294        ProcessState,295        XDateInserted,296        XDateUpdated,297        XUserInserted,298        XUserUpdated,299        CustomComment,300        XTouched,301        DisplayName302      FROM(303      SELECT304        x.GenProcID,305        max(CASE306        WHEN x.BasisObjectKey > ' ' THEN307        dbo.QBM_FCVObjectkeyToElement('TableName', x.BasisObjectKey)308      ELSE '<unknown Object>'309      END) AS BasisObjectType,310      max(CASE311      WHEN x.BasisObjectKey > ' ' THEN312      x.BasisObjectKey313      ELSE NULL314      END) AS ObjectKey,315      'E' AS ProcessState,316      max(x.XDateInserted) AS XDateInserted,317      max(x.XDateUpdated) AS XDateUpdated,318      max(x.XUserInserted) AS XUserInserted,319      max(x.XUserUpdated) AS XUserUpdated,320      max(JobChainName) AS CustomComment,321      '' AS XTouched,322      max(JobChainName) AS DisplayName323      FROM Jobhistory x324      LEFT325      OUTER326      JOIN Dialogprocess p327        ON x.GenProcID = p.GenProcID328      WHERE329        p.GenProcID IS NULL330      GROUP BY x.GenProcID) AS z331      WHERE332        NOT EXISTS(333      SELECT TOP 1 1334      FROM dialogprocess y335      WHERE336        y.GenProcID = z.GenProcID)337    END338    UPDATE dialogprocess339    SET xdateupdated = GetUTCDate()340    WHERE341      xdateupdated IS NULL342    UPDATE dialogprocess343    SET xdateinserted = GetUTCDate()344    WHERE345      xdateinserted IS NULL346  END TRY347  BEGIN CATCH348    EXEC QBM_PSessionErrorAdd DEFAULT349    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()350    RAISERROR(@Rethrow,351    18,352    1)353      WITH NOWAIT354  END CATCH355  endLabel:356  RETURN357END