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


CREATE VIEW [dbo].[V_EST_PROD_CPL_PDV]  
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.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,   
	dbo.EST_PROD_CPL_TRIB_EST.PERC_ICMS_EST AS PERC_ICMS_N_CONT_ESTA, 
	dbo.EST_PROD_CPL.QTDE_PONTO_FIDEL,   
	dbo.EST_PROD_CPL.PERC_DESC_FIDEL_MAX, 
	dbo.EST_PROD_CPL.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,
	
	CASE WHEN COALESCE(EST_PROD_PRC_BANDEIRA.STS_PROD, 0) = 0 THEN
		EST_PROD_CPL.STS_PROD
	ELSE
		COALESCE(EST_PROD_PRC_BANDEIRA.STS_PROD, 0)
	END AS STS_PROD,

	ISNULL(EST_PROD_MOTIVO_STS.DS_MOTIVO_STS,' ') DS_MOTIVO_STS,
	A.CD_CST_PIS,
	A.PERC_PIS,
	A.CST_TP_TRIB_PIS,
	A.CD_CST_COFINS,
	A.PERC_COFINS,
    A.CST_TP_TRIB_COFINS,
	EST_PROD_CPL_TRIB_EST.PERC_FECOP AS PERC_FCP,
	EST_PROD_CPL_TRIB_EST.PERC_FECOP_ST AS PERC_FCP_ST,
	ISNULL(EST_PROD_CPL_TRIB_EST.PERC_FECOP_RET,0) AS PERC_FCP_RET,
	COALESCE(dbo.EST_PROD_COD_BENEF.CD_BENEF, '') AS CD_BENEF,
	ISNULL(CASE WHEN EST_TRIB.ICMS_DESONERADO = 1 THEN 
		EST_PROD_COD_BENEF.ICMS_DESONERADO
	ELSE 
		0 
	END,0) ICMS_DESONERADO,
	ISNULL(EST_PROD_COD_BENEF.MOTIVO_DESONERADO_ICMS,0) MOTIVO_DESONERADO_ICMS,

	CASE WHEN ISNULL(dbo.EST_PROD_CPL_TRIB_EST.PERC_ICMS_DESONERADO, 0) > 0 AND EST_TRIB.ICMS_DESONERADO = 1 AND EST_PROD_COD_BENEF.ICMS_DESONERADO = 1 THEN
		ISNULL(dbo.EST_PROD_CPL_TRIB_EST.PERC_ICMS_DESONERADO, 0)
	ELSE
		0
	END AS PERC_ICMS_DESONERADO
	-----------------------FARMAGOI-3375-------------------
	,ISNULL(EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.PERC_RED_BASE_CALC_EFET,0) AS PERC_RED_BASE_CALC_EFET,
	ISNULL(EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.PERC_ICMS_RET,0) AS PERC_ICMS_RET,
	ISNULL(EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.PERC_ICMS_EFET,0) AS PERC_ICMS_EFET
	-------------------------------------------------------
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.PRC_FILIAL ON
		dbo.EST_PROD_CPL.CD_EMP = dbo.PRC_FILIAL.CD_EMP
		AND dbo.EST_PROD_CPL.CD_FILIAL = dbo.PRC_FILIAL.CD_FILIAL
	INNER JOIN dbo.GLB_CID ON
		dbo.PRC_FILIAL.CD_CID = dbo.GLB_CID.CD_CID
	LEFT JOIN dbo.EST_PROD_COD_BENEF ON
		dbo.EST_PROD.CD_EMP = dbo.EST_PROD_COD_BENEF.CD_EMP
		AND dbo.EST_PROD.CD_PROD = dbo.EST_PROD_COD_BENEF.CD_PROD
		AND dbo.GLB_CID.UF = dbo.EST_PROD_COD_BENEF.UF
	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
	LEFT JOIN PRC_BANDEIRA_PRC_FILIAL ON
		dbo.EST_PROD_CPL.CD_EMP = PRC_BANDEIRA_PRC_FILIAL.CD_EMP
		AND dbo.EST_PROD_CPL.CD_FILIAL = PRC_BANDEIRA_PRC_FILIAL.CD_FILIAL
	LEFT JOIN EST_PROD_PRC_BANDEIRA ON
		dbo.PRC_BANDEIRA_PRC_FILIAL.CD_EMP = EST_PROD_PRC_BANDEIRA.CD_EMP
		AND dbo.PRC_BANDEIRA_PRC_FILIAL.CD_BANDEIRA = EST_PROD_PRC_BANDEIRA.CD_BANDEIRA
		AND dbo.EST_PROD.CD_PROD = EST_PROD_PRC_BANDEIRA.CD_PROD
	LEFT JOIN EST_PROD_MOTIVO_STS ON
		EST_PROD_MOTIVO_STS.CD_MOTIVO_STS = EST_PROD_CPL.CD_MOTIVO_STS
	 OUTER APPLY UFD_EST_PROD_TRIB_PIS_COFINS(EST_PROD_CPL.CD_EMP, EST_PROD_CPL.CD_FILIAL,0,EST_PROD_CPL.CD_PROD, 0, 0, (SELECT TOP 1 COALESCE(
	 REGIME_TRIBUTARIO, 0) AS REGIME_TRIBUTARIO FROM PRC_FILIAL WHERE CD_EMP = dbo.EST_PROD_CPL.CD_EMP AND CD_FILIAL = dbo.EST_PROD_CPL.CD_FILIAL)) A
	 ----------FARMAGOI-3375----------------------
	 left JOIN EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST ON
		EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.CD_EMP = EST_PROD_CPL_TRIB_EST.CD_EMP
		AND EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.CD_REGIAO_FISCAL = EST_PROD_CPL_TRIB_EST.CD_REGIAO_FISCAL
		AND EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.CD_PROD = EST_PROD_CPL_TRIB_EST.CD_PROD
		AND EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.CD_TRIB = EST_PROD_CPL_TRIB_EST.CD_TRIB
		AND EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST.TP_MOV = EST_PROD_CPL_TRIB_EST.TP_MOV
		AND EST_PROD_PRC_REGIAO_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_CPL.TP_PROD IN (0, 8, 9, 10,11))