Timesheet based invoicing with project specific cost rates?

[smw]

Dear all,

we’re currently in the process of setting up timesheet based invoicing for our projects and have questions around it.

What we’d like to do is define activities like ‘development (junior level)’, ‘development (senior level)’, ‘consulting (senior level’) or ‘consulting (architect level)’ and then associate project specific rates to those activities.

Our plan is to have the team fill their timesheets with customer, project and activity type and have the right hourly rate automatically associated with the entry.

We noticed that activities and their rates by default are not project specific, making timesheet based invoicing impractical for T&M projects out of the box.

So, what is the best approach for what we’re trying to accomplish?

[mujeerhashmi]

I guess you will need custom development for this. You can add project field to activity cost and fetch the rates for activities based on project in sales invoice.

[smw]

Thanks for the hint!

Just to make sure I’ve understood that properly: the idea is to define activity types with default cost rates and then associate individual cost definition that in addition to their standard link to a specific employee would be linked to a project.

Then, while creating a sales invoices from a timesheet, one would fetch the actual rate by looking up activity costs matching both the employee and the project or - if none was found - fall back to default rates. Is that the idea?

If so, questions that come to mind are if this could be also linked to the timesheet’s “billing details” with their total billing and costing amounts (not to screw up the ERP’s view on projects margin etc.).

Also, what if you’d have more than just one or two workers on a project: you would have to define activity costs for every project each of them is on due to the activity cost being linked to the employee record. It seems as if not much would be gained compared to having activity types per project in the first place, no?

Doing one step back: maybe using timesheets is the wrong approach for T&M project invoicing?

How do others do this?

[szufisher]

my solution

1. create child doctype Project Activity Rate with three fields: Activity Type, Billing Rate, Costing Rate
   

   

2. add table type field to project doctype, set option as Project Activity Rate
   

   

3. create new app and api.py file, copy the following code to api.py, replace fisher_test with your own app name
   

   

import frappe from erpnext.projects.doctype.timesheet.timesheet import get_activity_cost as original_get_activity_cost

@frappe.whitelist() def get_activity_cost(employee=None, activity_type=None, currency=None, project=None):
rate = frappe.db.get_values(“Project Activity Rate”,
{“parent”: project,
“activity_type”: activity_type},
[“costing_rate”, “billing_rate”], as_dict=True) rate = rate and rate[0] or original_get_activity_cost(employee, activity_type, currency)
return rate

4. create a js script for Time Sheet doctype, copy the following code, replace fisher_test with your own app name
   
   

frappe.ui.form.on(‘Timesheet Detail’, {
activity_type: function(frm, cdt, cdn) {
const child = frappe.get_doc(cdt, cdn);
frappe.call({
method: “fisher_test.api.get_activity_cost”,
args: {
employee: frm.doc.employee,
activity_type: frm.selected_doc.activity_type,
currency: frm.doc.currency,
project:child.project
},
callback: function(r){
if(r.message){
frappe.model.set_value(cdt, cdn, ‘billing_rate’, r.message[‘billing_rate’]);
frappe.model.set_value(cdt, cdn, ‘costing_rate’, r.message[‘costing_rate’]);
let row = frappe.get_doc(cdt, cdn);
let billing_amount = 0.0;
let base_billing_amount = 0.0;
let exchange_rate = flt(frm.doc.exchange_rate);
frappe.model.set_value(cdt, cdn, ‘base_billing_rate’, flt(row.billing_rate) * exchange_rate);
frappe.model.set_value(cdt, cdn, ‘base_costing_rate’, flt(row.costing_rate) * exchange_rate);
if (row.billing_hours && row.is_billable) {
base_billing_amount = flt(row.billing_hours) * flt(row.base_billing_rate);
billing_amount = flt(row.billing_hours) * flt(row.billing_rate);
}
frappe.model.set_value(cdt, cdn, ‘base_billing_amount’, base_billing_amount);
frappe.model.set_value(cdt, cdn, ‘base_costing_amount’, flt(row.base_costing_rate) * flt(row.hours));
frappe.model.set_value(cdt, cdn, ‘billing_amount’, billing_amount);
frappe.model.set_value(cdt, cdn, ‘costing_amount’, flt(row.costing_rate) * flt(row.hours));
}
}
});
}
})

5. goto project form view, maintain the activity type billing rate and costing rate for the project

6. create new time sheet for the project, select the project at header
   
   

[smw]

Wow, thank you for your priceless help that is a HUGE push forward for me! I’ll add this to our ERPnext installation ASAP and and give feedback!

Looks clean and to the point. Wouldn’t this be worth a PR? The requirement must be fairly common amongst consultancies using ERPnext.

[CA_B.C_Chechani]

Thanks a lot to share complete deatils. It will be very helpful for us too as we are going to deploy ERPNext for large Company Secretary firm , will replicate the functionality and understand

[smw]

So, meanwhile I have tried out your suggestions and it seems they work as expected! That is pretty good! A few additions for the uninitiated, maybe:

1. … to add an application, do the following:

   $ bench new-app my_app

2. … relocate into the appropriate directory:

   $ cd frappe-bench/apps/my_app/my_app

3. … when in the appropriate directory, add api.py as follows:

   $ vi api.py
   <when adding the API code, make sure to follow the formatting. Do not just carbon copy what’s in szufisher’s code, double check against the screen shot! Pyhon is pretty strict with formatting…>

4. … build the app and install it into your ERP’s site:

   $ bench build --app my_app
   $ bench --site my.site.com install-app my_app

5. … create your client side script and here again, make sure to exactly follow the example as depicted in the screenshot, not as in the copyable code snippet.

Hope that helps…