MIN and MAX Function should be allowed in Salary Structure

Frappe HR
1

Hi,

We are trying to implement the following in Salary Structure but are unable to do the same since the MIN or MAX function is not defined in global_defaults and I am getting the below error:

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 170, in eval_condition_and_formula
    amount = frappe.safe_eval(formula, self.whitelisted_globals, data)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 1512, in safe_eval
    return eval(code, eval_globals, eval_locals)
  File "<string>", line 1, in <module>
NameError: name 'min' is not defined

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/form/save.py", line 22, in savedocs
    doc.save()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 260, in save
    return self._save(*args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 296, in _save
    self.run_before_save_methods()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 876, in run_before_save_methods
    self.run_method("validate")
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 772, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1048, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1031, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 766, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 51, in validate
    self.calculate_net_pay()
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 443, in calculate_net_pay
    self.calculate_component_amounts()
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 74, in calculate_component_amounts
    amount = self.eval_condition_and_formula(struct_row, data)
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 177, in eval_condition_and_formula
    frappe.throw(_("Name error: {0}".format(err)))
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 355, in throw
    msgprint(msg, raise_exception=exc, title=title, indicator='red')
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 341, in msgprint
    _raise_exception()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 314, in _raise_exception
    raise raise_exception(msg)
frappe.exceptions.ValidationError: Name error: name 'min' is not defined
request.js:321:6
Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 170, in eval_condition_and_formula
    amount = frappe.safe_eval(formula, self.whitelisted_globals, data)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 1512, in safe_eval
    return eval(code, eval_globals, eval_locals)
  File "<string>", line 1, in <module>
NameError: name 'min' is not defined

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 61, in application
    response = frappe.handler.handle()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 21, in handle
    data = execute_cmd(cmd)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 56, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 1030, in call
    return fn(*args, **newargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/form/save.py", line 22, in savedocs
    doc.save()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 260, in save
    return self._save(*args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 296, in _save
    self.run_before_save_methods()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 876, in run_before_save_methods
    self.run_method("validate")
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 772, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1048, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1031, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 766, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 51, in validate
    self.calculate_net_pay()
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 443, in calculate_net_pay
    self.calculate_component_amounts()
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 74, in calculate_component_amounts
    amount = self.eval_condition_and_formula(struct_row, data)
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/doctype/salary_slip/salary_slip.py", line 177, in eval_condition_and_formula
    frappe.throw(_("Name error: {0}".format(err)))
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 355, in throw
    msgprint(msg, raise_exception=exc, title=title, indicator='red')
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 341, in msgprint
    _raise_exception()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 314, in _raise_exception
    raise raise_exception(msg)
frappe.exceptions.ValidationError: Name error: name 'min' is not defined

Why do we need the MIN or MAX funtion, here is the use case:

  1. We need to calculate EPF (Employees Provident Fund), which is calculated as (Basic Salary + HRA + Other Allowances) * 12% but its limited to a maximum amount of 1800/-. Now when I try to put a condition and calculate the EPF which means if I put a condition BS + HRA + OA <= 15000 then EPF = (HRA+BS+OA) * 12%, this works fine but in case when the condition is above 15000/- then we have set the value at 1800/- there is the calculation gets wrong if an employee is absent.

image
image1212×819 71.7 KB

Now above formula would give the error as explained above but if we want to calculate the value for above 15000 then the below condition is being used which is giving wrong values:

image
image1183×786 65.7 KB

Now the problem with the amount is as below:
In the below example
BS = 15750 + OA = 5250/-
So basically if the person comes on all days in a month then EPF would be 15750 + 5250 = 21000 * 12% = 2520 but since its greater than 1800/- then EPF amount would be 1800/-

Now in the below case the person was absent for 1 day so the salary would be 15225 + 5075 = 20300 and EPF = 20300 * 12% = 2436/- but since its also greater than 1800/- then EPF is again 1800/- but in our system the EPF is getting calculated as 1740/-

The above calculation of 1740/- is happening since 20300/- is greater than 15k as per the condition and then it gives it an amount of 1800/- and since there is one absent the amount 1800 * 29/30 = 1740/- which is WRONG and hence we need MIN and MAX functions to be allowed in SALARY STRUCTURES.

image
image1117×717 39.5 KB

1 Like
2

I implemented something similar to this with a simpler approach.

In my case PF = 5% of Gross Salary, but not exceeding 995. Secondly, Gross Salary depends on Leave Without Pay. So PF being a percentage of that, we needn’t worry about how it’s affected.

Screenshot%20from%202019-05-07%2014-07-30
Screenshot%20from%202019-05-07%2014-07-30880×594 52 KB

Max amount whose 5% gives 995 is 19900. Anything below this is 5%. Anything beyond is fixed at 995.

Screenshot%20from%202019-05-07%2013-56-40
Screenshot%20from%202019-05-07%2013-56-40871×597 52.8 KB

Calculating fixed amount.

Screenshot%20from%202019-05-07%2014-11-37
Screenshot%20from%202019-05-07%2014-11-37951×556 35.1 KB

Putting it together.

Hope this is helpful to your problem.
3

Thanks @adam26d for the reply.

But if you read my image2 which is same as your case and its a wrong way of implementing the thing. This is because if GS > 19900 so lets take an example where GS = 30000/- now if this person is absent for 1 day then the PF calculated by your formula would be 995 * 29/30 = 962/- instead of 995 it should have calculated.

I have checked this and I have been using this for the past 1 year but now the difference is coming to be large and hence not manageable.

In your case as well the MIN function should be used instead of the condition which I have been using myself as well.

4

@adityaduggal Not correct. Because the condition GS >=19900 (30,000 or 29,xxx) still applies if the person is absent for one day, therefore the amount will never exceed 995.

5

Try to run it on your system and check and you would find that your formula would calculate the PF as 962 and I never said it would calculate the PF more than 995, but actually it would calculate the PF amount less than 995

6

Compare the two slips

Screenshot%20from%202019-05-07%2015-16-57
Screenshot%20from%202019-05-07%2015-16-57739×482 28.6 KB
Screenshot%20from%202019-05-07%2015-15-58
Screenshot%20from%202019-05-07%2015-15-58734×416 28.5 KB

7

Well again you have not understood me correctly. We are only talking here about the 2nd Salary Slip where the Operations Manager has Basic = 250k value. If the payment days goes to 26 instead of 27 then what would be the NAPSA contribution as per your salary slip.

I think that would be 958 instead of 995 since its happening that ways with my case as it was pointed out in my first post.

8

You mean 26 days instead of 27 days right?

With my case NAPSA remains 995 because 24k is still over 19900 which is the limit (in your case limited to a maximum amount of 1800).

Our cases seem exactly alike.

Here’s a screenshot of the employee absent for 1 day:

Screenshot%20from%202019-05-07%2015-38-23
Screenshot%20from%202019-05-07%2015-38-23757×540 40.6 KB

Note that NAPSA is not exceeding the set limit and is not affected by the absence because the amount is still above the 5% of 19900.

9

hii do you got any solution for this problem also my same problem…

10

hi @adityaduggal,

Did u find a solution to EPF calculation based on payment days for people who are absent ?

ERPNext applies proportionate payment days calculation at the end for each component, while for PF , it is calculated based on the gross_pay for that month .

So if gross_pay - HRA (proportionate as per the present days) >=15k then 1800 else gross_pay - HRA (proportionate as per the present days) *12%

any one has found a solution / workaround to it ?

11

Thanks to @jignesh_shah for pointing me in the right direction.
I have done salary_slip.py customization and override Class for working days and payment days manual entry.

  • Working Days = 26 manually
  • Payment Days = 23 manually
  • Salary Structure assigned to Employee and Base = 25000
  • Over Time = 6253 manually

–

image
image1042×622 44.3 KB

:point_right: OT, PT, ESIC and PF (uncheck - depend on payment days)

–

image
image1039×475 46.5 KB

PT Formula

0 if ((((base/total_working_days)*payment_days) * 0.5) + (OT)) < 6000 else 80 if ((((base/total_working_days)*payment_days) * 0.5) + (OT)) < 9000 else 200 if ((((base/total_working_days)*payment_days) * 0.5) +(OT)) < 12000 else 200

ESIC Formula

((((base/total_working_days)*payment_days) * 0.5) + (((base/total_working_days)*payment_days) * 0.35) + (((base/total_working_days)*payment_days) * 0.075) +(((base/total_working_days)*payment_days) * 0.075) + OT) * 0.0075 if ((((base/total_working_days)*payment_days) * 0.5) + (((base/total_working_days)*payment_days) * 0.35) + (((base/total_working_days)*payment_days) * 0.075) +(((base/total_working_days)*payment_days) * 0.075) + OT) * 0.0075 < 21000 else 0

PF Formula

(((((base/total_working_days)*payment_days) * 0.5) + (((base/total_working_days)*payment_days) * 0.35) + (((base/total_working_days)*payment_days) * 0.075) +(((base/total_working_days)*payment_days) * 0.075) ) * 0.12 * 0.65) if ((((base/total_working_days)*payment_days) * 0.5) +(((base/total_working_days)*payment_days) * 0.075) +(((base/total_working_days)*payment_days) * 0.075)) < 15000 else 1800

Tested and :100:% working. hope this may help someone.

Thanks
Suresh

4 Likes
12

HI @Suresh_Thakor with this formula need custmization