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

GO

CREATE view [dbo].[osuper_estoque] as 
select 
	EPC.cd_filial	as codigo_loja
	,EPC.cd_prod	as sku
	,EPC.qt_est		as estoque_disponivel
	,EPC.dt_cad		as data_inclusao
	,CASE 
	WHEN VESTENT.dt_enT > VESTSAI.DT_EMI_NF AND VESTENT.dt_enT > PDV_VD_IT.DT_VD THEN VESTENT.dt_enT
	WHEN PDV_VD_IT.DT_VD > VESTSAI.DT_EMI_NF AND PDV_VD_IT.DT_VD > VESTENT.dt_enT THEN PDV_VD_IT.DT_VD
	ELSE VESTSAI.DT_EMI_NF
	END as data_alteracao
from 
	EST_PROD_CPL EPC -- Saldos de estoque
	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)   -- Busca apenas produtos ativos
		AND ANTIBIOTICO = 0
		AND FLAG_PRODUTO_CONTROLADO = 0
	)
	LEFT JOIN (SELECT MAX(DT_HR_CAD) AS DT_EMI_NF, A.CD_EMP, A.CD_FILIAL, A.CD_PROD FROM V_EST_NF_SAI A  GROUP BY  A.CD_EMP, A.CD_FILIAL, A.CD_PROD )  VESTSAI ON 
					EPC.CD_EMP = VESTSAI.CD_EMP 
				AND EPC.CD_FILIAL = VESTSAI.CD_FILIAL
				AND EPC.CD_PROD = VESTSAI.CD_PROD
	LEFT JOIN (SELECT MAX(DT_HR_CAD) AS dt_ent, B.CD_EMP, B.CD_FILIAL, B.CD_PROD FROM V_EST_NF_ENT B  GROUP BY  B.CD_EMP, B.CD_FILIAL, B.CD_PROD ) VESTENT ON 
					EPC.CD_EMP = VESTENT.CD_EMP 
				AND EPC.CD_FILIAL = VESTENT.CD_FILIAL
				AND EPC.CD_PROD = VESTENT.CD_PROD 
	LEFT JOIN (SELECT MAX(C.DT_CONSD) AS DT_VD, C.CD_EMP, C.CD_FILIAL, C.CD_PROD FROM PDV_VD_IT C  GROUP BY  C.CD_EMP, C.CD_FILIAL, C.CD_PROD ) PDV_VD_IT ON 
					EPC.CD_EMP = PDV_VD_IT.CD_EMP 
				AND EPC.CD_FILIAL = PDV_VD_IT.CD_FILIAL
				AND EPC.CD_PROD = PDV_VD_IT.CD_PROD 
GROUP BY 
	EPC.cd_filial
	,EPC.cd_prod	
	,EPC.qt_est
	,EPC.dt_cad
	,EPC.DT_ATUALIZA_PARAM
	,VESTENT.dt_ent
	,VESTSAI.DT_EMI_NF
	,PDV_VD_IT.DT_VD
GO
