View: HR_EDWBV_WRK_RCTMNT_FCV

View Text

SELECT /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ 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
, 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 /* 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 /* 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 /**********************/ /* 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.ACCEPT_DATE
, TO_DATE(NULL)
, 0
, 1) ACCEPT_OCCURRED
, DECODE(REC.ACCEPT_SUCCESS
, -1
, 1
, 0) ACCEPT_FAIL_OCCURRED
, 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 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

Columns