[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Greatplains - Crystal Reports

Posted on 2009-02-09
8
Medium Priority
?
333 Views
Last Modified: 2012-05-06
Has anyone created a crystal report that will show you salesperson commissions from Greatplains?  I want to only pay commission once an invoice has been paid.
0
Comment
Question by:jnikodym
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 23591428
Is there a field in GP that shows the invoice has been paid?

mlmcc
0
 

Author Comment

by:jnikodym
ID: 23591523
I'm sure there is somewhere in the SQL tables
0
 
LVL 77

Expert Comment

by:peter57r
ID: 23591616
Also, you have to have a method for selecting records which does not lead to the same invoice being selected more than once.
This would imply that you need a 'date paid' field in your database so that you can select invoices paid on or after a specified date (unless you can run a stored procedure in your databse - which seems unlikely with a commercial software product.)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:jnikodym
ID: 23591652
We run stored procedures all the time in SQL with GP.
0
 
LVL 1

Accepted Solution

by:
RadRichie earned 1500 total points
ID: 23596937
One of these two queries should give you the data you need depending on whether you use SOP or Invoicing.  :
select  -- for Invoicing INVCNMBR,SEQNUMBR,SLPRSNID,SALSTERR,COMPRCNT,COMDLRAM,NCOMAMNT,PRCTOSAL,SLSAMNT,COMMCODE,COMMFLAG FROM IVC10400
OR  -- for SOP
select SOPNUMBE,SLPRSNID,SALSTERR,COMPRCNT,COMMAMNT,OCOMMAMT,PRCTOSAL,ACTSLAMT,ORSLSAMT,CMMSLAMT,ORCOSAMT,CURRNIDX,TRXSORCE FROM TWO.dbo.SOP10101

You need to set the "Pay commissions after invoice paid" option in Receivables Mgmt setup. (Tools >> Setup >> Sales >> Receivables Management).  You also need to join to the respective history transaction tables to get a date for the transaction.
0
 
LVL 1

Expert Comment

by:RadRichie
ID: 23596995
Oops. Those tables give you amounts before payment.  RM30501 contains the commission amounts after the transfer process (Tools >> Routines  >> Sales >> Transfer Commission), which respects the "Pay commissions after invoice paid" option.
0
 

Author Comment

by:jnikodym
ID: 23600570
Do i use this to create a view, then in Crystal select this view in the database expert and pull the data i need into my report?
0
 
LVL 1

Expert Comment

by:RadRichie
ID: 23615882
Yes, but better for performance to execute a stored procedure instead.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question