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

This report shows the mean score (the average results of the rating scale   questions) for each follow-up survey and survey page.

 

Report NameFollow-Up Evaluation By Individual Response
REPORT_NAME_IDFollowUpEvaluationByIndividualResponse
REPORT_ID125
CATEGORY_IDLearning
GROUP_IDFollowUpEvaluationByIndividualResponse
WORKFLOW_IDRun FollowUpEvaluationByIndividualResponse Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

 

 

LEVE3_SECTION_RATING_SCALE
SELECT   ( 
         CASE 
                  WHEN all_rating_labels.rating = 0 THEN 'N/A' 
                  ELSE Concat(Concat(all_rating_labels.rating, ' - '), all_rating_labels.rating_label)
         END) rating_label, 
         sup_label.supervisor_x, 
         emp_label.employee_x, 
         all_rating_labels.instructor_name 
FROM     ( 
                SELECT prsl.rating, 
                       al.label_value                                                                                           AS rating_label,
                              Concat((instdetail.fname), Concat(' ', Concat((instdetail.lname), Concat(' ', (instdetail.mi)))))    instructor_name
                FROM   pa_survey_responses psr, 
                       pa_survey_question psq, 
                       pa_stud_survey pss, 
                       pa_inst instdetail, 
                       pa_cpnt_survey pcs, 
                       pa_rating_scale_label prsl, 
                       pv_i18n_active_locale_label al 
                WHERE  psq.question_id = psr.question_id 
                AND    psr.stud_survey_id = pss.stud_survey_id 
                AND    pss.cpnt_survey_id = pcs.cpnt_survey_id 
                AND    psq.question_type = 'RATING SCALE' 
                AND    ( 
                              pss.rater_type_id = 'EMPLOYEE' 
                       OR     pss.rater_type_id = 'SUPERVISOR') 
                AND    psq.rating_scale_id = prsl.rating_scale_id 
                AND 
                       -- psr.stud_response_value = prsl.rating(+) and 
                       pss.cpnt_survey_id = ? 
                AND    pss.item_completion_date = ? 
                AND    psq.section_id = ? 
                AND    psq.question_id = ? 
                AND    pss.stud_id = ? 
                AND    al.label_id (+) = prsl.rating_label 
                AND    instdetail.inst_id (+) = psr.inst_id 
                AND    al.locale_id (+) = ? 
                UNION 
                SELECT 0, 
                       'N/A', 
                       NULL 
                FROM   dual ) all_rating_labels, 
         ( 
                  SELECT   pss.cpnt_survey_id, 
                           pss.rater_type_id, 
                           psq.question_id, 
                           psq.rating_scale_id, 
                           psq.section_id, 
                           psr.stud_response_value, ( 
                           CASE 
                                    WHEN pss.rater_type_id IS NULL THEN '-' 
                                    ELSE 'X' 
                           END) supervisor_x 
                  FROM     pa_survey_responses psr, 
                           pa_survey_question psq, 
                           pa_stud_survey pss, 
                           pa_cpnt_survey pcs 
                  WHERE    psq.question_id = psr.question_id 
                  AND      psr.stud_survey_id = pss.stud_survey_id 
                  AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
                  AND      psq.question_type = 'RATING SCALE' 
                  AND      pss.rater_type_id = 'SUPERVISOR' 
                  AND      pss.cpnt_survey_id = ? 
                  AND      pss.item_completion_date = ? 
                  AND      psq.section_id = ? 
                  AND      psq.question_id = ? 
                  AND      pss.assessee_id = ? 
                  GROUP BY (pss.cpnt_survey_id, pss.rater_type_id, psq.question_id, psq.rating_scale_id, psq.section_id, psr.stud_response_value) ) sup_label,
         ( 
                  SELECT   pss.cpnt_survey_id, 
                           pss.rater_type_id, 
                           psq.question_id, 
                           psq.rating_scale_id, 
                           psq.section_id, 
                           psr.stud_response_value, ( 
                           CASE 
                                    WHEN pss.rater_type_id IS NULL THEN '-' 
                                    ELSE 'X' 
                           END) employee_x 
                  FROM     pa_survey_responses psr, 
                           pa_survey_question psq, 
                           pa_stud_survey pss, 
                           pa_cpnt_survey pcs 
                  WHERE    psq.question_id = psr.question_id 
                  AND      psr.stud_survey_id = pss.stud_survey_id 
                  AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
                  AND      psq.question_type = 'RATING SCALE' 
                  AND      pss.rater_type_id = 'EMPLOYEE' 
                  AND      pss.cpnt_survey_id = ? 
                  AND      pss.item_completion_date = ? 
                  AND      psq.section_id = ? 
                  AND      psq.question_id = ? 
                  AND      pss.assessee_id = ? 
                  GROUP BY (pss.cpnt_survey_id, pss.rater_type_id, psq.question_id, psq.rating_scale_id, psq.section_id, psr.stud_response_value) ) emp_label
WHERE    all_rating_labels.rating = sup_label.stud_response_value(+) 
AND      all_rating_labels.rating = emp_label.stud_response_value(+) 
AND      (( 
                           sup_label.stud_response_value > -1) 
         OR       ( 
                           emp_label.stud_response_value > -1)) 
ORDER BY all_rating_labels.rating
LEVEL3_COMPONENT_DETAILS
SELECT DISTINCT t2.cpnt_survey_id, 
                t2.cpnt_title  cpnt_title, 
                t2.cpnt_typ_id cpnt_typ_id, 
                t2.cpnt_id     cpnt_id, 
                t2.rev_dte     rev_dte, 
                t2.item_completion_date, 
                Nvl(t1.total, 0)      total, 
                Nvl(t1.mean_score, 0) mean_score 
FROM            ( 
                         SELECT   pss.cpnt_survey_id cpnt_survey_id, 
                                  pss.item_completion_date, 
                                  Count(pss.stud_survey_id)    total, 
                                  Avg(psr.stud_response_value) mean_score 
                         FROM     pa_questionnaire_survey pqs, 
                                  pa_survey_responses psr, 
                                  pa_stud_survey pss, 
                                  pv_course pcpnt, 
                                  pa_cpnt_survey pcs, 
                                  pa_survey_question psq, 
                                  pa_rating_scale_label prsl 
                         WHERE    psr.stud_survey_id = pss.stud_survey_id 
                         AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
                         AND      psq.question_id = psr.question_id 
                         AND      psq.question_type = 'RATING SCALE' 
                         AND      pcs.survey_id = pqs.survey_id 
                         AND      psr.stud_response_value = prsl.rating 
                         AND      psq.rating_scale_id = prsl.rating_scale_id 
                         AND      pcpnt.cpnt_typ_id = pcs.cpnt_typ_id 
                         AND      pcpnt.cpnt_id = pcs.cpnt_id 
                         AND      pcpnt.rev_dte = pcs.rev_dte 
                         AND      pqs.level_indicator = 3 
                         AND      pss.assessee_id = ? 
                                  /**  and pqs.survey_id in [SurveyIDSearch]  and pqs.is_active = [QuestionnaireSurveyStatus]  and trunc(pss.item_completion_date) >= [LearningEventFromDate]  and NOT trunc(pss.item_completion_date) > [LearningEventToDate]  and (pcpnt.cpnt_typ_id,pcpnt.cpnt_id,pcpnt.rev_dte) in [ItemSearch]  and pcs.is_active = [IncludePreviousItemAssociations]  */ 
                         GROUP BY (pss.cpnt_survey_id, pss.item_completion_date) ) t1, 
                ( 
                                SELECT DISTINCT(pcs.cpnt_survey_id)      cpnt_survey_id, 
                                                pss.item_completion_date item_completion_date,
                                                pcpnt.cpnt_title         cpnt_title, 
                                                pcs.cpnt_typ_id          cpnt_typ_id, 
                                                pcs.cpnt_id              cpnt_id, 
                                                pcs.rev_dte              rev_dte 
                                FROM            pa_questionnaire_survey pqs, 
                                                pa_ssg_inst psi, 
                                                pa_stud_survey pss, 
                                                pa_cpnt_survey pcs, 
                                                pa_ssg_locn psl, 
                                                pv_course pcpnt 
                                WHERE           pcs.survey_id = pqs.survey_id 
                                AND             psi.schd_id(+) = pss.sch_id 
                                AND             psl.schd_id(+) = pss.sch_id 
                                AND             pcs.cpnt_survey_id = pss.cpnt_survey_id 
                                AND             pcpnt.cpnt_typ_id = pcs.cpnt_typ_id 
                                AND             pcpnt.cpnt_id = pcs.cpnt_id 
                                AND             pcpnt.rev_dte = pcs.rev_dte 
                                AND             pqs.level_indicator = 3 
                                AND             pss.assessee_id = ? 
                                                /**  and pqs.survey_id in [SurveyIDSearch]  and pqs.is_active = [QuestionnaireSurveyStatus]  and trunc(pss.item_completion_date) >= [LearningEventFromDate]  and NOT trunc(pss.item_completion_date) > [LearningEventToDate]  and psi.inst_id in [InstructorSearch]  and pss.stud_id in [UserSearch]  and pss.sch_id in [ScheduledOfferingSearch]  and psl.locn_id in [LocationSearch]  and (pcs.cpnt_typ_id, pcs.cpnt_id, pcs.rev_dte) in [ItemSearch]  and pcs.is_active = [IncludePreviousItemAssociations]  */ 
                ) t2 
WHERE           t1.cpnt_survey_id(+) = t2.cpnt_survey_id 
AND             t1.item_completion_date(+) = t2.item_completion_date
LEVEL3_COMPONENT_EVALUATOR
SELECT   Nvl(ps.lname, ' ') lname, 
         Nvl(ps.fname, ' ') fname, 
         Nvl(ps.mi, ' ')    mi, 
         pss.cpnt_survey_id, 
         pss.item_completion_date, 
         pss.stud_id, 
         pss.assessee_id, ( 
         CASE 
                  WHEN pss.stud_id <> pss.assessee_id THEN pss.stud_id 
                  ELSE NULL 
         END ) evaluator 
FROM     pa_questionnaire_survey pqs, 
         pa_cpnt_survey pcs, 
         pa_stud_survey pss, 
         pa_student ps 
WHERE    pqs.survey_id = pcs.survey_id 
AND      pcs.cpnt_survey_id = pss.cpnt_survey_id 
AND      pss.stud_id = ps.stud_id 
AND      pss.assessee_id = ? 
AND      pcs.cpnt_survey_id = ? 
AND      pss.stud_id <> pss.assessee_id 
         /**  and pqs.survey_id in [SurveyIDSearch]  and pqs.is_active = [QuestionnaireSurveyStatus]  and trunc(pss.item_completion_date) >= [LearningEventFromDate]  and NOT trunc(pss.item_completion_date) > [LearningEventToDate]  and (pcs.cpnt_typ_id, pcs.cpnt_id, pcs.rev_dte) in [ItemSearch]  and pcs.is_active = [IncludePreviousItemAssociations]  */ 
GROUP BY ( ps.fname, ps.lname, ps.mi, pss.cpnt_survey_id, pss.item_completion_date, pss.stud_id, pss.assessee_id)
LEVEL3_PAGE_DETAILS
SELECT   t1.cpnt_survey_id cpnt_survey_id, 
         t1.section_id, 
         t1.section_title, 
         t1.section_order, 
         t1.resource_type, 
         Nvl(t2.stud_survey_count,0) stud_survey_count, 
         Nvl(t2.mean_score, 0)       mean_score 
FROM     ( 
                  SELECT   pss.cpnt_survey_id cpnt_survey_id, 
                           psq.section_id, 
                           pssc.section_order, 
                           pssc.section_title, 
                           pssc.resource_type, 
                           Count(psq.section_id) 
                  FROM     pa_questionnaire_survey pqs, 
                           pa_survey_responses psr, 
                           pa_stud_survey pss, 
                           pa_survey_section pssc, 
                           pa_cpnt_survey pcs, 
                           pa_survey_question psq 
                  WHERE    psr.stud_survey_id = pss.stud_survey_id 
                  AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
                  AND      psq.question_id = psr.question_id 
                  AND      pcs.survey_id = pqs.survey_id 
                  AND      psq.section_id = pssc.section_id 
                  AND      pqs.level_indicator = 3 
                  AND      pss.cpnt_survey_id = ? 
                  AND      pss.assessee_id = ? 
                  GROUP BY (pss.cpnt_survey_id, pssc.section_order, psq.section_id, pssc.section_title,pssc.resource_type) ) t1,
         ( 
                  SELECT   pss.cpnt_survey_id cpnt_survey_id, 
                           psq.section_id, 
                           count(pss.stud_survey_id)    stud_survey_count, 
                           avg(psr.stud_response_value) mean_score 
                  FROM     pa_questionnaire_survey pqs, 
                           pa_survey_responses psr, 
                           pa_stud_survey pss, 
                           pa_cpnt_survey pcs, 
                           pa_survey_question psq, 
                           pa_rating_scale_label prsl 
                  WHERE    psr.stud_survey_id = pss.stud_survey_id 
                  AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
                  AND      psq.question_id = psr.question_id 
                  AND      psq.question_type = 'RATING SCALE' 
                  AND      pcs.survey_id = pqs.survey_id 
                  AND      psr.stud_response_value = prsl.rating 
                  AND      psq.rating_scale_id = prsl.rating_scale_id 
                  AND      pqs.level_indicator = 3 
                  AND      pss.cpnt_survey_id = ? 
                  AND      pss.assessee_id = ? 
                  AND      pss.item_completion_date = ? 
                  GROUP BY (pss.cpnt_survey_id, psq.section_id) ) t2 
WHERE    t1.cpnt_survey_id = t2.cpnt_survey_id(+) 
AND      t1.section_id = t2.section_id(+) 
ORDER BY t1.cpnt_survey_id, 
         t1.section_order, 
         t1.section_id
LEVEL3_QUESTION_COMMENTS
SELECT DISTINCT PSR.stud_comments AS STUD_COMMENTS, 
                Concat(( inst.fname ), Concat(' ', Concat(( inst.lname ), Concat 
                                                   (' ', 
( inst.mi ))))) 
INSTRUCTOR_NAME 
FROM   pa_cpnt_survey PCS, 
       pa_stud_survey PSS, 
       pa_survey_responses PSR, 
       pa_ssg_inst PSI, 
       pa_ssg_locn PSL, 
       pa_inst INST 
WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
       AND PSS.stud_survey_id = PSR.stud_survey_id 
       AND PSL.schd_id(+) = PSS.sch_id 
       AND PSI.schd_id(+) = PSS.sch_id 
       AND INST.inst_id(+) = PSR.inst_id 
       AND PSR.stud_comments IS NOT NULL 
       AND PSS.cpnt_survey_id = ? 
       AND PSR.question_id = ? 
       AND PSS.assessee_id = ? 
       AND PSS.item_completion_date = ? 
/**          AND PCS.SURVEY_ID in [SurveyIDSearch]    AND (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) in [ItemSearch]    AND PCS.IS_ACTIVE  = [IncludePreviousItemAssociations]    AND PSS.ASSESSEE_ID in [UserSearch]    AND trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]    AND NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]    AND PSS.SCH_ID in [ScheduledOfferingSearch]    AND PSI.INST_ID in [InstructorSearch]    AND PSL.LOCN_ID in [LocationSearch]    */ 
LEVEL3_QUESTION_DETAILS
SELECT pss.cpnt_survey_id, 
       psq.section_id, 
       psq.question_id, 
       psq.question_type, 
       psq.question_order AS question_order_number, 
       psq.question_text 
FROM   pa_questionnaire_survey pqs, 
       pa_survey_responses psr, 
       pa_stud_survey pss, 
       pa_cpnt_survey pcs, 
       pa_survey_question psq 
WHERE  psr.stud_survey_id = pss.stud_survey_id 
       AND pss.cpnt_survey_id = pcs.cpnt_survey_id 
       AND psq.question_id = psr.question_id 
       AND pcs.survey_id = pqs.survey_id 
       AND pss.cpnt_survey_id = ? 
       AND psq.section_id = ? 
       AND pqs.level_indicator = 3 
GROUP  BY pss.cpnt_survey_id, 
          psq.section_id, 
          psq.question_id, 
          psq.question_type, 
          psq.question_order, 
          psq.question_text 
ORDER  BY psq.question_order 
LEVEL3_SECTION_OPEN_ENDED
SELECT 
--  pss.cpnt_survey_id cpnt_survey_id, psr.stud_survey_id stud_survey_id, 
--  psq.section_id section_id, psq.question_id, pss.rater_type_id, psq.question_type, 
psr.stud_response_text, 
( CASE 
    WHEN pss.rater_type_id = 'EMPLOYEE' THEN 'X' 
    ELSE '' 
  END ) EMPLOYEE_X, 
( CASE 
    WHEN pss.rater_type_id = 'SUPERVISOR' THEN 'X' 
    ELSE '' 
  END ) SUPERVISOR_X, 
Concat(( inst.fname ), Concat(' ', Concat(( inst.lname ), Concat(' ', 
                                                          ( inst.mi ))))) 
        INSTRUCTOR_NAME 
FROM   pa_survey_responses psr, 
       pa_stud_survey pss, 
       pa_cpnt_survey pcs, 
       pa_survey_question psq, 
       pa_inst inst 
WHERE  psr.stud_survey_id = pss.stud_survey_id 
       AND pss.cpnt_survey_id = pcs.cpnt_survey_id 
       AND 
       --  pss.rater_type_id = 'EMPLOYEE' and 
       psq.question_id = psr.question_id 
       AND psq.question_type IN ( 'OPEN ENDED' ) 
       AND inst.inst_id (+) = psr.inst_id 
       --  pcs.survey_id = 'Level3ReportTestSurvey' and psr.stud_survey_id = 147 and 
       AND pcs.cpnt_survey_id = ? 
       AND psq.section_id = ? 
       AND psq.question_id = ? 
       AND pss.assessee_id = ? 
       AND pss.item_completion_date = ? 
LEVEL3_SECTION_SINGLE_N_MULTI_CHOICES
SELECT T1.choice_label, 
       T1.instructor_name, 
       ( CASE 
           WHEN T2.rater_type_id IS NULL THEN '' 
           ELSE 'X' 
         END ) EMPLOYEE_X, 
       ( CASE 
           WHEN T3.rater_type_id IS NULL THEN '' 
           ELSE 'X' 
         END ) SUPERVISOR_X 
FROM   (SELECT pqac.choice_label, 
               Concat(( inst.fname ), Concat(' ', Concat(( inst.lname ), Concat( 
                                                  ' ', 
       ( inst.mi ))))) 
       INSTRUCTOR_NAME 
        --    pqs.survey_id, pss.section_id, psq.question_id, psq.question_text, 
        --    pqac.answer_id, pqac.choice_label, pqac.choice_value, pqac.choice_order 
        FROM   pa_question_answer_choices pqac, 
               pa_survey_question psq, 
               pa_questionnaire_survey pqs, 
               pa_survey_section pss, 
               pa_survey_responses psr, 
               pa_inst inst 
        WHERE  pqs.survey_id = pss.survey_id 
               AND pss.section_id = psq.section_id 
               AND psq.question_id = pqac.question_id 
               AND psr.stud_response_value = pqac.answer_id 
               AND inst.inst_id (+) = psr.inst_id 
               AND 
               --    psq.question_type = 'ONE CHOICE' and 
               --    pqs.survey_id = 'Level3ReportTestSurvey' and 
               pss.section_id = ? 
               AND psq.question_id = ?) T1, 
       (SELECT 
       --    pss.cpnt_survey_id cpnt_survey_id, psr.stud_survey_id stud_survey_id, psq.section_id section_id, psq.question_id,
       pqac.choice_label choice_label, 
       pss.rater_type_id rater_type_id 
        FROM   pa_survey_responses psr, 
               pa_stud_survey pss, 
               pa_cpnt_survey pcs, 
               pa_survey_question psq, 
               pa_question_answer_choices pqac 
        WHERE  psr.stud_survey_id = pss.stud_survey_id 
               AND pss.cpnt_survey_id = pcs.cpnt_survey_id 
               AND pss.rater_type_id = 'EMPLOYEE' 
               AND psq.question_id = psr.question_id 
               AND 
               --    psq.question_type IN ('ONE CHOICE') and 
               --    pcs.survey_id = 'Level3ReportTestSurvey' and psr.stud_survey_id = 147 and 
               pcs.cpnt_survey_id = ? 
               AND pss.item_completion_date = ? 
               AND psq.section_id = ? 
               AND psq.question_id = ? 
               AND pss.assessee_id = ? 
               AND psr.stud_response_value = pqac.answer_id) T2, 
       (SELECT 
       --    pss.cpnt_survey_id cpnt_survey_id, psr.stud_survey_id stud_survey_id, psq.section_id section_id, psq.question_id,
       pqac.choice_label choice_label, 
       pss.rater_type_id rater_type_id 
        FROM   pa_survey_responses psr, 
               pa_stud_survey pss, 
               pa_cpnt_survey pcs, 
               pa_survey_question psq, 
               pa_question_answer_choices pqac 
        WHERE  psr.stud_survey_id = pss.stud_survey_id 
               AND pss.cpnt_survey_id = pcs.cpnt_survey_id 
               AND pss.rater_type_id = 'SUPERVISOR' 
               AND psq.question_id = psr.question_id 
               AND 
               --    psq.question_type IN ('ONE CHOICE') and 
               --    pcs.survey_id = 'Level3ReportTestSurvey' and psr.stud_survey_id = 147 and 
               pcs.cpnt_survey_id = ? 
               AND pss.item_completion_date = ? 
               AND psq.section_id = ? 
               AND psq.question_id = ? 
               AND pss.assessee_id = ? 
               AND psr.stud_response_value = pqac.answer_id) T3 
