LM_REG_PRG_VW

(SQL View)
 Index Back

Learner Programs

Lists all programs (any status) associated with a learner. Joins to the program table with the program registration table to bring in all of the program information, selecting the correct effective dated program row based upon the learners registration date.

SELECT A.LM_PERSON_ID ,A.LM_PRG_REG_ID ,A.LM_PRG_ID ,B.EFFDT ,B.LM_PRG_LONG_NM ,B.LM_PRG_TYPE ,A.LM_TYPE ,A.LM_ENRL_DT ,A.LM_PRG_STTS ,A.LM_COMPL_DT ,CASE WHEN A.LM_PRG_STTS='WARN' THEN D.LM_RECERT_DT ELSE A.LM_TARGET_CMPL_DT END ,A.LM_DP_DT ,A.LM_CERT_EFFDT ,A.LM_ACT_REQ ,A.LM_INITIATED_DT ,A.LM_REQUESTOR_ID ,A.LM_STTS_CHANGE_DT ,D.LM_EXPR_DT ,D.LM_RECERT_DT ,B.LM_PRG_CD ,A.LM_MAND_ENRL_FLG FROM PS_LM_PRG_REG_VW A LEFT JOIN PS_LM_LRNR_CERT D ON A.LM_PRG_REG_ID=D.LM_PRG_REG_ID , PS_LM_PRG B WHERE A.LM_PRG_ID = B.LM_PRG_ID AND A.LM_ENRL_DT BETWEEN B.EFFDT AND B.LM_END_EFFDT AND A.LM_PRG_STTS IN ('ENRL','INPO','PEAP','PEPA','PPYA','WARN')

  • Related Language Record: LM_REG_PRG_LVW

    • # 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_PRG_REG_ID Number(10,0) DECIMAL(10) NOT NULL The Program Registration ID is a unique identifier for a learner's program enrollment.
    3 LM_PRG_ID Number(10,0) DECIMAL(10) NOT NULL Program ID is a unique identifier for curriculum and certification Programs.
    4 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    5 LM_PRG_LONG_NM Character(200) VARCHAR2(200) NOT NULL Program Long Name
    6 LM_PRG_TYPE Character(2) VARCHAR2(2) NOT NULL Program Type
    CE=Certification
    CU=Curriculum
    7 LM_TYPE Character(4) VARCHAR2(4) NOT NULL Type of the Enrollment. This field for now will have only one value STDD
    ADHC=Adhoc
    PLAN=Planned
    RSRV=Reserved
    STDD=Standard
    8 LM_ENRL_DT Date(10) DATE Enrollment/Registration Date
    9 LM_PRG_STTS Character(4) VARCHAR2(4) NOT NULL Program Reg Status
    CANC=Dropped
    COMP=Completed
    DECL=Decline
    ENRL=Registered
    EXPR=Expired
    INCO=Not Completed
    INPO=In-Progress
    PEAP=Pending Approval
    PEPA=Pending Payment
    PLAN=Planned
    PPYA=Payment Approval
    REIS=Reissue
    REVK=Revoked
    WAIV=Waived
    WARN=Warning
    10 LM_COMPL_DT Date(10) DATE The date on which this Enrollment was marked Completed / InComplete
    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_DP_DT Date(10) DATE Drop Date
    13 LM_CERT_EFFDT Date(10) DATE Current Certification Date
    14 LM_ACT_REQ Character(1) VARCHAR2(1) NOT NULL Activity Required
    N=No
    Y=Yes

    Y/N Table Edit

    15 LM_INITIATED_DT Date(10) DATE The date on which the course was launched by the student. This applies only for WBT.
    16 LM_REQUESTOR_ID Number(15,0) DECIMAL(15) NOT NULL Requestor
    17 LM_STTS_CHANGE_DT Date(10) DATE Status Chage Date
    18 LM_EXPR_DT Date(10) DATE Certification Expiration Date is a fixed, preset date when the certification will expire.
    19 LM_RECERT_DT Date(10) DATE Recertification Date
    20 LM_PRG_CD Character(30) VARCHAR2(30) NOT NULL Program Code is a user defined field. This field allows duplicates and is not part of the primary key structure.
    21 LM_MAND_ENRL_FLG Character(1) VARCHAR2(1) NOT NULL Mandatory Learning Flag