Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures....: [ObjectsUpdate_ProcessStatus]'
Print ' Creation Date.: 08.04.2013'
Print ' Last Update...: 05.11.2015'
Print ' Autor.........: J.Feuerstein'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO


Print '' Print ''
Print '-- [ObjectsUpdate_ProcessStatus] ----------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectsUpdate_ProcessStatus" and type = "P")
Drop Procedure ObjectsUpdate_ProcessStatus
GO

/*
 Lastet die Daten der Process verarbeitungen in die Datenbank ein
 wichtig bei Struktur anpassungen ist immer die dazugehörigen View zu prüfen !!
*/
CREATE Procedure ObjectsUpdate_ProcessStatus
                    (
                    --- Object Data ---
                    @ObjectDataTableProcStateCol     nvarchar (32) ,     -- Name der Spalte ProcStateCol01..10 ProcStateColMc..Rf in dem der Processtatus der Quelltabelle steht

                    @Objects_tbl_ID                  uniqueidentifier = null ,  -- ID des zu bearbeitenden Datensatzes in Objects_tbl

                    @ObjectName                      nvarchar (32) , -- Name
                    @ObjectFromSiteID                nvarchar (32) , -- von welcher site 01.08.2015 09:33
                    @ObjectCliName                   nvarchar (255) , -- CliName des Objectes Ir Example: @DB/ItemName/Rev  Ds Example: @DB/ItemName/Rev/specification/ObjectName
                    @IRCliName                       nvarchar (255) , -- IR CliName Ir Example: @DB/ItemName/Rev (new:15.02.2012 J.Fes)
                    @ObjectClass                     nvarchar (10) , -- I DS IR

                    @ObjectSpecialEditRule           nvarchar (64) , -- new 30.1.09  see ..\publicenums.vb ObjectSpecialEditRule_enum

                    --- Process Data ---
                    @ProcessJobDataDoAddEdit         bit = null ,                   -- #New:05.11.2015/J.Fes defines if JobProcessData.. is to be process via Insert Update
                    @JobControl_ObjectsProcessData_tbl_ID uniqueidentifier = null , -- Ref auf Datensatz in JobControl_ObjectsProcessData_tbl
                    @ProcessPacketName               nvarchar (64) = null ,
                    @ProcessState                    nvarchar (34) = null ,     -- beinhaltet den Process Status eines Objektes Size geandert auf 34 23.11.2011 J.Fes
                    @ProcessStateExtendetEdit_SQL    nvarchar (max) = null ,    -- new 19.07.2012 J.Fes setzt erw. P.Satus via ProcStateCol01='M', ProcStateCol02='M'
                    @ProcessTimeStart                datetime = null,
                    @ProcessTimeEnd                  datetime = null,
                    @ProcessCMDLine                  nvarchar (255) = null ,
                    @ProcessResultCode               nvarchar (32) = null ,
                    @ProcessResultMsg                nvarchar (450) = null ,
                    @ProcessResultExtendedData       nvarchar (max) = null ,   -- new 08.07.2013 16:39 J.Fes extendet process Data
                    @ProcessResultHasWarning         bit = null ,
                    @ProcessResultLink               nvarchar (450) = null,    -- 26.06.2019/JFes Upsice 255 to 450
                    @ProcessOnComputer               nvarchar (64)  = null,
                    @TaskScheduler_tbl_ID            uniqueidentifier = null , -- ID des referenzierten TaskSheduler Datensatzes in TaskScheduler_tbl
                    @ProcessPrgUsedVer               nvarchar (255) = null, -- #New: 05.04.2013 / J.Fes
                    @ProcessObjectSaveVersion        nvarchar (64) = null ,  -- #New: 05.04.2013 / J.Fes
                    @ProcessProcStateCol             nvarchar (32) = null ,    -- Info: @ObjectDataTableProcStateCol beinhaltet den gleichen Wert wird jedoch aus übersichtsgründen mit verwendet

                    @ObjectSyncTrigger               int = null , -- #New: 20.06.2016 / J.Fes if value not null > it defines to @ObjectSyncTrigger

                    --- Returns ---
                    @ObjectCliName_SQL_r             nvarchar (255) = null Output  ,  -- Cli Return neu 15.02.2012 J.Fes
                    @Objects_tbl_ID_SQL_r            uniqueidentifier = null Output   -- ID  Return neu 15.02.2012 J.Fes
                    )

As
BEGIN

 Declare @DebugPrintOn bit
 set @DebugPrintOn = 0

 DECLARE @ObjectId uniqueidentifier
 DECLARE @ExecuteSQLstr nvarchar(max)
 DECLARE @MyValue nvarchar(max)


 if @DebugPrintOn = 0
 BEGIN
    EXECUTE zzzzzDebugTbl_Empty
 END

 -- wenn @Objects_tbl_ID gegeben ist dann diese suchen lassen
 if @Objects_tbl_ID IS NOT NULL
 BEGIN
    -- pruefen ob Object Datensatz in Objects_tbl vorhanden ist
    select @ObjectId=Id from Objects_tbl where ID = @Objects_tbl_ID
 END

 -- Abschnitt Objects_tbl bearbeiten --------------------------------------------------------------------------------
 if @DebugPrintOn = 1
 BEGIN
    set @MyValue = CONVERT(varchar(128), @Objects_tbl_ID)
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '01-@Objects_tbl_ID:',           @MyValue
    set @MyValue = CONVERT(varchar(128), @ObjectId)
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '02-@ObjectId:' ,                @MyValue
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '03-@ObjectSpecialEditRule:' ,   @ObjectSpecialEditRule
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '04-@ObjectName:' ,              @ObjectName
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '05-@ObjectCliName:' ,           @ObjectCliName
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '06-@ObjectFromSiteID:' ,        @ObjectFromSiteID
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '07-@ObjectClass:' ,             @ObjectClass
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '08-@ProcessJobDataDoAddEdit:',  @ProcessJobDataDoAddEdit
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '09-@ProcessProcStateCol' ,      @ProcessProcStateCol
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '10-@ProcessPrgUsedVer:' ,       @ProcessPrgUsedVer
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '11-@ProcessObjectSaveVersion:', @ProcessObjectSaveVersion
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '12-@ProcessResultHasWarning:',  @ProcessResultHasWarning
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '13-@ProcessResultMsg:',         @ProcessResultMsg
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '14-@ProcessResultMsg:',         @ObjectSyncTrigger
 END


 if @ObjectSpecialEditRule = 'UgMaster_Of_Ir'
 BEGIN
    if @DebugPrintOn = 1
    BEGIN
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , 'zz-ControlInfo:' ,  'in:if @ObjectSpecialEditRule = UgMaster_Of_Ir'
    END
    -- Sondersuchregel anwenden es wird nach dem UGMaster DS der IR gesucht
    set @ObjectId = NULL
    EXECUTE Objects_tbl_Object_GetId_UgMasterDS_of_IR  @ObjectCliName, @ObjectFromSiteID, @ObjectClass,
                                                       @ObjectId Output, @ObjectName Output, @ObjectCliName Output , @ObjectClass Output
 END

 -- #New 15.02.2012/J.Fes
 if @ObjectSpecialEditRule = 'DS_of_IR_viaObjectID'
 BEGIN
    if @DebugPrintOn = 1
    BEGIN
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , 'zz-ControlInfo:' ,  'in:if @ObjectSpecialEditRule = DS_of_IR_viaObjectID'
    END
    -- Sondersuchregel anwenden es wird nach dem UGMaster DS der IR gesucht
    set @ObjectId = NULL
    EXECUTE Objects_tbl_Object_GetId_DS_of_IR_viaObjectID
                    @IRCliName, @ObjectFromSiteID, @ObjectName,
                    @ObjectId Output, @ObjectCliName Output
    if @DebugPrintOn = 1
    BEGIN
      set @MyValue = CONVERT(varchar(128), @ObjectId)
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '14-@ObjectId:' ,  @MyValue
    end
 END


 if @ObjectSpecialEditRule = 'DS_of_IR_viaObjectType_UGMASTER'
 BEGIN
   if @DebugPrintOn = 1
   BEGIN
     EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus', '20-zz-ControlInfo:',
                      'in:if @ObjectSpecialEditRule = DS_of_IR_viaObjectType_UGMASTER'
   END
   -- Sondersuchregel anwenden es wird nach dem UGMaster DS der IR gesucht
   set @ObjectId = NULL
   EXECUTE Objects_tbl_Object_GetId_DS_of_IR_viaObjectType_UGMASTER
                   @IRCliName, @ObjectFromSiteID, @ObjectName,
                   @ObjectId Output, @ObjectCliName Output
  if @DebugPrintOn = 1
  BEGIN
    set @MyValue = CONVERT(varchar(128), @ObjectId)
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '14-@ObjectId:' ,  @MyValue
  END
 END

 -- #New 19.01.2017/J.Fes
 if @ObjectSpecialEditRule = 'ObjectfindBy_FromSiteClassObjectName'
 BEGIN
   if @DebugPrintOn = 1
   BEGIN
     EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus', '20-zz-ControlInfo:',
                      'in:if @ObjectSpecialEditRule = ObjectfindBy_FromSiteClassObjectName'
   END
   -- Sondersuchregel anwenden es wird nach dem UGMaster DS der IR gesucht
   set @ObjectId = NULL
   EXECUTE ObjectfindBy_FromSiteClassObjectName
                   @ObjectFromSiteID, @ObjectClass,  @ObjectName,
                   @ObjectId Output, @ObjectCliName Output
  if @DebugPrintOn = 1
  BEGIN
    set @MyValue = CONVERT(varchar(128), @ObjectId)
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '14-@ObjectId:' ,  @MyValue
  END
 END



 if @ObjectId IS NULL
 BEGIN
    -- Print ('in -2- if @ObjectId IS NULL')
    -- Nach der ID logisch suchen wenn nicht schon gefunden !!
    EXECUTE Objects_tbl_Object_GetId  @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @ObjectId Output
 END

 -- Print ('ermittelte @ObjectId=' + CONVERT(nvarchar(128), @ObjectId) + '')
 if @ObjectId IS NOT NULL
 BEGIN
   -- Object Processstatus updaten Hinweis es muss die passende Colume genommen werden / CodeExample: GUID via Parameter in T-SQL Procedure verwenden !
   set @ExecuteSQLstr = N'UPDATE Objects_tbl SET ' + @ObjectDataTableProcStateCol + '=''' + @ProcessState + ''''
   -- new 19.07.2012 19:23 J.Fes
   if @ProcessStateExtendetEdit_SQL <> '' set @ExecuteSQLstr = @ExecuteSQLstr + ', ' + @ProcessStateExtendetEdit_SQL
   set @ExecuteSQLstr = @ExecuteSQLstr + ' WHERE Objects_tbl.Id=''' + CONVERT(nvarchar(128), @ObjectId) + ''''
   execute sp_executesql @ExecuteSQLstr

   if @DebugPrintOn = 1
    BEGIN
      EXECUTE zzzzzDebugTbl_AddDataSet '21-@ExecuteSQLstr' , '@ExecuteSQLstr:' ,  @ExecuteSQLstr
    END

   if @ObjectSpecialEditRule = 'SetPStateTo_IR_And_DS' and @ObjectClass = 'IR'
   BEGIN
      -- EXECUTE zzzzzDebugTbl_Empty
      -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '10-@ObjectDataTableProcStateCol:' ,  @ObjectDataTableProcStateCol
      -- declare  @ObjectId_tmp nvarchar(128)
      -- set @ObjectId_tmp=CONVERT(nvarchar(128), @ObjectId)
      -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '20-@ObjectId:' ,  @ObjectId_tmp
      set @ExecuteSQLstr = N'UPDATE Objects_tbl SET ' + @ObjectDataTableProcStateCol + '=''' + @ProcessState + '''' +
                            ' WHERE Objects_tbl.Id IN ' +
                            '(SELECT' +
                            '   DS.Id' +
                            ' FROM  dbo.Objects_tbl IR' +
                            '  INNER JOIN dbo.Objects_Ir_Rel_tbl with (nolock) ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)' +
                            '  INNER JOIN dbo.Objects_tbl DS with (nolock) ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)' +
                            '  WHERE IR.Id = ''' + CONVERT(nvarchar(128), @ObjectId) + '''' + ')'
      -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '30-@ExecuteSQLstr:' , @ExecuteSQLstr
      execute sp_executesql @ExecuteSQLstr
   END

   if @ObjectSpecialEditRule = 'SetPStateTo_All_DS_Off_UGMASTER' and @ObjectClass = 'DS'
   BEGIN
       -- neu 20.06.2010 14:47 J.fes
       -- EXECUTE zzzzzDebugTbl_Empty
       -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '10-@ObjectDataTableProcStateCol:' ,  @ObjectDataTableProcStateCol
       -- declare  @ObjectId_tmp nvarchar(64)
       -- set @ObjectId_tmp=CONVERT(nvarchar(128), @ObjectId)
       -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '20-@ObjectId:' ,  @ObjectId_tmp
       -- 23.09.2010 13:20  with (nolock) eingefuegt J.Fes
       -- 27.02.2014 15:55 / J.Fes inactiv in JS see: JobServer\JobControl\JobServer_JobControl_Main.vb Line 1964
       set @ExecuteSQLstr = N'UPDATE Objects_tbl SET ' + @ObjectDataTableProcStateCol + '=''' + @ProcessState + '''' +
                             ' Where Objects_tbl.Id IN' +
                             ' (' +
                             '   SELECT' +
                             '    dbo.Objects_tbl.Id' +
                             '   FROM' +
                             '     dbo.Objects_Ir_Rel_tbl' +
                             '    INNER JOIN dbo.Objects_tbl with (nolock) ON (dbo.Objects_Ir_Rel_tbl.ObjectId = dbo.Objects_tbl.Id)' +
                             '   WHERE' +
                             '     dbo.Objects_Ir_Rel_tbl.IrId =' +
                             '     (' +
                             '       SELECT dbo.Objects_Ir_Rel_tbl.IrId' +
                             '       FROM dbo.Objects_tbl INNER JOIN dbo.Objects_Ir_Rel_tbl with (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)' +
                             '       WHERE dbo.Objects_tbl.Id = ''' + CONVERT(nvarchar(128), @ObjectId) + '''' +
                             '      )' +
                             ' )'
       -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '30-@ExecuteSQLstr:' , @ExecuteSQLstr
      execute sp_executesql @ExecuteSQLstr
   END

   if @ProcessObjectSaveVersion <> ''
   -- 05.11.2015 13:39/J.Fes Moved to this Line
   BEGIN
     if @DebugPrintOn = 1
     BEGIN
       EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '40-Insert @ProcessObjectSaveVersion:' ,  @ProcessObjectSaveVersion
       set @MyValue = CONVERT(varchar(128), @ObjectId)
       EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '41-Insert @ObjectId:' ,  @MyValue
     END

     UPDATE Objects_tbl
       SET ObjectSaveVersion = @ProcessObjectSaveVersion
       where Objects_tbl.ID = @ObjectId
   END

 END

 -- Abschnitt JobControl_ObjectsProcessData_tbl  bearbeiten --------------------------------------------------------
 if @ObjectId IS NOT NULL and (@ProcessJobDataDoAddEdit = 1 or @ProcessJobDataDoAddEdit IS NULL)
 BEGIN
    -- Datensatz in JobControl_ObjectsProcessData_tbl anlegen .. updaten

   -- suchen nach dem Referenzdatensatz via  @Objects_tbl_ID und ProcessPacketName
   if @ProcessPacketName <> ''
   BEGIN
     -- nur nach der @JobControl_ObjectsProcessData_tbl_ID via SQL suchen wenn @ProcessPacketName <> '' gegeben ist
     --     wenn @ProcessPacketName='' dann muss immer ein neuer datensatz in der JobControl_ObjectsProcessData_tbl Tabelle angelegt werden
     --     dieses ist im zusammenhang mit dem JobPlaner erforderlich da dieser vorgeplante JobInfos anlegt!
     if @JobControl_ObjectsProcessData_tbl_ID IS NULL
     BEGIN
        -- suchen in JobControl_ObjectsProcessData_tbl ueber ObejctId und ProcessPaketName
        select @JobControl_ObjectsProcessData_tbl_ID=Id from JobControl_ObjectsProcessData_tbl with (nolock)
               where     [Objects_tbl_ID] = @ObjectId
                     and [ProcessPacketName] = @ProcessPacketName
     END

   END
   -- 04.04.2012 21:29 angepasst damit bei Taskscheduler Objekten der Richtige referenz Datensatz gefunden wird
   if @TaskScheduler_tbl_ID IS NOT NULL
   BEGIN
      -- suchen in JobControl_ObjectsProcessData_tbl ueber ObejctId und TaskScheduler_tbl_ID erforderlich fuer ReDo
      select @JobControl_ObjectsProcessData_tbl_ID=Id from JobControl_ObjectsProcessData_tbl with (nolock)
             where     [Objects_tbl_ID] = @ObjectId
                   and [TaskScheduler_tbl_ID] = @TaskScheduler_tbl_ID
   END


   if @DebugPrintOn = 1
   BEGIN
      set @MyValue = CONVERT(varchar(64), @JobControl_ObjectsProcessData_tbl_ID)
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '30-Insert @JobControl_ObjectsProcessData_tbl_ID:' ,  @MyValue
      set @MyValue = CONVERT(varchar(128), @ObjectId)
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '31-Insert @ObjectId:' ,  @MyValue
   END

   -----------------------------------------  Daten anlegen Updaten ------------------------------------------------
   -- Process Dataset anlegen
   if @JobControl_ObjectsProcessData_tbl_ID IS NULL
   BEGIN
     --  neuen ProcessDatensatz zum Object anlegen
     SET @JobControl_ObjectsProcessData_tbl_ID = NEWID()


     if @DebugPrintOn = 1
     BEGIN
       set @MyValue = CONVERT(varchar(64), @JobControl_ObjectsProcessData_tbl_ID)
       EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '32-Insert @JobControl_ObjectsProcessData_tbl_ID:' ,  @MyValue
       set @MyValue = CONVERT(varchar(128), @ObjectId)
       EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '33-Insert @ObjectId:' ,  @MyValue
     END


     INSERT INTO JobControl_ObjectsProcessData_tbl
                 (Id,
                  Objects_tbl_ID,
                  ProcessPacketName,
                  ProcessState ,
                  ProcessTimeStart,
                  ProcessTimeEnd,
                  ProcessCMDLine,
                  ProcessResultCode,
                  ProcessResultMsg,
                  ProcessResultExtendedData,
                  ProcessResultHasWarning,
                  ProcessResultLink,
                  ProcessOnComputer,
                  TaskScheduler_tbl_ID,
                  ProcessPrgUsedVer,
                  ProcessObjectSaveVersion,
                  ProcessProcStateCol)
        VALUES   (@JobControl_ObjectsProcessData_tbl_ID,
                  @ObjectId,
                  @ProcessPacketName,
                  @ProcessState ,
                  @ProcessTimeStart,
                  @ProcessTimeEnd,
                  @ProcessCMDLine,
                  @ProcessResultCode,
                  @ProcessResultMsg,
                  @ProcessResultExtendedData,
                  @ProcessResultHasWarning,
                  @ProcessResultLink,
                  @ProcessOnComputer,
                  @TaskScheduler_tbl_ID,
                  @ProcessPrgUsedVer,
                  @ProcessObjectSaveVersion,
                  @ProcessProcStateCol)
     END
     ELSE
     BEGIN
      --  vorhanden ProcessDatensatz zum Object updaten = Ergebniss einbuchen
      UPDATE JobControl_ObjectsProcessData_tbl
             SET
                 Objects_tbl_ID            = @ObjectId ,            -- einfuegen zur evtl. selbstreperatur
                 ProcessPacketName         = @ProcessPacketName ,
                 ProcessState              = @ProcessState ,
                 ProcessTimeStart          = @ProcessTimeStart ,
                 ProcessTimeEnd            = @ProcessTimeEnd ,
                 ProcessCMDLine            = @ProcessCMDLine ,
                 ProcessResultCode         = @ProcessResultCode ,
                 ProcessResultExtendedData = @ProcessResultExtendedData ,
                 ProcessResultMsg          = @ProcessResultMsg ,
                 ProcessResultHasWarning   = @ProcessResultHasWarning ,
                 ProcessResultLink         = @ProcessResultLink ,
                 ProcessOnComputer         = @ProcessOnComputer ,
                 -- TaskScheduler_tbl_ID = @TaskScheduler_tbl_ID ,
                 -- ProcessProcStateCol = @ProcessProcStateCol  -- Daten nicht updaten da diese eigentlich nur beim anlegen gesetzt werden!
                 ProcessPrgUsedVer         = @ProcessPrgUsedVer ,
                 ProcessObjectSaveVersion  = @ProcessObjectSaveVersion ,
                 ProcessProcStateCol       = @ProcessProcStateCol
             Where Id = @JobControl_ObjectsProcessData_tbl_ID
   END

   -- #New: 20.06.2016/J.Fes set ObjectSyncTrigger
   if @ObjectSyncTrigger IS NOT NULL
   BEGIN
     -- #LUp: 09.07.2017/J.Fes inserted into Objects_tbl_EditSyncTrigger
     exec Objects_tbl_EditSyncTrigger @ObjectId, @ObjectSyncTrigger
   END

   -- 15.02.2012 in die
   Declare @Objects_tbl_ID_as_nvarchar nvarchar(36)
       set @Objects_tbl_ID_as_nvarchar=CONVERT(nvarchar(36), @ObjectId)
   -- und jetzt Mark as LastRes hier her verlegt
   Execute ObjectsUpdate_ProcessStatus_Mark_LastRes @Objects_tbl_ID_as_nvarchar, @ProcessProcStateCol
 END


 set @ObjectCliName_SQL_r = @ObjectCliName
 set @Objects_tbl_ID_SQL_r = @ObjectId

 if @DebugPrintOn = 1
 BEGIN
    set @MyValue = CONVERT(varchar(128), @ObjectId)
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '50-@ObjectId:' ,  @MyValue
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '52-@ObjectCliName_SQL_r:' , @ObjectCliName_SQL_r
    set @MyValue = CONVERT(varchar(64), @Objects_tbl_ID_SQL_r)
    EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '53-@Objects_tbl_ID_SQL_r:' , @MyValue
 END
END
GO


Print '-- [I_IR_DS_Update_ProcessStatus] ----------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "I_IR_DS_Update_ProcessStatus" and type = "P")
Drop Procedure I_IR_DS_Update_ProcessStatus
GO

/*
 Sets Status for Item of @ObjectFromSiteID
*/
CREATE Procedure I_IR_DS_Update_ProcessStatus
                    (
                  --  @ItemObjectId       uniqueidentifier = null, -- ID des zu bearbeitenden Datensatzes in Objects_tbl
                    @ItemPUID           nvarchar (64) = null,    -- Item PUID
                    @ItemID             nvarchar (128) = null,   -- ItemId, Update nvarchar (64) to nvarchar (128), 20.11.2017, S.Gueth
                    @ObjectFromSiteIDs  nvarchar (128),          -- von welchers site group's
                    @ProcStateCol       nvarchar (32),           -- Name der Spalte ProcStateCol01..10 ProcStateColMc..Rf
                    @ProcStateNewValue  nvarchar (32)            -- get new value
                    )

As
BEGIN

 Declare @DebugPrintOn bit
 set @DebugPrintOn = 0


 DECLARE @ExecuteSQLstr nvarchar(max)


 -- #Step 1 create tbl's
 DECLARE @O_tpb TABLE (
        [ID] [uniqueidentifier] NOT NULL PRIMARY KEY
 )

 DECLARE @IR_tpb TABLE (
        [Id] [uniqueidentifier] NOT NULL PRIMARY KEY
 )

 DECLARE @DS_tpb TABLE (
        [Id] [uniqueidentifier] NOT NULL PRIMARY KEY
 )

 -- #Step 2 select Item's ID
 INSERT INTO @O_tpb (Id)
  SELECT I.Id
  FROM
   dbo.ITEMs_View I
  WHERE
       I.ObjectFromSiteID = @ObjectFromSiteIDs
   AND (I.ItemId = @ItemID OR I.ItemPuid = @ItemPUID)


 -- #Step 2 select IR.IS of ItemID's
 INSERT INTO @IR_tpb (Id)
  SELECT IR.Id
  FROM
   dbo.ITEMs_View I
   LEFT JOIN dbo.IR_I_rel_View  IR_I  ON IR_I.Item_DbId = I.Id
   LEFT JOIN dbo.IRs_View       IR    ON IR.Id = IR_I.IR_DbId
  where I.Id in (select Id from @O_tpb) AND IR.Id IS NOT Null

  -- #Step 3 select DS's of IR.IS
 INSERT INTO @DS_tpb (Id)
  SELECT DS.Id
  FROM
   dbo.IRs_View IR
   LEFT JOIN dbo.DS_IR_rel_View   DS_IR ON DS_IR.IR_DbId = IR.Id
   LEFT JOIN dbo.Objects_tbl      DS    ON DS.Id = DS_IR.DS_DbId
  WHERE IR.Id in (select Id from @IR_tpb) and DS.Id IS NOT Null

  -- #Step 4 union all into all @O_tpb
 INSERT INTO @O_tpb (Id)
  SELECT (Id) FROM @IR_tpb
 UNION ALL
  SELECT (Id) FROM @DS_tpb

 UPDATE Objects_tbl
 set ProcStateColMc = @ProcStateNewValue
 WHERE Objects_tbl.Id in (select Id from @O_tpb)


--  SELECT ObjectCliName,ProcStateColMc  from Objects_tbl
--  WHERE Objects_tbl.Id in (select Id from @O_tpb)


END
GO


ende:
-- EXECUTE zzzzzDebugTbl_Empty
-- --  DECLARE @ObjectId uniqueidentifier
-- --  SET @ObjectId = NEWID()
-- -- EXECUTE I_IR_DS_Update_ProcessStatus '65010000793','''HIL_QS'',''BAL_QS''','Mc','X.XX'
-- EXECUTE I_IR_DS_Update_ProcessStatus '65010000793','HIL_QS','Mc','X.XX'

GO
Print ''
Print '-------------------------------------------------------------------------'
Print ' Procedures imported to #DB_N#'
Print '-------------------------------------------------------------------------'
GO

