PCMP_RI_RECERVW

(SQL View)
 Index Back

Prof Comp RI Re-certification

Used in the Professional Compliance module. View or regulated individual re-certifications.

SELECT D.PCMP_MGR_SUPER_ID ,D.BUSINESS_UNIT ,A.EMPLID ,B.PCMP_DEV_AREA ,C.EXPIRATN_DT ,D.PCMP_RI_STATUS FROM PS_PCMP_RI_DEV_REC A ,PS_PCMP_RI_DEVAREA B ,PS_ACCOMPLISHMENTS C ,PS_PCMP_MGRSUP_RI D WHERE A.PCMP_ACT_COMP_DT IS NOT NULL AND B.PCMP_ELEM_TYPE='A' AND B.PCMP_DEV_STATUS='P' AND B.EMPLID=A.EMPLID AND B.EMPL_RCD=A.EMPL_RCD AND B.PCMP_TYPE=A.PCMP_TYPE AND B.PCMP_REG_AUTH=A.PCMP_REG_AUTH AND B.PCMP_CREATION_DT=A.PCMP_CREATION_DT AND C.EMPLID=A.EMPLID AND C.ACCOMPLISHMENT=B.PCMP_DEV_AREA AND C.EXPIRATN_DT IS NOT NULL AND C.RENEWAL='N' AND D.EMPLID=A.EMPLID AND D.EMPL_RCD=A.EMPL_RCD AND D.PCMP_TYPE=A.PCMP_TYPE AND D.PCMP_REG_AUTH=A.PCMP_REG_AUTH AND D.PCMP_ASSIGNED_DT = ( SELECT MAX(PCMP_ASSIGNED_DT) FROM PS_PCMP_MGRSUP_RI WHERE PCMP_MGR_SUPER_ID = D.PCMP_MGR_SUPER_ID AND EMPLID = D.EMPLID AND EMPL_RCD = D.EMPL_RCD AND PCMP_TYPE = D.PCMP_TYPE AND PCMP_REG_AUTH = D.PCMP_REG_AUTH AND PCMP_ASSIGNED_DT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PCMP_MGR_SUPER_ID Character(11) VARCHAR2(11) NOT NULL Professional Compliance Manager/Supervisor ID - Used in the Professional Compliance module. Holds the EMPLID of the Manager/Supervisor.
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 PCMP_DEV_AREA Character(8) VARCHAR2(8) NOT NULL Used in the Professional Compliance module. Contains a development area for a given devlopment plan. This can be either an accomplishment, a competency, a training course, or a general activity.
5 EXPIRATN_DT Date(10) DATE Expiration Date
6 PCMP_RI_STATUS Character(1) VARCHAR2(1) NOT NULL Professional Compliance Regulated Individual Status - Used in the Professional Compliance module. Defines whether the individual is active/inactive within the compliance process.
A=Active
I=Inactive