/****** Object:  StoredProcedure [dbo].[P_EST_ORC_VENDA_PERDIDA_APLICAR]    Script Date: 02/10/2014 18:47:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_ORC_VENDA_PERDIDA_APLICAR]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_ORC_VENDA_PERDIDA_APLICAR]
GO 
--P_EST_ORC_VENDA_PERDIDA_APLICAR 1,2,200
CREATE PROCEDURE P_EST_ORC_VENDA_PERDIDA_APLICAR @CD_EMP INT, @CD_FILIAL INT,@QTDE_DIAS INT=120
AS
SET NOCOUNT ON
PRINT 'Venda perdida orcamento'
IF NOT EXISTS (SELECT
			CD_MT_CANCEL
		   FROM
			GLB_MT_CANCEL_ORC
		    WHERE 
				CD_MT_CANCEL=0)
BEGIN
	INSERT INTO GLB_MT_CANCEL_ORC
	(CD_MT_CANCEL,
	DS_MT_CANCEL)
	SELECT
	0,
	'NAO DEFINIDO'
END

INSERT INTO EST_ORC_VENDA_PERDIDA
(CD_EMP,
CD_FILIAL,
CD_ORC,
CD_USU,
DT_CAD,
CD_MT_CANCEL)
Select 
	EST_ORC.CD_EMP,
	EST_ORC.CD_FILIAL,  
	EST_ORC.CD_ORC,
	1,
	GETDATE(),
	0
From  
	EST_ORC LEFT JOIN PDV_VD_EST_ORC ON  
	EST_ORC.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND  
	EST_ORC.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND  
	EST_ORC.CD_ORC = PDV_VD_EST_ORC.CD_ORC 
	LEFT JOIN PDV_VD ON  
	PDV_VD_EST_ORC.CD_EMP = PDV_VD.CD_EMP AND  
	PDV_VD_EST_ORC.CD_FILIAL = PDV_VD.CD_FILIAL AND  
	PDV_VD_EST_ORC.CD_VD = PDV_VD.CD_VD  
	LEFT OUTER JOIN EST_ORC_MESCLAGEM ON  
	EST_ORC.CD_EMP = EST_ORC_MESCLAGEM.CD_EMP AND  
	EST_ORC.CD_FILIAL = EST_ORC_MESCLAGEM.CD_FILIAL AND  
	EST_ORC.CD_ORC = EST_ORC_MESCLAGEM.CD_ORC_ANTIGO 
	LEFT OUTER JOIN EST_ORC_VENDA_PERDIDA ON  
	EST_ORC.CD_EMP = EST_ORC_VENDA_PERDIDA.CD_EMP AND  
	EST_ORC.CD_FILIAL = EST_ORC_VENDA_PERDIDA.CD_FILIAL AND  
	EST_ORC.CD_ORC = EST_ORC_VENDA_PERDIDA.CD_ORC
Where 
	EST_ORC.CD_EMP = @CD_EMP 
	AND EST_ORC.CD_FILIAL=@CD_FILIAL 
	AND EST_ORC.DT_EMI <=GETDATE()-@QTDE_DIAS
	AND ISNULL(NR_ECF,0) =0 
	AND EST_ORC_MESCLAGEM.CD_ORC_ANTIGO IS NULL 
	AND EST_ORC_VENDA_PERDIDA.CD_ORC IS NULL
