
IF OBJECT_ID('UFD_EXP_CTB_MOV_VENDA_CONV') IS NOT NULL
    BEGIN
        DROP FUNCTION UFD_EXP_CTB_MOV_VENDA_CONV;
END;
GO	
-- SELECT * FROM UFD_EXP_CTB_MOV_VENDA_CONV (1,2,'20200103','20200103')
CREATE FUNCTION UFD_EXP_CTB_MOV_VENDA_CONV (@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,
 CD_CONV        INT,
 DS_CONV        VARCHAR(100),
 VLR_CONV       MONEY,
 CD_CTB_CNT_RED INT,
 CD_CTB_CNT     VARCHAR(50)
)
AS
     BEGIN

         INSERT INTO @RS_RETURN
                SELECT DISTINCT
                       dbo.PDV_VD.CD_EMP,
                       dbo.PDV_VD.CD_FILIAL,
                       dbo.PDV_VD.DT_VD,
                       ISNULL(dbo.RC_VD_CONV.CD_CONV, 0) AS CD_CONV,
                       ISNULL(DS_CONV, 'VENDA SEM VINCULO COM CONVENIO, MAS VENDIDO COMO CONVENIO') AS DS_CONV,
                       --SUM(dbo.RC_VD_CONV.VLR_CONV + ISNULL(PDV_VD.VLR_CONV_PBM, 0)) AS VLR_CONV,
					   SUM(dbo.RC_VD_CONV.VLR_CONV ) AS VLR_CONV,
                       dbo.RC_CLI_CONV.CD_CTB_CNT_RED,
                       dbo.RC_CLI_CONV.CD_CTB_CNT
                FROM dbo.PDV_VD
                     INNER JOIN PDV_VD_RC_VD_CONV ON PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
                                                     AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
                                                     AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
                     INNER JOIN dbo.RC_VD_CONV ON dbo.PDV_VD_RC_VD_CONV.CD_EMP = dbo.RC_VD_CONV.CD_EMP
                                                  AND dbo.PDV_VD_RC_VD_CONV.CD_CTR = dbo.RC_VD_CONV.CD_CTR
                     INNER JOIN dbo.RC_CLI_CONV ON dbo.RC_VD_CONV.CD_EMP = dbo.RC_CLI_CONV.CD_EMP
                                                   AND dbo.RC_VD_CONV.CD_CONV = dbo.RC_CLI_CONV.CD_CONV
                WHERE PDV_VD.ST_VD = 0
                      AND PDV_VD.VLR_CONV > 0
                      AND PDV_VD.CD_EMP = @CD_EMP
                      AND PDV_VD.CD_FILIAL = @CD_FILIAL
                      AND PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM

                GROUP BY PDV_VD.CD_EMP,
                         PDV_VD.CD_FILIAL,
                         PDV_VD.DT_VD,
                         RC_VD_CONV.CD_CONV,
                         DS_CONV,
                         RC_CLI_CONV.CD_CTB_CNT_RED,
                         RC_CLI_CONV.CD_CTB_CNT;

                INSERT INTO @RS_RETURN
				SELECT DISTINCT 
                        PDV_VD.CD_EMP,
                        PDV_VD.CD_FILIAL,
                        PDV_VD.DT_VD,
                        --ISNULL(dbo.RC_CLI_CONV_RC_CLI.CD_CONV, 0) AS CD_CONV,
						ISNULL(dbo.RC_CLI_CONV_VINC_RC_CLI.CD_CONV, 0) AS CD_CONV,
                        ISNULL(DS_CONV, 'VENDA SEM VINCULO COM CONVENIO, MAS VENDIDO COMO CONVENIO') AS DS_CONV,
                         SUM( ISNULL(PDV_VD_IT_VD_PBM.VLR_CONVENIO, 0) + ISNULL(PDV_VD_IT_VD_PBM.VLR_REEMBOL, 0)) AS VLR_CONV ,
                        RC_CLI_CONV.CD_CTB_CNT_RED,
                        RC_CLI_CONV.CD_CTB_CNT
				  FROM PDV_VD  
				       INNER JOIN PDV_VD_IT_VD_PBM ON PDV_VD_IT_VD_PBM.CD_EMP        = PDV_VD.CD_EMP
				    							  AND PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD.CD_FILIAL
				    							  AND PDV_VD_IT_VD_PBM.CD_VD     = PDV_VD.CD_VD
												 
				       INNER JOIN dbo.RC_CLI AS RC_CLI ON PDV_VD_IT_VD_PBM.CD_EMP     = RC_CLI.CD_EMP
				    								  AND PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI.CD_CLI
				    
				       LEFT OUTER JOIN dbo.RC_CLI_CONV_VINC_RC_CLI ON RC_CLI.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP
				    									     AND RC_CLI.CD_CLI = RC_CLI_CONV_VINC_RC_CLI.CD_CLI
				    
				       LEFT OUTER JOIN RC_CLI_CONV AS RC_CLI_CONV ON RC_CLI_CONV_VINC_RC_CLI.CD_EMP      = RC_CLI_CONV.CD_EMP
				    											 AND RC_CLI_CONV_VINC_RC_CLI.CD_CONV = RC_CLI_CONV.CD_CONV
				    
				       INNER JOIN dbo.PRC_FILIAL AS PRC_FILIAL ON PDV_VD_IT_VD_PBM.CD_EMP = PRC_FILIAL.CD_EMP
				    									      AND PDV_VD_IT_VD_PBM.CD_FILIAL = PRC_FILIAL.CD_FILIAL
                 WHERE PDV_VD.ST_VD = 0
                   AND PDV_VD.CD_EMP = @CD_EMP
                   AND PDV_VD.CD_FILIAL = @CD_FILIAL
                   AND PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM
                   AND ISNULL(PDV_VD.VLR_CONV_PBM, 0) > 0
                
                 GROUP BY PDV_VD.CD_EMP,
                          PDV_VD.CD_FILIAL,
						  PDV_VD.DT_VD,
                          RC_CLI_CONV_VINC_RC_CLI.CD_CONV,
                          DS_CONV,
                          RC_CLI_CONV.CD_CTB_CNT_RED,
                          RC_CLI_CONV.CD_CTB_CNT;

		  delete from @RS_RETURN where vlr_conv =0
         RETURN;
     END;    