Floworks NetSuite Import

Overview

Automated Work Order import into NetSuite is completed via a re-occurring script, currently running at a 20 minute interval. Following automated validation or override, work orders are set to a "Processed" state. Any "Processed" work orders are picked up by the import script which attempts to upload them to NetSuite via web services.

As outlined below, work orders that are successfully uploaded impact many areas including payroll, General Ledger (GL), Inventory Transfers, etc. The sections below are listed in the same order that the import script operates.

The import script was built to modular, meaning that any portion of the script can disabled individually. An example of this would be disabling the Payroll module for a given time.

Prior to Import

Before the import script begins to upload work orders, several sync scripts are run to ensure that all data in the Flogistix database is synced up correctly with NetSuite. This includes updating Unit information (Customer, Location, etc), Part and labor code information. This guarantees that any information going into NetSuite is up to date. This is also the same mechanism that updates the field laptops with up to date information.

Initial Validation

In addition to the preliminary validations done by Floworks, incoming work orders are subject to a final validation before attempting to be uploaded to NetSuite. These validations can be toggled to a disabled state upon request. Any work order failing to meet the validations below can be found in the "Invalid Work Order" view in Floworks with a reason given as to why it failed validation. The import script will re-attempt to upload invalid work orders on each cycle, so if a problem is corrected it is likely to be imported within minutes unless other errors are found. Validations are as follows:

• • Billable Work Orders - These work orders are still accepted and processed into NetSuite, however they are flagged for invoicing and will appear on the "Billable Work Orders" view in Floworks following successful upload. Work orders are flagged as Billable if the work order is checked as Billable at the top level and the Unit Number provided is not recognized as a rental compressor. A secondary check is done for any parts marked as billable. This will also trigger the system to mark this work order as "Billable" so that invoicing can be done if needed.

  • Recognized Mechanic - This should theoretically never happen, however there is a final check to ensure that the Mechanic number provided is a known mechanic. This will ensure that payroll is added properly.

  • Unlisted Part(s) - Mechanics are given the option of adding an "Unlisted Part" to a work order should they not be able to find a given part. Any work order containing one

Creating Overhead Journal Entry

After the Labor Journal Entry is created, an Overhead Journal Entry is also constructed and added for the calculated overhead costs of the related work order. Like the Labor JE, the Overhead JE consists of header information and line items. The header items reference the appropriate subsidiary of the unit (with mechanic subsidiary being the fallback if no unit is given on the work order, as with Indirect work orders), the date of the Journal Entry (this will be the work order date), and a reference to the work order that initiated this Journal Entry.

Overhead Journal Entry Amount Calculation

Overhead Total = Total Time * Business Unit Standard Burden Rate

Total Time = Total Time of work order translated to decimal (ex: 1:30 = 1.5)

Overhead Total is then rounded to 2 decimals places to reach a final number

Overhead Journal Entry Line Items

Debit Line

• • • Account: "53100 Cost of Sales : Overhead : Burden"

    • Debit Amount: The amount of overhead as calculated above

    • Memo: References the work order id that is generating this Journal Entry

    • Department/Product Line: For non-billable work orders, this will be "Product Line : Field Service". For Billable work orders, "Product line : Third Party" is used

    • Operating Area/BU: Business Unit of the Asset (again, falling back to the Mechanic's business unit if no unit provided)

Credit Line

• • Account: "66400 Contra - Burden"

  • Credit Amount: The amount of overhead as calculated above

  • Memo: References the work order id that is generating this Journal Entry

    • Department/Product Line: this will be "Department : Field Service"

    • Operating Area/BU: Business Unit of the Asset (again, falling back to the Mechanic's business unit if no unit provided)

    • Related Asset: Asset from the work order that is creating this Journal Entry. Setting the Related Asset is a vital step to maintaining the unit-level P&L statement

Creating the Inventory Adjustment

At the point, the import script will begin processing the parts attached to the work order. The parts will be added the custom record work order generated above. Parts will be attached with a part qty, billable & warranty flags set, and an associated labor code and inventory number. During this process, the script will begin to construct an Inventory Adjustment. Inventory Adjustments consist of header fields and line items that contain each part used. The script will set header items as follows:

• • Subsidiary - The subsidiary of the unit. Parts cannot be used on an Indirect work order, so we do not need to fall back to the mechanic subsidiary.

  • Operating Area/BU - This is the Business Unit of the Asset. Again, parts cannot be used on an Indirect work order, so we do not need to fall back to the mechanic business unit.

  • Adjustment Account - The account to take this adjustment against. This is set to "51200 Cost of Sales : Materials : Parts" on all Inventory Adjustments.

  • Date - This is the transaction date for this Inventory Adjustment. This is the current date & time that the adjustment was created. It is vital that this uses the current time stamp due to the fact that NetSuite will auto-populate the "before" part quantity on hand based on this date. Setting this to something other than the current time will cause this before number to be different and may result in negative inventory numbers as well incorrect income/expense amounts on the unit.

  • Work Order Ref - This is the reference to the work order that is generating this Inventory Adjustment.

  • Related Unit Asset Number - This is a reference to the Fixed Asset referenced on the work order that is generating this Inventory Adjustment.

Inventory Adjustment Line Items

Each line item for the Inventory Adjustment will contain the part number, warehouse and quantity by which to adjust the inventory levels. NetSuite will auto populate the Units, Qty On Hand, Current Value, New Quantity and Est Unit Cost Fields when the Inventory Adjustment is created.

Creating the Asset Income/Expense Items

All Labor/Overhead Journal Entries, as well as Inventory Adjustments and Invoices will be listed under the Income/Expense Tab on the Fixed Asset. This is generates a unit-level P&L statement. The Journal Entries created above will automatically become part of the Income/Expense Tab (based on setting the "Related Asset" field). However, the import script must create an Income/Expense entry for any Inventory Adjustments created during the import process. After creating the Inventory Adjustment, the import script queries the "Estimated Total Value" of the Inventory Adjustment (as automatically calculated by NetSuite upon creation). The Income/Expense Item is then constructed using this amount, as well as the Asset Number and FlogistixID of the work order in the Memo field.

After Work Order Import

After a work order has been successfully uploaded in NetSuite, a flag is flipped in the local Flogistix local database to ensure that work orders are not uploaded more than once.