286645 (PM-8ELMCL) - (Integration of DESP1 to GOLD)

DHL MTS

Integration of DESP1 to GOLD

FUNCTIONAL SPECIFICATION - 10.6

28/03/2011 - 1.0
Reference: 286645 PM-8ELMCL

Client Requirement

Change Request Summary:

Integration DESP1Paul McGoran/Manchester/UK/NFC

Change Request Details:

DESP1 C-TMS to GOLD. Order based milestone tracking events. Order lifecycle key status changes interfaced to GOLD.

Benefits identified as a result of the change:

Order status available to customer systems

Solution

This new flow will be an outbound flow to GOLD at specific STATUS changes, based on the generic xml code.

The triggers for the outbound flow are listed below

ORDERED	Order has been created and passed C-TMS validation, assigned a status of UNSCHEDULED
PLANNED	Order status has changed to SCHEDULED
ACCEPTED	Trip status has changed to ACCEPTED
ENROUTE	Trip status changes to EN-ROUTE
POD	POD flag on the order is updated to Y
DELIVERED	Status of the order changes to DELIVERED
CANCELLED	Status of the order changes to CANCELLED
REBOOKED	Order is rebooked, the rebooked order is set to UNSCHEDULED.
ABORTED	Order status is set to ABORTED

The data required by GOLD is listed below

EXTERNAL_REF

OMS_REF

CARRIER_ID

STATUS

TRIP_ID

VEHICLE_ID (OWN FLEET)

CARRIER_TYPE

The existing table trigger on SCH_TRIP (TRG_SCH_TRIP_XML_INT) will be amended to insert a new record into the INT_XML_CONTROL table. The data required for the new record is

INT_XML_SEQ generated from the existing sequence SEQ_INT_XML

EXTERNAL_SYSTEM GOLDDESP1

TRIP_SCHED

TRIP_ID

TRIP_STATUS

The existing generic process will be used (GEN_TRIP_XML). The records inserted into INT_XML_CONTROL table will be processed based on standard EDI polling frequency options.

A field will be added to the EDI_PROCESS_HEADER table called FORMAT_NAME.

In the generic procedure GEN_TRIP_XML, the global variable g_proc_type is set to GEN, this will be amended so that g_proc_type is set to the value of the new field FORMAT_NAME. An check will ensure that if FORMAT_NAME is null, g_proc_type is set to GEN.

The EDI tab on the IMPORT MAINTENANCE screen will be used to create the database job. On the screen, the user will be able to declare the parameters for the flow including outbound folders, frequency and filename format.

The current generic code processes all of the required data excluding CARRIER_TYPE. This has been added to the latest version of the XSD and code will be added to the generic xml procedure GEN_TRIP_DET to accommodate CARRIER_TYPE optionally so as not to affect existing users of the standard flows.

The vehicle id is only passed in the outbound file if the vehicle is own fleet. This will be determined by comparing the owning depot of the trip with the hub location of the Carrier. If the owning depot and hub location are the same, then the trip is own fleet and the vehicle id will be sent. This will be controlled using the global variable g_proc_type

Data which is not required for the flow will be excluded based on the value of g_proc_name. This will be all fields excluding the required fields plus any mandatory fields. Mandatory fields can be identified from the XSD and will be detailed in the functional spec.

Scope

This change will be applied to system version 10.6

Set-up

Pre-requisites

None

Menu Structure

Unchanged

Functional Description

The XSD has been amended with a new level under EVENT_HEADER called MILESTONE. To populate the new level for outbound flows, 5 new procedures will be created within the INT_XML_OUT2 package:

GEN_MILESTONE_EVENT

GEN_MILESTONE_EVENT_END

GEN_MILESTONE_START

GEN_MILESTONE_END

PROCESS_MIL_XML_OUT

The following data is available at the MILESTONE level

MST_DATE	SYSDATE
MST_STATUS	POPULATED BY TRIGGER
MST_COMMENTS	POPULATED BY TRIGGER
SO_REF	DERIVED FROM ORDER
TMS_REF	DERIVED FROM INTERFACE TABLE
BOOK_REF	DERIVED FROM ORDER
BOOK_DATE	DERIVED FROM ORDER
WB_TICKET	DERIVED FROM ORDER
WEIGHT	DERIVED FROM ORDER
VOLUME	DERIVED FROM ORDER
FOOTPRINT	DERIVED FROM ORDER
RPE_QTY	DERIVED FROM ORDER
REVENUE	DERIVED FROM ORDER
TRIP_REF	DERIVED FROM SCHEDULED ORDER
ROUTE_CODE	DERIVED FROM SCHEDULED ORDER
HAULIER	DERIVED FROM SCHEDULED ORDER
HAULIER_TYPE	DERIVED FROM SCHEDULED ORDER
TRACTOR	DERIVED FROM SCHEDULED ORDER
TRAILER_ID	DERIVED FROM SCHEDULED ORDER
DRIVER	DERIVED FROM SCHEDULED ORDER
FROM_NAME	DERIVED FROM ORDER
FROM_TOWN	DERIVED FROM ORDER
FROM_POSTCODE	DERIVED FROM ORDER
TO_NAME	DERIVED FROM ORDER
TO_TOWN	DERIVED FROM ORDER
TO_POSTCODE	DERIVED FROM ORDER
LOAD_AT_NAME	DERIVED FROM SCHEDULED ORDER
UNLOAD_AT_NAME	DERIVED FROM SCHEDULED ORDER
DELIVERED_DATE	DERIVED FROM ACTUAL STOP ARRIVE

Milestone triggers have been identified as the following events

ORDERED	Order has been created and passed C-TMS validation, assigned a status of UNSCHEDULED
PLANNED	Order status has changed to SCHEDULED
ACCEPTED	Trip status has changed to ACCEPTED
ENROUTE	Trip status changes to EN-ROUTE
POD	POD flag on the order is updated to Y
DELIVERED	Status of the order changes to DELIVERED
CANCELLED	Status of the order changes to CANCELLED
REBOOKED	Order is rebooked, the rebooked order is set to UNSCHEDULED.
ABORTED	Order status is set to ABORTED

At each of the trigger events above a new record will be added to the table INT_XML_CONTROL. Two new fields will be added to the INT_XML_CONTROL table called MILESTONE_COMMENTS and MILESTONE status.

The external system will be set to GOLD and the EVENT_TYPE will be set to ‘MIL’. For trip based events, the trip information(TRIP_ID, STATUS, TRIP_SCHED) will be populated. At trip events, one milestone record in INT_XML_CONTROL will generate outbound records for every order on the trip.

For order based events, the order information (OMS_SCHED, OMS_REF, OMS_STATUS) will be populated. When only order information is received for a MIL record, a trip will still be looked for

Each trigger will populate the MILESTONE_COMMENTS and MILESTONE_STATUS will specific values linked to the trigger.

Trigger	Milestone status	Milestone comments
UNSCHEDULED	ORDERED	Order successfully loaded in CTMS
SCHEDULED	PLANNED	Order successfully planned on a trip
ACCEPTED	ACCEPTED	Delivery trip Accepted
ENROUTE	ENROUTE	Delivery trip En route
POD	POD RECEIVED	POD has been received for order
DELIVERED	DELIVERED	Order has been delivered
CANCELLED	CANCELLED	Order has been cancelled
REBOOKED	REBOOKED	Order has been rebooked
ABORTED	ABORTED	Order has been aborted

The existing generic xml status trigger on SCH_TRIP (TRG_SCH_TRIP_XML_INT) will be amended to process records with an event type MIL, when the flow type is MILE_XML. The status changes which create the records will be listed and amended by selecting the PARAMS command button on the EDI screen.

A new generic xml status trigger will be created for Orders. The trigger will be based on the SCH_ORD table and will created records in INT_XML_CONTROL when status changes and the POD flag is set.

A new flow type will be created called MILE_XML which will run the new procedure INT_XML_OUT2.PROCESS_MIL_XML_OUT. Within this procedure a query will select all unprocessed records from INT_XML_CONTROL with an EVENT_TYPE ‘MIL’.

Selecting the PARAMS button will allow the user to determine which triggers will create a record in the INT_XML_CONTROL table and the generic xml triggers on SCH_ORD and SCH_TRIP will create the records for the specified events.

The values which a user can assign to a type T record will be validated to ensure the status exists in the trigger code on the trip or order tables.

Table Updates Required

ALTER TABLE INT_XML_CONTROL

ADD MILESTONE_COMMENTS VARCHAR2 (500);

ADD MILESTONE_STATUS VARCHAR2(50);

References

Ref No	Document Title & ID	Version	Date
	EST 286645 PM-8ELMCL Integration of DESP1 to GOLD v1.0	1.0

Document History

Version	Date	Status	Reason	Initials
0.1	23/03/2011	Draft	Initial version	SW
1.0	28/03/2011	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager