eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRFV_JOB_APPLICATIONS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,peo.full_name person_name ,vac.name vacancy_name ,app.date_received start_date ,nvl(app.date_end,hr_general.end_of_time) end_date ,app.current_employer current_employer ,app.projected_hire_date projected_hire_date ,DECODE(LEAST(lat.effective_end_date,TRUNC(sysdate)), lat.effective_end_date, HRI_OLTP_VIEW_ASG_STATUS_TYPES.get_asg_user_status(lat.assignment_type,peo.current_employee_flag, xT.user_status), NVL(yT.user_status,xT.user_status)) application_status ,DECODE(DECODE(LEAST(lat.effective_end_date,TRUNC(SYSDATE)), lat.effective_end_date, 'TERM_APL', NVL(y.per_system_status,x.per_system_status)),'ACTIVE_APL',1,0) new_applicants ,DECODE(DECODE(LEAST(lat.effective_end_date,TRUNC(SYSDATE)), lat.effective_end_date, 'TERM_APL', NVL(y.per_system_status,x.per_system_status)),'INTERVIEW1',1,0) interview1 ,DECODE(DECODE(LEAST(lat.effective_end_date,TRUNC(SYSDATE)), lat.effective_end_date, 'TERM_APL', NVL(y.per_system_status,x.per_system_status)),'INTERVIEW2',1,0) interview2 ,DECODE(DECODE(LEAST(lat.effective_end_date,TRUNC(sysdate)), lat.effective_end_date, DECODE(lat.assignment_type, 'A', 'TERM_APL', NVL(y.per_system_status, x.per_system_status)), NVL(y.per_system_status, x.per_system_status)) ,'TERM_APL', 1,0) terminations ,DECODE(DECODE(LEAST(lat.effective_end_date,TRUNC(SYSDATE)), lat.effective_end_date, 'TERM_APL', NVL(y.per_system_status,x.per_system_status)),'OFFER',1,0) offers ,DECODE(DECODE(LEAST(lat.effective_end_date,TRUNC(SYSDATE)), lat.effective_end_date, 'TERM_APL', NVL(y.per_system_status,x.per_system_status)),'ACCEPTED',1,0) accepts ,DECODE(lat.assignment_type, 'E', DECODE(GREATEST(NVL(pes.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')), TRUNC(SYSDATE)), TRUNC(SYSDATE),0,1), 0) still_current_employee ,hr_bis.bis_decode_lookup('YES_NO', DECODE(lat.assignment_type, 'E','Y','N')) hired ,DECODE(lat.assignment_type,'E','Y','N') hired_code ,hr_bis.bis_decode_lookup('YES_NO', DECODE(greatest(lat.effective_end_date, TRUNC(sysdate)), lat.effective_end_date, 'N', DECODE(lat.assignment_type,'E','N','Y'))) application_terminated ,hr_bis.bis_decode_lookup('YES_NO', NVL(peo.current_applicant_flag,'N')) current_applicant_flag ,peo.applicant_number applicant_number ,rac.name recruitment_activity_name ,ref.full_name referring_employee_name ,ref.employee_number referring_employee_number ,souT.name source_organization_name ,sup.full_name supervisor_name ,sup.employee_number supervisor_number ,DECODE(app.termination_reason, NULL, NULL, HR_BIS.BIS_DECODE_LOOKUP('TERM_APL_REASON',app.termination_reason)) termination_reason ,rec.full_name recruiter_name ,orgT.name organization_name ,pft.name position_name ,jbt.name job_name ,locT.location_code location_name ,gdt.name grade_name ,pgr.group_name people_group ,'_DF:PER:PER_ASSIGNMENTS:hir' ,'_DF:PER:PER_APPLICATIONS:app' ,lat.application_id application_id ,lat.assignment_id assignment_id ,lat.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,locT.location_id location_id ,hir.organization_id organization_id ,pgr.people_group_id people_group_id ,lat.person_id person_id ,pft.position_id position_id ,rec.person_id recruiter_id ,rac.recruitment_activity_id recruitment_activity_id ,ref.person_id referring_employee_id ,sup.person_id supervisor_id ,vac.vacancy_id vacancy_id FROM per_recruitment_activities rac ,per_ass_status_type_amends_tl yT ,(select * from per_ass_status_type_amends where business_group_id = hr_bis.get_sec_profile_bg_id) y ,per_assignment_status_types_tl xT ,per_assignment_status_types x ,per_periods_of_service pes ,hr_all_organization_units_tl souT ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,hr_all_positions_f_tl pft ,per_jobs_tl jbt ,hr_locations_all_tl locT ,per_grades_tl gdt ,per_all_vacancies vac ,pay_people_groups pgr ,per_applications app ,per_people_x rec ,per_people_x sup ,per_people_x ref ,per_people_x peo ,per_assignments_f lat ,per_assignments_f hir WHERE hir.organization_id = orgT.organization_id AND orgT.language (+) = userenv('LANG') AND hir.position_id = pft.position_id(+) AND pft.language (+) = userenv('LANG') AND hir.job_id = jbt.job_id(+) AND jbt.language (+) = userenv('LANG') AND hir.location_id = locT.location_id(+) AND locT.language (+) = userenv('LANG') AND hir.grade_id = gdt.grade_id(+) AND gdt.language (+) = userenv('LANG') AND hir.people_group_id = pgr.people_group_id(+) AND hir.vacancy_id = vac.vacancy_id(+) AND hir.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND hir.application_id = app.application_id AND hir.person_id = peo.person_id AND peo.applicant_number IS NOT NULL AND hir.source_organization_id = souT.organization_id (+) AND souT.language (+) = userenv('LANG') AND hir.assignment_id = lat.assignment_id AND lat.period_of_service_id = pes.period_of_service_id(+) AND lat.assignment_status_type_id = x.assignment_status_type_id AND x.assignment_status_type_id = xT.assignment_status_type_id AND xT.language = userenv('LANG') AND lat.assignment_status_type_id = y.assignment_status_type_id(+) AND y.ass_status_type_amend_id = yT.ass_status_type_amend_id(+) AND yT.language (+) = userenv('LANG') AND hir.person_referred_by_id = ref.person_id (+) AND hir.supervisor_id = sup.person_id (+) AND hir.recruiter_id = rec.person_id (+) AND hir.recruitment_activity_id = rac.recruitment_activity_id(+) AND hir.effective_start_date = ( SELECT MAX(a2.effective_start_date) FROM per_all_assignments_f a2 WHERE (a2.effective_start_date <= TRUNC(sysdate) AND a2.assignment_id = hir.assignment_id AND a2.assignment_type = 'A') ) AND lat.effective_start_date = ( SELECT MAX(a2.effective_start_date) FROM per_all_assignments_f a2 WHERE (a2.effective_start_date <= TRUNC(sysdate) AND a2.assignment_id = lat.assignment_id) ) AND app.date_received <= TRUNC(SYSDATE) AND NVL(x.business_group_id,peo.business_group_id) = peo.business_group_id AND hir.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, hir.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, VAC.NAME VACANCY_NAME
, APP.DATE_RECEIVED START_DATE
, NVL(APP.DATE_END
, HR_GENERAL.END_OF_TIME) END_DATE
, APP.CURRENT_EMPLOYER CURRENT_EMPLOYER
, APP.PROJECTED_HIRE_DATE PROJECTED_HIRE_DATE
, DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, HRI_OLTP_VIEW_ASG_STATUS_TYPES.GET_ASG_USER_STATUS(LAT.ASSIGNMENT_TYPE
, PEO.CURRENT_EMPLOYEE_FLAG
, XT.USER_STATUS)
, NVL(YT.USER_STATUS
, XT.USER_STATUS)) APPLICATION_STATUS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'ACTIVE_APL'
, 1
, 0) NEW_APPLICANTS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'INTERVIEW1'
, 1
, 0) INTERVIEW1
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'INTERVIEW2'
, 1
, 0) INTERVIEW2
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, DECODE(LAT.ASSIGNMENT_TYPE
, 'A'
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'TERM_APL'
, 1
, 0) TERMINATIONS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'OFFER'
, 1
, 0) OFFERS
, DECODE(DECODE(LEAST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'TERM_APL'
, NVL(Y.PER_SYSTEM_STATUS
, X.PER_SYSTEM_STATUS))
, 'ACCEPTED'
, 1
, 0) ACCEPTS
, DECODE(LAT.ASSIGNMENT_TYPE
, 'E'
, DECODE(GREATEST(NVL(PES.ACTUAL_TERMINATION_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 0
, 1)
, 0) STILL_CURRENT_EMPLOYEE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(LAT.ASSIGNMENT_TYPE
, 'E'
, 'Y'
, 'N')) HIRED
, DECODE(LAT.ASSIGNMENT_TYPE
, 'E'
, 'Y'
, 'N') HIRED_CODE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(GREATEST(LAT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
, LAT.EFFECTIVE_END_DATE
, 'N'
, DECODE(LAT.ASSIGNMENT_TYPE
, 'E'
, 'N'
, 'Y'))) APPLICATION_TERMINATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, RAC.NAME RECRUITMENT_ACTIVITY_NAME
, REF.FULL_NAME REFERRING_EMPLOYEE_NAME
, REF.EMPLOYEE_NUMBER REFERRING_EMPLOYEE_NUMBER
, SOUT.NAME SOURCE_ORGANIZATION_NAME
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, DECODE(APP.TERMINATION_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TERM_APL_REASON'
, APP.TERMINATION_REASON)) TERMINATION_REASON
, REC.FULL_NAME RECRUITER_NAME
, ORGT.NAME ORGANIZATION_NAME
, PFT.NAME POSITION_NAME
, JBT.NAME JOB_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, GDT.NAME GRADE_NAME
, PGR.GROUP_NAME PEOPLE_GROUP
, '_DF:PER:PER_ASSIGNMENTS:HIR'
, '_DF:PER:PER_APPLICATIONS:APP'
, LAT.APPLICATION_ID APPLICATION_ID
, LAT.ASSIGNMENT_ID ASSIGNMENT_ID
, LAT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, LOCT.LOCATION_ID LOCATION_ID
, HIR.ORGANIZATION_ID ORGANIZATION_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, LAT.PERSON_ID PERSON_ID
, PFT.POSITION_ID POSITION_ID
, REC.PERSON_ID RECRUITER_ID
, RAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, REF.PERSON_ID REFERRING_EMPLOYEE_ID
, SUP.PERSON_ID SUPERVISOR_ID
, VAC.VACANCY_ID VACANCY_ID
FROM PER_RECRUITMENT_ACTIVITIES RAC
, PER_ASS_STATUS_TYPE_AMENDS_TL YT
, (SELECT *
FROM PER_ASS_STATUS_TYPE_AMENDS
WHERE BUSINESS_GROUP_ID = HR_BIS.GET_SEC_PROFILE_BG_ID) Y
, PER_ASSIGNMENT_STATUS_TYPES_TL XT
, PER_ASSIGNMENT_STATUS_TYPES X
, PER_PERIODS_OF_SERVICE PES
, HR_ALL_ORGANIZATION_UNITS_TL SOUT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_POSITIONS_F_TL PFT
, PER_JOBS_TL JBT
, HR_LOCATIONS_ALL_TL LOCT
, PER_GRADES_TL GDT
, PER_ALL_VACANCIES VAC
, PAY_PEOPLE_GROUPS PGR
, PER_APPLICATIONS APP
, PER_PEOPLE_X REC
, PER_PEOPLE_X SUP
, PER_PEOPLE_X REF
, PER_PEOPLE_X PEO
, PER_ASSIGNMENTS_F LAT
, PER_ASSIGNMENTS_F HIR
WHERE HIR.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND HIR.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND HIR.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND HIR.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND HIR.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND HIR.PEOPLE_GROUP_ID = PGR.PEOPLE_GROUP_ID(+)
AND HIR.VACANCY_ID = VAC.VACANCY_ID(+)
AND HIR.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HIR.APPLICATION_ID = APP.APPLICATION_ID
AND HIR.PERSON_ID = PEO.PERSON_ID
AND PEO.APPLICANT_NUMBER IS NOT NULL
AND HIR.SOURCE_ORGANIZATION_ID = SOUT.ORGANIZATION_ID (+)
AND SOUT.LANGUAGE (+) = USERENV('LANG')
AND HIR.ASSIGNMENT_ID = LAT.ASSIGNMENT_ID
AND LAT.PERIOD_OF_SERVICE_ID = PES.PERIOD_OF_SERVICE_ID(+)
AND LAT.ASSIGNMENT_STATUS_TYPE_ID = X.ASSIGNMENT_STATUS_TYPE_ID
AND X.ASSIGNMENT_STATUS_TYPE_ID = XT.ASSIGNMENT_STATUS_TYPE_ID
AND XT.LANGUAGE = USERENV('LANG')
AND LAT.ASSIGNMENT_STATUS_TYPE_ID = Y.ASSIGNMENT_STATUS_TYPE_ID(+)
AND Y.ASS_STATUS_TYPE_AMEND_ID = YT.ASS_STATUS_TYPE_AMEND_ID(+)
AND YT.LANGUAGE (+) = USERENV('LANG')
AND HIR.PERSON_REFERRED_BY_ID = REF.PERSON_ID (+)
AND HIR.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND HIR.RECRUITER_ID = REC.PERSON_ID (+)
AND HIR.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID(+)
AND HIR.EFFECTIVE_START_DATE = ( SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND A2.ASSIGNMENT_ID = HIR.ASSIGNMENT_ID
AND A2.ASSIGNMENT_TYPE = 'A') )
AND LAT.EFFECTIVE_START_DATE = ( SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND A2.ASSIGNMENT_ID = LAT.ASSIGNMENT_ID) )
AND APP.DATE_RECEIVED <= TRUNC(SYSDATE)
AND NVL(X.BUSINESS_GROUP_ID
, PEO.BUSINESS_GROUP_ID) = PEO.BUSINESS_GROUP_ID
AND HIR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, HIR.BUSINESS_GROUP_ID) WITH READ ONLY