HR_WF_DIVERS_VW

(SQL View)
 Index Back

Workforce Diversity

Workforce Diversity View for HR Analytics

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.HR_STATUS_DESCR , A.NAME , A.COUNTRY , A.STATE , A.CITY , A.POSTAL , A.SEX , A.MAR_STATUS , A.HIGHEST_EDUC_LVL , A.MILITARY_STATUS , A.ETHNIC_GROUP , A.EFFDT , A.REG_TEMP , A.FULL_PART_TIME , %Round(A.ANNUAL_RT,2) , A.CURRENCY_CD , %Coalesce(CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(A.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END, 0) , ' ' , ' ' , A.EEO1CODE , A.EEO4CODE , A.JOBCODE_DESCR , A.POSN_DESCR , A.JOB_DESCR , A.LOCATION_DESCR , A.DEPT_DESCR , A.BUSINESS_DESCR , A.COMPANY_DESCR , A.EMPL_TYPE_DESCR , A.REG_REGION_DESCR , A.MANAGER_NAME , A.DISABILITY_STS_USA , A.DISABLED , A.AGE , A.HR_AGE_RANGE , A.VETERAN_STATUS , %Coalesce((SELECT AGE.DESCR100 FROM PS_HR_PG_WF_DIVERS AGE WHERE AGE.EMPLID = A.EMPLID AND AGE.EMPL_RCD = A.EMPL_RCD AND AGE.HR_DIV_TYPE = 'A'), ' ') , %Coalesce((SELECT DIS.DESCR100 FROM PS_HR_PG_WF_DIVERS DIS WHERE DIS.EMPLID = A.EMPLID AND DIS.EMPL_RCD = A.EMPL_RCD AND DIS.HR_DIV_TYPE = 'D'), ' ') , %Coalesce((SELECT ETH.DESCR100 FROM PS_HR_PG_WF_DIVERS ETH WHERE ETH.EMPLID = A.EMPLID AND ETH.EMPL_RCD = A.EMPL_RCD AND ETH.HR_DIV_TYPE = 'E'), ' ') , %Coalesce((SELECT SEX.DESCR100 FROM PS_HR_PG_WF_DIVERS SEX WHERE SEX.EMPLID = A.EMPLID AND SEX.EMPL_RCD = A.EMPL_RCD AND SEX.HR_DIV_TYPE = 'G'), ' ') , %Coalesce((SELECT VET.DESCR100 FROM PS_HR_PG_WF_DIVERS VET WHERE VET.EMPLID = A.EMPLID AND VET.EMPL_RCD = A.EMPL_RCD AND VET.HR_DIV_TYPE = 'V'), ' ') , A.ASOFDATE FROM PS_HR_PG_WF_DIVERS A LEFT OUTER JOIN PS_HR_TM_GRD_VW GRD ON A.SETID_SALARY = GRD.SETID AND A.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND A.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' WHERE (A.PER_ORG = 'EMP' OR A.PER_ORG = 'CWR')

  • Related Language Record: HR_WF_DIV_LN_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 HR_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL HR Status
    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    6 STATE Character(6) VARCHAR2(6) NOT NULL State
    7 CITY Character(30) VARCHAR2(30) NOT NULL City
    8 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    9 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    10 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
    C=Common-Law
    D=Divorced
    E=Separated
    H=Head of Household
    L=DissDeclLost Civil Partner
    M=Married
    P=Civil Partnership
    S=Single
    T=Surviving Civil Partner
    U=Unknown
    V=Dissolved Civil Partnership
    W=Widowed
    11 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level
    12 MILITARY_STATUS Character(1) VARCHAR2(1) NOT NULL Veteran Status / Military Status
    13 ETHNIC_GROUP Character(1) VARCHAR2(1) NOT NULL Ethnic Group
    1=White
    2=Black/African American
    3=Hispanic/Latino
    4=Asian
    5=American Indian/Alaska Native
    6=Not Applicable
    7=Native Hawaiian/Oth Pac Island
    A=American Indian/Alaskan Native
    B=Asian or Pacific Islander
    C=Black, not of Hispanic origin
    D=Hispanic
    E=White, not of Hispanic origin
    F=Asian Indian
    G=Chinese
    H=Filipino
    J=Guamanian
    K=Hawaiian
    L=Japanese
    M=Korean
    N=Samoan
    P=Vietnamese
    Q=All Other Asian o Pacific Is
    Y=Not Hispanic in Puerto Rico
    14 EFFDT Date(10) DATE Effective Date
    15 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary
    16 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    17 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
    18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    19 COMPA_RATIO Number(4,2) DECIMAL(3,2) NOT NULL Compa-Ratio
    20 EP_RATING Number(6,2) DECIMAL(5,2) NOT NULL Rating
    21 RATING_DESCR Character(30) VARCHAR2(30) NOT NULL Overall Rating Decription
    22 EEO1CODE Character(1) VARCHAR2(1) NOT NULL EEO-1 Job Category
    1=Officials and Managers
    2=Professionals
    3=Technicians
    4=Sales Workers
    5=Administrative Support Workers
    6=Craft Workers
    7=Operatives
    8=Laborers and Helpers
    9=Service Workers
    A=Executive/Sr Level Officials
    B=First/Mid Level Officials
    N=No EEO-1 Reporting
    23 EEO4CODE Character(1) VARCHAR2(1) NOT NULL EEO-4 Job Category
    1=Officials and Administrators
    2=Professionals
    3=Technicians
    4=Protective Service
    5=Paraprofessionals
    6=Administrative Support
    7=Skilled Craft
    8=Service Maintenance
    N=No EEO-4 Reporting
    24 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    25 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    26 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    27 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    28 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    29 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    30 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    31 EMPL_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Employee Type
    32 REG_REGION_DESCR Character(50) VARCHAR2(50) NOT NULL Regulatory Region
    33 MANAGER_NAME Character(30) VARCHAR2(30) NOT NULL Manager Name
    34 DISABILITY_STS_USA Character(1) VARCHAR2(1) NOT NULL Disability (Section 503) Status USA
    N=Not Disabled
    X=Not Indicated
    Y=Disabled
    35 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.
    36 AGE Number(3,0) SMALLINT NOT NULL Age
    37 HR_AGE_RANGE Character(1) VARCHAR2(1) NOT NULL Age Range
    1=<18
    2=18-24
    3=25-34
    4=35-44
    5=45-54
    6=55-64
    7=65+
    N=Not Available
    38 VETERAN_STATUS Character(1) VARCHAR2(1) NOT NULL Veteran Status
    N=Not a Veteran
    X=Not Indicated
    Y=Veteran
    39 AGE_DESCR Character(100) VARCHAR2(100) NOT NULL Description for Diversity Types (Age, Ethnic Disability, Ethnic Group, Gender, and Veteran Status).
    40 DISABLED_STS_DESCR Character(100) VARCHAR2(100) NOT NULL Description for Diversity Types (Age, Ethnic Disability, Ethnic Group, Gender, and Veteran Status).
    41 ETHNIC_GRP_DESCR Character(100) VARCHAR2(100) NOT NULL Description for Diversity Types (Age, Ethnic Disability, Ethnic Group, Gender, and Veteran Status).
    42 GENDER_DESCR Character(100) VARCHAR2(100) NOT NULL Description for Diversity Types (Age, Ethnic Disability, Ethnic Group, Gender, and Veteran Status).
    43 VETERAN_STS_DESCR Character(100) VARCHAR2(100) NOT NULL Description for Diversity Types (Age, Ethnic Disability, Ethnic Group, Gender, and Veteran Status).
    44 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.