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

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

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