IF EXISTS ( SELECT NAME FROM SYS.OBJECTS WHERE NAME = 'V_EST_PROD_TRANSF_x_EST_NF_SAI')
BEGIN
	DROP VIEW V_EST_PROD_TRANSF_x_EST_NF_SAI
END
GO
CREATE VIEW [dbo].[V_EST_PROD_TRANSF_x_EST_NF_SAI] AS 
SELECT     
	EST_PROD_TRANSF.CD_EMP, 
	EST_PROD_TRANSF.CD_FILIAL, 	
	EST_PROD_TRANSF.CD_CTR,
	PRC_FILIAL.CD_FILIAL AS CD_FILIAL_DEST,
	PRC_FILIAL.RZ_FILIAL AS NM_FILIAL_DEST, 
    0 AS NR_NF,
	EST_PROD_TRANSF_IT.CD_PROD, 
	EST_PROD_TRANSF_IT.QT_AT AS QTDE_PROD_CONF,
	0 AS QT_PROD_NF,
	EST_PROD_TRANSF.DT_TRANSF,
	EST_PROD_TRANSF.STS_TRANSF
FROM         
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_IT ON 
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_IT.CD_EMP AND 
    EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_IT.CD_FILIAL AND 
    EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_IT.CD_CTR 
	INNER JOIN PRC_FILIAL ON 
	EST_PROD_TRANSF.CD_EMP = PRC_FILIAL.CD_EMP AND 
	EST_PROD_TRANSF.CD_FIL_DEST = PRC_FILIAL.CD_FILIAL
UNION ALL
SELECT     
	EST_PROD_TRANSF_PED_VD.CD_EMP, 
	EST_PROD_TRANSF_PED_VD.CD_FILIAL, 
	EST_PROD_TRANSF_PED_VD.CD_CTR, 
	PRC_FILIAL.CD_FILIAL AS CD_FILIAL_DEST,
	PRC_FILIAL.RZ_FILIAL AS NM_FILIAL_DEST, 
	EST_NF_SAI.NF_NF, 
    EST_NF_SAI_IT.CD_PROD, 
	0 AS QTDE_PROD_CONF,
	EST_NF_SAI_IT.QT_IT AS QT_PROD_NF,
	EST_PROD_TRANSF.DT_TRANSF,
	EST_PROD_TRANSF.STS_TRANSF
FROM         
	EST_PROD_TRANSF_PED_VD INNER JOIN EST_NF_SAI_PED_VD ON 
	EST_PROD_TRANSF_PED_VD.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP AND 
    EST_PROD_TRANSF_PED_VD.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL AND 
    EST_PROD_TRANSF_PED_VD.CD_PED = EST_NF_SAI_PED_VD.CD_PED 
	INNER JOIN EST_NF_SAI ON 
	EST_NF_SAI_PED_VD.CD_EMP = EST_NF_SAI.CD_EMP AND 
	EST_NF_SAI_PED_VD.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND 
    EST_NF_SAI_PED_VD.CD_NF = EST_NF_SAI.CD_NF 
	INNER JOIN EST_NF_SAI_IT ON 
	EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND 
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
    EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF
	INNER JOIN EST_PROD_TRANSF ON 
	EST_PROD_TRANSF_PED_VD.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
    EST_PROD_TRANSF_PED_VD.CD_FILIAL = EST_PROD_TRANSF.CD_FILIAL AND 
    EST_PROD_TRANSF_PED_VD.CD_CTR = EST_PROD_TRANSF.CD_CTR 
	INNER JOIN PRC_FILIAL ON 
	EST_PROD_TRANSF.CD_EMP = PRC_FILIAL.CD_EMP AND 
	EST_PROD_TRANSF.CD_FIL_DEST = PRC_FILIAL.CD_FILIAL
