HR_PG_CURR_HR_I

(SQL View)
 Index Back

HR Admin's Current HC Profile

This view is used to list the latest job data 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 %EffdtCheck(COMPANY_TBL A11, A1, %CurrentDateIn)), ' '), 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 %EffdtCheck(DEPT_TBL A31, A3, %CurrentDateIn)), ' '), A.JOBCODE, COALESCE(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND %EffdtCheck(JOBCODE_TBL A41, A4, %CurrentDateIn)), ' '), A.LOCATION, COALESCE(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_LOCATION = A5.SETID AND A.LOCATION = A5.LOCATION AND %EffdtCheck(LOCATION_TBL A51, A5, %CurrentDateIn)), ' '), A.POSITION_NBR, COALESCE(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)), ' '), A.FULL_PART_TIME, COALESCE(( 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 %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)), ' ') , 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 %EffdtCheck(PSXLATITEM A81, A8, %CurrentDateIn)), ' '), A.GP_PAYGROUP, COALESCE(( SELECT A10.DESCR FROM PS_GP_PYGRP A10 WHERE A10.GP_PAYGROUP = A.GP_PAYGROUP), ' ') ,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 %EffdtCheck(PAYGROUP_TBL A111, A11, %CurrentDateIn)), ' '), 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 %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)), ' '), C.BIRTHDATE, C.NAME_DISPLAY, C.SEX, COALESCE(( SELECT A13.XLATLONGNAME FROM PSXLATITEM A13 WHERE A13.FIELDNAME = 'SEX' AND A13.FIELDVALUE = C.SEX AND A13.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A131, A13, %CurrentDateIn)), ' '), C.MAR_STATUS, COALESCE(( SELECT A14.XLATLONGNAME FROM PSXLATITEM A14 WHERE A14.FIELDNAME = 'MAR_STATUS' AND A14.FIELDVALUE = C.MAR_STATUS AND A14.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A141, A14, %CurrentDateIn)), ' '), 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 %EffdtCheck(ACTION_TBL ACT1, ACT, %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 %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) 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 A6A.DESCR FROM PS_POSITION_DATA A6A WHERE A.REPORTS_TO = A6A.POSITION_NBR AND %EffdtCheck(POSITION_DATA A6A1, A6A, %CurrentDateIn)), ' '), A.HR_STATUS, ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %DateNull ,%DateNull FROM PS_JOB A, PS_PERSONAL_DATA C WHERE 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(AA1.EFFSEQ) FROM PS_JOB AA1 WHERE AA1.EMPLID = A.EMPLID AND AA1.EFFDT = A.EFFDT AND AA1.EMPL_RCD = A.EMPL_RCD)

  • Related Language Record: HR_PG_CURR_LNG2

    • # 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_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    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 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    22 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    23 PAYGROUP Character(3) VARCHAR2(3) NOT NULL North American Pay Group
    24 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    25 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    26 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    27 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
    28 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    29 BIRTHDATE Date(10) DATE Date of Birth
    30 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    31 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    32 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    33 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
    34 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    35 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description
    36 ACTION Character(3) VARCHAR2(3) NOT NULL Action
    37 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
    38 DESCR100_2 Character(100) VARCHAR2(100) NOT NULL Description
    39 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    40 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    41 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    42 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    43 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    44 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    45 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    46 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    47 FROM_DT Date(10) DATE From Date
    48 TO_DT Date(10) DATE To Date