DuttonT: New page: {{Doc_Title|System=FUNCTIONAL SPECIFICATION|Title=Additional Trigger of TRP Message|Reference=FS 287345 NA-8F5HZ6|Version=1.0|Date=18/05/2011|Sysver=10.6|Client=DHL C-TMS}} = Functional...

2012-03-30T09:13:44Z

New page: {{Doc_Title|System=FUNCTIONAL SPECIFICATION|Title=Additional Trigger of TRP Message|Reference=FS 287345 NA-8F5HZ6|Version=1.0|Date=18/05/2011|Sysver=10.6|Client=DHL C-TMS}} = Functional...

New page

{{Doc_Title|System=FUNCTIONAL SPECIFICATION|Title=Additional Trigger of TRP Message|Reference=FS 287345 NA-8F5HZ6|Version=1.0|Date=18/05/2011|Sysver=10.6|Client=DHL C-TMS}}

= Functional Overview =
== Client Requirement ==
Currently a TRP message is only triggered when a Trip is moved from Planned to Accepted.

After this point, if planners make amendments to the order delivery date/time window but not move the trip between planned/accepted then the time Unilever are measuring us against does not match the actual time in C-TMS.

Can we add another trigger of the TRP message to trigger when the Trip is in an Accepted state and the order delivery date/time window is changed.

== Solution ==

Once an order is scheduled on a trip, changes to that order do not trigger a ‘TRP’ message. The ‘TRP’ message will only be triggered by changes to the trip.

A new trigger will be added to the ‘SCH_ORD’ table that will fire when the order windows are changed, and the order is at a status of ‘SCHED_COLL’ or ‘SCHEDULED’ (i.e. scheduled on a trip).

The trigger will only fire for orders that have a customer and from location set up to produce EDI messages. The order’s customer and from location will be compared to records on table ‘EDI_PROCESS_HEADER’.

If a record is found then the trigger will write a record to the table ‘INT_XML_CONTROL’ containing the following information:

* External System– ‘EDI_PROCESS_HEADER.PROCESS_NAME’ where the customer and location match the order.
* Event Type– ‘TRP’ to indicate the type of message being sent.
* Trip Schedule– ‘SCH_TRIP.SCHED_NAME’ for the trip which the order is on.
* Trip ID– ‘SCH_TRIP.TRIP_ID’ for the trip which the order is on
* Trip Status– ‘SCH_TRIP.TRIP_STATUS’ for the trip which the order is on.
* Processed– ‘N’

The Trip for the Order will be a trip that visits the location ID for the EDI process and on which the Order is being loaded.

The record in ‘INT_XML_CONTROL’ will be processed by the existing job for the EDI flow.

N.B. the current ‘UNILEVER’ and ‘UNIICE’ outbound flows, along with the new Unilever Ireland flow are being moved to the generic outbound flow as part of RIO NA-8F5HA6. This estimate is based on the changes for RIO NA-8F5HA6 being applied and the flows using ‘EDI_PROCESS_HEADER’.

== Scope ==
This change will be applied to system version 10.6.

= Set-up =
== Pre-requisites ==
None

== Menu Structure ==
‘Unchanged’

== Data ==
‘Unchanged’

= Functional Description =

Current functionality is as follows:

Once an order is scheduled on a trip, changes to that order do not trigger a ‘TRP’ message. The ‘TRP’ message is triggered by changes (add/remove orders, changes to stop times) to the trip when the trips status is at a status specified in the trigger value set up in the EDI maintenance form.

Proposed new functionality:

A new trigger will be added to the ‘SCH_ORD’ table that will fire when the order delivery date and time windows are changed, and the order is at a status of ‘SCHED_COLL’ or ‘SCHEDULED’ (i.e. scheduled on a trip).

The columns on the ‘SCH_ORD’ table to check when updating will be ‘EARLY_DEL’ and ‘LATE_DEL’.

The trigger will be called ‘TRG_SCH_ORD_DEL_TRP’ and will only fire for orders that have a customer and ‘from’ location set up to produce EDI messages (i.e. records that exist on table ‘EDI_PROCESS_HEADER’) an additional check will be made on the EDI_TRIGGERS table to ensure that the new SO_DEL_DATE_CHANGE value is set to ‘Y’

If a record is found then the trigger will write a record to the table ‘INT_XML_CONTROL’ containing the following information:

{| style="border-spacing:0;"
| style="background-color:#ffff99;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| '''Column'''
| style="background-color:#ffff99;border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| '''Value'''

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| EXTERNAL_SYSTEM
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Data from ‘EDI_PROCESS_HEADER.PROCESS_NAME’ where the customer and location match those of the order

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| EVENT_TYPE
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| ‘TRP’

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| TRIP_SCHED
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Data from ‘SCH_TRIP.SCHED_NAME’ for the trip on which the order is assigned

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| TRIP_ID
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Data from ‘SCH_TRIP.TRIP_ID’ for the trip on which the order is assigned

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| TRIP_STATUS
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Data from ‘SCH_TRIP.TRIP_STATUS’ for the trip on which the order is assigned

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| PROCESSED
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| ‘N’

|}
The data will be stored in the ‘Edi’ tab page of the ‘Import’ screen and used to select the following data:

{| style="border-spacing:0;"
| style="background-color:#ffff99;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| '''Field'''
| style="background-color:#ffff99;border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| '''Column'''

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Process Name
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| INT_XML_CONTROL.EXTERNAL_SYSTEM

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Customer
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| SCH_ORD.CUSTOMER

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Location
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| SCH_ORD.FROM_LOC

|}

[[Image:287345_1.png]]

The trip for the order will be a trip on which the order is being loaded at a specific location (i.e. has a haulage activity of ‘Load’ at a trip stop).

For example, the customer may be ‘UNIICE’ and the location may be ‘DHLUNGLO’, or the customer may be ‘UNILEVER’ and the location may be ‘DHLUNWTK’.

The record in ‘INT_XML_CONTROL’ will be processed by the existing database job generated when the EDI flow is started.

N.B. The current ‘UNILEVER’ and ‘UNIICE’ outbound flows, along with the new ‘Unilever Ireland’, flow should exist in the generic outbound flow as developed for RIO ‘NA-8F5HA6’ and this development must be installed first.

'''References'''

{| style="border-spacing:0;"
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Ref No'''</center>
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Document Title & ID'''</center>
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Version'''</center>
| style="background-color:#c0c0c0;border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Date'''</center>

|-
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>1</center>
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| EST-287345 NA-8F5HZ6 Additional Trigger of TRP Message v1.0.doc
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>1.0</center>
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>07/04/11</center>

|-
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|

|}

'''Glossary'''

{| style="border-spacing:0;"
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Term or Acronym'''</center>
| style="background-color:#c0c0c0;border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Meaning'''</center>

|-
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| C-TMS
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Calidus TMS

|-
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|

|-
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:none;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|

|}

'''Document History'''

{| style="border-spacing:0;"
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Version'''</center>
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Date'''</center>
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Status'''</center>
| style="background-color:#c0c0c0;border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Reason'''</center>
| style="background-color:#c0c0c0;border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>'''Initials'''</center>

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>0.1</center>
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>06/05/11</center>
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>Draft</center>
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Initial version
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>PDR</center>

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>1.0</center>
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>18/05/11</center>
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>Issue</center>
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| Reviewed and Issue
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"| <center>MJC</center>

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|

|-
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border-top:0.018cm solid #000000;border-bottom:0.018cm solid #000000;border-left:0.018cm solid #000000;border-right:none;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|
| style="border:0.018cm solid #000000;padding-top:0cm;padding-bottom:0cm;padding-left:0.191cm;padding-right:0.191cm;"|

|}

=AUTHORISED BY=
{| Border="1"
| '''''Matt Crisford'''''
| Development Manager
|
|-
| '''''Peter Greer'''''
| TMSCC MTS Product Manager
|
|}

287345 - Revision history

DuttonT: New page: {{Doc_Title|System=FUNCTIONAL SPECIFICATION|Title=Additional Trigger of TRP Message|Reference=FS 287345 NA-8F5HZ6|Version=1.0|Date=18/05/2011|Sysver=10.6|Client=DHL C-TMS}} = Functional...