System and method for transactional data collection and processing
A system and method for loading and processing of transaction data is provided. The system and method can obtain transaction data for commercial cards from many feeds with different data formats. A generic file parser is easily modified to account for different data feed formats. Further processing of the transaction data includes sorting the data into multiple temporary tables, which are then resorted in other sorting and processing operations to properly prepare the data for loading into tables in a database. These tables in the database greatly improve the information available to many different parties who need to access the transaction data in the database. The system and method provides the ability to quickly format large amounts of data and insert it into a database with minimal time.
This invention is directed towards data collection and processing, and more particularly towards transaction data collection and processing from disparate sources.
BACKGROUNDOnline transactions are becoming more prevalent. Similarly, online processing of transaction data is common. One area with large amounts of transaction data is credit cards and debit cards. Data from all such credit card transactions is handled electronically, which allows faster transmission of such data, but also creates its own set of problems.
One area of credit card use is the issuing of commercial cards and accounts to employees or agents of a company. Commercial credit cards are the fastest growing payment method in the world for high volume, low value procurement, and also day-to-day travel and entertainment (T&E) expenditures. Procurement expenses in many cases used to require purchase orders or order numbers, which are now more easily purchased with a commercial card. Payments made by commercial cards have been shown to reduce the real cost of processing purchase requisitions and invoices from more than $70 to less than $5 per invoice. T&E expenditures typically include hotels, meals, taxis, airline tickets etc. For example an employee who travels frequently on business would have a credit card to charge all her travel related expenses. The employee then does not need to submit detailed expense reimbursement reports for all her travel expenses, but instead needs only have the credit card statement processed directly by the company. Employees may have several different corporate cards, each for a different purpose.
This great expansion of commercial cards has caused a consequential increase in banks' and companies' accounting issues in handling these transactions. Statements from the credit card issuers must be processed by an accounting department, and expenses properly charged to the proper department or account within the company. The amount of transaction data, the complexity of the data, and the complexity of the company pay structure all create problems.
One problem is that although statements and transaction data from the banks and other credit card issuers is available in electronic format for simplified access, there is no set standard in the industry for the format of such transaction data. This problem is even worse on a global scale. Because they are unable to obtain electronic information in a common or consistent way across international boundaries, multinational businesses and corporation have been unable thus far to establish consistent procurement and expense management across their global operations.
The processing and accounting for electronic transaction data requires different processing based on the format of the data. This limits a company's ability to automatically process the data, and may limit the company's choice of credit card issuers or the providers of the transaction data. The other choice is for a company to enter certain transaction data by hand, or spend time and money to develop preliminary processing applications to handle the different formats of received transaction data. Further, as new formats for transaction data are introduced, or older formats are updated, the transaction data preliminary processing application must be updated. Even using standard formats, such as the XML Document Type Definition (DTD), formats must get updated as data sent by the processors is updated, enhanced or otherwise changed.
Another problem is that within a company such data must often be supplied to and utilized by several departments, which may have different requirements. Different departments would only want some data, for example for employees within that department. Other departments, such as accounting, may want all the transaction data entered into their general ledger, but they are not responsible for payment, which may be processed through other costs centers. Finally, different departments even within one company may want transaction data provided in different formats. This problem becomes more difficult when commercial card services are provided by a banking institution, which will have several different commercial clients who want customizations for their business model.
With so many requirements on what transaction data is used by certain departments, and also what format the departments want the data, a typical approach is to store the data in a database that allows access through querying interfaces, such as an SQL (structured query language) compliant interface. This allows each party's department to access and use the data in the form most convenient to them. For example the database accumulates the transaction data so that each department can access all data over a time period of their choosing.
But storing the transaction data in a database is problematic. Even separate from the problem of different formats on the transaction data feed, the transaction data is not in the form that can be readily loaded into a database. The transaction data is often acquired in batch, such as a download session from one of the credit card issuers. This download typically may occur on a daily, weekly or longer basis. The amount of data can be large. Aside from preliminary processing of all this acquired data in different formats, the parsing and insertion of the data into a database takes a good amount of time. Therefore the batch processing of transaction data and updating of the database with all the new transaction data can take several hours or longer. Also the process utilizes lots of memory and CPU time resources. Therefore the access to the database during the data download time must be minimized. In consequence, the loading of the new transaction data must be done during the user utilization down times.
SUMMARYThe present invention is useful in working with transaction data from various sources including company credit cards (corporate cards) and other expense tracking devices. Corporate cards are ideally suited to employees who travel or regularly incur expenses on the organization's behalf. The advantages of the present invention for corporate cards includes the ability to electronically review cardholder transactions daily and browse statements on-line, providing a significant degree of visibility and control over card usage; automatic generation of a downloadable file formatted for upload to a company's general ledger, hugely improving program management and removing manual intervention. Other advantages include the automatic generation of expense claims and review or approval on-line, thereby eliminating the inefficiencies associated with cumbersome expense management processes and employee expense claims; and easily generated comprehensive reports on day-to-day expenditure which establishes a powerful control that is unmatched by other payment methods. Further, the present invention provides common automated data processing for companies across data processors.
The present invention is directed towards a transaction processing system that is very easy to adapt and modify based on the requirements of companies and users. An embodiment of the present invention can easily accept transaction data in various formats, and can quickly be updated for new or changed formats. The embodiment can process the transaction data quickly and place all processed data into a database in much less time than previously required. The database system then allows many different parties and departments to access the data, and also to export the data in a format unique to the requirements of the data accessor. The system is easy to adapt to the needs of the industry. Also, the present invention provides common transaction data representation across data processors.
Another advantages of the present invention include the ability to fast load a large data volume. This provides greater efficiencies including less time that certain data is not accessible to the database users. The system remains potentially available 24/7.
BRIEF DESCRIPTION OF THE DRAWINGSThe foregoing and other features and advantages of the present invention will be more fully understood from the following detailed description of illustrative embodiments, taken in conjunction with the accompanying drawings in which:
An embodiment of the present invention provides unprecedented flexibility in the importing, processing and accessing of commercial card data. The system provides simple and secure access to all services from a web browser, including card transactions, eStatement browsing and history navigation, instant on-line management information with downloadable reports, automatic cost allocation at line item level regardless of complexity, and customized file exports which can be downloaded in a format that will load into a company's specific general ledger. Further, the system also provides automatic validation of transactions for VISA tax rule compliance or other national or jurisdictional or credit card processing rules; and generation of evidence and non-evidence for tax reports, all on-line. The system also handles multinational program management including consolidated reports, international tax administration and centralized or decentralized program management, as well as multiple languages and multiple currencies.
An input processing portion of the present invention is illustrated in
An illustrative embodiment of the present invention utilizes a parsing utility with a generic file parser 30 to define the most common way to parse a transaction data file. The generic parser defines the typical sequence of events happening during the parsing process. Also it defines the typical data identification and data extraction. The generic parser can typically handle a majority of transaction data. For transaction data in other formats, a specific parser 32a, 32b, or 32c can overwrite any of the functions provided by the generic parser 30. For example, company identification function, card identification function, currency identification function, transaction type mapping function, etc. within the generic parser 30 could all be overwritten by a specific parser. With a minimum effort for specific data parsing 32a, the format 24 from the data provider 22a can be parsed. This allows the illustrative embodiment to easily process a majority of file formats such as format 24.
For the other possible formats 26, 28, the illustrative embodiment uses extensions 32b and 32c to augment the generic file parser 30, to handle the specific features of the other possible formats 26, 28. The extensions 32b and 32c need only address the specific data fields that are different from the generic format. Thus for example, a set of extensions 32b will only need to be coded to handle the specific data records in transaction data format 26 from data provider 22b that are different from the generic format. Some example of differences are company identification data, tax information, field tags, line item details, number or currency conversions, etc. Another different data format 28 would then have a specific set of extensions 32b as a ‘plug-in’ to the generic file parser 30 as needed to parse the data format 28 for that data provider 22c. The specific data parser 32b can also define more important changes such as the sequence of data parsing in case the transaction records in the format 28 are not sorted in the most common order. Also, format 28 could present a different data structure from the common data format. Therefore the data mapping would be more complex. For instance a transaction data record could be spread over multiple records in format 28. The parser 32c would consolidate this information prior to storing it into a single data record of the common format.
More details of the input processing for the illustrative embodiment are shown in
This process allows the illustrative embodiment to be quickly modified to import new or changed data formats with minimal effort by administrators. Only the differences between a generic data format 24 and the new data format need to be identified and coded into the set of parser extensions 32. See
The file parser produces output in a common data format 34,
The next step of preliminary processing of transaction data is shown in
The last step 110 is the transferring of data from the temporary tables 52
The sorting operation 66
Details of the invoice processing step 68 are shown in
The next step is card balance processing 70, with details provided in
At step 124, the temporary tables will be accessed using SQL queries, therefore SQL query strings are set to access transaction information from the temporary tables. The card balance temporary table is opened, with a position cursor set up, step 126. Provided that no error occurred in opening the database temporary table, the transaction entries are read and processed one by one, step 128. After reading one entry, and checking to make sure that an end of file was not encountered, step 130 (which would indicate that all entries in the table have been read), the system checks to see if the entry is a duplicate of a previous stored entry, step 132. This may happen because of historical information stored in the database based on a company's parameters for allowing access to information within the system. If it is a duplicate, then the entry is discarded.
At step 134, the system checks the database to determine if the card number for the transaction represents a new card. A feature of the illustrative embodiment is the ability to detect new cards from either the processed transaction or the processed card balance, which have been issued but do not yet have account information entered into the system. Whenever a transaction is received and loaded there is a card number field. That card number field is checked against the database and if it exists, the transaction is processed against that card number. Otherwise a new card has been detected. The system, upon detecting a new card, sets up a dummy or decoy account for the card, and can record the transaction details to that dummy account, which can have the unknown details filled in later by a system administrator. This feature is valuable for markets where the information regarding new cards is not supplied with the transaction data. The transactions are still properly recorded, so it is easy to update the information on the new card, instead of having to re-run transaction data that could not be captured without an existing card account. If an error occurs in setting up a dummy card account, the error is recorded into the error table 43
If the card number has expired, the expired card is re-activated, step 138. This can occur when transaction data comes in for a card that is indicated by the system to have expired. Since transaction information is not always promptly provided, for example trip leg data, or data with dates near the end of one month, a card may be marked as expired but transaction data is received for it, and this data must be properly recorded. Therefore the card is marked as reactivated, step 138. If an error occurs during re-activation, it is recorded to the error table, step 136.
If a billing cycle date change did occur, as previously discussed with step 120, the change is recorded to the database, step 140. If an error occurs, it is recorded to the error table, step 136.
The card balance information for the transaction is finally written to the database, step 142. Again, if an error occurs, it is recorded to the error table, step 136. The system then repeats to read the next entry step 128 and repeat the cycle. When the end of file is detected, step 130, the access to the temporary files is closed, step 144. At step 146, the system makes a list of all new cards set up as part of step 134. This list is then used to indicate the number of new cards detected and the system can then be updated with the missing information for those new cards.
Turning back to
All three temporary tables are accessed for items with the same transaction id, steps 152-154. An entry with the same transaction id should be found in each table. If an unmatched entry is found in the line item table 156 or additional data table 158, an error is signaled. The system then processes through the accessed transaction record, step 160, unless no more transaction records exist in the tables, as indicated by step 170. Unless processing the transaction record results in an error, the transaction record is then marked for VAT (value added tax) processing, step 162, using the country VAT tables loaded as indicated hereinbefore.
Next, the sign on the transaction amount may need to be corrected, step 164. This occurs because some transactions may be marked as positive (for example, as a payment instead of a purchase), and the sign must be changed to properly indicate the bookkeeping operation. The system maintains a table of transactions that must be marked as negative, and changing the sign catches several errors which might later need to be resolved. Once the VAT and sign correct is completed, the transaction is written to the database, step 166.
If the transactions indicate an excessive number of duplicate entries step 168, then processing is aborted, step 169 and an alert is raised to indicate a problem with the number of duplicate transaction entries, step 174. Otherwise processing continues by reading the next transaction entry from the temporary tables, steps 152-154. Once no more entries are present in the temporary tables, step 170, the table accesses are closed, step 172. If new card numbers were detected and new card accounts created, the number is reported, step 146, as previously described. Finally, the temporary tables are deleted, step 176.
The next processing step as shown in
The next step for the processing of transactions as outlined in
Although the illustrative embodiment herein is disclosed as having two specific parsers, it should be appreciated that other specific parsers can be implemented as a function of the application and data provided. Additionally, although specific temporary tables are depicted in the illustrative embodiment, it should be appreciated that greater or fewer temporary tables can be implemented as a function of the application and data. Likewise, the number and nature of data base tables can be different as a function of the application and data.
The present invention can be implemented in one processor system with an internal database, or in a networked system with multiple processors and multiple databases and internet connections.
Although the invention has been shown and described with respect to illustrative embodiments thereof, various other changes, omissions and additions in the form and detail thereof may be made therein without departing from the spirit and scope of the invention.
Claims
1. A system for processing incoming data and inserting said incoming data into a database, comprising:
- an incoming data receiving component, to connect to a source of data and receive incoming data;
- a parsing component communicating with said incoming data receiving component, to receive and parse said incoming data as a function of a plurality of fields;
- a loader component, in communication with said parsing component, to receive parsed data from said parsing component, and to sort said parsed data into a plurality of temporary tables as a function of said plurality of fields;
- a data sorting component, in communication with said plurality of temporary tables and with said database, to access sorted data in said plurality of temporary tables, and to re-sort said sorted data into a plurality of tables in said database.
2. The system of claim 1 wherein said loader component performs the following steps:
- processing said parsed data into a proper format for insertion into said database;
- storing said parsed data in a file;
- deactivating access to a temporary table in said database;
- loading said file into said temporary table in said database; and
- re-activating access to said temporary table.
3. The system of claim 1 wherein said data sorting component also inserts relational link information in said plurality of tables in said database.
4. The system of claim 1 wherein said data sorting component, upon accessing a data item in said temporary tables that indicates an error, moves said data item into a corresponding error table.
5. The system of claim 1 wherein:
- said parsing component includes a generic parsing component having common functionality to parse data; and
- wherein at least one specific function is implemented into a specific parsing component which encapsulates said generic parsing component, said at least one specific function modifying functionality of said generic parsing component so that said specific parsing component can parse data in a specific format.
6. The system of claim 5 wherein said at least one specific function overrides corresponding functionality in said generic parsing component.
7. The system of claim 1 wherein said data sorting component processes data in terms of one of: transaction data, line item data, additional data, enhanced data, trip leg data, and card balance data.
8. The system of claim 1 wherein said data is transactional data representing transactions completed using a commercial credit card.
9. The system of claim 8 wherein said data sorting component includes additional information in said data tables regarding tax information for said transactional data.
10. A method for loading data into a database comprising:
- receiving data from a source of data;
- parsing said data as a function of a plurality of fields to form parsed data;
- sorting said parsed data into a plurality of temporary tables, said sorting being a function of said plurality of fields, to form sorted data; and
- re-sorting and inserting said sorted data into tables in said database.
11. The method of claim 10 wherein said step of sorting said parsed data into a plurality of temporary tables includes:
- processing said data into a proper format for insertion as formatted data into a database;
- storing said formatted data in a file;
- deactivating access to a temporary table in said database;
- loading said formatted data from said file into said temporary table in said database; and
- re-activating access to said data table.
12. The method of claim 10 further including:
- during said step of inserting said sorted data into tables in said database, inserting relational link information to other tables in said database.
13. The method of claim 10 wherein said step of re-sorting and inserting said sorted data into tables in said database includes:
- if a data item indicates an error, moving said data item into a corresponding error table in said database.
14. The method of claim 10 wherein said data is credit card transaction data.
15. The method of claim 10 wherein said step of parsing said data includes:
- providing a generic parsing process, said generic parsing process including common functionality to parse data;
- providing a set of specific functions to be implemented in a specific parsing process which encapsulates said generic parsing process, said set of specific functions modifying said generic parsing process so said generic parsing process includes functionality to parse data according to said set of specific functions.
16. The method of claim 15 wherein said set of specific functions override corresponding functions in said generic parsing process.
17. The method of claim 10 wherein said step of re-sorting and inserting said sorted data into tables in said database includes processing said sorted data in terms of one of: transaction data, line item data, additional data, enhanced data, trip leg data, and card balance data.
Type: Application
Filed: Apr 21, 2004
Publication Date: Oct 27, 2005
Inventors: Mairead Lyons (Dumcondra), Declan Ryan (Rathdrum), Pierre Saurel (DunLaoghaire), Des Cahill (Moone)
Application Number: 10/828,811