CM_ELEMENT_DTL

(SQL View)
 Index Back

Element Detail Table

This table tracks the progress of the employees' elements which are associated with a competency, if they have been satisfied. Release 9 Job Profile Managment (JPM): Table became a view to JPM data. Ownership of table transferred from HR Core Objects to JPM.

SELECT B.EMPLID ,C.EVALUATION_ID ,%Substring(C.JPM_CAT_ITEM_ID,1,8) ,A.EFFDT ,%Substring(A.JPM_CAT_ITEM_ID,1,10) ,A.JPM_YN_1 ,A.JPM_DATE_2 ,A.JPM_DATE_1 ,A.JPM_DATE_3 ,A.JPM_TEXT254_5 FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B , PS_JPM_JP_ITEMS C WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND A.JPM_PROFILE_ID = C.JPM_PROFILE_ID AND A.JPM_PARENT_KEY_ID = C.JPM_ITEM_KEY_ID AND B.JPM_JP_TYPE = ( SELECT JPM_PRI_PERS_TYPE FROM PS_JPM_JP_PRI_TYPE) AND A.JPM_CAT_TYPE IN ('ELEMENT' ,'NVQ ELEMENT') AND C.JPM_CAT_TYPE IN ('COMPETENCY' ,'NVQ UNIT') AND A.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_JPM_JP_ITEMS X WHERE A.JPM_PROFILE_ID = X.JPM_PROFILE_ID AND A.JPM_CAT_TYPE = X.JPM_CAT_TYPE AND A.JPM_CAT_ITEM_ID = X.JPM_CAT_ITEM_ID AND A.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
    2 EVALUATION_ID Character(2) VARCHAR2(2) NOT NULL Evaluation ID
    3 COMPETENCY Character(8) VARCHAR2(8) NOT NULL Competency
    4 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    5 ELEMENT_CD Character(10) VARCHAR2(10) NOT NULL Element code

    Prompt Table: CM_ELEMENT_TBL

    6 SATISFIED Character(1) VARCHAR2(1) NOT NULL This is a Yes/No flag to indicate if employee satisfied a certain criterion.

    Y/N Table Edit

    Default Value: N

    7 START_DT Date(10) DATE Start Date
    8 EXP_DT Date(10) DATE Expected Completion Date
    9 COMP_DT Date(10) DATE Completion Date
    10 DESCRLONG Long Character CLOB Long Description