IF OBJECT_ID('P_PROCESSA_RETORNO_EST_PED_VD_WMS') IS NOT NULL
BEGIN
	DROP PROCEDURE P_PROCESSA_RETORNO_EST_PED_VD_WMS
END
GO
GO
GO

CREATE PROCEDURE
	P_PROCESSA_RETORNO_EST_PED_VD_WMS
AS

DECLARE @CD_PED_WMS			INT
DECLARE @CD_EMP				INT
DECLARE @CD_FILIAL			INT
DECLARE @CD_PED				INT

DECLARE @CD_PROD			INT
DECLARE @CD_PROD_AUX		INT
DECLARE @QT_PROD			MONEY
DECLARE @NR_LOTE			VARCHAR(50)
DECLARE @CD_LOTE			INT
DECLARE @DT_FABR			DATETIME
DECLARE @DT_VENCTO			DATETIME


DECLARE @EST_PED_VD_IT_FISCAL TABLE(
	CD_EMP						INT
	,CD_FILIAL					INT
	,CD_PED						INT
	,CD_PROD					INT
	,QT_IT						MONEY
	,QT_IT_CHECK				MONEY
	,VLR_TOT_IT					MONEY
	,ALIQ_ICMS					MONEY
	,VLR_BASE_ICMS_IT			MONEY
	,VLR_ICMS_IT				MONEY
	,VLR_BASE_ICMS_SUBS			MONEY
	,VLR_ICMS_SUBS				MONEY
	,PERC_REDU_BASE_ICMS		MONEY
	,PERC_REDU_BASE_SUBS		MONEY
	,VLR_BASE_ICMS_RET			MONEY
	,VLR_ICMS_RETIDO			MONEY
	,VLR_OUTROS_DESPESAS		MONEY
	,VLR_IMPOSTO_REA			MONEY
	,VLR_BASE_ICMS_OP			MONEY
	,VLR_ICMS_OP				MONEY
	,PERC_ICMS_OP				MONEY
)


DECLARE
	cursor_pedidos_pendentes
CURSOR FOR
SELECT DISTINCT
	P.CD_PED_WMS,
	PS.CD_EMP,
	PS.CD_FILIAL,
	PS.CD_PED
FROM
	EST_WMS_SAI_RET_PEND P
	INNER JOIN EST_PED_VD_EST_WMS_SAI PS ON
		P.CD_PED_WMS = PS.CD_PED_WMS
	INNER JOIN EST_WMS_SAI_RET RET ON
		P.CD_PED_WMS = RET.CD_PED_WMS
--WHERE
--	PS.CD_FILIAL = 999
--	AND PS.CD_PED = 2643389
ORDER BY
	P.CD_PED_WMS

OPEN cursor_pedidos_pendentes

FETCH NEXT FROM cursor_pedidos_pendentes INTO @CD_PED_WMS, @CD_EMP, @CD_FILIAL, @CD_PED

WHILE @@FETCH_STATUS <> -1
BEGIN

	DECLARE
		cursor_pedidos
	CURSOR FOR

	SELECT
		RET.CD_PROD,
		RET.QT_PROD,
		COALESCE(RET.LOTE, '') AS LOTE,
		RET.DT_FABR,
		RET.DT_VENCTO
	FROM
		EST_WMS_SAI_RET_PEND P
		INNER JOIN EST_PED_VD_EST_WMS_SAI PS ON
			P.CD_PED_WMS = PS.CD_PED_WMS
		INNER JOIN EST_PED_VD PED ON
			PS.CD_EMP = PED.CD_EMP
			AND PS.CD_FILIAL = PED.CD_FILIAL
			AND PS.CD_PED = PED.CD_PED
		INNER JOIN EST_WMS_SAI_RET RET ON
			P.CD_PED_WMS = RET.CD_PED_WMS
	WHERE
		P.CD_PED_WMS = @CD_PED_WMS
	ORDER BY
		PED.CD_FILIAL,
		PED.CD_PED,
		RET.CD_PROD

	OPEN cursor_pedidos

	FETCH NEXT FROM cursor_pedidos INTO @CD_PROD, @QT_PROD, @NR_LOTE, @DT_FABR, @DT_VENCTO

	IF @@FETCH_STATUS <> -1
	BEGIN
		IF NOT EXISTS (SELECT * FROM EST_PED_VD_CONF WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED)
		BEGIN
			INSERT INTO
				EST_PED_VD_CONF (CD_EMP, CD_FILIAL, CD_PED, CD_USU, DT_CAD, DT_REF)
			VALUES 
				(@CD_EMP, @CD_FILIAL, @CD_PED, 1, CONVERT(DATE, GETDATE()), GETDATE())
		END

		UPDATE
			EST_PED_VD_CONF_CPL
		SET
			QT_IT_CHECK = 0
		WHERE
			CD_EMP = @CD_EMP
			AND CD_FILIAL = @CD_FILIAL
			AND CD_PED = @CD_PED
	END

	WHILE @@FETCH_STATUS <> -1
	BEGIN
		SET @CD_PROD_AUX = @CD_PROD
		
		WHILE @CD_PROD_AUX = @CD_PROD AND @@FETCH_STATUS <> -1
		BEGIN

			IF NOT EXISTS (SELECT * FROM EST_PED_VD_CONF_CPL WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED AND CD_PROD = @CD_PROD)
			BEGIN
				INSERT INTO
					EST_PED_VD_CONF_CPL (CD_EMP, CD_FILIAL, CD_PED, CD_PROD, QT_IT_CHECK)
				VALUES
					(@CD_EMP, @CD_FILIAL, @CD_PED, @CD_PROD, @QT_PROD)
			END ELSE
			BEGIN
				UPDATE
					EST_PED_VD_CONF_CPL
				SET
					QT_IT_CHECK = QT_IT_CHECK + @QT_PROD
				WHERE 
					CD_EMP = @CD_EMP
					AND CD_FILIAL = @CD_FILIAL
					AND CD_PED = @CD_PED
					AND CD_PROD = @CD_PROD
			END

			IF NOT @NR_LOTE = ''
			BEGIN
				SET @CD_LOTE = 0

				SELECT
					@CD_LOTE = COALESCE(CD_LOTE, 0)
				FROM
					EST_PROD_CPL_LOTE
				WHERE
					CD_EMP = @CD_EMP
					AND CD_FILIAL = @CD_FILIAL
					AND CD_PROD = @CD_PROD
					AND NR_LOTE = @NR_LOTE

				IF @CD_LOTE = 0
				BEGIN
					
					SELECT
						@CD_LOTE = COALESCE(MAX(CD_LOTE), 0) + 1
					FROM
						EST_PROD_CPL_LOTE
					WHERE
						CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL

					INSERT INTO
						EST_PROD_CPL_LOTE (CD_EMP, CD_FILIAL, CD_LOTE, CD_PROD, NR_LOTE, DT_VALID, QTDE_PROD, DT_FABR, DT_CAD)
					VALUES
						(@CD_EMP, @CD_FILIAL, @CD_LOTE, @CD_PROD, @NR_LOTE, @DT_VENCTO, @QT_PROD, @DT_FABR, GETDATE())

				END

				IF NOT EXISTS (SELECT * FROM EST_PED_VD_IT_EST_PROD_CPL_LOTE WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED AND CD_PROD = @CD_PROD AND CD_LOTE = @CD_LOTE)
				BEGIN
					-- Tag #I001
					IF EXISTS ( SELECT * FROM EST_PED_VD_IT WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED AND CD_PROD = @CD_PROD )
					BEGIN 
						INSERT INTO
							EST_PED_VD_IT_EST_PROD_CPL_LOTE (CD_EMP, CD_FILIAL, CD_PED, CD_PROD, CD_LOTE, QTDE_PROD, DT_CAD , CD_USU , CD_USU_ALT , DT_ALT )
						VALUES
							(@CD_EMP, @CD_FILIAL, @CD_PED, @CD_PROD, @CD_LOTE, @QT_PROD, GETDATE(), 1 , 1, GETDATE())
					END 				
				END
			END

			FETCH NEXT FROM cursor_pedidos INTO @CD_PROD, @QT_PROD, @NR_LOTE, @DT_FABR, @DT_VENCTO
		END
	END


	CLOSE cursor_pedidos
	DEALLOCATE cursor_pedidos

	IF NOT EXISTS (SELECT * FROM EST_NF_SAI_PED_VD WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED)
	BEGIN
		INSERT INTO
			@EST_PED_VD_IT_FISCAL(
				CD_EMP
				,CD_FILIAL
				,CD_PED
				,CD_PROD
				,QT_IT
				,QT_IT_CHECK
				,VLR_TOT_IT
				,ALIQ_ICMS
				,VLR_BASE_ICMS_IT
				,VLR_ICMS_IT
				,VLR_BASE_ICMS_SUBS
				,VLR_ICMS_SUBS
				,PERC_REDU_BASE_ICMS
				,PERC_REDU_BASE_SUBS
				,VLR_BASE_ICMS_RET
				,VLR_ICMS_RETIDO
				,VLR_OUTROS_DESPESAS
				,VLR_IMPOSTO_REA
				,VLR_BASE_ICMS_OP
				,VLR_ICMS_OP
				,PERC_ICMS_OP)
		SELECT
			V_EST_PED_VD_IT_FISCAL.CD_EMP
			,V_EST_PED_VD_IT_FISCAL.CD_FILIAL
			,V_EST_PED_VD_IT_FISCAL.CD_PED
			,V_EST_PED_VD_IT_FISCAL.CD_PROD
			,V_EST_PED_VD_IT_FISCAL.QT_IT
			,COALESCE(EST_PED_VD_CONF_CPL.QT_IT_CHECK, 0) AS QT_IT_CHECK
			,V_EST_PED_VD_IT_FISCAL.VLR_TOT_IT
			,V_EST_PED_VD_IT_FISCAL.ALIQ_ICMS
			,V_EST_PED_VD_IT_FISCAL.VLR_BASE_ICMS_IT
			,V_EST_PED_VD_IT_FISCAL.VLR_ICMS_IT
			,V_EST_PED_VD_IT_FISCAL.VLR_BASE_ICMS_SUBS
			,V_EST_PED_VD_IT_FISCAL.VLR_ICMS_SUBS
			,V_EST_PED_VD_IT_FISCAL.PERC_REDU_BASE_ICMS
			,V_EST_PED_VD_IT_FISCAL.PERC_REDU_BASE_SUBS
			,V_EST_PED_VD_IT_FISCAL.VLR_BASE_ICMS_RET
			,V_EST_PED_VD_IT_FISCAL.VLR_ICMS_RETIDO
			,V_EST_PED_VD_IT_FISCAL.VLR_OUTROS_DESPESAS
			,V_EST_PED_VD_IT_FISCAL.VLR_IMPOSTO_REA
			,V_EST_PED_VD_IT_FISCAL.VLR_BASE_ICMS_OP
			,V_EST_PED_VD_IT_FISCAL.VLR_ICMS_OP
			,V_EST_PED_VD_IT_FISCAL.PERC_ICMS_OP
		FROM
			V_EST_PED_VD_IT_FISCAL
			LEFT JOIN EST_PED_VD_CONF_CPL ON
				V_EST_PED_VD_IT_FISCAL.CD_EMP = EST_PED_VD_CONF_CPL.CD_EMP
				AND V_EST_PED_VD_IT_FISCAL.CD_FILIAL = EST_PED_VD_CONF_CPL.CD_FILIAL
				AND V_EST_PED_VD_IT_FISCAL.CD_PED = EST_PED_VD_CONF_CPL.CD_PED
				AND V_EST_PED_VD_IT_FISCAL.CD_PROD = EST_PED_VD_CONF_CPL.CD_PROD
		WHERE
			V_EST_PED_VD_IT_FISCAL.CD_EMP = @CD_EMP
			AND V_EST_PED_VD_IT_FISCAL.CD_FILIAL = @CD_FILIAL
			AND V_EST_PED_VD_IT_FISCAL.CD_PED = @CD_PED
			AND V_EST_PED_VD_IT_FISCAL.QT_IT <> COALESCE(EST_PED_VD_CONF_CPL.QT_IT_CHECK, 0)

		IF EXISTS (SELECT * FROM @EST_PED_VD_IT_FISCAL)
		BEGIN

			UPDATE
				EST_PED_VD_IT
			SET
				EST_PED_VD_IT.QT_SOL = EST_PED_VD_IT.QT_IT,
				EST_PED_VD_IT.QT_IT = 0,
				VLR_TOT_IT = 0
			FROM
				EST_PED_VD_IT
			WHERE
				CD_EMP = @CD_EMP
				AND CD_FILIAL = @CD_FILIAL
				AND CD_PED = @CD_PED
				AND NOT EXISTS (
					SELECT
						*
					FROM
						EST_PED_VD_CONF_CPL
					WHERE
						EST_PED_VD_CONF_CPL.CD_EMP = EST_PED_VD_IT.CD_EMP
						AND EST_PED_VD_CONF_CPL.CD_FILIAL = EST_PED_VD_IT.CD_FILIAL
						AND EST_PED_VD_CONF_CPL.CD_PED = EST_PED_VD_IT.CD_PED
						AND EST_PED_VD_CONF_CPL.CD_PROD = EST_PED_VD_IT.CD_PROD)

			print 'atualizando valores dos itens'
			UPDATE
				EST_PED_VD_IT
			SET
				EST_PED_VD_IT.QT_SOL = EST_PED_VD_IT.QT_IT,
				EST_PED_VD_IT.QT_IT = PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_TOT_IT = PED_IT_FISCAL.VLR_TOT_IT * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_BASE_ICMS_IT = PED_IT_FISCAL.VLR_BASE_ICMS_IT * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_ICMS_IT = PED_IT_FISCAL.VLR_ICMS_IT * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_BASE_ICMS_SUBS = PED_IT_FISCAL.VLR_BASE_ICMS_SUBS * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_ICMS_SUBS = PED_IT_FISCAL.VLR_ICMS_SUBS * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_BASE_ICMS_RET = PED_IT_FISCAL.VLR_BASE_ICMS_RET * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_ICMS_RETIDO = PED_IT_FISCAL.VLR_ICMS_RETIDO * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_OUTROS_DESPESAS = PED_IT_FISCAL.VLR_OUTROS_DESPESAS * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_IMPOSTO_REA = PED_IT_FISCAL.VLR_IMPOSTO_REA * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_BASE_ICMS_OP = PED_IT_FISCAL.VLR_BASE_ICMS_OP * PED_IT_FISCAL.QT_IT_CHECK,
				EST_PED_VD_IT.VLR_ICMS_OP = PED_IT_FISCAL.VLR_ICMS_OP * PED_IT_FISCAL.QT_IT_CHECK

			FROM
				EST_PED_VD_IT 
				INNER JOIN @EST_PED_VD_IT_FISCAL PED_IT_FISCAL ON
					EST_PED_VD_IT.CD_EMP = PED_IT_FISCAL.CD_EMP
					AND EST_PED_VD_IT.CD_FILIAL = PED_IT_FISCAL.CD_FILIAL
					AND EST_PED_VD_IT.CD_PED = PED_IT_FISCAL.CD_PED
					AND EST_PED_VD_IT.CD_PROD = PED_IT_FISCAL.CD_PROD

			print 'atualizando valores do cabeçalho'
			UPDATE
				EST_PED_VD
			SET
				VLR_TOT_PROD = IT.VLR_TOTAL,
				VLR_TOT_PED = IT.VLR_TOTAL + IT.VLR_ICMS_SUBS_TOTAL,
				VLR_BASE_ICMS = IT.VLR_BASE_ICMS_TOTAL,
				VLR_ICMS = IT.VLR_ICMS_TOTAL,
				VLR_BASE_ICMS_ST = IT.VLR_BASE_ICMS_ST_TOTAL,
				VLR_ICMS_SUBS = IT.VLR_ICMS_SUBS_TOTAL,
				--NAO EXISTE CAMPO PARA BASE ICMS RETIDO
				VLR_ICMS_RETIDO = IT.VLR_ICMS_RETIDO_TOTAL
				--NAO EXISTE CAMPO PARA OUTRAS DESPESAS
				--NAO EXISTE CAMPO PARA ICMS REA
				--NAO EXISTE CAMPO PARA BASE ICMS OP
				--NAO EXISTE CAMPO PARA ICMS OP

			FROM
				EST_PED_VD
				INNER JOIN (
					SELECT
						CD_EMP,
						CD_FILIAL,
						CD_PED,
						ROUND(SUM(VLR_TOT_IT), 4) AS VLR_TOTAL,
						ROUND(SUM(VLR_BASE_ICMS_IT), 4) AS VLR_BASE_ICMS_TOTAL,
						ROUND(SUM(VLR_ICMS_IT), 4) AS VLR_ICMS_TOTAL,
						ROUND(SUM(VLR_BASE_ICMS_SUBS), 4) AS VLR_BASE_ICMS_ST_TOTAL,
						ROUND(SUM(VLR_ICMS_SUBS), 4) AS VLR_ICMS_SUBS_TOTAL,
						--NAO EXISTE CAMPO PARA BASE ICMS RETIDO
						ROUND(SUM(VLR_ICMS_RETIDO), 4) AS VLR_ICMS_RETIDO_TOTAL
						--NAO EXISTE CAMPO PARA OUTRAS DESPESAS
						--NAO EXISTE CAMPO PARA ICMS REA
						--NAO EXISTE CAMPO PARA BASE ICMS OP
						--NAO EXISTE CAMPO PARA ICMS OP
					FROM
						EST_PED_VD_IT
					GROUP BY
						CD_EMP,
						CD_FILIAL,
						CD_PED) IT ON
					EST_PED_VD.CD_EMP = IT.CD_EMP
					AND EST_PED_VD.CD_FILIAL = IT.CD_FILIAL
					AND EST_PED_VD.CD_PED = IT.CD_PED
			WHERE
				EST_PED_VD.CD_EMP = @CD_EMP
				AND EST_PED_VD.CD_FILIAL = @CD_FILIAL
				AND EST_PED_VD.CD_PED = @CD_PED
		END

		DELETE FROM @EST_PED_VD_IT_FISCAL
	END

	DELETE FROM
		EST_WMS_SAI_RET_PEND 
	WHERE 
		CD_PED_WMS = @CD_PED_WMS

	--Liberando a pre-fatura para realizar a geracao da nota fiscal
	UPDATE
		EST_PED_VD_PENDENCIA
	SET 
		FLAG_REALIZAR_FAT=1
	WHERE
		CD_EMP =@CD_EMP
		AND CD_FILIAL = @CD_FILIAL
		AND CD_PED=@CD_PED
		
	FETCH NEXT FROM cursor_pedidos_pendentes INTO @CD_PED_WMS, @CD_EMP, @CD_FILIAL, @CD_PED
END


CLOSE cursor_pedidos_pendentes
DEALLOCATE cursor_pedidos_pendentes
