Print ''
Print '---------------------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [REPORTS_DataAnalyse]'
Print ' Creation      : 29.08.2013/J.Fes'
Print ' Last.Update   : 29.08.2013/J.Fes'
Print ' Last.Update   : 31.03.2022/A.Becker+J.Fes opti Rrt_410_List_IRsDatasets_With_MoreThenOneMaster'
Print ' Last.Update   : 31.03.2022/A.Becker+J.Fes add Report_412_xIDEAS_IRsDatasets_With_MoreThenOneIdeasDataset'

use [#DB_N#]

Print ''
Print ' '
Print '---------------------------------------------------------------------------------------'
Print '--------------------- R E P O R T S - DATA Analyse ------------------------------------'
Print '---------------------------------------------------------------------------------------'
Print ''



--------------------------------------------------------------------------------
Print '-- [Rrt_410_List_IRsDatasets_With_MoreThenOneMaster] ----------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Rrt_410_List_IRsDatasets_With_MoreThenOneMaster")
Drop Procedure Rrt_410_List_IRsDatasets_With_MoreThenOneMaster
GO
CREATE PROCEDURE Rrt_410_List_IRsDatasets_With_MoreThenOneMaster
       (
        @ObjectFromSiteID nvarchar(32),
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
        @ObjectType1 nvarchar(max)
       )

-- Hinweis:
-- 25.04.2012 J.Fes dieser Report darf nur in Abstimmung mit den zugehörigen L&L Formularen angepasst werden.
--            |-> siehe ..\01-BinServer\Resources\ReportLayouts\Report_902_ProcessStatistic_And_ProcessResults

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Rrt_410_List_IRsDatasets_With_MoreThenOneMaster'
  DECLARE @ExecuteSQLstr nvarchar(max)

--   DECLARE @AndProcessData_UDF nvarchar(32)
--   set @AndProcessData_UDF='DS_AndProcessData_UDF'
--   if @ObjectClass = 'DS' set @AndProcessData_UDF='DS_AndProcessData_UDF'
--   if @ObjectClass = 'IR' set @AndProcessData_UDF='IRs_AndProcessData_UDF'
--   if @ObjectClass = 'I'  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

DECLARE @dupIrToItem TABLE (IR_DbId UNIQUEIDENTIFIER, RelCount INT)


DECLARE @objectTypes TABLE (ObjectType NVARCHAR(32) PRIMARY KEY)
INSERT INTO @objectTypes(ObjectType)
SELECT DISTINCT Value FROM dbo.SplitStringToTable_UDF(@ObjectType1,',')

INSERT INTO @dupIrToItem
SELECT
 IR_DS.IR_DbId,
 Count(1) as RelCount
FROM
  dbo.Objects_tbl IR
  INNER JOIN dbo.DS_IR_rel_View IR_DS ON (IR.Id = IR_DS.IR_DbId)
  INNER JOIN dbo.Objects_tbl DS ON (IR_DS.DS_DbId = DS.Id)
WHERE
  EXISTS (SELECT 1 FROM @objectTypes WHERE ObjectType=DS.ObjectType)
GROUP BY
  IR_DS.IR_DbId
HAVING
  Count(1) > 1
-- 'UGMASTER',,
-- 'CATPART','CATPRODUCT'
-- UGMASTER;IDEASPART;IDEASASSEMBLY;PROPRT;PROASM;CATPART
 -- 'catpart','catproduct'
 -- 'UGMASTER';'IDEASPART';'PROPRT';'PROASM'
----
SELECT -- IR.Id AS IrDbId, IR.ObjectClass AS IRClass,
  IR.ObjectFromSiteId AS [From Site],
  IR.ObjectName AS [Ir.Id] ,
  IR.ObjectGroup AS [Owning.Group],
  -- DS.Id AS ItemDbId, DS.ObjectClass AS ItemClass,
  DS.ObjectName AS [DataSet Name],
  DS.ObjectType AS [DataSet Type],
  DUP.RelCount AS [N.Counts]
FROM @dupIrToItem DUP
  LEFT JOIN dbo.DS_IR_rel_View IR_DS ON (IR_DS.IR_DbId = DUP.IR_DbId)
  LEFT JOIN dbo.Objects_tbl IR ON (IR.Id = IR_DS.IR_DbId)
  LEFT JOIN dbo.Objects_tbl DS ON (DS.Id = IR_DS.DS_DbId)
WHERE
  -- DS.ObjectType IN (@ObjectType1,@ObjectType2)
  EXISTS (SELECT 1 FROM @objectTypes WHERE ObjectType=DS.ObjectType)
  and IR.ObjectFromSiteId like @ObjectFromSiteID
ORDER BY IR.Id
end
GO

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
Print '-- [Report_411_NX_IRsDatasets_With_MoreThenOneMaster] ---------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_411_NX_IRsDatasets_With_MoreThenOneMaster")
Drop Procedure Report_411_NX_IRsDatasets_With_MoreThenOneMaster
GO
CREATE PROCEDURE Report_411_NX_IRsDatasets_With_MoreThenOneMaster
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_410_List_IRsDatasets_With_MoreThenOneMaster @ObjectFromSiteID, @ProcStateCol, @ObjectClass, 'UGMASTER'
end
GO

----------------------------------------------------------------------------------------------
Print '-- [Report_412_IDEAS_IRsDatasets_With_MoreThenOneMaster] ------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_412_IDEAS_IRsDatasets_With_MoreThenOneMaster")
Drop Procedure Report_412_IDEAS_IRsDatasets_With_MoreThenOneMaster
GO
CREATE PROCEDURE Report_412_IDEAS_IRsDatasets_With_MoreThenOneMaster
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_410_List_IRsDatasets_With_MoreThenOneMaster @ObjectFromSiteID, @ProcStateCol, @ObjectClass, 'IDEASPART,IDEASASSEMBLY'
end
GO

----------------------------------------------------------------------------------------------
Print '-- [Report_412_IDEAS_IRsDatasets_With_MoreThenOneDrawing] ------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_412_IDEAS_IRsDatasets_With_MoreThenOneDrawing")
Drop Procedure Report_412_IDEAS_IRsDatasets_With_MoreThenOneDrawing
GO
CREATE PROCEDURE Report_412_IDEAS_IRsDatasets_With_MoreThenOneDrawing
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_410_List_IRsDatasets_With_MoreThenOneMaster @ObjectFromSiteID, @ProcStateCol, @ObjectClass, 'IDEASDRAWING'
end
GO

Print '-- [Report_412_xIDEAS_IRsDatasets_With_MoreThenOneIdeasDataset] ------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_412_xIDEAS_IRsDatasets_With_MoreThenOneIdeasDataset")
Drop Procedure Report_412_xIDEAS_IRsDatasets_With_MoreThenOneIdeasDataset
GO
CREATE PROCEDURE Report_412_xIDEAS_IRsDatasets_With_MoreThenOneIdeasDataset
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_410_List_IRsDatasets_With_MoreThenOneMaster @ObjectFromSiteID, @ProcStateCol, @ObjectClass, 'IDEASPART,IDEASASSEMBLY,IDEASDRAWING'
end
GO


----------------------------------------------------------------------------------------------
Print '-- [Report_413_ProE_IRsDatasets_With_MoreThenOneMaster] -------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_413_ProE_IRsDatasets_With_MoreThenOneMaster")
Drop Procedure Report_413_ProE_IRsDatasets_With_MoreThenOneMaster
GO
CREATE PROCEDURE Report_413_ProE_IRsDatasets_With_MoreThenOneMaster
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_410_List_IRsDatasets_With_MoreThenOneMaster @ObjectFromSiteID, @ProcStateCol, @ObjectClass, 'PROPRT,PROASM'
end
GO
----------------------------------------------------------------------------------------------
Print '-- [Report_414_CATIA_IRsDatasets_With_MoreThenOneMaster] ------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_414_CATIA_IRsDatasets_With_MoreThenOneMaster")
Drop Procedure Report_414_CATIA_IRsDatasets_With_MoreThenOneMaster
GO
CREATE PROCEDURE Report_414_CATIA_IRsDatasets_With_MoreThenOneMaster
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_410_List_IRsDatasets_With_MoreThenOneMaster @ObjectFromSiteID, @ProcStateCol, @ObjectClass, 'CATPART,CATPRODUCT'
end
GO


Print ''
Print '---------------------------------------------------------------------------------------'
Print ' R E P O R T S imported to #DB_N#'
Print '---------------------------------------------------------------------------------------'
GO
