Purchase Order Import Guide

General Data Import Notes

Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.

Purchase Order Import Notes

Timing and Preparation

The Purchase Order Import should occur after all customers have been imported, all Item Master records have been imported, and after purchase order buyers have been established.

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

General Notes

Configurable Items - The Purchase 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 costing and order totals to recalculate once the order is edited in Adjutant. Minor rounding differences, 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 no, it will default to taxable. Also, as stated above, tax calculations between two different systems will commonly be different because of rounding or calculation methods. Differences between vendor 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 Remit To location) can lead to different tax calculations. Customers should expect to carefully review and validate each imported order's tax amounts.

Purchase 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 Purchase Order data scrubbing include:

• Open vs. Closed 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 buyers, vendors, 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 correct costs are placed in the correct fields (purchase cost vs. unit cost), and that the units of measure and unit factors are placed in the correct columns.

• 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, including the exact capitalization of each letter. For example, 'Net 30', 'net 30', and 'NET 30' will be seen as three different entries. 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.

Purchase Order Import Screen (IMPORTPO)

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

File Name(Header)/Browse: Use the Browse button to locate and select the completed Purchase 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 Purchase 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 Purchase 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.

Purchase Order Import Header File Definitions

Required fields are indicated with an *

f1 - OrderNo - Purchase Order Order # field value (max 30 characters). This field can be used in the PO Header screen to search for records.

*f2 - Owner - Inventory owner name

*f3 - PODate - Purchase Order Date (MM/DD/YY format)

*f4 - ReqDate - Purchase Order Required Date (MM/DD/YY format). If left blank, the Req Date field will be set to 01/01/00.

*f5 - EstDate - Purchase Order Est Date (MM/DD/YY format). If left blank, the Est Date field will be set to 01/01/00.

f6 - Freight - Freight terms for the selected purchase 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 purchase 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 - Purchase order tax percentage rate in ##.## format. The tax rate is required if the purchase order is taxable. If the tax rate is not imported, and the order should be taxed, you must change the vendor remit to in the PO header, and then change it back in order to pull the desired tax rate from the vendor remit to record.

*f9 - POType - Purchase Order Type code from the POTYPE rule. Blank or invalid import entries will use the default entry from the POTYPE rule.

*f10 - HeldFor - Inventory Held For name

f11 - ConfirmTo - Purchase Order Confirm to field value (max 60 characters)

f12 - Remarks - Purchase Order Remarks field value (max 100 characters)

f13 - Buyer - Purchase Order Buyer. Buyer entries should exactly match an existing Contact record set up with the Buyer attribute. If they do not match, the entry will not be imported.

f14 - 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.

f15 - 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.

f16 - ShipTo - Ship To organization number to set the 'Deliver To:' value if purchase order items are not being received in the default warehouse.

*f17 - RecLoct - Receiving warehouse name

*f18 - PoNo - Purchase Order number (max 30 characters)

*f19 - RemitTo - Remit To organization number

*f20 - VendNo - Vendor organization number

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

Purchase Order Import Details File Definitions

Required fields are indicated with an *

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

*f2 - ExtCost - Extended subtotal amount (before tax) for the purchase order line. This value will be used to calculate the Header and Line Item screen order amount. 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 - HeldFor - Held For organization ID

*f4 - RecLoct - Warehouse name in Adjutant for receiving location

*f5 - ReqDate - Purchase Order line item Req Date (Required Date). If left blank, the Req Date field will be set to 01/01/00.

*f6 - EstDate - Purchase Order line item Est Date (Load Date). If left blank, the Est Date field will be set to 01/01/00.

*f7 - UnitFact - Unit of measure scaling factor for the conversion of unit cost to purchase cost. Generally unit cost/purchase cost (scost).

*f8 - Unit - Purchase 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)

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

*f10 - PoNo - Purchase Order number (max 30 characters) used to link line items to the purchase order header record

f11 - Descrip - Item description for the associated line item. The item code description will default from the associated item code if left blank.

*f12 - Cost - Purchase unit cost for the associated line item. This amount will be recalculated upon editing the line, based on the conversion of costing unit to purchase unit, using the unit factor.

f13 - QtyRec - Quantity previously received for the associated line item. The Line Items screen Rec Amt value will show the calculated received amount based on the total of received items at the purchase cost, UNTIL the line item screen is edited. After editing, the Rec Amt will calculate from the Receipt History, which will be empty for items received on the legacy system.

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

f15 - 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 purchase order number, only the last record will be imported.

*f16 - 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.

*f17 - Owner - Inventory Owner organization ID

*f18 - SCost - Purchase cost in dollars, by Costing Unit (spriceunit) value. This cost will remain in place as the agreed-to cost, even after editing the imported line.

*f19 - SPriceUnit - Purchase cost unit to define the Purchase Cost (SCost). Must be a valid unit of measure in the UNITS rule.

f20 - POStatus - This field is no longer in use

f21 - VPart - Vendor part number. If an imported vendor part number does not already exist in the C/V Part tab, editing the line item will create a C/V Part record for the new vendor part number. If the line is not edited, a C/V Part record will not be created.

f22 - Notes - Line notes for the associated line item

Purchase 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 purchase 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 purchase 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 purchase 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 purchase 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 Purchase Order Data report to select the imported records. Review the report for purchase order basic details and look for any missing or incorrect records.

Purchase Order Import Additional Steps

Purchase Orders imported with an 'O'pen status should be edited on the Purchase Order header screen and any missing fields should be completed.

Each imported lined item should be edited and saved to recalculate the order amounts, and to catch any missing data. Items with coil receipt default conversion factors should be carefully reviewed to ensure that purchase costs are accurate.

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:

• Project and Project Phase links
• Sales Order links
• Order header flag checkboxes such as 'Acknowledged'
• Order line item flag checkboxes such as 'Arrived', 'Hide Line', and 'Hide Cost'

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

Purchase Order Import Database Tables

POMAST - Each imported purchase order from the Header file creates a new KEYNO in the this table, linked to the imported PONO value. The POMAST KEYNO value is the link to the POTRAN table, but you can also link using PONO.

POTRAN - Each imported purchase order detail line will create an entry in the POTRAN table with a unique KEYNOD value. The POTRAN KEYNO value should match the POMAST KEYNO value for the associated sales order. The PONO value exists in both tables so it is easy to link.