Payables Report

(0) Share

Report

Posted on by Community Member

We are currently adding dimension to our chart of accounts - and will need to review/edit the default coding that has been set up in vendor maintenance. Is there a report that I can run or what fields to I need to build a report that will show me all the default coding set up by vendor. I am assuming that no matter what we will need to go into each individual vendor to actually change the defaults. However - if I can get a report initially to edit - then we can utilize multiple resources to actually go in and change the accounts .

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (2)

Suggested answer

Redbeard 12,931 on at

Like (0)

Report

I sounds like you might want to use Account Modifier in the Professional Services Tools library to change your existing accounts while changing your chart of accounts design.

Professional Services Tools Library is now available for Free to current GP customers. You should get your partner involved in this kind of project, of if you don't have a partner, find one with the requisite skills. As far as getting the default account setup out of Great Plains for Vendors you would likely want to start with the Vendor SmartLists, adding the necessary columns.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I can create a smartlist that shows the purchase account ... however if the vendor has split coding and has say 3 accounts that is set up in the default ... only the first account shows up. I am looking at the GP standard vendor detail report - and it does show the additional accounts - but I cannot find a field for that.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Go to Reports > Purchasing > Setup/Lists, then choose the Vendor Detail report from the Drop-down list.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I can get a vendor detail report - but it has every detail of the vendor and then the report is like 700 pages long ... What I am trying to do is prevent that - I want to see vendor #, Vendor name, Purchase account and additional purchase accounts - but I cannot find a field that would be for the additional accounts. When I try to run the SRS vendor detail report it locks up every time .... if I try to run the vendor detail report for excel it says I do not have the proper log in ... I am not sure -- but don't think we are set up for those.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

You would need to have the Active Directory Account you use to login to your computer added to SSRS, and the folder your Excel Reports are deployed to, in order to access these reports.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

While this query isn't perfect, it should provide you the data you need to make your analysis:

select

v.VENDORID Vendor_ID,

V.VENDNAME Vendor_Name,

PMAP.ACTNUMST AP,

PMCSH.ACTNUMST Cash,

PMDAV.ACTNUMST Discounts_Avail,

PMDTK.ACTNUMST Discounts_Taken,

PMFIN.ACTNUMST Finance_Charges,

PMMSCH.ACTNUMST Miscellaneous,

PMFRT.ACTNUMST Freight,

PMTAX.ACTNUMST Tax,

PMWRT.ACTNUMST Writeoffs,

PMPRCH.ACTNUMST Purchases,

PMTDSC.ACTNUMST Trade_Discount,

ACPUR.ACTNUMST Accrued_Purchases,

PURPV.ACTNUMST Purchase_Price_Variance,

GLD.ACTNUMST Alternate_Payables_Acct

from PM00200 V

left join PM00201 VA on V.VENDORID = VA.VENDORID

LEFT join PM00203 PA on V.VENDORID = PA.VENDORID

Left join GL00105 PMAP on PMAP.ACTINDX = V.PMAPINDX

Left Join GL00105 PMCSH on PMCSH.ACTINDX = V.PMCSHIDX

Left Join GL00105 PMDAV on PMDAV.ACTINDX = v.PMDAVIDX

Left Join GL00105 PMDTK on PMDTK.ACTINDX = v.PMDTKIDX

Left Join GL00105 PMFIN on PMFIN.ACTINDX = V.PMFINIDX

Left Join GL00105 PMMSCH on PMMSCH.ACTINDX = v.PMMSCHIX

Left Join GL00105 PMFRT on PMFRT.ACTINDX = V.PMFRTIDX

Left Join GL00105 PMTAX on PMTAX.ACTINDX = V.PMTAXIDX

Left Join GL00105 PMWRT on PMWRT.ACTINDX = V.PMWRTIDX

Left Join GL00105 PMPRCH on PMPRCH.ACTINDX = V.PMPRCHIX

Left Join GL00105 PMTDSC on PMTDSC.ACTINDX = V.PMTDSCIX

Left Join GL00105 ACPUR on ACPUR.ACTINDX = V.ACPURIDX

Left Join GL00105 PURPV on PURPV.ACTINDX = V.PURPVIDX

Left Join GL00105 GLD on PA.ACTINDX = GLD.ACTINDX

Was this reply helpful? Yes No
Verified answer

Justin Thorp 2,265 on at

Like (0)

Report

The additional purchases accounts are held in the PM00203.

Based on your request, I believe the below script is what you are after:

select a.VENDORID, a.VENDNAME,

(SELECT ACTNUMST FROM GL00105 g5 WHERE a.PMPRCHIX = g5.ACTINDX) AS 'Main Purchases Account',

(SELECT ACTNUMST FROM GL00105 g5 WHERE b.ACTINDX = g5.ACTINDX) AS 'Additional Purchases Account(s)'

from PM00200 a Left Outer JOIN PM00203 b

on a.VENDORID = b.VENDORID

Order by a.VENDORID

Thanks,

Justin

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have searched and searched for this field ... You will have to forgive me but this is the first time I am getting into this depth with GP ... you say held in the PM00203 ... and GL00105 .... where do I find these references? I am assuming PM = Payables management and GL is general ledger ... I do not see these references when I look at the tables ... how do I find those references?

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

PM00200, PM00203 and GL00105 are SQL Tables in the SQL database, neither Justin nor I asked if you had access to SQL Server Management Studio before offering you our scripts. You would need to access SQL Server Management Studio and have Select Permissions on the database(s) involved to run the queries we've provided. Justin's query is a subset of mine, which gives only the alternate accounts for the vendors. My query provides all the accounts and has multiple rows, which contain the alternate accounts for any Vendor who has them assigned.

PM00200 is the Vendor Master Table (contains most information about Vendors)

PM00203 is the Vendor Alternate Accounts Table (contains alternate accounts assigned to Vendors)

GL00105 is The GL Account Detail table, which contains the Whole Account Number, which is referenced as an Index in most other tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yeah - I do not believe I have access to the SQL Server Management.

I was trying to use smart list builder and accessing the tables and fields there - I do not believe the Vendor Alternate Accounts Table is available in Smart List Builder ...

We have only been using GP for a little over a year - and I really have just started looking into the functionality of it the last couple of months. I was not with the company during onboarding of the software - and no one here seems to know much more than the basics about it. How would one obtain access to the SQL Server Management Studio ?? And the permissions on the data base I am assuming we would set here as the administrator correct?

Was this reply helpful? Yes No