/****** Object:  View [dbo].[V_EST_PROD_CPL_ESTOQUE]    Script Date: 04/14/2014 17:08:57 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_CPL_ESTOQUE]'))
DROP VIEW [dbo].[V_EST_PROD_CPL_ESTOQUE]
GO
GO
GO
--select * from V_EST_PROD_CPL_ESTOQUE where cd_emp=1 and cd_filial = 99 and cd_prod = 2890617
CREATE VIEW [dbo].[V_EST_PROD_CPL_ESTOQUE]
        AS
        SELECT     
                pcpl.CD_EMP, 
                pcpl.CD_FILIAL, 
                pcpl.CD_PROD, 
                p.DS_PROD, 
                EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA AS CD_SC, 
				ISNULL(i.QT_EST, pcpl.QT_EST) QT_EST, 
                pcpl.QT_EST_EM_TERC,
                pcpl.QT_EST_DE_TERC,
                ISNULL(I.QT_PEND_SAI, ISNULL(rs.QT_PEND_SAI, 0)) AS QT_PEND_SAI, 
                ISNULL(I.QT_PEND_ENT, ISNULL(rs.QT_PEND_ENT, 0))  AS QT_PEND_ENT, 
                (pcpl.QT_EST + ISNULL(rs.QT_PEND_ENT , 0)) - ISNULL(rs.QT_PEND_SAI,0) AS QT_EST_PEND, 
                p.STS_PROD,
				p.CD_MOTIVO_STS,
                ISNULL(dbo.EST_PROD_CPL_NASC.QTDE_MES_VENDA_FECHADO, 0) AS QTDE_MES_VENDA_FECHADO,
                pcpl.TP_PROD,
                p.FLAG_VENDA_PERIODICA,
                p.FLAG_VENDA_CONSIGNADA,
                p.CD_UN_VD,
                pcpl.MEDIAF, 
                pcpl.EMAX, 
                pcpl.ESEG, 
                pcpl.QTDE_FACEAMENTO,
				CASE WHEN ISNULL(PERC_ESEG_FACEAMENTO,0)=0 THEN
					pcpl.QTDE_FACEAMENTO
				ELSE
					round(pcpl.QTDE_FACEAMENTO * (ISNULL(pcpl.PERC_ESEG_FACEAMENTO,0)/100.00),0)
				END AS QTDE_FACEAMENTO_REPOSICAO,			
                pcpl.QTDE_FINANCIADO,
                pcpl.QTDE_LIMITADOR,
                pcpl.CD_SIGLA,
                pcpl.STS_PROD STS_PROD_FILIAL,
				pcpl.CD_MOTIVO_STS CD_MOTIVO_STS_FILIAL,
                pcpl.QT_EST_DEPOSITOS,
                pcpl.VLR_CUSTO,
                pcpl.VLR_CUSTO_MD,
				pcpl.VLR_CUSTO_GER,
                CASE WHEN ISNULL(pcpl.VLR_FABRICA,0) + ISNULL(p.VLR_FABRICA,0) = 0 THEN  
					CASE WHEN ISNULL(pcpl.VLR_COMPRA,0)=0 THEN 
						pcpl.VLR_CUSTO  
					ELSE
						pcpl.VLR_COMPRA 
					END 
				ELSE 
					CASE WHEN ISNULL(p.VLR_FABRICA,0) = 0 THEN
						ISNULL(pcpl.VLR_FABRICA,0)
					ELSE
						ISNULL(p.VLR_FABRICA,0)
					END					
				END AS VLR_FABRICA,       
                pcpl.PERC_IPI,
                ISNULL(pcpl.QT_EST_FLUT,0) QT_EST_FLUT,
                pcpl.PRAT,
                pcpl.GAV,
                p.COMPRA_POR_SUPRIMENTO,
				ISNULL(p.QT_EMB_FECHADA_CP,0) * CASE WHEN ISNULL(p.UMM,1)=0 THEN 1 ELSE ISNULL(p.UMM,1) END AS QT_EMB_FECHADA_CP,
				ISNULL(p.QT_EMB_ALTERNATIVA_CP,0) * CASE WHEN ISNULL(p.UMM,1)=0 THEN 1 ELSE ISNULL(p.UMM,1) END AS QT_EMB_ALTERNATIVA_CP,
				(CASE WHEN p.QT_UNI_RESSUPRIMENTO = 0 THEN 1 ELSE p.QT_UNI_RESSUPRIMENTO END) AS QT_UNI_RESSUPRIMENTO,
				isnull(pcpl.COMPRA_POR_SUPRIMENTO, p.COMPRA_POR_SUPRIMENTO)  COMPRA_POR_SUPRIMENTO_FILIAL,
				ISNULL(p.PERC_DESC_FABRICA,0) AS PERC_DESC_FABRICA,
				p.CD_FABRIC,
				p.VLR_COMPRA_GRP,
				p.APRES_QT_CP,
				P.APRES_EMB_CP,
				EST_PROD_CPL_NASC.DT_NASC,
				EST_PROD_CPL_NASC.MEDIA_F_LIBERADA,
				EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA,
				pcpl.QT_IMP,
				ISNULL(pcpl.FLAG_FACE_COMPLEMENTAR,0) AS FLAG_FACE_COMPLEMENTAR 				
        FROM         
                dbo.EST_PROD AS p 
                INNER JOIN dbo.EST_PROD_CPL AS pcpl ON 
                p.CD_EMP = pcpl.CD_EMP AND 
                p.CD_PROD = pcpl.CD_PROD 

                INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON 
                p.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND 
                p.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
                
                
                LEFT OUTER JOIN dbo.EST_PROD_CPL_IMP_PLU i ON 
                pcpl.CD_EMP		= i.CD_EMP AND 
                pcpl.CD_FILIAL	= i.CD_FILIAL AND
                pcpl.CD_PROD	= i.CD_PROD  
                

                LEFT OUTER JOIN dbo.EST_PROD_CPL_NASC ON 
                pcpl.CD_EMP = dbo.EST_PROD_CPL_NASC.CD_EMP AND 
                pcpl.CD_FILIAL = dbo.EST_PROD_CPL_NASC.CD_FILIAL AND
                pcpl.CD_PROD = dbo.EST_PROD_CPL_NASC.CD_PROD 
                
                LEFT OUTER JOIN EST_PROD_CPL_PENDENTE  AS rs WITH (NOLOCK) ON 
                        pcpl.CD_EMP = rs.CD_EMP AND 
                        pcpl.CD_FILIAL = rs.CD_FILIAL AND 
                        pcpl.CD_PROD = rs.CD_PROD
					 