--EXEC P_SCNPC_SAIDA_AUTOMATICA 1
IF EXISTS (
		SELECT *
		FROM SYS.procedures
		WHERE NAME = 'P_SCNPC_SAIDA_AUTOMATICA'
		)
BEGIN
	DROP PROCEDURE P_SCNPC_SAIDA_AUTOMATICA;
END;
GO

CREATE PROCEDURE P_SCNPC_SAIDA_AUTOMATICA (@CD_EMP INT)
AS

BEGIN TRY 
BEGIN

	SET NOCOUNT ON;

	CREATE TABLE #INSERT_TRANSF (
		CD_EMP INT
		,CD_FILIAL INT
		,CD_PROD INT
		,CD_NF INT
		,CD_LOTE INT
		,CD_IT INT
		,DT_EMI_NF DATETIME
		,QTDE_PROD INT
		);

	CREATE TABLE #NF_ERROS (
		CD_EMP INT
		,CD_FILIAL INT
		,CD_NF INT
		);

	DECLARE @CD_FILIAL INT;
	DECLARE @OLD_FILIAL INT;
	DECLARE @DS_ERRO VARCHAR(255);
	DECLARE @VAL_INSERT INT;
	DECLARE @CD_PROD INT;
	DECLARE @NR_NF INT;
	DECLARE @CD_NF INT;
	DECLARE @CD_LOTE INT;
	DECLARE @DT_EMI_NF DATETIME;
	DECLARE @CD_IT INT;
	DECLARE @QTDE_PROD INT;
	DECLARE @DT_VALID DATETIME;
	DECLARE @REGISTRO_MS VARCHAR(30);
	DECLARE @SALDO_SNGPC INT;
	DECLARE @DATA_LIMITE DATETIME;
	DECLARE @SAIDA_AUTOMATICA INT;

	SET @CD_FILIAL = 1;
	


	DECLARE PENDENCIAS_NF CURSOR FAST_FORWARD
	FOR
	SELECT SAI.CD_FILIAL
		,NF.CD_NF
		,NF.NF_NF
		,SAI.CD_PROD
		,(
			SELECT MAX(PL.CD_LOTE)
			FROM EST_PROD_EST_SNGPC_LOTE PL
			WHERE PL.NR_LOTE = ITENS.NR_LOTE
				AND PL.CD_PROD = ITENS.CD_PROD
			) AS CD_LOTE_SNGPC
		,NF.CD_IT
		,CAST(L.QTDE_PROD AS INTEGER) AS QTDE_PROD
		,COALESCE(PLI.VALIDADE, PL.VALIDADE, ITENS.DT_VALID) AS DT_VALID
		,COALESCE(PLI.REGISTRO_MS, PL.REGISTRO_MS, G.REGISTRO_MS) AS REGISTRO_MS
		,PLI.QT_LOTE AS SALDO_SNGPC
		,NF.DT_EMI_NF
	FROM EST_NF_SAI_IT_EST_SNGPC_PENDENTE(NOLOCK) AS SAI
	INNER JOIN V_EST_NF_SAI(NOLOCK) AS NF ON SAI.CD_EMP = NF.CD_EMP
		AND SAI.CD_FILIAL = NF.CD_FILIAL
		AND SAI.CD_NF = NF.CD_NF
		AND SAI.CD_PROD = NF.CD_PROD
	INNER JOIN GLB_MOV_OP(NOLOCK) AS MOV ON MOV.CD_MOV_OP = NF.CD_NT_MOV
		AND MOV.TP_MOV = 10
		AND MOV.tp_mov_op_transa = 1
	-- NO MOMENTO SO NOTAS DE TRANSFERENCIAS
	-- AND  MOV.TP_MOV NOT IN (10, 26, 28)
	-- AND  MOV.TP_MOV_OP_TRANSA = 1
	-- AND  MOV.BX_EST =1
	INNER JOIN EST_NF_SAI_IT_EST_PROD_CPL_LOTE L ON SAI.CD_EMP = SAI.CD_EMP
		AND SAI.CD_FILIAL = SAI.CD_FILIAL
		AND SAI.CD_NF = L.CD_NF
		AND SAI.CD_PROD = L.CD_PROD
	INNER JOIN EST_PROD_CPL_LOTE ITENS ON ITENS.CD_EMP = L.CD_EMP
		AND ITENS.CD_FILIAL = L.CD_FILIAL
		AND ITENS.CD_LOTE = L.CD_LOTE
		AND ITENS.CD_PROD = L.CD_PROD
	INNER JOIN EST_NF_SAI_IT ITX ON ITX.CD_EMP = SAI.CD_EMP
		AND ITX.CD_FILIAL = SAI.CD_FILIAL
		AND ITX.CD_NF = SAI.CD_NF
		AND ITX.CD_PROD = L.CD_PROD
		AND ITX.CD_IT = L.CD_IT
	INNER JOIN EST_PROD P ON P.CD_EMP = ITENS.CD_EMP
		AND P.CD_PROD = ITENS.CD_PROD
	INNER JOIN EST_PROD_EST_SNGPC G ON G.CD_EMP = P.CD_EMP
		AND G.CD_PROD = P.CD_PROD
	LEFT JOIN EST_PROD_EST_SNGPC_LOTE PL ON PL.NR_LOTE = ITENS.NR_LOTE
		AND PL.CD_PROD = ITENS.CD_PROD
	LEFT JOIN EST_PROD_EST_SNGPC_LOTE_CPL PLI ON PLI.CD_EMP = SAI.CD_EMP
		AND PLI.CD_FILIAL = SAI.CD_FILIAL
		AND PLI.CD_LOTE = PL.CD_LOTE
	INNER JOIN PRC_FILIAL FIL ON FIL.CD_FILIAL = SAI.CD_FILIAL
	INNER JOIN PRC_FILIAL_CONFIG(NOLOCK) CF ON CF.CD_FILIAL = SAI.CD_FILIAL
		AND COALESCE(CF.VALOR, 0) = 0
		AND CF.CD_CHAVE = 'Confronta_Quantidade_Lote_SNGPC_Venda'
	WHERE SAI.CD_EMP = @CD_EMP
		AND FIL.FILIAL_MTZ <> 1
	ORDER BY SAI.CD_FILIAL
		,SAI.CD_NF

	OPEN PENDENCIAS_NF;

	FETCH NEXT
	FROM PENDENCIAS_NF
	INTO @CD_FILIAL
		,@CD_NF
		,@NR_NF
		,@CD_PROD
		,@CD_LOTE
		,@CD_IT
		,@QTDE_PROD
		,@DT_VALID
		,@REGISTRO_MS
		,@SALDO_SNGPC
		,@DT_EMI_NF

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @VAL_INSERT = 1;

		IF @OLD_FILIAL <> @CD_FILIAL
		BEGIN
			SET @DATA_LIMITE = (
					SELECT COALESCE(MAX(DT_FINAL), '1900-01-01')
					FROM EST_SNGPC_RELATORIOS_ANVISA(NOLOCK)
					WHERE CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL
						AND ACEITO = 1
					);
			SET @SAIDA_AUTOMATICA = (
					SELECT VALOR
					FROM PRC_FILIAL_CONFIG(NOLOCK)
					WHERE CD_FILIAL = @CD_FILIAL
						AND CD_CHAVE = 'Confronta_Quantidade_Lote_SNGPC_Venda'
					)
		END;

		IF @SALDO_SNGPC IS NULL
		BEGIN
			SET @DS_ERRO = 'Lote não não cadastrado no sngpc'
			SET @VAL_INSERT = 0;
		END;

		IF @SALDO_SNGPC < @QTDE_PROD
		BEGIN
			SET @DS_ERRO = @DS_ERRO + ' Quantidade de lote insuficiente'
			SET @VAL_INSERT = 0;
		END;

		IF @DT_EMI_NF < @DATA_LIMITE
		BEGIN
			SET @DS_ERRO = @DS_ERRO + ' Periodo ja enviado para anvisa'
			SET @VAL_INSERT = 0;
		END;

		IF @VAL_INSERT = 1
		BEGIN
			INSERT INTO #INSERT_TRANSF (
				CD_EMP
				,CD_FILIAL
				,CD_PROD
				,CD_NF
				,CD_LOTE
				,CD_IT
				,DT_EMI_NF
				,QTDE_PROD
				)
			VALUES (
				@CD_EMP
				,@CD_FILIAL
				,@CD_PROD
				,@CD_NF
				,@CD_LOTE
				,@CD_IT
				,@DT_EMI_NF
				,@QTDE_PROD
				)
		END
		ELSE
		BEGIN
			PRINT 'INSERINDO ERRO: ' + CAST(@CD_FILIAL AS VARCHAR(30)) + '  NOTA ' + CAST(@CD_NF AS VARCHAR(30)) + ' PRODUTO : ' + CAST(@CD_PROD AS VARCHAR(30)) + ' LOTE ' + CAST(@CD_LOTE AS VARCHAR(30)) + ' ' + @DS_ERRO;

			INSERT INTO #NF_ERROS (
				CD_EMP
				,CD_FILIAL
				,CD_NF
				)
			VALUES (
				@CD_EMP
				,@CD_FILIAL
				,@CD_NF
				);

			INSERT INTO EST_SNGPC_PEND_SAIDA_NF_AUDIT (
				CD_EMP
				,CD_FILIAL
				,CD_NF
				,DT_CAD
				,DS_ERRO
				)
			VALUES (
				@CD_EMP
				,@CD_FILIAL
				,@CD_NF
				,GETDATE()
				,@DS_ERRO
				)
		END

		SET @OLD_FILIAL = @CD_FILIAL;

		FETCH NEXT
		FROM PENDENCIAS_NF
		INTO @CD_FILIAL
			,@CD_NF
			,@NR_NF
			,@CD_PROD
			,@CD_LOTE
			,@CD_IT
			,@QTDE_PROD
			,@DT_VALID
			,@REGISTRO_MS
			,@SALDO_SNGPC
			,@DT_EMI_NF
	END;

	CLOSE PENDENCIAS_NF;

	DEALLOCATE PENDENCIAS_NF;

	DECLARE PERSITIR_TRANSF CURSOR FAST_FORWARD
	FOR
	SELECT *
	FROM #INSERT_TRANSF
	WHERE NOT EXISTS (
			SELECT CD_NF
			FROM #NF_ERROS
			WHERE #NF_ERROS.CD_EMP = #INSERT_TRANSF.CD_EMP
				AND #NF_ERROS.CD_FILIAL = #INSERT_TRANSF.CD_FILIAL
				AND #NF_ERROS.CD_NF = #INSERT_TRANSF.CD_NF
			)

	OPEN PERSITIR_TRANSF;

	FETCH NEXT
	FROM PERSITIR_TRANSF
	INTO @CD_EMP
		,@CD_FILIAL
		,@CD_PROD
		,@CD_NF
		,@CD_LOTE
		,@CD_IT
		,@DT_EMI_NF
		,@QTDE_PROD

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC P_EST_SNGPC_SAIDA_TRANSF_INCLUIR 0
			,@CD_EMP
			,@CD_FILIAL
			,@CD_LOTE
			,@DT_EMI_NF
			,@QTDE_PROD
			,1
			,@CD_NF
			,@CD_PROD
			,@CD_IT
			,'AUTO_1.0';

		PRINT 'INSERINDO TRANFERENCIA FILIAL: ' + CAST(@CD_FILIAL AS VARCHAR(30)) + '  NOTA ' + CAST(@CD_NF AS VARCHAR(30)) + ' PRODUTO : ' + CAST(@CD_PROD AS VARCHAR(30)) + ' LOTE ' + CAST(@CD_LOTE AS VARCHAR(30));

		FETCH NEXT
		FROM PERSITIR_TRANSF
		INTO @CD_EMP
			,@CD_FILIAL
			,@CD_PROD
			,@CD_NF
			,@CD_LOTE
			,@CD_IT
			,@DT_EMI_NF
			,@QTDE_PROD
	END;

	CLOSE PERSITIR_TRANSF;

	DEALLOCATE PERSITIR_TRANSF;

	END

END TRY 
BEGIN CATCH

   DECLARE @ErrorMessage VARCHAR(4000) = ERROR_MESSAGE();
   DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
   DECLARE @ErrorState INT = ERROR_STATE();

   INSERT INTO ADM_ERRO ( numero ,severidade ,estado ,rotina ,linha ,mensagem ,dtErro )
	  SELECT
		 ERROR_NUMBER()			AS numero
		 ,@ErrorSeverity		AS severidade
		 ,@ErrorState			AS estado
		 ,ERROR_PROCEDURE()		AS rotina
		 ,ERROR_LINE()			AS linha
		 ,@ErrorMessage			AS mensagem
		 ,GETDATE()				AS dtErro ;

   RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;
