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

ALTER 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

	IF @DS_TIPO = 'NR_FATURA' 
		BEGIN
			SET @DS_TIPO = 'COD_CLI'
			SET @CD_CLI =  (SELECT CD_CLI 
							FROM RC_DEB 
							WHERE CD_EMP = @CD_EMP 
									AND NR_FATURA = @NR_FATURA)
		END
				
	IF @DS_TIPO = 'COD_BARRA' 
		BEGIN
			SET @DS_TIPO = 'COD_CLI'
			SET @CD_CLI =  (SELECT CD_CLI 
							FROM RC_DEB 
							WHERE CD_EMP = @CD_EMP 
									AND CD_BARRAS_BOLETA = @CD_BARRA)
		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 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
		AND ((CASE WHEN @DS_TIPO = 'COD_CLI' THEN CLI.CD_CLI END) = @CD_CLI
		OR (CASE WHEN @DS_TIPO = 'RZ_CLI'	THEN CLI.RZ_CLI END) LIKE @RZ_CLI
		OR (CASE WHEN @DS_TIPO = 'NR_CPF'	THEN CGC_CPF END) = @NR_CPF
		OR (CASE WHEN @DS_TIPO = 'NR_CAR_NO' THEN CAR.NR_CARTAO END) = @NR_CARTAO_FIDE
		OR (CASE WHEN @DS_TIPO = 'NR_CAR_AN' THEN CLI.NR_CARTAO_ANTIGO END) = @NR_CARTAO_FIDE)

RETURN 
END

GO


