LM_LRQ_VW_LANG

(SQL View)
 Index Back

Learning Request with CATG/CI

This view returns details of learning requests that have categories and catalog items

SELECT 0 , B.LM_CATG_ID , A.LM_LRQ_ID , B.LANGUAGE_CD , B.LM_CS_LONG_NM , C.LM_TITLE FROM PS_LM_LRN_REQ A , PS_LM_CATG_LANG B ,PS_LM_LRN_REQ_LANG C WHERE A.LM_CATG_ID = B.LM_CATG_ID AND A.LM_LRQ_ID=C.LM_LRQ_ID AND B.LANGUAGE_CD=C.LANGUAGE_CD UNION SELECT 0 , E.LM_CATG_ID , D.LM_LRQ_ID , F.LANGUAGE_CD , E.LM_CS_LONG_NM , F.LM_TITLE FROM PS_LM_LRN_REQ D , PS_LM_CATG_TBL E , PS_LM_LRN_REQ_LANG F WHERE D.LM_CATG_ID = E.LM_CATG_ID AND D.LM_LRQ_ID = F.LM_LRQ_ID AND NOT EXISTS ( SELECT 'X' FROM PS_LM_CATG_LANG H WHERE H.LM_CATG_ID = E.LM_CATG_ID AND H.LANGUAGE_CD = F.LANGUAGE_CD) UNION SELECT 0 , J.LM_CATG_ID , I.LM_LRQ_ID , J.LANGUAGE_CD , J.LM_CS_LONG_NM , I.LM_TITLE FROM PS_LM_LRN_REQ I , PS_LM_CATG_LANG J WHERE I.LM_CATG_ID = J.LM_CATG_ID AND NOT EXISTS ( SELECT 'X' FROM PS_LM_LRN_REQ_LANG K WHERE K.LM_LRQ_ID = I.LM_LRQ_ID AND K.LANGUAGE_CD = J.LANGUAGE_CD) UNION SELECT A2.LM_CI_ID , 0 , A2.LM_LRQ_ID , L1.LANGUAGE_CD , C2.LM_CS_LONG_NM , '' FROM PS_LM_LRN_REQ A2 , PS_LM_CI_TBL C2 , PS_LM_LRN_REQ_LANG L1 , PS_LM_CI_LANG L2 WHERE A2.LM_CI_ID = C2.LM_CI_ID AND C2.LM_CI_ID = L2.LM_CI_ID AND L1.LANGUAGE_CD = L2.LANGUAGE_CD AND A2.LM_LRQ_ID = L1.LM_LRQ_ID AND A2.LM_LRQ_STATUS IN ('1', '3') AND %CurrentDateIn BETWEEN C2.EFFDT AND C2.LM_END_EFFDT AND C2.LM_CI_STATUS = '10' UNION SELECT E2.LM_CI_ID , 0 , D2.LM_LRQ_ID , F2.LANGUAGE_CD , E2.LM_CS_LONG_NM , '' FROM PS_LM_LRN_REQ D2 , PS_LM_CI_TBL E2 , PS_LM_LRN_REQ_LANG F2 WHERE D2.LM_CI_ID = E2.LM_CI_ID AND D2.LM_LRQ_ID = F2.LM_LRQ_ID AND D2.LM_LRQ_STATUS IN ('1', '3') AND %CurrentDateIn BETWEEN E2.EFFDT AND E2.LM_END_EFFDT AND E2.LM_CI_STATUS = '10' AND NOT EXISTS ( SELECT 'X' FROM PS_LM_CI_LANG H2 WHERE H2.LM_CI_ID = E2.LM_CI_ID AND H2.LANGUAGE_CD = F2.LANGUAGE_CD) UNION SELECT E3.LM_CI_ID , 0 , D3.LM_LRQ_ID , F3.LANGUAGE_CD , F3.LM_CS_LONG_NM , '' FROM PS_LM_LRN_REQ D3 , PS_LM_CI_TBL E3 , PS_LM_CI_LANG F3 WHERE D3.LM_CI_ID = E3.LM_CI_ID AND E3.LM_CI_ID = F3.LM_CI_ID AND D3.LM_LRQ_STATUS IN ('1', '3') AND E3.LM_CI_STATUS = '10' AND NOT EXISTS ( SELECT 'X' FROM PS_LM_LRN_REQ_LANG H3 WHERE H3.LM_LRQ_ID = D3.LM_LRQ_ID AND H3.LANGUAGE_CD = F3.LANGUAGE_CD)

  • Related Language Record for LM_LRQ_CT_CI_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_CATG_ID Number(10,0) DECIMAL(10) NOT NULL Category ID - System generated number used to uniquely identify a Category.
    3 LM_LRQ_ID Number(10,0) DECIMAL(10) NOT NULL New field for ELM 9.0, stores unique identifier for a single learning request.
    4 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    5 LM_CS_LONG_NM Character(200) VARCHAR2(200) NOT NULL Long Name - Describes the long name of an object
    6 LM_TITLE Character(30) VARCHAR2(30) NOT NULL Field to store title of an object