 
IF  EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_PDV_VD_DEVOLUCAO]'))
DROP VIEW [DBO].[V_PDV_VD_DEVOLUCAO]
GO

/****** OBJECT:  VIEW [DBO].[V_PDV_VD_DEVOLUCAO]    SCRIPT DATE: 12/03/2013 10:43:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--SELECT VLR_NF FROM V_PDV_VD_DEVOLUCAO WHERE CD_EMP = 1 AND CD_FILIAL = 2 AND DT_EMI_NF='2007-11-01'
CREATE VIEW [DBO].[V_PDV_VD_DEVOLUCAO] AS
SELECT
	CD_EMP,
	CD_FILIAL,
	DT_EMI_NF,
	SUM(VLR_NF) AS VLR_NF 
FROM
(SELECT
	EST_NF_ENT.CD_EMP,
	EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT.DT_EMI_NF,
	ISNULL(SUM(EST_NF_ENT.VLR_NF),0) AS VLR_NF 
FROM 
	EST_NF_ENT INNER JOIN GLB_MOV_OP  ON
	EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP	
WHERE 	
	GLB_MOV_OP.TP_MOV_OP_TRANSA =0 
	AND GLB_MOV_OP.TP_MOV =11 
	AND GLB_MOV_OP.DEST_ORIG =0 
	AND GLB_MOV_OP.ATU_EST =1 
	AND EST_NF_ENT.STS_NF = 0
	AND NOT EXISTS(SELECT 
						PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP
					FROM
						PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT
					WHERE
						EST_NF_ENT.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
						EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
						EST_NF_ENT.CD_NF = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF )
GROUP BY
	EST_NF_ENT.CD_EMP,
	EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT.DT_EMI_NF
UNION ALL
SELECT     
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	EST_PROD_IMP.DT_CAD, 
	SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT) AS VLR_NF
FROM         
	PDV_VD_DEV_EST_PROD_IMP INNER JOIN EST_PROD_IMP ON 
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_PROD_IMP.CD_EMP AND 
    PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_PROD_IMP.CD_FILIAL AND 
	PDV_VD_DEV_EST_PROD_IMP.CD_IMP = EST_PROD_IMP.CD_IMP AND 
    PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_PROD_IMP.CD_PROD AND
    CONVERT(VARCHAR(10),PDV_VD_DEV_EST_PROD_IMP.DT_CAD,120)  = CONVERT(VARCHAR(10),EST_PROD_IMP.DT_CAD,120)

WHERE
	EXISTS(SELECT 
		PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP
	FROM
		PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT
	WHERE
		PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
		PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
		PDV_VD_DEV_EST_PROD_IMP.CD_IMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP)
GROUP BY
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	EST_PROD_IMP.DT_CAD
UNION ALL
SELECT     
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	EST_PROD_IMP.DT_CAD, 
	SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT) AS VLR_NF
FROM         
	PDV_VD_DEV_EST_PROD_IMP INNER JOIN EST_PROD_IMP ON 
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_PROD_IMP.CD_EMP AND 
    	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_PROD_IMP.CD_FILIAL AND 
	PDV_VD_DEV_EST_PROD_IMP.CD_IMP = EST_PROD_IMP.CD_IMP AND 
    	PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_PROD_IMP.CD_PROD
WHERE
	NOT EXISTS(SELECT 
		PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP
	FROM
		PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT
	WHERE
		PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP AND
		PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL AND
		PDV_VD_DEV_EST_PROD_IMP.CD_IMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP)
GROUP BY
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP, 
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL, 
	EST_PROD_IMP.DT_CAD
UNION ALL

SELECT
	 PDV_VD_DEV_NCC.CD_EMP,
	 PDV_VD_DEV_NCC.CD_FILIAL,
	 PDV_VD_DEV_NCC.DT_CAD,
	 PDV_VD_DEV_NCC.VLR_NCC   
FROM         
	PDV_VD_DEV_NCC LEFT OUTER JOIN PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP ON 
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC = PDV_VD_DEV_NCC.CD_CTR_NCC
WHERE     
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_PROD IS NULL
   AND NOT EXISTS 
	(SELECT CD_CTR_NCC 
	FROM EST_NF_ENT_PDV_VD_DEV_NCC DEV_NCC 
    WHERE DEV_NCC.CD_EMP=PDV_VD_DEV_NCC.CD_EMP 
		AND DEV_NCC.CD_FILIAL=PDV_VD_DEV_NCC.CD_FILIAL 
		AND DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC.CD_CTR_NCC)
	AND PDV_VD_DEV_NCC.TIPO_NCC <> 12
	AND PDV_VD_DEV_NCC.TIPO_NCC <> 13
) TMP
GROUP BY
	CD_EMP,
	CD_FILIAL,
	DT_EMI_NF
GO