VIEW: APPS.HRI_MB_APL_ACTIVITY_V

Source

View Text - Preformatted

SELECT DECODE( hri_opl_recruitment.get_stage_date (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'HIRE') , to_date(null), 1, 0) current_applicant_indicator ,asg.applicant_rank applicant_rank ,apl.date_received application_date ,hri_opl_recruitment.get_stage_date (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'INTERVIEW1') interview1_date ,hri_opl_recruitment.get_stage_date (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'INTERVIEW2') interview2_date ,hri_opl_recruitment.get_stage_date (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'OFFER') offer_date ,hri_opl_recruitment.get_stage_date (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'ACCEPTED') accepted_date ,apl.date_end end_date ,hri_opl_recruitment.get_stage_date (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'CURRENT') current_stage_date ,apl.projected_hire_date projected_hire_date ,hri_opl_recruitment.get_stage_date (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'HIRE') hire_date ,hri_opl_recruitment.get_stage_status (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'ACTIVE_APL') application_status_code ,hri_opl_recruitment.get_stage_status (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'INTERVIEW1') interview1_status_code ,hri_opl_recruitment.get_stage_status (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'INTERVIEW2') interview2_status_code ,hri_opl_recruitment.get_stage_status (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'OFFER') offer_status_code ,hri_opl_recruitment.get_stage_status (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'ACCEPTED') accepted_status_code ,hri_opl_recruitment.get_stage_status (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'END') end_status_code ,hri_opl_recruitment.get_stage_status (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'CURRENT') current_status_code ,hri_opl_recruitment.get_stage_reason (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'ACTIVE_APL') application_reason ,hri_opl_recruitment.get_stage_reason (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'INTERVIEW1') interview1_reason ,hri_opl_recruitment.get_stage_reason (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'INTERVIEW2') interview2_reason ,hri_opl_recruitment.get_stage_reason (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'OFFER') offer_reason ,hri_opl_recruitment.get_stage_reason (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'ACCEPTED') accepted_reason ,hri_opl_recruitment.get_stage_reason (asg.assignment_id, asg.person_id, apl.termination_reason, apl.date_end, 'HIRE') hire_reason ,apl.termination_reason termination_reason ,hri_bpl_person_type.get_emp_system_type(trunc(sysdate), asg.person_id) current_emp_system_type ,hri_bpl_person_type.get_emp_system_type(apl.date_received, asg.person_id) appl_emp_system_type ,asg.job_post_source_name job_source_name ,asg.source_type job_source_type_code ,asg.application_id application_id ,asg.assignment_id assignment_id ,asg.business_group_id business_group_id ,NVL(asg.grade_id,-1) grade_id ,NVL(asg.job_id,-1) job_id ,NVL(asg.location_id,-1) location_id ,asg.organization_id organization_id ,NVL(asg.people_group_id,-1) people_group_id ,asg.person_id person_id ,NVL(asg.position_id,-1) position_id ,NVL(asg.posting_content_id,-1) posting_content_id ,NVL(asg.recruiter_id,-1) recruiter_id ,NVL(asg.recruitment_activity_id,-1) recruitment_activity_id ,NVL(asg.person_referred_by_id,-1) referred_by_person_id ,NVL(asg.supervisor_id,-1) supervisor_id ,NVL(asg.vacancy_id,-1) vacancy_id ,asg.vacancy_id fk_vacancy_id ,NVL(asg.vendor_id,-1) vendor_id FROM per_applications apl ,per_all_assignments_f asg WHERE asg.application_id = apl.application_id AND asg.assignment_type = 'A' AND apl.date_received <= trunc(sysdate) AND asg.effective_start_date = (SELECT max(effective_start_date) FROM per_all_assignments_f asg2 WHERE asg2.assignment_id = asg.assignment_id AND asg2.assignment_type = 'A') WITH READ ONLY

View Text - HTML Formatted

SELECT DECODE( HRI_OPL_RECRUITMENT.GET_STAGE_DATE (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'HIRE')
, TO_DATE(NULL)
, 1
, 0) CURRENT_APPLICANT_INDICATOR
, ASG.APPLICANT_RANK APPLICANT_RANK
, APL.DATE_RECEIVED APPLICATION_DATE
, HRI_OPL_RECRUITMENT.GET_STAGE_DATE (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'INTERVIEW1') INTERVIEW1_DATE
, HRI_OPL_RECRUITMENT.GET_STAGE_DATE (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'INTERVIEW2') INTERVIEW2_DATE
, HRI_OPL_RECRUITMENT.GET_STAGE_DATE (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'OFFER') OFFER_DATE
, HRI_OPL_RECRUITMENT.GET_STAGE_DATE (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'ACCEPTED') ACCEPTED_DATE
, APL.DATE_END END_DATE
, HRI_OPL_RECRUITMENT.GET_STAGE_DATE (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'CURRENT') CURRENT_STAGE_DATE
, APL.PROJECTED_HIRE_DATE PROJECTED_HIRE_DATE
, HRI_OPL_RECRUITMENT.GET_STAGE_DATE (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'HIRE') HIRE_DATE
, HRI_OPL_RECRUITMENT.GET_STAGE_STATUS (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'ACTIVE_APL') APPLICATION_STATUS_CODE
, HRI_OPL_RECRUITMENT.GET_STAGE_STATUS (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'INTERVIEW1') INTERVIEW1_STATUS_CODE
, HRI_OPL_RECRUITMENT.GET_STAGE_STATUS (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'INTERVIEW2') INTERVIEW2_STATUS_CODE
, HRI_OPL_RECRUITMENT.GET_STAGE_STATUS (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'OFFER') OFFER_STATUS_CODE
, HRI_OPL_RECRUITMENT.GET_STAGE_STATUS (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'ACCEPTED') ACCEPTED_STATUS_CODE
, HRI_OPL_RECRUITMENT.GET_STAGE_STATUS (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'END') END_STATUS_CODE
, HRI_OPL_RECRUITMENT.GET_STAGE_STATUS (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'CURRENT') CURRENT_STATUS_CODE
, HRI_OPL_RECRUITMENT.GET_STAGE_REASON (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'ACTIVE_APL') APPLICATION_REASON
, HRI_OPL_RECRUITMENT.GET_STAGE_REASON (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'INTERVIEW1') INTERVIEW1_REASON
, HRI_OPL_RECRUITMENT.GET_STAGE_REASON (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'INTERVIEW2') INTERVIEW2_REASON
, HRI_OPL_RECRUITMENT.GET_STAGE_REASON (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'OFFER') OFFER_REASON
, HRI_OPL_RECRUITMENT.GET_STAGE_REASON (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'ACCEPTED') ACCEPTED_REASON
, HRI_OPL_RECRUITMENT.GET_STAGE_REASON (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, APL.TERMINATION_REASON
, APL.DATE_END
, 'HIRE') HIRE_REASON
, APL.TERMINATION_REASON TERMINATION_REASON
, HRI_BPL_PERSON_TYPE.GET_EMP_SYSTEM_TYPE(TRUNC(SYSDATE)
, ASG.PERSON_ID) CURRENT_EMP_SYSTEM_TYPE
, HRI_BPL_PERSON_TYPE.GET_EMP_SYSTEM_TYPE(APL.DATE_RECEIVED
, ASG.PERSON_ID) APPL_EMP_SYSTEM_TYPE
, ASG.JOB_POST_SOURCE_NAME JOB_SOURCE_NAME
, ASG.SOURCE_TYPE JOB_SOURCE_TYPE_CODE
, ASG.APPLICATION_ID APPLICATION_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, NVL(ASG.GRADE_ID
, -1) GRADE_ID
, NVL(ASG.JOB_ID
, -1) JOB_ID
, NVL(ASG.LOCATION_ID
, -1) LOCATION_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, NVL(ASG.PEOPLE_GROUP_ID
, -1) PEOPLE_GROUP_ID
, ASG.PERSON_ID PERSON_ID
, NVL(ASG.POSITION_ID
, -1) POSITION_ID
, NVL(ASG.POSTING_CONTENT_ID
, -1) POSTING_CONTENT_ID
, NVL(ASG.RECRUITER_ID
, -1) RECRUITER_ID
, NVL(ASG.RECRUITMENT_ACTIVITY_ID
, -1) RECRUITMENT_ACTIVITY_ID
, NVL(ASG.PERSON_REFERRED_BY_ID
, -1) REFERRED_BY_PERSON_ID
, NVL(ASG.SUPERVISOR_ID
, -1) SUPERVISOR_ID
, NVL(ASG.VACANCY_ID
, -1) VACANCY_ID
, ASG.VACANCY_ID FK_VACANCY_ID
, NVL(ASG.VENDOR_ID
, -1) VENDOR_ID
FROM PER_APPLICATIONS APL
, PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.APPLICATION_ID = APL.APPLICATION_ID
AND ASG.ASSIGNMENT_TYPE = 'A'
AND APL.DATE_RECEIVED <= TRUNC(SYSDATE)
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG2.ASSIGNMENT_TYPE = 'A') WITH READ ONLY