We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Greatplains - Crystal Reports

Medium Priority
372 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.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is there a field in GP that shows the invoice has been paid?

mlmcc

Author

Commented:
I'm sure there is somewhere in the SQL tables
CERTIFIED EXPERT

Commented:
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.)

Author

Commented:
We run stored procedures all the time in SQL with GP.
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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.

Author

Commented:
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?
Yes, but better for performance to execute a stored procedure instead.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.