Finding Unpresented Cheques / Checks

[Former Member]

Hi,

I am trying to create my own cashflow in SQL Reporting but cannot work out the SQL to find unpresented cheques - can someone please help me at least with what flag or where clause I need on what table to get the list of unpresented AP Cheques - as I see there is data in OCHO, OVPM, JDT1 but nothing identifying that it has been reconciled.

Thanks

[Former Member]

Hi Nick,

Your query may meet your need already. Here is my update in case you have different reconciliation methods in the future.

SELECT DISTINCT T2.VendorCode, T2.CheckNum, T2.CheckSum
FROM         dbo.JDT1 T0 
INNER JOIN dbo.OVPM T1 ON T0.TransId = T1.TransId
INNER JOIN dbo.OCHO T2 ON T1.DocEntry = T2.PmntNum
GROUP BY T2.VendorCode, T2.CheckNum, T2.CheckSum
HAVING     (SUM(T0.ExtrMatch) + SUM(T0.IntrnMatch) = 0) AND (T2.VendorCode IS NOT NULL)

Thanks,

Gordon

[Former Member]

Thanks

[Former Member]

Ok, So I found a way while waiting - always happens this way.

This is the SQL I have used - If anyone knows a better way i would love to know

SELECT DISTINCT dbo.OCHO.VendorCode, dbo.OCHO.CheckNum, dbo.OCHO.CheckSum
FROM         dbo.JDT1 INNER JOIN
                      dbo.OVPM ON dbo.JDT1.TransId = dbo.OVPM.TransId INNER JOIN
                      dbo.OCHO ON dbo.OVPM.DocEntry = dbo.OCHO.PmntNum
GROUP BY dbo.OCHO.CheckNum, dbo.OCHO.CheckSum, dbo.OCHO.VendorCode
HAVING      (SUM(dbo.JDT1.ExtrMatch) = 0) AND (dbo.OCHO.VendorCode IS NOT NULL)