LM_PG_LEARN_VW2(SQL View) |
Index Back |
|---|
| SELECT LRNR.LM_PERSON_ID , LRNR.LM_ACT_ID , ACT.LM_CI_ID , ACT.LM_ACT_CD , LRNR.LM_ENRLMT_ID , CI.LM_CS_LONG_NM , LRNR.LM_STTS , XLAT.XLATLONGNAME , LRNR.LM_ENRL_DT , LRNR.LM_COMPL_DT , CASE WHEN LRNR.LM_STTS = 'PLAN' THEN ( SELECT LM_TARGET_DATE FROM PS_LM_LPLN_DTL PLN WHERE PLN.LM_PERSON_ID =LRNR.LM_PERSON_ID AND PLN.LM_ENRLMT_ID = LRNR.LM_ENRLMT_ID AND PLN.LM_LPLN_ID= ( SELECT MAX (PLN1.LM_LPLN_ID) FROM PS_LM_LPLN_DTL PLN1 WHERE PLN.LM_PERSON_ID =PLN1.LM_PERSON_ID AND PLN.LM_ENRLMT_ID = PLN1.LM_ENRLMT_ID AND PLN1.LM_ENRLMT_ID<>0)) ELSE LRNR.LM_TARGET_CMPL_DT END , %DateNull , %DateNull , CASE WHEN ( SELECT REQ1.LM_OBJV_NEEDED FROM PS_LM_LRNR_OBJV REQ1 WHERE REQ1.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ1.LM_ENRLMT_ID=LRNR.LM_ENRLMT_ID AND REQ1.LM_ACT_ID=LRNR.LM_ACT_ID AND REQ1.LM_CI_ID=LRNR.LM_CI_ID AND REQ1.LM_TARGET_CMPL_DT IS NOT NULL AND REQ1.LM_LRNR_OBJV_ID =( SELECT MAX(REQ2.LM_LRNR_OBJV_ID) FROM PS_LM_LRNR_OBJV REQ2 WHERE REQ1.LM_PERSON_ID= REQ2.LM_PERSON_ID AND REQ1.LM_ENRLMT_ID=REQ2.LM_ENRLMT_ID AND REQ1.LM_ACT_ID=REQ2.LM_ACT_ID AND REQ1.LM_CI_ID=REQ2.LM_CI_ID )) ='Y' THEN 'Y' WHEN ( SELECT REQ2.LM_REQUIRED FROM PS_LM_LPLN_DTL REQ2 WHERE REQ2.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ2.LM_ENRLMT_ID=LRNR.LM_ENRLMT_ID AND REQ2.LM_TARGET_DATE IS NOT NULL AND REQ2.LM_LPLN_ID= ( SELECT MAX (REQ3.LM_LPLN_ID) FROM PS_LM_LPLN_DTL REQ3 WHERE REQ2.LM_PERSON_ID =REQ3.LM_PERSON_ID AND REQ2.LM_ENRLMT_ID = REQ3.LM_ENRLMT_ID AND REQ3.LM_ENRLMT_ID<>0))='Y' THEN 'Y' ELSE 'N' END , CI.LM_CS_SHRT_NM FROM PS_LM_ENRLMT LRNR , PS_LM_ACT ACT , PS_LM_CI_TBL CI , PSXLATITEM XLAT WHERE LRNR.LM_ACT_ID =ACT.LM_ACT_ID AND LRNR.LM_CI_ID = CI.LM_CI_ID AND XLAT.FIELDVALUE=LRNR.LM_STTS AND XLAT.FIELDNAME='LM_STTS' AND %EffdtCheck(PSXLATITEM XLAT4, XLAT, %CurrentDateIn) AND LRNR.LM_ENRL_DT=( SELECT MAX(LRNR1.LM_ENRL_DT) FROM PS_LM_ENRLMT LRNR1 WHERE LRNR.LM_PERSON_ID =LRNR1.LM_PERSON_ID AND LRNR.LM_CI_ID = LRNR1.LM_CI_ID AND LRNR.LM_ACT_ID = LRNR1.LM_ACT_ID) AND LRNR.LM_ENRL_DT >= ( SELECT %Coalesce(MAX(SUPP.LM_ENRL_DT),%DateIn('1900-01-01')) FROM PS_LM_ADHC_LRN_TBL SUPP , PS_LM_ADHC_EQV_TBL EQV WHERE SUPP.LM_PERSON_ID =LRNR.LM_PERSON_ID AND LRNR.LM_CI_ID = EQV.LM_CI_ID AND SUPP.LM_ADHC_LRN_ID = EQV.LM_ADHC_LRN_ID AND SUPP.LM_ADHC_LRN_STTS <> 'DROP' AND LRNR.LM_ACT_ID=0) UNION ALL SELECT LRNR.LM_PERSON_ID , LRNR.LM_PRG_ID , 0 , PRGTBL.LM_PRG_CD , LRNR.LM_PRG_REG_ID , PRGTBL.LM_PRG_LONG_NM , LRNR.LM_PRG_STTS , XLAT.XLATLONGNAME , LRNR.LM_ENRL_DT , LRNR.LM_COMPL_DT , LRNR.LM_TARGET_CMPL_DT , CER.LM_CERT_DT , CER.LM_EXPR_DT , CASE WHEN LRNR.LM_PRG_STTS='EXPR' OR LRNR.LM_PRG_STTS='WARN' THEN 'Y' WHEN PRGTBL.LM_EXPR_DT IS NOT NULL THEN 'Y' WHEN ( SELECT REQ1.LM_OBJV_NEEDED FROM PS_LM_LRNR_OBJV REQ1 WHERE REQ1.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ1.LM_PRG_REG_ID=LRNR.LM_PRG_REG_ID AND REQ1.LM_PRG_ID=LRNR.LM_PRG_ID AND REQ1.LM_TARGET_CMPL_DT IS NOT NULL AND REQ1.LM_LRNR_OBJV_ID =( SELECT MAX(REQ2.LM_LRNR_OBJV_ID) FROM PS_LM_LRNR_OBJV REQ2 WHERE REQ1.LM_PERSON_ID= REQ2.LM_PERSON_ID AND REQ1.LM_PRG_REG_ID=REQ2.LM_PRG_REG_ID AND REQ1.LM_PRG_ID=REQ2.LM_PRG_ID)) ='Y' OR ( SELECT REQ2.LM_REQUIRED FROM PS_LM_LPLN_DTL REQ2 WHERE REQ2.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ2.LM_PRG_REG_ID=LRNR.LM_PRG_REG_ID AND REQ2.LM_TARGET_DATE IS NOT NULL AND REQ2.LM_LPLN_ID= ( SELECT MAX (REQ3.LM_LPLN_ID) FROM PS_LM_LPLN_DTL REQ3 WHERE REQ2.LM_PERSON_ID =REQ3.LM_PERSON_ID AND REQ2.LM_PRG_REG_ID = REQ3.LM_PRG_REG_ID AND REQ3.LM_PRG_REG_ID<>0))='Y' THEN 'Y' ELSE 'N' END , PRGTBL.LM_PRG_SHORT_NM FROM PS_LM_PRG_REG LRNR LEFT OUTER JOIN PS_LM_LRNR_CERT CER ON LRNR.LM_PRG_REG_ID=CER.LM_PRG_REG_ID , PS_LM_PRG PRGTBL , PSXLATITEM XLAT WHERE LRNR.LM_PRG_ID = PRGTBL.LM_PRG_ID AND XLAT.FIELDNAME='LM_PRG_STTS' AND XLAT.FIELDVALUE=LRNR.LM_PRG_STTS AND %EffdtCheck(PSXLATITEM XLAT1, XLAT, %CurrentDateIn) AND LRNR.LM_ENRL_DT BETWEEN PRGTBL.EFFDT AND PRGTBL.LM_END_EFFDT AND LRNR.LM_ENRL_DT >= ( SELECT %Coalesce(MAX(SUPP.LM_ENRL_DT),%DateIn('1900-01-01')) FROM PS_LM_ADHC_LRN_TBL SUPP , PS_LM_ADHC_EQV_TBL EQV WHERE SUPP.LM_PERSON_ID =LRNR.LM_PERSON_ID AND LRNR.LM_PRG_ID = EQV.LM_PRG_ID AND SUPP.LM_ADHC_LRN_ID = EQV.LM_ADHC_LRN_ID AND SUPP.LM_ADHC_LRN_STTS <> 'DROP') AND LRNR.LM_ENRL_DT=( SELECT MAX(LRNR1.LM_ENRL_DT) FROM PS_LM_PRG_REG LRNR1 WHERE LRNR.LM_PERSON_ID =LRNR1.LM_PERSON_ID AND LRNR.LM_PRG_ID = LRNR1.LM_PRG_ID) UNION ALL SELECT LRNR.LM_PERSON_ID , EQV.LM_PRG_ID , 0 , ( SELECT PRGTBL.LM_PRG_CD FROM PS_LM_PRG PRGTBL WHERE EQV.LM_PRG_ID=PRGTBL.LM_PRG_ID AND %EffdtCheck(LM_PRG EFF_PRGTBL, PRGTBL, %CurrentDateIn)) , LRNR.LM_ADHC_LRN_ID , LRNR.LM_PER_TRN_NM , LRNR.LM_ADHC_LRN_STTS , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME = 'LM_ADHC_LRN_STTS' AND XLAT.FIELDVALUE = LRNR.LM_ADHC_LRN_STTS AND %EffdtCheck(PSXLATITEM XLAT2, XLAT, %CurrentDateIn) ) , LRNR.LM_ENRL_DT , LRNR.LM_COMPL_DT , %DateNull , %DateNull , %DateNull , CASE WHEN ( SELECT REQ1.LM_OBJV_NEEDED FROM PS_LM_LRNR_OBJV REQ1 WHERE REQ1.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ1.LM_ADHC_LRN_ID=LRNR.LM_ADHC_LRN_ID AND REQ1.LM_TARGET_CMPL_DT IS NOT NULL AND REQ1.LM_LRNR_OBJV_ID =( SELECT MAX(REQ2.LM_LRNR_OBJV_ID) FROM PS_LM_LRNR_OBJV REQ2 WHERE REQ1.LM_PERSON_ID= REQ2.LM_PERSON_ID AND REQ1.LM_ADHC_LRN_ID=REQ2.LM_ADHC_LRN_ID))='Y' OR ( SELECT REQ2.LM_REQUIRED FROM PS_LM_LPLN_DTL REQ2 WHERE REQ2.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ2.LM_ADHC_LRN_ID=LRNR.LM_ADHC_LRN_ID AND REQ2.LM_TARGET_DATE IS NOT NULL AND REQ2.LM_LPLN_ID= ( SELECT MAX (REQ3.LM_LPLN_ID) FROM PS_LM_LPLN_DTL REQ3 WHERE REQ2.LM_PERSON_ID =REQ3.LM_PERSON_ID AND REQ2.LM_ADHC_LRN_ID = REQ3.LM_ADHC_LRN_ID AND REQ3.LM_ADHC_LRN_ID<>0))='Y' THEN 'Y' ELSE 'N' END , LRNR.LM_PER_TRN_NM FROM PS_LM_ADHC_LRN_TBL LRNR, PS_LM_ADHC_EQV_TBL EQV WHERE LRNR.LM_ADHC_LRN_ID=EQV.LM_ADHC_LRN_ID AND EQV.LM_PRG_ID <> 0 AND LRNR.LM_ADHC_LRN_STTS <> 'DROP' AND EXISTS ( SELECT A.LM_ENRL_DT FROM PS_LM_PRG_REG A , PS_LM_ADHC_EQV_TBL EQV WHERE LRNR.LM_ADHC_LRN_ID=EQV.LM_ADHC_LRN_ID AND A.LM_PRG_ID=EQV.LM_PRG_ID AND A.LM_PERSON_ID =LRNR.LM_PERSON_ID AND A.LM_ENRL_DT < LRNR.LM_ENRL_DT) UNION ALL SELECT LRNR.LM_PERSON_ID , 0 , EQV.LM_CI_ID , ( SELECT CITBL.LM_CRSE_CODE FROM PS_LM_CI_TBL CITBL WHERE EQV.LM_CI_ID=CITBL.LM_CI_ID AND %EffdtCheck(LM_CI_TBL EFF_CITBL, CITBL, %CurrentDateIn)) , LRNR.LM_ADHC_LRN_ID , LRNR.LM_PER_TRN_NM , LRNR.LM_ADHC_LRN_STTS ,( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME = 'LM_ADHC_LRN_STTS' AND XLAT.FIELDVALUE = LRNR.LM_ADHC_LRN_STTS AND %EffdtCheck(PSXLATITEM XLAT3, XLAT, %CurrentDateIn) ) , LRNR.LM_ENRL_DT , LRNR.LM_COMPL_DT , %DateNull , %DateNull , %DateNull , CASE WHEN ( SELECT REQ1.LM_OBJV_NEEDED FROM PS_LM_LRNR_OBJV REQ1 WHERE REQ1.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ1.LM_ADHC_LRN_ID=LRNR.LM_ADHC_LRN_ID AND REQ1.LM_TARGET_CMPL_DT IS NOT NULL AND REQ1.LM_LRNR_OBJV_ID =( SELECT MAX(REQ2.LM_LRNR_OBJV_ID) FROM PS_LM_LRNR_OBJV REQ2 WHERE REQ1.LM_PERSON_ID= REQ2.LM_PERSON_ID AND REQ1.LM_ADHC_LRN_ID=REQ2.LM_ADHC_LRN_ID))='Y' OR ( SELECT REQ2.LM_REQUIRED FROM PS_LM_LPLN_DTL REQ2 WHERE REQ2.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ2.LM_ADHC_LRN_ID=LRNR.LM_ADHC_LRN_ID AND REQ2.LM_TARGET_DATE IS NOT NULL AND REQ2.LM_LPLN_ID= ( SELECT MAX (REQ3.LM_LPLN_ID) FROM PS_LM_LPLN_DTL REQ3 WHERE REQ2.LM_PERSON_ID =REQ3.LM_PERSON_ID AND REQ2.LM_ENRLMT_ID = REQ3.LM_ADHC_LRN_ID AND REQ3.LM_ADHC_LRN_ID<>0))='Y' THEN 'Y' ELSE 'N' END , LRNR.LM_PER_TRN_NM FROM PS_LM_ADHC_LRN_TBL LRNR, PS_LM_ADHC_EQV_TBL EQV WHERE LRNR.LM_ADHC_LRN_ID=EQV.LM_ADHC_LRN_ID AND EQV.LM_CI_ID <> 0 AND LRNR.LM_ADHC_LRN_STTS <> 'DROP' AND EXISTS( SELECT ERL.LM_ENRL_DT FROM PS_LM_ENRLMT ERL , PS_LM_ADHC_EQV_TBL EQV WHERE LRNR.LM_ADHC_LRN_ID=EQV.LM_ADHC_LRN_ID AND ERL.LM_ACT_ID = 0 AND ERL.LM_CI_ID > 0 AND ERL.LM_CI_ID = EQV.LM_CI_ID AND ERL.LM_PERSON_ID =LRNR.LM_PERSON_ID AND ERL.LM_ENRL_DT < LRNR.LM_ENRL_DT) |
| # | 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_ACT_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Activity ID |
| 3 | LM_CI_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Catalog Item ID - System Generated ID Number associated with each Catalog Item |
| 4 | LM_ACT_CD |
Character(30) | VARCHAR2(30) NOT NULL | Activity Code |
| 5 | LM_ENRLMT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Enrollment ID |
| 6 | LM_ACT_NAME | Character(254) | VARCHAR2(254) NOT NULL | Activity Name |
| 7 | 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 |
| 8 | XLATLONGNAME | Character(30) | VARCHAR2(30) NOT NULL | Translate Long Name |
| 9 | LM_ENRL_DT | Date(10) | DATE | Enrollment/Registration Date |
| 10 | LM_COMPL_DATE | Date(10) | DATE | Learning Plan Completion Date |
| 11 | 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. |
| 12 | LM_CERT_DT | Date(10) | DATE | Certification Date |
| 13 | LM_EXPR_DT | Date(10) | DATE | Certification Expiration Date is a fixed, preset date when the certification will expire. |
| 14 | LM_LRNG_REQ | Character(1) | VARCHAR2(1) NOT NULL |
Required
N=No Y=Yes |
| 15 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |