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

Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO


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 '-- [Objects_tbl_ObjShortInfo] -------------------------------------------'
--
If Exists (select Name from sysobjects where Name = "Objects_tbl_ObjShortInfo" and type = "P")
                                      Drop Procedure Objects_tbl_ObjShortInfo
GO

-- #New: 15.04.2013 / J.Fes
Create Procedure Objects_tbl_ObjShortInfo  @ObjectPuid       nvarchar(32),
                                           @ObjectFromSiteID nvarchar(32)
-- gibt den Datensatz zur gesuchten ObjectPuid zurueck
As
BEGIN
     select Id ,
            ObjectOwningSiteID,
            ObjectLastModDate,
            ObjectOwningSiteID,
            ObjectUgMaster_Type ,
            ObjectUgMaster_CompCount ,
            ObjectUgMaster_APLevels
       from Objects_tbl with (nolock)
        where (ObjectPuid      = @ObjectPuid
          and ObjectFromSiteID = @ObjectFromSiteID)
end
go


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

-- 03.04.2013 add @Id  uniqueidentifier,
Create Procedure Objects_tbl_Object_GetDS  @Id               uniqueidentifier = null,  -- #New: 05.04.2013
                                           @ObjectCliName    nvarchar(255),
                                           @ObjectName       nvarchar(255),  -- new 28.01.2011 09:48 J.Fes
                                           @ObjectPuid       nvarchar(32),   -- change A.Becker 22.08.2012: Puid Feld von 15 auf 32 Zeichen erweitert
                                           @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 is not null
    begin
     -- suche via @Id  #New: 05.04.2013
     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 '-- [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 '-- [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(32) ,     -- change A.Becker 22.08.2012: Puid Feld von 15 auf 32 Zeichen erweitert
                                           @ObjectCliName    nvarchar(255) ,
                                           @ObjectFromSiteID nvarchar(32) ,
                                           @ObjectClass      nvarchar(10) ,
                                           @IrId             uniqueidentifier ,
                                           @IrPuid           nvarchar(32),      -- change A.Becker 22.08.2012: Puid Feld von 15 auf 32 Zeichen erweitert
                                           @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
-- 15.04.2013 J.Fes anpassungen siehe weiter unten
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 , '10.1 Get:@Id @IrId=NULL @Id=', @Id

       select @Id=Id from Objects_tbl WITH (nolock)
                 where ObjectPuid       = @ObjectPuid
                   and ObjectFromSiteID = @ObjectFromSiteID
                   and ObjectClass      = @ObjectClass

      -- if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '10.2 Ret:@Id @IrId=NULL @Id=', @Id
     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
         */
      /*
       if @DebugPrintOn = 1
        begin
         EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.1 Get:@Id @IrId<>NULL @ObjectPuid=', @ObjectPuid
         EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.2 Get:@Id @IrId<>NULL @ObjectFromSiteID=', @ObjectFromSiteID
         EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.3 Get:@Id @IrId<>NULL @ObjectClass=', @ObjectClass
         EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.4 Get:@Id @IrId<>NULL @IrId=', @IrId
        end
      */
       SELECT @Id=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

       /*
       if @DebugPrintOn = 1
       begin
        EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.5 Ret:@Id @IrId<>NULL @Id=', @Id
       end
       */
     end  -- *************************
   end

   -- wenn bis hier nicht gefunden wurde dann via Objects_tbl_Object_GetId nach der ID suchen
   if @Id is Null
   begin -- *************************
     -- ermittelt das Objekt via @ObjectCliName wenn:
     -- das Object über vorherige Abfrage nicht gefunden wurde
     -- 23.09.2010 wieder eingefuegt
     -- if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '30.1 Get:@Id @ObjectPuid = NULL @ObjectCliName=', @ObjectCliName
     EXECUTE Objects_tbl_Object_GetId @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @Id Output
     -- if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '30.2 Get:@Id @ObjectPuid = NULL @Id=', @Id
   end -- *************************

   -- if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '40.2 Final @ID', @Id

END
GO




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 '-- [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 '-- [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 '-- [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 '-- [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 '-- [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(32),   -- change 22.08.2012 a.becker (size from 15 to 32)
                               @ObjectName                    nvarchar(64),
                               @ObjectName2                   nvarchar(32),
                               @ObjectDescription             nvarchar(255),

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

                               @ObjectOwningSiteID            nvarchar(32),
                               @ObjectExportRecord            nvarchar(255) = null,   -- #New: 16.08.2013/J.Fes
                               @ObjectExportRecordDate        datetime = null, -- #New 20.08.2013/J.Fes
                               @ObjectType                    nvarchar(32),    -- UGMASTER UGPART ..

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

                               @ObjectFs_DPNE                 nvarchar(255),
                               @ObjectFs_Vol                  nvarchar(64),
                               @ObjectFs_NE                   nvarchar(64),
                               @ObjectFs_SizeBytes            bigint = null,          -- new 22.08.2012 a.becker angepasst 09.11.2012 10:50
                               @ObjectFs_HashCode             nvarchar(64),    -- new 22.08.2012 a.becker
                               @ObjectSaveVersion             nvarchar(64),    -- new 22.08.2012 a.becker

                               @ObjectUgMaster_PartFamMemName  nvarchar(64),
                               @ObjectUgMaster_Type            nvarchar(32),  -- SP AP
                               @ObjectUgMaster_APLevels        int,
                               @ObjectUgMaster_CompCount       int,
                               @ObjectUgMaster_CompCountUnique int,           -- new 22.08.2012 a.becker
                               @ObjectUgMaster_FsHitCount      int,

                               @ObjectReleaseState            nvarchar(32) = null,
                               @ObjectReleaseDate             datetime = null,

                               @ObjectSelectByMarker          nvarchar(32),
                               @ObjectNote                    nvarchar(255),
                               @ObjectPrjIds                  nvarchar(255),   -- #New: 01.04.2013 J.Fes

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

                               @IrId                          uniqueidentifier,
                               @IrPuid                        nvarchar(32),     -- change A.Becker 22.08.2012: Puid Feld von 15 auf 32 Zeichen erweitert
                               @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
-- 22.08.2012 A.Becker: @ObjectFs_SizeBytes, @ObjectFs_HashCode, @ObjectSaveVersion und @ObjectUgMaster_CompCountUnique hinzugefügt
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
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@ObjectClass' ,    @ObjectClass
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@ObjectReleaseDate' ,  @ObjectReleaseDate
 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


  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 ,
                               ObjectExportRecord ,
                               ObjectExportRecordDate ,
                               ObjectType ,

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

                               ObjectFs_DPNE ,
                               ObjectFs_Vol ,
                               ObjectFs_NE ,
                               ObjectFs_SizeBytes , -- new 22.08.2012 a.becker
                               ObjectFs_HashCode ,  -- new 22.08.2012 a.becker
                               ObjectSaveVersion ,   -- new 22.08.2012 a.becker

                               ObjectUgMaster_PartFamMemName ,
                               ObjectUgMaster_Type ,
                               ObjectUgMaster_APLevels ,
                               ObjectUgMaster_CompCount ,
                               ObjectUgMaster_CompCountUnique , -- new 22.08.2012 a.becker
                               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 ,
                               @ObjectExportRecord ,
                               @ObjectExportRecordDate ,
                               @ObjectType ,
--                                entnommen J.Fes/ 10.01.2012
--                                @ObjectIrType ,

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

                               @ObjectFs_DPNE ,
                               @ObjectFs_Vol ,
                               @ObjectFs_NE ,
                               @ObjectFs_SizeBytes ,  -- new 22.08.2012 a.becker
                               @ObjectFs_HashCode ,   -- new 22.08.2012 a.becker
                               @ObjectSaveVersion ,    -- new 22.08.2012 a.becker

                               @ObjectUgMaster_PartFamMemName ,
                               @ObjectUgMaster_Type ,
                               @ObjectUgMaster_APLevels ,
                               @ObjectUgMaster_CompCount ,
                               @ObjectUgMaster_CompCountUnique , -- new 22.08.2012 a.becker
                               @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:' , @ObjectId -- '@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 ,
                 ObjectExportRecord = @ObjectExportRecord ,
                 ObjectExportRecordDate = @ObjectExportRecordDate ,
                 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
                 ObjectPrjIds       = @ObjectPrjIds , -- 01.04.2013 eingefuegt JFes

                 ObjectFs_DPNE      = @ObjectFs_DPNE ,
                 ObjectFs_Vol       = @ObjectFs_Vol ,
                 ObjectFs_NE        = @ObjectFs_NE ,
                 ObjectFs_SizeBytes = @ObjectFs_SizeBytes ,  -- new 22.08.2012 a.becker
                 ObjectFs_HashCode  = @ObjectFs_HashCode ,   -- new 22.08.2012 a.becker
                -- ObjectSaveVersion   = @ObjectSaveVersion ,    -- new 22.08.2012 a.becker Rem entnommen damit diese Daten nicht Updatet werden

                 ObjectUgMaster_PartFamMemName  = @ObjectUgMaster_PartFamMemName ,
                 ObjectUgMaster_Type            = @ObjectUgMaster_Type ,
                 ObjectUgMaster_APLevels        = @ObjectUgMaster_APLevels ,
                 ObjectUgMaster_CompCount       = @ObjectUgMaster_CompCount ,
                 ObjectUgMaster_CompCountUnique = @ObjectUgMaster_CompCountUnique , -- new 22.08.2012 a.becker
                 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 '-- [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), -- = ItemID
                                    @ItemPuid                     nvarchar(32) = null, -- new 14.04.2013 J.Fes

                                    -- #New: 16.08.2013/J.Fes
                                    @ItemType                     nvarchar(32) = null,
                                    @ItemName2                    nvarchar(32) = null,
                                    @ItemDescription              nvarchar(255) = null,

                                    @ItemCliName                  nvarchar(128) = null,
                                    @ItemOwningSiteID             nvarchar(32) = null,
                                    @ItemExportRecord             nvarchar(255) = null,
                                    @ItemExportRecordDate         datetime = null,

                                    @ItemOwner                    nvarchar(32) = null,
                                    @ItemGroup                    nvarchar(32) = null,
                                    @ItemCreateDate               datetime = null,
                                    @ItemLastModUser              nvarchar(128) = null,
                                    @ItemLastModDate              datetime = null,

                                    @ItemReleaseState             nvarchar(32) = null,
                                    @ItemReleaseDate              datetime = null,
                                    -- #New: 16.08.2013/J.Fes

                                    @IrRev                        nvarchar(32),
                                    @IrPuid                       nvarchar(32),      -- change 22.08.2012 a.becker (size from 15 to 32)

                                    @IrNameRev                    nvarchar(64),      -- = IR ID
                                    @IrType                       nvarchar(32),
                                    @IrName2                      nvarchar(32),
                                    @IrDescription                nvarchar(255),

                                    @IrCliName                    nvarchar(128),
                                  --  @IrFromSiteID                 nvarchar(32),  -- entfernen 19.08.2013
                                    @IrOwningSiteID               nvarchar(32),
                                    @IrExportRecord               nvarchar(255) = null, -- #New: 16.08.2013/J.Fes
                                    @IrExportRecordDate           datetime = null,

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

                                    @IrReleaseState               nvarchar(32) = null,
                                    @IrReleaseDate                datetime = null,

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

                                    @ObjectPuid                   nvarchar(32),  -- change 22.08.2012 a.becker (size from 15 to 32)
                                    @ObjectName                   nvarchar(64),
                                    @ObjectDescription            nvarchar(64),

                                    @ObjectFromSiteID             nvarchar(32),
                                    @ObjectOwningSiteID           nvarchar(32),
                                    @ObjectExportRecord           nvarchar(255) = null, -- #New 16.08.2013
                                    @ObjectExportRecordDate       datetime = null,
                                    @ObjectType                   nvarchar(32),
                                    @ObjectClass                  nvarchar(64),
                                    @ObjectCliName                nvarchar(255),

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

                                    @ObjectFs_DPNE                nvarchar(255),
                                    @ObjectFs_Vol                 nvarchar(64),
                                    @ObjectFs_NE                  nvarchar(64),
                                    @ObjectFs_SizeBytes           bigint = null,           -- new 22.08.2012 a.becker
                                    @ObjectFs_HashCode            nvarchar(64),  -- new 22.08.2012 a.becker
                                    @ObjectSaveVersion            nvarchar(64),  -- new 22.08.2012 a.becker

                                    @ObjectUgMaster_PartFamMemName  nvarchar(64),
                                    @ObjectUgMaster_Type            nvarchar(32) ,
                                    @ObjectUgMaster_APLevels        int,
                                    @ObjectUgMaster_CompCount       int,
                                    @ObjectUgMaster_CompCountUnique int,         -- new 22.08.2012 a.becker
                                    @ObjectUgMaster_FsHitCount      int,

                                    @ObjectReleaseState           nvarchar(32) = null ,
                                    @ObjectReleaseDate            datetime = null ,

                                    @ObjectSelectByMarker         nvarchar(32) ,
                                    @ObjectNote                   nvarchar(255) ,
                                    @ObjectPrjIds                 nvarchar(255) , -- new 01.04.2013 J.Fes

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

                                    @DoImportItem                 bit ,  -- #new 16.08.2013/J.fes = Value = 1 do import Item Data
                                    @DoImportIr                   bit ,  -- new 22.06.09 = Value = 1 do import Ir
                                    @DoImportDs                   bit ,  -- new 22.06.09 = Value = 1 do import DS

                                    @ItemId_r                     uniqueidentifier = null Output, -- #New: 16.08.2013/J.Fes
                                    @IrId_r                       uniqueidentifier = null 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_r uniqueidentifier
 DECLARE @DataSet_Ir_RelId uniqueidentifier
 DECLARE @DataSet_ItemIr_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
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '05-@ItemPuid',           @ItemPuid
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '06-@DoImportItem',       @DoImportItem
  end

 if @DoImportItem = 1
  begin

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '11-@ItemName',             @ItemName
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '12-@ItemCliName',          @ItemCliName
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '13-@ItemReleaseDate',      @ItemReleaseDate
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '14-@ItemExportRecord',     @ItemExportRecord
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '15-@ItemExportRecordDate', @ItemExportRecordDate
  end
   -- einlasten IR Info: @IrId_r wird als Ergebnis zurück gegeben
   -- 22.08.2012 A.Becker: @ObjectFs_SizeBytes, @ObjectFs_HashCode, @ObjectSaveVersion und @ObjectUgMaster_CompCountUnique hinzugefügt
   EXECUTE Object_Import
           @ItemPuid,          @ItemName,          @ItemName2,          @ItemDescription,   @ObjectFromSiteID,
           @ItemCliName,       'I',                @IrOwningSiteID,     @ItemExportRecord,  @ItemExportRecordDate, @ItemType,
           @ItemOwner,         @ItemGroup,         @ItemCreateDate,     @ItemLastModUser,   @ItemLastModDate,
   -- DOC: @ObjectFs_DPNE      @ObjectFs_Vol       @ObjectFs_NE         @ObjectFs_SizeBytes @ObjectFs_HashCode   @ObjectSaveVersion    @ObjUgM_PartFamName @ObjectUgMaster_Type
           '~',                @ObjectFs_Vol,      '~',                 NULL,               '~',                 '~',                 '~',                @ObjectUgMaster_Type,
           @ObjectUgMaster_APLevels,               @ObjectUgMaster_CompCount,               @ObjectUgMaster_CompCountUnique,         @ObjectUgMaster_FsHitCount,
           @ItemReleaseState,  @ItemReleaseDate,   '',
   -- #Doc : 19.08.2013 @ObjectPrjIds der Items werden nicht (!noch nicht!) übertragen
           '',                 '',
           @ImportObjectNewProcStateMask,          @ImportObjectUpdProcStateMask,
           @ImportObjectErr,
           NULL , '' ,
           @ItemId_r Output

   if @DebugPrintOn = 1
   begin
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '16-@ItemId_r',        @ItemId_r
   end
  end


 if @DoImportIr = 1
  begin

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '21-@IrPuid',             @IrPuid
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '22-@IrNameRev',          @IrNameRev
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '23-@IrCliName',          @IrCliName
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '24-@IrReleaseDate',      @IrReleaseDate
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '25-@IrExportRecord',     @IrExportRecord
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '26-@IrExportRecordDate', @IrExportRecordDate
  end


   -- einlasten IR Info: @IrId_r wird als Ergebnis zurück gegeben
   -- 22.08.2012 A.Becker: @ObjectFs_SizeBytes, @ObjectFs_HashCode, @ObjectSaveVersion und @ObjectUgMaster_CompCountUnique hinzugefügt
   EXECUTE Object_Import
           @IrPuid,            @IrNameRev,         @IrName2,            @IrDescription,     @ObjectFromSiteID,
           @IrCliName,         'IR',               @IrOwningSiteID,     @IrExportRecord,    @IrExportRecordDate, @IrType,
           @IrOwner,           @IrGroup,           @IrCreateDate,       @IrLastModUser,     @IrLastModDate,
   -- DOC: @ObjectFs_DPNE      @ObjectFs_Vol       @ObjectFs_NE         @ObjectFs_SizeBytes @ObjectFs_HashCode   @ObjectSaveVersion    @ObjUgM_PartFamName @ObjectUgMaster_Type
           '~',                @ObjectFs_Vol,      '~',                 NULL,               '~',                 '~',                 '~',                @ObjectUgMaster_Type,
           @ObjectUgMaster_APLevels,               @ObjectUgMaster_CompCount,               @ObjectUgMaster_CompCountUnique,         @ObjectUgMaster_FsHitCount,
           @IrReleaseState,    @IrReleaseDate,     @IrSelectByMarker,
           @IrNote,            @ObjectPrjIds,
           @ImportObjectNewProcStateMask,          @ImportObjectUpdProcStateMask,
           @ImportObjectErr,
           NULL , '' ,
           @IrId_r Output

   if @DebugPrintOn = 1
   begin
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '29-@IrId_r',        @IrId_r
   end

  end

 if @DoImportDs = 1
  begin
   -- einlasten DS Info: @DataSetId_r 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
   -- 22.08.2012 A.Becker: @ObjectFs_SizeBytes, @ObjectFs_HashCode, @ObjectSaveVersion und @ObjectUgMaster_CompCountUnique hinzugefügt
   EXECUTE Object_Import
           @ObjectPuid ,       @ObjectName,        '',                  @ObjectDescription,  @ObjectFromSiteID,
           @ObjectCliName,     @ObjectClass,       @ObjectOwningSiteID, @ObjectExportRecord, @ObjectExportRecordDate, @ObjectType,
           @ObjectOwner,       @ObjectGroup,       @ObjectCreateDate,   @ObjectLastModUser,  @ObjectLastModDate,
           @ObjectFs_DPNE,     @ObjectFs_Vol,      @ObjectFs_NE,        @ObjectFs_SizeBytes, @ObjectFs_HashCode,         @ObjectSaveVersion,              @ObjectUgMaster_PartFamMemName,
           @ObjectUgMaster_Type,                   @ObjectUgMaster_APLevels,                 @ObjectUgMaster_CompCount,  @ObjectUgMaster_CompCountUnique,
           @ObjectUgMaster_FsHitCount,             @ObjectReleaseState, @ObjectReleaseDate,  @ObjectSelectByMarker,
           @ObjectNote,        @ObjectPrjIds,
           @ImportObjectNewProcStateMask,           @ImportObjectUpdProcStateMask,
           @ImportObjectErr,
           @IrId_r , @IrPuid,  @DataSetId_r 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_r , @DataSet_Ir_RelId Output
  end

 if @DoImportItem = 1 and not @ItemId_r is null
  begin
   -- new 21.08.2013 Relation Item zu IR
   EXECUTE Objects_Ir_Rel_Create @ItemId_r, @IrId_r, @DataSet_ItemIr_RelId Output
   if @DebugPrintOn = 1
   begin
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '91-@DataSet_ItemIr_RelId',  @DataSet_ItemIr_RelId
   end
  end


END
GO


Print '-- [ObjIds_View] --------------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjIds_View" and type = "V")
Drop View ObjIds_View
GO


CREATE VIEW [ObjIds_View]
-- Returns SubObj Id's of RootId
-- I  -> IR
-- IR -> DS
AS
SELECT
  OSub.Id as Id,
  ORoot.Id as RootId
FROM
  dbo.Objects_tbl ORoot
  JOIN dbo.Objects_Ir_Rel_tbl [Rel] ON (ORoot.Id = [Rel].IrId)
  JOIN dbo.Objects_tbl OSub ON ([Rel].ObjectId = OSub.Id)
GO


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

CREATE PROCEDURE Objects_tbl_IdsFromORootGet
                 @ObjectId uniqueidentifier

AS
BEGIN

  if (object_id('tempdb..#zzIt_tbl') is not null)  Drop table #zzIt_tbl
  if (object_id('tempdb..#zzIr_tbl') is not null)  Drop table #zzIr_tbl
  if (object_id('tempdb..#zzDs_tbl') is not null)  Drop table #zzDs_tbl

  CREATE TABLE #zzIt_tbl(Id uniqueidentifier)
  insert into #zzIt_tbl (Id) Values(@ObjectId)

  -- select IR's of Item
  SELECT Id
   into #zzIr_tbl
   from ObjIds_View WHERE  RootId in (select ID from #zzIt_tbl)

  -- select DS of IR's
  SELECT Id
   into #zzDs_tbl
   from ObjIds_View WHERE  RootId in (select ID from #zzIr_tbl)

  select * from #zzDs_tbl
  union all (SELECT * from #zzIr_tbl)
  union all (SELECT * from #zzIt_tbl)
END
GO

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
  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)

      -- EXECUTE Objects_tbl_Object_GetId @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @Id Output
      -- if @Object_tbl_Id = "" Goto EndeExit

      -- 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_ForAll_ProcessGroups @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

       if @DeleteMethod = "M5_ITM"
          begin
          -- löschen aller der Daten eines ITEMS

             if (object_id('tempdb..#zzIt_tbl') is not null)  Drop table #zzIt_tbl
             CREATE TABLE #zzId_tbl(Id uniqueidentifier)
             -- insert into #zzId_tbl (Id) Values(@ObjectId)
             insert into #zzId_tbl execute Objects_tbl_IdsFromORootGet @Object_tbl_Id

              -- Step: delete Proces results
              DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
                 WHERE dbo.JobControl_ObjectsProcessData_tbl.ID in (select Id from #zzId_tbl)

              -- Step: delete Relation from dbo.Objects_Ir_Rel_tbl
              DELETE FROM dbo.Objects_Ir_Rel_tbl
                 WHERE dbo.Objects_Ir_Rel_tbl.ObjectId in (select Id from #zzId_tbl)

              -- Step: delete Object Datasets
              DELETE FROM Objects_tbl WHERE Id in (select Id from #zzId_tbl)

          end


    end  -- Daten löschen via DataSet


  EndeExit:

END
GO





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 '-- [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 '-- [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 '-- [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 '-- [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
--

--     Dokumentation:
--     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 '-- [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 '-- [Objects_tbl_DuplicatesCleanUp] --------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Objects_tbl_DuplicatesCleanUp" and type = "P")
Drop Procedure Objects_tbl_DuplicatesCleanUp
GO

CREATE PROCEDURE Objects_tbl_DuplicatesCleanUp @Object_tbl_Id1 uniqueidentifier, -- ID1
                                               @Object_tbl_Id2 uniqueidentifier, -- ID2
                                               @Object_tbl_Id_delr uniqueidentifier = Null Output -- Returns ID of deleted Dataset


/* new 12.04.2013 20:13 J.Fes
   bereinigt Duplicat Object Daten aus Objects_tbl:
   Folgende Regeln werden hierbei eingehalten:
   -- der juegere (ObjectLastModDate) Datensatz bleibt erhalten der aeltere wird geloescht
   -- evtl vorhandene Processdaten werden vom del nach keep uebertragen
   */

As
begin

  DECLARE @DebugPrintOn        bit
  DECLARE @MyProcName          nvarchar(32)
  DECLARE @Object_tbl_Id1_LastModDT  datetime
  DECLARE @Object_tbl_Id2_LastModDT  datetime
  DECLARE @Object_tbl_Id_keep  uniqueidentifier
  DECLARE @TempStr             nvarchar(32)


  set @DebugPrintOn = 0
  set @MyProcName='Objects_tbl_DuplicatesCleanUp'

  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '00-TEST' , 'TEST'
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@Object_tbl_Id1' , @Object_tbl_Id1
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@Object_tbl_Id1' , @Object_tbl_Id2
  end

  -- wenn @Objects_tbl_ID gegeben ist dann diese suchen lassen
  -- pruefen ob Object Datensatz in Objects_tbl vorhanden ist
  select @Object_tbl_Id1_LastModDT=ObjectLastModDate from Objects_tbl where ID = @Object_tbl_Id1
  select @Object_tbl_Id2_LastModDT=ObjectLastModDate from Objects_tbl where ID = @Object_tbl_Id2

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-@Object_tbl_Id1_LastModDT', @Object_tbl_Id1_LastModDT
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-@Object_tbl_Id1_LastModDT', @Object_tbl_Id2_LastModDT
  end

  -- Exit wenn kein gueltiges Datum gefunden
  if @Object_tbl_Id1_LastModDT is null goto EndeExit
  if @Object_tbl_Id2_LastModDT is null goto EndeExit


  -- Step: Check wer ist neuer
  if @Object_tbl_Id1_LastModDT >= @Object_tbl_Id2_LastModDT
    begin
       set @Object_tbl_Id_keep = @Object_tbl_Id1
       set @Object_tbl_Id_delr = @Object_tbl_Id2
    end
    else
    begin
       set @Object_tbl_Id_keep = @Object_tbl_Id2
       set @Object_tbl_Id_delr = @Object_tbl_Id1
    end

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '21-@Object_tbl_Id_keep' , @Object_tbl_Id_keep
   select @TempStr=ObjectCliName from Objects_tbl where ID = @Object_tbl_Id_keep
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '21-@Object_tbl_Id_keepCli' , @TempStr

   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '22-@Object_tbl_Id_delr' , @Object_tbl_Id_delr
   select @TempStr=ObjectCliName from Objects_tbl where ID = @Object_tbl_Id_delr
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '21-@Object_tbl_Id_delCli' , @TempStr
  end

  -- Step: alle Process Daten von @Object_tbl_Id_delr nach an @Object_tbl_Id_keep uebertragen
  update JobControl_ObjectsProcessData_tbl
         set Objects_tbl_ID = @Object_tbl_Id_keep
         where  Objects_tbl_ID = @Object_tbl_Id_delr

  -- Step: aelteren DS loeschen
  Execute Objects_tbl_DeleteData 'M3_DSS', @Object_tbl_Id_delr, 'DS', ''

  -- Step: ProcessStatus_Mark_LastRes
  Execute ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups @Object_tbl_Id_keep

  EndeExit:

END
GO
---------------------------------------------------------------------------------------

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

-- Print ''
-- Print ''
-- Print ''
-- Print '---------------------------------------------------------------------'
-- Print ' Test Procedures ObjectsUpdate_ProcessStatus_Mark_LastRes'
-- Print '----------------------------------------------------'
-- GO
--
/*
 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)  + "]"
 */

--   Print ""  Print ""
--   Print "#Debug 12.04.2013 21:06 / J.Fes "
--   Print "#Debug Objects_tbl_DuplicatesCleanU"
  -- BG-Einzelteil-01/A	BG-Einzelteil-01-A	05812fe2-d2d9-4721-951d-dccf26ce0d8e	30.08.2011 12:46
  -- BG-Einzelteil-03/A	BG-Einzelteil-03-A	c36c86e0-7761-4bce-ae28-1b9c397cc55a	30.08.2011 12:46
  -- BG-Einzelteil-04/A	BG-Einzelteil-04-A	80a99333-c3a9-4173-bc0d-bde9a7e072af	30.08.2011 12:46
  -- EXECUTE  Objects_tbl_DuplicatesCleanUp 'eb92367f-2636-4d99-ad2c-f18241d0cbb3', '6a03c1ab-1639-41b7-9f09-14244b2ba392'
  go
ende:

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

