Figure 1
In this article we shall be considering a workflow system
for a Leave Approval process. An Employee raises a leave request by placing a
document in a folder. This document is then picked up by the system and routed
to the manager of the employee. The manager can either “Approve” or “Reject”
the leave request. In this case, when the manager is on leave or not
available, the leave request would then get routed to the manager’s boss for
his approval. The leave request would get “TIMEDOUT” and sent back to the
employee when the manager and his boss fail to respond to the leave
application. Refer to the Figure 1.
Database Design
The database consists of two tables, the “Employee” table
and the “EmployeeLeaveHistory” table.
The column names and the primary keys are shown in the
Figure 2.
Figure 2
Points to note
When an employee’s leave request gets APPROVED, the number
of leaves requested gets deducted from the SickLeaveBalance or the
EarnedLeaveBalance, based on the LeaveType.
If the number of leaves requested is greater than the
available leaves, the leave request is automatically REJECTED before being
routed to the manager of the employee.
The Employee Leave History table maintains the record of the
leaves taken by an employee. A row is inserted into this table when a leave
request is APPROVED.
Stored Procedures
Several stored procedures have been written for the common
tasks of querying and inserting records into the tables, shown in Figure 2. The
stored procedures list is shown in Figure 3.
Figure 3
Database Library
A Database library is used to interact with the SQL Server
database. This Database Library is referenced by the BizTalk project and is
called directly by the Orchestration to perform database related operations. This
Database Library must be present in the Global Assembly Cache (GAC), since all
assemblies referenced by BizTalk must be placed in GAC. The class diagram for
the Database Library is shown in Figure 4.
Figure 4
