Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [ObjectsAndProcessData]'
Print ' Creation Date : 22.03.2006/J.Fes'
Print ' Last Update   : 18.04.2024/J.Fes'
Print '-------------------------------------------------------------------------'

use [#DB_N#]
GO


Print '-- [DS_with_Irs_View] #LUp add 18.04.2024/J.Fes -------------------------'
If Exists (select Name
           from sysobjects
           where Name = "DS_with_Irs_View" and type = "V")
Drop View DS_with_Irs_View
GO

-- !! Namen nicht aendern !! siehe auch QuellCode ..\JobServer.DataSource\JobServer_DataSource.vb  Objects_tbl_UpdateProcessState
-- !! alter name war: ObjectsDataSets_View
CREATE VIEW [DS_with_Irs_View]
-- BasisView
-- Angezeigt werden die Datasets mit Irs
-- 31.12.2008 DS.ImportObjectErr eingefügt
AS
SELECT

   -- #Doc: set Caption see \JobManagerContracts\JobServer.DataSource\JobServer_DataSource.vb
   ---      ...   Public Sub Objects_DataTable_ColumnCaptionsSet(ByVal Objects_DataTable As DataTable)

  -- IRs.Id As IrId,
  IRs.ObjectName         AS IrName,
  IRs.ObjectName2        AS IrName2,
  IRs.ObjectType         AS IrType,
  IRs.ObjectCliName      AS IrObjectCliName,
  IRs.ObjectOwner        AS IROwner,
  IRs.ObjectGroup        AS IRGroup,
  IRs.ObjectDescription  AS IrDescription,
  IRs.ObjectReleaseState AS IrReleaseState,
  IRs.ObjectReleaseDate  AS IrReleaseDate,
  IRs.ObjectCreateDate   AS IrCreateDate,
  IRs.ObjectLastModDate  AS IrLastModDate,
  IRs.ObjectLastSavDate  AS IrLastSavDate,                 -- #New: 04.02.2020/J.Fes

  IRs.ObjectPuId         AS IrPuId,
  -- 10.01.2012 11:58 eingefuegt
    -- 24.03.2022 added S.Gueth
  IRs.Id AS IrId,


  IRs.ObjectIsCheckedOut AS IrObjectIsCheckedOut,         -- #New: 25.06.2020/S.Gueth + J.Fes
  IRs.ObjectCheckoutDate AS IrObjectCheckoutDate,         -- #New: 25.06.2020/S.Gueth + J.Fes
  IRs.ObjectCheckoutUser AS IrObjectCheckoutUser,         -- #New: 25.06.2020/S.Gueth + J.Fes
  IRs.ObjectCheckOutChangeId AS IrObjectCheckOutChangeId, -- #New: 25.06.2020/S.Gueth + J.Fes
  IRs.ObjectIsMFKObj AS IRObjectIsMFKObj,                 -- #New 28.06.2022/J.Fes


  -- #New 11.01.2022/J.Fes  see aslo \jobserver\mainform\01-objectslists\objectscustomultragrid_uc.vb
  -- #Doc: 18.01.2022/J.Fes on change see \JobManagerDataModel\SiteDef.vb --> Public ReadOnly Property DataSourceColumesGet() As List(Of String)
  IRs.ObjectAttr01 AS IrObjectAttr01,
  IRs.ObjectAttr02 AS IrObjectAttr02,
  IRs.ObjectAttr03 AS IrObjectAttr03,
  IRs.ObjectAttr04 AS IrObjectAttr04,
  IRs.ObjectAttr05 AS IrObjectAttr05,
  IRs.ObjectAttr06 AS IrObjectAttr06,
  IRs.ObjectAttr07 AS IrObjectAttr07,
  IRs.ObjectAttr08 AS IrObjectAttr08,
  IRs.ObjectAttr09 AS IrObjectAttr09,
  IRs.ObjectAttr10 AS IrObjectAttr10,

  DS.ObjectIrLatestRank      AS ObjectIrLatestRank , -- #New 03.06.2015 J.Fes/A.Becker
  DS.Id AS Id,
  DS.ObjectName,
  DS.ObjectDescription,
  DS.ObjectType,
-- 10.01.2012 11:58 entnommen das DS.ObjecIrType wieder entnommen wurde
--  DS.ObjectIrType        AS IrType,
  DS.ObjectCliName,
  DS.ObjectFromSiteId,
  DS.ObjectClass,
  DS.ObjectOwningSiteId,
  DS.ObjectExportRecord,
  DS.ObjectExportRecordDate,
  DS.ObjectOwner,
  DS.ObjectGroup,
  DS.ObjectCreateDate,
  DS.ObjectLastModUser,
  DS.ObjectLastModDate,
  DS.ObjectLastSavDate, -- #New: 04.02.2020/J.Fes
  DS.ObjectUgMaster_Type,
  DS.ObjectUgMaster_CompCount,
  DS.ObjectUgMaster_CompCountUnique, -- #New 22.08.2012/a.becker (KION)
  DS.ObjectUgMaster_Lev1CompN,       -- #New 07.08.2014/J.Fes
  DS.ObjectUgMaster_Lev1CompUniqueN, -- #New 07.08.2014/J.Fes
  DS.ObjectUgMaster_ApBOM_isPrecise, -- #New 07.08.2014/J.Fes

  -- #New 26.02.2014 J.Fes
  CASE DS.ObjectFromSiteId WHEN DS.ObjectOwningSiteId THEN '1-SO (SiteObj)'
                           ELSE   '2-RO (ReplObj)' END As [Object_SiteOwningTyp],

--  case DS.ObjectFromSiteId '#Debug' As [Object_SiteOwningTyp],
  DS.ObjectUgMaster_APLevels,
  DS.ObjectUgMaster_FsHitCount,
--  DS.ObjectUgMaster_FsHitCount_YN,
  DS.ObjectUgMaster_PartFamMemName,
  DS.ObjectReleaseState,
  DS.ObjectReleaseDate,
  DS.ObjectFs_DPNE,
  DS.ObjectFs_Vol,
  DS.ObjectFs_NE,
  DS.ObjectFs_SizeBytes, -- new 22.08.2012 a.becker
--   CAST(ROUND(DS.ObjectFs_SizeBytes/1048576, 2) AS decimal(10,2)) As [ObjectFs_SizeMB],
  CAST(DS.ObjectFs_SizeBytes/1048576.00 AS decimal(15,2)) As [ObjectFs_SizeMB],  -- new 08.11.2012 17:44 J.Fes (KION) 1048576=1024*1024
  DS.ObjectFs_HashCode,  -- new 22.08.2012 a.becker
  DS.ObjectFs_LastModDate, -- new 25.04.2014 J.Fes
  DS.ObjectDs_Ver,         -- #New: 29.05.2016 J.Fes
  DS.ObjectDs_VerKeepLimt, -- #New: 29.05.2016 J.Fes
  DS.ObjectDs_VersN,       -- #New: 29.05.2016 J.Fes

  DS.ObjectSaveVersion,  -- new 22.08.2012 a.becker
--  DS.ObjectNote,       -- #Removed 17.06.2016 J.Fes
  DS.ObjectPrjIds,       -- new 01.04.2013 J.Fes
  DS.ObjectSyncTrigger,  -- #New 17.06.2016 J.Fes
  DS.ProcStateCol01,
  DS.ProcStateCol02,
  DS.ProcStateCol03,
  DS.ProcStateCol04,
  DS.ProcStateCol05,
  DS.ProcStateCol06,
  DS.ProcStateCol07,
  DS.ProcStateCol08,
  DS.ProcStateCol09,
  DS.ProcStateCol10,

  -- #New 07.08.2014/J.Fes
  DS.ProcStateCol11,
  DS.ProcStateCol12,
  DS.ProcStateCol13,
  DS.ProcStateCol14,
  DS.ProcStateCol15,
  DS.ProcStateCol16,
  DS.ProcStateCol17,
  DS.ProcStateCol18,
  DS.ProcStateCol19,
  DS.ProcStateCol20,

  -- #New 07.11.2022/J.Fes
  DS.ProcStateCol21,
  DS.ProcStateCol22,
  DS.ProcStateCol23,
  DS.ProcStateCol24,
  DS.ProcStateCol25,
  DS.ProcStateCol26,
  DS.ProcStateCol27,
  DS.ProcStateCol28,
  DS.ProcStateCol29,
  DS.ProcStateCol30,

  -- #New 07.11.2022/J.Fes
  DS.ProcStateCol31,
  DS.ProcStateCol32,
  DS.ProcStateCol33,
  DS.ProcStateCol34,
  DS.ProcStateCol35,
  DS.ProcStateCol36,
  DS.ProcStateCol37,
  DS.ProcStateCol38,
  DS.ProcStateCol39,
  DS.ProcStateCol40,


  DS.ProcStateColMc,  -- #New:02.03.2016/J.Fes
  DS.ProcStateColRf,
  DS.ProcStateColUn,  -- #New:26.11.2021/S.Gueth

  DS.ImportObjectErr,

  DS.MultiCadAP,       -- #New: 21.08.2019/S.Gueth
  DS.MultiCadItemRevs, -- #New: 21.08.2019/S.Gueth

  DS.ObjectIsCheckedOut, -- #New: 25.06.2020/S.Gueth
  DS.ObjectCheckoutDate, -- #New: 25.06.2020/S.Gueth
  DS.ObjectCheckoutUser, -- #New: 25.06.2020/S.Gueth
  DS.ObjectCheckOutChangeId, -- #New: 25.06.2020/S.Gueth
  DS.ObjectWhereUsedN, -- #New: 25.06.2020/S.Gueth
  DS.ObjectRevChainAnchorPuid, -- #New: 25.06.2020/S.Gueth

  DS.ObjectIsMFKObj,  -- #New 28.06.2022/J.Fes

  -- #New 07.08.2014/J.Fes
  -- #Doc: 18.01.2022/J.Fes on change see \JobManagerDataModel\SiteDef.vb --> Public ReadOnly Property DataSourceColumesGet() As List(Of String)
  DS.ObjectAttr01 AS DSObjectAttr01,
  DS.ObjectAttr02 AS DSObjectAttr02,
  DS.ObjectAttr03 AS DSObjectAttr03,
  DS.ObjectAttr04 AS DSObjectAttr04,
  DS.ObjectAttr05 AS DSObjectAttr05,
  DS.ObjectAttr06 AS DSObjectAttr06,
  DS.ObjectAttr07 AS DSObjectAttr07,
  DS.ObjectAttr08 AS DSObjectAttr08,
  DS.ObjectAttr09 AS DSObjectAttr09,
  DS.ObjectAttr10 AS DSObjectAttr10,
  -- nur temporär
  DS.ObjectPuId
FROM
  -- 27.09.2011 WITH (nolock) engefuegt J.Fes
  Objects_tbl DS WITH (nolock)
  INNER JOIN Objects_Ir_Rel_tbl IrRel ON (DS.Id = IrRel.ObjectId)
  INNER JOIN Objects_tbl        Irs   ON (IrRel.IrId = Irs.Id)
where
  -- #New: 07.03.2017 /J.Fes to enshure only Data of DS will be returnd
  DS.ObjectClass = 'DS'
GO


Print '-- [IRs_View] #LUp add 18.04.2024/J.Fes ---------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "IRs_View" and type = "V")
Drop View IRs_View
GO

CREATE VIEW [IRs_View]
-- BasisView: Ausgegeben werden die IRs
AS
SELECT
  dbo.Objects_tbl.Id,
  dbo.Objects_tbl.ObjectPuId,
  dbo.Objects_tbl.ObjectName , -- wird zusäzliche benötigt
  dbo.Objects_tbl.ObjectPuId As IrPuid,  -- 05.12.2016 Renamed IrPuId -to- IrPuid
  dbo.Objects_tbl.ObjectName As IrName,
  dbo.Objects_tbl.ObjectName2 As IrName2,
  dbo.Objects_tbl.ObjectDescription As IrDescription,
  dbo.Objects_tbl.ObjectFromSiteId,
  dbo.Objects_tbl.ObjectClass,
  dbo.Objects_tbl.ObjectType As IrType, -- 20.12.2011 eingefuegt

  dbo.Objects_tbl.ObjectOwner      AS IrOwner, -- #LUp add 18.04.2024/J.Fes
  dbo.Objects_tbl.ObjectGroup      AS IrGroup, -- #LUp add 18.04.2024/J.Fes

  dbo.Objects_tbl.ObjectCliName,
  dbo.Objects_tbl.ObjectOwningSiteId,
  dbo.Objects_tbl.ObjectExportRecord,
  dbo.Objects_tbl.ObjectExportRecordDate, -- 20.08.2013 New
  dbo.Objects_tbl.ObjectOwner,
  dbo.Objects_tbl.ObjectGroup,
  dbo.Objects_tbl.ObjectCreateDate,
  dbo.Objects_tbl.ObjectLastModUser,
  dbo.Objects_tbl.ObjectLastModDate,
  dbo.Objects_tbl.ObjectLastSavDate,
  dbo.Objects_tbl.ObjectUgMaster_Type,
  dbo.Objects_tbl.ObjectUgMaster_CompCount,
  dbo.Objects_tbl.ObjectUgMaster_CompCountUnique,   -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectUgMaster_APLevels,
  dbo.Objects_tbl.ObjectUgMaster_FsHitCount,
  dbo.Objects_tbl.ObjectUgMaster_Lev1CompN,       -- #New 29.10.2015 12:13 J.Fes
  dbo.Objects_tbl.ObjectUgMaster_Lev1CompUniqueN, -- #New 29.10.2015 12:13 J.Fes
  dbo.Objects_tbl.ObjectUgMaster_ApBOM_isPrecise, -- #New 29.10.2015 12:13 J.Fes

--  dbo.Objects_tbl.ObjectFs_DPNE,  -- 25.04.2014 J.Fes entnommen
  dbo.Objects_tbl.ObjectFs_Vol,
--  dbo.Objects_tbl.ObjectFs_NE, ,  -- 25.04.2014 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_SizeBytes, -- new 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_HashCode,  -- new 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_LastModDate, -- new 25.04.2014 J.Fes but no usage

--  dbo.Objects_tbl.ObjectIsIrLatest, -- #New 03.06.2015 J.Fes/A.Becker
--   dbo.Objects_tbl.ObjectIsIrLatestWorking, -- #New 03.06.2015 J.Fes/A.Becker  #Remove 09.06.2015/J.Fes
  dbo.Objects_tbl.ObjectIrLatestRank, -- #New 03.06.2015 J.Fes/A.Becker


  dbo.Objects_tbl.ObjectSaveVersion,   -- new 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
  dbo.Objects_tbl.ObjectUgMaster_PartFamMemName,
  dbo.Objects_tbl.ObjectReleaseState,
  dbo.Objects_tbl.ObjectReleaseDate,
  
  dbo.Objects_tbl.MultiCadAP,          -- #New:21.08.2019/S.Gueth
  dbo.Objects_tbl.MultiCadItemRevs,    -- #New:21.08.2019/S.Gueth

  dbo.Objects_tbl.ObjectIsCheckedOut, -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectCheckoutDate, -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectCheckoutUser, -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectCheckOutChangeId, -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectWhereUsedN,       -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectIsMFKObj,         -- #New: 28.06.2022/J.Fes

  
--  dbo.Objects_tbl.ObjectNote,        -- #Removed 17.06.2016 J.Fes
  dbo.Objects_tbl.ObjectPrjIds,
  dbo.Objects_tbl.ObjectSyncTrigger,   -- #New: 17.06.2016/J.Fes
  dbo.Objects_tbl.ImportObjectErr,
  dbo.Objects_tbl.ProcStateCol01,
  dbo.Objects_tbl.ProcStateCol02,
  dbo.Objects_tbl.ProcStateCol03,
  dbo.Objects_tbl.ProcStateCol04,
  dbo.Objects_tbl.ProcStateCol05,
  dbo.Objects_tbl.ProcStateCol06,
  dbo.Objects_tbl.ProcStateCol07,
  dbo.Objects_tbl.ProcStateCol08,
  dbo.Objects_tbl.ProcStateCol09,
  dbo.Objects_tbl.ProcStateCol10,

  -- #New 07.08.2014/J.Fes
  dbo.Objects_tbl.ProcStateCol11,
  dbo.Objects_tbl.ProcStateCol12,
  dbo.Objects_tbl.ProcStateCol13,
  dbo.Objects_tbl.ProcStateCol14,
  dbo.Objects_tbl.ProcStateCol15,
  dbo.Objects_tbl.ProcStateCol16,
  dbo.Objects_tbl.ProcStateCol17,
  dbo.Objects_tbl.ProcStateCol18,
  dbo.Objects_tbl.ProcStateCol19,
  dbo.Objects_tbl.ProcStateCol20,

  -- #New 07.11.2022/J.Fes
  dbo.Objects_tbl.ProcStateCol21,
  dbo.Objects_tbl.ProcStateCol22,
  dbo.Objects_tbl.ProcStateCol23,
  dbo.Objects_tbl.ProcStateCol24,
  dbo.Objects_tbl.ProcStateCol25,
  dbo.Objects_tbl.ProcStateCol26,
  dbo.Objects_tbl.ProcStateCol27,
  dbo.Objects_tbl.ProcStateCol28,
  dbo.Objects_tbl.ProcStateCol29,
  dbo.Objects_tbl.ProcStateCol30,

  -- #New 07.11.2022/J.Fes
  dbo.Objects_tbl.ProcStateCol31,
  dbo.Objects_tbl.ProcStateCol32,
  dbo.Objects_tbl.ProcStateCol33,
  dbo.Objects_tbl.ProcStateCol34,
  dbo.Objects_tbl.ProcStateCol35,
  dbo.Objects_tbl.ProcStateCol36,
  dbo.Objects_tbl.ProcStateCol37,
  dbo.Objects_tbl.ProcStateCol38,
  dbo.Objects_tbl.ProcStateCol39,
  dbo.Objects_tbl.ProcStateCol40,

  dbo.Objects_tbl.ProcStateColMc, -- #New:02.03.2016/J.Fes
  dbo.Objects_tbl.ProcStateColRf,
  dbo.Objects_tbl.ProcStateColUn, -- #New:26.11.2021/S.Gueth

  -- #New 07.08.2014/J.Fes
  -- #LUp 14.01.2022/J.Fes from ObjectAttrXX IrObjectAttrYY
  -- #Doc: 18.01.2022/J.Fes on change see \JobManagerDataModel\SiteDef.vb --> Public ReadOnly Property DataSourceColumesGet() As List(Of String)
  dbo.Objects_tbl.ObjectAttr01 AS IrObjectAttr01,
  dbo.Objects_tbl.ObjectAttr02 AS IrObjectAttr02,
  dbo.Objects_tbl.ObjectAttr03 AS IrObjectAttr03,
  dbo.Objects_tbl.ObjectAttr04 AS IrObjectAttr04,
  dbo.Objects_tbl.ObjectAttr05 AS IrObjectAttr05,
  dbo.Objects_tbl.ObjectAttr06 AS IrObjectAttr06,
  dbo.Objects_tbl.ObjectAttr07 AS IrObjectAttr07,
  dbo.Objects_tbl.ObjectAttr08 AS IrObjectAttr08,
  dbo.Objects_tbl.ObjectAttr09 AS IrObjectAttr09,
  dbo.Objects_tbl.ObjectAttr10 AS IrObjectAttr10

FROM
  --  WITH (nolock) engefuegt 09.07.2010 J.Fes
  dbo.Objects_tbl WITH (nolock)
WHERE
  dbo.Objects_tbl.ObjectClass = 'IR'
GO




Print '-- [DS_AndProcessData_UDF] ----------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "DS_AndProcessData_UDF")
DROP FUNCTION DS_AndProcessData_UDF
GO

CREATE FUNCTION DS_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  DS_with_Irs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
--  PD_tbl.ObjectCliName AS AAPDObjectCliName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
--  PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  -- rem new 06.11.2021
  PD_tbl.ProcessTimeDuration,

  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer ,
  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData,
  -- eingetragen 05.04.2013 19:30 J.Fes
  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,
  -- added 26.11.2021 S.Gueth
  PD_tbl.ProcessCategory,
  -- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink,
  --  eingetragen 22.10.2008 J.Fes
   --  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol,
  --  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes ,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  DS_with_Irs_View.IrName AS N,
  -- #New: 04.02.2018 J.Fes to get easy process status of @ProcessProcStateCol
  -- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]

    WHEN 'ProcStateCol21' THEN [ProcStateCol21]
    WHEN 'ProcStateCol22' THEN [ProcStateCol22]
    WHEN 'ProcStateCol23' THEN [ProcStateCol23]
    WHEN 'ProcStateCol24' THEN [ProcStateCol24]
    WHEN 'ProcStateCol25' THEN [ProcStateCol25]
    WHEN 'ProcStateCol26' THEN [ProcStateCol26]
    WHEN 'ProcStateCol27' THEN [ProcStateCol27]
    WHEN 'ProcStateCol28' THEN [ProcStateCol28]
    WHEN 'ProcStateCol29' THEN [ProcStateCol29]
    WHEN 'ProcStateCol30' THEN [ProcStateCol30]
    WHEN 'ProcStateCol31' THEN [ProcStateCol31]
    WHEN 'ProcStateCol32' THEN [ProcStateCol32]
    WHEN 'ProcStateCol33' THEN [ProcStateCol33]
    WHEN 'ProcStateCol34' THEN [ProcStateCol34]
    WHEN 'ProcStateCol35' THEN [ProcStateCol35]
    WHEN 'ProcStateCol36' THEN [ProcStateCol36]
    WHEN 'ProcStateCol37' THEN [ProcStateCol37]
    WHEN 'ProcStateCol38' THEN [ProcStateCol38]
    WHEN 'ProcStateCol39' THEN [ProcStateCol39]
    WHEN 'ProcStateCol40' THEN [ProcStateCol40]

    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
    WHEN 'ProcStateColUn' THEN [ProcStateColUn]
   END
FROM
  --  27.09.2011  WITH (nolock) eingefuegt J.Fes
  DS_with_Irs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
      WHERE ProcessProcStateCol=@ProcessProcStateCol
       -- 01.08.2015/J.Fes removed
       -- AND ObjectClass = 'DS'
     ) as PD_tbl
  ON (DS_with_Irs_View.Id = PD_tbl.Objects_tbl_Id)
)
GO

Print '-- [DS_AndProcessData_GroupedByProcCategory_UDF] ------------------------'
If Exists (select Name
           from sysobjects
            where Name = "DS_AndProcessData_GroupedByProcCategory_UDF")
DROP FUNCTION DS_AndProcessData_GroupedByProcCategory_UDF
GO

CREATE FUNCTION DS_AndProcessData_GroupedByProcCategory_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  DS_with_Irs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
--  PD_tbl.ObjectCliName AS AAPDObjectCliName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
--  PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  -- rem new 06.11.2021
  PD_tbl.ProcessTimeDuration,

  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer ,
  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData,
  -- eingetragen 05.04.2013 19:30 J.Fes
  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,
  -- added 26.11.2021 S.Gueth
  PD_tbl.ProcessCategory,
  -- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink,
  --  eingetragen 22.10.2008 J.Fes
   --  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol,
  --  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes ,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  DS_with_Irs_View.IrName AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  -- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]

    WHEN 'ProcStateCol21' THEN [ProcStateCol21]
    WHEN 'ProcStateCol22' THEN [ProcStateCol22]
    WHEN 'ProcStateCol23' THEN [ProcStateCol23]
    WHEN 'ProcStateCol24' THEN [ProcStateCol24]
    WHEN 'ProcStateCol25' THEN [ProcStateCol25]
    WHEN 'ProcStateCol26' THEN [ProcStateCol26]
    WHEN 'ProcStateCol27' THEN [ProcStateCol27]
    WHEN 'ProcStateCol28' THEN [ProcStateCol28]
    WHEN 'ProcStateCol29' THEN [ProcStateCol29]
    WHEN 'ProcStateCol30' THEN [ProcStateCol30]
    WHEN 'ProcStateCol31' THEN [ProcStateCol31]
    WHEN 'ProcStateCol32' THEN [ProcStateCol32]
    WHEN 'ProcStateCol33' THEN [ProcStateCol33]
    WHEN 'ProcStateCol34' THEN [ProcStateCol34]
    WHEN 'ProcStateCol35' THEN [ProcStateCol35]
    WHEN 'ProcStateCol36' THEN [ProcStateCol36]
    WHEN 'ProcStateCol37' THEN [ProcStateCol37]
    WHEN 'ProcStateCol38' THEN [ProcStateCol38]
    WHEN 'ProcStateCol39' THEN [ProcStateCol39]
    WHEN 'ProcStateCol40' THEN [ProcStateCol40]

    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
    WHEN 'ProcStateColUn' THEN [ProcStateColUn]
   END
FROM
  --  27.09.2011  WITH (nolock) eingefuegt J.Fes
  DS_with_Irs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
      WHERE ProcessProcStateCol=@ProcessProcStateCol
       -- 01.08.2015/J.Fes removed
       -- AND ObjectClass = 'DS'
     ) as PD_tbl
  ON (DS_with_Irs_View.Id = PD_tbl.Objects_tbl_Id)
  INNER JOIN (SELECT ProcessCategory, Objects_tbl_ID, MAX(ProcessTimeStart) ProcessTimeStart FROM JobControl_ObjectsProcessData_tbl GROUP BY Objects_tbl_ID,ProcessCategory) PDGrouped_tbl
  ON PDGrouped_tbl.Objects_tbl_ID = PD_tbl.Objects_tbl_ID and PDGrouped_tbl.ProcessCategory = PD_tbl.ProcessCategory and PDGrouped_tbl.ProcessTimeStart = PD_tbl.ProcessTimeStart
)
GO


Print '-- [IRs_AndProcessData_UDF] ---------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "IRs_AndProcessData_UDF")
DROP FUNCTION IRs_AndProcessData_UDF
GO

CREATE FUNCTION IRs_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  IRs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
-- PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  -- rem new 06.11.2021
  PD_tbl.ProcessTimeDuration,

  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer ,
  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg ,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData ,

-- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink ,

-- eingetragen 20.08.2013 J.Fes
  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,
  -- added 26.11.2021 S.Gueth
  PD_tbl.ProcessCategory,

--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  IRs_View.IrName AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  -- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]

    WHEN 'ProcStateCol21' THEN [ProcStateCol21]
    WHEN 'ProcStateCol22' THEN [ProcStateCol22]
    WHEN 'ProcStateCol23' THEN [ProcStateCol23]
    WHEN 'ProcStateCol24' THEN [ProcStateCol24]
    WHEN 'ProcStateCol25' THEN [ProcStateCol25]
    WHEN 'ProcStateCol26' THEN [ProcStateCol26]
    WHEN 'ProcStateCol27' THEN [ProcStateCol27]
    WHEN 'ProcStateCol28' THEN [ProcStateCol28]
    WHEN 'ProcStateCol29' THEN [ProcStateCol29]
    WHEN 'ProcStateCol30' THEN [ProcStateCol30]
    WHEN 'ProcStateCol31' THEN [ProcStateCol31]
    WHEN 'ProcStateCol32' THEN [ProcStateCol32]
    WHEN 'ProcStateCol33' THEN [ProcStateCol33]
    WHEN 'ProcStateCol34' THEN [ProcStateCol34]
    WHEN 'ProcStateCol35' THEN [ProcStateCol35]
    WHEN 'ProcStateCol36' THEN [ProcStateCol36]
    WHEN 'ProcStateCol37' THEN [ProcStateCol37]
    WHEN 'ProcStateCol38' THEN [ProcStateCol38]
    WHEN 'ProcStateCol39' THEN [ProcStateCol39]
    WHEN 'ProcStateCol40' THEN [ProcStateCol40]

    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
    WHEN 'ProcStateColUn' THEN [ProcStateColUn]
   END
  FROM
  -- DS_with_Irs_View
  -- 06.02.2011 13:27 WITH (nolock) WHERE Proc.. eingefuegt J.Fes
  -- 27.09.2011 20:37 IRs_View WITH (nolock) engefuegt J.Fes
  IRs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
       WHERE ProcessProcStateCol=@ProcessProcStateCol
      -- 01.08.2015/J.Fes removed
      -- and ObjectClass = 'IR'
      ) as PD_tbl
  ON (IRs_View.Id = PD_tbl.Objects_tbl_Id)
)
GO

--------------------------------------------------------------------------------
Print '-- [IRs_AndProcessData_GroupedByProcCategory_UDF] -----------------------'
If Exists (select Name
           from sysobjects
            where Name = "IRs_AndProcessData_GroupedByProcCategory_UDF")
DROP FUNCTION IRs_AndProcessData_GroupedByProcCategory_UDF
GO

CREATE FUNCTION IRs_AndProcessData_GroupedByProcCategory_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  IRs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
-- PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  -- rem new 06.11.2021
  PD_tbl.ProcessTimeDuration,

  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer ,
  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg ,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData ,

-- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink ,

-- eingetragen 20.08.2013 J.Fes
  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,
  -- added 26.11.2021 S.Gueth
  PD_tbl.ProcessCategory,

--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  IRs_View.IrName AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  -- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]

    WHEN 'ProcStateCol21' THEN [ProcStateCol21]
    WHEN 'ProcStateCol22' THEN [ProcStateCol22]
    WHEN 'ProcStateCol23' THEN [ProcStateCol23]
    WHEN 'ProcStateCol24' THEN [ProcStateCol24]
    WHEN 'ProcStateCol25' THEN [ProcStateCol25]
    WHEN 'ProcStateCol26' THEN [ProcStateCol26]
    WHEN 'ProcStateCol27' THEN [ProcStateCol27]
    WHEN 'ProcStateCol28' THEN [ProcStateCol28]
    WHEN 'ProcStateCol29' THEN [ProcStateCol29]
    WHEN 'ProcStateCol30' THEN [ProcStateCol30]
    WHEN 'ProcStateCol31' THEN [ProcStateCol31]
    WHEN 'ProcStateCol32' THEN [ProcStateCol32]
    WHEN 'ProcStateCol33' THEN [ProcStateCol33]
    WHEN 'ProcStateCol34' THEN [ProcStateCol34]
    WHEN 'ProcStateCol35' THEN [ProcStateCol35]
    WHEN 'ProcStateCol36' THEN [ProcStateCol36]
    WHEN 'ProcStateCol37' THEN [ProcStateCol37]
    WHEN 'ProcStateCol38' THEN [ProcStateCol38]
    WHEN 'ProcStateCol39' THEN [ProcStateCol39]
    WHEN 'ProcStateCol40' THEN [ProcStateCol40]

    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
    WHEN 'ProcStateColUn' THEN [ProcStateColUn]
   END
  FROM
  -- DS_with_Irs_View
  -- 06.02.2011 13:27 WITH (nolock) WHERE Proc.. eingefuegt J.Fes
  -- 27.09.2011 20:37 IRs_View WITH (nolock) engefuegt J.Fes
  IRs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
       WHERE ProcessProcStateCol=@ProcessProcStateCol
      -- 01.08.2015/J.Fes removed
      -- and ObjectClass = 'IR'
      ) as PD_tbl
  ON (IRs_View.Id = PD_tbl.Objects_tbl_Id)
  INNER JOIN (SELECT ProcessCategory, Objects_tbl_ID, MAX(ProcessTimeStart) ProcessTimeStart FROM JobControl_ObjectsProcessData_tbl GROUP BY Objects_tbl_ID,ProcessCategory) PDGrouped_tbl
  ON PDGrouped_tbl.Objects_tbl_ID = PD_tbl.Objects_tbl_ID and PDGrouped_tbl.ProcessCategory = PD_tbl.ProcessCategory and PDGrouped_tbl.ProcessTimeStart = PD_tbl.ProcessTimeStart
)
GO
--------------------------------------------------------------------------------

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

Print '-- [ITEMs_View #New: 19.08.2013 J.Fes] ----------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ITEMs_View" and type = "V")
Drop View ITEMs_View
GO

CREATE VIEW [ITEMs_View]
-- BasisView: Ausgegeben werden die Items
-- #Doc siehe filter Details in: \02-VB.NET\JobServer\MainForm\Objects\ObjectsFilterPanelUserControl.vb
AS
SELECT
  dbo.Objects_tbl.Id,
  dbo.Objects_tbl.ObjectPuId,
--  dbo.Objects_tbl.ObjectName , -- wird nicht benötigt
  dbo.Objects_tbl.ObjectPuId As ItemPuid,  -- 05.12.2016 Renamed ItemPuId -to- ItemPuid
  dbo.Objects_tbl.ObjectName As ItemId,
  dbo.Objects_tbl.ObjectName2 As ItemName,
  dbo.Objects_tbl.ObjectDescription As ItemDescription,
  dbo.Objects_tbl.ObjectFromSiteId,
  dbo.Objects_tbl.ObjectClass,
  dbo.Objects_tbl.ObjectType As ItemType, -- 20.12.2011 04:48 eingefuegt
  dbo.Objects_tbl.ObjectCliName,
  dbo.Objects_tbl.ObjectOwningSiteId,
  dbo.Objects_tbl.ObjectExportRecord,
  dbo.Objects_tbl.ObjectExportRecordDate,
  dbo.Objects_tbl.ObjectOwner,
  dbo.Objects_tbl.ObjectGroup,
  dbo.Objects_tbl.ObjectCreateDate,
  dbo.Objects_tbl.ObjectLastModUser,
  dbo.Objects_tbl.ObjectLastModDate,
  dbo.Objects_tbl.ObjectLastSavDate,
  dbo.Objects_tbl.ObjectUgMaster_Type,
  dbo.Objects_tbl.ObjectUgMaster_CompCount,
  dbo.Objects_tbl.ObjectUgMaster_CompCountUnique,   -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectUgMaster_APLevels,
--  dbo.Objects_tbl.ObjectUgMaster_FsHitCount,
--  dbo.Objects_tbl.ObjectFs_DPNE,
  dbo.Objects_tbl.ObjectFs_Vol,
--  dbo.Objects_tbl.ObjectFs_NE,
--   dbo.Objects_tbl.ObjectFs_SizeBytes, -- #New: 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--   dbo.Objects_tbl.ObjectFs_HashCode,  -- #New: 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--  dbo.Objects_tbl.ObjectSaveVersion,   -- #New: 22.08.2012 a.becker (KION) 08.11.2012 J.Fes entnommen
--  dbo.Objects_tbl.ObjectUgMaster_PartFamMemName,
  dbo.Objects_tbl.ObjectReleaseState,
  dbo.Objects_tbl.ObjectReleaseDate,
--  dbo.Objects_tbl.ObjectNote,           -- #Removed 17.06.2016 J.Fes
  dbo.Objects_tbl.ObjectPrjIds,
  dbo.Objects_tbl.ObjectSyncTrigger,   -- #New: 17.06.2016/J.Fes
  dbo.Objects_tbl.ImportObjectErr,

  dbo.Objects_tbl.ProcStateCol01,
  dbo.Objects_tbl.ProcStateCol02,
  dbo.Objects_tbl.ProcStateCol03,
  dbo.Objects_tbl.ProcStateCol04,
  dbo.Objects_tbl.ProcStateCol05,
  dbo.Objects_tbl.ProcStateCol06,
  dbo.Objects_tbl.ProcStateCol07,
  dbo.Objects_tbl.ProcStateCol08,
  dbo.Objects_tbl.ProcStateCol09,
  dbo.Objects_tbl.ProcStateCol10,

  -- #New 07.08.2014/J.Fes
  dbo.Objects_tbl.ProcStateCol11,
  dbo.Objects_tbl.ProcStateCol12,
  dbo.Objects_tbl.ProcStateCol13,
  dbo.Objects_tbl.ProcStateCol14,
  dbo.Objects_tbl.ProcStateCol15,
  dbo.Objects_tbl.ProcStateCol16,
  dbo.Objects_tbl.ProcStateCol17,
  dbo.Objects_tbl.ProcStateCol18,
  dbo.Objects_tbl.ProcStateCol19,
  dbo.Objects_tbl.ProcStateCol20,

  -- #New 07.11.2022/J.Fes
  dbo.Objects_tbl.ProcStateCol21,
  dbo.Objects_tbl.ProcStateCol22,
  dbo.Objects_tbl.ProcStateCol23,
  dbo.Objects_tbl.ProcStateCol24,
  dbo.Objects_tbl.ProcStateCol25,
  dbo.Objects_tbl.ProcStateCol26,
  dbo.Objects_tbl.ProcStateCol27,
  dbo.Objects_tbl.ProcStateCol28,
  dbo.Objects_tbl.ProcStateCol29,
  dbo.Objects_tbl.ProcStateCol30,

  -- #New 07.11.2022/J.Fes
  dbo.Objects_tbl.ProcStateCol31,
  dbo.Objects_tbl.ProcStateCol32,
  dbo.Objects_tbl.ProcStateCol33,
  dbo.Objects_tbl.ProcStateCol34,
  dbo.Objects_tbl.ProcStateCol35,
  dbo.Objects_tbl.ProcStateCol36,
  dbo.Objects_tbl.ProcStateCol37,
  dbo.Objects_tbl.ProcStateCol38,
  dbo.Objects_tbl.ProcStateCol39,
  dbo.Objects_tbl.ProcStateCol40,


  dbo.Objects_tbl.ProcStateColMc, -- #New:02.03.2016/J.Fes
  dbo.Objects_tbl.ProcStateColRf,
  dbo.Objects_tbl.ProcStateColUn, -- #New:26.11.2021/S.Gueth

  -- #New: 07.08.2014/J.Fes
  -- #LUp: 14.01.2022/J.Fes renamed ObjectAttrXX -> ItemObjectAttrXX
  -- #Doc: 18.01.2022/J.Fes on change see \JobManagerDataModel\SiteDef.vb --> Public ReadOnly Property DataSourceColumesGet() As List(Of String)
  dbo.Objects_tbl.ObjectAttr01 AS ItemObjectAttr01,
  dbo.Objects_tbl.ObjectAttr02 AS ItemObjectAttr02,
  dbo.Objects_tbl.ObjectAttr03 AS ItemObjectAttr03,
  dbo.Objects_tbl.ObjectAttr04 AS ItemObjectAttr04,
  dbo.Objects_tbl.ObjectAttr05 AS ItemObjectAttr05,
  dbo.Objects_tbl.ObjectAttr06 AS ItemObjectAttr06,
  dbo.Objects_tbl.ObjectAttr07 AS ItemObjectAttr07,
  dbo.Objects_tbl.ObjectAttr08 AS ItemObjectAttr08,
  dbo.Objects_tbl.ObjectAttr09 AS ItemObjectAttr09,
  dbo.Objects_tbl.ObjectAttr10 AS ItemObjectAttr10,


  dbo.Objects_tbl.ObjectIsCheckedOut,     -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectCheckoutDate,     -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectCheckoutUser,     -- #New: 25.06.2020/S.Gueth
  dbo.Objects_tbl.ObjectCheckOutChangeId, -- #New: 25.06.2020/S.Gueth

  dbo.Objects_tbl.ObjectIsMFKObj          -- #New: 28.06.2022/J.Fes

FROM
  --  WITH (nolock) engefuegt 09.07.2010 J.Fes
  dbo.Objects_tbl WITH (nolock)
WHERE
  dbo.Objects_tbl.ObjectClass = 'I'
GO

Print '-- [ITEMs_AndProcessData_UDF #New: 19.08.2013/J.Fes] --------------------'
If Exists (select Name
           from sysobjects
            where Name = "ITEMs_AndProcessData_UDF")
DROP FUNCTION ITEMs_AndProcessData_UDF
GO

CREATE FUNCTION ITEMs_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  ITEMs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
-- PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  -- rem new 06.11.2021
  PD_tbl.ProcessTimeDuration,

  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer,

  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg ,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData ,

  -- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink ,

  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,
  -- added 26.11.2021 S.Gueth
  PD_tbl.ProcessCategory,

--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  ITEMs_View.ItemId AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  -- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]

    WHEN 'ProcStateCol21' THEN [ProcStateCol21]
    WHEN 'ProcStateCol22' THEN [ProcStateCol22]
    WHEN 'ProcStateCol23' THEN [ProcStateCol23]
    WHEN 'ProcStateCol24' THEN [ProcStateCol24]
    WHEN 'ProcStateCol25' THEN [ProcStateCol25]
    WHEN 'ProcStateCol26' THEN [ProcStateCol26]
    WHEN 'ProcStateCol27' THEN [ProcStateCol27]
    WHEN 'ProcStateCol28' THEN [ProcStateCol28]
    WHEN 'ProcStateCol29' THEN [ProcStateCol29]
    WHEN 'ProcStateCol30' THEN [ProcStateCol30]
    WHEN 'ProcStateCol31' THEN [ProcStateCol31]
    WHEN 'ProcStateCol32' THEN [ProcStateCol32]
    WHEN 'ProcStateCol33' THEN [ProcStateCol33]
    WHEN 'ProcStateCol34' THEN [ProcStateCol34]
    WHEN 'ProcStateCol35' THEN [ProcStateCol35]
    WHEN 'ProcStateCol36' THEN [ProcStateCol36]
    WHEN 'ProcStateCol37' THEN [ProcStateCol37]
    WHEN 'ProcStateCol38' THEN [ProcStateCol38]
    WHEN 'ProcStateCol39' THEN [ProcStateCol39]
    WHEN 'ProcStateCol40' THEN [ProcStateCol40]

    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
    WHEN 'ProcStateColUn' THEN [ProcStateColUn]
  END
FROM
  -- DS_with_ITEMs_View
  -- 06.02.2011 13:27 WITH (nolock) WHERE Proc.. eingefuegt J.Fes
  -- 27.09.2011 20:37 ITEMs_View WITH (nolock) engefuegt J.Fes
  ITEMs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
      WHERE ProcessProcStateCol=@ProcessProcStateCol
      -- 01.08.2015/J.Fes removed
      -- and ObjectClass = 'I'
      ) as PD_tbl
  ON (ITEMs_View.Id = PD_tbl.Objects_tbl_Id)
)
GO

Print '-- [ITEMs_AndProcessData_GroupedByProcCategory_UDF] ---------------------'
If Exists (select Name
           from sysobjects
            where Name = "ITEMs_AndProcessData_GroupedByProcCategory_UDF")
DROP FUNCTION ITEMs_AndProcessData_GroupedByProcCategory_UDF
GO

CREATE FUNCTION ITEMs_AndProcessData_GroupedByProcCategory_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  ITEMs_View.*,
  PD_tbl.Id As zzzPDat_DRId ,
  PD_tbl.Objects_tbl_Id ,
-- 01.08.2015/J.Fes removed
--  PD_tbl.JobControlRuleName,
  PD_tbl.ProcessPacketName,
-- 01.08.2015/J.Fes removed
-- PD_tbl.ProcessOnSiteId,
  PD_tbl.ProcessState,
  PD_tbl.ProcessTimeStart,
  PD_tbl.ProcessTimeEnd,
  -- rem new 06.11.2021
  PD_tbl.ProcessTimeDuration,

  PD_tbl.ProcessCMDLine,
  PD_tbl.ProcessOnComputer,

  PD_tbl.ProcessResultCode,
  PD_tbl.ProcessResultMsg ,
  -- eingetragen 08.07.2013 16:48 J.Fes
  PD_tbl.ProcessResultExtendedData ,

  -- eingetragen 12.07.2009 J.Fes
  PD_tbl.ProcessResultHasWarning,
  PD_tbl.ProcessResultLink ,

  PD_tbl.ProcessPrgUsedVer,
  PD_tbl.ProcessObjectSaveVersion,
  -- added 26.11.2021 S.Gueth
  PD_tbl.ProcessCategory,

--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_Id,
  PD_tbl.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  PD_tbl.Row_isLastRes,

  --  eingetragen 26.08.2015/A.Becker um den SQL Like bei ItemName filtern zu verkürzen
  ITEMs_View.ItemId AS N,
  -- #New 04.02.18 J.Fes to get easy process status of @ProcessProcStateCol
  -- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
  ObjProcStateValue = CASE @ProcessProcStateCol
    WHEN 'ProcStateCol01' THEN [ProcStateCol01]
    WHEN 'ProcStateCol02' THEN [ProcStateCol02]
    WHEN 'ProcStateCol03' THEN [ProcStateCol03]
    WHEN 'ProcStateCol04' THEN [ProcStateCol04]
    WHEN 'ProcStateCol05' THEN [ProcStateCol05]
    WHEN 'ProcStateCol06' THEN [ProcStateCol06]
    WHEN 'ProcStateCol07' THEN [ProcStateCol07]
    WHEN 'ProcStateCol08' THEN [ProcStateCol08]
    WHEN 'ProcStateCol09' THEN [ProcStateCol09]
    WHEN 'ProcStateCol10' THEN [ProcStateCol10]
    WHEN 'ProcStateCol11' THEN [ProcStateCol11]
    WHEN 'ProcStateCol12' THEN [ProcStateCol12]
    WHEN 'ProcStateCol13' THEN [ProcStateCol13]
    WHEN 'ProcStateCol14' THEN [ProcStateCol14]
    WHEN 'ProcStateCol15' THEN [ProcStateCol15]
    WHEN 'ProcStateCol16' THEN [ProcStateCol16]
    WHEN 'ProcStateCol17' THEN [ProcStateCol17]
    WHEN 'ProcStateCol18' THEN [ProcStateCol18]
    WHEN 'ProcStateCol19' THEN [ProcStateCol19]
    WHEN 'ProcStateCol20' THEN [ProcStateCol20]

    WHEN 'ProcStateCol21' THEN [ProcStateCol21]
    WHEN 'ProcStateCol22' THEN [ProcStateCol22]
    WHEN 'ProcStateCol23' THEN [ProcStateCol23]
    WHEN 'ProcStateCol24' THEN [ProcStateCol24]
    WHEN 'ProcStateCol25' THEN [ProcStateCol25]
    WHEN 'ProcStateCol26' THEN [ProcStateCol26]
    WHEN 'ProcStateCol27' THEN [ProcStateCol27]
    WHEN 'ProcStateCol28' THEN [ProcStateCol28]
    WHEN 'ProcStateCol29' THEN [ProcStateCol29]
    WHEN 'ProcStateCol30' THEN [ProcStateCol30]
    WHEN 'ProcStateCol31' THEN [ProcStateCol31]
    WHEN 'ProcStateCol32' THEN [ProcStateCol32]
    WHEN 'ProcStateCol33' THEN [ProcStateCol33]
    WHEN 'ProcStateCol34' THEN [ProcStateCol34]
    WHEN 'ProcStateCol35' THEN [ProcStateCol35]
    WHEN 'ProcStateCol36' THEN [ProcStateCol36]
    WHEN 'ProcStateCol37' THEN [ProcStateCol37]
    WHEN 'ProcStateCol38' THEN [ProcStateCol38]
    WHEN 'ProcStateCol39' THEN [ProcStateCol39]
    WHEN 'ProcStateCol40' THEN [ProcStateCol40]

    WHEN 'ProcStateColMc' THEN [ProcStateColMc]
    WHEN 'ProcStateColRf' THEN [ProcStateColRf]
    WHEN 'ProcStateColUn' THEN [ProcStateColUn]
  END
FROM
  -- DS_with_ITEMs_View
  -- 06.02.2011 13:27 WITH (nolock) WHERE Proc.. eingefuegt J.Fes
  -- 27.09.2011 20:37 ITEMs_View WITH (nolock) engefuegt J.Fes
  ITEMs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock)
      WHERE ProcessProcStateCol=@ProcessProcStateCol
      -- 01.08.2015/J.Fes removed
      -- and ObjectClass = 'I'
      ) as PD_tbl
  ON (ITEMs_View.Id = PD_tbl.Objects_tbl_Id)
  INNER JOIN (SELECT ProcessCategory, Objects_tbl_ID, MAX(ProcessTimeStart) ProcessTimeStart FROM JobControl_ObjectsProcessData_tbl GROUP BY Objects_tbl_ID,ProcessCategory) PDGrouped_tbl
  ON PDGrouped_tbl.Objects_tbl_ID = PD_tbl.Objects_tbl_ID and PDGrouped_tbl.ProcessCategory = PD_tbl.ProcessCategory and PDGrouped_tbl.ProcessTimeStart = PD_tbl.ProcessTimeStart
)
GO

--------------------------------------------------------------------------------
Print '-- [GetGroupKeysForObject_UDF] ------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "GetGroupKeysForObject_UDF" )
DROP FUNCTION GetGroupKeysForObject_UDF
GO

-- =============================================
-- Author:		Alexander Becker
-- Create date: 17.11.2017
-- Description:	Gibt die Gruppen zurück in denen ein Objekt verknüpft ist
-- =============================================


CREATE FUNCTION GetGroupKeysForObject_UDF
(
	-- Add the parameters for the function here
	@objectId UNIQUEIDENTIFIER,
 @objectGroupSelected GUID_TABLE READONLY,
 @returnKind VARCHAR(16) -- Values: returnAll or returnSelected
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	-- Declare the return variable here
 -- #LUp: 10.06.2022 A.Becker + J.Fes now in case returnAll Groups are always Sort by GroupKey Name
 --                                   #Exa: GR01, GR10, GR20, GrZZ100
	DECLARE @res NVARCHAR(MAX)

	-- Add the T-SQL statements to compute the return value here  + '//' + [GroupName]

 IF @returnKind = 'returnAll'
 BEGIN

  SELECT @res = COALESCE(@res + ', ', '') + [GroupKey]
 	 FROM Objects_ObjectGroups_Rel_tbl REL
 	 JOIN ObjectGroups_tbl OG ON OG.Id=REL.ObjectGroupId
 	 WHERE ObjectId=@objectId
   ORDER BY [GroupKey]

 END ELSE BEGIN

  SELECT TOP 1 @res = [GroupKey]
 	 FROM Objects_ObjectGroups_Rel_tbl REL
 	 JOIN ObjectGroups_tbl OG ON OG.Id=REL.ObjectGroupId
 	 WHERE ObjectId=@objectId AND
         EXISTS (SELECT 1 FROM @objectGroupSelected SEL WHERE SEL.Id=REL.ObjectGroupId)
   ORDER BY [GroupKey]

 END

	-- Return the result of the function
	RETURN @res

END
GO

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




Print '-- [ObjectsAndProcessData_PrepareIrObjectNameList] ----------------------'
If Exists (select Name
           from sysobjects
           where Name = 'ObjectsAndProcessData_PrepareIrObjectNameList')
Drop Procedure ObjectsAndProcessData_PrepareIrObjectNameList
GO

CREATE PROCEDURE ObjectsAndProcessData_PrepareIrObjectNameList
	-- Add the parameters for the stored procedure here
	@WhereIrObjectNameList NVARCHAR(MAX),
	@itemNameColumn SYSNAME,
	@includeLikeSQL NVARCHAR(MAX) OUTPUT,
	@excludeLikeSQL NVARCHAR(MAX) OUTPUT
AS
BEGIN

	-- NEW: 26.08.2015/A.Becker
    -- processes a string containing item name filters
	-- fills two temp tables (#irNameQueryListInclude and #irNameQueryListExclude) with full item names
	-- returns two sql-where strings with ORed Like filters for include and exclude wildcard item names

	SET NOCOUNT ON;

	-- a table for temporary store the splitted string along with metadata [rowNumber] and [isWildcard] indicator.
	DECLARE @irNameQueryListSplit 
		TABLE (irNameQuery NVARCHAR(512), rowNumber INT, isWildcard BIT, escapeChar NVARCHAR(1) NULL)

	-- split the item name list string to the table variable and generate the metadata
	INSERT INTO @irNameQueryListSplit (irNameQuery, rowNumber, isWildcard, escapeChar)
		SELECT CASE WHEN CHARINDEX(N'<LIKE', Value) = 1 
                    THEN dbo.StringTrim_UDF(SUBSTRING(Value, CHARINDEX(N'>', Value)+1, 512))
                    ELSE dbo.StringTrim_UDF(Value) 
               END AS irNameQuery, 
			   rowNumber,
			   CASE WHEN CHARINDEX(N'<LIKE', Value) = 1 
                    THEN 1 
                    ELSE 0 
               END AS isWildcard,
			   CASE WHEN CHARINDEX(N'<LIKE-ESCAPE:', Value) = 1 
                    THEN SUBSTRING(Value, CHARINDEX(N':', Value)+1, 1) 
                    ELSE NULL 
               END AS escapeChar
		FROM dbo.SplitStringToTable_UDF(@WhereIrObjectNameList, CHAR(10))
		WHERE LEN(Value) <= 512 AND dbo.StringHasText_UDF(Value) = 1
		OPTION (MAXRECURSION 0)

    -- search for and store the include/exclude marker delimiter (remember its rowNumber)
	DECLARE @excludeMarkerrowNumber INT
		SELECT @excludeMarkerrowNumber = rowNumber
		FROM @irNameQueryListSplit
		WHERE irNameQuery IN (N'!NOT!', N'!not!', N'!NO!', N'!no!')

	-- build include list of wildcards
    DECLARE @wildcardIncludeList TABLE (wildcard NVARCHAR(512), escapeChar NVARCHAR(1) NULL)
	INSERT INTO @wildcardIncludeList
	  SELECT irNameQuery, escapeChar
	  FROM @irNameQueryListSplit
	  WHERE (@excludeMarkerrowNumber IS NULL OR rowNumber < @excludeMarkerrowNumber)
		    AND (isWildcard = 1)

	-- build exclude list of wildcards
	DECLARE @wildcardExcludeList TABLE (wildcard NVARCHAR(512), escapeChar NVARCHAR(1) NULL)
	INSERT INTO @wildcardExcludeList
	  SELECT irNameQuery, escapeChar
	  FROM @irNameQueryListSplit
	  WHERE (rowNumber > @excludeMarkerrowNumber)
		    AND (isWildcard = 1)

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

	-- insert all full item names for include into the #temp table
	INSERT INTO #objectNameJoinListInclude (irNameInclude)
		SELECT DISTINCT qITEMS.irNameQuery 
		FROM @irNameQueryListSplit qITEMS
		WHERE (@excludeMarkerrowNumber IS NULL OR qITEMS.rowNumber < @excludeMarkerrowNumber)
		  AND (qITEMS.isWildcard = 0)
		  AND NOT EXISTS (SELECT 1 -- exclude all full item names which are covered by a wildcard search
		                  FROM @wildcardIncludeList
						  WHERE qITEMS.irNameQuery LIKE wildcard ESCAPE escapeChar
				 		 )

	-- insert all full item names for exclude into the #temp table
	INSERT INTO #objectNameJoinListExclude (irNameExclude)
		SELECT DISTINCT qITEMS.irNameQuery 
		FROM @irNameQueryListSplit qITEMS
		WHERE (@excludeMarkerrowNumber IS NOT NULL AND qITEMS.rowNumber > @excludeMarkerrowNumber)
		  AND (qITEMS.isWildcard = 0)
		  AND NOT EXISTS (SELECT 1 -- exclude all full item names which are covered by a wildcard search
		                  FROM @wildcardExcludeList
						  WHERE qITEMS.irNameQuery LIKE wildcard ESCAPE escapeChar
                          ) 

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

	-- builds an ORed LIKE string from all include wildcards.
	-- source: 00123%, 0256%
	-- dest:   ItemName LIKE N'00123%' OR ItemName LIKE N'0256%'
	SELECT @includeLikeSQL = COALESCE(@includeLikeSQL + N' OR ', N'') 
	                       -- + N'(' 
						   + @itemNameColumn + N' LIKE ''' + wildcard + '''' + ISNULL(N' ESCAPE ''' + escapeChar + '''', N'')
						   -- + N')'
		FROM @wildcardIncludeList

	-- builds an ORed LIKE string from all exclude wildcards.
	-- source: 00123%, 0256%
	-- dest:   ItemName LIKE N'00123%' OR ItemName LIKE N'0256%'
	SELECT @excludeLikeSQL = COALESCE(@excludeLikeSQL + N' OR ', N'') 
	                       -- + N'(' 
						   + @itemNameColumn + N' LIKE ''' + wildcard + '''' + ISNULL(N' ESCAPE ''' + escapeChar + '''', N'')
						   -- + N')'
		FROM @wildcardExcludeList

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


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


CREATE PROCEDURE dbo.ObjectsAndProcessData_DisSearch
(
     @disType    varchar(5),      -- *, TXT, PIM, DIM
     @disIncludeFulltext nvarchar(4000),
     @disExcludeFulltext nvarchar(4000),
     @disIncludeLike     nvarchar(4000)
)
AS
BEGIN

	-- Result TABLE #disSearchMatches( objectId UNIQUEIDENTIFIER, disId UNIQUEIDENTIFIER )
 -- 05.03.2023/Issue
--  Msg 468, Level 16, State 9, Server ics030000132, Procedure ObjectsAndProcessData_DisSearch, Line 403
-- Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like operation.
-- Msg 468, Level 16, State 9, Server ics030000132, Procedure ObjectsAndProcessData_DisSearch, Line 422 + 1235 = 1657
-- Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like operation.
-- -- [ObjectsAndProcessData] ----------------------------------------------
-- The module 'ObjectsAndProcessData' depends on the missing object 'dbo.ObjectsAndProcessData_DisSearch'. The module will still be created; however, it cannot run successfully until the object exists.
-- The module 'ObjectsAndProcessData' depends on the missing object 'dbo.ObjectsAndProcessData_DisSearch'. The module will still be created; however, it cannot run successfully until the object exists.


	TRUNCATE TABLE #disSearchMatches
	TRUNCATE TABLE #disSearchObjects

    -- default to * when disType is null or empty
    SET @disType = CASE WHEN ISNULL(@disType, N'') = N'' THEN N'*' ELSE @disType END

    -- @disType Mapping
    --  *   -> AllValues
    -- TXT  -> AllText
    -- else -> AllNonText
    DECLARE @fulltextColumn SYSNAME
    SET @fulltextColumn = CASE @disType
                               WHEN N'*'   THEN N'AllValues'
                               WHEN N'PIM' THEN N'AllValues'
                               WHEN N'TXT' THEN N'AllText'
                               ELSE             N'AllNonText'
                          END

--    DECLARE @searchAnyText BIT = CASE WHEN @disIncludeFulltext = N'*' AND dbo.StringHasText_UDF(@disExcludeFulltext) = 0 THEN 1 ELSE 0 END
    -- L.Up: 04.09.2025: S.Gueth
    DECLARE @searchAnyText BIT = CASE
                                     WHEN
                                         @disIncludeFulltext = N'*'
                                         AND dbo.StringHasText_UDF(@disExcludeFulltext) = 0
                                         AND ISNULL(@disIncludeLike, N'') IN (N'', N'%') -- NEUE, ERWEITERTE BEDINGUNG
                                     THEN 1
                                     ELSE 0
                                 END

    DECLARE @excludeAllText BIT = CASE WHEN @disExcludeFulltext = N'*' THEN 1 ELSE 0 END
    
	DECLARE @disSearchObjectIdFilterAvailable BIT = CASE WHEN EXISTS (SELECT 1 FROM #disSearchObjectIdFilter) THEN 1 ELSE 0 END
	DECLARE @dbg NVARCHAR(MAX) = (SELECT * FROM #disSearchObjectIdFilter FOR XML AUTO)
	print @dbg

    -- simple case 1, result is any object containing any text
    -- no fulltext query needed here
    IF @searchAnyText = 1
    BEGIN

        IF @disSearchObjectIdFilterAvailable = 0
		BEGIN

			-- return all sheets where the requested type has any text
			INSERT INTO #disSearchMatches(disId)
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'')
			  ORDER BY Id

			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
			  ORDER BY DIS.ObjIdRef

		END
		ELSE
		BEGIN

			-- return all sheets where the requested type has any text
			INSERT INTO #disSearchMatches(disId)
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'') AND
					EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY Id

			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id) AND
					EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY DIS.ObjIdRef

		END

        RETURN 0

    END
    
    -- simple case 2, result is any object not containing any text
    -- no fulltext query needed here
    IF @excludeAllText = 1 
    BEGIN
    
        -- collect all sheets where the requested type has any text
        DECLARE @excludeMatches TABLE ( disId UNIQUEIDENTIFIER PRIMARY KEY )

		IF @disSearchObjectIdFilterAvailable = 0
		BEGIN

			INSERT INTO @excludeMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND 
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'')
			  ORDER BY DIS.Id

			-- return all sheets not contained in the exclude matches
			INSERT INTO #disSearchMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE NOT EXISTS (SELECT 1 FROM @excludeMatches WHERE disId=DIS.Id)
			  ORDER BY Id
          
			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
			  ORDER BY DIS.ObjIdRef
    
	END
	ELSE
	BEGIN 
			INSERT INTO @excludeMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE (DIS.[Type] = @disType OR @disType = N'*') AND 
					(DIS.[Value] IS NOT NULL AND DIS.[Value] <> N'') AND
			EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY DIS.Id

			-- return all sheets not contained in the exclude matches
			INSERT INTO #disSearchMatches(disId) 
			  SELECT Id
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE NOT EXISTS (SELECT 1 FROM @excludeMatches WHERE disId=DIS.Id) AND
			EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY Id
          
			-- return unique object ids from matches
			INSERT INTO #disSearchObjects(objectId)
			  SELECT DISTINCT ObjIdRef
			  FROM dbo.DrawingIndexingSearch_tbl DIS
			  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id) AND
			EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef)
			  ORDER BY DIS.ObjIdRef
	END

        RETURN 0
    
    END


	-- STEP: Query fulltext catalog for includes and excludes
    --       Collect resulting IDs in table variable

	   DECLARE @fulltextMatches TABLE ( objectId UNIQUEIDENTIFIER, sheet NVARCHAR(128) )
    -- L.Upd: 05.09.2024: S.Gueth
    DECLARE @hasInclude BIT = CASE WHEN dbo.StringHasText_UDF(@disIncludeFulltext) = 1 THEN 1 ELSE 0 END
    DECLARE @hasExclude BIT = CASE WHEN dbo.StringHasText_UDF(@disExcludeFulltext) = 1 AND @disExcludeFulltext <> N'*' THEN 1 ELSE 0 END


-- L.Up: 04.09.2025: S.Gueth; added check for * on disIncludeFulltext
-- search with includes only
IF @hasInclude = 1 AND @hasExclude = 0
BEGIN

    -- NEU: Prüfen, ob nach "allem" gesucht werden soll.
    IF @disIncludeFulltext = N'*'
    BEGIN
        -- FALL 1: @disIncludeFulltext ist '*', also wollen wir keine Volltext-Filterung.
        -- Stattdessen füllen wir @fulltextMatches mit ALLEN Objekten/Sheets,
        -- damit diese später durch den LIKE-Filter weiter eingeschränkt werden können.

        IF @disSearchObjectIdFilterAvailable = 0
        BEGIN
            -- Keine Vorfilterung, also alle Objekte aus der Fulltext-Tabelle nehmen.
            INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet
            FROM dbo.DrawingIndexingSearchFullText_tbl;
        END
        ELSE
        BEGIN
            -- Es gibt eine Vorfilterung, also nur die Objekte nehmen, die im Filter sind.
            INSERT INTO @fulltextMatches(objectId, sheet)
            SELECT ObjIdRef, inSheet
            FROM dbo.DrawingIndexingSearchFullText_tbl DIS
            WHERE EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId = DIS.ObjIdRef);
        END
    END
    ELSE
    BEGIN
        -- FALL 2: @disIncludeFulltext ist ein normaler Suchbegriff.
        -- Hier folgt der komplette, ursprüngliche Code mit der CONTAINS-Logik.

        IF @fulltextColumn = N'AllValues'
        BEGIN
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllValues, @disIncludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllValues, @disIncludeFulltext) AND
                EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
        ELSE IF @fulltextColumn = N'AllText'
        BEGIN
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllText, @disIncludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllText, @disIncludeFulltext) AND
                EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
        ELSE
        BEGIN
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllNonText, @disIncludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllNonText, @disIncludeFulltext) AND
                EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
    END -- Ende der neuen IF/ELSE-Struktur
END



-- L.Up: 04.09.2025: S.Gueth; added check for * on disIncludeFulltext
 -- search with includes and excludes
IF @hasInclude = 1 AND @hasExclude = 1
BEGIN

    -- NEU: Prüfen, ob der "Include"-Teil "alles" bedeutet.
    IF @disIncludeFulltext = N'*'
    BEGIN
        -- FALL 1: Finde alles, was NICHT dem Exclude-Text entspricht.
        -- Die WHERE-Klausel benötigt nur den "NOT CONTAINS"-Teil.

        IF @fulltextColumn = N'AllValues'
        BEGIN
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE NOT CONTAINS(AllValues, @disExcludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE NOT CONTAINS(AllValues, @disExcludeFulltext) AND
                      EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
        ELSE IF @fulltextColumn = N'AllText'
        BEGIN
            -- (Logik für AllText Spalte)
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE NOT CONTAINS(AllText, @disExcludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE NOT CONTAINS(AllText, @disExcludeFulltext) AND
                      EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
        ELSE
        BEGIN
            -- (Logik für AllNonText Spalte)
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE NOT CONTAINS(AllNonText, @disExcludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE NOT CONTAINS(AllNonText, @disExcludeFulltext) AND
                      EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
    END
    ELSE
    BEGIN
        -- FALL 2: @disIncludeFulltext ist ein normaler Suchbegriff.
        -- Hier kann der ursprüngliche Code unverändert bleiben.

        IF @fulltextColumn = N'AllValues'
        BEGIN
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllValues, @disIncludeFulltext) AND NOT CONTAINS(AllValues, @disExcludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllValues, @disIncludeFulltext) AND NOT CONTAINS(AllValues, @disExcludeFulltext) AND
                      EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
        ELSE IF @fulltextColumn = N'AllText'
        BEGIN
            -- (Logik für AllText Spalte)
             IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllText, @disIncludeFulltext) AND NOT CONTAINS(AllText, @disExcludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllText, @disIncludeFulltext) AND NOT CONTAINS(AllText, @disExcludeFulltext) AND
                      EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
        ELSE
        BEGIN
            -- (Logik für AllNonText Spalte)
            IF @disSearchObjectIdFilterAvailable = 0
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllNonText, @disIncludeFulltext) AND NOT CONTAINS(AllNonText, @disExcludeFulltext);
            END
            ELSE
            BEGIN
                INSERT INTO @fulltextMatches(objectId, sheet)
                SELECT ObjIdRef, inSheet
                FROM DrawingIndexingSearchFullText_tbl DIS
                WHERE CONTAINS(AllNonText, @disIncludeFulltext) AND NOT CONTAINS(AllNonText, @disExcludeFulltext) AND
                      EXISTS (SELECT 1 FROM #disSearchObjectIdFilter F WHERE F.objectId=DIS.ObjIdRef);
            END
        END
    END -- Ende der neuen IF/ELSE-Struktur
END
    
    
    
    -- Split like patterns into tables
    DECLARE @includeLikes TABLE (pattern NVARCHAR(1000))
	
    INSERT INTO @includeLikes
	SELECT Value FROM dbo.SplitStringToTable_UDF(@disIncludeLike, N'|')


	IF @disType = '*'
	BEGIN

		-- Return DIS texts matching fulltext and like
		INSERT INTO #disSearchMatches(disId) 
		  SELECT Id
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM @fulltextMatches WHERE objectId = DIS.ObjIdRef AND sheet = DIS.inSheet)
			AND EXISTS (SELECT 1 FROM @includeLikes WHERE DIS.Value LIKE pattern)
		  ORDER BY Id

		-- return unique object ids from matches
		INSERT INTO #disSearchObjects(objectId)
		  SELECT DISTINCT ObjIdRef
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
		  ORDER BY DIS.ObjIdRef

	END
	ELSE
	BEGIN

		-- Return DIS texts matching fulltext and like
		INSERT INTO #disSearchMatches(disId) 
		  SELECT Id
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM @fulltextMatches WHERE objectId = DIS.ObjIdRef AND sheet = DIS.inSheet)
			AND EXISTS (SELECT 1 FROM @includeLikes WHERE DIS.Value LIKE pattern)
			AND (DIS.Type = @disType)
		  ORDER BY Id

		-- return unique object ids from matches
		INSERT INTO #disSearchObjects(objectId)
		  SELECT DISTINCT ObjIdRef
		  FROM dbo.DrawingIndexingSearch_tbl DIS
		  WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE disId=DIS.Id)
			AND (DIS.Type = @disType)
		  ORDER BY DIS.ObjIdRef

	END
END
GO



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


CREATE PROCEDURE ObjectsAndProcessData
(
     @WhereObjectClassSQL   nvarchar(10) = null ,
                                                    -- 25.08.2015 /A.Becker renamed from @WhereIrObjectNameSQL because it contains a value list now.
     @WhereIrObjectNameList nvarchar(max) = null ,  -- 20.11.2023 /A.Stein renamed from @WhereIrObjectNameList to @WhereIrObjectNameList -> case sensitive
     @WhereObjectDataSQL    nvarchar(max) = null ,
     @WhereProcessDataSQL   nvarchar(max) = null ,
     @SelectTop             integer ,
     @ProcessProcStateCol   nvarchar(32),
     @ObjectGroupsSelectedIdList nvarchar(max),  -- 29.08.2015 /A.Becker added
			  @ReturnObjectIdsOnly   bit = 0,
     @ObjInVisibleColList nvarchar(max) = null, -- 16.02.2018 /A.Becker added
     @disType    varchar(5) = null,             -- 12.12.2018 /A.Becker added for Drawing-Index-Search
     @disIncludeFulltext nvarchar(4000) = null, -- 12.12.2018 /A.Becker added for Drawing-Index-Search
     @disExcludeFulltext nvarchar(4000) = null, -- 12.12.2018 /A.Becker added for Drawing-Index-Search
     @disIncludeLike nvarchar(4000) = null,     -- 12.12.2018 /A.Becker added for Drawing-Index-Search
     @groupByPCatLatestRes   bit = 0            -- 28.02.2022 /S.Gueth added for Grouping by ProcessCategory and Latest Result
) AS

BEGIN


  -- Hinweise:
  -- Evtl. Indices fuer die #Temp Tabellen erzeugen
  -- Regel: immer die Datensaetze die die Datenmengen einschraenken zuerst beruecksistigen
  -- 03.09.2014 20:49 /J.Fes jetzt ohne schema tabellen ohne #zztmpObj1_tbl


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

  DECLARE @ExecuteSQLstr      nvarchar(max)
  DECLARE @WhereObjectsSQLstr nvarchar(max)
  DECLARE @SelectTopSQLstr    nvarchar(max)

  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@WhereObjectClassSQL' ,        @WhereObjectClassSQL
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@WhereIrObjectNameList' ,      @WhereIrObjectNameList
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@WhereObjectDataSQL' ,         @WhereObjectDataSQL
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@WhereProcessDataSQL' ,        @WhereProcessDataSQL
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@SelectTop' ,                  @SelectTop
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@ProcessProcStateCol' ,        @ProcessProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-@ObjectGroupsSelectedIdList' , @ObjectGroupsSelectedIdList
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '08-@ReturnObjectIdsOnly' ,        @ReturnObjectIdsOnly
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '09-@disType' ,                    @disType
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '10-@disIncludeFulltext' ,         @disIncludeFulltext
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-@disExcludeFulltext' ,         @disExcludeFulltext
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-@disIncludeLike' ,             @disIncludeLike
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '13-@groupByPCatLatestRese' ,      @groupByPCatLatestRes
  end

    -- ### Step: Prepare @visibleColumnNames as table with the column names
    -- #########
  IF @ObjInVisibleColList IS NULL
    SET @ObjInVisibleColList = N'ObjInGroupKeys, ObjInSelectedGroupKeys'

  DECLARE @visibleColumnNames TABLE (colName SYSNAME NOT NULL PRIMARY KEY)
  INSERT INTO @visibleColumnNames (colName)
  SELECT DISTINCT LTRIM(RTRIM(Value))
  FROM dbo.SplitStringToTable_UDF(@ObjInVisibleColList, N',')
  WHERE Value IS NOT NULL

    -- ### Step: Prepare @objectGroupsSelectedIdList by populating an indexed temp table of the values
    -- #########

  DECLARE @objGrpSelGuidList NVARCHAR(MAX)
  DECLARE @objectGroupsSelectedAny BIT = 0
  SET @objGrpSelGuidList = REPLACE(@ObjectGroupsSelectedIdList, N'''', N'')

  DECLARE @objectGroupSelectedIds GUID_TABLE

  INSERT INTO @objectGroupSelectedIds (Id)
  SELECT DISTINCT CAST(Value AS uniqueidentifier)
  FROM dbo.SplitStringToTable_UDF(@objGrpSelGuidList, N',')
  -- 22.02.2018/J.Fes A.Becker change because TRY_CONVERT not avilable in SQL 2008-R2
--   WHERE TRY_CONVERT(UNIQUEIDENTIFIER, Value) IS NOT NULL
  WHERE Value like '[a-fA-F0-9]%'

  IF EXISTS (SELECT 1 FROM @objectGroupSelectedIds)
    SET @objectGroupsSelectedAny = 1



    -- ### Step: Prepare the ObjectName search lists
    -- #########

	-- create the #temp table for the including full item names (no wildcards)
	IF OBJECT_Id('tempdb..#objectNameJoinListInclude') IS NOT NULL 
		DROP TABLE #objectNameJoinListInclude
	CREATE TABLE #objectNameJoinListInclude( irNameInclude NVARCHAR(512),
									 	     UNIQUE CLUSTERED (irNameInclude) ) 

	-- create the #temp table for the excluding full item names (no wildcards)
	IF OBJECT_Id('tempdb..#objectNameJoinListExclude') IS NOT NULL 
		DROP TABLE #objectNameJoinListExclude
	CREATE TABLE #objectNameJoinListExclude( irNameExclude NVARCHAR(512),
										     UNIQUE CLUSTERED (irNameExclude) )

	-- filter for wildcard item name searches (will be NULL if not used)
	DECLARE @objectNameLikeSqlInclude NVARCHAR(MAX)
	DECLARE @objectNameLikeSqlExclude NVARCHAR(MAX)

	-- processes a string containing item name filters
	-- fill two temp tables (#irNameQueryListInclude and #irNameQueryListExclude) with full item names
	-- returns two sql-where strings with ORed Like filters for include and exclude wildcard item names
	EXECUTE dbo.ObjectsAndProcessData_PrepareIrObjectNameList
			@WhereIrObjectNameList,
			N'N',  -- shortcut for ItemName, used to keep WHERE LIKE string short as possible
			@objectNameLikeSqlInclude OUTPUT,
			@objectNameLikeSqlExclude OUTPUT


    -- ### Step: DrawingIndexSearch (DIS) processing
    -- #########
 DECLARE @useDIS BIT
	DECLARE @processDISAfterObjectQuery BIT = 0
    SET @disType = ISNULL(@disType, N'*')
    SET @disIncludeFulltext = ISNULL(@disIncludeFulltext, N'')
    SET @disExcludeFulltext = ISNULL(@disExcludeFulltext, N'')
    SET @useDIS = CASE WHEN (dbo.StringHasText_UDF(@disIncludeFulltext) = 1 OR dbo.StringHasText_UDF(@disExcludeFulltext) = 1) THEN 1 ELSE 0 END
    SET @processDISAfterObjectQuery = CASE WHEN (@useDIS=1 AND (@disIncludeFulltext=N'*' OR dbo.StringHasText_UDF(@WhereIrObjectNameList)=1)) THEN 1 ELSE 0 END

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '14-@useDIS' ,        @useDIS
  end

 IF @useDIS = 1
    BEGIN
        -- create the #temp table for the DIS matches
        IF OBJECT_Id('tempdb..#disSearchMatches') IS NOT NULL
            DROP TABLE #disSearchMatches
        IF OBJECT_Id('tempdb..#disSearchObjects') IS NOT NULL
            DROP TABLE #disSearchObjects
        IF OBJECT_Id('tempdb..#disSearchObjectIdFilter') IS NOT NULL
            DROP TABLE #disSearchObjectIdFilter
        CREATE TABLE #disSearchMatches( disId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )
        CREATE TABLE #disSearchObjects( objectId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )
        CREATE TABLE #disSearchObjectIdFilter( objectId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )

		      IF @processDISAfterObjectQuery = 0
			   EXECUTE dbo.ObjectsAndProcessData_DisSearch @disType, @disIncludeFulltext, @disExcludeFulltext, @disIncludeLike
    END

    -- ### Step: Build the SQL parts
    -- #########

  -- SelectTop zusammenstellen
  if @SelectTop > 0
       set @SelectTopSQLstr = N'TOP ' + CONVERT(nvarchar(64), @SelectTop)
  else
       set @SelectTopSQLstr = N''

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '14-@SelectTopSQLstr' ,  @SelectTopSQLstr
  end

  -- WHERE zusammenstellen
  set @WhereObjectsSQLstr = ' where' + ' [ObjectClass]=' + '''' +  @WhereObjectClassSQL  + ''''

  if @WhereObjectDataSQL <> ''
     begin
       set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and (' + @WhereObjectDataSQL + ') '
     end

  if @WhereProcessDataSQL <> ''
     begin
       set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and (' + @WhereProcessDataSQL + ') '
     end

  -- add DrawingIndexSearch Filter
  DECLARE @useDIS_SQLPlaceholder NVARCHAR(MAX) = '''useDIS_SQLInject''=''useDIS_SQLInject'''
  DECLARE @useDIS_SQLInject NVARCHAR(MAX) = 'EXISTS (SELECT 1 FROM #disSearchObjects WHERE #disSearchObjects.objectId=Id)'
  IF @useDIS = 1
  BEGIN
    IF @processDISAfterObjectQuery = 0
      set @WhereObjectsSQLstr = @WhereObjectsSQLstr + N' and (' + @useDIS_SQLInject + N') '
    ELSE
      set @WhereObjectsSQLstr = @WhereObjectsSQLstr + N' and (' + @useDIS_SQLPlaceholder + ')'
  END

  -- add selected groups to WHERE
  IF @objectGroupsSelectedAny=1
    BEGIN
      set @WhereObjectsSQLstr = @WhereObjectsSQLstr + N' and (' + 
           N'EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl tREL ' + 
           N'        WHERE tREL.ObjectId=qDATA.Id AND ' +
           N'              EXISTS (SELECT 1 FROM @objectGroupSelectedIds SEL WHERE SEL.Id=tREL.ObjectGroupId)' +
           N'       )'
       + N') '
    END

  -- FROM zusammenstellen
  DECLARE @FromUDFName NVARCHAR(MAX)
  SET @FromUDFName = CASE @groupByPCatLatestRes
                          WHEN 1 THEN

                            CASE @WhereObjectClassSQL
                                WHEN N'DS' THEN N'dbo.DS_AndProcessData_GroupedByProcCategory_UDF'
                                WHEN N'IR' THEN N'dbo.IRs_AndProcessData_GroupedByProcCategory_UDF'
                                           ELSE N'dbo.ITEMs_AndProcessData_GroupedByProcCategory_UDF'
                           END

                          ELSE

                           CASE @WhereObjectClassSQL
                                WHEN N'DS' THEN N'dbo.DS_AndProcessData_UDF'
                                WHEN N'IR' THEN N'dbo.IRs_AndProcessData_UDF'
                                           ELSE N'dbo.ITEMs_AndProcessData_UDF'
                           END

                     END


  DECLARE @FromSQLstr NVARCHAR(MAX)
  SET @FromSQLstr = dbo.StringFormat_UDF(N' FROM {0}(''{1}'') qDATA ',
                                         @FromUDFName + ',' + @ProcessProcStateCol)

 -- EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '13.1-@FromSQLstr' ,      @FromSQLstr
    -- ### Step: Combine the SQL parts to the final execution SQL string
    -- #########


    DECLARE @hasObjectNameQuery           BIT
    DECLARE @hasObjectNameJoinList        BIT
    DECLARE @hasObjectNameJoinListInclude BIT
    DECLARE @hasObjectNameJoinListExclude BIT
    DECLARE @hasObjectNameLikeSql         BIT
    DECLARE @hasObjectNameLikeSqlInclude  BIT
    DECLARE @hasObjectNameLikeSqlExclude  BIT

    SET @hasObjectNameJoinListInclude = CASE WHEN EXISTS (SELECT 1 FROM #objectNameJoinListInclude) THEN 1 ELSE 0 END
    SET @hasObjectNameJoinListExclude = CASE WHEN EXISTS (SELECT 1 FROM #objectNameJoinListExclude) THEN 1 ELSE 0 END
    SET @hasObjectNameLikeSqlInclude  = dbo.StringHasText_UDF(@objectNameLikeSqlInclude)
    SET @hasObjectNameLikeSqlExclude  = dbo.StringHasText_UDF(@objectNameLikeSqlExclude)

    SET @hasObjectNameJoinList = @hasObjectNameJoinListInclude | @hasObjectNameJoinListExclude
    SET @hasObjectNameLikeSql  = @hasObjectNameLikeSqlInclude  | @hasObjectNameLikeSqlExclude
    SET @hasObjectNameQuery    = @hasObjectNameJoinList        | @hasObjectNameLikeSql

    if @DebugPrintOn = 1
    begin
     DECLARE @hasObjectNameDebug NVARCHAR(MAX)
 	   SET @hasObjectNameDebug = dbo.StringFormat_UDF(N'Query={0}, JoinList={1}, JoinListInclude={2}, JoinListExclude={3}, LikeSql={4}, LikeSqlInclude={5}, LikeSqlExclude={6}',
											  CONVERT(NVARCHAR(1), @hasObjectNameQuery)           + N',' +
											  CONVERT(NVARCHAR(1), @hasObjectNameJoinList)        + N',' +
											  CONVERT(NVARCHAR(1), @hasObjectNameJoinListInclude) + N',' +
											  CONVERT(NVARCHAR(1), @hasObjectNameJoinListExclude) + N',' +
											  CONVERT(NVARCHAR(1), @hasObjectNameLikeSql)         + N',' +
											  CONVERT(NVARCHAR(1), @hasObjectNameLikeSqlInclude)  + N',' +
											  CONVERT(NVARCHAR(1), @hasObjectNameLikeSqlExclude)
											  )
 	   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '15-@hasObjectNameXx' ,  @hasObjectNameDebug
 	   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '16-@objectNameLikeSqlInclude' ,  @objectNameLikeSqlInclude
 	   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '17-@objectNameLikeSqlExclude' ,  @objectNameLikeSqlExclude

	    DECLARE @objectNameJoinListCsv NVARCHAR(MAX)
	    DECLARE @objectNameJoinListCount INT

	    SET @objectNameJoinListCsv = NULL
	    SELECT @objectNameJoinListCount=COUNT(1) FROM #objectNameJoinListInclude
     SELECT TOP 100 @objectNameJoinListCsv = ISNULL(@objectNameJoinListCsv + N'; ', N'') + irNameInclude FROM #objectNameJoinListInclude
	    SET @objectNameJoinListCsv = N'TOP 100 of ' + CONVERT(NVARCHAR(10), @objectNameJoinListCount) + N': ' + @objectNameJoinListCsv
     EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '18-#objectNameJoinListInclude' ,  @objectNameJoinListCsv

	    SET @objectNameJoinListCsv = NULL
	    SELECT @objectNameJoinListCount=COUNT(1) FROM #objectNameJoinListExclude
	    SELECT TOP 100 @objectNameJoinListCsv = ISNULL(@objectNameJoinListCsv + N'; ', N'') + irNameExclude FROM #objectNameJoinListExclude
	    SET @objectNameJoinListCsv = N'TOP 100 of ' + CONVERT(NVARCHAR(10), @objectNameJoinListCount) + N': ' + @objectNameJoinListCsv
	    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '19-#objectNameJoinListExclude' ,  @objectNameJoinListCsv
    end

    DECLARE @UnionSql1 NVARCHAR(MAX)
    DECLARE @UnionSql2 NVARCHAR(MAX)

    -- check if any ObjectName filter is needed
    IF  @hasObjectNameQuery=0
    BEGIN

     -- no ObjectName filter is needed
     SET @UnionSql1=N'SELECT #InnerTopN# qDATA.* ' +
                    @FromSQLstr + N' ' +
                    @WhereObjectsSQLstr

   		if @DebugPrintOn = 1
        EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20-BuildExecuteSQL(@UnionSql1)' , @UnionSql1
     END

    -- check if any ObjectName join list is needed
    IF @hasObjectNameJoinListInclude=1
    BEGIN
      -- build SQL for ObjectName query with join list
      SET @UnionSql1 = N'SELECT #InnerTopN# qDATA.* '
                     + @FromSQLstr + N' '
                     + CASE WHEN @hasObjectNameJoinListInclude=1 THEN N' INNER JOIN #objectNameJoinListInclude tNameInc ON (irNameInclude = qDATA.N COLLATE Latin1_General_CI_AS) ' ELSE N'' END
                     + @WhereObjectsSQLstr
	    if @DebugPrintOn = 1
     begin
           EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '21-BuildExecuteSQL(JoinList)' , @UnionSql1
     end
    END

    -- check if any ObjectName LIKE list is needed
    IF @hasObjectNameLikeSqlInclude=1
    BEGIN
        -- build SQL for ObjectName query with join list
        SET @UnionSql2 = N'SELECT #InnerTopN# qDATA.* '
                       + @FromSQLstr + N' '
                       + @WhereObjectsSQLstr
                       + CASE WHEN @hasObjectNameLikeSqlInclude=1 THEN N' AND (' + @objectNameLikeSqlInclude + ') ' ELSE N'' END
        -- remember this query for the case there is no UNION needed later
	    if @DebugPrintOn = 1
     begin
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '22-BuildExecuteSQL(LikeSQL)' , @UnionSql2
     end
    END

    -- build resulting SQL
    DECLARE @needUnion BIT
    SET @needUnion = dbo.StringHasText_UDF(@UnionSql1) & dbo.StringHasText_UDF(@UnionSql2)

    IF @needUnion=1
    BEGIN

        -- UNION with subquery needed
        SET @ExecuteSQLstr = N'       ' + @UnionSql1
                           + CHAR(13) + CHAR(10) + N' UNION ALL ' + CHAR(13) + CHAR(10)
                           + N'       ' + @UnionSql2

        IF @DebugPrintOn = 1
        begin
	        EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '23-BuildExecuteSQL(UNION)' , @ExecuteSQLstr
        end

    END ELSE BEGIN
       -- single query without UNION (one of the unionSql strings is empty)
       SET @ExecuteSQLstr = ISNULL(@UnionSql1,N'') + ISNULL(@UnionSql2,N'')
        IF @DebugPrintOn = 1
        begin
	        EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '24-BuildExecuteSQL(SINGLE)' , @ExecuteSQLstr
        end
    END

    -- add objectName excludes to query

    -- when any exclude is needed, embed the query in another SELECT to enable excluding on the complete result set
    -- IF (@hasObjectNameJoinListExclude|@hasObjectNameLikeSqlExclude)=1
        SET @ExecuteSQLstr = N'SELECT #InnerTopN# qDATA.* ' + CHAR(13) + CHAR(10) +
                             N' FROM ( ' + CHAR(13) + CHAR(10)
                           + N'       ' + @ExecuteSQLstr
                           + CHAR(13) + CHAR(10) + N' ) AS qDATA ' + CHAR(13) + CHAR(10)

    -- add join to #objectNameJoinListExclude when needed
    IF @hasObjectNameJoinListExclude=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' LEFT  JOIN #objectNameJoinListExclude tNameExc ON (irNameExclude = qDATA.N COLLATE Latin1_General_CI_AS) '

    -- add where if there is any exclude
    IF (@hasObjectNameJoinListExclude|@hasObjectNameLikeSqlExclude)=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' WHERE '

    -- add join-list exclude
    IF @hasObjectNameJoinListExclude=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' tNameExc.irNameExclude IS NULL '

    -- add and if there are both excludes (join and like-string)
    IF (@hasObjectNameJoinListExclude&@hasObjectNameLikeSqlExclude)=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' AND '

    -- add like string excludes
    IF @hasObjectNameLikeSqlExclude=1
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' NOT (' + @objectNameLikeSqlExclude + ') '

    -- 06.11.2015 A.Becker: Improved TOP-N Handling when both include and exclude is requested.
    -- replace the #InnerTopN# placeholders
    IF (@hasObjectNameJoinListExclude|@hasObjectNameLikeSqlExclude)=1
       -- remove all #InnerTopN# when any excludes are needed
       SET @ExecuteSQLstr=REPLACE(@ExecuteSQLstr, N'#InnerTopN#', N'')
	ELSE
       -- activete all #InnerTopN# when only includes are requested
       SET @ExecuteSQLstr=REPLACE(@ExecuteSQLstr, N'#InnerTopN#', @SelectTopSQLstr)

	-- when the Id column is requested only, encapsulate the query once more to only return this single column
	IF @ReturnObjectIdsOnly = 1
    SET @ExecuteSQLstr=N'SELECT tDATA.Id FROM (' + CHAR(13) + CHAR(10) + @ExecuteSQLstr + CHAR(13) + CHAR(10) + + ') tDATA'
	ELSE BEGIN
    DECLARE @innerSQL NVARCHAR(MAX) = @ExecuteSQLstr
    SET @ExecuteSQLstr=N'SELECT ' + @SelectTopSQLstr + N' '

    -- Compute ObjInGroupKeys if column is visible, else return empty strings
    IF EXISTS (SELECT 1 FROM @visibleColumnNames WHERE colName = N'ObjInGroupKeys')
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' dbo.GetGroupKeysForObject_UDF(qDATA.Id, @objectGroupSelectedIds, ''returnAll'') as ObjInGroupKeys, '
    ELSE
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' N'''' as ObjInGroupKeys, '

    -- Compute ObjInSelectedGroupKeys if column is visible, else return empty strings
    IF EXISTS (SELECT 1 FROM @visibleColumnNames WHERE colName = N'ObjInSelectedGroupKeys')
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' dbo.GetGroupKeysForObject_UDF(qDATA.Id, @objectGroupSelectedIds, ''returnSelected'') as ObjInSelectedGroupKeys, '
    ELSE
       SET @ExecuteSQLstr=@ExecuteSQLstr + N' N'''' as ObjInSelectedGroupKeys, '

    -- Add all not-computed columns
    SET @ExecuteSQLstr=@ExecuteSQLstr + N'
                         qDATA.*
                         FROM (' + CHAR(13) + CHAR(10) + @innerSQL + CHAR(13) + CHAR(10) + + ') qDATA'
 END

  if @DebugPrintOn = 1
   begin
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '25-@ExecuteSQLstr' , @ExecuteSQLstr
   end

   IF @useDIS = 0
   BEGIN
	   -- Output object list without DIS filtering
	   execute sp_executesql @ExecuteSQLstr, N'@objectGroupSelectedIds GUID_TABLE READONLY', @objectGroupSelectedIds
   END
   ELSE BEGIN
 	  -- Collect IDs of all requested Objects (without DIS)
 	  DECLARE @ExecuteSQLstrIdOnly NVARCHAR(MAX) = N'SELECT DISTINCT q.Id FROM (' + REPLACE(@ExecuteSQLstr, N'qDATA.*', N'qDATA.Id') + N' ) q'
 	  IF @disType = '*'
 	  BEGIN
 	     SET @ExecuteSQLstrIdOnly = REPLACE(@ExecuteSQLstrIdOnly, @useDIS_SQLPlaceholder, N'EXISTS (SELECT 1 FROM dbo.DrawingIndexingSearchFullText_tbl DISFT WHERE DISFT.ObjIdRef = qDATA.Id)')
 	  END
 	  ELSE
 	  BEGIN
 	     SET @ExecuteSQLstrIdOnly = REPLACE(@ExecuteSQLstrIdOnly, @useDIS_SQLPlaceholder, N'EXISTS (SELECT 1 FROM dbo.DrawingIndexingSearch_tbl DIST WHERE DIST.ObjIdRef = qDATA.Id AND DIST.TYPE = ''' + @disType + ''')')
 	  END
     INSERT INTO #disSearchObjectIdFilter(objectId)
 	  execute sp_executesql @ExecuteSQLstrIdOnly, N'@objectGroupSelectedIds GUID_TABLE READONLY', @objectGroupSelectedIds

 	  -- process DIS query here if kannst du dir ausdenken...
 	  IF @processDISAfterObjectQuery = 1
 	  BEGIN
 		  EXECUTE dbo.ObjectsAndProcessData_DisSearch @disType, @disIncludeFulltext, @disExcludeFulltext, @disIncludeLike
 		  SET @ExecuteSQLstr = REPLACE(@ExecuteSQLstr, @useDIS_SQLPlaceholder, @useDIS_SQLInject)
 	  END

 	  -- Output Objects
 	  execute sp_executesql @ExecuteSQLstr, N'@objectGroupSelectedIds GUID_TABLE READONLY', @objectGroupSelectedIds

       -- Output DIS Details
 	  SELECT *
       FROM dbo.DrawingIndexingSearch_tbl DIS
       WHERE EXISTS (SELECT 1 FROM #disSearchMatches WHERE #disSearchMatches.disId=DIS.Id)
 	    AND EXISTS (SELECT 1 FROM #disSearchObjectIdFilter WHERE objectId=DIS.ObjIdRef)

   END

END
GO


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


Print '-- [ObjectGroups_UpdateRefCounts] ---------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_UpdateRefCounts" and type = "P")
DROP PROCEDURE ObjectGroups_UpdateRefCounts
GO

CREATE PROCEDURE [dbo].[ObjectGroups_UpdateRefCounts]
	@groupIdList NVARCHAR(MAX)
AS
BEGIN

    -- Calculates and updates the group reference counts
    -- Will be called from JobServer asynchronously for groups with added or removed objects

	SET NOCOUNT ON;

	-- #Step: split input GUId list to table
	DECLARE @groupIdTable TABLE (Id UNIQUEIdENTIFIER)
	IF dbo.StringHasText_UDF(@groupIdList) = 1
	  -- use group Ids from parameter
	  INSERT INTO @groupIdTable
	  SELECT Value FROM dbo.SplitStringToTable_UDF(@groupIdList, N',')
	  WHERE dbo.StringHasText_UDF(Value)=1
	  OPTION (MAXRECURSION 0)
	ELSE
	  -- use all group Ids when parameter is null
	  INSERT INTO @groupIdTable
	  SELECT Id
	  FROM dbo.ObjectGroups_tbl

    -- #Step: insert calculated statistics into temp table
	DECLARE @groupRefCounts TABLE (Id UNIQUEIdENTIFIER, ObjectReferenceCount_ITEM INT, ObjectReferenceCount_IR INT, ObjectReferenceCount_DS INT) 

	INSERT INTO @groupRefCounts(Id, ObjectReferenceCount_ITEM, ObjectReferenceCount_IR, ObjectReferenceCount_DS)
    SELECT tOG.Id,
           (SELECT COUNT(1) FROM dbo.Objects_ObjectGroups_Rel_tbl tREL WITH (NOLOCK)
			                JOIN dbo.Objects_tbl tOBJ WITH (NOLOCK) ON (tOBJ.Id = tREL.ObjectId) 
			                WHERE tREL.ObjectGroupId = tOG.Id AND tOBJ.ObjectClass = N'I') AS ObjectReferenceCount_ITEM,
           (SELECT COUNT(1) FROM dbo.Objects_ObjectGroups_Rel_tbl tREL WITH (NOLOCK)
			                JOIN dbo.Objects_tbl tOBJ WITH (NOLOCK) ON (tOBJ.Id = tREL.ObjectId)
			                WHERE tREL.ObjectGroupId = tOG.Id AND tOBJ.ObjectClass = N'IR') AS ObjectReferenceCount_IR,
           (SELECT COUNT(1) FROM dbo.Objects_ObjectGroups_Rel_tbl tREL WITH (NOLOCK)
			                JOIN dbo.Objects_tbl tOBJ WITH (NOLOCK) ON (tOBJ.Id = tREL.ObjectId)
							WHERE tREL.ObjectGroupId = tOG.Id AND tOBJ.ObjectClass = N'DS') AS ObjectReferenceCount_DS
    FROM dbo.ObjectGroups_tbl tOG 
	WITH (NOLOCK)
	WHERE tOG.Id IN (SELECT Id FROM @groupIdTable)
	
	-- #Step: update groups statistics
	UPDATE 
	       GROUPS
	SET
	       GROUPS.ObjectReferenceCount_ITEM = COUNTS.ObjectReferenceCount_ITEM,
	       GROUPS.ObjectReferenceCount_IR   = COUNTS.ObjectReferenceCount_IR,
		   GROUPS.ObjectReferenceCount_DS   = COUNTS.ObjectReferenceCount_DS
    FROM 
	       ObjectGroups_tbl GROUPS
		   JOIN @groupRefCounts COUNTS ON (COUNTS.Id = GROUPS.Id)

END
GO

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




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

CREATE PROCEDURE [dbo].[ObjectGroups_AddOrRemoveObjects]
                @objectGroupsIdList nvarchar(max),
                @addOrRemove        int,  -- = 1 = add, 2 = remove , 3 = Mirrow
                @DebugPrintOn       bit
AS
BEGIN

   -- 2nd level worker procedure
   -- called by [ObjectGroups_AddObjectsByFilter] and [ObjectGroups_AddOrRemoveObjectsByIdList]
   -- set @DebugPrintOn = 1

   SET NOCOUNT ON;

-- DEV --
   -- CREATE TABLE #objectIds (Id UNIQUEIdENTIFIER)
   -- CREATE TABLE #results (groupId UNIQUEIdENTIFIER, relCount_ITEM_Delta INT, relCount_IR_Delta INT, relCount_DS_Delta INT, relCount_Delta INT)
-- DEV --


   DECLARE @MyProcName nvarchar(32)
   set @MyProcName='ObjectGroups_AddOrRemoveObjects'

   -- split the objectGroup table Ids
   DECLARE @objectGroupIds TABLE (objectGroupId UNIQUEIdENTIFIER, rowNumber INT)
   INSERT INTO @objectGroupIds(objectGroupId, rowNumber)
       SELECT CAST(Value AS UNIQUEIdENTIFIER), rowNumber
       FROM dbo.SplitStringToTable_UDF(@objectGroupsIdList, N',')
       WHERE EXISTS (SELECT 1
                     FROM dbo.ObjectGroups_tbl
                     WHERE Id = CAST(Value AS UNIQUEIdENTIFIER))
       OPTION (MAXRECURSION 0)


   IF @DebugPrintOn = 1
   BEGIN

       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'11-@addOrRemove_p' ,        @addOrRemove
       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'12-@objectGroupsIdList' ,   @objectGroupsIdList

       DECLARE @count INT

       SELECT @count = COUNT(1) FROM @objectGroupIds
       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'13-objectGroupIds.Count' ,  @count

       SELECT @count = COUNT(1) FROM #objectIds
       EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'14-#objectIds.Count' ,      @count
   END

   -- Loop helper variables
   DECLARE @objectGroupId UNIQUEIdENTIFIER
   DECLARE @nextrowNumber INT
   SELECT @nextrowNumber=MIN(rowNumber) FROM @objectGroupIds

   DECLARE @deltaCount INT

   -- Iterate over all objectGroups
   WHILE 1=1
   BEGIN

         -- Get the next groupId
         SELECT TOP 1 @objectGroupId = objectGroupId,
                      @nextrowNumber = rowNumber+1
         FROM @objectGroupIds
         WHERE rowNumber >= @nextrowNumber

         -- Break if no more group is available
         IF @@ROWCOUNT = 0
           BREAK

         IF @DebugPrintOn = 1
         BEGIN
          EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'21-@objectGroupId_c' ,   @objectGroupId
         END

         -- Update the current ref count to allow precise delta count after insert or delete
         DECLARE @relCount_ITEM INT
         DECLARE @relCount_IR INT
         DECLARE @relCount_DS INT
         DECLARE @objectGroupIdString NVARCHAR(50)
           SET @objectGroupIdString = CONVERT(NVARCHAR(50), @objectGroupId)
           EXEC dbo.ObjectGroups_UpdateRefCounts @objectGroupIdString
         SELECT @relCount_ITEM = ObjectReferenceCount_ITEM,
                @relCount_IR   = ObjectReferenceCount_IR,
                @relCount_DS   = ObjectReferenceCount_DS
           FROM dbo.ObjectGroups_tbl
           WHERE Id = @objectGroupId

         -- ####################################################
         -- #### HERE IS THE ACTUAL WORKLOAD OF INSERT OR DELETE
         -- ####################################################

         SET @deltaCount = 0

         -- ADD to Group
         IF @addOrRemove = 1
         BEGIN
              IF @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'22-@addOrRemove' ,    'CASE 1:ADD'

             -- Insert all missing objects to the group
             INSERT INTO dbo.Objects_ObjectGroups_Rel_tbl(ObjectGroupId,ObjectId)
                 SELECT @objectGroupId, tOId.Id
                 FROM (SELECT DISTINCT Id FROM #objectIds) tOId
                 WHERE NOT EXISTS (SELECT 1
                                   FROM dbo.Objects_ObjectGroups_Rel_tbl
                                   WHERE ObjectGroupId = @objectGroupId AND ObjectId = tOId.Id)
             SET @deltaCount = @deltaCount + @@ROWCOUNT
         END

         -- REMOVE from Group
         IF @addOrRemove = 2
         BEGIN

             IF @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'22-@addOrRemove' ,    'CASE 2:REMOVE'

             -- Delete the selected objects from the group
             DELETE FROM dbo.Objects_ObjectGroups_Rel_tbl
             WHERE ObjectGroupId = @objectGroupId AND
                   ObjectId IN (SELECT Id FROM #objectIds)

             SET @deltaCount = @deltaCount-@@ROWCOUNT

         END

         -- MIRROR
         IF @addOrRemove = 3
         BEGIN

             IF @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName ,'22-@addOrRemove' ,    'CASE 3:MIRROR'


             -- needed to calculate @deltaCount
             DECLARE @oldCount INT
             SELECT @oldCount = COUNT(1) FROM
              dbo.Objects_ObjectGroups_Rel_tbl WHERE ObjectGroupId = @objectGroupId

             -- insert objects in source but not in group
             -- remove objects from group not in source
             MERGE dbo.Objects_ObjectGroups_Rel_tbl AS DST
             USING #objectIds AS SRC ON DST.ObjectGroupId = @objectGroupId AND DST.ObjectId = SRC.Id
             WHEN NOT MATCHED BY TARGET
                THEN INSERT (ObjectGroupId, ObjectId) VALUES (@objectGroupId, SRC.Id)
             WHEN NOT MATCHED BY SOURCE AND DST.ObjectGroupId = @objectGroupId
                THEN DELETE ;

             -- needed to calculate @deltaCount
             DECLARE @newCount INT
             SELECT @newCount = COUNT(1) FROM dbo.Objects_ObjectGroups_Rel_tbl WHERE ObjectGroupId = @objectGroupId

             -- calculate @deltaCount
             SET @deltaCount = @newCount - @oldCount

         END



      -- Update the ref count after update to calculate delta
         DECLARE @relCount_ITEM_Delta INT
         DECLARE @relCount_IR_Delta INT
         DECLARE @relCount_DS_Delta INT
         EXEC dbo.ObjectGroups_UpdateRefCounts @objectGroupIdString
         SELECT @relCount_ITEM_Delta = ObjectReferenceCount_ITEM - @relCount_ITEM,
                @relCount_IR_Delta   = ObjectReferenceCount_IR - @relCount_IR,
                @relCount_DS_Delta   = ObjectReferenceCount_DS - @relCount_DS
           FROM dbo.ObjectGroups_tbl
           WHERE Id = @objectGroupId


         IF @DebugPrintOn = 1
         BEGIN
             DECLARE @message NVARCHAR(MAX)
             EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '31-@objectGroupId' ,   @objectGroupId
             SET @message = CONVERT(NVARCHAR(50), @objectGroupId) + N': '
                          + CONVERT(NVARCHAR(10), @relCount_ITEM_Delta) + N' ITEMs, '
                          + CONVERT(NVARCHAR(10), @relCount_IR_Delta) + N' IRs, '
                          + CONVERT(NVARCHAR(10), @relCount_DS_Delta) + N' DSs, '
                          + CONVERT(NVARCHAR(10), @deltaCount) + N' total.'
             EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '32-GroupRefDelta.Count' ,   @message
         END

         -- Remember result
         INSERT INTO #results (groupId, relCount_ITEM_Delta, relCount_IR_Delta, relCount_DS_Delta, relCount_Delta)
         VALUES (@objectGroupId, @relCount_ITEM_Delta, @relCount_IR_Delta, @relCount_DS_Delta, @deltaCount)

     END

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




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

If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_AddOrRemoveObjectsByFilter" and type = "P")
Drop Procedure ObjectGroups_AddOrRemoveObjectsByFilter
GO


CREATE PROCEDURE [dbo].[ObjectGroups_AddOrRemoveObjectsByFilter]
               @objectGroupsIdList    nvarchar(max),
			            @WhereObjectClassSQL   nvarchar(10) = null ,
               @WhereIrObjectNameList nvarchar(max) = null ,
               @WhereObjectDataSQL    nvarchar(max) = null ,
               @WhereProcessDataSQL   nvarchar(max) = null ,
               @SelectTop             integer ,
               @ProcessProcStateCol   nvarchar(32),
               @addOrRemove           int  -- = 1 = add, 2 = remove , 3 = Mirrow
AS
BEGIN

	SET NOCOUNT ON;

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

	IF @DebugPrintOn = 1
	BEGIN
		-- Clear Debug Table
		EXECUTE zzzzzDebugTbl_Empty
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@objectGroupsIdList' ,        @objectGroupsIdList
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@WhereObjectClassSQL' ,       @WhereObjectClassSQL 
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@WhereIrObjectNameList' ,     @WhereIrObjectNameList
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@WhereObjectDataSQL' ,        @WhereObjectDataSQL
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@WhereProcessDataSQL' ,       @WhereProcessDataSQL
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@SelectTop' ,                 @SelectTop
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '07-@ProcessProcStateCol' ,       @ProcessProcStateCol
	END
	
	-- Get the object Ids from filter conditions into temp table
 CREATE TABLE #objectIds (Id UNIQUEIdENTIFIER)

 INSERT #objectIds(Id)
		EXEC dbo.ObjectsAndProcessData @WhereObjectClassSQL,
		                               @WhereIrObjectNameList,
                     									   @WhereObjectDataSQL,
                     									   @WhereProcessDataSQL,
                     									   @SelectTop,
                     									   @ProcessProcStateCol,
                     									   @ObjectGroupsSelectedIdList = null,
                     									   @ReturnObjectIdsOnly = 1

    -- create the result table
	CREATE TABLE #results (groupId UNIQUEIdENTIFIER, relCount_ITEM_Delta INT, relCount_IR_Delta INT, relCount_DS_Delta INT, relCount_Delta INT)

  -- ##################################
  -- ## execute the worker procedure ##
  -- ##################################
  EXEC ObjectGroups_AddOrRemoveObjects @objectGroupsIdList, @addOrRemove, @DebugPrintOn

	-- return results
	SELECT * FROM #results

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




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


CREATE PROCEDURE [dbo].[ObjectGroups_AddOrRemoveObjectsByIdList]
               @objectGroupsIdList nvarchar(max),
               @objectsIdList      nvarchar(max),
               @addOrRemove        int  -- = 1 = add, 2 = remove , 3 = Mirrow
AS
BEGIN

	SET NOCOUNT ON;

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

	IF @DebugPrintOn = 1
	BEGIN
		-- Clear Debug Table
		EXECUTE zzzzzDebugTbl_Empty
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@objectGroupsIdList' ,  @objectGroupsIdList
		DECLARE @objectsIdListTrunc NVARCHAR(1024)
		SET @objectsIdListTrunc = SUBSTRING(@objectsIdList, 1, 256) + ' ,...,... #Debug:Values truncated'
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@objectsIdList' ,       @objectsIdListTrunc
		EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@addOrRemove' ,         @addOrRemove
	END
	
	-- split the objectsId table Ids
    CREATE TABLE #objectIds (Id UNIQUEIdENTIFIER)
	INSERT INTO #objectIds(Id)
		SELECT DISTINCT CAST(Value AS UNIQUEIdENTIFIER)
		FROM dbo.SplitStringToTable_UDF(@objectsIdList, N',')
		WHERE EXISTS (SELECT 1
		              FROM dbo.Objects_tbl
					  WHERE Id = CAST(Value AS UNIQUEIdENTIFIER))
		OPTION (MAXRECURSION 0)

    -- create the result table
	CREATE TABLE #results (groupId UNIQUEIdENTIFIER, relCount_ITEM_Delta INT, relCount_IR_Delta INT, relCount_DS_Delta INT, relCount_Delta INT)

  -- ##################################
  -- ## execute the worker procedure ##
  -- ##################################
 EXEC ObjectGroups_AddOrRemoveObjects @objectGroupsIdList, @addOrRemove, @DebugPrintOn

	-- return results
	SELECT * FROM #results

END
GO



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

Print '-- [ObjectGroups_ClearRelations] ----------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectGroups_ClearRelations" and type = "P")
DROP PROCEDURE ObjectGroups_ClearRelations
GO

CREATE PROCEDURE [dbo].[ObjectGroups_ClearRelations]
	@objectGroupId UNIQUEIdENTIFIER, 
	@objectClass NVARCHAR(10),
	@removedRelationsCount INT OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

	-- remove the relations between groups and objects
	DELETE tREL
	FROM dbo.Objects_ObjectGroups_Rel_tbl tREL
		 INNER JOIN dbo.Objects_tbl tOBJ ON tOBJ.Id = tREL.ObjectId
	WHERE tREL.ObjectGroupId = @objectGroupId 
	  AND (@objectClass IS NULL OR tOBJ.ObjectClass = @objectClass)

	-- remember the count of removed relations
	SET @removedRelationsCount = @@ROWCOUNT

	-- when all objectClassed are cleared, set all reference counts to 0
	IF @objectClass IS NULL
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_ITEM = 0,
		    ObjectReferenceCount_IR = 0,
			ObjectReferenceCount_DS = 0
		WHERE Id = @objectGroupId

	-- set item reference counts to 0
	IF @objectClass = N'I'
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_ITEM = 0
		WHERE Id = @objectGroupId

	-- set itemrevision reference counts to 0
	IF @objectClass = N'IR'
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_IR = 0
		WHERE Id = @objectGroupId

	-- set dataset reference counts to 0
	IF @objectClass = N'DS'
		UPDATE dbo.ObjectGroups_tbl
		SET ObjectReferenceCount_DS = 0
		WHERE Id = @objectGroupId

END
GO

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

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

CREATE PROCEDURE DataSetSelectByWhereClause @WhereClauseSql nvarchar(max)
AS
BEGIN

/*
  -- Beispiel ObjectsDataSetSelectByWhereClause
  -- EXECUTE DataSetSelectByWhereClause 'where IrName LIKE ''07811002/%'' AND dbo.DS_with_Irs_View.ObjectType IN (''UGMASTER'',''UGPART'')'
*/

  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName = 'DataSetSelectByWhereClause'

  -- Debug
  -- EXECUTE zzzzzDebugTbl_Empty

  set @ExecuteSQLstr = 'SELECT '
                      + ' dbo.DS_with_Irs_View.IrName,'
                      + ' dbo.DS_with_Irs_View.Id,'
                      + ' dbo.DS_with_Irs_View.ObjectCliName,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol01,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol02,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol03,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol04,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol05,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol06,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol07,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol08,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol09,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol10,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol11'
                      + ' FROM'
                      + ' dbo.DS_with_Irs_View'

  if @WhereClauseSql <> ''
             begin
                    set @ExecuteSQLstr = @ExecuteSQLstr + ' ' +  @WhereClauseSql
             end

  -- EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , N'1-@ExecuteSQLstr' , @ExecuteSQLstr
  execute sp_executesql @ExecuteSQLstr

END
GO

Print '-- [Objects_tbl_Item_GetIRS #New: 18.02.2015 J.Fes] ---------------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_Item_GetIRS" and type = "P")
DROP PROCEDURE Objects_tbl_Item_GetIRS
GO

-- #Doc: Returns for  @ItemId and  @ObjectFromSiteId all IRS
CREATE PROCEDURE Objects_tbl_Item_GetIRS(@ItemId nvarchar(128), @ObjectFromSiteId nvarchar(32))
As
BEGIN

SELECT
  IR.*
FROM
  dbo.Objects_tbl I WITH (nolock)
  RIGHT OUTER JOIN dbo.Objects_Ir_Rel_tbl ON (I.Id = dbo.Objects_Ir_Rel_tbl.IrId)
  INNER JOIN dbo.Objects_tbl IR ON (dbo.Objects_Ir_Rel_tbl.ObjectId = IR.Id)
WHERE
  I.ObjectClass = 'I'
  and I.ObjectName = @ItemId
  and I.ObjectFromSiteId = @ObjectFromSiteId
end
GO



Print '-- [Objects_tbl_DS_GetIRS #New: 29.06.2021 J.Fes] -----------------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_DS_GetIRS" and type = "P")
DROP PROCEDURE Objects_tbl_DS_GetIRS
GO

-- #Doc: Returns for  @DSPuid and @ObjectFromSiteId all IRS
CREATE PROCEDURE Objects_tbl_DS_GetIRS(@DSPuid nvarchar(64), @ObjectFromSiteId nvarchar(32))
As
BEGIN

 SELECT
   IR.*
 FROM
   dbo.Objects_tbl DS WITH (nolock)
   RIGHT OUTER JOIN dbo.Objects_Ir_Rel_tbl ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
   INNER JOIN dbo.Objects_tbl IR ON (dbo.Objects_Ir_Rel_tbl.IrId = IR.Id)
 WHERE
   DS.ObjectClass = 'DS'
   and DS.ObjectPuid = @DSPuid
   and DS.ObjectFromSiteId = @ObjectFromSiteId

end
GO

Print '-- [Objects_tbl_GetAllNeedUpdate #New: 30.11.2016 J.Fes] ----------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_GetAllNeedUpdate" and type = "P")
DROP PROCEDURE Objects_tbl_GetAllNeedUpdate
GO

-- #Doc: Returns for  @ObjectFromSiteId all Objects need Update
-- #Lup: 05.12.2016 Returns also for DS Types IrPuid IrItemRev
CREATE PROCEDURE Objects_tbl_GetAllNeedUpdate(@ObjectFromSiteId nvarchar(32))
As
BEGIN
SELECT
  I_IR_DS.*,
  Ir.ObjectPuid AS IrPuid,
  Ir.ObjectName AS IrItemRev
FROM
  dbo.Objects_tbl I_IR_DS WITH (nolock)
  LEFT OUTER JOIN dbo.Objects_Ir_Rel_tbl ON (I_IR_DS.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
  LEFT OUTER JOIN dbo.Objects_tbl Ir ON (dbo.Objects_Ir_Rel_tbl.IrId = Ir.Id)
  AND (Ir.ObjectClass = 'IR')
WHERE
  I_IR_DS.ObjectFromSiteId = @ObjectFromSiteId AND
  I_IR_DS.ObjectSyncTrigger > 0
ORDER BY
  ObjectUgMaster_APLevels ASC,
  ObjectUgMaster_CompCount ASC

end
GO

Print '-- [Objects_tbl_TcMultiSiteCheck_GetToProcess] --------------------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_TcMultiSiteCheck_GetToProcess" and type = "P")
DROP PROCEDURE Objects_tbl_TcMultiSiteCheck_GetToProcess
GO

-- #Doc: Returns for  @ObjectFromSiteId all Objects need Update
CREATE PROCEDURE Objects_tbl_TcMultiSiteCheck_GetToProcess(@SiteGroupMembers nvarchar(32))
As
BEGIN

 DECLARE @ExecuteSQLstr nvarchar(max)
 --  01.12.2016 J.Fes/S.Gueth
 DECLARE @DebugPrintOn bit
 set @DebugPrintOn = 0
 DECLARE @MyProcName nvarchar(32)
 set @MyProcName='Objects_tbl_TcMultiSiteCheck_GetToProcess'

 -- if @ObjectClass = 'I' set @DebugPrintOn = 1


 if @DebugPrintOn = 1
 begin
  -- Clear Debug Table
  EXECUTE zzzzzDebugTbl_Empty
 end


set @ExecuteSQLstr = N'
 SELECT DISTINCT
    ObjectName
 FROM
    dbo.Objects_tbl with (nolock)
 WHERE
        (ProcStateColMc = ''N'' or ProcStateColMc = ''M'' or ProcStateColMc = ''U'')
    AND dbo.Objects_tbl.ObjectClass = ''I''
    AND dbo.Objects_tbl.ObjectFromSiteID IN (' + @SiteGroupMembers + ')'

 if @DebugPrintOn = 1
 begin
  -- Clear Debug Table
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ExecuteSQLstr' ,    @ExecuteSQLstr
 end


 -- Ausgabe der Tablle
 execute sp_executesql @ExecuteSQLstr

end
GO

-- EXECUTE DataSetSelectByWhereClause 'where IrName LIKE ''07811002/%'' AND dbo.DS_with_Irs_View.ObjectType IN (''UGMASTER'',''UGPART'')'

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

