PCMP_RI_SRC

(SQL View)
 Index Back

Prof Comp RI Search Record

Used in the Professional Compliance module. Search record for Regulated Individuals.

SELECT %Sql(SCRTY_SEL_PKEY,SEC,SEC) ,A.PCMP_MGR_SUPER_ID ,C.PCMP_TYPE ,C.PCMP_REG_AUTH , %Sql(SCRTY_SEL_NMSBR,SEC,SEC) ,C.BUSINESS_UNIT ,A.PCMP_RI_STATUS FROM PS_PERS_SRCH_GBL SEC ,PS_PCMP_MGRSUP_RI A ,PS_PCMP_RI C WHERE A.EMPLID=SEC.EMPLID AND A.EMPLID=C.EMPLID AND A.EMPL_RCD=C.EMPL_RCD AND A.PCMP_TYPE=C.PCMP_TYPE AND A.PCMP_REG_AUTH=C.PCMP_REG_AUTH AND A.PCMP_ASSIGNED_DT = ( SELECT MAX(D.PCMP_ASSIGNED_DT) FROM PS_PCMP_MGRSUP_RI D WHERE D.PCMP_MGR_SUPER_ID = A.PCMP_MGR_SUPER_ID AND D.PCMP_MGR_SUPER = A.PCMP_MGR_SUPER 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<=%CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
5 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.
6 PCMP_TYPE Character(3) VARCHAR2(3) NOT NULL Professiona Compliance Type - Used in the Professional Compliance module. Identifies the different types of professional complianc within a company.
7 PCMP_REG_AUTH Character(10) VARCHAR2(10) NOT NULL Professional Compliance Regulatory Authority - Used within the Professional Compliance module. Identifies the body or organization that defines the rules/regulations that a company must apply to its individuals in order for them to become compliant/qualified in that particular field.
8 NAME Character(50) VARCHAR2(50) NOT NULL Name
9 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
10 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI
11 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
12 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
13 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
14 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
15 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
16 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
17 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
18 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
19 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
20 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