Our own Victoria Yudin has once again hit a home run with her view revealing unposted checks in Dynamics GP. She has solved the problem of the "... universally hated ..." Check Batch Edit List report. You can read her post, and get her code here: https://victoriayudin.com/2017/08/29/sql-view-for-unposted-checks-in-dynamics-gp/I'm always afraid links will break, so I have copied her post below. Be sure to check her site for any modifications she may make in the future. The little man pictured at the start of this post is from Victoria's Web site, she always has the best graphics
Live the dream!
Thank you Victoria -
SQL view for unposted checks in Dynamics GP
August 29, 2017 
0 Comments
0 Comments I have been asked more than a few times for a script that shows uposted checks and their apply detail. This can be helpful to replace the Check Batch Edit List report, which is universally hated by all GP users I have spoken to.
create view view_unposted_check_batch
as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_unposted_check_batch
-- Created Aug 29, 2017 by Victoria Yudin, Flexible Solutions
-- For updates see https://victoriayudin.com/gp-reports/
-- Shows details of unposted checks and how they are applied
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select
rtrim(p.BACHNUMB) Batch,
rtrim(p.CHEKBKID) Checkbook,
rtrim(p.PMNTNMBR) [Payment Number],
p.DOCDATE [Check Date],
rtrim(p.CHEKTOTL) [Check Amount],
rtrim(p.VENDORID) [Vendor ID],
rtrim(p.VENDNAME) [Vendor Name],
rtrim(p.ADDRESS1) Address1,
rtrim(v.PYMTRMID) [Payment Terms],
rtrim(a.DOCNUMBR) [Invoice Number],
a.DOCDATE [Invoice Date],
a.DocDueDate [Due Date],
a.AMNTPAID [Amount Paid],
a.DISTKNAM Discount,
rtrim(a.TRXDSCRN) [Invoice Description]
from PM10300 p
left outer join PM10201 a
on p.PMNTNMBR = a.PMNTNMBR
inner join PM00200 v
on p.VENDORID = v.VENDORID
where p.BCHSOURC like '%check%'
and AMNTPAID <> 0
and CHEKTOTL <> 0
-- add permissions for DYNGRP
go
grant select on view_unposted_check_batch to DYNGRP
