Scenario
Create Automated journals at the end of every month except February. For the month February, the journals will be created only in the month of March.
Solution
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define(['N/record', 'N/search', 'N/format'],
/**
* @param{record} record
* @param{search} search
* @param{search} format
*/
(record, search, format) => {
/**
* Function to get all the time sheet data that is created in a particular month
* @param {*} filter
* @returns {}
*/
function getAllDateOnCurrentMonth(filter) {
try {
let timebillSearchObj = search.create({
type: "timebill",
filters:
[
["date", "within", filter],
"AND",
["approvalstatus", "anyof", "3"],
"AND",
["duration", "greaterthan", "0"],
"AND",
["employee.laborcost","lessthanorequalto","0.00"]
],
columns:
[
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "customer",
summary: "GROUP",
label: "Donor"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{durationdecimal}",
label: "Total Time per month"
})
]
});
let searchResultCount = timebillSearchObj.runPaged().count;
if (searchResultCount > 0) {
return timebillSearchObj
}
else {
return {}
}
}
catch (err) {
log.error("error@getAllDateOnCurrentMonth", err)
return {}
}
}
/**
* Function to get the project details
* @param {*} project
* @returns {}
*/
function searchProjectDetails(project) {
try {
let jobSearchObj = search.create({
type: "job",
filters:
[
["isinactive", "is", "F"],
"AND",
["internalid", "anyof", project]
],
columns:
[
search.createColumn({ name: "currency", label: "Currency" }),
search.createColumn({ name: "subsidiary", label: "Regional Office" }),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
let searchResultCount = jobSearchObj.runPaged().count;
let dataObject = {}
if (searchResultCount > 0) {
jobSearchObj.run().each(function (result) {
dataObject.currency = result.getValue({
name: "currency", label: "Currency"
})
dataObject.subsidiary = result.getValue({
name: "subsidiary", label: "Regional Office"
})
return true;
});
return dataObject;
}
else {
return {}
}
}
catch (err) {
log.error("error@projectDetails", err)
return {}
}
}
/**
* Funtion to get the jouranl record creation data division, activity code and monthly rate
* @param employee
* @param month
* @param year
* @param projects
* @returns {*[]}
*/
function payRollAllocationSearch(employee, month, year, projects) {
try {
let customrecord_jj_payroll_detailsSearchObj = search.create({
type: "customrecord_jj_payroll_details",
filters:
[
["custrecord_jj_payroll_emp", "anyof", employee],
"AND",
["custrecord_jj_month", "anyof", month],
"AND",
["custrecord_jj_year", "is", year],
"AND",
["isinactive", "is", "F"],
"AND",
["custrecord_jj_project_period.custrecord_jj_project", "anyof", projects],
"AND",
["custrecord_jj_project_period.isinactive", "is", "F"]
],
columns:
[
search.createColumn({ name: "custrecord_jj_monthly_rate", label: "Monthly Rate" }),
search.createColumn({ name: "custrecord_jj_month", label: "Month" }),
search.createColumn({ name: "custrecord_jj_year", label: "Year" }),
search.createColumn({ name: "custrecord_jj_payroll_emp", label: "Employee" }),
search.createColumn({
name: "custrecord_jj_pro_activity_code",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project Activity Code"
}),
search.createColumn({
name: "custrecord_jj_proj_div",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project Division"
}),
search.createColumn({
name: "internalid",
sort: search.Sort.DESC,
label: "Internal ID"
}),
search.createColumn({
name: "custrecord_jj_project",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project"
}),
search.createColumn({
name: "internalid",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Internal ID"
})
]
});
let searchResultCount = customrecord_jj_payroll_detailsSearchObj.runPaged().count;
let dataArray = [];
if (searchResultCount > 0) {
customrecord_jj_payroll_detailsSearchObj.run().each(function (result) {
let dataObj = {};
dataObj.activityCode = result.getValue({
name: "custrecord_jj_pro_activity_code",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project Activity Code"
});
dataObj.division = result.getValue({
name: "custrecord_jj_proj_div",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project Division"
});
dataObj.monthlyRate = result.getValue({
name: "custrecord_jj_monthly_rate", label: "Monthly Rate"
});
dataObj.project = result.getValue({
name: "custrecord_jj_project",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project"
});
dataObj.allocationId = result.getValue({
name: "internalid",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Internal ID"
});
dataObj.employee = result.getValue({
name: "custrecord_jj_payroll_emp", label: "Employee"
});
dataArray.push(dataObj)
return true;
});
return dataArray;
}
else {
return []
}
}
catch (err) {
log.error("error@payRollAllocationSearch", err)
return []
}
}
/**
* Function to Process Date
* @param {*} dateValue
* @returns {string}
*/
function processDate(dateValue) {
try {
let gmtDelegateDate = format.format({
value: dateValue,
type: format.Type.DATETIME,
timezone: format.Timezone.GMT
});
let startDateText = gmtDelegateDate.split(' ')
let delegationStartDateText = startDateText[0];
return delegationStartDateText;
}
catch (err) {
log.debug("error@ProcessDate", err)
return false
}
}
/**
* Function to get the projects data of a particular employee
* @param {*} employeeArray
* @param {*} filter
* @returns {}
*/
function getEmployeeProjectData(employeeArray, filter) {
try {
let employeeObject = {};
let timebillSearchObj = search.create({
type: "timebill",
filters:
[
["date", "within", filter],
"AND",
["approvalstatus", "anyof", "3"],
"AND",
["employee", "anyof", employeeArray],
"AND",
["duration", "greaterthan", "0"],
"AND",
["employee.laborcost","lessthanorequalto","0.00"]
],
columns:
[
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "customer",
summary: "GROUP",
label: "Donor"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{durationdecimal}",
label: "Total Time per month"
})
]
});
let searchResultCount = timebillSearchObj.runPaged().count;
let resultArray = [];
if (searchResultCount > 0) {
timebillSearchObj.run().each(function (result) {
let employee = result.getValue({
name: "employee",
summary: "GROUP",
label: "Employee"
})
let totalTime = result.getValue({
name: "formulanumeric",
summary: "SUM",
formula: "{durationdecimal}",
label: "Total Time per month"
})
if (!employeeObject.hasOwnProperty(employee)) {
employeeObject[employee] = Number(totalTime)
}
else {
let temp = Number(employeeObject[employee]) + Number(totalTime)
employeeObject[employee] = temp
}
return true;
});
return employeeObject;
}
else {
return {}
}
}
catch (err) {
log.error("error@getEmployeeProjectData", err)
return {}
}
}
/**
* Function to get the labour cost from an employee record
* @param {*} employee
* @returns
*/
function labourCostSearch(employee) {
try {
let labourCostObject = {}
let employeeSearchObj = search.create({
type: "employee",
filters:
[
["internalid", "anyof", employee],
"AND",
["isinactive", "is", "F"],
"AND",
["releasedate", "isempty", ""],
"AND",
["laborcost", "isnotempty", ""]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "laborcost", label: "Labor Cost" })
]
});
let searchResultCount = employeeSearchObj.runPaged().count;
if (searchResultCount > 0) {
employeeSearchObj.run().each(function (result) {
let laborcost = result.getValue({
name: "laborcost", label: "Labor Cost"
})
let employeeId = result.getValue({
name: "internalid", label: "Internal ID"
})
labourCostObject[employeeId] = laborcost
return true;
});
return labourCostObject;
}
else {
return {}
}
}
catch (err) {
log.error("error@labourCostSearch", err)
return {}
}
}
/**
* Function to create an error data if any errors are occurred while creating the journal record
* @param {*} errorMessage
* @param {*} project
* @param {*} journalData
* @param {*} totalCostObj
*/
function createErrorFile(errorMessage, projects, journalData, totalCostObj) {
try {
let customLogErrorRecord = record.create({
type: "customrecord_jj_journal_errors",
isDynamic: true,
});
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_journl_project_name",
value: projects
});
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_journal_data",
value: journalData[0]
});
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_journal_errors",
value: errorMessage
});
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_total_cost",
value: totalCostObj
});
let recordId = customLogErrorRecord.save({
enableSourcing: false,
ignoreMandatoryFields: false
});
}
catch (err) {
log.error("error@createErrorFile", err)
}
}
/**
* Function to get the currency exchange rate based on base and source currency
* @param sourceCurrency
* @param baseCurrency
**/
function getExchangeRate(sourceCurrency, baseCurrency)
{
try
{
let exchangeRateValue ;
let currencyrateSearchObj = search.create({
type: "currencyrate",
filters:
[
["basecurrency","anyof",sourceCurrency],
"AND",
["transactioncurrency","anyof",baseCurrency]
],
columns:
[
search.createColumn({
name: "exchangerate",
summary: "MAX",
label: "Exchange Rate",
}).setWhenOrderedBy({ name : 'effectivedate', join :'currencyrate'})
]
});
let searchResultCount = currencyrateSearchObj.runPaged().count;
if(searchResultCount > 0)
{
currencyrateSearchObj.run().each(function (result)
{
exchangeRateValue = result.getValue({
name: "exchangerate",
summary: "MAX",
label: "Exchange Rate"
});
});
return exchangeRateValue ;
}
else
{
return false
}
}
catch (e)
{
log.debug("Error @ getExchangeRate", e);
return false
}
}
/**
* 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
*/
const getInputData = (inputContext) => {
try {
let currentDate = new Date();
let finalDate = processDate(currentDate)
let dateArray = finalDate.split("/");
let month = dateArray[1];
let day = dateArray[0];
let lastDay = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0)
log.debug("lastDay",lastDay)
let finalLastDay = processDate(lastDay);
log.debug("finalLastDay",finalLastDay)
let filter;
if (month == '2') {
return false
}
else if ((day == '1') && (month == '3')) {
filter = "lastmonth"
let data = getAllDateOnCurrentMonth(filter);
if((Object.keys(data).length) > 0)
{
return data
}
}
else {
filter = "thismonth"
let data = getAllDateOnCurrentMonth(filter);
if((Object.keys(data).length) > 0)
{
return data
}
}
}
catch (err) {
log.debug("error@getInputData", err)
}
}
/**
* 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 {
let result = JSON.parse(mapContext.value);
let employees = result.values['GROUP(employee)'].value
let projects = result.values['GROUP(customer)'].value
let time = result.values['SUM(formulanumeric)']
let employeeObj = {};
let employeeArray = [];
employeeObj.emp = employees
employeeObj.time = time
employeeArray.push(employeeObj)
mapContext.write({
key: projects,
value: employeeArray
});
}
catch (err) {
log.debug("error@MapContext", err)
}
}
/**
* 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) => {
let projects;
let journalData;
let totalCostObj = {}
let transactionCurrency = "1"
try {
let currentDate = new Date();
let finalDate = processDate(currentDate)
let dateArray = finalDate.split("/");
let day = dateArray[0];
let month = dateArray[1];
let year = dateArray[2];
let filter;
if ((day == "1") && (month == '3')) {
month = 2;
filter = "lastmonth"
}
else {
month = month
filter = "thismonth"
}
projects = reduceContext.key;
log.debug("Project in Reduce",projects)
let reduceValues = reduceContext.values;
let employeeArray = [];
if (reduceValues.length > 0) {
for (let i = 0; i < reduceValues.length; i++) {
let projectObject = {}
let dataValues = JSON.parse(reduceContext.values[i])
projectObject.employee = dataValues[0].emp
projectObject.time = dataValues[0].time
employeeArray.push(projectObject)
}
}
log.debug("employeeArrayData", employeeArray)
let projectDetails = searchProjectDetails(projects)
let employees = []
if (employeeArray.length > 0) {
for (let i = 0; i < employeeArray.length; i++) {
employees.push(employeeArray[i].employee)
}
}
journalData = payRollAllocationSearch(employees, month, year, projects)
log.debug("journalDataDetails", journalData)
let labourCost = labourCostSearch(employees);
log.debug("labourCostDetails", labourCost);
let baseCurrency = projectDetails.currency
let exchangeRateValue = getExchangeRate(baseCurrency, transactionCurrency);
log.debug("exchangeRateValue",exchangeRateValue)
let monthProjectDetails = getEmployeeProjectData(employees, filter)
log.debug("monthProjectDetails", monthProjectDetails)
if (employeeArray.length > 0) {
for (let i = 0; i < employeeArray.length; i++) {
let employeeId = employeeArray[i].employee;
let time = Number(employeeArray[i].time)
// if (labourCost[employeeId]) {
// let totalCost = Number((labourCost[employeeId]) * exchangeRateValue) * time;
// totalCostObj[employeeId] = totalCost;
// }
// else {
let monthlyRate;
if (journalData.length > 0) {
for (let j = 0; j < journalData.length; j++) {
if (journalData[j].employee === employeeId) {
monthlyRate = Number((journalData[j].monthlyRate) * exchangeRateValue)
}
}
}
let totalProjectTime = Number(monthProjectDetails[employeeId])
if (totalProjectTime && monthlyRate) {
let totalCost = (time / totalProjectTime) * monthlyRate
totalCostObj[employeeId] = totalCost;
}
// }//else
}
log.debug("totalCostObjData", totalCostObj)
}
let journalEntry = record.create({
type: record.Type.JOURNAL_ENTRY,
isDynamic: true,
});
journalEntry.setValue({
fieldId: 'subsidiary',
value: projectDetails.subsidiary
});
journalEntry.setValue({
fieldId: 'currency',
value: projectDetails.currency
});
journalEntry.setValue({
fieldId: 'memo',
value: 'Automated Journal'
});
let totalCost = 0;
let fixedTotalCost
for (let key in totalCostObj) {
totalCost = Number(totalCost) + Number(Number(totalCostObj[key]).toFixed(2))
}
if (journalData.length > 0) {
let employeeId
for (let i = 0; i < journalData.length; i++) {
employeeId = journalData[i].employee;
if (totalCostObj[employeeId]) {
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'account',
value: '3859',
line: i
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'debit',
value: Number(totalCostObj[employeeId]).toFixed(2),
line: i
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'custcol3',
value: 'Automated Journal',
line: i
});
log.debug("employeeId",employeeId)
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'entity',
value: employeeId,
line: i
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'class',
value: journalData[i].division,
line: i
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'department',
value: journalData[i].activityCode,
line: i
});
journalEntry.commitLine({
sublistId: 'line'
});
}
}
journalEntry.selectNewLine({
sublistId: 'line'
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'account',
value: '3859',
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'credit',
value: totalCost,
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'custcol3',
value: 'Automated Journal',
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'class',
value: '1182',
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'department',
value: '10324',
});
journalEntry.commitLine({
sublistId: 'line'
});
let journalId = journalEntry.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
log.debug("journalId", journalId)
}
}
catch (err) {
log.debug("error@reduce", err)
let errorMessage = err.name + " " + ":" + " " + err.message
createErrorFile(errorMessage, projects, journalData, totalCostObj)
}
}
return { getInputData, map, reduce }
});