290933

DHL C-TMS

Baxter Mapping

FUNCTIONAL SPECIFICATION - 10.7

08/09/2011 - 1.1
Reference: FS 290933 -NW-8KEN3X

Functional Overview

Client Requirement

Change Request Summary:

Project Rigel - Baxter Mapping.

Change Request Details:

It is assumed that this RIO will be managed in conjunction with the Project Rigel System Requirements Document v1.0 or higher.

Implement two new mapping tables to derive Delivery Method and Route Code based on Baxter scheduling.

Benefits identified as a result of the change:

Defined as part of Project Rigel.

Solution

Two new tables will be created in CTMS to hold the Delivery Method and Route Code standing data.

Two tabs will be added to the Business Data Management screen, these tabs will allow the standing data to be entered and edited.

Delivery Method

The delivery method table and tab will contain the following fields:-

• Original Code – Free text field
• Carrier Type – Text field. Validated against RES_CARRIER_TYPES table. LOV will be available.
• Carrier ID – Text field. Validated against RES_CARRIER. LOV will be available.
• Service – Text field. Validated against CNT_DEL_TYPE. LOV will be available.
• Location Type – Text field. Validated against GEO_LOC_TYPE. LOV will be available.
• Revised Code – Free text field.

The data in this table will be used when sending the IFCSUM message to WISE.

Route Code

The route code table and tab will contain the following fields:-

• Route Code – Free text field
• Outbase – Text field. Validated against GEO_LOCATION. LOV will be available.
• Trunk No – Free text field

The visibility of the Tabs will be controlled using the Access Control screen.

N.B. This estimate only covers the entering and storage of the standing data for the Delivery Method and Route Codes. Changes to other functionality to utilise this data will be covered in the relevant RIO: NW-8KEMH2

Two new CSV imports will be created that will allow the import of the Delivery Method and Route Code data. The imports will be maintained in the current Imports Maintenance screen, and will be available in the Imports Exec screen.

The imports will accept data in CSV format and will store the data in relevant new table.

Scope

This change will be applied to system version 10.7.0.

Set-up

Menu Structure

Unchanged

Data

Two new tables will be created called DELIVERY_METHOD and ROUTE_CODE

Table DELIVERY_METHOD

ORIGINAL_CODE VARCHAR2(12)

CARRIER_TYPE VARCHAR2(35)

CARRIER_ID VARCHAR2(12)

SERVICE VARCHAR2(35)

LOCATION_TYPE VARCHAR2(12)

REVISED_CODE VARCHAR2(12)

Table ROUTE_CODE

ROUTE_CODE VARCHAR2(12)

OUTBASE VARCHAR2(12)

TRUNK_NOVARCHAR2(12)

Implementation Advice

A system super user will be required to provide access to the two new imports (DELIVERY_METHOD and ROUTE_CODE) to the relevant user groups.

The Access Control screen (below) is used to provide group access.

A system super user will be required to provide access to the two new tabs on the Business Data Management screen (DELIVERY_METHOD and ROUTE_CODE) to the relevant user groups.

The Access Control screen (below) is used to provide group access.

Functional Description

Data Maintenance

There is a requirement in C-TMS for 2 new tables to hold Delivery Method and Route Code data.

The tables will be user maintainable from the Business Data Management screen.

Each table will be displayed on a new tab within the Business Data Management screen

Lists of Values will be available to aid data entry on the following fields

• CARRIER_TYPE
• CARRIER_ID
• SERVICE
• LOCATION_TYPE

This ensures that any values entered in the fields have already been created in C-TMS. Validation will be carried out against the reference table for each field.

Field Name	Source Table	Source Field
CARRIER_TYPE	RES_CARRIER_TYPE	CARRIER_TYPE_ID
CARRIER_ID	RES_CARRIER	CARRIER_ID
SERVICE	CNT_DEL_TYPE	DEL_TYPE
LOCATION_TYPE	GEO_LOC_TYPE	LOC_TYPE

The screen will be used for adding new Delivery Method records and editing existing records.

When entering a Route Code record, a list of values will be available for the Outbase entry. The value entered will be validated against the location records in C-TMS.

Field Name	Source Table	Source Field
OUTBASE	GEO_LOCATION	LOCATION_ID

CSV Imports

Two new import types will be created: BAX_DEL_TYPE and BAX_ROUTE.

The following import fields will be created and associated with BAX_DEL_TYPE import:

ORIGINAL_CODE

CARRIER_TYPE

CARRIER_ID

SERVICE

LOCATION_TYPE

REVISED_CODE

The following import fields will be created and associated with the BAX_ROUTE import:

ROUTE_CODE

OUTBASE

TRUNK_NO

The imports will be created and maintained in the Import Maintenance screen, where users are able to define the name of the import file and the folder where the import file will be stored.

Two new procedures will be added to the import package (IMP.sql) to process the Delivery Method and Route Code imports. Both imports will add new records and amend existing records.

For Delivery Method records, the unique identifier will be a combination of:

• Original Code
• Carrier Type
• Carrier Id
• Service
• Location Type

If the system finds an existing record, the REVISED_CODE will be updated. If the system does not find an existing record then the record will be created.

For Route Code records, the unique identifier will be a combination of Route Code and Outbase.

If the system finds an existing record, the Trunk No will be updated otherwise a record will be created.

If values are received in the Carrier Type, Carrier id, Service, Location Type or Outbase fields which do not exist in C-TMS, the record import will fail.

Table Updates Required

Scripts to create the new tables are attached below.

-- Create table create table DELIVERY_METHOD (

 ROUTE_CODE      VARCHAR2(12) not null,
 OUTBASE         VARCHAR2(12) not null,
 TRUNK_NO  	  VARCHAR2(12),

);

--Synonym create PUBLIC SYNONYM ROUTE_CODE FOR ROUTE_CODE;

-- Grant/Revoke object privileges grant select, insert, update, delete on ROUTE_CODE to MTS_USER; grant select on ROUTE_CODE to MTS_USER_READ_ONLY;

-- Create table create table DELIVERY_METHOD (

 ORIGINAL_CODE      VARCHAR2(12) not null,
 CARRIER_TYPE       VARCHAR2(35) not null,
 CARRIER_ID  	     VARCHAR2(12),
 SERVICE            VARCHAR2(35),
 LOCATION_TYPE      VARCHAR2(12),
 REVISED_CODE       VARCHAR2(12) not null

);

--Synonym create PUBLIC SYNONYM DELIVERY_METHOD FOR DELIVERY_METHOD;

-- Grant/Revoke object privileges grant select, insert, update, delete on DELIVERY_METHOD to MTS_USER; grant select on DELIVERY_METHOD to MTS_USER_READ_ONLY;

References

Ref No	Document Title & ID	Version	Date
1	EST-290933 NW-8KEN3X Baxter Mapping v1.0	1.0	26/08/2011

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	05/09/2011	Draft	Initial version	SEW
0.1	06/09/2011	Draft	Review of the initial draft	PJH
1.0	06/09/2011	Issued	Issued to client	PJH
1.1	08/09/2011	Draft	Updated import type	PDR

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager