274599

DHL MTS

Trip Order Extract by Booking Ref

FUNCTIONAL SPECIFICATION - 10.4.7

07/06/2011 - 2.0
Reference: FS-274599 AA-834HMQ

Client Requirement

We need a new extract which gives us the full visibility of BKG Ref including cross dock trips. We need all the fields available in the attached file to measure our On Time Delivery with full visibility of every step (collection, cross-dock and delivery trip information).

Solution

A new extract will be created to run from the Exports screen. New procedures will be added to DP_CSV to run this extract.

A record will be added to the REP_REPORT table for the new CSV and records will be added to the REP_REPORT_PARAM table to define the three parameters for the CSV.

The extract will contain the following fields for each booking.

SECTION	NAME	DATABASE ITEM
BOOKING DETAIL	BKG Date	SCH_PRODUCT_SUMMARY.BKG_DATE
	Delivery Date	SCH_PRODUCT_SUMMARY.DELIVERY_DATE
	ASN Date	SCH_PRODUCT_SUMMARY.ASN_DATE
	ASN Time	SCH_PRODUCT_SUMMARY.ASN_DATE
	BKG REF	SCH_PRODUCT_SUMMARY.BOOKING_REF
	Delivery Priority	SCH_PRODUCT_SUMMARY.DEL_PRIORITY
	Source ID	SCH_PRODUCT_SUMMARY.SOURCE
	Source Name	GEO_LOCATION.LOCATION_NAME
	Source City	GEO_LOCATION.TOWN
	Destination ID	SCH_PRODUCT_SUMMARY.DESTINATION
	Destination Name	GEO_LOCATION.LOCATION_NAME
	Destination City	GEO_LOCATION.TOWN
	BKG Ref status	SCH_PRODUCT_SUMMARY.STATUS
	Carrier Code	SCH_PRODUCT_SUMMARY.CARRIER_CODE
	TI Creation Date	SCH_ORD.CREATED_DATE
	Order Status	SCH_ORD.STATUS
	BKG Schedule Date	SCH_PRODUCT_SUMMARY.SCHED_NAME
	Current Location	SCH_ORD.CURRENT_DEPOT

COLLECTION TRIP DETAIL	Collection Trip Owning Depot	SCH_TRIP.OWNING_DEPOT
	Collection Trip Created Date	SCH_TRIP.CREATED_DATE
	Collection Trip ID	SCH_TRIP.TRIP_ID
	Collection Trip Status	SCH_TRIP.TRIP_STATUS

POC INFORMATION	POC (Y/N)	SCH_ORD.POC
	POC Date	SCH_TRIP_STOP.ACTUAL_ARRIVE
	POC Time	SCH_TRIP_STOP.ACTUAL_ARRIVE
	POC Entered Date	INT_SAP_POC_OUTBOUND.CREATED_DATE
	POC Entered Time	INT_SAP_POC_OUTBOUND.CREATED_DATE
	POC Acknowledgment	INT_POC_POD_ACK.STATUS_MESSAGE
CROSS DOCK INFORMATION	Cross Dock Owning Depot	SCH_TRIP.OWNING_DEPOT
	Cross Dock Created Date	SCH_TRIP.CREATED_DATE
	Cross Dock Trip ID	SCH_TRIP.TRIP_ID
	Cross Dock Trip status	SCH_TRIP.TRIP_STATUS
	Cross Dock Arrival Date	SCH_TRIP_STOP.ARRIVE
	Cross Dock Arrival Time	SCH_TRIP_STOP.ARRIVE
	Cross Dock Departure Date	SCH_TRIP_STOP.DEPART
	Cross Dock Departure Time	SCH_TRIP_STOP.DEPART
DELIVERY TRIP DETAIL	Delivery Trip Owning Depot	SCH_TRIP.OWNING_DEPOT
	Delivery Trip Created Date	SCH_TRIP.CREATED_DATE
	Delivery Trip ID	SCH_TRIP.TRIP_ID
	Delivery Trip Status	SCH_TRIP.TRIP_STATUS
POD INFORMATION	POD (Y/N)	SCH_ORD.POD
	POD Date	SCH_TRIP_STOP.ACTUAL_ARRIVE
	POD Time	SCH_TRIP_STOP.ACTUAL_ARRIVE
	POD Entered Date in MTS	INT_SAP_POC_OUTBOUND.CREATED_DATE
	POD Entered Time in MTS	INT_SAP_POC_OUTBOUND.CREATED_DATE
	POD Acknowledgment	INT_POC_POD_ACK.STATUS_MESSAGE

Scope

This change will be applied to system version 10.4.7 on SARTST and once approved SARPRD.

Set-up

Pre-requisites

None

Menu Structure

Unchanged

Data

The new extract will be added to the REP_REPORT table to allow it to be selected from the standard exports form.

The user will be able to select from Booking Ref, Booking Date, From Date, To Date, Carrier Code, Owning Depot, Order Status, Booking Status, POC Applied and POD Applied when running the extract. The parameters will be added to the REP_REPORT_PARAM table.

The Booking Date, From Date and To Date parameters will have the format for date and time.

Functional Description

Ten parameters will be available when running the report:

• Booking Ref
• Booking Date
• From Date
• To Date
• Carrier Code
• Owning Depot
• Order Status
• Booking Status
• POC Applied
• POD Applied

The Booking Ref parameter will have a list of valid values ‘20’, ‘30’, ‘45’, ‘80’ or ’90’ but default to ‘All’.

The Booking Date, From Date and To Date parameters will have the format for date and time (i.e. ‘DD/MM/YYYY HH24:MI’) and will default to the system date; the From dates will default the time to ‘00:00’ and the To dates will default the time to ’23:59’; the user will be able to enter a range of booking dates and the end date must not be before the start date; the From Date and To Date parameters will refer to ASN dates.

The Carrier Code parameter will have a list of valid carrier codes but default to ‘All’.

The Owning Depot parameter will have a lookup for owning depots but default to ‘All’.

The Order Status parameter will have a list of valid values but default to ‘All’.

The Booking Status parameter will have a list of valid values ‘PROVISIONAL’ or ‘CONFIRMED’ but default to ‘All’.

The POC Applied and POD Applied parameters will have a list of valid values ‘Y’ or ‘N’ but default to blank for ‘All’

All parameters will be required.

The report will be based on the following data tables and links:

There will be four separate queries written to select the following information:

Booking and Order Details:

Booking details will be selected using the following parameters:

• Booking Ref (SCH_PRODUCT_SUMMARY.BOOKING_REF)
• Booking Date (Range of SCH_PRODUCT_SUMMARY.BKG_DATE)
• From Date (SCH_PRODUCT_SUMMARY.ASN_DATE)
• To Date (SCH_PRODUCT_SUMMARY.ASN_DATE)
• Carrier Code (SCH_PRODUCT_SUMMARY.CARRIER_CODE)
• Booking Status (SCH_PRODUCT_SUMMARY.STATUS)

Order details will be selected using the following parameter:

• Order Status (SCH_ORD.STATUS)
• POC (SCH_ORD.POC)
• POD (SCH_ORD.POD)

Collection Details:

• Collection details will be identified using the haulage activity table to find the trip which loads the order at the FROM_LOC.

Collection details will be selected using the following parameter:

• Owning Depot (SCH_TRIP.OWNING_DEPOT)

Cross Dock Details:

• Cross dock details will be identified using the haulage activity table to find trips where the load and unload locations do not match the TO_LOC and the FROM_LOC of the order.

Cross dock details will be selected using the following parameter:

• Owning Depot (SCH_TRIP.OWNING_DEPOT)

Delivery Details:

• Delivery details will be identified using the haulage activity table to find the trip which unloads the orders at the TO_LOC.

Delivery details will be selected using the following parameter:

• Owning Depot (SCH_TRIP.OWNING_DEPOT)

The export will display data at the OMS_REF level, and although the OMS_REF field will not be displayed on the export the queries will be linked using this field. As a booking reference can be used to generate TIs more than once, the maximum OMS_REF will be selected.

The cross dock query may run at a different frequency to the other queries for instances when an order is cross docked more than once. To ensure that the frequency difference does not cause duplicate records, the cross dock data will be displayed at the end of the extract. For multiple cross docks, the width of the extract will be increased to show the additional information.

POC and POD information will be selected in an additional two queries based on the table INT_SAP_POC_OUTBOUND. The TYPE field will be used to identify a POC or a POD, the two queries will be identical but for the selection criteria of ‘TYPE = POC’ or ‘TYPE =POD’. These queries will be linked to the other data using the OMS_REF.

The ACTUAL_ARRIVE date has been populated in this table for the last year and is consistently displaying the correct stop information for the POD/POC.

For POCs the actual arrival date and time is the actual arrive (as displayed on the Trip Debrief screen) of the PK stop of the Collection Trip.

For PODs the actual arrival date and time is the actual arrive (as displayed on the Trip Debrief screen) of the DL stop of the Delivery trip.

The MAX(CREATED_DATE) for each POC/POD record will be used to identify the latest POC/POD. This is to cover instances where a POC/POD has had to be re-sent.

Two new procedures will be added to the DP_CSV package: TRIP_ORDER_BKG_EXTRACT and WRITE_TRIP_ORDER_BKG_EXTRACT.

Table Updates Required

REP_REPORT table for specifying the extract

The selection parameters will require a record in the REP_REPORT_PARAM table.

References

Ref No	Document Title & ID	Version	Date
1	EST-274599 AA-834HMQ Trip Order Extract by Booking Ref v1.0.doc	1.0	30/03/10

Document History

Version	Date	Status	Reason	Initials
0.1	19/04/10	Draft	Initial version	SEW
1.0	20/04/10	Issue	Reviewed and Issued	MJC
1.1	28/05/10	Update	Updated to include extra selection parameters	PDR
2.0	07/06/10	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager