292357: Difference between revisions
| Line 1: | Line 1: | ||
{{Doc_Title|System=FUNCTIONAL SPECIFICATION|Title=Reports|Reference=FS 292357 - PM8LZMWA|Version=1.0|Date=10/11/11|Sysver=10.7|Client=DHL C-TMS}} | {{Doc_Title|System=FUNCTIONAL SPECIFICATION|Title=Reports|Reference=FS 292357 - PM8LZMWA|Version=1.0|Date=10/11/11|Sysver=10.7|Client=DHL C-TMS}} | ||
== Client Requirement == | == Client Requirement == | ||
New Reports | New Reports | ||
Revision as of 12:50, 8 May 2012
DHL C-TMS
Reports
FUNCTIONAL SPECIFICATION - 10.7
10/11/11 - 1.0
Reference: FS 292357 - PM8LZMWA
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).
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:
- A non-conformance reason code added
- AND the arrival time at the delivery location is between the From Date/Time and To Date/Time parameters.
- 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
| EST 292357 PM-8LZMWA Reports | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Issued | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
