294044

DHL C-TMS

Parcel Order Time Window Derivation

FUNCTIONAL SPECIFICATION - 10.7

03/01/12 - 1.1
Reference: FS 294044 NW-8NMLSD

FUNCTIONAL OVERVIEW

Client Requirement

This RIO is to cover off additional requirements which have been previously discussed but could not be completed under RIO NW-8KNCK4 as this has been closed.

An order will be identified as either PALLET or PARCEL based on a flag on the DU to denote the type of order.

For PALLET orders the principle is to derive the delivery date and time from the wholesale schedule based on the following conditions:

• If the delivery date is provided and the delivery is in the wholesale schedule for that day, set the delivery times according to the wholesale schedule – set the delivery type to ‘On Sched’ and the service level (new field) to ‘Standard’.
• If the delivery date is provided and the delivery is not in the wholesale schedule for that day, set the delivery times to 08:00 to 17:00 – set the delivery type to ‘Off Sched’ and the service level to ‘Standard’.
• If the delivery date is not provided, match to the next available day in the wholesale schedule, set the delivery times according to the wholesale schedule – set delivery type to ‘On Sched’ and the service level (new field) to ‘Standard’.
• If the delivery date is not provided and no match can be found in the wholesale schedule, set the delivery date to 72 hours forward 08:00 to 17:00 – set the delivery type to ‘Off Sched’ and the service level to ‘Standard’.
• If the delivery date is provided and is today, if the order matches the wholesale schedule move to the next available slot otherwise follow the off schedule rule.
• If the date is in the past keep the date and create the order on the relevant schedule but leave it as unscheduled as it is likely that these will be back orders and as such will be managed manually.

For PARCEL orders the principle is to derive the delivery date and time from the DHL service level of the order based on the following conditions:

• If the service level is provided and a delivery date not provided – set delivery date based on window associated to service level i.e. Pre 9 would be next day 07:00 to 09:00. This is based on the assumption that all parcels will be next day unless otherwise dated.
• If the service level is provided and a delivery date provided – set delivery date based on associated window to service level for the provided date.
• If the service level is not provided default to S24 (system parameter) and follow rules above – (S24 will be set to 09:00 to 17:00).
• Always work to the date being correct but still hold the service level against the order and in the instance where the date is earlier then keep this date and create the order on the relevant schedule but leave it as unscheduled as it is likely that these will be back orders and as such will be managed manually. If the date is later keep the service level but maintain the date so it will go out on the required date.
• Delivery type for parcels will always be 'Same Day' or 'Next Day'.

Solution

A new column called ‘SERVICE_LEVEL’ will be added to the existing ‘SCH_ORD_INFORMATION’ database table and its value will be displayed in the ‘Add Details’ tab page of the ‘ORDERS’ form next to the existing ‘Delivery Type’ field. The DHL ‘Service Level’ will be received as an order sub-reference in the upload files and will also be stored in the new ‘SERVICE_LEVEL’ column.

The new DU category will be used to denote whether a transport order uploaded into C-TMS via XML or CSV files is a ‘PALLET’ order or a ‘PARCEL’ order.

N.B. It will be expected that a transport order will contain only ‘PALLET’ despatch units or ‘PARCEL’ despatch units for this development to be applicable; if the despatch units are mixed then the transport order will be considered to be a ‘PALLET’ order by default for the time window derivation.

‘PALLET’ orders will assess the wholesale schedule maintained in the ‘Fixed Routes Maintenance’ screen to determine the early and late delivery times, delivery type and service level of the transport order (as described in the ‘Change Request Details’ section).

N.B. The wholesale schedule must not have delivery times in the past as regards the system time for it to be ‘On Schedule’. For example, this will prevent a route being accepted if the delivery date is for today but time of the route is now in the past.

The schedule of the transport order will continue to be based on its delivery date.

‘PARCEL’ orders will assess the delivery date and service level provided instead of the wholesale schedule (as described in the ‘Change Request Details’ section).

N.B. The associated window refers to the ‘Schedule Rules’ tab page in the new ‘Order Window Offsets’ screen as created for RIO ‘NW-8KNCK4’.

Scope

This change will be applied to system version 10.7.0.

SET-UP

Data

The new database column and system parameters will need to be created using the script in

Implementation Advice

The new database column and system parameters will need to be created prior to the compilation of the forms and packages.

The existing system parameter ‘MAINTAIN_SCHEDULE_DATES’ will be set to ‘Y’ for the cost centre ‘HUK’.

The existing EDI parameter ‘MAINTAIN_SCHEDULES will be set to ‘Y’ for the inbound order EDI process.

The new system parameter ‘OMS_STORE_SERVICE_LEVEL’ will be set to ‘Y’ for the cost centre ‘HUK’.

The new system parameter ‘OMS_DEFAULT_SERVICE_LEVEL’ will be set to ‘S24’ for the cost centre ‘HUK’

FUNCTIONAL DESCRIPTION

A new column will be added to the ‘SCH_ORD_INFORMATION’ database table to store the DHL service level for the transport order:

Service Level / Delivery Type Validation

The ‘Delivery Type’ and ‘Service Level’ fields will be validated to confirm that they exist as active delivery types in the ‘Contracts’ screen as accessed via the ‘Delivery Types’ button:

Therefore, it is expected that the following values will exist plus any additional service levels for the specific carriers, if the validation fails the order will be rejected as per standard processing:

• Same Day
• Next Day
• Standard
• On Sched
• Off Sched

N.B. The validation will apply when a transport order is uploaded via a ‘CSV’ or ‘XML’ file or is entered manually.

Orders Form

The ‘ORDERS’ form will be changed to display the new ‘SCH_ORD_INFORMATION.SERVICE_LEVEL’ database item in the ‘Add Detail’ tab page as may be seen below:

The new field will have the title of ‘Service Level’ to the left of its value and the user will be able to modify it manually or via a button to display a list of values.

The ‘Delivery Type’ and the ‘Service Level’ fields will be validated that they exist as active ‘Delivery Types’ as described in section 3.2.

It is also expected that the service level will be setup for the carrier services mapping although the ‘Service Level’ will not be validated against the ‘Service Type’, for example:

Service Level Upload

The new ‘SCH_ORD_INFORMATION.SERVICE_LEVEL’ database item will also be stored as an order sub-reference (should it have been uploaded as an order sub-reference) and it will be displayed in the ‘Add Refs’ tab page:

The ‘CSV’ and ‘XML’ transport order upload processes will be changed to store the ‘SERVICE_LEVEL’ order sub-reference in the new ‘SCH_ORD_INFORMATION.SERVICE_LEVEL’ database item.

The ‘SERVICE_LEVEL’ order sub-reference will be stored as a decode value for mapping from the sub-references received in the ‘CSV’ or ‘XML’ files.

For example:

The following functions will be changed to update the new ‘SCH_ORD_INFORMATION.SERVICE_LEVEL’ database item:

• IMP.PROCESS_TI_ORDER
• INT_XML_IN.PROCESS_ORDER_XML_IN

The update process will be controlled by a new cost-centre-level system parameter called ‘OMS_STORE_SERVICE_LEVEL’: if it is set to ‘Y’ then the order sub-reference will also be stored as a separate service level on the transport order; if it is set to ‘N’ or not set then separate service level will not be stored.

Time Window Derivatione

The derivation of the time windows for the new transport orders uploaded in the ‘CSV’ or ‘XML’ files will be based on the new category of the type of despatch unit used.

The DU category will be either ‘PARCEL’ or ‘PALLET’ and it will be maintained in the new ‘DU Category’ column in the ‘Despatch Unit Types’ tab page in the ‘Resource Maintenance’ screen:

N.B. It will be expected that a transport order will contain only ‘PALLET’ despatch units or ‘PARCEL’ despatch units for this development to be applicable: if the despatch units are mixed then the transport order will be considered to be a ‘PALLET’ order by default for the time window derivation.

The existing cost-centre-level system parameter called ‘MAINTAIN_SCHEDULE_DATES’ will control whether the time window derivation is applied during the upload of the transport order from a ‘CSV’ file: if it is set to ‘Y’ then the time window derivation will be active.

The existing EDI parameter called ‘MAINTAIN_SCHEDULES’ will control whether the time window derivation is applied during the upload of the transport order from a ‘XML’ file: if it is set to ‘Y’ then the time window derivation will be active.

This system parameter was introduced for the development performed for RIO ‘291212 NW-8KNCK4 Time Window Derivation’.

Pallet

‘PALLET’ transport orders will assess the wholesale schedule maintained in the ‘Fixed Routes Maintenance’ screen to determine the early and late delivery times, delivery type and service level of the transport order as described below:

1. If the delivery date is provided and is in the future and the delivery is in the wholesale schedule for that day:
   1. Set the delivery date and time window according to the wholesale schedule,
   2. Set the delivery type to ‘On Sched’,
   3. Set the service level to ‘Standard’.
2. If the delivery date is provided and is in the future and the delivery is not in the wholesale schedule for that day:
   1. Set the delivery time window to ‘08:00’ to ‘17:00’ for the delivery date provided,
   2. Set the delivery type to ‘Off Sched’,
   3. Set the service level to ‘Standard’.
3. If the delivery date is not provided:
   1. Match to the next available day in the wholesale schedule,
   2. Set the delivery date and time window according to the wholesale schedule,
   3. Set delivery type to ‘On Sched’,
   4. Set the service level to ‘Standard’.
4. If the delivery date is not provided and no match could be found in the wholesale schedule for point ‘3’:
   1. Set the delivery date to 72 hours forward from the system date,
   2. Set the delivery time window to ‘08:00’ to ‘17:00’,
   3. Set the delivery type to ‘Off Sched’,
   4. Set the service level to ‘Standard’.
5. If the delivery date is provided and it is for today’s system date:
   1. If the order matches the wholesale schedule move to the next available slot, set the delivery type to ‘On Sched’, set the service level to ‘Standard’,
   2. Otherwise follow the off schedule rule in point ‘4’.
6. If the delivery date is in the past:
   1. Keep the date and create the order on the relevant schedule but leave it as unscheduled as it is likely that these will be back orders and as such will be managed manually, set the delivery type to ‘Off Sched’, set the service level to ‘Standard’.

N.B. The wholesale schedule must not have delivery times in the past as regards the system time for it to be ‘On Sched’. For example, this will prevent a route being accepted if the delivery date is for today but time of the route is now in the past.

The wholesale schedule may be assessed for the transport order using the new functions available in the new ‘DP_SCHEDULING_ENGINE’ database package developed for RIO ‘290935 NW-8KEMH2 Create a New Scheduling Engine’.

A new function called ‘F_ASSESS_AVAIL_ROUTE’ will be created in the new ‘DP_SCHEDULING_ENGINE’ database package and this function will be called from the upload processes.

The following functions will be changed to use the new ‘F_ASSESS_AVAIL_ROUTE’ function for the time window derivation if the system parameter ‘MAINTAIN_SCHEDULE_DATES’ is set to ‘Y’ and the cost centre is ‘HUK’ (for other cost centres the existing functionality will be used):

• IMP.PROCESS_TI_ORDER
• INT_XML_IN.PROCESS_ORDER_XML_IN

N.B. The code to assess the time windows will need to be delayed until after the carrier and service level have been extracted from the file.

N.B. The existing system parameters ‘SCH_SCHED_ORD_IMP_TI_ORDER_SCHED_DATE_OVERRIDE’ (e.g. ‘Y’) and ‘SCH_SCHED_ORD_DERIVE’ (e.g. ‘EDDT’ for early delivery date) will continue to be applied to the determination of the schedule date of the transport order.

Equivalent functions to the following functions in the new ‘DP_SCHEDULING_ENGINE’ database will be used to find a suitable active wholesale schedule route for the unscheduled transport order being uploaded:

• IDENTIFY_ROUTE_UNSCHEDULED
  • Finds a direct route for the ‘from’ and ‘to’ locations or a trunk route for the ‘from’ location and ‘to’ zone.
• WEEKDAY_WS
  • Checks that the route found is active for the day of the early available (i.e. collection) date.
• TIME_WINDOWS
  • Checks that the route found has a stop for collection and delivery within the time window required.
• GET_MINUTES
  • Gets the number of minutes in the day for the appropriate time.

N.B. Equivalent functions will need to be used because the transport order will not be present on the database tables during the upload process. These equivalent functions will need to use the dates received in the file as parameters.

N.B. If the transport order has a mandated carrier then only routes for that carrier may be used.

The schedule of the transport order will continue to be based on its delivery date.

An example of the fixed routes may be seen below:

Parcel

‘PARCEL’ transport orders will assess the delivery date and service level provided instead of the wholesale schedule as described below:

1. If the service level is provided and a delivery date is not provided:
   • Set the delivery date based on the window associated to the service level (e.g. rule ‘Pre 9’ would be for next day between times ‘07:00’ to ‘09:00’). This is based on the assumption that all parcels will be next day unless otherwise dated.
2. If the service level is provided and a delivery date provided:
   • Set the delivery date based on the window associated to the service level provided.
3. If the service level is not provided:
   • Default to ‘S24’ (based on the new cost-centre-level system parameter called ‘OMS_DEFAULT_SERVICE_LEVEL’) and follow the rules in points ‘1’ and ‘2’ for the delivery date (e.g. ‘S24’ will be set to times between ‘09:00’ to ‘17:00’).
4. Always work to the delivery date being correct but still hold the service level against the order and, in the instance where the delivery date is earlier than the system date, keep the delivery date and create the order on the relevant schedule but leave it as unscheduled (as it is likely that these will be back orders and as such will be managed manually). If the delivery date is later than the system date, keep the service level but maintain the delivery date so it will go out on the required date.
5. The delivery type for parcels will always be 'Same Day' or 'Next Day'.

N.B. The associated window refers to the ‘Schedule Rules’ tab page in the new ‘Order Window Offsets’ screen as created for RIO ‘NW-8KNCK4’:

N.B. A new column called ‘Service’ will be added to the ‘Schedule Rules’ tab page to store the delivery type for the rule.

A new cost-centre-level system parameter called ‘AUTO_SET_DELIVERY_TYPES’ will be created to control the setting of delivery types based on the ‘Schedule Rules’ for the new development, however, the system parameter will not be active for Healthcare so the delivery type will not be calculated by this new method and will not contradict the logic required for the ‘PARCEL’ transport orders.

See RIO ‘291358 MS-8KNFRL 2 Order Addresses & DU & Del Type’ for further information about the new ‘Service’ column.

N.B. The setup for Healthcare need only contain data in the following columns:

N.B. An independent offset of 2 days will be added for transport orders uploaded on a Friday.

A new function called ‘F_ASSESS_SCHED_RULES’ will be created in the new ‘DP_SCHEDULING_ENGINE’ database package and this function will be called from the upload processes.

The following functions will be changed to use the new ‘F_ASSESS_SCHED_RULES’ function for the time window derivation if the system parameter ‘MAINTAIN_SCHEDULE_DATES’ is set to ‘Y’ and the cost centre is ‘HUK’ (for other cost centres the existing functionality will be used):

• IMP.PROCESS_TI_ORDER
• INT_XML_IN.PROCESS_ORDER_XML_IN

N.B. The code to assess the time windows will need to be delayed until after the carrier and service level have been extracted from the file.

REFERENCES

Ref No	Document Title & ID	Version	Date
1	EST-294044 NW-8NMLSD Parcel Order Time Window Derivation v1.0.doc	1.0	08/12/11

DOCUMENT HISTORY

Version	Date	Status	Reason	Initials
0.1	14/12/11	Draft	Initial version	PDR
0.1	16/12/11	Draft	Review of the initial version	PJH
1.0	16/12/11	Issue	Issued to client	PJH
1.1	30/12/11	Draft	Changes for delivery type and service level validation.	PDR
1.1	03/01/12	Draft	Review of v1.1	PJH
2.0	03/01/12	Issue	Issued to client	PJH

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager