on 03-10-2015 7:57 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.