Jira Code: UN-329
Automate the creation of intercompany journal entries when an expense transaction is created:
1. When entering an expense transaction that must be allocated to other subsidiaries, specify the subsidiary to which the line must be allocated. For example, the Utrus subsidiary is paying for Rent expenses on behalf of the Umitrus, Kwiktrus, and KCIMTRUS subsidiaries.
Also, mark the checkbox “ Intercompany Vendor Bill” for the creation of intercompany journal entries when an expense transaction is created.
2. A new custom field called “IC Sub” will be added to capture the subsidiary at line level of the vendor bill and vendor bill credit transactions.
3. Upon vendor bill entry, the AP staff entering the bill will enter one line for each subsidiary to receive a portion of the overall expense. The expense values are entered as amount and not in percentage. The applicable “IC SUB” will be selected at the line level.
4. On saving the transaction an intercompany journal entry has been created for each line. A link to the journal entry has been placed in a custom field “IC JOURNAl” on the line from which it was created.
5.The intercompany journal entry has credited (reversed) the expense on the Utrus subsidiary’s ledger, debited (booked) the expense on the Umitrus subsidiary, and created the correct intercompany payable/receivable entries.
6. On deleting the vendor bill, it deletes all the corresponding journal entries created. Note that the journal entries are created only on creation of bill.
7. A custom record called “Intercompany Details “(Intercompany Details) is created to fetch values like Account Receivable, Account Payable, Intercompany Customers and Intercompany Vendors using a saved search in the script.
- Account Receivable: 1101 Intercompany Accounts Receivable-Kwikclamp Systems , 1102 Intercompany Accounts Receivable-KCIMTRUS Pty Ltd, 1103 Intercompany Accounts Receivable-Umitrus Pty Ltd , 1104 Intercompany Accounts Receivable-Utrus Pty Ltd
- Account Payable: 2004 Intercompany Accounts Payables-Utrus Pty Ltd, 2005 Intercompany Accounts Payables-Aptrust Pty Ltd .
- Intercompany Customers: C88 Umitrus Pty Ltd, C88 Umitrus Pty Ltd , C13 Kwikclamp Systems , C86 KCIMTRUSPty Ltd .
- Intercompany Vendors: V149 Aptrust Pty Ltd , V139 Utrus PtyLtd , V143 Utrus Pty Ltd , V148 Utrus Pty Ltd , V150 Aptrust Pty Ltd , V151 Aptrust Pty Ltd , V152 Aptrust Pty Ltd .
/**
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
*/
/**
* Script Description
* Automate the creation of intercompany journal entries when an expense transaction is created.
**/
/*******************************************************************************
* UNICORN
* **************************************************************************
*
* Date: 11-12-2018
*
* Author: Jobin & Jismi IT Services LLP
*
*
* REVISION HISTORY
*
* Revision 1 $ 11-12-2018 rijoy : Created
*
* ****************************************************************************
*/
define([ 'N/file', 'N/format', 'N/record', 'N/runtime', 'N/search',
'N/transaction', 'N/url' ],
/**
* @param {file}
* file
* @param {format}
* format
* @param {record}
* record
* @param {runtime}
* runtime
* @param {search}
* search
* @param {transaction}
* transaction
*/
function(file, format, record, runtime, search, transaction) {
/**
* Function definition to be triggered before record is loaded.
*
* @param {Object}
* scriptContext
* @param {Record}
* scriptContext.newRecord - New record
* @param {string}
* scriptContext.type - Trigger type
* @param {Form}
* scriptContext.form - Current form
* @Since 2015.2
*/
function beforeLoad(scriptContext) {
}
/**
* Function definition to be triggered before record is loaded.
*
* @param {Object}
* scriptContext
* @param {Record}
* scriptContext.newRecord - New record
* @param {Record}
* scriptContext.oldRecord - Old record
* @param {string}
* scriptContext.type - Trigger type
* @Since 2015.2
*/
function beforeSubmit(scriptContext) {
}
/**
* Function definition to be triggered before record is loaded.
*
* @param {Object}
* scriptContext
* @param {Record}
* scriptContext.newRecord - New record
* @param {Record}
* scriptContext.oldRecord - Old record
* @param {string}
* scriptContext.type - Trigger type
* @Since 2015.2
*/
function afterSubmit(scriptContext) {
try {
var recId = scriptContext.newRecord.id;
log.debug("recId", recId);
var bill_Check = scriptContext.newRecord.getValue({
fieldId : 'custbody1'
});
log.debug("bill_Check", bill_Check);
var currency = scriptContext.newRecord.getValue({
fieldId : 'currency'
});
log.debug("currency", currency);
var subsidiary = scriptContext.newRecord.getValue({
fieldId : 'subsidiary'
});
log.debug("subsidiary", subsidiary);
var N_lines = scriptContext.newRecord.getLineCount({
sublistId : 'expense'
});
for (var i = 0; i < N_lines; i++) {
var Account = scriptContext.newRecord.getSublistValue({
sublistId : 'expense',
fieldId : 'account',
line : i
});
var Amount = scriptContext.newRecord.getSublistValue({
sublistId : 'expense',
fieldId : 'amount',
line : i
});
var IC_Sub = scriptContext.newRecord.getSublistValue({
sublistId : 'expense',
fieldId : 'custcol1',
line : i
});
log.debug("Account", Account);
log.debug("Amount", Amount);
log.debug("IC_Sub", IC_Sub);
var account_receivable;
var account_payable;
var ic_customer;
var ic_vendor;
var journal_Deatils = search.create({
type : "customrecord_jj_intercompany_details",
filters : [
[ "custrecord_jj_from_subsidiary", "anyof",
subsidiary ],
"AND",
[ "custrecord_jj_to_subsidiary", "anyof",
IC_Sub ] ],
columns : [ search.createColumn({
name : "custrecord_jj_account"
}), search.createColumn({
name : "custrecord_jj_account_payable"
}), search.createColumn({
name : "custrecord_jj_customer"
}), search.createColumn({
name : "custrecord_jj_ic_vendor"
}) ]
});
journal_Deatils.run().each(
function(result) {
account_receivable = result
.getValue(journal_Deatils.columns[0]);
account_payable = result
.getValue(journal_Deatils.columns[1]);
ic_customer = result
.getValue(journal_Deatils.columns[2]);
ic_vendor = result
.getValue(journal_Deatils.columns[3]);
});
log.debug("account_receivable", account_receivable);
log.debug("account_payable", account_payable);
log.debug("ic_customer", ic_customer);
log.debug("ic_vendor", ic_vendor);
createJournalentry(currency, subsidiary, IC_Sub, Amount,
Account, account_receivable, account_payable,
ic_customer, ic_vendor,recId,i);
}
} catch (err) {
log.debug("err", err);
}
}
return {
beforeLoad : beforeLoad,
beforeSubmit : beforeSubmit,
afterSubmit : afterSubmit,
};
/* Function to create intercompany journal entry*/
function createJournalentry(j_Currency, j_Subsidiary, j_IC_Sub, j_Amount,
j_Account, j_account_receivable, j_account_payable, j_ic_customer,
j_ic_vendor,j_recId,j) {
log.debug("j_Subsidiary", j_Subsidiary);
log.debug("j_IC_Sub", j_IC_Sub);
log.debug("j_Amount", j_Amount);
log.debug("j_Account", j_Account);
log.debug("j_account_receivable", j_account_receivable);
log.debug("j_account_payable", j_account_payable);
log.debug("j_ic_customer", j_ic_customer);
log.debug("j_ic_vendor", j_ic_vendor);
var newJournal = record.create({
type : record.Type.ADV_INTER_COMPANY_JOURNAL_ENTRY,
isDynamic : true,
});
newJournal.setValue({
fieldId : 'subsidiary',
value : j_Subsidiary,
});
newJournal.setValue({
fieldId : 'currency',
value : j_Currency,
});
for (var i = 0; i < 4; i++) {
newJournal.selectNewLine({
sublistId : 'line',
});
if (i == 3) {
newJournal.setCurrentSublistValue({
fieldId : 'linesubsidiary',
sublistId : 'line',
value : j_IC_Sub,
line :i,
});
newJournal.setCurrentSublistValue({
fieldId : 'entity',
sublistId : 'line',
value : j_ic_vendor,
line :i,
});
newJournal.setCurrentSublistValue({
fieldId : 'account',
sublistId : 'line',
value : j_account_payable,
line :i,
});
} else if (i == 0) {
newJournal.setCurrentSublistValue({
fieldId : 'linesubsidiary',
sublistId : 'line',
value : j_Subsidiary,
line :i
});
newJournal.setCurrentSublistValue({
fieldId : 'entity',
sublistId : 'line',
value : j_ic_customer,
line : i
});
newJournal.setCurrentSublistValue({
fieldId : 'account',
sublistId : 'line',
value : j_account_receivable,
line : i
});
} else if (i == 1) {
newJournal.setCurrentSublistValue({
fieldId : 'linesubsidiary',
sublistId : 'line',
value : j_Subsidiary,
line : i
});
newJournal.setCurrentSublistValue({
fieldId : 'account',
sublistId : 'line',
value : j_Account,
line : i
});
} else {
newJournal.setCurrentSublistValue({
fieldId : 'linesubsidiary',
sublistId : 'line',
value : j_IC_Sub,
line : i
});
newJournal.setCurrentSublistValue({
fieldId : 'account',
sublistId : 'line',
value : j_Account,
line : i
});
}
if (i % 2 == 0) {
newJournal.setCurrentSublistValue({
fieldId : 'debit',
sublistId : 'line',
value : j_Amount,
line : i
});
} else {
newJournal.setCurrentSublistValue({
fieldId : 'credit',
sublistId : 'line',
value : j_Amount,
line : i
});
}
newJournal.commitLine({
sublistId : 'line'
});
}
log.debug("j_recId",j_recId);
var j_Rec = newJournal.save();
log.debug("j_Rec", j_Rec);
var objRecord = record.load({
type: record.Type.VENDOR_BILL,
id:j_recId,
isDynamic: true,
});
objRecord.selectLine({
sublistId : 'expense',
line:j
});
objRecord.setCurrentSublistValue({
fieldId : 'custcol2',
sublistId : 'expense',
value :j_Rec ,
line :j
});
objRecord.commitLine({
sublistId : 'expense'
});
var edit_Bill = objRecord.save();
log.debug("edit_Bill",edit_Bill);
}
});