with REQ_EFF as (SELECT jobfamilytranslationpeo.job_family_name Requisition_type
,irv.RECRUITING_TYPE_CODE
,irv.REQUISITION_NUMBER
,(SELECT pjt.name
FROM per_jobs_f pj
,per_jobs_f_tl pjt
WHERE pj.JOB_ID = irv.job_id
AND pj.job_id = pjt.job_id
AND sysdate between pjt.EFFECTIVE_START_DATE and pjt.EFFECTIVE_END_DATE
AND sysdate between pj.EFFECTIVE_START_DATE and pj.EFFECTIVE_END_DATE
AND pjt.LANGUAGE = USERENV('LANG')
) job_name
,ppnf.full_name cand_name
,phn.phone_number cand_phone_number
,email.email_address cand_email
,per.date_of_birth
,irv.open_date date_needed_by
,irv.attribute_char8 client
,irv.attribute_char7 project
,(hgeo.geography_element3 ||','|| hgeo.geography_element2 ||','|| hgeo.geography_element1) primary_location
,plt.location_name primary_work_location
-- ,(case when houft.organization_id = irv.LEGAL_EMPLOYER_ID then houft.name else null)
,ple.name legal_employer
,houft.name department_name
,fabu.bu_name business_unit
,DECODE(irv.WORKER_TYPE_CODE,'E','Employee','Contingent Worker') WORKER_TYPE_CODE
,irv.ATTRIBUTE_CHAR10 Worker_Category
,sub.SUBMISSION_ID
,(select PEI_INFORMATION15
from irc_ja_extra_info
where SUBMISSION_ID=sub.SUBMISSION_ID
AND PEI_INFORMATION_CATEGORY ='LOGISTICS' ) logistics_owned_by
,(select PEI_INFORMATION_DATE3
from irc_ja_extra_info
where SUBMISSION_ID=sub.SUBMISSION_ID
AND PEI_INFORMATION_CATEGORY ='XX_LOGISTICS' ) logistics_completion_date
,(select PEI_INFORMATION_DATE2
from irc_ja_extra_info
where SUBMISSION_ID=sub.SUBMISSION_ID
AND PEI_INFORMATION_CATEGORY ='Craft' )craft_mobilisation_date
,(select PEI_INFORMATION20
from irc_ja_extra_info
where SUBMISSION_ID=sub.SUBMISSION_ID
AND PEI_INFORMATION_CATEGORY ='Screening' )owned_by
,(select PEI_INFORMATION_DATE15
from irc_ja_extra_info
where SUBMISSION_ID=sub.SUBMISSION_ID
AND PEI_INFORMATION_CATEGORY ='xxx' )xx_raft_completion_date
,(select PEI_INFORMATION8
from irc_ja_extra_info
where SUBMISSION_ID=sub.SUBMISSION_ID
AND PEI_INFORMATION_CATEGORY ='Medical_Health'
AND PEI_INFORMATION1='Health (Internal)')medical_health
,(select PEI_INFORMATION_DATE3
from irc_ja_extra_info
where SUBMISSION_ID=sub.SUBMISSION_ID
AND PEI_INFORMATION_CATEGORY ='Medical_Health'
AND PEI_INFORMATION1='Health (Internal)')medical_completion_date
FROM irc_requisitions_vl irv
,irc_states_vl st
,irc_phases_vl ph
,irc_submissions sub
,irc_candidates cand
,per_person_names_f ppnf
,per_phones phn
,per_email_addresses email
,per_persons per
,irc_geo_hier_nodes igeo
,hz_geographies hgeo
,per_locations pl
,per_location_details_f plf
,per_location_details_f_tl plt
,per_job_family_f jobfamilypeo
,per_job_family_f_tl jobfamilytranslationpeo
,hr_organization_units_f_tl houft
,fun_all_business_units_v fabu
-- ,irc_ja_extra_info ijei
,per_legal_employers ple
WHERE 1=1
--AND irv.requisition_number = '297'
AND irv.requisition_id = sub.requisition_id
AND ph.phase_id = sub.current_phase_id
AND st.state_id = sub.current_state_id
AND sub.active_flag = 'Y'
AND cand.person_id = sub.person_id
AND ppnf.person_id = cand.person_id
AND ppnf.name_type = 'GLOBAL'
--AND cand.person_id in (300000147313216)
AND phn.phone_id(+) = cand.cand_phone_id
AND email.email_address_id(+) = cand.cand_email_id
AND per.person_id= cand.person_id
AND igeo.GEOGRAPHY_ID = hgeo.GEOGRAPHY_ID
AND irv.GEOGRAPHY_NODE_ID = igeo.GEOGRAPHY_NODE_ID
AND pl.location_id = irv.location_id
AND pl.location_id = plf.location_id
AND plt.LOCATION_DETAILS_ID = plf.LOCATION_DETAILS_ID
AND plt.LANGUAGE = USERENV('LANG')
AND JobFamilyPEO.JOB_FAMILY_ID = JobFamilyTranslationPEO.JOB_FAMILY_ID
AND JobFamilyTranslationPEO.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN JobFamilyPEO.EFFECTIVE_START_DATE AND JobFamilyPEO.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN JobFamilyTranslationPEO.EFFECTIVE_START_DATE AND JobFamilyTranslationPEO.EFFECTIVE_END_DATE
AND JobFamilyPEO.JOB_FAMILY_ID = irv.JOB_FAMILY_ID
AND houft.organization_id = irv.DEPARTMENT_ID
AND fabu.BU_ID = irv.BUSINESS_UNIT_ID
AND houft.LANGUAGE = USERENV('LANG')
AND ple.organization_id =irv.LEGAL_EMPLOYER_ID
AND ple.STATUS ='A'
AND (jobfamilytranslationpeo.job_family_name IN (:p_requisition_type) OR COALESCE(:p_requisition_type, NULL) IS NULL)
AND (irv.requisition_id IN (:p_requisition_number) OR COALESCE(:p_requisition_number, NULL) IS NULL)
AND (ppnf.full_name IN (:p_candidate_name) OR COALESCE(:p_candidate_name, NULL) IS NULL)
AND (irv.open_date IN (:p_date_need_by) OR COALESCE(:p_date_need_by, NULL) IS NULL)
AND (fabu.BU_ID IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)
AND (houft.organization_id IN (:p_department_id) OR COALESCE(:p_department_id, NULL) IS NULL)
AND (plt.location_name IN (:p_primary_location) OR COALESCE(:p_primary_location, NULL) IS NULL)
AND (ple.organization_id IN (:p_legal_employer) OR COALESCE(:p_legal_employer, NULL) IS NULL)
order by ppnf.full_name
)
SELECT Requisition_type
,RECRUITING_TYPE_CODE
,REQUISITION_NUMBER
,job_name
,cand_name
,cand_phone_number
,cand_email
,date_of_birth
,date_needed_by
,client
,project
,primary_location
,primary_work_location
,legal_employer
,department_name
,business_unit
,WORKER_TYPE_CODE
,Worker_Category
,SUBMISSION_ID
,logistics_owned_by
,logistics_completion_date
,screening_trade_and_craft_mobilisation_date
,screening_trade_and_craft_owned_by
,screening_trade_and_craft_completion_date
,medical_occupational_health_owned_by
,medical_occupational_health_completion_date
FROM REQ_EFF
WHERE 1=1
AND (logistics_completion_date IN (:p_logistics_completion_date) OR COALESCE(:p_logistics_completion_date, NULL) IS NULL)
AND (screening_trade_and_craft_completion_date IN (:p_screening_trade_and_craft_completiond_date) OR COALESCE(:p_screening_trade_and_craft_completiond_date, NULL) IS NULL)
AND (medical_occupational_health_completion_date IN (:p_medical_occupational_health_completion_date) OR COALESCE(:p_medical_occupational_health_completion_date, NULL) IS NULL)