LM_PG_LCMP_LVW4

(SQL View)
 Index Back

SELECT DISTINCT CRSE.OPRID , CRSE.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , XLATLNGTBL.LANGUAGE_CD , CASE WHEN (CRSE.LM_ACT_ID <> 0 AND CRSE.LM_CI_ID <> 0) THEN %Upper((SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATLANG LNG WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 5 AND LNG.LANGUAGE_CD =XLATLNGTBL.LANGUAGE_CD)) WHEN (CRSE.LM_ACT_ID = 0 AND CRSE.LM_CI_ID <> 0) THEN %Upper((SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATLANG LNG WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 6 AND LNG.LANGUAGE_CD =XLATLNGTBL.LANGUAGE_CD)) ELSE %Upper((SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATLANG LNG WHERE MESSAGE_SET_NBR = 18095 AND MESSAGE_NBR = 7 AND LNG.LANGUAGE_CD =XLATLNGTBL.LANGUAGE_CD)) END , CASE WHEN CRSE.LM_ACT_ID <>0 AND CRSE.LM_CI_ID= 0 THEN ( SELECT PRG.LM_PRG_LONG_NM FROM PS_LM_PRG_LANG PRG WHERE CRSE.LM_ACT_ID=PRG.LM_PRG_ID AND %EffdtCheck(LM_PRG PRG1, PRG,%CurrentDateIn) AND PRG.LANGUAGE_CD =XLATLNGTBL.LANGUAGE_CD) ELSE ( SELECT CI.LM_CS_LONG_NM FROM PS_LM_CI_LANG CI WHERE CRSE.LM_CI_ID=CI.LM_CI_ID AND %EffdtCheck(LM_CI_TBL CI1, CI,%CurrentDateIn) AND CI.LANGUAGE_CD =XLATLNGTBL.LANGUAGE_CD) END , CASE WHEN LRNR.LM_STTS IS NULL THEN ( SELECT %Substring(MSGLANG.MESSAGE_TEXT, 1, 12) FROM PSMSGCATLANG MSGLANG WHERE MSGLANG.MESSAGE_SET_NBR = 18095 AND MSGLANG.MESSAGE_NBR = 2 AND MSGLANG.LANGUAGE_CD=XLATLNGTBL.LANGUAGE_CD) ELSE XLATLNGTBL.xlatlongname END , PERSL.LM_ORG_DESCR , PERSL.LM_JOB_TITLE , ( SELECT XLATLNG.xlatlongname FROM PSXLATITEMLANG XLATLNG WHERE XLATLNG.fieldvalue=%Coalesce(LRNR.LM_LRNG_REQ, 'N') AND XLATLNG.fieldname='LM_LRNG_REQ' AND XLATLNG.LANGUAGE_CD=XLATLNGTBL.LANGUAGE_CD) , ( SELECT %Substring(XLATLNG.xlatlongname,1,10) FROM PSXLATITEMLANG XLATLNG WHERE XLATLNG.fieldvalue=CRSE.LM_TEAM_FLG AND XLATLNG.fieldname='LM_TEAM_FLG' AND XLATLNG.LANGUAGE_CD=XLATLNGTBL.LANGUAGE_CD) FROM PS_LM_PG_TM_CRS_VW CRSE LEFT OUTER JOIN PS_LM_PG_LEARN_VW2 LRNR ON CRSE.LM_ACT_ID= LRNR.LM_ACT_ID AND CRSE.LM_CI_ID= LRNR.LM_CI_ID AND CRSE.LM_ACT_CD = LRNR.LM_ACT_CD AND CRSE.LM_PERSON_ID = LRNR.LM_PERSON_ID LEFT OUTER JOIN PS_LM_PERS_DTL_LVW PERSL ON PERSL.LM_PERSON_ID=CRSE.LM_PERSON_ID LEFT OUTER JOIN PS_LM_PG_MR_CS_LN CRSL ON CRSL.OPRID=CRSE.OPRID AND CRSL.LM_ACT_ID=CRSE.LM_ACT_ID AND CRSL.LM_CI_ID=CRSE.LM_CI_ID AND CRSL.LM_ACT_CD = CRSE.LM_ACT_CD, PSXLATITEMLANG XLATLNGTBL WHERE XLATLNGTBL.FIELDVALUE= CASE WHEN LRNR.LM_STTS IS NULL THEN 'INPO' ELSE LRNR.LM_STTS END AND XLATLNGTBL.FIELDNAME='LM_STTS'

  • Related Language Record for LM_PG_LCOMP_VW4

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 LM_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL ELM Person ID
    3 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
    4 LM_CI_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Item ID - System Generated ID Number associated with each Catalog Item
    5 LM_ACT_CD Character(30) VARCHAR2(30) NOT NULL Activity Code
    6 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    7 LM_TMPL_TYPE_NAME Character(30) VARCHAR2(30) NOT NULL Template Name
    8 LM_ACT_NAME Character(254) VARCHAR2(254) NOT NULL Activity Name
    9 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
    10 LM_ORG_DESCR Character(50) VARCHAR2(50) NOT NULL Customer Description
    11 LM_JOB_TITLE Character(30) VARCHAR2(30) NOT NULL Job Title
    12 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    13 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description