IF EXISTS (SELECT * FROM SYS.views WHERE NAME = 'V_EST_PROD_TRILHO_EXCESSO_CONSULTA') 
BEGIN
	DROP VIEW V_EST_PROD_TRILHO_EXCESSO_CONSULTA
END

GO

CREATE VIEW 
	[dbo].[V_EST_PROD_TRILHO_EXCESSO_CONSULTA]
AS
SELECT 
	TRILHO.CD_EMP,
	TRILHO.CD_FILIAL,
	TRILHO.CD_CTR,
	TRILHO.CD_DESTINO,
	TRILHO_DEST.NOME_FANTASIA_DESTINO,
	TRILHO.DT_TRILHO,
	TRILHO.STS_TRILHO,
	TRILHO_IT.CD_PROD,
	EST_PROD.DS_PROD,
	EST_PROD.CTR_LOTE,
	TRILHO_IT.QTDE_EXCESSO,
	EST_PROD_PRT.DS_PRT, EST_PROD_GVT.DS_GVT,
	(SELECT TOP 1 
		CD_BARRA
	FROM
		EST_PROD_CD_BARRA
	WHERE
		EST_PROD_CD_BARRA.CD_EMP = TRILHO_IT.CD_EMP AND
		EST_PROD_CD_BARRA.CD_PROD = TRILHO_IT.CD_PROD) AS CD_BARRA,
	TRILHO_NF_IT.CD_NF,
	COALESCE(TRILHO.NR_PROTOCOLO,'') AS NR_PROTOCOLO
FROM 
	EST_PROD_TRILHO_EXCESSO AS TRILHO 
	INNER JOIN EST_PROD_TRILHO_EXCESSO_ITENS AS TRILHO_IT ON
		TRILHO.CD_EMP = TRILHO_IT.CD_EMP AND
		TRILHO.CD_FILIAL = TRILHO_IT.CD_FILIAL AND
		TRILHO.CD_CTR = TRILHO_IT.CD_CTR
	INNER JOIN EST_PROD ON 
		EST_PROD.CD_EMP = TRILHO_IT.CD_EMP  AND
		EST_PROD.CD_PROD  = TRILHO_IT.CD_PROD 
	INNER JOIN EST_PROD_CPL ON 
		EST_PROD_CPL.CD_EMP = TRILHO_IT.CD_EMP AND
		EST_PROD_CPL.CD_FILIAL = TRILHO_IT.CD_FILIAL AND
		EST_PROD_CPL.CD_PROD = TRILHO_IT.CD_PROD 
	INNER JOIN EST_PROD_PRT ON 
		EST_PROD_CPL.CD_EMP = EST_PROD_PRT.CD_EMP AND 
		EST_PROD_CPL.PRAT = EST_PROD_PRT.CD_PRT 
	INNER JOIN EST_PROD_GVT ON 
		EST_PROD_CPL.CD_EMP = EST_PROD_GVT.CD_EMP AND 
		EST_PROD_CPL.GAV = EST_PROD_GVT.CD_GVT 
	INNER JOIN EST_PROD_TRILHO_EXCESSO_ITENS_EST_NF_ENT_IT AS TRILHO_NF_IT ON
		TRILHO_NF_IT.CD_EMP = TRILHO_IT.CD_EMP AND
		TRILHO_NF_IT.CD_FILIAL = TRILHO_IT.CD_FILIAL AND
		TRILHO_NF_IT.CD_CTR = TRILHO_IT.CD_CTR AND
		TRILHO_NF_IT.CD_PROD = TRILHO_IT.CD_PROD 
	INNER JOIN (
	SELECT
		TRILHO_DEST.CD_EMP,
		TRILHO_DEST.CD_FILIAL,
		TRILHO_DEST.CD_CTR,
		RC_CLI.CD_CLI AS CD_DESTINO,
		RZ_CLI AS RAZAO_SOCIAL_DESTINO,
		NM_FANT AS NOME_FANTASIA_DESTINO,
		'CLIENTE' AS DS_TIPO_PESSOA	
	FROM
		EST_PROD_TRILHO_EXCESSO_RC_CLI AS TRILHO_DEST 
		INNER JOIN RC_CLI 
		ON
		RC_CLI.CD_EMP = TRILHO_DEST.CD_EMP AND
		RC_CLI.CD_CLI = TRILHO_DEST.CD_CLI  
	UNION ALL
	SELECT
		TRILHO_DEST.CD_EMP,
		TRILHO_DEST.CD_FILIAL,
		TRILHO_DEST.CD_CTR,
		PG_FORN.CD_FORN AS CD_DESTINO,
		RZ_FORN AS RAZAO_SOCIAL_DESTINO,
		NM_FANT AS NOME_FANTASIA_DESTINO,
		'FORNECEDOR' AS DS_TIPO_PESSOA	
	FROM
		EST_PROD_TRILHO_EXCESSO_PG_FORN AS TRILHO_DEST 
		INNER JOIN
		PG_FORN ON
		PG_FORN.CD_EMP = TRILHO_DEST.CD_EMP AND
		PG_FORN.CD_FORN = TRILHO_DEST.CD_FORN 
	UNION ALL
	SELECT
		TRILHO_DEST.CD_EMP,
		TRILHO_DEST.CD_FILIAL,
		TRILHO_DEST.CD_CTR,
		PRC_FILIAL.CD_FILIAL AS CD_DESTINO,
		RZ_FILIAL AS RAZAO_SOCIAL_DESTINO,
		NM_FANT AS NOME_FANTASIA_DESTINO,
		'FILIAL' AS DS_TIPO_PESSOA	
	FROM
		EST_PROD_TRILHO_EXCESSO_PRC_FILIAL AS TRILHO_DEST 
		INNER JOIN
		PRC_FILIAL ON
		PRC_FILIAL.CD_EMP = TRILHO_DEST.CD_EMP AND
		PRC_FILIAL.CD_FILIAL = TRILHO_DEST.CD_FILIAL
		) AS TRILHO_DEST ON
			TRILHO.CD_EMP = TRILHO_DEST.CD_EMP  AND
			TRILHO.CD_FILIAL = TRILHO_DEST.CD_FILIAL AND
			TRILHO.CD_CTR = TRILHO_DEST.CD_CTR  AND
			TRILHO.CD_DESTINO = TRILHO_DEST.CD_DESTINO 


GO


