Fields:

Field Name	Common Type	Database Type	Generic Field ID	Array Size	Default	Flags	Comment
case_objid	LONG	int				DIRECT	Internal record number of case
case_owner_objid	LONG	int				DIRECT	Internal record number of user
case_status	CHAR	varchar		80			Title of condition for case
description	CHAR	varchar		255			Task description
duration	LONG	int				DIRECT	Expected/actual amount of time to complete the task in seconds
engineer_eta	CLK_TIME	datetime					Proposed date/time of scheduled appointment or commitment
objid	LONG	int	3			DIRECT	Internal record number of disptchfe
scheduled_from	CLK_TIME	datetime					Requested date and time of field engineer's arrival at dispatch site
subc_objid	LONG	int				DIRECT	Internal record number of subcase
subc_owner_objid	LONG	int				DIRECT	Internal record number of user
subc_status	CHAR	varchar		80			Title of condition for subcase
t_e_status	CHAR	varchar		30			Resolution code for T&E log. From user-defined pop up with default name RESOLUTION_CODE
work_order	CHAR	varchar		80			Work order number entered by the user

SQL:

(
select a.objid as objid, a.disptchfe2case as case_objid, 0 as subc_objid, a.work_order as work_order, a.description as description,
a.requested_eta as scheduled_from, a.duration as duration, a.appointment as engineer_eta,
b.resolution as t_e_status, c.case_owner2user as case_owner_objid, d.title as case_status,
0 as subc_owner_objid, '0' as subc_status
from table_disptchfe a, table_onsite_log b, table_case c, table_condition d
where c.objid = a.disptchfe2case and b.disfe_onsit2disptchfe = a.objid and b.creation_time =
(select max(creation_time) from table_onsite_log where disfe_onsit2disptchfe=a.objid) and c.case_state2condition = d.objid
union
select a.objid as objid, a.disptchfe2case as case_objid, 0 as subc_objid, a.work_order as work_order, a.description as description,
a.requested_eta as scheduled_from, a.duration as duration, a.appointment as engineer_eta,
b.elm_title as t_e_status, c.case_owner2user as case_owner_objid, d.title as case_status,
0 as subc_owner_objid, '0' as subc_status
from table_disptchfe a, table_hgsl2hgse b, table_case c, table_condition d
where c.objid = a.disptchfe2case and a.objid not in (select disfe_onsit2disptchfe from table_onsite_log where disfe_onsit2disptchfe IS NOT NULL)
and c.case_state2condition = d.objid and b.lst_title='TELOG_STATUS' and b.elm_rank=0
union
select a.objid as objid, 0 as case_objid, a.disptchfe2subcase as subc_objid, a.work_order as work_order, a.description as description,
a.requested_eta as scheduled_from, a.duration as duration, a.appointment as engineer_eta,
b.resolution as t_e_status, 0 as case_owner_objid, '0' as case_status,
c.subc_owner2user as subc_owner_objid, d.title as subc_status
from table_disptchfe a, table_onsite_log b, table_subcase c, table_condition d
where c.objid = a.disptchfe2subcase and b.disfe_onsit2disptchfe = a.objid and b.creation_time =
(select max(creation_time) from table_onsite_log where disfe_onsit2disptchfe=a.objid) and c.subc_state2condition = d.objid
union
select a.objid as objid, 0 as case_objid, a.disptchfe2subcase as subc_objid, a.work_order as work_order, a.description as description,
a.requested_eta as scheduled_from, a.duration as duration, a.appointment as engineer_eta,
b.elm_title as t_e_status, 0 as case_owner_objid, '0' as case_status,
c.subc_owner2user as subc_owner_objid, d.title as subc_status
from table_disptchfe a, table_hgsl2hgse b, table_subcase c, table_condition d
where c.objid = a.disptchfe2subcase and a.objid not in (select disfe_onsit2disptchfe from table_onsite_log where disfe_onsit2disptchfe IS NOT NULL)
and c.subc_state2condition = d.objid and b.lst_title='TELOG_STATUS' and b.elm_rank=0
)