IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_DANFE_PROVISORIO_ITENS_VALIDACAO_IMPOSTO]'))
DROP VIEW V_EST_NF_ENT_DANFE_PROVISORIO_ITENS_VALIDACAO_IMPOSTO
GO
GO
--SELECT * FROM V_EST_NF_ENT_DANFE_PROVISORIO_ITENS_VALIDACAO_IMPOSTO where cd_ctr = 66578
CREATE VIEW [dbo].[V_EST_NF_ENT_DANFE_PROVISORIO_ITENS_VALIDACAO_IMPOSTO] AS
select
	PRI.CD_CTR,
	PRI.CD_CTR_IT,
	PRI.CD_PROD,
	EST_PROD.DS_PROD,
	CASE WHEN ISNULL(CD_SIT_OPERA_SIMPLES_NACIO,0)>0 THEN 
		CONVERT(VARCHAR(3),ISNULL(CD_SIT_OPERA_SIMPLES_NACIO,0))
	ELSE
		CONVERT(VARCHAR(1),ORIGEM) + CONVERT(VARCHAR(2),RIGHT('00000000'+RTRIM(LTRIM(ISNULL(CD_TRIB, 0))),2))
	END AS CD_TRIB_FC,
	CASE WHEN ISNULL(CD_SIT_OPERA_SIMPLES_NACIO,0)>0 THEN 
		CONVERT(VARCHAR(3),ISNULL(CD_SIT_OPERA_SIMPLES_NACIO,0))
	ELSE
	        CONVERT(VARCHAR(2),RIGHT('00000000'+RTRIM(LTRIM(ISNULL(CD_TRIB, 0))),2))
	END  AS CD_TRIB_CST,
	0 AS CD_TRIB,
	PRI.VLR_PROD_UNIT,
	PRI.PERC_PROD_DESC, 
	PRI.VLR_PROD_TOTAL,
	PRI.VLR_PROD_BASE_ICMS,
	PRI.VLR_PROD_ICMS,
	PRI.VLR_PROD_BASE_IPI,
	PRI.VLR_PROD_IPI, 
	PRI.PERC_PROD_IPI, 
	PRI.PERC_PROD_ICMS,
	PRI.VLR_PROD_PMC,
	PRI.VLR_PROD_BASE_ST, 
	PRI.VLR_PROD_ICMS_ST,
	PRI.PERC_PROD_IVA_ST, 
	PRI.PERC_PROD_RED_BASE_ICMS,
	PRI.PERC_PROD_RED_BASE_ST,
	PRI.PERC_PROD_ICMS_ST,
	PRI.VLR_PROD_FRETE,
	PRI.QTDE_PROD,
	PRI.VLR_PROD_DESC,
	PRI.APRES_EMB_CP,
	PRI.APRES_QT_CP,
	PRI.VLR_BASE_PIS,
	PRI.VLR_PIS,
	PRI.PERC_ALIQ_PIS,
	PRI.VLR_BASE_COFINS,
	PRI.VLR_COFINS,
	PRI.PERC_ALIQ_COFINS,
	PRI.VLR_BASE_ICMS_RET,
	PRI.VLR_ICMS_RET, 
	PRI.VLR_CRED_BASE_SN,
	PRI.VLR_CRED_ICMS_SN,
	PRI.PERC_CRED_ICMS_SIMPLES,
	PRI.VLR_CRED_ICMS_SIMPLES,
	PRI.VLR_OUTRAS_DESPESAS,
	PRI.PERC_DESC_FINAN,
	PRI.VLR_DESC_FINAN,
	0 AS TP_DANFE,
	0 AS VLR_BASE_ICMS_OP,
	0 AS VLR_ICMS_OP,
	0 AS PERC_ICMS_OP,
	0 AS PERC_IVA_RET,
	0 AS PERC_ICMS_RET,
	NCM,
	DS_NCM,
	ISNULL(VLR_BASE_FECOP_ST,0) AS VLR_BASE_FECOP_ST,
	ISNULL(PERC_FECOP_ST,0) AS PERC_FECOP_ST,
	ISNULL(VLR_FECOP_ST,0) AS VLR_FECOP_ST,
	ISNULL(VLR_BASE_FECOP_ST_RET,0) AS VLR_BASE_FECOP_ST_RET,
	ISNULL(PERC_FECOP_ST_RET,0) AS PERC_FECOP_ST_RET,
	ISNULL(VLR_FECOP_ST_RET,0) AS VLR_FECOP_ST_RET,
	ISNULL(VLR_FECOP,0) AS VLR_FECOP,
	ISNULL(VLR_BASE_FECOP,0) AS VLR_BASE_FECOP,
	ISNULL(PERC_FECOP,0) AS PERC_FECOP,
	'XML' AS TIPO_DOC
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_ITENS PRI INNER JOIN EST_PROD ON    
	PRI.CD_EMP = EST_PROD.CD_EMP AND
	PRI.CD_PROD = EST_PROD.CD_PROD 
	LEFT OUTER JOIN EST_PROD_NCM_CADASTRO ON
	EST_PROD.NR_NCM = EST_PROD_NCM_CADASTRO.NR_NCM
UNION ALL
select
	V.CD_CTR,
	V.CD_CTR_IT,
	V.CD_PROD, 
	EST_PROD.DS_PROD,
	EST_TRIB.CD_TRIB_FC,
	EST_TRIB.CD_TRIB_CST,
	V.CD_TRIB,
	V.VLR_PROD_UNIT,
	V.PERC_PROD_DESC, 
	V.VLR_PROD_TOTAL,
	V.VLR_PROD_BASE_ICMS,
	V.VLR_PROD_ICMS,
	V.VLR_PROD_BASE_IPI,
	V.VLR_PROD_IPI, 
	V.PERC_PROD_IPI, 
	V.PERC_PROD_ICMS,
	V.VLR_PROD_PMC,
	V.VLR_PROD_BASE_ST, 
	V.VLR_PROD_ICMS_ST,
	V.PERC_PROD_IVA_ST, 
	CASE WHEN V.PERC_PROD_RED_BASE_ICMS>0 THEN
		(100.00 - V.PERC_PROD_RED_BASE_ICMS) 
	ELSE
		0 
	END AS PERC_PROD_RED_BASE_ICMS,
	CASE WHEN V.PERC_PROD_RED_BASE_ST>0 THEN
		(100.00 - V.PERC_PROD_RED_BASE_ST) 
	ELSE
		0 
	END as PERC_PROD_RED_BASE_ST,
	
	V.PERC_PROD_ICMS_ST,
	V.VLR_PROD_FRETE,
	V.QTDE_PROD,
	V.VLR_PROD_DESC,
	V.APRES_EMB_CP,
	V.APRES_QT_CP,
	V.VLR_BASE_PIS,
	V.VLR_PIS,
	V.PERC_ALIQ_PIS,
	V.VLR_BASE_COFINS,
	V.VLR_COFINS,
	V.PERC_ALIQ_COFINS,
	V.VLR_BASE_ICMS_RET,
	V.VLR_ICMS_RET, 
	V.VLR_CRED_BASE_SN,
	V.VLR_CRED_ICMS_SN,
	V.PERC_CRED_ICMS_SIMPLES,
	V.VLR_CRED_ICMS_SIMPLES,
	V.VLR_OUTRAS_DESPESAS,
	V.PERC_DESC_FINAN,
	V.VLR_DESC_FINAN,
	1 AS TP_DANFE,
	ISNULL(v.VLR_BASE_ICMS_OP,0) AS VLR_BASE_ICMS_OP,
	ISNULL(v.VLR_ICMS_OP,0) AS VLR_ICMS_OP,
	ISNULL(PERC_ICMS_OP,0) AS PERC_ICMS_OP,
	ISNULL(PERC_IVA_RET,0) AS PERC_IVA_RET,
	ISNULL(PERC_ICMS_RET,0) AS PERC_ICMS_RET,
	EST_PROD.NR_NCM AS NCM,
	DS_NCM,
	ISNULL(VLR_BASE_FECOP_ST,0) AS VLR_BASE_FECOP_ST,
	ISNULL(PERC_FECOP_ST,0) ASPERC_FECOP_ST,
	ISNULL(VLR_FECOP_ST,0) AS VLR_FECOP_ST,
	ISNULL(VLR_BASE_FECOP_ST_RET,0) AS VLR_BASE_FECOP_ST_RET,
	ISNULL(PERC_FECOP_ST_RET,0) AS PERC_FECOP_ST_RET,
	ISNULL(VLR_FECOP_ST_RET,0) AS VLR_FECOP_ST_RET,
	ISNULL(VLR_FECOP,0) AS VLR_FECOP,
	ISNULL(VLR_BASE_FECOP,0) AS VLR_BASE_FECOP,
	ISNULL(PERC_FECOP,0) AS PERC_FECOP,

	'VALIDACAO' AS TIPO_DOC
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_ITENS_VALIDACAO_IMPOSTO v INNER JOIN EST_TRIB ON
	v.CD_TRIB = EST_TRIB.CD_TRIB
	INNER JOIN EST_PROD ON
	v.CD_EMP = EST_PROD.CD_EMP AND
	v.CD_PROD = EST_PROD.CD_PROD 
	LEFT OUTER JOIN EST_PROD_NCM_CADASTRO ON
	EST_PROD.NR_NCM = EST_PROD_NCM_CADASTRO.NR_NCM