LM_SRCH_ACTCIVW

(SQL View)
 Index Back

Search: View for Activity

This is the activity view. It also selects catalog items where there are no associated actitivies. In this case, the activity ID is set to zero. In addition, the eligible for enrollment for courses without activities are set to Yes. This is needed when selecting the search SES indexes.

SELECT ACT1.LM_ACT_ID , ACT1.LM_CI_ID , ACT1.LM_START_DT , %Coalesce(ACT1.LM_END_DT, %DateNull) , ACT1.LM_ENRL_TOT , ACT1.LM_ACT_STATUS , ACT1.LM_ACT_CD , DMALL1.LM_DMTHD_ID , DMTYP1.LM_DMTHD_TYPE_ID , DMTYP1.LM_DM_LONG_NM , DMTYP1.LM_DM_SHRT_NM , ACT1.LANGUAGE_CD , ACT1.LM_RESTRICT_TO_PRG , ACT1.LM_DISP_DATES , ACT1.LM_DURN_DAYS , ACT1.LM_DURN_HRS , ACT1.LM_DURN_MINS , ACT1.LM_INT_PRICE , ACT1.LM_EXT_PRICE , ACT1.LM_TRNG_UT , ACT1.CURRENCY_CD , CIT1.LM_CRSE_CODE , CIT1.LM_CS_LONG_NM , CIT1.LM_CS_DESCR , CIT1.LM_CI_STATUS , CIT1.LM_ENABLE_LRQ , ELIG1.LM_ELIG_ENRL_FLG , STMP1.LM_ROW_MAINT_DTTM FROM PS_LM_ACT ACT1 , PS_LM_SRCH_ELIG_VW ELIG1 , PS_LM_DMTHD_TBL DMALL1 , PS_LM_DMTH_TYP_TBL DMTYP1 , PS_LM_SRCH_DT1_TBL STMP1 , PS_LM_CI_TBL CIT1 WHERE EXISTS ( SELECT 1 FROM PS_LM_ACT_SEC SEC1 WHERE SEC1.LM_ACT_ID = ACT1.LM_ACT_ID) AND %Sql(LM_SRCHACTCI_NOMSG_SQL,ACT1,DMALL1,DMTYP1,CIT1,ELIG1,STMP1) UNION SELECT 0 , CIT2.LM_CI_ID , %DateNull , %DateNull , 0 , ' ' , ' ' , 0 , 0 , M3142.MESSAGE_TEXT , ' ' , ' ' , ' ' , ' ' , 0 , 0 , 0 , 0 , 0 , 0 , ' ' , CIT2.LM_CRSE_CODE , CIT2.LM_CS_LONG_NM , CIT2.LM_CS_DESCR , CIT2.LM_CI_STATUS , CIT2.LM_ENABLE_LRQ , 'X' , STMP2.LM_ROW_MAINT_DTTM FROM PS_LM_CI_TBL CIT2 LEFT OUTER JOIN PSMSGCATDEFN M3142 ON M3142.MESSAGE_SET_NBR = 18088 AND M3142.MESSAGE_NBR = 314 , PS_LM_SRCH_DTTMV0 STMP2 WHERE CIT2.LM_CI_ID = STMP2.LM_CI_ID AND %CurrentDateIn BETWEEN CIT2.EFFDT AND CIT2.LM_END_EFFDT AND NOT EXISTS ( SELECT 'X' FROM PS_LM_SRCH_DTTMV6 SDT1 WHERE SDT1.LM_CI_ID = CIT2.LM_CI_ID) UNION SELECT 0 , CIT3.LM_CI_ID , %DateNull , %DateNull , 0 , ' ' , ' ' , 0 , 0 , M3142I.MESSAGE_TEXT , ' ' , ' ' , ' ' , ' ' , 0 , 0 , 0 , 0 , 0 , 0 , ' ' , CIT3.LM_CRSE_CODE , CIT3.LM_CS_LONG_NM , CIT3.LM_CS_DESCR , CIT3.LM_CI_STATUS , CIT3.LM_ENABLE_LRQ , 'X' , STMP21.LM_ROW_MAINT_DTTM FROM PS_LM_CI_TBL CIT3 LEFT OUTER JOIN PSMSGCATDEFN M3142I ON M3142I.MESSAGE_SET_NBR = 18088 AND M3142I.MESSAGE_NBR = 314 , PS_LM_SRCH_DTTMV6 STMP21 WHERE CIT3.LM_CI_ID = STMP21.LM_CI_ID AND %CurrentDateIn BETWEEN CIT3.EFFDT AND CIT3.LM_END_EFFDT

  • Related Language Record: LM_SRCHACTCILNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
    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_START_DT Date(10) DATE Start Date
    4 LM_END_DT Date(10) DATE End Date
    5 LM_ENRL_TOT Number(6,0) INTEGER NOT NULL Enrollment Total
    6 LM_ACT_STATUS Character(1) VARCHAR2(1) NOT NULL Activity Status
    1=Active
    2=Inactive
    3=Closed
    4=Pending
    5=Concluded
    7 LM_ACT_CD Character(30) VARCHAR2(30) NOT NULL Activity Code
    8 LM_DMTHD_ID Number(10,0) DECIMAL(10) NOT NULL Delivery Method ID - This is a system generated number used to uniquely identify each new Delivery Method
    9 LM_DMTHD_TYPE_ID Number(10,0) DECIMAL(10) NOT NULL Delivery Method Type ID - This is a unique identifier for each delivery method type
    10 LM_DM_LONG_NM Character(200) VARCHAR2(200) NOT NULL Long Name - the long name of a Delivery Method Type
    11 LM_DM_SHRT_NM Character(10) VARCHAR2(10) NOT NULL Short Name - Short Name of a Delivery Method Type
    12 LM_LANG_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    CFR=Canadian French
    DAN=Danish
    DUT=Dutch
    ENG=English
    ESP=Spanish
    FRA=French
    GER=German
    GRK=Greek
    INE=International English
    ITA=Italian
    JPN=Japanese
    KOR=Korean
    POR=Portuguese
    SVE=Swedish
    THA=Thai
    ZHS=Simplified Chinese
    ZHT=Traditional Chinese
    13 LM_RESTRICT_TO_PRG Character(1) VARCHAR2(1) NOT NULL Restrict to Programs
    14 LM_DISP_DATES Character(1) VARCHAR2(1) NOT NULL Use Display Dates
    15 LM_DURN_DAYS Number(4,0) SMALLINT NOT NULL Length of Activity in Days
    16 LM_DURN_HRS Number(3,0) SMALLINT NOT NULL Duration - Hours
    17 LM_DURN_MINS Number(3,0) SMALLINT NOT NULL Duration - Minutes
    18 LM_INT_PRICE Number(19,3) DECIMAL(18,3) NOT NULL Internal Learner - Price
    19 LM_EXT_PRICE Number(19,3) DECIMAL(18,3) NOT NULL External Learner - Price
    20 LM_TRNG_UT Number(14,3) DECIMAL(13,3) NOT NULL Training Units - Price
    21 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    22 LM_CRSE_CODE Character(30) VARCHAR2(30) NOT NULL Course Code - User defined Code
    23 LM_SRCH_TITLE Character(200) VARCHAR2(200) NOT NULL Search Title
    24 LM_SRCH_DESCR Character(254) VARCHAR2(254) NOT NULL Search Description
    25 LM_CI_STATUS Character(3) VARCHAR2(3) NOT NULL Catalog Item Status - A drop down list with values indicating the current status of the learning activity.
    10=Active
    20=Inactive
    40=Pending
    26 LM_ENABLE_LRQ Character(1) VARCHAR2(1) NOT NULL New Field. Uppercase. Indicates whether learning requests are active (at various configuration levels).
    27 LM_ELIG_ENRL_FLG Character(1) VARCHAR2(1) NOT NULL Eligible for Enrollment
    N=Ineligible for Enrollment
    X=Not Applicable
    Y=Eligible For Enrollment

    Default Value: X

    28 LM_ROW_MAINT_DTTM DateTime(26) TIMESTAMP Row Maintainted DateTime