287835

DHL MTS

Develop CSV Extract

FUNCTIONAL SPECIFICATION - 10.6

- 1.0

Reference: FS 287835 TH-8FVJUJ

Client Requirement

Change Request Summary:

Develop CSV extract to the format used in the operation currently as a central data repository for periodic management reports, statistics and KPIs – will look something like the current C-TMS Trip and Order extract.

Change Request Details:

Develop CSV extract to the format used in the operation currently as a central data repository for periodic management reports, statistics and KPIs – will look something like the current C-TMS Trip and Order extract.

Benefits identified as a result of the change:

Migration component.

Solution

A new CSV extract with the name ‘STL Trip and Order Extract’ will be setup to be run from the ‘Exports’ screen.

The parameters for the extract will be:

• Start Trip Schedule
• End Trip Schedule
• Order Status
• Carrier
• Location Type

Data will be extracted for the trips and orders based on the selection criteria listed above.

The data present in the existing ‘Trip and Order Detail Extract’ will be present in the new ‘STL Trip and Order Extract’ but with the following items removed:

1. Schedule
2. Group Name
3. Product Type
4. DU Type
5. RPE Quantity
6. Lane Comments
7. Order Revenue
8. Order VAT
9. Trailer Type
10. Trip Cost Centre
11. Trip Currency
12. Trip VAT

The following items will be added:

1. Temperature
2. From Location Address Line 1
3. From Location Address Line 2
4. From Location Address Line 3
5. From Location Town
6. From Location County
7. From Location Postcode
8. From Location Contact Name
9. From Location Contact Phone
10. To Location Address Line 1
11. To Location Address Line 2
12. To Location Address Line 3
13. To Location Town
14. To Location County
15. To Location Postcode
16. To Location Contact Name
17. To Location Contact Phone
18. General Comments
19. Actual Quantity

Other data required may be extracted as described below:

The ‘Temperature’ will be obtained from Microlise but is subject to the development of a different RIO. If the development has not been performed to store the temperature then the item will be blank.

The ‘Non Conformances’ will be a list of reason codes for the trip activity.

The ‘Activity Name’ may be the ‘Service’ as it indicates ‘Load’ or ‘Unload’ at the trip stop.

The reason for selecting the haulier may be recorded in the ‘General Comments’ for the trip.

The ‘DU Quantity’ indicates the ordered quantity.

The ‘Actual DU Quantity’ indicates the delivered quantity.

The ‘Early’ and ‘Late’ dates and times for the order may be the ‘Visit Date’.

The ‘Trip Cost’ will be renamed ‘Carrier Cost’ as displayed as the ‘Carrier Cost’ in the ‘Finance’ tab page in the trip planning screens.

N.B. Any commas within the data from C-TMS will be removed prior to extract.

Scope

This change will be applied to system version 10.6.

Set-up

Pre-requisites

The user will require authorisation to run the new export which can be promoted to relevant user groups by DHL implementation team.

Menu Structure

‘Unchanged’

Data

The export parameters will be setup using the scripts in Appendix A.

Functional Description

Data Setup

The new report will be run from the existing ‘Exports’ screen with the following parameters available:

• Start Trip Schedule (Mandatory)
• End Trip Schedule (Mandatory)
• Order Status (Optional)
• Carrier (Optional)
• Location Type (Optional)

The ‘End Trip Schedule’ must not be before the ‘Start Trip Schedule’ and both will accept a schedule date in format ‘YYMMDD’.

A list of values will be available for the ‘Order Status’, ‘Carrier’ and ‘Location Type’:

• ‘Order Status’ will be either ‘SCHEDULED’ or ‘SCHED_COLL’.
• ‘Carrier’ will be obtained from the records in C-TMS’
• ‘Location Type’ will be ‘ALL’, ‘RDC’ or ‘SUPPLIER’.

‘STL Trip and Order Extract’

The data will be extracted for the trips and orders based on the export parameters as may be seen in the table below:

Parameter	Table	Column
Start Trip Schedule	SCH_TRIP	SCHED_NAME
End Trip Schedule	SCH_TRIP	SCHED_NAME
Order Status	SCH_ORD	STATUS
Carrier	SCH_TRIP	CARRIER_ID
Location Type	GEO_LOCATION	DEPOT

‘Location Type’ will be used to select the depot of the ‘From Location’ of the order.

The export parameters will be passed to new procedure ‘DP_CSV2. STL_TRIP_ORDER_EXTRACT’ as type ‘VARCHAR2’ with the filename and path for the extract:

• I_EXPORT_PATH
• I_EXPORT_FILE
• I_START_SCHED
• I_END_SCHED
• I_ORDER_STATUS
• I_CARRIER_ID
• I_LOC_TYPE

These parameters will then be passed to new function ‘DP_CSV2. WRITE_STL_TRIP_ORDER_EXTRACT’ to select the records for the extract with ‘V_ERRMSG’ as type VARCHAR2 for the return of any error messages generated.

The data present in the existing ‘Trip and Order Detail Extract’ will be present in the new ‘STL Trip and Order Extract’ but with the following items removed:

1. Schedule
2. Group Name
3. Product Type
4. DU Type
5. RPE Quantity
6. Lane Comments
7. Order Revenue
8. Order VAT
9. Trailer Type
10. Trip Cost Centre
11. Trip Currency
12. Trip VAT

The following items will be added to the new export:

1. Temperature
2. From Location Address Line 1
3. From Location Address Line 2
4. From Location Address Line 3
5. From Location Town
6. From Location County
7. From Location Postcode
8. From Location Contact Name
9. From Location Contact Phone
10. To Location Address Line 1
11. To Location Address Line 2
12. To Location Address Line 3
13. To Location Town
14. To Location County
15. To Location Postcode
16. To Location Contact Name
17. To Location Contact Phone
18. General Comments
19. Actual DU Quantity

Therefore, the items included in the ‘STL Trip and Order Extract’ will be as follows:

Item	Title	Table	Column	Format
1	Trip Status	SCH_TRIP	TRIP_STATUS
2	Trip Number	SCH_TRIP	TRIP_ID
3	Route Code	SCH_TRIP	ROUTE_CODE
4	Start Time	SCH_TRIP	START_TIME	DD/MM/YYYY HH24:MI
5	Departure Time	SCH_TRIP_STOP	DEPART	DD/MM/YYYY HH24:MI
6	Stop No	SCH_TRIP_STOP	STOP_NO
7	Location Name	GEO_LOCATION	LOCATION_NAME
8	Planned Arrive	SCH_TRIP_STOP	ARRIVE	DD/MM/YYYY HH24:MI
9	Actual Arrive	SCH_TRIP_STOP	DEPART	DD/MM/YYYY HH24:MI
10	Planned Depart	SCH_TRIP_STOP	ACTUAL_ARRIVE	DD/MM/YYYY HH24:MI
11	Actual Depart	SCH_TRIP_STOP	ACTUAL_DEPART	DD/MM/YYYY HH24:MI
12	Activity Name	SCH_HAULAGE_ACTIVITY	ACTIVITY_NAME
13	OMS Ref	SCH_ORD	OMS_REF
14	Customer Ref	SCH_ORD	EXTERNAL_REF
15	Booking Ref	SCH_ORD	BOOKING_REF
16	Del Point Ref	SCH_ORD	DEL_POINT_REF
17	POD Name	SCH_ORD	POD_NAME
18	Delivery Priority	SCH_ORD	DEL_PRIORITY
19	Customer	SCH_ORD	CUSTOMER
20	Order Cost Centre	SCH_ORD	COST_CENTRE_NAME
21	Order Schedule	SCH_ORD	SCHED_NAME	YYMMDD
22	From Location	SCH_ORD	FROM_LOC
23	From Location Address Line 1	GEO_LOCATION	ADDRESS_LINE1
24	From Location Address Line 2	GEO_LOCATION	ADDRESS_LINE2
25	From Location Address Line 3	GEO_LOCATION	ADDRESS_LINE3
26	From Location Town	GEO_LOCATION	TOWN
27	From Location County	GEO_LOCATION	COUNTY
28	From Location Postcode	GEO_LOCATION	POSTCODE
29	From Location Contact Name	GEO_CONTACT	FORENAME + ‘ ‘ + SURNAME
30	From Location Contact Phone	GEO_CONTACT	PHONE
31	To Location	SCH_ORD	TO_LOC
32	To Location Address Line 1	GEO_LOCATION	ADDRESS_LINE1
33	To Location Address Line 2	GEO_LOCATION	ADDRESS_LINE2
34	To Location Address Line 3	GEO_LOCATION	ADDRESS_LINE3
35	To Location Town	GEO_LOCATION	TOWN
36	To Location County	GEO_LOCATION	COUNTY
37	To Location Postcode	GEO_LOCATION	POSTCODE
38	To Location Contact Name	GEO_CONTACT	FORENAME + ‘ ‘ + SURNAME
39	To Location Contact Phone	GEO_CONTACT	PHONE
40	Early Collection	SCH_ORD	EARLY_AVAIL	DD/MM/YYYY HH24:MI
41	Late Collection	SCH_ORD	LATE_AVAIL	DD/MM/YYYY HH24:MI
42	Early Delivery	SCH_ORD	EARLY_DEL	DD/MM/YYYY HH24:MI
43	Late Delivery	SCH_ORD	LATE_DEL	DD/MM/YYYY HH24:MI
44	DU Quantity	SCH_ORDER_LINE	QUANTITY
45	Actual DU Quantity	SCH_ORDER_LINE	ACTUAL_QUANTITY
46	Order Comments	SCH_ORD	ORDER_COMMENTS
47	Non Conformances	SCH_ORD_NON_CONFORM	REASON_CODE + ‘;’
48	Trailer ID	SCH_TRIP	TRAILER_ID
49	Owning Depot	SCH_TRIP	OWNING_DEPOT
50	Carrier	SCH_TRIP	CARRIER_ID
51	Driver	RES_PERSON	SURNAME + ‘ ‘ + FORENAME
52	Vehicle	SCH_TRIP	TRACTOR_ID
53	Total Distance	SCH_TRIP	DISTANCE	NUMBER(12,2)
54	Total Elapsed Time	SCH_TRIP	ELAPSED_TIME	NUMBER(5)
55	Total Drive Time	SCH_TRIP	DRIVE_TIME	NUMBER(5)
56	Carrier Cost	SCH_TRIP	TRIP_COST	NUMBER(20,2)
57	Temperature			BLANK
58	Reason - Courier	SCH_TRIP_COMMENT	COMMENT_DETAIL

• ‘Departure Time’ will be obtained from the first stop on the trip.

• ‘Activity Name’ will be obtained from the haulage activity (i.e. ‘Load’ will correspond to the service ‘Collection’ and ‘Unload’ to ‘Delivery’).

• ‘Location Name’ will be obtained using function ‘GEO.GET_LOC_NAME_ID’ for the trip stop location (i.e. ‘SCH_TRIP_STOP.LOCATION_ID’).

• The contact information will be obtained from the first contact record for the ‘From Location’ or the ‘To Location’ (i.e. with the lowest ‘ID’).

• The ‘Early’ and ‘Late’ dates and times for the order correspond to the ‘Visit Date’.

• The ‘DU Quantity’ indicates the ordered quantity.

• The ‘Actual DU Quantity’ indicates the delivered quantity.

• The ‘Non Conformances’ will be a list of reason codes for the trip activity delimited by a ‘;’.

• ‘Driver’ will be obtained using function ‘RES.GET_DRIVER_NAME’ for the driver of the trip (i.e. ‘SCH_TRIP.DRIVER_ID’) and then the comma replaced with a ‘;’ using the function ‘CLEAN’.

• The ‘Trip Cost’ will be renamed ‘Carrier Cost’ as displayed as the ‘Carrier Cost’ in the ‘Finance’ tab page in the trip planning screens.

• The ‘Temperature’ will be obtained from Microlise but is subject to the development of a different RIO. Therefore, at present the temperature cannot display any values.

• ‘Reason – Courier’ will be entered in the ‘General Comments’ tab page of the trip planning screens and will be obtained for comment type ‘GENERAL’ then any commas replaced with a ‘;’ using the function ‘CLEAN’.

N.B. The new items are displayed in blue ink.

N.B. Any commas within the data from C-TMS will be removed prior to extract to avoid the inclusion of any unexpected delimiters: this is purpose of the function ‘CLEAN’.

Table Updates Required

The export parameters may be created using the following scripts:

INSERT INTO rep_report (NAME,REPORT_TYPE,FILENAME,PROC_NAME,PRINTER_TYPE,ORIENTATION) VALUES ('STL Trip and Order Extract','CSV','STL_Trip_Order_Extract','DP_CSV2.STL_TRIP_ORDER_EXTRACT','Laser','LANDSCAPE'); /

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,PARAM_TITLE,SQL_STRING) VALUES ('STL Trip and Order Extract','CSV','P_START_SCHED','P_START_DATETIME','M','Start Trip Schedule',);

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,PARAM_TITLE,SQL_STRING) VALUES ('STL Trip and Order Extract','CSV','P_END_SCHED','P_END_DATETIME','M','End Trip Schedule',);

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,PARAM_TITLE,SQL_STRING) VALUES ('STL Trip and Order Extract','CSV','PG_SELECT_LIST1','P_ORDER_STATUS','O','Order Status','SELECT 'SCHEDULED' col1, 'SCHEDULED' col2 FROM DUAL UNION SELECT 'SCHED_COLL' col1, 'SCHED_COLL' col2 FROM DUAL');

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,PARAM_TITLE,SQL_STRING) VALUES ('STL Trip and Order Extract','CSV','PG_SELECT_LIST2','P_CARRIER_ID','O','Carrier','SELECT distinct(carrier_name) COL1, carrier_id COL2 FROM res_carrier rc, adm_user_param aup WHERE rc.carrier_id like DECODE(aup.value,'ALL','%%',aup.value) AND aup.username = (SELECT user FROM dual) AND aup.param_type = 'CARRIER);

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,PARAM_TITLE,SQL_STRING) VALUES ('STL Trip and Order Extract','CSV','PG_SELECT_LIST3','P_LOC_TYPE','O','Location Type','SELECT 'ALL','ALL' FROM DUAL UNION SELECT 'RDC','RDC' FROM DUAL UNION SELECT 'SUPPLIER','SUPPLIER' FROM DUAL'); /

References

Ref No	Document Title & ID	Version	Date
1	EST-287835 TH-8FVJUJ Develop CSV Extract v2.0.doc	2.0	06/05/11

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

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

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager