HR_COMP_DESCRS

(SQL View)
 Index Back

Compensation Fields Descrs

This view is used to get the descriptions of important fields used in Compensation from latest job data row for all employees.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.COMPANY , %Coalesce(( SELECT A1.DESCR FROM PS_COMPANY_TBL A1 WHERE A.COMPANY = A1.COMPANY AND A1.EFF_STATUS = 'A' AND A1.EFFDT = ( SELECT MAX(A11.EFFDT) FROM PS_COMPANY_TBL A11 WHERE A11.COMPANY = A1.COMPANY AND A11.EFF_STATUS=A1.EFF_STATUS AND A11.EFFDT <= A.EFFDT)),' ') , A.BUSINESS_UNIT ,%Coalesce(( SELECT A2.DESCR FROM PS_BUS_UNIT_TBL_HR A2 WHERE A.BUSINESS_UNIT = A2.BUSINESS_UNIT),' ') , A.DEPTID , %Coalesce(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND A3.EFFDT = ( SELECT MAX(A31.EFFDT) FROM PS_DEPT_TBL A31 WHERE A3.SETID = A31.SETID AND A3.DEPTID = A31.DEPTID AND A31.EFFDT <= (A.EFFDT))),' ') , A.JOBCODE , %Coalesce(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND A4.EFFDT = ( SELECT MAX(A41.EFFDT) FROM PS_JOBCODE_TBL A41 WHERE A4.SETID = A41.SETID AND A4.JOBCODE = A41.JOBCODE AND A41.EFFDT <= (A.EFFDT))),' ') , A.LOCATION , %Coalesce(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_DEPT = A5.SETID AND A.LOCATION = A5.LOCATION AND A5.EFFDT = ( SELECT MAX(A51.EFFDT) FROM PS_LOCATION_TBL A51 WHERE A5.SETID = A51.SETID AND A5.LOCATION = A51.LOCATION AND A51.EFFDT <= (A.EFFDT))),' ') , A.POSITION_NBR , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR=A61.POSITION_NBR AND A61.EFFDT <= (A.EFFDT))),' ') ,A.FULL_PART_TIME , ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND A7.EFFDT = ( SELECT MAX(A71.EFFDT) FROM PSXLATITEM A71 WHERE A71.FIELDNAME = A7.FIELDNAME AND A71.FIELDVALUE = A7.FIELDVALUE AND A71.EFF_STATUS=A7.EFF_STATUS AND A71.EFFDT <= (A.EFFDT)) ) , A.PER_ORG , %Coalesce(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND A8.EFFDT = ( SELECT MAX(A81.EFFDT) FROM PSXLATITEM A81 WHERE A81.FIELDNAME = A8.FIELDNAME AND A81.FIELDVALUE = A8.FIELDVALUE AND A81.EFF_STATUS=A8.EFF_STATUS AND A81.EFFDT <= (A.EFFDT))),' ') , A.PAYGROUP , %Coalesce(( SELECT A11.DESCR FROM PS_PAYGROUP_TBL A11 WHERE A11.COMPANY = A.COMPANY AND A11.PAYGROUP = A.PAYGROUP AND A11.EFF_STATUS = 'A' AND A11.EFFDT = ( SELECT MAX(A111.EFFDT) FROM PS_PAYGROUP_TBL A111 WHERE A111.COMPANY = A11.COMPANY AND A111.PAYGROUP = A11.PAYGROUP AND A111.EFF_STATUS=A11.EFF_STATUS AND A111.EFFDT <= (A.EFFDT))),' ') , A.SAL_ADMIN_PLAN , A.REG_REGION , A.ESTABID , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND A12.EFFDT = ( SELECT MAX(A121.EFFDT) FROM PS_ESTAB_TBL A121 WHERE A121.ESTABID = A12.ESTABID AND A121.EFF_STATUS=A12.EFF_STATUS AND A121.EFFDT <= (A.EFFDT))),' ') , B.BIRTHDATE , C.NAME_DISPLAY , C.SEX , ( SELECT A13.XLATLONGNAME FROM PSXLATITEM A13 WHERE A13.FIELDNAME = 'SEX' AND A13.FIELDVALUE = C.SEX AND A13.EFF_STATUS = 'A' AND A13.EFFDT = ( SELECT MAX(A131.EFFDT) FROM PSXLATITEM A131 WHERE A131.FIELDNAME = A13.FIELDNAME AND A131.FIELDVALUE = A13.FIELDVALUE AND A131.EFF_STATUS=A13.EFF_STATUS AND A131.EFFDT <= (A.EFFDT)) ) , C.MAR_STATUS , ( SELECT A14.XLATLONGNAME FROM PSXLATITEM A14 WHERE A14.FIELDNAME = 'MAR_STATUS' AND A14.FIELDVALUE = C.MAR_STATUS AND A14.EFF_STATUS = 'A' AND A14.EFFDT = ( SELECT MAX(A141.EFFDT) FROM PSXLATITEM A141 WHERE A141.FIELDNAME = A14.FIELDNAME AND A14.FIELDVALUE = A141.FIELDVALUE AND A141.EFF_STATUS=A14.EFF_STATUS AND A141.EFFDT <= (A.EFFDT)) ) , CASE WHEN (%Round((%datediff(C.BIRTHDATE,%currentdatein))/365 ,0)) < 25 THEN '<25' ELSE CASE WHEN (%Round((%datediff(C.BIRTHDATE,%currentdatein))/365 ,0)) BETWEEN 25 AND 34 THEN '25-34' ELSE CASE WHEN (%Round((%datediff(C.BIRTHDATE,%currentdatein))/365 ,0)) BETWEEN 35 AND 44 THEN '35-44' ELSE CASE WHEN (%Round((%datediff(C.BIRTHDATE,%currentdatein))/365 ,0)) BETWEEN 45 AND 54 THEN '45-54' ELSE CASE WHEN (%Round((%datediff(C.BIRTHDATE,%currentdatein))/365 ,0)) BETWEEN 55 AND 64 THEN '55-64' ELSE CASE WHEN (%Round((%datediff(C.BIRTHDATE,%currentdatein))/365 ,0)) >= 65 THEN '65+' ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30165) END END END END END END , A.ACTION , A.ACTION_REASON , %Coalesce(( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.EFF_STATUS = ACT1.EFF_STATUS AND ACT1.EFFDT <= %CurrentDateIn)),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_DEPT AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID=SA1.SETID AND SA.SAL_ADMIN_PLAN=SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS=SA1.EFF_STATUS AND SA1.EFFDT <= (A.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = A.REG_REGION),' ') , A.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID),' '),A.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR=A61.POSITION_NBR AND A61.EFFDT <= (A.EFFDT))),' '),A.HR_STATUS ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),%DateNull ,%DateNull FROM PS_JOB A , PS_PERSON B , PS_PERSONAL_DATA C WHERE A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_JOB AA WHERE A.EMPLID = AA.EMPLID AND A.EMPL_RCD = AA.EMPL_RCD AND AA.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A1.EFFSEQ) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EFFDT = A.EFFDT AND A1.EMPL_RCD = A.EMPL_RCD) AND A.PER_ORG IN ('EMP','CWR','POI')

  • Related Language Record: HR_COMP_DESCR_L

    • # 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 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    6 COMPANY_NAME Character(30) VARCHAR2(30) NOT NULL Company Name
    7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: BUS_UNIT_TBL_HR

    8 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    10 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    11 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    12 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    13 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    14 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    15 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    16 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    17 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    18 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    19 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
    CWR=Contingent Worker
    EMP=Employee
    POI=Person of Interest
    20 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    21 PAYGROUP Character(3) VARCHAR2(3) NOT NULL North American Pay Group
    22 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    23 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    24 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    25 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
    26 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    27 BIRTHDATE Date(10) DATE Date of Birth
    28 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    29 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    30 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    31 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
    32 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    33 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description
    34 ACTION Character(3) VARCHAR2(3) NOT NULL Action
    35 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
    36 DESCR100_2 Character(100) VARCHAR2(100) NOT NULL Description
    37 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    38 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    39 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    40 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    41 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    42 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    43 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    44 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    45 FROM_DT Date(10) DATE From Date
    46 TO_DT Date(10) DATE To Date