Document coding is an essential function of ERP system, which is used to generate serial numbers of various documents, often using the date and time characters to generate a unique document number. From the software point of view, it is to generate the primary key value of the data table (reference number), from the user's point of view, is to create coding specifications for business documents. Then do see the name, such as the sales order number is SO201508190001, the purchase order number is PO201508190001.
1 Basic document Encoding document serialization basic
A document encoding master table for storing documents and their encoding rules.
CREATE TABLE[dbo]. [Documentserialization] ([Seriescode] [NVARCHAR] (8) not NULL, [Description] [NVARCHAR] (40) not NULL, [Suspended] [NVARCHAR] (1)NULL, [Seriallength] [DECIMAL] (2, 0)NULL, [Prefixlength] [DECIMAL] (2, 0)NULL, [Prefix] [NVARCHAR] (12)NULL, [nextseqno] [DECIMAL] (10, 0)NULL, [allowoverride] [NVARCHAR] (1)NULL, [CreatedDate] [DATETIME]NULL, [CreatedBy] [NVARCHAR] (10)NULL, [reviseddate] [DATETIME]NULL, [Revisedby] [NVARCHAR] (10)NULL, [Withreset] [NVARCHAR] (1)NULL, [prevresetdate] [DATETIME]NULL, [Prefixdefault] [NVARCHAR] (12)NULL,CONSTRAINT[Pk_documentserialization]PRIMARY KEY CLUSTERED([Seriescode]ASC) with(Pad_index =OFF, Statistics_norecompute =OFF, Ignore_dup_key =OFF, allow_row_locks = on, allow_page_locks = on) on[PRIMARY]) on[PRIMARY]go
For example, the meaning of these field values.
seriescode |
d Escription |
suspended |
seriallength |
prefixlength |
|
nextseqno |
slsosc |
Sales Order cancellation |
N |
[ |
6 |
[email ;p rotected]@m |
|
slsoso |
Sales Order Entry |
N |
12 /td> |
6 |
[email protected]@m |
4 |
slsosq |
Sales Quota tions processing |
N |
|
6 |
[email protected]@m |
2 |
Processing sales order function Slsoso, its document encoding total length is 12, the ordinal prefix length is 6, the ordinal prefix rule is [email protected]@m,@y represents two-digit years, @M represents two-digit month, the next document code serial number is 4, So when the document encoding that deals with sales orders is generated, it is SO1508000004.
2 macro Processing
Sometimes we need to choose one or more serial number generation method according to the situation, when generating the sequence number of the form, let us choose which prefix encoding scheme, such as the purchase order encoding rules, sometimes PO201508180001, sometimes OE201508180001, their prefixes (Prefix) is not the same. To achieve this, we add a child table to documentserialization.
CREATE TABLE[dbo]. [Documentserializationdetail] ([Index] [int] not NULL, [seriescode] [nvarchar] (8)COLLATESQL_Latin1_General_CP1_CI_AS not NULL,[Prefix] [varchar] (12)COLLATESQL_Latin1_General_CP1_CI_AS not NULL, [TextPattern] [varchar] (12)COLLATESQL_Latin1_General_CP1_CI_AS not NULL CONSTRAINT, [nextseqno] [decimal] (10, 0)NULL, [CreatedDate] [datetime]NULL, [CreatedBy] [varchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [reviseddate] [datetime]NULL, [Revisedby] [varchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [Suspended] [varchar] (1)COLLATESQL_Latin1_General_CP1_CI_ASNULL) on[PRIMARY]GOALTER TABLE[dbo]. [Documentserializationdetail]ADD CONSTRAINT[Pk_documentserializationdetail]PRIMARY KEY CLUSTERED([Index], [Seriescode]) on[PRIMARY]GOALTER TABLE[dbo]. [Documentserializationdetail]ADD CONSTRAINT[Fk_documentserializationdetail_documentserialization]FOREIGN KEY([Seriescode])REFERENCES[dbo]. [Documentserialization] ([Seriescode])GO
Refer to the following data examples to understand the meaning of this table:
Seriescode |
Prefix |
TextPattern |
Nextseqno |
Slsoso |
[Email protected]@m |
PO |
9 |
Slsoso |
[Email protected]@m |
Oe |
17 |
Using a question mark as a placeholder, pop-up the form at run time to let the user choose which document encoding scheme, the user selects the PO, generates the PO201508 prefix of the purchase order encoding, if the user chooses OE, generates the OE201508 prefix of the purchase order encoding.
To deepen the understanding of placeholder symbols, the following examples are described.
1 The prefix definition value is?? ABC, the user chooses XY, then returns the prefix result xyabc.
2 prefix definition??? ABC, the user selects XY, returns the result before the XY_ABC, and replaces the placeholder symbol with an underscore.
3 prefix definition @[email protected] @YABC, the current date is August 19, 2015, then the generated prefix value is 150819ABC.
3 User-based coding scheme user-based document serialization
Sometimes different users have different document coding rules, we need to follow the user to create the coding rules. Create the database first.
CREATE TABLE[dbo]. [Documentserializationuser] ([Index] [int] not NULL, [seriescode] [nvarchar] (8)COLLATESQL_Latin1_General_CP1_CI_AS not NULL, [UserId] nvarchar (10) not NULL COLLATESQL_Latin1_General_CP1_CI_AS not NULL,[Prefix] [varchar] (12)COLLATESQL_Latin1_General_CP1_CI_AS not NULL, [TextPattern] [varchar] (12)COLLATESQL_Latin1_General_CP1_CI_AS not NULL CONSTRAINT, [nextseqno] [decimal] (10, 0)NULL, [CreatedDate] [datetime]NULL, [CreatedBy] [varchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [reviseddate] [datetime]NULL, [Revisedby] [varchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [Suspended] [varchar] (1)COLLATESQL_Latin1_General_CP1_CI_ASNULL) on[PRIMARY]GO
This table is also a sub-table of the ordinal encoding documentserialization, the primary key adds the user-encoded field userid, which records each user to encode the rule.
In the system, priority is given to user-based coding rules, followed by macro substitution processing, and finally to application-based coding rules.
4 Concurrent Processing Concurrency
When two concurrent users create or save the same business document at the same time, the system returns two identical document encodings, resulting in concurrency problems.
Programme A
When the business function is opened, the document code is created for the current document immediately, such as generating document encoding SO15080004, when the document is saved, it is found that the document code is used by other users, then re-generates a new business document encoding SO15080005, if it is found that the code is still occupied, Search down until you find the document encoding that you can save.
The advantage of this scheme is that documents can always be saved, and the disadvantage is that the document code that is seen in the interface is not necessarily the final saved document encoding.
Programme B
When opening a business function, document encoding is not generated and document encoding is generated only when the document is saved. Avoid document concurrency conflicts.
The advantage of this scheme is that there is no concurrency conflict, and the disadvantage is that document encoding can only be seen after the document is saved.
5 Coding rules Programming Document serialization programming
When the document is saved, the calling interface generates the encoding rules, referring to the following program fragment.
Ecnentity ECN ..... if string. Empty) { Idocumentserializationmanager Serializationmanager =proxyinstance<idocumentserializationmanager > (); Ecn. Ecnno = Serializationmanager.getnextserialno (SessionId, Seriescode, ECN. Ecnno, ECN);}
If an exception occurs when the entity of the business document is saved, the user code needs to be reset to clear the resulting ordinal encoding.
catch {adapter. Rollback (); if (ECN. IsNew && string . CompareOrdinal (ECN). Ecnno, currentrefno)! = 0) {try {ECN. Ecnno = CURRENTREFNO; Serializationmanager.resetnextsequenceno (Seriescode); } catch {}} throw ;}
6 Fixed Code rule fix document serialization
The above implementation of the document coding rules based on the serial number, if the document coding rules are relatively fixed, then the above method does not work. Please read the following requirements Note first:
The
Receives the customer order, enters into the contract number: HT201508003; then do the contract review, resulting in a contract review PS201508003; After the contract review, and then to the ERP system to do sales orders, the sales number is XSD201508003 If a contract is placed under three sales orders, the xsd201508003-01,xsd201508003-02,xsd201508003-03 three sales order number will be generated separately. Continue to issue the sales order, sales order XSD201508003 The issue number should be XSFH201508003, if the sales order XSD201508003 three shipments, then the resulting three sales issue ticket number is xsfh201508003-01, xsfh201508003-02,xsfh201508003-03.
Sales Contract |
Contract Review |
Sales order |
Sales Delivery |
Note |
HT201508003 |
PS201508003 |
A sales order XSD201508003 Three Sales orders Xsd201508003-01 xsd201508003-02 xsd201508003-03 |
A Sales Invoice XSFH201508003 Two sales invoices Xsfh201508003-01 xsfh201508003-02 |
Document number 201508003 from the sales contract to the sales issue are the same document number, but the encoding prefix is different. |
This coding scheme requires a document number to pass through the entire process, and the document number is passed from the start-point business document to the final business document, with only a different prefix.
To achieve this fixed-format document encoding, the flow of each document must be programmed to process, business documents should also have a fixed push-down process, do not be universal, but the advantages are very obvious, a number throughout the business document, very clear.
Analysis of document encoding function implementation of large. NET ERP System