IF OBJECT_ID('P_CONSULTA_NUMERACAO_CUPONS') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_CONSULTA_NUMERACAO_CUPONS
	END
 
GO 
CREATE PROCEDURE [dbo].[P_CONSULTA_NUMERACAO_CUPONS] (@CD_EMP INT, @CD_FILIAL INT, @DATA DATETIME, @CD_PROD VARCHAR(50) = NULL) AS
BEGIN

	SET NOCOUNT ON;

	SELECT *
	FROM (
			SELECT DISTINCT PDV_VD.CD_VD,
							PDV_VD.DT_VD,
							PDV_VD.NR_ECF,
							PDV_VD.VLR_LIQ_VD,
							ISNULL(PDV_VD_IT.VLR_FRETE_RAT, 0) AS VLR_FRETE_RAT,
							PDV_VD.VLR_DUP,
							PDV_VD.NR_ORC,
							PDV_VD.VLR_DINH,
							PDV_VD.VLR_CHQS,
							ISNULL(PDV_VD.VLR_NCC, 0) AS VLR_NCC,
							PDV_VD.VLR_CARTAO,
							PDV_VD.VLR_TICKETS,
							PDV_VD.VLR_CONV,
							PDV_VD.VLR_TROCO_CHQ,
							PDV_VD.ST_VD,
							ISNULL(RS.QTDE, 0) AS QTDE,
							EST_ORC.DT_EMI AS DT_ORC,
							PDV_VD.HR_VD,
							ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM,
							PDV_VD.VLR_SAQUE_CARTAO,
							0 AS TP_CUPOM,
							PDV_VD.CD_CX,
							COALESCE(PDV_VD_NFCE.CH_NFE, '') AS CH_NFE,
							PDV_VD.DT_CONSD,
							COALESCE(SIT_IT_CANC, 'Não') AS SIT_IT_CANC,
							pdv_vd.CD_USU_OPE
			FROM DBO.PDV_VD WITH(NOLOCK)
			INNER JOIN
			  (SELECT PDV_VD_IT.Cd_Emp,
					  PDV_VD_IT.CD_FILIAL,
					  PDV_VD_IT.CD_VD,
					  VLR_FRETE_RAT = SUM(ISNULL(PDV_VD_IT.VLR_FRETE_RAT, 0))
			   FROM DBO.PDV_VD_IT WITH(NOLOCK)
			   WHERE PDV_VD_IT.CD_EMP = @CD_EMP
			   AND PDV_VD_IT.DT_VD = @DATA
			   AND PDV_VD_IT.CD_FILIAL = @CD_FILIAL
			   AND PDV_VD_IT.CD_PROD = CASE WHEN ISNULL(@CD_PROD,'') <> '' THEN @CD_PROD ELSE PDV_VD_IT.CD_PROD END
			   GROUP BY PDV_VD_IT.Cd_Emp,
						PDV_VD_IT.CD_FILIAL,
						PDV_VD_IT.CD_VD) AS PDV_VD_IT ON PDV_VD_IT.CD_EMP = PDV_VD.CD_EMP
			AND PDV_VD_IT.CD_FILIAL = PDV_VD.CD_FILIAL
					AND PDV_VD_IT.CD_VD = PDV_VD.CD_VD
			LEFT OUTER JOIN DBO.EST_ORC WITH (NOLOCK) ON PDV_VD.CD_EMP = EST_ORC.CD_EMP
			AND PDV_VD.CD_FILIAL = EST_ORC.CD_FILIAL
			AND PDV_VD.NR_ORC = EST_ORC.CD_ORC
			LEFT OUTER JOIN DBO.PDV_VD_PDV_VD_NFCE WITH (NOLOCK) 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 DBO.PDV_VD_NFCE WITH (NOLOCK) ON PDV_VD_PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE.CD_NFCE
			LEFT OUTER JOIN
			  (SELECT CD_EMP,
					  CD_FILIAL,
					  NR_ECF,
					  CD_CX,
					  COUNT(NR_ECF) AS QTDE
			   FROM DBO.PDV_VD WITH (NOLOCK)
			   WHERE CD_EMP =@CD_EMP
				 AND CD_FILIAL =@CD_FILIAL
				 AND DT_VD BETWEEN @DATA AND @DATA
				 AND ST_VD = 0
			   GROUP BY NR_ECF,
						CD_CX,
						CD_FILIAL,
						CD_EMP,
						VLR_SAQUE_CARTAO
			   HAVING COUNT(NR_ECF) > 1) Rs ON PDV_VD.CD_EMP = Rs.CD_EMP
			AND PDV_VD.CD_FILIAL = Rs.CD_FILIAL
			AND PDV_VD.NR_ECF = Rs.NR_ECF
			AND PDV_VD.CD_CX = Rs.CD_CX OUTER APPLY
			  (SELECT DISTINCT CD_EMP, CD_FILIAL, CD_VD, 'Sim' AS SIT_IT_CANC
			   FROM PDV_VD_IT IT
			   WHERE IT.CD_EMP= PDV_VD.cd_emp
				 AND IT.CD_FILIAL=PDV_VD.cd_filial
				 AND IT.CD_VD = PDV_VD.CD_VD
				 AND IT.ST_IT=2) IT_CANC
			WHERE PDV_VD.CD_EMP =@CD_EMP
			  AND PDV_VD.CD_FILIAL =@CD_FILIAL
			  AND PDV_VD.DT_VD BETWEEN @DATA AND @DATA

			UNION ALL

			SELECT 
			  CD_CTR, 
			  DT_CUPOM, 
			  NR_COO, 
			  VLR_TOT_CUPOM, 
			  0 AS VLR_FRETE_RAT, 
			  0 AS VLR_DUP, 
			  0 AS NR_ORC, 
			  VLR_DINH, 
			  VLR_CHQS, 
			  0 AS VLR_NCC, 
			  VLR_CARTAO,
			  0 AS VLR_TICKETS, 
			  0 AS VLR_CONV, 
			  0 AS VLR_TROCO_CHQ, 
			  ST_CUPOM, 
			  0 AS QTDE, 
			  NULL AS DT_ORC, 
			  HR_CUPOM, 
			  0 AS VLR_CONV_PBM, 
			  0 AS VLR_SAQUE_CARTAO,
			  1 AS TP_CUPOM, CD_CX,
			  '' AS CH_NFE,PDV_CUPOM_NAO_FISCAL.DT_CONSD
			  ,'Não' AS SIT_IT_CANC
			  , PDV_CUPOM_NAO_FISCAL.CD_USU
			FROM 
			  PDV_CUPOM_NAO_FISCAL  WITH (NOLOCK) 
			WHERE 
			  CD_EMP =  @CD_EMP
			  AND CD_FILIAL = @CD_FILIAL
			  AND DT_CUPOM BETWEEN @DATA AND @DATA
	) C
	ORDER BY DT_VD,
			 NR_ECF

END 
