Adjutant Wiki MW Pivot Skin

General Import Template File Notes

• Template files are typically generated from their respective Import screen using an 'Export Template' button.
• Using the 'Export Template' button from an updated system will help ensure that you are using the most up-to-date import file, and will help avoid errors.
• Templates generally (but not in all cases) contain 3 rows of header information.
  • The first row is generally labelled with f1 through f## and is used to give a consistent column id to each data column. Do not remove this row unless instructed.
  • The second row is labelled with the column data/field name. The values in this row are used for the file mapping screen. Editing these values is generally not needed and may make the field mapping process difficult.
  • The third row (if present) is labelled either with the same values from row 2, or with more detailed field descriptions. The third row is available to create a customer-friendly name for the associated data column.
    • The third row should be removed from the source file prior to importing. It will cause errors since it will be treated as the first import record on the file.
• Import files MUST always be saved as 'Microsoft Excel 5.0/95 Workbook (*.xls)' format until further notice. The import screens will generate errors if the file type is not correct.

• It is a good idea to perform a Mapping Table Import using a current source list before beginning on any imports. Without a current mapping table, the quality of imports can be compromised. The Mapping Table Import only needs to be run once on a newly set up Adjutant system. It does not need to be run before each import.
  • Export a current list from the Mapping Table Import screen from an updated system (or contact your Implementation Consultant for an updated file).
  • Perform a Mapping Table Import on the target system with the XLS file from the step above.

Sales Order Import Notes

Timing and Preparation

The Sales Order Import should occur after all customers have been imported, all Item Master records have been imported, and after account managers and salespersons have been established.

The Sales Order Import is generally used to import only the open sales order records at the time of go-live. However, it is also possible to import completed sales orders for historical reference. If importing completed sales orders, understand that completed sales orders should not be reopened and edited because the sales order amounts may recalculate, which would create incorrect historical records.

General Notes

Configurable Items - The Sales Order import does not have the ability to import custom configuration details with each line item. Discuss this with the customer and develop a plan to capture the configuration details and to update the imported order items manually after the import.

Order Amounts - Customers should expect the order totals for imported open orders to change once they are edited in Adjutant. There are a number of factors which may cause pricing and order totals to recalculate once the order is edited in Adjutant. Minor rounding differences, changes in customer pricing structure, program differences between the legacy system pricing calculations and Adjutant, configuration pricing differences, and tax calculations are just some of the factors that may affect order totals once the imported records are edited in Adjutant. Customers should be prepared for these differences, and a plan of action should be developed to review and adjust orders as needed.

Taxability - Confirm with the customer that taxable flags can be reliably set on the source data. The import requires that the taxability of each line item is defined, and if it is not set to yes, it will default to non-taxable. Also, as stated above, tax calculations between two different systems will commonly be different because of rounding or calculation methods. Differences between customer setup information or between the ways that order taxes are controlled (for example, legacy systems may allow tax rates to be manually decided at the order level compared to being decided by the Ship To location) can lead to different tax calculations. Customers should expect to carefully review and validate each imported order's tax amounts.

Sales Order Import File Data Scrubbing

Every customer's data will have different issues that need addressing. Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing. This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import. Some common things that need attention during AP invoice data scrubbing include:

• Open vs. Closed sales orders: If you are importing completed historical orders, understand that the source data may require significantly more scrubbing, as well as decisions on how to handle obsolete data. Data like salespersons, account managers, customers, rule-based drop-down options, and even items that may no longer be valid will have to be added to Adjutant in order to complete the import. This means deliberately adding obsolete records in several areas in order to support historical data. Discuss the options carefully with the customer before proceeding.

• Dates - Make sure all dates follow the MM/DD/YY format before importing. Also review each date column and work with the customer to ensure the right dates are being imported in the right columns.

• Amounts - Carefully review and verify that the header file totals and the detail file totals match by invoice number. Also verify that the header subtotal/tax total/total amount values add up correctly and match with the extended price and extended tax values in the detail file.

• Line numbers - If the legacy system doesn't provide line numbers, the import can automatically generate line numbers based on the order of the order details on the import spreadsheet. If line numbers are provided, pay close attention that line number data does not get altered during the scrubbing process. The line number information should also be carefully reviewed for duplicate line numbers on the same order. The import will skip duplicates and only import the last one encountered.

• Terms - Make sure terms data is accurate and matches an entry in the TERMS rule. Any imported TERMS value that does not exist in the TERMS rule will create a blank entry in the rule. Best practice is to match each order's terms to a valid TERMS entry. In some systems, order terms are a text field and there is no standardization on the legacy system. For these instances, it is best to scrub each term value until it matches an existing TERM. Alternatively, you can create a catch-all TERM rule for unknown, incomplete, or undefined term details in the source data.

• Ship Via - Make sure Ship Via data is accurate and matches an entry in the SHIPVIA rule. Follow the same guidelines detailed in the Terms section above for scrubbing Ship Via data.

• Freight - Make sure Freight data is accurate and matches an entry in the FREIGHT rule. Follow the same guidelines detailed in the Terms section above for scrubbing Freight data.

• FOB - Make sure FOB data is accurate and matches an entry in the FOB rule. Follow the same guidelines detailed in the Terms section above for scrubbing FOB data.

Sales Order Import Screen (IMPORTSO)

Menu Location: Transaction>>Importers/Exporters>>Purchase and Sales Orders>>Sales Order Import

File Name(Header)/Browse: Use the Browse button to locate and select the completed Sales Order Header import template file (in XLS format). Note - both the Header and Details files are required and must be imported at the same time.

File Name(Details)/Browse: Use the Browse button to locate and select the completed Sales Order Details import template file (in XLS format). Note - both the Header and Details files are required and must be imported at the same time.

Export Template: Generates a blank Sales Order Header or Details template file

Map Fields: Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping. Repeat for any red lines on the left that should be mapped. If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.

Validate to TXT file: Selecting this box will create a TXT errors file listing any rows that would create errors such as invalid data, or duplicate records. If no errors are found in the file, the import will still process and no error file will be generated. Checking the validate box only prevents the import process if errors are encountered.

Sales Order Import Header File Definitions

Required fields are indicated with an *

f1 - TaxTable - This field is no longer used

f2 - OrderNo - Sales order Order # field value (max 30 characters)

*f3 - Owner - Inventory owner name

*f4 - SODate - Sales Order Date (MM/DD/YY format)

f5 - ReqDate - Sales Order Required Date (MM/DD/YY format)

f6 - Freight - Freight terms for the selected sales order. Valid freight terms from the FREIGHT rule should be used. If an entry is found during import that doesn't match an existing FREIGHT rule record, that entry will be added to the FREIGHT rule with no other rule details. This allows the import to complete, but could add unwanted records to the FREIGHT rule.

f7 - Terms - Payment terms for the selected sales order. Valid terms from the TERMS rule should be used. If an entry is found during import that doesn't match an existing TERMS rule record, that entry will be added to the TERMS rule with no other rule details. This allows the import to complete, but could add unwanted records to the TERMS rule.

f8 - TaxRate - Sales order tax percentage rate in ##.## format. The tax rate is stored in the SO Header for certain forms and reporting on closed or voided orders. For importing open orders, the tax table and tax rate will be updated from the sales order ship to record as soon as the order is edited and the UpTax button is used.

*f9 - SOType - Sales Order Type code from the SOTYPE rule. Blank or invalid import entries will use the first entry from the SOTYPE rule.

*f10 - ShipTo - Ship To organization number

f11 - Notes - Sales order header notes

f12 - Status - Sales order status code. Valid entries are 'O' (Open), 'C' (Closed), and 'V' (Void). If Status is blank or invalid, the sales order will be set to 'O'pen status.

f13 - OrdDate - This field is no longer in use

f14 - EstDate - Sales Order Load Date (MM/DD/YY format)

f15 - ConfirmTo - Sales order Confirm to field value (max 60 characters)

f16 - Remarks - Sales order Remarks field value (max 100 characters)

f17 - AcctMgr - Sales order Account Manager field value. Account Manager entries should exactly match an existing Contact record set up with the Account Manager attribute. If they do not match, the entry will not be imported.

*f18 - HeldFor - Inventory Held For name

f19 - PONum - Sales order Cust PO# field value (max 30 characters)

f20 - SP - Sales order Salesperson field value. Salesperson entries should exactly match an existing Contact record set up with the Salesperson attribute. If they do not match, the entry will not be imported.

f21 - FOB - Free on Board value for the selected sales order. Valid FOB names from the FOB rule should be used. If an entry is found during import that doesn't match an existing FOB rule record, that entry will be added to the FOB rule with no other rule details. This allows the import to complete, but could add unwanted records to the FOB rule.

f22 - ShipVia - Ship Via value for the selected sales order. Valid ship via names from the SHIPVIA rule should be used. If an entry is found during import that doesn't match an existing SHIPVIA rule record, that entry will be added to the SHIPVIA rule with no other rule details. This allows the import to complete, but could add unwanted records to the SHIPVIA rule.

*f23 - ShipWhse - Shipping warehouse name

*f24 - SoNO - Sales order number (max 30 characters)

*f25 - BillTo - Bill To organization number

*f26 - CustNo - Sold To organization number

Sales Order Import Details File Definitions

Required fields are indicated with an *

*f1 - ShipTo - Ship To organization number, must match the Ship To from the header file

*f2 - ExtPrice - Extended subtotal amount (before tax) for the sales order line. This value will be used to calculate the Line Item screen subtotal. It is also used to set the line item unit price by dividing the ExtPrice by the QtyOrd value. These values will automatically recalculate if the sales order is opened and any related fields are edited, which may change the imported amounts.

*f3 - Owner - Inventory Owner organization ID

*f4 - SPrice - Sell price in dollars, by SPriceUnit value. This price will remain in place as the agreed-to price, even after editing the imported line.

*f5 - SPriceUnit - Sell price unit to define the SPrice. Must be a valid unit of measure in the UNITS rule.

f6 - SOStatus - This field is no longer in use

*f7 - Price - Line item price used to calculate the overall order amount. The Price value used to calculate the sales order header order amount by multiplying the Price by the QtyOrd for all sales order lines. These values will automatically recalculate if the sales order is opened and any related fields are edited, which may change the imported amounts.

f8 - Notes - Line notes for the associated line item

*f9 - ExtTax - Extended tax amount for the associated line item. These values will automatically recalculate if the sales order is opened and any related fields are edited, which may change the imported amounts.

*f10 - HeldFor - Held For organization ID

*f11 - ShipWhse - Warehouse name in Adjutant for shipping location

f12 - ReqDate - Sales order line item Req Date (Required Date)

f13 - EstDate - Sales order line item Est Date (Load Date)

f14 - UnitFact - Unit of measure scaling factor

f15 - Unit - Sell unit of measure for the associated line item. Common entries are EA, FT, LBS (or any valid unit of measure established in the UNITS rule)

f16 - Taxable - Taxable flag for the associated line item (Y/N). If field is left blank, the line item will be set as non-taxable.

*f17 - SoNo - Sales order number (max 30 characters) used to link line items to the sales order header record

*f18 - Descrip - Item description for the associated line item. The description must be entered in order to display on imported records. It will not default from the associated item code.

f19 - Cost - Sell unit cost for the associated line item

f20 - QtyShp - Quantity shipped for the associated line item

*f21 - QtyOrd - Quantity ordered for the associated line item

f22 - LineNum - Line number for the associated line item. If left blank, the line number will automatically generate according to the spreadsheet order. If duplicate line numbers are found for the same sales order number, only the last record will be imported.

*f23 - Item - Item number. The item number must exist in Adjutant. The import will not import invalid items, will not create item codes, and will not default to a MISC code.

Sales Order Import Reconciliation

Reconciling imported data should begin with spot-checking several records field-by-field for complete data import. Pick records from the source file that have the most data columns filled in. Verify that all source file data fields imported correctly and display as expected.

Review the TERMS rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated. Otherwise, clean up the TERMS rule, then delete the imported records, re-scrub the source file, and re-import.

Review the FREIGHT rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated. Otherwise, clean up the FREIGHT rule, then delete the imported records, re-scrub the source file, and re-import.

Review the SHIPVIA rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated. Otherwise, clean up the SHIPVIA rule, then delete the imported records, re-scrub the source file, and re-import.

Review the FOB rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated. Otherwise, clean up the FOB rule, then delete the imported records, re-scrub the source file, and re-import.

Run the Sales Order report to select the imported records. Review the report for sales order basic details and look for any missing or incorrect records.

Sales Order Import Additional Steps

Sales Orders imported with an 'O'pen status should be edited on the Sales Order header screen and the 'UpTax' button should be used to set the correct ShipTo tax table and recalculate taxes according to the latest Adjutant tables. Sales taxes will be recalculated upon invoicing, and may generate errors if the sales order has not been updated after importing.

Review imported orders for fields that are not available on the import file, and manually update any required information that could not be imported. Some key fields to consider are:

• Production Plant
• Project and Project Phase links
• Purchase Order links
• Order header flag checkboxes such as 'Hold', 'Certs', and 'SMS Update'
• Order line item flag checkboxes such as 'Drop Ship', 'Hide Line', and 'Hide Price'

Configuration details are not available on the import file. Review any configurable items on the import file and update configuration details manually before sending to production.

Sales Order Import Database Tables

SOMAST - Each imported sales order from the Header file creates a new KEYNOH in the this table, linked to the imported SONO value. The SOMAST KEYNOH value is the link to the SOTRAN table, but you can also link using SONO.

SOTRAN - Each imported sales order detail line will create an entry in the SOTRAN table. The KEYNOH value should match the SOMAST KEYNOH value for the associated sales order. The SONO value exists in both tables so it is easy to link.