Novell is now a part of Micro Focus

Developing the Vendor Alliance Tracking Program

Articles and Tips: article

KENNETH J. JENSEN JR.
Research Engineer
Systems Research Department

01 May 1994


Novell's Vendor Support Alliance Group used Visual Appbuilder to create a vendor tracking database. The group uses the database to track contacts and related information about companies in the vendor support program. This DevNote tells how Visual AppBuilder helped the group implement the database application faster than they could with other development tools. It also shows features of the vendor tracking database program.

Acknowledgements

Thanks to Steve Bowen, Perri Bird, and Ed Firmage for contributing to this DevNote.

After Novell purchased Serius Corporation last year, many groups within Novell began looking at how they could use Serius' development tool, renamed Visual AppBuilder, to accelerate internal software development projects. One of the first applications of Visual Appbuilder within Novell was a vendor tracking database. Novell's Vendor Support Alliance Group, managed by Perri Bird, uses the database to track contacts and related information about companies in the vendor support program.

Novell Vendor Alliance Group

Novell's service strategy parallels its product delivery and development approach, using third parties to provide service and support within the networking industry. These third parties require education, service products, tools, programs and telephone escalation support. Novell provides this support to vendors through support programs such as Original Equipment Manufacturers (OEMs), Technical Support Alliance (TSA) and Technical Support Alliance Network members (TSANet).

Some of the current TSA and OEM companies are:

3Com, Adaptec, ALR, Apple, Banyan, Borland, Cisco Systems, Compaq, Digital Equipment, Hewlett Packard, IBM, Intel, Lotus, National Semiconductor, NCR, Oracle, Storage Dimensions, Sun Microsystems, Ungermann-Bass, Wordperfect.

Novell provides "backup" or escalation support to vendor partners through direct contact with Novell technical support engineers. Multivendor support issues can be complex, so Novell has established formalized relationships with its partners. In this way Novell can deliver all that is necessary for complete problem resolutions. As mentioned, the first objective is to enable and empower others to be self sufficient. To accomplish this objective, Novell provides the Alliance partners with the following:

  • Assigned technical & business contacts.

  • Novell products for duplication of problems and training of support personnel.

  • Novell information tools (NSEPro, Application Notes, Developer Notes, and so forth).

  • Novell education manuals, computer-based training courses, discount coupons, and instructor-led courses.

  • Novell certification programs such as CNE, and product programs like Yes and Tested and Approved.

Another objective of the Vendor Alliance Group is to reduce support costs for Novell's customers, vendor partners, and for Novell itself. Many other issues are initiated in the support arena, and must be tracked through completion in order to keep high levels of customer satisfaction. The Novell Technical Support Alliance is highly regarded by Novell's customer base and it provides direct benefits to Novell support personnel.

One element of the program is a product exchange. Currently Novell maintains a very large (over $1 million) on-loan inventory of product for training and problem duplication in its support labs. The Vendor Alliance Group has the responsibility to maintain an accurate record of these products. To date, they have used a paper/spreadsheet/database method of tracking and scheduling.

Some of the other information that is gathered deals with the objectives listed above: names, numbers, contacts, associated costs, how much and when the training is accomplished, and which products are certified.

Defining the Problem

Although Novell has a good database to maintain the customer incident tracking, there was a need to track exchanged products, information databases, and a much more extensive contact list. In some cases, certain vendors required immediate attention to requests, and it was becoming difficult to track request status and the action items relating to the request. Some of the tasks went unfinished and others were completed twice by different people. Some vendors were participating in multiple programs simultaneously, making it difficult to reference all information related to a single vendor.

Currently, the data for these partners is kept in separate spreadsheets and word processor files, macros, and applications. It was a major effort to have one person track all of the data, so several people were needed to keep the data current. Because Novell's Vendor Alliance Group has been successful handling many of these tasks, it was decided to focus Novell Services support programs through one group. Now services are all under the same service model, requiring the same deliverables and information from the Alliance Group. Novell works with hundreds of different vendors through the varied support programs, and it was imperative to track all of the business transactions with each vendor. Without a good database application information structure, referencing data by vendor was becoming impossible and action items were not being prioritized as the Novell programs continued to grow. In addition, an extensive paper trail was causing a backlog with the files. If a shipment needed to be tracked, a report provided, or a database updated it required a lot of work and time.

Exploring Solutions

The Vendor Alliance Group had explored and used varying combinations of databases and applications to track the data for its partners. The Group had taken the traditional route of looking to outside sources, third-party custom developers, and developing their own package in-house.

In 1991 there were only 11 TSA members and no significant number of OEM partners in place, so there was no real need for a formalized database. Tracking the data came down to a couple of spreadsheets and word processor files. The Vendor Alliance Group looked at increasing the call tracking database that was currently being used to help collect customer information. They could not expect a quick turn-around due to many of the factors involved in custom programming, time and other priorities. They also looked at some support tool applications, inventory systems, and other action item databases, but there was no application that met all the requirements necessary to accomplish the task. The Vendor Alliance Group then explored some of the 4GL programming tools like Borland's ObjectVision and others. The idea to write a database with these resources looked like their best alternative, but time was a factor. It looked like it was going to become an after hours project, using volunteers to do the programming, budgeting for outside services, and paying overtime for the employees. Each of the options had its merits and drawbacks. The new database was to come on-line within two years of initiating the project but due to length of the project and change in business direction, the project was canceled in 1993.

In 1993 Novell Services was going through a change in its customer tracking database. The Vendor Alliance Group considered integrating their needs with the customer database project for technical support functions. The approval was made to go ahead with the changes, however the timeline for implementation of the second phase was 6 months out. Once again resources and time became critical in the decision-making process.

In the spring of 1993, there did not appear to be a solution to the Vendor Alliance Group's problem. Their existing database was inadequate, and all alternatives they had investigated required too great an investment in development, and too much to time to implement.

AppWare Systems Group Formed

Novell acquired Serius Corp. in June of 1993 along with another company, Software Transformation, Inc. (STI) the two companies became Novell's AppWare System Group. The application building tool that caught the eye of the Vendor Alliance Group was Visual AppBuilder. They could see that the product was capable of producing applications that could be implemented quickly, tested, changed in a short time. The AppWare Systems Group looked at the requirements document and resources needed to complete the project. They then decided to create the application using Visual AppBuilder.

System Requirements for the Tracking Program

The Vendor Alliance Tracking program was developed using Visual AppBuilder with the stored procedure capabilities available in the Oracle Relational Database Management System (RDBMS) procedural option. The stored Procedures and Functions are objects that logically group a set of SQL and other PL/SQL (Oracle Programming Language/Structured Query Language) programming language statements together to perform a specific task. Functions and procedures are the same except that functions always return a single value to the caller, while procedures do not return values to the caller. The RDBMS utilizes a client/server architecture where the client executes the application that accesses database information and interacts with the user through the keyboard, screen, or mouse. The server portion executes the Oracle RDBMS software and handles the functions required for concurrent, shared data access to an Oracle database. The Oracle transaction processing option and procedural option of the RDBMS were also implemented on the server. The software used for the server is OracleWare NetWare Edition V1.0 and consists of NetWare 3.12 and Oracle Version 7, which is an NLM-based RDBMS engine. The NLMs required for the server installation are loaded using the ORALOAD.NCF file: (CLIB, MATHLIB, DIRECTFS, AFTER311, CORE, ORACLE7). The ORACLE7.NLM autoloads ORADSOP, SPXSRV and SQLSPX. The server also requires SQL*Net SPX for Netware v1.1.1.4.

Figure 1 shows the hardware and software components which make up the Client/Server environment.

Figure 1: Vendor Alliance tracking program environment.

The hardware requirements needed for the file server are dependent upon the usage or types of transactions, number of users, and size of the database. The configuration recommendations for running OracleWare are as follows:

Server Requirements

Memory. In addition to the memory requirements for NetWare 3.12, OracleWare requires 8MB for Oracle7 NLMs, and between 150-1400 KB for each client connection dependent upon the client usage.

Disk Space. Installation takes place from CD-ROM to hard disk and requires 90MB for Oracle files, not including the database contents. The Oracle on-line documentation (optional) requires an additional 72MB.

Connectivity. Novell requires the client to use NETX, VLM, or OS/2 Requester to be loaded to log in to the NetWare server. Oracle does not require you to be logged in or attached to access the Oracle database, as long as the Oracle NLMs are loaded.

Client Requirements

Memory. For use with DOS SQL*Net SPX 82 KB in real mode, 42 KB in addition for protected mode. Four megabytes of system memory is recommended when using Windows SQL*Net SPX and Windows 3.1. SQL*Net SPX is the Oracle network interface that allows Oracle tools or applications running on one computer to access, modify, share, and store data on Oracle databases on different computers and the server. It specifically enables clients to communicate with Oracle7 Server for NetWare using the IPX/SPX protocol.

Disk Space. Ninety KB for DOS version of SQL*Net and 31 KB for Windows version.

CPU. 80386 processor or higher.

Connectivity. MS-DOS Version 5.0 or later or DR DOS version 6.0 or later. NETX.EXE version 3.32 or higher or NetWare DOS requester VLMs v1.02 or higher.

The requirements for Visual AppBuilder require the client to be a Macintosh with System 7 or Intel system with Windows 3.1. Visual AppBuilder provides software components that are linked together. These procedures, called AppWare Loadable Modules (ALMs) are used to build an application that meets specific needs and objectives. The ALMs enable developers to quickly build portable software applications, modify the results and create custom programs. The ALMs that are composed of an objects and its associated functions can range from graphical utilities, spreadsheet modules, multimedia engines, (SQL) client-server connections. Each ALMs object and function is represented by icons and the application is constructed by combining different ALM icons.

The Oracle ALMs available from Novell are shipped with the Visual AppBuilder product. Development of the application was accomplished using the Windows version of Visual AppBuilder; however, the Oracle ALMs used with Windows are also available for the Macintosh. Oracle connectivity consists of two Oracle ALMs, Oracle Connection ALM and Oracle SQL ALM. In Figure 2 you can see the two ALMs from the Object & Function Palette for Windows.

Figure 2: Oracle Connection and Oracle SQL ALMs.

The Oracle Connection object setup is used to specify user name and password information (Figure 3). The user name and password must reside in the text objects specified to the left in (object palette). The Oracle SQL object is used to specify which Oracle Connection object the SQL statement will be executed. The text object which contains the SQL statement is specified here.

Figure 3: Oracle Connection setup.

The bind variables and other objects that receive the data from the database are also set in the Oracle SQL object (Figure 4).

Figure 4: Setting up the Oracle SQL object.

These two Oracle ALMs consist of functions that allow you to connect to the database, perform transactions, and execute SQL statements and stored procedures. The functions for the Connection ALM include Connect, Commit, Error handling, Disconnect, Rollback and Status (see Figures 5, 6, and 7).

Figure 5: The Oracle Connection ALM's Connect function.

Figure 6: The Oracle Connection ALM's Commit function.

Figure 7: The Oracle Connection ALM's Disconnect function.

The functions for the SQL ALM include Execute, Fetch, Flush, Status, and Fetch to Table (see Figures 8, 9, 10). In addition to the application logic developed in Visual AppBuilder, stored procedures and database triggers were also used to aid in data validation and referential integrity checking.

Figure 8: The Oracle SQL ALM's Execute function.

Figure 9: The Oracle SQL ALM's Fetch function.

Figure 10: The Oracle SQL ALM's Status function.

The Vendor Alliance Tracking Program

The purpose of the application is to track information about companies, contacts and products involved in Novell's vendor support program. The program retains information about each company and the contacts associated with the company, whether they be program managers, technical personnel, or corporate level contacts.

Once these items are in place, the program allows users to record information about products exchanged between Novell and its vendors. Users enter dates when inbound products from vendors were received, when they were returned, location, product description, and estimated value. The program also records information about outbound Novell products (Figure 11).

Figure 11: Outbound products window from Vendor Alliance Tracking program.

The program validates part number and revisions against the Novell master parts list. The majority of the work in the system involves the management of calls and action items. The vendor support team is constantly in contact with vendors in solving problems and issues. This activity requires the management of action items and their priorities and assignments.

An active issue may require several action items of differing priorities and assignments to different people. The call action item screen (Figure 12) and its associated reports help the vendor support team keep track of what has been completed.

Figure 12: Call Action Items window from Vendor Alliance Tracking program.

When an item must be addressed, the person assigned to the task can inquire on the status and track the action item. Users of the system have the option of performing screen queries or generating reports obtaining information about action items by company, by open or closed status or by assigned employee. The program reporting features were written in Oracle Data Browser to import management data into spreadsheets and to generate reports.

Adding a Company

Users of Visual AppBuilder organize their projects into sub-projects or "subjects". Subjects allow a large, complex program to be broken down into reusable, smaller pieces. To connect subjects, the user aliases objects from one subject into another. Visual AppBuilder represents program logic in the form of function icons organized in chains that pass control from left to right. Data flows from top to bottom. A new project is opened from the File menu and is called TSACOMP.PJT. One of its sub-projects handles the process of inputting data in the Oracle database.

In the complied application (Figure 13), the user adds information to the database by entering the data in the fields (Company, Street, City, etc.) and clicking the Add Button.

Figure 13: Company data entry window from Vendor Alliance Tracking program.

The function chains that give the button its behavior (Figure 14) represent the following sequence of operations. When the user presses the Add button, a subroutine called AddComp validates the contents of the required text fields. Prior to actually examining their values, the subroutine sets a Boolean ("Error Flag") to zero, If an error occurs during validation the subroutines that validate the data change the value of this flag so that the AddComp routine can change program flow accordingly.

Figure 14: Add Company function chain.

The next function in the chain labeled CheckCompR, triggers another subroutine that validates the date. If the validation is successful, the Error Flag remains unchanged. If the date validation subroutines finds an error in the data, it sets the flag to a non-zero value. After calling the CheckCompR subroutine, AddComp examines the value of the Error Flag. If it is equal to zero, the function chain proceeds; otherwise it is terminated. The subroutine functions labeled GetCompSeq (Get Company Sequence Id) and ChkUnqComp (Check unique company name) ensures that the company ID and name are unique. If so, a query statement AddCompQry is executed to add the company to the Oracle database.

Query a Company

To locate information about a company, the user enters the company name in the Company field and clicks the Query button (Figure 13). Doing so triggers the function chain illustrated in Figure 15. The first function in the subroutine, "Compare Text", evaluates the current selection in the Contract list, in order to build part of the following query statement, then executes the query (QryCompQry). If this operation is successful, the function chain proceeds to the QryCompLoop (the function chain attached to the object called QryCompQry) that fetches rows one by one from the database. This will add them to the table displayed in the Companies window (see figure 13). When no more rows are available, the loop terminates, and program flow returns to the QryComp function chain, whose last two functions flush the query and clear the fields.

Summary

The Vendor Alliance Tracking program was developed using Visual AppBuilder for Windows in 3 weeks and will be implemented during the spring of 1994. The Vendor Alliance Group plans to maintain the program and use Visual Appbuilder for future projects and to add to the existing program. Any changes made to the program can take place almost instantly and the database reflects the changes.

Figure 15: Query Company function chain.

* Originally published in Novell AppNotes

Disclaimer

The origin of this information may be internal or external to Novell. While Novell makes all reasonable efforts to verify this information, Novell does not make explicit or implied claims to its validity.

© Copyright Micro Focus or one of its affiliates