asked on
Invoice Report - Great Plains Version 9
Hello,
The company that I work for uses Great Plains Version 9 as the accounting package. I have been assigned the task of creating our corporate Invoice. As everyone is probably aware of (or at least this is the case for me) GPs SQL Server Database is probably not the easiest database to read and the data model not necessarily the easiest to understand either.
I am planning to use Crystal Reports (this is the means that we have chosen corporate wide) as the tool to generate this report. I am familiar with Stored Procedures and Views but in this specific case I would like to use Stored Procedures.
We were told that version 10 of GP has some views that are portable to version 9 so I created a new Virtual Server and installed a fresh instance of SQL Server. After that I installed version 10 of GP followed by the creation of a sample company (TWO) using the Utilities . After all of these steps were completed I look into both DYNAMICS & TWO databases and to my surprise seems that neither have any View that will encapsulate the notion of Invoice (,InvoiceLineItems, InvoiceHeader, etc neither) records.
I have been doing a bit of reading and perhaps there are few things that can be attempted after installing EConnect but this is quite frankly for me unknown territory.
I would like to be able to get to this data (Invoice, InvoiceLineItems, InvoiceHeader, etc) without having to go directly against the tables if I can avoid it.
Any suggestions will be greatly appreciated.
Kind regards,
P.S.: I am not an accountant by any means of imagination ...
The company that I work for uses Great Plains Version 9 as the accounting package. I have been assigned the task of creating our corporate Invoice. As everyone is probably aware of (or at least this is the case for me) GPs SQL Server Database is probably not the easiest database to read and the data model not necessarily the easiest to understand either.
I am planning to use Crystal Reports (this is the means that we have chosen corporate wide) as the tool to generate this report. I am familiar with Stored Procedures and Views but in this specific case I would like to use Stored Procedures.
We were told that version 10 of GP has some views that are portable to version 9 so I created a new Virtual Server and installed a fresh instance of SQL Server. After that I installed version 10 of GP followed by the creation of a sample company (TWO) using the Utilities . After all of these steps were completed I look into both DYNAMICS & TWO databases and to my surprise seems that neither have any View that will encapsulate the notion of Invoice (,InvoiceLineItems, InvoiceHeader, etc neither) records.
I have been doing a bit of reading and perhaps there are few things that can be attempted after installing EConnect but this is quite frankly for me unknown territory.
I would like to be able to get to this data (Invoice, InvoiceLineItems, InvoiceHeader, etc) without having to go directly against the tables if I can avoid it.
Any suggestions will be greatly appreciated.
Kind regards,
P.S.: I am not an accountant by any means of imagination ...
Tax / Financial SoftwareMicrosoft SQL Server
Last Comment
chrome2000
ASKER
Victoria,
First of all thank you very much for your comments. To answer some of your questions:
1-) " ..it greatly depends on what GP module you're using for invoicing (there are 3 possibilities: Sales Order Processing, Invoicing or Receivables) and what details you are entering on your invoices and need to show on the Invoice report.
Answer: The initial answer is Sales Order Processing
2-) "Another question is going to be whether you need unposted invoices, posted invoices, both in one report or 2 separate reports"
Answer: Seems like both
One thing that I find out during my research is that these two views exist in GP V10 and these might be helpfull
- Dbo.SalesTransactions
- Dbo.SalesLineItems
Kind regards,
First of all thank you very much for your comments. To answer some of your questions:
1-) " ..it greatly depends on what GP module you're using for invoicing (there are 3 possibilities: Sales Order Processing, Invoicing or Receivables) and what details you are entering on your invoices and need to show on the Invoice report.
Answer: The initial answer is Sales Order Processing
2-) "Another question is going to be whether you need unposted invoices, posted invoices, both in one report or 2 separate reports"
Answer: Seems like both
One thing that I find out during my research is that these two views exist in GP V10 and these might be helpfull
- Dbo.SalesTransactions
- Dbo.SalesLineItems
Kind regards,
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Victoria:
You are probably answering to me in both forums ( but you probaly already know that :-) ).
You have opened my eyes and I can now see that more research is required from my part.
Let me see how quickly I can digest all this information.
Regards,
You are probably answering to me in both forums ( but you probaly already know that :-) ).
You have opened my eyes and I can now see that more research is required from my part.
Let me see how quickly I can digest all this information.
Regards,
If you're not sure what module you're using, ask the people entering the invoices what menu path they take to get to the screen...post back and we'll take it from there.
Regardless of that, I am not aware of any views in any version of GP that will help you with all the data you need for creating an invoice. You're going to have to determine the tables/fields needed or get some help. As a start, I have created pages on my blog with the commonly used tables for the core GP modules as well as some additional field definitions and reporting tips. Here is the page for Sales Order Processing (SOP): http://victoriayudin.com/gp-reports/sop-tables/ and here is Receivables: http://victoriayudin.com/gp-reports/rm-tables/. You won't see invoicing there because 99% of GP users use Sales Order Processing to invoice.
I would not spend time on eConnect - it is used primarily for importing data into GP, so I cannot really see how it would help you create invoices or any other reports against GP data. Having created many invoice and other reports in GP, my recommendation for invoices would be to create a stored procedure, just as you mentioned you wanted to do upfront.