cancel
Showing results for 
Search instead for 
Did you mean: 

Query for get unreconciled JDT1 Lines

Former Member
0 Kudos

Hi

I need to get the fully or partially unreconciled Jouran entry lines for a G/L.

I had a query but this is only shows the fully unreconciled entries

Select * from dbo.OJDT T0 WHERE T0.transid NOT in (Select transid from itr1)

how to get the unreconcile JE lines which are are all not fully reconciled

Advance Thanks to sovers

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Try this;

SELECT T0.TRANSID, T0.Line_ID, T0.Debit

FROM JDT1 T0

LEFT JOIN ITR1 T1 ON T1.TRANSID=T0.TRANSID

WHERE T0.TRANSID LIKE '[%0]%' AND T0.Debit !=0 AND T1.TRANSID IS NULL

Thanks,

Gordon

Former Member
0 Kudos

Thanks for your reply.

But this is shows the unreconciled only and not shows the partially unreconciled

if Debit amount is 500 and 300 was reconciled out of it. here the balance amount 200 transid and its details i need

former_member206488
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT DISTINCT T0.TransId,T0.ShortName,T0.DueDate, T0.LineMemo, T0.Debit, T0.Credit FROM  JDT1 T0  WHERE T0.DueDate >= [%1] AND T0.DueDate  <= [%2] AND (T0.BalDueCred <>0 OR T0.BalDueDeb  != 0 )

Thanks,

Neetu

Former Member
0 Kudos

Try:

SELECT T0.TRANSID, T0.Line_ID, T0.Debit, T0.Credit

FROM JDT1 T0

WHERE T0.BalDueCred+T0.BalDuedeb != 0

Former Member
0 Kudos

Thanks for your responds

I need to know the Query run in the background for the internal receoniliation window (Manual option) in SAP B1 2007

i.e Financials --> Internal reconciliation --> reconciliation (Manual Option - reconciliation window)