Entering a Purchase Requisition

[Previous] [Next]

Two programs are used for entering a purchase requisition: prEnter.asp and prProcess.asp. The following sections describe these programs.

prEnter.asp

The prEnter.asp program is an ASP page that presents a view of the Vendors database, which contains catalogs for all approved vendors. Figure 11-4 shows this page with data selected.

This program reads the Employees and Vendors databases and populates drop-down list boxes. When a vendor is selected, the ASP page calls itself to update the items in the Item drop-down list box. This process is shown in Listing 11-1.

click to view at full size.

Figure 11-4. The purchase requisition page, prEnter.asp, allows an employee to start the process of ordering goods.

Listing 11-1. prEnter.asp, a program for entering purchase requisitions.

 

prEnter.asp

<%@language=vbscript%> <% Set oVendorConn = Server.CreateObject("ADODB.Connection") oVendorConn.Open("dbTC-Vendors") Function dateRange(fieldName, startDate, daysBackward, daysForward) If Request.Form(fieldName) = "" Then selectDate = FormatDateTime(startDate, vbShortDate) Else selectDate = Request.Form(fieldName) End If dateRange = vbCrLf & "<SELECT NAME='" & fieldName & "'>" For i = -daysBackward To daysForward thisDate = FormatDateTime(startDate + i, vbShortDate) dateRange = dateRange & vbCrLf & vbCrLf & "<OPTION " If thisDate = selectDate Then dateRange = dateRange & _ "SELECTED" dateRange = dateRange & " VALUE='" & thisDate & "'>" _ & thisDate & "</OPTION>" Next dateRange = dateRange & vbCrLf & "</SELECT>" End Function %> <HTML> <HEAD> <TITLE>Purchase Requisition</TITLE> <SCRIPT> function loadItems() { document.all.vendorForm.action="prEnter.asp" document.all.vendorForm.submit() } </SCRIPT> </HEAD> <BODY STYLE="font-family:Verdana;"> <H1>Purchase Requisition</H1> <FORM METHOD="POST" id="vendorForm" ACTION="prProcess.asp"> <TABLE> <TR> <TD>Employee</TD> <TD> <SELECT NAME="requestorID"> <OPTION VALUE="0">-- Select an Employee --</OPTION>" <% Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open("dbTC-Employees") Set rsEmployee = oConn.Execute( _ "SELECT * FROM Employees ORDER BY LName") Do Until rsEmployee.EOF Response.Write ("<OPTION ") If Int(Request.Form("requestorID")) = _ Int(rsEmployee("empID")) Then Response.Write (" SELECTED") End If Response.Write (" VALUE='" _ & rsEmployee("empID") & "'>"_ & rsEmployee("FName") & " " _ & rsEmployee("LName") _ & "</OPTION>") rsEmployee.MoveNext Loop Set rsEmployee = Nothing oConn.Close Set oConn = Nothing %> </SELECT> </TD> </TR> <TR> <TD>Vendor</TD> <TD> <SELECT ID="vendorID" NAME="vendorID" ONCHANGE="javascript:loadItems();"> <OPTION VALUE="0">-- Select a Vendor --</OPTION> <% Set rsVendor = oVendorConn.Execute( _ "SELECT * FROM Vendors ORDER BY vendorName") Do Until rsVendor.EOF Response.Write ("<OPTION ") If Int(Request.Form("vendorID")) = _ Int(rsVendor("vendorID")) Then Response.Write (" SELECTED") End If Response.Write (" VALUE='" _ & rsVendor("vendorID") & "'>" _ & rsVendor("vendorName") & "</OPTION>") rsVendor.MoveNext Loop Set rsVendor = Nothing %> </SELECT> </TD> </TR> <TR> <TD>Item</TD> <TD> <SELECT NAME="itemNum"> <OPTION VALUE="0">-- Select an Item --</OPTION> <% If Request.Form("vendorID") <> 0 Then Set rsItem = oVendorConn.Execute( _ "SELECT * FROM catalog WHERE vendorID=" _ & Request.Form("vendorID") _ & " ORDER BY description") Do Until rsItem.EOF Response.Write(vbCrLf & "<OPTION ") If Request.Form("itemNum") = rsItem("itemNum") Then Response.Write(" SELECTED") End If Response.Write(" VALUE='" _ & rsItem("itemNum") & "'>" _ & rsItem("description") _ & ", " & rsItem("color") & "</OPTION>") rsItem.MoveNext Loop set rsItem = Nothing End If %> </SELECT> </TD> </TR> <TR> <TD>Quantity</TD> <TD><INPUT NAME="itemQty" VALUE="1" SIZE="5"> </INPUT></TD> </TR> <TR> <TD>Need After</TD> <TD><%=dateRange("needAfter", Now(), 0, 30)%></TD> </TR> <TR> <TD>Need Before</TD> <TD><%=dateRange("needBefore", Now() + 21, 20, 30)%></TD> </TR> </TABLE> <INPUT TYPE="SUBMIT" VALUE="Submit Purchase Req"></INPUT> </FORM> </BODY> </HTML> <% oVendorConn.Close Set oVendorConn = Nothing %>

In the vendor pull-down box is an ONCHANGE event. Changing the value of this control executes the JScript function loadItems, which temporarily changes the ACTION method on the form, causing the page to reload itself. When the page calls itself, the page loads the Item dropdown box with all items from the selected vendor. When the Submit button is clicked, prEnter.asp calls prProcess.asp, which verifies the requisition.

prProcess.asp

The prProcess.asp program contains business logic designed to verify that the requestor is authorized to purchase the desired items. The program also performs other checks, including determining whether the item is already in stock. Figure 11-5 shows the confirmation screen.

click to view at full size.

Figure 11-5. ASP page confirming that a purchase requisition has been added to the purchase requisition database.

Listing 11-2 shows prProcess.asp, which verifies that a purchase requisition is valid.

Listing 11-2. This program verifies that a purchase requisition is valid.

 

prProcess.asp

<%@ LANGUAGE = "VBScript" %> <HTML><BODY STYLE="font-family:Verdana"> <% ' Open Employee database Set oEmpConn = Server.CreateObject("ADODB.Connection") oEmpConn.Open("dbTC-Employees") ' Open Vendor database Set oVendorsConn = Server.CreateObject("ADODB.Connection") oVendorsConn.Open("dbTC-Vendors") ' Open PO database Set oPOConn = Server.CreateObject("ADODB.Connection") oPOConn.Open("dbTC-PurchaseOrders") ' Open Inventory database Set oInventoryConn = Server.CreateObject("ADODB.Connection") oInventoryConn.Open("dbTC-Inventory") reqError = "" ' Do some error checking Set itemRS = oVendorsConn.Execute("SELECT price FROM Catalog " _ & "WHERE itemNum='" _ & Request.Form("itemNum") _ & "'") If itemRS.EOF Then reqError = reqError _ & "<BR>Cannot find item " _ & Request.Form("itemNum") End If Set employeeRS = oEmpConn.Execute("SELECT purchaseLimit " _ & "FROM Employees " _ & "WHERE empID=" & Request.Form("requestorID")) If employeeRS.EOF Then reqError = reqError _ & "<BR>Cannot find employee " _ & Request.Form("requestorID") End If ' Check to see if our requestor has purchasing authority qty = Request.Form("itemQty") price = itemRS("price") totalPrice = qty * price If CDbl(totalPrice) > CDbl(employeeRS("purchaseLimit")) Then reqError = reqError _ & "<BR>Your purchase authority of $" _ & employeeRS("purchaseLimit") _ & " is less than your request total of $" _ & totalPrice _ & ". Try again." End If ' Check to see if the item is already in stock Set inventoryRS = oInventoryConn.Execute( _ "SELECT * FROM inventory " _ & "WHERE vendorID=" & Request.Form("vendorID") _ & " AND itemNum='" & Request.Form("itemNum") & "'") qtyRequested = Request.Form("itemQty") If Not inventoryRS.EOF Then qtyOnHand = inventoryRS("quantity") If CDbl(qtyOnHand) >= CDbl(qtyRequested) Then reqError = reqError _ & "<BR>Inventory reports " _ & qtyOnHand _ & " units of item " _ & Request.Form("itemNum") _ & " already in stock. Requisition denied." Else reqMessage = reqMessage _ & "<BR>Inventory reports " _ & qtyOnHand _ & " units of item " _ & Request.Form("itemNum") _ & " already in stock. Order quantity reduced to " _ & qtyRequested - qtyOnHand _ & " units." qtyRequested = qtyRequested - qtyOnHand End If End If If reqError <> "" Then Response.Write("<H1>Error</H1>") Response.Write(reqError) Else Response.Write(reqMessage) Set rsOrder = Server.CreateObject("ADODB.Recordset") rsOrder.CursorType = 2 rsOrder.LockType = 2 rsOrder.Open "PORequests", oPOConn rsOrder.AddNew rsOrder("timeStamp") = Now() rsOrder("requestorID") = Request.Form("requestorID") rsOrder("vendorID") = Request.Form("vendorID") rsOrder("itemNum") = Request.Form("itemNum") rsOrder("itemQty") = qtyRequested rsOrder("needBefore") = Request.Form("needBefore") rsOrder("needAfter") = Request.Form("needAfter") rsOrder("itemPrice") = price rsOrder.Update rsOrder.Close Set rsOrder = Nothing oPOConn.close Set oPOConn = Nothing Response.Write("<H1>Request added!</H1>") Response.Write("<P>Your request has been added to the " _ & "purchase requisition database") Response.Write("<TABLE>") Response.Write("<TR><TD>Requestor ID</TD><TD>" _ & Request.Form("requestorID") & "</TD></TR>") Response.Write("<TR><TD>Vendor ID</TD><TD>" _ & Request.Form("vendorID") & "</TD></TR>") Response.Write("<TR><TD>Item Number</TD><TD>" _ & Request.Form("itemNum") & "</TD></TR>") Response.Write("<TR><TD>Quantity</TD><TD>" _ & qtyRequested & "</TD></TR>") Response.Write("<TR><TD>Need Before</TD><TD>" _ & Request.Form("needBefore") & "</TD></TR>") Response.Write("<TR><TD>Need After</TD><TD>" _ & Request.Form("needAfter") & "</TD></TR>") End If %> </BODY> </HTML>

ROPE and SDL

The program we will write in this section is poGen.exe, and we'll write it in Visual Basic. The poGen program uses a new Microsoft technology named ROPE (which you'll recall stands for Remote Object Proxy Engine). In Chapter 8, we built a SOAP client the hard way—that is, we concatenated strings to form the SOAP envelope, then sent that envelope, along with a payload inside, to a server that read the package and sent a response. Concatenating strings helped us understand SOAP packets and learn about how they work, but concatenation is not a very user-friendly way to write programs for a couple of reasons. First of all, programmers must know the SOAP syntax in order to make a remote procedure call. Second, they need to understand the rules of XML so that they create documents that are parser-friendly.

Wouldn't it be nice if there existed a service that stood between you and your creation of SOAP documents? This service could translate your server request into the required XML code and then return the expected data. In other words, wouldn't it be nice to have a proxy do your remote method-invocation bidding? That proxy is ROPE, which consists of a shared COM object (rope.dll) that comes with the SOAP Toolkit for Visual Studio 6.0, included on the companion CD. Figure 11-6 shows how ROPE works.

click to view at full size.

Figure 11-6. ROPE translates requests for services into SOAP documents and communicates with remote objects.

ROPE depends on the existence of an XML file tagged according to the Service Description Language (SDL). This tagged file describes all services that are available to a calling application. You can create an SDL document using the SOAP Toolkit Wizard that ships as part of the SOAP Toolkit. This wizard reads a COM object and creates the appropriate SDL. You can also write SDL documents by hand when you're dealing with non-COM applications as in Listing 11-3. The SDL specification is included with the SOAP Toolkit for Visual Studio 6.0. Listing 11-3 shows an example of the SDL document we are using.

NOTE
At the time of this writing, the SDL specification is still in flux. Most parties agree that an XML-based method-discovery system is needed, but the syntax might change by the publication date of this book. Check http://architag.com/support for update information.

Listing 11-3. SDL defines Web services exposed to SOAP and ROPE.

 

Services.xml

<?xml version="1.0" ?> <serviceDescription name="SoapService" xmlns="http://localhost/soapdemo/sdlschema.xml" xmlns:dt="http://www.w3.org/1999/XMLSchema" xmlns:ss ="#ServiceSchema"> <import namespace="#ServiceSchema" location ="#ServiceSchema"/> <soap xmlns="urn:schemas-xmlsoap-org:soap-sdl-2000-01-25"> <interface name="BizTalkInterface"/> <service> <implements name="BizTalkInterface" /> <!-- Send a BizTalk message for processing --> <requestResponse name="BizTalkMessage"> <request ref="ss:BizTalkMessageRequest"/> <response ref="ss:BizTalkMessageResponse"/> <parameterorder>BizTalkDoc</parameterorder> </requestResponse> <!-- Check Inventory --> <requestResponse name="checkInventory"> <request ref="ss:checkInventoryRequest"/> <response ref="ss:checkInventoryResponse"/> <parameterorder>itemNum quantity</parameterorder> </requestResponse> <!-- Check the server status ("are you OK?") --> <requestResponse name="RUOK"> <request ref="ss:RUOKRequest"/> <response ref="ss:RUOKResponse"/> </requestResponse> <addresses> <location url="http://localhost/toycarparts/BTServer.xar"/> </addresses> </service> </soap> <ss:schema id="ServiceSchema"> <element name="BizTalkMessageRequest"> <type> <element name="BizTalkDoc" type="dt:string"/> </type> </element> <element name="BizTalkMessageResponse"> <type> <element name="DeliveryReceipt" type="dt:string"/> </type> </element> <element name="checkInventoryRequest"> <type> <element name="itemNum" type="dt:string"/> <element name="quantity" type="dt:string"/> </type> </element> <element name="checkInventoryResponse"> <type> <element name="itemPrice" type="dt:string"/> <element name="deliveryDate" type="dt:string"/> </type> </element> <element name="RUOKRequest"> <type> <element name="Subsystem" type="dt:string"/> </type> </element> <element name="RUOKResponse"> <type> <element name="Status" type="dt:string"/> </type> </element> </ss:schema> </serviceDescription>

To provide a COM interface to your program, the ROPE object reads the SDL document to discover what services are available. These services are then exposed as SOAP-based Web services. In other words, to the ROPE programmer, the entire world looks like a COM object. This is very exciting stuff. You will see what the ROPE interface looks like in the sendMsg Visual Basic function in the next example.

The Purchase Order Generation Program

The purchase order generation program, poGen, reads the PORequests table in the PurchaseOrders database, collects requests by vendor, and creates a number of purchase orders that are shipped to various vendors based on their addresses in the Vendors database. It can be run daily or even every minute when there is a lot of internal purchase requisition traffic. The poGen user interface is shown in Figure 11-7. The important procedures in the poGen program are described in the following sections.

click to view at full size.

Figure 11-7. The poGen program builds purchase orders and sends them, as BizTalk documents, to vendors.

Form.Load subroutine

The Form.Load subroutine, shown in Listing 11-4, retrieves all purchase requisitions from the database and displays them in a window. This display is for demonstration purposes—a program that does what poGen does will probably not have any user interaction at all, just logging.

Listing 11-4. The Form_Load subroutine from the poGen program displays purchase requisitions.

 

Form.Load

Private Sub Form_Load() Randomize strSQL = "SELECT PORequests.*, catalog.*, vendors.* " _ & "FROM vendors INNER JOIN (PORequests " _ & "INNER JOIN [catalog] ON [PORequests].[itemNum]=" _ & "[catalog].[itemNum]) ON ([PORequests].[vendorID]=" _ & "[vendors].[vendorID]) AND ([vendors].[vendorID]=" _ & "[catalog].[vendorID]) ORDER BY [vendors].[vendorID]" Set odbPO = New ADODB.Connection odbPO.Open ("dbTC-PurchaseOrders") Set orsPO = odbPO.Execute(strSQL) Do Until orsPO.EOF gridPO.AddItem orsPO("itemNum") _ & vbTab & orsPO("description") _ & vbTab & orsPO("itemQty") _ & vbTab & Format(orsPO("itemPrice"), _ "###,##0.00") _ & vbTab & Format(orsPO("itemPrice") _ * orsPO("itemQty"), "###,##0.00") orsPO.MoveNext Loop orsPO.Close Set orsPO = Nothing End Sub

The poGen program uses the MSFlexGrid control to display columns of information. A query is made against the tables in the PurchaseOrder database and the grid control is filled.

createPO_Click subroutine

When the user clicks the Create And Send POs button, the createPO_Click subroutine collects all purchase requisitions for a given vendor and creates a single purchase order document for that vendor. Then it moves to the next vendor until all purchase requisitions are sent. This subroutine, createPO_Click, is shown in Listing 11-5.

Listing 11-5. The createPO_Click subroutine consolidates and sends purchase orders to vendors.

 

createPO_Click

Private Sub createPO_Click() Dim txtBizTalk As String Dim txtSOAP As String Dim txtXML As String Dim oXMLDoc As New msxml2.DOMDocument Dim oParmsDoc As New msxml2.DOMDocument Dim orsRequisitions As New ADODB.Recordset Dim orsPOSent As New ADODB.Recordset Dim strSQL As String Dim currentDate As Date Dim strSendTo As String Dim strReplyTo As String Dim currentVendor As Integer Dim poNum Dim strParms As String txtResponse.Text = "" currentDate = Now() currentVendor = 0 strSQL = "SELECT PORequests.*, vendors.*, PORequests.vendorID " _ & "FROM vendors INNER JOIN " _ & "PORequests ON vendors.vendorID = PORequests.vendorID " _ & "ORDER BY PORequests.vendorID" orsRequisitions.CursorType = adOpenKeyset orsRequisitions.LockType = adLockOptimistic orsRequisitions.Open strSQL, odbPO, , , adCmdText orsPOSent.CursorType = adOpenKeyset orsPOSent.LockType = adLockOptimistic orsPOSent.Open "POSent", odbPO, , , adCmdTable Do Until orsRequisitions.EOF Screen.MousePointer = vbHourglass ' Generate a new purchase order number orsPOSent.AddNew orsPOSent("poDate") = currentDate orsPOSent("timeSent") = Now() orsPOSent.Update poNum = orsPOSent("poNum") ' Generate the purchase order number orsPOSent("messageID") = "uuid:" & genUUID() orsPOSent.Update txtXML = "<purchaseOrder_Chapter11_1.0" _ & vbCrLf & " xmlns = 'urn:purchaseOrder_Chapter12_1.0.xdr'" _ & vbCrLf & " custID = '" & CUST_ID & "'" _ & vbCrLf & " poNumber = '" & poNum & "'" _ & vbCrLf & " poDate = '" & iso8601(currentDate) & "'>" currentVendor = orsRequisitions("vendorID") nextItem: txtXML = txtXML & vbCrLf & " <item" _ & vbCrLf & " needAfter='" _ & iso8601(orsRequisitions("needAfter")) & "'" _ & vbCrLf & " needBefore='" _ & iso8601(orsRequisitions("needBefore")) & "'" _ & vbCrLf & " reqID='" & orsRequisitions("reqID") & "'" _ & ">" txtXML = txtXML & vbCrLf & " <number>" _ & orsRequisitions("itemNum") & "</number>" txtXML = txtXML & vbCrLf & " <price>" _ & orsRequisitions("itemPrice") & "</price>" txtXML = txtXML & vbCrLf & " <qty>" _ & orsRequisitions("itemQty") & "</qty>" txtXML = txtXML & vbCrLf & "</item>" strParms = "<?xml version='1.0'?>" _ & "<parms>" _ & " <toAddress type='agr:department'>" _ & orsRequisitions("vendorToAddress") & "</toAddress>" _ & " <messageID>" & orsPOSent("messageID") _ & "</messageID>" _ & " <sendTo>" & orsRequisitions("vendorToAddress") _ & "</sendTo>" _ & " <confirmTo>" & orsRequisitions("vendorConfirmAddress") _ & "</confirmTo>" _ & "</parms>" orsRequisitions("poNum") = poNum orsRequisitions.Update orsRequisitions.MoveNext If orsRequisitions.EOF Then GoTo around If currentVendor = orsRequisitions("vendorID") Then GoTo nextItem End If around: txtXML = txtXML & "</purchaseOrder_Chapter11_1.0>" oXMLDoc.loadXML (txtXML) If oXMLDoc.parseError.errorCode <> 0 Then MsgBox ("XML Error: " & oXMLDoc.parseError.reason _ & " on line " & oXMLDoc.parseError.Line) End If oParmsDoc.loadXML (strParms) txtBizTalk = envelopeBizTalk(oXMLDoc, oParmsDoc) oXMLDoc.loadXML (txtBizTalk) If oXMLDoc.parseError.errorCode <> 0 Then MsgBox ("XML Error: " & oXMLDoc.parseError.reason _ & " on line " & oXMLDoc.parseError.Line) End If sendMsg oXMLDoc Loop out: orsPOSent.Close Set orsPOSent = Nothing orsRequisitions.Close Set orsRequisitions = Nothing Screen.MousePointer = vbDefault End Sub

The subroutine builds the PurchaseOrder_Chapter11_1.0 element as an XML string. This will be the payload placed into the BizTalk document shipped to the vendor.

sendMsg function

Interaction with the ROPE proxy is done in the sendMsg function. This function is shown in Listing 11-6.

Listing 11-6. The sendMsg function creates a connection to an external Web service by reading the SDL file on the remote site and creating a COM interface.

 

sendMsg

Function sendMsg(oXMLDoc As msxml2.DOMDocument) Dim retryCount Dim sendTo As String ' Discover service call sendTo = oXMLDoc.selectSingleNode("//dlv:to/dlv:address").Text Dim oROPE As New ROPE.Proxy Dim oPackager As New ROPE.SOAPPackager ' Call ROPE proxy Dim bRetVal As Boolean bRetVal = oROPE.LoadServicesDescription(icURI, sendTo) If (bRetVal = False) Then MsgBox "LoadServicesDescription = " _ & bRetVal, , "LoadServicesDescription FAILED" End End If Dim sBuffer As String Dim sTransaction sTransaction = vbCrLf & "---- request ----" & vbCrLf & oXMLDoc.xml On Error GoTo badSOAP sBuffer = oROPE.BizTalkMessage(oXMLDoc.xml) sTransaction = sTransaction _ & vbCrLf & "---- response ----" & vbCrLf & sBuffer sTransaction = sTransaction _ & vbCrLf & vbCrLf & "------------------------" _ & vbCrLf & vbCrLf txtResponse.Text = sTransaction & txtResponse.Text txtResponse.Refresh On Error GoTo 0 Set oROPE = Nothing sendMsg = True Exit Function badSOAP: Set oROPE = Nothing MsgBox ("SOAP server returned a fault") On Error GoTo 0 sendMsg = False Exit Function End Function

The main part of sendMsg is the instantiation and connection to the ROPE engine. Check out the following code:

 bRetVal = oROPE.LoadServicesDescription(icURI, sendTo) sBuffer = oROPE.BizTalkMessage(oXMLDoc.xml) 

The first line creates a connection to the external service by loading the SDL document, providing a late-bound interface to the object. Late binding means that the methods of the object are not available at design time, only at run time. (You'll notice that the Microsoft IntelliSense processor does not expose the methods of the external object.)

Once the ROPE object has information from the SDL document, you can invoke methods by using a familiar COM interface. The previous two lines of code handle all the heavy lifting and deal with any external object that supports SOAP and SDL. If you want to see the methods discovered by the SDL query, click the Show SDL button.



XML and SOAP Programming for BizTalk Servers
XML and SOAP Programming for BizTalk(TM) Servers (DV-MPS Programming)
ISBN: 0735611262
EAN: 2147483647
Year: 2000
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net