Get Total Unbilled PO Amount on Vendor Record Using Client Side Script – JCurve Solutions

Scenario

The total unbilled amount which can be seen in the Amount field on the Vendor record is based on the items' total quantity multiplied by their respective amounts. Some use cases permits users to change the rate of each item on the Vendor Bill record which will sometime exceed the total Purchase Order amount set in the Purchase Order record.

Solution

The following code can be useful for approval processes if the user wants to flag vendor bills exceeding the total Purchase Order amount for approval. Run this code in the PageInit function of a Client Script deployed to the Vendor Bill record:

function unbilledPOAmount(type){‌
    var ponum = nlapiGetFieldValue('podocnum');
    if(ponum != null && ponum != ''){‌
        var arrSearchFilter = new Array();
        arrSearchFilter[0] = new nlobjSearchFilter('createdfrom', null,'is', ponum);
        arrSearchFilter[1] = new nlobjSearchFilter('mainline', null,'is','T');
        var arrSearchColumn = new Array();
        arrSearchColumn[0] = new nlobjSearchColumn('amount',null,'sum');
        var result = nlapiSearchRecord('vendorbill', null, arrSearchFilter, arrSearchColumn);
        var billed = result[0].rawValues[0].value;
        var porec = nlapiLoadRecord('purchaseorder', ponum);
        var totalPO = porec.getFieldValue('total');
        var remaining = (totalPO - billed) < 0 ? 0  :  totalPO - billed;
        nlapiSetFieldValue('custbody_remainingamount',remaining); //set the calculated unbilled or remianing amount to be billed in your custom field
    }
	if (type == 'edit'){‌
        var arrSearchFilter = new Array();
        arrSearchFilter[0] = new nlobjSearchFilter('internalid', null,'is', nlapiGetFieldValue('id'));
        arrSearchFilter[1] = new nlobjSearchFilter('mainline', null,'is','T');
        var arrSearchColumn = new Array();
        arrSearchColumn[0] = new nlobjSearchColumn('createdfrom');
        var result = nlapiSearchRecord('vendorbill', null, arrSearchFilter, arrSearchColumn);
        var createdFromRecord = result[0].getValue('createdfrom');
        if(createdFromRecord != null && createdFromRecord != ''){‌
            var arrSearchFilter = new Array();
            arrSearchFilter[0] = new nlobjSearchFilter('createdfrom', null,'is', createdFromRecord);
            arrSearchFilter[1] = new nlobjSearchFilter('mainline', null,'is','T');
            var arrSearchColumn = new Array();
            arrSearchColumn[0] = new nlobjSearchColumn('amount',null,'sum');
            var result = nlapiSearchRecord('vendorbill', null, arrSearchFilter, arrSearchColumn);
            var billed = result[0].rawValues[0].value;
            var porec = nlapiLoadRecord('purchaseorder', createdFromRecord);
            var totalPO = porec.getFieldValue('total');
            var remaining = (totalPO - billed) < 0 ? 0  :  totalPO - billed;
            nlapiSetFieldValue('custbody_remainingamount',remaining); //set the calculated unbilled or remianing amount to be billed in your custom field

        }
	}
}