Jira Code: TM – 190
To create a vendor cost report with custom columns, these columns are not available in a standard report. So we have to create scripts to complete the requirement.
Suitelet
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/*******************************************************************************
*
* Author : Jobin and jismi created on:13/06/2018
* Created an API for custom transaction
* request JSON scriptContext.request.body JSON with credit transaction details
* response {isSucess:BOOLEAN,message:"STRING/OBJECT"}
*
* IF ERROR {"isSucess":false,"message":"ERROR_MESSAGE"}
* IF SUCESS {"isSucess":true,"message":{"creditId":RECORDID(integer)}}
*
* REVISION HISTORY :
*
* Revision 1.0 $ 21/07/2018 aj : updated
* 1.1 $ 25/04/2019 aj : updated to add total transaction type in Job record.
******************************************************************************/
define(['N/record','N/search'], function (record,search) {
function onRequest(scriptContext) {
var response={}
try {
var JSONobj=JSON.parse(scriptContext.request.body);
log.debug({title:"JSON",details:scriptContext.request.body});
try{
var creditPayment = record.create({
type: "customtransaction_tm77_cust_transaction",
isDynamic: true
});
creditPayment.setText({ fieldId: 'trandate', text: JSONobj.date, ignoreFieldChange: true});
//creditPayment.setValue({fieldId: 'trantype', value: JSONobj.trantype, ignoreFieldChange: true});
creditPayment.setValue({fieldId: 'custbody_tm_tranid', value: JSONobj.transid, ignoreFieldChange: true});
creditPayment.setValue({fieldId: 'custbody_tm_vendor', value: JSONobj.vendorid, ignoreFieldChange: true});
//creditPayment.setValue({fieldId: 'total', value: JSONobj.accountid, ignoreFieldChange: true});
creditPayment.setValue({fieldId: 'memo', value: JSONobj.memo, ignoreFieldChange: true});
//creditPayment.setValue({fieldId: 'custbody_tm_amount', value: JSONobj.usertotal, ignoreFieldChange: true});
var totalAmount = 0.0;
var expenseArray = JSONobj.expense;
for(var i=0;i<expenseArray.length;i++)
{
totalAmount = parseFloat(totalAmount) + parseFloat(expenseArray[i].amount);
}
creditPayment.setValue({fieldId: 'custbody_tm_amount', value: totalAmount, ignoreFieldChange: true});
creditPayment.setValue({fieldId: 'total', value: totalAmount, ignoreFieldChange: false});
if(JSONobj.trantype=='CardChrg')
{
creditPayment.setValue({fieldId: 'custbody_tm_charge', value: true, ignoreFieldChange: true});
}
else{
creditPayment.setValue({fieldId: 'custbody_tm_iscredit', value: true, ignoreFieldChange: true});
}
var creditId = creditPayment.save({
enableSourcing: true,
ignoreMandatoryFields: false
});
}catch(e)
{
log.debug({
title : "ERROR@ Create Cust Transaction type",
details : e.message
});
}
try{
var expenseArray = JSONobj.expense
for (var i = 0; i < expenseArray.length; i++) {
// to create new record
var custRecord = record.create({
type : "customrecord_tm77_cust_tran_record",
isDynamic : true
});
// to enter the fields
custRecord.setValue({
fieldId : 'custrecord_tm_record_id',
value : creditId,
ignoreFieldChange : true
});
custRecord.setValue({
fieldId : 'custrecord_tm_amount',
value : expenseArray[i].amount,//Amount to be set in Job
ignoreFieldChange : true
});
custRecord.setValue({
fieldId : 'custrecord_tm_account',
value : expenseArray[i].accountid,// Id of job
ignoreFieldChange : true
});
custRecord.setValue({
fieldId : 'custrecord_tm_customer',
value : expenseArray[i].customerid,
ignoreFieldChange : true
});
var custRe= custRecord.save({
enableSourcing : true,
ignoreMandatoryFields : false
});
try{
/*AJ Mod on 25.04.2019 to get total transaction amount*/
// to get the amount in job
var currentTotalAmountInJob = getCurrentAmountInJob(expenseArray[i].customerid);
var totalAmount = parseFloat(checkIf(currentTotalAmountInJob))+parseFloat(expenseArray[i].amount);
var jobRec = record.submitFields({
type:'job',
id: expenseArray[i].customerid,
values: {
custentity_external_cart:totalAmount,
custentity_transaction_id:creditId,
custentity_vendor:JSONobj.vendorid,
// to set value to custom field 'transaction amount'
custentity_transaction_amount:expenseArray[i].amount,
// to set the total amount
custentity_total_trn_amt:totalAmount
}
});
log.debug('jobRec',jobRec)
}catch(e)
{
log.debug({
title : "ERROR@ Job Setting",
details : e.message
});
}
}
}catch(e)
{
log.debug({
title : "ERROR@ Create Cust Record type",
details : e.message
});
}
try{
// to set the fields in Job
}catch(e)
{
log.debug({
title : "ERROR@ setting Job fields",
details : e.message
});
}
response={
isSucess:true,
message :"",
creditId:creditId
};
log.debug("CREATED",creditId);
scriptContext.response.write(JSON.stringify(response));
} catch (err) {
response={isSucess:false,message:err.message,creditId:0};
log.debug({title:"ERROR@onRequest",details:err});
scriptContext.response.write(JSON.stringify(response));
}
}
/******
* Check if function
*/
function checkIf(parameter)
{
try{
if(parameter!=''&& parameter!=undefined && parameter!='undefined' && parameter!= null && parameter!= 'null' && parameter!=" ")
{
return parameter;
}
else
return 0;
}catch(e)
{
log.debug("Err@Check if",e)
log.error("Err@Check if",e)
}
}
/**********************************************
* Function to get the total transaction amount
* @created by aj 25.04.2019
**********************************************/
function getCurrentAmountInJob(jobId){
try{
var totalAmount;
if(jobId)
{
// create a search
var jobSearchObj = search.create({
type: "job",
filters:
[
["internalidnumber","equalto",jobId]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "custentity_total_trn_amt", label: "Total transaction amount"})
]
});
var searchResultCount = jobSearchObj.runPaged().count;
//to get the result
var searchResult = jobSearchObj.run().getRange({
start:0,
end:1
});
if(searchResultCount>0)
{
totalAmount=searchResult[0].getValue({
name: "custentity_total_trn_amt"
});
}
}
return totalAmount;
}catch(e)
{
log.debug("Err@ getCurrentAmountInJob ",e);
log.error("Err@ getCurrentAmountInJob FN ",e);
}
}
return {
onRequest: onRequest
}
});Schedule scripts
/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CLIENTNAME:TAKE 5 MEDIA
*
*************************************************************************
* Date : 15/03/2019
* Date created : 15/03/2019
*
* REVISION HISTORY
*
* Revision 1.0 ${15/03/2019} aj : created
* 1.1 ${10/04/2019} aj : modified to slve repeating scheduled script
* 1.2 ${26/04/2019} aj : modified to add total vendor cost
******************************************************************************/
define(['N/record', 'N/search', 'N/ui/serverWidget','N/runtime'],
function(record, search, serverWidget,runtime) {
/**
* Definition of the Scheduled script trigger point.
*
* @param {Object} scriptContext
* @param {string} scriptContext.type - The context in which the script is executed. It is one of the values from the scriptContext.InvocationType enum.
* @Since 2015.2
*/
function execute(scriptContext) {
try{
var recId = runtime.getCurrentScript().getParameter("custscript1");
var billRecord=record.load({
type:'vendorbill',
id:recId
});
// to get the vendor name
var vendorNameID = billRecord.getValue({
fieldId:'entity'
});
var vendorName = getVendorNameFromID(vendorNameID);
log.debug("vendorName",vendorName)
// to get the lines
var lineNum = billRecord.getLineCount({
sublistId:'expense'
});
// var SO#='';
if(lineNum>0)
{
for(var i=0;i<lineNum;i++)
{
// to get the jobId & Amount
var Amount= billRecord.getSublistValue({
sublistId:'expense',
fieldId:'amount',
line:i
});
//customer
var job= billRecord.getSublistValue({
sublistId:'expense',
fieldId:'customer',
line:i
});
// to get the SO# of job
if(job!=''&& job!=undefined && job!='undefined' && job!= null && job!= 'null' && job!=" ")
{
var jobSearchObj = search.create({
type: "job",
filters:
[
["internalidnumber","equalto",job]
],
columns:
[
search.createColumn({name: "custentity_io_number", label: "IO#"}),
// added to get the existing total cost
search.createColumn({name: "custentity_total_trn_amt", label: "Total transaction amount"}),
]
});
var searchResultCount = jobSearchObj.runPaged().count;
var searchResult = jobSearchObj.run().getRange({
start:0,
end:1
});
// to get the SO#
if(searchResultCount>0)
{
var reltdSO = searchResult[0].getValue({
name:'custentity_io_number'
});
// search for Invoice
log.debug("reltdSO",reltdSO);
// to get the InvoiceId
var invoiceId = getInvoiceDetails(reltdSO);
// to get the amount in JOB
var totalAmountInJob = searchResult[0].getValue({
name:'custentity_total_trn_amt'
});
var totalTransactionAmountInJob = parseFloat(checkIf(totalAmountInJob))+parseFloat(checkIf(Amount));
// to submit JOB record
log.debug("totalTransactionAmountInJob",totalTransactionAmountInJob);
try{
var jobID = record.submitFields({
type: 'job',
id: job,
values: {
'custentity_jj_bill_vendor':vendorName,
'custentity_total_trn_amt':totalTransactionAmountInJob
}
});
}catch(e)
{
log.debug("Err @ job submit",e);
}
log.debug("jobID",jobID);
// to submit the Invoice Amount in Invoice
for(var j=0;j<invoiceId.length;j++)
{
var JobExp=0;
var oldAmount=invoiceId[j].amount;
if(oldAmount==null || oldAmount==undefined || oldAmount==" "||oldAmount==""||oldAmount==''||oldAmount==' ')
oldAmount=0;
JobExp = parseFloat(oldAmount)+parseFloat(Amount);
log.debug("JobExp"+JobExp,"invoiceId[j].invceId"+invoiceId[j].invceId);
var id = record.submitFields({
type: record.Type.INVOICE,
id: invoiceId[j].invceId,
values: {
'custbody_jj_job_expense': JobExp
}
});
}
}
if(i==lineNum-1)
{
/*billRecord.save({ignoreMandatory})*/
var idBill = record.submitFields({
type: record.Type.VENDOR_BILL,
id: recId,
values: {
'custbody_queued': 'false'
}
});
}
}
}
}
}catch(e)
{
log.debug("Err@ FN execute",e);
log.error("Err@ execute FN ",e);
}
}
function getVendorNameFromID(vendorNameID){
try{
var vendorSearchObj = search.create({
type: "vendor",
filters:
[
["internalidnumber","equalto",vendorNameID]
],
columns:
[
search.createColumn({
name: "entityid",
sort: search.Sort.ASC,
label: "Name"
})
]
});
var searchResultCount = vendorSearchObj.runPaged().count;
var vendor;
if(searchResultCount>0)
{
var searchResult = vendorSearchObj.run().getRange({
start:0,
end:1
});
vendor = searchResult[0].getValue({
name: "entityid",
sort: search.Sort.ASC,
label: "Name"
});
}
return vendor;
}catch(e)
{
log.debug("Err@ get vendor name",e)
log.error("Err@ get vendor name",e)
}
}
/******
* Check if function
*/
function checkIf(parameter)
{
try{
if(parameter!=''&& parameter!=undefined && parameter!='undefined' && parameter!= null && parameter!= 'null' && parameter!=" ")
{
return parameter;
}
else
return 0;
}catch(e)
{
log.debug("Err@Check if",e)
log.error("Err@Check if",e)
}
}
/***
* To get the Vendor Bills Id
*/
function findVendorBills()
{
try{
var vendorbillSearchObj = search.create({
type: "vendorbill",
filters:
[
["type","anyof","VendBill"],
"AND",
["custbody_queued","is","true"],
"AND",
["mainline","is","T"]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"})
]
});
var searchResultCount = vendorbillSearchObj.runPaged().count;
log.debug("vendorbillSearchObj result count",searchResultCount);
// to get the results..
var searchResult = vendorbillSearchObj.run().getRange({
start:0,
end:1000
});
var vendorBillArray=[];
for(var i=0;i<searchResult.length;i++)
{
var billID = searchResult[i].getValue({
name:'internalid'
});
vendorBillArray.push(billID);
}
log.debug("vendorBillArray",vendorBillArray);
return vendorBillArray;
}catch(e)
{
log.debug("Err@ FN findVendorBills",e);
log.error("Err@ findVendorBills FN ",e);
//console.log("Err@ FN =",e);
}
}
/***
* to get the invoice Id's & its job expenses
*
*/
function getInvoiceDetails(reltdSO)
{
try
{
var returnArray =[];
var invoiceSearchObj = search.create({
type: "invoice",
filters:
[
["type","anyof","CustInvc"],
"AND",
["createdfrom","anyof",reltdSO],
"AND",
["mainline","is","T"]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "tranid", label: "Document Number"}),
search.createColumn({name: "custbody_jj_job_expense", label: "Job Expenses"})
]
});
var searchResultCount = invoiceSearchObj.runPaged().count;
log.debug("invoiceSearchObj result count",searchResultCount);
var searchResult = invoiceSearchObj.run().getRange({
start:0,
end:1000
});
for(var i=0;i<searchResultCount;i++)
{
var InvceObj={};
InvceObj.invceId = searchResult[i].getValue({
name: "internalid"
});
InvceObj.amount = searchResult[i].getValue({
name: "custbody_jj_job_expense"
});
returnArray.push(InvceObj);
}
return returnArray;
}catch(e)
{
log.debug("Err@ FN getInvoiceDetails",e.message);
}
}
return {
execute: execute
};
});
Mass update script
/**
* @NApiVersion 2.x
* @NScriptType MassUpdateScript
* @NModuleScope SameAccount
*/
define(['N/record', 'N/search'],
/**
* @param {record} record
* @param {search} search
*/
function(record, search) {
/**
* Definition of Mass Update trigger point.
*
* @param {Object} params
* @param {string} params.type - Record type of the record being processed by the mass update
* @param {number} params.id - ID of the record being processed by the mass update
*
* @since 2016.1
*/
function each(params) {
try{
// to get the internal ID of Job
var jobID = params.id;
// to get the transaction total
var jobSearchObj = search.create({
type: "job",
filters:
[
["internalidnumber","equalto",jobID]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "custentity_total_trn_amt", label: "Total transaction amount"}),
search.createColumn({name: "custentity_transaction_amount", label: "TRANSACTION AMOUNT"})
]
});
var searchResultCount = jobSearchObj.runPaged().count;
var searchResult = jobSearchObj.run().getRange({
start:0,
end:1
});
var transactionAmount = searchResult[0].getValue({
name:'custentity_transaction_amount'
});
// to get the bill amount
var totalBillAmount = getTotalBillAmount(jobID);
var totalTransactionAmount = parseFloat(checkIf(totalBillAmount))+parseFloat(checkIf(transactionAmount));
log.debug("totalTransactionAmount of JOB="+jobID,totalTransactionAmount)
var jobID = record.submitFields({
type: 'job',
id: jobID,
values: {
'custentity_total_trn_amt':totalTransactionAmount
}
});
}catch(err){
log.debug("err @ JobID="+jobID,err)
}
}
function getTotalBillAmount(jobID)
{
try{
var vendorbillSearchObj = search.create({
type: "vendorbill",
filters:
[
["type","anyof","VendBill"],
"AND",
["customer.internalidnumber","equalto",jobID]
],
columns:
[
search.createColumn({name: "mainline", label: "*"}),
search.createColumn({name: "custbodyid_job", label: "Job"}),
search.createColumn({
name: "name",
join: "expenseCategory",
label: "Name"
}),
search.createColumn({
name: "category",
join: "expenseDetail",
label: "Category"
}),
search.createColumn({
name: "amount",
join: "expenseDetail",
label: "Amount"
}),
search.createColumn({name: "amount", label: "Amount"})
]
});
var searchResultCount = vendorbillSearchObj.runPaged().count;
var transactionAmount=0,totalAmount =0;
if(searchResultCount>0)
{
var searchResult = vendorbillSearchObj.run().getRange({
start:0,
end:1000
});
for(var i=0;i<searchResult.length;i++)
{
transactionAmount =searchResult[i].getValue({
name: "amount"
});
totalAmount=parseFloat(totalAmount)+parseFloat(checkIf(transactionAmount));
}
}
return totalAmount;
}catch(e)
{
log.debug("err@get amount",e)
}
}
/******
* Check if function
*/
function checkIf(parameter)
{
try{
if(parameter!=''&& parameter!=undefined && parameter!='undefined' && parameter!= null && parameter!= 'null' && parameter!=" ")
{
return parameter;
}
else
return 0;
}catch(e)
{
log.debug("Err@Check if",e)
log.error("Err@Check if",e)
}
}
return {
each: each
};
});