IF OBJECT_ID('UFD_EXP_CTB_MOV_DOACAO') IS NOT NULL
	BEGIN
		DROP FUNCTION UFD_EXP_CTB_MOV_DOACAO
	END
	
GO	

   
CREATE FUNCTION UFD_EXP_CTB_MOV_DOACAO(    @CD_EMP  INT,  
             @CD_FILIAL INT,  
             @DT_INI  DATETIME,  
             @DT_FIM  DATETIME)    
  
RETURNS @RS_RETURN TABLE(  
  CD_EMP    INT,   
  CD_FILIAL   INT,     
  DT_CUPOM   DATETIME,      
  CD_ENTID	 INT,  
  NM_ENTID   VARCHAR(100),   
  CD_CTB_CNT   VARCHAR(100),   
  CD_CTB_CNT_RED  VARCHAR(100),   
  VLR_DINHEIRO  MONEY,
  VLR_CARTAO  MONEY,
  CD_CLI	INT)
AS  
BEGIN         
  
 INSERT INTO @RS_RETURN    
  SELECT   
   PDV_CUPOM_NAO_FISCAL.CD_EMP,  
   PDV_CUPOM_NAO_FISCAL.CD_FILIAL,  
   PDV_CUPOM_NAO_FISCAL.DT_CUPOM,  
   EST_ENTID_DOACAO.CD_ENTID,  
   EST_ENTID_DOACAO.NM_ENTID,   
   EST_ENTID_DOACAO.CD_CTB_CNT,   
   EST_ENTID_DOACAO.CD_CTB_CNT_RED,  
   SUM(PDV_CUPOM_NAO_FISCAL.VLR_DINH) AS VLR_DINHEIRO,
   SUM(PDV_CUPOM_NAO_FISCAL.VLR_CARTAO) AS VLR_CARTAO 
  ,CASE WHEN ISNULL(RC_ADM_CARTAO_RC_CLI.CD_CLI,0) = 0  THEN  ISNULL( CLI_PDV.CD_CLI,0) ELSE ISNULL(RC_ADM_CARTAO_RC_CLI.CD_CLI,0) END  AS CD_CLI
  
  FROM PDV_CUPOM_NAO_FISCAL   
   INNER 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   
   INNER JOIN EST_ENTID_DOACAO ON   
    PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_EMP = EST_ENTID_DOACAO.CD_EMP AND  
    PDV_CUPOM_NAO_FISCAL_EST_ENTID_DOACAO.CD_ENTID = EST_ENTID_DOACAO.CD_ENTID  
 				 
   LEFT JOIN PDV_VD_TEF ON  PDV_CUPOM_NAO_FISCAL.CD_EMP = PDV_VD_TEF.CD_EMP 
   AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL= PDV_VD_TEF.CD_FILIAL
   AND PDV_CUPOM_NAO_FISCAL.CD_VD_DOACAO = PDV_VD_TEF.CD_VD

   LEFT JOIN PDV_CUPOM_NAO_FISCAL_TEF 
   ON   PDV_CUPOM_NAO_FISCAL_TEF.CD_EMP = PDV_CUPOM_NAO_FISCAL.CD_EMP AND 
			 PDV_CUPOM_NAO_FISCAL_TEF.CD_FILIAL = PDV_CUPOM_NAO_FISCAL.CD_FILIAL AND 
			 PDV_CUPOM_NAO_FISCAL_TEF.CD_CTR = PDV_CUPOM_NAO_FISCAL.CD_CTR
			 
	LEFT JOIN PDV_RECBTO_TEF ON
	PDV_RECBTO_TEF.CD_EMP = PDV_CUPOM_NAO_FISCAL_TEF.CD_EMP AND 
	PDV_RECBTO_TEF.CD_FILIAL = PDV_CUPOM_NAO_FISCAL_TEF.CD_FILIAL AND 
	PDV_RECBTO_TEF.CD_RECTO_TEF = PDV_CUPOM_NAO_FISCAL_TEF.CD_RECTO_TEF

	LEFT JOIN RC_ADM_CARTAO_RC_CLI ON 
				 PDV_RECBTO_TEF.CD_EMP = RC_ADM_CARTAO_RC_CLI.CD_EMP AND 
				 PDV_RECBTO_TEF.CD_ADM_CARTAO = RC_ADM_CARTAO_RC_CLI.CD_ADM_CARTAO

	LEFT JOIN RC_ADM_CARTAO_RC_CLI CLI_PDV ON 
				 PDV_VD_TEF.CD_EMP = CLI_PDV.CD_EMP AND 
				 PDV_VD_TEF.CD_ADM_CARTAO = CLI_PDV.CD_ADM_CARTAO   
 
     WHERE PDV_CUPOM_NAO_FISCAL.CD_EMP  = @CD_EMP  
     AND PDV_CUPOM_NAO_FISCAL.CD_FILIAL = @CD_FILIAL  
     AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM BETWEEN @DT_INI AND @DT_FIM 
       
  GROUP BY   
   PDV_CUPOM_NAO_FISCAL.CD_EMP,  
   PDV_CUPOM_NAO_FISCAL.CD_FILIAL,  
   PDV_CUPOM_NAO_FISCAL.DT_CUPOM,  
   EST_ENTID_DOACAO.CD_ENTID,
   EST_ENTID_DOACAO.NM_ENTID,   
   EST_ENTID_DOACAO.CD_CTB_CNT,   
   EST_ENTID_DOACAO.CD_CTB_CNT_RED 
   ,RC_ADM_CARTAO_RC_CLI.CD_CLI  
   ,CLI_PDV.CD_CLI      
     
 RETURN    
      
END   