Requirement:
The automation is used to send the spiff reports via email. This automation script will run at the end of each month and send the spiff data to the appropriate spiff person in CSV format.
Solution:
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/************************************************************************************************
CDU - 762 Automate Spiff Report Email
*********************************************************************************************
*
* Author: Jobin & Jismi IT Services LLP
*
* Date Created : 01/February/2022
*
* Description : Send spiff report email to spiff person on 28th of every month
*
***********************************************************************************************/
define(['N/email', 'N/file', 'N/record', 'N/runtime', 'N/search'],
/**
* @param{email} email
* @param{file} file
* @param{record} record
* @param{runtime} runtime
* @param{search} search
*/
(email, file, record, runtime, search) => {
/**
* Defines the function to load invoice search based on spiff person and sales order
* @param {spiff} spiff persons internal id
* @param {salesrep} sales reps internal id
* @param {spiffAmnt} total spiff amount
* @return {Array} filteredArray
*/
function invoiceSearchUsingSpiff(spiff, salesrep, spiffAmnt){
try{
var titleArray = ["SPIFF VENDOR", "INVOICE DATE", "INVOICE NUMBER", "SALES ORDER #", "PO #", "SPIFF AMOUNT OF ITEM", "INVOICE AMOUNT", "ITEM"];
var csvFileData = titleArray.toString() + '\r\n';
var invoiceSearchObj = search.create({
type: "invoice",
filters:
[
["type","anyof","CustInvc"],
"AND",
["status","anyof","CustInvc:B"],
"AND",
["totalamount","notequalto","0.00"],
"AND",
["class","noneof","@NONE@"],
"AND",
["formulanumeric: case when ({closedate}-{trandate}<47) then( case when ( {item} is not null) AND ({class}='Desking Products : One Suite Legs' OR {class}='Desking Products : One Suite Espresso' OR {class}='Desking Products : One Suite Blanc de Gris' OR {class}='Desking Products : One Suite Blanco' OR {class}='Desking Products : One Suite Grigio' OR {class}='Desking Products : One Suite Miele' OR {class}='Desking Products : One Suite Noce' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Glass' OR {class}='Desking Products : Potenza Blanco' OR {class}='Desking Products : Potenza Cherry' OR {class}='Desking Products : Potenza Espresso' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Potenza Miele' OR {class}='Desking Products : Potenza Noce' OR {class}='Desking Products : Sereno' OR {class}='Desking Products : Sling' OR {class}='Desking Products : Training / Benching' OR {class}='Desking Products : Potenza Blanc de Gris') then {amount}*0.05 else case when ({item} is not null) AND ({class}='Seating Products : Guest Chair' OR {class}='Seating Products : Training / Stacking') then {quantity}*5 else case when ({item}is not null) AND ({class}='Seating Products : Swivel Chair') then {quantity}*10 else 0 end end end) else 0 end","greaterthan","0"],
"AND",
["cogs","is","F"],
"AND",
["closedate","within","1/1/2022 12:00 am"],
"AND",
["custbody_jj_sent_spiff_email","is","F"],
"AND",
["salesrep.internalid","anyof",salesrep],
"AND",
["custbody_corpdesign_spiff.internalid","anyof",spiff]
],
columns:
[
search.createColumn({
name: "custbody_corpdesign_spiff",
sort: search.Sort.ASC,
label: "Spiff Vendor"
}),
search.createColumn('internalid'),
search.createColumn({name: "trandate", label: "Invoice Date"}),
search.createColumn({name: "closedate", label: "Paid in Full Date"}),
search.createColumn({name: "tranid", label: "Invoice Number"}),
search.createColumn({name: "createdfrom", label: "Sales Order #"}),
search.createColumn({name: "otherrefnum", label: "PO #"}),
search.createColumn({
name: "formulanumeric",
formula: "case when ({closedate}-{trandate}<47) then( case when ( {item} is not null) AND ({class}='Desking Products : One Suite Legs' OR {class}='Desking Products : One Suite Espresso' OR {class}='Desking Products : One Suite Blanc de Gris' OR {class}='Desking Products : One Suite Blanco' OR {class}='Desking Products : One Suite Grigio' OR {class}='Desking Products : One Suite Miele' OR {class}='Desking Products : One Suite Noce' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Glass' OR {class}='Desking Products : Potenza Blanco' OR {class}='Desking Products : Potenza Cherry' OR {class}='Desking Products : Potenza Espresso' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Potenza Miele' OR {class}='Desking Products : Potenza Noce' OR {class}='Desking Products : Sereno' OR {class}='Desking Products : Sling' OR {class}='Desking Products : Training / Benching' OR {class}='Desking Products : Potenza Blanc de Gris') then {amount}*0.05 else case when ({item} is not null) AND ({class}='Seating Products : Guest Chair' OR {class}='Seating Products : Training / Stacking') then {quantity}*5 else case when ({item}is not null) AND ({class}='Seating Products : Swivel Chair') then {quantity}*10 else 0 end end end) else 0 end",
label: "Spiff amount of item"
}),
search.createColumn({name: "amount", label: "Invoice Amount"}),
search.createColumn({name: "item", label: "Item"})
]
});
var spiffPerson, date, invNumber, salesOrder, po, spiffAmntItem, invAmnt, item, internalid;
var invoiceArr = [];
var searchResultCount = invoiceSearchObj.runPaged().count;
log.debug("invoiceSearchObj result count",searchResultCount);
if (searchResultCount>0) {
invoiceSearchObj.run().each(function (result) {
//push invoice internal id to an array
internalid = result.getValue('internalid');
invoiceArr.push(internalid);
spiffPerson = result.getText({
name: "custbody_corpdesign_spiff",
sort: search.Sort.ASC,
label: "Spiff Vendor"
});
date = result.getValue({name: "trandate", label: "Invoice Date"});
invNumber = result.getValue({name: "tranid", label: "Invoice Number"});
salesOrder = result.getValue({name: "createdfrom", label: "Sales Order #"});
po = result.getValue({name: "otherrefnum", label: "PO #"});
spiffAmntItem = result.getValue({
name: "formulanumeric",
formula: "case when ({closedate}-{trandate}<47) then( case when ( {item} is not null) AND ({class}='Desking Products : One Suite Legs' OR {class}='Desking Products : One Suite Espresso' OR {class}='Desking Products : One Suite Blanc de Gris' OR {class}='Desking Products : One Suite Blanco' OR {class}='Desking Products : One Suite Grigio' OR {class}='Desking Products : One Suite Miele' OR {class}='Desking Products : One Suite Noce' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Glass' OR {class}='Desking Products : Potenza Blanco' OR {class}='Desking Products : Potenza Cherry' OR {class}='Desking Products : Potenza Espresso' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Potenza Miele' OR {class}='Desking Products : Potenza Noce' OR {class}='Desking Products : Sereno' OR {class}='Desking Products : Sling' OR {class}='Desking Products : Training / Benching' OR {class}='Desking Products : Potenza Blanc de Gris') then {amount}*0.05 else case when ({item} is not null) AND ({class}='Seating Products : Guest Chair' OR {class}='Seating Products : Training / Stacking') then {quantity}*5 else case when ({item}is not null) AND ({class}='Seating Products : Swivel Chair') then {quantity}*10 else 0 end end end) else 0 end",
label: "Spiff amount of item"
});
invAmnt = result.getValue({name: "amount", label: "Invoice Amount"})
item = result.getText({name: "item", label: "Item"})
//append to csv file
csvFileData += spiffPerson + ',' + date + ',' + invNumber + ',' + salesOrder + ',' + po + ',' + spiffAmntItem + ',' + invAmnt + ',' + item + ',';
csvFileData += '\r\n';
return true;
});
log.debug("csv file data", csvFileData)
//filename
var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth() + 1).padStart(2, '0');
var yyyy = today.getFullYear();
today = mm + '-' + dd + '-' + yyyy;
var fileObj = file.create({
name: 'Spiff report:' + today + '.csv',
fileType: file.Type.CSV,
contents: csvFileData,
encoding: file.Encoding.UTF8,
});
var vendorEmail = search.lookupFields({
type: search.Type.VENDOR,
id: spiff,
columns: ['email']
});
var emailSent = 0;
if (vendorEmail.email) {
//send email
email.send({
author: 2256,
recipients: spiff,
subject: 'Monthly Spiff Report',
body: 'Hi,' +'\n'+ 'This email is to notify the spiff report of this month.' + '\n' + 'This month, you earned a spiff of ' + spiffAmnt + ' dollars.' + '\n' + 'Please find the attached file' + '\n' + 'Thank You.',
attachments: [fileObj]
});
emailSent++;
}
if (emailSent) {
//keep only unique id's in invoiceArr array
log.debug("invoiceArr", invoiceArr)
invoiceArr = invoiceArr.filter(function (item, index, inputArray) {
return inputArray.indexOf(item) == index;
});
log.debug("unique array", invoiceArr)
//load each invoice and check its sent spiff report email check box
for (let i = 0; i < invoiceArr.length; i++) {
record.submitFields({
type: 'invoice',
id: invoiceArr[i],
values: {
'custbody_jj_sent_spiff_email': true
}
});
}
}
}
}catch (e) {
log.debug("Error@invoiceSearchUsingSpiff", e)
}
}
const getInputData = (inputContext) => {
try {
//load the search created for spiff report automation(Automate Spiff Report Email, id: 633)
var returnArray = [];
var automateSpiffReportSearch = search.load({id: 'customsearch_jj_ss_spiff_amt_cdu_474_4'})
var searchcount = automateSpiffReportSearch.runPaged().count;
log.debug("count", searchcount)
automateSpiffReportSearch.run().each(function(result) {
var dataObj = {};
dataObj.spiffPerson = result.getValue({
name: "custbody_corpdesign_spiff",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Spiff Person"
});
dataObj.spiffAmount = result.getValue({
name: "formulanumeric",
summary: "SUM",
formula: "case when ({closedate}-{trandate}<47) then( case when ( {item} is not null) AND ({class}='Desking Products : One Suite Legs' OR {class}='Desking Products : One Suite Espresso' OR {class}='Desking Products : One Suite Blanc de Gris' OR {class}='Desking Products : One Suite Blanco' OR {class}='Desking Products : One Suite Grigio' OR {class}='Desking Products : One Suite Miele' OR {class}='Desking Products : One Suite Noce' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Glass' OR {class}='Desking Products : Potenza Blanco' OR {class}='Desking Products : Potenza Cherry' OR {class}='Desking Products : Potenza Espresso' OR {class}='Desking Products : Potenza Grigio' OR {class}='Desking Products : Potenza Miele' OR {class}='Desking Products : Potenza Noce' OR {class}='Desking Products : Sereno' OR {class}='Desking Products : Sling' OR {class}='Desking Products : Training / Benching' OR {class}='Desking Products : Potenza Blanc de Gris') then {amount}*0.05 else case when ({item} is not null) AND ({class}='Seating Products : Guest Chair' OR {class}='Seating Products : Training / Stacking') then {quantity}*5 else case when ({item}is not null) AND ({class}='Seating Products : Swivel Chair') then {quantity}*10 else 0 end end end) else 0 end",
label: "Spiff amount"
});
dataObj.salesRep = result.getValue({
name: "salesrep",
summary: "GROUP",
label: "Sales Rep"
});
dataObj.sendSpiffReport = result.getValue({
name: "custentity_jj_send_spiff_report",
join: "CUSTBODY_CORPDESIGN_SPIFF",
summary: "GROUP",
label: "Send spiff report"
});
if (dataObj.spiffAmount >= 100 && dataObj.sendSpiffReport == true)
returnArray.push(dataObj);
return true;
});
log.debug(returnArray)
return returnArray;
}catch (e) {
log.debug("Error@getInputData", e)
return [];
}
}
const reduce = (reduceContext) => {
try {
// log.debug("reduceContext", reduceContext)
if (reduceContext.values.length >0)
var dataObj = JSON.parse(reduceContext.values[0]);
else
return;
log.debug("dataObj", dataObj);
//create the invoice search
var loadInvoiceSearch = invoiceSearchUsingSpiff(dataObj.spiffPerson, dataObj.salesRep, dataObj.spiffAmount);
}catch (e) {
log.debug("Error@reduce", e)
}
}
const summarize = (summaryContext) => {
}
return {getInputData, reduce, summarize}
});