In some systems sales and purchase orders can remain active for a long time – with changes to increase quantity, change price / discount, change stock codes and deleting lines occurring throughout.Where Sales Order Processing is configured for Service Billing – Washroom Servicing or Rental Agreements for example – the order can remain active until the agreement is ended.This makes reporting and analysis of the order book in relation to time more difficult.
This new accessory, available for both Sales and Purchase Order Processing, allows for the generation of a log file of order changes allowing you to better analyse your order book in relation to time.
For example, the Excel screenshot below uses the log file against Washroom Service Billing configured Sales Order Processing system and the Datafile Excel add-in tools to monitor changes in the Contract Pool.
|
CHG IN CONTRACT POOL |
Column Labels |
||
|
2017 |
Grand Total |
||
|
Row Labels |
Jul |
Aug |
|
|
01. NEW |
1,320.00 |
492.00 |
1,812.00 |
|
02. ADDITION |
840.00 |
60.00 |
900.00 |
|
03. ADDITIONAL ITEMS |
432.00 |
180.00 |
612.00 |
|
04. ADDITIONAL VISITS |
360.00 |
360.00 |
|
|
05. PRICE INCREASE |
324.00 |
324.00 |
|
|
11. CANCELLED |
0.00 |
0.00 |
|
|
12. CANCELLED ITEM |
-1,224.00 |
-1,224.00 |
|
|
13. REDUCED QTY |
-624.00 |
-624.00 |
|
|
14. REDUCED VISITS |
-156.00 |
-156.00 |
|
|
15. REDUCED PRICE |
-144.00 |
-144.00 |
|
|
Grand Total |
1,128.00 |
732.00 |
1,860.00 |
Premier users could equally include the log file as the Sales/Purchase Order History database on the Ledger Enquiry allowing you to enquire on the log changes.
Installation
Database Changes
The Sales and Purchase Order Logging will need their 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-SPLG.DFD where CCC is the company id, SP is for Sales Order Processing (use PP for Purchase Order Processing) and LG identifies as the log file.
Title – enter the title for the database.
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 add/amend orders 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. The logging structure includes some fixed elements (1-7 below) but in most aspect depends on the items you wish to monitor the changes in values for but a typical structure could be
No Item Name Type Description
1 ORDER-REF X (16) Holds the Order Reference log generated for.
2 ENTRY-NO N Holds the Entry No log generated for
3 LOG-ADD-BY X (6) Holds the User Id who created the log entry
4 LOG-ADD-ON E Holds the date the log entry created
5 LOG-ADD-AT N Holds the time the log entry created
6 ACTION X (4) Holds the program parameter the log entry created under
7 ADD/CHG/DEL X (1) Holds A/C/D depending on whether the log entry created under a add, change or delete action.
11 STATUS-WAS X (1)
12 STOCK-WAS X (30)
13 QTY-WAS K / L / M
14 PRICE-WAS K / L / M
15 DISC-WAS K / L
16 ORDER-WAS K / L
21 STATUS-NOW X (1)
22 STOCK-NOW X (30)
23 QTY-NOW K / L / M
24 PRICE-NOW K / L / M
25 DISC-NOW K / L
26 ORDER-NOW K / L
33 QTY-CHG * (+D23 -D13)
34 PRICE-CHG * (+D24 -D14)
35 DISC-CHG * (+D25 -D15)
36 ORDER-CHG * (+D26 -D16)
Once the mandatory data items have been added press the <ESC> key and choose ‘Data Items are Complete’
Prime Index Data name – leave blank
Secondary Index 1/2 – you would typically select the Order Reference as the initial secondary index.
Access by Record Number – respond Y
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.
Reference the ‘Add New Records’ user-id, date and time items, other items can be set as required.
Application User Facilities
Once the database has been created you need to configure Sales / Purchase Order Processing to maintain the log database. Select Installationfrom the main menu followed by Application Manager and Application User Facilities. Choose the required Order Processing application and then select Enter/Amend Orders and Advanced Input Facilities and Change Logging.
Log Database Pathname – enter the path/filename for the log database.
Check for Change in Detail Items – nominate the detail data items to generate a log entry on change of value of – these would typically be the items nominated above but could also, for example, include cost values or other key items you wish to monitor.
For Currency systems you would typically nominate the currency equivalents of price / detail value even if reporting / analysis is primarily on the base currency – if revaluing orders though you should include the base price item to detect changes arising due to revaluation.
Log Detail – Add/Change/Delete– typically set all three items.
The next prompts ask you to reference the ‘fixed’ structure items for the log file record.
Log: Order Ref
Log: Entry No
Log: User Id
Log: Date
Log: Time
Log: Action
Log: Det-Action
Reference to the appropriate data items.
The second screen asks you to define up to fifteen copy items for the Old or ‘Was’ items, fifteen copy items for the new or ‘now’ items and also allows up to fifteen copy items from the order header for extra analysis (this could include the order header status, customer account, etc.)
Article ID: 2228
Created On: Tue, Mar 3, 2020 at 12:14 PM
Last Updated On: Mon, Jun 19, 2023 at 4:43 PM
Online URL: https://kb.datafile.co.uk/article/sales-and-purchase-order-change-logging-2228.html