LM_STATS_UN3_VW(SQL View) |
Index Back |
|---|---|
Union for LM_STATS repUnion view for LM_STATS report |
| SELECT A.LM_ACT_CD , A.LM_CI_ID , B.LM_ACT_ID , B.LM_SCO_ID , B.LM_BLOCK_ID , B.LM_SCO_TITLE , B.LM_ELRN_SEQ_ID , B.LM_SCO_TYPE , C.LM_PERSON_ID , F.NAME , C.LM_ENRLMT_ID , C.LM_SCORE_RAW , C.LM_LSN_STAT , D.XLATLONGNAME , E.LM_CS_LONG_NM , G.LM_LC_ID , G.LM_LC_LONG_NM , D.FIELDNAME , D.FIELDVALUE , D.EFFDT , Q.OPRID , Q.RUN_CNTL_ID FROM PS_LM_ACT_LE_VW A , PS_LM_SCO_DETAIL B , PS_LM_TRACK_STUDNT C , PSXLATITEMLANG D , PS_LM_CI_VW_LNG E , PS_LM_PERS_NAME_VW F , PS_LM_LC_LVW G , PS_LM_RCNTL_STDNT Q , PS_PRCSRUNCNTL T WHERE ( A.LM_ACT_ID = B.LM_ACT_ID AND A.LM_ACT_ID = C.LM_ACT_ID AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PSXLATITEMLANG D_ED WHERE D.FIELDNAME = D_ED.FIELDNAME AND D.FIELDVALUE = D_ED.FIELDVALUE AND D_ED.EFFDT <= %CurrentDateIn) AND B.LM_BLOCK_ID <> ' ' AND B.LM_SCO_ID <> 'Root' AND D.FIELDNAME = 'LM_LSN_STAT' AND C.LM_LSN_STAT = D.FIELDVALUE AND A.LM_CI_ID = E.LM_CI_ID AND C.LM_PERSON_ID = F.LM_PERSON_ID AND B.LM_SCO_ID = C.LM_SCO_ID AND C.LM_LC_ID = G.LM_LC_ID AND G.LM_ACT_ID = C.LM_ACT_ID AND B.LM_LC_ID = G.LM_LC_ID AND A.LM_ACT_ID = Q.LM_ACT_ID AND Q.RUN_CNTL_ID = T.RUN_CNTL_ID AND D.LANGUAGE_CD = E.LANGUAGE_CD AND E.LANGUAGE_CD = G.LANGUAGE_CD AND G.LANGUAGE_CD = T.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 |