287420

DHL MTS

Development of Reports in Line with Phase 2

FUNCTIONAL SPECIFICATION - 10.6

- 0.3

Reference: FS 287420 AD-8FGFSU

Client Requirement

Change Request Summary:

Development of reports in line with the phase 2 requirements. RIO being produced to obtain an estimate for the work.

Change Request Details:

Development of reports in line with the phase 2 requirements. RIO being produced to obtain an estimate for the work.

Benefits identified as a result of the change:

Service offering.

Solution

A new ‘AA Trip Sheet’ will be required based on the ‘HC Trip Sheet’ with the following modifications:

1. Rename the file to AA_TRIP_SHEET and ensure this is shown here too.
2. Add in a BARCODE at the top of the TRIP DETAILS box that relates to the FIX number (i.e. Trip ID) and prefix the FIX number with the DHL route ID.
3. Replace this column header with ‘Returned LRC’.
4. Replace this column header with ‘Returned SRC’.
5. Replace this column header with ‘Returned LSE’.
6. Replace this column header with ‘Returned ENG’.
7. Replace this column header with ‘Returned STG’.
8. Remove this column header.
9. Put a line across the whole data area under each dealer (to make it easier to see where the breaks are).
10. Fix the ordering so that drop 10 is in sequence with the rest of the drops.
11. Remove this column header.
12. Remove this column header.
13. Remove this column header and the data below.

The same report parameters will be used to select the data.

Points 3 to 7 will not display any data beneath them as they will be blank for the driver to record any quantities.

Five extracts will be required:

1. Asset Scanning
2. Asset Tracking
3. Asset Utilisation
4. Asset On Time Delivery
5. Asset Volume

The ‘Oracle Reporting Suite’ will be used to extract the asset detail and history, plus some order and item details, in a comma-delimited CSV file for uploading into a spreadsheet.

Each of the five extracts may then be created from the data provided in the CSV file as the data can be manipulated using a pivot table in the spreadsheet.

The SQL code for the reporting suite will be setup at the three levels required.

The date range for the location date for the asset history may be specified in the reporting suite as required.

Scope

This change will be applied to system version 10.6.0 on AAMTST and once approved AAMPRD.

Set-up

Pre-requisites

1. The new report will be made available for the authorised user groups.
2. The new extracts will be made available in the ‘Oracle Reporting Suite’.
3. The new extracts will be made available in the ‘Exports’ screen.

Menu Structure

‘Unchanged’

Data

1. The new report parameters will be setup.
2. The new extracts will be setup in the ‘Oracle Reporting Suite’.
3. The new CSV extracts will be setup.
4. See Appendix A for the scripts for the data setup.

Functional Description

AA Trip Sheet

A new ‘AA Trip Sheet’ will be required based on the ‘HC Trip Sheet’ with the following modifications:

1. Rename the file to AA_TRIP_SHEET and ensure this is shown here too.
2. Add in a BARCODE at the top of the TRIP DETAILS box that relates to the FIX number (i.e. Trip ID) and prefix the FIX number with the DHL route ID.
3. Replace this column header with ‘Returned LRC’.
4. Replace this column header with ‘Returned SRC’.
5. Replace this column header with ‘Returned LSE’.
6. Replace this column header with ‘Returned ENG’.
7. Replace this column header with ‘Returned STG’.
8. Remove this column header.
9. Put a line across the whole data area under each dealer (to make it easier to see where the breaks are).
10. Fix the ordering so that drop 10 is in sequence with the rest of the drops.
11. Remove this column header.
12. Remove this column header.
13. Remove this column header and the data below.

The reporting parameters will be as follows:

• From Date
• To Date
• Carrier
• Group Name
• Customer
• Trip ID (optional)
• Owning Depot

For example:

A list of values will be available for the ‘Carrier’, ‘Group Name’, ‘Customer’ and ‘Owning Depot’ parameters, plus the ‘To Date’ must not be before the ‘From Date’.

The ‘From Date’, ‘To Date’ and ‘Owning Depot’ parameters will be mandatory.

The changes to the existing ‘HC Trip Sheet’ may be seen below for the points listed above:

Further details about the changes are described below:

2. The DHL route ID will be used as a prefix for the ‘Trip ID’, for example, ‘A01 FIX-12524’. The barcode will be created for the ‘Trip ID’ (e.g. ‘A01 FIX-12524’) using the formula ‘CF_TRIP_BARCODE’ (i.e. ‘BARCODE_39('*'||:route_code || ‘ ‘ || :trip_id||'*')’).

3. The data column will be blank.

4. The data column will be blank.

5. The data column will be blank.

6. The data column will be blank.

7. The data column will be blank.

9. The separator for the trip stops will be a single line over the width of the report and will appear after any special instructions associated with the stop have been displayed.

10. The close-down stop of the trip will be displayed at the end of the report by changing its frequency. Therefore the stops will be displayed in sequence of the stop number.

N.B. The layout of the report will be changed so that there will not be any spaces where columns have been removed:

8. Key Code

11. Booking Window

12. Temp Code

13. Planned DUs

No other changes will be made in comparison to the ‘HC Trip Sheet’.

The data that will no longer be displayed will not be selected in the query ‘Q_1’:

4. CASES

6. WGT

7. ACT_WGT

8. KEYCODE

12. TEMP_ID

13. QTY

The new report will be made available for authorisation for the users in the ‘Reports’ tab page of the ‘Access Groups’ screen:

Oracle Reporting Suite

An example of the ‘Oracle Reporting Suite’ may be seen below:

Parameters may be entered in the ‘From’ (if ‘Set’ is ‘Y’ or ‘R’) and ‘To’ (if ‘Set’ is ‘R’) columns to select the data for that column.

An example of the data extracted for the report above may be seen below:

Schedule,Trip ID,Trip Status,Stop Location,Stop No.,Order Ref

050812,=t("MAN-00002162"),=t("CONFIRMED"),=t("LOCATION A"),1,=t("3349")

Asset Tracking

The ‘Oracle Reporting Suite’ will be used to extract the asset detail and history in a comma-delimited CSV file for uploading into a spreadsheet.

The actual extract may then be created from the data provided in the CSV file in a spreadsheet.

The SQL code for the reporting suite will be setup at the different levels required.

For example, the date range for the location date for the asset history may be specified in the reporting suite as required.

The SQL code on table ‘ORS_SQL’ will be setup to select records at the different levels as follows:

ORS_TYPE	ORS_LEVEL	ORS_FROM_SQL	ORS_WHERE_SQL	ORS_ORDERBY_SQL
ASSETS	1 (DETAIL)	ASSET_DETAIL AD		AD.ASSET_ID
ASSETS	2 (HISTORY)	ASSET_DETAIL AD, ASSET_HISTORY AH	AD.ASSET_ID = AH.ASSET_ID	AD.ASSET_ID, AH.LOCATION_DATE

The different levels may be setup as follows, for example:

Level	Title	Displayed Title	Set	From	To	Sort	Req	Inc
DETAIL	Asset ID	Asset ID	R				Y	Y
DETAIL	Owner	Customer	R				N	Y
DETAIL	Asset Type	Asset Type	R				N	Y
DETAIL	Origin	Origin	R				N	Y
DETAIL	Current Loc	Current Loc	R				N	Y
DETAIL	Route	Route	R				N	Y
DETAIL	Status	Status	R				N	Y
DETAIL	Comments	Comments	N				N	Y
DETAIL	Created By	Created By	N				N	N
DETAIL	Created Date	Created Date	N				N	Y
DETAIL	Created Time	Created Time	N				N	Y
DETAIL	Inactive	Inactive	Y				N	Y
DETAIL	Open/Close	Open/Close	Y				N	Y
DETAIL	Asset Seq	Asset Seq	R				N	Y
HISTORY	Asset ID	Asset ID	N				N	N
HISTORY	Location Date	Location Date	R				N	Y
HISTORY	Location Time	Location Time	R				N	Y
HISTORY	Location	Dealership	R				N	Y
HISTORY	Action	Action	N				N	Y
HISTORY	Action By	Action By	N				N	N
HISTORY	Process	Process	Y				N	Y

The data extracted will be as follows should all columns be included:

Level	Title	Table	Column	Type
DETAIL	Asset ID	ASSET_DETAIL	ASSET_ID	VARCHAR2(30)
DETAIL	Customer	ASSET_DETAIL	OWNER	VARCHAR2(12)
DETAIL	Asset Type	ASSET_DETAIL	ASSET_TYPE	VARCHAR2(12)
DETAIL	Origin	ASSET_DETAIL	ORIGIN	VARCHAR2(12)
DETAIL	Current Loc	ASSET_DETAIL	CURRENT_LOCATION	VARCHAR2(12)
DETAIL	Route	ASSET_DETAIL	ROUTE	VARCHAR2(12)
DETAIL	Status	ASSET_DETAIL	STATUS	VARCHAR2(12)
DETAIL	Comments	ASSET_DETAIL	COMMENTS	VARCHAR2(150)
DETAIL	Created By	ASSET_DETAIL	CREATED_BY	VARCHAR2(50)
DETAIL	Created Date	ASSET_DETAIL	CREATED_DATE	DATE
DETAIL	Created Time	ASSET_DETAIL	CREATED_DATE	VARCHAR(8)
DETAIL	Inactive	ASSET_DETAIL	INACTIVE	VARCHAR2(1)
DETAIL	Open/Close	ASSET_DETAIL	OPEN_CLOSE	VARCHAR2(12)
DETAIL	Asset Seq	ASSET_DETAIL	ASSET_SEQ	NUMBER
HISTORY	Asset ID	ASSET_HISTORY	ASSET_ID	VARCHAR2(30)
HISTORY	Location Date	ASSET_HISTORY	LOCATION_DATE	DATE
HISTORY	Location Time	ASSET_HISTORY	LOCATION_DATE	VARCHAR(8)
HISTORY	Dealership	ASSET_HISTORY	LOCATION	VARCHAR2(12)
HISTORY	Action	ASSET_HISTORY	ACTION	VARCHAR2(12)
HISTORY	Action By	ASSET_HISTORY	ACTION BY	VARCHAR2(50)
HISTORY	Process	ASSET_HISTORY	PROCESS	VARCHAR2(30)

N.B. The ‘Created’ and ‘Location’ dates and times will be based on a ‘CALCULATION’ and not the file name in item ‘ORS_MASTER_DATA.ORS_FILE’ when setup to enable the date format to be specified (e.g. trunc(ASSET_DETAIL.CREATED_DATE) and to_char(ASSET_DETAIL.CREATED_DATE, ‘HH24:MI:SS’)).

For example,

Asset ID,Customer,Asset Type,Origin,Current Loc,Route,Status,Comments,Created By,Created Date,Inactive,Open/Close,Asset Seq,Location Date,Dealership,Action,Action By,Process

MC040407,MERCEDES,SRC,DHLMILT,DHLROCH,,NEW,,MTS_OWNER,04/04/2011 11:32:20,,,1,04/04/2011 11:32:20,DHLMILT,Created,MTS_OWNER,WCS

MC040407,MERCEDES,SRC,DHLMILT,DHLROCH,,NEW,,MTS_OWNER,04/04/2011 11:32:20,,,1,04/04/2011 11:41:58,DHLMILT,Depart,TM1,WCS

MC040407,MERCEDES,SRC,DHLMILT,DHLROCH,,NEW,,MTS_OWNER,04/04/2011 11:32:20,,,1,04/04/2011 11:47:25,DHLROCH,Arrive,TR1,WCS

Asset Scanning

The ‘Oracle Reporting Suite’ will be used to extract the trips and orders in a comma-delimited CSV file for uploading into a spreadsheet.

The actual extract may then be created from the data provided in the CSV file in a spreadsheet.

The SQL code for the reporting suite will be setup at the different levels required.

For example, the date range for the schedule for the trip may be specified in the reporting suite as required.

The SQL code on table ‘ORS_SQL’ will be setup to select records at the different levels as follows:

ORS_TYPE	ORS_LEVEL	ORS_FROM_SQL	ORS_WHERE_SQL	ORS_ORDERBY_SQL
SCANS	1 (TRIP)	SCH_TRIP ST		ST.SCHED_NAME, ST.TRIP_ID
SCANS	2 (STOP)	SCH_TRIP ST, SCH_TRIP_STOP STS	ST.TRIP_ID = STS.TRIP_ID	ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO
SCANS	3 (ORDER)	SCH_TRIP ST, SCH_TRIP_STOP STS, SCH_HAULAGE_ACTIVITY SHA, SCH_ORD SO, RES_DESPATCH_UNIT_TYPE RDUT	ST.TRIP_ID = STS.TRIP_ID AND STS.STOP_ID = SHA.STOP_ID AND SHA.OMS_REF = SO.OMS_REF AND SOL.DU_TYPE = RDUT.DU_TYPE	ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO, SO.OMS_REF
SCANS	4 (ASSET)	SCH_TRIP ST, SCH_TRIP_STOP STS, SCH_HAULAGE_ACTIVITY SHA, SCH_ORD SO, SCH_ORD_ITEMS SOI, RES_DESPATCH_UNIT_TYPE RDUT, ASSET_DETAIL AD	ST.TRIP_ID = STS.TRIP_ID AND STS.STOP_ID = SHA.STOP_ID AND SHA.OMS_REF = SO.OMS_REF AND SO.OMS_REF = SOI.OMS_REF AND SOI.DU_TYPE = RDUT.DU_TYPE AND SOI.ITEM_IDENTIFIER = AD.ASSET_ID	ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO, SO.OMS_REF, SOI.ITEM_IDENTIFIER
SCANS	5 (REASON)	SCH_TRIP ST, SCH_TRIP_STOP STS, SCH_HAULAGE_ACTIVITY SHA, SCH_ORD SO, SCH_ORD_ITEMS SOI, RES_DESPATCH_UNIT_TYPE RDUT, ASSET_DETAIL AD, SCH_ORD_ITEMS_REASONS SOIR	ST.TRIP_ID = STS.TRIP_ID AND STS.STOP_ID = SHA.STOP_ID AND SHA.OMS_REF = SO.OMS_REF AND SO.OMS_REF = SOI.OMS_REF AND SOI.DU_TYPE = RDUT.DU_TYPE AND SOI.ITEM_IDENTIFIER = AD.ASSET_ID AND SOI.OMS_REF = SOIR.OMS_REF AND SOI.ITEM_IDENTIFIER = SOIR.ITEM_IDENTIFIER	ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO, SO.OMS_REF, SOI.ITEM_IDENTIFIER, SOIR.REASON_CODE

The different levels may be setup as follows, for example:

Level	Title	Displayed Title	Set	From	To	Sort	Req	Inc
TRIP	Schedule	Schedule	R				Y	Y
TRIP	Trip	Trip	R				N	Y
TRIP	Trip Status	Trip Status	Y				N	Y
TRIP	Route	Route	Y				N	Y
TRIP	Driver ID	Driver ID	Y				N	Y
STOP	Stop Location	Stop Location	Y				N	Y
ORDER	OMS Ref	OMS Ref	N				N	Y
ORDER	From Location	From Loc	Y				N	Y
ORDER	To Location	To Loc	Y				N	Y
ORDER	Order Status	Order Status	Y				N	Y
ORDER	POD	POD	Y				N	Y
ORDER	DU Type	DU Type	Y				N	Y
ORDER	Activity	Activity	Y				N	Y
ORDER	Planned Qty	Planned Qty	N				N	Y
ORDER	Actual Qty	Actual Qty	N				N	Y
ORDER	Despatched	Despatched Qty	N				N	Y
ORDER	Delivered	Delivered Qty	N				N	Y
ASSET	Asset ID	Asset ID	N				N	Y
ASSET	Origin	Origin	Y				N	Y
ASSET	Customer	Customer	Y				N	Y
REASON	Reason Code	Reason Code	Y				N	Y

The data extracted will be as follows should all columns be included:

Level	Title	Table	Column	Type
TRIP	Schedule	SCH_TRIP	SCHED_NAME	VARCHAR2(12)
TRIP	Trip	SCH_TRIP	TRIP_ID	VARCHAR2(12)
TRIP	Trip Status	SCH_TRIP	TRIP_STATUS	VARCHAR2(12)
TRIP	Route	SCH_TRIP	ROUTE_CODE	VARCHAR2(12)
TRIP	Driver ID	SCH_TRIP	DRIVER_ID	NUMBER(10)
STOP	Stop Location	SCH_TRIP_STOP	LOCATION_ID	VARCHAR2(12)
ORDER	OMS Ref	SCH_ORD	OMS_REF	VARCHAR2(12)
ORDER	From Loc	SCH_ORD	FROM_LOC	VARCHAR2(12)
ORDER	To Loc	SCH_ORD	TO_LOC	VARCHAR2(12)
ORDER	Order Status	SCH_ORD	STATUS	VARCHAR2(12)
ORDER	POD	SCH_ORD	POD	VARCHAR2(12)
ORDER	DU Type	SCH_ORD_ITEMS	DU_TYPE	VARCHAR2(1)
ORDER	RPE	RES_DESPATCH_UNIT_TYPE	RPE	NUMBER(10,4)
ORDER	Activity	SCH_HAULAGE_ACTIVITY	ACTIVITY_NAME	VARCHAR2(35)
ORDER	Planned Qty	SCH_ORD_ITEMS	QTY_ORDERED	NUMBER(8)
ORDER	Despatched Qty	SCH_ORD_ITEMS	QTY_TO_DELIVER	NUMBER(8)
ORDER	Delivered Qty	SCH_ORD_ITEMS	QTY_DELIVERED	NUMBER(8)
ASSET	Asset ID	ASSET_DETAIL	ASSET_ID	VARCHAR2(30)
ASSET	Origin	ASSET_DETAIL	ORIGIN	VARCHAR2(12)
ASSET	Customer	ASSET_DETAIL	OWNER	VARCHAR2(12)
REASON	Reason Code	SCH_ORD_ITEMS_REASONS	REASON_CODE	VARCHAR2(5)

Asset Volume

The extract may be created as in section 3.2.2.

Exports

Asset On Time Delivery

A separate extract will be run for this report via the ‘Export’ screen.

The actual extract may then be created from the data provided in the CSV file in a spreadsheet.

The parameters will be:

Parameter	Type
From Date	P_START_DATE
To Date	P_END_DATE
Route	PG_SELECT_LIST1
Location	PG_SELECT_LIST2
Customer	PG_SELECT_LIST3

A list of values will be available for the ‘Route’ (i.e. for asset routes), ‘Trunk’ (i.e. ‘Y’ or ‘N’ for ‘IN TRANSIT’), ‘Location’ (i.e. for all locations) and ‘Customer’ (i.e. for asset owners), plus the ‘To Date’ must not be before the ‘From date’.

The ‘From Date’ and ‘To Date’ parameters will be mandatory and will default to the system date.

The extract will assess the movements of assets on trips on the schedules specified.

The parameters will select the data as follows:

Parameter	Table	Column
Route	SCH_TRIP	ROUTE_CODE
Location	ASSET_HISTORY	LOCATION_ID
Customer	ASSET_DETAIL	OWNER

The layout of the extract will be:

Dealership, Date, Date, Date, Date, Date, Date, Date, Date, Date, Date, Date, Date, Date, Date, Variance, Variance, Variance, Variance, Variance, Variance, Variance

Dealership, LRC, SRC, ENG, STL, LSE, LCE, PLAN, LRC, SRC, ENG, STL, LSE, LCE, POD, LRC, SRC, ENG, STL, LSE, LCE, Variance

The last 21 items may be repeated for the schedules selected.

The items ‘Dealership’, ‘Date’ and ‘Variance’ may be combined in the spreadsheet to create the layout displayed below:

The trips may be obtained from location and dates of the asset history.

The detail section will display the number of assets per DU type contained on the trips made for the routes.

The ‘PLAN’ time will be the ‘Late Delivery’ time of the order (i.e. ‘SCH_ORD.LATE_DEL’) that contains the asset.

The ‘POD’ time will be the ‘POD Date’ of the order (i.e. ‘SCH_ORD.POD_DATE’).

The ‘Variance’ will be the ‘POD’ time minus the ‘PLAN’ time.

The extract will run new procedure ‘DP_CSV2.ASSET_ON_TIME_DEL’.

Asset Utilisation

A separate extract will be run for this report via the ‘Export’ screen.

The actual extract may then be created from the data provided in the CSV file in a spreadsheet.

The parameters will be:

Parameter	Type
From Date	P_START_DATE
To Date	P_END_DATE
Route	PG_SELECT_LIST1
Location	PG_SELECT_LIST2
Current Location	PG_SELECT_LIST3
Customer	PG_SELECT_LIST4
Vehicle	PG_SELECT_LIST5

A list of values will be available for the ‘Route’ (i.e. for asset routes), ‘Location’ (i.e. for all locations), ‘Current location’ (i.e. for all locations or ‘IN TRANSIT’ for trunks), ‘Customer’ (i.e. for asset owners) and ‘Vehicle’ (i.e. for tractor IDs) parameters, plus the ‘To Date’ must not be before the ‘From date’.

The ‘From Date’ and ‘To Date’ parameters will be mandatory and will default to the system date.

The extract will assess the movements of assets on trips on the schedules specified.

The parameters will select the data as follows:

Parameter	Table	Column
Route	SCH_TRIP	ROUTE_CODE
Location	ASSET_HISTORY	LOCATION_ID
Current Location	ASSET_DETAIL	CURRENT_LOCATION
Customer	ASSET_DETAIL	OWNER
Vehicle	SCH_TRIP	TRACTOR_ID

The layout of the extract will be:

Route, Capacity (LCE), Date, Date, Date, Date, Date, Date, Capacity (LCE)

Route, Capacity (LCE), LRC, SRC, ENG, STL, LSE, LCE, Capacity (LCE)

The last 7 items may be repeated for the schedules selected.

The items ‘Route’, ‘Capacity (LCE)’ and ‘Date’ may be combined in the spreadsheet to create the layout displayed below:

The capacity may be obtained from the trailer type used on the trip.

The trips may be obtained from location and dates of the asset history.

The detail section will display the number of assets per DU type contained on the trips made for the routes.

The extract will run new procedure ‘DP_CSV2.ASSET_UTILISATION’.

Table Updates Required

1. The scripts to setup the ‘AA Trip Sheet’ report and exports may be run:

--Report Name insert into rep_report (name, report_type, filename, printer_type, show_pform, orientation) values ('AA Trip Sheet','REP','AA_TRIP_SHEET.rep','Laser','F','LANDSCAPE');

insert into rep_report (name, report_type, filename, proc_name, printer_type, show_pform, orientation) values ('Asset Utilisation','CSV','ASSET_UTILISATION','DP_CSV2.ASSET_UTILISATION','Laser',,'LANDSCAPE');

insert into rep_report (name, report_type, filename, proc_name, printer_type, show_pform, orientation) values ('Asset On Time Delivery','CSV','ASSET_ON_TIME_DEL','DP_CSV2.ASSET_ON_TIME_DEL','Laser',,'LANDSCAPE'); /

--Report Parameters

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, default_value_type, param_title) values ('AA Trip Sheet', 'REP', 'P_START_DATETIME', 'P_START_DATETIME', 'M', 'TODAY', 'From Date');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, default_value_type, param_title) values ('AA Trip Sheet', 'REP', 'P_END_DATETIME', 'P_END_DATETIME', 'M', 'TODAY', 'To Date');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('AA Trip Sheet', 'REP', 'PG_SELECT_LIST1', 'P_CARRIER', 'O', 'Carrier', 'SELECT distinct(carrier_name) COL1, carrier_id COL2 FROM res_carrier rc, adm_user_param aup WHERE rc.carrier_id like DECODE(aup.value,ALL,%%,aup.value) AND aup.username = (SELECT user FROM dual) AND aup.param_type = CARRIER ORDER BY Carrier_Name');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('AA Trip Sheet', 'REP', 'PG_SELECT_LIST2', 'P_GROUP_NAME', 'O', 'Group Name', 'SELECT distinct(ord.group_name) COL1, ord.group_name COL2 FROM sch_ord ord where ord.group_name is not null order by ord.group_name');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('AA Trip Sheet', 'REP', 'PG_SELECT_LIST3', 'P_CUSTOMER', 'O', 'Customer', 'SELECT distinct(customer_name) COL1, customer_id COL2 FROM org_customer oc, adm_user_param aup WHERE oc.customer_name like DECODE(aup.value,ALL,%%,aup.value) AND aup.username = (SELECT user FROM dual) AND aup.param_type = CUSTOMER ORDER by customer_name');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string, lov_title, col1_title, col2_title) values ('AA Trip Sheet', 'REP', 'PG_LOV1', 'P_OWN_DEPOT', 'M', 'Owning Depot', 'SELECT distinct(gl.location_id) COL1, gl.location_name COL2 FROM geo_location gl, adm_user_param aup WHERE gl.location_id = aup.value AND aup.username = (SELECT user FROM dual) AND aup.param_type = BASED_AT AND gl.depot = RDC and NVL(gl.inactive,N) = N UNION SELECT distinct (gl.location_id) COL1, gl.location_name COL2 FROM geo_location gl, adm_user_param aup WHERE gl.location_name like DECODE(aup.value,Y,%%) AND aup.username = (SELECT user FROM dual) and aup.param_type = ALL_DEPOTS and gl.depot = RDC and NVL(gl.inactive,N) = N', 'Depot', 'Location Id', 'Name');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title) values ('AA Trip Sheet', 'REP', 'PG_FFCHAR1', 'P_TRIP_ID', 'O', 'Trip ID (optional)'); /

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, default_value_type, param_title) values ('Asset Utilisation', 'CSV', 'P_START_DATE', 'P_START_DATE', 'M', 'TODAY', 'From Date');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, default_value_type, param_title) values ('Asset Utilisation', 'CSV', 'P_END_DATE', 'P_END_DATE', 'M', 'TODAY', 'To Date');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset Utilisation', 'CSV', 'PG_SELECT_LIST1', 'P_ROUTE', 'O', 'Route', 'SELECT distinct(route) COL1, route COL2 FROM asset_detail');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset Utilisation', 'CSV', 'PG_SELECT_LIST2', 'P_LOCATION', 'O', 'Location', 'SELECT distinct(location_id) COL1, location_name COL2 FROM geo_location');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset Utilisation', 'CSV', 'PG_SELECT_LIST3', 'P_CURR_LOCATION', 'O', 'Current Location', 'SELECT distinct(location_id) COL1, location_name COL2 FROM geo_location');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset Utilisation', 'CSV', 'PG_SELECT_LIST4', 'P_CUSTOMER', 'O', 'Customer', 'SELECT distinct(owner) COL1, owner COL2 FROM asset_detail');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset Utilisation', 'CSV', 'PG_SELECT_LIST5', 'P_VEHICLE', 'O', 'Vehicle', 'SELECT distinct(tractor_id) COL1, tractor_id COL2 FROM res_tractor'); /

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, default_value_type, param_title) values ('Asset On Time Delivery', 'CSV', 'P_START_DATE', 'P_START_DATE', 'M', 'TODAY', 'From Date');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, default_value_type, param_title) values ('Asset On Time Delivery', 'CSV', 'P_END_DATE', 'P_END_DATE', 'M', 'TODAY', 'To Date');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset On Time Delivery', 'CSV', 'PG_SELECT_LIST1', 'P_ROUTE', 'O', 'Route', 'SELECT distinct(route) COL1, route COL2 FROM asset_detail');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset On Time Delivery', 'CSV', 'PG_SELECT_LIST2', 'P_LOCATION', 'O', 'Location', 'SELECT distinct(location_id) COL1, location_name COL2 FROM geo_location');

insert into rep_report_param(report_name, report_type, param_type, param_name, conditional, param_title, sql_string) values ('Asset On Time Delivery', 'CSV', 'PG_SELECT_LIST4', 'P_CUSTOMER', 'O', 'Customer', 'SELECT distinct(owner) COL1, owner COL2 FROM asset_detail'); /

2. The script to setup the SQL code for the ‘Oracle Reporting Suite’ may be run:

--Report Name insert into ors_sql (ors_type, ors_level, ors_from_sql, ors_where_sql, ors_orderby_sql) values ('ASSETS','DETAIL','ASSET_DETAIL AD',,'AD.ASSET_ID');

insert into ors_sql (ors_type, ors_level, ors_from_sql, ors_where_sql, ors_orderby_sql) values ('ASSETS','HISTORY','ASSET_DETAIL AD, ASSET_HISTORY AH','AD.ASSET_ID = AH.ASSET_ID','AD.ASSET_ID, AH.LOCATION_DATE'); /

insert into ors_sql (ors_type, ors_level, ors_from_sql, ors_where_sql, ors_orderby_sql) values ('SCANS','TRIP','SCH_TRIP ST',,'ST.SCHED_NAME, ST.TRIP_ID');

insert into ors_sql (ors_type, ors_level, ors_from_sql, ors_where_sql, ors_orderby_sql) values ('SCANS','STOP','SCH_TRIP ST, SCH_TRIP_STOP STS','ST.TRIP_ID = STS.TRIP_ID','ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO');

insert into ors_sql (ors_type, ors_level, ors_from_sql, ors_where_sql, ors_orderby_sql) values ('SCANS','ORDER','SCH_TRIP ST, SCH_TRIP_STOP STS, SCH_HAULAGE_ACTIVITY SHA, SCH_ORD SO, RES_DESPATCH_UNIT_TYPE RDUT', 'ST.TRIP_ID = STS.TRIP_ID AND STS.STOP_ID = SHA.STOP_ID AND SHA.OMS_REF = SO.OMS_REF AND SOL.DU_TYPE = RDUT.DU_TYPE','ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO, SO.OMS_REF');

insert into ors_sql (ors_type, ors_level, ors_from_sql, ors_where_sql, ors_orderby_sql) values ('SCANS','ASSET','SCH_TRIP ST, SCH_TRIP_STOP STS, SCH_HAULAGE_ACTIVITY SHA, SCH_ORD SO, SCH_ORD_ITEMS SOI, RES_DESPATCH_UNIT_TYPE RDUT, ASSET_DETAIL AD,

ASSET_HISTORY AH',

'ST.TRIP_ID = STS.TRIP_ID AND STS.STOP_ID = SHA.STOP_ID AND SHA.OMS_REF = SO.OMS_REF AND SO.OMS_REF = SOI.OMS_REF AND SOI.DU_TYPE = RDUT.DU_TYPE AND SOI.ITEM_IDENTIFIER = AD.ASSET_ID', 'ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO, SO.OMS_REF, SOI.ITEM_IDENTIFIER');

insert into ors_sql (ors_type, ors_level, ors_from_sql, ors_where_sql, ors_orderby_sql) values ('SCANS','REASON','SCH_TRIP ST, SCH_TRIP_STOP STS, SCH_HAULAGE_ACTIVITY SHA, SCH_ORD SO, SCH_ORD_ITEMS SOI, RES_DESPATCH_UNIT_TYPE RDUT, ASSET_DETAIL AD, SCH_ORD_ITEMS_REASONS SOIR', 'ST.TRIP_ID = STS.TRIP_ID AND STS.STOP_ID = SHA.STOP_ID AND SHA.OMS_REF = SO.OMS_REF AND SO.OMS_REF = SOI.OMS_REF AND SOI.DU_TYPE = RDUT.DU_TYPE AND SOI.ITEM_IDENTIFIER = AD.ASSET_ID AND SOI.OMS_REF = SOIR.OMS_REF AND SOI.ITEM_IDENTIFIER = SOIR.ITEM_IDENTIFIER', 'ST.SCHED_NAME, ST.TRIP_ID, STS.STOP_NO, SO.OMS_REF, SOI.ITEM_IDENTIFIER, SOIR.REASON_CODE'); /

References

Ref No	Document Title & ID	Version	Date
1	EST-287420 AD-8FGFSU Development of Reports in Line with Phase 2 v1.0.doc	1.0	19/04/11

Glossary

Term or Acronym	Meaning
C-TMS	Calidus TMS

Document History

Version	Date	Status	Reason	Initials
0.1	04/05/11	Draft	Initial version	PDR

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager