VIEW: APPS.HRI_OBI_MDS_WRKFC_QTR_V

Source

View Text - Preformatted

SELECT to_number(null) wevt_evtypcmb_fk ,fct.asg_assgnmnt_fk ,fct.per_person_fk ,fct.per_person_mgr_fk ,hri_oltp_view_wrkfrc.get_sup_fk (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') - 1) per_person_mgr_prv_fk ,fct.mgr_mngrsc_fk ,hri_oltp_view_wrkfrc.get_mgrsc_fk (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') - 1) mgr_mngrsc_prv_fk ,fct.org_organztn_fk ,hri_oltp_view_wrkfrc.get_org_fk (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') - 1) org_organztn_prv_fk ,fct.job_job_fk ,hri_oltp_view_wrkfrc.get_job_fk (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') - 1) job_job_prv_fk ,fct.grd_grade_fk ,hri_oltp_view_wrkfrc.get_grd_fk (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') - 1) grd_grade_prv_fk ,fct.pos_position_fk ,hri_oltp_view_wrkfrc.get_pos_fk (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') - 1) pos_position_prv_fk ,fct.geo_location_fk ,hri_oltp_view_wrkfrc.get_loc_fk (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') - 1) geo_location_prv_fk ,fct.asgrsn_asgrsn_fk ,fct.sprn_sprtnrsn_fk ,fct.scat_spcatgry_fk ,fct.ptyp_pertyp_fk ,fct.prfm_perfband_fk ,fct.pow_powband_fk ,fct.time_day_mnth_end_fk ,fct.cur_currency_fk ,to_number(null) headcount_start ,fct.headcount_end ,to_number(null) fte_start ,fct.fte_end ,fct.pow_days_on_end_date * fct.employee_ind pos_days_on_end_date ,fct.pow_months_on_end_date * fct.employee_ind pos_months_on_end_date ,(fct.pow_months_on_end_date / 12) * fct.employee_ind pos_years_on_end_date ,fct.pow_days_on_end_date * fct.contingent_ind pop_days_on_end_date ,fct.pow_months_on_end_date * fct.contingent_ind pop_months_on_end_date ,(fct.pow_months_on_end_date / 12) * fct.contingent_ind pop_years_on_end_date ,fct.days_since_last_prmtn ,fct.months_since_last_prmtn ,fct.months_since_last_prmtn / 12 years_since_last_prmtn ,to_number(null) anl_slry_start ,fct.anl_slry_end ,hri_oltp_view_wrkfrc.get_hire_hdc (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) headcount_hire ,fct.headcount_term ,fct.headcount_sep_vol ,fct.headcount_sep_invol ,hri_oltp_view_wrkfrc.get_prmtn_hdc (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) headcount_prmtn_emp ,to_number(null) headcount_start_emp ,fct.headcount_end * fct.employee_ind headcount_end_emp ,hri_oltp_view_wrkfrc.get_hire_hdc (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) * fct.employee_ind headcount_hire_emp ,fct.headcount_term * fct.employee_ind headcount_term_emp ,to_number(null) headcount_start_cwk ,fct.headcount_end * fct.contingent_ind headcount_end_cwk ,hri_oltp_view_wrkfrc.get_hire_hdc (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) * fct.contingent_ind headcount_hire_cwk ,fct.headcount_term * fct.contingent_ind headcount_term_cwk ,hri_oltp_view_wrkfrc.get_hire_fte (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) fte_hire ,fct.fte_term ,fct.fte_sep_vol ,fct.fte_sep_invol ,hri_oltp_view_wrkfrc.get_prmtn_fte (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) fte_prmtn_emp ,to_number(null) fte_start_emp ,fct.fte_end * fct.employee_ind fte_end_emp ,hri_oltp_view_wrkfrc.get_hire_fte (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) * fct.employee_ind fte_hire_emp ,fct.fte_term * fct.employee_ind fte_term_emp ,to_number(null) fte_start_cwk ,fct.fte_end * fct.contingent_ind fte_end_cwk ,hri_oltp_view_wrkfrc.get_hire_fte (fct.asg_assgnmnt_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') ,fct.time_day_mnth_end_fk) * fct.contingent_ind fte_hire_cwk ,fct.fte_term * fct.contingent_ind fte_term_cwk ,fct.count_pasg_end * fct.employee_ind count_pasg_end_emp ,to_number(null) count_pasg_hire_emp ,fct.count_pasg_term * fct.employee_ind count_pasg_term_emp ,fct.count_asg_end * fct.employee_ind count_asg_end_emp ,to_number(null) count_asg_hire_emp ,fct.count_asg_term * fct.employee_ind count_asg_term_emp ,fct.count_pasg_end * fct.contingent_ind count_pasg_end_cwk ,to_number(null) count_pasg_hire_cwk ,fct.count_pasg_term * fct.contingent_ind count_pasg_term_cwk ,fct.count_asg_end * fct.contingent_ind count_asg_end_cwk ,to_number(null) count_asg_hire_cwk ,fct.count_asg_term * fct.contingent_ind count_asg_term_cwk ,fct.count_pasg_sep_vol ,fct.count_pasg_sep_invol ,fct.count_asg_sep_vol ,fct.count_asg_sep_invol ,to_number(null) count_asg_prmtn_emp ,fct.time_quarter_fk ,trunc(fct.time_day_mnth_end_fk, 'Q') time_day_qtr_start_fk ,add_months(trunc(fct.time_day_mnth_end_fk, 'Q'), 12) - 1 time_day_qtr_end_fk ,hri_oltp_view_currency.convert_to_primary_crnc (fct.cur_currency_fk, anl_slry_start) anl_slry_start_prm ,hri_oltp_view_currency.convert_to_primary_crnc (fct.cur_currency_fk, anl_slry_end) anl_slry_end_prm ,hri_oltp_view_currency.convert_to_secondary_crnc (fct.cur_currency_fk, anl_slry_start) anl_slry_start_sec ,hri_oltp_view_currency.convert_to_secondary_crnc (fct.cur_currency_fk, anl_slry_end) anl_slry_end_sec FROM hri_mds_wrkfc_mnth_ct fct WHERE fct.last_month_in_qtr_ind = 1

View Text - HTML Formatted

SELECT TO_NUMBER(NULL) WEVT_EVTYPCMB_FK
, FCT.ASG_ASSGNMNT_FK
, FCT.PER_PERSON_FK
, FCT.PER_PERSON_MGR_FK
, HRI_OLTP_VIEW_WRKFRC.GET_SUP_FK (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') - 1) PER_PERSON_MGR_PRV_FK
, FCT.MGR_MNGRSC_FK
, HRI_OLTP_VIEW_WRKFRC.GET_MGRSC_FK (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') - 1) MGR_MNGRSC_PRV_FK
, FCT.ORG_ORGANZTN_FK
, HRI_OLTP_VIEW_WRKFRC.GET_ORG_FK (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') - 1) ORG_ORGANZTN_PRV_FK
, FCT.JOB_JOB_FK
, HRI_OLTP_VIEW_WRKFRC.GET_JOB_FK (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') - 1) JOB_JOB_PRV_FK
, FCT.GRD_GRADE_FK
, HRI_OLTP_VIEW_WRKFRC.GET_GRD_FK (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') - 1) GRD_GRADE_PRV_FK
, FCT.POS_POSITION_FK
, HRI_OLTP_VIEW_WRKFRC.GET_POS_FK (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') - 1) POS_POSITION_PRV_FK
, FCT.GEO_LOCATION_FK
, HRI_OLTP_VIEW_WRKFRC.GET_LOC_FK (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') - 1) GEO_LOCATION_PRV_FK
, FCT.ASGRSN_ASGRSN_FK
, FCT.SPRN_SPRTNRSN_FK
, FCT.SCAT_SPCATGRY_FK
, FCT.PTYP_PERTYP_FK
, FCT.PRFM_PERFBAND_FK
, FCT.POW_POWBAND_FK
, FCT.TIME_DAY_MNTH_END_FK
, FCT.CUR_CURRENCY_FK
, TO_NUMBER(NULL) HEADCOUNT_START
, FCT.HEADCOUNT_END
, TO_NUMBER(NULL) FTE_START
, FCT.FTE_END
, FCT.POW_DAYS_ON_END_DATE * FCT.EMPLOYEE_IND POS_DAYS_ON_END_DATE
, FCT.POW_MONTHS_ON_END_DATE * FCT.EMPLOYEE_IND POS_MONTHS_ON_END_DATE
, (FCT.POW_MONTHS_ON_END_DATE / 12) * FCT.EMPLOYEE_IND POS_YEARS_ON_END_DATE
, FCT.POW_DAYS_ON_END_DATE * FCT.CONTINGENT_IND POP_DAYS_ON_END_DATE
, FCT.POW_MONTHS_ON_END_DATE * FCT.CONTINGENT_IND POP_MONTHS_ON_END_DATE
, (FCT.POW_MONTHS_ON_END_DATE / 12) * FCT.CONTINGENT_IND POP_YEARS_ON_END_DATE
, FCT.DAYS_SINCE_LAST_PRMTN
, FCT.MONTHS_SINCE_LAST_PRMTN
, FCT.MONTHS_SINCE_LAST_PRMTN / 12 YEARS_SINCE_LAST_PRMTN
, TO_NUMBER(NULL) ANL_SLRY_START
, FCT.ANL_SLRY_END
, HRI_OLTP_VIEW_WRKFRC.GET_HIRE_HDC (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) HEADCOUNT_HIRE
, FCT.HEADCOUNT_TERM
, FCT.HEADCOUNT_SEP_VOL
, FCT.HEADCOUNT_SEP_INVOL
, HRI_OLTP_VIEW_WRKFRC.GET_PRMTN_HDC (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) HEADCOUNT_PRMTN_EMP
, TO_NUMBER(NULL) HEADCOUNT_START_EMP
, FCT.HEADCOUNT_END * FCT.EMPLOYEE_IND HEADCOUNT_END_EMP
, HRI_OLTP_VIEW_WRKFRC.GET_HIRE_HDC (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) * FCT.EMPLOYEE_IND HEADCOUNT_HIRE_EMP
, FCT.HEADCOUNT_TERM * FCT.EMPLOYEE_IND HEADCOUNT_TERM_EMP
, TO_NUMBER(NULL) HEADCOUNT_START_CWK
, FCT.HEADCOUNT_END * FCT.CONTINGENT_IND HEADCOUNT_END_CWK
, HRI_OLTP_VIEW_WRKFRC.GET_HIRE_HDC (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) * FCT.CONTINGENT_IND HEADCOUNT_HIRE_CWK
, FCT.HEADCOUNT_TERM * FCT.CONTINGENT_IND HEADCOUNT_TERM_CWK
, HRI_OLTP_VIEW_WRKFRC.GET_HIRE_FTE (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) FTE_HIRE
, FCT.FTE_TERM
, FCT.FTE_SEP_VOL
, FCT.FTE_SEP_INVOL
, HRI_OLTP_VIEW_WRKFRC.GET_PRMTN_FTE (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) FTE_PRMTN_EMP
, TO_NUMBER(NULL) FTE_START_EMP
, FCT.FTE_END * FCT.EMPLOYEE_IND FTE_END_EMP
, HRI_OLTP_VIEW_WRKFRC.GET_HIRE_FTE (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) * FCT.EMPLOYEE_IND FTE_HIRE_EMP
, FCT.FTE_TERM * FCT.EMPLOYEE_IND FTE_TERM_EMP
, TO_NUMBER(NULL) FTE_START_CWK
, FCT.FTE_END * FCT.CONTINGENT_IND FTE_END_CWK
, HRI_OLTP_VIEW_WRKFRC.GET_HIRE_FTE (FCT.ASG_ASSGNMNT_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, FCT.TIME_DAY_MNTH_END_FK) * FCT.CONTINGENT_IND FTE_HIRE_CWK
, FCT.FTE_TERM * FCT.CONTINGENT_IND FTE_TERM_CWK
, FCT.COUNT_PASG_END * FCT.EMPLOYEE_IND COUNT_PASG_END_EMP
, TO_NUMBER(NULL) COUNT_PASG_HIRE_EMP
, FCT.COUNT_PASG_TERM * FCT.EMPLOYEE_IND COUNT_PASG_TERM_EMP
, FCT.COUNT_ASG_END * FCT.EMPLOYEE_IND COUNT_ASG_END_EMP
, TO_NUMBER(NULL) COUNT_ASG_HIRE_EMP
, FCT.COUNT_ASG_TERM * FCT.EMPLOYEE_IND COUNT_ASG_TERM_EMP
, FCT.COUNT_PASG_END * FCT.CONTINGENT_IND COUNT_PASG_END_CWK
, TO_NUMBER(NULL) COUNT_PASG_HIRE_CWK
, FCT.COUNT_PASG_TERM * FCT.CONTINGENT_IND COUNT_PASG_TERM_CWK
, FCT.COUNT_ASG_END * FCT.CONTINGENT_IND COUNT_ASG_END_CWK
, TO_NUMBER(NULL) COUNT_ASG_HIRE_CWK
, FCT.COUNT_ASG_TERM * FCT.CONTINGENT_IND COUNT_ASG_TERM_CWK
, FCT.COUNT_PASG_SEP_VOL
, FCT.COUNT_PASG_SEP_INVOL
, FCT.COUNT_ASG_SEP_VOL
, FCT.COUNT_ASG_SEP_INVOL
, TO_NUMBER(NULL) COUNT_ASG_PRMTN_EMP
, FCT.TIME_QUARTER_FK
, TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q') TIME_DAY_QTR_START_FK
, ADD_MONTHS(TRUNC(FCT.TIME_DAY_MNTH_END_FK
, 'Q')
, 12) - 1 TIME_DAY_QTR_END_FK
, HRI_OLTP_VIEW_CURRENCY.CONVERT_TO_PRIMARY_CRNC (FCT.CUR_CURRENCY_FK
, ANL_SLRY_START) ANL_SLRY_START_PRM
, HRI_OLTP_VIEW_CURRENCY.CONVERT_TO_PRIMARY_CRNC (FCT.CUR_CURRENCY_FK
, ANL_SLRY_END) ANL_SLRY_END_PRM
, HRI_OLTP_VIEW_CURRENCY.CONVERT_TO_SECONDARY_CRNC (FCT.CUR_CURRENCY_FK
, ANL_SLRY_START) ANL_SLRY_START_SEC
, HRI_OLTP_VIEW_CURRENCY.CONVERT_TO_SECONDARY_CRNC (FCT.CUR_CURRENCY_FK
, ANL_SLRY_END) ANL_SLRY_END_SEC
FROM HRI_MDS_WRKFC_MNTH_CT FCT
WHERE FCT.LAST_MONTH_IN_QTR_IND = 1