IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_GLB_PESSOA_BENNER]'))
	DROP VIEW [dbo].[V_GLB_PESSOA_BENNER]
GO

CREATE VIEW [dbo].[V_GLB_PESSOA_BENNER]  
AS SELECT 
	newid() AS ID,
	CLI.CD_EMP AS CD_EMP, 
	CLI.CD_FILIAL AS CD_FILIAL,
	CLI.CD_CLI AS CD_PESSOA,
	CLI.RZ_CLI AS RAZAO_SOCIAL,
	CLI.NM_FANT AS NOME_FANTASIA,
	CLI.CGC_CPF AS CPF_CNPJ,
	CLI.PESSOA AS TIPO_PESSOA,
	CLI.INSC_EST AS INSC_ESTADUAL,
	CLI.INSC_MUNI AS INSC_MUNICIPAL,
	CASE 
		WHEN CLI.INSC_EST = 'ISENTO' THEN 1
		ELSE 0 
	END AS INSC_ESTADUAL_ISENTO,
	CASE 
		WHEN CLI.INSC_MUNI = 'ISENTO' THEN 1
		ELSE 0 
	END AS INSC_MUNICIPAL_ISENTO,
	CLI.NUMERO AS END_NUMERO,
	CLI.END_CLI AS ENDERECO,
	CLI.BAIRRO AS BAIRRO,
	CLI.COMPLEMENTO AS END_COMPLEMENTO,
	CID.DS_CID AS CIDADE,
	CID.UF AS ESTADO_UF,
	PAIS.DS_PAIS AS PAIS,
	CLI.CEP AS CEP,
	LOGRA.DESCRICAO AS END_TIPO_LOGRADOURO,
	CLI.END_COB AS END_COBRANCA,
	CLI.NUMER_COBRANCA AS END_NUMERO_COBRANCA,
	CLI.CEP_COB AS CEP_COBRANCA,
	CLI.BAIRRO_COB AS BAIRRO_COBRANCA,
	CLI.COMPLE_COBRANCA AS COMPLEMENTO_COBRANCA,
	CID_COB.DS_CID AS CIDADE_COBRANCA,
	CID_COB.UF AS ESTADO_UF_COBRANCA,
	PAIS_COB.DS_PAIS AS PAIS_COBRANCA,
	LOGRA_COB.DESCRICAO AS END_TIPO_LOGRADOURO_COBRANCA,
	REPLACE(REPLACE(CLI.DDD_TEL, '(', ''), ')', '') AS DDD_TEL,
	CLI.TEL AS TEL,
	'' AS DDD_CEL,
	'' AS CEL,
	REPLACE(REPLACE(CLI.DDD_FAX, '(', ''), ')', '') AS DDD_FAX,
	CLI.FAX AS FAX,
	REPLACE(REPLACE(CLI.DDD_TEL_COB, '(', ''), ')', '') AS DDD_TEL_COBRANCA,
	CLI.TEL_COB AS TEL_COBRANCA,
	CLI.CARGO AS CARGO,
	CLI.EMAIL AS EMAIL,
	CLI.EMAIL_COB AS EMAIL_COBRANCA,
	CLI.SEXO_CLI AS TIPO_SEXO,
	CLI.DATA_NASC AS DATA_NASCIMENTO,
	CLI.RG AS RG,
	CLI.RG_ORG_EXP AS ORGAO_EXPEDITOR_RG,
	CLI.NM_MAE AS NOME_MAE,
	CLI.NM_PAI AS NOME_PAI,
	0 AS TIPO_REGISTRO,
	'RC_CLI' AS TABELA_REGISTRO
FROM
	DBO.RC_CLI CLI
LEFT JOIN 
	DBO.GLB_CID CID ON 
	CLI.CD_CID = CID.CD_CID
LEFT JOIN 
	DBO.GLB_PAIS PAIS ON 
	CID.CD_PAIS = PAIS.CD_PAIS
LEFT JOIN 
	DBO.GLB_CID CID_COB ON 
	CLI.CD_CID_COB = CID_COB.CD_CID
LEFT JOIN 
	DBO.GLB_PAIS PAIS_COB ON 
	CID_COB.CD_PAIS = PAIS_COB.CD_PAIS
LEFT JOIN 
	DBO.GLB_CID_LOGRA_TP LOGRA 
	ON CLI.TP_LOGRA = LOGRA.TP_LOGRA
LEFT JOIN 
	DBO.GLB_CID_LOGRA_TP LOGRA_COB
	ON CLI.TP_LOGRA_COB = LOGRA.TP_LOGRA
	
UNION ALL

SELECT 
	newid() AS ID,
	FORN.CD_EMP AS CD_EMP,
	FORN.CD_FILIAL AS CD_FILIAL,
	FORN.CD_FORN AS CD_PESSOA,
	FORN.RZ_FORN AS RAZAO_SOCIAL,
	FORN.NM_FANT AS NOME_FANTASIA,
	FORN.CGC_CPF AS CPF_CNPJ,
	FORN.PESSOA AS TIPO_PESSOA,
	FORN.INSC_EST AS INSC_ESTADUAL,
	FORN.INSC_MUNI AS INSC_MUNICIPAL,
	CASE 
		WHEN FORN.INSC_EST = 'ISENTO' THEN 1
		ELSE 0 
	END AS INSC_ESTADUAL_ISENTO,
	CASE 
		WHEN FORN.INSC_MUNI = 'ISENTO' THEN 1
		ELSE 0 
	END AS INSC_MUNICIPAL_ISENTO,
	FORN.NR_END_FORN AS END_NUMERO,
	FORN.END_FORN AS ENDERECO,
	FORN.BAIRRO_FORN AS BAIRRO,
	FORN.COMPLE_END AS END_COMPLEMENTO,
	CID.DS_CID AS CIDADE,
	CID.UF AS ESTADO_UF,
	PAIS.DS_PAIS AS PAIS,
	FORN.CEP AS CEP,
	LOGRA.DESCRICAO AS END_TIPO_LOGRADOURO,
	'' AS END_COBRANCA,
	'' AS END_NUMERO_COBRANCA,
	'' AS CEP_COBRANCA,
	'' AS BAIRRO_COBRANCA,
	'' AS COMPLEMENTO_COBRANCA,
	'' AS CIDADE_COBRANCA,
	'' AS ESTADO_UF_COBRANCA,
	'' AS PAIS_COBRANCA,
	'' AS END_TIPO_LOGRADOURO_COBRANCA,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(FORN.TEL))), 1, 2) AS DDD_TEL,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(FORN.TEL))), 3, LEN(LTRIM(RTRIM(DBO.F_NUMEROS(FORN.TEL))))) AS TEL,
	'' AS DDD_CEL,
	'' AS CEL,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(FORN.FAX))), 1, 2) AS DDD_FAX,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(FORN.FAX))), 3, LEN(LTRIM(RTRIM(DBO.F_NUMEROS(FORN.FAX))))) AS FAX,
	'' AS DDD_TEL_COBRANCA,
	'' AS TEL_COBRANCA,
	FORN.CARGO AS CARGO,
	FORN.EMAIL AS EMAIL,
	'' AS EMAIL_COBRANCA,
	'' AS TIPO_SEXO,
	'' AS DATA_NASCIMENTO,
	'' AS RG,
	'' AS ORGAO_EXPEDITOR_RG,
	'' AS NOME_MAE,
	'' AS NOME_PAI,
	1 AS TIPO_REGISTRO,
	'PG_FORN' AS TABELA_REGISTRO
FROM 
	PG_FORN FORN
LEFT JOIN GLB_CID CID ON
	FORN.CD_CID = CID.CD_CID
LEFT JOIN 
	DBO.GLB_PAIS PAIS ON 
	CID.CD_PAIS = PAIS.CD_PAIS
LEFT JOIN 
	DBO.GLB_CID_LOGRA_TP LOGRA 
	ON FORN.TP_LOGRA = LOGRA.TP_LOGRA

UNION ALL

SELECT 
	newid() AS ID,
	EMI.CD_EMP AS CD_EMP,
	EMI.CD_FILIAL AS CD_FILIAL ,
	EMI.CD_EMI AS CD_PESSOA,
	EMI.NM_EMI AS RAZAO_SOCIAL,
	'' AS NOME_FANTASIA,
	EMI.CGC_CPF  AS CPF_CNPJ,
	EMI.TP_PESSOA AS TIPO_PESSOA,
	'' AS INSC_ESTADUAL,
	'' AS INSC_MUNICIPAL,
	'' AS INSC_ESTADUAL_ISENTO,
	'' AS INSC_MUNICIPAL_ISENTO,
	'' AS END_NUMERO,
	EMI.END_EMI AS ENDERECO,
	EMI.BAIRRO_EMI AS BAIRRO,
	'' AS END_COMPLEMENTO,
	CID.DS_CID AS CIDADE,
	CID.UF AS ESTADO_UF,
	PAIS.DS_PAIS AS PAIS,
	EMI.CEP_EMI AS CEP,
	LOGRA.DESCRICAO AS END_TIPO_LOGRADOURO,
	'' AS END_COBRANCA,
	'' AS END_NUMERO_COBRANCA,
	'' AS CEP_COBRANCA,
	'' AS BAIRRO_COBRANCA,
	'' AS COMPLEMENTO_COBRANCA,
	'' AS CIDADE_COBRANCA,
	'' AS ESTADO_UF_COBRANCA,
	'' AS PAIS_COBRANCA,
	'' AS END_TIPO_LOGRADOURO_COBRANCA,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(EMI.FONE_EMI))), 1, 2) AS DDD_TEL,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(EMI.FONE_EMI))), 3, LEN(LTRIM(RTRIM(DBO.F_NUMEROS(EMI.FONE_EMI))))) AS TEL,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(EMI.CEL_EMI))), 1, 2) AS DDD_CEL,
	SUBSTRING(LTRIM(RTRIM(DBO.F_NUMEROS(EMI.CEL_EMI))), 3, LEN(LTRIM(RTRIM(DBO.F_NUMEROS(EMI.CEL_EMI))))) AS CEL,
	'' AS DDD_FAX,
	'' AS FAX,
	'' AS DDD_TEL_COBRANCA,
	'' AS TEL_COBRANCA,
	'' AS CARGO,
	'' AS EMAIL,
	'' AS EMAIL_COBRANCA,
	'' AS TIPO_SEXO,
	'' AS DATA_NASCIMENTO,
	'' AS RG,
	'' AS ORGAO_EXPEDITOR_RG,
	'' AS NOME_MAE,
	'' AS NOME_PAI,
	2 AS TIPO_REGISTRO,
	'CHQ_EMI' AS TABELA_REGISTRO
FROM 
	DBO.CHQ_EMI EMI
LEFT JOIN 
	DBO.GLB_CID CID 
	ON EMI.CD_CID = CID.CD_CID
LEFT JOIN 
	DBO.GLB_PAIS PAIS ON 
	CID.CD_PAIS = PAIS.CD_PAIS
LEFT JOIN 
	DBO.GLB_CID_LOGRA_TP LOGRA 
	ON EMI.TP_LOGRA = LOGRA.TP_LOGRA;