Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0039474
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 07. Sales managementmajoralways2018-10-17 21:482018-12-11 20:22
ReporterlbressanView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityhighResolutionfixedFixed in Version3.0PR19Q1
StatusclosedFix in branchFixed in SCM revisionfc4300d9862a
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product Version3.0PR18Q3.1SCM revision 
Review Assigned ToSandrahuguet
Web browserGoogle Chrome
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0039474: Commission calculation error

DescriptionWhen an invoice is created, with a payment condition that divides the payment plan into two or more plans, the calculation of the commission for fully paid invoices does not calculate the commission correctly for each line.
Steps To ReproduceSteps:
1.create payment term as shown in the file "payment termns". (30-60 days)see attached image.
2.create a sales invoice with a payment termn that divides the payment plan into two or more planssee attached image
   2.1 add sales representative (example: Javier Martín)
3.add many lines
4.book sales invoice
5.select the first plan and make 3 payments(different dates)
6.select the second plan and make 3 payments(different dates)
7.create commission for example: Javier Martin(see attached image)
    7.1 Frequency Type: Monthly
    7.2 Basis Status : Fully paid documents
    7.3 Basis Amount: Net amount
    7.4 List Details = true
    7.5 Cascade = true
8. Create line 10:
    8.1 Exclude=false
    8.2 Based on sales rep= true
    8.3 Business Partner Category = Customer - Tier 1
    8.4 Multiplier Amount = 0.13
9.Create line 20:
    8.1 Exclude=true
    8.2 Based on sales rep= false
    8.3 Business Partner Category = null
10. Generate commission for the last payment date
11. calculation made by the process of generating commission:
     amt = "net amount of the line" x ("expected amount of the payment plan" / "total amount of the invoice")
given that the payment termn divides the plan in two, the expected amount is half of the total of the invoice, then the calculation of the amount of each one of the lines of the invoice is reduced to half of its real value.
this generates an incorrect commission amount
Proposed Solutionchange the calculation of the commission for fully paid documents: example
SELECT h.C_Currency_ID,
      -- remove this line l.LineNetAmt * (psv.expected / h.grandtotal),
        l.LineNetAmt,
       CASE
           WHEN dt.docbasetype IN ('ARC',
                                   'APC') THEN -1
           ELSE 1
       END * l.QtyInvoiced,
             NULL,
             l.C_InvoiceLine_ID,
             h.DocumentNo,
             substr(COALESCE(prd.Name,l.Description),1,60),
             'Y'
FROM C_Invoice h
JOIN C_InvoiceLine l ON h.C_Invoice_ID = l.C_Invoice_ID
JOIN c_doctype dt ON h.c_doctype_id = dt.c_doctype_id
LEFT JOIN M_Product prd ON l.M_Product_ID = prd.M_Product_ID
LEFT JOIN fin_payment_sched_inv_v psv ON psv.C_Invoice_ID = h.C_Invoice_ID
WHERE h.DocStatus IN ('CL',
                      'CO')
  AND h.ISSOTRX = 'Y'
  AND h.AD_Client_ID = 'xxxxx'
  AND h.IsPaid = 'Y'
  AND h.SalesRep_ID IN
    (SELECT AD_User_ID
     FROM AD_User
     WHERE IsActive='Y'
       AND C_BPartner_ID='xxxxxxxxxxxxxxxx')
  AND AD_ISORGINCLUDED(h.AD_Org_ID, 'xx', h.AD_Client_ID) <> -1
  AND h.C_BPartner_ID IN
    (SELECT C_BPartner_ID
     FROM C_BPartner
     WHERE C_BP_Group_ID='zzzzzz')
GROUP BY 1,
         2,
         3,
         4,
         5,
         6,
         7,
         8 HAVING max(psv.LastPayment) >= to_date('01-12-2018')
AND max(psv.LastPayment) < to_date('30-12-2018') +1
TagsNo tags attached.
Attached Filesgz file icon example_commission.tar.gz [^] (411,378 bytes) 2018-10-17 21:48

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0039623 newTriage Omni OMS Actual Qty in Commission Amt is set wrongly when Commission is configured as Cascade = No 

-  Notes
(0107892)
AtulOpenbravo (developer)
2018-11-13 19:09
edited on: 2018-11-15 05:17

 Test Plan

Case I: Cascade = No

- Login as International Group Admin.
- Create a Payment Term header:
   Overdue Payment Days Rule: 60
   Add a line to it:
      Percentage due: 50
      Overdue Payment Days Rule: 30
- Create a Commission with:
      Business Partner / Sales Rep.: Javier Martín
      Frequency Type: Monthly
      Basis Document: Invoice
      Basis Status. Fully paid docuemnts
      Basis Amount: Net amount
      Cascade = No
   Add a line to it:
      Based on Sales Rep. = Y
      Multiplier amount: 0.13
- Create a Sales Invoice and associate the previously created payment terms and the sales representative to it.
- Line 1: Product - Agua sin Gas 1L, Quantity: 100, Tax: Entregas IVA 21%, Line Net Amount = 153.00
- Line 2: Product - Cerveza Ale 0,5L, Quantity: 100, Tax: Entregas IVA 21%, Line Net Amount = 204.00
- Line 3: Product - Bebida Energética 0,5L, Quantity: 1, Tax: Entregas IVA 21%, Line Net Amount = 368.00
- Total Gross Amount 877.25, Line Gross Amount:725.00, Complete it.
- Realize that two payment plan are available for this invoice.
  Due Date 1: Today + 30 days, Amount = 438.62
  Due Date 2: Today + 60 days, Amount = 438.63
