HR_PG_MVMT_HR_I

(SQL View)
 Index Back

HR Admin's Headcount View

This view is used as the base view for HR Admin's Headcount Movement PG.

SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , '0' , JOB.COMPANY , COALESCE(( SELECT CMP.DESCR FROM PS_COMPANY_TBL CMP WHERE JOB.COMPANY = CMP.COMPANY AND CMP.EFF_STATUS = 'A' AND %EffdtCheck(COMPANY_TBL CMP1, CMP, %CurrentDateIn)), ' '), JOB.BUSINESS_UNIT, COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE JOB.BUSINESS_UNIT = BU.BUSINESS_UNIT), ' '), JOB.DEPTID, COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)), ' '), JOB.JOBCODE, COALESCE(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)), ' '), JOB.LOCATION, COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE JOB.SETID_LOCATION = LOC.SETID AND JOB.LOCATION = LOC.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CurrentDateIn)), ' '), JOB.POSITION_NBR, COALESCE(( SELECT POSN.DESCR FROM PS_POSITION_DATA POSN WHERE JOB.POSITION_NBR = POSN.POSITION_NBR AND %EffdtCheck(POSITION_DATA POSN1, POSN, %CurrentDateIn)), ' '), JOB.FULL_PART_TIME, ( SELECT X1.XLATLONGNAME FROM PSXLATITEM X1 WHERE X1.FIELDNAME = 'FULL_PART_TIME' AND X1.FIELDVALUE = JOB.FULL_PART_TIME AND X1.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM X11, X1, %CurrentDateIn)), JOB.PER_ORG, COALESCE(( SELECT X2.XLATLONGNAME FROM PSXLATITEM X2 WHERE X2.FIELDNAME = 'PER_ORG' AND X2.FIELDVALUE = JOB.PER_ORG AND X2.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM X21, X2, %CurrentDateIn)), ' '), JOB.GP_PAYGROUP, COALESCE(( SELECT GPPG.DESCR FROM PS_GP_PYGRP GPPG WHERE GPPG.GP_PAYGROUP = JOB.GP_PAYGROUP), ' '), JOB.PAYGROUP , COALESCE(( SELECT PGRP.DESCR FROM PS_PAYGROUP_TBL PGRP WHERE PGRP.COMPANY = JOB.COMPANY AND PGRP.PAYGROUP = JOB.PAYGROUP AND PGRP.EFF_STATUS = 'A' AND %EffdtCheck(PAYGROUP_TBL PGRP1, PGRP, %CurrentDateIn)), ' '), JOB.SAL_ADMIN_PLAN, JOB.REG_REGION, JOB.ESTABID, COALESCE(( SELECT EST.DESCR FROM PS_ESTAB_TBL EST WHERE EST.ESTABID = JOB.ESTABID AND EST.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL EST1, EST, %CurrentDateIn)), ' '), PER_D.BIRTHDATE, PER_D.NAME_DISPLAY, PER_D.SEX ,( SELECT X3.XLATLONGNAME FROM PSXLATITEM X3 WHERE X3.FIELDNAME = 'SEX' AND X3.FIELDVALUE = PER_D.SEX AND X3.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM X31, X3, %CurrentDateIn)), PER_D.MAR_STATUS, ( SELECT X4.XLATLONGNAME FROM PSXLATITEM X4 WHERE X4.FIELDNAME = 'MAR_STATUS' AND X4.FIELDVALUE = PER_D.MAR_STATUS AND X4.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM X41, X4, %CurrentDateIn)), CASE WHEN (%Round((%DATEDIFF(PER_D.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) < 25 THEN '<25' ELSE CASE WHEN (%Round((%DATEDIFF(PER_D.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 25 AND 34 THEN '25-34' ELSE CASE WHEN (%Round((%DATEDIFF(PER_D.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 35 AND 44 THEN '35-44' ELSE CASE WHEN (%Round((%DATEDIFF(PER_D.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 45 AND 54 THEN '45-54' ELSE CASE WHEN (%Round((%DATEDIFF(PER_D.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 55 AND 64 THEN '55-64' ELSE CASE WHEN (%Round((%DATEDIFF(PER_D.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, JOB.ACTION, JOB.ACTION_REASON, CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END , COALESCE(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_DEPT AND SA.SAL_ADMIN_PLAN = JOB.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 = JOB.REG_REGION), ' '), JOB.SUPERVISOR_ID, COALESCE(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = JOB.SUPERVISOR_ID), ' '), JOB.REPORTS_TO, COALESCE(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)), ' '), JOB.HR_STATUS, ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %DateNull, %DateNull FROM PS_JOB JOB, PS_PERSONAL_DATA PER_D WHERE JOB.EMPLID = PER_D.EMPLID AND JOB.EFFSEQ = ( SELECT MAX(A1.EFFSEQ) FROM PS_JOB A1 WHERE A1.EMPLID = JOB.EMPLID AND A1.EFFDT = JOB.EFFDT AND A1.ACTION = JOB.ACTION AND A1.EMPL_RCD = JOB.EMPL_RCD) AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') UNION ALL SELECT W.EMPLID , W.EMPL_RCD , W.EFFDT , W.EFFSEQ , '1' , W.COMPANY , COALESCE(( SELECT CO1.DESCR FROM PS_COMPANY_TBL CO1 WHERE W.COMPANY = CO1.COMPANY AND CO1.EFF_STATUS = 'A' AND %EffdtCheck(COMPANY_TBL CO2, CO1, %CurrentDateIn)), ' '), W.BUSINESS_UNIT, COALESCE(( SELECT BU1.DESCR FROM PS_BUS_UNIT_TBL_HR BU1 WHERE W.BUSINESS_UNIT = BU1.BUSINESS_UNIT), ' '), W.DEPTID, COALESCE(( SELECT DT1.DESCR FROM PS_DEPT_TBL DT1 WHERE W.SETID_DEPT = DT1.SETID AND W.DEPTID = DT1.DEPTID AND %EffdtCheck(DEPT_TBL DT2, DT1, %CurrentDateIn)), ' '), W.JOBCODE, COALESCE(( SELECT JC3.DESCR FROM PS_JOBCODE_TBL JC3 WHERE W.SETID_DEPT = JC3.SETID AND W.JOBCODE = JC3.JOBCODE AND %EffdtCheck(JOBCODE_TBL JC4, JC3, %CurrentDateIn)), ' '), W.LOCATION, COALESCE(( SELECT LT3.DESCR FROM PS_LOCATION_TBL LT3 WHERE W.SETID_LOCATION = LT3.SETID AND W.LOCATION = LT3.LOCATION AND %EffdtCheck(LOCATION_TBL LT4, LT3, %CurrentDateIn)), ' '), W.POSITION_NBR, COALESCE(( SELECT PD3.DESCR FROM PS_POSITION_DATA PD3 WHERE W.POSITION_NBR = PD3.POSITION_NBR AND %EffdtCheck(POSITION_DATA PD4, PD3, %CurrentDateIn)), ' '), W.FULL_PART_TIME, COALESCE(( SELECT XL5.XLATLONGNAME FROM PSXLATITEM XL5 WHERE XL5.FIELDNAME = 'FULL_PART_TIME' AND XL5.FIELDVALUE = W.FULL_PART_TIME AND XL5.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM XL51, XL5, %CurrentDateIn)), ' '), W.PER_ORG, ( SELECT XL6.XLATLONGNAME FROM PSXLATITEM XL6 WHERE XL6.FIELDNAME = 'PER_ORG' AND XL6.FIELDVALUE = W.PER_ORG AND XL6.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM XL61, XL6, %CurrentDateIn)), W.GP_PAYGROUP, COALESCE(( SELECT PG3.DESCR FROM PS_GP_PYGRP PG3 WHERE PG3.GP_PAYGROUP = W.GP_PAYGROUP), ' '), W.PAYGROUP, COALESCE(( SELECT NPG3.DESCR FROM PS_PAYGROUP_TBL NPG3 WHERE NPG3.COMPANY = W.COMPANY AND NPG3.PAYGROUP = W.PAYGROUP AND NPG3.EFF_STATUS = 'A' AND %EffdtCheck(PAYGROUP_TBL NPG31, NPG3, %CurrentDateIn)), ' '), W.SAL_ADMIN_PLAN, W.REG_REGION, W.ESTABID, COALESCE(( SELECT ET3.DESCR FROM PS_ESTAB_TBL ET3 WHERE ET3.ESTABID = W.ESTABID AND ET3.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL ET31, ET3, %CurrentDateIn)), ' ') ,Y.BIRTHDATE, Y.NAME_DISPLAY, Y.SEX, ( SELECT XL7.XLATLONGNAME FROM PSXLATITEM XL7 WHERE XL7.FIELDNAME = 'SEX' AND XL7.FIELDVALUE = Y.SEX AND XL7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM XL71, XL7, %CurrentDateIn)), Y.MAR_STATUS, ( SELECT XL8.XLATLONGNAME FROM PSXLATITEM XL8 WHERE XL8.FIELDNAME = 'MAR_STATUS' AND XL8.FIELDVALUE = Y.MAR_STATUS AND XL8.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM XL81, XL8, %CurrentDateIn)), CASE WHEN (%Round((%DATEDIFF(Y.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) < 25 THEN '<25' ELSE CASE WHEN (%Round((%DATEDIFF(Y.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 25 AND 34 THEN '25-34' ELSE CASE WHEN (%Round((%DATEDIFF(Y.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 35 AND 44 THEN '35-44' ELSE CASE WHEN (%Round((%DATEDIFF(Y.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 45 AND 54 THEN '45-54' ELSE CASE WHEN (%Round((%DATEDIFF(Y.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) BETWEEN 55 AND 64 THEN '55-64' ELSE CASE WHEN (%Round((%DATEDIFF(Y.BIRTHDATE,%CURRENTDATEIN))/365 ,0)) >= 65 THEN '65+' ELSE ( SELECT MC3.MESSAGE_TEXT FROM PSMSGCATDEFN MC3 WHERE MC3.MESSAGE_SET_NBR = 1000 AND MC3.MESSAGE_NBR = 30165) END END END END END END, W.ACTION, W.ACTION_REASON, ( SELECT MC4.MESSAGE_TEXT FROM PSMSGCATDEFN MC4 WHERE MC4.MESSAGE_SET_NBR = 1000 AND MC4.MESSAGE_NBR = 30163), COALESCE(( SELECT SA3.DESCR FROM PS_SAL_PLAN_TBL SA3 WHERE SA3.SETID = W.SETID_DEPT AND SA3.SAL_ADMIN_PLAN = W.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA4, SA3, %CurrentDateIn) AND SA3.EFF_STATUS <> 'I'), ' '), COALESCE(( SELECT RR3.DESCR50 FROM PS_REG_REGION_TBL RR3 WHERE RR3.REG_REGION = W.REG_REGION), ' '), W.SUPERVISOR_ID, COALESCE(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = W.SUPERVISOR_ID),' '), W.REPORTS_TO, COALESCE(( SELECT PD3.DESCR FROM PS_POSITION_DATA PD3 WHERE W.REPORTS_TO = PD3.POSITION_NBR AND %EffdtCheck(POSITION_DATA PD4, PD3, %CurrentDateIn)), ' '), W.HR_STATUS, ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = W.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %DateNull, %DateNull FROM PS_JOB W, PS_PERSONAL_DATA Y WHERE Y.EMPLID = W.EMPLID AND (W.ACTION = 'XFR' OR (W.ACTION = 'POS' AND W.ACTION_REASON = 'XFR'))

  • Related Language Record: HR_PG_MVMT_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 DIRVALUEFLAG Character(1) VARCHAR2(1) NOT NULL Directory Value?
    6 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    7 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: BUS_UNIT_TBL_HR

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