LM_PG_MGR_CR_VW(SQL View) |
Index Back |
|---|---|
Learning Compliance Mgr CrseManager View for Learning Compliance Pivot for Course |
| SELECT CRSE.OPRID , PER.LM_PERSON_ID , %Coalesce(LRNR.LM_ACT_ID, CRSE.LM_ACT_ID) , CRSE.LM_CI_ID , %Coalesce(LRNR.LM_ACT_CD, CRSE.LM_ACT_CD) , %Coalesce(LRNR.LM_TMPL_TYPE_NAME, CRSE.LM_LRN_TYPE_DESC) , ( SELECT C.LM_NAME_DISPLAY FROM PS_LM_PERSON_NAME C WHERE C.LM_PERSON_ID = PER.LM_PERSON_ID AND C.LM_DISPLAY_FLAG = 'Y' AND %CurrentDateIn BETWEEN C.EFFDT AND C.LM_END_EFFDT AND ((C.LM_NAME_TYPE = 'PRF') OR (C.LM_NAME_TYPE = 'PRI' AND NOT EXISTS ( SELECT LM_NAME_TYPE FROM PS_LM_PERSON_NAME WHERE LM_PERSON_ID = C.LM_PERSON_ID AND LM_NAME_TYPE = 'PRF')))) , ( SELECT C.LM_NAME_DISPLAY FROM PS_LM_PERSON_NAME C WHERE C.LM_PERSON_ID = PER.LM_MANAGER_ID AND C.LM_DISPLAY_FLAG = 'Y' AND %CurrentDateIn BETWEEN C.EFFDT AND C.LM_END_EFFDT AND ((C.LM_NAME_TYPE = 'PRF') OR (C.LM_NAME_TYPE = 'PRI' AND NOT EXISTS ( SELECT LM_NAME_TYPE FROM PS_LM_PERSON_NAME WHERE LM_PERSON_ID = C.LM_PERSON_ID AND LM_NAME_TYPE = 'PRF')))) , %Coalesce(LRNR.LM_ENRLMT_ID, CRSE.LM_ENRLMT_ID), %Coalesce(LRNR.LM_ACT_NAME, CRSE.LM_ACT_NAME) , %Coalesce(LRNR.LM_STTS,CRSE.LM_STTS, ' ') , %Coalesce(LRNR.XLATLONGNAME,CRSE.XLATLONGNAME, (SELECT %Substring(MESSAGE_TEXT, 1, 12) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 2)) , %Coalesce((SELECT ORG.LM_ORG_DESCR FROM PS_LM_ORGANIZATION ORG WHERE ORG.LM_ORGANIZATION_ID = PER.LM_ORGANIZATION_ID AND %CurrentDateIn BETWEEN ORG.EFFDT AND ORG.LM_END_EFFDT AND ORG.EFF_STATUS = 'A'),' ') , PER.LM_JOB_TITLE , %Coalesce(LRNR.LM_ENRL_DT, CRSE.LM_ENRL_DT), LRNR.LM_COMPL_DT , %Coalesce(LRNR.LM_TARGET_CMPL_DT, CRSE.LM_TARGET_CMPL_DT) , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME='LM_LRNG_REQ' AND XLAT.FIELDVALUE=(%Coalesce(LRNR.LM_LRNG_REQ,CRSE.LM_LRNG_REQ, 'N')) AND %EffdtCheck(PSXLATITEM XLAT1, XLAT, %CurrentDateIn)) FROM ( SELECT DISTINCT D1.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , CRSE.LM_LRN_TYPE_DESC , CRSE.OPRID , CRSE.LM_ACT_NAME , ENRL.LM_ENRLMT_ID , ENRL.LM_STTS , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME = 'LM_STTS' AND XLAT.FIELDVALUE = ENRL.LM_STTS AND %EffdtCheck(PSXLATITEM XLAT2, XLAT, %CurrentDateIn)) AS XLATLONGNAME, ENRL.LM_ENRL_DT, CASE WHEN ENRL.LM_STTS = 'PLAN' THEN ( SELECT LM_TARGET_DATE FROM PS_LM_LPLN_DTL PLN WHERE PLN.LM_PERSON_ID = ENRL.LM_PERSON_ID AND PLN.LM_ENRLMT_ID = ENRL.LM_ENRLMT_ID) ELSE ENRL.LM_TARGET_CMPL_DT END AS LM_TARGET_CMPL_DT, CASE WHEN ( SELECT REQ2.LM_REQUIRED FROM PS_LM_LPLN_DTL REQ2 WHERE REQ2.LM_PERSON_ID = ENRL.LM_PERSON_ID AND REQ2.LM_ENRLMT_ID = ENRL.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 AS LM_LRNG_REQ FROM PS_LM_GROUP_PERSON D1 JOIN PS_LM_CI_SEC_TBL B1 ON B1.LM_LRNR_GROUP_ID = D1.LM_LRNR_GROUP_ID JOIN PS_LM_PG_MGR_CRSE CRSE ON CRSE.LM_CI_ID = B1.LM_CI_ID LEFT OUTER JOIN PS_LM_ENRLMT ENRL ON CRSE.LM_CI_ID = ENRL.LM_CI_ID AND ENRL.LM_ACT_ID = 0 AND D1.LM_PERSON_ID = ENRL.LM_PERSON_ID WHERE CRSE.LM_ACT_ID = 0 AND CRSE.LM_CI_ID<>0 AND CRSE.LM_CURR_USER_FLG <> 'Y' AND (ENRL.LM_ENRLMT_ID = ( SELECT MAX(LRNR2.LM_ENRLMT_ID) FROM PS_LM_ENRLMT LRNR2 WHERE ENRL.LM_PERSON_ID = LRNR2.LM_PERSON_ID AND ENRL.LM_CI_ID = LRNR2.LM_CI_ID) OR ENRL.LM_ENRLMT_ID IS NULL)) CRSE JOIN PS_LM_PERSON_ATTRB PER ON CRSE.LM_PERSON_ID = PER.LM_PERSON_ID JOIN PS_LM_PERSON_OPRID OPR ON CRSE.OPRID = OPR.OPRID AND PER.LM_MANAGER_ID = OPR.LM_PERSON_ID LEFT OUTER JOIN ( SELECT LRNR.LM_PERSON_ID , LRNR.LM_ACT_ID , ACT.LM_CI_ID , ACT.LM_ACT_CD , LRNR.LM_ENRLMT_ID , ( SELECT CI.LM_CS_LONG_NM FROM PS_LM_CI_TBL CI WHERE LRNR.LM_CI_ID = CI.LM_CI_ID) AS LM_ACT_NAME , LRNR.LM_STTS , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME = 'LM_STTS' AND XLAT.FIELDVALUE = LRNR.LM_STTS AND %EffdtCheck(PSXLATITEM XLAT1, XLAT, %CurrentDateIn)) AS XLATLONGNAME , ( SELECT %Upper(%Substring(MESSAGE_TEXT, 1, 10)) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 5) AS LM_TMPL_TYPE_NAME , 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) ELSE LRNR.LM_TARGET_CMPL_DT END AS LM_TARGET_CMPL_DT , 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 AS LM_LRNG_REQ FROM PS_LM_ENRLMT LRNR, PS_LM_ACT ACT WHERE LRNR.LM_ACT_ID =ACT.LM_ACT_ID 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_ENRLMT_ID = ( SELECT MAX(LRNR2.LM_ENRLMT_ID) FROM PS_LM_ENRLMT LRNR2 WHERE LRNR.LM_PERSON_ID =LRNR2.LM_PERSON_ID AND LRNR.LM_CI_ID = LRNR2.LM_CI_ID AND LRNR.LM_ENRL_DT=LRNR2.LM_ENRL_DT)) LRNR ON CRSE.LM_CI_ID = LRNR.LM_CI_ID AND PER.LM_PERSON_ID = LRNR.LM_PERSON_ID WHERE PER.LM_ACTIVE = 'Y' AND PER.LM_EMPL_RCD = ( SELECT MIN(JOB2.LM_EMPL_RCD) FROM PS_LM_PERSON_ATTRB JOB2 WHERE JOB2.LM_PERSON_ID = PER.LM_PERSON_ID AND JOB2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = JOB2.LM_PERSON_ID AND LM_EMPL_RCD = JOB2.LM_EMPL_RCD AND EFFDT <= PER.EFFDT) AND JOB2.LM_END_EFFDT >= %CurrentDateIn AND ((JOB2.LM_ACTIVE = 'Y' AND JOB2.LM_JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_LM_PERSON_ATTRB JOB3 WHERE JOB3.LM_PERSON_ID = JOB2.LM_PERSON_ID AND JOB3.LM_EMPL_RCD <> JOB2.LM_EMPL_RCD AND JOB3.LM_END_EFFDT >= %CurrentDateIn AND JOB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = JOB3.LM_PERSON_ID AND LM_EMPL_RCD = JOB3.LM_EMPL_RCD AND EFFDT <= %CurrentDateIn AND LM_END_EFFDT >= %CurrentDateIn) AND ((JOB3.LM_ACTIVE = 'Y' AND (JOB2.LM_ACTIVE <> 'Y' OR (JOB3.LM_JOB_INDICATOR = 'P' AND JOB2.LM_JOB_INDICATOR <> 'P'))) OR (JOB3.LM_JOB_INDICATOR = 'P' AND JOB2.LM_JOB_INDICATOR <> 'P' AND JOB2.LM_ACTIVE <> 'Y'))))) AND PER.LM_END_EFFDT >= %CurrentDateIn |
| # | 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 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |