Not Posted Check Batch Review

(0) Share

Report

Posted on by Napolo

766

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

I have the same question (0)

All responses (11)

Answers (2)

Suggested answer

soma 24,410 on at

Like (0)

Report

There is no default smartlist available for viewing unposted payables payments. You can create a new smartlist with using the below tables.

PM00200 - Vendor Master

PM10300 – Work/Unposted Payments

PM10400 – Work/Unposted Manual Payments

Hope this helps!!!

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

Hi Soma -

Here is an image of my smartlist builder. I am still getting zero data. Can you tell from this image, what pieces I might be missing?

Was this reply helpful? Yes No
Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report
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

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,

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

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!!!

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

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,

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

Make sure to run the script on the company database.

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Suggested answer

L Vail 65,271 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Napolo 766 on at

Like (0)

Report

HI Mahmoud!

I was able to get back to this task. I was able to start fresh and use the SQL code, added the vendor Master File and my output is working. Thank you!! I am learning as we go..enough to be dangerous:)

Was this reply helpful? Yes No