CO_PE_EXPIR_LIC

(SQL View)
 Index Back

Employee Licence Certificates

Employee Licence Certificates View to be used in expiring licence / certificates pagelet

SELECT B.EMPLID , %Substring(A.JPM_CAT_ITEM_ID,1,8) ,A.EFFDT , %Substring(C.JPM_DESCR90,1,30) ,C.DESCRSHORT ,A.JPM_YN_2 ,A.JPM_DATE_3 ,%Substring(A.JPM_TEXT254_4,1,30) ,D.NAME_DISPLAY ,A.JPM_PROFILE_ID FROM PS_JPM_JP_ITEMS A , PS_JPM_PROFILE B , PS_JPM_CAT_ITEMS C , PS_PERSON_NAME 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 = 'LIC' 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_LIC_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 RENEWAL Character(1) VARCHAR2(1) NOT NULL Renewal in Progress
    7 EXPIRATN_DT Date(10) DATE Expiration Date
    8 ISSUED_BY Character(30) VARCHAR2(30) NOT NULL Issued By
    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.