webMethods Creating a business process with a task for approval Part 3 Create a database structure to hold the PurchaseOrder

We will want to display the purchase order content to our manager to approve it. There are two ways to do this:

 - You can pass the document in between activities in the BPM flow, but I really don't recommend this approach, it creates a big load on BPM engine and it only supports a small amount of MBs, depending on you're system, you might try to crash the system by generating a 10MB or more PurchaseOrder and try to feed it to activities in the process, normally a PurchaseOrder shouldn't be this big but there are allot of document that include pictures and can get quite big. Bottom line is store you're PurchaseOrder (or whatever input document is there) to a database.

- Second approach is store the document in a database, we are using standard relational database, you could use a non SQL database but this is a PurchaseOrder and probably in real world applications you might want to query it in relation to multiple other tables from other system and relational databases are really much more flexible in terms of queries.

1. We are going for second option the first thing is to generate the relational model, from our document/xml (check the XMLSpy tabular form). We can see that there are two one to many relations from the PurchaseOrder with Items and Address elements. This will have an external key to the PurchaseOrderNumber that will be a primary key in a top relational table PurchaseOrder.

The relational model diagram using MS SQL Server 2008 and SQL Management Studio could be modelled like bellow. Note that an extra field was added to the PurchaseOrder table, Approved, this will be the field to be changed on manager approval, by default this field will be set to No. 

2. To generate this model first create a database in MS SQL, this is just for the demo so we are leaving the default values.

Note on the top comments of each section.

	-- This are scripts created by using the Management Studio generate script tool
	-- Code to create the database, use it only if it was not created using the Management Studio
	USE [master]
	GO
	/****** Object: Database [PurchaseOrder] Script Date: 08/12/2014 17:28:42 ******/
	CREATE DATABASE [PurchaseOrder] ON PRIMARY
	( NAME = N'PurchaseOrder', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PurchaseOrder.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
	LOG ON
	( NAME = N'PurchaseOrder_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PurchaseOrder_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
	GO
	ALTER DATABASE [PurchaseOrder] SET COMPATIBILITY_LEVEL = 100
	GO
	IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
	begin
	EXEC [PurchaseOrder].[dbo].[sp_fulltext_database] @action = 'enable'
	end
	GO
	ALTER DATABASE [PurchaseOrder] SET ANSI_NULL_DEFAULT OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET ANSI_NULLS OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET ANSI_PADDING OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET ANSI_WARNINGS OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET ARITHABORT OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET AUTO_CLOSE OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET AUTO_CREATE_STATISTICS ON
	GO
	ALTER DATABASE [PurchaseOrder] SET AUTO_SHRINK OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET AUTO_UPDATE_STATISTICS ON
	GO
	ALTER DATABASE [PurchaseOrder] SET CURSOR_CLOSE_ON_COMMIT OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET CURSOR_DEFAULT GLOBAL
	GO
	ALTER DATABASE [PurchaseOrder] SET CONCAT_NULL_YIELDS_NULL OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET NUMERIC_ROUNDABORT OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET QUOTED_IDENTIFIER OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET RECURSIVE_TRIGGERS OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET DISABLE_BROKER
	GO
	ALTER DATABASE [PurchaseOrder] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET DATE_CORRELATION_OPTIMIZATION OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET TRUSTWORTHY OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET ALLOW_SNAPSHOT_ISOLATION OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET PARAMETERIZATION SIMPLE
	GO
	ALTER DATABASE [PurchaseOrder] SET READ_COMMITTED_SNAPSHOT OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET HONOR_BROKER_PRIORITY OFF
	GO
	ALTER DATABASE [PurchaseOrder] SET READ_WRITE
	GO
	ALTER DATABASE [PurchaseOrder] SET RECOVERY FULL
	GO
	ALTER DATABASE [PurchaseOrder] SET MULTI_USER
	GO
	ALTER DATABASE [PurchaseOrder] SET PAGE_VERIFY CHECKSUM
	GO
	ALTER DATABASE [PurchaseOrder] SET DB_CHAINING OFF
	GO
	-- Create the PurchaseOrder table
	USE [PurchaseOrder]
	GO
	/****** Object: Table [dbo].[PurchaseOrder] Script Date: 08/12/2014 17:27:55 ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[PurchaseOrder](
	[PurchaseOrderNumber] [varchar](50) NOT NULL,
	[OrderDate] [varchar](50) NULL,
	[DeliveryNotes] [varchar](50) NULL,
	[Approved] [varchar](50) NULL,
	CONSTRAINT [PK_PurchaseOrder] PRIMARY KEY CLUSTERED
	(
	[PurchaseOrderNumber] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	SET ANSI_PADDING OFF
	GO
	-- Create the Address table
	USE [PurchaseOrder]
	GO
	/****** Object: Table [dbo].[Address] Script Date: 08/12/2014 17:28:12 ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT NULL,
	[Type] [varchar](50) NULL,
	[Name] [varchar](50) NULL,
	[Street] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[State] [varchar](50) NULL,
	[Zip] [varchar](50) NULL,
	[Country] [varchar](50) NULL,
	[PurchaseOrderNumber] [varchar](50) NULL,
	CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
	(
	[AddressID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	SET ANSI_PADDING OFF
	GO
	ALTER TABLE [dbo].[Address] WITH CHECK ADD CONSTRAINT [FK_Address_PurchaseOrder] FOREIGN KEY([PurchaseOrderNumber])
	REFERENCES [dbo].[PurchaseOrder] ([PurchaseOrderNumber])
	GO
	ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_PurchaseOrder]
	GO
	-- Create the Item table
	USE [PurchaseOrder]
	GO
	/****** Object: Table [dbo].[Item] Script Date: 08/12/2014 17:28:07 ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[Item](
	[ItemsID] [int] IDENTITY(1,1) NOT NULL,
	[PartNumber] [varchar](50) NULL,
	[ProductName] [varchar](50) NULL,
	[Quantity] [varchar](50) NULL,
	[USPrice] [varchar](50) NULL,
	[Comment] [varchar](50) NULL,
	[Shipdate] [varchar](50) NULL,
	[PurchaseOrderNumber] [varchar](50) NULL,
	CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
	(
	[ItemsID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	SET ANSI_PADDING OFF
	GO
	ALTER TABLE [dbo].[Item] WITH CHECK ADD CONSTRAINT [FK_Item_PurchaseOrder] FOREIGN KEY([PurchaseOrderNumber])
	REFERENCES [dbo].[PurchaseOrder] ([PurchaseOrderNumber])
	GO
	ALTER TABLE [dbo].[Item] CHECK CONSTRAINT [FK_Item_PurchaseOrder]
	GO

view raw gistfile1.sql hosted with ❤ by GitHub

3.  Once the database structure has been created we can now proceed to develop the adapters to insert the PurchaseOrder document in the database once it has been received by the exposed web service and before it is published to the broker.

The folder structure of the package has been changed to receive the adapters, we are using standard wM adapters to insert values in the table, this kind of a trivial process so it is being skipped.

4. Once the adapters have been created generate a service Demo_POApprovalProcess.services.db:createPurchaseOrder that will be responsible for inserting a full PurchaseOrder to the database. Note that the transaction is implicit as the JDBC connector is set LOCAL_TRANSACTION.

5. Change the service Demo_POApprovalProcess.services:publishPurchaseOrder created in Part 1 to include the previous service just before the document is published.

6. Use SOAP UI to call of the WS (as shown in Part 1) and check that the fields are inserted in the database.

Now that there is a database structure to work with we can start using the task engine and the some CAF to display a the data for the manager to approve. 

webMethods Creating a business process with a task for approval Part 2 Create a Dummy Process to triggered by the PurchaseOrder submission

 On this second part of webMethods BPM/Task Engine tutorial we are going to invoke the previously created web service to start a Process in webMethods with a simple Dummy task this will enable to progressively increment the level of complexity. Note that we are not going to explore BPM governance nor problem resolution this a tutorial for beginners that now they're way on IS but don't have experience on BPM.

1. Start by creating a new Process in Designer. If you don't have the Process view available in the top shortcut tabs hit the left most icon and look for Process Development.

Now you can select that view and on the left pane create a new Process Project by right clicking the Processes folder and clicking the new Process Project. Name you're process whatever you want but in real life it should have a designation related with the business unit the children processes will be related to.

After this the newly created Process Project folder can be used to create our process by second mouse button on it. This is a key component in process development organization, Processes Projects are our packages for Processes. 

The final structure should look something like this.

 2. Create a new process definition. If this is the first time you are creating a Process you might want to change from Business Analyst (blue) perspective to Process Developer (purple) otherwise you won't have access to the Implementation tab in the activities Properties tab.

Using the palette create a Process, I am using a pool with two lanes one will be for system and the second one for the Manager (at this time we are still not creating user tasks but this will be later assigned to a fictitious user). Also drag a start and end activity as well as an abstract class (that we are renaming to dummy at this time we just want to check the process has started and that we can monitor it on MWS).

The screen before is quite explicitly on the process design.

3. Enable the subscription of documents from the broker. To do this go to the Implementation tab and set Receive Protocol  for Subscription(For Broker Documents), this will enable that any document type specified on the Receive Document parameter, whenever is published to the broker, it will immediately  start a new process instance (of course there are filters that can be applied to fields on the received document).

4. Finally we need to build and upload the process, just click the icon on the picture bellow and everything is ok the process will be deployed to IS and also be visible on MWS.

This is how the Build Report should look.

5.Check that the process is upload in MWS and IS.

In the IS the BPM engine will generate a package. With a trigger for the transaction and one for the PurchaseOrder broker subscription. Yeap wM does all this background work to enable the BPM feature wMPRT is one of the packages responsible for this, this is a strong reload candidate whenever there is some anomalous BPM functionality.

On MWS when navigating to Administration > Business Processes the uploaded process should be available here, make sure the Execution is enabled. 

If you are new to BPM I strongly advise to play around and create more complex processes, try to drag a service from the IS and see what happens, add more tasks and check how the IS package was changed.

6. Finally lets test if everything is working correctly. Using the same procedure as explained in part 1 run the SOAP UI (or other interface to call the WS). If things are working correctly you should be able to navigate (in MWS with Administrator account) to Monitoring > Business > Process Instances  and see a BP instance available, you might have to hit search.

If you click detail you should an extensive page that will resume the process execution status.

Well folks that's it for this part.