LM_SRCHFCLT_LNG

(SQL View)
 Index Back

SES: Facility and Location

This is the related language for Facility and location data for Activities. The message catalogs, country and state descriptions have related language tables.

SELECT E.LM_CI_ID , B.LM_ACT_ID , B.LM_LC_ID , B.LM_SES_ID , B.LM_FCLTY_ID , M0L.LANGUAGE_CD , %Coalesce(CL.DESCR ,M0L.MESSAGE_TEXT) , %Coalesce(DL.DESCR ,M1L.MESSAGE_TEXT) , %Coalesce(A.CITY , M2L.MESSAGE_TEXT) FROM PS_LM_ACT E , 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_COUNTRYTBL_LANG CL ON A.COUNTRY = CL.COUNTRY LEFT OUTER JOIN PS_STATE_TBL_LANG DL ON (A.STATE = DL.STATE AND A.COUNTRY = DL.COUNTRY) LEFT OUTER JOIN PSMSGCATLANG M0L ON M0L.MESSAGE_SET_NBR = 18088 AND M0L.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATLANG M1L ON M1L.MESSAGE_SET_NBR = 18088 AND M1L.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATLANG M2L ON M2L.MESSAGE_SET_NBR = 18088 AND M2L.MESSAGE_NBR = 302 WHERE 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)) AND M0L.LANGUAGE_CD = M1L.LANGUAGE_CD AND M0L.LANGUAGE_CD = M2L.LANGUAGE_CD AND CL.LANGUAGE_CD = M0L.LANGUAGE_CD AND DL.LANGUAGE_CD = M0L.LANGUAGE_CD UNION SELECT E.LM_CI_ID , B.LM_ACT_ID , B.LM_LC_ID , B.LM_SES_ID , B.LM_FCLTY_ID , M0L.LANGUAGE_CD , %Coalesce(C.DESCR ,M0L.MESSAGE_TEXT) , %Coalesce(D.DESCR ,M1L.MESSAGE_TEXT) , %Coalesce(A.CITY , M2L.MESSAGE_TEXT) FROM PS_LM_ACT E , 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 PSMSGCATLANG M0L ON M0L.MESSAGE_SET_NBR = 18088 AND M0L.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATLANG M1L ON M1L.MESSAGE_SET_NBR = 18088 AND M1L.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATLANG M2L ON M2L.MESSAGE_SET_NBR = 18088 AND M2L.MESSAGE_NBR = 302 WHERE 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)) AND M0L.LANGUAGE_CD = M1L.LANGUAGE_CD AND M0L.LANGUAGE_CD = M2L.LANGUAGE_CD AND NOT EXISTS ( SELECT 1 FROM PS_COUNTRYTBL_LANG CL WHERE C.COUNTRY = CL.COUNTRY AND CL.LANGUAGE_CD = M0L.LANGUAGE_CD) AND NOT EXISTS ( SELECT 1 FROM PS_STATE_TBL_LANG DL WHERE D.COUNTRY = DL.COUNTRY AND D.STATE = DL.STATE AND DL.LANGUAGE_CD = M0L.LANGUAGE_CD) UNION SELECT E.LM_CI_ID , B.LM_ACT_ID , B.LM_LC_ID , B.LM_SES_ID , B.LM_FCLTY_ID , CL.LANGUAGE_CD , %Coalesce(CL.DESCR ,M0A.MESSAGE_TEXT) , %Coalesce(D.DESCR ,M1A.MESSAGE_TEXT) , %Coalesce(A.CITY , M2A.MESSAGE_TEXT) FROM PS_LM_ACT E , 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_COUNTRYTBL_LANG CL ON A.COUNTRY = CL.COUNTRY LEFT OUTER JOIN PS_STATE_TBL D ON (A.STATE = D.STATE AND A.COUNTRY = D.COUNTRY) LEFT OUTER JOIN PSMSGCATDEFN M0A ON M0A.MESSAGE_SET_NBR = 18088 AND M0A.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATDEFN M1A ON M1A.MESSAGE_SET_NBR = 18088 AND M1A.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATDEFN M2A ON M2A.MESSAGE_SET_NBR = 18088 AND M2A.MESSAGE_NBR = 302 WHERE 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)) AND NOT EXISTS ( SELECT 1 FROM PSMSGCATLANG M0L WHERE M0A.MESSAGE_SET_NBR = M0L.MESSAGE_SET_NBR AND M0A.MESSAGE_NBR = M0L.MESSAGE_NBR AND M0L.LANGUAGE_CD = CL.LANGUAGE_CD) AND NOT EXISTS ( SELECT 1 FROM PS_STATE_TBL_LANG DL WHERE D.COUNTRY = DL.COUNTRY AND D.STATE = DL.STATE AND DL.LANGUAGE_CD = CL.LANGUAGE_CD) UNION SELECT E.LM_CI_ID , B.LM_ACT_ID , B.LM_LC_ID , B.LM_SES_ID , B.LM_FCLTY_ID , DL.LANGUAGE_CD , %Coalesce(C.DESCR ,M0A.MESSAGE_TEXT) , %Coalesce(DL.DESCR ,M1A.MESSAGE_TEXT) , %Coalesce(A.CITY , M2A.MESSAGE_TEXT) FROM PS_LM_ACT E , 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_LANG DL ON (A.STATE = DL.STATE AND A.COUNTRY = DL.COUNTRY) LEFT OUTER JOIN PSMSGCATDEFN M0A ON M0A.MESSAGE_SET_NBR = 18088 AND M0A.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATDEFN M1A ON M1A.MESSAGE_SET_NBR = 18088 AND M1A.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATDEFN M2A ON M2A.MESSAGE_SET_NBR = 18088 AND M2A.MESSAGE_NBR = 302 WHERE 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)) AND NOT EXISTS ( SELECT 1 FROM PSMSGCATLANG M0L WHERE M0A.MESSAGE_SET_NBR = M0L.MESSAGE_SET_NBR AND M0A.MESSAGE_NBR = M0L.MESSAGE_NBR AND M0L.LANGUAGE_CD = DL.LANGUAGE_CD) AND NOT EXISTS ( SELECT 1 FROM PS_COUNTRYTBL_LANG CL WHERE C.COUNTRY = CL.COUNTRY AND CL.LANGUAGE_CD = DL.LANGUAGE_CD) UNION SELECT E1.LM_CI_ID , E1.LM_ACT_ID , 0 , 0 , 0 , M0L.LANGUAGE_CD , M0L.MESSAGE_TEXT , M1L.MESSAGE_TEXT , M2L.MESSAGE_TEXT FROM PS_LM_ACT E1 LEFT OUTER JOIN PSMSGCATLANG M0L ON M0L.MESSAGE_SET_NBR = 18088 AND M0L.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATLANG M1L ON M1L.MESSAGE_SET_NBR = 18088 AND M1L.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATLANG M2L ON M2L.MESSAGE_SET_NBR = 18088 AND M2L.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 , M0L.LANGUAGE_CD , M0L.MESSAGE_TEXT , M1L.MESSAGE_TEXT , M2L.MESSAGE_TEXT FROM PS_LM_CI_TBL CI LEFT OUTER JOIN PSMSGCATLANG M0L ON M0L.MESSAGE_SET_NBR = 18088 AND M0L.MESSAGE_NBR = 300 LEFT OUTER JOIN PSMSGCATLANG M1L ON M1L.MESSAGE_SET_NBR = 18088 AND M1L.MESSAGE_NBR = 301 LEFT OUTER JOIN PSMSGCATLANG M2L ON M2L.MESSAGE_SET_NBR = 18088 AND M2L.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)

  • Related Language Record for LM_SRCH_FCLT_VW

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    7 LM_SRCH_COUNTRY Character(30) VARCHAR2(30) NOT NULL Country Description
    8 LM_SRCH_STATE Character(30) VARCHAR2(30) NOT NULL State Description
    9 LM_SRCH_CITY Character(30) VARCHAR2(30) NOT NULL City