Openbravo Issue Tracking System - Openbravo ERP
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
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
| related to | defect | 0039623 | new | Triage Omni OMS | Actual Qty in Commission Amt is set wrongly when Commission is configured as Cascade = No |
example_commission.tar.gz (411,378) 2018-10-17 21:48https://issues.openbravo.com/file_download.php?file_id=12280&type=bug