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

/*SELECT * FROM V_EST_PROD_CPL WHERE CD_EMP = 1 AND CD_FILIAL = 19 AND CD_PROD = 1*/  
CREATE VIEW [dbo].[V_EST_PROD_CPL]  
AS  
SELECT     
	dbo.EST_PROD_CPL.CD_EMP, 
	dbo.EST_PROD_CPL.CD_FILIAL, 
	dbo.EST_PROD_CPL.CD_PROD, 
	0 AS CD_TRIB_CP, 
	0 AS CD_TRIB_CP_EST,   
	0 AS CD_TRIB_TRANSF, 
	0 AS CD_TRIB_VD_INTER, 
	dbo.EST_PROD_CPL_TRIB_EST.CD_TRIB AS CD_TRIB_VD, 
	dbo.EST_PROD_CPL.CD_USU,   
	dbo.EST_PROD_CPL.DT_CAD, 
	dbo.EST_PROD_CPL.DT_IMP, 
	dbo.EST_PROD_CPL.DT_ULT_ATU, 
	dbo.EST_PROD_CPL.GAV,   
	dbo.EST_PROD_CPL.IPI_NF_SAIDA, 
	0 AS PERC_ICMS_CONT_ESTA, 
	0 AS PERC_ICMS_CONT_INTER, 
	0 AS PERC_ICMS_CP_ESTA,   
	0 AS PERC_ICMS_MICRO_EMP, 
	0 AS PERC_ICMS_N_CONT_INTER, 
	0 AS PERC_IPI, 
	0 AS PERC_IVA, 
	0 AS PERC_RD_CP_EST,   
	0 AS PERC_RD_VD_INTER, 
	0 AS PERC_RED_CP, 
	dbo.EST_PROD_CPL_TRIB_EST.PERC_RED AS PERC_RED_VD, 
	dbo.EST_PROD_CPL.PRAT,   
	dbo.EST_PROD_CPL.QT_EST, 
	dbo.EST_PROD_CPL.QT_IMP, 
	dbo.EST_PROD_CPL.SLD_INI, 
	dbo.EST_PROD_CPL.STS_PROD,   
	dbo.EST_PROD_CPL.TP_PROD, 
	dbo.EST_PROD_CPL.VLR_CONS, 
	dbo.EST_PROD_CPL.VLR_CUSTO, 
	dbo.EST_PROD_CPL.VLR_CUSTO_ANT,   
	dbo.EST_PROD_CPL.VLR_CUSTO_INI, 
	dbo.EST_PROD_CPL.VLR_CUSTO_MD, 
	dbo.EST_PROD_CPL.VLR_CUSTO_TAB, 
	dbo.EST_PROD_CPL.VLR_REV,   
	CASE WHEN FISCAL_TRIB_EST.PERC_ICMS_EST > 0 THEN  FISCAL_TRIB_EST.PERC_ICMS_EST 
		 ELSE dbo.EST_PROD_CPL_TRIB_EST.PERC_ICMS_EST 
	  END AS PERC_ICMS_N_CONT_ESTA, 
	dbo.EST_PROD_CPL.QTDE_PONTO_FIDEL,   
	isnull (dbo.EST_PROD_CPL.PERC_DESC_FIDEL_MAX,0) as PERC_DESC_FIDEL_MAX, 
	isnull (dbo.EST_PROD_CPL.PERC_DESC_FIDEL,0) as PERC_DESC_FIDEL,
	GETDATE() AS DT_ESTOQUE, 
	0 AS ALT_SIST, 
	0 AS REG_ALTERADO, 
	EST_PROD.APRES_QT_VD,
	EST_PROD_CPL.PERC_DESC_LIMITE,
	EST_PROD_CPL_TRIB_EST.PERC_IBPT,
	ISNULL(EST_TRIB.ICMS,0) AS ICMS,
	ISNULL(EST_TRIB.SUBS_TRIB,0) AS SUBS_TRIB,
	ISNULL(EST_TRIB.ICMS_RD,0) AS ICMS_RD,
	ISNULL(EST_TRIB.SUBS_TRIB_ANT,0) AS SUBS_TRIB_ANT,
	ISNULL(EST_TRIB.ISENTO,0) AS ISENTO,
	ISNULL(EST_TRIB.FLAG_OUTROS,0) AS FLAG_OUTROS,
	EST_TRIB.CD_TRIB_FC,
	EST_PROD_CPL.FLAG_VERBA_DESCON,
	EST_PROD_CPL_TRIB_EST.CST_PIS,
	EST_PROD_CPL_TRIB_EST.CST_COFINS,
	EST_PROD_CPL_TRIB_EST.PERC_PIS,
	EST_PROD_CPL_TRIB_EST.PERC_COFINS,
	EST_PROD.TIPO_TRIBUTACAO_PIS_COFINS
	
FROM         
	dbo.EST_PROD 
	INNER JOIN dbo.EST_PROD_CPL ON 
		dbo.EST_PROD.CD_EMP = dbo.EST_PROD_CPL.CD_EMP    
		AND dbo.EST_PROD.CD_PROD = dbo.EST_PROD_CPL.CD_PROD 
	INNER JOIN dbo.EST_PROD_CPL_TRIB_EST ON 
		dbo.EST_PROD_CPL.CD_EMP = dbo.EST_PROD_CPL_TRIB_EST.CD_EMP   
		AND dbo.EST_PROD_CPL.CD_FILIAL = dbo.EST_PROD_CPL_TRIB_EST.CD_FILIAL   
		AND dbo.EST_PROD_CPL.CD_PROD = dbo.EST_PROD_CPL_TRIB_EST.CD_PROD  
	INNER JOIN EST_TRIB ON
	EST_PROD_CPL_TRIB_EST.CD_TRIB = EST_TRIB.CD_TRIB
	INNER JOIN EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST FISCAL_TRIB_EST ON FISCAL_TRIB_EST.CD_EMP			=EST_PROD_CPL_TRIB_EST.CD_EMP			
																  AND FISCAL_TRIB_EST.CD_REGIAO_FISCAL	=EST_PROD_CPL_TRIB_EST.CD_REGIAO_FISCAL	
																  AND FISCAL_TRIB_EST.CD_PROD			=EST_PROD_CPL_TRIB_EST.CD_PROD			
																  AND FISCAL_TRIB_EST.CD_TRIB			=EST_PROD_CPL_TRIB_EST.CD_TRIB			
																  AND FISCAL_TRIB_EST.TP_MOV			=EST_PROD_CPL_TRIB_EST.TP_MOV			
																  AND FISCAL_TRIB_EST.TP_TRANSA			=EST_PROD_CPL_TRIB_EST.TP_TRANSA			
WHERE     
	(dbo.EST_PROD_CPL_TRIB_EST.TP_MOV = 0) 
	AND (dbo.EST_PROD_CPL_TRIB_EST.TP_TRANSA = 1) 
	AND (dbo.EST_PROD.STS_PROD IN (0, 2)) 
	AND (dbo.EST_PROD_CPL.TP_PROD IN (0, 8, 9, 10,11))

 