Hello,
We currently have the below code written which displays answers entered during the recruitment process:
------------------------------------------------------------------
WITH req as (
SELECT
irb.requisition_number
,isu.submission_id
FROM
IRC_REQUISITIONS_B irb --requisitions
inner join IRC_SUBMISSIONS ISu--submission table
on isu.requisition_id=irb.requisition_id
WHERE
:p_requisition_number=irb.requisition_number
)
,singleans as (
select
question_code
,hqab.long_text
,participants.subject_id
from
hrq_qstnr_responses qstrnresponses
inner join hrq_qstnr_participants participants
on participants.qstnr_participant_id = qstrnresponses.qstnr_participant_id
AND participants.subject_id<>'PREVIEW'
inner join hrq_qstn_responses qsresponse
on qstrnresponses.qstnr_response_id=qsresponse.qstnr_response_id
inner join hrq_qstnr_questions hqq
on hqq.QSTNR_QUESTION_ID=qsresponse.QSTNR_QUESTION_ID
left outer join hrq_questions_b hqv
on hqv.question_id=hqq.question_id
and hqv.latest_version='Y'
and hqv.status='A'
inner join hrq_qstn_answers_VL hqab
on hqab.qstn_answer_id=qsresponse.qstn_answer_id
inner join req
on req.submission_id=participants.subject_id
where
question_code IN (
'LAT_PART_Legally_Auth'
,'300008106182600'
,'300008172881419'
,'300008105631537'
,'300008105631526'
)
)
,textans as (
select
question_code
,qsresponse.answer_text
,participants.subject_id
from
hrq_qstnr_responses qstrnresponses
inner join hrq_qstnr_participants participants
on participants.qstnr_participant_id = qstrnresponses.qstnr_participant_id
AND participants.subject_id<>'PREVIEW'
inner join hrq_qstn_responses qsresponse
on qstrnresponses.qstnr_response_id=qsresponse.qstnr_response_id
inner join hrq_qstnr_questions hqq
on hqq.QSTNR_QUESTION_ID=qsresponse.QSTNR_QUESTION_ID
left outer join hrq_questions_b hqv
on hqv.question_id=hqq.question_id
and hqv.latest_version='Y'
and hqv.status='A'
inner join req
on req.submission_id=participants.subject_id
where
question_code IN (
'GRAD_PrineWE_Opportunity'
,'GRAD_Interests'
,'300008296648349'
,'PM_PRIZES_AWARDS'
,'300008106182726'
,'300008106182716'
)
)
,other as (
select *
from singleans
union ALL
select * from textans
)
,results as (
select * from other
pivot (max(long_text) for question_code in
(
'LAT_PART_Legally_Auth'
,'300008106182600'
,'300008172881419'
,'300008105631537'
,'300008105631526'
,'GRAD_PrineWE_Opportunity'
,'GRAD_Interests'
,'300008296648349'
,'PM_PRIZES_AWARDS'
,'300008106182726'
,'300008106182716'
)
))
SELECT
irb.requisition_number
,ppnfc.first_name
,ppnfc.LAST_name
,icea.email_address candidate_email
,pjft.JOB_FAMILY_NAME
,irt.title requistion_title
,ipt.name Current_req_phase
,ic.CANDIDATE_NUMBER
,ic.creation_date Candidate_Creation_Date
,r_ppnf.display_name Recruiter
,loc.location_name
,LKUP2.MEANING Recruiting_type
,org.NAME Team
,irb.open_date
,isu.submission_date
,ist.name current_req_state
,istcand.name candidate_state
,results.*
,isu.submission_id
From
IRC_REQUISITIONS_B irb --requisitions
left outer join IRC_REQUISITIONS_TL irt
on irt.requisition_id=irb.requisition_id
inner join IRC_SUBMISSIONS ISu--submission table
on isu.requisition_id=irb.requisition_id
left outer join per_person_names_f ppnfc -- candidate name
on ppnfc.person_id = isu.person_id
AND ppnfc.name_type = 'GLOBAL' -- fetch the global name record only
AND sysdate BETWEEN ppnfc.effective_start_date AND ppnfc.effective_end_date
left outer join hr_all_organization_units_f_vl bu -- business unit
on bu.organization_id = irb.business_unit_id
AND sysdate BETWEEN bu.effective_start_date AND bu.effective_end_date
left outer join hr_locations_all_f_vl loc -- location
on loc.location_id = irb.location_id
AND sysdate BETWEEN loc.effective_start_date AND loc.effective_end_date
Left outer join hr_all_organization_units_f_vl org -- team
ON org.organization_id = irb.organization_id
AND sysdate BETWEEN org.effective_start_date AND org.effective_end_date
left outer join per_jobs_f_vl job -- jobs
on job.job_id = irb.job_id
AND sysdate BETWEEN job.effective_start_date AND job.effective_end_date
left outer join PER_JOB_FAMILY_F_TL pjft
on pjft.job_family_id=irb.job_family_id
and sysdate between pjft.effective_start_date and pjft.effective_end_date
Left outer join per_people_legislative_f pplf
ON pplf.person_id = isu.person_id
AND sysdate BETWEEN pplf.effective_start_date AND pplf.effective_end_date
left outer join HCM_LOOKUPS lkup
on irb.job_function_code=lkup.lookup_code
and lkup.lookup_type='JOB_FUNCTION_CODE'
left outer join HCM_LOOKUPS lkup2
on irb.recruiting_type_code=lkup2.lookup_code
and lkup2.lookup_type='ORA_IRC_RECRUITING_TYPE'
left outer join IRC_PHASES_TL ipt
on ipt.phase_id=isu.current_phase_id
left outer join IRC_CANDIDATES IC
on ic.person_id=isu.person_id
left outer join IRC_STATES_TL ist --requisition state
on ist.state_id = irb.current_state_id
left outer join IRC_STATES_TL istCand --candidate state
on istcand.state_id = isu.current_state_id
left outer join results
on results.subject_id=isu.submission_id
Left outer join per_person_names_f r_ppnf -- recruiter person name
ON r_ppnf.person_id = irb.RECRUITER_ID
AND r_ppnf.name_type = 'GLOBAL' -- fetch the global name record only
AND sysdate BETWEEN r_ppnf.effective_start_date AND r_ppnf.effective_end_date
left outer join IRC_CAND_EMAIL_ADDRESS_V icea
on icea.candidate_number=ic.candidate_number
where
(COALESCE(:p_requisition_number, NULL) IS NULL OR irb.requisition_number IN (:p_requisition_number))
------------------------------------------------------------------
Although, this does not display the entire text values on larger fields (getting max of 3968 characters displayed) and some responses are up to 4k in total.
Any ideas?