Developed scripts in Production environment for the functionality developed to automatically attach the PDF files in the Bill Payment Record related to the Vendor Bill and Bill Credit. In the create and edit context of Bill Payment record the scripts will execute and files will be attached in the File section below the Communication subtab in the Bill Payment.
List of scripts developed for the auto population of files in Bill Payment record:
JJ UE attach file payment
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
/**********************************************************************************************************************************************
* RSPS-625 RSP Supply-USA-NS
*
* RSPS-625 : Automatically Adding Attachments from Bills and Bill Credits to related Payment records
*
*
***********************************************************************************************************************************************
*
* Author: Jobin and Jismi IT Services
*
* Date Created : 03-July-2024
*
* Description : This script is used to get bill payement record internal id and then triggers a map reduce script for further processing.
*
* REVISION HISTORY
*
* @version 1.0 RSPS-625 : 03-July-2024 : Created the initial build by JJ0223
*
* **********************************************************************************************************************************************/
define(['N/record', 'N/search', 'N/log', 'N/task'],
/**
* @param{record} record
* @param{search} search
* @param{log} log
* @param{task} task
*/
(record, search, log, task) => {
/**
* Defines the function definition that is executed after record is submitted.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.type - Trigger type; use values from the scriptContext.UserEventType enum
* @since 2015.2
*/
const afterSubmit = (scriptContext) => {
try {
log.debug("billPaymentId", scriptContext)
if (scriptContext.type !== scriptContext.UserEventType.CREATE && scriptContext.type !== scriptContext.UserEventType.EDIT&&scriptContext.type !== scriptContext.UserEventType.PAYBILLS) {
return;
}
let billPaymentRecord = scriptContext.newRecord;
let billPaymentId = billPaymentRecord.id;
log.debug("billPaymentId", billPaymentId)
let mrTaskId = createMapReduceTask(billPaymentId);
log.debug("mrTaskId", mrTaskId )
} catch (e) {
log.debug('Error Retrieving Applied Records', e);
}
}
/**
* Creates a Map/Reduce task to attach files to the bill payment record.
*
* @param {Array<Object>} appliedRecords - An array of objects containing the internal ID and type of each applied record.
* @param {string} billPaymentId - The internal ID of the bill payment record.
* @returns {string} The task ID of the submitted Map/Reduce task.
*/
const createMapReduceTask = (billPaymentId) => {
try {
let mrTask = task.create({ taskType: task.TaskType.MAP_REDUCE });
mrTask.scriptId = "customscript_jj_mr_attach_file_payment";
mrTask.deploymentId = "customdeploy_jj_mr_attach_file_payment";
mrTask.params = {
"custscript_jj_bill_payment_id": billPaymentId
};
return mrTask.submit();
} catch (e) {
log.debug('Error in createMapReduceTask', e);
return null;
}
};
return { afterSubmit }
});
JJ MR attach file payment
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/**********************************************************************************************************************************************
* RSPS-625 RSP Supply-USA-NS
*
* RSPS-625 : Automatically Adding Attachments from Bills and Bill Credits to related Payment records
*
*
***********************************************************************************************************************************************
*
* Author: Jobin and Jismi IT Services
*
* Date Created : 03-July-2024
*
* Description : This script is used to get the PDF file attachments in the bills and bill credits record applied in the Bill Payment record.
* Automatically attach those files in the files section below the Communication subtab in the Bill Payment record.
*
* REVISION HISTORY
*
* @version 1.0 RSPS-625 : 03-July-2024 : Created the initial build by JJ0223
*
* **********************************************************************************************************************************************/
define(['N/file', 'N/record', 'N/runtime', 'N/search', 'N/log', 'N/query'],
/**
* @param{file} file
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{log} log
* @param{query} query
*/
(file, record, runtime, search, log, query) => {
/**
* 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 billPaymentRecordId = runtime.getCurrentScript().getParameter({ name: 'custscript_jj_bill_payment_id' });
log.debug("bilPaymentId", billPaymentRecordId);
let billAppliedId = getAppliedRecords(billPaymentRecordId);
log.debug("billappliedId", billAppliedId);
let creditAppliedId = getBillPaymentCredits(billPaymentRecordId);
log.debug("creditAppliedId", creditAppliedId);
let a = [
JSON.stringify({
billAppliedId: billAppliedId,
creditAppliedId: creditAppliedId
})
];
log.debug("a", a)
return [
JSON.stringify({
billAppliedId: billAppliedId,
creditAppliedId: creditAppliedId
})
];
} catch (e) {
log.error('Error in getInputData', e.message);
return [];
}
}
/**
* 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 {
reduceContext.values.forEach(value => {
let inputData = JSON.parse(value);
log.debug("inputData", inputData);
if (inputData.billAppliedId) {
processAppliedIds(inputData.billAppliedId, 'VendBill');
} else {
log.debug("billAppliedId not found in inputData");
}
if (inputData.creditAppliedId) {
processAppliedIds(inputData.creditAppliedId, 'VendCred');
} else {
log.debug("creditAppliedId not found in inputData");
}
});
} catch (e) {
log.error('Error in reduce', e.message);
}
};
/**
* Retrieves the internal IDs of applied records from a given bill payment record.
*
* @param {number} billPaymentRecordId - The internal ID of the bill payment record.
* @returns {Array<number>} An array of internal IDs of the applied records.
*/
const getAppliedRecords = (billPaymentRecordId) => {
try {
// Create the saved search
let appliedRecordsSearch = search.create({
type: search.Type.TRANSACTION,
filters: [
['type', 'anyof', 'VendPymt'], // Vendor Payment
'AND',
['internalid', 'is', billPaymentRecordId],
'AND',
['mainline', 'is', 'F'],
'AND',
["appliedtotransaction", "noneof", "@NONE@"]
],
columns:
[
search.createColumn({ name: "appliedtotransaction", label: "Applied To Transaction" }),
search.createColumn({
name: "internalid",
join: "appliedToTransaction",
label: "Internal ID"
})
]
});
let appliedRecords = [];
// Run the search and process the results
let searchResultCount = appliedRecordsSearch.runPaged().count;
log.debug('Applied Records Search Result Count', searchResultCount);
appliedRecordsSearch.run().each(result => {
let billId = result.getValue({
name: "internalid",
join: "appliedToTransaction",
label: "Internal ID"
});
appliedRecords.push(parseInt(billId, 10));;
return true;
});
return appliedRecords;
} catch (e) {
log.error('Error in getAppliedRecords', e);
return [];
}
};
/**
* Retrieves the applied bill credit records from the bill payment record.
*
* @param {number} billPaymentId - The internal ID of the bill payment record.
* @returns {Array<number>} An array containing the internal IDs of the applied bill credit records.
*/
function getBillPaymentCredits(billPaymentId) {
let queryResults = query.runSuiteQL({
query: `
SELECT
Applied.nextdoc as billCreditId
FROM
AppliedCreditTransactionLineLink as Applied
WHERE
Applied.paymenttransaction = ${billPaymentId}
`
});
let results = queryResults.asMappedResults();
// Map the results to an array of billCreditId values
let billCreditIds = results.map(result => result.billcreditid);
return billCreditIds;
}
/**
* Processes applied IDs (either bill or credit) based on provided IDs and type.
* This function retrieves transaction details for each ID, checks the document name,
* retrieves file internal IDs, and attaches files to the corresponding bill payment record.
*
* @param {Array<number>} appliedIds - An array of internal IDs to process.
* @param {string} type - The record type, which can be either VendBill or VendCred.
*/
const processAppliedIds = (appliedIds, type) => {
appliedIds.forEach(id => {
let transactionDetails = getTransactionDetails(id, type);
transactionDetails.forEach(({ vendorName, documentName, referenceNumber, recType }) => {
log.debug("Vendor Name", vendorName);
log.debug("Reference Number", referenceNumber);
log.debug("Document Name", documentName);
log.debug("recType", recType);
if (referenceNumber) {
if (documentName.includes(referenceNumber)) {
let fileInternalIdArray = getFileInternalIds(vendorName, referenceNumber, recType);
let billPaymentRecordId = runtime.getCurrentScript().getParameter({ name: 'custscript_jj_bill_payment_id' });
attachFilesToRecord(fileInternalIdArray, billPaymentRecordId);
} else {
log.debug("Document name does not contain reference number, skipping attachment");
}
}
else {
log.debug("No Reference Number in the record");
}
});
});
};
/**
* Defines the function that retrieves transaction details for a given internal ID.
* This function creates a saved search to get the reference number and vendor name.
*
* @param {number} internalId - The internal ID of the transaction to retrieve details for.
* @param {string} type - The record type can be either VendBill or VendCred.
* @returns {Array} An array of search results containing the transaction details.
*/
const getTransactionDetails = (internalId, type) => {
try {
let typeFilter = type === 'VendBill' ? 'VendBill' : 'VendCred';
log.debug("typeFilter", typeFilter);
let vendorbillSearchObj = search.create({
type: search.Type.TRANSACTION,
filters: [
['type', 'anyof', typeFilter],
'AND',
['internalid', 'anyof', internalId],
'AND',
['mainline', 'is', 'T']
],
columns: [
'tranid',
'entity',
search.createColumn({
name: 'name',
join: 'file',
label: 'Name'
}),
search.createColumn({ name: "type", label: "Type" })
]
});
let billSearchResultCount = vendorbillSearchObj.runPaged().count;
log.debug("vendorcreditSearchObj result count", billSearchResultCount);
let vendorBillDetails = [];
vendorbillSearchObj.run().each(result => {
let vendorName = result.getText('entity');
let referenceNumber = result.getValue('tranid');
let documentName = result.getValue({
name: 'name',
join: 'file',
label: 'Name'
});
let recType = result.getValue("type");
vendorBillDetails.push({
vendorName: vendorName,
documentName: documentName,
referenceNumber: referenceNumber,
recType: recType
});
return true;
});
return vendorBillDetails;
} catch (e) {
log.error('Error in getTransactionDetails', e.message);
return [];
}
};
/**
* Defines the function that retrieves file internal IDs for a given vendor name and reference number.
* This function creates a saved search to get the file internal IDs.
*
* @param {string} vendorName - The name of the vendor.
* @param {string} referenceNumber - The reference number to search for in file names.
* @param {string} recType - The record type can be either VendBill or VendCred.
* @returns {Array<string>} An array of file internal IDs.
*/
const getFileInternalIds = (vendorName, referenceNumber, recType) => {
try {
let folderName = vendorName + " " + (recType === 'VendBill' ? "Invoices" : "Credit Memos");
let fileInternalIdArray = [];
let folderSearch = search.create({
type: search.Type.FOLDER,
filters: [
['name', 'is', folderName],
'AND',
['file.name', 'contains', referenceNumber]
],
columns: [
search.createColumn({
name: 'name',
join: 'file',
label: 'Name'
}),
search.createColumn({
name: 'internalid',
join: 'file',
label: 'Internal ID'
})
]
});
folderSearch.run().each(result => {
let fileName = result.getValue({
name: 'name',
join: 'file',
label: 'Name'
});
let fileInternalId = result.getValue({
name: 'internalid',
join: 'file',
label: 'Internal ID'
});
fileInternalIdArray.push(fileInternalId);
return true;
});
return fileInternalIdArray;
} catch (e) {
log.error('Error in getFileInternalIdsBill', e.message);
}
};
/**
* Defines the function that attaches files to a given bill payment record.
* This function iterates over the array of file internal IDs and attaches each file to the bill payment record.
*
* @param {Array<string>} fileInternalIdArray - An array of file internal IDs to attach.
* @param {string} billPaymentRecordId - The internal ID of the bill payment record to attach files to.
*/
const attachFilesToRecord = (fileInternalIdArray, billPaymentRecordId) => {
try {
fileInternalIdArray.forEach(fileId => {
record.attach({
record: { type: 'file', id: fileId },
to: { type: 'vendorpayment', id: billPaymentRecordId }
});
});
} catch (e) {
log.error('Error in attachFilesToRecord', e.message);
}
};
return { getInputData, reduce }
});