USP_KPI_EMAILPROCESS_STATUS

          create procedure dbo.USP_KPI_EMAILPROCESS_STATUS(
              @VALUE integer output, 
              @ASOFDATE datetime,
            @INCLUDETRANSACTIONALPROCESSES bit = 1,
            @INCLUDEGENERALPURPOSEPROCESSES bit = 1,
            @INCLUDESTATUSPROCESSES bit = 1
          )
          as
          begin
              set nocount on;

            select @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);


            declare @GENERALPURPOSEID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
            declare @TRANSACTIONALID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
            declare @STATUSID uniqueidentifier = '00000000-0000-0000-0000-000000000000';

            if @INCLUDETRANSACTIONALPROCESSES = 1
              set @TRANSACTIONALID = 'F79575E5-FE4F-47BD-954C-AB056EF109D9';


            if @INCLUDEGENERALPURPOSEPROCESSES = 1
              set @GENERALPURPOSEID = '5066A489-6282-400C-96AF-1ECC1B7F281F';

            if @INCLUDESTATUSPROCESSES = 1
              set @STATUSID = '8C3ECDB1-BE50-4898-AD1B-98ECB6D07812';

            declare @HEALTHYPROCESS integer;
            declare @TOTALPROCESS integer;



            select @HEALTHYPROCESS = COUNT(*)
              from dbo.BUSINESSPROCESSSTATUS as BPS
              inner join dbo.GLOBALCHANGESTATUS as STATUS on STATUS.ID = BPS.ID
              inner join dbo.GLOBALCHANGE as GC on GC.ID = STATUS.PARAMETERSETID
              inner join dbo.GLOBALCHANGECATALOG as GCC on GCC.ID = GC.GLOBALCHANGECATALOGID and GCC.ID in(@TRANSACTIONALID, @GENERALPURPOSEID, @STATUSID)
              where BPS.STATUSCODE <> 2 and BPS.STARTEDON <= @ASOFDATE;

            select @TOTALPROCESS = COUNT(*)
              from dbo.BUSINESSPROCESSSTATUS as BPS
              inner join dbo.GLOBALCHANGESTATUS as STATUS on STATUS.ID = BPS.ID
              inner join dbo.GLOBALCHANGE as GC on GC.ID = STATUS.PARAMETERSETID
              inner join dbo.GLOBALCHANGECATALOG as GCC on GCC.ID = GC.GLOBALCHANGECATALOGID and GCC.ID in(@TRANSACTIONALID, @GENERALPURPOSEID, @STATUSID)
              where BPS.STARTEDON <= @ASOFDATE;


            set @VALUE = @TOTALPROCESS - @HEALTHYPROCESS;

            if @VALUE < 0
              set @VALUE = 0;

            --if @TOTALPROCESS <> 0

              --set @VALUE = (cast(@HEATHLYPROCESS as decimal(20,4)) / cast(@TOTALPROCESS as decimal(20,4)));

            --else

              --set @VALUE = 1;

          end