LM_STATS_UN4_VW(SQL View) |
Index Back |
|---|---|
Union for LM_STATS repUnion view for LM_STATS report |
| SELECT H.LM_ACT_CD , H.LM_CI_ID , I.LM_ACT_ID , I.LM_SCO_ID , I.LM_BLOCK_ID , I.LM_SCO_TITLE , I.LM_ELRN_SEQ_ID , I.LM_SCO_TYPE , M.LM_PERSON_ID , L.NAME , M.LM_ENRLMT_ID , ' ' , 'N' , O.XLATLONGNAME , K.LM_CS_LONG_NM , N.LM_LC_ID , N.LM_LC_LONG_NM , O.FIELDNAME , O.FIELDVALUE ,O.EFFDT , R.OPRID , R.RUN_CNTL_ID FROM PS_LM_ACT_LE_VW H , PS_LM_SCO_DETAIL I , PS_LM_ENR_LC_BL_VW J , PS_LM_CI_VW_LNG K , PS_LM_PERS_NAME_VW L , PS_LM_ENRLMT_VW M , PS_LM_LC_LVW N , PSXLATITEMLANG O , PS_LM_RCNTL_STDNT R , PS_PRCSRUNCNTL U WHERE ( I.LM_LC_ID = J.LM_LC_ID AND K.LM_CI_ID = H.LM_CI_ID AND L.LM_PERSON_ID = M.LM_PERSON_ID AND I.LM_ACT_ID = N.LM_ACT_ID AND I.LM_LC_ID = N.LM_LC_ID AND O.EFFDT = ( SELECT MAX(O_ED.EFFDT) FROM PSXLATITEM O_ED WHERE O.FIELDNAME = O_ED.FIELDNAME AND O.FIELDVALUE = O_ED.FIELDVALUE AND O_ED.EFFDT <= %CurrentDateIn) AND H.LM_ACT_ID = M.LM_ACT_ID AND J.LM_ENRLMT_ID = M.LM_ENRLMT_ID AND J.LM_LC_ID = N.LM_LC_ID AND H.LM_ACT_ID = N.LM_ACT_ID AND I.LM_LC_ID = N.LM_LC_ID AND I.LM_SCO_TYPE = 'SCO' AND O.FIELDNAME = 'LM_LSN_STAT' AND O.FIELDVALUE = 'N' AND 'Y' NOT IN ( SELECT 'Y' FROM PS_LM_TRACK_STUDNT P WHERE P.LM_ENRLMT_ID = J.LM_ENRLMT_ID AND P.LM_LC_ID = J.LM_LC_ID) AND H.LM_ACT_ID = R.LM_ACT_ID AND R.RUN_CNTL_ID = U.RUN_CNTL_ID AND R.OPRID = U.OPRID AND K.LANGUAGE_CD = N.LANGUAGE_CD AND K.LANGUAGE_CD = O.LANGUAGE_CD AND K.LANGUAGE_CD = U.LANGUAGE_CD ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | LM_ACT_CD | Character(30) | VARCHAR2(30) NOT NULL | Activity Code |
| 2 | LM_CI_ID | Number(10,0) | DECIMAL(10) NOT NULL | Catalog Item ID - System Generated ID Number associated with each Catalog Item |
| 3 | LM_ACT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Activity ID |
| 4 | LM_SCO_ID | Character(200) | VARCHAR2(200) NOT NULL | A system id for the launchable SCOs or AUs, determined by the content provider |
| 5 | LM_BLOCK_ID | Character(254) | VARCHAR2(254) NOT NULL | A block id for the launchable SCOs or AUs, determined by the content provider |
| 6 | LM_SCO_TITLE | Character(254) | VARCHAR2(254) NOT NULL | SCO Title |
| 7 | LM_ELRN_SEQ_ID | Number(3,0) | SMALLINT NOT NULL | Seq number |
| 8 | LM_SCO_TYPE | Character(30) | VARCHAR2(30) NOT NULL | A User defined category |
| 9 | LM_PERSON_ID | Number(15,0) | DECIMAL(15) NOT NULL | ELM Person ID |
| 10 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 11 | LM_ENRLMT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Enrollment ID |
| 12 | LM_SCORE_RAW | Character(7) | VARCHAR2(7) NOT NULL | Score Raw |
| 13 | LM_LSN_STAT | Character(1) | VARCHAR2(1) NOT NULL |
Lesson current Status. values:
P:passed
C:completed
F:failed
I:incomplete
B:browsed
N:not attempted
B=Browsed C=Completed F=Failed I=Incomplete N=Not Attempted P=Passed |
| 14 | XLATLONGNAME | Character(30) | VARCHAR2(30) NOT NULL | Translate Long Name |
| 15 | LM_CS_LONG_NM | Character(200) | VARCHAR2(200) NOT NULL | Long Name - Describes the long name of an object |
| 16 | LM_LC_ID | Number(10,0) | DECIMAL(10) NOT NULL | Learning Component ID - Blended Learning |
| 17 | LM_LC_LONG_NM | Character(200) | VARCHAR2(200) NOT NULL | Learning Component Long Name |
| 18 | FIELDNAME | Character(18) | VARCHAR2(18) NOT NULL | Field Name (see PSDBFIELD). |
| 19 | FIELDVALUE | Character(4) | VARCHAR2(4) NOT NULL | Field Value |
| 20 | EFFDT | Date(10) | DATE | Effective Date |
| 21 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 22 | RUN_CNTL_ID | Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |