294567

DHL C-TMS

AA Daily Performance Report

FUNCTIONAL SPECIFICATION - 10.7

07/19/12 - 1.0
Reference: FS 294567 OB-8NRE6H

FUNCTIONAL OVERVIEW

Client Requirement

The business requires 2 versions of a daily 'Delivery Performance' report that they can send out to the customers and use internally.

The report needs to detail the overall performance for each customer, and the performance by each site on the first page. On the second and subsequent pages, any exceptions should display (if there are any).

The parameters for what counts as an exception are detailed on the attached report design. If there are no exceptions, the report should just be on one page.

The business would like two versions of this report: one that excludes a certain range of ‘NON_CON’ codes from the ‘OTIF’ column and one that includes all ‘NON_CON’ codes in the ‘OTIF’ column.

The name of the report that excludes the range of ‘NON_CON’ codes is to be called 'Delivery Status Report'

The name of the report that includes all ‘NON_CON’ codes is to be called 'Delivery Status Report Internal'

The range of ‘NON_CON’ codes that is to be excluded from the 'Delivery Status Report' is ‘A01’ through to ‘A99’ (please note these codes have not been set up in AA test environment yet and will need doing once the report has been delivered).

The reports should be in a .pdf format.

The reports should have the following parameters to run:

• From Sched - [Mandatory]
• To Sched - [Mandatory]
• Customer - this should be a drop down of all the customers from the CUST_COST table. There should also be an option for 'ALL' customers. [Mandatory]

Solution

A new Oracle (PDF) report will be generated based on the attached requirements.

Two options for running the report will be provided to reduce the amount of code required where differences in layout and content are required these will be controlled programmatically within the report process. Two separate methods of running the report will be provided to avoid confusion for the users.

Scope

This change will be applied to system version 10.7.0.

SET-UP

Pre-Requisites

The new report and its selection parameters will need to be created.

Data

The new report and its selection parameters will need to be created. (See Appendix A for the scripts to add the new report.)

NB Two Reports should be set up in the Report Parameters one for Internal and One for External. The value of ‘usage’ will default accordingly.

Implementation Advice

The new report will need to be authorised for use by the relevant user groups:

FUNCTIONAL DESCRIPTION

AA Delivery Statuse Report Parameters

The new ‘AA Delivery Status Report’ will have the following selection parameters available:

• Start Schedule
• End Schedule
• Customer
• Usage

The customer parameter will default to ‘ALL’ to indicate that all customers will be valid unless otherwise specified and the list of customers will be restricted to the value of the ‘CUSTOMER’ parameter for the user:

The parameters will contain the following lookups and validation:

The default value for the ‘Usage’ selection parameter will be ‘INTERNAL’ for internal usage.

AA Delivery Status Report Layout

The proposed design of the report may be seen below:

N.B. The standard OBS report format and page headings will be used in the report.

Where ‘Driver Trip Sheet’ will be ‘Delivery Status Report’, ‘TRIP_SHEET’ will be ‘AA_DEL_STAT’ and ‘Depot’ will be blank.

The ‘Summary By Customer’ (at the customer level), ‘Summary By Site’ (at the location level) and ‘Exceptions’ (at the transport order level) sections will be developed as displayed in the example provided.

Comments present in the example provided:

AA Delivery Status Report Query

The new report will have two different queries for internal and external usage as determined by the ‘Usage’ selection parameter.

The ‘Usage’ refers to the type of reason code in the ‘Reason Codes’ tab page of the ‘Business Data Maintenance’ screen:

N.B. The reason codes may now be segregated by cost centre and a ‘+’ in the cost centre indicates that the reason code is available for all cost centres. Therefore, it is expected that the new ‘NON_CON’ reason codes will be created for cost centre ‘NRCC’.

Internal Usage

All ‘NON_CON’ codes will be included in the ‘Total OTIF’ column and the ‘Exceptions’ section of the report.

External Usage

‘NON_CON’ codes in the range ‘A01’ to ‘A99’ will be excluded from the ‘Total OTIF’ column and the ‘Exceptions’ section of the report.

The title of the report will be ‘Delivery Status Report’.

Data

The 4 selection parameters will be passed to the report and used in the query to select the trips and transport order records for the report from the database items:

The report will be divided into 3 sections as shown in section 3.2:

1. Summary By Customer (always displayed)
2. Summary By Site (always displayed)
3. Exceptions (displayed only if any exceptions exist)

The data in the example report will be obtained as follows (note that the title of the report will be ‘Delivery Status Report’ and not ‘Delivery Performance Report’):

Summary by Customer

Customer’ will be the customer name of the transport order on the trip stops.

• ‘Total Deliveries’ will be the total number of trip stops (count of ‘SCH_TRIP_STOP.STOP_NO’) where items for the customer are unloaded for locations of type ‘BRANCH’.

• ‘Total PODs’ will be the total number of trip stops (count of ‘SCH_TRIP_STOP.STOP_NO’) where items for the customer are unloaded for locations of type ‘BRANCH’ and the trip stop has actual arrival and departure times recorded.

‘Total PODs’ will also be expressed as a percentage of the ‘Total Deliveries’.

• ‘Total OTIF’ will be the total number of trip stops (count of ‘SCH_TRIP_STOP.STOP_NO’) selected for the ‘Total PODs’ quantity that have had all of their order items delivered ‘on time’ (in regards to the delivery slots setup against the delivery location of the trip stop) and ‘in full’.

‘Total OTIF’ will also be expressed as a percentage of the ‘Total PODs’.

(N.B. Include or exclude non-conformance reason codes as described in sections 3.3.1 and 3.3.2).

• ‘Total Items Despatched’ will be a subtotal of the despatched quantities (‘SCH_ORD_ITEMS.QTY_TO_DELIVER’) of all of the items included on the transport orders included in the calculation of the ‘Total Deliveries’.

• ‘Total Items Delivered’ will be a subtotal of the delivered quantities (‘SCH_ORD_ITEMS.QTY_DELIVERED’) of all of the items included on the transport orders included in the calculation of the ‘Total Deliveries’.

• ‘Var’ will be the ‘Total Items Despatched’ quantity minus the ‘Total Items Delivered’ quantity.

• ‘Var %’ will be ‘Var’ expressed as a percentage of the ‘Total Items Despatched’.

• ‘Total Items Collected’ will be a subtotal of the despatched quantities (‘SCH_ORD_ITEMS.QTY_TO_DELIVER’) of all of the items included on the transport orders for the customer are loaded at a location of type ‘BRANCH’.

N.B. The collection trip stops will be selected like the delivery trip stops that constitute the ‘Total Deliveries’ which are for the unloading activities.

• ‘Totals’ will be calculated as subtotals and average percentages based on the subtotals calculated.

Summary By Site

• ‘Site’ will be the location name of the trip stops at the location of type ‘RDC’.

• ‘Total Deliveries’ will be the total number of trip stops (count of ‘SCH_TRIP_STOP.STOP_NO’) where items for the customer are unloaded for locations of type ‘RDC’.

• ‘Total PODs’ will be the total number of trip stops (count of ‘SCH_TRIP_STOP.STOP_NO’) where items for the customer are unloaded for locations of type ‘RDC’ and the trip stop has actual arrival and departure times recorded.

‘Total PODs’ will also be expressed as a percentage of the ‘Total Deliveries’.

N.B. All other calculations will be performed as described for the ‘Summary By Customer’ section.

Exceptions

An exception will be recorded for the following conditions:

1. A difference exists between the actual despatched and delivered quantities (assessed for the transport orders themselves on the trip stop).
2. A delivery classed as late (assessed per transport order on the trip stop.)
3. A delivery trip stop has no actual arrival and departure times against it (will include all transport orders on the trip stop.)

• Del Route’ will be the customer reference (‘SCH_ORD.EXTERNAL_REF’) of the transport order that contains the exception.

• ‘Dealer’ will be destination location ID (‘SCH_ORD.TO_LOC’) and name (‘GEO_LOCATION.LOCATION_NAME’) of the transport order that contains the exception.

• ‘Delivery Window’ will be the early delivery (‘SCH_ORD.EARLY_DEL’) and late delivery (‘SCH_ORD.LATE_DEL’) dates and times of the transport order that contains the exception.

• ‘Actual Arrival Time’ will be the actual arrival time (‘SCH_TRIP_STOP.ACTUAL_ARRIVE’) of the trip stop for the unloading activity at the destination location of the transport order that contains the exception.

• ‘Despatched’ will be the total number of items despatched (‘SCH_ORD_ITEMS.QTY_TO_DELIVER’) on the transport order that contains the exception (as the ‘Total Deliveries’ calculation).

• ‘Delivered’ will be the total number of items delivered (‘SCH_ORD_ITEMS.QTY_DELIVERED’) of the transport order that contains the exception (as the ‘Total Deliveries’ calculation).

• ‘Var’ will be the ‘Despatched’ quantity minus the ‘Delivered’ quantity.

• ‘Reason Code’ will be the reason codes (‘SCH_ORD_NON_CONFORM.REASON_CODE’) of usage ‘NON_CON’ held against the transport order.

A section for the reason codes, and their descriptions, used in the ‘Exceptions’ section will be present at the end of the ‘Exceptions’ section should a reason code be displayed in the ‘Exceptions’ section.

• ‘A07’ is a reason code (‘SCH_ORD_NON_CONFORM.REASON_CODE’) of usage ‘NON_CON’ held against the transport orders selected in the previous sections of the report.
• ‘Late loading at Depot’ is the description (‘SCH_REASON_CODE.DESCRIPTION’) of the reason code.

The breakdown of the database records is show below:

Trip -> Stop -> Haulage Activity -> Transport Order -> Line -> Item

Trip -> Stop -> Haulage Activity -> Transport Order -> Non-conformance

REFERENCES

Ref No	Document Title & ID	Version	Date
1	EST-294567 OB-8NRE6H AA Daily Performance Report v1.0.doc	1.0	08/12/11
2	AA Daily Status Report Design v1_03.xls	1_03

DOCUMENT HISTORY

Version	Date	Status	Reason	Initials
0.1	16/12/11	Draft	Initial version	PDR
1.0	19/12/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager