252217

252217 PA-7DWATD Amend Allocate Resource Screen

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

Re-design the 'Allocate Resource' screen 'TRIP_RES v1.4’ to enable all resource Driver/Tractor/Trailer to be allocated to a trip in the same screen, invoked from the ‘Trip Overview (Waterfall)’ screen pre-populated with Trip details for the Trip highlighted in the ‘Trip Overview (Waterfall)’ screen - Schedule, Trip ID, Status fields, & Driver, Tractor, Trailer fields if any one already resourced.

Pre-populate the listings with Location-based Drivers and Tractors (poplist) and Trailer numbers bound by Trailer Type selected when the Trip was planned.

Add an 'Allocated' field (Tick-Box) alongside each resource to show, as a prompt only, that a resource has already been used. For example, if one user has allocated a driver to a trip that will take him less than a full shift, if this driver is marked as 'allocated' another user would not then additionally allocate him to an 8 hour shift, but it would leave the driver available for a short run on return from his first load. Similarly Tractors and Trailers can be used several times within a schedule but users would be aware if they were already committed to another trip.

Added by PA - Check current functionality of Driver/Tractor being displayed in 'Bold' if allocated - if the function is the same as expected from an ‘Allocated’ field then delete addition of ‘Allocated’ field (tick-box).

Allow Status change to ‘TENDERED’ or ‘ACCEPTED’ only, with or without any resource added as the eventual resource is not always known until the point of departure. The status change control will not allow the trip to be moved to an ‘En-Route’ status without complete resource entry.

Solution

The ‘HEADER’ section of the ‘ALLOCATE RESOURCE’ screen will be changed to include the ‘Carrier Id’ and ‘Trailer Type’ of the ‘Trip Id’ should any have been assigned. It will be possible to change the ‘Trailer Type’ of the ‘Trip Id’ in this screen if a ‘Trailer Id’ has not been assigned: this will enable the user to find the ‘Trailer Id’ required if it is for a different ‘Trailer Type’.

The ‘ALLOCATE RESOURCE’ screen will also be changed so that the ‘Id’, ‘Surname’, ‘Forename’ (i.e. ‘Driver’ details) and ‘Tractor Id’ columns are pre-populated upon entry to the screen with valid records for the ‘Carrier Id’ of the ‘Trip Id’. However, the ‘Driver’ details displayed will also include those drivers with a job title of ‘CONTRACT’ as well as ‘DRIVER’. If the ‘Carrier Id’ does not have any drivers assigned to it then none will be displayed for selection.

Likewise, the ‘Trailer Id’ will be pre-populated with valid records for the ‘Trailer Type’ of the first stop of the ‘Trip Id’ should one have been set, however, if one has not been set then all ‘Trailer Ids’ will be displayed for selection. The ‘Trailer Id’ will be further validated for the ‘Carrier Group’ of the ‘Carrier Id’ set. If a ‘Trailer Id’ is then selected it will be assigned to the first stop of the trip; this is because only the ‘Trailer Id’ of the first stop of the trip is displayed in the screen.

The pre-population and maintenance of the resources for the trip will follow the functionality of the ‘poplist’ display type of the ‘Carrier Id’ regardless of whether it is set to be ‘freetext’; this means that the resources may only be selected from the details displayed and none may be entered as ‘freetext’.

It will be ensured that all details for the selection of resources are not marked as inactive. If the ‘Driver Id’ (i.e. ‘Surname’ and ‘Forename’), ‘Tractor Id’ and ‘Trailer Id’ are assigned to a ‘Trip Id’ for the ‘Schedule’ that is concurrent with the ‘Trip Id’ selected then the records will be highlighted in grey (i.e. ‘bold’) so that the users know that there is a restriction present; therefore, the layout of the screen will not be changed to include an ‘Allocated’ column.

The ‘Status’ of a ‘Trip Id’ may be progressed to ‘TENDERED’ from ‘PLANNED’ without ‘Driver Id’ (i.e. ‘Surname’ and ‘Forename’), ‘Tractor Id’ and ‘Trailer Id’ assigned but it must have a ‘Carrier ID’ and a ‘Start Time’ set.

The ‘Status’ of a ‘Trip Id’ will be changed so that it may be progressed to ‘ACCEPTED’ from ‘PLANNED’ or ‘TENDERED’ without ‘Driver Id’ (i.e. ‘Surname’ and ‘Forename’), ‘Tractor Id’ and ‘Trailer Id’ assigned but it must have a ‘Start Time’ set. Note that this new validation will be applied throughout MTS.

The ‘Status’ of a ‘Trip Id’ may be progressed to ‘EN-ROUTE’ from ‘PLANNED’ or ‘ACCEPTED’ but not without ‘Driver Id’ (i.e. ‘Surname’ and ‘Forename’), ‘Tractor Id’ and ‘Trailer Id’ assigned, therefore the validation for the progression to ‘ACCEPTED’ may be removed.

Scope

This change will be applied to system version 10.6 on CONTST and once approved CONPRD. This form will be applicable to all MTS environments, as the nature of this change is an addition to functionality and will reduce input time.

FUNCTIONAL DESCRIPTION

New Functionality

Below is a screenshot of what the current TRIP_RES screen looks like. All data has been cleared out:

IMAGE

This screen will be made longer so that ‘Carrier Id’ and ‘Trailer Type’ can be included in the header section. When the screen is opened from the TRIP_OVERVIEW screen, the details for the selected trip will be passed in, and these fields will be populated if they have been assigned.

The newly added ‘Trailer Type’ in the header will have a lookup button next to it that will show a list of all trailer types, when clicked. This will only be active if a Trailer ID has not already been assigned to the Trip. This allows the possibility of changing the trailer type if the trailer id they require does not appear in the ‘Trailer Id’ column for assigning.

The Driver (Id, Surname, Forename) and Tractor data will be pre-populated upon entry to this screen. The data displayed here corresponds to data associated with the Carrier Id assigned to the Trip. Drivers and Tractors can be associated with a Carrier Id within the Maintenance > Resources screen. Currently only Drivers with a Job Title of DRIVER are displayed, but this will be amended to also include Drivers with a Job Title of CONTRACT.

If the Amend Resources screen is opened and the Carrier Id of the selected trip has no Drivers or Tractors assigned to their carrier, then the lists will be empty and the user will not be able to assign a Driver or Tractor in this screen.

Likewise, the Trailer Id list will be pre-populated with those associated to the Trailer Type assigned to the first stop of the trip. If the trip has no Trailer Type set, then all Trailer Ids will be displayed in the list for selection. This list can be filtered using the new lookup button next to the Trailer Type in the header. Selecting a particular Trailer Type will amend the list so that it only displays Trailers associated to the selected Trailer Type. If the user assigns a Trailer to the trip without making use of the Trailer Type lookup button then, when a Save is invoked, the Trailer Type in the header will be updated according to the selected Trailer. The list of Trailers displayed for selection is also further validated according to the Carrier Group of the Carrier Id for the trip. The select Trailer Id will be assigned to the first stop of the trip.

Also note that for population of data in all lists in the Amend Resources screen, only data that is active will be displayed. The Maintenance > Resources screen can be referenced to see how all data is associated.

Resources can only be added through selection from the pop-lists. It will not be possible to assign resources in this screen using free-text.

Below is an example of the layout of what the new screen should look like:

IMAGE

If the Driver (Id, Surname, Forename, Tractor and Trailer are assigned to a Trip for the Schedule that is concurrent with the schedule of the trip selected, then these records will be highlighted in grey with bold text, so that it is evident to the user’s, those resources that have a restriction.

Below is what the screen should look like: Note that the lookup button next to the Trailer Type is inactive as the trip already has a Trailer assigned.

IMAGE

PLEASE NOTE – all Trip Status changes in screen TRIP_RES will done in accordance with the conditions set out in RIO PA-7DJLS6 (252344 Set Parameter for Trip Status change).

Code Additions and Amendments

The addition of the new ‘Trailer Type’ lookup requires a new button to be added to the TRIP_RES screen. When this is clicked it will call a newly created LOV named TRAILER_TYPE. This LOV will contain the following query:

SELECT rt.trailer_type, rt.description FROM res_trailer_type rt WHERE rt.inactive != ‘Y’ ORDER BY rt.trailer_type;

This will open a lookup table of all active trailer types.

When a ‘Trailer Type’ is selected this will list all associated active ‘Trailer Id’s’ in the list for selection. The following query will be used on the Trailer Data Block to filter the list of Trailer Id’s shown according to the Trailer Type selected in the header:

SELECT rt.trailer_id FROM res_trailer rt WHERE (NVL(rt.inactive. ‘N’) <> ‘Y’) AND rt.trailer_type = :SCH_TRIP_STOP.trailer_type AND EXISTS ( SELECT 1 FROM res_trailer_carrier_group g, WHERE g.trailer_id = rt.trailer_id AND (NVL(g.inactive. ‘N’) <> ‘Y’) AND g.name = (SELECT rc.group_name FROM res_carrier rc WHERE rc.carrier_id = :SCH_TRIP.carrier_id) ) ORDER BY rt.trailer_id

The list of Drivers currently displayed for selection in TRIP_RES, is those associated with the current Carrier and with a job title of DRIVER. This will be updated to also include Drivers with a job title of CONTRACT, and also will not include those that have been set as inactive. The sql select statement required to display this will be:

SELECT p.id, p.surname, p.forename FROM res_person p WHERE UPPER( p.job_title ) = 'DRIVER' OR UPPER( p.job_title ) = 'CONTRACT' AND (NVL(p.inactive. ‘N’) <> ‘Y’) AND EXISTS (

SELECT 1
FROM   res_person_carrier pc
WHERE  p.id = pc.id
AND    pc.carrier_id = :SCH_TRIP.carrier_id

) ORDER BY p.surname, p.forename

Trigger Updates

The following triggers will be applied to Data Block Trailer:

PRE-QUERY - will create a string of trailer ids that are currently in use between the currently selected trip start time and trip end time POST-QUERY - will highlight in grey and bold those trailer ids that appear in the string created during pre-query.

This will then bring to the user’s attention those trailer ids that are in use between the selected trip start and end time and therefore have restrictions on them.

The code for the PRE-QUERY trigger will take the following form:

DECLARE

       CURSOR c_alloc_trailers IS
       SELECT trailer_id
       FROM   sch_trip 
       WHERE trailer_id IS NOT NULL
       AND    start_time BETWEEN :sch_trip.start_time AND :sch_trip.finish_time
       OR     ( start_time + ( elapsed_time / 1440 ) ) 
       BETWEEN :sch_trip.start_time AND :sch_trip.finish_time;

BEGIN

ctrl.tractor_str := NULL;

FOR cr_alloc_tractors IN c_alloc_tractors LOOP :ctrl.tractor_str := :ctrl.tractor_str || '/' || cr_alloc_tractors.tractor_id; END LOOP;

ctrl.tractor_str := :ctrl.tractor_str || '/';

END;

The code for the POST-QUERY trigger will take the following form:

DECLARE

BEGIN IF INSTR( :ctrl.tractor_str, :tractor.tractor_id || '/' ) > 0 THEN

SET_ITEM_INSTANCE_PROPERTY( 'TRAILER.TRAILER_ID', CURRENT_RECORD, VISUAL_ATTRIBUTE, 'ALLOCATED' );

END IF; END;

Where VISUAL_ATTRIBUTE of type ALLOCATED it set to display the current record in bold with a grey background as described below:

IMAGE

References

IMAGE

Glossary

IMAGE

Document History

IMAGE

Authorised By

Dave Meir	Development Manager
Suk Sandhu	TMSCC MTS Product Manager