Upon creation of Bill/Bill credit records containing expense/item lines with specific tax codes, an automated journal entry needs to be generated for the notional tax amounts related to specific tax Codes. This entry will credit the purchases account and debit the expense account to the liability account. Additionally, we will need to incorporate a checkbox within the tax codes. When checked, these tax codes will be considered in the automation process.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/record', 'N/search'],
/**
* @param{record} record
* @param{search} search
*/
(record, search) => {
/**
* 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 context.UserEventType enum
* @since 2015.2
*/
const beforeSubmit = (scriptContext) => {
const newRecord = scriptContext.newRecord;
if (scriptContext.type !== scriptContext.UserEventType.CREATE && scriptContext.type !== scriptContext.UserEventType.EDIT) return;
const status = newRecord.getValue({ fieldId: 'approvalstatus' });
if (status != 2) return; // checking condition for Approved Status
const lineCount = newRecord.getLineCount({ sublistId: 'item' });
const billId = newRecord.id;
const billMemo = newRecord.getValue({ fieldId: 'memo' });
const tranid = newRecord.getValue({ fieldId: 'tranid' });
const tranNumber = newRecord.getValue({ fieldId: 'transactionnumber' });
const subsidiary = newRecord.getValue({ fieldId: 'subsidiary' });
const journalEntryData = [];
const reverseJEData = [];
for (let i = 0; i < lineCount; i++) {
const taxCode = newRecord.getSublistValue({ sublistId: 'item', fieldId: 'taxcode', line: i });
const taxLookUp = search.lookupFields({
type: 'salestaxitem',
id: taxCode,
columns: ['custrecord_jj_vat_rev_liab_ippf2302']
});
const isJEReversible = taxLookUp.custrecord_jj_vat_rev_liab_ippf2302;
const jeCreated = newRecord.getSublistValue({ sublistId: 'item', fieldId: 'custcol_jj_created_tax_rev_je', line: i });
const entryData = {
account: newRecord.getSublistValue({ sublistId: 'item', fieldId: 'custcol_itemaccount_cc', line: i }),
amount: newRecord.getSublistValue({ sublistId: 'item', fieldId: 'amount', line: i }),
pjt_activity: newRecord.getSublistValue({ sublistId: 'item', fieldId: 'custcol_jj_pro_acti', line: i }),
pjt_code: newRecord.getSublistValue({ sublistId: 'item', fieldId: 'custcol_cseg13', line: i }),
act_code: newRecord.getSublistValue({ sublistId: 'item', fieldId: 'department', line: i }),
div_unit_code: newRecord.getSublistValue({ sublistId: 'item', fieldId: 'class', line: i })
};
if (isJEReversible && !jeCreated) {
journalEntryData.push(entryData);
newRecord.setSublistValue({ sublistId: 'item', fieldId: 'custcol_jj_created_tax_rev_je', line: i, value: true }); // Checking checkbox if journal created
} else if (!isJEReversible && jeCreated) {
reverseJEData.push(entryData);
newRecord.setSublistValue({ sublistId: 'item', fieldId: 'custcol_jj_created_tax_rev_je', line: i, value: false }); // Unchecking checkbox if journal created
}
}
// Create the Journal
const processJournalEntry = (data, isReverse = false) => {
const journalEntryRecord = record.create({
type: record.Type.JOURNAL_ENTRY,
isDynamic: true
});
journalEntryRecord.setValue({ fieldId: 'subsidiary', value: subsidiary, });
journalEntryRecord.setValue({ fieldId: 'memo', value: tranid, ignoreFieldChange: true });
journalEntryRecord.setValue({ fieldId: 'custbody_jj_bill_reference', value: billId, ignoreFieldChange: true });
journalEntryRecord.setValue({ fieldId: 'custbody_jj_janice_journal_worflw', value: 2, ignoreFieldChange: true });
journalEntryRecord.setValue({ fieldId: 'approvalstatus', value: 2, ignoreFieldChange: true });
data.forEach(entry => {
const jeAmount = Number(entry.amount) * (20 / 100);
// Debit line
journalEntryRecord.selectNewLine({ sublistId: 'line' });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'account', value: isReverse ? 110 : entry.account });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'debit', value: jeAmount });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'custcol3', value: billMemo });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'memo', value: `Reverse charge liability booked for bill: ${tranNumber}` });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'department', value: entry.act_code });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'class', value: entry.div_unit_code });
journalEntryRecord.commitLine({ sublistId: 'line' });
// Credit line
journalEntryRecord.selectNewLine({ sublistId: 'line' });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'account', value: isReverse ? entry.account : 110 });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'credit', value: jeAmount });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'custcol3', value: billMemo });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'memo', value: `Reverse charge liability booked for bill: ${tranNumber}` });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'department', value: entry.act_code });
journalEntryRecord.setCurrentSublistValue({ sublistId: 'line', fieldId: 'class', value: entry.div_unit_code });
journalEntryRecord.commitLine({ sublistId: 'line' });
});
const journalId = journalEntryRecord.save();
const journalDocumentNumber = search.lookupFields({
type: 'journalentry',
id: journalId,
columns: ['tranid']
});
const journalEntryNumber = 'Journal #' + journalDocumentNumber.tranid;
return {
journalId,
journalEntryNumber: journalEntryNumber
};
};
if (journalEntryData.length > 0) {
let journalId = processJournalEntry(journalEntryData);
log.debug("journalId", journalId);
if (journalId) {
newRecord.setValue({ fieldId: 'custbody_jj_je_tax', value: journalId.journalEntryNumber});
}
}
if (reverseJEData.length > 0) {
let journalId = processJournalEntry(reverseJEData, true);
if (journalId) {
newRecord.setValue({ fieldId: 'custbody_jj_je_tax', value: journalId.journalEntryNumber});
}
}
};
return { beforeSubmit };
});