IF  EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_DW_EST_PROD_CPL_ESTOQUE]'))
DROP VIEW [DBO].[V_DW_EST_PROD_CPL_ESTOQUE]
GO
--select top 100 * from V_DW_EST_PROD_CPL_ESTOQUE
CREATE VIEW  V_DW_EST_PROD_CPL_ESTOQUE AS
SELECT
	A.CD_ARV_MERC_CATEG	 COD_CATEGORIA,
	C.CD_ARV_MERC_DEPTO	COD_DEPARTAMENTO,
	P.CD_FABRIC			COD_FABRICANTE,
	PCL.CD_FILIAL,
	A.CD_ARV_MERC_LINHA	COD_LINHA,
	PCL.CD_PROD,
	CONVERT(CHAR(10),GETDATE(),112) AS DT_ESTOQUE,
	QT_EST,
	VLR_CUSTO_MD,
	VLR_CUSTO_GER,
	MF.QTDE_MEDIAF,
	MP.QTDE_MEDIAP,
	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 INNER JOIN DBO.EST_PROD_EST_ARV_MERCADOLOGICA A ON 
	PCL.CD_EMP  = A.CD_EMP AND
	PCL.CD_PROD = A.CD_PROD	
	INNER JOIN DBO.EST_ARV_MERC_CATEGORIA C ON 
	A.CD_EMP  = C.CD_EMP AND
	A.CD_ARV_MERC_CATEG = C.CD_ARV_MERC_CATEG
	INNER JOIN EST_PROD P ON
	PCL.CD_EMP = P.CD_EMP AND
	PCL.CD_PROD = P.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