283810
DHL C-TMS
Create 2 new reports for UK Healthcare
FUNCTIONAL SPECIFICATION - 10.6
20/01/2011 - 1.0
Reference: 283810 NW-8BGNGD
Client Requirement
Change Request Summary:
Create two new reports for UK Healthcare.Kate Vallentine/Milton Keynes/UK/Exel
Change Request Details:
Create two new reports / exports as per attached documents. Details are included in the notes section and as comments against cells. The two KPI Reports should contain a front page summary and then a detailed export.
Benefits identified as a result of the change:
Business requirement to allow reporting from the TMS.
Solution
Create 2 new extracts in CSV format. Each extract will be based on the same detail information , with customers included or excluded from the extract. The Campus Extract will exclude the following clients:
COVIDIEN, SMITHS & BACKHAUL, while the OPS extract will be only for COVIDIEN, SMITHS & BACKHAUL. Additional customers will be controlled via parameter/look up table, to be defined in specification.
Both extracts will have a different front summary sheet.
The extracts will be created using a PL/SQL package and will be added as 2 separate procedures to the existing KPI package. Data required for calculations and data returned from calculations will be stored in temporary variables and will not be written back to the database.
New records will be added to the rep-report and rep_report_param tables to allow the extracts to be run from the export screen within C-TMS.
Detail Page
The detail data will be presented at Order Line level and will be ordered by trip and departure time.
| Schedule date | Sch_ord |
| Delivery Date | Sch_trip_stop |
| Trip Number | Sch_trip_stop |
| Trip Status | Sch_trip |
| Delivery Depot | Sch_trip (owning depot) |
| Carrier | Sch_trip |
| Customer ID | Sch_ord |
| Customer Name | Sch_ord |
| Customer Ref | Sch_ord |
| Delivery Type | Sch_ord |
| Driver | Sch_trip |
| Tractor | Sch_trip |
| Trailer Type | Sch_trip_stop/ sch_trip |
| Trailer ID | Sch_trip_stop/ sch_trip |
| Trip Stop Location Name | Sch_trip_stop |
| Postcode | Geo_location |
| Location Type | Geo_location |
| Location Trailer Restrictions | Res_resource |
| Activity | Sch_haulage_activity |
| Planned Arr | Sch_trip_stop |
| Actual Arr | Sch_trip_stop |
| Planned Dep | Sch_trip_stop |
| Actual Dep | Sch_trip_stop |
| Planned Drop Time | Calc |
| Actual Drop Time | Calc |
| Trip Comments | Sch_trip |
| Product Type | Sch_order_line |
| Temp Combo | Sch_ord |
| DU type | Sch_order_line |
| Planned DU Qty | Calc by du type |
| Actual Desp DU qty | Calc by du type |
| Actual Del DU qty | Calc by du type |
| DU Var | Calc |
| DU qty returned | Calc by du type |
| Vehicle fill %RPE | Calc |
| Planned Weight | Calc by du type |
| Actual Weight | Calc by du type |
| Weight Variance | Calc by du type |
| Vehicle Fill% Weight | Calc |
| Planned Lifts | Calc by du type |
| Actual Lifts | Calc by du type |
| Lifts Variance | Calc by du type |
| Vehicle Fill %Lifts | Calc |
| Planned Km | Sch_trip |
| Actual Km | Sch_trip |
| Variance Km | Calc |
| Planned hrs | Sch_trip_stop |
| Actual hrs | Sch_trip_stop |
| Variance hrs | Calc |
| Vehicle / Driver%Utilisation | Calc |
| POD | Sch_ord |
| Non Conformance code | Sch_ord_non_conform |
| Non conformance desc | Sch_ord_non_conform |
| Non conformance reason | Sch_ord_non_conform |
| Order Comments | Sch_ord |
| Order Special Instructions | Sch_ord |
Both extracts will be based on the same parameters:
- COST CENTRE
- CLIENT
- CARRIER
- DELIVERY DEPOT
- DATE RANGE values and types
The date range type will be a static list with the values ‘SCHED_NAME’,’ORDER CREATED’.’ORDER DELIVERED’
The value of the this parameter will determine how the dates are applied to the where clause
Ops Extract Summary Sheet
The following data will be analysed by Collection (inbound) and Delivered (outbound).
Orders
Despatch Units
Weight
Lifts ( a new field as part of another RIO)
Vehicle Utilisation by carrier
Vehicle utilisation will be analysed by RPE, Lifts and Time.
A further block will analyse Trip data, splitting the trips into Own Fleet, Internal Sub Contract and External Sub Contract and a count of each status.
OWN FLEET – The owning depot is the hub location of the carrier
INTERNAL SUB CONTRACT – The owning depot is not the hub location of the carrier. The carrier is of type FLEET and has been assigned a hub location.
EXTERNAL SUB LOCATION – The carrier does not have a hub location and is set up as a HAULIER.
The trip data will be further analysed by the carrier types to show total KM, total hours, average shift, average trips, and trips no completed. If the user has selected a specific Carrier all the trip information will be analysed by the carrier, if the user has selected ALL carriers, the data will be of summary of ALL and not by individual carriers.
If the user has selected a single Carrier, the trips analysis will be limited to trips which have been assigned this carrier, so OWN FLEET, INTERNAL SUB and EXTERNAL SUB will not all be relevant.
OTD (on time deliveries) will be reported as a %, the calculation of this field will differ depending on the COST_CENTRE of the order (HUK or BAX) (On time for HUK = delivered by latest delivery time on order.
On time for BAX = delivered no more than an hour earlier or three hours later than the planned delivery time.)
Parameters will be created for BAX_OTD and HUK_OTD , to store the calculation.
Finally there will be an analysis of Planned v’s actual listed by non conformance reasons, this will be based on analysis of the REASON CODE in the SCH_ORD_NON_CONFORM table.
Campus Extract Summary Sheet
The extract will analyse Orders, DUs and Lifts by the three Carrier categories (own fleet, internal sub and external sub) Again, this analysis will depend on the Carrier parameter that the user has selected. The Carrier categories will be split further into inbound and outbound trips.
Like the OPS summary, this extract will also be summarised by status and display the OTD % and an analysis of non conformance.
The calculations required for the summary sheets will be described in more detail in the functional spec.
Scope
This change will be applied to system version 10.6.
Set-up
Pre-requisites
Menu-Structure
Data
Two new records will be added to REP_REPORT, one for each extract.
OPS Extract
Campus Extract
Records for each extract will be added to REP_REPORT_PARAM.
The records will be for parameters
COST CENTRE
CLIENT
CARRIER
DELIVERY DEPOT – Trips will be included if the drop off or pick up from this depot
DATE RANGE TYPE – Either Sched Name, Order Created, Order Delivered
DATE RANGE – Date from and Date too.
Functional Description
Two new extracts will be written to be printed in CSV format. The extracts will be referred to as OPS and CAMPUS in this specification. Both extracts will contain the same detail section but will have a different summary.
The Campus extract will run for all clients except:-
COVIDEN
SMITHS & BACKHAUL
The OPS extract will run for clients :-
COVIDEN
SMITHS & BACKHAUL
To create these new extracts two procedures will be added to the KPI package.
Parameters
Both extracts will use the same parameters.
COST CENTRE
CLIENT
CARRIER
DELIVERY DEPOT – Trips will be included if the drop off or pick up from this depot
DATE RANGE TYPE – Either Sched Name, Order Created, Order Delivered
DATE RANGE – Date from and Date too.
The date range type will determine which date the date range is applied to.
Sched Name - orders with a sched name (Sch_Ord.Sched_Name) within the range will be included.
Order Created - orders created (Sch_Ord.Date_Created) within the range will be included
Order Delivered – Orders delivered within the date range will be included, this will be based on the actual arrival time at the stop for the to location of the order.
Detail Page
Both extracts will print the same columns for the detail section. The table below details the database column that will be displayed or in the case of a calculated column will show the calculation to be used.
| Schedule date | Sch_ord.Sched_name |
| Delivery Date | Sch_trip_stop.Arrive |
| Trip Number | Sch_trip_stop.Trip_ID |
| Trip Status | Sch_trip.Trip_Status |
| Delivery Depot | Sch_trip.owning depot |
| Carrier | Sch_trip.Carrier_ID |
| Customer ID | Sch_ord.Customer |
| Customer Name | Org_Customer.Customer_name |
| Customer Ref | Sch_ord.External_Ref |
| Delivery Type | Sch_ord.Delivery_Type_Id |
| Driver | Res_Person.Forename\Surname |
| Tractor | Sch_trip.Tractor_ID |
| Trailer Type | Sch_trip_stop.Trailer_Type |
| Trailer ID | Sch_trip_stop.Trailer_ID |
| Trip Stop Location Name | Sch_trip_stop.Location_ID (Link To Geo_Location |
| Postcode | Geo_location.Postcode |
| Location Type | Geo_location.Branch |
| Location Trailer Restrictions | Max Trailer length against location |
| Activity | Sch_haulage_activity.Activity_Name |
| Planned Arr | Sch_trip_stop.Arrive |
| Actual Arr | Sch_trip_stop.Actual_Arrive |
| Planned Dep | Sch_trip_stop.Depart |
| Actual Dep | Sch_trip_stop.Actual_Depart |
| Planned Drop Time | Sch_trip_stop.Depart - Sch_trip_stop.Arrive |
| Actual Drop Time | Sch_trip_stop.Actual_Depart - Sch_trip_stop.Actual_Arrive |
| Trip Comments | Use TRM.Get_Trip_Comments |
| Product Type | Sch_order_line.Product_Type |
| Temp Combo | Sch_ord.Temp_Combo_ID |
| DU type | Sch_order_line.DU_Type |
| Planned DU Qty | Sch_order_line.Quantity |
| Actual Desp DU qty | Sch_order_line.Actual_Despatched_Quantity |
| Actual Del DU qty | Sch_order_line.Actual_Quantity |
| DU Var | Sch_order_line.Actual_Quantity - Sch_order_line.Quantity |
| DU qty returned | Qty on an order with a non conformance |
| Vehicle fill %RPE | Actual RPE as a % of Res_trailer_type.Max_RPE |
| Planned Weight | Sch_order_line.Weight |
| Actual Weight | Sch_order_line.Actual_Weight |
| Weight Variance | Sch_order_line.Actual_Weight - Sch_order_line.Weight |
| Vehicle Fill% Weight | Actual Weight as a % of Res_trailer_type.Max_Weight |
| Planned Lifts | To be added later. Header to be included in extract |
| Actual Lifts | To be added later. Header to be included in extract |
| Lifts Variance | To be added later. Header to be included in extract |
| Vehicle Fill %Lifts | To be added later. Header to be included in extract |
| Planned Km | Sch_trip.Distance |
| Actual Km | Sch_trip.ODO_End - Sch_trip.ODO_Start |
| Variance Km | Calculated Value of Actual - Sch_trip.Distance |
| Planned hrs | Sch_trip.Drive_Time |
| Actual hrs | Sch_trip.End_Time - Sch_trip.Start_Time |
| Variance hrs | Calculated Value for Actual Hrs - Sch_trip.Drive_Time |
| Vehicle / Driver%Utilisation | Time the vehicle was in use as a % of 24 hours |
| POD | Sch_ord.POD |
| Non Conformance code | Sch_ord_non_conform.Reason_Code |
| Non conformance desc | Sch_reason_code.Description |
| Non conformance reason | Sch_ord_non_conform.Comments |
| Order Comments | Sch_ord.Comments |
| Order Special Instructions | Sch_ord.Special_Instructions |
N.B. Lifts are being added under a separate RIO.
OPS Summary Section
The summary section will be printed above the details section already mentioned.
The first part of the summary will display the parameters that were used to generate the Report, these will be
COST CENTRE
CLIENT
CARRIER
DELIVERY DEPOT
DATE RANGE
The summary will then show the totals for the extract broken down into different sections. Firstly it will show
Number of Orders
Number of Despatch Units
Weight
Number of Lifts
Vehicle Utilisation by Carrier
This will be further broken down to show
Collected
Delivered
Returned
Total
Balance
| Collected | Delivered | Returned | Total | Balance | |
| Orders | |||||
| Despatch Units | |||||
| Weight | |||||
| Lifts | |||||
| Vehicle Utilisation by Carrier (3 lines for RPE, Lifts and Time) |
The balance will be the total delivered minus the returned and will show for the DU’s only.
The vehicle utilisation will show the RPE, Lifts and Time for the extract. This will be calculated based on the total available time, RPE and Lifts for the chosen date range. The time will be the number of hours vehicles were in use as a percentage of the total time for all trips. The number of RPE’s shown as a percentage of the total number of Max RPE’s for the trailers on the trips. N.B. Lifts will not be included at this point. Each total will be shown against the Inbound and outbound trips.
The next section of the summary will show the Trip information split into 3 categories, Own Fleet, Internal Subcontract and External Sub Location.
OWN FLEET – The owning depot is the hub location of the carrier
INTERNAL SUB CONTRACT – The owning depot is not the hub location of the carrier. The carrier is of type FLEET and has been assigned a hub location.
EXTERNAL SUB LOCATION – The carrier does not have a hub location and is set up as a HAULIER.
For each category the extract will show,
- Trips by status and a total of all trips
- Total KM of the trips
- Total Hours of the trips
- Average shift per driver / vehicle – the average shift duration across the trips. This will be calculated as ‘Total hours for all trips \ number of trips’
- Average trips per day per vehicle – This will be calculated by counting the number of trips divided by the number of trailers used.
- Trips not Completed – Trips not at status completed or trips that do not have all actuals entered.
- On Time Deliveries (see below)
- Planned Vs actuals show against then Non conformance code used – the number of time a non conformance code was used.
OTD (on time deliveries) will be reported as a %, the calculation of this field will differ depending on the COST_CENTRE of the order (HUK or BAX) (On time for HUK = delivered by latest delivery time on order.
On time for BAX = delivered no more than an hour earlier or three hours later than the planned delivery time.)
Parameters will be created for BAX_OTD and HUK_OTD , to store the calculation.
Campus Summary Section
The summary section will be printed above the details section already mentioned.
The first part of the summary will display the parameters that were used to generate the Report, these will be
COST CENTRE
CLIENT
CARRIER
DELIVERY DEPOT
DATE RANGE
The next section will show a breakdown of the orders, du types and lifts for each of the 3 carrier categories detailed in the last section.
The total number of orders will be shown, this will then be split into Inbound and Outbound for each of the 3 categories with a percentage total for each category. The percentage total will be the number of trips in that category shown as a percentage of the total trips.
The DU’s will be shown split by DU type and product type. This will give a total per DU type and product type used and also a percentage total which will be the percentage of the total DU’s.
| Total | Collected | Delivered | Total | % | Collected | Delivered | Total | % | Collected | Delivered | Total | % | |||
| Orders | |||||||||||||||
| DU's | |||||||||||||||
| Lifts | |||||||||||||||
The next section of the summary will show the Trip information split into the 3 carrier categories.
For each category the extract will show,
- Trips by status and a total of all trips
- On Time Deliveries (see below)
- Planned Vs actuals show against then Non conformance code used – the number of time a non conformance code was used.
The on time deliveries will be calculated in the same way as described for the OPS summary.
The Lifts column in the detail section, and also any use of Lifts in the summary will be added at a later date. The development behind the Lifts column has not yet taken place. The column headings will be included in the extracts.
Document History
| Initial version | ||||
| Reviewed and Issued | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
