LM_PG_LCOMP_VW4(SQL View) |
Index Back |
|---|
| SELECT DISTINCT CRSE.OPRID , CRSE.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , CASE WHEN (CRSE.LM_ACT_ID <> 0 AND CRSE.LM_CI_ID <> 0) THEN %Upper((SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 5 )) WHEN (CRSE.LM_ACT_ID = 0 AND CRSE.LM_CI_ID <> 0) THEN %Upper((SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 6 )) ELSE %Upper((SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 7)) END , CRSE.LM_NAME_DISPLAY , %Coalesce(CRSE.NAME, ' ') , LRNR.LM_ENRLMT_ID , CASE WHEN CRSE.LM_ACT_ID <>0 AND CRSE.LM_CI_ID= 0 THEN ( SELECT PRG.LM_PRG_LONG_NM FROM PS_LM_PRG PRG WHERE CRSE.LM_ACT_ID=PRG.LM_PRG_ID AND %EffdtCheck(LM_PRG PRG1, PRG,%CurrentDateIn)) ELSE ( SELECT CI.LM_CS_LONG_NM FROM PS_LM_CI_TBL CI WHERE CRSE.LM_CI_ID=CI.LM_CI_ID AND %EffdtCheck(LM_CI_TBL CI1, CI,%CurrentDateIn) ) END , CASE WHEN LRNR.LM_STTS IS NULL THEN '' ELSE LRNR.LM_STTS END , CASE WHEN LRNR.LM_STTS IS NULL THEN ( SELECT %Substring(MESSAGE_TEXT, 1, 12) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 2) ELSE LRNR.XLATLONGNAME END , CRSE.LM_ORG_DESCR , CRSE.LM_JOB_TITLE , LRNR.LM_ENRL_DT , LRNR.LM_COMPL_DATE , LRNR.LM_TARGET_CMPL_DT , LRNR.LM_CERT_DT , LRNR.LM_EXPR_DT , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME='LM_LRNG_REQ' AND XLAT.FIELDVALUE=%Coalesce(LRNR.LM_LRNG_REQ,'N') AND %EffdtCheck(PSXLATITEM XLAT1, XLAT, %CurrentDateIn) ) , ( SELECT %Substring(XLAT.XLATLONGNAME,1,10) FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME='LM_TEAM_FLG' AND XLAT.FIELDVALUE=CRSE.LM_TEAM_FLG AND %EffdtCheck(PSXLATITEM XLAT2, XLAT, %CurrentDateIn) ) FROM PS_LM_PG_TM_CRS_VW CRSE LEFT OUTER JOIN PS_LM_PG_LEARN_VW2 LRNR ON CRSE.LM_ACT_ID= LRNR.LM_ACT_ID AND CRSE.LM_CI_ID= LRNR.LM_CI_ID AND CRSE.LM_ACT_CD = LRNR.LM_ACT_CD AND CRSE.LM_PERSON_ID = LRNR.LM_PERSON_ID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | LM_PERSON_ID |
Number(15,0) | DECIMAL(15) NOT NULL | ELM Person ID |
| 3 | LM_ACT_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Activity ID |
| 4 | LM_CI_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Catalog Item ID - System Generated ID Number associated with each Catalog Item |
| 5 | LM_ACT_CD |
Character(30) | VARCHAR2(30) NOT NULL | Activity Code |
| 6 | LM_TMPL_TYPE_NAME | Character(30) | VARCHAR2(30) NOT NULL | Template Name |
| 7 | LM_NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Name Display field |
| 8 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 9 | LM_ENRLMT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Enrollment ID |
| 10 | LM_ACT_NAME | Character(254) | VARCHAR2(254) NOT NULL | Activity Name |
| 11 | 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 |
| 12 | XLATLONGNAME | Character(30) | VARCHAR2(30) NOT NULL | Translate Long Name |
| 13 | LM_ORG_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Customer Description |
| 14 | LM_JOB_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 15 | LM_ENRL_DT | Date(10) | DATE | Enrollment/Registration Date |
| 16 | LM_COMPL_DATE | Date(10) | DATE | Learning Plan Completion Date |
| 17 | LM_TARGET_CMPL_DT | Date(10) | DATE | The target date by which the student should complete the course. This field is only applicable for WBT activity and the Learning Period for the WBT activity is Enforced. If the Learning Period is not enforced, this field will hold no value. |
| 18 | LM_CERT_DT | Date(10) | DATE | Certification Date |
| 19 | LM_EXPR_DT | Date(10) | DATE | Certification Expiration Date is a fixed, preset date when the certification will expire. |
| 20 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
| 21 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |