264542
264542 - NW-7RGULE/ Slot Utilisation Report
Copyright OBS Logistics © 2010
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
FUNCTIONAL OVERVIEW
Client Requirement
Create a new Report from MTS to allow utilisation management from Bookings. This Report should be run based on the following criteria:
Schedule From Loc (include selection of Location Type i.e. Supplier, RDC) ALL to be included as an option
To Loc (include selection of Location Type i.e. RDC, Branch) ALL to be included as an option
Variance Threshold (Only variances greater than this value to be displayed)
The Report should display the following data:
- Schedule
- From Loc
- To Loc
- Planned QTY (Taken from Bookings form)
- Variance QTY (Taken from Bookings form)
- Unused Slots (relating to the selected schedule and displayed as per the four columns relating to Unused Slots in the bottom left of the Slot Usage screen in MTS, this could provide multiple lines per from and to location combination)
Report should be called MS Slot Utilisation Report.
Solution
This will be a new Oracle report based on the following views and tables
GEO_SLOT
GEO_LOCATION
GEO_SLOT_TRAILER_TYPE
SCH_SLOT_USAGE
RES_TRAILER_TYPE
SCH_ORD
The report will be accessed from the MTS system using the existing Reports screen. The report will have 4 parameters which will be passed from the form to the report.
- % FILL – This will be a numeric parameter which the users will be free to enter up to 100% (i.e. it will accept values from 1 to 100% including decimals i.e 12.5) The report will use this as a maximum value to look for, i.e. if the %FILL of the report is less than or equal to this parameter the record will be displayed.
- DU Qty – This will be a numeric parameter which the users will be free to enter any value between 1 and 999,999
- DC – This will be a validated parameter based on available RDC type locations within the MTS system. The user will be able to select from a list of values including an option for ‘ALL’ RDC locations.
- Slot Type – This will be a drop down parameter based on entries associated with RIO: NW-7S7FHR, the data will then be used to select either CORE, ADDITIONAL or ALL slot types. (Not to be confused with Collection/Delivery slot types)
The report and parameter tables will be updated to accommodate the new report.
The report will be based on existing report formats with the relevant logo.
Scope
This change will be applied to system version 10.6.
Data
New record will be added to REP_REPORT for the new report.
New records will be added to REP_REPORT_PARAM for the parameters on this report.
FUNCTIONAL DESCRIPTION
A new Oracle report will be created called MS Slot Utilisation Report. The report will display the following data
| DATA ITEM | TABLE | DESCRIPTION |
|---|---|---|
| LOCATION_NAME | GEO_LOCATION | The location name of the slot’s secondary location i.e. the store being delivered to for the slot in question. |
| OMS_REF | SCH_ORD | The Order reference assigned to the slot being reported, nb will be blank if no order is assigned |
| DEPOT | GEO_LOCATION | The location type of the secondary location, i.e. BRANCH, RDC, LOOSE_STORE etc. |
| ADD_CORE | GEO_SLOT | Displays whether the slot in question is a CORE or ADDITIONAL slot |
| Calculation | SCH_ORD/SCH_ORD_LINE | Calculation of the Total DU qty for the order assigned to the slot in question |
| TRAILER_TYPE | GEO_SLOT_TRAILER_TYPE | The Trailer type associated with the slot master data. (Edit Slot will display this currently) |
| TOTAL_RPE_QTY | SCH_ORD | The total RPE’s from the order assigned to the slot |
| MAX_RPE | RES_TRAILER_TYPE | The theoretical maximum RPE’s for the slot based on the trailer type of the slot’s master data |
| Calculation | (TOTAL_RPE_QTY/MAX_RPE)*100 | Calculate % FILL using previous two fields, format will be 999.99, any null values will be substituted with 0.00 |
| Calculation | Count of GEO_SLOT records | The GEO_SLOT records will be counted based on the current slot’s primary and secondary locations matching. I.e. if Neasden have 6 slots delivering to M&S Marble Arch this will display 6 |
The data will be displayed in columns across the page. The parameters chosen will be printed at the top of the report.
The selection criteria for the report will be :-
- % FILL – This will be a numeric parameter which the users will be free to enter up to 100% (i.e. it will accept values from 1 to 100% including decimals i.e 12.5) The report will use this as a maximum value to look for, i.e. if the %FILL of the report is less than or equal to this parameter the record will be displayed.
- DU Qty – This will be a numeric parameter which the users will be free to enter any value between 1 and 999,999
- DC – This will be a validated parameter based on available RDC type locations within the MTS system. The user will be able to select from a list of values including an option for ‘ALL’ RDC locations.
- Slot Type – This will be a drop down parameter based on entries associated with RIO: NW-7S7FHR, the data will then be used to select either CORE, ADDITIONAL or ALL slot types. (Not to be confused with Collection/Delivery slot types)
To allow the location LOV’s to be restricted to the type chosen the Reports form will be changed to create the list of values based on RDC only. This functionality will be for this report only at present.
REFERENCES
Not Available
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued | ||||
| Revised based on new scope | ||||
| Issued |
AUTHORISED BY
| Dave Meir | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
