/****** Object:  View [dbo].[V_EST_NF_ENT_EST_WMS_ENT]    Script Date: 05/08/2013 09:22:37 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_EST_WMS_ENT]'))
DROP VIEW [dbo].[V_EST_NF_ENT_EST_WMS_ENT]
GO
GO
--SELECT * FROM V_EST_NF_ENT_EST_WMS_ENT WHERE CD_EMP =1 AND CD_FILIAL = 900 AND CD_PED_WMS=107019
CREATE VIEW V_EST_NF_ENT_EST_WMS_ENT AS 
SELECT        
	EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_EMP, 
	EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_FILIAL, 
    EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_NF, 
	EST_WMS_ENT.CD_PED_WMS, 
	EST_WMS_ENT.DT_CAD, 
	EST_WMS_ENT.CD_USU,
	CASE WHEN EST_WMS_ENT_PEND.CD_PED_WMS IS NULL THEN
		CASE WHEN NOT EST_WMS_ENT_RET.CD_PED_WMS IS NULL THEN
			CASE WHEN EST_WMS_ENT_RET_PEND.CD_PED_WMS IS NULL THEN
				'FINALIZADO PELO WMS'
			ELSE
				'PENDENTE DE ENVIO RETORNO DO WMS'
			END
		ELSE
			'SEM RETORNO WMS'
		END
	ELSE
		'PENDENTE DE ENVIO AO WMS'
	END AS STS_PED_WMS,
	'PRE-ENTRADA' AS TIPO_DOCUMENTO,
	NM_USU,
	EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_CTR_PRE_ENTRADA,
	EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_FILIAL AS CD_FILIAL_ORIGEM, 
    EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_CTR_PRE_ENTRADA AS CD_DOCUMENTO_CONTROLE,
	0 AS CD_MOV_OP,
	'' AS DS_MOV_OP
FROM  
EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT INNER JOIN EST_NF_ENT_PRE_ENTRADA_EST_WMS_ENT ON 
EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_EMP = EST_NF_ENT_PRE_ENTRADA_EST_WMS_ENT.CD_EMP AND 
EST_NF_ENT_PRE_ENTRADA_EST_NF_ENT.CD_CTR_PRE_ENTRADA = EST_NF_ENT_PRE_ENTRADA_EST_WMS_ENT.CD_CTR_PRE_ENTRADA 
INNER JOIN EST_WMS_ENT ON 
EST_NF_ENT_PRE_ENTRADA_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT.CD_PED_WMS
INNER JOIN GLB_USU ON
EST_WMS_ENT.CD_USU  = GLB_USU.CD_USU 
LEFT JOIN EST_WMS_ENT_PEND ON 
EST_NF_ENT_PRE_ENTRADA_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_PEND.CD_PED_WMS
LEFT JOIN 
	(SELECT
		DISTINCT CD_PED_WMS
	FROM
		EST_WMS_ENT_RET) EST_WMS_ENT_RET ON		
EST_NF_ENT_PRE_ENTRADA_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_RET.CD_PED_WMS
LEFT JOIN 
	(SELECT
		DISTINCT CD_PED_WMS
	FROM
		EST_WMS_ENT_RET_PEND) EST_WMS_ENT_RET_PEND ON		
EST_NF_ENT_PRE_ENTRADA_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_RET_PEND.CD_PED_WMS
UNION ALL
SELECT        
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP, 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST, 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT, 
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS, 
	EST_NF_SAI_EST_WMS_ENT.DT_CAD, 
	EST_NF_SAI_EST_WMS_ENT.CD_USU,
	CASE WHEN EST_WMS_ENT_PEND.CD_PED_WMS IS NULL THEN
		CASE WHEN NOT EST_WMS_ENT_RET.CD_PED_WMS IS NULL THEN
			CASE WHEN EST_WMS_ENT_RET_PEND.CD_PED_WMS IS NULL THEN
				'FINALIZADO PELO WMS'
			ELSE
				'PENDENTE DE ENVIO RETORNO DO WMS'
			END
		ELSE
			'SEM RETORNO WMS'
		END
	ELSE
		'PENDENTE DE ENVIO AO WMS'
	END,
	'TRANSFERENCIA' AS TIPO_DOCUMENTO,
	NM_USU,
	0 AS CD_CTR_PRE_ENTRADA,
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG AS CD_FILIAL_ORIGEM, 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI AS CD_DOCUMENTO_CONTROLE,
	EST_NF_SAI_EST_WMS_ENT.CD_MOV_OP,
	GLB_MOV_OP.DS_MOV_OP
FROM            
	EST_NF_ENT_EST_NF_SAI_TRANSF INNER JOIN EST_NF_SAI_EST_WMS_ENT ON 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_SAI_EST_WMS_ENT.CD_EMP AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = EST_NF_SAI_EST_WMS_ENT.CD_FILIAL_ORIG AND 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = EST_NF_SAI_EST_WMS_ENT.CD_NF_ORIG 
	INNER JOIN EST_WMS_ENT ON 
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT.CD_PED_WMS
	INNER JOIN GLB_USU ON
	EST_WMS_ENT.CD_USU  = GLB_USU.CD_USU 
	LEFT JOIN EST_WMS_ENT_PEND ON 
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_PEND.CD_PED_WMS
	LEFT JOIN 
		(SELECT
			DISTINCT CD_PED_WMS
		FROM
	EST_WMS_ENT_RET_PEND) EST_WMS_ENT_RET_PEND ON		
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_RET_PEND.CD_PED_WMS
	INNER JOIN GLB_MOV_OP ON
	EST_NF_SAI_EST_WMS_ENT.CD_MOV_OP = GLB_MOV_OP.CD_MOV_OP
LEFT JOIN 
	(SELECT
		DISTINCT CD_PED_WMS
	FROM
		EST_WMS_ENT_RET) EST_WMS_ENT_RET ON		
EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_RET.CD_PED_WMS
UNION ALL
SELECT        
	EST_NF_SAI.CD_EMP, 
	EST_NF_SAI.CD_FILIAL, 
    EST_NF_SAI.CD_NF, 
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS, 
	EST_NF_SAI_EST_WMS_ENT.DT_CAD, 
	EST_NF_SAI_EST_WMS_ENT.CD_USU,
	CASE WHEN EST_WMS_ENT_PEND.CD_PED_WMS IS NULL THEN
		CASE WHEN NOT EST_WMS_ENT_RET.CD_PED_WMS IS NULL THEN
			CASE WHEN EST_WMS_ENT_RET_PEND.CD_PED_WMS IS NULL THEN
				'FINALIZADO PELO WMS'
			ELSE
				'PENDENTE DE ENVIO RETORNO DO WMS'
			END
		ELSE
			'SEM RETORNO WMS'
		END
	ELSE
		'PENDENTE DE ENVIO AO WMS'
	END,
	'TRANSFERENCIA' AS TIPO_DOCUMENTO,
	NM_USU,
	0 AS CD_CTR_PRE_ENTRADA,
	EST_NF_SAI.CD_FILIAL AS CD_FILIAL_ORIGEM, 
    EST_NF_SAI.CD_NF AS CD_DOCUMENTO_CONTROLE,
	EST_NF_SAI_EST_WMS_ENT.CD_MOV_OP,
	GLB_MOV_OP.DS_MOV_OP
FROM            
	EST_NF_SAI INNER JOIN EST_NF_SAI_EST_WMS_ENT ON 
	EST_NF_SAI.CD_EMP = EST_NF_SAI_EST_WMS_ENT.CD_EMP AND 
    EST_NF_SAI.CD_FILIAL = EST_NF_SAI_EST_WMS_ENT.CD_FILIAL_ORIG AND 
	EST_NF_SAI.CD_NF = EST_NF_SAI_EST_WMS_ENT.CD_NF_ORIG 
	INNER JOIN EST_WMS_ENT ON 
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT.CD_PED_WMS
	INNER JOIN GLB_USU ON
	EST_WMS_ENT.CD_USU  = GLB_USU.CD_USU 
	LEFT JOIN EST_WMS_ENT_PEND ON 
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_PEND.CD_PED_WMS
	LEFT JOIN 
		(SELECT
			DISTINCT CD_PED_WMS
		FROM
	EST_WMS_ENT_RET_PEND) EST_WMS_ENT_RET_PEND ON		
	EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_RET_PEND.CD_PED_WMS
LEFT JOIN 
	(SELECT
		DISTINCT CD_PED_WMS
	FROM
		EST_WMS_ENT_RET) EST_WMS_ENT_RET ON		
EST_NF_SAI_EST_WMS_ENT.CD_PED_WMS = EST_WMS_ENT_RET.CD_PED_WMS
	INNER JOIN GLB_MOV_OP ON
	EST_NF_SAI_EST_WMS_ENT.CD_MOV_OP = GLB_MOV_OP.CD_MOV_OP
