/****** Object:  View [dbo].[V_PDV_VD_DUPLICATA]    Script Date: 11/14/2011 09:04:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[V_PDV_VD_DUPLICATA]
AS
SELECT     
	dbo.PDV_VD.CD_EMP, 
	dbo.PDV_VD.CD_FILIAL, 
	dbo.PDV_VD.CD_VD, 
	dbo.PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON, 
	dbo.PRC_GRP_ECON.NM_GRP_ECON, 
	dbo.PDV_VD.NR_ECF, 
	dbo.PDV_VD.CD_CX, 
	dbo.PDV_VD.DT_VD, 
	dbo.PDV_VD.VLR_LIQ_VD,
    dbo.PDV_VD.VLR_DUP,
	ISNULL(dbo.EST_NF_SAI.NF_NF, '0') AS NR_NF, 
	dbo.EST_NF_SAI.DT_EMI_NF, 
	ISNULL(dbo.EST_NF_SAI.STS_NF, '0')  AS STS_NF, 
	ISNULL(dbo.EST_NF_SAI.CD_CLI, '0') AS CD_CLI, 
	ISNULL(dbo.RC_CLI.RZ_CLI, 'SEM EMISSÃO NOTA FISCAL') AS RZ_CLI,
	ISNULL(dbo.RC_CLI.CD_COND_PGTO,0) AS CD_COND_PGTO,
	ISNULL(SUM(DEV.VLR_IT),0) VLR_DEV
FROM         
	dbo.RC_CLI

	INNER JOIN dbo.EST_NF_SAI ON
		dbo.RC_CLI.CD_EMP = dbo.EST_NF_SAI.CD_EMP AND 
		dbo.RC_CLI.CD_CLI = dbo.EST_NF_SAI.CD_CLI 

	INNER JOIN dbo.PDV_VD_EST_NF_SAI ON
		dbo.EST_NF_SAI.CD_EMP = dbo.PDV_VD_EST_NF_SAI.CD_EMP AND 
		dbo.EST_NF_SAI.CD_FILIAL = dbo.PDV_VD_EST_NF_SAI.CD_FILIAL AND 
		dbo.EST_NF_SAI.CD_NF = dbo.PDV_VD_EST_NF_SAI.CD_NF 

	RIGHT OUTER JOIN dbo.PDV_VD ON
		dbo.PDV_VD_EST_NF_SAI.CD_EMP = dbo.PDV_VD.CD_EMP AND 
		dbo.PDV_VD_EST_NF_SAI.CD_FILIAL = dbo.PDV_VD.CD_FILIAL AND 
		dbo.PDV_VD_EST_NF_SAI.CD_VD = dbo.PDV_VD.CD_VD 

	INNER JOIN dbo.PRC_GRP_ECON_PRC_FILIAL ON
		dbo.PDV_VD.CD_EMP = dbo.PRC_GRP_ECON_PRC_FILIAL.CD_EMP AND 
		dbo.PDV_VD.CD_FILIAL = dbo.PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL 

	INNER JOIN dbo.PRC_GRP_ECON ON
		dbo.PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = dbo.PRC_GRP_ECON.CD_GRP_ECON

	INNER JOIN PDV_VD_GERAR_DUP_PENDENCIA ON
		PDV_VD.CD_EMP = PDV_VD_GERAR_DUP_PENDENCIA.CD_EMP AND
		PDV_VD.CD_FILIAL = PDV_VD_GERAR_DUP_PENDENCIA.CD_FILIAL AND
		PDV_VD.CD_VD = PDV_VD_GERAR_DUP_PENDENCIA.CD_VD

	LEFT JOIN PDV_VD_DEV_EST_PROD_IMP DEV ON 
		DEV.CD_EMP=PDV_VD.CD_EMP 
		AND DEV.CD_FILIAL=PDV_VD.CD_FILIAL
		AND DEV.CD_VD=PDV_VD.CD_VD

WHERE     
	dbo.PDV_VD.ST_VD = 0
	AND dbo.PDV_VD.VLR_DUP > 0
	AND NOT EXISTS
    (SELECT     
			CD_VD
    FROM          
			dbo.RC_DEB_PDV_VD
    WHERE      
			dbo.PDV_VD.CD_EMP = CD_EMP 
			AND dbo.PDV_VD.CD_FILIAL = CD_FILIAL_VD 
			AND dbo.PDV_VD.CD_VD = CD_VD)
	AND 
	NOT EXISTS
	(SELECT     
			PDV_VD_EST_NF_ENT.CD_VD
	FROM    
			dbo.PDV_VD_EST_NF_ENT INNER JOIN PDV_VD_CANCEL_DUP ON
			PDV_VD_EST_NF_ENT.CD_EMP = PDV_VD_CANCEL_DUP.CD_EMP AND
			PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD_CANCEL_DUP.CD_FILIAL AND
			PDV_VD_EST_NF_ENT.CD_VD = PDV_VD_CANCEL_DUP.CD_VD 
	WHERE      
		dbo.PDV_VD.CD_EMP = PDV_VD_EST_NF_ENT.CD_EMP AND 
		dbo.PDV_VD.CD_FILIAL = PDV_VD_EST_NF_ENT.CD_FILIAL
		AND dbo.PDV_VD.CD_VD = PDV_VD_EST_NF_ENT.CD_VD)
	
	AND 
	NOT EXISTS
	(SELECT CD_VD FROM PDV_VD_CANCEL_DUP where 
		dbo.PDV_VD.CD_EMP = CD_EMP 
		AND dbo.PDV_VD.CD_FILIAL = CD_FILIAL 
		AND dbo.PDV_VD.CD_VD = CD_VD)
		
	--AND
	--NOT EXISTS
	--(SELECT  
	--	DEV.CD_VD
	--FROM PDV_VD_DEV_EST_PROD_IMP  DEV INNER JOIN PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT DEV_NF ON
	--	DEV.CD_EMP=DEV_NF.CD_EMP 
	--	AND DEV.CD_FILIAL=DEV_NF.CD_FILIAL
	--	AND DEV.CD_VD=DEV_NF.CD_VD
	--WHERE      
	--PDV_VD.CD_EMP = DEV.CD_EMP
	--AND PDV_VD.CD_FILIAL = DEV.CD_FILIAL
	--AND PDV_VD.CD_VD = DEV.CD_VD)
			
	AND
    NOT EXISTS
	(SELECT   
	       NF_SAI.CD_VD
	 FROM PDV_VD_EST_NF_SAI  NF_SAI INNER JOIN EST_NF_SAI_CANCEL SAI_CANCEL ON
	    NF_SAI.CD_EMP   =SAI_CANCEL.CD_EMP
		and NF_SAI.CD_FILIAL=SAI_CANCEL.CD_FILIAL
		and NF_SAI.CD_NF	=SAI_CANCEL.CD_NF
	WHERE  
         SAI_CANCEL.CANCEL_FORA_PRAZO=1    
         AND PDV_VD.CD_FILIAL = NF_SAI.CD_FILIAL        
         AND PDV_VD.CD_VD = NF_SAI.CD_VD
		  )
		  
		  

	 AND  ISNULL(dbo.EST_NF_SAI.STS_NF, '0') = 0


	GROUP BY 
	dbo.PDV_VD.CD_EMP, 
	dbo.PDV_VD.CD_FILIAL, 
	dbo.PDV_VD.CD_VD, 
	dbo.PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON, 
	dbo.PRC_GRP_ECON.NM_GRP_ECON, 
	dbo.PDV_VD.NR_ECF, 
	dbo.PDV_VD.CD_CX, 
	dbo.PDV_VD.DT_VD, 
	dbo.PDV_VD.VLR_LIQ_VD,
	dbo.PDV_VD.VLR_DUP,
	dbo.EST_NF_SAI.NF_NF,
	dbo.EST_NF_SAI.DT_EMI_NF, 
	dbo.EST_NF_SAI.STS_NF, 
	dbo.EST_NF_SAI.CD_CLI,  
	dbo.RC_CLI.RZ_CLI,
	dbo.RC_CLI.CD_COND_PGTO


GO


