291369
DHL C-TMS
Develop Driver's Manifest
FUNCTIONAL SPECIFICATION - 10.7
21/10/11 - 2.0
Reference: 291169 AR-8KNK2F
Client Requirement
Change Request Summary:
ManifestPaul McGoran/Manchester/UK/NFC
Change Request Details:
Develop Driver’s Manifest/POD and allow output to be emailed / faxed / printed to carrier or fleet base location.
Benefits identified as a result of the change:
Required for Implementation
Solution
Data Set up:
The name of the drivers manifest must be setup in the MANIFEST_NAME system parameter with the appropriate cost centre value. The value of the manifest name will be NR_DRIVER_MAN. This will then control which manifest is sent when the trip is set to ACCEPTED. The details of email addresses/fax numbers should be stored against the Carrier in the message maintenance form. The manifest should be produced when:
- The trip is set to ACCEPTED via the Single and Brief/Combine and Brief functionality in Carrier Trip Planning
- The right click Brief Trip option in Carrier Trip Planning
- The right click Re-Brief Trip option in Carrier Trip Planning
- The Status of the trip is set to ACCEPTED in Trip Manipulation, Trip Planning, Execution
The necessary functionality to produce the manifest already exists within the Carrier Trip Planning Form, Trip Manipulation, Trip Planning, Execution in addition whenever the Trip status changes to ACCEPTED and no change is needed as part of this RIO.
Drivers Manifest:
A PDF format ‘Drivers Manifest report’ (NR_DRIVER_MAN) will be developed. It is understood there is no requirement to produce this report in an MS Excel format. The new report will be produced via a database package MSG_CONSTRUCTOR. The code to produce and reformat the manifest into the correct format for emailing or sending via zetafax already exists in this package and will not require amendment.
Source data fields are:
- Journey Number = Concatenated Schedule date-Trip-Order
- EFX Number = sch_trip.EFX number
- Haulier = Assigned carrier name
- Briefed On = Action date (Status change to ACCEPTED)
- Briefed by = Action By from sch trip audit
- Collection Details = Address Details of the From Loc
- Delivery Details = Address Details of the To Loc
- Collection Site Notes = GEO Location from Comments
- Delivery Site Notes = GEO Location to Comments
- Order Number = OMS ref of each order at this stop
- Item number = sch_ord_items item identifier
- Length = sch_order_line.length
- Product description = sch_ord_items.item_description
- Qty = sch_order_line.quantity
- Weight = sch_order_line.weight
- Quantity Collected sch_order_line.actual_quantity
The order details can be processed over numerous pages as shown in the examples, the Comments and hours worked etc will only be displayed on the last page of the manifest.
The report will also be available to run from the Reports screen and may be run for the following values
- Carrier Name
- Group Name
- Customer
- From Sched
- To Sched
- Trip Id
- Owning Depot
The report should be built to include these values as filters in the report.
Scope
This change will be applied to system 10.7.0
Set-up
Pre-requisites
None
Menu Structure
‘Unchanged’
Data
A new record will be added to the REP_REPORT table and the REP_REPORT_PARAM table.
| Name | NR_MANF |
| Report_Type | REP |
| Filename | NR_DRIVER_MAN.rep |
| Printer_type | Laser |
| Orientation | Portrait |
7 new records will be added to the REP_REPORT_PARAM table allowing the report to be run for a specific trip_id, from and to schedules, customer, carrier, group name and owning depot.
| Report_Name | NR_MANF |
| Report_Type | REP |
| Param_Type | PG_FFCHAR1 |
| Param_Name | TRIP_ID |
| Conditional | O |
| Param_Title | Trip ID |
| Report_Name | NR_MANF |
| Report_Type | REP |
| Param_Type | PG_SELECT_LIST1 |
| Param_Name | P_START_SCHED |
| Conditional | O |
| Param_Title | From Schedule |
| Report_Name | NR_MANF |
| Report_Type | REP |
| Param_Type | PG_SELECT_LIST2 |
| Param_Name | P_END_SCHED |
| Conditional | O |
| Param_Title | To Schedule |
| Report_Name | NR_MANF |
| Report_Type | REP |
| Param_Type | PG_SELECT_LIST3 |
| Param_Name | P_CUSTOMER |
| Conditional | O |
| Param_Title | Customer |
| Param_Title | Carier |
| Report_Name | NR_MANF |
| Report_Type | REP |
| Param_Type | PG_SELECT_LIST4 |
| Param_Name | P_CARRIER |
| Conditional | O |
| Param_Title | Carrier |
| Param_Title | Group Name |
| Report_Name | NR_MANF |
| Report_Type | REP |
| Param_Type | PG_SELECT_LIST5 |
| Param_Name | P_GROUP_NAME |
| Conditional | O |
| Param_Title | Group Name |
| Param_Title | Owning Depot |
| Report_Name | NR_MANF |
| Report_Type | REP |
| Param_Type | PG_LOV1 |
| Param_Name | P_DEPOT |
| Conditional | O |
| Param_Title | Owning Depot |
The SQL_STRING column for all of the above columns other than the TRIP ID should be populated with the SQL statement required to produce the required list of values for the Report Form.
Implementation Advice
A new cost centre based system parameter will be created and will need to be set by a system super user to specify the report name for the manifest.
| PARAM_NAME | MANIFEST_NAME |
| VALUE | NR_DIRVER_MAN |
| DESCRIPTION | Indicates which manifest report to distribute based on the cost centre of the trip. |
| CONFIG_BY | COST_CENTRE |
| CONFIG_BY_VALUE | NRCC |
A super user will also be required to provide access to the report to the relevant user groups. This will be done using the Access Control screen.
Functional Description
Report Design
The report will be created using Oracle Reports and will be available to run on an ad-hoc basis from the Reports screen in C-TMS. When running the report from this screen, the user will be able to select records using TRIP_ID, SCHEDULE_FROM, SCHEDULE_TO, CUSTOMER, CARRIER ID, GROUP NAME and OWNING_DEPOT.
The report will be written as a header/ detail report, where the header will display Trip and stop information and the details will display the order information from each stop on the trip. Each stop will represent a new manifest.
The format of the report will be based on the existing NR Manifest.
| JOURNEY NUMBER | SCH_TRIP.SCHED_NAME | - | SCH_TRIP.TRIP_ID |
| EFX NUMBER | SCH_TRIP.EFX_NUMBER | ||
| HAULIER | SCH_TRIP.CARRIER_ID | ||
| BRIEFED_ON | SCH_TRIP_AUDIT.ACTION_DATE (trip status changed to ACCEPTED) | ||
| BRIEFED_BY | SCH_TRIP_AUDIT.ACTION_BY (trip status changed to ACCEPTED) | ||
| COLLECTION_DETAILS | GEO_LOCATION (where location_id = sch_ord.from_loc joined by sch_trip_stop and sch_haulage_activity) | ||
| DELIVERY_DETAILS | GEO_LOCATION (where location_id = sch_ord.to_loc joined by sch_trip_stop and sch_haulage_activity) | ||
| COLLECTION_SITE_NOTES | GEO_LOCATION.COMMENTS(where location_id = sch_ord.from_loc joined by sch_trip_stop and sch_haulage_activity) | SCH_ORD_INFORMATION.FROM_LOC_COMMENT | |
| DELIVERY_SITE_NOTES | GEO_LOCATION.COMMENTS(where location_id = sch_ord.to_loc joined by sch_trip_stop and sch_haulage_activity) | SCH_ORD_INFORMATION.TO_LOC_COMMENT |
The delivery and collection notes will be based on a concatenation of specific location comments held at location level and order location comments held at the order level.
The header will also include the DHL and NR logos. And may consist of a number of pages depending how many orders are present at each stop. The number of hours worked and signature boxes will only be displayed on the last page of each report.
The following data will be selected and displayed for Orders:
| ORDER NUMBER | SCH_ORD.OMS_REF |
| ITEM_NUMBER | SCH_ORD_ITEMS.ITEM_NUMBER |
| LENGTH | SCH_ORDER_LINE.FOOTPRINT |
| PRODUCT DESCRIPTION | SCH_ORD_ITEMS.ITEM_DESCRIPTION |
| QTY | SCH_ORDER_LINE.QUANTYITY |
| WEIGHT | SCH_ORDER_LINE.WEIGHT |
| QUANTITY COLLECTED | SCH_ORDER_LINE.ACTUAL_QUANTITY |
Samples of the existing report are shown below
Cover Sheet Single
Cover Sheet2 Delivery
Cover Sheet Single Many Prod
Auto Faxing and E-mailing
When a trip is set to Accepted status in C-TMS or the trip is re-briefed using the carrier trip planning screen, these actions will be the triggers to create and distribute the Manifest report to the relevant recipient(s). The report will be distributed based on the owning depot location. Users will be able to configure how a depot will receive the manifest report using the Message Maintenance screen in C-TMS
[[Image:]]
The e-mail subject will be derived from the existing system parameter MSG_SUBJECT_MANIFEST.
[[Image:]]
This is currently set up to display the Text ‘Transport Confirmation – Ref’ followed by the trip id. This RIO will alter the system parameter to be a cost centre parameter and the NR specific text added as appropriate.
When the trip is set to Accepted or the trip is re-briefed, a record will be inserted into the Message control table to generate the NR_DRIVER_MAN report for the current trip. The record will wait to be processed by the existing message processing database job which runs every 5 minutes. There are no changes required to this process.
Table Updates Required
No table changes are required for this development.
Modules to be changed
| Module Name | Module Type | Notes |
| NR_DRIVER_MAN.rdf | Report | NR Drivers Manifest |
References
| EST 291369 MS-8KNHBA Driver's Manifest v1.0 | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed | ||||
| Revised | ||||
| Reviewed | ||||
| Revised | ||||
| Reviewed and Issued | ||||
| Revised following Client review | ||||
| Reviewed |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
