
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_GLB_PESSOA]'))
DROP VIEW [dbo].[V_GLB_PESSOA]
GO
--SELECT TOP 10 * FROM V_GLB_PESSOA where cd_pessoa=31
CREATE VIEW V_GLB_PESSOA AS 
SELECT
	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 collate SQL_Latin1_General_CP1_CI_AS AS ENDERECO_PESSOA ,
	RC_CLI.NUMERO  collate SQL_Latin1_General_CP1_CI_AS 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,
	ISNULL(INSC_EST,'') 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,
	0 AS FLAG_ENTRAR_NF_SEM_VINC_PEDIDO,
	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,
	0 AS FLAG_NAO_ABATER_ICMS_DESONERADO,
	RC_CLI.STS_CLI STS_PESSOA,
	RC_CLI.CONS_FINAL,
	ISNULL(RC_CLI.INSC_SUFRAMA,'') AS INSC_SUFRAMA
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
UNION ALL
SELECT
	PG_FORN.CD_EMP,
	PG_FORN.CD_FORN,
	RZ_FORN,
	NM_FANT,
	CGC_CPF,	
	END_FORN  collate SQL_Latin1_General_CP1_CI_AS,
	ISNULL(PG_FORN.NR_END_FORN, '')  collate SQL_Latin1_General_CP1_CI_AS 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,
	ISNULL(INSC_EST,'') 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(PRC_FILIAL_PG_FORN.CD_FORN,0)>0 THEN 1
	ELSE ISNULL(PG_FORN.FLAG_ENTRAR_NF_SEM_VINC_PEDIDO,0) 
	END AS FLAG_ENTRAR_NF_SEM_VINC_PEDIDO,

	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,
	ISNULL(PG_FORN.FLAG_NAO_ABATER_ICMS_DESONERADO,0)FLAG_NAO_ABATER_ICMS_DESONERADO,
	PG_FORN.STS_FORN STS_PESSOA,
	0 CONS_FINAL,
	ISNULL(PG_FORN.INSC_SUFRAMA,'') AS INSC_SUFRAMA
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 PRC_FILIAL_PG_FORN ON
	PG_FORN.CD_EMP = PRC_FILIAL_PG_FORN.CD_EMP AND
	PG_FORN.CD_FORN= PRC_FILIAL_PG_FORN.CD_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

UNION ALL

--***************************************************
SELECT
	PRC_FILIAL.CD_EMP,
	PRC_FILIAL.CD_FILIAL,
	RZ_FILIAL,
	NM_FANT,
	CGC,	
	PRC_FILIAL.END_FILILAL,
	'' 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,
	ISNULL(INSC_EST,'') 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,
	CASE WHEN ISNULL(PRC_FILIAL.REGIME_TRIBUTARIO,0) = 1 OR ISNULL(PRC_FILIAL.REGIME_TRIBUTARIO,0) = 2 THEN
	   1
	ELSE 
	   0 
	END 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,
	0 AS FLAG_ENTRAR_NF_SEM_VINC_PEDIDO, 
	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,
	0 AS FLAG_NAO_ABATER_ICMS_DESONERADO,
	PRC_FILIAL.STS_FILIAL STS_PESSOA,
	0 CONS_FINAL,
	ISNULL(PRC_FILIAL.INSC_SUFRAMA,'') AS INSC_SUFRAMA
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
WHERE
	PRC_FILIAL.CD_EMP=1

	