VIEW: APPS.HR_EDWBV_WRK_ACTVTY_FCV

Source

View Text - Preformatted

SELECT 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.effective_start_date <= SYSDATE 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

View Text - HTML Formatted

SELECT 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.EFFECTIVE_START_DATE <= SYSDATE
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