schema for selecting invoices and statements

[majorBanzai]

Greetings.

I've been asked to spend some time with our poor, neglected finance department. They've been using GP for a while, but nothing more than the standard product. (GP 7.2 on SQL Server. Soon to be 7.5, or 8.0 if they feel comfortable with the changes.)

We're interested in selecting invoices and current statements for feeding into a reporting server which will eventually present the data for customer viewing from a customer portal.

So, where do I begin? Can someone point me to a schema diagram, or better yet, a query, that will produce all invoices for a certain period of time (say, the last six months) and will produce their current statement? We want this data to match our monthly printing of invoices and statements.

Yes, this sounds like the typical on-line invoices and statements. I have to imagine that we are far from the first company wanting to do this. Any help, tips, or points in the right direction would be helpful.

Thanks in advance.

(by the way, I'm glad I discovered this forum. since I'll be living la vida GPloca for a while, I hope to be a frequent visitor - and contributor once I get more into this project.)

 

[majorBanzai]

Some more info..

.. we will be using a consultant to set up a development install of our GP application. However, she is more of a configurator and not an integrator, which means she isn't really familiar with the schema.

I do have access to the schema and have begun to recon the data. Google searches uncovered a few key tables (RM00401 as an example) but nothing with a better overall description. Our consultant has mentioned to "get the SDK", but can't quantify exactly how that will help.

So, I'll keep looking and reading, but if someone has 'been there done that', I would sure like to chat with you.

Thanks.

 

[jazgeek]

been there done that.

you can get table descriptions and field level info from the interface via tools>resource description>tables

without knowing if you're using just AR for invoicing or sales order processing I can't point you to any frequently used tables.




-----------
and they wonder why they call it Great Pains!

jaz

 

[klewis10367]

Go to Report Writer within Great Plains and select the Invoice Report or Statement open each and select the tables. This will tell you which tables these rports pull from. As an FYI, the statements pull from temp tables and it is very difficult or impossible to reproduce the statements correctly out of Great Plains. Your historical statements will be all out of wack. Been down this road before and it was a waste of time to try and reproduce statements exactly as you would in the UI.

 

[majorBanzai]

Thanks for the pointers so far.

The Report Writer is high on my list to go investigate - waiting for my dev install so I don't bug our finance folks too much. Will the Report Writer show me the actual table names? Or will it show me a logical name for the tables that I will need to translate?

About statements, it's disappointing to hear that they are so hard to produce. We'd like to show a little invoice/payment history. klewis, did you abandon statements alltogether? Or did you opt to show recent invoice/payment history instead, if at all?

This might not be such a deal breaker for us. Our finance folks would still like to send paper statements and our marketing folks still want to send paper spam. Having online invoices only might be a good compromise for this project.

 

[klewis10367]

We still use statements but they are very limited in what we ultimately want to accomplish. We are looking for alternative methods. We thought crystal would be the way to go, but you cannot do any balance forwarding in crystal. There is something hardcoded in the Receivables open table that will not allow this. You can see the data, but when you choose date ranges to sum the BF it will not calculate the Bal Forward and remaining balance. That's just one problem. Also, the report writer will show the logical, I believe. All you need to do there is just go to ( In Great Plains) Tools>Resource Descriptions>Tables and do a lookup by logical and it will give you the table physical name. The problem with the Statements is that the transactions are generated by a temp transaction table and temp header instead of the actual RM Open table. Unless you know something that I do not know, crystal will not be able to see the temp tables because they are not populated until the report is generated. Hope this hepls.

 

[jazgeek]

I've created reports for producing statements in crystal reports several times. I believe Luvsql has as well.

all the info is there, you just have to be able to manipulate it properly in crystal.

-----------
and they wonder why they call it Great Pains!

jaz

 

[majorBanzai]

In our case, we won't be producing the invoices and statements using Crystal Reports. We want to query the data and cache it in separate database - the one supporting our customer portal - and control presentation via our front end (java web app).

I get my dev install of GP today. Can't wait. I'll keep you all updated.

 

[majorBanzai]

Okay, got my dev install and am now poking around the Report Writer and the Table resource definitions.

There seems to be a general naming convention to the tables - other than SOP, RM, etc. Can someone point me to a FAQ explaining the table names (the numbering)?

I'll be searching for posted invoices. How can I determine if an invoice is still open (unpaid or not fully paid) versus a closed (paid) invoice?


btw...I was surprised at the copious use of the sa password for the SQL Server database. I ended up needing two SQL Server logins - one created from the GP interface, and then my own that I can use to browse the data with my favorite query tool. It appears that GP encrypts the user password. I couldn't simply use the same password that I use in GP with my favorite query tool.

 

[klewis10367]

the table structure goes as follows..start with RM00xxx is master tables, RM1xxxx is work, RM2xxxx is open,RM3xxxx is history,RM4xxxx is setup, I do not personal use 5's so I am not familiar there uses. If you are using query analyzer you should be able to use the sa wit no problem. Make sure you are using SQL Authentication. As far as users you are right, the passwords are encrypted. You can unencrypt them by re entering the password in their sql login. This opens up backdoor access to tables though. Not a very good practise. I talk about it because we had to do this to allow FRx to work before the latest Service Pack.

 

[majorBanzai]

Thanks for all the great tips - including other threads. You all are a helpful bunch.

So, I need a reality check. After playing with GP and swimming in the data, I've narrowed my targets to the following listed below.

We've decided to show account history (recent invoices and payments) in lieu of Statements and Invoices. That seems to limit my work and scope a bit as well, which I don't mind.

Please help me review the following for any fallacies or gotchas...

[tt]

Invoices
--------
SOP30200 for invoice summary
- mind voidstts for voids
- I seem to have my choice of amount columns
(SUBTOTAL, ORSUBTOT, REMSUBTO, OREMSUBT,
DOCAMNT, ORDOCAMT, ACCTAMNT, ORACTAMT)
what do all these columns mean?

SOP10106 for comments on invoices

SOP30300 for invoice detail
- join on SOPNUMBE
- there are also a choice of amount columns


Payments
--------
CM20300 for payments (mind voided for voids)
- also a choice of amount columns


For our purposes, CM20300.CMLinkID = SOP30200.custnmbr

[/tt]

So how does the above look? Is there anything available describing the different amount columns? Any landmines or gotchas I should consider?