291361

DHL C-TMS

Requestor Emails

FUNCTIONAL SPECIFICATION - 10.7

21/10/11 - 2.0
Reference: FS 291361 MS8KNGMG

Client Requirement

Change Request Summary:

Requestor Emails

Change Request Details:

Order requestor is automatically sent an email to confirm details of bookings as order is received into C-TMS. Once briefed, a follow-up email confirms the carrier appointed.

Benefits identified as a result of the change:

Confirmation received by the requestor for any orders placed and then briefed to carrier.

Solution

Additional References/Message Types:

The additional references functionality provided by the orders screen will be used to receive and store the requestor name and requestor email address. This data will be either manually entered or captured through the CSV order import facility or captured through the XML order EDI interface.

The requestor name and email address will be used to send an order received email as soon as the order is received into C-TMS, then later in the transport process, once the order is planned and a carrier assigned, a confirmation email is sent to the requestor. The order received and order confirmed emails will contain an attached PDF document formatted as shown below

[[Image:]][[Image:]]

Order Capture:

The C-TMS order interface will be configured so that the requestor and email address can be uploaded into pre-defined additional reference fields. If more than one destination email address is required the reference field in the interface mapping will presented as a concatenation of the email addresses separated by “;” (semi-colon).

The CSV order import will be modified to allow the requestor and email address to be uploaded with each order.

If manually keyed, the additional reference for the requestor and email address can be keyed into the Add Refs Tab of the order detail screen.

Note – The system parameter that holds the default sending email address will be set up to be the value ‘[email protected]’. Note this will need to be done at the cost centre level of system parameters.

Once the requestor email addresses have been captured and the order validated (UNSCHEDULED status) a message will be queued before a the existing C-TMS email process will activate on a five minute schedule to send any new emails.

Email Attachments:

An Oracle report will be developed to generate the two PDF documents that form the attachment to the emails. The output is very similar for both the received and confirmed outputs. The confirmed output in addition shows the details of the appointed carrier providing the transportation.

Email Processing:

A database job will run on C-TMS looking for NEW queued received and confirmed emails to send. As these are recognised, the existing process will identify queued records, generate the report output, attach to an appropriate email and send to the required recipient.

Carrier Brief:

Once an order has been planned and a carrier assigned to the trip and the trip status promoted to ACCEPTED, a carrier confirmation email will be sent to confirm this information. This action will only be performed for the order(s) on the trip that contain a requester email address in additional references.

Other considerations:

It is assumed that if the carrier is changed and re-briefed, this will generate another carrier confirmation email to the requestor with the revised carrier information.

Technical Notes:

(A new message type of ‘ORD_CONF’ will be created to indentify orders which require requestor emails). (Insert into the MSG_EVENT table for the record with the new ORD_CONF type containing the OMS ref of the order. This information will then be processed by a database job which will complete the sending of the email). (A change will be required in the MSG_PROCESSING package to process the new ORD_CONF type message and run the Oracle report described. A change will then be required in the MSG_CONSTUCTOR package to obtain the email addresses and requestor names which have been captured as additional references against this order).

Scope

This change will be applied to system version 10.7.0

Set-up

Data

Insert into MSG_TYPE

Values (‘ORD_CONF’,’Order Confirmation Email’,’Y’,’[email protected]’,null)

Insert into IMP_DECODE_ENTRY

Values(‘XML_REFERENCE’,’1001’,’Requestor Name’);

Insert into IMP_DECODE_ENTRY

Values(‘XML_REFERENCE’,’1002’,’Requestor Email’);

Insert into REP_REPORT

Values(‘ORD_CONF’,’REP’,’ORD_CONF.rep’,’’,’’,’Laser’,’F’,’PORTRAIT’,’’,’’,’’)

Insert into REP_REPORT_PARAM

Values(‘ORD_CONF’,’REP’,’ PG_SELECT_LIST1’,’P_OMS’,’M’,’’,’OMS Ref’,’’,’’,’’,’’,’’,’N’)

Implementation Advice

A super user will be required to set the following cost centre parameters using the system parameter screen.

SMPT_ORD_CONF_SENDER	[email protected]
ORD_RECEIVED_SUBJECT	NWR Order Received
ORD_RECEIVED_MSG	Please find attached confirmation that your NWR order has been received
ORD_PROCESSED_SUBJECT	NWR Order Processed
ORD_PROCESSED_MSG	Please find attached confirmation that your NWR order has been processed.
ORD_CONF_REPORT	ORD_CONF.rep

Functional Description

Requestor Details

The requestor name and email address will be received with the order or entered manually and stored in the order references table and displayed in the ADD_REFS tab on the order screen.

The Order interface and the CSV import will be amended to allow the requestor information to be passed in and stored in the reference table. If more than one requestor is received, the system cannot store multiple records of the same reference. The import and interface will be coded to concatenate the multiple requestors into one requestor name record and one email record. The requestor details will be separated by ‘;’. The <SUB_REF_IDENTIFIER> will be set to 1001 for REQUESTOR_NAME and 1002 for REQUESTOR_EMAIL. The numbers will be translated to the required field names in the Import decode screen.

Order Events

When an order is validated and set to Status UNSCHEDULED for the first time on the system, a record will be added to the MSG_EVENT table to generate an email to the requestor. In addition, whenever the order is scheduled and the trip is set to ACCEPTED, a record will also be added to the control table MSG_EVENT for each order on the trip with a requestor_email record. (NB this could be more than once)

MESSGE_EVENT
EVENT_TYPE	‘ORD_CONF’
EVENT_REF	OMS_REF
STATUS	‘NEW’
SUBJECT	‘NWR Order Received’	external_refererence
MESSAGE	‘Please find attached confirmation that your NWR order has been received’

The subject and message will be derived from four COST CENTRE parameters called

ORD_RECEIVED_SUBJECT	NWR Order Received
ORD_RECEIVED_MSG	Please find attached confirmation that your NWR order has been received
ORD_PROCESSED_SUBJECT	NWR Order Processed	trip_id
ORD_PROCESSED_MSG	Please find attached confirmation that your NWR order has been processed.

When a record is created in the Message event table, the status of the order will determine which type of report is sent to the requestor and which parameter is used to populate the subject and message fields in the MESSAGE_EVENT table. If the order status is UNSCHEDULED, the ORD_RECEIVED parameters will be used, for order status SCHEDULED, the ORD_PROCESSED parameters will be used.

When the order status is UNSCHEDULED, the new Order_Confirmation report will be sent in the received format, when the order status is SCHEDULED, the Order Confirmation report will be sent in Processed format.

In addition to the text selected for the subject, data will also be added depending upon the status of the order. For unscheduled orders, the external reference will be added to the message subject, for scheduled orders the trip id will be added as part of the message subject

A record will only be added to the MESSAGE_EVENT table if an order has been assigned a requestor_email record. If a trip is set back to PLANNED and the carrier is changed, another record will be added into the MESSAGE_EVENT table for the trip.

Order Confirmation Report

A new report called ‘Order Confirmation’ report will be created in Oracle reports and will generate a PDF file. The report will include Order details and some trip details in the Processed Format.

ORDER RECEIVED REPORT
ORDER_NUMBER	SCH_ORD.OMS_REF
CREATION_DATE	SCH_ORD.DATE_CREATED
COLLECTION_CONTACT	GEO_CONTACT.FORENAME GEO_CONTACT.SURNAME
COLLECTION_SITE_NAME	GEO_LOCATION.LOCATION_NAME
COLLECTION_ADDRESS1	GEO_LOCATION.ADRESS_LINE1
COLLECTION_ADDRESS2	GEO_LOCATION.ADRESS_LINE2
COLLECTION_ADDRESS3	GEO_LOCATION.ADRESS_LINE3
COLLECTION_POSTCODE	GEO_LOCATION.POSTCODE
COLLECTION_CONTACT_NUMBER	GEO_CONTACT.PHONE
COLLECTION_DATE_TIME	SCH_ORD.EARLY_COLL SCH_ORD.LATE_COLL
PRODUCT_CODE	SCH_ORD_ITEMS.PRODUCT_TYPE_ID
PRODUCT_DESCRIPTION	PRD_PRODUCT_ITEM.ITEM_DESCRIPTION
PRODUCT_QTY	SUM(SCH_ORD_ITEMS.QTY_ORDERED)
DELIVERY_CONTACT	GEO_CONTACT.FORENAME GEO_CONTACT.SURNAME
DELIVERY_SITE_NAME	GEO_LOCATION.LOCATION_NAME
DELIVERY_ADDRESS_1	GEO_LOCATION.ADRESS_LINE1
DELIVERY_ADDRESS_2	GEO_LOCATION.ADRESS_LINE2
DELIVERY_ADDRESS_3	GEO_LOCATION.ADRESS_LINE3
DELIVERY_POSTCODE	GEO_LOCATION.POSTCODE
HIAB_REQUESTED
DELIVERY_DATE_TIME	SCH_ORD.EARLY_DEL SCH_ORD.LATE_DEL
SURCHARGES
SPECIAL INSTRUCTIONS	SCH_ORD.SPECIAL_INSTRUCTIONS

ORDER PROCESSED REPORT
ORDER_NUMBER	SCH_ORD.OMS_REF
CREATION_DATE	SCH_ORD.DATE_CREATED
JOURNEY NUMBER	SCH_TRIP.TRIP_ID
HAULIER NAME	RES_CARRIER.CARRIER_NAME
HAULIER CONTACT	RES_CARRIER.PHONE
COLLECTION_CONTACT	GEO_CONTACT.FORENAME GEO_CONTACT.SURNAME
COLLECTION_SITE_NAME	GEO_LOCATION.LOCATION_NAME
COLLECTION_ADDRESS1	GEO_LOCATION.ADRESS_LINE1
COLLECTION_ADDRESS2	GEO_LOCATION.ADRESS_LINE2
COLLECTION_ADDRESS3	GEO_LOCATION.ADRESS_LINE3
COLLECTION_POSTCODE	GEO_LOCATION.POSTCODE
COLLECTION_CONTACT_NUMBER	GEO_CONTACT.PHONE
COLLECTION_DATE_TIME	SCH_ORD.EARLY_COLL SCH_ORD.LATE_COLL
PRODUCT_CODE	SCH_ORD_ITEMS.PRODUCT_TYPE_ID
PRODUCT_DESCRIPTION	PRD_PRODUCT_ITEM.ITEM_DESCRIPTION
PRODUCT_QTY	SUM(SCH_ORD_ITEMS.QTY_ORDERED)
DELIVERY_CONTACT	GEO_CONTACT.FORENAME GEO_CONTACT.SURNAME
DELIVERY_SITE_NAME	GEO_LOCATION.LOCATION_NAME
DELIVERY_ADDRESS_1	GEO_LOCATION.ADRESS_LINE1
DELIVERY_ADDRESS_2	GEO_LOCATION.ADRESS_LINE2
DELIVERY_ADDRESS_3	GEO_LOCATION.ADRESS_LINE3
DELIVERY_POSTCODE	GEO_LOCATION.POSTCODE
HIAB_REQUESTED
DELIVERY_DATE_TIME	SCH_ORD.EARLY_DEL SCH_ORD.LATE_DEL
SURCHARGES
SPECIAL INSTRUCTIONS	SCH_ORD.SPECIAL_INSTRUCTIONS

The order status will be used to control if the trip information is displayed. The DHL and Network Rail logos will be displayed on the report and fixed text will be displayed in the footer. The content of the text will be controlled by the status of the order.

The report will be automatically generated based on order events and will also be available to run from the reports screen. The user will be required to provide an OMS_REF. A new record will be added in the REP_REPORT and REP_REPORT_PARAMS tables to facilitate this.

Creating the Email

To generate the email, a new COST CENTRE parameters will be created:

ORD_CONF_REPORT

ORD_CONF.rep

A new message type will be created called ORD_CONF , this record will be created as part of the release process.

A new procedure will be added to the Message Processing package to process MESSAGE EVENT records created for the message type ORD­_CONF. A new procedure will be added to the message constructor package to create the record and send to the requestor email address.

Table Updates Required

No Table Updates required

Modules to be changed

Module Name	Module Type	Notes
MSG_PROCESSING	Package	Amendment: New procedure
MSG_CONSTRUCTOR	Package	Amendment: New procedure
ORD_CONF	Report	New Report
TI_SCH_ORD_STATUS_TRIGGER	Trigger	Amendment
TIU_TRIP_STATUS	Trigger	Amendment

References

Ref No	Document Title & ID	Version	Date
1	EST-291361 MS-8KNGMB Requestor Emails v1.0	1.0	20/09/2011

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	07/10/2011	Draft	Initial version	SEW
1.0	10/10/2011	Issue	Reviewed and Issued	MJC
1.1	21/10/2011	Draft	Changed following review with client	SEW
2.0	21/10/2011	Draft	Reviewed and Emailed to AK, tracking left on for ease of reading, expected v3.0 will clear	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager