Reconciliation data processing method and device

Abstract

The invention provides a reconciliation data processing method and device.The method comprises the steps that when it is detected that reconciliation starts, a pre-configured reconciliation file configuration table is obtained, and the reconciliation file configuration table at least comprises the following fields of a reconciliation service type, a file name, a file format suffix, a file obtaining channel, a temporary table, a formal table and a preprocessing import program statement; executing a reconciliation file storage process based on the reconciliation file configuration table; obtaining a pre-configured reconciliation factor configuration table, wherein the reconciliation factor configuration table at least comprises the following fields: reconciliation service types, data table names, reconciliation factor combinations and reconciliation modes; and executing a transaction flow detail reconciliation process based on the reconciliation element configuration table. According to the scheme, the reconciliation file storage process and the transaction flow detail reconciliation process are respectively carried out through the configurable reconciliation file configuration table and the reconciliation element configuration table, so that the reconciliation requirements of various business scenes are met.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a method and device for processing reconciliation data. Background Art

[0002] With the development of digital information technology, banks have provided people with convenient and efficient payment and settlement services. Faced with the development of multiple businesses, transaction scenarios are becoming more and more complex, and the formats of reconciliation documents and the reconciliation elements required by the corresponding businesses are also different. The same reconciliation procedure cannot meet the growing types of reconciliation business. Therefore, there is an urgent need for a reconciliation data processing method that can adapt to various business scenarios. Summary of the invention

[0003] In view of this, an embodiment of the present invention provides a method and device for processing reconciliation data to meet the reconciliation needs of various business scenarios.

[0004] To achieve the above objectives, the embodiments of the present invention provide the following technical solutions:

[0005] A first aspect of an embodiment of the present invention discloses a method for processing reconciliation data, the method comprising:

[0006] When detecting that the reconciliation starts, obtaining a pre-configured reconciliation file configuration table, wherein the reconciliation file configuration table at least includes the following fields: reconciliation business type, file name, file format suffix, file acquisition channel, temporary table, formal table, and pre-processing import program statement;

[0007] Execute the reconciliation file storage process based on the reconciliation file configuration table;

[0008] Obtain a pre-configured reconciliation element configuration table, wherein the reconciliation element configuration table includes at least the following fields: reconciliation business type, data table name, reconciliation element combination, and reconciliation mode;

[0009] The transaction flow detail reconciliation process is executed based on the reconciliation element configuration table.

[0010] Preferably, the reconciliation file storage process is executed based on the reconciliation file configuration table, including:

[0011] Based on the file name, file format suffix and file acquisition channel in the reconciliation file configuration table, obtain the file to be reconciled;

[0012] Preprocessing the to-be-reconciled file;

[0013] Import the pre-processed to-be-reconciled file into a temporary table;

[0014] Performing a total score check on the temporary table into which the pre-processed to-be-reconciled file is imported;

[0015] When the total score verification is passed, the temporary table into which the pre-processed to-be-reconciled file is imported is imported into the formal table.

[0016] Preferably, importing the pre-processed to-be-reconciled file into a temporary table includes:

[0017] Obtaining the import program template corresponding to the temporary table name and the database type used from the reconciliation file configuration table;

[0018] According to the file import mode, using the import program template to generate an actual import program statement, the file import mode is a field fixed length mode or a field separator mode;

[0019] Based on the actual import program statement, the pre-processed file to be reconciled is imported into the temporary table corresponding to the temporary table name.

[0020] Preferably, executing the transaction flow detail reconciliation process based on the reconciliation element configuration table includes:

[0021] Acquire reconciliation configuration parameters from the reconciliation element configuration table, wherein the reconciliation configuration parameters at least include a reconciliation element combination string and the number of reconciliation element fields;

[0022] Based on the reconciliation configuration parameters, a reconciliation program statement is obtained;

[0023] Use the reconciliation program statement to reconcile the current day account and the next day account to obtain the reconciliation result;

[0024] Perform statistical analysis on the reconciliation results.

[0025] Preferably, obtaining the reconciliation configuration parameters from the reconciliation element configuration table includes:

[0026] According to the reconciliation business type and the reconciliation mode, a specified data table is obtained from the reconciliation element configuration table;

[0027] According to the table name of the designated data table and the reconciliation mode, obtaining the reconciliation element combination character string required for reconciliation of the designated data table from the reconciliation element configuration table;

[0028] The number of reconciliation element fields is calculated based on the reconciliation element combination character string.

[0029] A second aspect of an embodiment of the present invention discloses a reconciliation data processing device, the device comprising:

[0030] A first acquisition unit is used to acquire a pre-configured reconciliation file configuration table when detecting that reconciliation starts, wherein the reconciliation file configuration table at least includes the following fields: reconciliation business type, file name, file format suffix, file acquisition channel, temporary table, formal table, and pre-processing import program statement;

[0031] A first execution unit, configured to execute a reconciliation file storage process based on the reconciliation file configuration table;

[0032] A second acquisition unit is used to acquire a pre-configured reconciliation element configuration table, wherein the reconciliation element configuration table at least includes the following fields: reconciliation business type, data table name, reconciliation element combination, and reconciliation mode;

[0033] The second execution unit is used to execute the transaction flow detail reconciliation process based on the reconciliation element configuration table.

[0034] Preferably, the first execution unit includes:

[0035] An acquisition module, used to acquire the to-be-reconciled file based on the file name, file format suffix and file acquisition channel in the reconciliation file configuration table;

[0036] A preprocessing module, used for preprocessing the to-be-reconciled file;

[0037] A first import module, used for importing the pre-processed to-be-reconciled file into a temporary table;

[0038] A checking module, used for checking the total score of the temporary table into which the pre-processed file to be reconciled is imported;

[0039] The second import module is used to import the temporary table containing the pre-processed files to be reconciled into the formal table when the total score verification is passed.

[0040] Preferably, the first import module is specifically used to: obtain an import program template corresponding to a temporary table name and a database type used from the reconciliation file configuration table; generate an actual import program statement using the import program template according to a file import mode, wherein the file import mode is a field fixed-length mode or a field delimiter mode; based on the actual import program statement, import the preprocessed file to be reconciled into a temporary table corresponding to the temporary table name.

[0041] Preferably, the second execution unit includes:

[0042] A configuration parameter acquisition module, used to acquire reconciliation configuration parameters from the reconciliation element configuration table, wherein the reconciliation configuration parameters at least include a reconciliation element combination string and the number of reconciliation element fields;

[0043] A splicing module, used for splicing to obtain a reconciliation program statement based on the reconciliation configuration parameters;

[0044] A reconciliation module, used to use the reconciliation program statement to perform reconciliation of the same day account and the next day account to obtain a reconciliation result;

[0045] The statistical module is used to perform statistical analysis on the reconciliation results.

[0046] Preferably, the configuration parameter acquisition module is specifically used to: obtain a specified data table from the reconciliation element configuration table according to the reconciliation business type and the reconciliation mode; obtain the reconciliation element combination string required for reconciliation of the specified data table from the reconciliation element configuration table according to the table name of the specified data table and the reconciliation mode; and calculate the number of reconciliation element fields based on the reconciliation element combination string.

[0047] Based on the above-mentioned embodiment of the present invention, a reconciliation data processing method and device are provided. The method is as follows: when the start of reconciliation is detected, a pre-configured reconciliation file configuration table is obtained, and the reconciliation file configuration table contains at least the following fields: reconciliation business type, file name, file format suffix, file acquisition channel, temporary table, formal table, pre-processing import program statement; based on the reconciliation file configuration table, the reconciliation file warehousing process is executed; a pre-configured reconciliation element configuration table is obtained, and the reconciliation element configuration table contains at least the following fields: reconciliation business type, data table name, reconciliation element combination, reconciliation mode; based on the reconciliation element configuration table, the transaction flow detail reconciliation process is executed. This scheme uses a configurable reconciliation file configuration table and a reconciliation element configuration table to respectively perform a reconciliation file warehousing process and a transaction flow detail reconciliation process to meet the reconciliation needs of various business scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0048] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying creative work.

[0049] Figure 1 A flowchart of a method for processing reconciliation data provided by an embodiment of the present invention;

[0050] Figure 2 Another flow chart of a method for processing reconciliation data provided by an embodiment of the present invention;

[0051] Figure 3 A flowchart of a process for executing the reconciliation file storage process provided by an embodiment of the present invention;

[0052] Figure 4Another flow chart of the process of executing the reconciliation file storage process provided by an embodiment of the present invention;

[0053] Figure 5 A flowchart of a transaction flow detail reconciliation process provided in an embodiment of the present invention;

[0054] Figure 6 Another flow chart of a transaction flow detail reconciliation process provided by an embodiment of the present invention;

[0055] Figure 7 A structural block diagram of a reconciliation data processing device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0056] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0057] In this application, the terms "comprises", "comprising" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprising a ..." does not exclude the presence of other identical elements in the process, method, article or device comprising the element.

[0058] In order to better understand the following content, some terms are explained here:

[0059] Transaction flow: A series of transaction records recorded by a financial institution in fund flows.

[0060] Reconciliation elements: A collection of fields used in reconciliation to determine that the transaction records of both parties are the same transaction.

[0061] SQL statement: Structured Query Language (SQL).

[0062] Temporary table: A data table that is temporarily stored after the file is directly imported into the database according to the original data.

[0063] Formal table: A temporary table is a data table that is imported into the database for persistent storage after data preprocessing.

[0064] Total-to-score verification: A method used to verify the integrity of files received by the system. For example, the transaction amounts of all transaction details are totaled and then verified with the transaction amounts provided by the summary file.

[0065] Check mark: the status of transaction flow detail reconciliation, including unreconciled status, non-reconciliation status, successful check status, unsuccessful check unilateral status, etc.

[0066] One-sided: For a transaction, only one of the two parties records the transaction flow.

[0067] Error: A situation where the transaction information recorded by both parties for the same transaction is inconsistent.

[0068] Day Cut: Change the system accounting time. The system switches from the current working day to the next working day. During the day cut process, transactions can be submitted as usual and processed and returned correctly.

[0069] With the development of digital information technology, various new consumption, new formats and new models supported by information technology continue to emerge, and innovations in product services, business models, acceptance channels, terminal equipment, transaction verification and other aspects are changing with each passing day, providing people with convenient and efficient payment and settlement services. Faced with the development of multiple businesses and increasingly complex transaction scenarios, the rapid response and adaptability of bank reconciliation systems have also become important capabilities to support business development. With the continuous growth of reconciliation business types, there is an urgent need for a reconciliation data processing method that can adapt to various business scenarios to meet business reconciliation needs.

[0070] The basic logic of the current bank reconciliation system can be summarized into the following steps: obtaining reconciliation documents, storing reconciliation documents, reconciling transaction details, and generating reconciliation results.

[0071] After the reconciliation system receives the transaction flow file of a certain business, it needs to develop the corresponding reconciliation file storage program and the flow detail reconciliation program. Although the basic logic of reconciliation file storage and flow detail reconciliation for different businesses is the same, the format of each reconciliation file and the reconciliation elements required by the corresponding business are different, which will lead to poor reusability of the reconciliation programs for different businesses, and the development cost of the reconciliation system is relatively high when facing new businesses.

[0072] Therefore, this solution proposes a reconciliation data processing method and device, which respectively performs the reconciliation file warehousing process and the transaction flow detail reconciliation process through a configurable reconciliation file configuration table and a reconciliation element configuration table to meet the reconciliation needs of various business scenarios.

[0073] See also Figure 1 , shows a flow chart of a method for processing reconciliation data provided by an embodiment of the present invention, the method for processing reconciliation data comprising:

[0074] Step S101: when it is detected that the reconciliation starts, a pre-configured reconciliation file configuration table is obtained.

[0075] In the specific implementation of step S101, when the start of the reconciliation process is detected, it is determined whether the reconciliation file configuration has been completed; if the reconciliation file configuration has been completed, a pre-configured reconciliation file configuration table is obtained;

[0076] If the reconciliation file configuration is not completed, configure the configuration items of the reconciliation file configuration table to obtain a configured reconciliation file configuration table.

[0077] It should be noted that the reconciliation file configuration table contains at least the following fields: reconciliation business type, file name, file format suffix, file acquisition channel, temporary table, formal table, pre-processing import program statement (such as pre-processing import SQL statement), and other fields of the reconciliation file configuration table can be determined according to the variable elements contained in the file storage SQL template corresponding to the database type used.

[0078] For example, taking the model where both parties to a transaction provide a batch of transaction flows every day as an example, the table structure of the reconciliation file configuration table is shown in Table 1.

[0079] Table 1:

[0080]

[0081] In Table 1 above, the meanings of the fields in the reconciliation file configuration table are as follows:

[0082] Reconciliation business type: the business type that needs to be reconciled;

[0083] File name: the file name to be obtained for reconciliation;

[0084] File format suffix: txt, csv, plain text without suffix or other file formats;

[0085] File acquisition channel: the file server channel for obtaining reconciliation files;

[0086] Temporary table: the name of the temporary table of transaction flow details or the name of the transaction flow summary table;

[0087] SQL template for file storage: The SQL template for file storage corresponds to the database type used;

[0088] File import mode: for example, fixed field length, field separator;

[0089] Formal table: the formal table name of transaction flow;

[0090] Preprocessing import SQL statement: SQL statement that imports temporary table into formal table after data preprocessing.

[0091] In some embodiments, before the reconciliation file is stored in the database, if the reconciliation file configuration is not completed, the configuration method of the reconciliation file configuration table includes the following configuration steps 1 to 5:

[0092] Configuration step 1: Build the table structure of the data table;

[0093] In the specific implementation, the data structure of the transaction flow detail files and summary files on both sides are obtained from the new reconciliation business system, and the table structures of the temporary table of transaction flow details on both sides, the summary table of transaction flow details on both sides, and the formal table of transaction flow details on both sides are constructed.

[0094] Configuration step 2: Add configuration items to the reconciliation file configuration table;

[0095] In the specific implementation, add the reconciliation business type, file name, file format suffix, file acquisition channel, temporary table name, file storage SQL template, file import mode, formal table name and other information of the transaction flow detail files and summary files on both sides of the new reconciliation business in the reconciliation file configuration table.

[0096] Configuration step 3: Build pre-processing import SQL statements;

[0097] In the specific implementation, a SQL statement is constructed to construct a temporary table of details and import it into the formal table of details after data preprocessing. The data preprocessing process sets the readability of the transaction flow information and the adaptability of transactions on both sides. For example, the primary key of the transaction flow details, the transaction source, and the completed amount conversion are configured, and the check status of the transaction flow details that initially participate in the reconciliation is set to the unreconciled status, and the status of the transaction details that do not participate in the reconciliation is set to the non-reconciled status.

[0098] Configuration step 4: Update the configuration items of the reconciliation file configuration table;

[0099] In the specific implementation, the pre-processing import SQL statement corresponding to the temporary table of transaction flow details in the reconciliation file configuration table is updated.

[0100] Configuration step 5: Complete the configuration of the reconciliation file configuration table (reconciliation file configuration is completed).

[0101] Step S102: executing the reconciliation file storage process based on the reconciliation file configuration table.

[0102] In the specific implementation of step S102 , based on the configuration data in the reconciliation file configuration table, the reconciliation file storage process is executed to implement a configurable universal reconciliation file storage method.

[0103] Step S103: Obtain a pre-configured reconciliation element configuration table.

[0104] In the specific implementation of step S103, it is determined whether the reconciliation element configuration has been completed; if the reconciliation element configuration has been completed, a pre-configured reconciliation element configuration table is obtained; if the reconciliation element configuration has not been completed, the configuration items of the reconciliation element configuration table are configured to obtain a configured reconciliation element configuration table.

[0105] It should be noted that for a reconciliation mode of a reconciliation business type, two data tables should be configured in the reconciliation element configuration table. These two data tables are the transaction flow details tables of Party A and Party B (referred to as the transaction flow details tables of both parties A and B); the reconciliation element configuration table contains at least the following fields: reconciliation business type, data table name, reconciliation element combination, and reconciliation mode. That is, the fields of the reconciliation element configuration table are shown in Table 2.

[0106] Table 2:

[0107]

[0108] In Table 2 above, the meanings of the fields in the reconciliation element configuration table are as follows:

[0109] Reconciliation business type: the business type that needs to be reconciled;

[0110] Data table name: the name of the data table that needs to be reconciled;

[0111] Reconciliation element combination: the combination of field names required for reconciliation in the data table, connected by the “||” symbol;

[0112] Reconciliation mode: Customized reconciliation mode, for example, full reconciliation or principal reconciliation, etc.

[0113] In some embodiments, if the reconciliation element configuration is not completed, the configuration method of the reconciliation element configuration table is: add the reconciliation element configuration of the formal tables of transaction flow details on both sides of the newly added reconciliation business (formal table A of transaction flow details and formal table B of transaction flow details) in the reconciliation element configuration table. Specifically, the transaction flow date corresponding to the reconciliation process is defined as the date of the day, and the date of the previous day of the transaction flow is defined as the date of the next day. Taking into account the daily cutting situation of the transaction system, some transactions may appear in the files of the next day. It is necessary to screen out the details of the unreconciled status in the transaction flow of the next day and the details of the unreconciled status in the transaction flow of the current day for reconciliation.

[0114] Step S104: Execute the transaction flow detail reconciliation process based on the reconciliation element configuration table.

[0115] In the specific implementation of step S104, based on the configuration data in the reconciliation element configuration table, the transaction flow detail reconciliation process is executed to implement a configurable general transaction detail reconciliation method.

[0116] In the embodiment of the present invention, the reconciliation file storage process and the transaction flow detail reconciliation process are respectively performed through the configurable reconciliation file configuration table and the reconciliation element configuration table to meet the reconciliation needs of various business scenarios.

[0117] To better understand the above embodiments of the present invention Figure 1 The content in Figure 2 Another flowchart of a method for processing reconciliation data is shown as an example. Figure 2 The following steps are involved:

[0118] Step S201: Determine whether the reconciliation file configuration is completed; if not, execute step S202; if completed, execute step S203.

[0119] Step S202: configure the configuration items in the reconciliation file configuration table.

[0120] Step S203: Execute the reconciliation file storage process based on the reconciliation file configuration table.

[0121] Step S204: Determine whether the reconciliation element configuration is completed; if not, execute step S205; if completed, execute step S206.

[0122] Step S205: configure the configuration items in the reconciliation element configuration table.

[0123] Step S206: Execute the transaction flow detail reconciliation process based on the reconciliation element configuration table.

[0124] It should be noted that the execution principle of steps S201 to S206 can be found in the above embodiment of the present invention. Figure 1 The content will not be repeated here.

[0125] For the above-mentioned embodiment of the present invention Figure 1 Step S102 involves executing the reconciliation file storage process based on the reconciliation file configuration table. Overall, the reconciliation file storage process includes "acquisition of reconciliation files", "file preprocessing", "file import into temporary table", "total score verification" and "importing temporary table into formal table". Figure 3 Let's explain in detail the process of reconciliation file storage.

[0126] See also Figure 3 , shows a flowchart of a process for executing a reconciliation file storage process provided by an embodiment of the present invention, including the following steps:

[0127] Step S301: based on the file name, file format suffix and file acquisition channel in the reconciliation file configuration table, obtain the file to be reconciled.

[0128] In the specific implementation of step S301, the to-be-reconciled file is obtained based on the file name, file format suffix and file acquisition channel in the reconciliation file configuration table;

[0129] For example: according to the reconciliation business type, the file name, file format suffix and file acquisition channel of the file to be reconciled are obtained from the reconciliation file configuration table, and the transaction flow files of Party A and Party B (the transaction flow files of both parties A and B, i.e. the files to be reconciled) are obtained on a daily basis to the server file directory, so as to conduct the reconciliation of transaction flow details;

[0130] Among them, if a certain type of transaction flow file is missing, the delayed re-retrieval mechanism for obtaining the transaction flow file of this type will be triggered.

[0131] Step S302: pre-process the account reconciliation file.

[0132] In the process of implementing step S302, the file to be reconciled is preprocessed. Specifically, the format of the file to be reconciled (such as a transaction flow file) may be txt, csv, plain text without a suffix, or other file formats. The file format suffix is ​​obtained from the reconciliation file configuration table according to the file name of the file to be reconciled, and then the parsing method corresponding to the file format suffix is ​​adapted to preprocess the file to be reconciled.

[0133] Step S303: Import the pre-processed to-be-reconciled file into a temporary table.

[0134] In the specific implementation of step S303, the import program template (such as import SQL template) corresponding to the temporary table name and the database type used is obtained from the reconciliation file configuration table;

[0135] According to the file import mode, use the import program template to generate the actual import program statement (such as the actual import SQL statement), and the file import mode is the field fixed-length mode or the field delimiter mode; based on the actual import program statement, import the preprocessed file to be reconciled into the temporary table corresponding to the temporary table name.

[0136] Taking the case where the to-be-reconciled file includes a transaction flow detail file and a transaction flow summary file, the import program template is an import SQL template, and the actual import program statement is an actual import SQL statement as an example, the following example illustrates how to import the to-be-reconciled file (preprocessing has been completed, referred to as the to-be-reconciled file here) into a temporary table:

[0137] According to the file name, obtain the temporary table name for storing the file and the import SQL template corresponding to the database type used from the reconciliation file configuration table.

[0138] If the file import mode is the fixed-length field mode, you need to first query the table structure of the temporary table from the database to obtain the length of each field, and then use the import SQL template to splice the temporary table name, file name, fixed-length field and other elements to form the actual import SQL statement;

[0139] If the file import mode is the field separator mode, the import SQL template is used to concatenate temporary table names, file names, field separators and other elements to form the actual import SQL statement.

[0140] The transaction flow detail files on both sides of the reconciliation business are imported into the transaction flow detail temporary table on both sides using the actual import SQL statement formed above, and the transaction flow summary files on both sides of the reconciliation business are imported into the summary table on both sides using the actual import SQL statement.

[0141] Step S304: performing a total score check on the temporary table into which the pre-processed to-be-reconciled file is imported.

[0142] In the specific implementation of step S304, the total score is checked on the temporary table into which the pre-processed to-be-reconciled file is imported.

[0143] For example: in the process of importing the reconciliation file into the temporary table, the transaction flow detail files on both sides of the reconciliation business are imported into the transaction flow detail temporary table on both sides using the actual import SQL statement formed above, and the transaction flow summary files on both sides of the reconciliation business are imported into the summary table on both sides using the actual import SQL statement;

[0144] Compare the "summary amount calculated by summing up the temporary tables of transaction flow details on both sides" with the summaries on both sides to determine whether the total transaction flow on both sides is consistent;

[0145] If the total score of the transaction flow on both sides is consistent, it is determined that the total score verification has passed, and step S305 is executed;

[0146] If the total scores of the transaction flows on both sides do not match, it is determined that the total score verification has not passed, and the reconciliation needs to be terminated and the reasons need to be analyzed.

[0147] That is, if the total score of the transaction flow on either side does not match, it means that the data in the transaction detail table and the summary table are inconsistent, and the reconciliation needs to be terminated and the cause needs to be analyzed.

[0148] Step S305: when the total score verification is passed, the temporary table containing the pre-processed files to be reconciled is imported into the formal table.

[0149] In the specific implementation of step S305, when the total score verification is passed, the temporary table into which the pre-processed to-be-reconciled file is imported is imported into the formal table.

[0150] For example: According to the table name of the temporary table of transaction flow details, the formal table name to be imported and the SQL statement used to import the formal table are queried from the reconciliation file configuration table; based on the formal table name and SQL statement, the temporary tables of transaction flow details on both sides of various reconciliation businesses are pre-processed and inserted into the corresponding formal tables of transaction flow details on a daily basis.

[0151] To better understand the above reconciliation file storage process, take the mode where both parties (Party A and Party B) provide a batch of transaction flow every day as an example. Figure 4 Another flowchart of the execution of the reconciliation file storage process is shown as an example. Figure 4 The steps include:

[0152] Step S401: Obtain reconciliation file.

[0153] It should be noted that the files to be reconciled obtained include detailed files and summary files of both parties A and B.

[0154] Step S402: file preprocessing.

[0155] Step S403: Import the file into a temporary table.

[0156] It should be noted that Figure 4 The "File Import Data Table" in is the file import temporary table.

[0157] Step S404: total score verification.

[0158] Step S405: Import the temporary table into the formal table.

[0159] above Figure 3 and Figure 4 It is an explanation about the process of entering reconciliation documents into the warehouse.

[0160] For the above-mentioned embodiments of the present invention Figure 1 Step S104 involves executing the transaction flow reconciliation process based on the reconciliation element configuration table. Overall, the transaction flow reconciliation process includes the following parts: obtaining reconciliation configuration parameters, splicing reconciliation program statements (splicing reconciliation SQL statements), resetting the check mark, checking the next day's account, checking the current day's account, setting the wrong account mark, and statistical analysis of the reconciliation results; the following is Figure 5 Let's explain in detail the transaction flow reconciliation process.

[0161] See also Figure 5 , shows a flow chart of executing a transaction flow detail reconciliation process provided by an embodiment of the present invention, including the following steps:

[0162] Step S501: Obtain reconciliation configuration parameters from the reconciliation element configuration table.

[0163] It should be noted that the reconciliation configuration parameters at least include a reconciliation element combination string and the number of reconciliation element fields.

[0164] In the specific implementation process of step S501, the specified data table is obtained from the reconciliation element configuration table according to the reconciliation business type and the reconciliation mode; the reconciliation element combination string required for reconciliation of the specified data table is obtained from the reconciliation element configuration table according to the table name and the reconciliation mode of the specified data table; and the number of reconciliation element fields is calculated based on the reconciliation element combination string.

[0165] Specifically, according to the reconciliation business type and the reconciliation mode, the data tables are arranged in order and in reverse order based on the table names in the reconciliation element configuration table, the first number is taken from the data table arranged in order to obtain one of the designated data tables corresponding to the reconciliation business type (referred to as data table A), and the first number is taken from the data table arranged in reverse order to obtain another designated data table corresponding to the reconciliation business type (referred to as data table B);

[0166] That is, from the reconciliation element configuration table, the first one is taken by arranging the data table names in order and in reverse order, so as to obtain the designated data tables (data table A and data table B) on both sides corresponding to the reconciliation business type.

[0167] According to the table name and reconciliation mode of the specified data table, the reconciliation element combination string required for the reconciliation of the specified data table is obtained from the reconciliation element configuration table. For example, the reconciliation element combination string str1 and str2 required for the reconciliation of data table A and data table B (specified data tables on both sides) on both sides are obtained from the reconciliation element configuration table.

[0168] After obtaining the reconciliation element combination string required for reconciliation of the specified data table, replace the “||” symbol in the reconciliation element combination string with the “|” symbol, calculate the length difference between the reconciliation element combination string after the “|” symbol is replaced and the original reconciliation element combination string, thereby obtaining the number of reconciliation element fields n;

[0169] That is, the number n of reconciliation element fields is calculated based on the length difference between the string obtained by replacing the “||” symbol in the reconciliation element combination string with the “|” symbol and the original reconciliation element string.

[0170] Step S502: obtaining a reconciliation program statement based on the reconciliation configuration parameters.

[0171] In the process of specifically implementing step S502, based on the obtained reconciliation configuration parameters, the reconciliation program statement is spliced; for example: taking the transaction parties as Party A and Party B, and the reconciliation program statement as a reconciliation SQL statement as an example, the specific method of splicing the reconciliation SQL statement includes the following splicing steps 1 to 9.

[0172] Step 1: Set the SQL template statement sqlcmd1 for Party A's reconciliation to: If both Party A's details and Party B's details are in an unreconciled state, update Party A's details to a checked state; set the SQL template statement sqlcmd2 for Party B's reconciliation to: If Party A's details are in a checked state and Party B's details are in an unreconciled state, update Party B's details to a checked state.

[0173] Stitching step 2: Set the initial loop factor i to 1.

[0174] Joining step 3: Set the position of the “||” symbol in the reconciliation element combination string str1 to be joined by Party A to pos1, and the position of the “||” symbol in the reconciliation element combination string str2 to be joined by Party B to pos2.

[0175] Concatenation step 4: The substring substr1 of str1 from position 1 to pos1-1 is: the i-th reconciliation element in Party A's reconciliation element combination; the substring substr2 of str2 from position 1 to pos2-1 is: the i-th reconciliation element in Party B's reconciliation element combination.

[0176] Concatenation step 5: Concatenate the specified SQL statement "andA.substr1=B.substr2" after sqlcmd1 and sqlcmd2.

[0177] Concatenation step 6: Replace the string from position 1 to pos1+1 of str1 with an empty string to obtain a new reconciliation element combination string str1 to be concatenated, and replace the string from position 1 to pos2+1 of str2 with an empty string to obtain a new reconciliation element combination string str2 to be concatenated.

[0178] Splicing step 7: The loop factor i is increased by 1.

[0179] Splicing step 8: Repeat splicing steps 3 to 7 until the loop factor i in splicing step 7=n+1, and the loop ends.

[0180] Step 9 of splicing: The SQL statement sqlcmd1 for reconciliation of Party A after splicing is: if the details of Party A and Party B are both in the unreconciled state, and each reconciliation element of Party A is equal to each reconciliation element of Party B, then update the details of Party A to the checked state;

[0181] After splicing, the function of the SQL statement sqlcmd2 for reconciliation of Party B is: if the details of Party A are in a checked state, the details of Party B are in an unreconciled state, and each reconciliation element of Party A corresponds to each reconciliation element of Party B, then update the details of Party B to a checked state.

[0182] After the reconciliation program statement (reconciliation SQL statement) is obtained through splicing, the check mark is reset, that is, the next day date details in the transaction flow detail table on both sides that are in a unilateral state are reset to an unreconciled state.

[0183] Step S503: Use the reconciliation program statement to reconcile the current day account and the next day account to obtain the reconciliation result.

[0184] In the specific implementation of step S503, the next day's account and the current day's account are reconciled, and the current day's account and the next day's account are reconciled by using the reconciliation program statement to obtain the reconciliation result.

[0185] For example: in the process of reconciling the next day's accounts, splice the reconciliation SQL statements of Party A's next day's details with Party B's current day's details and perform the next day's account reconciliation, splice the reconciliation SQL statements of Party B's next day's details with Party A's current day's details and perform the next day's account reconciliation;

[0186] In the process of reconciling the daily accounts, the reconciliation SQL statements of Party A's daily details and Party B's daily details are spliced ​​and the daily accounts are reconciled.

[0187] It should be noted that after reconciling the accounts of the current day and the accounts of the next day, the wrong account flag is set to put the details that failed to be reconciled into a unilateral state. For example, the wrong account flag is set to put the details that failed to be reconciled in the details of the next day of both parties A and B into a unilateral state.

[0188] Step S504: Perform statistical analysis on the reconciliation result.

[0189] In the specific implementation of step S504, after checking the current day account and checking the next day account to perform the current day account reconciliation and the next day account reconciliation, the obtained reconciliation results are statistically analyzed.

[0190] Specifically, the statistical analysis of the reconciliation results includes the following: retrieval of transaction flow details on both sides, statistics of unilateral detailed accounts of transaction flows on both sides, formation of credit adjustment error transactions after unilateral details are cleared, and generation of adjustment details of credit adjustment error transactions in batches on a daily basis. The adjustment system performs daily adjustment processing based on the adjustment details.

[0191] To better understand the transaction flow reconciliation process, take the transaction two parties (Party A and Party B) provide a batch of transaction flow every day as an example. Figure 6 Another flowchart of the execution transaction flow detail reconciliation process is shown as an example. Figure 6 The steps include:

[0192] Step S601: Obtain reconciliation configuration parameters.

[0193] Step S602: concatenate reconciliation SQL statements.

[0194] Step S603: Reset the check mark.

[0195] Step S604: Check the next day's account.

[0196] Step S605: Check the account for the day.

