Solved

How to calculate commissions on payments based on invoices from projects

  • 12 September 2022
  • 9 replies
  • 357 views
M
Rank
Userlevel 1

I have what seems to be an interesting challenge; how to calculate commissions based on paid project based invoices. This differs from many other commission based systems based on accrued sales.  Commissions at this company are based on payments received on project issued or related invoices.

I can see payments coming into table ARPayments based on a calendar date or fiscal period.  But when I try to connect those payments to the project that initiated the invoice and payment, that is where my challenge begins.  If I am not on the right path, please put forward some alternative suggestions.

Some constraints:

  • All payments are initiated by invoices
    • either directly issued through AR (so the project is referenced in the details tab) or
    • through a project generated invoice.
  • So, at least theoretically, a payment should not received unless an invoice, that is in AR, has been issued.

If I start a GI or report from the ARPayments table, to capture all payments based on a calendar period, and then connect to:

  • If I look at the applications tab on the payment to get the invoice (with the intent connecting to ARInvoice to connect to the Project and the Project Manager (field=OwnerID), that is table ARTranPostBal, to which there is no connection to any other table. I have tried in GI and Report Designer, I can not connect to that table.
  • Since I can not connect to ARTranPostBal, I tried to connect to variants of ARTran* but all either fail to connect or create duplicate values for each payment.
  • The connection I have been fighting with is: ARPayment » ARTran* (there are a few tables, ARTranPostBal has no connections even though it is referenced on the applications tab of the payments and applications application) » ARInvoice » PMProject (to get the project ID and Project Manager [OwnerID]).
    • It fails between ARTran* and ARInvoice.

So here is my question for the community, can you suggest a connection between an actual payment in ARPayment table and relate it to a source Project (in PMProject) that should have come from ARInvoice table in 1 report?  By extension, a payment taken/posted in ARPayment should fail to find a connection to a payment if it is unapplied to an invoice.  It should be listed as a payment received in the reporting period, but fail to find a connection to an invoice.

My current solution is to connect ARPayment to ARTranPost to get the invoice number related to a payment and then a separate GI to extract ARInvoice to PMProject.  Then export both to Excel and via a Vlookup to connect Project ID’s and Project Managers to payments for reporting purposes.  While it seems to work so far, it feels like a labour intensive solution.

I would like to think Acumtica can do better.  Can someone redirect my thinking and solve my challenge… please… :-)

 

icon

Best answer by webshoe 29 March 2023, 20:06

View original

9 replies

Debbie Reed CLA
Rank
Userlevel 3
Badge

I am replying only to perhaps bring this up into Acumatica view.  I cannot see any way to calculate commissions (put salesman on a line or header) in a Project, to carry to Invoices coming from Project Billing.  Sounds like mschumacher51 has found only to be able to do this via a GI.  If true, it would be good to find that connection in the DAC mentioned above.

Debbie Reed, W.M. Synergy
aaghaei
Rank
Userlevel 7
Badge +8

Debbie,

you will need to join ARAdjust with ARRegister(ARInvoice)

The logic is you will need to join the above tables on those key fields include “Adjd” ending. ie ARInvoice.DocType = ARAdjust.AdjdDocType and ARInvoice.RefNbr = ARAdjust.AdjdRefNbr but your Where Statement is going to be on “Adjg” ie AdjgDocType = “You Payment Doc Types” and AdjgFinPeriod = “Your Period of Payment”

if you couldn’t make it work let me me know that I can help you with the GI or query 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Debbie Reed CLA
Rank
Userlevel 3
Badge

Thank you, Reza @aaghaei! This confirms that there is not a way to use the commission features for invoices created via projects, so a GI is required?  I appreciate the information regarding the joins needed and will see if client wants to take that route.  Thanks again!

Debbie Reed, W.M. Synergy
aaghaei
Rank
Userlevel 7
Badge +8

Even those invoices created through projects, will be pushed to ARInvoice. You can still filter on your projects. I’m not sure the ProjectID exist in ARRegister (I’m not on my computer). If exist then perfect you can filter, if not still you can join your ARInvoice to PMProforma that has the ProjectID 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
V
Rank
Userlevel 7
Badge +11

Hi @mschumacher51 

Can you use the default ‘Commission Data’ Generic inquiry by setting a filter based on ProjectID on ARTran table? The Project information does exist on ARTran table. 

Hope this helps,

Regards,

VijayKumarU
W
Rank
Userlevel 2

I had forgotten I made this post but since it has responses now, I thought I would share the direction I ended up taking.  Although I like the direction that @aaghaei is going.  I wish I knew that then…  And @vkumar idea is also good if not for the client’s further requirements of commission rates changed based elements defined in attributes on the project, accumulated by project manager and only invoices that were paid.  My solution was not pretty but seemed to work for the time.

I could not create a one-to-one relationship between ARPayments and ARInvoice because a payment could be applied to multiple invoices and this duplicated records on the payment or invoice side.  So I had to find a one-to-one relationship.  I then realized that the payment application records had a one-to-one relation with the invoice.  While a payment can apply to multiple invoices, each application of that payment created a unique payment application record.  And since they configured their invoices to only have 1 project # captured in the header, that was my connection to the PMProject fields.  One of the additional requirements they put on me was to group this report by project manager (PMProject.OwnerID).

So I started from the ARTranPost table payment application records, because I only want to deal with paid invoices (see conditions below), connect to the ARInvoice table to get the project ID and from there to the PMProject table.  You will notice the connect to ARTran post is not standard in the DAC; SourceDocType instead of DocType, SourceRefNbr instead of RefNbr.  When I pulled a few hundred records from ARTranPost, these provided a better connection to work with payment application records.

I had to restrict by a range of payments (established on the parameters tab so the user could choose the range of dates to review) and ARTranPost.Type.  Adjusted identified the payment application records instead of the payment itself; those lines had the payment reference and invoice number references.  I had to include voids because they made many posting mistakes and often reversed payments to apply to the correct invoice/project.

The results grid was a mishmash of fields (it was one of those clients who kept asking for more fields “just in case” they need them).

While it worked in a clumsy fashion, I hope there is a better way in the future to report on payments made on projects and grouped by Project header fields (like OwnerID/Project Manager).  I am schumacher51 but it was so long ago when I originally posted this, I forgot this account.

aaghaei
Rank
Userlevel 7
Badge +8

Great work @webshoe . I had never looked at the ARTranPost and now I know it 😂.

Considering if multi-project is enabled in AR/AP Preferences, the project won’t be saved in the AR/AP Invoice headers and only will exist at the line level, the most reliable option related to the Project Invoices is to Join the ARInvoice with PMProforma. If you need a foolproof join from your ARPayment all the way up to PMProject below is what you need. I did write it in query form but of course, you can translate it to GI Relations or use it in Reports. The payment $ value you want, will be ARAdjust.CuryAdjgAmt and Your filter for the payments will be ARAdjust.AdjgDocType = ‘PMT’. You can use Inner Join only if you need the PM Invoices. Also please note that only Released documents will be included.

 

ARPayment projectPayments = SelectFrom<ARPayment>
.LeftJoin<ARAdjust>.On<ARPayment.docType.IsEqual<ARAdjust.adjgDocType>.And<ARPayment.refNbr.IsEqual<ARAdjust.adjgRefNbr>>>
.LeftJoin<ARInvoice>.On<ARAdjust.adjdDocType.IsEqual<ARInvoice.docType>.And<ARAdjust.adjdRefNbr.IsEqual<ARInvoice.refNbr>>>
.LeftJoin<PMProforma>.On<ARInvoice.refNbr.IsEqual<PMProforma.aRInvoiceRefNbr>.And<ARInvoice.proformaExists.IsEqual<True>.And<PMProforma.corrected.IsNotEqual<True>>>>
.LeftJoin<PMProject>.On<PMProforma.projectID.IsEqual<PMProject.contractID>>.View.Select(this);

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
W
Rank
Userlevel 2

Thanks @aaghaei .  For anyone reading this thread, I think I like your solution better than mine.  It is more direct and cleaner.  And you are right, the only reason my solution worked at all was because the client opted to configure 1 project per invoice.  At the time I was 3 months into Acumatica, given a deadline of a little over a week and drinking from a firehose trying to take it all in. 😅  I’m just happy it worked.

Debbie Reed CLA
Rank
Userlevel 3
Badge

 

 

Hello - I wanted to provide update on the path we took.  The commission calculation is simple and it is one salesman per customer.. so I implemented commissions.  I found that as long as you have: Commissions Enabled,

AR Preferences set to Commission by Payment,

the Salesperson setup and assigned to customer,

when the Invoice is generated via the pro forma, it will carry the Salesperson and Commission percentage.  Then Calculate commissions will generate the record when the invoice is paid. 

Then, I just created a separate GI to look at the commission records, and include the Project in the grid.

 

My GI:

 

 

Debbie Reed, W.M. Synergy

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved