August 29, 2017 
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
-- Updated Jul 13, 2019 to add Purchase Order
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
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],
rtrim(coalesce(i.PORDNMBR,'')) [Purchase Order]
from PM10300 p
left outer join PM10201 a
on p.PMNTNMBR = a.PMNTNMBR
inner join PM00200 v
on p.VENDORID = v.VENDORID
left outer join PM20000 i
on a.APTVCHNM = i.VCHRNMBR
and a.APTODCTY = i.DOCTYPE
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
Victoria Yudin 
Victoria – Do you know if there is a table or view that shows that status of a check batch “pending approval” for printing? We use Mekorma and require aproval for all check batches, so I wanted if there was a Mekorma table somewhere that would house that information. I’d like to run a view of all outstanding batches that have been submitted for approval but are not yet approved.
LikeLike
Hi Andrew,
Unless you are doing something special to Mekorma for the approvals (which I don’t know about, sorry), regular GP batch approvals for any unposted batch will be stored in the SY00500 table. There is a column for approval status (APPROVL) which I believe will be zero is the batch is not approved.
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
First off, thank you for this site. It is a tremendous resource. Let me know if there’s a better place to post this question.
My company is about to implement GP 2016. Is the material in your GP 2013 still useful? I’m an analyst and won’t be directly responsible for implementation but, since we’re a small company, I’ll be involved.
Also, is your book useful to someone new to enterprise ERP systems? I understand accounting (as an end-user with a simple GL) and SQL (haven’t worked with ERP DBs). I think I could use a basic intro to ERP systems and I’m not sure if your book is more advanced than that.
Thanks again for your great site.
-Chris
LikeLike
Hi Chris,
I think a lot of the material is generic and would apply to GP 2016, as well. It also should be still be helpful if you have not worked with ERP’s before. That’s a little bit harder for me to judge since I’ve been working with them for such a long time.
-Victoria
LikeLike
Do you have anything similar for unposted manual payments (EFT/Credit Card)? Thanks
LikeLike
Andrew, try removing the restriction on the BCHSOURC in the WHERE clause at the bottom – that should show you all payments instead of just checks.
-Victoria
LikeLike
great, excellent job done
LikeLike