IF  EXISTS (SELECT * FROM sys.views 
				WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_CPL_ESTOQUE_PEDIDO]'))
DROP VIEW [dbo].[V_EST_PROD_CPL_ESTOQUE_PEDIDO]
GO
CREATE VIEW [dbo].[V_EST_PROD_CPL_ESTOQUE_PEDIDO] with encryption AS 
SELECT     
    e.CD_EMP, 
    e.CD_FILIAL, 
    e.CD_PROD, 
    e.DS_PROD, 
    e.CD_SC, 
    e.QT_EST			+ ISNULL(eda.QT_EST,0) QT_EST, 
    e.QT_EST_EM_TERC	+ ISNULL(eda.QT_EST_EM_TERC,0) AS QT_EST_EM_TERC,
    e.QT_EST_DE_TERC	+ ISNULL(eda.QT_EST_DE_TERC,0) AS QT_EST_DE_TERC,
    ISNULL(e.QT_PEND_SAI, 0)	+ ISNULL(eda.QT_PEND_SAI,0)	AS QT_PEND_SAI, 
    ISNULL(e.QT_PEND_ENT, 0)	+ ISNULL(eda.QT_PEND_ENT,0) AS QT_PEND_ENT,  
    e.QT_EST_PEND + ((ISNULL(eda.QT_EST,0) + ISNULL(eda.QT_PEND_ENT , 0)) - ISNULL(eda.QT_PEND_SAI,0)) AS QT_EST_PEND,  
    e.STS_PROD, 
    ISNULL(e.QTDE_MES_VENDA_FECHADO, 0) AS QTDE_MES_VENDA_FECHADO,
    e.TP_PROD,
    e.FLAG_VENDA_PERIODICA,
    e.FLAG_VENDA_CONSIGNADA,
    e.CD_UN_VD,
    e.MEDIAF, 
    e.EMAX, 
    e.ESEG, 
    e.QTDE_FACEAMENTO, 
    e.QTDE_FINANCIADO,
    e.QTDE_LIMITADOR,
    e.CD_SIGLA,
    e.STS_PROD_FILIAL STS_PROD_FILIAL,
    e.QT_EST_DEPOSITOS,
    ISNULL(eda.VLR_CUSTO, e.VLR_CUSTO) VLR_CUSTO,
    ISNULL(eda.VLR_CUSTO_MD, e.VLR_CUSTO_MD) VLR_CUSTO_MD,
    e.VLR_FABRICA,       
    e.PERC_IPI, 
    ISNULL(e.QT_EST_FLUT,0) + ISNULL(eda.QT_EST_FLUT,0) QT_EST_FLUT,  
    e.PRAT,
    e.GAV,
    e.COMPRA_POR_SUPRIMENTO,
    e.QT_EMB_FECHADA_CP,
    e.QT_EMB_ALTERNATIVA_CP,
	e.PERC_DESC_FABRICA,
	e.CD_FABRIC,
	e.VLR_COMPRA_GRP,
	e.APRES_QT_CP,
	e.APRES_EMB_CP,
	mf.QT_VD_MES1,
	mf.QT_VD_MES2,
	mf.QT_VD_MES3,
	mf.QT_VD_ATUAL,
	mf.PERC_ESEG_FACEAMENTO,
	mf.VALID_QTDE_FACEAMENTO,
	e.FLAG_FACE_COMPLEMENTAR
FROM         
    V_EST_PROD_CPL_ESTOQUE e   
	--eda - Estoque do Deposito Auxiliar, somar para efeitos de compra..
	LEFT JOIN (SELECT 
					P.CD_EMP,
					A.CD_FILIAL_DEPO CD_FILIAL,
					P.CD_PROD,
					avg(p.VLR_CUSTO) VLR_CUSTO,
					avg(p.VLR_CUSTO_MD) VLR_CUSTO_MD,
					sum(P.QT_EST) QT_EST , 
					sum(P.QT_EST_EM_TERC)	QT_EST_EM_TERC,
					sum(P.QT_EST_DE_TERC)	QT_EST_DE_TERC,
					sum(ISNULL( p.QT_PEND_SAI , 0)) AS QT_PEND_SAI, 
					sum(ISNULL( p.QT_PEND_ENT , 0)) AS QT_PEND_ENT, 
					sum((P.QT_EST + ISNULL(p.QT_PEND_ENT , 0)) - ISNULL(p.QT_PEND_SAI,0)) AS QT_EST_PEND,
					sum(P.QT_EST_FLUT) QT_EST_FLUT
				FROM 
					V_EST_PROD_CPL_ESTOQUE p
					 
					INNER JOIN (SELECT CD_EMP, CD_FILIAL_DEPO_AUX CD_FILIAL,CD_FILIAL_DEPO, CD_PROD 
										FROM 
											PRC_FILIAL_DEPOSITO_AUXILIAR_EST_PROD
									union
									select 
											CD_EMP, CD_FILIAL_DEPO_AUX CD_FILIAL,CD_FILIAL_DEPO, CD_PROD  
									from 
										PRC_FILIAL_DEPOSITO_AUXILIAR_EST_PROD_cpl) A ON 
					A.CD_EMP	= P.CD_EMP AND
					A.CD_FILIAL = P.CD_FILIAL AND
					A.CD_PROD	= P.CD_PROD   
				GROUP BY 
					P.CD_EMP,
					A.CD_FILIAL_DEPO,
					P.CD_PROD) eda  ON 
	e.CD_EMP	= eda.CD_EMP AND 
	e.CD_FILIAL = eda.CD_FILIAL AND 
	e.CD_PROD	= eda.CD_PROD
	INNER JOIN EST_PROD_CPL_MEDIAF mf ON 
	e.CD_EMP	= mf.CD_EMP AND 
	e.CD_FILIAL = mf.CD_FILIAL AND 
	e.CD_PROD	= mf.CD_PROD