Requirement
Client would like to introduce a novel GL posting approach for its sales transactions, focusing on recognizing revenue exclusively after order fulfillment. Under this system, when generating the cash sale record, the GL impact posting into the sales account will undergo a reversal. Simultaneously, the equivalent amount will be redirected and posted into the deferred revenue account, extending until the fulfillment date.
Solution
On creating a cash sale record from a sales order, the script will sum up the amounts that are credited to the account “4000 Sales” for the inventory items and will add two custom GL lines in the cash sale record as follows:
- Debit the total sales amount for the inventory items from the account “4000 Sales”.
- Credit the same amount to the revenue account “11504 Deferred Income”.
We will not consider the items that are fulfilled (shipped) before creating a cash sale record.
Upon shipping an item fulfillment for the items in Cash Sale, we will reverse the GL and post back to the Sales account
Note : The script is not support suitescript 2.0
/**
* @NScriptType plugintypeimpl
*/
/*************************************************************************************************************************************************
* Toolfetch LLC-USA-NS
*
* ${TFLL-374} : ${Custom GL lines updates in the sales order}
*
*************************************************************************************************************************************************
*
* Author: Jobin & Jismi
*
* Date Created : 26-September-2023
*
* Description :This Module is used to add a custom GL line in the Cash Sales with "Deferred account"
*
* REVISION HISTORY
*
* @version 1.0 TFLL-383 : 25-September-2023 : Created the initial build by JJ0170
*************************************************************************************************************************************************/
/**
* Function to check whether the Cash sales is created from Sales order or not
* @param {number} createdFrom - Internal Id of the record wheter the cash sales is created
* @returns {boolean} true/false - true when record is created from SO else return false
*/
function checkCashSaleCreatedFromSalesOrder(createdFrom) {
try {
var salesorderSearch = nlapiSearchRecord("salesorder", null,
[
["type", "anyof", "SalesOrd"],
"AND",
["internalid", "anyof", "21475530"],
"AND",
["mainline", "is", "T"]
],
[
new nlobjSearchColumn("internalid")
]
);
if (salesorderSearch) {
return true;
}
else {
return false;
}
}
catch (e) {
nlapiLogExecution('ERROR', 'Error @ checkCashSaleCreatedFromSalesOrder', e.message);
return false;
}
}
/**
* Function to check whetehr an item fulfillment exists with the sales order
* @param {number} createdFrom - Internal Id of the Slaes order
* @returns ifOrderLines - Array containing item order order lines from item fulfillment
*/
function fetchItemOrderLinesFromIf(createdFrom) {
try {
var itemFulfillmentSearch = nlapiSearchRecord("transaction", null,
[
["type", "anyof", "ItemShip"],
"AND",
["createdfrom.type", "anyof", "SalesOrd"],
"AND",
["appliedtotransaction.internalid", "anyof", createdFrom],
"AND",
["item.type", "anyof", "InvtPart"],
"AND",
["status", "anyof", "ItemShip:C"]
],
[
new nlobjSearchColumn("item"),
new nlobjSearchColumn("line", "appliedToTransaction", null)
]
);
var ifOrderLines = [];
if (itemFulfillmentSearch && itemFulfillmentSearch.length > 0) {
for (var i = 0; i < itemFulfillmentSearch.length; i++) {
ifOrderLines.push(itemFulfillmentSearch[i].getValue("line", "appliedToTransaction", null));
}
}
return ifOrderLines;
}
catch (e) {
nlapiLogExecution('ERROR', 'Error @ fetchItemOrderLinesFromIf', e.message);
return [];
}
}
/**
* Function to fetch the order lines which are in the Cash Sales and in the Item fulfillment
* @param {Array} ifOrderLineArray - array containg item order lines from Cash sale record
* @param {Array} cashSaleOrderLineArray - array containg item order lines from Item fulfillment record
* @returns {Array} orderLinesNotInIfOrder
*/
function findOrderLinesNotInIfOrderArray(cashSaleOrderLineArray, ifOrderLineArray) {
try {
var orderLinesNotInIfOrder = [];
for (var i = 0; i < cashSaleOrderLineArray.length; i++) {
var orderLine = cashSaleOrderLineArray[i];
if (ifOrderLineArray.indexOf(orderLine) == -1) {
orderLinesNotInIfOrder.push(orderLine);
}
}
return orderLinesNotInIfOrder;
}
catch (e) {
nlapiLogExecution('ERROR', 'Error @ findOrderLinesNotInIfOrderArray', e.message);
return [];
}
}
/**
* Function to add custom GL lines in the Cash Sales record
* @param {object} transactionRecord - object of the current record
* @param {object} standardLines - object contains standard custom gl lines
* @param {object} customLines - object contains custom gl lines
*/
function customizeGlImpact(transactionRecord, standardLines, customLines) {
try {
if (transactionRecord.getRecordType() == "cashsale") {
var totalCashSaleAmount = 0;
var cashSaleInternalId = transactionRecord.getId();
// To work the script in Create mode
if (!cashSaleInternalId) {
var createdFromRecord = transactionRecord.getFieldValue('createdfrom');
if (createdFromRecord) {
// Proceeds only if the cash sale is created from Sales order
if (checkCashSaleCreatedFromSalesOrder(createdFromRecord) == true) {
var cashSaleorderLineArray = [];
for (var i = 0; i < transactionRecord.getLineItemCount('item'); i++) {
var itemType = transactionRecord.getLineItemValue("item", "itemtype", i + 1);
if (itemType == "InvtPart") {
var orderLine = transactionRecord.getLineItemValue("item", "orderline", i + 1);
cashSaleorderLineArray.push(orderLine);
}
}
var ifOrderLineArray = fetchItemOrderLinesFromIf(createdFromRecord);
var orderLinesNotInIf = findOrderLinesNotInIfOrderArray(cashSaleorderLineArray, ifOrderLineArray);
//When No IF created this Cash Sale with Shipped status
if (orderLinesNotInIf && orderLinesNotInIf.length > 0) {
for (var i = 0; i < transactionRecord.getLineItemCount('item'); i++) {
var orderLine = transactionRecord.getLineItemValue("item", "orderline", i + 1);
if (orderLinesNotInIf.indexOf(orderLine) != -1) {
var cashSalesAmount = transactionRecord.getLineItemValue("item", "amount", i + 1);
for (var j = 0; j < standardLines.getCount(); j++) {
var creditAmount = standardLines.getLine(j).getCreditAmount();
var accountId = standardLines.getLine(j).getAccountId();
if ((Number(creditAmount) - Number(cashSalesAmount) == 0) && accountId == 23) {
totalCashSaleAmount = Number(totalCashSaleAmount) + Number(creditAmount);
}
}
}
}
if (totalCashSaleAmount > 0) {
// To debit the credited amount in Sales account
var newLine1 = customLines.addNewLine();
newLine1.setDebitAmount(totalCashSaleAmount);
newLine1.setAccountId(23);
// To credit the debited amount to the Deferred revenue account
var newLine2 = customLines.addNewLine();
newLine2.setCreditAmount(totalCashSaleAmount);
newLine2.setAccountId(290);
}
}
}
}
}
}
} catch (e) {
nlapiLogExecution('ERROR', 'Error @ customizeGlImpact', e.message);
}
}.