SQL View for Daily Payment Summary
Based on a request from the community, I have decided to post a script for identifying all the payments made on a specific date from a checkbook and the details of what was voided for the specific date.
This query helps a great deal in reconciling the funds that were used for processing the payments.
SELECT A.CHEKBKID , A.PSTGDATE , ISNULL(( SELECT SUM(B.DOCAMNT) FROM dbo.PM30200 B WHERE B.DOCTYPE = 6 AND B.CHEKBKID = A.CHEKBKID AND B.PSTGDATE = A.PSTGDATE ), 0) AS CHECKAMOUNT , ISNULL(( SELECT SUM(B.DOCAMNT) FROM dbo.PM30200 B WHERE B.DOCTYPE = 6 AND B.CHEKBKID = A.CHEKBKID AND B.PSTGDATE = A.PSTGDATE AND B.VOIDED = 1 ), 0) AS VOIDEDCHECKS FROM dbo.PM30200 A WHERE A.DOCTYPE = 6 AND A.PSTGDATE <> '1900-01-01' GROUP BY A.CHEKBKID , A.PSTGDATE
Hope this helps the communityâ€¦
Until next post!
August 5, 2011
Â· veeyeskay Â· 2 Comments
Tags: check amount, payment reconciliation, payment summary, voided amount Â· Posted in: Accounts Payables, Dynamics, Great Plains, SQL Server, SQL Server 2005 Total Views: 2,381