Invoicing the Retailer | Programming Microsoft SQL Server(TM) 2000 with XML (Pro-Developer)

The final aspect of the scenario that we need to discuss is the sending of invoices from the supplier to the retailer. I could have implemented this functionality in the application in a number of ways—I could have sent an updategram or used the OpenXML function to insert an XML invoice that I posted to an ASP. But those strategies don't address the fact that the invoice is generated from the order document, and some fields must be transformed. I could have solved this problem simply by applying an XSL style sheet, but yet another issue needed to be addressed: the supplier could receive orders from multiple retailers. So I needed to find some way to route the invoice to the correct retailer.

Introducing BizTalk Server 2000

Business document routing and transformation are two tasks that Microsoft BizTalk Server 2000 was designed to perform. BizTalk Server 2000 is a business process management and document exchange handling application that can be used in all kinds of integration solutions.

BizTalk Server provides two main services: orchestration and messaging. The orchestration functionality in BizTalk Server allows you to define business processes graphically (using a Microsoft Visio-based interface) and to generate compiled schedules to automate the key processes in your business. The messaging functionality provides support for the automated routing and translation of business documents between trading partners and internal systems. These documents can be in virtually any format including Electronic Data Interchange (EDI) standards such as X.12 or EDIFACT, SAP IDoc documents, and XML. A full discussion of BizTalk Server is beyond the scope of this book, but I want to examine some ways in which BizTalk Server messaging functionality can be used together with SQL Server's XML support to build integrated e-commerce solutions.

BizTalk Server Messaging

BizTalk Server messaging involves the creation of a number of conceptual objects. BizTalk Server provides several tools that you can use in this task.

Defining Business Documents

Your first task when building a messaging solution is to define the business documents you'll be exchanging. These can be XML, EDI or virtually any other kind of document; however, BizTalk Server always uses XML internally to represent the documents that flow through the messaging service. You can use the BizTalk Editor to create a new document definition from scratch, or you can import a definition from an existing document. When using XML, you can import an XDR schema, including annotated schemas that are used in SQL Server-based solutions. Figure 9-8 shows the BizTalk Editor.

Figure 9.8 - The BizTalk Editor

In the sample scenario, we used two documents in the messaging solution. The first is a purchase order defined by the following XML schema:

 <?xml version="1.0"?> <!-- Generated by using BizTalk Editor on Thu, Mar 08 2001 04:53:54 PM --> <!-- Microsoft Corporation (c) 2000 (http://www.microsoft.com) --> <Schema name="PurchaseOrder" b:BizTalkServerEditorTool_Version="1.0"     b:root_reference="PurchaseOrder" b:standard="XML"      xmlns="urn:schemas-microsoft-com:xml-data"      xmlns:dt="urn:schemas-microsoft-com:datatypes"      xmlns:b="urn:schemas-microsoft-com:BizTalkServer"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <b:SelectionFields/>     <ElementType name="UnitPrice">         <b:FieldInfo/>     </ElementType>     <ElementType name="Tax">         <b:FieldInfo/>     </ElementType>     <ElementType name="SupplierID">         <b:FieldInfo/>     </ElementType>     <ElementType name="RetailerID">         <b:FieldInfo/>     </ElementType>     <ElementType name="Quantity">         <b:FieldInfo/>     </ElementType>     <ElementType name="PurchaseOrder" sql:is-constant="1">         <b:RecordInfo/>         <element type="Order" maxOccurs="1" minOccurs="1"/>     </ElementType>     <ElementType name="ProductID">         <b:FieldInfo/>     </ElementType>     <ElementType name="OrderStatus">         <b:FieldInfo/>     </ElementType>     <ElementType name="OrderDetail" sql:relation="OrderDetails">         <b:RecordInfo/>         <element type="ProductID" maxOccurs="1" minOccurs="1"             sql:field="ProductID"/>         <element type="Quantity" maxOccurs="1" minOccurs="1"             sql:field="Quantity"/>         <element type="UnitPrice" maxOccurs="1" minOccurs="1"             sql:field="UnitPrice"/>         <element type="Tax" maxOccurs="1" minOccurs="1" sql:field="Tax"/>     </ElementType>     <ElementType name="OrderDate">         <b:FieldInfo/>     </ElementType>     <ElementType name="Order" sql:relation="Orders">         <b:RecordInfo/>         <AttributeType name="OrderNo">             <b:FieldInfo/>         </AttributeType>         <attribute type="OrderNo" required="yes" sql:field="OrderNo"/>         <element type="OrderDate" maxOccurs="1" minOccurs="1"             sql:field="OrderDate"/>         <element type="RetailerID" maxOccurs="1" minOccurs="1"              sql:field="RetailerID"/>         <element type="SupplierID" maxOccurs="1" minOccurs="1"             sql:field="SupplierID"/>         <element type="Customer" maxOccurs="1" minOccurs="1"             sql:field="Customer"/>         <element type="DeliveryAddress" maxOccurs="1" minOccurs="1"             sql:field="DeliveryAddress"/>         <element type="OrderStatus" maxOccurs="1" minOccurs="1"             sql:field="OrderStatus"/>         <element type="OrderDetail" maxOccurs="*" minOccurs="1">             <sql:relationship key-relation="Orders"                  key="OrderNo"                  foreign-key="OrderNo"                  foreign-relation="OrderDetails">             </sql:relationship>         </element>     </ElementType>     <ElementType name="DeliveryAddress">         <b:FieldInfo/>     </ElementType>     <ElementType name="Customer">         <b:FieldInfo/>     </ElementType> </Schema>

The BizTalk Editor generates this schema from the OrderSchema.xml annotated schema used by the retailer and supplier to define a purchase order. An XML document based on this schema looks like this:

 <PurchaseOrder>     <Order OrderNo="1000">         <OrderDate>2001-03-16T00:54:36.120</OrderDate>         <RetailerID>RetailerSite</RetailerID>         <SupplierID>SupplierSite</SupplierID>         <Customer>053F4A23-05AC-4D4B-8898-FABF502E45B9</Customer>         <DeliveryAddress>1 Any Street, Anytown</DeliveryAddress>         <OrderStatus>placed</OrderStatus>         <OrderDetail>             <ProductID>1</ProductID>             <Quantity>1</Quantity>             <UnitPrice>1000</UnitPrice>             <Tax>250</Tax>         </OrderDetail>         <OrderDetail>             <ProductID>9</ProductID>             <Quantity>1</Quantity>             <UnitPrice>300</UnitPrice>             <Tax>75</Tax>         </OrderDetail>     </Order> </PurchaseOrder>

The second document defined in the sample scenario messaging solution is an invoice. The schema for the invoice document is shown here:

 <?xml version="1.0"?> <!-- Generated using BizTalk Editor on Mon, Oct 02 2000 09:36:47 AM --> <!-- Microsoft Corporation (c) 2000 (http://www.microsoft.com) --> <Schema name="Invoice" b:BizTalkServerEditorTool_Version="1.0"     b:root_reference="Invoice" b:standard="XML"      xmlns="urn:schemas-microsoft-com:xml-data"      xmlns:dt="urn:schemas-microsoft-com:datatypes"      xmlns:b="urn:schemas-microsoft-com:BizTalkServer">     <b:SelectionFields/>     <ElementType name="Total">         <b:FieldInfo/>     </ElementType>     <ElementType name="SupplierID">         <b:FieldInfo/>     </ElementType>     <ElementType name="Sale">         <b:RecordInfo/>  <AttributeType name="OrderDate">             <b:FieldInfo/>         </AttributeType>         <AttributeType name="InvoiceNo">             <b:FieldInfo/>         </AttributeType>         <attribute type="InvoiceNo" required="yes"/>         <attribute type="OrderDate" required="yes"/>         <element type="CustomerID" maxOccurs="1" minOccurs="1"/>         <element type="SupplierID" maxOccurs="1" minOccurs="1"/>         <element type="CustomerRef" maxOccurs="1" minOccurs="1"/>         <element type="LineItem" maxOccurs="*" minOccurs="1"/>         <element type="Total" maxOccurs="1" minOccurs="1"/>     </ElementType>     <ElementType name="Quantity">         <b:FieldInfo/>     </ElementType>     <ElementType name="ProductName">         <b:FieldInfo/>     </ElementType>     <ElementType name="Price">         <b:FieldInfo/>     </ElementType>     <ElementType name="LineItem">         <b:RecordInfo/>         <element type="ProductName" maxOccurs="1" minOccurs="1"/>         <element type="Quantity" maxOccurs="1" minOccurs="1"/>         <element type="Price" maxOccurs="1" minOccurs="1"/>     </ElementType>     <ElementType name="Invoice">         <b:RecordInfo/>         <element type="Sale" maxOccurs="1" minOccurs="1"/>     </ElementType>     <ElementType name="CustomerRef">         <b:FieldInfo/>     </ElementType>     <ElementType name="CustomerID">         <b:FieldInfo/>     </ElementType> </Schema>

A document based on this schema might contain the following data:

 <Invoice>     <Sale InvoiceNo="1000" OrderDate="01/01/2001">         <CustomerID>RetailerSite</CustomerID>         <SupplierID>SupplierSite</SupplierID>         <CustomerRef>053F4A23-05AC-4D4B-8898-FABF502E45B9</CustomerRef>         <LineItem>             <ProductName>Desktop System</ProductName>             <Quantity>1</Quantity>             <Price>1250</Price>         </LineItem>         <LineItem>             <ProductName>ISDN Router</ProductName>             <Quantity>1</Quantity>             <Price>375</Price>         </LineItem>         <Total>1825</Total>     </Sale> </Invoice>

This is the kind of invoice that a supplier sends to acknowledge an order from a retailer.

Both the purchase order and invoice document definitions are stored in the BizTalk Server repository, a local Web site accessed using the HTTP-DAV protocol.

Creating a Map Between the Order and Invoice Documents

Invoices are generated from orders, and the simplest way to create an invoice is to map fields in the order to fields in the invoice. BizTalk Server provides the BizTalk Mapper, a tool that can be used to define a mapping between two different document specifications. Figure 9-9 shows the BizTalk Mapper and the map created between the purchase order and invoice document definitions.

Figure 9.9 - The BizTalk Mapper

In many cases, fields can simply be mapped directly from one document to another. For example, you can see in Figure 9-9 that the OrderNo field in the purchase order document is mapped directly to the InvoiceNo field in the invoice document. However, some mappings aren't so straightforward. The BizTalk Mapper provides some mapping functions (known as functoids) that you can use to define more complex mappings between fields.

The map created for the sample scenario uses a number of functoids to map fields in the purchase order document to fields in the invoice document. First of all, the BizTalk Mapper uses the Database Lookup functoid to retrieve the row in the Products table for each product ID in the order, and then uses the Value Extractor functoid to extract the corresponding product name and write it to the invoice. Second, the BizTalk Mapper uses the Addition functoid to add the price and tax for each item in the order and store the total unit price in the invoice. Finally, it uses the Addition functoid once again to add the price and tax fields, and the Multiplication functoid to multiply the total unit price by the quantity ordered. This produces the total price for each item, and the BizTalk Mapper then uses the Sum functoid to calculate the total value of the invoice.

When you create a map with the BizTalk Mapper, the map is stored in the BizTalk Server repository as an XSL style sheet that can be applied during document exchanges in which the source document must be transformed into a different destination document. Because you can transform a document in this way, you can build solutions to automate business processes, such as generating an invoice from an order or translating a specific type of document from one format to another so that organizations using different document definitions can be integrated. For example, you could use a FOR XML query to extract an XML invoice from a database and then submit that invoice to BizTalk Server, which could use a map to translate the invoice to an EDI format such as X.12 before sending it to its destination.

Configuring a Messaging Solution

Once you have defined the documents and maps that you'll use in your exchange, you can use the BizTalk Messaging Manager to define the messaging architecture for your integration solution. A messaging architecture can include the following entities:

Organization: An organization represents a trading partner with which you'll exchange documents. Your own organization is represented as the Home Organization, and you can add as many other organizations as necessary. The Home Organization can include one or more applications, representing internal line-of-business (LOB) applications that need to exchange documents.
Document: A document is, unsurprisingly, a document specification from the BizTalk Server repository that you will exchange with trading partners or among internal applications.
Channel: A channel is used to define the processing that must take place during a particular document exchange. You can create a channel that defines the source and destination documents, and a map that should be applied as the document is routed to its destination.
Port: A port defines how a document is transmitted to its destination. It can be used to route documents to a specific HTTP, FILE, or MSMQ address, or to pass the document to a component that will perform further processing.
Distribution List: A distribution list is a group of ports that can be used to route a single document to multiple destinations. For example, a supplier could use a distribution list to send a catalog document to multiple retailers.
Envelope: An envelope is used to encapsulate a document, perhaps by adding routing or manifest information.

Figure 9-10 shows the BizTalk Messaging Manager, which you can use to create and configure all of the preceding entities.

Figure 9.10 - The BizTalk Messaging Manager

In the sample scenario, two organizations participate in the document exchange process: the Home Organization, representing the supplier, and RetailerSite, representing the retailer. The purchase order and invoice documents defined earlier are used as the source and destination documents respectively in a channel named InvoiceChannel. This channel applies the map I described earlier to the purchase order to generate an invoice. The resulting invoice document is then sent to a port named InvoicePort, where it is routed over HTTP to the RetailSite Web site.

Submitting a Document to BizTalk Server

So we've configured a messaging solution using BizTalk Server. The challenge now is to find a way to pass instances of the business documents we want to exchange into BizTalk Server so that they can be routed through the correct channel and sent to their destinations.

We can choose from two main techniques to submit documents to BizTalk Server: the IInterchange interface or a receive function.

IInterchange is a COM interface that COM-aware clients can use to submit a document programmatically. It provides Submit and SubmitSync methods so that you can choose to pass documents asynchronously or synchronously.

Receive functions are specified folders or messaging queues that BizTalk Server polls for new documents. Applications that can't use COM can deliver documents into a receive function so that BizTalk Server will pick them up for processing. You can create receive functions using the BizTalk Server Administration tool.

In the sample scenario, the purchase order document is submitted to BizTalk Server from an ASP script. Because COM components can be created in script, we use the IInterchange interface. Here's the code from PlaceOrder.asp that passes the document to BizTalk Server:

 Dim oBTS, BIZTALK_OPENNESS_TYPE_NOTOPEN BIZTALK_OPENNESS_TYPE_NOTOPEN = 1  Set oBTS = Server.CreateObject("BizTalk.Interchange") 'Submit document asynchronously, specifying the channel. oBTS.Submit BIZTALK_OPENNESS_TYPE_NOTOPEN, strXML,,, ,,, "InvoiceChannel" Set oBTS = Nothing

As you can see, we use the Submit method of the IInterchange interface to pass the purchase order document to BizTalk Server. This method can accept several parameters to identify the correct channel through which to route the document. In this case, I've taken the most simple option of simply specifying the channel name in the method call. However, you can choose from a number of approaches to routing a document in which either the source or the destination (or both) is defined in the channel and port as open, and the document is routed dynamically. You can specify the source and destination organizations in the call, allowing you to use the same code to route documents to different destinations, or you can use "self-routing" documents that include fields designated as source or destination identifiers so that BizTalk Server routes the document dynamically, based on data in the document itself.

Receiving a Document from BizTalk Server

Once a document is submitted to BizTalk Server, it passes through a channel and is routed to its destination through a port. The port can send the document to a specified URL over HTTP, a message queue, or a file system location, or the port can send the document in e-mail using SMTP. Furthermore, you can build an Application Integration Component (AIC) and send the document to your component for further custom processing.

In the sample scenario, the document is sent over HTTP to the Invoice.asp page in the RetailSite Web site. Here it's received and read into a DOM document object. The XML is then passed as a string to the importInvoice stored procedure in the RetailSite database. The code in Invoice.asp is shown here:

 Dim objXML 'Load the incoming XML document. Set objXML = server.CreateObject("Microsoft.XMLDom") objXML.Load Request.BinaryRead(Request.TotalBytes) RecordInvoice objXML.xml Sub RecordInvoice(strXML) Dim cmd' As ADODB.Command 'Connect to the database. Set conDB = CreateObject("ADODB.Connection") conDB.ConnectionString = strCon conDB.Open 'Call the importInvoice stored procedure, passing the XML invoice. Set cmd = CreateObject("ADODB.Command") With cmd     Set .ActiveConnection = conDB         .CommandText = "importInvoice"         .CommandType = adCmdStoredProc         .Parameters(1).Value = strXML         .Execute End With End Sub

The importInvoice stored procedure uses the OpenXML function to insert the invoice data into the Invoices and LineItems tables as shown here:

 CREATE PROC importInvoice @xml ntext AS DECLARE @idoc int BEGIN EXEC sp_xml_preparedocument @idoc OUTPUT, @xml --Insert invoice header data from XML into Invoices table. INSERT Invoices     SELECT * FROM OpenXML(@idoc, 'Invoice/Sale', 3)     WITH (InvoiceNo integer,           OrderDate datetime,           RetailerID varchar(30),           SupplierID varchar(30),           CustomerRef varchar(40),           Total money           ) --Insert line items from XML into LineItems table. INSERT LineItems     SELECT * FROM OpenXML(@idoc, 'Invoice/Sale/LineItem', 2)     WITH (InvoiceNo integer '../@InvoiceNo',           ProductName varchar(40),           Quantity int,           Price money           ) EXEC sp_xml_removedocument @idoc END