Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [ObjectStucture]'
Print ' Creation Date : 22.03.2006'
Print ' Autor         : J.Feuerstein'
Print ' '
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO

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

CREATE Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes
                 @Objects_tbl_ID  nvarchar(36)  -- ID des zu bearbeitenden Datensatzes in JobControl_ObjectsProcessData_tbl


/* Makiert den LastRes Datensatz in dbo.JobControl_ObjectsProcessData_tbl  erstellt 23.02.2009 J.Fes */
As
BEGIN

  -- EXECUTE zzzzzDebugTbl_Empty
  -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus_Mark_LastRes' , '01-@Objects_tbl_ID:' ,  @Objects_tbl_ID
          -- declare  @ObjectId_tmp nvarchar(64)
          -- set @ObjectId_tmp=CONVERT(nvarchar(64), @ObjectId)
          -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '20-@ObjectId:' ,  @ObjectId_tmp

  if (object_id('tempdb..#zztmpMAX1_tbl') is not null)  Drop table #zztmpMAX1_tbl
  if (object_id('tempdb..#zztmpMAX2_tbl') is not null)  Drop table #zztmpMAX2_tbl

  -- Step: auffinden des .. der Last Result Datensätze in Tabelle: dbo.JobControl_ObjectsProcessData_tbl
  SELECT
    MAX(dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart) AS ProcessTimeStart_MAX,
    dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID ,
    dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol
  INTO #zztmpMAX1_tbl
  FROM
    dbo.JobControl_ObjectsProcessData_tbl with (nolock)
  WHERE  -- wenn @Objects_tbl_ID='' --> entfält WHERE clausel
    dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID
  GROUP BY
     dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
   , dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol

  -- Step: dbo.JobControl_ObjectsProcessData_tbl.ID's aller Last Result Datensätze ermitteln
  -- siehe hierzu auch http://www.eggheadcafe.com/conversation.aspx?messageid=30818900&threadid=30818889 (28.02.2009 09:40)
  -- CodeExample: vergleichbar mit first(..) Dataset
  SELECT *
   Into #zztmpMAX2_tbl
  from
    (Select dbo.JobControl_ObjectsProcessData_tbl.ID
           , dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
           -- , rn = row_number() OVER (PARTITION BY dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID ORDER BY dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
           , rn = row_number() OVER (PARTITION BY #zztmpMAX1_tbl.Objects_tbl_ID, #zztmpMAX1_tbl.ProcessProcStateCol ORDER BY #zztmpMAX1_tbl.Objects_tbl_ID)
     from
      dbo.JobControl_ObjectsProcessData_tbl with (nolock),
      #zztmpMAX1_tbl with (nolock)
     Where
          dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID       = #zztmpMAX1_tbl.Objects_tbl_ID
      and dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol  = #zztmpMAX1_tbl.ProcessProcStateCol
      and dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart     = #zztmpMAX1_tbl.ProcessTimeStart_MAX
     ) As SelectSub
  where SelectSub.rn=1

  -- Step: alle Objekte die nicht Last Result sind auf 0 setzen
  Update dbo.JobControl_ObjectsProcessData_tbl set dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 0
   where
     (dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 0 or dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes is NULL) and
      -- -- wenn @Objects_tbl_ID='' entfaellt die nachfolgende Zeile
      dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID and
      dbo.JobControl_ObjectsProcessData_tbl.ID not IN (select #zztmpMAX2_tbl.ID from #zztmpMAX2_tbl)

  -- Step Objekte der Last Result ist auf 1 setzen
  Update dbo.JobControl_ObjectsProcessData_tbl set dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 1
   where
     (dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 1 or dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes is NULL) and
      dbo.JobControl_ObjectsProcessData_tbl.ID IN (select #zztmpMAX2_tbl.ID from #zztmpMAX2_tbl)

END
GO

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

CREATE Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes__MaintainDB

/* Makiert den LastRes Datensatz in dbo.JobControl_ObjectsProcessData_tbl  erstellt 23.02.2009 J.Fes */
As
BEGIN
  -- 05.03.2011 12:10 eingefuegt
  set nocount on

  if (object_id('tempdb..#zztmpMAX1_tbl') is not null)  Drop table #zztmpMAX1_tbl
  if (object_id('tempdb..#zztmpMAX2_tbl') is not null)  Drop table #zztmpMAX2_tbl

  -- Step: auffinden des .. der Last Result Datensätze in Tabelle: dbo.JobControl_ObjectsProcessData_tbl
  SELECT
    MAX(dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart) AS ProcessTimeStart_MAX,
    dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID ,
    dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol
  INTO #zztmpMAX1_tbl
  FROM
    dbo.JobControl_ObjectsProcessData_tbl -- with (nolock)
  -- WHERE  -- wenn @Objects_tbl_ID='' --> entfält WHERE clausel
    -- dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID
  GROUP BY
     dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
   , dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol

  -- Step: dbo.JobControl_ObjectsProcessData_tbl.ID's aller Last Result Datensätze ermitteln
  -- siehe hierzu auch http://www.eggheadcafe.com/conversation.aspx?messageid=30818900&threadid=30818889 (28.02.2009 09:40)
  -- CodeExample: vergleichbar mit first(..) Dataset
  SELECT *
   Into #zztmpMAX2_tbl
  from
    (Select dbo.JobControl_ObjectsProcessData_tbl.ID
           , dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
           -- , rn = row_number() OVER (PARTITION BY dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID ORDER BY dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
           , rn = row_number() OVER (PARTITION BY #zztmpMAX1_tbl.Objects_tbl_ID, #zztmpMAX1_tbl.ProcessProcStateCol ORDER BY #zztmpMAX1_tbl.Objects_tbl_ID)
     from
      dbo.JobControl_ObjectsProcessData_tbl ,
      #zztmpMAX1_tbl -- with (nolock)
     Where
          dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID       = #zztmpMAX1_tbl.Objects_tbl_ID
      and dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol  = #zztmpMAX1_tbl.ProcessProcStateCol
      and dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart     = #zztmpMAX1_tbl.ProcessTimeStart_MAX
     ) As SelectSub
  where SelectSub.rn=1

  -- Step: alle Objekte die nicht Last Result sind auf 0 setzen
  Update dbo.JobControl_ObjectsProcessData_tbl set dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 0
   where
     (dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 0 or dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes is NULL) and
      -- wenn @Objects_tbl_ID='' entfaellt die nachfolgende Zeile
      -- dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID and
      dbo.JobControl_ObjectsProcessData_tbl.ID not IN (select #zztmpMAX2_tbl.ID from #zztmpMAX2_tbl)

  -- Step Objekte der Last Result ist auf 1 setzen
  Update dbo.JobControl_ObjectsProcessData_tbl set dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 1
   where
     (dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 1 or dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes is NULL) and
      dbo.JobControl_ObjectsProcessData_tbl.ID IN (select #zztmpMAX2_tbl.ID from #zztmpMAX2_tbl)

END
GO

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

CREATE PROCEDURE JobControl_ObjectsProcessData_tbl_Reset
AS
BEGIN

  /* alles was in P geblieben ist auf 'D' setzen */
  UPDATE JobControl_ObjectsProcessData_tbl SET ProcessState = 'D' WHERE ProcessState = 'P'

 /*
  UPDATE Objects_tbl SET ProcStateCol01 = 'N'  where ProcStateCol01 = '' or ProcStateCol01 is NULL
  Print 'DONE ProcStateCol01'
 -- UPDATE Objects_tbl SET ProcStateCol03 = 'N'  where ProcStateCol03 = '' or ProcStateCol03 is NULL
  UPDATE Objects_tbl SET ProcStateCol04 = 'N'  where ProcStateCol04 = '' or ProcStateCol04 is NULL
  Print 'DONE ProcStateCol04'
  UPDATE Objects_tbl SET ProcStateCol05 = 'N'  where ProcStateCol05 = '' or ProcStateCol05 is NULL
  Print 'DONE ProcStateCol05'
  UPDATE Objects_tbl SET ProcStateCol06 = 'N'  where ProcStateCol06 = '' or ProcStateCol06 is NULL
  Print 'DONE ProcStateCol06'
  UPDATE Objects_tbl SET ProcStateCol07 = 'N'  where ProcStateCol07 = '' or ProcStateCol07 is NULL
  Print 'DONE ProcStateCol07'
  UPDATE Objects_tbl SET ProcStateCol08 = 'N'  where ProcStateCol08 = '' or ProcStateCol08 is NULL
  Print 'DONE ProcStateCol08'
  UPDATE Objects_tbl SET ProcStateCol09 = 'N'  where ProcStateCol09 = '' or ProcStateCol09 is NULL
  Print 'DONE ProcStateCol09'
  UPDATE Objects_tbl SET ProcStateCol10 = 'N'  where ProcStateCol10 = '' or ProcStateCol10 is NULL
  Print 'DONE ProcStateCol10'
  */
END
GO

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

Create Procedure Objects_tbl_Object_GetDS  @ObjectCliName    nvarchar(255),
                                           @ObjectName       nvarchar(255),  -- new 28.01.2011 09:48 J.Fes
                                           @ObjectPuid       nvarchar(15),
                                           @ObjectFromSiteID nvarchar(32),
                                           @ObjectClass      nvarchar(10)
-- gibt den Datensatz zur gesuchten ObjectPuid zurueck
-- Wichtig: ein Datensatz wird eindeutig durch @ObjectPuid und @ObjectFromSiteID @ObjectClass
--          oder durch @ObjectCliName und @ObjectFromSiteID @ObjectClass
-- with (nolock) 10.07.2010 16:38 eingefuegt!!
-- ergaenzung jetzt auch suche via @ObjectName integriert
-- @Id               nvarchar (64),
As
BEGIN
--    if @ID <> ''
--     begin
--      -- suche mit @ObjectPuid
--      select * from Objects_tbl with (nolock)
--         where (ID      = @Id)
--      goto MyExit
--     end

   if @ObjectPuid <> ''
    begin
     -- suche mit @ObjectPuid
     select * from Objects_tbl with (nolock)
        where (ObjectPuid      = @ObjectPuid
          and ObjectFromSiteID = @ObjectFromSiteID)
     goto MyExit
    end

    if @ObjectCliName <> ''
    begin
     -- suche mit @ObjectCliName
     select * from Objects_tbl with (nolock)
        where (ObjectCliName   = @ObjectCliName
          and ObjectFromSiteID = @ObjectFromSiteID
          and ObjectClass      = @ObjectClass)
     goto MyExit
    end

    if @ObjectName <> ''
    begin
       -- suche mit @ObjectName new 28.01.2011 09:47
       select * from Objects_tbl with (nolock)
          where (ObjectName      = @ObjectName
            and ObjectFromSiteID = @ObjectFromSiteID
            and ObjectClass      = @ObjectClass)
       goto MyExit
    end
  MyExit:
END
GO


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


Create Procedure Objects_tbl_Object_GetId  @ObjectCliName    nvarchar(255) ,
                                           @ObjectFromSiteID nvarchar(32) ,
                                           @ObjectClass      nvarchar(10) ,
                                           @Id               uniqueidentifier Output
-- gibt die ID zum Datensatz des gesuchten ObjectNamens zurueck
-- wichtig ein DataSet Datensatz wird eindeutig durch [ObjectCliName], [ObjectFromSiteID], [ObjectClass]
-- WITH (nolock) eingefuegt  10.07.2010 16:38 J.fes
As
BEGIN
  select @ID=Id from Objects_tbl with (nolock)
         where ObjectCliName    = @ObjectCliName
           and ObjectFromSiteID = @ObjectFromSiteID
           and ObjectClass      = @ObjectClass
END
GO



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


Create Procedure Objects_tbl_Object_GetId_via_ObjectPuid
                                           @ObjectPuid       nvarchar(15) ,
                                           @ObjectCliName    nvarchar(255) ,
                                           @ObjectFromSiteID nvarchar(32) ,
                                           @ObjectClass      nvarchar(10) ,
                                           @IrId             uniqueidentifier ,
                                           @IrPuid           nvarchar(15),
                                           @Id               uniqueidentifier Output
-- gibt die ID zum Datensatz des gesuchten Objects zurueck
-- wichtig ein DataSet Datensatz wird eindeutig durch [@ObjectPuid], [ObjectFromSiteID], [ObjectClass]
-- 24.02.2011 J.Fes @IrId eingefuegt um feststellen zu koennen ob ein DS mehrfach referenziert ist
-- ##Debug: -- BgStrukEx-Einzelteil-01_01/A;BgStrukEx-Einzelteil-01_01/B
As
BEGIN

--  DECLARE @ExcuteSQLStr nvarchar(max)
--  DECLARE @DebugPrintOn bit
--  set @DebugPrintOn = 0
--  DECLARE @MyProcName nvarchar(32)
--  set @MyProcName='Objects_tbl_Object_GetId_via_ObjectPuid'
--
--
--  if @DebugPrintOn = 1
--  begin
--   -- Clear Debug Table
--   -- EXECUTE zzzzzDebugTbl_Empty
--   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectPuid'       , @ObjectPuid
--   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ObjectCliName'    , @ObjectCliName
--   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@ObjectFromSiteID' , @ObjectFromSiteID
--   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@ObjectClass'      , @ObjectClass
--   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@IrId'             , @IrId
--   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@IrPuid'           , @IrPuid
--  end


 -- letzte anpassung 24.02.2011  J.Fes
 set @Id = NULL
 -- angepasst 01.12.2010 18:38 erstmal via PuiD suchen ohne ObjectCliName !!  da sich der ObjectCliName eventuell aendert
 if @ObjectPuid <> ''
  begin
   if @IrId is NULL
    begin
     -- if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.1-@IrId-IsNull'   , @IrId
     select @Id=Id from Objects_tbl WITH (nolock)
               where ObjectPuid       = @ObjectPuid
                 and ObjectFromSiteID = @ObjectFromSiteID
                 and ObjectClass      = @ObjectClass
    end
   else
    begin
     /* SQL Stand bis 01.03.2011 dieser hatte dazugeführt das ein Dataset mit mehreren referenzen im JobManger mehrfach referenziert ist */
     /*
     select @Id=Id from Objects_tbl WITH (nolock)
               where ObjectPuid       = @ObjectPuid
                 and ObjectFromSiteID = @ObjectFromSiteID
                 and ObjectClass      = @ObjectClass
       */

     SELECT @Id=dbo.Objects_tbl.Id
     FROM
       dbo.Objects_tbl WITH (nolock)
       INNER JOIN dbo.Objects_Ir_Rel_tbl WITH (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
     WHERE
       ObjectPuid           = @ObjectPuid
       and ObjectFromSiteID = @ObjectFromSiteID
       and ObjectClass      = @ObjectClass
       and dbo.Objects_Ir_Rel_tbl.IrId = @IrId

    end --
   end
   else
   begin
     -- ermittelt das Objekt via @ObjectCliName wenn:
     -- das Object über vorherige Abfrage nicht gefunden wurde
     -- 23.09.2010 wieder eingefuegt
     EXECUTE Objects_tbl_Object_GetId @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @Id Output
   end

END
GO



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


Create Procedure Objects_tbl_Object_GetId_UgMasterDS_of_IR
                                   @Ir_ObjectCliName    nvarchar(255) ,
                                   @Ir_ObjectFromSiteID nvarchar(32) ,
                                   @Ir_ObjectClass      nvarchar(10) ,
                                   @Id                  uniqueidentifier Output ,
                                   @DS_ObjectName       nvarchar(255) Output ,
                                   @DS_ObjectCliName    nvarchar(255) Output ,
                                   @DS_ObjectClass      nvarchar(255) Output
-- gibt die ID des Dataset UGmaster zurück ausgehend von einer IR
-- wichtig ein DataSet Datensatz wird eindeutig durch [@ObjectPuid], [ObjectFromSiteID], [ObjectClass]
As
BEGIN
  select @Id=DS.Id , @DS_ObjectName=DS.ObjectName, @DS_ObjectClass=DS.ObjectClass , @DS_ObjectCliName=DS.ObjectCliName
  FROM
    dbo.Objects_tbl IR WITH (NOLOCK)
    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.ObjectCliName = @Ir_ObjectCliName AND
    IR.ObjectClass = 'IR' AND
    IR.ObjectFromSiteID = @Ir_ObjectFromSiteID AND
    DS.ObjectType = 'UGMASTER'
END
GO


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


Create Procedure Objects_tbl_Object_GetId_DS_of_IR_viaObjectID
                                   @Ir_ObjectCliName    nvarchar(255) ,
                                   @Ir_ObjectFromSiteID nvarchar(32) ,
                                   @DS_ObjectName       nvarchar(255) ,
                                   @Id                  uniqueidentifier Output ,
                                   @DS_ObjectCliName    nvarchar(255) Output
-- gibt die ID des Dataset von IR mit DS.ObjectName=@DS_ObjectName new 15.02.2012
-- wichtig ein DataSet Datensatz wird eindeutig durch [@Ir_ObjectFromSiteID ], [@Ir_ObjectCliName], [@DS_ObjectName]
As
BEGIN
  select top 1
    @Id=DS.Id ,
    @DS_ObjectCliName=DS.ObjectCliName
  FROM
    dbo.Objects_tbl IR WITH (NOLOCK)
    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.ObjectCliName = @Ir_ObjectCliName
    And IR.ObjectClass = 'IR'
    And IR.ObjectFromSiteID = @Ir_ObjectFromSiteID
    And DS.ObjectName = @DS_ObjectName
END
GO

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


Create Procedure Objects_tbl_Object_GetId_DS_of_IR_viaObjectType_UGMASTER
                                   @Ir_ObjectCliName    nvarchar(255) ,
                                   @Ir_ObjectFromSiteID nvarchar(32) ,
                                   @DS_ObjectName       nvarchar(255) ,
                                   @Id                  uniqueidentifier Output ,
                                   @DS_ObjectCliName    nvarchar(255) Output
-- gibt die ID des Dataset von IR mit DS.ObjectName=@DS_ObjectName new 15.02.2012
-- wichtig ein DataSet Datensatz wird eindeutig durch [@Ir_ObjectFromSiteID ], [@Ir_ObjectCliName], [@DS_ObjectName]
As
BEGIN
  select top 1
    @Id=DS.Id ,
    @DS_ObjectCliName=DS.ObjectCliName
  FROM
    dbo.Objects_tbl IR WITH (NOLOCK)
    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.ObjectCliName = @Ir_ObjectCliName
    And IR.ObjectClass = 'IR'
    And IR.ObjectFromSiteID = @Ir_ObjectFromSiteID
    And DS.ObjectType = 'UGMASTER'
END
GO


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

Create Procedure Objects_Ir_Rel_tbl_GetId  @IrId        uniqueidentifier,
                                           @ObjectId    uniqueidentifier,
                                           @Id          uniqueidentifier Output
-- gibt die ID zum Datensatz zurück
-- 11.07.2010 14:27 WITH (NOLOCK) ergänzt
As
BEGIN
  select @ID=Id from Objects_Ir_Rel_tbl WITH (NOLOCK)
         where IrId      = @IrId
           and ObjectId  = @ObjectId
END
GO

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

Create Procedure Objects_Ir_Rel_Create  @IrId              uniqueidentifier,
                                        @ObjectId          uniqueidentifier,
                                        @DataSet_Ir_RelId  uniqueidentifier Output
-- fuegt in die Tabelle Objects_Ir_Rel_tbl die Releation ein
-- 12.07.2010 09:14 J.Fes new
As
BEGIN
 EXECUTE Objects_Ir_Rel_tbl_GetId @IrId ,@ObjectId  , @DataSet_Ir_RelId Output

 DECLARE @RetryCounter INT
 SET @RetryCounter = 1
 RETRY: -- Label RETRY

 BEGIN TRY          -- *************  Begin TRY ************
  BEGIN TRANSACTION  -- *************  BEGIN Transaction **************  Verlegt 03.09.2010 14:35 J.Fes/A.Becker
   -- referenzen IR - Dataset anlegen
  if @DataSet_Ir_RelId IS NULL
    BEGIN
     SET @DataSet_Ir_RelId = NEWID()
     INSERT INTO Objects_Ir_Rel_tbl (ID ,                IrId , ObjectId)
                 VALUES             (@DataSet_Ir_RelId ,@IrId ,@ObjectId)
    END
 COMMIT TRANSACTION   -- *************  Commit Transaction **************
 END TRY              -- *************  End TRY **************

 BEGIN CATCH          -- *************  Begin CATCH ************
 	-- PRINT 'Rollback Transaction'
 	ROLLBACK TRANSACTION
 	DECLARE @DoRetry bit; -- Whether to Retry transaction or not
 	DECLARE @ErrorMessage varchar(500)
 	SET @doRetry = 0;
 	SET @ErrorMessage = ERROR_MESSAGE()
 	IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
 	BEGIN
 		SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
 	END
 	IF @DoRetry = 1
 	BEGIN
 		SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
 		IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
 		BEGIN
 			RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if still deadlock occurred after three retries
 		END
 		ELSE
 		BEGIN
    	WAITFOR DELAY '00:00:00.10' -- 02.08.2010 19:35 Wait=01 sec 23.07.2010 01:30 Wait=01 sec --- Wait for 10 ms
 			GOTO RETRY	-- Go to Label RETRY
 		END
 	END
 	ELSE
 	BEGIN
 		RAISERROR(@ErrorMessage, 18, 1)
 	END
 END CATCH    -- *************  End CATCH ************
END
GO


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


Create Procedure Object_Import @ObjectPuid                    nvarchar(15),
                               @ObjectName                    nvarchar(64),
                               @ObjectName2                   nvarchar(32),
                               @ObjectDescription             nvarchar(255),

                               @ObjectFromSiteID              nvarchar(32),
                               @ObjectCliName                 nvarchar(255),
                               @ObjectClass                   nvarchar(10),   -- IR DS

                               @ObjectOwningSiteID            nvarchar(32),
                               @ObjectType                    nvarchar(32),   -- Pos9 UGMASTER UGPART ..
--                                10.01.2012 J.Fes entnommen
--                                @ObjectIrType                  nvarchar(32),   -- Pos10 ItemRevisionType new 20.12.2011

                               @ObjectOwner                   nvarchar(32),
                               @ObjectGroup                   nvarchar(32),
                               @ObjectCreateDate              datetime    ,
                               @ObjectLastModUser             nvarchar(32),
                               @ObjectLastModDate             datetime    ,

                               @ObjectFs_DPNE                 nvarchar(255),
                               @ObjectFs_Vol                  nvarchar(64),
                               @ObjectFs_NE                   nvarchar(64),

                               @ObjectUgMaster_PartFamMemName nvarchar(64),
                               @ObjectUgMaster_Type           nvarchar(32),   -- SP AP
                               @ObjectUgMaster_APLevels       int,
                               @ObjectUgMaster_CompCount      int,
                               @ObjectUgMaster_FsHitCount     int,

                               @ObjectReleaseState            nvarchar(32),
                               @ObjectReleaseDate             datetime,

                               @ObjectSelectByMarker          nvarchar(32),
                               @ObjectNote                    nvarchar(255),

                               @ImportObjectNewProcStateMask  nvarchar(11),
                               @ImportObjectUpdProcStateMask  nvarchar(11),
                               @ImportObjectErr               nvarchar(64),

                               @IrId                          uniqueidentifier,
                               @IrPuid                        nvarchar(15),
                               @ObjectId                      uniqueidentifier Output


--  Lastet ein Object in die Datenbank ein wichtig: bei Struktur anpassungen ist immer die .. dazugehörigen View auch zu prüfen !!
--  31.12.2008 J.Fes @ImportObjectErr
--  12.07.2010 J.Fes Transaction methoden eingefuegt
--  24.02.2011 J.Fes @IrId eingefuegt
As
BEGIN

 DECLARE @DebugPrintOn bit
 set @DebugPrintOn = 0
 DECLARE @MyProcName nvarchar(32)
 set @MyProcName='Object_Import'

 if @DebugPrintOn = 1
 begin
  -- Clear Debug Table
  -- EXECUTE zzzzzDebugTbl_Empty
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectPuid' ,    @ObjectPuid
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ObjectCliName' , @ObjectCliName
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@ObjectType' , @ObjectType
 end


 DECLARE @RetryCounter INT
 SET @RetryCounter = 1
 RETRY: -- Label RETRY
 BEGIN TRY          -- *************  Begin TRY ************
 BEGIN TRANSACTION  -- *************  BEGIN Transaction ************** 03.09.2010 14:39 Verlegt J.Fes/A.Becker

  EXECUTE JF_DateResetCheck @ObjectReleaseDate Output

  DECLARE @ProcStateCol01Def nvarchar(1)
  DECLARE @ProcStateCol02Def nvarchar(1)
  DECLARE @ProcStateCol03Def nvarchar(1)
  DECLARE @ProcStateCol04Def nvarchar(1)
  DECLARE @ProcStateCol05Def nvarchar(1)
  DECLARE @ProcStateCol06Def nvarchar(1)
  DECLARE @ProcStateCol07Def nvarchar(1)
  DECLARE @ProcStateCol08Def nvarchar(1)
  DECLARE @ProcStateCol09Def nvarchar(1)
  DECLARE @ProcStateCol10Def nvarchar(1)
  DECLARE @ProcStateColRfDef nvarchar(1)

  -- set @ObjectCliName = @ObjectCliName + 'Debug#:Test'
  -- debug: BgStrukEx-Einzelteil-01_01/A;BgStrukEx-Einzelteil-01_01/B
  -- ermittelt die Id der IR in der Objects_tbl
  EXECUTE Objects_tbl_Object_GetId_via_ObjectPuid @ObjectPuid, @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @IrId, @IrPuid,  @ObjectId Output

--   20.12.2011 00:20 entnommen da IrType jetzt verwendet wird
--   if @ObjectClass = 'IR' -- and @ObjectType = 'UGMASTER'
--   begin
--     -- ObjectType auf ~ setzen
--     set @ObjectType = '~'
--   end

  if @ObjectId IS NULL
     BEGIN
      if @DebugPrintOn = 1
      begin
        EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '13-@ObjectId IS:' , '@ObjectId IS NULL Insert'
      end
      SET @ProcStateCol01Def = substring(@ImportObjectNewProcStateMask,1,1)
      SET @ProcStateCol02Def = substring(@ImportObjectNewProcStateMask,2,1)
      SET @ProcStateCol03Def = substring(@ImportObjectNewProcStateMask,3,1)
      SET @ProcStateCol04Def = substring(@ImportObjectNewProcStateMask,4,1)
      SET @ProcStateCol05Def = substring(@ImportObjectNewProcStateMask,5,1)
      SET @ProcStateCol06Def = substring(@ImportObjectNewProcStateMask,6,1)
      SET @ProcStateCol07Def = substring(@ImportObjectNewProcStateMask,7,1)
      SET @ProcStateCol08Def = substring(@ImportObjectNewProcStateMask,8,1)
      SET @ProcStateCol09Def = substring(@ImportObjectNewProcStateMask,9,1)
      SET @ProcStateCol10Def = substring(@ImportObjectNewProcStateMask,10,1)
      SET @ProcStateColRfDef = substring(@ImportObjectNewProcStateMask,11,1)

      -- NEWSEQUENTIALID()
      -- http://sqlpractice.wordpress.com/2010/04/08/sql-server-newid-versus-newsequentialid/ 04.03.2011 21:15 von Alex
      -- last ID get via SCOPE_IDENTITY()
      SET @ObjectId = NEWID()
      INSERT INTO Objects_tbl (ID ,
                               ObjectPuid ,
                               ObjectName ,
                               ObjectName2 ,
                               ObjectDescription ,

                               ObjectFromSiteID ,
                               ObjectCliName ,
                               ObjectClass ,

                               ObjectOwningSiteID ,
                               ObjectType ,
--                             10.01.2012 12:03 entnommen
--                             ObjectIrType,

                               ObjectOwner ,
                               ObjectGroup ,
                               ObjectCreateDate ,
                               ObjectLastModUser ,
                               ObjectLastModDate ,
                               ObjectNote , --  21.10.2009 J.Fes eingefuegt

                               ObjectFs_DPNE ,
                               ObjectFs_Vol ,
                               ObjectFs_NE ,

                               ObjectUgMaster_PartFamMemName ,
                               ObjectUgMaster_Type ,
                               ObjectUgMaster_APLevels ,
                               ObjectUgMaster_CompCount ,
                               ObjectUgMaster_FsHitCount ,

                               ObjectReleaseState ,
                               ObjectReleaseDate ,

                               ProcStateCol01 ,
                               ProcStateCol02 ,
                               ProcStateCol03 ,
                               ProcStateCol04 ,
                               ProcStateCol05 ,
                               ProcStateCol06 ,
                               ProcStateCol07 ,
                               ProcStateCol08 ,
                               ProcStateCol09 ,
                               ProcStateCol10 ,
                               ProcStateColRf ,

                               ImportObjectErr
                               )
                  VALUES      (@ObjectId ,
                               @ObjectPuid ,
                               @ObjectName ,
                               @ObjectName2 ,
                               @ObjectDescription ,

                               @ObjectFromSiteID ,
                               @ObjectCliName ,
                               @ObjectClass ,

                               @ObjectOwningSiteID ,
                               @ObjectType ,
--                                entnommen J.Fes/ 10.01.2012
--                                @ObjectIrType ,

                               @ObjectOwner ,
                               @ObjectGroup ,
                               @ObjectCreateDate ,
                               @ObjectLastModUser ,
                               @ObjectLastModDate ,
                               @ObjectNote ,

                               @ObjectFs_DPNE ,
                               @ObjectFs_Vol ,
                               @ObjectFs_NE ,

                               @ObjectUgMaster_PartFamMemName ,
                               @ObjectUgMaster_Type ,
                               @ObjectUgMaster_APLevels ,
                               @ObjectUgMaster_CompCount ,
                               @ObjectUgMaster_FsHitCount ,

                               @ObjectReleaseState ,
                               @ObjectReleaseDate ,

                               @ProcStateCol01Def ,
                               @ProcStateCol02Def ,
                               @ProcStateCol03Def ,
                               @ProcStateCol04Def ,
                               @ProcStateCol05Def ,
                               @ProcStateCol06Def ,
                               @ProcStateCol07Def ,
                               @ProcStateCol08Def ,
                               @ProcStateCol09Def ,
                               @ProcStateCol10Def ,
                               @ProcStateColRfDef ,

                               @ImportObjectErr
                               )
       -- SET @ReturnValue = @ReturnValue + '/DataSet=Add'
     END
  ELSE
     BEGIN
      -- Print @Objects_tbl + ' ist vorhanden Update erforderlich'
      -- PRINT 'Edit Datensatz mit @Id:=' + CONVERT(varchar(100), @Id)
      if @DebugPrintOn = 1
      begin
       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '13-@ObjectId IS:' , '@ObjectId is NOT Null Update'
      end
      UPDATE Objects_tbl
             SET ObjectPuid         = @ObjectPuid ,
                 ObjectName         = @ObjectName ,
                 ObjectName2        = @ObjectName2 ,
                 ObjectDescription  = @ObjectDescription ,

                 ObjectFromSiteID   = @ObjectFromSiteID ,
                 ObjectCliName      = @ObjectCliName ,
                 ObjectClass        = @ObjectClass ,

                 ObjectOwningSiteID = @ObjectOwningSiteID ,
                 ObjectType         = @ObjectType ,
--                 10.01.2012 J.Fes entnommen
--                 ObjectIrType       = @ObjectIrType , -- 20.12.2011 06:27 eingefuegt

                 ObjectOwner        = @ObjectOwner ,
                 ObjectGroup        = @ObjectGroup ,
                 ObjectCreateDate   = @ObjectCreateDate ,
                 ObjectLastModUser  = @ObjectLastModUser ,
                 ObjectLastModDate  = @ObjectLastModDate ,
                 ObjectNote         = @ObjectNote , -- 21.10.2009 eingefuegt JFes

                 ObjectFs_DPNE      = @ObjectFs_DPNE ,
                 ObjectFs_Vol       = @ObjectFs_Vol ,
                 ObjectFs_NE        = @ObjectFs_NE ,

                 ObjectUgMaster_PartFamMemName = @ObjectUgMaster_PartFamMemName ,
                 ObjectUgMaster_Type           = @ObjectUgMaster_Type ,
                 ObjectUgMaster_APLevels       = @ObjectUgMaster_APLevels ,
                 ObjectUgMaster_CompCount      = @ObjectUgMaster_CompCount ,
                 ObjectUgMaster_FsHitCount     = @ObjectUgMaster_FsHitCount ,

                 ObjectReleaseState = @ObjectReleaseState ,
                 ObjectReleaseDate  = @ObjectReleaseDate ,

                 ImportObjectErr = @ImportObjectErr
             Where ID = @ObjectId

             -- Updating ProcState
             SET @ProcStateCol01Def = substring(@ImportObjectUpdProcStateMask,1,1)
             SET @ProcStateCol02Def = substring(@ImportObjectUpdProcStateMask,2,1)
             SET @ProcStateCol03Def = substring(@ImportObjectUpdProcStateMask,3,1)
             SET @ProcStateCol04Def = substring(@ImportObjectUpdProcStateMask,4,1)
             SET @ProcStateCol05Def = substring(@ImportObjectUpdProcStateMask,5,1)
             SET @ProcStateCol06Def = substring(@ImportObjectUpdProcStateMask,6,1)
             SET @ProcStateCol07Def = substring(@ImportObjectUpdProcStateMask,7,1)
             SET @ProcStateCol08Def = substring(@ImportObjectUpdProcStateMask,8,1)
             SET @ProcStateCol09Def = substring(@ImportObjectUpdProcStateMask,9,1)
             SET @ProcStateCol10Def = substring(@ImportObjectUpdProcStateMask,10,1)
             SET @ProcStateColRfDef = substring(@ImportObjectUpdProcStateMask,11,1)

             -- Update der Processspalten wenn <> '-'
             if @ProcStateCol01Def <> '-' UPDATE Objects_tbl SET ProcStateCol01 = @ProcStateCol01Def Where ID = @ObjectId
             if @ProcStateCol02Def <> '-' UPDATE Objects_tbl SET ProcStateCol02 = @ProcStateCol02Def Where ID = @ObjectId
             if @ProcStateCol03Def <> '-' UPDATE Objects_tbl SET ProcStateCol03 = @ProcStateCol03Def Where ID = @ObjectId
             if @ProcStateCol04Def <> '-' UPDATE Objects_tbl SET ProcStateCol04 = @ProcStateCol04Def Where ID = @ObjectId
             if @ProcStateCol05Def <> '-' UPDATE Objects_tbl SET ProcStateCol05 = @ProcStateCol05Def Where ID = @ObjectId
             if @ProcStateCol06Def <> '-' UPDATE Objects_tbl SET ProcStateCol06 = @ProcStateCol06Def Where ID = @ObjectId
             if @ProcStateCol07Def <> '-' UPDATE Objects_tbl SET ProcStateCol07 = @ProcStateCol07Def Where ID = @ObjectId
             if @ProcStateCol08Def <> '-' UPDATE Objects_tbl SET ProcStateCol08 = @ProcStateCol08Def Where ID = @ObjectId
             if @ProcStateCol09Def <> '-' UPDATE Objects_tbl SET ProcStateCol09 = @ProcStateCol09Def Where ID = @ObjectId
             if @ProcStateCol10Def <> '-' UPDATE Objects_tbl SET ProcStateCol10 = @ProcStateCol10Def Where ID = @ObjectId
             if @ProcStateColRfDef <> '-' UPDATE Objects_tbl SET ProcStateColRf = @ProcStateColRfDef Where ID = @ObjectId
     END

 COMMIT TRANSACTION   -- *************  Commit Transaction **************
 END TRY              -- *************  End TRY **************

 BEGIN CATCH          -- *************  Begin CATCH ************
 	-- PRINT 'Rollback Transaction'
 	ROLLBACK TRANSACTION
 	DECLARE @DoRetry bit; -- Whether to Retry transaction or not
 	DECLARE @ErrorMessage varchar(500)
 	SET @doRetry = 0;
 	SET @ErrorMessage = ERROR_MESSAGE()
 	IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
 	BEGIN
 		SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
 	END
 	IF @DoRetry = 1
 	BEGIN
 		SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
 		IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
 		BEGIN
 			RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if still deadlock occurred after three retries
 		END
 		ELSE
 		BEGIN
    WAITFOR DELAY '00:00:00.10' -- 02.08.2010 19:35 Wait=01 sec 23.07.2010 01:30 Wait=01 sec --- Wait for 10 ms
 			GOTO RETRY	-- Go to Label RETRY
 		END
 	END
 	ELSE
 	BEGIN
 		RAISERROR(@ErrorMessage, 18, 1)
 	END
 END CATCH    -- *************  End CATCH ************

END
GO

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


Create Procedure DataSets_and_IRs_Import
                                    @ItemName                     nvarchar(64),
                                    @IrPuid                       nvarchar(15),

                                    @IrRev                        nvarchar(32),
                                    @IrNameRev                    nvarchar(64),
                                    @IrType                       nvarchar(32),
                                    @IrName2                      nvarchar(32),
                                    @IrDescription                nvarchar(255),

                                    @IrCliName                    nvarchar(128),
                                    @IrFromSiteID                 nvarchar(32),
                                    @IrOwningSiteID               nvarchar(32),

                                    @IrOwner                      nvarchar(32),
                                    @IrGroup                      nvarchar(32),
                                    @IrCreateDate                 datetime,
                                    @IrLastModUser                nvarchar(128),
                                    @IrLastModDate                datetime,

                                    @IrReleaseState               nvarchar(32) ,
                                    @IrReleaseDate                datetime ,

                                    @IrSelectByMarker             nvarchar(32)  ,
                                    @IrNote                       nvarchar(255) ,

                                    @ObjectPuid                   nvarchar(15),
                                    @ObjectName                   nvarchar(64),
                                    @ObjectDescription            nvarchar(64),

                                    @ObjectFromSiteID             nvarchar(32),
                                    @ObjectOwningSiteID           nvarchar(32),
                                    @ObjectType                   nvarchar(32),
                                    @ObjectClass                  nvarchar(64),
                                    @ObjectCliName                nvarchar(255),

                                    @ObjectOwner                  nvarchar(32),
                                    @ObjectGroup                  nvarchar(32),
                                    @ObjectCreateDate             datetime    ,
                                    @ObjectLastModUser            nvarchar(32),
                                    @ObjectLastModDate            datetime    ,

                                    @ObjectFs_DPNE                nvarchar(255),
                                    @ObjectFs_Vol                 nvarchar(64),
                                    @ObjectFs_NE                  nvarchar(64),

                                    @ObjectUgMaster_PartFamMemName nvarchar(64),
                                    @ObjectUgMaster_Type           nvarchar(32) ,
                                    @ObjectUgMaster_APLevels      int,
                                    @ObjectUgMaster_CompCount     int,
                                    @ObjectUgMaster_FsHitCount    int,

                                    @ObjectReleaseState           nvarchar(32) ,
                                    @ObjectReleaseDate            datetime ,

                                    @ObjectSelectByMarker         nvarchar(32) ,
                                    @ObjectNote                   nvarchar(255) ,

                                    @ImportObjectNewProcStateMask nvarchar(11) ,
                                    @ImportObjectUpdProcStateMask nvarchar(11) ,
                                    @ImportObjectErr              nvarchar(64) ,

                                    @DoImportIr                   bit ,  -- new 22.06.09 = Value = 1 do import Ir
                                    @DoImportDs                   bit ,  -- new 22.06.09 = Value = 1 do import DS

                                    @IrId_r                       uniqueidentifier Output

/* Lastet die IR und DataSets in die Datenbank ein
   wichtig bei Struktur anpassungen ist immer die .. dazugehörigen View auch zu prüfen !!
   @ImportObjectNewProcStateMask, @ImportObjectUpdProcStateMask, 16.05.2008 eingefuegt
   @IrType 20.12.2011 eingefuegt
   */
As
BEGIN

 DECLARE @DataSetId uniqueidentifier
 DECLARE @DataSet_Ir_RelId uniqueidentifier

 DECLARE @DebugPrintOn bit
 set @DebugPrintOn = 0
 DECLARE @MyProcName nvarchar(32)
 set @MyProcName='DataSets_and_IRs_Import'


 if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '----------------------' , '----------------------'
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectPuid' ,        @ObjectPuid
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@ObjectCliName',      @ObjectCliName
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@IrType',             @IrType
  end


 if @DoImportIr = 1
  begin
   -- einlasten IR Info: @IrId_r wird als Ergebnis zurück gegeben
   EXECUTE Object_Import
           @IrPuid,            @IrNameRev,         @IrName2,            @IrDescription,     @IrFromSiteID,
           @IrCliName,         'IR',               @IrOwningSiteID,     @IrType,
           @IrOwner,           @IrGroup,           @IrCreateDate,       @IrLastModUser,     @IrLastModDate,
           '~',                @ObjectFs_Vol,      '~',                 '~',                @ObjectUgMaster_Type,
           @ObjectUgMaster_APLevels,               @ObjectUgMaster_CompCount,               @ObjectUgMaster_FsHitCount,
           @IrReleaseState,    @IrReleaseDate,     @IrSelectByMarker,
           @IrNote,            @ImportObjectNewProcStateMask,
           @ImportObjectUpdProcStateMask,          @ImportObjectErr,
           NULL , '' ,
           @IrId_r Output
  end

 if @DoImportDs = 1 --
  begin
   -- einlasten DS Info: @DataSetId wird als Ergebniss zurück gegeben
   -- @ObjectDescription entnommen da eigendlich nur IR Description und IR PartName verwendet werden
   -- 21.10.2009 10:21 @ObjectDescription wieder eingefuegt da diese doch sinvoll sind und von der IR Descripton abweichen können
   EXECUTE Object_Import
           @ObjectPuid ,       @ObjectName,        '',                  @ObjectDescription, @ObjectFromSiteID,
           @ObjectCliName,     @ObjectClass,       @ObjectOwningSiteID, @ObjectType,
           @ObjectOwner,       @ObjectGroup,       @ObjectCreateDate,   @ObjectLastModUser, @ObjectLastModDate,
           @ObjectFs_DPNE,     @ObjectFs_Vol,      @ObjectFs_NE,        @ObjectUgMaster_PartFamMemName,
           @ObjectUgMaster_Type,                   @ObjectUgMaster_APLevels,                @ObjectUgMaster_CompCount,
           @ObjectUgMaster_FsHitCount,             @ObjectReleaseState, @ObjectReleaseDate, @ObjectSelectByMarker,
           @ObjectNote,        @ImportObjectNewProcStateMask,           @ImportObjectUpdProcStateMask,
           @ImportObjectErr,   @IrId_r , @IrPuid,  @DataSetId Output

   -- Check via @ObjectPuid ob mehrfach Refenzen des selben DS vorhanden sind neu 02.03.2011 --
   -- 07.03.2011 and dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID eingefuegt
   if @ObjectPuid <> '' --
    begin
     DECLARE @NRefsFound int
     SELECT @NRefsFound=count(*) FROM  dbo.Objects_tbl
     WHERE   dbo.Objects_tbl.ObjectPuid = @ObjectPuid
         and dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID

     if @NRefsFound > 1 --
      begin
        -- EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectPuid' , @ObjectPuid
        UPDATE
          dbo.Objects_tbl
        SET
          ImportObjectErr = 'Inf_140_DataSetHasMultibleIrReferences'
        WHERE
              dbo.Objects_tbl.ObjectPuid = @ObjectPuid
          and dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
      end ----------------
    end ------------------
  end ----------------

 if @DoImportDs = 1
  begin
   -- new 22.06.2009 nur wenn DS und IR eingebucht werden dann sind auch die relationen zu erzeugen
   EXECUTE Objects_Ir_Rel_Create @IrId_r ,@DataSetId , @DataSet_Ir_RelId Output
  end
END
GO



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

CREATE Procedure ObjectsUpdate_ProcessStatus
                    (
                    @ObjectDataTableProcStateCol     nvarchar (32) ,     -- Name der Spalte ProcStateCol01..10 ProcStateColRf in dem der Processtatus der Quelltabelle steht

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

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

                    @ObjectSpecialEditRule           nvarchar (64) , -- new 30.1.09  Rule1='UgMaster_Of_Ir' Rule2='SetPStateTo_IR_And_DS'

                    @JobControlRuleName              nvarchar (32) ,

                    @JobControl_ObjectsProcessData_tbl_ID uniqueidentifier , -- Ref auf Datensatz in JobControl_ObjectsProcessData_tbl

                    @ProcessPacketName               nvarchar (64)  ,
                    @ProcessOnSiteID                 nvarchar (32)  ,
                    @ProcessState                    nvarchar (34)  ,   -- beinhaltet den Process Status eines Objektes Size geandert auf 34 23.11.2011 J.Fes
                    @ProcessStateExtendetEdit_SQL    nvarchar (max) ,   -- new 19.07.2012 J.Fes setzt erw. P.Satus via ProcStateCol01='M', ProcStateCol02='M'
                    @ProcessTimeStart                datetime ,
                    @ProcessTimeEnd                  datetime ,
                    @ProcessCMDLine                  nvarchar (255) ,
                    @ProcessResultCode               nvarchar (32)  ,
                    @ProcessResultMsg                nvarchar (255) ,
                    @ProcessResultHasWarning         bit ,
                    @ProcessResultLink               nvarchar (255) ,
                    @ProcessOnComputer               nvarchar (64)  ,
                    @TaskScheduler_tbl_ID            uniqueidentifier , -- ID des referenzierten TaskSheduler Datensatzes in TaskScheduler_tbl
                    @ProcessProcStateCol             nvarchar (32) ,    -- Info: @ObjectDataTableProcStateCol beinhaltet den gleichen Wert wird jedoch aus übersichtsgründen mit verwendet

                    @ObjectCliName_SQL_r             nvarchar (255) Output ,   -- Cli Return neu 15.02.2012 J.Fes
                    @Objects_tbl_ID_SQL_r            uniqueidentifier Output   -- ID  Return neu 15.02.2012 J.Fes
                    )
   /*
   Lastet die Daten der Process verarbeitungen in die Datenbank ein
   wichtig bei Struktur anpassungen ist immer die dazugehörigen View zu prüfen !!
   */
As
BEGIN

 Declare @DebugPrintOn bit
 set @DebugPrintOn = 0

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

 -- ID werte auf NULL setzen wenn diese mit dem Wert '00000000-0000-0000-0000-000000000000' übergeben wurden
 if @JobControl_ObjectsProcessData_tbl_ID = '00000000-0000-0000-0000-000000000000'
     BEGIN
      set @JobControl_ObjectsProcessData_tbl_ID = NULL
     END
 if @TaskScheduler_tbl_ID = '00000000-0000-0000-0000-000000000000'
     BEGIN
      set @TaskScheduler_tbl_ID = NULL
     END

 -- wenn @Objects_tbl_ID gegeben ist dann diese suchen lassen
 if @Objects_tbl_ID IS NOT NULL
    BEGIN
     -- Datensatz in Objects_tbl ndern
     -- Print ('in -1- if @Objects_tbl_ID IS NOT NULL')
     select @ObjectId=Id from Objects_tbl where ID = @Objects_tbl_ID
    END

 -- Abschnitt Objects_tbl bearbeiten --------------------------------------------------------------------------------
 if @DebugPrintOn = 1
     begin
      EXECUTE zzzzzDebugTbl_Empty
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '00-@ObjectSpecialEditRule:' ,  @ObjectSpecialEditRule
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '00-@ObjectName:' ,     @ObjectName
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '01-@ObjectCliName:' ,  @ObjectCliName
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '02-@ObjectFromSiteID:' ,  @ObjectFromSiteID
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '03-@ObjectClass:' ,  @ObjectClass
     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

 -- neu 15.02.2012
 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(64), @ObjectId)
         EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '14-@ObjectId:' ,  @MyValue
       end
     END

 -- neu 15.02.2012
 if @ObjectSpecialEditRule = 'DS_of_IR_viaObjectType_UGMASTER'
     BEGIN
     if @DebugPrintOn = 1
       begin
         EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus', '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(64), @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(64), @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(64), @ObjectId) + ''''
     execute sp_executesql @ExecuteSQLstr

     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(64)
          -- set @ObjectId_tmp=CONVERT(nvarchar(64), @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(64), @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(64), @ObjectId)
          -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '20-@ObjectId:' ,  @ObjectId_tmp
          -- 23.09.2010 13:20  with (nolock) eingefuegt J.Fes
          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(64), @ObjectId) + '''' +
                                '      )' +
                                ' )'
          -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '30-@ExecuteSQLstr:' , @ExecuteSQLstr
         execute sp_executesql @ExecuteSQLstr
        END
    END

 -- Abschnitt JobControl_ObjectsProcessData_tbl  bearbeiten --------------------------------------------------------
 if @ObjectId IS NOT 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

          -- wenn Referenzdatensatz nicht gefunden dann suchen via  ProcessPacketName und ObjectCliName
          if @JobControl_ObjectsProcessData_tbl_ID IS NULL
            BEGIN
             select @JobControl_ObjectsProcessData_tbl_ID=Id from JobControl_ObjectsProcessData_tbl with (nolock)
                    where     [ProcessPacketName] = @ProcessPacketName
                          and [ObjectCliName]     = @ObjectCliName
            END
        END
      -- 04.04.2012 21:29 angepasst damit bei Taskscheduler Objekten der Richtige referenz Datensatz gefunden wird
      -- Else
      --   BEGIN
      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
       -- 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()
        INSERT INTO JobControl_ObjectsProcessData_tbl
                             (ID ,                                   Objects_tbl_ID, ObjectFromSiteID,  ObjectCliName,  ObjectClass,  ObjectName,  JobControlRuleName ,  ProcessPacketName,  ProcessOnSiteID ,  ProcessState ,  ProcessTimeStart,  ProcessTimeEnd,  ProcessCMDLine,  ProcessResultCode,  ProcessResultMsg,  ProcessResultHasWarning,  ProcessResultLink,  ProcessOnComputer,  TaskScheduler_tbl_ID, ProcessProcStateCol  )
                    VALUES   (@JobControl_ObjectsProcessData_tbl_ID, @ObjectId,      @ObjectFromSiteID, @ObjectCliName, @ObjectClass, @ObjectName, @JobControlRuleName , @ProcessPacketName, @ProcessOnSiteID , @ProcessState , @ProcessTimeStart, @ProcessTimeEnd, @ProcessCMDLine, @ProcessResultCode, @ProcessResultMsg, @ProcessResultHasWarning, @ProcessResultLink, @ProcessOnComputer, @TaskScheduler_tbl_ID, @ProcessProcStateCol)
       END
     ELSE
       BEGIN
        --  vorhanden ProcessDatensatz zum Object updaten = Ergebniss einbuchen
        UPDATE JobControl_ObjectsProcessData_tbl
               SET
                   Objects_tbl_ID          = @ObjectId ,            -- einfuegen zur evtl. selbstreperatur
                   ObjectFromSiteID        = @ObjectFromSiteID ,
                   ObjectCliName           = @ObjectCliName ,
                   ObjectClass             = @ObjectClass ,

                   ObjectName              = @ObjectName ,
                   JobControlRuleName      = @JobControlRuleName ,
                   ProcessPacketName       = @ProcessPacketName ,
                   ProcessOnSiteID         = @ProcessOnSiteID ,
                   ProcessState            = @ProcessState ,
                   ProcessTimeStart        = @ProcessTimeStart ,
                   ProcessTimeEnd          = @ProcessTimeEnd ,
                   ProcessCMDLine          = @ProcessCMDLine ,
                   ProcessResultCode       = @ProcessResultCode,
                   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!
               Where ID = @JobControl_ObjectsProcessData_tbl_ID
       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
    END

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

 if @DebugPrintOn = 1
     begin
      set @MyValue = CONVERT(varchar(64), @ObjectId)
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '90-@ObjectId:' ,  @MyValue
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '91-@ObjectCliName_SQL_r:' , @ObjectCliName_SQL_r
      set @MyValue = CONVERT(varchar(64), @Objects_tbl_ID_SQL_r)
      EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '92-@Objects_tbl_ID_SQL_r:' , @MyValue
     end
END
GO


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

CREATE PROCEDURE Objects_tbl_DeleteData @DeleteMethod  nvarchar (64) , -- Delete Methode
                                        @Object_tbl_Id nvarchar (64) , -- Objects_tbl.ID
                                        @ObjectClass   nvarchar (10) , -- IR oder DS
                                        @PDataId       nvarchar (64)   -- Process Datensatz ID kann leer sein

/* new 03.01.2011 J.Fes
   loescht von Object Daten aus den Tabellen:
   -- Objects_tbl
   -- Objects_ir_Rel_tbl
   -- JobControl_ObjectsProcessData_tbl

   -- Example Data: tceng: BgStrukEx-KBG-03_01/A

   -- loesch Methoden:
   -- M0_NON  0 - nichts löschen
   -- M1_PDS  1 - Process data:'selected'
   -- M2_PDA  2 - Process data:'all'
   -- M3_DSS  3 - Dataset(s) and all Process data
   -- M4_IRS  4 - Ir(s) including all Dataset and all Process data
   */
AS
BEGIN

  DECLARE @DebugPrintOn bit
  DECLARE @I          integer
  DECLARE @MyProcName nvarchar(32)
  DECLARE @IrId       nvarchar(64)

  set @DebugPrintOn = 0
  set @MyProcName='Objects_tbl_DeleteData'

  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@DeleteMethod' , @DeleteMethod
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ObjectClass'  , @ObjectClass
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@Object_tbl_Id', @Object_tbl_Id
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@PDataId'      , @PDataId
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@ObjectClass'  , @ObjectClass
  end


  -- Exit wenn keine @Object_tbl_Id gegeben ist
  if @Object_tbl_Id = "" Goto EndeExit

  if @ObjectClass = 'IR'
     begin -- Daten löschen via IR (new 25.01.2011)
      if @DebugPrintOn = 1
      -- Step: delete result of datasets from dbo.JobControl_ObjectsProcessData_tbl
      DELETE FROM
         dbo.JobControl_ObjectsProcessData_tbl
       WHERE
         JobControl_ObjectsProcessData_tbl.Objects_tbl_ID IN
         (SELECT DS.Id FROM dbo.Objects_tbl
           IR INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
              INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
           WHERE IR.Id = @Object_tbl_Id)

      -- Step: delete Datasets of Ir from Objects_tbl
      DELETE FROM
         dbo.Objects_tbl
       where dbo.Objects_tbl.id in
       (SELECT
         DS.Id
        FROM
          dbo.Objects_tbl IR
          INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
          INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
        WHERE
          IR.Id = @Object_tbl_Id)

      -- Step: delete datasets Relation from dbo.Objects_Ir_Rel_tbl
      DELETE FROM
        dbo.Objects_Ir_Rel_tbl
       WHERE
        dbo.Objects_Ir_Rel_tbl.IrId = @Object_tbl_Id

      -- Step: delete IR
      DELETE FROM
        dbo.Objects_tbl
       WHERE
        Id=@Object_tbl_Id
     end   -- ende Daten löschen via IR

  else  --------- !!!!!!!!!!!!! ELSE !!!!!!!!! -----------------------------

 		  begin -- Daten löschen ausgehend von einem DataSet

       if @DeleteMethod = "M1_PDS" and @PDataId <> ""
          begin
            DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
              WHERE dbo.JobControl_ObjectsProcessData_tbl.ID = @PDataId

            -- Step: Mark_lastRes durchführen
            --       Info: die nachfolgende Aktion muss auf @Object_tbl_Id
            --              angewendet werden da ja von diesem Dataset die
            --              ProcessDaten verändert werden können
            Execute ObjectsUpdate_ProcessStatus_Mark_LastRes @Object_tbl_Id
          end

       if @DeleteMethod = "M2_PDA"  or @DeleteMethod = "M3_DSS" or @DeleteMethod = "M4_IRS"
            begin
            -- Info dieser Vorgang muss bei den den Methoden M2_ M3_ und M4_ durchgefuehrt werden (05.01.2011 21:15 J.Fes)
            -- Step: delete Dataset All Process Data
            DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
             WHERE dbo.JobControl_ObjectsProcessData_tbl.ID in
             (SELECT
                dbo.JobControl_ObjectsProcessData_tbl.ID
              FROM
                dbo.Objects_tbl
                INNER JOIN dbo.JobControl_ObjectsProcessData_tbl ON (dbo.Objects_tbl.Id = dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID)
              WHERE
                dbo.Objects_tbl.Id = @Object_tbl_Id)
          end

       -- delte TC-Dataset Data  new 04.01.2011 J.Fes
       if @DeleteMethod = "M3_DSS"
          begin
            -- Step: delete Dataset-DataRow
            DELETE FROM Objects_tbl WHERE Id=@Object_tbl_Id
          end

       if @DeleteMethod = "M4_IRS"
          begin

           -- IrId ermitteln
           SELECT
             @IrId = dbo.Objects_Ir_Rel_tbl.IrId
           FROM
             dbo.Objects_tbl
             INNER JOIN dbo.Objects_Ir_Rel_tbl ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
           WHERE
             dbo.Objects_tbl.Id = @Object_tbl_Id -- '0285c568-7177-4f14-9879-e9d60ec81e86';

           -- Step: delete Relation from dbo.Objects_Ir_Rel_tbl
           DELETE FROM dbo.Objects_Ir_Rel_tbl
            WHERE dbo.Objects_Ir_Rel_tbl.ObjectId = @Object_tbl_Id

           -- Step: delete Dataset-DataRow
           DELETE FROM Objects_tbl WHERE Id=@Object_tbl_Id

           -- check ob noch ein weitere Datset auf IR vorhanden ist wenn nicht dann IR löschen new 05.02.2011 15:44  J.Fes
           -- Anzahl Referenzen zu IRID ermitteln
           select @I = count(*) from dbo.Objects_Ir_Rel_tbl
             where  Objects_Ir_Rel_tbl.IrId = @IrId ;
           if @DebugPrintOn = 1
              begin
                EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.1-loeschen @IrId:', @IrId
                EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.2-loeschen @I:', @I
              end

           -- wenn kein Element mehr vorhanden dann auch IR DS löschen
           if @I = 0
            begin
              if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.3-loeschen YES @IR:', @IrId
              DELETE FROM Objects_tbl WHERE Id=@IrId
            end
            else
            begin
              if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.3-loeschen NO:', @IrId
            end
          end
    end  -- Daten löschen via DataSet


  EndeExit:

END
GO




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


Create Procedure Objects_tbl_GetId_IR_of_DS  @DSId nvarchar(64) ,
                                             @IrId nvarchar(64) Output
-- gibt die IrId zu einem DS zurück
As
BEGIN
  SELECT @IrId=IR.Id
  FROM
    dbo.Objects_tbl with (nolock)
    INNER JOIN dbo.Objects_Ir_Rel_tbl with (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
    INNER JOIN dbo.Objects_tbl IR with (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = IR.Id)
  WHERE
    dbo.Objects_tbl.Id = @DSId -- '7fc5c1cf-4678-4557-8055-f589b96ea2c8'
END
GO

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


Create Procedure Objects_tbl_GetId_UgMaster_of_DS @DSId nvarchar(64) ,
                                                  @DSUgMasterId nvarchar(64) Output
/* gibt die DsUgMasterId zu einem DS zurück  neu erstellt 20.06.2010 J.Fes */

As
BEGIN
 SELECT
   @DSUgMasterId=Id
 FROM
   Objects_tbl_GetId_UgMaster_of_DS_UDF(@DSId)
END
GO
--------------------------------------------------------------------------------
Print '-'
Print '-- [Objects_tbl_GetId_UgMaster_of_DS_UDF] -------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_GetId_UgMaster_of_DS_UDF" and type = "IF")
DROP FUNCTION Objects_tbl_GetId_UgMaster_of_DS_UDF
GO

--- Gibt die ID des UgMaster datensatzes zu einem Dataset zurueck
CREATE FUNCTION Objects_tbl_GetId_UgMaster_of_DS_UDF(
                              @ObjectId  nvarchar(64)  -- ID des Datasets zu dem die UG Master Datensatz ID gesucht wird
        )
RETURNS TABLE AS
Return (
 SELECT -- Top 1
   Objects_tbl1.Id as Id
 FROM
   dbo.Objects_tbl WITH (nolock)
   INNER JOIN dbo.Objects_Ir_Rel_tbl with (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
   INNER JOIN dbo.Objects_Ir_Rel_tbl Objects_Ir_Rel_tbl1 with (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = Objects_Ir_Rel_tbl1.IrId)
   LEFT OUTER JOIN dbo.Objects_tbl Objects_tbl1 with (nolock) ON (Objects_Ir_Rel_tbl1.ObjectId = Objects_tbl1.Id)
 WHERE
   dbo.Objects_tbl.Id = @ObjectId AND
   Objects_tbl1.ObjectType = 'UGMASTER'
)
GO


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

CREATE PROCEDURE Objects_tbl_UpdateProcStateCol @ObjectId    nvarchar(64) , -- ID des zu bearbeitenden Datensatzes in Objects_tbl
                                                @ColumeName  nvarchar(32) , -- Name des Columes
                                                @ValueNew    nvarchar(MAX) -- neuer Wert
AS
BEGIN

  -- UPDATE Objects_tbl  SET ProcStateColRf=@ValueNew Where ID = @ObjectId
  -- Messung 2 End Edit ALL rows:[Objects_UltraGrid] Colume:[ProcStateColRf] duration:[16,45 [sec]]
                                                   -- 06.09.2011 18:06  -- or @ColumeName = 'ObjectSelectByMarker' entfernt
--    if substring(@ColumeName,1,12) = 'ProcStateCol' -- or @ColumeName = 'ObjectSelectByMarker'
--    begin
--      -- Func.Beschreibung: Wenn MyColume=@ColumeName dann Set MyColume=@ValueNew
--      --              Wenn nicht MyColume=@ColumeName dann Set [MyColume]=[MyColume].Value
--      UPDATE Objects_tbl
--      SET ProcStateColRf = CASE @ColumeName WHEN 'ProcStateColRf' THEN @ValueNew ELSE ProcStateColRf END,
--          ProcStateCol01 = CASE @ColumeName WHEN 'ProcStateCol01' THEN @ValueNew ELSE ProcStateCol01 END,
--          ProcStateCol02 = CASE @ColumeName WHEN 'ProcStateCol02' THEN @ValueNew ELSE ProcStateCol02 END,
--          ProcStateCol03 = CASE @ColumeName WHEN 'ProcStateCol03' THEN @ValueNew ELSE ProcStateCol03 END,
--          ProcStateCol04 = CASE @ColumeName WHEN 'ProcStateCol04' THEN @ValueNew ELSE ProcStateCol04 END,
--          ProcStateCol05 = CASE @ColumeName WHEN 'ProcStateCol05' THEN @ValueNew ELSE ProcStateCol05 END,
--          ProcStateCol06 = CASE @ColumeName WHEN 'ProcStateCol06' THEN @ValueNew ELSE ProcStateCol06 END,
--          ProcStateCol07 = CASE @ColumeName WHEN 'ProcStateCol07' THEN @ValueNew ELSE ProcStateCol07 END,
--          ProcStateCol08 = CASE @ColumeName WHEN 'ProcStateCol08' THEN @ValueNew ELSE ProcStateCol08 END,
--          ProcStateCol09 = CASE @ColumeName WHEN 'ProcStateCol09' THEN @ValueNew ELSE ProcStateCol09 END,
--          ProcStateCol10 = CASE @ColumeName WHEN 'ProcStateCol10' THEN @ValueNew ELSE ProcStateCol10 END
--             -- 06.09.2011 18:06  -- or @ColumeName = 'ObjectSelectByMarker' entfernt
--             -- ObjectSelectByMarker = CASE @ColumeName WHEN 'ObjectSelectByMarker' THEN @ValueNew ELSE ObjectSelectByMarker END
--      Where ID = @ObjectId
--    end
--    else
--    begin
--      DECLARE @ExecuteSQLstr nvarchar(max)
--      set @ExecuteSQLstr = 'UPDATE Objects_tbl SET [' + @ColumeName + ']=''' + @ValueNew + ''' where Objects_tbl.Id=''' + CONVERT(nvarchar(64), @ObjectId) + ''''
--      execute sp_executesql @ExecuteSQLstr
--      -- EXECUTE zzzzzDebugTbl_Empty
--      -- EXECUTE zzzzzDebugTbl_AddDataSet 'Objects_tbl_UpdateProcStateCol' , '10-@ExecuteSQLstr:' ,  @ExecuteSQLstr
--      -- Messung 1 End Edit ALL rows:[Objects_UltraGrid] Colume:[ProcStateColRf] duration:[13,63 [sec]]
--    end

     DECLARE @ExecuteSQLstr nvarchar(max)
     set @ExecuteSQLstr = 'UPDATE Objects_tbl SET [' + @ColumeName + ']=''' + @ValueNew + ''' where Objects_tbl.Id=''' + CONVERT(nvarchar(64), @ObjectId) + ''''
     execute sp_executesql @ExecuteSQLstr

--      UPDATE Objects_tbl SET @ColumeName=@ValueNew where Objects_tbl.Id=@ObjectId
end
Go

------------- ##################################### ----------------------------
------------- ##################################### ----------------------------


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



CREATE PROCEDURE Objects_tbl_UpdateColumeValue_by_ID @ObjectId              nvarchar(64) , -- ID des zu bearbeitenden Datensatzes in Objects_tbl
                                                     @ColumeName            nvarchar(32) , -- Name des Columes
                                                     @ValueNew              nvarchar(MAX), -- neuer Wert
                                                     @ObjectSpecialEditRule nvarchar(64)   -- erweiterte Edit funktionen
/*
   Description: Updaten die Daten des Columes in der Objects_tbl
   Angepasst  : 20.06.2010 18:38 J.Fes
*/

AS
BEGIN

  -- als erstes den Datensatz zur @ObjectId bearbeiten
  EXECUTE Objects_tbl_UpdateProcStateCol @ObjectId, @ColumeName, @ValueNew

--   DECLARE @DebugPrintOn bit
--   set @DebugPrintOn = 1
--   DECLARE @MyProcName nvarchar(32)
--   set @MyProcName='Objects_tbl_UpdateColumeValue_by_ID'
--
--
--   if @DebugPrintOn = 1
--   begin
--    -- Clear Debug Table
--    EXECUTE zzzzzDebugTbl_Empty
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectId',   @ObjectId
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ColumeName', @ColumeName
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@ValueNew',   @ValueNew
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@ObjectSpecialEditRule' , @ObjectSpecialEditRule
--   end
--

--     Dokiumentation:
--     Public Enum ObjectSpecialEditRule_enum
--         NoSecRule
--         UgMaster_Of_Ir
--         SetPStateTo_IR_And_DS
--         SetPStateTo_All_DS_Off_UGMASTER
--         SetPStateTo_UgMaster_Off_DS
--     End Enum

  -- Option auch den Status der IR des Dataset ändern
  if @ObjectSpecialEditRule = 'SetPStateTo_IR_And_DS'
  begin
     DECLARE @IrId nvarchar(64)
     set @IrId = NULL
     EXECUTE Objects_tbl_GetId_IR_of_DS @ObjectId, @IrId Output
     -- EXECUTE zzzzzDebugTbl_AddDataSet 'Objects_tbl_UpdateColumeValue_by_ID' , N'02-@IrId:' , @IrId
     if @IrId is not NULL and @IrId <> @ObjectId
      begin
        -- wenn IR gefunden dann Updaten der Daten
        EXECUTE Objects_tbl_UpdateProcStateCol @IrId, @ColumeName, @ValueNew
      end
  end

  -- Option auch den Status des UgMaster Objektes zum Dataset ändern
  if @ObjectSpecialEditRule = 'SetPStateTo_UgMaster_Off_DS'
  begin
     -- Clear Debug Table
     DECLARE @DSUgMasterId nvarchar(64)
     set @DSUgMasterId = NULL
     EXECUTE Objects_tbl_GetId_UgMaster_of_DS @ObjectId, @DSUgMasterId Output
     if @DSUgMasterId is not NULL and @DSUgMasterId <> @ObjectId
      begin
       EXECUTE Objects_tbl_UpdateProcStateCol @DSUgMasterId, @ColumeName, @ValueNew
      end
      return
  end
  
END
GO
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

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



CREATE PROCEDURE Objects_tbl_Statistic
           (
           @ObjectFromSiteID   nvarchar(32) , -- Statistic for Site
           @ObjectLastModDateAfter DATETIME ,
           @IrsCount           int Output ,   -- Returns IrsCount
           @DsCount            int Output ,
           @NxMasterCount      int Output ,
           @NxNonMasterCount   int Output
           )


/* new 05.03.2011 19:24 J.Fes
   Returns Site Statistic
   */
AS
BEGIN



  DECLARE @DebugPrintOn bit
  DECLARE @I          integer
  DECLARE @MyProcName nvarchar(32)
  DECLARE @IrId       nvarchar(64)

  set @DebugPrintOn = 0
  set @MyProcName='Objects_tbl_Statistic'

  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectFromSiteID' , @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ObjectLastModDateAfter' , @ObjectLastModDateAfter
  end

  Select @IrsCount=Count(*) From dbo.Objects_tbl
   where     dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
         and dbo.Objects_tbl.ObjectClass = 'IR'
         and dbo.Objects_tbl.ObjectLastModDate > @ObjectLastModDateAfter


  Select @DSCount=Count(*) From dbo.Objects_tbl
   where     dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
         and dbo.Objects_tbl.ObjectClass = 'DS'
         and dbo.Objects_tbl.ObjectLastModDate > @ObjectLastModDateAfter


  Select @NxMasterCount=Count(*) From dbo.Objects_tbl
   where     dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
         and dbo.Objects_tbl.ObjectClass = 'DS'
         and dbo.Objects_tbl.ObjectType =  'UGMASTER'
         and dbo.Objects_tbl.ObjectLastModDate > @ObjectLastModDateAfter

  set @NxNonMasterCount = @DsCount - @NxMasterCount


  EndeExit:

END
GO

---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

-- Print ''
-- Print ''
-- Print ''
-- Print '----------------------------------------------------'
-- Print ' Test Procedures ObjectsUpdate_ProcessStatus_Mark_LastRes'
-- Print '----------------------------------------------------'
-- GO
--
-- -- Execute ObjectsUpdate_ProcessStatus_Mark_LastRes '' -- '63bdd795-be19-4a07-93fb-f9260a8d3787' -- ''
-- GROB DB
-- Execute ObjectsUpdate_ProcessStatus_Mark_LastRes '51EA3694-A4BF-4934-89A6-DA0F2F51C5AA'
--   EXECUTE zzzzzDebugTbl_Empty
--   EXECUTE  Objects_tbl_UpdateColumeValue_by_ID '30157A93-358E-4525-AA94-0382A24B6368', 'ProcStateColRf', 'XX', 'SetPStateTo_UgMaster_Off_DS'
-- goto ende

/*
 Declare   @ObjectFromSiteID nvarchar(32)
 Declare   @IrsCount         int
 Declare   @DsCount          int
 Declare   @NxMasterCount    int
 Declare   @NxNonMasterCount int


 set @ObjectFromSiteID='tceng'
 EXECUTE Objects_tbl_Statistic @ObjectFromSiteID, @IrsCount Output, @DSCount Output , @NxMasterCount Output , @NxNonMasterCount Output
 Print ""
 Print "IrsCount:[" + convert(nvarchar,@IrsCount) + "]"
 Print " DsCount:[" + convert(nvarchar,@DsCount)  + "]"
 Print "NxMasterCount:[" + convert(nvarchar,@NxMasterCount)  + "]"
 Print "NxNonMasterCount:[" + convert(nvarchar,@NxNonMasterCount)  + "]"
 */

ende:

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