WHERE  T1.choice_label = T2.choice_label(+) 
       AND T1.choice_label = T3.choice_label(+) 
       AND ( T2.rater_type_id IS NOT NULL 
              OR T3.rater_type_id IS NOT NULL ) 
LEVEL3_SURVEY_SCORES_CHART1
SELECT   pss.cpnt_survey_id        cpnt_survey_id, 
         Count(pss.stud_survey_id) count, ( 
         CASE 
                  WHEN pss.rater_type_id = 'SUPERVISOR' THEN 'Evaluator' 
                  WHEN pss.rater_type_id = 'EMPLOYEE' THEN 'Employee' 
                  ELSE '-' 
         END)                         rater_type_id, 
         Avg(psr.stud_response_value) mean_score 
FROM     pa_questionnaire_survey pqs, 
         pa_survey_responses psr, 
         pa_stud_survey pss, 
         pa_cpnt_survey pcs, 
         pa_survey_question psq, 
         pa_rating_scale_label prsl 
WHERE    psr.stud_survey_id = pss.stud_survey_id 
AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
AND      psq.question_id = psr.question_id 
AND      psq.question_type = 'RATING SCALE' 
AND      pcs.survey_id = pqs.survey_id 
AND      psr.stud_response_value = prsl.rating 
AND      psq.rating_scale_id = prsl.rating_scale_id 
AND      pqs.level_indicator = 3 
AND      pss.cpnt_survey_id = ? 
AND      pss.assessee_id = ? 
AND      pss.item_completion_date = ? 
         /**  and pqs.survey_id in [SurveyIDSearch]  and pqs.is_active like [QuestionnaireSurveyStatus]  and trunc(pss.item_completion_date) >= [LearningEventFromDate]  and NOT trunc(pss.item_completion_date) > [LearningEventToDate]  and pcs.is_active = [IncludePreviousItemAssociations]  */ 
GROUP BY (pss.cpnt_survey_id, pss.rater_type_id) 
order BY pss.rater_type_id ASC
LEVEL3_SURVEY_SCORES_CHART2
SELECT   pss.cpnt_survey_id cpnt_survey_id, 
         psq.section_id, 
         Count(pss.stud_survey_id) stud_survey_count, ( 
         CASE 
                  WHEN pss.rater_type_id = 'SUPERVISOR' THEN 'Evaluator' 
                  WHEN pss.rater_type_id = 'EMPLOYEE' THEN 'Employee' 
                  ELSE '-' 
         END)                         rater_type_id, 
         Avg(psr.stud_response_value) mean_score 
FROM     pa_questionnaire_survey pqs, 
         pa_survey_responses psr, 
         pa_stud_survey pss, 
         pa_cpnt_survey pcs, 
         pa_survey_question psq, 
         pa_rating_scale_label prsl 
WHERE    psr.stud_survey_id = pss.stud_survey_id 
AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
AND      psq.question_id = psr.question_id 
AND      psq.question_type = 'RATING SCALE' 
AND      psr.stud_response_value = prsl.rating 
AND      pcs.survey_id = pqs.survey_id 
AND      psq.rating_scale_id = prsl.rating_scale_id 
AND      pqs.level_indicator = 3 
AND      pss.cpnt_survey_id = ? 
AND      psq.section_id = ? 
AND      pss.assessee_id = ? 
AND      pss.item_completion_date = ? 
         /**  and pqs.survey_id in [SurveyIDSearch]  and pqs.is_active = [QuestionnaireSurveyStatus]  and trunc(pss.item_completion_date) >= [LearningEventFromDate]  and NOT trunc(pss.item_completion_date) > [LearningEventToDate]  and pcs.is_active = [IncludePreviousItemAssociations]  */ 
GROUP BY (pss.cpnt_survey_id, psq.section_id, pss.rater_type_id) 
order BY pss.rater_type_id ASC
LEVEL3_USER_MAIN_DETAILS
SELECT ps.stud_id            stud_id, 
       Nvl(ps.lname, ' ')    lname, 
       Nvl(ps.fname, ' ')    fname, 
       Nvl(ps.mi, ' ')       mi, 
       Nvl(t1.mean_score, 0) mean_score 
FROM   ( 
                SELECT   pss.stud_id                  stud_id, 
                         Count(pss.assessee_id)       count, 
                         Avg(psr.stud_response_value) mean_score 
                FROM     pa_questionnaire_survey pqs, 
                         pa_survey_responses psr, 
                         pv_course pcpnt, 
                         pa_stud_survey pss, 
                         pa_cpnt_survey pcs, 
                         pa_survey_question psq, 
                         pa_rating_scale_label prsl 
                WHERE    psr.stud_survey_id = pss.stud_survey_id 
                AND      pss.cpnt_survey_id = pcs.cpnt_survey_id 
                AND      pcpnt.cpnt_typ_id = pcs.cpnt_typ_id 
                AND      pcpnt.cpnt_id = pcs.cpnt_id 
                AND      pcpnt.rev_dte = pcs.rev_dte 
                AND      psq.question_id = psr.question_id 
                AND      psq.question_type = 'RATING SCALE' 
                AND      psr.stud_response_value = prsl.rating 
                AND      psq.rating_scale_id = prsl.rating_scale_id 
                AND      pcs.survey_id = pqs.survey_id 
                AND      pqs.level_indicator = 3 
                         /**  and trunc(pss.item_completion_date) >= [LearningEventFromDate]  and NOT trunc(pss.item_completion_date) > [LearningEventToDate]  and pcs.survey_id in [SurveyIDSearch]  and pqs.is_active like [QuestionnaireSurveyStatus]  and pss.assessee_id in [UserSearch]  and (pcpnt.cpnt_typ_id,pcpnt.cpnt_id,pcpnt.rev_dte) in [ItemSearch]  and pcs.is_active = [IncludePreviousItemAssociations]  */ 
                GROUP BY (pss.stud_id) ) t1, 
       ( 
                SELECT   UNIQUE(Decode(pss.rater_type_id, 
                                       'SUPERVISOR',pss.assessee_id, 
                                       pss.stud_id))AS stud_id 
                FROM     pa_questionnaire_survey pqs, 
                         pa_stud_survey pss, 
                         pa_cpnt_survey pcs, 
                         pv_course pcpnt, 
                         pa_ssg_inst psi, 
                         pa_ssg_locn psl 
                WHERE    pqs.survey_id = pcs.survey_id 
                AND      pcs.cpnt_survey_id = pss.cpnt_survey_id 
                AND      pcpnt.cpnt_typ_id = pcs.cpnt_typ_id 
                AND      pcpnt.cpnt_id = pcs.cpnt_id 
                AND      pcpnt.rev_dte = pcs.rev_dte 
                AND      pcs.survey_id = pqs.survey_id 
                AND      pqs.level_indicator = 3 
                AND      psi.schd_id(+) = pss.sch_id 
                AND      psl.schd_id(+) = pss.sch_id 
                AND      ( 
                                  pss.rater_type_id = 'EMPLOYEE' 
                         OR       pss.rater_type_id = 'SUPERVISOR') 
                         /**  and trunc(pss.item_completion_date) >= [LearningEventFromDate]  and NOT trunc(pss.item_completion_date) > [LearningEventToDate]  and pcs.survey_id in [SurveyIDSearch]  and pqs.is_active = [QuestionnaireSurveyStatus]  and pss.assessee_id in [UserSearch]  and (pcpnt.cpnt_typ_id,pcpnt.cpnt_id,pcpnt.rev_dte) in [ItemSearch]  and pcs.is_active = [IncludePreviousItemAssociations]  and psi.inst_id in [InstructorSearch]  and pss.sch_id in [ScheduledOfferingSearch]  and psl.locn_id in [LocationSearch]  */ 
                         /**  and [security:PA_QUESTIONNAIRE_SURVEY pqs]  */ 
                GROUP BY (pss.rater_type_id,pss.assessee_id,pss.stud_id) )t2, 
       pa_student ps 
WHERE  t2.stud_id = ps.stud_id 
AND    t2.stud_id = t1.stud_id(+)

 

 

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

 

 

  • No labels