You’re offline. This is a read only version of the page.
750
Our AP Team would like to review all Vendor Names and Check Names. They have created a smart list to run for un-posted invoices , but we are unable to do the same for un-posted Checks. Is there a way to review this before it is posted?
*This post is locked for comments
Good to hear back from you Nicole, it is definitely my pleasure
Never hesitate to share any further inquiries,
Hi Nicole,
You probably didn't change the database name. It defaults to the 'master' database, it won't find any GP tables in there. The screenshot below is where you would select your company database.
Be very careful working in SQL, I've seen some horrors from folks accidentally deleting gobs of data by mistake.
Kind regards,
Leslie
Make sure to run the script on the company database.
Your feedback is highly appreciated,
Hi Mohmoud-
I think I may have made things more complicated. Is there a way to delete these below views(screen shot). I had created two by mistake. I would like to start fresh.
Now, when I try to paste your code into SQL it tells me that "Invalid object name 'PM10300' cannot find the object 'vW_UnpostedPayments', because it does not exist or you do not have permission. yesterday when I clicked execute, it did run successfully.
Hoping there is a way to wipe everything clean and start fresh..
Hello Nicole,
Just to make sure of one single point, does the view retrieve data on the SQL level ? If you run the following select statement on the SQL management studio, would it retrieve any data using the company database
SELECT * FROM vW_UnpostedPayments
Your feedback is highly appreciated,
Hi Mahmoud! Glad I'm not considered a pest yet:)
I have never worked in SQL before, so this is very new to me. Just received access a few weeks ago. Your code and instructions were very helpful! I have made a great attempt and believe I have followed all of the appropriate steps, but the test check batch is not populating.
Here is a screenshot of what I changed. How off am I?
p.s. Thank you both so much for your assistance - truly grateful.
Hi Nicole,
I think, due to link ids problem it wont work. You need to combine/union Payment work and Manual payment work table then need to join with Vendor master table.
So, execute the View mention by Mr. Mahmoud against your company database and then create a new Smartlist with using this view.
Hope this helps!!!
Hello Nicole
It is good to hear from you, create a view as per the code below and then simply create a customized smart list on the smart list builder by choosing the view. To create the view and get it published to smartlist builder, consider the steps mentioned on How to use a SQL view in SmartList Builder
Here is the code
Share
Report
All responses (
Answers (
CREATE VIEW vW_UnpostedPayments
AS
SELECT 'Unposted Payment' AS Type ,
BACHNUMB AS 'Bach Number' ,
BCHSOURC AS 'Bach Source' ,
PMNTNMBR AS 'Payment Number' ,
VENDORID AS 'Vendor ID' ,
VENDNAME AS 'Vendor Name' ,
CHEKBKID AS 'CheckBook ID' ,
DOCDATE AS DocumentDate ,
APPLDAMT AS 'Applied Amount' ,
CURTRXAM AS 'Current Transaction Amount' ,
CHEKTOTL AS 'Document Amount' ,
CURNCYID AS 'Currency ID' ,
CASE VOIDED
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE ''
END AS 'Voided'
FROM PM10300
UNION ALL
SELECT 'Unposted Manual Payment' ,
BACHNUMB ,
BCHSOURC ,
PMNTNMBR ,
A.VENDORID ,
B.VENDNAME ,
A.CHEKBKID ,
DOCDATE ,
APPLDAMT ,
CURTRXAM ,
DOCAMNT ,
A.CURNCYID ,
'No'
FROM PM10400 AS A
LEFT OUTER JOIN dbo.PM00200 AS B ON A.VENDORID = B.VENDORID
GO
GRANT SELECT ON vW_UnpostedPayments TO DYNGRP
Your feedback is highly appreciated,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156