Proposal summary
This proposal covers the scope of updating employee Labor Cost per month.
Requirement
Neutrinos need to update the employee cost which is used for labor costing calculations in the project module, according to the change in their payroll amounts in Adrenalin.
Our Solution
The Unit Cost field in the project task record is used for the labor cost calculations in the project module. The cost for an employee is calculated as,
Cost = Unit Cost (per hour) X Hours Worked (picked from timesheets)
The value for the Unit Cost field in the project task record will be picked from the Labor cost field in the employee record.
It is possible to update the employee Labor cost field according to the change in the payroll amount in Adrenalin. As there is already an integration planned between Adrenalin and NetSuite, we can include this update in that integration as a separate scope.
Note : Mandatorily post the approved timesheets to account the labor cost against projects.
The process involves following steps
Create a custom field in the Employee Record
Prerequisites
The monthly payroll amount(a single value) should be shared from Adrenalin to netsuite through integration.
Description
Will maintain a custom field in employee record to store the employee salary amount which is received from Adrenalin through integration. Only this field will be updated on every integration process.
Update employee cost on schedule basis.
Prerequisites
Neutrinos need to set up an appropriate work calendar for every active resource in Netsuite.
Description
The Labor Cost field in the employee record will be updated on the first day of every month through a scheduled script.
The script runs for the employee records if there is any change in their payroll amount for the previous month (Custom field). This can be achieved by analyzing the history of the custom field in the employee record (which stores the monthly payroll amount).
We need a per hour rate to calculate the employee cost. The monthly payroll amount should be converted to an hourly rate before updating the Labor cost field in employee records.
- For each employee, the total working hours assigned for a particular month can be calculated from the employee Work Calendar.
- The number of working days of a week and the working hours per day needs to be fetched using a work calendar saved search. Find out the number of such days in the previous month. Then total working hours assigned per month can be calculated.
- Exclude the days that have been mentioned as Non Working days (in their respective work calendar) for that month from the total hours.
- This value can be used to calculate the hourly rate of an employee from their monthly salary amount which is kept in the custom employee record field.
Formula for Hourly Rate= Payroll Value On custom field / (Total working hours of last Month)
Update the Unit cost in project task
The updated labor cost value will not be automatically reflected in the already existing project tasks. Whenever the Labor cost field in the employee record gets updated, then it needs to be updated in the project task records where that particular employee is assigned using a script.
- The unit Cost field in the project tasks with In Progress and Not Started Status can be updated.
- For the intercompany resources there should be an appropriate Exchange rate to convert their labor cost from employee currency to project currency. For that we will use the exchange rate of the last day of the previous month.
Error Handling
A custom record will be maintained to log the errors in case there are any issues in updating employee Labor cost or project task unit cost. By referring to this record, you can update the fields manually before moving to the timesheet import (only in the case of any process failure).
Assumptions
- There will be only a single value received for the monthly payroll amount.
- The Monthly payroll amount will be updated only once at the end of each month in Adrenalin.
Risks
- If the project cost budget is already set up with old employee cost then the updated employee cost will not reflect in the project budget labor cost.
- For the already existing employees in NetSuite, the Primary currency should be the same as in Adrenalin records.
- The cost will not be updated in the project tasks with “Completed” status. If the status of any project tasks changes from “Completed” to “In Progress”, then it will be out of the scope of this proposal.
- The timesheets should be imported to NetSuite only after completing the employee cost update process. As we are planning to schedule it on the first day of every month, the timesheet import can be started on day 3.
- The Estimated total cost for an employee will be “Unit cost * Planned work” (in hours). If a part of the planned time is already tracked against the project task, then when we change the unit cost, the Total Cost field will consider the already tracked time.
For example, for a project task, the employee unit cost was 60 and planned time was 10. Tracked 9hrs by this value. And then changed the unit cost to 70. Then the Cost will be 610 (9*60 + 1*70).
And If 10 was the planned time and tracked 10 hrs in the current unit cost. And then updated the unit cost field. Then there will not be any change on the unit cost field.
This is the standard netsuite flow. We can not change the same.
- The total number of working days will be calculated based only on the work calendar assigned to the employee on the NetSuite employee record. We will not consider any leaves or additional working for an employee’s working days.
- At every stage of cost calculation, the amount will be rounded off to 2 precision decimal points. So the final value may have slight deviations due to the rounding.
SCRIPT
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/*******************************************************************************
* CLIENTNAME: Neutrinos Technologies Pte Ltd
* TAG-340
* Schedule script for update cost
* **************************************************************************
* Date : 05-01-2022
*
* Author: Jobin & Jismi IT Services LLP
* Script Description :
* The script to update labor cost and unit cost
* Date created :05-01-2022
* Created by: Athul Krishna, JJobin & Jismi IT Services LLP
* REVISION HISTORY
* Revision 1.0 ${05-01-2022}
*
******************************************************************************/
define(['N/record', 'N/search'],
/**
* @param{record} record
* @param{search} search
*/
(record, search) => {
// returns the total days in month
function daysInMonth(month,year) {
return new Date(year, month, 0).getDate();
}
// Function returns total working hours after exceptions
function workCalendar(calenderid){
var workcalendarSearchObj = search.create({
type: "workcalendar",
filters:
[
["internalid","anyof",calenderid]
],
columns:
[
search.createColumn({
name: "monday",
summary: "GROUP",
label: "Monday"
}),
search.createColumn({
name: "tuesday",
summary: "GROUP",
label: "Tuesday"
}),
search.createColumn({
name: "wednesday",
summary: "GROUP",
label: "Wednesday"
}),
search.createColumn({
name: "thursday",
summary: "GROUP",
label: "Thursday"
}),
search.createColumn({
name: "friday",
summary: "GROUP",
label: "Friday"
}),
search.createColumn({
name: "sunday",
summary: "GROUP",
label: "Sunday"
}),
search.createColumn({
name: "saturday",
summary: "GROUP",
label: "Saturday"
}),
search.createColumn({
name: "workhoursperday",
summary: "GROUP",
label: "Work Hours Per Day"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN {exceptiondate} BETWEEN to_date(TRUNC(TRUNC({today}, 'MONTH')-1,'MONTH'), 'MM/DD/YYYY') AND LAST_DAY(TRUNC({today}, 'MONTH')-1) THEN 1 ELSE 0 END",
label: "Formula (Numeric)"
})
]
});
var monday,tuesday,wednesday,thursday,friday,saturday,sunday,work,nonWorking;
// var searchResultCount = workcalendarSearchObj.runPaged().count;
// log.debug("workcalendarSearchObj result count",searchResultCount);
workcalendarSearchObj.run().each(function(result){
monday=result.getValue({ name: "monday",
summary: "GROUP",
label: "Monday"})
tuesday=result.getValue({ name: "tuesday",
summary: "GROUP",
label: "Tuesday"})
wednesday=result.getValue({name: "wednesday",
summary: "GROUP",
label: "Wednesday"})
thursday=result.getValue({ name: "thursday",
summary: "GROUP",
label: "Thursday"})
friday=result.getValue({name: "friday",
summary: "GROUP",
label: "Friday"})
saturday=result.getValue({ name: "saturday",
summary: "GROUP",
label: "Saturday"})
sunday=result.getValue({name: "saturday",
summary: "GROUP",
label: "Saturday"})
work=result.getValue({name: "workhoursperday",
summary: "GROUP",
label: "Work Hours Per Day"}),
nonWorking=result.getValue({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN {exceptiondate} BETWEEN to_date(TRUNC(TRUNC({today}, 'MONTH')-1,'MONTH'), 'MM/DD/YYYY') AND LAST_DAY(TRUNC({today}, 'MONTH')-1) THEN 1 ELSE 0 END",
label: "Formula (Numeric)"
})
return true;
});
// To find days in the month
var getTotal,month,year
var da=new Date();
if(da.getMonth()==0){
getTotal=daysInMonth(12,da.getFullYear()-1)
month=11
year=(da.getFullYear()-1);
}else {
getTotal = daysInMonth(da.getMonth(), da.getFullYear());
month=(da.getMonth()-1)
year=da.getFullYear()
}
var dayCount=0
//To check Sunday working day
if(sunday==true){
for(var i=1;i<=getTotal;i++){
var newDate = new Date(year,month,i)
if(newDate.getDay()==7){
dayCount+=1;
}
}
}
//To check Monday working day
if(monday==true){
for(var i=1;i<=getTotal;i++){
var newDate = new Date(year,month,i)
if(newDate.getDay()==1){
dayCount+=1;
}
}
}
//To check Tuesday working day
if(tuesday==true){
for(var i=1;i<=getTotal;i++){
var newDate = new Date(year,month,i)
if(newDate.getDay()==2){
dayCount+=1;
}
}
}
//To check Wednesday working day
if(wednesday==true){
for(var i=1;i<=getTotal;i++){
var newDate = new Date(year,month,i)
if(newDate.getDay()==3){
dayCount+=1;
}
}
}
//To check Thrusday working day
if(thursday==true){
for(var i=1;i<=getTotal;i++){
var newDate = new Date(year,month,i)
if(newDate.getDay()==4){
dayCount+=1;
}
}
}
//To check Friday working day
if(friday==true){
for(var i=1;i<=getTotal;i++){
var newDate = new Date(year,month,i)
if(newDate.getDay()==5){
dayCount+=1;
}
}
}
//To check Saturday working day
if(saturday==true){
for(var i=1;i<=getTotal;i++){
var newDate = new Date(year,month,i)
if(newDate.getDay()==6){
dayCount+=1;
}
}
}
//Total workinghour calculation
var totalWorkingHour=(dayCount*work)-(nonWorking*work);
log.debug("Today Day Count",getTotal)
log.debug("Total Working Day Count",dayCount)
log.debug("Total Working Hours After Exception",totalWorkingHour)
return totalWorkingHour;
}
/**
* Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
* @param {Object} inputContext
* @param {boolean} inputContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Object} inputContext.ObjectRef - Object that references the input data
* @typedef {Object} ObjectRef
* @property {string|number} ObjectRef.id - Internal ID of the record instance that contains the input data
* @property {string} ObjectRef.type - Type of the record instance that contains the input data
* @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use in the map/reduce process
* @since 2015.2
*/
// Finds the employee record that payroll amount changed
const getInputData = (inputContext) => {
var employeeSearchObj = search.create({
type: "employee",
filters:
[
["systemnotes.field","anyof","CUSTENTITY_JJ_EMP_PAYROLL_AMOU"],
"AND",
["systemnotes.date","on","today"]
],
columns:
[
search.createColumn({
name: "entityid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "workcalendar", label: "Work Calendar"}),
search.createColumn({
name: "currency",
join: "subsidiary",
label: "Currency"
}),
search.createColumn({name: "custentity_jj_emp_payroll_amou", label: "Payroll Amount"})
]
});
return employeeSearchObj;
}
/**
* Defines the function that is executed when the map entry point is triggered. This entry point is triggered automatically
* when the associated getInputData stage is complete. This function is applied to each key-value pair in the provided
* context.
* @param {Object} mapContext - Data collection containing the key-value pairs to process in the map stage. This parameter
* is provided automatically based on the results of the getInputData stage.
* @param {Iterator} mapContext.errors - Serialized errors that were thrown during previous attempts to execute the map
* function on the current key-value pair
* @param {number} mapContext.executionNo - Number of times the map function has been executed on the current key-value
* pair
* @param {boolean} mapContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} mapContext.key - Key to be processed during the map stage
* @param {string} mapContext.value - Value to be processed during the map stage
* @since 2015.2
*/
const map = (mapContext) => {
try {
var pushArray=[]
var pushObj={}
//log.debug({title: "hlo", details: mapContext})
var searchResult = JSON.parse(mapContext.value);
log.debug({title:"Search Result",details:searchResult.values})
var calenderID=searchResult.values.workcalendar.value
var currency=searchResult.values['currency.subsidiary'].value
log.debug("employee currency",currency)
pushObj.currency=currency
var payroll=searchResult.values.custentity_jj_emp_payroll_amou;
var employeeRecordId=searchResult.values.internalid.value;
log.debug("employee internal id",employeeRecordId)
// finding payrollHour rate
log.debug("payroll amount",payroll)
log.debug("employee calenderid",calenderID)
var hoursWorked=workCalendar(calenderID);
log.debug("working hours after deduction",hoursWorked);
// Calculation of Labor Cost
var payrollHour=Math.round(((payroll/hoursWorked)+Number.EPSILON)*100)/100;
pushObj.payrollHour=payrollHour
pushArray.push(pushObj);
log.debug("Labor Cost Value",payrollHour)
//setting value to Labour Cost
var employeeRec=record.load({
type:'employee',
id:employeeRecordId,
isDynamic: true,
});
employeeRec.setValue({
fieldId:'laborcost',
value:payrollHour
});
employeeRec.save();
}catch (e) {
log.debug(e)
log.error(e)
}
mapContext.write({
key: employeeRecordId,
value: pushArray
});
}
// returns the exchange rate between base and transaction currency on last month last date
function exchange(base,transaction){
var currencyrateSearchObj = search.create({
type: "currencyrate",
filters:
[
["basecurrency","anyof",base],
"AND",
["transactioncurrency","anyof",transaction],
"AND",
["effectivedate","on","lastmonth"]
],
columns:
[
search.createColumn({
name: "exchangerate",
summary: "MAX",
label: "Exchange Rate"
}),
search.createColumn({
name: "effectivedate",
summary: "MAX",
label: "Effective Date"
})
]
});
var searchResultCount = currencyrateSearchObj.runPaged().count;
var exchangeRate
//log.debug("currencyrateSearchObj result count",searchResultCount);
currencyrateSearchObj.run().each(function(result){
exchangeRate=result.getValue({
name: "exchangerate",
summary: "MAX",
label: "Exchange Rate"
})
return true;
});
return exchangeRate;
}
/**
* Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
* automatically when the associated map stage is complete. This function is applied to each group in the provided context.
* @param {Object} reduceContext - Data collection containing the groups to process in the reduce stage. This parameter is
* provided automatically based on the results of the map stage.
* @param {Iterator} reduceContext.errors - Serialized errors that were thrown during previous attempts to execute the
* reduce function on the current group
* @param {number} reduceContext.executionNo - Number of times the reduce function has been executed on the current group
* @param {boolean} reduceContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} reduceContext.key - Key to be processed during the reduce stage
* @param {List<String>} reduceContext.values - All values associated with a unique key that was passed to the reduce stage
* for processing
* @since 2015.2
*/
const reduce = (reduceContext) => {
try {
var employee = reduceContext.key;
log.debug("Employee id in reduce", employee)
var employeeResult = JSON.parse(reduceContext.values);
var currency=employeeResult[0].currency;
log.debug("Employee Currency",currency);
var amount=employeeResult[0].payrollHour;
//log.debug("amount",amount)
// var employeeResult = JSON.parse(reduceContext.values[1]);
// log.debug("cur",currency)
log.debug({title: 'getReduceData', details: employeeResult})
// Search for Project task that assigned to employee
var projecttaskSearchObj = search.create({
type: "projecttask",
filters:
[
["status", "anyof", "PROGRESS", "NOTSTART"],
"AND",
["assignee", "anyof", employee]
],
columns:
[
search.createColumn({name: "title", label: "Name"}),
search.createColumn({name: "company", label: "Project"}),
search.createColumn({name: "status", label: "Status"}),
search.createColumn({name: "internalid", label: "Internal ID"})
]
});
var searchResultCount = projecttaskSearchObj.runPaged().count;
// log.debug("projecttaskSearchObj result count",searchResultCount);
if(searchResultCount>0) {
var projectTaskId = [];
projecttaskSearchObj.run().each(function (result) {
projectTaskId.push(result.getValue({name: "internalid", label: "Internal ID"}))
return true;
});
// Project Task Load
for (var j = 0; j < projectTaskId.length; j++) {
var projectTaskRec = record.load({
type: "projecttask",
id: projectTaskId[j],
isDynamic: true
});
// log.debug("projecttaslload", projectTaskRec)
var line_count = projectTaskRec.getLineCount({
sublistId: 'assignee'
});
// iterating through assignee sublist
for (var i = 0; i < line_count; i++) {
projectTaskRec.selectLine({
sublistId: 'assignee',
line: i
});
// Getting the vaue of the sublist field resource
var assignee_id_from_itemline = projectTaskRec.getCurrentSublistValue({
sublistId: 'assignee',
fieldId: 'resource',
});
// checking assignee and employee is same
if (assignee_id_from_itemline === employee) {
var project=projectTaskRec.getValue({
fieldId: 'company'
});
var projectCurrency=search.lookupFields({
type: 'job',
id: project,
columns: ['currency']
});
log.debug("Project ID",project)
log.debug("Project currency",projectCurrency.currency[0].value)
// setting the value in the sublist field unit cost
if(currency!==projectCurrency.currency[0].value){
log.debug("amount before",amount)
var exchangeRate=exchange(currency,projectCurrency.currency[0].value);
log.debug("Exchange Rate",exchangeRate)
amountAfter=Math.round(((amount*exchangeRate)+Number.EPSILON)*100)/100;
log.debug("amountAfterExchange",amountAfter)
var unit = projectTaskRec.setCurrentSublistValue({
sublistId: 'assignee',
fieldId: 'unitcost',
value: amountAfter
});
}else {
var unit = projectTaskRec.setCurrentSublistValue({
sublistId: 'assignee',
fieldId: 'unitcost',
value: amount
});
}
projectTaskRec.commitLine({
sublistId: 'assignee'
});
// var project=projectTaskRec.getValue({
// fieldId: 'company'
// });
// log.debug("unitcost", unit)
}
}
projectTaskRec.save();
}
}
// var projectTaskRec = record.load({
// type: "projecttask",
// id: projectTaskId[0],
// isDynamic: true
// });
// log.debug("projecttaslload",projectTaskRec)
}catch (e) {
log.debug(e);
log.error(e);
}
}
return {getInputData, map, reduce}
});