CN104636341A

CN104636341A - Data cleaning storage method for added value tax one-number multi-name monitoring

Info

Publication number: CN104636341A
Application number: CN201310547671.5A
Authority: CN
Inventors: 陈勇; 范钢; 谢宇; 陈博; 潘竞旭; 孟祥宽
Original assignee: Aisino Corp
Current assignee: Aisino Corp
Priority date: 2013-11-06
Filing date: 2013-11-06
Publication date: 2015-05-20
Anticipated expiration: 2033-11-06
Also published as: CN104636341B

Abstract

The invention discloses a data cleaning storage method for added value tax one-number multi-name monitoring. The method includes the following steps that service analysis is conducted according to tax data items required for one-number multi-name detection; a database list structure is designed according to a service analysis result; data are cleaned, the data with the special meanings are processed, and a final result is stored according to the design of the database list structure. By the adoption of the method, the data required for one-number multi-name detection can be rapidly cleaned and extracted from huge data and ingeniously stored for preparation of following judgment, and the efficiency for one-number multi-name detection of a system is improved.

Description

A kind of data cleansing storage means for a value added tax several monitoring

Technical field

The present invention relates to tax control technical field, particularly a kind of data cleansing storage means for a value added tax several monitoring.

Background technology

Forgery prevention for value-added tax taxation control system is the important component part of national golden tax project.By using numerical ciphers and nnir Rtorae technologies and strengthen the antiforge function of special invoice, accomplish the phenomenon that successfully containment utilizes VAT invoice to evade taxes, evade taxation.

Enterprises end software of making out an invoice uses numerical ciphers and electronic information Storage Techniques, and the antiforge function of strengthening special invoice, realizes, to the value added tax general taxpayer tax source control, opening the system of VAT invoice for enterprise.

The tax data of current general taxpayer mainly to be made out an invoice software, the tax-supervise system network edition and CTAIS(CTAIS from enterprises end, China Tex Administration Information System) core levies and manages three systems.

CTAIS formulates " tax collection and administration service regulation ", " office of city level tax collection and administration business demand " and " State Tax Administration CTAIS development requires summary " with the State Tax Administration and develops for benchmark, and it is orient towards the whole country tax authorities at various levels, unify, large-scale application software.

Above three system log (SYSLOG)s tax data of general taxpayer's magnanimity, the application and development for all kinds of tax system provides original Data Source.

Therefore, how designing a kind of method by these huge data efficients ground cleaning and filtering and then can store, and is that a several doubtful point detects and raises the efficiency.That is: enterprise is when opening VAT invoice, and namely the enterprise if there is a corresponding multiple pin party name of pin side's duty paragraph is classified as a several doubtful point, is the research direction place of those skilled in the art.

Summary of the invention

The object of this invention is to provide a kind of data cleansing storage means for a value added tax several monitoring, can by the raw data cleaning and filtering of mixed and disorderly redundancy, then store by the structure of design, for data encasement is carried out in a follow-up several efficient judgement.

In order to achieve the above object, the invention provides a kind of data cleansing storage means for a value added tax several monitoring, it is characterized in that, it comprises the following steps:

Business diagnosis is carried out for tax data item needed for a several detection;

The design of database table structure is carried out for business diagnosis result;

Carry out data cleansing, and process the data of particular meaning, the design of net result by described database structure is stored.

Wherein, the step of carrying out business diagnosis for tax data item needed for a several detection is with the incidence relation between the data item needed for object oriented analysis technical Analysis and data item, be that value added tax several detection is analyzed, comprise value added tax and enter sales invoice data and tax collection and administration data.

Wherein, the design carrying out database table structure for business diagnosis result carries out modeling process with Object-Oriented Design thought to the data provided from data source, produce ETL database structure model, comprise income invoice tables of data, income invoice listings data table, sales invoice table, sales invoice listings data table, the several result table of No. one, income, tax authority's tables of data, operating personnel's data table and taxpayer's tables of data.

Wherein, ETL process need carries out abnormality processing, and defines a series of tables, exception, carries out special processing to some special processing objects.

Wherein, enter sales invoice data and listings data respectively with authenticated time with declare dutiable goods the time for subregion key, monthly a subregion carries out partition zone optimizing storage, and sets up index on relevant field.

Wherein, design business diagnosis result being carried out to database table structure adopts relevant polymerization table technology, when considering Report Form Design, is saved in tables of data in advance by pre-service by the combined data calculated, and is directly supplied to user's inquiry.

Wherein, carry out data cleansing, and process the data of particular meaning, being undertaken storing by the design of described database structure by net result is wash irrelevant, redundancy, mixed and disorderly data, the data of process particular meaning, store net result by database design.

Wherein, for ETL process, need from source database, extract required data, and authority data form, be stored in ETL data model, and pay close attention on the accuracy of data, the performance of process and operation expanding.

Wherein, carrying out data cleansing, and process the data of particular meaning, during net result is stored by the design of described database structure, when extracting raw data, removing according to tables, exception or replacing unusual character, may find that the content that individual data is filled in is incorrect after extraction is come,, after correcting these data by backstage management procedure, mark them no longer by synchronized update, with specification raw data.

Wherein, carrying out data cleansing, and processing the data of particular meaning, in being stored by the design of described database structure by net result, the ETL process of source data specifically comprises: the extraction of income invoice data, conversion, loading procedure; The extraction of income invoice listings data, conversion, loading procedure; The extraction of sales invoice data, conversion, loading procedure; The extraction of sales invoice listings data, conversion, loading procedure; Tax authority's data extraction, conversion, loading procedure; The extraction of operating personnel's data, conversion, loading procedure; The several data extraction of the extraction of taxpayer's data, conversion, loading procedure and No. one, income, conversion, loading procedure.

Wherein, the several data extraction of No. one, income, conversion, loading procedure comprise the following steps:

Scanning value added tax income invoice table on the same day, gets rid of pin side Taxpayer Identification Number or name and is called sky, or pin square that pays taxes is other provinces taxpayer, or income invoice be for drawing a bill, the incomplete data of data;

One by one by the pin side's enterprise name in income invoice, carry out similarity-rough set with the taxpayer's title in taxpayer's registration form according to following rule, be inserted in the several result table of No. one, income.

Compared with prior art, beneficial effect of the present invention is: the present invention can Rapid Cleaning extract to detect several required data inside huge data, and can store dexterously as follow-up judgement provides preparation, improve the efficiency that No. one, systems axiol-ogy is several.

Accompanying drawing explanation

A kind of data cleansing storage means process flow diagram for a value added tax several monitoring that Fig. 1 provides for the embodiment of the present invention.

Embodiment

Below in conjunction with accompanying drawing, to above-mentioned being described in more detail with other technical characteristic and advantage of the present invention.

As shown in Figure 1, be a kind of data cleansing storage means process flow diagram for a value added tax several monitoring that the embodiment of the present invention provides, a kind of data cleansing storage means for a value added tax several monitoring of the present invention comprises the following steps:

Step S1: carry out business diagnosis for tax data item needed for a several detection;

In this step, be with the incidence relation between the data item needed for object oriented analysis technology (OOA) analysis and data item, for the table structure of database provides foundation.In business, we are to value added tax several detection (in a regional extent, the situation of the multiple title of duty paragraph in cash sale side is gone out in all income invoices) analyze, for: value added tax enters sales invoice data, (what be mainly derived from that the tax-supervise system network edition provides sends a duplicate to certification snapshot data to main operand involved by discovery, comprise and send a duplicate to invoice conterfoil part detail, send a duplicate to non-deduction stub detailed, the data such as certification invoice offsetting slip is detailed, and enterprises end long-range send a duplicate to that authentication software gathers enter sales invoice seven key element, invoice schedule, the data such as invoice scanned picture) and tax collection and administration data (be mainly derived from CTAIS CTAIS and comprise tax authority's data, operating personnel, taxpayer, the data such as industry).

Step S2: the analysis result for step S1 carries out the design of database table structure;

This step is the analysis result (mainly comprising the relation between data item required for a several detection and each data item) for step S1, carries out the design of database, takes into full account the factor such as search efficiency, data scale simultaneously.According to service needed, we carry out modeling process with Object-Oriented Design thought (OOD) to the data provided from data source, produce ETL database structure model, mainly comprise income invoice tables of data, income invoice listings data table, sales invoice table, sales invoice tablet menu, the several result table of No. one, income, tax authority's tables of data, operating personnel's data table, taxpayer's tables of data etc.; In abnormality processing, simultaneously because other third party's Data in Information Management System is inaccurate, in order to avoid causing the incorrect of native system data results due to these inexact datas, ETL process need carries out abnormality processing, and define a series of tables, exception, special processing is carried out to some special processing objects; In performance optimization, the first, in order to improve the efficiency of access, enter sales invoice data and listings data respectively with authenticated time with declare dutiable goods the time for subregion key, monthly a subregion carries out partition zone optimizing storage, and sets up index on relevant field; The second, in order to improve Consumer's Experience, native system adopts relevant polymerization table technology, when considering Report Form Design, is saved in tables of data in advance by pre-service by the combined data calculated, and is directly supplied to user's inquiry, shortens query responding time in a large number.

Step S3: source data is cleaned and stores.

This step washes irrelevant, redundancy, mixed and disorderly data, and the data of process particular meaning, store the database design of net result by step S2.For ETL process, we need from source database, extract required data, and authority data form, be stored in ETL data model.Income invoice data are mainly derived from network edition certification invoice offsetting slip detailed data and enterprises end offsetting slip detailed data (being associated with invoice codes and invoice number field); Income invoice listings data is mainly derived from enterprises end offsetting slip itemized bill data, and associates income invoice data; Sales invoice data are mainly derived from the network edition and send a duplicate to invoice conterfoil part detailed data and enterprises end stub detailed data (being associated with invoice codes and invoice number field); Sales invoice listings data is mainly derived from enterprises end stub itemized bill data, and associates sales invoice data; The several result table of No. one, income can utilize existing income invoice data and registration taxpayer information architecture in ETL model; Tax authority's data are mainly extracted from CTAIS tax authority code table; Operating personnel's data are mainly extracted from CTAIS operating personnel's code table; Taxpayer's data are mainly from taxpayer's information that CTAIS registered or assert and acquisition (associating with taxpayer's electronic record field) extend information; In ETL process, we pay close attention on the accuracy of data, the performance of process and operation expanding: in data accuracy, raw data in the such as Chinese Fields such as title, abbreviation, because the error of typing usually occurs some unusual characters, as ". ", space etc.

The present invention, when extracting raw data, removing according to tables, exception or replacing unusual character, may find that the content that individual data is filled in is incorrect after extraction is come.We mark them no longer by synchronized update after correcting these data by backstage management procedure, with specification raw data, ensure the quality of data analysis; In treatment effeciency, for mass data, we do incremental processing by temporary table mechanism, only load certain section of time vicissitudinous taxpayer or tax authority's information; In order to guarantee business retaining history process data, for critical data table, set up and be set effective bit-identify, prevent historical data to be capped.

Below the concrete enforcement of above-mentioned steps is described in detail:

1.1, primitive data item:

For the business of a several detection, required raw data table is as follows:

(1) income invoice seven key element table

(2) income invoice scanned picture table

(3) income invoice commodity detail list

Title	Code	Annotation	Data type	Non-NULL major key
					Vendor code	CSDM	Data acquisition vendor code	VARchar2(20)	TRUEFALSE
Invoice codes	FPDM	Invoice codes	char(10)	TRUETRUE
					Invoice number	FPHM	Invoice number	char(8)	TRUETRUE
Article line number	HH	Article line number	NUMBER	TRUETRUE
					Item Title	WP_MC	Item Title	VARchar2(100)	TRUEFALSE
Article model	WP_XH	Article model	VARchar2(40)	FALSEFALSE
					Article unit	WP_DW	Article unit	VARchar2(32)	FALSEFALSE
Number of articles	SL	Number of articles	NUMBER(16,2)	TRUEFALSE
					Unit price	DJ	Unit price	NUMBER(16,2)	FALSEFALSE
The amount of money	JE	The amount of money	NUMBER(16,2)	TRUEFALSE
					The amount of tax to be paid	SE	The amount of tax to be paid	NUMBER(16,2)	TRUEFALSE
The tax rate	WP_SL	The tax rate	NUMBER(10,6)	TRUEFALSE

(4) sales invoice seven key element table

(5) sales invoice commodity detail list

(6) certification offsetting slip invoice is detailed

(7) stub invoice is sent a duplicate to detailed

(8) non-deduction stub detail list is sent a duplicate to

(9) taxpayer's information table is registered

(10) taxpayer's expansion table is registered

(11) taxpayer's qualification history information table is assert

(12) the detailed code table of industry

(13) taxpayer's qualification code table

(14) tax authority's code table

(15) operating personnel's code table

1.2, a several detection desired data item.The field of table each in 1.1 analyzed, draw the several required data item of No. one, detection, then design the storage list structure of these new data item, concrete grammar is shown in step S2.

Step S2, the analysis result for step S1 carries out the design of database table structure, and after system carries out cleaning and filtering to raw data, what obtain is the several required data of No. one, detection, and these data need in an organized way to deposit, and facilitate follow-up judgement.These data are pressed following organize by this method:

(1) value added tax income invoice table

(2) value added tax income invoice tablet menu

(3) value added tax sales invoice table

(4) value added tax sales invoice tablet menu

(5) tax authority's information table

(6) tax authority's information temporary table

(7) taxpayer's information table

(8) operating personnel's table

(9) operating personnel's temporary table

Title	Code	Annotation	Data type	Non-NULL	Major key
						Operating personnel's code	CZRY_DM	Operating personnel's code	char(11)	TRUE	FALSE
Tax authority's code	SWJG_DM	Tax authority's code	char(11)	TRUE	FALSE
						Operating personnel's title	CZRY_MC	Operating personnel's title	VARchar2(60)	TRUE	FALSE

(10) the several result table of No. one, income

Can find out, the data after cleaned only store just with a table, and these data are enough to ensure that No. one, detection is several.In addition, due to VAT invoice business distinctive month characteristic, the data of whole database table have also carried out partitioned storage by month, like this can quick position when retrieval.

Step S3: source data is cleaned and stores, wherein, the ETL process of source data specifically comprises:

Step S31: the extraction of income invoice data, conversion, loading procedure:

1) extract the tax-supervise system network edition and import certification offsetting slip invoice detail (RZ_FPDKL_MX) in data, stored in value added tax income invoice table (ETL_JXFP).Need in certification month to convert YYYYMM form to;

2) according to invoice codes and invoice number, import income invoice seven key element table (DKLMX) data from enterprises end software and obtain pin side's enterprise name (XF_QYMC), Acquiring enterprise's title (GF_QYMC), upgrade the pin side taxpayer title (XF_NSRMC) in value added tax income invoice table (ETL_JXFP) and the side of purchasing taxpayer title (GF_NSRMC) respectively;

3) obtain amount of money integrality (JEWZX) in income invoice seven key element table (DKLMX), be 1 data integrity, otherwise data are imperfect, result inserted " data whether complete (SFSJWZ) " field;

More than operate and process every day according to authenticated time.

Step S32: the extraction of income invoice listings data, conversion, loading procedure:

1) extract enterprises end authentication web software and import income invoice commodity detail list (DKLMX_QD) in data;

2) income invoice seven key element table (DKLMX) is associated;

More than operate and process every day according to authenticated time.

Step S33: the extraction of sales invoice data, conversion, loading procedure:

1) send a duplicate to invoice conterfoil part detail list (CB_FPCGL_MX) in extraction tax-supervise system network edition importing data and obtain VAT invoice, stored in value added tax sales invoice table (ETL_XXFP).Declare dutiable goods and need in month to convert YYYYMM form to;

2) send a duplicate to non-deduction stub detail list (CB_FDKFPCGL_MX) in extraction tax-supervise system network edition importing data and obtain common invoice, stored in value added tax sales invoice table (ETL_XXFP).Declare dutiable goods and need in month to convert YYYYMM form to;

3) according to invoice codes and invoice number, import sales invoice seven key element table (CGLMX) data from enterprises end software and obtain pin side's enterprise name (XF_QYMC), Acquiring enterprise's title (GF_QYMC), upgrade the pin side taxpayer title (XF_NSRMC) in value added tax sales invoice table (ETL_XXFP) and the side of purchasing taxpayer title (GF_NSRMC) respectively;

4) obtain amount of money integrality (JEWZX) in income invoice seven key element table (DKLMX), be 1 data integrity, otherwise data are imperfect, result inserted " data whether complete (SFSJWZ) " field;

More than operation processes according to declaring dutiable goods every day time.

Step S34: the extraction of sales invoice listings data, conversion, loading procedure:

1) extract enterprises end authentication web software and import sales invoice commodity detail list (CGLMX_QD) in data;

2) sales invoice seven key element table (CGLMX) is associated;

Step S35: tax authority's data extraction, conversion, loading procedure:

1) according to tax authority's code, title, abbreviation, the tax authorities at higher levels, scanning tax authority's code table (DM_SWJG), finds out the information be updated, does abnormality processing simultaneously to unreasonable field or record;

2) be loaded into successively in tax authority's dimension table (DW_DIM_SWJG) according to level.

3) to the tax authority be updated, former data are upgraded:

A. ROW_IS_CURRENT in former data is set to N;

B. ROW_END_DATE in former data is set to current time;

Step S36: the extraction of operating personnel's data, conversion, loading procedure:

Compare according to operating personnel's code, title, scan operation personnel code table (DM_CZRY), finds out the information be updated and is stored in operating personnel's temporary table (ETL_CZRY_TMP);

1) according to temporary table information, the information of renewal is loaded into the person of managing Wei Biaoli;

2) to the person's of the managing information be updated, former data are upgraded:

A. ROW_IS_CURRENT in former data is set to N;

B. ROW_END_DATE in former data is set to current time;

Step S37: the extraction of taxpayer's data, conversion, loading procedure:

1) extract in core expropriation and management system importing data and register taxpayer's information table (DJ_NSRXX), according to the amendment date, renewal rewards theory is carried out to existing taxpayer in taxpayer's information table (ETL_NSR), update is performed to the taxpayer also do not had;

2) scan in core expropriation and management system importing data and register taxpayer's expansion table (DJ_NSRXX_KZ), association Taxpayer Identification Number (NSRSBH), according to the amendment date, renewal rewards theory is carried out to taxpayer's information corresponding in taxpayer's information table (ETL_NSR);

3) scan in core expropriation and management system importing data and assert taxpayer's qualification history information table (RD_NSRZG_LSXX),

4) associate Taxpayer Identification Number (NSRSBH), according to the amendment date, renewal rewards theory is carried out to taxpayer's information corresponding in taxpayer's information table (ETL_NSR);

Step S38: the several data extraction of No. one, income, conversion, loading procedure:

1) scans value added tax income invoice table on the same day (ETL_JXFP), get rid of pin side Taxpayer Identification Number or name and be called sky, or pin square that pays taxes is other provinces taxpayer, or income invoice be for drawing a bill, the incomplete data of data.

2) one by one by the pin side's enterprise name in income invoice, carry out similarity-rough set with the taxpayer's title in taxpayer's registration form according to following rule, be inserted in the several result table (ETL_JX_YHDM_JG) of No. one, income.

A, 2 enterprise names compared, if equal, similarity is 100%;

B, 2 enterprise names that will compare remove special character (as: space, bracket etc.) respectively;

C, 2 enterprise names that will compare remove the character (as: province, autonomous region, city, county, area etc.) representing area respectively;

D, 2 enterprise names that will compare remove the character (as: joint-stock company of company limited, responsibility company limited, company, factory, group etc.) representing enterprise nature respectively;

After e, respectively rejecting key word, calculate the similarity of 2 enterprise names; If similarity is 100% after rejecting, be then set to 99%.

In sum, the present invention Rapid Cleaning can extract to detect several required data inside huge data, and can store dexterously as follow-up judgement provides preparation, improves the efficiency that No. one, systems axiol-ogy is several.

More than illustrate just illustrative for the purpose of the present invention; and nonrestrictive, those of ordinary skill in the art understand, when not departing from the spirit and scope that claim limits; many amendments, change or equivalence can be made, but all will fall within protection scope of the present invention.

Claims

1., for a data cleansing storage means for a value added tax several monitoring, it is characterized in that, it comprises the following steps:

2. a kind of data cleansing storage means for a value added tax several monitoring according to claim 1, it is characterized in that, the step of carrying out business diagnosis for tax data item needed for a several detection is with the incidence relation between the data item needed for object oriented analysis technical Analysis and data item, be that value added tax several detection is analyzed, comprise value added tax and enter sales invoice data and tax collection and administration data.

3. a kind of data cleansing storage means for a value added tax several monitoring according to claim 1, it is characterized in that, the design carrying out database table structure for business diagnosis result carries out modeling process with Object-Oriented Design thought to the data provided from data source, produce ETL database structure model, comprise income invoice tables of data, income invoice listings data table, sales invoice table, sales invoice listings data table, the several result table of No. one, income, tax authority's tables of data, operating personnel's data table and taxpayer's tables of data.

4. a kind of data cleansing storage means for a value added tax several monitoring according to claim 3, it is characterized in that, ETL process need carries out abnormality processing, and defines a series of tables, exception, carries out special processing to some special processing objects.

5. a kind of data cleansing storage means for a value added tax several monitoring according to claim 3, it is characterized in that, enter sales invoice data and listings data respectively with authenticated time with declare dutiable goods the time for subregion key, monthly a subregion carries out partition zone optimizing storage, and sets up index on relevant field.

6. a kind of data cleansing storage means for a value added tax several monitoring according to claim 3, it is characterized in that, design business diagnosis result being carried out to database table structure adopts relevant polymerization table technology, when considering Report Form Design, in advance the combined data calculated is saved in tables of data by pre-service, is directly supplied to user's inquiry.

7. a kind of data cleansing storage means for a value added tax several monitoring according to claim 1, it is characterized in that, carry out data cleansing, and process the data of particular meaning, being undertaken storing by the design of described database structure by net result is wash irrelevant, redundancy, mixed and disorderly data, the data of process particular meaning, store net result by database design.

8. a kind of data cleansing storage means for a value added tax several monitoring according to claim 1, it is characterized in that, for ETL process, need from source database, extract required data, and authority data form, be stored in ETL data model, and pay close attention on the accuracy of data, the performance of process and operation expanding.

9. a kind of data cleansing storage means for a value added tax several monitoring according to claim 8, it is characterized in that, carrying out data cleansing, and process the data of particular meaning, during net result is stored by the design of described database structure, when extracting raw data, remove according to tables, exception or replace unusual character, may find that the content that individual data is filled in is incorrect after extraction is come, after correcting these data by backstage management procedure, mark them no longer by synchronized update, with specification raw data.

10. a kind of data cleansing storage means for a value added tax several monitoring according to claim 8, it is characterized in that, carrying out data cleansing, and process the data of particular meaning, in being stored by the design of described database structure by net result, the ETL process of source data specifically comprises: the extraction of income invoice data, conversion, loading procedure; The extraction of income invoice listings data, conversion, loading procedure; The extraction of sales invoice data, conversion, loading procedure; The extraction of sales invoice listings data, conversion, loading procedure; Tax authority's data extraction, conversion, loading procedure; The extraction of operating personnel's data, conversion, loading procedure; The several data extraction of the extraction of taxpayer's data, conversion, loading procedure and No. one, income, conversion, loading procedure.

11. a kind of data cleansing storage meanss for a value added tax several monitoring according to claim 10, is characterized in that, the several data extraction of No. one, income, conversion, loading procedure comprise the following steps: