287105

DHL CTMS

Setup Interface Flows for Unilever

FUNCTIONAL SPECIFICATION - 10.6

- 1.0

Reference: 287105 NA-8F5HA6

Client Requirement

DHL have won some Unilever transport business. We will be transporting goods from Doncaster NDC and Cannock NDC to Ireland.

It is envisaged that we will setup one customer with 2 locations (Cannock and Doncaster). All interface flows (ORD, TRP, Confirmation, ARR and DEL) as per other Unilever locations will be required. The TRP message needs to trigger for each location.

Solution

Due to the increasing number of flows that are based on the original Unilever flow we will incorporate the Unilever flows (Unilever and Uniice) into the generic outbound EDI flow.

Outbound Flows The two existing flows for Unilever and Uniice are stand alone and are not controlled by the EDI parameters screen, this means any new customer and locations that need the same messages require a whole new set of procedures each time. This will be changed to allow new flows to be added via the EDI screen, these flows will then use the generic code.

The outbound generic flow will be changed to include processing the ARR and DEL messages along with the TRP message. All three messages will then be processed by the one procedure (INT_XML_OUT2.GEN_TRIP_XML) rather than individually. This one procedure will be called and the flow name (E.G. UNI, GLO) will be passed in. Based on this the necessary information will be retrieved from EDI_PROCESS_HEADER. This will allow several message flows to use the same procedure without the need to duplicate code.

To help accommodate the different customers and locations that are now using the flows, the EDI setup tab on the imports screen will be used. The tab already includes a field for Customer and Location so these fields will be used when creating a new flow. A new outbound message flow will need to be setup for each Customer and location combination. The flows will be set up as Outbound and the relevant folder information will need to be added. To ensure the current flows are unaffected they will be setup as UNI and GLO and will continue to process as they do now.

Two new tick boxes will be added to the screen, one for the DEL message and one for the ARR message. These will only be enabled when the message direction is Outbound. These flags will be checked when the job is processed, if they are set then the ARR and DEL messages will be produced providing there is an appropriate record in the interface table and the relevant details are present (E.G. non conformance reasons where required).

Any hardcoded variables in the procedures will be removed and replaced with the data retrieved from the EDI setup table EDI_PROCESS_HEADER.

The triggers that write records to the table INT_XML_CONTROL will need to be altered to remove the hardcoded values. Instead of these values the order information (Customer and From Location) will be compared to EDI_PROCESS_HEADER. If a record is found that matches than a record for that flow type will be written and will contain the trip and order information required. This will then be picked up the next time the job runs.

The triggers that need updating are:-

•TRG_SCH_TRIP_XML_OUT

•TRG_SHA_XML_OUT

•TRG_SOL_XML

•TRG_STS_XML_OUT

Inbound Flows To allow consistency between the flows the changes that were requested to the Inbound order flow for Unilever and Uniice will be incorporated into the generic inbound flow. The changes that are currently in INT_XML_OUT.sql will be moved to INT_XML.sql. The inbound flows will then be controlled by the EDI tab on the Imports screen.

A summary of the changes are:-

•Sap Line No added to order lines / items

•RPE can be manually added to order lines (controlled by customer parameter).

•Alternative Item Id can be used if required (controlled by customer parameter).

•Update To Deliver quantity (controlled by system parameter).

A comparison will be made of the current Unilever process at functional spec stage to ensure all changes have been included.

Scope

This change will be applied to system version 10.6.

Functional Description

Outbound Flows

The number of flows that are using the Unilever template is increasing, which is leading to repetition of code and increased time to develop each flow. To try and reduce the amount of code and enable the addition of future flows the Unilever messages will be moved into the generic EDI flow that already exists in CTMS. This will include the inclusion of the new flows for Unilever Ireland.

Control of the flows will be moved from system parameters into the EDI setup tab on the Imports screen. This will allow new flows to be added without the addition of new system parameters and without code being added to the existing packages.

Part of the Unilever and Uniice development was the addition of two new message types DEL and ARR. These messages are currently run separately from the TRP message and require separate procedures. The generic outbound flow will be changed to include processing the DEL and ARR messages. The existing code for the DEL and ARR message will be included in the procedure INT_XML_OUT2.GEN_TRIP_XML.

There are currently 4 procedures that run the DEL and ARR messages, two for UNI and two for GLO. The code is identical apart from the variables used (e.g. location, customer etc). The removal of the hardcoded variables (see further down) will allow the removal of these 4 procedures with the code being moved into GEN_TRIP_XML.

The flow name (setup in the EDI setup tab) is passed into GEN_TRIP_XML and will be used to determine the parameters that will be used for this flow. The parameters are stored on EDI_PROCESS_HEADER.

• Customer
• Location
• Folder (Delivery Folder)
• Archive Folder
• DEL message – New tick box
• ARR message – New tick box

All of the information required to produce the TRP, DEL and ARR messages will be controlled in the EDI Setup tab on the Imports screen.

The screen already includes fields for customer and location so these fields will be used when adding new flows. A new message will be setup for each customer and location combination that requires the TRP, DEL or ARR message.

The EDI tab will be changed to add two tick boxes labelled:

• ‘Send DEL message’
• ‘Send ARR message’.

The tick boxes will only be enabled when the Direction is set to Outbound. If these flags are ticked for a particular flow then the DEL and / or ARR message code will run and produce the appropriate message for any record in the interface table.

The current processes contain several hardcoded values for the location and the customer. These will be removed and the values retrieved from EDI_PROCESS_HEADER.LOCATION_ID and EDI_PROCESS_HEADER.CUSTOMER. The values will then be used to determine which trips and orders are to be included in the outbound file.

The name of the files produced will be taken from the Filename Format field on the screen combined with the date and timestamp.

The outbound directories that are currently stored as parameters will be stored in EDI_PROCESS_HEADER and will be entered using the fields in EDI screen.

• Delivery Folder
• Archive Folder

A new button will be added to the screen that will open a popup window. The window will contain the fields required to FTP the files once they have been created.

• FTP Destination IP – Ip address
• FTP Port – destination port
• FTP Username – destination username
• FTP Password – destination password
• FTP Directory – destination directory.

New columns will be added to table ESI_PROCESS_HEADER to store this information.

The triggers that write the records to the interface table INT_XML_CONTROL for processing will be changed to remove the hardcoded values. Currently the From Location and customer of each order are compared to a list of hardcoded values; this will be changed to compare the From Location and customer of the order to the values held against EDI_PROCESS_HEADER. If there is a matching EDI record then a record will be written to INT_XML_CONTROL for the process name retrieved.

The triggers that will be updated are:-

• TRG_SCH_TRIP_XML_OUT
• TRG_SHA_XML_OUT
• TRG_SOL_XML
• TRG_STS_XML_OUT

Additional changes to Outbound flow

The ARR and DEL messages are only required once per order. A new field will be added to SCH_ORD that will contain the messages sent for that particular order. When an ARR or DEL message is sent the field will be updated with ARR or DEL. The values will be separated by a comma. During the processing of the ARR and DEL message it will check if the appropriate value is stored in this field, indicating this message type has previously been sent. If it has then this order will be excluded from the file.

As part of this RIO the existing flows that use the UNILEVER messages will be set up in this screen as UNI and GLO which will allow them to carry on processing as they do now.

Inbound Flows

Several changes were made for the Unilever and Unice flows. These will now be incorporated into the generic inbound flow. The code will be moved from INT_XML_OUT.sql to INT_XML.sql. Any of the changes that affect functionality are already controlled by system or customer parameters, so they should not impact existing flows.

The changes that need to be applied are:-

• Sap Line No – This was added to the Order lines and Items, and is sent in the tags SAP_LINE_NO.
• RPE can be manually added to order lines – RPE is sent in the tags RPE_QTY, and it’s use is controlled by a customer parameter (ALLOW_MANUAL_RPE).
• Alternative Item ID can be used in place of Item Identifer – Controlled by customer parameter USE_ALTERNATIVE_ITEM.
• Update To Deliver Quantity – Updates the To Deliver Quantity with the ordered quantity, controlled by a system parameter UPDATE_TO_DELIVER.

TABLE UPDATES REQUIRED

• Alter EDI_PROCESS_HEADER Add

• (FTP_DESTINATION_IP VARCHAR2(20)

• FTP_DESTINATION_PORT VARCHAR2(20)

• FTP_USERNAME VARCHAR2(20)

• FTP_PASSWORD VARCHAR2(20)

• FTP_DIRECTORY VARCHAR2(20))

Alter table SCH_ORD add (EDI_SENT VARCHAR2(12));

Document History

Version	Date	Status	Reason	Initials
0.1	10/03/11	Draft	Initial version	DNG
1.0	14/03/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager

|}