291373

DHL C-TMS

Outputs Reports & Extracts

FUNCTIONAL SPECIFICATION - 10.7

08/12/11 - 1.0
Reference: 291373 MS-8KNHMH

FUNCTIONAL OVERVIEW

Client Requirement

Requirements and development of reporting pack.

Solution

A suite of CSV format data extracts will be developed to provide a management reporting pack for the Network Rail C-TMS solution.

The scope of outputs are;

The numbering convention above is provided to allow cross reference to the current reporting pack and will not be carried forward into C-TMS.

Each of the output in the scope list will be provided in CSV format generated from C-TMS exports menu. The summary description of each output is detailed below. DHL have provided a phase value which defines the priority of development as shown.

For all reports if new accounts (customers) are created these will be selectable where account (customer) or account type (customer group) is a selectable or account is displayed as a field in the output.

Revenue Check Account

This output will be provided as a data export in CSV format. The output is a revenue statement for orders debriefed on a specific date; this means the date and time the order status reaches ‘DELIVERED’ and the trip is at a status of COMPLETED or CONFIRMED.

The output will be provided with selection criteria debrief date from and to range and customer account(Heavy/Non Heavy). The output rows will be sorted by delivery date and time of each order.

Columns for each order will be;

Date Raised, Date Debriefed, Cancelled (true or false) and Status (Trip Status), Journey Number (schedule and trip number), Failure Reason, Order Number (cust ref), EFX number, Account (customer), Commodity Name, Week, Vehicle Type, Haulier, Mode (delivery type), Collection Site (name), Collection Postcode, Collection Planned Date, Delivery Site (name), Delivery Postcode, Delivery Planned Date, Journey Planned Distance Miles (trip), Order Planned Distance Miles, Order Pallets (LPNs), Order Weight, Calculated Revenue, Override Revenue, Total Revenue, Calculated Cost, Override Cost, Total Cost, Margin, Notes, Cost Code, Distance Revenue, Revenue Service & Revenue Surcharge Amount (repeated) Cost Service & Cost Surcharge Amount (repeated).

The service surcharges will be displayed horizontally – It is suggested that the most significant service codes are fixed as column headings and then others that are less often used grouped into an ‘other’ column.

Month End Accruals

The Month End Accruals Report will contain same fields as “Revenue Check Account” and the same selection criteria but to display all orders collected within the selected date range. Note that Revenue Check Account shows all orders debriefed in a date range.

POD Scanning Performance (Will be Renamed Debrief Performance)

The POD scanning extract is a CSV output that shows for a schedule of trips (execution / delivery date of trip), the status of the trip. Trip status will reported as C-TMS trips status so, PLANNED, ACCEPTED (Briefed), EN-ROUTE(heavy orders will not move to s status of EN-ROUTE), CONFIRMED (means carrier has confirmed delivery), COMPLETED (means fully debriefed). A days column is displayed to measure days between job (planned delivery date) and debriefed date. Assume job means the planned delivery date. This output allows a measure of how quickly the carriers are returning paperwork and deliveries are fully debriefed. Selection criteria will be trip schedule or trip schedule range and optionally account type and carrier.

Additional requirements will be to change name of report to Debrief Performance and to add carrier code and carrier name as additional columns.

Each row of the output is a an order on a trip and the rows are sequenced by trip number; the columns displayed will be;

Delivery Date, Delivery Time (planned earliest delivery date time), Debriefed Date (when trip status becomes COMPLETED), Days Difference, Journey Number(schedule and trip number), carrier and carrier name, Order Number (cust ref) ,Trip Status and commodity.

Driver Hours

The driver hours extract will be provided as a CSV output. The report shows for each trip the amount of hours worked.

Hours worked is captured from Microlise for microlise enabled trips and hours entered for all others. It is assumed that Microlise sends driving hours based on trip actuals dates and times.

Selection criteria for the report will be delivery date or delivery date range and customer account.

A page heading will be created for each Account (customer)

Each row of the output is a trip and the rows are sequenced by trip number; the columns displayed will be

Delivery Date, Account (customer), Journey Number (schedule and trip number), Driver code and Name, Carrier, Vehicle, Vehicle Type, Hours.

A report footing will be output as a total of journeys and total of hours

OTIF Reports

The OTIF reports (previously known as Possession Arrival Point Punctuality, Possession Arrival Point Punctuality, Collection on Time in Full and Delivery on time in Full) will be provided in CSV format and is an analysis of delivery at delivery location being executed on time.

Select criteria will be trip schedule or schedule range, or date from and to range to range and customer account and commodity type. Carrier will be included as an optional additional selection criterion.

The rows of the report will be sequenced by trip number within schedule; the columns will be

Delivery Date, Journey Number (schedule and trip number), Carrier and Carrier Name, Vehicle, Account (customer), Order Number (cust ref), Commodity (Product Type), Deliver To Location, Due Date (earliest delivery date and time of order), Date Executed (date and time delivered actual), At Fault (from reason code), Failure Reason (reason code), Debrief Notes, Status (either On Time In Full, Non-Blameworthy Fault, Blameworthy Fault).

A final section of the OTIF outputs at the end will provide an overall summary of On Time in Full Count and %age, Non Blameworthy Count and %age, Total Count and %age by delivery day and then final grand total of the same columns for the report across all days reported.

Essentially the four OTIF reports that look the same:

1. Collection OTIF Day measuring whether the order was collected successfully on the day requested
2. Delivery OTIF Day measuring whether the order was delivered successfully on the day requested
3. Collection OTIF Time Window measuring whether the order was collected within the time window specified on the order (if no time window is specified default to 00:00 – 23:59)
4. Delivery OTIF Time Window measuring whether the order was delivered within the time window specified on the order (if no time window is specified default to 00:00 – 23:59)

Vehicle Utilisation

The vehicle utilisation extract displays an analysis of the vehicle resource utilised to fulfil order delivery. The output is slightly different for non-Heavy fleet (pallets) versus Carrier work (weight) so will be provided as two CSV outputs;

#Non Heavy

The extract will be selected using trip schedule range.

The output rows will be sorted by trip schedule and trip number and columns reported will be;

Carrier (own fleet Worcester), vehicle type, vehicle registration, Journey Number (schedule and trip number), planned distance, actual distance, hours worked for NR (from Microlise actual start and end trip times), pallets out (DU delivered), pallets back (DU collected), Pallet Capacity (from vehicle type), Fill Outbound (%age of pallet fill delivered), Fill Back (%age of pallet fill collected).

The fill value of each trip will be calculated using the number of pallet DUs loaded at the start depot (Worcester NDS) as a percentage of the vehicle capacity to represent Fill Outbound. The Fill Back will be calculated using the number of pallet DUs unloaded back at the end depot (Worcester NDC) from collections made during the delivery route. If the number of pallets exceeds the vehicle capacity then the report will show the Fill percentage as 100%

Report totals will be provided as a footing of planned distance, actual distance, fuel drawn, hours worked, pallets out, pallets back, fill outbound and fill back. This summary will represent the averages for the schedule range period selected.

Note pallets back is assumed to be return collections planned to vehicle (not empty media return) and will include the returns entered on Microlise (as Tasks).

#Heavy (Ad Hoc etc.)

The extract will be selected using trip schedule range and optional customer (account)

The output rows will be sorted by customer (account) then trip schedule and trip number and columns reported will be;

Customer (account), Carrier (own fleet Worcester), vehicle type, vehicle registration, Journey Number (schedule and trip number), planned distance, actual distance,hours worked for NR (from Microlise actual start and end trip times where Microlise is available otherwise times taken from those entered at debrief), shipment weight (of orders moved), capacity weight (of vehicle type), Fill (%age of capacity), Commodity (Product Type).

The fill value of the trip will be calculated using the DU type of the orders against the capacity of the vehicle used on the trip more information regarding volumetrics can be found in RIO 291365 MS-8KNH33 9 – Volumetrics.

Report totals will be provided as a footing of planned distance, actual distance, fuel drawn, hours worked, shipment weight, capacity weight and fill.