HRCD_OTHPER_LNG

(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 , JC.LANGUAGE_CD , T.TREE_NAME , T.EFFDT , PJ.EMPLID , N.NAME_DISPLAY , N.LAST_NAME_SRCH , JC.DESCR FROM PSOPRDEFN O , PS_PRIMARY_JOB_VW PJ , PS_NAMES_LNG N , PSTREEDEFN T , PSTREENODE TN , PS_HRCD_JOB_TREE JT , PS_JOBCODE_LANG JC WHERE O.OPRTYPE = 0 AND PJ.EMPLID <> O.EMPLID AND N.EMPLID = PJ.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES_LNG NE, N, %CurrentDateIn) 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_LANG JCE, JC, %CurrentDateIn) UNION SELECT O.OPRID , JC.LANGUAGE_CD , T.TREE_NAME , T.EFFDT , PJ.EMPLID , N.NAME_DISPLAY , N.LAST_NAME_SRCH , JC.DESCR FROM PSOPRDEFN O , PS_PRIMARY_JOB_VW PJ , PS_NAMES N , PSTREEDEFN T , PSTREENODE TN , PS_HRCD_JOB_TREE JT , PS_JOBCODE_LANG JC WHERE O.OPRTYPE = 0 AND PJ.EMPLID <> O.EMPLID AND N.EMPLID = PJ.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES NE, N, %CurrentDateIn) 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_LANG JCE, JC, %CurrentDateIn) AND NOT EXISTS ( SELECT 'X' FROM PS_NAMES_LNG NL WHERE NL.EMPLID = PJ.EMPLID AND NL.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES_LNG NLE, NL, %CurrentDateIn)) UNION SELECT O.OPRID , N.LANGUAGE_CD , T.TREE_NAME , T.EFFDT , PJ.EMPLID , N.NAME_DISPLAY , N.LAST_NAME_SRCH , JC.DESCR FROM PSOPRDEFN O , PS_PRIMARY_JOB_VW PJ , PS_NAMES_LNG N , PSTREEDEFN T , PSTREENODE TN , PS_HRCD_JOB_TREE JT , PS_JOBCODE_TBL JC WHERE O.OPRTYPE = 0 AND PJ.EMPLID <> O.EMPLID AND N.EMPLID = PJ.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES_LNG NN, N, %CurrentDateIn) 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) AND NOT EXISTS ( SELECT 'X' FROM PS_JOBCODE_LANG JCL WHERE PJ.SETID_JOBCODE = JCL.SETID AND PJ.JOBCODE = JCL.JOBCODE AND %EffdtCheck(JOBCODE_LANG JCLE, JCL, %CurrentDateIn))

  • Related Language Record for HRCD_OTHERPER_V

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    3 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
    4 TREE_EFFDT Date(10) DATE Effective date of the Tree
    5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    7 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    8 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title