HR_CP_LICERT_VW

(SQL View)
 Index Back

Employee Resume Licenses & Cer

This view is created from the HR_CP_ACCOMP_VW and ACCOMP_TBL tables and is used for the Licenses and Certifications grid on the Employee Self-Service Interal Resume

SELECT DISTINCT A.EMPLID ,%Substring(B.JPM_CAT_ITEM_ID,1,8) , B.EFFDT ,B.MAJOR_CODE ,%Substring(C.JPM_DESCR90,1,30) ,%Substring(B.JPM_TEXT254_2,1,15) ,%Substring(B.JPM_TEXT254_4,1,30) ,B.COUNTRY ,B.STATE ,B.JPM_DATE_3 ,B.JPM_YN_3 ,B.JPM_YN_2 FROM PS_HR_CP_CAT_VW A , PS_JPM_JP_ITEMS B , PS_JPM_CAT_ITEMS C WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND A.JPM_CAT_TYPE = B.JPM_CAT_TYPE AND A.JPM_CAT_TYPE = C.JPM_CAT_TYPE AND B.JPM_CAT_ITEM_ID = C.JPM_CAT_ITEM_ID AND A.JPM_CAT_TYPE = 'LIC'

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    2 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
    3 DT_ISSUED Date(10) DATE Issue Date
    4 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 LICENSE_NBR Character(15) VARCHAR2(15) NOT NULL License/Certification Number
    7 ISSUED_BY Character(30) VARCHAR2(30) NOT NULL Issued By
    8 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Default Value: OPR_DEF_TBL_HR.COUNTRY

    Prompt Table: COUNTRY_TBL

    9 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: HCR_STONLY_I

    10 EXPIRATN_DT Date(10) DATE Expiration Date
    11 LICENSE_VERIFIED Character(1) VARCHAR2(1) NOT NULL License Verified

    Y/N Table Edit

    Default Value: N

    12 RENEWAL Character(1) VARCHAR2(1) NOT NULL Renewal in Progress

    Y/N Table Edit

    Default Value: N