Print ''
Print '---------------------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [REPORTS Customer FEI]'
Print ' Creation      : 07.05.2015 /J.Fes'
Print ' Last.Update   : 07.05.2015 /J.Fes'

use [#DB_N#]

Print ''
Print ' '
Print '---------------------------------------------------------------------------------------'
Print '--------------------- R E P O R T S - FEI  --------------------------------------------'
Print '---------------------------------------------------------------------------------------'
Print ''


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


AS
begin

-- SELECT
--   IR.ObjectFromSiteId    As [FromSite],
--   IR.ObjectOwningSiteID  As [Owning Site],
--   IR.ObjectName          AS [Item/Rev],
--   DS.ObjectName          AS [DS.Name],
--   DS.ObjectType          AS [DS.Type],
--   DS.ObjectCliName       AS [DS.CliName],
--   DS.ObjectLastModDate   AS [DS.LastModDate],
--   DS.ObjectReleaseState  AS [DS.Rel.State],
--   DS.ObjectReleaseDate   AS [DS.Rel.Date]
-- FROM
--   dbo.Objects_tbl IR
--   INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
--   INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
-- where
--    DS.ObjectClass = 'DS'
--    AND DS.ObjectFromSiteID like @ObjectFromSiteID
--    AND DS.ID IN
--    (
--      SELECT
--        dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
--      --  COUNT(*) AS NTimesProcessed
--      FROM
--        dbo.JobControl_ObjectsProcessData_tbl
--      WHERE
--        dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol = @ProcStateCol
--      GROUP BY dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
--      HAVING COUNT(*) > 5
--     )
-- order by IR.ObjectFromSiteId, IR.ObjectCliName

  SELECT
    Min(IR.ObjectFromSiteId)    As [FromSite],
    Min(IR.ObjectOwningSiteID)  As [Owning Site],
    Min(IR.ObjectName)          AS [Item/Rev],
    Min(DS.ObjectName)          AS [DS.Name],
    Min(DS.ObjectType)          AS [DS.Type],
    Min(DS.ObjectCliName)       AS [DS.CliName],
    Min(DS.ObjectLastModDate)   AS [DS.LastModDate],
    Min(DS.ObjectReleaseState)  AS [DS.Rel.State],
    Min(DS.ObjectReleaseDate)   AS [DS.Rel.Date],
    COUNT(PD.Id) AS ProcDataCount
  FROM
    dbo.Objects_tbl IR
    INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
    INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
    INNER JOIN dbo.JobControl_ObjectsProcessData_tbl PD ON (PD.Objects_tbl_ID = DS.Id)
  WHERE
        PD.ProcessProcStateCol = @ProcStateCol
    AND DS.ObjectClass = @ObjectClass
    AND DS.ObjectFromSiteID like @ObjectFromSiteID
  GROUP BY DS.Id
  HAVING COUNT(PD.Id) > 5
  ORDER BY Min(IR.ObjectFromSiteId), Min(IR.ObjectCliName)

end
GO

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