LM_ALL_CD_VW

(SQL View)
 Index Back

Learning Item Codes

Use this view to select all the activity and program codes.

SELECT ACT.LM_ACT_CD , CI.LM_CS_LONG_NM , DMTH.LM_DM_LONG_NM , LE.LM_LE_LONG_NM , ACT.LM_ACT_ID , 0 , ACT.LM_CI_ID FROM PS_LM_ACT ACT , PS_LM_CI_TBL CI , PS_LM_DMTH_TYP_TBL DMTH , PS_LM_ACT_LE LE1 , PS_LM_LE_TBL LE WHERE ACT.LM_CI_ID = CI.LM_CI_ID AND %CurrentDateIn BETWEEN CI.EFFDT AND CI.LM_END_EFFDT AND DMTH.LM_DMTHD_TYPE_ID = ACT.LM_DMTHD_TYPE_ID AND ACT.LM_ACT_ID = LE1.LM_ACT_ID AND LE1.LM_PRM_LE = 'Y' AND LE1.LM_LE_ID = LE.LM_LE_ID UNION ALL SELECT PG.LM_PRG_CD , PG.LM_PRG_LONG_NM , XLAT.XLATLONGNAME , LE.LM_LE_LONG_NM , 0 , PG.LM_PRG_ID , 0 FROM PS_LM_PRG PG , PSXLATITEM XLAT , PS_LM_PRG_LE LE1 , PS_LM_LE_TBL LE WHERE PG.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_LM_PRG X WHERE X.LM_PRG_ID = PG.LM_PRG_ID AND X.EFFDT <= %CurrentDateIn) AND XLAT.FIELDNAME = 'LM_PRG_TYPE' AND XLAT.FIELDVALUE = PG.LM_PRG_TYPE AND %EffdtCheck(PSXLATITEM XLAT1, XLAT, %CURRENTDATEIN) AND PG.LM_PRG_ID = LE1.LM_PRG_ID AND LE1.LM_PRM_LE = 'Y' AND LE1.EFFDT = ( SELECT MAX(LE2.EFFDT) FROM PS_LM_PRG_LE LE2 WHERE LE2.LM_PRG_ID = LE1.LM_PRG_ID AND LE1.LM_PRM_LE = 'Y' AND LE2.EFFDT <= %CurrentDateIn) AND LE1.LM_LE_ID = LE.LM_LE_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LM_RTG_ACT_PRG_CD Character(30) VARCHAR2(30) NOT NULL Learning Code
2 LM_RTG_LRNG_TITLE Character(200) VARCHAR2(200) NOT NULL Learning Title
3 LM_DM_LONG_NM Character(200) VARCHAR2(200) NOT NULL Long Name - the long name of a Delivery Method Type
4 LM_LE_LONG_NM Character(60) VARCHAR2(60) NOT NULL Learning Environment Long Name
5 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
6 LM_PRG_ID Number(10,0) DECIMAL(10) NOT NULL Program ID is a unique identifier for curriculum and certification Programs.
7 LM_CI_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Item ID - System Generated ID Number associated with each Catalog Item