HR_PG_POS_DT_VW

(SQL View)
 Index Back

Position Data View for PG

POSITION_DATA is the main data record for the Position Management application. It is also used in Succession Planning to establish key positions. If Position Management is installed, data contained in LIKE field names is copied across to the Job record as Position Data changes. /* PLEASE NOTE: The PeopleCode currently on ADDS_TO_FTE.SaveEdit MUST be located on the last data field on the record. If you modify Position Management, new fields must be added above this logic, or the logic moved accordingly. */

SELECT A.POSITION_NBR ,A.EFFDT ,A.EFF_STATUS ,A.DESCR ,A.DESCRSHORT ,A.ACTION ,A.ACTION_REASON ,( SELECT DISTINCT(ACTRSN.DESCR) FROM PS_ACTN_REASON_TBL ACTRSN WHERE ACTRSN.ACTION=A.ACTION AND ACTRSN.ACTION_REASON=A.ACTION_REASON AND ACTRSN.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ACTN_REASON_TBL ACTRSN1 WHERE ACTRSN.ACTION = ACTRSN1.ACTION AND ACTRSN.ACTION_REASON = ACTRSN1.ACTION_REASON AND ACTRSN1.EFFDT <= A.EFFDT AND ACTRSN1.EFF_STATUS = 'A') ) ACTION_REASON_DESCR ,A.ACTION_DT ,A.POSN_STATUS ,A.REG_REGION ,A.BUSINESS_UNIT ,A.COMPANY ,( SELECT DISTINCT(COMP.DESCR) FROM PS_COMPANY_TBL COMP WHERE COMP.COMPANY=A.COMPANY AND COMP.EFFDT = ( SELECT MAX(EFFDT) FROM PS_COMPANY_TBL COMP1 WHERE COMP.COMPANY = COMP1.COMPANY AND COMP1.EFFDT <= A.EFFDT AND COMP1.EFF_STATUS = 'A') ) COMPANY_NAME ,A.REG_TEMP ,A.JOBCODE, ( SELECT DISTINCT(JOBCD.DESCR) FROM PS_JOBCODE_TBL JOBCD WHERE JOBCD.JOBCODE=A.JOBCODE AND JOBCD.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = A.BUSINESS_UNIT AND REC_GROUP_ID = 'HR_02' AND RECNAME = 'JOBCODE_TBL') AND JOBCD.EFFDT = ( SELECT MAX(JOBCD1.EFFDT) FROM PS_JOBCODE_TBL JOBCD1 WHERE JOBCD.SETID = JOBCD1.SETID AND JOBCD.JOBCODE = JOBCD1.JOBCODE AND JOBCD1.EFFDT <= A.EFFDT AND JOBCD1.EFF_STATUS = 'A') ) JOBCODE ,A.DEPTID ,( SELECT DISTINCT(DEPT.DESCR) FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID=A.DEPTID AND DEPT.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = A.BUSINESS_UNIT AND REC_GROUP_ID = 'HR_01' AND RECNAME = 'DEPT_TBL') AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= A.EFFDT AND DEPT1.EFF_STATUS = 'A') ) DEPARTMENT ,A.UNION_CD ,( SELECT UT.DESCR FROM PS_UNION_TBL UT WHERE UT.UNION_CD = A.UNION_CD AND UT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_UNION_TBL UT1 WHERE UT.UNION_CD = UT1.UNION_CD AND UT.EFFDT <= A.EFFDT AND UT.EFF_STATUS = 'A') ) UNIONCODE ,A.MAX_HEAD_COUNT ,A.BUDGETED_POSN ,A.CONFIDENTIAL_POSN ,A.JOB_SHARE ,A.AVAIL_TELEWORK_POS ,A.REPORTS_TO , A.LOCATION, ( SELECT DISTINCT(LOC.DESCR) FROM PS_LOCATION_TBL LOC WHERE LOC.LOCATION=A.LOCATION AND LOC.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = A.BUSINESS_UNIT AND REC_GROUP_ID = 'HR_03' AND RECNAME = 'LOCATION_TBL') AND LOC.EFFDT = ( SELECT MAX(LOC1.EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC.SETID = LOC1.SETID AND LOC.LOCATION = LOC1.LOCATION AND LOC1.EFFDT <= A.EFFDT AND LOC1.EFF_STATUS = 'A') ) LOCATION_DESCR ,A.FULL_PART_TIME, CURCNT.CURR_HEAD_COUNT,CASE WHEN (CURCNT.MAX_HEAD_COUNT- CURCNT.CURR_HEAD_COUNT) >= 0 THEN (CURCNT.MAX_HEAD_COUNT- CURCNT.CURR_HEAD_COUNT) ELSE 0 END VACANTPOS FROM PS_POSITION_DATA A, PS_HR_PG_CURCNT_VW CURCNT WHERE A.POSITION_NBR = CURCNT.POSITION_NBR AND A.EFFDT = CURCNT.EFFDT AND A.EFFDT = ( SELECT MAX(C.EffDt) FROM PS_POSITION_DATA C WHERE C.Position_Nbr = A.Position_Nbr AND C.EffDt <= %CurrentDateIn) AND A.EFF_STATUS = 'A'

  • Related Language Record: HR_PG_POSDT_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

    Default Value: 00000000

    2 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    3 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    6 ACTION Character(3) VARCHAR2(3) NOT NULL Action

    Default Value: POS

    Prompt Table: ACTION_TBL

    7 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

    Prompt Table: ACTN_REASON_TBL

    8 ACTION_REASN_DESCR Character(50) VARCHAR2(50) NOT NULL Action Reason Description
    9 ACTION_DT Date(10) DATE Action Date

    Default Value: %date

    10 POSN_STATUS Character(1) VARCHAR2(1) NOT NULL Position Status
    A=Approved
    P=Proposed
    R=Frozen

    Default Value: A

    11 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

    Default Value: OPR_DEF_TBL_HR.REG_REGION

    Prompt Table: REG_STANDARD_VW

    12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

    Prompt Table: BUSUNIT_HR_VW

    13 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Default Value: DEPT_TBL.COMPANY

    Prompt Table: COMPANY_TBL

    14 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    15 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    R=Regular
    T=Temporary

    Default Value: JOBCODE_TBL.REG_TEMP

    16 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: %EDITTABLE4

    17 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    18 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

    19 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    20 UNION_CD Character(3) VARCHAR2(3) NOT NULL Union Code

    Prompt Table: UNION_TBL

    21 UNION_CODE_DESCR Character(50) VARCHAR2(50) NOT NULL Description of length 50
    22 MAX_HEAD_COUNT Number(4,0) SMALLINT NOT NULL Max Head Count

    Default Value: 1

    23 HR_BUDGETED_POSN Character(1) VARCHAR2(1) NOT NULL Budgeted Position
    N=Not Budgeted
    Y=Budgeted
    24 HR_CONFIDEN_POSN Character(1) VARCHAR2(1) NOT NULL Confidential Position
    N=Not Confidential
    Y=Confidential
    25 HR_JOB_SHARE Character(1) VARCHAR2(1) NOT NULL Job Sharing Permitted
    N=No Job Sharing
    Y=Job Sharing
    26 HR_AVAL_TELWRK_POS Character(1) VARCHAR2(1) NOT NULL Position Available for Telework
    N=Not Available for Telework
    Y=Available for Telework
    27 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

    Prompt Table: POSITION_DATA

    28 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    29 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    30 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time

    Default Value: F

    31 CURR_HEAD_COUNT Number(4,0) SMALLINT NOT NULL Current Head Count

    Default Value: 0

    32 VACANT_POS_COUNT Number(4,0) SMALLINT NOT NULL Vacant Positions

    Default Value: 0