291807

DHL C-TMS

EFX Flows

FUNCTIONAL SPECIFICATION - 10.7

18/10/2011 - 1.0
Reference: 291807 TH-8LFCN3

Contents

Client Requirement

Change Request Summary:

Development of new export CSV file to the EFX format to allow trip data with carrier cost to be manually uploaded into EFX.

Change Request Details:

Modification, Mapping consultation, implementation and UAT support of the new export CSV file to the EFX format to allow trip data with carrier cost to be manually uploaded into EFX. To Include;

Mapping workshop

interface build

C-TMS setup

testing support

implementation to live.

Benefits identified as a result of the change:

ETHOS migration

Solution

A new export function will be created that will send trip details to the EFX system.

The user will be able to enter the ‘Export’ screen and select data for the following parameters:

Schedule From

Schedule To

Trip Status (expected to be either ‘En-Route’, ‘Confirmed’ or ‘Completed’)

Cost Centre Code

Customer Group

The export will produce a comma-delimited file to match the EFX Import format.

As part of this change OBSL will create the possibility to link the export record definition to the ‘Edi Maintenance’ screen so that the export can be run nightly for example. An implementer will then be able to define the polling frequency (i.e. Daily at 00:30) and the ftp destination details. Note that it is expected that the process will be defined to run once per day since all trips for the defined schedule(s) will be sent to EFX in each file.

Scope

This change will be applied to system version 10.7.0

Set-up

Pre-requisites

N/A

Menu Structure

N/A

Data

A new record will be added to the REP_REPORT table to define the export:

Field Name	Value
Name	BGP_EFX_Export’
Report_Type	‘CSV’
Filename	‘GYP_EFX_Export.csv’
Proc_Name	‘DP_CSV5.Gyp_EFX_Export.csv’
Printer_type	‘Laser’
Orientation	‘Landscape’

5 new records will be added to the REP_REPORT_PARAMS table.

Report Name	GYP_EFX_EXPORT	GYP_EFX_EXPORT	GYP_EFX_EXPORT	GYP_EFX_EXPORT
Report Type	CSV	CSV	CSV	CSV
Param Type	PG_SELECT_LIST1/ PG_SELECT_LIST2	PG_SELECT_LIST3	PG_SELECT_LIST4	PG_SELECT_LIST5
Param Name	P_START_SCHED/P_END_SCHED	I_STATUS	I_COST_CENTRE	I_CUST_GRP
Conditional	M	O	O	O
Param Title	From Schedule/To Schedule	Trip Status	Cost Centre	Customer Group
SQL_String		Select ‘En-route’ ‘Confirmed’, ‘Completed’ from dual	Select distinct (cost_centre_name)From rev_cost_centre	Select distinct (cust_group) from Org_customer_group

Implementation Advice

A super user will be required to provide access to the export to the relevant groups. Access is provided using the Access Control screen, by highlighting the extract then pressing CTRL and clicking in the selected record.

Functional Description

Extract Definition

A new export will be available in the Exports screen to run a CSV export. The export will be based on the existing extract to EFX, allowing users to select from the following parameters:

Parameter	Source Field
SCHEDULE_FROM	SCH_TRIP.SCHED_NAME
SCHEDULE_TO	SCH_TRIP.SCHED_NAME
TRIP_STATUS	SCH_TRIP.TRIP_STATUS
COST_CENTRE_CODE	SCH_TRIP.COST_CENTRE
CUSTOMER_GROUP	SCH_ORD.GROUP_NAME

Data selected in the extract is listed below. If running the extract from the EXPORT screen, users will be forced to populate the SCHEDULE_FROM and SCHEDULE_TO parameters, the other parameters may be left null.

Two new procedures (GYP_EFX_Extract and Write_GYP_EFX_Extract) will be added to DP_CSV5 , using the standard coding for selecting and running CSV extracts.

COLUMN NAME	SOURCE	EXAMPLE
Load Ref	External Reference of the Earliest order on the trip	0083798325
Load From	First loading location on this trip	East Leake (BG)
Load PC	First two characters of Postcode at load point	LE
Loading Area	Blank field
Load Start Date	Date of trip	19/08/2011
Load Start Time	Load time of first load point.	0400
Load End Date	Date of trip	19/08/2011
Load End Time	Depart time of first load point.	0430
Pallets	Number of pallets for the whole load	2
Pallet Type	Blank
Weight	Total weight loaded for this trip in the unit of measure.	24.839
Units	Unit of measure	Tonnes
Rate	Total cost of this trip	225
Deadline Date	Load start date	19/08/2011
Deadline Time	1 hour before load start time but not on the day before	0300
Vehicle Restrictions	Blank
Pref Site	Decode from carrier code	1515
Product Restrictions	Blank
Drop 1 PC	First two characters of Postcode at stop 2 on this trip	NG
Drop 1 Area	Town for stop 2	Newark On Trent
Drop 1 Name	Location name for stop 2	TRAVIS PERKINS NEWARK
Drop 1 Date	Arrival date for stop 2	19/08/2011
Drop 1 Time	Arrival time for stop 2	0800
Drop 2 Name	Location name for stop 3
Drop 2 Date	Arrival date for stop 3
Drop 2 Time	Arrival time for stop 3
…….	……….
Drop 9 Name	Location name for stop 10
Drop 9 Date	Arrival date for stop 10
Drop 9 Time	Arrival time for stop 10
Comments	Blank
Subcontractor	Site/ carrier name	RITCHIE TRANSPORT
EFX Vendor Number	Decode from carrier set-up blank for internal carriers	70736
Subcontract Fax	Decode from carrier set-up	01664 480951
Subcontract Email	Decode from carrier set-up	[email protected]
Subcontract Rate	Cost on the trip (external contractor only)	225
Pref Accept After Deadline	Default to Y	Y

Automation

The new extract will be available to run as an automated job which can be defined and customised in the EDI Maintenance screen

The flow type must be set to EXPORT and this will display the Report_Values tab in the parameters pop up screen. In the report values tab, the super user should define the report procedure and any required report parameters which will not be left null.

Developer Notes: To find out the name of the process and the parameters, select the export to run from the export screen and copy the string that is generated eg DP_CSV2.HAULIER_TRIP_SHEET( '/u03/webint/mtstst/export', 'HAULIER_TRIP_SHEET_2011-10-17_141046.csv', i_carrier=> , i_group_name=> , i_sched=> '100529', i_load_loc=> , i_depot=> , i_trip_id=> );

The super user will be responsible for defining when and how often the job should run. The super user will also be responsible for determining the values of the FROM_SCHED and TO_SCHED parameters. SYSSCHED is an existing parameter which has been defined as the system date converted to a schedule. The super user can define which schedules should be included each time the export is run.

SCHEDULE	PARAMETER VALUE
TODAY	SYSSCHED
TOMORROW	SYSSCHED+1
YESTERDAY	SYSSCHED-1

The super user will be required to seek advice from OBSL to establish the export name and the parameter names. It would be expected that the cost centre parameter is set to the name of the GYPSUM cost centre.

Table Updates Required

No table changes are required for this development

Modules to be changed

Module Name	Module Type	Notes
DP_CSV5	CSV Extract Package	Create 2 new procedures to generate the extract

References

Ref No	Document Title & ID	Version	Date
1	EST- 291807 - TH-8LFCN3 - EFX Flows - v1.0	1.0	27/09/2011

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	17/10/2011	Draft	Initial version	SEW
0.2	18/10/2011	Draft	Reviewed	MJC
1.0	18/10/2011	Issue	Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager