Report UserData

SELECT sc.stud_id, 
       sc.cpty_id, 
       ct.cpty_type_desc, 
       c.cpty_desc, 
       sc.reqd_lvl, 
       pkg_student.Get_stud_cpty_assessment (sc.stud_id, sc.cpty_id) AS 
       assess_lvl, 
       MAX.max_assess_dte 
FROM   pa_stud_cpty sc, 
       pa_cpty c, 
       pa_cpty_type ct, 
       (SELECT cpty_id, 
               Max (assess_dte) AS max_assess_dte 
        FROM   pa_stud_cpty_assessment 
        WHERE  stud_id = ? 
        GROUP  BY cpty_id) MAX 
WHERE  sc.stud_id = ? 
       AND sc.cpty_id = c.cpty_id 
       AND sc.cpty_id = MAX.cpty_id(+) 
       AND c.cpty_type = ct.cpty_type_id 

SELECT * 
FROM   (SELECT cpnt_typ_id, 
               cpnt_id, 
               rev_dte, 
               cpnt_title, 
               rtyp_desc, 
               req_dte, 
               ?                         AS preferred_timezone, 
               Row_number () 
                 over ( 
                   PARTITION BY cpnt_typ_id, cpnt_id, rev_dte 
                   ORDER BY req_dte ASC) row_num 
        FROM   (SELECT sqc.stud_id, 
                       sqc.cpnt_typ_id, 
                       sqc.cpnt_id, 
                       sqc.rev_dte, 
                       c.cpnt_title, 
                       sqc.req_dte, 
                       r.rtyp_desc 
                FROM   pa_stud_qual_cpnt sqc, 
                       pv_course c, 
                       pa_rqmt_type r 
                WHERE  sqc.cpnt_typ_id = c.cpnt_typ_id 
                       AND sqc.cpnt_id = c.cpnt_id 
                       AND sqc.rev_dte = c.rev_dte 
                       AND sqc.rtyp_id = r.rtyp_id(+) 
                       AND sqc.stud_id = ? 
                       AND ( sqc.compl_dte IS NULL 
                              OR current_date > sqc.exp_dte 
                              OR sqc.retrng_int > 0 ) 
                UNION 
                SELECT sc.stud_id, 
                       sc.cpnt_typ_id, 
                       sc.cpnt_id, 
                       sc.rev_dte, 
                       c.cpnt_title, 
                       sc.req_dte, 
                       r.rtyp_desc 
                FROM   pv_stud_course sc, 
                       pv_course c, 
                       pa_rqmt_type r 
                WHERE  sc.cpnt_typ_id = c.cpnt_typ_id 
                       AND sc.cpnt_id = c.cpnt_id 
                       AND sc.rev_dte = c.rev_dte 
                       AND sc.rtyp_id = r.rtyp_id(+) 
                       AND sc.stud_id = ? 
                       AND sc.compl_dte IS NULL)) 
WHERE  row_num = 1 
ORDER  BY 1, 
          2, 
          3 

SELECT a.col_num, 
       label, 
       user_value, 
       user_desc 
FROM   pa_stud_user a, 
       pa_usrrf_stud b, 
       pa_usrcl_stud c 
WHERE  stud_id = ? 
       AND a.col_num = c.col_num 
       AND a.col_num = b.col_num(+) 
       AND a.user_value = b.user_id(+) 
--AND NOT ($includecustomfieldsfilter$) 
ORDER  BY col_num 

SELECT st.stud_id, 
       st.super, 
       st.fname, 
       st.lname, 
       st.mi, 
       Nvl (userpref.preferred_timezone, pkg_state.get_default_timezone) AS 
       preferred_timezone, 
       st.addr, 
       st.city, 
       st.state, 
       st.postal, 
       st.cntry, 
       st.emp_stat_id, 
       st.emp_typ_id, 
       st.jl_id, 
       st.jp_id, 
       st.dmn_id, 
       st.org_id, 
       st.hire_dte, 
       st.term_dte, 
       st.email_addr, 
       st.resume_locn, 
       st.comments, 
       dmn.dmn_desc, 
       emp_stat.emp_stat_desc, 
       emp_type.emp_typ_desc, 
       jobpos.jp_desc, 
       org.org_desc, 
       jobloc.jl_desc, 
       Decode (st.notactive, 'Y', 'N', 
                             'Y')                                        AS 
       is_active, 
       sup.lname                                                         AS 
       lname_sup, 
       sup.fname                                                         AS 
       fname_sup, 
       sup.mi                                                            AS 
       mi_sup, 
       st.notactive, 
       st.pin 
FROM   pa_student st, 
       pa_domain dmn, 
       pa_emp_stat emp_stat, 
       pa_emp_type emp_type, 
       pa_job_pos jobpos, 
       pa_org org, 
       pa_job_loc jobloc, 
       pa_student sup, 
       pa_user_preference userpref 
WHERE  st.emp_stat_id = emp_stat.emp_stat_id(+) 
       AND st.emp_typ_id = emp_type.emp_typ_id(+) 
       AND st.jl_id = jobloc.jl_id(+) 
       AND st.jp_id = jobpos.jp_id(+) 
       AND st.dmn_id = dmn.dmn_id(+) 
       AND st.org_id = org.org_id(+) 
       AND st.super = sup.stud_id(+) 
       AND st.stud_id = userpref.user_id(+) 
       AND userpref.user_type(+) = 'S' 
/** AND st.stud_id in [UserSearch] */ 
/** AND st.notactive = [NotActive] */ 
/** AND [security:pa_student st]*/ 
ORDER  BY st.stud_id 

SELECT b.cpty_id, 
       ct.cpty_type_desc, 
       c.cpty_desc, 
       b.reqd_lvl, 
       pkg_student.Get_stud_cpty_assessment (a.stud_id, b.cpty_id) AS assess_lvl 
       , 
       MAX.max_assess_dte 
FROM   pa_stud_pprf a, 
       pa_pprf_cpty b, 
       pa_cpty c, 
       pa_cpty_type ct, 
       (SELECT a.cpty_id, 
               Max (a.assess_dte) AS max_assess_dte 
        FROM   pa_stud_cpty_assessment a, 
               pa_pprf_cpty pc 
        WHERE  pc.pprf_id = ? 
               AND pc.cpty_id = a.cpty_id 
               AND a.stud_id = ? 
        GROUP  BY a.cpty_id) MAX 
WHERE  a.stud_id = ? 
       AND a.pprf_id = ? 
       AND a.pprf_id = b.pprf_id 
       AND b.cpty_id = c.cpty_id 
       AND b.cpty_id = MAX.cpty_id(+) 
       AND c.cpty_type = ct.cpty_type_id 

SELECT sp.stud_id, 
       pprf.pprf_id, 
       pprf.pprf_title, 
       sp.assgn_dte, 
       pkg_student.Stud_pprf_complete(sp.stud_id, pprf.pprf_id) AS complete 
FROM   pa_stud_pprf sp, 
       pa_pprf pprf 
WHERE  sp.stud_id = ? 
       AND sp.pprf_id = pprf.pprf_id 

SELECT c.cpnt_typ_id, 
       c.cpnt_id, 
       c.rev_dte, 
       ? AS preferred_timezone, 
       c.cpnt_title, 
       r.rtyp_desc, 
       sqc.req_dte, 
       sqc.exp_dte, 
       c.rev_num 
FROM   pa_stud_qual_cpnt sqc, 
       pv_course c, 
       pa_rqmt_type r 
WHERE  stud_id = ? 
       AND sqc.qual_id = ? 
       AND sqc.cpnt_typ_id = c.cpnt_typ_id 
       AND sqc.cpnt_id = c.cpnt_id 
       AND sqc.rev_dte = c.rev_dte 
       AND sqc.rtyp_id = r.rtyp_id(+) 
ORDER  BY 1, 
          2, 
          3 

SELECT inner.*, 
       Substr(status_remday, 1, 1)                             AS COMPLETED, 
       Substr(status_remday, Instr(status_remday, '|', 1) + 1) AS DAYS_REMAINING 
FROM   (SELECT sq.stud_id, 
               q.qual_id, 
               q.qual_title, 
               ? 
                      AS preferred_timezone, 
               pkg_student.Get_delm_stud_qual_stat_rmday(sq.stud_id, sq.qual_id, 
               sq.qual_id) AS 
               status_remday 
        FROM   pa_stud_qual sq, 
               pa_qual q 
        WHERE  sq.stud_id = ? 
               AND sq.qual_id = q.qual_id) INNER 

SELECT e.cpnt_typ_id, 
       e.cpnt_id, 
       e.rev_dte, 
       e.rev_num, 
       e.compl_dte, 
       e.cmpl_stat_id, 
       cs.cmpl_stat_desc, 
       cp.cpnt_title AS cpnt_desc, 
       e.grade, 
       e.comments 
FROM   pa_cpnt_evthst e, 
       pa_cmpl_stat cs, 
       pv_course cp 
WHERE  e.stud_id = ? 
       AND e.cmpl_stat_id = cs.cmpl_stat_id 
       AND cp.cpnt_typ_id(+) = e.cpnt_typ_id 
       AND cp.cpnt_id(+) = e.cpnt_id 
       AND cp.rev_dte(+) = e.rev_dte 
UNION 
SELECT /*+index(x,pk_xcpnt_evthst)*/ NULL AS cpnt_typ_id, 
                                     NULL AS cpnt_id, 
                                     NULL AS rev_dte, 
                                     NULL AS rev_num, 
                                     x.compl_dte, 
                                     NULL AS cmpl_stat_id, 
                                     NULL AS cmpl_stat_desc, 
                                     x.cpnt_desc, 
                                     x.grade, 
                                     x.comments 
FROM   pa_xcpnt_evthst x 
WHERE  x.stud_id = ? 
ORDER  BY cpnt_typ_id, 
          cpnt_id, 
          rev_dte, 
          compl_dte, 
          cpnt_desc, 
          cmpl_stat_id