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.

Approved Price Report

This extract will display the estimated versus calculated / actual cost specifically for orders captured through the web order portal. The extract will be selected by a created date range and optional customer (account) and will be provided as a CSV output.

The output rows will be sorted and sequenced by date created, then order reference; the columns reported will be

Date Created, Customer (account), Order Ref, *Requestor, *Approver, *Approved Date, Order Status, Journey Number (schedule and trip number), Trip Status, Carrier and Carrier Name, Estimated Cost and Actual Cost.

• note the fields highlighted are assumed to be uploaded into Additional References fields on the order for Web Orders uploaded into C-TMS using the to be modified CSV data import functionality.

Short Rail Report

The Short Rail extract provides transport plan and execution information for this commodity type. The extract will be provided with a selection criteria of commodity (product type) so can be run for any product type not just short rail as required. The output will be provided in CSV output format.

The selection criteria will be Product Type (commodity) and Trip Schedule date range.

The rows will be sorted and sequenced by Trip Schedule Date and Trip Number; the columns reported will be;

Trip Schedule, Trip Number, Order Ref (cust ref), Booking Ref, Del Point Ref (assuming one of these could be a CORUS reference as uploaded into C-TMS), Carrier Code and Name, product code and description, length, quantity, from location name, from location postcode, to location name, to location postcode, HIAB required, delivery date, earliest delivery time, latest delivery time, delivery contact name, contact details and order notes for collection location.

Additional Reporting Requirements An additional Report is required containing the details of all trips and orders for a given schedule range and cost centre The fields required in the output are –

• Date order created
• Manual Order Y/N
• Order Created By
• Cancelled Order
• Cancelled by
• Journey Reference(only to be shown once per trip
• Customer Order Number
• Commodity
• Failed Y/N
• Failure Reason
• Journey Status
• Order Status
• EFX Number
• Account(heavy/non heavy/Ad Hoc)
• Period
• Week Number within period
• Vehicle Type
• Haulier Name
• Haulier Contact Name
• Haulier Contact Tel Num
• Date Briefed
• Briefed By
• Delivery Mode(next day,standard,emergency etc)
• Order Contact Name
• Order Contact No
• Site Name – Collection
• Site Notes (stored against site)
• Collection Contact Name
• Collection Telelphone no
• Address 1
• Address 2
• Address 3
• Postcode
• Collection Region
• Planned Collection Date
• Planned collection start time
• Palnned collection end time
• Actual Collection Date
• Actual Collection Start time
• Actual collection end time
• Collection instructions/Notes (stored against order)
• Site Name (delivery)
• Site Notes (stored against site)
• Delivery Contact Name
• Delivery Telephone No
• Address 1
• Address 2
• Address 3
• Postcode
• Delivery Region
• Planned Delivery Date
• Planned delivery start time
• Planned delivery end time
• Actual delivery date
• Actual delivery start stime
• Actual delivery end time
• Delivery Instructions/Notes (stored against order)
• Planned weight
• Actual Weight
• Order Miles
• Order Mileage Overridden (Y/N)
• Order Overridden Mileage
• Order Mileage band
• Journey Miles(only appear once per trip)
• Journey Mileage overridden(Y/N)
• Journey Overridden Mileage
• Journey Mileage Band
• Number of Pallets
• Total Revenue
• Total Cost
• Margin
• Distance Rate Revenue
• Distance Rate Revenue Overridden (Y/N)
• Distance Overridden Revenue Price
• Distance Rate Cost
• Distance Rate cost overridden (Y/N)
• Distance overridden cost price
• Hiab (Y/N)
• Hiab Revenue
• Hiab Cost
• PTS(Y/N)
• PTS Revenue
• PTS cost
• Banksman (Y/N)
• Banksman Revenue
• Banksman Cost
• Vehicle Escort (Y/N)
• Vehicle Escort Revenue
• Vehicle Escort Cost
• Demurrage (Y/N)
• Demurrage Revenue
• Demurrage Cost
• Putaway to MSP(Y/N)
• Putaway to MSP Revenue
• Putaway to MSP Cost
• Additional Drop(Y/N)
• Additional Drop Cost
• Saturday(Y/N)
• Saturday Revenue
• Saturday Cost
• Sunday/Bank Holiday(Y/N)
• Sunday/Bank Holiday Revenue
• Sunday/Bank Holiday Cost
• Other Service(Y/N)
• Other Service Revenue
• Other Service Cost
• Raised By (sent by customer in CSV)
• Approver(sent by customer)
• Cost Code(provided by customer)
• Order Type

The most significant service codes are fixed as column headings all others that are less often used grouped into an ‘other’ column.

The output rows will be sorted and sequenced by trip then order reference.

Oracle Reporting Suite

The Oracle Reporting Suite can also be used to run pre existing reports, the columns in the report can be selected and de-selected as required an example of the reporting suite is displayed below