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

GO

CREATE view [dbo].[osuper_produto] as 
select 
	 EP.cd_prod			as sku
	,case 
		when EP.cd_mc = 0 or EP.cd_mc is null then 1111
		else  EP.cd_mc 
	end 	as codigo_marca
	,EP.ds_prod			as nome
	,EPCB.CD_BARRA		as gtin
	,''					as conteudo
	,''					as unidade_conteudo
	,	CASE
		WHEN EP.CD_UN_VD = 5 THEN 'FD'
		WHEN EP.CD_UN_VD = 3 THEN 'CX'
		WHEN EP.CD_UN_VD = 0 THEN 'SD'
		WHEN EP.CD_UN_VD = 2 THEN 'VD'
		ELSE 'UN' 
	END 				as unidade_venda
	,''					as tipo_produto
	,EP.DT_CAD			as data_inclusao
	,EP.DT_ULT_ATU		as data_alteracao
from EST_PROD_CPL EPC
    INNER JOIN EST_PROD EP ON (
			EP.CD_PROD    	= EPC.CD_PROD
        AND EP.CD_EMP    	= EPC.CD_EMP
		AND EPC.QT_EST > 0
		AND EPC.STS_PROD IN (0) 
		AND ANTIBIOTICO = 0
		AND FLAG_PRODUTO_CONTROLADO = 0
	)
	OUTER APPLY ( SELECT TOP 1 CD_BARRA
				  FROM EST_PROD_CD_BARRA EPCB
				  WHERE EPCB.CD_PROD    = EPC.CD_PROD
						 AND EPCB.CD_EMP		= EPC.CD_EMP
				  AND epcb.NAO_EXPORTAR = 0
				  and epcb.EAN_CAIXA_FECHADA = 0
				  ORDER BY EPCB.DT_CAD DESC
				  ) EPCB

GROUP BY 
	 EP.cd_prod
	,case 
		when EP.cd_mc = 0 or EP.cd_mc is null then 1111
		else  EP.cd_mc 
	end
	,EP.ds_prod
	,EPCB.CD_BARRA
	,EP.CD_UN_VD
	,EP.DT_ULT_ATU	
	,EP.DT_CAD
GO
