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.

Bill of Material (BOM) Import Notes

Timing and Preparation

The Bill of Material (BOM) Import should occur soon after the Item Master records have been imported and reviewed. Importing the BOM details is essential for testing production items.

General Notes

The import template file for the BOM Import can be generated two different ways:

• The Bill of Materials (BOM) Export screen, detailed below can be requested from any system with BOM data that matches the general BOM structure that you need. The export screen has multiple filters to help with selecting the right records for building an import file.

• Use the 'Export Template' option from the Bill of Materials (BOM) Import screen to create a blank template.

If you intend to use most of the field data from an BOM Export file, review the data critically before attempting to associate it with a customer's item file. We do not want to propogate bad data.

Be extremely careful when copying data in Excel that you don't accidentally increase values down the page. Use the correct procedures in Excel to copy, rather than copy and sequence, data down columns.

The BOM Import will add records if they do not exist, but can also be used to update/replace existing records. The update function will replace all matched BOM record details with the data in the latest import file. Be aware, that if you have an existing BOM record with 10 input items and you import a new file with only a single input item, it will remove all previous input items.

The BOM Import will also create the Bill of Material (BOM) attribute for the associated item, if it doesn't already exist.

Bill of Material (BOM) 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.

Item: When copying data into the file, review the item number fields carefully. Carefully review any items that are being renumbered in Adjutant and make sure the item numbers are correct. Review for any transposed numbers or faulty copy and paste issues.

RevLevel: Review any entries in Rev Level carefully. Understand that the rev level is used to match against multiple iterations of the same BOM record, and if entered incorrectly, may create a new BOM record.

RCode (Report Code): Carefully review the report code values for all input items and ensure that there are no mismatched codes.

Bill of Material (BOM) Export Screen (BOMEXPORT)

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Bill of Material (BOM) Export

Class: Enter a single item class value or a range to filter the BOM Export results by Item Master class.

Item Range: Enter an item code range to filter the BOM Export results by all item numbers that fall between the from and to values.

Item Like Block: Enter up to 6 item-like searches to filter the BOM Export results by custom item number searches.

Commodity Code: Enter a single commodity code or a range to filter the BOM Export results by Item Master commodity code.

Held For: Enter a Held For name to filter the BOM Export by Held For.

Owner: Enter an Owner name to filter the BOM Export by Owner.

Limit XLS Export: Use this button to select the desired BOM fields that will be selected for export. This feature can help limit the columns exported so you can fit the maximum number of input items on the export file.

Bill of Material (BOM) Import Screen (BOMIMPORT)

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Bill of Material (BOM) Import

Item File Name/Browse: Use the Browse button to locate and select the completed BOM import template file (in XLS format).

Export Template: Generates a blank BOM Import 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: Generates a TXT file of errors that will prevent the import from completing.

Import Good Records: If selected, only valid records will be imported.

Append Mode: If selected, imported input items will be added to existing, matched BOM records according to rev level.

Bill of Material (BOM) Import File Definitions

Required fields are indicated with an *

*f1 - Item - Item number in Adjutant that the BOM is attached to

f2 - RevLevel - BOM revision level (optional), visible in the BOM Details tab. Each unique rev level will create a unique BOM record. When using the 'Append Mode', the rev level will be used to match an existing BOM. If there is no match found, a new BOM with the new rev level will be created.

f3 - IsPhantom - Enter 'Y' to check the 'Phantom' box in the BOM Details tab

f4 - INote - BOM-level input note. There is not a way to import detail-level input notes.

f5 - ONote - BOM-level output note. There is not a way to import detail-level output notes.

f6 - Custom - Enter 'Y' to check the 'Custom' box in the BOM Details tab

f7 - Current - Enter 'Y' to check the 'Current' box in the BOM Details tab

f8 - Locked - Enter 'Y' to check the 'Locked' box in the BOM Details tab

f9 - Route - Enter a route name from the Route Maintenance screen to associate the BOM with a route. The route name must exactly match an existing route name.

*f10 - Qty - BOM Output quantity. The Output item is automatically assumed to be the Item from field f1.

*f11 - Item1 - Input Item 1

*f12 - Qty1 - Input item 1 quantity. Enter 1 for input items that should scale according to an associated report code. The input quantity assumes the stocking unit of measure for input item 1.

f13 - Scale1 - Enter 'Y' to check the 'Scale' box for input item 1.

f14 - Optional1 - Enter 'Y' to check the 'Optional' box for input item 1.

f15 - Bubble1 - Enter a 1 to 4 digit number to define the bubble level of input item 1. Items can have the same bubble level to group certain inputs.

f16 - ExtQty1 - Enter a numerical value (up to 4 decimals) to update the '+Qty (NS)' field for input item 1.

f17 - ContPc1 - Enter 'Y' to check the 'Continuous' box for input item 1.

f18 - ATPP1 - Enter the percentage value (up to 2 decimals) to update the first value of the 'AT% (=/-)' field for input item 1.

f19 - ATPM1 - Enter the percentage value (up to 2 decimals) to update the second value of the 'AT% (=/-)' field for input item 1.

f20 - RCode1 - Report code associated with input item 1.

• The input item columns repeat in the same pattern through input item 24.

• The BOM Import can handle up to 40 input items per BOM, but Excel has a 254 column limit. Columns for fields that are not needed can be removed from the import template to make room for additional input items.

• For example, if you only need Item, Qty, Scale, and RCode for all of the input items, you can easily customize the columns to fit 40 unique input items on the spreadsheet. (40 items X 4 columns each = 160 columns)

Bill of Material (BOM) 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.

Run the BOM Export with filters to match the data just imported, and compare to the BOM Import file used. Verify that the number or records exported matches the number of records that should have been created/updated. Compare the export and import details for any any discrepancies.

Submit various BOM items to production to verify that the BOM details are working as expected.

Bill of Material (BOM) Import Additional Steps

There are no additional steps required for the BOM Import.

Bill of Material (BOM) Import Database Tables

BOMH - Each imported BOM record will create a unique KEYNO in this table, linked to the primary item IKEY. This table contains the BOM details and BOM-level notes.

BOMLIST - Each input and output item will be added to this table and assigned a unique KEYNO. Each input and output item will be linked to the BOMH table with the KEYNOH value that links to the BOMH table KEYNO value. The PIKEY value also refers to the IKEY value of the BOMH record.