MANMAN and SQL: An Internet “Store-Front” for MANMAN and Data Warehousing

By Tim Peer

This paper highlights one company’s approach to leveraging CA/MANMAN and Internet technologies to implement an eCommerce EZorder storefront to service its order entry team and external customers. EZorder was built on top of a platform combining data warehousing, HTML/ASP, and a COM+ object written in Visual Basic. Data seamlessly flows from MANMAN to EZorder, then EZorder into MANMAN using eNVy Systems’ Warehouse Suite technology and custom applications.

A MANMAN Transaction. Customers, distributors and internal users (Customer Service) perform various MANMAN functions using the WEB browser. These functions encompass frequently accessed MANMAN transactions (MAT511, MAT510, MAT530, MAT531, MAT540 and others).

 

EZorder Adding Sales Orders.  When the customer enters a new order, products previously added to an electronic “shopping cart” are added to the sales order. For each sales order line, the user selects order quantity (validated using the pricing and availability chart and considering any minimum order quantity restrictions) and desired ship date. The availability chart displays product availability based on the MANMAN/Omar availability.

 

The customer may add any special order comments/instructions to the order. EZorder is available and online 24 hours a day, 7 days a week. Customers can update, modify, and change their order any time, on any day.

 

Sales Order status events, e.g. credit limit hold, are handled by MANMAN. These events are reported using standard MANMAN commands and are handled outside of EZorder. Two sales order numbering strategies are used, the MANMAN sales order numbers which are assigned automatically, and EZorder-generated sales order numbers that are assigned by SQL Server from SQL Server Unique Identifiers. EZorder-generated sales order numbers are easily identified in MANMAN since they all have a “Z00” prefix.

Customer Discounts. Sales order pricing via EZorder is flexible. In addition to standard pricing, preferential pricing options by virtually any sales/order criteria can be used. It can consider product, product type, product family, customer bill-to, order date and order quantity, and the MANMAN pricing table based on any number of factors. Pricing incentives are offered to certain customers based on order date. For some customers, product pricing is negotiated, and the SQL pricing table is updated by order administration  personnel on a customer-by-customer basis. The MANMAN pricing table is primarily used for pricing sales orders in EZorder.

 

Order Status. The customer may check order status anytime for any order placed by the customer via EZorder. From the Change Order page, the customer is permitted to navigate to a page containing order status and details. The customer may perform a look up by ship-date, by part (product number) or by order number. 

 

The customer is given limited capability to modify their sales order. Changes include the ability to alter the quantity on an order whose sales order line had not shipped.

  

EZorder Summary.  Processing sales orders via EZorder is preferred by customers as well as customer service representatives. Since this MANMAN site has multiple MFG plant/business units, a customer or order administration end-user can place an order through EZorder, and the order is automatically booked in the correct order entry system.  For MANMAN order-entry users, the simplicity of the screen permits rapid, accurate order entry without the cumbersome navigation of the MAT511 command.

 

In the near term, EZorder will be expanded to include Quality and Engineering functions reporting.

 

Environment. The data warehouse and associated application environment including websites consist of the following:

 

CA/MANMAN. Installed on a Compaq/VAX 7610, the business information system includes:  MFG, OMAR, G/L, Multi-plant MFG, Projects, Repetitive, Automotive, EDI, PlanMan MFG, PlanMan G/L, and Engineer.

 

Microsoft Windows 2000. Location of  eNVy warehouse databases and applications.

 

Microsoft SQL Server. The relational database install on Windows 2000. In addition to WEB access, end users create reports via MS Access, Excel and an OLAP tool. Stored Procedures simplify queries and ensure user views are consistent. Store procedures are SQL “scripts” and are stored in the database.

 

ENVy Warehouse Suite (Data Warehouse). Data are loaded into a relational database and kept up-dated from MANMAN numerous times daily. All MANMAN databases have a companion SQL Server-data warehouse.

 

Microsoft Internet Information Server. Executing on Windows 2000, IIS manages all server-side scripts (ASP) that query MANMAN data in the data warehouse.

 

File Transfer Protocol.  FTP is used to transfer a transaction file from the IIS server to the Compaq VAX system. The transfer file is loaded into an “incoming” directory. When processed, it is then archived to a “processed” directory for later audit.

 

Fortran Loader Program.  This is a stand-alone program that loads transaction records from the transfer file into MANMAN.

 

Data Collection. For MANMAN transactions, Microsoft Active Server Pages (ASP) collects and validates static information such as “product” and “bill-to.” When a sales order or other transaction is entered, the ASP validates static information from the SQL Server (Data Warehouse). Products, customers and special codes are read/selected from data warehouse tables.

 

Data Transfer. A VB application is installed as a COM+ object installed in Microsoft Component Services on the server executing IIS. Sales order data are validated prior to transfer via a VB-based COM WEB. Once validated, it is written to a sequential file.

 

The File Transfer Protocol (FTP) utility then copies the file to an “incoming” directory on the MANMAN/VAX system. A “standalone” Fortran program reads the data-file and updates MANMAN, then moves the file to an offline VAX directory.

 

Efficiencies.  The extra-net WEB applications have worked out so well that internal users (Customer Service, Engineering and Manufacturing) are entering MANMAN transactions from EZorder and “off-line” internal pages. According to the Application Manager, the WEB interface, “Has significantly reduced MANMAN transaction processing on the VAX, the time to add a sales order is far more efficient from EZorder than using MANMAN. We are finding more efficient ways of transacting day-to-day business in virtually every area of MANMAN by using the Data Warehouse and the WEB.”

 

Data Archival. Due to the extreme growth of the data in MANMAN, only 3 months of MANMAN data are retained. Data are archived (copied then deleted) to numerous (RMS) flat files and stored in a directory on the VAX. Using eNVy RMS data warehouse (archival) technology, these files are loaded directly into the data warehouse. MANMAN’s MRP RMS data file is also stored in the data warehouse daily.

 

One significant benefit from the moving of RMS data to SQL Server (using Microsoft tools such as Excel and Access) is that users generate their own reports (MGR151 and others) using the data warehouse. Before the data warehouse, on an average day no fewer than twenty MGR151 reports would execute on the VAX. Now, fewer than three MGR151 reports are executed daily, and on some days, none execute at all.

 

The Ultimate Link.  The data warehouse data is merged and consolidated with offline databases. In one case, Quality databases are updated on the VAX, data access queries to the relational (MANMAN/Quality and a home-grown Oracle Rdb) database added so much overhead to the VAX, that the queries had to be batched during non-transaction peak times. To solve this problem, the databases were replicated to the SQL Server eNVy Data Warehouse. Datamarts were generated, consolidating the MANMAN and the Rdb quality system from a single source. For the end user, regardless of the reporting/analysis tool, performance and speed of access is excellent.

 

The company originally needed to relieve MANMAN and reporting on the host computer, hence the eNVy data warehouse was implemented. After reviewing the data warehouse technology and re-assessing internal and external needs, IT and management decided to implement an eCommerce solution with links to MANMAN.  EZorder was born. New uses are uncovered daily using the eNVy Warehouse Suite technology, data archival to SQL Server, OLAP and other projects have been implemented The combination of a Warehouse Suite technology and MANMAN have created simplicity and efficiency in an environment where massive customization would have been necessary. The application manager states, “We could not have implemented EZorder without eNVy’s Warehouse Suite technology—no other technology has the speed and simplicity that it has.”

_________

Tim Peer a Senior Developer with eNVy Systems, Inc., has nearly 15 years’ experience with MANMAN and DEC/Compaq environments. Has been invited to lead data warehousing and database administration workshops at various conferences in the US and UK. For more information on the eNVy Warehouse Suite of products, please contact eNVy Systems at 4960 Almaden Expressway., Suite 330, San Jose, CA 95118, VOICE (408) 363-8896, FAX (408) 363-8897, E-Mail info@envysys.com, or visit the website at http://www.envysys.com.