CO_PE_EXPIR_MEM

(SQL View)
 Index Back

Employee memberships

Employee memberships View to be used in expiring memberships pagelet

SELECT B.EMPLID ,%Substring(A.JPM_CAT_ITEM_ID,1,8) ,A.EFFDT ,%Substring(C.JPM_DESCR90,1,30) ,C.DESCRSHORT ,A.JPM_DATE_3 ,%Substring(A.JPM_TEXT254_2,1,15) ,%Substring(A.JPM_TEXT254_3,1,15) ,D.NAME_DISPLAY ,A.JPM_PROFILE_ID FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B , PS_JPM_CAT_ITEMS C , PS_NAMES D WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND B.JPM_JP_TYPE = ( SELECT JPM_PRI_PERS_TYPE FROM PS_JPM_JP_PRI_TYPE) AND C.JPM_CAT_TYPE= A.JPM_CAT_TYPE AND C.JPM_CAT_ITEM_ID= A.JPM_CAT_ITEM_ID AND B.EMPLID = D.EMPLID AND A.JPM_CAT_TYPE = 'MEM' AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JPM_JP_ITEMS E WHERE E.JPM_PROFILE_ID = A.JPM_PROFILE_ID AND E.JPM_CAT_TYPE= A.JPM_CAT_TYPE AND E.JPM_CAT_ITEM_ID= A.JPM_CAT_ITEM_ID AND E.EFFDT <= %CurrentDateIn) AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JPM_CAT_ITEMS F WHERE F.JPM_CAT_TYPE= A.JPM_CAT_TYPE AND F.JPM_CAT_ITEM_ID= A.JPM_CAT_ITEM_ID AND F.EFFDT <= A.EFFDT)

  • Related Language Record: CO_EXPR_MEM_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 ACCOMPLISHMENT Character(8) VARCHAR2(8) NOT NULL Accomplishment
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    6 MANDATE_END_DATE Date(10) DATE Mandate End Date
    7 MANDATE Character(15) VARCHAR2(15) NOT NULL Mandate
    8 MANDATE_FUNCTION Character(15) VARCHAR2(15) NOT NULL Mandate Position
    9 PERSON_NAME Character(100) VARCHAR2(100) NOT NULL
    10 JPM_PROFILE_ID Character(12) VARCHAR2(12) NOT NULL The id, autoassigned or user assigned of the profile. This id is used to group items and other related material together into one coherent profile.