System and method for validating data record

The present invention discloses a system and associated method for utilizing data stored in a database and/or databases for a business application.

Conventional business application databases are updated due to transactions over time, but validating data stored in conventional business application databases requires multiple retrieval and complex administration of each database. Thus, there is a need for a system and associated method that overcomes at least one of the preceding disadvantages of current methods and systems for data validation.

The present invention provides a method for utilizing a record stored in a database, the method comprising:

receiving a request from a requester application for validating the record, the record comprising at least one attribute having a respective value provided in the record;

subsequent to said receiving, validating the record resulting in a validated record, wherein the database comprises at least one configuration table corresponding to said at least one attribute, wherein each configuration table contains a set of valid values for each attribute, wherein the record is validated if each attribute in the record is either valid or invalid, wherein a first attribute is valid if the first attribute has a first respective value that is contained in a first configuration table for the first attribute, wherein the first attribute is invalid if the first attribute has the first respective value that is null;

subsequent to said validating, creating a record object with the record from said validating, wherein the record object comprises said at least one attribute and a validity flag per attribute; and

subsequent to said creating, sending the record object to the requester application,

wherein said receiving, said validating, said creating, and said sending are performed by a data validator.

The present invention provides a computer program product, comprising a computer usable medium having a computer readable program code embodied therein, said computer readable program code containing instructions that when executed by a processor of a computer system implement a method for utilizing a record stored in a database, the method comprising:

receiving a request from a requester application for validating the record, the record comprising at least one attribute having a respective value provided in the record;

subsequent to said receiving, validating the record resulting in a validated record, wherein the database comprises at least one configuration table corresponding to said at least one attribute, wherein each configuration table contains a set of valid values for each attribute, wherein the record is validated if each attribute in the record is either valid or invalid, wherein a first attribute is valid if the first attribute has a first respective value that is contained in a first configuration table for the first attribute, wherein the first attribute is invalid if the first attribute has the first respective value that is null;

subsequent to said validating, creating a record object with the record from said validating, wherein the record object comprises said at least one attribute and a validity flag per attribute; and

subsequent to said creating, sending the record object to the requester application,

wherein said receiving, said validating, said creating, and said sending are performed by a data validator.

The present invention provides a computer system comprising a processor and a computer readable memory unit coupled to the processor, said memory unit containing instructions that when executed by the processor implement a method for utilizing a record stored in a database, the method comprising:

receiving a request from a requester application for validating the record, the record comprising at least one attribute having a respective value provided in the record;

subsequent to said receiving, validating the record resulting in a validated record, wherein the database comprises at least one configuration table corresponding to said at least one attribute, wherein each configuration table contains a set of valid values for each attribute, wherein the record is validated if each attribute in the record is either valid or invalid, wherein a first attribute is valid if the first attribute has a first respective value that is contained in a first configuration table for the first attribute, wherein the first attribute is invalid if the first attribute has the first respective value that is null;

subsequent to said validating, creating a record object with the record from said validating, wherein the record object comprises said at least one attribute and a validity flag per attribute; and

subsequent to said creating, sending the record object to the requester application,

wherein said receiving, said validating, said creating, and said sending are performed by a data validator.

The present invention provides a process for supporting computer infrastructure, said process comprising providing at least one support service for at least one of creating, integrating, hosting, maintaining, and deploying computer-readable code in a computing system, wherein the code in combination with the computing system is capable of performing a method for utilizing a record stored in a database, the method comprising:

receiving a request from a requester application for validating the record, the record comprising at least one attribute having a respective value provided in the record;

subsequent to said receiving, validating the record resulting in a validated record, wherein the database comprises at least one configuration table corresponding to said at least one attribute, wherein each configuration table contains a set of valid values for each attribute, wherein the record is validated if each attribute in the record is either valid or invalid, wherein a first attribute is valid if the first attribute has a first respective value that is contained in a first configuration table for the first attribute, wherein the first attribute is invalid if the first attribute has the first respective value that is null;

subsequent to said validating, creating a record object with the record from said validating, wherein the record object comprises said at least one attribute and a validity flag per attribute; and

subsequent to said creating, sending the record object to the requester application,

wherein said receiving, said validating, said creating, and said sending are performed by a data validator.

The present invention provides a method and system that overcomes at least one of the current disadvantages of conventional method and system for validating data in application database.

FIG. 1 illustrates a system for validating business data, in accordance with embodiments of the present invention.

FIG. 2 is a flowchart depicting a method for the data validator of FIG. 1 , in accordance with the embodiments of the present invention.

FIG. 2A is a flowchart depicting a method for the query process performing step 1302 of FIG. 2 , in accordance with the embodiments of the present invention.

FIG. 2B is a flowchart depicting a method for the object process performing step 1303 of FIG. 2 , in accordance with the embodiments of the present invention.

FIG. 2C is a flowchart depicting a method for the object process performing step 4003 of FIG. 2B , in accordance with the embodiments of the present invention.

FIG. 3A illustrates data of the business application represented in tables in the business application database, in accordance with the embodiments of the present invention.

FIG. 3B illustrates a record object table in a database representing a record object that is returned to a requester application in step 1304 of FIG. 2 , after performing the method of the present invention over tables illustrated in FIG. 3A , in accordance with the embodiments of the present invention.

FIG. 4 illustrates a query in Structured Query Language (SQL) to attain a record object table of FIG. 3B , in accordance with the embodiments of the present invention.

FIGS. 5A , 5 B, 5 C and 5 D illustrate example Java® program codes of the data validator, in accordance with embodiments of the present invention.

FIG. 6 illustrates a computer system used for utilizing a record stored in a database, in accordance with embodiments of the present invention.

FIG. 1 illustrates a system for validating business data, in accordance with embodiments of the present invention.

A business data validating system 100 comprises a business application 110 , a business application database 120 , and a data validator 130 . The data validator 130 comprises a data validator interface 200 , a query process 300 , and an object process 400 .

The business application 110 has multiple business objects that represent data entities and/or business transactions that are accommodated by the business application 110 . Examples of the business application 110 include, inter alia, a procurement requisition system, etc. Examples of conventional procurement applications may include, inter alia, Ariba® and SAP SRM (Supplier Relationship Management). Examples of business objects include, inter alia, a requisition item, a requisition, a purchase order item, a purchase order, an invoice item, an invoice, etc.

The business application data base 120 comprises a record that represents data entity and/or a business transaction necessary to perform the business application 110 . Each record has attributes to describe properties of the record. An attribute configuration table 120 A comprises a set of valid data values for the attribute that is available for the business application 110 . For example, if the attribute is a currency, a currency configuration table in the database has all valid currency values that a business transaction may use. See FIG. 3A for examples.

The business application 110 modifies records stored in the business application database 120 while performing transactions. Thus, a set of valid data values for an attribute changes over time. The attribute configuration table 120 A is updated accordingly by a database administrator or by an automated update process. However, there may be a data value in the business application database 120 that has become invalid due to a recent transaction of the business application 110 . To assure validity of the data value stored in the business application database 120 , the attribute configuration table 120 A must be consulted on all attributes of a business object. When the business application 110 attempts to use a business object, it is also necessary to validate data values of all attributes that describes the business object. Thus, the business application 110 sends a request for data validation to the data validator 130 . The method of the present invention validates data values for all attributes of the business object against the attributes configuration tables 120 A upon a request of the business application 110 .

The data validator interface 200 is an interface between the business application 110 and the data validator 130 . The data validator interface 200 processes and transfers a data validating request from the business application 110 to the query process 300 and receives a business object instantiated with validated data from the object process 400 . The data validator interface 200 returns the business object to the business application 110 .

The query process 300 interacts with the business application database 120 . The query process 300 executes a query upon the business application database 120 to retrieve validated data that has been requested by the business application 110 . Then the query process 300 passes a result of the query to the object process 400 .

The object process 400 analyzes the validated data received from the query process 300 and instantiates a business object with the validated data. By examining a value associated with an attribute in the business object, the object process 400 marks an invalid attribute in the business object. The object process 400 passes the business object as the result of the data validating request to the data validator interface 200 which will forward the business object to the business application 110 .

In one embodiment of the present invention, the data validator 130 is programmed in Java® language and a query of the query process 300 is written in Structured Query Language (SQL).

FIG. 2 is a flowchart depicting a method for the data validator of FIG. 1 , supra, in accordance with the embodiments of the present invention.

In step 1301 , the data validator interface of the data validator receives a request for data validation from a business application. The data validator interface processes and transfers the received request to the query process.

In step 1302 , the query process of the data validator executes a query against a business application database to service the request. The query process of the data validator uses a query to validate all data attributes that are spread in multiple records. See FIG. 2A , infra, for details of the query process.

In step 1303 , the object process of the data validator processes a result of the query executed in step 1302 . The object process instantiates the business object and flags valid data based on the result of the query. The business object is programmable with modules that perform specific functions on the business object. See FIG. 2B , infra, for details of the object process.

In step 1304 , the data validator interface of the data validator receives the business object instantiated in step 1303 and returns the business object to the business application that had sent the request for data validation.

FIG. 2A is a flowchart depicting a method for the query process performing step 1302 of FIG. 2 , supra, in accordance with the embodiments of the present invention.

In step 3001 , the query process receives a request for data validation from the data validator interface.

In step 3002 , the query process determines whether a business object comprises any sub-objects in the business object. A sub-object of a parent business object is also an object that is a component of the parent business object. The sub-object comprises an attribute. For example, a requisition object comprises multiple requisition item objects, and each requisition item object comprises a supplier attribute and an item code attribute. If the query process determines that a business object has any sub-objects in the business object, then the query process proceeds with step 3003 . If the query process determines that a business object does not have any sub-objects in the business object, then the query process proceeds with step 3004 .

In step 3003 , the business object is a parent business object comprising sub-objects. The query process executes a query upon the business application database to retrieve validated data values for all attributes of all sub-objects of the parent business object. If an attribute of a sub-object has an invalid data value, the query process sets a data value for the attribute to a null value. If an attribute of a sub-object has a valid data value, the query process uses the valid data value.

In step 3004 , the business object comprises only attributes without any sub-object. The query process executes a query upon the business application database to retrieve validated data for all the attributes of the business object. If an attribute of the business object has an invalid data value, the query process sets a data value for the attribute to a null value. If an attribute of the business object has a valid data value, the query process uses the valid data value.

In steps 3003 and 3004 , the query is constructed to retrieve data values of multiple attributes from the business application database. The query adds a validity field for each attribute to be validated. A result of the query comprises a same number of records as a number of primary keys that identify records.

In step 3005 , the query process produces a result of the query in step 3003 or step 3004 . The query process provides the result of the query as an input to the object process.

FIG. 2B is a flowchart depicting a method for the object process performing step 1303 of FIG. 2 , supra, in accordance with the embodiments of the present invention.

In step 4001 , the object process receives the result of the query from the query process.

In step 4002 , the object process determines whether the business object has sub-objects within the business object. If the object process determines that the business object has sub-objects within the business object, then the object process proceeds with step 4003 . If the object process determines that the business object does not have sub-objects within the business object, then the object process proceeds with step 4004 .

In step 4003 , the object process instantiates all sub-objects, marks validity of each sub-object, and subsequently groups sub-objects into a sub-object list. See FIG. 2C , infra, for details.

In step 4004 , the object process instantiates business objects that comprise attributes and validity fields of each business object.

In step 4005 , the object process flags all valid attributes that have a non-null value in a validity field of the attribute in the business object.

In step 4006 , the object process flags each business object as valid if any attribute of the business object is valid.

In step 4007 , the object process determines whether a business object has any sub-objects in the business object. If the object process determines that a business object has any sub-objects, then the object process proceeds with step 4008 . If the object process determines that a business object does not have any sub-objects, then the object process proceeds with step 4010 .

In step 4008 , the object process inserts the sub-object list from step 4003 into the business object. In step 4009 , the object process flags the business object indicating that the business object comprises sub-objects.

In step 4010 , the object process returns the business object to the data validator interface, which returns the business object to the business application.

FIG. 2C is a flowchart depicting a method for the object process performing step 4003 of FIG. 2B , supra, in accordance with the embodiments of the present invention.

In step 4003 A, the object process instantiates sub-objects that comprise attributes and validity fields of each sub-object.

In step 4003 B, the object process flags all valid attributes that have a non-null value in a validity field of the attribute in each sub-object.

In step 4003 C, the object process flags each sub-object as valid if any attribute of the sub-object is valid.

In step 4003 D, the object process groups all sub-objects flagged as valid into a sub-object list.

In step 4003 E, the object process flags the sub-object list as valid if any sub-object in the sub-object list is flagged as valid.

FIG. 3A illustrates data of the business application represented in tables in the business application database, in accordance with the embodiments of the present invention.

In one embodiment, a business application is a software performing requisitions. The requisition software has a record object Requisition. A record is stored in a form of table in a requisition database. A requisition item record is stored in a REQITEM table 610 . Each column of the REQITEM table represents an attribute of the REQITEM table 610 . The embodiment of this example does not comprise a sub-object, but comprise only attributes.

A first attribute REQID of the REQITEM table 610 represents an identifier of a requisition. A second attribute REQITEMNUM represents a requisition item that is transacted in the requisition. A third attribute CURR_CD represents a currency code that is used in the requisition item. A fourth attribute UOM_CD represents a unit of measure code that is used in the requisition item. A fifth attribute CTRY_CD represents a country code that is used in the requisition item.

A set of valid values for the currency code attribute is stored in a CURRENCY table 620 . A set of valid values for the unit of measure code is stored in a UOM table 630 . A set of valid values for the country record is stored in a COUNTRY table 640 .

FIG. 3B illustrates a record object table in a database representing a record object that is returned to a requester application in step 1304 of FIG. 2 , supra, after performing the method of the present invention over tables illustrated in FIG. 3A , supra, in accordance with the embodiments of the present invention.

A first element REQID of the OBJECT table 650 represents an identifier attribute of a requisition. A second element REQITEMNUM represents a requisition item attribute that is transacted in the requisition. A third element CURR_CD represents a currency code attribute that is used in the requisition item. The CURR_CD attribute in the REQITEM table 610 comprises three values of USD, RUP, and SUR. The CURRENCY table 620 that comprises valid values for the currency code attribute comprises USD, RUP, and DNR. Because the third row of the REQITEM table 610 that has SUR for the currency code attribute is not listed in the configuration table CURRENCY table 620 , SUR is determined to be an invalid value for the currency code in step 1302 of FIG. 2 , supra. A fourth element CURR_CD_VLDT is a validity flag for a value of the currency code attribute in the REQITEM table 610 . A first row of CURR_CD_VLDT has USD value as it is valid from the configuration table CURRENCY table 620 . A second row of CURR_CD_VLDT has RUP value as it is valid from the configuration table CURRENCY table 620 . A third row of CURR_CD_VLDT has a null value as it is invalid from the configuration table CURRENCY table 620 .

A fifth attribute UOM_CD represents a unit of measure code that is used in the requisition item. The UOM_CD attribute in the REQITEM table 610 comprises three values of HR, KEG, and PTR. The UOM table 630 that comprises valid values for the unit of measure code attribute comprises HR, KGR, and PTR. Because the second row of the REQITEM table 610 that has KEG for the unit of measure code attribute is not listed in the configuration table UOM table 630 , KEG is determined to be an invalid value for the unit of measure code in step 1302 of FIG. 2 , supra. A sixth element UOM_CD_VLDT is a validity flag for a value of the unit of measure code attribute in the REQITEM table 610 . A first row of UOM_CD_VLDT has HR value as it is valid from the configuration table UOM table 620 . A second row of UOM_CD_VLDT has a null value as it is invalid from the configuration table UOM table 620 . A third row of UOM_CD_VLDT has PTR value as it is valid from the configuration table UOM table 620 .

A seventh attribute CTRY_CD represents a country code that is used in the requisition item. The CTRY_CD attribute in the REQITEM table 610 comprises three values of US, IN, and ZM. The COUNTRY table 640 that comprises valid values for the country code attribute comprises US, IN, and GB. Because the third row of the REQITEM table 610 that has ZM for the currency code attribute is not listed in the configuration table COUNTRY table 640 , ZM is determined to be an invalid value for the currency code in step 1302 of FIG. 2 , supra. An eighth element CTRY_CD_VLDT is a validity flag for a value of the country code attribute in the REQITEM table 610 . A first row of CTRY_CD_VLDT has US value as it is valid from the configuration table COUNTRY table 640 . A second row of CTRY_CD_VLDT has IN value as it is valid from the configuration table COUNTRY table 640 . A third row of CTRY_CD_VLDT has a null value as it is invalid from the configuration table COUNTRY table 640 .

FIG. 4 illustrates a query in Structured Query Language (SQL) to attain a record object table 650 of FIG. 3B , in accordance with the embodiments of the present invention.

A query 660 is constructed in SQL to retrieve data from the REQITEM table, the CURRENCY table, the UOM table, and the COUNTRY table of FIG. 3A , supra, to produce a result of the query 660 comprising data values indicating validity of the data in the REQITEM table against all configuration tables for attributes in the REQITEM table. In the query 660 , the REQITEM table is referred to as A, the CURRENCY table is referred to as B, the UOM table is referred to as C, and the COUNTRY table is referred to as D. The query 660 returns a row per requisition item that has same REQID attribute that indicates a requisition identifier and same REQITNUM attribute that indicates a requisition item number. Because REQID values are identical for all records as defined in line L 29 , rows are distinguished by REQITNUM values in the result of the query 660 . Each row in the result has the requisition identifier REQID, the requisition item number REQITNUM, the currency code CURR_CD, a currency code validity flag CURR_CD_VLDT of which value is determined in lines L 05 to L 09 , the unit of measure code UOM_CD, a unit of measure code validity flag UOM_CD_VLDT of which value is determined in lines L 11 to L 15 , the country code CTRY_CD, and a country code validity flag CTRY_CD_VLDT of which value is determined in lines L 17 to L 21 .

FIGS. 5A , 5 B, 5 C and 5 D illustrate example Java® program codes of the data validator 500 , in accordance with embodiments of the present invention.

A Java® program object RequisitionItem is created for each row of a query result. Lines L 501 to L 533 define a class RequisitionItem. RequisitionItem class comprises a first data element currency that is defined in line L 502 , a first validity flag for the first data element currencyValid that is defined in line L 505 , a second data element unitofMeasure that is defined in line L 503 , a second validity flag for the second data element uomValid that is defined in line L 506 , a third data element country that is defined in line L 504 , and a third validity flag for the third data element countryValid that is defined in line L 507 .

RequisitionItem class further comprises object methods setValidatedCurrency( ) that is defined in lines L 508 to L 515 , getCurrency( ) that is defined in lines L 516 to L 518 , isCurrencyValid( ) that is defined in lines L 519 to L 521 , hasAnyInvalidDataelement( ) that is defined in lines L 522 to L 525 , and hasAnyValidDataelement( ) that is defined in lines L 526 to L 532 . The RequisitionItem item has methods hasAnyInvalidDataelement( ) and hasAnyValidDataelement( ) to provide other program modules with a high-level validity information that indicates whether the RequisitionItem item comprises any invalid and/or valid data element prior to checking the validity of individual data element. The setValidatedCurrency( ) method can add additional logic when setting a data element is valid or not.

Each RequisitionItem item is stored in a RequisitionItemList list. The RequisitionItemList list can be used by any program module that needs validated RequisitionIem items in performing a transaction. Lines L 534 to L 569 define methods for RequisitionItemList that comprises RequisitionItem objects.

A first method hasItemWithInvalidDataElement( ) is defined in lines L 534 to L 545 . The first method hasItemWithInvalidDataElement( ) invokes hasAnyInvalidDataelement( ) that is defined in lines L 522 to L 525 of RequisitionItem class definition. A second method hasItemWithValidDataElement( ) is defined in lines L 546 to L 557 . The second method hasItemWithValidDataElement( ) invokes hasAnyValidDataelement( ) that is defined in lines L 526 to L 532 of RequisitionItem class definition. A third method hasItemWithInvalidCurrencyCode( ) is defined in lines L 558 to L 569 . The third method hasItemWithInvalidCurrencyCode ( ) invokes isCurrencyValid( ) that is defined in lines L 519 to L 521 of RequisitionItem class definition. RequisitionItemList class may further comprises hasItemWithInvalidUomCode( ) and hasItemWithInvalidCountryCode( ) for each data element.

FIG. 6 illustrates a computer system 90 used for utilizing a record stored in a database, in accordance with embodiments of the present invention.

The computer system 90 comprises a processor 91 , an input device 92 coupled to the processor 91 , an output device 93 coupled to the processor 91 , and memory devices 94 and 95 each coupled to the processor 91 . The input device 92 may be, inter alia, a keyboard, a mouse, a keypad, a touchscreen, a voice recognition device, a sensor, a network interface card (NIC), a Voice/video over Internet Protocol (VOIP) adapter, a wireless adapter, a telephone adapter, a dedicated circuit adapter, etc. The output device 93 may be, inter alia, a printer, a plotter, a computer screen, a magnetic tape, a removable hard disk, a floppy disk, a NIC, a VOIP adapter, a wireless adapter, a telephone adapter, a dedicated circuit adapter, an audio and/or visual signal generator, a light emitting diode (LED), etc. The memory devices 94 and 95 may be, inter alia, a cache, a dynamic random access memory (DRAM), a read-only memory (ROM), a hard disk, a floppy disk, a magnetic tape, an optical storage such as a compact disc (CD) or a digital video disc (DVD), etc. The memory device 95 includes a computer code 97 which is a computer program that comprises computer-executable instructions. The computer code 97 includes, inter alia, an algorithm used for utilizing a record stored in a database according to the present invention. The processor 91 executes the computer code 97 . The memory device 94 includes input data 96 . The input data 96 includes input required by the computer code 97 . The output device 93 displays output from the computer code 97 . Either or both memory devices 94 and 95 (or one or more additional memory devices not shown in FIG. 6 ) may be used as a computer usable medium (or a computer readable medium or a program storage device) having a computer readable program embodied therein and/or having other data stored therein, wherein the computer readable program comprises the computer code 97 . Generally, a computer program product (or, alternatively, an article of manufacture) of the computer system 90 may comprise said computer usable medium (or said program storage device).

Any of the components of the present invention can be deployed, managed, serviced, etc. by a service provider that offers to deploy or integrate computing infrastructure with respect to a process for utilizing a record stored in a database of the present invention. Thus, the present invention discloses a process for supporting computer infrastructure, comprising integrating, hosting, maintaining and deploying computer-readable code into a computing system (e.g., computing system 90 ), wherein the code in combination with the computing system is capable of performing a method for utilizing a record stored in a database.

In another embodiment, the invention provides a business method that performs the process steps of the invention on a subscription, advertising and/or fee basis. That is, a service provider, such as a Solution Integrator, can offer to create, maintain, support, etc. a process for utilizing a record stored in a database of the present invention. In this case, the service provider can create, maintain, support, etc. a computer infrastructure that performs the process steps of the invention for one or more customers. In return, the service provider can receive payment from the customer(s) under a subscription and/or fee agreement, and/or the service provider can receive payment from the sale of advertising content to one or more third parties.

While FIG. 6 shows the computer system 90 as a particular configuration of hardware and software, any configuration of hardware and software, as would be known to a person of ordinary skill in the art, may be utilized for the purposes stated supra in conjunction with the particular computer system 90 of FIG. 6 . For example, the memory devices 94 and 95 may be portions of a single memory device rather than separate memory devices.

While particular embodiments of the present invention have been described herein for purposes of illustration, many modifications and changes will become apparent to those skilled in the art. Accordingly, the appended claims are intended to encompass all such modifications and changes as fall within the true spirit and scope of this invention.