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.VLR_IT_BRUTO * PDV_VD_DEV_EST_PROD_IMP.QT_IT - ISNULL(((PDV_VD_DEV_EST_PROD_IMP.VLR_IT_BRUTO * PDV_VD_DEV_EST_PROD_IMP.QT_IT) * PDV_VD_DEV_EST_PROD_IMP.PERC_DESC_IT / 100),0)) 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;
