267850
267850 - KL-7UEBAR/ Location Screen Validation
Copyright OBS Logistics © 2010
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
FUNCTIONAL OVERVIEW
Client Requirement
Within MTS there is a locations screen – this needs tighter control to stop users entering data in different formats and duplicating records as they can’t find the correct code.
- Take access away from general users. Limit to 4-6 people throughout consumer
- Add further controls on system to ensure data entering in correct format
The id is made up of first characters of Company name and Town of Delivery ie ASDALUTT The id name is Town_Company Name_Postcode Currently there are no limitations to this. In the main body of the screen, users need to type in address, town, postcode, country.
Requirement for Change The Name to be added into this section The Post Code to have control to ensure that only valid postcodes are entered and in the correct format. The name of the ID, then can be automatically populated from the data already received. (Note this still relies on users putting the Town in the town field etc) By putting the correct postcode the Longitude and Latitude should automatically be populated – for Country GB, do not allow user Long Lat update
Requirement to Tidy the Current data Any duplicates can be checked, once have customer account numbers linked to the correct ID. the duplicates be made inactive. Create a list of Delivery points which have not had deliveries in past two years, these need to be made inactive also.
Requirement for Change A parameter on system should change any delivery locations that have not been visited in the last xxx days to automatically go to inactive.
Solution
Locations Form Changes
In order to restrict access to Location Maintenance all users who do require access will have to add the ADM_GEO_MAINT function to their user group.
A new system parameter ‘ADDITIONAL_LOC_CHECKS’ will be added to the system. Setting this to ‘Y’ will cause the location maintenance module to provide stricter validation when entering a new record.
Upon attempting to save a newly entered location record, the program will check if the location id consists of the company name followed by the town entered. If this is not the case, an error message will be given and the user will be expected to alter either the location id or town. (NB Location ID is only 12 chars, should this validation be along the lines of first four characters should equal the first 4 characters of an existing customer code, the next 4 characters should equal the first 4 characters of the entered location, final 4 reserved for future use?)
If the validation is passed, the new location will be saved and a request will be sent to the external mapping system in order to obtain the longitude and latitude co-ordinates. As it is possible for this request to take some time, the status of the new location will initially be set to inactive.
In order to make the new record active, the user will be expected to re-query the data. If the longitude and latitude co-ordinates have been populated, this indicates that the post code the user initially entered must have been valid and the user can therefore update the record to ‘Active’.
If the coordinates have not been set and the country code is GB, then it will not be possible for the user to make the new location record active. In this situation, the user will be expected to re-enter the postcode, save the record and then re-query the data. The cycle will be repeated until valid longitude and latitude values have been obtained. Note that upon attempting to save a new customer record, the user will be given a warning if a record already exists for the post code entered. They will not, however, be prevented from saving the record.
To validate initial entry of postcodes we could apply the following rule based on the country code entered against the location. This would need a format mask code setting against the Country in the Geography form and then be reliant on the user entering the country code correctly in the Locations form.
Data Cleansing
A query will be written to analyze the existing data and determine if duplicate records exist for the same post code. The results of the query will be sent to DHL in the form of a csv spreadsheet. This will list each customer and the last order number raised against it if there has been one. Note that as it is theoretically possible to have 2 delivery points within the same post code, no action will taken by OBS. It will be up to the users to determine which of the duplicates should be made inactive.
Automatic Inactivation of Locations
A new system parameter ‘MAX_LOCATION_INACTIVE_DAYS’ will be created. This will allow the user to specify the number of days a location is allowed to remain active on the system for without being used. A new process will be created to check the date each location was created and compare it to the current date. If the number of days is higher than the MAX_LOCATION_INACTIVE_DAYS then further checks will be made. The newest order against each location will be retrieved. The created date of this order will be compared with the current date. If the number of days is higher the MAX_LOCATION_INACTIVE_DAYS or no order was found then the location will be set to inactive.
Scope
This change will be applied to system version 10.6.
SET-UP
Data
New Customer group records may need to be entered to allow location codes to be entered successfully.
FUNCTIONAL DESCRIPTION
Locations Form - Entry Mode
A new system function ‘APPLY_ADDITIONAL_LOC_CHECKS’ will be added to the system. If this is added to a group assigned to the user, this will cause the location maintenance module to provide stricter validation when entering a new record.
Whenever a new location is entered, a check will be made to verify if there are any records already on the system that are similar to the value entered. Records will be considered similar if there are less than 3 differences between them (more technically 3 steps when using the utl_match.edit_distance function). If records are found, they will be displayed within a new canvas titled ‘Duplicate Locations’ that will be displayed for information purposes.
So if the user attempted to enter a new location with the id ‘ASDADUND’ for example, they would presented with a canvas containing the following list:
| LOCATION ID | LOCATION_NAME |
|---|---|
| ARRADUND | Dundonal_Arran Aromatics_KA2 9BE |
| ASWADUNS | Dunstable_A S Watson_LU5 4RZ |
| HADADUND | DUNDEE_H ADAMSON &N SONS_DD6 8RD |
| CEDADUND | Dundee_Cedars_DD5 |
| ALLADUND | Dundee_Allan Robertson _Dd5 2ah |
| ASDADONC | Doncaste_Asda Stores Lt._DN11 9H |
| ASDADUMB | Dumbarto_Asda Walmart _G82 1RD |
| ASDADUND | Dundee_Asda Walmart_DD3 0FS |
| ASDADUNF | Dunferml_Asda _KY11 4LP |
| ASDADUNS | Dunstabl_Asda Wal*Mart ._LU5 4EU |
| ASDAHUDD | Huddersfield_Asda |
| ASDAHUNT | Huntly_Asda_AB54 8SX |
| ASDAKEND | KENDAL_ASDA STORES_LA9 7JA |
| ASDA DUNDE | DUNDEE_ASDA WALMART - 4927_DD4 8JS |
The canvas will contain ‘OK’ and ‘Cancel’ buttons. If the user clicks ‘OK’, they will be allowed to continue to enter the new location record. If they click ‘Cancel’, the location id they have entered will be blanked.
Further checks will be made to ensure that when the user enters the ‘Town’ field, the first four characters of the town entered are contained somewhere within the location id entered. If this is not the case, an error message will be given and the user will be expected to alter either the location id or town.
Upon entering a postcode, if the country code is set as ‘GB’, then the post code will be validated using standard format checking adhering to British Standard 7666. The check will need to test the value entered, e.g. v_postcode within a function such as REGEXP_LIKE(v_postcode,'(GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|[ABEHMNPRV-Y]))|[0-9][A-HJKS-UW]) [0-9][ABD-HJLNP-UW-Z]{2})'). An error message will be given if the post code is invalid.
If it is valid, further checks will be made to determine if there are already records on the system with the same post code. If there are, a new canvas will be displayed listing similar records. The canvas will contain ‘OK’ and ‘Cancel’ buttons. If the user clicks ‘OK’, they will be allowed to continue to enter the postcode. If they click ‘Cancel’, the postcode they have entered will be blanked. Post codes should only be considered similar if they are one step away from the value entered using the utl_match.edit_distance. For example, L1 2HH and L1 2HK would be considered similar but L1 2HH and L1 2KK would not.
If the new location record is saved successfully, it will initially be set as ‘Inactive’. A request is then automatically sent to the external mapping system. If the country code is ‘GB’, the user will not be able to update the record to ‘Active’ unless valid longitude and latitude coordinates are obtained. If valid coordinates cannot be obtained, the user may need to re-enter the postcode, save the record and re-query the data. The cycle will be repeated until valid longitude and latitude values have been obtained.
Note that within the current system only users that have the ADM_GEO_MAINT function assigned to their user group have access to the Locations form. If it is required that some users do not need to access the form, this function should be removed for these users.
Locations Form - Query Mode
A new radio group will be added to the form below the existing ‘Location Id’ field. The radio group will relate to the active status of the locations the List of Values will select. The three options will be ‘All’, ‘Active’ and ‘Inactive’. The DEFAULT VALUE WILL BE ‘All’. Clicking the ‘Active’/‘Inactive’ radio buttons and then performing a lookup on location codes will cause only ‘Active’/’Inactive’ records to be displayed in the lookup.
Once a record has been selected, the ‘Active’ or ‘Inactive’ radio value will indicate the status of the record retrieved. Therefore changing the value of the radio button after a record has been found will cause the location to move to a different status.
Duplicate Post Codes Report
A new report will be created that will list location records which have the same postcode. The report will be called ‘Duplicate Postcodes’ and will be setup in the System Configuration screen as shown below (the printer type and Orientation settings are optional)
When the user runs the report, they will be prompted to enter a range of location codes. If they leave these values blank, the report will list duplicate record for all locations on the system. They will also be prompted whether they want to ‘Ignore Inactive Locations’. Choosing ‘Yes’ for this option will mean that Inactive duplicate locations will not be listed.
The output of the report will be in csv format. The report will list the following fields:
| LABEL | DATA |
|---|---|
| Location ID | LOATION_ID |
| Location Name | LOCATION_NAME |
| Post Code | POST_CODE |
| Inactive Flag | INACTIVE |
| Created Date | CREATED_DATE |
| Created By | CREATED_BY |
| Location Address 1 | ADDRESS_LINE1 |
| Location Address 2 | ADDRESS_LINE2 |
| Location Address 3 | ADDRESS_LINE3 |
| Town | TOWN |
| County | COUNTY |
Note that as it is theoretically possible to have 2 delivery points within the same geographical post code since it can cover an area of several square miles. For this reason, no action will taken by OBS to attempt update duplicate records. It will be up to the users to use this report to determine which of the duplicate locations should be made inactive.
Automatic Inactivation Of Locations
A new procedure will be created within the housekeeping (HKP) package to set location codes to ‘Inactive’ if they are no longer required. The package will check for the existence of a new system parameter called ‘MAX_LOCATION_INACTIVE_DAYS’. If this exists and is set to a figure greater than 0, then the process will search for location records that have not been used for this number of days or more. For each record on the location table, the newest order will be retrieved from the SCH_ORD table by checking the CREATED_DATE field. If this date is earlier than the current date minus the ‘MAX_LOCATION_INACTIVE_DAYS’ parameter, then the location in question will be set to inactive. The process will be submitted by OBS as a database job and will be set to run every 24 hours at a quiet time of day.
REFERENCES
| Rio – Work Request.mht | |||
| EST-267850 KL-7UEBAR Location Screen validation v1.0.doc |
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
