Preparing a Material Master Import ✔️

Owned by Piotr - Technical Writer at ilabo (Unlicensed)
Last updated: Sept 08, 2023 by Agnieszka Wisniewska
5 min read

We created a template XLSX file that you will need to use in PackOS to import your SKUs:

 

 

 

 

  • Materials – a list of all SKUs.

  • MaterialGroups – a list of SKU groups.

  • Units – a dictionary of all possible units.

  • MaterialsRoutingSpeeds [-3.3] – expected performance for materials being produced on lines.

  • MaterialsLineConfigurations [3.3+] - configuration of line for each SKU - flow, design & validated performance and expected man-hours per volume produced

  • MaterialsPackingStructure – definitions of unit conversions active when the material is being produced.

  • GroupsRoutingSpeeds [-3.3] – expected performance for material groups being produced on lines.

  • GroupsLineConfigurations [3.3+] - configuration of line for each SKU group - flow, design & validated performance and expected man-hours per volume produced

  • GroupsPackingStructure – definitions of unit conversions active when the group is being produced.

The naming of each worksheet and each column is important and cannot be changed as they are used by the system to recognize where to look for the data.

Below, you can find a detailed description of each worksheet.

Materials Worksheet

In the 1st worksheet called Materials, you can add the names of your items and the groups to which they belong.

  • MaterialNumber – a unique identifier of the SKU and used in other sheets and in order import. If you import an SKU with the MaterialNumber that already exists in the system, it will be updated with the details provided in the file

  • MaterialDescription – an additional description of an SKU.

  • MaterialGroup – a unique identifier of the group to which the material belongs (it’s optional and can be empty). If you set it, an SKU will inherit all settings from it, including routing speeds and missing unit conversions. Note that settings set directly to the SKU have a higher priority (in case of a conflict e.g. different expected performances for the same line)

MaterialGroups Worksheet

In the 2nd worksheet called MaterialGroups, you can add the names of groups of products:

  • MaterialGroupName – is a unique identifier of the group, used to refer to the group in other sheets.

Units Worksheet

Worksheet Units contains a dictionary of all possible units used in the system.

  • UnitCode – a unique identifier of the unit, used as a reference in other sheets. It cannot be changed after it has been added.

  • UnitName – is an additional description of a unit.

  • [-3.2] IsUnitBase – is used to simplify unit conversion scenarios (when a unit is selected as a default unit). If a direct unit conversion (for example, bottles to pallets) is not available, PackOS will try to convert both units to a base unit (for example, litres), and use it as a common denominator in the conversion.
    (for example, 1 bottle = 2 litres, 1 pallet = 100 litres, therefore 1 pallet = 50 bottles)

(-3.2) Materials Routing Speeds Worksheet

Worksheet MaterialsRoutingSpeeds contains definitions for each material and each line.
If a line is omitted (and not defined in an SKU group either), PackOS will assume that this SKU won’t run on that line (for example, a configuration check won’t 'complain' about missing unit conversions).
However, currently running SKU with missing configuration is not blocked. PackOS will just use the configuration which was already active on the line before that SKU was added.

  • MaterialNumber – a reference to a specific SKU.

  • LineCode – name of the line, as displayed in PackOS

  • DesignSpeed & DesignSpeed UoM – the value and unit of the expected design performance. Can be set in any unit defined in Units as long as the packing structure for this SKU (or its group) contains appropriate unit conversions. Use notation ‘unit/h’, ‘unit/min’ or ‘unit/s’ where ‘unit’ is the UnitCode from Units worksheet.

  • ValidatedSpeed & ValidatedSpeed UoM – the same as above, but for expected validated performance.

(3.3+) Materials Line Configurations Worksheet

Worksheet MaterialsLineConfigurations contains definitions for each material and each line.
If a line is omitted (and not defined in an SKU group either), PackOS will assume that this SKU won’t run on that line (for example, a configuration check won’t 'complain' about missing unit conversions).
If Performance or Flow is omitted, PackOS will just use the configuration which was already active on the line before that SKU was added. So if a line have only 1 flow, or have the same performance for all SKUs - you can leave this fields empty

  • MaterialNumber – a reference to a specific SKU.

  • LineCode – code of the line, as displayed in PackOS Config Hub

  • LineFlow – is a reference to a line flow, defining which machines are taking part in the production of that SKU. An active flow is suggested when order for this SKU created, and automatically applied to line settings when started.

  • DesignSpeed & DesignSpeed UoM – the value and unit of the expected design performance. Can be set in any unit defined in Units as long as the packing structure for this SKU (or its group) contains appropriate unit conversions. Use notation ‘unit/h’, ‘unit/min’ or ‘unit/s’ where ‘unit’ is the UnitCode from Units worksheet.

  • ValidatedSpeed & ValidatedSpeed UoM – the same as above, but for expected validated performance

  • Manhours - number of expected manhours, needed to produce certain volume of that SKU

  • ManhoursVolume & ManhoursVolume UoM - the value and unit of the volume refered by the Manhours column
    (e.g. Manhours: 8, ManhoursVolume: 100, ManhoursVolume UoM: car - means that an order for 350 car, will be expected to consume 28 manhours)

Materials Packing Structure Worksheet

Worksheet MaterialsPackingStructure contains definitions for unit conversions for materials.

  • MaterialNumber – a reference to a specific SKU.

  • ParentUoM, Multiplier and ChildUoM contain definitions for unit conversions of different units for that specific SKU. It’s easiest to think about it as an equation:
    ParentUoM = Multiplier x ChildUoM
    For example, PAL = 4 CAR, meaning that 1 pallet contains 4 cartons.
    PackOS will use a reverse conversion automatically (1 CAR = ¼ PAL)
    Parent / Child – are names used only for the purpose of defining the conversion. It doesn’t matter which unit is used on the line/machine level (it can be either ‘Parent’ or ‘Child’).
    Note that if either ‘Parent’ or ‘Child’ unit is a base unit, and a direct unit conversion is not specified, PackOS will try to make indirect conversions as described in the Units worksheet 

(-3.2) Groups Routing Speeds Worksheet

Please see Materials Routing Speeds above. The only difference is that now MaterialGroupName corresponds to an SKU group, rather than a specific SKU. If the routing speed for a line is not specified directly as an SKU setting, it will be inherited from the group.

(3.3+) Groups Line Configurations Worksheet

Please see Materials Line Configurations above. The only difference is that now MaterialGroupName corresponds to an SKU group, rather than a specific SKU. If the performance/flow or expected manhours for a line is not specified directly as an SKU setting, it will be inherited from the group.

Groups Packing Structure Worksheet

Please see Materials Packing Structure above. The only difference is that now MaterialGroupName corresponds to an SKU group rather than a specific SKU. Packing definitions are merged together. For example: 1 PAL = 4 CAR is inherited from the group but for SKU-A: 1 CAR = 4 BOT, and SKU-B: 1 CAR = 6 BOT.

A configuration check will combine all of those conversions for you, compare with the units used for machines and lines, and display any ‘setup issues’ found in the PackOS settings.