DROP FUNCTION dbo.fn_GetCambioDocFrmov
GO

CREATE FUNCTION dbo.fn_GetCambioDocFrmov(@SEQ INT) RETURNS DECIMAL(18,12)	
 
BEGIN
	DECLARE @RAD			INT
	DECLARE @SEQ_MOEDA		INT
	DECLARE @CAMBIO			DECIMAL(18,12)

	SELECT @RAD=ISNULL(RAD,0),@SEQ_MOEDA=SEQ_MOEDA
	FROM FRMOV 
	WHERE SEQ=@SEQ

	IF @RAD=1 
	BEGIN
		IF @SEQ_MOEDA=0
		BEGIN
			SELECT @CAMBIO=ISNULL(CAMBIO,0)
			FROM FRMOV 
			WHERE SEQ_MOEDA=@SEQ
		END
		ELSE
		BEGIN
			SELECT @CAMBIO=ISNULL(CAMBIO,0)
			FROM FRMOV 
			WHERE SEQ=@SEQ_MOEDA
		END
	END
	ELSE
	BEGIN
		SELECT @CAMBIO=ISNULL(CAMBIO,0)
		FROM FRMOV 
		WHERE SEQ=@SEQ
	END

	RETURN(ROUND(@CAMBIO,12))

END

GO

DROP FUNCTION dbo.fn_GetCambioDocClmov
GO

CREATE FUNCTION dbo.fn_GetCambioDocClmov(@SEQ INT) RETURNS DECIMAL(18,12)	
 
BEGIN
	DECLARE @RAD			INT
	DECLARE @SEQ_MOEDA		INT
	DECLARE @CAMBIO			DECIMAL(18,12)

	SELECT @RAD=ISNULL(RAD,0),@SEQ_MOEDA=SEQ_MOEDA
	FROM CLMOV 
	WHERE SEQ=@SEQ

	IF @RAD=1 
	BEGIN
		IF @SEQ_MOEDA=0
		BEGIN
			SELECT @CAMBIO=ISNULL(CAMBIO,0)
			FROM CLMOV 
			WHERE SEQ_MOEDA=@SEQ
		END
		ELSE
		BEGIN
			SELECT @CAMBIO=ISNULL(CAMBIO,0)
			FROM CLMOV 
			WHERE SEQ=@SEQ_MOEDA
		END
	END
	ELSE
	BEGIN
		SELECT @CAMBIO=ISNULL(CAMBIO,0)
		FROM CLMOV 
		WHERE SEQ=@SEQ
	END

	RETURN(ROUND(@CAMBIO,12))

END

GO

DROP PROCEDURE [dbo].[sp_DifCmbCli]
GO
CREATE PROCEDURE [dbo].[sp_DifCmbCli](@SEQ INT)
AS 
BEGIN

	DECLARE @INDICE			INT
	DECLARE @CLMOV_SEQ		INT
	DECLARE @CLMOV_SEQ_REFL	INT
	DECLARE @SEQ_REFL		INT
	DECLARE @SEQ_MOEDA		INT
	DECLARE @MOV			SMALLINT
	DECLARE @RAD			SMALLINT
	DECLARE @RAD_ARRED		SMALLINT
	DECLARE @CAMBIO			DECIMAL(18,12)
	DECLARE @CAMBIO_ASSOC	DECIMAL(18,12)
	DECLARE @VALOR			DECIMAL(18,4)
	DECLARE @VALOR_REFL		DECIMAL(18,4)
	DECLARE @VL_DIF_CMB		DECIMAL(18,4)
	DECLARE @DESIG_MOV_DIF  VARCHAR(50)
	DECLARE @DATADOC		INT
	DECLARE @DATAVENC		INT
	DECLARE @SEQ_DIF_CMB    INT

	SELECT @RAD=ISNULL(RAD,0),@CAMBIO=dbo.fn_GetCambioDocClmov(SEQ)
	FROM CLMOV 
	WHERE SEQ=@SEQ

	SELECT @SEQ_REFL=ISNULL(SEQ,-1),@RAD_ARRED=RAD
	FROM CLMOV 
	WHERE SEQ_MOEDA=@SEQ

	DELETE FROM DIF_CMB_CLI_CAB WHERE SEQ=@SEQ
	DELETE FROM DIF_CMB_CLI_MOV WHERE SEQ=@SEQ_REFL

	CREATE TABLE #AUX_DIF_CMB_CLI
	(INDICE INT IDENTITY(1,1),
		CLMOV_SEQ INT,
		CLMOV_SEQ_REFL INT
	)

	INSERT INTO #AUX_DIF_CMB_CLI(CLMOV_SEQ)
	SELECT CLMOV_SEQ
	FROM CLCRD INNER JOIN CLMOV ON 
	CLCRD.CLMOV_SEQ=CLMOV.SEQ
	WHERE CLCRD.SEQ=@SEQ
	AND NOT MOV IN (dbo.fn_GetGlobalGciTdoc('CL_DifCambial_C'),dbo.fn_GetGlobalGciTdoc('CL_DifCambial_D'))

	SELECT @INDICE=MAX(INDICE) 
	FROM #AUX_DIF_CMB_CLI

	WHILE @INDICE>0
	BEGIN
		
		SELECT @CLMOV_SEQ=CLMOV_SEQ 
		FROM #AUX_DIF_CMB_CLI
		WHERE INDICE=@INDICE

		SELECT @CLMOV_SEQ_REFL=SEQ_MOEDA FROM CLMOV WHERE SEQ=@CLMOV_SEQ
		IF @CLMOV_SEQ_REFL=0
		BEGIN
			SELECT @CLMOV_SEQ_REFL=SEQ FROM CLMOV WHERE SEQ_MOEDA=@CLMOV_SEQ
		END
		UPDATE #AUX_DIF_CMB_CLI SET CLMOV_SEQ_REFL=@CLMOV_SEQ_REFL WHERE INDICE=@INDICE

		SET @INDICE=@INDICE-1
	END
	
	SELECT @INDICE=MAX(INDICE) 
	FROM #AUX_DIF_CMB_CLI

	WHILE @INDICE>0
	BEGIN
		SELECT @CLMOV_SEQ=CLMOV_SEQ,@CLMOV_SEQ_REFL=CLMOV_SEQ_REFL
		FROM #AUX_DIF_CMB_CLI
		WHERE INDICE=@INDICE AND NOT CLMOV_SEQ_REFL=0
		
		IF NOT @@ROWCOUNT=0 
		BEGIN

			SET @VL_DIF_CMB=0
			
			SET @CAMBIO_ASSOC=dbo.fn_GetCambioDocClmov(@CLMOV_SEQ_REFL)

			SELECT @VALOR=ISNULL(CASE WHEN MOV BETWEEN dbo.fn_GetGlobalGciTdoc('CL_Debito_Ini') AND dbo.fn_GetGlobalGciTdoc('CL_Debito_Fim') THEN  CLCRD.VALOR  ELSE -CLCRD.VALOR END,0)
			FROM CLCRD 
			INNER JOIN CLMOV ON CLCRD.CLMOV_SEQ=CLMOV.SEQ
			WHERE CLCRD.SEQ=@SEQ AND CLCRD.CLMOV_SEQ=@CLMOV_SEQ

			SELECT @VALOR_REFL=ISNULL(CASE WHEN MOV BETWEEN dbo.fn_GetGlobalGciTdoc('CL_Debito_Ini') AND dbo.fn_GetGlobalGciTdoc('CL_Debito_Fim') THEN  CLCRD.VALOR  ELSE -CLCRD.VALOR END,0)
			FROM CLCRD 
			INNER JOIN CLMOV ON CLCRD.CLMOV_SEQ=CLMOV.SEQ
			WHERE CLCRD.SEQ=@SEQ_REFL AND CLCRD.CLMOV_SEQ=@CLMOV_SEQ_REFL

			IF NOT @RAD=1
			BEGIN
				SET @VL_DIF_CMB= @VALOR*@CAMBIO-@VALOR*@CAMBIO_ASSOC
			END
			ELSE
			BEGIN
				SET @VL_DIF_CMB= @VALOR/@CAMBIO-@VALOR/@CAMBIO_ASSOC
			END

			IF NOT @VL_DIF_CMB=0 
			BEGIN
				IF NOT @SEQ_REFL IS NULL AND NOT @CLMOV_SEQ_REFL IS NULL
				BEGIN
					INSERT INTO DIF_CMB_CLI_MOV(SEQ,CLMOV_SEQ,DIF_CMB)
					VALUES(@SEQ_REFL,@CLMOV_SEQ_REFL,ISNULL(@VL_DIF_CMB,0))
				END
			END
		END

		SET @INDICE=@INDICE-1
	END

	DROP TABLE #AUX_DIF_CMB_CLI

	SELECT @VL_DIF_CMB=SUM(DIF_CMB) 
	FROM DIF_CMB_CLI_MOV
	WHERE SEQ=@SEQ_REFL
	
	SET @VL_DIF_CMB=dbo.GetValArredMoeda(@RAD_ARRED,@VL_DIF_CMB)

	IF NOT @VL_DIF_CMB=0
	BEGIN

		INSERT INTO DIF_CMB_CLI_CAB (SEQ,SEQ_REFL,RAD,CAMBIO)
		VALUES (@SEQ,@SEQ_REFL,@RAD,@CAMBIO)

		IF @VL_DIF_CMB<0
		BEGIN
			SET @MOV=dbo.fn_GetGlobalGciTdoc('CL_DifCambial_C')
			SET @VL_DIF_CMB=ABS(@VL_DIF_CMB)
		END
		ELSE
		BEGIN
			SET @MOV=dbo.fn_GetGlobalGciTdoc('CL_DifCambial_D')
		END

		SELECT @DESIG_MOV_DIF=DESIG1 FROM TAB01_MOV_CLI
		WHERE COD=@MOV

		SELECT @SEQ_DIF_CMB=ISNULL(SEQ_CLMOV,0)+1 FROM TBPARAM

		UPDATE TBPARAM SET SEQ_CLMOV=SEQ_CLMOV+1

		UPDATE DIF_CMB_CLI_CAB 
		SET SEQ_DIF_CMB=@SEQ_DIF_CMB
		WHERE SEQ=@SEQ

		INSERT INTO CLMOV(SEQ,RAD,CLIENTE,DATA,DOC,DOCI,DATAVENC,MOV,DESIG,VALOR,VLPAGO,DATAPAG,VEND,COMIS,COMIS_ORI,VLCUSTO,VLLIQ,SUJ1,SUJ2,SUJ3,SUJ4,TXIVA1,TXIVA2,TXIVA3,TXIVA4,IVA1,IVA2,IVA3,IVA4,DESCONTOS,CONTAB,NLANCAMENTO,CONTA_POC,LOJAS,SINAL,SMOV,CAMBIO,CMB_MULTI,SEQ_MOEDA,SEQ_DESCFIN,BCMOV_SEQ,VDMOV_SEQ,CLLET_SEQ,COD_FPAG,CUSTO_COM,MOVDIR,OPER,TERM,DATADIA)
		SELECT @SEQ_DIF_CMB,RAD,CLIENTE,DATA,DOC,DOCI,DATAVENC,@MOV,@DESIG_MOV_DIF,@VL_DIF_CMB,@VL_DIF_CMB,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,CAMBIO,CMB_MULTI,SEQ_MOEDA,0,0,0,0,0,0,0,OPER,TERM,DATADIA
		FROM CLMOV
		WHERE SEQ=@SEQ_REFL

		SELECT @DATADOC=DATA,@DATAVENC=DATAVENC 
		FROM CLMOV 
		WHERE SEQ=@SEQ_REFL

		INSERT INTO CLCRD(SEQ,CLMOV_SEQ,DATA,CLIENTE,DOC,DATADOC,DATAVENC,VALOR)
		SELECT TOP 1 @SEQ_REFL,@SEQ_DIF_CMB,DATA,CLIENTE,DOC,@DATADOC,@DATAVENC,@VL_DIF_CMB 
		FROM CLCRD WHERE SEQ=@SEQ_REFL

	END
	ELSE
	BEGIN
		--ISTO EM PRINCIPIO SÓ ACONTECE SE O SOMATÓRIO DAS DIFERENÇAS CAMBIAIS A DEBITO E A CREDITO DEREM 0
		--SÓ PARA O CASO....
		DELETE FROM DIF_CMB_CLI_MOV
		WHERE SEQ=@SEQ_REFL
	END

END

GO

DROP PROCEDURE [dbo].[sp_DifCmbFORN]
GO
DROP PROCEDURE [dbo].[sp_DifCmbForn]
GO
CREATE PROCEDURE [dbo].[sp_DifCmbForn](@SEQ INT)
AS 
BEGIN

	DECLARE @INDICE			INT
	DECLARE @FRMOV_SEQ		INT
	DECLARE @FRMOV_SEQ_REFL	INT
	DECLARE @SEQ_REFL		INT
	DECLARE @SEQ_MOEDA		INT
	DECLARE @MOV			SMALLINT
	DECLARE @RAD			SMALLINT
	DECLARE @RAD_ARRED		SMALLINT
	DECLARE @CAMBIO			DECIMAL(18,12)
	DECLARE @CAMBIO_ASSOC	DECIMAL(18,12)
	DECLARE @VALOR			DECIMAL(18,4)
	DECLARE @VALOR_REFL		DECIMAL(18,4)
	DECLARE @VL_DIF_CMB		DECIMAL(18,4)
	DECLARE @DESIG_MOV_DIF  VARCHAR(50)
	DECLARE @DATADOC		INT
	DECLARE @DATAVENC		INT
	DECLARE @SEQ_DIF_CMB    INT

	SELECT @RAD=ISNULL(RAD,0),@CAMBIO=dbo.fn_GetCambioDocFrmov(SEQ)
	FROM FRMOV 
	WHERE SEQ=@SEQ

	SELECT @SEQ_REFL=ISNULL(SEQ,-1),@RAD_ARRED=RAD
	FROM FRMOV 
	WHERE SEQ_MOEDA=@SEQ

	DELETE FROM DIF_CMB_FORN_CAB WHERE SEQ=@SEQ
	DELETE FROM DIF_CMB_FORN_MOV WHERE SEQ=@SEQ_REFL

	CREATE TABLE #AUX_DIF_CMB_FORN
	(INDICE INT IDENTITY(1,1),
		FRMOV_SEQ INT,
		FRMOV_SEQ_REFL INT
	)

	INSERT INTO #AUX_DIF_CMB_FORN(FRMOV_SEQ)
	SELECT FRMOV_SEQ
	FROM FRCRD INNER JOIN FRMOV ON 
	FRCRD.FRMOV_SEQ=FRMOV.SEQ
	WHERE FRCRD.SEQ=@SEQ
	AND NOT MOV IN (dbo.fn_GetGlobalGciTdoc('FR_DifCambial_C'),dbo.fn_GetGlobalGciTdoc('FR_DifCambial_D'))

	SELECT @INDICE=MAX(INDICE) 
	FROM #AUX_DIF_CMB_FORN

	WHILE @INDICE>0
	BEGIN
		
		SELECT @FRMOV_SEQ=FRMOV_SEQ 
		FROM #AUX_DIF_CMB_FORN
		WHERE INDICE=@INDICE

		SELECT @FRMOV_SEQ_REFL=SEQ_MOEDA FROM FRMOV WHERE SEQ=@FRMOV_SEQ
		IF @FRMOV_SEQ_REFL=0
		BEGIN
			SELECT @FRMOV_SEQ_REFL=SEQ FROM FRMOV WHERE SEQ_MOEDA=@FRMOV_SEQ
		END
		UPDATE #AUX_DIF_CMB_FORN SET FRMOV_SEQ_REFL=@FRMOV_SEQ_REFL WHERE INDICE=@INDICE

		SET @INDICE=@INDICE-1
	END
	
	SELECT @INDICE=MAX(INDICE) 
	FROM #AUX_DIF_CMB_FORN

	WHILE @INDICE>0
	BEGIN
		SELECT @FRMOV_SEQ=FRMOV_SEQ,@FRMOV_SEQ_REFL=FRMOV_SEQ_REFL
		FROM #AUX_DIF_CMB_FORN
		WHERE INDICE=@INDICE AND NOT FRMOV_SEQ_REFL=0
		
		IF NOT @@ROWCOUNT=0 
		BEGIN

			SET @VL_DIF_CMB=0
			
			SET @CAMBIO_ASSOC=dbo.fn_GetCambioDocFrmov(@FRMOV_SEQ_REFL)

			SELECT @VALOR=ISNULL(CASE WHEN MOV BETWEEN dbo.fn_GetGlobalGciTdoc('FR_Credito_Ini') AND dbo.fn_GetGlobalGciTdoc('FR_Credito_Fim') THEN  FRCRD.VALOR  ELSE -FRCRD.VALOR END,0)
			FROM FRCRD 
			INNER JOIN FRMOV ON FRCRD.FRMOV_SEQ=FRMOV.SEQ
			WHERE FRCRD.SEQ=@SEQ AND FRCRD.FRMOV_SEQ=@FRMOV_SEQ

			SELECT @VALOR_REFL=ISNULL(CASE WHEN MOV BETWEEN dbo.fn_GetGlobalGciTdoc('FR_Credito_Ini') AND dbo.fn_GetGlobalGciTdoc('FR_Credito_Fim') THEN  FRCRD.VALOR  ELSE -FRCRD.VALOR END,0)
			FROM FRCRD 
			INNER JOIN FRMOV ON FRCRD.FRMOV_SEQ=FRMOV.SEQ
			WHERE FRCRD.SEQ=@SEQ_REFL AND FRCRD.FRMOV_SEQ=@FRMOV_SEQ_REFL

			IF NOT @RAD=1
			BEGIN
				SET @VL_DIF_CMB= @VALOR*@CAMBIO-@VALOR*@CAMBIO_ASSOC
			END
			ELSE
			BEGIN
				SET @VL_DIF_CMB= @VALOR/@CAMBIO-@VALOR/@CAMBIO_ASSOC
			END

			IF NOT @VL_DIF_CMB=0 
			BEGIN
				IF NOT @SEQ_REFL IS NULL AND NOT @FRMOV_SEQ_REFL IS NULL
				BEGIN
					INSERT INTO DIF_CMB_FORN_MOV(SEQ,FRMOV_SEQ,DIF_CMB)
					VALUES(@SEQ_REFL,@FRMOV_SEQ_REFL,ISNULL(@VL_DIF_CMB,0))
				END
			END
		END

		SET @INDICE=@INDICE-1
	END

	DROP TABLE #AUX_DIF_CMB_FORN

	SELECT @VL_DIF_CMB=SUM(DIF_CMB) 
	FROM DIF_CMB_FORN_MOV
	WHERE SEQ=@SEQ_REFL
	
	SET @VL_DIF_CMB=dbo.GetValArredMoeda(@RAD_ARRED,@VL_DIF_CMB)

	IF NOT @VL_DIF_CMB=0
	BEGIN

		INSERT INTO DIF_CMB_FORN_CAB (SEQ,SEQ_REFL,RAD,CAMBIO)
		VALUES (@SEQ,@SEQ_REFL,@RAD,@CAMBIO)

		IF @VL_DIF_CMB<0
		BEGIN
			SET @MOV=dbo.fn_GetGlobalGciTdoc('FR_DifCambial_D')
			SET @VL_DIF_CMB=ABS(@VL_DIF_CMB)
		END
		ELSE
		BEGIN
			SET @MOV=dbo.fn_GetGlobalGciTdoc('FR_DifCambial_C')
		END

		SELECT @DESIG_MOV_DIF=DESIG1 FROM TAB01_MOV_FR
		WHERE COD=@MOV

		SELECT @SEQ_DIF_CMB=ISNULL(SEQ_FRMOV,0)+1 FROM TBPARAM

		UPDATE TBPARAM SET SEQ_FRMOV=SEQ_FRMOV+1

		UPDATE DIF_CMB_FORN_CAB 
		SET SEQ_DIF_CMB=@SEQ_DIF_CMB
		WHERE SEQ=@SEQ

		INSERT INTO FRMOV(SEQ,RAD,FORNEC,DATA,DOC,DOCI,DATAVENC,MOV,DESIG,VALOR,VLPAGO,DATAPAG,VEND,COMIS,COMIS_ORI,VLCUSTO,VLLIQ,SUJ1,SUJ2,SUJ3,SUJ4,TXIVA1,TXIVA2,TXIVA3,TXIVA4,IVA1,IVA2,IVA3,IVA4,DESCONTOS,CONTAB,NLANCAMENTO,CONTA_POC,LOJAS,SINAL,SMOV,CAMBIO,CMB_MULTI,SEQ_MOEDA,SEQ_DESCFIN,BCMOV_SEQ,COD_FPAG,CUSTO_COM,MOVDIR,OPER,TERM,DATADIA)
		SELECT @SEQ_DIF_CMB,RAD,FORNEC,DATA,DOC,DOCI,DATAVENC,@MOV,@DESIG_MOV_DIF,@VL_DIF_CMB,@VL_DIF_CMB,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,CAMBIO,CMB_MULTI,SEQ_MOEDA,0,0,0,0,0,OPER,TERM,DATADIA
		FROM FRMOV
		WHERE SEQ=@SEQ_REFL

		SELECT @DATADOC=DATA,@DATAVENC=DATAVENC 
		FROM FRMOV 
		WHERE SEQ=@SEQ_REFL

		INSERT INTO FRCRD(SEQ,FRMOV_SEQ,DATA,FORNEC,DOC,DATADOC,DATAVENC,VALOR)
		SELECT TOP 1 @SEQ_REFL,@SEQ_DIF_CMB,DATA,FORNEC,DOC,@DATADOC,@DATAVENC,@VL_DIF_CMB 
		FROM FRCRD WHERE SEQ=@SEQ_REFL

	END
	ELSE
	BEGIN
		--ISTO EM PRINCIPIO SÓ ACONTECE SE O SOMATÓRIO DAS DIFERENÇAS CAMBIAIS A DEBITO E A CREDITO DEREM 0
		--SÓ PARA O CASO....
		DELETE FROM DIF_CMB_FORN_MOV
		WHERE SEQ=@SEQ_REFL
	END

END