System and method for catalog administration using supplier provided flat files Number:7,133,868 from the United States Patent and Trademark Office (PTO) owispatent A hybird Notes/DB2 environment provides a requisition catalog on the Web. Client browsers are connected to a GWA infrastructure including a first network dispatcher and a virtual cluster of Domino.Go servers. The network dispatcher sprays out browser requests among configured .nsf servers in virtual server cluster. Communications from this virtual server cluster are, in turn, dispatched by a second network dispatcher servers in a Domino cluster. External objects, primarily for a GUI, are served in a .dfs and include graphic files, Java files, HTML images and net.data macros. The catalog is built from supplier provided flat files. A front end is provided for business logic and validation, as also is a relation database backend. HTML forms are populated using relational database agents. A role table is used for controlling access both to Notes code and DB2 data. Large amounts of data is quickly transferred using an intermediate agent and window.<H administration, provided, System, and, meth, 7133868.html, Patent, pto, 7133868

Title: System and method for catalog administration using supplier provided flat files

Abstract: A hybird Notes/DB2 environment provides a requisition catalog on the Web. Client browsers are connected to a GWA infrastructure including a first network dispatcher and a virtual cluster of Domino.Go servers. The network dispatcher sprays out browser requests among configured .nsf servers in virtual server cluster. Communications from this virtual server cluster are, in turn, dispatched by a second network dispatcher servers in a Domino cluster. External objects, primarily for a GUI, are served in a .dfs and include graphic files, Java files, HTML images and net.data macros. The catalog is built from supplier provided flat files. A front end is provided for business logic and validation, as also is a relation database backend. HTML forms are populated using relational database agents. A role table is used for controlling access both to Notes code and DB2 data. Large amounts of data is quickly transferred using an intermediate agent and window.

Patent Number: 7,133,868 Issued on 11/07/2006 to Ruest, et al.

Inventors: Ruest; David G. (Vestal, NY), Snavely; Amy J. (Binghamton, NY), Musa; Mark A. (Brackney, PA), Cunningham; Candace M. (Binghamton, NY)
Assignee: International Business Machines Corporation (Armonk, NY)

Appl. No.: 09/657,196

Filed: September 7, 2000

Current U.S. Class: 707/5 ; 707/102; 709/203

Current International Class: G06F 17/30 (20060101)

Field of Search: 707/1-10,100-200 709/203

References Cited [Referenced By]

U.S. Patent Documents


5712989	January 1998	Johnson et al.
5715453	February 1998	Stewart
5758351	May 1998	Gibson et al.
5799151	August 1998	Hoffer
5870717	February 1999	Wiecha
5970475	October 1999	Barnes et al.
6112242	August 2000	Jois et al.
6151608	November 2000	Abrams
6360211	March 2002	Anderson et al.

Other References

Lotus Development Corp., an IBM Subsidiary. Lotus Domino: Application Development with Domino Designer, Rel. 5. USA, privately printed, 1998. i-vii, 425-430. cited by other .
Lotus Development Corp., an IBM Subsidiary. Lotus Domino: Domino Enterprise Integration Guide, Rel. 5. USA, privately printed, 1998. 9-20, 107-122, 444-451. cited by other.

Primary Examiner: Ali; Mohammad
Assistant Examiner: Pannala; Sathyanarayan
Attorney, Agent or Firm: Beckstrand; Shelley M Schiesser; William E.

Claims

We claim:

1. A method for publishing a catalog as a relational database production table, comprising the steps of: building a web catalog system including an application server, a catalog administration component, a requisition catalog component, a relational database including a relational database staging table and a relational database production table, a catalog administration browser, and a requester browser; connecting said application server through a firewall to a supplier server; connecting said application server, catalog administration browser and catalog administration component to said relational database staging table; connecting said relational database staging table and said requester browser to said relational database production table, said relational database providing for each of a plurality of supplier catalogs a catalog profile including supplier name, start and end dates of catalog validity, and currency, and fields for each supplier catalog for describing catalog parts and a listing by part number of price; in said supplier server, extracting and reformatting supplier source data to create a flat file catalog in a column delimited format specified by an enterprise; receiving from said supplier server through said firewall via electronic data interchange (EDI) said flat file catalog containing catalog items in said column delimited format specified to suppliers by said enterprise; accepting said flat file catalog in said column delimited format into an enterprise EDI mailbox; reformatting and storing from said enterprise EDI mailbox said flat file catalog as a reformatted flat file-catalog into a generation data group store for retaining a last N iterations of flat file catalogs received by said enterprise from suppliers; executing job scripts for reading said reformatted flat file catalog, alerting said buyer of receipt of a new flat file catalog, and loading said reformatted flat file catalog into said relational database staging table; providing a single path connection for enabling requester access via said requester browser to said relational database production table and buyer access via said catalog administration browser to said relational database staging table, said requester and buyer being provided through an access control list respective requester and buyer levels of authority for accessing said relational database staging table and said relational database production table over said single path, said relational database staging table and said relational database production table having identical formats; providing in said catalog administration function access list controls for controlling through a graphical user interface access presented by said requester browser and access presented by said administration browser to said relational database staging table and to said relational database production table, and further providing in said catalog administration function hard coded controls for selectively enabling and inhibiting access by a buyer at said administration browser to fields within said relational database staging table to allow allowing buyer audit control over first predetermined fields in said relational database staging table while restricting buyer access to second predetermined fields within said relational database staging table, said second Predetermined fields including unit price and unit of measure fields; executing validation procedures in said database server for validating format of said flat file catalog and identifying and logging catalog changes, moving content of said flat file catalog in said relational database staging table to said relational database production table responsive to buyer acceptance of said changes as not including chances to fields to which said buyer is inhibited access by said hard coded controls; and said catalog administration function operating said graphical user interface to enable said buyer to update said relational database production table from said relational database staging table while inhibiting access to said relational database staging table by said requester and allowing said requester read access to said relational database production table for reference in preparing a requisition for a catalog item.

2. System for building and using a web catalog, comprising: a web catalog system including an application server, a catalog administration component, a requisition catalog component, a relational database, a catalog administration browser, and a requester browser; a supplier catalog flat file for storing catalog items in an enterprise defined column delimited format; said relational database providing for each of a plurality of supplier catalogs a catalog profile including supplier name, start and end dates of catalog validity, and currency, and for each supplier catalog fields for describing catalog parts and a listing by part number of price; said relational database including a staging table and a production table; said application server including an enterprise electronic data interchange (EDI) mailbox for receiving, converting and storing said flat file catalog to said staging table; said application server further for reformatting and storing from said enterprise EDI mailbox said flat file catalog as a reformatted flat file catalog into a generation data group store for retaining a last N iterations of flat file catalogs received from suppliers to said enterprise, and for alerting said buyer of receipt of a new flat file catalog; said catalog administration component including an administration function for controlling content of catalog information from a vendor stored to said staging table from said flat file; said catalog administration component including a catalog administration procedure for presenting said staging table to said catalog administration function in a graphical user interface with fields of said staging table selectively enabled or disabled for auditing in accordance with role and authority of a user of said administration function; and for publishing an administration audited catalog to said production table; a requisition creation function operable by a user for creating a requisition with reference to said production table; a single path connection for enabling requester access via said requester browser to said production table and buyer access via said catalog administration browser to said staging table; an access control list for specifying respective requester and buyer levels of authority for accessing said staging table and said production table over said single path connection, said staging table and said production table having identical formats; said catalog administration component further for providing access list controls for controlling access through a graphical user interface to said staging table and to said production table, and further for providing hard coded controls for selectively enabling and inhibiting access by a buyer to predetermined fields within said staging table; said catalog administration component further for moving content of said flat file catalog from said staging table to said production table responsive to buyer acceptance of changes as not including changes to fields to which said buyer is inhibited access by said hard coded controls; and a web catalog function for presenting said production table to said requisition creation function.

Description

CROSS REFERENCES TO RELATED APPLICATIONS

The following U.S. patent applications filed concurrently herewith are assigned to the same assignee hereof and contain subject matter related, in certain respects, to the subject matter of the present application, the teachings of which applications are incorporated herein by this reference: Ser. No. 09/657,215, entitled "System and Method for Clustering Servers for Performance and Load Balancing"; Ser. No. 09/657,216, entitled "System and Method for Front End Business Logic and Validation"; Ser. No. 09/657,217, entitled "System and Method for Data Transfer With Respect to External Applications"; Ser. No. 09/656,037, entitled "System and Method for Providing a Relational Database Backend"; Ser. No. 09/656,803, entitled "System and Method for Providing a Role Table GUI via Company Group"; Ser. No. 09/656,967, entitled "System and Method for Populating HTML Forms Using Relational Database Agents"; and Ser. No. 09/657,195, entitled "System and Method for Providing an Application Navigator Client Menu Side Bar".

BACKGROUND OF THE INVENTION

1. Technical Field of the Invention

This invention pertains to a system and method for managing a requisition catalog on the web.

2. Background Art

A requisition catalog for a large enterprise in a web environment must enable very fast access to a very large database from a large number of clients. A large number of clients is required for optimal performance of a catalog system. There is a need to architect such a system so as to be scalable, that is, capable of providing that fast access to an ever increasing number of clients and a growing database or collection of databases.

In a requisition catalog system for a large enterprise in a web environment, there is a requirement for a system and method for insuring that all general ledger accounts associated with commodities are correct, and within this requirement for providing a description from the associated accounting system for use by customer or requester to select the correct general ledger (GL) account when doing financial validation on a requisition.

A requisition catalog for a large enterprise is stored in a very large database. However, a Lotus Notes database has a hard limit of 2 to 4 gigabytes of data. That is, Notes puts its entire database into one big Notes standard format(.nsf) file. Information in such a .nsf file is accessed by a system geometrically. As a result, such a system slows down significantly as it accesses larger files.

This slow down ramps up rather badly. Consequently, the hard limit is artificially put in because of this ramp up in access time. The typical solution to this problem is to split a very large database into many smaller databases. Lotus Notes has a Lotus connector/Lotus script extensions connector (LCLSX). This allows connection to other databases but these script extensions are not set up to actually be the database. There is a need in the art for a system and method for utilizing Lotus script extensions in combination with a relational database to provide high capacity storage without performance degradation.

In building a requisition catalog for a large enterprise with many suppliers, an automated process is needed to receive a flat file from a supplier for review by a buyer before being externalized for use by requesters. While the buyer must be able to review the contents, he must be restricted from making changes to certain sensitive fields, such as changing a unit price or a unit of measure, both of which could constitute fraud. Consequently, allowing the buyer to edit the flat file can't provide the level of security required. There is a need in the art to provide a buyer a means of auditing catalog content before externalizing it to production for access by requesters.

A requisition catalog for use in a web environment requires a very large database, such as an IBM DB2 Version 5 database, and the functionality provided by, for example, a Lotus Notes Release 5 server. However, a Lotus Notes access control list (ACL) can not be used control access to an IBM DB2 database, and the privileges on a DB2 table can be granted only by the table instance owner. Additionally, since Notes agents which access DB2 databases are running from a Notes server, the Notes server ID often has full access to all tables, and there is no way to limit that. That is, in a hybrid (Notes/DB2) environment, the user ID which accesses DB2 tables is the ID of the Notes server. Therefore, it can't restrict access by a user to the DB2 tables. There is a need in the art for a system and method which allows certain users access to certain data in certain selected tables. That is, there is needed a system and method for providing very flexible access to DB2 tables without requiring database administrator (DBA) involvement to issue grants against the tables, and bypassing the problem caused by Notes agents all coming from the same user (the Notes server ID).

In a hybrid requisition catalog system for use in a web environment, much of the application data is stored in, for example, IBM DB2 tables. However, a web interface written in Java script and in HTML does not have functionality for connecting to a DB2 server and reading data from DB2 tables. No function is provided in Java script and in HTML to connect to or access such a database. It is not presently possible to make the connection by connecting to the database and executing SQL queries. Consequently, there is a need in the art for a system and method utilizing an existing infrastructure including Lotus Notes, Domino Go, and DB2 to combine HTML and Java script web presentation with DB2 data.

In a requisition catalog system for use in a web environment by a large enterprise, there must be provide a way to deal with web sites that exist outside of a firewall, or internal applications within the firewall but outside of the requisition catalog (Req/Cat Web or RCW) application. That is, a system and method is required for transfering a large quantity of data back from such a web site or application to the RCW application in a timely manner. One possible way is to send data on the universal resource locator (URL). However, such a URL is limited to 1 K bytes, which is not enough to do the task quickly for the quantities of information required. Information can be put into a frame, and that information can be read, but only if the information is written and read by the same server.

In a preferred system, the RCW application exists in a frame set in a browser. As part of security for such a browser, Netscape and Internet Explorer (IE) establish ownership of the frames: each frame is a window under control of the browser. A user can open up a session in any frame desired. However, if all of the frames are not owned by same session, then these frames not commonly owned cannot see the contents of each other. In Netscape version 4.51 frame ownership was changed to protect against frame spoofing. Frame spoofing is a way other servers can use to trick the owner of a frame into thinking it had created a frame actually created by someone else, enabling access to data not owned. Netscape 4.51 plugs that hole.

Consequently, in the new environment (with spoofing inhibited) a problem is presented: if a RCW application needs to access an outside supplier site for information to get back through one its own the frames, as soon as data is written by that outside supplier site into one of the frames owned by RCW, ownership of the frame is transferred from RCW to the supplier; RCW no longer owns the frame and cannot access the information. A system and method is needed to enable transfer of data from a supplier site to a RCW application which does not entail frame spoofing.

In a web application, such as a requisition catalog system for a large enterprise, a fast browser interface and navigator is needed, including a very fast graphical user interface (GUI).

There is known in the art several Internet applications which provide a strip down, for example, the left hand side of a window that has menu of items from which a user may select. Such windows also may display a header, including header type items which will expand upon selection into a drop down list. Such windows also may include a footer including leafs which will, upon selection, change what is seen on right side of screen. Such applications provide a very nice user interface for documents, with a table of contents (TOC) on the left, and on the right the selected contents.

However, these expandible and collapsible menus are characteristically slow. This slowness is a result of every click sending a request to a remote server, which server responds by sending information back to expand the list at the browser. Every click on the web is slow, involving communications of the request and response over a relatively slow web communication link. There is, therefore, a need in the art to dramatically increase the speed of operation of a graphical user interface at a browser.

It is an object of the invention to provide a scalable database system in a web environment with optimal access performance characteristics for an expanding number of clients and a growing database.

It is an object of the invention to provide a system and method for enabling a requester to select the correct general ledger account when doing financial validation on a requisition.

It is an object of the invention to provide a system and method for utilizing Lotus script extensions in combination with a relational database to provide high capacity storage without performance degradation.

It is an object of the invention to provide a system and method for enabling a buyer a means for editing catalog content before externalizing it to production for access by requesters.

It is an object of the invention to provide a system and method for providing very flexible access to DB2 tables without requiring database administrator (DBA) involvement to issue grants against the tables, and bypassing the problem caused by Notes agents all coming from the same user (the Notes server ID).

It is an object of the invention to provide a system and method utilizing an existing infrastructure including Lotus Notes, Domino Go, and DB2 to combine HTML and Java script web presentation with DB2 data.

It is an object of the invention to provide a system and method for enabling transfer of data from a supplier site to a RCW application which does not entail frame spoofing.

It is an object of the invention to provide a system and method for dramatically increasing the speed of operation of a navigation frame of a GUI.

Domino, IBM, the IBM Logo, Lotus, Notes, DB2 are trademarks of International Business Machines Corporation in the United States, other countries, or both.

SUMMARY OF THE INVENTION

A system and method for publishing a catalog. A flat file catalog is received via electronic data interchange (EDI) from a supplier and loaded into a relational database staging table. A buyer is granted audit control over selected fields in the staging table catalog, access to other fields is restricted. A relational database production table is updated from the relational database staging table; and user read access is granted to the relational database production table.

Other features and advantages of this invention will become apparent from the following detailed description of the presently preferred embodiment of the invention, taken in conjunction with the accompanying drawings.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a high level system diagram illustrating a clustered configuration of servers for performance and load balancing.

FIG. 2 is a diagram illustrating proxying out of URLs to clustered servers.

FIG. 3 is a system diagram illustrating a specific example of a clustered configuration of servers.

FIG. 4 is a diagram illustrating the frames comprising a typical screen display.

FIG. 5 is a diagram illustrating a specific instance of the display of FIG. 4.

FIG. 6 is a diagram illustrating ZIP code validation.

FIG. 7 is a diagram illustrating requisition catalog searching.

FIG. 8 is a diagram illustrating the mapping of commodity codes to accounting codes.

FIG. 9 is a diagram illustrating a commodity document.

FIG. 10 is a diagram illustrating a pyramid structure of application program interfaces.

FIG. 11 is a diagram illustrating the graphical, database, and business logic API's of the pyramid structure of FIG. 10.

FIG. 12 is a diagram illustrating the interaction of API's with each other, a database, and a browser.

FIG. 13 is a diagram illustrating an example configuration of API's.

FIG. 14 is a flow diagram illustrating the operation of the userProfile class of FIG. 13.

FIG. 15 is a system diagram illustrating the system of FIG. 1 for accessing a requisition catalog.

FIG. 16 is a system diagram illustrating a system for using a staged requisition catalog built from supplier flat catalog files.

FIG. 17 is a flow diagram illustrating the steps for receiving a supplier flat catalog.

FIG. 18 is a flow diagram illustrating the steps executed by an application server and database server for building and accessing a requisition catalog.

FIG. 19 is a diagram illustrating a user profile.

FIG. 20 is a diagram illustrating a Notes agent for building an HTML page from a DB2 table.

FIG. 21 is a diagram illustrating Notes agents for transferring data to an application browser session from a supplier window.

BEST MODE FOR CARRYING OUT THE INVENTION

1. Clustered Servers

In accordance with the preferred embodiment of the invention, a requisition catalog system (RCW, or Rec/Cat Web) is provided within a global web architecture (GWA) infrastructure. Such an architecture provides for the large number of clients required to assure good performance. In an exemplary embodiment, the requisition catalog application is deployed within the IBM web domain, which requires the use of GWA for clustering of W3 and www.ibm.com web sites. This architected solution assures a scalable Req/Cat Web application.

Referring to FIG. 1, client browsers 100 are connected to a GWA infrastructure including network dispatcher 102 and domino.go, a virtual cluster of Domino servers. Network dispatcher 102, sprays out or dispatches requests among configured servers S1, S2, S3 in virtual server cluster 104. Communications from cluster 104 are, in turn, dispatched (also referred to as sprayed out, distributed, proxy passed, or redirected) by network dispatcher 106 among servers S4, S5, and S6 in Domino cluster 112. While three servers are illustrated as configured in each of clusters 104 and 112, each cluster configuration may be scaled to any number of servers. External objects 108, which can be stored on a distributed file system (.dfs), include graphic files, Java files, HTML images, net.data macros, and other nsf files external to Domino, and in particular include configuration file proxy statements 110. In this instance, external objects 108 are stored on a .dfs and exist only once, so it is not necessary to replicate all of the external objects to each of the servers S4 S6. External objects 108, served in a .dfs are graphic files, Java files, anything that would live outside of the server files (also referred to .nsf files) S4 S6, including HTML images and net.data macros. These are part of the code implementing the Req/Cat Web application of the preferred embodiment of the invention, but are not part of Domino code, and are primarily for the GUI. By storing them outside of cluster servers 112, performance is improved.

In order to avoid potential bottlenecks on the clustered Domino servers 112 and in order to store a larger amount of data than is quickly searchable in Domino, a relational database 129, such as the IBM DB2 database, is used to store configuration data. Data is written by the clustered servers 112 by the end user, or by batched programs stored on application server 114 that are receiving data from back-end systems 116.

Referring to FIG. 2, in accordance with the invention, a proxy pass is used with both domino.go cluster 104 and Domino cluster 112. In accordance with a proxy pass, when a URL 120 is passed to network dispatcher 106, the NP processes that out and sprays it to any one of the configured servers. Spray means to distribute or map a URL 120 to any one of these configured servers S1 S3, S4 S6, which is the effect of mapping, as is represented by line 126, URL 120 to any of S4, S5, S6 in cluster 112. Examples of URLs include <w3.ibm.com/*>, <www.ibm.com/*>, and <w3.ibm.com/transform/reqcat/?opendatabase rccreate>.

In accordance with the invention, a unique architecture for a requisition catalog system includes a hybrid application using external objects 108 in a distributed file system off of the domino.go cluster 104 that works with network dispatcher 106 and the proxy pass capability 110 to redirect traffic to the Domino cluster 112.

These servers S4 S6 are mirror images: each has same .nsf files. Periodically, these servers replicate back and forth so that information is contained in all of them. Data is kept consistent.

In operation, when a client comes in through browser 100, his request can be directed to any domino.go server S1 S3 that determines the mapping of the URL request and what type of function is needed (displaying of images or code execution on S1 S3, or connecting to an .nsf server S4 S6 to display user data).

.nsf servers S4 S6 then feed requests to the application server 114, which in this embodiment is a backend Req/Cat Web (RCW) server to which all data gets replicated and where the bridges and agents run. Data gets replicated out to other back-end servers (DB2, MVS, SAP) 116 as needed. A bridge is an application that transfers data from one server to another server. An agent is an application that runs scheduled or by request to do some processing of data.

In an exemplary embodiment, Domino.go, or virtual server cluster, 104 is part of the GWA infrastructure. Any w3.ibm.com or www.ibm.com must go through a domino.go cluster 104. In accordance with the present invention a proxy out to the Domino cluster 112 is also provided. The purpose of this is to improve scalability and performance. Proxy statements 110 are used to ensure that the proper pieces of the application are invoked as appropriate, depending upon what the end user is doing. These statements are a mapping through a configuration file of URL 120 to any clustered server 112.

Referring to FIG. 3, a specific exemplary embodiment of the invention includes client browser 100 connected to network dispatcher (URL redirect) 102, which is connected to GWA shared GO cluster (W3-1.IBM.COM) 104. Cluster 104 is connected to external objects including OBI 117 and DFS 118, to dedicated DB2 server 129, and to network dispatcher (proxy) 106. Dispatcher 106 is connected to dedicated Domino "end user" cluster 112. Cluster 112 is connected to Blue Pages database 121 (an internal personnel database), dedicated DB2 server 129, dedicated Domino "application" server 114, and other Notes databases 119, including Skills Matching (an application for contracting technical services), AMNF (an application for identifying nonmanager requisition approvers), and public address book (PAB, for user login and authentication). Application server 114 is connected to FormWave 125 (an application that does approval form routing), PRISM/Copics 123 (which are requisition feeder systems on MVS and AS/400), SAP 382 (an ERP, or enterprise resource planning system, including an accounting application having an accounts payable function), dedicated content staging server 127 where an administration Notes client 128 runs, and dedicated DB2 server 129. The content staging server is used to update both nsf and DB2 configuration data, and is also connected to server 129.

The architecture of FIG. 3 presents a complex, scalable global procurement application (referred to as Req/Cat Web)implemented within Global Web Architecture (GWA). Req/Cat Web allows customers to generate on-line, general procurement requisitions. Customers interfacing client browser 100 can search through vendor catalogs to select items and fill in order information. Submitted requisitions are routed through an approval process using FormWave 125. Requisitions that have been approved are sent to a back-end system (SAP) 382, where a purchase order is cut and billing occurs. Customers can monitor their requisition status, as the back end sends status updates to the application 114.

Technologies utilized in building Req/Cat Web include the following: Domino.Go 104 provides a proxy passing function and caching facility. Lotus Notes/Domino applications are used for their security and workflow capabilities. DB2 applications provide rich relational database functions and data management. Net.Data is used for its catalog searching functionality. Javascript is used for GUI presentation and data verification. Req/Cat Web application code sits on DFS 118, on the Domino cluster servers 112, and on Domino application server 114.

Architectural elements include load balancing, file storage, end-user front end (which reside in the Domino cluster servers 112), back-end processing, external dependencies, and use of frames.

For load balancing, Req/Cat Web uses the GWA proxy pass architecture, documented in the presentation currently available on the Notes/Web application CoC Web site at //w3.coc.ibm.com/coc/cocweb.nsf/Homepages/gwatrain.html.

Network dispatchers 102, 106 are used between the client 100 and the Domino.Go cluster 104 and also between the Domino.Go cluster 104 and Domino cluster 112 to automatically balance the load of http requests among servers S1 S3 and S4 S6. Configuration file (httpd.conf) 110 contains the proxy statements that are used for redirections. When a client 100 enters the url (w3.ibm.com/transform/reqcat) and Network dispatcher 106 redirects the client to the appropriate server 112, the redirection is transparent to the client.

Workload is split between Domino.Go 104 and Domino 112 to improve performance.

File storage is provided by dynamic file system (.dfs, or DFS) 118, which contains javascript files (.js), html, images, and net.data macros. Domino servers 114 contain navigation, configuration, create requisition, open requisition, requisition invoice paid, requisition archive, cost center, confirmations, and help databases. DB2 server 129 contains tables including: confirmations, user profiles, zip codes, accounting data, commodity configuration data, buyer information, routing, and catalogs.

An end-user front end for Req/Cat Web uses Domino authentication to permit login to the application. The client uses his Lotus Notes ID and pre-set http password to "authenticate". When a client logs in at browser 100, the Domino servers 112 (S4 S6) are configured to check the name and password in public address book 119, which is a designated server within GNA. In a further exemplary embodiment, secure login function may be provided through the implementation of digital certificates. Data is retrieved from DB2 129 using LC:LSX calls via Notes agents, or Net.Data. Net.Data is used for a catalog searching and drill-down function. Java APIs are used for information retrieval from BluePages 121.

As much processing as possible is performed asynchronously on the back-end, application server 114. Bridge jobs are scheduled on two levels: system level (CRON) and notes level (Agents). Agents run periodically, say hourly, to process requisitions and send them to SAP 382.

Other agents are scheduled off-peak, where ever possible. External dependencies include FormWave for form approval routing, BluePages for personal data for user profile creation and approver changes, interfaces to Open Buying on the Internet (OBI) server and skill matching applications, and SAP for receiving requisitions for purchase order (PO) creation and processing. Information returned by SAP to Req/Cat Web includes requisition status, PO/RFS status, PO history, negative confirmations, currency codes, and configuration information.

By using frames, a large majority of preprocessing can be performed dynamically, on the client, reducing the number of trips back and forth to the server. This is a tremendous boost to performance. The web screen described hereafter is not he result of a Notes form, but rather of a dynamically generated HTML/javascript code produced by a displayReqHeadero function. This function dynamically writes html and javascript code into the content frame of the application. The javascript function is coded in a displayreq.js file stored on the filesystem and loaded into a jsCode frame by a source (<script src="./js/displayreq.js">) command in a jscode.html file at the time when the initial frameset is loading. A displayReqHeader( ) function is called from several places in the application to redisplay the requisition information in the content frame. This screen is called any time a WebReq Lotus Notes form is opened by an OpenForm command for a new requisition, or by an OpenDocument command when an existing document is opened. OpenForm occurs when the displayReqHeader( ) javascript function is called as the last part of an OnLoad event coded in the HTML-Attributes property of the WebReq form. Any time an existing document is opened that was saved with Form-WebReq, the OnLoad event causes the displayReqHeader( ) javascript to be run to OpenDocument. Any time a content frame has been loaded with some other page during the processing of a requisition, and the user performs an action to return to the requisition in process, the displyReqHeader( ) javascript function is called directly. This form reads the information stored in reData frame and dynamically fills the content frame with this screen.

Referring to FIGS. 4 and 5, as will be more fully described hereafter, a screen display includes header frame 470, navigation frame 472, footer frame 474, temporary data frame 476, request data frame 478, and content frame 480. The tempData frame 476 is used as a temporary holder for information, and to direct calls dynamically, while keeping the current data in the screen, and making the return data available to the application.

Table 1 shows, for the exemplary embodiment of FIG. 3, the software loaded on each of the servers used for the Req/Cat Web application.

TABLE-US-00001 TABLE 1 SERVERS AND SOFTWARE 1 Server Description Software 2 Domino.Go Servers S1 S3 AIX 4.3.2 3 Domino Go 4.6.2.6 with Denial 4 of Service Fix 5 Net.Data 2.0.8 6 DB2 CAE 5.2 7 DFS Client 8 Java Runtime 1.1.6 9 Domino End-User Servers S4 S6 AIX 4.3.2 10 Domino 4.6.4 11 DB2 CAE 5.2 12 Java Runtime 1.1.6 13 Domino Application Server 114 AIX 4.3.2 14 Domino Go 4.6.2.6 with Denial 15 of Service Fix 16 DB2 CAE 5.2 17 Java Runtime 1.1.6 18 Mercator 1.4.2 with Svc Pack 3 19 Hith Test API 20 Lotus VIM 21 C++ 3.6.4 22 UDB Server 129 AIX 4.3.2 23 UDB 5.2 24 Java Runtime 1.1.6 25 Content Staging Server 127 AIX 4.3.2 26 Domino 4.6.4 27 DB2 CAE 5.2 28 Java Runtime 1.1.6

2. Front End

The Req/Cat Web front end provides several validation routines, including ZIP code validation, catalog search criteria, and chart of account validation. In the architecture of FIGS. 1 and 3, various programs, including ZIP code validation, catalog search and chart of account validation routines reside in application server 114, and the data tables, including the chart of accounts, reside in the relational database 129.

ZIP code validation is provided to assure that the tax department is provided the information needed implement the correct tax rules on purchase orders in SAP 382.

Chart of accounts validation includes the mapping of commodity codes to account codes. This is done to insure that all general ledger accounts associated with commodities are correct, and within this to insure that a description from SAP 382 is available for use by a customer to select correct general ledger (GL) account when doing financial validation on a requisition.

Referring to FIG. 6, for ZIP code validation, when a purchaser access RCW, a user profile 130 is accessed. Profile 130 includes many defaults, one of which is delivery information (defaulted to all line items of requisition). One of the fields in profile 130 is ZIP code. When the purchaser enters his ZIP code, RCW searches ZIP code database 134, a database for ZIP codes which is fed periodically, say nightly, from the enterprise tax system 136. This same validation routine continues by creating a requisition 132 with item options, including deliver to information with a zip code field. The requester can change the deliver to information, 132, but any time it is changed, the ZIP code is checked against ZIP code database 134. Whereas previously, customer input of ZIP code was accepted without checking. By this invention, ZIP code validation is performed at the front end by a java or SQL program call to DB2 database 134. Responsive to entry of ZIP code on a requisition or to the changing of delivery information which includes ZIP code on a requisition line item, the ZIP code is validated against a database of valid ZIP codes. In an alternative embodiment, the ZIP code database is refreshed from a trusted source, and the entered or changed ZIP code is checked for valid match with respect to state and city.

A create requisition request goes to catalog search, which used to search by part number or description. Previously, this was a very limited search to just the catalogs. A search argument of % pen % was not a very crisp search for the customer. In order to improve the catalog search, in accordance with the present invention, searches may be conducted against a longer description and files up to 255 characters. Screen down searches are provided for sub-commodity. Wild card searches used to require %, but now assumes a wild card search in all cases. Searches are also provided against subcommodity. As a result, catalog searches now reference short description, long description, and catalog sub-commodity. A database catalog includes part number, short description, long description, oem part number, commodity code. Newly added is subcommodity.

Referring to FIGS. 7 and 9, the method of the preferred embodiment of the invention is described for managing a chart of accounts 140. When creating a commodity list, which includes expense, capital, and resale accounts 142, commodities descriptions 180 are pushed to the correct commodity group. The resulting chart of accounts 140 is available from SAP 382. Previously an administrator had input a chart of accounts. Now, a company administrator, for example, may select from commodity accounts 142 the expense field, which results in drop-down display of a valid chart of accounts 144 from SAP chart of accounts 140 with account numbers 148 and commodity descriptions 146. The company administrator may then select from that valid chart of accounts 144 the correct commodity to push to company/commodity document 150.

Referring to FIG. 8, the process for a requester to create requisition is set forth. By way of example, a requester creates a requisition by doing in step 154 a search for "supplies", which will bring to him in step 156 a display presenting commodity W14, and thence in step 158 to a catalog (for example, a Staples catalog) which includes commodities (pens, erasers, calendars), from which the requester can create several line items. Upon selecting "proceed to accounting", the requester is presented a financial summary 160 including commodity code w14 pens for line item 1, w14 erasers for line item 2, and w14 for line item 3 calendars. The user may then request display of financial worksheet 162. In this window 162, the requester will see a title 164 expense, which can be changed, for example, by toggling to other categories, such as balance sheet. Selecting G/L account 166 may drop down a list showing several account codes 168 and related descriptions 170, depending upon what the company administrator has pushed to the commodity document 150 from which financial worksheet 162 is derived. Previously, a requester was provided in worksheet 162 one account code choice without description. By this invention, the requester is provided correct general ledger account codes and descriptions, resulting in less miscodes, more correct ledger entries, and correct SAP account codes. This improved general ledger account selection process avoids back end processing to correct erroneous entries.

Thus, in accordance with a preferred embodiment of the invention, a method is provided for creating a valid chart of accounts from which an administrator 184 can facilitate and enable a requisitioner to select a valid general ledger account. First, there is push from an enterprise (erp) system a chart of accounts 140 with descriptions to a req/cat system database. The administrator selects from req/cat system database valid accounts with descriptions for a given commodity and purchase time period, and then pushes the selected account/description tuple to the company commodity groups, thus completing the setup of the commodity documents 150 to be used in the requisition creation process. A company commodity document 150 created by administrator 184 may include for each commodity code under each company, commodity code 152, which is a very broad catagory, short description 190, long description 191 (from procurement organization 182), key words 192, approvers 193, financial information 194 (including purchase type 198, and general ledger account 199), route-to buyer 195 (by plant association), preferred supplier 196 (which associates the commodity code to a catalog 158), and special handling code 197 (with drop list including, for example, skills matching, obi, administrative services)--all used to drive the customer to the correct commodity.

To create a requisition, a user searches against commodities and catalogs in commodity description documents 150, which may be Notes documents or DB2 records, and creates one or more line items. These searches may be done by catalog and non-catalog, and driven based on descriptions entered by requester. A hierarchy of families may be provided as an alternative search approach. The requisitioner initiates a proceed to accounting process, which displays line items which may be selected by requisitioner; and then displays a financial worksheet created by a Java agent with fields which need to be selected or populated by the requisitioner from the company commodity document, based on purchase type, and which presents valid general ledger accounts numbers and descriptions to the requisitioner.

3. Back End

Referring to FIG. 12, in a large enterprise, the requisition catalog requires a very large database. In accordance with the preferred embodiment of the invention, such a large data repository is provided by the using the IBM DB2 relational database 210. Other possible databases include Oracle, Sybase, and MSSQL.

Lotus Notes databases are built upon an object model and classes: databases, views, and documents are classes used to access Notes data. But, these classes are set to be final and not extendible, and a Req/Cat Web database must be extendible.

Consequently, referring to FIG. 12, in accordance with an embodiment of the present invention, DB2 access routines 208 are provided for accessing DB2 data 210. The Req/Cat Web application executes Lotus code, with access controlled on the code, and data obtained from and written to relational database 210.

Normally, Notes saves all data as documents. There is a save method provided for that purpose. In accordance with a preferred embodiment of the invention, the Notes save method is intercepted and stopped, and execution passed to Req/Cat Web code for saving data DB2 database. In the same way, execution of a Notes open method is intercepted and stopped, and then Req/Cat Web code executed to pull information in from the DB2 database.

Lotus Notes provides for web applications, and supports methods called webqueryopenagent, and webquerysaveagent. Notes also provides a saveoptions parameter. Setting saveoptions to zero tells Notes not to save a document.

In accordance with the preferred embodiment of the invention, saveoptions is set to zero, and webquerysaveagent used to save data in a DB2 database. The webquerysaveagent is written in Lotus Script, and calls Lotus Script extensions and also can also call its own APIs 200, 202, including the database api's 208. At this point, Req/Cat Web haves full control, and can save one or many tables, can explode the data model and write many tables.

On the other side, instead of editing an existing document as is done with Notes, Req/Cat Web executes createnewdocument. As the document opens, Lotus Notes gives the query webqueryopenagent, and this is also written in Lotus Script, which has access to data base api's 202, where data from many database 210 tables may be read to construct a Lotus document from DB2 data.

This configuration involves some naming standards and a hierarchy of interfaces. By way of example, database access routines are, by convention, data application programming interfaces (DAPI) 208. These are routines for accessing data 210 outside of the Req/Cat Web application. Referring to FIG. 10, a hierarchy of application program interfaces (APIs) includes core API's 200 containing everything necessary to connect to database 210. Next in hierarchy, to access specific data, are database API's (DAPI) 208, which interface to a single piece of data (such as company or employee information.) Below these rest the business logic code 204.

In a programming environment, the top of pyramid represents the work of a core DB2 programmer. Below him are those people who use core DB2 API's and write, for example, DAPI's 208 to access individual DB2 tables within database 210, for example API's for countries. These core APIs 200, therefore, include a GET method, and update, insert, and delete routines. Third level 204 represents the application programmer who only needs to use these methods (ie, company dapi: IBM US) in their business logic 204 applications, including ability to update, for example.

Referring to FIG. 11, this same pyramid is used to implement graphical APIs 206 on the user interface 212, business APIs 207 on the business logic 204 interface, and database APIs 208 on the database 210 interface. This illustrates that duties of programers can be separated, so that everyone need not know the complexities of the entire system. That is, some developers work on data manipulation, others on the user interface, and still others on business logic. The application developer need not know the names of the actual database, tables, or fields, or even how to access them. This also enables a DB2 administrator to alter a table, and only affect the one DAPI developer that wrote the specific table DAPI 202. All code is one routine, so changes to the database need only affect one piece of logic.

Referring to FIG. 13, an example of this API implementation is illustrated. CoreDB2 220 is the core API 200 to connect to DB2 210. It contains two classes, configuration class DB2 Config 222 and base class DB2 Base 224.

Calling DB2 Config 222 determines database name 230, user identifier 231, and password 232--information that the database requires to establish a valid connection, and is passed to DAPI 208 for making that connection. (Without this method, user IDs and passwords would have to be hard coded in the application.)

DB2 Base 224 is extended by the DAPI 208 programmer for each DAPI 202 instance that is needed. It contains methods 240, 241 for connecting to and disconnecting from the database, a method 242 for defining the number of rows to return at a time, a method 243 for getting the next group of records, methods for reads 244, inserts 245, updates 246, and deletes 247, commit 248 and rollback 249 options, and a flag 250 to determine if all data has been retrieved.

DAPIUserProfile 226 is a class for retrieving or updating information about an employee. It extends DB2Base 224 so the application 204 developer would not have to write the logic to access DB2 database 210, but could concentrate on the information about the employee. The DAPI 202 developer would need to know about the employee table (table and field names, for example) and would implement methods for selecting and displaying data. In an exemplary embodiment, DAPIUserProfile class methods include the following: 260 selectEmployeeByEmplID(employeeID,companyCode, countrycode) 261 selectEmployeeByWebID(employeeWebID) 262 selectEmployeeBothWays(employeeID,companyCode, countryCode,employeeWebID) 263 selectEmployeeByName(lastName,firstName) 264 insertEmployee(columnNames,DB2 ColumnValues) 265 updateEmployeeByEmpID(employeeID, employeeCompanyCode,employeeCountryCode, UpdateNameValues . . . ) 266 updateEmployeeByWebID(employeeWebID, UpdateNameValues,UpdateByUserID) 267 deleteEmployeeByEmpID(employeeID,companyCode, countryCode,UpdateByUserID) 268 deleteEmployeeByWebID(employeeWebID, UpdateByUserID) 269 deleteEmployeesWhere(Condition, UpdateByUserID) 270 clearTable( )

Once the data is selected, a few of the properties that are available for a give employee include empWebID, empLastName, empFirstname, empIntPhoneNum, empExtPhoneNum, empEmailID, empID, empCopanyCode,empCountryCode, and empCountryName.

An application 204 like the human resources (HR) application would then need to read HR data and insert it into the employee table if the employee did not exist, or update it if something changed, or delete it if the employee no longer exists. This application developer would then only have to know the methods and properties of the userProfile class 226 in order to write the application. An example of such an application is set forth in Table 2, with reference to the steps of FIG. 14. This table sets forth the HR load routine, a batch program to read HR data from a flat file and insert it into the DB2 user profile table.

TABLE-US-00002 TABLE 2 EXAMPLE APPLICATION 1 `in the following step, instantiate a DB2 config 2 object, and call it db2; the database name 230, etc., 3 is determined by instantiating the db2 config object, 4 as defined by the core programmers` 5 272: Dim db2 As New DB2Config(session) 6 `the database information is known, and can be passed 7 to the employee profile.` 8 274: Print "The target DB2 database alias is "& 9 db2.getDB2DatabaseName( ) ) 10 `All that must be done is to pass the DB2 class to the 11 userProfile.` 12 276: Dim eps As New userProfile(db2) 13 `Delete everything from the employee table to start the 14 bridge.` 15 278: I headerDivision = "" Then 16 Call eps.clearTable( ) 17 Else 18 Call eps.deleteEmployeesWhere("COGRP_CD= "" 19 &headerDivision &"") 20 End If 21 280: For count = 2 To records-1 22 `Read the next record and make sure that it can be 23 loaded without problems` 24 If ReadInputFile(inputFileNum, count, userid, al, cl 25 eps, cci) Then 26 Call eps.insertEmployee(DB2ColumnNames, 27 BuildDB2ColumnValues( ) ) 28 db2kAdditions = db2Additions+1 29 End If 30 Next . . .

In Table 3, a pseudo code example of use of the webquerysaveagent process is illustrated.

TABLE-US-00003 TABLE 3 WEQUERYSAVEAGENT EXAMPLE 1 Dimension db2 As New DB2Config(session) 2 Print "The target DB2 database alias is "& 3 db2.getDB2DatabaseName( ) ) 4 Dimension eps As New userProfile(db2) 5 execute process 260 to selectemployee by employee id 6 if employee does not exist, then 7 execute process 264 to insert employee 8 else if employee changed, then 9 execute process 265 to update employee 10 else (employee not changed) information to user 11 "employee not changed" 12 no save 13 endif

In the example of Table 3, an application programmer 204 is using a dapi written by programmers 202.

In this manner, the relational database 210 is used as the data source, instead of a Notes database, in a way that hides the complexities of DB2 database programming. That is, in a fashion to similar Notes programming--the idea is to allow a Notes programmer to use a familiar looking class 226 to load and save data.

4. Catalog Administration

In accordance with the preferred embodiment of the invention, a requisition catalog administration function provides control, audit, and publishing procedures for flat files received from suppliers.

Referring to FIG. 15, a system architecture for implementing catalog administration includes a requester browser 410, a buyer browser 412, with net.data connections 391 and 393 to a dedicated DB2 server and DB2 database 390 having a staging table 392 and a production table 394 through network dispatcher 102 and Go cluster 104. Go cluster 104 is also connected through network dispatcher 106 and Domino cluster 112 to Domino application server 114. A buyer 412 accesses staging table 392 via net.data connection 391, and a requester 410 accesses the production 394 table via net.data connection 393. This connection 391, 393 is implemented as a single path, and the requester and buyer provided different levels of authority to access different tables 392, 394 in DB2 390 over that same path. Buyer 412 can change selected fields in the staging table 392 and can update production table 394 from staging table 392. Requester 410 can only view (not change) the production table 394. The buyer at browser 412 is controlled by a GUI which contains access control list (ACL) control on fields, and edit authority for catalog access.

Referring to FIG. 16, this architecture further includes a catalog flat file 314, an application program 384 within application server 114, catalog administration function 386, Req/Cat Web function 388, and WEB communications 396 and 398 connecting a catalog administration function with ACL control 400 and requester 402 to database 390.

In operation, catalog flat file 314 is received by application server 114 through firewall 380 via electronic data interchange (EDI) and loaded into DB2 database 390 by application program 384. Catalog administration function 386 specific users 400 audit control over certain fields in staging table 392, and publishes the catalog data to the live, or production, system 394. Function 386 presents to buyer 400 a staging table 392 with a GUI front end, with selected fields enable and other fields not enabled to be personalized.

Catalog file 314 is a flat file containing catalog items in a column delimited format specified to supplier 300 by the enterprise.

Application server 114 manages database 390 containing staging table 392 and production table 394. A catalog file 314 comes to application server 114, which includes a program 384 for moving data from that flat file to staging table 392. See Table 7.

A buyer at terminal 400 accesses the staging table 392 on the web 396. He views catalog items and enters transactions with action button which transfers information from staging table 392 to production table 394. Production table 394 is referenced by req cat web 388, and staging table 392 is referenced by the catalog administration function 386 operated by the buyer 400. Typically, a buyer is member of procurement organization with responsibility for negotiating deals with suppliers. A requester 402 accesses production table 394 over web 398 to create and submit a requisition to SAP 382.

In accordance with the preferred embodiment of the invention, control over what buyer 400 can change is provided by a GUI in a process which loads a catalog 392 from a supplier into a production system 394.

Catalog files 314 come in from suppliers in an enterprise defined standard format, and the access to fields in that format is hard coded into the catalog administration function 386. Application server program 384 has error checking functions to assure validity of a catalog 314 from a supplier 300. Buyer 400 accesses staging table 392 through a catalog administration function 386 which has hard coded into it the access controls on the various fields in the catalog format. Production table 394, which is accessed by the requester 402, is updated periodically (upon buyer actuation) from the staging table 392.

Implementation of the invention involves several code procedures: there is a program 384 which loads a file 314 that is received via electronic data interchange (EDI) into a table 392 in the DB2 application (throughout this specification reference to the DB2 application is to its Version 5 or later). There are routines 388 which allow a buyer 400 to browse certain catalogs in the staging table 392 and change certain fields while being inhibited from changing others. And there are the routines 386 which take the approved catalog and migrate the data from the staging DB2 table 392 to the production DB2 table 394. Table 7 is an example of one such routine.

Referring to FIG. 17, a preferred embodiment of these processes are presented. In supplier system 300, supplier source data 310 is extracted and reformatted in step 312 to create catalog flat file 314 in the format specified by the enterprise. In step 316 that flat file is transmitted to the enterprise 302, as is represented by line 305, where it is accepted in step 320 into the enterprise electronic data interchange (EDI) mailbox 322. In step 324, the data in the flat file in mailbox 322 is reformatted and put into generation data group (GDG) 328, a location for saving more than one file, so as to retain the last N iterations, and a archive entry made to processing log 326. In step 330, a delivery component executes to send data from GDG 328 to application server 114, as is represented by line 303, in the form of catalog flat file 340. In step 342, a delivery component receives the flat file and, as is represented by line 347, starts job scripts including MASSLOAD for reading the flat file and loading staging table 392, and as represented by line 345 alerts the buyer 352.

As is represented by lines 311, 313 and 315, respectively, MASSLOAD 344 accesses database server