cancel
Showing results for 
Search instead for 
Did you mean: 

Campo do valores de juros do Boleto (BankSync)

Former Member
0 Kudos

Boa tarde.

Estou fazendo uma consulta para retorna os valores recebidos do boletos até aqui tudo bem o problema e que o valor do juros e criado um novo documento LCM e gostaria de saber qual e o vinculo para esse novo lançamento.

-- Boletos

SELECT OBOE.BoeKey as DocEntry,

       OBOE.Comments,

       'OBOE' as LinkDoc,

        (select TOP 1 TaxDate FROM OBOT T0 INNER JOIN BOT1 T1 ON T0.AbsEntry=T1.AbsEntry

        Where StatusTo='P' AND StatusFrom<>'P' AND BOENumber=OBOE.BoeNum) as TaxDate,

       OBOE.DueDate,

       OBOE.PmntDate as DocDate,

       OBOE.CardCode,

       OBOE.CardName,

       'OCRD' as CardType,

       'BOLETO' as TypeDoc,

       100,

       CASE WHEN (Select COUNT(*) from RCT2 Where RCT2.DocNum=T2.DocNum) > 1 THEN '1/1'

       ELSE (SELECT CAST(T31.[InstlmntId] as VARCHAR(10))+'/'+CAST(T3.[Installmnt] as VARCHAR(10)) FROM RCT2 T22

            LEFT OUTER JOIN OINV T3 ON T22.DocEntry=T3.DocEntry

            LEFT OUTER JOIN INV6 T31 ON T22.DocEntry=T31.DocEntry AND T22.InstId=T31.InstlmntID

            WHERE T22.DocNum=T2.DocNum)

       END as InstlmntId,

       --'1/1' as InstlmntId,

       CAST(OBOE.PmntNum as VARCHAR(254)) as DocNum,

       t22.sumapplied as Total,--OBOE.BoeSum as Total,

       0 Pagamento,

       t22.sumapplied as SumApplied,--OBOE.BoeSum as SumApplied,

       0 as WtAppld,

      0 as Juros,

       0 as Descontos,

       CASE WHEN ISNULL(CAST(OBOE.PmntDate-OBOE.DueDate AS INT),0) < 0 THEN 0

       ELSE ISNULL(CAST(OBOE.PmntDate-OBOE.DueDate AS INT),0) END as DaysLate,

       'Y',

       T51.TransId,

       'Recebidas' as 'Tipo',

       '0' ContaPG,

       '' Conta,

       T200.AcctCode+' - '+ T200.AcctName   as ContaRB,

       T2.BPLId as CodigoBPL,

       T2.BPLName as NomeEmpresa

FROM OBOE INNER JOIN ORCT T2 ON T2.DocNum=OBOE.PmntNum

INNER JOIN RCT2 T22 ON T2.DocNum=T22.DocNum

LEFT OUTER JOIN OINV T3 ON T22.DocEntry=T3.DocEntry

LEFT OUTER JOIN INV6 T31 ON T22.DocEntry=T31.DocEntry AND T22.InstId=T31.InstlmntID

LEFT OUTER JOIN OJDT T51 ON T2.DocEntry=T51.BaseRef AND T2.TransId = T51.TransId

left JOIN OACT T200 ON T200.AcctCode=OBOE.BoeAcct

WHERE OBOE.BoeStatus in ('P') AND OBOE.BoeNum IN (select BOENumber FROM OBOT T0 INNER JOIN BOT1 T1 ON T0.AbsEntry=T1.AbsEntry

                                                  Where StatusTo='P' AND StatusFrom not in ('P','F') AND t0.TaxDate BETWEEN

                                                   @DataInicio

Att.

Denilson Rezende

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Boa noite Denilson.

A informação dada pelo nosso amigo Wagner realmente está correta, não tem uma associação do pagamento de juros para o com o boleto pago.

Existe alguns modos de como recuperar esse valor, e um dos modos é fazer o subselect na JDT1 olhando o campo LineMemo = 1 retornando o valor Credit, tendo em vista que o Line_id 0 ele retorna o valor de débito e Line_id retorna o valor de Crédito.

Abaixo está o sql que modifique para fazer isso.  Deixei a linha sublinhada e negrita para conseguir distinguir  o que foi alterado.

SELECT OBOE.BoeKey AS DocEntry

  ,OBOE.Comments

  ,'OBOE' AS LinkDoc

  ,(

  SELECT TOP 1 TaxDate

  FROM OBOT T0

  INNER JOIN BOT1 T1 ON T0.AbsEntry = T1.AbsEntry

  WHERE StatusTo = 'P'

  AND StatusFrom <> 'P'

  AND BOENumber = OBOE.BoeNum

  ) AS TaxDate

  ,OBOE.DueDate

  ,OBOE.PmntDate AS DocDate

  ,OBOE.CardCode

  ,OBOE.CardName

  ,'OCRD' AS CardType

  ,'BOLETO' AS TypeDoc

  ,100

  ,CASE

  WHEN (

  SELECT COUNT(*)

  FROM RCT2

  WHERE RCT2.DocNum = T2.DocNum

  ) > 1

  THEN '1/1'

  ELSE (

  SELECT CAST(T31.[InstlmntId] AS VARCHAR(10)) + '/' + CAST(T3.[Installmnt] AS VARCHAR(10))

  FROM RCT2 T22

  LEFT OUTER JOIN OINV T3 ON T22.DocEntry = T3.DocEntry

  LEFT OUTER JOIN INV6 T31 ON T22.DocEntry = T31.DocEntry

  AND T22.InstId = T31.InstlmntID

  WHERE T22.DocNum = T2.DocNum

  )

  END AS InstlmntId

  ,

  --'1/1' as InstlmntId,

  CAST(OBOE.PmntNum AS VARCHAR(254)) AS DocNum

  ,t22.sumapplied AS Total

  ,--OBOE.BoeSum as Total,

  0 Pagamento

  ,t22.sumapplied AS SumApplied

  ,--OBOE.BoeSum as SumApplied,

  0 AS WtAppld

,(

  SELECT SUM(X0.Credit)

  FROM JDT1 X0

  WHERE X0.LineMemo LIKE '%' + CONVERT(VARCHAR(10), 19355) + '%'

  AND X0.Line_ID = 1

  ) AS Juros

  ,0 AS Descontos

  ,CASE

  WHEN ISNULL(CAST(OBOE.PmntDate - OBOE.DueDate AS INT), 0) < 0

  THEN 0

  ELSE ISNULL(CAST(OBOE.PmntDate - OBOE.DueDate AS INT), 0)

  END AS DaysLate

  ,'Y'

  ,T51.TransId

  ,'Recebidas' AS 'Tipo'

  ,'0' ContaPG

  ,'' Conta

  ,T200.AcctCode + ' - ' + T200.AcctName AS ContaRB

  ,T2.BPLId AS CodigoBPL

  ,T2.BPLName AS NomeEmpresa

FROM OBOE

INNER JOIN ORCT T2 ON T2.DocNum = OBOE.PmntNum

INNER JOIN RCT2 T22 ON T2.DocNum = T22.DocNum

LEFT OUTER JOIN OINV T3 ON T22.DocEntry = T3.DocEntry

LEFT OUTER JOIN INV6 T31 ON T22.DocEntry = T31.DocEntry

  AND T22.InstId = T31.InstlmntID

LEFT OUTER JOIN OJDT T51 ON T2.DocEntry = T51.BaseRef

  AND T2.TransId = T51.TransId

LEFT JOIN OACT T200 ON T200.AcctCode = OBOE.BoeAcct

WHERE OBOE.BoeStatus IN ('P')

  AND OBOE.BoeNum IN (

  SELECT BOENumber

  FROM OBOT T0

  INNER JOIN BOT1 T1 ON T0.AbsEntry = T1.AbsEntry

  WHERE StatusTo = 'P'

  AND StatusFrom NOT IN (  'P'  ,'F'  )  )

Espero ter ajudado amigo.

former_member209021
Active Participant
0 Kudos

Ola Denilson,

Dia desses conversando com o pessoal da SISTEMA INFO, entendemos que os juros são calculados pela diferença do valor original do titulo pelo valor recebido que aparece na ORCT... Esse valor não é gravado dentro dessas tabelas, é usado apenas para gerar o LC...

Estou me desdobrando por aqui tambem com esta mesma situação!


Boa sorte!