Using Infoplus Scripts, create an Invoice Worksheet Customizer script which allows the manipulation of 3PL Billing Invoice Worksheet Lines as an Invoice Worksheet is being generated. Available to the script is the Invoice Worksheet object, the list of input entities that were used to generate Invoice Worksheet Lines, and the list of Invoice Worksheet Line objects. This script must call the setOutputLines method if any lines are added to the Invoice Worksheet. If setOutputLines is not called, then no lines will be added to the Invoice Worksheet for this Invoice Template Line.


NOTE: When data is manipulated on the original Invoice Line objects, fields that may require recalculation, such as line totals, must be performed by the script as they will not be recalculated automatically. Also, be sure that your seqNo field, which is required, is not the same as any other Worksheet Line, from this Invoice Template Line or any other Invoice Template Lines in this Invoice Template.


A script of this type has the following variables available:


utils - object with the following methods:

    .log(message) - add a line to the Script Output's log.

    .getInvoiceWorksheet() - returns the Invoice Worksheet object which is the header record for all Invoice Worksheet Lines.

    .getInputEntities() - returns the list of objects that were used for calculating the data in the orignalLines.

    .getOriginalLines() - returns the list of Invoice Worksheet Line objects that were generated and would have been added to the Invoice Worksheet if no script was specified on the Invoice Template Line.

    .getInvoiceTemplateLine() - returns the Invoice Template Line for which the original lines were generated.

    .setOutputLines(invoiceWorksheetLineList) - sets the list of Invoice Worksheet lines that will actually be used for the Invoice Worksheet.


infoplusApi - object which provides access to the Infoplus API, specifically, with the following methods:

    .search(type, filter, pageNo, limit, orderBy) - run a search query in the Infoplus API.

    .getById(type, id) - get the record of the specified type identified by the id.

    .getTags(type, id) - get the tags from Infoplus for the specified record.

    .constructModel(type) - used to construct an API model object of the given type.

        - See the https://developer.infopluscommerce.com/ for more details.


Here's an example 3PL Billing Invoice Worksheet Customizer script.  This example script counts up the number of cartons that were shipped using a 3rd party account, then modifies the billing OrderActivity rows, setting an extendedCharge for each one.  The script also modifies the invoiceWorksheetLine by setting the quantity and extendedCharge.


utils.log("Running Invoice Worksheet Script...");
 
var carrierMap = {};
 
carrierMap["RPS - RALSTON 3RD PARTY"] = 1;
carrierMap["FEDERAL EXPRESS/3RD PARTY"] = 1;
carrierMap["FED.EXP.2DAY 3RD PARTY"] = 1;
carrierMap["USF Holland 3rd Party"] = 1;
carrierMap["Amazon 3rd Party - LTL"] = 1;
carrierMap["LTL-3rd Pty Walgreens/NCS"] = 1;
carrierMap["UPS 3rd Party - Quidsi"] = 1;
carrierMap["MMB 3rd Party LTL-FW"] = 1;
carrierMap["Cascio 3rd Pty LTL-UPSFRT"] = 1;
carrierMap["UPS 3rd Party GND - MFI"] = 1;
carrierMap["LTL 3rd Party - MFI"] = 1;
carrierMap["UPS 3rd Party GND- Cascio"] = 1;
carrierMap["UPS 3rd Party GND- Skip's"] = 1;
carrierMap["Estes - 3rd Party"] = 1;
carrierMap["R+L Carriers 3rd Party"] = 1;
carrierMap["FEDEX INTL-3rd PARTY"] = 1;
carrierMap["FED EX/PRI.ONE-3rd Party"] = 1;
carrierMap["FED-EX/STD-3rd Party"] = 1;
carrierMap["FED-EX/ECO/2DAY-3rd Party"] = 1;
carrierMap["UPS 3rd Party"] = 1;
carrierMap["FEDEX 3rd Party"] = 1;
carrierMap["FED-EX ECONOMY 2-DAY/3RD"] = 1;
carrierMap["ROADWAY EXPRESS INC-3RD P"] = 1;
carrierMap["FED-EX FREIGHT 3rd Party"] = 1;
carrierMap["FedEx FRT 2day GUAR 3rd P"] = 1;
carrierMap["Old Dominion- 3rd Party"] = 1;
carrierMap["All American Transprt-3rd"] = 1;
carrierMap["HYMAN FRT-3RD PARTY-PRE"] = 1;
carrierMap["UPS FREIGHT 3rd PARTY"] = 1;
carrierMap["YELLOW FREIGHT/3RD PARTY"] = 1;
carrierMap["CENTRAL TRANSPORT/3RD PAR"] = 1;
carrierMap["1st Choice - 3rd Party"] = 1;
carrierMap["Freight Watchers 3rd Prty"] = 1;
carrierMap["DOD Fees - 3rd Party"] = 1;
carrierMap["PILOT FREIGHT-3RD PARTY"] = 1;
carrierMap["AD 3RD PARTY TRUCK"] = 1;
carrierMap["C.H. ROBINSON/3RD PARTY"] = 1;
carrierMap["ROADWAY-3RD PARTY COLLECT"] = 1;
carrierMap["HOLMES-3RD PARTY COLLECT"] = 1;
carrierMap["HYMAN-3RD PARTY COLLECT"] = 1;
carrierMap["FDX Smartpost- 3rd Party"] = 1;
 
var outputLine = utils.originalLines.get(0);
var quantity = 0;
 
/////////////////////////////////////////////////////
// uncomment these three lines to produce an error //
/////////////////////////////////////////////////////
// var outOfBounds = utils.inputEntities.size();
// outOfBounds++;
// var outputLineBad = utils.originalLines.get(outOfBounds);
 
utils.log("Viewing Input Entities");
for(var i=0; i<utils.inputEntities.size(); i++)
{
   var inputEntity = utils.inputEntities.get(i);
   var isMatch = false;
   
   var carrier = inputEntity.getCarrierName();
   if(carrierMap[carrier] > -1)
   {
      utils.log("Carrier [" + carrier + "] matches the test for 3rd party.");
      isMatch = true;
   }
   else
   {
      // can turn this log line on for debugging.
      // utils.log("Carrier [" + carrier + "] does NOT match the test for 3rd party.");
   }
     
   if(inputEntity.getThirdPartyParcelAccountId() != null)
   {
      utils.log("Has a third party parcel account, so it's a match!");
      isMatch = true;
   }
 
   //////////////////////////////////////////////////////////////////
   //set the charge for this line in the backup file charge details//
   //////////////////////////////////////////////////////////////////
   if(isMatch && inputEntity.getNumberOfCartons())
   {
      if(inputEntity.getNumberOfCartons())
      {
        inputEntity.setExtendedCharge(inputEntity.getNumberOfCartons() * outputLine.chargeRate);
        quantity += Number(inputEntity.getNumberOfCartons());
      }
   }
   else
   {
      inputEntity.setExtendedCharge(0);
   }
}
 
utils.log("");
 
utils.log("Manipulate data in the original invoice lines...");
outputLine.quantity = quantity;
outputLine.extendedCharge = quantity * outputLine.chargeRate;
 
var outputLines = utils.originalLines;
utils.setOutputLines(outputLines);


To use an Invoice Worksheet Customizer script, edit a line on a Customer Invoice Template to be associated to the script.  For example, in the screenshot below we're applying a script called "test 3pl" against this line on a Customer Invoice Template: