LM_ACT_ROST_VW(SQL View) |
Index Back |
|---|---|
View For Activity RosterView containing person, activity,and enrollment details |
| SELECT A.LM_ACT_ID ,A.LM_ENRLMT_ID ,B.FIRST_NAME_SRCH ,B.LAST_NAME_SRCH ,B.LM_HR_EMPLID ,A.LM_ENRL_DT ,B.LM_ACTIVE ,B.FIRST_NAME ,B.LAST_NAME ,B.NAME_AC ,B.LM_ORGANIZATION_ID ,B.LM_ORG_DESCR ,B.LM_ORG_SDESCR ,B.LM_ORG_TYPE ,B.LM_MANAGER_ID ,B.LM_MGR_FIRST_NAME ,B.LM_MGR_LAST_NAME ,B.LM_JOB_TITLE ,B.LM_HIRE_DT ,A.LM_APPR_ID ,A.LM_STTS ,A.LM_WTLST_NBR ,A.LM_SCORE ,A.LM_PERSON_ID ,A.LM_GRADE_VAL_ID ,A.LM_ENRLMT_DESCR ,A.LM_PASS_VAL_ID ,A.LM_ATT_VAL_ID ,B.LM_NAME_DISPLAY ,A.LM_EMPL_RCD ,%subrec(LM_ENRL_FRA_SBR,A) FROM PS_LM_ENRLMT A ,PS_LM_PERS_DTLI_VW B WHERE A.LM_PERSON_ID = B.LM_PERSON_ID AND LM_STTS <> 'PLAN' AND NOT (A.LM_OLD_STTS = 'PLAN' AND A.LM_STTS = 'CANC') AND ( A.LM_EMPL_RCD = B.LM_EMPL_RCD OR (A.LM_EMPL_RCD <> B.LM_EMPL_RCD AND NOT EXISTS( SELECT 'X' FROM PS_LM_PERS_DTLI_VW C WHERE C.LM_PERSON_ID = A.LM_PERSON_ID AND C.LM_EMPL_RCD = A.LM_EMPL_RCD ))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  LM_ACT_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Activity ID |
| 2 | LM_ENRLMT_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Enrollment ID |
| 3 | FIRST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 4 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 5 | LM_HR_EMPLID | Character(11) | VARCHAR2(11) NOT NULL | EMPLID from the HR System |
| 6 | LM_ENRL_DT | Date(10) | DATE | Enrollment/Registration Date |
| 7 | LM_ACTIVE | Character(1) | VARCHAR2(1) NOT NULL |
Active
N=Inactive Y=Active |
| 8 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name field |
| 9 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name Field |
| 10 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 11 | LM_ORGANIZATION_ID | Number(15,0) | DECIMAL(15) NOT NULL | Organization ID |
| 12 | LM_ORG_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Customer Description |
| 13 | LM_ORG_SDESCR | Character(10) | VARCHAR2(10) NOT NULL | Organization Short Description |
| 14 | LM_ORG_TYPE | Character(15) | VARCHAR2(15) NOT NULL | Organization Type |
| 15 | LM_MANAGER_ID | Number(15,0) | DECIMAL(15) NOT NULL | Manager ID |
| 16 | LM_MGR_FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Manager First Name |
| 17 | LM_MGR_LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Manager Last Name |
| 18 | LM_JOB_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 19 | LM_HIRE_DT | Date(10) | DATE | Hire Date |
| 20 | LM_APPR_ID | Number(15,0) | DECIMAL(15) NOT NULL | Approver ID field |
| 21 | LM_STTS | Character(4) | VARCHAR2(4) NOT NULL |
Enrollment Status
CANC=Dropped COMP=Completed DECL=Denied ENRL=Enrolled INCO=Not Completed INPO=In-Progress MACT=Moved to New Activity NOTS=Not Started PEAP=Pending Approval PEPA=Pending Payment PLAN=Planned PPYA=Payment Approval RQST=Learning Request WAIV=Waived WTLT=Waitlisted |
| 22 | LM_WTLST_NBR | Number(10,0) | DECIMAL(10) NOT NULL | Number on the Waitlist for an Activity. |
| 23 | LM_SCORE | Character(30) | VARCHAR2(30) NOT NULL | Score for this Enrollment. Score will be passed by Content Vendor. |
| 24 | LM_PERSON_ID | Number(15,0) | DECIMAL(15) NOT NULL | ELM Person ID |
| 25 | LM_GRADE_VAL_ID | Number(10,0) | DECIMAL(10) NOT NULL |
This will uniquely identify the combination of User Label / Grade value.
Prompt Table: LM_GRD_SCH_VW |
| 26 | LM_ENRLMT_DESCR | Long Character(2000) | VARCHAR2(2000) | Comments |
| 27 | LM_PASS_VAL_ID | Number(10,0) | DECIMAL(10) NOT NULL |
Passing Scheme Value ID
Prompt Table: LM_PASS_VAL_VW |
| 28 | LM_ATT_VAL_ID | Number(10,0) | DECIMAL(10) NOT NULL |
Attendance Scheme Value Id. This will identify the combination of User Label / Attendance uniquely
Prompt Table: LM_ATT_ALL_VW |
| 29 | LM_NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Name Display field |
| 30 | LM_EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record umber |
| 31 | LM_TRAINING_CLASS | Character(10) | VARCHAR2(10) NOT NULL |
Training Class
Default Value: 9 Prompt Table: LM_TRAIN_CLASS |
| 32 | LM_ACT_DIF_FLG | Character(1) | VARCHAR2(1) NOT NULL |
DIF Enrollment
Y/N Table Edit Default Value: N |
| 33 | LM_TRG_PLN_CTGY | Character(10) | VARCHAR2(10) NOT NULL | Training Plan Category |