291172

DHL C-TMS

CSV extract of order and trip info

FUNCTIONAL SPECIFICATION - 10.7

09/09/2011 - 1.1
Reference: FS 291172 AR-8KNKVZ

Functional Overview

Client Requirement

Change Request Summary:

Develop csv extract of order and trip information to support management reporting pack.Paul McGoran/Manchester/UK/NFC

Change Request Details:

Provide an extract as per the SDD section 2.2.18

Benefits identified as a result of the change:

Allow the operation to produce existing KPI's

Solution

A new CSV extract will be created which will be available to run from the File Interface – Exports screen. The extract will be based on the existing Debriefed Jobs extract, provided by the Business.

Name	C-TMS field	C-TMS Table
OUTBASE CODE	OWNING_DEPOT	SCH_TRIP
OUTBASE NAME	LOCATION_NAME	GEO_LOCATION
JOURNEY REFERENCE	TRIP ID	SCH_TRIP
DRIVER CODE	DRIVER_ID	SCH_TRIP
DRIVER_NAME	FORENAME / SURNAME	RES_PERSON
SECOND MAN CODE	DRIVER_ID2	SCH_TRIP
SECOND MAN NAME	FORENAME / SURNAME	RES_PERSON
ACCOUNT CODE	GROUP NAME	SCH_ORD
ACCOUNT NAME	DESCRIPTION	OMS_SCHED_GROUP
LOCATION_CODE	FROM LOC	SCH_ORD
LOCATION_POSTCODE	POSTCODE	GEO_LOCATION
ORDER REFERENCE	BOOKING CODE	SCH_ORD
ETHOS CODE	EXTERNAL REF	SCH_ORD
ORDER DUE DATE	EARLY_AVAIL	SCH_ORD
ORDER IS COLLECTION	CALCULATED	WHERE FROM LOC = LOAD LOC
FAILURE REASON CODE	REASON CODE	SCH_ORD_NON_CONFORM
FAILURE REASON NAME	DESCRIPTION	SCH_REASON_CODE
AT FAULT PROVIDER CODE	AT_FAULT	SCH_REASON_CODE
AT FAULT PROVIDER NAME

There will be four parameters available, From date and To date, Customer and Cost Centre. The dates will select the relevant trips based on the start time being between the dates entered.

Scope

This change will be applied to system version 10.7.0.

Set-up

Pre-requisites

None

Menu Structure

‘Unchanged’

Data

The new extract will be added to the standing data to allow it to be selected from the standard exports form. The selection criteria for the export will be from and to date, based on the start date of the trip.

A new record will be added to the REP_REPORT table

NAME	BGW_DEBRIEF
REPORT_TYPE	CSV
FILENAME	BGW_DEBRIEF
PROC_NAME	CSV.BGW_DEBRIEF
PRINTER_TYPE	LASER
ORIENTATION	LANDSCAPE

4 new records will be added to the REP_REPORT_PARAM table

REPORT_NAME	BGW_DEBRIEF	BGW_DEBRIEF
REPORT_TYPE	CSV	CSV
PARAM_TYPE	P_START_DATETIME	P_END_DATETIME
PARAM_NAME	P_START_DATETIME	P_END_DATETIME
CONDITIONAL	M	M
DEFAULT_VALUE_TYPE	TODAY	TODAY
PARAM_TITLE	From Date	To Date
REPORT_LIST	N	N

REPORT_NAME	BGW_DEBRIEF	BGW_DEBRIEF
REPORT_TYPE	CSV	CSV
PARAM_TYPE	P_SELECT_LIST1	P_SELECT_LIST2
PARAM_NAME	P_CUSTOMER	P_COST_CENTRE
CONDITIONAL	O	O
DEFAULT_VALUE_TYPE
PARAM_TITLE	Customer	Cost Centre
REPORT_LIST	N	N

2 new fields will be added to the SCH_REASON_CODE table to store the AT_FAULT information. When a reason code is set up, the user will be able to indicate who is responsible for the issue. A code and description field will be available, both values may be left blank.

ALTER TABLE SCH_ REASON­_CODE

ADD COLUMN AT_FAULT VARCHAR2(12);

ADD COLUMN AT_FAULT_DESCRIPTION VARCHAR2(100);

Implementation Advice

A super user will be required to provide access to the extract. Access is granted to user groups in the ACCESS CONTROL screen

Functional Description

An extract will be developed to replace the existing Debriefed Jobs extract and will be available to run from the Exports screen

The user will be prompted to enter from and to date, the values will be used to select trips by the trip start date. Customer and Cost Centre will be optional parameters and will be pre populated with any user parameter values.

The following C-TMS tables will be joined together as appropriate:

The contents of the new extract are listed below, based on the data model displayed on the previous page.

Name	C-TMS field	C-TMS Table
OUTBASE CODE	OWNING_DEPOT	SCH_TRIP
OUTBASE NAME	LOCATION_NAME	GEO_LOCATION
JOURNEY REFERENCE	TRIP ID	SCH_TRIP
DRIVER CODE	DRIVER_ID	SCH_TRIP
DRIVER_NAME	FORENAME	SURNAME	RES_PERSON
SECOND MAN CODE	DRIVER_ID2	SCH_TRIP
SECOND MAN NAME	FORENAME	SURNAME	RES_PERSON
ACCOUNT CODE	GROUP NAME	SCH_ORD
ACCOUNT NAME	DESCRIPTION	OMS_SCHED_GROUP
LOCATION_CODE	FROM LOC	SCH_ORD
LOCATION_POSTCODE	POSTCODE	GEO_LOCATION
ORDER REFERENCE	BOOKING CODE	SCH_ORD
ETHOS CODE	EXTERNAL REF	SCH_ORD
ORDER DUE DATE	EARLY_AVAIL	SCH_ORD
ORDER IS COLLECTION	CALCULATED	WHERE FROM LOC = LOAD LOC
FAILURE REASON CODE	REASON CODE	SCH_ORD_NON_CONFORM
FAILURE REASON NAME	DESCRIPTION	SCH_REASON_CODE
AT FAULT PROVIDER CODE	AT_FAULT	SCH_REASON_CODE
AT FAULT PROVIDER NAME	AT_FAULT_DESCRIPTION	SCH_REASON_CODE
ENGINEER	SCH_ORD_REFERENCE	SUB_REF_VALUE where SUB_REF_NAME =’ENGINEER’

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.

Two new fields will be added to the reason code static data table (SCH_REASON_CODE) to indicate who is at fault for specific reason codes. The data will be maintained using the Business Data Management screen:

Table Updates Required

ALTER TABLE SCH_ REASON­_CODE

ADD COLUMN AT_FAULT VARCHAR2(12);

ADD COLUMN AT_FAULT_DESCRIPTION VARCHAR2(100);

References

Ref No	Document Title & ID	Version	Date
1	EST-291172 AR-8KNKVZ CSV extract of order and trip info v1.0	1.0	30/08/2011

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	30/08/2011	Draft	Initial version	SEW
0.2	31/08/2011	Draft	Reviewed	MJC
0.3	31/08/2011	Issue	Amended and Issued	SEW
1.0	31/08/2011	Issue	Amended and Issued	MJC
1.1	07/09/2011	Revised	Amended following referral –client add req	SEW
2.0	09/09/2011	Issue	Reviewed and Re-issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager