LM_LRCMP_SRC_VW

(SQL View)
 Index Back

Learner Competency View

This view is used for HR Learner Competency Component Search page.

SELECT F.LM_PERSON_ID , E.LM_OBJV_ID , F.LM_HR_EMPLID ,G.NAME ,G.LAST_NAME_SRCH ,G.FIRST_NAME_SRCH ,H.LM_HR_SETID ,G.LM_ORGANIZATION_ID , D.EFFDT , D.LM_CAT_ITEM_QUAL , D.LM_RATING_MODEL , D.LM_REVIEW_RATING , E.LM_EXT_SRC_CD ,E.LM_EXT_CD FROM PS_LM_PROFILES A , PS_LM_PROF_RVAL B , PS_LM_PROFILE_CAT C , PS_LM_PROFILE_ITEM D , PS_LM_OBJV_DTL E , PS_LM_PERSON F , PS_LM_PERS_DTL_VW G ,PS_LM_ORG_VW H , PS_LM_RTNG_MDL_TBL I , PS_LM_JP_TYPES J WHERE A.LM_PROFILE_ID = B.LM_PROFILE_ID AND A.LM_PROFILE_ID = C.LM_PROFILE_ID AND C.LM_PROFILE_ID = D.LM_PROFILE_ID AND C.LM_CAT_TYPE = D.LM_CAT_TYPE AND E.LM_EXT_SRC_CD = 'PM' AND E.LM_EXT_CD = D.LM_CAT_ITEM_ID AND F.LM_HR_EMPLID =%Substring(B.LM_RLAT_KEY1,1,11) AND F.LM_PERSON_ID = G.LM_PERSON_ID AND G.LM_ORGANIZATION_ID = H.LM_ORGANIZATION_ID AND C.LM_CAT_TYPE = 'COMPETENCY' AND A.LM_JP_TYPE = J.LM_JP_TYPE AND J.LM_PROFILE_USAGE = 'P' AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_LM_JP_TYPES J1 WHERE J1.LM_JP_TYPE = J.LM_JP_TYPE AND J1.EFFDT <= %CurrentDateIn) AND D.LM_RATING_MODEL = I.LM_RATING_MODEL AND I.EFFDT = ( SELECT MAX(I1.EFFDT) FROM PS_LM_RTNG_MDL_TBL I1 WHERE I.LM_RATING_MODEL = I1.LM_RATING_MODEL AND I1.EFFDT <=%CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LM_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL ELM Person ID
2 LM_OBJV_ID Number(10,0) DECIMAL(10) NOT NULL Objective ID. System Generated ID Number associated with each Objective.
3 LM_HR_EMPLID Character(11) VARCHAR2(11) NOT NULL EMPLID from the HR System
4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
6 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
7 LM_HR_SETID Character(5) VARCHAR2(5) NOT NULL SetID
8 LM_ORGANIZATION_ID Number(15,0) DECIMAL(15) NOT NULL Organization ID
9 LM_EXT_EFFDT Date(10) DATE External Source EFFDT
10 LM_EVAL_TYPE Character(2) VARCHAR2(2) NOT NULL LM evaluation Type
A=Approved/Official
B=Subordinate
C=Customer
E=Executive Committee
L=Learning Management
M=Mentor
P=Peer
R=Supervisor/Manager
S=Self
11 LM_RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Used in Competency Sync messages
12 LM_REVIEW_RATING Character(1) VARCHAR2(1) NOT NULL REview points for Rating models from HRMS
13 LM_EXT_SRC_CD Character(10) VARCHAR2(10) NOT NULL External Source Code
14 LM_EXT_CD Character(30) VARCHAR2(30) NOT NULL External objective code