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   (CSV)
REPORT_NAME_IDFollowUpEvaluationByIndividualResponseCSV
REPORT_ID390
CATEGORY_IDLearning
GROUP_IDFollowUpEvaluationByIndividualResponse
WORKFLOW_IDRun FollowUpEvaluationByIndividualResponse Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

 

ResponseDataSet
SELECT UNIQUE * 
FROM   ( 
              SELECT ps.stud_id         stud_id, 
                     Nvl(ps.lname, ' ') lname, 
                     Nvl(ps.fname, ' ') fname, 
                     Nvl(ps.mi, ' ')    mi 
              FROM   ( 
                              SELECT   pss.stud_id stud_id 
                              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(+) )users, 
       ( 
              SELECT t2.cpnt_survey_id cpnt_survey_id_2, 
                     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, 
                     t2.assessee_id        assessee_id_2, 
                     nvl(t1.mean_score, 0) mean_score 
              FROM   ( 
                              SELECT   pss.cpnt_survey_id cpnt_survey_id, 
                                       pss.item_completion_date, 
                                       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 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, 
                                                     pss.assessee_id 
                                     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 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 ) items, 
       ( 
              SELECT nvl(ps.lname, ' ') lname, 
                     nvl(ps.fname, ' ') fname, 
                     nvl(ps.mi, ' ')    mi, 
                     pss.cpnt_survey_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.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]  */ 
       )evaluators, 
       ( 
              SELECT t1.cpnt_survey_id cpnt_survey_id_1, 
                     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_page, 
                     t1.assessee_id              assessee_id_1, 
                     t2.cpnt_survey_id           cpnt_survey_id_2, 
                     t2.assessee_id              assessee_id_2 
              FROM   ( 
                              SELECT   pss.cpnt_survey_id cpnt_survey_id, 
                                       psq.section_id, 
                                       pssc.section_order, 
                                       pssc.section_title, 
                                       pssc.resource_type, 
                                       pss.assessee_id, 
                                       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 
                              GROUP BY (pss.cpnt_survey_id, pssc.section_order, psq.section_id, pssc.section_title,pssc.resource_type,pss.assessee_id) ) t1,
                     ( 
                              SELECT   pss.cpnt_survey_id cpnt_survey_id, 
                                       psq.section_id, 
                                       pss.assessee_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 
                              GROUP BY pss.cpnt_survey_id, 
                                       psq.section_id, 
                                       pss.assessee_id ) t2 
              WHERE  t1.cpnt_survey_id = t2.cpnt_survey_id(+) 
              AND    t1.section_id = t2.section_id(+) ) page_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      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 )question_details, 
       ( 
                       SELECT DISTINCT psr.stud_comments AS stud_comments, 
                                       pss.cpnt_survey_id, 
                                       psr.question_id, 
                                       pss.assessee_id, 
                                       pss.item_completion_date 
                       FROM            pa_cpnt_survey pcs, 
                                       pa_stud_survey pss, 
                                       pa_survey_responses psr, 
                                       pa_ssg_inst psi, 
                                       pa_ssg_locn psl 
                       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 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]  */ 
       )stud_comments, 
       ( 
              SELECT 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,
                     NULL                                                                                   AS rating,
                     NULL                                                                                   AS rating_label,
                     NULL                                                                                   AS choice_label,
                     pcs.cpnt_survey_id                                                                     AS cpnt_survey_id_label,
                     psq.section_id                                                                         AS section_id_label,
                     psq.question_id                                                                        AS question_id_label,
                     pss.assessee_id                                                                        AS assessee_id_label,
                     pss.item_completion_date                                                               AS item_completion_date_label,
                            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    psq.question_id = psr.question_id 
              AND    psq.question_type IN ('OPEN ENDED') 
              AND    inst.inst_id (+) = psr.inst_id 
              UNION 
              SELECT NULL AS stud_response_text , 
                     emp_label.employee_x, 
                     sup_label.supervisor_x, 
                     all_rating_labels.rating, ( 
                     CASE 
                            WHEN all_rating_labels.rating = 0 THEN 'N/A' 
                            ELSE all_rating_labels.rating_label 
                     END)                                   rating_label, 
                     NULL                                   AS choice_label, 
                     all_rating_labels.cpnt_survey_id       AS cpnt_survey_id_label, 
                     all_rating_labels.section_id           AS section_id_label, 
                     all_rating_labels.question_id          AS question_id_label, 
                     all_rating_labels.stud_id              AS stud_id_label, 
                     all_rating_labels.item_completion_date AS item_completion_date_label, 
                     all_rating_labels.instructor_name 
              FROM   ( 
                            SELECT prsl.rating, 
                                   prsl.rating_label, 
                                   pss.cpnt_survey_id, 
                                   pss.item_completion_date, 
                                   psq.section_id, 
                                   psq.question_id, 
                                   (decode(pss.rater_type_id, 
                                           'SUPERVISOR',pss.assessee_id, 
                                           pss.stud_id))                                                                  AS stud_id,
                                          concat((inst.fname), concat(' ', concat((inst.lname), concat(' ', (inst.mi)))))    instructor_name
                            FROM   pa_survey_responses psr, 
                                   pa_survey_question psq, 
                                   pa_stud_survey pss, 
                                   pa_cpnt_survey pcs, 
                                   pa_rating_scale_label prsl, 
                                   pa_inst inst 
                            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    inst.inst_id (+) = psr.inst_id 
                            UNION 
                            SELECT 0, 
                                   'N/A', 
                                   0, 
                                   NULL, 
                                   0,0, 
                                   NULL, 
                                   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, 
                                       pss.item_completion_date, 
                                       pss.assessee_id, ( 
                                       CASE 
                                                WHEN pss.rater_type_id IS NULL THEN '-' 
                                                ELSE 'X' 
                                       END)    supervisor_x, 
                                       NULL AS rating 
                              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' 
                              GROUP BY (pss.cpnt_survey_id, pss.rater_type_id, psq.question_id, psq.rating_scale_id, psq.section_id, psr.stud_response_value,pss.item_completion_date,pss.assessee_id) ) 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, 
                                       pss.item_completion_date, 
                                       pss.assessee_id, ( 
                                       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' 
                              GROUP BY (pss.cpnt_survey_id, pss.rater_type_id, psq.question_id, psq.rating_scale_id, psq.section_id, psr.stud_response_value,pss.item_completion_date,pss.assessee_id) ) 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)) 
              AND    all_rating_labels.cpnt_survey_id = sup_label.cpnt_survey_id(+) 
              AND    all_rating_labels.cpnt_survey_id = emp_label.cpnt_survey_id(+) 
              AND    all_rating_labels.item_completion_date = sup_label.item_completion_date(+)
              AND    all_rating_labels.item_completion_date = emp_label.item_completion_date(+)
              AND    all_rating_labels.section_id = sup_label.section_id(+) 
              AND    all_rating_labels.section_id = emp_label.section_id(+) 
              AND    all_rating_labels.question_id = sup_label.question_id(+) 
              AND    all_rating_labels.question_id = emp_label.question_id(+) 
              AND    all_rating_labels.stud_id = sup_label.assessee_id(+) 
              AND    all_rating_labels.stud_id = emp_label.assessee_id(+) 
              UNION 
                    ( 
                           SELECT NULL AS stud_response_text, ( 
                                  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, 
                                  NULL AS rating, 
                                  NULL AS rating_label, 
                                  t1.choice_label, 
                                  decode(t2.rater_type_id, 
                                         NULL,t3.cpnt_survey_id, 
                                         t2.cpnt_survey_id) AS cpnt_survey_id_label, 
                                  t1.section_id             AS section_id_label, 
                                  t1.question_id            AS question_id_label, 
                                  decode(t2.rater_type_id, 
                                         NULL,t3.assessee_id, 
                                         t2.assessee_id) AS assessee_id_label, 
                                  decode(t2.rater_type_id, 
                                         NULL,t3.item_completion_date, 
                                         t2.item_completion_date) AS item_completion_date_label,
                                  t2.instructor_name 
                           FROM   ( 
                                         SELECT pqac.choice_label, 
                                                pss.section_id, 
                                                psq.question_id 
                                         FROM   pa_question_answer_choices pqac, 
                                                pa_survey_question psq, 
                                                pa_questionnaire_survey pqs, 
                                                pa_survey_section pss 
                                         WHERE  pqs.survey_id = pss.survey_id 
                                         AND    pss.section_id = psq.section_id 
                                         AND    psq.question_id = pqac.question_id ) t1, 
                                  ( 
                                         SELECT pqac.choice_label choice_label, 
                                                pss.rater_type_id rater_type_id, 
                                                pcs.cpnt_survey_id, 
                                                pss.item_completion_date, 
                                                pss.assessee_id, 
                                                       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_question_answer_choices pqac, 
                                                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    psr.stud_response_value = pqac.answer_id 
                                         AND    inst.inst_id(+) = psr.inst_id ) t2, 
                                  ( 
                                         SELECT pqac.choice_label choice_label, 
                                                pss.rater_type_id rater_type_id, 
                                                pcs.cpnt_survey_id, 
                                                pss.item_completion_date, 
                                                pss.assessee_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    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 ) ) )labels 
WHERE  items.assessee_id_2 =users.stud_id 
AND    evaluators.cpnt_survey_id(+) =items.cpnt_survey_id_2 
AND    page_details.assessee_id_1 =users.stud_id 
AND    page_details.cpnt_survey_id_1 =items.cpnt_survey_id_2 
AND    question_details.cpnt_survey_id =items.cpnt_survey_id_2 
AND    question_details.section_id =page_details.section_id 
AND    stud_comments.cpnt_survey_id =items.cpnt_survey_id_2 
AND    stud_comments.assessee_id =users.stud_id 
AND    stud_comments.question_id =question_details.question_id 
AND    stud_comments.item_completion_date=items.item_completion_date 
AND    labels.assessee_id_label =users.stud_id 
AND    labels.cpnt_survey_id_label =items.cpnt_survey_id_2 
AND    labels.item_completion_date_label =items.item_completion_date 
AND    labels.question_id_label =question_details.question_id 
AND    labels.section_id_label =page_details.section_id

 

 

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

 

 

  • No labels