Query to fetch Requisition details and Offer Details of Employee

 

SELECT ic.candidate_number,
       ppnfv.full_name candidate_name,
       irv.requisition_number,
       ppnf.full_name  rec_name,
       irv.title       req_name,
       iro.offer_id,
       iro.offer_name
FROM   irc_requisitions_vl irv,
       irc_submissions isub,
       per_person_names_f ppnf,
       per_person_names_f ppnfv,
       irc_candidates ic,
       irc_offers iro
WHERE  1 = 1
       AND irv.requisition_id (+) = isub.requisition_id
       AND isub.submission_id = iro.submission_id
       AND isub.person_id (+) = ic.person_id
       AND irv.recruiter_id = ppnf.person_id
       AND ppnf.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND
                                  ppnf.effective_end_date
       AND ic.person_id = ppnfv.person_id
       AND ppnfv.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN ppnfv.effective_start_date AND
                                  ppnfv.effective_end_date
       AND irv.object_status = 'ORA_ACTIVE'
ORDER  BY irv.requisition_number,
          ic.candidate_number