280702 (RA-88QFWP) - (Ack Messages for Cadbury)

DHL MTS

Acknowledgement Messages for Cadbury

FUNCTIONAL SPECIFICATION - 10.6

14/10/2010 - 1.0
Reference: 280702 RA-88QFWP

Client Requirement

Change Request Summary:

Customer requires an acknowledgement message for all orders received into MTS for Cadbury Customer.

Change Request Details:

Customer requires acknowledgment message back from MTS due to success or failure of file upload. Customer is in the process of transferring over to using EDI for their orders and all orders will be received in one file per order. This is to help with acknowledgment of orders the customer requires back.What the customer requires is an acknowledgement message back to them to confirm that the transport system (Calidus TMS) has received the file and whether or not it processed successfully or not.If an order processes into MTS and is successfully uploaded - an acknowledgment is needed to be sent backIf the order fails to upload - e.g. already exists - at wrong status to change etc. an acknowledgment to be sent back with the reason of failure.

What the customer need in the file is as follows:

Date and Time of failure

message number

Order Number

Haulier ID

Reason for failure (Can be free text)

the other fields can be defaulted in ESI.

Benefits identified as a result of the change:

Customer requirement as orders can change - the customer wants to send in an amendment but if the order has already been planned they need a message back to advise them so they can go back to their customer. If it fails for any reason they need to know so they can work around the issues.

Solution

The XML order process will be changed to include an acknowledgement file to inform the customer that an order has been successfully or unsuccessfully uploaded.

The acknowledgement file will be in an XML format and will contain the following items:

DATE_TIME (of upload)

MESSAGE_ID (of XML filename uploaded)

ORDER_REFERENCE (of customer order reference uploaded)

HAULIER_ID (blank as order has not been planned)

REASON_DESCRIPTION (of successful or unsuccessful upload)

The XML order file will be moved after processing to either a subdirectory called ‘archive’ (if the record status is ‘SUCCESS’) or ‘failed’ (if the record status is ‘FAILURE’).

The acknowledgement file will then be moved to a subdirectory called ‘ack’.

Scope

This change will be applied to system version 10.5

Set-up

Pre-requisites

The new EDI directories will need to be setup for customer Cadbury. The new columns on table ‘EDI_PROCESS_HEADER’ will need to be added – see Appendix A.

Menu Structure

Unchanged

Data

The EDI parameters will need to be setup for customer Cadbury in the ‘Import’ screen.

Functional Description

‘IMPORTS_EXEC’ Form

The XML order files for Cadbury will be uploaded via the generic EDI method as currently used by Jeyes as may be seen below (as an example that will need to be confirmed):

The screen will be changed to include a new parameter called ‘Acknowledgement Folder’ which will be placed beneath the parameter called ‘Failures Folder’. The parameter will be stored in column ‘ACK_FOLDER’ on table ‘EDI_PROCESS_HEADER’.

A tick box will be present immediately after the ‘Acknowledgement Folder’ to indicate if the acknowledgement files are required and they will be if the box is ticked. If the box is ticked then the column ‘SEND_ACK_MESSAGE’ on table ‘EDI_PROCESS_HEADER’ will be set to ‘Y’ otherwise it will be set to ‘N’.

It is expected that the ‘Acknowledgement Folder’ will be consistent with the other folders, for example:

‘/webint/contest/interface/ORD/in/ack’.

‘INT_XML_IN’ Package

A database job will be created if the EDI import is started:

If the XML order file has failed to upload successfully and the XML order header status has been updated to ‘FAILURE’ then the file received will be moved to the ‘Failures Folder’, otherwise the order will be uploaded and the file moved to the ‘Archive Folder’.

A change will be made to procedure ‘INT_XML.IN.PROCESS_ORD_XML_IN’ to assess whether the order has been successfully processed or not and into which directory the file will be placed.

All of the existing validation will be unchanged.

This XML order procedure will be changed to include an acknowledgement file to inform the customer that an order has been successfully or unsuccessfully uploaded. The acknowledgement will only be generated if the ‘Acknowledgement Folder’ box has been ticked.

The acknowledgement file will be in XML format and will contain the following items:

DATE_TIME (of upload)

MESSAGE_ID (of XML filename uploaded)

ORDER_REFERENCE (of customer order reference uploaded)

HAULIER_ID (blank as order has not been planned)

REASON_DESCRIPTION (of successful or unsuccessful upload)

Name	Datatype	Format	Column	Table
DATE_TIME	DATE	YYYY/MM/DD HH24:MI:SS	CREATED_DATE	INT_XML_ORD_HEADER
MESSAGE_ID	VARCHAR2(100)		INT_FILENAME	INT_XML_ORD_HEADER
ORDER_REFERENCE	VARCHAR2(20)		EXTERNAL_REF	INT_XML_ORD_HEADER
HAULIER_ID	VARCHAR2(12)	Blank
REASON_DESCRIPTION	VARCHAR2(2000)		VALIDATION_ERROR	INT_XML_ORD_HEADER

The acknowledgement filename will have the format ‘{XML_FILENAME}_ACK’.

N.B. If the order has been uploaded successfully then there will not be a validation error to report therefore the text ‘Processed OK’ will be inserted as the reason description.

N.B. As the acknowledgement message is generated at the order level, it is possible that there are multiple order lines and it is an order line that can cause the upload to fail; in this instance the reason description will relate to the order header level and will only indicate that the upload has failed due to a failure in the order detail lines.

The acknowledgement file will then be in the correct directory to be collected by ESI.

Table Updates Required

Table ‘EDI_PROCESS_HEADER’ will have 2 new columns added using the following script:

References

Ref No	Document Title & ID	Version	Date
1	EST-RA-88QFWP Acknowledgement Messages for Cadbury v1.0.doc	1.0	06/09/10

Document History

Version	Date	Status	Reason	Initials
0.1	04/10/10	Draft	Initial version	PDR
1.0	14/10/10	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager