292987: Difference between revisions

From CTMS
VisualWikitext
No edit summary
Line 808: Line 808:
[[Image:]]
[[Image:]]


#
#:
'''Modules to be changed'''
#::
#:::
#::::
#:::::
#::::::
#:::::::
#:::::::# '''Modules to be changed'''
 


{| style="border-spacing:0;"
{| style="border-spacing:0;"
Line 850: Line 842:


|}
|}


'''References'''
'''References'''


{| style="border-spacing:0;"
{| style="border-spacing:0;"
Line 879: Line 871:


|}
|}
'''Glossary'''
'''Glossary'''


{| style="border-spacing:0;"
{| style="border-spacing:0;"
Line 899: Line 892:


|}
|}
'''Document History '''
'''Document History '''


{| style="border-spacing:0;"
{| style="border-spacing:0;"

Revision as of 14:40, 26 April 2012

[[Image:]]


DHL C-TMS


10.7


FUNCTIONAL SPECIFICATION


292987-TH-8MLJTQ C-TMS reports to be produced



Version :
2.0

Copyright OBS Logistics © 2011


The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied


Contents


[#__RefHeading__1_1810204259 1. Functional Overview3]

[#__RefHeading__3_1810204259 2. Set-up7]

[#__RefHeading__5_1810204259 3. Functional Description8]

[#__RefHeading__7_1810204259 Appendix A table updates REQUIRED13]

[#__RefHeading__9_1810204259 Appendix B Modules to be changed14]

[#__RefHeading__11_1810204259 Appendix C QUOTE & DoCuMENT History15]= Functional Overview =

Client Requirement

Development of 3 new extracts.


  • C-TMS Activity Extract
  • C-TMS Delivery Overview
  • C-TMS Provider Revenue

Solution

Three new CSV extract reports will be developed to be available to be run from the Exports screen the details of each extract are described below.


C-TMS Activity Extract(Trip Level)


The following fields are required in the extract


Name C-TMS field C-TMS Table
TO_PLANNING_REG PLANNING_REGION GEO_LOCATION record for the to_loc of the order
SCHEDULE DATE SCHED_NAME SCH_TRIP
TRIP ID TRIP_ID SCH_TRIP
CARRIER CARRIER_ID SCH_TRIP
CARRIER_TYPE CARRIER_TYPE_ID RES_CARRIER
DRIVER ID FORNAME ’ ‘ SURNAME SCH_TRIP.driver_id join to RES_PERSON.id
VEHICLE ID TRACTOR_ID SCH_TRIP
TRAILER ID TRAILER_ID SCH_TRIP
TRAILER TYPE DESCRIPTION RES_TRAILER_TYPE
FROM LOC LOCATION_ID SCH_TRIP_STOP (the start location of the trip)
FROM TOWN TOWN GEO_LOCATION (town for the above location id)
FROM POSTCODE POSTCODE GEO_LOCATION(postcode for the above location id)
TO LOC LOCATION_ID SCH_TRIP_STOP(location id of the last delivery on the trip)
TO LOC TOWN TOWN GEO_LOCATION(town for the above location id)
TO LOC POSTCODE POSTCODE GEO_LOCATION(postcode for the above location id)
FIRST DEL TIME EARLY_DEL SCH_ORD(indicates the early del Date/Time of the first order on the trip)
SHIPPMENT ROUTE_CODE SCH_TRIP
TOTAL DEL NOTES CALCULATED Total count of orders on the trip
COMMENTS ORDER_COMMENTS SCH_ORD(taken from the first order on the trip)
RPE CALCULATED Sum of the SCH_ORD.total_rpe for all orders on the trip
REVENUE CALCULATED Sum of the SCH_ORD.ord_revenue for all orders on the trip
COST CALCULATED Sum of the SCH_ORD.ord_cost for all orders on the trip
POD RECIEVED DERIVED Will only be set if all orders on the trip have the SCH_ORD.POD set

There will be three parameters available to control the records selected,

  • From Schedule
  • To Schedule (the schedules will be used to select trips with a schedule name between these 2 values)
  • Depot will restrict orders selected by Owning Depot. If the user only has one owning depot the list will default to this else the owning depot can be selected from the list. If no depot is selected all relevant depots will be used

Delivery Overview (Order Level)


The Following fields are required in the extract


Name C-TMS field C-TMS Table
TO_PLANNING_REG PLANNING_REGION GEO_LOCATION planning region of the to_loc of the order
SCHEDULE DATE SCHED_NAME SCH_TRIP
TRIP ID TRIP_ID SCH_TRIP
DROP NUMBER CALCULATED Taken from trip details so delivery 1 is drop one, delivery 2 is drop 2 etc
CARRIER CARRIER_ID SCH_TRIP
CARRIER_TYPE_ID CARRIER_TYPE_ID RES_CARRIER
DRIVER ID FORNAME ’ ‘ SURNAME SCH_TRIP.driver_id join to RES_PERSON.id
VEHICLE ID TRACTOR_ID SCH_TRIP
TRAILER ID TRAILER_ID SCH_TRIP
TRAILER TYPE TRAILER_TYPE RES_TRAILER_TYPE
SHIPPMENT ROUTE_CODE SCH_TRIP
DEL NOTE EXTERNAL_REF SCH_ORD
DEL TYPE DELIVERY_TYPE_ID SCH_ORD
FROM LOC FROM_LOC SCH_ORD
FROM TOWN TOWN GEO_LOCATION(town of above location id)
FROM POSTCODE POSTCODE GEO_LOCATION(postcode of above location id)
TO LOC TO_LOC SCH_ORD
TO LOC TOWN TOWN GEO_LOCATION(town of above location id)
TO POSTCODE POSTCODE GEO_LOCATION(postcode of above location id)
FIRST DEL TIME EARLY_DEL SCH_ORD
MILES DISTANCE SCH_ORD(this value may be in km’s and will need to be calculated correctly)
WEIGHT TOTAL_WEIGHT SCH_ORD
RPE TOTAL_RPE SCH_ORD
ORDER REVENUE ORD_REVENUE SCH_ORD
TRIP COST TRIP_COST SCH_TRIP(for multiple order trips this will only be displayed on the first order)
POD CONFIRMED POD SCH_ORD
MANUAL FINANCE DERIVED This field will be set to ‘Y ‘if any manual finance has been associated with this order/trip

There will be four parameters available to control the records selected,

  • From Schedule
  • To Schedule (the schedules will be used to select trips with a schedule name between these 2 values)
  • Depot will restrict the trips selected by Owning Depot . If the user only has one owning depot the list will default to this else the owning depot can be selected from the list. If no depot is selected all relevant depots will be used
  • POD confirmed will restrict orders selected by proof of delivery the value can be Yes/No/Both

Provider Revenue (Carrier Level)


The Following fields are required in the extract


Name C-TMS field C-TMS Table
DEPOT OWING_DEPOT SCH_TRIP
CARRIER ID CARRIER ID SCH_TRIP
CARRIER_TYPE_ID CARRIER_TYPE_ID RES_CARRIER
TOTAL TRIPS CALCULATED Total trips for the carrier within schedule range
TOTAL ORDERS CALCULATED Total of orders on the trips
TOTAL REVENUE CALCULATED Total SCH_ORD.ord_revenue of orders
TOTAL COST CALCULATED Total SCH_ORD.ord_cost of orders
MARGIN CALCULATED TOTAL REVENUE – TOTAL COST
% MARGIN CALCULATED TOTAL REVENUE – TOTAL COST as a % value

There will be Three parameters available to control the records selected all totals will be produced based on the trip carrier,

  • From Schedule
  • To Schedule (the schedules will be used to select trips with a schedule name between these 2 values)
  • Depot will restrict the trips selected by Owning Depot .If the user only has one owning depot the list will default to this else the owning depot can be selected from the list. If no depot is selected all relevant depots will be used

Scope

This change will be applied to system version 10.7

Set-up

Pre-requisites

None


Menu Structure

Unchanged


Data

New entries will be inserted into the REP_REPORT and REP_REPORT_PARAMS tables to control the reports.


Implementation Advice

A system super user will be required to grant access to the required extracts

[[Image:]]

Functional Description

C-TMS Activity Extract (Trip Level)

An extract will be developed and will be available to be run from the Exports Screen and example of which is shown below


[[Image:]]


The user will be prompted to enter From Schedule, To Schedule and the Owning Depot.

The following information will be required in the extract.


Name C-TMS field C-TMS Table
TO_PLANNING_REG PLANNING_REGION GEO_LOCATION for to_loc of the order
SCHEDULE DATE SCHED_NAME SCH_TRIP
TRIP ID TRIP_ID SCH_TRIP
CARRIER CARRIER_ID SCH_TRIP
CARRIER_TYPE_ID CARRIER_TYPE_ID RES_CARRIER
DRIVER ID FORNAME ’ ‘ SURNAME SCH_TRIP.driver_id join to RES_PERSON.id
VEHICLE ID TRACTOR_ID SCH_TRIP
TRAILER ID TRAILER_ID SCH_TRIP
TRAILER TYPE DESCRIPTION RES_TRAILER_TYPE
FROM LOC LOCATION_ID SCH_TRIP_STOP (the start location of the trip)
FROM TOWN TOWN GEO_LOCATION (town for the above location id)
FROM POSTCODE POSTCODE GEO_LOCATION(postcode for the above location id)
TO LOC LOCATION_ID SCH_TRIP_STOP(location id of the last delivery on the trip)
TO LOC TOWN TOWN GEO_LOCATION(town for the above location id)
TO LOC POSTCODE POSTCODE GEO_LOCATION(postcode for the above location id)
FIRST DEL TIME EARLY_DEL SCH_ORD(indicates the early del Date/Time of the first order on the trip)
SHIPPMENT ROUTE_CODE SCH_TRIP
TOTAL DEL NOTES CALCULATED Total count of orders on the trip
COMMENTS ORDER_COMMENTS SCH_ORD(taken from the first order on the trip)
RPE CALCULATED Sum of the SCH_ORD.total_rpe for all orders on the trip
REVENUE CALCULATED Sum of the SCH_ORD.ord_revenue for all orders on the trip
COST CALCULATED Sum of the SCH_ORD.ord_cost for all orders on the trip
POD RECIEVED DERIVED Will only be set if all orders on the trip have the SCH_ORD.POD set

The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The schedules and the owning depot parameters will be used to control the information selected. If the user only has one owning depot the list will default to this value. If no owning depot is selected all depots relevant to the user will be used.

Delivery Overview (Order Level)

An extract will be developed and will be available to be run from the Exports Screen and example of which is shown below:


[[Image:]]


The user will be required to enter the From and To Schedule, the Owning Depot and optionally a Proof of Delivery Y/N/ALL option.


The flowing information is required in the extract


Name C-TMS field C-TMS Table
TO_PLANNING_REG PLANNING_REGION GEO_LOCATION planning region of to_loc
SCHEDULE DATE SCHED_NAME SCH_TRIP
TRIP ID TRIP_ID SCH_TRIP
DROP NUMBER CALCULATED Taken from trip details so delivery 1 is drop one, delivery 2 is drop 2 etc
CARRIER CARRIER_ID SCH_TRIP
CARRIER_TYPE_ID CARRIER_TYPE_ID RES_CARRIER
DRIVER ID FORNAME ’ ‘ SURNAME SCH_TRIP.driver_id join to RES_PERSON.id
VEHICLE ID TRACTOR_ID SCH_TRIP
TRAILER ID TRAILER_ID SCH_TRIP
TRAILER TYPE TRAILER_TYPE RES_TRAILER_TYPE
SHIPPMENT ROUTE_CODE SCH_TRIP
DEL NOTE EXTERNAL_REF SCH_ORD
DEL TYPE DELIVERY_TYPE_ID SCH_ORD
FROM LOC FROM_LOC SCH_ORD
FROM TOWN TOWN GEO_LOCATION(town of above location id)
FROM POSTCODE POSTCODE GEO_LOCATION(postcode of above location id)
TO LOC TO_LOC SCH_ORD
TO LOC TOWN TOWN GEO_LOCATION(town of above location id)
TO POSTCODE POSTCODE GEO_LOCATION(postcode of above location id)
FIRST DEL TIME EARLY_DEL SCH_ORD
MILES DISTANCE SCH_ORD(this value may be in km’s and will need to be calculated correctly)
WEIGHT TOTAL_WEIGHT SCH_ORD
RPE TOTAL_RPE SCH_ORD
ORDER REVENUE ORD_REVENUE SCH_ORD
TRIP COST TRIP_COST SCH_TRIP(for multiple order trips this will only be displayed on the first order)
POD CONFIRMED POD SCH_ORD
MANUAL FINANCE DERIVED This field will be set to ‘Y ‘if any manual finance has been associated with this order/trip

The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The schedules and the owning depot parameters will be used to control the information selected. If the user only has one owning depot the list will default to this value. If no owning depot is selected all depots relevant to the user will be used. The Proof of delivery parameter will be set to Yes/No or Both this will control records extracted by proof of delivery.


Provider Revenue (Carrier Level)

An extract will be developed and will be available to be run from the Exports Screen and example of which is shown below:


[[Image:]]


The user will be required to enter the From and To Schedules and the Owning Depot.

The extract will contain the following information:


Name C-TMS field C-TMS Table
DEPOT OWNING_DEPOT SCH_TRIP
CARRIER ID CARRIER ID SCH_TRIP
CARRIER_TYPE_ID CARRIER_TYPE_ID RES_CARRIER
TOTAL TRIPS CALCULATED Total trips for the carrier within schedule range
TOTAL ORDERS CALCULATED Total of orders on the trips
TOTAL REVENUE CALCULATED Total SCH_ORD.ord_revenue of orders
TOTAL COST CALCULATED Total SCH_ORD.ord_cost of orders
MARGIN CALCULATED TOTAL REVENUE – TOTAL COST
% MARGIN CALCULATED TOTAL REVENUE – TOTAL COST as a % value

The extract will be developed into the standard CSV processing package. A new procedure to select the data and a new procedure to write the data to the extract file will be added to the existing program. The schedules and the owning depot parameters will be used to control the information selected. If the user only has one owning depot the list will default to this value.If no owning depot is selected all depots relevant to the user will be used. All totals produced will be created at carrier level within the specified parameters


    1. table updates REQUIRED

[[Image:]]


[[Image:]]


Modules to be changed

Module Name Module Type Notes
DP_CSV5.sql Package Add new extracts


References

Ref No
Document Title & ID
Version
Date
1
 EST-292987 TH-8MLJTQ
0.1
18/10/11


Glossary

Term or Acronym
Meaning
C-TMS Calidus TMS


Document History

Version
Date
Status
Reason
Initials
0.1
21/10/11
Draft
 Initial version
CAK
0.2
24/10/11
Draft
 Reviewed
MJC
1.0
24/10/11
Issue
 Issued
MJC
1.1
27/10/11
Draft
 Revised
CAK
1.2
31/10/11
Draft
 Revised
CAK
2.0
01/11/11
Issue
 Reviewed and Issued
MJC

AUTHORISED BY

Matt Crisford Development Manager
Peter Greer TMSCC MTS Product Manager
Retrieved from ‘https://calidusassist.adcservices.apteancloud.com/calidus-assist/MTS/index.php?title=292987&oldid=4835’