[0197] Step S606: Set the wrong account flag.

[0198] Step S607: Statistical analysis of reconciliation results.

[0199] above Figure 5 and Figure 6 It is an explanation about the transaction flow details reconciliation process.

[0200] In summary, this solution builds a reconciliation file configuration table that meets the necessary process of the reconciliation system. According to different file import modes, the SQL template required for the corresponding file storage can be determined; after determining the storage SQL template, the specific file storage SQL can be obtained according to the import file name, database table name and other information configured in the reconciliation file configuration table, thereby successfully completing the file storage operation.

[0201] This solution configures the reconciliation elements in advance through the reconciliation element configuration table, automatically generates reconciliation SQL statements and finally completes the reconciliation. Specifically, this solution adopts the method of updating the data in the transaction detail tables on both sides of the reconciliation system database for reconciliation. Even if processing tens of millions of reconciliation detail data, data processing can be completed quickly and efficiently.

[0202] When facing new business needs, this solution does not need to develop reconciliation file warehousing procedures and transaction detail reconciliation procedures for specific reconciliation businesses. It only needs to add configuration items to the reconciliation file configuration table and the reconciliation element configuration table to quickly implement the development work of new reconciliation business needs, which greatly reduces the development cost of new businesses in the reconciliation system.

[0203] Furthermore, since the reconciliation file configuration and reconciliation element configuration are not single and unchangeable, they can quickly respond to business changes by adding, deleting and modifying. Therefore, this solution can be applied to the reconciliation of multiple transaction businesses at the same time, and can also be applied to the rapid adaptation when the reconciliation elements required for the reconciliation scenario of a certain business change. Obviously, it can also be applied to the situation where the reconciliation function of a certain business is quickly shut down after the business disappears.

[0204] Corresponding to the method for processing reconciliation data provided in the above embodiment of the present invention, see Figure 7 , an embodiment of the present invention further provides a structural block diagram of a reconciliation data processing device, the reconciliation data processing device comprising: a first acquisition unit 100, a first execution unit 200, a second acquisition unit 300 and a second execution unit 400;

[0205] The first acquisition unit 100 is used to acquire a pre-configured reconciliation file configuration table when detecting that reconciliation starts. The reconciliation file configuration table at least includes the following fields: reconciliation business type, file name, file format suffix, file acquisition channel, temporary table, formal table, and pre-processing import program statement.

[0206] The first execution unit 200 is configured to execute a reconciliation file storage process based on the reconciliation file configuration table.

[0207] The second acquisition unit 300 is used to acquire a pre-configured reconciliation element configuration table, which at least includes the following fields: reconciliation business type, data table name, reconciliation element combination, and reconciliation mode;

[0208] The second execution unit 400 is used to execute the transaction flow detail reconciliation process based on the reconciliation element configuration table.

[0209] In the embodiment of the present invention, the reconciliation file storage process and the transaction flow detail reconciliation process are respectively performed through the configurable reconciliation file configuration table and the reconciliation element configuration table to meet the reconciliation needs of various business scenarios.

[0210] Preferably, combined Figure 7 As shown in the figure, the first execution unit 200 includes an acquisition module, a preprocessing module, a first import module, a checking module and a second import module. The execution principle of each module is as follows:

[0211] The acquisition module is used to obtain the file to be reconciled based on the file name, file format suffix and file acquisition channel in the reconciliation file configuration table.

[0212] The preprocessing module is used to preprocess the reconciliation files.

[0213] The first import module is used to import the pre-processed to-be-reconciled file into a temporary table.

[0214] In the specific implementation, the first import module is specifically used to: obtain the import program template corresponding to the temporary table name and the database type used from the reconciliation file configuration table; generate the actual import program statement using the import program template according to the file import mode, and the file import mode is the field fixed-length mode or the field separator mode; based on the actual import program statement, import the preprocessed file to be reconciled into the temporary table corresponding to the temporary table name.

[0215] The verification module is used to verify the total score of the temporary table into which the pre-processed files to be reconciled are imported.

[0216] The second import module is used to import the temporary table containing the pre-processed files to be reconciled into the formal table when the total score is checked.

[0217] Preferably, combined Figure 7 The second execution unit 400 includes a configuration parameter acquisition module, a splicing module, a reconciliation module and a statistics module. The execution principle of each module is as follows:

[0218] The configuration parameter acquisition module is used to obtain the reconciliation configuration parameters from the reconciliation element configuration table. The reconciliation configuration parameters at least include the reconciliation element combination string and the number of reconciliation element fields.

[0219] In the specific implementation, the configuration parameter acquisition module is specifically used to: obtain the specified data table from the reconciliation element configuration table according to the reconciliation business type and reconciliation mode; obtain the reconciliation element combination string required for the reconciliation of the specified data table from the reconciliation element configuration table according to the table name and reconciliation mode of the specified data table; calculate the number of reconciliation element fields based on the reconciliation element combination string.

[0220] The splicing module is used to obtain the reconciliation program statements based on the reconciliation configuration parameters.

[0221] The reconciliation module is used to use the reconciliation program statements to reconcile the daily accounts and the next day's accounts to obtain the reconciliation results.

[0222] The statistics module is used to perform statistical analysis on the reconciliation results.

[0223] In summary, the embodiments of the present invention provide a method and device for processing reconciliation data, which respectively perform the reconciliation file warehousing process and the transaction flow detail reconciliation process through a configurable reconciliation file configuration table and a reconciliation element configuration table to meet the reconciliation needs of various business scenarios.

[0224] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can refer to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system or system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can refer to the partial description of the method embodiment. The system and system embodiments described above are merely schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without creative work.

[0225] Professionals may further appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in the above description according to function. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians may use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

[0226] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for processing reconciliation data, characterized in that: The method comprises: When detecting that the reconciliation starts, obtaining a pre-configured reconciliation file configuration table, wherein the reconciliation file configuration table at least includes the following fields: reconciliation business type, file name, file format suffix, file acquisition channel, temporary table, formal table, and pre-processing import program statement; Execute the reconciliation file storage process based on the reconciliation file configuration table; Obtain a pre-configured reconciliation element configuration table, wherein the reconciliation element configuration table includes at least the following fields: reconciliation business type, data table name, reconciliation element combination, and reconciliation mode; The transaction flow detail reconciliation process is executed based on the reconciliation element configuration table.

2. The method according to claim 1, characterized in that The reconciliation file storage process is executed based on the reconciliation file configuration table, including: Based on the file name, file format suffix and file acquisition channel in the reconciliation file configuration table, obtain the file to be reconciled; Preprocessing the to-be-reconciled file; Import the pre-processed to-be-reconciled file into a temporary table; Performing a total score check on the temporary table into which the pre-processed to-be-reconciled file is imported; When the total score verification is passed, the temporary table into which the pre-processed to-be-reconciled file is imported is imported into the formal table.

3. The method according to claim 2, characterized in that Import the pre-processed reconciliation file into a temporary table, including: Obtaining the import program template corresponding to the temporary table name and the database type used from the reconciliation file configuration table; According to the file import mode, using the import program template to generate an actual import program statement, the file import mode is a field fixed length mode or a field separator mode; Based on the actual import program statement, the pre-processed file to be reconciled is imported into the temporary table corresponding to the temporary table name.

4. The method according to claim 1, characterized in that: The transaction flow detail reconciliation process is executed based on the reconciliation element configuration table, including: Acquire reconciliation configuration parameters from the reconciliation element configuration table, wherein the reconciliation configuration parameters at least include a reconciliation element combination string and the number of reconciliation element fields; Based on the reconciliation configuration parameters, a reconciliation program statement is obtained; Use the reconciliation program statement to reconcile the current day account and the next day account to obtain the reconciliation result; Perform statistical analysis on the reconciliation results.

5. The method according to claim 4, characterized in that Obtaining reconciliation configuration parameters from the reconciliation element configuration table includes: According to the reconciliation business type and the reconciliation mode, a specified data table is obtained from the reconciliation element configuration table; According to the table name of the designated data table and the reconciliation mode, obtaining the reconciliation element combination character string required for reconciliation of the designated data table from the reconciliation element configuration table; The number of reconciliation element fields is calculated based on the reconciliation element combination character string.

6. A reconciliation data processing device, characterized in that: The device comprises: A first acquisition unit is used to acquire a pre-configured reconciliation file configuration table when detecting that reconciliation starts, wherein the reconciliation file configuration table at least includes the following fields: reconciliation business type, file name, file format suffix, file acquisition channel, temporary table, formal table, and pre-processing import program statement; A first execution unit, configured to execute a reconciliation file storage process based on the reconciliation file configuration table; A second acquisition unit is used to acquire a pre-configured reconciliation element configuration table, wherein the reconciliation element configuration table at least includes the following fields: reconciliation business type, data table name, reconciliation element combination, and reconciliation mode; The second execution unit is used to execute the transaction flow detail reconciliation process based on the reconciliation element configuration table.

7. The device according to claim 6, characterized in that The first execution unit comprises: An acquisition module, used to acquire the to-be-reconciled file based on the file name, file format suffix and file acquisition channel in the reconciliation file configuration table; A preprocessing module, used for preprocessing the to-be-reconciled file; A first import module, used for importing the pre-processed to-be-reconciled file into a temporary table; A checking module, used for checking the total score of the temporary table into which the pre-processed to-be-reconciled file is imported; The second import module is used to import the temporary table containing the pre-processed files to be reconciled into the formal table when the total score verification is passed.

8. The device according to claim 7, characterized in that The first import module is specifically used to: obtain an import program template corresponding to a temporary table name and a used database type from the reconciliation file configuration table; generate an actual import program statement using the import program template according to a file import mode, wherein the file import mode is a field fixed length mode or a field delimiter mode; Based on the actual import program statement, the pre-processed file to be reconciled is imported into the temporary table corresponding to the temporary table name.

9. The device according to claim 6, characterized in that The second execution unit comprises: A configuration parameter acquisition module, used to acquire reconciliation configuration parameters from the reconciliation element configuration table, wherein the reconciliation configuration parameters at least include a reconciliation element combination string and the number of reconciliation element fields; A splicing module, used for splicing to obtain a reconciliation program statement based on the reconciliation configuration parameters; A reconciliation module, used to use the reconciliation program statement to perform reconciliation of the same day account and the next day account to obtain a reconciliation result; The statistical module is used to perform statistical analysis on the reconciliation results.

10. The device according to claim 9, characterized in that The configuration parameter acquisition module is specifically used to: obtain a specified data table from the reconciliation element configuration table according to the reconciliation business type and the reconciliation mode; obtain the reconciliation element combination string required for reconciliation of the specified data table from the reconciliation element configuration table according to the table name of the specified data table and the reconciliation mode; and calculate the number of reconciliation element fields based on the reconciliation element combination string.

Images