IF OBJECT_ID('osuper_preco') IS NOT NULL
	BEGIN
			DROP VIEW osuper_preco
	END

GO

CREATE view [dbo].[osuper_preco] as 
select 
	 EPC.cd_filial	as codigo_loja
	,EPC.cd_prod		AS sku
	,epp.vlr_tabela
	,epp.vlr_oferta
	,case 
		when epp.vlr_oferta > 0 and epp.vlr_oferta < epp.vlr_tabela then		
			'T' 
		else
			'F' 
	end				as promocao
	,null			as data_inclusao
	,epp.dt_registro AS data_alteracao
from 
	est_prod_CPL EPC -- CADASTRO DE PROUTO
	inner join  est_prod_preco epp on -- CADASTRO DE PREÇOS
		EPC.cd_emp = epp.cd_emp
		AND EPC.cd_filial = epp.cd_filial
		AND EPC.cd_prod = epp.cd_prod
	INNER JOIN EST_PROD EP ON (
			EP.CD_PROD    	= EPC.CD_PROD
        AND EP.CD_EMP    	= EPC.CD_EMP
		AND EPC.STS_PROD IN (0) 
		AND EPC.QT_EST > 0
		AND ANTIBIOTICO = 0
		AND FLAG_PRODUTO_CONTROLADO = 0
	)
GROUP BY 
	 EPC.cd_filial
	,EPC.cd_prod	
	,epp.vlr_tabela
	,epp.vlr_oferta
	,epp.vlr_oferta	
	,epp.dt_registro 	
GO
