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.
<select name="qe01"> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> </select>
- HR_QUEST_ANSWERS > stores the answer ‘name’ from the HTML <select> tag
- HR_QUEST_ANSWER_VALUES > stores the values for the associated name
- 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.
The Main Appraiser Questionnaire 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
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.
You must log in to post a comment.