View: HR_EDWBV_WRK_CMPSTN_FCV

View Text

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'
, ASG.EFFECTIVE_START_DATE) COMPOSITION_HEADCOUNT
, HRI_EDW_FCT_RECRUITMENT.CALC_ABV( ASG.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, 'FTE'
, ASG.EFFECTIVE_START_DATE) COMPOSITION_FTE /****************************/ /* OTHER MEASURES */ /****************************/
, NULL LWST_GRD_SLRY
, NULL HGHST_GRD_SLRY
, DECODE(DSD.SALARY_CURRENCY_CODE
, NULL
, -1
, EDW_CURRENCY.GET_RATE(DSD.SALARY_CURRENCY_CODE
, SNP.SNAPSHOT_DATE
, NULL)) CRRNCY_CNVRSN_RATE
, 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

Columns