In this session, we are
going to see how to how to fetch Oracle Cloud HCM Performance Questionnaire data using SQL
SQL Text:
SQL Text:
with response_view as (
select ep.evaluation_id
,ep.person_id
,ep.role_type_code
,pq.qstnr_question_id
,pq.question_type
,pq.question_text question
,coalesce(qr.answer_text,qa.long_text) answer
from hra_eval_participants ep
,hrq_qstnr_participants qp
,hrq_eval_ptcpnt_questions_v epq
,hrq_ptcpnt_questions_v pq
,hrq_qstnr_latest_resps_v qnlr
,hrq_qstn_responses qr
,hrq_all_qstn_answers_v qa
where 1 = 1
and qp.participant_id = to_char(ep.eval_participant_id)
and epq.participant_id =qp.participant_id
and epq.subject_id = qp.subject_id
and pq.qstnr_section_id = epq.qstnr_section_id
and pq.qstnr_question_id = epq.qstnr_question_id
and qnlr.subject_id = to_char(ep.evaluation_id)
and qnlr.qstnr_participant_id = qp.qstnr_participant_id
and qr.qstnr_response_id = qnlr.qstnr_response_id
and qr.qstnr_question_id = pq.qstnr_question_id
and qa.qa_qstn_answer_id(+) = qr.qstn_answer_id
and pq.adhoc_qstn = 'N'
)
,worker_response_view as (
select he.worker_id worker_person_id
,rv.*
from hra_evaluations he
,response_view rv
where 1 = 1
and rv.evaluation_id = he.evaluation_id
and rv.person_id = he.worker_id
and rv.role_type_code = 'WORKER'
)
,manager_response_view as (
select he.worker_id worker_person_id
,rv.*
from hra_evaluations he
,response_view rv
where 1 = 1
and rv.evaluation_id = he.evaluation_id
and rv.person_id = he.manager_id
and rv.role_type_code = 'MANAGER'
)
select papf.person_number
,wrv.question
,wrv.answer worker_response
,mrv.answer manager_response
from worker_response_view wrv
,manager_response_view mrv
,per_all_people_f papf
where 1 = 1
and mrv.worker_person_id(+) = wrv.worker_person_id
and mrv.qstnr_question_id(+) = wrv.qstnr_question_id
and papf.person_id = wrv.worker_person_id
and sysdate between papf.effective_start_date and papf.effective_end_date
Comments
Post a Comment