Walkthrough: Planning a Sales Management Workflow Application for SQL Server

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Introduction

This walkthrough takes you through the steps of planning a workflow application prior to building it with the Workflow Designer for SQL Server. This workflow application systemizes the activities of sales employees, such as cost estimation, placing orders, and delivery.

Practical work analysis

The first step in planning a workflow application is to analyze the business process you want to define. This example describes a process of analyzing sales workflow from order request to delivery.

It is important to note that the point of this application is to focus on how to automate the workflow and how this application can support work in the company. At design time, you must have an understanding of the business process and how it works in each step.

The steps of the sales person at a fictitious distributor are:

1. Receive a request for an estimate
2. Issue the estimate
3. Place order
4. Check the stock
5. Request the delivery
6. Deliver

It is important to focus on the main job when you list steps, because if surrounding business factors, such as financial considerations or stock management, also are listed, then the story is blurred. In other words, those extra procedures might interrupt the basic system design.

Then, from the earlier list, imagine the environment surrounding the sales employee. In actual practice, workflow includes the communication among the people involved in the business process. Each plays an important part in the design of your workflow.

In this example, the roles are:

1. Sales employee
2. Customer
3. Stock keeper
4. Delivery person

If a transfer of money occurs, the accountant might be involved. However, because this sample is being considered as a sales support system, the accounting process does not influence it directly, and the financial staff is not included. If you draw a diagram showing workflow steps, it might help you understand the relationship and the work involved in each step.

Each step has a pool of information. For example, if you order a product, then information, such as the product name, quantity, and price, is involved. The information pools have a relationship to each other. If you draw a picture of how the information goes around these steps with such relationships considered, you will understand the relationship in each process more. These are shown in the following illustration:

Information Flow

Information Flow Detail Table

Work	No.Work	Data
A	Estimate request	Product name/quantity
B	Stock confirm request	Product ID/product name/quantity/estimate ID
C	Stock status and Report the delivery date	Estimate ID/stock status/delivery date
C - i	Inquiry to maker of stock status and the delivery date	Product name/quantity
C - ii	Report from maker of stock status and the delivery date	Product name/quantity/stock status/delivery date
D	Stock status and Report of the delivery date	Product name/quantity/delivery date/price
D'	Estimate cancel	Information flow starts over at A
E	Request to issue estimate	Product name/quantity/delivery date/price
F	Issue an Estimate	Estimate statement (estimate ID/product name/quantity/price/delivery date)
F'	Re-request for estimate	Information flow starts over at A
G	Order	Estimate ID
H	Request to retrieve from the Stock	Estimate ID / order ID
H'	Order complete notification	Order ID/ordered product name/quantity/delivery date/price
I	Stock status change notification	Order ID
I - i	Order to maker	Product name/quantity
I - ii	Delivery from maker	Product
J	Ship request	Order ID
K	Delivery request to stock	Order ID
L	Delivery to delivery department	Order ID/product
M	Check request	Order ID/product
N	Check	N/A
O	Check complete notification	Order ID/product
O'	Redelivery request (poor quality)	Order ID
P	Ship to customer	Product ID and order ID/detailed delivery statement
Q	Response to delivery statement	Delivery statement
R	Delivery notification to sales	Order ID

When you have created the diagram of the sales activities, you can determine which ones to develop into a workflow process. This sample system is supposed to support sales employees' activities, so parts that are unnecessary or unrelated to the basic tracking are omitted.

In this sample scenario, only items that follow the following steps would be tracked:

• Estimate request — order products — granted from stock
• Delivery management — delivery

There are two main processes here. Others have been left out, such as obtaining stock from supply companies and the checking process (quality assurance). If including those processes in the workflow was important, it is easy to imagine that the entire process could be managed by combining an accounting system and a stock management system.

Next, create an outline showing what kinds of actions are involved in each process and what requirements there are to go from one step to the next. If you complete such schematization, it will provide more help in designing your practical system.

Physical design and application implementation

If the design plan is reliable, it helps you to clarify each step. When you start the actual design, there are a few important factors to consider, such as the kind of workflow process that must be created and the kind of user interface controls that must be added to accomplish required tasks.

In this example, the application works using Active Server Pages for the user interface. It is assumed that a database design has been completed already. The database includes a table to store the order content and a table for stock information.

The business rules for this application require that it be able to issue estimates, register orders, and confirm delivery. The following two tables show the functionality that must be implemented in the application.

Sales Functionality

Sales employee	Application for estimate
	Stock/delivery date confirm
	Issuance of estimate
	Order confirm
	Stock request
	Delivery request
Stock keeper	Stock
Delivery person	Logistics

Delivery Functionality

Order management	Application for estimate
	Issuance of estimate
	Order management
Stock management	Stock/delivery date confirm
	Stock
Logistics management	Delivery statement issue
	Logistics management

In the following outlines, the roles of the earlier process and the user interface on the client side are collected.

An Outline of the Workflow Components

Order Management

These components are required to operate the data for customer orders:

• Estimation Request Apply

  Adds the customer request to the database and uses Microsoft® ActiveX® Data Object and OLE DB to manage data.

• Estimation

  Issues the estimate, based on the Estimation Request Apply from a customer.

• Order Management

  Manages the status for an estimate based on the order and operates requests for each section. It is the main class of components.

Stock Management

These components confirm the requested stock and the delivery date based on an estimate request, and manage the work related to stock.

• Stock Delivery Date Confirm

  Estimates the delivery date and returns the result based on current stock information. Operates on requests from the Order Management component.

• Stock Management

  Manages the requested product information. When it is confirmed that the stock is allocated, it immediately updates the update/order data of the stock information.

Logistics Management

These components are provided to control data related to product delivery.

• Issue Delivery Statement

  Prints out a delivery statement to send with the product.

• Logistics Management

  Manages the product delivery status. Updates the order-managing database when the delivery request and the issue delivery statement are completed.

An Outline of the User Interface

Estimation Request Apply	In this application, entering the product information sends a request order to the shop. It also confirms the stocks and the delivery date. When the registration is completed, the information is stored in the database, and an estimate ID is assigned to it automatically.
Stocks/Delivery Date Confirm	Confirms the information in the estimate request and enters the stock and the delivery date information. Updates the status to return the operation to the sales employee. The information of stock and the delivery date is confirmed automatically by the Estimation and Issue Confirm component. It is constructed so it is not required to input data directly.
Estimation and Issue Confirm	Contacts the shop information database. Confirms the delivery date by checking stock selection. Issues an estimate statement if required, which can be printed out.
Order Confirm	Confirms that products are ordered and the status of the ordered products.
Request Stock	Orders product stock.
Request Logistics	Provides status report to the sales employee.
Closing Operation	Closes the order upon delivery complete notification.
Stock Management	Provides information about the delivery date and stock availability to the sales employee. The Logistics Management component returns answers to inquiries about the delivery date. Displays updates from the stock department as to the status of work completed. Sends notification of completion to the sales employee.
Logistics Management	Provides information about current order status. Makes a delivery statement that can be sent with the product. Notifies the sales employee if the status is set to Returned.

You can raise the efficiency of application development by clarifying the individual processes. In addition, you can improve reusability and maintenance by simplifying the design of each application in a distributed system. This helps not only developers and administrators but also users who use the applications.

After you complete the analysis of the application requirements, use the Workflow Designer for SQL Server to create a workflow process that reflects the demands of the business and a user interface that provides access to the functionality.

The Web page shown below was made to be part of the user interface for the sample application. The developer used Microsoft® Visual InterDev® 6.0 to create active server pages that interact with the Microsoft® SQL Server™ database. This is the main order management page that makes it possible for the status of the order to be seen quickly, but each step of the process has a detail page that provides the salesperson access to the database for inserting, updating, and deleting records.

Sales Workflow Sample Web Interface

See Also

Walkthrough: Developing a Workflow Application Using Workflow Designer for SQL Server | Walkthrough: Creating a Workflow Application for SQL Server with a Visual Basic Interface | Developing Workflow Applications for SQL Server