287253

DHL C-TMS

Additional Reporting Requirements

FUNCTIONAL SPECIFICATION - 10.6

- 3.0

Reference: FS 287253 DK-8F9GVA

Client Requirement

Change Request Summary:

To enable OBS to commence work with estimating the report requirements for the Openfield Project.

Change Request Details:

This RIO is for the additional reports required for the Openfield Project:

1. Order Tracking
2. Future Worksheet
3. Carrier Work Confirmation
4. Failed Shipments by Code/Week
5. DOOD

Benefits identified as a result of the change:

As above.

Solution

1. Order Tracking Report:

Excel format. Date driven by month with an offset option of a number of weeks either side. Function: by a count function to list out all orders which reside in C-TMS by order status. Current statuses include: CA (Cancelled), FA (Failed), NS (No Shipment), UN (Unplanned), PL (Planned - Carrier not assigned), AL (Allocated - not briefed/carrier assigned), BR (Briefed), MC (Movement confirmed), DE (Debriefed in Tokairo), PA (Sent for payment).

Report Parameter Requirements:

1. ‘Year’ (e.g. ‘2011’)
2. ‘Month’ (e.g. ‘January’)
3. ‘Week Offset’ (e.g. ‘1’ but optional)

The ‘Week Offset’ will include up to the last full week of the previous month if the offset is ‘1’, and up to the first full week of the next month if the offset is ‘1’. A full week will be from Sunday to Saturday.

The CSV extract will include a column for each status that the order may be (based on the definition of the order status for Openfield above) and a column for each date in the range set by the report parameters. A total number of orders per status will be included in the final column.

The status will be as per the list provided above and the orders and trips in C-TMS will be mapped to these values.

CA (Cancelled) – Order status will be cancelled

FA (Failed) – Trip of Order will be status COMPLETED with no POD and with reason codes

NS (No Shipment) – Order status INVALID so loaded into C-TMS with errors

UN (Unplanned) – Order status UNSCHEDULED

PL (Planned) – Order allocated to Trip with Trip status PLANNED not allocated to Carrier

AL (Allocated) - Order allocated to Trip with Trip status PLANNED and Carrier allocated

BR (Briefed) – Order allocated to Trip status ACCEPTED and carrier assigned

MC (Movement confirmed) – Trip actual dates and times captured

DE (Debreifed) – Order POD flag set to Y

PA (Sent for Payment) – Final PAYE message sent to EFX

The rows will display the order status and then the number of orders at that status on the dates displayed in each column. A total per date will also be included on the final row.

An order can only be counted once under one status value. This means after considering CA, FA and NS first, then in sequence MC, BR, AL, PL, UN in that order until the order fits the criteria of that status as described above.

An example of the output format is shown below;

1. Future Worksheet Report:

The Future Worksheet has been removed from scope and replaced by an extended content in the DESP1 milestone EDI message flow from C-TMS to Openfield GOLD.

1. Carrier Work Confirmation Report:

PDF format. Designed to be e-mailed or faxed to haulier listing out their work load for their scheduled collection and deliveries on the following day. Carrier work confirmation report will be e-mailed or faxed out en masse at 18:00 every evening to the haulier by its preferred method. Carrier will return paperwork to HQ via fax by 10:00am the day after delivery was due. Some of the report will be populated with data but there is also space for handwritten notes for the driver to fill in. Fields 1. Delivery Date = Trip Date. 2. Journey Ref = Schedule, Trip, Order Ref. 3. Order Ref= Customer Ref. 4. Collection Town = From Location. 5. Delivery Town = To Location. 6. Weight = Order Weight (planned).

Column headings will be provided to allow free text to be written in by the carrier as Collection Arrive date and time, Collection Depart Date and time, Delivery Arrive date and time, Delivery Depart date and time, Reason for missed delivery / re-delivery date (if applicable), redirected (Y/N)

Column headings will be provided to allow free test to be written in by the carriers as per the example output shown below, so Delivered ? Y/N, Reason for missed delivery / re-delivery date (if applicable), redirected (Y/N).

Report Parameter Requirements:

1. ‘From Schedule’
2. ‘To Schedule’
3. ‘Carrier Code’ (optional)

Each ‘Journey’ will be considered to be unique for the combination of the schedule, trip and order. A report of the journeys will be produced per carrier assigned.

A database job will be used to produce the report at the scheduled time and frequency.

The e-mail and fax details will be stored for a carrier in the ‘Message Maintenance’ screen and used to send the report to the carrier.

A record of when the carrier is sent the report will be required. This will be available from the Trip Audit Notes and from Message Monitoring screens.

4. Failed Shipment By Code Week:

Excel format. Purpose of report to report by exception all 'failed' loads that have met certain requirements for delivery etc. E.g. outside time window, rejected load, cancelled delivery, insufficient load to collect, load reassigned to another carrier, wrong delivery address etc. All reasons need to be stored as reason codes for non conformance and be reported within this report. Fields: Column 1 = Planned delivery date, Column 2 = Customer Code, Column 3 = Journey Reference (Schedule, Trip, Order) Column 3 Order reference = Customer Ref, Column 4 Failure Reason Code = Status/Reason Code, Column 5 Shipment Failed Date = Date briefed as failed, Column 6 Shipment Failed By = Planner Log on Column 7 Notes = Non conformance reason comments.

Report Parameter Requirements:

1. ‘From Schedule’
2. ‘To Schedule’

The reasons for the non-conformance will be recorded in C-TMS when the trip and order is debriefed.

5. The DOOD Report:

Excel format. Data extract of a combination of all the above reports. This output provides the user visibility of all loads at all statuses.

Report Parameter Requirements:

1. ‘From Schedule’
2. ‘To Schedule’

The ‘Oracle Reporting Suite’ can be used for this extract.

Please note that a method of file transfer to a shared directory on the DHL infrastructure has been discussed. The DOOD output is utilised to upload data into an end user MS Access database to facilitate production of various reports, statistics and KPIs. The scope of this requirement is production of the DOOD output and subject to security approval, configuration and networking (which remains the responsibility of DHL), a method to write the DOOD output to a particular shared directory.

Scope

This change will be applied to system version 10.6.0 on INDTST and once approved INDPRD.

General Comments

The data take-on spreadsheet will need to be modified to take consideration of new functionality implemented into C-TMS to support the Openfield operation.

Openfield Postcode out-code to out-code (district) distance matrix.

Openfield Postcode charging matrix.

Carrier Lanes

Message preferences – carrier message preference, e-mail address or telephone number for fax.

User message data – list of outputs and recipients for automated outputs by user. Note this data collection requirement is necessary to support the implementation of the reports subscription functionality; this is subject to new C-TMS development to be made available as a subsequent development release of the product some time after the Openfield Go-Live to be determined.

Reason codes may need to be segregated with a prefix to segregate for Openfield use.

Outputs and Delivery of Information

Each of the outputs described in this specification will be available to manually request from either the Reports or Extracts menu depending on whether PDF or CSV output respectively as defined by each output in this specification respectively. Note this is either PDF or CSV not both. The output can be viewed in the browser or saved to a local directly as either .PDF or .CSV with a default filename that can be overtyped at run-time.

Where fax out is developed, C-TMS will deposit the necessary output control file .G3F SUB file and the image of the fax as a .tiff format file to a defined server directory. The DHL Zetafax service will process and send the fax according to this C-TMS output. The Zetafax server resides in the DHL network and this service is managed and maintained exclusively by DHL or other third parties. OBSL has no responsibility beyond sending the C-TMS output to the required directory. Investigation is required as to whether the transportation of the two files from the C-TMS server to the Zetfax Server will be based on ftp push or on a file-share to be organised and configured by DHL and OBSL.

Where email output is developed. C-TMS will interact with DHL’s email service exactly as in previous deployments of C-TMS, for example DSGSet-up

Pre-requisites

1. The user groups will require access to run the new reports.
2. The reports will need to be available in the ‘Reports’ screen.

Menu Structure

‘Unchanged’

Data

See Appendix A for the scripts to setup the pre-requisites.

Functional Description

Order Tracking Report

Excel format. Date driven by month with an offset option of a number of weeks either side. Function: by a count function to list out all orders which reside in C-TMS by order status. Current statuses include: CA (Cancelled), FA (Failed), NS (No Shipment), UN (Unplanned), PL (Planned - Carrier not assigned), AL (Allocated - not briefed/carrier assigned), BR (Briefed), MC (Movement confirmed), DE (Debriefed in Tokairo), PA (Sent for payment).

Report Parameter Requirements:

1. ‘Year’ (e.g. ‘2011’)
2. ‘Month’ (e.g. ‘January’)
3. ‘Week Offset’ (e.g. ‘1’ but optional)

The ‘Week Offset’ will include up to the last full week of the previous month if the offset is ‘1’, and up to the first full week of the next month if the offset is ‘1’. A full week will be from Sunday to Saturday.

The status will be as per the list provided above and the orders and trips in C-TMS will be mapped to these values.

CA (Cancelled) – Order status will be cancelled

FA (Failed) – Trip of Order will be status COMPLETED with no POD and with reason codes

NS (No Shipment) – Order status INVALID so loaded into C-TMS with errors

UN (Unplanned) – Order status UNSCHEDULED

PL (Planned) – Order allocated to Trip with Trip status PLANNED not allocated to Carrier

AL (Allocated) - Order allocated to Trip with Trip status PLANNED and Carrier allocated

BR (Briefed) – Order allocated to Trip status ACCEPTED and carrier assigned

MC (Movement confirmed) – Trip actual dates and times captured

DE (Debreifed) – Order POD flag set to Y

PA (Sent for Payment) – Final PAYE message sent to EFX

The export parameter types will be:

1. PG_FFNUM1
2. PG_VALUE_LIST1
3. PG_FFNUM2

The parameters will be passed to a new procedure called ‘ORDER_TRACKING’ and a new function called ‘WRITE_ORDER_TRACKING’ in package ‘DP_CSV2’.

The CSV extract will include a column for each status that the order can be (based on the definition of the order status for Openfield above) and a column for each date in the range set by the report parameters. A total number of orders per status will be included in the final column.

The status will be as per the list provided above and the orders and trips in C-TMS will be mapped to these values.

The rows will display the order status and then the number of orders at that status on the dates displayed in each column. A total per date will also be included on the final row.

The parameters will be used to select the schedule of the orders.

The name of the file will be ‘ORDER_TRACKING_{SYSDATE}_{SYSTIME}’.

The heading will be:

‘Openfield KPI (Order Tracking)’

‘{Day} {SYSDATE}’

The system date will be in format ‘DD MON YYYY’.

The columns reported will be as follows:

Column	Count on Date	Total
Order Status
CA (Cancelled)
FA (Failed)
NS (No Shipment)
UN (Unplanned)
PL (Planned – Carrier Not Assigned)
AL (Allocated – Not Briefed/Carrier Assigned)
BR (Briefed)
MC (Movement Confirmed)
DE (Debriefed in Tokairo)
PA (Sent for Payment)
Total:

The count will be of orders on the date that match the definition of the ‘Order Status’.

The status of each column with its equivalent in C-TMS for an order is described below:

Column	Equivalent
CA	‘SCH_ORD.STATUS’ = ‘CANCELLED’
FA	‘SCH_ORD_NON_CONFORM.REASON_CODE’ is not NULL
NS	‘SCH_ORD.STATUS’ in (‘ABORTED’,’DELETED’,’INVALID’)
UN	‘SCH_ORD.STATUS’ = ‘UNSCHEDULED’
PL	‘SCH_ORD.STATUS’ = ‘SCHEDULED’ and ‘SCH_TRIP.CARRIER_ID’ is NULL
AL	‘SCH_ORD.STATUS’ = ‘SCHEDULED’ and ‘SCH_TRIP.CARRIER_ID’ is not NULL and ‘SCH_TRIP.STATUS’ <> ‘ACCEPTED’
BR	‘SCH_TRIP.STATUS’ = ‘ACCEPTED’
MC	‘SCH_ORD.STATUS’ = ‘DELIVERED’
DE	‘SCH_ORD.POD’ = ‘Y’
PA	‘SCH_TRIP.EFX_SEND_FLAG’ = ‘Y’

The data will be in ascending sequence of date.

Carrier Work Confirmation Report

PDF format. Designed to be e-mailed or faxed to haulier listing out their work load for their scheduled deliveries on the following day. Carrier work confirmation report will be e-mailed or faxed out en masse at 18:00 every evening to the haulier by its preferred method. Haulier will return paperwork to HQ via fax by 10:00am the day after delivery was due. Some of the report will be populated with data but there is also space for handwritten notes for the driver to fill in. Fields 1. Delivery Date = Trip Date. 2. Journey Ref = Schedule, Trip, Order Ref. 3. Order Ref= Customer Ref. 4. Collection Town = Start Location. 5. Delivery Town = End Location. 6. Weight = Order Weight (planned). All other columns form a free text field for the driver to amend.

Report Parameter Requirements:

1. ‘From Schedule’
2. ‘To Schedule’
3. ‘Carrier’

The report parameter types will be:

1. PG_SELECT_LIST1
2. PG_SELECT_LIST2
3. PG_SELECT_LIST3

The parameters will be passed to a new report called ‘CARRIER_WORK_CONFIRMATION’.

Each ‘Journey’ will be considered to be unique for the combination of the schedule, trip and order. A report of the journeys will be produced per carrier date assigned.

A database job will be used to produce the report at the scheduled time and frequency.

The e-mail and fax details will be stored for a carrier in the ‘Message Maintenance’ screen and used to send the report to the carrier.

A record of when the carrier is sent the report will be required.

The schedule range will be used to select the schedule of the trips. The carrier selection will be optional and will be used to print or reprint specific carrier work confirmations

DHL logos will be required for display in the report.

A new page will be started for each carrier.

The page heading will display the following text:

• ‘Openfield’ (ORG_CUSTOMER.CUSTOMER_NAME)
• From: {PG_SELECT_LIST1} (Format ‘DDD/MM/YYYY)
• To: {PG_SELECT_LIST2} (Format ‘DDD/MM/YYYY)
• Carrier: {RES_CARRIER.CARRIER_NAME}
• Signed: Blank
• Date: Blank

The page footing will be:

• ‘PLEASE ENSURE THAT THIS SHEET IS COMPLETED AND FAXED TO {} NO LATER THAN 10:00am ON DAY FOLLOWING DELIVERY’
• ‘({SYSDATE})’
• ‘Page n of n’

The system date will be in format ‘DY DD MON YYYY HH24:MI’.

The columns reported will display the following text:

Column	Value
Delivery Date	SCH_TRIP_STOP.ARRIVE (Format ‘DD/MM/YYYY’)
Journey Reference	{SCH_TRIP.SCHED_NAME}-{SCH_HAULAGE_ACTIVITY.TRIP_ID}-{SCH_HAULAGE_ACTIVITY.OMS_REF}
Order Reference	SCH_ORD.EXTERNAL_REF
Collection Town	GEO_LOCATION.TOWN
Delivery Town	GEO_LOCATION.TOWN
Weight	SCH_ORD.WEIGHT / 1000 for Metric Tonnes
Delivered ? Y/N	Blank
Reason for missed delivery / re-delivery (if applicable)	Blank
Re-directed? Y/N	Blank

The data will be in ascending sequence of ‘Carrier’ and ‘Journey Reference’.

Failed Shipment by Code Week

Excel format. Purpose of report is to report by exception all 'failed' loads that have met certain requirements for delivery etc. E.g. outside time window, rejected load, cancelled delivery, insufficient load to collect, load reassigned to another carrier, wrong delivery address etc. All reasons need to be stored as reason codes for non conformance and be reported within this report. Fields: Column 1 = Planned collection / delivery date, Column 2 = Customer Code, Column 3 = Journey Reference so Schedule, Trip, Order, Column 4 Order reference = Customer Ref, Column 5 Failure Reason Code = Status/Reason Code, Column 6 Shipment Failed Date = Date briefed as failed, Column 7 Shipment Failed By = Planner Logon Column 8 Notes = Non conformance reason comments.

Report Parameter Requirements:

1. ‘From Schedule’
2. ‘To Schedule’

The reasons for the non-conformance will be recorded in C-TMS when the trip and order is debriefed.

The export parameter types will be:

1. PG_SELECT_LIST1
2. PG_SELECT_LIST2

The parameters will be passed to a new procedure called ‘FAILED_SHIPMENT’ and a new function called ‘WRITE_FAILED_SHIPMENT’ in package ‘DP_CSV2’.

A failed shipment will be identified if an order exists with a non-conformance recorded during trip debrief, or if an order has been cancelled, within the schedule range set.

The schedule range will be used to select the schedule of the orders.

The name of the file will be ‘FAILED_SHIPMENT_{SYSDATE}_{SYSTIME}’.

The heading will be:

‘Failed Shipments by Code Week’

‘Openfield’

The columns reported will be as follows:

Column	Value
Date	SCH_ORD.EARLY_AVAIL
Code	SCH_ORD.CUSTOMER
Journey Reference	{SCH_TRIP.SCHED_NAME}-{SCH_HAULAGE_ACTIVITY.TRIP_ID}-{SCH_HAULAGE_ACTIVITY.OMS_REF}
Order Reference	SCH_ORD.EXTERNAL_REF
Failure Reason Name	SCH_REASON_CODE.DESCRIPTION
Shipment Failed Date	SCH_ORD_NON_CONFORM.CREATED_DATE
Shipment Failed By	SCH_ORD_NON_CONFORM.CREATED_BY
Notes	SCH_ORD_NON_CONFORM.COMMENTS

The haulage loading activity will be found for the non-conformance recorded for the order.

The ‘Date’ will the date of the early collection date in format ‘DD/MM/YYYY’.

If an order is not on a trip then the ‘Journey Reference’ will be ‘Not on Journey’.

N.B. The ‘Failure Reason Name’ will be displayed as the description of the reason code recorded (i.e. SCH_ORD_NON_CONFORM.REASON_CODE).

The data will be in a ascending sequence of ‘Code’, ‘Date’, ‘Journey Reference’ and ‘Order Reference’.

DOOD Report

Excel format. Data extract of a combination of all the above reports. Provides the user with visibility of all loads at all statuses.

DOOD stands for Delivery Order Overview Detail.

Report Parameter Requirements:

1. ‘From Schedule’
2. ‘To Schedule’

The export parameter types will be:

1. PG_SELECT_LIST1
2. PG_SELECT_LIST2

The parameters will be passed to a new procedure called ‘DOOD_REPORT’ and a new function called ‘WRITE_DOOD_REPORT’ in package ‘DP_CSV2’.

The schedule range will be used to select the schedule of the trips.

The name of the file will be ‘DOOD_{SYSDATE}_{SYSTIME}’

The heading will be:

‘DOOD Report’

The columns reported will be as follows:

Column	Value
Journey Date	SCH_TRIP.SCHED_NAME (Format ‘DD/MM/YY’)
Journey Reference	{SCH_TRIP.SCHED_NAME}-{SCH_HAULAGE_ACTIVITY.TRIP_ID}-{SCH_HAULAGE_ACTIVITY.OMS_REF}
Load Number	SCH_TRIP_STOP.STOP_NO
Vehicle Reg	SCH_TRIP.TRACTOR_ID
Vehicle Type	SCH_TRIP_STOP.TRAILER_TYPE
Vehicle Provider	SCH_TRIP.HAULIER_ID
Trailer Code	SCH_TRIP_STOP.TRAILER_ID
Lane	RTE_HAULIER_LANE.LANE_TYPE
Lane Type	RTE_HAULIER_LANE_ORDERS.LANE_GROUP
Order Reference	SCH_HAULAGE_ACTIVITY.OMS_REF
Account Code	SCH_ORD.CUSTOMER
Collection Location	GEO_LOCATION.LOCATION_ID
Collection Name	GEO_LOCATION.LOCATION_NAME
Collection Town	GEO_LOCATION.TOWN
Collection Postcode	GEO_LOCATION.POSTCODE
Collection Contact	{GEO_CONTACT.FORENAME}+{GEO_CONTACT.SURNAME}
Collection Contact Phone	GEO_CONTACT.PHONE
Collection Contact Email	GEO_CONTACT.EMAIL
Customer Location	GEO_LOCATION.LOCATION_ID
Customer Name	GEO_LOCATION.LOCATION_NAME
Customer Address	GEO_LOCATION.ADDRESS_LINE1
Customer Postcode	GEO_LOCATION.POSTCODE
Slot Start Date	SCH_ORD.EARLY_DEL (Format ‘DD MONTH YYYY’)
Slot Start Time	SCH_ORD.EARLY_DEL (Format ‘HH24:MI’)
Slot End Date	SCH_ORD.LATE_DEL (Format ‘DD MONTH YYYY’)
Slot End Time	SCH_ORD.LATE_DEL (Format ‘HH24:MI’)
Slot Length (Hrs)	SCH_ORD.LATE_DEL - SCH_ORD.EARLY_DEL
Collection Arrival Date	SCH_TRIP_STOP.ARRIVE (Format ‘DD MONTH YYYY’)
Collection Arrival Time	SCH_TRIP_STOP.ARRIVE (Format ‘HH24:MI’)
Collection Departure Date	SCH_TRIP_STOP.DEPART (Format ‘DD MONTH YYYY’)
Collection Departure Time	SCH_TRIP_STOP.DEPART (Format ‘HH24:MI’)
Arrival Date	SCH_TRIP_STOP.ACTUAL_ARRIVE (Format ‘DD MONTH YYYY’)
Arrival Time	SCH_TRIP_STOP.ACTUAL_ARRIVE (Format ‘HH24:MI’)
Departure Date	SCH_TRIP_STOP.ACTUAL_DEPART (Format ‘DD MONTH YYYY’))
Departure Time	SCH_TRIP_STOP.ACTUAL_DEPART (Format ‘HH24:MI’)
On Time?	‘Incomplete/Pre Date/Early On Date/On Time/Late On Date/Post Date’
Miles	Blank
Order Weight	SCH_ORD.WEIGHT
Capped Tonnage	SCH_ORD.CAPPED_TONNAGE
Actual Weight	SCH_ORD.ACTUAL_WEIGHT
Order Revenue	{SCH_ORD.ORD_REVENUE}+{SCH_ORD.REVENUE_VAT}
Order Status	As described for status in Order Tracking Report. Include just the description here and not the code.
Journey Cost	SCH_TRIP.TRIP_COST
Briefed By	SCH_TRIP_AUDIT.ACTIONED_BY
Briefed Date	SCH_TRIP_AUDIT.ACTION_DATE (Format ‘DD/MM/YYYY’)
Briefed Time	SCH_TRIP_AUDIT.ACTION_DATE (Format ‘HH24:MI’)
Region	SCH_ORD.PLANNING_REGION
Exception	SCH_ORD.EXCEPTION
Fixing Number	SCH_ORD_REFERENCE.SUB_REF_VALUE (81)
Fixing Customer Reference	SCH_ORD.DEL_POINT_REF
Intake Reference	SCH_ORD_REFERENCE.SUB_REF_VALUE (21)
Commodity	SCH_ORD_REFERENCE.SUB_REF_VALUE (68)
Desp2 Date	SCH_ORD.POD_DATE (Format ‘DD MONTH YYYY’)
Lead Time	SCH_ORD.LATE_DEL - SCH_ORD.CREATED_DATE (less weekends and bank holidays so lead-time expressed in working days).
Date Range	SCH_ORD.LATE_DEL - SCH_ORD.EARLY_DEL (Number of days inclusive)
Order Received Date	SCH_ORD.CREATED_DATE (Format ‘DD/MM/YYYY’)
Order Received Time	SCH_ORD.CREATED_DATE (Format ‘HH24:MI’)

N.B. The ‘Vehicle Reg’ and the ‘Trailer Code’ are stored on the first stop of the trip.

The ‘Lane Type’ will be found for the order and the ‘Lane’ for the lane group and haulier.

The ‘Collection’ address details will be obtained for the source location of the order and the ‘Customer’ address details for the destination location of the order. The location ID will be reverse decoded using decode name ‘GOLD_LOCS’.

The ‘Slot’ times refer to the ‘Delivery’ times of the order.

The ‘Collection’ times refer to the times of the collection trip stop.

‘On Time?’ will be calculated from the times for order delivery slot (i.e. ‘Slot End Time’) and the actual delivery of the trip (i.e. ‘Arrival Time’):

Column	Calculation
Incomplete	Order has not been delivered
Pre Date	Order was delivered on a day before its end date
Early On Date	Order was delivered before its end time on the same date
On Time	Order was delivered before its end time
Late On Date	Order was delivered after its end time on the same date
Post Date	Order was delivered after its end date

The ‘Order Status’ will be the milestone status uploaded:

Column	Equivalent
CA	‘SCH_ORD.STATUS’ = ‘CANCELLED’
FA	‘SCH_ORD_NON_CONFORM.REASON_CODE’ is not NULL
NS	‘SCH_ORD.STATUS’ in (‘ABORTED’,’DELETED’,’INVALID’)
UN	‘SCH_ORD.STATUS’ = ‘UNSCHEDULED’
PL	‘SCH_ORD.STATUS’ = ‘SCHEDULED’ and ‘SCH_TRIP.CARRIER_ID’ is NULL
AL	‘SCH_ORD.STATUS’ = ‘SCHEDULED’ and ‘SCH_TRIP.CARRIER_ID’ is not NULL and ‘SCH_TRIP.STATUS’ <> ‘ACCEPTED’
BR	‘SCH_TRIP.STATUS’ = ‘ACCEPTED’
MC	‘SCH_ORD.STATUS’ = ‘DELIVERED’
DE	‘SCH_ORD.POD’ = ‘Y’
PA	‘SCH_TRIP.EFX_SEND_FLAG’ = ‘Y’

The order sub-references will be obtained using the following values:

Column	SCH_ORD_REFERENCE.SUB_REF_NAME
Fixing Number	81
Intake Reference	21
Commodity	68

The ‘Lead Time’ will be calculated as the number of days between the raising of the order and the delivery less bank holidays and weekdends.

The data will be in an ascending sequence of ‘Journey Date’ and ‘Journey Reference’.

Table Updates Required

Access to the reports for the user groups will be required using the following scripts:

INSERT INTO adm_group_report (REPORT_TYPE,GROUP_NAME,REPORT_NAME) VALUES ('CSV','ADMIN','Order Tracking');

INSERT INTO adm_group_report (REPORT_TYPE,GROUP_NAME,REPORT_NAME) VALUES ('REP','ADMIN','Carrier Work Confirmation'); /

The reports and extracts may be created using the following scripts:

INSERT INTO rep_report (NAME,REPORT_TYPE,FILENAME,PROC_NAME,PRINTER_TYPE,ORIENTATION) VALUES ('Order Tracking','CSV','Order_Tracking','DP_CSV2.ORDER_TRACKING','Laser','LANDSCAPE');

INSERT INTO rep_report (NAME,REPORT_TYPE,FILENAME,PROC_NAME,PRINTER_TYPE,ORIENTATION) VALUES ('Carrier Work Confirmation','REP','CARRIER_WORK_CONFIRMATION.rep',,'Laser','LANDSCAPE');

INSERT INTO rep_report (NAME,REPORT_TYPE,FILENAME,PROC_NAME,PRINTER_TYPE,ORIENTATION) VALUES ('Failed Shipment','CSV','DP_CSV2.FAILED_SHIPMENT',,'Laser','LANDSCAPE');

INSERT INTO rep_report (NAME,REPORT_TYPE,FILENAME,PROC_NAME,PRINTER_TYPE,ORIENTATION) VALUES ('DOOD Report','CSV','DP_CSV2.DOOD_REPORT',,'Laser','LANDSCAPE'); /

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('Order Tracking','CSV','PG_FFNUM1','P_YEAR','M','STRING','TO_CHAR(SYSDATE,'RRRR')','Year',,'N');

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','P_MONTH','M','STRING','01','Month');

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('Order Tracking','CSV','PG_FFNUM2','P_WEEK_OFFSET','O','STRING','1','Week Offset',,'N'); /

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('Carrier Work Confirmation','REP','P_SELECT_LIST1','P_START_SCHED','M',,,'From Schedule',

'SELECT sched_name col1, sched_name col2 FROM sch_schedule WHERE sched_status = 'ACTIVE' ORDER BY 1 DES',);

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('Carrier Work Confirmation','REP','P_SELECT_LIST2','P_END_SCHED','M',,,'To Schedule',

'SELECT sched_name col1, sched_name col2 FROM sch_schedule WHERE sched_status = 'ACTIVE' ORDER BY 1 DES',);

/

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('Failed Shipment','CSV','P_START_SCHED','P_START_SCHED','M',,,'From Schedule',,);

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('Failed Shipment','CSV','P_END_SCHED','P_END_SCHED','M',,,'To Schedule',,); /

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('DOOD Report','CSV','P_START_SCHED','P_START_SCHED','M',,,'From Schedule',,);

INSERT INTO rep_report_param (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,CONDITIONAL,DEFAULT_VALUE_TYPE,DEFAULT_VALUE,PARAM_TITLE,SQL_STRING,REPORT_LIST) VALUES ('DOOD Report','CSV','P_END_SCHED','P_END_SCHED','M',,,'To Schedule',,); /

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','01','January')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','02','February')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','03','March')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','04','April')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','05','May')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','06','June')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','07','July')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','08','August')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','09','September')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','10','October')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','11','November')

INSERT INTO rep_param_value (REPORT_NAME,REPORT_TYPE,PARAM_TYPE,PARAM_NAME,DISPLAYED_VALUE) VALUES ('Order Tracking','CSV','PG_VALUE_LIST1','12','December') /

References

Ref No	Document Title & ID	Version	Date
1	EST-287253 DK-8F9GVA Additional Reporting Requirements v1.0.doc	1.0	04/04/2011

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	18/04/2011	Draft	Initial version	PDR
1.0	21/04/2011	Issue	Reviewed and Issued	MJC
2.0	09/05/2011	Issue	Reviewed and Issued	DJM
3.0	16/5/2011	Issue	Reviewed against DHL comments and Issued	DJM

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager