LM_SRCH_FCLT_VW

(SQL View)
 Index Back

SES: Facility and Location

Facility and location data for Activities. This also has the catalog items where there are no associated activities.

SELECT DISTINCT E.LM_CI_ID , B.LM_ACT_ID , B.LM_LC_ID , B.LM_SES_ID , B.LM_FCLTY_ID , ' ' , A.LM_FCLTY , B.LM_ROOM_ID , B.LM_SES_DT , B.LM_START_TM , B.LM_END_TM , B.TIMEZONE , R.LM_REQD_FLAG , A.COUNTRY , A.STATE , A.CITY , B.LM_SS_FCLTY_DISP , %Coalesce(C.DESCR ,M300A.MESSAGE_TEXT) , %Coalesce(D.DESCR ,M301A.MESSAGE_TEXT) , %Coalesce(A.CITY , M302A.MESSAGE_TEXT) , A.LM_ROW_MAINT_DTTM FROM PS_LM_ACT E , PS_LM_ACT_ROUTES R , PS_LM_SES B LEFT OUTER JOIN PS_LM_FCLTY_TBL A ON B.LM_FCLTY_ID = A.LM_FCLTY_ID LEFT OUTER JOIN PS_COUNTRY_TBL C ON A.COUNTRY = C.COUNTRY LEFT OUTER JOIN PS_STATE_TBL D ON (A.STATE = D.STATE AND A.COUNTRY = D.COUNTRY) LEFT OUTER JOIN PSMSGCATDEFN M300A ON M300A.MESSAGE_SET_NBR = 18088 AND M300A.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATDEFN M301A ON M301A.MESSAGE_SET_NBR = 18088 AND M301A.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATDEFN M302A ON M302A.MESSAGE_SET_NBR = 18088 AND M302A.MESSAGE_NBR = 302 WHERE R.LM_ACT_ID = B.LM_ACT_ID AND R.LM_LC_ID = B.LM_LC_ID AND B.LM_ACT_ID = E.LM_ACT_ID AND EXISTS ( SELECT 1 FROM PS_LM_ACT_SEC S WHERE S.LM_ACT_ID = E.LM_ACT_ID) AND (E.LM_END_DT IS NULL OR (%CurrentDateIn <= E.LM_END_DT AND E.LM_END_DT IS NOT NULL)) UNION SELECT DISTINCT E1.LM_CI_ID , E1.LM_ACT_ID , 0 , 0 , 0 , ' ' , ' ' , 0 , %DateNull , %TimeNull , %TimeNull , ' ' , 'N' , ' ' , ' ' , ' ' , 'N' , M300B.MESSAGE_TEXT , M301B.MESSAGE_TEXT , M302B.MESSAGE_TEXT , E1.LM_ROW_MAINT_DTTM FROM PS_LM_ACT E1 LEFT OUTER JOIN PSMSGCATDEFN M300B ON M300B.MESSAGE_SET_NBR = 18088 AND M300B.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATDEFN M301B ON M301B.MESSAGE_SET_NBR = 18088 AND M301B.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATDEFN M302B ON M302B.MESSAGE_SET_NBR = 18088 AND M302B.MESSAGE_NBR = 302 WHERE (E1.LM_END_DT IS NULL OR (%CurrentDateIn <= E1.LM_END_DT AND E1.LM_END_DT IS NOT NULL) ) AND NOT EXISTS ( SELECT A1.LM_ACT_ID FROM PS_LM_SES A1 WHERE A1.LM_ACT_ID = E1.LM_ACT_ID) AND EXISTS ( SELECT B1.LM_ACT_ID FROM PS_LM_ACT B1 WHERE B1.LM_ACT_ID = E1.LM_ACT_ID) UNION SELECT CI.LM_CI_ID , 0 , 0 , 0 , 0 , ' ' , ' ' , 0 , %DateNull , %TimeNull , %TimeNull , ' ' , 'N' , ' ' , ' ' , ' ' , 'N' , M300C.MESSAGE_TEXT , M301C.MESSAGE_TEXT , M302C.MESSAGE_TEXT , CI.LM_ROW_MAINT_DTTM FROM PS_LM_CI_TBL CI LEFT OUTER JOIN PSMSGCATDEFN M300C ON M300C.MESSAGE_SET_NBR = 18088 AND M300C.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATDEFN M301C ON M301C.MESSAGE_SET_NBR = 18088 AND M301C.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATDEFN M302C ON M302C.MESSAGE_SET_NBR = 18088 AND M302C.MESSAGE_NBR = 302 WHERE NOT EXISTS ( SELECT A2.LM_CI_ID FROM PS_LM_ACT A2 WHERE A2.LM_CI_ID = CI.LM_CI_ID) AND %CurrentDateIn BETWEEN CI.EFFDT AND CI.LM_END_EFFDT

  • Related Language Record: LM_SRCHFCLT_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 LM_CI_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Item ID - System Generated ID Number associated with each Catalog Item
    2 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
    3 LM_LC_ID Number(10,0) DECIMAL(10) NOT NULL Learning Component ID - Blended Learning
    4 LM_SES_ID Number(10,0) DECIMAL(10) NOT NULL Session ID
    5 LM_FCLTY_ID Number(10,0) DECIMAL(10) NOT NULL Facility ID
    6 LM_COMB_FCLTY_KEY Character(60) VARCHAR2(60) NOT NULL Use this field to concatenate key fields LM_CI_ID, LM_ACT_ID, LM_LC_ID, LM_SES_ID and LM_FCLTY_ID. This is only used for SES search.
    7 LM_FCLTY Character(50) VARCHAR2(50) NOT NULL Facility Description
    8 LM_ROOM_ID Number(10,0) DECIMAL(10) NOT NULL Room ID
    9 LM_SES_DT Date(10) DATE Session Date
    10 LM_START_TM Time(15) TIMESTAMP Start Time
    11 LM_END_TM Time(15) TIMESTAMP End Time
    12 TIMEZONE Character(9) VARCHAR2(9) NOT NULL Time Zone

    Prompt Table: PSTIMEZONE

    13 LM_REQD_FLAG Character(1) VARCHAR2(1) NOT NULL Learning Component required? - Blended Learning
    14 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    15 STATE Character(6) VARCHAR2(6) NOT NULL State
    16 CITY Character(30) VARCHAR2(30) NOT NULL City
    17 LM_SS_FCLTY_DISP Character(1) VARCHAR2(1) NOT NULL Display facility through self service
    18 LM_SRCH_COUNTRY Character(30) VARCHAR2(30) NOT NULL Country Description
    19 LM_SRCH_STATE Character(30) VARCHAR2(30) NOT NULL State Description
    20 LM_SRCH_CITY Character(30) VARCHAR2(30) NOT NULL City
    21 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.