291808

DHL C-TMS

Delivery Confirmation Flow

FUNCTIONAL SPECIFICATION - 10.7

20/10/2011 - 3.0
Reference: FS 291808 – TH-8LFCHA

Functional Overview

Client Requirement

Delivery confirmation flow: Development, Mapping consultation, implementation and UAT support of the Delivery Confirmation shipment flow from C-TMS into SAP.

To include:

Mapping workshop

Interface build

CTMS set-up

Testing support

Implementation to live

Solution

EDI Messages

An outbound Delivery Confirmation message is required to be produced to indicate to the SAP system that an order has been delivered.

C-TMS will be altered to queue a message record whenever a Gypsum Order moves to the status of ‘DELIVERED’ (i.e. debrief info completed).

A polling process within the ‘Edi Maintenance’ screen will be set up to select these queued records and produce an outbound file per owning depot (i.e. Barrow / East Leake) to be sent to SAP via DHL Link.

The message format will be in the following csv file format:

External Ref, Delivery Date, time, Driver of Trip/Subcontractor

0083777430,20110812,0815,Jeff Cooper,,

Note there is an empty field at the end of the file layout and that no header info will be sent in the actual files.

CSV Export

In addition, an export process will be developed to produce a CSV file for a range of schedules. The user will be prompted for:

From Actual Date

To Actual Date

Cost Centre (i.e. British Gypsum to segregate from other Industrial contracts)

Owning Depot (i.e. Barrow, East Leake or All)

The layout to be sent will be a csv file. This be an exact replica of the EDI file format but will be extracted using the above criteria rather than using the queued records to derive the data to be sent.

Scope

This change will be applied to system version 10.7

Set-up

Pre-requisites

None

Menu Structure

Unchanged

Data

Records will be created in the REP_REPORT and REP_REPORT_PARAM table to control the data extract.

Implementation Advice

A system super user will be required to set up the correct EDI flow.

A system super user will be required to grant access to the export functionality to the correct user groups

Functional Description

Message Creation

The “TI_SCH_ORD_STATUS” trigger will be amended to create delivery confirmation messages. Each time the status of an order moves to “Delivered” the EDI flows for the customer will be checked to ascertain if this customer has a DELIVERY_CONF flow. If a flow is found an entry will be created in the control table used to hold messages waiting to be processed. The following fields should be used to create the message.

Control table	C-TMS Data
INT_XML_CONTROL.EXTERNAL_SYSTEM	DELIVERY_CONF
INT_XML_CONTROL.OMS_REF	SCH_ORD.oms_ref
INT_XML_CONTROL.CUSTOMER	SCH_ORD.customer
INT_XML_CONTROL.EVENT_TYPE	ORD
INT_XML_CONTROL.PROCESSED	N

EDI Message Flow

A new extract “DELIVERY_CONF” will be developed to process delivery confirmation messages. The delivery folder and file name will be passed into the extract as parameters and will be contained within the EDI set up information. The output from the extract will be in a csv format and will contain the following information

Extract File	C-TMS Data
External Ref	SCH_ORD.external_ref
Delivery Date	INT_XML_CONTROL.created_date(date only)
Time	INT_XML_CONTROL.created_date(time only)
Driver/Subcontractor	SCH_TRIP.driver_id join to RES_PERSON.forname, surname or If no driver allocated SCH_TRIP.carrier_id

The file will contain all unprocessed “DELIVERY_CONF” records in the control table for the British Gypsum customer. Once a record has been processed it will be updated and will not be extracted again. All processed records will also have the extract filename recorded against them. An example of the file format is shown below

0083777430,20110812,0815,Jeff Cooper,,

Note there is an empty field at the end of the file layout.

Files will be produced by owing depot so each extract may produce more than one file.

The extract will be set up in the EDI maintenance screen. The name of the extract will be set to “DELIVERY_CONF” and will be for the British Gypsum Customer and Cost Centre. The files will be delivered to the British Gypsum Server via DHL LINK ftp with the, ETHOS Login credentials TBA. The output filenaming convention is DHL27_XX_YYYYMMDD_[******].csv where ****** is a 6 digit number that increments with each new file, and XX is two characters representing the depots “BA” for Barrow and “EL” for east leake. These are to be delivered to the ETHOS subfolder “/in” for test and /in for live. The frequency of the extract is set when the EDI flow is created this can be specified in days, hours, or minutes.

An example of the EDI maintenance screen is shown below

The extract will require extract parameters to specify the export name and file extension. An Example of which is shown below

CSV Export

The “DELIVERY_CONF” extract will also be available to run from the Exports screen. As part of this RIO records will be created in the REP_REPORT and REP_REPORT_PARAM tables to control the export and the “DELIVERY_CONF” extract will be amended to accept these values as input parameters. The parameters will be:

• From Actual Date
• To Actual Date
• Cost Centre
• Owning Depot.
• File Name

The dates and cost centre parameters must be entered when running from the exports screen to ensure the correct records are selected. If any of these parameters are omitted an error will be returned. The Owning depot can be omitted which will then export data from all relevant owning depots. Only orders which are at a status of Delivered and the actual arrival time of the order is between the specified dates and for the relevant cost centre and owning depots will be selected.

When the extract is being run from the exports screen the previously mentioned control table will not be used instead the data will be collected directly from the Order and Trip records.

The file format will be unchanged and files will still be produced by owning depot and may produce more than one file per extract.

The file name will be specified as one of the export parameters in the format BYGP_ORDCONF this will then have the a two digit owning depot and from and to dates added to create the full file name e.g. BGYP_ORDCONF_XX_111017_111021.csv.

XX is two characters representing the depots “BA” for Barrow and “EL” for east leake..

NB the set up of the Owing Depots within C-TMS are expected to either use ‘BA’ and ‘EL’ as their location codes. OBSL will include additional code to translate values set up as Owning Depots into alternative ids as an optional addition. This will be facilitated by a new Decode type of EDI_OWNING_DEPOT. Therefore the users will be able to set up an owning depot of EASTLEAKE and a translated value of EA.

An example of the exports screen is shown below

Table Updates Required

insert into rep_report (name, report_type, filename, printer_type, show_pform, orientation, proc_name) values ('DELIVERY_CONF','CSV','BG_ORDCONF','Laser','F','LANDSCAPE', 'dp_csv5.delivery_conf');

INSERT INTO rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title,default_value_type,report_list) values ('DELIVERY_CONF','CSV','P_START_DATETIME','P_START_DATETIME','M','Start Date','TODAYN');

INSERT INTO rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title,default_value_type,report_list) values ('DELIVERY_CONF','CSV','P_END_DATETIME','P_END_DATETIME','M','End Date','TODAY','N');

INSERT INTO rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string, report_list) values ('DELIVERY_CONF','CSV','PG_SELECT_LIST1','P_COST_CENTRE','M','Cost Centre','SELECT distinct(cost_centre_name) COL1, cost_centre_name COL2 FROM rev_cost_centre rcc, adm_user_param aup WHERE rcc.cost_centre_name like DECODE(aup.value,ALL,%%,aup.value) AND aup.username = (SELECT user FROM dual) AND aup.param_type = COST_CENTRE ORDER BY cost_centre_name','N');

INSERT INTO rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string, report_list) values ('DELIVERY_CONF','CSV','PG_SELECT_LIST2','P_OWNING_DEPOT','O','Owning Depot','SELECT distinct(gl.location_id) COL1, gl.location_name COL2 FROM geo_location gl, adm_user_param aup WHERE gl.location_id = aup.value AND aup.username = (SELECT user FROM dual) AND aup.param_type = BASED_AT AND gl.depot = RDC and NVL(gl.inactive,N) = N UNION SELECT distinct (gl.location_id) COL1, gl.location_name COL2 FROM geo_location gl, adm_user_param aup WHERE gl.location_name like DECODE(aup.value,Y,%%) AND aup.username = (SELECT user FROM dual) and aup.param_type = ALL_DEPOTS and gl.depot = RDC and NVL(gl.inactive,N) = N','N');

INSERT INTO rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, default_value_type,default_value report_list) values ('DELIVERY_CONF','CSV','PG_FFCHAR1','PFILENAME','M','File name','STRING','BGYP_ORDCONF','N');

Modules to be changed

Module Name	Module Type	Notes
DP_CSV5.sql	Package	Add new functionality
TI_SCH_ORD_STATUS.sql	Trigger	Add new functionality

References

Ref No	Document Title & ID	Version	Date
1	EST-291808-TH-8LFCHA Delivery Confirmation Flow	1.0	30/09/11

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	17/10/11	Draft	Initial version	CAK
0.2	18/10/11	Draft	Reviewed	MJC
0.3	18/10/11	Draft	Revised	CAK
1.0	18/10/11	Issue	Issued	MJC
1.1	19/10/11	Draft	Revised	CAK
2.0	19/10/11	Issue	Reviewed and Issued	MJC
2.1	20/10/11	Draft	Revised	CAK
3.0	20/10/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager