SET NOCOUNT ON

DECLARE @STR_EXEC VARCHAR(4000)

SET @STR_EXEC = 'CREATE '

IF EXISTS(
	SELECT 
		ID 
	FROM 
		SYSOBJECTS 
	WHERE 
		NAME = 'V_TELE_ORC_ENTREGUE' AND XTYPE = 'V')
BEGIN
	SET	@STR_EXEC = 'ALTER '
END

	SET @STR_EXEC = @STR_EXEC + 'VIEW [dbo].[V_TELE_ORC_ENTREGUE] AS  
			SELECT ORC.CD_FILIAL
				 , ORC.CD_ORC  
				 , ETP.DTHR_ETAPA  
				 , ISNULL(ENT.DS_ENTREGADOR, ''CLIENTE BUSCOU NA LOJA'') AS DS_ENTREGADOR  
				 , USU.NM_USU  
				 , (  
					CASE  
					   WHEN ORC.ORG_PEDIDO = 0 THEN  
						''PD''  
					   WHEN ORC.ORG_PEDIDO = 1 THEN  
						''LJ''  
					END  
				  ) AS TIPO  
			FROM TELE_ORC AS ORC INNER JOIN 
				 TELE_ORC_ETAPA AS ETP ON ORC.CD_ORC = ETP.CD_ORC AND ORC.CD_ORC_ETAPA = ETP.CD_ORC_ETAPA INNER JOIN 
				 GLB_USU AS USU ON ETP.CD_USU = USU.CD_USU LEFT JOIN (  
					  SELECT A.CD_ORC  
							, MAX(A.CD_ORC_ETAPA) AS CD_ORC_ETAPA  
					  FROM TELE_ORC_ETAPA AS A   
					  WHERE A.CD_ETAPAS = 4 AND A.CD_TIPO = 0  
					  GROUP BY A.CD_ORC     
				 ) AS TMP1 ON ORC.CD_ORC = TMP1.CD_ORC LEFT JOIN 
				 TELE_ORC_ETAPA_SAIDA AS ETPS ON TMP1.CD_ORC_ETAPA = ETPS.CD_ORC_ETAPA LEFT JOIN 
				 TELE_SAIDA AS SAI ON ETPS.CD_SAIDA = SAI.CD_SAIDA LEFT JOIN 
				 TELE_ENTREGADOR AS ENT ON SAI.CD_ENTREGADOR = ENT.CD_ENTREGADOR  
			WHERE (ORC.CD_ETAPAS = 5 AND ORC.CD_TIPO = 0)  AND
				   ORC.DTHR_PEDIDO > DATEADD(MONTH, -3, GETDATE()) AND
				   ETP.DTHR_ETAPA >= DATEADD(MINUTE, - (60 * CAST(dbo.UFD_PRC_EMP_CONFIG(1,''TV_TEMPO_VISUAL_PED_ENTREGUE'',''2'') AS INT)), GETDATE())
			UNION ALL  
			  
			SELECT OS.CD_FILIAL  
				 , OS.CD_OS  
				 , ETP.DTHR_ETAPA  
				 , ENT.DS_ENTREGADOR AS DS_ENTREGADOR  
				 , USU.NM_USU  
				 , ''OS'' AS TIPO  
			FROM TELE_OS AS OS INNER JOIN 
				 TELE_OS_ETAPA AS ETP ON OS.CD_OS = ETP.CD_OS AND OS.CD_OS_ETAPA = ETP.CD_OS_ETAPA INNER JOIN 
				 GLB_USU AS USU ON ETP.CD_USU = USU.CD_USU INNER JOIN 
				 TELE_SAIDA_OS AS ETPS ON OS.CD_OS = ETPS.CD_OS INNER JOIN 
				 TELE_SAIDA AS SAI ON ETPS.CD_SAIDA = SAI.CD_SAIDA INNER JOIN 
				 TELE_SAIDA_RETORNO_ENT AS RET ON SAI.CD_SAIDA = RET.CD_SAIDA INNER JOIN 
				 TELE_ENTREGADOR AS ENT ON SAI.CD_ENTREGADOR = ENT.CD_ENTREGADOR    
			WHERE (OS.DTHR > DATEADD(MONTH, -3, GETDATE())) AND 
			      RET.DTHR_RETORNO >= DATEADD(MINUTE, - (60 * CAST(dbo.UFD_PRC_EMP_CONFIG(1,''TV_TEMPO_VISUAL_PED_ENTREGUE'',''2'') AS INT)), GETDATE())'	
		
	EXEC (@STR_EXEC)