Movimentação Diaria

[Igorfosp]

Bom dia a todos, tenho um relatorio feito aqui na empresa, que está com erro, ele chama uma função que é essa q vou passar o codigo e o mesmo lancamento gerando no fluxus retorna e soma varias vezes, alguem qconsegue ver onde está o erro? Se alguem puder me ajudar vai salvar minha pele aqui no trampo. Vlw

Olha o print do erro:

Uploaded with ImageShack.us

USE [CorporeRM]

GO

/****** Object: UserDefinedFunction [dbo].[FC_ANALISE_MOVIMENTACAO_DIARIA] Script Date: 04/04/2011 09:45:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[FC_ANALISE_MOVIMENTACAO_DIARIA]

( @DATA_INICIAL DATETIME, @DATA_FINAL DATETIME )

RETURNS @TAB TABLE

(

ORDEM INT,

APLICACAO VARCHAR(255),

CODAPLICACAO VARCHAR(255),

CODCOLIGADA INT,

CODFILIAL INT,

CODLOC VARCHAR(255),

NOME VARCHAR(255),

CODTMV VARCHAR(15),

REFERENCIA INT,

CODTDO VARCHAR(15),

CODCFO VARCHAR(15),

FORNECEDOR VARCHAR(255),

VENCIMENTO DATETIME,

DATAEMISSAO DATETIME,

DATACRIACAO DATETIME,

NUMERODOCUMENTO VARCHAR(255),

VALORLIQUIDO NUMERIC(15,2),

USUARIO VARCHAR(255),

CODNIVEL1 VARCHAR(15),

DESCNIVEL1 VARCHAR(255),

CODNIVEL2 VARCHAR(15),

DESCNIVEL2 VARCHAR(255),

CODNIVEL3 VARCHAR(15),

DESCNIVEL3 VARCHAR(255),

CODIGONATUREZA VARCHAR(15),

NATUREZA VARCHAR(255),

NUMEROSEQUENCIAL INT,

CODIGOPRD VARCHAR(15),

PRODUTO VARCHAR(255),

HISTORICO VARCHAR(255),

CODCCUSTO VARCHAR(255),

CENTRODECUSTO VARCHAR(255),

QUANTIDADE VARCHAR(15),

PRECOUNITARIO NUMERIC(15,2),

VALOR NUMERIC(15,2)

)

AS

BEGIN

--DECLARE @DATAINI DATETIME

--DECLARE @DATAFIM DATETIME

--

--SET @DATAINI = @DATA_INICIAL

--SET @DATAFIM = @DATA_FINAL

INSERT INTO @TAB

SELECT

'1' AS ORDEM,

'NUCLEUS' AS APLICACAO,

'T' AS CODAPLICACAO,

TMOV.CODCOLIGADA,

TMOV.CODFILIAL,

TMOV.CODLOC,

GFILIAL.NOME,

TMOV.CODTMV,

TMOV.IDMOV AS REFERENCIA,

ISNULL (TMOV.CODTDO, TMOV.SERIE) AS CODTDO,

TMOV.CODCFO,

FCFO.NOME AS FORNECEDOR,

TMOV.DATAEXTRA1 AS VENCIMENTO,

TMOV.DATAEMISSAO,

TMOV.DATACRIACAO,

TMOV.NUMEROMOV AS NUMERODOCUMENTO,

TMOV.VALORLIQUIDO,

TMOV.CODUSUARIO AS USUARIO,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN NIVEL1GER.CODTB1FAT

ELSE NIVEL1.CODTB1FAT

END AS CODNIVEL1,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN NIVEL1GER.DESCRICAO

ELSE NIVEL1.DESCRICAO

END AS DESCNIVEL1,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN NIVEL2GER.CODTB1FAT

ELSE NIVEL2.CODTB1FAT

END AS CODNIVEL2,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN NIVEL2GER.DESCRICAO

ELSE NIVEL2.DESCRICAO

END AS DESCNIVEL2,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN NIVEL3GER.CODTB1FAT

ELSE NIVEL3.CODTB1FAT

END AS CODNIVEL3,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN NIVEL3GER.DESCRICAO

ELSE NIVEL3.DESCRICAO

END AS DESCNIVEL3,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN TPRDCOMPL.DESRECGER

ELSE TITMMOV.CODTB1FAT

END AS CODIGONATUREZA,

CASE WHEN TPRDCOMPL.RELAGERENCIAL = 2

THEN TTB1GER.DESCRICAO

ELSE TTB1.DESCRICAO

END AS NATUREZA,

TITMMOV.NUMEROSEQUENCIAL,

TPRD.CODIGOPRD,

TPRD.NOMEFANTASIA AS PRODUTO,

TITMMOVHISTORICO.HISTORICOCURTO AS HISTORICO,

TITMMOVRATCCU.CODCCUSTO,

GCCUSTO.NOME AS CENTRODECUSTO,

TITMMOV.QUANTIDADE,

TITMMOV.PRECOUNITARIO,

TITMMOVRATCCU.VALOR AS VALOR

FROM TMOV

INNER JOIN

FCFO (NOLOCK)

ON TMOV.CODCOLIGADA = FCFO.CODCOLIGADA

AND TMOV.CODCFO = FCFO.CODCFO

INNER JOIN

GFILIAL (NOLOCK)

ON TMOV.CODCOLIGADA = GFILIAL.CODCOLIGADA

AND TMOV.CODFILIAL = GFILIAL.CODFILIAL

INNER JOIN

TITMMOV (NOLOCK)

ON TMOV.CODCOLIGADA = TITMMOV.CODCOLIGADA

AND TMOV.IDMOV = TITMMOV.IDMOV

LEFT JOIN

TITMMOVHISTORICO (NOLOCK)

ON TITMMOV.CODCOLIGADA = TITMMOVHISTORICO.CODCOLIGADA

AND TITMMOV.IDMOV = TITMMOVHISTORICO.IDMOV

AND TITMMOV.NSEQITMMOV = TITMMOVHISTORICO.NSEQITMMOV

INNER JOIN

TPRD (NOLOCK)

ON TITMMOV.CODCOLIGADA = TPRD.CODCOLIGADA

AND TITMMOV.IDPRD = TPRD.IDPRD

LEFT JOIN

TPRDCOMPL (NOLOCK)

ON TPRD.CODCOLIGADA = TPRDCOMPL.CODCOLIGADA

AND TPRD.IDPRD = TPRDCOMPL.IDPRD

LEFT JOIN

TTB1 TTB1GER (NOLOCK)

ON TPRDCOMPL.CODCOLIGADA = TTB1GER.CODCOLIGADA

AND TPRDCOMPL.DESRECGER = TTB1GER.CODTB1FAT

INNER JOIN

TTB1 (NOLOCK)

ON TITMMOV.CODCOLIGADA = TTB1.CODCOLIGADA

AND TITMMOV.CODTB1FAT = TTB1.CODTB1FAT

INNER JOIN

TITMMOVRATCCU (NOLOCK)

ON TITMMOV.CODCOLIGADA = TITMMOVRATCCU.CODCOLIGADA

AND TITMMOV.IDMOV = TITMMOVRATCCU.IDMOV

AND TITMMOV.NSEQITMMOV = TITMMOVRATCCU.NSEQITMMOV

INNER JOIN

GCCUSTO (NOLOCK)

ON TITMMOVRATCCU.CODCOLIGADA = GCCUSTO.CODCOLIGADA

AND TITMMOVRATCCU.CODCCUSTO = GCCUSTO.CODCCUSTO

INNER JOIN

GCCUSTO ARVORECUSTO (NOLOCK)

ON TITMMOVRATCCU.CODCOLIGADA = ARVORECUSTO.CODCOLIGADA

AND SUBSTRING(TITMMOVRATCCU.CODCCUSTO,1,4) = ARVORECUSTO.CODCCUSTO

INNER JOIN

TTB1 NIVEL3

ON TTB1.CODCOLIGADA = NIVEL3.CODCOLIGADA

AND SUBSTRING(TTB1.CODTB1FAT,1,6) = NIVEL3.CODTB1FAT

INNER JOIN

TTB1 NIVEL2

ON TTB1.CODCOLIGADA = NIVEL2.CODCOLIGADA

AND SUBSTRING(TTB1.CODTB1FAT,1,4) = NIVEL2.CODTB1FAT

INNER JOIN

TTB1 NIVEL1

ON TTB1.CODCOLIGADA = NIVEL1.CODCOLIGADA

AND SUBSTRING(TTB1.CODTB1FAT,1,1) = NIVEL1.CODTB1FAT

LEFT JOIN

TTB1 NIVEL3GER

ON TTB1GER.CODCOLIGADA = NIVEL3GER.CODCOLIGADA

AND SUBSTRING(TTB1GER.CODTB1FAT,1,6) = NIVEL3GER.CODTB1FAT

LEFT JOIN

TTB1 NIVEL2GER

ON TTB1GER.CODCOLIGADA = NIVEL2GER.CODCOLIGADA

AND SUBSTRING(TTB1GER.CODTB1FAT,1,4) = NIVEL2GER.CODTB1FAT

LEFT JOIN

TTB1 NIVEL1GER

ON TTB1GER.CODCOLIGADA = NIVEL1GER.CODCOLIGADA

AND SUBSTRING(TTB1GER.CODTB1FAT,1,1) = NIVEL1GER.CODTB1FAT

WHERE TMOV.DATACRIACAO BETWEEN @DATA_INICIAL AND @DATA_FINAL

AND TMOV.STATUS <> 'C'

AND TMOV.CODTMV <> '1.1.40'

AND TMOV.CODTMV <> '1.1.41'

UNION ALL

SELECT

'2' AS ORDEM,

'FLUXUS' AS APLICACAO,

'F' AS CODAPLICACAO,

FLAN.CODCOLIGADA,

FLAN.CODFILIAL,

'' AS CODLOC,

GFILIAL.NOME,

'' AS CODTMV,

FLAN.IDLAN AS REFERENCIA,

FLAN.CODTDO,

FLAN.CODCFO,

FCFO.NOME AS FORNECEDOR,

FLAN.DATAVENCIMENTO AS VENCIMENTO,

FLAN.DATAEMISSAO,

FLAN.DATACRIACAO,

FLAN.NUMERODOCUMENTO AS NUMERODOCUMENTO,

FLAN.VALORORIGINAL AS VALORLIQUIDO,

FLAN.USUARIOCRIACAO AS USUARIO,

NIVEL1.CODINTERNO AS CODNIVEL1,

NIVEL1.DESCRICAO AS DESCNIVEL1,

NIVEL2.CODINTERNO AS CODNIVEL2,

NIVEL2.DESCRICAO AS DESCNIVEL2,

NIVEL3.CODINTERNO AS CODNIVEL3,

NIVEL3.DESCRICAO AS DESCNIVEL3,

FLANCOMPL.DESRECGER AS CODIGONATUREZA,

GCONSIST.DESCRICAO AS NATUREZA,

'1' AS NUMEROSEQUENCIAL,

'' AS CODIGOPRD,

'' AS PRODUTO,

FLAN.HISTORICO AS HISTORICO,

FLANRATCCU.CODCCUSTO,

GCCUSTO.NOME AS CENTRODECUSTO,

'1' AS QUANTIDADE,

FLAN.VALORORIGINAL AS PRECOUNITARIO,

FLANRATCCU.VALOR

FROM FLAN

INNER JOIN

GFILIAL (NOLOCK)

ON FLAN.CODCOLIGADA = GFILIAL.CODCOLIGADA

AND FLAN.CODFILIAL = GFILIAL.CODFILIAL

LEFT JOIN

FLANCOMPL (NOLOCK)

ON FLAN.CODCOLIGADA = FLANCOMPL.CODCOLIGADA

AND FLAN.IDLAN = FLANCOMPL.IDLAN

LEFT JOIN

FRELLAN (NOLOCK)

ON FLAN.CODCOLIGADA = FRELLAN.CODCOLIGADA

AND FLAN.IDLAN = FRELLAN.IDLAN

LEFT JOIN

FLAN FLAN2 (NOLOCK)

ON FRELLAN.CODCOLIGADA = FLAN2.CODCOLIGADA

AND FRELLAN.IDLANREL = FLAN2.IDLAN

INNER JOIN

FCFO (NOLOCK)

ON FLAN.CODCOLIGADA = FCFO.CODCOLIGADA

AND FLAN.CODCFO = FCFO.CODCFO

LEFT JOIN

GCONSIST (NOLOCK)

ON FLANCOMPL.DESRECGER = GCONSIST.CODINTERNO

AND GCONSIST.CODTABELA = 'RECDESP'

INNER JOIN

FLANRATCCU (NOLOCK)

ON FLAN.CODCOLIGADA = FLANRATCCU.CODCOLIGADA

AND FLAN.IDLAN = FLANRATCCU.IDLAN

INNER JOIN

GCCUSTO (NOLOCK)

ON FLANRATCCU.CODCOLIGADA = GCCUSTO.CODCOLIGADA

AND FLANRATCCU.CODCCUSTO = GCCUSTO.CODCCUSTO

INNER JOIN

GCCUSTO ARVORECUSTO (NOLOCK)

ON FLANRATCCU.CODCOLIGADA = ARVORECUSTO.CODCOLIGADA

AND SUBSTRING(FLANRATCCU.CODCCUSTO,1,4) = ARVORECUSTO.CODCCUSTO

LEFT JOIN

GCONSIST NIVEL3

ON SUBSTRING(GCONSIST.CODINTERNO,1,6) = NIVEL3.CODINTERNO

AND NIVEL3.CODTABELA = 'RECDESP'

LEFT JOIN

GCONSIST NIVEL2

ON SUBSTRING(GCONSIST.CODINTERNO,1,4) = NIVEL2.CODINTERNO

AND NIVEL2.CODTABELA = 'RECDESP'

LEFT JOIN

GCONSIST NIVEL1

ON SUBSTRING(GCONSIST.CODINTERNO,1,1) = NIVEL1.CODINTERNO

AND NIVEL1.CODTABELA = 'RECDESP'

WHERE (FLAN.CODAPLICACAO = 'F' OR FLAN.CODAPLICACAO IS NULL)

AND FLAN.DATACRIACAO BETWEEN @DATA_INICIAL AND @DATA_FINAL

AND FLAN.CLASSIFICACAO <> 6

AND FLAN.CLASSIFICACAO <> 7

AND FLAN.CLASSIFICACAO <> 11

AND FLAN.CLASSIFICACAO <> 15

AND FLAN.CLASSIFICACAO <> 21

AND FLAN.CLASSIFICACAO <> 26

AND FLAN.CODTDO <> 'AF01'

AND FLAN.IDBAIXAPARCIAL IS NULL

AND FLAN.NFOUDUP <> 2

AND (FLAN.CODCXA <> 'C998' OR FLAN.CODCXA IS NULL)

UNION ALL

SELECT

'3' AS ORDEM,

'FLUXUS' AS APLICACAO,

'F' AS CODAPLICACAO,

FLAN.CODCOLIGADA,

FLAN.CODFILIAL,

'' AS CODLOC,

GFILIAL.NOME,

'' AS CODTMV,

FLAN.IDLAN AS REFERENCIA,

FLAN.CODTDO,

FLAN.CODCFO,

FCFO.NOME AS FORNECEDOR,

FLAN.DATAVENCIMENTO AS VENCIMENTO,

FLAN.DATAEMISSAO,

FLAN.DATACRIACAO,

FLAN.NUMERODOCUMENTO AS NUMERODOCUMENTO,

FLAN.VALORORIGINAL AS VALORLIQUIDO,

FLAN.USUARIOCRIACAO AS USUARIO,

NIVEL1.CODINTERNO AS CODNIVEL1,

NIVEL1.DESCRICAO AS DESCNIVEL1,

NIVEL2.CODINTERNO AS CODNIVEL2,

NIVEL2.DESCRICAO AS DESCNIVEL2,

NIVEL3.CODINTERNO AS CODNIVEL3,

NIVEL3.DESCRICAO AS DESCNIVEL3,

FLANCOMPL.DESRECGER AS CODIGONATUREZA,

GCONSIST.DESCRICAO AS NATUREZA,

'1' AS NUMEROSEQUENCIAL,

'' AS CODIGOPRD,

'' AS PRODUTO,

FLAN.HISTORICO AS HISTORICO,

FLANRATCCU.CODCCUSTO,

GCCUSTO.NOME AS CENTRODECUSTO,

'1' AS QUANTIDADE,

FLAN.VALORORIGINAL AS PRECOUNITARIO,

FLANRATCCU.VALOR

FROM FLAN

INNER JOIN

GFILIAL (NOLOCK)

ON FLAN.CODCOLIGADA = GFILIAL.CODCOLIGADA

AND FLAN.CODFILIAL = GFILIAL.CODFILIAL

LEFT JOIN

FLANCOMPL (NOLOCK)

ON FLAN.CODCOLIGADA = FLANCOMPL.CODCOLIGADA

AND FLAN.IDLAN = FLANCOMPL.IDLAN

LEFT JOIN

FRELLAN (NOLOCK)

ON FLAN.CODCOLIGADA = FRELLAN.CODCOLIGADA

AND FLAN.IDLAN = FRELLAN.IDLAN

LEFT JOIN

FLAN FLAN2 (NOLOCK)

ON FRELLAN.CODCOLIGADA = FLAN2.CODCOLIGADA

AND FRELLAN.IDLANREL = FLAN2.IDLAN

INNER JOIN

FCFO (NOLOCK)

ON FLAN.CODCOLIGADA = FCFO.CODCOLIGADA

AND FLAN.CODCFO = FCFO.CODCFO

LEFT JOIN

GCONSIST (NOLOCK)

ON FLANCOMPL.DESRECGER = GCONSIST.CODINTERNO

AND GCONSIST.CODTABELA = 'RECDESP'

INNER JOIN

FLANRATCCU (NOLOCK)

ON FLAN.CODCOLIGADA = FLANRATCCU.CODCOLIGADA

AND FLAN.IDLAN = FLANRATCCU.IDLAN

INNER JOIN

GCCUSTO (NOLOCK)

ON FLANRATCCU.CODCOLIGADA = GCCUSTO.CODCOLIGADA

AND FLANRATCCU.CODCCUSTO = GCCUSTO.CODCCUSTO

INNER JOIN

GCCUSTO ARVORECUSTO (NOLOCK)

ON FLANRATCCU.CODCOLIGADA = ARVORECUSTO.CODCOLIGADA

AND SUBSTRING(FLANRATCCU.CODCCUSTO,1,4) = ARVORECUSTO.CODCCUSTO

LEFT JOIN

GCONSIST NIVEL3

ON SUBSTRING(GCONSIST.CODINTERNO,1,6) = NIVEL3.CODINTERNO

AND NIVEL3.CODTABELA = 'RECDESP'

LEFT JOIN

GCONSIST NIVEL2

ON SUBSTRING(GCONSIST.CODINTERNO,1,4) = NIVEL2.CODINTERNO

AND NIVEL2.CODTABELA = 'RECDESP'

LEFT JOIN

GCONSIST NIVEL1

ON SUBSTRING(GCONSIST.CODINTERNO,1,1) = NIVEL1.CODINTERNO

AND NIVEL1.CODTABELA = 'RECDESP'

WHERE (FLAN.CODAPLICACAO = 'F' OR FLAN.CODAPLICACAO IS NULL)

AND FLAN.DATACRIACAO BETWEEN @DATA_INICIAL AND @DATA_FINAL

AND FLAN.CLASSIFICACAO <> 15

AND FLAN.CODTDO = 'AF01'

AND FLAN.IDBAIXAPARCIAL IS NULL

AND FLAN.NFOUDUP <> 2

AND (FLAN.CODCXA <> 'C998' OR FLAN.CODCXA IS NULL)

AND FLAN.IDLAN NOT IN (SELECT IDLANREL FROM FRELLAN)

RETURN

END

Editado 4 de Abril de 2011 por Igorfosp