IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_GLB_PESSOA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_GLB_PESSOA]
GO 

CREATE FUNCTION UFD_GLB_PESSOA (@CD_PESSOA INT, @TP_PESSOA INT)
 
RETURNS @RS_RETORNO TABLE ( CD_EMP					INT,
							CD_PESSOA				INT,
							RAZAO_SOCIAL_PESSOA		VARCHAR(250) collate SQL_Latin1_General_CP1_CI_AS,
							NOME_FANTASIA_PESSOA	VARCHAR(250) collate SQL_Latin1_General_CP1_CI_AS,	
							CGC_CPF					VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS,
							ENDERECO_PESSOA			VARCHAR(250) collate SQL_Latin1_General_CP1_CI_AS,
							NUMERO_ENDERECO_PESSOA	VARCHAR(250) collate SQL_Latin1_General_CP1_CI_AS,
							BAIRRO_ENDERECO_PESSOA	VARCHAR(250) collate SQL_Latin1_General_CP1_CI_AS,
							CEP_ENDERECO_PESSOA		VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS,
							TELEFONE_PESSOA			VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS,
							CD_CID					INT,
							DS_CID					VARCHAR(150) collate SQL_Latin1_General_CP1_CI_AS,
							UF						VARCHAR(2) collate SQL_Latin1_General_CP1_CI_AS,
							COD_IBGE_MUNICIPIO INT,	 
							INSC_EST				VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS,
							INSC_MUNI				VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS,
							FLAG_ICMS_SUBST_PAGO_ANTERIOR  INT,	
							FLAG_VALIDA_NFE					INT,
							TP_SUBS_TRIB					INT,
							SUBS_TRIB						INT,
							DS_TIPO_PESSOA					VARCHAR(100) collate SQL_Latin1_General_CP1_CI_AS,
							CD_TIPO_PESSOA					INT,
							FLAG_SIMPLES_NACIONAL			INT,
							QTDE_CASAS_DECIMAIS_NOTA		INT,
							TP_LAYOUT_EXP_ENT				INT,
							EXPORT_NF_ESCRITA				INT,
							ENVIAR_XML_NFE					INT,
							EMAIL_PESSOA					VARCHAR(150) collate SQL_Latin1_General_CP1_CI_AS,
							DEST_ORIG						INT,
							CONTRIBUINTE					INT,
							PRECO_BASE_SUBS					INT,
							CD_OPER_MVA						INT,
							DS_OPER_MVA						VARCHAR(150),
							AR_MERCANTIL					INT,
							COMPLEMENTO_RESIDENCIAL         VARCHAR(150),
							-- FLAG ADICIONADA
							FLAG_NAO_ABATER_ICMS_DESONERADO TINYINT,
							CONSUMIDOR_FINAL                INT)
	 
WITH ENCRYPTION   AS
BEGIN 

IF @TP_PESSOA =0 -- CLIENTE
BEGIN
	INSERT INTO @RS_RETORNO
	SELECT top 1
		RC_CLI.CD_EMP,
		RC_CLI.CD_CLI AS CD_PESSOA,
		RZ_CLI AS RAZAO_SOCIAL_PESSOA,
		NM_FANT AS NOME_FANTASIA_PESSOA,
		CGC_CPF,
		END_CLI AS ENDERECO_PESSOA,
		RC_CLI.NUMERO AS NUMERO_ENDERECO_PESSOA,
		BAIRRO AS BAIRRO_ENDERECO_PESSOA,
		RC_CLI.CEP AS CEP_ENDERECO_PESSOA,
		TEL AS TELEFONE_PESSOA,
		GLB_CID.CD_CID,
		GLB_CID.DS_CID,
		GLB_CID.UF,
		ISNULL(COD_MUNICIPIO_SINTEGRA,0) AS COD_IBGE_MUNICIPIO, 
		CASE WHEN ISNULL(RC_CLI.CONTRIBUINTE,0)<>9 THEN 
			ISNULL(INSC_EST,'') 
		ELSE
			''
		END AS INSC_EST,
		ISNULL(INSC_MUNI,'') AS INSC_MUNI,
		0 AS FLAG_ICMS_SUBST_PAGO_ANTERIOR,
		0 AS FLAG_VALIDA_NFE,
		0 AS TP_SUBS_TRIB,
		0 AS SUBS_TRIB,
		'CLIENTE' AS DS_TIPO_PESSOA,
		0 AS CD_TIPO_PESSOA,
		0 AS FLAG_SIMPLES_NACIONAL,
		4 AS QTDE_CASAS_DECIMAIS_NOTA,
		0 AS TP_LAYOUT_EXP_ENT,
		0 AS EXPORT_NF_ESCRITA,
		ISNULL(ENVIAR_XML_NFE,0) AS ENVIAR_XML_NFE,
		ISNULL(EMAIL, '') AS EMAIL_PESSOA,
		0 AS DEST_ORIG,
		CASE WHEN ISNULL(RC_CLI.CONTRIBUINTE,0)=0 THEN 
		   (CASE WHEN ISNULL(INSC_EST,'') IN ('ISENTO','ISENTA') THEN
				2
			ELSE		
				(CASE WHEN PESSOA =1 THEN 
					1 
				 ELSE 
					9 
				END)
		   END)
		ELSE
			ISNULL(RC_CLI.CONTRIBUINTE,0)
		END AS CONTRIBUINTE,
		0 AS PRECO_BASE_SUBS,
		ISNULL(RC_CLI.CD_OPER_MVA,0) AS CD_OPER_MVA,
		ISNULL(EST_TRIB_OPER_MVA.DS_OPER_MVA,'') AS DS_OPER_MVA,
		0 AS AR_MERCANTIL,
		RC_CLI.COMPLEMENTO_RESIDENCIAL as COMPLEMENTO_RESIDENCIAL,
		-- TRAZER APENAS O ICMS ZERADO QUANDO FOR O TIPO 'CLIENTE'
		0 AS FLAG_NAO_ABATER_ICMS_DESONERADO,
		ISNULL(CONS_FINAL,0)CONSUMIDOR_FINAL
	FROM
		RC_CLI 
		INNER JOIN GLB_CID ON
		RC_CLI.CD_CID = GLB_CID.CD_CID 
		LEFT OUTER JOIN GLB_CID_GLB_MUNICIPIO ON
		GLB_CID.CD_CID = GLB_CID_GLB_MUNICIPIO.CD_CID 
		LEFT OUTER JOIN GLB_MUNICIPIO ON
		GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO = GLB_MUNICIPIO.CD_MUNICIPIO
		LEFT OUTER JOIN EST_TRIB_OPER_MVA ON
		RC_CLI.CD_OPER_MVA = EST_TRIB_OPER_MVA.CD_OPER_MVA
	WHERE
		RC_CLI.CD_EMP =1
		AND RC_CLI.CD_CLI =@CD_PESSOA
	RETURN;
END


IF @TP_PESSOA =  1 -- FORNECEDOR
BEGIN
	INSERT INTO @RS_RETORNO
	SELECT top 1
		PG_FORN.CD_EMP,
		PG_FORN.CD_FORN,
		PG_FORN.RZ_FORN,
		PG_FORN.NM_FANT,
		PG_FORN.CGC_CPF,	
		END_FORN  collate SQL_Latin1_General_CP1_CI_AS,
		ISNULL(PG_FORN.NR_END_FORN, '') AS NUMERO,
		BAIRRO_FORN,
		PG_FORN.CEP,
		TEL,
		GLB_CID.CD_CID,
		GLB_CID.DS_CID,
		GLB_CID.UF,
		ISNULL(COD_MUNICIPIO_SINTEGRA,0) AS COD_IBGE_MUNICIPIO, 
		CASE WHEN ISNULL(PG_FORN.CONTRIBUINTE,0)<>9 THEN 
			ISNULL(INSC_EST,'') 
		ELSE
			''
		END AS INSC_EST,
		ISNULL(INSC_MUNI,'') AS INSC_MUNI,
		FLAG_ICMS_SUBST_PAGO_ANTERIOR,
		FLAG_VALIDA_NFE,
		ISNULL(TP_SUBS_TRIB,0),
		ISNULL(SUBS_TRIB,0),
		DS_TP AS DS_TIPO_PESSOA,
		PG_FORN.CD_TP_FORN,
		ISNULL(FLAG_FORN_MICRO_EMPRESA,0),
		CASE WHEN ISNULL(QTDE_CASAS_DECIMAIS_NOTA,4) = 0 THEN 4 ELSE ISNULL(QTDE_CASAS_DECIMAIS_NOTA,4) END  AS QTDE_CASAS_DECIMAIS_NOTA,
		ISNULL(TP_LAYOUT_EXP_ENT,0) AS TP_LAYOUT_EXP_ENT,
		ISNULL(EXPORT_NF_ESCRITA,0) AS EXPORT_NF_ESCRITA,
		ISNULL(ENVIAR_XML_NFE,0) AS ENVIAR_XML_NFE,
		ISNULL(EMAIL, '') AS EMAIL_PESSOA,
		1 AS DEST_ORIG,
		CASE WHEN ISNULL(PG_FORN.CONTRIBUINTE,0)=0 THEN 
			(CASE WHEN ISNULL(INSC_EST,'') IN ('ISENTO','ISENTA') THEN
				2
			ELSE		
				(CASE WHEN PESSOA= 1 THEN 1 
					ELSE  9  END)
			END) 
		ELSE  ISNULL(PG_FORN.CONTRIBUINTE,0)  END  CONTRIBUINTE,
		PG_FORN.PRECO_BASE_SUBS,
		ISNULL(PG_FORN.CD_OPER_MVA,0) AS CD_OPER_MVA,
		ISNULL(EST_TRIB_OPER_MVA.DS_OPER_MVA,'') AS DS_OPER_MVA,
		PG_FORN.AR_MERCANTIL,
		'' as COMPLEMENTO_RESIDENCIAL,
		--TRAZER O VALOR DE PG_FORN
		ISNULL(PG_FORN.FLAG_NAO_ABATER_ICMS_DESONERADO,0) AS FLAG_NAO_ABATER_ICMS_DESONERADO,
		0 CONSUMIDOR_FINAL
	FROM
		PG_FORN INNER JOIN GLB_CID ON
		PG_FORN.CD_CID = GLB_CID.CD_CID 
		LEFT OUTER JOIN GLB_CID_GLB_MUNICIPIO ON
		GLB_CID.CD_CID = GLB_CID_GLB_MUNICIPIO.CD_CID 
		LEFT OUTER JOIN GLB_MUNICIPIO ON
		GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO = GLB_MUNICIPIO.CD_MUNICIPIO
		LEFT OUTER JOIN PG_TP_FORN ON
		PG_FORN.CD_TP_FORN = PG_TP_FORN.CD_TP_FORN
		LEFT OUTER JOIN EST_TRIB_OPER_MVA ON
		PG_FORN.CD_OPER_MVA = EST_TRIB_OPER_MVA.CD_OPER_MVA
	WHERE
		PG_FORN.CD_EMP =1
		AND PG_FORN.CD_FORN =@CD_PESSOA
		RETURN;
	END
	 
IF @TP_PESSOA =2 --filial
BEGIN
	
	INSERT INTO @RS_RETORNO
		SELECT top 1
		PRC_FILIAL.CD_EMP,
		PRC_FILIAL.CD_FILIAL,
		RZ_FILIAL,
		NM_FANT,
		CGC,	
		PRC_FILIAL.END_FILILAL,
		ISNULL(PRC_FILIAL.NUMERO,'') 				AS NUMERO,
		BAIRRO,
		PRC_FILIAL.CEP,
		TEL,
		GLB_CID.CD_CID,
		GLB_CID.DS_CID,
		GLB_CID.UF,
		ISNULL(COD_MUNICIPIO_SINTEGRA,0) AS COD_IBGE_MUNICIPIO, 
		CASE WHEN ISNULL(PRC_FILIAL.CONTRIBUINTE,0)<>9 THEN 
			ISNULL(INSC_EST,'') 
		ELSE
			''
		END AS INSC_EST,
		ISNULL(INSC_MUNI,'') AS INSC_MUNI,
		0 AS FLAG_ICMS_SUBST_PAGO_ANTERIOR,
		0 AS FLAG_VALIDA_NFE,
		1 AS TP_SUBS_TRIB,
		1 AS SUBS_TRIB,
		'FILIAL' AS DS_TIPO_PESSOA,
		0 AS CD_TP_FORN,
		0 AS FLAG_FORN_MICRO_EMPRESA,
		4 AS QTDE_CASAS_DECIMAIS_NOTA,
		0 AS TP_LAYOUT_EXP_ENT,
		0 AS EXPORT_NF_ESCRITA,
		ISNULL(ENVIAR_XML_NFE,0) AS ENVIAR_XML_NFE,
		ISNULL(EMAIL, '') AS EMAIL_PESSOA,
		2 AS DEST_ORIG,
		CASE WHEN ISNULL(PRC_FILIAL.CONTRIBUINTE,0)=0 THEN 
			(CASE WHEN ISNULL(INSC_EST,'') IN ('ISENTO','ISENTA') THEN
			2 ELSE 1 END)
		ELSE  ISNULL(PRC_FILIAL.CONTRIBUINTE,0) END CONTRIBUINTE,
		0 AS PRECO_BASE_SUBS,
		0 AS CD_OPER_MVA,
		'' AS DS_OPER_MVA,
		0 AS AR_MERCANTIL,
		'' AS COMPLEMENTO_RESIDENCIAL,
		ISNULL(PRC_FILIAL_CONFIG.VALOR,'0')        AS FLAG_NAO_ABATER_ICMS_DESONERADO,
		0 CONSUMIDOR_FINAL
	FROM
		PRC_FILIAL INNER JOIN GLB_CID ON
		PRC_FILIAL.CD_CID = GLB_CID.CD_CID 
		LEFT OUTER JOIN GLB_CID_GLB_MUNICIPIO ON
		GLB_CID.CD_CID = GLB_CID_GLB_MUNICIPIO.CD_CID 
		LEFT OUTER JOIN GLB_MUNICIPIO ON
		GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO = GLB_MUNICIPIO.CD_MUNICIPIO
		-- RETORNAR SE HAVERÁ ABATIMENTO 
		LEFT JOIN PRC_FILIAL_CONFIG ON
		PRC_FILIAL.CD_EMP = PRC_FILIAL_CONFIG.CD_EMP AND
		PRC_FILIAL.CD_FILIAL = PRC_FILIAL_CONFIG.CD_FILIAL
		AND PRC_FILIAL_CONFIG.CD_CHAVE='NAO_ABATER_ICMS_DESONERADO_VALOR_NOTA_FISCAL' 
		
	WHERE
		PRC_FILIAL.CD_EMP =1
		AND PRC_FILIAL.CD_FILIAL  =@CD_PESSOA
		RETURN;
	END

	RETURN;
END