Patent Grant
8156143
Companies may need to ensure the accuracy and consistency of financial data stored in enterprise databases. Examples of such databases may include Human Resource (“HR”) payroll, payroll posting, General Ledger (“G/L”) accounts, tax, and travel management. Two databases may be logically related when a data field in a first database is logically related to a data field in a second database. A first data field is logically related to a second data field when the data recorded in the first and the second data field may be traced to a common original event. Examples of common original events may include monthly salary payment to an employee and associated payroll deductions, an expense debit for a group of employees, or an employee's travel expense and reimbursement for a month. In another example, after the travel expense reimbursement for an employees' business trip was processed, the controller of the corporation may receive an overview of the amount that was paid out in comparison to the amount that was requested to be reimbursed. In such a situation, the first data field may include the amount that the employee requested to be reimbursed and the second data field may include the amount actually reimbursed to the employee, where the business trip is the common original event for both data fields, and therefore, they are logically related. When data are recorded in logically related databases, the data may have discrepancies for a number of reasons. For example, monthly payroll data for an employee may differ from corresponding monthly payroll posting data in finance accounting because the data for the employee may not be posted to the payroll accounting at the time of comparison due to off days. In another example, data in a tax database may differ from corresponding data in a payroll posting database with respect to a tax code due to lack of coordination between the two databases. Companies may need to report and explain any discrepancies among logically related databases to internal and external authorities by regulation or by law.
Currently, there is no standard tool to automatically reconcile and report discrepancies in logically related databases. Instead, individual software applications generate tables of data in separate reports. As a result, to reconcile databases, these reports are compared manually by human resource or accounting experts, a process that is time consuming and prone to human errors.
Current HR management systems in marketplace do not have the capability of reconciling diverse databases in an enterprise business information system. There is a great need for system and method that automate the database reconciliation process.
a shows one example screen shot of Human Resource database reconciliation system involving a payroll database and a payroll posting database according to one example embodiment of the present invention.
b shows another example screen shot of Human Resource database reconciliation system involving a payroll database and a payroll posting database according to one example embodiment of the present invention.
Embodiments of the present invention allows a corporation to automatically reconcile its Human Resource (“HR”) databases existing in a corporation's business information system. A server, for example, SAP® ERP, may provide a storage platform for these databases critical to the operation of the corporation. These databases, commonly including HR payroll, payroll posting, accounting, tax and travel management, may result from running instructions stored in a memory on a digital processor. Because of the difference in the purpose and timing of entering data into these databases, discrepancies may exist between data stored in two logically related databases or different versions of a database. However, internal corporate governance, such as account auditing, or external legal authority, such as tax authorities in different countries, may require a corporation to ensure the accuracy and consistency of data stored in logically related databases. Under an instruction from a user or at preset schedules, embodiments of the present invention may run a reconciliation application stored in the memory on the digital processor. The application may compare data stored in logically related databases and drill down on any discrepancies for further explanation. The reconciliation may be implemented on SAP business information management platforms such as SAP ERP or SAP NetWeaver® for corporations using SAP solutions for all relevant applications, or may be implemented in a combination of SAP HR applications with Financial Accounting (“FI”) applications supplied by another vendor.
An embodiment of the present invention provides a logic model containing relational information between different databases. The logic model may also contain relational information between data fields in a database and a user specified request. The logic model may be implemented as a matching table between data fields in a first database and data fields in a second database. It may also be implemented as a function such that data contained in a data field in a first database is related to data in a single data field in the second database or data in multiple data fields in a second and/or first database. The first and second database that need to be reconciled may be accessed in the storage by executing instructions in the digital processor.
A user of the reconciliation system, commonly a person who has the responsibility for the integrity of corporate financial information, may enter a request for reconciliation of a first database against a second database. The first and the second database may include any one of the HR payroll, payroll posting, accounting, tax and travel databases. Also under certain circumstances as mandated by laws or regulations, a request for reconciliation may be automatically generated at a preset time or in response to a specified event. For example, the reconciliation system may send out a request for reconciling HR payroll with accounting at the end of each month, or send out a request for reconciling accounting with tax in the event of an internal or external tax auditing. Based on the information contained in a request, the system may initiate a query for retrieving data from the first and the second database. The query may be constructed explicitly based on the need of reconciliation, or implicitly based on the nature of the reconciliation event. For example, if a request is made to reconcile the payroll data of a particular employee over a specific period of time between payroll result and payroll posting in accounting, a query may be generated based on the explicit information regarding that employee. In another example, if the request is made to reconcile as mandated by a tax regulation, the system may look up the logic model for data fields relating to the tax regulation and generate a query containing a list of data fields relating to the tax regulation. Using the query, the reconciliation application may retrieve data from logically related data fields in the first and the second database in the storage. At an initial step, data fields may be retrieved at corporate level in the form of sums of individual business units.
In an embodiment of the present invention, logically related data fields at corporation level from the first and the second database may be compared with each other. The comparison may be a direct one-to-one comparison between data in a data field in the first database and a corresponding data in a logically related data field in the second database. The comparison may also be an one-to-many comparison of data in a data field in the first database against data in multiple data fields in the second database, or against data in more than one database, for example, the first and the second database. The comparison may be a many-to-many comparison of data from multiple data fields in the first database against data from multiple data fields in the second database. The value of data in a data field may be numerical type or symbolic type. The comparison may be in terms of difference, Euclidean distance or other forms of distance metrics, between two data. The comparison result may show a match or a discrepancy. The comparison result may indicate a match if the comparison result meets a preset criteria, for example, the distance between two data is less than a preset threshold value. If the comparison result shows a match between the two logically related databases under mandates imposed by internal and external authorities, the reconciliation application may end and generate a report to the authorities. On the other hand, if the comparison result shows a discrepancy, for example, the distance between data in two logically related data fields is larger than a preset threshold, the reconciliation application may then drill down on the discrepancies to locate the source for discrepancies. To this end, the reconciliation application may generate a new query requesting more data at a higher level of detail for data fields that have discrepancies between logically related databases. A higher level of detail may show more data fields than a lower level of detail. For example, if the total pay for a business unit according to a first version of payroll database is different from that for the same business unit in a second version of payroll posting database for a month, the reconciliation application may generate a query for payroll data as broken down to sub business units. Then the reconciliation application may retrieve data from the first and the second database at the sub business unit level and compare the data at that level. The reconciliation application may repeat these two steps of comparison and drill-down recursively until there is no further level of details in the first and the second database, or the reason for the discrepancy is found to satisfy a preset condition.
An embodiment of the present invention may generate a report according to the result of reconciliation. The report may be formulated based on legal requirements for corporate disclosure including the origin and the reason for discrepancies. For example, a tax regulation may require a report specifying discrepancies in taxable deductions in each employee pay check. Therefore, at the end of a reconciliation between payroll and tax databases, a report may be generated for tax authorities with details relating to discrepancies (if any) in each and every employee salaries. Even when the databases match each other, the reconciliation application may still generate a report showing that each data field in first database is matched to a corresponding data field in the second database according to law or regulation.
An embodiment of the present invention may build the logic model according to tax information gathered from historical data and up-to-date tax regulations. A corporation may need by law or regulation to file tax reports based on its financial data including revenue and expense data. Expense data may be recorded in the forms of General Ledger (G/L) account, employee payroll, or travel. A logic model containing information relating data fields in databases to tax regulations may be built based on historical data and previous experience with the tax regulation. Moreover, tax regulation may change each year in response to tax legislations. The logic model may also be updated according to tax regulation changes. The logic model may specify for a particular tax regulation the type of data that needs to be reconciled and reported to authority. Therefore, the reconciliation application may automatically generate a query for retrieving all relevant data relating to a tax request based on the logic model. The logic model may be created in advance of the reconciliation and be updated periodically or according to changes in tax regulations.
An example variant of the present invention may provide a method to automatically generate a query for retrieving data from the first and the second database. In one example, where the user may issue an instruction to reconcile a first database against a second database for a particular type of data over a specific period of time, a query for retrieving data relating to the type of data may be automatically generated based on information in the logic model. For example, the user may instruct to reconcile the payroll information for a group of employees over a year against payroll posting information for the same employees over the same year. A query may be generated based on a mapping relation between data fields in the payroll database and the payroll posting database so that a data retrieval in the first database results in a retrieval of corresponding data in the second database. In another example, an automatic request to reconcile financial data according to a tax regulation at the end of a month may trigger the reconciliation application to automatically construct a query based on information in the logic model relating to the tax regulation.
An example of the present invention reconciles a first database containing a first version of payroll information and a second database containing a second version of payroll information. Different versions of payroll information may be stored in different corporate business information databases for a number of reasons. One version may be created and maintained by the HR payroll department while another version may be created and maintained by the accounting department. The two versions of payroll database may have discrepancies because they may be updated at different times for different purposes. Corporations under internal and external legal mandates may need to reconcile these different versions of a database. The reconciliation between different versions of payroll data is also called cross-validation of payroll. For example, when payroll department would like to get a list of employees whose tax statutory deductions have discrepancies between employee deduction and employer deduction, data from a list of data fields may be extracted. In a SAP Business Information (“BI”) system, an embodiment of the present invention may contain columns for field names, field descriptions, a table of origins and, fields in the table of origins. Field names may include a list of unique names for data fields which are correspondingly described in field description column. Examples of fields include “from data, sequential number,” “payroll area,” “for-period for payroll,” “start date of payroll period (for period),” “end of payroll period (for period),” “in-period for payroll,” “end of payroll period (in period),” “legal person, payroll type,” “reversal indicator,” “date of payroll run,” “pay date for payroll result,” “reversal indicator,” “out-of-sequence check indicator,” “create out-of-sequence check (account to payroll control record),” “personnel number,” “country grouping,” “business area,” “controlling area of master cost center,” “company code,” “employee group,” “employee subgroup,” “position, master cost center,” “organizational unit,” “personnel area,” “personnel subarea,” “time management status,” “expenditure type,” “financial management area,” “fund,” “funds center,” “commitment item,” “controlling area,” “debited activity type,” “debited activity,” “debited business area,” “debited cost center,” “debited company code,” “debited network,” “debited order,” “debited element,” “debited fund center,” “debited commitment item,” “debited fund,” “national assignment indicator,” “variable assignment number,” “information variable assignment,” “wage type,” and “currency key.” The table of origins may include a list of original directories as described in fields in the table of origin from which data fields may be stored. In an embodiment of the present invention, an origin directory may contain a single field name or no field name. In another embodiment, an origin directory may contain multiple field names. A comparison of all or a subset of these data fields for a particular employee over a specific period of time may show discrepancies between the first payroll database and the second payroll posting database.
An example of the present invention may reconcile any one of a first database including a payroll database, a tax database, an accounting database and a travel database against any one of a second database including a payroll database, a tax database, an accounting database and a travel database. For example, the tax database may need to reconcile with the payroll database so that all statutory payroll deductions sent to the government are not only correctly reported, but also correctly withheld and submitted. Therefore, reconciliation application may need payroll information relating to each employee including salary and other types of deductions including social security deduction, medical insurance deduction, and flexible spending deduction.
In another embodiment of the present invention, a payroll database may be reconciled with accounting data posted to Financial Accounting (“FI”). In an example variant of the embodiment, the payroll may be reconciled with the accounting interface between payroll and FI. Since payroll results commonly contain information that may be relevant for accounting, they may need evaluation before being posted to FI. HR commonly provides an accounting interface to perform the task of evaluating payroll data. The accounting interface may create intermediate accounting documents that may be posted to accounting. A reconciliation between payroll data and the intermediate documents may be required before posting the intermediate documents to FI because HR may transfer only summarized data to FI. In an example variant of embodiment, accounting may require the information from payroll for paying wages or salaries to employees. A preliminary data medium exchange program (“Pre-DME”) in HR evaluates the payroll results and prepares the payment information which accounting uses to make payments to employees. Before transmitting this information to the accounting, this information is validated against the bank transfer for each employee as calculated by payroll.
In another example of the present invention, payroll may be reconciled directly with accounting data in Financial Accounting and Controlling (“FI/CO”). The purposes of this type of accounting reconciliation are to check the integrity of accounting documents, to satisfy legal reporting and auditing, to check outgoing payables with payroll, and to review expenses for a particular financial entity including cost centers or fund centers.
In an embodiment of the present invention, payroll may be reconciled with travel expense database. Travel related payroll deductions may be limited by law and regulation. Also, travel expense reimbursement to employees may be the source of discrepancies in different versions of payroll databases.
An embodiment of the present invention may execute reconciliation application for databases according to a preset schedule. For example, reconciliation between payroll and tax may be conducted bi-monthly for payroll, annual tax and pay card expenses in country specific tax report. Reconciliation for Employers contributions and cumulated payroll results may be conducted for annual tax statement. Reconciliation between payroll and accounting may be conducted annually for FI/CO result accounts and balance accounts, and bi-monthly for tax report. Reconciliation for employer's contribution may be conducted when financial data are posted to FI/CO. Reconciliation for travel management may be conducted for monthly or annual tax report.
a shows another example screenshot illustrating reconciliation of different versions of a payroll database according to an embodiment of the present invention. The example payroll results on the right contain one more payroll sequence number for some employees (pernr=135001, 135002) as compared with their corresponding payroll posting document. A comparison of the two versions of payroll at the document posting lines (doclineno) level and a subsequent drill-down at the employee wage type level may reveal that a payroll sequence has not been posted to payroll posting document at the time of reconciliation. An example reason for this discrepancy may be that data for regular pay have been posted, but data for vacation day pay have not been posted yet.
b shows another example screenshot illustrating reconciliation of different versions of a payroll database according to an embodiment of the present invention. The example payroll results on the right contain data for one employee (Pernr=135002) that is not posted in the payroll posting document on the left. This may be a result when the payroll data have not been posted to accounting for the employee (Pernr=135002).
The various computer systems described herein may each include a storage component for storing machine-readable instructions for performing the various processes as described and illustrated. The storage component may be any type of machine readable medium (i.e., one capable of being read by a machine) such as hard drive memory, flash memory, floppy disk memory, optically-encoded memory (e.g., a compact disk, DVD-ROM, DVD±R, CD-ROM, CD±R, holographic disk), a thermomechanical memory (e.g., scanning-probe-based data-storage), or any type of machine readable (computer readable) storing medium. Each computer system may also include addressable memory (e.g., random access memory, cache memory) to store data and/or sets of instructions that may be included within, or be generated by, the machine-readable instructions when they are executed by a processor on the respective platform. The methods and systems described herein may also be implemented as machine-readable instructions stored on or embodied in any of the above-described storage mechanisms.
Although the present invention has been described with reference to particular examples and embodiments, it is understood that the present invention is not limited to those examples and embodiments. Further, those embodiments may be used in various combinations with and without each other. The present invention as claimed therefore includes variations from the specific examples and embodiments described herein, as will be apparent to one of skill in the art.
This is a Divisional Application of U.S. Utility application Ser. No. 11/956,980, filed Dec. 14, 2007, which is incorporated herein in its entirety.
| Number | Name | Date | Kind |
|---|---|---|---|
| 5715397 | Ogawa et al. | Feb 1998 | A |
| 20020147668 | Smith et al. | Oct 2002 | A1 |
| 20020188480 | Liebeskind et al. | Dec 2002 | A1 |
| 20030225638 | Secola | Dec 2003 | A1 |
| 20050027507 | Patrudu | Feb 2005 | A1 |
| 20050192979 | Keller et al. | Sep 2005 | A1 |
| 20070250379 | Pappas et al. | Oct 2007 | A1 |
| 20070255656 | Olson et al. | Nov 2007 | A1 |
| 20070265901 | Olson et al. | Nov 2007 | A1 |
| 20080021799 | Blowers | Jan 2008 | A1 |
| 20080082575 | Peter et al. | Apr 2008 | A1 |
| 20080235365 | Bansal et al. | Sep 2008 | A1 |
| Number | Date | Country | |
|---|---|---|---|
| 20110035408 A1 | Feb 2011 | US |
| Number | Date | Country | |
|---|---|---|---|
| Parent | 11956980 | Dec 2007 | US |
| Child | 12908203 | US |
