CM_COMP_TRN

(SQL View)
 Index Back

Person Competencies

This view is used for the training functionality that assigns competrencies to employees upon training course completion. It will retrieve competencies for employees enrolled in a course.

SELECT A.emplid , A.evaluation_id , A.competency , A.EFFDT , B.course , B.session_nbr , A.yr_last_used , A.how_verified , A.proficiency FROM ps_competencies A , PS_TRAINING B WHERE A.emplid = B.emplid AND B.attendance IN ('E','A','C') AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_COMPETENCIES WHERE EMPLID = A.EMPLID AND EVALUATION_ID = A.EVALUATION_ID AND COMPETENCY = A.COMPETENCY AND EFFDT <= %CurrentDateIn)

  • Parent record: COMPETENCIES

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    2 EVALUATION_ID Character(2) VARCHAR2(2) NOT NULL Evaluation ID
    3 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency

    Prompt Table: COMPETENCY_TBL

    4 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    5 COURSE Character(6) VARCHAR2(6) NOT NULL Course Code
    6 SESSION_NBR Character(4) VARCHAR2(4) NOT NULL Course Session Nbr
    7 YR_LAST_USED Number(4,0) SMALLINT NOT NULL Year Last Used
    8 HOW_VERIFIED Character(1) VARCHAR2(1) NOT NULL Verified By
    E=Employee Claim
    J=On the Job Training
    N=Not Verified
    O=Employer Observation
    T=By Test
    V=NVQ Authority
    9 PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Proficiency

    Prompt Table: REVW_RATING_TBL