VIEW: APPS.IRCFV_JOB_MATCHING_PREFERENCES

Source

View Text - Preformatted

SELECT bgT.name business_group_name ,vac.name vacancy_name ,hr_bis.bis_decode_lookup('YES_NO',isc.employee) suitable_for_employee ,hr_bis.bis_decode_lookup('YES_NO',isc.contractor) suitable_for_contractor ,hr_bis.bis_decode_lookup('IRC_EMP_CAT',isc.employment_category) employment_status ,hr_bis.bis_decode_lookup('IRC_TRAVEL_PERCENTAGE', isc.travel_percentage) travel_percentage ,isc.min_salary min_salary ,isc.max_salary max_salary ,curT.name salary_currency ,hr_bis.bis_decode_lookup('PAY_BASIS',isc.salary_period) salary_period ,hr_bis.bis_decode_lookup('IRC_PROFESSIONAL_AREA',isc.professional_area) professional_area ,hr_bis.bis_decode_lookup('IRC_WORK_AT_HOME',isc.work_at_home) work_at_home ,qal1.name minimum_qualification_level ,qal2.name maximum_qualification_level /* flexfields */ ,'_DF:PER:IRC_SEARCH_CRITERIA:isc' ,'_DF:PER:IRC_SEARCH_CRITERIA_DDF:isc' /* system codes */ ,isc.employee suitable_for_employee_code ,isc.contractor suitable_for_contractor_code ,isc.employment_category employment_category_code ,isc.travel_percentage travel_percentage_code ,isc.professional_area professional_area_code ,isc.work_at_home work_at_home_code ,isc.salary_period pay_basis_code ,isc.salary_currency salary_currency_code /* system ids */ ,isc.search_criteria_id search_criteria_id ,isc.object_id vacancy_id ,vac.business_group_id business_group_id ,vac.organization_id organization_id ,vac.job_id job_id ,vac.grade_id grade_id ,vac.position_id position_id ,vac.location_id location_id ,vac.requisition_id requisition_id ,vac.people_group_id people_group_id ,vac.recruiter_id recruiter_id ,vac.manager_id manager_id FROM irc_search_criteria isc, per_vacancies vac, hr_all_organization_units_tl bgT, fnd_currencies_tl curT, per_qualification_types qal1, per_qualification_types qal2 WHERE isc.object_type = 'VACANCY' AND isc.object_id = vac.vacancy_id AND vac.business_group_id = bgT.organization_id AND bgt.language = USERENV('LANG') AND isc.salary_currency = curT.currency_code (+) AND curT.language = USERENV('LANG') AND isc.min_qual_level = qal1.qualification_type_id (+) AND isc.max_qual_level = qal2.qualification_type_id (+) AND vac.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, vac.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGT.NAME BUSINESS_GROUP_NAME
, VAC.NAME VACANCY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ISC.EMPLOYEE) SUITABLE_FOR_EMPLOYEE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ISC.CONTRACTOR) SUITABLE_FOR_CONTRACTOR
, HR_BIS.BIS_DECODE_LOOKUP('IRC_EMP_CAT'
, ISC.EMPLOYMENT_CATEGORY) EMPLOYMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('IRC_TRAVEL_PERCENTAGE'
, ISC.TRAVEL_PERCENTAGE) TRAVEL_PERCENTAGE
, ISC.MIN_SALARY MIN_SALARY
, ISC.MAX_SALARY MAX_SALARY
, CURT.NAME SALARY_CURRENCY
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, ISC.SALARY_PERIOD) SALARY_PERIOD
, HR_BIS.BIS_DECODE_LOOKUP('IRC_PROFESSIONAL_AREA'
, ISC.PROFESSIONAL_AREA) PROFESSIONAL_AREA
, HR_BIS.BIS_DECODE_LOOKUP('IRC_WORK_AT_HOME'
, ISC.WORK_AT_HOME) WORK_AT_HOME
, QAL1.NAME MINIMUM_QUALIFICATION_LEVEL
, QAL2.NAME MAXIMUM_QUALIFICATION_LEVEL /* FLEXFIELDS */
, '_DF:PER:IRC_SEARCH_CRITERIA:ISC'
, '_DF:PER:IRC_SEARCH_CRITERIA_DDF:ISC' /* SYSTEM CODES */
, ISC.EMPLOYEE SUITABLE_FOR_EMPLOYEE_CODE
, ISC.CONTRACTOR SUITABLE_FOR_CONTRACTOR_CODE
, ISC.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ISC.TRAVEL_PERCENTAGE TRAVEL_PERCENTAGE_CODE
, ISC.PROFESSIONAL_AREA PROFESSIONAL_AREA_CODE
, ISC.WORK_AT_HOME WORK_AT_HOME_CODE
, ISC.SALARY_PERIOD PAY_BASIS_CODE
, ISC.SALARY_CURRENCY SALARY_CURRENCY_CODE /* SYSTEM IDS */
, ISC.SEARCH_CRITERIA_ID SEARCH_CRITERIA_ID
, ISC.OBJECT_ID VACANCY_ID
, VAC.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, VAC.ORGANIZATION_ID ORGANIZATION_ID
, VAC.JOB_ID JOB_ID
, VAC.GRADE_ID GRADE_ID
, VAC.POSITION_ID POSITION_ID
, VAC.LOCATION_ID LOCATION_ID
, VAC.REQUISITION_ID REQUISITION_ID
, VAC.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, VAC.RECRUITER_ID RECRUITER_ID
, VAC.MANAGER_ID MANAGER_ID
FROM IRC_SEARCH_CRITERIA ISC
, PER_VACANCIES VAC
, HR_ALL_ORGANIZATION_UNITS_TL BGT
, FND_CURRENCIES_TL CURT
, PER_QUALIFICATION_TYPES QAL1
, PER_QUALIFICATION_TYPES QAL2
WHERE ISC.OBJECT_TYPE = 'VACANCY'
AND ISC.OBJECT_ID = VAC.VACANCY_ID
AND VAC.BUSINESS_GROUP_ID = BGT.ORGANIZATION_ID
AND BGT.LANGUAGE = USERENV('LANG')
AND ISC.SALARY_CURRENCY = CURT.CURRENCY_CODE (+)
AND CURT.LANGUAGE = USERENV('LANG')
AND ISC.MIN_QUAL_LEVEL = QAL1.QUALIFICATION_TYPE_ID (+)
AND ISC.MAX_QUAL_LEVEL = QAL2.QUALIFICATION_TYPE_ID (+)
AND VAC.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, VAC.BUSINESS_GROUP_ID) WITH READ ONLY