VIEW: APPS.HR_EDWBV_WRK_SPRTN_FCV

Source

View Text - Preformatted

SELECT /**************************************************************************/ /* Primary Key of Fact */ /**************************************************************************/ /**/ pps.period_of_service_id||'-'||pps.person_id|| '-'||pasg.assignment_id|| '-'||instance_code separation_pk /**/ /**************************************************************************/ /* Foreign Keys to Dimensions */ /**************************************************************************/ /**/ /**************/ /* Assignment */ /**************/ /* Construct assignment_fk manually */ /**/ ,pasg.assignment_id || '-' || instance_code assignment_fk /**/ /************/ /* 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(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date, sysdate)))) , peo.date_of_birth)) age_band_fk /**/ /****************/ /* Service Band */ /****************/ /* Use service band API to construct service_band_fk */ /**/ /* length of service is calculated as the difference */ /* between snp.snapshot_date */ /* and the employment start */ /**/ ,edw_hr_service_pkg.service_band_fk( NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date, sysdate)))) - pps.date_start) service_band_fk /**/ /*************/ /* Geography */ /*************/ /* Construct geography_fk manually, providing an assignment location exists */ /**/ ,decode(pasg.location_id, to_number(null), 'NA_EDW', pasg.location_id || '-' || instance_code || '-' || 'HR_LOC') geography_fk /**/ /********* /* Grade */ /*********/ /* Construct grade_fk manually providing an assignment grade exists */ /**/ ,decode(pasg.grade_id, to_number(null),'NA_EDW', pasg.grade_id || '-' || instance_code) grade_fk /**/ /************/ /* Instance */ /************/ /* Standard EDW foreign key */ ,instance_code instance_fk /**/ /*******/ /* Job */ /*******/ /* Construct job_fk manually providing an assignment job exists */ /**/ ,decode(pasg.job_id, to_number(null),'NA_EDW', pasg.job_id || '-' || instance_code) job_fk /**/ /****************/ /* Organization */ /****************/ /* Construct organization fk manually */ /**/ ,pasg.organization_id || '-' || instance_code organization_fk /**/ /**********/ /* Person */ /**********/ /* Construct person_fk manually */ /**/ ,pps.person_id || '-' || instance_code || '-EMPLOYEE-PERS' person_fk /**/ /***************/ /* Person Type */ /***************/ /* Use person type API to construct person_type_fk */ ,edw_hr_prsn_typ_pkg.person_type_fk ( pasg.person_id, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date, sysdate))))) person_type_fk /**/ /************/ /* Position */ /************/ /* Construct position_fk manually, if an assignment position exists */ /**/ ,decode(pasg.position_id, to_number(null),'NA_EDW', pasg.position_id || '-' || instance_code) position_fk /**/ /**********************/ /* Time (Application) */ /**********************/ /* Construct time_trmntn_ntfd_fk manually */ /**/ ,decode(pps.notified_termination_date, to_date(null), 'NA_EDW', to_char(pps.notified_termination_date,'dd-mm-yyyy')||'-DAY') time_trm_ntfd_fk /* Construct time_emplymnt_strt_fk manually */ /**/ ,decode(pps.date_start, to_date(null), 'NA_EDW', to_char(pps.date_start,'dd-mm-yyyy')||'-DAY') time_emp_strt_fk /* Construct time_trmntn_accptd_fk manually */ /**/ ,decode(pps.accepted_termination_date, to_date(null), 'NA_EDW', to_char(pps.accepted_termination_date,'dd-mm-yyyy')||'-DAY') time_trm_accptd_fk /* Construct time_trmntn_prjctd_fk manually */ /**/ ,decode(hpps.effective_projected_date, to_date(null), 'NA_EDW', to_char(hpps.effective_projected_date, 'dd-mm-yyyy')||'-DAY') time_trm_prjctd_fk /* Construct time_final_process_fk manually */ /**/ ,decode(pps.final_process_date, to_date(null), 'NA_EDW', to_char(pps.final_process_date, 'dd-mm-yyyy')||'-DAY') time_trm_prcss_fk /* Construct time_actual_termination_fk manually */ /**/ ,decode(pps.actual_termination_date, to_date(null), 'NA_EDW', to_char(pps.actual_termination_date, 'dd-mm-yyyy')||'-DAY') time_trm_occrd_fk /**/ /****************************/ /* Reason (for separation) */ /****************************/ /* Construct reason_fk manually */ /**/ ,decode(pps.leaving_reason, null, 'NA_EDW', 'LEAV_REAS-' || pps.leaving_reason || '-' || inst.instance_code) reason_fk /**/ /****************************/ /* Workforce Movement */ /****************************/ /* Construct workforce_mvmnt_fk manually */ /**/ ,hri_edw_fct_wrkfc_sprtn.find_movement_fk( hpps.past_actual_date, hpps.past_accepted_date, hpps.past_notified_date, hpps.effective_projected_date, hpps.past_final_date, pps.leaving_reason) movement_type_fk /**/ /**************************************************************************/ /* Hidden Primary Keys */ /**************************************************************************/ ,pasg.assignment_id asg_assignment_id ,pasg.business_group_id asg_business_group_id ,pasg.grade_id asg_grade_id ,pasg.job_id asg_job_id ,pasg.location_id asg_location_id ,pasg.organization_id asg_organization_id ,pasg.person_id asg_person_id ,pasg.position_id asg_position_id ,pps.period_of_service_id pps_prd_of_srvc_id ,pps.termination_accepted_person_id pps_trm_acptd_prsn_id /**************************************************************************/ /* Dates */ /**************************************************************************/ /*,asg.effective_start_date assignment_start_date */ ,peo.date_of_birth date_of_birth ,GREATEST(NVL(pps.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(hpps.past_notified_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(hpps.past_accepted_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(hpps.past_actual_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(hpps.past_final_date,to_date('01-01-2000','DD-MM-YYYY'))) last_update_date ,pps.creation_date creation_date /**************************************************************************/ /* Measures */ /**************************************************************************/ /**/ /****************************/ /* Assignment Budget Values */ /****************************/ ,DECODE(pps.date_start,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'FTE', pps.date_start))) emp_start_fte ,DECODE(pps.date_start,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'HEAD', pps.date_start))) emp_start_hdcnt ,DECODE(hpps.past_notified_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'FTE', hpps.past_notified_date))) sprtn_ntfd_fte ,DECODE(hpps.past_notified_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'HEAD', hpps.past_notified_date))) sprtn_ntfd_hdcnt ,DECODE(hpps.past_accepted_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'FTE', hpps.past_accepted_date))) sprtn_accptd_fte ,DECODE(hpps.past_accepted_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'HEAD', hpps.past_accepted_date))) sprtn_accptd_hdcnt ,DECODE(hpps.effective_projected_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'FTE', hpps.effective_projected_date))) sprtn_prjctd_fte ,DECODE(hpps.effective_projected_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'HEAD', hpps.effective_projected_date))) sprtn_prjctd_hdcnt ,DECODE(hpps.past_actual_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'FTE', hpps.past_actual_date))) emp_sprtn_fte ,DECODE(hpps.past_actual_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'HEAD', hpps.past_actual_date))) emp_sprtn_hdcnt ,DECODE(hpps.past_final_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'FTE', hpps.past_final_date))) final_prcss_fte ,DECODE(hpps.past_final_date,NULL,NULL, (hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'HEAD', hpps.past_final_date))) final_prcss_hdcnt ,DECODE( NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date,sot.start_of_time))))), sot.start_of_time,NULL, hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'FTE', NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, hpps.past_notified_date))))) ) separation_fte ,DECODE( NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date,sot.start_of_time))))), sot.start_of_time,NULL, hri_edw_fct_wrkfc_sprtn.calc_abv( pasg.assignment_id, pasg.business_group_id, 'HEAD', NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, hpps.past_notified_date))))) ) separation_headcount ,DECODE(hpps.latest_stage_primary_flag,'Y', DECODE( NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date,sot.start_of_time))))), sot.start_of_time,NULL, NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, hpps.past_notified_date)))) - date_start ),NULL) dys_frm_strt_to_lst_updt ,DECODE( NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date,sot.start_of_time))))), sot.start_of_time,NULL, NVL(hpps.past_final_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, hpps.past_notified_date)))) - date_start ) dys_frm_strt_to_lst_updt_asg ,DECODE(pasg.effective_end_date, sot.end_of_time, DECODE(NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, NVL(hpps.past_notified_date,sot.start_of_time)))), sot.start_of_time, fasg.effective_start_date, NVL(hpps.past_actual_date, NVL(hpps.effective_projected_date, NVL(hpps.past_accepted_date, hpps.past_notified_date)))), pasg.effective_end_date) - fasg.effective_start_date latest_asg_duration /**/ /*******************************************/ /* Set all the valuer flags to indicate if */ /* particular stages in the separation */ /* process have occurred */ /*******************************************/ /**/ ,DECODE(hpps.past_notified_date,NULL,0,1) sprtn_ntfd_vl ,DECODE(hpps.past_accepted_date,NULL,0,1) sprtn_accptd_vl ,DECODE(hpps.effective_projected_date,NULL,0,1) sprtn_plnd_vl ,NULL sprtn_cncld_vl ,DECODE(hpps.past_actual_date,NULL,0,1) sprtn_occrrd_vl ,DECODE(hpps.past_final_date,NULL,0,1) sprtn_fnl_prcssng_vl /**/ /*****************************************/ /* The number of days between various */ /* stages in the separation process */ /*****************************************/ /**/ ,hpps.past_notified_date - pps.date_start dys_frm_strt_to_ntfd_asg ,hpps.past_accepted_date - pps.date_start dys_frm_strt_to_accptd_asg ,hpps.effective_projected_date - pps.date_start dys_frm_strt_to_plnd_asg ,hpps.past_actual_date - pps.date_start dys_frm_strt_to_trm_asg ,hpps.past_final_date - pps.date_start dys_frm_strt_to_prcss_asg /**/ /* The following period of service measures are only populated for */ /* primary assignments */ /**/ ,DECODE(hpps.notified_trmntn_primary_flag,'Y', (hpps.past_notified_date - pps.date_start), NULL) dys_frm_strt_to_ntfd ,DECODE(hpps.accepted_trmntn_primary_flag,'Y', (hpps.past_accepted_date - pps.date_start), NULL) dys_frm_strt_to_accptd ,DECODE(hpps.projected_trmntn_primary_flag,'Y', (hpps.effective_projected_date - pps.date_start), NULL) dys_frm_strt_to_plnd ,DECODE(hpps.actual_trmntn_primary_flag,'Y', (hpps.past_actual_date - pps.date_start), NULL) dys_frm_strt_to_trm ,DECODE(hpps.final_process_primary_flag,'Y', (hpps.past_final_date - pps.date_start), NULL) dys_frm_strt_to_prcss ,hpps.past_accepted_date - hpps.past_notified_date dys_frm_ntfd_to_acptd ,hpps.effective_projected_date - hpps.past_notified_date dys_frm_ntfd_to_plnd ,hpps.past_actual_date - hpps.past_notified_date dys_frm_ntfd_to_ocrd ,hpps.effective_projected_date - hpps.past_accepted_date dys_frm_acptd_to_plnd ,hpps.past_actual_date - hpps.past_accepted_date dys_frm_acptd_to_ocrd /**/ /**************************************************************************/ /* Other attributes from the period of service OLTP table included for */ /* derivation purposes */ /**************************************************************************/ /**/ ,pps.notified_termination_date ntfd_trmntn_dt ,pps.accepted_termination_date accptd_trmntn_dt ,pps.projected_termination_date prjctd_trmntn_dt ,pps.actual_termination_date actual_trmntn_dt ,pps.final_process_date final_process_dt ,pps.leaving_reason /**/ /**************************************************************************/ /* Flex Tags */ /**************************************************************************/ /**/ ,'_DF:PER:PER_PERIODS_OF_SERVICE:pps' ,'_DF:PER:PER_PDS_DEVELOPER_DF:pps' /**/ FROM per_all_assignments_f pasg ,per_all_assignments_f fasg ,per_all_people_f peo ,edw_local_instance inst ,per_periods_of_service pps ,hri_edw_period_of_service hpps ,(SELECT hr_general.start_of_time start_of_time , hr_general.end_of_time end_of_time from sys.dual) sot WHERE hpps.assignment_id = pasg.assignment_id AND hpps.period_of_service_id = pps.period_of_service_id AND pasg.assignment_type = 'E' AND pasg.effective_start_date <= SYSDATE AND peo.person_id = pasg.person_id AND peo.person_id = pps.person_id AND fasg.assignment_id = pasg.assignment_id AND fasg.effective_start_date = (SELECT MIN(asg3.effective_start_date) FROM per_all_assignments_f asg3 WHERE asg3.assignment_type = 'E' AND asg3.assignment_id = pasg.assignment_id) AND peo.effective_start_date = (SELECT MAX(peo1.effective_start_date) FROM per_all_people_f peo1 WHERE peo1.effective_start_date BETWEEN pps.date_start AND GREATEST( NVL(pps.actual_termination_date, sysdate), NVL(pps.final_process_date, sysdate)) AND peo1.person_id = pps.person_id) AND pasg.effective_start_date = (SELECT MAX(asg2.effective_start_date) FROM per_all_assignments_f asg2 WHERE asg2.effective_start_date BETWEEN pps.date_start AND GREATEST( NVL(pps.actual_termination_date, sysdate), NVL(pps.final_process_date, sysdate)) AND asg2.person_id = pps.person_id AND asg2.assignment_id = pasg.assignment_id)

View Text - HTML Formatted

SELECT /**************************************************************************/ /* PRIMARY KEY OF FACT */ /**************************************************************************/ /**/ PPS.PERIOD_OF_SERVICE_ID||'-'||PPS.PERSON_ID|| '-'||PASG.ASSIGNMENT_ID|| '-'||INSTANCE_CODE SEPARATION_PK /**/ /**************************************************************************/ /* FOREIGN KEYS TO DIMENSIONS */ /**************************************************************************/ /**/ /**************/ /* ASSIGNMENT */ /**************/ /* CONSTRUCT ASSIGNMENT_FK MANUALLY */ /**/
, PASG.ASSIGNMENT_ID || '-' || INSTANCE_CODE ASSIGNMENT_FK /**/ /************/ /* 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(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SYSDATE))))
, PEO.DATE_OF_BIRTH)) AGE_BAND_FK /**/ /****************/ /* SERVICE BAND */ /****************/ /* USE SERVICE BAND API TO CONSTRUCT SERVICE_BAND_FK */ /**/ /* LENGTH OF SERVICE IS CALCULATED AS THE DIFFERENCE */ /* BETWEEN SNP.SNAPSHOT_DATE */ /*
AND THE EMPLOYMENT START */ /**/
, EDW_HR_SERVICE_PKG.SERVICE_BAND_FK( NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SYSDATE)))) - PPS.DATE_START) SERVICE_BAND_FK /**/ /*************/ /* GEOGRAPHY */ /*************/ /* CONSTRUCT GEOGRAPHY_FK MANUALLY
, PROVIDING AN ASSIGNMENT LOCATION EXISTS */ /**/
, DECODE(PASG.LOCATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, PASG.LOCATION_ID || '-' || INSTANCE_CODE || '-' || 'HR_LOC') GEOGRAPHY_FK /**/ /********* /* GRADE */ /*********/ /* CONSTRUCT GRADE_FK MANUALLY PROVIDING AN ASSIGNMENT GRADE EXISTS */ /**/
, DECODE(PASG.GRADE_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, PASG.GRADE_ID || '-' || INSTANCE_CODE) GRADE_FK /**/ /************/ /* INSTANCE */ /************/ /* STANDARD EDW FOREIGN KEY */
, INSTANCE_CODE INSTANCE_FK /**/ /*******/ /* JOB */ /*******/ /* CONSTRUCT JOB_FK MANUALLY PROVIDING AN ASSIGNMENT JOB EXISTS */ /**/
, DECODE(PASG.JOB_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, PASG.JOB_ID || '-' || INSTANCE_CODE) JOB_FK /**/ /****************/ /* ORGANIZATION */ /****************/ /* CONSTRUCT ORGANIZATION FK MANUALLY */ /**/
, PASG.ORGANIZATION_ID || '-' || INSTANCE_CODE ORGANIZATION_FK /**/ /**********/ /* PERSON */ /**********/ /* CONSTRUCT PERSON_FK MANUALLY */ /**/
, PPS.PERSON_ID || '-' || INSTANCE_CODE || '-EMPLOYEE-PERS' PERSON_FK /**/ /***************/ /* PERSON TYPE */ /***************/ /* USE PERSON TYPE API TO CONSTRUCT PERSON_TYPE_FK */
, EDW_HR_PRSN_TYP_PKG.PERSON_TYPE_FK ( PASG.PERSON_ID
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SYSDATE))))) PERSON_TYPE_FK /**/ /************/ /* POSITION */ /************/ /* CONSTRUCT POSITION_FK MANUALLY
, IF AN ASSIGNMENT POSITION EXISTS */ /**/
, DECODE(PASG.POSITION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, PASG.POSITION_ID || '-' || INSTANCE_CODE) POSITION_FK /**/ /**********************/ /* TIME (APPLICATION) */ /**********************/ /* CONSTRUCT TIME_TRMNTN_NTFD_FK MANUALLY */ /**/
, DECODE(PPS.NOTIFIED_TERMINATION_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(PPS.NOTIFIED_TERMINATION_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_TRM_NTFD_FK /* CONSTRUCT TIME_EMPLYMNT_STRT_FK MANUALLY */ /**/
, DECODE(PPS.DATE_START
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(PPS.DATE_START
, 'DD-MM-YYYY')||'-DAY') TIME_EMP_STRT_FK /* CONSTRUCT TIME_TRMNTN_ACCPTD_FK MANUALLY */ /**/
, DECODE(PPS.ACCEPTED_TERMINATION_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(PPS.ACCEPTED_TERMINATION_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_TRM_ACCPTD_FK /* CONSTRUCT TIME_TRMNTN_PRJCTD_FK MANUALLY */ /**/
, DECODE(HPPS.EFFECTIVE_PROJECTED_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(HPPS.EFFECTIVE_PROJECTED_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_TRM_PRJCTD_FK /* CONSTRUCT TIME_FINAL_PROCESS_FK MANUALLY */ /**/
, DECODE(PPS.FINAL_PROCESS_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(PPS.FINAL_PROCESS_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_TRM_PRCSS_FK /* CONSTRUCT TIME_ACTUAL_TERMINATION_FK MANUALLY */ /**/
, DECODE(PPS.ACTUAL_TERMINATION_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(PPS.ACTUAL_TERMINATION_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_TRM_OCCRD_FK /**/ /****************************/ /* REASON (FOR SEPARATION) */ /****************************/ /* CONSTRUCT REASON_FK MANUALLY */ /**/
, DECODE(PPS.LEAVING_REASON
, NULL
, 'NA_EDW'
, 'LEAV_REAS-' || PPS.LEAVING_REASON || '-' || INST.INSTANCE_CODE) REASON_FK /**/ /****************************/ /* WORKFORCE MOVEMENT */ /****************************/ /* CONSTRUCT WORKFORCE_MVMNT_FK MANUALLY */ /**/
, HRI_EDW_FCT_WRKFC_SPRTN.FIND_MOVEMENT_FK( HPPS.PAST_ACTUAL_DATE
, HPPS.PAST_ACCEPTED_DATE
, HPPS.PAST_NOTIFIED_DATE
, HPPS.EFFECTIVE_PROJECTED_DATE
, HPPS.PAST_FINAL_DATE
, PPS.LEAVING_REASON) MOVEMENT_TYPE_FK /**/ /**************************************************************************/ /* HIDDEN PRIMARY KEYS */ /**************************************************************************/
, PASG.ASSIGNMENT_ID ASG_ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID ASG_BUSINESS_GROUP_ID
, PASG.GRADE_ID ASG_GRADE_ID
, PASG.JOB_ID ASG_JOB_ID
, PASG.LOCATION_ID ASG_LOCATION_ID
, PASG.ORGANIZATION_ID ASG_ORGANIZATION_ID
, PASG.PERSON_ID ASG_PERSON_ID
, PASG.POSITION_ID ASG_POSITION_ID
, PPS.PERIOD_OF_SERVICE_ID PPS_PRD_OF_SRVC_ID
, PPS.TERMINATION_ACCEPTED_PERSON_ID PPS_TRM_ACPTD_PRSN_ID /**************************************************************************/ /* DATES */ /**************************************************************************/ /*
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE */
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, GREATEST(NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(HPPS.PAST_NOTIFIED_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(HPPS.PAST_ACCEPTED_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(HPPS.PAST_ACTUAL_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(HPPS.PAST_FINAL_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
, PPS.CREATION_DATE CREATION_DATE /**************************************************************************/ /* MEASURES */ /**************************************************************************/ /**/ /****************************/ /* ASSIGNMENT BUDGET VALUES */ /****************************/
, DECODE(PPS.DATE_START
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, PPS.DATE_START))) EMP_START_FTE
, DECODE(PPS.DATE_START
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, PPS.DATE_START))) EMP_START_HDCNT
, DECODE(HPPS.PAST_NOTIFIED_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, HPPS.PAST_NOTIFIED_DATE))) SPRTN_NTFD_FTE
, DECODE(HPPS.PAST_NOTIFIED_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, HPPS.PAST_NOTIFIED_DATE))) SPRTN_NTFD_HDCNT
, DECODE(HPPS.PAST_ACCEPTED_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, HPPS.PAST_ACCEPTED_DATE))) SPRTN_ACCPTD_FTE
, DECODE(HPPS.PAST_ACCEPTED_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, HPPS.PAST_ACCEPTED_DATE))) SPRTN_ACCPTD_HDCNT
, DECODE(HPPS.EFFECTIVE_PROJECTED_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, HPPS.EFFECTIVE_PROJECTED_DATE))) SPRTN_PRJCTD_FTE
, DECODE(HPPS.EFFECTIVE_PROJECTED_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, HPPS.EFFECTIVE_PROJECTED_DATE))) SPRTN_PRJCTD_HDCNT
, DECODE(HPPS.PAST_ACTUAL_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, HPPS.PAST_ACTUAL_DATE))) EMP_SPRTN_FTE
, DECODE(HPPS.PAST_ACTUAL_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, HPPS.PAST_ACTUAL_DATE))) EMP_SPRTN_HDCNT
, DECODE(HPPS.PAST_FINAL_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, HPPS.PAST_FINAL_DATE))) FINAL_PRCSS_FTE
, DECODE(HPPS.PAST_FINAL_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, HPPS.PAST_FINAL_DATE))) FINAL_PRCSS_HDCNT
, DECODE( NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SOT.START_OF_TIME)))))
, SOT.START_OF_TIME
, NULL
, HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, HPPS.PAST_NOTIFIED_DATE))))) ) SEPARATION_FTE
, DECODE( NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SOT.START_OF_TIME)))))
, SOT.START_OF_TIME
, NULL
, HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, HPPS.PAST_NOTIFIED_DATE))))) ) SEPARATION_HEADCOUNT
, DECODE(HPPS.LATEST_STAGE_PRIMARY_FLAG
, 'Y'
, DECODE( NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SOT.START_OF_TIME)))))
, SOT.START_OF_TIME
, NULL
, NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, HPPS.PAST_NOTIFIED_DATE)))) - DATE_START )
, NULL) DYS_FRM_STRT_TO_LST_UPDT
, DECODE( NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SOT.START_OF_TIME)))))
, SOT.START_OF_TIME
, NULL
, NVL(HPPS.PAST_FINAL_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, HPPS.PAST_NOTIFIED_DATE)))) - DATE_START ) DYS_FRM_STRT_TO_LST_UPDT_ASG
, DECODE(PASG.EFFECTIVE_END_DATE
, SOT.END_OF_TIME
, DECODE(NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, NVL(HPPS.PAST_NOTIFIED_DATE
, SOT.START_OF_TIME))))
, SOT.START_OF_TIME
, FASG.EFFECTIVE_START_DATE
, NVL(HPPS.PAST_ACTUAL_DATE
, NVL(HPPS.EFFECTIVE_PROJECTED_DATE
, NVL(HPPS.PAST_ACCEPTED_DATE
, HPPS.PAST_NOTIFIED_DATE))))
, PASG.EFFECTIVE_END_DATE) - FASG.EFFECTIVE_START_DATE LATEST_ASG_DURATION /**/ /*******************************************/ /* SET ALL THE VALUER FLAGS TO INDICATE IF */ /* PARTICULAR STAGES IN THE SEPARATION */ /* PROCESS HAVE OCCURRED */ /*******************************************/ /**/
, DECODE(HPPS.PAST_NOTIFIED_DATE
, NULL
, 0
, 1) SPRTN_NTFD_VL
, DECODE(HPPS.PAST_ACCEPTED_DATE
, NULL
, 0
, 1) SPRTN_ACCPTD_VL
, DECODE(HPPS.EFFECTIVE_PROJECTED_DATE
, NULL
, 0
, 1) SPRTN_PLND_VL
, NULL SPRTN_CNCLD_VL
, DECODE(HPPS.PAST_ACTUAL_DATE
, NULL
, 0
, 1) SPRTN_OCCRRD_VL
, DECODE(HPPS.PAST_FINAL_DATE
, NULL
, 0
, 1) SPRTN_FNL_PRCSSNG_VL /**/ /*****************************************/ /* THE NUMBER OF DAYS BETWEEN VARIOUS */ /* STAGES IN THE SEPARATION PROCESS */ /*****************************************/ /**/
, HPPS.PAST_NOTIFIED_DATE - PPS.DATE_START DYS_FRM_STRT_TO_NTFD_ASG
, HPPS.PAST_ACCEPTED_DATE - PPS.DATE_START DYS_FRM_STRT_TO_ACCPTD_ASG
, HPPS.EFFECTIVE_PROJECTED_DATE - PPS.DATE_START DYS_FRM_STRT_TO_PLND_ASG
, HPPS.PAST_ACTUAL_DATE - PPS.DATE_START DYS_FRM_STRT_TO_TRM_ASG
, HPPS.PAST_FINAL_DATE - PPS.DATE_START DYS_FRM_STRT_TO_PRCSS_ASG /**/ /* THE FOLLOWING PERIOD OF SERVICE MEASURES ARE ONLY POPULATED FOR */ /* PRIMARY ASSIGNMENTS */ /**/
, DECODE(HPPS.NOTIFIED_TRMNTN_PRIMARY_FLAG
, 'Y'
, (HPPS.PAST_NOTIFIED_DATE - PPS.DATE_START)
, NULL) DYS_FRM_STRT_TO_NTFD
, DECODE(HPPS.ACCEPTED_TRMNTN_PRIMARY_FLAG
, 'Y'
, (HPPS.PAST_ACCEPTED_DATE - PPS.DATE_START)
, NULL) DYS_FRM_STRT_TO_ACCPTD
, DECODE(HPPS.PROJECTED_TRMNTN_PRIMARY_FLAG
, 'Y'
, (HPPS.EFFECTIVE_PROJECTED_DATE - PPS.DATE_START)
, NULL) DYS_FRM_STRT_TO_PLND
, DECODE(HPPS.ACTUAL_TRMNTN_PRIMARY_FLAG
, 'Y'
, (HPPS.PAST_ACTUAL_DATE - PPS.DATE_START)
, NULL) DYS_FRM_STRT_TO_TRM
, DECODE(HPPS.FINAL_PROCESS_PRIMARY_FLAG
, 'Y'
, (HPPS.PAST_FINAL_DATE - PPS.DATE_START)
, NULL) DYS_FRM_STRT_TO_PRCSS
, HPPS.PAST_ACCEPTED_DATE - HPPS.PAST_NOTIFIED_DATE DYS_FRM_NTFD_TO_ACPTD
, HPPS.EFFECTIVE_PROJECTED_DATE - HPPS.PAST_NOTIFIED_DATE DYS_FRM_NTFD_TO_PLND
, HPPS.PAST_ACTUAL_DATE - HPPS.PAST_NOTIFIED_DATE DYS_FRM_NTFD_TO_OCRD
, HPPS.EFFECTIVE_PROJECTED_DATE - HPPS.PAST_ACCEPTED_DATE DYS_FRM_ACPTD_TO_PLND
, HPPS.PAST_ACTUAL_DATE - HPPS.PAST_ACCEPTED_DATE DYS_FRM_ACPTD_TO_OCRD /**/ /**************************************************************************/ /* OTHER ATTRIBUTES
FROM THE PERIOD OF SERVICE OLTP TABLE INCLUDED FOR */ /* DERIVATION PURPOSES */ /**************************************************************************/ /**/
, PPS.NOTIFIED_TERMINATION_DATE NTFD_TRMNTN_DT
, PPS.ACCEPTED_TERMINATION_DATE ACCPTD_TRMNTN_DT
, PPS.PROJECTED_TERMINATION_DATE PRJCTD_TRMNTN_DT
, PPS.ACTUAL_TERMINATION_DATE ACTUAL_TRMNTN_DT
, PPS.FINAL_PROCESS_DATE FINAL_PROCESS_DT
, PPS.LEAVING_REASON /**/ /**************************************************************************/ /* FLEX TAGS */ /**************************************************************************/ /**/
, '_DF:PER:PER_PERIODS_OF_SERVICE:PPS'
, '_DF:PER:PER_PDS_DEVELOPER_DF:PPS' /**/
FROM PER_ALL_ASSIGNMENTS_F PASG
, PER_ALL_ASSIGNMENTS_F FASG
, PER_ALL_PEOPLE_F PEO
, EDW_LOCAL_INSTANCE INST
, PER_PERIODS_OF_SERVICE PPS
, HRI_EDW_PERIOD_OF_SERVICE HPPS
, (SELECT HR_GENERAL.START_OF_TIME START_OF_TIME
, HR_GENERAL.END_OF_TIME END_OF_TIME
FROM SYS.DUAL) SOT
WHERE HPPS.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND HPPS.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.EFFECTIVE_START_DATE <= SYSDATE
AND PEO.PERSON_ID = PASG.PERSON_ID
AND PEO.PERSON_ID = PPS.PERSON_ID
AND FASG.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND FASG.EFFECTIVE_START_DATE = (SELECT MIN(ASG3.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG3
WHERE ASG3.ASSIGNMENT_TYPE = 'E'
AND ASG3.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID)
AND PEO.EFFECTIVE_START_DATE = (SELECT MAX(PEO1.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PEO1
WHERE PEO1.EFFECTIVE_START_DATE BETWEEN PPS.DATE_START
AND GREATEST( NVL(PPS.ACTUAL_TERMINATION_DATE
, SYSDATE)
, NVL(PPS.FINAL_PROCESS_DATE
, SYSDATE))
AND PEO1.PERSON_ID = PPS.PERSON_ID)
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
WHERE ASG2.EFFECTIVE_START_DATE BETWEEN PPS.DATE_START
AND GREATEST( NVL(PPS.ACTUAL_TERMINATION_DATE
, SYSDATE)
, NVL(PPS.FINAL_PROCESS_DATE
, SYSDATE))
AND ASG2.PERSON_ID = PPS.PERSON_ID
AND ASG2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID)