IF OBJECT_ID('UFD_EXP_CTB_MOV_CAIXA') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_CAIXA
	END 
GO  
-- SELECT * FROM UFD_EXP_CTB_MOV_CAIXA (1,47,'20201019','20201019')
CREATE FUNCTION UFD_EXP_CTB_MOV_CAIXA(@CD_EMP		INT,
									  @CD_FILIAL	INT,
									  @DT_INI		DATETIME,
									  @DT_FIM		DATETIME)		

RETURNS @RS_RETURN TABLE(
		CD_EMP							INT,
		CD_FILIAL						INT,
		DT_MOV							DATETIME, 
		VLR_DINH						MONEY, 
		VLR_DINH_LIQ					MONEY,		    
		VLR_CHQ_VISTA					MONEY,
		VLR_TICKETS						MONEY, 
		VLR_DEV_VD						MONEY, 
		VLR_CHQ_PRZ						MONEY, 
		VLR_DUP							MONEY,
		VLR_CONV						MONEY, 
		VLR_CONV_SEM_CAD				MONEY, 
		VLR_CONSOLIDADO					MONEY,
		VLR_LEITURA_Z					MONEY,
		VLR_DINHEIRO					MONEY, 
		VLR_SLD_INI						MONEY, 
		VLR_FUNDO_TROCO					MONEY,
		VLR_DESP						MONEY,
		VLR_DOACAO						MONEY,
		VLR_DINHEIRO_CONSD				MONEY, 
		VLR_DEV_DUP				     MONEY,    
		VLR_CARTAO_DEBITO				MONEY,
		VLR_CARTAO_CREDITO				MONEY,
		VLR_RECBTO_CREDIARIO			MONEY,
		VLR_RECBTO_CREDIARIO_CHQ_VISTA	MONEY,
		VLR_RECBTO_CREDIARIO_CHQ_PRAZO	MONEY,
		VLR_VALE_COMPRA                 MONEY,
		VLR_NCC_OUTRO_DIA               MONEY,
		VLR_RECBTO_DINH_RECARGA         MONEY,
		VLR_LINX_PROMO_N_FISCAL        MONEY,
		VLR_LINX_PROMO_FISCAL          MONEY,
		VLR_RECBTO_TERCEIROS_DINH      MONEY,
		VLR_RPS_DINH                   MONEY)
AS
BEGIN


	DECLARE @FLAG_NAO_ABATE_DEV_NO_DINHEIRO_FECHA_CAIXA INT 
	
	SET @FLAG_NAO_ABATE_DEV_NO_DINHEIRO_FECHA_CAIXA = (SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP,'NAO_ABATE_DEV_NO_DINHEIRO_FECHA_CAIXA',0,0))    

	INSERT INTO @RS_RETURN

    SELECT 
		PDV_VD_FECHA_MOV.CD_EMP,
		PDV_VD_FECHA_MOV.CD_FILIAL, 
		PDV_VD_FECHA_MOV.DT_MOV, 
		--(PDV_VD_FECHA_MOV.VLR_DINH + PDV_VD_FECHA_MOV.VLR_DEV_VD + PDV_VD_FECHA_MOV.VLR_CONV_SEM_CAD + PDV_VD_FECHA_MOV.VLR_DOACAO) - (PDV_VD_FECHA_MOV.VLR_DEV_TEF + PDV_VD_FECHA_MOV.VLR_DEV_CONV  + VLR_NCC_CANCELADA + VLR_NCC_AMORTIZADA) AS VLR_DINH, 
		--(PDV_VD_FECHA_MOV.VLR_DINH + PDV_VD_FECHA_MOV.VLR_DEV_VD + PDV_VD_FECHA_MOV.VLR_CONV_SEM_CAD + PDV_VD_FECHA_MOV.VLR_DOACAO) - (PDV_VD_FECHA_MOV.VLR_DEV_TEF + PDV_VD_FECHA_MOV.VLR_DEV_CONV + VLR_NCC_CANCELADA + VLR_NCC_AMORTIZADA) AS VLR_DINH, 
		--(PDV_VD_FECHA_MOV.VLR_DINH + PDV_VD_FECHA_MOV.VLR_DEV_VD + PDV_VD_FECHA_MOV.VLR_CONV_SEM_CAD + PDV_VD_FECHA_MOV.VLR_DOACAO) - (PDV_VD_FECHA_MOV.VLR_DEV_CONV + VLR_NCC_CANCELADA + VLR_NCC_AMORTIZADA) AS VLR_DINH, 
		 (PDV_VD_FECHA_MOV.VLR_DINH + PDV_VD_FECHA_MOV.VLR_CONV_SEM_CAD + PDV_VD_FECHA_MOV.VLR_DOACAO + PDV_VD_FECHA_MOV.VLR_CARTAO_PROPRIO) - (VLR_NCC_CANCELADA_DINH  + VLR_NCC_AMORTIZADA) AS VLR_DINH, 

		--Não abate devoluccao dinheiro
		(CASE WHEN @FLAG_NAO_ABATE_DEV_NO_DINHEIRO_FECHA_CAIXA = '1' THEN
			(PDV_VD_FECHA_MOV.VLR_DINH + VLR_NCC_NOVA_VENDA )
		ELSE
			PDV_VD_FECHA_MOV.VLR_DINH  + ((PDV_VD_FECHA_MOV.VLR_DEV_VD - (PDV_VD_FECHA_MOV.VLR_DEV_TEF + PDV_VD_FECHA_MOV.VLR_DEV_CONV )))
		END) AS VLR_DINH_LIQ,
				    
		PDV_VD_FECHA_MOV.VLR_CHQ_VISTA,
		PDV_VD_FECHA_MOV.VLR_TICKETS, 
		PDV_VD_FECHA_MOV.VLR_DEV_VD -(PDV_VD_FECHA_MOV.VLR_DEV_TEF + PDV_VD_FECHA_MOV.VLR_DEV_CONV + ISNULL(PDV_VD_FECHA_MOV.VLR_DEV_CONV_PBM,0)) AS VLR_DEV_VD, 
		PDV_VD_FECHA_MOV.VLR_CHQ_PRZ, 
		PDV_VD_FECHA_MOV.VLR_DUP,
		PDV_VD_FECHA_MOV.VLR_CONV, 
		PDV_VD_FECHA_MOV.VLR_CONV_SEM_CAD, 
		PDV_VD_FECHA_MOV.VLR_CONSOLIDADO,
		PDV_VD_FECHA_MOV.VLR_LEITURA_Z,
		PDV_VD_FECHA_MOV.VLR_DINHEIRO, 
		PDV_VD_FECHA_MOV.VLR_SLD_INI, 
		PDV_VD_FECHA_MOV.VLR_FUNDO_TROCO,
		PDV_VD_FECHA_MOV.VLR_DESP,
		PDV_VD_FECHA_MOV.VLR_DOACAO,
		(PDV_VD_FECHA_MOV.VLR_DINH + PDV_VD_FECHA_MOV.VLR_DEV_VD + PDV_VD_FECHA_MOV.VLR_CONV_SEM_CAD) AS VLR_DINHEIRO_CONSD, 
		 PDV_VD_FECHA_MOV.VLR_DEV_DUP, 
		VLR_CARTAO_DEBITO = (SELECT 
							   SUM(VLR_CARTAO) 
						   FROM 
							   V_PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO_ACUMULADO
						   WHERE 
							   PDV_VD_FECHA_MOV.CD_EMP = V_PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO_ACUMULADO.CD_EMP AND
							   PDV_VD_FECHA_MOV.CD_FILIAL = V_PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO_ACUMULADO.CD_FILIAL AND
							   PDV_VD_FECHA_MOV.DT_MOV = V_PDV_VD_FECHA_RC_ADM_CARTAO_DEBITO_ACUMULADO.DT_MOV), 

		VLR_CARTAO_CREDITO = (SELECT 
							   SUM(VLR_CARTAO) 
						   FROM 
							   V_PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO_ACUMULADO
						   WHERE 
							   PDV_VD_FECHA_MOV.CD_EMP = V_PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO_ACUMULADO.CD_EMP AND
							   PDV_VD_FECHA_MOV.CD_FILIAL = V_PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO_ACUMULADO.CD_FILIAL AND
							   PDV_VD_FECHA_MOV.DT_MOV = V_PDV_VD_FECHA_RC_ADM_CARTAO_CREDITO_ACUMULADO.DT_MOV),

		VLR_RECBTO_CREDIARIO = ISNULL((SELECT 
							   SUM(VLR_DINH) 
						   FROM 
							   PDV_CUPOM_NAO_FISCAL LEFT OUTER JOIN PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO ON 
							   PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_EMP AND
							   PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_FILIAL AND 
							   PDV_CUPOM_NAO_FISCAL.CD_CTR = PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_CTR 
						   WHERE 
							   PDV_VD_FECHA_MOV.CD_EMP = PDV_CUPOM_NAO_FISCAL.CD_EMP AND
							   PDV_VD_FECHA_MOV.CD_FILIAL = PDV_CUPOM_NAO_FISCAL.CD_FILIAL AND
							   PDV_VD_FECHA_MOV.DT_MOV = PDV_CUPOM_NAO_FISCAL.DT_CUPOM AND 
							   PDV_CUPOM_NAO_FISCAL.ST_CUPOM <> 3
							   AND NOT PDV_CUPOM_NAO_FISCAL.TP_CUPOM_NAO_FISCAL IN (3,4,5)
							   AND PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_CTR IS NULL),0),

		VLR_RECBTO_CREDIARIO_CHQ_VISTA = ISNULL((SELECT 
												   SUM(CHQ_LANC_CPL.VLR_CHQ)
												FROM
												   CHQ_LANC_CPL INNER JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO ON
												   CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP AND
												   CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_CHQ
												   INNER JOIN PRC_FILIAL ON
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = PRC_FILIAL.CD_EMP AND
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL = PRC_FILIAL.CD_FILIAL
												   INNER JOIN CHQ_EMI ON
												   CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP AND
												   CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI
												   INNER JOIN PDV_VD_FECHA_MOV_CHQ_LANC_CPL ON
												   CHQ_LANC_CPL.CD_EMP = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_EMP AND
												   CHQ_LANC_CPL.CD_CHQ = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_CHQ
												WHERE
												   CHQ_LANC_CPL.DT_EMI = CHQ_LANC_CPL.DT_VENCTO  AND
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = PDV_VD_FECHA_MOV.CD_EMP AND
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL AND
												   CHQ_LANC_CPL.DT_EMI = PDV_VD_FECHA_MOV.DT_MOV),0),

		VLR_RECBTO_CREDIARIO_CHQ_PRAZO = ISNULL((SELECT 
												   SUM(CHQ_LANC_CPL.VLR_CHQ)
												FROM
												   CHQ_LANC_CPL INNER JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO ON
												   CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP AND
												   CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_CHQ
												   INNER JOIN PRC_FILIAL ON
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = PRC_FILIAL.CD_EMP AND
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL = PRC_FILIAL.CD_FILIAL
												   INNER JOIN CHQ_EMI ON
												   CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP AND
												   CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI
												   INNER JOIN PDV_VD_FECHA_MOV_CHQ_LANC_CPL ON
												   CHQ_LANC_CPL.CD_EMP = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_EMP AND
												   CHQ_LANC_CPL.CD_CHQ = PDV_VD_FECHA_MOV_CHQ_LANC_CPL.CD_CHQ
												WHERE
												   CHQ_LANC_CPL.DT_EMI <> CHQ_LANC_CPL.DT_VENCTO  AND
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_EMP = PDV_VD_FECHA_MOV.CD_EMP AND
												   CHQ_LANC_CPL_RC_CARTAO_PROPRIO.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL AND
												   CHQ_LANC_CPL.DT_EMI = PDV_VD_FECHA_MOV.DT_MOV),0),
		
		VLR_TICKETS AS VLR_VALE_COMPRA ,
		
		VLR_NCC_OUTRO_DIA =  ISNULL((  SELECT SUM( PDV_VD_DEV_NCC.VLR_NCC)

											FROM   PDV_VD_DEV_NCC
									  INNER JOIN PDV_VD_DEV_NCC_PDV_VD
									  ON     PDV_VD_DEV_NCC.CD_EMP     = PDV_VD_DEV_NCC_PDV_VD.CD_EMP
									  AND    PDV_VD_DEV_NCC.CD_FILIAL  = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL
									  AND    PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC
									  INNER JOIN PDV_VD
									  ON     PDV_VD_DEV_NCC_PDV_VD.CD_EMP    = PDV_VD.CD_EMP
									  AND    PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL = PDV_VD.CD_FILIAL
									  AND    PDV_VD_DEV_NCC_PDV_VD.CD_VD     = PDV_VD.CD_VD
							   WHERE  PDV_VD.CD_EMP                          = @CD_EMP
							   AND    PDV_VD.CD_FILIAL                       =@CD_FILIAL
							   AND    PDV_VD.DT_VD =                         PDV_VD_FECHA_MOV.DT_MOV
							   AND    PDV_VD_DEV_NCC.STS_NCC                 = 1
							   AND    PDV_VD.ST_VD                           = 0
							   AND    PDV_VD.DT_VD                          <> PDV_VD_DEV_NCC.DT_FECHA_CX
							   AND PDV_VD_DEV_NCC.CD_EMP = PDV_VD_FECHA_MOV.CD_EMP AND
												   PDV_VD_DEV_NCC.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL ),0),

			VLR_RECBTO_DINH_RECARGA = ISNULL((SELECT 
							   SUM(VLR_DINH) 
						   FROM 
							   PDV_CUPOM_NAO_FISCAL 
						   WHERE 

							   PDV_CUPOM_NAO_FISCAL.TP_CUPOM_NAO_FISCAL = 3
							   AND PDV_CUPOM_NAO_FISCAL.ST_CUPOM NOT IN (1,2,3)
							   AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM = PDV_VD_FECHA_MOV.DT_MOV 
							   AND PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_VD_FECHA_MOV.CD_EMP
							   AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL ),0),
			
			
			VLR_LINX_PROMO_N_FISCAL = ISNULL((SELECT 
							   SUM(VALOR) 
						   FROM 
							   PDV_CUPOM_NAO_FISCAL_CARTAO_LINX_PROMO LINX_PROMO
						   WHERE 
							   LINX_PROMO.DT_VENDA = PDV_VD_FECHA_MOV.DT_MOV 
							   AND LINX_PROMO.CD_EMP = PDV_VD_FECHA_MOV.CD_EMP
							   AND LINX_PROMO.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL ),0),

			PDV_VD_FECHA_MOV.VLR_LINX_PROMO AS VLR_LINX_PROMO_FISCAL,

			VLR_RECBTO_TERCEIROS_DINH =   ISNULL((SELECT 
							   SUM(VLR_DINH) 
							   FROM PDV_CUPOM_NAO_FISCAL
							INNER JOIN PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV ON PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_EMP
								AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_FILIAL
								AND PDV_CUPOM_NAO_FISCAL.CD_CTR = PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_CTR
							INNER JOIN EST_RECB_PDV ON PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_EMP = EST_RECB_PDV.CD_EMP
								AND PDV_CUPOM_NAO_FISCAL_EST_RECB_PDV.CD_RECB_PDV = EST_RECB_PDV.CD_RECB_PDV
						   WHERE 

							   PDV_CUPOM_NAO_FISCAL.TP_CUPOM_NAO_FISCAL <> 3
							   AND PDV_CUPOM_NAO_FISCAL.ST_CUPOM NOT IN (1,2,3)
							   AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM = PDV_VD_FECHA_MOV.DT_MOV 
							   AND PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_VD_FECHA_MOV.CD_EMP
							   AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL
							   AND PDV_CUPOM_NAO_FISCAL.VLR_DINH > 0 ),0) ,
 
       VLR_RPS_DINH =    ISNULL((SELECT 
							   SUM(VLR_DINH) 
							   FROM PDV_CUPOM_NAO_FISCAL
						   WHERE 

							   PDV_CUPOM_NAO_FISCAL.TP_CUPOM_NAO_FISCAL = 7
							   AND ST_CUPOM = 0
							   AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM = PDV_VD_FECHA_MOV.DT_MOV 
							   AND PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_VD_FECHA_MOV.CD_EMP
							   AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL
							   AND PDV_CUPOM_NAO_FISCAL.VLR_DINH > 0 ),0) 


		FROM PDV_VD_FECHA_MOV

		WHERE	PDV_VD_FECHA_MOV.CD_EMP			=  @CD_EMP
				AND PDV_VD_FECHA_MOV.CD_FILIAL	=  @CD_FILIAL
				AND PDV_VD_FECHA_MOV.DT_MOV		BETWEEN  @DT_INI AND @DT_FIM

	
	RETURN 
				
END				