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

 

The Class Data report describes a class, including the learning items, curricula, and offerings associated with the class, the users in the class, and other attributes of the class.

 

Report NameClass Data (CSV)
REPORT_NAME_IDClassDataCSV
REPORT_ID348
CATEGORY_IDLearning
GROUP_IDClassData
WORKFLOW_IDRun Class Data Report
TYPEBIRT /PRD
USER_TYPEAdmin
PUBLISHEDYes
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

SQL Query used

WithCustomFields
SELECT * 
FROM   (SELECT class_id  AS master_class_id, 
               class_desc, 
               c.dmn_id, 
               dmn_desc, 
               c.qual_id, 
               qual_title, 
               start_dte AS c_st_dt, 
               end_dte   AS c_end_dt, 
               max_size 
        FROM   pa_class c, 
               pa_domain d, 
               pa_qual q 
        WHERE  c.dmn_id = d.dmn_id(+) 
               AND c.qual_id = q.qual_id(+) 
       /** AND c.CLASS_ID IN [ClassIDSearch]         and [security:pa_class c]*/ 
       ) master, 
       (SELECT NULL AS users_class_id, 
               NULL AS stud_id, 
               NULL AS lname, 
               NULL AS fname, 
               NULL AS mi, 
               NULL AS class_stat_id, 
               NULL AS class_stat_desc, 
               NULL AS comments 
        FROM   dual) users, 
       (SELECT NULL AS schedules_class_id, 
               NULL AS schd_id, 
               NULL AS cpnt_typ_id, 
               NULL AS act_cpnt_id, 
               NULL AS rev_dte, 
               NULL AS rev_num, 
               NULL AS start_dte, 
               NULL AS end_dte, 
               NULL AS auto_enroll, 
               NULL AS timezone_id, 
               NULL AS display_in_schd_tz 
        FROM   dual) schedules, 
       (SELECT t1.*, 
               cu.user_value, 
               cr.user_desc 
        FROM   pa_class_user cu, 
               (SELECT c.class_id AS custom_class_id, 
                       cum.col_num, 
                       cum.label 
                FROM   pa_class c, 
                       pa_usrcl_class cum) t1, 
               pa_usrrf_class cr 
        WHERE  t1.col_num = cu.col_num (+) 
               AND t1.custom_class_id = cu.class_id (+) 
               AND cu.user_value = cr.user_id(+)) custom 
WHERE  custom.custom_class_id(+) = master.master_class_id 
UNION 
SELECT * 
FROM   (SELECT class_id  AS master_class_id, 
               class_desc, 
               c.dmn_id, 
               dmn_desc, 
               c.qual_id, 
               qual_title, 
               start_dte AS c_st_dt, 
               end_dte   AS c_end_dt, 
               max_size 
        FROM   pa_class c, 
               pa_domain d, 
               pa_qual q 
        WHERE  c.dmn_id = d.dmn_id(+) 
               AND c.qual_id = q.qual_id(+) 
       /** AND c.CLASS_ID IN [ClassIDSearch]         and [security:pa_class c]*/ 
       ) master, 
       (SELECT cs.class_id AS users_class_id, 
               cs.stud_id, 
               lname, 
               fname, 
               mi, 
               cs.class_stat_id, 
               class_stat_desc, 
               cs.comments 
        FROM   pa_class_student cs, 
               pa_student s, 
               pa_class_stat stat 
        WHERE  cs.stud_id = s.stud_id 
               AND cs.class_stat_id = stat.class_stat_id(+)) users, 
       (SELECT NULL AS schedules_class_id, 
               NULL AS schd_id, 
               NULL AS cpnt_typ_id, 
               NULL AS act_cpnt_id, 
               NULL AS rev_dte, 
               NULL AS rev_num, 
               NULL AS start_dte, 
               NULL AS end_dte, 
               NULL AS auto_enroll, 
               NULL AS timezone_id, 
               NULL AS display_in_schd_tz 
        FROM   dual) schedules, 
       (SELECT t1.*, 
               NULL AS user_value, 
               NULL AS user_desc 
        FROM   dual, 
               (SELECT NULL AS custom_class_id, 
                       NULL AS col_num, 
                       NULL AS label 
                FROM   dual) t1) custom 
WHERE  users.users_class_id = master.master_class_id 
UNION 
SELECT * 
FROM   (SELECT class_id  AS master_class_id, 
               class_desc, 
               c.dmn_id, 
               dmn_desc, 
               c.qual_id, 
               qual_title, 
               start_dte AS c_st_dt, 
               end_dte   AS c_end_dt, 
               max_size 
        FROM   pa_class c, 
               pa_domain d, 
               pa_qual q 
        WHERE  c.dmn_id = d.dmn_id(+) 
               AND c.qual_id = q.qual_id(+) 
       /** AND c.CLASS_ID IN [ClassIDSearch]         and [security:pa_class c]*/ 
       ) master, 
       (SELECT NULL AS users_class_id, 
               NULL AS stud_id, 
               NULL AS lname, 
               NULL AS fname, 
               NULL AS mi, 
               NULL AS class_stat_id, 
               NULL AS class_stat_desc, 
               NULL AS comments 
        FROM   dual) users, 
       (SELECT cs.class_id AS schedules_class_id, 
               cs.schd_id  AS schd_id, 
               s.cpnt_typ_id, 
               s.act_cpnt_id, 
               s.rev_dte, 
               cp.rev_num, 
               sr.start_dte, 
               sr.end_dte, 
               auto_enroll, 
               s.timezone_id, 
               s.display_in_schd_tz 
        FROM   pa_class_sched cs, 
               ps_schd_resources sr, 
               pa_sched s, 
               pv_course cp 
        WHERE  cs.schd_id = s.schd_id 
               AND cs.schd_id = sr.schd_id 
               AND s.cpnt_typ_id = cp.cpnt_typ_id 
               AND s.act_cpnt_id = cp.cpnt_id 
               AND s.rev_dte = cp.rev_dte) schedules, 
       (SELECT t1.*, 
               NULL AS user_value, 
               NULL AS user_desc 
        FROM   dual, 
               (SELECT NULL AS custom_class_id, 
                       NULL AS col_num, 
                       NULL AS label 
                FROM   dual) t1) custom 
WHERE  schedules.schedules_class_id = master.master_class_id 
WithoutCustomFields
SELECT * 
FROM   (SELECT * 
        FROM   (SELECT class_id  AS master_class_id, 
                       class_desc, 
                       c.dmn_id, 
                       dmn_desc, 
                       c.qual_id, 
                       qual_title, 
                       start_dte AS c_st_dt, 
                       end_dte   AS c_end_dt, 
                       max_size 
                FROM   pa_class c, 
                       pa_domain d, 
                       pa_qual q 
                WHERE  c.dmn_id = d.dmn_id(+) 
                       AND c.qual_id = q.qual_id(+) 
               /** AND c.CLASS_ID IN [ClassIDSearch] 
               and [security:pa_class c]*/ 
               ) master, 
               (SELECT cs.class_id AS users_class_id, 
                       cs.stud_id, 
                       lname, 
                       fname, 
                       mi, 
                       cs.class_stat_id, 
                       class_stat_desc, 
                       cs.comments 
                FROM   pa_class_student cs, 
                       pa_student s, 
                       pa_class_stat stat 
                WHERE  cs.stud_id = s.stud_id 
                       AND cs.class_stat_id = stat.class_stat_id(+)) users, 
               (SELECT NULL AS schedules_class_id, 
                       NULL AS schd_id, 
                       NULL AS cpnt_typ_id, 
                       NULL AS act_cpnt_id, 
                       NULL AS rev_dte, 
                       NULL AS rev_num, 
                       NULL AS start_dte, 
                       NULL AS end_dte, 
                       NULL AS auto_enroll, 
                       NULL AS timezone_id, 
                       NULL AS display_in_schd_tz 
                FROM   dual) schedules, 
               (SELECT t1.*, 
                       NULL AS user_value, 
                       NULL AS user_desc 
                FROM   dual, 
                       (SELECT NULL AS custom_class_id, 
                               NULL AS col_num, 
                               NULL AS label 
                        FROM   dual) t1) custom 
        WHERE  users.users_class_id = master.master_class_id 
        UNION 
        SELECT * 
        FROM   (SELECT class_id  AS master_class_id, 
                       class_desc, 
                       c.dmn_id, 
                       dmn_desc, 
                       c.qual_id, 
                       qual_title, 
                       start_dte AS c_st_dt, 
                       end_dte   AS c_end_dt, 
                       max_size 
                FROM   pa_class c, 
                       pa_domain d, 
                       pa_qual q 
                WHERE  c.dmn_id = d.dmn_id(+) 
                       AND c.qual_id = q.qual_id(+) 
               /** AND c.CLASS_ID IN [ClassIDSearch] 
               and [security:pa_class c]*/ 
               ) master, 
               (SELECT NULL AS users_class_id, 
                       NULL AS stud_id, 
                       NULL AS lname, 
                       NULL AS fname, 
                       NULL AS mi, 
                       NULL AS class_stat_id, 
                       NULL AS class_stat_desc, 
                       NULL AS comments 
                FROM   dual) users, 
               (SELECT cs.class_id         AS schedules_class_id, 
                       To_char(cs.schd_id) AS schd_id, 
                       s.cpnt_typ_id, 
                       s.act_cpnt_id, 
                       s.rev_dte, 
                       cp.rev_num, 
                       sr.start_dte, 
                       sr.end_dte, 
                       auto_enroll, 
                       s.timezone_id, 
                       s.display_in_schd_tz 
                FROM   pa_class_sched cs, 
                       ps_schd_resources sr, 
                       pa_sched s, 
                       pv_course cp 
                WHERE  cs.schd_id = s.schd_id 
                       AND cs.schd_id = sr.schd_id 
                       AND s.cpnt_typ_id = cp.cpnt_typ_id 
                       AND s.act_cpnt_id = cp.cpnt_id 
                       AND s.rev_dte = cp.rev_dte) schedules, 
               (SELECT t1.*, 
                       NULL AS user_value, 
                       NULL AS user_desc 
                FROM   dual, 
                       (SELECT NULL AS custom_class_id, 
                               NULL AS col_num, 
                               NULL AS label 
                        FROM   dual) t1) custom 
        WHERE  schedules.schedules_class_id = master.master_class_id) 
       classwithuser_so 
UNION 
SELECT pc.class_id AS master_class_id, 
       class_desc, 
       pc.dmn_id, 
       dmn_desc, 
       pc.qual_id, 
       qual_title, 
       start_dte   AS c_st_dt, 
       end_dte     AS c_end_dt, 
       max_size, 
       NULL        AS class_id, 
       NULL        AS stud_id, 
       NULL        AS lname, 
       NULL        AS fname, 
       NULL        AS mi, 
       NULL        AS class_stat_id, 
       NULL        AS class_stat_desc, 
       NULL        AS comments, 
       NULL        AS class_id, 
       NULL        AS schd_id, 
       NULL        AS cpnt_typ_id, 
       NULL        AS act_cpnt_id, 
       NULL        AS rev_dte, 
       NULL        AS rev_num, 
       NULL        AS start_dte, 
       NULL        AS end_dte, 
       NULL        AS auto_enroll, 
       NULL        AS timezone_id, 
       NULL        AS display_in_schd_tz, 
       NULL        AS custom_class_id, 
       NULL        AS col_num, 
       NULL        AS label, 
       NULL        AS user_value, 
       NULL        AS user_desc 
FROM   pa_class pc, 
       pa_domain d, 
       pa_qual q 
WHERE  pc.dmn_id = d.dmn_id(+) 
       AND pc.qual_id = q.qual_id(+) 
       /** AND pc.CLASS_ID IN [ClassIDSearch] 
       and [security:pa_class pc]*/ 
       AND NOT EXISTS (SELECT * 
                       FROM   (SELECT * 
                               FROM   (SELECT c.class_id AS master_class_id, 
                                              class_desc, 
                                              c.dmn_id, 
                                              dmn_desc, 
                                              c.qual_id, 
                                              qual_title, 
                                              start_dte  AS c_st_dt, 
                                              end_dte    AS c_end_dt, 
                                              max_size 
                                       FROM   pa_class c, 
                                              pa_domain d, 
                                              pa_qual q 
                                       WHERE  c.dmn_id = d.dmn_id(+) 
                                              AND c.qual_id = q.qual_id(+) 
                                      /** AND c.CLASS_ID IN [ClassIDSearch] 
                                      and [security:pa_class c]*/ 
                                      ) master, 
                                      (SELECT cs.class_id AS users_class_id, 
                                              cs.stud_id, 
                                              lname, 
                                              fname, 
                                              mi, 
                                              cs.class_stat_id, 
                                              class_stat_desc, 
                                              cs.comments 
                                       FROM   pa_class_student cs, 
                                              pa_student s, 
                                              pa_class_stat stat 
                                       WHERE  cs.stud_id = s.stud_id 
                                              AND cs.class_stat_id = 
                                                  stat.class_stat_id(+)) 
                                      users, 
                                      (SELECT NULL AS schedules_class_id, 
                                              NULL AS schd_id, 
                                              NULL AS cpnt_typ_id, 
                                              NULL AS act_cpnt_id, 
                                              NULL AS rev_dte, 
                                              NULL AS rev_num, 
                                              NULL AS start_dte, 
                                              NULL AS end_dte, 
                                              NULL AS auto_enroll, 
                                              NULL AS timezone_id, 
                                              NULL AS display_in_schd_tz 
                                       FROM   dual) schedules, 
                                      (SELECT t1.*, 
                                              NULL AS user_value, 
                                              NULL AS user_desc 
                                       FROM   dual, 
                                              (SELECT NULL AS custom_class_id, 
                                                      NULL AS col_num, 
                                                      NULL AS label 
                                               FROM   dual) t1) custom 
                               WHERE 
                      users.users_class_id = master.master_class_id 
                               UNION 
               SELECT * 
               FROM   (SELECT c.class_id AS master_class_id, 
                              class_desc, 
                              c.dmn_id, 
                              dmn_desc, 
                              c.qual_id, 
                              qual_title, 
                              start_dte  AS c_st_dt, 
                              end_dte    AS c_end_dt, 
                              max_size 
                       FROM   pa_class c, 
                              pa_domain d, 
                              pa_qual q 
                       WHERE  c.dmn_id = d.dmn_id(+) 
                              AND c.qual_id = q.qual_id(+) 
                      /** AND c.CLASS_ID IN [ClassIDSearch] 
                      and [security:pa_class c]*/ 
                      ) master, 
                      (SELECT NULL AS users_class_id, 
                              NULL AS stud_id, 
                              NULL AS lname, 
                              NULL AS fname, 
                              NULL AS mi, 
                              NULL AS class_stat_id, 
                              NULL AS class_stat_desc, 
                              NULL AS comments 
                       FROM   dual) users, 
                      (SELECT cs.class_id         AS schedules_class_id 
                              , 
                              To_char(cs.schd_id) AS schd_id, 
                              s.cpnt_typ_id, 
                              s.act_cpnt_id, 
                              s.rev_dte, 
                              cp.rev_num, 
                              sr.start_dte, 
                              sr.end_dte, 
                              auto_enroll, 
                              s.timezone_id, 
                              s.display_in_schd_tz 
                       FROM   pa_class_sched cs, 
                              ps_schd_resources sr, 
                              pa_sched s, 
                              pv_course cp 
                       WHERE  cs.schd_id = s.schd_id 
                              AND cs.schd_id = sr.schd_id 
                              AND s.cpnt_typ_id = cp.cpnt_typ_id 
                              AND s.act_cpnt_id = cp.cpnt_id 
                              AND s.rev_dte = cp.rev_dte) schedules, 
                      (SELECT t1.*, 
                              NULL AS user_value, 
                              NULL AS user_desc 
                       FROM   dual, 
                              (SELECT NULL AS custom_class_id, 
                                      NULL AS col_num, 
                                      NULL AS label 
                               FROM   dual) t1) custom 
               WHERE  schedules.schedules_class_id = master.master_class_id 
                              ) 
                              class_with_users_so 
                       WHERE  pc.class_id = class_with_users_so.master_class_id) 

 

 

 

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