Difference between revisions of "Coil Import Guide"

From Adjutant Wiki
 
(17 intermediate revisions by 2 users not shown)
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 29: Line 19:
 
*Stock Item flag
 
*Stock Item flag
 
*Lot# Control flag
 
*Lot# Control flag
 +
*Unit Weight (per Stock Unit, not required, but it helps with verification)
 
*Coil Receipt Defaults attribute (also known as COIL LOT or COILJUMP)
 
*Coil Receipt Defaults attribute (also known as COIL LOT or COILJUMP)
 
*Purchase Units and Stock Units should be completed for all items with the correct units
 
*Purchase Units and Stock Units should be completed for all items with the correct units
  
 
The import can automatically generate lot numbers for coils if the coil number (lot#) column is blank. Review the settings for the LOTNO counter in the 'Counters' tab of CID Maintenance prior to importing and verify that the numbering settings are correct.  
 
The import can automatically generate lot numbers for coils if the coil number (lot#) column is blank. Review the settings for the LOTNO counter in the 'Counters' tab of CID Maintenance prior to importing and verify that the numbering settings are correct.  
−
 
  
  
Line 41: Line 31:
 
Do not use the Coil Import for items with the Coil-Like Item (COIL LIKE) item attribute. The Coil Import is specifically designed for importing coil items with the cost expressed as cost per 100 lbs of material, and items with the COIL LIKE attribute may import with unexpected costs. Use the Inventory Qty Import for non-coil items.  
 
Do not use the Coil Import for items with the Coil-Like Item (COIL LIKE) item attribute. The Coil Import is specifically designed for importing coil items with the cost expressed as cost per 100 lbs of material, and items with the COIL LIKE attribute may import with unexpected costs. Use the Inventory Qty Import for non-coil items.  
  
−
Review the COILPROCESSLIST CID Setup Option setting. With this turned on, the imported Processor Name value must exactly match the company name of an organization set up with the PROCESSOR Organization attribute. If this option is off, any valid text wil import as the coil processor.
+
The Coil Import will add Item Master records for any item code on the import that doesn't already exist in Adjutant. The system-added Item Master records will be incomplete, and should be reviewed and updated to make sure the item settings are correct.  
  
−
Review the POCOILLBS CID Setup Option setting. With this turned on, the coil import assumes coil is stocked in LBS and the catch-weight calculation uses FT. This can affect the cost calculations. Best practices for coil inventory are to stock in FT, so for the majority of customers, this option should be turned off.  
+
Review the '''COILPROCESSLIST''' CID Setup Option setting. With this turned on, the imported Processor Name value must exactly match the company name of an organization set up with the PROCESSOR Organization attribute. If this option is off, any valid text wil import as the coil processor.
  
−
''coil cost''
+
Review the '''POCOILLBS''' CID Setup Option setting. With this turned on, the coil import assumes coil is stocked in LBS and the catch-weight calculation uses FT. This can affect the cost calculations. Best practices for coil inventory are to stock in FT, so for the majority of customers, this option should be turned off.
  
 +
'''Coil Cost (Stocked in FT):''' The coil cost (column f5 in the template) should be entered and imported as cost per hundred LBS (catch-weight). The import process will convert and import the cost correctly as long as the cost is expressed in catch-weight, and the linear feet and actual weight columns are correct.
 +
*If the customer data includes cost per LB, simply multiply that number by 100 to use as the cost.
 +
*If the customer data includes cost per FT, multiply the cost/FT times the Linear Feet to get a total cost for the coil. Divide the calculated total cost by the Actual Weight value to get a cost/LB. Multiply the cost/LB times 100 to get the catch-weight cost.
 +
*If the customer data only includes a total cost for the coil, divide the total cost by the Actual Weight to get a cost/LB. Multiple the cost/LB to get the catch-weight cost. See other considerations below.
 +
 +
'''Coil Cost (Stocked in LBS):''' Verify the the '''POCOILLBS''' setup option is turned on if the customer is stocking in LBS. The coil cost (column f5 in the template) should be entered and imported as cost per hundred LBS (catch-weight), which is the same procedure to use as when stocking in FT. With the setup option turned on, the import process will convert and import the cost correctly as long as the cost is expressed in catch-weight, and the linear feet and actual weight columns are correct.
 +
 +
'''Coil Cost (other considerations):''' If the source data does not include a fixed cost per unit (either LBS or FT), and only includes a total cost per coil, be very careful that the cost does not include used material. For example, if you import a coil with an original total cost of $5000, but that coil has been partially used prior to import, you would get an inflated cost per unit if you only used the remaining quantity to calculate cost/LB. If 10,000 LBS of coil originally cost $5,000, that is .50/LB. However, if you are only importing the remaining 2,500 LBS of that coil and used the total cost of $5000, you would import a cost of $2/LB. If you are using an original cost total, you need to obtain an original quantity total to calculate the correct cost per unit.
  
  
Line 54: Line 52:
 
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 Item Master data scrubbing include:
 
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 Item Master data scrubbing include:
  
−
* '''Units''' - It is common for customer item data to only include a single unit of measure value for each item. Review all item import files carefully and fill in any required unit fields with the correct unit of measure for the different transaction types (purchase, stock, sell). Setting up items with the wrong units can have dramatic effects on costing, GL postings, and other transactions throughout Adjutant. Review the unit details carefully with the customer before importing. It can be difficult to correct unit-related issues after item records have been used.  
+
* '''Date Received''' - Every imported coil should have a date received value. The import process will not auto-fill the date received if the column is left empty. If an original date received cannot be provided, work with the customer to decide on a date value to use for all empty records.  
  
−
* '''Cost''' - Verify that the current cost is entered in the AvgCost column for each item. Also make sure the stock unit of measure relates to the AvgCost value, because it will default to use the stock unit for the AvgCost. When imported, it sets the correct cost for the item for sales orders and purchase orders.  
+
* '''Cost''' - Verify that the cost per 100 LBS is entered for each item. See General Notes area above for cost calculations. Cost can be an extremely difficult item to correct once the item has been used in live transactions. Double-check the cost values carefully.
−
 
 
−
* '''Unit Weights''' - Unit weight can be critically important throughout multiple Adjutant systems and processes such as production and shipping. It is best practices to make sure that all sold items have a unit weight that corresponds to the stock unit imported.  
 
  
 +
* '''Coil Number''' - This converts to LOT#. Work with the customer to confirm whether they want to continue using the same lot numbers, manually assign new ones, or let the system auto-assign lot numbers based on the system counters.
  
  
Line 67: Line 64:
 
Menu Location: Transaction>>Importers/Exporters>>Item Control>>Coil Import
 
Menu Location: Transaction>>Importers/Exporters>>Item Control>>Coil Import
  
−
[[File:ITEMIMPORTSBS1.png]]
+
[[File:Coil_Import.png]]
  
 +
'''Item File Name/Browse:''' Use the Browse button to locate and select the completed Coil import template file (in XLS format).
  
−
'''File Name(Header)/Browse:''' Use the Browse button to locate and select the completed Item Master import template file (in XLS format).
+
'''Export Template:''' Generates a blank Coil import template file
−
 
 
−
'''Export Template:''' Generates a blank Item Master 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.
 
'''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.
  
−
'''Default Warehouse:''' Sets the warehouse location for any imported items where the WHSE column on the import file is left blank. This list is populated by Warehouse Control records that have been created.
+
'''Default Owner:''' Sets the inventory owner for all imported coil records. This list is populated with Organization records that have the Inventory Owner attribute.  
  
−
'''Default Owner:''' Sets the inventory owner for any imported items where the OWNERID column on the import file is left blank. This list is populated with Organization records that have the Inventory Owner attribute.  
+
'''Default Warehouse:''' Sets the warehouse location for all imported coil records. This list is populated by Warehouse Control records that have been created.
  
−
'''Default HedlFor:''' Sets the inventory holder for any imported items where the HELDFOR column on the import file is left blank. This list is populated from the Item Held For Codes (HOLDER) Rule Maintenance record. If a catch-all code such as 'DEF' is used, make sure the HOLDER rule is set up properly.  
+
'''Default HedlFor:''' Sets the inventory holder for all imported coil records. This list is populated from the Item Held For Codes (HOLDER) Rule Maintenance record. If a catch-all code such as 'DEF' is used, make sure the HOLDER rule is set up properly.  
  
−
'''Create All Combinations:''' Selecting this box will create separate OHF (ITEMDET) records for each OHF combination based on the owners and held for records that have been set up.
 
  
−
'''Unit Group:''' Sets the unit group for any imported items where the UNITGRP column is left blank
 
  
−
'''Unit:''' Sets ALL unit values for any imported items where any unit-related column (PURUNIT, STKUNIT, SELLUNIT, etc) is left blank
+
==Coil Import File Definitions==
  
−
'''Validate Units:''' Selecting this box will verify that all unit values on the import are valid defined units in the Unit of Measure (UNITS) rule, and that they are valid units for the selected Unit Group. For example, if FT is not defined as a valid unit in the IND Unit Group according to the rule, it will not pass verification.
+
Required fields are indicated with an *
  
−
'''Ignore Bad Vendnos:''' Selecting this box will import the VENDNO column data as-is, without verifying that the vendor number exists in Address Book.
+
'''f1 - Mill Coil#''' - Mill original coil number (20 alphanumeric character max, some special characters allowed)
  
−
'''Check for Inactive Items:''' Selecting this box will check all imported item numbers against existing item numbers that have been marked as inactive, and will reject the imported items if there is a match.  
+
'''f2 - Processor Name''' -  Outside coil processor company name (60 characters). Processor name must EXACTLY match the Adjutant Organization name is COILPROCESSLIST is turned on.  
  
−
'''Select Attributes:''' Attributes selected during the import process will be applied to ALL imported items. Review and verify that the attributes are set correctly before each import. Correcting associated attributes can be a complicated process. Attributes show up in this list based on the 'Show on Import' (Logic2) field in the Item Attributes (ITEMATTRIB) Rule Maintenance record.
+
'''f3 - Processor #''' -  Processor coil number (20 alphanumeric character max, some special characters allowed)
  
 +
'''f4 - Paint Code''' -  Paint Code value (10 alphanumeric character max)
  
 +
'''*f5 - Cost''' -  Coil cost per 100 LBS. Use the cost per 100 LBS whether stocking the coil in FT or in LBS. The coil import process will convert the cost appropriately.
  
−
==Item Master Import File Definitions==
+
'''f6 - Print''' -  Enter 'N' to indicate that a coil tag should not be printed for this coil. A blank entry is imported as 'Y'.
  
−
Required fields are indicated with an * ; Required fields that can be auto-filled by the Import screen are indicated with **
+
'''f7 - Width''' -  Coil width (in inches). The width can be used as a filter in Coil Finder, so use the simplest expression of width possible. (i.e. use 43 instead of 42.9875)
  
−
'''*f1 - Item''' - Item number/code (20 character max, alphanumeric, no spaces allowed). The following special characters are allowed (. - + / _)
+
'''f8 - HeatNo''' - Original heat number (20 alphanumeric character max, some special characters allowed)
  
−
'''*f2 - Descrip''' -  Item Description (99 alphanumeric character max)
+
'''*f9 - Coil Number''' -  Coil Lot # in Adjutant. If left blank, and the CID Maintenance LOT counter is set up, records will be automatically assigned a Lot #.
  
−
'''**f3 - UnitGrp''' -  Item Unit of Measure Group (examples are LENGTH, WEIGHT, TIME, IND). Must match a value in the UNITS rule.
+
'''*f10 - Date Received''' -  Original coil receipt date. Leaving the date blank imports an empty receipt date.  
  
−
'''**f4 - PurUnit''' -  Item purchase unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+
'''f11 - Received By''' -  Name, USERID, or initials of receiving person
  
−
'''**f5 - StkUnit''' -  Item stocking unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+
'''*f12 - Inventory#''' -  Item Master item code for the associated lot record. If the item code does not already exist, the import process will create a placeholder item code with the item and description from the file. All placeholder item codes should be reviewed and updated in Item Master.
  
−
'''**f6 - SellUnit''' -  Item sell unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+
'''*f13 - Linear Feet''' -  Total available linear feet for the associated lot record
  
−
'''*f7 - AvgCost''' -  Item’s current average cost per stocking unit
+
'''f14 - Theo Weight''' -  Theoretical weight for the associated lot record (LBS)
  
−
'''*f8 - Price''' -  Item’s price per pricing unit
+
'''*f15 - Actual Weight''' -  Total available pounds of coil for the associated lot record
  
−
'''**f9 - PriceUnit''' -  Item’s pricing unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+
'''f16 - Description''' -  Coil description override. If left blank, the item code description will be used. The entered description will be used for the associated coil tag.
  
−
'''**f10 - CostUnit''' -  Item’s costing unit associated with the Standard Cost (STDCOST) value (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+
'''f17 - Grade''' -  Coil quality/grade code (10 alphanumeric character max)
  
−
'''*f11 - StkFlag''' -  Y if the item is stocked in the warehouse, N if it is not (made to order items are not stocked)
+
'''f18 - Condition''' -  Coil condition code/description (10 alphanumeric character max)
  
−
'''f12 - SerialFlag''' -  Y if the item is serialized, N if it is not (serialized items have a serial number for every quantity of 1)
+
'''f19 - Base Metal''' -  Coil base metal code/decsription (10 alphanumeric character max)
  
−
'''f13 - LotFlag''' -  Y if the item is lot controlled, N if it is not (all coils are lot controlled).
+
'''f20 - Backer''' -  Coil backer code/description (if any) (10 alphanumeric character max)
  
−
'''f14 - Vendno''' -  The vendor code of  the item’s default supplier
+
'''f21 - Thickness''' -  Coil material thickness, in inches
  
−
'''f15 - Vpartno''' -  The default supplier’s item code
+
'''f22 - Coat''' -  Coil coating code/description (10 alphanumeric character max)
  
−
'''f16 - HeldFor''' -  Item's Held For name from the HOLDER rule. This is generally only used in locations where inventory can be held for other entities. Generally, all items should be imported with the default HeldFor name from the HOLDER rule, unless given specific instructions to create multiple HeldFor records.
+
'''f23 - Vendor''' -  Original Vendor name from the receiving PO (60 characters)
  
−
'''f17 - Whse''' -  Enter the warehouse name (not the ID code) for the primary warehouse where these items are located/produced
+
'''f24 - PO#''' -  Original PO#
  
−
'''f18 - Taxable''' -  Y if the item is taxable, N if it is not
+
'''f25 - Mill Name''' -  Original mill name (60 characters)
  
−
'''f19 - UnitW''' -  The item’s unit weight per stocking unit
 
  
−
'''f20 - Cubes''' -  Used in Warehouse Management System shipping calculations and forms (rare)
 
  
−
'''f21 - DecQty''' -  The number of decimals that should be available when entering quantities of the item on a purchase or sales order (valid entries are 0-5)
+
==Coil Import Additional Steps==
  
−
'''f22 - MiscItem''' - Y if the item is miscellaneous, N if it is not
+
'''IMPORTANT!!''' - Perform the Cost Tiers Import for every imported lot record to establish the historical receipt cost tier. The purchase order receipt process automatically builds cost tier data, but the coil import process does not create cost tier data. Failing to import cost tiers for the imported coils can have adverse effects down the road during inventory, procurement, and production processes. Follow the [[Cost Tiers Import Guide]] for details on importing cost tiers.
  
−
'''f23 - Resell''' -  Y if you plan on selling this item, N if you do not. If this field is left blank, the item cannot be added to a sales order.
+
Perform the Coil Receipt Defaults (COILJUMP) import to set the default coil receipt tag values for each coil item. Follow the [[Coil Receipt Defaults Import Guide]] for details.  
  
−
'''f24 - PackCode''' -  Item Packing Code (accepts single character letters from A - T). Can be used to group items that should be packed together by this code. (rare)
+
Run the Update Item OH/AL/OO (ITEMUPDATE) report after all coil lots have been imported. This will update the Item Master on hand displays and item search results with the imported values.
  
−
'''f25 - ABCCode''' -  Item ABC ranking code (accepts single character letters from A - Z). Can be used to manually assign ABC rankings to inventory items. The Item ABC Code Assignments screen can be used to automatically assign the ABC Code  based on usage or value, once usage and on-hand quantity information is available. (rarely used during item import)
+
Print coil tags for all imported coils.
 +
*During parallel testing, it is okay to only print coil tags for selected items that will be used for testing. All coil item types should be tested (i.e. coil for panels, coil for cees, etc.)
  
−
'''f26 - Pgroup''' -  The item’s pricing group. Customer pricing may be broken into item pricing groups; e.g. customer XYZ gets 40% off PANELS, 50% off COLDFORM, and 30% off ACCESSORIES, for component
+
*For go-live, all imported coil records must have coil tags printed.
−
sales. Leaving this field blank can cause pricing issues if pricing is based on price group breakdowns.  
 
  
−
'''f27 - ComCode''' -  The item’s commodity code. Commodity codes are used in procurement and determine which suppliers can bid on which items. Commodity Codes can also be used as filters for certain inventory reports.
 
  
−
'''f28 - BaseCost''' -  The item's base cost. This value can be useful if you are pricing your items using a cost-plus method.
 
  
−
'''f29 - ProdLine''' -  This field is no longer used
+
==Coil Import Reconciliation==
−
 
 
−
'''*f30 - ItemCat''' -  Item’s category for creating transaction categories. Item Categories are combined with Customer Categories to create a Transaction Category that is used to drive revenue and cost to the correct GL accounts. DEF should be entered for most items, unless a specific accounting model has been established based on item category. Review the revenue and cost accounting procedures carefully to determine the desired entries for each item . If left blank, the item record will not be usable until the category is filled in.
 
−
 
 
−
'''f31 - SqFt''' -  The item's square footage per stocking unit
 
−
 
 
−
'''f32 - WarLen''' -  If the item has an associated warranty length, enter it here (maximum 5 characters alphanumeric)
 
−
 
 
−
'''f33 - Produce''' -  Y if the item is produced in the plant, N if the item is not produced
 
−
 
 
−
'''f34 - SearchCode''' -  Custom text that can be used to generate a search screen for items with the same/similar search code value. For example, all items with the search code of 'Trim' will display in a search code display (by double-clicking on the item Search Code field), regardless of their item number or description. (maximum 35 characters, alphanumeric)
 
−
 
 
−
'''f35 - ProdGrp''' -  Item’s product group. Enter one of the following in the column - '''M''' (Material), '''L''' (Labor), '''E''' (Equipment), '''X''' (Expense), or '''S''' (Subcontract). All items must be assigned to one of the 5 system-defined categories that drive revenue and cost accounting.
 
−
 
 
−
'''f36 - OwnerID''' -  Item's inventory owner. This is generally only used in locations where inventory can be owned by other entities and the accounting needs to be kept separate. Generally, all items should be imported with the default OwnerID value, unless given specific instructions to create multiple Owner records, and multiple OHF combinations.
 
−
 
 
−
'''f37 - iClass''' -  Custom text field available for further identifying item groups. Can be used on certain reports or custom reports. (maximum 10 characters, alphanumeric)
 
−
 
 
−
'''f38 - StdCost''' -  Item’s standard cost per pricing unit
 
−
 
 
−
'''f39 - Coil''' - Y if the item is a coil. Coil items will automatically be assigned the Coil Receipts Default attribute. All coil items should also have the LotFlag column set to Y.
 
−
 
 
−
'''f40 - SingleIssue''' -  Y if the item is the SingleIssue Item box should be checked in the Catch Weight attribute screen. This is only saved for catch-weight items (CW column set to Y).
 
−
 
 
−
'''f41 - CW''' -  Y if the item is a catch-weight item. Catch-weight items will automatically be assigned the Catch Weight attribute.
 
−
 
 
−
'''f42 - OldItem''' - This field is no longer used
 
−
 
 
−
'''f43 - CWSPriceConv''' -  Catch-Weight conversion factor between stock unit and pricing unit. (numeric, up to 5 digits)
 
−
 
 
−
'''f44 - CWSTKConv''' -  Catch-Weight conversion factor between catch unit and stock unit. (numeric, up to 5 digits)
 
−
 
 
−
'''f45 - CWSellConv''' -  Catch-Weight conversion factor between stock unit and selling unit. (numeric, up to 5 digits)
 
−
 
 
−
'''f46 - CWPurCon''' - Catch-Weight conversion factor between stock unit and purchase unit. (numeric, up to 5 digits)
 
−
 
 
−
'''f47 - CWUnit''' -  Catch-Weight Unit
 
−
 
 
−
'''f48 - PackQty''' -  Quantity to pack. (numeric, up to 5 digits)
 
−
 
 
−
'''f49 - AllOrder''' - Enter Y to set the Allocate at Order flag in Item Master
 
−
 
 
−
'''f50 - EDICode''' - Item's EDI code (alphanumeric, up to 10 digits, rare)
 
−
 
 
−
'''f51 - UPCCode''' -  Item's UPC code (alphanumeric, up to 12 characters)
 
−
 
 
−
 
 
−
 
 
−
==Item Master 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.
 
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 Item Master Report and verify that the imported records all display with the key values filled in. You can also verify that the number of records imported matched the number of records reported. If there are fewer items reported, looks for items that did not get imported and reconcile.
+
Run the Inventory On Hand Report filtered for the imported records and verify that the imported records all display as expected. Review the lots, cost entries, and on hand value for all imported lot records.  
−
 
 
−
Try to sell various items on a sales order to verify that the correct flags and defaults are working as expected.
 
−
 
 
−
Try to order various items on a purchase order to verify that costs and units work as expected.  
 
−
 
 
  
 +
Perform several searches in the Coil Finder screen to make sure that imported coils show up as expected. Review the coil tags and make sure they print as expected.
  
−
==Item Master Import Additional Steps==
+
Compare the Adjutant cost and on hand inventory value for several records against the source data. Verify that imported coil inventory value matches the source data value. If there are any discrepancies, review, adjust, and try to correct with an updated import. If the differences can't be corrected with a new import, document the reasons for the differences and discuss with customer.
  
−
Complete the required additional imports based on how the item will be used. For example:
+
Complete different types of production with the imported coils and verify that lot records are available for production assignments. Verify that cost accounting for production usage is accurate.  
  
−
*Item Quantity (O/H) Data
 
−
*SOC Data
 
−
*Profile Data
 
−
*Min/Max Data
 
−
*Coil Quantity Data
 
−
*and many more
 
  
  
 +
==Coil Import Database Tables==
  
−
==Item Master Import Database Tables==
+
'''COILRECEIPT''' - Each imported lot creates a KEYNO in this table, linked to the imported LOT and tied to the item's IKEY.
  
−
'''ITEMMASTER''' - Each imported item creates a unique IKEY tied to the ITEM value. Most of the Item Master fields are imported to the ITEMMASTER table.
+
'''ITEMTRACK''' - Each imported lot creates a TRACKNO in this table, linked to the imported LOT and tied to the item's IKEY.  
  
−
'''ITEMDET''' - Item OHF details are created in the ITEMDET table. Each OHF combination has a unique KEYNO based on the associated IKEY (from ITEMMASTER) and the combination of Warehouse (LOCTID), Owner (VENDID), and HELDFOR.
+
'''ITEMMASTER''' - Any coil item that doesn't already exist will create a unique IKEY tied to the ITEM value.  
  
−
'''PCXREF''' - Attributes added to item records will create cross-reference entries in the PCXREF table. The TYPE will generally be the attribute name or code. The PARENTID and CHILDID will generally match the IKEY value of the associated item.
+
'''ITEMMDET''' - Any coil item that doesn't already exist will also create a unique ITEMDET record for the OHF combination specified on the import screen.
  
−
'''ATTRIBUTE TABLES''' - Attributes such as Catch Weight and Coil Receipt Defaults create their own data tables with details from the attribute jump screens. For any attribute jump screens created that contain data, you should find a separate data table, such as CWINFO (for Catch Weight) or COILJUMP (for Coil Receipt Defaults). The associated FORM (jump screen) name from the ITEMATTRIB rule is generally the same name as the data table. These tables will usually link to the item using the IKEY value. 
 
  
  
 
[[Category: Item Control]]
 
[[Category: Item Control]]
 
[[Category: Import Guides]]
 
[[Category: Import Guides]]

Latest revision as of 10:55, 8 April 2024

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.


Coil Import Notes

Timing and Preparation

The Coil Import is a quantity import, and should occur after all of the coil item records have been imported. An early Coil Import should be completed for parallel testing to create available coil inventory to test production of several types of produced coil material. Testing the coil import early can help find any data issues that show up during the production, inventory reporting, and inventory valuation testing procedures. The Item Control rules should be complete prior to importing item master records. The Item Control Setup Guide covers all of the Rule Maintenance records that should be completed prior to importing item master records.

Coil import files should be separated into multiple imports based on the combination of Owner/HeldFor/Warehouse (OFH). The import template does not have data columns for OHF data, so if coils need to be imported to specific OHF combinations, the data will need to be separated into multiple files and the OHF values must be specified at the time of import. If all coils should be stocked under the same OHF details, you can still separate the coil files by item type, department, category, or any number of item grouping details if it makes formatting or reconciling inventory values easier. However, it is not required.

The Coil Import will need to be run again at go-live to import the final available coil inventory as of the go-live date. A 'difference conversion' or 'catch-up conversion' is generally not worth it after coil quantities have been used in testing, and after purchase order coil receipts have been completed during testing. The best practice is to delete all of the coil inventory and re-import with the current, final on hand coil quantities at go-live.

Coil Items should all have the following settings (at a minimum) in Item Master:

  • Stock Item flag
  • Lot# Control flag
  • Unit Weight (per Stock Unit, not required, but it helps with verification)
  • Coil Receipt Defaults attribute (also known as COIL LOT or COILJUMP)
  • Purchase Units and Stock Units should be completed for all items with the correct units

The import can automatically generate lot numbers for coils if the coil number (lot#) column is blank. Review the settings for the LOTNO counter in the 'Counters' tab of CID Maintenance prior to importing and verify that the numbering settings are correct.


General Notes

Do not use the Coil Import for items with the Coil-Like Item (COIL LIKE) item attribute. The Coil Import is specifically designed for importing coil items with the cost expressed as cost per 100 lbs of material, and items with the COIL LIKE attribute may import with unexpected costs. Use the Inventory Qty Import for non-coil items.

The Coil Import will add Item Master records for any item code on the import that doesn't already exist in Adjutant. The system-added Item Master records will be incomplete, and should be reviewed and updated to make sure the item settings are correct.

Review the COILPROCESSLIST CID Setup Option setting. With this turned on, the imported Processor Name value must exactly match the company name of an organization set up with the PROCESSOR Organization attribute. If this option is off, any valid text wil import as the coil processor.

Review the POCOILLBS CID Setup Option setting. With this turned on, the coil import assumes coil is stocked in LBS and the catch-weight calculation uses FT. This can affect the cost calculations. Best practices for coil inventory are to stock in FT, so for the majority of customers, this option should be turned off.

Coil Cost (Stocked in FT): The coil cost (column f5 in the template) should be entered and imported as cost per hundred LBS (catch-weight). The import process will convert and import the cost correctly as long as the cost is expressed in catch-weight, and the linear feet and actual weight columns are correct.

  • If the customer data includes cost per LB, simply multiply that number by 100 to use as the cost.
  • If the customer data includes cost per FT, multiply the cost/FT times the Linear Feet to get a total cost for the coil. Divide the calculated total cost by the Actual Weight value to get a cost/LB. Multiply the cost/LB times 100 to get the catch-weight cost.
  • If the customer data only includes a total cost for the coil, divide the total cost by the Actual Weight to get a cost/LB. Multiple the cost/LB to get the catch-weight cost. See other considerations below.

Coil Cost (Stocked in LBS): Verify the the POCOILLBS setup option is turned on if the customer is stocking in LBS. The coil cost (column f5 in the template) should be entered and imported as cost per hundred LBS (catch-weight), which is the same procedure to use as when stocking in FT. With the setup option turned on, the import process will convert and import the cost correctly as long as the cost is expressed in catch-weight, and the linear feet and actual weight columns are correct.

Coil Cost (other considerations): If the source data does not include a fixed cost per unit (either LBS or FT), and only includes a total cost per coil, be very careful that the cost does not include used material. For example, if you import a coil with an original total cost of $5000, but that coil has been partially used prior to import, you would get an inflated cost per unit if you only used the remaining quantity to calculate cost/LB. If 10,000 LBS of coil originally cost $5,000, that is .50/LB. However, if you are only importing the remaining 2,500 LBS of that coil and used the total cost of $5000, you would import a cost of $2/LB. If you are using an original cost total, you need to obtain an original quantity total to calculate the correct cost per unit.


Coil 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 Item Master data scrubbing include:

  • Date Received - Every imported coil should have a date received value. The import process will not auto-fill the date received if the column is left empty. If an original date received cannot be provided, work with the customer to decide on a date value to use for all empty records.
  • Cost - Verify that the cost per 100 LBS is entered for each item. See General Notes area above for cost calculations. Cost can be an extremely difficult item to correct once the item has been used in live transactions. Double-check the cost values carefully.
  • Coil Number - This converts to LOT#. Work with the customer to confirm whether they want to continue using the same lot numbers, manually assign new ones, or let the system auto-assign lot numbers based on the system counters.


Coil Import Screen (ITEMIMPORTSBS1)

Menu Location: Transaction>>Importers/Exporters>>Item Control>>Coil Import

Coil Import.png

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

Export Template: Generates a blank Coil 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.

Default Owner: Sets the inventory owner for all imported coil records. This list is populated with Organization records that have the Inventory Owner attribute.

Default Warehouse: Sets the warehouse location for all imported coil records. This list is populated by Warehouse Control records that have been created.

Default HedlFor: Sets the inventory holder for all imported coil records. This list is populated from the Item Held For Codes (HOLDER) Rule Maintenance record. If a catch-all code such as 'DEF' is used, make sure the HOLDER rule is set up properly.


Coil Import File Definitions

Required fields are indicated with an *

f1 - Mill Coil# - Mill original coil number (20 alphanumeric character max, some special characters allowed)

f2 - Processor Name - Outside coil processor company name (60 characters). Processor name must EXACTLY match the Adjutant Organization name is COILPROCESSLIST is turned on.

f3 - Processor # - Processor coil number (20 alphanumeric character max, some special characters allowed)

f4 - Paint Code - Paint Code value (10 alphanumeric character max)

*f5 - Cost - Coil cost per 100 LBS. Use the cost per 100 LBS whether stocking the coil in FT or in LBS. The coil import process will convert the cost appropriately.

f6 - Print - Enter 'N' to indicate that a coil tag should not be printed for this coil. A blank entry is imported as 'Y'.

f7 - Width - Coil width (in inches). The width can be used as a filter in Coil Finder, so use the simplest expression of width possible. (i.e. use 43 instead of 42.9875)

f8 - HeatNo - Original heat number (20 alphanumeric character max, some special characters allowed)

*f9 - Coil Number - Coil Lot # in Adjutant. If left blank, and the CID Maintenance LOT counter is set up, records will be automatically assigned a Lot #.

*f10 - Date Received - Original coil receipt date. Leaving the date blank imports an empty receipt date.

f11 - Received By - Name, USERID, or initials of receiving person

*f12 - Inventory# - Item Master item code for the associated lot record. If the item code does not already exist, the import process will create a placeholder item code with the item and description from the file. All placeholder item codes should be reviewed and updated in Item Master.

*f13 - Linear Feet - Total available linear feet for the associated lot record

f14 - Theo Weight - Theoretical weight for the associated lot record (LBS)

*f15 - Actual Weight - Total available pounds of coil for the associated lot record

f16 - Description - Coil description override. If left blank, the item code description will be used. The entered description will be used for the associated coil tag.

f17 - Grade - Coil quality/grade code (10 alphanumeric character max)

f18 - Condition - Coil condition code/description (10 alphanumeric character max)

f19 - Base Metal - Coil base metal code/decsription (10 alphanumeric character max)

f20 - Backer - Coil backer code/description (if any) (10 alphanumeric character max)

f21 - Thickness - Coil material thickness, in inches

f22 - Coat - Coil coating code/description (10 alphanumeric character max)

f23 - Vendor - Original Vendor name from the receiving PO (60 characters)

f24 - PO# - Original PO#

f25 - Mill Name - Original mill name (60 characters)


Coil Import Additional Steps

IMPORTANT!! - Perform the Cost Tiers Import for every imported lot record to establish the historical receipt cost tier. The purchase order receipt process automatically builds cost tier data, but the coil import process does not create cost tier data. Failing to import cost tiers for the imported coils can have adverse effects down the road during inventory, procurement, and production processes. Follow the Cost Tiers Import Guide for details on importing cost tiers.

Perform the Coil Receipt Defaults (COILJUMP) import to set the default coil receipt tag values for each coil item. Follow the Coil Receipt Defaults Import Guide for details.

Run the Update Item OH/AL/OO (ITEMUPDATE) report after all coil lots have been imported. This will update the Item Master on hand displays and item search results with the imported values.

Print coil tags for all imported coils.

  • During parallel testing, it is okay to only print coil tags for selected items that will be used for testing. All coil item types should be tested (i.e. coil for panels, coil for cees, etc.)
  • For go-live, all imported coil records must have coil tags printed.


Coil 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 Inventory On Hand Report filtered for the imported records and verify that the imported records all display as expected. Review the lots, cost entries, and on hand value for all imported lot records.

Perform several searches in the Coil Finder screen to make sure that imported coils show up as expected. Review the coil tags and make sure they print as expected.

Compare the Adjutant cost and on hand inventory value for several records against the source data. Verify that imported coil inventory value matches the source data value. If there are any discrepancies, review, adjust, and try to correct with an updated import. If the differences can't be corrected with a new import, document the reasons for the differences and discuss with customer.

Complete different types of production with the imported coils and verify that lot records are available for production assignments. Verify that cost accounting for production usage is accurate.


Coil Import Database Tables

COILRECEIPT - Each imported lot creates a KEYNO in this table, linked to the imported LOT and tied to the item's IKEY.

ITEMTRACK - Each imported lot creates a TRACKNO in this table, linked to the imported LOT and tied to the item's IKEY.

ITEMMASTER - Any coil item that doesn't already exist will create a unique IKEY tied to the ITEM value.

ITEMMDET - Any coil item that doesn't already exist will also create a unique ITEMDET record for the OHF combination specified on the import screen.

Retrieved from "http://www.abiscorp.com/faq/index.php?title=Coil_Import_Guide&oldid=4883"