Report CurriculumData

The Curriculum Data Report returns curricula attributes, including the sub curricula in the curriculum.

Report Name	Curriculum Data
REPORT_NAME_ID	CurriculumData
REPORT_ID	166
CATEGORY_ID	Learning
GROUP_ID	CurriculumData
WORKFLOW_ID	Run Qualification Data Report
TYPE	BIRT /PRD
USER_TYPE	Admin
PUBLISHED	Yes
SHOW_IN_MENU	Yes
DMN_ID	PUBLIC

components

SELECT c.cpnt_typ_id, 
       c.cpnt_id, 
       c.rev_dte, 
       c.cpnt_title, 
       r.rtyp_desc, 
       ip.label_id AS init_period_type_id, 
       qc.init_number, 
       ib.label_id AS init_basis_type_id, 
       rp.label_id AS retrng_period_type_id, 
       qc.retrng_number, 
       rb.label_id AS retrng_basis_type_id, 
       qc.stat_effect_dte, 
       c.rev_num 
FROM   pa_qual_cpnt qc, 
       pv_course c, 
       pa_rqmt_type r, 
       pa_qual q, 
       ps_period_type ip, 
       ps_basis_type ib, 
       ps_period_type rp, 
       ps_basis_type rb 
WHERE  qc.qual_id = ? 
       AND qc.cpnt_typ_id = c.cpnt_typ_id 
       AND qc.cpnt_id = c.cpnt_id 
       AND qc.rev_dte = c.rev_dte 
       AND qc.rtyp_id = r.rtyp_id(+) 
       AND q.qual_id = qc.qual_id 
       AND ip.period_type_id(+) = qc.init_period_type_id 
       AND ib.basis_type_id(+) = qc.init_basis_type_id 
       AND rp.period_type_id(+) = qc.retrng_period_type_id 
       AND rb.basis_type_id(+) = qc.retrng_basis_type_id 
ORDER  BY 1, 
          2, 
          3

documents

SELECT doc.doc_id, 
       doc.doc_title, 
       qd.review_flg, 
       q.qual_id, 
       q.qual_title 
FROM   pa_qual_doc qd, 
       pa_doc doc, 
       pa_qual q 
WHERE  qd.qual_id = ? 
       AND qd.doc_id = doc.doc_id 
       AND q.qual_id = qd.qual_id 
ORDER  BY 1

groupRequirements

SELECT qual_rec.*, 
       req.req_type_id, 
       req.req_id, 
       req.req_desc, 
       req.hour_type_id, 
       req.num_hours, 
       req.num_items, 
       ht.hour_type_desc, 
       ht.label_id  AS hour_type_label_id, 
       rt.req_type_desc, 
       rt.label_id  AS req_type_label_id, 
       ipt.label_id AS init_period_type_label, 
       ibt.label_id AS init_basis_type_label, 
       rpt.label_id AS retrng_period_type_label, 
       rbt.label_id AS retrng_basis_type_label 
FROM   (SELECT a.qual_id, 
               NULL AS req_seq, 
               a.req_group_id, 
               a.req_group_desc, 
               a.init_period_type_id, 
               a.init_number, 
               a.init_basis_type_id, 
               a.retrng_period_type_id, 
               a.retrng_number, 
               a.retrng_basis_type_id 
        FROM   pa_qual_req_group a 
        WHERE  qual_id = ?) qual_rec, 
       pa_requirement req, 
       ps_hour_type ht, 
       ps_qual_requirement_type rt, 
       ps_period_type rpt, 
       ps_basis_type rbt, 
       ps_period_type ipt, 
       ps_basis_type ibt 
WHERE  qual_rec.req_seq = req.req_seq(+) 
       AND req.hour_type_id = ht.hour_type_id(+) 
       AND req.req_type_id = rt.req_type_id(+) 
       AND qual_rec.retrng_basis_type_id = rbt.basis_type_id(+) 
       AND qual_rec.retrng_period_type_id = rpt.period_type_id(+) 
       AND qual_rec.init_basis_type_id = ibt.basis_type_id(+) 
       AND qual_rec.init_period_type_id = ipt.period_type_id(+)

jobpositions

SELECT jp.jp_id, 
       jp.jp_desc 
FROM   pa_qual_jp qj, 
       pa_job_pos jp 
WHERE  qj.qual_id = ? 
       AND qj.jp_id = jp.jp_id 
ORDER  BY 1

qualification

SELECT q.qual_id, 
       q.qual_title, 
       q.dmn_id, 
       d.dmn_desc, 
       Decode (q.notactive, 'Y', 'N', 
                            'Y') active, 
       q.qual_desc, 
       q.create_dte, 
       q.force_incomplete 
FROM   pa_qual q, 
       pa_domain d 
WHERE  q.dmn_id = d.dmn_id(+) 
/** AND qual_id in [CurriculumSearch] */ 
/** AND [sub-select:RequirementTypeSearch "q.qual_id in               (select qual_id from pa_qual_req qr, pa_requirement r               where req_type_id in [RequirementTypeSearch]                    and qr.req_seq = r.req_seq)"]*/ 
/** AND notactive = [NotActive] */ 
/** AND [security:PA_QUAL q]*/ 
ORDER  BY qual_title

requirements

SELECT qual_rec.*, 
       req.req_type_id, 
       req.req_id, 
       req.req_desc, 
       req.hour_type_id, 
       req.num_hours, 
       req.num_items, 
       ht.hour_type_desc, 
       ht.label_id  AS hour_type_label_id, 
       rt.req_type_desc, 
       rt.label_id  AS req_type_label_id, 
       ipt.label_id AS init_period_type_label, 
       ibt.label_id AS init_basis_type_label, 
       rpt.label_id AS retrng_period_type_label, 
       rbt.label_id AS retrng_basis_type_label 
FROM   (SELECT a.qual_id, 
               a.req_seq, 
               a.req_group_id, 
               NULL AS req_group_desc, 
               a.init_period_type_id, 
               a.init_number, 
               a.init_basis_type_id, 
               a.retrng_period_type_id, 
               a.retrng_number, 
               a.retrng_basis_type_id 
        FROM   pa_qual_req a 
        WHERE  qual_id = ? 
               AND req_group_id IS NULL) qual_rec, 
       pa_requirement req, 
       ps_hour_type ht, 
       ps_qual_requirement_type rt, 
       ps_period_type rpt, 
       ps_basis_type rbt, 
       ps_period_type ipt, 
       ps_basis_type ibt 
WHERE  qual_rec.req_seq = req.req_seq(+) 
       AND req.hour_type_id = ht.hour_type_id(+) 
       AND req.req_type_id = rt.req_type_id(+) 
       AND qual_rec.retrng_basis_type_id = rbt.basis_type_id(+) 
       AND qual_rec.retrng_period_type_id = rpt.period_type_id(+) 
       AND qual_rec.init_basis_type_id = ibt.basis_type_id(+) 
       AND qual_rec.init_period_type_id = ipt.period_type_id(+)

subqualifications

SELECT a.qual_id, 
       a.qual_id_sub 
FROM   pa_qual_subqual a 
CONNECT BY PRIOR qual_id_sub = qual_id 
START WITH qual_id = ?

subqual-qualification

SELECT q.qual_id, 
       q.qual_title, 
       q.dmn_id, 
       d.dmn_desc, 
       ?                         AS root_qual_id, 
       Decode (q.notactive, 'Y', 'N', 
                            'Y') active, 
       q.qual_desc, 
       q.create_dte, 
       q.force_incomplete 
FROM   pa_qual q, 
       pa_domain d 
WHERE  q.dmn_id = d.dmn_id(+) 
       AND q.qual_id = ?

subRequirements

SELECT req.req_type_id, 
       req.req_id, 
       req.req_desc, 
       req.hour_type_id, 
       req.num_hours, 
       req.num_items, 
       ht.hour_type_desc, 
       ht.label_id AS hour_type_label_id, 
       rt.req_type_desc, 
       rt.label_id AS req_type_label_id, 
       NULL        AS req_group_id, 
       NULL        AS req_group_desc 
FROM   pa_qual_req qual_rec, 
       pa_requirement req, 
       ps_hour_type ht, 
       ps_qual_requirement_type rt 
WHERE  qual_rec.req_seq = req.req_seq(+) 
       AND req.hour_type_id = ht.hour_type_id(+) 
       AND req.req_type_id = rt.req_type_id(+) 
       AND qual_rec.req_group_id = ?

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

Page tree

Report CurriculumData