VIEW: APPS.IRCFV_CANDIDATE_EMPLYMNT_HSTRY

Source

View Text - Preformatted

SELECT par.person_name candidate_name ,ppe.employer_name employer_name ,ppe.start_date employer_start_date ,ppe.end_date employer_end_date ,NVL(ppe.start_date, hr_general.start_of_time) employer_start_date_nn ,NVL(ppe.end_date, hr_general.end_of_time) employer_end_date_nn ,ppe.period_years employer_period_years ,ppe.period_months employer_period_months ,ppe.period_days employer_period_days ,ppj.job_name job_title ,ppj.start_date job_start_date ,ppj.end_date job_end_date ,ppj.period_years job_period_years ,ppj.period_months job_period_months ,ppj.period_days job_period_days ,ftr.territory_short_name employer_country ,ppe.employer_address employer_address ,hr_bis.bis_decode_lookup('PREV_EMP_TYPE',ppe.employer_type) employer_type ,hr_bis.bis_decode_lookup('PREV_EMP_SUBTYPE',ppe.employer_subtype) employer_subtype ,hr_bis.bis_decode_lookup('EMPLOYEE_CATG',ppj.employment_category) employment_category ,ppe.description employer_description ,ppj.description job_description ,'_DF:PER:PER_PREVIOUS_EMPLOYERS:ppe' ,'_DF:PER:Previous Employer Developer DF:ppe' ,'_DF:PER:PER_PREVIOUS_JOBS:ppj' ,'_DF:PER:Previous Job Developer DF:ppj' ,ppe.employer_country employer_country_code ,ppe.employer_type employer_type_code ,ppe.employer_subtype employer_subtype_code ,ppj.employment_category employment_category_code ,ppe.previous_employer_id previous_employer_id ,ppe.business_group_id business_group_id ,ppe.person_id person_id ,ppe.party_id party_id FROM per_previous_employers ppe , per_previous_jobs ppj , irc_notification_preferences inp , hz_person_profiles par , fnd_territories_vl ftr WHERE ppe.previous_employer_id = ppj.previous_employer_id AND ppe.party_id = inp.party_id AND inp.allow_access = 'Y' AND inp.party_id = par.party_id AND ftr.territory_code(+) = ppe.employer_country AND trunc(sysdate) between NVL(par.effective_start_date, trunc(sysdate)) and NVL(par.effective_end_date ,sysdate) WITH READ ONLY

View Text - HTML Formatted

SELECT PAR.PERSON_NAME CANDIDATE_NAME
, PPE.EMPLOYER_NAME EMPLOYER_NAME
, PPE.START_DATE EMPLOYER_START_DATE
, PPE.END_DATE EMPLOYER_END_DATE
, NVL(PPE.START_DATE
, HR_GENERAL.START_OF_TIME) EMPLOYER_START_DATE_NN
, NVL(PPE.END_DATE
, HR_GENERAL.END_OF_TIME) EMPLOYER_END_DATE_NN
, PPE.PERIOD_YEARS EMPLOYER_PERIOD_YEARS
, PPE.PERIOD_MONTHS EMPLOYER_PERIOD_MONTHS
, PPE.PERIOD_DAYS EMPLOYER_PERIOD_DAYS
, PPJ.JOB_NAME JOB_TITLE
, PPJ.START_DATE JOB_START_DATE
, PPJ.END_DATE JOB_END_DATE
, PPJ.PERIOD_YEARS JOB_PERIOD_YEARS
, PPJ.PERIOD_MONTHS JOB_PERIOD_MONTHS
, PPJ.PERIOD_DAYS JOB_PERIOD_DAYS
, FTR.TERRITORY_SHORT_NAME EMPLOYER_COUNTRY
, PPE.EMPLOYER_ADDRESS EMPLOYER_ADDRESS
, HR_BIS.BIS_DECODE_LOOKUP('PREV_EMP_TYPE'
, PPE.EMPLOYER_TYPE) EMPLOYER_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('PREV_EMP_SUBTYPE'
, PPE.EMPLOYER_SUBTYPE) EMPLOYER_SUBTYPE
, HR_BIS.BIS_DECODE_LOOKUP('EMPLOYEE_CATG'
, PPJ.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, PPE.DESCRIPTION EMPLOYER_DESCRIPTION
, PPJ.DESCRIPTION JOB_DESCRIPTION
, '_DF:PER:PER_PREVIOUS_EMPLOYERS:PPE'
, '_DF:PER:PREVIOUS EMPLOYER DEVELOPER DF:PPE'
, '_DF:PER:PER_PREVIOUS_JOBS:PPJ'
, '_DF:PER:PREVIOUS JOB DEVELOPER DF:PPJ'
, PPE.EMPLOYER_COUNTRY EMPLOYER_COUNTRY_CODE
, PPE.EMPLOYER_TYPE EMPLOYER_TYPE_CODE
, PPE.EMPLOYER_SUBTYPE EMPLOYER_SUBTYPE_CODE
, PPJ.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, PPE.PREVIOUS_EMPLOYER_ID PREVIOUS_EMPLOYER_ID
, PPE.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPE.PERSON_ID PERSON_ID
, PPE.PARTY_ID PARTY_ID
FROM PER_PREVIOUS_EMPLOYERS PPE
, PER_PREVIOUS_JOBS PPJ
, IRC_NOTIFICATION_PREFERENCES INP
, HZ_PERSON_PROFILES PAR
, FND_TERRITORIES_VL FTR
WHERE PPE.PREVIOUS_EMPLOYER_ID = PPJ.PREVIOUS_EMPLOYER_ID
AND PPE.PARTY_ID = INP.PARTY_ID
AND INP.ALLOW_ACCESS = 'Y'
AND INP.PARTY_ID = PAR.PARTY_ID
AND FTR.TERRITORY_CODE(+) = PPE.EMPLOYER_COUNTRY
AND TRUNC(SYSDATE) BETWEEN NVL(PAR.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PAR.EFFECTIVE_END_DATE
, SYSDATE) WITH READ ONLY