/****** Object:  UserDefinedFunction [dbo].[UFD_EST_PROD_TRIB]    Script Date: 04/29/2014 23:12:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_PESQ_CLI_PDV_ON_LINE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[UFD_PESQ_CLI_PDV_ON_LINE]
GO 
/****** Object:  UserDefinedFunction [dbo].[UFD_PESQ_CLI_PDV_ON_LINE]    Script Date: 02/28/2012 17:28:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[UFD_PESQ_CLI_PDV_ON_LINE](
                                      @CD_EMP INT,
                                      @CD_BARRA NVARCHAR(100),
                                      @NR_FATURA NVARCHAR(20),
                                      @CD_CLI INT,
								      @RZ_CLI NVARCHAR(100),		
                                      @NR_CPF NVARCHAR(18),
                                      @NR_CARTAO_FIDE NVARCHAR(18),
                                      @DS_TIPO NVARCHAR(18)
                                      ) 

RETURNS @TBL_CLI TABLE (
						CD_EMP				INT,
						CD_CLI				INT,
						RZ_CLI				NVARCHAR(100),
					    CGC_CPF				NVARCHAR(20),
					    NR_CARTAO			NVARCHAR(30),
					    NR_CARTAO_ANTIGO	NVARCHAR(30),
					    END_CLI				NVARCHAR(200)
					   )
AS

BEGIN
	DECLARE @RS_CLI TABLE (CD_EMP INT, CD_CLI INT);

	IF @DS_TIPO = 'NR_FATURA' 
		BEGIN 
			INSERT INTO @RS_CLI
			SELECT @CD_EMP , CD_CLI 
			FROM RC_DEB 
			WHERE 
				CD_EMP = @CD_EMP 
				AND NR_FATURA = @NR_FATURA;
		END 
	IF @DS_TIPO = 'COD_BARRA' 
		BEGIN 
			INSERT INTO @RS_CLI
			SELECT 
				@CD_EMP , CD_CLI 
			FROM RC_DEB 
			WHERE 
				CD_EMP = @CD_EMP 
				AND CD_BARRAS_BOLETA = @CD_BARRA
		END
		 
	IF @DS_TIPO = 'COD_CLI' 
	BEGIN 
		INSERT INTO @RS_CLI
		SELECT 
			@CD_EMP CD_EMP,
			CD_CLI 
		FROM 
			RC_CLI
		WHERE
			CD_CLI   = @CD_CLI
			AND CD_EMP=@CD_EMP 
	END

	IF @DS_TIPO = 'RZ_CLI' 
	BEGIN 
		INSERT INTO @RS_CLI
		SELECT TOP 10
			@CD_EMP CD_EMP,
			CD_CLI 
		FROM 
			RC_CLI
		WHERE
			 RZ_CLI LIKE  @RZ_CLI + '%'
			 AND CD_EMP=@CD_EMP 
	END

	IF @DS_TIPO = 'NR_CPF' 
	BEGIN 
		INSERT INTO @RS_CLI
		SELECT TOP 20
			@CD_EMP CD_EMP,
			CD_CLI 
		FROM 
			RC_CLI
		WHERE
			 CGC_CPF   = @NR_CPF
			 AND CD_EMP=@CD_EMP 
	END

	IF @DS_TIPO = 'NR_CAR_NO' 
	BEGIN 
		INSERT INTO @RS_CLI
		SELECT 
			@CD_EMP CD_EMP,
			CAR.CD_CLI 
		FROM 
			RC_CLI_FIDELI_CARTAO CAR
		WHERE
			CAR.NR_CARTAO = @NR_CARTAO_FIDE
			AND CAR.CD_EMP=@CD_EMP 
	END

	IF @DS_TIPO = 'NR_CAR_AN' 
	BEGIN 
		INSERT INTO @RS_CLI
		SELECT 
			@CD_EMP CD_EMP,
			CD_CLI 
		FROM 
			RC_CLI
		WHERE
			NR_CARTAO_ANTIGO = @NR_CARTAO_FIDE
			AND CD_EMP=@CD_EMP 
	END 


	INSERT INTO @TBL_CLI
	SELECT 
		CLI.CD_EMP,
		CLI.CD_CLI,
		CLI.RZ_CLI,
		CGC_CPF,
		CAR.NR_CARTAO,
		CLI.NR_CARTAO_ANTIGO,
		(CLI.END_CLI + ' ' +  CLI.BAIRRO + ' ' + GLB_CID.DS_CID + '-' + GLB_CID.UF) AS END_CLI
	FROM 
		RC_CLI CLI 
		INNER JOIN @RS_CLI B ON 
		CLI.CD_EMP	= B.CD_EMP AND 
		CLI.CD_CLI	= B.CD_CLI
		INNER JOIN GLB_CID ON
		CLI.CD_CID = GLB_CID.CD_CID  
		LEFT JOIN RC_CLI_FIDELI_CARTAO CAR ON
		CLI.CD_EMP		= CAR.CD_EMP
		AND CLI.CD_CLI	= CAR.CD_CLI
	WHERE  
		CLI.CD_EMP		= @CD_EMP 

RETURN 
END

GO


