SQL view for unposted checks in Dynamics GP


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.

Sorry about the strange colors showing up in the code below. If you copy it and paste it into SQL, it should look just fine.

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

11 Responses to “SQL view for unposted checks in Dynamics GP”

  1. 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.

    Like

    • 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

      Like

  2. 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

    Like

    • 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

      Like

  3. Do you have anything similar for unposted manual payments (EFT/Credit Card)? Thanks

    Like

  4. great, excellent job done

    Like

Trackbacks/Pingbacks

  1. SQL view for unposted checks in Dynamics GP | Victoria Yudin - DynamicAccounting.net - Dynamics GP Users - DUG - September 4, 2017

    […] New from Victoria Yudin: SQL view for unposted checks in Dynamics GP | Victoria Yudin […]

    Like

  2. SQL view for unposted checks in Dynamics GP | Victoria Yudin - Microsoft Dynamics GP Community - September 4, 2017

    […] New from Victoria Yudin: SQL view for unposted checks in Dynamics GP | Victoria Yudin […]

    Like

  3. DynamicAccounting.net | SQL view for unposted checks in Dynamics GP | Victoria YudinSQL view for unposted checks in Dynamics GP | Victoria Yudin - DynamicAccounting.net - September 4, 2017

    […] New from Victoria Yudin: SQL view for unposted checks in Dynamics GP | Victoria Yudin […]

    Like

  4. Victoria Does it Again - Unposted Check Report - Microsoft Dynamics GP Community - August 29, 2017

    […] 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 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: