Auto Create POP Orders From Excel

Solution Categories

Automotive | Analytics | Chemical Manufacturing | Collaboration | Computer & Electronic Equip. Manufacturing | Customer Relationship Management | Construction | eCommerce | Education | Financial Management | Finance, Insurance and Real Estate | Human Resource Management | Food & Beverage Manufacturing | Integration | Government | Inventory Management | Life Sciences | Logistics | Machinery Manufacturing | Mobility | Metal Fabrication | Portals | Not for Profit | Project Management | Other Manufacturing | Sales | Other Services | Paper & Printing | Professional, Scientific, and Technical Services | Retail Trade | Telecommunications | Transportation and Warehousing | Utilities | Wholesale Distribution

More Information

Auto Create POP Orders From Excel

Overview:

Your company generates a production plan for top-level items on an Excel spreadsheet. You would like to automate the creation of Macola Production Orders, including required sub assemblies, based on the data in the planning spreadsheet.

Proposed Solution:

A MS Access 2000 program will be written to read the Excel Planning worksheet, present the proposed orders to the user and automatically create all required Production Orders in Macola.

Detail of Solution:

The Excel Planning worksheet (blank machine weekly schedule1.xls) that will be read by this application will be in a template format.

The file will be named "blank machine weekly schedule1.xls" and located according to the settings in the 'Admin' screen.

The application will present the user with a startup screen.

Admin
The admin command button will expose a screen.

The name and filepath to the Excel planning worksheet are entered by the user.

Generate POP Orders

The application will scan the Excel Planning Worksheet column 'O' looking for the Due Date entries. When a Due Date is located, the Item Number and Quantity will be retrieved from that row.

The Generate POP Orders command will then present a screen.

The top section contains the order details for the Parent Item(s); the lower section contains order details for required subassemblies.

Data fields:

Ord Date

Default is current date; this will become the Production Order date on generated orders
Item No

The Parent item number or Subassembly item number
Item Desc

IMITMIDX_SQL.Item_Desc_1 (Item Master) for the Item
Qty

The total quantity to be produced.

For Parent Items, the quantity is as entered on the Excel spreadsheet.

For subassembly items, the quantity is the sum of all identical subassemblies required to satisfy the Parent Items where 'Process' has been checked.
Due Date

For parent items, the Production Order Due Date is drawn from the Excel spreadsheet; for subassembly items, the due date will be the earliest due date found on a Parent item for which this subassembly is required.
Mach No

The Machine Number on which this production order should run (entered by the user on Excel spreadsheet, but may be edited in this interface). The Machine No will be written to the POP Order Header User Defined Field 1.
Lot Size

The Lot Size for each Production Order. The Lot Size will be drawn from the Item Location 'Economic Order Qty' field and may be edited by the user. A separate production order will be created for each lot quantity within the total quantity ordered.

Note: The Qty ordered must be evenly divisible by the Lot Size
Process

When the 'Process' checkbox is checked Production Order(s) will be generated for this line item. The 'Process' checkbox is selected by default but the user may choose to unselect this checkbox.

Note: Unselecting or selecting a 'Process' checkbox on a Parent Item will cause the Sub Assembly quantities to be recalculated.
Print

When the 'Print' checkbox is checked Production Order(s) Reports will be generated for this line item. The 'Print checkbox is selected by default but the user may choose to unselect this checkbox.

Vermed uses one of two Production Order reports. The specific Production Order Report to be printed will be drawn from the Item Location 'PO Multiple' field (IMINVLOC_SQL.PO_Mult).

  • The existing Production Order report will be recreated to allow it to be printed from within the Access program.
Validation

Items must be 'Valid' to be processed. If an item is not validated, the reason will be displayed in this field (see Validations below)

When selection is complete, user will click the 'Send To Macola' command to create 'Released' Macola Production Orders.

  1. One or more production orders will be created for each Parent Item according to the lot size and quantity ordered.
  2. One or more production orders will be created for each Subassembly Item according to the lot size and quantity ordered. These orders will be created as subassembly 'parent' orders and not as dependent subassembly orders.
  3. Preferences:
    • Create WIP: 'N=Allocate Components with no G/L Distributions" will be used
    • Report Production: "P=Parent Order Level" will be used

Machine Detail:

The same item may have a different Bill of Materials depending on the machine on which this specific order is being produced. The user may specify which machine to use for each order (see Detail screen above).

  • When a specific Machine No is entered, the application will look for an alternate item number comprised of the base parent number plus the machine number:

    • Example: Item No 120 running on Machine 02
    • The application will search for Item 120-02
    • The Production Order BOM for Item 120 will be replaced with the BOM for Item 120-02
    • If no alternate item or alternate item BOM is found, the standard BOM for the parent item will be used.
Validations:

The following validations will be applied to each item. An order may not be processed unless it is fully validated (note exception 4A).

  1. Item exists and is active in Item Master
  2. Item exists and is active at the default item location
  3. BOM exists for finished goods item
  4. BOM exists for indicated finished goods item - Machine No
    • Note: This validation is a warning only. The item will still be considered valid and the regular finished goods BOM will be used as a default.
  5. Due Date is greater than or equal to today

A Production Order will print to the default printer for the workstation for each order where the 'Print' checkbox is checked.

POP Orders Report

When the user has completed sending Production Orders to Macola, the 'POP Order Report' may be selected. This report will show all Parent and Subassembly details as shown above, and will include the Macola POP Order Number that was assigned to each planned order.

  • The report will be run to screen, from where it may be printed to printer if desired.
General Information
  • Live Implementation: 1-5
  • Customer References Available: No
  • Maintenance Plan Available: No
  • Support Plan Available: No
  • Technical Documentation: Yes
  • User Manual Documentation: No
  • Marketing Literature: No
  • Suggested Retail Price: $3,950.00
  • Available for Resale: No
  • Primary Contact Person - Sales: This e-mail address is being protected from spambots. You need JavaScript enabled to view it - Tel: 800-468-0834 Ext.2650
  • Primary Contact Person - Technical: This e-mail address is being protected from spambots. You need JavaScript enabled to view it - Tel: 978-474-4900
  • Web-Site/URL: www.exactamerica.com
  • Information Last Updated: 2006-10-13
Languages Exact Product Line
  • English
  • Exact Macola Progression
Development Platform Modules Impacted
  • Other
  • Production Order Processing
Database Supported Version
  • MS SQL 2000
  • Pervasive PSQL
  • N/A
Industries Horizontal Focus
  • Automotive
  • Chemical Manufacturing
  • Computer and Electronic Equip. Manufacturing
  • Construction
  • Education
  • Finance, Insurance, & Real Estate
  • Food & Beverage Manufacturing
  • Government
  • Life Sciences
  • Metal Fabrication
  • Machinery Manufacturing
  • Not for Profit
  • Other Manufacturing
  • Other Services
  • Paper & Printing
  • Professional, Scientific, and Technical Services
  • Retail Trade
  • Telecommunications
  • Transportation and Warehousing
  • Utilities
  • Wholesale Distribution
  • Analytics
  • Collaboration
  • Customer Relationship Mgmt (CRM)
  • e-Commerce
  • Financial Mgmt
  • Human Resource Mgmt (HRM)
  • Integration
  • Inventory Mgmt
  • Logistics
  • Mobility
  • Portals
  • Project Mgmt
  • Sales