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:
- Collection OTIF Day measuring whether the order was collected successfully on the day requested
- Delivery OTIF Day measuring whether the order was delivered successfully on the day requested
- 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)
- 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
Once the columns are selected/de-selected using the tick box at the right of the column the report runs and produces an extract an example of which is shown below
Scope
This change will be applied to system version 10.7
SET-UP
Pre-Requisites
291360 - MS-8KNGFM - Services
Data
A significant service column will be added to the ACC_SERVICES table to control grouping of services for reports.
Implementation Advice
A system super user will be required to grant access to the extracts to the correct user groups.
FUNCTIONAL DESCRIPTION
Revenue Check Account
A significant service column will be added to the ACC_SERVICES table to control the grouping of services for reporting, this will be a single character column which if set to ‘Y’ will use the service code as a column heading. The services tab within the Accounts Maintenance screen will be changed to display the significant service field. The Services capture tab will also be amended to allow the capture or amendment of the field.
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
The following information is required in the extract
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The output is a revenue statement for orders debriefed on a specific date this means the date and time the order status reached “DELIVERED” and the trip status is COMPLETED or CONFIRMED. The distances on the records must be returned in miles. The extract will have a start and end date and a customer account parameter which must be populated to extract the relevant data. The output will be sorted by delivery date and time of each order. The service surcharges will be displayed horizontally it is suggested that the most significant service codes are fixed as column headings and then others used less often grouped into an “Other column”. This will be achieved using the significant service column on the ACC_SERVICES table.
Month End Accruals
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
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.
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have a start and end date and a customer parameter which must be populated to extract the relevant data. The date range will be used to extract all orders which are on a trip collected between the dates. The output will be sorted by delivery date and time of each order. The service surcharges will be displayed horizontally it is suggested that the most significant service codes are fixed as column headings and then others used less often grouped into an “Other column”. This will be achieved using the significant service column on the ACC_SERVICES table.
Debrief Performance
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have SCHEDULE_FROM, SCHEDULE_TO,CUSTOMER_ACCOUNT and CARRIER parameters. Only data associated with the cost centre of the user running the extract will be selected. Data will be displayed in trip order.
The extract will contain the following information
Drivers Hours
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have DELIVERY_DATE_FROM, DELIVERY_DATE_TO and ACCOUNT parameters. A page heading will be created for each customer account and a report footing will be required showing total of journeys and total of hours. The data will be displayed in trip order.
The report will contain the following information
OTIF Reports
Essentially the four OTIF reports that look the same:
Collection OTIF Day measuring whether the order was collected successfully on the day requested Delivery OTIF Day measuring whether the order was delivered successfully on the day requested 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) 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)
Four extracts will be developed and will be available to be run from the Exports Screen an example of which is shown below
The extracts will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have DATE_FROM, DATE_TO or SCHEDULE_FROM and SCHEDULE_TO ,ACCOUNT, COMMODITY and optionally the CARRIER as parameters.
The following information is required for the extracts
The status field will contain one of the values On Time in full, Non Blameworthy Fault or Blameworthy fault this information will be obtained based on delivery times and failure reasons. The final section of each export will provide an overall total summary based on the status. e.g. On time in Full, total records and percentage value in relation to total orders by the delivery day and on the final page a grand total across all days covered by the report. Note – debrief reasons are captured at load or unload or both which facilitates the style of reporting required for OTIF.
Vehicle Utilisation
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
Non Heavy
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have a “from” and “to” schedule range as parameters. Only data associated with the cost centre of the user running the report will be selected. The Data will be produced in trip id order and the following columns will be required in the report
Note pallets back is return collections planned or ad-hoc collected on a vehicle (not empty media return). Report totals as average will be provided as a footing of planned distance, actual distance, fuel drawn, hours worked, pallets out, pallets back, fill outbound and fill back.
Heavy (Ad-Hoc etc)
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have a “from” and “to” schedule range and optionally customer account as parameters. Only data associated with the cost centre of the user running the report will be selected. The Data will be produced in trip id order and the following columns will be required in the report
Report totals will be provided as a footing average of planned distance, actual distance, fuel drawn, hours worked, shipment weight, capacity weight and fill.
Approved Price Report
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have a “from” and “to” created date range and optionally customer account as parameters. Only data associated with the cost centre of the user running the report will be selected. The output rows will be sorted by created date and then order reference. The following information will be required.
Short Rail Report
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have SCHEDULE_FROM, SCHEDULE_TO and PRODUCT TYPE as parameters. Only data associated with the cost centre of the user running the report will be selected. The data will be sorted by trip schedule and trip id. The report can be run for any valid product type. The following information is required in the report.
Additional Reporting Requirements
An extract will be developed and will be available to be run from the Exports Screen an example of which is shown below
The following information is required in the extract
Supporting Notes –
Failure reasons will be reported from the order and if more than one failure code has been entered, the first one will be reported in the output.
The Order Mileage Override Flag and Order Mileage Override will not be supported by C-TMS. Any changes to the revenue to charge to NR manually input into C-TMS will be qualified by the user by entry of comments in the special Instructions field of the order.
The Journey Mileage Override Flag and Journey Mileage Override will not be supported by C-TMS. Any changes to the cost to be charged by a carrier manually input into C-TMS will be qualified by the user by entry of comments in the General Comments field for the Trip. (A new column will be included into the definition above to accommodate this field).
The Raised By, Approver and Cost Centre values will be reported from fields in the additional references found against each order. The Additional References functionality in C-TMS allows dynamic configuration of new fields to store ‘miscellaneous’ or ‘general’ information about an order against a user defined label (rather than developing specific fields in the C-TMS database).
The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The extract will have a start and end schedule and a cost centre parameters which must be populated to extract the relevant data. The output will be sorted by trip reference and order reference.
REFERENCES
| EST-291378 –MA-8KNHMH Output Reports and Extracts |
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued | ||||
| Revised | ||||
| Reviewed and Issued | ||||
| Revised | ||||
| Reviewed and Issued | ||||
| Reviewed by DHL | ||||
| Re-issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
