LM_PG_AD_PR_LVW

(SQL View)
 Index Back

Lang Vw Compliance Admin Prgm

Language View - Administator Learning Compliance Pivot for Program

SELECT CRSE.OPRID , PER.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , JLANG.LANGUAGE_CD , CRSE.LM_LRN_TYPE_DESC , %Coalesce((SELECT XLAT.XLATLONGNAME FROM PSXLATITEMLANG XLAT WHERE XLAT.FIELDNAME = 'LM_PER_ORG' AND XLAT.FIELDVALUE=PER.LM_PER_ORG AND XLAT.LANGUAGE_CD=JLANG.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLAT1, XLAT, %CurrentDateIn)) ,(SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATLANG MSGLANG WHERE MSGLANG.MESSAGE_SET_NBR = 18095 AND MSGLANG.MESSAGE_NBR = 21 AND MSGLANG.LANGUAGE_CD=JLANG.LANGUAGE_CD)) , 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 XLAT2, XLAT, %CurrentDateIn)) , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEMLANG XLAT WHERE XLAT.FIELDNAME='LM_TEAM_FLG' AND XLAT.FIELDVALUE=(CASE WHEN PER.LM_MANAGER_ID=CRSE.LM_OPR_ID THEN 'Y' ELSE 'N' END) AND XLAT.LANGUAGE_CD=JLANG.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLAT3, XLAT, %CurrentDateIn)) FROM ( SELECT 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 , CRSE.LM_PERSON_ID AS LM_OPR_ID , CRSL.LM_ACT_NAME FROM PS_LM_PG_MGR_CRSE CRSE , PS_LM_PG_MR_CS_LN CRSL , PS_LM_PRG_SEC B1 , PS_LM_GROUP_PERSON D1 WHERE CRSE.LM_ACT_ID = B1.LM_PRG_ID AND B1.LM_LRNR_GROUP_ID = D1.LM_LRNR_GROUP_ID AND %CurrentDateIn BETWEEN B1.EFFDT AND B1.LM_END_EFFDT 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_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_PRG_ID , LRNR.LM_PRG_REG_ID , ELANG.LANGUAGE_CD , LRNR.LM_PRG_STTS , ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEMLANG XLAT WHERE XLAT.FIELDNAME = 'LM_PRG_STTS' AND XLAT.FIELDVALUE = LRNR.LM_PRG_STTS AND XLAT.LANGUAGE_CD=ELANG.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLAT4, XLAT, %CurrentDateIn)) AS XLATLONGNAME , 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_PRG_REG_ID=LRNR.LM_PRG_REG_ID AND REQ1.LM_PRG_ID=LRNR.LM_PRG_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_PRG_REG_ID=REQ2.LM_PRG_REG_ID AND REQ1.LM_PRG_ID=REQ2.LM_PRG_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_PRG_REG_ID=LRNR.LM_PRG_REG_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_PRG_REG_ID = REQ3.LM_PRG_REG_ID AND REQ3.LM_PRG_REG_ID<>0))='Y' THEN 'Y' ELSE 'N' END AS LM_LRNG_REQ FROM PS_LM_PRG_REG LRNR, PS_LM_PRG_REG_LANG ELANG WHERE LRNR.LM_PRG_REG_ID=ELANG.LM_PRG_REG_ID AND LRNR.LM_ENRL_DT=( SELECT MAX(LRNR1.LM_ENRL_DT) FROM PS_LM_PRG_REG LRNR1 WHERE LRNR.LM_PERSON_ID =LRNR1.LM_PERSON_ID AND LRNR.LM_PRG_ID = LRNR1.LM_PRG_ID) AND LRNR.LM_PRG_REG_ID = ( SELECT MAX(LRNR2.LM_PRG_REG_ID) FROM PS_LM_PRG_REG LRNR2 WHERE LRNR.LM_PERSON_ID =LRNR2.LM_PERSON_ID AND LRNR.LM_PRG_ID = LRNR2.LM_PRG_ID AND LRNR.LM_ENRL_DT=LRNR2.LM_ENRL_DT)) LRNR ON CRSE.LM_ACT_ID = LRNR.LM_PRG_ID AND PER.LM_PERSON_ID = LRNR.LM_PERSON_ID AND CRSE.LM_CI_ID = 0 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_ADM_PR_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_PRG_ID Number(10,0) DECIMAL(10) NOT NULL Program ID is a unique identifier for curriculum and certification Programs.
    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_PRG_CD Character(30) VARCHAR2(30) NOT NULL Program Code is a user defined field. This field allows duplicates and is not part of the primary key structure.
    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_XLAT_LONG Character(30) VARCHAR2(30) NOT NULL Learner Type
    9 LM_ACT_NAME Character(254) VARCHAR2(254) NOT NULL Activity Name
    10 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
    11 LM_ORG_DESCR Character(50) VARCHAR2(50) NOT NULL Customer Description
    12 LM_JOB_TITLE Character(30) VARCHAR2(30) NOT NULL Job Title
    13 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    14 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description