291972
DHL C-TMS
Dunelm Item Movements Extract
FUNCTIONAL SPECIFICATION - 10.7
30/09/2011 - 1.0
Reference: FS 291972 OB-8JWKBY
Functional Overview
Client Requirement
Change Request Summary:
Creation of new 'Dunelm Item Movements Extract' that will be used by Load hog Lids to track the Dunelm's totes through the x-dock network Steve Gooding/Peterboro/UK/NFC
Change Request Details:
Additional extract to be added to the DUNPRD database that we (DHL) can send to Loadhog on a daily / weekly basis to allow the to track Dunelm totes through the system.The extract needs to work like the current 'Dunelm Actual Unload' and 'Dunelm Actual Load' extract, where the data and time parameters the extract is run for is linked to the actual scan in and out times provided for each item by the WCS. The extract needs to work at item level. The following parameters must be present when running the extract, but for full details please see the attached .xls fileActual From Date (mandatory)Actual From Time (mandatory)Actual To Date (mandatory)Actual To Time (mandatory)DU Type (optional)P/O Number (optional)Manifest Number (optional)
Benefits identified as a result of the change:
Ability to provide external 3rd parties with information they need.
Solution
A new extract will be developed in the Dunelm CSV extract package.
The user will provide the following parameter values:
| Parameter Name | Required |
| Actual From Date | (mandatory) |
| Actual From Time | (mandatory) |
| Actual To Date | (mandatory) |
| Actual To Time | (mandatory) |
| DU Type | (optional) |
| Supplier | (optional) |
| Store | (optional) |
| P/O Number | (optional) |
| Manifest Number | (optional) |
Using this information, the system will identify all items which have been scanned in and out of the RDC based on the FROM and TO date/times and the du type/ Po number / manifest Number is provided.
The reason table will be used to identify the times that an item was received into the RDC and despatched from the RDC. Items received into the RDC will be assigned a reason code SU or MI and items despatched from the RDC will be assigned a reason code SL or ML. The order audit table will be used to identify the time an order was applied a POD.
Item identifiers will be tracked through the system to identify all occurrences. Split orders may be assigned the relevant reason codes or the order audit record for PODs.
The times for all occurrences of an item will be selected and then MIN or MAX applied to identify the relevant time.
Item Created will be based on the minimum time an occurrence of the item was created in the items table (first occurrence of the item)
Received RDC will be based on the minimum time an SU or MI reason code was assigned to an occurrence of the item.
Despatched RDC will be based on the minimum time an SL or ML reason code was assigned to an occurrence of the item.
POD Confirmed will be based on the maximum time an order which includes an occurrence of the item was applied a POD.
The extract will display the following information, based on the parameter values provided by the user:
| Field Name | Source |
| DU_TYPE | SCH_ORD_ITEMS.ITEM_DESCRIPTION |
| ITEM CREATED | SCH_ORD.CREATED_DATE |
| SUPPLIER CODE | SCH_ORD.FROM_LOC |
| SUPPLIER NAME | ORG_CUSTOMER.CUSTOMER_NAME |
| STORE ID | SCH_ORD.TO_LOC |
| STORE NAME | GEO_LOCATION.LOCATION_NAME |
| ITEM NUMBER | SCH_ORD_ITEMS.ITEM_IDENTIFER |
| CONSIGNMENT | SCH_ORD.OMS_REF |
| P/O NUMBER | SCH_ORD.EXTERNAL_REF |
| MANIFEST NO | SCH_ORD.BOOKING_REF |
| RECEIVED RDC | MIN(SCH_ORD_ITEM_REASONS.CREATED_DATE) |
| DESPATCHED RDC | MIN(SCH_ORD_ITEM_REASONS.CREATED_DATE) |
| POD CONFIRMED | SCH_ORD.POD_DATE |
| QTY | MAX(SCH_ORD_ITEMS.QTY_TO_DELIVER) |
Scope
This change will be applied to system version 10.7.0
Set-up
Pre-requisites
None
Menu Structure
Unchanged
Data
The new report will be added to the standing data table REP_REPORT to allow it to be selected from the standard reports form. The selection criteria will be added to REP_REPORT_PARAM. The parameters for the report will be
Actual From Date
Actual From Time
Actual To Date
Actual To Time
DU Type
PO Number
Manifest Number
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 window to allow the user to save or print the output. The export will be developed in the existing package DP_CSV_DUN.sql.
Seven 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
- DU Type – Drop down list
- Supplier – Drop down list
- Store - Free text field with look up.
- P/O Number – Free text field
- Manifest Number – Free text field.
Items will be considered for the export if they have a scanned in action and scanned out action that are between the From Date/Time and To Date/Time parameters. The items will also be restricted based on the DU Type, store, supplier, PO number and manifest number if provided.
The reason codes against an item, stored in SCH_ORD_ITEMS_REASONS, will be used to identify when an item was scanned in and out of the RDC. An item that has been scanned in will have a reason code of SU or MI. Items that have been scanned out will have a reason code of SL or ML. The time that a particular reason code is applied to the item is also stored on this table, allowing the export to determine which items are to be included.
The export will show the following columns.
| Field Name | Source |
| DU_TYPE | SCH_ORD_ITEMS.ITEM_DESCRIPTION |
| ITEM CREATED | SCH_ORD.CREATED_DATE |
| SUPPLIER CODE | SCH_ORD.FROM_LOC |
| SUPPLIER NAME | ORG_CUSTOMER.CUSTOMER_NAME |
| STORE ID | SCH_ORD.TO_LOC |
| STORE NAME | GEO_LOCATION.LOCATION_NAME |
| ITEM NUMBER | SCH_ORD_ITEMS.ITEM_IDENTIFER |
| CONSIGNMENT | SCH_ORD.OMS_REF |
| P/O NUMBER | SCH_ORD.EXTERNAL_REF |
| MANIFEST NO | SCH_ORD.BOOKING_REF |
| RECEIVED RDC | MIN(SCH_ORD_ITEM_REASONS.CREATED_DATE) |
| DESPATCHED RDC | MIN(SCH_ORD_ITEM_REASONS.CREATED_DATE) |
| POD CONFIRMED | SCH_ORD.POD_DATE |
| QTY | MAX(SCH_ORD_ITEMS.QTY_TO_DELIVER) |
The Item Created field will be based on the created date / time of the most recent order to contain this item. The most recent order that contains this item will be the one on which the item is not prefixed with an ‘X’.
The Item Created field will be based on the date and time that the order item record was created. This will therefore reflect the date and time that the supplier collection manifest was closed for the item.
The Received RDC field will be the time of the first occurrence of an SU or MI reason code against the item.
The Despatched RDC value will be the time of the first occurrence of an SL or ML reason code against the item.
The order information, PO Number, Manifest No and POD Confirmed, will be the information that belongs to the latest order that the item has been applied to. If the item is moved to a new order created during the inbound or outbound scan the order displayed in the extract should be the most recent. This should be the order on which the item is assigned and is not prefixed with an ‘X’.
Note – This export will only include Item level DUs used to move order consignments through the network. Empty DU’s returned from store are not tracked. Empty DU’s returned to supplier are not tracked. Empty DUs used to ‘square off’ TOTE blocks are not tracked. Tote Lids are not tracked.
References
| {Detail attachments to RIO here} | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed and Issued | ||||
| Spec referred | ||||
| Reviewed and Issued | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
