SET NOCOUNT ON

DECLARE @STR_EXEC VARCHAR(2000)

SET @STR_EXEC = 'CREATE '

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

	SET @STR_EXEC = @STR_EXEC + 'VIEW [dbo].[V_TELE_ORC_TRANSITO]  
			AS  
			SELECT A.CD_FILIAL
				 , A.CD_ORC
				 , C.CD_SAIDA_ORC
				 , B.DTHR_ETAPA
				 , A.ENT_DTHR
				 , A.DTHR_PEDIDO
				 , E.CD_ENTREGADOR
				 , E.DS_ENTREGADOR
				 , D.CD_SAIDA
				 , D.QTDORC
				 , ''PD'' AS TIPO
				 , DATEDIFF(minute, GETDATE(), A.ENT_DTHR) AS PRAZO  
			FROM dbo.TELE_ORC AS A INNER JOIN 
				 dbo.TELE_ORC_ETAPA AS B ON A.CD_ORC = B.CD_ORC AND A.CD_ORC_ETAPA=B.CD_ORC_ETAPA INNER JOIN 
				 dbo.TELE_ORC_ETAPA_SAIDA AS C ON B.CD_ORC_ETAPA = C.CD_ORC_ETAPA INNER JOIN 
				 dbo.TELE_SAIDA AS D ON C.CD_SAIDA = D.CD_SAIDA INNER JOIN 
				 dbo.TELE_ENTREGADOR AS E ON D.CD_ENTREGADOR = E.CD_ENTREGADOR  
			WHERE A.CD_ETAPAS = 4 AND A.CD_TIPO = 0    
			UNION ALL  
			SELECT A.CD_FILIAL
				 , A.CD_OS
				 , C.CD_SAIDA_OS
				 , B.DTHR_ETAPA
				 , A.DTHR
				 , A.DTHR_OS
				 , E.CD_ENTREGADOR
				 , E.DS_ENTREGADOR
				 , C.CD_SAIDA
				 , D.QTDORC
				 , ''OS'' AS TIPO
				 , DATEDIFF(minute, GETDATE(), A.DTHR) AS PRAZO  
			FROM dbo.TELE_OS AS A INNER JOIN 
				 dbo.TELE_OS_ETAPA AS B ON A.CD_OS = B.CD_OS AND A.CD_OS_ETAPA = B.CD_OS_ETAPA INNER JOIN
				 dbo.TELE_SAIDA_OS AS C ON A.CD_OS = C.CD_OS INNER JOIN 
				 dbo.TELE_SAIDA AS D ON C.CD_SAIDA = D.CD_SAIDA INNER JOIN 
				 dbo.TELE_ENTREGADOR AS E ON D.CD_ENTREGADOR = E.CD_ENTREGADOR LEFT OUTER JOIN 
				 TELE_SAIDA_RETORNO_ENT ON C.CD_SAIDA = TELE_SAIDA_RETORNO_ENT.CD_SAIDA  
			WHERE B.CD_OS_TIPO_STATUS = 3   
			 AND dbo.TELE_SAIDA_RETORNO_ENT.CD_SAIDA IS NULL'	
		
	EXEC (@STR_EXEC)