IF OBJECT_ID ('P_PDV_VD_DEV_EST_PROD_IMP_DEVOLUCAO') IS NOT NULL 
    BEGIN
	   DROP PROCEDURE P_PDV_VD_DEV_EST_PROD_IMP_DEVOLUCAO
    END
GO
CREATE PROCEDURE P_PDV_VD_DEV_EST_PROD_IMP_DEVOLUCAO(@CD_EMP   INT, @CD_FILAL INT, @DT_INI   DATETIME, @DT_FIM   DATETIME)
AS
SET NOCOUNT ON 
	   CREATE TABLE #Prod_imp
	   (CD_EMP    INT,
	    CD_FILIAL INT,
	    CD_VD     INT,
	    CD_IMP    INT,
	    CD_PROD   INT,
	    CD_IT     INT,
	    VLR_DEV   MONEY,
	    DT_CAD	 DATETIME
	   );
    insert into #Prod_imp 
    SELECT PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
		 PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
		 PDV_VD_DEV_EST_PROD_IMP.CD_VD,
		 PDV_VD_DEV_EST_PROD_IMP.CD_IMP,
		 PDV_VD_DEV_EST_PROD_IMP.CD_PROD,
		 PDV_VD_DEV_EST_PROD_IMP.CD_IT,
		 SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT) AS VLR_DEV,
		 EST_PROD_IMP.DT_CAD
    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 EST_PROD_IMP.CD_EMP = @CD_EMP
		AND EST_PROD_IMP.CD_FILIAL = @CD_FILAL
		AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM
    GROUP BY PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
		   PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
		   PDV_VD_DEV_EST_PROD_IMP.CD_VD,
		   PDV_VD_DEV_EST_PROD_IMP.CD_IMP,
		   PDV_VD_DEV_EST_PROD_IMP.CD_PROD,
		   PDV_VD_DEV_EST_PROD_IMP.CD_IT,
		   EST_PROD_IMP.DT_CAD;	




	   SELECT #Prod_imp.CD_EMP,
			PDV_VD.CD_CX,
			CASE ISNULL(PDV_VD.NR_MFE,0) WHEN 0
				THEN ISNULL(PDV_VD.NR_ECF,0)
				ELSE ISNULL(PDV_VD.NR_MFE,0)
			END AS NR_ECF,
			#Prod_imp.DT_CAD,
			PDV_VD.CD_VD,
			PDV_VD.VLR_LIQ_VD,
			PDV_VD.DT_VD,
		    #Prod_imp.VLR_DEV,
			NM_COMPRADOR,
			ISNULL(PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT_DENEGADA.CD_EMP, 0) AS ST_DENEGADA,
			COALESCE(PDV_VD_NFCE.CH_NFE, '') AS CH_NFE
	   FROM #Prod_imp
		   INNER JOIN PDV_VD ON #Prod_imp.CD_VD = PDV_VD.CD_VD
						    AND #Prod_imp.CD_FILIAL = PDV_VD.CD_FILIAL
						    AND #Prod_imp.CD_EMP = PDV_VD.CD_EMP
 
		   INNER JOIN PDV_VD_DEV_EST_PROD_IMP_PEND_NF ON #Prod_imp.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_PEND_NF.CD_EMP
											    AND #Prod_imp.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_PEND_NF.CD_FILIAL
											    AND #Prod_imp.CD_IMP = PDV_VD_DEV_EST_PROD_IMP_PEND_NF.CD_IMP
											    AND #Prod_imp.CD_PROD = PDV_VD_DEV_EST_PROD_IMP_PEND_NF.CD_PROD
											    AND #Prod_imp.CD_IT = PDV_VD_DEV_EST_PROD_IMP_PEND_NF.CD_IT
		   LEFT JOIN PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT_DENEGADA ON #Prod_imp.CD_EMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT_DENEGADA.CD_EMP
														AND #Prod_imp.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT_DENEGADA.CD_FILIAL
														AND #Prod_imp.CD_IMP = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT_DENEGADA.CD_IMP
														AND #Prod_imp.CD_PROD = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT_DENEGADA.CD_PROD
														AND #Prod_imp.CD_IT = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT_DENEGADA.CD_IT
		   LEFT OUTER JOIN PDV_VD_PDV_VD_NFCE ON PDV_VD.CD_EMP = PDV_VD_PDV_VD_NFCE.CD_EMP
										 AND PDV_VD.CD_FILIAL = PDV_VD_PDV_VD_NFCE.CD_FILIAL
										 AND PDV_VD.CD_VD = PDV_VD_PDV_VD_NFCE.CD_VD
		   LEFT OUTER JOIN PDV_VD_NFCE ON PDV_VD_PDV_VD_NFCE.CD_EMP = PDV_VD_NFCE.CD_EMP
								    AND PDV_VD_PDV_VD_NFCE.CD_FILIAL = PDV_VD_NFCE.CD_FILIAL
								    AND PDV_VD_PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE.CD_NFCE
 
	   ORDER BY #Prod_imp.DT_CAD,
			  PDV_VD.NR_ECF;