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

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

 

Report NameCurriculum Data (CSV)
REPORT_NAME_IDCurriculumDataCSV
REPORT_ID335
CATEGORY_IDLearning
GROUP_IDCurriculumData
WORKFLOW_IDRun Qualification Data Report
TYPEBIRT /PRD
USER_TYPEAdmin
PUBLISHEDYes
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

As this CSV have too many columns, the screen-shot of the report is displayed in 4 parts.

CurriculumDataCSV1

SQL Query used 

details
--modified 
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, 
       ------- 
       c.cpnt_typ_id, 
       c.cpnt_id, 
       c.rev_dte, 
       c.cpnt_title, 
       r.rtyp_desc               AS c_rtyp_desc, 
       ip.label_id               AS c_init_period_type_id, 
       qc.init_number            AS c_init_number, 
       ib.label_id               AS c_init_basis_type_id, 
       rp.label_id               AS c_retrng_period_type_id, 
       qc.retrng_number          AS c_retrng_number, 
       rb.label_id               AS c_retrng_basis_type_id, 
       qc.stat_effect_dte, 
       c.rev_num, 
       -------- 
       jp.jp_id, 
       jp.jp_desc, 
       ------ 
       r_qual_rec.r_req_seq, 
       r_qual_rec.r_req_group_id, 
       r_qual_rec.r_req_group_desc, 
       r_qual_rec.r_init_period_type_id, 
       r_qual_rec.r_init_number, 
       r_qual_rec.r_init_basis_type_id, 
       r_qual_rec.r_retrng_period_type_id, 
       r_qual_rec.r_retrng_number, 
       r_qual_rec.r_retrng_basis_type_id, 
       r_req.req_type_id         AS r_req_type_id, 
       r_req.req_id              AS r_req_id, 
       r_req.req_desc            AS r_req_desc, 
       r_req.hour_type_id        AS r_hour_type_id, 
       r_req.num_hours           AS r_num_hours, 
       r_req.num_items           AS r_num_items, 
       r_ht.hour_type_desc       AS r_hour_type_desc, 
       r_ht.label_id             AS r_hour_type_label_id, 
       r_rt.req_type_desc        AS r_req_type_desc, 
       r_rt.label_id             AS r_req_type_label_id, 
       r_ipt.label_id            AS r_init_period_type_label, 
       r_ibt.label_id            AS r_init_basis_type_label, 
       r_rpt.label_id            AS r_retrng_period_type_label, 
       r_rbt.label_id            AS r_retrng_basis_type_label, 
       ------- 
       gr_qual_rec.gr_req_seq, 
       gr_qual_rec.gr_req_group_id, 
       gr_qual_rec.gr_req_group_desc, 
       gr_qual_rec.gr_init_period_type_id, 
       gr_qual_rec.gr_init_number, 
       gr_qual_rec.gr_init_basis_type_id, 
       gr_qual_rec.gr_retrng_period_type_id, 
       gr_qual_rec.gr_retrng_number, 
       gr_qual_rec.gr_retrng_basis_type_id, 
       gr_req.req_type_id        AS gr_req_type_id, 
       gr_req.req_id             AS gr_req_id, 
       gr_req.req_desc           AS gr_req_desc, 
       gr_req.hour_type_id       AS gr_hour_type_id, 
       gr_req.num_hours          AS gr_num_hours, 
       gr_req.num_items          AS gr_num_items, 
       gr_ht.hour_type_desc      AS gr_hour_type_desc, 
       gr_ht.label_id            AS gr_hour_type_label_id, 
       gr_rt.req_type_desc       AS gr_req_type_desc, 
       gr_rt.label_id            AS gr_req_type_label_id, 
       gr_ipt.label_id           AS gr_init_period_type_label, 
       gr_ibt.label_id           AS gr_init_basis_type_label, 
       gr_rpt.label_id           AS gr_retrng_period_type_label, 
       gr_rbt.label_id           AS gr_retrng_basis_type_label, 
       --------- 
       sr_req.req_type_id        AS sr_req_type_id, 
       sr_req.req_id             AS sr_req_id, 
       sr_req.req_desc           AS sr_req_desc, 
       sr_req.hour_type_id       AS sr_hour_type_id, 
       sr_req.num_hours          AS sr_num_hours, 
       sr_req.num_items          AS sr_num_items, 
       sr_ht.hour_type_desc      AS sr_hour_type_desc, 
       sr_ht.label_id            AS sr_hour_type_label_id, 
       sr_rt.req_type_desc       AS sr_req_type_desc, 
       sr_rt.label_id            AS sr_req_type_label_id, 
       NULL                      AS sr_req_group_id, 
       NULL                      AS sr_req_group_desc, 
       ------- 
       doc.doc_id                AS d_doc_id, 
       doc.doc_title             AS doc_title, 
       qd.review_flg             AS review_flag, 
       q.qual_id                 AS d_qual_id, 
       q.qual_title              AS d_qual_title 
----- 
FROM   pa_qual q, 
       pa_domain d, 
       --------------- 
       pa_qual_cpnt qc, 
       pv_course c, 
       pa_rqmt_type r, 
       ps_period_type ip, 
       ps_basis_type ib, 
       ps_period_type rp, 
       ps_basis_type rb, 
       ----------- 
       pa_qual_jp qj, 
       pa_job_pos jp, 
       ---------- 
       (SELECT a.qual_id, 
               a.req_seq               AS r_req_seq, 
               a.req_group_id          AS r_req_group_id, 
               NULL                    AS r_req_group_desc, 
               a.init_period_type_id   AS r_init_period_type_id, 
               a.init_number           AS r_init_number, 
               a.init_basis_type_id    AS r_init_basis_type_id, 
               a.retrng_period_type_id AS r_retrng_period_type_id, 
               a.retrng_number         AS r_retrng_number, 
               a.retrng_basis_type_id  AS r_retrng_basis_type_id 
        FROM   pa_qual_req a 
        WHERE  req_group_id IS NULL) r_qual_rec, 
       pa_requirement r_req, 
       ps_hour_type r_ht, 
       ps_qual_requirement_type r_rt, 
       ps_period_type r_rpt, 
       ps_basis_type r_rbt, 
       ps_period_type r_ipt, 
       ps_basis_type r_ibt, 
       -------- 
       (SELECT a.qual_id, 
               NULL                    AS gr_req_seq, 
               a.req_group_id          AS gr_req_group_id, 
               a.req_group_desc        AS gr_req_group_desc, 
               a.init_period_type_id   AS gr_init_period_type_id, 
               a.init_number           AS gr_init_number, 
               a.init_basis_type_id    AS gr_init_basis_type_id, 
               a.retrng_period_type_id AS gr_retrng_period_type_id, 
               a.retrng_number         AS gr_retrng_number, 
               a.retrng_basis_type_id  AS gr_retrng_basis_type_id 
        FROM   pa_qual_req_group a) gr_qual_rec, 
       pa_requirement gr_req, 
       ps_hour_type gr_ht, 
       ps_qual_requirement_type gr_rt, 
       ps_period_type gr_rpt, 
       ps_basis_type gr_rbt, 
       ps_period_type gr_ipt, 
       ps_basis_type gr_ibt, 
       --------- 
       pa_qual_req sr_qual_rec, 
       pa_requirement sr_req, 
       ps_hour_type sr_ht, 
       ps_qual_requirement_type sr_rt, 
       ------- 
       pa_qual_doc qd, 
       pa_doc doc 
WHERE  q.dmn_id = d.dmn_id(+) 
       /** AND q.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 [security:PA_QUAL q]*/ 
       /** AND q.notactive = [NotActive] */ 
       ------- 
       AND qc.qual_id(+) = q.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 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 
       --------- 
       AND qj.qual_id(+) = q.qual_id 
       AND qj.jp_id = jp.jp_id(+) 
       -------- 
       AND r_qual_rec.qual_id(+) = q.qual_id 
       AND r_qual_rec.r_req_seq = r_req.req_seq(+) 
       AND r_req.hour_type_id = r_ht.hour_type_id(+) 
       AND r_req.req_type_id = r_rt.req_type_id(+) 
       AND r_qual_rec.r_retrng_basis_type_id = r_rbt.basis_type_id(+) 
       AND r_qual_rec.r_retrng_period_type_id = r_rpt.period_type_id(+) 
       AND r_qual_rec.r_init_basis_type_id = r_ibt.basis_type_id(+) 
       AND r_qual_rec.r_init_period_type_id = r_ipt.period_type_id(+) 
       ---------- 
       AND gr_qual_rec.qual_id(+) = q.qual_id 
       AND gr_qual_rec.gr_req_seq = gr_req.req_seq(+) 
       AND gr_req.hour_type_id = gr_ht.hour_type_id(+) 
       AND gr_req.req_type_id = gr_rt.req_type_id(+) 
       AND gr_qual_rec.gr_retrng_basis_type_id = gr_rbt.basis_type_id(+) 
       AND gr_qual_rec.gr_retrng_period_type_id = gr_rpt.period_type_id(+) 
       AND gr_qual_rec.gr_init_basis_type_id = gr_ibt.basis_type_id(+) 
       AND gr_qual_rec.gr_init_period_type_id = gr_ipt.period_type_id(+) 
       ------- 
       AND sr_qual_rec.req_seq = sr_req.req_seq(+) 
       AND sr_req.hour_type_id = sr_ht.hour_type_id(+) 
       AND sr_req.req_type_id = sr_rt.req_type_id(+) 
       AND sr_qual_rec.req_group_id(+) = gr_qual_rec.gr_req_group_id 
       ------ 
       AND qd.qual_id(+) = q.qual_id 
       AND qd.doc_id = doc.doc_id(+) 
ORDER  BY q.qual_id 
detailsWithSubQual
(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, 
        NULL                      AS par_qual_id, 
        ------- 
        c.cpnt_typ_id, 
        c.cpnt_id, 
        c.rev_dte, 
        c.cpnt_title, 
        r.rtyp_desc               AS c_rtyp_desc, 
        ip.label_id               AS c_init_period_type_id, 
        qc.init_number            AS c_init_number, 
        ib.label_id               AS c_init_basis_type_id, 
        rp.label_id               AS c_retrng_period_type_id, 
        qc.retrng_number          AS c_retrng_number, 
        rb.label_id               AS c_retrng_basis_type_id, 
        qc.stat_effect_dte, 
        c.rev_num, 
        -------- 
        jp.jp_id, 
        jp.jp_desc, 
        ------ 
        r_qual_rec.r_req_seq, 
        r_qual_rec.r_req_group_id, 
        r_qual_rec.r_req_group_desc, 
        r_qual_rec.r_init_period_type_id, 
        r_qual_rec.r_init_number, 
        r_qual_rec.r_init_basis_type_id, 
        r_qual_rec.r_retrng_period_type_id, 
        r_qual_rec.r_retrng_number, 
        r_qual_rec.r_retrng_basis_type_id, 
        r_req.req_type_id         AS r_req_type_id, 
        r_req.req_id              AS r_req_id, 
        r_req.req_desc            AS r_req_desc, 
        r_req.hour_type_id        AS r_hour_type_id, 
        r_req.num_hours           AS r_num_hours, 
        r_req.num_items           AS r_num_items, 
        r_ht.hour_type_desc       AS r_hour_type_desc, 
        r_ht.label_id             AS r_hour_type_label_id, 
        r_rt.req_type_desc        AS r_req_type_desc, 
        r_rt.label_id             AS r_req_type_label_id, 
        r_ipt.label_id            AS r_init_period_type_label, 
        r_ibt.label_id            AS r_init_basis_type_label, 
        r_rpt.label_id            AS r_retrng_period_type_label, 
        r_rbt.label_id            AS r_retrng_basis_type_label, 
        ------- 
        gr_qual_rec.gr_req_seq, 
        gr_qual_rec.gr_req_group_id, 
        gr_qual_rec.gr_req_group_desc, 
        gr_qual_rec.gr_init_period_type_id, 
        gr_qual_rec.gr_init_number, 
        gr_qual_rec.gr_init_basis_type_id, 
        gr_qual_rec.gr_retrng_period_type_id, 
        gr_qual_rec.gr_retrng_number, 
        gr_qual_rec.gr_retrng_basis_type_id, 
        gr_req.req_type_id        AS gr_req_type_id, 
        gr_req.req_id             AS gr_req_id, 
        gr_req.req_desc           AS gr_req_desc, 
        gr_req.hour_type_id       AS gr_hour_type_id, 
        gr_req.num_hours          AS gr_num_hours, 
        gr_req.num_items          AS gr_num_items, 
        gr_ht.hour_type_desc      AS gr_hour_type_desc, 
        gr_ht.label_id            AS gr_hour_type_label_id, 
        gr_rt.req_type_desc       AS gr_req_type_desc, 
        gr_rt.label_id            AS gr_req_type_label_id, 
        gr_ipt.label_id           AS gr_init_period_type_label, 
        gr_ibt.label_id           AS gr_init_basis_type_label, 
        gr_rpt.label_id           AS gr_retrng_period_type_label, 
        gr_rbt.label_id           AS gr_retrng_basis_type_label, 
        --------- 
        sr_req.req_type_id        AS sr_req_type_id, 
        sr_req.req_id             AS sr_req_id, 
        sr_req.req_desc           AS sr_req_desc, 
        sr_req.hour_type_id       AS sr_hour_type_id, 
        sr_req.num_hours          AS sr_num_hours, 
        sr_req.num_items          AS sr_num_items, 
        sr_ht.hour_type_desc      AS sr_hour_type_desc, 
        sr_ht.label_id            AS sr_hour_type_label_id, 
        sr_rt.req_type_desc       AS sr_req_type_desc, 
        sr_rt.label_id            AS sr_req_type_label_id, 
        NULL                      AS sr_req_group_id, 
        NULL                      AS sr_req_group_desc, 
        ------- 
        doc.doc_id                AS d_doc_id, 
        doc.doc_title             AS doc_title, 
        qd.review_flg             AS review_flag, 
        NULL                      AS d_qual_id, 
        NULL                      AS d_qual_title, 
        0                         AS qorder 
 FROM   pa_qual q, 
        pa_domain d, 
        --------------- 
        pa_qual_cpnt qc, 
        pv_course c, 
        pa_rqmt_type r, 
        ps_period_type ip, 
        ps_basis_type ib, 
        ps_period_type rp, 
        ps_basis_type rb, 
        ----------- 
        pa_qual_jp qj, 
        pa_job_pos jp, 
        ---------- 
        (SELECT a.qual_id, 
                a.req_seq               AS r_req_seq, 
                a.req_group_id          AS r_req_group_id, 
                NULL                    AS r_req_group_desc, 
                a.init_period_type_id   AS r_init_period_type_id, 
                a.init_number           AS r_init_number, 
                a.init_basis_type_id    AS r_init_basis_type_id, 
                a.retrng_period_type_id AS r_retrng_period_type_id, 
                a.retrng_number         AS r_retrng_number, 
                a.retrng_basis_type_id  AS r_retrng_basis_type_id 
         FROM   pa_qual_req a 
         WHERE  req_group_id IS NULL) r_qual_rec, 
        pa_requirement r_req, 
        ps_hour_type r_ht, 
        ps_qual_requirement_type r_rt, 
        ps_period_type r_rpt, 
        ps_basis_type r_rbt, 
        ps_period_type r_ipt, 
        ps_basis_type r_ibt, 
        -------- 
        (SELECT a.qual_id, 
                NULL                    AS gr_req_seq, 
                a.req_group_id          AS gr_req_group_id, 
                a.req_group_desc        AS gr_req_group_desc, 
                a.init_period_type_id   AS gr_init_period_type_id, 
                a.init_number           AS gr_init_number, 
                a.init_basis_type_id    AS gr_init_basis_type_id, 
                a.retrng_period_type_id AS gr_retrng_period_type_id, 
                a.retrng_number         AS gr_retrng_number, 
                a.retrng_basis_type_id  AS gr_retrng_basis_type_id 
         FROM   pa_qual_req_group a) gr_qual_rec, 
        pa_requirement gr_req, 
        ps_hour_type gr_ht, 
        ps_qual_requirement_type gr_rt, 
        ps_period_type gr_rpt, 
        ps_basis_type gr_rbt, 
        ps_period_type gr_ipt, 
        ps_basis_type gr_ibt, 
        --------- 
        pa_qual_req sr_qual_rec, 
        pa_requirement sr_req, 
        ps_hour_type sr_ht, 
        ps_qual_requirement_type sr_rt, 
        ------- 
        pa_qual_doc qd, 
        pa_doc doc 
 WHERE  q.dmn_id = d.dmn_id(+) 
        /** AND q.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 [security:PA_QUAL q]*/ 
        /** AND q.notactive = [NotActive] */ 
        ------- 
        AND qc.qual_id(+) = q.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 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 
        --------- 
        AND qj.qual_id(+) = q.qual_id 
        AND qj.jp_id = jp.jp_id(+) 
        -------- 
        AND r_qual_rec.qual_id(+) = q.qual_id 
        AND r_qual_rec.r_req_seq = r_req.req_seq(+) 
        AND r_req.hour_type_id = r_ht.hour_type_id(+) 
        AND r_req.req_type_id = r_rt.req_type_id(+) 
        AND r_qual_rec.r_retrng_basis_type_id = r_rbt.basis_type_id(+) 
        AND r_qual_rec.r_retrng_period_type_id = r_rpt.period_type_id(+) 
        AND r_qual_rec.r_init_basis_type_id = r_ibt.basis_type_id(+) 
        AND r_qual_rec.r_init_period_type_id = r_ipt.period_type_id(+) 
        ---------- 
        AND gr_qual_rec.qual_id(+) = q.qual_id 
        AND gr_qual_rec.gr_req_seq = gr_req.req_seq(+) 
        AND gr_req.hour_type_id = gr_ht.hour_type_id(+) 
        AND gr_req.req_type_id = gr_rt.req_type_id(+) 
        AND gr_qual_rec.gr_retrng_basis_type_id = gr_rbt.basis_type_id(+) 
        AND gr_qual_rec.gr_retrng_period_type_id = gr_rpt.period_type_id(+) 
        AND gr_qual_rec.gr_init_basis_type_id = gr_ibt.basis_type_id(+) 
        AND gr_qual_rec.gr_init_period_type_id = gr_ipt.period_type_id(+) 
        ------- 
        AND sr_qual_rec.req_seq = sr_req.req_seq(+) 
        AND sr_req.hour_type_id = sr_ht.hour_type_id(+) 
        AND sr_req.req_type_id = sr_rt.req_type_id(+) 
        AND sr_qual_rec.req_group_id(+) = gr_qual_rec.gr_req_group_id 
        ------ 
        AND qd.qual_id(+) = q.qual_id 
        AND qd.doc_id = doc.doc_id(+) 
 UNION 
 SELECT b.qual_id, 
        b.qual_title, 
        b.dmn_id, 
        d.dmn_desc, 
        Decode (b.notactive, 'Y', 'N', 
                             'Y') active, 
        b.qual_desc, 
        b.create_dte, 
        b.force_incomplete, 
        x.* 
 FROM   (SELECT qsq.qual_id          AS par_qual_id, 
                ------- 
                c.cpnt_typ_id, 
                c.cpnt_id, 
                c.rev_dte, 
                c.cpnt_title, 
                r.rtyp_desc          AS c_rtyp_desc, 
                ip.label_id          AS c_init_period_type_id, 
                qc.init_number       AS c_init_number, 
                ib.label_id          AS c_init_basis_type_id, 
                rp.label_id          AS c_retrng_period_type_id, 
                qc.retrng_number     AS c_retrng_number, 
                rb.label_id          AS c_retrng_basis_type_id, 
                qc.stat_effect_dte, 
                c.rev_num, 
                -------- 
                jp.jp_id, 
                jp.jp_desc, 
                ------ 
                r_qual_rec.r_req_seq, 
                r_qual_rec.r_req_group_id, 
                r_qual_rec.r_req_group_desc, 
                r_qual_rec.r_init_period_type_id, 
                r_qual_rec.r_init_number, 
                r_qual_rec.r_init_basis_type_id, 
                r_qual_rec.r_retrng_period_type_id, 
                r_qual_rec.r_retrng_number, 
                r_qual_rec.r_retrng_basis_type_id, 
                r_req.req_type_id    AS r_req_type_id, 
                r_req.req_id         AS r_req_id, 
                r_req.req_desc       AS r_req_desc, 
                r_req.hour_type_id   AS r_hour_type_id, 
                r_req.num_hours      AS r_num_hours, 
                r_req.num_items      AS r_num_items, 
                r_ht.hour_type_desc  AS r_hour_type_desc, 
                r_ht.label_id        AS r_hour_type_label_id, 
                r_rt.req_type_desc   AS r_req_type_desc, 
                r_rt.label_id        AS r_req_type_label_id, 
                r_ipt.label_id       AS r_init_period_type_label, 
                r_ibt.label_id       AS r_init_basis_type_label, 
                r_rpt.label_id       AS r_retrng_period_type_label, 
                r_rbt.label_id       AS r_retrng_basis_type_label, 
                ------- 
                gr_qual_rec.gr_req_seq, 
                gr_qual_rec.gr_req_group_id, 
                gr_qual_rec.gr_req_group_desc, 
                gr_qual_rec.gr_init_period_type_id, 
                gr_qual_rec.gr_init_number, 
                gr_qual_rec.gr_init_basis_type_id, 
                gr_qual_rec.gr_retrng_period_type_id, 
                gr_qual_rec.gr_retrng_number, 
                gr_qual_rec.gr_retrng_basis_type_id, 
                gr_req.req_type_id   AS gr_req_type_id, 
                gr_req.req_id        AS gr_req_id, 
                gr_req.req_desc      AS gr_req_desc, 
                gr_req.hour_type_id  AS gr_hour_type_id, 
                gr_req.num_hours     AS gr_num_hours, 
                gr_req.num_items     AS gr_num_items, 
                gr_ht.hour_type_desc AS gr_hour_type_desc, 
                gr_ht.label_id       AS gr_hour_type_label_id, 
                gr_rt.req_type_desc  AS gr_req_type_desc, 
                gr_rt.label_id       AS gr_req_type_label_id, 
                gr_ipt.label_id      AS gr_init_period_type_label, 
                gr_ibt.label_id      AS gr_init_basis_type_label, 
                gr_rpt.label_id      AS gr_retrng_period_type_label, 
                gr_rbt.label_id      AS gr_retrng_basis_type_label, 
                --------- 
                sr_req.req_type_id   AS sr_req_type_id, 
                sr_req.req_id        AS sr_req_id, 
                sr_req.req_desc      AS sr_req_desc, 
                sr_req.hour_type_id  AS sr_hour_type_id, 
                sr_req.num_hours     AS sr_num_hours, 
                sr_req.num_items     AS sr_num_items, 
                sr_ht.hour_type_desc AS sr_hour_type_desc, 
                sr_ht.label_id       AS sr_hour_type_label_id, 
                sr_rt.req_type_desc  AS sr_req_type_desc, 
                sr_rt.label_id       AS sr_req_type_label_id, 
                NULL                 AS sr_req_group_id, 
                NULL                 AS sr_req_group_desc, 
                ------- 
                doc.doc_id           AS d_doc_id, 
                doc.doc_title        AS doc_title, 
                qd.review_flg        AS review_flag, 
                q.qual_id            AS d_qual_id, 
                q.qual_title         AS d_qual_title, 
                1                    AS qorder 
         -------- 
         FROM   pa_qual q, 
                pa_domain d, 
                --------------- 
                pa_qual_cpnt qc, 
                pv_course c, 
                pa_rqmt_type r, 
                ps_period_type ip, 
                ps_basis_type ib, 
                ps_period_type rp, 
                ps_basis_type rb, 
                ----------- 
                pa_qual_jp qj, 
                pa_job_pos jp, 
                ---------- 
                (SELECT a.qual_id, 
                        a.req_seq               AS r_req_seq, 
                        a.req_group_id          AS r_req_group_id, 
                        NULL                    AS r_req_group_desc, 
                        a.init_period_type_id   AS r_init_period_type_id, 
                        a.init_number           AS r_init_number, 
                        a.init_basis_type_id    AS r_init_basis_type_id, 
                        a.retrng_period_type_id AS r_retrng_period_type_id, 
                        a.retrng_number         AS r_retrng_number, 
                        a.retrng_basis_type_id  AS r_retrng_basis_type_id 
                 FROM   pa_qual_req a 
                 WHERE  req_group_id IS NULL) r_qual_rec, 
                pa_requirement r_req, 
                ps_hour_type r_ht, 
                ps_qual_requirement_type r_rt, 
                ps_period_type r_rpt, 
                ps_basis_type r_rbt, 
                ps_period_type r_ipt, 
                ps_basis_type r_ibt, 
                -------- 
                (SELECT a.qual_id, 
                        NULL                    AS gr_req_seq, 
                        a.req_group_id          AS gr_req_group_id, 
                        a.req_group_desc        AS gr_req_group_desc, 
                        a.init_period_type_id   AS gr_init_period_type_id, 
                        a.init_number           AS gr_init_number, 
                        a.init_basis_type_id    AS gr_init_basis_type_id, 
                        a.retrng_period_type_id AS gr_retrng_period_type_id, 
                        a.retrng_number         AS gr_retrng_number, 
                        a.retrng_basis_type_id  AS gr_retrng_basis_type_id 
                 FROM   pa_qual_req_group a) gr_qual_rec, 
                pa_requirement gr_req, 
                ps_hour_type gr_ht, 
                ps_qual_requirement_type gr_rt, 
                ps_period_type gr_rpt, 
                ps_basis_type gr_rbt, 
                ps_period_type gr_ipt, 
                ps_basis_type gr_ibt, 
                --------- 
                pa_qual_req sr_qual_rec, 
                pa_requirement sr_req, 
                ps_hour_type sr_ht, 
                ps_qual_requirement_type sr_rt, 
                ------- 
                pa_qual_doc qd, 
                pa_doc doc, 
                -----------SUB SUB SUB SUB SUB ------------------------------------------ 
                pa_qual_subqual qsq 
         WHERE  q.dmn_id = d.dmn_id(+) 
                /** AND qsq.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 [security:PA_QUAL q]*/ 
                /** AND q.notactive = [NotActive] */ 
                ------- 
                AND qc.qual_id(+) = q.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 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 
                --------- 
                AND qj.qual_id(+) = q.qual_id 
                AND qj.jp_id = jp.jp_id(+) 
                -------- 
                AND r_qual_rec.qual_id(+) = q.qual_id 
                AND r_qual_rec.r_req_seq = r_req.req_seq(+) 
                AND r_req.hour_type_id = r_ht.hour_type_id(+) 
                AND r_req.req_type_id = r_rt.req_type_id(+) 
                AND r_qual_rec.r_retrng_basis_type_id = r_rbt.basis_type_id(+) 
                AND r_qual_rec.r_retrng_period_type_id = r_rpt.period_type_id(+) 
                AND r_qual_rec.r_init_basis_type_id = r_ibt.basis_type_id(+) 
                AND r_qual_rec.r_init_period_type_id = r_ipt.period_type_id(+) 
                ---------- 
                AND gr_qual_rec.qual_id(+) = q.qual_id 
                AND gr_qual_rec.gr_req_seq = gr_req.req_seq(+) 
                AND gr_req.hour_type_id = gr_ht.hour_type_id(+) 
                AND gr_req.req_type_id = gr_rt.req_type_id(+) 
                AND gr_qual_rec.gr_retrng_basis_type_id = 
                    gr_rbt.basis_type_id(+) 
                AND gr_qual_rec.gr_retrng_period_type_id = 
                    gr_rpt.period_type_id(+) 
                AND gr_qual_rec.gr_init_basis_type_id = gr_ibt.basis_type_id(+) 
                AND gr_qual_rec.gr_init_period_type_id = 
                    gr_ipt.period_type_id(+) 
                ------- 
                AND sr_qual_rec.req_seq = sr_req.req_seq(+) 
                AND sr_req.hour_type_id = sr_ht.hour_type_id(+) 
                AND sr_req.req_type_id = sr_rt.req_type_id(+) 
                AND sr_qual_rec.req_group_id(+) = gr_qual_rec.gr_req_group_id 
                ------ 
                AND qd.qual_id(+) = q.qual_id 
                AND qd.doc_id = doc.doc_id(+) 
                ---------------------------------------------SUB SUB SUB SUB SUB-------------- 
                AND qsq.qual_id_sub = q.qual_id)x, 
        pa_qual b, 
        pa_domain d 
 WHERE  b.qual_id = x.par_qual_id 
        AND d.dmn_id = b.dmn_id) 
ORDER  BY qual_id, 
          qorder 

 

 

 

 

 

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

 

  • No labels