274132
274132 - MW-82GEUD / Tesco PO Trip
Copyright OBS Logistics © 2010
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
FUNCTIONAL OVERVIEW
Client Requirement
Tesco require further development / information holding within MTS and sending via EDI to their own system.
New development of screens and functionality within the Trip screens
Solution
A new version of the xsd (MTSPurchaseOrder version 1.8) will be created (see Purchase Orders estimate for details – 274129 and Appendix A).
Additional tags required for this development are :-
Add to TRIP_TRACKING section :-
| FROM_MOTHER_FEEDER | VARCHAR2(1) |
| FROM_VESSEL | VARCHAR2(12) |
| FROM_VOYAGE | VARCHAR2(12) |
| TO_MOTHER_FEEDER | VARCHAR2(1) |
| TO_VESSEL | VARCHAR2(12) |
| TO_VOYAGE | VARCHAR2(12) |
| TRANSPORT_MODE | VARCHAR2(12) |
| SWB | VARCHAR2(12) |
<br\> <br\> <br\> <br\> <br\> <br\> <br\> <br\> <br\> <br\> <br\>
Add to CROSS_DOCK section :-
| MOTHER_FEEDER | VARCHAR2(1) |
| VESSEL | VARCHAR2(12) |
| VOYAGE | VARCHAR2(12) |
<br\> <br\> <br\> <br\> <br\>
Add to ORDER_TRACKING section :-
| CMR_NO | VARCHAR2(50) |
<br\> <br\>
New MILESTONES section will need adding at the same level as PO_HEADER :-
| MST3_DATE_SENT_FIN | DATE |
| MST5_DATE_SCAN | DATE |
| MST5_SCAN_URL | VARCHAR2(1000) |
| MST7_DATE_DOC_DISP | DATE |
| MST8_COURIER_REF | VARCHAR2(50) |
<br\> <br\> <br\> <br\> <br\> <br\> <br\>
There are also some existing MTS tables that need some new fields adding to store the additional fields that the client has requested :-
Additional fields need to be added to table SCH_TRIP (ST) :-
| TRANSPORT_MODE | VARCHAR2(12) |
| SWB | VARCHAR2(50) |
<br\> <br\> <br\> <br\> <br\> <br\>
Additional fields need to be added to table SCH_TRIP_STOP (STS) :-
| MOTHER_FEEDER | VARCHAR2(1) |
| VESSEL | VARCHAR2(12) |
| VOYAGE | VARCHAR2(12) |
<br\> <br\> <br\> <br\> <br\> <br\>
New table SCH_ORD_PO_DATA (SOPOD) :-
| OMS_REF | VARCHAR2(12) – Unique primary key |
| MST3_DATE_SENT_FIN | DATE |
| MST5_DATE_SCAN | DATE |
| MST5_SCAN_URL | VARCHAR2(1000) |
| MST7_DATE_DOC_DISP | DATE |
| MST8_COURIER_REF | VARCHAR2(50) |
<br\> <br\> <br\> <br\> <br\> <br\> <br\> <br\>
The following fields also need to be added to INT_PO_OUTBOUND_FILE table :-
| TRIP_TRANSPORT_MODE | VARCHAR2(12) |
| SWB | VARCHAR2(50) |
| MST3_DATE_SENT_FIN | DATE |
| MST5_DATE_SCAN | DATE |
| MST5_SCAN_URL | VARCHAR2(1000) |
| MST7_DATE_DOC_DISP | DATE |
| MST8_COURIER_REF | VARCHAR2(50) |
<br\> <br\> <br\> <br\> <br\> <br\> <br\> <br\> <br\>
Also a new table called RES_VESSEL also need creating :-
| VESSEL_ID | VARCHAR2(12) – Unique key |
| VESSEL_NAME | VARCHAR2(50) |
| CARRIER_ID | VARCHAR2(12) – Foreign key with RES_CARRIER |
<br\> <br\> <br\> <br\> <br\> <br\>
NB) It is assumed that the vessel will only have one carrier.
Trip Form
In TRIPSUM form (2.164 the 2 new fields TRANSPORT_MODE and SWB will be added to the Trip Detail tab. These will be displayed based on a system parameter so as not to interfere with other MTS installations. The TRANPORT_MODE will be free text.
When the SWB is changed for the first time then this will generate a new milestone outbound message (MST1 see outbound XML later). It will also automatically update the SO.BILL_OF_LADING field on all of the related orders.
Furthermore, on this tab canvas a change will be made so that the Delivery Type field is amendable and appropriate validation (add a standard lookup) will be added. This field (SCH_TRIP.SVC_TYPE) will then be used to update all related orders (SO.DELIVERY_TYPE_ID).
The additional fields that will be added to STS table (MOTHER_FEEDER, VESSEL and VOYAGE) will need to be added to the configurable section for trip stops.
- MOTHER_FEEDER will be entered as either ‘M’ or ‘F’.
- VESSEL will be validated against the new table RES_VESSEL for a matching carrier. A standard lookup will be made available.
- VOYAGE will be free text.
A new option on the right click drop down menu from the Trip tree will be added called ‘Re-Send PO Message’.
This will initially act a like the ‘Set Status’ option where it will expand to show all of the statuses for which a message can be re-sent (‘ACCEPTED’, ‘EN_ROUTE’ and ‘COMPLETED’). The extra validation required before a message can be sent and the generation of the message can be taken from the existing TIU_TRIP_STATUS_TESCO trigger.
NB) The trigger will also be amended to also send a message when the trip is set to status ‘ABANDONED’.
A new tab will be added called ‘PO Detail’ next to the existing Audit tab for the trip.
This new tab will be driven by the new table SOPOD showing orders in a cluster that are on this trip along with the new milestone fields.
NB: Whenever one of these fields is changed then this will generate an XML file putting the fields into the appropriate fields on the outbound file.
The Transport Mode to update the new field on the new table SCH_ORD_PO_DATA for all orders on the trip that were generated from a PO.
All of the above changes also need to be made to TRIP_PLAN form.
Resource Form
A new tab will be added to the RESOURCE form (2.54) called ‘Vessels’ which will be used to maintain the new table RES_VESSEL.
- VESSEL_ID will be a validated as a unique identifier.
- VESSEL_NAME is free text.
- CARRIER_ID will be validated against RES_CARRIER with a standard lookup.
Outbound XML
Additional outbound changes not already covered in the PO Order estimate.
OBS will change the INT_XML.PROCESS_PO_OUTBOUND to populate the new fields SWB on INT_PO_OUTBOUND_FILE and also TRIP_TRANSPORT from the corresponding fields on the STS table.
These will then be written to the appropriate tags in the TRIP_TRACKING section.
Against all stops on a trip there are 3 new fields MOTHER_FEEDER, VESSEL and VOYAGE.
- The values on the first stop will be used to populate the new FROM tags in the TRIP_TRACKING section.
- The values from the last stop will be used to populate the new TO tags in the TRIP_TRACKING section.
- The values in the intermediate stops will be used to populate the new tags in the CROSS_DOCKING section.
The SO.POD_NAME field will be used to populate the CMR_NO tag in the ORDER_TRACKING section.
A new MILESTONES section at the same level as PO_HEADER etc.will be added:-
NB) MST1 is in the TRIP_TRACKING section and MST2 is in the PO_HEADER section.
All of the following tags will be set from corresponding field from the new SOPOD file.
MST3_DATE_SENT_FIN
MST5_DATE_SCAN
MST5_SCAN_URL
MST7_DATE_DOC_DISP
MST8_COURIER_REF
Scope
This change will be applied to system version 10.6.
Data
Pre-requisites
These are all additions to the existing Tesco interfaces.
System Registries
None.
FUNCTIONAL DESCRIPTION
Trip Form
The trip form (Trip Detail tab) currently looks like :-
Delivery Type field will be changed so it is amendable with appropriate validation added (and a standard lookup). This field SCH_TRIP.SVC_TYPE will then be used to update the field SO.DELIVERY_TYPE_ID on all of the related orders.
Due to limited space on the form all of the new Purchase Order fields will be added to a new tab called ‘PO Detail’ next to the existing tab ‘Audit’.
The new fields TRANSPORT_MODE and SWB from the SCH_TRIP table will be added at the top of the tab.
The TRANSPORT_MODE will be free text.
When the SWB field which is also free text is populated for the first time then this will automatically generate a new milestone outbound message (MST1 see outbound XML later).
The system will also need to automatically update the SO.BILL_OF_LADING field on all of the related orders.
Also on this new tab will be a cluster showing the fields from the new table SOPOD for all of the orders that are on this trip along with the new milestone fields.
Whenever one of these milestone fields is changed then this will generate an XML file putting the fields into the appropriate fields on the outbound file (see outbound XML later).
A new option on the right click drop down menu from the Trip tree called ‘Re-Send PO Message’ will be created.
This will initially act like the ‘Set Status’ option where it will expand to show all of the statuses for which a message can be re-sent (‘ACCEPTED’, ‘EN_ROUTE’ and ‘COMPLETED’). The extra validation required before a message can be sent and the generation of the request for a message can be taken from the existing TIU_TRIP_STATUS_TESCO trigger.
The trigger TIU_TRIP_STATUS_TESCO will itself also be amended to send a message request when a trip is set to status ‘ABANDONED’.
The trip stops tab on the trip form is user configurable and few new fields will be added to the list of fields available to display.
The additional fields that have been added to STS table (MOTHER_FEEDER, VESSEL, VOYAGE and PORT) will need to be added to the configurable section for trip stops.
- MOTHER_FEEDER will be entered as either ‘M’ or ‘F’.
- VESSEL will be validated against the new table RES_VESSEL for a matching carrier. A standard lookup will be made available.
- VOYAGE is free text.
- PORT is free text.
NB) The equivalent of all of the above changes also need to be made to TRIP_PLAN form.
Resource Form
The resource form currently looks like:-
A new tab needs adding to the RESOURCE form called ‘Vessels’ which will be used to maintain the new table RES_VESSEL.
- VESSEL_ID will be a validated as a unique identifier.
- VESSEL_NAME is free text.
- CARRIER_ID will be validated against RES_CARRIER with a standard lookup.
Outbound XML
Some additional changes to the outbound XML that are not already covered in the PO estimate or the Order estimate are also required.
Change the INT_XML.PROCESS_PO_OUTBOUND to populate the new fields SWB on INT_PO_OUTBOUND_FILE and also TRIP_TRANSPORT from the corresponding fields on the ST table.
These will then be written to the appropriate tags in the TRIP_TRACKING section.
Against all of the stops on a trip there are 4 new fields MOTHER_FEEDER, VESSEL, VOYAGE and PORT.
- The values on the first stop will be used to populate the new FROM tags in the TRIP_TRACKING section.
- The values from the last stop will be used to populate the new TO tags in the TRIP_TRACKING section.
- The values in the intermediate stops will be used to populate the new tags in the CROSS_DOCKING section.
The SO.POD_NAME field will be used to populate the CMR_NO tag in the ORDER_TRACKING section.
The new milestone MST1 is reported in the TRIP_TRACKING section (covered earlier in the specification) and the new milestone MST2 is reported in the PO_HEADER section (covered in the specification for the PO).
The other new milestones are all in the new MILESTONES section which is at the same level as PO_HEADER and needs populating:-
All of the following tags will be set from corresponding fields from the new SOPOD file.
MST3_DATE_SENT_FIN
MST5_DATE_SCAN
MST5_SCAN_URL
MST7_DATE_DOC_DISP
MST8_COURIER_REF
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued | ||||
| Added PORT to Trip Stops |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
