IF  EXISTS (SELECT * FROM SYS.VIEWS 
				WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_BI_ESTOQUE]'))
DROP VIEW [DBO].[V_BI_ESTOQUE]
GO
--select top 100 * from v_bi_estoque WHERE NOT CD_DEPO IS NULL
CREATE VIEW  V_BI_ESTOQUE AS
SELECT
	(SELECT CD_CTR_DT_MOVIMENTO FROM GLB_DT_MOVIMENTO 
			WHERE DT_MOVIMENTO=CONVERT(DATE,GETDATE())) CD_CTR_DT_MOVIMENTO ,
	PCL.CD_FILIAL,
	D.CD_FILIAL CD_DEPO , 
	PCL.CD_PROD,
	QT_EST,
	PCL.VLR_CUSTO_MD,
	PCL.VLR_CUSTO_GER,
	V.VLR_TABELA,
	V.VLR_OFERTA,
	PCL.MEDIAF MEDIA,
	MF.QTDE_MEDIAF,
	MP.QTDE_MEDIAP,
	PCL.ESEG,
	PCL.EMAX,
	MF.QTDE_FACEAMENTO,
	MF.VALID_QTDE_FACEAMENTO AS DT_VALIDADE_FACEAMENTO,
	MF.QTDE_FINANCIADO,
	MF.QTDE_LIMITADOR,
	MF.CD_SIGLA,
	PCL.STS_PROD AS STS_PROD_FILIAL,
	CASE WHEN QT_EST >0 THEN 0 ELSE 1 END AS ESTZERADO,
	ISNULL(PEND.QT_PEND_ENT, 0) AS QT_PEND_ENT, 
	ISNULL(PEND.QT_PEND_SAI, 0) AS QT_PEND_SAI 
FROM
    EST_PROD_CPL PCL  
	

	LEFT JOIN PRC_FILIAL_DEPOSITO D ON 
	PCL.CD_EMP		= D.CD_EMP AND
	PCL.CD_FILIAL	= D.CD_FILIAL
	
	INNER JOIN EST_PROD_PRECO V ON 
	PCL.CD_EMP		= V.CD_EMP AND
	PCL.CD_FILIAL	= V.CD_FILIAL AND
	PCL.CD_PROD		= V.CD_PROD

	INNER JOIN EST_PROD_CPL_MEDIAF MF ON 
	PCL.CD_EMP		= MF.CD_EMP AND
	PCL.CD_FILIAL	= MF.CD_FILIAL AND
	PCL.CD_PROD		= MF.CD_PROD

	INNER JOIN EST_PROD_CPL_MEDIAP MP ON 
	PCL.CD_EMP		= MP.CD_EMP AND
	PCL.CD_FILIAL	= MP.CD_FILIAL AND
	PCL.CD_PROD		= MP.CD_PROD
	
	LEFT OUTER JOIN EST_PROD_CPL_PENDENTE PEND ON
	PCL.CD_EMP = PEND.CD_EMP AND
	PCL.CD_FILIAL = PEND.CD_FILIAL AND
	PCL.CD_PROD = PEND.CD_PROD
WHERE
	PCL.CD_EMP		= 1
	