HRCD_OTHERPER_V

(SQL View)
 Index Back

Co. Directory - Other Per Prpt

Company Directory - User Preferences Prompt view for selecting a person as the starting node

SELECT O.OPRID , T.TREE_NAME , T.EFFDT , PJ.EMPLID , N.NAME_DISPLAY , N.LAST_NAME_SRCH , PJ.BUSINESS_UNIT , PJ.DEPTID , JC.DESCR FROM PSOPRDEFN O , PS_PRIMARY_JOB_VW PJ , PS_PERSON_NAME N , PSTREEDEFN T , PSTREENODE TN , PS_HRCD_JOB_TREE JT , PS_JOBCODE_TBL JC WHERE O.OPRTYPE = 0 AND PJ.EMPLID <> O.EMPLID AND T.TREE_NAME = TN.TREE_NAME AND T.EFFDT = TN.EFFDT AND PJ.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = PJ.EMPLID AND EMPL_RCD = PJ.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND PJ.EMPLID = N.EMPLID AND PJ.HR_STATUS = 'A' AND PJ.EFFDT <= T.EFFDT AND JT.EMPLID = PJ.EMPLID AND JT.EMPL_RCD = PJ.EMPL_RCD AND JT.TREE_NODE = TN.TREE_NODE AND JT.TREE_NODE = ( SELECT MIN(JT1.TREE_NODE) FROM PS_HRCD_JOB_TREE JT1 WHERE JT1.EMPLID = PJ.EMPLID AND JT1.EMPL_RCD = PJ.EMPL_RCD) AND PJ.SETID_JOBCODE = JC.SETID AND PJ.JOBCODE = JC.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCE, JC, %CurrentDateIn) ORDER BY N.NAME_DISPLAY, JC.DESCR

  • Related Language Record: HRCD_OTHPER_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
    3 TREE_EFFDT Date(10) DATE Effective date of the Tree
    4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    5 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    6 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    9 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title