286751

DHL CTMS

Generate Left Offs Report

FUNCTIONAL SPECIFICATION - 10.6

- 1.0

Reference: FS 286751 OB-8ERCHY

Functional Overview

Client Requirement

To produce an extract (rather than a report) which is a system version of the manual listing currently done.

The extract needs to report all items in the building which have a scanned in code, but do not have a scanned out code. PLEASE NOTE - in order for this extract not to build up and up, if a user manually adds a 'SL' code to an item it should not appear on this report.

The following parameters need to be included in the extract...

'Days Back' (mandatory) - this parameter is the number of actual days back from the time at which the extract is run that the system needs to report items with a scanned in reason code, but without a scanned out reason code. So if the extract is run on a Thursday @ 06:00 with '2' in the days back field, the extract would report all items that had been scanned in since the Tuesday (2 days back) @ 06:00, but not scanned out

Trip ID (optional) - this optional parameter should allow a user to query items for a specific trip number, if left blank the extract should display items for all trips

Store (optional) - this optional should allow the user to query items for a specific store ID, if left blank the extract should display items for all stores

Please see attached design with comments added to relevant fields for explanations

Solution

A new CSV extract will be created called LEFT_OFFS_EXTRACT. It will run from the Exports screen and will produce a CSV containing the data retrieved. The extract will be added to the existing DP_CSV_DUN.sql package.

The parameters used to create the extract will be:

•Days Back (Mandatory) – Number of days to search for data prior to current date (sysdate).

•Trip Id (Optional)

•Store (Optional)

The extract will show the following information:

•Planned Trip Number – Trip Id that the item was originally set to despatch on.

•Store ID – To Location of the order

•Store Name – Name associated to the To Location

•P/O – External Reference of the order

•Original Con – Oms Ref item was originally attached to

•New con – Oms Ref the item is currently attached to

•Supplier Code – From location of the order

•Supplier Name – Name associated to the From Location

•Du Type – Despatch Unit Type of the Item

•Qty – Item quantity (should be 1)

•Item Number – Item Identifier

•Aka Code – Items Aka code

To retrieve the items that have been scanned in, but not scanned out the extract will look for items that have a reason code of SU, LN, ON, EN, LP, OP or EP but do not have a reason code of SL, UL, OL or XL.

To calculate the Original Con (original consignment), the extract will search for the item identifier preceded by an ‘X’. If a record is found then the OMS Ref associated with this record will be considered as the Original Con. If more than one record is returned, due to the item being split more than once, then the earliest OMS Ref will be used as the Original Con.

Item Identifiers beginning with ‘X’ will not be included in the extract.

Scope

This change will be applied to system version 10.6.

Set-up

Data

A record will be added to REP_REPORT for this export.

3 records will be added to REP_REPORT_PARAM, one for each parameter.

The export will be promoted to the Admin group.

Functional Description

A new export will be added to DP_CSV_DUN.sql called LEFT_OFFS_EXTRACT. The export will run from the Exports screen and will be produced in CSV format.

The following parameters will be used to generate the Export:-

• Days Back (mandatory)
• Trip ID (optional)
• Store (optional)

The ‘Days Back’ parameter will be a numeric field. This will determine how many days previous to the current date the export will retrieve data from. So if the extract is run on 10/05/2011 at 12:00, and the parameter is 2, then the extract will retrieve items that have been scanned in since 08/05/2011 12:00 but not scanned out.

The Trip ID parameter is optional and if used will restrict the items to those on the specific trip chosen. The trip will be the trip that the item is scheduled to be despatched on.

The Store parameter is optional and if used will restrict the items to those for the specific store chosen.

The extract will display the following information:-

• Planned Trip Number – SCH_TRIP.TRIP_ID for the trip that the item was originally set to despatch on.
• Store ID – SCH_ORD.TO_LOC for the order that contains the item.
• Store Name – GEO_LOCATION.LOCATION_NAME for the store id
• P/O – SCH_ORD.EXTERNAL_REF for the order that contains the item.
• Original Con – SCH_ORD.OMS_REF that the order was originally attached to (see below for more details).
• New Con – SCH_ORD.OMS_REF for the order that the item is currently on.
• Supplier Code – SCH_ORD.FROM_LOC for the order that contains the item.
• Supplier Name – GEO_LOCATION.LOCATION_NAME for the supplier code
• DU Type – SCH_ORD_ITEMS.DU_TYPE of the item
• Qty – SCH_ORD_ITEMS.QTY_ORDERED of the item
• AKA Code – SCH_ORD_ITEMS.ITEM_AKA_CODE for the item.

Items will be included on the report if they have an unload reason code in SCH_ORD_ITEMS_REASONS but do not have a load reason code. The unload reason codes are SU, LN, ON, EN, LP, OP and EP. The load reason codes are SL, UL, OL and XL.

The Original Con field will be populated with the earliest OMS Ref that included the item. To find this the extract will search for the Item Identifier preceded by an ‘X’. This order will be considered as the original consignment. It is possible that the order may have been split more than once; if this is the case more than one order will be retrieved. If this occurs then the earliest OMS Ref will be used as the Original Con field.

Document History

Version	Date	Status	Reason	Initials
0.1	11/04/11	Draft	Initial version	DNG
1.0	13/04/11	Issue	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager