285936
DHL MTS
New Order Extract
FUNCTIONAL SPECIFICATION - 10.6
16/02/2011 - 2.0
Reference: 285936 PG-8DXCSP
Client Requirement
Create a CSV export that pulls out all orders in a database over a selected period.
Fields: Ord schedule, OMS_REF, Customer Ref, Ord Status, Order Cost Centre, Order Group Name
Parameters: From Schedule and To Schedule
Solution
A new export will be created in CSV format. A procedure will be added to DP_CSV2 that will create the export.
The export will run for the following parameters.
From Schedule
To Schedule
The export will contain the following fields.
| Export Field | DB Column |
| Order Schedule | sch_ord.sched_name |
| OMS Ref | sch_ord.oms_ref |
| Customer ref | sch_ord.external_ref |
| Order Status | sch_ord.status |
| Order Cost Centre | sch_ord.cost_centre_name |
| Order Group Name | sch_ord.group_name |
Orders with a Schedule between the parameter schedules will be included in the report. No other restrictions will be used.
As there are potentially thousands of order records that could be returned the system integrity must be protected in order to ensure that excessive volumes of data are not returned.
Therefore, the schedule range will be restricted to a certain number of days defined by a system parameter. Once a start schedule is chosen the end schedule will be restricted to schedules within the period of time.
For example:
System parameter = 30 days.
Start schedule = ‘110101’
The end schedule will be restricted to schedules less than 110131.
Scope
This change will be applied to system version 10.6.
Set-up
Pre-requisites
Menu-Structure
Data
New system parameter REP_ORD_EXTRACT_DAYS will be added to the table ADM_SYSTEM_PARAM. This parameter will accept numerical values.
Record will be added to REP_REPORT.
Two records will be added to REP_REPORT_PARAM, one for each parameter.
Extract should be added to a users group to allow the user to run the extract.
Functional Description
Extract
A new extract will be created in CSV format. The new extract will be produced from a new procedure that will be added to DP_CVS2.sql.
The export will return data based on two parameters.
FROM SCHEDULE
TO SCHDULE
These parameters will be in a drop down list. The From Schedule will contain all active schedules.
Once a From Schedule has been chosen the To Schedule will be restricted to a number of days after the From Schedule. The number of days between the from and to schedules will be determined by a system parameter. This will prevent reports being produced that contain too much data.
The extract will contain the following data.
| Export Field | DB Column |
| Order Schedule | sch_ord.sched_name |
| OMS Ref | sch_ord.oms_ref |
| Customer ref | sch_ord.external_ref |
| Order Status | sch_ord.status |
| Order Cost Centre | sch_ord.cost_centre_name |
| Order Group Name | sch_ord.group_name |
The data will be restricted to Orders that have a Sched Name between the From and To schedules chosen in the parameters. No other restrictions will take place on the data.
Exports screen
The exports screen will be changed to allow the restriction of the To Schedule parameter. Two new fields will be added that will be used when this extract is chosen from the list.
When the From Schedule is chosen from the drop down list, the To Schedule list will be populated with the schedules that are within the number of days defined by the system parameter.
Document History
| Initial version | ||||
| Review and issue | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
