Page tree
Skip to end of metadata
Go to start of metadata

The user Curriculum Item Status report returns, for each user, the   curriculum assigned to each user and their curriculum status, the items   assigned to each curriculum, and the completion date, completion status, and   required date for each item.

 

Report NameCurriculum Item Status
REPORT_NAME_IDReportUserCurriculumItemStatus
REPORT_ID135
CATEGORY_IDUserManagement
GROUP_IDUserCurriculumItemStatus
WORKFLOW_IDRun Student Qualification Component Status Report
TYPEBIRT /PRD
USER_TYPEAdmin
PUBLISHEDYes
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

SQL Query used 

stud-allquals
SELECT s.stud_id, 
       s.fname, 
       s.lname, 
       s.mi, 
       Nvl(userPref.preferred_timezone, pkg_state.get_default_timezone) AS 
       preferred_timezone 
FROM   pa_student s, 
       pa_user_preference userPref, 
       (SELECT sq.stud_id 
        FROM   pa_stud_qual sq 
        WHERE  1 = 1 
       /** and sq.stud_id in [UserSearch] */ 
       /** and sq.qual_id in [CurriculumSearch] */ 
        /** and PKG_STUDENT.get_stud_qual_status(sq.stud_id, sq.qual_id, sq.qual_id) = [CurriculaStatus] */
        UNION 
        SELECT sq.stud_id 
        FROM   pa_stud_qual_relation sq 
        WHERE  1 = 1 
       /** and sq.stud_id in [UserSearch] */ 
       /** and sq.qual_id in [CurriculumSearch] */ 
       /** and PKG_STUDENT.get_stud_qual_status(sq.stud_id, sq.qual_id, sq.qual_id_root) = [CurriculaStatus] */
       ) a 
WHERE  s.stud_id = a.stud_id 
       AND s.stud_id = userPref.user_id(+) 
       AND userPref.user_type(+) = 'S' 
/** and s.notactive = [UserStatus] */ 
/** and [security:pa_student s] */ 
ORDER  BY s.stud_id 
studqual-allquals
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, 
               sq.qual_id, 
               sq.assgn_dte, 
               q.qual_title, 
               sq.qual_id_root, 
               ?                AS preferred_timezone, 
               pkg_student.Get_delm_stud_qual_stat_rmday (sq.stud_id, 
               sq.qual_id, 
               sq.qual_id_root) AS status_remday 
        FROM   (SELECT sq.stud_id, 
                       sq.qual_id, 
                       sq.assgn_dte, 
                       sq.qual_id_root, 
                       Row_number() 
                         over ( 
                           PARTITION BY sq.stud_id, sq.qual_id 
                           ORDER BY sq.assgn_dte) rnum 
                FROM   (SELECT sq.stud_id, 
                               sq.qual_id, 
                               sq.assgn_dte, 
                               sq.qual_id_root 
                        FROM   pa_stud_qual_relation sq 
                        WHERE  sq.stud_id = ? 
                        /** and sq.qual_id in [CurriculumSearch]*/ 
                        UNION ALL 
                        SELECT sq.stud_id, 
                               sq.qual_id, 
                               sq.assgn_dte, 
                               sq.qual_id_root 
                        FROM   pa_stud_qual_relation sq, 
                               pa_qual_relation qr 
                        WHERE  sq.stud_id = ? 
                               AND qr.qual_id_child = sq.qual_id 
                               AND qr.qual_id_parent IN (SELECT psqr.qual_id 
                                                         FROM 
                                   pa_stud_qual_relation psqr 
                                                         WHERE 
                                   sq.stud_id = psqr.stud_id 
                                   /** and qual_id in [CurriculumSearch]*/)) 
                       sq) sq, 
               pa_qual q 
        WHERE  sq.rnum = 1 
               AND sq.qual_id = q.qual_id 
        /** and PKG_STUDENT.get_stud_qual_status(sq.stud_id, sq.qual_id, sq.qual_id_root) = [CurriculaStatus] */
        ORDER  BY sq.qual_id)INNER 
ORDER  BY stud_id 
studqualcpnt
SELECT DISTINCT sqc.cpnt_typ_id, 
                sqc.cpnt_id, 
                sqc.rev_dte, 
                Nvl(userPref.preferred_timezone, pkg_state.get_default_timezone) 
                AS 
                preferred_timezone, 
                cpt.cpnt_title, 
                sqc.compl_dte, 
                sqc.cmpl_stat_id, 
                cs.cmpl_stat_desc, 
                sqc.req_dte 
FROM   pa_stud_qual_cpnt sqc, 
       pv_course cpt, 
       pa_cmpl_stat cs, 
       pa_user_preference userPref 
WHERE  sqc.stud_id = ? 
       AND sqc.qual_id = ? 
       AND sqc.qual_id_root = ? 
       AND sqc.cpnt_typ_id = cpt.cpnt_typ_id (+) 
       AND sqc.cpnt_id = cpt.cpnt_id (+) 
       AND sqc.rev_dte = cpt.rev_dte (+) 
       AND sqc.cmpl_stat_id = cs.cmpl_stat_id (+) 
       AND sqc.stud_id = userPref.user_id(+) 
       AND userPref.user_type(+) = 'S' 
ORDER  BY 1, 
          2, 
          3 
studqual-rootqualonly
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, 
               ? 
                      AS preferred_timezone, 
               q.qual_id, 
               q.qual_id 
                      AS qual_id_root, 
               q.qual_title, 
               sq.assgn_dte 
                      AS assgn_dte, 
               pkg_student.Get_delm_stud_qual_stat_rmday (sq.stud_id, q.qual_id, 
               q.qual_id) AS 
               status_remday 
        FROM   pa_stud_qual sq, 
               pa_qual q 
        WHERE  sq.qual_id = q.qual_id 
               AND sq.stud_id = ? 
       /** and q.qual_id in [CurriculumSearch]*/ 
        /** and PKG_STUDENT.get_stud_qual_status(sq.stud_id, q.qual_id, q.qual_id) = [CurriculaStatus] */
        ORDER  BY 1) INNER 
ORDER  BY stud_id 
stud-rootqualonly
SELECT DISTINCT s.stud_id, 
                s.fname, 
                s.lname, 
                s.mi, 
                Nvl(userPref.preferred_timezone, pkg_state.get_default_timezone) 
                AS 
                preferred_timezone 
FROM   pa_student s, 
       pa_stud_qual sq, 
       pa_user_preference userPref 
WHERE  s.stud_id = sq.stud_id 
       AND s.stud_id = userPref.user_id(+) 
       AND userPref.user_type(+) = 'S' 
/** and s.stud_id in [UserSearch] */ 
/** and s.notactive in [UserStatus]*/ 
/** and sq.qual_id in [CurriculumSearch] */ 
/** and PKG_STUDENT.get_stud_qual_status(sq.stud_id, sq.qual_id, sq.qual_id) = [CurriculaStatus] */
/** and [security:pa_student s] */ 
ORDER  BY s.stud_id 

 

 

Link: http://wiki.scn.sap.com/wiki/x/VIHyFw

 

  • No labels