291365

DHL C-TMS

Volumetrics

FUNCTIONAL SPECIFICATION - 10.7

12/10/11 - 1.0
Reference: 291365 MS-8KNH33

Client Requirement

Master data entered by user and uploaded from CSV. Define for each commodity the max order qty per vehicle type. This data allows the planner additional decision support to select and order the appropriate vehicle capacity for each order. Modifications to order well to allow volumetric information to be displayed on demand.

Solution

A new tab will be added to the existing Products screen (PRODUCT.fmb) to display, amend and enter values for existing product types and vehicle types. The tab will contain a scrollable area containing data columns for product types, vehicle types and a maximum DU quantity of that product allowed in a particular vehicle. This will allow the maximum quantity of a product to be stored against several vehicles types if needed. A new table will be created to store the new volumetric data.

A new CSV import flow will be created that will allow the importing of the new volumetric data. The import will be maintained in the existing Imports Maintenance screen and will run from the Imports Exec screen. The import will accept data in a CSV format and will store the data in the new table created for this RIO.

A new option will be added to the right click menu on the unscheduled orders tab of the trip screens (TRIPSUM.fmb and TRIP_PLAN.fmb, EXECUTION.fmb). When this option is chosen a pop up window will be displayed that will show the product types associated with the order. Along with the product types the pop up screen will show the available vehicle types and the maximum quantity of that product that is allowed on the vehicle along with % fill for each vehicle type/Product Type combination.

Scope

This change will be applied to system 10.7

Set-up

Pre-requisites

None

Menu Structure

Unchanged

Data

A new table will be created PROD_VEHICLE_VOLUME this table will hold the maximum volume of products on a given vehicle.

A new sequence will be created SEQ_PROD_VEHICLE_VOLUME will be created to be used as the primary key of the new table.

Records will be inserted into the IMP_RECORD_FORMAT, IMP_IMPORT_TYPE and

IMP_FIELD tables to accommodate the CSV import of volumetric information.

A record will be created in the ADM_FORM_TAB table for the new tab page.

New data will be added to the Extract Suite tables to facilitate a data extract of the Product Information

Implementation Advice

A system super user will be required to ensure that the import function and new tab page are added to the user group.

[[Image:]]

[[Image:]]

Functional Description

Product Volume Maintenance

A new tab page will be added to the product Maintenance screen to display enter and amend product/vehicle volume combinations, the data for this tab will be based on the information held in the new PROD_VEHICLE_VOLUME table. A suggested Layout is shown below.

[[Image:]]

The data shown in this screen is display only. The action buttons at the head of each column allow the data to be sorted by the values in the column in ascending or descending order. The existing functionality within the products screen to allow the user to query specific records will also be available in this page. There will be two buttons at the foot of the screen.

• New – this button will be used to add a new record, when this button is selected a new popup window will be displayed to create the record. A suggested layout is shown below.

[[Image:]]

There will be no information populated for a new record. A list of values will be provided for the product type, du type and vehicle type which the user must select from. The max du qty will be a numeric field all four fields must be entered. Duplicate records will not be allowed and the combination of product type, du type and vehicle type will make the record unique. If a duplicate is entered and appropriate error will be displayed. There will be 4 buttons on the screen

• New will clear the details currently displayed to allow the entry of another record
• Save will validate the details input save the information.
• Cancel will discard any changes and return to the main canvas
• Exit will close the popup window and return to the main canvas.

• Edit – this button will be used to amend the details of an existing record, when this button is selected the canvas above will be displayed however the details of the currently selected row will be used to populate the values displayed. The buttons and validation will have the same functionality as for adding new records.

CSV Import

The IMP package will be amended to add a new function PROCESS_VOLUMETRICS. The format of the CSV import file is as follows

Import Field	Database field
PRODUCT_TYPE	PROD_VEHICLE_VOLUME.PRODUCT_TYPE
DU_TYPE	PROD_VEHICLE_VOLUME.DU_TYPE
VEHICLE_TYPE	PROD_VEHICLE_VOLUME.VEHICLE_TYPE
MAX_DU_QTY	PROD_VEHICLE_VOLUME.MAX_DU_QTY

The data contained within the CSV file must be validated to ensure that all product types, du types and vehicle types already exist in C-TMS if any of the information proves to be invalid the record will be rejected.

The max_du_qty must be numeric if non numeric data is found the record will be rejected.

The PROD_VEHICLE_VOLUME table also has an ID column which will be populated using a new database sequence (SEQ_ PROD_VEHICLE_VOLUME) this will then be used as the primary key of the table.

The product type, du type and vehicle type combination make the record unique if a duplicate record is found the max_du_qty should be updated for that record.

All rejected records will be displayed with an appropriate error message to allow the user to correct errors and re-import the data.

Trip Planning

The trip planning screens TRIPSUM, TRIP_PLAN and EXECUTION will be amended to add a new option “Show Volumetric Information” to the right click menu in the unscheduled order well an example is displayed below from the trip plan screen, similar functionality will exist in the Trip Manipulation and Execution screens which will also require amendment.

[[Image:]]

When the “Show Volumetric Information” option is selected a new window will be displayed. The information from the PROD_VEHICLE_VOLUME table for the product types associated with the selected order will be displayed along with the maximum du qty allow for each and a percentage calculation of the quantities on the order to the maximum allowed for the vehicle. The Information on the screen will be display only. A suggested layout is shown below.

[[Image:]]

The information should be displayed in product type and percentage fill order to ensure that all vehicles associated with a given product are displayed together and the percentage values can be displayed highest to lowest. The action buttons at the head of each column will be used to sort the display by the data in the column in ascending or descending order. There will be only one button on the screen which when selected will return to the main screen. This functionality will be developed in the same way in all three of the trip planning screens.

Data Export

A new CSV extract process will be created to extract the volumetric information from the C-TMS database into a CSV format. This functionality will allow maintenance and amendments to be managed in excel and then re-imported using the CSV upload function mentioned above.

The generic Oracle reporting suite may be used to obtain the extracts an example of which is shown below

[[Image:]]

The system parameters ‘MTS_CSV_EXPORT_PATH’ and ‘MTS_CSV_EXPORT_PATH_ORS’ control where the output file is placed. The format of the extract should match the CSV format of the import records will be created in the ORS_MASTER_DATA and ORS_SQL tables for the import. The records can then be manipulated and re-imported.

Table Updates Required

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

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

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

Modules to be changed

Module Name	Module Type	Notes
PRODUCT.fmb	Form	Add new tab page
IMP.sql	Package	Add new import
TRIP_PLAN.fmb	Form	Add Volumetric display
TRIPSUM.fmb	Form	Add Volumetric display
EXECUTION.fmb	Form	Add Volumetric display

References

Ref No	Document Title & ID	Version	Date
1	EST-291365 MS-8KNH33 Volumetrics	1.0	13/09/11

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	10/10/11	Draft	Initial version	CAK
0.2	11/10/11	Draft	Reviewed	MJC
0.3	12/10/11	Draft	Revised	CAK
1.0	12/10/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager