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

Client Requirement

1: 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

Ref No	Document Title & ID	Version	Date
	EST 254770 PA-7JJFWR Trip Filter.doc	1	29/09/2008

Document History

Version	Date	Status	Reason	Initials
1a	01/10/08	Draft	Initial version	SEW
1	01/10/08	Issue	Review & Issue	JAT

Authorised By

Dave Meir	Development Manager
Suk Sandhu	TMSCC MTS Product Manager