When a document is processed against a Sales or Purchase Order a document reference number is assigned to that document – in the case of delivery notes this is used as the Delivery Note Reference, in the case of an invoice this is the Invoice No.
Existing options for Sales Invoices, when using the company wide Sales Invoice sequence, allowed the system to maintain a control database of invoice numbers used. This can be used for subsequent queries and a control log to prevent re-use of invoice references. This feature has now been extended to all Sales and Purchase Order document reference sequences.
Installation
Database Changes
Each document sequence will need its own DFD file. You can create the DFD using the Create/Amend Database Structure tools within the Database Systems menu. Select Database Systems followed by Datafiler Database Utilities and Create/Amend Database Structures. To create the database, select the option to Create New Database.
Database to Create – enter the filename of the new Database. The filename is typically created in an 8.3 format and standard Datafile terminology would be CCC-SPNN.DFD where CCC is the company id, SP is for Sales Order Processing (use PP for Purchase Order Processing) and NN represents the document number sequence.
Title – enter the title for the database, our suggestion would be SOP Document Control NN (where NN is the document sequence no).
Date Format – accept the default DD/MM/YY format
Auth-Level – accept the default authority level of ‘9’. This is the lowest authority level – you need all users who can process the document(s) to be able to add records to this DFD.
Once the initial database header detail is defined you can then enter the data item structure. For these databases the initial layout follows a fixed structure.
No Item Name Type Description
1 DOC-NO X (6) Prime-Index. Holds the document reference.
2 USER-ID X (6) Holds the user id who create the document using this reference.
3 DATE-ADDED E Holds the date the document was created
4 TIME-ADDED N Holds the time the document was created
5 ACCOUNT X (10) Holds the account for which the document was created
6 DOC-DATE E Holds the date the document was processed "as at”
7 ORDER-REF X (16) Holds the order ref the document was processed for (provided that the reference used against a "single order document”
Once the mandatory data items have been added press the <ESC> key and choose ‘Data Items are Complete’
Prime Index Data name – Select the ‘Doc-No’ data item.
Secondary Index 1/2 – you would typically select Account and Order Reference as the initial two secondary indexes.
Access by Record Number – respond N
Number of Records to Reserve– enter number of records required (typically we’d suggest 10000 as an initial value)
You will then be prompted to create the database – respond Yes – and then to Write the Database to disk – again respond Yes.
Apply Advanced Features to this Database – respond Y to this option, advanced features need to be applied to record the user-id, date and time.
In addition to referencing the user-id, date and time items we would also suggest setting the prompt for Extra Overflow Blocks to ‘2’. Other items can be set as required.
Document Parameters
The document control database needs to be referenced within the document parameters of the appropriate documents.
To update select Installationfrom the main menu followed by Application Manager and then Document Design Manager. Select the Sales Order or Purchase Order Processing application and then the document type before selecting the document number to be amended and the document mask will be displayed. Select the Parameters option from the ribbon / toolbar and then use the Action button to move to parameter screen 2.
Document Number Control File (DFD format) – enter the path/filename of the document control database as required.
This option is only applicable for document sequence numbers 1-18 and for the System Wide Reference 101-116.When using the Sales Ledger sequences (19/20) and Purchase Ledger sequences (21/22) you can still maintain a document control database but as these references can also incremented via direct transaction entry in the Sales/Purchase Ledger or the Invoicer applications (including Invoicer, Invoice Costing and Invoice Generator) then the control database is reference within the Sales/Purchase System profiles.
System Profiles
As indicated above where looking to maintain a control database for invoice numbers the facility exists to reference the database within the Sales Ledger System Profiles for sequence ‘19/20’ and the Purchase Ledger System Profiles for sequences ‘21/22’
To reference select Installationfrom the main menu and then Application Manager and System Profiles. Parameter Screen 4 allows reference of the invoice (sequence 19 for S/L and 21 for P/L) and credit (20/22) control databases.
Notes
When a document control database is in use the system will prevent re-use of a document number. This option should not be applied where documents use the order reference as the document number. If, during processing, the system defaults to a reference that has already been used it will attempt to clock on the reference five times, if the reference has still been used then the user will be warned and asked to review configuration settings.
The system reviews the document reference as the document reference is confirmed – users with larger databases may notice a pause as the document reference is confirmed.
The system will auto expand the database by 500 records as it becomes full.
Article ID: 2224
Created On: Tue, Mar 3, 2020 at 10:08 AM
Last Updated On: Mon, Jun 19, 2023 at 4:43 PM
Online URL: https://kb.datafile.co.uk/article/document-control-databases-2224.html