SET NOCOUNT ON

DECLARE @STR_EXEC VARCHAR(8000)

SET @STR_EXEC = 'CREATE '

--FN = Scalar function
--FS = Assembly (CLR) scalar-function
--FT = Assembly (CLR) table-valued function
--TF = Table function

IF EXISTS(
	SELECT 
		ID 
	FROM 
		SYSOBJECTS 
	WHERE 
		NAME = 'UFD_TELE_PAINEL_ETAPAS' AND XTYPE in ('FN', 'FS', 'FT', 'TF', 'IF'))
BEGIN
	SET	@STR_EXEC = 'ALTER '
END

	SET @STR_EXEC = @STR_EXEC + 'FUNCTION [dbo].[UFD_TELE_PAINEL_ETAPAS]
			(@CD_FILIAL   INT)          
						  
			RETURNS @RS_RET TABLE(        
				 CD_FILIAL   INT,         
				 CD_ETAPAS   INT,              
				 TIPO        INT,       
				 QTD         INT)        
			AS
			BEGIN      
				  
			INSERT INTO @RS_RET
			SELECT
				CD_FILIAL
				, CD_ETAPAS
				, TIPO
				, COUNT(TIPO) AS QTD
			FROM
				(
					SELECT
						CD_FILIAL
						, 2 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_AGUARDSEPARACAO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 2 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_ORC_EMSEPARACAO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 2 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_ORC_EM_CHECK_OUT -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 1 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_ORC_SEPARADO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 3 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_ORC_CONFERIDO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 4 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_ORC_TRANSITO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 5 AS CD_ETAPAS
						, 1 AS TIPO
					FROM
						V_TELE_ORC_ENTREGUE --ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 6 AS CD_ETAPAS
						, 1 AS TIPO
					FROM
						V_TELE_ORC_CANCEL --ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 7 AS CD_ETAPAS
						, 1 AS TIPO
					FROM
						V_TELE_ORC_ALTERADO --ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 8 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_ORC_PENDENCIA --ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 8 AS CD_ETAPAS
						, (
							CASE
								WHEN PRAZO > 2 THEN
									1
								WHEN PRAZO < 0 THEN
									3
								ELSE
									2
							END
						) AS TIPO
					FROM
						V_TELE_ORC_PENDENCIA_RESOLUCAO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 9 AS CD_ETAPAS
						, 1 AS TIPO
					FROM
						V_TELE_ORC_RETORNO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
					----------------------------------
					UNION ALL
					----------------------------------
					SELECT
						CD_FILIAL
						, 10 AS CD_ETAPAS
						, 1 AS TIPO
					FROM
						V_TELE_ORC_DEVOLUCAO -- ALTERADO
					WHERE
						CD_FILIAL = @CD_FILIAL
				) AS VISAO_GERAL
			GROUP BY
				CD_FILIAL
				, CD_ETAPAS
				, TIPO
			  
			 RETURN      
			END'	
		
	EXEC (@STR_EXEC)