COMPETENCIES_VW

(SQL View)
 Index Back

Competencies View

This record is a shadow view for the Competencies record, since we don't want the field EFFDT to be a key (it causes a problem on the grid since it rolls forwards the values of the previous row.)

SELECT C.EMPLID , C.EVALUATION_ID ,C.COMPETENCY , C.EFFDT ,C.YR_LAST_USED ,C.HOW_VERIFIED ,C.PROFICIENCY ,C.INTEREST_LEVEL ,C.YEARS_OF_EXP ,C.YR_ACQUIRED ,C.SELECT_CMP FROM %Table(COMPETENCIES) C WHERE C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM %Table(COMPETENCIES) C2 WHERE C2.EMPLID = C.EMPLID AND C2.COMPETENCY = C.COMPETENCY AND C2.EFFDT <= %CurrentDateIn)

  • Parent record: RS_EVALUATIONS

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

    Prompt Table: PERSONAL_DATA

    2 EVALUATION_ID Character(2) VARCHAR2(2) NOT NULL Evaluation ID

    Default Value: 01

    3 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency

    Prompt Table: COMPETENCY_TBL

    4 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    5 YR_LAST_USED Number(4,0) SMALLINT NOT NULL Year Last Used
    6 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
    7 PROFICIENCY Character(1) VARCHAR2(1) NOT NULL Proficiency

    Prompt Table: REVW_RATING_TBL

    8 INTEREST_LEVEL Character(1) VARCHAR2(1) NOT NULL Interest Level

    Default Value: 2

    Prompt Table: INT_RATING_VW
    Set Control Field: REVIEW_RATING

    9 YEARS_OF_EXP Number(5,1) DECIMAL(4,1) NOT NULL Years of Work Experience
    10 YR_ACQUIRED Number(4,0) SMALLINT NOT NULL Year Acquired
    11 SELECT_CMP Character(1) VARCHAR2(1) NOT NULL Include Competency for Search

    Y/N Table Edit

    Default Value: Y