Oracle Workflow Tables with links

---------Get Process details------------------

SELECT distinct wa_process.NAME, wat_process.display_name, wa_process.item_type,wa_process.TYPE,wa_process.FUNCTION
,trunc(wa_process.BEGIN_DATE),trunc(wa_process.END_DATE),wa_process.VERSION
  FROM wf_activities wa_process, wf_activities_tl wat_process
WHERE wa_process.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_process.TYPE = 'PROCESS'
--   AND wa_process.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type =:A AND TYPE = 'PROCESS')
   AND wat_process.item_type = wa_process.item_type
   AND wat_process.VERSION = wa_process.VERSION
   AND wat_process.LANGUAGE = 'US'
   AND wat_process.NAME = wa_process.NAME
   And to_char(trunc(wa_process.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_process.END_DATE) is null or trunc(wa_process.END_DATE) > sysdate)
UNION
---------Get Event details------------------
SELECT distinct wa_event.NAME, wat_event.display_name, wa_event.item_type,wa_event.TYPE,wa_event.FUNCTION
,trunc(wa_event.BEGIN_DATE),trunc(wa_event.END_DATE),wa_event.VERSION
  FROM wf_activities wa_event, wf_activities_tl wat_event
WHERE wa_event.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_event.TYPE = 'EVENT'
--   AND wa_event.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'EVENT')
   AND wat_event.item_type = wa_event.item_type
   AND wat_event.VERSION = wa_event.VERSION
   AND wat_event.LANGUAGE = 'US'
   AND wat_event.NAME = wa_event.NAME
   And to_char(trunc(wa_event.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_event.END_DATE) is null or trunc(wa_event.END_DATE) > sysdate)
UNION
---------Get Function details------------------
SELECT distinct wa_func.NAME, wat_func.display_name, wa_func.item_type,wa_func.TYPE,wa_func.FUNCTION
,trunc(wa_func.BEGIN_DATE),trunc(wa_func.END_DATE),wa_func.VERSION
  FROM wf_activities wa_func, wf_activities_tl wat_func
WHERE wa_func.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_func.TYPE = 'FUNCTION'
--   AND wa_func.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'FUNCTION')
   AND wat_func.item_type = wa_func.item_type
   AND wat_func.VERSION = wa_func.VERSION
   AND wat_func.LANGUAGE = 'US'
   AND wat_func.NAME = wa_func.NAME
   And to_char(trunc(wa_func.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_func.END_DATE) is null or trunc(wa_func.END_DATE) > sysdate)
UNION  
---------Get Notification details------------------
SELECT distinct wa_not.NAME, wat_not.display_name, wa_not.item_type,wa_not.TYPE,wa_not.FUNCTION
,trunc(wa_not.BEGIN_DATE),trunc(wa_not.END_DATE),wa_not.VERSION
  FROM wf_activities wa_not, wf_activities_tl wat_not
WHERE wa_not.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_not.TYPE = 'NOTICE'
--   AND wa_not.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'NOTICE')
   AND wat_not.item_type = wa_not.item_type
   AND wat_not.VERSION = wa_not.VERSION
   AND wat_not.LANGUAGE = 'US'
   AND wat_not.NAME = wa_not.NAME
   And to_char(trunc(wa_not.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_not.END_DATE) is null or trunc(wa_not.END_DATE) > sysdate)
UNION
---------Get Message  details------------------
SELECT ws.name,wst.display_name,ws.TYPE,'Message',null,null,null,null FROM wf_messages ws,WF_messages_TL wst
Where ws.TYPE IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.type=wst.TYPE
And ws.name=wst.name
And wst.language='US'
UNION
---------Get Message Attribute details------------------
Select ws.NAME,wst.DISPLAY_NAME,ws.message_type,'Message Attribute',null,null,null,null
from  WF_MESSAGE_ATTRIBUTES ws,WF_MESSAGE_ATTRIBUTES_TL wst
Where ws.message_type  IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.NAME=wst.NAME
And ws.message_TYPE=wst.MESSAGE_TYPE
And wst.language='US'
Union
---------Get Lookup  details------------------
Select  wl.LOOKUP_TYPE NAME,wlt.DISPLAY_NAME,wl.item_type,'Lookup Type',null,null,null,null
from wf_lookup_types wl,
      wf_lookup_types_TL wlt
  Where wl.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And wl.LOOKUP_TYPE=wlt.LOOKUP_TYPE
And wl.ITEM_TYPE=wlt.ITEM_TYPE
And wlt.language='US'
UNION
---------Get Item Attribute details------------------
Select WIA.NAME,wit.DISPLAY_NAME,WIA.item_type,'Item Attribute',null,null,null,null
FROM
WF_ITEM_ATTRIBUTES WIA,
WF_ITEM_ATTRIBUTES_TL WIT
Where WIA.item_type =WIT.item_type
And WIA.name=WIT.name
And language='US'
And WIA.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
order by 3,4;