Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [JobMgr_30_ApplProceduresObjects_Import_DataSets_and_IRs.sql]'
Print ' Autor         : J.Feuerstein'
Print ' Creation Date : 22.03.2006'
Print ' Last Update   : 14.06.2017/J.Fes'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
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(128), -- 25.10.2017 S.Gueth (size from 64 to 128)
                                    @ItemPuid                     nvarchar(64) = null, -- #New: 14.04.13 J.Fes #LUp:30.11.17 J.Fes (size from 32 to 64)

                                    -- #New: 16.08.2013/J.Fes
                                    @ItemType                     nvarchar(32) = null,
                                    @ItemName2                    nvarchar(128) = null,     -- 25.10.2017 S.Gueth (size from 64 to 128)
                                    @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(255) = null,  -- #Upd: 16.09.2015/J.Fes size enlarged
                                    @ItemCreateDate               datetime = null,
                                    @ItemLastModUser              nvarchar(128) = null,
                                    @ItemLastModDate              datetime = null,

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

                                    @IrRev                        nvarchar(32),
                                    @IrPuid                       nvarchar(64),      -- change 22.08.2012 a.becker (size from 15 to 32) #LUp:30.11.17 J.Fes (size from 32 to 64)

                                    @IrNameRev                    nvarchar(128),     -- 25.10.2017 S.Gueth (size from 64 to 128)
                                    @IrType                       nvarchar(32),
                                    @IrName2                      nvarchar(128),      -- 25.10.2017 S.Gueth (size from 64 to 128)
                                    @IrDescription                nvarchar(255),

                                    @IrCliName                    nvarchar(128),

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

                                    @IrOwner                      nvarchar(32),
                                    @IrGroup                      nvarchar(255),     -- #Upd: 16.09.2015/J.Fes size enlarged
                                    @IrCreateDate                 datetime = null,
                                    @IrLastModUser                nvarchar(128),
                                    @IrLastModDate                datetime,

                                    @IrReleaseState               nvarchar(255) = null,  -- #UPd: 11.01.2019/J.Fes upsize 32 -255
                                    @IrReleaseDate                datetime = null,

                                    @IrNote                       nvarchar(255) ,

                                    @ObjectPuid                   nvarchar(64),  -- change 22.08.2012 a.becker (size from 15 to 32)
                                    @ObjectName                   nvarchar(128),  -- 25.10.2018 S.Gueth (size from 64 to 128)
                                    @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(255),    -- #Upd: 16.09.2015/J.Fes size enlarged
                                    @ObjectCreateDate             datetime = null,
                                    @ObjectLastModUser            nvarchar(32) = null,
                                    @ObjectLastModDate            datetime,

                                    @ObjectFs_DPNE                nvarchar(255) = null,
                                    @ObjectFs_Vol                 nvarchar(64) = null,
                                    @ObjectFs_NE                  nvarchar(64) = null,
                                    @ObjectFs_SizeBytes           bigint = null,   -- #New: 22.08.2012 a.becker
                                    @ObjectFs_HashCode            nvarchar(64),    -- #New: 22.08.2012 a.becker
                                    @ObjectFs_LastModDate         datetime = null, -- #New: 25.04.2014 J.Fes

                                    @ObjectDs_Ver                 int  = null,     -- #New: 27.05.2016 J.Fes
                                    @ObjectDs_VerKeepLimt         int  = null,     -- #New: 27.05.2016 J.Fes
                                    @ObjectDs_VersN               int  = null,     -- #New: 27.05.2016 J.Fes

                                    @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(255) = null,  -- #UPd: 11.01.2019/J.Fes upsize 32 -255
                                    @ObjectReleaseDate            datetime = null,

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

                                    @ImportObjectNewProcStateMask nvarchar(220), -- LUp:01.03.2016 + P.Gr Mc // Upd:08.08.2014 / J.Fes je Pgr = 10
                                    @ImportObjectUpdProcStateMask nvarchar(220), -- LUp:01.03.2016 + P.Gr Mc // Upd:08.08.2014 / J.Fes je Pgr = 10
                                    @ImportObjectErr              nvarchar(255), -- Uodate 14.08.2014 / J.Fes from 64 to 255

                                    @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

                                    @DoDataAnalyseOnImport        bit,  -- #New: 24.04.2019/J.Fes
                                    @DoUpdateRank                 bit,  -- #New: 29.04.2019/Anton

                                    -- #New: 07.08.2014  -------------------------
                                    @ObjectUgMaster_Lev1CompN       int = null,
                                    @ObjectUgMaster_Lev1CompUniqueN int = null,
                                    @ObjectUgMaster_ApBOM_isPrecise bit = null,
                                    
                                    -- #New: 25.03.2018 / J.Fes 16:21 15:52   -------------------------
                                    @ItemAttr01                   nvarchar(255) = null,
                                    @ItemAttr02                   nvarchar(255) = null,
                                    @ItemAttr03                   nvarchar(255) = null,
                                    @ItemAttr04                   nvarchar(255) = null,
                                    @ItemAttr05                   nvarchar(255) = null,
                                    @ItemAttr06                   nvarchar(255) = null,
                                    @ItemAttr07                   nvarchar(255) = null,
                                    @ItemAttr08                   nvarchar(255) = null,
                                    @ItemAttr09                   nvarchar(255) = null,
                                    @ItemAttr10                   nvarchar(255) = null,

                                    -- #New: 25.03.2018 / J.Fes 16:21 15:52   -------------------------
                                    @IrAttr01                   nvarchar(255) = null,
                                    @IrAttr02                   nvarchar(255) = null,
                                    @IrAttr03                   nvarchar(255) = null,
                                    @IrAttr04                   nvarchar(255) = null,
                                    @IrAttr05                   nvarchar(255) = null,
                                    @IrAttr06                   nvarchar(255) = null,
                                    @IrAttr07                   nvarchar(255) = null,
                                    @IrAttr08                   nvarchar(255) = null,
                                    @IrAttr09                   nvarchar(255) = null,
                                    @IrAttr10                   nvarchar(255) = null,

                                    -- #New: 07.08.2014 ------------------------------
                                    @ObjectAttr01               nvarchar(255) = null,
                                    @ObjectAttr02               nvarchar(255) = null,
                                    @ObjectAttr03               nvarchar(255) = null,
                                    @ObjectAttr04               nvarchar(255) = null,
                                    @ObjectAttr05               nvarchar(255) = null,
                                    @ObjectAttr06               nvarchar(255) = null,
                                    @ObjectAttr07               nvarchar(255) = null,
                                    @ObjectAttr08               nvarchar(255) = null,
                                    @ObjectAttr09               nvarchar(255) = null,
                                    @ObjectAttr10               nvarchar(255) = null,

                                    @MultiCadAP                 nvarchar(255) = null, -- #New:20.08.2019 S.Gueth
                                    @MultiCadItemRevs           nvarchar(max) = null, -- #New:20.08.2019 S.Gueth

                                    @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'
 -- set @ImportObjectErr = '#Debug WRN'
 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
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '07-@ObjectUgMaster_Lev1CompN',       @ObjectUgMaster_Lev1CompN
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '08-@ObjectUgMaster_Lev1CompUniqueN',       @ObjectUgMaster_Lev1CompUniqueN
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '09-@ObjectUgMaster_ApBOM_isPrecise',       @DoImportItem
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '10-@ObjectAttr01',       @ObjectAttr01
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '11-@ObjectAttr10',       @ObjectAttr10
  end

 if @DoImportItem = 1
  begin

   if @DebugPrintOn = 1
   begin
 --    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '31-@ItemName',             @ItemName
 --    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '32-@ItemCliName',          @ItemCliName
 --    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '33-@ItemReleaseDate',      @ItemReleaseDate
 --    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '34-@ItemExportRecord',     @ItemExportRecord
 --    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '35-@ItemExportRecordDate', @ItemExportRecordDate
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '36-@ItemId_r', @ItemId_r
   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
   -- 05.04.2015 J.Fes: solving Issue Item OwingSite was wrong because @IrOwningSiteID was used instead of @ItemOwningSiteID
   EXECUTE Object_Import
           @ItemPuid,          @ItemName,          @ItemName2,          @ItemDescription,   @ObjectFromSiteID,
           @ItemCliName,       'I',                @ItemOwningSiteID,   @ItemExportRecord,  @ItemExportRecordDate, @ItemType,
           @ItemOwner,         @ItemGroup,         @ItemCreateDate,     @ItemLastModUser,   @ItemLastModDate,
   -- #Doc:@ObjectFs_DPNE      @ObjectFs_Vol       @ObjectFs_NE         @ObjectFs_SizeBytes @ObjectFs_HashCode
           '~',                @ObjectFs_Vol,      '~',                 NULL,               '~',
   -- #Doc:@ObjectDs_Ver       @ObjectDs_VerKeepLimt  @ObjectDs_Ver
           NULL,               NULL,               NULL,
   -- #Doc:@ObjectFs_LastModDate  @ObjectSaveVersion  @ObjUgM_PartFamName @ObjectUgMaster_Type
           NULL,                  '~',             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,

   -- #New: 07.08.2014 / J.Fes
           @ObjectUgMaster_Lev1CompN,              @ObjectUgMaster_Lev1CompUniqueN,         @ObjectUgMaster_ApBOM_isPrecise,
  
   -- #LUp: 25.03.2018 ItemAttr usage
           @ItemAttr01,      @ItemAttr02,      @ItemAttr03,       @ItemAttr04,      @ItemAttr05,
           @ItemAttr06,      @ItemAttr07,      @ItemAttr08,       @ItemAttr09,      @ItemAttr10,
           
            -- #New: 20.08.2019 / S.Gueth
           @MultiCadAP, @MultiCadItemRevs,
           
           @DoUpdateRank,
           
           NULL , '' ,       @ItemId_r Output

   if @DebugPrintOn = 1
   begin
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '41-@ItemId_r',        @ItemId_r
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '41-@ObjectAttr01',    @ObjectAttr01
   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   PFM = 27
   -- 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
           '~',                @ObjectFs_Vol,      '~',                 NULL,               '~',
   -- #Doc:@ObjectDs_Ver       @ObjectDs_VerKeepLimt  @ObjectDs_VersN,
           NULL,               NULL,               NULL,
   -- #Doc:@ObjectFs_LastModDate  @ObjectSaveVersion    @ObjUgM_PartFamName @ObjectUgMaster_Type
           NULL,                  '~',             @ObjectUgMaster_PartFamMemName,          @ObjectUgMaster_Type,
           @ObjectUgMaster_APLevels,               @ObjectUgMaster_CompCount,               @ObjectUgMaster_CompCountUnique,         @ObjectUgMaster_FsHitCount,
           @IrReleaseState,    @IrReleaseDate,
           @IrNote,            @ObjectPrjIds,
           @ImportObjectNewProcStateMask,          @ImportObjectUpdProcStateMask,
           @ImportObjectErr,
      -- #New: 07.08.2014 / J.Fes
           @ObjectUgMaster_Lev1CompN,              @ObjectUgMaster_Lev1CompUniqueN,         @ObjectUgMaster_ApBOM_isPrecise,
           @IrAttr01,      @IrAttr02,      @IrAttr03,       @IrAttr04,      @IrAttr05,
           @IrAttr06,      @IrAttr07,      @IrAttr08,       @IrAttr09,      @IrAttr10,
           
            -- #New: 20.08.2019 / S.Gueth
           @MultiCadAP, @MultiCadItemRevs,
           
           @DoUpdateRank,
          
           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,     'DS',               @ObjectOwningSiteID, @ObjectExportRecord, @ObjectExportRecordDate, @ObjectType,
           @ObjectOwner,       @ObjectGroup,       @ObjectCreateDate,   @ObjectLastModUser,  @ObjectLastModDate,
           @ObjectFs_DPNE,     @ObjectFs_Vol,      @ObjectFs_NE,        @ObjectFs_SizeBytes, @ObjectFs_HashCode,         @ObjectFs_LastModDate,
           @ObjectDs_Ver,      @ObjectDs_VerKeepLimt,  @ObjectDs_VersN,
           @ObjectSaveVersion, @ObjectUgMaster_PartFamMemName,
           @ObjectUgMaster_Type,                   @ObjectUgMaster_APLevels,                 @ObjectUgMaster_CompCount,  @ObjectUgMaster_CompCountUnique,
           @ObjectUgMaster_FsHitCount,             @ObjectReleaseState, @ObjectReleaseDate,
           @ObjectNote,        @ObjectPrjIds,
           @ImportObjectNewProcStateMask,           @ImportObjectUpdProcStateMask,
           @ImportObjectErr,
           
           -- #New: 07.08.2014 / J.Fes
           @ObjectUgMaster_Lev1CompN,              @ObjectUgMaster_Lev1CompUniqueN,         @ObjectUgMaster_ApBOM_isPrecise,
           @ObjectAttr01,      @ObjectAttr02,      @ObjectAttr03,       @ObjectAttr04,      @ObjectAttr05,
           @ObjectAttr06,      @ObjectAttr07,      @ObjectAttr08,       @ObjectAttr09,      @ObjectAttr10,
           
           -- #New: 20.08.2019 / S.Gueth
           @MultiCadAP, @MultiCadItemRevs,
           
           @DoUpdateRank,
           
           @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 <> '' and @DoDataAnalyseOnImport = 1 --
    begin
     DECLARE @NRefsFound int,
             @WRN_140 nvarchar(32),
             @ERR_150 nvarchar(32)
             set @WRN_140 = 'WRN_140_DSHasMultiIrRefs'
             set @ERR_150 = 'ERR_150_IrHasDsWithSameName'
     -- #Doc: (1*) find if dataset has multible IR references
     SELECT @NRefsFound=count(*) FROM  dbo.Objects_tbl WITH (NOLOCK)
     WHERE   dbo.Objects_tbl.ObjectPuid = @ObjectPuid
         and dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
     -- #Doc: if (1*) happend then set for all DS ImportObjectErr
     if @NRefsFound > 1
      begin
        UPDATE dbo.Objects_tbl
          SET ImportObjectErr = LEFT(REPLACE(LTRIM(REPLACE(ImportObjectErr, @WRN_140, '') + ' ' + @WRN_140),' ', ';'),255)
        WHERE
              dbo.Objects_tbl.ObjectPuid = @ObjectPuid
          and dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
      end

      -- #New: 14.08.2014 19:11
      -- #Doc: (2*) find if IR contains Datasets with same type and name
      if @IrId_r IS NOT NULL
       begin
        SELECT @NRefsFound=count(*)
              FROM dbo.Objects_tbl IR WITH (NOLOCK)
                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 = @IrId_r
                and DS.ObjectType = @ObjectType
                and DS.ObjectName = @ObjectName
        -- #Doc: if (2*) happend then set for all DS ImportObjectErr
        if @NRefsFound > 1
         begin
           set @ImportObjectErr = @ERR_150
           UPDATE dbo.Objects_tbl
              SET ImportObjectErr = LEFT(REPLACE(LTRIM(REPLACE(ImportObjectErr, @ERR_150, '') + ' ' + @ERR_150),' ', ';'),255)
           WHERE
                 dbo.Objects_tbl.Id in
                (select DS.Id FROM dbo.Objects_tbl IR WITH (NOLOCK)
                   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 = @IrId_r
                   and DS.ObjectType = @ObjectType
                   and DS.ObjectName = @ObjectName
                )
         end
       end
    end
  end

 if @DoImportDs = 1
  begin
      -- #New: 22.06.2009 wenn DS import dann sind auch die relationen zu IR zu erzeugen
      EXECUTE Objects_Ir_Rel_Create @IrId_r ,@DataSetId_r , @DataSet_Ir_RelId Output
   if @DebugPrintOn = 1
   begin
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '91-@IrId_r',  @IrId_r
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '92-@DataSetId_r',  @DataSetId_r
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '93-@DataSet_Ir_RelId',  @DataSet_Ir_RelId
   end
  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 , '95-@ItemId_r',  @ItemId_r
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '96-@IrId_r',  @IrId_r
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '97-@DataSet_ItemIr_RelId',  @DataSet_ItemIr_RelId
   end
  end

 if @DoImportDs = 1 and @DoDataAnalyseOnImport = 1
  begin
      -- #New: 15.09.2016/J.Fes if DS @ImportObjectErr has Error
      --        then all Datasets of IR will be excudet in all ProcStateCol* with Status 'X.PLM.ERR'
      -- #LUp: 11.05.2018/J.Fes   charindex('ERR' -> charindex('ERR_'
      if charindex('ERR_',@ImportObjectErr) <> 0
      begin
          --  #New:28.11.2016 17:24/J.Fes now Status are only set to 'X.PLM.ERR' if Status was before = 'N'
          --  #LUp:29.06.2017 Renamed X.TC.Struc.ERR to: X.PLM.ERR
          UPDATE dbo.Objects_tbl
           SET ProcStateCol01 = case WHEN ProcStateCol01 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol01 END,
               ProcStateCol02 = case WHEN ProcStateCol02 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol02 END,
               ProcStateCol03 = case WHEN ProcStateCol03 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol03 END,
               ProcStateCol04 = case WHEN ProcStateCol04 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol04 END,
               ProcStateCol05 = case WHEN ProcStateCol05 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol05 END,
               ProcStateCol06 = case WHEN ProcStateCol06 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol06 END,
               ProcStateCol07 = case WHEN ProcStateCol07 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol07 END,
               ProcStateCol08 = case WHEN ProcStateCol08 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol08 END,
               ProcStateCol09 = case WHEN ProcStateCol09 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol09 END,
               ProcStateCol10 = case WHEN ProcStateCol10 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol10 END,

               ProcStateCol11 = case WHEN ProcStateCol11 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol11 END,
               ProcStateCol12 = case WHEN ProcStateCol12 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol12 END,
               ProcStateCol13 = case WHEN ProcStateCol13 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol13 END,
               ProcStateCol14 = case WHEN ProcStateCol14 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol14 END,
               ProcStateCol15 = case WHEN ProcStateCol15 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol15 END,
               ProcStateCol16 = case WHEN ProcStateCol16 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol16 END,
               ProcStateCol17 = case WHEN ProcStateCol17 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol17 END,
               ProcStateCol18 = case WHEN ProcStateCol18 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol18 END,
               ProcStateCol19 = case WHEN ProcStateCol19 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol19 END,
               ProcStateCol20 = case WHEN ProcStateCol20 = 'N' THEN 'X.PLM.ERR' ELSE ProcStateCol20 END,
               ProcStateColRf = case WHEN ProcStateColRf = 'N' THEN 'X.PLM.ERR' ELSE ProcStateColRf END
               -- ProcStateColMc =    MC Status bleibt
            WHERE
               dbo.Objects_tbl.Id = @DataSetId_r
               -- #LUp: 30.08.2017 12:09 now only DS having issue get status X.PLM.ERR
               ---                       before it was set to all DS of IR
--                dbo.Objects_tbl.Id in
--                (SELECT DS.Id FROM dbo.Objects_tbl IR WITH (NOLOCK)
--                   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 = @IrId_r
--                )
      end
  end

 -- #DOC:29.04.2019 
 if not @ItemId_r is null and @DoUpdateRank = 1
  begin
    -- #New 08.06.2015 / J.Fes
    EXECUTE Item_IrLatestRank_Update @ItemId_r
  end

END
GO



ende:

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

