293561

DHL C-TMS

Drop Level Debrief Info to LOTS

FUNCTIONAL SPECIFICATION - 10.7

20/01/12 - 2.0
Reference: FS 293561 ID-8N3BT7

FUNCTIONAL OVERVIEW

Client Requirement

This document is a business requirement to enable drop-level debriefing information to be passed from the main C-TMS transport management system into the C-TMS LOTS web portal.

Currently a drop can be debriefed across multiple systems (C-TMS, LOTS, Microlise, IVR), but only those from LOTS, Microlise and IVR are actually stored inside LOTS as debrief events.

Any site-based debrief undertaken inside C-TMS is not currently reflected inside LOTS.

Please refer to the attached MS work document that is the business requirement specification for this work.

Note: This is the first of several changes needed to enhance the debriefing and LOTS visibility health needed by the One Network transportation sites.

Solution

A change will be made within C-TMS to generate an outbound XML file for ‘LOTS’ for certain events at the trip debriefing stage.

The trigger points will be the entry of the following information against the trip stops and transport orders:

• Trip Stop Actual Arrival Time
• Trip Stop Actual Departure Time
• Order Delivered Quantity
• Order Late Reason Code
• Order Non-conformance Reason Code
• Order Item Non-conformance Reason Code

It is expected that the user will enter the trip debriefing information but not confirm the trip as fully debriefed.

Therefore, when the ‘Save’ button is pressed the trip will be assessed to determine whether an outbound XML file need be generated and sent to ‘LOTS’ because data has been changed for the trip stops that contain transport orders for customers with ‘LOTS’ installed.

The outbound XML file will be produced to the lowest level for those transport orders present on the trip stops updated. The trip, stop, transport order, item and non-conformance information will be sent to ‘LOTS’.

N.B. A transport order will only be included if ‘LOTS’ is enabled for its customer.

Scope

This change will be applied to system version 10.7.0.

FUNCTIONAL DESCRIPTION

User Context

To ensure that the new ‘LOTS’ event messages are generated only by processing within the ‘Trip Debrief’ screen, the user context for the current session will be set to ‘LOTS_TRIP_DEBRIEF’ via form triggers when the user enters the screen. The user context will then be removed when the user exits the screen.

The user will then be able to process as required and not generate the new ‘LOTS’ event messages when the relevant data is updated by other methods.

The user context may be set by the call:

USER_CONTEXT_PKG.SET_CONTEXT ('LOTS_TRIP_DEBRIEF', 'Y')

The user context may be removed by the call:

USER_CONTEXT_PKG.SET_CONTEXT ('LOTS_TRIP_DEBRIEF', 'N')

The check for the user context in the database triggers may be made by the call:

IF NVL(SYS_CONTEXT('USER_CONTEXT', 'LOTS_TRIP_DEBRIEF'), 'N') = 'Y'

Trip Debrief Screen

The ‘Trip Debrief’ screen (i.e. the ‘TRIPDTL’ form) will be changed to include new functionality for ‘LOTS’ behind the ‘Save’ button in the ‘Order Debrief’ tab page.

The delivery quantity (‘Actual Deliver’), actual arrival time (‘Actual Arr.’), actual departure time (‘Actual Dep.’), late order reason code (via ‘Add Time Reason(s)’) and order non-conformance reason code (via ‘Non Conformance’) trigger points are highlighted in the ‘Order Debrief’ tab page.

The item non-conformance reason code (via ‘Non Conformance’) trigger point is highlighted in the ‘Order Items’ tab page.

The new functionality will only be effective should the trip contain a transport order for a customer that has ‘LOTS’ installed (i.e. a ‘LOTS Owner’ exists in the ‘Customers’ tab page in the ‘Customers’ screen):

It is expected that the user will enter the trip debriefing information but may not confirm the trip as fully debriefed.

Therefore, when the ‘Save’ button is pressed the trip will be assessed via database triggers to determine whether an outbound XML file needs to be generated and sent to ‘LOTS’ because data has been changed for the trip stops that contain transport orders for customers with ‘LOTS’ installed.

The data assessed will be:

• Actual Arrival Time (at the trip stop)
• Actual Departure Time (at the trip stop)
• Delivered Quantity (of the transport order)
• Late Reason Code (of the transport order)
• Non-conformance Reason Code (of the transport order)
• Non-conformance Reason Code (of the transport order item)

N.B. It is expected that the ‘Delivered Quantity’ may be entered at a cross-dock location for a trans-shipment trip and for the transport order at its final delivery stop. The type of trip can be determined within ‘LOTS’.

Event Triggers

The existing database triggers ‘TRG_STS_XML_OUT’, ‘TRG_SOL_XML’, ‘TRG_SCH_ORD_NON_CONF_XML_INT’ and ‘TRG_SCH_ORD_ITEMS_REASONS’ will be changed to assess whether there is an existing interface control record waiting to be processed for the appropriate event type: if there is not then a new control record will be created from which the XML file for ‘LOTS’ will be generated, otherwise the existing unprocessed control record will generate the file when it is processed by an existing database job.

A new database trigger called ‘TRG_SOLR_XML_INT’ will be created on the ‘SCH_ORD_LATE_REASON’ database table and it will perform the same checks described above for the ‘TRG_SCH_ORD_NON_CONF_XML_INT’ database trigger.

Database trigger ‘TRG_STS_XML_OUT’ will generate an event type of ‘ARR’ or ‘DEP’ for the trip stops and database triggers ‘TRG_SOL_XML’, ‘TRG_SOLR_XML_INT’, ‘TRG_SCH_ORD_NON_CONF_XML_INT’ and ‘TRG_SCH_ORD_ITEMS_REASONS’ will generate an event type of ‘DEL’ for the transport orders and items.

The ‘TRG_STS_XML_OUT’ database trigger will assess the data for action ‘UPDATE’ as follows:

The ‘TRG_SOL_XML’, ‘TRG_SCH_ORD_NON_CONF_XML_INT’ and ‘TRG_SCH_ORD_ITEMS_REASONS’ database triggers will assess the data for actions ‘INSERT’, ‘UPDATE’ and ‘DELETE’ as follows:

The following values will be used to check for the presence of the interface control record:

The stop ID of the trip stop will need to be stored so that messages may be generated for an individual trip stop.

The OMS reference of the transport order will need to be stored so that messages may be generated for an individual transport order on the trip.

It is expected that the transport order will only be included once on a trip (i.e. for ‘Load’ and ‘Unload’ activities), therefore the transport order itself may be checked on the trip regardless of the trip stop.

The ‘PROCESSED’ flag indicates whether an XML file has been generated for the control record: if it is ‘Y’, or a record does not exist for the trip and stop, then a new control record may be written.

The event types will be:

N.B. It will be possible for the user to create another file via the ‘Save’ button if a change has been made to a transport order for a customer with ‘LOTS’ installed, or a trip stop with such a transport order, and a previous file has been processed or not generated, for the appropriate level.

Therefore, the user will be able to change the relevant data multiple times and send a trip debrief file to inform ‘LOTS’ multiple times.

The interface control record will be on the ‘INT_XML_CONTROL’ database table with the following values:

The new event types will indicate that the trip has been partially debriefed and the process to generate the file will be changed to account for this new event type.

TripOrder XSD

The ‘TripOrder’ XSD file format will be used to send and receive ‘LOTS’ messages and the current active version is v2.10.

A new version v2.26 will be created to enable the non-conformance and late reason codes to be sent to LOTS for transport orders at a trip stop.

The <STOP_REASON_CODES> section will be retained but disused for inbound LOTS messages: currently this sections stores the late reason codes stored for a trip stop as advised by LOTS.

A new <ORDER_REASON_CODES> section will be introduced to replace <STOP_REASON_CODES> for inbound LOTS messages and to be used for outbound LOTS messages.

The new <ORDER_REASON_CODES> section will contain the same items as the <STOP_REASON_CODES> section but will be placed in the <ORDERS> section.

The structure of the <ORDER_REASON_CODES> section will be:

The <STOP_REASON_CODES> section may be seen below:

The <ORDER_REASON_CODES> section will be added to the <ORDERS> section after the <ORDER_HAZARDOUS_DETAILS> section as may be seen below:

The existing <REASON_CODES> section will be changed to include the existing <RC_TYPE> item that is contained in the <STOP_REASON_CODES> after the <RC_CASE> item:

The inclusion of the <RC_TYPE> item in the existing <REASON_CODES> section will enable the same section to be used for different reason codes in the future.

Outbound LOTS XML File Processing

The outbound XML file will be produced to the lowest level for those transport orders present on the trip stops updated. The trip, stop, transport order, item and non-conformance information will be sent to ‘LOTS’.

N.B. A transport order will only be included if ‘LOTS’ is installed for its customer.

The ‘INT_XML_OUT2’ package will be changed to process the new ‘ARR’, ‘DEP’ and ‘DEL’ event types from the interface control records.

The ‘PROCESS_XML_OUTBOUND_LOTS’ procedure will search for event types of ‘ARR’, ‘DEP’ or ‘DEL’ and generate XML files for ‘LOTS’ at the required level of the trip:

‘ARR’ / ‘DEP’:

• Trip Header
• Trip Detail
• Stop Header
• Stop Detail

N.B. Only the trip stop recorded by the database trigger will be included.

‘DEL’:

• Trip Header
• Trip Detail
• Stop Header
• Stop Detail
• Order Header
• Order Detail
• Reason Codes

N.B. Only the transport order recorded by the database trigger will be included, the trip stop will be obtained for the transport order stored in the control record.

N.B. A file will be created for each trigger event so that any changes made for a specific trip stop or a specific transport order will be included at that level: this is because only transport orders that have been debriefed should be updated in LOTS, therefore any other transport orders on the same trip stop will be included in a separate file when they are updated.

The filename will be changed to include the event type as follows:

‘TMS_LOTS_{DATABASE}_{EVENT_TYPE}_{SYSDATE}{SYSTIME}’

N.B. The EDI trigger ‘INCLUDE_ITEM_DELIVER’ will control whether the <DELIVERED> item is populated in the <ORDER_DETAIL> section of the file: if it is set to ‘Y’ then the actual delivered quantity of the transport order despatch unit, or stock item, will be included.

N.B. The EDI trigger ‘INCLUDE_REASON_CODES’ will control whether the <REASON_CODES> section of the file is included: if it is set to ‘Y’ then the section will be included for the transport order despatch unit, or stock item, should a late reason code exist for the transport order despatch unit or a reason code exist for the stock item.

The <ORDER_REASON_CODES> section will now include the late reason codes for the transport order in the outbound LOTS message: the <RC_TYPE> item will contain the text ‘LATE’ to indicate the type of reason code provided.

The <ORDER_REASON_CODES> section will also include the non-conformance reason codes for the transport order in the outbound LOTS message: the <RC_TYPE> item will contain the text ‘NONC’ to indicate the type of reason code provided.

A new system parameter called ‘LOTS_NONC_REASONS’ will be created to indicate where the non-conformance reason codes for the transport order will be contained in the outbound LOTS messages: the description of the system parameter will be ‘Include the non-conformance reason codes for the transport order at the despatch unit level (Y/N)’.

If the system parameter is set to ‘Y’ then the non-conformance reason codes for the transport order will not be included in the <ORDER_REASON_CODES> section but included in the <REASON_CODES> section for the despatch unit in addition to any specific non-conformance reason codes for the despatch unit and product type.

If the system parameter is set to ‘N’, or not set, then the non-conformance reason codes for the transport order will only be included in the <ORDER_REASON_CODES> section as standard.

Therefore, the <REASON_CODES> section will only include the non-conformance reason codes for the despatch unit or stock item level.

The new <RC_TYPE> item will be set to ‘NONC’ for the non-conformance reason codes.

REFERENCES

Ref No	Document Title & ID	Version	Date
1	EST-293561 ID-8N3BT7 Drop Level Debrief Info to LOTS v1.0.doc	1.0	10/11/11

DOCUMENT HISTORY

Version	Date	Status	Reason	Initials
0.1	12/12/11	Draft	Initial version	PDR
0.1	14/12/11	Draft	Review of the initial version	PJH
1.0	14/12/11	Issue	Issued to client	PJH
1.1	06/01/12	Draft	Changes to run the new ‘LOTS’ functionality via the ‘Save’ button.	PDR
1.1	06/01/12	Draft	Review of latest specification	PJH
2.0	06/01/12	Issue	Issued to client	PJH

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager