Query de Partidas em Aberto

[Former Member]

Boa Tarde Pessoal.

Estou precisando montar uma query que traga todas as partidas encima de um cliente que estão em aberto.

Eu montei baseado na tabela OJDT com integração à tabela JDT1. Porém ele traz todas as partidas, nas somas o resultado bate certinho, mas eu quero trazer apenas as que estão em aberto... as reconciliadas eu não quero mostrar.

Eis minha query

SELECT

T2.CardCode,

T2.CardName,

(( T1.Debit )-(T1.Credit)) AS Saldo

FROM OJDT T0

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

INNER JOIN OCRD AS T2 ON T2.CardCode = T1.ShortName

WHERE T2.CardType = 'C' AND T2.CardCode = 'C03333'

Alguém tem alguma idéia?

Obrigado desde já.

Guilherme.

[former_member248613]

Prezados, saudações

Alguém tem as tabelas para que eu possa efetuar isto na SQ01?

[Former Member]

Oi Guilherme,

Na JDT1, existe um campo chamado: ExtrMatch, que grava o número da reconciliação do lançamento contábil, pode utiliza-lo no comando WHERE.

Esse campo tem os mesmos valores gravados na tabela OMTH, campo MacthNum.

Abraços

[Former Member]

Bom dia Colega.

Segue processo para retornar titulos em aberto.

Você deve especificar as datas iniciais e finais de lançamento e vencimento e informar o parceiro de negócio.

SELECT T2.CardCode, T2.CardName, T1.Number 'DocEntry', MAX(T0.[TransType]) 'Tp.Doc.', T0.Line_ID 'Parcela', T0.RefDate 'Dt.Lançamento',

T0.DueDate 'Dt.Vencimento', SUM(T0.[Debit]) + SUM(T0.[Credit]) 'Vl.Titulo',

(SUM(T0.[Debit]) + SUM(T0.[Credit])) - (ABS(MAX(T0.[BalDueCred]) - MAX(T0.[BalDueDeb]))) 'Vl.Recebido',

ABS(MAX(T0.[BalDueCred]) - MAX(T0.[BalDueDeb])) 'Vl.Saldo', T0.SourceID 'Nr.Doc'

FROM .[JDT1] T0

INNER JOIN .[OJDT] T1 ON T1.[TransId] = T0.[TransId]

INNER JOIN .[OCRD] T2 ON T2.[CardCode] = T0.[ShortName]

LEFT OUTER JOIN .[B1_JournalTransSourceView] T3 ON T3.[ObjType] = T0.[TransType]

AND T3.[DocEntry] = T0.[CreatedBy]

AND (T3.[TransType] <> N'I' OR (T3.[TransType] = N'I'

AND T3.[InstlmntID] = T0.[SourceLine]))

WHERE T0.[RefDate] BETWEEN '2001-01-01' AND '2012-12-31'

AND T0.[DueDate] BETWEEN '2001-01-01' AND '2012-12-31'

--AND T2.[CardCode] = ISNULL('XXXX',T2.[CardCode])

AND T2.[CardType] = ('C')

AND T2.[Balance] <> (0)

AND T0.[TransType] NOT IN ('14')

AND (T0.[BalDueCred] <> T0.[BalDueDeb] OR T0.[BalFcCred] <> T0.[BalFcDeb] )

AND NOT EXISTS (SELECT U0.[TransId], U0.[TransRowId]

FROM .[ITR1] U0

INNER JOIN .[OITR] U1 ON U1.[ReconNum] = U0.[ReconNum]

WHERE T0.[TransId] = U0.[TransId]

AND T0.[Line_ID] = U0.[TransRowId]

--AND U1.[ReconDate] > ('2010-12-31')

GROUP BY U0.[TransId], U0.[TransRowId])

GROUP BY T2.CardCode, T2.CardName, T1.Number, T0.Line_ID, T0.RefDate, T0.DueDate, T0.SourceID

UNION ALL

SELECT T6.CardCode, T6.CardName, T7.Number 'DocEntry', MAX(T0.[TransType]) 'Tp.Doc.', T0.Line_ID 'Parcela', T0.RefDate 'Dt.Lançamento',

T0.DueDate 'Dt.Vencimento', SUM(T0.[Debit]) + SUM(T0.[Credit]) 'Vl.Titulo', 0 'Vl.Recebido',

ABS(MAX(T0.[BalDueCred]) - MAX(T0.[BalDueDeb])) 'Vl.Saldo', T0.SourceID 'Nr.Doc'

FROM .[JDT1] T0 INNER JOIN .[ITR1] T1 ON T1.[TransId] = T0.[TransId]

AND T1.[TransRowId] = T0.[Line_ID]

INNER JOIN .[OITR] T2 ON T2.[ReconNum] = T1.[ReconNum]

INNER JOIN .[OJDT] T3 ON T3.[TransId] = T0.[TransId]

INNER JOIN .[OCRD] T4 ON T4.[CardCode] = T0.[ShortName]

INNER JOIN .[OCRD] T6 ON T6.[CardCode] = T0.[ShortName]

INNER JOIN .[OJDT] T7 ON T7.[TransId] = T0.[TransId]

INNER JOIN .[OINV] T8 ON T8.[docentry] = T0.[TransId]

LEFT OUTER JOIN .[B1_JournalTransSourceView] T5 ON T5.[ObjType] = T0.[TransType]

AND T5.[DocEntry] = T0.[CreatedBy]

AND (T5.[TransType] <> 'I' OR (T5.[TransType] = 'I' AND T5.[InstlmntID] = T0.[SourceLine]))

WHERE (T4.[Balance] <> 0

--AND T4.[CardCode] = ISNULL('XXX',T4.[CardCode])

AND T1.[IsCredit] = ('D'))

AND (T4.[CardCode] IS NULL

OR (T4.[validFor] = ('Y')

OR (T4.[frozenFor] = ('Y')

AND (T4.[frozenFrom] IS NOT NULL

OR T4.[frozenTo] IS NOT NULL))

OR (T4.[validFor] = ('N')

AND T4.[frozenFor] = ('N'))))

AND T0.[RefDate] BETWEEN '2001-01-01' AND '2012-12-31'

AND T0.[DueDate] BETWEEN '2001-01-01' AND '2012-12-31'

AND T3.TransId NOT IN (SELECT StornoToTr FROM OJDT)

GROUP BY T6.CardCode, T6.CardName, T7.Number, T0.Line_ID, T0.RefDate, T0.DueDate, T0.SourceID

Edited by: Alaito Rosner Junior on Nov 19, 2010 12:07 PM

[Former Member]

Opa Boa Tarde!

Obrigado pela ajuda, porém a query acima está cheia de erros.

Obrigado.

Até mais.

Guilherme.

[Former Member]

Alguém sabe me informar o significado do campo TransType em JDT1 ?

Desde já obrigado

[Former Member]

Carinha... é que no portal ele come alguns caracteres especiais..

substitua os comandos "**" por "'<' '>'"

SELECT T2.CardCode, T2.CardName, T1.Number 'DocEntry', MAX(T0.TransType) 'Tp.Doc.', T0.Line_ID 'Parcela', T0.RefDate 'Dt.Lançamento',

T0.DueDate 'Dt.Vencimento', SUM(T0.Debit) + SUM(T0.Credit) 'Vl.Titulo',

(SUM(T0.Debit) + SUM(T0.Credit)) - (ABS(MAX(T0.BalDueCred) - MAX(T0.BalDueDeb))) 'Vl.Recebido',

ABS(MAX(T0.BalDueCred) - MAX(T0.BalDueDeb)) 'Vl.Saldo', T0.SourceID 'Nr.Doc'

FROM JDT1 T0

INNER JOIN OJDT T1 ON T1.TransId = T0.TransId

INNER JOIN OCRD T2 ON T2.CardCode = T0.ShortName

LEFT OUTER JOIN B1_JournalTransSourceView T3 ON T3.ObjType = T0.TransType

AND T3.DocEntry = T0.CreatedBy

AND (T3.TransType NOT IN ('I') OR (T3.TransType = 'I'

AND T3.InstlmntID = T0.SourceLine))

WHERE T0.RefDate BETWEEN '2001-01-01' AND '2012-12-31'

AND T0.DueDate BETWEEN '2001-01-01' AND '2012-12-31'

--AND T2.CardCode = ISNULL('XXXX',T2.CardCode)

AND T2.CardType = ('C')

AND T2.Balance NOT IN (0)

AND T0.TransType NOT IN ('14')

AND (T0.BalDueCred ** T0.BalDueDeb OR T0.BalFcCred ** T0.BalFcDeb )

AND NOT EXISTS (SELECT U0.TransId, U0.TransRowId

FROM ITR1 U0

INNER JOIN OITR U1 ON U1.ReconNum = U0.ReconNum

WHERE T0.TransId = U0.TransId

AND T0.Line_ID = U0.TransRowId

--AND U1.ReconDate > ('2010-12-31')

GROUP BY U0.TransId, U0.TransRowId)

GROUP BY T2.CardCode, T2.CardName, T1.Number, T0.Line_ID, T0.RefDate, T0.DueDate, T0.SourceID

UNION ALL

SELECT T6.CardCode, T6.CardName, T7.Number 'DocEntry', MAX(T0.TransType) 'Tp.Doc.', T0.Line_ID 'Parcela', T0.RefDate 'Dt.Lançamento',

T0.DueDate 'Dt.Vencimento', SUM(T0.Debit) + SUM(T0.Credit) 'Vl.Titulo', 0 'Vl.Recebido',

ABS(MAX(T0.BalDueCred) - MAX(T0.BalDueDeb)) 'Vl.Saldo', T0.SourceID 'Nr.Doc'

FROM JDT1 T0 INNER JOIN ITR1 T1 ON T1.TransId = T0.TransId

AND T1.TransRowId = T0.Line_ID

INNER JOIN OITR T2 ON T2.ReconNum = T1.ReconNum

INNER JOIN OJDT T3 ON T3.TransId = T0.TransId

INNER JOIN OCRD T4 ON T4.CardCode = T0.ShortName

INNER JOIN OCRD T6 ON T6.CardCode = T0.ShortName

INNER JOIN OJDT T7 ON T7.TransId = T0.TransId

INNER JOIN OINV T8 ON T8.docentry = T0.TransId

LEFT OUTER JOIN B1_JournalTransSourceView T5 ON T5.ObjType = T0.TransType

AND T5.DocEntry = T0.CreatedBy

AND (T5.TransType ** 'I' OR (T5.TransType = 'I' AND T5.InstlmntID = T0.SourceLine))

WHERE (T4.Balance NOT IN (0)

--AND T4.CardCode = ISNULL('XXX',T4.CardCode)

AND T1.IsCredit = ('D'))

AND (T4.CardCode IS NULL

OR (T4.validFor = ('Y')

OR (T4.frozenFor = ('Y')

AND (T4.frozenFrom IS NOT NULL

OR T4.frozenTo IS NOT NULL))

OR (T4.validFor = ('N')

AND T4.frozenFor = ('N'))))

AND T0.RefDate BETWEEN '2001-01-01' AND '2012-12-31'

AND T0.DueDate BETWEEN '2001-01-01' AND '2012-12-31'

AND T3.TransId NOT IN (SELECT StornoToTr FROM OJDT)

GROUP BY T6.CardCode, T6.CardName, T7.Number, T0.Line_ID, T0.RefDate, T0.DueDate, T0.SourceID

Sobre o transtype, ele informa a origem do registro.. que veio da nota fiscal de saida.. ou de entrada.. ou do financeiro... por exemplo.. codigo 30.... é que foi feito lancamento contabil manual.... Código 13.. nota fiscal de saída.. e por ae vai..

abraços.

Edited by: Alaito Rosner Junior on Nov 22, 2010 2:20 PM

Edited by: Alaito Rosner Junior on Nov 22, 2010 2:27 PM

[Former Member]

O campo TransType faz referencia ao campo ORIGEM do LCM, através dele você pode selecionar determinados tipos de LCM.

Ex: Somente LCM que foram gerados por emissão de Nota Fiscal de Saída, LCM gerados por Nota Fiscal de Entrada.

Cada transação de origem do B1, tem um código numérico de referencia.

Abraços

Edited by: Thiago Pereira on Dec 1, 2010 6:44 PM