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.
P.S.: I am not an accountant by any means of imagination ...
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.