Skip to main content

How to Get Questionnaire Data from an Appraisal in Oracle SSHR

Mark Pescatrice Oracle HCM
Picture of Carmen Sandiegor
Where in the world are my questionnaire answers?

The appraisal function in Oracle E-Business Suite Self-Service HR, like many other parts of EBS, is very good at capturing lots of data, but not very good at letting it go.  The questionnaire, which is an optional component of the appraisal function within SSHR, can be used to collect data using standard HTML form functions, but retrieving the data can appear to be impossible, if you do not know where to look.  It’s kind of like finding Carmen Sandiego. The actual design of questionnaires is outside of the scope of this article, but I will reference some of the HTML used.

This article applies to both 11i and R12.

Data is collected on the questionnaire using standard HTML form controls, such as short text, long text, radio buttons, and lists.  This is an example of an HTML <select> tag that is used for a list:

<select name="qe01">
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
</select>
…which looks like this on the questionnaire page in your browser:
HTML List
To keep things interesting, Oracle stores the answers in, not one, but two tables, joined by questionnaire_answer_id:
  • HR_QUEST_ANSWERS > stores the answer ‘name’ from the HTML <select> tag
  • HR_QUEST_ANSWER_VALUES > stores the values for the associated name
To make things even more exciting, Oracle also does the following:
  • Main Appraiser and Participant Appraiser(s) questionnaire data is stored differently, thus requiring more than one query;
  • The names of the fields are different between tables, which is not unheard of, but confusing nonetheless.
Are we having fun yet? Just follow the bouncing ball… I will start with the main appraiser query.

The Main Appraiser Questionnaire Query

To figure this out, I had to put on my detective’s hat and figure out all of the joins.  This was difficult, as the names were not always the same between fields.  The eTRM on the MOS site was of little help, nor were the view objects within SSHR, so it was a matter of trial and error.  I started mapping everything and came up with this (click to make it bigger):
Appraisal Questionnaire Answer Table Schema
Once I figured this out, it was easy to write the query.  I want to note that the hr_questionnaires and per_appraisal_templates tables are optional, however I included them as I needed to reference the questionnaires and template by name, not just ID (primary keys).  To make things easier for myself, I created a function that would return just the value based on the appraisal_id (p_appraisal_id), the HTML input name (p_name), and the appraisal template name (p_template_name).  The function is based on this query:
    select hqav.value
    from hr_quest_answers hqa ,
         hr_quest_answer_values hqav ,
         hr_quest_fields hqf ,
         per_appraisals pa ,
         per_participants pp,
         hr_questionnaires hq,
         per_appraisal_templates pat
    where pa.appraisal_id = p_appraisal_id
        and hqf.name = p_name
        and pp.participation_in_id = pa.appraisal_id
        and pp.participant_id = hqa.type_object_id
        and hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
        and hqav.field_id = hqf.field_id
        and hq.questionnaire_template_id = pat.ma_quest_template_id
        and pat.appraisal_template_id = pa.appraisal_template_id
        and pat.name = p_template_name;

The Appraisee (Employee) Questionnaire Query

This is where it gets really strange. The join between per_appraisals and hr_quest_answers uses the appraisal_id in per_appraisals, which is the unique identifier for the appraisal, joined to type_object_id.
Appraisee Questionnaire Answer Table SchemaThe hr_quest_answers table is joined to two different tables, depending on the type of questionnaire.  Normally this would not be a problem, but this is a key relationship.  I’m sure the developers behind this had good intentions, but the design is mind boggling.

Moving right along….just like the main appraiser, I created a function to get the value. This is the query:

    select hqav.value
    from hr_quest_answers hqa ,
       hr_quest_answer_values hqav ,
       hr_questionnaires hq ,
       hr_quest_fields hqf ,
       per_appraisal_templates pat ,
       per_appraisals pa 
    where pa.appraisal_id = p_appraisal_id
       and hqf.name = p_name
       and hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
       and hqa.questionnaire_template_id = hq.questionnaire_template_id
       and hq.questionnaire_template_id = pat.questionnaire_template_id
       and hqf.field_id = hqav.field_id
       and hqa.type_object_id = pa.appraisal_id
       and pat.appraisal_template_id = pa.appraisal_template_id
       and pat.name = p_template_name;

Epilogue

Once I figured out the relationships and the joins, the rest was pretty easy.  I created a view that uses the functions and brings in other data, such as appraisee name, main appraiser name, org, and other other information.  The above queries are really the starting point for getting the data you need. I know this is a long article, but I hope you find this information useful.