CM_ROLE_EE_VW

(SQL View)
 Index Back

CM Employee Role Competencies

This view selects the required competencies per employee role. The role is derived from the Position number and Jobcode from the current job record. The view is used during the employee competency evaluation to populate the required competencies into the employes profile in order to be rated.

SELECT J1.EMPLID , J1.EMPL_RCD , CT.COMPETENCY FROM PS_JOB J1 , PS_COMPETENCY_TBL CT WHERE J1.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = J1.EMPLID AND EMPL_RCD = J1.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND J1.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = J1.EMPLID AND EMPL_RCD = J1.EMPL_RCD AND EFFDT = J1.EFFDT) AND ((CT.COMPETENCY IN ( SELECT COMPETENCY FROM PS_CM_ROLE_COMPS RC WHERE ((RC.POSITION_NBR <> ' ' AND RC.POSITION_NBR = J1.POSITION_NBR) OR (RC.JOBCODE = J1.JOBCODE AND RC.SETID = J1.SETID_JOBCODE)) AND RC.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CM_ROLE WHERE POSITION_NBR = RC.POSITION_NBR AND JOBCODE = RC.JOBCODE AND SETID = RC.SETID AND EFFDT <= %CurrentDateIn) AND RC.COMPETENCY <> ' ')) OR (CT.COMPETENCY IN ( SELECT COMPETENCY FROM PS_CM_CLST_COMP_V1 CC WHERE ((CC.POSITION_NBR <> ' ' AND CC.POSITION_NBR = J1.POSITION_NBR) OR (CC.JOBCODE = J1.JOBCODE AND CC.SETID = J1.SETID_JOBCODE)) AND CC.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CM_ROLE WHERE POSITION_NBR = CC.POSITION_NBR AND JOBCODE = CC.JOBCODE AND SETID = CC.SETID AND EFFDT <= %CurrentDateIn) AND CC.COMPETENCY <> ' ')))

  • Parent record: PER_ORG_ASGN_VW

    • # 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 Rcd Nbr
    3 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency