VIEW: APPS.HR_EDWBV_WRK_CMPSTN_FCV

Source

View Text - Preformatted

SELECT /********************************************************************************/ /* Primary Key of Fact */ /********************************************************************************/ /**/ asg.assignment_id||'-'||snp.snapshot_date||'-'||instance_code composition_pk /**/ /********************************************************************************/ /* 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(asg.effective_start_date, 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( snp.snapshot_date - pps.date_start) service_band_fk /**/ /**************/ /* Assignment */ /**************/ /* Construct assignment_fk manually */ /**/ ,asg.assignment_id || '-' || instance_code assignment_fk /**/ /*************/ /* Currency */ /*************/ /**/ ,dsd.salary_currency_code currency_fk /**/ /*************/ /* Geography */ /*************/ /* Construct geography_fk manually, providing an assignment location exists */ /**/ ,decode(asg.location_id, to_number(null), 'NA_EDW', asg.location_id || '-' || 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 || '-' || instance_code) grade_fk /**/ /************/ /* Instance */ /************/ /* Standard EDW foreign key */ ,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 || '-' || instance_code) job_fk /**/ /****************/ /* Organization */ /****************/ /* Construct organization fk manually */ /**/ ,asg.organization_id || '-' || instance_code organization_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 , snp.snapshot_date) 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 || '-' || instance_code) position_fk /**/ /**********************/ /* Time (Application) */ /**********************/ /* Construct time_application_fk manually */ /**/ ,decode(snp.snapshot_date, to_date(null), 'NA_EDW', to_char(snp.snapshot_date,'dd-mm-yyyy')||'-DAY') time_fk /**/ /********************************************************************************/ /* Hidden Primary Keys */ /********************************************************************************/ ,asg.assignment_id asg_assignment_id ,asg.business_group_id asg_business_group_id ,asg.grade_id asg_grade_id ,asg.job_id asg_job_id ,asg.location_id asg_location_id ,asg.organization_id asg_organization_id ,asg.person_id asg_person_id ,asg.position_id asg_position_id /********************************************************************************/ /* Dates */ /********************************************************************************/ ,snp.snapshot_date snapshot_date ,asg.effective_start_date assignment_start_date ,peo.date_of_birth date_of_birth ,GREATEST( NVL(asg.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(peo.last_update_date,to_date('01-01-2000','DD-MM-YYYY'))) last_update_date ,asg.creation_date creation_date /********************************************************************************/ /* Measures */ /********************************************************************************/ /**/ /****************************/ /* Assignment Budget Values */ /****************************/ ,hri_edw_fct_recruitment.calc_abv( asg.assignment_id, asg.business_group_id, 'HEAD', snp.snapshot_date) composition_headcount ,hri_edw_fct_recruitment.calc_abv( asg.assignment_id, asg.business_group_id, 'FTE', snp.snapshot_date) composition_fte /****************************/ /* other measures */ /****************************/ ,null lwst_grd_slry ,null hghst_grd_slry ,DECODE(dsd.assignment_id, to_number(null), to_number(null), DECODE(dsd.salary_currency_code, NULL, -1, edw_currency.get_rate(dsd.salary_currency_code, snp.snapshot_date, null))) crrncy_cnvrsn_rate ,DECODE(SIGN(edw_currency.get_rate(dsd.salary_currency_code, snp.snapshot_date, null)),-1,NULL, (edw_currency.get_rate(dsd.salary_currency_code, snp.snapshot_date, null) * (dsd.salary))) crnt_annlzed_slry ,dsd.salary crnt_annlzed_slry_bc FROM per_all_assignments_f asg ,per_assignment_status_types ast ,per_all_people_f peo ,edw_local_instance inst ,per_periods_of_service pps ,hri_edw_cmpstn_snpsht_dts snp ,hri_edw_daily_salary_details dsd WHERE asg.assignment_type = 'E' AND asg.assignment_id = dsd.assignment_id (+) AND ast.assignment_status_type_id = asg.assignment_status_type_id AND ast.per_system_status <> 'TERM_ASSIGN' AND asg.person_id = peo.person_id AND snp.snapshot_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND asg.person_id = pps.person_id AND asg.effective_start_date BETWEEN pps.date_start AND NVL(pps.actual_termination_date,snp.snapshot_date) AND snp.snapshot_date BETWEEN asg.effective_start_date AND asg.effective_end_date

View Text - HTML Formatted

SELECT /********************************************************************************/ /* PRIMARY KEY OF FACT */ /********************************************************************************/ /**/ ASG.ASSIGNMENT_ID||'-'||SNP.SNAPSHOT_DATE||'-'||INSTANCE_CODE COMPOSITION_PK /**/ /********************************************************************************/ /* 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(ASG.EFFECTIVE_START_DATE
, 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( SNP.SNAPSHOT_DATE - PPS.DATE_START) SERVICE_BAND_FK /**/ /**************/ /* ASSIGNMENT */ /**************/ /* CONSTRUCT ASSIGNMENT_FK MANUALLY */ /**/
, ASG.ASSIGNMENT_ID || '-' || INSTANCE_CODE ASSIGNMENT_FK /**/ /*************/ /* CURRENCY */ /*************/ /**/
, DSD.SALARY_CURRENCY_CODE CURRENCY_FK /**/ /*************/ /* GEOGRAPHY */ /*************/ /* CONSTRUCT GEOGRAPHY_FK MANUALLY
, PROVIDING AN ASSIGNMENT LOCATION EXISTS */ /**/
, DECODE(ASG.LOCATION_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, ASG.LOCATION_ID || '-' || 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 || '-' || INSTANCE_CODE) GRADE_FK /**/ /************/ /* INSTANCE */ /************/ /* STANDARD EDW FOREIGN KEY */
, 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 || '-' || INSTANCE_CODE) JOB_FK /**/ /****************/ /* ORGANIZATION */ /****************/ /* CONSTRUCT ORGANIZATION FK MANUALLY */ /**/
, ASG.ORGANIZATION_ID || '-' || INSTANCE_CODE ORGANIZATION_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
, SNP.SNAPSHOT_DATE) 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 || '-' || INSTANCE_CODE) POSITION_FK /**/ /**********************/ /* TIME (APPLICATION) */ /**********************/ /* CONSTRUCT TIME_APPLICATION_FK MANUALLY */ /**/
, DECODE(SNP.SNAPSHOT_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(SNP.SNAPSHOT_DATE
, 'DD-MM-YYYY')||'-DAY') TIME_FK /**/ /********************************************************************************/ /* HIDDEN PRIMARY KEYS */ /********************************************************************************/
, ASG.ASSIGNMENT_ID ASG_ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID ASG_BUSINESS_GROUP_ID
, ASG.GRADE_ID ASG_GRADE_ID
, ASG.JOB_ID ASG_JOB_ID
, ASG.LOCATION_ID ASG_LOCATION_ID
, ASG.ORGANIZATION_ID ASG_ORGANIZATION_ID
, ASG.PERSON_ID ASG_PERSON_ID
, ASG.POSITION_ID ASG_POSITION_ID /********************************************************************************/ /* DATES */ /********************************************************************************/
, SNP.SNAPSHOT_DATE SNAPSHOT_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, GREATEST( NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(PEO.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
, ASG.CREATION_DATE CREATION_DATE /********************************************************************************/ /* MEASURES */ /********************************************************************************/ /**/ /****************************/ /* ASSIGNMENT BUDGET VALUES */ /****************************/
, HRI_EDW_FCT_RECRUITMENT.CALC_ABV( ASG.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, 'HEAD'
, SNP.SNAPSHOT_DATE) COMPOSITION_HEADCOUNT
, HRI_EDW_FCT_RECRUITMENT.CALC_ABV( ASG.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, 'FTE'
, SNP.SNAPSHOT_DATE) COMPOSITION_FTE /****************************/ /* OTHER MEASURES */ /****************************/
, NULL LWST_GRD_SLRY
, NULL HGHST_GRD_SLRY
, DECODE(DSD.ASSIGNMENT_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(DSD.SALARY_CURRENCY_CODE
, NULL
, -1
, EDW_CURRENCY.GET_RATE(DSD.SALARY_CURRENCY_CODE
, SNP.SNAPSHOT_DATE
, NULL))) CRRNCY_CNVRSN_RATE
, DECODE(SIGN(EDW_CURRENCY.GET_RATE(DSD.SALARY_CURRENCY_CODE
, SNP.SNAPSHOT_DATE
, NULL))
, -1
, NULL
, (EDW_CURRENCY.GET_RATE(DSD.SALARY_CURRENCY_CODE
, SNP.SNAPSHOT_DATE
, NULL) * (DSD.SALARY))) CRNT_ANNLZED_SLRY
, DSD.SALARY CRNT_ANNLZED_SLRY_BC
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ALL_PEOPLE_F PEO
, EDW_LOCAL_INSTANCE INST
, PER_PERIODS_OF_SERVICE PPS
, HRI_EDW_CMPSTN_SNPSHT_DTS SNP
, HRI_EDW_DAILY_SALARY_DETAILS DSD
WHERE ASG.ASSIGNMENT_TYPE = 'E'
AND ASG.ASSIGNMENT_ID = DSD.ASSIGNMENT_ID (+)
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASG.PERSON_ID = PEO.PERSON_ID
AND SNP.SNAPSHOT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG.PERSON_ID = PPS.PERSON_ID
AND ASG.EFFECTIVE_START_DATE BETWEEN PPS.DATE_START
AND NVL(PPS.ACTUAL_TERMINATION_DATE
, SNP.SNAPSHOT_DATE)
AND SNP.SNAPSHOT_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE