254770
254770-PA-7JJFWR Trip Filter / Depot Alpha Sort
Copyright OBS Logistics © 2008
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
MTS Modules/Trip Management/Trip Planning & Trip Manipulation/Trip Filter - DEPOT Filter - Sort list Alphabetically. Sort by Location ID (1st column)Added by Suk Sandhu 29/07/08 It seems that the depot list should be sorted alphabetically by location code and not location name. Also any depots which have numerical values at the beginning of their depot code should appear at the top of the list followed in alphabetical order. This should also be applied to Collect From and Deliver to Fields also.
Solution
Amend the Record Group which populates the list of Values for Collection From and Delivery to locations. Currently the record group is ordered by location name, amend this to location Id.
The order by clause in Oracle will always order alphabetically ahead of numerically for alphanumeric values. The request is for the numerical location ids to be displayed before the alphabetical location ids.
To allow this, we will create a new column called sort which will contain the location id, if the value is numeric or 0 if the value is alphabetical. We will use this field to complete a primary sort which will place all numerics at the top and then perform a secondary sort on location_id, which will then sort all alphabetical values under the numerics. The sort column will never be visible to the user.
The development requires a new field called sort, automated population of the sort field and a change to the ALL_LOCATIONS record group.
Scope
This change will be applied to system version 10.5 on CONTST and once approved CONPRD.
Data
A non database item called Sort will be added to the Trip detail data block. This will be used to hold the value 0 or 1 depending on the value of Location id.
If Location id is numeric, then sort will be set to 1, if location id is alphanumeric then sort will be set to 0.
Functional Description
The owning depot field is attached to a command button which launches a List of Values. Currently list of values displays the location name and location id, in order of the location name. This will be amended to display the list in order of location id.
In addition, the user has requested that all numeric location ids are displayed first in the list this will be accomplished using a Non Database item called ‘SORT’. This item will be populated with a numeric value depending on the location id.
The Record group will then be ordered by 2 values, the sort column followed by the location id. The first ‘order by’ will separate the numeric locations from the alphanumeric locations and the second ‘order by’ will ensure that the numeric locations are in order, followed by alphanumeric locations in order.
When the form is opened the SORT field will be automatically populated from a procedure SET_ORDER. The SET_ORDER procedure will use a cursor to loop through the location id in GEO_LOCATION and set the value of the SORT field using a case statement.
The case statement will list the different scenarios for location_id and how the sort field is populated for each one.
References
| EST 254770 | PA-7JJFWR Trip Filter.doc |
Document History
| Initial version | ||||
| Review & Issue |
Authorised By
| Dave Meir | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
