BOM Import Guide - Revision history

Amohammad at 14:58, 3 May 2024

2024-05-03T14:58:39Z

Freddy at 20:03, 15 July 2019

2019-07-15T20:03:34Z

Freddy at 14:15, 1 July 2019

2019-07-01T14:15:05Z

Freddy at 14:54, 14 June 2019

2019-06-14T14:54:32Z

Freddy at 19:22, 10 January 2019

2019-01-10T19:22:01Z

Freddy at 21:25, 3 January 2019

2019-01-03T21:25:47Z

Freddy at 21:22, 3 January 2019

2019-01-03T21:22:49Z

Freddy at 21:10, 3 January 2019

2019-01-03T21:10:09Z

Show changes

Freddy: Created page with " ==General Import Template File Notes== Template files are typically generated from their respective Import screen using an 'Export Template' button. Using the 'Export Temp..."

2019-01-03T19:53:01Z

Created page with " ==General Import Template File Notes== *Template files are typically generated from their respective Import screen using an 'Export Template' button. *Using the 'Export Temp..."

New page

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

==Configurable Item (SOC) Import Notes==

===Timing and Preparation===

The Configurable Item (SOC) Import should occur soon after the Item Master records have been imported and reviewed. Importing the SOC details is essential for testing configurable items in the sales and production processes.

===General Notes===

The import template file for the SOC Import is generated by using the Export Configurable (SOC) Items screen, detailed below. The Export Configurable Items (SOC) can be requested from any system with configurable items. The export screen has multiple filters to help with selecting the right records for building an import file. The data can also be filled in manually, but it is much easier to use existing SOC records as a reference when creating a new import file.

If you intend to use most of the field data from an SOC 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.

The safest practice is to pick an accurate example of each question type and copy the single, reviewed question down to all new customer items. This way, the same data is used for all questions.

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 SOC Import will add records if they do not exist, but can also be used to update existing records. The SOC Import will also create the Configurable Item (SOC) attribute for the associated item, if it doesn't already exist.

==Configurable Item (SOC) 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.

The SOC import file is generally built 'from scratch' or by exporting a known good list of SOC items and editing it heavily to match the customer's data. Because there is typically no single source data file from the customer, creating the SOC import file will typically involve pulling bits of data from one or more customer files to create the import. Below are some of the key data columns to review carefully prior to import:

'''Keyno: ''' Remove the data in the keyno column before importing. The keyno value is not used during the import, and it can lead to confusion when reviewing and researching SOC imports. The SOC import process will generate new keyno values upon import, and the ones on the import file will no longer relate to anything.

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

'''QuestNo: ''' Re-sort the import file by item and look for any question numbering gaps or duplicates.

'''Question: ''' Review the import file to standardize question labels for spelling and capitalization. Avoid importing things like 'LENGTH', 'Length', and 'length' for the same type of question. Make them all match before importing.

'''RCode (Report Code): ''' Carefully review the report code values for all questions and ensure that there are no mismatched codes. Review to ensure that questions of the same type all use the same report code. For example, all weight questions should use 'W', rather than some using 'W', and some using 'WT'. If the customer needs multiple versions of the same question, such as Punch Code 1 and Punch Code 2, make sure the associated report codes are consistent.

==Configurable Item (SOC) Export Screen (SOCEXPORT)==

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Configurable Item (SOC) Export

[[File:SOCEXPORT.png]]

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

'''Item Range:''' Enter an item code range to filter the SOC 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 SOC Export results by custom item number searches.

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

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

'''Owner:''' Enter an Owner name to filter the SOC Export by Owner.

==Configurable Item (SOC) Import Screen (SOCIMPORT)==

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Configurable Item (SOC) Import

[[File:SOCIMPORT1.png]]

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

==Configurable Item (SOC) Import File Definitions==

'''KeyNo''' - Keyno value of the exported SOC Question. This column should be cleared of all data prior to import, but the column position or header should not be changed.

'''Item''' - Item number in Adjutant

'''QuestNo''' - SOC question number

'''Question''' - SOC question text (maximum 50 characters)

'''QType''' - SOC question type code. Valid entries are LIST, TEXT, NUM, and CAL.

'''RCode''' - Report code for the associated question. Question 1 should always be report code A. The Length question is always report code B and generally the second question. Some other standard report codes are S=Roof Slope, Q=Square Feet, and W=Weight.

'''CalcVal''' - Enter 'Y' to indicate this is a calculated value, which sets the CALC checkbox. Enter 'N' otherwise.

'''CalcExp''' - Enter a custom formula using report codes, numerical values, and Excel-style formula codes. An example of the formula structure would be (B/2) + 100.

'''DecQty''' - Updates the Dec. Quantity for the associated question. Valid entries are 0 through 5.

'''InOnly''' - Enter 'Y' to check the Inches box for the associated question. Either the InOnly or the FtInOnly box should be checked for length questions, but not both.

'''FtInOnly''' - Enter 'Y' to check the Ft-Inch box for the associated question. Either the InOnly or the FtInOnly box should be checked for length questions, but not both.

'''MinLen''' - Enter the minimum length, if needed, for the length question. Enter 0 if there is no minimum length.

'''MaxLen''' - Enter the maximum length, if needed, for the length question. Enter 0 if there is no maximum length.

'''Inc''' - Enter the length increment value (in decimal form) to control the valid length entries. For example, to limit length entries by the 16th of an inch, enter 0.0625.

'''AItem''' - Additional item number that can be used for contract or special pricing options

'''Unit''' - UNit of measure associated with the Additional Item number

'''PAdjustable''' - Enter 'Y' to check the Price/Cost Adjust box in the Price and/or Other Adjustment Settings area

'''PriceAdj''' - Enter the dollar value for the price adjustment, or if no price adjustment, enter 0

'''QNum1''' - Price/Cost Adjustment question number. Enter the SOC question number to fill in the 'or From Q#' value for the Price/Cost Adjust fields

'''ConQuest''' - Enter 'Y' to check the Controlling Qty box

'''ShowAdj''' - Enter 'Y' to check the Display Price Adjustment box

'''OtherAdj''' - Enter the dollar value for the Other Adjustment field, or if not other adjustment, enter 0.

'''QNum4''' - Other Adjustment question number. Enter the SOC question number to fill in the 'To Q#' value.

'''QNum2''' - Pass 1 question number. Enter the SOC question number to fill in the Pass 1 'Multiply Q#' value.

'''QNum3''' - Pass 2 question number. Enter the SOC question number to fill in the Pass 2 'Adjustment by Q#' value.

'''CostAdj''' - Enter the dollar value for the cost adjustment, or if no cost adjustment, enter 0

'''Spacer1-Spacer10''' - The spacer fields are placeholder entries for future expansion. They should not be used.

'''Ans1-Ans43''' - List item descriptive name (max 100 characters) for LIST type questions. 43 list item entries are available for each question. List items display in the order according to the sequence identifier (1-43) on the import.

'''AnsPA1-AnsPA43''' - Price adjustment dollar value for the associated list item.

'''AnsCA1-AnsCA43''' - Cost adjustment dollar value for the associated list item.

'''AnsOPA1-AnsOPA43''' - Other adjustment dollar value for the associated list item.

'''Item1-Item43''' - Additional item number for special or contract pricing for the associated list item.

==Configurable Item (SOC) 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 SOC Export with filters to match the data just imported, and compare to the SOC 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.

Try to sell various configurable items on a sales order to verify that the configurations and pricing are working as expected.

==Configurable Item (SOC) Import Additional Steps==

There are no additional steps required for the SOC Import.

==Configurable Item (SOC) Import Database Tables==

'''SOCQUESTION''' - Each imported question will create a KEYNO in this table. The KEYNOH value ties to the KEYNO value of the SOCITEM table which creates the link to the item number. This table holds most of the question details, length control options, and price/cost adjustment values. The SOC Export file Keyno value refers to the Keyno value from this table. Repeated imports for the same item number will generate new SOCQUESTION KEYNO values and delete the previous SOCQUESTION KEYNO.

'''SOCITEM''' - The KEYNO value in this table links to the KEYNOH value in the SOCQUESTION table. This table holds the IKEY and ITEM values.

'''SOCANSWER''' - Each imported question will create a KEYNO in this table. The KEYNOH value ties to the KEYNO value of the SOCQUESTION table for the associated question number. This table holds the list item details. Repeated imports for the same item number will generate new SOCANSWER KEYNO values, but will NOT delete the previous values. The previous values will remain in the table, but will no longer be linked to valid SOCQUESTIONs.

'''PCXREF''' - The Configurable Item (SOC) attribute added to item records will create a cross-reference entry in the PCXREF table. The TYPE will be 'SOC'. The PARENTID and CHILDID will match the IKEY value of the associated item.

[[Category: Item Control]]
[[Category: Production]]
[[Category: Import Guides]]

@@ Line 46: / Line 46: @@
 Menu Location: Transaction>>Importers/Exporters>>Item Control>>Bill of Material (BOM) Export
-[[File:BOMEXPORT.png]]
+[[File:BOM_Export.png]]
 '''Class:''' Enter a single item class value or a range to filter the BOM Export results by Item Master class.
@@ Line 68: / Line 68: @@
 Menu Location: Transaction>>Importers/Exporters>>Item Control>>Bill of Material (BOM) Import
-[[File:BOMIMPORT1.png]]
+[[File:BOM_Import.png]]
 '''Item File Name/Browse:''' Use the Browse button to locate and select the completed BOM import template file (in XLS format).

@@ Line 116: / Line 116: @@
 '''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. Bubble level numbers should be unique for input items on the same import line. Duplicate bubble numbers for the same line will not be imported.
+'''f15 - Bubble1''' - Enter a 1 to 4 digit number to define the bubble level of input item 1. Bubble level numbers should be unique for input items on the same import line. Input items with duplicated bubble numbers for the same output item (same line on the import) will only be imported if the corresponding optional flag is 'Y', or if the import is run with 'Append Mode' checked.
 '''f16 - ExtQty1''' - Enter a numerical value (up to 4 decimals) to update the '+Qty (NS)' field for input item 1.

@@ Line 116: / Line 116: @@
 '''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.
+'''f15 - Bubble1''' - Enter a 1 to 4 digit number to define the bubble level of input item 1. Bubble level numbers should be unique for input items on the same import line. Duplicate bubble numbers for the same line will not be imported.
 '''f16 - ExtQty1''' - Enter a numerical value (up to 4 decimals) to update the '+Qty (NS)' field for input item 1.

@@ Line 122: / Line 122: @@
 '''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.
+'''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.
+'''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.

← Older revision		Revision as of 19:22, 10 January 2019
Line 1:		Line 1:

−	==General Import ~~Template File~~ Notes==	+	==General Data Import Notes==

−	*Template files are typically generated from their respective Import ~~screen using an 'Export Template' button.~~	+	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.
−	*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.

@@ Line 41: / Line 41: @@
-==Configurable Item (SOC) Import File Data Scrubbing==
+==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.
@@ Line 164: / Line 164: @@
 ==Bill of Material (BOM) Import Database Tables==
-'''SOCQUESTION''' - Each imported question will create a KEYNO in this table. The KEYNOH value ties to the KEYNO value of the SOCITEM table which creates the link to the item number. This table holds most of the question details, length control options, and price/cost adjustment values. The SOC Export file Keyno value refers to the Keyno value from this table. Repeated imports for the same item number will generate new SOCQUESTION KEYNO values and delete the previous SOCQUESTION KEYNO.
+'''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.
-'''SOCITEM''' - The KEYNO value in this table links to the KEYNOH value in the SOCQUESTION table. This table holds the IKEY and ITEM values.
+'''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.