DenormalizedViewsForReporting

-- Select for RT_TICKETS_VW
-- Non-Disabled Tickets Only (No Custom Fields) View
create or replace view RT_TICKETS_VW as
select distinct A.Id as Ticket, B.Name as Queue, A.Subject, A.Status, A.Priority,
       C.RealName as OwnerName, C.EmailAddress, A.TimeEstimated, A.TimeWorked, A.TimeLeft,
       trunc(A.Created) as Created,
       trunc(A.Started) as Started,
       trunc(A.Due) as Due,
       trunc(A.Resolved) as Resolved,
       trunc(A.LastUpdated) as LastUpdated
from TICKETS A,
     QUEUES B,
     USERS C
where A.Type = 'ticket'
and   A.Id   = A.EffectiveId
and   A.Status <> 'deleted'
and   A.Disabled = 0
and   B.Id = A.Queue
and   C.Id = A.Owner;

grant select on RT_TICKETS_VW to RTACCT;

-- Select for RT_TICKET_CUSTOM_FIELDS_VW
-- Ticket Custom Fields Only View
create or replace view RT_TICKET_CUSTOM_FIELDS_VW as
select distinct T.Id as Ticket, nvl(C.Name, ' ') as CustomField, nvl(C.Content, ' ') as Content
from rtadm.TICKETS T
left outer join (select A.ObjectId, B.Name, A.Content
                 from rtadm.OBJECTCUSTOMFIELDVALUES A,
                      rtadm.CUSTOMFIELDS B

                 Where A.ObjectType = 'RT::Ticket'
                 and   A.Disabled = 0
                 and   B.Id = A.CustomField
                 and   B.Disabled = 0) C
on T.Id = C.ObjectId
where T.Type = 'ticket'
and   T.Id = T.EffectiveId
and   T.Status <> 'deleted'
and   T.Disabled = 0;

grant select on RT_TICKET_CUSTOM_FIELDS_VW to RTACCT;

-- Select for RT_TICKET_LINKS_VW
-- Ticket Links Only View
create or replace view RT_TICKET_LINKS_VW as
select distinct A.LocalBase as Base, A.LocalTarget as Target, A.Type
from LINKS A,
     TICKETS B
where A.Type <> 'MergedInto'
and   B.Id = A.LocalBase
and   B.Disabled = 0;

grant select on RT_TICKET_LINKS_VW to RTACCT;

-- Select for RT_TICKET_REQUESTORS_VW
-- Ticket Requestors Only View
create or replace view RT_TICKET_REQUESTORS_VW as
select distinct A.Instance as Ticket, C.RealName as Name, C.EmailAddress
from GROUPS A,
     GROUPMEMBERS B,
     USERS C
where A.Domain = 'RT::Ticket-Role'
  and A.Type = 'Requestor'
  and B.GroupId = A.Id
  and B.MemberId = C.Id;

grant select on RT_TICKET_REQUESTORS_VW to RTACCT;

-- Select for RT_TICKET_COMMENTS_VW
-- Ticket Comments Only View
create or replace view RT_TICKET_COMMENTS_VW as
select distinct B.EffectiveId as Ticket, A.Created, DBMS_LOB.SubStr(C.Content, 4000, 1) Content
from TRANSACTIONS A,
     TICKETS B,
     ATTACHMENTS C
where A.ObjectType = 'RT::Ticket'
  and A.Type = 'Comment'
  and A.ObjectId = B.Id
  and B.Status <> 'deleted'
  and A.Id = C.TransactionId
  and C.ContentType = 'text/plain'
  and C.ContentEncoding = 'none'
  order by B.EffectiveID, A.Created;

grant select on RT_TICKET_COMMENTS_VW to RTACCT;


-- This is an SQL report using the RT Views
-- for a report for Queue "XXXXX"
Select T.Ticket, T.Subject, R.Name as Requestor, T.Started as "Date Opened", T.Resolved as "Date Resolved",
      CF1.Content as "Resolution Type", T.Priority, T.OwnerName as Owner, C.Content as Comments,
      T.Status, CF2.Content as "Work-State", T.Due
From RT_TICKETS_VW T,
     RT_TICKET_REQUESTORS_VW R,
     RT_TICKET_COMMENTS_VW C,
     RT_TICKET_CUSTOM_FIELDS_VW CF1,
     RT_TICKET_CUSTOM_FIELDS_VW CF2
Where T.Queue = 'XXXXX'
  and T.Ticket = R.Ticket
  and T.Ticket = C.Ticket(+)
  and T.Ticket = CF1.Ticket(+)
  and T.Ticket = CF2.Ticket(+)
  and CF1.CustomField(+) = 'Resolution Type'
  and CF2.CustomField(+) = 'Work-State';

Contributed by Kenn