Print ''
Print '---------------------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [REPORTS Customer ASML]'
Print ' Creation      : 01.07.2014/J.Fes'
Print ' Last.Update   : 01.07.2014 12:38/J.Fes'

use [#DB_N#]

Print ''
Print ' '
Print '---------------------------------------------------------------------------------------'
Print '--------------------- R E P O R T S - LIST OF Results  --------------------------------'
Print '---------------------------------------------------------------------------------------'
Print ''


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


AS
begin
 SELECT
  OBJ.IrName,
  OBJ.ObjectName,
  OBJ.ObjectType,
  CASE
    WHEN SC1_DS.ProcessResultMsg IS Null or SC2_DS.ProcessResultMsg IS Null
      THEN '~'
    WHEN SC1_DS.ProcessResultMsg = SC2_DS.ProcessResultMsg
      THEN 'YES'
    ELSE  'NO'
  END AS [P.Stat.SC1-2.Equal],
  CASE
    WHEN SC1_DS.ProcessResultMsg IS Null or SC3_DS.ProcessResultMsg IS Null
      THEN '~'
    WHEN SC1_DS.ProcessResultMsg = SC3_DS.ProcessResultMsg
      THEN 'YES'
    ELSE  'NO'
  END AS [P.Stat.SC1-3.Equal],
  CASE
    WHEN SC2_DS.ProcessResultMsg IS Null or SC3_DS.ProcessResultMsg IS Null
      THEN '~'
    WHEN SC2_DS.ProcessResultMsg = SC3_DS.ProcessResultMsg
      THEN 'YES'
    ELSE  'NO'
  END AS [P.Stat.SC2-3.Equal],
  CASE
    WHEN SC1_DS.ProcessResultMsg = SC2_DS.ProcessResultMsg
         AND SC2_DS.ProcessResultMsg = SC3_DS.ProcessResultMsg
      THEN 'YES'
    ELSE  'NO'
  END AS [P.Stat.SC1-2-3.Equal],
  SC1_DS.ProcessState AS [SC1.P.Stat],
  SC1_DS.ProcessResultCode AS [SC1.P.Code],
  SC1_DS.ProcessResultMsg AS [SC1.P.MSG],
  SC2_DS.ProcessState AS [SC2.P.Stat],
  SC2_DS.ProcessResultCode AS [SC2.P.Code],
  SC2_DS.ProcessResultMsg AS [SC2.P.MSG],
  SC3_DS.ProcessState AS [SC3.P.Stat],
  SC3_DS.ProcessResultCode AS [SC3.P.Code],
  SC3_DS.ProcessResultMsg AS [SC3.P.MSG]
FROM
  dbo.DS_with_Irs_View OBJ
  LEFT OUTER JOIN (SELECT * FROM dbo.DS_AndProcessData_UDF('ProcStateCol01') WHERE Row_isLastRes = 1) SC1_DS ON (SC1_DS.ObjectPuid = OBJ.ObjectPuid)
  LEFT OUTER JOIN (SELECT * FROM dbo.DS_AndProcessData_UDF('ProcStateCol03') WHERE Row_isLastRes = 1) SC2_DS ON (SC2_DS.ObjectPuid = OBJ.ObjectPuid)
  LEFT OUTER JOIN (SELECT * FROM dbo.DS_AndProcessData_UDF('ProcStateCol04') WHERE Row_isLastRes = 1) SC3_DS ON (SC3_DS.ObjectPuid = OBJ.ObjectPuid)
WHERE
  OBJ.ObjectFromSiteID = @ObjectFromSiteID AND
  OBJ.ObjectClass = 'DS' AND
  (SC1_DS.ProcessState LIKE 'D%ERR' OR
  SC2_DS.ProcessState LIKE 'D%ERR' OR
  SC3_DS.ProcessState LIKE 'D%ERR')
end
GO

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