294570
DHL C-TMS
Modify BG Waste Debrief Report
FUNCTIONAL SPECIFICATION - 10.7
21/12/11 - 1.0
Reference: 294570 PM-8P5LYY
FUNCTIONAL OVERVIEW
Client Requirement
Modify the selection criteria to look at the date the order is completed (we need to discuss whether that is the schedule name for the trip, actual arrival & depart time on the stop or the planned arrival & departure time of the trip stop).
Only show orders that have been debriefed with actual despatch value (zero or greater than zero).
Do not show orders that do not have an actual despatch value. (So the selection is no longer based on the en-route time & date for trips at completed & confirmed status.)
Add the following data to the report:
- Job completed date as the first field (same as first point above)
- 1st line of the address (before postcode)
- Collection location contact phone number (before 1st line of address)
- Collection location contact name (before location contact phone number)
- Special instructions from order
- Order comments from order
- Tractor ID assigned to the trip
- To Location name from the order
Solution
Currently the selection criteria for this data extract is a From and To Date range, Customer and Cost Centre.
The date range currently matches on the trip start time (which equates to the SU start time of the trip) and selects data where the trip status is either COMPLETED or CONFIRMED.
The selection criteria will be retained but the logic in the extract will be changed to use the date range differently. Trips and orders on the trips will be selected where the trip stop actual date and time that the order is actually collected falls within the date range. In other words, the actual execution date of the collection. This means referencing trip stops and selecting those where actual arrival falls into the date range specified.
All orders will be selected on the above criteria regardless of whether the actual despatch value is null, zero or greater than zero.
Currently the export outputs the following columns of data:
Outbase Code, Outbase Name, Journey Reference, Driver Code, Driver Name, Second Man Code, Second Man Name, Account Code, Account Name, Location Code, Location Postcode, Order Reference, Ethos Code, Order Due Date, Order is Collection, Failure Reason Code, Failure Reason Name, At Fault Provider code, At Fault Provider Name, Engineer.
Additional columns will be added to the report as follows;
- Planned arrival date and time at the trip stop
- Actual date and time of arrival at the trip stop
- First line of the location address
- Collection location contact phone number
- Collection location contact name
- Special instructions from the order
- Order comments from the order
- Tractor ID assigned to the trip
- To Location name from the order
- Customer ID from the order
- Customer Name from the order
The revised layout of columns will be presented in the following sequence:
Actual date and time of arrival at the trip stop, Planned arrival date and time at the trip stop, Order Due Date, Outbase Code, Outbase Name, Journey Reference, Tractor ID assigned to the trip, Driver Code, Driver Name, Second Man Code, Second Man Name, Account Code, Account Name, Customer ID, Customer Name, Order Reference, External Reference, Collection location contact name, Collection location contact phone number, First line of the location address, Location Postcode, Location Code, To Location name from the order, Order is Collection, Engineer, Failure Reason Code, Failure Reason Name, At Fault Provider code, At Fault Provider Name, Special instructions from the order, Order comments from the order.
The selection logic will be modified to ignore any reason codes with value ‘CD’. The trip and orders should still be reported but no ‘CD’ reason codes will be output. This means if all the reason codes are ‘CD’, then no reason code will show against the order. If some reason codes are ‘CD’ this will ignored and the first value other than ‘CD’ will be output against the order. This means that where there are ‘CD’ codes and ‘BGW%’ codes, only the ‘BGW%’ codes would be visible on the report.
Scope
This change will be applied to system version 10.7.0.
SET-UP
Implementation Advice
The new version of the export may be released without any setup changes required.
FUNCTIONAL DESCRIPTION
Selection Criteria
The existing selection criteria will be retained for the ‘British Gas Waste Debrief’ export but the logic in the extract will be changed to use the date range differently:
British Gas Waste Debrief Report
The ‘DP_CSV3.WRITE_BGW_DEBRIEF’ function will be changed to use the ‘From Date’ and ‘To Date’ selection criteria so that they assess the trip stops and not the start time of the trip itself.
The date range currently matches on the trip start time (which equates to the ‘SU’ start time of the trip) and selects data where the trip status is either ‘COMPLETED’ or ‘CONFIRMED’.
Trips and the transport orders on the trip will be selected where the actual date and time of the trip stop (i.e. when the transport order is actually collected) falls within the date range (i.e. the actual execution date of the collection).
This means referencing trip stops and selecting those where the actual arrival date and time falls into the date range specified.
All orders will be selected on the above criteria regardless of whether the actual despatch value is null, zero or greater than zero.
This means where the actual despatch value is set positively (i.e. it may be zero) and is not null.
Currently the export includes the following items:
- Outbase Code
- Outbase Name
- Journey Reference
- Driver Code
- Driver Name
- Second Man Code
- Second Man Name
- Account Code
- Account Name
- Location Code
- Location Postcode
- Order Reference
- Ethos Code
- Order Due Date
- Order is Collection
- Failure Reason Code
- Failure Reason Name
- At Fault Provider Code
- At Fault Provider Name
- Engineer
Additional items will be added to the export as follows:
- Planned Arrival Date and Time at the Trip Stop
- Actual Arrival Date and Time at the Trip Stop
- First Line of the Location Address
- Collection Location Contact Phone Number
- Collection Location Contact Name
- Special Instructions from the Transport Order
- Order Comments from the Transport Order
- Tractor ID assigned to the Trip
- To Location Name of the Transport Order
- Customer ID from the Transport Order
- Customer Name from the Transport Order
The revised sequence of the items will be presented as follows:
- Actual Arrival (Date and Time at the Trip Stop)
- Planned Arrival (Date and Time at the Trip Stop)
- Order Due Date
- Outbase Code
- Outbase Name
- Journey Reference
- Tractor ID (assigned to the Trip)
- Driver Code
- Driver Name
- Second Man Code
- Second Man Name
- Account Code
- Account Name
- Customer ID (from the Transport Order)
- Customer Name (from the Transport Order)
- Order Reference
- External Reference
- Collection Location Contact Name
- Collection Location Contact Phone Number
- Collection Location First Line (of the Location Address)
- Location Code
- Location Postcode
- To Location Name (from the Transport Order)
- Order is Collection
- Engineer
- Failure Reason Code
- Failure Reason Name
- At Fault Provider Code
- At Fault Provider Name
- Special Instructions (from the Transport Order)
- Order Comments (from the Transport Order)
N.B. Items in italics will be added for this development and the text in the brackets will not be included in the name of the item in the export.
The ‘Ethos Reference’ will be renamed ‘External Reference’ but it will retain its position after the ‘Order Reference’ in the export file.
The selection logic will be modified to ignore any reason codes with value ‘CD’.
The trip and transport orders should still be reported but no ‘CD’ reason codes will be included in the output.
This means that if all of the reason codes are ‘CD’, then a reason code will not show against the transport order.
If some reason codes are ‘CD’ they will be ignored and the first value other than ‘CD’ will be included in the output against the transport order.
This means that where there are ‘CD’ codes and ‘BGW%’ codes, only the ‘BGW%’ codes would be visible on the report.
The added items will be obtained from the database records as follows:
The planned and actual arrival dates and times will be obtained for the trip stop with a loading activity for the transport order.
The location contact will be obtained from the first contact, regardless of job title, stored against the location in the ‘Contacts’ tab page of the ‘Locations’ screen, for example:
The contact name will be the forename plus the surname.
The ‘To Location Name’ will be the name of the location obtained from the ‘To Location’ of the transport order.
REFERENCES
| EST-294570 PM-8P5LYY Modify BG Waste Debrief Report v1.0.doc |
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