- Pay each of it in three payments with different dates.
- Generate commission for the last payment date.
- Go to the Commission Payment window:
- Realize a there is payment created with Total Amount = 94.25.
- In Amount Tab: Amount = 94.25, Converted Amount = 725.00, Actual Quantity = 402
- In Details Tab:
   Line 1: Product - Cerveza Ale 0,5L, Actual Amount = 102.00, Converted Amount = 102.00, Actual Qty = 100
   Line 2: Product - Agua sin Gas 1L, Actual Amount = 76.50, Converted Amount = 76.50, Actual Qty = 100
   Line 3: Product - Bebida Energética 0,5L, Actual Amount = 184.00, Converted Amount = 184.00, Actual Qty = 1
   Line 4: Product - Bebida Energética 0,5L, Actual Amount = 184.00, Converted Amount = 184.00, Actual Qty = 1
   Line 5: Product - Agua sin Gas 1L, Actual Amount = 76.50, Converted Amount = 76.50, Actual Qty = 100
   Line 6: Product - Cerveza Ale 0,5L, Actual Amount = 102.00, Converted Amount = 102.00, Actual Qty = 100


  Case II: Cascade = Yes

- Set IsCascade flag in Commission as Yes.
- Follow same steps as above until generate commission steps.
- Realize a there is commission payment created with Total Amount = 94.25.
- In Amount Tab: Amount = 94.25, Converted Amount = 725.00, Actual Quantity = 201
- In Details Tab:
   Line 1: Product - Cerveza Ale 0,5L, Actual Amount = 204.00, Converted Amount = 204.00, Actual Qty = 100
   Line 2: Product - Agua sin Gas 1L, Actual Amount = 153.00, Converted Amount = 153.00, Actual Qty = 100
   Line 3: Product - Bebida Energética 0,5L, Actual Amount = 368.00, Converted Amount = 368.00, Actual Qty = 1
(0107909)
hgbot (developer)
2018-11-14 18:09

 Repository: erp/devel/pi
Changeset: fc4300d9862a45e8e9b3adfbff5b8826cbba43a2
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Tue Nov 13 19:23:04 2018 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/fc4300d9862a45e8e9b3adfbff5b8826cbba43a2 [^]

Fixes Issue 39474: Commission calculation error

When commission is set as Cascade = Yes, comission details
with invoiceline or orderline are deleted before creating
lines are same subsequent invoiceline or orderline.
Hence the information about the amount is lost once such
lines are deleted. To keep track amounts of the multiple
invoiceline or orderline they are stored in variable and
added to same subsequent invoiceline or orderline being
created.

---
M src-db/database/model/functions/C_COMMISSION_PROCESS.xml
---
(0107910)
Sandrahuguet (developer)
2018-11-14 18:10

 Code review + testing OK
(0108469)
hudsonbot (developer)
2018-12-11 20:22

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/470e3cd384c5 [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2018-10-17 21:48 lbressan New Issue
2018-10-17 21:48 lbressan Assigned To => Triage Finance
2018-10-17 21:48 lbressan File Added: example_commission.tar.gz
2018-10-17 21:48 lbressan Web browser => Google Chrome
2018-10-17 21:48 lbressan Modules => Core
2018-10-17 21:48 lbressan Resolution time => 1541545200
2018-10-17 21:48 lbressan Triggers an Emergency Pack => No
2018-10-18 10:03 Practics Issue Monitored: Practics
2018-11-02 13:47 gsuarez Web browser Google Chrome => Google Chrome
2018-11-02 13:47 gsuarez Type design defect => defect
2018-11-02 14:23 aferraz Assigned To Triage Finance => AtulOpenbravo
2018-11-02 21:31 AtulOpenbravo Status new => scheduled
2018-11-12 09:57 Sandrahuguet Web browser Google Chrome => Google Chrome
2018-11-12 09:57 Sandrahuguet Resolution time 1541545200 => 1542927600
2018-11-12 13:20 mgonzalezh Issue Monitored: mgonzalezh
2018-11-13 19:09 AtulOpenbravo Note Added: 0107892
2018-11-13 19:15 AtulOpenbravo Relationship added related to 0039623
2018-11-13 19:16 AtulOpenbravo Note Edited: 0107892 View Revisions
2018-11-14 18:09 hgbot Checkin
2018-11-14 18:09 hgbot Note Added: 0107909
2018-11-14 18:09 hgbot Status scheduled => resolved
2018-11-14 18:09 hgbot Resolution open => fixed
2018-11-14 18:09 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/fc4300d9862a45e8e9b3adfbff5b8826cbba43a2 [^]
2018-11-14 18:10 Sandrahuguet Review Assigned To => Sandrahuguet
2018-11-14 18:10 Sandrahuguet Note Added: 0107910
2018-11-14 18:10 Sandrahuguet Status resolved => closed
2018-11-14 18:10 Sandrahuguet Fixed in Version => 3.0PR19Q1
2018-11-15 05:17 AtulOpenbravo Note Edited: 0107892 View Revisions
2018-12-11 20:22 hudsonbot Checkin
2018-12-11 20:22 hudsonbot Note Added: 0108469

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker