IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_TRILHO_EXCESSO_PEND]'))
DROP VIEW [dbo].[V_EST_PROD_TRILHO_EXCESSO_PEND]
GO
GO
GO
GO

CREATE VIEW 
[dbo].[V_EST_PROD_TRILHO_EXCESSO_PEND]
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
FROM 
EST_PROD_TRILHO_EXCESSO AS TRILHO
INNER JOIN 
EST_PROD_TRILHO_EXCESSO_PENDENTE AS TRILHO_PEND ON
TRILHO.CD_EMP = TRILHO_PEND.CD_EMP AND
TRILHO.CD_FILIAL = TRILHO_PEND.CD_FILIAL AND
TRILHO.CD_CTR = TRILHO_PEND.CD_CTR 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


