LM_PG_MG_CL_LVW

(SQL View)
 Index Back

Lang Vw Compliance Mgr Cls

Language View - Manager for Learning Compliance Pivot for Class

SELECT CRSE.OPRID , PER.LM_PERSON_ID , %Coalesce(LRNR.LM_ACT_ID, CRSE.LM_ACT_ID) , CRSE.LM_CI_ID , %Coalesce(LRNR.LM_ACT_CD, CRSE.LM_ACT_CD) , JLANG.LANGUAGE_CD , %Coalesce(LRNR.LM_TMPL_TYPE_NAME, CRSE.LM_LRN_TYPE_DESC) , %Coalesce(LRNR.LM_ACT_NAME, CRSE.LM_ACT_NAME) , %Coalesce(LRNR.XLATLONGNAME,(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=JLANG.LANGUAGE_CD)) , %Coalesce((SELECT ORGLANG.LM_ORG_DESCR FROM PS_LM_ORGANIZATION ORG, PS_LM_ORG_LANG ORGLANG WHERE ORG.LM_ORGANIZATION_ID = PER.LM_ORGANIZATION_ID AND %CurrentDateIn BETWEEN ORG.EFFDT AND ORG.LM_END_EFFDT AND ORG.EFF_STATUS = 'A' AND ORG.LM_ORGANIZATION_ID = ORGLANG.LM_ORGANIZATION_ID AND ORG.EFFDT = ORGLANG.EFFDT AND ORGLANG.LANGUAGE_CD=JLANG.LANGUAGE_CD),' ') , JLANG.LM_JOBCD_DESCR , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEMLANG XLAT WHERE XLAT.FIELDNAME='LM_LRNG_REQ' AND XLAT.FIELDVALUE=(%Coalesce(LRNR.LM_LRNG_REQ,'N')) AND XLAT.LANGUAGE_CD=JLANG.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLAT1, XLAT, %CurrentDateIn)) FROM ( SELECT DISTINCT D1.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , CRSL.LANGUAGE_CD , CRSL.LM_LRN_TYPE_DESC , CRSE.OPRID , CRSL.LM_ACT_NAME FROM PS_LM_PG_MGR_CRSE CRSE , PS_LM_PG_MR_CS_LN CRSL , PS_LM_ACT_SEC B1 , PS_LM_GROUP_PERSON D1 WHERE CRSE.LM_ACT_ID = B1.LM_ACT_ID AND B1.LM_LRNR_GROUP_ID = D1.LM_LRNR_GROUP_ID AND CRSE.LM_ACT_ID<>0 AND CRSE.LM_CI_ID<>0 AND 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 AND CRSE.LM_CURR_USER_FLG <> 'Y') CRSE JOIN PS_LM_PERSON_ATTRB PER ON CRSE.LM_PERSON_ID = PER.LM_PERSON_ID JOIN PS_LM_PERSON_OPRID OPR ON CRSE.OPRID = OPR.OPRID AND PER.LM_MANAGER_ID = OPR.LM_PERSON_ID JOIN PS_LM_JOBCODE_TBL JTBL ON JTBL.LM_JOBCODE_ID = PER.LM_JOBCODE_ID JOIN PS_LM_JOBCODE_LANG JLANG ON JTBL.LM_JOBCODE_ID = JLANG.LM_JOBCODE_ID AND JTBL.EFFDT = JLANG.EFFDT AND JTBL.EFF_STATUS = 'A' AND JLANG.LANGUAGE_CD=CRSE.LANGUAGE_CD LEFT OUTER JOIN ( SELECT LRNR.LM_PERSON_ID , LRNR.LM_ACT_ID , ACT.LM_CI_ID , ACT.LM_ACT_CD , LRNR.LM_ENRLMT_ID , ELANG.LANGUAGE_CD , ( SELECT CI.LM_CS_LONG_NM FROM PS_LM_CI_LANG CI WHERE LRNR.LM_CI_ID = CI.LM_CI_ID AND CI.LANGUAGE_CD=ELANG.LANGUAGE_CD) AS LM_ACT_NAME , LRNR.LM_STTS , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEMLANG XLAT WHERE XLAT.FIELDNAME = 'LM_STTS' AND XLAT.FIELDVALUE = LRNR.LM_STTS AND XLAT.LANGUAGE_CD=ELANG.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLAT2, XLAT, %CurrentDateIn)) AS XLATLONGNAME , ( SELECT %Upper(%Substring(MSGLANG.MESSAGE_TEXT, 1, 10)) FROM PSMSGCATLANG MSGLANG WHERE MSGLANG.MESSAGE_SET_NBR = 18095 AND MSGLANG.MESSAGE_NBR = 5 AND MSGLANG.LANGUAGE_CD = ELANG.LANGUAGE_CD) AS LM_TMPL_TYPE_NAME , LRNR.LM_ENRL_DT , LRNR.LM_COMPL_DT , CASE WHEN LRNR.LM_STTS = 'PLAN' THEN ( SELECT LM_TARGET_DATE FROM PS_LM_LPLN_DTL PLN WHERE PLN.LM_PERSON_ID =LRNR.LM_PERSON_ID AND PLN.LM_ENRLMT_ID = LRNR.LM_ENRLMT_ID) ELSE LRNR.LM_TARGET_CMPL_DT END AS LM_TARGET_CMPL_DT , CASE WHEN ( SELECT REQ1.LM_OBJV_NEEDED FROM PS_LM_LRNR_OBJV REQ1 WHERE REQ1.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ1.LM_ENRLMT_ID=LRNR.LM_ENRLMT_ID AND REQ1.LM_ACT_ID=LRNR.LM_ACT_ID AND REQ1.LM_CI_ID=LRNR.LM_CI_ID AND REQ1.LM_TARGET_CMPL_DT IS NOT NULL AND REQ1.LM_LRNR_OBJV_ID =( SELECT MAX(REQ2.LM_LRNR_OBJV_ID) FROM PS_LM_LRNR_OBJV REQ2 WHERE REQ1.LM_PERSON_ID= REQ2.LM_PERSON_ID AND REQ1.LM_ENRLMT_ID=REQ2.LM_ENRLMT_ID AND REQ1.LM_ACT_ID=REQ2.LM_ACT_ID AND REQ1.LM_CI_ID=REQ2.LM_CI_ID))='Y' THEN 'Y' WHEN ( SELECT REQ2.LM_REQUIRED FROM PS_LM_LPLN_DTL REQ2 WHERE REQ2.LM_PERSON_ID= LRNR.LM_PERSON_ID AND REQ2.LM_ENRLMT_ID=LRNR.LM_ENRLMT_ID AND REQ2.LM_TARGET_DATE IS NOT NULL AND REQ2.LM_LPLN_ID= ( SELECT MAX (REQ3.LM_LPLN_ID) FROM PS_LM_LPLN_DTL REQ3 WHERE REQ2.LM_PERSON_ID =REQ3.LM_PERSON_ID AND REQ2.LM_ENRLMT_ID = REQ3.LM_ENRLMT_ID AND REQ3.LM_ENRLMT_ID<>0))='Y' THEN 'Y' ELSE 'N' END AS LM_LRNG_REQ FROM PS_LM_ENRLMT LRNR, PS_LM_ACT ACT , PS_LM_ENRLMT_LANG ELANG WHERE LRNR.LM_ACT_ID =ACT.LM_ACT_ID AND LRNR.LM_ENRLMT_ID=ELANG.LM_ENRLMT_ID AND LRNR.LM_ENRL_DT=( SELECT MAX(LRNR1.LM_ENRL_DT) FROM PS_LM_ENRLMT LRNR1 WHERE LRNR.LM_PERSON_ID =LRNR1.LM_PERSON_ID AND LRNR.LM_CI_ID = LRNR1.LM_CI_ID AND LRNR.LM_ACT_ID = LRNR1.LM_ACT_ID ) AND LRNR.LM_ENRLMT_ID = ( SELECT MAX(LRNR2.LM_ENRLMT_ID) FROM PS_LM_ENRLMT LRNR2 WHERE LRNR.LM_PERSON_ID =LRNR2.LM_PERSON_ID AND LRNR.LM_CI_ID = LRNR2.LM_CI_ID AND LRNR.LM_ACT_ID = LRNR2.LM_ACT_ID AND LRNR.LM_ENRL_DT=LRNR2.LM_ENRL_DT)) LRNR ON CRSE.LM_CI_ID = LRNR.LM_CI_ID AND CRSE.LM_ACT_ID = LRNR.LM_ACT_ID AND PER.LM_PERSON_ID = LRNR.LM_PERSON_ID AND JLANG.LANGUAGE_CD =LRNR.LANGUAGE_CD WHERE PER.LM_ACTIVE = 'Y' AND PER.LM_EMPL_RCD = ( SELECT MIN(JOB2.LM_EMPL_RCD) FROM PS_LM_PERSON_ATTRB JOB2 WHERE JOB2.LM_PERSON_ID = PER.LM_PERSON_ID AND JOB2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = JOB2.LM_PERSON_ID AND LM_EMPL_RCD = JOB2.LM_EMPL_RCD AND EFFDT <= PER.EFFDT) AND JOB2.LM_END_EFFDT >= %CurrentDateIn AND ((JOB2.LM_ACTIVE = 'Y' AND JOB2.LM_JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_LM_PERSON_ATTRB JOB3 WHERE JOB3.LM_PERSON_ID = JOB2.LM_PERSON_ID AND JOB3.LM_EMPL_RCD <> JOB2.LM_EMPL_RCD AND JOB3.LM_END_EFFDT >= %CurrentDateIn AND JOB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = JOB3.LM_PERSON_ID AND LM_EMPL_RCD = JOB3.LM_EMPL_RCD AND EFFDT <= %CurrentDateIn AND LM_END_EFFDT >= %CurrentDateIn) AND ((JOB3.LM_ACTIVE = 'Y' AND (JOB2.LM_ACTIVE <> 'Y' OR (JOB3.LM_JOB_INDICATOR = 'P' AND JOB2.LM_JOB_INDICATOR <> 'P'))) OR (JOB3.LM_JOB_INDICATOR = 'P' AND JOB2.LM_JOB_INDICATOR <> 'P' AND JOB2.LM_ACTIVE <> 'Y'))))) AND PER.LM_END_EFFDT >= %CurrentDateIn

  • Related Language Record for LM_PG_MGR_CL_VW

    • # 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