July 19, 2012 
Commissions are typically difficult to create generic reports for, as many companies have unique commission structures. We find that out-of-the-box functionality in GP does not really work for most companies and often the commissions are recorded in ways other than what was intended. That said, if you find that you are using the commissions functionality in any capacity at all, you may find yourself needing to report on the commissions data in the GP tables.
The view below returns all the commissions from the SOP (Sales Order Processing) module and also includes commissions from RM (Receivables Management) transactions that did not come from SOP. Both posted and unposted transactions are included, but I have added a column called Posted to show the posting status so you can easily exclude the unposted transactions if you need to. More notes are found in the comments in the view. This code was tested on a few different sets of data, however, it may be that this will not work for your specific data – before using this to base commissions on, please test the results.
Related code and table information:
CREATE VIEW view_Commissions_Details AS --***************************************************************** -- view_Commissions_Details -- Created Jul 19, 2012 by Victoria Yudin, Flexible Solutions, Inc. -- For updates see https://victoriayudin.com/gp-reports/ -- Returns all commissions details for SOP transactions -- (invoices and returns only) -- also adds in non-SOP transactions commissions from RM tables -- Only shows functional amounts -- Excludes voided transactions --*****************************************************************select CUSTNMBR Customer_ID, DOCDATE Document_Date, GLPOSTDT GL_Posting_Date, Data_Source, case RMDTYPAL when 1 then 'Invoice' when 3 then 'Debit Memo' when 5 then 'Service / Repair' when 7 then 'Credit Memo' when 8 then 'Return' end Document_Type, DOCNUMBR Document_Number, SEQNUMBR Sequence_Number, TRXSORCE Trx_Source, SLPRSNID Salesperson, SALSTERR Territory, case when RMDTYPAL < 7then Sales_Amount else Sales_Amount * -1 end Sales_Amount, case when RMDTYPAL < 7 then Non_Comm_Amount else Non_Comm_Amount * -1 end Non_Comm_Amount, cast(PRCTOSAL as numeric)/100 Percent_of_Sale, case when RMDTYPAL < 7 then Commission_Amount else Commission_Amount * -1 end Commission_Amount, cast(COMPRCNT as numeric)/100 Commission_Percent, case POSTED when 0 then 'No' else 'Yes' end Posted from (select r.CUSTNMBR, r.DOCDATE, r.GLPOSTDT, c.* from (select Data_Source = 'RM History', RMDTYPAL, DOCNUMBR, SEQNUMBR, TRXSORCE, SLPRSNID, SALSTERR, SLSAMNT Sales_Amount, NCOMAMNT Non_Comm_Amount, PRCTOSAL, COMDLRAM Commission_Amount, COMPRCNT, POSTED from RM30501 -- RM Commission History where left(TRXSORCE,5) <> 'SLSTE' union all select Data_Source = 'RM Work', RMDTYPAL, DOCNUMBR, SEQNUMBR, TRXSORCE, SLPRSNID, SALSTERR, SLSAMNT Sales_Amount, NCOMAMNT Non_Comm_Amount, PRCTOSAL, COMDLRAM Commission_Amount, COMPRCNT, POSTED from rm10501 -- RM Commission Work where left(TRXSORCE,5) <> 'SLSTE') c -- RM commissions inner join (select RMDTYPAL, DOCNUMBR, DOCDATE, GLPOSTDT, CUSTNMBR from RM10301 --RM Work union select RMDTYPAL, DOCNUMBR, DOCDATE, GLPOSTDT, CUSTNMBR from RM20101 --RM Open where VOIDSTTS = 0 union select RMDTYPAL, DOCNUMBR, DOCDATE, GLPOSTDT, CUSTNMBR from RM30101 --RM History where VOIDSTTS = 0) r --all RM transactions on r.RMDTYPAL = c.RMDTYPAL and r.DOCNUMBR = c.DOCNUMBR union all select s.CUSTNMBR, s.DOCDATE, s.GLPOSTDT, Data_Source = 'SOP', case c.SOPTYPE when 3 then 1 else 8 end RMDTYPAL, c.SOPNUMBE DOCNUMBR, c.SEQNUMBR, c.TRXSORCE, c.SLPRSNID, c.SALSTERR, c.ACTSLAMT Sales_Amount, c.NCOMAMNT Non_Comm_Amount, c.PRCTOSAL, c.COMMAMNT Commission_Amount, c.COMPRCNT, case c.TRXSORCE when '' then 0 else 1 end POSTED from SOP10101 c --SOP commissions (work and history) inner join (select SOPTYPE, SOPNUMBE, DOCDATE, GLPOSTDT, CUSTNMBR from SOP10100 where SOPTYPE in (3,4) and VOIDSTTS = 0 union select SOPTYPE, SOPNUMBE, DOCDATE, GLPOSTDT, CUSTNMBR from SOP30200 where SOPTYPE in (3,4) and VOIDSTTS = 0) s --SOP transactions on s.SOPTYPE = c.SOPTYPE and s.SOPNUMBE = c.SOPNUMBE where c.SOPTYPE in (3,4)) a --all data GO GRANT SELECT ON view_Commissions_Details to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Hi Victoria,
I’m a relative “rookie” with SQL so bear with me but we are running SQL 18.4. When I run this commission script I’m getting the following errors –
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near ‘SLSTE’.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near ‘SLSTE’.
Msg 102, Level 15, State 1, Line 64
Incorrect syntax near ‘r’.
Msg 102, Level 15, State 1, Line 86
Incorrect syntax near ‘)’.
Any suggestions?
Thank you,
Scott
LikeLike
Hi Scott,
Sorry about that. Once in a while something happens behind the scenes on the blog platform and the less than and greater than signs get stripped out of the code. I believe I put them back – can you please try re-copying the code? If you’re still getting the error add
< >
before the ‘SLSTE’ on the two lines where you see that string.
Thanks,
-Victoria
LikeLike
Hi Victoria,
Thank you for the quick response. I re-ran it with this change and got these errors –
Msg 4145, Level 15, State 1, Line 18
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ‘Sales_Amount’.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near ‘Non_Comm_Amount’.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ‘Commission_Amount’.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ‘Posted’.
Msg 102, Level 15, State 1, Line 86
Incorrect syntax near ‘a’.
Msg 15151, Level 16, State 1, Line 89
Cannot find the object ‘view_Commissions_Details’, because it does not exist or you do not have permission.
Don’t spend a ton of time on this if it’s something on my end.
Thanks,
Scott
LikeLike
Scott, it’s likely not getting copied correctly, and it’s probably the blog software – sorry about that. If you give me your email I can email you the script directly and it should not have any of these issues.
-Victoria
LikeLike
Thanks again for the site Victoria – it helps a BUNCH on a regular basis. I am trying to delete / merge some super old sales person records. When I hit the delete button I get “Commissions exist for this salesperson…” — nothing in any of the following tables, so I am curious if you had another place I could dig with my shovel? not in RM10501, RM30101, RM30501, SOP30300, SOP30200, SOP10100, SOP10200 or SOP10101 (also checked RM00101 just for giggles) – I know there are a total of 42 tables in GP with that field, but picking off the big 9 – there has to be another obvious one… thoughts? I also ran your view for that slprsnid and nothing…
LikeLike
Jim,
The other table I would check is RM10504.
-Victoria
LikeLike
after much digging – looks like the one I was having most issues with were RM00302 — another one for your magic tool belt
thanks for the help!
LikeLike
Hi Victoria,
I, too, am extremely grateful for everything you publish. I frequently rely on your articles, queries, and tips. I found what I believe to be an issue with the query. The credit memos are not included in the RM10501 or RM30501 tables since they aren’t used for commissions calculations. They are retrieved from the RM transaction tables, but then dropped by the inner join with the commission tables. If/when I come up with an alternate query, I will post that.
Thanks again!
Courtney
LikeLike
Victoria – I am testing this at a customer’s site, and I’ve discovered something I don’t understand. My issue is with a SOP document that has been posted. It looks like the view pulls SOP10101.ACTSLSAMT for this document as the sales_amount in the view. My sales document has two commissions rows, split 50/50 between two salespeople/territories. In SOP10101, actslamt and orslsamt are both 0.00 for ONE line of this document (the second commission). The cmmslamt and orcosamt fields are both populated correctly with the commission sales amount. Can you provide any insight into why you are using the actslamt field instead of the commslamt field for SOP documents in the view?
Thank you for all that you do/provide. I visit your site just about every day.
LikeLike
I did a bit more testing on this. If I have a sales order with one line item at $10, that line item can only be assigned to ONE sales person. That drives the value of the sop10101.actslamt field – $10. That gets assigned to the row in SOP10101 that is for the salesperson assigned to the line item in Sales Item Detail Entry. Any other split commissions in Sales Commission Entry will have 0.00 for Line Item Sale Amount (sop10101.actslamt).
LikeLike
Hi Sherry,
Thanks for the update. That’s right, even though you can enter the split commission (you have to trick GP into it, right?) it’s not stored the same way. Do you think I should change how this is shown?
Thanks,
-Victoria
LikeLike