292357

DHL C-TMS

Reports

FUNCTIONAL SPECIFICATION - 10.7

10/11/11 - 1.0
Reference: FS 292357 - PM8LZMWA

[[Image:]]

DHL C-TMS

10.7

FUNCTIONAL SPECIFICATION 292357 PM-8LZMWA Reports

Version :

1.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_1771500641 1. Functional Overview3]

[#__RefHeading__3_1771500641 2. Set-up5]

[#__RefHeading__5_1771500641 3. Functional Description7]

[#__RefHeading__7_1771500641 Appendix A table updates REQUIRED8]

[#__RefHeading__9_1771500641 Appendix B Modules to be changed9]

[#__RefHeading__11_1771500641 Appendix C QUOTE & DoCuMENT History10]= Functional Overview =

Client Requirement

New Reports

1. Utilisation KPI 2. Reasons Listing 3. Resource Export

It is understood items 1 & 3 above have been removed from scope as per Andrew Philip’s email attached below.

[[Image:]]

Solution

2) Reasons Listing

A new CSV export will be written that will simply list order items with reason codes along with data from the corresponding order and delivery trip.

Selection criteria will be a date range and a depot.

The date will be compared against the orders’ actual arrival time at the delivery point.

The depot will be compared against the orders collection point (the manufacturer’s location is also the DHL depot as they are based at the site).

The proposed layout is as below:-

Both the original trip number (held on the trip in the Route Code field) and the generated trip id will need to be reported.

Stop will be the location name for the delivery point of the order.

PO Reference is also from the order.

The customer (external) reference from the order will also be displayed..

All other data is from the order item non-conformance table apart from the reason text. That will be obtained from the description held against the reason code:

The following additional columns will be included on the report:-

Planned DU, Planned DU qty, Actual DU qty, Item, Item Desc, Planned Item Qty, Actual Item Qty

A variance column will be available for each of the quantity fields.

Scope

This change will be applied to system version 10.7

Set-up

Pre-requisites

None

Menu Structure

Administration > File Interfaces > Exports

Data

The new export will be added to the standing data to allow it to be selected from the standard exports form. The selection criteria for the export will also be added as parameters.

REP_REPORT

Column	Value
Name	Reasons Listing
Report Type	CSV
Filename	reasons_listing
Proc Name	DP_CSV3.reasons_listing_export
Default Queue	NULL
Printer Type	NULL
Show PFORM	NULL
Orientation	LANDSCAPE
Active	NULL
Display Name	NULL
Report Extension	NULL

REP_REPORT_PARAM

Column	Value
Report Name	Reasons Listing
Report Type	CSV
Record 1
Param Type	P_START_DATETIME
Param Name	P_START_DATETIME
Conditional	M
Default Value Type	TODAY
Default Value	TODAY:00:01
Param Title	Date From
Format Mask	NULL
Sql String	NULL
LOV Title	NULL
Col 1 Title	NULL
Col 2 Title	NULL
Col 3 Title	NULL
Report List	N
Record 2
Param Type	P_END_DATETIME
Param Name	P_END_DATETIME
Conditional	M
Default Value Type	TODAY
Default Value	TODAY:23:59
Param Title	Date To
Format Mask	NULL
Sql String	NULL
LOV Title	NULL
Col 1 Title	NULL
Col 2 Title	NULL
Col 3 Title	NULL
Report List	N
Record 3
Param Type	P_DEPOT
Param Name	P_DEPOT
Conditional	M
Default Value Type	NULL
Default Value	NULL
Param Title	Depot
Format Mask	NULL
Sql String	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'
LOV Title	Depot
Col 1 Title	Depot Id
Col 2 Title	Name
Col 3 Title	NULL
Report List	N

Implementation Advice

For the export to be available in the Exports screen, it must be promoted the relevant group(s).

[[Image:]]

Functional Description

A new CSV export will be created in CTMS. The export will be available in the existing Exports screen. When the export is run it will display in a new browser window or MS Excel depending on the user’s local configuration. This will allow the user to save or print the output. The export will be developed in the existing package DP_CSV3.sql.

Three parameters will be added to the REP_REPORT_PARAM table and these parameters will be available in the Exports screen.

• Actual From Date / Time – Date and time field
• Actual To Date / Time – Date and time field
• Depot – Free text with lookup on valid depots for user.

Order Items will be displayed in the export if:

1. A non-conformance reason code added
2. AND the arrival time at the delivery location is between the From Date/Time and To Date/Time parameters.
3. AND the Depot code selected in the selection parameters the Order’s collection location

The export will show the following columns.

Field Name	Source
Trip	SCH_TRIP.TRIP_ID (Derived by linking from sch_ord_items to sch_ord to sch_haulage_activity (based on to_loc) to sch_trip_stop to sch_trip)
Route	SCH_TRIP.ROUTE_CODE (Derived by linking from sch_ord_items to sch_ord to sch_haulage_activity (based on to_loc) to sch_trip_stop to sch_trip.)
Stop	GEO_LOCATION.LOCATION_ID (Derived by linking from sch_ord_items to sch_ord to sch_haulage_activity (based on to_loc) to sch_trip_stop.)
PO Reference	SCH_ORD.BOOKING_REF (Derived by linking from sch_ord_items to sch_ord)
External Reference	SCH_ORD.EXTERNAL_REF (Derived by linking from sch_ord_items to sch_ord)
Reason Code	SCH_ORD_ITEMS_REASONS.REASON_CODE (Derived by linking from sch_ord_items to sch_ord_items_reasons)
Reason Text	SCH_ REASON_CODES.DESCRIPTION (Derived by linking from sch_ord_items to sch_reason_codes (usage = ITEM_NON_CON)
Comment	SCH_ORD_ITEMS_REASONS.REASON_COMMENTS (Derived by linking from sch_ord_items to sch_ord_items_reasons)
User	SCH_ORD_ITEMS_REASONS.CREATED_BY (Derived by linking from sch_ord_items to sch_ord_items_reasons)
DU Type	SCH_ORDER_LINE.DU_TYPE (Derived by linking from sch_ord_items to sch_order_line using DU_TYPE)
Planned DU Qty	SCH_ORDER_LINE.QUANTITY (Derived by linking from sch_ord_items to sch_order_line using DU_TYPE)
Actual DU Qty	SCH_ORDER_LINE.ACTUAL_QUANTITY (Derived by linking from sch_ord_items to sch_order_line using DU_TYPE)
DU Qty Variance	Calculation = SCH_ORDER_LINE.ACTUAL_QUANTITY - SCH_ORDER_LINE.QUANTITY
Item ID	SCH_ORD_ITEMS.ITEM_IDENTIFIER
Item Description	SCH_ORD_ITEMS.ITEM_DESCRIPTION
Planned Item Qty	SCH_ORD_ITEMS.QTY_ORDERED
Actual Item Qty	SCH_ORD_ITEMS.QTY_DELIVERED
Item Qty Variance	Calculation = SCH_ORD_ITEMS.QTY_DELIVERED -SCH_ORD_ITEMS.QTY_ORDERED

Table Updates Required

‘No table changes are required for this development.

Modules to be changed

Module Name	Module Type	Notes
DP_CSV3.sql	Packaged	Add new extract

References

Ref No	Document Title & ID	Version	Date
1	EST 292357 PM-8LZMWA Reports	1.0	19/10/2011

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	10/11/11	Draft	Initial version	MJC
1.0	10/11/11	Issue	Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager