Report CertificateOfCompletionByitem

SELECT s.lname, 
       s.fname, 
       s.mi, 
       c.cpnt_title, 
       h.compl_dte, 
       Nvl(userPref.preferred_timezone, pkg_state.get_default_timezone) AS 
       preferred_timezone 
FROM   pa_student s, 
       pv_course c, 
       pa_cpnt_evthst h, 
       pa_cmpl_stat cs, 
       pa_user_preference userPref 
WHERE  h.cpnt_typ_id = c.cpnt_typ_id 
       AND s.stud_id = userPref.user_id(+) 
       AND userPref.user_type(+) = 'S' 
       AND h.cpnt_id = c.cpnt_id 
       AND h.rev_dte = c.rev_dte 
       AND s.stud_id = h.stud_id 
       AND h.cmpl_stat_id = cs.cmpl_stat_id 
       AND cs.provide_crdt = 'Y' 
/** and s.stud_id IN [UserSearch]  and (c.cpnt_typ_id,c.cpnt_id,c.rev_dte) IN [ItemSearch]  and [security:pa_student s] */ 
ORDER  BY lname, 
          fname, 
          mi, 
          h.cpnt_typ_id, 
          h.cpnt_id, 
          h.rev_dte 

SELECT s.lname, 
       s.fname, 
       s.mi, 
       c.cpnt_title, 
       '' AS compl_dte, 
       '' AS preferred_timezone 
FROM   pa_student s, 
       pv_course c 
WHERE  1 = 1 
/** and s.stud_id IN [UserSearch]*/ 
/** and (c.cpnt_typ_id,c.cpnt_id,c.rev_dte) IN [ItemSearch] */ 
/** and [security:pa_student s] */ 
ORDER  BY lname, 
          fname, 
          mi, 
          cpnt_typ_id, 
          cpnt_id, 
          rev_dte