IF OBJECT_ID('P_EST_PROD_TRANSF_CORRIGIR_VOLUME') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_EST_PROD_TRANSF_CORRIGIR_VOLUME
	END
GO
--EXEC P_EST_PROD_TRANSF_CORRIGIR_VOLUME
/*
	Esta procedure roda dentro da P_EST_PROD_PENDENCIA_ENTRADA_FILIAL
*/
CREATE PROCEDURE P_EST_PROD_TRANSF_CORRIGIR_VOLUME 
WITH ENCRYPTION
AS
SET NOCOUNT ON
CREATE TABLE #RsTransf (CD_EMP INT,
						CD_FILIAL INT,
						CD_CTR INT,
						QTDE_VOLUMES MONEY)
INSERT INTO #RsTransf
(CD_EMP,
CD_FILIAL,
CD_CTR,
QTDE_VOLUMES)
SELECT
	TRANSF.CD_EMP,
	TRANSF.CD_FILIAL,
	TRANSF.CD_CTR,
	MAX(ISNULL(RET.QT_VOLUME,0)) AS QT_VOLUME
FROM
	EST_WMS_SAI_RET RET INNER JOIN EST_PROD_TRANSF_EST_WMS_SAI TRANSF ON
	RET.CD_PED_WMS = TRANSF.CD_PED_WMS
	INNER JOIN EST_PROD_TRANSF ON
	TRANSF.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
	TRANSF.CD_FILIAL = EST_PROD_TRANSF.CD_FILIAL AND 
	TRANSF.CD_CTR = EST_PROD_TRANSF.CD_CTR  
WHERE 
	RET.QT_PROD >0
	AND EST_PROD_TRANSF.DT_TRANSF >= GETDATE()-5
GROUP BY 
    TRANSF.CD_EMP,
    TRANSF.CD_FILIAL,
    TRANSF.CD_CTR,
	EST_PROD_TRANSF.QTDE_VOLUMES
HAVING 
	MAX(ISNULL(RET.QT_VOLUME,0)) <> ISNULL(EST_PROD_TRANSF.QTDE_VOLUMES,0)
	AND MAX(ISNULL(RET.QT_VOLUME,0)) > 0


UPDATE
	T
SET
	T.QTDE_VOLUMES = v.QTDE_VOLUMES
FROM
	EST_PROD_TRANSF T 
	INNER JOIN #RsTransf v ON
	T.CD_EMP = v.CD_EMP AND
	T.CD_FILIAL = v.CD_FILIAL AND
	T.CD_CTR = v.CD_CTR 
	LEFT JOIN EST_PROD_TRANSF_PED_VD TV ON
	T.CD_EMP		= TV.CD_EMP AND
	T.CD_FILIAL 	= TV.CD_FILIAL AND
	T.CD_CTR		= TV.CD_CTR  
where
	t.STS_TRANSF IN (0,1) 
	AND TV.CD_EMP IS NULL -- q nao tenha pre fatura, senao dispara a trigger de bloqueio e da erro

DROP TABLE #RsTransf