 /****** Object:  StoredProcedure [dbo].[P_EST_NF_SAI_IT_REORGANIZA]    Script Date: 02/11/2014 18:34:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_SAI_IT_REORGANIZA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_SAI_IT_REORGANIZA]
GO
 
--EXEC P_EST_NF_SAI_IT_REORGANIZA 1,27,'2014-10-14', '2014-10-14'
CREATE PROCEDURE P_EST_NF_SAI_IT_REORGANIZA (@CD_EMP INT,
											 @CD_FILIAL INT,
											 @DT_INI DATETIME,
											 @DT_FIM DATETIME)
AS
CREATE TABLE #RsNotasItem (ORDEM INT IDENTITY (1, 1),
						   CD_EMP INT, 
						   CD_FILIAL INT, 
						   CD_NF INT, 
						   CD_PROD INT)

DECLARE @CD_NF INT

DECLARE cNotaItemControle CURSOR FAST_FORWARD FOR 
SELECT 
	EST_NF_SAI.CD_NF   
FROM 
	EST_NF_SAI INNER JOIN EST_NF_SAI_IT ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND
	EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF  
WHERE  
	EST_NF_SAI.CD_EMP = @CD_EMP 
	AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
	AND EST_NF_SAI.DT_EMI_NF BETWEEN  @DT_INI AND @DT_FIM 
	AND EST_NF_SAI_IT.CD_IT = 0 
GROUP BY
	EST_NF_SAI.CD_EMP,
	EST_NF_SAI.CD_FILIAL,
	EST_NF_SAI.CD_NF
ORDER BY 
	EST_NF_SAI.CD_EMP,
	EST_NF_SAI.CD_FILIAL,
	EST_NF_SAI.CD_NF
		
OPEN cNotaItemControle
FETCH NEXT FROM cNotaItemControle INTO @CD_NF 
WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO #RsNotasItem (CD_EMP, 
									CD_FILIAL, 
									CD_NF, 
									CD_PROD)
		SELECT
			EST_NF_SAI_IT.CD_EMP,
			EST_NF_SAI_IT.CD_FILIAL,
			EST_NF_SAI_IT.CD_NF, 
			EST_NF_SAI_IT.CD_PROD
		FROM
			EST_NF_SAI_IT 
		WHERE
			EST_NF_SAI_IT.CD_EMP = @CD_EMP
			AND EST_NF_SAI_IT.CD_FILIAL =@CD_FILIAL
			AND EST_NF_SAI_IT.CD_NF =@CD_NF
		ORDER BY
			EST_NF_SAI_IT.CD_PROD

		UPDATE
			it
		SET 
			it.CD_IT = i.ORDEM
		FROM
			#RsNotasItem i inner join est_nf_sai_it it on
			i.cd_emp = it.cd_emp and
			i.cd_filial = it.cd_filial and
			i.cd_nf = it.cd_nf and 
			i.cd_prod = it.cd_prod 

select * from #RsNotasItem

		TRUNCATE TABLE #RsNotasItem
		FETCH NEXT FROM cNotaItemControle INTO @CD_NF 
	END
CLOSE cNotaItemControle;
DEALLOCATE cNotaItemControle;
DROP TABLE #RsNotasItem