Query for Reconciled and Unreconciled transactions for a Bank GL

[rajesh_khater]

Hi,

How should I write the query to retrieve all the reconciled and unreconciled transactions for a specific bank GL for a specific Posting Date range?

I need columns like Date, Doc No., Check No., Debit Amt, Credit Amt, Status (Reconciled or Unreconciled).

Mainly I need help in identifying the column based on which I can determine whether a particular line in JDT1 table is reconciled or not in the Bank Reconciliation screen.

Thanks.

[Former Member]

Hi Rajesh,

Hope this helps:

SELECT

T1.TransID,

T1.[RefDate],

T1.[BaseRef],

T0.[Ref3Line] 'Check No.',

T0.[Debit],

T0.[Credit],

CASE

  WHEN T0.[DebCred] = 'C' AND T0.[BalDueCred] = 0 THEN 'Reconciled'

  WHEN T0.[DebCred] = 'C' AND T0.[Credit] = T0.[BalDueCred] THEN 'Unreconciled'

  WHEN T0.[DebCred] = 'C' AND T0.[Credit] <> T0.[BalDueCred] THEN 'Partial'

  WHEN T0.[DebCred] = 'D' AND T0.[BalDueDeb] = 0 THEN 'Reconciled'

  WHEN T0.[DebCred] = 'D' AND T0.[Debit] = T0.[BalDueDeb] THEN 'Unreconciled'

  WHEN T0.[DebCred] = 'D' AND T0.[Debit] <> T0.[BalDueDeb] THEN 'Partial'

  END 'Status'

FROM JDT1 T0

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

WHERE

T0.[Account] = [%0]

AND T1.[RefDate] >=[%1]

AND T1.[RefDate] <=[%2]

Regards,

[former_member205766]

Hi Rajesh

You can use the Standard Sap Business one General for your above requirement.

Go to => Financial => Financial Reports => Accounting => General Ledger. Select the unreconciled Externally for pending BRS  and Reconciled Externally for postings that are reconciled externally.

For Check No Check the Ref3 Row in Report Form Setting.

Check the Below Screen shot for you Ref

With Regards

Balaji Sampath

[rajesh_khater]

Thanks. But I am looking for an SQL query.

[former_member205766]

Hi

Try the below query

select t0.RefDate,T0.BaseRef,t1.Debit,t1.Credit, t1.Ref3Line,

Reconcilled = CASE t1.ExtrMatch when 0 then 'Unreconcilled' else 'Reconciled' end

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

WHERE T1.[Account] =[%0] AND  T0.[RefDate] > =[%1] AND  T0.[RefDate] < =[%2]

With Regards

Balaji Sampath

[rajesh_khater]

Hi,

The ExtrMatch field in JDT1 table contains so many values like 0, 1, 2 and 3. From your query, it seems that 0 means Unreconciled. Then what is the difference between 1, 2 and 3?

Is it the Recon No.?

Thanks.