View: HR_EDWBV_WRK_SPRTN_FCV

Product:	HRI - Human Resources Intelligence
Description:
Implementation/DBA Data:	APPS.HR_EDWBV_WRK_SPRTN_FCV

View Text

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(PPS.ACTUAL_TERMINATION_DATE
, NVL(PPS.PROJECTED_TERMINATION_DATE
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, NVL(PPS.NOTIFIED_TERMINATION_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(PPS.ACTUAL_TERMINATION_DATE
, NVL(PPS.PROJECTED_TERMINATION_DATE
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, NVL(PPS.NOTIFIED_TERMINATION_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
, PPS.LAST_UPDATE_DATE) 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(PPS.PROJECTED_TERMINATION_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(PPS.PROJECTED_TERMINATION_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( PPS.ACTUAL_TERMINATION_DATE
, PPS.ACCEPTED_TERMINATION_DATE
, PPS.NOTIFIED_TERMINATION_DATE
, PPS.PROJECTED_TERMINATION_DATE
, PPS.FINAL_PROCESS_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
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY')) LAST_UPDATE_DATE
, PPS.CREATION_DATE CREATION_DATE /**************************************************************************/ /* MEASURES */ /**************************************************************************/ /**/ /****************************/ /* ASSIGNMENT BUDGET VALUES */ /****************************/
, DECODE(DATE_START
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, DATE_START))) EMP_START_FTE
, DECODE(DATE_START
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, DATE_START))) EMP_START_HDCNT
, DECODE(NOTIFIED_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, NOTIFIED_TERMINATION_DATE))) SPRTN_NTFD_FTE
, DECODE(NOTIFIED_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, NOTIFIED_TERMINATION_DATE))) SPRTN_NTFD_HDCNT
, DECODE(ACCEPTED_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, ACCEPTED_TERMINATION_DATE))) SPRTN_ACCPTD_FTE
, DECODE(ACCEPTED_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, ACCEPTED_TERMINATION_DATE))) SPRTN_ACCPTD_HDCNT
, DECODE(PROJECTED_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, PROJECTED_TERMINATION_DATE))) SPRTN_PRJCTD_FTE
, DECODE(PROJECTED_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, PROJECTED_TERMINATION_DATE))) SPRTN_PRJCTD_HDCNT
, DECODE(ACTUAL_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, ACTUAL_TERMINATION_DATE))) EMP_SPRTN_FTE
, DECODE(ACTUAL_TERMINATION_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, ACTUAL_TERMINATION_DATE))) EMP_SPRTN_HDCNT
, DECODE(FINAL_PROCESS_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'FTE'
, FINAL_PROCESS_DATE))) FINAL_PRCSS_FTE
, DECODE(FINAL_PROCESS_DATE
, NULL
, NULL
, (HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( PASG.ASSIGNMENT_ID
, PASG.BUSINESS_GROUP_ID
, 'HEAD'
, FINAL_PROCESS_DATE))) FINAL_PRCSS_HDCNT
, DECODE(GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_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'
, GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_DATE
, SOT.START_OF_TIME) ))) SEPARATION_FTE
, DECODE(GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_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'
, GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_DATE
, SOT.START_OF_TIME) ))) SEPARATION_HEADCOUNT
, DECODE(HPPS.LATEST_STAGE_PRIMARY_FLAG
, 'Y'
, DECODE(GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_DATE
, SOT.START_OF_TIME) )
, SOT.START_OF_TIME
, NULL
, GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_DATE
, SOT.START_OF_TIME) ) - DATE_START )
, NULL) DYS_FRM_STRT_TO_LST_UPDT
, DECODE(GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_DATE
, SOT.START_OF_TIME) )
, SOT.START_OF_TIME
, NULL
, GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.FINAL_PROCESS_DATE
, SOT.START_OF_TIME) ) - DATE_START ) DYS_FRM_STRT_TO_LST_UPDT_ASG
, DECODE(PASG.EFFECTIVE_END_DATE
, SOT.END_OF_TIME
, DECODE( GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME) )
, SOT.START_OF_TIME
, FASG.EFFECTIVE_START_DATE
, GREATEST( NVL(PPS.NOTIFIED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACCEPTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.PROJECTED_TERMINATION_DATE
, SOT.START_OF_TIME)
, NVL(PPS.ACTUAL_TERMINATION_DATE
, SOT.START_OF_TIME) ) )
, 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(NOTIFIED_TERMINATION_DATE
, NULL
, 0
, 1) SPRTN_NTFD_VL
, DECODE(ACCEPTED_TERMINATION_DATE
, NULL
, 0
, 1) SPRTN_ACCPTD_VL
, DECODE(PROJECTED_TERMINATION_DATE
, NULL
, 0
, 1) SPRTN_PLND_VL
, NULL SPRTN_CNCLD_VL
, DECODE(ACTUAL_TERMINATION_DATE
, NULL
, 0
, 1) SPRTN_OCCRRD_VL
, DECODE(FINAL_PROCESS_DATE
, NULL
, 0
, 1) SPRTN_FNL_PRCSSNG_VL /**/ /*****************************************/ /* THE NUMBER OF DAYS BETWEEN VARIOUS */ /* STAGES IN THE SEPARATION PROCESS */ /*****************************************/ /**/
, NOTIFIED_TERMINATION_DATE - DATE_START DYS_FRM_STRT_TO_NTFD_ASG
, ACCEPTED_TERMINATION_DATE - DATE_START DYS_FRM_STRT_TO_ACCPTD_ASG
, PROJECTED_TERMINATION_DATE - DATE_START DYS_FRM_STRT_TO_PLND_ASG
, ACTUAL_TERMINATION_DATE - DATE_START DYS_FRM_STRT_TO_TRM_ASG
, FINAL_PROCESS_DATE - 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'
, (NOTIFIED_TERMINATION_DATE - DATE_START)
, NULL) DYS_FRM_STRT_TO_NTFD
, DECODE(HPPS.ACCEPTED_TRMNTN_PRIMARY_FLAG
, 'Y'
, (ACCEPTED_TERMINATION_DATE - DATE_START)
, NULL) DYS_FRM_STRT_TO_ACCPTD
, DECODE(HPPS.PROJECTED_TRMNTN_PRIMARY_FLAG
, 'Y'
, (PROJECTED_TERMINATION_DATE - DATE_START)
, NULL) DYS_FRM_STRT_TO_PLND
, DECODE(HPPS.ACTUAL_TRMNTN_PRIMARY_FLAG
, 'Y'
, (ACTUAL_TERMINATION_DATE - DATE_START)
, NULL) DYS_FRM_STRT_TO_TRM
, DECODE(HPPS.FINAL_PROCESS_PRIMARY_FLAG
, 'Y'
, (FINAL_PROCESS_DATE - DATE_START)
, NULL) DYS_FRM_STRT_TO_PRCSS
, ACCEPTED_TERMINATION_DATE - NOTIFIED_TERMINATION_DATE DYS_FRM_NTFD_TO_ACPTD
, PROJECTED_TERMINATION_DATE - NOTIFIED_TERMINATION_DATE DYS_FRM_NTFD_TO_PLND
, ACTUAL_TERMINATION_DATE - NOTIFIED_TERMINATION_DATE DYS_FRM_NTFD_TO_OCRD
, PROJECTED_TERMINATION_DATE - ACCEPTED_TERMINATION_DATE DYS_FRM_ACPTD_TO_PLND
, ACTUAL_TERMINATION_DATE - ACCEPTED_TERMINATION_DATE DYS_FRM_ACPTD_TO_OCRD /**/ /**************************************************************************/ /* OTHER ATTRIBUTES
FROM THE PERIOD OF SERVICE OLTP TABLE INCLUDED FOR */ /* DERIVATION PURPOSES */ /**************************************************************************/ /**/
, NOTIFIED_TERMINATION_DATE NTFD_TRMNTN_DT
, ACCEPTED_TERMINATION_DATE ACCPTD_TRMNTN_DT
, PROJECTED_TERMINATION_DATE PRJCTD_TRMNTN_DT
, ACTUAL_TERMINATION_DATE ACTUAL_TRMNTN_DT
, FINAL_PROCESS_DATE FINAL_PROCESS_DT
, 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 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)
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 HPPS.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND HPPS.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PASG.ASSIGNMENT_TYPE = 'E'

Columns