/****** Object:  StoredProcedure [dbo].[P_EST_NOTAS_TRANSFERENCIA_CHECKIN_ENTRADA_AUTOMATICO]    Script Date: 02/27/2014 14:43:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NOTAS_TRANSFERENCIA_CHECKIN_ENTRADA_AUTOMATICO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NOTAS_TRANSFERENCIA_CHECKIN_ENTRADA_AUTOMATICO]
GO
CREATE PROCEDURE P_EST_NOTAS_TRANSFERENCIA_CHECKIN_ENTRADA_AUTOMATICO
with encryption 
AS
DECLARE @CD_EMP INT
DECLARE @CD_FILIAL INT 
DECLARE @CD_CTR INT 
DECLARE @CD_NF INT
DECLARE @QTDE_DIAS_PROC AS INT
DECLARE @DT_PROC DATETIME

--Buscando parametro filial
SET @QTDE_DIAS_PROC = ISNULL((SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'QTDE_DIAS_CHECKIN_TRANSFERENCIA_AUTOMATICO'),0);

--Se nao existir analisa o parametro empresa
IF @QTDE_DIAS_PROC=0 
BEGIN
	SET @QTDE_DIAS_PROC = ISNULL((SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'QTDE_DIAS_CHECKIN_TRANSFERENCIA_AUTOMATICO'),0);
END

--Se nao existir informa o valor padrao de 200 dias
IF @QTDE_DIAS_PROC=0 
BEGIN
	SET @QTDE_DIAS_PROC = 200;
END

SET @DT_PROC = CONVERT(CHAR(10),GETDATE() - @QTDE_DIAS_PROC,112)
PRINT 'CheckIn de notas fiscais inferior a ' + CONVERT(CHAR(10),GETDATE() - @QTDE_DIAS_PROC,103)

CREATE TABLE #NotaFiscalRessuprimento(CD_EMP INT,
									  CD_FILIAL INT,
									  CD_NF INT,
									  CD_CTR INT)

INSERT INTO #NotaFiscalRessuprimento
SELECT
	EST_NF_SAI.CD_EMP,
	EST_NF_SAI.CD_FILIAL,
	EST_NF_SAI.CD_NF,
	ISNULL(EST_PROD_TRANSF_PED_VD.CD_CTR,0)
FROM
	EST_NF_SAI INNER JOIN EST_NF_SAI_PENDENCIA ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_PENDENCIA.CD_EMP AND 
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_PENDENCIA.CD_FILIAL AND
	EST_NF_SAI.CD_NF = EST_NF_SAI_PENDENCIA.CD_NF 
	LEFT OUTER JOIN EST_NF_SAI_PED_VD ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL AND
	EST_NF_SAI.CD_NF = EST_NF_SAI_PED_VD.CD_NF 
	LEFT OUTER JOIN EST_PROD_TRANSF_PED_VD ON
	EST_NF_SAI_PED_VD.CD_EMP = EST_PROD_TRANSF_PED_VD.CD_EMP AND
	EST_NF_SAI_PED_VD.CD_FILIAL = EST_PROD_TRANSF_PED_VD.CD_FILIAL AND
	EST_NF_SAI_PED_VD.CD_PED = EST_PROD_TRANSF_PED_VD.CD_PED
WHERE
	EST_NF_SAI.DT_EMI_NF <=@DT_PROC

--Checkin de entrada ressuprimento
DECLARE Ressup CURSOR FAST_FORWARD FOR 
			SELECT
				DISTINCT 
				CD_EMP,
				CD_FILIAL,
				CD_CTR
			FROM
				#NotaFiscalRessuprimento
			WHERE
				CD_CTR > 0 		
OPEN Ressup
FETCH NEXT FROM Ressup INTO @CD_EMP,@CD_FILIAL,@CD_CTR
	WHILE @@FETCH_STATUS=0
	  begin
		PRINT 'CheckIn ressuprimento ' + CONVERT(VARCHAR, GETDATE (),108)
		EXEC P_EST_PROD_TRANSF_CHECKIN_ENTRADA_AUTOMATICO @CD_EMP,@CD_FILIAL,@CD_CTR

		FETCH NEXT FROM Ressup INTO @CD_EMP,@CD_FILIAL,@CD_CTR
	  end

PRINT 'Concluido ressuprimento.'
CLOSE Ressup;
DEALLOCATE Ressup;

--Checkin nota fiscal de saida
DECLARE NotaFiscalAutomatica CURSOR FAST_FORWARD FOR 
			SELECT
				CD_EMP,
				CD_FILIAL,
				CD_NF
			FROM
				#NotaFiscalRessuprimento

OPEN NotaFiscalAutomatica
FETCH NEXT FROM NotaFiscalAutomatica INTO @CD_EMP,@CD_FILIAL,@CD_NF
	WHILE @@FETCH_STATUS=0
	  begin
		PRINT 'CheckIn de nota fiscal ' + CONVERT(VARCHAR, GETDATE (),108)
		EXEC P_EST_NF_SAI_CHECKIN_ENTRADA_AUTOMATICO @CD_EMP,@CD_FILIAL,@CD_NF

		FETCH NEXT FROM NotaFiscalAutomatica INTO @CD_EMP,@CD_FILIAL,@CD_NF
	  end

PRINT 'Concluido nota fiscal.'
CLOSE NotaFiscalAutomatica;
DEALLOCATE NotaFiscalAutomatica;

