An SOV can be imported via an XML data file (created using Excel).
To use, from Job Contract Manager, with the Job selected, SOV folder, SOV workbench, use the Options menu in the upper right to Import SOV.
Figure 1: Import SOV in Options menu on SOV Workbench
On the Import screen, click on the Choose File button and select the SOV Template to be imported. This file should be an XML file (Excel XML 2003).
Fill in the other required data:
Import Contains Headers?
If the file has a header row (column headings), then make sure the checkbox is checked. This will let the function know to ignore the first row of the file.
Import Options
Append and Import File
This will add the rows from the import to any existing SOV lines/sections
Delete and Import File
This will delete existing SOV data and provide option to delete any change management. This option will not work if billings exist.
Archive and Import File
Will prompt user to create a new “SOV” in addition to the normal SOV to which to move existing items. Not recommended to use.
Detailed Report checkbox
Select to create a detailed report of all the lines as they are added to the SOV. If there are issues importing a specific file, selecting this option may provide more details as to what needs to be corrected. It is safe to leave this unchecked unless there is an issue with loading.
Uncheck Create Cost Codes checkbox as this SOV will not be used to create Job Status Cost Codes.
Stop Error
This indicates the number of errors to encounter before aborting the load. Leave at 1 to prevent loading a file with any errors.
Figure 2: Import SOV Page with Details Filled
Click Next.
After the file loads, go back to the SOV Workbench to see the imported SOV.
Figure 3: Imported SOV
Import File Format
The import file for the SOV will allow for the creation of an SOV if nothing exists (including the initial SOV “section”). This example file was designed to look like the bulk in most aspects.
SOV Import Template Columns
SOV Section
This is the code of the section to which the line should be added. If the Section code does not already exist, a section will be created. (e.g. BASE)
SOV Description
This is the description of the SOV level. (e.g. Base Contract)
Row ID
This is the Row ID. If left blank, COINS will auto-generate a number in sequential order. This number must be unique.
Every row in the file will have a Row ID (either manually specified or auto-generated).
The order of lines as imported into the SOV will be determined by the Row ID value.
E.g. Importing a file with two rows:
First row has a Row ID of 20
Second Row has a Row ID of 10
After import, the row with ID 10 will be listed first and row with ID 20 will be second, regardless of the order on the import file.
Row Type
This determines how the row is used in the SOV. Options Include:
Head – a Heading which has no value. Item Number must remain blank for Heading rows
Totprice – a Lump Sum row. User must fill in quantity (1), Unit (EA), Rate (the value of the lump sum line), and Value (same as Rate column value)
Cost – a Cost Based row. COINS will use Cost Transactions as pulled into the Job Contract Manager to determine the billing amount for this line. User can enter in quantity, Unit, Rate, and Value if system is set for GMP or user wants to use that for validation purposes to know when cost-based billings go above a certain value.
Subtot – Subtotal row. User must leave Item Number blank. User should add in the Row IDs of the rows that are to be used to calculate the Subtotal amount in the Subtotal field. This can have the format of +100, +101. Calc Rate % field should have 100 to fully calculate the subtotal.
Calc – Calculated row similar in function to the Subtotal row except the value of the Row will be added to the overall value of the Billing. User will need to specify the Row IDs of the rows on which to base the calculation and the Calc Rate % field should be the percent of the value of those rows to add to the total billing.
Leaving Row Type Blank – Leaving the Row Type field blank will make the row a Qty/Rate line. User can enter in A Quantity, Unit, Rate, and Value to allow entering in quantities installed to determine billing value of that line.
Item Number
This is the identifier of the line that is typically printed on the Billing form.
Description
The description of the row that will appear on the printed Billing form.
Client Reference
An optional field to record any text based data point for this row. It is typically not printed on the Billing form.
Series
The Job Receivables Series code that should be attached to this line. (Also known as Sales Series) These are set up on the Job file under Job Receivables Series.
Revenue Analysis
Optional field to redirect revenue for this line to a different job and/or cost code than is specified on the Job Receivables Series. Frequently used in Master/Subsidiary Job billings.
Tax Code
Optional Field to override the Tax Code that is specified on the Job Receivables Series
Quantity
Quantity of units on this line. For Lump Sum (Totprice) lines, typically a 1. If using Qty/Rate Row Type, then the number of units that will be installed.
Optional for Cost Based lines but can enter in 1 and a rate in the following field for comparison to actual cost based billing amounts.
Should remain blank for Heading, Calculated and Subtotal Rows
Unit
Typically EA for Lump Sum (Totprice) lines. Can specify different Unit of measure for Qty/Rate Row Types if applicable.
Should remain blank for Heading, Calculated and Subtotal Rows
Rate
Typically the value of the line for Lump Sum (Totprice) lines. For Qty/Rate Row Types, the Rate that should be filled for every unit installed.
Optional for Cost Based lines but can enter in value of the line and a quantity of 1 in the Quantity field for comparison to actual cost based billing amounts.
Should remain blank for Heading, Calculated and Subtotal Rows
Value
Should be equal to the quantity * rate for any line where Quantity and Rate were entered
Should remain blank for Heading, Calculated and Subtotal Rows
Subtotal
If using Subtotal or Calculated Rows, the definition of which Rows are being Subtotaled or which are being used as the value to base the calculated amount.
Calc Rate %
For Subtotal Rows, this should always be 100
For Calculated Rows, the percentage to use in the Calculation
Entered as a percentage
E.g. 10% calculation would be entered as 10
Print
Whether to print the Row on the Billing form
Y = Print
N or Blank = Do Not Print
Figure 4: SOV Import Template
