View: HR_EDWBV_WRK_ACTVTY_FCV

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

View Text

SELECT /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ ASG.ASSIGNMENT_ID || '-' || ASG.EFFECTIVE_START_DATE || '-' || INSTANCE_CODE ASSIGNMENT_CHANGE_PK /* AGE BAND */ /************/ /* USE AGE BAND API WITH AGE IN DAYS AT TIME OF CHANGE */
, EDW_HR_AGE_BAND_PKG.AGE_BAND_FK ( MONTHS_BETWEEN(ASG.EFFECTIVE_START_DATE
, DATE_OF_BIRTH)) AGE_BAND_FK /* APPLICATION */ /***************/
, NULL APPLICATION_FK /* ASSIGNMENT */ /**************/ /* CONSTRUCT ASSIGNMENT_FK MANUALLY */
, ASG.ASSIGNMENT_ID || '-' || INSTANCE_CODE ASSIGNMENT_FK /* ASSIGNMENT CHANGE TYPE */ /**************************/
, DECODE(CHG.ORG_CHANGE_FLAG
, 1
, 'Y'
, 'N') || '-' || DECODE(CHG.JOB_CHANGE_FLAG
, 1
, 'Y'
, 'N') || '-' || DECODE(CHG.POS_CHANGE_FLAG
, 1
, 'Y'
, 'N') || '-' || DECODE(CHG.GRD_CHANGE_FLAG
, 1
, 'Y'
, 'N') || '-' || DECODE(CHG.GEOG_CHANGE_FLAG
, 1
, 'Y'
, 'N') || '-' || DECODE( CHG.JOB_CHANGE_FLAG + CHG.GRD_CHANGE_FLAG + CHG.POS_CHANGE_FLAG + CHG.ORG_CHANGE_FLAG + CHG.GEOG_CHANGE_FLAG + CHG.EMPLYMNT_START_FLAG
, 0
, 'Y'
, 'N') || '-Y-' || INSTANCE_CODE ASSIGNMENT_CHANGE_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_FROM_FK
, DECODE(ASG.LOCATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.LOCATION_ID || '-' || INSTANCE_CODE || '-' || 'HR_LOC') GEOGRAPHY_TO_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_FROM_FK
, DECODE(ASG.GRADE_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.GRADE_ID || '-' || INSTANCE_CODE) GRADE_TO_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_FROM_FK
, DECODE(ASG.JOB_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.JOB_ID || '-' || INSTANCE_CODE) JOB_TO_FK /* ORGANIZATION */ /****************/ /* CONSTRUCT ORGANIZATION_ASSIGNMENT MANUALLY
, IT IS REQUIRED FOR ASSIGNMENT */
, DECODE(PASG_ORGANIZATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, PASG_ORGANIZATION_ID || '-' || INSTANCE_CODE) ORGANIZATION_FROM_FK
, ASG.ORGANIZATION_ID || '-' || INSTANCE_CODE ORGANIZATION_TO_FK /* PERSON */ /**********/ /* CONSTRUCT PERSON_FK MANUALLY */
, ASG.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 (ASG.PERSON_ID
, ASG.EFFECTIVE_START_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_FROM_FK
, DECODE(ASG.POSITION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.POSITION_ID || '-' || INSTANCE_CODE) POSITION_TO_FK /* WORKFORCE MOVEMENTS */ /***********************/ /* CALL FUNCTION TO CONSTRUCT MOVEMENT_FK FOR THE EVENT */
, DECODE(CHG.ORG_CHANGE_FLAG
, 0
, 'NA_EDW'
, 'GAINS-' || INSTANCE_CODE || '-GAIN_ORG-' || INSTANCE_CODE || '-GAIN_ORG-' || INSTANCE_CODE || '-LOSSES-' || INSTANCE_CODE || '-LOSS_ORG-' || INSTANCE_CODE || '-LOSS_ORG-' || INSTANCE_CODE || '-' || NA_EDW_PK || '-' || NA_EDW_PK || '-' || INSTANCE_CODE) MOVEMENT_FK /* REASON */ /**********/ /* CALL A FUNCTION TO VALIDATE ASSIGNMENT REASON */
, DECODE(ASG.CHANGE_REASON
, NULL
, 'NA_EDW'
, HRI_EDW_FCT_WRK_ACTVTY.CHECK_REASON(ASG.CHANGE_REASON
, INSTANCE_CODE)) REASON_FK /* SERVICE BAND */ /****************/ /* USE SERVICE BAND API TO CONSTRUCT SERVICE_BAND_FK AT TIME OF CHANGE */
, EDW_HR_SERVICE_PKG.SERVICE_BAND_FK (ASG.EFFECTIVE_START_DATE - PPS_DATE_START) SERVICE_BAND_FK /* TIME (APPLICATION) */ /**********************/ /* CONSTRUCT TIME_FK MANUALLY */
, TO_CHAR(ASG.EFFECTIVE_START_DATE
, 'DD-MM-YYYY') ||'-DAY' TIME_FROM_FK
, DECODE(ASG.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, 'NA_EDW'
, TO_CHAR(ASG.EFFECTIVE_END_DATE
, 'DD-MM-YYYY') ||'-DAY') TIME_TO_FK /************/ /* MEASURES */ /************/
, CHG.JOB_CHANGE_FLAG JOB_CHANGE_FLAG
, CHG.GRD_CHANGE_FLAG GRD_CHANGE_FLAG
, CHG.POS_CHANGE_FLAG POS_CHANGE_FLAG
, CHG.ORG_CHANGE_FLAG ORG_CHANGE_FLAG
, CHG.GEOG_CHANGE_FLAG GEOG_CHANGE_FLAG
, CHG.EMPLYMNT_START_FLAG EMPLYMNT_START_FLAG
, CHG.ASSGNMNT_ENDED_FLAG ASSGNMNT_ENDED_FLAG
, DECODE( CHG.JOB_CHANGE_FLAG + CHG.GRD_CHANGE_FLAG + CHG.POS_CHANGE_FLAG + CHG.ORG_CHANGE_FLAG + CHG.GEOG_CHANGE_FLAG + CHG.EMPLYMNT_START_FLAG
, 0
, 1
, 0) OTHER_CHANGE_FLAG
, HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( ASG.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, 'HEAD'
, ASG.EFFECTIVE_START_DATE) ASG_CHANGE_HEADCOUNT
, HRI_EDW_FCT_WRKFC_SPRTN.CALC_ABV( ASG.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, 'FTE'
, ASG.EFFECTIVE_START_DATE) ASG_CHANGE_FTE
, DECODE(CHG.ORG_CHANGE_FLAG
, 1
, HRI_EDW_FCT_WRK_ACTVTY.GET_DAYS_TO_LAST_ORG_X (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.EFFECTIVE_START_DATE)
, TO_NUMBER(NULL)) DAYS_SINCE_LAST_ORG_X
, DECODE(CHG.JOB_CHANGE_FLAG
, 1
, HRI_EDW_FCT_WRK_ACTVTY.GET_DAYS_TO_LAST_JOB_X (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.EFFECTIVE_START_DATE)
, TO_NUMBER(NULL)) DAYS_SINCE_LAST_JOB_X
, DECODE(CHG.POS_CHANGE_FLAG
, 1
, HRI_EDW_FCT_WRK_ACTVTY.GET_DAYS_TO_LAST_POS_X (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.EFFECTIVE_START_DATE)
, TO_NUMBER(NULL)) DAYS_SINCE_LAST_POS_X
, DECODE(CHG.GRD_CHANGE_FLAG
, 1
, HRI_EDW_FCT_WRK_ACTVTY.GET_DAYS_TO_LAST_GRD_X (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.EFFECTIVE_START_DATE)
, TO_NUMBER(NULL)) DAYS_SINCE_LAST_GRD_X
, DECODE(CHG.GEOG_CHANGE_FLAG
, 1
, HRI_EDW_FCT_WRK_ACTVTY.GET_DAYS_TO_LAST_GEOG_X (ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.EFFECTIVE_START_DATE)
, TO_NUMBER(NULL)) DAYS_SINCE_LAST_GEOG_X
, DECODE(CHG.ASSGNMNT_ENDED_FLAG
, 1
, CHG.ASSIGNMENT_END - CHG.EMPLOYMENT_START
, TO_NUMBER(NULL)) DAYS_EMP_START_TO_TERM
, CHG.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CHG.CREATION_DATE CREATION_DATE /* OTHER ASSIGNMENT ATTRIBUTES */
, ASG.ASSIGNMENT_ID
, ASG.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE
, ASG.BUSINESS_GROUP_ID
, ASG.RECRUITER_ID
, ASG.GRADE_ID
, ASG.POSITION_ID
, ASG.JOB_ID
, ASG.ASSIGNMENT_STATUS_TYPE_ID
, ASG.PAYROLL_ID
, ASG.LOCATION_ID
, ASG.PERSON_REFERRED_BY_ID
, ASG.SUPERVISOR_ID
, ASG.SPECIAL_CEILING_STEP_ID
, ASG.PERSON_ID
, ASG.RECRUITMENT_ACTIVITY_ID
, ASG.SOURCE_ORGANIZATION_ID
, ASG.ORGANIZATION_ID
, ASG.PEOPLE_GROUP_ID
, ASG.SOFT_CODING_KEYFLEX_ID
, ASG.VACANCY_ID
, ASG.PAY_BASIS_ID
, ASG.ASSIGNMENT_SEQUENCE
, ASG.ASSIGNMENT_TYPE
, ASG.PRIMARY_FLAG
, ASG.APPLICATION_ID
, ASG.ASSIGNMENT_NUMBER
, ASG.CHANGE_REASON
, ASG.COMMENT_ID
, ASG.DATE_PROBATION_END
, ASG.DEFAULT_CODE_COMB_ID
, ASG.EMPLOYMENT_CATEGORY
, ASG.FREQUENCY
, ASG.INTERNAL_ADDRESS_LINE
, ASG.MANAGER_FLAG
, ASG.NORMAL_HOURS
, ASG.PERF_REVIEW_PERIOD
, ASG.PERF_REVIEW_PERIOD_FREQUENCY
, ASG.PERIOD_OF_SERVICE_ID
, ASG.PROBATION_PERIOD
, ASG.PROBATION_UNIT
, ASG.SAL_REVIEW_PERIOD
, ASG.SAL_REVIEW_PERIOD_FREQUENCY
, ASG.SET_OF_BOOKS_ID
, ASG.SOURCE_TYPE
, ASG.TIME_NORMAL_FINISH
, ASG.TIME_NORMAL_START
, ASG.REQUEST_ID
, ASG.PROGRAM_APPLICATION_ID
, ASG.PROGRAM_ID
, ASG.PROGRAM_UPDATE_DATE
, ASG.TITLE
, ASG.OBJECT_VERSION_NUMBER
, ASG.BARGAINING_UNIT_CODE
, ASG.LABOUR_UNION_MEMBER_FLAG
, ASG.HOURLY_SALARIED_CODE
, ASG.CONTRACT_ID
, ASG.COLLECTIVE_AGREEMENT_ID
, ASG.CAGR_ID_FLEX_NUM
, ASG.CAGR_GRADE_DEF_ID
, ASG.ESTABLISHMENT_ID
FROM PER_ALL_ASSIGNMENTS_F ASG
, (SELECT ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, HRI_EDW_FCT_WRK_ACTVTY.GET_HIRE_DAYS(ASS.PERSON_ID
, ASS.EFFECTIVE_START_DATE) EMPLOYMENT_START
, LASG.EFFECTIVE_END_DATE ASSIGNMENT_END
, PPS.DATE_START PPS_DATE_START
, PPS.ACTUAL_TERMINATION_DATE PPS_ACTUAL_TERMINATION_DATE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, INST.INSTANCE_CODE INSTANCE_CODE
, PASG.ORGANIZATION_ID PASG_ORGANIZATION_ID
, PASG.GRADE_ID PASG_GRADE_ID
, PASG.JOB_ID PASG_JOB_ID
, PASG.POSITION_ID PASG_POSITION_ID
, PASG.LOCATION_ID PASG_LOCATION_ID
, DECODE(ASS.ASSIGNMENT_TYPE
, 'A'
, TO_NUMBER(NULL)
, DECODE(PASG.ASSIGNMENT_TYPE
, 'A'
, 1
, DECODE(PASG.ASSIGNMENT_ID
, TO_NUMBER(NULL)
, 1
, DECODE(NVL(PASG.JOB_ID
, -1)
, NVL(ASS.JOB_ID
, -1)
, 0
, 1)))) JOB_CHANGE_FLAG
, DECODE(ASS.ASSIGNMENT_TYPE
, 'A'
, TO_NUMBER(NULL)
, DECODE(PASG.ASSIGNMENT_TYPE
, 'A'
, 1
, DECODE(PASG.ASSIGNMENT_ID
, TO_NUMBER(NULL)
, 1
, DECODE(NVL(PASG.GRADE_ID
, -1)
, NVL(ASS.GRADE_ID
, -1)
, 0
, 1)))) GRD_CHANGE_FLAG
, DECODE(ASS.ASSIGNMENT_TYPE
, 'A'
, TO_NUMBER(NULL)
, DECODE(PASG.ASSIGNMENT_TYPE
, 'A'
, 1
, DECODE(PASG.ASSIGNMENT_ID
, TO_NUMBER(NULL)
, 1
, DECODE(NVL(PASG.POSITION_ID
, -1)
, NVL(ASS.POSITION_ID
, -1)
, 0
, 1)))) POS_CHANGE_FLAG
, DECODE(ASS.ASSIGNMENT_TYPE
, 'A'
, TO_NUMBER(NULL)
, DECODE(PASG.ASSIGNMENT_TYPE
, 'A'
, 1
, DECODE(PASG.ORGANIZATION_ID
, TO_NUMBER(NULL)
, 1
, ASS.ORGANIZATION_ID
, 0
, 1))) ORG_CHANGE_FLAG
, DECODE(ASS.ASSIGNMENT_TYPE
, 'A'
, TO_NUMBER(NULL)
, DECODE(PASG.ASSIGNMENT_TYPE
, 'A'
, 1
, DECODE(PASG.ASSIGNMENT_ID
, TO_NUMBER(NULL)
, 1
, DECODE(NVL(PASG.LOCATION_ID
, -1)
, NVL(ASS.LOCATION_ID
, -1)
, 0
, 1)))) GEOG_CHANGE_FLAG
, DECODE(PPS.DATE_START
, ASS.EFFECTIVE_START_DATE
, 1
, 0) EMPLYMNT_START_FLAG
, DECODE(LASG.EFFECTIVE_END_DATE
, TO_DATE(NULL)
, 0
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, 0
, ASS.EFFECTIVE_END_DATE
, 1
, 0) ASSGNMNT_ENDED_FLAG
, 'NA_EDW-' || INST.INSTANCE_CODE || '-NA_EDW-' || INST.INSTANCE_CODE || '-NA_EDW-' || INST.INSTANCE_CODE NA_EDW_PK
, NVL(ASS.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY')) LAST_UPDATE_DATE
, ASS.CREATION_DATE CREATION_DATE
FROM PER_ALL_ASSIGNMENTS_F ASS
, PER_ALL_ASSIGNMENTS_F PASG
, PER_ALL_ASSIGNMENTS_F LASG
, PER_ALL_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
, PER_ASSIGNMENT_STATUS_TYPES AST
, EDW_LOCAL_INSTANCE INST
WHERE ASS.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID (+)
AND ASS.EFFECTIVE_START_DATE-1 = PASG.EFFECTIVE_END_DATE (+)
AND ASS.ASSIGNMENT_TYPE IN ('E'
, 'A')
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASS.PERSON_ID = PEO.PERSON_ID
AND ASS.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASS.ASSIGNMENT_STATUS_TYPE_ID
AND ASS.ASSIGNMENT_ID = LASG.ASSIGNMENT_ID /* LATEST NON-TERMINATED ASSIGNMENT */
AND LASG.EFFECTIVE_START_DATE = (SELECT MAX(ASS2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASS2
, PER_ASSIGNMENT_STATUS_TYPES AST2
WHERE ASS2.ASSIGNMENT_ID = ASS.ASSIGNMENT_ID
AND ASS2.ASSIGNMENT_STATUS_TYPE_ID = AST2.ASSIGNMENT_STATUS_TYPE_ID
AND ASS2.ASSIGNMENT_TYPE IN ('E'
, 'A')
AND AST2.PER_SYSTEM_STATUS <> 'TERM_APL'
AND AST2.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
AND ASS.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID (+) ) CHG
WHERE ASG.ASSIGNMENT_ID = CHG.ASSIGNMENT_ID
AND ASG.EFFECTIVE_START_DATE = CHG.EFFECTIVE_START_DATE WITH READ ONLY

Columns