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.HR_EDWBV_WRK_RCTMNT_FCV

Source

View Text - Preformatted


SELECT NVL(to_char(rec.application_id),'NA_EDW') || '-' || rec.assignment_id || '-' || inst.instance_code recruitment_gain_pk ,peo.last_name ,rec.assignment_start_date /***********************/ /* Hidden Primary Keys */ /***********************/ ,rec.application_id application_id ,rec.assignment_id assignment_id ,rec.business_group_id business_group_id ,rec.person_id person_id /******************************/ /* Foreign Keys to Dimensions */ /******************************/ /* Age Band */ /************/ /* Use age band API with age in days at time of application */ ,edw_hr_age_band_pkg.age_band_fk ( months_between( NVL(rec.hire_date, NVL(rec.termination_date, NVL(rec.accept_date, NVL(rec.offer_date, NVL(rec.interview2_date, NVL(rec.interview1_date, rec.application_date)))))) ,peo.date_of_birth ) ) age_band_fk /* Assignment */ /**************/ /* Construct assignment_fk manually */ ,rec.assignment_id || '-' || inst.instance_code assignment_fk /* Geography */ /*************/ /* Construct geography_fk manually, providing an assignment location exists */ ,decode(asg.location_id, to_number(null),'NA_EDW', asg.location_id || '-' || inst.instance_code || '-' || 'HR_LOC') geography_fk /* Grade */ /*********/ /* Construct grade_fk manually providing an assignment grade exists */ ,decode(asg.grade_id, to_number(null),'NA_EDW', asg.grade_id || '-' || inst.instance_code) grade_fk /* Instance */ /************/ /* Standard EDW foreign key */ ,inst.instance_code instance_fk /* Job */ /*******/ /* Construct job_fk manually providing an assignment job exists */ ,decode(asg.job_id, to_number(null),'NA_EDW', asg.job_id || '-' || inst.instance_code) job_fk /* Organization (Assignment) */ /*****************************/ /* Construct organization_assignment manually, it is required for assignment */ ,asg.organization_id || '-' || inst.instance_code orgnztn_assgnmnt_fk /* Organization (Coordinating) */ /*******************************/ /* Construct coordinating_organization_fk manually if one exists */ ,decode(rac.run_by_organization_id, to_number(null),'NA_EDW', rac.run_by_organization_id || '-' || inst.instance_code) orgnztn_crdntng_fk /* Person (Applicant) */ /**********************/ /* Construct person_applicant_fk manually */ ,rec.person_id || '-' || inst.instance_code || '-EMPLOYEE-PERS' person_applicant_fk /* Person (Authoriser) */ /***********************/ /* Use person API to construct person_authoriser_fk */ ,decode(rac.authorising_person_id, to_number(NULL),'NA_EDW', rac.authorising_person_id || '-' || inst.instance_code || '-EMPLOYEE-PERS') person_authoriser_fk /* Person (Contact) */ /********************/ /* Use person API to construct person_contact_fk */ ,decode(rac.internal_contact_person_id, to_number(NULL),'NA_EDW', rac.internal_contact_person_id || '-' || inst.instance_code || '-EMPLOYEE-PERS') person_contact_fk /* Person (Recruiter) */ /**********************/ /* Use person API to construct person_recruiter_fk */ ,decode(vcs.recruiter_id, to_number(NULL),'NA_EDW', vcs.recruiter_id || '-' || inst.instance_code || '-EMPLOYEE-PERS') person_recruiter_fk /* Person (Originator) */ /**********************/ /* Use person API to construct person_recruiter_fk */ ,decode(rqn.person_id, to_number(NULL),'NA_EDW', rqn.person_id || '-' || inst.instance_code || '-EMPLOYEE-PERS') person_originator_fk /* Person Type */ /***************/ /* Use person type API to construct person_type_fk */ ,edw_hr_prsn_typ_pkg.person_type_fk ( rec.person_id , NVL(rec.separation_date,SYSDATE)) person_type_fk /* Position */ /************/ /* Construct position_fk manually, if an assignment position exists */ ,decode(asg.position_id, to_number(null),'NA_EDW', asg.position_id || '-' || inst.instance_code) position_fk ,rec.gain_type gain_type /* Workforce Movements */ /********************/ /* If the event occurred, use the find_movement_pk to compose the foreign key */ ,decode(rec.application_id, to_number(null), 'NA_EDW', hri_edw_fct_recruitment.find_movement_pk ( 'ACTIVE_APL', rec.gain_type, rec.application_success )) movement_application_fk ,decode(rec.interview1_date, to_date(null), 'NA_EDW', hri_edw_fct_recruitment.find_movement_pk ( 'INTERVIEW1', rec.gain_type, rec.interview1_success )) movement_interview1_fk ,decode(rec.interview2_date, to_date(null), 'NA_EDW', hri_edw_fct_recruitment.find_movement_pk ( 'INTERVIEW2', rec.gain_type, rec.interview2_success )) movement_interview2_fk ,decode(rec.offer_date, to_date(null), 'NA_EDW', hri_edw_fct_recruitment.find_movement_pk ( 'OFFER', rec.gain_type, rec.offer_success )) movement_offer_fk ,decode(rec.accept_date, to_date(null), 'NA_EDW', hri_edw_fct_recruitment.find_movement_pk ( 'ACCEPTED', rec.gain_type, rec.accept_success )) movement_accept_fk ,decode(rec.success, 1 , hri_edw_fct_recruitment.find_movement_pk( 'HIRE', rec.gain_type, 1 ), 'NA_EDW') movement_hire_fk ,decode(rec.success, -1 , hri_edw_fct_recruitment.find_movement_pk( 'TERM_APL', rec.gain_type, -1 ), 'NA_EDW') movement_termination_fk ,decode(rec.separation_date, to_date(null),'NA_EDW', hri_edw_fct_recruitment.find_movement_pk( 'END_EMP', rec.gain_type, 1 )) movement_emplymnt_end_fk ,hri_edw_fct_recruitment.find_movement_pk(rec.current_stage, rec.gain_type, rec.success) movement_crrnt_stage_fk /* Reason */ /**********/ ,decode(rec.application_reason, null, 'NA_EDW', 'APL_ASSIGN_REASON-' || rec.application_reason || '-' || inst.instance_code) reason_application_fk ,decode(rec.interview1_reason, null, 'NA_EDW', 'APL_ASSIGN_REASON-' || rec.interview1_reason || '-' || inst.instance_code) reason_interview1_fk ,decode(rec.interview2_reason, null, 'NA_EDW', 'APL_ASSIGN_REASON-' || rec.interview2_reason || '-' || inst.instance_code) reason_interview2_fk ,decode(rec.offer_reason, null, 'NA_EDW', 'APL_ASSIGN_REASON-' || rec.offer_reason || '-' || inst.instance_code) reason_offer_fk ,decode(rec.accept_reason, null, 'NA_EDW', 'APL_ASSIGN_REASON-' || rec.accept_reason || '-' || inst.instance_code) reason_accept_fk ,decode(rec.hire_reason, null, 'NA_EDW', 'EMP_ASSIGN_REASON-' || rec.hire_reason || '-' || inst.instance_code) reason_hire_fk ,decode(rec.termination_reason, null, 'NA_EDW', 'TERM_APL_REASON-' || rec.termination_reason || '-' || inst.instance_code) reason_termination_fk ,decode(rec.separation_reason, null, 'NA_EDW', 'LEAV_REAS-' || rec.separation_reason || '-' || inst.instance_code) reason_emplymnt_end_fk ,decode(rec.current_stage_reason, null, 'NA_EDW', decode(rec.current_stage, 'HIRE','EMP_ASSIGN_REASON-' || rec.hire_reason, 'TERM_APL','TERM_APL_REASON-' || rec.termination_reason, 'END_EMP','LEAV_REAS-' || rec.separation_reason, 'APL_ASSIGN_REASON-' || rec.current_stage_reason) || '-' || inst.instance_code) reason_crrnt_stage_fk /* Requisition Vacancy */ /***********************/ /* Use vacancy API to construct requisition_vacancy_fk */ ,edw_hr_rqn_vcncy_pkg.vacancy_fk(asg.vacancy_id) requisition_vacancy_fk /* Recruitment Activity */ /************************/ /* Use recruitment activity API to construct recruitment_activity_fk */ ,edw_hr_rec_act_pkg.recruitment_activity_fk ( asg.recruitment_activity_id) recruitment_activity_fk /* Service Band */ /****************/ /* Use service band API to construct service_band_fk for successful applicants */ /* length of service is calculated as the difference between employment end */ /* date (or sysdate if employee has not been terminated) and the employment */ /* start (which for applicants is the day after the application ended) */ ,decode(rec.success, 1,edw_hr_service_pkg.service_band_fk ( nvl(rec.separation_date,SYSDATE) - rec.hire_date), 'NA_EDW') service_band_fk /* Time (Application) */ /**********************/ /* Construct time_fk manually if the relevant stage was reached */ ,decode(rec.application_id, to_number(null),'NA_EDW', to_char(NVL(rec.application_date,rec.assignment_start_date),'dd-mm-yyyy') ||'-DAY') time_application_fk ,decode(rec.interview1_date, to_date(null),'NA_EDW', to_char(rec.interview1_date,'dd-mm-yyyy')||'-DAY') time_interview1_fk ,decode(rec.interview2_date, to_date(null),'NA_EDW', to_char(rec.interview2_date,'dd-mm-yyyy')||'-DAY') time_interview2_fk ,decode(rec.offer_date, to_date(null),'NA_EDW', to_char(rec.offer_date,'dd-mm-yyyy')||'-DAY') time_offer_fk ,decode(rec.accept_date, to_date(null),'NA_EDW', to_char(rec.accept_date,'dd-mm-yyyy')||'-DAY') time_accepted_fk ,decode(rec.success, 1, to_char(rec.hire_date,'dd-mm-yyyy')||'-DAY', 'NA_EDW') time_hire_fk ,decode(rec.success, -1,to_char(rec.termination_date,'dd-mm-yyyy')||'-DAY', 'NA_EDW') time_terminated_fk ,decode(rec.separation_date, to_date(null),'NA_EDW', to_char(rec.separation_date,'dd-mm-yyyy')||'-DAY') time_employment_ended_fk ,decode(rec.current_stage_date, to_date(null),'NA_EDW', to_char(rec.current_stage_date,'dd-mm-yyyy')||'-DAY') time_crrnt_stage_fk /**********************/ /* Regular Attributes */ /**********************/ ,rec.application_date application_start_date ,rec.application_end_date application_end_date ,rec.hire_date hire_date ,planned_start_date planned_start_date ,rec.creation_date creation_date ,NVL(rec.last_update_date, to_date('01-01-2000','DD-MM-YYYY')) last_update_date ,hri_edw_fct_recruitment.calc_abv (rec.assignment_id, asg.business_group_id, 'HEAD', NVL(rec.hire_date, NVL(rec.termination_date, NVL(rec.accept_date, NVL(rec.offer_date, NVL(rec.interview2_date, NVL(rec.interview1_date, rec.application_date))))))) applicant_headcount ,hri_edw_fct_recruitment.calc_abv (rec.assignment_id, asg.business_group_id, 'FTE', NVL(rec.hire_date, NVL(rec.termination_date, NVL(rec.accept_date, NVL(rec.offer_date, NVL(rec.interview2_date, NVL(rec.interview1_date, rec.application_date))))))) applicant_fte ,rec.interview1_date-rec.application_date days_to_interview1 ,rec.interview2_date-rec.application_date days_to_interview2 ,rec.offer_date-rec.application_date days_to_offer ,rec.accept_date-rec.application_date days_to_accept ,rec.hire_date - rec.application_date days_to_hire ,rec.termination_date-rec.application_date days_to_term_apl ,rec.separation_date-rec.application_date days_to_end_emp ,decode(rec.application_date,to_date(null),0,1) application_occurred ,decode(rec.application_success, 0, 1, 0) application_result_pending ,decode(rec.application_success, 1, 1, 0) application_pass_occurred ,decode(rec.application_success,-1, 1, 0) application_fail_occurred ,decode(rec.interview1_date,to_date(null),0,1) interview1_occurred ,decode(rec.interview1_success, 0, 1, 0) interview1_result_pending ,decode(rec.interview1_success, 1, 1, 0) interview1_pass_occurred ,decode(rec.interview1_success,-1, 1, 0) interview1_fail_occurred ,decode(rec.interview2_date,to_date(null),0,1) interview2_occurred ,decode(rec.interview2_success, 0, 1, 0) interview2_result_pending ,decode(rec.interview2_success, 1, 1, 0) interview2_pass_occurred ,decode(rec.interview2_success,-1, 1, 0) interview2_fail_occurred ,decode(rec.offer_date,to_date(null),0,1) offer_occurred ,decode(rec.offer_success,-1, 1, 0) offer_fail_occurred ,decode(rec.success, 1, 0, -1, 0, decode(rec.offer_date, to_date(null), 0, decode(rec.accept_date, to_date(null), 1, 0))) offer_result_pending ,decode(rec.accept_date,to_date(null),0,1) accept_occurred ,decode(rec.accept_success,-1, 1, 0) accept_fail_occurred ,decode(rec.success, 1, 0, -1, 0, decode(rec.accept_date, to_date(null), 0, 1)) accept_result_pending ,decode(rec.hire_date,to_date(null),0,1) hire_occurred ,decode(rec.termination_date,to_date(null),0,1) application_terminated ,decode(rec.separation_date,to_date(null),0,1) end_emp_occurred ,decode(rec.success,0,0,1) finished_value /******************************************************************************/ FROM hri_recruitment_stages rec ,per_all_assignments_f asg ,per_all_people_f peo ,edw_local_instance inst ,per_recruitment_activities rac ,per_all_vacancies vcs ,per_requisitions rqn WHERE asg.assignment_id = rec.assignment_id AND asg.effective_start_date = rec.assignment_start_date AND rec.assignment_start_date <= SYSDATE AND asg.person_id = peo.person_id AND vcs.vacancy_id (+) = asg.vacancy_id AND rqn.requisition_id (+) = vcs.requisition_id AND rac.recruitment_activity_id (+) = asg.recruitment_activity_id AND asg.effective_start_date BETWEEN peo.effective_start_date AND peo.effective_end_date

View Text - HTML Formatted

SELECT NVL(TO_CHAR(REC.APPLICATION_ID)
, 'NA_EDW') || '-' || REC.ASSIGNMENT_ID || '-' || INST.INSTANCE_CODE RECRUITMENT_GAIN_PK
, PEO.LAST_NAME
, REC.ASSIGNMENT_START_DATE /***********************/ /* HIDDEN PRIMARY KEYS */ /***********************/
, REC.APPLICATION_ID APPLICATION_ID
, REC.ASSIGNMENT_ID ASSIGNMENT_ID
, REC.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, REC.PERSON_ID PERSON_ID /******************************/ /* FOREIGN KEYS TO DIMENSIONS */ /******************************/ /* AGE BAND */ /************/ /* USE AGE BAND API WITH AGE IN DAYS AT TIME OF APPLICATION */
, EDW_HR_AGE_BAND_PKG.AGE_BAND_FK ( MONTHS_BETWEEN( NVL(REC.HIRE_DATE
, NVL(REC.TERMINATION_DATE
, NVL(REC.ACCEPT_DATE
, NVL(REC.OFFER_DATE
, NVL(REC.INTERVIEW2_DATE
, NVL(REC.INTERVIEW1_DATE
, REC.APPLICATION_DATE))))))
, PEO.DATE_OF_BIRTH ) ) AGE_BAND_FK /* ASSIGNMENT */ /**************/ /* CONSTRUCT ASSIGNMENT_FK MANUALLY */
, REC.ASSIGNMENT_ID || '-' || INST.INSTANCE_CODE ASSIGNMENT_FK /* GEOGRAPHY */ /*************/ /* CONSTRUCT GEOGRAPHY_FK MANUALLY
, PROVIDING AN ASSIGNMENT LOCATION EXISTS */
, DECODE(ASG.LOCATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.LOCATION_ID || '-' || INST.INSTANCE_CODE || '-' || 'HR_LOC') GEOGRAPHY_FK /* GRADE */ /*********/ /* CONSTRUCT GRADE_FK MANUALLY PROVIDING AN ASSIGNMENT GRADE EXISTS */
, DECODE(ASG.GRADE_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.GRADE_ID || '-' || INST.INSTANCE_CODE) GRADE_FK /* INSTANCE */ /************/ /* STANDARD EDW FOREIGN KEY */
, INST.INSTANCE_CODE INSTANCE_FK /* JOB */ /*******/ /* CONSTRUCT JOB_FK MANUALLY PROVIDING AN ASSIGNMENT JOB EXISTS */
, DECODE(ASG.JOB_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.JOB_ID || '-' || INST.INSTANCE_CODE) JOB_FK /* ORGANIZATION (ASSIGNMENT) */ /*****************************/ /* CONSTRUCT ORGANIZATION_ASSIGNMENT MANUALLY
, IT IS REQUIRED FOR ASSIGNMENT */
, ASG.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE ORGNZTN_ASSGNMNT_FK /* ORGANIZATION (COORDINATING) */ /*******************************/ /* CONSTRUCT COORDINATING_ORGANIZATION_FK MANUALLY IF ONE EXISTS */
, DECODE(RAC.RUN_BY_ORGANIZATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, RAC.RUN_BY_ORGANIZATION_ID || '-' || INST.INSTANCE_CODE) ORGNZTN_CRDNTNG_FK /* PERSON (APPLICANT) */ /**********************/ /* CONSTRUCT PERSON_APPLICANT_FK MANUALLY */
, REC.PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS' PERSON_APPLICANT_FK /* PERSON (AUTHORISER) */ /***********************/ /* USE PERSON API TO CONSTRUCT PERSON_AUTHORISER_FK */
, DECODE(RAC.AUTHORISING_PERSON_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, RAC.AUTHORISING_PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS') PERSON_AUTHORISER_FK /* PERSON (CONTACT) */ /********************/ /* USE PERSON API TO CONSTRUCT PERSON_CONTACT_FK */
, DECODE(RAC.INTERNAL_CONTACT_PERSON_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, RAC.INTERNAL_CONTACT_PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS') PERSON_CONTACT_FK /* PERSON (RECRUITER) */ /**********************/ /* USE PERSON API TO CONSTRUCT PERSON_RECRUITER_FK */
, DECODE(VCS.RECRUITER_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, VCS.RECRUITER_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS') PERSON_RECRUITER_FK /* PERSON (ORIGINATOR) */ /**********************/ /* USE PERSON API TO CONSTRUCT PERSON_RECRUITER_FK */
, DECODE(RQN.PERSON_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, RQN.PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS') PERSON_ORIGINATOR_FK /* PERSON TYPE */ /***************/ /* USE PERSON TYPE API TO CONSTRUCT PERSON_TYPE_FK */
, EDW_HR_PRSN_TYP_PKG.PERSON_TYPE_FK ( REC.PERSON_ID
, NVL(REC.SEPARATION_DATE
, SYSDATE)) PERSON_TYPE_FK /* POSITION */ /************/ /* CONSTRUCT POSITION_FK MANUALLY
, IF AN ASSIGNMENT POSITION EXISTS */
, DECODE(ASG.POSITION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.POSITION_ID || '-' || INST.INSTANCE_CODE) POSITION_FK
, REC.GAIN_TYPE GAIN_TYPE /* WORKFORCE MOVEMENTS */ /********************/ /* IF THE EVENT OCCURRED
, USE THE FIND_MOVEMENT_PK TO COMPOSE THE FOREIGN KEY */
, DECODE(REC.APPLICATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK ( 'ACTIVE_APL'
, REC.GAIN_TYPE
, REC.APPLICATION_SUCCESS )) MOVEMENT_APPLICATION_FK
, DECODE(REC.INTERVIEW1_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK ( 'INTERVIEW1'
, REC.GAIN_TYPE
, REC.INTERVIEW1_SUCCESS )) MOVEMENT_INTERVIEW1_FK
, DECODE(REC.INTERVIEW2_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK ( 'INTERVIEW2'
, REC.GAIN_TYPE
, REC.INTERVIEW2_SUCCESS )) MOVEMENT_INTERVIEW2_FK
, DECODE(REC.OFFER_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK ( 'OFFER'
, REC.GAIN_TYPE
, REC.OFFER_SUCCESS )) MOVEMENT_OFFER_FK
, DECODE(REC.ACCEPT_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK ( 'ACCEPTED'
, REC.GAIN_TYPE
, REC.ACCEPT_SUCCESS )) MOVEMENT_ACCEPT_FK
, DECODE(REC.SUCCESS
, 1
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK( 'HIRE'
, REC.GAIN_TYPE
, 1 )
, 'NA_EDW') MOVEMENT_HIRE_FK
, DECODE(REC.SUCCESS
, -1
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK( 'TERM_APL'
, REC.GAIN_TYPE
, -1 )
, 'NA_EDW') MOVEMENT_TERMINATION_FK
, DECODE(REC.SEPARATION_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK( 'END_EMP'
, REC.GAIN_TYPE
, 1 )) MOVEMENT_EMPLYMNT_END_FK
, HRI_EDW_FCT_RECRUITMENT.FIND_MOVEMENT_PK(REC.CURRENT_STAGE
, REC.GAIN_TYPE
, REC.SUCCESS) MOVEMENT_CRRNT_STAGE_FK /* REASON */ /**********/
, DECODE(REC.APPLICATION_REASON
, NULL
, 'NA_EDW'
, 'APL_ASSIGN_REASON-' || REC.APPLICATION_REASON || '-' || INST.INSTANCE_CODE) REASON_APPLICATION_FK
, DECODE(REC.INTERVIEW1_REASON
, NULL
, 'NA_EDW'
, 'APL_ASSIGN_REASON-' || REC.INTERVIEW1_REASON || '-' || INST.INSTANCE_CODE) REASON_INTERVIEW1_FK
, DECODE(REC.INTERVIEW2_REASON
, NULL
, 'NA_EDW'
, 'APL_ASSIGN_REASON-' || REC.INTERVIEW2_REASON || '-' || INST.INSTANCE_CODE) REASON_INTERVIEW2_FK
, DECODE(REC.OFFER_REASON
, NULL
, 'NA_EDW'
, 'APL_ASSIGN_REASON-' || REC.OFFER_REASON || '-' || INST.INSTANCE_CODE) REASON_OFFER_FK
, DECODE(REC.ACCEPT_REASON
, NULL
, 'NA_EDW'
, 'APL_ASSIGN_REASON-' || REC.ACCEPT_REASON || '-' || INST.INSTANCE_CODE) REASON_ACCEPT_FK
, DECODE(REC.HIRE_REASON
, NULL
, 'NA_EDW'
, 'EMP_ASSIGN_REASON-' || REC.HIRE_REASON || '-' || INST.INSTANCE_CODE) REASON_HIRE_FK
, DECODE(REC.TERMINATION_REASON
, NULL
, 'NA_EDW'
, 'TERM_APL_REASON-' || REC.TERMINATION_REASON || '-' || INST.INSTANCE_CODE) REASON_TERMINATION_FK
, DECODE(REC.SEPARATION_REASON
, NULL
, 'NA_EDW'
, 'LEAV_REAS-' || REC.SEPARATION_REASON || '-' || INST.INSTANCE_CODE) REASON_EMPLYMNT_END_FK
, DECODE(REC.CURRENT_STAGE_REASON
, NULL
, 'NA_EDW'
, DECODE(REC.CURRENT_STAGE
, 'HIRE'
, 'EMP_ASSIGN_REASON-' || REC.HIRE_REASON
, 'TERM_APL'
, 'TERM_APL_REASON-' || REC.TERMINATION_REASON
, 'END_EMP'
, 'LEAV_REAS-' || REC.SEPARATION_REASON
, 'APL_ASSIGN_REASON-' || REC.CURRENT_STAGE_REASON) || '-' || INST.INSTANCE_CODE) REASON_CRRNT_STAGE_FK /* REQUISITION VACANCY */ /***********************/ /* USE VACANCY API TO CONSTRUCT REQUISITION_VACANCY_FK */
, EDW_HR_RQN_VCNCY_PKG.VACANCY_FK(ASG.VACANCY_ID) REQUISITION_VACANCY_FK /* RECRUITMENT ACTIVITY */ /************************/ /* USE RECRUITMENT ACTIVITY API TO CONSTRUCT RECRUITMENT_ACTIVITY_FK */
, EDW_HR_REC_ACT_PKG.RECRUITMENT_ACTIVITY_FK ( ASG.RECRUITMENT_ACTIVITY_ID) RECRUITMENT_ACTIVITY_FK /* SERVICE BAND */ /****************/ /* USE SERVICE BAND API TO CONSTRUCT SERVICE_BAND_FK FOR SUCCESSFUL APPLICANTS */ /* LENGTH OF SERVICE IS CALCULATED AS THE DIFFERENCE BETWEEN EMPLOYMENT END */ /* DATE (OR SYSDATE IF EMPLOYEE HAS NOT BEEN TERMINATED)
AND THE EMPLOYMENT */ /* START (WHICH FOR APPLICANTS IS THE DAY AFTER THE APPLICATION ENDED) */
, DECODE(REC.SUCCESS
, 1
, EDW_HR_SERVICE_PKG.SERVICE_BAND_FK ( NVL(REC.SEPARATION_DATE
, SYSDATE) - REC.HIRE_DATE)
, 'NA_EDW') SERVICE_BAND_FK /* TIME (APPLICATION) */ /**********************/ /* CONSTRUCT TIME_FK MANUALLY IF THE RELEVANT STAGE WAS REACHED */
, DECODE(REC.APPLICATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, TO_CHAR(NVL(REC.APPLICATION_DATE
, REC.ASSIGNMENT_START_DATE)
, 'DD-MM-YYYY') ||'-DAY') TIME_APPLICATION_FK
, DECODE(REC.INTERVIEW1_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(REC.INTERVIEW1_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_INTERVIEW1_FK
, DECODE(REC.INTERVIEW2_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(REC.INTERVIEW2_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_INTERVIEW2_FK
, DECODE(REC.OFFER_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(REC.OFFER_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_OFFER_FK
, DECODE(REC.ACCEPT_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(REC.ACCEPT_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_ACCEPTED_FK
, DECODE(REC.SUCCESS
, 1
, TO_CHAR(REC.HIRE_DATE
, 'DD-MM-YYYY')||'-DAY'
, 'NA_EDW') TIME_HIRE_FK
, DECODE(REC.SUCCESS
, -1
, TO_CHAR(REC.TERMINATION_DATE
, 'DD-MM-YYYY')||'-DAY'
, 'NA_EDW') TIME_TERMINATED_FK
, DECODE(REC.SEPARATION_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(REC.SEPARATION_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_EMPLOYMENT_ENDED_FK
, DECODE(REC.CURRENT_STAGE_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(REC.CURRENT_STAGE_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_CRRNT_STAGE_FK /**********************/ /* REGULAR ATTRIBUTES */ /**********************/
, REC.APPLICATION_DATE APPLICATION_START_DATE
, REC.APPLICATION_END_DATE APPLICATION_END_DATE
, REC.HIRE_DATE HIRE_DATE
, PLANNED_START_DATE PLANNED_START_DATE
, REC.CREATION_DATE CREATION_DATE
, NVL(REC.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY')) LAST_UPDATE_DATE
, HRI_EDW_FCT_RECRUITMENT.CALC_ABV (REC.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, 'HEAD'
, NVL(REC.HIRE_DATE
, NVL(REC.TERMINATION_DATE
, NVL(REC.ACCEPT_DATE
, NVL(REC.OFFER_DATE
, NVL(REC.INTERVIEW2_DATE
, NVL(REC.INTERVIEW1_DATE
, REC.APPLICATION_DATE))))))) APPLICANT_HEADCOUNT
, HRI_EDW_FCT_RECRUITMENT.CALC_ABV (REC.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, 'FTE'
, NVL(REC.HIRE_DATE
, NVL(REC.TERMINATION_DATE
, NVL(REC.ACCEPT_DATE
, NVL(REC.OFFER_DATE
, NVL(REC.INTERVIEW2_DATE
, NVL(REC.INTERVIEW1_DATE
, REC.APPLICATION_DATE))))))) APPLICANT_FTE
, REC.INTERVIEW1_DATE-REC.APPLICATION_DATE DAYS_TO_INTERVIEW1
, REC.INTERVIEW2_DATE-REC.APPLICATION_DATE DAYS_TO_INTERVIEW2
, REC.OFFER_DATE-REC.APPLICATION_DATE DAYS_TO_OFFER
, REC.ACCEPT_DATE-REC.APPLICATION_DATE DAYS_TO_ACCEPT
, REC.HIRE_DATE - REC.APPLICATION_DATE DAYS_TO_HIRE
, REC.TERMINATION_DATE-REC.APPLICATION_DATE DAYS_TO_TERM_APL
, REC.SEPARATION_DATE-REC.APPLICATION_DATE DAYS_TO_END_EMP
, DECODE(REC.APPLICATION_DATE
, TO_DATE(NULL)
, 0
, 1) APPLICATION_OCCURRED
, DECODE(REC.APPLICATION_SUCCESS
, 0
, 1
, 0) APPLICATION_RESULT_PENDING
, DECODE(REC.APPLICATION_SUCCESS
, 1
, 1
, 0) APPLICATION_PASS_OCCURRED
, DECODE(REC.APPLICATION_SUCCESS
, -1
, 1
, 0) APPLICATION_FAIL_OCCURRED
, DECODE(REC.INTERVIEW1_DATE
, TO_DATE(NULL)
, 0
, 1) INTERVIEW1_OCCURRED
, DECODE(REC.INTERVIEW1_SUCCESS
, 0
, 1
, 0) INTERVIEW1_RESULT_PENDING
, DECODE(REC.INTERVIEW1_SUCCESS
, 1
, 1
, 0) INTERVIEW1_PASS_OCCURRED
, DECODE(REC.INTERVIEW1_SUCCESS
, -1
, 1
, 0) INTERVIEW1_FAIL_OCCURRED
, DECODE(REC.INTERVIEW2_DATE
, TO_DATE(NULL)
, 0
, 1) INTERVIEW2_OCCURRED
, DECODE(REC.INTERVIEW2_SUCCESS
, 0
, 1
, 0) INTERVIEW2_RESULT_PENDING
, DECODE(REC.INTERVIEW2_SUCCESS
, 1
, 1
, 0) INTERVIEW2_PASS_OCCURRED
, DECODE(REC.INTERVIEW2_SUCCESS
, -1
, 1
, 0) INTERVIEW2_FAIL_OCCURRED
, DECODE(REC.OFFER_DATE
, TO_DATE(NULL)
, 0
, 1) OFFER_OCCURRED
, DECODE(REC.OFFER_SUCCESS
, -1
, 1
, 0) OFFER_FAIL_OCCURRED
, DECODE(REC.SUCCESS
, 1
, 0
, -1
, 0
, DECODE(REC.OFFER_DATE
, TO_DATE(NULL)
, 0
, DECODE(REC.ACCEPT_DATE
, TO_DATE(NULL)
, 1
, 0))) OFFER_RESULT_PENDING
, DECODE(REC.ACCEPT_DATE
, TO_DATE(NULL)
, 0
, 1) ACCEPT_OCCURRED
, DECODE(REC.ACCEPT_SUCCESS
, -1
, 1
, 0) ACCEPT_FAIL_OCCURRED
, DECODE(REC.SUCCESS
, 1
, 0
, -1
, 0
, DECODE(REC.ACCEPT_DATE
, TO_DATE(NULL)
, 0
, 1)) ACCEPT_RESULT_PENDING
, DECODE(REC.HIRE_DATE
, TO_DATE(NULL)
, 0
, 1) HIRE_OCCURRED
, DECODE(REC.TERMINATION_DATE
, TO_DATE(NULL)
, 0
, 1) APPLICATION_TERMINATED
, DECODE(REC.SEPARATION_DATE
, TO_DATE(NULL)
, 0
, 1) END_EMP_OCCURRED
, DECODE(REC.SUCCESS
, 0
, 0
, 1) FINISHED_VALUE /******************************************************************************/
FROM HRI_RECRUITMENT_STAGES REC
, PER_ALL_ASSIGNMENTS_F ASG
, PER_ALL_PEOPLE_F PEO
, EDW_LOCAL_INSTANCE INST
, PER_RECRUITMENT_ACTIVITIES RAC
, PER_ALL_VACANCIES VCS
, PER_REQUISITIONS RQN
WHERE ASG.ASSIGNMENT_ID = REC.ASSIGNMENT_ID
AND ASG.EFFECTIVE_START_DATE = REC.ASSIGNMENT_START_DATE
AND REC.ASSIGNMENT_START_DATE <= SYSDATE
AND ASG.PERSON_ID = PEO.PERSON_ID
AND VCS.VACANCY_ID (+) = ASG.VACANCY_ID
AND RQN.REQUISITION_ID (+) = VCS.REQUISITION_ID
AND RAC.RECRUITMENT_ACTIVITY_ID (+) = ASG.RECRUITMENT_ACTIVITY_ID
AND ASG.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE