/****** Object:  StoredProcedure [dbo].[P_EST_ORC_CPL_VENDEDOR]    Script Date: 02/10/2014 18:47:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_ORC_CPL_VENDEDOR]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_ORC_CPL_VENDEDOR]
GO 
-- EXECUTE P_EST_ORC_CPL_VENDEDOR 1, 2
CREATE PROCEDURE [dbo].[P_EST_ORC_CPL_VENDEDOR]	@CD_EMP INT, @CD_FILIAL INT AS
SET NOCOUNT ON;
PRINT 'Analise de vendedor pelo orcamento versus PDV' 
DECLARE @CD_VD AS INT
DECLARE @CD_IT AS INT
DECLARE @CD_VEND AS INT

CREATE TABLE #RsOrc (CD_EMP INT,
					 CD_FILIAL INT,
					 CD_IT INT,
					 CD_VD INT,
					 CD_VEND INT)

INSERT INTO #RsOrc
SELECT
	EST_ORC_CPL.CD_EMP,
	EST_ORC_CPL.CD_FILIAL,
	EST_ORC_CPL.CD_IT,
	PDV_VD.CD_VD,
	EST_ORC_CPL.CD_VEND
FROM
	EST_ORC_CPL INNER JOIN PDV_VD ON 
	EST_ORC_CPL.CD_EMP = PDV_VD.CD_EMP AND
	EST_ORC_CPL.CD_FILIAL = PDV_VD.CD_FILIAL AND
	EST_ORC_CPL.CD_ORC = PDV_VD.NR_ORC
WHERE
	PDV_VD.CD_EMP=@CD_EMP
	AND PDV_VD.CD_FILIAL=@CD_FILIAL
	AND PDV_VD.DT_VD >=GETDATE() - 20 

DECLARE COrcamentoVendedor CURSOR FAST_FORWARD FOR     
SELECT
	PDV_VD_IT.CD_EMP,
	PDV_VD_IT.CD_FILIAL,
	PDV_VD_IT.CD_IT,
	PDV_VD_IT.CD_VD,
	o.CD_VEND
FROM
	PDV_VD_IT INNER JOIN #RsOrc o ON
	PDV_VD_IT.CD_EMP = o.CD_EMP AND
	PDV_VD_IT.CD_FILIAL = o.CD_FILIAL AND
	PDV_VD_IT.CD_VD = o.CD_VD AND 
	PDV_VD_IT.CD_IT = o.CD_IT AND 
	PDV_VD_IT.CD_VEND <> o.CD_VEND 


OPEN COrcamentoVendedor
FETCH NEXT FROM COrcamentoVendedor INTO @CD_EMP,@CD_FILIAL,@CD_IT,@CD_VD,@CD_VEND
WHILE @@FETCH_STATUS=0
  begin
	PRINT	'  Analisando venda ' + convert(varchar(15), @CD_VD) + '  item ' + convert(varchar(15), @CD_IT) + 
			' (' + CONVERT(VARCHAR(8), GETDATE(), 114) + ')...'
			UPDATE
				PDV_VD_IT
			SET 
				CD_VEND=@CD_VEND
			WHERE
				CD_EMP =@CD_EMP
				AND CD_FILIAL=@CD_FILIAL
				AND CD_VD=@CD_VD
				AND CD_IT=@CD_IT
	FETCH NEXT FROM COrcamentoVendedor INTO @CD_EMP,@CD_FILIAL,@CD_IT,@CD_VD,@CD_VEND
  end
PRINT 'Concluido.'
CLOSE COrcamentoVendedor
DEALLOCATE COrcamentoVendedor
