MWF Integration

Modified on Mon, 20 Mar 2023 14:41 by Curtis Riddle — Categorized as: Import- Export Manager, Item Control, Metal Construction Industry, Order Entry, Project Management, Steel

Table of Contents [Hide/Show]

General Info
Setup and Use
   Validations
   Excel File Format
   AMS File Format
   AMS Bundle In Format

General Info¶

Adjutant is able to import files generated by the MWF (aka StrucSoft) program. It is mainly used for light gauge steel member and wall/floor panel production. The integration is able to take files with thousands of lines and create a full order within a few minutes or less. MWF Integration is intended to be used in conjunction with AMS/Eclipse, and is a necessary part of integration today.

Importing the files will create two new sales orders, attached by a project, one for assembled panels (SO Type = "S") and one for the members (SO Type = "M") of the finished panels, with the finished panels including a custom bill of materials based on the produced members. The Sales Order types do need to be defined in the rule with the codes "S" and "M".

Setup and Use¶

All files from the ConvertXLSX folder within the Adjutant folder on the ABIS FTP need to be copied to the Adjutant folder of the environment using the import. This step will need to be done by an ABIS Strategist.

The screen takes three files - an Excel spreadsheet file with two tabs (format detailed below), along with two AMS generated files. The AMS OrderIn and PartIn files are generated outside of Adjutant and imported with this process. Once imported, Adjutant will store the AMS data for the order until it is ready to be sent to production (S2P), at which time it will send out the AMS data to Eclipse and follow normal AMS integration procedures.

Adjutant is configured to use a custom BOM item called "WALLPANEL". It should be configurable, contain a BOM attribute, be produced and have the resell check box. All units should be configured in EA. Adjutant will use this item when making the Assembled Panel order. Configuration questions will be filled in from the Panel tab in the MWF Excel format (see below). The custom BOM will be filled out according to the Member tab of the MWF Excel format (see below). The sales order created will have "_PF" appended to the end.

The second sales order, called the Member Order will have "_mem" appended to the end of the sales order number. It will contain all the members for the imported job. This order will contain saved AMS information, as well as some unique configurations of the members from the MWF Excel format (see below). It is important that any member being sent from MWF exist as a produced item in Adjutant. These members, once produced through AMS, serve as the BOM for the assembled Panels.

Validations¶

* Projects need to be active with a valid phase.
* There needs to be a sales order with a number that matches the project number.
* All items need to exist in Adjutant and are validated. WALLPANEL needs to exist. Any called out member parts should exist as well.
* Order number lengths are validated - Project Number + Phase + PF/MEM should not exceed 20 character limits.
* OrderIn/PartIn files are validated against item codes as well.
* Panels named in Col. A of the Member file are validated against the Panel tab.
* OrderIn/PartIn items are validated against the Excel sheet.

Excel File Format¶

The Excel file should be divided into two tabs, a Member tab (called Member) and a Panel Tab (called Wall Panel). The import template can be downloaded by clicking this link. The downloaded file will be a .xls, but the file can be imported as an .xlsx.

Most columns will correspond to the Report Code (R-Code) of an item's configuration question and will be filled out as an answer to the configuration. It is very important that the corresponding questions be set up correctly with the right Report Codes shown below to ensure the data will flow correctly.

Columns that are not imported do not need to be filled out, but do still need the header on the template.

Member Tab:

* Column A - appended to Column E as the Panel Name and Position ID. This is placed into R-Code A (or PieceMark, Text type) of the item's configuration question. It is also used as a custom description on the item.
* Column B - This field is not imported
* Column C - This field is put into R-Code CAT (Text type), for Category
* Column D - This field is not imported
* Column E - appended to the end of Column A and used for the item description and the piece mark (R-Code A, Text type)
* Column F - This is the Quantity produced of this part on the line.
* Column G - This field is put into R-Code TY (Text type), for Type.
* Column H - This field is not imported, but does show whether part is a subassembly or not. This is a Yes/No column.
* Column I - This field is not imported, but does show the part number of the completed subassembly.
* Column J - This field is put into R-Code ME (Text type), for Member Name.
* Column K - This is the Length in inches, which is put into the Length configuration questions (R-Code B, Num type).
* Column L - This is the Weight in pounds, which is put into the Weight configuration questions (R-Code W, Num type) as well as any order weight fields.
* Column M - This field is put into R-Code HC (Text type), for Hole Code.
* Column N - This field is put into R-Code HO (Text type), for Hole List.
* Column O - This field is not imported.
* Column P - This field is put into R-Code MC (Text type), for Material Code (used for raw material reference only).
* Column Q - This is the item code that will be used for the member. It will be validated against the Item Master to ensure it exists.
* Column R - This field is not imported.
* Column S - This field is put into R-Code BU (Text type), for Bundle Code.
* Column T - This field is put into R-Code PA (Text type), for Pack Code.
* Column U - This field is put into R-Code TR (Text type), for Truck Code.

Panel Tab:

The Panel Tab brings over the WALLPANEL items and will fill out separate configurations based on the columns.

* Column A - This is used as the Panel Name. This is placed into Piece Mark (R-Code A, Text type) of the item's configuration question. It is also used as a custom description on the item.
* Column B - This field is put into R-Code CAT (Text type), for Category.
* Column C - This is the Length in inches, which is put into the Length configuration questions (R-Code B, Num type).
* Column D - This is the Height in inches, which is put into the Height configuration questions (R-Code HE, Num type).
* Column E - This is the SQFT in inches, which is put into the SQFT configuration questions (R-Code SQ, Num type).
* Column F - This is the unit weight in pounds, which is used to determine the order weights for loading and production.
* Column G - This field is not imported.
* Column H - This field is put into R-Code BU (Text type), for Bundle Code.
* Column I - This field is put into R-Code TR (Text type), for Truck Code.
* Column J - This field is not imported.
* Column K - This field is put into R-Code SS (Text type), for a Spool Sheet Link.
* Column L - This field is put into R-Code ED (Text type), for a Electrical Drawing Link.

AMS File Format¶

The AMS Files should be filled out as .txt files in the proper format. This format is comma-delimited, with a comma separating each of the fields below. If there is no information there can be a double quote ("") as a placeholder of the information. Each line should have CR/LF at the end.

OrderIn File Configuration:

orderno - the AMS order number
bundle - the AMS bundle quantity
quantity - the line quantity
length - length in inches
material - AMS material needed
message - a general message for AMS
prodcoode - the product being made. This is validated against the Adjutant Item Master and against the Excel file.
partno - the AMS part number
partoption - an AMS part option setting (typically "R")
bundlelabel - an ASCII encoded string that can be used by AMS to print custom labels onto the bundle tag
partlabel - an ASCII encoded string that can be used by AMS to print custom labels onto the part itself
kitname - AMS kit name
item_id - Adjutant will assign this and should be left blank
action - an AMS action code (typically "C")
schedate - schedule date on the AMS job, can be left blank
machine - AMS Machine Name
user1 - this is a UDF field that can be used however needed.

The following are assigned by Adjutant as needed.

loctid
plantid
adduser
holdflag = 'y'

PartIn File Configuration:

partnum - AMS assigned part number
length - length in inches
partoption - an AMS part option setting (typically "R")
toolnumber - AMS assigned tool number
refno - an AMS reference number (typically 0)
offset - offset amount in inches
permanent - T/F (typically "F")
macroname - an AMS field, can be left blank

The following are assigned by Adjutant as needed.

adddate
holdflag

Additional Information:

Rcode BU (Bundle) will fill out in the amsorder.bundle2 field.
Rcode TR (Truck) will fill out in the amsorder.truck field.

AMS Bundle In Format¶

MWF integration will also fill out the AMSBUNDLE table to pass additional information. The following will be used in the AMSBUNDLE Fields from the Member tab of the file:

Sqlstatus : 'C'
Order_ : Required and Brought in by column A
Material : Required and Brought in by column P
PCode : Required and Brought in by column Q
Bundle : 0
User1 : Optional and Empty
User2 : Optional and Empty
User3 : Optional and Empty
User4 : Brought in by column U
User5 : Brought in by column S

Similar to the AMSORDER records, they will come over as on Hold until the order is Submitted to production. The records flow to the AMS_Bundlein table as normal.