LM_SRCHACTC1LNG

(SQL View)
 Index Back

Search: Rel Lang View

Related Language View for Activity / Catalog Item search view. This view includes catalog items that does not have any activities. Here is how the view is constructed. Indicates if relaled language exists. -------------------------------------(1)---------(1A)----------(1B)-----------(1C) Delivery Method............Y............Y.............N................N Catalog..........................Y............N.............Y................N Message........................Y............Y.............Y................Y

SELECT ACT1.LM_ACT_ID , ACT1.LM_CI_ID , CITLNG1.LANGUAGE_CD , DMTYPLNG1.LM_DM_LONG_NM , DMTYPLNG1.LM_DM_SHRT_NM , CITLNG1.LM_CS_LONG_NM , CITLNG1.LM_CS_DESCR FROM PS_LM_ACT ACT1 , PS_LM_DMTHD_TBL DMALL1 , PS_LM_DMTH_TYP_TBL DMTYP1 , PS_LM_DMTH_TYP_LNG DMTYPLNG1 , PS_LM_CI_TBL CIT1 , PS_LM_CI_LANG CITLNG1 WHERE EXISTS ( SELECT 1 FROM PS_LM_ACT_SEC SEC1 WHERE SEC1.LM_ACT_ID = ACT1.LM_ACT_ID) AND %Sql(LM_SRCHACTCI_NOMSG_RLNG_SQL,ACT1,DMALL1,DMTYP1,CIT1) AND CIT1.LM_CI_ID = CITLNG1.LM_CI_ID AND CIT1.EFFDT = CITLNG1.EFFDT AND CITLNG1.LANGUAGE_CD = DMTYPLNG1.LANGUAGE_CD AND DMTYP1.LM_DMTHD_TYPE_ID = DMTYPLNG1.LM_DMTHD_TYPE_ID UNION SELECT ACT1A.LM_ACT_ID , ACT1A.LM_CI_ID , DMTYPLNG1A.LANGUAGE_CD , DMTYPLNG1A.LM_DM_LONG_NM , DMTYPLNG1A.LM_DM_SHRT_NM , CIT1A.LM_CS_LONG_NM , CIT1A.LM_CS_DESCR FROM PS_LM_ACT ACT1A , PS_LM_DMTHD_TBL DMALL1A , PS_LM_DMTH_TYP_TBL DMTYP1A , PS_LM_DMTH_TYP_LNG DMTYPLNG1A , PS_LM_CI_TBL CIT1A WHERE %Sql(LM_SRCHACTCI_NOMSG_RLNG_SQL,ACT1A,DMALL1A,DMTYP1A,CIT1A) AND DMTYP1A.LM_DMTHD_TYPE_ID = DMTYPLNG1A.LM_DMTHD_TYPE_ID AND NOT EXISTS ( SELECT 1 FROM PS_LM_CI_LANG CITLNG1A WHERE CIT1A.LM_CI_ID = CITLNG1A.LM_CI_ID AND CIT1A.EFFDT = CITLNG1A.EFFDT AND CITLNG1A.LANGUAGE_CD = DMTYPLNG1A.LANGUAGE_CD) UNION SELECT ACT1B.LM_ACT_ID , ACT1B.LM_CI_ID , CITLNG1B.LANGUAGE_CD , DMTYP1B.LM_DM_LONG_NM , DMTYP1B.LM_DM_SHRT_NM , CITLNG1B.LM_CS_LONG_NM , CITLNG1B.LM_CS_DESCR FROM PS_LM_ACT ACT1B , PS_LM_DMTHD_TBL DMALL1B , PS_LM_DMTH_TYP_TBL DMTYP1B , PS_LM_CI_TBL CIT1B , PS_LM_CI_LANG CITLNG1B WHERE %Sql(LM_SRCHACTCI_NOMSG_RLNG_SQL,ACT1B,DMALL1B,DMTYP1B,CIT1B) AND CIT1B.LM_CI_ID = CITLNG1B.LM_CI_ID AND CIT1B.EFFDT = CITLNG1B.EFFDT AND NOT EXISTS ( SELECT 1 FROM PS_LM_DMTH_TYP_LNG DMTYPLNG1B WHERE DMTYP1B.LM_DMTHD_TYPE_ID = DMTYPLNG1B.LM_DMTHD_TYPE_ID AND DMTYPLNG1B.LANGUAGE_CD = CITLNG1B.LANGUAGE_CD) UNION SELECT ACT.LM_ACT_ID , ACT.LM_CI_ID , M314LNG.LANGUAGE_CD , DMTYP.LM_DM_LONG_NM , DMTYP.LM_DM_SHRT_NM , CIT.LM_CS_LONG_NM , CIT.LM_CS_DESCR FROM PS_LM_ACT ACT , PS_LM_DMTHD_TBL DMALL , PS_LM_DMTH_TYP_TBL DMTYP , PS_LM_CI_TBL CIT , PSMSGCATLANG M314LNG WHERE M314LNG.MESSAGE_SET_NBR = 18088 AND M314LNG.MESSAGE_NBR = 314 AND ACT.LM_DMTHD_ID = DMALL.LM_DMTHD_ID AND ACT.LM_DMTHD_TYPE_ID = DMALL.LM_DMTHD_TYPE_ID AND ACT.LM_CI_ID = DMALL.LM_CI_ID AND ACT.LM_DMTHD_TYPE_ID = DMTYP.LM_DMTHD_TYPE_ID AND %CurrentDateIn BETWEEN DMALL.EFFDT AND DMALL.LM_END_EFFDT AND DMALL.LM_DM_STATUS = '10' AND ACT.LM_CI_ID = CIT.LM_CI_ID AND %CurrentDateIn BETWEEN CIT.EFFDT AND CIT.LM_END_EFFDT AND NOT EXISTS ( SELECT 1 FROM PS_LM_DMTH_TYP_LNG DMTYPLNG WHERE DMTYP.LM_DMTHD_TYPE_ID = DMTYPLNG.LM_DMTHD_TYPE_ID AND DMTYPLNG.LANGUAGE_CD = M314LNG.LANGUAGE_CD) AND NOT EXISTS ( SELECT 1 FROM PS_LM_CI_LANG CITLNG WHERE CIT.LM_CI_ID = CITLNG.LM_CI_ID AND CIT.EFFDT = CITLNG.EFFDT AND CITLNG.LANGUAGE_CD = M314LNG.LANGUAGE_CD)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
2 LM_CI_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Item ID - System Generated ID Number associated with each Catalog Item
3 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
4 LM_DM_LONG_NM Character(200) VARCHAR2(200) NOT NULL Long Name - the long name of a Delivery Method Type
5 LM_DM_SHRT_NM Character(10) VARCHAR2(10) NOT NULL Short Name - Short Name of a Delivery Method Type
6 LM_CS_LONG_NM Character(200) VARCHAR2(200) NOT NULL Long Name - Describes the long name of an object
7 LM_CS_DESCR Character(254) VARCHAR2(254) NOT NULL Description - Describes an object with further detail